/****** Object:  View [dbo].[ViwAccSellFactor2]    Script Date: 4/5/2025 06:31:44 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--select BuyerProject, SellerProject, BuyerCostCenter, SellerCostCenter,BuyerProjectTitle, SellerProjectTitle, BuyerCostCenterTitle, SellerCostCenterTitle, * from ViwAccSellFactor2


ALTER VIEW [dbo].[ViwAccSellFactor2]
AS
SELECT  Main.ID, Main.Number, Main.OrderDate, Main.OrderTotalID, Main.OrderControlID, Main.OrderDetailID1, Main.OrderDetailID2, Main.Type, Main.Source, Main.BuyerTotalID, Main.BuyerControlID, Main.BuyerDetailID1, 
    Main.BuyerDetailID2, Main.SellerTotalID, Main.SellerControlID, Main.SellerDetailID1, Main.SellerDetailID2, Main.Request, Main.RequestUnit, Main.RequestText, Main.SendDate, Main.ControlA, Main.ControlB, Main.ControlC, 
    Main.ControlD, Main.SellType, Main.SellTime, Main.SellPrice, Main.BuyType, Main.BuyTime, Main.BuyPrice, Main.City, Main.Destination, Main.Address, Main.DocItemID, Main.DocItemRelationID, Main.DocItemType, 
    Main.Description, Main.UpdateDate, Main.Updater, Main.Trailer, COALESCE (Trucks.Weigh, 0) AS TrucksWeight, dbo.FARSI_DATE2(Main.OrderDate) AS OrderDatePer, CAST(0 AS bit) AS Selected, COALESCE (Trucks.DocItemID, 0) 
    AS TrucksDocItemID, Main.PrintCount, Main.BuyerRef, Main.MelliCode, Main.BuyerPaid, Main.SellerPaid, Main.IsPermanent, Main.Tel, Main.BuyPrice2, Main.SellPrice2, Main.Barbari, Main.WareOutID, Main.WareInID, 
    Main.PostalCode, Main.FinancialCode, Main.WareOrderID, Main.InsertDate, Main.Inserter, Main.CancelDate, Main.ProfitType, Main.Canceler, Main.PermanentDate, Main.Permanenter, Main.Sender, Main.AppData, Main.FactorID, 
    Main.Sign1User, Main.Sign1Date, Main.Sign2User, Main.Sign2Date, dbo.FARSI_DATE2(Main.UpdateDate) AS UpdateDatePer, dbo.FARSI_DATE2(Main.InsertDate) AS InsertDatePer, dbo.FARSI_DATE2(Main.SendDate) 
    AS SendDatePer, dbo.FARSI_DATE2(Main.CancelDate) AS CancelDatePer, CONVERT(bit, CASE MelliCards.Image1 WHEN MelliCards.Image1 THEN 1 ELSE 0 END) AS HasMelliImage, Main.Cottage,
	dbo.FARSI_DATE2(Main.Sign1Date) AS Sign1DatePer,dbo.FARSI_DATE2(Main.Sign2Date) AS Sign2DatePer
	,AccOrder.Code AS OrderCode, AccOrder.FullCode AS OrderFullCode, AccOrder.Title as OrderName, AccOrder.FullTitle as OrderFullTitle
	,AccBuyer.Code as BuyerCode, AccBuyer.FullCode as BuyerFullCode, AccBuyer.Title as BuyerName, AccBuyer.FullTitle as BuyerFullTitle
	,AccSeller.Code as SellerCode, AccSeller.FullCode as SellerFullCode,AccSeller.Title as SellerName,AccSeller.FullTitle as SellerFullTitle
	,Units.Title as UnitName
	,InserterUser.Name+' '+InserterUser.Family as InserterName
	,UpdaterUser.Name+' '+UpdaterUser.Family as UpdaterName
	,CancelerUser.Name+' '+CancelerUser.Family as CancelerName
	,(case when Main.IsPermanent=1 then dbo.FARSI_DATE2(Main.PermanentDate) else '' end) as PermanentDatePer 
	,(case when Main.IsPermanent=1 then PermanenterUser.Name+' '+PermanenterUser.Family else '' end) as PermanenterName 
	,Main.Request * UnitMultiply.TheValue as RequestTrucksUnit
	,Main.BuyerProject, Main.SellerProject ,Main.BuyerCostCenter, Main.SellerCostCenter
	,BuyerProject.Title as BuyerProjectTitle, SellerProject.Title  as SellerProjectTitle
	,BuyerCostCenter.Title as BuyerCostCenterTitle, SellerCostCenter.Title as SellerCostCenterTitle 

FROM dbo.AccSellFactor AS Main WITH (NOLOCK) left OUTER JOIN
        (SELECT        SellFactorID, CAST(0 AS bigint) AS DocItemID, SUM(Weigh) AS Weigh
        FROM            dbo.AccSellFactorTrucks WITH (NOLOCK)
        WHERE        (DocItemID > 0)
        GROUP BY SellFactorID) AS Trucks ON Main.ID = Trucks.SellFactorID 
	LEFT OUTER JOIN dbo.MelliCards ON Main.MelliCode = dbo.MelliCards.MelliCode
	LEFT OUTER JOIN dbo.ViwAccounts AS AccOrder WITH (NOLOCK) ON AccOrder.TotalID = Main.OrderTotalID AND AccOrder.ControlID = Main.OrderControlID AND AccOrder.Detail1ID = Main.OrderDetailID1 AND AccOrder.Detail2ID = Main.OrderDetailID2 
	LEFT OUTER JOIN dbo.ViwAccounts AS AccBuyer WITH (NOLOCK) ON AccBuyer.TotalID = Main.BuyerTotalID AND AccBuyer.ControlID = Main.BuyerControlID AND AccBuyer.Detail1ID = Main.BuyerDetailID1 AND AccBuyer.Detail2ID = Main.BuyerDetailID2 
	LEFT OUTER JOIN dbo.ViwAccounts AS AccSeller WITH (NOLOCK) ON AccSeller.TotalID = Main.SellerTotalID AND AccSeller.ControlID = Main.SellerControlID AND AccSeller.Detail1ID = Main.SellerDetailID1 AND AccSeller.Detail2ID = Main.SellerDetailID2 
	LEFT OUTER JOIN dbo.SmallLists AS Units WITH (NOLOCK) On Main.RequestUnit = Units.ID
	LEFT OUTER JOIN dbo.SecUser AS InserterUser WITH (NOLOCK) On Main.Inserter = InserterUser.ID
	LEFT OUTER JOIN dbo.SecUser AS UpdaterUser WITH (NOLOCK) On Main.Updater = UpdaterUser.ID
	LEFT OUTER JOIN dbo.SecUser AS CancelerUser WITH (NOLOCK) On Main.Canceler  = CancelerUser.ID
	LEFT OUTER JOIN dbo.SecUser AS PermanenterUser WITH (NOLOCK) On Main.Permanenter  = PermanenterUser.ID
	LEFT OUTER JOIN AccDetail  AS BuyerProject WITH (NOLOCK) On Main.BuyerProject  = BuyerProject.ID
	LEFT OUTER JOIN AccDetail  AS BuyerCostCenter WITH (NOLOCK) On Main.BuyerCostCenter  = BuyerCostCenter.ID
	LEFT OUTER JOIN AccDetail  AS SellerProject WITH (NOLOCK) On Main.SellerProject  = SellerProject.ID
	LEFT OUTER JOIN AccDetail  AS SellerCostCenter WITH (NOLOCK) On Main.SellerCostCenter  = SellerCostCenter.ID
	CROSS JOIN (SELECT TheValue FROM dbo.AppSetting WHERE (Name = 'Company.SellFactor.UnitMultiply')) AS UnitMultiply
GO


