
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[ViwAccSellFactor]
AS
SELECT Main.*, 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,
	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(MinDate) as FirstTruckDatePer,
	dbo.FARSI_DATE2(MaxDate) as LastTruckDatePer,
	Convert(bit, CASE MelliCards.Image1 WHEN MelliCards.Image1 THEN 1 ELSE 0 END) as HasMelliImage
FROM dbo.AccSellFactor as Main WITH (NOLOCK) LEFT OUTER JOIN
  (SELECT     SellFactorID, CAST(0 AS bigint) AS DocItemID, SUM(Weigh) AS Weigh, 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  
GO


