PHP kôd:
$query = "SELECT @done_date:=done_date as done_date, report.creation_date AS creation_date, report.id AS id, @hours:=hours as hours, @overtime_a:=overtime_a as overtime_a, @overtime_b:=overtime_b as overtime_b, object.name AS name, @objectId := object.id, work_type.title AS work_type, job_status.title AS job_status,
@sum_price_extra_material:=( SELECT SUM(extra_material.quantity * extra_material.price) FROM extra_material WHERE report.id = extra_material.id_report ) AS sum_price_extra_material,
@sum_price_material:=( SELECT SUM((material.quantity + ((@ttt:=material.id_article) * 0)) *
( select if (d.discount_value is null, p.price, p.price * d.discount_value / 100) as price
from `article` a, `price` p, `group` g
left join discount d on d.id_group = g.id
and d.id_object = $idObject
and d.id = (select id from discount where id_group = g.id and (date(@done_date) < end_date or end_date is null) order by id limit 1)
where
a.id = @ttt
and a.id = p.id_article
and p.id = (select id from price where id_article = a.id and (date(@done_date) < end_date or end_date is null) order by id limit 1)
and a.id_group = g.id)
) FROM material WHERE report.id = material.id_report ) AS sum_price_material,
comment.comment,
@done_date as date,
@price_per_hour1:=( SELECT workhour FROM work_price WHERE id_object = @objectId AND end_date IS NULL ),
@price_per_hour:= IF ( @price_per_hour1 IS NULL, 0, @price_per_hour1 ) AS price_per_hour,
@price_per_overtime_a1:=( SELECT overtime_a FROM work_price WHERE id_object = @objectId AND end_date IS NULL ),
@price_per_overtime_a:= IF ( @price_per_overtime_a1 IS NULL, 0, @price_per_overtime_a1 ) AS price_per_overtime_a,
@price_per_overtime_b1:=( SELECT overtime_b FROM work_price WHERE id_object = @objectId AND end_date IS NULL ),
@price_per_overtime_b:= IF ( @price_per_overtime_b1 IS NULL, 0, @price_per_overtime_b1 ) AS price_per_overtime_b,
@hours_price:=( @hours * @price_per_hour ) as hours_price,
@overtime_a_price:=( @overtime_a * @price_per_overtime_a ) as overtime_a_price,
@overtime_b_price:=( @overtime_b * @price_per_overtime_b ) as overtime_b_price,
@sum_hours_prices:=( @hours_price + @overtime_a_price + @overtime_b_price ) as sum_hours_prices,
@sum_all_prices:=( @sum_hours_prices + ( IF ( @sum_price_extra_material is null, 0, @sum_price_extra_material ) ) + ( IF ( @sum_price_material is null, 0, @sum_price_material ) ) ) as sum_all_prices,
object.temp as is_temp
FROM report
LEFT JOIN comment ON comment.id_report = report.id,
activities
LEFT JOIN object ON activities.id_customer = object.id
LEFT JOIN job_status ON activities.id_job_status = job_status.id,
work_type
WHERE report.id_user = $idUser
AND done_date >= '$dateFrom'
AND done_date <= '$dateTo'
AND report.id_activity = activities.id
-- AND activities.id_job_status = 4
AND report.id_work_type = work_type.id
ORDER BY is_temp ASC, date DESC";
kad sam vec zakuvao