public function postStore(Request $request, $type = null) { // dd($request->all()); $sel_ar = $request->ar; $sel_st = $request->st; $frm = $request->fr; $to = $request->to; $sel_av = $request->availability; $sel_tag = $request->tags; $availability = ['1' => 'oos', '2' => 'osa']; $tags = ['1' => 'OSA', '2' => 'NPI']; $report_type = 1; if (is_null($type) || $type != 'assortment') { $report_type = 2; } if ($report_type == 2) { $areas = StoreInventories::getAreaList(); } else { $areas = AssortmentInventories::getAreaList(); } if (!empty($sel_ar)) { $data['areas'] = $sel_ar; } if (!empty($sel_st)) { $data['stores'] = $sel_st; } if (!empty($frm)) { $data['from'] = $frm; } if (!empty($to)) { $data['to'] = $to; } if (!empty($sel_av)) { $data['availability'] = $sel_av; } if (!empty($sel_tag)) { $data['tags'] = $sel_tag; } if ($report_type == 2) { $header = 'MKL SO Per Store Report'; $inventories = ItemInventories::getSoPerStores($data); } else { $header = 'Assortment SO Per Store Report'; $inventories = AssortmentItemInventories::getSoPerStores($data); } if ($request->has('submit')) { return view('so.store', compact('inventories', 'frm', 'to', 'areas', 'sel_ar', 'sel_st', 'header', 'type', 'availability', 'sel_av', 'sel_tag', 'tags')); } if ($request->has('download')) { \Excel::create($header, function ($excel) use($inventories) { $weeks = []; $items = []; foreach ($inventories as $value) { $week_start = new \DateTime(); $week_start->setISODate($value->yr, $value->yr_week); // $weeks[$week_start->getTimestamp()] = "Week ".$value->yr_week." of ".$value->yr; $weeks[$week_start->format('Y-m-d')] = "Week " . $value->yr_week . " of " . $value->yr; $items[$value->area][$value->store_name]["Week " . $value->yr_week . " of " . $value->yr] = ['fso' => $value->fso_sum, 'fso_val' => $value->fso_val_sum]; } // dd($items); ksort($weeks); $excel->sheet('Sheet1', function ($sheet) use($items, $weeks) { $col_array = []; $col = 2; foreach ($weeks as $week) { $sheet->setCellValueByColumnAndRow($col, 2, $week); $n_col = $col + 1; $col_array[$week] = $col; $sheet->mergeCells(\PHPExcel_Cell::stringFromColumnIndex($col) . "2:" . \PHPExcel_Cell::stringFromColumnIndex($n_col) . "2"); $col = $col + 2; } $fso_sub_total_col = $col; $fso_val_sub_total_col = $col + 1; $sheet->setCellValueByColumnAndRow($fso_sub_total_col, 2, 'Total Sum of FSO'); $sheet->setCellValueByColumnAndRow($fso_val_sub_total_col, 2, 'Total Sum of FSO VAL'); $area_col = 0; $store_col = 1; $sheet->setCellValueByColumnAndRow($area_col, 3, 'AREA'); $sheet->setCellValueByColumnAndRow($store_col, 3, 'STORE NAME'); foreach ($weeks as $week) { $sheet->setCellValueByColumnAndRow($store_col + 1, 3, 'Sum of FSO'); $sheet->setCellValueByColumnAndRow($store_col + 2, 3, 'Sum of FSO VAL'); $store_col = $store_col + 2; } $row = 4; $col_g_total = []; $g_fso = []; $g_fso_val = []; foreach ($items as $key => $value) { $first = true; $total_row = count($value) + $row; $last_row = $total_row - 1; $start_row = $row; foreach ($value as $skey => $record) { if ($first) { $sheet->setCellValueByColumnAndRow(0, $row, $key); $first = false; } $sheet->setCellValueByColumnAndRow(1, $row, $skey); $fso_ar = []; $fsoval_ar = []; foreach ($record as $k => $rowValue) { $fso_col = $col_array[$k]; $fso_val_col = $col_array[$k] + 1; $fso_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($fso_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($fso_col) . $last_row . ")"; $fso_val_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($fso_col + 1) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($fso_col + 1) . $last_row . ")"; $fso_g_total[] = \PHPExcel_Cell::stringFromColumnIndex($fso_col) . $total_row; $fsoval_g_total[] = \PHPExcel_Cell::stringFromColumnIndex($fso_val_col) . $total_row; $sheet->setCellValueByColumnAndRow($fso_col, $row, $rowValue['fso']); $sheet->setCellValueByColumnAndRow($fso_col, $total_row, $fso_total); $sheet->setCellValueByColumnAndRow($fso_val_col, $row, $rowValue['fso_val']); $sheet->setCellValueByColumnAndRow($fso_val_col, $total_row, $fso_val_total); $col_gfso_total[$k][$key] = \PHPExcel_Cell::stringFromColumnIndex($fso_col) . $total_row; $col_gfsoval_total[$k][$key] = \PHPExcel_Cell::stringFromColumnIndex($fso_val_col) . $total_row; $fso_ar[] = \PHPExcel_Cell::stringFromColumnIndex($fso_col) . $row; $fsoval_ar[] = \PHPExcel_Cell::stringFromColumnIndex($fso_val_col) . $row; } $sheet->setCellValueByColumnAndRow($fso_sub_total_col, $row, '=sum(' . implode(",", $fso_ar) . ')'); $sheet->setCellValueByColumnAndRow($fso_val_sub_total_col, $row, '=sum(' . implode(",", $fsoval_ar) . ')'); $row++; } $sheet->setCellValueByColumnAndRow(0, $row, $key . ' Total'); $fso_stotal = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($fso_sub_total_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($fso_sub_total_col) . $last_row . ")"; $fso_val_stotal = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($fso_val_sub_total_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($fso_val_sub_total_col) . $last_row . ")"; $sheet->setCellValueByColumnAndRow($fso_sub_total_col, $row, $fso_stotal); $sheet->setCellValueByColumnAndRow($fso_val_sub_total_col, $row, $fso_val_stotal); $g_fso[] = \PHPExcel_Cell::stringFromColumnIndex($fso_sub_total_col) . $row; $g_fso_val[] = \PHPExcel_Cell::stringFromColumnIndex($fso_val_sub_total_col) . $row; $row++; } $col = 2; $sheet->setCellValueByColumnAndRow(0, $row, 'Grand Total'); foreach ($weeks as $week) { $fso_data = []; $fsoval_data = []; $fso_colums = $col_gfso_total[$week]; foreach ($fso_colums as $cell) { $fso_data[] = $cell; } $fsoval_colums = $col_gfsoval_total[$week]; foreach ($fsoval_colums as $cell) { $fsoval_data[] = $cell; } $sheet->setCellValueByColumnAndRow($col, $row, '=sum(' . implode(",", $fso_data) . ')'); $sheet->setCellValueByColumnAndRow($col + 1, $row, '=sum(' . implode(",", $fsoval_data) . ')'); $col = $col + 2; } //grand total $sheet->setCellValueByColumnAndRow($fso_sub_total_col, $row, '=sum(' . implode(",", $g_fso) . ')'); $sheet->setCellValueByColumnAndRow($fso_val_sub_total_col, $row, '=sum(' . implode(",", $g_fso_val) . ')'); }); })->download('xlsx'); } }
public function postStore(Request $request, $type = null) { $sel_ar = $request->ar; $sel_st = $request->st; $sel_tag = $request->tags; $frm = $request->fr; $to = $request->to; $sel_av = $request->availability; $availability = ['1' => 'oos', '2' => 'osa']; $tags = ['1' => 'OSA', '2' => 'NPI']; $report_type = 1; if (is_null($type) || $type != 'assortment') { $report_type = 2; } if ($report_type == 2) { $areas = StoreInventories::getAreaList(); } else { $areas = AssortmentInventories::getAreaList(); } if (!empty($sel_ar)) { $data['areas'] = $sel_ar; } if (!empty($sel_st)) { $data['stores'] = $sel_st; } if (!empty($frm)) { $data['from'] = $frm; } if (!empty($to)) { $data['to'] = $to; } if (!empty($sel_av)) { $data['availability'] = $sel_av; } if (!empty($sel_tag)) { $data['tags'] = $sel_tag; } if ($report_type == 2) { $header = 'MKL OSA Per Store Report'; $inventories = ItemInventories::getOsaPerStore($data); } else { $header = 'Assortment OSA Per Store Report'; $inventories = AssortmentItemInventories::getOsaPerStore($data); } if ($request->has('submit')) { return view('osa.store', compact('inventories', 'frm', 'to', 'areas', 'sel_ar', 'sel_st', 'header', 'type', 'availability', 'sel_av', 'tags', 'sel_tag')); } // dd($inventories); if ($request->has('download')) { \Excel::create($header, function ($excel) use($inventories) { $weeks = []; $items = []; foreach ($inventories as $value) { $week_start = new \DateTime(); $week_start->setISODate($value->yr, $value->yr_week); $store_list[$value->area][$value->store_name] = $value; $weeks[$week_start->format('Y-m-d')] = "Week " . $value->yr_week . " of " . $value->yr; $items[$value->area][$value->store_name]["Week " . $value->yr_week . " of " . $value->yr] = ['passed' => $value->passed, 'failed' => $value->failed]; } // dd($store_list); ksort($weeks); $excel->sheet('Sheet1', function ($sheet) use($items, $weeks, $store_list) { $default_store_col = 9; $col_array = []; $col = 10; foreach ($weeks as $week) { $sheet->setCellValueByColumnAndRow($col, 2, $week); $n_col = $col + 3; $col_array[$week] = $col; $sheet->mergeCells(\PHPExcel_Cell::stringFromColumnIndex($col) . "2:" . \PHPExcel_Cell::stringFromColumnIndex($n_col) . "2"); $sheet->getStyle(\PHPExcel_Cell::stringFromColumnIndex($col) . '2')->getAlignment()->applyFromArray(array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $col = $col + 4; } $sheet->setCellValueByColumnAndRow($col, 2, 'Grand Total'); $sheet->mergeCells(\PHPExcel_Cell::stringFromColumnIndex($col) . "2:" . \PHPExcel_Cell::stringFromColumnIndex($col + 3) . "2"); $sheet->getStyle(\PHPExcel_Cell::stringFromColumnIndex($col) . '2')->getAlignment()->applyFromArray(array('horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER)); $area_col = 0; $store_col = $default_store_col; $sheet->setCellValueByColumnAndRow($area_col, 3, 'AREA'); $sheet->setCellValueByColumnAndRow(1, 3, 'REGION NAME'); $sheet->setCellValueByColumnAndRow(2, 3, 'DISTRIBUTOR CODE'); $sheet->setCellValueByColumnAndRow(3, 3, 'DISTRIBUTOR NAME'); $sheet->setCellValueByColumnAndRow(4, 3, 'AGENCY'); $sheet->setCellValueByColumnAndRow(5, 3, 'STORE CODE'); $sheet->setCellValueByColumnAndRow(6, 3, 'STORE ID'); $sheet->setCellValueByColumnAndRow(7, 3, 'CHANNEL CODE'); $sheet->setCellValueByColumnAndRow(8, 3, 'CHANNEL NAME'); $sheet->setCellValueByColumnAndRow($store_col, 3, 'STORE NAME'); foreach ($weeks as $week) { $sheet->setCellValueByColumnAndRow($store_col + 1, 3, 'OOS'); $sheet->setCellValueByColumnAndRow($store_col + 2, 3, 'With Stocks'); $sheet->setCellValueByColumnAndRow($store_col + 3, 3, 'Total'); $sheet->setCellValueByColumnAndRow($store_col + 4, 3, 'OSA Score'); $sheet->getStyle(\PHPExcel_Cell::stringFromColumnIndex($store_col + 4))->getNumberFormat()->applyFromArray(array('code' => \PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00)); $store_col = $store_col + 4; } $sheet->setCellValueByColumnAndRow($store_col + 1, 3, 'Total OOS'); $sheet->setCellValueByColumnAndRow($store_col + 2, 3, 'Total With Stocks'); $sheet->setCellValueByColumnAndRow($store_col + 3, 3, 'Grand Total'); $sheet->setCellValueByColumnAndRow($store_col + 4, 3, 'Total OSA Score'); $sheet->getStyle(\PHPExcel_Cell::stringFromColumnIndex($store_col + 4))->getNumberFormat()->applyFromArray(array('code' => \PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00)); $row = 4; // dd($items); $per_area_total_rows = []; foreach ($items as $key => $value) { $first = true; $total_row = count($value) + $row; $last_row = $total_row - 1; $start_row = $row; foreach ($value as $skey => $record) { $oos_row_total = 0; $withstock_row_total = 0; $sheet->setCellValueByColumnAndRow(0, $row, $key); $sheet->setCellValueByColumnAndRow(1, $row, $store_list[$key][$skey]->region_name); $sheet->setCellValueByColumnAndRow(2, $row, $store_list[$key][$skey]->distributor_code); $sheet->setCellValueByColumnAndRow(3, $row, $store_list[$key][$skey]->distributor); $sheet->setCellValueByColumnAndRow(4, $row, $store_list[$key][$skey]->agency); $sheet->setCellValueByColumnAndRow(5, $row, $store_list[$key][$skey]->store_code); $sheet->setCellValueByColumnAndRow(6, $row, $store_list[$key][$skey]->store_id); $sheet->setCellValueByColumnAndRow(7, $row, $store_list[$key][$skey]->channel_code); $sheet->setCellValueByColumnAndRow(8, $row, $store_list[$key][$skey]->channel_name); $sheet->setCellValueByColumnAndRow(9, $row, $skey); $grand_total = 0; foreach ($record as $k => $rowValue) { $oos_col = $col_array[$k]; $with_stock_col = $col_array[$k] + 1; $store_total = $rowValue['failed'] + $rowValue['passed']; $osa_score = '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($with_stock_col) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($with_stock_col) . $row . '),"")'; //oos $sheet->setCellValueByColumnAndRow($oos_col, $row, $rowValue['failed']); $oos_row_total += $rowValue['failed']; // //with stocks $sheet->setCellValueByColumnAndRow($with_stock_col, $row, $rowValue['passed']); $withstock_row_total += $rowValue['passed']; // //total $sheet->setCellValueByColumnAndRow($oos_col + 2, $row, $store_total); $sheet->setCellValueByColumnAndRow($oos_col + 3, $row, $osa_score); } $sheet->setCellValueByColumnAndRow($store_col + 1, $row, $oos_row_total); $sheet->setCellValueByColumnAndRow($store_col + 2, $row, $withstock_row_total); $sheet->setCellValueByColumnAndRow($store_col + 3, $row, $oos_row_total + $withstock_row_total); $osa_score_total = '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . '),"")'; $sheet->setCellValueByColumnAndRow($store_col + 4, $row, $osa_score_total); $row++; } $per_area_total_rows[] = $row; $sheet->setCellValueByColumnAndRow(0, $row, $key . ' Total'); $store_col = $default_store_col; foreach ($weeks as $week) { $sheet->setCellValueByColumnAndRow($store_col + 1, $row, "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $last_row . ")"); $sheet->setCellValueByColumnAndRow($store_col + 2, $row, "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $last_row . ")"); $sheet->setCellValueByColumnAndRow($store_col + 3, $row, "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 3) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 3) . $last_row . ")"); $sheet->setCellValueByColumnAndRow($store_col + 4, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $row . '),"")'); $store_col = $store_col + 4; } $sheet->setCellValueByColumnAndRow($store_col + 1, $row, "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $last_row . ")"); $sheet->setCellValueByColumnAndRow($store_col + 2, $row, "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $last_row . ")"); $sheet->setCellValueByColumnAndRow($store_col + 3, $row, "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 3) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($store_col + 3) . $last_row . ")"); $sheet->setCellValueByColumnAndRow($store_col + 4, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $row . '),"")'); $row++; } $sheet->setCellValueByColumnAndRow(0, $row, 'Grand Total'); $store_col = $default_store_col; foreach ($weeks as $week) { $oos_row_cells = []; $withstock_row_cells = []; $total_row_cells = []; $oos_col = $col_array[$week]; foreach ($per_area_total_rows as $cell) { $oos_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col) . $cell; $withstock_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $cell; $total_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $cell; } $sheet->setCellValueByColumnAndRow($oos_col, $row, '=sum(' . implode(",", $oos_row_cells) . ')'); $sheet->setCellValueByColumnAndRow($oos_col + 1, $row, '=sum(' . implode(",", $withstock_row_cells) . ')'); $sheet->setCellValueByColumnAndRow($oos_col + 2, $row, '=sum(' . implode(",", $total_row_cells) . ')'); $sheet->setCellValueByColumnAndRow($oos_col + 3, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $row . '),"")'); } $oos_row_cells = []; $withstock_row_cells = []; $total_row_cells = []; $oos_col = count($col_array) * 4 + 6; // dd($oos_col); foreach ($per_area_total_rows as $cell) { $oos_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $cell; $withstock_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $cell; $total_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 3) . $cell; } $sheet->setCellValueByColumnAndRow($oos_col + 1, $row, '=sum(' . implode(",", $oos_row_cells) . ')'); $sheet->setCellValueByColumnAndRow($oos_col + 2, $row, '=sum(' . implode(",", $withstock_row_cells) . ')'); $sheet->setCellValueByColumnAndRow($oos_col + 3, $row, '=sum(' . implode(",", $total_row_cells) . ')'); $sheet->setCellValueByColumnAndRow($oos_col + 4, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $row . '),"")'); }); })->download('xlsx'); } }
public function postsku(Request $request, $type = null) { ini_set('max_input_vars', 3000); set_time_limit(0); $sel_ar = $request->ar; $sel_st = $request->st; $frm = $request->fr; $to = $request->to; $sel_av = $request->availability; $sel_tag = $request->tags; $availability = ['1' => 'oos', '2' => 'osa']; $tags = ['1' => 'OSA', '2' => 'NPI']; $report_type = 1; if (is_null($type) || $type != 'assortment') { $report_type = 2; } if ($report_type == 2) { $areas = StoreInventories::getAreaList(); } else { $areas = AssortmentInventories::getAreaList(); } if (!empty($sel_ar)) { $data['areas'] = $sel_ar; } if (!empty($sel_st)) { $data['stores'] = $sel_st; } if (!empty($frm)) { $data['from'] = $frm; } if (!empty($to)) { $data['to'] = $to; } if (!empty($sel_av)) { $data['availability'] = $sel_av; } if (!empty($sel_tag)) { $data['tags'] = $sel_tag; } if ($report_type == 2) { $header = 'MKL OOS SKU Report'; $inventories = ItemInventories::getOosPerStore($data); } else { $header = 'Assortment OOS SKU Report'; $inventories = AssortmentItemInventories::getOosPerStore($data); } if ($request->has('submit')) { return view('oos.sku', compact('inventories', 'frm', 'to', 'areas', 'sel_ar', 'sel_st', 'header', 'type', 'type', 'sel_av', 'availability', 'sel_tag', 'tags')); } if ($request->has('download')) { \Excel::create($header, function ($excel) use($data, $inventories) { $items = []; $sku = []; $stores = []; foreach ($inventories as $value) { $week_start = new \DateTime(); $week_start->setISODate($value->yr, $value->yr_week, 1); $items[$value->area][$value->store_name][$value->sku_code]['other'] = $value->other_barcode; $items[$value->area][$value->store_name][$value->sku_code]['oos'][$value->transaction_date] = $value->oos; $sku[$value->sku_code] = $value->description; $stores[$value->store_name] = ['store_name' => $value->store_name, 'store_code' => $value->store_code, 'channel' => $value->channel_name]; } $excel->sheet('Sheet1', function ($sheet) use($data, $items, $sku, $stores) { $col = 7; $row = 2; $dates = ItemInventories::getDays($data['from'], $data['to']); foreach ($dates as $date) { $sheet->setCellValueByColumnAndRow($col, $row, $date->date); $col_array[$date->date] = $col; $col++; } $last_col = $col; $sheet->setCellValueByColumnAndRow($col, $row, 'Grand Total'); $sheet->setCellValueByColumnAndRow(0, $row, 'AREA'); $sheet->setCellValueByColumnAndRow(1, $row, 'STORE NAME'); $sheet->setCellValueByColumnAndRow(2, $row, 'STORE CODE'); $sheet->setCellValueByColumnAndRow(3, $row, 'CHANNEL NAME'); $sheet->setCellValueByColumnAndRow(4, $row, 'SKU CODE'); $sheet->setCellValueByColumnAndRow(5, $row, 'OTHER CODE'); $sheet->setCellValueByColumnAndRow(6, $row, 'ITEM DESCRIPTION'); // dd($items); $row = 3; $start_row = $row; foreach ($items as $area => $area_value) { foreach ($area_value as $store => $store_value) { foreach ($store_value as $item => $item_value) { $sheet->setCellValueByColumnAndRow(0, $row, $area); $sheet->setCellValueByColumnAndRow(1, $row, $store); $sheet->setCellValueByColumnAndRow(2, $row, $stores[$store]['store_code']); $sheet->setCellValueByColumnAndRow(3, $row, $stores[$store]['channel']); $sheet->setCellValueByColumnAndRow(4, $row, $item); $sheet->setCellValueByColumnAndRow(5, $row, $item_value['other']); $sheet->setCellValueByColumnAndRow(6, $row, $sku[$item]); foreach ($item_value['oos'] as $k => $oos) { $day_col = $col_array[$k]; $sheet->setCellValueByColumnAndRow($day_col, $row, $oos); } $store_item_oos_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex(7) . $row . ":" . \PHPExcel_Cell::stringFromColumnIndex($last_col - 1) . $row . ")"; $sheet->setCellValueByColumnAndRow($last_col, $row, $store_item_oos_total); $row++; } } $sheet->setCellValueByColumnAndRow(0, $row, $area . " Total"); $last_row = $row - 1; foreach ($dates as $date) { $day_col = $col_array[$date->date]; $area_week_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($day_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($day_col) . $last_row . ")"; $sheet->setCellValueByColumnAndRow($day_col, $row, $area_week_total); $per_area_total[$date->date][$area] = \PHPExcel_Cell::stringFromColumnIndex($day_col) . $row; } $area_grand_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($last_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($last_col) . $last_row . ")"; $sheet->setCellValueByColumnAndRow($last_col, $row, $area_grand_total); $g_total[] = \PHPExcel_Cell::stringFromColumnIndex($last_col) . $row; $row++; $start_row = $row; } $sheet->setCellValueByColumnAndRow(0, $row, 'Grand Total'); $col = 7; foreach ($dates as $date) { $area_total = []; $day_cols = $per_area_total[$date->date]; foreach ($day_cols as $cell) { $area_total[] = $cell; } $sheet->setCellValueByColumnAndRow($col, $row, '=sum(' . implode(",", $area_total) . ')'); $col++; } $sheet->setCellValueByColumnAndRow($last_col, $row, '=sum(' . implode(",", $g_total) . ')'); }); })->download('xlsx'); } }