public function fileImport($filePath)
{
header('Content-Type: text/html; charset=UTF-8');
// Call benefiter table in db
$file_import_Fields = ['id', 'folder_number', 'name', 'lastname', 'fathers_name', 'mothers_name', 'gender', 'origin_country', 'nationality_country', 'birth_date', 'arrival_date', 'address', 'telephone', 'marital_status', 'number_of_children', 'relatives_residence', 'legal_status', 'education', 'language', 'language_level', 'is_benefiter_working', 'work_title', 'working_legally', 'country_abandon_reason', 'travel_route', 'travel_duration', 'detention_duration'];
// get max id in File_import_schema so that it won't try to insert benefiters already inserted via file
$maxIdInFileImportSchema = File_import_schema::max('id');
if ($maxIdInFileImportSchema == null) {
$maxIdInFileImportSchema = 0;
}
// Import csv
$csvFile = file($filePath);
// Iterate between all rows of the csv file and add each value to the benefiters table
for ($i = 1; $i < count($csvFile); $i++) {
$colValues = str_getcsv($csvFile[$i]);
$file_import = new File_import_schema();
for ($j = 1; $j < count($colValues); $j++) {
if ($j != count($colValues) - 1) {
$file_import->{$file_import_Fields}[$j] = $colValues[$j];
}
}
try {
$file_import->save();
} catch (\Exception $e) {
array_push($this->__errors, \Lang::get('upload_file_errors.import_csv_row_error1') . $file_import->folder_number . \Lang::get('upload_file_errors.import_csv_row_error2'));
Log::error($e);
}
}
\DB::insert(\DB::raw('insert into work_title_list_lookup (work_title) select distinct f.work_title from File_Import_Schema f left outer join work_title_list_lookup work_title on f.work_title = work_title.work_title where work_title.id is null;'));
$this->conversionForFile = new ConversionsForFileUpload();
$this->selectAppropriateDBTableForEachFileRowColumns($maxIdInFileImportSchema);
return $this->__errors;
}
public function update($input)
{
foreach ($input as $id => $value) {
$data['sent_at'] = DB::raw('NOW()');
if ($value == 1) {
$this->invite->update($id, $data);
$invite = $this->inviteService->get($id);
$emaildata['code'] = $invite->code;
$emaildata['email'] = $invite->email;
$this->mailService->sendInvite($emaildata);
}
}
return true;
}
/**
* Insert new Match
*
* @param array $data
* @return Participant
*/
public function create_match(array $data)
{
$players = Player::select('player_id', 'first_name', 'last_name', \DB::raw('CONCAT(first_name, " ", last_name) as full_name'))->lists('player_id', 'full_name');
//check to see if players exist in Players table
$err = '';
if (!array_key_exists($data['player1'], $players)) {
$err .= ' Not in Players table: ' . $data['player1'];
}
if (!array_key_exists($data['player2'], $players)) {
$err .= ' Not in Players table: ' . $data['player2'];
}
if ($err == '') {
$player1_id = $players[$data['player1']];
$player2_id = $players[$data['player2']];
$match = \DB::table('matches')->where('tournament_id', '=', $data['tournament_id'])->where('player1_id', '=', $player1_id)->where('player2_id', '=', $player2_id)->first();
if (is_null($match)) {
$match = Match::create(['player1_id' => $player1_id, 'player2_id' => $player2_id, 'winner_id' => $player1_id, 'tournament_id' => $data['tournament_id'], 'match_date' => $data['match_date'], 'match_type' => $data['match_type'], 'match_division' => $data['match_division']]);
}
}
}
public function getReportDataForRegisteredBenefiters()
{
try {
// get benefiter registration number for any particular month.
$benefitersCount = \DB::select(\DB::raw("select date_format(created_at, '%Y-%m') as created_at, count(id) as idcounter from benefiters group by date_format(created_at, '%Y %m')"));
// $benefitersCount = \DB::select(\DB::raw("select created_at, count(id) as idcounter from benefiters group by year(created_at), month(created_at)"));
} catch (\Exception $e) {
Log::error("A problem occured while trying to count the users based on registration date.\n" . $e);
return null;
}
Log::info("Returning result with users based on their registration date.");
return $benefitersCount;
}
private function getDoctorIdFromName($doctorName)
{
$tmp = \DB::select(\DB::raw('select id from users where (user_role_id = 1 or user_role_id = 2) and (lastname like "%' . $doctorName . '%" or name like "%' . $doctorName . '%")'));
if ($tmp != null) {
Log::info("Returning the doctors ids.");
return $tmp;
} else {
Log::error("Couldn't find the doctor's id");
return null;
}
}
private function getAllBenefitersForCsvFileDownload()
{
$queryString = "select b.*, gl.gender, msl.marital_status_title, el.education_title, wll.description " . "as legal_working_status, wtll.work_title, " . "carl.description as country_abandon_reason from benefiters as b " . "left join benefiters_legal_status as bls on b.id = bls.benefiter_id left join genders_lookup as gl " . "on b.gender_id = gl.id left join marital_status_lookup as msl on b.marital_status_id = msl.id " . "left join education_lookup as el on b.education_id = el.id left join working_legally_lookup as wll " . "on b.working_legally = wll.id left join work_title_list_lookup as wtll on b.work_title_id = wtll.id " . "left join country_abandon_reasons_lookup as carl on b.country_abandon_reason_id = carl.id " . "where deleted_at is null group by b.id";
return \DB::select(\DB::raw($queryString));
}
