def get_detail_info(second_srv_id: int, start_time: str, end_time: str): sql_text = ''' SELECT bi.id, bi.buy_date, si.brand_name, si.model_name, bi.number, si.unit, bi.unit_price, bi.total, bi.supplier_id, sl.supplier_name, si.first_service_name || '-' || si.second_service_name, bi.paid, bi.unpaid, di.value_desc, bi.stock_id FROM buy_info bi, stock_info si, dictionary di, supplier sl WHERE si.second_service_id = {} and bi.stock_id = si.id and bi.buy_type = {} and di.key_id = bi.buy_type and di.group_name = 'buy_type' and sl.id = bi.supplier_id and bi.buy_date BETWEEN '{}' and '{}' '''.format(second_srv_id, BuyInfo.bought(), start_time, end_time) result = execute(sql_text) return result
def _add_buy_info(stock_id, supplier_id, price, number, buy_date, unpaid, paid, total, payment, note, balance): buy_info = BuyInfo() buy_info.buy_date(buy_date) buy_info.stock_id(stock_id) buy_info.supplier_id(supplier_id) buy_info.unit_price(price) buy_info.payment_method(payment) buy_info.number(abs(number)) create_time = time_utils.get_now() buy_info.create_time(create_time) create_op = Common.config.login_user_info[0] buy_info.create_op(create_op) buy_info.paid(abs(paid)) buy_info.unpaid(abs(unpaid)) buy_info.total(abs(total)) buy_info.note(note) buy_info.buy_type(BuyInfo.bought()) # 计算剩余量 if balance < 0: left_number = number + balance else: left_number = number buy_info.left(left_number) return buy_handler.add_buy_info(buy_info)
def get_history_buy_info_by_model_id(model_id: int): sql_text = ''' SELECT sts.brand_id, sts.model_id, sts.brand_name, sts.model_name, min_price, avg_price, lbi.unit_price FROM ( SELECT si.brand_id, si.model_id, si.brand_name, si.model_name, min(bi.unit_price) min_price, avg(bi.unit_price) avg_price, max(bi.id) max_id FROM buy_info bi, stock_info si WHERE bi.stock_id = si.id AND si.model_id = {} AND bi.buy_type = {} GROUP BY si.brand_name, si.model_name ) sts, buy_info lbi WHERE lbi.id = max_id''' \ .format(model_id, BuyInfo.bought()) result = execute(sql_text) return result
def get_buy_info_summary_by_time(start_date: str, end_date: str): sql_text = ''' SELECT si.first_service_id, si.second_service_id, si.first_service_name, si.second_service_name, sum(bi.number) total_number, sum(bi.total) total_price FROM buy_info bi, stock_info si WHERE bi.stock_id = si.id AND bi.buy_type = {} AND bi.buy_date BETWEEN '{}' AND '{}' GROUP BY si.first_service_id, si.first_service_name, si.second_service_id, si.second_service_name ORDER BY si.first_service_id, si.first_service_name, si.second_service_id, si.second_service_name''' \ .format(BuyInfo.bought(), start_date, end_date) result = execute(sql_text) return result
def get_compare_info(model_id: int): sql_text = ''' SELECT sp.supplier_name, si.brand_name, si.model_name, avg(bi.unit_price) avg_price, count(1) buy_times, sum(bi.number) total_number FROM buy_info bi, stock_info si, supplier sp WHERE bi.stock_id = si.id AND si.model_id = {} AND bi.buy_type = {} AND bi.supplier_id = sp.id GROUP BY sp.supplier_name, si.brand_name, si.model_name ORDER BY sp.supplier_name, si.brand_name, si.model_name ''' \ .format(model_id, BuyInfo.bought()) result = execute(sql_text) return result
def get_all_buy_info(): sql_text = ''' SELECT bi.id, bi.buy_date, si.brand_name, si.model_name, bi.number, si.unit, bi.unit_price, bi.total, bi.supplier_id, sl.supplier_name, si.first_service_name || '-' || si.second_service_name, bi.paid, bi.unpaid, di.value_desc, bi.stock_id FROM buy_info bi, stock_info si, supplier sl, service sr, dictionary di WHERE bi.stock_id = si.id AND bi.supplier_id = sl.id AND sr.id = si.second_service_id AND di.key_id = bi.buy_type AND di.group_name = 'buy_type' AND bi.buy_type in({},{}) ORDER BY buy_date DESC'''.format(BuyInfo.bought(), BuyInfo.returned()) result = execute(sql_text) return result
def get_unpaid_gt_zero(stock_id: int): sql_text = '''SELECT ID, unpaid, paid FROM buy_info WHERE stock_id = {} AND unpaid > 0.0 AND buy_type = {} order by buy_date''' \ .format(stock_id, BuyInfo.bought()) return execute(sql_text)
def get_left_gt_zero(stock_id: int): sql_text = '''SELECT ID, number, left_number FROM buy_info WHERE stock_id = {} AND left_number > 0 AND buy_type = {} order by buy_date''' \ .format(stock_id, BuyInfo.bought()) return execute(sql_text)
def add_buy_info(stock_id, supplier_id, price, number, buy_date, unpaid, paid, total, payment, note, left_number, op=None, buy_type=None, state=0): buy_info = BuyInfo() buy_info.buy_date(buy_date) buy_info.stock_id(stock_id) buy_info.supplier_id(supplier_id) buy_info.unit_price(price) buy_info.payment_method(payment) if buy_type == BuyInfo.calibrated(): buy_info.number(number) buy_info.total(total) else: buy_info.number(abs(number)) buy_info.total(abs(total)) create_time = time_utils.get_now() buy_info.create_time(create_time) if not op: create_op = common.config.login_user_info[0] else: create_op = op buy_info.create_op(create_op) buy_info.paid(abs(paid)) buy_info.unpaid(abs(unpaid)) buy_info.note(note) # 判断是进货还是退货 if not buy_type: if number < 0: buy_info.buy_type(BuyInfo.returned()) left_number = 0 else: buy_info.buy_type(BuyInfo.bought()) else: buy_info.buy_type(buy_type) if state: buy_info.state(state) buy_info.left(left_number) return buy_handler.add_buy_info(buy_info)
def get_arrears_info_buy(supplier_id: int): sql_text = ''' SELECT bi.buy_date, si.brand_name, si.model_name, bi.number, si.unit, bi.unit_price, bi.total, bi.paid, bi.unpaid, bi.id FROM buy_info bi, stock_info si, supplier sl WHERE bi.stock_id = si.id AND bi.supplier_id = sl.id AND bi.unpaid > 0.0 AND sl.id = {} AND bi.buy_type = {} ORDER BY buy_date''' \ .format(supplier_id, BuyInfo.bought()) result = execute(sql_text) return result