public function summary($id)
{
$store_audit = StoreAudit::findOrFail($id);
$summaries = StoreAuditSummary::where('store_audit_id', $id)->get();
$categories = StoreAuditSummary::getUniqueCategoryForPerpectStore($id);
$groups = StoreAuditSummary::getUniqueGroupForPerpectStore($id);
\Excel::create($store_audit->store_name . ' - ' . $store_audit->template_name, function ($excel) use($summaries, $categories, $groups) {
$excel->sheet('Sheet1', function ($sheet) use($summaries, $categories, $groups) {
$sheet->row(1, array('ENROLLMENT TYPE', 'AREA', 'STORE CODE', 'STORE NAME', 'KPI'));
$col = 5;
$x = array();
foreach ($categories as $category) {
$sheet->setCellValueByColumnAndRow($col, 1, $category->category);
$x[$category->category] = $col;
$col++;
}
$sheet->setCellValueByColumnAndRow($col, 1, 'PS SCORE');
$row = 2;
$y = array();
foreach ($groups as $group) {
$sheet->setCellValueByColumnAndRow(4, $row, $group->group);
$y[$group->group] = $row;
for ($i = 5; $i < 8; $i++) {
$sheet->setCellValueByColumnAndRow($i, $row, 'N/A');
}
// add ps score
$ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex(5) . $row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex(5) . $row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col - 1) . $row . ',1),1,"N/A"))';
$sheet->setCellValueByColumnAndRow($col, $row, $ps_code);
$row++;
}
$sheet->setCellValueByColumnAndRow(4, $row, 'Perfect Store Summary');
$col = 5;
$first_row = 2;
$last_row = $row - 1;
foreach ($categories as $category) {
$cat_ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',1),1,"N/A"))';
$sheet->setCellValueByColumnAndRow($col, $row, $cat_ps_code);
$col++;
}
$cat_ps_code = '=IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',0),0,IF(COUNTIF(' . \PHPExcel_Cell::stringFromColumnIndex($col) . $first_row . ':' . \PHPExcel_Cell::stringFromColumnIndex($col) . $last_row . ',1),1,"N/A"))';
$sheet->setCellValueByColumnAndRow($col, $row, $cat_ps_code);
// echo '<pre>';
// print_r($x);
// print_r($y);
// echo '</pre>';
// dd(1);
foreach ($summaries as $summary) {
$x_point = $x[$summary->category];
$y_point = $y[$summary->group];
$sheet->setCellValueByColumnAndRow($x_point, $y_point, $summary->passed);
}
})->download('xls');
});
}
public function storesummary($id)
{
$store_auidit = StoreAudit::findOrFail($id);
$summaries = StoreAuditSummary::raw("SELECT category,min(passed) as passed")->where('store_audit_id', $store_auidit->id)->groupBy('category')->get();
return view('userreport.storesummary', compact('summaries'));
}
public function storeaudit(Request $request)
{
if ($request->hasFile('data')) {
$destinationPath = storage_path() . '/uploads/audit/';
$fileName = $request->file('data')->getClientOriginalName();
$request->file('data')->move($destinationPath, $fileName);
$filePath = storage_path() . '/uploads/audit/' . $fileName;
$filename_data = explode("_", $fileName);
$user_id = $filename_data[0];
$store_code = $filename_data[1];
DB::beginTransaction();
try {
$reader = ReaderFactory::create(Type::CSV);
// for XLSX files
$reader->setFieldDelimiter('|');
$reader->open($filePath);
$first_row = true;
$summary = false;
$audit_id = 0;
foreach ($reader->getSheetIterator() as $sheet) {
foreach ($sheet->getRowIterator() as $row) {
if ($first_row) {
$start_date = date_format(date_create($row[11]), "Y-m-d");
$end_date = date_format(date_create($row[12]), "Y-m-d");
$audit = StoreAudit::where('user_id', $row[0])->where('store_code', $row[9])->where('start_date', $start_date)->where('end_date', $end_date)->first();
if (!empty($audit)) {
$audit->user_id = $row[0];
$audit->user_name = $row[1];
$audit->account = $row[2];
$audit->customer_code = $row[3];
$audit->customer = $row[4];
$audit->region_code = $row[5];
$audit->region = $row[6];
$audit->distributor_code = $row[7];
$audit->distributor = $row[8];
$audit->store_code = $row[9];
$audit->store_name = $row[10];
$audit->start_date = $start_date;
$audit->end_date = $end_date;
$audit->template_code = $row[13];
$audit->template_name = $row[14];
$audit->passed = $row[15];
$audit->updated_at = date('Y-m-d H:i:s');
$audit->update();
$audit_id = $audit->id;
StoreAuditDetail::where('store_audit_id', $audit_id)->delete();
StoreAuditSummary::where('store_audit_id', $audit_id)->delete();
} else {
$new_audit = new StoreAudit();
$new_audit->user_id = $row[0];
$new_audit->user_name = $row[1];
$new_audit->account = $row[2];
$new_audit->customer_code = $row[3];
$new_audit->customer = $row[4];
$new_audit->region_code = $row[5];
$new_audit->region = $row[6];
$new_audit->distributor_code = $row[7];
$new_audit->distributor = $row[8];
$new_audit->store_code = $row[9];
$new_audit->store_name = $row[10];
$new_audit->start_date = $start_date;
$new_audit->end_date = $end_date;
$new_audit->template_code = $row[13];
$new_audit->template_name = $row[14];
$new_audit->passed = $row[15];
$new_audit->save();
$audit_id = $new_audit->id;
}
$first_row = false;
} else {
if ($row[0] == 'audit_summary') {
$summary = true;
continue;
}
if (!$summary) {
StoreAuditDetail::insert(['store_audit_id' => $audit_id, 'category' => $row[0], 'group' => $row[1], 'prompt' => $row[2], 'type' => $row[3], 'answer' => $row[4]]);
} else {
StoreAuditSummary::insert(['store_audit_id' => $audit_id, 'category' => $row[0], 'group' => $row[1], 'passed' => $row[2]]);
}
}
}
}
$reader->close();
DB::commit();
return response()->json(array('msg' => 'file uploaded', 'status' => 0, 'audit_id' => $audit_id));
} catch (Exception $e) {
DB::rollback();
return response()->json(array('msg' => 'file uploaded error', 'status' => 1));
}
}
return response()->json(array('msg' => 'file uploaded error', 'status' => 1));
}