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