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 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