/**
* 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]);
}
}
}
}
/**
* 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");
}
/**
* 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();
}
/**
* 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 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));
}
}