Skip to content

Instantly share code, notes, and snippets.

@tuckbloor

tuckbloor/gist:8b0935d6b8aa917a41f6daed3ddd6376

Created May 3, 2018 16:41
Show Gist options
  • Save tuckbloor/8b0935d6b8aa917a41f6daed3ddd6376 to your computer and use it in GitHub Desktop.
Save tuckbloor/8b0935d6b8aa917a41f6daed3ddd6376 to your computer and use it in GitHub Desktop.
Download ZIP
sql queries
Raw
gistfile1.txt
public function claimLinePrice()
{
$quote_number = \Session::get('project_quote_number');
return \DB::select("SELECT quote_builder_line_price_id, quote_builder_line_price_description, quote_builder_line_price_description2, quote_builder_line_price_description3,
quote_builder_line_price_description4, quote_builder_line_price_description5, quote_builder_line_price_description6,
quote_builder_line_price_description7,
quote_builder_line_price_description8, quote_builder_line_price_description9, quote_builder_line_price_description10,
quote_builder_line_price,quote_builder_line_price2,quote_builder_line_price3,quote_builder_line_price4,quote_builder_line_price5,quote_builder_line_price6,
quote_builder_line_price7,quote_builder_line_price8,quote_builder_line_price9,quote_builder_line_price10,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price1') as line_price1_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price2') as line_price2_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price3') as line_price3_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price4') as line_price4_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price5') as line_price5_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price6') as line_price6_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price7') as line_price7_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price8') as line_price8_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price9') as line_price9_percentage_claimed,
(SELECT SUM(percentage) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price10') as line_price10_percentage_claimed
FROM quote_builder_line_price WHERE quote_number = '$quote_number'");
}
$return['data'] = \DB::select("SELECT str_to_date(concat(yearweek(ic.created_at), ' friday'), '%X%V %W') as end_of_week_date,
(SELECT quote_builder_line_price FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price,
(SELECT quote_builder_line_price2 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price2,
(SELECT quote_builder_line_price3 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price3,
(SELECT quote_builder_line_price4 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price4,
(SELECT quote_builder_line_price5 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price5,
(SELECT quote_builder_line_price6 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price6,
(SELECT quote_builder_line_price7 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price7,
(SELECT quote_builder_line_price8 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price8,
(SELECT quote_builder_line_price9 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price9,
(SELECT quote_builder_line_price10 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price10,
(SELECT quote_builder_line_price_description FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description2 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description3 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description4 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description5 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description6 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description7 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description8 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description9 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
(SELECT quote_builder_line_price_description10 FROM quote_builder_line_price WHERE quote_number = '$quote_number'),
qbr.include_membrane,qbr.include_cills,qbr.include_steel,qbr.include_epdm_at_cill,qbr.include_soffits,qbr.include_jamb_packers,qbr.include_ah_assembly,week_payment_due,week_completed,
qbr.include_head_trims,qbr.include_jamb_trims,qbr.include_internal_silicone,qbr.include_perforated_panel,qbr.include_spandrel_panel,
qbr.include_spare1,qbr.include_spare2,qbr.include_spare3,qbr.include_spare4,qbr.include_spare5,qbr.include_item,
quote_protection_sides,
SUM(IF(type = 'installation', ((qbr.quote_builder_installation_price / qbr.quote_builder_qty / 100) * percentage_complete) , 0 )) as installation_cost,
SUM(IF(type = 'membrane', (qbr.quote_builder_membrane_price / qbr.quote_builder_qty) , 0 )) as membrane_cost,
SUM(IF(type = 'protection', (qbr.quote_builder_protection_price / qbr.quote_builder_qty * quote_protection_sides) , 0 )) as protection_cost,
SUM(IF(type = 'cill', (qbr.quote_builder_cills_price / qbr.quote_builder_qty) , 0 )) as cill_cost,
SUM(IF(type = 'steel', (qbr.quote_builder_steel_price / qbr.quote_builder_qty) , 0 )) as steel_cost,
SUM(IF(type = 'EPDM', (qbr.quote_builder_epdm_at_cill_price / qbr.quote_builder_qty) , 0 )) as epdm_cost,
SUM(IF(type = 'head soffits', (qbr.quote_builder_head_soffits_price / qbr.quote_builder_qty) , 0 )) as head_soffits_cost,
SUM(IF(type = 'jamb packers', (qbr.quote_builder_jamb_packers_price / qbr.quote_builder_qty) , 0 )) as jamb_packers_cost,
SUM(IF(type = 'AH Assembly', (qbr.quote_builder_acoustic_head_assembly_price / qbr.quote_builder_qty) , 0 )) as ah_assembly_cost,
SUM(IF(type = 'head trims', (qbr.quote_builder_head_trims_price / qbr.quote_builder_qty) , 0 )) as head_trims_cost,
SUM(IF(type = 'jamb trims', (qbr.quote_builder_jamb_trims_price / qbr.quote_builder_qty) , 0 )) as jamb_trims_cost,
SUM(IF(type = 'internal silicone', (qbr.quote_builder_silicone_price / qbr.quote_builder_qty) , 0 )) as internal_silicone_cost,
SUM(IF(type = 'perforated panel', (qbr.quote_builder_perforated_panel_price / qbr.quote_builder_qty) , 0 )) as perforated_panel_cost,
SUM(IF(type = 'spandrel panel', (qbr.quote_builder_spandrel_panel_price / qbr.quote_builder_qty) , 0 )) as spandrel_panel_cost,
SUM(IF(type = 'spare1', (qbr.quote_builder_spare1_price / qbr.quote_builder_qty) , 0 )) as spare1_cost,
SUM(IF(type = 'spare2', (qbr.quote_builder_spare2_price / qbr.quote_builder_qty) , 0 )) as spare2_cost,
SUM(IF(type = 'spare3', (qbr.quote_builder_spare3_price / qbr.quote_builder_qty) , 0 )) as spare3_cost,
SUM(IF(type = 'spare4', (qbr.quote_builder_spare4_price / qbr.quote_builder_qty) , 0 )) as spare4_cost,
SUM(IF(type = 'spare5', (qbr.quote_builder_spare5_price / qbr.quote_builder_qty) , 0 )) as spare5_cost,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type ='line_price1' AND ic.week_completed = week_completed) as line_price_total ,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price2' AND ic.week_completed = week_completed) as line_price_total2,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price3' AND ic.week_completed = week_completed) as line_price_total3,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price4' AND ic.week_completed = week_completed) as line_price_total4,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price5' AND ic.week_completed = week_completed) as line_price_total5,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price6' AND ic.week_completed = week_completed) as line_price_total6,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price7' AND ic.week_completed = week_completed) as line_price_total7,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price8' AND ic.week_completed = week_completed) as line_price_total8,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price9' AND ic.week_completed = week_completed) as line_price_total9,
(SELECT SUM(price) FROM install_completed_line WHERE quote_number = '$quote_number' AND line_type = 'line_price10' AND ic.week_completed = week_completed) as line_price_total10,
spare1_name, spare2_name, spare3_name, spare4_name, spare5_name,
(SELECT quote_builder_line_price FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price,
(SELECT quote_builder_line_price2 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price2,
(SELECT quote_builder_line_price3 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price3,
(SELECT quote_builder_line_price4 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price4,
(SELECT quote_builder_line_price5 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price5,
(SELECT quote_builder_line_price6 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price6,
(SELECT quote_builder_line_price7 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price7,
(SELECT quote_builder_line_price8 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price8,
(SELECT quote_builder_line_price9 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price9,
(SELECT quote_builder_line_price10 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price10,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price1') as line_price_final_total ,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price2') as line_price_final_total2,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price3') as line_price_final_total3,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price4') as line_price_final_total4,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price5') as line_price_final_total5,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price6') as line_price_final_total6,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price7') as line_price_final_total7,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price8') as line_price_final_total8,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price9') as line_price_final_total9,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price10') as line_price_final_total10
FROM install_completed ic
INNER JOIN quote_builder_installer_rows qbir ON qbir.quote_builder_installer_rows_id = ic.row_id
INNER JOIN quote_builder_rows qbr ON ic.row_id = qbir.quote_builder_installer_rows_id
LEFT JOIN quote_builder_spare qbs ON qbs.quote_number = ic.quote_number
INNER JOIN quote_builder_price qbp ON qbp.quote_number=ic.quote_number
WHERE ic.quote_number = '$quote_number'
AND qbr.quote_builder_rows_id = qbir.quote_builder_rows_id
GROUP BY end_of_week_date
ORDER BY end_of_week_date, ic.week_payment_due");
//divide on quote_builder_qty because installer rows is taken from quote builder rows qty
$return['application_totals'] = \DB::select("SELECT
SUM(IF(type = 'installation', (qbr.quote_builder_installation_price / qbr.quote_builder_qty) , 0 )) as installation_cost,
SUM(IF(type = 'membrane', (qbr.quote_builder_membrane_price / qbr.quote_builder_qty) , 0 )) as membrane_cost,
SUM(IF(type = 'protection', (qbr.quote_builder_protection_price / qbr.quote_builder_qty * quote_protection_sides) , 0 )) as protection_cost,
SUM(IF(type = 'cill', (qbr.quote_builder_cills_price / qbr.quote_builder_qty) , 0 )) as cill_cost,
SUM(IF(type = 'steel', (qbr.quote_builder_steel_price / qbr.quote_builder_qty) , 0 )) as steel_cost,
SUM(IF(type = 'EPDM', (qbr.quote_builder_epdm_at_cill_price / qbr.quote_builder_qty) , 0 )) as epdm_cost,
SUM(IF(type = 'head soffits', (qbr.quote_builder_head_soffits_price / qbr.quote_builder_qty) , 0 )) as head_soffits_cost,
SUM(IF(type = 'jamb packers', (qbr.quote_builder_jamb_packers_price / qbr.quote_builder_qty) , 0 )) as jamb_packers_cost,
SUM(IF(type = 'AH Assembly', (qbr.quote_builder_acoustic_head_assembly_price / qbr.quote_builder_qty) , 0 )) as ah_assembly_cost,
SUM(IF(type = 'head trims', (qbr.quote_builder_head_trims_price / qbr.quote_builder_qty) , 0 )) as head_trims_cost,
SUM(IF(type = 'jamb trims', (qbr.quote_builder_jamb_trims_price / qbr.quote_builder_qty) , 0 )) as jamb_trims_cost,
SUM(IF(type = 'internal silicone', (qbr.quote_builder_silicone_price / qbr.quote_builder_qty) , 0 )) as internal_silicone_cost,
SUM(IF(type = 'perforated panel', (qbr.quote_builder_perforated_panel_price / qbr.quote_builder_qty) , 0 )) as perforated_panel_cost,
SUM(IF(type = 'spandrel panel', (qbr.quote_builder_spandrel_panel_price / qbr.quote_builder_qty) , 0 )) as spandrel_panel_cost,
SUM(IF(type = 'spare1', (qbr.quote_builder_spare1_price / qbr.quote_builder_qty) , 0 )) as spare1_cost,
SUM(IF(type = 'spare2', (qbr.quote_builder_spare2_price / qbr.quote_builder_qty) , 0 )) as spare2_cost,
SUM(IF(type = 'spare3', (qbr.quote_builder_spare3_price / qbr.quote_builder_qty) , 0 )) as spare3_cost,
SUM(IF(type = 'spare4', (qbr.quote_builder_spare4_price / qbr.quote_builder_qty) , 0 )) as spare4_cost,
SUM(IF(type = 'spare5', (qbr.quote_builder_spare5_price / qbr.quote_builder_qty) , 0 )) as spare5_cost,
(SELECT quote_builder_line_price FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price,
(SELECT quote_builder_line_price2 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price2,
(SELECT quote_builder_line_price3 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price3,
(SELECT quote_builder_line_price4 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price4,
(SELECT quote_builder_line_price5 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price5,
(SELECT quote_builder_line_price6 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price6,
(SELECT quote_builder_line_price7 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price7,
(SELECT quote_builder_line_price8 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price8,
(SELECT quote_builder_line_price9 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price9,
(SELECT quote_builder_line_price10 FROM quote_builder_line_price WHERE quote_number = '$quote_number') as quote_builder_line_price10,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price1') as line_price_final_total ,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price2') as line_price_final_total2,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price3') as line_price_final_total3,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price4') as line_price_final_total4,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price5') as line_price_final_total5,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price6') as line_price_final_total6,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price7') as line_price_final_total7,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price8') as line_price_final_total8,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price9') as line_price_final_total9,
(SELECT (SUM(price)) FROM install_completed_line inst WHERE quote_number = '$quote_number' AND line_type='line_price10') as line_price_final_total10
FROM install_completed ic
INNER JOIN quote_builder_installer_rows qbir ON qbir.quote_builder_installer_rows_id = ic.row_id
INNER JOIN quote_builder_rows qbr ON ic.row_id = qbir.quote_builder_installer_rows_id
INNER JOIN quote_builder_price qbp ON qbp.quote_number=ic.quote_number
WHERE ic.quote_number = '$quote_number'
AND qbr.quote_builder_rows_id = qbir.quote_builder_rows_id
GROUP BY ic.quote_number
ORDER BY ic.week_payment_due");
$return['application_final_totals'] = \DB::select("SELECT * FROM quote_builder_final_total WHERE quote_number = '$quote_number'");
$return['application_line_includes'] = \DB::select("SELECT *
FROM (SELECT SUM(final_line_price) as line_price_total FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total,
(SELECT SUM(final_line_price2) as line_price_total2 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total2 ,
(SELECT SUM(final_line_price3) as line_price_total3 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total3,
(SELECT SUM(final_line_price4) as line_price_total4 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total4,
(SELECT SUM(final_line_price5) as line_price_total5 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total5,
(SELECT SUM(final_line_price6) as line_price_total6 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total6,
(SELECT SUM(final_line_price7) as line_price_total7 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total7,
(SELECT SUM(final_line_price8) as line_price_total8 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total8,
(SELECT SUM(final_line_price9) as line_price_total9 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total9,
(SELECT SUM(final_line_price10) as line_price_total10 FROM quote_builder_final_total WHERE quote_number = '$quote_number') as line_price_total10
");
$return['line_description'] = \DB::select("SELECT quote_builder_line_price_description,
quote_builder_line_price_description2, quote_builder_line_price_description3,quote_builder_line_price_description4,
quote_builder_line_price_description5,quote_builder_line_price_description6,quote_builder_line_price_description7,
quote_builder_line_price_description8,quote_builder_line_price_description9,quote_builder_line_price_description10
FROM quote_builder_line_price
WHERE quote_number ='$quote_number' ");
$return['line_price'] = \DB::select("SELECT quote_builder_line_price,
quote_builder_line_price2, quote_builder_line_price3,quote_builder_line_price4,
quote_builder_line_price5,quote_builder_line_price6,quote_builder_line_price7,
quote_builder_line_price8,quote_builder_line_price9,quote_builder_line_price10
FROM quote_builder_line_price
WHERE quote_number ='$quote_number' ");
$return['job_tracker_extra_line'] = \DB::select("SELECT jtel.job_tracker_extra_line_id, jtel.date, jtel.project_id,
project_tracker_invoice_id, total, extra_works_total, comment, total
FROM job_tracker_extra_line jtel
LEFT JOIN project_tracker_invoice pti ON pti.project_id = jtel.project_id
AND pti.invoice_date = jtel.date
WHERE jtel.project_id = '$quote_id'");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment