public function readExcel() { $excels = \Excel::selectSheets($this->class_name)->load(storage_path('excel/' . $this->class_name . '.xlsx'))->get(); foreach ($excels as $excel) { $this->datas[] = $excel->toArray(); } return $this->datas; }
public function importExcel() { $fileInfo = $this->uploadFile(request()->file('file'), 'import'); $pathToFile = $fileInfo['path'] . '/' . $fileInfo['name']; Excel::selectSheets('Sheet1')->load($pathToFile, function ($reader) { $rows = $reader->get(); foreach ($rows as $key => $value) { // b1: insert Order $order = $this->excelForOrder($value, 'imei'); $newOrder = new OrderRepository(new Order()); $t = $newOrder->create($order); // b2: insert ship from order foreach ($t->phones as $index => $phone) { $input = $this->excelForShip($value, $phone->id); $newShip = new ShipRepository(new Ship()); $newShip->create($input); } } }); return redirect()->back(); }
public function silentLoad() { //used in order to reload the spreadsheet and immediately go back to the save ( in sessions ) query. Excel::selectSheets('Sheet1')->load(storage_path() . '\\items.xlsx', function ($reader) { // Loop through all sheets $reader->each(function ($row) { // Loop through all rows // $sheet->each(function ($row) { if ($row->cmmf != null) { //row in cmmf input is not blank so add/edit item $item = Item::where('cmmf', '=', $row->cmmf)->first(); if ($item == null) { //item doesn't exist yet $item = new Item(); $item->cmmf = $row->cmmf; $item->case = $row->case_pack; $item->weight = $row->weight_lb; $item->size = $row->size; $item->cartonsperpallet = $row->ctnspallet; $item->save(); unset($item); // $itemCount++; } else { //cmmf already exists..just update it $item->cmmf = $row->cmmf; $item->case = $row->case_pack; $item->weight = $row->weight_lb; $item->size = $row->size; $item->cartonsperpallet = $row->ctnspallet; $item->save(); unset($item); // $itemCount++; } } // }); }); }); // $data['itemCount']=$itemCount; // return View::make('load-list-done', $data); }
public function run() { $folderpath = 'database/seeds/seed_files'; $folders = File::directories($folderpath); $latest = '11232015'; foreach ($folders as $value) { $_dir = explode("/", $value); $cnt = count($_dir); $name = $_dir[$cnt - 1]; $latest_date = DateTime::createFromFormat('mdY', $latest); $now = DateTime::createFromFormat('mdY', $name); if ($now > $latest_date) { $latest = $name; } } $file_path = $folderpath . "/" . $latest . "/Category Tagging.xlsx"; echo (string) $file_path, "\n"; Model::unguard(); DB::statement('SET FOREIGN_KEY_CHECKS=0;'); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->category)) { $form_category = FormCategory::where('category', $row->category)->first(); // dd($form_category); if (!empty($form_category)) { $form_category->sos_tagging = $row->sos; $form_category->osa_tagging = $row->osa; $form_category->secondary_display = $row->secondary_display; $form_category->update(); } } }); }); DB::statement('SET FOREIGN_KEY_CHECKS=1;'); Model::reguard(); }
// $reader->createWriter($reader, 'Excel2007'); // $reader->create('Nimit New.xlsx')->store('xlsx'); })->setFileName('achmadi_BSSSSghfhfhfh')->save('xlsx'); // ->export('xlsx'); // ->get();//tidak bisa dipakai xxx // dd($dataexcel); // echo dd($dataexcel->take(10));//xx // echo $dataexcel->getTitle(); // foreach($dataexcel as $sheet) // { // // get sheet title // echo $sheet->getTitle(); // // dd($sheet); // } // dd($dataexcel); $dataExcel = Excel::selectSheets('2008-2009')->load('storage\\app\\php6EA5.tmp.xlsx', function ($reader) { echo $reader->setActiveSheetIndex(0)->getCell('B7')->getValue(); })->setFileName('achmadi_B')->save('xlsx'); // })->store('xlsx','E:\xampp 1.8.3\htdocs\laravel5\storage/exports/phpD3.xlsx'); // ->convert('csv'); // dd($dataExcel->getFileName()); // Storage::disk('local')->put('simpan.xlsx', File::get($dataexcel));//xxx // // Excel::create('sasa')->store('xlsx');//xxx }); Route::get('m', function () { $u = new Urusan(); $u->tahun = 2018; $u->kd_urusan = 65; $u->nm_urusan = 'Nama Urusan 09999999'; $u->save();
public function run() { $start_date = ''; $end_date = ''; $folderpath = 'database/seeds/seed_files'; $folders = File::directories($folderpath); $latest = '11232015'; foreach ($folders as $value) { $_dir = explode("/", $value); $cnt = count($_dir); $name = $_dir[$cnt - 1]; $latest_date = DateTime::createFromFormat('mdY', $latest); $now = DateTime::createFromFormat('mdY', $name); if ($now > $latest_date) { $latest = $name; } } $file_path = $folderpath . "/" . $latest . "/Store Mapping.xlsx"; echo (string) $file_path, "\n"; // dd($file_path); Model::unguard(); DB::statement('SET FOREIGN_KEY_CHECKS=0;'); DB::table('audit_templates')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->template)) { $template = AuditTemplate::where('template', $row->template)->first(); if (count($template) == 0) { $newtemplate = new AuditTemplate(); $newtemplate->template_code = $row->channel_code; $newtemplate->template = $row->template; $newtemplate->save(); } } }); }); DB::table('grade_matrixs')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->enrollment_type)) { $matrix = GradeMatrix::where('desc', $row->enrollment_type)->first(); if (count($matrix) == 0) { $newmatrix = new GradeMatrix(); $newmatrix->desc = $row->enrollment_type; $newmatrix->save(); } } }); }); DB::table('users')->truncate(); DB::table('role_user')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->fullname)) { $userlist = explode("/", $row->fullname); $emaillist = explode("/", $row->email); // dd($row); for ($i = 0; $i < count($userlist); $i++) { $user = User::where('username', $row->username)->first(); if (count($user) == 0) { if (empty($emaillist[$i])) { $email = strtolower($row->username . "@unilever.com"); } else { $email = strtolower($emaillist[$i]); } $newuser = User::create(array('name' => strtoupper($userlist[$i]), 'email' => $email, 'username' => $row->username, 'password' => Hash::make('password'))); $newuser->roles()->attach(3); } else { // $user->name = strtoupper($row->fullname); // $user->username = $row->username; // $user->email = strtolower($row->email); // $user->update(); // if(!$user->hasRole('field')){ // $user->roles()->attach(3); // } // echo $user->hasRole('field'); } } } }); }); DB::table('accounts')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->account)) { $account = Account::where('account', $row->account)->first(); if (count($account) == 0) { $newaccount = new Account(); $newaccount->account = $row->account; $newaccount->save(); } } }); }); DB::table('customers')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->account)) { // var_dump($row); $account = Account::where('account', $row->account)->first(); if (!empty($account)) { $customer = Customer::where('account_id', $account->id)->where('customer_code', $row->customer_code)->where('customer', $row->customer)->first(); if (count($customer) == 0) { $newcustomer = new Customer(); $newcustomer->account_id = $account->id; $newcustomer->customer_code = $row->customer_code; $newcustomer->customer = $row->customer; $newcustomer->save(); } } } }); }); DB::table('areas')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->account)) { $account = Account::where('account', $row->account)->first(); if (!empty($account)) { $customer = Customer::where('account_id', $account->id)->where('customer_code', $row->customer_code)->where('customer', $row->customer)->first(); if (!empty($customer)) { $area = Area::where('customer_id', $customer->id)->where('area', $row->area)->first(); if (count($area) == 0) { $newarea = new Area(); $newarea->customer_id = $customer->id; $newarea->area = $row->area; $newarea->save(); } } } } }); }); DB::table('regions')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->account)) { $region = Region::where('region_code', $row->region_code)->where('region', $row->region)->first(); if (count($region) == 0) { $newregion = new Region(); $newregion->region_code = $row->region_code; $newregion->region = $row->region; $newregion->save(); } } }); }); DB::table('distributors')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->account)) { $dis = Distributor::where('distributor_code', $row->distributor_code)->where('distributor', $row->distributor)->first(); if (count($dis) == 0) { $newdis = new Distributor(); $newdis->distributor_code = $row->distributor_code; $newdis->distributor = strtoupper($row->distributor); $newdis->save(); } } }); }); DB::table('stores')->truncate(); DB::table('store_user')->truncate(); Excel::selectSheets('Sheet1')->load($file_path, function ($reader) { $records = $reader->get(); $records->each(function ($row) { if (!is_null($row->account)) { $account = Account::where('account', $row->account)->first(); if (!empty($account)) { $customer = Customer::where('account_id', $account->id)->where('customer_code', $row->customer_code)->where('customer', $row->customer)->first(); if (!empty($customer)) { $region = Region::where('region_code', $row->region_code)->first(); $dis = Distributor::where('distributor_code', $row->distributor_code)->first(); $store = Store::where('account_id', $account->id)->where('customer_id', $customer->id)->where('region_id', $region->id)->where('distributor_id', $dis->id)->where('store_code', $row->store_code)->where('store', $row->store_name)->first(); if (count($store) == 0) { $template = AuditTemplate::where('template', $row->template)->first(); $matrix = GradeMatrix::where('desc', $row->enrollment_type)->first(); $newstore = new Store(); $newstore->account_id = $account->id; $newstore->customer_id = $customer->id; $newstore->region_id = $region->id; $newstore->distributor_id = $dis->id; $newstore->store_code = $row->store_code; $newstore->store = $row->store_name; $newstore->grade_matrix_id = $matrix->id; $newstore->audit_template_id = $template->id; $newstore->save(); $emaillist = explode("/", $row->email); for ($i = 0; $i < count($emaillist); $i++) { if (empty($emaillist[$i])) { $email = strtolower($row->username . "@unilever.com"); } else { $email = strtolower($emaillist[$i]); } $user = User::where('email', $email)->first(); $newstore->users()->attach($user->id); } } else { $emaillist = explode("/", $row->email); for ($i = 0; $i < count($emaillist); $i++) { if (empty($emaillist[$i])) { $email = strtolower($row->username . "@unilever.com"); } else { $email = strtolower($emaillist[$i]); } $user = User::where('email', $email)->first(); $store->users()->attach($user->id); } } } } } }); }); DB::statement('SET FOREIGN_KEY_CHECKS=1;'); Model::reguard(); }
}); Route::get('/databases', function () { return View::make('layouts.databases'); }); Route::get('/datawarehouse', function () { return View::make('layouts.datawarehouse'); }); // MySQL Route::resource('products', 'ProductsController'); Route::resource('product_brand_types', 'ProductBrandTypesController'); Route::resource('product_statuses', 'ProductStatusesController'); Route::resource('product_distributions', 'ProductDistributionsController'); Route::resource('product_orders', 'ProductOrdersController'); Route::resource('product_distribution_dates', 'ProductDistributionDatesController'); Route::resource('product_order_dates', 'ProductOrderDatesController'); // PostreSQL Route::resource('sales', 'SalesController'); Route::resource('branches', 'BranchesController'); Route::resource('cashier_sales', 'CashierSalesController'); Route::resource('cashier_infos', 'CashierInfosController'); Route::get('/original', function () { return View::make('hello'); }); Route::get('test', function () { if (DB::connection('pgsql')->getDatabaseName()) { echo "conncted sucessfully to database " . DB::connection('pgsql')->getDatabaseName(); } }); Route::get('xlsx', function () { $reader = Excel::selectSheets('customer')->load('/Users/brynzky/Sites/Projects/datawarehouse_etl/public/excel/excel.xlsx')->get()->toArray(); });