U - i<@sddlmZmZddlmZddlmZddlmZmZddZ ddZ dd lmZd d Z dd lmZd d Z ddZ ddZdS))requestjsonify)closing)connect_to_database)datetime timedeltac Cst}dddddd}z\z t|jdd}|d |f|}|sptd dd d fW5QRWWS|d r|d d|d<|dr|dd|d<||d|d|d<|d|f| |d<|d|f| }||d<t|dddfW5QRWWXSQRXWnHt k rp}z(tdt |dd dfWYWSd}~XYnXW5|XdS)NPaidDraftPending PaymentOverdueCanceledpaiddraftpendingZoverdueZcanceledT dictionaryz SELECT i.*, s.user_id, p.package_name FROM invoices i JOIN subscriptions s ON i.subscription_id = s.id JOIN packages p ON s.package_id = p.id WHERE i.id = %s zInvoice not foundmessageerrori issue_date %b %d, %Yissue_date_friendlydue_datedue_date_friendlystatusstatus_friendlyz1SELECT * FROM invoice_items WHERE invoice_id = %sitemsz SELECT id, amount, payment_method, transaction_id, payment_date, status, gateway_response FROM payments WHERE invoice_id = %s ORDER BY payment_date DESC paymentsF)datarzError fetching invoice: ) rclosercursorexecutefetchonergetstrftimefetchall Exceptionstr) invoice_id connection status_mapr#invoicerer07/var/www/html/api-medvista/Services/Billing/Invoices.py get_invoicesH&    :r2cCsdS)Nr0)r+r0r0r1generate_invoice_pdfEsr3)rc Cstjd}tjjddtd}tjjddtd}tjd}tjd}t}d d d d d d}zLzt|jdd}d} |g} |r| d7} | ||r| d7} | ||r| d7} | |d| d} | | | | d} | d7} | ||d|g| | | | } | sNt gd||dddfW5QRWWbSdd| D}dd gt|}| d!|d"t|| }i}|D]}||d#g|q| D]r}||d$g|d%<||d|d|d&<|d'r |d'd(|d)<|d*r|d*d(|d+<qt | | ||dddfW5QRWWXSQRXWnHtk r}z(t d,t|dd-d.fWYWSd/}~XYnXW5|Xd/S)0zDGet all invoices for a specific user with filters and their paymentsrpagedefaulttypelimit start_dateend_daterr r r r r Tra SELECT i.*, p.package_name, s.start_date as subscription_start FROM invoices i JOIN subscriptions s ON i.subscription_id = s.id JOIN packages p ON s.package_id = p.id WHERE s.user_id = %s  AND i.status = %s AND i.issue_date >= %s AND i.issue_date <= %sSELECT COUNT(*) as total FROM ( ) as temptotal. ORDER BY i.issue_date DESC LIMIT %s OFFSET %srFrrBr4per_pagerr cSsg|] }|dqSidr0.0r.r0r0r1 sz%get_user_invoices..,%s SELECT id, invoice_id, amount, payment_method, transaction_id, payment_date, status FROM payments WHERE invoice_id IN (9) ORDER BY payment_date DESC r+rGrrrrrrrError fetching invoices: rr!Nrargsr&intrr"rr#appendr$r%extendr(rjoinlentuple setdefaultr'r)r*)user_idrr4r9start_date_str end_date_strr,r-r#queryparams count_queryrBinvoices invoice_idsformat_stringsr payments_mappayr.r/r0r0r1get_user_invoicesKs             :rdc Cstjd}tjjddtd}tjjddtd}tjd}tjd}t}d d d d d d}zJzt|jdd}d}g} |r|d7}| ||r|d7}| ||r|d7}| |d|d} | | | | d} |d7}| ||d|g| || | } | sLt gd||dddfW5QRWWbSdd| D} dd gt| }| d!|d"t| | }i}|D]}||d#g|q| D]r}||d$g|d%<||d|d|d&<|d'r |d'd(|d)<|d*r|d*d(|d+<qt | | ||dddfW5QRWWXSQRXWnHtk r}z(t d,t|dd-d.fWYWSd/}~XYnXW5|Xd/S)0zYGet all invoices with their payments, optionally filtered by status or invoice date rangerr4r5r6r9r:r;r<rr r r r r Tra SELECT i.*, p.package_name, s.user_id, s.start_date as subscription_start FROM invoices i JOIN subscriptions s ON i.subscription_id = s.id JOIN packages p ON s.package_id = p.id WHERE 1=1 r=r>r?r@rArBrCrFrDr cSsg|] }|dqSrFr0rHr0r0r1rJsz$get_all_invoices..rKrLrMrNr+rGrrrrrrrrOrr!NrP)rr4r9rZr[r,r-r#r\r]r^rBr_r`rarrbrcr.r/r0r0r1get_all_invoicess             :rec CsLt}t}z,zt|}|d||tdd|f|}|D]}|\}}}} } } } |tdd|tddd |} d| dd |}|d ||||tdd| | | f|d | |fqJW5QRX| d d dWWNSt k r8}z(| dt|ddWYWSd}~XYnXW5|XdS)Nag SELECT s.id AS subscription_id, s.billing_cycle, s.next_billing_date, s.package_id, p.price, p.currency_id, p.package_name FROM subscriptions s JOIN packages p ON s.package_id = p.id WHERE s.user_id = %s AND s.status = 'active' AND s.next_billing_date IS NOT NULL AND s.next_billing_date <= %s AND s.next_billing_date > %s daysmmonthlyannualINV-%Y%m%d-* INSERT INTO invoices ( subscription_id, invoice_number, issue_date, due_date, amount, total_amount, currency_id, status, gateway_type ) VALUES (%s, %s, %s, %s, %s, %s, %s, 'draft', 'razorpay') z UPDATE subscriptions SET next_billing_date = %s WHERE id = %s z+Invoice generated if within billing window.FrzFailed to generate invoice: T)rrnowr"rr#r$rr(r&r'commitr)rollbackr*)rYr,todayr# subscriptionssubsubscription_id billing_cyclenext_billing_date package_idprice currency_id package_namenew_billing_dateinvoice_numberr/r0r0r1generate_next_invoice_if_duesL     2rc Csnt}t}zNzt|}|d|tdd|f|}d}|D]}|\}}}} } } } } |tdd|tddd |}d| d d |}|d ||||tdd| | | f|d ||f|d 7}qLW5QRX| t |ddddfWWVSt k rZ}z0|t dt|dddfWYWSd}~XYnXW5|XdS)Na_ SELECT s.id AS subscription_id, s.billing_cycle, s.next_billing_date, s.package_id, s.user_id, p.price, p.currency_id, p.package_name FROM subscriptions s JOIN packages p ON s.package_id = p.id WHERE s.status = 'active' AND s.next_billing_date IS NOT NULL AND s.next_billing_date <= %s AND s.next_billing_date > %s rfrgrrirjrkrnrorprqz UPDATE subscriptions SET next_billing_date = %s WHERE id = %s r5z invoice(s) generated.Frr zFailed to generate invoices: Tr!)rrrrr"rr#r$rr(r&r'rsrr)rtr*)r,rur#rvcountrwrxryrzr{rYr|r}r~rrr/r0r0r1$generate_next_invoices_for_all_users]sN     :rN)flaskrr contextlibrconfrrrr2r3rdrerrr0r0r0r1s  = f fD