select a.id,
concat(ifnull(a.proName, ''), ifnull(a.custName, '')) proNamecustName,
a.proName proName,
a.custName custName,
ot.pro_label proTypeName,
DATE_FORMAT(a.validAt, '%Y-%m-%d') validAt,
DATE_FORMAT(a.real_valid_At, '%Y-%m-%d') realValidAt,
a.signStatus signStatus,
a.proStatus proStatus,
DATE_FORMAT(b.actual_check_time, '%Y-%m-%d') actualCheckTime,
DATE_FORMAT(a.closing_item_time, '%Y-%m-%d') closingItemTime,
ctr.hours accumulatedOccurrence,
c.totalAmount ctrAmount,
acc.receiveAmount ctrReceivedPayment,
f.orderAmount purchaseAmount,
f.payAmount purchasePaid,
ctr.cost occurredLaborCost,
d.expenseAmount occurredExpense,
ROUND((IFNULL(c.totalAmount, 0) - IFNULL(acc.receiveAmount, 0) - IFNULL(ctr.cost, 0) -
IFNULL(d.expenseAmount, 0)), 2) currentProRevExp,
ROUND(((IFNULL(c.totalAmount, 0) - IFNULL(acc.receiveAmount, 0) - IFNULL(ctr.cost, 0) -
IFNULL(d.expenseAmount, 0)) + (IFNULL(c.totalAmount, 0) - IFNULL(acc.receiveAmount, 0)) -
(IFNULL(f.orderAmount, 0) - IFNULL(f.payAmount, 0)) - IFNULL(e.cost, 0)), 2) totalProGrossMargin,
ROUND((IFNULL(c.totalAmount, 0) - IFNULL(acc.receiveAmount, 0)), 2) ctrUnpaidPayments,
ROUND((IFNULL(f.orderAmount, 0) - IFNULL(f.payAmount, 0)), 2) purchaseUnpaidPayments,
e.cost planCost,
ROUND(beforeUserMoney, 2) beforeUserMoney,
ROUND(g.beforeCost, 2) beforeCost,
ROUND(h.afterUserMoney, 2) afterUserMoney,
ROUND(h.afterCost, 2) afterCost,
j.*
from pro_main a
-- ===================================================================
left join pro_main_extend b ON a.id = b.pro_id
-- ===================================================================
left join
(select b.pro_id,
sum(a.review_hour) hours,
sum(ROUND(b.post_cost * IFNULL(a.review_hour, 0) / (22 * 8), 2)) cost
from oa_pro_task_hour a
left join oa_pro_task b on a.task_id = b.id
where a.hour_state = '2'
and DATE_FORMAT(a.hour_date, '%Y-%m') <= #{yearMonth}
group by b.pro_id) ctr on a.id = ctr.pro_id
-- ===================================================================
left join
(select proId, sum(receiveAmount) receiveAmount
from acc_receivable
where planStatus = '2'
and DATE_FORMAT(receiveDate, '%Y-%m') <= #{yearMonth}
group by proId) acc on a.id = acc.proId
-- ===================================================================
left join ctr_main c ON a.id = c.proId
-- ===================================================================
left join (select a.pro_id, sum(a.order_amount) orderAmount, sum(b.pay_amount) payAmount
from out_order_main a
left join (select out_order_main_id, sum(pay_amount) pay_amount
from out_order_pay
where status = '2'
and DATE_FORMAT(pay_at, '%Y-%m') <= #{yearMonth}
group by out_order_main_id) b on a.id = b.out_order_main_id
group by a.pro_id) f ON f.pro_id = a.id
-- ===================================================================
left join oa_pro_type ot ON ot.group_id = a.group_id and ot.pro_value = a.proType
-- ===================================================================
left join (select pro_id, sum(expense_amount) expenseAmount
from oa_expense_ledger
where DATE_FORMAT(happen_date, '%Y-%m') <= #{yearMonth}
group by pro_id) d ON d.pro_id = a.id
-- ===================================================================
left join (select a.pro_id, ROUND(sum((c.post_cost / 22) * IFNULL(a.workload, 0)), 2) as cost
from resource_plan_staff_detail a
left join resource_plan_staff b ON a.plan_staff_id = b.id
left join
( )
c ON b.posts = c.post_id and c.post_level=b.post_level
where year_month_str > #{yearMonth}
group by a.pro_id) e ON e.pro_id = a.id
-- ===================================================================
left join (select a.pro_id,
sum(a.workload) beforeUserMoney,
ROUND(sum((c.post_cost / 22) * IFNULL(a.workload, 0)), 2) as beforeCost
from resource_plan_staff_detail a
left join resource_plan_staff b ON a.plan_staff_id = b.id
left join ( ) c ON b.posts = c.post_id and c.post_level=b.post_level
where year_month_str < #{year}'-01'
group by a.pro_id) g on g.pro_id = a.id
-- ===================================================================
left join (select a.pro_id,
sum(a.workload) afterUserMoney,
ROUND(sum((c.post_cost / 22) * IFNULL(a.workload, 0)), 2) as afterCost
from resource_plan_staff_detail a
left join resource_plan_staff b ON a.plan_staff_id = b.id
left join ( ) c ON b.posts = c.post_id and c.post_level=b.post_level
where year_month_str > #{year}'-12'
group by a.pro_id) h on h.pro_id = a.id
-- ===================================================================
left join (SELECT a.pro_id,
sum(a.workload) userMoney,
ROUND(sum((c.post_cost / 22) * IFNULL(a.workload, 0)),
2
) AS cost,
sum(CASE WHEN MOD(year_mon,100) = 1 THEN a.workload ELSE 0 END ) AS userMoney1,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 1 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost1,
sum(CASE WHEN MOD(year_mon,100) = 2 THEN a.workload ELSE 0 END ) AS userMoney2,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 2 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost2,
sum(CASE WHEN MOD(year_mon,100) = 3 THEN a.workload ELSE 0 END ) AS userMoney3,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 3 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost3,
sum(CASE WHEN MOD(year_mon,100) = 4 THEN a.workload ELSE 0 END ) AS userMoney4,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 4 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost4,
sum(CASE WHEN MOD(year_mon,100) = 5 THEN a.workload ELSE 0 END ) AS userMoney5,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 5 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost5,
sum(CASE WHEN MOD(year_mon,100) = 6 THEN a.workload ELSE 0 END ) AS userMoney6,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 6 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost6,
sum(CASE WHEN MOD(year_mon,100) = 7 THEN a.workload ELSE 0 END ) AS userMoney7,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 7 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost7,
sum(CASE WHEN MOD(year_mon,100) = 8 THEN a.workload ELSE 0 END ) AS userMoney8,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 8 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost8,
sum(CASE WHEN MOD(year_mon,100) = 9 THEN a.workload ELSE 0 END ) AS userMoney9,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 9 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost9,
sum(CASE WHEN MOD(year_mon,100) = 10 THEN a.workload ELSE 0 END ) AS userMoney10,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 10 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost10,
sum(CASE WHEN MOD(year_mon,100) = 11 THEN a.workload ELSE 0 END ) AS userMoney11,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 11 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost11,
sum(CASE WHEN MOD(year_mon,100) = 12 THEN a.workload ELSE 0 END ) AS userMoney12,
ROUND(sum(CASE WHEN MOD(year_mon,100) = 12 THEN ( c.post_cost / 22 )* IFNULL( a.workload, 0 ) ELSE 0 END ),2) AS cost12
FROM resource_plan_staff_detail a
LEFT JOIN resource_plan_staff b ON a.plan_staff_id = b.id
LEFT JOIN ( ) c ON b.posts = c.post_id and c.post_level=b.post_level
WHERE FLOOR(year_mon/100) = #{year}
GROUP BY a.pro_id) j on j.pro_id = a.id
where a.group_id = #{groupId} order by a.id desc