Backend URL : https://loantap.in/wp-admin/post.php?post=890218&action=edit
Frontend URL : https://loantap.in/banking/manage-suspense-dealer-disburse/loantap_credit
Main Query :
SELECT txn_set_id as data_id, txn_set_id, account as dealer_code FROM loan_entries WHERE head = 'Dealer' AND (success_ref IS NULL OR success_ref='') AND txn_ref IS NOT NULL AND sublan_id!='LAN' AND field1 ='dealer-downloaded' [if.not_empty env.search.nbfc] AND nbfc='[env.search.nbfc/]' [/if.not_empty] GROUP BY txn_set_id HAVING SUM(credit-debit) > 0
Description :
1) Get all records whose
head is ‘Dealer’
AND success_ref IS NULL
AND txn_ref IS NOT NULL
AND sublan_id is not ‘LAN’
AND field1 is ‘dealer-downloaded’
2) Filter data if NBFC provided in URL
Filter :
SELECT [module.previous_query_no /].*, sum(credit-debit) as debit, lan_id, sublan_id, entry_date, txn_ref, nbfc FROM [module.previous_query_no /] JOIN loan_entries ON [module.previous_query_no /].txn_set_id=loan_entries.txn_set_id WHERE loan_entries.head = 'Dealer' AND (loan_entries.success_ref IS NULL OR loan_entries.success_ref='') AND loan_entries.txn_ref IS NOT NULL AND loan_entries.sublan_id!='LAN' AND loan_entries.field1 ='dealer-downloaded' [if.not_empty env.search.nbfc] AND loan_entries.nbfc='[env.search.nbfc/]' [/if.not_empty] GROUP BY [module.previous_query_no /].txn_set_id
Add Field :
SELECT [module.previous_query_no /].*, object_id as dealer_object_id FROM [module.previous_query_no /] LEFT JOIN common_meta ON [module.previous_query_no /].dealer_code=common_meta.meta_value WHERE common_meta.coll_id='dealer' and common_meta.meta_key='dealer_code'
Description :
Joined to common_meta table on dealer_code and get dealer_object_id to load dealer data
Columns Used in UI :
Common Meta:
dealer_bank_name
dealer_name
dealer_bank_account_no
dealer_bank_ifsc_code
dealer_city
dealer_pincode
Direct:
Sublan_id
Lan_id
txn_ref
txn_set_id
Debit
dealer_code
dealer_object_id
Nbfc
entry_date
External action:
Sublan Id have action link: open transaction page in new window
Update UTR form column
Reset action button
UTR Update:
UPDATE loan_entries SET success_ref = '[template.success_ref/]' WHERE head='dealer' AND account='[template.account /]' AND sublan_id='[template.sublan_id/]' AND (success_ref IS NULL OR success_ref='') AND txn_set_id="[template.txn_set_id/]"
Description :
Update Success_ref Column on matching account, sublan_id, txn_set_id Columns and head=dealer success_ref is NULL
Reset to Back Ready :
UPDATE loan_entries SET field1=NULL WHERE head='dealer' AND account='[template.req.account /]' AND sublan_id='[template.req.sublan_id/]' AND (success_ref IS NULL OR success_ref='') AND txn_set_id="[template.req.txn_set_id/]"
Description :
Update field1 = NULL on matching account, sublan_id, txn_set_id field and head=’dealer’ and success_ref is NULL