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();
}
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();
}
}
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');
}
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$agencies = AssortmentInventories::getAgencyList();
$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;
$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;
}
$items = AssortmentItemInventories::filter($data);
if ($request->has('submit')) {
return view('inventory.assortment', 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'));
}
if ($request->has('download')) {
\Excel::create('Assortment Posted Transaction', function ($excel) use($items) {
$excel->sheet('Sheet1', function ($sheet) use($items) {
$sheet->row(1, array('STORE CODE', 'STORE NAME', 'OTHER CODE', 'SKU CODE', 'ITEM DESCRIPTION', 'IG', 'FSO MULTIPLIER', 'SAPC', 'WHPC', 'WHCS', 'SO', 'FSO', 'FSO VAL', 'OSA', 'OSS', 'TRANSACTION DATE', 'POSTING DATE AND TIME', 'SIGNATURE LINK'));
$row = 2;
foreach ($items as $item) {
if (!is_null($item->signature)) {
$link = url('api/assortmentimage', [$item->signature]);
} else {
$link = '';
}
$sheet->setCellValueByColumnAndRow(0, $row, $item->store_code);
$sheet->setCellValueByColumnAndRow(1, $row, $item->store_name);
$sheet->setCellValueByColumnAndRow(2, $row, $item->other_barcode);
$sheet->setCellValueByColumnAndRow(3, $row, $item->sku_code);
$sheet->setCellValueByColumnAndRow(4, $row, $item->description);
$sheet->setCellValueByColumnAndRow(5, $row, $item->ig);
$sheet->setCellValueByColumnAndRow(6, $row, $item->fso_multiplier);
$sheet->setCellValueByColumnAndRow(7, $row, $item->sapc);
$sheet->setCellValueByColumnAndRow(8, $row, $item->whpc);
$sheet->setCellValueByColumnAndRow(9, $row, $item->whcs);
$sheet->setCellValueByColumnAndRow(10, $row, $item->so);
$sheet->setCellValueByColumnAndRow(11, $row, $item->fso);
$sheet->setCellValueByColumnAndRow(12, $row, $item->fso_val);
$sheet->setCellValueByColumnAndRow(13, $row, $item->osa);
$sheet->setCellValueByColumnAndRow(14, $row, $item->oos);
$sheet->setCellValueByColumnAndRow(15, $row, $item->transaction_date);
$sheet->setCellValueByColumnAndRow(16, $row, $item->created_at);
$sheet->setCellValueByColumnAndRow(17, $row, $link);
$sheet->setCellValue('P' . $row, $link);
$sheet->getCell('P' . $row)->getHyperlink()->setUrl($link);
$sheet->getCell('P' . $row)->getHyperlink()->setTooltip('Download Signature');
$sheet->getStyle('P' . $row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$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');
}
}
public function uploadassortment(Request $request)
{
$destinationPath = storage_path() . '/uploads/assortment/';
$fileName = $request->file('data')->getClientOriginalName();
$request->file('data')->move($destinationPath, $fileName);
$filePath = storage_path() . '/uploads/assortment/' . $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);
// dd($store->store_code);
$user = User::find($userid);
DB::beginTransaction();
try {
// dd($store);
$store_inventory = AssortmentInventories::where('store_pri_id', $store->id)->where('transaction_date', $transdate)->first();
if (!empty($store_inventory)) {
AssortmentItemInventories::where('store_inventory_id', $store_inventory->id)->delete();
$store_inventory->delete();
}
$store_inventory = AssortmentInventories::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)) {
$store_item = StoreItem::where('store_id', $store->id)->where('item_id', $item->id)->first();
$osa = 0;
$oos = 0;
$min_stock = 2;
if (in_array($store->area->area, $areas)) {
$min_stock = 3;
}
if (!isset($row[11])) {
if (!empty($store_item)) {
$min_stock = $store_item->min_stock;
}
} else {
$min_stock = $row[11];
}
if ($row[1] > $min_stock) {
$osa = 1;
} else {
$oos = 1;
}
AssortmentItemInventories::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], 'ig' => $row[9], 'min_stock' => $min_stock, '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]);
}
}
}
$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));
}
}
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');
}
}
/**
* 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();
}
}
