| 12357 |
manish.sha |
1 |
from elixir import *
|
|
|
2 |
from sqlalchemy.sql import func
|
|
|
3 |
|
|
|
4 |
from shop2020.model.v1.catalog.impl import DataService
|
|
|
5 |
from shop2020.model.v1.catalog.impl.DataService import Item, ItemVatMaster, CategoryVatMaster
|
|
|
6 |
from shop2020.model.v1.catalog.impl.DataAcessors import get_vat_percentage_for_item
|
|
|
7 |
|
|
|
8 |
from datetime import datetime, timedelta
|
|
|
9 |
import MySQLdb
|
|
|
10 |
import xlwt
|
|
|
11 |
import sys
|
|
|
12 |
|
|
|
13 |
DataService.initialize(db_hostname='192.168.190.114')
|
|
|
14 |
db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )
|
|
|
15 |
cursor = db.cursor()
|
|
|
16 |
|
|
|
17 |
def getPurchaseDataAndUpdate():
|
|
|
18 |
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"
|
|
|
19 |
print sql
|
|
|
20 |
cursor.execute(sql)
|
|
|
21 |
result = cursor.fetchall()
|
|
|
22 |
|
|
|
23 |
print result
|
|
|
24 |
|
|
|
25 |
exceptionList = []
|
|
|
26 |
|
|
|
27 |
for rec in result:
|
|
|
28 |
try:
|
|
|
29 |
|
|
|
30 |
vatFactor = 0.0
|
|
|
31 |
|
|
|
32 |
if rec[6] == 3:
|
|
|
33 |
vatFactor = get_vat_percentage_for_item(rec[0], rec[6], rec[3])
|
|
|
34 |
else:
|
|
|
35 |
vatFactor = get_vat_percentage_for_item(rec[0], rec[6], rec[1])
|
|
|
36 |
|
|
|
37 |
vatDiff = round(float((rec[1]*(vatFactor/100.0))),2)
|
|
|
38 |
nlcP = rec[2] + vatDiff
|
|
|
39 |
|
|
|
40 |
sql_update ="update lineitem set nlcP = %f, vatDiff = %f where itemId = %d and purchaseOrder_id = %d"%(nlcP, vatDiff, rec[0],rec[4])
|
|
|
41 |
print sql_update
|
|
|
42 |
try:
|
|
|
43 |
# Execute the SQL command
|
|
|
44 |
cursor.execute(sql_update)
|
|
|
45 |
# Commit your changes in the database
|
|
|
46 |
db.commit()
|
|
|
47 |
except:
|
|
|
48 |
# Rollback in case there is any error
|
|
|
49 |
db.rollback()
|
|
|
50 |
print rec
|
|
|
51 |
except Exception as e:
|
| 12392 |
manish.sha |
52 |
item = Item.get_by(id =rec[0])
|
| 12357 |
manish.sha |
53 |
if item is not None and item.category == 0:
|
| 12392 |
manish.sha |
54 |
exceptionList.append(str(e) + "For ItemId :" + str(rec[0]) + " StateId :" + str(rec[7])+ " Category:"+ str(item.category))
|
| 12357 |
manish.sha |
55 |
else:
|
| 12392 |
manish.sha |
56 |
exceptionList.append(str(e) + "For ItemId :" + str(rec[0]) + " StateId :" + str(rec[7]))
|
| 12357 |
manish.sha |
57 |
continue
|
|
|
58 |
'''
|
|
|
59 |
sql_update ="update lineitem set nlcP = nlc, vatDiff = 0.0 where itemId = %d and purchaseOrder_id = %d"%(rec[0],rec[3])
|
|
|
60 |
print sql_update
|
|
|
61 |
try:
|
|
|
62 |
# Execute the SQL command
|
|
|
63 |
cursor.execute(sql_update)
|
|
|
64 |
# Commit your changes in the database
|
|
|
65 |
db.commit()
|
|
|
66 |
except:
|
|
|
67 |
# Rollback in case there is any error
|
|
|
68 |
db.rollback()
|
|
|
69 |
'''
|
|
|
70 |
for exce in exceptionList:
|
|
|
71 |
print exce
|
|
|
72 |
|
|
|
73 |
db.close()
|
|
|
74 |
|
|
|
75 |
|
|
|
76 |
def main():
|
|
|
77 |
getPurchaseDataAndUpdate()
|
|
|
78 |
|
|
|
79 |
|
|
|
80 |
if __name__=='__main__':
|
|
|
81 |
main()
|