Documentation ============= How does the business work? --------------------------- Borrowers come to moneylenders (ML) to borrower money. Lenders create an MLCB (third party credit agency) application. The application is sen't to MLCB to get info about the borrower. If MLCB rejects a loan for any reason the loan has to be altered to fit or the borrower is returned. If MLCB returns approved, the loan is created using the Give Loan Form (GLF) The MLCB procedure is a recent government requirement. It didn't exist always. Was implemented in 2016. Every loan has a payment schedule (PS) Borrowers have to pay based on the PS Repayments are recorded when the borrower pays back the lender Sometimes lenders call the borrower to followup on the payment. These followups are recorded in the calls table and displayed in the followup module. In case of late repayments fees can be applicable such as late fee (LF), late interest (LI), etc demand letters (DL) can be sent to the borrower if they are not paying Once all the repayments are done the loan is closed Sometimes the loan can be consolidated - this mean the PS of the loan is altered for a new payment structure for the borrower. Sometimes the loans are declared bad debt. This is when the lender knows that the money is never coming back Sometimes the bad debt borrower might pay later. In such a case a bad debt recovery entry is made In some cases the loan might be closed as a goodwill. The goodwill amount is basically a discount to the borrower. Sometimes borrowers can be blacklisted. So that the system know the next time the borrower is trying to get a loan. Sometimes MLs can have agents who bring customers to the ML. They are like resellers. The loans created by agents belong to them in the loan application. The lender can view statistics of agents this way. As to which agent is doing what amount of business. MLs have to sent borrower statements to borrowers every 6 months. They are similar to bank statements we get from our banks. MLs also have to submit a snapshot of their quarterly business to the govt. using the ML Statement report (MLS) The above reportings are govt. rules and need to be accurate. If not, they can have legal consequences for the ML. What's the folder structure of the codebase? -------------------------------------------- app/ contains all the code files - controllers, views, helpers, libraries, etc. This is where most of the coding happens. framework/ folder contains base code not to be touched 99.9999% of the times ins/ contains lender specific files such as config, backups, pdfs, etc What does the framework do? --------------------------- framework/ contains code to do some commonly used functionalities to speed up development CRUD modules are created by extending the form class such as "class repayment extends form {}" Reference: app/controllers/repayment.php Various variables are defined in the constructor which is used to generate several functionalities. view_fields defines fields that will be visible on the view page of the module view_fields_a defines fields that will be visible in the info button view_code defines parameters to construct the SQL query required to generate view_fields and view_fields_a input_fields configures the create and edit forms Search, Reporting, Charting, Export, Import functionalities are automatically created based on the view_fields and view_fields_a Normal modules are also supported by using the controller class like this "class payment_due extends Controller" Ref: app/controllers/payment_due.php Normal modules act like normal MVC setup. Exception: framework/ doesn't support models. So helpers are used instead. For instance loan_helper does a lot of DB work. Admin functionalities Backup - self explanatory User roles and permissions - to control what each role can do in the system DB tables related to framework functionality All tables with the prefix `vt_` are tables related to framework operations and shouldn't be touched unless extremely necessary. Permissions - `vt_classes`, `vt_methods`, `vt_roles`, `vt_users`, `vt_user_roles`, `vt_permissions` Dashboard - `vt_dashboards`, `vt_dashboard_perms`, `vt_dashlets` Data Sharing Rules (DSR) - `vt_sharing_rules` How are new systems migrated? ----------------------------- MLs often switch from competiting softwares to our loan application system. In such a case the data from the competiting software is migrated accurately to our database through proper mapping. The source data can come from a variety of sources - excel files, Microsoft DBs, MySQL DBs, MS Access DBs, etc. The general process is the extract data from the source DB and put it in a fresh loanapp installer MySQL DB. The installer DB is an empty DB which can run the loanapp. I have mailed it to Nathan and Marcus. A migration script is run which takes data from the imported data put in the installer DB and then maps that data in the loanapp DB. There is a system called autocount which has data corruption when migration is done. Check Processes(Loanapp) project to look at the documentation for it. Most common sources of migration: PGM - sbc_migration_new.sql Microsoft - dy_migration.sql Excel - many files present in misc/ Desktop - migration2.sql Check the entire flow of a PGM migration I did below somewhere in this file. General migration flow ---------------------- 1. Put all the foreign system's data in a mysql DB. Generally this is done using PMA. For dy_migration.sql the code contains SQL commands to import the files as well. 2. import the loanapp tables from the installer file in that DB. 3. Run a patch to map and place data from the foreign system's tables to the loanapp tables 4. Login > run delta to upgrade the DB to latest > solve diagnostics 5. Do some checks to make sure migrated data looks fine. Compare reports of figures or total row count, etc. How does permission system work ------------------------------- Every URL is in MVC format. index.php/controller_name/function_name Whenever a new function is created it's restricted for non-admins. Admins can access any URL by default. For non admins the permission has to be enabled to allow them to access that URL. Permission grid entry This is done from the system invoking superadmin priviledges [ask nathan/marcus how to invoke]. If the controller is new, a class needs to be created (developer > users and security > classes) If the function is new, a method needs to be created (developer > users and security > methods) The permission grid for the role has to be updated using the "generate permissions" button in roles module. The permission grid is then to be checked using the "set permissions" button in the roles module. Auto permissions These are defined in app/config/rbac.php under the 'group_rbac_methods' config. This basically means tieing the permssion of a URL to another URL. So payment_due.php/create_repayment can be tied to repayment/add This prevents updating the permission grids for all lenders Free permissions These are defined in app/config/rbac.php under the 'no_rbac_controllers' or 'no_rbac_methods' config This is used for URLs which need open access. Example - delta, api connections, patch urls, etc. Used less frequently. How does Data Sharing Rules (DSR) work -------------------------------------- DSR is important to give access of data that belongs to one user to another user. Eg. When a subagent is created under an agent, he doesn't have access to the loans created by the agents by default. So a DSR might be created to grant it. DSR can be defined role-to-role, user-to-user, role-to-user, user-role. It's defined in Menu > Developers > User & Security > Sharing Rules. Note: When adding a new rule - seniors allowed/juniors visible fields are not functional. What's the history of the system? --------------------------------- The loan application was started based on a desktop application in the initial years. The database design of the desktop application still has it's structure in our system. Eg. `vouchers` and `ledgers` are tables that were directly taken from the desktop system and are still in use How do `ledgers` work? ---------------------- Ledgers are created for each borrower and bank as soon as they are created. Ledgers link back to the borrowers and books module through borrower_id and book_id fields. If a ledger is missing for any bank or borrower it must be created. There is a diagnostic to catch borrowers without ledgers. Apart from books and borrowers, normal ledgers can also exist which are not related to books or borrowers. These ledgers are used in places like non loan transactions. For eg. Interest, Advertisement expenses, etc. Ledgers are used for accounting purposes for example in the vouchers table. And from the vouchers table reports like trial balance, profit loss are generated How do audit logs work? ----------------------- Every user activity is automatically logged in `vt_audit_log` by framework and is used to create audit reports to check user activity. This is required to track what user does what in situations like data breach, mistakes, bug tracing, etc vt_audit_log becomes huge very fast, so a cached version is maintained in `audit_report` `audit_report` is also used in some modules to check who is the creator of an entry (loans, repayments, followups, etc). How do diagnostics work? ------------------------ Diagnostics are used to trace issues in the system which can have negative effects. These issues can be because of development lapses, wrong data entry, migration bugs, etc. The purpose of diagnostics is to trace affected entries quickly and fix them to reduce the issues with the system Diagnostics should be updated regularly based on new bugs that are found and new tracing SQLs that are used while debugging. This reduces future work. Diagnostics also contain autofixes (sql queries) which are automatically triggered when the page is loaded in the app. This is done only when the fix is tried and tested and sure to work. It reduces the work of going into each lender's system to apply the patch. The diagnostic items also contain normal fixes which are just variables to note down a potential fix for an entry. The SQL is then triggered manually. What's the point of `vouchers`? (Accounting knowledge/background makes understanding this easy) ----------------------------------------------------------------------------------------------- `vouchers` was inherited from the desktop system which was used as a reference in the initial years to build the loanapp. This table contains a reflection of all financial entries in one single table. It's used in reports such as balance sheet, trial balance, profit loss, MLS, etc It's mostly for accounting/reporting purpose. Different kinds of monetary entries being captured in a single table makes it easier to build reports, etc. For each transaction a voucher entry is created. Disbursements, repayments, receipts, payments, book transfers, account transfers. Vouchers are created to keep a single-table-record of all financial transactions which can be later used to generate accounting reports like TB and PL Fields in the table: pay_type: D = Disbursements, R = Repayments, O = others(NLTs) reference_number: the id of disbursements/repayments table entry to which the voucher is related. entry_mode: M (Special entries), P = Payments, T (Transfers = Book/Account Transfers), R (Receipts) ledger_id/book_ledger_id: the 2 ledgers involved in the entry parent_voucher_id: used for book and account transfers where 2 entries are created for each record. The second entry is linked to the first entry using the parent_voucher_id. The second entry is generally a reverse of the first entry. How to create charts? --------------------- There are 2 ways to create charts - custom dashlets and report creator. Custom dashlets are coded in custom_dashlet.php The function name is used to access the URL to check the dashlet Report creator is available in any CRUD module under Actions > Reporting The reporting dialog shows a lot of options. A nice way to understand the meaning of all options is to click on the title of every dashlet on the dashboard and land on a charts page. Then click on Actions > Reporting to see how the chart is configured. The reporting dialog works with the fields defined in view_fields and view_fields_a so sometimes to create a chart a new field might be required to be added. How to create statistics? ------------------------- Statistics are the figures on the top left section of the dashboard. They are added to the dashboard through actions > add to dashboard option in any CRUD module. It's basically a count or sum of some specific field in a CRUD module. The specific field needs to be added using view_field/_a if it doesn't exist. They can also be injected via JS usting custom_js.php if the actions > add to dashboard functionality doesn't satisfy the requirement. custom_dashlet does NOT work for statistics. Can apply search/advanced search before adding to dashboard if you want a statistic on a limited number of rows only. How does historical data work? ------------------------------ Certain information about borrowers are saved on a historical basis. This is done to make sure that if the borrower takes another loan with different details, the details don't override the previous loan's data. In this case the latest information entered for a borrower in the GLF is maintained in `borrowers`. The historical data is maintained in `disbursement_contact_details` Data about the borrower saved in `disbursements` automatically works as historical data. How are borrowers and corporate borrowers work? ----------------------------------------------- Every corporate borrower is saved in `borrowers_corporate` This table has a field called borrower_id through which it's linked to the `borrowers` table `borrowers` contains an entry which is of the director of the corporate borrower generally Entries in `borrowers` are always of an individual with a `uid` Corporate Borrowers don't have uids they have incorporation_number. Application life cycle ---------------------- 1. Life cycle for application wich is integrated with MLCB - Check next heading - mentioned there. 2. Life cycle of application without MLCB - Not allowed anymore - MLCB is compulsory for SG. MLCB ---- NOTE: MLCB had a recent upgrade froom 3.9 to 4.6 where the codebase was enhanced to support both versions. It's WIP so there might be bugs in the code and the documentation may lack certain info. So feel free to edit the code in the best way required but test it properly. NOTE: Even I don't have a thorough understanding of the MLCB end-to-end workings because its mostly on a business operations level so Nathan/Marcus can make things more clear in cases of doubt. If there is a conflict between their version and the documentation below, their version is to be considered correct. For the business process of a disbursement check "How does the business work?" 1. ML creates MA for every borrower. Then the ML can download 3 reports for it (as shown in the CM and in the MA add form) 2. Once the MA report downloaded the lender can disburse the loan through the GLF. 3. In 4.6 there is support for multiple applicants - so the lender needs to download at least the mandatory report for all the applicants without which the MA won't appear in the dropdown in GLF. 4. The multiple applicant mandatory report download feature is WIP and was partially tested only. Might need to look again and change the code if any flaws found. 5. Before 4.6, in 3.9 only one applicant was filled up in the MA and as soon as the mandatory report was downloaded for that applicant the loan appeared in the GLF to be disbursed. There are (or should be) 4 API connections: 1. UAT 3.9 2. UAT 4.6 3. LIVE 3.9 4. LIVE 4.6 MLCB does NOT provide all API connections simultaneously so in cases of bugs in a specific item from the 4 we ask them to switch the end points which is a slow process because of reasons. Repayments are also submitted to MLCB. When a repayment is deleted a reverse submission is done. For repayment edits - Repayment edits are not allowed any more. The repayment is deleted and then re-entered. This leads to a reverse submission for the deleted entry and then a new submission for the new one. With every repayment a pay status PaySTT is submitted for the loan. It's calculated by loan_helper.php > get_mlcb_paystt() I have written the logic for the function above the function itself. If possible please add a brief document about the framework. Give us the link if there is any online documentation available ---------------------------------------------------------------------------------------------------------------------------- The framework folder should not be touched. In the past several years it was only touched < 3 times. If it's absolutely necessary one can override a framework file by creating the same file in app/ The code is such that the file from app/ is checked first compared to framework/ The primary purpose of the framework is to provide a quick creation of CRUD interfaces for modules. For instance repayment.php - index.php/repayment It also provides some readymade feaures such as login, user management, roles and permissions, etc Prashant(11-10-17): is this custom framework or opensource framework. If opensource then please let me know the documentation url. This will helpfull for future. No, it's a propreitary framework made several years ago. Cronjob ------- 1. what are the different type of functionality associated with cronjob? Cronjobs is used to trigger certain functionalities on the server at certain times. app/res/cronjobs.php is the file where the function names to be triggered are mentioned along with the cron frequency. Prashant(11-10-17): Can you list the functionality controlled by cronjob? All the functionalities controlled by cronjob can be checked in the cronjob.php file mentioned above. 2. how we can set it and what we need to take care while setting the cronjob? First of all make sure that the scheduler option is enabled in settings and set to run at * * * * * What functionality associated with Admin->Deployment? ----------------------------------------------------- This feature was built by Lorie to help lenders deploy their system to latest on their own. It was supposed to be connected to DHQ but was never put in production. Menu based/Sitemap overview --------------------------- Dashboard MLCB Application Form - Form used to record basid information to submit to MLCB for approval New Give Loan - Form used to give out loans Collect Money Records - Simple views of records for browsing and searching Application view - loans Repayment view - repayments Demand Letter - DL records HR Letter - Prints related to loans - not sure of business use Caveat Letter - Prints related to loans - not sure of business use Management Reports - generate various excel/pdf/html reports form the data in the application Transactions - these are non loan related transactions Receipt - record entries of receipts Payment - entries of payments eg. advertisement expense Book Transfer - entries of transfer of money between 2 books of the same lender. eg. cashbook to bankbook $1000 Account Transfer - entries of transfer between 2 ledgers/accounts. Not used much. eg. capital account to salary account transfer Bad Debt Recovery - entries of repayments from loans which were previously declared as bad. Audit Diagnostics - lets check known issues in a system to fix bugs. Todays Progress - Shows activity of all users in the system from audit logs Bank Review - Lenders upload bank statements in csv/excel format and do a side-by-side comparison with book reports generated from the system to make sure both match. MLCB Log - All the entries/logs of communication/submissions between system and MLCB server. Masters - Minor modules Borrower Rejected Borrower Online Application Agent - Used to create agents. Manage Users module is NOT used to create agents because agents need an additional entry in `agents` apart from `vt_users` and have some extra fields to fill up. Debt Collector Loan Plan - These are used to autopop multiple fee related fields in the GLF using a single dropdown. MLs have specific loan plans they keep reusing. Book Ledger Blacklist Reason Consolidation List - List of loans which were consolidated to change the PS. Corporate Borrower - View of corporate borrowers. CBs are NOT added from this module. This is just a view. Giro Upload - Not sure, someone else coded this Admin Deployment - Not functional I think Backup Database Backup Borrower images User & Security Manage Roles Manage Users Allowed IP - I don't think this is used much. VPN is used to implement IP permissions for access. Dashlet Clear Server Cache - Erase server cache of JS/CSS files. Done after updates to theme or JS or CSS if the system still shows the old version Email Settings SMTP server Email Profile Settings - general settings which are lender specific. Dash Notifications - Not sure, someone else coded this SMS - Not sure, someone else coded this SMS Payment Due Report SMS Closed Loan SMS Logs SMS Template CRUD Module Actions menu ------------------------ Bookmark Page Not used Advanced Search This is used generally to apply multiple criterias to search the view page of any CRUD module. The search options are pretty self explanatory. Reporting Render charts/tables Field 1: X axis of the chart Value field: Y axis of the chart Value Limit: Limit number of entries in the table or the number of segments in a chart. Takes an integer. Eg. 5. 1D Cumulation: Used to create single segment charts. Eg. Monthly disbursements. 2D Cumulation: Used to create multi-segment charts. Eg. Monthly disbursements newloan vs reloan. Field 2 is used for creating 2D Cumulations Filter Criteria: This is a replication of the advanced search box, which can be used to segment/narrow down the data based on various criterias. Once a chart is rendered, it also shows a tabular data. Actions > Add to dashboard can be used on this page to add the chart or table to the dashboard. Note: In the add to dashboard form select Generic Dashboard as it's the default one. Suggestion: Click on the title of each chart/table on the dashboard and check the reporting box on the page you land on to understand how that chart was created. Add to Dashboard Triggered from view page: Add statistics to Dashboard (Check: How to create statistics?) Triggered from cumulation page: Add chart/table to Dashboard Export Data Simple way to export data seen in the CRUD view as csv or excel Import Data Not used Configure Self explanatory settings for the CRUD module. Used very less. Create Alert Not used CRUD controller constructor configurations ------------------------------------------ CRUD controllers are the ones which extend the form class: "class repayment extends form {}" The constructor function of these controllers can have the following configurations: $this->set_config $this->context_menu $this->bulk_action $view_fields; $this->set_view_fields($view_fields); $view_fields_a; $this->set_view_fields_a($view_fields_a); $this->view_code $input_fields; $this->set_input_fields($input_fields); $this->enable_ro(); How Row level Ownership works ----------------------------- Every loan is owned by a user. `vt_user_id` in `applications` is what defines it. For non admins, loans and related entries like PS/R/etc should only be visible to the people who own it, or have been shared with. 1. Definition in CRUD modules: You will see code like this in application.php/repayment.php/etc - if(session_get('username') && !is_admin() && !is_root()) $this->enable_ro('vt_user_id'); vt_user_id is defined in the select param of $this->view_code. enable_ro is a function of the form class coming from framework which applies filtering based on the field vt_user_id provided to it 2. Using in custom SQLs RO has to be applied to reports, and features at various places to make sure users are not able to see data of loans that don't belong to them. For this a WHERE sql clause has to be generated based on the user heirarchy. The following is a sample code from function account_numbers in repayment.php which generates a list of account numbers for the ARF dropdown: $ac = $this->load->controller("application"); if(session_get('username') != "root" && !is_admin()) $where[] = "a.vt_user_id IN ('".implode("', '", $ac->get_allowed_ids())."')"; $ac is the applications controller loaded. Because the applications table is the one which has the ownership of loans defined via `vt_user_id` get_allowed_ids() gives a list of vt_user_ids which are permitted. And the second line of the code creates a where clause which is later used in a normal SQL query. IMP: This HAS to be used in reports, etc as well to prevent data leak 3. Using DSR: Data Sharing Rules are used to give loan access to users from other users. Check How data sharing rules work By defining DSRs the get_allowed_ids() return a larger set of vt_user_ids that are allowed to view an entry. Known Issues ------------ 1. Fields added to CRUD view using process_view_data() doesn't appear in file downloaded from actions > export Fields are added in the CRUD view using process_view_data when it's too complicated to add it using the view_code in the constructor through an SQL. So to deal with this issue, one strategy is to try to simplify the new field required in the view so that it can be generated in the view_code instead of process_view_data. In extreme cases, a custom export functionality can be built to bypass this drawback of the CRUD module export functionality generated by the framework code. But this hasn't been required till date over so many years. Due Calculations ---------------- Due calculations are done by calculate_due() in app/helpers/loan_helper.php This is a complicated legacy function which was upgraded several times with new logic being added over the years. The purpose of this function is to take a loan and return the due values for that loan for a specific date. It's a holy code which should be touched very carefully because of any updates having the ability to break the logic for all loans. This function is used in ARF to auto populate values. This function is used by SOA to calculate closing LI/LF/etc. What makes this function complicated is the fact that repayments don't sync with the PS entries in real life which makes calculating LI/LF very complicated. For instance: pre payments part payments multiple PS payments advance payments multiple ICMs Suggestion: If you are editing this code, make sure to understand the logic it completely. PaySTT Calculations ------------------- CMR --- CMR is driven by payment_due.php. I am covering just the important functions below: 1. setup_pdr_sql() - sets up controller properties to define SQL parts so that any function later on can use it to generate CMR. 2. index() - actually generates the CMR data using the variables set in #1 3. cache_payment_due() - triggered when the user clicks CMR in the navigation. It checks the current caching status first. If CMR is already generated and saved in `payment_due_report` it simply takes data from that and dumps. If `payment_due_report` is empty or if it's filled up with last day's data, it will trigger index() to generate a fresh copy of the CMR and cache it in `payment_due_report` Note: index() is not called repeatedly. Data is cached in `payment_due_report` to prevent server crashes for lenders who have a lot of loans. 4. get_result_more_details() - Triggered by ajax requests when the RSI is displayed to the user on CMR, henever the user clicks on an entry in the CMR. 5. excel() - simple function to generate CMR in excel format. But AFAIR dump_excel() is now used. 6. dump_excel() - Mostly a formatting function which is called by cache_payment_due() for excel output on user's request. Common support requests ----------------------- Loan not appearing in CMR Need to figure out why the loan is not appearing in the CMR. CMR has a lot of criterias and settings so some of the criteria probably blocks the loan from getting shown. Search the loan in the CMR. Check all the details of the loan. Then try to go through the criterias where the loan might be getting filtered. Wrong due calculation in ARF First of all, if a ML claims that ARF calculations are wrong, look into the logs as to how it's calculated. The CMR > ARF has a link to 'Show Calculations' Sometimes it can be a misunderstanding. Sometimes it can be a bug. If a bug need to solve it based on the reading of the calculations and EBCB. Wrong I/LI/LF figures in Borrower statements Due calculation code is also used for due figures in the SOAs The way to check how figures are calculated in SOAs is to check its log by using the SOA url. If SOA URL is index.php/borrower_statement/print_account_statement/12 add ?show_logs=1 as a get param in the URL to check calculation logs. The logs look similar to ARF logs Wrong figures in MLS MLS will sometimes show wrong figures for cash/bank/loan/repayment balances. The number of MLS issues have reduced a lot over time because of repeated testing and developing diagnostics to catch issues fast. The first step should be to see diagnostics if there is any diagnostic entry about vouchers in that date range. If the vouchers are correct and there are still issues then it could mean wrong data entry as well which needs tracking down. For example using the bad debt book for a normal repayment. TB not matching This is about vouchers. TB is made from the vouchers table. The first step should be to see diagnostics if there is any diagnostic entry about vouchers in that date range. If entries are not found, TB date range should be narrowed down to as little as possible so that there are fewer entries to deal with. Most of the times it can come down to 1 day where there is a difference and finding the misbehaving entry can show a pattern which can then be used to track all the entries throughout the date range. Data missing in migration Migration patches need constant updates as the source data can change in structure of the amount of data it has. For example when MLCB was introduced, the immediate migrations after that required updates in the patches to deal with the new data about MLCB. For this, new SQLs can be added to the migration patches as required. Apart from that sometimes some data might actually miss because there was a wrong method implemented for the migration. Doesn't happen a lot because the migration files have been re used a lot of times successfully but still, if it happens the migration file should be ammended as required. IMP: Keep a backup before changing things Note: Debugging these issues need higher familiarity with the business, codebase, and DB structure so this could take some time to learn. PGM Migration ------------- 1. Created DB projects_loan_trillion_extract_18102017 2. Opened all the excel files sent by the ML and deleted the first row because it was blank for some reason 3. Started importing all the excel files into PMA (older version because new version doesn't support excel imports) 4. Excel files which didn't have data didnt create a table. The file with data created tables. 5. Renamed all the imported tables from `Sheet1` which is the default name to something like _client_address_data 6. The underscore in the beginning is to sort these imported tables at the end of the table list in the DB for browsing convenience 7. The names of the table came from the file names: CientAddressData10Oct2017_1of1.xls became _client_address_data. All lowercase, words separated by underscore, no datetime stamp. 8. Copied data from LoanData10Oct2017_1of2 and LoanData10Oct2017_1of2 to LoanData10Oct2017_1of1 to make a single file to import. 9. Did the same as #8 for all files which came in parts. 10. files received: BadDebtsData10Oct2017.xls, BadDebtsReinstateData10Oct2017.xls, BlacklistedClientData10Oct2017.xls, CientAddressData10Oct2017_1of1.xls, CientContactData10Oct2017.xls, ClientData10Oct2017_1of1.xls, ClientEmploymentData10Oct2017.xls, DebitccreditTransferData10Oct2017.xls, IncomePaData10Oct2017.xls, LoanData10Oct2017_1of3.xls, LoanData10Oct2017_2of3.xls, LoanData10Oct2017_3of3.xls, PaidRepaymentData10Oct2017_1of2.xls, PaidRepaymentData10Oct2017_2of2.xls, UnpaidRepaymentData10Oct2017.xls 11. Tables created: _client_address_data, _client_contact_data, _client_data, _client_employment_data, _debit_credit_transfer_data, _income_pa_data, _loan_data, _paid_repayment_data, _unpaid_repayment_data 12. Copied the DB projects_loan_trillion_extract_18102017 to projects_loan_trillion. Switched to projects_loan_trillion for further work. 13. Exported all the tables from projects_loan_installer_super_latest and imported it into projects_loan_trillion to create all the loanapp tables required. 14. Migration Patch - Copied the entire SQL from sbc_migration_new.sql file and triggered it on the projects_loan_trillion DB. 15. Error: #1054 - Unknown column 'A' in '_loan_data' - Fixed by renaming the second field in _loan_data from B to A. 16. Restarted the patching process from the SQL query that had interrupted with the error. Ignored the SQL queries before that in sbc_migration_new.sql as they already got triggered before the error. 16. Error: #1146 - Table 'projects_loan_trillion._bad_debts_reinstate_data' doesn't exist - This table wasnt created because the file was empty/missing. So went to the patch file and searched for the SQL which caused this issue. Then ignored all the SQLs related to this table and started the patch again from the next SQL which dealt with some other table. 17. Error: #1452 - Cannot add or update a child row: a foreign key constraint fails (`projects_loan_trillion`.`applications`, CONSTRAINT `applications_ibfk_3` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON UPDATE CASCADE) - This error happened for the main INSERT INTO applications query 18. Added SET FOREIGN_KEY_CHECKS=0; to the patch segment in the beginning. to avoid foreign key errors 19. Error: #1052 - Column 'PAYER / PAYEE' in field list is ambiguous - happened for entry under book transfers. 20. Added table alias to the field dctd.`PAYER / PAYEE` and it worked. 21. Error: #1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' - happened for query - UPDATE borrowers b LEFT JOIN _client_data cd ON cd.NRIC = b.uid SET b.country_id = ( SELECT id........... 22. Added the following alteration before the errored query in the sbc_migration_new.sql - ALTER TABLE `_client_data` CHANGE `Nationality` `Nationality` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL; 23. Error: #1146 - Table 'projects_loan_trillion.nationality' doesn't exist - This is the last set of SQL queries, and since the table doesn't exist we can leave it. 24. Migration patch completed, switched local setup to use projects_loan_trillion to check the system 25. Triggered index.php/delta to make sure DB gets updated to latest 26. Opened diagnostic but got this error: select date, amount, (SELECT name FROM ledgers WHERE id = book_ledger_id) as book, (SELECT name FROM ledgers WHERE id = ledger_id) as ledger from vouchers where ledger_id = (SELECT id FROM ledgers WHERE name = 'Bad Debt Expense') and book_ledger_id != (SELECT id FROM ledgers WHERE name = 'Bad Debt Book') and deleted = 0 AND date BETWEEN '0000-00-00' AND '9999-12-31' - failed - Subquery returns more than 1 row 27. Turned out there are 2 ledger entries for Bad Debt Book 28. Deleted the dupe and diagnostics started working fine. 29. Full voucher discrepency showed up, as migration patch doesnt create vouchers. 30. Executed voucher_creation.sql code to create all vouchers at one go and remove the voucher discrepency box in the diagnostics 31. Triggered index.php/patch/sbc_create_installments to create PS entries for all loans to fix "Disbursements With Zero Payment Schedule". 1 entry still remained broken - left it. 32. Triggered index.php/patch/generate_eir/all to generate EIR for all loans to fix "Disbursements Having Interest Rate Missing Eir After June 2012" 33. #32 was done before #31 because EIR is generated based on PS. If PS doesn't exist EIR cannot be generated. 34. Other diagnostics left for later. 35. Did a quick scan of the charts and stats on the Dashboard and the data looks good. System ready to be used. 36. Exported the DB from PMA with zip compression and mailed to marcus+nathan as I didn't have server details to upload. 37. While uploading the DB to the server through workbench got this error --- ERROR 1452 (23000) at line 69413: Cannot add or update a child row: a foreign key constraint fails (`sivrendb2`.`#sql-5b9_79`, CONSTRAINT `applications_ibfk_3` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON UPDATE CASCADE) --- This had happened in #17 as well 38. There is a foreign key link between applications.borrower_id and borrowers.id That's what's failing here. Maybe because some loan has null/0 borrower_id. 39. A simple sorting of the applications table based on borrower_id shows that account_number 2012-443 has borrower_id = 0. 40. _loan_data shows that Borrower 1 for the loan is S8109222B. Searching that UID in the _client_data table doesn't show any entry. Tried a LIKE %S8109222B% search as well. No results. Tried looking in the excel export file as well. Not found. 41. Reported to marcus. Cannot fix missing data. The foreign key cannot work. Maybe the loan should be deleted. Not sure. 42. #37-#42 could have been done at #17 to provide an early heads up to nathan/marcus 43. Turned out the issue was with PGM export, Marcus was able to get the borrower's details from PGM for manual entry. Didn't do manual entry as it can be done in the final migration. Question/Anwers/FAQs -------------------- Abbreviations ------------- ML = Money Lender PL = Profit Loss Report TB = Trial Balance Report CMR = Collect Money Report RSI = Right Side Information on CMR GLF = Give Loan Form MA = MLCB Application PS = Payment schedule LI = Late Interest LF = Late Fee CI = Compound Interest ORM = Old Repayment Module ARF = Add Repayment Form NLT = Non Loan Transaction DL = Demand Letter PMA = PhpMyAdmin R = Repayments RO = Rowlevel ownership SOA = Statement of Accounts/Borrower Statements ICM = Interest calculation method CM = context menu