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