def sync_product_brand(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
# 导入品牌
local_sql = """
select max(stamp) AS timestamp from product_brand
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product_brand "
btw = self.query_period(local_sql, remote_sql)
sql = """
SELECT pb.BrandId,cast(pb.BrandName as nvarchar(100)) as name,CAST (TIMESTAMP AS INT ) AS stamps
FROM product_brand pb
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
product_brand_list = ms.ExecQuery(sql.encode('utf-8'))
_logger.info("product_brand_list have %d records need to update" %
len(product_brand_list))
for (BrandId, name, stamp) in product_brand_list:
val = {
'code': BrandId,
'name': name,
'stamp': stamp,
}
brand_id = self.pool.get('product.brand').search_bycode(
cr, uid, BrandId)
if brand_id:
self.pool.get('product.brand').write(cr, uid, brand_id, val)
else:
self.pool.get('product.brand').create(cr, uid, {
'code': BrandId,
'name': name,
'stamp': stamp,
})
def query_period(self, local, remote):
start_stamp = 0
end_stamp = 0
query_local = local
query_remote = remote
cr = self._cr
cr.execute(query_local)
for local_max_num in cr.fetchall():
start_stamp = local_max_num[0]
if local_max_num[0] is None:
start_stamp = 0
return_start = start_stamp
ms = Lz_read_SQLCa(self)
remote_stamp = ms.ExecQuery(query_remote.encode('utf-8'))
for end_stamp in remote_stamp:
if remote_stamp[0] is None:
end_stamp = 0
return_end = end_stamp[0]
res = {
'start_stamp': return_start,
'end_stamp': return_end,
}
return res
def import_sponse(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
imp = self.pool.get('check.account.import').browse(cr, uid, ids[0])
account = imp.account
braid = imp.company_id.code
start = account.date_start
end = account.date_stop + ' 23:59:59'
sup_sponse_sql = "SELECT ContactId,SupId,YearNo,yeartype,taxtype,CreateDate FROM sup_sponse_year where CreateDate between '%s' and '%s' and Braid='%s'" % (
start, end, braid)
sup_sponse_record = ms.ExecQuery(sup_sponse_sql.encode('utf-8'))
for (ContactId, SupId, YearNo, yeartype, taxtype,
CreateDate) in sup_sponse_record:
sup_id = self.pool.get('res.partner').search_bycode(cr, uid, SupId)
val = {
'contractid': ContactId,
'supplier': sup_id,
'year': YearNo,
'yeartype': yeartype,
'taxtype': taxtype,
'date': CreateDate,
}
self.pool.get('sup.sponse').create(cr, uid, val)
# sup_sponse_detail_sql="SELECT ContactId,SupId,YearNo,yeartype,taxtype,CreateDate FROM sup_sponse_year where CreateDate between '%s' and '%s' and Braid='%s'"%(start,end,braid)
# sup_sponse_detail_record= ms.ExecQuery(sup_sponse_detail_sql.encode('utf-8'))
# for () in sup_sponse_detail_record:
# val={
# }
# self.pool.get('sup.sponse.detail').create(cr,uid,val)
return
def get_employee_recordset(self):
# 获取事业部代码
d01 = self._get_business()
# 获取更新记录范围，本地库的时间戳和服务端时间戳
local_sql = """
select max(timestamp) AS timestamp from hr_employee where buid = {0}
"""
local_sql = local_sql.format(d01['id'])
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from branch_employee"
btw = self.query_period(local_sql, remote_sql)
# 获取更新记录
sql = """
select a.* from
(SELECT be.EmpId,cast(be.EmpName as nvarchar(100)) as name,CONVERT(INT,max(timestamp)) AS timestamp
FROM branch_employee be
group by be.EmpId,be.EmpName ) a
where a.timestamp between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
ms = Lz_read_SQLCa(self)
res = ms.ExecQuery(sql.encode('utf-8'))
return res
def check_pos_order(self, cr, uid, ids, context=None):
check_sql = """
SELECT braid,date,SUM(amount),SUM(profit)
FROM v_bn_saledetail
WHERE date IS NOT NULL
GROUP BY braid,date
ORDER BY date
"""
ms = Lz_read_SQLCa(self)
record = ms.ExecQuery(check_sql.encode('utf-8'))
for (braid, date, amount, profit) in record:
company_id = self.pool.get('res.company').search_bycode(
cr, uid, braid)
s_date = date + " 00:00:00"
e_date = date + " 23:59:59"
pos_order_ids = self.pool.get('sy.pos.order').search(
cr, uid, [('company_id', '=', company_id),
('sale_date', '>=', s_date),
('sale_date', '<=', e_date)])
sum_amount = 0
sum_profit = 0
for pos_id in pos_order_ids:
pos_order = self.pool.get('sy.pos.order').browse(
cr, uid, pos_id)
sum_amount = sum_amount + pos_order.amount
sum_profit = sum_profit + pos_order.profit
if int(amount) != int(sum_amount) or int(profit) != int(
sum_profit):
self.synch_pos_order(cr, uid, ids, date, context=None)
return
def sync_product_class(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
# 导入产品类别
# 获取更新记录范围，本地库的时间戳和服务端时间戳
local_sql = """
select max(stamp) AS timestamp from product_category
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product_class "
btw = self.query_period(local_sql, remote_sql)
sql = """
select ClassId,cast(ClassName as nvarchar(100)) as name,CAST (TIMESTAMP AS INT ) AS stamps
from product_class
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
product_class_list = ms.ExecQuery(sql.encode('utf-8'))
_logger.info("product_class_list have %d records need to update" %
len(product_class_list))
for (ClassId, name, stamp) in product_class_list:
val = {
'code': ClassId,
'name': name,
'stamp': stamp,
}
code = ClassId
p_ids = self.pool.get('product.category').search_bycode(
cr, uid, code)
if p_ids:
if val:
self.pool.get('product.category').write(
cr, uid, p_ids, val)
else:
self.pool.get('product.category').create(cr, uid, val)
def check_pos_data_daily(self, para_interval):
vals = []
end_date = datetime.datetime.now()
for i in range(0, para_interval + 1):
servercnt = 0
localcnt = 0
day = end_date - datetime.timedelta(days=i)
print day
exec_sql = """
select count(*) from pos_order
where to_char(date_order,'yyyy-mm-dd')='{0}' and buid ={1}
"""
exec_sql = exec_sql.format(day.strftime('%Y-%m-%d'),
self._get_business().id)
cr = self._cr
cr.execute(exec_sql)
remote_exec_sql = """
select count(*) from (
SELECT bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag,min(DATEADD(hour,-8,bs.SaleDate)) as saledate
FROM v_bn_saledetail bs
where datediff(day,saledate,'{0}')=0
group by bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag
) a
"""
remote_exec_sql = remote_exec_sql.format(day)
ms = Lz_read_SQLCa(self)
remote_cnt = ms.ExecQuery(remote_exec_sql.encode('utf-8'))
for rcnt in remote_cnt:
servercnt = remote_cnt[0]
for local_count in cr.fetchall():
localcnt = local_count[0]
_logger.info(" bnc =>jspot check_pos_data_daily ")
_logger.info(
" bnc =>jspot check_pos_data_daily local using %s " %
exec_sql)
_logger.info(
" bnc =>jspot check_pos_data_daily remote using %s " %
remote_exec_sql)
# if localcnt==servercnt:
# print 'ok '
# else:
# print day.strftime('%Y%m%d')+'===>'+str(local_count[0]) +'===>' +'Need to clean'
vals.append({
'proc_date': day.strftime('%Y-%m-%d'),
'local_records_count': localcnt,
'remote_records_count': servercnt[0]
})
return vals
def check_pos_data_daily(self, cr, uid, ids, para_interval, context=None):
vals = []
end_date = datetime.datetime.now()
for i in range(1, para_interval + 1):
servercnt = 0
localcnt = 0
day = end_date - datetime.timedelta(days=i)
print day
exec_sql = """
select count(*) from
(
select company_id,code,count(*)
from sy_pos_order
where to_char(sale_date,'yyyy-mm-dd')='{0}'
group by company_id,code ) a
"""
exec_sql = exec_sql.format(day.strftime('%Y-%m-%d'))
cr = self.pool.cursor()
cr.execute(exec_sql)
remote_exec_sql = """
select count(*) from (
SELECT bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag,min(DATEADD(hour,-8,bs.SaleDate)) as saledate
FROM v_bn_saledetail bs
where datediff(day,saledate,'{0}')=0
group by bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag
) a
"""
remote_exec_sql = remote_exec_sql.format(day)
ms = Lz_read_SQLCa(self)
remote_cnt = ms.ExecQuery(remote_exec_sql.encode('utf-8'))
for rcnt in remote_cnt:
servercnt = remote_cnt[0]
for local_count in cr.fetchall():
localcnt = local_count[0]
# _logger.info(" shangyi =>jspot check_pos_data_daily local using %s " % exec_sql)
# _logger.info(" shangyi =>jspot check_pos_data_daily remote using %s " % remote_exec_sql)
vals.append({
'proc_date': day.strftime('%Y-%m-%d'),
'local_records_count': localcnt,
'remote_records_count': servercnt[0]
})
return vals
def get_supplier_recordset(self):
# 获取更新记录范围，本地库的时间戳和服务端时间戳
local_sql = """
select max(timestamp) AS timestamp from jsport_supplier
"""
remote_sql = "select CONVERT (int,max(timestamp)) as timestamp from supplier"
btw = self.query_period(local_sql, remote_sql)
# 获取更新记录
sql = """
select SupId,cast(SupName as nvarchar(100)) as name,cast(Addr as nvarchar(100)) as addr,
Tel,Fax,Zip,Email,CONVERT (int,timestamp) as timestamp
from supplier
where CONVERT(INT,timestamp) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
ms = Lz_read_SQLCa(self)
res = ms.ExecQuery(sql.encode('utf-8'))
return res
def update_date(self, cr, uid, ids, context=None):
sql = "SELECT PurGroupId,cast(PurName as nvarchar(100)) FROM purchase_group"
ms = Lz_read_SQLCa(self)
record = ms.ExecQuery(sql.encode('utf-8'))
for (PurGroupId, PurName) in record:
item_ids = self.pool.get('purchase.group').search(
cr, uid, [('purgroupid', '=', PurGroupId)])
if item_ids:
item = self.pool.get('purchase.group').browse(
cr, uid, item_ids[0])
if item.purname != PurName:
self.pool.get('purchase.group').write(
cr, uid, item_ids[0], {'purname': PurName})
else:
self.pool.get('purchase.group').create(cr, uid, {
'purgroupid': PurGroupId,
'purname': PurName,
})
return
def auto_update(self, cr, uid, ids, context=None):
# 导入前一天销售数据
date = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
self.synch_product_data(cr, uid, ids, context=None)
self.synch_product_category_parent(cr, uid, ids, context=None)
self.synch_pos_order(cr, uid, ids, date, context=None)
# 检查前5天的销售数据是否正确
day = 5
start_date = (datetime.datetime.now() -
datetime.timedelta(days=1 + day)).strftime("%Y-%m-%d")
end_date = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
check_sql = """
SELECT braid,date,SUM(amount),SUM(profit)
FROM v_bn_saledetail
WHERE saledate BETWEEN '%s' AND '%s'
GROUP BY braid,date
""" % (start_date, end_date)
ms = Lz_read_SQLCa(self)
record = ms.ExecQuery(check_sql.encode('utf-8'))
for (braid, date, amount, profit) in record:
company_id = self.pool.get('res.company').search_bycode(
cr, uid, braid)
s_date = date + " 00:00:00"
e_date = date + " 23:59:59"
pos_order_ids = self.pool.get('sy.pos.order').search(
cr, uid, [('company_id', '=', company_id),
('sale_date', '>=', s_date),
('sale_date', '<=', e_date)])
sum_amount = 0
sum_profit = 0
for pos_id in pos_order_ids:
pos_order = self.pool.get('sy.pos.order').browse(
cr, uid, pos_id)
sum_amount = sum_amount + pos_order.amount
sum_profit = sum_profit + pos_order.profit
if int(amount) != int(sum_amount) or int(profit) != int(
sum_profit):
self.synch_pos_order(cr, uid, ids, date, context=None)
return True
def sync_employee(self, cr, uid, ids, context=None):
# 导入员工
ms = Lz_read_SQLCa(self)
local_sql = """
select max(stamp) AS timestamp from hr_employee
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from branch_employee"
btw = self.query_period(local_sql, remote_sql)
exec_sql = """
SELECT be.BraId,be.EmpId,cast(be.EmpName as nvarchar(100)) as name,CAST (TIMESTAMP AS INT ) AS stamp
FROM branch_employee be
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
exec_sql = exec_sql.format(btw['start_stamp'], btw['end_stamp'])
employee_list = ms.ExecQuery(exec_sql.encode('utf-8'))
_logger.info("employee_list have %d records need to update" %
len(employee_list))
for (BraId, EmpId, name, stamp) in employee_list:
e_id = self.pool.get('hr.employee').search_bycode(cr, uid, EmpId)
company_id = self.pool.get('res.company').search_bycode(
cr, uid, BraId)
val = {
'company_id': company_id,
'name': name,
'stamp': stamp,
'code': EmpId
}
if e_id:
hr_employee = self.pool.get('hr.employee').browse(
cr, uid, e_id)
if hr_employee.company_id and hr_employee.company_id.id != company_id:
val['company_id'] = company_id
if hr_employee.company_id == False and company_id:
val['company_id'] = company_id
if hr_employee.name != name:
val['name'] = name
if val:
self.pool.get('resource.resource').write(
cr, uid, hr_employee.resource_id.id, val)
else:
self.pool.get('hr.employee').create(cr, uid, val)
return
def import_check_account_one(self, cr, uid, checkid):
#删除已有对账单信息
check_account_ids = self.pool.get('check.account').search(
cr, uid, [('checkid', '=', checkid)])
for check_account_id in check_account_ids:
self.pool.get('check.account').unlink(cr,
uid, [check_account_id],
context=None)
#同步最新对账单信息
ms = Lz_read_SQLCa(self)
account_sql = """SELECT ca.checkid,ca.checkdate,ca.supid,isnull(ca.SaleMethod,0),s.CounterFlag,ca.checkamt,ca.receiptamt,
ca.returnamt,ca.rentamt,ca.disamt,ca.adjustamt,ca.saleamt,ca.SaleCostAmt,s.SettleMethod,s.PayMethod,
s.SettleDays,ca.begindate,ca.enddate,ca.Remark,ca.braid,ca.operatorid,spr.PurGroupId
FROM check_account ca
LEFT JOIN supplier s ON ca.supid=s.SupId
LEFT JOIN supplier_purgroup_rel spr ON spr.supid=s.SupId
where ca.checkid='%s'
ORDER BY ca.checkid""" % (checkid)
check_account_record = ms.ExecQuery(account_sql.encode('utf-8'))
if check_account_record:
self.pool.get('check.account').create_byrecord(cr, uid,
check_account_record)
#联营销售明细
saledetail_sql = """select checkid,Braid,SaleDate,Proid,SaleQty,SaleAmt,ReturnRat,Supid,flag
FROM counter_check_saledetail
WHERE checkid= '%s'
ORDER BY Proid,SaleDate""" % (checkid)
counter_check_saledetail_record = ms.ExecQuery(
saledetail_sql.encode('utf-8'))
self.pool.get('counter.check.saledetail').create_byrecord(
cr, uid, counter_check_saledetail_record)
#寄售明细
saledetail_sql = """SELECT checkid,supid,inputdate,proid,intax,BraId,receiptid,receiptdate,ReceiptQty,ReturnQty,ReceiptPrice,
CheckQty,CheckAmt,NocheckQty,NocheckAmt,OperatorId,isnull(sourceflag,9),status
FROM payable_dx pd
WHERE checkid= '%s'
ORDER BY pd.proid,pd.receiptid""" % (checkid)
payable_dx_record = ms.ExecQuery(saledetail_sql.encode('utf-8'))
self.pool.get('payable.dx').create_byrecord(cr, uid, payable_dx_record)
#买断验收单
payable_head_sql = """SELECT checkid,OrderId,DmId,Ordertype,ReceiptId,ReceiptDate,SupId,AccountDate,PayDate,
isnull(OrderAmt,0),isnull(OrderTax,0),invoiceamt,payAmt,standard,Status,
checkflag,braid,isnull(needinvoice,0)
FROM payable_head
WHERE checkid= '%s'
ORDER BY OrderId""" % (checkid)
payable_head_record = ms.ExecQuery(payable_head_sql.encode('utf-8'))
self.pool.get('payable.head').create_byrecord(cr, uid, payable_head_record)
#合同扣款明细
deduct_fund_sql = """SELECT checkid,fundtype,cast(ContactId as nvarchar(100)),cast(content as nvarchar(100)) as content,yearmon,braid,amount,
tax,paymode,status,billflag,operatorid,supid
FROM deduct_fund
WHERE checkid= '%s'""" % (checkid)
deduct_fund_record = ms.ExecQuery(deduct_fund_sql.encode('utf-8'))
self.pool.get('deduct.fund').create_byrecord(cr, uid, deduct_fund_record)
return
def sync_supplier(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
# 导入供应商
local_sql = """
select max(stamp) AS timestamp from res_partner where supplier is true
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from supplier "
btw = self.query_period(local_sql, remote_sql)
sql = """
select SupId,cast(SupName as nvarchar(100)) as name,cast(Addr as nvarchar(100)) as addr,
Tel,Fax,Zip,Email,CAST (TIMESTAMP AS INT ) AS stamps
from supplier
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
supplier_list = ms.ExecQuery(sql.encode('utf-8'))
_logger.info("supplier_list have %d records need to update" %
len(supplier_list))
for (SupId, name, addr, Tel, Fax, Zip, Email, stamp) in supplier_list:
val = {
'code': SupId,
'name': name,
'street': addr,
'phone': Tel,
'fax': Fax,
'zip': Zip,
'email': Email,
'is_company': True,
'supplier': True,
'customer': False,
'company_id': False,
'stamp': stamp
}
code = SupId
p_ids = self.pool.get('res.partner').search_bycode(cr, uid, code)
if p_ids:
if val:
self.pool.get('res.partner').write(cr, uid, p_ids, val)
else:
self.pool.get('res.partner').create(cr, uid, val)
def get_product_recordset(self):
# 获取更新记录范围，本地库的时间戳和服务端时间戳
local_sql = """
select max(timestamp) AS timestamp from product_template where buid = {0}
"""
local_sql = local_sql.format(self._get_business()['id'])
remote_sql = "select CONVERT (int,max(timestamp)) as timestamp from product"
btw = self.query_period(local_sql, remote_sql)
# 获取更新记录
sql = """
select ProId,Barcode,cast(ProName as nvarchar(100)) as name,cast(spec as nvarchar(100)) as spec,
ClassId,SupId,isnull(NormalPrice,0),BrandId,CONVERT (int,timestamp) as timestamp
from product
where CONVERT(INT,timestamp) between {0} and {1}
order by CONVERT (int,timestamp)
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
ms = Lz_read_SQLCa(self)
res = ms.ExecQuery(sql.encode('utf-8'))
return res
def sync_pos_machine(self):
# 获取待更新记录
# 获取更新记录
sql = """
select braid,posno from pos_machine
"""
sql = sql.format()
ms = Lz_read_SQLCa(self)
pos_mahcine_list = ms.ExecQuery(sql.encode('utf-8'))
for (braid, posno) in pos_mahcine_list:
# 封装product.category记录
vals = {
'name': braid + '-' + posno,
# 'company_id':self.env['res.company'].search_bycode(braid).id
}
# 检查是插入还是更新
r01 = self.env['pos.config'].search([('name', '=',
braid + '-' + posno)])
if not r01:
self.env['pos.config'].create(vals)
return True
def get_product_brand_recordset(self):
# 获取事业部代码
d01 = self._get_business()
# 获取更新记录范围，本地库的时间戳和服务端时间戳
local_sql = """
select max(timestamp) AS timestamp from product_brand where buid = {0}
"""
local_sql = local_sql.format(d01['id'])
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product_brand"
btw = self.query_period(local_sql, remote_sql)
# 获取更新记录
sql = """
select brandId,cast(brandName as nvarchar(100)) as name,CONVERT(INT,timestamp) AS timestamp
from product_brand
where CONVERT(INT,timestamp) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
ms = Lz_read_SQLCa(self)
res = ms.ExecQuery(sql.encode('utf-8'))
return res
def import_account(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
imp = self.pool.get('check.account.import').browse(cr, uid, ids[0])
account = imp.account
braid = imp.company_id.code
start = account.date_start
end = account.date_stop + ' 23:59:59'
#删除本账期已经导入的对账单
check_account = self.pool.get('check.account').search(
cr, uid, [('company_id', '=', imp.company_id.id),
('date', '>=', imp.account.date_start),
('date', '<=', imp.account.date_stop)])
self.pool.get('check.account').unlink(cr, uid, check_account)
#导入对账单
account_sql = """SELECT ca.checkid,ca.checkdate,ca.supid,isnull(ca.SaleMethod,0),s.CounterFlag,ca.checkamt,ca.receiptamt,
ca.returnamt,ca.rentamt,ca.disamt,ca.adjustamt,ca.saleamt,ca.SaleCostAmt,
s.SettleMethod,s.PayMethod,s.SettleDays,
ca.begindate,ca.enddate,ca.Remark,ca.braid,ca.operatorid,spr.PurGroupId
FROM check_account ca
LEFT JOIN supplier s ON ca.supid=s.SupId
LEFT JOIN supplier_purgroup_rel spr ON spr.supid=s.SupId
where checkdate between '%s' and '%s' and ca.braid='%s'
ORDER BY ca.checkid
""" % (start, end, braid)
check_account_record = ms.ExecQuery(account_sql.encode('utf-8'))
self.pool.get('check.account').create_byrecord(cr, uid,
check_account_record)
#联营销售明细
saledetail_sql = """select c.checkid,c.Braid,c.SaleDate,c.Proid,c.SaleQty,c.SaleAmt,c.ReturnRat,c.Supid,c.flag
FROM counter_check_saledetail c
LEFT JOIN check_account ca ON c.checkid=ca.checkid
WHERE ca.checkdate between '%s' and '%s' and ca.Braid='%s'
ORDER BY c.Proid,c.SaleDate
""" % (start, end, braid)
counter_check_saledetail_record = ms.ExecQuery(
saledetail_sql.encode('utf-8'))
self.pool.get('counter.check.saledetail').create_byrecord(
cr, uid, counter_check_saledetail_record)
#寄售明细
saledetail_sql = """SELECT pd.checkid,pd.supid,pd.inputdate,pd.proid,pd.intax,pd.BraId,pd.receiptid,pd.receiptdate,
pd.ReceiptQty,pd.ReturnQty,pd.ReceiptPrice,pd.CheckQty,pd.CheckAmt,pd.NocheckQty,
pd.NocheckAmt,pd.OperatorId,isnull(pd.sourceflag,9),pd.status
FROM payable_dx pd
LEFT JOIN check_account ca ON pd.checkid=ca.checkid
WHERE ca.checkdate between '%s' and '%s' and ca.Braid='%s'
ORDER BY pd.proid,pd.receiptid
""" % (start, end, braid)
payable_dx_record = ms.ExecQuery(saledetail_sql.encode('utf-8'))
self.pool.get('payable.dx').create_byrecord(cr, uid, payable_dx_record)
#买断验收单
payable_head_sql = """SELECT ph.checkid,ph.OrderId,ph.DmId,ph.Ordertype,ph.ReceiptId,ph.ReceiptDate,ph.SupId,ph.AccountDate,ph.PayDate,
isnull(ph.OrderAmt,0),isnull(ph.OrderTax,0),ph.invoiceamt,ph.payAmt,ph.standard,ph.Status,
ph.checkflag,ph.braid,isnull(ph.needinvoice,0)
FROM payable_head ph
LEFT JOIN check_account ca ON ph.checkid=ca.checkid
WHERE ca.checkdate between '%s' and '%s' and ca.Braid='%s'
ORDER BY ph.OrderId
""" % (start, end, braid)
payable_head_record = ms.ExecQuery(payable_head_sql.encode('utf-8'))
self.pool.get('payable.head').create_byrecord(cr, uid,
payable_head_record)
#合同扣款明细
deduct_fund_sql = """SELECT df.checkid,df.fundtype,df.ContactId,cast(df.content as nvarchar(100)) as content,df.yearmon,df.braid,df.amount,
df.tax,df.paymode,df.status,df.billflag,df.operatorid,df.supid
FROM deduct_fund df
LEFT JOIN check_account ca ON df.checkid=ca.checkid
WHERE ca.checkdate between '%s' and '%s' and ca.Braid='%s' """ % (
start, end, braid)
deduct_fund_record = ms.ExecQuery(deduct_fund_sql.encode('utf-8'))
self.pool.get('deduct.fund').create_byrecord(cr, uid,
deduct_fund_record)
#其他扣款输入
otherdetain_sql = "SELECT billid,code,SupId,amount,tax,amt_tax,cast(Remark as nvarchar(100)) as Remark,PayDate,PayMode,Status,OperatorId,Braid FROM OtherDetain where InputDate between '%s' and '%s'" % (
start, end)
otherdetain_record = ms.ExecQuery(otherdetain_sql.encode('utf-8'))
self.pool.get('otherdetain').create_byrecord(cr, uid,
otherdetain_record)
return
def sync_pos_order(self, cr, uid, ids, sy_product_date, context=None):
ms = Lz_read_SQLCa(self)
date = sy_product_date
# 删除当天数据
del_ids = self.pool.get('sy.pos.order').search(
cr, uid, [('sale_date', 'like', date + '%')])
for del_id in del_ids:
self.pool.get('sy.pos.order').unlink(cr, uid, del_id)
del_ids = self.pool.get('sy.pos.payment').search(
cr, uid, [('date', 'like', date + '%')])
for del_id in del_ids:
self.pool.get('sy.pos.payment').unlink(cr, uid, del_id)
# 导入当天pos订单和pos支付
exec_sql = """
SELECT bs.BraId,DATEADD(hour,-8,bs.SaleDate) AS SaleDate,
bs.proid,bs.SaleQty,bs.NormalPrice,bs.amount,bs.SaleId,
bs.SaleMan,bs.SaleType,bs.PosNo,bs.profit
FROM v_bn_saledetail bs
WHERE convert(VARCHAR(10),bs.SaleDate,126)= '{0}'
"""
exec_sql = exec_sql.format(date)
pos_order_list = ms.ExecQuery(exec_sql.encode('utf-8'))
for (BraId, SaleDate, proid, SaleQty, NormalPrice, amount, SaleId,
SaleMan, SaleType, PosNo, profit) in pos_order_list:
product_id = self.pool.get('product.template').search_bycode(
cr, uid, proid)
company_id = self.pool.get('res.company').search_bycode(
cr, uid, BraId)
employee_id = self.pool.get('hr.employee').search_bycode(
cr, uid, SaleMan)
self.pool.get('sy.pos.order').create(
cr, uid, {
'code': SaleId,
'product': product_id,
'sale_date': SaleDate,
'qty': SaleQty,
'normal_price': NormalPrice,
'amount': amount,
'company_id': company_id,
'sale_man': employee_id,
'sale_type': SaleType,
'PosNo': PosNo,
'profit': profit,
})
sub_sql = """
SELECT spa.SaleId,spa.PaymodeId,DATEADD(hour,-8,spa.SaleDate) as SaleDate,spa.PayMoney,spa.BraId
FROM sale_paymode_all spa
WHERE convert(VARCHAR(10),spa.SaleDate,126) = '{0}'
"""
sub_sql = sub_sql.format(date)
pos_order_pay_list = ms.ExecQuery(sub_sql.encode('utf-8'))
for (SaleId, PaymodeId, SaleDate, PayMoney,
BraId) in pos_order_pay_list:
company_id = self.pool.get('res.company').search_bycode(
cr, uid, BraId)
self.pool.get('sy.pos.payment').create(
cr, uid, {
'code': SaleId,
'date': SaleDate,
'paymodel': PaymodeId,
'paymoney': PayMoney,
'company_id': company_id,
})
return
def sync_pos_data(self):
# 获取待更新记录
# 获取更新记录
date = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
startdate = datetime.datetime.now()
para_interval = 66
# self.check_pos_data(startdate,para_interval)
self.check_pos_data_weekly()
sql = """
SELECT bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag,min(DATEADD(hour,-8,bs.SaleDate)) as saledate
FROM v_bn_saledetail bs
where datediff(day,saledate,'{0}')<={1}
group by bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag
order by braid,saleid,min(DATEADD(hour,-8,bs.SaleDate))
"""
sql = sql.format(date, para_interval)
ms = Lz_read_SQLCa(self)
pos_order_list = ms.ExecQuery(sql.encode('utf-8'))
_logger.info(
" bnc =>jspot sync_pos_data total have %d records need to sync" %
len(pos_order_list))
_logger.info(" bnc =>jspot sync_pos_data using %s sql" % sql)
for (braid, saleid, memb_id, salerid, saleflag,
saledate_order) in pos_order_list:
sql_order_line = """
SELECT bs.saleman,DATEADD(hour,-8,bs.SaleDate) AS SaleDate,bs.proid,bs.SaleQty,
bs.NormalPrice,bs.curprice,bs.amount,bs.SaleType,bs.PosNo,bs.profit
FROM v_bn_saledetail bs
where saleid='{0}'
"""
vals = []
res = []
if braid:
br01 = self.env['res.company'].search_bycode(braid).id
else:
br01 = None
if memb_id:
m01 = self.env['res.partner'].search_bycardid(memb_id).id
else:
m01 = None
if salerid:
s01 = self.env['hr.employee'].search_bycode(salerid).id
else:
s01 = None
sql_order_line = sql_order_line.format(saleid)
pos_order_line = ms.ExecQuery(sql_order_line.encode('utf-8'))
for (saleman, saledate_detail, proid, SaleQty, NormalPrice,
curprice, amount, SaleType, PosNo, profit) in pos_order_line:
res.append((0, 0, {
'product_id':
self.env['product.template'].search_bycode(
self._get_business()['strBuscode'] + '-' + proid).id,
'price_unit':
curprice,
'qty':
SaleQty,
'lngsaleid':
self.env['hr.employee'].search_bycode(saleman).id,
}))
vals = {
'date_order':
saledate_order,
'company_id':
br01,
'user_id':
self.env['res.users'].search([('login', '=', 'jspot-users')
]).id,
'note':
self._get_business()['strBuscode'] + '-' + braid + '-' +
saleid,
'partner_id':
m01,
'pos_reference':
self._get_business()['strBuscode'] + '-' + braid + '-' +
saleid,
'lines':
res,
'state':
'done',
'buid':
self._get_business().id,
'strstoreid':
braid,
'lngcasherid':
s01,
}
master = self.env['pos.order'].create(vals)
return True
def sync_product(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
# 导入产品
local_sql = """
select max(stamp) AS timestamp from product_template
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product "
btw = self.query_period(local_sql, remote_sql)
sql = """
select ProId,Barcode,cast(ProName as nvarchar(100)) as name,cast(spec as nvarchar(100)) as spec,
ClassId,SupId,isnull(NormalPrice,0),BrandId ,CAST (TIMESTAMP AS INT ) AS stamps
from product
where CAST (TIMESTAMP AS INT ) between {0} and {1}
order by CAST (TIMESTAMP AS INT )
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
product_list = ms.ExecQuery(sql.encode('utf-8'))
_logger.info("product_list have %d records need to update" %
len(product_list))
for (ProId, Barcode, name, spec, ClassId, SupId, NormalPrice, BrandId,
stamp) in product_list:
code = ProId
p_id = self.pool.get('product.template').search_bycode(
cr, uid, ProId)
categ_id = self.pool.get('product.category').search_bycode(
cr, uid, ClassId)
m_categ_id = self.pool.get('product.category').search_bycode(
cr, uid, ClassId[0:6])
b_categ_id = self.pool.get('product.category').search_bycode(
cr, uid, ClassId[0:4])
sup_id = self.pool.get('res.partner').search_bycode(cr, uid, SupId)
brand_id = self.pool.get('product.brand').search_bycode(
cr, uid, BrandId)
product = self.pool.get('product.template').browse(cr, uid, p_id)
val = {
'code': ProId,
'barcode': Barcode,
'name': name,
'spec': spec,
'list_price': NormalPrice,
'sale_ok': True,
'type': 'product',
'active': True,
'categ_id': categ_id,
'm_category': m_categ_id,
'b_category': b_categ_id,
'brand_id': brand_id,
'company_id': False,
'stamp': stamp,
}
if p_id:
product = self.pool.get('product.template').browse(
cr, uid, p_id)
if val:
self.pool.get('product.template').write(cr, uid, p_id, val)
seller_ids = product.seller_ids
s_ids = []
for seller_id in seller_ids:
s_ids.append(seller_id.name.id)
if sup_id and sup_id not in s_ids:
unlink_ids = self.pool.get('product.supplierinfo').search(
cr, uid, [('product_tmpl_id', '=', p_id)])
self.pool.get('product.supplierinfo').unlink(
cr, uid, unlink_ids)
self.pool.get('product.supplierinfo').create(
cr, uid, {
'product_tmpl_id': p_id,
'name': sup_id,
})
else:
product_tmpl_id = self.pool.get('product.template').create(
cr, uid, val)
if sup_id:
self.pool.get('product.supplierinfo').create(
cr, uid, {
'product_tmpl_id': product_tmpl_id,
'name': sup_id,
})
return
def synch_product_data(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
# 导入产品类别
product_class_list = ms.ExecQuery(
"select ClassId,cast(ClassName as nvarchar(100)) as name from product_class"
.encode('utf-8'))
for (
ClassId,
name,
) in product_class_list:
code = ClassId
p_ids = self.pool.get('product.category').search_bycode(
cr, uid, code)
if p_ids:
category = self.pool.get('product.category').browse(
cr, uid, p_ids)
old_name = category.name
val = {}
if old_name != name:
val['name'] = name
if val:
self.pool.get('product.category').write(
cr, uid, p_ids, val)
else:
self.pool.get('product.category').create(
cr, uid, {
'code': ClassId,
'name': name,
})
# 导入品牌
product_brand_list = ms.ExecQuery(
"SELECT pb.BrandId,cast(pb.BrandName as nvarchar(100)) as name FROM product_brand pb"
.encode('utf-8'))
for (
BrandId,
name,
) in product_brand_list:
brand_id = self.pool.get('product.brand').search_bycode(
cr, uid, BrandId)
if brand_id:
brand = self.pool.get('product.brand').browse(
cr, uid, brand_id)
old_name = brand.name
val = {}
if old_name != name:
val['name'] = name
if val:
self.pool.get('product.brand').write(
cr, uid, brand_id, val)
else:
self.pool.get('product.brand').create(cr, uid, {
'code': BrandId,
'name': name,
})
# 导入供应商
supplier_class_list = ms.ExecQuery(
"select SupId,cast(SupName as nvarchar(100)) as name,cast(Addr as nvarchar(100)) as addr,Tel,Fax,Zip,Email from supplier"
.encode('utf-8'))
for (
SupId,
name,
addr,
Tel,
Fax,
Zip,
Email,
) in supplier_class_list:
code = SupId
p_ids = self.pool.get('res.partner').search_bycode(cr, uid, code)
if p_ids:
partner = self.pool.get('res.partner').browse(cr, uid, p_ids)
val = {}
if partner.name != name:
val['name'] = name
if partner.street != addr:
val['street'] = addr
if partner.phone != Tel:
val['phone'] = Tel
if partner.fax != Fax:
val['fax'] = Fax
if partner.zip != Zip:
val['zip'] = Zip
if partner.email != Email:
val['email'] = Email
if val:
# partner.write(val)
self.pool.get('res.partner').write(cr, uid, p_ids, val)
else:
self.pool.get('res.partner').create(
cr, uid, {
'code': SupId,
'name': name,
'street': addr,
'phone': Tel,
'fax': Fax,
'zip': Zip,
'email': Email,
'is_company': True,
'supplier': True,
'customer': False,
'company_id': False,
})
# 导入产品
product_list = ms.ExecQuery(
"select ProId,Barcode,cast(ProName as nvarchar(100)) as name,cast(spec as nvarchar(100)) as spec,ClassId,SupId,isnull(NormalPrice,0),BrandId from product"
.encode('utf-8'))
for (
ProId,
Barcode,
name,
spec,
ClassId,
SupId,
NormalPrice,
BrandId,
) in product_list:
code = ProId
p_id = self.pool.get('product.template').search_bycode(
cr, uid, ProId)
categ_id = self.pool.get('product.category').search_bycode(
cr, uid, ClassId)
m_categ_id = self.pool.get('product.category').search_bycode(
cr, uid, ClassId[0:6])
b_categ_id = self.pool.get('product.category').search_bycode(
cr, uid, ClassId[0:4])
sup_id = self.pool.get('res.partner').search_bycode(cr, uid, SupId)
brand_id = self.pool.get('product.brand').search_bycode(
cr, uid, BrandId)
if p_id:
product = self.pool.get('product.template').browse(
cr, uid, p_id)
val = {}
if product.name != name:
val['name'] = name
if product.barcode != Barcode:
val['barcode'] = Barcode
if product.categ_id.id != categ_id:
val['categ_id'] = categ_id
if product.b_category.id != b_categ_id:
val['b_category'] = b_categ_id
if product.m_category.id != m_categ_id:
val['m_category'] = m_categ_id
if product.list_price != float(NormalPrice):
val['list_price'] = NormalPrice
if product.brand_id.id != brand_id:
val['brand_id'] = brand_id
if product.spec != spec:
val['spec'] = spec
if val:
self.pool.get('product.template').write(cr, uid, p_id, val)
seller_ids = product.seller_ids
s_ids = []
for seller_id in seller_ids:
s_ids.append(seller_id.name.id)
if sup_id and sup_id not in s_ids:
unlink_ids = self.pool.get('product.supplierinfo').search(
cr, uid, [('product_tmpl_id', '=', p_id)])
self.pool.get('product.supplierinfo').unlink(
cr, uid, unlink_ids)
self.pool.get('product.supplierinfo').create(
cr, uid, {
'product_tmpl_id': p_id,
'name': sup_id,
})
else:
product_tmpl_id = self.pool.get('product.template').create(
cr, uid, {
'code': ProId,
'barcode': Barcode,
'name': name,
'spec': spec,
'list_price': NormalPrice,
'sale_ok': True,
'type': 'product',
'active': True,
'categ_id': categ_id,
'm_category': m_categ_id,
'b_category': b_categ_id,
'brand_id': brand_id,
'company_id': False,
})
if sup_id:
self.pool.get('product.supplierinfo').create(
cr, uid, {
'product_tmpl_id': product_tmpl_id,
'name': sup_id,
})
return
def insert_pos_data_daily(self, begin, end):
# begin 和end之间的日期资料导入
sql = """
SELECT bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag,min(DATEADD(hour,-8,bs.SaleDate)) as saledate
FROM v_bn_saledetail bs
where saledate between '{0}' and '{1}'
group by bs.BraId,bs.saleid,bs.memb_id ,salerid,saleflag
order by braid,saleid,min(DATEADD(hour,-8,bs.SaleDate))
"""
sql = sql.format(begin + ' 00:00:00', end + ' 23:59:59')
ms = Lz_read_SQLCa(self)
pos_order_list = ms.ExecQuery(sql.encode('utf-8'))
_logger.info(
" bnc =>jspot insert_pos_data_daily have %d records need to sync"
% len(pos_order_list))
_logger.info(" bnc =>jspot insert_pos_data_daily %s sql" % sql)
for (braid, saleid, memb_id, salerid, saleflag,
saledate_order) in pos_order_list:
sql_order_line = """
SELECT bs.saleman,DATEADD(hour,-8,bs.SaleDate) AS SaleDate,bs.proid,bs.SaleQty,
bs.NormalPrice,bs.curprice,bs.amount,bs.SaleType,bs.PosNo,bs.profit
FROM v_bn_saledetail bs
where saleid='{0}'
"""
vals = []
res = []
if braid:
br01 = self.env['res.company'].search_bycode(braid).id
else:
br01 = None
if memb_id:
# print memb_id
m01 = self.env['res.partner'].search_bycardid(memb_id).id
else:
m01 = None
if salerid:
s01 = self.env['hr.employee'].search_bycode(salerid).id
else:
s01 = None
sql_order_line = sql_order_line.format(saleid)
pos_order_line = ms.ExecQuery(sql_order_line.encode('utf-8'))
for (saleman, saledate_detail, proid, SaleQty, NormalPrice,
curprice, amount, SaleType, PosNo, profit) in pos_order_line:
# 数量为0的交易为促销折扣
qty_tmp = SaleQty
price_unit_tmp = curprice
if SaleQty == 0:
qty_tmp = 1
price_unit_tmp = amount
res.append((0, 0, {
'product_id':
self.env['product.product'].search([
('default_code', '=',
self._get_business()['strBuscode'] + '-' + proid)
]).id,
'price_unit':
price_unit_tmp,
'qty':
qty_tmp,
'lngsaleid':
self.env['hr.employee'].search_bycode(saleman).id,
}))
vals = {
'date_order':
saledate_order,
'company_id':
br01,
'user_id':
self.env['res.users'].search([('login', '=', 'jspot-users')
]).id,
'note':
self._get_business()['strBuscode'] + '-' + braid + '-' +
saleid,
'partner_id':
m01,
'pos_reference':
self._get_business()['strBuscode'] + '-' + braid + '-' +
saleid,
'lines':
res,
'state':
'done',
'buid':
self._get_business().id,
'strstoreid':
braid,
'lngcasherid':
s01,
}
master = self.env['pos.order'].create(vals)
return True
def import_sale_plan(self, cr, uid, ids, context=None):
import_sale_plan = self.browse(cr, uid, ids[0])
period = import_sale_plan.period_id
code = period.code
begin = period.date_start
begin = datetime.datetime.strptime(begin, "%Y-%m-%d").date()
end = period.date_stop
end = datetime.datetime.strptime(end, "%Y-%m-%d").date()
yearmonth = code[3:7] + code[0:2]
company_id = import_sale_plan.company_id
braid = company_id.code
# 检查是否有已导入数据，如果有，则删除
delete_record = self.pool.get('sale.plan').search(
cr, uid, [('period_id', '=', period.id),
('company_id', '=', company_id.id)])
for delete_id in delete_record:
self.pool.get('sale.plan').unlink(cr, uid, delete_id)
# 导入销售计划
sql = """SELECT isnull(PlanSale,0),isnull(PlanProfit,0) FROM sale_plan
WHERE plantype='0' AND BraId='%s' AND YearMon='%s'""" % (braid,
yearmonth)
sql = sql.encode('utf-8')
ms = Lz_read_SQLCa(self)
plan_record = ms.ExecQuery(sql.encode('utf-8'))
for (PlanSale, PlanProfit) in plan_record:
plan_code = braid + '-' + yearmonth
plan_id = self.pool.get('sale.plan').create(
cr, uid, {
'company_id': company_id.id,
'period_id': period.id,
'code': plan_code,
'plan_sale': PlanSale,
'plan_profit': PlanProfit,
})
sql = """SELECT itemid,isnull(PlanSale,0),isnull(PlanProfit,0) FROM sale_plan WHERE plantype='2' AND BraId='%s' AND YearMon='%s'""" % (
braid, yearmonth)
sql = sql.encode('utf-8')
category_record = ms.ExecQuery(sql.encode('utf-8'))
for (itemid, PlanSale, PlanProfit) in category_record:
if itemid:
categ_id = self.pool.get('product.category').search(
cr, uid, [('code', '=', itemid)])[0]
categ_code = self.pool.get('product.category').browse(
cr, uid, categ_id).code
else:
categ_id = False
categ_code = 'null'
category_plan_sale = self.pool.get('category.sale.plan').create(
cr, uid, {
'sale_plan_id': plan_id,
'period_id': period.id,
'company_id': company_id.id,
'category': categ_id,
'plan_sale': PlanSale,
'plan_profit': PlanProfit,
})
sql = "SELECT procdate,plansale,planprofit FROM bn_temp_import_budget_dailly WHERE plantype='2' and itemid='%s' and braid='%s'" % (
categ_code, braid)
sql = sql.encode('utf-8')
category_detail_record = ms.ExecQuery(sql.encode('utf-8'))
for (procdate, plansale, planprofit) in category_detail_record:
self.pool.get('category.sale.plan.daily').create(
cr, uid, {
'date': procdate,
'plan_sale': plansale,
'plan_profit': planprofit,
'category_sale_plan': category_plan_sale,
})
sql = "SELECT itemid,isnull(PlanSale,0),isnull(PlanProfit,0) FROM sale_plan WHERE plantype='1' AND BraId='%s' AND YearMon='%s' and classid='%s'" % (
braid, yearmonth, categ_code)
sql = sql.encode('utf-8')
brand_record = ms.ExecQuery(sql.encode('utf-8'))
for (itemid, PlanSale, PlanProfit) in brand_record:
if itemid:
brand_id = self.pool.get('product.brand').search(
cr, uid, [('code', '=', itemid)])
brand_id = brand_id and brand_id[0] or False
else:
brand_id = False
brand_plan_sale = self.pool.get('brand.sale.plan').create(
cr, uid, {
'category_sale_plan': category_plan_sale,
'period_id': period.id,
'company_id': company_id.id,
'brand': brand_id,
'plan_sale': PlanSale,
'plan_profit': PlanProfit,
})
if brand_id:
brand = self.pool.get('product.brand').browse(
cr, uid, brand_id)
brand_code = brand.code
else:
brand_code = 'null'
sql = "SELECT procdate,plansale,planprofit FROM bn_temp_import_budget_dailly WHERE plantype='1' and itemid='%s' and braid='%s' and classid='%s'" % (
brand_code, braid, categ_code)
brand_detail_record = ms.ExecQuery(sql.encode('utf-8'))
for (procdate, plansale, planprofit) in brand_detail_record:
self.pool.get('brand.sale.plan.daily').create(
cr, uid, {
'date': procdate,
'plan_sale': plansale,
'plan_profit': planprofit,
'brand_sale_plan': brand_plan_sale,
})
return
def import_receipt_data(self):
start_date = self.start_date
end_date = self.end_date
unlink_record = self.env['receipt.head'].search([
('receiptdate', '>=', start_date), ('receiptdate', '<=', end_date)
])
for unlink in unlink_record:
self.env['receipt.head'].browse(unlink.id).unlink()
ms = Lz_read_SQLCa(self)
head_sql = """SELECT ReceiptId,ReceiptDate,OrderId,BraId,SupId,OperatorId,ReceiptMan,status,cast(Remark as nvarchar(100))
FROM receipt_head
where ReceiptDate between '%s' and '%s'""" % (start_date,
end_date)
head_record = ms.ExecQuery(head_sql.encode('utf-8'))
for (ReceiptId, ReceiptDate, OrderId, BraId, SupId, OperatorId,
ReceiptMan, status, Remark) in head_record:
if BraId:
BraId = self.env['res.company'].search([('code', '=', BraId)],
limit=1)
if BraId:
BraId = BraId.id
else:
BraId = False
else:
BraId = False
if SupId:
SupId = self.env['res.partner'].search([('code', '=', SupId)],
limit=1)
if SupId:
SupId = SupId.id
else:
SupId = False
else:
SupId = False
if OperatorId:
OperatorId = self.env['hr.employee'].search(
[('code', '=', OperatorId)], limit=1)
if OperatorId:
OperatorId = OperatorId.id
else:
OperatorId = False
else:
OperatorId = False
if ReceiptMan:
ReceiptMan = self.env['hr.employee'].search(
[('code', '=', ReceiptMan)], limit=1)
if ReceiptMan:
ReceiptMan = ReceiptMan.id
else:
ReceiptMan = False
else:
ReceiptMan = False
self.env['receipt.head'].create({
'receiptid': ReceiptId,
'receiptdate': ReceiptDate,
'orderid': OrderId,
'company': BraId,
'sup': SupId,
'operatorid': OperatorId,
'r_id': ReceiptMan,
'status': status,
'remark': Remark,
})
detail_sql = """SELECT rd.ReceiptId,rd.ProId,rd.OrderQty,rd.OrderPrice,rd.GifQty,rd.ReceiptQty,rd.ReceiptPrice,rd.ReceiptTax FROM receipt_detail rd
LEFT JOIN receipt_head rh ON rd.ReceiptId=rh.ReceiptId
WHERE rh.ReceiptDate BETWEEN '%s' AND '%s'""" % (
start_date, end_date)
detail_record = ms.ExecQuery(detail_sql.encode('utf-8'))
for (ReceiptId, ProId, OrderQty, OrderPrice, GifQty, ReceiptQty,
ReceiptPrice, ReceiptTax) in detail_record:
if ReceiptId:
ReceiptId = self.env['receipt.head'].search(
[('receiptid', '=', ReceiptId)], limit=1)
if ReceiptId:
ReceiptId = ReceiptId.id
else:
ReceiptId = False
else:
ReceiptId = False
if ProId:
ProId = self.env['product.template'].search(
[('code', '=', ProId)], limit=1)
if ProId:
ProId = ProId.id
else:
ProId = False
else:
ProId = False
self.env['receipt.detail'].create({
'receiptid': ReceiptId,
'proid': ProId,
'orderqty': OrderQty,
'orderprice': OrderPrice,
'gifqty': GifQty,
'receiptqty': ReceiptQty,
'receiptprice': ReceiptPrice,
'receipttax': ReceiptTax,
})
# 寄售总部订单
order_sql = """
SELECT Inputdate,OrderId,BraId,SupId,OperatorId,ReceiptMan,cast(Remark as nvarchar(100)) FROM order_head
where Inputdate between '%s' and '%s' and OrderMode not in ('0','1','2')
""" % (start_date, end_date)
order_record = ms.ExecQuery(order_sql.encode('utf-8'))
for (Inputdate, OrderId, BraId, SupId, OperatorId, ReceiptMan,
Remark) in order_record:
if BraId:
BraId = self.env['res.company'].search([('code', '=', BraId)],
limit=1)
if BraId:
BraId = BraId.id
else:
BraId = False
else:
BraId = False
if SupId:
SupId = self.env['res.partner'].search([('code', '=', SupId)],
limit=1)
if SupId:
SupId = SupId.id
else:
SupId = False
else:
SupId = False
if OperatorId:
OperatorId = self.env['hr.employee'].search(
[('code', '=', OperatorId)], limit=1)
if OperatorId:
OperatorId = OperatorId.id
else:
OperatorId = False
else:
OperatorId = False
if ReceiptMan:
ReceiptMan = self.env['hr.employee'].search(
[('code', '=', ReceiptMan)], limit=1)
if ReceiptMan:
ReceiptMan = ReceiptMan.id
else:
ReceiptMan = False
else:
ReceiptMan = False
self.env['receipt.head'].create({
'receiptid': OrderId,
'receiptdate': Inputdate,
'orderid': OrderId,
'company': BraId,
'sup': SupId,
'operatorid': OperatorId,
'r_id': ReceiptMan,
'status': 'pable_dx',
'remark': Remark,
})
order_detail_sql = """
SELECT od.OrderID,od.ProId,od.OrderQty,od.OrderPrice,od.GifQty,od.ReceiptQty,od.ReceiptPrice,od.ReceiptTax FROM order_detail od
LEFT JOIN order_head oh ON od.OrderID=oh.OrderID
where oh.Inputdate between '%s' and '%s' and
oh.OrderMode not in ('0','1','2')
""" % (start_date, end_date)
order_detail_record = ms.ExecQuery(order_detail_sql.encode('utf-8'))
for (OrderID, ProId, OrderQty, OrderPrice, GifQty, ReceiptQty,
ReceiptPrice, ReceiptTax) in order_detail_record:
if OrderID:
ReceiptId = self.env['receipt.head'].search(
[('receiptid', '=', OrderID)], limit=1)
if ReceiptId:
ReceiptId = ReceiptId.id
else:
ReceiptId = False
else:
ReceiptId = False
if ProId:
ProId = self.env['product.template'].search(
[('code', '=', ProId)], limit=1)
if ProId:
ProId = ProId.id
else:
ProId = False
else:
ProId = False
self.env['receipt.detail'].create({
'receiptid': ReceiptId,
'proid': ProId,
'orderqty': OrderQty,
'orderprice': OrderPrice,
'gifqty': GifQty,
'receiptqty': ReceiptQty,
'receiptprice': ReceiptPrice,
'receipttax': ReceiptTax,
})
def get_sqlca(self):
ms = Lz_read_SQLCa(self)
return ms
def synch_pos_order(self, cr, uid, ids, sy_product_date, context=None):
if type(sy_product_date) is types.DictType:
if self.browse(cr, uid, ids[0]) and self.browse(cr, uid,
ids[0]).date:
date = self.browse(cr, uid, ids[0]).date
else:
date = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime("%Y-%m-%d")
else:
date = sy_product_date
ms = Lz_read_SQLCa(self)
# 导入员工
employee_list = ms.ExecQuery(
"SELECT be.BraId,be.EmpId,cast(be.EmpName as nvarchar(100)) as name FROM branch_employee be"
.encode('utf-8'))
for (BraId, EmpId, name) in employee_list:
e_id = self.pool.get('hr.employee').search_bycode(cr, uid, EmpId)
company_id = self.pool.get('res.company').search_bycode(
cr, uid, BraId)
if e_id:
hr_employee = self.pool.get('hr.employee').browse(
cr, uid, e_id)
val = {}
if hr_employee.company_id and hr_employee.company_id.id != company_id:
val['company_id'] = company_id
if hr_employee.company_id == False and company_id:
val['company_id'] = company_id
if hr_employee.name != name:
val['name'] = name
if val:
self.pool.get('resource.resource').write(
cr, uid, hr_employee.resource_id.id, val)
else:
self.pool.get('hr.employee').create(cr, uid, {
'code': EmpId,
'name': name,
})
# 删除当天数据
del_ids = self.pool.get('sy.pos.order').search(
cr, uid, [('sale_date', 'like', date + '%')])
for del_id in del_ids:
self.pool.get('sy.pos.order').unlink(cr, uid, del_id)
del_ids = self.pool.get('sy.pos.payment').search(
cr, uid, [('date', 'like', date + '%')])
for del_id in del_ids:
self.pool.get('sy.pos.payment').unlink(cr, uid, del_id)
# 导入当天pos订单和pos支付
pos_order_list = ms.ExecQuery(
"SELECT bs.BraId,DATEADD(hour,-8,bs.SaleDate) AS SaleDate,bs.proid,bs.SaleQty,bs.NormalPrice,bs.amount,bs.SaleId,bs.SaleMan,bs.SaleType,bs.PosNo,bs.profit FROM v_bn_saledetail bs WHERE convert(VARCHAR(10),bs.SaleDate,126) = '%s'"
% (date).encode('utf-8'))
for (BraId, SaleDate, proid, SaleQty, NormalPrice, amount, SaleId,
SaleMan, SaleType, PosNo, profit) in pos_order_list:
product_id = self.pool.get('product.template').search_bycode(
cr, uid, proid)
company_id = self.pool.get('res.company').search_bycode(
cr, uid, BraId)
employee_id = self.pool.get('hr.employee').search_bycode(
cr, uid, SaleMan)
self.pool.get('sy.pos.order').create(
cr, uid, {
'code': SaleId,
'product': product_id,
'sale_date': SaleDate,
'qty': SaleQty,
'normal_price': NormalPrice,
'amount': amount,
'company_id': company_id,
'sale_man': employee_id,
'sale_type': SaleType,
'PosNo': PosNo,
'profit': profit,
})
pos_order_pay_list = ms.ExecQuery(
"SELECT spa.SaleId,spa.PaymodeId,DATEADD(hour,-8,spa.SaleDate) as SaleDate,spa.PayMoney,spa.BraId FROM sale_paymode_all spa WHERE convert(VARCHAR(10),spa.SaleDate,126) = '%s'"
% (date).encode('utf-8'))
for (SaleId, PaymodeId, SaleDate, PayMoney,
BraId) in pos_order_pay_list:
company_id = self.pool.get('res.company').search_bycode(
cr, uid, BraId)
self.pool.get('sy.pos.payment').create(
cr, uid, {
'code': SaleId,
'date': SaleDate,
'paymodel': PaymodeId,
'paymoney': PayMoney,
'company_id': company_id,
})
return
def compute_brand(self):
# 删除已有品牌明细
unlink_ids = self.env['sale.plan.model.brand'].search([('model_id.model_id', '=', self.model_id.id)])
for unlink_id in unlink_ids:
unlink_id.unlink()
# 创建品牌明细
day = 100
if self.company_code:
braid = self.company_code
else:
braid = ''
sql = """
DECLARE @braid NVARCHAR(5);
SELECT @braid ='{1}';
SELECT z.top_classid,
z.brandid,
z.brandamount / y.classamount*100 AS bc_sale_rate,
z.brandprofit / y.classprofit*100 AS bc_profit_rate
FROM (SELECT top_classid,
brandid,
brandname,
Sum(amount) AS brandamount,
Abs(Sum(profit)) AS brandprofit
FROM v_bn_saledetail
WHERE Datediff(day, date, Getdate()) <= {0}
AND top_classid <> '0900' and (@braid='' OR braid=@braid)
GROUP BY top_classid,
top_class_name,
brandid,
brandname) z
LEFT JOIN (SELECT top_classid,
top_class_name,
Sum(amount) AS classamount,
abs(Sum(profit)) AS classprofit
FROM v_bn_saledetail
WHERE Datediff(day, date, Getdate()) <= {0}
AND brandid IS NOT NULL
AND top_classid <> '0900' and (@braid='' OR braid=@braid)
GROUP BY top_classid,
top_class_name) y
ON z.top_classid = y.top_classid
"""
sql = sql.format(day, braid)
ms = Lz_read_SQLCa(self)
records = ms.ExecQuery(sql)
for (top_classid, brandid, bc_sale_rate, bc_profit_rate) in records:
if top_classid:
categ_id = self.env['sale.plan.model.bigclass'].search([('bigclass.code', '=', top_classid),
('model_id', '=', self.model_id.id)])
if categ_id:
categ_id = categ_id[0].id
else:
category_id = self.env['product.category'].search([('code', '=', top_classid)])
msg = u'该公司模板{0}大类未建立！！！'
msg = msg.format(category_id[0].name)
raise osv.except_osv(('错误!'), (msg))
brand_id = False
if brandid:
brand_id = self.env['product.brand'].search([('code', '=', brandid)])
if brand_id:
brand_id = brand_id[0].id
val = {
'model_id': categ_id,
'brand': brand_id,
'sale_weight': bc_sale_rate,
'profit_weight': bc_profit_rate,
}
self.env['sale.plan.model.brand'].create(val)
def sync_product_data(self, cr, uid, ids, context=None):
ms = Lz_read_SQLCa(self)
# 导入产品类别
# 获取更新记录范围，本地库的时间戳和服务端时间戳
local_sql = """
select max(stamp) AS timestamp from product_category
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product_class "
btw = self.query_period(local_sql, remote_sql)
sql = """
select ClassId,cast(ClassName as nvarchar(100)) as name,CAST (TIMESTAMP AS INT ) AS stamps
from product_class
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
product_class_list = ms.ExecQuery(sql.encode('utf-8'))
for (ClassId, name, stamp) in product_class_list:
code = ClassId
p_ids = self.pool.get('product.category').search_bycode(cr, uid, code)
if p_ids:
category = self.pool.get('product.category').browse(cr, uid, p_ids)
old_name = category.name
val = {}
if old_name != name:
val['name'] = name
val['stamp'] = stamp
if val:
self.pool.get('product.category').write(cr, uid, p_ids, val)
else:
self.pool.get('product.category').create(cr, uid, {
'code': ClassId,
'name': name,
'stamp': stamp,
})
# 导入品牌
local_sql = """
select max(stamp) AS timestamp from product_brand
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product_brand "
btw = self.query_period(local_sql, remote_sql)
sql = """
SELECT pb.BrandId,cast(pb.BrandName as nvarchar(100)) as name,CAST (TIMESTAMP AS INT ) AS stamps
FROM product_brand pb
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
product_brand_list = ms.ExecQuery(sql.encode('utf-8'))
for (BrandId, name, stamp) in product_brand_list:
brand_id = self.pool.get('product.brand').search_bycode(cr, uid, BrandId)
if brand_id:
brand = self.pool.get('product.brand').browse(cr, uid, brand_id)
old_name = brand.name
val = {}
if old_name != name:
val['name'] = name
val['stamp'] = stamp
if val:
self.pool.get('product.brand').write(cr, uid, brand_id, val)
else:
self.pool.get('product.brand').create(cr, uid, {
'code': BrandId,
'name': name,
'stamp': stamp,
})
# 导入供应商
local_sql = """
select max(stamp) AS timestamp from res_partner where supplier is true
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from supplier "
btw = self.query_period(local_sql, remote_sql)
sql = """
select SupId,cast(SupName as nvarchar(100)) as name,cast(Addr as nvarchar(100)) as addr,
Tel,Fax,Zip,Email,CAST (TIMESTAMP AS INT ) AS stamps
from supplier
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
supplier_class_list = ms.ExecQuery(sql.encode('utf-8'))
for (SupId, name, addr, Tel, Fax, Zip, Email, stamp) in supplier_class_list:
code = SupId
p_ids = self.pool.get('res.partner').search_bycode(cr, uid, code)
if p_ids:
partner = self.pool.get('res.partner').browse(cr, uid, p_ids)
val = {}
if partner.name != name:
val['name'] = name
if partner.street != addr:
val['street'] = addr
if partner.phone != Tel:
val['phone'] = Tel
if partner.fax != Fax:
val['fax'] = Fax
if partner.zip != Zip:
val['zip'] = Zip
if partner.email != Email:
val['email'] = Email
val['stamp'] = stamp
if val:
# partner.write(val)
self.pool.get('res.partner').write(cr, uid, p_ids, val)
else:
self.pool.get('res.partner').create(cr, uid, {
'code': SupId,
'name': name,
'street': addr,
'phone': Tel,
'fax': Fax,
'zip': Zip,
'email': Email,
'is_company': True,
'supplier': True,
'customer': False,
'company_id': False,
'stamp': stamp
})
# 导入产品
local_sql = """
select max(stamp) AS timestamp from product_template
"""
remote_sql = "SELECT CONVERT(INT,max(timestamp)) AS timestamp from product "
btw = self.query_period(local_sql, remote_sql)
sql = """
select ProId,Barcode,cast(ProName as nvarchar(100)) as name,cast(spec as nvarchar(100)) as spec,
ClassId,SupId,isnull(NormalPrice,0),BrandId ,CAST (TIMESTAMP AS INT ) AS stamps
from product
where CAST (TIMESTAMP AS INT ) between {0} and {1}
"""
sql = sql.format(btw['start_stamp'], btw['end_stamp'])
product_list = ms.ExecQuery(sql.encode('utf-8'))
for (ProId, Barcode, name, spec, ClassId, SupId, NormalPrice, BrandId, stamp) in product_list:
code = ProId
p_id = self.pool.get('product.template').search_bycode(cr, uid, ProId)
categ_id = self.pool.get('product.category').search_bycode(cr, uid, ClassId)
m_categ_id = self.pool.get('product.category').search_bycode(cr, uid, ClassId[0:6])
b_categ_id = self.pool.get('product.category').search_bycode(cr, uid, ClassId[0:4])
sup_id = self.pool.get('res.partner').search_bycode(cr, uid, SupId)
brand_id = self.pool.get('product.brand').search_bycode(cr, uid, BrandId)
if p_id:
product = self.pool.get('product.template').browse(cr, uid, p_id)
val = {}
if product.name != name:
val['name'] = name
if product.barcode != Barcode:
val['barcode'] = Barcode
if product.categ_id.id != categ_id:
val['categ_id'] = categ_id
if product.b_category.id != b_categ_id:
val['b_category'] = b_categ_id
if product.m_category.id != m_categ_id:
val['m_category'] = m_categ_id
if product.list_price != float(NormalPrice):
val['list_price'] = NormalPrice
if product.brand_id.id != brand_id:
val['brand_id'] = brand_id
if product.spec != spec:
val['spec'] = spec
val['stamp'] = stamp
if val:
self.pool.get('product.template').write(cr, uid, p_id, val)
seller_ids = product.seller_ids
s_ids = []
for seller_id in seller_ids:
s_ids.append(seller_id.name.id)
if sup_id and sup_id not in s_ids:
unlink_ids = self.pool.get('product.supplierinfo').search(cr, uid,
[('product_tmpl_id', '=', p_id)])
self.pool.get('product.supplierinfo').unlink(cr, uid, unlink_ids)
self.pool.get('product.supplierinfo').create(cr, uid, {
'product_tmpl_id': p_id,
'name': sup_id,
})
else:
product_tmpl_id = self.pool.get('product.template').create(cr, uid, {
'code': ProId,
'barcode': Barcode,
'name': name,
'spec': spec,
'list_price': NormalPrice,
'sale_ok': True,
'type': 'product',
'active': True,
'categ_id': categ_id,
'm_category': m_categ_id,
'b_category': b_categ_id,
'brand_id': brand_id,
'company_id': False,
'stamp': stamp,
})
if sup_id:
self.pool.get('product.supplierinfo').create(cr, uid, {
'product_tmpl_id': product_tmpl_id,
'name': sup_id,
})
return
