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_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 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_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_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_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_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()
