/****** Object: StoredProcedure [dbo].[WebFeatureSyncWorker] Script Date: 07/06/2020 15:20:26 ******/ /* Creates Stored Procedures for TempData database */ ------- Change this <[TEMPLINK]> to the intermediate link between tempdata and NickMid databases. USE [TempData] GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'WebFeatureSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[WebFeatureSyncWorker] PRINT 'WebFeatureSyncWorker Droped' END GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'CustomerSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[CustomerSyncWorker] PRINT 'CustomerSyncWorker Droped' END Go IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'VisitorSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[VisitorSyncWorker] PRINT 'VisitorSyncWorker Droped' END Go IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'BookSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[BookSyncWorker] PRINT 'BookSyncWorker Droped' END Go IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'BookGroupSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[BookGroupSyncWorker] PRINT 'BookGroupSyncWorker Droped' END Go IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'BookGroup2SyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[BookGroup2SyncWorker] PRINT 'BookGroup2SyncWorker Droped' END SET ANSI_NULLS ON GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'StoreSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[StoreSyncWorker] PRINT 'StoreSyncWorker Droped' END GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'UnitSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[UnitSyncWorker] PRINT 'UnitSyncWorker Droped' END Go IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sanaditemsSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[sanaditemsSyncWorker] PRINT 'sanaditemsSyncWorker Droped' END GO IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'sanaditemsSyncWorker') AND type IN ( N'P', N'PC' ) ) BEGIN DROP PROCEDURE [dbo].[sanaditemsSyncWorker] PRINT 'sanaditemsSyncWorker Droped' END GO Go USE [TempData] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[WebFeatureSyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY SET XACT_ABORT ON BEGIN TRANSACTION DECLARE @Code bigint DECLARE dbcursor CURSOR FOR SELECT ID FROM TempData.dbo.WebFeatures WHERE ISNULL(IsDeleted, 0) = 0; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @Code; WHILE @@FETCH_STATUS = 0 BEGIN IF (ISNULL((SELECT TOP 1 ID FROM [TEMPLINK].[NickMid].dbo.WebFeatures WHERE FeatureID = @Code),0) <> 0) INSERT INTO [TEMPLINK].[NickMid].dbo.WebFeatures ( FeatureID, FeatureName, IsActive ) SELECT FeatureID, FeatureName, IsActive FROM TempData.dbo.WebFeatures WHERE ID = @Code IF (ISNULL((SELECT TOP 1 ID FROM [TEMPLINK].[NickMid].dbo.WebFeatures WHERE FeatureID = @Code),0) = 0) UPDATE A SET A.IsActive = B.IsActive FROM [TEMPLINK].[NickMid].dbo.WebFeatures A INNER JOIN TempData.dbo.WebFeatures B ON A.FeatureID = B.FeatureID WHERE B.ID = @Code UPDATE TempData.dbo.WebFeatures SET IsDeleted = 'True' WHERE ID = @Code FETCH NEXT FROM dbcursor INTO @Code; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'WebFeatures', 16, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END COMMIT END TRY BEGIN CATCH ROLLBACK INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync failed', 2, GETDATE(), 'WebFeatures', 16, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO USE [TempData] GO /****** Object: StoredProcedure [dbo].[CustomerSyncWorker] Script Date: 07/06/2020 15:20:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CustomerSyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY --SET XACT_ABORT ON --BEGIN TRANSACTION DECLARE @Code bigint, @ID bigint DECLARE dbcursor CURSOR FOR SELECT customercode, ID FROM TempData.dbo.tblcustomerinf WHERE ISNULL(IsDeleted, 0) = 0 Order BY Id Desc; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @Code, @ID; WHILE @@FETCH_STATUS = 0 BEGIN IF (ISNULL((SELECT TOP 1 Code FROM [TEMPLINK].[NickMid].Product.CustomerInfo WHERE Code = @Code),0) <> 0) INSERT INTO [TEMPLINK].[NickMid].[dbo].[tblcustomerinf] ( customerName, customerCode, customerAddress, customerPhone, customerfax, customerZipCode, prefix, city, comments, manager, nationalcode, economiccode, groupcode, credit, lastbuyinvoiceno, lastbuydate, lastsellinvoiceno, lastselldate, generalledger, date, email, hesabtafzil, CheckCredit, VisitorCode, SMS, Mobile, Pay, Recive, WebShopLoginStatus, WebShopDescription, WebShopSyncDocs, ActionTypeID ) SELECT customerName, customerCode, customerAddress, customerPhone, customerfax, customerZipCode, prefix, city, comments, manager, nationalcode, economiccode, groupcode, credit, lastbuyinvoiceno, lastbuydate, lastsellinvoiceno, lastselldate, generalledger, date, email, hesabtafzil, CheckCredit, VisitorCode, SMS, Mobile, Pay, Recive, WebShopLoginStatus, WebShopDescription, WebShopSyncDocs, 2 FROM TempData.dbo.tblcustomerinf WHERE customercode = @Code AND ID = @ID IF (ISNULL((SELECT TOP 1 Code FROM [TEMPLINK].[NickMid].Product.CustomerInfo WHERE Code = @Code),0) = 0) INSERT INTO [TEMPLINK].[NickMid].dbo.tblcustomerinf ( customerName, customerCode, customerAddress, customerPhone, customerfax, customerZipCode, prefix, city, comments, manager, nationalcode, economiccode, groupcode, credit, lastbuyinvoiceno, lastbuydate, lastsellinvoiceno, lastselldate, generalledger, date, email, hesabtafzil, CheckCredit, VisitorCode, SMS, Mobile, Pay, Recive, WebShopLoginStatus, WebShopDescription, WebShopSyncDocs, ActionTypeID ) SELECT customerName, customerCode, customerAddress, customerPhone, customerfax, customerZipCode, prefix, city, comments, manager, nationalcode, economiccode, groupcode, credit, lastbuyinvoiceno, lastbuydate, lastsellinvoiceno, lastselldate, generalledger, date, email, hesabtafzil, CheckCredit, VisitorCode, SMS, Mobile, Pay, Recive, WebShopLoginStatus, WebShopDescription, WebShopSyncDocs, 1 FROM TempData.dbo.tblcustomerinf WHERE customercode = @Code AND ID = @ID UPDATE TempData.dbo.tblcustomerinf SET IsDeleted = 'True' WHERE ID <= @ID and customerCode=@Code and ISNULL(IsDeleted,0)=0 FETCH NEXT FROM dbcursor INTO @Code, @ID; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'Customer', 11, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END --COMMIT END TRY BEGIN CATCH --ROLLBACK INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync failed', 2, GETDATE(), 'Customer', 11, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO USE [TempData] GO /****** Object: StoredProcedure [dbo].[VisitorSyncWorker] Script Date: 07/06/2020 15:20:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[VisitorSyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY SET XACT_ABORT ON BEGIN TRANSACTION DECLARE @Code bigint DECLARE dbcursor CURSOR FOR SELECT ID FROM TempData.dbo.tbl_Visitor WHERE ISNULL(IsDeleted, 0) = 0; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @Code; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [TEMPLINK].[NickMid].dbo.tbl_Visitor ( VisitorCode, VisitorName, VisitorPercent, VisitorHesab, ExpenseHesab ) SELECT VisitorCode, VisitorName, VisitorPercent, VisitorHesab, ExpenseHesab FROM TempData.dbo.tbl_Visitor WHERE ID = @Code UPDATE TempData.dbo.tbl_Visitor SET IsDeleted = 'True' WHERE ID = @Code FETCH NEXT FROM dbcursor INTO @Code; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'Visitor', 15, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END COMMIT END TRY BEGIN CATCH ROLLBACK INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync failed', 2, GETDATE(), 'Visitor', 15, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO USE [TempData] GO /****** Object: StoredProcedure [dbo].[BookSyncWorker] Script Date: 07/06/2020 15:20:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[BookSyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY --SET XACT_ABORT ON --BEGIN TRANSACTION DECLARE @S_Code bigint, @ID bigint, @ActionTypeId int DECLARE dbcursor CURSOR FOR SELECT s_code, ID,ActionTypeID FROM TempData.dbo.tblbookinf WHERE ISNULL(IsDeleted, 0) = 0 and WebShop_SPrice is not null Order by Id desc; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @S_Code, @ID,@ActionTypeId; WHILE @@FETCH_STATUS = 0 BEGIN IF (ISNULL((SELECT TOP 1 SCode FROM [TEMPLINK].[NickMid].Product.Products WHERE SCode = @S_Code),0) <> 0) INSERT INTO [TEMPLINK].[NickMid].[dbo].[tblbookinf] ( s_code, bookname, barcode, bookcode, lastsdate, sprice, weight, pages, pubyear, safetystock, discount, translator, subdiscount, lastbnumber, lastbdate, groupcode, writer, covertype, status, lastseller, publisher, publishercode, buytype, currentdate, bprice, pubtime, printcount, covercount, location, isbn, Tax1, groupcode2, Tax2, image_address, select_, UnitID, lastBPrice, WebShop_Description, WebShop_CartLimit, WebShop_SellStatus, WebShop_SPrice, WebShop_Discount, WebShop_SafetyStock, ActionTypeID ) SELECT s_code, REPLACE(REPLACE(bookname,N'ي',N'ی'),N'ك',N'ک'), barcode, bookcode, lastsdate, sprice, weight, pages, pubyear, safetystock, discount, translator, subdiscount, lastbnumber, lastbdate, groupcode, writer, covertype, status, lastseller, publisher, publishercode, buytype, currentdate, bprice, pubtime, printcount, covercount, location, isbn, Tax1, groupcode2, Tax2, image_address, select_, UnitID, lastBPrice, WebShop_Description, WebShop_CartLimit, WebShop_SellStatus, WebShop_SPrice, WebShop_Discount, WebShop_SafetyStock, case Isnull(@ActionTypeId,0) when 0 then 2 ELSE @ActionTypeId END FROM TempData.dbo.tblbookinf WHERE s_code = @S_Code AND ID = @ID IF (ISNULL((SELECT TOP 1 SCode FROM [TEMPLINK].[NickMid].Product.Products WHERE SCode = @S_Code),0) = 0) INSERT INTO [TEMPLINK].[NickMid].[dbo].[tblbookinf] ( s_code, bookname, barcode, bookcode, lastsdate, sprice, weight, pages, pubyear, safetystock, discount, translator, subdiscount, lastbnumber, lastbdate, groupcode, writer, covertype, status, lastseller, publisher, publishercode, buytype, currentdate, bprice, pubtime, printcount, covercount, location, isbn, Tax1, groupcode2, Tax2, image_address, select_, UnitID, lastBPrice, WebShop_Description, WebShop_CartLimit, WebShop_SellStatus, WebShop_SPrice, WebShop_Discount, WebShop_SafetyStock, ActionTypeID ) SELECT s_code, REPLACE(REPLACE(bookname,N'ي',N'ی'),N'ك',N'ک'), barcode, bookcode, lastsdate, sprice, weight, pages, pubyear, safetystock, discount, translator, subdiscount, lastbnumber, lastbdate, groupcode, writer, covertype, status, lastseller, publisher, publishercode, buytype, currentdate, bprice, pubtime, printcount, covercount, location, isbn, Tax1, groupcode2, Tax2, image_address, select_, UnitID, lastBPrice, WebShop_Description, WebShop_CartLimit, WebShop_SellStatus, WebShop_SPrice, WebShop_Discount, WebShop_SafetyStock, 1 FROM TempData.dbo.tblbookinf WHERE s_code = @S_Code AND ID = @ID UPDATE TempData.dbo.tblbookinf SET IsDeleted = 'True' WHERE ID <= @ID and s_code=@S_Code and ISNULL(IsDeleted, 0) = 0 FETCH NEXT FROM dbcursor INTO @S_Code, @ID,@ActionTypeId; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@S_Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'Book', 10, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END END TRY BEGIN CATCH DECLARE @Message nvarchar(500) SELECT @Message=ERROR_MESSAGE() INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES(@Message, 2, GETDATE(), 'Book', 10, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO USE [TempData] GO /****** Object: StoredProcedure [dbo].[BookGroupSyncWorker] Script Date: 07/06/2020 15:20:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[BookGroupSyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY SET XACT_ABORT ON BEGIN TRANSACTION DECLARE @Code bigint DECLARE dbcursor CURSOR FOR SELECT ID FROM TempData.dbo.tblBookGroup WHERE ISNULL(IsDeleted, 0) = 0; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @Code; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [TEMPLINK].[NickMid].dbo.tblbookgroup ( GroupCode, GroupName, ActionTypeId ) SELECT GroupCode, GroupName, ActionTypeID FROM TempData.dbo.tblbookgroup WHERE ID = @Code UPDATE TempData.dbo.tblBookGroup SET IsDeleted = 'True' WHERE ID = @Code FETCH NEXT FROM dbcursor INTO @Code; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'BookGroup1', 13, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END COMMIT END TRY BEGIN CATCH ROLLBACK INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync failed', 2, GETDATE(), 'BookGroup1', 13, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO USE [TempData] GO /****** Object: StoredProcedure [dbo].[BookGroup2SyncWorker] Script Date: 07/06/2020 15:20:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[BookGroup2SyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY SET XACT_ABORT ON BEGIN TRANSACTION DECLARE @Code bigint DECLARE dbcursor CURSOR FOR SELECT ID FROM TempData.dbo.tblBookGroup2 WHERE ISNULL(IsDeleted, 0) = 0; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @Code; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [TEMPLINK].[NickMid].dbo.tblBookGroup2 ( GroupCode, GroupName ,ActionTypeId ) SELECT GroupCode, GroupName, ActionTypeID FROM TempData.dbo.tblBookGroup2 WHERE ID = @Code UPDATE TempData.dbo.tblBookGroup2 SET IsDeleted = 'True' WHERE ID = @Code FETCH NEXT FROM dbcursor INTO @Code; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'BookGroup2', 14, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END COMMIT END TRY BEGIN CATCH ROLLBACK INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync failed', 2, GETDATE(), 'BookGroup2', 14, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO USE [TempData] GO /****** Object: StoredProcedure [dbo].[StoreSyncWorker] Script Date: 07/06/2020 15:20:28 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[sanaditemsSyncWorker] AS BEGIN SET NOCOUNT ON; DECLARE @Feature bit IF EXISTS(select ID from [TempData].[dbo].[WebFeatures] where FeatureName='B2B' and IsActive=1) SET @Feature=1 IF(ISNULL(@Feature,0)=1) BEGIN BEGIN TRY DECLARE @sanad bigint, @ID bigint, @ActionTypeId int DECLARE dbcursor CURSOR FOR SELECT sanad, Id,ActionTypeID FROM TempData.dbo.tbl_sanaditems WHERE ISNULL(IsDeleted, 0) = 0; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @sanad, @ID,@ActionTypeId; WHILE @@FETCH_STATUS = 0 BEGIN IF (ISNULL((SELECT TOP 1 sanad FROM [TEMPLINK].[NickMid].[dbo].[tbl_sanaditems] WHERE sanad = @sanad),0) =0 and @ActionTypeId=1) BEGIN INSERT INTO [TEMPLINK].[NickMid].[dbo].[tbl_sanaditems] ([sanad] ,[hesabtafzil] ,[sharh2] ,[bedehkar] ,[bestankar] ,[molhaghat] ,[recno] ,[kol] ,[hesabmoin] ,[s_code_check] ,[type_note] ,[sharh1] ,[record_number] ,[sanaddate] ) SELECT [sanad] ,[hesabtafzil] ,[sharh2] ,[bedehkar] ,[bestankar] ,[molhaghat] ,[recno] ,[kol] ,[hesabmoin] ,[s_code_check] ,[type_note] ,[sharh1] ,[record_number] ,[sanaddate] FROM [TempData].[dbo].[tbl_sanaditems] where Id=@ID UPDATE TempData.[dbo].[tbl_sanaditems] SET IsDeleted = 'True' WHERE ID = @ID END --RECORD EXIST ELSE IF (ISNULL((SELECT TOP 1 sanad FROM [TEMPLINK].[NickMid].[dbo].[tbl_sanaditems] WHERE sanad = @sanad),0) <> 0) BEGIN --update IF(@ActionTypeId=1) BEGIN update [TEMPLINK].[NickMid].[dbo].[tbl_sanaditems] SET [hesabtafzil]=d.[hesabtafzil] ,[sharh2]=d.[sharh2] ,[bedehkar]=d.[bedehkar] ,[bestankar]=d.[bestankar] ,[molhaghat]=d.[molhaghat] ,[kol]=d.[kol] ,[hesabmoin]=d.[hesabmoin] ,[s_code_check]=d.[s_code_check] ,[type_note]=d.[type_note] ,[sharh1]=d.[sharh1] ,[record_number] =d.[record_number] ,[sanaddate]=d.[sanaddate] FROM [TEMPLINK].[NickMid].[dbo].[tbl_sanaditems] I JOIN [TempData].[dbo].[tbl_sanaditems] d on I.recno=d.recno where d.Id=@ID END END --Delete IF(@ActionTypeId=3) BEGIN Delete [TEMPLINK].[NickMid].[dbo].[tbl_sanaditems] where sanad=@sanad END UPDATE TempData.[dbo].[tbl_sanaditems] SET IsDeleted = 'True' WHERE ID = @ID FETCH NEXT FROM dbcursor INTO @sanad, @ID,@ActionTypeId; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@sanad,0) =0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'tbl_sanaditems', 13, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END END TRY BEGIN CATCH Declare @Message nvarchar(max) set @Message=ERROR_MESSAGE() INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES(@Message, 2, GETDATE(), 'tbl_sanaditems', 13, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID(N'[dbo].[tblInvoiceSyncWorker]', 'P') IS NOT NULL DROP PROCEDURE [dbo].[tblInvoiceSyncWorker] GO CREATE PROCEDURE [dbo].[tblInvoiceSyncWorker] AS BEGIN DECLARE @Feature bit IF EXISTS(select ID from [TempData].[dbo].[WebFeatures] where FeatureName='B2B' and IsActive=1) SET @Feature=1 IF(ISNULL(@Feature,0)=1) BEGIN SET NOCOUNT ON; BEGIN TRY DECLARE @invoiceNo bigint, @ID bigint, @ActionTypeId int DECLARE dbcursor CURSOR FOR SELECT invoiceNo, Id,ActionTypeID FROM TempData.dbo.tblInvoice WHERE ISNULL(IsDeleted, 0) = 0; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @invoiceNo, @ID,@ActionTypeId; WHILE @@FETCH_STATUS = 0 BEGIN IF (ISNULL((SELECT TOP 1 invoiceNo FROM [TEMPLINK].[NickMid].[dbo].[tblInvoice] WHERE invoiceNo = @invoiceNo),0) =0 and @ActionTypeId=1) BEGIN INSERT INTO [TEMPLINK].[NickMid].[dbo].[tblInvoice] ([InvoiceNo] ,[InvoiceDate] ,[customerCode] ,[Discount] ,[Quantity] ,[SpecialDiscount] ,[InvoiceRealPrice] ,[expense] ,[invoicetype] ,[expensecomment] ,[cashiercode] ,[cardexinvoicetype] ,[sanad_no] ,[sellercode] ,[close_] ,[sharh_invoice] ,[image_address] ,[t_invoiceno] ,[dbName] ,[Tax1] ,[Tax2] ,[InvoiceTime] ,[VisitorCode] ,[Pos_] ,[RealDate] ) SELECT [InvoiceNo] ,[InvoiceDate] ,[customerCode] ,[Discount] ,[Quantity] ,[SpecialDiscount] ,[InvoiceRealPrice] ,[expense] ,[invoicetype] ,[expensecomment] ,[cashiercode] ,[cardexinvoicetype] ,[sanad_no] ,[sellercode] ,[close_] ,[sharh_invoice] ,[image_address] ,[t_invoiceno] ,[dbName] ,[Tax1] ,[Tax2] ,[InvoiceTime] ,[VisitorCode] ,[Pos_] ,[RealDate] FROM [TempData].[dbo].[tblInvoice] where Id=@ID INSERT INTO [TEMPLINK].[NickMid].[dbo].[tblInvoiceItems] ([InvoiceNo] ,[Quantity] ,[quantityback] ,[Discount] ,[SPrice] ,[bprice] ,[RecNo] ,[backinvoiceno] ,[buyfrom] ,[s_code_book] ,[s_code_store] ,[Tax1] ,[Tax2] ) SELECT [InvoiceNo] ,[Quantity] ,[quantityback] ,[Discount] ,[SPrice] ,[bprice] ,[RecNo] ,[backinvoiceno] ,[buyfrom] ,[s_code_book] ,[s_code_store] ,[Tax1] ,[Tax2] FROM [dbo].[tblInvoiceItems] where InvoiceNo=@invoiceNo UPDATE TempData.[dbo].[tblInvoice] SET IsDeleted='true' where id=@ID UPDATE TempData.[dbo].[tblInvoiceItems] SET IsDeleted = 'True' WHERE InvoiceNo=@invoiceNo END --RECORD EXIST ELSE IF (ISNULL((SELECT TOP 1 invoiceNo FROM [TEMPLINK].[NickMid].[dbo].[tblInvoice] WHERE invoiceNo = @invoiceNo),0)<> 0) BEGIN --update IF(@ActionTypeId=1) BEGIN update [TEMPLINK].[NickMid].[dbo].[tblInvoice] SET [InvoiceDate]=d.[InvoiceDate] ,[customerCode]=d.[customerCode] ,[Discount]=d.[Discount] ,[Quantity]=d.[Quantity] ,[SpecialDiscount]=d.[SpecialDiscount] ,[InvoiceRealPrice]=d.[InvoiceRealPrice] ,[expense]=d.[expense] ,[invoicetype]=d.[invoicetype] ,[expensecomment]=d.[expensecomment] ,[cashiercode]=d.[cashiercode] ,[cardexinvoicetype]=d.[cardexinvoicetype] ,[sanad_no]=d.[sanad_no] ,[sellercode]=d.[sellercode] ,[close_]=d.[close_] ,[sharh_invoice]=d.[sharh_invoice] ,[image_address]=d.[image_address] ,[t_invoiceno]=d.[t_invoiceno] ,[dbName]=d.[dbName] ,[Tax1]=d.[Tax1] ,[Tax2]=d.[Tax2] ,[InvoiceTime]=d.[InvoiceTime] ,[VisitorCode]=d.[VisitorCode] ,[Pos_]=d.[Pos_] ,[RealDate]=d.[RealDate] FROM [TEMPLINK].[NickMid].[dbo].[tblInvoice] I JOIN [TempData].[dbo].[tblInvoice] d on I.InvoiceNo=d.InvoiceNo where id=@id update [TEMPLINK].[NickMid].[dbo].[tblInvoiceItems] SET [Quantity]=d.[Quantity] ,[quantityback]=d.[quantityback] ,[Discount]=d.[Discount] ,[SPrice]=d.[SPrice] ,[bprice]=d.[bprice] ,[backinvoiceno]=d.[backinvoiceno] ,[buyfrom]=d.[buyfrom] ,[s_code_book]=d.[s_code_book] ,[s_code_store]=d.[s_code_store] ,[Tax1]=d.[Tax1] ,[Tax2]=d.[Tax2] FROM [TEMPLINK].[NickMid].[dbo].[tblInvoiceItems] I JOIN [TempData].[dbo].[tblInvoiceItems] d on I.RecNo=d.RecNo where d.InvoiceNo=@invoiceNo END END --Delete IF(@ActionTypeId=3) BEGIN Delete [TEMPLINK].[NickMid].[dbo].[tblInvoice] where InvoiceNo=@invoiceNo DELETE [TEMPLINK].[NickMid].[dbo].[tblInvoiceItems] where InvoiceNo=@invoiceNo END UPDATE TempData.[dbo].[tblInvoice] SET IsDeleted = 'True' WHERE ID = @ID update TempData.[dbo].[tblInvoiceItems] SET IsDeleted = 'True' where InvoiceNo=@invoiceNo FETCH NEXT FROM dbcursor INTO @invoiceNo, @ID,@ActionTypeId; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@invoiceNo,0) =0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'invoice', 14, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END END TRY BEGIN CATCH Declare @Message nvarchar(max) set @Message=ERROR_MESSAGE() INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES(@Message, 2, GETDATE(), 'invoice', 14, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END END GO IF OBJECT_ID(N'[dbo].[StoreSyncWorker]', 'P') IS NOT NULL DROP PROCEDURE [dbo].[StoreSyncWorker] GO CREATE PROCEDURE [dbo].[StoreSyncWorker] AS BEGIN SET NOCOUNT ON; BEGIN TRY DECLARE @S_Code bigint, @ID bigint DECLARE dbcursor CURSOR FOR SELECT ID,s_code_book FROM TempData.dbo.tblstore WHERE ISNULL(IsDeleted, 0) = 0 Order By ID ; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @ID,@S_Code; WHILE @@FETCH_STATUS = 0 BEGIN IF (ISNULL((SELECT TOP 1 SCode FROM [NickMid].Product.ProductStores WHERE SCode = @S_Code),0) <> 0) BEGIN INSERT INTO [NickMid].[dbo].[tblstore] ( s_code_store, s_code_book, quantity, ActionTypeID ) SELECT s_code_store, s_code_book, quantity, 2 FROM TempData.dbo.tblstore WHERE s_code_book = @S_Code AND ID = @ID UPDATE TempData.dbo.tblstore SET IsDeleted = 'True' WHERE ID = @ID END FETCH NEXT FROM dbcursor INTO @ID,@S_Code; END; CLOSE dbcursor; DEALLOCATE dbcursor; IF (ISNULL(@S_Code,0) <> 0) BEGIN INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES('Sync sucessfuly completed', 1, GETDATE(), 'Store', 12, @@ROWCOUNT) PRINT 'Sync successful' PRINT GETDATE() END END TRY BEGIN CATCH Declare @Message nvarchar(max) set @Message=ERROR_MESSAGE() INSERT INTO TempData.dbo.tblJobLog(Message, StatusTypeId, DateTime, Type, TypeId, Rows) VALUES(@Message, 2, GETDATE(), 'Store', 12, @@ROWCOUNT) PRINT 'Sync failed' PRINT GETDATE() END CATCH END GO
/* Creates Jobs */ USE [msdb] GO DECLARE @jobId_ binary(16) SELECT @jobId_ = job_id FROM msdb.dbo.sysjobs WHERE (name = N'NickMidSyncWorker') IF (@jobId_ IS NOT NULL) BEGIN EXEC msdb.dbo.sp_delete_job @jobId_ END /****** Object: Job [NickMidSyncWorker] Script Date: 8/24/2020 8:34:55 PM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 8/24/2020 8:34:55 PM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'NickMidSyncWorker', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Customer Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Customer Sync', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC CustomerSyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Book Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Book Sync', @step_id=2, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC BookSyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Store Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Store Sync', @step_id=3, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC StoreSyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BookGroup1 Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BookGroup1 Sync', @step_id=4, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC BookGroupSyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [BookGroup2 Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'BookGroup2 Sync', @step_id=5, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC BookGroup2SyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Visitor Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Visitor Sync', @step_id=6, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC VisitorSyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [sanaditems Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'sanaditems Sync', @step_id=7, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC [dbo].[sanaditemsSyncWorker]', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [invoice Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'invoice Sync', @step_id=8, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC [dbo].[tblInvoiceSyncWorker]', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [WebFeatures Sync] Script Date: 8/24/2020 8:34:56 PM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'WebFeatures Sync', @step_id=9, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC WebFeatureSyncWorker', @database_name=N'TempData', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Sync Schedule', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=15, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20200126, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'dbbdca9b-d920-4c62-ab23-8055c40e3a52' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO GO USE [msdb] GO DECLARE @jobId_ binary(16) SELECT @jobId_ = job_id FROM msdb.dbo.sysjobs WHERE (name = N'NickhesabAutoInvoice') IF (@jobId_ IS NOT NULL) BEGIN EXEC msdb.dbo.sp_delete_job @jobId_ END USE [msdb] GO /****** Object: Job [NickhesabAutoInvoice] Script Date: 8/28/2020 5:37:23 AM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 8/28/2020 5:37:23 AM ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'NickhesabAutoInvoice', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [InvoiceProcess] Script Date: 8/28/2020 5:37:23 AM ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'InvoiceProcess', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' ------------------------------------------ -- Declarations ------------------------------------------ DECLARE @CustomerCode bigint ,@CashierCode bigint ,@OwnerCode bigint ,@Store_SCode bigint ,@InvoiceNo bigint ,@InvoiceDate bigint ,@AutoInvoice bit ,@OrderId bigint ,@OrderCode nvarchar(max) ,@OrderDate nvarchar(max) ,@OrderTime nvarchar(max) ,@OwnerId bigint ,@OrderExpense bigint ,@OrderDiscount bigint ,@OrderQuantity bigint ,@OrderFinalPrice bigint ,@OrderTax1 bigint ,@OrderTax2 bigint ,@InvoiceDesc nvarchar(max) ,@SanadDesc nvarchar(max) ,@OwnerFName nvarchar(max) ,@OwnerLName nvarchar(max) ,@OwnerFullName nvarchar(max) ,@OwnerPhone nvarchar(max) ,@OrderItemSCode bigint ,@HesabTafzil1 bigint ,@HesabTafzil2 bigint ,@HesabTafzil3 bigint ,@HesabTafzil4 bigint ,@HesabTafzil5 bigint ,@HesabTafzil6 bigint ,@HesabTafzil7 bigint ,@HesabTafzilBack bigint ,@ERR bigint ,@PaymentType bigint ,@PaymentTypeName nvarchar(max) ,@Action bigint ,@IsSuccess bit ,@OrderType bigint ,@PaymentStatus bigint ,@IsDeleted bit ,@OrderState bigint ,@TempInvoiceNo bigint ,@SanadTypeID bigint ,@BookCode bigint ,@BookName nvarchar(max) ,@BPrice bigint ,@SPrice bigint ,@DiscountPercent bigint ,@Quantity bigint ,@Tax1 bigint ,@Tax2 bigint ,@CreatorUserId bigint ,@VisitorCode bigint ,@VCashierCode bigint BEGIN TRY SET XACT_ABORT ON BEGIN TRANSACTION -- ----------------------------------- -- Get order information from NickMid -- ----------------------------------- SELECT TOP 1 @OrderId = CAST(Id AS bigint) ,@OrderCode = CAST(OrderCode AS nvarchar(max)) ,@OrderDate = CheckOutDateTime ,@OrderTime = SUBSTRING(CAST(CAST(CheckOutDateTime AS time) AS nvarchar(max)), 0, 6) ,@OrderExpense = CAST(TotalDeliveryPrice AS bigint) ,@OrderFinalPrice = CAST(TotalRoundPrice AS bigint) ,@PaymentType = PaymentType ,@PaymentTypeName = CASE PaymentType WHEN 0 THEN N''آنلاین'' WHEN 1 THEN N''در محل'' WHEN 2 THEN N''از حساب کاربری'' WHEN 3 THEN N''کارت به کارت'' WHEN 4 THEN N''اعتباری'' END ,@Action = [Action] ,@IsSuccess = IsSuccess ,@IsDeleted = IsDeleted ,@OrderState = OrderState ,@TempInvoiceNo = Isnull(TempInvoiceNo,0) ,@PaymentStatus = PaymentStatus ,@OrderType = OrderType ,@CreatorUserId =CreatorUserId FROM [TEMPLINK].[NickMid].[Order].[Orders] where IsDeleted=0 IF(@OrderId IS NULL) OR (@OrderCode IS NULL) OR (@OrderId = '''') OR (@OrderCode = '''') BEGIN PRINT ''1. OrderID or OrderCode is not in true format'' ROLLBACK TRANSACTION RETURN END SELECT @OrderQuantity = SUM(Quantity) FROM [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId SET @OrderDate = dbo.fn_setdatetostringformat(@OrderDate) SELECT @OrderDate = shamsidate FROM fn_miladitoshamsi(@OrderDate) SELECT @OwnerId = OwnerUserId FROM [TEMPLINK].[NickMid].[Order].[OrderInformations] WHERE Id = @OrderId SELECT @OwnerFName = FirstName ,@OwnerLName = LastName ,@OwnerPhone = MobileNumber ,@OwnerCode = Code FROM [TEMPLINK].[NickMid].[User].[UserProfiles] WHERE Id = @OwnerId SET @OwnerFullName = @OwnerFName + '' '' + @OwnerLName SET @InvoiceDesc = REPLACE(REPLACE(N''فروش اینترنتی '' + @PaymentTypeName + N'' به شماره سفارش '' + CAST(@OrderCode AS nvarchar(max)) + N'' به نام '' + @OwnerFullName,N''ي'',N''ی''),N''ك'',N''ک'') SELECT @SanadDesc = PaymentMessage FROM [TEMPLINK].[NickMid].[Order].[PaymentLogs] WHERE OrderId = @OrderId -- ----------------------------------------- -- -- Get invoice information from Nickhesab -- ----------------------------------------- SELECT @CustomerCode = WebShopCustomerCode ,@CashierCode = WebShopCashierCode ,@Store_SCode = WebShopStore_SCode ,@SanadTypeID = ISNULL(WebShopSanadTypeID,1) ,@VCashierCode= ISNULL(WebShopVCashierCode,0) FROM dbo.tblconfigtable WHERE cashiercode = 10001 SET @InvoiceDate = CONVERT(bigint,Replace(@OrderDate,''/'','''')) SELECT @HesabTafzil1 = account from tbl_account where name = ''sell'' SELECT @HesabTafzil2 = account from tbl_account where name = ''buyprice'' SELECT @HesabTafzil3 = account from tbl_account where name = ''anbar'' SELECT @HesabTafzil4 = account from tbl_account where name = ''expensesell'' SELECT @HesabTafzil5 = account from tbl_account where name = ''sellkhadamat'' SELECT @HesabTafzil6 = account from tbl_account where name = ''task_sell'' SELECT @HesabTafzil7 = account from tbl_account where name = ''task_tax'' SELECT @HesabTafzilBack=account from tbl_account where name =''backsell'' --backsell ---- ////DELETE IF(@Action=3) BEGIN IF (@TempInvoiceNo<>0) BEGIN DELETE FROM dbo.tbltempInvoice WHERE InvoiceNo = @TempInvoiceNo END UPDATE [TEMPLINK].[NickMid].[Order].[Orders] SET IsDeleted = 1 where Id=@OrderId END -- ---------------------------------------------- -- //////// Action 2 (EDIT ORDER) //////// -- ---------------------------------------------- IF (@Action in(1,2) AND (@TempInvoiceNo <>0)) BEGIN IF (@OrderType =1) BEGIN DELETE FROM dbo.tbltempInvoiceItems WHERE InvoiceNo = @TempInvoiceNo DELETE FROM dbo.tbltempInvoice WHERE InvoiceNo = @TempInvoiceNo SET @Action = 1 SET @TempInvoiceNo = 0 UPDATE [TEMPLINK].[NickMid].[Order].[Orders] SET TempInvoiceNo = 0 where Id=@OrderId END END -- ---------------------------------------------- -- //////// Action 1 (INSERT ORDER) ///////// -- ////OrderType=1 B2C صورتحساب-- -- ///OrderType=2 visitor -- ///OrderType=3 B2B -- //OrderType=4 Refund -- //OrderType=5 Deleted -- ---------------------------------------------- IF (@Action in(1,2) AND @TempInvoiceNo=0 AND (@OrderType IN (1, 2, 3,4))) BEGIN -- Normal sell orders IF (@OrderType IN (1)) BEGIN EXEC invoice_temp_insert @InvoiceDate, @CustomerCode, @CashierCode, 0, 2, @InvoiceNo OUTPUT END IF (@PaymentType = 3) BEGIN UPDATE [TEMPLINK].[NickMid].[Order].[Orders] SET TempInvoiceNo = @InvoiceNo, IsDeleted=1 WHERE Id = @OrderId END -- Visitor sell orders IF (@OrderType IN (2)) BEGIN SELECT @VisitorCode= Code FROM [TEMPLINK].[NickMid].[User].[UserProfiles] WHERE Id= @CreatorUserId EXEC invoice_temp_insert @InvoiceDate, @OwnerCode, @VCashierCode, @VisitorCode,2, @InvoiceNo OUTPUT END -- -- B2B IF (@OrderType IN (3)) BEGIN EXEC previous_invoice_insert @InvoiceDate,@OwnerCode,@CashierCode,NULL, @InvoiceNo OUTPUT UPDATE [dbo].[tblpreviousInvoice] SET busy = 0 ,close_pre=''True'' WHERE InvoiceNo = @InvoiceNo DECLARE dbcursor CURSOR FOR SELECT SCode from [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @OrderItemSCode; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @BookCode = bookcode ,@BPrice = bprice FROM dbo.select_book2(@OrderItemSCode) SELECT @BookName = ProductName ,@Quantity = CAST(Quantity AS bigint) ,@DiscountPercent = CAST(Discount AS bigint) ,@SPrice = CAST(ProductPrice AS bigint) ,@Tax1 = CAST(Tax1 AS bigint) ,@Tax2 = CAST(Tax2 AS bigint) FROM [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId AND SCode = @OrderItemSCode INSERT INTO dbo.tblpreviousInvoiceItems(InvoiceNo ,BookCode ,bookname ,Quantity ,Discount ,SPrice ,bprice ,s_code_book ,s_code_store ,Tax1 ,Tax2) VALUES (@InvoiceNo ,@BookCode ,@BookName ,@Quantity ,@DiscountPercent ,@SPrice ,@BPrice ,@OrderItemSCode ,@Store_SCode ,@Tax1 ,@Tax2) FETCH NEXT FROM dbcursor INTO @OrderItemSCode; END; CLOSE dbcursor; DEALLOCATE dbcursor; UPDATE dbo.tblpreviousInvoice SET Discount = 0 ,Quantity = @OrderQuantity ,InvoiceRealPrice = @OrderFinalPrice WHERE InvoiceNo = @InvoiceNo END IF(@OrderType in (4)) BEGIN EXEC invoice_temp_insert @InvoiceDate, @CustomerCode, @CashierCode, 0, 5, @InvoiceNo OUTPUT END IF(@InvoiceNo IS NULL) OR (@InvoiceNo = '''') BEGIN PRINT ''2. An error occured while TempInvoice insertion'' ROLLBACK TRANSACTION RETURN END -------------------// Move order items of normal and Visitor orders IF (@OrderType IN (1)) BEGIN UPDATE tbltempInvoice SET busy = 0 WHERE InvoiceNo = @InvoiceNo DECLARE dbcursor CURSOR FOR SELECT SCode from [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @OrderItemSCode; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @BookCode = bookcode ,@BPrice = bprice FROM dbo.select_book2(@OrderItemSCode) SELECT @BookName = ProductName ,@Quantity = CAST(Quantity AS bigint) ,@DiscountPercent = CAST(Discount AS bigint) ,@SPrice = CAST(ProductPrice AS bigint) ,@Tax1 = CAST(Tax1 AS bigint) ,@Tax2 = CAST(Tax2 AS bigint) FROM [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId AND SCode = @OrderItemSCode INSERT INTO dbo.tbltempInvoiceItems(InvoiceNo ,BookCode ,bookname ,Quantity ,Discount ,SPrice ,bprice ,s_code_book ,s_code_store ,Tax1 ,Tax2) VALUES (@InvoiceNo ,@BookCode ,@BookName ,@Quantity ,@DiscountPercent ,@SPrice ,@BPrice ,@OrderItemSCode ,@Store_SCode ,@Tax1 ,@Tax2) FETCH NEXT FROM dbcursor INTO @OrderItemSCode; END; CLOSE dbcursor; DEALLOCATE dbcursor; UPDATE dbo.tbltempinvoice SET Discount = 0--@OrderDiscount ,expense = @OrderExpense ,expensecomment = ISNULL(@InvoiceDesc, '''') ,Quantity = @OrderQuantity ,InvoiceRealPrice = @OrderFinalPrice WHERE InvoiceNo = @InvoiceNo ----***paymentType=3==>cart2cart**--- END -------------------// Move order items of visitor orders IF (@OrderType IN (2)) BEGIN UPDATE dbo.tbltempInvoice SET busy = 0 WHERE InvoiceNo = @InvoiceNo DECLARE dbcursor CURSOR FOR SELECT SCode from [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @OrderItemSCode; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @BookCode = bookcode ,@BPrice = bprice FROM dbo.select_book2(@OrderItemSCode) SELECT @BookName = ProductName ,@Quantity = CAST(Quantity AS bigint) ,@DiscountPercent = CAST(Discount AS bigint) ,@SPrice = CAST(ProductPrice AS bigint) ,@Tax1 = CAST(Tax1 AS bigint) ,@Tax2 = CAST(Tax2 AS bigint) FROM [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId AND SCode = @OrderItemSCode INSERT INTO dbo.tbltempInvoiceItems(InvoiceNo ,BookCode ,bookname ,Quantity ,Discount ,SPrice ,bprice ,s_code_book ,s_code_store ,Tax1 ,Tax2) VALUES (@InvoiceNo ,@BookCode ,@BookName ,@Quantity ,@DiscountPercent ,@SPrice ,@BPrice ,@OrderItemSCode ,@Store_SCode ,@Tax1 ,@Tax2) FETCH NEXT FROM dbcursor INTO @OrderItemSCode; END; CLOSE dbcursor; DEALLOCATE dbcursor; UPDATE dbo.tbltempInvoice SET Discount = 0--@OrderDiscount ,Quantity = @OrderQuantity ,InvoiceRealPrice = @OrderFinalPrice WHERE InvoiceNo = @InvoiceNo END END -- ---------------------------------------------- -- //////// Sell payment successful ///////// -- ---------------------------------------------- IF (@IsDeleted = ''False'') AND (@IsSuccess = ''True'') AND (@PaymentStatus = 1) AND (@OrderType= 1) BEGIN IF @SanadDesc ='''' OR @SanadDesc is null SET @SanadDesc=@InvoiceDesc EXEC dbo.invoice_close @InvoiceNo, @InvoiceDate, @hesabtafzil1 ,@hesabtafzil2, @hesabtafzil3, @hesabtafzil4, @hesabtafzil5, @hesabtafzil6 ,@hesabtafzil7, 0, @OrderExpense, @InvoiceDesc, @SanadDesc, @SanadTypeID, @ERR OUTPUT UPDATE dbo.tblInvoice SET sharh_invoice = @InvoiceDesc WHERE InvoiceNo = @ERR SET @InvoiceNo=@ERR IF(@ERR < 10) BEGIN PRINT ''3. An error occured while Invoice insertion'' ROLLBACK TRANSACTION RETURN END END ---------------------------------------------- -- //////// Refund payment successful ///////// -- ---------------------------------------------- IF (@IsDeleted = ''False'') AND (@IsSuccess = ''True'') AND (@PaymentStatus = 1) AND (@OrderType=4) BEGIN Declare @backinvoiceno bigint; --GET OrderId Of Master OrderId from current refound description select @OrderCode=SUBSTRING(description, CHARINDEX(''{'', description) + 1, CHARINDEX(''}'', description) - CHARINDEX(''{'', description) - 1) from [TEMPLINK].[NickMid].[Order].[Orders] where Id=@OrderId IF (@OrderCode is null) BEGIN ROLLBACK TRANSACTION Print ''OrderId 4 has not any refrence'' END select @backinvoiceno=TempInvoiceNo from [TEMPLINK].[NickMid].[Order].[Orders] where Id=@OrderCode IF (@backinvoiceno is null) BEGIN ROLLBACK TRANSACTION PRINT ''Return Transacion there is no refrence'' END DECLARE dbcursor CURSOR FOR SELECT SCode from [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId; OPEN dbcursor FETCH NEXT FROM dbcursor INTO @OrderItemSCode; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Quantity=CAST(Quantity AS bigint) FROM [TEMPLINK].[NickMid].[Order].[OrderDetails] WHERE OrderId = @OrderId AND SCode = @OrderItemSCode EXEC [dbo].[invoice_back_items_NickWeb] @backinvoiceno,@InvoiceNo,@OrderItemSCode,@Quantity,@Store_SCode,@ERR OUTPUT FETCH NEXT FROM dbcursor INTO @OrderItemSCode; END; CLOSE dbcursor; DEALLOCATE dbcursor; EXEC [dbo].[invoice_back_items_close] @InvoiceNo,@InvoiceDate,0,@HesabTafzilBack,@HesabTafzil2,@HesabTafzil3,@HesabTafzil4,@HesabTafzil5,@HesabTafzil6,@CashierCode,@SanadTypeID,@ERR OUTPUT IF @SanadDesc ='''' OR @SanadDesc is null SET @SanadDesc =REPLACE(REPLACE(N''برگشت از فروش اینترنتی '' + @PaymentTypeName + N'' به شماره سفارش '' + CAST(@OrderCode AS nvarchar(max)) + N'' به نام '' + @OwnerFullName,N''ي'',N''ی''),N''ك'',N''ک'') UPDATE dbo.tblInvoice SET sharh_invoice = @SanadDesc WHERE InvoiceNo = @ERR IF(@ERR < 10) BEGIN PRINT ''3. An error occured while Invoice insertion'' ROLLBACK TRANSACTION RETURN END END -- ---------------------------------------------- -- //////// Backsell payment successful ///////// -- ---------------------------------------------- IF (@IsDeleted = ''False'') AND (@IsSuccess = ''True'') AND (@PaymentStatus = 1) BEGIN update [TEMPLINK].[NickMid].[Order].[Orders] set IsDeleted=1, DeletedDateTime=GetDate(), TempInvoiceNo=@InvoiceNo WHERE id= @OrderId END COMMIT TRANSACTION END TRY BEGIN CATCH declare @ErrorMessage nvarchar(500) select @ErrorMessage=ERROR_MESSAGE() PRINT @ErrorMessage+'' ''+''4. An error occured while invoice proccessing'' if (XACT_STATE())=1 Commit TRANSACTION; else RoLLBACK TRANSACTION; if(@@TRANCOUNT>1) ROLLBACK TRANSACTION THROW; END CATCH ', @database_name=N'nickhesab', @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'AutoInvoiceByOneMin', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=30, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20191023, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'10b27af8-0dcc-44f1-9877-59fe37d5ff9b' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO