ef9@ddlZddlmZddlmZddlZddlZdZdZy)N)datetime) connect_dbcK d}tj|dtjj|\}}|j dd}t j jd}|d||}tjj||}t|d5}|j|dddtd{} | j} d} |f} | j| | | j| j} | j!| j!t#| |d{y#1swYxYw77#t$$r}t'd |Yd}~y d}~wwxYww) NuploadsT)exist_ok _z %Y%m%d_%H%M%Swbzl INSERT INTO file_list (filename, created_at, updated_at) VALUES (%s, NOW(), NOW()) zError saving file: F)osmakedirspathsplitextreplacernowstrftimejoinopenwritercursorexecutecommit lastrowidcloseinsert_file_data Exceptionprint) file_contentoriginal_filenameupload_directoryfilenamefile_extension timestamp new_filename file_pathfileconnr insert_queryvalues file_list_ides Oc:\Users\Admin\OneDrive - OMSOFTWARE\Python\NoticeGen\operations\upload_data.py upload_datar,sd.$ $t4$&77#3#34E#F .##C-LLN++O< "1YK/?@ GGLL!1<@ )T " %d JJ| $ % \! |V, ''    |Y7775 % %"( 8  #A3'(sfFB%E)E ;EEA6EEE F EEE E>&E94F9E>>FcK td{}|j}tj|}|j d}|j tj |d}gd}|D]8}tj||djjd||<:|jd}|jD]1\}}d} |j| |jdf|j} | rd } g|jd |jd |jd |jd |jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd |jd!|jd"|jd#|jd$|jd%|jd&|jd'|jd(|jd)|jd*|jd+|jd,|jd-|jd.|jd/|jd0|jd1|jd2|jd3|jd4|jd5|jd6|jd7|jd8|jd9|jd:|jd;|jd<|jd=|jd>|jd?|jd@|jdA| dB} |j| | dC} | dB||jddDf}|j| |ndE} g||jd|jd |jd |jd |jd |jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd|jd |jd!|jd"|jd#|jd$|jd%|jd&|jd'|jd(|jd)|jd*|jd+|jd,|jd-|jd.|jd/|jd0|jd1|jd2|jd3|jd4|jd5|jd6|jd7|jd8|jd9|jd:|jd;|jd<|jd=|jd>|jd?|jd@|jdA} |j| | |j4 j%j%y7 #t $r}t#dF|Yd}~@d}~wwxYw#j%j%wxYww)GN)DRR CUT OFF DATE OVER DUE DATETOS Date DATE OF DISB.coerce)errorsz%Y-%m-%dz4SELECT id FROM notice_list WHERE cl_contract_id = %szCL CONTRACT IDa UPDATE notice_list SET lk_loan_account_id = %s, formatted_lan = %s, lrn = %s, language_of_notice = %s, advocate_name = %s, customer_name = %s, zone = %s, state = %s, location = %s, district = %s, ncm = %s, opn_dpd = %s, drr_cut_off_date = %s, current_dpd = %s, current_pos = %s, lender_name = %s, co_lender_name = %s, lender_colender_ratio = %s, current_month_best_dispo = %s, date_of_disb = %s, mob = %s, non_starter_flag = %s, loan_amount = %s, pos_amount_in_lakh = %s, emi_amount = %s, emi_frequency = %s, overdue_emi_amount = %s, over_due_charges = %s, total_overdue_amount = %s, over_due_date = %s, tos_amount = %s, tos_date = %s, payment_link = %s, lk_contact_person_name = %s, email_id = %s, lk_contact_person_cell_no = %s, borrower_name = %s, borrower_address = %s, borrower_city = %s, borrower_state = %s, borrower_pin_code = %s, borrower_email_id = %s, borrower_mob_no = %s, co_borrower_1_name = %s, co_borrower_1_address = %s, co_borrower_1_city = %s, co_borrower_1_state = %s, co_borrower_1_pin_code = %s, co_borrower_1_email_id = %s, co_borrower_1_mob_no = %s, primary_sms_whats_app_no = %s, primary_email_id = %s, secondary_sms_whats_app_no = %s, secondary_email_id = %s, date_of_issue = %s, link = %s, updated_at = NOW() WHERE id = %s zLK LOAN ACCOUNT IDz FORMATTED LANLRNzLanguage of Noticez Advocate Namez CUSTOMER NAMEZONESTATELOCATIONDISTRICTNCMzOPN DPDr/z CURRENT DPDz CURRENT POSz LENDER NAMEzCO-LENDER NAMEzLENDER-COLENDER RATIOzCURRENT MONTH BEST DISPOr2MOBzNON-STARTER FLAGz LOAN AMOUNTzPOS Amount in Lakhz EMI AMOUNTz EMI FREQUENCYzOVERDUE EMI AMOUNTzOVER DUE CHARGESzTOTAL OVERDUE AMOUNTr0z TOS AMOUNTr1z PAYMENT LINKzLK CONTACT PERSON NAMEzEMAIL IDzLK CONTACT PERSON CELL NOz BORROWER NAMEzBORROWER ADDRESSz BORROWER CITYzBORROWER STATEzBORROWER PIN CODEzBORROWER EMAIL IDzBORROWER MOB NOzCO-BORROWER-1 NAMEzCO-BORROWER-1 ADDRESSzCO-BORROWER-1 CITYzCO-BORROWER-1 STATEzCO-BORROWER-1 PIN CODEzCO-BORROWER-1 EMAIL IDzCO-BORROWER-1 MOB NOzPrimary SMS/Whats App NozPrimary Email IDzSecondary SMS/Whats App NozSecondary Email IDz date of issuelinkra INSERT INTO notice_list_data ( notice_list_id, file_list_id, cl_contract_id, status, created_at, updated_at ) VALUES ( %s, %s, %s, %s, NOW(), NOW() ) ReissuedaZ INSERT INTO notice_list ( file_list_id, cl_contract_id, lk_loan_account_id, formatted_lan, lrn, language_of_notice, advocate_name, customer_name, zone, state, location, district, ncm, opn_dpd, drr_cut_off_date, current_dpd, current_pos, lender_name, co_lender_name, lender_colender_ratio, current_month_best_dispo, date_of_disb, mob, non_starter_flag, loan_amount, pos_amount_in_lakh, emi_amount, emi_frequency, overdue_emi_amount, over_due_charges, total_overdue_amount, over_due_date, tos_amount, tos_date, payment_link, lk_contact_person_name, email_id, lk_contact_person_cell_no, borrower_name, borrower_address, borrower_city, borrower_state, borrower_pin_code, borrower_email_id, borrower_mob_no, co_borrower_1_name, co_borrower_1_address, co_borrower_1_city, co_borrower_1_state, co_borrower_1_pin_code, co_borrower_1_email_id, co_borrower_1_mob_no, primary_sms_whats_app_no, primary_email_id, secondary_sms_whats_app_no, secondary_email_id, date_of_issue, link, created_at, updated_at, status ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW(), NOW(), 'First Notice Send' ) zError inserting file data: )rrpd read_excelheadwherenotnull to_datetimedtrfillnaiterrowsrgetfetchonerrrr)r)r$r&rdf date_columnscolr row select_queryexisting_record update_queryr(r' insert_valuesr*s r+rr9s K\!]]9 % WWQZ XXbjjnd +Z  WCnnRWX>AAJJ:VBsG WYYr]kkms FAsQL NN<#''2B*C)E F$oo/O<  z:GG01:GGO,:GGEN:GG01 : GGO, : GGO, :GGFO:GGG$:GGJ':GGJ':GGEN:GGI&:GG./:GGM*:GGM*: GGM*!:"GG,-#:$GG34%:&GG67':(GGO,):*GGEN+:,GG./-:.GGM*/:0GG011:2GGL)3:4GGO,5:6GG017:8GG./9::GG23;:<GGO,=:>GGL)?:@GGJ'A:BGGN+C:DGG45E:FGGJ'G:HGG78I:JGGO,K:LGG./M:NGGO,O:PGG,-Q:RGG/0S:TGG/0U:VGG-.W:XGG01Y:ZGG34[:\GG01]:^GG12_:`GG45a:bGG45c:dGG23e:fGG67g:hGG./i:jGG89k:lGG01m:nGGO,o:pGGFOq:r$A&s:v|V4  ($A& GG,- ! |];  &; ;GG,-;GG01;GGO, ; GGEN ; GG01 ;GGO,;GGO,;GGFO;GGG$;GGJ';GGJ';GGEN;GGI&;GG./; GGM*!;"GGM*#;$GGM*%;&GG,-';(GG34);*GG67+;,GGO,-;.GGEN/;0GG./1;2GGM*3;4GG015;6GGL)7;8GGO,9;:GG01;;<GG./=;>GG23?;@GGO,A;BGGL)C;DGGJ'E;FGGN+G;HGG45I;JGGJ'K;LGG78M;NGGO,O;PGG./Q;RGGO,S;TGG,-U;VGG/0W;XGG/0Y;ZGG-.[;\GG01];^GG34_;`GG01a;bGG12c;dGG45e;fGG45g;hGG23i;jGG67k;lGG./m;nGG89o;pGG01q;rGGO,s;tGGFOu;x|V4 KKMgs n   S"J 1 +A3/001   sQe6 d-d*c6d-e !e6*d-- e6e e ee"e33e6) r rmodels.connectrpandasr?asyncior,rr+rWs  %/bLrV