def get_pic(): with open("E:/File/download/444.txt", encoding="utf8") as f: line = f.readline() cur = bm.get_mia_cursor() index = 0 while line: line = line.strip('\n').split()[0] line = line[19:] # m = line.rfind(".") + 1 # print(line, line[m:]) # TODO 开始进行图片的查询了! # cur.execute("SELECT spu_id FROM db_pop.item_specification_image where spec_image_url like '%"+line+"%'") # data = cur.fetchall() # if data and len(data) > 0: # print(data[0][0]) cur.execute("SELECT item_id FROM mia_mirror.item_pictures where local_url = '" + line + "'") data = cur.fetchall() if data and len(data) > 0: print(data[0][0], line) index += 1 print(index) line = f.readline() f.close()
def export_template_detail(freight_template_id): cur = bm.get_mia_cursor("mia_mirror") sql = "SELECT distinct freight_template_id, prov_id,prov_name,rule_type,freight_amount FROM mia_mirror.freight_template_city WHERE freight_template_id = " + str( freight_template_id) + " ORDER BY FIELD(`prov_id`, 31, 26, 11, 28,30,29,8,9,10,23,24,25)" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] xinjiang = next((x for x in rows if x["prov_id"] == 31), None) xizang = next((x for x in rows if x["prov_id"] == 26), None) neimeng = next((x for x in rows if x["prov_id"] == 11), None) gansu = next((x for x in rows if x["prov_id"] == 28), None) ningxia = next((x for x in rows if x["prov_id"] == 30), None) qinghai = next((x for x in rows if x["prov_id"] == 29), None) xinjiang = handel_name(xinjiang) xizang = handel_name(xizang) neimeng = handel_name(neimeng) gansu = handel_name(gansu) ningxia = handel_name(ningxia) qinghai = handel_name(qinghai) sql = "SELECT count(*) AS use_count from item WHERE freight_template_id = " + str(freight_template_id) cur.execute(sql) use_status = "未使用" if cur.fetchall()[0][0] > 0: use_status = "已使用" print(use_status, xinjiang, xizang, neimeng, gansu, ningxia, qinghai) return ""
def get_mia_group_info(table_name, field, param): cur = bm.get_mia_cursor("mia_mirror") sql = get_sql_param(table_name, field, param) cur.execute(sql, param) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] return rows
def get_order_list(): cur = bm.get_mia_cursor("mia_mirror") sql = "select id,item_id,warehouse_id,stock_quantity from stock_item WHERE warehouse_id IN(40,96,3364,3928,6868,6869,7575,7576) AND status = 1 and modify_time > '2020-08-10 18:00' ORDER BY modify_time ASC" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] return rows
def spu_timed_task(spu_id): cur = bm.get_mia_cursor("db_pop") sql = "SELECT id FROM db_pop.item_spu WHERE id < " + str(spu_id) + " ORDER BY id DESC LIMIT 1" cur.execute(sql) result_data = cur.fetchall() spu_id = result_data[0][0] print(spu_id) Timer(3, spu_timed_task, (spu_id,)).start()
def get_warehouse_id(item_id): cur = bm.get_mia_cursor() cur.execute("select sw.id as warehouse_id from mia_mirror.customer_supplier cs " "left join mia_mirror.stock_warehouse sw on cs.id = sw.supplier_id where cs.id" " = (SELECT supplier_id FROM mia_mirror.item where id = " + item_id + ")") data = cur.fetchall() if data and len(data) > 0: return data[0][0] return ""
def get_stock_item(wid): cur = bm.get_mia_cursor("mia_mirror") sql = "SELECT id,item_id,pre_qty FROM stock_item WHERE warehouse_id = " + str( wid ) + " AND status = 1 AND modify_time > ADDDATE(NOW(),INTERVAL -1 HOUR) ORDER BY modify_time DESC" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] return rows
def get_db_spu_ids(value_id, is_draft=True): cur = bm.get_mia_cursor("db_pop") sql = "SELECT spu_id FROM db_pop.item_spu_draft WHERE id = " + str(value_id) if is_draft: sql = "SELECT id FROM db_pop.item_spu_draft WHERE spu_id = " + str(value_id) cur.execute(sql) result_data = cur.fetchall() if len(result_data) > 0: return result_data[0][0] return ""
def get_order_list(): cur = bm.get_mia_cursor("mia_mirror") # sql = "select id, order_code, warehouse_id from orders WHERE order_code in ('2012052398902155') and `status` !=6" sql = "select DISTINCT o.id, o.order_code,o.warehouse_id from orders o LEFT JOIN order_item t on o.id = t.order_id " + \ "where o.order_time>'2021-1-4' and t.spu_id=5876088 and o.`status`<3 AND o.warehouse_id in (6868,7575)" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] return rows
def get_stock_item_id(item_id, item_size, warehouse_id, stock_quantity): cur = bm.get_mia_cursor() cur.execute("SELECT id as stock_item_id, stock_quantity FROM mia_mirror.stock_item " "where item_id = " + item_id + " and item_size = '" + item_size + "' and warehouse_id = " + warehouse_id + " and status = 1 and stock_quantity >=" + stock_quantity) data = cur.fetchall() if data and len(data) > 0: return data[0] return ""
def get_db_sku_ids(value_id, is_draft=True): cur = bm.get_mia_cursor("db_pop") sql = "SELECT id FROM db_pop.item_sku WHERE spu_id = " + str(value_id) if is_draft: sql = "SELECT id FROM db_pop.item_sku_draft WHERE spu_id = " + str(value_id) cur.execute(sql) result_data = cur.fetchall() if len(result_data) > 0: return list(map(lambda x: x[0], result_data)) return []
def get_returns_list(order_item_ids): cur = bm.get_mia_cursor("mia_mirror") sql = "select id, order_code, warehouse_id from returns where order_code in (%s)" condition = ", ".join(list(map(lambda x: "'" + str(x) + "'", order_item_ids))) sql %= condition cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] return rows
def get_bmp_stock(stock): cur = bm.get_mia_cursor("mia_bmp") sql = "SELECT id, stock_quantity FROM `brand_stock_item_channel` where item_id = " + str( stock["item_id"]) + " and warehouse_id = " + str( stock["warehouse_id"]) + " and channel_id = 10" sql += " and status = 1" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] print(stock, rows)
def get_supplier_brand(args): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, ["supplier_id", "brand_id", "category_id_ng"]))) sql = "SELECT " + column + " FROM vr_pop.pop_supplier_brand WHERE supplier_id IN (%s)" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() f_data = list(map(lambda x: {"supplier_id": x[0], "brand_id": x[1], "category_id_ng": x[2]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["id"], sup) return result
def get_stock_item_list(item_ids): cur = bm.get_mia_cursor("mia_mirror") sql = "select id,item_id,warehouse_id from stock_item where item_id in (%s)" condition = ", ".join(list(map(lambda x: str(x), item_ids))) sql %= condition sql += " and status = 1" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] stock_map = {} for x in rows: stock_map.setdefault(str(x["item_id"]) + "#" + str(x["warehouse_id"]), x["id"]) return stock_map
def get_store_map(args): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, ["id", "supplier_id", "name", "status"]))) sql = "SELECT " + column + " FROM db_pop.store_info WHERE supplier_id IN (%s) and status = 1" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() # 转成列表对象 f_data = list(map(lambda x: {"id": x[0], "supplier_id": x[1], "name": x[2], "status": x[3]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["supplier_id"], sup) return result
def get_brand_map(args): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, ["id", "name"]))) sql = "SELECT " + column + " FROM mia_mirror.item_brand WHERE id IN (%s)" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() # 转成列表对象 f_data = list(map(lambda x: {"id": x[0], "name": x[1]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["id"], sup["name"]) return result
def get_pop_supplier_map(args, column=["p.id", "p.mia_supplier_id", "p.pop_admin_id"]): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, column))) sql = "SELECT " + column + " FROM vr_pop.pop_customer_supplier p WHERE p.mia_supplier_id IN (%s)" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() # 转成列表对象 f_data = list(map(lambda x: {"pop_supplier_id": x[0], "mia_supplier_id": x[1], "pop_admin_id": x[2]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["mia_supplier_id"], sup) return result
def getKdRate(contractNo, brandId): cur = bm.get_mia_cursor() cur.execute("select kd_rate from mia_mirror.contract_brand_kd_rate_cycle " "where contract_no = %s " "and brand_id = " + str(brandId) + " and status = 1 " "and NOW() BETWEEN start_date and end_date", str(contractNo)) data = cur.fetchall() if data and len(data) > 0: return data[0][0] cur.execute("select kd_rate from mia_mirror.contract_brand_kd_rate " "where contract_no = %s " "and brand_id = " + str(brandId), str(contractNo)) data = cur.fetchall() if data and len(data) > 0: return data[0][0] return ""
def get_item_supplier_map(args): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, ["id", "supplier_id", "warehouse_type", "status"]))) sql = "SELECT " + column + " FROM mia_mirror.item WHERE id IN (%s)" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() # 转成列表对象 f_data = list(map(lambda x: {"id": x[0], "supplier_id": x[1], "warehouse_type": x[2], "status": x[3]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["id"], sup) return result
def get_contractno_map(args): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, ["supplier_id", "contract_no", "status"]))) sql = "SELECT " + column + " FROM vr_pop.procurement_contract WHERE supplier_id IN (%s)" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() # 转成列表对象 f_data = list(map(lambda x: {"supplier_id": x[0], "contract_no": x[1], "status": x[2]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["supplier_id"], sup) return result
def get_category_rate(supper_id): warehouse_id = get_warehouse_id(supper_id) business_id_ng = get_business_id_ng(supper_id) cur = bm.get_mia_cursor("mia_mirror") sql = "SELECT sum(o.pay_price) as price FROM mia_mirror.orders o WHERE o.warehouse_id = " + str( warehouse_id) + " AND o.order_time >= '2018-11-07' AND o.order_time < '2018-12-07'" cur.execute(sql) result_data = cur.fetchall() r0 = result_data[0][0] sql = "SELECT sum(oi.pay_price) as price " \ "FROM mia_mirror.orders o " \ "LEFT JOIN order_item oi ON o.id = oi.order_id " \ "LEFT JOIN stock_item si ON si.id = oi.stock_item_id " \ "LEFT JOIN item i ON i.id = si.item_id " \ "WHERE o.warehouse_id = " + str(warehouse_id) \ + " AND o.order_time >= '2018-11-07' " \ "AND o.order_time < '2018-12-07' " \ "AND i.category_id_ng = " + str(business_id_ng) cur.execute(sql) result_data = cur.fetchall() r1 = result_data[0][0] sql = "SELECT sum(o.pay_price) as price FROM mia_mirror.orders o WHERE o.warehouse_id = " + str( warehouse_id) + " AND o.order_time >= '2018-12-07'" cur.execute(sql) result_data = cur.fetchall() r2 = result_data[0][0] sql = "SELECT sum(oi.pay_price) as price " \ "FROM mia_mirror.orders o " \ "LEFT JOIN order_item oi ON o.id = oi.order_id " \ "LEFT JOIN stock_item si ON si.id = oi.stock_item_id " \ "LEFT JOIN item i ON i.id = si.item_id " \ "WHERE o.warehouse_id = " + str(warehouse_id) \ + " AND o.order_time >= '2018-12-07' " \ "AND i.category_id_ng =" + str(business_id_ng) cur.execute(sql) result_data = cur.fetchall() r3 = result_data[0][0] return (r0, r1, r2, r3, business_id_ng)
def export_rule_info(): cur = bm.get_mia_cursor("mia_mirror") sql = "SELECT " \ "r.category_id_ng, " \ "ca.category_name, " \ "r.rule_name, " \ "r.type, " \ "p.prov_id, " \ "p.rule_set, " \ "p.freight_amount " \ "FROM " \ "(SELECT p4.id AS category_id_ng, CONCAT_WS(' >> ', p1.`name`, p2.`name`, p3.`name`, p4.`name`) AS category_name FROM item_category_ng p1, item_category_ng p2, item_category_ng p3, item_category_ng p4 WHERE p2.parent_id = p1.id AND p3.parent_id = p2.id AND p4.parent_id = p3.id AND p4.is_leaf = 1) ca " \ "LEFT JOIN freight_rule r ON r.category_id_ng = ca.category_id_ng " \ "LEFT JOIN freight_rule_prov p ON r.id = p.freight_rule_id limit 300" cur.execute(sql) columns = [col[0] for col in cur.description] rows = [dict(zip(columns, row)) for row in cur.fetchall()] for data in rows: print(data) return ""
def get_trac_rate(supper_id): warehouse_id = get_warehouse_id(supper_id) cur = bm.get_mia_cursor("mia_mirror") sql1 = "select COUNT(ntii.id) from new_trac_issue_info ntii INNER JOIN orders o ON o.order_code = ntii.order_code " \ "WHERE ntii.create_time >= '2018-11-07 00:00:00' " \ "AND ntii.status = 30 " \ "AND o.warehouse_id = " + str(warehouse_id) cur.execute(sql1) result_data = cur.fetchall() r1 = result_data[0][0] sql2 = "select COUNT(ntii.id) from new_trac_issue_info ntii INNER JOIN orders o ON o.order_code = ntii.order_code " \ "WHERE ntii.create_time >= '2018-11-07 00:00:00' AND o.warehouse_id = " + str(warehouse_id) cur.execute(sql2) result_data = cur.fetchall() r2 = result_data[0][0] return (r1, r2)
def get_supplier_map(args, column=["c.id", "c.name", "c.pop_admin_id", "s.name", "sw.type", "sw.id"]): cur = bm.get_mia_cursor() column = ", ".join(list(map(lambda x: x, column))) sql = "SELECT " + column + " FROM mia_mirror.customer_supplier c " \ "left join mia_mirror.stock_warehouse sw on c.id = sw.supplier_id " \ "left join mia_mirror.sec_user s on " \ "c.pop_admin_id = s.user_id WHERE c.id IN (%s)" condition = ", ".join(list(map(lambda x: "%s", args))) sql %= condition cur.execute(sql, args) f_data = cur.fetchall() # 转成列表对象 f_data = list( map(lambda x: {"id": x[0], "name": x[1], "pop_admin_id": x[2], "user_name": x[3], "w_type": x[4], "w_id": x[5]}, f_data)) result = {} for sup in f_data: result.setdefault(sup["id"], sup) return result
# coding=utf-8 import util as bm import util.mia_db as mu """ 1.获取table名称 2.根据table名称 获取 字段信息 3.根据栏目信息进行 其他处理 """ if __name__ == "__main__": cursor = bm.get_mia_cursor("db_pop") tables = mu.get_table_list("item_spu_draft", cursor) for name in tables: columns = mu.get_columns_name(name, cursor) print("表名" + name) for c in columns: print(c) print("======================")
def get_business_id_ng(supper_id): cur = bm.get_mia_cursor("mia_mirror") sql = "SELECT business_id_ng FROM customer_supplier WHERE id = " + str(supper_id) cur.execute(sql) result_data = cur.fetchall() return result_data[0][0]
def get_warehouse_id(supper_id): cur = bm.get_mia_cursor("mia_mirror") sql = "SELECT id FROM stock_warehouse WHERE supplier_id = " + str(supper_id) cur.execute(sql) result_data = cur.fetchall() return result_data[0][0]
def batch_check_all_by_stock_item_id(stock_id_list=[6985391], is_modify=False): cur = bm.get_mia_cursor("mia_mirror") for stock_item_id in stock_id_list: sql = "SELECT * FROM stock_item WHERE id = " + str(stock_item_id) cur.execute(sql) columns = [col[0] for col in cur.description] fetch = cur.fetchall() if len(fetch) == 0: print("数据库查询失败", stock_item_id) return db_item = dict(zip(columns, fetch[0])) item_id = db_item["item_id"] wid = db_item["warehouse_id"] order_sql = "select ifnull(count(oi.id), 0) as number " \ "from order_item oi left join orders os on oi.order_id = os.id " + \ "where os.warehouse_id= " + str(wid) + \ " and os.status in(1,2) and os.wdgj_status=1 and os.is_test= 0 " + \ "and oi.stock_item_id= " + str(db_item["id"]) + \ " group by oi.stock_item_id" cur.execute(order_sql) fetch = cur.fetchall() order_count = (0 if (len(fetch) == 0) else fetch[0][0]) redis_client = get_cluster_client() stock_key = gen_stock_key(item_id) pre_qty_field = gen_pre_qty_field(wid) redis_pre_qty = redis_client.hget(stock_key, pre_qty_field) if redis_pre_qty is None: print("预占库存不存在 stock_item_id = %d" % stock_item_id) return redis_pre_qty = int(redis_pre_qty) if order_count != db_item["pre_qty"]: print( "预占库存与订单不一致 stock_item_id = %d, db_pre_qty = %d, order_count= %d" % (stock_item_id, db_item["pre_qty"], order_count)) if is_modify: pre_qty_lock_key = gen_pre_qty_lock_key(item_id, wid) redis_client.delete(pre_qty_lock_key) redis_client.delete(stock_key) # 刷新 param_list = [{ "itemId": item_id, "warehouseIds": [wid], "isExact": 0 }] r_data = {"paramJSON": json.dumps(param_list)} r = requests.post( "http://10.5.107.234:7777/getStockQtyForums.sc", data=r_data) content = json.loads(r.content.decode("utf-8")) print(content["result"]) elif redis_pre_qty != db_item["pre_qty"]: print( "预占库存与redis不一致 stock_item_id = %d, db_pre_qty = %d, redis_pre_qty= %d" % (stock_item_id, db_item["pre_qty"], redis_pre_qty)) if is_modify: redis_client.delete(stock_key) # 刷新 param_list = [{ "itemId": item_id, "warehouseIds": [wid], "isExact": 0 }] r_data = {"paramJSON": json.dumps(param_list)} r = requests.post( "http://10.5.107.234:7777/getStockQtyForums.sc", data=r_data) content = json.loads(r.content.decode("utf-8")) print(content["result"]) cur.close()
# coding=utf-8 import sched import time from datetime import datetime import util as bm import requests from multiprocessing import Queue from collections import deque queue = deque() cur = bm.get_mia_cursor() create_time = datetime.now().strftime("%Y-%m-%d") # create_time = "2017-08-31" sql = "SELECT distinct item_id FROM mia_mirror.item_pictures where status = 1 and width = 0 and created_time like '" + create_time + "%'" with open('e:/pic_size.txt', 'wt') as f: print(datetime.now().strftime("%Y-%m-%d %H:%M:%S"), sql, file=f) cur.execute(sql) f_data = cur.fetchall() # print("本次需要跑出的数组长度 ", len(f_data)) for dd in f_data: queue.append(dd[0]) # 初始化sched模块的 scheduler 类 # 第一个参数是一个可以返回时间戳的函数,第二个参数可以在定时未到达之前阻塞。 schedule = sched.scheduler(time.time, time.sleep) # 被周期性调度触发的函数 def printTime(inc): if len(queue) > 0: itemId = queue.popleft()