def order_part(factory_id):
# order
cur = connect_postgresql()
start_timestamp = get_week_timestamp()
end_timestamp = time.mktime(datetime.datetime.now().timetuple())
w_sales = get_sales(cur, start_timestamp, end_timestamp, factory_id)
w_champ_name, w_champ_id, w_champ_sales = get_salesman(
cur, start_timestamp, factory_id)
start_timestamp, end_timestamp = get_month_timestamp(0)
m_sales = get_sales(cur, start_timestamp, end_timestamp, factory_id)
m_champ_name, m_champ_id, m_champ_sales = get_salesman(
cur, start_timestamp, factory_id)
start_timestamp, end_timestamp = get_month_timestamp(1)
lm_sales = get_sales(cur, start_timestamp, end_timestamp, factory_id)
m_rose = round((m_sales - lm_sales) / lm_sales * 100, 2) if lm_sales else 0
order = {}
order['w_sales'] = finance_format(w_sales)
order['w_champ_sales'] = finance_format(w_champ_sales)
order['w_champ_id'] = w_champ_id
order['w_champ_name'] = w_champ_name
order['m_sales'] = finance_format(m_sales)
order['m_rose'] = m_rose
order['m_champ_sales'] = finance_format(m_champ_sales)
order['m_champ_id'] = m_champ_id
order['m_champ_name'] = m_champ_name
disconnect_postgresql(cur)
return order
def get_factory_id(phone):
cur = connect_postgresql()
cur.execute("select factory from factory_users where phone = '%s';" %
phone)
tmp = cur.fetchall()
factory_id = tmp[0][0] if tmp else None
# 如果号码不存在？
return factory_id
def purchase_part(factory_id):
# purchase
cur = connect_postgresql()
start_timestamp = get_week_timestamp()
end_timestamp = time.mktime(datetime.datetime.now().timetuple())
w = purchase(cur, start_timestamp, end_timestamp, factory_id)
start_timestamp, end_timestamp = get_month_timestamp(1)
m = purchase(cur, start_timestamp, end_timestamp, factory_id)
material = {'w': w, 'm': m}
disconnect_postgresql(cur)
return material
def finance_part(factory_id):
# finance
cur = connect_postgresql()
start_timestamp, end_timestamp = get_month_timestamp(0)
month_0 = get_sales(cur, start_timestamp, end_timestamp, factory_id)
start_timestamp, end_timestamp = get_month_timestamp(1)
month_1 = get_sales(cur, start_timestamp, end_timestamp, factory_id)
rose = month_0 - month_1
four_months = []
start_timestamp, end_timestamp = get_month_timestamp(2)
month_2 = get_sales(cur, start_timestamp, end_timestamp, factory_id)
start_timestamp, end_timestamp = get_month_timestamp(3)
month_3 = get_sales(cur, start_timestamp, end_timestamp, factory_id)
month = month_part()
four_months.append({'m': str(month), 'sales': finance_format(month_0)})
four_months.append({
'm': str(correct_time(0, month - 1)[1]),
'sales': finance_format(month_1)
})
four_months.append({
'm': str(correct_time(0, month - 2)[1]),
'sales': finance_format(month_2)
})
four_months.append({
'm': str(correct_time(0, month - 3)[1]),
'sales': finance_format(month_3)
})
finance = {}
finance['rose'] = finance_format(rose)
finance['list'] = four_months
disconnect_postgresql(cur)
return finance
def month_store(factory_id):
"""仓库部 上个月产品出库 分类型号、名称、数量排名前3"""
cur = connect_postgresql()
start_timestamp, end_timestamp = month_timestamp(1)
cur.execute(
"select material_type_id, sum(material_count) from materials_log where factory = '{}' and time between {} and {}"
"group by material_type_id having sum(material_count) < 0 order by sum(material_count) limit 3;"
.format(factory_id, start_timestamp, end_timestamp))
# for test
# cur.execute("select material_type_id, sum(material_count) from materials_log where time between {} and {}"
# "group by material_type_id having sum(material_count) < 0 order by sum(material_count) limit 20;".format(
# start_timestamp, end_timestamp))
result_materials_log = cur.fetchall()
# print(result_materials_log)
month_store_list = []
if result_materials_log:
for material in result_materials_log:
m_store = {}
material_type_id = material[0] or None
material_count = material[1] or 0
m_store["count"] = abs(int(material_count))
if material_type_id:
cur.execute(
"select name, unit, category_id from material_types where id='{}';"
.format(material_type_id))
result_material_types = cur.fetchall()
# print("result_material_types=", result_material_types)
# if result_material_types:
name = result_material_types[0][
0] if result_material_types else ""
unit = result_material_types[0][
1] if result_material_types else ""
category_id = result_material_types[0][
2] if result_material_types else None
m_store["name"] = name
m_store["unit"] = unit
if category_id:
cur.execute(
"select name from material_categories where id='{}';".
format(category_id))
result_material_categories = cur.fetchall()
# print("result_material_categories=", result_material_categories) # [('电池材料',)]
category_name = result_material_categories[0][
0] if result_material_categories else ""
# print("category_name=", category_name)
m_store["category_name"] = category_name
else:
m_store["category_name"] = ""
else:
continue
month_store_list.append(m_store)
# print("month_store_list=", month_store_list) # [{'count': -1111}, {'count': -110}, {'count': -10}]
disconnect_postgresql(cur)
return month_store_list
def week_store(factory_id):
"""仓库部 本周产品出库 分类型号、名称、数量排名前3"""
cur = connect_postgresql()
timestamp = week_timestamp()
cur.execute(
"select material_type_id, sum(material_count) from materials_log where factory = '{}' and time > {}"
" group by material_type_id having sum(material_count) < 0 order by sum(material_count) limit 3;"
.format(factory_id, timestamp))
result_materials_log = cur.fetchall()
# print(result_materials_log) # [('9dHV77G24bDzCvEBd2', -4.0), ('9dGvfekQT1KWZUcBEW', -2.0), ('9dGvdvuJhIfFDBQ4IK', -1.0)]
week_store_list = []
if result_materials_log:
for material in result_materials_log:
w_store = {}
material_type_id = material[0] or None
material_count = material[1] or 0
w_store["count"] = abs(int(material_count))
if material_type_id:
cur.execute(
"select name, unit, category_id from material_types where id='{}';"
.format(material_type_id))
result_material_types = cur.fetchall()
# if result_material_types:
name = result_material_types[0][
0] if result_material_types else ""
unit = result_material_types[0][
1] if result_material_types else ""
category_id = result_material_types[0][
2] if result_material_types else None
# print("name=", name)
# print("unit=", unit)
# print("category_id=", category_id)
w_store["name"] = name
w_store["unit"] = unit
if category_id:
cur.execute(
"select name from material_categories where id='{}';".
format(category_id))
result_material_categories = cur.fetchall()
# print("result_material_categories=", result_material_categories) # [('电池材料',)]
category_name = result_material_categories[0][
0] if result_material_categories else None
# print("category_name=", category_name)
w_store["category_name"] = category_name
else:
w_store["category_name"] = ""
else:
continue
week_store_list.append(w_store)
# print("week_store_list=", week_store_list)
disconnect_postgresql(cur)
return week_store_list
def month_product(factory_id):
"""生产部 上个月产品出库 分类型号、名称、数量排名前3"""
cur = connect_postgresql()
start_timestamp, end_timestamp = month_timestamp(1)
cur.execute(
"select product_id, sum(count) from products_log where factory = '{}' and time between {} and {} group by product_id "
"having sum(count) < 0 order by sum(count) limit 3;".format(
factory_id, start_timestamp, end_timestamp))
# for test
# cur.execute(
# "select product_id, sum(count) from products_log where time between {} and {} group by product_id "
# "order by sum(count) limit 50;".format(15410, 1545804471))
result_products_log = cur.fetchall()
# print("result_products_log=", result_products_log)
month_product_list = []
if result_products_log:
for product in result_products_log:
# print("product=", product)
m_product = {}
product_id = product[0] or None
product_num = product[1] or 0
m_product["count"] = abs(int(product_num))
if product_id:
cur.execute(
"select name, unit, category_id from products where id='{}';"
.format(product_id))
result_products = cur.fetchall(
) # [('一次性口罩', 9deSVx9iUzI0tSKuK8), ('急支糖浆', 9dGjrURmx6lfeZbedc)]
# print("result_products=",
# result_products) # [], [('保温杯', '个', '')], [('999感冒灵', '盒', '9dF9r7e8NDdGQU76jQ')]
# if result_products:
product_name = result_products[0][0] if result_products else ""
product_unit = result_products[0][1] if result_products else ""
product_category_id = result_products[0][
2] if result_products else None
# print("product_name=", product_name) # 999感冒灵
# print("product_unit=", product_unit) # 盒
# print("product_category_id=", product_category_id) # '9dF9r7e8NDdGQU76jQ'
m_product["name"] = product_name
m_product["unit"] = product_unit
if product_category_id:
cur.execute(
"select name from product_categories where id='{}'".
format(product_category_id))
result_product_categories = cur.fetchall()
# print("result_product_categories=", result_product_categories) # [('口服',)], [('外用',)]
product_category_name = result_product_categories[0][
0] if result_product_categories else None
# print("product_category_name=", product_category_name) # 口服
m_product["category_name"] = product_category_name
else:
m_product["category_name"] = ""
else:
continue
month_product_list.append(m_product)
disconnect_postgresql(cur)
# print(month_product_list)
return month_product_list
def week_product(factory_id):
"""生产部 本周产品出库 分类型号、名称、数量排名前3"""
cur = connect_postgresql()
timestamp = week_timestamp() # 1545580800
cur.execute(
"select product_id, sum(count) from products_log where factory = '{}' and time > {} group by product_id having sum(count) < 0 "
"order by sum(count) limit 3;".format(factory_id, timestamp))
# cur.execute(
# "select product_id, sum(count) as co from products_log where time > {} group by product_id order by co limit 100;".format(
# 1545580))
result_products_log = cur.fetchall()
# print("result_products_log=", result_products_log)
week_product_list = []
if result_products_log:
for product in result_products_log:
# print("product=", product)
w_product = {}
product_id = product[0] or None
product_num = product[1] or 0
w_product["count"] = abs(int(product_num))
if product_id:
cur.execute(
"select name, unit, category_id from products where id='{}';"
.format(product_id))
result_products = cur.fetchall(
) # [('一次性口罩', 9deSVx9iUzI0tSKuK8), ('急支糖浆', 9dGjrURmx6lfeZbedc)]
# print("result_products=", result_products) # [], [('保温杯', '个', '')], [('999感冒灵', '盒', '9dF9r7e8NDdGQU76jQ')]
# if result_products:
product_name = result_products[0][0] if result_products else ""
product_unit = result_products[0][1] if result_products else ""
product_category_id = result_products[0][
2] if result_products else None
# print("product_name=", product_name) # 999感冒灵
# print("product_unit=", product_unit) # 盒
# print("product_category_id=", product_category_id) # '9dF9r7e8NDdGQU76jQ'
w_product["name"] = product_name
w_product["unit"] = product_unit
if product_category_id:
cur.execute(
"select name from product_categories where id='{}'".
format(product_category_id))
result_product_categories = cur.fetchall()
# print("result_product_categories=", result_product_categories) # [('口服',)], [('外用',)]
product_category_name = result_product_categories[0][
0] if result_product_categories else None
# print("product_category_name=", product_category_name) # 口服
w_product["category_name"] = product_category_name
else:
w_product["category_name"] = ""
else:
continue
week_product_list.append(w_product)
# print(week_product_list)
disconnect_postgresql(cur)
return week_product_list
