def _gather_data(self, begin=None, end=None):
begin, end = _standard_period(begin, end)
r = session().connection().execute(
"""select CAST( date_trunc('month',start_time) AS DATE) as m, trunc(sum(duration)) as duration
from timetracks
join tasks on tasks.task_id = timetracks.task_id
left outer join tasks_operation_definitions on tasks_operation_definitions.task_id = tasks.task_id
left outer join operation_definitions on (operation_definitions.operation_definition_id = tasks_operation_definitions.operation_definition_id)
where short_id is not null and start_time between timestamp '{}' and timestamp '{}'
group by m
order by m""".format(timestamp_to_pg(begin),
timestamp_to_pg(end))).fetchall()
indirects = [("Indirects", t[0], t[1]) for t in r]
r = session().connection().execute(
"""select CAST( date_trunc('month',start_time) AS DATE) as m, trunc(sum(duration)) as duration from timetracks
join tasks on tasks.task_id = timetracks.task_id
left outer join tasks_operations on tasks_operations.task_id = tasks.task_id
left outer join operations on tasks_operations.operation_id = operations.operation_id
left outer join operation_definitions on operation_definitions.operation_definition_id = operations.operation_definition_id
where short_id is not null and start_time between timestamp '{}' and timestamp '{}'
group by m
order by m;""".format(timestamp_to_pg(begin),
timestamp_to_pg(end))).fetchall()
directs = [("Directs", t[0], t[1]) for t in r]
x_legends, legends, array = to_arrays(directs + indirects)
self.set_data(x_legends, legends, array)
def direct_work_cost_chart(self, begin: date = None, end: date = None):
begin, end, duration_in_months = _standard_period2(begin, end)
r = session().connection().execute("""
with dates as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month
),
data as (
select short_id,
CAST( date_trunc('month',start_time) AS DATE) as m,
sum(duration*opdef.hourly_cost) as duration
from timetracks
join tasks on tasks.task_id = timetracks.task_id
join tasks_operations on tasks_operations.task_id = tasks.task_id
join operations on tasks_operations.operation_id = operations.operation_id
join operation_definitions opdef on opdef.operation_definition_id = operations.operation_definition_id
where short_id is not null and start_time between timestamp '{}' and timestamp '{}'
group by short_id,m
order by short_id,m
)
select coalesce(short_id,''), month, coalesce( duration, 0)
from dates
left join data on data.m = dates.month
""".format(timestamp_to_pg(begin), duration_in_months,
timestamp_to_pg(begin), timestamp_to_pg(end))).fetchall()
x_legends, legends, array = to_arrays(r)
return GraphData(x_legends, legends, array)
def direct_vs_indirect_chart(self, begin: date = None, end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
durations as (
select
date_trunc( 'MONTH', timetracks.start_time) as start_time,
trunc(sum( coalesce( timetracks.duration,0))) as duration
from timetracks
join horse.tasks on tasks.task_id = timetracks.task_id
join horse.tasks_operation_definitions on horse.tasks_operation_definitions.task_id = tasks.task_id
join horse.operation_definitions opdef on opdef.operation_definition_id = horse.tasks_operation_definitions.operation_definition_id
group by date_trunc( 'MONTH', timetracks.start_time)
)
select
month_start_day as month_year,
trunc(sum( coalesce( duration,0))) as duration
from month_start
left join durations on durations.start_time = month_start_day
group by month_start_day""".format(timestamp_to_pg(begin),
duration)).fetchall()
indirects = [("Indirects", t[0], t[1]) for t in r]
r = session().connection().execute("""with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
durations as (
select
date_trunc( 'MONTH', timetracks.start_time) as start_time,
trunc(sum( coalesce( timetracks.duration,0))) as duration
from timetracks
join horse.tasks on horse.tasks.task_id = timetracks.task_id
join horse.tasks_operations on horse.tasks_operations.task_id = tasks.task_id
join horse.operations on horse.operations.operation_id = horse.tasks_operations.operation_id
join horse.operation_definitions opdef on opdef.operation_definition_id = horse.operations.operation_definition_id
group by date_trunc( 'MONTH', timetracks.start_time)
)
select
month_start_day as month_year,
trunc(sum( coalesce( duration,0))) as duration
from month_start
left join durations on durations.start_time = month_start_day
group by month_start_day
""".format(timestamp_to_pg(begin), duration)).fetchall()
directs = [("Directs", t[0], t[1]) for t in r]
x_legends, legends, array = to_arrays(directs + indirects)
return GraphData(x_legends, legends, array)
def preorder_parts_value_chart(self, begin: date = None, end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
preorder_sent as (
select order_id, date_trunc( 'MONTH', was_preorder_on) as month_year
from horse.orders
),
order_values as (
select o.order_id,
sum(opart.sell_price * opart.quantity ) as value
from order_parts opart
join orders o on o.order_id = opart.order_id
group by o.order_id
)
select month_start_day as month_year, total_value
from (
select
month_start.month_start_day,
coalesce( round(sum(value)), 0) as total_value
from month_start
left join preorder_sent on preorder_sent.month_year = date_trunc( 'MONTH', month_start.month_start_day)
left join order_values on order_values.order_id = preorder_sent.order_id
group by month_start.month_start_day) as data
order by data.month_start_day
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def order_parts_value_chart(self, begin: date = None, end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
parts_values as (
select
date_trunc( 'MONTH', o.creation_date) as month_year,
sum(opart.sell_price * opart.quantity ) as value
from order_parts opart
join orders o on o.order_id = opart.order_id
group by date_trunc( 'MONTH', o.creation_date)
)
select
date_trunc( 'MONTH', month_start.month_start_day) as month_year,
coalesce( parts_values.value, 0)
from month_start
left join parts_values on parts_values.month_year = month_start.month_start_day
order by date_trunc( 'MONTH', month_start.month_start_day)
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def number_of_created_order_parts_chart(self,
begin: date = None,
end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
parts_created_per_month as (
select
date_trunc( 'MONTH', o.creation_date) as month_year,
count(*) as cnt
from orders o
join order_parts op on op.order_id = o.order_id
group by date_trunc( 'MONTH', o.creation_date)
)
select
date_trunc( 'MONTH', month_start.month_start_day) as month_year,
coalesce( parts_created_per_month.cnt, 0)
from month_start
left join parts_created_per_month on parts_created_per_month.month_year = month_start.month_start_day
order by date_trunc( 'MONTH', month_start.month_start_day)
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def number_of_customer_non_conformity_chart(self,
begin: date = None,
end: date = None) -> GraphData:
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
nc_per_month as (
select
date_trunc( 'MONTH', quality_events.when) as month_year,
count(*) nb_non_conformity
from quality_events
where kind = 'non_conform_customer'
group by date_trunc( 'MONTH', quality_events.when)
)
select
date_trunc( 'MONTH', month_start.month_start_day) as month_year,
coalesce( nc_per_month.nb_non_conformity, 0)
from month_start
left join nc_per_month on nc_per_month.month_year = month_start.month_start_day
order by date_trunc( 'MONTH', month_start.month_start_day)
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def direct_work_evolution_chart(self,
begin: date = None,
end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""with dates as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS day
),
data as (
select short_id,
CAST( date_trunc('month',start_time) AS DATE) as m,
trunc(sum(duration)) as duration
from timetracks
join tasks on tasks.task_id = timetracks.task_id
left outer join tasks_operations on tasks_operations.task_id = tasks.task_id
left outer join operations on tasks_operations.operation_id = operations.operation_id
left outer join operation_definitions on operation_definitions.operation_definition_id = operations.operation_definition_id
where short_id is not null and start_time between timestamp '{}' and timestamp '{}'
group by short_id,m
)
select
horse.operation_definitions.short_id,
dates.day days,
coalesce( data.duration, 0)
from dates
-- cross joins makes sure we have all the dates and all the operation id's. It's necessary
-- to build the series
cross join horse.operation_definitions
left join data on date_trunc('month',data.m) = date_trunc('month',dates.day)
and data.short_id = horse.operation_definitions.short_id
order by days, short_id""".format(timestamp_to_pg(begin), duration,
timestamp_to_pg(begin),
timestamp_to_pg(end))).fetchall()
x_legends, legends, array = to_arrays(r) # x,y value
return GraphData(x_legends, legends, array)
def indirect_work_evolution_chart(self,
begin: date = None,
end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
short_ids as (
select short_id, operation_definition_id
from horse.operation_definitions
where short_id is not null and not on_operation
),
durations as (
select
date_trunc( 'MONTH', timetracks.start_time) as start_time,
short_id,
trunc(sum( coalesce( timetracks.duration,0))) as duration
from timetracks
join horse.tasks on tasks.task_id = timetracks.task_id
join horse.tasks_operation_definitions on horse.tasks_operation_definitions.task_id = tasks.task_id
join horse.operation_definitions opdef on opdef.operation_definition_id = horse.tasks_operation_definitions.operation_definition_id
group by date_trunc( 'MONTH', timetracks.start_time), short_id
)
select
short_ids.short_id,
month_start_day as month_year,
trunc(sum( coalesce( duration,0))) as duration
from month_start
left join short_ids on 1=1 -- left join because we might have no short id at all...
left join durations on durations.start_time = month_start_day and durations.short_id = short_ids.short_id
group by short_ids.short_id, month_start_day
order by short_ids.short_id, month_start_day
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, legends, array = to_arrays(
r) # r = serie_id, x coord, value
return GraphData(x_legends, legends, array)
def number_of_internal_non_conformity_chart(self,
begin: date = None,
end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
completed_parts as (
-- Figure when each part is completed
select order_part_id, completion_date
from
(select
case when (spq.q_out >= p.quantity) and (p.completed_date is not null) THEN greatest( p.completed_date, spq.first_slip_time)
when (spq.q_out >= p.quantity) and (p.completed_date is null) THEN spq.first_slip_time
else p.completed_date -- can be null !
end as completion_date,
p.order_part_id
from
order_parts as p
left join
(select order_part_id,
sum(quantity_out) as q_out,
min(creation) as first_slip_time,
max(creation) as last_slip_time
from delivery_slip_parts sp
join delivery_slip s on s.delivery_slip_id = sp.delivery_slip_id
where s.active
group by order_part_id) as spq
on p.order_part_id = spq.order_part_id) cpl_date
where cpl_date.completion_date is not null
),
completed_per_month as (
select
date_trunc( 'MONTH',completion_date) as month_year,
count(*) as cnt
from completed_parts
group by date_trunc( 'MONTH',completion_date)
),
nc_per_month as (
select
date_trunc( 'MONTH', quality_events.when) as month_year,
count(*) as cnt
from quality_events
where kind = 'non_conform_intern'
group by date_trunc( 'MONTH', quality_events.when)
),
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
)
select
date_trunc( 'MONTH', month_start_day) as month_year,
case when completed_per_month.cnt > 0
THEN 100.0 * coalesce(nc_per_month.cnt,0) / completed_per_month.cnt
-- then coalesce(nc_per_month.cnt,0)
else 0
end as ratio
from month_start
left join nc_per_month on nc_per_month.month_year = month_start.month_start_day
left join completed_per_month on completed_per_month.month_year = month_start.month_start_day
order by date_trunc( 'MONTH', month_start_day);
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def done_versus_planned_hours_on_completed_parts_chart(
self, begin: date = None, end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
completed_parts as (
-- Figure when each part is completed
select order_part_id,
completion_date
from
(select
case when (spq.q_out >= p.quantity) and (p.completed_date is not null) THEN greatest( p.completed_date, spq.first_slip_time)
when (spq.q_out >= p.quantity) and (p.completed_date is null) THEN spq.first_slip_time
else p.completed_date -- can be null !
end as completion_date,
p.order_part_id
from
order_parts as p
left join
(select order_part_id,
sum(quantity_out) as q_out,
min(creation) as first_slip_time,
max(creation) as last_slip_time
from delivery_slip_parts sp
join delivery_slip s on s.delivery_slip_id = sp.delivery_slip_id
where s.active
group by order_part_id) as spq
on p.order_part_id = spq.order_part_id) cpl_date
where cpl_date.completion_date is not null
),
hours_done_on_part as (
select completed_parts.order_part_id,
sum(tt.duration) as hours
from completed_parts -- make sure we compute the done hours only for the completed parts
join production_files pf on pf.order_part_id = completed_parts.order_part_id
join operations op on op.production_file_id = pf.production_file_id
join tasks_operations taskop on taskop.operation_id = op.operation_id
join timetracks tt on tt.task_id = taskop.task_id
group by completed_parts.order_part_id
),
hours_planned_on_part as (
select completed_parts.order_part_id,
sum(op.planned_hours*part.quantity) as hours
from completed_parts -- make sure we compute the planned hours only for the completed parts
join order_parts part on part.order_part_id = completed_parts.order_part_id
join production_files pf on pf.order_part_id = completed_parts.order_part_id
join operations op on op.production_file_id = pf.production_file_id
group by completed_parts.order_part_id
),
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
)
select
date_trunc( 'MONTH', completion_date) as month_year,
-- sum( done_hours) as done_hours,
-- sum( plan_hours) as plan_hours,
case when sum(plan_hours) > 0 then sum(done_hours) / sum(plan_hours)
else null -- no ratio is not 0 nor 1; it's no ration hence, null.
end as ratio
from (
select
month_start.month_start_day completion_date,
h_done.order_part_id,
h_done.hours as done_hours,
h_plan.hours as plan_hours
from month_start
left join completed_parts on date_trunc( 'MONTH', month_start.month_start_day) = date_trunc( 'MONTH', completed_parts.completion_date)
left join hours_done_on_part h_done on h_done.order_part_id = completed_parts.order_part_id
left join hours_planned_on_part h_plan on h_plan.order_part_id = completed_parts.order_part_id
) as data
group by date_trunc( 'MONTH', completion_date)
order by date_trunc( 'MONTH', completion_date);
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def deadline_delivery_otd_chart(self,
begin: date = None,
end: date = None):
begin, end, duration = _standard_period2(begin, end)
r = session().connection().execute("""
with
completed_parts as (
-- Figure when each part is completed
select order_part_id, completion_date
from
(select
case when (spq.q_out >= p.quantity) and (p.completed_date is not null) THEN greatest( p.completed_date, spq.first_slip_time)
when (spq.q_out >= p.quantity) and (p.completed_date is null) THEN spq.first_slip_time
else p.completed_date -- can be null !
end as completion_date,
p.order_part_id
from
order_parts as p
left join
(select order_part_id,
sum(quantity_out) as q_out,
min(creation) as first_slip_time,
max(creation) as last_slip_time
from delivery_slip_parts sp
join delivery_slip s on s.delivery_slip_id = sp.delivery_slip_id
where s.active
group by order_part_id) as spq
on p.order_part_id = spq.order_part_id) cpl_date
where cpl_date.completion_date is not null
),
month_start as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS month_start_day
),
data as (
select
date_trunc( 'MONTH', completion_date) as completion_date,
--sum( late),
--count(*),
-- 'a' || sum( on_time) * 100.0 /1,
-- count(*),
sum( on_time) * 100.0 / count(*) as ratio
from (
select
-- for each order part we dtermine if it is on time (1) or not (0)
order_parts.order_part_id,
completed_parts.completion_date as completion_date,
case when completed_parts.completion_date < order_parts.deadline then 1 else 0 end as on_time
from order_parts as order_parts
join completed_parts on order_parts.order_part_id = completed_parts.order_part_id
) as data
group by date_trunc( 'MONTH', completion_date)
order by date_trunc( 'MONTH', completion_date))
select
date_trunc( 'MONTH', month_start.month_start_day),
ratio
from month_start
left join data on date_trunc( 'MONTH', month_start.month_start_day) = date_trunc( 'MONTH', data.completion_date)
order by month_start_day""".format(timestamp_to_pg(begin),
duration)).fetchall()
x_legends, values = to_serie(r)
return GraphData(x_legends, [''],
[values]) # data are one serie of values => [values]
def solde_carnet_commande_chart(self,
begin: datetime = None,
end: datetime = None):
"""It's the delta between
the value of the work accomplished on
and the expected sell price of
the order part that are in production on a given day.
So if an order part is completed, then it leaves the solde
computation. Conversely, if a part is not yet in production,
it is not in the computation.
L'argent que je dois ("dois" car les commandes sont fermes et a faire,
cad pretes pour la production) encore realiser.
C'est donc la difference entre le prix de vente de la partie de commande
et l'argent deja depense sur cette commande
On suppose ici qu'au final, l'argent depense sur une commande sera
toujours inferieur au prix de vente.
"""
begin, end, duration = _standard_period2(begin, end)
duration = (end - begin).days
r = session().connection().execute("""
with dates as (
select date '{}' + generate_series(0, {}) AS day
), active_parts as (
-- We find the "active" order parts on a given day
-- for those, we compute the beginning of their validity period and its end
select p.order_part_id, p.quantity, o.creation_date,
least(p.completed_date, dates.day ) as end_date, dates.day as rday
from order_parts p
join orders o on o.order_id = p.order_id
cross join dates
where o.creation_date <= dates.day
and ( (p.completed_date >= dates.day and p.state not in ('aborted','preorder'))
or (p.completed_date is null and p.state not in ('aborted','preorder')))
), money_planned_per_part as (
-- Money planned is the planned (during preorder phase) cost (what it will cost) or benefit
-- (what it will bring to the company) of an order part.
-- Money planned per order part. We can compute the planned money in
-- two ways : cost or sell price.
-- *** Planned money is COST :
-- select opart.order_part_id, sum((op.value * 1.25 + op.planned_hours * opdef.hourly_cost) * 1.15 * opart.quantity ) as money_planned_per_part
-- from order_parts opart
-- join production_files pf on pf.order_part_id = opart.order_part_id
-- join operations op on op.production_file_id = pf.production_file_id
-- join operation_definitions opdef on opdef.operation_definition_id = op.operation_definition_id
-- group by opart.order_part_id
-- *** Planned money is SELL PRICE
select opart.order_part_id, (opart.sell_price * opart.quantity ) as money_planned_per_part -- SELL PRICE
from order_parts opart
), money_planned_on_day as (
select dates.day as day,
sum(money_planned_per_part.money_planned_per_part) as money_planned
from dates
join active_parts on active_parts.rday = dates.day
join money_planned_per_part on money_planned_per_part.order_part_id = active_parts.order_part_id
group by dates.day
), timetracks_date as (
select *, cast(start_time as date) start_date
from timetracks
), money_consumed_up_to_day_for_parts_active_on_that_day as (
-- Let's show the timetracks (consumed hours on a given day for a given operation) :
-- Day 1 2 3 4 5
-- ---------------------------
-- Part A
-- Op A.1 2 . . . .
-- Op A.2 2 . 1 . .
-- Part 2
-- Op B.1 3 . 2 . .
-- Op B.2 0 . 1 . 5
-- -------------
--
-- The join-clause on dates with tt.start_date <= dates.day
-- lets us consider a growing group of column, starting
-- left and growing one column at a time to the right
-- The group by clause sums all the done hours in each of these
-- groups :
-- Day 1 2 3 4 5
-- ---------------------------
-- Part A
-- Op A.1 2 . . . .
-- Op A.2 2 . 1 . .
-- Part 2
-- Op B.1 3 . 2 . .
-- Op B.2 0 . 1 . 5
-- -------------
-- 7 7 11 11 16
-- But doing so will produce an ever increasing number of hours
-- so we also join on active_parts to only consider the parts
-- actives on a given day.
-- For example, if A is active up to day 3 and B only from day 3 :
-- Day 1 2 3 4 5
-- ---------------------------
-- Part A
-- Op A.1 2 . . . .
-- Op A.2 2 1 1 . .
-- Part 2
-- Op B.1 . . . 4 .
-- Op B.2 . . 1 1 5
-- -------------
-- 4 5 7 6 10
select dates.day as day,
sum( tt.duration*opdef.hourly_cost) as money_consumed
from timetracks_date tt
join dates on tt.start_date <= dates.day
join tasks on tasks.task_id = tt.task_id
join tasks_operations on tasks_operations.task_id = tasks.task_id
join operations on tasks_operations.operation_id = operations.operation_id
join operation_definitions opdef on opdef.operation_definition_id = operations.operation_definition_id
join production_files pf on operations.production_file_id = pf.production_file_id
-- FIXME Don't the timetracks automatically select that ?
join active_parts opart on pf.order_part_id = opart.order_part_id and opart.rday = dates.day
group by dates.day -- This is not naive at all
)
select dates.day,
cast( coalesce( mpd.money_planned,0) as int),
cast( coalesce( mcd.money_consumed,0) as int),
cast( greatest( 0, coalesce( mpd.money_planned,0) - coalesce( mcd.money_consumed,0)) as int) as solde
from dates
left join money_planned_on_day mpd on mpd.day = dates.day
left join money_consumed_up_to_day_for_parts_active_on_that_day mcd on mpd.day = mcd.day
order by dates.day
""".format(timestamp_to_pg(begin), duration)).fetchall()
x_axis = []
soldes = []
consumed = []
planned = []
for d, money_planned, money_consumed, solde in r:
x_axis.append(d)
planned.append(money_planned)
consumed.append(money_consumed)
soldes.append(solde)
# self.set_data( x_axis,[_("Remaining"),_("Consumed"),_("Sell price")],[soldes, consumed, planned])
return GraphData(
x_axis,
[_("Remaining"), _("Consumed"),
_("Sell price")], [soldes, consumed, planned])
def to_facture_per_month_chart(self, begin: date = None, end: date = None):
begin, end, duration = _standard_period2(begin, end)
mainlog.debug("to_facture_per_month_chart {} - {}".format(begin, end))
r = session().connection().execute("""
with dates as (
select date '{}' + generate_series(0, {}) * (interval '1' month) AS day
),
billable as (
select
date_trunc('month',s.creation) as m,
sum(sp.quantity_out * p.sell_price)
from order_parts as p
join delivery_slip_parts as sp on sp.order_part_id = p.order_part_id
join delivery_slip as s on s.delivery_slip_id = sp.delivery_slip_id
join orders as ord on ord.order_id = p.order_id
where s.active and s.creation between timestamp '{}' and timestamp '{}'
group by m
),
actual_cost as (
select
date_trunc('month',s.creation) as m,
sum(tt.duration * opdefper.hourly_cost)
from order_parts as p
join orders as o on o.order_id = p.order_id
join production_files pf on pf.order_part_id = p.order_part_id
join operations op on op.production_file_id = pf.production_file_id
join tasks_operations taskop on taskop.operation_id = op.operation_id
join timetracks tt on tt.task_id = taskop.task_id
join operation_definitions opdef on opdef.operation_definition_id = op.operation_definition_id
join operation_definition_periods opdefper on opdefper.operation_definition_id = opdef.operation_definition_id and o.creation_date between opdefper.start_date and coalesce(opdefper.end_date, TO_DATE('01/10/2100','DD/MM/YYYY'))
join delivery_slip_parts as sp on sp.order_part_id = p.order_part_id
join delivery_slip as s on s.delivery_slip_id = sp.delivery_slip_id
where s.active and s.creation between timestamp '{}' and timestamp '{}'
group by m
),
planned_cost as (
select
date_trunc('month',s.creation) as m,
sum( pph.part_planned_cost_per_unit * p.quantity) as planned
from order_parts as p
join orders as o on o.order_id = p.order_id
join delivery_slip_parts sp on sp.order_part_id = p.order_part_id
join delivery_slip s on s.delivery_slip_id = sp.delivery_slip_id
join
(select order_part_id,
sum(op.planned_hours*opdef.hourly_cost) as part_planned_cost_per_unit
from production_files pf
join operations op on op.production_file_id = pf.production_file_id
join operation_definitions opdef on op.operation_definition_id = opdef.operation_definition_id
group by pf.order_part_id) as pph on pph.order_part_id = p.order_part_id
where s.active and s.creation between timestamp '{}' and timestamp '{}'
group by m
)
select
dates.day,
cast( coalesce( billable.sum,0) as real),
cast( coalesce( actual_cost.sum,0) as real),
cast( coalesce( planned_cost.planned,0) as real)
from dates
left join billable on billable.m = dates.day
left join actual_cost on actual_cost.m = dates.day
left join planned_cost on planned_cost.m = dates.day
order by dates.day
""".format(timestamp_to_pg(begin), duration, timestamp_to_pg(begin),
timestamp_to_pg(end), timestamp_to_pg(begin),
timestamp_to_pg(end), timestamp_to_pg(begin),
timestamp_to_pg(end))).fetchall()
x_legends, data = to_series(r)
return GraphData(
x_legends,
[_("To bill"), _("Actual cost"),
_("Planned cost")], data)