/****** Object:  View [dbo].[ViwSellFactorDetail]    Script Date: 10/01/2024 05:09:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
Select FactorNumber, Count(*) from ViwSellFactorDetail where FactorNumber>0 group by FactorNumber having Count(*)>1
Select * from ViwSellFactorDetail where FactorNumber=684
select * from AccSellFactorTrucks where SellFactorID in (select ID from AccSellFactor where number=440)
*/
ALTER VIEW [dbo].[ViwSellFactorDetail]
AS
SELECT distinct   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, Trucks.TruckNo, 
                Trucks.Weigh AS TrucksWeigh, Trucks.DocItemRelationID AS TrucksDocItemRelationID, Trucks.ID AS TrucksID, Main.PrintCount, Trucks.Date AS TrucksDate, COALESCE (Trucks.Status, 0) AS TrucksStatus, COALESCE (Trucks.DocHeaderID, 0) 
                AS TrucksDocHeaderID, Trucks.Description AS TrucksDescription, Trucks.BarName, Trucks.Flags AS TrucksFlags, Trucks.Status, Trucks.ReportDate, Main.BuyerRef, Trucks.TruckTel, Trucks.Pack, Main.Inserter, Trucks.InsertDate, Main.Barbari, 
                Main.Canceler, Main.PermanentDate, Main.Permanenter, Main.Sender, Main.WareOutID as MainWareOutID, Main.WareInID as MainWareInID, Main.PostalCode, Main.FinancialCode, Main.MelliCode, Main.Tel, Main.IsPermanent, Main.SellerPaid, Main.BuyerPaid, Main.CancelDate, 
                Main.ProfitType, Main.BuyPrice2, Main.SellPrice2, Main.WareOrderID, Main.AppData, Main.FactorID as MainFactorID, Trucks.FactorID AS TrucksFactorID, Trucks.Canceler AS TrucksCanceler, Trucks.CancelDate AS TrucksCancelDate, 
                Trucks.Inserter AS TrucksInserter, Trucks.BSK, Trucks.ShipID, Main.Sign1User, Main.Sign1Date, Main.Sign2User, Main.Sign2Date, Main.Flags, Main.IsFalsi, Main.Zir5Darsad, Main.HaghAmalKari, Main.Type1Code, Main.CityCode, 
                Main.KalaTypeID, Main.DestCode, Trucks.WareOutID AS TrucksWareOutID, Trucks.WareInID AS TrucksWareInID, Trucks.TruckName, Trucks.Unit, Trucks.FDate, Trucks.HamlTotalID, Trucks.HamlControlID, Trucks.HamlDetailID1, 
                Trucks.HamlDetailID2, Trucks.HamlPrice, Trucks.ChaperTotalID, Trucks.ChaperControlID, Trucks.ChaperDetailID1, Trucks.ChaperDetailID2, Trucks.ChaperPrice, Trucks.Updater AS TrucksUpdater, Trucks.UpdateDate AS TrucksUpdateDate, 
                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.BJoft, Main.BTak, Main.BBarnamehNashodeh, 
                Main.BTakhsis, Trucks.BarnameSerial, Trucks.TaghirMaghsad, Trucks.BarnameAvval, Trucks.BarnameSerialAvval, Trucks.DestinationAvval, Trucks.CottageAvval, Trucks.NumberAvval, Trucks.BKharidId, Trucks.BTakhsisId, 
                AccOrder.Code AS OrderCode, AccOrder.Title AS OrderTitle, AccOrder.FullCode AS OrderFullCode, AccOrder.FullTitle AS OrderFullTitle, AccBuyer.Code AS BuyerCode, AccBuyer.Title AS BuyerTitle, AccBuyer.FullCode AS BuyerFullCode, 
                AccBuyer.FullTitle AS BuyerFullTitle, AccSeller.Code AS SellerCode, AccSeller.Title AS SellerTitle, AccSeller.FullCode AS SellerFullCode, AccSeller.FullTitle AS SellerFullTitle,				
				coalesce(trucks.FactorID,main.FactorID,0) as FactorID,
				coalesce(trucks.WareOutID,main.WareOutID,0) as WareOutID,
				coalesce(trucks.WareInID,main.WareInID,0) as WareInID,
				coalesce(Factor.Number,RevFactor.Number,0) as FactorNumber, coalesce(WareIn.Number,0) as WareInNumber, coalesce(WareOut.Number,0) as WareOutNumber
FROM      dbo.AccSellFactor AS Main WITH (NOLOCK) LEFT OUTER JOIN
                dbo.AccSellFactorTrucks AS Trucks  WITH (NOLOCK) ON Main.ID = Trucks.SellFactorID 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.AccFactor as Factor  WITH (NOLOCK) On Factor.ID=coalesce(trucks.FactorID,main.FactorID,0) LEFT OUTER JOIN
				dbo.AccFactor as RevFactor  WITH (NOLOCK) On RevFactor.OrderID=main.id left OUTER JOIN
				dbo.AccWarehouse as WareIn  WITH (NOLOCK) on WareIn.ID=coalesce(trucks.WareInID,main.WareInID,0) LEFT OUTER JOIN
				dbo.AccWarehouse as WareOut  WITH (NOLOCK) on WareOut.ID=coalesce(trucks.WareOutID,main.WareOutID,0)
GO


