/****** Object:  View [dbo].[ViwAccSellFactor2]    Script Date: 13/2/2025 12:25:17 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


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

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
	CROSS JOIN (SELECT TheValue FROM dbo.AppSetting WHERE (Name = 'Company.SellFactor.UnitMultiply')) AS UnitMultiply
GO


