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