Subversion Repositories SmartDukaan

#!/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()