public function getQuery()
{
$query = new Query(Yii::app()->db);
$query->from('groupon_biz');
$query->andWhere('display=' . ARBiz::DISPLAY);
if ($this->id) {
$query->andWhere('id=:id', array(':id' => trim($this->id)));
}
if ($this->title) {
$query->andWhere(array('like', 'title', '%' . $this->title . '%'));
}
if ($this->status) {
$query->andWhere('examine_status=:status', array(':status' => $this->status));
}
$query->order('id desc');
return $query;
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('schedules')->delete();
$db = App::make('AccessDB');
$query = new Query('USER_OF_RUN', $db->get_dbh());
$query->order('STARTDATE');
$result = $query->get('STARTDATE,ENDDATE,USERID,NUM_OF_RUN_ID');
$result_array = [];
foreach ($result as $row) {
$result_array[] = ['start_date' => $row['STARTDATE'], 'end_date' => $row['ENDDATE'], 'employee_id' => $row['USERID'], 'weekly_schedule_id' => $row['NUM_OF_RUN_ID']];
}
$schedule = array_chunk($result_array, 1000);
foreach ($schedule as $value) {
Schedule::insert($value);
}
$convert_file = public_path() . '\\Last Convert.txt';
$record = explode(';', file_get_contents($convert_file));
$record[5] = $result[count($result) - 1]['STARTDATE'];
$file = fopen($convert_file, 'w');
fwrite($file, implode(';', $record));
fclose($file);
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('employees')->delete();
$db = App::make('AccessDB');
$query = new Query('USERINFO', $db->get_dbh());
$query->order('USERID');
$result = $query->get('USERID,SSN,Name,Gender,BIRTHDAY,street,DEFAULTDEPTID');
$result_array = [];
foreach ($result as $row) {
$result_array[] = ['id' => $row['USERID'], 'ssn' => $row['SSN'], 'name' => $row['Name'], 'is_male' => $row['Gender'] == 'Male' ? 1 : 0, 'birthday' => $row['BIRTHDAY'], 'street' => $row['street'], 'department_id' => $row['DEFAULTDEPTID']];
}
$employee = array_chunk($result_array, 1000);
foreach ($employee as $value) {
Employee::insert($value);
}
$convert_file = public_path() . '\\Last Convert.txt';
$record = explode(';', file_get_contents($convert_file));
$record[7] = $result[count($result) - 1]['USERID'];
$file = fopen($convert_file, 'w');
fwrite($file, implode(';', $record));
fclose($file);
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('daily_schedules')->delete();
$db = App::make('AccessDB');
$query = new Query('SCHCLASS', $db->get_dbh());
$query->order('SCHCLASSID');
$result = $query->get('SCHCLASSID,SCHNAME,STARTTIME,ENDTIME');
$result_array = [];
foreach ($result as $row) {
$result_array[] = ['id' => $row['SCHCLASSID'], 'name' => $row['SCHNAME'], 'start_time' => $row['STARTTIME'], 'end_time' => $row['ENDTIME']];
}
$daily_schedule = array_chunk($result_array, 1000);
foreach ($daily_schedule as $value) {
DailySchedule::insert($value);
}
$convert_file = public_path() . '\\Last Convert.txt';
$record = explode(';', file_get_contents($convert_file));
$record[4] = $result[count($result) - 1]['SCHCLASSID'];
$file = fopen($convert_file, 'w');
fwrite($file, implode(';', $record));
fclose($file);
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('manual_checks')->delete();
$db = App::make('AccessDB');
$query = new Query('CHECKEXACT', $db->get_dbh());
$query->where('EXACTID', '>=', 1455);
$query->order('EXACTID');
$result = $query->get('EXACTID,USERID,CHECKTIME,CHECKTYPE');
$result_array = [];
foreach ($result as $row) {
$result_array[] = ['date_time' => $row['CHECKTIME'], 'is_in' => $row['CHECKTYPE'] == 'I' ? 1 : 0, 'employee_id' => $row['USERID']];
}
$manual_check = array_chunk($result_array, 1000);
foreach ($manual_check as $value) {
ManualCheck::insert($value);
}
$convert_file = public_path() . '\\Last Convert.txt';
$record = explode(';', file_get_contents($convert_file));
$record[0] = $result[count($result) - 1]['EXACTID'];
$file = fopen($convert_file, 'w');
fwrite($file, implode(';', $record));
fclose($file);
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('absences')->delete();
$db = App::make('AccessDB');
$query = new Query('USER_SPEDAY', $db->get_dbh());
$query->where('STARTSPECDAY', '>=', '2014-07-01');
$query->order('DATE,STARTSPECDAY');
$result = $query->get();
$result_array = [];
foreach ($result as $row) {
$result_array[] = ['start_date' => $row['STARTSPECDAY'], 'end_date' => $row['ENDSPECDAY'], 'reason' => isset($row['YUANYING']) ? $row['YUANYING'] : '', 'employee_id' => $row['USERID'], 'absence_category_id' => $row['DATEID']];
}
$absence = array_chunk($result_array, 1000);
foreach ($absence as $value) {
Absence::insert($value);
}
$convert_file = public_path() . '\\Last Convert.txt';
$record = explode(';', file_get_contents($convert_file));
$record[6] = $result[count($result) - 1]['DATE'];
$file = fopen($convert_file, 'w');
fwrite($file, implode(';', $record));
fclose($file);
}
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('weekly_schedules')->delete();
$db = App::make('AccessDB');
$query = new Query('NUM_RUN_DEIL', $db->get_dbh());
$query->order('NUM_RUNID');
$result = $query->get('NUM_RUNID,STARTTIME,ENDTIME,SDAYS,EDAYS,SCHCLASSID');
$result_array = [];
foreach ($result as $row) {
$allowance_id = 0;
if ($row['SDAYS'] != 6) {
$end = date_parse($row['ENDTIME']);
$start = date_parse($row['STARTTIME']);
$duration = (mktime($end['hour'], $end['minute']) - mktime($start['hour'], $start['minute'])) / 3600;
if ($duration >= 8) {
$allowance_id = 1;
} else {
if ($duration < 7) {
$allowance_id = 3;
} else {
$allowance_id = 2;
}
}
}
$result_array[] = ['id' => $row['NUM_RUNID'], 'start_day' => $row['SDAYS'], 'end_day' => $row['EDAYS'], 'daily_schedule_id' => $row['SCHCLASSID'], 'allowance_id' => $allowance_id];
}
$weekly_schedule = array_chunk($result_array, 1000);
foreach ($weekly_schedule as $value) {
WeeklySchedule::insert($value);
}
$convert_file = public_path() . '\\Last Convert.txt';
$record = explode(';', file_get_contents($convert_file));
$record[3] = $result[count($result) - 1]['NUM_RUNID'];
$file = fopen($convert_file, 'w');
fwrite($file, implode(';', $record));
fclose($file);
}
/**
* Sorting requirements to a server-side processing query.
* @param Query $query Query instance to apply sorting to
* @param array $http HTTP variables (i.e. GET or POST)
* @private
*/
private function _ssp_sort($query, $http)
{
for ($i = 0; $i < count($http['order']); $i++) {
$order = $http['order'][$i];
$query->order($this->_ssp_field($http, $order['column']) . ' ' . ($order['dir'] === 'asc' ? 'asc' : 'desc'));
}
}
public function userinfo($start)
{
$query = new Query('USERINFO', $this->dbh);
$query->where('USERID', '>', $start);
$query->order('USERID');
$result = $query->get('USERID,SSN,Name,Gender,BIRTHDAY,street,DEFAULTDEPTID');
foreach ($result as $row) {
Employee::create(['id' => $row['USERID'], 'ssn' => $row['SSN'], 'name' => $row['Name'], 'is_male' => $row['Gender'] == 'Male' ? 1 : 0, 'birthday' => $row['BIRTHDAY'], 'street' => $row['street'], 'department_id' => $row['DEFAULTDEPTID']]);
}
$size = count($result);
$this->num_data += $size;
return $size > 0 ? $result[$size - 1]['USERID'] : $start;
}
