def list_with_run_checks(car_id, limit): query = """SELECT * FROM ( SELECT R.date_refuel ,R.km ,R.type ,R.amount ,R.sum ,R.comment ,null as 'run_check' FROM refuels_flow AS R WHERE R.car_id = %s UNION ALL SELECT C.date ,C.km ,"" ,"" ,"" ,C.comment ,1 FROM run_checks_flow AS C WHERE C.car_id = %s ORDER BY date_refuel DESC LIMIT %s) AS reorder ORDER BY date_refuel ASC """ limit = 10 params = (car_id, car_id, limit) result = fetch_sql_allintuple(query, params=params) return result
def report_by_dump_group(): query = """SELECT dump_groups.name as dump_group_name ,flow.dump_group_id, SUM(IF(flow.employee_group = 0, IFNULL(remains, qty) , NULL)) AS remains0, SUM(IF(flow.employee_group = 1, IFNULL(remains,qty) , NULL)) AS remains1 FROM talons_flow as flow JOIN dump_groups AS dump_groups ON flow.dump_group_id = dump_groups.id WHERE (is_closed IS NULL AND NOT (operation_type IN (1, 2))) GROUP BY dump_group_id""" result = fetch_sql_allintuple(query, params=None) return result
def by_company_status(): query = """SELECT cs.val as status_name ,cs.id as company_status_id ,SUM(qty) as summ FROM bunker_objects_remains AS r LEFT JOIN objects AS o ON o.id = r.object_id LEFT JOIN companies AS co ON co.id = o.company_id LEFT JOIN company_status AS cs ON cs.id = co.status_id WHERE o.company_id IS NOT NULL GROUP BY cs.id""" result = fetch_sql_allintuple(query, params=None) return result
def by_object_type(): query = """SELECT ot.val as type_name ,SUM(IF (r.type_id = 1, qty, NULL)) as summ1 ,SUM(IF (r.type_id = 2, qty, NULL)) as summ2 ,SUM(IF (r.type_id = 3, qty, NULL)) as summ3 ,SUM(IF (r.type_id = 4, qty, NULL)) as summ4 ,SUM(IF (r.type_id = 5, qty, NULL)) as summ5 ,SUM(IF (r.type_id = 6, qty, NULL)) as summ6 FROM bunker_objects_remains AS r JOIN objects AS o ON o.id = r.object_id JOIN object_types AS ot ON ot.id = o.type_id GROUP BY o.type_id""" result = fetch_sql_allintuple(query, params=None) return result
def refuels_on_period_for_car(date_start, date_end, car_pk): # получаем список всех заправок от (последней ПЕРЕД периодом или первой ВНУТРИ периода) до последней ВНУТРИ # периода. Cписок для расчета "пробега" и показа в отчете по всем заправкам за период # БЕЗ УЧЕТА ДРАЙВЕРА, для КОНКРЕТНОЙ машины log.info("--- date_start: %s, date_end: %s, car_pk: %s" % (date_start, date_end, car_pk)) query = """SELECT R2.car_id ,R2.date_refuel ,R2.driver_id ,P.id AS driver_person_id ,P.family_name ,P.given_name ,P.nick_name ,R2.amount ,R2.km ,R2.sum ,R2.checked FROM refuels_flow AS R2 LEFT JOIN employies AS E ON E.id = R2.driver_id LEFT JOIN persons AS P ON P.id = E.person_id LEFT OUTER JOIN ( SELECT LAST.car_id ,IFNULL( ( SELECT MAX(date_refuel) FROM refuels_flow WHERE date_refuel < %(date_start)s AND car_id = %(car_pk)s ), MIN(TFIRST.date_refuel) ) as date_start ,MAX(LAST.date_refuel) as date_end FROM refuels_flow AS LAST JOIN refuels_flow AS TFIRST ON TFIRST.car_id = LAST.car_id WHERE LAST.date_refuel >= %(date_start)s AND LAST.date_refuel < %(date_end)s AND LAST.car_id = %(car_pk)s ) AS DATES_INTERVAL ON DATES_INTERVAL.car_id = R2.car_id WHERE R2.date_refuel BETWEEN DATES_INTERVAL.date_start AND DATES_INTERVAL.date_end""" params = { 'date_start': date_start, 'date_end': date_end, 'car_pk': car_pk } result = fetch_sql_allintuple(query, params=params) return result
def drivers_list_races_and_summary(date_start, date_end): result = {} query = """SELECT R2.driver_id ,R2.races_done as races ,R2.driver_family_name ,R2.driver_given_name ,R2.driver_nick_name ,ss.id as summary_id ,ss.check_status ,ss.date_add ,ss.races_done ,ss.total_hodkis ,ss.total_run ,ss.km_on_hodkis ,ss.total_amount ,ss.average_consumption ,ss.over_run_status ,ss.over_fuel_status ,ss.fuel_comment ,ss.run_comment ,ss.over_run_penalty ,ss.over_fuel_penalty FROM ( SELECT R.driver_id AS driver_id ,P.id ,P.nick_name AS driver_nick_name ,P.family_name AS driver_family_name ,P.given_name AS driver_given_name ,COUNT(R.id) AS races_done FROM races AS R JOIN employies AS E ON E.id = R.driver_id JOIN persons AS P ON P.id = E.person_id WHERE (R.date_race >= %(date_start)s AND R.date_race < %(date_end)s) GROUP BY driver_id ) AS R2 LEFT OUTER JOIN salary_month_summary AS ss ON ss.employee_id = R2.driver_id AND `ss`.`month` = MONTH(%(date_start)s) AND `ss`.`year`= YEAR(%(date_start)s)""" params = {'date_start': date_start, 'date_end': date_end} result['data'] = fetch_sql_allintuple(query, params=params) return result
def list_flow(): query = """SELECT o.transaction_id as id ,ABS(o.qty) as QTY_o ,o.object_id as object_id_o ,i.object_id as object_id_i ,ob.name FROM dummy_flow AS o LEFT JOIN dummy_flow AS i ON i.transaction_id = o.transaction_id AND i.object_id != o.object_id LEFT JOIN objects AS ob ON ob.id = o.object_id WHERE (i.id IS NOT NULL AND i.qty > 0) OR i.id IS NULL ORDER BY id DESC LIMIT %s """ limit = 10 params = (limit, ) result = fetch_sql_allintuple(query, params=params) return result
def by_company_id(company_id): query ="""SELECT o.id as object_id ,o.name as object_name ,ot.id as type_id ,ot.val as type_name ,SUM(IF (r.type_id = 1, qty, NULL)) as type1_summ ,SUM(IF (r.type_id = 2, qty, NULL)) as type2_summ ,SUM(IF (r.type_id = 3, qty, NULL)) as type3_summ ,SUM(IF (r.type_id = 4, qty, NULL)) as type4_summ ,SUM(IF (r.type_id = 5, qty, NULL)) as type5_summ ,SUM(IF (r.type_id = 6, qty, NULL)) as type6_summ FROM objects AS o JOIN object_types AS ot ON ot.id = o.type_id LEFT JOIN bunker_objects_remains AS r ON o.id = r.object_id WHERE o.company_id = %s AND ot.id = 3 GROUP BY o.id""" result = fetch_sql_allintuple(query, params=(company_id,)) return result
def by_company_id(company_id): query = """SELECT o.id as object_id ,o.name as object_name ,ot.id as type_id ,ot.val as type_name ,SUM(IF (r.type_id = 1, qty, NULL)) as type1_summ ,SUM(IF (r.type_id = 2, qty, NULL)) as type2_summ ,SUM(IF (r.type_id = 3, qty, NULL)) as type3_summ ,SUM(IF (r.type_id = 4, qty, NULL)) as type4_summ ,SUM(IF (r.type_id = 5, qty, NULL)) as type5_summ ,SUM(IF (r.type_id = 6, qty, NULL)) as type6_summ FROM objects AS o JOIN object_types AS ot ON ot.id = o.type_id LEFT JOIN bunker_objects_remains AS r ON o.id = r.object_id WHERE o.company_id = %s AND ot.id = 3 GROUP BY o.id""" result = fetch_sql_allintuple(query, params=(company_id, )) return result
def refuels_on_period_for_car(date_start, date_end, car_pk): # получаем список всех заправок от (последней ПЕРЕД периодом или первой ВНУТРИ периода) до последней ВНУТРИ # периода. Cписок для расчета "пробега" и показа в отчете по всем заправкам за период # БЕЗ УЧЕТА ДРАЙВЕРА, для КОНКРЕТНОЙ машины log.info("--- date_start: %s, date_end: %s, car_pk: %s" % (date_start, date_end, car_pk)) query = """SELECT R2.car_id ,R2.date_refuel ,R2.driver_id ,P.id AS driver_person_id ,P.family_name ,P.given_name ,P.nick_name ,R2.amount ,R2.km ,R2.sum ,R2.checked FROM refuels_flow AS R2 LEFT JOIN employies AS E ON E.id = R2.driver_id LEFT JOIN persons AS P ON P.id = E.person_id LEFT OUTER JOIN ( SELECT LAST.car_id ,IFNULL( ( SELECT MAX(date_refuel) FROM refuels_flow WHERE date_refuel < %(date_start)s AND car_id = %(car_pk)s ), MIN(TFIRST.date_refuel) ) as date_start ,MAX(LAST.date_refuel) as date_end FROM refuels_flow AS LAST JOIN refuels_flow AS TFIRST ON TFIRST.car_id = LAST.car_id WHERE LAST.date_refuel >= %(date_start)s AND LAST.date_refuel < %(date_end)s AND LAST.car_id = %(car_pk)s ) AS DATES_INTERVAL ON DATES_INTERVAL.car_id = R2.car_id WHERE R2.date_refuel BETWEEN DATES_INTERVAL.date_start AND DATES_INTERVAL.date_end""" params = {'date_start': date_start, 'date_end': date_end, 'car_pk': car_pk} result = fetch_sql_allintuple(query, params=params) return result
def list_bunker_remains(company_id): query = """SELECT ob.id, ob.name, addresses.city, addresses.street, addresses.app, addresses.lat, addresses.lng, gb.summ FROM objects AS ob LEFT JOIN ( SELECT object_id, SUM(qty) as summ FROM ( SELECT f1.object_in_id as object_id ,f1.qty as qty ,o1.type_id as object_type_id FROM bunker_flow AS f1 LEFT JOIN objects AS o1 ON f1.object_in_id = o1.id WHERE o1.type_id = 3 UNION ALL SELECT f1.object_out_id as object_id ,-1* f1.qty as qty ,o1.type_id as object_type_id FROM bunker_flow AS f1 LEFT JOIN objects AS o1 ON f1.object_out_id = o1.id WHERE o1.type_id = 3 ) as flow GROUP BY object_id ) as gb ON gb.object_id = ob.id LEFT JOIN addresses on addresses.id = ob.address_id WHERE ob.company_id = %s ORDER BY summ DESC""" params = (company_id,) result = fetch_sql_allintuple(query, params) return result
def driver_month_stats(date_start, date_end, driver_pk): query = """SELECT RR.car_id ,C.reg_num ,C.nick_name ,C.fuel_norm ,CM.val AS car_model ,COUNT(*) AS races_done ,SUM(RR.hodkis) AS total_hodkis ,IFNULL(J3.total_refuels,0) AS total_refuels ,J3.total_amount ,J3.total_run ,ROUND(((J3.total_amount / J3.total_run) * 100),1) AS lit_on_100 ,ROUND(J3.total_run / SUM(RR.hodkis), 1) AS km_on_hodkis ,(ROUND(((J3.total_amount / J3.total_run) * 100),1)) - C.fuel_norm AS fuel_overuse FROM races AS RR LEFT JOIN cars AS C ON C.id = RR.car_id LEFT JOIN car_models AS CM ON CM.id = C.id LEFT OUTER JOIN ( SELECT MIN.car_id ,( IFNULL(( SELECT ((SUM(last_km) - SUM(start_km)) + MAX.km) FROM car_speedometer_change_log WHERE date_change BETWEEN %(date_start)s AND %(date_end)s AND car_id = MIN.car_id ), MAX.km) - IFNULL(( SELECT PREV.km FROM refuels_flow PREV JOIN ( SELECT car_id, MAX(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel < %(date_start)s AND driver_id = %(driver_pk)s GROUP BY car_id ) AS PREVJ ON PREV.car_id = PREVJ.car_id AND PREV.date_refuel = PREVJ.date_refuel WHERE PREV.car_id = MIN.car_id ), MIN.km)) as total_run ,MAX.last_amount ,MAX.date_refuel as date_refuel_last ,(SUM(COUNT.amount) - MAX.last_amount) as total_amount ,COUNT(COUNT.id) as total_refuels FROM refuels_flow AS MIN JOIN ( SELECT car_id, MIN(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel >= %(date_start)s AND date_refuel < %(date_end)s AND driver_id = %(driver_pk)s GROUP BY car_id ) AS MINJ ON MIN.car_id = MINJ.car_id AND MIN.date_refuel = MINJ.date_refuel JOIN ( SELECT LAST.id, LAST.car_id, LAST.date_refuel, LAST.km, LAST.amount as last_amount FROM refuels_flow LAST JOIN ( SELECT car_id, MAX(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel >= %(date_start)s AND date_refuel < %(date_end)s AND driver_id = %(driver_pk)s GROUP BY car_id) AS LASTJ ON LAST.car_id = LASTJ.car_id AND LAST.date_refuel = LASTJ.date_refuel ) AS MAX ON MAX.car_id = MIN.car_id LEFT JOIN refuels_flow AS COUNT ON COUNT.car_id = MIN.car_id WHERE COUNT.date_refuel <= MAX.date_refuel AND COUNT.date_refuel >= IFNULL(( SELECT PREV.date_refuel FROM refuels_flow PREV JOIN ( SELECT car_id, MAX(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel < %(date_start)s AND driver_id = %(driver_pk)s GROUP BY car_id ) AS PREVJ ON PREV.car_id = PREVJ.car_id AND PREV.date_refuel = PREVJ.date_refuel WHERE PREV.car_id = MIN.car_id ), MIN.date_refuel) GROUP BY COUNT.car_id ) AS J3 ON J3.car_id = RR.car_id WHERE RR.date_race >= %(date_start)s AND RR.date_race < %(date_end)s GROUP BY car_id""" params = {'date_start': date_start, 'date_end': date_end, 'driver_pk': driver_pk} result = fetch_sql_allintuple(query, params=params) return result
def get_flow(): query = """SELECT * FROM dummy_flow""" result = fetch_sql_allintuple(query, params=None) return result
def driver_month_stats(date_start, date_end, driver_pk): query = """SELECT RR.car_id ,C.reg_num ,C.nick_name ,C.fuel_norm ,CM.val AS car_model ,COUNT(*) AS races_done ,SUM(RR.hodkis) AS total_hodkis ,IFNULL(J3.total_refuels,0) AS total_refuels ,J3.total_amount ,J3.total_run ,ROUND(((J3.total_amount / J3.total_run) * 100),1) AS lit_on_100 ,ROUND(J3.total_run / SUM(RR.hodkis), 1) AS km_on_hodkis ,(ROUND(((J3.total_amount / J3.total_run) * 100),1)) - C.fuel_norm AS fuel_overuse FROM races AS RR LEFT JOIN cars AS C ON C.id = RR.car_id LEFT JOIN car_models AS CM ON CM.id = C.id LEFT OUTER JOIN ( SELECT MIN.car_id ,( IFNULL(( SELECT ((SUM(last_km) - SUM(start_km)) + MAX.km) FROM car_speedometer_change_log WHERE date_change BETWEEN %(date_start)s AND %(date_end)s AND car_id = MIN.car_id ), MAX.km) - IFNULL(( SELECT PREV.km FROM refuels_flow PREV JOIN ( SELECT car_id, MAX(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel < %(date_start)s AND driver_id = %(driver_pk)s GROUP BY car_id ) AS PREVJ ON PREV.car_id = PREVJ.car_id AND PREV.date_refuel = PREVJ.date_refuel WHERE PREV.car_id = MIN.car_id ), MIN.km)) as total_run ,MAX.last_amount ,MAX.date_refuel as date_refuel_last ,(SUM(COUNT.amount) - MAX.last_amount) as total_amount ,COUNT(COUNT.id) as total_refuels FROM refuels_flow AS MIN JOIN ( SELECT car_id, MIN(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel >= %(date_start)s AND date_refuel < %(date_end)s AND driver_id = %(driver_pk)s GROUP BY car_id ) AS MINJ ON MIN.car_id = MINJ.car_id AND MIN.date_refuel = MINJ.date_refuel JOIN ( SELECT LAST.id, LAST.car_id, LAST.date_refuel, LAST.km, LAST.amount as last_amount FROM refuels_flow LAST JOIN ( SELECT car_id, MAX(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel >= %(date_start)s AND date_refuel < %(date_end)s AND driver_id = %(driver_pk)s GROUP BY car_id) AS LASTJ ON LAST.car_id = LASTJ.car_id AND LAST.date_refuel = LASTJ.date_refuel ) AS MAX ON MAX.car_id = MIN.car_id LEFT JOIN refuels_flow AS COUNT ON COUNT.car_id = MIN.car_id WHERE COUNT.date_refuel <= MAX.date_refuel AND COUNT.date_refuel >= IFNULL(( SELECT PREV.date_refuel FROM refuels_flow PREV JOIN ( SELECT car_id, MAX(date_refuel) AS date_refuel FROM refuels_flow WHERE date_refuel < %(date_start)s AND driver_id = %(driver_pk)s GROUP BY car_id ) AS PREVJ ON PREV.car_id = PREVJ.car_id AND PREV.date_refuel = PREVJ.date_refuel WHERE PREV.car_id = MIN.car_id ), MIN.date_refuel) GROUP BY COUNT.car_id ) AS J3 ON J3.car_id = RR.car_id WHERE RR.date_race >= %(date_start)s AND RR.date_race < %(date_end)s GROUP BY car_id""" params = { 'date_start': date_start, 'date_end': date_end, 'driver_pk': driver_pk } result = fetch_sql_allintuple(query, params=params) return result