Subversion Repositories SmartDukaan

from elixir import *
from sqlalchemy.sql import func

from shop2020.model.v1.catalog.impl import DataService
from shop2020.model.v1.catalog.impl.DataService import Item, ItemVatMaster, CategoryVatMaster
from shop2020.model.v1.catalog.impl.DataAcessors import get_vat_percentage_for_item

from datetime import datetime, timedelta
import MySQLdb
import xlwt
import sys

DataService.initialize(db_hostname='192.168.190.114')
db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )
cursor = db.cursor()

def getPurchaseDataAndUpdate():
    sql = "select l.itemId, l.unitPrice, l.nlc, it.mrp, p.id, p.taxType, s.stateId, wh.state_id, p.warehouseId from lineitem l join purchaseorder p on l.purchaseOrder_id = p.id join supplier s on s.id= p.supplierId join inventory.warehouse wh on p.warehouseId= wh.id join catalog.item it on l.itemId= it.id where p.taxType=2 and s.stateId!=wh.state_id and l.nlcP is null group by l.itemId, p.id"
    print sql
    cursor.execute(sql)
    result = cursor.fetchall()
    
    print result
    
    exceptionList = []
    
    for rec in result:
        try:
            
            vatFactor = 0.0
            
            if rec[6] == 3:
                vatFactor = get_vat_percentage_for_item(rec[0], rec[7], rec[3])
            else:
                vatFactor = get_vat_percentage_for_item(rec[0], rec[7], rec[1])
            
            vatDiff = round(float((rec[1]*(vatFactor/100.0))),2)
            nlcP = rec[2] + vatDiff
            
            sql_update ="update lineitem set nlcP = %f, vatDiff = %f where itemId = %d and purchaseOrder_id = %d"%(nlcP, vatDiff, rec[0],rec[4])
            print sql_update
            try:
                # Execute the SQL command
                cursor.execute(sql_update)
                # Commit your changes in the database
                db.commit()
            except:
                # Rollback in case there is any error
                db.rollback()
            print rec
        except Exception as e:
            item = Item.get_by(id =rec[0])
            if item is not None and item.category == 0:
                exceptionList.append(str(e) + "For ItemId :" + str(rec[0]) + " StateId :" + str(rec[7])+ " Category:"+ str(item.category))
            else:               
                exceptionList.append(str(e) + "For ItemId :" + str(rec[0]) + " StateId :" + str(rec[7]))
            continue
        '''
        sql_update ="update lineitem set nlcP = nlc, vatDiff = 0.0 where itemId = %d and purchaseOrder_id = %d"%(rec[0],rec[3])
        print sql_update
        try:
            # Execute the SQL command
            cursor.execute(sql_update)
            # Commit your changes in the database
            db.commit()
        except:
            # Rollback in case there is any error
            db.rollback()
        '''
    for exce in exceptionList:
        print exce
    
    db.close()


def main():
    getPurchaseDataAndUpdate()


if __name__=='__main__':
    main()