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