API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 10th, 2012
139
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.28 KB | None | 0 0
raw download clone embed print report
  1. Add the excel sheet imported directly into the table
  2. Public Sub update_feedback()
  3. Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
  4. Dim rst As New ADODB.Recordset
  5. Dim eno As Long
  6. Dim fname As String
  7. Dim fname1 As String
  8. Dim fno1 As Integer
  9. Dim fno As Integer
  10. Dim mstr As String
  11. Dim flds() As String
  12. Dim valid_data As Boolean
  13. Dim errstr As String
  14. Dim errcnt As Integer
  15. Dim pickupid As Long
  16. Dim cancel_rec As String
  17. Dim bkno As Double
  18. Dim sReceipt_no As Long
  19. eno = 1
  20. 'CommonDialog1.ShowOpen
  21. 'fname = CommonDialog1.FileName
  22. rst.Open "select * from upload_schedule where UPPER(status)='PENDING'", db.ConnectionString, adOpenDynamic, adLockOptimistic
  23. Do While Not rst.EOF
  24. 'fname = "d:redefine" & nvl(rst!file_name)
  25. fname = "e:field_crmupload_datafeedback" & nvl(rst!file_name)
  26. fno = FreeFile
  27. Open fname For Input As #fno
  28. If Left(Right(fname, 4), 1) = "." Then
  29. fname1 = Mid(fname, 1, Len(fname) - 4) & "_err.csv"
  30. Else
  31. fname1 = fname & "_err.csv"
  32. End If
  33. fno1 = FreeFile
  34. Open fname1 For Output As #fno1
  35. errcnt = 0
  36. Line Input #fno, mstr
  37. Print #fno1, mstr
  38. Do While Not EOF(fno)
  39. Line Input #fno, mstr
  40. mstr = Replace(mstr, ",", "|")
  41. If InStr(mstr, "|") > 0 Then
  42.  
  43.  
  44. flds = Split(mstr, "|")
  45.  
  46.  
  47. valid_data = True
  48. errstr = ""
  49. cancel_rec = ""
  50. bkno = 0
  51. For i = 0 To UBound(flds)
  52. Select Case i
  53. Case Is = 2
  54. If IsNull(flds(i)) Or flds(i) = "" Then
  55. valid_data = False
  56. errstr = errstr & IIf(errstr = "", "", "|") & "LOAN NO"
  57. End If
  58. Case Is = 5
  59. If IsNull(flds(i)) Or flds(i) = "" Then
  60. valid_data = False
  61. errstr = errstr & IIf(errstr = "", "", "|") & "AGENCY NAME"
  62. End If
  63. Case Is = 7
  64. If IsNull(flds(i)) Or flds(i) = "" Then
  65. valid_data = False
  66. errstr = errstr & IIf(errstr = "", "", "|") & "FOS NAME"
  67. End If
  68. Case Is = 9
  69. If IsNull(flds(i)) Or flds(i) = "" And flds(7) = "" Then
  70. valid_data = False
  71. errstr = errstr & IIf(errstr = "", "", "|") & "FEEDBACK DATE"
  72. End If
  73. Case Is = 11
  74. If IsNull(flds(i)) Or flds(i) = "" And flds(7) = "" Then
  75. valid_data = False
  76. errstr = errstr & IIf(errstr = "", "", "|") & "FEEDBACK"
  77. End If
  78. Case Is = 12
  79. If IsNull(flds(i)) Or flds(i) = "" And flds(7) = "" Then
  80. valid_data = False
  81. errstr = errstr & IIf(errstr = "", "", "|") & "REMARK"
  82. End If
  83. End Select
  84.  
  85. '-------------- PAYMENTS
  86. If Not IsNull(flds(13)) And flds(13) <> "" Then
  87.  
  88. If IsNumeric(flds(13)) Or Abs(Val(flds(13))) > 0 Then
  89. Select Case i
  90. Case Is = 14
  91. If IsNull(flds(i)) Or flds(i) = "" Then
  92. valid_data = False
  93. errstr = errstr & IIf(errstr = "", "", "|") & "MOD OF PAYMENT"
  94. End If
  95. Case Is = 15
  96. If InStr(UCase(flds(14)), "CHQ") > 0 Or InStr(UCase(flds(14)), "CHEQ") > 0 Then
  97. If IsNull(flds(i)) Or flds(i) = "" Then
  98. valid_data = False
  99. errstr = errstr & IIf(errstr = "", "", "|") & "CHQ NO"
  100. End If
  101. End If
  102. Case Is = 16
  103. If InStr(UCase(flds(14)), "CHQ") > 0 Or InStr(UCase(flds(14)), "CHEQ") > 0 Then
  104. If IsNull(flds(i)) Or flds(i) = "" Then
  105. valid_data = False
  106. errstr = errstr & IIf(errstr = "", "", "|") & "CHQ DATE"
  107. End If
  108. End If
  109. Case Is = 19
  110. If IsNull(flds(i)) Or flds(i) = "" Then
  111. valid_data = False
  112. errstr = errstr & IIf(errstr = "", "", "|") & "RECEIPT NO"
  113. End If
  114. rs.Open "select book_no from receipt_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and " & flds(i) & " > = receipt_no_from and " & flds(i) & " <= receipt_no_to", db.ConnectionString
  115. If rs.EOF Then
  116. valid_data = False
  117. errstr = errstr & IIf(errstr = "", "", "|") & "INCORRECT RECEIPT NO."
  118. Else
  119. bkno = rs!book_no
  120. End If
  121. rs.Close
  122. Case Is = 20
  123. If IsNull(flds(i)) Or flds(i) = "" Then
  124. valid_data = False
  125. errstr = errstr & IIf(errstr = "", "", "|") & "RECEIPT DATE"
  126. End If
  127. End Select
  128. End If
  129. End If
  130. If (UCase(flds(15)) = "CANCEL" Or UCase(flds(15)) = "LOST") And IsNull(flds(19)) Then
  131. cancel_rec = "CANCEL"
  132. errstr = ""
  133. End If
  134. Next i
  135. If cancel_rec <> "CANCEL" Then
  136. rs.Open "select count(0) from data_field01 where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "'", db.ConnectionString
  137. If Val(rs.fields(0)) = 0 Then
  138. valid_data = False
  139. errstr = errstr & IIf(errstr = "", "", "|") & "LOAN NO " & flds(2) & " is invalid"
  140. End If
  141. rs.Close
  142. If errstr <> "" Then
  143. errcnt = errcnt + 1
  144. Print #fno1, mstr
  145. End If
  146. End If
  147. Else
  148. valid_data = False
  149. errstr = "Missing field seperator |"
  150. errcnt = errcnt + 1
  151. End If
  152.  
  153. Loop
  154. Close #fno
  155. Close #fno1
  156.  
  157. If errcnt = 0 Then
  158. '-------------------- update data
  159. fno = FreeFile
  160. Open fname For Input As #fno
  161.  
  162.  
  163.  
  164. Line Input #fno, mstr
  165. Do While Not EOF(fno)
  166. Line Input #fno, mstr
  167. mstr = Replace(mstr, ",", "|")
  168. flds = Split(mstr, "|")
  169.  
  170. rs.Open "select max(entry_no) from feedback_dump where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "'", db.ConnectionString
  171. If Not rs.EOF Then
  172. eno = nNV(rs.fields(0)) + 1
  173. End If
  174. rs.Close
  175.  
  176. rs.Open "select * from feedback_dump where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "'", db.ConnectionString, adOpenDynamic, adLockOptimistic
  177.  
  178. rs.AddNew
  179. rs!region_id = mvarregion_id
  180. rs!cost_id = mvarcost_id
  181. rs!portfolio_id = mvarportfolio_id
  182. rs!portfolio_type = mvarportfolio_type
  183. rs!entry_no = eno
  184. rs!entry_date = Format(Now, "mm/dd/yyyy")
  185. rs!account_type = flds(1)
  186. rs!Lan_no = flds(2)
  187. rs!customer_name = flds(3)
  188. rs!pickup_date = flds(4)
  189. rs!agency_name = flds(5)
  190. rs!agency_location = flds(6)
  191. rs!fos_name = flds(7)
  192. rs!fos_contact_no = flds(8)
  193. rs!disposition_date = flds(9)
  194. rs!agency_disposition = flds(10)
  195. rs!disposition_code = flds(11)
  196. rs!remark = flds(12)
  197. rs!collected_amount = Val(flds(13))
  198. rs!mode_of_payment = flds(14)
  199. rs!cheque_no = flds(15)
  200. rs!cheque_date = flds(16)
  201. rs!bank_name = flds(17)
  202. rs!branch_name = flds(18)
  203. rs!receipt_no = Val(flds(19))
  204. rs!receipt_date = flds(20)
  205. rs!deposited_date = flds(21)
  206. rs!deposited_bank = flds(22)
  207. rs!customer_contact_no = flds(23)
  208. rs!region = mvarregion_id
  209. rs!product = flds(1)
  210. rs!status = "Pending"
  211. rs!entry_user_id = mvaruser_id
  212. rs!batch_no = batchno
  213. rs.Update
  214. rs.Close
  215.  
  216. TrailId = 1
  217. rs1.Open "select * from pickup_trail_sequence where region_id = " & mvarregion_id & " and billmonth = convert(Char(6), getdate(), 112) ", db.ConnectionString, adOpenDynamic, adLockOptimistic
  218. If Not rs1.EOF Then
  219. pickupid = rs1!trail_id + 1
  220. rs1!trail_id = pickupid
  221. rs1.Update
  222. Else
  223. rs1.AddNew
  224. rs1!region_id = gRegion_id
  225. rs1!billmonth = Format(gDisp_date, "yyyymm")
  226. rs1!trail_id = 1
  227. pickupid = 1
  228. rs1!Prefix = Format(gDisp_date, "MMM")
  229. rs1.Update
  230. End If
  231. rs1.Close
  232.  
  233. '---------- fos details
  234. If nvl(flds(7)) <> "" Then
  235. rs1.Open "select pickup_id from pickup_dtl where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and convert(char(8),pickup_date,112) = '" & Format(flds(4), "yyyymmdd") & "'", db.ConnectionString
  236. If Not rs1.EOF Then
  237. pkid = rs1!pickup_id
  238. Else
  239. pkid = 0
  240. End If
  241. rs1.Close
  242. rs1.Open "select * from agency_executive where upper(executive_name) = '" & UCase(flds(7)) & "' and region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and agency_id = " & rst!agency_id, db.ConnectionString, adOpenDynamic, adLockOptimistic
  243.  
  244. If rs1.EOF Then
  245. If rs.State = 1 Then
  246. rs.Close
  247. End If
  248. rs.Open "select max(executive_id) from agency_executive where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and agency_id = " & rst!agency_id, db.ConnectionString
  249. rs1.AddNew
  250. rs1!region_id = mvarregion_id
  251. rs1!cost_id = mvarcost_id
  252. rs1!agency_id = rst!agency_id
  253. rs1!executive_id = nNV(rs.fields(0)) + 1
  254. rs1!Executive_name = UCase(flds(7))
  255. rs1!contact_no = nvl(flds(8))
  256. rs1!date_of_joining = Now
  257. rs1.Update
  258.  
  259. db.Execute "update data_field01 set executive_id = " & nNV(rs.fields(0)) + 1 & " where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and pickup_id = '" & pkid & "'"
  260. rs.Close
  261. Else
  262. db.Execute "update data_field01 set executive_id = " & rs1.fields(0) & " where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and pickup_id = '" & pkid & "'"
  263. db.Execute "update agency_executive set contact_no = '" & nvl(flds(8)) & "' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and agency_id = " & rst!agency_id & " and executive_id = " & rs1!executive_id
  264. End If
  265. rs1.Close
  266. End If
  267. '------------- field disposition details
  268. If rs.State = 1 Then
  269. rs.Close
  270. End If
  271. rs.Open "select * from dispositions where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no = '" & flds(2) & "' and upper(disposition_code) = '" & UCase(flds(10)) & "' and convert(char(8),disposition_date,112) = '" & Format(flds(9), "yyyymmdd") & "'", db.ConnectionString, adOpenDynamic, adLockOptimistic
  272. If rs.EOF Then
  273. rs.AddNew
  274. rs!region_id = mvarregion_id
  275. rs!cost_id = mvarcost_id
  276. rs!portfolio_id = mvarportfolio_id
  277. rs!portfolio_type = mvarportfolio_type
  278. rs!Lan_no = flds(2)
  279. rs!trail_id = pickupid
  280. rs!disposition_code = flds(10)
  281. rs!disposition_date = flds(9)
  282. rs!remark = flds(12)
  283. rs!userid = mvaragency_id
  284. rs!dialed_no = "FIELD"
  285. rs.Update
  286.  
  287. End If
  288. rs.Close
  289. '------------------- receipt details
  290. If Not IsNull(flds(13)) And flds(13) <> "" Then
  291.  
  292. If IsNumeric(flds(13)) Or Abs(flds(13)) > 0 Then
  293. rs.Open "select * from receipt_detail where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and receipt_no = " & Val(flds(19)), db.ConnectionString, adOpenDynamic, adLockOptimistic
  294. If rs.EOF Then
  295. rs.AddNew
  296. rs!region_id = mvarregion_id
  297. rs!cost_id = mvarcost_id
  298. rs!portfolio_id = mvarportfolio_id
  299. rs!portfolio_type = mvarportfolio_type
  300. rs!Lan_no = flds(2)
  301. rs!receipt_no = flds(19)
  302. rs!issued_date = flds(20)
  303. rs!amount = Val(flds(13))
  304. rs!payment_mode = nvl(flds(14))
  305. rs!entry_date = Now
  306. rs!instrument_no = flds(15)
  307. If Not IsNull(flds(16)) And flds(16) <> "" Then
  308. rs!instrument_date = flds(16)
  309. End If
  310. rs!agency_id = mvaragency_id
  311. rs1.Open "select a.book_no,receipt_no_from,receipt_no_to from receipt_master a, receipt_detail b where a.region_id = " & mvarregion_id & " and a.cost_id = " & mvarcost_id & " and a.portfolio_id = " & mvarportfolio_id & " and a.portfolio_type = '" & mvarportfolio_type & "' and " & flds(19) & " >= receipt_no_from and " & flds(19) & " <= receipt_no_to and a.book_no = b.book_no", db.ConnectionString
  312. If Not rs1.EOF Then
  313. bkno = rs1!book_no
  314. sReceipt_no = rs1!receipt_no_from
  315.  
  316. End If
  317. rs1.Close
  318. rs!book_no = bkno
  319.  
  320. rs.Update
  321. rs.Close
  322.  
  323. '------------- missing receipt control check
  324. If flds(19) > sReceipt_no Then
  325. rs1.Open "select * from receipt_detail where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and receipt_no = " & Val(flds(19) - 1), db.ConnectionString, adOpenDynamic, adLockOptimistic
  326. If rs1.EOF Then
  327. cancel_rec = "Missing receipt details ..." & flds(19) - 1 & "/" & bkno
  328. End If
  329. rs1.Close
  330. mailto = ""
  331. ccto = ""
  332. bccto = ""
  333. rs1.Open "select * from email_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and upper(process_name) = 'FEEDBACK UPDATE'", db.ConnectionString
  334. If Not rs1.EOF Then
  335. mailto = nvl(rs1!mail_to)
  336. ccto = nvl(rs1!cc_to)
  337. bccto = nvl(rs1!bcc_to)
  338. End If
  339. rs1.Close
  340. rs1.Open "select agency_name from agencymaster where agency_id = " & rst!agency_id, db.ConnectionString
  341. If Not rs1.EOF Then
  342. db.Execute "EXEC msdb.dbo.sp_send_dbmail @profile_name='Test Mail', @recipients='" & mailto & "', @copy_recipients = '" & ccto & ";" & bccto & "', @subject='MISSING RECEIPT DETAIL',@body= 'Payment detail missing from agency : " & UCase(rs1!agency_name) & "..." & cancel_rec & "'"
  343. End If
  344. rs1.Close
  345. End If
  346.  
  347. End If
  348. '------------- missing receipt control check end
  349.  
  350. If Val(flds(13)) > 0 Then
  351.  
  352. Select Case UCase(flds(14))
  353. Case Is = "CASH"
  354. db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'"
  355. Case Is = "CHEQUE"
  356. db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'"
  357. Case Is = "CHQ"
  358. db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'"
  359. Case Is = "DD"
  360. db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'"
  361. Case Is = "CARD"
  362. db.Execute " update data_dump01 set mis_status='COLLECTED' where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and portfolio_id = " & mvarportfolio_id & " and portfolio_type = '" & mvarportfolio_type & "' and lan_no='" & flds(2) & "'"
  363. Case Is = "CANCEL"
  364. Case Is = "LOST/STOLEN"
  365. End Select
  366.  
  367. End If
  368.  
  369. End If
  370. End If
  371. Loop
  372. Close #fno
  373. rst!status = "Completed"
  374. rst.Update
  375. mailto = ""
  376. ccto = ""
  377. bccto = ""
  378. rs1.Open "select * from email_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and upper(process_name) = 'FEEDBACK UPDATE'", db.ConnectionString
  379. If Not rs1.EOF Then
  380. mailto = nvl(rs1!mail_to)
  381. ccto = nvl(rs1!cc_to)
  382. bccto = nvl(rs1!bcc_to)
  383. End If
  384. rs1.Close
  385. rs1.Open "select agency_name from agencymaster where agency_id = " & rst!agency_id, db.ConnectionString
  386. If Not rs1.EOF Then
  387. db.Execute "EXEC msdb.dbo.sp_send_dbmail @profile_name='Test Mail', @recipients='" & mailto & "', @copy_recipients = '" & ccto & ";" & bccto & "', @subject='FEEDBACK UPDATE',@body= 'Upload data successful for agency : " & UCase(rs1!agency_name) & " ... File Name: " & rst!file_name & "'"
  388. End If
  389. rs1.Close
  390. Else
  391. '---------------- send error mail
  392. mailto = ""
  393. ccto = ""
  394. bccto = ""
  395. rs1.Open "select * from email_master where region_id = " & mvarregion_id & " and cost_id = " & mvarcost_id & " and upper(process_name) = 'FEEDBACK UPDATE'", db.ConnectionString
  396. If Not rs1.EOF Then
  397. mailto = nvl(rs1!mail_to)
  398. ccto = nvl(rs1!cc_to)
  399. bccto = nvl(rs1!bcc_to)
  400. End If
  401. rs1.Close
  402. rs1.Open "select agency_name from agencymaster where agency_id = " & rst!agency_id, db.ConnectionString
  403. If Not rs1.EOF Then
  404. db.Execute "EXEC msdb.dbo.sp_send_dbmail @profile_name='Test Mail', @recipients='" & mailto & "', @copy_recipients = '" & ccto & ";" & bccto & "', @subject='FEEDBACK UPDATE',@body= 'Upload data error for agency : " & UCase(rs1!agency_name) & " ... File Name: " & rst!file_name & errstr & "'"
  405. End If
  406. rs1.Close
  407. End If
  408. rst!status = "Completed"
  409. rst.Update
  410.  
  411. rst.MoveNext
  412. Loop
  413. rst.Close
  414.  
  415. 'rs.Close
  416. End Sub
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!