package com.kingdee.shr.compensation.util.integrate;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Maps;
import com.kingdee.bos.BOSException;
import com.kingdee.bos.Context;
import com.kingdee.bos.util.EASResource;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.hr.base.HRBillBaseInfo;
import com.kingdee.eas.hr.base.HRBillStateEnum;
import com.kingdee.eas.hr.base.app.util.HRWorkFlowCheckUtil;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.base.syssetting.json.JSONUtils;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillCollection;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillFactory;
import com.kingdee.shr.compensation.app.integrate.BatchSubmitShemeBillInfo;
import com.kingdee.shr.compensation.integrate.dto.CalShemeSubmitItemDTO;
import com.kingdee.shr.compensation.util.CmpStrUtil;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

public class CmpIntegrateUtils {
	private static Logger logger = Logger.getLogger(CmpIntegrateUtils.class);

	private static String FIELDS_STR = "fields";

	private static String FORMULA_STR = "formula";

	private static String TARGET_STR = "targetField";

	private static String TARGET_NAME_STR = "name";

	private static String TARGET_PRECISION_STR = "decimalPrecision";

	public static void updateEntryBillState(Context ctx, String billId, HRBillStateEnum stateEnum, String billEntryTable) throws BOSException, EASBizException {
		BatchSubmitShemeBillInfo info = BatchSubmitShemeBillFactory.getLocalInstance(ctx).getBatchSubmitShemeBillInfo("where id = '" + billId + "'");
		String billEntryTableName = billEntryTable;
		if (StringUtils.isEmpty(billEntryTableName)) {
			Map<String, Object> map = getBillColumnListAndBillEntryTableName(ctx, info
					.getSubmitScheme().getId().toString());
			billEntryTableName = map.get("fBillEntryTableName").toString();
		}
		String sqlTemplate = "UPDATE %s SET FBillState = ? WHERE FBILLID = ?";
		String sql = String.format(sqlTemplate, new Object[] { billEntryTableName });
		DbUtil.execute(ctx, sql, new Object[] { Integer.valueOf(stateEnum.getValue()), info.getId().toString() });
	}

	public static String getUsableItemByHro(Context ctx, String hrOrgUnit, boolean isDisatribute) {
		StringBuilder personCmpItemPerm = new StringBuilder();
		personCmpItemPerm.append("select FBaseInfoId from T_HR_SCalSubmitItemRelHR  where T_HR_SCalSubmitItemRelHR.FHrOrgUseId ='");
		personCmpItemPerm.append(hrOrgUnit).append("'");
		if (isDisatribute)
			personCmpItemPerm.append(" and FState=100 ");
		return personCmpItemPerm.toString();
	}

	public static boolean checkSubmitSchemeIsUsingBill(Context ctx, String schemeId) {
		boolean flag = false;
		if (StringUtils.isEmpty(schemeId))
			return flag;
		String sql = "Select top 1 fid  from T_HR_SBatchSubmitShemeBill where fbillstate in (0,1,2)  and FSubmitSchemeID = ? ";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql, new Object[] { schemeId });
			if (rs.size() > 0)
				flag = true;
		} catch (BOSException e) {
			logger.error(e);
		}
		return flag;
	}

	public static boolean checkSubmitBillEntryIsExists(Context ctx, String billEntryTableName, String billId) {
		boolean flag = false;
		if (StringUtils.isEmpty(billId))
			return flag;
		String sql = "Select top 1 fid  from " + billEntryTableName + " where fbillId = ? ";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql, new Object[] { billId });
			if (rs.size() > 0)
				flag = true;
		} catch (BOSException e) {
			logger.error(e);
		}
		return flag;
	}

	public static boolean checkSubmitSchemeIsUsedBill(Context ctx, String schemeId) {
		boolean flag = false;
		if (StringUtils.isEmpty(schemeId))
			return flag;
		String sql = "Select top 1 fid  from T_HR_SBatchSubmitShemeBill where fbillstate in (1,2)  and FSubmitSchemeID = ? ";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql, new Object[] { schemeId });
			if (rs.size() > 0)
				flag = true;
		} catch (BOSException e) {
			logger.error(e);
		}
		return flag;
	}

	public static Map checkSubmitSchemesIsUsingBill(Context ctx, List<String> list) {
		Map<String, Boolean> map = new HashMap<>();
		String sql = "Select distinct FSubmitSchemeID from T_HR_SBatchSubmitShemeBill where fbillstate in (0,1,2)  and FSubmitSchemeID in( " + CmpStrUtil.convertListToString(list) + ") ";
		Set<String> snSet = new HashSet<>();
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next()) {
				String submitSchemeID = rs.getString("FSubmitSchemeID");
				snSet.add(submitSchemeID);
			}
			for (String submitSchemeID : list)
				map.put(submitSchemeID, Boolean.valueOf(snSet.contains(submitSchemeID)));
		} catch (Exception e) {
			logger.error(e);
		}
		return map;
	}

	public static Map<String, List> assembleSubmitSchemeColModel(Context ctx, String schemeId, Integer dataSource, String costTypeId) throws BOSException {
		Map<String, List> map = new HashMap<>();
		List<String> colNames = new ArrayList<>();
		List<Map<String, Object>> colModel = new ArrayList<>();
		if (StringUtils.isEmpty(schemeId))
			return map;
		String language = ctx.getLocale().getLanguage();
		Set<String> calItemSn = getCalItemSn(ctx, schemeId);
		Map<String, List<Map<String, Object>>> initAutoCalFieldOption = initAutoCalFieldOption(ctx, schemeId);
		String sql = "Select m.FNumber,e.FSubmitSchemeType,e.FSubmitTarget,e.FIsEnableSubmitElement,e.FEFFECTDATEDISPLAY , m.fieldSn,m.fdataType,m.fname_" + language + " MNAME,t.FIsRequired ,m.FDataDot,m.FDataLength,  M.FDATETYPE,M.FFILTERFIELD,B.FNAME_" + language + " BNAME, B.FUipk FMAINTAINUIPK,b.FISBASIC   from  T_HR_SCalSubmitScheme e  inner join  T_HR_SCalSubmitSchemeItem t on t.FCalSubmitSchemeID = e.fid  inner join T_HR_SCalSubmitItem m on  m.fid = t.FCalSubmitItemID  left join  T_HR_SCalSchemeItemForF7 b on m.FFilterField = b.fid  where e.fid = '" + schemeId + "' and t.FIsShow = 1 order by  t.FSortSn asc ";
		try {
			String submitTarget = "", dataType = "";
			int dateType = 0;
			int effectDateDisplay = 0;
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			String label45 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label45", ctx.getLocale());
			String label46 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label46", ctx.getLocale());
			String label47 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label47", ctx.getLocale());
			String label48 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label48", ctx.getLocale());
			String label49 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label49", ctx.getLocale());
			String label50 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label50", ctx.getLocale());
			String label51 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label51", ctx.getLocale());
			String label52 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label52", ctx.getLocale());
			String label53 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label53", ctx.getLocale());
			String label54 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label54", ctx.getLocale());
			String label55 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label55", ctx.getLocale());
			boolean f7Flag = true, isEnableSubmitElement = false;
			while (rs.next()) {
				submitTarget = rs.getString("FSubmitTarget");
				effectDateDisplay = rs.getInt("FEFFECTDATEDISPLAY");
				isEnableSubmitElement = rs.getBoolean("FIsEnableSubmitElement");
				dataType = rs.getString("fdataType");
				if (f7Flag) {
					f7Flag = false;
					Map<String, Object> gridMainKeyIdMap = new HashMap<>();
					Map<String, Object> adminOrgUnitGridMainKeyIdMap = new HashMap<>();
					Map<String, Object> positionGridMainKeyIdMap = new HashMap<>();
					Map<String, Object> cmpEmpORGridMainKeyIdMap = new HashMap<>();
					Map<String, Object> gridIdMap = new HashMap<>();
					assembleColModelId(gridIdMap);
					colNames.add("id");
					colModel.add(gridIdMap);
					if ("02".equals(submitTarget)) {
						assembleColModelF7(gridMainKeyIdMap, "orgUnit", label45, true);
						gridMainKeyIdMap.put("editoptions", assembleColModelF7(label45, "com.kingdee.eas.basedata.org.app.AdminOrgUnit.F7", true));
						gridMainKeyIdMap.put("sortable", Boolean.valueOf(true));
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label0", ctx.getLocale()));
						colModel.add(gridMainKeyIdMap);
					} else {
						Map<String, Object> map1 = new HashMap<>();
						map1.put("name", "number");
						map1.put("label", label46);
						map1.put("frozen", Boolean.valueOf(false));
						map1.put("width", Integer.valueOf(90));
						map1.put("align", "left");
						map1.put("key", Boolean.valueOf(true));
						map1.put("hidden", Boolean.valueOf(false));
						map1.put("editable", Boolean.valueOf(false));
						map1.put("sorttype", "string");
						map1.put("classes", "disabled");
						map1.put("edittype", "shrtext");
						map1.put("sortable", Boolean.valueOf(true));
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label1", ctx.getLocale()));
						colModel.add(map1);
						String CmpEmpORelF7Uipk = "com.kingdee.shr.compensation.app.integrate.CmpEmpORelation.F7";
						if (3 == dataSource.intValue())
							CmpEmpORelF7Uipk = "com.kingdee.shr.compensation.app.integrate.stores.CmpEmpORelation.F7";
						assembleColModelF7(gridMainKeyIdMap, "person", label47, true);
						gridMainKeyIdMap.put("editoptions", assembleColModelF7(label47, CmpEmpORelF7Uipk, true));
						gridIdMap.put("frozen", Boolean.valueOf(true));
						map1.put("frozen", Boolean.valueOf(true));
						gridMainKeyIdMap.put("frozen", Boolean.valueOf(true));
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label2", ctx.getLocale()));
						colModel.add(gridMainKeyIdMap);
						assembleColModelF7NotEdit(adminOrgUnitGridMainKeyIdMap, "adminOrgUnit", label48);
						adminOrgUnitGridMainKeyIdMap.put("sortable", Boolean.valueOf(true));
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label3", ctx.getLocale()));
						colModel.add(adminOrgUnitGridMainKeyIdMap);
						assembleColModelF7NotEdit(positionGridMainKeyIdMap, "position", label49);
						positionGridMainKeyIdMap.put("sortable", Boolean.valueOf(true));
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label4", ctx.getLocale()));
						colModel.add(positionGridMainKeyIdMap);
						assembleColModelF7Hidden(cmpEmpORGridMainKeyIdMap, "cmpEmpORelation", label50);
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label5", ctx.getLocale()));
						colModel.add(cmpEmpORGridMainKeyIdMap);
						Map<String, Object> gridPersonIdMap = new HashMap<>();
						gridPersonIdMap.put("name", "tempId");
						gridPersonIdMap.put("label", label51);
						gridPersonIdMap.put("frozen", Boolean.valueOf(false));
						gridPersonIdMap.put("width", Integer.valueOf(90));
						gridPersonIdMap.put("align", "left");
						gridPersonIdMap.put("key", Boolean.valueOf(true));
						gridPersonIdMap.put("hidden", Boolean.valueOf(true));
						gridPersonIdMap.put("editable", Boolean.valueOf(false));
						gridPersonIdMap.put("sorttype", "string");
						gridPersonIdMap.put("classes", "disabled");
						gridPersonIdMap.put("edittype", "shrtext");
						colNames.add("tempId");
						colModel.add(gridPersonIdMap);
					}
					if (isEnableSubmitElement) {
						Map<String, Object> gridSubmitElementMap = new HashMap<>();
						assembleColModelF7(gridSubmitElementMap, "submitElement", label52, true);
						gridSubmitElementMap.put("editoptions", assembleColModelF7(label52, "com.kingdee.shr.compensation.app.integrate.SubmitElement.SubmitBillEntry.F7", true));
						colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label6", ctx.getLocale()));
						colModel.add(gridSubmitElementMap);
					}
					Map<String, Object> gridCurrencyMap = new HashMap<>();
					assembleColModelF7(gridCurrencyMap, "currency", label53, true);
					gridCurrencyMap.put("editoptions", assembleColModelF7(label53, "com.kingdee.eas.basedata.assistant.app.Currency.CMP.F7", true));
					colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label7", ctx.getLocale()));
					colModel.add(gridCurrencyMap);
					Map<String, Object> gridPersonNumberMap = new HashMap<>();
					gridPersonNumberMap.put("name", "effectDay");
					gridPersonNumberMap.put("label", label54);
					gridPersonNumberMap.put("frozen", Boolean.valueOf(false));
					gridPersonNumberMap.put("width", Integer.valueOf(100));
					gridPersonNumberMap.put("align", "left");
					gridPersonNumberMap.put("key", Boolean.valueOf(true));
					gridPersonNumberMap.put("hidden", Boolean.valueOf(false));
					gridPersonNumberMap.put("editable", Boolean.valueOf(true));
					gridPersonNumberMap.put("sorttype", "date");
					gridPersonNumberMap.put("sortable", Boolean.valueOf(false));
					gridPersonNumberMap.put("formatter", "shrDateTimePicker");
					gridPersonNumberMap.put("edittype", "shrDateTimePicker");
					gridPersonNumberMap.put("classes", "required");
					StringBuilder datapickerjsonBuilder = new StringBuilder();
					datapickerjsonBuilder.append("{");
					datapickerjsonBuilder.append("\"datepickerjson\":");
					datapickerjsonBuilder.append("{");
					datapickerjsonBuilder.append("\"id\":\"dp_autoSize\",");
					datapickerjsonBuilder.append("\"name\":\"dp_autoSize\",");
					datapickerjsonBuilder.append("\"minuteStep\":\"\",");
					datapickerjsonBuilder.append("\"showTimeZoneForCtrl\":false,");
					datapickerjsonBuilder.append("\"isAutoTimeZoneTrans\":false,");
					datapickerjsonBuilder.append("\"isShowUTC\":false,");
					datapickerjsonBuilder.append("\"isReadonlyUTC\":true,");
					datapickerjsonBuilder.append("\"isRemoveSeconds\":true,");
					if (20 == effectDateDisplay) {
						datapickerjsonBuilder.append("\"isRemoveDay\":true,");
					} else if (10 == effectDateDisplay) {
						datapickerjsonBuilder.append("\"isRemoveDay\":false,");
					} else {
						datapickerjsonBuilder.append("\"isRemoveDay\":true,");
					}
					datapickerjsonBuilder.append("\"ctrlType\":\"Date\"");
					datapickerjsonBuilder.append("},");
					datapickerjsonBuilder.append("\"validateJson\":");
					datapickerjsonBuilder.append("{");
					datapickerjsonBuilder.append("\"errMsg\":\"\",");
					datapickerjsonBuilder.append("\"rules\":\"required:true\",");
					datapickerjsonBuilder.append("\"validatetrigger\":\"focus keyup datachange\"");
					datapickerjsonBuilder.append("}");
					datapickerjsonBuilder.append("}");
					gridPersonNumberMap.put("editoptions", JSONObject.parseObject(datapickerjsonBuilder.toString()));
					colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label8", ctx.getLocale()));
					colModel.add(gridPersonNumberMap);
				}
				dataType = rs.getString("fdataType");
				int isRequired = rs.getInt("FIsRequired");
				int fDataDot = rs.getInt("FDataDot");
				int fDataLength = rs.getInt("FDataLength");
				String fieldSn = "S" + rs.getString("fieldSn");
				if ("0".equals(dataType)) {
					Map<String, Object> schemeMap = new HashMap<>();
					schemeMap.put("name", fieldSn);
					schemeMap.put("label", rs.getString("MNAME"));
					schemeMap.put("frozen", Boolean.valueOf(false));
					schemeMap.put("width", Integer.valueOf(90));
					schemeMap.put("align", "left");
					schemeMap.put("key", Boolean.valueOf(false));
					schemeMap.put("editable", Boolean.valueOf(true));
					schemeMap.put("sorttype", "bigdecimal");
					schemeMap.put("sortable", Boolean.valueOf(false));
					schemeMap.put("formatter", "numberfield");
					schemeMap.put("edittype", "numberfield");
					JSONObject jSONObject1 = JSONObject.parseObject("{ \"inputJson\": { \"id\": \"txt_money\", \"name\": \"money\", \"formatType\": \"number\" }, \"validateJson\": {}}");
					if (isRequired == 1) {
						schemeMap.put("classes", "required");
						jSONObject1.put("validateJson", JSONObject.parseObject("{ \"errMsg\": \"\", \"rules\": \"required:true\", \"validatetrigger\": \"focus keyup datachange\" }"));
					}
					if (calItemSn.contains(fieldSn)) {
						schemeMap.put("editable", Boolean.valueOf(false));
						schemeMap.put("classes", "disabled");
					}
					JSONObject jSONObject2 = jSONObject1.getJSONObject("inputJson");
					jSONObject2.put("decimalPrecision", Integer.valueOf(fDataDot));
					jSONObject2.put("maxlength", Integer.valueOf(fDataLength));
					if (initAutoCalFieldOption.containsKey(fieldSn)) {
						jSONObject1.put("autoCal", Boolean.valueOf(true));
						jSONObject1.put("cellFomular", initAutoCalFieldOption.get(fieldSn));
					}
					schemeMap.put("editoptions", jSONObject1);
					colModel.add(schemeMap);
					colNames.add(rs.getString("MNAME"));
					continue;
				}
				if ("1".equals(dataType)) {
					Map<String, Object> schemeMap = new HashMap<>();
					schemeMap.put("name", fieldSn);
					schemeMap.put("label", rs.getString("MNAME"));
					schemeMap.put("frozen", Boolean.valueOf(false));
					schemeMap.put("width", Integer.valueOf(90));
					schemeMap.put("align", "left");
					schemeMap.put("key", Boolean.valueOf(false));
					schemeMap.put("editable", Boolean.valueOf(true));
					schemeMap.put("sorttype", "string");
					schemeMap.put("sortable", Boolean.valueOf(false));
					JSONObject jSONObject1 = JSONObject.parseObject("{ \"inputJson\": {  } }");
					if (isRequired == 1) {
						schemeMap.put("classes", "required");
						jSONObject1.put("validateJson", JSONObject.parseObject("{ \"errMsg\": \"\", \"rules\": \"required:true\", \"validatetrigger\": \"focus keyup datachange\" }"));
					} else {
						jSONObject1.put("validateJson", JSONObject.parseObject("{ \"errMsg\": \"\", \"rules\": \"required:false\", \"validatetrigger\": \"focus keyup datachange\" }"));
					}
					if (calItemSn.contains(fieldSn)) {
						schemeMap.put("editable", Boolean.valueOf(false));
						schemeMap.put("classes", "disabled");
					}
					JSONObject jSONObject2 = jSONObject1.getJSONObject("inputJson");
					jSONObject2.put("decimalPrecision", Integer.valueOf(fDataDot));
					jSONObject2.put("maxlength", Integer.valueOf(fDataLength));
					schemeMap.put("editoptions", jSONObject1);
					schemeMap.put("edittype", "shrtext");
					colModel.add(schemeMap);
					colNames.add(rs.getString("MNAME"));
					continue;
				}
				if ("2".equals(dataType)) {
					dateType = rs.getInt("FDATETYPE");
					Map<String, Object> schemeMap = new HashMap<>();
					schemeMap.put("name", fieldSn);
					schemeMap.put("label", rs.getString("MNAME"));
					schemeMap.put("frozen", Boolean.valueOf(false));
					schemeMap.put("width", Integer.valueOf(100));
					schemeMap.put("align", "left");
					schemeMap.put("key", Boolean.valueOf(true));
					schemeMap.put("hidden", Boolean.valueOf(false));
					schemeMap.put("editable", Boolean.valueOf(true));
					schemeMap.put("sorttype", "date");
					schemeMap.put("sortable", Boolean.valueOf(false));
					schemeMap.put("formatter", "shrDateTimePicker");
					schemeMap.put("edittype", "shrDateTimePicker");
					if (isRequired == 1)
						schemeMap.put("classes", "required");
					StringBuilder datapickerjsonBuffer = new StringBuilder();
					datapickerjsonBuffer.append("{");
					datapickerjsonBuffer.append("\"datepickerjson\":");
					datapickerjsonBuffer.append("{");
					datapickerjsonBuffer.append("\"id\":\"dp_autoSize\",");
					datapickerjsonBuffer.append("\"name\":\"dp_autoSize\",");
					datapickerjsonBuffer.append("\"minuteStep\":\"\",");
					datapickerjsonBuffer.append("\"showTimeZoneForCtrl\":false,");
					datapickerjsonBuffer.append("\"isAutoTimeZoneTrans\":false,");
					datapickerjsonBuffer.append("\"isShowUTC\":false,");
					datapickerjsonBuffer.append("\"isReadonlyUTC\":true,");
					datapickerjsonBuffer.append("\"isRemoveSeconds\":true,");
					if (20 == dateType) {
						datapickerjsonBuffer.append("\"isRemoveDay\":true,");
					} else if (10 == dateType) {
						datapickerjsonBuffer.append("\"isRemoveDay\":false,");
					} else {
						datapickerjsonBuffer.append("\"isRemoveDay\":false,");
					}
					datapickerjsonBuffer.append("\"ctrlType\":\"Date\"");
					datapickerjsonBuffer.append("},");
					datapickerjsonBuffer.append("\"validateJson\":");
					datapickerjsonBuffer.append("{");
					datapickerjsonBuffer.append("\"errMsg\":\"\",");
					if (isRequired == 1) {
						datapickerjsonBuffer.append("\"rules\":\"required:true\",");
					} else {
						datapickerjsonBuffer.append("\"rules\":\"required:false\",");
					}
					datapickerjsonBuffer.append("\"validatetrigger\":\"focus keyup datachange\"");
					datapickerjsonBuffer.append("}");
					datapickerjsonBuffer.append("}");
					schemeMap.put("editoptions", JSONObject.parseObject(datapickerjsonBuffer.toString()));
					colNames.add(rs.getString("MNAME"));
					colModel.add(schemeMap);
					continue;
				}
				if ("3".equals(dataType)) {
					Map<String, Object> schemeMap = new HashMap<>();
					assembleColModelF7(schemeMap, fieldSn, rs.getString("MNAME"), (isRequired == 1));
					String filter = "";
					if ("AwardName".equals(rs.getString("FNumber")) && StringUtils.isNotEmpty(costTypeId)) {
						filter = (rs.getInt("FISBASIC") == 1) ? ("CostType.id = '" + costTypeId + "' and state = 1") : "";
					} else {
						filter = (rs.getInt("FISBASIC") == 1) ? "state = 1" : "";
					}
					Map<String, Object> map1 = assembleColModelF7(rs.getString("BNAME"), rs.getString("FMAINTAINUIPK"), (isRequired == 1), filter);
					schemeMap.put("editoptions", map1);
					colNames.add(rs.getString("MNAME"));
					colModel.add(schemeMap);
				}
			}
			Map<String, Object> gridDescriptionMap = new HashMap<>();
			gridDescriptionMap.put("name", "description");
			gridDescriptionMap.put("label", label55);
			gridDescriptionMap.put("frozen", Boolean.valueOf(false));
			gridDescriptionMap.put("width", Integer.valueOf(90));
			gridDescriptionMap.put("align", "left");
			gridDescriptionMap.put("key", Boolean.valueOf(false));
			gridDescriptionMap.put("editable", Boolean.valueOf(true));
			gridDescriptionMap.put("sortable", Boolean.valueOf(false));
			gridDescriptionMap.put("formatter", "shrMultiLangBox");
			gridDescriptionMap.put("edittype", "shrMultiLangBox");
			JSONObject editoptions = JSONObject.parseObject("{ \"inputJson\": {  } }");
			JSONObject inputJson = editoptions.getJSONObject("inputJson");
			inputJson.put("maxlength", Integer.valueOf(255));
			gridDescriptionMap.put("editoptions", editoptions);
			colNames.add(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label9", ctx.getLocale()));
			colModel.add(gridDescriptionMap);
		} catch (Exception e) {
			throw new BOSException(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label10", ctx.getLocale()));
		}
		map.put("colNames", colNames);
		map.put("colModel", colModel);
		return map;
	}

	public static Map<String, Object> assembleColModelId(Map<String, Object> gridIdMap) {
		gridIdMap.put("name", "id");
		gridIdMap.put("label", "id");
		gridIdMap.put("frozen", Boolean.valueOf(false));
		gridIdMap.put("width", Integer.valueOf(90));
		gridIdMap.put("align", "left");
		gridIdMap.put("key", Boolean.valueOf(true));
		gridIdMap.put("hidden", Boolean.valueOf(true));
		gridIdMap.put("editable", Boolean.valueOf(false));
		gridIdMap.put("sorttype", "");
		gridIdMap.put("sortable", Boolean.valueOf(false));
		gridIdMap.put("classes", "disabled");
		return gridIdMap;
	}

	public static Map<String, Object> assembleColModelF7(String name, String uipk, boolean isRequired) {
		Map<String, Object> resultMap = new HashMap<>();
		Map<String, Object> f7JsonMap = new HashMap<>();
		Map<String, Object> validateJsonMap = new HashMap<>();
		Map<String, Object> uipkMap = new HashMap<>();
		uipkMap.put("title", name);
		uipkMap.put("uipk", uipk);
		uipkMap.put("query", "");
		uipkMap.put("filter", "");
		uipkMap.put("multiselect", Boolean.valueOf(false));
		uipkMap.put("isTree", Boolean.valueOf(false));
		uipkMap.put("treeUrl", "");
		uipkMap.put("isContainLowerOrg", Boolean.valueOf(false));
		uipkMap.put("isAdminOrg", Boolean.valueOf(false));
		uipkMap.put("isEnableExpFilter", Boolean.valueOf(true));
		f7JsonMap.put("subWidgetOptions", uipkMap);
		f7JsonMap.put("subWidgetName", "shrPromptGrid");
		f7JsonMap.put("searchFields", "");
		f7JsonMap.put("isHROrg", Boolean.valueOf(false));
		f7JsonMap.put("isAdminOrg", Boolean.valueOf(false));
		f7JsonMap.put("searchLikePattern", "any");
		resultMap.put("f7Json", f7JsonMap);
		validateJsonMap.put("errMsg", "");
		if (isRequired)
			validateJsonMap.put("rules", "required:true");
		validateJsonMap.put("validatetrigger", "focus keyup datachange");
		resultMap.put("validateJson", validateJsonMap);
		return resultMap;
	}

	public static Map<String, Object> assembleColModelF7(String name, String uipk, boolean isRequired, String filter) {
		Map<String, Object> resultMap = new HashMap<>();
		Map<String, Object> f7JsonMap = new HashMap<>();
		Map<String, Object> validateJsonMap = new HashMap<>();
		Map<String, Object> uipkMap = new HashMap<>();
		uipkMap.put("title", name);
		uipkMap.put("uipk", uipk);
		uipkMap.put("query", "");
		uipkMap.put("filter", filter);
		uipkMap.put("multiselect", Boolean.valueOf(false));
		uipkMap.put("isTree", Boolean.valueOf(false));
		uipkMap.put("treeUrl", "");
		uipkMap.put("isContainLowerOrg", Boolean.valueOf(false));
		uipkMap.put("isAdminOrg", Boolean.valueOf(false));
		f7JsonMap.put("subWidgetOptions", uipkMap);
		f7JsonMap.put("subWidgetName", "shrPromptGrid");
		f7JsonMap.put("searchFields", "");
		f7JsonMap.put("isHROrg", Boolean.valueOf(false));
		f7JsonMap.put("isAdminOrg", Boolean.valueOf(false));
		f7JsonMap.put("searchLikePattern", "any");
		resultMap.put("f7Json", f7JsonMap);
		validateJsonMap.put("errMsg", "");
		if (isRequired)
			validateJsonMap.put("rules", "required:true");
		validateJsonMap.put("validatetrigger", "focus keyup datachange");
		resultMap.put("validateJson", validateJsonMap);
		return resultMap;
	}

	public static Map<String, Object> assembleColModelF7(Map<String, Object> gridMainKeyIdMap, String name, String label, boolean isRequired) {
		gridMainKeyIdMap.put("name", name);
		gridMainKeyIdMap.put("label", label);
		gridMainKeyIdMap.put("frozen", Boolean.valueOf(false));
		gridMainKeyIdMap.put("width", Integer.valueOf(120));
		gridMainKeyIdMap.put("align", "left");
		gridMainKeyIdMap.put("key", Boolean.valueOf(false));
		gridMainKeyIdMap.put("edittype", "promptBox");
		gridMainKeyIdMap.put("editable", Boolean.valueOf(true));
		gridMainKeyIdMap.put("sorttype", "objectvalue");
		gridMainKeyIdMap.put("sortable", Boolean.valueOf(false));
		gridMainKeyIdMap.put("formatter", "promptBox");
		if (isRequired)
			gridMainKeyIdMap.put("classes", "required");
		return gridMainKeyIdMap;
	}

	public static Map<String, Object> assembleColModelF7NotEdit(Map<String, Object> gridMainKeyIdMap, String name, String label) {
		gridMainKeyIdMap.put("name", name);
		gridMainKeyIdMap.put("label", label);
		gridMainKeyIdMap.put("frozen", Boolean.valueOf(false));
		gridMainKeyIdMap.put("width", Integer.valueOf(120));
		gridMainKeyIdMap.put("align", "left");
		gridMainKeyIdMap.put("key", Boolean.valueOf(false));
		gridMainKeyIdMap.put("edittype", "promptBox");
		gridMainKeyIdMap.put("editable", Boolean.valueOf(false));
		gridMainKeyIdMap.put("sorttype", "objectvalue");
		gridMainKeyIdMap.put("sortable", Boolean.valueOf(false));
		gridMainKeyIdMap.put("formatter", "promptBox");
		gridMainKeyIdMap.put("classes", "disabled");
		return gridMainKeyIdMap;
	}

	public static Map<String, Object> assembleColModelF7Hidden(Map<String, Object> gridMainKeyIdMap, String name, String label) {
		gridMainKeyIdMap.put("name", name);
		gridMainKeyIdMap.put("label", label);
		gridMainKeyIdMap.put("frozen", Boolean.valueOf(false));
		gridMainKeyIdMap.put("width", Integer.valueOf(120));
		gridMainKeyIdMap.put("hidden", Boolean.valueOf(true));
		gridMainKeyIdMap.put("align", "left");
		gridMainKeyIdMap.put("key", Boolean.valueOf(false));
		gridMainKeyIdMap.put("edittype", "promptBox");
		gridMainKeyIdMap.put("editable", Boolean.valueOf(false));
		gridMainKeyIdMap.put("sorttype", "objectvalue");
		gridMainKeyIdMap.put("sortable", Boolean.valueOf(false));
		gridMainKeyIdMap.put("formatter", "promptBox");
		gridMainKeyIdMap.put("classes", "disabled");
		return gridMainKeyIdMap;
	}

	public static Map<String, Object> getBillColumnListAndQuerySql(Context ctx, String submitSchemeId, String billID) throws BOSException {
		Map<String, Object> map = new HashMap<>();
		List<String> colNames = new ArrayList<>();
		Map<String, Object> columnMap = new HashMap<>();
		if (StringUtils.isEmpty(submitSchemeId))
			return map;
		if (null != billID && billID.contains(" "))
			throw new BOSException("Illegal parameter: " + billID);
		try {
			String sqlBuilder = " SELECT   item.fieldSn FIELDSN,  b.FTable FTABLE FROM T_HR_SCalSubmitScheme scheme  inner join T_HR_SCalSubmitSchemeItem schemeItem on scheme.fid = schemeItem.FCalSubmitSchemeID  inner join T_HR_SCalSubmitItem item on item.fid = schemeItem.FCalSubmitItemID  inner join T_HR_SCalSchemeItemForF7 b on item.FFilterField = b.fid  where scheme.fid = ? and item.FDataType = '3' order by  FSortSn";
			IRowSet rs = DbUtil.executeQuery(ctx, sqlBuilder, (Object[])new String[] { submitSchemeId });
			Map<String, Map<String, String>> f7ItemTableMapper = Maps.newHashMap();
			int tableNameCount = 1;
			while (rs.next()) {
				Map<String, String> tableNameMap = new HashMap<>();
				tableNameMap.put("TABLENAME", rs.getString("FTABLE"));
				tableNameMap.put("ALIANAME", rs.getString("FTABLE") + tableNameCount);
				f7ItemTableMapper.put("S" + rs.getInt("FIELDSN"), tableNameMap);
				tableNameCount++;
			}
			String language = ctx.getLocale().getLanguage();
			String sql = "Select e.FSubmitSchemeType,e.FSubmitTarget,e.FIsEnableSubmitElement , m.fieldSn,m.FDataDot,m.fdataType,m.fname_" + language + " ,n.FBillEntryTableName from  T_HR_SCalSubmitScheme e  inner join  T_HR_SCalSubmitSchemeItem t on t.FCalSubmitSchemeID = e.fid  inner join T_HR_SCalSubmitItem m on  m.fid = t.FCalSubmitItemID  inner join T_HR_SCalShemeTableRelation n on n.FSubmitSchemeID =e.fid   where e.fid = '" + submitSchemeId + "'  order by FSortSn ";
			StringBuilder sd = new StringBuilder(" select t.fid id ,  t.FEffectDate effectDate ,t.FLEffectDate leffectDate , t.FCmpEmpORelationID cmpEmpORelation,");
			colNames.add("id");
			colNames.add("effectDate");
			colNames.add("leffectDate");
			String submitTarget = "", fBillEntryTableName = "";
			rs = DbUtil.executeQuery(ctx, sql);
			boolean f7Flag = true, personFlag = true, isEnableSubmitElement = false;
			while (rs.next()) {
				submitTarget = rs.getString("FSubmitTarget");
				isEnableSubmitElement = rs.getBoolean("FIsEnableSubmitElement");
				if (f7Flag) {
					f7Flag = false;
					fBillEntryTableName = rs.getString("FBillEntryTableName");
					if ("02".equals(submitTarget)) {
						personFlag = false;
						colNames.add("orgId");
						colNames.add("name");
						colNames.add("number");
						sd.append("n.fid orgId , n.fname_").append(language).append(" name , n.fnumber number ");
					} else {
						colNames.add("dealStatus");
						colNames.add("personId");
						colNames.add("name");
						colNames.add("number");
						colNames.add("admOrgId");
						colNames.add("admOrgName");
						colNames.add("positionId");
						colNames.add("positionName");
						colNames.add("cmpEmpORelation");
						sd.append(" t.FDealStatus dealStatus, person.fid personId , person.fname_").append(language).append("  name, person.fnumber number, adminOrgUnit.fid admOrgId, adminOrgUnit.fname_").append(language).append(" admOrgName, position.fid positionId, position.fname_").append(language).append(" positionName  ");
					}
				}
				String fieldName = "S" + rs.getString("fieldSn");
				if (f7ItemTableMapper.containsKey(fieldName)) {
					Map<String, String> map2 = f7ItemTableMapper.get(fieldName);
					String ailaName = map2.get("ALIANAME");
					sd.append(",").append(ailaName).append(".FID ").append(fieldName).append("id,").append(ailaName).append(".FNAME_").append(language).append(" ").append(fieldName).append(",").append(ailaName).append(".FNUMBER ").append(fieldName).append("number");
				} else {
					sd.append(",").append(fieldName);
				}
				colNames.add(fieldName);
				if ("0".equals(rs.getString("fdataType")))
					columnMap.put(fieldName, Integer.valueOf(rs.getInt("FDataDot")));
			}
			colNames.add("description");
			if (isEnableSubmitElement) {
				colNames.add("submitElement");
				sd.append(", t.FSubmitElementID submitElement,submitElement.Fname_").append(language).append(" submitElementName ");
			}
			colNames.add("currency");
			sd.append(",currency.fid currency,currency.fname_").append(language).append(" currencyName,t.fdescription_l1 description_l1,t.fdescription_l2 description_l2,t.fdescription_l3 description_l3");
			
			// start xiaoxin 添加提报单分录字段
			sd.append(",se.CFSuperiorID superiorId,se.CFSuperiorName superiorName,se.CFHourlyWage hourlyWage,se.CFStartTime startTime,se.CFEndTime endTime,se.CFRestStartTime restStartTime,se.CFRemark remark");
			sd.append(",se.CFRestEndTime restEndTime,se.CFExplain explain,se.CFTotalWorkHours totalWorkHours,se.CFTotalAmount totalAmount,se.CFBeginContractDate beginContractDate,se.CFEndContractDate endContractDate");
			colNames.add("superiorId"); 
			colNames.add("superiorName");
			colNames.add("hourlyWage");
			colNames.add("startTime");
			colNames.add("endTime");
			colNames.add("restStartTime");
			colNames.add("restEndTime");
			colNames.add("explain");
			colNames.add("totalWorkHours");
			colNames.add("totalAmount");
			colNames.add("beginContractDate");
			colNames.add("endContractDate");
			colNames.add("remark");
			// end xiaoxin
			
			sd.append(" from ").append(fBillEntryTableName).append(" ");
			if (personFlag) {
				sd.append(" t  inner join t_bd_person person on t.fpersonId = person.fid ");
				sd.append(" LEFT join T_ORG_Admin adminOrgUnit on t.FAdminOrgUnitID = adminOrgUnit.fid ");
				sd.append(" LEFT join T_ORG_Position position on t.FPositionID = position.fid ");
			} else {
				sd.append(" t  inner join T_ORG_Admin  n on t.FOrgUnitID = n.fid ");
			}
			sd.append(" LEFT join T_BD_Currency currency on t.fCurrencyID = currency.fid ");
			
			// start xiaoxin 添加提报单分录字段
			sd.append(" LEFT join T_HR_SchemeBillEntry se on se.fid = t.fid ");
			// end xiaoxin
			
			if (isEnableSubmitElement)
				sd.append(" LEFT join T_HR_SSubmitElement submitElement on t.FSubmitElementID = submitElement.fid ");
			Set<String> f7KeySet = f7ItemTableMapper.keySet();
			for (String f7Key : f7KeySet) {
				Map<String, String> map2 = f7ItemTableMapper.get(f7Key);
				String tableName = map2.get("TABLENAME");
				String aliaTbName = map2.get("ALIANAME");
				sd.append(" LEFT JOIN ").append(tableName).append(" ").append(aliaTbName).append(" on t.").append(f7Key).append(" = ").append(aliaTbName).append(".FID ");
			}
			sd.append(" where t.fbillid = '").append(StringEscapeUtils.escapeSql(billID)).append("'");
			map.put("querySql", sd.toString());
			map.put("colNames", colNames);
			map.put("columnMap", columnMap);
		} catch (Exception e) { 
			throw new BOSException(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label10", ctx.getLocale()));
		}
		return map;
	}

	public static Map<String, Object> getBillColumnListAndBillEntryTableName(Context ctx, String submitSchemeId) throws BOSException {
		Map<String, Object> map = new HashMap<>();
		List<String> colNames = new ArrayList<>();
		List<String> colTypes = new ArrayList<>();
		if (StringUtils.isEmpty(submitSchemeId))
			return map;
		String language = ctx.getLocale().getLanguage();
		String sql = "Select e.FSubmitTarget , m.fieldSn,m.fdataType,m.fname_" + language + " ,n.FBillEntryTableName ,n.FCalShemeTableName ,t.FIsRequired from  T_HR_SCalSubmitScheme e  inner join  T_HR_SCalSubmitSchemeItem t on t.FCalSubmitSchemeID = e.fid  inner join T_HR_SCalSubmitItem m on  m.fid = t.FCalSubmitItemID  inner join T_HR_SCalShemeTableRelation n on n.FSubmitSchemeID =e.fid   where e.fid = '" + submitSchemeId + "'  order by FSortSn ";
		try {
			String fBillEntryTableName = "";
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next()) {
				if (StringUtils.isEmpty(fBillEntryTableName))
					fBillEntryTableName = rs.getString("FBillEntryTableName");
				colNames.add("S" + rs.getString("fieldSn"));
				colTypes.add(rs.getString("fdataType"));
			}
			map.put("fBillEntryTableName", fBillEntryTableName);
			map.put("colNames", colNames);
			map.put("colTypes", colTypes);
		} catch (Exception e) {
			throw new BOSException(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label10", ctx.getLocale()));
		}
		return map;
	}

	public static Map<String, Object> getBillColumnListAndBillEntryTableNameByDataType(Context ctx, String submitSchemeId, String dataType) throws BOSException {
		Map<String, Object> map = new HashMap<>();
		List<String> colNames = new ArrayList<>();
		if (StringUtils.isEmpty(submitSchemeId))
			return map;
		String language = ctx.getLocale().getLanguage();
		String sql = "Select e.FSubmitTarget , m.fieldSn,m.fdataType,m.fname_" + language + " ,n.FBillEntryTableName ,n.FCalShemeTableName ,t.FIsRequired from  T_HR_SCalSubmitScheme e  inner join  T_HR_SCalSubmitSchemeItem t on t.FCalSubmitSchemeID = e.fid  inner join T_HR_SCalSubmitItem m on  m.fid = t.FCalSubmitItemID  inner join T_HR_SCalShemeTableRelation n on n.FSubmitSchemeID =e.fid   where e.fid = '" + submitSchemeId + "' and m.fdataType = '" + dataType + "'  order by FSortSn ";
		try {
			String fBillEntryTableName = "", fCalShemeTableName = "";
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next()) {
				if (StringUtils.isEmpty(fBillEntryTableName)) {
					fBillEntryTableName = rs.getString("FBillEntryTableName");
					fCalShemeTableName = rs.getString("FCalShemeTableName");
				}
				colNames.add("S" + rs.getString("fieldSn"));
			}
			map.put("fBillEntryTableName", fBillEntryTableName);
			map.put("fCalShemeTableName", fCalShemeTableName);
			map.put("colNames", colNames);
		} catch (Exception e) {
			throw new BOSException(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label10", ctx.getLocale()));
		}
		return map;
	}

	public static String getSchemeEntryTableName(Context ctx, String submitSchemeId) {
		String fBillEntryTableName = "";
		String sql = "Select n.FBillEntryTableName  from  T_HR_SCalSubmitScheme e  inner join T_HR_SCalShemeTableRelation n on n.FSubmitSchemeID =e.fid   where e.fid = '" + submitSchemeId + "'  ";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next())
				fBillEntryTableName = rs.getString("FBillEntryTableName");
		} catch (Exception e) {
			logger.info(e);
		}
		return fBillEntryTableName;
	}

	public static String getCalShemeTableName(Context ctx, String submitSchemeId) {
		String calShemeTableName = "";
		String sql = "Select n.FBillEntryTableName  from  T_HR_SCalSubmitScheme e  inner join T_HR_SCalShemeTableRelation n on n.FSubmitSchemeID =e.fid   where e.fid = '" + submitSchemeId + "'  ";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next())
				calShemeTableName = rs.getString("FBillEntryTableName");
		} catch (Exception e) {
			logger.info(e);
		}
		return calShemeTableName;
	}

	public static boolean deleteSubmitSchemeRelationDate(Context ctx, String schemeId) throws BOSException, SQLException {
		boolean flag = false;
		if (StringUtils.isEmpty(schemeId))
			return flag;
		String sql = "select FBillEntryTableName , FCalShemeTableName  from T_HR_SCalShemeTableRelation where FSubmitSchemeID = ?";
		String calShemeTableName = "";
		String billEntryTableName = "";
		IRowSet list = DbUtil.executeQuery(ctx, sql, new Object[] { schemeId });
		while (list.next()) {
			billEntryTableName = list.getString("FBillEntryTableName");
			calShemeTableName = list.getString("FCalShemeTableName");
		}
		if (!CmpStrUtil.isEmpty(calShemeTableName)) {
			DbUtil.execute(ctx, "If exists (select * from KSQL_USERTABLES where KSQL_TABNAME ='" + calShemeTableName + "') drop table " + calShemeTableName + "");
			DbUtil.execute(ctx, "If exists (select * from KSQL_USERTABLES where KSQL_TABNAME ='" + billEntryTableName + "') drop table " + billEntryTableName + "");
		}
		DbUtil.execute(ctx, "delete   from T_HR_SCalShemeTableRelation where FSubmitSchemeID = ? ", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SCalSubmitSchemeItem where FCalSubmitSchemeID = ?", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SBaseFormula where FBusinessRelationConfigID in(select fid from T_HR_SBusinessRelationConfig where FBusinessId = ?)", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SBusinessRelationConfig where FBusinessId = ?", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SSchemeDeduConfig where FCalSubmitSchemeID = ?", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SSubmitSchemeVRuleNodeRel where FSubmitSchemeVRuleID in(select fid from T_HR_SSubSchemeVerifiRule where FSubmitSchemeID = ? )", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SSubSchemeVerifiRule where FSubmitSchemeID = ?", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SCalSubmitPeriod where FSubmitSchemeId = ?", new Object[] { schemeId });
		DbUtil.execute(ctx, "delete   from T_HR_SchemeBizKeyDefine where FSubmitSchemeId = ?", new Object[] { schemeId });
		return flag;
	}

	public static boolean dropSubmitSchemeEntryTableCol(Context ctx, String schemeId, String itemId) throws BOSException, SQLException {
		boolean flag = false;
		if (StringUtils.isEmpty(schemeId))
			return flag;
		String sql = "select FBillEntryTableName , FCalShemeTableName  from T_HR_SCalShemeTableRelation where FSubmitSchemeID = '" + schemeId + "'";
		String sqlItem = "select fieldSn from  T_HR_SCalSubmitItem m  inner join  T_HR_SCalSubmitSchemeItem t on m.fid = t.FCalSubmitItemID  where t.fid  = '" + itemId + "'";
		String calShemeTableName = "", billEntryTableName = "", col = "";
		IRowSet rs = DbUtil.executeQuery(ctx, sql);
		while (rs.next()) {
			billEntryTableName = rs.getString("FBillEntryTableName");
			calShemeTableName = rs.getString("FCalShemeTableName");
		}
		rs = DbUtil.executeQuery(ctx, sqlItem);
		while (rs.next())
			col = "S" + rs.getString("fieldSn");
		StringBuilder calShemeTable = new StringBuilder("If  exists (select * from KSQL_USERCOLUMNS where KSQL_COL_NAME ='" + col + "'  and KSQL_COL_TABNAME='" + calShemeTableName + "')");
		calShemeTable.append("ALTER TABLE " + calShemeTableName + " drop column " + col + " ");
		StringBuilder billEntryTable = new StringBuilder("If  exists (select * from KSQL_USERCOLUMNS where KSQL_COL_NAME ='" + col + "'  and KSQL_COL_TABNAME='" + billEntryTableName + "')");
		billEntryTable.append("ALTER TABLE " + billEntryTableName + " drop column " + col + " ");
		if (!CmpStrUtil.isEmpty(calShemeTableName)) {
			DbUtil.execute(ctx, calShemeTable.toString());
			DbUtil.execute(ctx, billEntryTable.toString());
		}
		return flag;
	}

	public static Map<String, Object> getRequiredColumns(Context ctx, String submitSchemeId, boolean needEnableData) throws BOSException {
		Map<String, Object> map = new HashMap<>();
		List<Map<String, Object>> colNames = new ArrayList<>();
		if (StringUtils.isEmpty(submitSchemeId))
			return map;
		String language = ctx.getLocale().getLanguage();
		String sql = "Select e.FSubmitTarget , m.fieldSn,m.fdataType,m.fname_" + language + " MNAME,n.FBillEntryTableName ,n.FCalShemeTableName ,t.FIsRequired,t.fstate state from  T_HR_SCalSubmitScheme e  inner join  T_HR_SCalSubmitSchemeItem t on t.FCalSubmitSchemeID = e.fid  inner join T_HR_SCalSubmitItem m on  m.fid = t.FCalSubmitItemID  inner join T_HR_SCalShemeTableRelation n on n.FSubmitSchemeID =e.fid   where e.fid = '" + submitSchemeId + "'";
		if (needEnableData)
			sql = sql + "and t.fstate = 1 ";
		sql = sql + " order by t.FSortSn asc ";
		String submitTarget = null;
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next()) {
				Map<String, Object> gridIdMap = new HashMap<>();
				gridIdMap.put("rename", rs.getString("MNAME"));
				gridIdMap.put("name", "CMP_SMT002.S" + rs.getString("fieldSn"));
				gridIdMap.put("label", rs.getString("MNAME"));
				gridIdMap.put("type", "String");
				submitTarget = rs.getString("FSubmitTarget");
				colNames.add(gridIdMap);
			}
			map.put("colNames", colNames);
			map.put("submitTarget", submitTarget);
		} catch (Exception e) {
			throw new BOSException(EASResource.getString("com.kingdee.shr.compensation.resource.SubmitShemeBillResource", "label10", ctx.getLocale()));
		}
		return map;
	}

	public static List<CalShemeSubmitItemDTO> getDynamicSubmitItemBySchemeId(Context ctx, String schemeId) {
		List<CalShemeSubmitItemDTO> calShemeSubmitItemDTOS = new ArrayList<>();
		StringBuilder sqlBuffer = new StringBuilder();
		String language = ctx.getLocale().getLanguage();
		sqlBuffer.append(" SELECT M.FNUMBER AS ITEMNUMBER,E.FID AS SCHEMEID, M.FID as ITEMID,  M.FIELDSN,M.FDATATYPE,M.FNAME_" + language + " AS ITEMNAME,T.FISREQUIRED ,M.FDATADOT,M.FDATALENGTH ");
		sqlBuffer.append(" FROM  T_HR_SCALSUBMITSCHEME E  ");
		sqlBuffer.append(" INNER JOIN  T_HR_SCALSUBMITSCHEMEITEM T ON T.FCALSUBMITSCHEMEID = E.FID  ");
		sqlBuffer.append(" INNER JOIN T_HR_SCALSUBMITITEM M ON  M.FID = T.FCALSUBMITITEMID WHERE T.FISSHOW=1  AND T.FState= ").append(1).append(" AND E.FID = '");
		sqlBuffer.append(schemeId);
		sqlBuffer.append("' ORDER BY T.FSORTSN");
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sqlBuffer.toString());
			CalShemeSubmitItemDTO calShemeSubmitItemDTO = null;
			while (rs.next()) {
				calShemeSubmitItemDTO = new CalShemeSubmitItemDTO();
				calShemeSubmitItemDTO.setSubmitSchemeId(rs.getString("SCHEMEID"));
				calShemeSubmitItemDTO.setItemId(rs.getString("ITEMID"));
				calShemeSubmitItemDTO.setFieldSn(Integer.valueOf(rs.getInt("FIELDSN")));
				calShemeSubmitItemDTO.setDataType(rs.getString("FDATATYPE"));
				calShemeSubmitItemDTO.setSubmitItemName(rs.getString("ITEMNAME"));
				calShemeSubmitItemDTO.setIsRequired(Integer.valueOf(rs.getInt("FISREQUIRED")));
				calShemeSubmitItemDTO.setDataDot(Integer.valueOf(rs.getInt("FDATADOT")));
				calShemeSubmitItemDTO.setDataLength(Integer.valueOf(rs.getInt("FDATALENGTH")));
				calShemeSubmitItemDTO.setSubmitItemNumber(rs.getString("ITEMNUMBER"));
				calShemeSubmitItemDTOS.add(calShemeSubmitItemDTO);
			}
		} catch (BOSException e) {
			e.printStackTrace();
			logger.error(e.getMessage(), (Throwable)e);
		} catch (SQLException e) {
			logger.error(e.getMessage(), e);
			e.printStackTrace();
		}
		return calShemeSubmitItemDTOS;
	}

	public static Map<String, Integer> getCellLength(Context ctx, String schemeId) {
		Map<String, Integer> map = new HashMap<>();
		String sql = "Select m.fieldSn,m.FDataLength from  T_HR_SCalSubmitScheme e  inner join  T_HR_SCalSubmitSchemeItem t on t.FCalSubmitSchemeID = e.fid  inner join T_HR_SCalSubmitItem m on  m.fid = t.FCalSubmitItemID  where e.fid = '" + schemeId + "' and FDataType = '1' order by  FSortSn";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sql);
			while (rs.next()) {
				String fieldSn = String.valueOf(rs.getInt(1));
				Integer dataLength = Integer.valueOf(rs.getInt(2));
				map.put(fieldSn, dataLength);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}

	public static Set<String> getCalItemSn(Context ctx, String schemeId) throws BOSException {
		Set<String> calItemSn = new HashSet<>();
		String formulaSql = "SELECT bf.FCalItem calItem FROM T_HR_SBaseFormula bf LEFT JOIN T_HR_SBusinessRelationConfig brc ON bf.FBUSINESSRELATIONCONFIGID = brc.FID WHERE brc.FBUSINESSID = '" + schemeId + "' AND bf.FISCAL = 1";
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, formulaSql);
			while (rs.next()) {
				String sn = rs.getString("calItem");
				if (null != sn || !"".equals(sn))
					calItemSn.add(sn);
			}
		} catch (SQLException e) {
			String label56 = EASResource.getString("com.kingdee.shr.compensation.resource.CmpXQLangResource", "label56", ctx.getLocale());
			throw new BOSException(label56, e);
		}
		return calItemSn;
	}

	public static Map<String, List<Map<String, Object>>> initAutoCalFieldOption(Context ctx, String schemeId) {
		StringBuilder sqlBuffer = new StringBuilder();
		sqlBuffer.append(" SELECT FORMULA.FCALITEM, FORMULA.FSORTSN, FORMULA.FEXPRESS, FORMULA.FLISTITEM, ");
		sqlBuffer.append(" ITEM.FDATADOT FDATADOT ");
		sqlBuffer.append(" FROM T_HR_SBUSINESSRELATIONCONFIG CONFIG  ");
		sqlBuffer.append(" INNER JOIN T_HR_SBASEFORMULA FORMULA ON FORMULA.FBUSINESSRELATIONCONFIGID = CONFIG.FID ");
		sqlBuffer.append(" LEFT JOIN T_HR_SCalSubmitItem ITEM ON FORMULA.FCALITEM =  CONCAT('S',CONVERT(VARCHAR,ITEM.FIELDSN))  ");
		sqlBuffer.append(" WHERE ");
		sqlBuffer.append(" CONFIG.FBUSINESSID = '").append(schemeId).append("' ");
		sqlBuffer.append(" AND FORMULA.FISCAL = 1 ");
		sqlBuffer.append(" AND FORMULA.FEXPRESS IS NOT NULL ");
		sqlBuffer.append(" AND FORMULA.FCALITEM IS NOT NULL ");
		sqlBuffer.append(" AND (FORMULA.FLISTFUNC IS NULL OR FORMULA.FLISTFUNC LIKE '') ");
		sqlBuffer.append(" AND (FORMULA.FLISTVAR IS NULL OR FORMULA.FLISTVAR LIKE '') ");
		sqlBuffer.append(" AND ITEM.FDATATYPE = '").append("0").append("' ");
		sqlBuffer.append(" ORDER BY FORMULA.FSORTSN ");
		String tableName = getSchemeEntryTableName(ctx, schemeId);
		HashMap<String, List<Map<String, Object>>> result = new HashMap<>();
		HashMap<String, List<Map<String, Object>>> realResult = new HashMap<>();
		try {
			IRowSet rs = DbUtil.executeQuery(ctx, sqlBuffer.toString());
			String calItem = "", express = "", listItems = "";
			int targetDataDot = 2;
			while (rs.next()) {
				Map<String, Object> targetFieldMap = new HashMap<>();
				calItem = rs.getString("FCALITEM");
				targetDataDot = rs.getInt("FDATADOT");
				targetFieldMap.put(TARGET_NAME_STR, calItem);
				targetFieldMap.put(TARGET_PRECISION_STR, Integer.valueOf(targetDataDot));
				express = rs.getString("FEXPRESS");
				listItems = rs.getString("FLISTITEM");
				if (express.contains("if"))
					continue;
				express = express.replace(tableName, "").replace("_", "");
				express = express.substring(express.indexOf("=") + 1);
				listItems = listItems.replace(tableName, "").replace("double", "").replace("~", "");
				String[] listItemsArr = listItems.split(";");
				if (listItemsArr.length == 1 && listItems.equals(calItem))
					continue;
				List<String> realListItems = new ArrayList<>(listItemsArr.length - 1);
				for (String str : listItemsArr) {
					if (!str.equals(calItem))
						realListItems.add(str);
				}
				for (String str : realListItems) {
					List<Map<String, Object>> perFieldFormulas;
					if (result.containsKey(str)) {
						perFieldFormulas = result.get(str);
					} else {
						perFieldFormulas = new ArrayList<>();
					}
					Map<String, Object> perFormula = new HashMap<>();
					perFormula.put(FIELDS_STR, realListItems);
					perFormula.put(FORMULA_STR, express);
					perFormula.put(TARGET_STR, targetFieldMap);
					perFieldFormulas.add(perFormula);
					result.put(str, perFieldFormulas);
				}
			}
			Set<String> keySet = result.keySet();
			Iterator<String> iterator = keySet.iterator();
			String listItem = "", tempItem = "";
			String resultStr = JSONUtils.convertObjectToJson(ctx, result);
			realResult.putAll(JSONUtils.convertJsonToObject(ctx, resultStr));
			while (iterator.hasNext()) {
				listItem = iterator.next();
				List<Map<String, Object>> perFieldFormulas = result.get(listItem);
				Iterator<Map<String, Object>> iterator2 = perFieldFormulas.iterator();
				while (iterator2.hasNext()) {
					tempItem = (String)((Map)((Map)iterator2.next()).get(TARGET_STR)).get(TARGET_NAME_STR);
					if (result.containsKey(tempItem))
						((List)realResult.get(listItem)).addAll(result.get(tempItem));
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
		}
		return realResult;
	}

	public static void checkHRBillIsEdit(Context ctx, String billId) throws BOSException, EASBizException {
		if (!StringUtils.isEmpty(billId)) {
			BatchSubmitShemeBillCollection billInfoC = BatchSubmitShemeBillFactory.getLocalInstance(ctx).getBatchSubmitShemeBillCollection("select  id,billState where id = '" + billId + "'");
			BatchSubmitShemeBillInfo billInfo = billInfoC.isEmpty() ? null : billInfoC.get(0);
			HRBillStateEnum oldBillState = billInfoC.isEmpty() ? null : billInfoC.get(0).getBillState();
			HRWorkFlowCheckUtil.checkHRBillIsEdit(ctx, (HRBillBaseInfo)billInfo, oldBillState);
		}
	}
}