API tools faq
paste
Advertisement
ksviridova

Untitled

ksviridova
Sep 25th, 2023
979
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Delphi 10.62 KB | None | 0 0
raw download clone embed print report
  1.  
  2. /* Запрос № 1  25.09.2023 17:02:42  Алиас Sov */
  3. CREATE TABLE #FltDoc(
  4. Code INT NULL,
  5. KeyGroup INT NULL,
  6. IsBill TINYINT NULL ,
  7. numreg CHAR (10) NULL ,
  8. datereg SMALLDATETIME NULL ,
  9. sumdoc MONEY NULL ,
  10. CodePay INT NULL ,
  11. SprPay TINYINT NULL ,
  12. CodeType INT NULL ,
  13. FlgDir TINYINT NULL
  14. )
  15.  
  16. /* Запрос № 2  25.09.2023 17:02:42  Алиас Sov */
  17. CREATE TABLE #TypeDoc(
  18. CodeType INT NULL ,
  19. Shifr VARCHAR(8) NULL ,
  20. fldname VARCHAR(70) NULL ,
  21. fldlabel VARCHAR(70) NULL ,
  22. FlgDir TINYINT NULL
  23. )
  24.  
  25. /* Запрос № 3  25.09.2023 17:02:42  Алиас Sov */
  26. INSERT #FltDoc(
  27. Code,KeyGroup,IsBill,numreg,datereg,sumdoc,
  28. CodeType,FlgDir,CodePay,SprPay
  29. ) SELECT
  30. keybill,0,
  31. 1,numreg,datereg,
  32. ISNULL(ROUND(SumPrc,2),0),
  33. CodeType,
  34. ISNULL(FlgDir,0),
  35. CodePay=CASE FlgDir WHEN 0 THEN CodePay ELSE coderec END,
  36. SprPay=CASE FlgDir WHEN 0 THEN SprPay ELSE sprrec END
  37. FROM bill Bil
  38. WHERE datereg>='09/01/2015'
  39. AND datereg<='09/30/2023'
  40. AND Bil.FlgDir=0
  41.  
  42. /* Запрос № 4  25.09.2023 17:02:42  Алиас Sov */
  43. INSERT #FltDoc(
  44. Code,KeyGroup,IsBill,numreg,datereg,sumdoc,
  45. CodeType,FlgDir,CodePay,SprPay
  46. ) SELECT
  47. keypay,KeyGroup,
  48. 0,numreg,datereg,
  49. ISNULL(ROUND(SumPrc,2),0),
  50. CodeType,
  51. 1-ISNULL(FlgDir,0),
  52. CodePay=CASE FlgDir WHEN 0 THEN CodePay ELSE coderec END,
  53. SprPay=CASE FlgDir WHEN 0 THEN SprPay ELSE sprrec END
  54. FROM Pay Pay
  55. WHERE datereg>='09/01/2015'
  56. AND datereg<='09/30/2023'
  57. AND Pay.FlgDir=0
  58.  
  59. /* Запрос № 5  25.09.2023 17:02:42  Алиас Sov */
  60. INSERT #TypeDoc(
  61. CodeType,
  62. FlgDir
  63. ) SELECT DISTINCT
  64. CodeType,
  65. FlgDir
  66. FROM #FltDoc
  67.  
  68. /* Запрос № 6  25.09.2023 17:02:42  Алиас Sov */
  69. UPDATE #TypeDoc SET
  70. Shifr=Ref.Shifr
  71. FROM #TypeDoc Tmp,reference Ref
  72. WHERE Ref.Code=Tmp.CodeType
  73.  
  74. /* Запрос № 7  25.09.2023 17:02:42  Алиас Sov */
  75. UPDATE #TypeDoc SET
  76. fldname =(CASE FlgDir
  77. WHEN 0 THEN 'Db'+CONVERT(VARCHAR(27),ISNULL(CodeType,0))
  78. ELSE 'Cr'+CONVERT(VARCHAR(27),ISNULL(CodeType,0)) END)
  79. ,fldlabel =(CASE FlgDir
  80. WHEN 0 THEN 'Дб '+RTRIM(ISNULL(Shifr,''))+''
  81. ELSE 'Кр '+RTRIM(ISNULL(Shifr,''))+'' END)
  82.  
  83. /* Запрос № 8  25.09.2023 17:02:42  Алиас Sov */
  84. SELECT fldname
  85. FROM #TypeDoc
  86. WHERE FlgDir=0
  87. ORDER BY Shifr
  88.  
  89. /* Запрос № 9  25.09.2023 17:02:42  Алиас Sov */
  90. SELECT fldname
  91. FROM #TypeDoc
  92. WHERE FlgDir=1
  93. ORDER BY Shifr
  94.  
  95. /* Запрос № 10  25.09.2023 17:02:42  Алиас Sov */
  96. CREATE TABLE #report(
  97. no INT NULL,
  98. CodePay INT NULL ,
  99. SprPay TINYINT NULL,
  100. DbBeg MONEY  NULL,
  101. CrBeg MONEY  NULL,
  102. Db178 MONEY NULL ,
  103. DbObor MONEY NULL ,
  104. Cr1245 MONEY NULL ,
  105. Cr662 MONEY NULL ,
  106. CrObor MONEY NULL ,
  107. DbEnd MONEY  NULL,
  108. CrEnd MONEY  NULL
  109. )
  110.  
  111. /* Запрос № 11  25.09.2023 17:02:42  Алиас Sov */
  112. INSERT #report(no,
  113. CodePay,SprPay
  114. ) SELECT DISTINCT
  115. 1,CodePay,SprPay
  116. FROM #FltDoc
  117.  
  118. /* Запрос № 12  25.09.2023 17:02:42  Алиас Sov */
  119. CREATE TABLE #saldo(
  120. dbsum MONEY NULL ,
  121. crsum MONEY NULL ,
  122. CodePay INT NULL ,
  123. SprPay TINYINT NULL ,
  124. typesaldo TINYINT NULL
  125. )
  126.  
  127. /* Запрос № 13  25.09.2023 17:02:42  Алиас Sov */
  128. INSERT #saldo(
  129. typesaldo,SprPay,CodePay,dbsum
  130. ) SELECT 1,
  131. SprPay,CodePay,SUM(ROUND(SumPrc,2))
  132. FROM bill Bil
  133. WHERE datereg<'09/01/2015'
  134. AND Bil.FlgDir=0
  135. GROUP BY SprPay,CodePay
  136.  
  137. /* Запрос № 14  25.09.2023 17:02:44  Алиас Sov */
  138. INSERT #saldo(
  139. typesaldo,SprPay,CodePay,dbsum
  140. ) SELECT 1,
  141. SprPay,CodePay,-SUM(ROUND(SumPrc,2))
  142. FROM Pay Pay
  143. WHERE datereg<'09/01/2015'
  144. AND Pay.FlgDir=0
  145. GROUP BY SprPay,CodePay
  146.  
  147. /* Запрос № 15  25.09.2023 17:02:44  Алиас Sov */
  148. INSERT #saldo(
  149. typesaldo,SprPay,CodePay,dbsum
  150. ) SELECT
  151. 2,SprPay,CodePay,SUM(dbsum)
  152. FROM #saldo
  153. GROUP BY  SprPay,CodePay
  154.  
  155. /* Запрос № 16  25.09.2023 17:02:44  Алиас Sov */
  156. DELETE #saldo
  157. WHERE typesaldo<>2
  158.  
  159. /* Запрос № 17  25.09.2023 17:02:45  Алиас Sov */
  160. UPDATE #saldo SET
  161. dbsum=CASE WHEN ISNULL(dbsum,0)>=0 THEN dbsum ELSE NULL END,
  162. crsum=CASE WHEN ISNULL(dbsum,0)<0 THEN -dbsum ELSE NULL END
  163.  
  164. /* Запрос № 18  25.09.2023 17:02:45  Алиас Sov */
  165. UPDATE #report SET
  166. DbBeg=dbsum,
  167. CrBeg=crsum
  168. FROM #saldo
  169. WHERE #report.SprPay=#saldo.SprPay
  170. AND #report.CodePay=#saldo.CodePay
  171.  
  172. /* Запрос № 19  25.09.2023 17:02:45  Алиас Sov */
  173. DELETE #saldo
  174. FROM #report
  175. WHERE #report.SprPay=#saldo.SprPay
  176. AND #report.CodePay=#saldo.CodePay
  177.  
  178. /* Запрос № 20  25.09.2023 17:02:45  Алиас Sov */
  179. INSERT #report(
  180. DbBeg,CrBeg,no,SprPay,CodePay
  181. ) SELECT
  182. dbsum,crsum,1,SprPay,CodePay
  183. FROM #saldo
  184.  
  185. /* Запрос № 21  25.09.2023 17:02:45  Алиас Sov */
  186. DROP TABLE #saldo
  187.  
  188. /* Запрос № 22  25.09.2023 17:02:45  Алиас Sov */
  189. INSERT #report(no) VALUES(2)
  190.  
  191. /* Запрос № 23  25.09.2023 17:02:45  Алиас Sov */
  192. UPDATE Dst SET
  193.   DbBeg=(SELECT SUM(Rep.DbBeg) FROM #report Rep
  194.             WHERE Rep.no<>2)
  195. FROM #report Dst
  196. WHERE Dst.no=2
  197.  
  198. /* Запрос № 24  25.09.2023 17:02:45  Алиас Sov */
  199. UPDATE Dst SET
  200.   CrBeg=(SELECT SUM(Rep.CrBeg) FROM #report Rep
  201.             WHERE Rep.no<>2)
  202. FROM #report Dst
  203. WHERE Dst.no=2
  204.  
  205. /* Запрос № 25  25.09.2023 17:02:45  Алиас Sov */
  206. SELECT
  207. CodeType,fldname
  208. FROM #TypeDoc
  209. WHERE FlgDir=0
  210.  
  211. /* Запрос № 26  25.09.2023 17:02:45  Алиас Sov */
  212. UPDATE #report SET
  213. Db178=
  214. (SELECT SUM(sumdoc)
  215. FROM #FltDoc
  216. WHERE #FltDoc.FlgDir=0
  217. AND #FltDoc.CodePay=#report.CodePay
  218. AND #FltDoc.SprPay=#report.SprPay
  219. AND #FltDoc.CodeType=178
  220. AND no<>2)
  221.  
  222. /* Запрос № 27  25.09.2023 17:02:45  Алиас Sov */
  223. UPDATE Dst SET
  224.   Db178=(SELECT SUM(Rep.Db178) FROM #report Rep
  225.             WHERE Rep.no<>2)
  226. FROM #report Dst
  227. WHERE Dst.no=2
  228.  
  229. /* Запрос № 28  25.09.2023 17:02:45  Алиас Sov */
  230. UPDATE #report
  231. SET DbObor=
  232. (SELECT SUM(sumdoc) FROM #FltDoc
  233. WHERE #FltDoc.FlgDir=0
  234. AND #FltDoc.CodePay=#report.CodePay
  235. AND #FltDoc.SprPay=#report.SprPay
  236. AND no<>2)
  237.  
  238. /* Запрос № 29  25.09.2023 17:02:45  Алиас Sov */
  239. SELECT
  240. CodeType,fldname
  241. FROM #TypeDoc
  242. WHERE FlgDir=1
  243.  
  244. /* Запрос № 30  25.09.2023 17:02:45  Алиас Sov */
  245. UPDATE #report SET
  246. Cr662=
  247. (SELECT SUM(sumdoc)
  248. FROM #FltDoc
  249. WHERE #FltDoc.FlgDir=1
  250. AND #FltDoc.CodePay=#report.CodePay
  251. AND #FltDoc.SprPay=#report.SprPay
  252. AND #FltDoc.CodeType=662
  253. AND no<>2)
  254.  
  255. /* Запрос № 31  25.09.2023 17:02:46  Алиас Sov */
  256. UPDATE Dst SET
  257.   Cr662=(SELECT SUM(Rep.Cr662) FROM #report Rep
  258.             WHERE Rep.no<>2)
  259. FROM #report Dst
  260. WHERE Dst.no=2
  261.  
  262. /* Запрос № 32  25.09.2023 17:02:46  Алиас Sov */
  263. UPDATE #report SET
  264. Cr1245=
  265. (SELECT SUM(sumdoc)
  266. FROM #FltDoc
  267. WHERE #FltDoc.FlgDir=1
  268. AND #FltDoc.CodePay=#report.CodePay
  269. AND #FltDoc.SprPay=#report.SprPay
  270. AND #FltDoc.CodeType=1245
  271. AND no<>2)
  272.  
  273. /* Запрос № 33  25.09.2023 17:02:46  Алиас Sov */
  274. UPDATE Dst SET
  275.   Cr1245=(SELECT SUM(Rep.Cr1245) FROM #report Rep
  276.             WHERE Rep.no<>2)
  277. FROM #report Dst
  278. WHERE Dst.no=2
  279.  
  280. /* Запрос № 34  25.09.2023 17:02:46  Алиас Sov */
  281. UPDATE #report
  282. SET CrObor=
  283. (SELECT SUM(sumdoc) FROM #FltDoc
  284. WHERE #FltDoc.FlgDir=1
  285. AND #FltDoc.CodePay=#report.CodePay
  286. AND #FltDoc.SprPay=#report.SprPay
  287. AND no<>2)
  288.  
  289. /* Запрос № 35  25.09.2023 17:02:46  Алиас Sov */
  290. UPDATE Dst SET
  291.   DbObor=(SELECT SUM(Rep.DbObor) FROM #report Rep
  292.             WHERE Rep.no<>2)
  293. FROM #report Dst
  294. WHERE Dst.no=2
  295.  
  296. /* Запрос № 36  25.09.2023 17:02:46  Алиас Sov */
  297. UPDATE Dst SET
  298.   CrObor=(SELECT SUM(Rep.CrObor) FROM #report Rep
  299.             WHERE Rep.no<>2)
  300. FROM #report Dst
  301. WHERE Dst.no=2
  302.  
  303. /* Запрос № 37  25.09.2023 17:02:46  Алиас Sov */
  304. UPDATE #report SET
  305. DbEnd=
  306. ISNULL(DbBeg,0)-ISNULL(CrBeg,0)+
  307. ISNULL(DbObor,0)-ISNULL(CrObor,0)
  308. WHERE no<>2
  309.  
  310. /* Запрос № 38  25.09.2023 17:02:46  Алиас Sov */
  311. UPDATE #report SET
  312. DbEnd=CASE WHEN DbEnd>0 THEN DbEnd ELSE NULL END,
  313. CrEnd=CASE WHEN DbEnd<0 THEN -DbEnd ELSE NULL END
  314. WHERE no<>2
  315.  
  316. /* Запрос № 39  25.09.2023 17:02:46  Алиас Sov */
  317. UPDATE Dst SET
  318.   DbEnd=(SELECT SUM(Rep.DbEnd) FROM #report Rep
  319.             WHERE Rep.no<>2)
  320. FROM #report Dst
  321. WHERE Dst.no=2
  322.  
  323. /* Запрос № 40  25.09.2023 17:02:46  Алиас Sov */
  324. UPDATE Dst SET
  325.   CrEnd=(SELECT SUM(Rep.CrEnd) FROM #report Rep
  326.             WHERE Rep.no<>2)
  327. FROM #report Dst
  328. WHERE Dst.no=2
  329.  
  330. /* Запрос № 41  25.09.2023 17:02:46  Алиас Sov */
  331. DELETE #report
  332. WHERE no=1
  333. AND ROUND(ISNULL(DbObor,0),2)=0
  334. AND ROUND(ISNULL(CrObor,0),2)=0
  335. AND ROUND(ISNULL(DbBeg,0),2)=0
  336. AND ROUND(ISNULL(CrBeg,0),2)=0
  337. AND ROUND(ISNULL(DbEnd,0),2)=0
  338. AND ROUND(ISNULL(CrEnd,0),2)=0
  339.  
  340. /* Запрос № 42  25.09.2023 17:02:46  Алиас Sov */
  341. SELECT
  342. Kor.Shifr,Kor.Name Korr,Tmp.*
  343. FROM #report Tmp
  344. LEFT OUTER JOIN AllRef Kor ON Tmp.SprPay=Kor.CodeSpr AND Tmp.CodePay=Kor.Code
  345. ORDER BY no,Kor.Shifr,Korr
  346.  
  347. /* Запрос № 43  25.09.2023 17:02:47  Алиас Sov */
  348. SELECT fldlabel FROM #TypeDoc
  349. WHERE fldname='Shifr'
  350.  
  351. /* Запрос № 44  25.09.2023 17:02:47  Алиас Sov */
  352. SELECT fldlabel FROM #TypeDoc
  353. WHERE fldname='Korr'
  354.  
  355. /* Запрос № 45  25.09.2023 17:02:47  Алиас Sov */
  356. SELECT fldlabel FROM #TypeDoc
  357. WHERE fldname='no'
  358.  
  359. /* Запрос № 46  25.09.2023 17:02:47  Алиас Sov */
  360. SELECT fldlabel FROM #TypeDoc
  361. WHERE fldname='CodePay'
  362.  
  363. /* Запрос № 47  25.09.2023 17:02:47  Алиас Sov */
  364. SELECT fldlabel FROM #TypeDoc
  365. WHERE fldname='SprPay'
  366.  
  367. /* Запрос № 48  25.09.2023 17:02:47  Алиас Sov */
  368. SELECT fldlabel FROM #TypeDoc
  369. WHERE fldname='DbBeg'
  370.  
  371. /* Запрос № 49  25.09.2023 17:02:47  Алиас Sov */
  372. SELECT fldlabel FROM #TypeDoc
  373. WHERE fldname='CrBeg'
  374.  
  375. /* Запрос № 50  25.09.2023 17:02:47  Алиас Sov */
  376. SELECT fldlabel FROM #TypeDoc
  377. WHERE fldname='Db178'
  378.  
  379. /* Запрос № 51  25.09.2023 17:02:47  Алиас Sov */
  380. SELECT fldlabel FROM #TypeDoc
  381. WHERE fldname='DbObor'
  382.  
  383. /* Запрос № 52  25.09.2023 17:02:47  Алиас Sov */
  384. SELECT fldlabel FROM #TypeDoc
  385. WHERE fldname='Cr1245'
  386.  
  387. /* Запрос № 53  25.09.2023 17:02:47  Алиас Sov */
  388. SELECT fldlabel FROM #TypeDoc
  389. WHERE fldname='Cr662'
  390.  
  391. /* Запрос № 54  25.09.2023 17:02:47  Алиас Sov */
  392. SELECT fldlabel FROM #TypeDoc
  393. WHERE fldname='CrObor'
  394.  
  395. /* Запрос № 55  25.09.2023 17:02:47  Алиас Sov */
  396. SELECT fldlabel FROM #TypeDoc
  397. WHERE fldname='DbEnd'
  398.  
  399. /* Запрос № 56  25.09.2023 17:02:47  Алиас Sov */
  400. SELECT fldlabel FROM #TypeDoc
  401. WHERE fldname='CrEnd'
  402.  
  403. /* Запрос № 57  25.09.2023 17:02:47  Алиас Sov */
  404. DROP TABLE #report
  405.  
  406. /* Запрос № 58  25.09.2023 17:02:47  Алиас Sov */
  407. DROP TABLE #TypeDoc
  408.  
  409. /* Запрос № 59  25.09.2023 17:02:47  Алиас Sov */
  410. DROP TABLE #FltDoc
  411.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!