SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[VW_CompleteInvoice]
AS
SELECT     dbo.invoiceDetailTbl.GoodSysCode, dbo.invoiceDetailTbl.Comment AS DetailComment, dbo.invoiceDetailTbl.Quantity, dbo.invoiceDetailTbl.Fee, 
                      dbo.invoiceDetailTbl.Unit, dbo.invoiceDetailTbl.UnitRate, dbo.invoiceDetailTbl.UserPrice, dbo.invoiceDetailTbl.SerialNo, dbo.invoiceDetailTbl.Size, 
                      dbo.invoiceDetailTbl.Color, dbo.invoiceDetailTbl.WareHouse, dbo.invoiceDetailTbl.FK_InvoiceSysCode, dbo.invoiceDetailTbl.DiscountPercent, 
                      dbo.invoiceDetailTbl.SysCode, dbo.invoiceDetailTbl.Term, dbo.invoiceDetailTbl.RowNo, dbo.InvoiceTbl.CustomerName, dbo.InvoiceTbl.FK_AccountSysCode, 
                      dbo.InvoiceTbl.Comment, dbo.InvoiceTbl.InvoiceSerialNo, dbo.InvoiceTbl.RecivedDate, dbo.InvoiceTbl.DelivaredDate, dbo.InvoiceTbl.PaymentDate, 
                      dbo.InvoiceTbl.InvoiceDate, dbo.InvoiceTbl.FK_DocsysCode, dbo.InvoiceTbl.ArchiveName, dbo.InvoiceTbl.UserNo, dbo.InvoiceTbl.InvoiceNo, 
                      dbo.InvoiceTbl.VisitorSyscode, dbo.InvoiceTbl.VisitorPer, dbo.InvoiceTbl.VisitorAmount, dbo.InvoiceTbl.SysDate, dbo.InvoiceTbl.Sign, dbo.InvoiceTbl.Type, 
                      dbo.InvoiceTbl.Amani, dbo.invoiceDetailTbl.DiscountPercent2
FROM         dbo.invoiceDetailTbl INNER JOIN
                      dbo.InvoiceTbl ON dbo.invoiceDetailTbl.FK_InvoiceSysCode = dbo.InvoiceTbl.InvoiceSerialNo
GO

ALTER VIEW [dbo].[VW_invoice_Cost_Amount]
AS
SELECT     CAST((CASE WHEN InvoiceAmount IS NULL THEN 0 ELSE InvoiceAmount END) AS money) AS InvoiceAmount, CAST((CASE WHEN CostAmount IS NULL 
                      THEN 0 ELSE CostAmount END) AS money) AS CostAmount, CAST((CASE WHEN InvoiceAmount IS NULL THEN 0 ELSE InvoiceAmount END) 
                      + (CASE WHEN CostAmount IS NULL THEN 0 ELSE CostAmount END) AS money) AS TotalAmount, CAST(GoodVisitorAmount AS money) AS GoodVisitorAmount, 
                      FK_InvoiceSysCode, InvoiceWieght, GoodCount
FROM         (SELECT     Body.InvoiceAmount, Body.GoodVisitorAmount, Cost.CostAmount, Body.FK_InvoiceSysCode, Body.InvoiceWieght, Body.GoodCount
                        FROM         (SELECT     SUM(dbo.InvoiceCostTbl.Sign * dbo.InvoiceCostDetailTbl.Amount) AS CostAmount, dbo.InvoiceCostDetailTbl.FK_FactorSerialNo
                                                FROM         dbo.InvoiceCostTbl INNER JOIN
                                                                      dbo.InvoiceCostDetailTbl ON dbo.InvoiceCostTbl.SysCode = dbo.InvoiceCostDetailTbl.FK_InvoiceCostSysCode
                                                GROUP BY dbo.InvoiceCostDetailTbl.FK_FactorSerialNo) AS Cost FULL OUTER JOIN
                                                  (SELECT     SUM((dbo.invoiceDetailTbl.Quantity * dbo.invoiceDetailTbl.Fee * dbo.invoiceDetailTbl.UnitRate) 
                                                                           * (1 - dbo.invoiceDetailTbl.DiscountPercent - dbo.invoiceDetailTbl.DiscountPercent2+ dbo.invoiceDetailTbl.DiscountPercent * dbo.invoiceDetailTbl.DiscountPercent2)) AS InvoiceAmount, 
                                                                           dbo.invoiceDetailTbl.FK_InvoiceSysCode, 
                                                                           SUM(dbo.invoiceDetailTbl.Quantity * dbo.invoiceDetailTbl.Fee * dbo.invoiceDetailTbl.UnitRate * dbo.StockTbl.VisitorPer) 
                                                                           AS GoodVisitorAmount, SUM(dbo.StockTbl.Weight) AS InvoiceWieght, COUNT(dbo.StockTbl.GoodSyscode) AS GoodCount
                                                     FROM         dbo.StockTbl INNER JOIN
                                                                           dbo.invoiceDetailTbl ON dbo.StockTbl.GoodSyscode = dbo.invoiceDetailTbl.GoodSysCode
                                                     GROUP BY dbo.invoiceDetailTbl.FK_InvoiceSysCode) AS Body ON Cost.FK_FactorSerialNo = Body.FK_InvoiceSysCode) AS derivedtbl_1
GO
ALTER VIEW [dbo].[VWInvoiceDetailComplete]
AS
SELECT     dbo.invoiceDetailTbl.GoodSysCode, dbo.invoiceDetailTbl.Quantity * dbo.InvoiceTbl.Sign * dbo.invoiceDetailTbl.UnitRate AS NetQuantity, 
                      dbo.invoiceDetailTbl.Fee * (1 - dbo.invoiceDetailTbl.DiscountPercent) AS Fee, dbo.invoiceDetailTbl.SerialNo, dbo.invoiceDetailTbl.Size, dbo.invoiceDetailTbl.Color, 
                      dbo.invoiceDetailTbl.WareHouse, dbo.InvoiceTbl.Amani, dbo.InvoiceTbl.Type, dbo.InvoiceTbl.FK_DocsysCode, dbo.InvoiceTbl.InvoiceDate, 
                      dbo.InvoiceTbl.CustomerName, dbo.InvoiceTbl.InvoiceSerialNo, dbo.InvoiceTbl.FK_AccountSysCode, dbo.invoiceDetailTbl.SysCode, dbo.invoiceDetailTbl.Quantity, 
                      dbo.invoiceDetailTbl.Fee AS GrossFee, dbo.invoiceDetailTbl.Unit, dbo.invoiceDetailTbl.DiscountPercent, dbo.invoiceDetailTbl.Term, dbo.InvoiceTbl.SysShamsiDate, 
                      dbo.InvoiceTbl.SysDate, dbo.InvoiceTbl.InvoiceNo, dbo.InvoiceTbl.Comment, dbo.invoiceDetailTbl.DiscountPercent2
FROM         dbo.invoiceDetailTbl INNER JOIN
                      dbo.InvoiceTbl ON dbo.invoiceDetailTbl.FK_InvoiceSysCode = dbo.InvoiceTbl.InvoiceSerialNo
GO
ALTER PROCEDURE [dbo].[STPCreateInvoiceDetail] 
		@GoodSysCode	int	,
		@Comment	nvarchar(1000)	,
		@Quantity	float	,
		@Fee	money	,
		@Unit	nvarchar(1000)	,
		@UnitRate	float	,
		@UserPrice	money	,
		@SerialNo	nvarchar(100)	,
		@Size	int	,
		@Color	int	,
		@WareHouse	int,	
		@FK_InvoiceSysCode	int	,
		@DiscountPercent	float,
		@DiscountPercent2	float,
		@term nvarchar(1000),
		@rowNo int	,
		@IgnoreAvailability bit=1,
		@invoiceType int,
		@IgnorLowPrice bit=1
				
AS

--Check good
exec [STP_Sys_CheckGoodForInvoiceDetail] 
		@GoodSysCode	=@GoodSysCode,
		@Unit	=@Unit	,
		@UnitRate	=@UnitRate	,
		@SerialNo	=	@SerialNo,
		@Size	=@Size	,
		@Color	=	@Color,
		@Quantity=@Quantity ;

declare @QTY float
set @qty =@Quantity * @UnitRate

if (@invoiceType=1 or @invoiceType=5) exec [STP_Sys_CheckGoodInfoForCreateSellInvoice]
		@GoodSysCode =@GoodSysCode ,
		@Size =@Size,
		@WareHouse =@WareHouse,
		@Color =@Color,
		@Serial=@SerialNo,
		@QTY =@qty,
		@Price =@Fee,
		@IgnoreAvailability=@IgnoreAvailability,
		@IgnorLowPrice=@IgnorLowPrice

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

---------------------------------------------------------------------------------------------------------------------
Go
ALTER PROCEDURE [dbo].[STPGridList_InvoiceDetailChange]
AS
SELECT     Users_1.UName AS OnChangeUName, ChangeinvoiceDetailTbl.OnChangeSysDate, ChangeinvoiceDetailTbl.OnChangeUserId, ChangeinvoiceDetailTbl.RowNo, 
                      ChangeinvoiceDetailTbl.Term, ChangeinvoiceDetailTbl.GoodSysCode, StockTbl.Title AS GoodTitle, StockTbl.Code, StockTbl.BarCode, GoodColorTbl.ColorName, 
                      WareHouseTbl.WareHouseName, GoodSizeTbl.SizeName, ChangeinvoiceDetailTbl.SysCode, ChangeinvoiceDetailTbl.DiscountPercent, 
                      ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.SerialNo, ChangeinvoiceDetailTbl.UnitRate, ChangeinvoiceDetailTbl.Unit, 
                      ChangeinvoiceDetailTbl.Fee, ChangeinvoiceDetailTbl.Quantity, ChangeinvoiceDetailTbl.Comment, CAST((CASE WHEN EXISTS
                          (SELECT     GoodSysCode
                             FROM         invoiceDetailTbl
                             WHERE     (GoodSysCode = ChangeinvoiceDetailTbl.GoodSysCode) AND (FK_InvoiceSysCode = ChangeinvoiceDetailTbl.FK_InvoiceSysCode)) THEN 0 ELSE 1 END)
                       AS bit) AS IsDelete, ChangeinvoiceDetailTbl.DiscountPercent2
FROM         GoodColorTbl RIGHT OUTER JOIN
                      ChangeinvoiceDetailTbl LEFT OUTER JOIN
                      Users AS Users_1 ON ChangeinvoiceDetailTbl.OnChangeUserId = Users_1.UID LEFT OUTER JOIN
                      GoodSizeTbl ON ChangeinvoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN
                      WareHouseTbl ON ChangeinvoiceDetailTbl.WareHouse = WareHouseTbl.SysCode ON GoodColorTbl.SysCode = ChangeinvoiceDetailTbl.Color LEFT OUTER JOIN
                      StockTbl ON ChangeinvoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode
ORDER BY ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.RowNo
Go
ALTER PROCEDURE [dbo].[STPGridList_InvoiceDetailChanged_DetailList] 
AS
SELECT     ChangeinvoiceDetailTbl.GoodSysCode, ChangeinvoiceDetailTbl.Quantity, ChangeinvoiceDetailTbl.Fee, ChangeinvoiceDetailTbl.Unit, ChangeinvoiceDetailTbl.UnitRate, 
                      ChangeinvoiceDetailTbl.SerialNo, ChangeinvoiceDetailTbl.Size, ChangeinvoiceDetailTbl.Color, GoodSizeTbl.SizeName, GoodColorTbl.ColorName, 
                      WareHouseTbl.WareHouseName, ChangeinvoiceDetailTbl.FK_InvoiceSysCode, ChangeinvoiceDetailTbl.DiscountPercent, ChangeinvoiceDetailTbl.OnChangeUserId, 
                      ChangeinvoiceDetailTbl.OnChangeSysDate, ChangeinvoiceDetailTbl.OnChangeSysShamsiDate, dbo.FunMiladiDate(ChangeinvoiceDetailTbl.OnChangeSysDate) 
                      AS MiladiSysDate, ChangeinvoiceDetailTbl.DiscountPercent2
FROM         ChangeinvoiceDetailTbl LEFT OUTER JOIN
                      GoodSizeTbl ON ChangeinvoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN
                      GoodColorTbl ON ChangeinvoiceDetailTbl.Color = GoodColorTbl.SysCode LEFT OUTER JOIN
                      WareHouseTbl ON ChangeinvoiceDetailTbl.WareHouse = WareHouseTbl.SysCode
Go
ALTER PROCEDURE [dbo].[STPListInvoiceDetail] 
	@InvoiceSerial int
AS
SELECT     invoiceDetailTbl.GoodSysCode, invoiceDetailTbl.Comment, invoiceDetailTbl.Quantity, invoiceDetailTbl.Fee, invoiceDetailTbl.Unit, invoiceDetailTbl.UnitRate, 
                      invoiceDetailTbl.UserPrice, invoiceDetailTbl.SerialNo, invoiceDetailTbl.Size, invoiceDetailTbl.Color, invoiceDetailTbl.WareHouse, invoiceDetailTbl.DiscountPercent, 
                      StockTbl.Title, StockTbl.Code, StockTbl.BarCode, invoiceDetailTbl.Term, StockTbl.Weight, GoodSizeTbl.SizeName, WareHouseTbl.WareHouseName, 
                      GoodColorTbl.ColorName, invoiceDetailTbl.RowNo, invoiceDetailTbl.SysCode, invoiceDetailTbl.GoodSysCode AS GoodCode, invoiceDetailTbl.DiscountPercent2
FROM         invoiceDetailTbl INNER JOIN
                      StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode LEFT OUTER JOIN
                      WareHouseTbl ON invoiceDetailTbl.WareHouse = WareHouseTbl.SysCode LEFT OUTER JOIN
                      GoodSizeTbl ON invoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN
                      GoodColorTbl ON invoiceDetailTbl.Color = GoodColorTbl.SysCode
WHERE     (invoiceDetailTbl.FK_InvoiceSysCode = @InvoiceSerial)
Go
ALTER PROCEDURE [dbo].[STPListInvoiceDetailPrint] 
	@SerialNo int
AS
SELECT     invoiceDetailTbl.GoodSysCode AS کدکالا, invoiceDetailTbl.Comment AS توضیحات_کالا, invoiceDetailTbl.Quantity AS تعداد, invoiceDetailTbl.Fee AS فی, 
                      invoiceDetailTbl.Unit AS واحد, invoiceDetailTbl.UnitRate AS نسبت_واحد, invoiceDetailTbl.UserPrice AS قیمت_مصرف_کننده, invoiceDetailTbl.SerialNo AS شماره_سریال, 
                      invoiceDetailTbl.DiscountPercent * 100 AS درصدتخفیف1, StockTbl.Title AS نام_کالا, StockTbl.Code AS کدفنی, StockTbl.BarCode AS بارکد, 
                      invoiceDetailTbl.Term AS نحوه_پرداخت, StockTbl.Weight AS وزن, GoodSizeTbl.SizeName AS سایز, WareHouseTbl.WareHouseName AS انبار, 
                      GoodColorTbl.ColorName AS رنگ, invoiceDetailTbl.RowNo AS ردیف, invoiceDetailTbl.Fee * invoiceDetailTbl.UnitRate AS فی_واحد, 
                      invoiceDetailTbl.Fee * invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate * invoiceDetailTbl.DiscountPercent AS تخفیف1, 
                      invoiceDetailTbl.Fee * invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate AS مبلغ,
					 ((invoiceDetailTbl.Fee * invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate) 
                      * (1 - invoiceDetailTbl.DiscountPercent))* (1 - invoiceDetailTbl.DiscountPercent2) AS مبلغ_کل,
						 invoiceDetailTbl.UserPrice * invoiceDetailTbl.Quantity AS قیمت_کل_مصرف_کننده, 
                      invoiceDetailTbl.DiscountPercent2 * 100 AS درصدتخفیف2,
					invoiceDetailTbl.Fee * invoiceDetailTbl.Quantity * invoiceDetailTbl.UnitRate * (1-invoiceDetailTbl.DiscountPercent) * invoiceDetailTbl.DiscountPercent2 AS تخفیف2
FROM         invoiceDetailTbl INNER JOIN
                      StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode LEFT OUTER JOIN
                      WareHouseTbl ON invoiceDetailTbl.WareHouse = WareHouseTbl.SysCode LEFT OUTER JOIN
                      GoodSizeTbl ON invoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN
                      GoodColorTbl ON invoiceDetailTbl.Color = GoodColorTbl.SysCode
WHERE     (invoiceDetailTbl.FK_InvoiceSysCode = @SerialNo)
GO


ALTER PROCEDURE [dbo].[STPListInvoiceDetailView] 
	@InvoiceSerial int
AS
SELECT     invoiceDetailTbl.GoodSysCode, invoiceDetailTbl.Comment, invoiceDetailTbl.Quantity, invoiceDetailTbl.Fee, invoiceDetailTbl.Unit, invoiceDetailTbl.UnitRate, 
                      invoiceDetailTbl.UserPrice, invoiceDetailTbl.SerialNo, invoiceDetailTbl.Size, invoiceDetailTbl.Color, invoiceDetailTbl.WareHouse, invoiceDetailTbl.DiscountPercent, 
                      invoiceDetailTbl.SysCode, StockTbl.Title, StockTbl.Code, StockTbl.BarCode, invoiceDetailTbl.Term, StockTbl.Weight, GoodSizeTbl.SizeName, 
                      WareHouseTbl.WareHouseName, GoodColorTbl.ColorName, invoiceDetailTbl.RowNo, invoiceDetailTbl.Fee * invoiceDetailTbl.UnitRate AS UnitFee, 
                      invoiceDetailTbl.DiscountPercent * invoiceDetailTbl.Fee * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Quantity AS DiscountAmount, 
                      invoiceDetailTbl.Fee * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Quantity AS SubTotalFee, (1 - invoiceDetailTbl.DiscountPercent) 
                      * invoiceDetailTbl.Fee * invoiceDetailTbl.UnitRate * invoiceDetailTbl.Quantity AS TotalFee, invoiceDetailTbl.DiscountPercent2
FROM         invoiceDetailTbl INNER JOIN
                      StockTbl ON invoiceDetailTbl.GoodSysCode = StockTbl.GoodSyscode LEFT OUTER JOIN
                      WareHouseTbl ON invoiceDetailTbl.WareHouse = WareHouseTbl.SysCode LEFT OUTER JOIN
                      GoodSizeTbl ON invoiceDetailTbl.Size = GoodSizeTbl.SysCode LEFT OUTER JOIN
                      GoodColorTbl ON invoiceDetailTbl.Color = GoodColorTbl.SysCode
WHERE     (invoiceDetailTbl.FK_InvoiceSysCode = @InvoiceSerial)