/****** Object:  View [dbo].[ViwAccSellFactor]    Script Date: 28/11/1404 03:16:46 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




--Select PermanentDate ,Permanenter, PermanentDateper,  * from [dbo].[ViwAccSellFactor]
--select BuyerProject, SellerProject, BuyerCostCenter, SellerCostCenter,BuyerProjectTitle, SellerProjectTitle, BuyerCostCenterTitle, SellerCostCenterTitle, * from ViwAccSellFactor
--5

ALTER VIEW [dbo].[ViwAccSellFactor]
AS
SELECT Main.*, COALESCE (Trucks.Weigh, 0) AS TrucksWeight, COALESCE (Trucks.Pack, 0) AS TrucksPack, 
	dbo.FARSI_DATE3(Main.OrderDate) AS OrderDatePer,
	CAST(0 AS bit) AS Selected, COALESCE (Trucks.DocItemID, 0) AS TrucksDocItemID,
	COALESCE (Trucks.TruckNo, '') AS TruckNo,
	dbo.FARSI_DATE3(Main.UpdateDate) AS UpdateDatePer,
	dbo.FARSI_DATE3(Main.InsertDate) AS InsertDatePer,
	dbo.FARSI_DATE3(Main.SendDate) AS SendDatePer,
	dbo.FARSI_DATE3(Main.CancelDate) AS CancelDatePer,
	dbo.FARSI_DATE3(Trucks.MinDate) as FirstTruckDatePer,
	dbo.FARSI_DATE3(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_DATE3(Main.PermanentDate) else '' end) as PermanentDatePer 
	,(case when Main.IsPermanent=1 then PermanenterUser.Name+' '+PermanenterUser.Family else '' end) as PermanenterName 
	,dbo.FARSI_DATE3(Main.Sign1Date) AS Sign1DatePer
	,dbo.FARSI_DATE3(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
	,CASE WHEN dbo.FARSI_DATE3(Main.CancelDate)='' THEN '' ELSE CancelerUser.Name+' '+CancelerUser.Family END as CancelerName
	,Main.Request * UnitMultiply.TheValue as RequestTrucksUnit
	,BuyerProject.Title as BuyerProjectTitle, SellerProject.Title  as SellerProjectTitle
	,BuyerCostCenter.Title as BuyerCostCenterTitle, SellerCostCenter.Title as SellerCostCenterTitle 
	,MainDocHeader.Number as DocNumber
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
	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
	LEFT OUTER JOIN AccDocItem AS MainDocItem WITH (NOLOCK) On main.DocItemID = MainDocItem.ID
	LEFT OUTER JOIN AccDocHeader AS MainDocHeader WITH (NOLOCK) On MainDocItem.HeaderID = MainDocHeader.ID
	
	CROSS JOIN (SELECT TheValue FROM dbo.AppSetting WHERE (Name = 'Company.SellFactor.UnitMultiply')) AS UnitMultiply
GO


