package cn.hmsoft.skill.school.control;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import cn.hmsoft.application.web.Ajax;
import cn.hmsoft.frame.exception.BusinessException;
import cn.hmsoft.helper.excel.Excel2007Writer;
import cn.hmsoft.helper.excel.ExcelReaderHelper;
import cn.hmsoft.skill.constants.SkillLogType;
import cn.hmsoft.skill.data.dao.school.SchoolLogDao;
import cn.hmsoft.skill.data.dao.std.StdEnrolDao;
import cn.hmsoft.skill.data.model.school.SchoolReg;
import cn.hmsoft.skill.data.model.std.StdEnrolSubject;
import cn.hmsoft.skill.helper.SkillParamHelper;
import cn.hmsoft.skill.school.service.SchoolScoreService;

@RestController
public class SchoolScoreControl extends SchoolAbstractControl {
	@Autowired
	private SchoolScoreService sScore;

	@Autowired
	private StdEnrolDao daoEnrol;
	@Autowired
	private SchoolLogDao daoLog;

	@RequestMapping("/school/school/score/init")
	public Ajax init() {
		String sql = "select count(1) from std_enrol where school_id=? and std_score is not null";
		return new Ajax(this.daoEnrol.find(Integer.class, sql, this.getSchool().getSchool_id()));

	}

	@RequestMapping("/school/school/score/upload")
	public Ajax upload(MultipartFile file) {
		SchoolReg school = this.getSchool();
		List<List<String>> list = null;
		try {
			list = ExcelReaderHelper.readSheet(file.getOriginalFilename(), file.getInputStream(), 1,
					6 + SkillParamHelper.MaxExamSubjectCount * 5);
		} catch (Exception e) {
		}

		if (list != null && list.size() > 0) {
			this.sScore.upload(list, school.getSchool_id());
		} else
			throw new BusinessException("在上传的文件中未找到有效的数据！");
		return new Ajax();

	}

	@RequestMapping("/school/school/score/download")
	public void download() {
		SchoolReg school = this.getSchool();
		String sql = "select s.cert_id,s.std_name,e.*,a.aspect_name from std_reg s,std_enrol e,school_aspect a where a.aspect_id=e.aspect_id and s.std_id=e.std_id and e.school_id=? and e.std_score is not null order by ticket_num";
		List<Map<String, Object>> list = this.daoEnrol.listMapBySql(sql, school.getSchool_id());
		Map<String, Integer> vMap = Excel2007Writer.createTitleMap("#", 2);
		vMap.put("准考证号", 10);
		vMap.put("身份证号", 18);
		vMap.put("考生姓名", 10);
		vMap.put("报考专业", 20);
		vMap.put("专业成绩", 15);
		vMap.put("专业排名", 6);
		sql = "select max(subject_order) from std_enrol_subject sb where sb.school_id=?";
		int subject_count = this.daoEnrol.findObject(Integer.class, sql, school.getSchool_id());
		for (int i = 0; i < subject_count; i++) {
			vMap.put("科目" + (i + 1) + "名称", 15);
			vMap.put("科目" + (i + 1) + "成绩", 15);
		}

		List<List<Object>> values = new ArrayList<List<Object>>();

		int index = 1;
		sql = "select * from std_enrol_subject sb where aspect_id=? and std_id=? order by subject_order";
		for (Map<String, Object> map : list) {
			List<Object> array = new ArrayList<Object>();
			array.add(++index);
			array.add(map.get("ticket_num"));
			array.add(map.get("cert_id"));
			array.add(map.get("std_name"));
			array.add(map.get("aspect_name"));
			array.add(map.get("std_score"));
			array.add(map.get("std_level"));
			for (StdEnrolSubject sb : this.daoEnrol.listBySql(StdEnrolSubject.class, sql, (int) map.get("aspect_id"),
					(int) map.get("std_id"))) {
				array.add(sb.getSubject_name());
				array.add(sb.getStd_subject_score());
			}
			values.add(array);

		}
		this.daoLog.log(school.getSchool_id(), SkillLogType.DownloadScore.toString(), values.size() + "");
		Excel2007Writer.downloadAsExcel(this.getResponse(), this.getSchool().getSchool_name() + "-考生成绩", values,
				vMap);

	}
}
