def main(start, end):
_sql = "SELECT * FROM oms_order_new WHERE order_status>1 AND member_sid>0 AND pay_time between %s and %s "
_orders = dc.mysql_ori.query(_sql, start, end)
_order_map = map_key(_orders, 'sid')
_sql = "SELECT * FROM member WHERE sid in %s"
_members = query_large_list(dc.mysql_ori, _sql, [o.member_sid for o in _orders])
_member_map = map_key(_members, 'sid')
_sql = "SELECT * FROM oms_order_detail_new WHERE order_sid in %s"
_order_details = query_large_list(dc.mysql_ori, _sql, _order_map.keys())
for _od in _order_details:
_order = _order_map[_od.order_sid]
if _order.payment_type =='pos' or _order.payment_type == 'pos款台' :
if _od.pos_payment_mode.split(',')[0] =='01':
_order.payment_type='pos(现金)'
elif _od.pos_payment_mode.split(',')[0] in ['0301','0302','0303','0304','0305','0306']:
_order.payment_type='pos(银行卡)'
elif _od.pos_payment_mode.split(',')[0] in ['0701','0704','0705','0707','0708','0709','0710','0711','0712','0714','0715','0716','0717','0718','0719','0720','0721','0723','0724']:
_order.payment_type='pos(三方卡)'
else :
_order.payment_type='pos(其他)'
_member = _member_map.get(_order.member_sid, None)
if not _member: continue
if not _member.mobile: _member['mobile'] = ""
_od['category_name'] = getcatname(_od.category_sid)
if not _od.brand_name: _od['brand_name'] = ""
if not _od.shop_name: _od['shop_name'] = ""
_sql = "REPLACE INTO customer_buy_log (order_no,member_sid,pay_time,sale_number,sale_price,shop_sid,shop_name,brand_name,category_sid,category_name,pos_payment_mode,is_refund,mobile,detail_sid,carrier) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
dc.mysql_mid.execute(_sql, _order.order_no, _member.sid, _order.pay_time, _order.sale_all_sum, _od.sale_price, _od.shop_sid, _od.shop_name, _od.brand_name, _od.category_sid, _od.category_name, _order.payment_type, _order.order_refund_bit, _member.mobile, _od.sid,getcarrier(_member.mobile))
pass
def main(start,end):
_sql = "SELECT max(member_sid) FROM member"
_order = dc.mysql_mid.query(_sql)
_max_id = _order[0]['max(member_sid)']
_id_list = list()
_id_sql1 = "SELECT distinct(member_sid) FROM oms_order_new WHERE member_sid is not null AND pay_time BETWEEN %s AND %s"
_id_orders1 = dc.mysql_ori.query(_id_sql1,start,end)
for _id in _id_orders1 :
_id_list.append(_id['member_sid'])
_id_sql2 = "SELECT sid FROM member WHERE sid > %s"
_id_orders2 = dc.mysql_ori.query(_id_sql2,_max_id)
for _id in _id_orders2 :
if _id['sid'] not in _id_list :
_id_list.append(_id['sid'])
_member_sql = "SELECT sid, email, mobile, unionid, m_time FROM member WHERE sid in %s"
_orders = dc.mysql_ori.query(_member_sql,_id_list)
_order_map = map_key(_orders, 'sid')
_sql = "SELECT member_sid, min(pay_time) AS first_pay_time, max(pay_time) AS newest_pay_time FROM oms_order_new WHERE member_sid in %s GROUP BY member_sid "
_buy_orders = dc.mysql_ori.query(_sql,[o.sid for o in _orders])
_buy_map = map_key(_buy_orders, 'member_sid')
for _od in _orders :
if _od.sid in _buy_map.keys() :
_order = _buy_map[_od.sid]
_sql = "REPLACE INTO member (member_sid,mobile,email,xeixin_no,join_time,first_pay_time,newest_pay_time) VALUES (%s,%s,%s,%s,%s,%s,%s)"
dc.mysql_mid.execute(_sql,_od.sid,_od.mobile,_od.email,_od.unionid,_od.m_time,_order.first_pay_time,_order.newest_pay_time)
else :
_order = {'newest_pay_time':'','member_sid':_od.sid,'first_pay_time':''}
_sql = "REPLACE INTO member (member_sid,mobile,email,xeixin_no,join_time) VALUES (%s,%s,%s,%s,%s)"
dc.mysql_mid.execute(_sql,_od.sid,_od.mobile,_od.email,_od.unionid,_od.m_time)
pass
def main():
one = range(0,27758541)
list_x = [ x for x in one if x%400000==0 ]
for x in list_x :
y=x+400000
print y
_sql = "SELECT sid, m_type, email, mobile, unionid, m_time FROM member WHERE sid BETWEEN %s AND %s AND mobile !=''"
_orders = dc.mysql_ori.query(_sql,x,y)
if _orders :
_order_map = map_key(_orders, 'sid')
_sql = "SELECT member_sid, min(pay_time) AS first_pay_time, max(pay_time) AS newest_pay_time FROM oms_order_new WHERE member_sid in %s GROUP BY member_sid "
_buy_orders = dc.mysql_ori.query(_sql,[o.sid for o in _orders])
_buy_map = map_key(_buy_orders, 'member_sid')
for _od in _orders :
if _od.sid in _buy_map.keys() :
_order = _buy_map[_od.sid]
_sql = "REPLACE INTO test (member_sid,m_type,mobile,email,xeixin_no,join_time,first_pay_time,newest_pay_time) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
dc.mysql_mid.execute(_sql,_od.sid,_od.m_type,_od.mobile,_od.email,_od.unionid,_od.m_time,_order.first_pay_time,_order.newest_pay_time)
else :
_order = {'newest_pay_time':'','member_sid':_od.sid,'first_pay_time':''}
_sql = "REPLACE INTO test (member_sid,m_type,mobile,email,xeixin_no,join_time) VALUES (%s,%s,%s,%s,%s,%s)"
dc.mysql_mid.execute(_sql,_od.sid,_od.m_type,_od.mobile,_od.email,_od.unionid,_od.m_time)
pass
def get_dict(self, start_time, end_time):
_sql = "SELECT sale_money_sum, sale_all_sum, pay_time FROM oms_order_new WHERE pay_time BETWEEN %s AND %s AND order_source_sid in(0,12,18) AND order_status>1"
_orders = self.ori_db.query(_sql, start_time, end_time+' 23:59:59')
_order_map = map_key(_orders, 'pay_time')
day_dict = dict()
for _od in _order_map :
day = _od.strftime('%y-%m-%d')
hour = _od.hour
if day in day_dict.keys() :
day_dict[day].append([_order_map[_od].pay_time.hour,_order_map[_od].sale_all_sum,_order_map[_od].sale_money_sum])
else :
day_dict[day] = [[_order_map[_od].pay_time.hour,_order_map[_od].sale_all_sum,_order_map[_od].sale_money_sum]]
final_dict = dict()
for day in day_dict :
hour_dict = dict()
for i in range(0,24) :
hour_dict[i] = [0,0]
for d in day_dict[day] :
hour_dict[d[0]][0] += d[1]
hour_dict[d[0]][1] += d[2]
final_dict[day] = hour_dict
return final_dict
def main():
one = range(39800000, 44000000)
list_x = [x for x in one if x % 100000 == 0]
for x in list_x:
y = x + 100000
print y
_sql = "SELECT a.*, b.* FROM oms_order_new a, oms_order_detail_new b WHERE a.sid = b.order_sid AND a.order_status>1 AND a.m_type>0 AND a.sid BETWEEN %s AND %s "
_orders = dc.mysql_ori.query(_sql, x, y)
_sql = "SELECT * FROM member WHERE sid in %s"
_members = query_large_list(dc.mysql_ori, _sql, [o.member_sid for o in _orders])
_member_map = map_key(_members, "sid")
for _od in _orders:
if _od.payment_type == "pos" or _od.payment_type == "pos款台":
if _od.pos_payment_mode.split(",")[0] == "01":
_od.payment_type = "pos(现金)"
elif _od.pos_payment_mode.split(",")[0] in ["0301", "0302", "0303", "0304", "0305", "0306"]:
_od.payment_type = "pos(银行卡)"
elif _od.pos_payment_mode.split(",")[0] in [
"0701",
"0704",
"0705",
"0707",
"0708",
"0709",
"0710",
"0711",
"0712",
"0714",
"0715",
"0716",
"0717",
"0718",
"0719",
"0720",
"0721",
"0723",
"0724",
]:
_od.payment_type = "pos(三方卡)"
else:
_od.payment_type = "pos(其他)"
if _od.payment_type == "支付宝":
if _od.payment_type_sid == 41:
_od.payment_type = "支付宝（线下）"
else:
_od.payment_type = "支付宝（线上）"
_member = _member_map.get(_od.member_sid, None)
if not _member:
continue
if not _member.mobile:
_member["mobile"] = ""
_od["category_name"] = getcatname(_od.category_sid)
if not _od.brand_name:
_od["brand_name"] = ""
if not _od.shop_name:
_od["shop_name"] = ""
_sql = "REPLACE INTO test (order_no,member_sid,pay_time,sale_number,sale_price,shop_sid,shop_name,brand_name,category_sid,category_name,payment_type_sid,pos_payment_mode,is_refund,mobile,detail_sid,carrier,order_source_sid) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
dc.mysql_mid.execute(
_sql,
_od.order_no,
_member.sid,
_od.pay_time,
_od.sale_all_sum,
_od.sale_price,
_od.shop_sid,
_od.shop_name,
_od.brand_name,
_od.category_sid,
_od.category_name,
_od.payment_type_sid,
_od.payment_type,
_od.order_refund_bit,
_member.mobile,
_od.sid,
getcarrier(_member.mobile),
_od.order_source_sid,
)
pass
