public function import()
{
$path = 'C:/Filename.xls';
Excel::selectSheetsByIndex(0)->load($path, function ($reader) {
DB::table('members')->insert($reader->toArray());
});
}
public function proses_import(Request $request)
{
ini_set('max_execution_time', 300);
$rules = ['excel' => 'required'];
$pesan = ['excel.required' => 'File excel harus diisi'];
$validasi = Validator::make($request->all(), $rules, $pesan);
if ($validasi->fails()) {
return Redirect::back()->withErrors($validasi)->withInput();
}
$excel = $request->file('excel');
//ambil sheet pertama
$excels = Excel::selectSheetsByIndex(0)->load($excel, function ($reader) {
//options jika ada
})->get();
//digunakan untuk menghitung total siswa yang masuk
$counter = 0;
$rowRules = ['no_peserta' => 'required', 'nisn' => 'required', 'nama' => 'required', 'jk' => 'required', 'rombel' => 'required'];
foreach ($excels as $row) {
//membuat validasi untuk row di excel
//jangan lupa mengubah $row menjadi array
$validasi = Validator::make($row->toArray(), $rowRules);
//skip baris ini jika tidak valid, langsung ke baris berikutnya
if ($validasi->fails()) {
continue;
}
$cek = Siswa::where('nis', $row['nisn'])->count();
if ($cek > 0) {
continue;
}
$pass = mt_rand() . '@';
$data = array('nis' => $row['nisn'], 'nama' => $row['nama'], 'kd_kelas' => $row['rombel'], 'jk' => $row['jk'], 'tmp_lahir' => $row['tempat'], 'tgl_lahir' => $row['tanggal'], 'nik' => $row['nik'], 'agama' => $row['agama'], 'alamat' => $row['alamat'], 'rt' => $row['rt'], 'rw' => $row['rw'], 'dusun' => $row['dusun'], 'kelurahan' => $row['kelurahan'], 'kecamatan' => $row['kecamatan'], 'kode_pos' => $row['kode_pos'], 'no_skhun' => $row['skhun'], 'nm_ayah' => $row['ayah'], 'nm_ibu' => $row['ibu'], 'no_peserta' => $row['no_peserta'], 'password' => Hash::make($pass), 'password_asli' => $pass, 'status' => 0);
DB::table('siswa')->insert($data);
$counter++;
}
Session::flash('pesan', "Berhasil mengimport " . $counter . " siswa");
return Redirect::to('admin/siswa');
}
public function import(Request $request)
{
exit;
$path = $request->input('path');
set_time_limit(600);
// Excel::filter('chunk')->selectSheetsByIndex(0)->load($path)->noHeading()->chunk(1000, function($result){
// $data = $result->toArray();
// unset($data[0]);
// if ($data) {
// foreach ($data as $k=>$v) {
// $in[] = array(
// 'user_name'=>$v[0],
// 'sno'=>$v[1],
// 'gender'=>$v[2],
// 'id_card'=>$v[3],
// 'birthday'=>$v[4],
// 'nation'=>$v[5],
// 'grade'=>$v[6],
// 'degree'=>$v[7],
// 'major'=>$v[8],
// 'phone'=>$v[9],
// 'learn_center'=>$v[10],
// 'roll_status'=>$v[11],
// 'account_status'=>$v[12],
// 'roll_filled'=>$v[13],
// 'graduation_filled'=>$v[14],
// 'degree_filled'=>$v[15],
// 'fee'=>$v[16],
// 'fee_ed'=>$v[17],
// 'fee_status'=>$v[18],
// 'teaching_way'=>$v[19],
// 'ordered'=>$v[20],
// );
//// $in[] = array(
//// 'user_name'=>$v[0],
//// 'sno'=>$v[1],
//// 'gender'=>$v[2],
//// 'id_card'=>$v[3],
//// 'class_name'=>$v[4],
//// 'grade'=>$v[5],
//// 'major'=>$v[6],
//// 'school'=>$v[7],
//// 'site'=>$v[8],
//// 'degree'=>$v[9],
//// 'course_name'=>$v[10],
//// 'note'=>$v[11],
//// 'ordered'=>$v[12],
//// );
// }
// DB::table('t1')->insert($in);
// }
// });
// exit;
Excel::selectSheetsByIndex(0)->load($path, function ($reader) {
$data = $reader->noHeading()->toArray();
unset($data[0]);
if ($data) {
foreach ($data as $k => $v) {
$in[] = array('user_name' => $v[0], 'sno' => $v[1], 'gender' => $v[2], 'id_card' => $v[3], 'birthday' => $v[4], 'nation' => $v[5], 'grade' => $v[6], 'degree' => $v[7], 'major' => $v[8], 'phone' => $v[9], 'learn_center' => $v[10], 'roll_status' => $v[11], 'account_status' => $v[12], 'roll_filled' => $v[13], 'graduation_filled' => $v[14], 'degree_filled' => $v[15], 'fee' => $v[16], 'fee_ed' => $v[17], 'fee_status' => $v[18], 'teaching_way' => $v[19], 'ordered' => $v[20]);
// $in[] = array(
// 'user_name'=>$v[0],
// 'sno'=>$v[1],
// 'gender'=>$v[2],
// 'id_card'=>$v[3],
// 'class_name'=>$v[4],
// 'grade'=>$v[5],
// 'major'=>$v[6],
// 'school'=>$v[7],
// 'site'=>$v[8],
// 'degree'=>$v[9],
// 'course_name'=>$v[10],
// 'note'=>$v[11],
// 'ordered'=>$v[12],
// );
}
DB::table('t1')->insert($in);
}
// DB::table('t2')->insert($reader->toArray());
});
exit;
Excel::filter('chunk')->selectSheetsByIndex(0)->load($path)->chunk(500, function ($results) {
// DB::enableQueryLog();
// $results = $results->slice(1);
foreach ($results->toArray() as $v) {
print_r($v);
}
// DB::table('t2')->insert($results->toArray());
// exit;
// foreach ($results as $k=>$v) {
//// $data = $v->toArray();//->values()
//
// $in[] = array(
// 'user_name'=>$v[0],
// 'sno'=>$v[1],
// 'gender'=>$v[2],
// 'id_card'=>$v[3],
// 'class_name'=>$v[4],
// 'grade'=>$v[5],
// 'major'=>$v[6],
// 'school'=>$v[7],
// 'site'=>$v[8],
// 'degree'=>$v[9],
// 'course_name'=>$v[10],
// 'note'=>$v[11],
// 'ordered'=>$v[12],
// );
//
//// //t3
//// $in[] = array(
//// 'degree'=>$v[1],
//// 'major'=>$v[2],
//// 'admit'=>$v[3],
//// 'user_name'=>$v[4],
//// 'sno'=>$v[5],
//// 'phone'=>$v[6],
//// 'id_card'=>$v[8],
//// 'gender'=>$v[9],
//// 'grade'=>$v[10],
//// 'ordered'=>$v[11],
//// );
//
//
//
// }
// var_dump($in);
// DB::table('t2')->insert($in);
});
// var_dump(DB::getQueryLog());
}
protected function injectInsertAndUpdateData()
{
return Excel::selectSheetsByIndex(0)->filter('chunk')->load($this->callback->getData()['realpath'])->skip(HoneyBaby::SKIP_LARAVEL_EXCEL_CHUNK_BUG_INDEX)->chunk(HoneyBaby::CHUNK_SIZE, $this->getChunkProcess());
}
public function postUpload(Request $request)
{
//return dd($request->all());
if (!$request->hasFile('file') || !$request->file('file')->isValid()) {
return redirect('/remittance/philhealth')->withErrors('File is corrupted! Try again.');
}
if (strtolower($request->file->getClientOriginalExtension()) !== 'xls') {
return redirect('/remittance/philhealth')->withErrors('Invalid file extension! Use .xls file extension.');
}
$filename = $request->file->getClientOriginalName();
$destinationPath = public_path('uploads' . DS . 'remittance');
$request->file->move($destinationPath, $filename);
$file = $destinationPath . DS . $filename;
try {
$e = Excel::selectSheetsByIndex(0)->load($file);
} catch (\Exception $e) {
return redirect('/remittance/philhealth')->withErrors('Something went wrong! ' . $e->getMessage());
}
try {
$company = $this->company->find($request->input('company_id'));
} catch (\Exception $e) {
return redirect('/remittance/philhealth')->withErrors('Invalid company!');
}
if (is_null($company)) {
return redirect('/remittance/philhealth')->withErrors('Company not found!');
}
$fields = ['phealth_no', 'bracket', 'e_status', 'date_hired', 'birthday'];
$rs = $e->select($fields)->get();
$data = [];
$data[0] = ['MEM_PHIC_NO', 'MO_SAL', 'EE_STAT', 'EFF_DATE', 'DATE_OF_BIRTH'];
foreach ($rs as $key => $row) {
$idx = $key + 1;
foreach ($fields as $key => $value) {
switch ($value) {
case 'e_status':
if ($row->{$value} == 'NH') {
$data[$idx][$key] = 'NH';
$data[$idx][$key + 1] = $row->date_hired->format('Y-m-d');
} else {
$data[$idx][$key] = 'A';
$data[$idx][$key + 1] = null;
}
break;
case 'date_hired':
break;
default:
$data[$idx][$key] = $row->{$value};
break;
}
}
}
$month = Carbon::parse($request->input('date'))->endOfMonth();
$fname = 'PLH-' . strtoupper($company->code) . '-' . $month->format('Ym');
try {
Excel::create($fname, function ($excel) use($fname, $data) {
$excel->sheet($fname, function ($sheet) use($data) {
$sheet->fromArray($data);
});
})->store('csv', public_path('downloads' . DS . 'remittance'));
} catch (\Exception $e) {
return redirect('/remittance/philhealth')->withErrors('Something went wrong! ' . $e->getMessage());
}
$companies = $this->company->all(['code', 'descriptor', 'id']);
$dl = public_path('downloads' . DS . 'remittance') . DS . $fname . '.csv';
if (File::exists($dl)) {
return redirect('remittance/philhealth')->with('companies', $companies)->with('dl', $fname . '.csv');
} else {
return redirect('remittance/philhealth')->with('companies', $companies)->withErrors('Something went wrong!');
}
}
