Пример #1
5
 /**
  * Execute the console command.
  *
  * @return mixed
  */
 public function handle()
 {
     $startdate = $this->argument('start_date');
     $enddate = $this->argument('end_date');
     set_time_limit(0);
     ini_set('memory_limit', -1);
     $timeFirst = strtotime(date('Y-m-d H:i:s'));
     $filePath = storage_path() . '/report/postedmkl/Posted MKL ' . $startdate . ' - ' . $enddate . '.csv';
     $dates = Dates::getDatesFromRange($startdate, $enddate);
     $writer = WriterFactory::create(Type::CSV);
     // $writer->setShouldCreateNewSheetsAutomatically(true); // default value
     $writer->openToFile($filePath);
     $writer->addRow(array('AREA', 'REGION', 'DISTRIBUTOR', 'DISTRIBUTOR CODE', 'STORE ID', 'STORE CODE', 'STORE NAME', 'OTHER CODE', 'SKU CODE', 'DIVISION', 'BRAND', 'CATEGORY', 'SUB CATEGORY', 'ITEM DESCRIPTION', 'IG', 'FSO MULTIPLIER', 'SAPC', 'WHPC', 'WHCS', 'SO', 'FSO', 'FSO VAL', 'OSA', 'OSS', 'TRANSACTION DATE', 'POSTING DATE AND TIME', 'SIGNATURE LINK'));
     foreach ($dates as $date) {
         $rows = NULL;
         $rows = ItemInventories::getByDate($date);
         $plunck_data = [];
         foreach ($rows as $row) {
             if (!is_null($row->signature)) {
                 $link = url('api/pcountimage', [$row->signature]);
             } else {
                 $link = '';
             }
             $row_data[0] = $row->area;
             $row_data[1] = $row->region_name;
             $row_data[2] = $row->distributor;
             $row_data[3] = $row->distributor_code;
             $row_data[4] = $row->store_id;
             $row_data[5] = $row->store_code;
             $row_data[6] = $row->store_name;
             $row_data[7] = $row->other_barcode;
             $row_data[8] = $row->sku_code;
             $row_data[9] = $row->division;
             $row_data[10] = $row->brand;
             $row_data[11] = $row->category;
             $row_data[12] = $row->sub_category;
             $row_data[13] = $row->description;
             $row_data[14] = $row->ig;
             $row_data[15] = $row->fso_multiplier;
             $row_data[16] = $row->sapc;
             $row_data[17] = $row->whpc;
             $row_data[18] = $row->whcs;
             $row_data[19] = $row->so;
             $row_data[20] = $row->fso;
             $row_data[21] = (double) $row->fso_val;
             $row_data[22] = $row->osa;
             $row_data[23] = $row->oos;
             $row_data[24] = $row->transaction_date;
             $row_data[25] = $row->created_at;
             $row_data[26] = $link;
             $plunck_data[] = $row_data;
         }
         $writer->addRows($plunck_data);
         // add multiple rows at a time
     }
     $writer->close();
     $timeSecond = strtotime(date('Y-m-d H:i:s'));
     $differenceInSeconds = $timeSecond - $timeFirst;
     echo 'Time used ' . $differenceInSeconds . " sec";
 }
Пример #2
0
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     foreach (StoreInventories::where('fixed', 0)->get() as $inventory) {
         $inventory_items = ItemInventories::where('store_inventory_id', $inventory->id)->get();
         foreach ($inventory_items as $item) {
             $t_item = TempInventories::where('store_inventory_id', $inventory->id)->where('other_barcode', $item->other_barcode)->first();
             if (empty($t_item)) {
                 TempInventories::insert(array('id' => $item->id, 'store_inventory_id' => $item->store_inventory_id, 'division' => $item->division, 'category' => $item->category, 'category_long' => $item->category_long, 'sub_category' => $item->sub_category, 'brand' => $item->brand, 'sku_code' => $item->sku_code, 'other_barcode' => $item->other_barcode, 'description' => $item->description, 'description_long' => $item->description_long, 'lpbt' => $item->lpbt, 'conversion' => $item->conversion, 'ig' => $item->ig, 'fso_multiplier' => $item->fso_multiplier, 'sapc' => $item->sapc, 'whpc' => $item->whpc, 'whcs' => $item->whcs, 'so' => $item->so, 'fso' => $item->fso, 'fso_val' => $item->fso_val));
             }
         }
         $inventory->fixed = 1;
         $inventory->update();
         ItemInventories::where('store_inventory_id', $inventory->id)->delete();
         $store = Store::where('storeid', $inventory->store_id)->first();
         $skus = DB::table('store_items')->select('store_items.id', 'store_items.store_id', 'items.description', 'items.conversion', 'store_items.ig', 'store_items.fso_multiplier', 'items.lpbt', 'categories.category_long', 'sub_categories.sub_category', 'brands.brand', 'divisions.division', 'other_barcodes.other_barcode', 'items.sku_code')->join('stores', 'stores.id', '=', 'store_items.store_id')->join('items', 'items.id', '=', 'store_items.item_id')->join('other_barcodes', 'other_barcodes.item_id', '=', 'items.id')->join('categories', 'categories.id', '=', 'items.category_id')->join('sub_categories', 'sub_categories.id', '=', 'items.sub_category_id')->join('brands', 'brands.id', '=', 'items.brand_id')->join('divisions', 'divisions.id', '=', 'items.division_id')->whereRaw('other_barcodes.area_id = stores.area_id')->where('store_items.store_id', $store->id)->orderBy('items.id', 'asc')->get();
         foreach ($skus as $sku) {
             $temp_item = TempInventories::where('store_inventory_id', $inventory->id)->where('other_barcode', $sku->other_barcode)->first();
             if (empty($temp_item)) {
                 $item2 = Item::with('division')->with('category')->with('subcategory')->with('brand')->where('sku_code', $sku->sku_code)->first();
                 $fso = $sku->ig;
                 if ($sku->fso_multiplier > $sku->ig) {
                     $fso = $sku->fso_multiplier;
                 }
                 ItemInventories::insert(['store_inventory_id' => $inventory->id, 'division' => $item2->division->division, 'category' => $item2->category->category, 'category_long' => $item2->category->category_long, 'sub_category' => $item2->subcategory->sub_category, 'brand' => $item2->brand->brand, 'sku_code' => $item2->sku_code, 'other_barcode' => $sku->other_barcode, 'description' => $item2->description, 'description_long' => $item2->description_long, 'lpbt' => $item2->lpbt, 'conversion' => $sku->conversion, 'ig' => $sku->ig, 'fso_multiplier' => $sku->fso_multiplier, 'sapc' => 0, 'whpc' => 0, 'whcs' => 0, 'so' => $sku->ig, 'fso' => $fso, 'fso_val' => $item2->lpbt * $sku->ig]);
             } else {
                 ItemInventories::insert(['store_inventory_id' => $temp_item->store_inventory_id, 'division' => $temp_item->division, 'category' => $temp_item->category, 'category_long' => $temp_item->category_long, 'sub_category' => $temp_item->sub_category, 'brand' => $temp_item->brand, 'sku_code' => $temp_item->sku_code, 'other_barcode' => $temp_item->other_barcode, 'description' => $temp_item->description, 'description_long' => $temp_item->description_long, 'lpbt' => $temp_item->lpbt, 'conversion' => $temp_item->conversion, 'ig' => $temp_item->ig, 'fso_multiplier' => $temp_item->fso_multiplier, 'sapc' => $temp_item->sapc, 'whpc' => $temp_item->whpc, 'whcs' => $temp_item->whcs, 'so' => $temp_item->so, 'fso' => $temp_item->fso, 'fso_val' => $temp_item->fso_val]);
             }
         }
     }
 }
Пример #3
0
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     DB::table('item_inventories')->where('conversion', '>', 0)->update(['min_stock' => 2, 'oos' => 1, 'osa' => 0]);
     $areas = ['MDC', 'ROSE PHARMACY', '360 PHARMACY', '360 DRUG', 'ST. JOSEPH DRUG', 'SOUTH STAR DRUG'];
     $stores = StoreInventories::select('id')->whereIn('area', $areas)->get();
     foreach ($stores as $item) {
         ItemInventories::where('store_inventory_id', $item->id)->update(['min_stock' => 3]);
     }
     DB::statement("update `item_inventories` set `oos` = 0, `osa` = 1 where `sapc` > min_stock");
 }
Пример #4
0
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     DB::table('item_inventories')->update(array('osa' => 0, 'oos' => 0));
     $items = ItemInventories::where('sapc', '>', 0)->orWhere('whpc', '>', 0)->orWhere('whcs', '>', 0)->get();
     foreach ($items as $item) {
         $item->osa = 1;
         $item->update();
     }
     $items = ItemInventories::where('sapc', 0)->where('whpc', 0)->where('whcs', 0)->get();
     foreach ($items as $item) {
         $item->oos = 1;
         $item->update();
     }
 }
 public function run()
 {
     $storeInventories = StoreInventories::where('created_at', '>', '2016-05-25')->get();
     foreach ($storeInventories as $storeinventory) {
         ItemInventories::where('store_inventory_id', $storeinventory->id)->delete();
         $storeinventory->delete();
     }
     echo 'Total OSA Transaction deleted : ' . $storeInventories->count() . PHP_EOL;
     $assortmetnInventories = AssortmentInventories::where('created_at', '>', '2016-05-25')->get();
     foreach ($assortmetnInventories as $assortmentinventory) {
         AssortmentItemInventories::where('store_inventory_id', $assortmentinventory->id)->delete();
         $assortmentinventory->delete();
     }
     echo 'Total Assortment Transaction deleted : ' . $assortmetnInventories->count() . PHP_EOL;
     DB::table('updated_igs')->truncate();
 }
Пример #6
0
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     $temp_items = TempInventories::all();
     foreach ($temp_items as $temp_item) {
         $item = ItemInventories::where('store_inventory_id', $temp_item->store_inventory_id)->where('other_barcode', $temp_item->other_barcode)->first();
         // echo $item->id . "\n";
         if (!empty($item)) {
             $item->so = $temp_item->so;
             $item->fso = $temp_item->fso;
             $item->update();
         }
     }
     $items = ItemInventories::where('sapc', 0)->where('whpc', 0)->where('whcs', 0)->where('fso_multiplier', '>', 'ig')->get();
     foreach ($items as $item) {
         $item->fso = $item->fso_multiplier;
         $item->fso_val = $item->fso * $item->lpbt;
         $item->update();
     }
 }
 public function run()
 {
     // TestDummy::times(20)->create('App\Post');
     $list = ['MT CONVI', 'MT MINIMART', 'MT MDC'];
     $stores = StoreInventories::whereIn('client_name', $list)->where('transaction_date', '>', '2016-03-31')->get();
     foreach ($stores as $store) {
         $items = ItemInventories::where('store_inventory_id', $store->id)->get();
         $client_name = $store->client_name;
         foreach ($items as $item) {
             $osa = 0;
             $oos = 0;
             $total_stockcs = $item->sapc + $item->whpc + $item->whcs * $item->conversion;
             if (strtoupper($client_name) == 'MT CONVI' || strtoupper($client_name) == 'MT MINIMART' || strtoupper($client_name) == 'MT MDC') {
                 if (strtoupper($client_name) == 'MT MDC') {
                     if ($total_stockcs < 4) {
                         $oos = 1;
                     } else {
                         $osa = 1;
                     }
                 } else {
                     if ($total_stockcs < 3) {
                         $oos = 1;
                     } else {
                         $osa = 1;
                     }
                 }
             } else {
                 if ($total_stockcs > 0) {
                     $osa = 1;
                 } else {
                     $oos = 1;
                 }
             }
             $item->oos = $oos;
             $item->osa = $osa;
             $item->update();
         }
     }
 }
 public function run()
 {
     $divisions = ['HPC CATEGORY', 'FOODS CATEGORY'];
     $inventories = ItemInventories::whereNotIn('division', $divisions)->get();
     foreach ($inventories as $inventory) {
         $item = Item::where('sku_code', $inventory->sku_code)->first();
         $inventory->division = $item->division->division;
         $inventory->category = $item->category->category;
         $inventory->category_long = $item->category->category_long;
         $inventory->sub_category = $item->subcategory->sub_category;
         $inventory->brand = $item->brand->brand;
         $inventory->update();
     }
     $asinventories = AssortmentItemInventories::whereNotIn('division', $divisions)->get();
     foreach ($asinventories as $inventory) {
         $item = Item::where('sku_code', $inventory->sku_code)->first();
         $inventory->division = $item->division->division;
         $inventory->category = $item->category->category;
         $inventory->category_long = $item->category->category_long;
         $inventory->sub_category = $item->subcategory->sub_category;
         $inventory->brand = $item->brand->brand;
         $inventory->update();
     }
 }
Пример #9
0
 public function postStore(Request $request, $type = null)
 {
     $sel_ar = $request->ar;
     $sel_st = $request->st;
     $sel_tag = $request->tags;
     $frm = $request->fr;
     $to = $request->to;
     $sel_av = $request->availability;
     $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 OSA Per Store Report';
         $inventories = ItemInventories::getOsaPerStore($data);
     } else {
         $header = 'Assortment OSA Per Store Report';
         $inventories = AssortmentItemInventories::getOsaPerStore($data);
     }
     if ($request->has('submit')) {
         return view('osa.store', compact('inventories', 'frm', 'to', 'areas', 'sel_ar', 'sel_st', 'header', 'type', 'availability', 'sel_av', 'tags', 'sel_tag'));
     }
     // dd($inventories);
     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);
                 $store_list[$value->area][$value->store_name] = $value;
                 $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] = ['passed' => $value->passed, 'failed' => $value->failed];
             }
             // dd($store_list);
             ksort($weeks);
             $excel->sheet('Sheet1', function ($sheet) use($items, $weeks, $store_list) {
                 $default_store_col = 9;
                 $col_array = [];
                 $col = 10;
                 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 CODE');
                 $sheet->setCellValueByColumnAndRow(3, 3, 'DISTRIBUTOR NAME');
                 $sheet->setCellValueByColumnAndRow(4, 3, 'AGENCY');
                 $sheet->setCellValueByColumnAndRow(5, 3, 'STORE CODE');
                 $sheet->setCellValueByColumnAndRow(6, 3, 'STORE ID');
                 $sheet->setCellValueByColumnAndRow(7, 3, 'CHANNEL CODE');
                 $sheet->setCellValueByColumnAndRow(8, 3, 'CHANNEL NAME');
                 $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, 'OSA 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 OSA 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;
                     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_code);
                         $sheet->setCellValueByColumnAndRow(3, $row, $store_list[$key][$skey]->distributor);
                         $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, $store_list[$key][$skey]->channel_code);
                         $sheet->setCellValueByColumnAndRow(8, $row, $store_list[$key][$skey]->channel_name);
                         $sheet->setCellValueByColumnAndRow(9, $row, $skey);
                         $grand_total = 0;
                         foreach ($record as $k => $rowValue) {
                             $oos_col = $col_array[$k];
                             $with_stock_col = $col_array[$k] + 1;
                             $store_total = $rowValue['failed'] + $rowValue['passed'];
                             $osa_score = '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($with_stock_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);
                         $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 . '),"")';
                         $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 . ")");
                         $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 . '),"")');
                         $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 . ")");
                     $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 . '),"")');
                     $row++;
                 }
                 $sheet->setCellValueByColumnAndRow(0, $row, 'Grand Total');
                 $store_col = $default_store_col;
                 foreach ($weeks as $week) {
                     $oos_row_cells = [];
                     $withstock_row_cells = [];
                     $total_row_cells = [];
                     $oos_col = $col_array[$week];
                     foreach ($per_area_total_rows as $cell) {
                         $oos_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col) . $cell;
                         $withstock_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $cell;
                         $total_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $cell;
                     }
                     $sheet->setCellValueByColumnAndRow($oos_col, $row, '=sum(' . implode(",", $oos_row_cells) . ')');
                     $sheet->setCellValueByColumnAndRow($oos_col + 1, $row, '=sum(' . implode(",", $withstock_row_cells) . ')');
                     $sheet->setCellValueByColumnAndRow($oos_col + 2, $row, '=sum(' . implode(",", $total_row_cells) . ')');
                     $sheet->setCellValueByColumnAndRow($oos_col + 3, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $row . '),"")');
                 }
                 $oos_row_cells = [];
                 $withstock_row_cells = [];
                 $total_row_cells = [];
                 $oos_col = count($col_array) * 4 + 6;
                 // dd($oos_col);
                 foreach ($per_area_total_rows as $cell) {
                     $oos_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $cell;
                     $withstock_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $cell;
                     $total_row_cells[] = \PHPExcel_Cell::stringFromColumnIndex($oos_col + 3) . $cell;
                 }
                 $sheet->setCellValueByColumnAndRow($oos_col + 1, $row, '=sum(' . implode(",", $oos_row_cells) . ')');
                 $sheet->setCellValueByColumnAndRow($oos_col + 2, $row, '=sum(' . implode(",", $withstock_row_cells) . ')');
                 $sheet->setCellValueByColumnAndRow($oos_col + 3, $row, '=sum(' . implode(",", $total_row_cells) . ')');
                 $sheet->setCellValueByColumnAndRow($oos_col + 4, $row, '=IFERROR(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $row . '/SUM(' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 1) . $row . ',' . \PHPExcel_Cell::stringFromColumnIndex($oos_col + 2) . $row . '),"")');
             });
         })->download('xlsx');
     }
 }
 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');
     }
 }
Пример #11
0
 public function uploadpcount(Request $request)
 {
     $destinationPath = storage_path() . '/uploads/pcount/';
     $fileName = $request->file('data')->getClientOriginalName();
     $request->file('data')->move($destinationPath, $fileName);
     $filePath = storage_path() . '/uploads/pcount/' . $fileName;
     $filename_data = explode("-", $fileName);
     if (count($filename_data) == 6 && $filename_data[5] == '5.csv') {
         $storeid = $filename_data[0];
         $userid = $filename_data[1];
         $year = explode(".", $filename_data[4]);
         $transdate = date('Y-m-d', strtotime($year[0] . '-' . $filename_data[2] . '-' . $filename_data[3]));
         $imgname = explode(".", $fileName);
         $signature = 'IM_' . $imgname[0] . '.jpg';
         $store = Store::with('area')->with('enrollment')->with('distributor')->with('client')->with('channel')->with('customer')->with('region')->with('agency')->find($storeid);
         $user = User::find($userid);
         DB::beginTransaction();
         try {
             $settings = Setting::find(1);
             $store_inventory = StoreInventories::where('store_pri_id', $store->id)->where('transaction_date', $transdate)->first();
             if (!empty($store_inventory)) {
                 ItemInventories::where('store_inventory_id', $store_inventory->id)->delete();
                 $store_inventory->delete();
             }
             $store_inventory = StoreInventories::create(['area' => $store->area->area, 'enrollment_type' => $store->enrollment->enrollment, 'distributor_code' => $store->distributor->distributor_code, 'distributor' => $store->distributor->distributor, 'store_id' => $store->storeid, 'store_pri_id' => $store->id, 'store_code' => $store->store_code, 'store_code_psup' => $store->store_code_psup, 'store_name' => $store->store_name, 'client_code' => $store->client->client_code, 'client_name' => $store->client->client_name, 'channel_code' => $store->channel->channel_code, 'channel_name' => $store->channel->channel_desc, 'customer_code' => $store->customer->customer_code, 'customer_name' => $store->customer->customer_name, 'region_short_name' => $store->region->region_short, 'region_name' => $store->region->region, 'region_code' => $store->region->region_code, 'agency_code' => $store->agency->agency_code, 'agency' => $store->agency->agency_name, 'username' => $user->name, 'signature' => $signature, 'transaction_date' => $transdate]);
             $reader = ReaderFactory::create(Type::CSV);
             // for XLSX files
             $reader->setFieldDelimiter(';');
             $reader->open($filePath);
             $areas = ['MDC', 'ROSE PHARMACY', '360 PHARMACY', '360 DRUG', 'ST. JOSEPH DRUG', 'SOUTH STAR DRUG'];
             foreach ($reader->getSheetIterator() as $sheet) {
                 foreach ($sheet->getRowIterator() as $row) {
                     $item = Item::with('division')->with('category')->with('subcategory')->with('brand')->where('sku_code', trim($row[0]))->first();
                     if (!empty($item)) {
                         $osa = 0;
                         $oos = 0;
                         $min_stock = 2;
                         if (in_array($store->area->area, $areas)) {
                             $min_stock = 3;
                         }
                         // dd($min_stock);
                         $store_item = StoreItem::where('store_id', $store->id)->where('item_id', $item->id)->first();
                         if (!isset($row[13])) {
                             if (!empty($store_item)) {
                                 $min_stock = $store_item->min_stock;
                             }
                         } else {
                             $min_stock = $row[13];
                         }
                         if ($row[1] > $min_stock) {
                             $osa = 1;
                         } else {
                             $oos = 1;
                         }
                         ItemInventories::insert(['store_inventory_id' => $store_inventory->id, 'division' => $item->division->division, 'category' => $item->category->category, 'category_long' => $item->category->category_long, 'sub_category' => $item->subcategory->sub_category, 'brand' => $item->brand->brand, 'sku_code' => $item->sku_code, 'other_barcode' => $row[7], 'description' => $item->description, 'description_long' => $item->description_long, 'lpbt' => $item->lpbt, 'conversion' => $row[10], 'min_stock' => $min_stock, 'ig' => $row[9], 'fso_multiplier' => $row[8], 'sapc' => $row[1], 'whpc' => $row[2], 'whcs' => $row[3], 'so' => $row[4], 'fso' => $row[5], 'fso_val' => $row[6], 'osa' => $osa, 'oos' => $oos, 'osa_tagged' => $row[11], 'npi_tagged' => $row[12]]);
                         if ($settings->enable_ig_edit) {
                             if (!empty($store_item)) {
                                 if ($store_item->ig != $row[9]) {
                                     $updated_ig = UpdatedIg::where('store_id', $store->id)->where('sku_code', $item->sku_code)->first();
                                     $other_code = OtherBarcode::where('item_id', $item->id)->where('area_id', $store->area->id)->first();
                                     $othercode = '';
                                     if (!empty($other_code)) {
                                         $othercode = $other_code->other_barcode;
                                     }
                                     if (!empty($updated_ig)) {
                                         $updated_ig->area = $store->area->area;
                                         $updated_ig->region_code = $store->region->region_code;
                                         $updated_ig->region = $store->region->region;
                                         $updated_ig->distributor_code = $store->distributor->distributor_code;
                                         $updated_ig->distributor = $store->distributor->distributor;
                                         $updated_ig->agency_code = $store->agency->agency_code;
                                         $updated_ig->agency = $store->agency->agency_name;
                                         $updated_ig->storeid = $store->storeid;
                                         $updated_ig->channel_code = $store->channel->channel_code;
                                         $updated_ig->channel = $store->channel->channel_desc;
                                         $updated_ig->other_code = $othercode;
                                         $updated_ig->division = $item->division->division;
                                         $updated_ig->category = $item->category->category;
                                         $updated_ig->sub_category = $item->subcategory->sub_category;
                                         $updated_ig->brand = $item->brand->brand;
                                         $updated_ig->conversion = $item->conversion;
                                         $updated_ig->fso_multiplier = $row[8];
                                         $updated_ig->min_stock = $min_stock;
                                         $updated_ig->lpbt = $item->lpbt;
                                         $updated_ig->ig = $row[9];
                                         $updated_ig->updated_at = date('Y-m-d H:i:s');
                                         $updated_ig->save();
                                     } else {
                                         UpdatedIg::create(['area' => $store->area->area, 'region_code' => $store->region->region_code, 'region' => $store->region->region, 'distributor_code' => $store->distributor->distributor_code, 'distributor' => $store->distributor->distributor, 'agency_code' => $store->agency->agency_code, 'agency' => $store->agency->agency_name, 'storeid' => $store->storeid, 'store_id' => $store->id, 'store_code' => $store->store_code, 'store_name' => $store->store_name, 'channel_code' => $store->channel->channel_code, 'channel' => $store->channel->channel_desc, 'other_code' => $othercode, 'sku_code' => $item->sku_code, 'description' => $item->description, 'division' => $item->division->division, 'category' => $item->category->category, 'sub_category' => $item->subcategory->sub_category, 'brand' => $item->brand->brand, 'conversion' => $item->conversion, 'fso_multiplier' => $row[8], 'min_stock' => $min_stock, 'lpbt' => $item->lpbt, 'ig' => $row[9]]);
                                     }
                                 }
                             }
                         }
                     }
                 }
             }
             $reader->close();
             DB::commit();
             return response()->json(array('msg' => 'file uploaded', 'status' => 0));
         } catch (Exception $e) {
             DB::rollback();
             return response()->json(array('msg' => 'file uploaded error', 'status' => 1));
         }
     } else {
         return response()->json(array('msg' => 'Cannot upload file, invalid version', 'status' => 1));
     }
 }
Пример #12
0
 public function brandlist(Request $request)
 {
     if (\Request::ajax()) {
         $divisions = $request->divisions;
         $categories = $request->categories;
         $sub_categories = $request->sub_categories;
         $type = $request->type;
         $report_type = 1;
         if (is_null($type) || $type != 'assortment') {
             $report_type = 2;
         }
         if ($report_type == 2) {
             $data['selection'] = ItemInventories::select('brand')->whereIn('division', $divisions)->whereIn('category', $categories)->whereIn('sub_category', $sub_categories)->groupBy('brand')->orderBy('brand')->lists('brand', 'brand');
         } else {
             $data['selection'] = AssortmentItemInventories::select('brand')->whereIn('division', $divisions)->whereIn('category', $categories)->whereIn('sub_category', $sub_categories)->groupBy('brand')->orderBy('brand')->lists('brand', 'brand');
         }
         return \Response::json($data, 200);
     }
 }
 public function postsku(Request $request, $type = null)
 {
     ini_set('max_input_vars', 3000);
     set_time_limit(0);
     $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 OOS SKU Report';
         $inventories = ItemInventories::getOosPerStore($data);
     } else {
         $header = 'Assortment OOS SKU Report';
         $inventories = AssortmentItemInventories::getOosPerStore($data);
     }
     if ($request->has('submit')) {
         return view('oos.sku', compact('inventories', 'frm', 'to', 'areas', 'sel_ar', 'sel_st', 'header', 'type', 'type', 'sel_av', 'availability', 'sel_tag', 'tags'));
     }
     if ($request->has('download')) {
         \Excel::create($header, function ($excel) use($data, $inventories) {
             $items = [];
             $sku = [];
             $stores = [];
             foreach ($inventories as $value) {
                 $week_start = new \DateTime();
                 $week_start->setISODate($value->yr, $value->yr_week, 1);
                 $items[$value->area][$value->store_name][$value->sku_code]['other'] = $value->other_barcode;
                 $items[$value->area][$value->store_name][$value->sku_code]['oos'][$value->transaction_date] = $value->oos;
                 $sku[$value->sku_code] = $value->description;
                 $stores[$value->store_name] = ['store_name' => $value->store_name, 'store_code' => $value->store_code, 'channel' => $value->channel_name];
             }
             $excel->sheet('Sheet1', function ($sheet) use($data, $items, $sku, $stores) {
                 $col = 7;
                 $row = 2;
                 $dates = ItemInventories::getDays($data['from'], $data['to']);
                 foreach ($dates as $date) {
                     $sheet->setCellValueByColumnAndRow($col, $row, $date->date);
                     $col_array[$date->date] = $col;
                     $col++;
                 }
                 $last_col = $col;
                 $sheet->setCellValueByColumnAndRow($col, $row, 'Grand Total');
                 $sheet->setCellValueByColumnAndRow(0, $row, 'AREA');
                 $sheet->setCellValueByColumnAndRow(1, $row, 'STORE NAME');
                 $sheet->setCellValueByColumnAndRow(2, $row, 'STORE CODE');
                 $sheet->setCellValueByColumnAndRow(3, $row, 'CHANNEL NAME');
                 $sheet->setCellValueByColumnAndRow(4, $row, 'SKU CODE');
                 $sheet->setCellValueByColumnAndRow(5, $row, 'OTHER CODE');
                 $sheet->setCellValueByColumnAndRow(6, $row, 'ITEM DESCRIPTION');
                 // dd($items);
                 $row = 3;
                 $start_row = $row;
                 foreach ($items as $area => $area_value) {
                     foreach ($area_value as $store => $store_value) {
                         foreach ($store_value as $item => $item_value) {
                             $sheet->setCellValueByColumnAndRow(0, $row, $area);
                             $sheet->setCellValueByColumnAndRow(1, $row, $store);
                             $sheet->setCellValueByColumnAndRow(2, $row, $stores[$store]['store_code']);
                             $sheet->setCellValueByColumnAndRow(3, $row, $stores[$store]['channel']);
                             $sheet->setCellValueByColumnAndRow(4, $row, $item);
                             $sheet->setCellValueByColumnAndRow(5, $row, $item_value['other']);
                             $sheet->setCellValueByColumnAndRow(6, $row, $sku[$item]);
                             foreach ($item_value['oos'] as $k => $oos) {
                                 $day_col = $col_array[$k];
                                 $sheet->setCellValueByColumnAndRow($day_col, $row, $oos);
                             }
                             $store_item_oos_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex(7) . $row . ":" . \PHPExcel_Cell::stringFromColumnIndex($last_col - 1) . $row . ")";
                             $sheet->setCellValueByColumnAndRow($last_col, $row, $store_item_oos_total);
                             $row++;
                         }
                     }
                     $sheet->setCellValueByColumnAndRow(0, $row, $area . " Total");
                     $last_row = $row - 1;
                     foreach ($dates as $date) {
                         $day_col = $col_array[$date->date];
                         $area_week_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($day_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($day_col) . $last_row . ")";
                         $sheet->setCellValueByColumnAndRow($day_col, $row, $area_week_total);
                         $per_area_total[$date->date][$area] = \PHPExcel_Cell::stringFromColumnIndex($day_col) . $row;
                     }
                     $area_grand_total = "=SUM(" . \PHPExcel_Cell::stringFromColumnIndex($last_col) . $start_row . ":" . \PHPExcel_Cell::stringFromColumnIndex($last_col) . $last_row . ")";
                     $sheet->setCellValueByColumnAndRow($last_col, $row, $area_grand_total);
                     $g_total[] = \PHPExcel_Cell::stringFromColumnIndex($last_col) . $row;
                     $row++;
                     $start_row = $row;
                 }
                 $sheet->setCellValueByColumnAndRow(0, $row, 'Grand Total');
                 $col = 7;
                 foreach ($dates as $date) {
                     $area_total = [];
                     $day_cols = $per_area_total[$date->date];
                     foreach ($day_cols as $cell) {
                         $area_total[] = $cell;
                     }
                     $sheet->setCellValueByColumnAndRow($col, $row, '=sum(' . implode(",", $area_total) . ')');
                     $col++;
                 }
                 $sheet->setCellValueByColumnAndRow($last_col, $row, '=sum(' . implode(",", $g_total) . ')');
             });
         })->download('xlsx');
     }
 }
Пример #14
0
 /**
  * Show the form for editing the specified resource.
  *
  * @param  int  $id
  * @return \Illuminate\Http\Response
  */
 public function edit($id)
 {
     //
     $status = ['0' => 'In-active', '1' => 'Active'];
     $divisions = ItemInventories::getDivisionList();
     $brand = Brand::all()->lists('brand', 'id');
     $data = array();
     $sel_dv = [];
     $sel_cat = [];
     $sel_scat = [];
     $sel_br = [];
     if (!empty($sel_cat)) {
         $data['categories'] = $sel_cat;
     }
     if (!empty($sel_dv)) {
         $data['divisions'] = $sel_dv;
     }
     if (!empty($sel_scat)) {
         $data['sub_categories'] = $sel_scat;
     }
     if (!empty($sel_br)) {
         $data['brands'] = $sel_br;
     }
     $item = Item::findOrFail($id);
     return view('item.edit', ['item' => $item, 'brand' => $brand], compact('sel_dv', 'divisions', 'sel_cat', 'sel_scat', 'sel_br', 'status'));
 }
Пример #15
-1
 /**
  * Store a newly created resource in storage.
  *
  * @param  \Illuminate\Http\Request  $request
  * @return \Illuminate\Http\Response
  */
 public function store(Request $request, $type = null)
 {
     $sel_ag = $request->ag;
     $sel_cl = $request->cl;
     $sel_ch = $request->ch;
     $sel_ds = $request->ds;
     $sel_en = $request->en;
     $sel_rg = $request->rg;
     $sel_st = $request->st;
     $sel_tag = $request->tags;
     $sel_av = $request->availability;
     $tags = ['1' => 'OSA', '2' => 'NPI'];
     $availability = ['1' => 'oos', '2' => 'osa'];
     $report_type = 1;
     if (is_null($type) || $type != 'assortment') {
         $report_type = 2;
     }
     if ($report_type == 2) {
         $agencies = StoreInventories::getAgencyList();
         $divisions = ItemInventories::getDivisionList();
     } else {
         $agencies = AssortmentInventories::getAgencyList();
         $divisions = AssortmentItemInventories::getDivisionList();
     }
     $sel_dv = $request->dv;
     $sel_cat = $request->ct;
     $sel_scat = $request->sc;
     $sel_br = $request->br;
     $frm = $request->fr;
     $to = $request->to;
     $data = array();
     if (!empty($sel_ag)) {
         $data['agencies'] = $sel_ag;
     }
     if (!empty($sel_cl)) {
         $data['clients'] = $sel_cl;
     }
     if (!empty($sel_ch)) {
         $data['channels'] = $sel_ch;
     }
     if (!empty($sel_ds)) {
         $data['distributors'] = $sel_ds;
     }
     if (!empty($sel_en)) {
         $data['enrollments'] = $sel_en;
     }
     if (!empty($sel_rg)) {
         $data['regions'] = $sel_rg;
     }
     if (!empty($sel_st)) {
         $data['stores'] = $sel_st;
     }
     if (!empty($sel_dv)) {
         $data['divisions'] = $sel_dv;
     }
     if (!empty($sel_cat)) {
         $data['categories'] = $sel_cat;
     }
     if (!empty($sel_scat)) {
         $data['sub_categories'] = $sel_scat;
     }
     if (!empty($sel_br)) {
         $data['brands'] = $sel_br;
     }
     if (!empty($frm)) {
         $data['from'] = $frm;
     }
     if (!empty($to)) {
         $data['to'] = $to;
     }
     if (!empty($sel_tag)) {
         $data['tags'] = $sel_tag;
     }
     if (!empty($sel_av)) {
         $data['availability'] = $sel_av;
     }
     if ($report_type == 2) {
         $items = ItemInventories::filter($data);
         $header = "MKL Posted Transaction Report";
     } else {
         $items = AssortmentItemInventories::filter($data);
         $header = "Assortment Posted Transaction Report";
     }
     if ($request->has('submit')) {
         return view('inventory.index', compact('frm', 'to', 'agencies', 'sel_ag', 'sel_cl', 'sel_ch', 'sel_ds', 'sel_en', 'sel_rg', 'sel_st', 'divisions', 'sel_dv', 'sel_cat', 'sel_scat', 'sel_br', 'items', 'header', 'type', 'sel_tag', 'tags', 'sel_av', 'availability'));
     }
     set_time_limit(0);
     if ($request->has('download')) {
         $take = 1000;
         // adjust this however you choose
         $skip = 0;
         // used to skip over the ones you've already processed
         $writer = WriterFactory::create(Type::CSV);
         $writer->openToBrowser($header . '.csv');
         $writer->addRow(array('AREA', 'REGION', 'DISTRIBUTOR', 'DISTRIBUTOR CODE', 'STORE ID', 'STORE CODE', 'STORE NAME', 'OTHER CODE', 'SKU CODE', 'DIVISION', 'BRAND', 'CATEGORY', 'SUB CATEGORY', 'ITEM DESCRIPTION', 'IG', 'FSO MULTIPLIER', 'SAPC', 'WHPC', 'WHCS', 'SO', 'FSO', 'FSO VAL', 'OSA', 'OOS', 'TRANSACTION DATE', 'POSTING DATE AND TIME', 'SIGNATURE LINK'));
         if ($report_type == 2) {
             while ($rows = ItemInventories::getPartial($data, $take, $skip)) {
                 if (count($rows) == 0) {
                     break;
                 }
                 $skip++;
                 $plunck_data = [];
                 foreach ($rows as $row) {
                     if (!is_null($row->signature)) {
                         if ($report_type == 2) {
                             $link = url('api/pcountimage', [$row->signature]);
                         } else {
                             $link = url('api/assortmentimage', [$row->signature]);
                         }
                     } else {
                         $link = '';
                     }
                     $row_data[0] = $row->area;
                     $row_data[1] = $row->region_name;
                     $row_data[2] = $row->distributor;
                     $row_data[3] = $row->distributor_code;
                     $row_data[4] = $row->store_id;
                     $row_data[5] = $row->store_code;
                     $row_data[6] = $row->store_name;
                     $row_data[7] = $row->other_barcode;
                     $row_data[8] = $row->sku_code;
                     $row_data[9] = $row->division;
                     $row_data[10] = $row->brand;
                     $row_data[11] = $row->category;
                     $row_data[12] = $row->sub_category;
                     $row_data[13] = $row->description;
                     $row_data[14] = $row->ig;
                     $row_data[15] = $row->fso_multiplier;
                     $row_data[16] = $row->sapc;
                     $row_data[17] = $row->whpc;
                     $row_data[18] = $row->whcs;
                     $row_data[19] = $row->so;
                     $row_data[20] = $row->fso;
                     $row_data[21] = (double) $row->fso_val;
                     $row_data[22] = $row->osa;
                     $row_data[23] = $row->oos;
                     $row_data[24] = $row->transaction_date;
                     $row_data[25] = $row->created_at;
                     $row_data[26] = $link;
                     $plunck_data[] = $row_data;
                 }
                 $writer->addRows($plunck_data);
                 // add multiple rows at a time
             }
         } else {
             while ($rows = AssortmentItemInventories::getPartial($data, $take, $skip)) {
                 if (count($rows) == 0) {
                     break;
                 }
                 $skip++;
                 $plunck_data = [];
                 foreach ($rows as $row) {
                     if (!is_null($row->signature)) {
                         if ($report_type == 2) {
                             $link = url('api/pcountimage', [$row->signature]);
                         } else {
                             $link = url('api/assortmentimage', [$row->signature]);
                         }
                     } else {
                         $link = '';
                     }
                     $row_data[0] = $row->area;
                     $row_data[1] = $row->region_name;
                     $row_data[2] = $row->distributor;
                     $row_data[3] = $row->distributor_code;
                     $row_data[4] = $row->store_id;
                     $row_data[5] = $row->store_code;
                     $row_data[6] = $row->store_name;
                     $row_data[7] = $row->other_barcode;
                     $row_data[8] = $row->sku_code;
                     $row_data[9] = $row->division;
                     $row_data[10] = $row->brand;
                     $row_data[11] = $row->category;
                     $row_data[12] = $row->sub_category;
                     $row_data[13] = $row->description;
                     $row_data[14] = $row->ig;
                     $row_data[15] = $row->fso_multiplier;
                     $row_data[16] = $row->sapc;
                     $row_data[17] = $row->whpc;
                     $row_data[18] = $row->whcs;
                     $row_data[19] = $row->so;
                     $row_data[20] = $row->fso;
                     $row_data[21] = (double) $row->fso_val;
                     $row_data[22] = $row->osa;
                     $row_data[23] = $row->oos;
                     $row_data[24] = $row->transaction_date;
                     $row_data[25] = $row->created_at;
                     $row_data[26] = $link;
                     $plunck_data[] = $row_data;
                 }
                 $writer->addRows($plunck_data);
                 // add multiple rows at a time
             }
         }
         $writer->close();
     }
 }