set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go








ALTER PROCEDURE [dbo].[STPCreateInvoiceDetail] 
		@GoodSysCode	int	,
		@Comment	nvarchar(1000)	,
		@Quantity	real	,
		@Fee	money	,
		@Unit	nvarchar(1000)	,
		@UnitRate	real	,
		@UserPrice	money	,
		@SerialNo	nvarchar(1000)	,
		@Size	int	,
		@Color	int	,
		@WareHouse	int,	
		@FK_InvoiceSysCode	int	,
		@DiscountPercent	real,
		@term nvarchar(1000),
		@rowNo int	,
		@IgnoreAvailability bit=1
				
AS

--Check good
declare @goodUnit1 nvarchar(1000),@GoodUnitRate real, @goodUnit2 nvarchar(1000),@hasSerial bit;
SELECT   @GoodUnitRate= UnitRate, @goodUnit2=SecUnit, @goodUnit1=FstUnit,@hasSerial=@serialNo
FROM         StockTbl
WHERE     (GoodSyscode = @goodsyscode)
if @goodunitrate is null begin raiserror (50601,16,1)   return end   --not found
if not (@goodUnit1=@Unit and @UnitRate=1 ) and not (@goodUnit2=@Unit and @UnitRate=@GoodUnitRate )  begin raiserror (50604,16,1)   return end   --not valid row
if not @Color  is null if not exists(SELECT     GoodSysCode FROM         GoodColorTbl WHERE     (GoodSysCode = @goodsyscode) AND (SysCode = @color))  begin raiserror (50604,16,1)   return end  --color not found
if not @Size is null if not exists(SELECT     GoodSysCode FROM         GoodSizeTbl WHERE     (GoodSysCode = @goodsyscode) AND (SysCode = @Size))  begin raiserror (50604,16,1)   return end  --size not found

--------------check serial no
if @hasSerial=1 and (@SerialNo is null or @SerialNo='')   begin raiserror (50602,16,1)   return end  
-------------------------


-------------------  check mojoodi
if @IgnoreAvailability=0 
begin
	declare @SqlStr nvarchar(4000),@GoodRemain real
	set @sqlstr=N'SELECT    @remain=Remain 	FROM  VWGoodDetailRemain WHERE  ('
	if @color is null   set @sqlstr=@sqlstr + N'color is null ' else   set @sqlstr=@sqlstr + N'color =' + cast(@color  as nvarchar)   
	set @sqlstr=@sqlstr + N' and  '
	if @Size is null   set @sqlstr=@sqlstr + N'Size is null ' else   set @sqlstr=@sqlstr + N'Size =' + cast(@Size  as nvarchar)   
	set @sqlstr=@sqlstr + N' and  '
	if @SerialNo is null   set @sqlstr=@sqlstr + N'SerialNo is null ' else   set @sqlstr=@sqlstr + N'SerialNo =' + cast(@SerialNo  as nvarchar)  
	set @sqlstr=@sqlstr + N' and  '
	if @WareHouse is null   set @sqlstr=@sqlstr + N'WareHouse is null ' else   set @sqlstr=@sqlstr + N'WareHouse =' + cast(@WareHouse  as nvarchar)
	set @sqlstr=@sqlstr + N' and GoodSysCode =' + cast(@GoodsysCode  as nvarchar) + ')'
	exec sp_executesql @sqlstr,N'@remain int output',@remain=@GoodRemain output;
	if @GoodRemain is null set @GoodRemain=0
	if @GoodRemain<=@Quantity*@UnitRate   begin raiserror (50603,16,1)   return end  --Quantity exceeded
end
--------------------

INSERT INTO invoiceDetailTbl
                      (GoodSysCode, Comment, Quantity, Fee, Unit, UnitRate, UserPrice, SerialNo, Size, Color, WareHouse, FK_InvoiceSysCode, DiscountPercent, Term, 
                      RowNo)
VALUES     (@GoodSysCode,@Comment,@Quantity,@Fee,@Unit,@UnitRate,@UserPrice,@SerialNo,@Size,@Color,@WareHouse,@FK_InvoiceSysCode,@DiscountPercent,@term,@rowNo)return @@identity