select blds.bldg_id, #fp_mach.proposed_serial_number,
@device_commencement_date := (CASE WHEN fp_mach.commencement_date IS NULL THEN 1530403200 WHEN UNIX_TIMESTAMP(fp_mach.commencement_date) < 1530403200 THEN 1530403200 ELSE UNIX_TIMESTAMP(fp_mach.commencement_date) END) device_commencement_date,

@cm_diff := 1561852800- @device_commencement_date,

@c_blk := (CASE WHEN fp_mach.commencement_black_meter IS NULL THEN mt.black_meter ELSE (mt.black_meter - fp_mach.commencement_black_meter) END) c_blk,

@last_diff_orig := (CASE WHEN mt.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mt.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,

@last_diff := (CASE WHEN @last_diff_orig < 0 THEN 1 ELSE @last_diff_orig END) last_diff,

@cm_diff_orig := ((1561852800- @device_commencement_date) /60/60/24) cm_diff_orig,

@cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 1 ELSE @cm_diff_orig END) cm_diff,

SUM((CASE WHEN @cm_diff = 0 THEN 0 WHEN 1530403200 IS NULL THEN (@c_blk / @last_diff * 345) WHEN 1530403200 < 1530403200 THEN @c_blk / @last_diff * 345 ELSE (@c_blk / @cm_diff * LEAST(364, 345)) END)) projected_black

from `y2019_floorplan_machines` as `fp_mach`
inner join `y2019_meters` as `mt` on `mt`.`serial_number` = `fp_mach`.`proposed_serial_number`
inner join `y2019_floorplans` as `fplan` on `fplan`.`fp_id` = `fp_mach`.`floorplans_id`
inner join `y2019_buildings` as `blds` on `blds`.`bldg_id` = `fplan`.`buildings_id`

inner join (select max(created_date) as dt, serial_number from `y2019_meters` where `created_date` > '2018-07-01 00:00:00' and `created_date` < '2019-06-30 00:00:00' group by `serial_number`) as `serNumDateMetersMax` on `mt`.`serial_number` = `serNumDateMetersMax`.`serial_number` and `mt`.`created_date` = `serNumDateMetersMax`.`dt`

where `blds`.`organizations_id` = 75 and `fp_mach`.`proposed_serial_number` is not null
GROUP BY blds.bldg_id;



select blds.bldg_id, fp_mach.proposed_serial_number,
@device_commencement_date := (CASE WHEN fp_mach.commencement_date IS NULL THEN 1530403200 WHEN UNIX_TIMESTAMP(fp_mach.commencement_date) < 1530403200 THEN 1530403200 ELSE UNIX_TIMESTAMP(fp_mach.commencement_date) END) device_commencement_date,

@cm_diff := 1561852800- @device_commencement_date,

@c_blk := (CASE WHEN fp_mach.commencement_black_meter IS NULL THEN mt.black_meter ELSE (mt.black_meter - fp_mach.commencement_black_meter) END) c_blk,

@last_diff_orig := (CASE WHEN mt.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mt.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,

@last_diff := (CASE WHEN @last_diff_orig < 0 THEN 1 ELSE @last_diff_orig END) last_diff,

@cm_diff_orig := ((1561852800- @device_commencement_date) /60/60/24) cm_diff_orig,

@cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 1 ELSE @cm_diff_orig END) cm_diff,

(CASE WHEN 1530403200 IS NULL THEN (@c_blk / @last_diff * 345) WHEN 1530403200 < 1530403200 THEN @c_blk / @last_diff * 345 ELSE (@c_blk / @cm_diff * LEAST(364, 345)) END) projected_black

from `y2019_floorplan_machines` as `fp_mach`
inner join `y2019_meters` as `mt` on `mt`.`serial_number` = `fp_mach`.`proposed_serial_number`
inner join `y2019_floorplans` as `fplan` on `fplan`.`fp_id` = `fp_mach`.`floorplans_id`
inner join `y2019_buildings` as `blds` on `blds`.`bldg_id` = `fplan`.`buildings_id`

inner join (select max(created_date) as dt, serial_number
			from `y2019_meters` where `created_date` > '2018-07-01 00:00:00' and `created_date` < '2019-06-30 00:00:00'
			group by `serial_number`) as `serNumDateMetersMax` on `mt`.`serial_number` = `serNumDateMetersMax`.`serial_number` and `mt`.`created_date` = `serNumDateMetersMax`.`dt`

where `blds`.`organizations_id` = 75 and `fp_mach`.`proposed_serial_number` is not null;














select blds.bldg_id, fp_mach.proposed_serial_number,
@device_commencement_date := (CASE WHEN fp_mach.commencement_date IS NULL THEN 1530403200 WHEN UNIX_TIMESTAMP(fp_mach.commencement_date) < 1530403200 THEN 1530403200 ELSE UNIX_TIMESTAMP(fp_mach.commencement_date) END) device_commencement_date,

@cm_diff := 1561852800- @device_commencement_date,

@c_blk := (CASE WHEN fp_mach.commencement_black_meter IS NULL THEN mt.black_meter ELSE (mt.black_meter - fp_mach.commencement_black_meter) END) c_blk,

@last_diff_orig := (CASE WHEN mt.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mt.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,

@last_diff := (CASE WHEN @last_diff_orig < 0 THEN 1 ELSE @last_diff_orig END) last_diff,

@cm_diff_orig := ((1561852800- @device_commencement_date) /60/60/24) cm_diff_orig,

@cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 1 ELSE @cm_diff_orig END) cm_diff,

(CASE WHEN 1530403200 IS NULL THEN (@c_blk / @last_diff * 345) WHEN 1530403200 < 1530403200 THEN @c_blk / @last_diff * 345 ELSE (@c_blk / @cm_diff * LEAST(364, 345)) END) projected_black

from `y2019_floorplan_machines` as `fp_mach`
inner join `y2019_meters` as `mt` on `mt`.`serial_number` = `fp_mach`.`proposed_serial_number`
inner join `y2019_floorplans` as `fplan` on `fplan`.`fp_id` = `fp_mach`.`floorplans_id`
inner join `y2019_buildings` as `blds` on `blds`.`bldg_id` = `fplan`.`buildings_id`
where `blds`.`organizations_id` = 75 and `fp_mach`.`proposed_serial_number` is not null;



/*->select(DB::raw('SUM(fpm.budgeted_black) as budget_black,
                                        SUM(fpm.budgeted_color) as budget_color,
                                        SUM(mtr.black_meter - fpm.commencement_black_meter) as cons_blk,
                                        SUM(mtr.color_meter - fpm.commencement_color_meter) as cons_col,
                                        SUM(mtr.black_meter * fpm.cpc_black) as cons_blk_cost,
                                        SUM(mtr.color_meter * fpm.cpc_color) as cons_col_cost,
                                        
                                        SUM((CASE WHEN fpm.commencement_black_meter IS NULL THEN mtr.black_meter ELSE (mtr.black_meter - fpm.commencement_black_meter) END)) consumed_black,
                                        SUM((CASE WHEN fpm.commencement_color_meter IS NULL THEN mtr.color_meter ELSE (mtr.color_meter - fpm.commencement_color_meter) END)) consumed_color,
                                        
                                        
                                        SUM((CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_black
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_black
                                        	ELSE (fpm.budgeted_black / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' END)) budgeted_black,
                                        	
                                        SUM((CASE WHEN org.commencement_date IS NULL THEN fpm.budgeted_color
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN fpm.budgeted_color
                                        	ELSE (fpm.budgeted_color / ' . $fiscal_days . ') * ' . $org_fiscal_days . ' END)) budgeted_color,
                                        	
                                        bldgs.bldg_id, bldgs.bldg_name'))*/
										
										
										
										 /*Get the serial_number and the device_commencement_date for the machine*/
        $projected_amounts = DB::table($yearTab . '_floorplan_machines as fp_mach')
                            ->join($yearTab . '_meters as mt', 'mt.serial_number', '=', 'fp_mach.proposed_serial_number')
                            ->join($yearTab . '_floorplans as fplan', 'fplan.fp_id', '=', 'fp_mach.floorplans_id')
                            ->join($yearTab . '_buildings as blds', 'blds.bldg_id', '=', 'fplan.buildings_id')
                            
                            ->joinSub($serNumDateMetersMax, 'serNumDateMetersMax', function($join){
            												$join->on('mtr.serial_number', '=', 'serNumDateMetersMax.serial_number');
                    										$join->on('mtr.created_date', '=', 'serNumDateMetersMax.dt');
                            })
                            
                            ->select(DB::raw('blds.bldg_id, fp_mach.proposed_serial_number,
                            @device_commencement_date := (CASE WHEN fp_mach.commencement_date IS NULL THEN ' . $org_commencement_date . 
                            ' WHEN UNIX_TIMESTAMP(fp_mach.commencement_date) < ' . $org_commencement_date . ' THEN ' . $org_commencement_date . 
                            ' ELSE UNIX_TIMESTAMP(fp_mach.commencement_date) END) device_commencement_date, @cm_diff := ' . $endingDate . '-  @device_commencement_date,
                            
                            @c_blk := (CASE WHEN fp_mach.commencement_black_meter IS NULL THEN mt.black_meter ELSE (mt.black_meter - fp_mach.commencement_black_meter) END) c_blk,
                            @last_diff_orig := (CASE WHEN mt.created_date IS NULL THEN 1 ELSE (UNIX_TIMESTAMP(mt.created_date) - @device_commencement_date) / 60/60/24 END) last_diff_orig,
                            @last_diff := (CASE WHEN @last_diff_orig < 0 THEN 1 ELSE @last_diff_orig END) last_diff,
                            @cm_diff_orig := ((' . $endingDate . '- @device_commencement_date) /60/60/24) cm_diff_orig,
                            @cm_diff := (CASE WHEN @cm_diff_orig < 0 THEN 0 ELSE @cm_diff_orig END) cm_diff,
                            
                            (CASE WHEN @cm_diff = 0 THEN 0 WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_blk / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_blk / @last_diff * 345)
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_black'
                            ))
                            
                            /*(CASE WHEN ' . $org_commencement_date . ' IS NULL THEN (@c_blk / @last_diff * 345)
                                        	WHEN ' . $org_commencement_date . ' < ' . $startingDate . ' THEN (@c_blk / @last_diff * 345)
                                        	ELSE (@c_blk / @cm_diff * LEAST(' . $org_fiscal_days .', 345)) END) projected_black'
                            ))*/
                            ->where('blds.organizations_id', '=', $org_id)
                            ->whereNotNull('fp_mach.proposed_serial_number')->toSql(); //->get();
        
        /*(CASE WHEN @cm_diff > 0 THEN(CASE WHEN ' . $cm_diff_test . '= "Y" ' . $startingDate THEN "y" ELSE "n" END)*/
        
  /*mtr.black_meter - fpm.commencement_black_meter as cons_blk,
                         mtr.color_meter - fpm.commencement_color_meter as cons_col,
                         @cm_diff := ' . $endingDate . '-  @device_commencement_date,
                         fpm.budgeted_black as budget_black,
                         fpm.budgeted_color as budget_color,*/