function excel($postdata)
{
require_once 'Excel_reader.php';
require_once 'Classes/PHPExcel.php';
$tmp = $postdata;
$save_path = "./";
//暂时上传的文件路径
$file_name = $save_path . date('Ymdhis') . ".xls";
//上传后的文件保存路径和名称
if (copy($tmp, $file_name)) {
}
$PHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007();
//为了可以读取所有版本Excel文件
if (!$PHPReader->canRead($file_name)) {
$PHPReader = new PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($file_name)) {
echo '未发现Excel文件！';
return;
}
}
//不需要读取整个Excel文件而获取所有工作表数组的函数，感觉这个函数很有用，找了半天才找到
$sheetNames = $PHPReader->listWorksheetNames($file_name);
//读取Excel文件
$PHPExcel = $PHPReader->load($file_name);
//获取工作表的数目
$sheetCount = $PHPExcel->getSheetCount();
for ($i = 0; $i < $sheetCount; $i++) {
//选择第一个工作表
$currentSheet = $PHPExcel->getSheet($i);
//取得一共有多少列
$allColumn = $currentSheet->getHighestColumn();
//取得一共有多少行
$allRow = $currentSheet->getHighestRow();
for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
$address = $currentColumn . $currentRow;
$data[$i][$currentRow][$currentColumn] = $currentSheet->getCell($address)->getValue();
//echo ."\t";
}
}
//$data[$i]=$currentSheet;
}
unlink($file_name);
//删除上传的文件
return $data;
}
public function index()
{
// $xq_list = $this->manage_model->get_all_xiaoqu_list();
// foreach ($xq_list as $xq) {
// $name = str_replace("•","",$xq['name']);
// //echo $xq['id'] . ": " . $this->encode($name) . '<br>';
// $xq['jianpin'] = $this->encode($name);
// $this->manage_model->update_xiaoqu_jianpin($xq);
// }
// die('DONE');
require_once APPPATH . 'libraries/PHPExcel/PHPExcel.php';
$PHPExcel = new \PHPExcel();
$PHPReader = new \PHPExcel_Reader_Excel2007();
$file = 'E:/broker.xlsx';
//为了可以读取所有版本Excel文件
if (!$PHPReader->canRead($file)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($file)) {
echo '未发现Excel文件！';
return;
}
}
//不需要读取整个Excel文件而获取所有工作表数组的函数，感觉这个函数很有用，找了半天才找到
$sheetNames = $PHPReader->listWorksheetNames($file);
//读取Excel文件
$PHPExcel = $PHPReader->load($file);
//获取工作表的数目
$sheetCount = $PHPExcel->getSheetCount();
//选择第一个工作表
$currentSheet = $PHPExcel->getSheet(0);
//取得一共有多少列
$allColumn = $currentSheet->getHighestColumn();
//取得一共有多少行
$allRow = $currentSheet->getHighestRow();
//循环读取数据，默认编码是utf8，这里转换成gbk输出
$data = array();
for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {
$name = trim($currentSheet->getCell("A" . $currentRow)->getValue());
$tel = trim($currentSheet->getCell("B" . $currentRow)->getValue());
$data[] = array('username' => $tel, 'passwd' => sha1('888888'), 'tel' => $tel, 'company_name' => "顺达地产", 'rel_name' => $name, 'region_id' => 6, 'admin_group' => 2);
}
$this->manage_model->add_broker_batch($data);
}
private function _phydata($fileinfo, $ac = 'phydata')
{
//import('Think.Cache');
//$Cache = \Cache::getInstance();
//查看是否截止上报
$school_year_info = D('SchoolYear')->get_info($this->school_year);
if (empty($school_year_info) || $school_year_info['state'] == 207020) {
$this->ajaxReturn(array('errno' => 101, 'errtitle' => '您选择的学年' . $this->school_year . '未开始,无法录入'));
}
//审核状态
$userinfo = session('userinfo');
$dictList = session('dictList');
//非补录数据需要验证上报状态
if ($ac != 'historyPhydata') {
if (!empty($school_year_info['not_upload_time']) && time() > strtotime($school_year_info['not_upload_time'])) {
$this->ajaxReturn(array('errno' => 102, 'errtitle' => '数据上报截止时间为' . $school_year_info['not_upload_time']));
}
//朝阳区功能
//if($userinfo['org_id'] == 110105){
// $this->ajaxReturn(array('errno'=>109,'errtitle'=>'功能暂未开放!'));
//}
if ($userinfo['org_id'] != 110105 && $userinfo['user_kind'] != '109010') {
$s_status = D('SchoolStatus')->get_status_info_one($this->school_year, $this->school_code);
if ($s_status['s_status'] == 206020 || $s_status['s_status'] == 206030) {
$this->ajaxReturn(array('errno' => 109, 'errtitle' => '您当前上报状态为' . $dictList['206'][$s_status['s_status']]['dict_name'] . ',如需重新上报或修改请等待区县撤销！'));
}
}
}
//读取excel文件内容
$fPath = '/Upload/' . $fileinfo['info']['savepath'] . $fileinfo['info']['savename'];
//文件大小限制
$filesize = filesize($_SERVER['DOCUMENT_ROOT'] . $fPath);
if ($filesize > 2097152) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
$this->ajaxReturn(array('errno' => 111, 'errtitle' => '请不要上传超过2M的文件！'));
}
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.IOFactory");
$reader = \PHPExcel_IOFactory::createReader('Excel2007');
$PHPExcel = \PHPExcel_IOFactory::load($_SERVER['DOCUMENT_ROOT'] . $fPath);
$reader->setReadDataOnly(true);
$PHPReader = new \PHPExcel_Reader_Excel2007();
//为了可以读取所有版本Excel文件
if (!$PHPReader->canRead($_SERVER['DOCUMENT_ROOT'] . $fPath)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($_SERVER['DOCUMENT_ROOT'] . $fPath)) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
$this->ajaxReturn(array('errno' => 104, 'errtitle' => '未发现excel文件!'));
}
}
////不需要读取整个Excel文件而获取所有工作表数组的函数
$sheetNames = $PHPReader->listWorksheetNames($_SERVER['DOCUMENT_ROOT'] . $fPath);
if ($userinfo['org_id'] != 110105) {
$sheetNames = array($this->school_code);
} else {
if (empty($sheetNames)) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
$this->ajaxReturn(array('errno' => 1115, 'errtitle' => 'excel内容为空!'));
}
}
/*表头*/
$key_base = array('grade_num', 'class_num', 'class_name', 'country_education_id', 'folk_code', 'name', 'sex', 'birthday', 'address');
$titContent = array('body_height' => '身高', 'body_weight' => '体重', 'vital_capacity' => '肺活量', 'wsm' => '50米跑', 'ldty' => '立定跳远', 'zwtqq' => '坐位体前屈', 'bbm_nv' => '800米跑（女）', 'yqm_nan' => '1000米跑（男）', 'ywqz_nv' => '一分钟仰卧起坐（女）', 'ytxs_nan' => '引体向上（男）', 'wsmwfp' => '50米×8往返跑', 'yfzts' => '一分钟跳绳', 'ywqz_ytxs' => '一分钟仰卧起坐');
$gradeItem = C('GRADE_ITEM_FIELD');
//判断验证学籍号还是教育ID号
if ($ac == 'phydata2') {
$field = 'education_id';
$fieldTitle = '教育ID号';
} else {
$field = 'country_education_id';
$fieldTitle = '全国学籍号';
}
/*表头end*/
//循环sheet
//记录错误内容
$errorLog = '';
//启动事务
M()->startTrans();
foreach ($sheetNames as $sk => $sname) {
$this->school_code = $sname;
$this->school_id = D('School')->get_list_by_schoolcode_year($this->school_code, $this->school_year, 'one');
if (empty($this->school_id)) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
$this->ajaxReturn(array('errno' => 1116, 'errtitle' => 'sheet名称为' . $sname . '的学校代码错误,找不到该学校'));
break;
}
$this->school_id = $this->school_id['school_id'];
$schoolids[] = $this->school_id;
/*读取每一页内容*/
$sheet = $PHPExcel->getSheet($sk);
//sheet1
$highestRow = $sheet->getHighestRow();
//总行数
$highestColumn = $sheet->getHighestColumn();
//总列数,字母表示
//判断是否空文件
if ($highestRow <= 1) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
$this->ajaxReturn(array('errno' => 103, 'errtitle' => '您上传的文件没有内容!'));
}
//将导入记录插入到导入历史表
//上传状态为初始状态204010 上传中
//is_error 初始状态为正确，待数据校验完毕后更改该列值
$importLogData = array('user_id' => $this->school_id, 'year_year' => $this->school_year, 'import_time' => date('Y-m-d H:i:s'), 'file_name' => $fileinfo['info']['name'], 'file_path' => $fPath, 'deal_status' => '204010', 'is_error' => '0', 'town_id' => $this->town_id, 'partition_field' => intval($this->town_id . $this->school_year));
//补录数据
if ($ac == 'historyPhydata') {
$importLogData['is_examine'] = 0;
}
$import_id = D('ImportLog')->add($importLogData);
$importids[] = $import_id;
if (!$import_id) {
//删除文件
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
M()->rollback();
$this->ajaxReturn(array('errno' => 102, 'errtitle' => '保存上传记录失败，请稍候重试！'));
}
//缓存学校学生信息
//$stuinfos = S('base_data_' . $this->school_id);
//if($stuinfos === false){
$stuinfos = D('StudentScore')->get_school_datas($importLogData['partition_field'], $this->school_id, $ac);
// S('base_data_' . $this->school_id,serialize($stuinfos));
//}else{
// $stuinfos = unserialize($stuinfos);
//dump($stuinfos);exit();
//}
$titleArr = array();
//从第九列开始
$columnNum = 9;
for ($column = 'J'; $column <= $highestColumn; $column++) {
$val = $sheet->getCellByColumnAndRow($columnNum, 1)->getValue();
if ($val instanceof PHPExcel_RichText) {
//富文本转换字符串
$val = $val->__toString();
}
$val = trim($val);
if ($val == '身高') {
$titleArr[] = 'body_height';
} elseif ($val == '体重') {
$titleArr[] = 'body_weight';
} elseif ($val == '肺活量') {
$titleArr[] = 'vital_capacity';
} elseif ($val == '50米跑') {
$titleArr[] = 'wsm';
} elseif ($val == '坐位体前屈') {
$titleArr[] = 'zwtqq';
} elseif ($val == '一分钟跳绳') {
$titleArr[] = 'yfzts';
} elseif (strpos($val, '800米跑') !== false) {
$titleArr[] = 'bbm_nv';
} elseif (strpos($val, '1000米跑') !== false) {
$titleArr[] = 'yqm_nan';
} elseif (strpos($val, '一分钟仰卧起坐') !== false) {
$titleArr[] = 'ywqz_ytxs';
} elseif (strpos($val, '引体向上') !== false) {
$titleArr[] = 'ytxs_nan';
} elseif (strpos($val, '立定跳远') !== false) {
$titleArr[] = 'ldty';
} elseif (strpos($val, '往返跑') !== false) {
$titleArr[] = 'wsmwfp';
}
$columnNum++;
}
if (empty($titleArr)) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
M()->rollback();
$this->ajaxReturn(array('errno' => 105, 'errtitle' => '请务必使用本系统或者国家体质检测系统下载的上报模板并不要更改列格式！'));
}
//合并表头
$keys = array_merge($key_base, $titleArr);
$titleCount = count($keys);
$phyData = array();
if (count($sheetNames) > 1) {
$sheetErr = $sname . '页 ';
} else {
$sheetErr = '';
}
for ($row = 2; $row <= $highestRow; $row++) {
//if($row == 100) exit($highestRow);
for ($column = 0; $column < $titleCount; $column++) {
$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
if ($val instanceof PHPExcel_RichText) {
//富文本转换字符串
$val = $val->__toString();
}
$val = trim($val);
$phyData[$row][$keys[$column]] = $val;
}
if ($phyData[$row]['country_education_id'] == '' && $phyData[$row]['name'] == '') {
break;
}
$errLogT1 = '第 ' . $row . ' 行';
$errLogT2 = '';
if ($phyData[$row]['country_education_id'] == '') {
$errLogT2 .= $fieldTitle . '不能为空；';
}
if ($phyData[$row]['name'] == '') {
$errLogT2 .= '姓名不能为空；';
}
$stuinfo = $stuinfos[$phyData[$row]['country_education_id']];
if (empty($stuinfo) || $stuinfo['in_school'] == 0 || $stuinfo['name'] != $phyData[$row]['name']) {
if (empty($stuinfo) || $stuinfo['name'] != $phyData[$row]['name']) {
$errLogT2 .= " 非当前学校数据或者数据格式错误，请核对学生姓名、" . $fieldTitle . "是否有误；";
} elseif ($stuinfo['in_school'] == 0) {
$errLogT2 .= " 当前学生已设置为不在测,如需上报体质信息请设置该生是否在测为'是';";
}
} else {
$realGrade = $phyData[$row]['grade_num'];
if ($stuinfo['school_length54'] == 1) {
switch ($stuinfo['school_grade']) {
case 21:
$realGrade = 16;
break;
case 22:
$realGrade = 21;
break;
case 23:
$realGrade = 22;
break;
case 24:
$realGrade = 23;
break;
default:
$realGrade = $stuinfo['school_grade'];
break;
}
}
if (!in_array($realGrade, array('11', '12', '13', '14', '15', '16', '21', '22', '23', '24', '30', '31', '32', '33', '34'))) {
$errLogT2 .= ' 年级编号错误 ;';
}
if (in_array($realGrade, array('11', '12'))) {
$titles = $gradeItem[11];
} elseif (in_array($realGrade, array('13', '14'))) {
$titles = $gradeItem[13];
} elseif (in_array($realGrade, array('15', '16'))) {
$titles = $gradeItem[15];
} else {
$titles = $gradeItem[21];
}
if (!in_array($phyData[$row]['sex'], array(1, 2))) {
$errLogT2 .= ' 性别列用数字1和2表示 ;';
}
if ($phyData[$row]['sex'] != substr($stuinfo['sex'], 4, 1)) {
$errLogT2 .= ' 学生性别与cmis信息不一致 ;';
}
$titleErr = 0;
foreach ($titles as $va) {
$tmpStr = $phyData[$row][$va];
if ($phyData[$row]['body_height'] == '免体') {
break;
}
if ($realGrade > 16) {
if ($tmpStr == '' && in_array($va, array('body_height', 'body_weight', 'vital_capacity', 'wsm', 'ldty', 'zwtqq'))) {
$titleErr = 1;
} else {
if ($phyData[$row]['sex'] == 2 && $va == 'bbm_nv' && $tmpStr == '') {
$titleErr = 1;
} elseif ($phyData[$row]['sex'] == 2 && $va == 'ywqz_ytxs' && $tmpStr == '') {
$titleErr = 1;
} elseif ($phyData[$row]['sex'] == 1 && $va == 'yqm_nan' && $tmpStr == '') {
$titleErr = 1;
} elseif ($phyData[$row]['sex'] == 1 && $va == 'ytxs_nan' && $tmpStr == '') {
$titleErr = 1;
}
}
} else {
if ($va == 'ywqz_ytxs') {
//$tmpStr=$phyData[$row]['ywqz_ytxs'];
$titleErr = $tmpStr == '' ? 1 : 0;
}
}
if ($titleErr == 1) {
$errLogT2 .= $titContent[$va] . ' 不能为空;';
$titleErr = 0;
continue;
}
if (!is_numeric($tmpStr) && $tmpStr != '') {
if (!preg_match("('|’|”|′|\")", $tmpStr)) {
$errLogT2 .= $titContent[$va] . "[" . $tmpStr . "] 不是有效的体质健康数据，请确认; ";
} else {
$tmpStr = str_replace("’", '′', $tmpStr);
$tmpStr = str_replace("”", '′′', $tmpStr);
$tmpStr = str_replace("'", '′', $tmpStr);
$tmpStr = str_replace("''", '′′', $tmpStr);
$tmpStr = str_replace("\"", '′′', $tmpStr);
}
}
if (!strpos($tmpStr, '′') && !strpos($tmpStr, '′′')) {
$phyData[$row][$va] = floatval($tmpStr);
} else {
//50米跑只能用小数点分隔
if ($va == 'wsm') {
$errLogT2 .= $titContent[$va] . "[" . $tmpStr . "] 必须使用小数点分隔; ";
} else {
$tmpFen = strpos($tmpStr, '′') ? intval(substr($tmpStr, 0, strpos($tmpStr, '′'))) : 0;
$tmpMiao = strpos($tmpStr, '′') ? substr(strstr($tmpStr, '′'), 3, strlen($tmpStr) - 1) : '00';
$tmpMiao = strlen($tmpMiao) == 1 ? '0' . $tmpMiao : $tmpMiao;
$end_result = $tmpFen == 0 ? intval($tmpMiao) : floatval($tmpFen . '.' . $tmpMiao);
if (!$end_result || intval($tmpMiao) > 59) {
$errLogT2 .= " 分秒时间格式错误或者秒大于59，请确认; ";
} else {
$phyData[$row][$va] = $end_result;
}
}
}
}
}
$errLogT3 = ' <br>';
if ($errLogT2 != '') {
$errorLog .= $sheetErr . $errLogT1 . $errLogT2 . $errLogT3;
continue;
}
$data = array('import_id' => $import_id, 'is_error' => 0, 'error_desc' => '', 'excel_num' => $row, 'education_id' => $stuinfo['education_id'], 'country_education_id' => $stuinfo['country_education_id'], 'student_no' => $stuinfo['studentno'], 'grade_num' => str_replace("'", "", $phyData[$row]['grade_num']), 'class_num' => str_replace("'", "", $phyData[$row]['class_num']), 'class_name' => str_replace("'", "", $phyData[$row]['class_name']), 'folk_code' => str_replace("'", "", $stuinfo['folk']), 'name' => $stuinfo['name'], 'sex' => $stuinfo['sex'] == '106020' ? '女' : '男', 'birthday' => date('Y-m-d', strtotime($stuinfo['birthday'])), 'student_source' => $stuinfo['student_source'], 'address' => str_replace("'", "", $phyData[$row]['address']), 'rewards_code' => 0, 'town_id' => $importLogData['town_id'], 'partition_field' => $importLogData['partition_field'], 'year_year' => $importLogData['year_year'], 'import_time' => date('Y-m-d H:i:s'), 'is_avoid' => '否');
//是否为补录数据
if ($ac == 'historyPhydata') {
$data['examine'] = 0;
}
if ($phyData[$row]['body_height'] === '免体') {
$data['is_avoid'] = '是';
} else {
$data['body_height'] = floatval($phyData[$row]['body_height']);
$data['body_weight'] = floatval($phyData[$row]['body_weight']);
$data['vital_capacity'] = floatval($phyData[$row]['vital_capacity']);
$data['wsm'] = floatval($phyData[$row]['wsm']);
$data['ldty'] = floatval($phyData[$row]['ldty']);
$data['zwtqq'] = floatval($phyData[$row]['zwtqq']);
if ($phyData[$row]['wsmwfp'] != '') {
$data['wsmwfp'] = floatval($phyData[$row]['wsmwfp']);
}
if ($phyData[$row]['yfzts'] != '') {
$data['yfzts'] = (int) $phyData[$row]['yfzts'];
}
if ($stuinfo['sex'] == '106020' && $phyData[$row]['bbm_nv'] != '') {
$data['bbm_yqm'] = floatval($phyData[$row]['bbm_nv']);
} elseif ($stuinfo['sex'] == '106010' && $phyData[$row]['yqm_nan'] != '') {
$data['bbm_yqm'] = floatval($phyData[$row]['yqm_nan']);
}
if ($realGrade < 21) {
if ($phyData[$row]['ywqz_ytxs'] != '') {
$data['ywqz_ytxs'] = (int) $phyData[$row]['ywqz_ytxs'];
}
} else {
if ($stuinfo['sex'] == '106020' && $phyData[$row]['ywqz_ytxs'] != '') {
$data['ywqz_ytxs'] = (int) $phyData[$row]['ywqz_ytxs'];
} elseif ($stuinfo['sex'] == '106010' && $phyData[$row]['ytxs_nan'] != '') {
$data['ywqz_ytxs'] = (int) $phyData[$row]['ytxs_nan'];
}
}
}
//$school_length54 = $stuinfo['school_length54'];
$detail_id = D('import_detail_new')->add($data);
if (!$detail_id) {
$errLogT2 .= '添加学生记录失败！';
}
if ($errLogT2 != '') {
$errorLog .= $sheetErr . $errLogT1 . $errLogT2 . $errLogT3;
}
}
///M()->execute("execute sp_check_detail_main");
}
if (!empty($errorLog)) {
@unlink($_SERVER['DOCUMENT_ROOT'] . $fPath);
//回滚
M()->rollback();
$this->ajaxReturn(array('errno' => '109', 'errtitle' => $errorLog));
} else {
//数据上传成功后修改状态为 待校验：204020
$where['import_id'] = array('IN', $importids);
D('ImportLog')->where($where)->setField('deal_status', '204020');
//朝阳区学校自动变更为已上报，无需点击上报按钮
if ($userinfo['org_id'] == 110105 || $userinfo['user_kind'] == 109010) {
$data = array('s_status' => 206020, 'sub_time' => time());
$where['school_id'] = array('IN', $schoolids);
D('SchoolStatus')->where($where)->save($data);
}
//提交
M()->commit();
$this->ajaxReturn(array('errno' => 0, 'errtitle' => '文件上传成功,请等待系统校验...', 'import_id' => implode(',', $importids)));
}
}
