/****** Object:  View [dbo].[ViwAccSellFactorTrucks]    Script Date: 4/5/2025 06:09:52 ب.ظ ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[ViwAccSellFactorTrucks]
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, Main.OrderDatePer, CAST(0 AS bit) AS Selected, COALESCE (Trucks.DocItemID, 0) AS TrucksDocItemID, Main.PrintCount, Main.BuyerRef, 
                Trucks.TrucksDate, Main.MelliCode, Main.Tel, Main.BuyPrice2, Main.SellPrice2, Main.BuyerPaid, Main.SellerPaid, Main.IsPermanent, Main.CancelDate, Main.InsertDate, Main.Inserter, Main.Barbari, Main.Canceler, Main.PermanentDate, 
                Main.Permanenter, Main.Sender, Main.WareOrderID, Main.FinancialCode, Main.PostalCode, Trucks.MinDate, Trucks.MaxDate, Main.Sign1User, Main.Sign1Date, Main.Sign2User, Main.Sign2Date, Main.InsertDatePer, 
				CONVERT(bit, CASE MelliCards.Image1 WHEN MelliCards.Image1 THEN 1 ELSE 0 END) AS HasMelliImage, Main.Flags, Main.IsFalsi, Main.Zir5Darsad, Main.HaghAmalKari, Main.Type1Code, Main.CityCode, Main.KalaTypeID, Main.DestCode, Main.MoreStr0, 
                Main.MoreStr1, Main.MoreStr2, Main.MoreStr3, Main.MoreStr4, Main.MoreStr5, Main.MoreStr6, Main.MoreStr7, Main.MoreStr8, Main.MoreStr9, Main.MoreNum0, Main.MoreNum1, Main.MoreNum2, Main.MoreNum3, Main.MoreNum4, 
                Main.MoreNum5, Main.MoreNum6, Main.MoreNum7, Main.MoreNum8, Main.MoreNum9, Main.MoreDate0, Main.MoreDate1, Main.MoreDate2, Main.MoreDate3, Main.MoreDate4, Main.MoreDate5, Main.MoreDate6, Main.MoreDate7, 
                Main.MoreDate8, Main.MoreDate9, Main.MoreFlags, Main.Cottage, Main.BuyerCostCenter, Main.BuyerProject, Main.SellerCostCenter, Main.SellerProject, Main.BPeygiri, Main.BOstan, Main.BShahr, Main.BPardakhti, Main.BKhShomHesab, 
                Main.BShivPardakht, Main.BShenArzeh, Main.BKhShenasMelli, Main.BKhCodePosti, Main.BKhAddress, Main.BShenVariz, Main.BKhMobile, Main.BKhYekta, Main.BTak, Main.BJoft, Main.BBarnamehNashodeh, Main.BTakhsis, 
                Main.AvarezSeller, Main.AvarezBuyer, Main.HamlCode, Main.CompanyCode, 
				Main.FactorLazem, Main.Status, Main.TaghirStatus, 
				Main.VizTotalID, Main.VizControlID, Main.VizDetailID1, Main.VizDetailID2, Main.VizValue,
                COALESCE (Main.FactorID, 0) AS FactorID, COALESCE (Main.WareOutID, 0) AS WareOutID, COALESCE (Main.WareInID, 0) AS WareInID, COALESCE (Factor.Number, 0) AS FactorNumber, COALESCE (WareIn.Number, 0) 
                AS WareInNumber, COALESCE (WareOut.Number, 0) AS WareOutNumber,  
				Main.SendDatePer, Main.UpdateDatePer,
				Main.PermanentDatePer,
				Main.CancelDatePer,
				Main.Sign1DatePer,
				Main.Sign2DatePer,
				Main.UnitName as MainUnitName,
				Main.RequestTrucksUnit,
				Main.RequestTrucksUnit-TrucksWeight as Diffrence,
				Main.InserterName, 
				Main.UpdaterName,
				Main.CancelerName,
				Main.PermanenterName,
				dbo.FARSI_DATE2(Trucks.MinDate) as MinDatePer, 
				dbo.FARSI_DATE2(Trucks.MaxDate) as MaxDatePer
	,Main.OrderCode, Main.OrderFullCode, Main.OrderName, Main.OrderFullTitle
	,Main.BuyerCode, Main.BuyerFullCode, Main.BuyerName, Main.BuyerFullTitle
	,Main.SellerCode, Main.SellerFullCode,Main.SellerName,Main.SellerFullTitle
	,BuyerProject.Title as BuyerProjectTitle, SellerProject.Title  as SellerProjectTitle
	,BuyerCostCenter.Title as BuyerCostCenterTitle, SellerCostCenter.Title as SellerCostCenterTitle 

FROM      dbo.ViwAccSellFactor AS Main WITH (NOLOCK) LEFT OUTER JOIN
                    (SELECT   SellFactorID, CAST(0 AS bigint) AS DocItemID, SUM(Weigh) AS Weigh, Date AS TrucksDate, MIN(Date) AS MinDate, MAX(Date) AS MaxDate
                     FROM      dbo.AccSellFactorTrucks WITH (NOLOCK)
                     GROUP BY SellFactorID, Date) AS Trucks ON Main.ID = Trucks.SellFactorID 
		LEFT OUTER JOIN dbo.MelliCards ON Main.MelliCode = dbo.MelliCards.MelliCode
		LEFT OUTER JOIN dbo.AccFactor AS Factor WITH (NOLOCK) ON Factor.ID = COALESCE (Main.FactorID, 0) 
		LEFT OUTER JOIN dbo.AccWarehouse AS WareIn WITH (NOLOCK) ON WareIn.ID = COALESCE (Main.WareInID, 0) 
		LEFT OUTER JOIN dbo.AccWarehouse AS WareOut WITH (NOLOCK) ON WareOut.ID = COALESCE (Main.WareOutID, 0) 
	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
GO


