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)