/****** Object:  View [dbo].[ViwAccSellFactor]    Script Date: 9/2/2025 06:37:53 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--Select PermanentDate ,Permanenter, PermanentDateper,  * from [dbo].[ViwAccSellFactor]

ALTER VIEW [dbo].[ViwAccSellFactor]
AS
SELECT Main.*, COALESCE (Trucks.Weigh, 0) AS TrucksWeight, COALESCE (Trucks.Pack, 0) AS TrucksPack, 
	dbo.FARSI_DATE2(Main.OrderDate) AS OrderDatePer,
	CAST(0 AS bit) AS Selected, COALESCE (Trucks.DocItemID, 0) AS TrucksDocItemID,
	COALESCE (Trucks.TruckNo, '') AS TruckNo,
	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,
	dbo.FARSI_DATE2(Trucks.MinDate) as FirstTruckDatePer,
	dbo.FARSI_DATE2(Trucks.MaxDate) as LastTruckDatePer,
	Convert(bit, CASE MelliCards.Image1 WHEN MelliCards.Image1 THEN 1 ELSE 0 END) as HasMelliImage
	,(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 
	,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
	,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,SUM(Pack) AS Pack, MIN([Date]) as MinDate, MAX([Date]) as MaxDate, STUFF
	((SELECT     ',' + COALESCE(TruckNo,'')
		FROM          dbo.AccSellFactorTrucks
		WHERE t.SellFactorID = SellFactorID FOR XML PATH('')), 1, 1, '') AS TruckNo
		FROM         dbo.AccSellFactorTrucks AS t WITH (NOLOCK)
		GROUP BY SellFactorID) AS Trucks ON Main.ID = Trucks.SellFactorID
	left outer join MelliCards on Main.MelliCode = 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


