WebSVN – SmartDukaan – /trunk/PyProj/src/shop2020/model/v1/order/script/InsuranceDeclaration.py

#!/usr/bin/python

from elixir import *
from datetime import datetime, timedelta, date 
import sys
from shop2020.model.v1.order.impl import DataService
from shop2020.model.v1.order.impl.DataAccessors import move_orders_to_correct_warehouse
from shop2020.model.v1.order.impl.DataService import InsuranceDetailForOrder,\
    Order
import xlwt
from shop2020.utils import EmailAttachmentSender
from shop2020.utils.EmailAttachmentSender import get_attachment_part
from shop2020.thriftpy.model.v1.order.ttypes import OrderStatus
from shop2020.clients.CatalogClient import CatalogClient
from suds.client import Client
from xml.dom.minidom import parseString
import logging
import re
    
def delivered_orders():
    DataService.initialize(db_hostname="192.168.190.114")
    todate = datetime.now() 
    todate = todate-timedelta(days = 10)
    insurances = InsuranceDetailForOrder.query.filter(InsuranceDetailForOrder.isDeclared == False).filter(InsuranceDetailForOrder.startDate >= todate).all()
    if not insurances:
        return

    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('main')

    date_style = xlwt.XFStyle()
    date_style.num_format_str = "D/M/YY"
    
    heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
    sheet.set_panes_frozen(True)
    sheet.set_horz_split_pos(1)
    sheet.set_remove_splits(True)
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format

    sheet.write(0, 0, "Riskstartdate", heading_xf)
    sheet.write(0, 1, "StoreId", heading_xf)
    sheet.write(0, 2, "SystemInvoiceNumber", heading_xf)
    sheet.write(0, 3, "CustomerName", heading_xf)
    sheet.write(0, 4, "Type", heading_xf)
    sheet.write(0, 5, "Make", heading_xf)
    sheet.write(0, 6, "Model", heading_xf)
    sheet.write(0, 7, "EquipmentIdentificationNumber", heading_xf)
    sheet.write(0, 8, "Qty", heading_xf)
    sheet.write(0, 9, "Rate", heading_xf)
    sheet.write(0, 10, "TaxAmount", heading_xf)
    sheet.write(0, 11, "NetSales", heading_xf)
    sheet.write(0, 12, "FatherName", heading_xf)
    sheet.write(0, 13, "Birthdate", heading_xf)
    sheet.write(0, 14, "SystemInvoiceDate.", heading_xf)
    sheet.write(0, 15, "OrderId", heading_xf)
    sheet.write(0, 16, "OtherDetails3", heading_xf)
        
    xstr = lambda s: s or ""
    
    declaredAmount= 0.0
    i = 1
    for insurance in insurances:
        order = insurance.order
        if order.status != OrderStatus.DELIVERY_SUCCESS:
            continue
        line = order.lineitems[0]
        insurance.isDeclared = True
        declaredAmount += line.total_price
        
        sheet.write(i, 0, insurance.startDate, date_style)
        sheet.write(i, 1, '')
        sheet.write(i, 2, order.invoice_number)
        sheet.write(i, 3, order.customer_name)
        sheet.write(i, 4, 'Mobile Phones')
        sheet.write(i, 5, line.brand)
        sheet.write(i, 6, xstr(line.model_name) + " " + xstr(line.model_number))
        sheet.write(i, 7, line.serial_number)
        sheet.write(i, 8, line.quantity)
        sheet.write(i, 9, line.unit_price)
        sheet.write(i, 10, "0")
        sheet.write(i, 11, line.total_price)
        sheet.write(i, 12, insurance.guardianName)
        sheet.write(i, 13, insurance.dob)
        sheet.write(i, 14, order.billing_timestamp, date_style)
        sheet.write(i, 15, order.id)
        sheet.write(i, 16, '')        
        i = i + 1
    
    session.commit()
    client = CatalogClient().get_client()
    client.updateInsuranceDeclaredAmount(1, declaredAmount)
        
    today = date.today()
    datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)
    filename = "/tmp/insurance-" + datestr + ".xls"
    wbk.save(filename)
    EmailAttachmentSender.mail("cnc.center@shop2020.in", "5h0p2o2o", ["murthy@ubiassociates.com","kashi@ubiassociates.com"], "Insurance Details for date " + datestr, "Please find attached insurance details for today.", [get_attachment_part(filename)], ["pramit.singh@shop2020.in","anikendra.das@shop2020.in","kshitij.sood@shop2020.in","rajneesh.arora@shop2020.in"], [])

    insurer = client.getInsurer(1)
    EmailAttachmentSender.mail("cnc.center@shop2020.in", "5h0p2o2o", ["pramit.singh@shop2020.in","anikendra.das@shop2020.in","kshitij.sood@shop2020.in","rajneesh.arora@shop2020.in", "amit.tyagi@spiceretail.co.in", "pardeep.panwar@spiceretail.co.in", "j.p.gupta@shop2020.in"], "Declaration Status for insurer  " + insurer.name + " as on " + datestr + " : (Declared Amount - " +  str(insurer.declaredAmount) + ")  (Total Amount - " + str(insurer.creditedAmount), "", [], [], [])
    
def returned_orders():
    DataService.initialize(db_hostname="192.168.190.114")
    todate = datetime.now() 
    todate = todate-timedelta(days = 2)
    orders = Order.query.filter(Order.insurer > 0).filter(Order.received_return_timestamp >= todate).all()
    
    if not orders:
        return

    wbk = xlwt.Workbook()
    sheet = wbk.add_sheet('main')

    date_style = xlwt.XFStyle()
    date_style.num_format_str = "D/M/YY"
    
    heading_xf = xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
    sheet.set_panes_frozen(True)
    sheet.set_horz_split_pos(1)
    sheet.set_remove_splits(True)
    
    excel_integer_format = '0'
    integer_style = xlwt.XFStyle()
    integer_style.num_format_str = excel_integer_format

    sheet.write(0, 0, "SalesReturnDate", heading_xf)
    sheet.write(0, 1, "SalesReturnNumber", heading_xf)
    sheet.write(0, 2, "CustomerName", heading_xf)
    sheet.write(0, 3, "EquipmentIdentificationNumber", heading_xf)
    sheet.write(0, 4, "SystemInvoiceNumber", heading_xf)
    sheet.write(0, 5, "Rate", heading_xf)
    sheet.write(0, 6, "Amount", heading_xf)
    sheet.write(0, 7, "OrderId", heading_xf)

    xstr = lambda s: s or ""
    
    declaredAmount= 0.0
    i = 1
    for order in orders:
        insurance = order.insuranceDetails[0]
        if not (insurance.startDate or insurance.expiryDate) or insurance.expiryDate < datetime.now():
            continue
        
        line = order.lineitems[0]
        insurance.expiryDate = order.received_return_timestamp
    
        insured_days = ((order.received_return_timestamp).date() - ((insurance.startDate).date())).days 
        
        declaredAmount += line.total_price/365*(365-insured_days)
        
        sheet.write(i, 0, insurance.startDate, date_style)
        sheet.write(i, 1, order.invoice_number)
        sheet.write(i, 2, order.customer_name)
        sheet.write(i, 3, line.serial_number)
        sheet.write(i, 4, order.invoice_number)
        sheet.write(i, 5, line.unit_price)
        sheet.write(i, 6, line.unit_price)
        sheet.write(i, 7, order.id)
        i = i + 1
    
    session.commit()
    client = CatalogClient().get_client()
    client.updateInsuranceDeclaredAmount(1, -declaredAmount)
        
    today = date.today()
    datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)
    filename = "/tmp/insurance-return-" + datestr + ".xls"
    wbk.save(filename)
    EmailAttachmentSender.mail("cnc.center@shop2020.in", "5h0p2o2o", ["murthy@ubiassociates.com", "kashi@ubiassociates.com"], "Cancelled Insurance Policy Details for date " + datestr, "Please find attached orders which insurance need to be cancelled for today.", [get_attachment_part(filename)], ["pramit.singh@shop2020.in","anikendra.das@shop2020.in","kshitij.sood@shop2020.in","rajneesh.arora@shop2020.in"], [])

def submitDeliveredOrdersDetails(args):
    DataService.initialize(db_hostname="192.168.190.114")
    todate = datetime.now() 
    todate = todate-timedelta(days = 10)
    xstr = lambda s: s or "" 
    
    successful= []
    duplicateRecord = []
    serverSide = []
    dataFormat = []
    client = Client('http://115.112.177.176/SSSWebservice/SSSWebservice.asmx?WSDL')
    header=client.factory.create('AuthHeader')
    header.Username  = 'saholic'
    header.Password  = '1Dfgh4@#'
    client.set_options(soapheaders=header)
    
    
    if (len(args)>1):
        insurances = InsuranceDetailForOrder.query.filter(InsuranceDetailForOrder.order_id.in_(args[1:])).all()
    else:
        insurances = InsuranceDetailForOrder.query.filter(InsuranceDetailForOrder.isDeclared == False).filter(InsuranceDetailForOrder.startDate >= todate).all()
    if not insurances:
        return
    for insurance in insurances:
        order = insurance.order
        if order.status != OrderStatus.DELIVERY_SUCCESS:
            continue
        try:
            dateOfBirth = getDateFormatForDob(insurance.dob)
        except:
            try:
                dateOfBirth = getDateFormatForDob2(insurance.dob)
            except Exception as ex:
                print ex
                dataFormat.append(order.id)
                continue
        line = order.lineitems[0]
        Connected =False
        Attempt = 1
        while not Connected:
            try:
                response = client.service.InsertSalesRecord('SAHOLIC',line.serial_number,getValidDateFormat(order.billing_timestamp.date()),order.invoice_number,\
                                         'Mobile Phones',line.brand,xstr(line.model_name) + " " + xstr(line.model_number),line.unit_price,\
                                         0.0,line.total_price,getValidName(order.customer_name),getValidName(insurance.guardianName),dateOfBirth,str(order.id),\
                                         order.customer_mobilenumber,'')
                print ('SAHOLIC',line.serial_number,getValidDateFormat(order.billing_timestamp.date()),order.invoice_number,\
                                         'Mobile Phones',line.brand,xstr(line.model_name) + " " + xstr(line.model_number),line.unit_price,\
                                         0.0,line.total_price,getValidName(order.customer_name),getValidName(insurance.guardianName),dateOfBirth,str(order.id),\
                                         order.customer_mobilenumber,'')
                print "Response : ",response
                if response is None:
                    print "Unable to insert sale record.Empty Response came.Attempting again...."
                    if Attempt == 3:
                        break
                    Attempt+=1
                    continue
                Connected=True
            except:
                print "Unable to insert sale record.Attempting again...."
                Attempt = Attempt+1
                if Attempt == 3:
                    break
        if not Connected:
            serverSide.append(order.id)
            continue
                    
        dom = parseString("<dom>" + response + "</dom>")
        sssResponse = dom.getElementsByTagName('SSSResponse')[0]
        status = sssResponse.getElementsByTagName('Status')
        statusStr = status[0].childNodes[0].data
        if (statusStr == 'Failure'):
            error = sssResponse.getElementsByTagName('ErrorCode')
            errorCode = int(error[0].childNodes[0].data)
            if (errorCode == 999):
                duplicateRecord.append(order.id)
            elif (errorCode ==9999 or errorCode==99999):
                serverSide.append(order.id)
            else:
                dataFormat.append(order.id)
        
        elif (statusStr == 'Success'):
            successful.append(order.id)
            print "Insurance details successfully inserted.Order Id ",order.id

        else:
            serverSide.append(order.id)
    
    today = date.today()
    datestr = str(today.year) + "-" + str(today.month) + "-" + str(today.day)
    mail_body = "Number of records successfully inserted :"+str(len(successful))+"\nFollowing order id's were not processed because\nServer Side Error: "+', '.join(map(str, serverSide))+"\n"+"Data Format Error: "+', '.join(map(str, dataFormat))+"\n"+"Duplicate entry IMEI: "+', '.join(map(str, duplicateRecord))
    print mail_body
    EmailAttachmentSender.mail("cnc.center@shop2020.in", "5h0p2o2o", ["anikendra.das@shop2020.in","kshitij.sood@shop2020.in","rajneesh.arora@saholic.com","khushal.bhatia@saholic.com"], "Insurance submission result for date " + datestr, mail_body, [], [], [])
    
    if(len(args)>1):
        print "Exiting.Dont want to run delivered_orders() and returned_orders() again."
        sys.exit(0)
        

def getValidDateFormat(dateToFormat):
    return str(dateToFormat).replace('-','')

def getDateFormatForDob(dateToFormat):
    if (dateToFormat=='Organisation'):
        return '19700101'
    return str(datetime.strptime(dateToFormat,"%b %d %Y").date()).replace('-','')

def getDateFormatForDob2(dateToFormat):
    try:
        return str(datetime.strptime(dateToFormat,"%d %b %Y").date()).replace('-','')
    except:
        return str(datetime.strptime(dateToFormat,"%d/%m/%Y").date()).replace('-','')

def getValidName(nameToFormat):
    pattern = r'[^\.a-zA-Z\ \']'
    return re.sub(pattern,"",nameToFormat)
   
def main():
    submitDeliveredOrdersDetails(sys.argv)
    delivered_orders()
    returned_orders()
    
if __name__ == '__main__':
    main()
Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/order/script/InsuranceDeclaration.py – Rev 20172
