def del_payment(self, payment_str):
if None == self.db_id or None == self.shop_id:
print('db is or shop_id is empty')
return
self.write_log(u'删除店铺' + str(self.shop_id) + u'的payment:' +
','.join(payment_str))
for payment in payment_str:
ps = payment.split(',')
for p in ps:
self.write_del('payment', p)
sql = 'select id from order_payment where shop_id=' + str(
self.shop_id
) + ' and payment_id=\'' + p + '\' and deleted=0;'
rep = restique.req_restique(self.db_id, sql,
self.restique_user,
self.restique_optcode)
if None != rep and len(rep) > 0 and rep != 'null':
jd = json.loads(rep)
if None != jd:
for r in jd:
self.write_del('order_payment', r['id'])
sql = 'select id from staff_performance where shop_id=' + str(
self.shop_id
) + ' and object_id=\'' + p + '\' and deleted=0;'
rep = restique.req_restique(self.db_id, sql,
self.restique_user,
self.restique_optcode)
if None != rep and len(rep) > 0 and rep != 'null':
jd = json.loads(rep)
if None != jd:
for r in jd:
self.write_del('staff_performance', r['id'])
def export(self):
if None == self.db_id or int(self.db_id) <= 0:
print('Invalid database id')
return
if None == self.shop_Id or int(self.shop_Id) <= 0:
print('Invalid shop_id')
return
sql = 'select id,shop_id,number,identity,pledge,amount,reward_amt,cumu_score,create_time,expiry_date,member_type_id,member_name,\
member_mobile,member_dob,member_sex,member_address,member_company,remark,state from member where shop_id=' + str(self.shop_Id) + \
' and deleted=0;'
rep = restique.req_restique(self.env, self.db_id, sql,
self.restique_user, self.restique_optcode)
if None == rep or len(rep) == 0 or rep == 'null':
print('No data from clound')
return
mb_type_hash = {}
mb_type_shop_hash = {}
jd = json.loads(rep)
self.save_file('member', jd)
if None != jd:
for i in range(len(jd)):
mb_id = jd[i]['id']
mbtype_id = jd[i]['member_type_id']
mbtype_name = ''
mbtype_shopid = jd[i]['shop_id']
if mbtype_id in mb_type_hash:
mbtype_name = mb_type_hash[mbtype_id]
mbtype_shopid = mb_type_shop_hash[mbtype_id]
else:
sql = 'select name,shop_id from member_type where id=\'' + mbtype_id + '\';'
rep = restique.req_restique(self.env, self.db_id, sql)
if None != rep and len(rep) > 4:
jMbType = json.loads(rep)
if None != jMbType and len(jMbType) > 0:
mbtype_name = jMbType[0]['name']
mbtype_shopid = jMbType[0]['shop_id']
mb_type_hash[mbtype_id] = mbtype_name
mb_type_shop_hash[mbtype_id] = mbtype_shopid
jd[i]['member_type_name'] = mbtype_name
jd[i]['member_type_shopid'] = mbtype_shopid
mb_score = 0
sql = 'select sum(score) as sc from member_score where member_id=\'' + mb_id + '\' and deleted=0 and (expiry_date=\'0000-00-00\' or \
expiry_date>=\'2018-06-21\' or expiry_date is null);'
rep = restique.req_restique(self.env, self.db_id, sql)
if None != rep and len(rep) > 4:
jSc = json.loads(rep)
if None != jSc and len(jSc) > 0 and None != jSc[0]['sc']:
mb_score += float(jSc[0]['sc'])
jd[i]['useable_score'] = mb_score
self.write_excel(jd)
def get_shift(self, date):
end_date = date + 86400
sql = 'select id,start_staff_name,start_time,shift_staff_name,shift_time,pos_owner_id from `shift` where shop_id=' + str(self.shop_id) \
+ ' and deleted=0 and ((start_time>=' + str(date) + ' and start_time<' + str(end_date) + ') or (shift_time>=' \
+ str(date) + ' and shift_time<' + str(end_date) + ')) order by start_time,shift_time;'
#print(sql)
rep = restique.req_restique(self.db_id, sql, self.restique_user, self.restique_optcode)
if None == rep or len(rep) <= 0 or rep == 'null':
print('No shift info\n')
return
#print(rep)
pos_shift_time_hash = {}
data = json.loads(rep)
sz = len(data)
for i in range(sz):
d = data[i]
st_time = int(d['start_time'])
ed_time = int(d['shift_time'])
if ed_time < st_time:
if ed_time != 0 or i != sz - 1:
print('error shift recard: id->' + d['id'] + ' start_time:' + str(st_time) + ' shift_time:' + str(ed_time) + '\n' )
if d['pos_owner_id'] not in pos_shift_time_hash:
pos_shift_time_hash[d['pos_owner_id']] = ed_time
elif pos_shift_time_hash[d['pos_owner_id']] >= st_time:
print('error shift recard: id->' + d['id'] + ' start_time:' + str(st_time) + ' shift_time:' + str(ed_time) + '\n' )
else:
pos_shift_time_hash[d['pos_owner_id']] = ed_time
return data
def getMemberTypeIds(self):
sql = 'select id,shop_id,name,`range`,lan_mode from member_type where shop_id in (' + str(
self.strShopIds) + ');'
rep = restique.req_restique(self.env, self.db_id, sql,
self.restique_user, self.restique_optcode)
if None == rep or len(rep) == 0 or rep == 'null':
print('No member_type from clound')
return None
jd = json.loads(rep)
return jd
def get_latItem(self, oid):
sql = 'select id,(price*counts) as price from order_item where one_order_id in (select id from one_order where order_id="' + oid + '") order by created desc limit 1;'
rep = restique.req_restique(self.dbname, self.db_idx, sql)
if None == rep or len(rep) <= 0 or rep == 'null':
return None
try:
data = json.loads(rep)
return data[0]
except Exception:
print(rep)
return None
def get_payment(self, oid):
sql = 'select id,paid from payment where id in (select payment_id from order_payment where one_order_id in (select id from one_order where order_id="' + oid + '"));'
rep = restique.req_restique(self.dbname, self.db_idx, sql)
if None == rep or len(rep) <= 0 or rep == 'null':
return None
try:
data = json.loads(rep)
except Exception:
print(rep)
return None
return data
def get_orders(self, date):
date_bef = date - 86400
end_date = date + 86400
sql = 'select id,order_type,order_num,amount,total,create_time,close_time,status,pos_owner_id from orders where shop_id=' + str(self.shop_id) \
+ ' and order_date>=' + str(date_bef) + ' and deleted=0 and create_time>=' + str(date_bef) + ' and create_time<' + str(end_date) \
+ ' order by create_time;';
rep = restique.req_restique(self.db_id, sql, self.restique_user, self.restique_optcode)
if None == rep or len(rep) <= 0 or rep == 'null':
print('No orders\n')
datas = json.loads(rep)
for i in range(len(datas)):
d = datas[i]
sql = 'select create_time,payment_method_name from payment where id in (select payment_id from order_payment where one_order_id in (select id from ' \
+ ' one_order where order_id=\'' + d['id'] + '\' and deleted=0) and deleted=0) and deleted=0 order by create_time limit 1;'
pRep = restique.req_restique(self.db_id, sql, self.restique_user, self.restique_optcode)
pay_time = 0
if None != pRep and len(pRep) > 0 and pRep != 'null':
payments = json.loads(pRep)
if None != payments and len(payments) > 0:
pay_time = int(payments[0]['create_time'])
datas[i]['pay_time'] = pay_time
return datas
def check_order(self):
if None == self.oid:
print(u'openapi订单ID为空（一般为看板订单）')
return False
if None == self.qid:
print('纯收银订单ID为空')
sql = 'select id,order_type,shop_id,order_sub_src,total,remark from orders where id in ("' + self.oid + '","' + self.qid + '");'
rep = restique.req_restique(self.dbname, self.db_idx, sql, self.user, self.opt_code)
if None == rep or len(rep) <= 0 or rep == 'null':
print('connect to restique failed!')
return False
try:
data = json.loads(rep)
except Exception:
print(rep)
return False
total_o = None
total_q = None
for jDIter in data:
if jDIter['id'] == self.oid:
if u'纯收银' in jDIter['remark']:
print(u'openapi订单可能是纯收银订单')
return False
total_o = (float)(jDIter['total'])
elif jDIter['id'] == self.qid:
if u'纯收银' in jDIter['remark']:
print(u'纯收银订单ID可能错误')
return False
total_q = (float)(jDIter['total'])
if None == total_o or None == total_q:
print(u'没有找到订单')
return False
fDiff = total_q - total_o
if 0 != fDiff:
print(u'两个订单差额(order.total):' + str(fDiff))
payment_q = self.get_payment(self.qid)
if None == payment_q or len(payment_q) != 1:
print(u'')
return False
pay_eaq = True
del_
if float(payment_q['paid']) != total_o:
if 'delItem' == self.oper_type:
pay_eaq = False
lastItem = self.get_latItem(self.oid)
if None != lastItem:
if total_o - float(lastItem['price']) == total_q:
def getChildShopIds(self, shop_id):
sql = 'select distinct(child) as shop_id from shop_tree where parent=' + str(
shop_id) + ';'
rep = restique.req_restique(self.env, 'main', sql, self.restique_user,
self.restique_optcode)
if None == rep or len(rep) == 0 or rep == 'null':
print('None ShopTree')
return None
jd = json.loads(rep)
strResult = None
for item in jd:
if None == strResult:
strResult = str(item['shop_id'])
else:
strResult += ',' + str(item['shop_id'])
return strResult
def export_member(self):
if None == self.db_id or int(self.db_id) <= 0:
print('Invalid database id')
return
if None == self.memberType_id or len(self.memberType_id) != 16:
print('Invalid member type id')
return
sDate = time.strftime('%Y-%m-%d', time.localtime(self.date_time))
sql = 'select id,shop_id,number,identity,pledge,amount,reward_amt,cumu_score,member_name,\
member_mobile,state from member where member_type_id=\'' + self.memberType_id + \
'\' and left(create_time,10)<\'' + sDate + '\' and deleted=0;'
rep = restique.req_restique(self.db_id, sql, self.restique_user,
self.restique_optcode)
if None == rep or len(rep) == 0 or rep == 'null':
print('No data from clound')
return
jd = json.loads(rep)
self.save_file('member_someday', jd)
def getShopName(self, shop_id):
sid = (int)(shop_id)
if sid in self.shopNameMap:
return self.shopNameMap[sid]
else:
sql = 'select name from shop where id=' + str(sid) + ';'
rep = restique.req_restique(self.env, 'main', sql,
self.restique_user,
self.restique_optcode)
if None == rep or len(rep) == 0 or rep == 'null':
return str(sid)
else:
jd = json.loads(rep)
if None == jd:
return str(sid)
shopName = jd[0]['name']
self.shopNameMap[sid] = shopName
return shopName
def export(self):
if None == self.db_id or int(self.db_id) <= 0:
print('Invalid database id')
return
if None == self.strShopIds or len(self.strShopIds) <= 0:
print('Invalid shop_id')
return
memberTypeIds = self.getMemberTypeIds()
if None == memberTypeIds:
return
for mbt in memberTypeIds:
mbt_id = mbt['id']
mbt_sid = mbt['shop_id']
strShopIds = self.getChildShopIds(mbt_sid)
if None == strShopIds:
print('No child shop ids')
return
shopName = self.getShopName(mbt_sid)
fileName = str(mbt_sid) + '_' + shopName + '_' + mbt['name']
offset = 0
sql0 = 'select left(ml.created,16) as created,m.number as number, m.member_mobile as member_mobile,m.member_name as member_name,' + \
'm.state as state, ml.shop_id as busi_sid,ml.type as type,var_amt,var_amt_reward,var_score,amt_reward,' + \
'(score_reward + gift_score_reward) as score_reward from member_busi_log as ml left join member as m on ml.member_id=m.id where ' + \
'ml.member_id in (select id from member where shop_id in (' + strShopIds + ') and member_type_id=\'' + mbt_id + \
'\' ) and ml.state in (0,2) order by ml.created limit '
while True:
sql = sql0 + str(offset) + ',5000;'
rep = restique.req_restique(self.env, self.db_id, sql,
self.restique_user,
self.restique_optcode)
if None == rep or len(rep) == 0 or rep == 'null':
print('No data from clound,mbt_id:' + str(mbt_id))
continue
jd = json.loads(rep)
if None == jd or len(jd) == 0:
break
if not os.path.exists(r'./' + fileName):
os.mkdir(r'./' + fileName)
self.write_excel(
jd, r'./' + fileName + r'/' + fileName + '_' +
str(offset) + '.xls')
offset += len(jd)
def export_amt_someday(self):
if os.path.exists('member_someday') == False:
print('no file:member_someday')
return
result = []
fp = 'member_someday'
with open(fp, 'r', encoding="utf-8") as f:
d = f.read()
if None == d or len(d) == 0:
print('read no data from member_someday')
return
jD = json.loads(d)
for db_iter in jD:
mb_id = db_iter['id']
curMb = {}
curMb['id'] = mb_id
curMb['number'] = db_iter['number']
curMb['member_name'] = db_iter['member_name']
curMb['member_mobile'] = db_iter['member_mobile']
curMb['state'] = db_iter['state']
curMb['amount'] = 0
curMb['reward_amt'] = 0
sql = 'select prev_amt,prev_amt_reward,prev_score from member_busi_log where member_id=\'' + mb_id + \
'\' order by busi_time limit 1;'
rep = restique.req_restique(self.db_id, sql)
if None == rep or len(rep) == 0 or rep.startswith('null'):
curMb['amount'] = db_iter['amount']
curMb['reward_amt'] = db_iter['reward_amt']
result.append(curMb)
continue
jd_busi = json.loads(rep)
if None == jd_busi or len(jd_busi) != 1 or None == jd_busi[
0] or None == jd_busi[0]['prev_amt']:
curMb['amount'] = db_iter['amount']
curMb['reward_amt'] = db_iter['reward_amt']
result.append(curMb)
continue
curMb['amount'] = (float)(jd_busi[0]['prev_amt'])
curMb['reward_amt'] = (float)(jd_busi[0]['prev_amt_reward'])
sql = 'select sum(var_amt) as a, sum(var_amt_reward+amt_reward) as r from member_busi_log where member_id=\'' + mb_id + \
'\' and state in (0,2) and busi_time<' + str(self.date_time) + ';'
rep = restique.req_restique(self.db_id, sql)
if None == rep or len(rep) == 0 or rep.startswith('null'):
print('get sum(var) failed')
return
jd_busi = json.loads(rep)
if None == jd_busi or len(jd_busi) != 1 or None == jd_busi[
0] or None == jd_busi[0]['a']:
curMb['amount'] = db_iter['amount']
curMb['reward_amt'] = db_iter['reward_amt']
result.append(curMb)
continue
var_a = (float)(jd_busi[0]['a'])
var_r = (float)(jd_busi[0]['r'])
sql = 'select sum(amt_reward) as r from member_busi_log where member_id=\'' + mb_id + \
'\' and state=1 and busi_time<' + str(self.date_time) + ';'
rep = restique.req_restique(self.db_id, sql)
if None != rep and len(rep) > 0 and rep.startswith(
'null') == False:
jd_busi = json.loads(rep)
if None != jd_busi and len(
jd_busi
) > 0 and None != jd_busi[0] and None != jd_busi[0]['r']:
var_r -= (float)(jd_busi[0]['r'])
curMb['amount'] += var_a
curMb['reward_amt'] += var_r
result.append(curMb)
self.write_excel(result)