· 4 years ago · Mar 18, 2021, 11:00 AM
1SET QUOTED_IDENTIFIER ON
2SET ANSI_NULLS ON
3GO
4IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'TVPIntPair' AND ss.name = N'Exc')
5CREATE TYPE [Exc].[TVPIntPair] AS TABLE(
6 [ValueFirst] [int] NULL,
7 [ValueSecond] [int] NULL,
8 [ValueThree] [int] NULL
9)
10GO
11SET QUOTED_IDENTIFIER ON
12SET ANSI_NULLS ON
13GO
14CREATE OR ALTER PROCEDURE [dbo].[up_ws_AutoCreateDZDocsSingleDoc]
15@SectorID INT, -- ИД. УЧАСТКА
16@de DATETIME, -- ДАТА ПОСТРОЕНИЯ ОТЧЕТА
17@AccPTRTId INT=NULL,
18@Acc31Id INT=NULL
19WITH ENCRYPTION
20AS
21SET NOCOUNT ON
22SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
23DECLARE @AccID INT
24
25DECLARE @MscNO_TR SMALLINT
26DECLARE @MscNO_PK SMALLINT
27
28SELECT @MscNO_TR = 11--MSC_NO FROM MISC WITH (NOLOCK) WHERE MSC_NAME = 'Торговый представитель' AND MSC_TYPE = -1
29SELECT @MscNO_PK = 13--MSC_NO FROM MISC WITH (NOLOCK) WHERE MSC_NAME = 'Вид продукции' AND MSC_TYPE = -1
30
31-- ============================================================================================================================
32-- СПИСОК ДОКУМЕНТОВ "ПРИВЯЗКА ТРТ К УЧАСТКУ И КАТЕГОРИИ"
33-- ============================================================================================================================
34IF(@AccPTRTId IS null)
35 SELECT @AccID = ACC_ID FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = 'ПТРТ'
36ELSE
37 SELECT @AccID = @AccPTRTId
38
39-- СПИСОК ДОКУМЕНТОВ "ПРИВЯЗКА ТРТ К УЧАСТКУ И КАТЕГОРИИ"
40SELECT DISTINCT m.MSC_ID AS SECTOR_ID, j.J_AG1 AS AG_ID, j.DOC_ID, j.J_DATE AS DOC_DATE
41INTO #RELATION_TABLE
42FROM JOURNAL j WITH (NOLOCK) LEFT JOIN JRN_MISC m WITH (NOLOCK) ON j.J_ID = m.J_ID AND m.MSC_NO = @MscNO_TR
43WHERE j.ACC_DB = @AccID AND j.J_TR_NO = 0 AND j.J_DATE < @de AND j.J_DONE = 2
44OPTION (MAXDOP 8)
45-- ============================================================================================================================
46-- СПИСОК КОРРЕСПОНДЕНТОВ
47-- ============================================================================================================================
48-- ТАБЛИЦА СО СПИСКОМ КОРРЕСПОНДЕНТОВ
49CREATE TABLE #AGENTS_TABLE (AG_ID INT, DOC_ID INT)
50
51-- СПИСОК КОРРЕСПОНДЕНТОВ
52INSERT INTO #AGENTS_TABLE (AG_ID)
53SELECT DISTINCT AG_ID
54FROM #RELATION_TABLE
55WHERE SECTOR_ID = @SectorID
56
57-- ИД. АКТУАЛЬНОГО ДОКУМЕНТА "ПРИВЯЗКА ТРТ К УЧАСТКУ И КАТЕГОРИИ"
58UPDATE a
59SET a.DOC_ID =
60 (
61 SELECT TOP 1 DOC_ID
62 FROM #RELATION_TABLE
63 WHERE AG_ID = a.AG_ID
64 ORDER BY DOC_DATE DESC, DOC_ID DESC
65 )
66FROM #AGENTS_TABLE a
67
68-- ============================================================================================================================
69-- ОПОРНАЯ ТАБЛИЦА
70-- ============================================================================================================================
71-- ОПОРНАЯ ТАБЛИЦА
72SELECT a.AG_ID, pk.MSC_ID AS PK_ID
73INTO #BASE_TABLE
74FROM #AGENTS_TABLE a INNER JOIN JOURNAL j WITH (NOLOCK) ON a.DOC_ID = j.DOC_ID
75 INNER JOIN JRN_MISC tr WITH (NOLOCK) ON j.J_ID = tr.J_ID AND tr.MSC_NO = @MscNO_TR
76 INNER JOIN JRN_MISC pk WITH (NOLOCK) ON j.J_ID = pk.J_ID AND pk.MSC_NO = @MscNO_PK
77WHERE j.J_TR_NO = 0 AND tr.MSC_ID = @SectorID
78
79-- ============================================================================================================================
80-- КОРРЕСПОНДЕНЦИЯ СЧЕТОВ
81-- ============================================================================================================================
82IF(@Acc31Id IS NULL)
83 SELECT @AccID = ACC_ID FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '31'
84ELSE
85 SELECT @AccID = @Acc31Id
86
87-- КОРРЕСПОНДЕНЦИЯ СЧЕТОВ
88-- ACC_TYPE: 1 - ОПЛАТА, 2 - РЕАЛИЗАЦИЯ, 3 - ВОЗВРАТ
89
90--CREATE TABLE #ACCOUNTS_TABLE(ACC_DB int, ACC_CR int, ACC_TYPE int)
91--IF NOT EXISTS(SELECT * FROM @TvpACCOUNTS_TABLE)
92--begin
93-- INSERT #ACCOUNTS_TABLE(ACC_DB, ACC_CR, ACC_TYPE)
94-- SELECT ACC_DB, ACC_CR, ACC_TYPE
95-- FROM dbo.upfn_AccountsList(@AccID)
96--END
97--ELSE
98--BEGIN
99-- INSERT #ACCOUNTS_TABLE(ACC_DB, ACC_CR, ACC_TYPE)
100-- SELECT ValueFirst,ValueSecond,ValueThree
101-- FROM @TvpACCOUNTS_TABLE
102--END
103
104-- КОРРЕСПОНДЕНЦИЯ СЧЕТОВ ПРЕДОПЛАТ (ВСТУПИТЕЛЬНЫЙ БАЛАНС)
105--CREATE TABLE #ACCOUNTS_PREPAY(ACC_DB int, ACC_CR int)
106
107--IF NOT EXISTS(SELECT* FROM @TvpACCOUNTS_PREPAY)
108--begin
109--INSERT #ACCOUNTS_PREPAY(ACC_DB,ACC_CR)
110--SELECT d.ACC_DB, c.ACC_CR
111--FROM
112-- (SELECT ACC_ID AS ACC_DB FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE IN ('36111', '36112', '36113', '36114', '36115')) d,
113-- (SELECT ACC_ID AS ACC_CR FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '0') c
114--UNION ALL
115--SELECT d.ACC_DB, c.ACC_CR
116--FROM
117-- (SELECT ACC_ID AS ACC_DB FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '03611') d,
118-- (SELECT ACC_ID AS ACC_CR FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '00') c
119--END
120--ELSE
121--BEGIN
122--INSERT #ACCOUNTS_PREPAY(ACC_DB,ACC_CR)
123--SELECT ValueFirst, ValueSecond FROM @TvpACCOUNTS_PREPAY
124--END
125-- ============================================================================================================================
126-- СПИСОК ОТГРУЗОК
127-- ============================================================================================================================
128SELECT b.AG_ID, b.PK_ID, j.DOC_ID, SUM(ISNULL(j.J_SUM, 0.0)) AS DOC_SUM, j.ACC_DB
129INTO #SALES_TABLE
130FROM JOURNAL j WITH (NOLOCK) INNER JOIN ##ACCOUNTS_TABLE a ON j.ACC_DB = a.ACC_DB AND j.ACC_CR = a.ACC_CR AND a.ACC_TYPE = 2
131 INNER JOIN JRN_MISC m WITH (NOLOCK) ON j.J_ID = m.J_ID AND m.MSC_NO = @MscNO_PK
132 INNER JOIN #BASE_TABLE b ON j.J_AG1 = b.AG_ID AND m.MSC_ID = b.PK_ID
133WHERE j.J_DATE < @de AND j.J_DONE = 2 AND ISNULL(j.J_SUM, 0.0) > 0.0
134GROUP BY b.AG_ID, b.PK_ID, j.DOC_ID, j.ACC_DB
135
136-- ============================================================================================================================
137-- ИНФОРМАЦИЯ О СВЯЗАННЫХ ОПЛАТАХ
138-- ============================================================================================================================
139SELECT a.PDOC_ID, SUM(a.PAY_SUM) AS PAY_SUM
140INTO #PAY_TABLE
141FROM
142 (
143 SELECT j.PDOC_ID, ISNULL(j.J_SUM, 0.0) AS PAY_SUM
144 FROM JOURNAL j WITH (NOLOCK) INNER JOIN ##ACCOUNTS_TABLE a ON j.ACC_DB = a.ACC_DB AND j.ACC_CR = a.ACC_CR AND a.ACC_TYPE = 1
145 INNER JOIN #SALES_TABLE s ON j.PDOC_ID = s.DOC_ID
146 WHERE j.J_DONE = 2
147 UNION ALL
148 SELECT j.PDOC_ID, ABS(ISNULL(j.J_SUM, 0.0)) AS PAY_SUM
149 FROM JOURNAL j WITH (NOLOCK) INNER JOIN ##ACCOUNTS_PREPAY a ON j.ACC_DB = a.ACC_DB AND j.ACC_CR = a.ACC_CR
150 INNER JOIN #SALES_TABLE s ON j.PDOC_ID = s.DOC_ID
151 WHERE j.J_DONE = 2 AND ISNULL(j.J_SUM, 0.0) < 0.0 AND j.J_TR_NO = 0
152 ) a
153GROUP BY a.PDOC_ID
154
155-- ============================================================================================================================
156-- ИНФОРМАЦИЯ О СВЯЗАННЫХ ВОЗВРАТАХ
157-- ============================================================================================================================
158SELECT j.PDOC_ID, SUM(ABS(ISNULL(j.J_SUM, 0.0))) AS RETURN_SUM
159INTO #RETURN_TABLE
160FROM JOURNAL j WITH (NOLOCK) INNER JOIN ##ACCOUNTS_TABLE a ON j.ACC_DB = a.ACC_DB AND j.ACC_CR = a.ACC_CR AND a.ACC_TYPE = 3
161 INNER JOIN #SALES_TABLE s ON j.PDOC_ID = s.DOC_ID
162WHERE ISNULL(j.J_SUM, 0.0) < 0.0 AND j.J_DONE = 2
163GROUP BY j.PDOC_ID
164
165-- ============================================================================================================================
166-- ПРЕДВАРИТЕЛЬНЫЙ РЕЗУЛЬТАТ
167-- ============================================================================================================================
168SELECT @AccID = ACC_ID FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '03611'
169
170-- ТАБЛИЦА С ПРЕДВАРИТЕЛЬНЫМ РЕЗУЛЬТАТОМ
171CREATE TABLE #RESULT_TABLE (AG_ID INT, PK_ID INT, DOC_ID INT, DOC_GUID UNIQUEIDENTIFIER, DOC_NO NVARCHAR(50), DOC_DATE DATETIME, DAY_DELAY SMALLINT, DATE_DELAY DATETIME,
172 DEBT_SUM MONEY)
173
174-- ПРЕДВАРИТЕЛЬНЫЙ РЕЗУЛЬТАТ
175INSERT INTO #RESULT_TABLE (AG_ID, PK_ID, DOC_ID, DOC_NO, DEBT_SUM)
176SELECT s.AG_ID, s.PK_ID, s.DOC_ID, CASE WHEN s.ACC_DB = @AccID THEN '[СФ]' ELSE '[РН]' END, s.DOC_SUM - ISNULL(p.PAY_SUM, 0.0) - ISNULL(r.RETURN_SUM, 0.0)
177FROM #SALES_TABLE s LEFT JOIN #PAY_TABLE p ON s.DOC_ID = p.PDOC_ID
178 LEFT JOIN #RETURN_TABLE r ON s.DOC_ID = r.PDOC_ID
179WHERE s.DOC_SUM - ISNULL(p.PAY_SUM, 0.0) - ISNULL(r.RETURN_SUM, 0.0) > 0.1
180
181-- ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ
182UPDATE #RESULT_TABLE SET DOC_GUID = d.DOC_GUID, DOC_NO = ISNULL(d.DOC_NO, '') + '/' + r.DOC_NO + '/' + ISNULL(m.MSC_TAG, ''), DOC_DATE = d.DOC_DATE,
183 DAY_DELAY = DATEDIFF(DAY, d.DOC_DATE, ISNULL(p.PRM_DATE, d.DOC_DATE)), DATE_DELAY = ISNULL(p.PRM_DATE, d.DOC_DATE)
184FROM #RESULT_TABLE r INNER JOIN DOCUMENTS d WITH (NOLOCK) ON r.DOC_ID = d.DOC_ID
185 LEFT JOIN DOC_PARAMS p WITH (NOLOCK) ON r.DOC_ID = p.DOC_ID AND p.PRM_ID = 1000
186 INNER JOIN MISC m WITH (NOLOCK) ON m.MSC_ID = r.PK_ID
187
188-- ============================================================================================================================
189-- РЕЗУЛЬТАТ
190-- ============================================================================================================================
191--SELECT COUNT(*) FROM #RESULT_TABLE
192
193-- РЕЗУЛЬТАТ
194SELECT r.AG_ID, r.DOC_GUID, r.DOC_NO, r.DOC_DATE, r.DAY_DELAY, r.PK_ID, r.DATE_DELAY, r.DEBT_SUM
195FROM #RESULT_TABLE r INNER JOIN AGENTS a WITH (NOLOCK) ON r.AG_ID = a.AG_ID
196ORDER BY a.AG_NAME, r.DOC_DATE, r.DOC_NO
197GO
198GRANT EXECUTE on dbo.up_ws_AutoCreateDZDocsSingleDoc TO ap_public
199GO
200
201-- exec dbo.up_ws_AutoCreateDZDocs 46625
202--exec dbo.up_ws_AutoCreateDZDocs 39147; --LG
203-- exec up_ws_AutoCreateDZDocs 44932
204--SELECT * FROM dbo.MISC AS m WHERE m.MSC_ID=39147
205CREATE OR ALTER PROCEDURE dbo.up_ws_AutoCreateDZDocs
206@rootStartGroupTrade int with encryption
207as
208set nocount on
209set transaction isolation level read UNCOMMITTED
210DECLARE @debug BIT=0
211DECLARE @curRowCount INT
212
213
214-- test datacount on account
215--DECLARE @accId int
216--SELECT @accId = ACC_ID FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = 'ПТРТ'
217--SELECT @accId AS ACC_ID, COUNT(*) AS DataCount FROM dbo.JOURNAL AS j WHERE j.ACC_DB = @accId AND j.J_TR_NO=0 AND j.J_DONE=2
218
219
220--if exists(select * from dbo.MISC_TREE as mt where p0=@rootStartGroupTrade and (ISNULL(p4,0)<>0 or ISNULL(p3,0)=0))
221--begin
222--RAISERROR (N'Неверный уровень аналитики', 16, 10)
223--return
224--end
225
226--declare @rootStartGroupTrade int
227--set @rootStartGroupTrade = 44932
228declare @date datetime = CAST(getdate() as date)
229
230declare @fldId int
231select top(1) @fldId = v.FLD_ID from dbo.FOLDERS as v where v.FLD_NAME='16.06. Дебиторская задолженность (экспорт)' OR v.FLD_GUID='45DC9147-4A5B-E311-9223-0030488DF6D6'
232declare @frmId int
233declare @docAutoNumId int
234select top(1) @frmId = v.FRM_ID, @docAutoNumId=v.FA_ID from dbo.FORMS as v where v.FRM_NAME='Экспорт: Дебиторская задолженность' OR v.FRM_GUID='FBB141D2-495B-E311-9223-0030488DF6D6'
235declare @tmlId int
236select top(1) @tmlId = v.TML_ID from dbo.TEMPLATES as v where v.TML_NAME='16.06. Дебиторская задолженность (экспорт)' OR v.TML_GUID='76611301-4A5B-E311-9223-0030488DF6D6'
237declare @accdb int
238select top(1) @accdb = v.ACC_ID from dbo.ACCOUNTS as v where v.ACC_CODE='ДЗ' OR v.ACC_GUID='40185DB2-C222-DF11-8DD5-0030483377C3'
239declare @acccr int
240select top(1) @acccr = v.ACC_ID from dbo.ACCOUNTS as v where v.ACC_CODE='00' OR v.ACC_GUID='3CB92629-2118-4B7F-9F86-F73DAFC59B2F'
241
242declare @curDocNo int
243
244
245--Проверка
246--select @frmId, @tmlId, @acccr, @accdb, @curDocNo
247
248declare @prmIdDate1 int
249select top(1) @prmIdDate1 = PRM_ID from dbo.JRN_PARAM_NAMES as v where v.PRM_NAME = 'Дата_1'
250declare @prmIdDate2 int
251select top(1) @prmIdDate2 = PRM_ID from dbo.JRN_PARAM_NAMES as v where v.PRM_NAME = 'Дата_2'
252declare @prmIdstring1 int
253select top(1) @prmIdstring1 = PRM_ID from dbo.JRN_PARAM_NAMES as v where v.PRM_NAME = 'Строка_1'
254declare @prmIdstring3 int
255select top(1) @prmIdstring3 = PRM_ID from dbo.JRN_PARAM_NAMES as v where v.PRM_NAME = 'Строка_3'
256declare @prmIdSum1 int
257select top(1) @prmIdSum1 = PRM_ID from dbo.JRN_PARAM_NAMES as v where v.PRM_NAME = 'Сумма_1'
258declare @prmIdLong1 int
259select top(1) @prmIdLong1 = PRM_ID from dbo.JRN_PARAM_NAMES as v where v.PRM_NAME = 'Целое_1'
260-- Проверка
261--select @prmIdDate1, @prmIdDate2, @prmIdstring1, @prmIdstring3, @prmIdSum1, @prmIdLong1
262
263declare @prmDocIdTerraCmd int
264select top(1) @prmDocIdTerraCmd = PRM_ID from dbo.DOC_PARAM_NAMES as v where v.PRM_NAME = 'Терра: Команда'
265declare @prmDocIdTerraTerritory int
266select top(1) @prmDocIdTerraTerritory = PRM_ID from dbo.DOC_PARAM_NAMES as v where v.PRM_NAME = 'Терра: Территория'
267declare @prmDocIdTerraDepatment int
268select top(1) @prmDocIdTerraDepatment = PRM_ID from dbo.DOC_PARAM_NAMES as v where v.PRM_NAME = 'Терра: Участок'
269
270
271declare @mscNoTradeAg int = 11
272declare @mscNoVidProd int = 13
273declare @currenrTradeId int
274declare @currenrTradeP0 int
275declare @currenrTradeP1 int
276
277declare @currenrTradeRowNo int = 1
278declare @maxTradeRowNo int = 0
279
280declare @TradeAgents table(id int, p0 int, p1 int, rowno int)
281declare @tempResDZ table(vKey int identity, ag_id int, doc_guid uniqueidentifier, doc_no nvarchar(32),
282 doc_date datetime, day_delay int, pk_id int, datedelay datetime, leave_sum money, rowno int)
283
284
285--insert @TradeAgents(id, p0, p1, rowno)
286--select Id, p0, p1, ROW_NUMBER() over(order by m.MSC_NAME asc) from dbo.MISC_TREE as t inner join dbo.MISC as m on m.MSC_ID = t.ID
287--where t.p0=@rootStartGroupTrade and t.SHORTCUT=0
288--and id=46367
289;with dv(Id, msc_name, p0,p1, isInt)
290as
291(
292select
293id,
294REPLACE(REPLACE(LEFT(msc_name,6),'.',''),' ',''),
295p0,p1,
296TRY_CAST(REPLACE(REPLACE(LEFT(msc_name,6),'.',''),' ','') as int)
297from dbo.MISC_TREE as mt
298inner join dbo.MISC as m on m.MSC_ID = mt.ID
299where
300id = @rootStartGroupTrade or (p0=@rootStartGroupTrade or p1=@rootStartGroupTrade or p2=@rootStartGroupTrade)
301and mt.SHORTCUT=0
302AND m.MSC_LNG3 = 1
303--and m.MSC_NAME not like '06%'
304--and m.MSC_NAME not like '2.1%'
305)
306insert @TradeAgents(id, p0, p1, rowno)
307select Id, p0, p1, ROW_NUMBER() over(order by IsInt asc) from dv where dv.isInt>100
308order by dv.isInt
309
310select @maxTradeRowNo = @@ROWCOUNT
311--select * from @TradeAgents as ta
312
313declare @docTag nvarchar(255)
314declare @insertedDocId int
315declare @insertedJrn table(J_ID int, SOURCE_ID int)
316declare @insertedJrnMiscId table (pk int)
317declare @dt datetime2
318declare @dt2 datetime2
319declare @docDone smallint = 2
320
321declare @doccount int = 0
322
323DECLARE @LogTimeStart DATETIME, @LogTimeEnd DATETIME
324DECLARE @LogMessage NVARCHAR(255)
325DECLARE @LogSql NVARCHAR(1000)
326
327DECLARE @AccPTRTId int
328SELECT @AccPTRTId = ACC_ID FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = 'ПТРТ'
329DECLARE @Acc31Id int
330SELECT @Acc31Id = ACC_ID FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '31'
331
332
333-- ACC_TYPE: 1 - ОПЛАТА, 2 - РЕАЛИЗАЦИЯ, 3 - ВОЗВРАТ
334DROP TABLE IF EXISTS ##ACCOUNTS_TABLE
335CREATE TABLE ##ACCOUNTS_TABLE(ACC_DB int, ACC_CR int, ACC_TYPE int)
336
337INSERT ##ACCOUNTS_TABLE(ACC_DB, ACC_CR, ACC_TYPE)
338SELECT ACC_DB, ACC_CR, ACC_TYPE
339FROM dbo.upfn_AccountsList(@Acc31Id)
340
341-- КОРРЕСПОНДЕНЦИЯ СЧЕТОВ ПРЕДОПЛАТ (ВСТУПИТЕЛЬНЫЙ БАЛАНС)
342DROP TABLE IF EXISTS ##ACCOUNTS_PREPAY
343CREATE TABLE ##ACCOUNTS_PREPAY(ACC_DB int, ACC_CR int)
344INSERT ##ACCOUNTS_PREPAY(ACC_DB,ACC_CR)
345SELECT d.ACC_DB, c.ACC_CR
346FROM
347 (SELECT ACC_ID AS ACC_DB FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE IN ('36111', '36112', '36113', '36114', '36115')) d,
348 (SELECT ACC_ID AS ACC_CR FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '0') c
349UNION ALL
350SELECT d.ACC_DB, c.ACC_CR
351FROM
352 (SELECT ACC_ID AS ACC_DB FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '03611') d,
353 (SELECT ACC_ID AS ACC_CR FROM ACCOUNTS WITH (NOLOCK) WHERE ACC_CODE = '00') c
354
355EXEC sp_set_session_context 'dbo.up_Repl_JournalIUDTrig', 1; -- set
356EXEC sp_set_session_context 'dbo.up_Repl_JrnMiscIUDTrig', 1; -- set
357
358declare @dateToRunProc datetime = DATEADD(d, 1, @date)
359while @currenrTradeRowNo<=@maxTradeRowNo
360begin
361 select @doccount = @doccount + 1
362 select @dt = GETDATE();
363
364 select top(1) @currenrTradeId = id, @currenrTradeP0=p0, @currenrTradeP1=p1 from @TradeAgents where rowno=@currenrTradeRowNo
365
366 --PRINT 'exec up_ws_rep_terra_AccountsReceivableForDocument @currenrTradeId,@dateToRunProc,default' + CAST(@currenrTradeId AS NVARCHAR(10))
367 IF @debug=1
368 BEGIN
369 SET @LogTimeStart = GETDATE()
370 SET @LogMessage= convert (varchar(30), @LogTimeStart, 8) +
371 ' exec up_ws_rep_terra_AccountsReceivableForDocument @currenrTradeId,@dateToRunProc,default //' + CAST(@currenrTradeId AS NVARCHAR(10))
372 RAISERROR ('%s', 0, 16, @LogMessage) WITH NOWAIT
373 END
374
375
376 insert @tempResDZ(ag_id,doc_guid,doc_no,doc_date, day_delay,pk_id, datedelay, leave_sum)
377 exec up_ws_AutoCreateDZDocsSingleDoc @currenrTradeId,@dateToRunProc,@AccPTRTId,@Acc31Id
378 --exec up_ws_rep_terra_AccountsReceivableForDocument @currenrTradeId,@dateToRunProc,default
379 SET @curRowCount = @@ROWCOUNT
380 IF @debug=1
381 BEGIN
382 SET @LogTimeEnd = GETDATE()
383 SET @LogMessage= CONVERT (varchar(30), @LogTimeEnd, 8)
384 RAISERROR ('%s', 0, 16, @LogMessage) WITH NOWAIT
385 SET @LogMessage= 'Time in milisec: ' + CONVERT (varchar(30), DATEDIFF(MILLISECOND, @LogTimeStart, @LogTimeEnd) , 8)
386 RAISERROR ('%s', 0, 16, @LogMessage) WITH NOWAIT
387 END
388
389 if(@curROwcount>0)
390 begin
391 begin tran
392 IF @debug=1
393 BEGIN
394 SET @LogTimeStart = GETDATE()
395 SET @LogMessage= CONVERT (varchar(30), getdate(), 8) + ' Start save doc'
396 RAISERROR ('%s', 0, 16, @LogMessage) WITH NOWAIT
397
398 END
399 select @curDocNo = v.AN_CURRENT from dbo.FRM_AUTONUM as v where v.FA_ID = @docAutoNumId
400
401 insert dbo.DOCUMENTS(DOC_DATE,DOC_DONE,DOC_NO,DOC_SUM,TML_ID,FRM_ID,DOC_NAME,MC_ID,FLD_ID)
402 VALUES(@date,@docDone, cast(@curDocNo+1 as nvarchar(32)),0, @tmlId, @frmId,'Дебиторская задолженность',1,@fldId)
403
404 select @insertedDocId = scope_identity()
405
406 update dbo.FRM_AUTONUM set AN_CURRENT = AN_CURRENT+1 where FA_ID = @docAutoNumId
407
408 ;with val(vKey, rowno) as
409 (
410 select v.vKey, ROW_NUMBER()over(ORDER BY v.vKey)-1
411 from @tempResDZ v
412 )
413 update @tempResDZ set rowno=val.rowno from @tempResDZ s inner join val on s.vKey=val.vKey
414
415
416 insert dbo.JOURNAL(DOC_ID,SOURCE_ID, J_DONE,J_DATE,J_TR_NO,J_LN_NO,J_SUM,ACC_DB,ACC_CR,J_AG1)
417 output Inserted.J_ID, Inserted.SOURCE_ID into @insertedJrn
418 select @insertedDocId,v.vKey, @docDone, @date,0, v.rowno, v.leave_sum, @accdb, @acccr, v.ag_id from @tempResDZ as v
419
420
421 --select * from @insertedJrn
422
423 insert dbo.JRN_MISC(J_ID,MSC_ID,MSC_NO)
424 select J_ID, @currenrTradeId, @mscNoTradeAg from @insertedJrn
425
426 insert dbo.JRN_MISC(J_ID,MSC_ID,MSC_NO)
427 select c.J_ID, v.pk_id,@mscNoVidProd from @tempResDZ v
428 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
429
430
431
432 insert dbo.DOC_PARAMS(DOC_ID,PRM_ID,PRM_LONG)
433 values(@insertedDocId,@prmDocIdTerraCmd,@currenrTradeP1),
434 (@insertedDocId,@prmDocIdTerraTerritory,@currenrTradeP0),
435 (@insertedDocId,@prmDocIdTerraDepatment,@currenrTradeId);
436
437 insert dbo.JRN_PARAMS(J_ID,PRM_ID,PRM_DATE)
438 select c.J_ID,@prmIdDate1,v.doc_date from @tempResDZ v
439 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
440
441 insert dbo.JRN_PARAMS(J_ID,PRM_ID,PRM_DATE)
442 select c.J_ID,@prmIdDate2,v.datedelay from @tempResDZ v
443 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
444
445 insert dbo.JRN_PARAMS(J_ID,PRM_ID,PRM_STRING)
446 select c.J_ID,@prmIdstring1,v.doc_no from @tempResDZ v
447 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
448
449 insert dbo.JRN_PARAMS(J_ID,PRM_ID,PRM_STRING)
450 select c.J_ID,@prmIdstring3,'{' +CAST(v.doc_guid as nvarchar(255)) +'}' from @tempResDZ v
451 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
452
453 insert dbo.JRN_PARAMS(J_ID,PRM_ID,PRM_CY)
454 select c.J_ID,@prmIdSum1,v.leave_sum from @tempResDZ v
455 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
456
457 insert dbo.JRN_PARAMS(J_ID,PRM_ID,PRM_LONG)
458 select c.J_ID,@prmIdLong1,v.day_delay from @tempResDZ v
459 inner join @insertedJrn c on v.vKey=c.SOURCE_ID
460
461 select @dt2 =GETDATE()
462 select @docTag = SUSER_NAME() + ' ' + FORMAT(@dt, 'dd/MM HH:mm-создан за:') + CAST(DATEDIFF(second, @dt, @dt2) as nvarchar(10)) +'сек|'
463
464
465 update dbo.DOCUMENTS set
466 DOC_SUM= (select SUM(v.leave_sum)from @tempResDZ v),
467 DOC_TAG = LEFT(@docTag,50),
468 DOC_PS1 = (select top(1) LEFT(m.MSC_NAME,50) from dbo.MISC as m where m.MSC_ID=@currenrTradeId),
469 --DOC_MEMO = 'Документ создан модулем автоматического создания документов',
470 LAST_DATE =GETDATE()
471 where DOC_ID = @insertedDocId
472
473 --select * from dbo.DOCUMENTS as d where doc_id=@insertedDocId
474 --print @docTag
475
476
477 IF @debug=1
478 BEGIN
479 SET @LogTimeEnd = GETDATE()
480 SET @LogMessage= CONVERT (varchar(30), @LogTimeEnd, 8) + ' End save doc'
481 RAISERROR ('%s', 0, 16, @LogMessage) WITH NOWAIT
482 SET @LogMessage= 'Time to create and save in milisec: ' + CONVERT (varchar(30), DATEDIFF(MILLISECOND, @LogTimeStart, @LogTimeEnd) , 8)
483 RAISERROR ('%s', 0, 16, @LogMessage) WITH NOWAIT
484 RAISERROR ('%s', 0, 16, '____________') WITH NOWAIT
485 END
486 commit tran
487
488
489 end
490 --
491 delete @tempResDZ
492 delete @insertedJrn
493 delete @insertedJrnMiscId
494 select @currenrTradeRowNo = @currenrTradeRowNo + 1
495end
496
497EXEC sp_set_session_context 'dbo.up_Repl_JournalIUDTrig', NULL -- clear
498EXEC sp_set_session_context 'dbo.up_Repl_JrnMiscIUDTrig', NULL -- set
499
500SELECT @doccount
501GO
502GRANT EXECUTE on dbo.up_ws_AutoCreateDZDocs TO ap_public
503GO
504
505--EXECUTE AS LOGIN = 'admin';
506--exec dbo.up_ws_AutoCreateDZDocs 39147
507--revert;
508--GO
509--DROP PROCEDURE IF EXISTS dbo.up_ws_AutoCreateDZDocs
510
511BEGIN TRY
512EXECUTE AS LOGIN = 'admin';
513--exec dbo.up_ws_AutoCreateDZDocs 39147 -- lg
514exec dbo.up_ws_AutoCreateDZDocs 38179 -- dn
515revert;
516END TRY
517BEGIN CATCH
518 EXEC sp_executesql N'DROP PROCEDURE IF EXISTS dbo.up_ws_AutoCreateDZDocs';
519 THROW;
520END CATCH
521GO
522DROP PROCEDURE IF EXISTS dbo.up_ws_AutoCreateDZDocs
523GO
524DROP PROC IF EXISTS dbo.up_ws_AutoCreateDZDocsSingleDoc
525GO