programs_info/三资项目/资源计划导出1.1.sql

 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
       ( <include refid="oa_user_cost_pro"/> )
                                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 ( <include refid="oa_user_cost_pro"/> ) c ON b.posts = c.post_id and c.post_level=b.post_level
                            where year_month_str &lt; #{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 ( <include refid="oa_user_cost_pro"/> ) 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 ( <include refid="oa_user_cost_pro"/> ) 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