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');
}
}
