PSPCL IFIX ADAPTER NEW API INTEGRATION

Overview

PSPCL iFix Adapter is a java standalone application, which will be running as a cron job to fetch the bills and payments from the PSPCL system and reconcile the same. And finally, publish them to the iFix core system.

Prerequisites

Before proceeding with the configuration, ensure the following pre-requisites are met.

• Java 8

• Postgres DB should be up and running

PSPCL SYSTEM API

Link for details of API https://mail.google.com/mail/u/0/#search/from%3Aarindam.gupta%40egovernments.org/FMfcgzGsmWsNPFpMJZDMKVddtstqSDGZ?projector=1&messagePartId=0.1

CURL to get BILl : at any time there will be a only one bill for a account and it will give you details of bill for any customer at that time .

In Above the hasheddata can be obtained by using encryption details given in PSPCL doc. Please refer that
example:
Hashed Seq: USERNAME|USERPASSWORD|PSPCLACCOUNT|MOBILE|EMAIL|SEARCHBY
plaintext: SOMETHING|SOMETHING123|3000000000|NA|NA|1 (search by 1 means search by account number)
salt: yctHETRuWDJwILgj

Same goes for Getting PSPCL TXN : Note that it will give you any payments done in last 50 days
CURL to GET PAYMENTS :

Features

pspcl-ifix-adapter fetches the pspcl bills and payments from the PSPCL system based on the account number that is mapped in MDMS (can be found in https://github.com/misdwss/mdms-mgramseva/blob/QA/data/pb/pspcl-integration/accountNumberGpMapping.json ). Once will get the bills and payments, the below steps are there to reconcile and publish to the iFix Core system.

• Sort the pspcl bills and payments results based on 'BILL_ISSUE_DATE' and 'TXNDATE' respectively.

For the first time check if any bill is present with the given Account Number in Pspcl adapter DB
If No i.e. no bill with given account number then just add the bill details in the PSPCL adapter DB and send the fiscal event to fiscal event service for bill . Here we will not do nothing for payments.
If yes i.e. we have previous bill details in Pspcl db for given account umber .
: Now check for if the bill details which you got for the account number is same or if its a new bill. we can check this by bill number if its same bill or not(Basically call pspcl db to check if bill exist with same account number and same bill number
If yes: That means we already have the bill details and we have already send the bill fiscal event previously. So don’t send the bill fiscal event to fiscal event service Now check for the payments details received from the PSPCL system. PSPCL send all the transaction made in last 50 days buffer.
firstly, Filter out the payments and keep only payments made after the bill issue date.(i.e. previous bill which you already have in db as well as in the get bill detail Api from PSPCL SYSTEM.
example : if last bill issue date is 15th of may. then take all payments made after 15th of may.
then check for the payments if these payments are already send to fiscal event service .Basically check in pspcl db by comparing the txn id . Check if there is any new payment made or not
if yes: Then save the details of the Payments in PSPCL adapter DB and send the payment fiscal event to fiscal event service.
if No: Then no need to save or send any fiscal event to fiscal event service.
If No: That means we have new bill which got generated. Save the bill details in Pspcl Adapter DB and send fiscal event details to fiscal event service by calculating current month bill (we will calculate the current bill based on the logic below). Now same as above check for payments check. filter out the Payments made after the previous bill issue date which we can get from pspcl DB by order by bill issue date DESC and limit 1. Also filter out Payments made before the current new bill issue Date. Check if there is any new payments made
if yes:
1. Then save payments details in PSPCL adapter DB and send payment fiscal event to fiscal event Service.
2. Then calculate the current month bill from the below logic
Calculated current bill = (Curr_Bill - (Last_Bill - Last_Payments ))(last_payments made between previous bill_issue_date and current_month bill_issue_date ))
Curr_Bill: This is the pspcl bill that we get in pspcl bill result.
Last_Bill: Will get this bill from PSPCL db by sorting result with bill issue date limit it to 1
Last_Payments: Will pick up the record from the PSPCL payment detail table with 'TXNDATE' as a range from 'LAST_BILL_ISSUE_DATE' & 'CURRENT_MOTH_BILL_ISSUE_DATE '.
3. Save bill details which we got from PSPCL system with same bill amount. And send fiscal event bill amount from calculated current bill amount from above logic
Please check the notes and assumptions point mentioned below

if No:
1. No need to save any payment details and send fiscal event as there is no new payments done

2. Then calculate the current month bill from the below logic

Calculated current bill = (Curr_Bill - (Last_Bill - Last_Payments ))(last_payments made between previous bill_issue_date and current_month bill_issue_date ))
Curr_Bill: This is the pspcl bill that we get in pspcl bill result.
Last_Bill: Will get this bill from PSPCL db by sorting result with bill issue date limit it to 1
Last_Payments: Will pick up the record from the PSPCL payment detail table with 'TXNDATE' as a range from 'LAST_BILL_ISSUE_DATE' & 'CURRENT_MOTH_BILL_ISSUE_DATE '.

3.Save bill details which we got from PSPCL system with same bill amount. And send fiscal event bill amount from calculated current bill amount from above logic

Please check the notes and assumptions point mentioned below

• Publish the bill and payment as event type 'Demand' and 'Receipt' respectively with calculated actual bill and payment amount to the iFix Core system.

• Log the success or failure of fiscal events to the DB.

Interaction Diagram

Open

Environment

Below are the list of environment variables :

Configurations and Setup

Update all the configuration in the dev.yaml, qa.yaml, prod.yaml file.

For QA:
https://github.com/misdwss/iFix-DevOps/blob/pspcl/deploy-as-code/helm/environments/pspcl-qa-secrets.yaml
https://github.com/misdwss/iFix-DevOps/blob/pspcl/deploy-as-code/helm/environments/pspcl-qa.yaml here you can change the Cronjob scheduler time

DB DETAILS

PSPCL ADAPTER DB DETAILS
It's in pspcl namespace in QA env :
POSTGRES DB:
 kubectl get pods -n pspcl | grep Playground
kubectl exec -it playground-55d5964454-246gf -n pspcl  bash
psql -h postgres.mgramseva -U egovpg mgramseva_qa_db
Password : Please ask @Arindam Gupta

Table’s Name in PSPCL :

• pspcl_bill_detail Table → holds data and info of the bills fetch from PSPCL System

• pspcl_payment_details Table → hold data and info of payments fetch from PSPCL system

• pspcl_event_posting_details → hold the fiscal event data pushed to Fiscal event service

IFIX FISCAL EVENT SERVICE DB:
It's in ifix namespace in QA env:
POSTGRES DB:

kubectl exec -it playground-b4d7bcdb-n2hl2 -n fix bash
psql -h postgres.ifix -U  ifix_admin ifix_qa_db ifix_uat_db
Password : Please ask @Arindam Gupta

Table’s Name in IFIX:

• eg_ifix_fiscal_event → holds data of fiscal event came from pspcl-adapter

• eg_ifix_amount_detail → holds info of the amount for the fiscal events

FLOW OF DATA TO MGRAMSEVA
PSPCL SYSTEM → PSPCL-IFIX-ADAPTER CRONJOB → FISCAL-EVENT-SERVICE
IFIX-REFERENCE-ADAPTER SCHEDULED JOB → MGRAMSEVA SYSTEM

NOTES AND ASSUMPTION
1.for creating/pushing the date in mgramseva from mgramseva adpater there should be one superuser which can login credential for all tenants in PROD.

2.Bill will be always be zero from pspcl if the advance is more than current bill amount (Asssumption we will not accept negative bill) and we will send the bill details oly if bill amount is greater than Zero.
3.If partial advance is paid than we will not use the logic of
{currentMonthBill- (lastMonthBill- (totalpayments ))} totalPayment is Sum of Payments made between Last and current bill issue date))
if (lastMonthBill- (totalpayments ) is less than zero we will only send the current bill amount as the due amount received from PSPCL system

4. Every tenant should have only one PSPCl account mapped to it.
5. As Discussed with @Satish N , based on the inputs of @Rushang Dhanesha and @Manish Srivastava We had to adopt the approach where assumptions is payments are made every month and payment amount is less or equal to the current month bill amount. This is because we are using mcollect for expenditure.

Document Resources and Links