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