
/****** Object:  View [dbo].[ViwFactorTotalPrice]    Script Date: 7/29/2023 11:41:27 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[ViwFactorTotalPrice]
AS
SELECT       Factor.ID, Factor.Type, Factor.Number, Factor.UserNumber, Factor.Date, Factor.SeriallID, Factor.PersonTotalID, Factor.PersonControlID, Factor.PersonDetailID1, Factor.PersonDetailID2, Factor.PersonAddress, 
                         Factor.PersonFinancialCode, Factor.PersonTel, Factor.PersonFax, Factor.PersonEmail, Factor.PersonWeb, Factor.Description, Factor.Discont, Factor.DiscontIsPercent, Factor.PayType, Factor.UpdateDate, Factor.Updater, 
                         Factor.DiscontTotalID, Factor.DiscontControlID, Factor.DiscontDetailID1, Factor.DiscontDetailID2, Factor.WarehouseID, Factor.WareOutID, Factor.FromWarehouse, Factor.Avarez, Factor.AvarezIsPercent, Factor.AvarezTotalID, 
                         Factor.AvarezControlID, Factor.AvarezDetailID1, Factor.AvarezDetailID2, Factor.Haml, Factor.HamlIsPercent, Factor.HamlTotalID, Factor.HamlControlID, Factor.HamlDetailID1, Factor.HamlDetailID2, Factor.PayTime, Factor.Paid, 
                         Factor.IsPermanent, Factor.VizitorTotalID, Factor.VizitorControlID, Factor.VizitorDetailID1, Factor.VizitorDetailID2, Factor.SellType, SUM(Detail.TotalPrice) AS TotalPrice, Factor.PersonCodePosti, Factor.PersonSabt, 
                         Factor.PersonShenase, Factor.CostCenter, Factor.Project, Factor.OrderID, Factor.IsFalsi, Factor.Zir5Darsad, Factor.HaghAmalKari, Factor.Type1Code, Factor.CityCode, Factor.KalaTypeID,
						 Factor.Taxid, Factor.Irtaxid, Factor.TaxType, factor.TaxUID, Factor.TaxIns, (CASE WHEN Factor.TaxIns=4 THEN -1 ELSE 1 END) as FactorReturn
FROM  (select * from dbo.AccFactor where not ID in (select IrID  from AccFactor with(nolock) where IrID>0 and TaxIns in (2,3))) AS Factor 
		LEFT OUTER JOIN dbo.AccFactorDetail AS Detail ON Factor.ID = Detail.FactorID

GROUP BY Factor.ID, Factor.Type, Factor.Number, Factor.UserNumber, Factor.Date, Factor.SeriallID, Factor.PersonTotalID, Factor.PersonControlID, Factor.PersonDetailID1, Factor.PersonDetailID2, Factor.PersonAddress, 
                         Factor.PersonFinancialCode, Factor.PersonTel, Factor.PersonFax, Factor.PersonEmail, Factor.PersonWeb, Factor.Description, Factor.Discont, Factor.DiscontIsPercent, Factor.PayType, Factor.UpdateDate, Factor.Updater, 
                         Factor.DiscontTotalID, Factor.DiscontControlID, Factor.DiscontDetailID1, Factor.DiscontDetailID2, Factor.WarehouseID, Factor.WareOutID, Factor.FromWarehouse, Factor.Avarez, Factor.AvarezIsPercent, Factor.AvarezTotalID, 
                         Factor.AvarezControlID, Factor.AvarezDetailID1, Factor.AvarezDetailID2, Factor.Haml, Factor.HamlIsPercent, Factor.HamlTotalID, Factor.HamlControlID, Factor.HamlDetailID1, Factor.HamlDetailID2, dbo.FARSI_DATE2(Factor.Date), 
                         Factor.PayTime, Factor.Paid, Factor.IsPermanent, Factor.VizitorTotalID, Factor.VizitorControlID, Factor.VizitorDetailID1, Factor.VizitorDetailID2, Factor.SellType, Detail.FactorID, Factor.PersonCodePosti, Factor.PersonSabt, 
                         Factor.PersonShenase, Factor.CostCenter, Factor.Project, Factor.OrderID, Factor.IsFalsi, Factor.Zir5Darsad, Factor.HaghAmalKari, Factor.Type1Code, Factor.CityCode, Factor.KalaTypeID, Factor.KalaTypeID,
						 Factor.Taxid, Factor.Irtaxid, Factor.TaxType, factor.TaxUID, Factor.TaxIns 


GO


