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