File manager - Edit - G:/PleskVhosts/fullscreenksa.com/SultanCompany.fullscreenksa.com/Report/ScriptSultan.txt
Back
ALTER proc [dbo].[FinalDataReortAutomationOrder_2021] --0,50,0,1,1 @TotalDaysX1 int=0, @TotalDaysX2 int=0, @UserId int, @YearNo int = 0 ,--- 0 as 2017 -- 1 as 2018 @IsComeQantity int =0 as begin ----------------------------------------------- Delete and insert data delete from [dbo].[TbShowReportSuppliers] --------------------------------------------------------------------- insert into [dbo].[TbShowReportSuppliers] ([ItemCode],[SupplierPrices],[SupplierName]) SELECT mt.[ItemCode], mt.[ItemPrice], mt.[SupplierName] FROM TestSupplier mt INNER JOIN (SELECT ItemCode, MIN(ItemPrice) MinPrcie FROM TestSupplier GROUP BY ItemCode) t ON mt.ItemCode = t.ItemCode and mt.userid= @UserId AND mt.ItemPrice = t.MinPrcie ------------------------------------------------------------------------------- update ItemsName if(@YearNo=1 or @YearNo=0 ) begin update TabSupplier set TabSupplier.ItemName=t2018.[ItemName], TabSupplier.itemid=t2018.itemid FROM [dbo].[TbShowReportSuppliers] TabSupplier inner join items t2018 ON TabSupplier .[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = t2018.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and t2018.[ConvertedItemID] is null end ------------------------------------------------------------------------------- Update All Colums if(@YearNo=0) begin --UPDATE A --SET --[SafySales] = B.[SafySales] --FROM [dbo].[TbShowReportSuppliers] A INNER JOIN --(SELECT [ItemCode] , -- SUM(isnull(SafySales,0)) AS [SafySales] -- FROM [dbo].[Sales2019] -- GROUP BY [ItemCode]) B --ON A.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = --b.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS update [TbShowReportSuppliers] set [SafySales]=( Isnull((select sum ([Data2023].dbo.OrderDetails.Quantity) from [Data2023].dbo.OrderDetails where [Data2023].dbo.OrderDetails.ItemID= [TbShowReportSuppliers].ItemID AND [Data2023].dbo.OrderDetails.CompID!=4),0) - Isnull((select sum ([Data2023].dbo.OrdersReturnDetails.Quantity) from [Data2023].dbo.OrdersReturnDetails where [Data2023].dbo.OrdersReturnDetails.ItemID= [TbShowReportSuppliers].ItemID AND [Data2023].dbo.OrdersReturnDetails.CompID!=4),0)) update [TbShowReportSuppliers] set [lastPrices]= (select NULLIF(smalllastprice, 0) from items where [TbShowReportSuppliers].[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS =items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null) update [TbShowReportSuppliers] set [MinDateOrder]= (select min(orderdate) from orders) update [TbShowReportSuppliers] set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where [TbShowReportSuppliers].Itemid=[dbo].[ItemQuantity].itemid) end if(@YearNo=1) begin update [TbShowReportSuppliers] set [lastPrices]= (select NULLIF(smalllastprice, 0) from items where [TbShowReportSuppliers].[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS =items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null) update [TbShowReportSuppliers] set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where [TbShowReportSuppliers].Itemid=[dbo].[ItemQuantity].itemid) update [TbShowReportSuppliers] set [MinDateOrder]= (select min(orderdate) from orders) update [TbShowReportSuppliers] set [SafySales]=( Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= [TbShowReportSuppliers].ItemID AND dbo.OrderDetails.CompID!=4),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= [TbShowReportSuppliers].ItemID AND dbo.OrdersReturnDetails.CompID!=4),0)) end -------------------------------------------------------------------------------------------update Qantity With ComingQantity if(@IsComeQantity=1) begin UPDATE A SET [Quantity_Coming] = B.[Quantity_Available] FROM [dbo].[TbShowReportSuppliers] A INNER JOIN (SELECT [Item_Code] , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY [Item_Code]) B ON A.[ItemCode] = B.[Item_Code] end update [dbo].[TbShowReportSuppliers] set [TotalQantity]=isnull(isnull([Quantity_Available],0)+isnull([Quantity_Coming],0),0) --------------------------------------------------------------------------------------- update Required_Quantity if(@YearNo=0) begin set @TotalDaysX1= 365; --(SELECT DATEDIFF(day,(select MIN( MinDateOrder) from [dbo].[TbShowReportSuppliers] Brch),'2018-12-28 00:00:00.000') AS DiffDate) update [dbo].[TbShowReportSuppliers] set [dbo].[TbShowReportSuppliers].Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From [dbo].[TbShowReportSuppliers] Brch end if(@YearNo=1) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from [dbo].[TbShowReportSuppliers] Brch),getdate()) AS DiffDate) update [dbo].[TbShowReportSuppliers] set [dbo].[TbShowReportSuppliers].Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From [dbo].[TbShowReportSuppliers] Brch end create table #DeleteDubicate ( id int, ItemCode nvarchar(150), ItemName nvarchar(350), SafySales decimal(18, 5) , Quantity_Available decimal(18, 5) , Quantity_Come decimal(18, 5) , lastPrices decimal(18, 5) , MinDateOrder datetime , Required_Quantity decimal(18, 5), SupplierPrices decimal(18, 5) , SupplierName nvarchar(150), UserId int , lastPrices1 decimal(18, 5) , HaveAlternative int ) insert into #DeleteDubicate (itemcode) select distinct [ItemCode] from [dbo].[TbShowReportSuppliers] UPDATE T1 SET ItemName =t2.ItemName, SafySales=t2.SafySales, Quantity_Available=t2.Quantity_Available, Quantity_Come=t2.Quantity_Coming, lastPrices=t2.lastPrices, MinDateOrder=t2.MinDateOrder, Required_Quantity=t2.Required_Quantity, SupplierPrices=t2.SupplierPrices, SupplierName =t2.SupplierName, UserId=t2.UserId, lastPrices1=t2.lastPrices1, HaveAlternative=t2.HaveAlternative FROM #DeleteDubicate T1 JOIN [dbo].[TbShowReportSuppliers] T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS truncate table [dbo].[TbShowReportSuppliers] insert into [dbo].[TbShowReportSuppliers] (ItemCode,ItemName,SafySales,Quantity_Available,lastPrices,MinDateOrder,Required_Quantity, SupplierPrices,SupplierName,UserId,lastPrices1,HaveAlternative,Quantity_Coming) select ItemCode,ItemName,SafySales,Quantity_Available,lastPrices,MinDateOrder,Required_Quantity, SupplierPrices,SupplierName,UserId,lastPrices1,HaveAlternative,Quantity_Come from #DeleteDubicate UPDATE T1 SET [price3] =T2.[SmallSellPrice3] FROM [dbo].[TbShowReportSuppliers] T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS and t2.[ConvertedItemID] is null update [dbo].[TbShowReportSuppliers] set [YearName]=case when @YearNo= 1 then 'السنة الحالية' else 'السنة السابقة' end, ComingAdd=case when @IsComeQantity= 1 then 'نعم' else 'لا' end, AlterntaiveAdd='لا', DaysEnter=@TotalDaysX2, DaysFristOrders=@TotalDaysX1, dataorder=getdate() end ALTER proc [dbo].[CompreSuppliersWithAlternativeItems_2021] --0,0,0,1 @TotalDaysX1 int=0, @TotalDaysX2 int=0, @YearNo int = 0 ,--- 0 as 2017 -- 1 as 2018 @IsComeQantity int =0 as BEGIN CREATE TABLE #temp (id INT IDENTITY(1,1),masterid INT) CREATE TABLE #tempshow (id INT IDENTITY(1,1),ItemParentID INT) create table #Tempitems ( id INT IDENTITY(1,1), itemmasterID INT , itemParentID INT, ItemCodeMaster [NVARCHAR](150), ItemCode [NVARCHAR](150), [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Come] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) create table #Tempitems2 ( id INT IDENTITY(1,1), itemmasterID INT, itemParentID INT, ItemCodeMaster [NVARCHAR](150), ItemCode [NVARCHAR](150) , [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Come] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) INSERT INTO #Tempitems2(ItemCode) SELECT ItemCode FROM dbo.TbShowReportSuppliers ------------------------------------------------------------------------------- update ItemsName update TabSupplier set TabSupplier.itemmasterID=t2017.ItemID FROM #Tempitems2 TabSupplier inner join items t2017 ON TabSupplier .[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = t2017.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and t2017.[ConvertedItemID] is null --select * from #Tempitems2 --select * from #Tempitems2 DECLARE @i INT DECLARE @numrows INT DECLARE @RecordID INT DECLARE @ItemMaster INT DECLARE @ItemPerternt INT DECLARE @CheckItemPerternt INT DECLARE @ItemParntID numeric(18, 0) DECLARE @CheckItemMaster INT DECLARE @ID1 INT DECLARE @id INT SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM #Tempitems2) IF @numrows > 0 WHILE (@i <= (SELECT MAX(id) FROM #Tempitems2)) BEGIN SET @ItemParntID = (SELECT ItemMasterID FROM #Tempitems2 WHERE id=@i) TRUNCATE TABLE #Temp TRUNCATE TABLE #tempshow INSERT INTO #temp(masterid) EXEC ViewItemParentproinsert @ItemParntID INSERT INTO #tempshow( ItemParentID )(SELECT masterid FROM #temp) DECLARE @count INT SET @count=1 DECLARE @countall INT SET @countall=(SELECT COUNT(*) FROM #temp) WHILE @count<=@countall BEGIN DECLARE @item INT SET @item=(SELECT masterid FROM #temp WHERE id=@count) INSERT INTO #tempshow ( ItemParentID ) EXEC ViewItemParentproinsert @item SET @count=@count+1 END INSERT INTO #Tempitems(itemmasterID,itemParentID) SELECT DISTINCT @ItemParntID as ItemID, #tempshow.ItemParentID FROM #tempshow WHERE #tempshow.ItemParentID<>@ItemParntID SET @i = @i + 1 PRINT @i END -------------------------------------------------------------update tempitems update TabSupplier set TabSupplier.ItemCode=t2017.ItemCode FROM #Tempitems TabSupplier inner join items t2017 ON TabSupplier.itemParentID = t2017.ItemID update TabSupplier set TabSupplier.ItemCodeMaster =t2018.ItemCode FROM #Tempitems TabSupplier inner join items t2018 ON TabSupplier.itemmasterID = t2018.ItemID ------------------------------------------------------------------------------- Update All Colums if(@YearNo=1) begin update #Tempitems set [SafySales]=(Isnull((select sum ([Data2023].dbo.OrderDetails.Quantity) from [Data2023].dbo.OrderDetails where [Data2023].dbo.OrderDetails.ItemID= #Tempitems.itemparentid AND [Data2023].dbo.OrderDetails.CompID !=4),0) -Isnull((select sum ([Data2023].dbo.OrdersReturnDetails.Quantity) from [Data2023].dbo.OrdersReturnDetails where [Data2023].dbo.OrdersReturnDetails.ItemID= #Tempitems.itemparentid AND [Data2023].dbo.OrdersReturnDetails.CompID!=4),0)) update #Tempitems set [lastPrices]= (select isnull([SmallLastPrice], 0) from items where #Tempitems.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null) update #Tempitems set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #Tempitems.itemparentid=[dbo].[ItemQuantity].itemid) end if(@YearNo=2) begin update #Tempitems set [lastPrices]= (select isnull([SmallLastPrice], 0) from items where #Tempitems.itemparentid=items.itemid) update #Tempitems set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #Tempitems.itemparentid=[dbo].[ItemQuantity].itemid) update #Tempitems set [MinDateOrder]= (select min(orderdate) from orders) update #Tempitems set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems.itemparentid AND dbo.OrderDetails.CompID !=4),0) -Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems.itemparentid AND OrdersReturnDetails.CompID!=4),0)) end ------------------------------------------------------------------------------------------- update Qantity Come if(@IsComeQantity=1) begin UPDATE A SET [Quantity_Come] = B.[Quantity_Available] FROM #Tempitems A INNER JOIN (SELECT [Item_Code] , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY [Item_Code]) B ON A.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = B.[Item_Code] COLLATE SQL_Latin1_General_CP1_CI_AS end UPDATE A SET A.[Quantity_Available] = A.[Quantity_Available] + B.[Quantity_Available], A.[SafySales] = A.[SafySales]+ B.[SafySales], A.[lastPrices1] = B.[LastPrice] , A.[HaveAlternative]=1 FROM dbo.TbShowReportSuppliers A INNER JOIN (SELECT ItemCodeMaster,SUM(isnull(Quantity_Available,0)) AS [Quantity_Available], SUM(isnull(SafySales,0)) AS [SafySales], MIN(NULLIF(lastPrices, 0)) AS [LastPrice] FROM #Tempitems GROUP BY ItemCodeMaster) B ON A.ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS = B.ItemCodeMaster COLLATE SQL_Latin1_General_CP1_CI_AS UPDATE A SET A.[Quantity_Coming] = A.[Quantity_Coming] + B.[Quantity_Available] FROM dbo.TbShowReportSuppliers A INNER JOIN (SELECT ItemCodeMaster,SUM(IsNull([Quantity_Come],0) ) AS [Quantity_Available] FROM #Tempitems GROUP BY ItemCodeMaster) B ON A.ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS = B.ItemCodeMaster COLLATE SQL_Latin1_General_CP1_CI_AS update [dbo].[TbShowReportSuppliers] set [Quantity_Available]=0 where [Quantity_Available] is null update [dbo].[TbShowReportSuppliers] set [Quantity_Coming]=0 where [Quantity_Coming] is null update [dbo].[TbShowReportSuppliers] set [TotalQantity]=isnull(isnull([Quantity_Available],0)+ isnull([Quantity_Coming],0),0) --------------------------------------------------------------------------------------- update Required_Quantity if(@YearNo=1) begin set @TotalDaysX1= 365; update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From [dbo].[TbShowReportSuppliers] Brch end if(@YearNo=2) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from [dbo].[TbShowReportSuppliers] Brch),getdate()) AS DiffDate); update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From [dbo].[TbShowReportSuppliers] Brch end select * from [TbShowReportSuppliers] create table #DeleteDubicate ( id int, ItemCode nvarchar(150), ItemName nvarchar(350), SafySales decimal(18, 5) , Quantity_Available decimal(18, 5) , Quantity_Come decimal(18, 5) , lastPrices decimal(18, 5) , MinDateOrder datetime , Required_Quantity decimal(18, 5), SupplierPrices decimal(18, 5) , SupplierName nvarchar(150), UserId int , lastPrices1 decimal(18, 5) , HaveAlternative int , itemid bigint ) insert into #DeleteDubicate (itemcode) select distinct [ItemCode] from [dbo].[TbShowReportSuppliers] UPDATE T1 SET ItemName =t2.ItemName, SafySales=t2.SafySales, Quantity_Available=t2.Quantity_Available, Quantity_Come=t2.Quantity_Coming, lastPrices=t2.lastPrices, MinDateOrder=t2.MinDateOrder, Required_Quantity=t2.Required_Quantity, SupplierPrices=t2.SupplierPrices, SupplierName =t2.SupplierName, UserId=t2.UserId, lastPrices1=t2.lastPrices1, HaveAlternative=t2.HaveAlternative, itemid=t2.itemid FROM #DeleteDubicate T1 JOIN [dbo].[TbShowReportSuppliers] T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS truncate table [dbo].[TbShowReportSuppliers] insert into [dbo].[TbShowReportSuppliers] (ItemCode,ItemName,SafySales,Quantity_Available,lastPrices,MinDateOrder,Required_Quantity, SupplierPrices,SupplierName,UserId,lastPrices1,HaveAlternative,Quantity_Coming,itemid) select ItemCode,ItemName,SafySales,Quantity_Available,lastPrices,MinDateOrder,Required_Quantity, SupplierPrices,SupplierName,UserId,lastPrices1,HaveAlternative,Quantity_Come,itemid from #DeleteDubicate UPDATE T1 SET itemid =T2.itemid FROM [dbo].[TbShowReportSuppliers] T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS and t2.[ConvertedItemID] is null UPDATE T1 SET [price3] =T2.[SmallSellPrice3] FROM [dbo].[TbShowReportSuppliers] T1 JOIN items T2 ON T1.itemid = T2.itemid UPDATE T1 SET itemidPurches =T2.itemid FROM TbShowReportSuppliers T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS ------------------------------------------------ update Supllier from 2022 --UPDATE TbShowReportSuppliers --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] where -- TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- ORDER BY ordersn DESC) -- UPDATE TbShowReportSuppliers SET supyear=1 WHERE lastPurches IS NOT NULL -- UPDATE TbShowReportSuppliers --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] where TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid ORDER BY ordersn DESC) --UPDATE T1 SET supid =T2.supplierid FROM TbShowReportSuppliers T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid AND ISNULL (supyear,0)=1 ------------------------------------------------------- update suplier from 2021 --UPDATE TbShowReportSuppliers --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] where -- TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- AND ISNULL (supyear,0)!=1 -- ORDER BY ordersn DESC) -- UPDATE TbShowReportSuppliers SET supyear=0 WHERE ISNULL (supyear,0) !=1 -- UPDATE TbShowReportSuppliers --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] where TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid and ISNULL (supyear,0)=0 -- ORDER BY ordersn DESC) --UPDATE T1 SET supid =T2.supplierid FROM TbShowReportSuppliers T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid AND ISNULL (supyear,0)=0 UPDATE TbShowReportSuppliers SET supid=( CASE WHEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=TbShowReportSuppliers.Itemid ORDER BY OrderDate DESC) >0 THEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=TbShowReportSuppliers.Itemid ORDER BY OrderDate DESC) ELSE (SELECT TOP 1 SupplierID FROM [Data2023].dbo.Purchases INNER JOIN [Data2023].dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE [Data2023].dbo.PurchaseDetails.ItemID=TbShowReportSuppliers.Itemid ORDER BY OrderDate DESC) end ) UPDATE T1 SET supname =T2.[CustSuppName] FROM TbShowReportSuppliers T1 JOIN [Data2023].[dbo].[CustSupp] T2 ON T1.supid =T2.[CustSuppId] update [dbo].[TbShowReportSuppliers] SET LastSaleDate= (SELECT MAX(OrderDate) FROM dbo.Orders INNER JOIN OrderDetails on dbo.Orders.OrderSn=OrderDetails.OrderSn AND dbo.Orders.CompID=OrderDetails.CompID AND [TbShowReportSuppliers].Itemid=OrderDetails.ItemID) update [dbo].[TbShowReportSuppliers] set [YearName]=case when @YearNo= 1 then 'السنة الحالية' else 'السنة السابقة' end, ComingAdd=case when @IsComeQantity= 1 then 'نعم' else 'لا' end, AlterntaiveAdd= 'نعم', DaysEnter=@TotalDaysX2, DaysFristOrders=@TotalDaysX1, dataorder=getdate() DROP TABLE #temp DROP TABLE #Tempitems2 DROP TABLE #Tempitems DROP TABLE #tempshow drop table #DeleteDubicate END ALTER proc [dbo].[GetDataFromFiles_Taliba_2021] --100,300,1,1,1 @TotalDaysX1 int=0, @TotalDaysX2 int=0, @UserId int, @YearNo int = 0 ,--- 0 as 2017 -- 1 as 2018 @IsComeQantity int =0 as begin ------------------------------------------------------------------------------------------------------------------- Get Items From Files create table #tempItemsSuppliers (id int , itemcode NVARCHAR(150) ,ItemName NVARCHAR(150),SafySales decimal(18, 5),Quantity_Available decimal(18, 5) , lastPrices decimal(18, 5),MinDateOrder datetime,Required_Quantity decimal(18, 5) ,SupplierPrices decimal(18, 5),SupplierName nvarchar(150),UserId int , lastPrices1 decimal(18, 5),HaveAlternative int,price3 decimal(18, 5),Quantity_Coming decimal(18, 5),TotalQantity decimal(18, 5), SupNameMin NVARCHAR(150),itemid bigint,dateTlabia datetime, itemidPurches numeric(18, 0),supname nvarchar(350),lastPurches bigint ,supid bigint ,compid int, YearName nvarchar(150),AlterntaiveAdd nvarchar(150),ComingAdd nvarchar(150),DaysEnter bigint,DaysFristOrders BIGINT, OrdersnSale Bigint,LastSaleDate nvarchar(350) ) insert into #tempItemsSuppliers (itemcode,SupplierPrices, SupplierName) select [ItemCode],[ItemPrice],[SupplierName] from [TestSupplier] where itemcode is not null --select * from #tempItemsSuppliers where SupplierName= 'Rokn' and itemcode in (select itemcode from #tempItemsSuppliers where SupplierName= 'alrawasi') order by itemcode --drop table #tempItemsSuppliers --select distinct itemcode from #tempItemsSuppliers -------------------------------------------------------------------------------------------- Update Prices if(@YearNo=1 or @YearNo=0 ) begin update TabSupplier set TabSupplier.ItemName=t2018.[ItemName],TabSupplier.itemid=t2018.itemid FROM #tempItemsSuppliers TabSupplier inner join items t2018 on TabSupplier .[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = t2018.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and t2018.[ConvertedItemID] is null end ------------------------------------------------------------------------------- Update All Colums if(@YearNo=0) begin update #tempItemsSuppliers set [SafySales]=(Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= #tempItemsSuppliers.itemid AND Data2023.dbo.OrderDetails.CompID!=4 ),0) -Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= #tempItemsSuppliers.itemid AND Data2023.dbo.OrdersReturnDetails.CompID !=4 ),0)) update #tempItemsSuppliers set [lastPrices]= (select top 1 isnull([SmallLastPrice], 0) from items where #tempItemsSuppliers.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null) update #tempItemsSuppliers set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #tempItemsSuppliers.itemid=[dbo].[ItemQuantity].itemid) end if(@YearNo=1) begin update #tempItemsSuppliers set [lastPrices]= (select top 1 isnull([SmallLastPrice], 0) from items where #tempItemsSuppliers.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null) --select * from #tempItemsSuppliers update #tempItemsSuppliers set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #tempItemsSuppliers.itemid=[dbo].[ItemQuantity].itemid) update #tempItemsSuppliers set [MinDateOrder]= (select min(orderdate) from orders) update #tempItemsSuppliers set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #tempItemsSuppliers.itemid AND OrderDetails.CompID!=4 ),0) -Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #tempItemsSuppliers.itemid AND OrdersReturnDetails.CompID !=4 ),0)) end if(@IsComeQantity=1) begin UPDATE A SET [Quantity_Coming] = B.[Quantity_Available] FROM #tempItemsSuppliers A INNER JOIN (SELECT [Item_Code] , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY [Item_Code]) B on A.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = B.[Item_Code] COLLATE SQL_Latin1_General_CP1_CI_AS end update #tempItemsSuppliers set [Quantity_Coming] =0 where [Quantity_Coming] is null update #tempItemsSuppliers set [TotalQantity]=isnull(isnull([Quantity_Available],0)+isnull([Quantity_Coming],0),0) if(@YearNo=0) begin set @TotalDaysX1= 365; update #tempItemsSuppliers set Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From #tempItemsSuppliers Brch end if(@YearNo=1) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from #tempItemsSuppliers Brch),getdate()) AS DiffDate) update #tempItemsSuppliers set Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From #tempItemsSuppliers Brch end UPDATE T1 SET [price3] =T2.[SmallSellPrice3] FROM #tempItemsSuppliers T1 JOIN items T2 on T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS and T2.[ConvertedItemID] is null ------------------------------------------------------------------- End Frist Store CREATE TABLE #temp (id INT IDENTITY(1,1),masterid INT) CREATE TABLE #tempshow (id INT IDENTITY(1,1),ItemParentID INT) create table #Tempitems ( id INT IDENTITY(1,1), itemmasterID INT , itemParentID INT, ItemCodeMaster [NVARCHAR](150), ItemCode [NVARCHAR](150), [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Come] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) create table #Tempitems2 ( id INT IDENTITY(1,1), itemmasterID INT, itemParentID INT, ItemCodeMaster [NVARCHAR](150), ItemCode [NVARCHAR](150) , [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Come] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) INSERT INTO #Tempitems2(ItemCode) SELECT distinct ItemCode FROM #tempItemsSuppliers if(@YearNo=0) begin update TabSupplier set TabSupplier.itemmasterID=t2017.ItemID FROM #Tempitems2 TabSupplier inner join items t2017 on TabSupplier .[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = t2017.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and t2017.[ConvertedItemID] is null end if(@YearNo=1) begin update TabSupplier set TabSupplier.itemmasterID=t2018.ItemID FROM #Tempitems2 TabSupplier inner join items t2018 on TabSupplier .[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = t2018.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and t2018.[ConvertedItemID] is null end DECLARE @i INT DECLARE @numrows INT DECLARE @RecordID INT DECLARE @ItemMaster INT DECLARE @ItemPerternt INT DECLARE @CheckItemPerternt INT DECLARE @ItemParntID numeric(18, 0) DECLARE @CheckItemMaster INT DECLARE @ID1 INT DECLARE @id INT SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM #Tempitems2) IF @numrows > 0 WHILE (@i <= (SELECT MAX(id) FROM #Tempitems2)) BEGIN SET @ItemParntID = (SELECT ItemMasterID FROM #Tempitems2 WHERE id=@i) TRUNCATE TABLE #Temp TRUNCATE TABLE #tempshow INSERT INTO #temp(masterid) EXEC ViewItemParentproinsert @ItemParntID INSERT INTO #tempshow( ItemParentID )(SELECT masterid FROM #temp) DECLARE @count INT SET @count=1 DECLARE @countall INT SET @countall=(SELECT COUNT(*) FROM #temp) WHILE @count<=@countall BEGIN DECLARE @item INT SET @item=(SELECT masterid FROM #temp WHERE id=@count) INSERT INTO #tempshow ( ItemParentID ) EXEC ViewItemParentproinsert @item SET @count=@count+1 END INSERT INTO #Tempitems(itemmasterID,itemParentID) SELECT DISTINCT @ItemParntID as ItemID, #tempshow.ItemParentID FROM #tempshow WHERE #tempshow.ItemParentID<>@ItemParntID SET @i = @i + 1 PRINT @i END if(@YearNo=0) begin update TabSupplier set TabSupplier.ItemCode=t2017.ItemCode FROM #Tempitems TabSupplier inner join items t2017 ON TabSupplier.itemParentID = t2017.ItemID end if(@YearNo=1) begin update TabSupplier set TabSupplier.ItemCode=t2018.ItemCode FROM #Tempitems TabSupplier inner join items t2018 ON TabSupplier.itemParentID = t2018.ItemID end update TabSupplier set TabSupplier.ItemCodeMaster =t2018.ItemCode FROM #Tempitems TabSupplier inner join items t2018 ON TabSupplier.itemmasterID = t2018.ItemID ------------------------------------------------------------------------------- Update All Colums if(@YearNo=0) begin --UPDATE A --SET --[SafySales] = B.[SafySales] --FROM #Tempitems A INNER JOIN --(SELECT [ItemCode] , -- SUM(isnull(SafySales,0)) AS [SafySales] -- FROM [dbo].[Sales2019] -- GROUP BY [ItemCode]) B -- ON A.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = --B.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS update #Tempitems set [SafySales]=(Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= #Tempitems.itemparentid AND Data2023.dbo.OrderDetails.CompID !=4 ),0) -Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= #Tempitems.itemparentid AND Data2023.dbo.OrdersReturnDetails.CompID!=4 ),0)) update #Tempitems set [lastPrices]= (select isnull([SmallLastPrice], 0) from items where #Tempitems.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null ) update #Tempitems set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #Tempitems.itemparentid=[dbo].[ItemQuantity].itemid) end if(@YearNo=1) begin update #Tempitems set [lastPrices]= (select isnull([SmallLastPrice], 0) from items where #Tempitems.itemparentid=items.itemid) update #Tempitems set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #Tempitems.itemparentid=[dbo].[ItemQuantity].itemid) update #Tempitems set [MinDateOrder]= (select min(orderdate) from orders) update #Tempitems set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems.itemparentid AND OrderDetails.CompID !=4 ),0) -Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems.itemparentid AND OrdersReturnDetails.CompID!=4 ),0)) end --select * from #Tempitems order by itemmasterid ------------------------------------------------------------------------------------------- update Qantity Come --select * from #Tempitems order by itemmasterid if(@IsComeQantity=1) begin UPDATE A SET [Quantity_Come] = B.[Quantity_Available] FROM #Tempitems A INNER JOIN (SELECT [Item_Code] , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY [Item_Code]) B ON A.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = B.[Item_Code] COLLATE SQL_Latin1_General_CP1_CI_AS end UPDATE A SET A.[Quantity_Available] = A.[Quantity_Available] + B.[Quantity_Available], A.[SafySales] = A.[SafySales]+ B.[SafySales], A.[lastPrices1] = B.[LastPrice] , A.[HaveAlternative]=1 FROM #tempItemsSuppliers A INNER JOIN (SELECT ItemCodeMaster,SUM(isnull(Quantity_Available,0)) AS [Quantity_Available], SUM(isnull(SafySales,0)) AS [SafySales], MIN(NULLIF(lastPrices, 0)) AS [LastPrice] FROM #Tempitems GROUP BY ItemCodeMaster) B ON A.ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS = B.ItemCodeMaster COLLATE SQL_Latin1_General_CP1_CI_AS UPDATE A SET A.[Quantity_Coming] = isnull(A.[Quantity_Coming],0) + isnull(B.[Quantity_Available],0) FROM #tempItemsSuppliers A INNER JOIN (SELECT ItemCodeMaster,SUM(IsNull([Quantity_Come],0) ) AS [Quantity_Available] FROM #Tempitems GROUP BY ItemCodeMaster) B ON A.ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS = B.ItemCodeMaster COLLATE SQL_Latin1_General_CP1_CI_AS update #tempItemsSuppliers set [Quantity_Available]=0 where [Quantity_Available] is null update #tempItemsSuppliers set [Quantity_Coming]=0 where [Quantity_Coming] is null update #tempItemsSuppliers set [TotalQantity]=isnull(isnull([Quantity_Available],0)+ isnull([Quantity_Coming],0),0) --------------------------------------------------------------------------------------- update Required_Quantity if(@YearNo=0) begin set @TotalDaysX1= 365; --(SELECT DATEDIFF(day,(select MIN( MinDateOrder) from [dbo].[TbShowReportSuppliers] Brch),'2018-12-28 00:00:00.000') AS DiffDate); update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From #tempItemsSuppliers Brch end if(@YearNo=1) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from #tempItemsSuppliers Brch),getdate()) AS DiffDate); update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From #tempItemsSuppliers Brch end UPDATE T1 SET [price3] =T2.[SmallSellPrice3] FROM #tempItemsSuppliers T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS and T2.[ConvertedItemID] is null create table #temp10(itemcode NVARCHAR(150) , [SupplierPrices] decimal(18, 2) , [SupplierName] nvarchar(150)) insert into #temp10 ([ItemCode],[SupplierPrices],[SupplierName]) SELECT mt.[ItemCode], mt.SupplierPrices, mt.[SupplierName] FROM #tempItemsSuppliers mt INNER JOIN (SELECT ItemCode, MIN(SupplierPrices) MinPrcie FROM #tempItemsSuppliers GROUP BY ItemCode) t ON mt.ItemCode = t.ItemCode AND mt.SupplierPrices = t.MinPrcie update #tempItemsSuppliers set #tempItemsSuppliers.supnamemin =#temp10.[SupplierName] FROM #temp10 inner join #tempItemsSuppliers ON #temp10.itemcode = #tempItemsSuppliers.itemcode UPDATE T1 SET itemidPurches =T2.itemid FROM #tempItemsSuppliers T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS --UPDATE #tempItemsSuppliers --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] WHERE -- #tempItemsSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- ORDER BY ordersn DESC) --UPDATE #tempItemsSuppliers --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] WHERE -- #tempItemsSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- ORDER BY ordersn DESC) -- UPDATE #tempItemsSuppliers --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] -- where #tempItemsSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- ORDER BY ordersn DESC) -- UPDATE #tempItemsSuppliers --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] -- where #tempItemsSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- ORDER BY ordersn DESC) --UPDATE T1 SET supid =T2.supplierid FROM #tempItemsSuppliers T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid --UPDATE T1 SET supid =T2.supplierid FROM #tempItemsSuppliers T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid --UPDATE T1 SET supname =T2.[CustSuppName] FROM #tempItemsSuppliers T1 JOIN [Data2023].[dbo].[CustSupp] T2 ON T1.supid =T2.[CustSuppId] UPDATE #tempItemsSuppliers SET supid=( CASE WHEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=#tempItemsSuppliers.Itemid ORDER BY OrderDate DESC) >0 THEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=#tempItemsSuppliers.Itemid ORDER BY OrderDate DESC) ELSE (SELECT TOP 1 SupplierID FROM [Data2023].dbo.Purchases INNER JOIN [Data2023].dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE [Data2023].dbo.PurchaseDetails.ItemID=#tempItemsSuppliers.Itemid ORDER BY OrderDate DESC) end ) UPDATE T1 SET supname =T2.[CustSuppName] FROM #tempItemsSuppliers T1 JOIN [Data2023].[dbo].[CustSupp] T2 ON T1.supid =T2.[CustSuppId] update #tempItemsSuppliers set [YearName]=case when @YearNo= 1 then 'السنة الحالية' else 'السنة السابقة' end, ComingAdd=case when @IsComeQantity= 1 then 'نعم' else 'لا' end, AlterntaiveAdd= 'نعم', DaysEnter=@TotalDaysX2, DaysFristOrders=@TotalDaysX1, dateTlabia=getdate() update #tempItemsSuppliers SET LastSaleDate= (SELECT CONVERT(VARCHAR(10), MAX(OrderDate), 111) FROM dbo.Orders INNER JOIN OrderDetails on dbo.Orders.OrderSn=OrderDetails.OrderSn AND dbo.Orders.CompID=OrderDetails.CompID AND #tempItemsSuppliers.itemidPurches=OrderDetails.ItemID) -- update #tempItemsSuppliers SET LastSaleDate= ( SELECT CONVERT(VARCHAR,DATEADD(DAY,-1,MAX(OrderDate)),101) FROM dbo.Orders -- INNER JOIN OrderDetails on dbo.Orders.OrderSn=OrderDetails.OrderSn AND dbo.Orders.CompID=OrderDetails.CompID --AND #tempItemsSuppliers.itemidPurches=OrderDetails.ItemID) --------------------------------------------------------------------------------------------------------------------------------------------------------- create table #tempShowItemsSuppliers ("سنة المقارنة" NVARCHAR(150),"كميات قادمة" NVARCHAR(150),"أصناف بديلة" NVARCHAR(150),"ايام مدخلة" bigint,"ايام المقارنة" bigint,"تاريخ الطالبية" datetime,"كود الصنف" NVARCHAR(150) ,"اسم الصنف" NVARCHAR(150) ,"مبيعات" decimal(18, 2),"متوفرة" decimal(18, 2) , "قادمة" decimal(18, 2),"سعر3" decimal(18, 2),"تكلفة البديل" decimal(18, 2), "تكلفة مستودع" decimal(18, 2),"اخر مورد" NVARCHAR(150) ,SupplierPrices decimal(18, 2),SupplierName nvarchar(150),"مقترحة" decimal(18, 2),"SupMin" nvarchar(150),"تاريخ أخر بيع" nvarchar(350)) insert into #tempShowItemsSuppliers("سنة المقارنة","كميات قادمة","أصناف بديلة","ايام مدخلة","ايام المقارنة","تاريخ الطالبية","كود الصنف","اسم الصنف","مبيعات","متوفرة","قادمة","سعر3","تكلفة البديل","تكلفة مستودع","اخر مورد",SupplierPrices,SupplierName,"مقترحة","SupMin","تاريخ أخر بيع") SELECT YearName,ComingAdd,AlterntaiveAdd,DaysEnter,DaysFristOrders,dateTlabia,itemcode,itemname,SafySales,Quantity_Available,Quantity_Coming,price3,lastPrices1,lastPrices,supname,SupplierPrices,SupplierName,Required_Quantity ,supnamemin,LastSaleDate from #tempItemsSuppliers DECLARE @ColumnName AS NVARCHAR(MAX) SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(SupplierName ) FROM (SELECT DISTINCT SupplierName FROM #tempShowItemsSuppliers ) AS Weeks DECLARE @query NVARCHAR(max)=' select * from #tempShowItemsSuppliers pivot ( sum ([SupplierPrices]) for [SupplierName] in ('+@ColumnName+')) as MaxIncomePerDay ' execute(@query); end ALTER proc [dbo].[UpdateAutomationOrderAllBranchs_2021] --0,120,0,1,1,1,1,1,1 @TotalDaysX1 int=0, @TotalDaysX2 int=0, @BranchNo int=0, @YearNo int, -- 1 is Table2019 --- 2 is Table2020 @UserId int =1, @ClassId int, @ShowResult int=0, @QantityCome int =0 , @IsAlaternative int=0 as begin create Table #tempReortTaliba ( [id] int identity(1,1) not null, [ItemID] numeric(18, 0) null , [ItemCode] nvarchar(150) null, [ItemName] nvarchar(350) null, [TotalSales] decimal(18, 5) null, [TotalRetern] decimal(18, 5) null, [SafySales] decimal(18, 5) null, [Quantity_Available] decimal(18, 5) null, [lastPrices] decimal(18, 5) null, [lastPrices1] decimal(18, 5) null, [MinDateOrder] datetime null, [Required_Quantity] decimal(18, 5) null, [Branch_No] nvarchar(150) null , [LassPrice] decimal(18, 5) null, [OverPrice] decimal(18, 5) null , [ClassID] int null , [ClassName] nvarchar(150) null, [Quantity_Coming] decimal(18, 5) null, [TotalQantity] decimal(18, 5) null, [price3] decimal(18, 5) null, [HaveAlternative] int ) if(@YearNo=1 and @BranchNo!=0) begin set @TotalDaysX1= 365; insert into #tempReortTaliba([ItemID],[ItemCode],[ItemName],[TotalSales],[TotalRetern],[SafySales],[Branch_No]) SELECT DISTINCT(Data2023.dbo.items.ItemID),(Data2023.dbo.Items.ItemCode),Data2023.dbo.Items.ItemName, Isnull((select sum (Data2023.dbo.[OrderDetails].Quantity) from Data2023.dbo.[OrderDetails] where Data2023.dbo.[OrderDetails].ItemID=Data2023.dbo.Items.ItemID and Data2023.dbo.[OrderDetails].compid=@BranchNo),0) as TotalSales, Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= Data2023.dbo.Items.ItemID and Data2023.dbo.OrdersReturnDetails.compid=@BranchNo),0) as TotalRetern, Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= Data2023.dbo.Items.ItemID and Data2023.dbo.OrderDetails.compid=@BranchNo),0) - Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= Data2023.dbo.Items.ItemID and Data2023.dbo.OrdersReturnDetails.compid=@BranchNo),0) as SafySales,@BranchNo FROM Data2023.dbo.Items left JOIN Data2023.dbo.OrderDetails ON Data2023.dbo.Items.ItemID = Data2023.dbo.OrderDetails.ItemID AND Data2023.dbo.OrderDetails.compid=@BranchNo UPDATE T1 SET itemid = T2.itemid, [ItemName] = T2.itemname, [lastPrices]=T2.[SmallLastPrice], [ClassID]=T2.classid FROM #tempReortTaliba T1 JOIN dbo.items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS --select top 10 * from items where [ConvertedItemID] is not null delete from #tempReortTaliba where [ClassID] !=[ClassID] DELETE t1 FROM #tempReortTaliba t1 INNER JOIN items t2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS WHERE t2.[ConvertedItemID] is not null DELETE t1 FROM #tempReortTaliba t1 where ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS not in (select ItemCode from items) UPDATE #tempReortTaliba SET [Quantity_Available] = (select (sum([ItemQuantity].CurrentBalance) + sum([ItemQuantity].OpeningBalance)) from [ItemQuantity] where [ItemQuantity].itemid=#tempReortTaliba.itemid and #tempReortTaliba.[Branch_No] = [ItemQuantity].compid) UPDATE #tempReortTaliba SET [LassPrice] =Isnull((select min ([OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= #tempReortTaliba.ItemID and [dbo].[OrderDetails].compid= #tempReortTaliba.[Branch_No]),0) UPDATE #tempReortTaliba SET [OverPrice] =Isnull((select max ([OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= #tempReortTaliba.ItemID and [dbo].[OrderDetails].compid= #tempReortTaliba.[Branch_No]),0) update Brch1 set Brch1.Required_Quantity = (ROUND((Brch1.SafySales/@TotalDaysX1)* @TotalDaysX2 - Brch1.Quantity_Available ,0)) From #tempReortTaliba Brch1 end -----------------------------------GET Date from Table 2018 if(@YearNo=2 and @BranchNo!=0) begin insert into #tempReortTaliba([ItemID],[ItemCode],[ItemName],[TotalSales],[TotalRetern],[SafySales],[Quantity_Available],[lastPrices] ,[MinDateOrder],[ClassName],[ClassID],LassPrice,OverPrice,[Branch_No]) SELECT DISTINCT(items.ItemID),(Items.ItemCode),Items.ItemName, Isnull((select sum ([OrderDetails].Quantity) from [OrderDetails] where [OrderDetails].ItemID=Items.ItemID and [OrderDetails].compid=@BranchNo),0) as TotalSales, Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= Items.ItemID and OrdersReturnDetails.compid=@BranchNo),0) as TotalRetern, Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= Items.ItemID and OrderDetails.compid=@BranchNo),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= Items.ItemID and OrdersReturnDetails.compid=@BranchNo),0) as SafySales , (select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where Items.ItemID = [ItemQuantity].ItemID and [ItemQuantity].compid=@BranchNo ) as Quantity_Available ,(items.SmallLastPrice) as lastPrices ,(SELECT min (orders.OrderDate) FROM orders) as MinOrderDate , (TblClass.ClassDesc) as ClassDesc, (TblClass.ClassID) as ClassID, Isnull((select min ([OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= dbo.Items.ItemID and [OrderDetails].compid=@BranchNo),0) as LassPrice,Isnull((select max ([dbo].[OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= dbo.Items.ItemID AND [OrderDetails].compid= @BranchNo),0) as OverPrice,@BranchNo FROM Items left JOIN OrderDetails ON Items.ItemID = OrderDetails.ItemID and OrderDetails.compid=@BranchNo inner join [ItemQuantity] ON Items.ItemID = [ItemQuantity].ItemID and [ItemQuantity].compid=@BranchNo inner join TblClass ON Items.ClassID = TblClass.ClassID delete from #tempReortTaliba where [ClassID] !=[ClassID] DELETE t1 FROM #tempReortTaliba t1 INNER JOIN items t2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS WHERE t2.[ConvertedItemID] is not null set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from #tempReortTaliba Brch),getdate()) AS DiffDate); update Brch1 set Brch1.Required_Quantity = (ROUND((Brch1.SafySales/@TotalDaysX1)* @TotalDaysX2 - Brch1.Quantity_Available ,0)) From #tempReortTaliba Brch1 end -------------------------------------- update all branches if(@YearNo=1 and @BranchNo =0) begin set @TotalDaysX1= 365; insert into #tempReortTaliba([ItemID],[ItemCode],[ItemName],[TotalSales],[TotalRetern],[SafySales],[Branch_No]) SELECT DISTINCT(Data2023.dbo.items.ItemID),(Data2023.dbo.Items.ItemCode),Data2023.dbo.Items.ItemName, Isnull((select sum (Data2023.dbo.[OrderDetails].Quantity) from Data2023.dbo.[OrderDetails] where Data2023.dbo.[OrderDetails].ItemID=Data2023.dbo.Items.ItemID and Data2023.dbo.[OrderDetails].compid=@BranchNo),0) as TotalSales, Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= Data2023.dbo.Items.ItemID and Data2023.dbo.OrdersReturnDetails.compid=@BranchNo),0) as TotalRetern, Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= Data2023.dbo.Items.ItemID and Data2023.dbo.OrderDetails.compid=@BranchNo),0) - Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= Data2023.dbo.Items.ItemID and Data2023.dbo.OrdersReturnDetails.compid=@BranchNo),0) as SafySales,@BranchNo FROM Data2023.dbo.Items left JOIN Data2023.dbo.OrderDetails ON Data2023.dbo.Items.ItemID = Data2023.dbo.OrderDetails.ItemID AND Data2023.dbo.OrderDetails.compid=@BranchNo --insert into #tempReortTaliba([ItemCode],[TotalSales],[TotalRetern],[SafySales],[Branch_No]) --select itemcode,[TotalSales],[TotalRetern],[SafySales],[Branch_No] from [dbo].[Sales2019] UPDATE T1 SET itemid = T2.itemid, [ItemName] = T2.itemname, [lastPrices]=T2.[SmallLastPrice], [ClassID]=T2.classid FROM #tempReortTaliba T1 JOIN dbo.items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS delete from #tempReortTaliba where [ClassID] !=[ClassID] DELETE t1 FROM #tempReortTaliba t1 INNER JOIN items t2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS WHERE t2.[ConvertedItemID] is not null DELETE t1 FROM #tempReortTaliba t1 where ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS not in (select ItemCode from items) UPDATE #tempReortTaliba SET [Quantity_Available] = (select (sum([ItemQuantity].CurrentBalance) + sum([ItemQuantity].OpeningBalance)) from [ItemQuantity] where [ItemQuantity].itemid=#tempReortTaliba.itemid) UPDATE #tempReortTaliba SET [LassPrice] =Isnull((select min ([OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= #tempReortTaliba.ItemID),0) UPDATE #tempReortTaliba SET [OverPrice] =Isnull((select max ([OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= #tempReortTaliba.ItemID),0) update Brch1 set Brch1.Required_Quantity = (ROUND((Brch1.SafySales/@TotalDaysX1)* @TotalDaysX2 - Brch1.Quantity_Available ,0)) From #tempReortTaliba Brch1 end if(@YearNo=2 and @BranchNo =0) begin insert into #tempReortTaliba([ItemID],[ItemCode],[ItemName],[TotalSales],[TotalRetern],[SafySales],[Quantity_Available],[lastPrices] ,[MinDateOrder],[ClassName],[ClassID],LassPrice,OverPrice,[Branch_No]) SELECT DISTINCT(items.ItemID),(Items.ItemCode),Items.ItemName, Isnull((select sum ([OrderDetails].Quantity) from [OrderDetails] where [OrderDetails].ItemID=Items.ItemID AND [OrderDetails].CompID!=4 ),0) as TotalSales, Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= Items.ItemID AND OrdersReturnDetails.CompID!=4 ),0) as TotalRetern, Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= Items.ItemID AND [OrderDetails].CompID!=4),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= Items.ItemID AND OrdersReturnDetails.CompID!=4 ),0) as SafySales ,(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where Items.ItemID = [ItemQuantity].ItemID ) as Quantity_Available ,(items.SmallLastPrice) as lastPrices ,(SELECT min (orders.OrderDate) FROM orders) as MinOrderDate , (TblClass.ClassDesc) as ClassDesc, (TblClass.ClassID) as ClassID, Isnull((select min ([OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= dbo.Items.ItemID AND [OrderDetails].CompID !=4 ),0) as LassPrice,Isnull((select max ([dbo].[OrderDetails].UnitPrice) from [dbo].[OrderDetails] where [dbo].[OrderDetails].ItemID= dbo.Items.ItemID AND [OrderDetails].CompID !=4),0) as OverPrice,@BranchNo FROM Items left JOIN OrderDetails ON Items.ItemID = OrderDetails.ItemID inner join [ItemQuantity] ON Items.ItemID = [ItemQuantity].ItemID inner join TblClass ON Items.ClassID = TblClass.ClassID delete from #tempReortTaliba where [ClassID] !=[ClassID] DELETE t1 FROM #tempReortTaliba t1 INNER JOIN items t2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS WHERE t2.[ConvertedItemID] is not null set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from #tempReortTaliba Brch),getdate()) AS DiffDate); update Brch1 set Brch1.Required_Quantity = (ROUND((Brch1.SafySales/@TotalDaysX1)* @TotalDaysX2 - Brch1.Quantity_Available ,0)) From #tempReortTaliba Brch1 end if(@QantityCome=1) begin UPDATE A SET [Quantity_Coming] = B.[Quantity_Available] FROM #tempReortTaliba A INNER JOIN (SELECT [Item_Code] , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY [Item_Code]) B ON A.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = B.[Item_Code] COLLATE SQL_Latin1_General_CP1_CI_AS end update #tempReortTaliba set [TotalQantity]=isnull([Quantity_Available],0)+ isnull ([Quantity_Coming],0) if(@YearNo=1) begin set @TotalDaysX1= 365; update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - Brch.[TotalQantity] ,0)) From #tempReortTaliba Brch end if(@YearNo=2) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from #tempReortTaliba),getdate()) AS DiffDate); update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - Brch.[TotalQantity] ,0)) From #tempReortTaliba Brch end if(@ShowResult =1) begin delete from #tempReortTaliba where (Required_Quantity <= 0 or Required_Quantity is null) end if(@ShowResult =2) begin delete from #tempReortTaliba where Required_Quantity > 0 end if (@IsAlaternative=1) begin create table #Tempitems ( id INT IDENTITY(1,1), itemMasterid int, itemMastercode nVarchar(250), itemParentid int, itemParentcode nVarchar(250), [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Coming] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) create table #Tempitems2 ( id INT IDENTITY(1,1), itemMasterid int, itemMastercode nVarchar(250), itemParentid int, itemParentcode nVarchar(250), [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Coming] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) DELETE FROM #tempReortTaliba WHERE (itemid IN (SELECT [ItemMasterID] FROM [dbo].[ItemParent])) DELETE FROM #tempReortTaliba WHERE (itemid IN (SELECT [ItemParentID] FROM [dbo].[ItemParent])) insert into #Tempitems(itemMasterid) select itemid from [Master_ItemAlternative] insert into #Tempitems2 (itemMasterid,itemParentid) select itemMasterid,itemParentid from [HasMasteritems] where itemparentid is not null DELETE t1 FROM #Tempitems t1 INNER JOIN items t2 ON T1.itemMasterid = T2.itemid WHERE t2.[ConvertedItemID] is not null DELETE t1 FROM #Tempitems2 t1 INNER JOIN items t2 ON T1.itemMasterid = T2.itemid WHERE t2.[ConvertedItemID] is not null DELETE t1 FROM #Tempitems2 t1 INNER JOIN items t2 ON T1.itemParentid = T2.itemid WHERE t2.[ConvertedItemID] is not null if(@YearNo=1 and @BranchNo<>0) BEGIN update #Tempitems set [SafySales]=(Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= #Tempitems.itemmasterid and Data2023.dbo.OrderDetails.compid=@BranchNo),0) - Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= #Tempitems.itemmasterid and Data2023.dbo.OrdersReturnDetails.compid=@BranchNo),0)),HasAlternative=1 update #Tempitems2 set [SafySales]=(Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= #Tempitems2.itemmasterid and Data2023.dbo.OrderDetails.compid=@BranchNo),0) - Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= #Tempitems2.itemmasterid and Data2023.dbo.OrdersReturnDetails.compid=@BranchNo),0)) ,HasAlternative=1 --UPDATE -- Table_A --SET -- Table_A.[SafySales] = Table_B.[SafySales], -- Table_A.HasAlternative=1 --FROM -- #Tempitems AS Table_A -- INNER JOIN [dbo].[Sales2019] AS Table_B -- ON Table_A.itemmasterid = Table_B.itemid -- where Table_B.[Branch_No] = @BranchNo --UPDATE -- Table_A --SET -- Table_A.[SafySales] = Table_B.[SafySales], -- Table_A.HasAlternative=1 --FROM -- #Tempitems2 AS Table_A -- INNER JOIN [dbo].[Sales2019] AS Table_B -- ON Table_A.itemmasterid = Table_B.itemid -- where Table_B.[Branch_No] = @BranchNo update #Tempitems set [lastPrices]=(select [lastPrices] from items where #Tempitems.itemmasterid=items.itemid) update #Tempitems set [Quantity_Available]=(select [OpeningBalance]+[CurrentBalance] from [dbo].[ItemQuantity] where #Tempitems.itemmasterid=[ItemQuantity].itemid and [ItemQuantity].compid=@BranchNo) update #Tempitems2 set [lastPrices]=(select [lastPrices] from items where #Tempitems2.itemParentid=items.itemid) update #Tempitems2 set [Quantity_Available]=(select [OpeningBalance]+[CurrentBalance] from [dbo].[ItemQuantity] where #Tempitems2.itemParentid=[ItemQuantity].itemid and [ItemQuantity].compid=@BranchNo) END if(@YearNo=2 and @BranchNo<>0) begin update #Tempitems set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems.itemmasterid and OrderDetails.compid=@BranchNo),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems.itemmasterid and OrdersReturnDetails.compid=@BranchNo),0)) update #Tempitems set [Quantity_Available]=(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where #Tempitems.itemmasterid = [ItemQuantity].ItemID and [ItemQuantity].compid=@BranchNo ) update #Tempitems set lastPrices=(select SmallLastPrice from items where items.itemid= #Tempitems.itemmasterid) update #Tempitems set [MinDateOrder]= (SELECT min (orders.OrderDate) FROM orders) update #Tempitems2 set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems2.itemParentid and OrderDetails.compid=1),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems2.itemParentid and OrdersReturnDetails.compid=1),0)) update #Tempitems2 set [Quantity_Available]=(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where #Tempitems2.itemParentid = [ItemQuantity].ItemID and [ItemQuantity].compid=1 ) update #Tempitems2 set lastPrices=(select SmallLastPrice from items where items.itemid= #Tempitems2.itemParentid) update #Tempitems2 set [MinDateOrder]= (SELECT min (orders.OrderDate) FROM orders) end if(@YearNo=1 and @BranchNo=0) begin update #Tempitems set [SafySales]=Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= #Tempitems.itemmasterid AND Data2023.dbo.OrderDetails.CompID!=4),0) - Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= #Tempitems.itemmasterid AND Data2023.dbo.OrdersReturnDetails.CompID!=4),0) update #Tempitems2 set [SafySales]=Isnull((select sum (Data2023.dbo.OrderDetails.Quantity) from Data2023.dbo.OrderDetails where Data2023.dbo.OrderDetails.ItemID= #Tempitems2.itemParentid AND Data2023.dbo.OrderDetails.CompID!=4),0) - Isnull((select sum (Data2023.dbo.OrdersReturnDetails.Quantity) from Data2023.dbo.OrdersReturnDetails where Data2023.dbo.OrdersReturnDetails.ItemID= #Tempitems2.itemParentid AND Data2023.dbo.OrdersReturnDetails.CompID!=4 ),0) --UPDATE Table_A --SET -- Table_A.[SafySales] = Table_B.[SafySales], -- Table_A.HasAlternative=1 --FROM #Tempitems as Table_A INNER JOIN --(SELECT itemid , -- SUM(isnull(SafySales,0)) AS [SafySales] -- FROM [dbo].[Sales2019] -- GROUP BY itemid) Table_B --ON Table_A.itemmasterid = Table_B.itemid --UPDATE Table_A --SET -- Table_A.[SafySales] = Table_B.[SafySales], -- Table_A.HasAlternative=1 --FROM #Tempitems2 as Table_A INNER JOIN --(SELECT itemid , -- SUM(isnull(SafySales,0)) AS [SafySales] -- FROM [dbo].[Sales2019] -- GROUP BY itemid) Table_B --ON Table_A.itemParentid = Table_B.itemid update #Tempitems set [Quantity_Available]=(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where #Tempitems.itemmasterid = [ItemQuantity].ItemID ) update #Tempitems set lastPrices=(select SmallLastPrice from items where items.itemid= #Tempitems.itemmasterid) update #Tempitems set [MinDateOrder]= (SELECT min (orders.OrderDate) FROM orders) update #Tempitems set HasAlternative=1 update #Tempitems2 set [Quantity_Available]=(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where #Tempitems2.itemParentid = [ItemQuantity].ItemID ) update #Tempitems2 set lastPrices=(select SmallLastPrice from items where items.itemid= #Tempitems2.itemParentid) update #Tempitems2 set HasAlternative=1 end if(@YearNo=2 and @BranchNo=0) begin update #Tempitems set [SafySales]=Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems.itemmasterid AND OrderDetails.CompID!=4),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems.itemmasterid AND OrdersReturnDetails.CompID!=4),0) update #Tempitems set [Quantity_Available]=(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where #Tempitems.itemmasterid = [ItemQuantity].ItemID ) update #Tempitems set lastPrices=(select SmallLastPrice from items where items.itemid= #Tempitems.itemmasterid) update #Tempitems set [MinDateOrder]= (SELECT min (orders.OrderDate) FROM orders) update #Tempitems set HasAlternative=1 update #Tempitems2 set [SafySales]=Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems2.itemParentid AND OrderDetails.CompID!=4),0) - Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems2.itemParentid AND OrdersReturnDetails.CompID!=4 ),0) update #Tempitems2 set [Quantity_Available]=(select sum([ItemQuantity].CurrentBalance) +sum([ItemQuantity].OpeningBalance) from [ItemQuantity] where #Tempitems2.itemParentid = [ItemQuantity].ItemID ) update #Tempitems2 set lastPrices=(select SmallLastPrice from items where items.itemid= #Tempitems2.itemParentid) update #Tempitems2 set [MinDateOrder]= (SELECT min (orders.OrderDate) FROM orders) update #Tempitems2 set HasAlternative=1 end if(@QantityCome=1) begin UPDATE A SET [Quantity_Coming] = B.[Quantity_Available] FROM #Tempitems A INNER JOIN (SELECT itemid , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY itemid) B ON A.itemMasterid = B.itemid UPDATE A SET [Quantity_Coming] = B.[Quantity_Available] FROM #Tempitems2 A INNER JOIN (SELECT itemid , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY itemid) B ON A.itemParentid = B.itemid update #Tempitems set Quantity_coming =0 where Quantity_coming is null update #Tempitems2 set Quantity_coming =0 where Quantity_coming is null update #Tempitems set [lastPrices] =0 where [lastPrices] is null update #Tempitems set [lastPrices2] =0 where [lastPrices2] is null update #Tempitems2 set [lastPrices] =0 where [lastPrices] is null update #Tempitems2 set [lastPrices2] =0 where [lastPrices2] is null UPDATE A SET A.[Quantity_Available] = A.[Quantity_Available] + B.[Quantity_Available], A.[SafySales] = A.[SafySales]+ B.[SafySales], A.[lastPrices] = A.[lastPrices] , A.[Quantity_Coming] = A.[Quantity_Coming] + B.[Quantity_Come], A.[lastPrices2] = CASE WHEN A.[lastPrices] = 0 then B.[LastPrice] WHEN B.[LastPrice] =0 THEN A.[lastPrices] WHEN A.[lastPrices] > B.[LastPrice] then B.[LastPrice] else A.[lastPrices] END FROM #Tempitems A INNER JOIN (SELECT x.itemMasterid, (select top 1 w.ItemParentid from #Tempitems2 w where w.lastPrices=( SELECT MIN(NULLIF(z.lastPrices, 0)) FROM #Tempitems2 z where z.itemMasterid=x.itemMasterid GROUP BY z.itemMasterid) and w.itemMasterid= x.itemMasterid) as itemParent , SUM(isnull(x.Quantity_Available,0)) AS [Quantity_Available], SUM(isnull(x.Quantity_Coming,0)) AS [Quantity_Come], SUM(isnull(x.SafySales,0)) AS [SafySales], MIN(NULLIF(x.lastPrices, 0)) AS [LastPrice] FROM #Tempitems2 x GROUP BY x.itemMasterid) B ON A.itemMasterid = B.itemMasterid INSERT INTO #tempReortTaliba(itemid) SELECT distinct itemMasterid FROM #Tempitems UPDATE T1 SET itemcode = T2.itemcode, itemname = T2.itemname FROM #tempReortTaliba T1 JOIN dbo.items T2 ON T1.itemid = T2.itemid UPDATE Table_A SET Table_A.SafySales = Table_B.SafySales, Table_A.Quantity_Available = Table_B.Quantity_Available, Table_A.lastPrices = Table_B.lastPrices, Table_A.MinDateOrder = Table_B.MinDateOrder, Table_A.[lastPrices1] = Table_B.lastPrices2, Table_A.[Quantity_Coming] = Table_B.[Quantity_Coming], Table_A.havealternative = 1 FROM #tempReortTaliba AS Table_A INNER JOIN #Tempitems AS Table_B ON Table_A.itemid = Table_B.itemMasterid if(@YearNo=1) begin set @TotalDaysX1= 365; update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - (isnull(Brch.Quantity_Available,0)+isnull(Brch.[Quantity_Coming],0)) ,0) ) From #tempReortTaliba Brch end if(@YearNo=2) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from #tempReortTaliba Brch),getdate()) AS DiffDate) update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - (isnull(Brch.Quantity_Available,0)+isnull(Brch.[Quantity_Coming],0)),0) ) From #tempReortTaliba Brch end if(@ShowResult =1) begin delete from #tempReortTaliba where ( Required_Quantity <= 0 or Required_Quantity is null) end if(@ShowResult =2) begin delete from #tempReortTaliba where Required_Quantity > 0 END end UPDATE T1 SET [price3] =T2.[SmallSellPrice3] FROM #tempReortTaliba T1 JOIN items T2 ON T1.itemid = T2.itemid truncate table [TbShowReport] insert into [TbShowReport] (id,[ItemCode],[ItemName],[SafySales],[Quantity_Available],[lastPrices] ,[MinDateOrder],[Required_Quantity],[lastPrices1],[HaveAlternative],[Price3],[Quantity_Coming],[TotalQantity]) select [id],[ItemCode] ,[ItemName] ,[SafySales] ,[Quantity_Available] ,[lastPrices] ,[MinDateOrder], [Required_Quantity],[lastPrices1] ,[HaveAlternative],[price3],[Quantity_Coming],[TotalQantity] from #tempReortTaliba create table #DeleteDubicate ( id int, ItemCode nvarchar(150), ItemName nvarchar(350), SafySales decimal(18, 5) , Quantity_Available decimal(18, 5) , lastPrices decimal(18, 5), MinDateOrder datetime null , Required_Quantity decimal(18, 5), lastPrices1 decimal(18, 5), HaveAlternative int , Price3 decimal(18, 5), Quantity_Coming decimal(18, 5), TotalQantity decimal(18, 5) ) insert into #DeleteDubicate (itemcode) select distinct [ItemCode] from TbShowReport UPDATE T1 SET ItemName =t2.[ItemName], SafySales=t2.SafySales, Quantity_Available=t2.Quantity_Available, [lastPrices]=t2.[lastPrices], [MinDateOrder]=t2.[MinDateOrder], [Required_Quantity]=t2.[Required_Quantity], [lastPrices1]=t2.[lastPrices1], [HaveAlternative]=t2.[HaveAlternative], [Price3]=t2.[Price3], [Quantity_Coming]=t2.[Quantity_Coming], [TotalQantity]=t2.[TotalQantity] FROM #DeleteDubicate T1 JOIN [dbo].TbShowReport T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS truncate table TbShowReport insert into [TbShowReport] (id,[ItemCode],[ItemName],[SafySales],[Quantity_Available],[lastPrices] ,[MinDateOrder],[Required_Quantity],[lastPrices1],[HaveAlternative],[Price3],[Quantity_Coming],[TotalQantity]) select [id],[ItemCode] ,[ItemName] ,[SafySales] ,[Quantity_Available] ,[lastPrices] ,[MinDateOrder], [Required_Quantity],[lastPrices1] ,[HaveAlternative],[price3],[Quantity_Coming],[TotalQantity] from #DeleteDubicate UPDATE T1 SET classid =T2.classid FROM TbShowReport T1 JOIN items T2 ON T1.itemcode = T2.itemcode delete from TbShowReport where classid !=@classid UPDATE T1 SET itemidPurches =T2.itemid FROM TbShowReport T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS --UPDATE TbShowReport --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] where TbShowReport.itemidPurches= [Data2023].[dbo].[PurchaseDetails].itemid ORDER BY ordersn DESC) -- UPDATE TbShowReport --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] where TbShowReport.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid ORDER BY ordersn DESC) --UPDATE T1 SET supid =T2.supplierid FROM TbShowReport T1 JOIN [Data2023].dbo.[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid UPDATE TbShowReport SET supid=( CASE WHEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=TbShowReport.itemidPurches ORDER BY OrderDate DESC) >0 THEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=TbShowReport.itemidPurches ORDER BY OrderDate DESC) ELSE (SELECT TOP 1 SupplierID FROM [Data2023].dbo.Purchases INNER JOIN [Data2023].dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE [Data2023].dbo.PurchaseDetails.ItemID=TbShowReport.itemidPurches ORDER BY OrderDate DESC) end ) UPDATE T1 SET supname =T2.[CustSuppName] FROM TbShowReport T1 JOIN [Data2023].[dbo].[CustSupp] T2 ON T1.supid =T2.[CustSuppId] select * from TbShowReport end update [dbo].[TbShowReport] set [YearName]=case when @YearNo= 2 then 'السنة الحالية' else 'السنة السابقة' end, ComingAdd=case when @QantityCome= 1 then 'نعم' else 'لا' end, AlterntaiveAdd= case when @IsAlaternative= 1 then 'نعم' else 'لا' end, DaysEnter=@TotalDaysX2, DaysFristOrders=@TotalDaysX1 end ALTER proc [dbo].[CompreSuppliersWithAlternativeItems_2021] --0,0,0,1 @TotalDaysX1 int=0, @TotalDaysX2 int=0, @YearNo int = 0 ,--- 0 as 2017 -- 1 as 2018 @IsComeQantity int =0 as BEGIN CREATE TABLE #temp (id INT IDENTITY(1,1),masterid INT) CREATE TABLE #tempshow (id INT IDENTITY(1,1),ItemParentID INT) create table #Tempitems ( id INT IDENTITY(1,1), itemmasterID INT , itemParentID INT, ItemCodeMaster [NVARCHAR](150), ItemCode [NVARCHAR](150), [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Come] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) create table #Tempitems2 ( id INT IDENTITY(1,1), itemmasterID INT, itemParentID INT, ItemCodeMaster [NVARCHAR](150), ItemCode [NVARCHAR](150) , [SafySales] [decimal](18, 5) NULL, [Quantity_Available] [decimal](18, 5) NULL, [Quantity_Come] [decimal](18, 5) NULL, [lastPrices] [decimal](18, 5) NULL, [lastPrices2] [decimal](18, 5) NULL, [MinDateOrder] [datetime] NULL, HasAlternative int ) INSERT INTO #Tempitems2(ItemCode) SELECT ItemCode FROM dbo.TbShowReportSuppliers ------------------------------------------------------------------------------- update ItemsName update TabSupplier set TabSupplier.itemmasterID=t2017.ItemID FROM #Tempitems2 TabSupplier inner join items t2017 ON TabSupplier .[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = t2017.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and t2017.[ConvertedItemID] is null --select * from #Tempitems2 --select * from #Tempitems2 DECLARE @i INT DECLARE @numrows INT DECLARE @RecordID INT DECLARE @ItemMaster INT DECLARE @ItemPerternt INT DECLARE @CheckItemPerternt INT DECLARE @ItemParntID numeric(18, 0) DECLARE @CheckItemMaster INT DECLARE @ID1 INT DECLARE @id INT SET @i = 1 SET @numrows = (SELECT COUNT(*) FROM #Tempitems2) IF @numrows > 0 WHILE (@i <= (SELECT MAX(id) FROM #Tempitems2)) BEGIN SET @ItemParntID = (SELECT ItemMasterID FROM #Tempitems2 WHERE id=@i) TRUNCATE TABLE #Temp TRUNCATE TABLE #tempshow INSERT INTO #temp(masterid) EXEC ViewItemParentproinsert @ItemParntID INSERT INTO #tempshow( ItemParentID )(SELECT masterid FROM #temp) DECLARE @count INT SET @count=1 DECLARE @countall INT SET @countall=(SELECT COUNT(*) FROM #temp) WHILE @count<=@countall BEGIN DECLARE @item INT SET @item=(SELECT masterid FROM #temp WHERE id=@count) INSERT INTO #tempshow ( ItemParentID ) EXEC ViewItemParentproinsert @item SET @count=@count+1 END INSERT INTO #Tempitems(itemmasterID,itemParentID) SELECT DISTINCT @ItemParntID as ItemID, #tempshow.ItemParentID FROM #tempshow WHERE #tempshow.ItemParentID<>@ItemParntID SET @i = @i + 1 PRINT @i END -------------------------------------------------------------update tempitems update TabSupplier set TabSupplier.ItemCode=t2017.ItemCode FROM #Tempitems TabSupplier inner join items t2017 ON TabSupplier.itemParentID = t2017.ItemID update TabSupplier set TabSupplier.ItemCodeMaster =t2018.ItemCode FROM #Tempitems TabSupplier inner join items t2018 ON TabSupplier.itemmasterID = t2018.ItemID ------------------------------------------------------------------------------- Update All Colums if(@YearNo=1) begin update #Tempitems set [SafySales]=(Isnull((select sum ([Data2023].dbo.OrderDetails.Quantity) from [Data2023].dbo.OrderDetails where [Data2023].dbo.OrderDetails.ItemID= #Tempitems.itemparentid AND [Data2023].dbo.OrderDetails.CompID !=4),0) -Isnull((select sum ([Data2023].dbo.OrdersReturnDetails.Quantity) from [Data2023].dbo.OrdersReturnDetails where [Data2023].dbo.OrdersReturnDetails.ItemID= #Tempitems.itemparentid AND [Data2023].dbo.OrdersReturnDetails.CompID!=4),0)) update #Tempitems set [lastPrices]= (select isnull([SmallLastPrice], 0) from items where #Tempitems.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS = items.[ItemCode] COLLATE SQL_Latin1_General_CP1_CI_AS and items.[ConvertedItemID] is null) update #Tempitems set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #Tempitems.itemparentid=[dbo].[ItemQuantity].itemid) end if(@YearNo=2) begin update #Tempitems set [lastPrices]= (select isnull([SmallLastPrice], 0) from items where #Tempitems.itemparentid=items.itemid) update #Tempitems set [Quantity_Available]= (select SUM(isnull(CurrentBalance,0)) + SUM(isnull(OpeningBalance,0)) from [ItemQuantity] where #Tempitems.itemparentid=[dbo].[ItemQuantity].itemid) update #Tempitems set [MinDateOrder]= (select min(orderdate) from orders) update #Tempitems set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Tempitems.itemparentid AND dbo.OrderDetails.CompID !=4),0) -Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Tempitems.itemparentid AND OrdersReturnDetails.CompID!=4),0)) end ------------------------------------------------------------------------------------------- update Qantity Come if(@IsComeQantity=1) begin UPDATE A SET [Quantity_Come] = B.[Quantity_Available] FROM #Tempitems A INNER JOIN (SELECT [Item_Code] , SUM(isnull([Item_Qantity],0)) AS [Quantity_Available] FROM [Automation_Coming] GROUP BY [Item_Code]) B ON A.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = B.[Item_Code] COLLATE SQL_Latin1_General_CP1_CI_AS end UPDATE A SET A.[Quantity_Available] = A.[Quantity_Available] + B.[Quantity_Available], A.[SafySales] = A.[SafySales]+ B.[SafySales], A.[lastPrices1] = B.[LastPrice] , A.[HaveAlternative]=1 FROM dbo.TbShowReportSuppliers A INNER JOIN (SELECT ItemCodeMaster,SUM(isnull(Quantity_Available,0)) AS [Quantity_Available], SUM(isnull(SafySales,0)) AS [SafySales], MIN(NULLIF(lastPrices, 0)) AS [LastPrice] FROM #Tempitems GROUP BY ItemCodeMaster) B ON A.ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS = B.ItemCodeMaster COLLATE SQL_Latin1_General_CP1_CI_AS UPDATE A SET A.[Quantity_Coming] = A.[Quantity_Coming] + B.[Quantity_Available] FROM dbo.TbShowReportSuppliers A INNER JOIN (SELECT ItemCodeMaster,SUM(IsNull([Quantity_Come],0) ) AS [Quantity_Available] FROM #Tempitems GROUP BY ItemCodeMaster) B ON A.ItemCode COLLATE SQL_Latin1_General_CP1_CI_AS = B.ItemCodeMaster COLLATE SQL_Latin1_General_CP1_CI_AS update [dbo].[TbShowReportSuppliers] set [Quantity_Available]=0 where [Quantity_Available] is null update [dbo].[TbShowReportSuppliers] set [Quantity_Coming]=0 where [Quantity_Coming] is null update [dbo].[TbShowReportSuppliers] set [TotalQantity]=isnull(isnull([Quantity_Available],0)+ isnull([Quantity_Coming],0),0) --------------------------------------------------------------------------------------- update Required_Quantity if(@YearNo=1) begin set @TotalDaysX1= 365; update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From [dbo].[TbShowReportSuppliers] Brch end if(@YearNo=2) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN( MinDateOrder) from [dbo].[TbShowReportSuppliers] Brch),getdate()) AS DiffDate); update Brch set Brch.Required_Quantity = (ROUND((Brch.SafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.TotalQantity,0) ,0) ) From [dbo].[TbShowReportSuppliers] Brch end select * from [TbShowReportSuppliers] create table #DeleteDubicate ( id int, ItemCode nvarchar(150), ItemName nvarchar(350), SafySales decimal(18, 5) , Quantity_Available decimal(18, 5) , Quantity_Come decimal(18, 5) , lastPrices decimal(18, 5) , MinDateOrder datetime , Required_Quantity decimal(18, 5), SupplierPrices decimal(18, 5) , SupplierName nvarchar(150), UserId int , lastPrices1 decimal(18, 5) , HaveAlternative int , itemid bigint ) insert into #DeleteDubicate (itemcode) select distinct [ItemCode] from [dbo].[TbShowReportSuppliers] UPDATE T1 SET ItemName =t2.ItemName, SafySales=t2.SafySales, Quantity_Available=t2.Quantity_Available, Quantity_Come=t2.Quantity_Coming, lastPrices=t2.lastPrices, MinDateOrder=t2.MinDateOrder, Required_Quantity=t2.Required_Quantity, SupplierPrices=t2.SupplierPrices, SupplierName =t2.SupplierName, UserId=t2.UserId, lastPrices1=t2.lastPrices1, HaveAlternative=t2.HaveAlternative, itemid=t2.itemid FROM #DeleteDubicate T1 JOIN [dbo].[TbShowReportSuppliers] T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS truncate table [dbo].[TbShowReportSuppliers] insert into [dbo].[TbShowReportSuppliers] (ItemCode,ItemName,SafySales,Quantity_Available,lastPrices,MinDateOrder,Required_Quantity, SupplierPrices,SupplierName,UserId,lastPrices1,HaveAlternative,Quantity_Coming,itemid) select ItemCode,ItemName,SafySales,Quantity_Available,lastPrices,MinDateOrder,Required_Quantity, SupplierPrices,SupplierName,UserId,lastPrices1,HaveAlternative,Quantity_Come,itemid from #DeleteDubicate UPDATE T1 SET itemid =T2.itemid FROM [dbo].[TbShowReportSuppliers] T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS and t2.[ConvertedItemID] is null UPDATE T1 SET [price3] =T2.[SmallSellPrice3] FROM [dbo].[TbShowReportSuppliers] T1 JOIN items T2 ON T1.itemid = T2.itemid UPDATE T1 SET itemidPurches =T2.itemid FROM TbShowReportSuppliers T1 JOIN items T2 ON T1.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS = T2.itemcode COLLATE SQL_Latin1_General_CP1_CI_AS ------------------------------------------------ update Supllier from 2022 --UPDATE TbShowReportSuppliers --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] where -- TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- ORDER BY ordersn DESC) -- UPDATE TbShowReportSuppliers SET supyear=1 WHERE lastPurches IS NOT NULL -- UPDATE TbShowReportSuppliers --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] where TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid ORDER BY ordersn DESC) --UPDATE T1 SET supid =T2.supplierid FROM TbShowReportSuppliers T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid AND ISNULL (supyear,0)=1 ------------------------------------------------------- update suplier from 2021 --UPDATE TbShowReportSuppliers --SET -- lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] where -- TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid -- AND ISNULL (supyear,0)!=1 -- ORDER BY ordersn DESC) -- UPDATE TbShowReportSuppliers SET supyear=0 WHERE ISNULL (supyear,0) !=1 -- UPDATE TbShowReportSuppliers --SET -- compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] where TbShowReportSuppliers.itemidPurches=[Data2023].[dbo].[PurchaseDetails].itemid and ISNULL (supyear,0)=0 -- ORDER BY ordersn DESC) --UPDATE T1 SET supid =T2.supplierid FROM TbShowReportSuppliers T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid AND ISNULL (supyear,0)=0 UPDATE TbShowReportSuppliers SET supid=( CASE WHEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=TbShowReportSuppliers.Itemid ORDER BY OrderDate DESC) >0 THEN (SELECT TOP 1 SupplierID FROM dbo.Purchases INNER JOIN dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE dbo.PurchaseDetails.ItemID=TbShowReportSuppliers.Itemid ORDER BY OrderDate DESC) ELSE (SELECT TOP 1 SupplierID FROM [Data2023].dbo.Purchases INNER JOIN [Data2023].dbo.PurchaseDetails ON PurchaseDetails.CompID = Purchases.CompID AND PurchaseDetails.OrderSn = Purchases.OrderSn WHERE [Data2023].dbo.PurchaseDetails.ItemID=TbShowReportSuppliers.Itemid ORDER BY OrderDate DESC) end ) UPDATE T1 SET supname =T2.[CustSuppName] FROM TbShowReportSuppliers T1 JOIN [Data2023].[dbo].[CustSupp] T2 ON T1.supid =T2.[CustSuppId] update [dbo].[TbShowReportSuppliers] SET LastSaleDate= (SELECT MAX(OrderDate) FROM dbo.Orders INNER JOIN OrderDetails on dbo.Orders.OrderSn=OrderDetails.OrderSn AND dbo.Orders.CompID=OrderDetails.CompID AND [TbShowReportSuppliers].Itemid=OrderDetails.ItemID) update [dbo].[TbShowReportSuppliers] set [YearName]=case when @YearNo= 1 then 'السنة السابقة' else 'السنة الحالية' end, ComingAdd=case when @IsComeQantity= 1 then 'نعم' else 'لا' end, AlterntaiveAdd= 'نعم', DaysEnter=@TotalDaysX2, DaysFristOrders=@TotalDaysX1, dataorder=getdate() DROP TABLE #temp DROP TABLE #Tempitems2 DROP TABLE #Tempitems DROP TABLE #tempshow drop table #DeleteDubicate END ALTER PROCEDURE [dbo].[Get_Taliba_Items_new] --'22401-ed815',1,2,120 @ItemCode [nvarchar](max) = Null, @compid int , @YearNo INT , @TotalDaysX2 INT -- 1 oldyear -- 2 newyerr AS BEGIN CREATE TABLE #Finaltemp ( id INT IDENTITY (1,1), itemcode NVARCHAR(MAX), itemname NVARCHAR(MAX), itemid BIGINT , price3 DECIMAL(18,2), lastprice DECIMAL(18,2), BranchesQantity DECIMAL(18,2), BranchesQantityAlterntaive DECIMAL(18,2), ComimgQantity DECIMAL(18,2), ComimgQantityAlternative DECIMAL(18,2), SafyQantity DECIMAL(18,2), SafySales DECIMAL(18,2), TotalSafySales DECIMAL(18,2), RquetQantity DECIMAL(18,2), supname NVARCHAR(max), supid INT, lastPurches INT, compid int ) CREATE TABLE #Fintemp ( id INT IDENTITY (1,1), itemid BIGINT , itemid2 BIGINT , BranchesQantityAlterntaive DECIMAL(18,2), SafySales DECIMAL(18,2) ) INSERT INTO #Finaltemp ( itemcode, itemid, itemname, price3, lastprice, BranchesQantity )(SELECT ItemCode ,ItemID,ItemName ,SmallSellPrice3, SmallLastPrice ,dbo.GetItemSmallBalance__AllStore_Taliba(ItemID) AS toalqantitybaranches FROM View_Items where isnull(ConvertedItemID,0)=0 and ItemCode=@ItemCode) DECLARE @id bigint SET @id= (SELECT TOP 1 itemid FROM #Finaltemp) CREATE TABLE #temp (id INT IDENTITY(1,1),masterid INT) CREATE TABLE #tempshow (id INT IDENTITY(1,1),ItemParentID INT) INSERT INTO #temp ( masterid ) EXEC ViewItemParentproinsert @id INSERT INTO #tempshow ( ItemParentID ) (SELECT masterid FROM #temp) DECLARE @count INT SET @count=1 DECLARE @countall INT SET @countall=(SELECT COUNT(*) FROM #temp) WHILE @count<=@countall BEGIN DECLARE @item INT SET @item=(SELECT masterid FROM #temp WHERE id=@count) INSERT INTO #tempshow ( ItemParentID ) EXEC ViewItemParentproinsert @item SET @count=@count+1 END INSERT INTO #temp ( masterid )(SELECT DISTINCT ItemParentID FROM #tempshow) SET @count=1 SET @countall=(SELECT COUNT(*) FROM #temp) WHILE @count<=@countall BEGIN SET @item=(SELECT masterid FROM #temp WHERE id=@count) INSERT INTO #tempshow ( ItemParentID ) EXEC ViewItemParentproinsert @item SET @count=@count+1 END INSERT INTO #temp ( masterid )(SELECT DISTINCT ItemParentID FROM #tempshow) SET @count=1 SET @countall=(SELECT COUNT(*) FROM #temp) WHILE @count<=@countall BEGIN SET @item=(SELECT masterid FROM #temp WHERE id=@count) INSERT INTO #tempshow ( ItemParentID ) EXEC ViewItemParentproinsert @item SET @count=@count+1 END INSERT INTO #Fintemp ( itemid ,itemid2,BranchesQantityAlterntaive) SELECT DISTINCT @id as ItemID , #tempshow.ItemParentID, (SELECT ISNULL(SUM(CurrentBalance + OpeningBalance), 0) AS Expr1 FROM dbo.ItemQuantity WHERE (ItemID = #tempshow.ItemParentID)) AS AllStoreCurrentBalance FROM dbo.Items INNER JOIN #tempshow ON dbo.Items.ItemID = #tempshow.ItemParentID WHERE #tempshow.ItemParentID<>@id if(@YearNo=1) begin UPDATE #Fintemp SET SafySales=(Isnull((select sum ([Data2023].dbo.OrderDetails.Quantity) from [Data2023].dbo.OrderDetails where [Data2023].dbo.OrderDetails.ItemID= #Fintemp.itemid2 AND [Data2023].dbo.OrderDetails.CompID !=4),0) -Isnull((select sum ([Data2023].dbo.OrdersReturnDetails.Quantity) from [Data2023].dbo.OrdersReturnDetails where [Data2023].dbo.OrdersReturnDetails.ItemID= #Fintemp.itemid2 AND [Data2023].dbo.OrdersReturnDetails.CompID!=4),0)) update #Finaltemp set [SafySales]=(Isnull((select sum ([Data2023].dbo.OrderDetails.Quantity) from [Data2023].dbo.OrderDetails where [Data2023].dbo.OrderDetails.ItemID= #Finaltemp.itemid AND [Data2023].dbo.OrderDetails.CompID !=4),0) -Isnull((select sum ([Data2023].dbo.OrdersReturnDetails.Quantity) from [Data2023].dbo.OrdersReturnDetails where [Data2023].dbo.OrdersReturnDetails.ItemID= #Finaltemp.itemid AND [Data2023].dbo.OrdersReturnDetails.CompID!=4),0)) end if(@YearNo=2) begin UPDATE #Fintemp SET SafySales=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Fintemp.itemid2 AND dbo.OrderDetails.CompID !=4),0) -Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Fintemp.itemid2 AND OrdersReturnDetails.CompID!=4),0)) update #Finaltemp set [SafySales]=(Isnull((select sum (OrderDetails.Quantity) from OrderDetails where OrderDetails.ItemID= #Finaltemp.itemid AND dbo.OrderDetails.CompID !=4),0) -Isnull((select sum (OrdersReturnDetails.Quantity) from OrdersReturnDetails where OrdersReturnDetails.ItemID= #Finaltemp.itemid AND OrdersReturnDetails.CompID!=4),0)) end UPDATE dbo.Automation_Coming SET Itemid=(SELECT Itemid FROM items WHERE Item_Code=items.ItemCode) UPDATE #Finaltemp SET ComimgQantity= (SELECT ISNULL(SUM(Item_Qantity),0) FROM dbo.Automation_Coming WHERE Item_Code=@ItemCode) UPDATE #Finaltemp SET BranchesQantityAlterntaive=(SELECT SUM(BranchesQantityAlterntaive) FROM #Fintemp) UPDATE #Finaltemp SET ComimgQantityAlternative=(SELECT ISNULL(SUM(Item_Qantity),0) FROM dbo.Automation_Coming WHERE itemid IN (SELECT itemid2 FROM #Fintemp )) UPDATE #Finaltemp SET SafyQantity=(ISNULL(BranchesQantityAlterntaive,0) + ISNULL(BranchesQantity,0) + ISNULL(ComimgQantity,0) + ISNULL(ComimgQantityAlternative,0)) UPDATE #Finaltemp SET TotalSafySales= ((SELECT SUM(SafySales) FROM #Fintemp) + ISNULL(#Finaltemp.SafySales,0)) DECLARE @orderdate DATE SET @orderdate=(select min(orderdate) from orders) DECLARE @TotalDaysX1 INT if(@YearNo=1) begin set @TotalDaysX1= 365; update Brch set Brch.RquetQantity = (ROUND((Brch.TotalSafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.SafyQantity,0) ,0) ) From #Finaltemp Brch end if(@YearNo=2) begin set @TotalDaysX1= (SELECT DATEDIFF(day,(select MIN(Brch.OrderDate) from orders Brch),getdate()) AS DiffDate); update Brch set Brch.RquetQantity = (ROUND((Brch.TotalSafySales/@TotalDaysX1)* @TotalDaysX2 - isnull(Brch.SafyQantity,0) ,0) ) From #Finaltemp Brch end UPDATE #Finaltemp SET lastPurches = (SELECT TOP 1 ordersn FROM [Data2023].[dbo].[PurchaseDetails] where #Finaltemp.Itemid=[Data2023].[dbo].[PurchaseDetails].itemid ORDER BY ordersn DESC) UPDATE #Finaltemp SET compid = (SELECT TOP 1 compid FROM [Data2023].[dbo].[PurchaseDetails] where #Finaltemp.itemid=[Data2023].[dbo].[PurchaseDetails].itemid ORDER BY ordersn DESC) UPDATE T1 SET T1.supid =T2.supplierid FROM #Finaltemp T1 JOIN [Data2023].[dbo].[Purchases] T2 ON T1.lastPurches =T2.ordersn and T1.compid =T2.compid UPDATE T1 SET T1.supname =T2.[CustSuppName] FROM #Finaltemp T1 JOIN [Data2023].[dbo].[CustSupp] T2 ON T1.supid =T2.[CustSuppId] --SELECT * FROM #Finaltemp --SELECT * FROM #Fintemp SELECT itemcode AS 'الكود' ,itemname AS ' الاسم', TotalSafySales AS ' المبيعات',price3 AS 'سعر3',lastprice AS 'أقل تكلفة ', BranchesQantity AS 'كميات الصنف',BranchesQantityAlterntaive AS 'كميات البدائل' , ComimgQantity AS ' قادمة',ComimgQantityAlternative AS ' قادمة (بدائل)' ,SafyQantity AS 'صافى الكميات',RquetQantity AS ' مقترحة' ,supname 'مورد' FROM #Finaltemp END
| ver. 1.4 |
Github
|
.
| PHP 7.3.33 | Generation time: 0.1 |
proxy
|
phpinfo
|
Settings