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