public function getGraduatesByInstitutionID($institution_id)
{
$result = Graduate::select(DB::raw('
sum(regular_level1_male) as reg_level1_male,
sum(regular_level1_female) as reg_level1_female,
sum(regular_level1_male) + sum(regular_level1_female) as reg_level1_total,
sum(regular_level2_male) as reg_level2_male,
sum(regular_level2_female) as reg_level2_female,
sum(regular_level2_male) + sum(regular_level2_female) as reg_level2_total,
sum(regular_level3_male) as reg_level3_male,
sum(regular_level3_female) as reg_level3_female,
sum(regular_level3_male) + sum(regular_level3_female) as reg_level3_total,
sum(regular_level4_male) as reg_level4_male,
sum(regular_level4_female) as reg_level4_female,
sum(regular_level4_male) + sum(regular_level4_female) as reg_level4_total,
sum(regular_level5_male) as reg_level5_male,
sum(regular_level5_female) as reg_level5_female,
sum(regular_level5_male) + sum(regular_level5_female) as reg_level5_total,
sum(extension_level1_male) as ext_level1_male,
sum(extension_level1_female) as ext_level1_female,
sum(extension_level1_male) + sum(extension_level1_female) as ext_level1_total,
sum(extension_level2_male) as ext_level2_male,
sum(extension_level2_female) as ext_level2_female,
sum(extension_level2_male) + sum(extension_level2_female) as ext_level2_total,
sum(extension_level3_male) as ext_level3_male,
sum(extension_level3_female) as ext_level3_female,
sum(extension_level3_male) + sum(extension_level3_female) as ext_level3_total,
sum(extension_level4_male) as ext_level4_male,
sum(extension_level4_female) as ext_level4_female,
sum(extension_level4_male) + sum(extension_level4_female) as ext_level4_total,
sum(extension_level5_male) as ext_level5_male,
sum(extension_level5_female) as ext_level5_female,
sum(extension_level5_male) + sum(extension_level5_female) as ext_level5_total'))->where('report_date_id', $this->report_date_id)->where('institution_id', $institution_id)->get();
return $result;
}
public function getExtensionGraduatesByOccupation($occupation_id)
{
$result = Graduate::select(DB::raw('
sum(extension_level1_male) as ext_level1_male,
sum(extension_level1_female) as ext_level1_female,
sum(extension_level1_male) + sum(extension_level1_female) as ext_level1_total,
sum(extension_level2_male) as ext_level2_male,
sum(extension_level2_female) as ext_level2_female,
sum(extension_level2_male) + sum(extension_level2_female) as ext_level2_total,
sum(extension_level3_male) as ext_level3_male,
sum(extension_level3_female) as ext_level3_female,
sum(extension_level3_male) + sum(extension_level3_female) as ext_level3_total,
sum(extension_level4_male) as ext_level4_male,
sum(extension_level4_female) as ext_level4_female,
sum(extension_level4_male) + sum(extension_level4_female) as ext_level4_total,
sum(extension_level5_male) as ext_level5_male,
sum(extension_level5_female) as ext_level5_female,
sum(extension_level5_male) + sum(extension_level5_female) as ext_level5_total'))->where('report_date_id', $this->report_date_id)->where('occupation_id', $occupation_id)->whereIn('institution_id', Institution::select('id')->where('region_id', $this->region_id)->where('ownership', 'Public')->lists('id'))->get();
return $result;
}
public function saveAs(Request $request)
{
$user = Auth::user();
$criteria = array('report_date_id' => $request->report_date_id_target, 'institution_id' => $user->institution->id);
$target_report_id_exist = Graduate::where($criteria)->get();
if (count($target_report_id_exist) > 0) {
// send a flash message
$request->session()->flash('alert-danger', 'Save-as report operation failed. Target report already exists.');
return redirect('trainees-graduates');
}
// retrieve all records as collection
$records = Graduate::select('report_date_id', 'institution_id', 'occupation_id', 'regular_level1_male', 'regular_level1_female', 'regular_level2_male', 'regular_level2_female', 'regular_level3_male', 'regular_level3_female', 'regular_level4_male', 'regular_level4_female', 'regular_level5_male', 'regular_level5_female', 'extension_level1_male', 'extension_level1_female', 'extension_level2_male', 'extension_level2_female', 'extension_level3_male', 'extension_level3_female', 'extension_level4_male', 'extension_level4_female', 'extension_level5_male', 'extension_level5_female')->where('report_date_id', $request->report_date_id_source)->get();
if (count($records) > 0) {
// update report date id to target report date id
foreach ($records as $rec) {
$rec->report_date_id = $request->report_date_id_target;
}
// insert into the table
Graduate::insert($records->toArray());
// send a flash message
$request->session()->flash('alert-success', 'Save as operation was successful!');
} else {
// send a flash message
$request->session()->flash('alert-danger', 'Save as operation failed! No records found.');
}
return redirect('trainees-graduates');
}
public function getGraduateddownload(Request $request)
{
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
if (!$request->has('filterRules')) {
return;
}
$filterRules = $request->has("filterRules") ? Input::get("filterRules") : null;
$filterRules = urldecode($filterRules);
$filterRules = json_decode($filterRules, 1);
$pages = Graduate::where('id', '>', 0);
if (!is_null($filterRules)) {
foreach ($filterRules as $key => $value) {
if ($value) {
$pages = $pages->where($key, 'like', '%' . $value . '%', "and");
}
}
}
if ($request->has('table') && $request->get('table')) {
$stu = json_decode(json_encode($pages->get()), true);
} else {
$stu = $pages->get()->toArray();
}
$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;
PHPExcel_Settings::setCacheStorageMethod($cacheMethod);
$date = "筛选导出毕业生";
$objPHPExcel = new PHPExcel();
$objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle($date . '毕业生名单');
$data = array('序号', '类别', '国籍', '学号', '中文姓名', '英文姓名', 'CSC登记号', '护照号码', '性别', '出生日期', '学院', '专业', '入学日期', '原班级', '现班级', '授课语言', '费用', '住址', '联系方式', 'E-MAIL地址', '导师', '休学年数', '最后修改人');
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A1');
$data = array();
$num = 1;
foreach ($stu as $item) {
$data[$num - 1][0] = $num;
$data[$num - 1][1] = $item['type'];
$data[$num - 1][2] = $item['nationality'];
$data[$num - 1][3] = $item['id'];
$data[$num - 1][4] = $item['name_cn'];
$data[$num - 1][5] = $item['name_en'];
$data[$num - 1][6] = $item['csc_no'];
$data[$num - 1][7] = $item['passport_id'];
$data[$num - 1][8] = $item['gender'];
$data[$num - 1][9] = $item['birthday'];
$data[$num - 1][10] = $item['school'];
$data[$num - 1][11] = $item['major'];
$data[$num - 1][12] = $item['admission_date'];
$data[$num - 1][13] = $item['old_class'];
$data[$num - 1][14] = $item['new_class'];
$data[$num - 1][15] = $item['language'];
$data[$num - 1][16] = $item['fee_type'];
$data[$num - 1][17] = $item['graduated_address'];
$data[$num - 1][18] = $item['telephone'];
$data[$num - 1][19] = $item['email'];
$data[$num - 1][20] = $item['tutor'];
$data[$num - 1][21] = $item['suspend_years'];
$data[$num - 1][22] = $item['last_modify_by'];
$num++;
}
$objPHPExcel->getActiveSheet()->fromArray($data, NULL, 'A2');
$objPHPExcel->setActiveSheetIndex(0);
$excel_name = '筛选导出数据@' . date('Y-m-d H:i:s') . ".xls";
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Type: application/vnd.ms-excel");
header('Content-Disposition: attachment;filename="' . urlencode($excel_name) . '"');
$objWriter->save("php://output");
$objPHPExcel->disconnectWorksheets();
unset($objPHPExcel);
return;
}
public function show_report2_non_government(Request $request)
{
$user = Auth::user();
$region = Region::findOrFail($user->region_id);
$report_date = ReportDate::findOrFail($request->get('petsa'));
$rpt2 = new RTAReport2NonGovernment($report_date->id, $user->region_id);
switch ($request->get('report')) {
case 0:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', NewEnrollee::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_1', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
case 1:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', ReEnrollee::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_2', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
case 2:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', Transferee::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_3', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
case 3:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', Graduate::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_4', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
case 4:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', ShortTermTrainee::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_5', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
case 5:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', DropoutTransferee::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_6', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
case 6:
$institutions = Institution::where('region_id', $region->id)->where('ownership', '!=', 'Public')->whereIn('id', DropoutGraduate::where('report_date_id', $request->get('petsa'))->lists('institution_id'))->orderBy('name')->get();
return view('rtaadmin.show_rpt2nongov_7', compact('rpt2', 'region', 'report_date', 'institutions'));
break;
}
}
public function getDeleteinfo(Request $request)
{
if ($request->has("id")) {
$stu = Graduate::find(Input::get("id"));
if ($stu) {
$stu->delete();
$response = array('status' => ['error' => 0, 'note' => '删除成功'], 'influence' => 1);
return json_encode($response);
} else {
//未找到stu_id
$response = array('status' => ['error' => 1, 'note' => '未找到id'], 'influence' => 0);
return json_encode($response);
}
} else {
//没有stu_id参数
$response = array('status' => ['error' => -1, 'note' => '需要id'], 'influence' => 0);
return json_encode($response);
}
}
