API tools faq
paste
Advertisement
irvan_herz

Untitled

irvan_herz
Sep 24th, 2024 (edited)
43
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.76 KB | None | 0 0
raw download clone embed print report
  1. ============== TOKOPEDIA
  2.  
  3. DECLARE @sql NVARCHAR(MAX) = ''
  4. DECLARE @dbname NVARCHAR(255)
  5. DECLARE @mindate NVARCHAR(255) = '2024-08-01'
  6.  
  7. -- Cursor to iterate over each database
  8. DECLARE db_cursor CURSOR FOR
  9. SELECT name
  10. FROM sys.databases
  11. WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND name LIKE 'ERASOFT_%'
  12.  
  13. OPEN db_cursor
  14. FETCH NEXT FROM db_cursor INTO @dbname
  15.  
  16. WHILE @@FETCH_STATUS = 0
  17. BEGIN
  18. --tidak update batal
  19. SET @sql = @sql + '
  20. SELECT
  21. ''Tidak Update Jadi Batal'' AS TYPE,
  22. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  23. ''' + @dbname + ''' AS DBPATHERA,
  24. a.TGL AS TGL_WEBHOOK,
  25. b.TGL_INPUT,
  26. a.CUST,
  27. a.SHOPID,
  28. c.TIDAK_HIT_UANG_R,
  29. b.NO_BUKTI,
  30. b.NO_REFERENSI,
  31. b.STATUS_TRANSAKSI,
  32. b.PRINT_COUNT,
  33. a.JSON AS JSON
  34. FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
  35. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',no_referensi)) and a.CUST=b.CUST
  36. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  37. WHERE
  38. a.tgl >= '''+ @mindate +''' AND
  39. status_transaksi not in (''11'',''12'') and
  40. a.ORDER_STATUS in (''0'',''3'',''5'',''6'',''10'',''15'') and b.NO_BUKTI like ''TP%''
  41. UNION ALL'
  42.  
  43. --tidak update selesai
  44. SET @sql = @sql + '
  45. SELECT
  46. ''Tidak Update Jadi Selesai'' AS TYPE,
  47. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  48. ''' + @dbname + ''' AS DBPATHERA,
  49. a.TGL AS TGL_WEBHOOK,
  50. b.TGL_INPUT,
  51. a.CUST,
  52. a.SHOPID,
  53. c.TIDAK_HIT_UANG_R,
  54. b.NO_BUKTI,
  55. b.NO_REFERENSI,
  56. b.STATUS_TRANSAKSI,
  57. b.PRINT_COUNT,
  58. a.JSON AS JSON
  59. FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
  60. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',no_referensi)) and a.CUST=b.CUST
  61. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  62. WHERE
  63. a.tgl >= '''+ @mindate +''' AND
  64. ((ISNULL(al_cust, '''') <> '''' AND status_transaksi <> ''04'') OR (ISNULL(al_cust, '''') = '''' AND status_transaksi <> ''01'')) and
  65. a.ORDER_STATUS in (''700'',''701'') and b.NO_BUKTI like ''tp%''
  66. UNION ALL'
  67.  
  68. --tidak update siap kirim
  69. SET @sql = @sql + '
  70. SELECT
  71. ''Tidak Update Jadi Siap Kirim'' AS TYPE,
  72. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  73. ''' + @dbname + ''' AS DBPATHERA,
  74. a.TGL AS TGL_WEBHOOK,
  75. b.TGL_INPUT,
  76. a.CUST,
  77. a.SHOPID,
  78. c.TIDAK_HIT_UANG_R,
  79. b.NO_BUKTI,
  80. b.NO_REFERENSI,
  81. b.STATUS_TRANSAKSI,
  82. b.PRINT_COUNT,
  83. a.JSON AS JSON
  84. FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
  85. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',no_referensi)) and a.CUST=b.CUST
  86. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  87. WHERE
  88. a.tgl >= '''+ @mindate +''' AND
  89. isnull(print_count,'''') not in (''1'',''2'') and
  90. a.ORDER_STATUS in (''400'',''450'') and b.NO_BUKTI like ''tp%''
  91. UNION ALL'
  92.  
  93. --telat masuk (> 30 menit)
  94. SET @sql = @sql + '
  95. SELECT
  96. ''Telat Masuk (> 30 menit)'' AS TYPE,
  97. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  98. ''' + @dbname + ''' AS DBPATHERA,
  99. a.TGL AS TGL_WEBHOOK,
  100. b.TGL_INPUT,
  101. a.CUST,
  102. a.SHOPID,
  103. c.TIDAK_HIT_UANG_R,
  104. b.NO_BUKTI,
  105. b.NO_REFERENSI,
  106. b.STATUS_TRANSAKSI,
  107. b.PRINT_COUNT,
  108. a.JSON AS JSON
  109. FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
  110. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',no_referensi)) and a.CUST=b.CUST
  111. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  112. WHERE
  113. a.tgl >= '''+ @mindate +''' AND
  114. DATEDIFF(MINUTE, a.tgl, b.tgl_input) > 30 AND CAST(b.tgl_input AS DATE) = CAST(GETDATE() AS DATE) and a.ORDER_STATUS=''220''
  115. UNION ALL'
  116.  
  117. --tidak masuk
  118. SET @sql = @sql + '
  119. SELECT
  120. ''Tidak Masuk'' AS TYPE,
  121. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  122. ''' + @dbname + ''' AS DBPATHERA,
  123. a.TGL AS TGL_WEBHOOK,
  124. b.TGL_INPUT,
  125. a.CUST,
  126. a.SHOPID,
  127. c.TIDAK_HIT_UANG_R,
  128. b.NO_BUKTI,
  129. CAST(a.ORDERID as varchar),
  130. b.STATUS_TRANSAKSI,
  131. b.PRINT_COUNT,
  132. a.JSON AS JSON
  133. FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
  134. LEFT JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=substring(no_referensi,0, charindex('';'',no_referensi)) and a.CUST=b.CUST
  135. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  136. WHERE
  137. a.tgl >= '''+ @mindate +''' AND
  138. a.ORDER_STATUS=''220'' and b.NO_BUKTI is null
  139. UNION ALL'
  140.  
  141. --dobel
  142. SET @sql = @sql + '
  143. SELECT
  144. ''Dobel'' AS TYPE,
  145. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  146. ''' + @dbname + ''' AS DBPATHERA,
  147. a.TGL AS TGL_WEBHOOK,
  148. NULL,
  149. a.CUST,
  150. a.SHOPID,
  151. c.TIDAK_HIT_UANG_R,
  152. b.DUP_NOBUK,
  153. CAST(a.ORDERID as varchar),
  154. b.DUP_ST,
  155. NULL,
  156. a.JSON AS JSON
  157. FROM [' + @dbname + ']..TABEL_WEBHOOK_TOKPED a(nolock)
  158. INNER JOIN (
  159. SELECT
  160. y.CUST, y.NO_REFERENSI, COUNT(*) AS DUP,
  161. STUFF((SELECT '','' + x1.NO_BUKTI FROM [' + @dbname + ']..SOT01A x1 WHERE x1.NO_REFERENSI = y.NO_REFERENSI FOR XML PATH('''')), 1, 1, '''') AS DUP_NOBUK,
  162. STUFF((SELECT '','' + x2.STATUS_TRANSAKSI FROM [' + @dbname + ']..SOT01A x2 WHERE x2.NO_REFERENSI = y.NO_REFERENSI FOR XML PATH('''')), 1, 1, '''') AS DUP_ST
  163. FROM [' + @dbname + ']..SOT01A y (nolock)
  164. GROUP BY CUST, NO_REFERENSI
  165. ) b ON a.ORDERID=substring(b.no_referensi,0, charindex('';'',b.no_referensi)) and a.CUST=b.CUST
  166. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  167. WHERE
  168. a.tgl >= '''+ @mindate +''' AND
  169. a.ORDER_STATUS=''220'' AND b.DUP > 1
  170.  
  171. UNION ALL'
  172. FETCH NEXT FROM db_cursor INTO @dbname
  173. END
  174.  
  175. -- Remove the last 'UNION ALL'
  176. SET @sql = LEFT(@sql, LEN(@sql) - 10)
  177.  
  178. -- Apply condition as needed
  179. --SET @sql = CONCAT('WITH RES AS (', @sql, ') SELECT * FROM RES WHERE TIDAK_HIT_UANG_R = ''1''')
  180.  
  181. -- Execute the dynamic SQL
  182. EXEC sp_executesql @sql
  183.  
  184. -- Cleanup
  185. CLOSE db_cursor
  186. DEALLOCATE db_cursor
  187.  
  188.  
  189.  
  190.  
  191.  
  192. ======= SHOPEE
  193.  
  194.  
  195.  
  196.  
  197.  
  198.  
  199.  
  200. DECLARE @sql NVARCHAR(MAX) = ''
  201. DECLARE @dbname NVARCHAR(255)
  202. DECLARE @mindate NVARCHAR(255) = '2024-08-01'
  203.  
  204. -- Cursor to iterate over each database
  205. DECLARE db_cursor CURSOR FOR
  206. SELECT name
  207. FROM sys.databases
  208. WHERE state_desc = 'ONLINE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND name LIKE 'ERASOFT_%'
  209.  
  210. OPEN db_cursor
  211. FETCH NEXT FROM db_cursor INTO @dbname
  212.  
  213. WHILE @@FETCH_STATUS = 0
  214. BEGIN
  215. --tidak update batal
  216. SET @sql = @sql + '
  217. SELECT
  218. ''Tidak Update Jadi Batal'' AS TYPE,
  219. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  220. ''' + @dbname + ''' AS DBPATHERA,
  221. a.TGL AS TGL_WEBHOOK,
  222. b.TGL_INPUT,
  223. a.CUST,
  224. a.SHOPID,
  225. c.TIDAK_HIT_UANG_R,
  226. b.NO_BUKTI,
  227. b.NO_REFERENSI,
  228. b.STATUS_TRANSAKSI,
  229. b.PRINT_COUNT,
  230. a.JSON AS JSON
  231. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a (nolock)
  232. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
  233. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  234. WHERE
  235. a.tgl >= '''+ @mindate +''' AND
  236. b.status_transaksi not in (''11'',''12'') AND
  237. a.ORDER_STATUS in (''CANCELLED'') and b.NO_BUKTI like ''sp%''
  238. UNION ALL'
  239.  
  240. --tidak update selesai
  241. SET @sql = @sql + '
  242. SELECT
  243. ''Tidak Update Jadi Selesai'' AS TYPE,
  244. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  245. ''' + @dbname + ''' AS DBPATHERA,
  246. a.TGL AS TGL_WEBHOOK,
  247. b.TGL_INPUT,
  248. a.CUST,
  249. a.SHOPID,
  250. c.TIDAK_HIT_UANG_R,
  251. b.NO_BUKTI,
  252. b.NO_REFERENSI,
  253. b.STATUS_TRANSAKSI,
  254. b.PRINT_COUNT,
  255. a.JSON AS JSON
  256. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
  257. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
  258. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  259. WHERE
  260. a.tgl >= '''+ @mindate +''' AND
  261. ((ISNULL(al_cust, '''') <> '''' AND
  262. status_transaksi <> ''04'') OR (ISNULL(al_cust, '''') = '''' AND status_transaksi <> ''01'')) and
  263. a.ORDER_STATUS in (''COMPLETED'') and b.NO_BUKTI like ''sp%''
  264. UNION ALL'
  265.  
  266. --tidak update siap kirim
  267. SET @sql = @sql + '
  268. SELECT
  269. ''Tidak Update Jadi Siap Kirim'' AS TYPE,
  270. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  271. ''' + @dbname + ''' AS DBPATHERA,
  272. a.TGL AS TGL_WEBHOOK,
  273. b.TGL_INPUT,
  274. a.CUST,
  275. a.SHOPID,
  276. c.TIDAK_HIT_UANG_R,
  277. b.NO_BUKTI,
  278. b.NO_REFERENSI,
  279. b.STATUS_TRANSAKSI,
  280. b.PRINT_COUNT,
  281. a.JSON AS JSON
  282. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
  283. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
  284. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  285. WHERE
  286. a.tgl >= '''+ @mindate +''' AND
  287. isnull(b.STATUS_TRANSAKSI,'''') in (''0'') AND
  288. a.ORDER_STATUS in (''READY_TO_SHIP'',''PROCESSED'',''SHIPPED'',''TO_CONFIRM_RECEIVE'',''READY'') and b.NO_BUKTI like ''sp%''
  289. UNION ALL'
  290.  
  291. --tidak update sudah bayar
  292. SET @sql = @sql + '
  293. SELECT
  294. ''Tidak Update Jadi Sudah Bayar'' AS TYPE,
  295. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  296. ''' + @dbname + ''' AS DBPATHERA,
  297. a.TGL AS TGL_WEBHOOK,
  298. b.TGL_INPUT,
  299. a.CUST,
  300. a.SHOPID,
  301. c.TIDAK_HIT_UANG_R,
  302. b.NO_BUKTI,
  303. b.NO_REFERENSI,
  304. b.STATUS_TRANSAKSI,
  305. b.PRINT_COUNT,
  306. a.JSON AS JSON
  307. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
  308. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) on a.ORDERID=NO_REFERENSI and a.CUST=b.CUST
  309. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  310. WHERE
  311. a.tgl >= '''+ @mindate +''' AND
  312. isnull(STATUS_TRANSAKSI,'''') in (''0'') and
  313. a.ORDER_STATUS in (''READY_TO_SHIP'',''PROCESSED'',''SHIPPED'',''TO_CONFIRM_RECEIVE'',''READY'') and b.NO_BUKTI like ''sp%''
  314. UNION ALL'
  315.  
  316. --tidak masuk
  317. SET @sql = @sql + '
  318. SELECT
  319. ''Tidak Masuk'' AS TYPE,
  320. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  321. ''' + @dbname + ''' AS DBPATHERA,
  322. a.TGL AS TGL_WEBHOOK,
  323. b.TGL_INPUT,
  324. a.CUST,
  325. a.SHOPID,
  326. c.TIDAK_HIT_UANG_R,
  327. b.NO_BUKTI,
  328. a.ORDERID,
  329. b.STATUS_TRANSAKSI,
  330. b.PRINT_COUNT,
  331. a.JSON AS JSON
  332. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
  333. LEFT JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=b.NO_REFERENSI and a.CUST=b.CUST
  334. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  335. WHERE
  336. a.tgl >= '''+ @mindate +''' AND
  337. a.ORDER_STATUS=''UNPAID'' and b.NO_BUKTI is null
  338. UNION ALL'
  339.  
  340. --telat masuk (> 30 menit)
  341. SET @sql = @sql + '
  342. SELECT
  343. ''Telat Masuk (> 30 menit)'' AS TYPE,
  344. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  345. ''' + @dbname + ''' AS DBPATHERA,
  346. a.TGL AS TGL_WEBHOOK,
  347. b.TGL_INPUT,
  348. a.CUST,
  349. a.SHOPID,
  350. c.TIDAK_HIT_UANG_R,
  351. b.NO_BUKTI,
  352. a.ORDERID,
  353. b.STATUS_TRANSAKSI,
  354. b.PRINT_COUNT,
  355. a.JSON AS JSON
  356. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
  357. INNER JOIN [' + @dbname + ']..SOT01A b(nolock) ON a.ORDERID=b.NO_REFERENSI and a.CUST=b.CUST
  358. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  359. WHERE
  360. a.tgl >= '''+ @mindate +''' AND
  361. DATEDIFF(MINUTE, a.tgl, b.tgl_input) > 30 AND CAST(b.tgl_input AS DATE) = CAST(GETDATE() AS DATE) and a.ORDER_STATUS=''UNPAID''
  362. UNION ALL'
  363.  
  364. --dobel
  365. SET @sql = @sql + '
  366. SELECT
  367. ''Dobel'' AS TYPE,
  368. (SELECT Email from MO..Account WHERE DatabasePathErasoft='''+ @dbname +''') AS EMAIL,
  369. ''' + @dbname + ''' AS DBPATHERA,
  370. a.TGL AS TGL_WEBHOOK,
  371. NULL,
  372. a.CUST,
  373. a.SHOPID,
  374. c.TIDAK_HIT_UANG_R,
  375. b.DUP_NOBUK,
  376. a.ORDERID,
  377. b.DUP_ST,
  378. NULL,
  379. a.JSON AS JSON
  380. FROM [' + @dbname + ']..TABEL_WEBHOOK_SHOPEE a(nolock)
  381. INNER JOIN (
  382. SELECT
  383. y.CUST, y.NO_REFERENSI, COUNT(*) AS DUP,
  384. STUFF((SELECT '','' + x1.NO_BUKTI FROM [' + @dbname + ']..SOT01A x1 WHERE x1.NO_REFERENSI = y.NO_REFERENSI FOR XML PATH('''')), 1, 1, '''') AS DUP_NOBUK,
  385. STUFF((SELECT '','' + x2.STATUS_TRANSAKSI FROM [' + @dbname + ']..SOT01A x2 WHERE x2.NO_REFERENSI = y.NO_REFERENSI FOR XML PATH('''')), 1, 1, '''') AS DUP_ST
  386. FROM [' + @dbname + ']..SOT01A y (nolock)
  387. GROUP BY CUST, NO_REFERENSI
  388. ) b ON a.ORDERID=b.NO_REFERENSI and a.CUST=b.CUST
  389. LEFT JOIN [' + @dbname + ']..ARF01 c(nolock) ON a.CUST=c.CUST
  390. WHERE
  391. a.tgl >= '''+ @mindate +''' AND
  392. a.ORDER_STATUS=''UNPAID'' AND b.DUP > 1
  393.  
  394. UNION ALL'
  395. FETCH NEXT FROM db_cursor INTO @dbname
  396. END
  397.  
  398. -- Remove the last 'UNION ALL'
  399. SET @sql = LEFT(@sql, LEN(@sql) - 10)
  400.  
  401. -- Apply condition as needed
  402. --SET @sql = CONCAT('WITH RES AS (', @sql, ') SELECT * FROM RES WHERE TIDAK_HIT_UANG_R = ''1''')
  403.  
  404. -- Execute the dynamic SQL
  405. EXEC sp_executesql @sql
  406.  
  407. -- Cleanup
  408. CLOSE db_cursor
  409. DEALLOCATE db_cursor
  410.  
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!