oa/plugins/bpm/com/yw/bpm/form/dao/FormDaoImpl.java

package com.yw.bpm.form.dao;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.fileupload.FileItem;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import com.yw.core.common.BaseDao;
import com.yw.core.framework.view.service.PagerService;
import com.yw.core.utils.CommonUtil;
import com.yw.core.utils.StringUtil;
import com.yw.eu.base.system.model.UploadFile;
import com.yw.bpm.form.dao.FormDaoImpl.FormElementMapperAddBDDZ;
import com.yw.bpm.form.model.FormElement;
import com.yw.bpm.form.model.FormInfo;
import com.yw.bpm.form.model.FormInfopath;
import com.yw.bpm.instance.model.GenFile;
import com.yw.bpm.system.constant.ExceptionDefine;
import com.yw.bpm.system.util.SpringUtil;

public class FormDaoImpl extends BaseDao implements FormDao {

	@Resource
	private PagerService pagerService;
	@Resource
	private JdbcTemplate jdbcTemplate;

	private StringBuffer standardSqlForFormInfo = new StringBuffer("select ").append("t.l_form_id, t.l_form_vid, t.v_form_name, ").append("t.l_file_id, t.n_version, t.v_creator, b.name, ")
			.append("t.d_createdate, t.n_state, c.v_data_name as v_state_name, ").append("t.v_table, t.v_html, ").append("t.v_remark, t.n_has_gen, a.v_data_name as n_has_gen_name, ")
			.append("t.n_is_create, s.groupname, t.v_dept, ").append("t.n_savetype, d.v_data_name as n_savetype_name,count(e.l_ins_id) as flowCount ").append("from bpm_form_info t ")
			.append("left join t_group s on t.v_dept=s.universalid ").append("left join bpm_data a on t.n_has_gen=a.n_value and a.v_data_field='n_has_gen' and a.v_data_table='form_info' ")
			.append("left join t_user b on t.v_creator=b.universalid ").append("left join bpm_data c on t.n_state=c.n_value and c.v_data_table='form_info' and c.v_data_field='n_state' ")
			.append("left join bpm_data d on t.n_savetype=d.n_value and d.v_data_field='n_savetype' and d.v_data_table='form_info' ")
			.append("left join bpm_flow_instance e on t.l_form_id=e.l_form_id and e.n_state=1 ");

	private StringBuffer standardSqlForFormElement = new StringBuffer("select ")
	.append("t.l_form_id, t.n_version, t.v_element_name, t.v_element_code, ")
	.append("t.v_table_field, t.n_field_type, t.v_field_validate, t.v_sum_field, t.v_search_field, ")
	.append("t.l_element_id, ")
	.append("t.n_type_detail, t.n_long, t.n_is_group, ")
	.append("t.l_group_id, t.n_type,c.v_data_name ")
	.append("from bpm_form_element t ")
	.append("left join bpm_data c on t.n_field_type=c.n_value and c.v_data_table='form_element' and c.v_data_field='n_field_type' ");

	@Override
	public int changeStateOfFormInfo(FormInfo form) {
		String sql = "update bpm_form_info set n_state=? where l_form_id=?";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(form.getState().getValue());
		conditionList.add(form.getFormId());
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List getListOfFormInfoForPage(int p, int pSize, Map<String, String> conditions) throws SQLException, Exception {

		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 and t.n_state not in(2,3) ";

		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formName") != null && !"".equals(conditions.get("formName"))) {
			conditionList.add("%" + conditions.get("formName") + "%");
			sqlBuffer.append(" and t.v_form_name like ?");

		}
		if (conditions.get("groupid") != null && !"".equals(conditions.get("groupid"))) {
			sqlBuffer.append(" and t.v_dept = ?");
			conditionList.add(conditions.get("groupid"));
		}
		sqlBuffer.append(" group by t.l_form_id order by t.n_state,t.d_createdate desc ");
		return pagerService.queryForList(p, pSize, sqlBuffer.toString(), conditionList, new FormInfoMapper());
	}

	@Override
	public int getCountOfFormInfoForPage(Map<String, String> conditions) {
		String sql = "select count(t.l_form_id) " + "from " + "bpm_form_info t " + "where 1=1 and t.n_state not in(2,3) ";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formName") != null && !"".equals(conditions.get("formName"))) {
			conditionList.add("%" + conditions.get("formName") + "%");
			sqlBuffer.append(" and t.v_form_name like ?");
		}
		if (conditions.get("groupid") != null && !"".equals(conditions.get("groupid"))) {
			sqlBuffer.append(" and t.v_dept = ?");
			conditionList.add(conditions.get("groupid"));
		}
		return jdbcTemplate.queryForObject(sqlBuffer.toString(), conditionList.toArray(),Integer.class);
	}

	@Override
	public int createFormInfoPath(FormInfopath formInfopath) {
		String sql = "insert into bpm_form_infopath " + "(l_form_id, v_infopath_file, v_infopath_content, " + "v_path, l_finfopath_id)" + " values(?, ?, ?, ?, ?)";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(formInfopath.getFormId().getFormId());
		conditionList.add(formInfopath.getInfopathFile());
		conditionList.add(formInfopath.getInfopathContent());
		conditionList.add(formInfopath.getPath());
		conditionList.add(formInfopath.getFinfopathId());
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List getListOfFormInfopathByFormId(String formId) {
		String sql = "select l_form_id, " + "v_infopath_file, " + "v_infopath_content, " + "l_finfopath_id, " + "v_path from bpm_form_infopath where l_form_id=?";
		return SpringUtil.springQueryForList(jdbcTemplate, sql, new Object[] { formId }, new FormInfopathMapper());
	}

	@Override
	public int createFormElement(FormElement element) {
		String sql = "insert into bpm_form_element " + "(l_form_id, n_version, v_element_name, v_element_code, " + "v_table_field, n_field_type, n_type, "// n_is_edit,
				+ "n_type_detail,v_field_validate, l_element_id" + ", n_is_group, l_group_id)" + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		return jdbcTemplate.update(sql, new Object[] { element.getFormId().getFormId(), element.getVesion(), element.getElementName(), element.getElementCode(), element.getTableField(),
				element.getFieldType().getValue(), element.getType().getValue(), element.getTypeDetail(), element.getFieldValidate(), element.getElementId(), element.getIsGroup(), element.getGroupId() });
	}

	@Override
	public int createFormElementForNewVersion(FormElement element) throws Exception {
		String sql = "insert into bpm_form_element " + "(l_form_id, n_version, v_element_name, v_element_code, " + "v_table_field, n_field_type, n_type, "// n_is_edit,
				+ "v_field_validate, l_element_id" + ", n_is_group, l_group_id, n_long, n_type_detail,v_sum_field, v_search_field)" + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(element.getFormId().getFormId());
		conditionList.add(element.getVesion());
		conditionList.add(element.getElementName());
		conditionList.add(element.getElementCode());
		conditionList.add(element.getTableField());
		conditionList.add(element.getFieldType().getValue());
		conditionList.add(element.getType().getValue());
		conditionList.add(element.getFieldValidate());
		conditionList.add(element.getElementId());
		conditionList.add(element.getIsGroup());
		conditionList.add(element.getGroupId());
		conditionList.add(element.getLength());
		conditionList.add(element.getTypeDetail());
		conditionList.add(element.getSumField());
		conditionList.add(element.getSearchFiled());
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	// @Override
	// public int createRepeatElement(Map<String, Object> element) {
	// String sql = "INSERT INTO bpm_form_element " + "(l_form_id, "
	// + "n_version, " + "v_element_name, " + "v_table_field, "
	// + "n_field_type, " + "n_type, " + "n_is_edit, "
	// + "v_field_validate, " + "n_is_group, " + "l_group_id, "
	// + "l_element_id" + ")" + "VALUES" + "(?, " + "?, " + "?, "
	// + "?, " + "?, ?, " + "?, " + "?, " + "?, ?, ?)";
	// return jdbcTemplate.update(
	// sql,
	// new Object[] { element.get("l_form_id"),
	// element.get("n_version"),
	// element.get("v_element_name"),
	// element.get("v_table_field"),
	// element.get("n_field_type"), element.get("n_type"),
	// element.get("n_is_edit"),
	// element.get("v_field_validate"),
	// element.get("n_is_group"), element.get("l_group_id"),
	// element.get("l_element_id") });
	// }

	@Override
	public int editFormElement(FormElement element) {
		String sql = "update bpm_form_element set " + "n_type = ?, " + "n_type_detail = ?,v_sum_field = ?,v_search_field = ?, " + "n_field_type = ?, n_long = ?, " + "v_element_name=? "
				+ "where l_element_id = ? and  n_version = ? ";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(element.getType().getValue());
		conditionList.add(element.getTypeDetail());
		conditionList.add(element.getSumField());
		conditionList.add(element.getSearchFiled());
		conditionList.add(element.getFieldType().getValue());
		conditionList.add(element.getLength());
		conditionList.add(element.getElementName());
		conditionList.add(element.getElementId());
		conditionList.add(element.getVesion());
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	@Override
	public int createFormInfo(FormInfo form) {
		String sql = "insert into bpm_form_info " + "(l_form_id, l_form_vid, v_form_name, " + "l_file_id, n_version, v_creator, " + "d_createdate, n_state, v_table, "
				+ "v_html, v_remark, v_dept, n_is_create, n_has_gen, n_savetype" + ")" + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(form.getFormId());
		conditionList.add(form.getFormVid());
		conditionList.add(form.getFormName());
		conditionList.add(form.getFileId().getUniversalid());
		conditionList.add(form.getVersion());
		conditionList.add(form.getCreator().getUserId());
		conditionList.add(new Date());
		conditionList.add(form.getState().getValue());
		// conditionList.add("oa_form_ins_" + form.getFormId()); 新版本沿用原来的表,不重新创建
		// 2015-04-22 cxx
		conditionList.add(form.getTable());
		conditionList.add(form.getHtml());
		conditionList.add(form.getRemark());
		conditionList.add(form.getDept().getGroupId());
		conditionList.add(form.getIsCreate().getValue()); // 表单是否创建沿用旧版本标识
		conditionList.add(form.getHasGen().getValue());
		conditionList.add(form.getSaveType().getValue());
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	@Override
	public int editFormInfo(FormInfo form) {
		String sql = "update bpm_form_info set " + "v_form_name = ?, n_state = ?, v_remark = ?, " + "n_is_create = ?, n_has_gen=?, v_dept = ?, n_version = ?, "
				+ "l_file_id = ?, l_form_vid = ?, n_savetype = ?  " + "where l_form_id = ?";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(form.getFormName());
		conditionList.add(form.getState().getValue());
		conditionList.add(form.getRemark());
		conditionList.add(form.getIsCreate().getValue());
		conditionList.add(form.getHasGen().getValue());
		conditionList.add(form.getDept().getGroupId());
		conditionList.add(form.getVersion());
		conditionList.add(form.getFileId().getUniversalid());
		conditionList.add(form.getFormVid());
		conditionList.add(form.getSaveType().getValue());
		conditionList.add(form.getFormId());
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	@Override
	public FormInfo getFormInfoById(String formId) {
		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 and t.l_form_id=? group by t.l_form_id ";
		Object result = SpringUtil.springQueryForObject(jdbcTemplate, sql, new Object[] { formId }, new FormInfoMapper());
		if (result == null) {
			return new FormInfo();
		} else {
			return (FormInfo) result;
		}
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List getListOfFormElementByConditions(Map<String, String> conditions) {
		String sql = this.standardSqlForFormElement.toString() + "where 1=1 ";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formId") != null) {
			conditionList.add(conditions.get("formId"));
			sqlBuffer.append(" and t.l_form_id=?");
		}
		if (conditions.get("isGroup") != null) {
			conditionList.add(conditions.get("isGroup"));
			sqlBuffer.append(" and t.n_is_group=?");
		}
		if (conditions.get("groupId") != null) {
			conditionList.add(conditions.get("groupId"));
			sqlBuffer.append(" and t.l_group_id=?");
		}
		if (conditions.get("!type") != null) {
			conditionList.add(conditions.get("!type"));
			sqlBuffer.append(" and t.n_type!=?");
		}
		if (conditions.get("version") != null) {
			conditionList.add(conditions.get("version"));
			sqlBuffer.append(" and t.n_version=?");
		}
		if (conditions.get("element_ids") != null) {
			String element_ids = conditions.get("element_ids");
			Object[] inSql = CommonUtil.toInSql(element_ids);
			if (inSql == null)
				return new ArrayList<UploadFile>();
			Object[] params = (Object[]) inSql[1];

			sqlBuffer.append(" and t.l_element_id in (");
			sqlBuffer.append(inSql[0].toString() + ")");
			//
			for (Object element_id : (Object[]) inSql[1]) {
				conditionList.add(element_id);
			}

		}
		return SpringUtil.springQueryForList(jdbcTemplate, sqlBuffer.toString(), conditionList.toArray(), new FormElementMapper());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getGroupIdOfFormElementByConditions(Map<String, String> conditions) {
		String sql = "select distinct t.l_group_id from bpm_form_element t " + "where 1=1";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formId") != null) {
			conditionList.add(conditions.get("formId"));
			sqlBuffer.append(" and t.l_form_id=?");
		}
		if (conditions.get("isGroup") != null) {
			conditionList.add(conditions.get("isGroup"));
			sqlBuffer.append(" and t.n_is_group=?");
		}
		return SpringUtil.springQueryForList(jdbcTemplate, sqlBuffer.toString(), conditionList.toArray(), new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				return rs.getString("l_group_id");
			}
		});
	}

	@Override
	public FormElement getFormElementByConditions(Map<String, String> conditions) throws Exception {
		String sql = this.standardSqlForFormElement.toString() + "where 1=1";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formId") != null) {
			conditionList.add(conditions.get("formId"));
			sqlBuffer.append(" and t.l_form_id=?");
		}
		if (conditions.get("elementCode") != null) {
			conditionList.add(conditions.get("elementCode"));
			sqlBuffer.append(" and t.v_element_code=?");
		}
		if (conditions.get("elementId") != null) {
			conditionList.add(conditions.get("elementId"));
			sqlBuffer.append(" and t.l_element_id=?");
		}
		if (conditions.get("version") != null) {
			conditionList.add(conditions.get("version"));
			sqlBuffer.append(" and t.n_version=?");
		}
		Object result = SpringUtil.springQueryForObject(jdbcTemplate, sqlBuffer.toString(), conditionList.toArray(), new FormElementMapper());
		if (result == null) {
			throw new Exception(ExceptionDefine.objectIsNull);
		} else {
			return (FormElement) result;
		}
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List getListOfFormInfoForUnused(String deptId, String modelId) {
		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 and t.n_state not in(1,2,3) " + "and t.l_form_id not in ("
				+ " select s.l_form_id from bpm_flow_model s where s.l_model_id!=? and s.n_state!=-1)";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(modelId);
		if (deptId != null && !"".equals(deptId)) {
			sqlBuffer.append(" and t.v_dept=?");
			conditionList.add(deptId);
		}
		sqlBuffer.append(" group by t.l_form_id ");
		return SpringUtil.springQueryForList(jdbcTemplate, sqlBuffer.toString(), conditionList.toArray(), new FormInfoMapper());
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public Map<String, Object> infoFormFlowElement(
	// Map<String, String> conditions) {
	// String sql = "SELECT  "
	// + "t.l_form_id, "
	// + "t.l_model_id, "
	// + "t.l_tmodel_id, "
	// + "t.l_element_id, "
	// + "t.n_can_edit "
	// + "FROM bpm_form_element a "
	// +
	// "LEFT JOIN bpm_flow_form_element t ON a.l_element_id=t.l_element_id  WHERE 1=1";
	// StringBuffer sqlBuffer = new StringBuffer(sql);
	// List<Object> conditionList = new ArrayList<Object>();
	// if (conditions.get("formId") != null) {
	// conditionList.add(conditions.get("formId"));
	// sqlBuffer.append(" AND t.l_form_id=?");
	// }
	// if (conditions.get("elementId") != null) {
	// conditionList.add(conditions.get("elementId"));
	// sqlBuffer.append(" AND t.l_element_id=?");
	// }
	// if (conditions.get("tmodelId") != null
	// && !"".equals(conditions.get("tmodelId"))) {
	// conditionList.add(conditions.get("tmodelId"));
	// sqlBuffer.append(" AND t.l_tmodel_id=?");
	// }
	// Map<String, Object> map = null;
	// try {
	// map = SpringUtil.springQueryForMap(jdbcTemplate,
	// sqlBuffer.toString(), conditionList.toArray());
	// } catch (Exception e) {
	// map = new HashMap<String, Object>();
	// }
	// return map;
	// }

	@Override
	public int getCountOfCanEditForElement(Map<String, String> conditions) {
		String sql = "select  " + "count(n_can_edit) " + "from bpm_flow_form_element where 1=1";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formId") != null) {
			conditionList.add(conditions.get("formId"));
			sqlBuffer.append(" and l_form_id=?");
		}
		if (conditions.get("elementId") != null) {
			conditionList.add(conditions.get("elementId"));
			sqlBuffer.append(" and l_element_id=?");
		}
		if (conditions.get("tmodelId") != null && !"".equals(conditions.get("tmodelId"))) {
			conditionList.add(conditions.get("tmodelId"));
			sqlBuffer.append(" and l_tmodel_id=?");
		}
		return jdbcTemplate.queryForObject(sqlBuffer.toString(), conditionList.toArray(),Integer.class);
	}

	@Override
	public int deleteFlowFormElement(String l_form_id, String l_tmodel_id) {
		String sql = "delete from bpm_flow_form_element where l_form_id=? and l_tmodel_id=?";
		return jdbcTemplate.update(sql, new Object[] { l_form_id, l_tmodel_id });
	}

	@Override
	public int createFlowFormElement(Map<String, String> ffElement) {
		String sql = "insert into bpm_flow_form_element (" + "l_form_id, " + "l_model_id, " + "l_tmodel_id, " + "l_element_id, " + "n_can_edit ) " + "values (? ,? ,? ,? ,?)";
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(ffElement.get("l_form_id"));
		conditionList.add(ffElement.get("l_model_id"));
		conditionList.add(ffElement.get("l_tmodel_id"));
		conditionList.add(ffElement.get("l_element_id"));
		conditionList.add(ffElement.get("n_can_edit"));
		return jdbcTemplate.update(sql, conditionList.toArray());
	}

	@Override
	public int updateSql(String sql, List<Object> values) {

		return jdbcTemplate.update(sql, values.toArray());
	}

	@Override
	public int updateSqlForGen(String sql, final List<Object> values) {
		return jdbcTemplate.update(sql, new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				for (int i = 0; i < values.size(); i++) {
					Object obj = values.get(i);
					if (obj instanceof FileItem) {
						FileItem fi = (FileItem) obj;
						try {
							ps.setBinaryStream(i + 1, fi.getInputStream(), (int) fi.getSize());
						} catch (IOException e) {
							e.printStackTrace();
						}
					} else {
						ps.setObject(i + 1, obj);
					}
				}

			}
		});

	}

	@Override
	public GenFile getGenFileById(String formInsId) {
		String sql = "select t.l_form_id,t.l_form_ins_id,t.v_gen_file,t.v_gen_path from bpm_genfile t " + "where 1=1 and t.l_form_ins_id=?";
		Object result = SpringUtil.springQueryForObject(jdbcTemplate, sql, new Object[] { formInsId }, new GenFileMapper());
		if (result == null) {
			return null;
		} else {
			return (GenFile) result;
		}
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List getListOfFormInfo(Map<String, String> conditions) {
		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 and t.n_state not in(2,3) ";

		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("groupid") != null && !"".equals(conditions.get("groupid"))) {
			sqlBuffer.append(" and t.v_dept = ?");
			conditionList.add(conditions.get("groupid"));
		}
		sqlBuffer.append(" group by t.l_form_id ");
		return SpringUtil.springQueryForList(jdbcTemplate, sqlBuffer.toString(), conditionList.toArray(), new FormInfoMapper());

	}

	@Override
	public int getMaxFormVersionByFormVid(String formVid) {
		String sql = "select IFNULL(max(n_version),0) from bpm_form_info where l_form_vid = " + formVid;
		return jdbcTemplate.queryForObject(sql,Integer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<FormInfo> getListOfFormInfoForOldVersion(Map<String, String> conditions) throws Exception {
		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 ";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("groupid") != null && !"".equals(conditions.get("groupid"))) {
			sqlBuffer.append(" and t.v_dept = ?");
			conditionList.add(conditions.get("groupid"));
		}
		if (conditions.get("formVid") != null && !"".equals(conditions.get("formVid"))) {
			sqlBuffer.append(" and t.l_form_vid = ?");
			conditionList.add(conditions.get("formVid"));
		}
		if (conditions.get("state") != null && !"".equals(conditions.get("state"))) {
			sqlBuffer.append(" and t.n_state = ?");
			conditionList.add(conditions.get("state"));
		}
		sqlBuffer.append(" group by t.l_form_id ");
		return SpringUtil.springQueryForList(jdbcTemplate, sqlBuffer.toString(), conditionList.toArray(), new FormInfoMapper());
	}

	@Override
	public FormInfo getInUseFormInfoByVid(String formVid) {
		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 and t.n_state not in(2,3) and t.l_form_vid=? group by t.l_form_id ";
		Object result = SpringUtil.springQueryForObject(jdbcTemplate, sql, new Object[] { formVid }, new FormInfoMapper());
		if (result == null) {
			return new FormInfo();
		} else {
			return (FormInfo) result;
		}
	}

	@Override
	public List getListOfFormInfoVersionForPage(int p, int pSize, Map<String, String> conditions) throws SQLException, Exception {
		String sql = this.standardSqlForFormInfo.toString() + "where 1=1 and t.n_state not in(3) ";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formVid") != null && !"".equals(conditions.get("formVid"))) {
			sqlBuffer.append(" and t.l_form_vid = ?");
			conditionList.add(conditions.get("formVid"));
		}
		sqlBuffer.append(" group by t.l_form_id ");
		sqlBuffer.append(" order by t.n_state asc");
		if ("version".equals(conditions.get("sortname"))) {
			if ("asc".equals(conditions.get("sortorder"))) {
				sqlBuffer.append(",t.n_version asc");
			} else if ("desc".equals(conditions.get("sortorder"))) {
				sqlBuffer.append(",t.n_version desc");
			}
		} else if ("flowCount".equals(conditions.get("sortname"))) {
			if ("asc".equals(conditions.get("sortorder"))) {
				sqlBuffer.append(",flowcount asc");
			} else if ("desc".equals(conditions.get("sortorder"))) {
				sqlBuffer.append(",flowcount desc");
			}
		} else {
			sqlBuffer.append(",t.n_version desc");
		}
		return pagerService.queryForList(p, pSize, sqlBuffer.toString(), conditionList, new FormInfoMapper());
	}

	@Override
	public int getCountOfFormInfoVersionForPage(Map<String, String> conditions) {
		String sql = "select count(t.l_form_id) " + "from " + "bpm_form_info t " + "where 1=1 and t.n_state not in(3) ";
		StringBuffer sqlBuffer = new StringBuffer(sql);
		List<Object> conditionList = new ArrayList<Object>();
		if (conditions.get("formVid") != null && !"".equals(conditions.get("formVid"))) {
			sqlBuffer.append(" and t.l_form_vid = ?");
			conditionList.add(conditions.get("formVid"));
		}
		return jdbcTemplate.queryForObject(sqlBuffer.toString(), conditionList.toArray(),Integer.class);
	}

	@Override
	public int getCountOfDealingFlowByFormId(String formId) {
		String sql = "SELECT COUNT(t.l_ins_id) FROM bpm_flow_instance t WHERE t.n_state=1  AND t.l_form_id=" + formId;
		return jdbcTemplate.queryForObject(sql,Integer.class);
	}

	protected class FormInfoMapper implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			FormInfo formInfo = new FormInfo();
			formInfo.setFormId(rs.getString("l_form_id"));
			formInfo.setFormVid(rs.getString("l_form_vid"));
			formInfo.setFormName(rs.getString("v_form_name"));
			formInfo.getFileId().setUniversalid(rs.getString("l_file_id"));
			formInfo.setVersion(rs.getString("n_version"));
			formInfo.getCreator().setUserId(rs.getString("v_creator"));
			formInfo.getCreator().setUsername(rs.getString("name"));
			formInfo.getState().setDataName(rs.getString("v_state_name"));
			formInfo.getState().setValue(rs.getString("n_state"));
			formInfo.setCreatedate(rs.getTimestamp("d_createdate"));
			formInfo.setTable(rs.getString("v_table"));
			formInfo.setHtml(rs.getString("v_html"));
			formInfo.setRemark(rs.getString("v_remark"));
			formInfo.getHasGen().setValue(rs.getString("n_has_gen"));
			formInfo.getHasGen().setDataName(rs.getString("n_has_gen_name"));
			formInfo.getIsCreate().setValue(rs.getString("n_is_create"));
			formInfo.getDept().setGroupId(rs.getString("v_dept"));
			formInfo.getDept().setGroupName(rs.getString("groupname"));
			formInfo.getSaveType().setValue(rs.getString("n_savetype"));
			formInfo.getSaveType().setDataName(rs.getString("n_savetype_name"));
			formInfo.setFlowCount(rs.getString("flowCount"));
			return formInfo;
		}
	}

	protected class FormElementMapper implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			FormElement element = new FormElement();
			element.getFormId().setFormId(rs.getString("l_form_id"));
			element.setVesion(rs.getString("n_version"));
			element.setElementName(rs.getString("v_element_name"));
			element.setElementCode(rs.getString("v_element_code"));
			element.setTableField(rs.getString("v_table_field"));
			element.getFieldType().setValue(rs.getString("n_field_type"));
			element.setFieldValidate(rs.getString("v_field_validate"));
			element.setElementId(rs.getString("l_element_id"));
			element.getType().setValue(rs.getString("n_type"));
			element.setTypeDetail(rs.getString("n_type_detail"));
			element.setSumField(rs.getString("v_sum_field"));
			element.setSearchFiled(rs.getString("v_search_field"));
			element.setLength(rs.getString("n_long"));
			element.setIsGroup(rs.getString("n_is_group"));
			element.setGroupId(rs.getString("l_group_id"));
			element.getFieldType().setDataName(rs.getString("v_data_name"));
			return element;
		}
	}

	protected class FormInfopathMapper implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			FormInfopath infopath = new FormInfopath();
			infopath.getFormId().setFormId(rs.getString("l_form_id"));
			infopath.setInfopathFile(rs.getString("v_infopath_file"));
			infopath.getInfopathContent().append(rs.getString("v_infopath_content"));
			infopath.setFinfopathId(rs.getString("l_finfopath_id"));
			infopath.setPath(rs.getString("v_path"));
			return infopath;
		}
	}

	protected class GenFileMapper implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			GenFile genFile = new GenFile();
			genFile.setFormId(rs.getLong("l_form_id"));
			genFile.setFormInsId(rs.getLong("l_form_ins_id"));
			genFile.setGenFile(rs.getString("v_gen_file"));
			genFile.setGenPath(rs.getString("v_gen_path"));
			return genFile;
		}
	}

	@Override
	public PagerService getFormRecodPageList(int p, int pSize, Map<String, Object> conditions) throws SQLException, Exception {

		String formId = conditions.get("formId").toString();

		Object searchElementList = conditions.get("searchElementList"); // 查询条件
		FormInfo formInfo = this.getFormInfoById(formId);
		StringBuffer sb = new StringBuffer("SELECT fi.v_ins_name insName,fi.d_createdate createdate,foi.*,a.v_data_name as dataname FROM ");
		sb.append(formInfo.getTable());
		sb.append(" foi,bpm_flow_instance fi,bpm_data a,bpm_form_info fo ");
		sb.append(" where foi.l_form_ins_id = fi.l_form_ins_id");
		sb.append(" and fi.n_state = a.n_value AND a.v_data_module = 'ins' AND a.v_data_field = 'n_state'");
		sb.append(" and fo.l_form_id = ? ");
		sb.append("	and fo.l_form_vid = ? ");
		sb.append(" and fi.v_creator =  ? ");
		sb.append(" and fi.n_state not in(0,4) ");// 过去撤销和删除的流程

		List<Object> params = new ArrayList();
		params.add(formId);
		params.add(formInfo.getFormVid());
		params.add(conditions.get("userId"));

		if (searchElementList != null) {
			for (FormElement searchElement : (List<FormElement>) searchElementList) {
				sb.append(" and foi." + searchElement.getTableField() + " = ? ");
				params.add(conditions.get(searchElement.getTableField()));
			}
		}

		if (conditions.get("insname") != null) { // 获取流程标题
			String insname = conditions.get("insname").toString();
			if (!StringUtil.isEmpty(insname)) {
				sb.append(" and fi.v_ins_name like ? ");
				params.add("%" + insname + "%");
			}

		}

		sb.append(" order by fi.d_createdate desc");

		PagerService ps = getPagerService();
		ps.queryPageForList(p, pSize, sb.toString(), params);
		return ps;

	}

	@Override
	public Map getSumFiled(Map<String, Object> conditions) {
		String formId = conditions.get("formId").toString();
		FormInfo formInfo = this.getFormInfoById(formId);
		StringBuffer sb = new StringBuffer("SELECT sum(foi." + conditions.get("sumField") + ") allTotal FROM ");

		// SELECT fi.v_ins_name,fo.* FROM `oa_form_ins_6486064572673`
		// fo,bpm_flow_instance fi where fo.l_form_ins_id = fi.l_form_ins_id and
		// fi.l_form_id =6486064572673 ORDER BY fi.d_createdate desc;
		Object searchElementList = conditions.get("searchElementList"); // 查询条件
		sb.append(formInfo.getTable());
		sb.append(" foi,bpm_flow_instance fi,bpm_form_info fo ");
		sb.append(" where foi.l_form_ins_id = fi.l_form_ins_id");
		sb.append(" and fo.l_form_id = ? ");
		sb.append("	and fo.l_form_vid = ? ");
		sb.append(" and fi.v_creator =  ? ");
		sb.append(" and fi.n_state not in(0,4) ");// 过去撤销和删除的流程

		List<Object> params = new ArrayList();
		params.add(formId);
		params.add(formInfo.getFormVid());
		params.add(conditions.get("userId"));

		if (searchElementList != null) {
			for (FormElement searchElement : (List<FormElement>) searchElementList) {
				sb.append(" and foi." + searchElement.getTableField() + " = ? ");
				params.add(conditions.get(searchElement.getTableField()));
			}
		}

		if (conditions.get("insname") != null) { // 获取流程标题
			String insname = conditions.get("insname").toString();
			if (!StringUtil.isEmpty(insname)) {
				sb.append(" and fi.v_ins_name like ? ");
				params.add("%" + insname + "%");
			}

		}

		sb.append(" order by fi.d_createdate desc");

		try {
			return jdbcTemplate.queryForMap(sb.toString(), params.toArray());
		} catch (DataAccessException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	protected class FormElementMapperAddBDDZ implements RowMapper {
		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			FormElement element = new FormElement();
			element.getFormId().setFormId(rs.getString("l_form_id"));
			element.setVesion(rs.getString("n_version"));
			element.setElementName(rs.getString("v_element_name"));
			element.setElementCode(rs.getString("v_element_code"));
			element.setTableField(rs.getString("v_table_field"));
			element.getFieldType().setValue(rs.getString("n_field_type"));
			element.setFieldValidate(rs.getString("v_field_validate"));
			element.setElementId(rs.getString("l_element_id"));
			element.getType().setValue(rs.getString("n_type"));
			element.setTypeDetail(rs.getString("n_type_detail"));
			element.setSumField(rs.getString("v_sum_field"));
			element.setSearchFiled(rs.getString("v_search_field"));
			element.setLength(rs.getString("n_long"));
			element.setIsGroup(rs.getString("n_is_group"));
			element.setGroupId(rs.getString("l_group_id"));
			element.getFieldType().setDataName(rs.getString("v_data_name"));
			element.setBddz(rs.getString("BDDZ"));
			element.setBddzText(rs.getString("BDDZText"));
			return element;
		}
	}

	@Override
	public List<FormElement> getListOfFormElementByConditionsAddBDDZ(Map<String, String> conditions) {
		String sql = " SELECT t.l_form_id,t.n_version,t.v_element_name,	t.v_element_code,t.v_table_field,bf.n_is_edit_detail,"
				+ " b.v_formula_name as BDDZ,b.v_formula_text AS BDDZText,t.n_field_type,t.v_field_validate,	t.v_sum_field,	t.v_search_field,t.l_element_id,"
				+ "	t.n_type_detail,t.n_long,t.n_is_group,t.l_group_id,	t.n_type,c.v_data_name"
				+ "	FROM bpm_form_element t "
				+ " LEFT JOIN bpm_data c ON t.n_field_type = c.n_value "
				+ "	AND c.v_data_table = 'form_element' AND c.v_data_field = 'n_field_type' "
				+ " LEFT JOIN bpm_flow_form_element bf ON bf.l_element_id = t.l_element_id "
				+ " LEFT JOIN bpm_form_formula b ON b.l_form_id = bf.l_form_id "
				+ "	AND b.l_form_id = t.l_form_id AND b.l_formula_id = bf.n_is_edit_detail "
				+ "	WHERE 1 = 1 "
				+ " AND t.l_form_id = ?"
				+ " AND t.n_is_group = ?"
				+ "	AND t.n_type != ?"
				+ " AND bf.l_tmodel_id = ?"
				+ " AND bf.n_state = 1 "
				+ "  ORDER BY t.sort is null,t.sort asc "; 
		List<Object> conditionList = new ArrayList<Object>();
		conditionList.add(conditions.get("formId"));
		conditionList.add(conditions.get("isGroup"));
		conditionList.add(conditions.get("!type"));
		conditionList.add(conditions.get("tmodelId"));
		return SpringUtil.springQueryForList(jdbcTemplate, sql, conditionList.toArray(), new FormElementMapperAddBDDZ());
	}

}