public function postposting(Request $request)
{
$sel_ag = $request->ag;
$sel_ch = $request->ch;
$sel_rg = $request->rg;
$sel_st = $request->st;
$sel_us = $request->us;
$sel_ty = $request->ty;
$frm = $request->fr;
$to = $request->to;
$agencies = ComplianceRepository::getAllAgency();
$regions = ComplianceRepository::getAllRegion();
$channels = ComplianceRepository::getAllChannel();
$stores = ComplianceRepository::getAllStore();
$users = ComplianceRepository::getAllUser();
$types = ['1' => 'OSA', '2' => 'Assoertment'];
if (!empty($frm)) {
$data['from'] = $frm;
}
if (!empty($to)) {
$data['to'] = $to;
}
if (!empty($sel_ag)) {
$data['agencies'] = $sel_ag;
}
if (!empty($sel_rg)) {
$data['regions'] = $sel_rg;
}
if (!empty($sel_ch)) {
$data['channels'] = $sel_ch;
}
if (!empty($sel_st)) {
$data['stores'] = $sel_st;
}
if (!empty($sel_us)) {
$data['users'] = $sel_us;
}
if (!empty($sel_ty)) {
$data['types'] = $sel_ty;
}
$postings = HistoryRepositiry::getHistory($data);
if ($request->has('submit')) {
return view('history.posting', compact('postings', 'frm', 'to', 'agencies', 'sel_ag', 'regions', 'sel_rg', 'channels', 'sel_ch', 'stores', 'sel_st', 'users', 'sel_us', 'types', 'sel_ty'));
}
if ($request->has('download')) {
$fileName = "Posting History Report.csv";
$writer = WriterFactory::create(Type::CSV);
// for CSV files
$writer->openToBrowser($fileName);
// stream data directly to the browser
$writer->addRow(array('Agency Code', 'Agency Name', 'Region Code', 'Region Name', 'Channel Code', 'Channel Name', 'Distributor', 'Store Name', 'Store Code', 'Username', 'Transaction Date', 'Posting Date', 'Posting Type'));
foreach ($postings as $row) {
$_data[0] = $row->agency_code;
$_data[1] = $row->agency;
$_data[2] = $row->region_code;
$_data[3] = $row->region_name;
$_data[4] = $row->channel_code;
$_data[5] = $row->channel_name;
$_data[6] = $row->distributor;
$_data[7] = $row->store_name;
$_data[8] = $row->store_code;
$_data[9] = $row->username;
$_data[10] = $row->transaction_date;
$_data[11] = $row->updated_at;
$_data[12] = $row->type;
$writer->addRow($_data);
// add multiple rows at a time
}
$writer->close();
}
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
set_time_limit(0);
$sel_ar = $request->ar;
$sel_st = $request->st;
$frm = $request->fr;
$to = $request->to;
$areas = ComplianceRepository::getAllArea();
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;
}
$compliances = ComplianceRepository::getAssortmentCompliance($data);
if ($request->has('submit')) {
return view('compliance.index', compact('compliances', 'frm', 'to', 'areas', 'sel_ar', 'sel_st'));
}
if ($request->has('download')) {
\Excel::create('Total Compliance Report', function ($excel) use($compliances) {
$weeks = [];
$items = [];
$store_list = [];
foreach ($compliances as $value) {
$week_start = new \DateTime();
$week_start->setISODate($value->yr, $value->yr_week);
$weeks[$week_start->format('Y-m-d')] = "Week " . $value->yr_week . " of " . $value->yr;
$store_list[$value->area][$value->store_name] = $value;
$items[$value->area][$value->store_name]["Week " . $value->yr_week . " of " . $value->yr] = ['passed' => $value->passed, 'failed' => $value->failed, 'client_name' => $value->client_name];
}
// dd($items);
ksort($weeks);
$excel->sheet('Sheet1', function ($sheet) use($items, $weeks, $store_list) {
$default_store_col = 7;
$col_array = [];
$col = 8;
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 NAME');
$sheet->setCellValueByColumnAndRow(3, 3, 'DISTRIBUTOR CODE');
$sheet->setCellValueByColumnAndRow(4, 3, 'AGENCY');
$sheet->setCellValueByColumnAndRow(5, 3, 'STORE CODE');
$sheet->setCellValueByColumnAndRow(6, 3, 'STORE ID');
$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, 'Compliance 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 Compliance 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;
$client_name = '';
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);
$sheet->setCellValueByColumnAndRow(3, $row, $store_list[$key][$skey]->distributor_code);
$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, $skey);
$grand_total = 0;
foreach ($record as $k => $rowValue) {
$client_name = strtoupper($rowValue['client_name']);
$oos_col = $col_array[$k];
$with_stock_col = $col_array[$k] + 1;
$store_total = $rowValue['failed'] + $rowValue['passed'];
if ($client_name == 'MT MDC') {
$osa_score = '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($with_stock_col) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($with_stock_col) . $row . '),"")';
} else {
$osa_score = '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($oos_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);
if ($client_name == 'MT MDC') {
$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 . '),"")';
} else {
$osa_score_total = '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $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 . ")");
if ($client_name == 'MT MDC') {
$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 . '),"")');
} else {
$sheet->setCellValueByColumnAndRow($store_col + 4, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $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 . ")");
if ($client_name == 'MT MDC') {
$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 . '),"")');
} else {
$sheet->setCellValueByColumnAndRow($store_col + 4, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 2) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($store_col + 1) . $row . '),"")');
}
$row++;
}
});
})->download('xlsx');
}
}