wwy_wy/websrc/com/kingdee/eas/custom/wamke/syncdata/web/MobileEntryBillExtendEditHandler.java

package com.kingdee.eas.custom.wamke.syncdata.web;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URL;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.http.HttpEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.util.EntityUtils;
import org.apache.log4j.Logger;
import org.apache.log4j.Priority;
import org.springframework.ui.ModelMap;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.dao.IObjectPK;
import com.kingdee.bos.dao.ormapping.ObjectUuidPK;
import com.kingdee.bos.metadata.entity.EntityViewInfo;
import com.kingdee.bos.metadata.entity.FilterInfo;
import com.kingdee.bos.metadata.entity.FilterItemInfo;
import com.kingdee.eas.base.attachment.AttachmentFactory;
import com.kingdee.eas.base.attachment.AttachmentInfo;
import com.kingdee.eas.base.attachment.BoAttchAssoFactory;
import com.kingdee.eas.base.attachment.IAttachment;
import com.kingdee.eas.base.attachment.IBoAttchAsso;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.custom.utils.HttpUtils;
import com.kingdee.eas.custom.wamke.syncdata.MobileEntryBillInfo;
import com.kingdee.eas.custom.wamke.syncdata.dto.MobileAttachAssembleDto;
import com.kingdee.eas.custom.wamke.syncdata.dto.MobileAttachDto;
import com.kingdee.eas.framework.CoreBaseInfo;
import com.kingdee.eas.util.app.ContextUtil;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.attachment.AttachmentState;
import com.kingdee.shr.attachment.AttachmentTypeEnum;
import com.kingdee.shr.attachment.ISHRAttachmentExt;
import com.kingdee.shr.attachment.SHRAttachmentExtCollection;
import com.kingdee.shr.attachment.SHRAttachmentExtFactory;
import com.kingdee.shr.attachment.SHRAttachmentExtInfo;
import com.kingdee.shr.base.syssetting.app.filter.HRFilterUtils;
import com.kingdee.shr.base.syssetting.context.SHRContext;
import com.kingdee.shr.base.syssetting.exception.SHRWebException;
import com.kingdee.shr.base.syssetting.exception.ShrWebBizException;
import com.kingdee.shr.base.syssetting.web.handler.EditHandler;
import com.kingdee.util.StringUtils;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;


public class MobileEntryBillExtendEditHandler extends EditHandler {

	private static Logger logger = Logger.getLogger(MobileEntryBillExtendEditHandler.class);

	// 重载初始化
	@Override
	public String initalizeAction(HttpServletRequest request,
			HttpServletResponse response, ModelMap modelMap)
	throws SHRWebException {

		logInfo("initalizeAction初始化");
		String operateStatus = getOperateStatus(request, modelMap);
		if (!(StringUtils.isEmpty(operateStatus))) {
			logInfo("初始化得到操作状态为:" + operateStatus);
			if ("ADDNEW".equalsIgnoreCase(operateStatus))
				throw new SHRWebException("不允许创建数据");
			// return addNewAction(request, response, modelMap);
			if ("EDIT".equalsIgnoreCase(operateStatus))
				throw new SHRWebException("不允许编辑数据");
			// return editAction(request, response, modelMap);
			if (("VIEW".equalsIgnoreCase(operateStatus))
					|| ("readonly".equalsIgnoreCase(operateStatus))) {
				return super.viewAction(request, response, modelMap);
			}
		}

		return super.viewAction(request, response, modelMap);
	}

	// 修改保存
	@Override
	protected IObjectPK runSaveData(HttpServletRequest request,
			HttpServletResponse response, CoreBaseInfo model) throws Exception {

		logInfo("runSaveData初化");
		JSONObject jsonObj = JSON.parseObject(request.getParameter("model"));
		
		MobileEntryBillInfo info = (MobileEntryBillInfo) model;
		// 校验是否已存在相面的关联人员，蝶城，负责人类型
		String reAuditStatus = mappingAuditStatus(model.get("reApprovalStatus")
				.toString());
		String auditStatus = mappingAuditStatus(model.get("approvalStatus")
				.toString());

		IObjectPK pk = super.runSaveData(request, response, model);
		// 更新负责人姓名和编号,蝶城
		if ((info != null && info.getId() != null) || pk != null) {
			String id = pk != null ? pk.toString() : info.getId().toString();
			//if(reAuditStatus.equals("DISAPPROVAL") || reAuditStatus.equals("APPROVAL")){
				afterRunSaveData(id, auditStatus, reAuditStatus, info);
			//}			
		}
		return pk;
	}

	private void logInfo(String msg) {
		System.out.println(msg);
		logger.log(Priority.INFO, msg);
	}

	protected void afterInitModel(HttpServletRequest request,
			HttpServletResponse response, CoreBaseInfo coreBaseInfo)
	throws SHRWebException {
		logInfo("after初始化");
		super.afterInitModel(request, response, coreBaseInfo);

		logInfo("after初始化-结束");
		String billId = getBillId(request);
		logInfo("获取billId");
		if (!(StringUtils.isEmpty(billId))) {
			try {
				// 添加附件
				coreBaseInfo.put("attachJson", getAttachString(billId));
			} catch (Exception e) {
				logInfo("添加附件出错:"+e.getMessage());
				throw new SHRWebException(e.getMessage());
			}
		}
	}

	/**
	 * 获取附件对象的JSON字符串
	 * 
	 * @param dataId
	 * @return
	 * @throws Exception
	 */
	private String getAttachString(String dataId) throws Exception {
		List<MobileAttachDto> allData = getAttachList(dataId);
		if (allData.isEmpty()) {
			return "";
		}
		logInfo("json化附件");
		String json = JSON.toJSONString(transToDto(allData));
		return json.replace("\"", "'");
	}

	/**
	 * 获取附件列表
	 * 
	 * @param dataId
	 * @return
	 * @throws Exception
	 */
	private List<MobileAttachDto> getAttachList(String dataId) throws Exception {
		String sql = "SELECT * FROM CT_WK_attach WHERE data_id = '" + dataId+ "' and is_delete = 0";
		logInfo("获取附件列表");
		Context ctx = SHRContext.getInstance().getContext();
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		List<MobileAttachDto> allData = new ArrayList<MobileAttachDto>();
		MobileAttachDto item = null;

		logInfo("附件循环");
		while (rowSet.next()) {
			item = new MobileAttachDto();
			item.setId(rowSet.getInt("id"));
			item.setAttachName(rowSet.getString("attach_name"));
			item.setAttchUrl(rowSet.getString("attach_url"));
			item.setAttachType(rowSet.getString("attach_type"));
			item.setOssName(rowSet.getString("oss_name"));
			allData.add(item);
		}
		return allData;
	}

	/**
	 * 把附件转换成DTO对象
	 * 
	 * @param allData
	 * @return
	 */
	private MobileAttachAssembleDto transToDto(List<MobileAttachDto> allData) {
		MobileAttachAssembleDto imgDto = new MobileAttachAssembleDto();
		imgDto.setIdCard(getMatchAttachList("IdCard", allData));
		imgDto.getIdCard().addAll(getMatchAttachList("IdCardBack", allData));
		imgDto.setBankCard(getMatchAttachList("Bank", allData));
		imgDto.setContract(getMatchAttachList("Contract", allData));
		imgDto.setEntryRegistration(getMatchAttachList("EntryRegistration",
				allData));
		imgDto.setHealth(getMatchAttachList("Health", allData));
		imgDto.setSafe(getMatchAttachList("Safe", allData));
		imgDto.setInHand(getMatchAttachList("IdCardInHand", allData));
		imgDto.setOther(getMatchAttachList("Other", allData));
		imgDto.setManagemenetFile(getMatchAttachList("ManagementFile", allData));
		imgDto.setInsuranceProve(getMatchAttachList("InsuranceProve", allData));
		imgDto.setBehavioralNorm(getMatchAttachList("BehavioralNorm", allData));
		imgDto.setRecommendStimulate(getMatchAttachList("RecommendStimulate", allData));
		imgDto.setPromiseCommitment(getMatchAttachList("PromiseCommitment", allData));
		return imgDto;
	}

	/**
	 * 获取指定类型的附件并返回
	 * 
	 * @param type
	 * @param data
	 * @return
	 */
	private List<MobileAttachDto> getMatchAttachList(String type,
			List<MobileAttachDto> data) {
		List<MobileAttachDto> result = new ArrayList<MobileAttachDto>();
		for (MobileAttachDto item : data) {
			if (item.getAttachType().equals(type)) {
				result.add(item);
			}
		}
		return result;
	}

	/**
	 * 更新审批状态字段
	 * 
	 * @param id
	 * @param auditType
	 * @return
	 * @throws Exception
	 */
	private void afterRunSaveData(String id, String auditType,
			String reAuditType, MobileEntryBillInfo info) throws Exception {
		// 更新复审字段
		Context ctx = SHRContext.getInstance().getContext();
		StringBuilder sb = new StringBuilder();
		String userid = ContextUtil.getCurrentUserInfo(ctx).getId().toString();
		String userName = ContextUtil.getCurrentUserInfo(ctx).getName()
		.toString();

		sb.append(" UPDATE CT_WK_MobileEntryBill SET CFApprovalStatus = '")
		.append(auditType).append("', CFReApprovalStatus = '").append(
				reAuditType).append("', CFLastReApprover = '").append(
						userName).append("' ").append(
						", CFLastReApproverId = '").append(userid).append("' ")
						.append(", FLastUpdateUserID = '").append(userid).append("' ")
						.append(", FLastUpdateTime = GETDATE() ").append(
						" WHERE FID = '").append(id).append("'");
		logInfo("执行SQL" + sb.toString());
		DbUtil.execute(ctx, sb.toString());
		
		if(reAuditType.equals("")){
			
		}

		// 如果审批通过，则生成入职单
		if (reAuditType.equals("APPROVAL")) {
			builderEnrollEntryBill(userid, info);
		}

	}

	/**
	 * 
	 * @param webAuditStatus
	 * @return
	 */
	private String mappingAuditStatus(String webAuditStatus) {

		if (webAuditStatus.equals("待审核") || webAuditStatus.equals("待审批")) {
			return "WAIT";
		}

		if (webAuditStatus.equals("审核不通过")) {
			return "DISAPPROVAL";
		}
		if (webAuditStatus.equals("审核通过")) {
			return "APPROVAL";
		}
		return webAuditStatus;
	}

	/**
	 * 生成入职单据
	 * 
	 * @throws Exception
	 */
	private void builderEnrollEntryBill(String userId, MobileEntryBillInfo info)
	throws Exception {

		Context ctx = SHRContext.getInstance().getContext();
		String billId = getNewIdByType(ctx, "B41CAA3F");

		// String entryBillId = getNewIdByType(ctx, "04BEFD73");
		// 生成入职单
		logInfo("生成入职单");
		insertBill(ctx, userId, info, billId);

		// 生成入职单分录
		logInfo("生成入职单分录");
		insertBillEntry(ctx, userId, info, billId);

		// 生成附件
		logInfo("生成附件");
		saveAttach(ctx, userId, info, billId);
	}

	/**
	 * 保存附件
	 * 
	 * @param ctx
	 * @param userId
	 * @param info
	 * @throws Exception
	 */
	private void saveAttach(Context ctx, String userId, MobileEntryBillInfo info, String billId)
	throws Exception {

		// 获取附件数据
		List<MobileAttachDto> attachList = getAttachList(info.getId().toString());
		if (attachList.isEmpty()) {
			return;
		}
		getAttachAndTrans(getMatchAttachList("IdCard", attachList), "身份证正面", billId);
		getAttachAndTrans(getMatchAttachList("IdCardBack", attachList), "身份证背面", billId);
		getAttachAndTrans(getMatchAttachList("Bank", attachList), "银行卡", billId);
		getAttachAndTrans(getMatchAttachList("Contract", attachList), "劳动合同", billId);
		getAttachAndTrans(getMatchAttachList("EntryRegistration", attachList),
				"入职信息登记", billId);
		getAttachAndTrans(getMatchAttachList("Health", attachList), "健康承诺书", billId);
		getAttachAndTrans(getMatchAttachList("Safe", attachList), "安全受训承诺书", billId);
		getAttachAndTrans(getMatchAttachList("IdCardInHand", attachList),
				"手持身份证", billId);
		getAttachAndTrans(getMatchAttachList("Other", attachList),
				"其他档案", billId);
		getAttachAndTrans(getMatchAttachList("ManagementFile", attachList),
				"保洁员管理规范", billId);
		getAttachAndTrans(getMatchAttachList("InsuranceProve", attachList),
				"保险/投保证明", billId);
		getAttachAndTrans(getMatchAttachList("BehavioralNorm", attachList),
				"行为规范", billId);
		getAttachAndTrans(getMatchAttachList("Training", attachList),
				"训练/验收清单", billId);
		getAttachAndTrans(getMatchAttachList("RecommendStimulate", attachList),
				"推荐激励受信书", billId);
		getAttachAndTrans(getMatchAttachList("PromiseCommitment", attachList),
				"上岗承诺书", billId);
	}

	/**
	 * 下载附件并转化
	 * 
	 * @param currentList
	 * @throws Exception
	 */
	private void getAttachAndTrans(List<MobileAttachDto> currentList,
			String fileName, String billId) throws Exception {
		
		logInfo("下载附件并转化:"+fileName+",json:"+JSON.toJSONString(currentList));
		// 处理的附件列表
		Integer id = null;
		String downloadUrl = null;
		byte[] fileByte = null;
		for (int i = 0; i < currentList.size(); i++) {

			// 获取文件下载链接
			id = currentList.get(i).getId();
			downloadUrl = HttpUtils.sendGet(
					"https://shr.onewo.com:8443/shr_mobile/h5/mobile/attach/download/url/"
					+ id.toString(), null);
			if (null == downloadUrl) {
				continue;
			}

			// 生成附件
			fileByte = downloadAliyunAttach(downloadUrl);
			if (fileByte == null || fileByte.length <= 0) {
				continue;
			}
			logInfo("当前处理第"+i+"个");
			makeBosAttach(fileByte, currentList.get(i).getAttachName(), String.format("%s_%s",fileName,(i+1)), billId);
		}
	}
	
	/**
	 * 构建员工编号
	 *
	 * @return
	 * @throws Exception 
	 */
	private String generatePersonNumber(String idCardNo,Context ctx) throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");
		String defaultNum = String.format("WY%s%s", sdf.format(new Date()), idCardNo.substring(idCardNo.length() - 4));
		if (defaultNum.contains("X") || defaultNum.contains("x")) {
			defaultNum = defaultNum.toLowerCase().replace("x", "0").toUpperCase();
		}
		if (checkNumberPersonExist(ctx,defaultNum) <= 0 && checkNumberPersonEnrollEntry(ctx,defaultNum) <= 0) {
			return defaultNum;
		}
		for (int i = 1; i <= 10000; i++) {
			defaultNum = String.format("WY%s%04d", sdf.format(new Date()), i);
			if (checkNumberPersonExist(ctx,defaultNum) <= 0 && checkNumberPersonEnrollEntry(ctx,defaultNum) <= 0) {
				return defaultNum;
			}
		}
		logInfo("生成员工编号时执行超时异常,身份证号:"+idCardNo);
		//这里非常容易死锁超时
		return generatePersonNumber(ctx);
	}
	
	private Integer checkNumberPersonExist(Context ctx, String code) throws Exception{
		String sql = "SELECT COUNT(1) AS Num FROM T_BD_Person WHERE Fnumber = '"+code+"'";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getInt("Num");
		}
		return 1;
	}
	
	private Integer checkNumberPersonEnrollEntry(Context ctx, String code) throws Exception{
		String sql = "SELECT COUNT(1) AS Num FROM T_HR_EmpEnrollBizBillEntry WHERE FEMPNUMBER = '"+code+"'";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getInt("Num");
		}
		return 1;
	}

	/**
	 * 获取人员ID
	 * 
	 * @param ctx
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	private String getPersonIdByPMUserId(Context ctx, String userId)
	throws Exception {
		String sql = "SELECT TOP 1 FPersonID FROM T_PM_User WHERE FID = '"
			+ userId + "'";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getString("FPersonID");
		}
		throw new SHRWebException("获取申请人的人员ID失败");
	}

	/**
	 * 生成指定bosType的数据ID
	 * 
	 * @param ctx
	 * @param bosType
	 * @return
	 * @throws Exception
	 */
	private String getNewIdByType(Context ctx, String bosType) throws Exception {
		String sql = " SELECT dbo.newbosId('" + bosType + "') AS id";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getString("id");
		}
		throw new SHRWebException("生成ID失败,bosType:" + bosType);
	}

	/**
	 * 根据编码获取指定表的ID
	 * 
	 * @param tableName
	 * @param code
	 * @param ctx
	 * @return
	 */
	private String getIdByCode(Context ctx, String tableName, String code)
	throws Exception {
		String sql = "SELECT FID FROM " + tableName + "WHERE Fnumber = '"
		+ code + "'";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getString("FID");
		}
		throw new SHRWebException("获取ID失败,tableName:" + tableName + ",code="
				+ code);
	}

	/**
	 * 添加生成员工入职单据
	 * 
	 * @param ctx
	 * @param userId
	 *            当前操作人ID
	 * @param billInfo
	 *            移动入职单信息对象
	 * @param billId
	 *            入职单ID
	 * @throws Exception
	 */
	private void insertBill(Context ctx, String userId,
			MobileEntryBillInfo billInfo, String billId) throws Exception {

		StringBuilder sb = new StringBuilder();
		String adminId = getIdByTableName(ctx, " T_ORG_BaseUnit  ", billInfo
				.getOrganCode());

		// 用户UserID要转换成BDPerson的ID
		String personId = getPersonIdByPMUserId(ctx, userId);

		sb
		.append(" INSERT INTO T_HR_EmpEnrollBizBill(")
		.append(
		"FApplierID,FApplyDate,FAdminOrgUnitId,FHROrgUnitID,FBillState,FApproveType,FNumber,")
		.append(
		"FBizDate,FCreatorID,FCreateTime,FLastUpdateUserID,FLastUpdateTime,FControlUnitID,FID,")
		.append(
		"FZDY8,FZDY9,FZDY10,FIsMultiEntry,FInnerState,FuseDefault) VALUES (")
		.append("'")
		.append(personId)
		.append("', GETDATE(), '")
		.append(adminId)
		.append("','qi8AAABKUC/M567U',0,0,")
		// 入职单号
		.append("'")
		.append(generateBillNumber(ctx))
		.append("',GETDATE(),'")
		.append(userId)
		.append("',GETDATE(),'")
		.append(userId)
		.append(
		"',GETDATE(),'00000000-0000-0000-0000-000000000000CCE7AED4','")
		.append(billId).append("',0,0,0,0,0,1);");
		logInfo("当前生成入职单sql为:" + sb.toString());
		DbUtil.execute(ctx, sb.toString());
	}

	/**
	 * 根据编码获取指定表数据的ID
	 * 
	 * @param ctx
	 * @param tableName
	 * @param code
	 * @return
	 * @throws Exception
	 */
	private String getIdByTableName(Context ctx, String tableName, String code)
	throws Exception {
		String sql = " SELECT TOP 1 FID FROM " + tableName
		+ " WHERE Fnumber = '" + code + "'";
		logInfo("根据编码获取指定表数据的sql:"+sql);
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getString("FID");
		}
		return null;
		//throw new SHRWebException("获取指定表的ID失败,表名:" + tableName + ",编码:" + code);
	}
	
	private String getIdByNameSignTable(Context ctx, String tableName, String name)
	throws Exception {
		String sql = " SELECT TOP 1 FID FROM " + tableName
		+ " WHERE FName_L2 = '" + name + "'";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			return rowSet.getString("FID");
		}
		return null;
		//throw new SHRWebException("获取指定表的ID失败,表名:" + tableName + ",编码:" + code);
	}

	/**
	 * 生成入职单号
	 * 
	 * @param ctx
	 * @return
	 * @throws Exception
	 */
	private String generateBillNumber(Context ctx) throws Exception {

		String updateSql = "UPDATE T_BAS_MaxSerial SET FSERIALNUMBER = FSERIALNUMBER+1 WHERE FCODINGRULEENTRYID  = 'IjjBiShXTDOgYw8KsQcLISbvRW8='";
		DbUtil.execute(ctx, updateSql);

		String sql = "SELECT FSERIALNUMBER from T_BAS_MaxSerial where FCODINGRULEENTRYID  = 'IjjBiShXTDOgYw8KsQcLISbvRW8='";
		BigDecimal num = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		Integer r = (int)(Math.floor(Math.random()*100));
		String numberString = null;

		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			num = rowSet.getBigDecimal("FSERIALNUMBER");
			numberString = String.format("%s%02d-%05d", sdf.format(new Date()), r, num
					.intValue());
		}
		int count = 0;

		while (checkNumberExist(ctx, numberString, "T_HR_EmpEnrollBizBill")
				&& count++ < 10) {
			logger.debug("入职单编号" + numberString + "重复");
			DbUtil.execute(ctx, updateSql);
			rowSet = DbUtil.executeQuery(ctx, sql);
			if (rowSet.next()) {
				r = (int)(Math.floor(Math.random()*100));
				num = rowSet.getBigDecimal("FSERIALNUMBER");
				numberString = String.format("%s-%05d", sdf.format(new Date()), r,	num.intValue());
			}
		}
		if (count < 10) {
			return numberString;
		}
		throw new SHRWebException("生成入职单编号发生异常");

	}

	/**
	 * 生成员工编号
	 * 
	 * @param ctx
	 * @return
	 * @throws Exception
	 */
	private String generatePersonNumber(Context ctx) throws Exception {

		String updateSql = "UPDATE T_BAS_MaxSerial SET FSERIALNUMBER = FSERIALNUMBER+1 WHERE FCODINGRULEENTRYID  = '88c5a232-0101-1000-e001-cd97c0a8100d26EF456F'";
		DbUtil.execute(ctx, updateSql);

		String sql = "SELECT FSERIALNUMBER from T_BAS_MaxSerial where FCODINGRULEENTRYID  = '88c5a232-0101-1000-e001-cd97c0a8100d26EF456F'";
		BigDecimal num = null;
		String numberString = null;

		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			num = rowSet.getBigDecimal("FSERIALNUMBER");
			numberString = String.format("WY%06d", num.intValue());
		}
		int count = 0;

		while (checkNumberExist(ctx, numberString, "T_BD_Person")
				&& count++ < 10) {
			logger.debug("人员编号" + numberString + "重复");
			DbUtil.execute(ctx, updateSql);
			rowSet = DbUtil.executeQuery(ctx, sql);
			if (rowSet.next()) {
				num = rowSet.getBigDecimal("FSERIALNUMBER");
				numberString = String.format("WY%06d", num.intValue());
			}
		}
		if (count < 10) {
			return numberString;
		}
		throw new SHRWebException("生成人员编号发生异常");

	}

	/**
	 * 检查指定编号是否存在指定表中
	 * 
	 * @param ctx
	 * @param number
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	private boolean checkNumberExist(Context ctx, String number,
			String tableName) throws Exception {
		String sql = "SELECT COUNT(1) AS Num FROM " + tableName
		+ " WHERE Fnumber = '" + number + "'";
		IRowSet rowSet = DbUtil.executeQuery(ctx, sql);
		if (rowSet.next()) {
			if (rowSet.getInt("Num") > 0) {
				return true;
			}
		}
		return false;
	}

	/**
	 * 生成入职单分录
	 * 
	 * @param ctx
	 * @param userId
	 * @param billInfo
	 * @param billId
	 * @throws Exception
	 */
	private void insertBillEntry(Context ctx, String userId,
			MobileEntryBillInfo billInfo, String billId) throws Exception {

		StringBuilder sb = new StringBuilder();
		
		try{
			sb
			.append("INSERT INTO T_HR_EmpEnrollBizBillEntry(FBillID,FBizDate,FAdminOrgID,FPositionID,FEmpTypeID,");
			sb
			.append("FPersonID,FEmpNumber,FEmpName,FIdCardNo,FGender,FBirthday,FEnrollDate,FEnrollAgain,");
			sb.append("FExistPersonID,FUseOldNumber,FResumeID,FSeq,FID,");
			sb
			.append("FIsParPerson,FReplaceAdminOrgID,FidCardAddress,FOldAdminOrgID,FOldPositionID,FIsPrimary,FOldEmpTypeID,");
			sb
			.append("FAffairActReasonID,FHrBizDefineID,FVariationReasonID,FTelNum,FMyFolkID, FIdCardEndDate,FIdCardStartDate,FIdCardIssued,");
			sb
			.append("FEmployerUnitID,FCompanyID,FUseDefault,FHrOrgUnitID,FIdCardLongEffect,FSourceBillType,");
			sb
			.append("CFSupplierID1, CFLinkName, CFLinkTelNum, CFLinkRelationID, FBankID, FAccountNum, CFEmpEntrySourceID, CFEMPLOYEEPROJECTI) VALUES (");
			
			SimpleDateFormat rSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			sb.append("'").append(billId).append("',");
			// 业务日期\组织ID\岗位ID
			String organId = getIdByTableName(ctx, "T_ORG_BaseUnit", billInfo.getOrganCode());	
			
			sb.append("'").append(rSdf.format(billInfo.getEntryTime())).append("',");
			
			sb.append("'").append(organId).append("',");
			sb.append("'").append(getIdByTableName(ctx, "T_ORG_Position", billInfo.getPositionCode())).append("',");
	
			// 用工关系默认劳动外包
			String useTypeId = getIdByTableName(ctx,"T_HR_BDEMPLOYEETYPE", billInfo.getUseEmployeeTypeCode());
			logInfo("用工关系"+billInfo.getUseEmployeeTypeCode()+",查询结果:"+useTypeId);
			if(useTypeId!=null){
				sb.append("'").append(useTypeId).append("',");
			}
			else{
				sb.append("'qi8AAAAHNf2inoWz',");
			}
	
			// 获取员工编码和旧关系数据
			Map<String, String> checkResult = checkAndGetOldInfo(ctx, billInfo.getIdcardNo());
			Boolean isOldPerson = false;
			if ("true".equals(checkResult.get("exist"))) {
				isOldPerson = true;
				sb.append("'").append(checkResult.get("personId")).append("',");
				sb.append("'").append(checkResult.get("personCode")).append("',");
			} else {
				sb.append("null,");
				// 构建人员编号
				String personCode = generatePersonNumber(billInfo.getIdcardNo(), ctx);
				sb.append("'").append(personCode).append("',");
			}
			
			
			// 姓名/身份证/性别/出生日期/入职时间/是否重入职
			sb.append("'").append(billInfo.getPersonName()).append("',");
			sb.append("'").append(billInfo.getIdcardNo()).append("',");
			sb.append(mapToGender(billInfo.getIdcardNo())).append(",");
			Date birthDay = transToBirthday(billInfo.getIdcardNo());
			if (birthDay != null) {
				sb.append("'").append(rSdf.format(birthDay)).append("',");
			} else {
				sb.append("null,");
			}
	
			sb.append("'").append(rSdf.format(billInfo.getEntryTime())).append("',");
			String oldEmpType = null;
			// 如果是旧人员
			if (isOldPerson) {
				sb.append("1,").append("'").append(checkResult.get("personId")).append("',1,");
				oldEmpType = getOldEmpType(ctx,checkResult.get("personId"));
			} else {
				sb.append("0,null,0,");
			}
			//身份证地址
			sb.append("null,1,");
	
			// 生成并添加FID
			String fid = getNewIdByType(ctx, "04BEFD73");
			// 续存组织要替换为公司的ID
			String companyID = getCompanyIDByOrganId(ctx, organId);
	
			// FID/续组织织、身份证地址
			sb.append("'").append(fid).append("',0,").append("'").append(companyID);
			if(billInfo.getIdcardAddress()!=null&&billInfo.getIdcardAddress()!=""){
				sb.append("','").append(billInfo.getIdcardAddress()).append("',");
			}
			else{
				sb.append("',null,");
			}
			
			// 是否有原组织/原岗位/
			if (isOldPerson) {
				sb.append("'").append(checkResult.get("organId")).append("','")
				.append(checkResult.get("positionId")).append("',");
			} else {
				sb.append("null,null,");
			}
			sb.append("0,").append(oldEmpType == null ? "null" : ("'"+oldEmpType+"'")).append(",");
			//如果是重入职，则需要变更关系
			if(isOldPerson){
				sb.append("'1wXho31/Tsi0LhiLKfCQ+JYRae4=','DawAAAApC9TmaL7Z','sKSMJ3pDTQCtVNKkqB5WnOas36w=',");
			}else{
			    //变动类型，默认入职; 变动操作默认雇佣入职; 变动原因默认：其他
				sb.append("'1wXho31/Tsi0LhiLKfCQ+JYRae4=','DawAAAApC9DmaL7Z','sKSMJ3pDTQCtVNKkqB5WnOas36w=',");
			}
			// 手机号码/民族/身份证起止时间/地址
			sb.append("'").append(billInfo.getPhoneNum()).append("',");

			logInfo("民族"+billInfo.getNationName()+","+billInfo.getNationCode());
			if(billInfo.getNationCode()!=null && billInfo.getNationCode() != "" ){
				String folkId =getIdByTableName(ctx, "T_BD_HRFolk", billInfo.getNationCode());
				logInfo("民族查询ID:"+folkId);
				if(folkId!=null){
					sb.append("'").append(folkId).append("',");
				}
			}
			else{
				sb.append("null,");
			}
			
			//身份证结束时间
			if(billInfo.getIdCardEndTime()!=null){
				sb.append("'").append(rSdf.format(billInfo.getIdCardEndTime())).append("',");
			}
			else{
				sb.append("null,");
			}
			
			//身份证开始时间
			if(billInfo.getIdCardStartTime()!=null){
				sb.append("'").append(rSdf.format(billInfo.getIdCardStartTime())).append("',");
			}
			else{
				sb.append("null,");
			}
			//签发机关
			if(billInfo.getIdCardIssuingAuthority()!=null){
				sb.append("'").append(billInfo.getIdCardIssuingAuthority()).append("',");
			}
			else{
				sb.append("null,");
			}
			// 用工单位/公司
			sb.append("'00000000-0000-0000-0000-000000000000CCE7AED4','"+ companyID	+ "',1,'qi8AAABKUC/M567U',");
			// 身份证是否长期			
			sb.append(idcardIsLong(billInfo.getIdCardEndTime())).append(",100,");
	
			// 自定义字段
			// 供应商/联系人/银行卡  
			String supplierId = getIdByTableName(ctx, "CT_BD_Gys", billInfo.getSupplierCode() ) ;
			if(null!= supplierId && "" != supplierId){
				sb.append("'").append(supplierId ).append("',");
			}
			else{
				sb.append("null,");
			}
			
			//sb.append("null,");
			sb.append("'").append(billInfo.getContactName()).append("',");
			sb.append("'").append(billInfo.getContactCellNum()).append("',");
			sb.append("'").append(			getIdByNameSignTable(ctx, "T_HR_BDRelation", billInfo.getContactRelationName())).append("',");
			String bankName = getIdByNameSignTable(ctx, "T_HR_PBank", billInfo.getBankName());
			if(bankName!=null)
			{
				sb.append("'").append(bankName).append("',");
			}
			else{
				sb.append("null,");
			}
			if(billInfo.getBankCardNo()!=null&&billInfo.getBankCardNo()!=""){
				sb.append("'").append(billInfo.getBankCardNo()).append("'");
			}
			else{
				sb.append("null");
			}
			
			//入职来源ID
			logInfo("入职来源"+billInfo.getEntrySource()+","+billInfo.getEntrySourceId());
			if(billInfo.getEntrySourceId()!=null && billInfo.getEntrySourceId() != ""){
				String sourceId = getIdByTableName(ctx, "CT_MP_EntrySource", billInfo.getEntrySourceId());
				logInfo("入职来源查询ID:"+sourceId);
				if(sourceId!=null && sourceId != ""){
					sb.append(",'").append(sourceId ).append("'");
				}
				else{
					sb.append(",null");
				}
			}
			else{				
				sb.append(",null");
			}
			
			//战图项目
			logInfo("入职来源"+billInfo.getProjectCode()+","+billInfo.getProjectName());
			if(billInfo.getProjectCode()!=null ){
				String projectId = getIdByTableName(ctx, "CT_MP_EmployeeProjectInfo", billInfo.getProjectCode());
				logInfo("入职来源查询ID"+projectId);
				if(projectId!=null && projectId != ""){
					sb.append(",'").append(projectId ).append("'");
				}
				else{
					sb.append(",null");
				}
			}
		}
		catch(Exception e){
			logInfo("当前生成入职单sql为:" + sb.toString());
			e.printStackTrace();
			logInfo(""+e.getMessage());
		}
		// 结束
		sb.append(")");
		logInfo("当前生成入职单sql为:" + sb.toString());
		DbUtil.execute(ctx, sb.toString());
	}
	
	//比较身份证是否长期有效
	private String idcardIsLong(Date idCardEndTime){
		if(idCardEndTime == null){
			return "0";
		}
		
		SimpleDateFormat rSdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(rSdf.format(idCardEndTime).compareTo("2199-12-30")>=0){
			return "1";
		}
		return "0";
	}
	
	
	private String getOldEmpType(Context ctx, String personId) throws Exception{
		String sql = "SELECT TOP 1 FLABORRELATIONSTATEID FROM T_HR_EmpLaborRelation WHERE FPERSONID  = '"
			+ personId+ "' AND FEMPLOYEEMODLEID  IN('00000000-0000-0000-0000-000000000006A29E85B3','00000000-0000-0000-0000-000000000007A29E85B3') ORDER BY FLEFFDT DESC";
		IRowSet rowset = DbUtil.executeQuery(ctx, sql);
		String laborreTypeId = null;
		if (rowset.next()) {
			laborreTypeId = rowset.getString("FLABORRELATIONSTATEID");
		}
		return laborreTypeId;
	}
	

	/**
	 * 获取员工入职所在的公司ID
	 * 
	 * @param ctx
	 * @param organId
	 * @return
	 * @throws Exception
	 */
	private String getCompanyIDByOrganId(Context ctx, String organId)
	throws Exception {
		String sql = "SELECT TOP 1 FLongNumber FROM T_ORG_BaseUnit WHERE FID = '"
			+ organId + "'";
		StringBuilder sb = new StringBuilder();
		logInfo(sql);
		IRowSet rowset = DbUtil.executeQuery(ctx, sql);
		String longNumber = null;
		if (rowset.next()) {
			longNumber = rowset.getString("FLongNumber");
		}

		if (null != longNumber) {
			String[] longNumbers = longNumber.split("!");
			if (longNumbers.length > 0) {
				sb.append(" SELECT TOP 1 a.FID AS FID FROM T_ORG_BaseUNit AS a ");
				sb.append(" LEFT JOIN T_ORG_OUPartAdmin AS b ON( a.FID = b.FUNITID  ) ");
				sb.append(" WHERE  b.FLAYERTYPEID ='00000000-0000-0000-0000-00000000000262824988' AND  a.Fnumber IN( ");
				for (int i = 0; i < longNumbers.length; i++) {
					if (i > 0) {
						sb.append(",");
					}
					sb.append("'").append(longNumbers[i]).append("'");
				}
				sb.append(") order by a.FLEVEL DESC ");
				rowset = DbUtil.executeQuery(ctx, sb.toString());
				if (rowset.next()) {
					return rowset.getString("FID");
				}
			}
		}
		throw new SHRWebException("获取员工公司ID时发生异常");
	}

	/**
	 * 检查是否是再入职人员(如果是，则获取原职位、原用工关系、用所在组织
	 */
	private Map<String, String> checkAndGetOldInfo(Context ctx, String idCardNo)
	throws Exception {
		logInfo("获取再入职人员信息");
		Map<String, String> result = new TreeMap<String, String>();
		result.put("exist", "false");

		String sql = "SELECT TOP 1 p.FID userId, p.FNumber userCode, p.FName_L2 userName,dep.FID userOrgId, dep.FNumber userOrgCode, pson.FPrimaryPositionID positionId "
					+ " FROM t_bd_person p "
					+ " LEFT JOIN T_HR_PersonPosition pson ON pson.FPersonID = p.fid "
					+ " LEFT JOIN T_ORG_Admin dep ON dep.fid = pson.FPersonDep " 
					+ " LEFT JOIN T_HR_BDEmployeeType empType ON empType.fid = p.FEmployeeTypeID "
					+ "  WHERE  p.FIDCardNO = '"
					+ idCardNo + "' AND empType.FNumber NOT IN ( '001', '002', 'S02', 'S04', 'S06', '035', 'S11', 'S10' ) ORDER BY p.FCreateTime DESC";
		logInfo("获取再入职人员信息SQL:"+sql);
		IRowSet rowset = DbUtil.executeQuery(ctx, sql);
		if (rowset.next()) {
			result.put("exist", "true");
			result.put("personId", rowset.getString("userId"));
			result.put("organId", rowset.getString("userOrgId"));
			result.put("positionId", rowset.getString("positionId"));
			// 人员编码
			result.put("personCode", rowset.getString("userCode"));
		}
		//logInfo("返回结果:"+JSON.toJSONString(result));
		return result;
	}

	/**
	 * 从身份证解析性别：1男2女
	 * 
	 * @param idcardNo
	 * @return
	 */
	private Integer mapToGender(String idcardNo) {
		if (Integer.parseInt(idcardNo.substring(16, 17)) % 2 == 0) {
			return 2;
		}
		return 1;
	}

	/**
	 * 提取出生日期
	 */
	private Date transToBirthday(String idcardNo) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		try {
			return sdf.parse(idcardNo.substring(6, 14));
		} catch (Exception e) {
			return null;
		}
	}
	
//	/**
//	 * 保存附件到本地，并返回本地路径
//	 * @param signedUrl
//	 */
//	private String saveToLocalFile(String signedUrl){
//				
//		  try {
//	            // 示例URL，你可以替换为任何有效的图片URL
//	            URL url = new URL(signedUrl);
//	            InputStream in = url.openStream();  // 获取输入流
//	            	            
//	            String filePath = "/path/to/your/directory/image.jpg";  // 指定本地文件路径
//	            OutputStream out = new FileOutputStream(filePath);  // 创建输出流
//	 
//	            // 缓冲区，提高读写效率
//	            byte[] buffer = new byte[4096];
//	            int bytesRead;
//	 
//	            // 读取输入流，并写入输出流
//	            while ((bytesRead = in.read(buffer)) != -1) {
//	                out.write(buffer, 0, bytesRead);
//	            }
//	 
//	            // 关闭流
//	            out.close();
//	            in.close();
//	            System.out.println("保存文件附件失败:"+signedUrl);
//	        } catch (Exception e) {
//	            e.printStackTrace();
//	        }
//	   
//	}

	/**
	 * 转换成SHR中的BOS和attach
	 * 
	 * @param signedUrl
	 * @throws Exception
	 */
	private byte[] downloadAliyunAttach(String signedUrl) throws Exception {
		CloseableHttpResponse response = null;
		CloseableHttpClient client = null;
		byte[] data = null;
		try {
			client = HttpClients.createDefault();
			HttpGet get = new HttpGet(signedUrl.toString());
			response = client.execute(get);
			if (200 != response.getStatusLine().getStatusCode()) {
				throw new SHRWebException("下载附件失败");
			}
			HttpEntity entity = response.getEntity();
			data = EntityUtils.toByteArray(entity);
			client.close();
		} catch (IOException e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (client != null) {
				client.close();
			}
		}
		return data;
	}

	/**
	 * 
	 * @param request
	 * @param multipartFile
	 * @return
	 * @throws Exception
	 * @throws BOSException
	 * @throws EASBizException
	 * @throws IOException
	 * @throws ShrWebBizException
	 */
	private void makeBosAttach(byte[] data, String originalName, String signName, String billId )
	throws Exception {
		logInfo("makeBosAttach初始化,原文件:"+originalName+"指定文件名:"+signName);
		
		IAttachment attachment = AttachmentFactory.getRemoteInstance();
		IBoAttchAsso attchAsso = BoAttchAssoFactory.getRemoteInstance();
		Context ctx = SHRContext.getInstance().getContext();

		String boID = getNewIdByType(ctx, "172F3A47");
		String propertyName = "null0";
		String onlyone = "false";
		String description = "附件";
		String uipk = "com.kingdee.eas.hr.affair.app.EmpEnrollBizBill.form";
		String userId = HRFilterUtils.getCurrentUserId(SHRContext.getInstance()
				.getContext());
		SHRAttachmentExtInfo attchExt = new SHRAttachmentExtInfo();

		if (Boolean.valueOf(onlyone).booleanValue()) {
			FilterInfo filterInfo = new FilterInfo();
			filterInfo.getFilterItems().add(
					new FilterItemInfo("bunding", new StringBuilder().append(
							userId).append('#').append(uipk).toString()));
			filterInfo.getFilterItems().add(
					new FilterItemInfo("propertyName", propertyName));
			EntityViewInfo entityViewInfo = new EntityViewInfo();
			if (!(StringUtils.isEmpty(boID))) {
				filterInfo.getFilterItems().add(
						new FilterItemInfo("boID", boID));
			} else {
				filterInfo.getFilterItems().add(
						new FilterItemInfo("state", Integer.valueOf(20)));
			}
			entityViewInfo.setFilter(filterInfo);
			ISHRAttachmentExt SHRAttchExt = SHRAttachmentExtFactory
			.getRemoteInstance();
			SHRAttachmentExtCollection collection = SHRAttchExt
			.getSHRAttachmentExtCollection(entityViewInfo);
			SHRAttchExt.delete(filterInfo);

			for (int i = 0; i < collection.size(); ++i) {
				String attId = collection.get(i).getAttachment().getId()
				.toString();
				if (!(StringUtils.isEmpty(boID))) {
					attchAsso.delete(MessageFormat.format(
							" where attachment = ''{0}''",
							new Object[] { attId }));
				} else {
					attachment.delete(new ObjectUuidPK(attId));
				}
			}
		}

		String fullname = originalName;
		fullname = checkScriptAttack(fullname);

		//如果无格式
		if(fullname.lastIndexOf(46)<=-1){
			fullname = fullname +".jpg";
		}
		
		String mainname = fullname.substring(0, fullname.lastIndexOf(46));
		String extname = fullname.substring(fullname.lastIndexOf(46) + 1,
				fullname.length());
		extname = extname.toLowerCase();

		AttachmentInfo ai = new AttachmentInfo();
		ai.setName(signName);
		ai.setSimpleName(extname);
		ai.setDescription(description);
		ai.setFile(data);
		ai.setIsShared(false);
		ai.setSharedDesc("否");
		int size = (int) data.length;
		if (size < 1024)
			ai
			.setSize(new StringBuilder().append(size).append("字节")
					.toString());
		else {
			ai.setSize(new StringBuilder().append(size / 1024).append("KB")
					.toString());
		}
		ai.setSizeInByte(size);
		ai.setAttachID(new StringBuilder().append("").append(
				System.currentTimeMillis()).toString());
		ai.setType(getFileType(fullname));

		attchExt.setAttachment(ai);
		attchExt.setName(fullname);
		attchExt.setPropertyName(propertyName);
		if (propertyName.startsWith("null"))
			attchExt.setType(AttachmentTypeEnum.FORM);
		else {
			attchExt.setType(AttachmentTypeEnum.PROPERTY);
		}
		attchExt.setState(AttachmentState.UNSAVE);
		attchExt.setBunding(new StringBuilder().append(userId).append('#')
				.append(uipk).toString());
		attchExt.setBoID(boID);
		try {
			attachment.addnew(ai);
		} catch (Exception e) {
			logInfo("生成附件时发生异常:"+e.getMessage());
			logger.error(e.getMessage(), e);
			throw new ShrWebBizException(
					"附件上传失败，请您从以下几项检查（如问题还未解决请导出日志提单解决）：\n1、数据库空间不足；\n2、磁盘空间不足；\n3、存放附件服务器异常，请检查服务器的网络或配置是否正常。",
					e);
		}

		//生成中间关联表
		buildBosAttach(ctx, billId, ai.getId().toString());		
	}

	/**
	 * 生成附件中间关联表
	 * @param ctx
	 * @param billId
	 * @param attachId
	 * @throws Exception
	 */
	private void buildBosAttach(Context ctx, String billId, String attachId ) throws Exception{
		String bosId = getNewIdByType(ctx, "172F3A47");
		String busObjectType = "F4AF4F03";
		StringBuilder sb = new StringBuilder();
		sb.append(" INSERT INTO T_BAS_BOATTCHASSO(FID,FBoID,FAssoType_L2, FAttachmentID, FAssoBusObjType ) VALUES ( ");
		sb.append(" '").append(bosId).append("',");
		sb.append("'").append(billId).append("','Added Accessories',");
		sb.append("'").append(attachId).append("','B41CAA3F')");
		logInfo("生成附件中间表sql:"+sb.toString());		
		DbUtil.execute(ctx, sb.toString());
	}

	private String checkScriptAttack(String str) {
		if (!(StringUtils.isEmpty(str))) {
			if (str.contains("<")) {
				str.replaceAll("<", "&lt;");
			}
			if (str.contains(">")) {
				str.replaceAll(">", "&gt;");
			}
		}
		return str;
	}

	private String getFileType(String fullname) {
		String extname = fullname.substring(fullname.lastIndexOf(46) + 1,
				fullname.length());
		if (("doc".equalsIgnoreCase(extname))
				|| ("docx".equalsIgnoreCase(extname)))
			return "Microsoft Word 文档";
		if (("xls".equalsIgnoreCase(extname))
				|| ("xlsx".equalsIgnoreCase(extname))
				|| ("xlsm".equalsIgnoreCase(extname))
				|| ("xlsb".equalsIgnoreCase(extname)))
			return "Microsoft Excel 表格";
		if (("ppt".equalsIgnoreCase(extname))
				|| ("pptx".equalsIgnoreCase(extname))
				|| ("pptm".equalsIgnoreCase(extname)))
			return "Microsoft PowerPoint 幻灯片";
		if ("txt".equalsIgnoreCase(extname)) {
			return "TEXT 文本文件";
		}
		return new StringBuilder().append("未知文件类型(.").append(extname).append(
		")").toString();
	}
}