package com.yc.service.panel;

import com.yc.service.BaseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 添加了and d.headflag=0 去除从表在面板上显示
 * 
 * @author pengbei
 * 
 */
@Service("SystemSettings")
public class SystemSettingsImpl extends BaseService implements SystemSettingsDao {
    @Autowired
    private SqlDBHelperIfc sqlDBHelper;
    private PanelParmHelper pHelper = null;

    public void setPanelHelper(PanelParmHelper pHelper) {
	this.pHelper = pHelper;
    }

    /**
     * <br>
     * 1 --表示数值型<br>
     * 2---表示text,ntext,image之类<br>
     * 3---字符和4日期
     * */
    public Map<String, String> getVerification(String table) throws SQLException {
	String sql = "";
	Map<String, String> vmap = null;
	if (!table.equals("")) {
	    if(table.toLowerCase().startsWith("taobao.")) return vmap;//by danaus 12-06-16,处理淘宝接口，不需要取得数据类型
	    if (table.indexOf("|") != -1) {
		table = table.split("\\|")[0];
	    }
	    sql = "exec  sp_columns " + table;
	    Connection connection = null;
	    PreparedStatement pStatement = null;
	    ResultSet rs = null;
	    vmap = new HashMap<String, String>();
	    try {
		connection = this.jdbcTemplate.getDataSource().getConnection();
		pStatement = connection.prepareStatement(sql);
		rs = pStatement.executeQuery();
		while (rs.next()) {
		    int intStr = 0;
		    switch (rs.getInt("data_type")) {
			case Types.TINYINT:
			case Types.INTEGER:
			case Types.DECIMAL:
			case Types.DOUBLE:
			case Types.FLOAT:
			case Types.NUMERIC:
			case Types.BIGINT:
			case Types.REAL:
			case Types.SMALLINT:
			case 0:
			    intStr = 1;
			break;
			case Types.BINARY:
			case Types.BLOB:
			case Types.CLOB:
			case Types.NCLOB:
			    intStr = 2;
			break;
			case Types.DATE:
			case Types.TIME:
			case Types.TIMESTAMP:
			    intStr = 4;// 日期
			break;
			default:
			    intStr = 3;
			break;
		    }
		    vmap.put(rs.getString("column_name").toLowerCase(), String.valueOf(intStr));
		}
	    } catch (Exception e) {
		vmap = null;
		throw new SQLException(e.getMessage());
	    } finally {
		if (rs != null) rs.close();
		if (pStatement != null) pStatement.close();
		if (connection != null) connection.close();
	    }
	}
	return vmap;
    }

    /**
     * 得到此功能号上面板上隐藏的集合
     * 
     * @param formId
     * @return
     */
    public List<Map<String, Object>> selectListHid(int formId) {
	String sql = "set nocount on ; select " + repPanTo(getXin(), pHelper.pan11, true) + repPanTo(" from gform m ,gfield d where m.formid=d.formid  and ((d.Visible=0 or RowNo=0 or ColNo=0) or (MasterFieldShowLocation > 0)) and m.formid=", pHelper.pan11, false) + formId + " and d.headflag=" + pHelper.primary;// 获得隐藏字段及grid下面的面板
	return getListSystemSettings(sql);
    }

    /**
     * 得到StatisType字段在9822设置中Ft的值
     * 
     * @return
     */
    public int getFtByStatisType() {
	String sql = "set nocount on ;  select ft from gfield where formid=9822 and fieldid='StatisType' ";
	  Integer in=jdbcTemplate.queryForObject(sql,Integer.class);
  return in==null?0:in;
    }

    @Deprecated
    public int getShowMaxRow(int formid, boolean downGrid) {
	return getShowMaxRow(formid, 3, downGrid);
    }

    /**
     * 得到功能号formId面板共多小行
     * 
     * @param formid
     * @return
     */
    @Deprecated
    public int getShowMaxRow(int formid, int primary, boolean downGrid) {
	String formString = "  from gform m ,gfield d where m.formid=d.formid  and d.Visible=1 and m.formid=" + formid;
	if (primary == 0 || primary == 1) {
	    formString += " and HeadFlag=" + primary;
	}
	// 如果是显示在grid下
	formString += " and " + ((downGrid) ? "MasterFieldShowLocation > 0" : "isnull(MasterFieldShowLocation,0)=0 ");
	String sql = repPanTo("set nocount on ; select max(rowno+d.HeightNum-1) as rownum ", pHelper.pan11, false) + formString;
	Integer in= jdbcTemplate.queryForObject(sql,Integer.class);
  return in==null?0:in;
    }

    public int getShowMaxRow(int formid, int primary, int downFormId) {
	String formString = "  from gform m ,gfield d where m.formid=d.formid  and d.Visible=1 and m.formid=" + formid;
	if (primary == 0 || primary == 1) {
	    formString += " and HeadFlag=" + primary;
	}
	// 如果是显示在grid下
	formString += " and " + ("isnull(MasterFieldShowLocation,0)=" + downFormId);
	String sql = repPanTo("set nocount on ; select max(rowno+d.HeightNum-1) as rownum ", pHelper.pan11, false) + formString;
	Integer in= jdbcTemplate.queryForObject(sql,Integer.class);
	return in==null?0:in;
    }

    public int getMaxRow(int formid) {
	String formString = " from gformFilter where  Visible=1 and formid=" + formid;
	String sql = getRowSql(formString);
	Integer in= jdbcTemplate.queryForObject(sql,Integer.class);
    return in==null?0:in;
    }

    /**
     * 查出有多少行
     * 
     * @param formString 从此语句中查询
     * @return
     */
    private String getRowSql(String formString) {
	StringBuffer sBuffer = new StringBuffer();
	sBuffer.append("\r\n set nocount on ;");
	sBuffer.append("\r\n declare @sum int");
	sBuffer.append("\r\n select @sum=sum(isnull(HeightNum,0))").append(formString);
	sBuffer.append("\r\n  select @sum as rownum");
	return sBuffer.toString();
    }

    /**
     * 得到功能号formId面板共多小列
     * 
     * @param formid
     * @return
     */
    public int getShowMaxCol(int formid) {
	String sql = repPanTo("set nocount on ; select max(colno)", pHelper.pan11, false) + " as colno from gform m ,gfield d where m.formid=d.formid  and d.Visible=1 and m.formid=" + formid;// and d.headflag=0
	Integer in= jdbcTemplate.queryForObject(sql,Integer.class);
    return in==null?0:in;
    }

    @Deprecated
    public int getShowHei(int formid) {
	String sql = repPanTo("set nocount on ; select max(colno)", pHelper.pan11, false) + " as colno from gform m ,gfield d where m.formid=d.formid  and d.Visible=1 and m.formid=" + formid;// and d.headflag=0
	return jdbcTemplate.queryForObject(sql,Integer.class);
    }

    /**
     * 得到功能号formId面板的分页名称集合，一个特殊NULL
     * 
     * @param formId
     * @return
     */
    @Deprecated
    public List<String> selectTabsheetname(int formId, boolean downGrid) {
	String sql = "set nocount on ; select distinct isnull(tabsheetname,'') as tabsheetname from gform m ,gfield d where m.formid=d.formid and m.formid=" + formId + " and d.Visible=1 and d.headflag=" + pHelper.primary + " and "
		+ ((downGrid) ? "MasterFieldShowLocation > 0" : "isnull(MasterFieldShowLocation,0)=0 ") + " group by tabsheetname ";// and
	return jdbcTemplate.queryForList(sql, String.class);
    }

    public List<String> selectTabsheetname(int formId, int downFromid) {
	String sql = "set nocount on ; select distinct isnull(tabsheetname,'') as tabsheetname from gform m ,gfield d where m.formid=d.formid and m.formid=" + formId + " and d.Visible=1 and d.headflag=" + pHelper.primary + " and " + ("isnull(MasterFieldShowLocation,0)=" + downFromid + " ")
		+ " group by tabsheetname ";// and
	return jdbcTemplate.queryForList(sql, String.class);
    }

    /**
     * 得到功能号formId在面板tabsheetname的第rowno行有多小列
     * 
     * @param formId
     * @param downGrid
     * @return
     */
    @Deprecated
    public List<Map<String, Object>> selectList(int formId, boolean downGrid) {
	return selectList(formId, 3, downGrid);
    }

    @Override
	public Map<String, Object> getFormIdInTable(int formId) {
    	//这里加多一个isShowMemoWhenApprovals字段（审核时显示意见录入框），添加人xin 2021-6-21 15:00:38
	String sql = "set nocount on ; select hdtable,dttable,formname,isshowmemowhenapprovals from  gform where formid=?";
	return jdbcTemplate.queryForMap(sql, new Object[] { formId });
    }

    @Override
	public Map<String, Object> getFormIdtable(int formId, int primary, int downFromId) {
    	String sql="set nocount on ; declare @MaxRows int = 0 ,@MaxCols int = 0 select top 1 @MaxRows = isnull(rowno,0) + isnull(a.heightnum,0) -1 "
    			+ "from gfield a where formid = "+formId+" and isnull(headflag,0)= "+primary+" and isnull(visible,0) = 1 "
    			+ "and isnull(MasterFieldShowLocation,0) = "+downFromId+" and isnull(isLoad,0) = 1 and isnull(DataLink,0) = 1 "
    			+ "and isnull(isCustomizeHtmlField,0) = 0 order by (isnull(a.rowno,0) + isnull(a.heightnum,0)) desc "
    			+ "select top 1 @MaxCols = isnull(colno,0) + isnull(lengthnum,0) -1 from gfield a where formid = "+formId+" "
    			+ "and isnull(headflag,0)= "+primary+" and isnull(visible,0) = 1 and isnull(isLoad,0) = 1 and isnull(DataLink,0) = 1 "
    			+ "and isnull(isCustomizeHtmlField,0) = 0 and isnull(controltype,0) not in (34,36) "//41控件不需要加进去，加进去后41显示不了 xin 2020-5-14 10:46:08
    			+ "order by (isnull(a.colno,0) + isnull(a.lengthnum,0)) desc select @MaxRows as MaxRows ,@MaxCols as MaxCols";
          	return jdbcTemplate.queryForMap(sql);
        }
    
    @Deprecated
    public List<Map<String, Object>> selectList(int formId, int primary, boolean downGrid) {
	String sql = " from gform m ,gfield d where m.formid=d.formid  and m.formid=" + formId;// + " and d.Visible=1 ";
	if (primary == 0 || primary == 1) {
	    sql += " and d.headflag=" + primary;
	}
	sql += " and " + ((downGrid) ? "MasterFieldShowLocation > 0" : "isnull(MasterFieldShowLocation,0)=0 ") + "  order by rowno,ColNo";// 只显示grid之上
	sql = "set nocount on ; select " + repPanTo(getXin(), pHelper.pan11, true) + repPanTo(sql, pHelper.pan11, false);
	return getListSystemSettings(sql);
    }

    @Override
	public List<Map<String, Object>> selectList(int formId, int primary, int downFromId) {
		String sql = " from gfield d join gform m on m.formid=d.formid \n"
				+ " left join _sys_AttachmentParameter p  on d.formid = p.formid and d.headflag = p.headflag and d.fieldid = p.fieldid \n"
				+ " where  d.formid=" + formId;
		if (primary == 0 || primary == 1) {
			sql += " and d.headflag=" + primary;
		}
		sql += " and " + ("isnull(MasterFieldShowLocation,0)=" + downFromId) + "  order by rowno,ColNo";
		try {
			sql = "set nocount on ; select " + repPanTo(getXin() + (",isCopyExclude,audit,d.onlyOne,d.SuggestFileds,d.RelationField,d.exportTitle,isnull(p.FileSize,0) as maxFileSize ").toLowerCase(), pHelper == null ? false : pHelper.pan11, true)
					+ repPanTo(sql, pHelper == null ? false : pHelper.pan11, false);
			return getListSystemSettings(sql);
		} catch (Exception e) {
			// sql = "select " + repPanTo(getXin(), pHelper.pan11, true) + repPanTo(sql, pHelper.pan11, false);
			sql = sql.replaceAll((",isCopyExclude").toLowerCase(), "");
			return getListSystemSettings(sql);
		}
	}

	/**
	 * 自定义控件布局
	 * @param formType 窗体类型
	 * @param formId 功能号
	 * @param primary 0 主表数据 1从表数据
	 * @param downFromId 表格之前 0 或表格之后 1
	 * @param userCode 用户
	 * @return
	 */
	@Override
	public List<Map<String, Object>> getCustomLayout(int formType,int formId, int primary, int downFromId, String userCode) {
		try {
			String sql="select usercode,formid,headflag,fieldid,statisid,fieldname,datatype,datatypelength,showongrid,gridcaption," +
					"visible,hidelabel,rowno,colno,lengthnum,heightnum,masterfieldshowlocation from gFieldCustomLayout " +
					"where UserCode='"+userCode+"' and FormId="+formId +" and formType="+formType;
			if (primary == 0 || primary == 1) {
				sql += " and HeadFlag=" + primary;
			}
			sql += " and " + ("isnull(MasterFieldShowLocation,0)=" + downFromId) + "  order by rowno,ColNo";
			return getListSystemSettings(sql);
		}catch (Exception e){
			throw e;
		}
	}
	@Override
	public List<Map<String, Object>> selectAll(int formId, int primary) {
	String sql = " from gform m ,gfield d where m.formid=d.formid  and m.formid=" + formId;
	if (primary == 0 || primary == 1) {
	    sql += " and d.headflag=" + primary;
	}
	sql = "set nocount on ; select " + repPanTo(getXin()+(",isCopyExclude").toLowerCase(), false, true) + repPanTo(sql, false, false);
	return getListSystemSettings(sql);
    }

    @Override
	public List<Map<String, Object>> getQuery(int formId, int primary) {
	String sql = "select " + repPanTo(getXin(), pHelper.pan11, true) + " from gform m ,gfield d where m.formid=d.formid  and m.formid=" + formId ;
	if (primary == 0 || primary == 1) {
	    primary = (primary == 0) ? 1 : 0;// 要得到表格的筛选条件当然要与面板相反
	    sql += " and d.headflag=" + primary +" and d.showongrid=1 and d.StatisFlag=1 order by StatisID ";
	}
	return getListSystemSettings(sql);
    }
    public List<Map<String, Object>> getQuery_APP(int formId, int primary) {//APP调用
	String sql = "set nocount on ; select fieldid,fieldname from gform m ,gfield d where m.formid=d.formid  and m.formid=" + formId ;
	if (primary == 0 || primary == 1) {
	    primary = (primary == 0) ? 1 : 0;// 要得到表格的筛选条件当然要与面板相反
	    sql += " and d.headflag=" + primary +" and d.showongrid=1 and d.StatisFlag=1 order by StatisID ";
	}
	return getListSystemSettings(sql);
    }
    @Override
    public List<Map<String, Object>> selecTH(String thid, String sys, String role) {
	String sql="set nocount on ; select ReaderUsercodes,ReaderOrganizations,ReaderRoles from "+thid+" where ReaderUsercodes='"+sys+"' and ReaderRoles='"+role+"'";	
	return getListSystemSettings(sql);
    }
    /**
     * 获得sql语句的 list集合
     * 
     * @param sql1
     * @return List<Map<String, Object>>
     */
    private List<Map<String, Object>> getListSystemSettings(String sql1) {
	List<Map<String, Object>> sysList = jdbcTemplate.queryForList(sql1);
	return sysList;
    }

    /**
     * 获得此功能号面板上的感应字段字符串连接(;)
     * 
     * @param formId
     * @return
     */
    @Override
	public String getPanDataLinks(int formId) {
	String links = "";
	String sql = "set nocount on ; select FieldID from gform m ,gfield d where m.formid=d.formid  and d.Visible=1 and rowno>0 and colno>0 and datalink =1 and m.formid=" + formId;// and d.headflag=0
	List<String> liksList = jdbcTemplate.queryForList(repPanTo(sql, pHelper.pan11, false), String.class);
	for (String li : liksList) {
	    links += li + ";";
	}
	if (links.indexOf(";") != -1) {
	    links = links.substring(0, links.lastIndexOf(";"));
	}
	return links;
    }

    /**
     * 等到功能号Id窗体的类型
     * 
     * @param formId
     * @return
     */
    @Override
	public int getFormType(int formId) {
	String sql = "set nocount on ; select formtype from _sysMenu where formid = " + formId;
	Integer in= jdbcTemplate.queryForObject(sql,Integer.class);
    return in==null?0:in;
    }

    /**
     * 需要查询的所有字段
     * 
     * @return
     */
    private String getXin() {
	return "d.tabsheetname,m.FormID as FormID,d.HeadFlag,d.FieldID,d.StatisID,d.fieldname,d.datatype,d.FTFormType as FTFormType,\n"
		+ "d.Displayformat,d.ShowOnGrid,d.RowNo,d.ColNo,d.Visible,d.ControlType,\n" 
		+ "d.HeightNum,d.LengthNum,d.FT as FT,d.FK as FK,d.SeekGroupID as SeekGroupID,d.ReadOnly,d.KeyInput,m.DTTable,\n"
		+ "m.HDTable,d.Hidelabel,d.InitValue,d.DataLink,d.Emptyrefdata,d.Uppercase,d.EditStatus,d.IsReader,\n" 
		+ "d.sPremissField as sPremissField,d.dPremissField as dPremissField,d.eFilter,d.MasterFieldShowLocation,\n"
		+ "d.SqlScript,d.SqlWhere,d.Hints,d.passwordchar,d.isCustomHTMLComponent,\n"
		+ "d.showFieldValueExpression,d.dyfieldview,d.calcuField,d.formula,\n"
		+ "d.HyperlinkFT,d.HyperlinkFTFormType,d.Hyperlinkmode,d.isAutoRefresh,d.HyperlinkSPremissField,d.HyperlinkDPremissField,\n"
		+ "d.HyperlinkEFilter,d.styleCss,d.TipsExpression,d.isExport,d.isImport,d.isLoad,m.glcodefield,d.issuppressblanklinefordropdown \n".toLowerCase();
    }

    /**
     * 功能号Id在设置功能号Id中查得信息，集合的key为列名小写
     * 
     * @param formid 功能号id
     * @param inFormid
     * @return
     */
    @Override
	public List<HashMap<String, String>> getListIn(int formid, int inFormid) {
	String sql = "";
	List<HashMap<String, String>> lists = new ArrayList<HashMap<String, String>>();
	if (inFormid == 9802) {// 查询inFormid在9802的设置信息
	    sql = "set nocount on ; select * from gform m ,gfield d where m.formid=d.formid and m.formid=" + formid;
	    lists = sqlDBHelper.getHashMap(sql);
	} else {
	    lists = getListIn(inFormid, 9802);// 查询inFormid的设置页面显示
	    sql = "select ";
	    String inTableString = "";
	    int i = 0;
	    for (Map<String, String> map : lists) {// 从lists中就知道这inFormid中的显示及该查询写什么信息了
		i++;
		if (map.get("headflag").equals("0")) {// 主表字段(map.get("showongrid").equals("1")||
		    sql += " a." + map.get("fieldid") + ",";
		}
		if (map.get("headflag").equals("1") && !map.get("dttable").equals("")) {// 从表字段 有从表字段无从表表名
		    // 不查
		    sql += " b." + map.get("fieldid") + ",";
		}
		if (i == 1) {// i=1只是让其查询第一信息，因为每次查询获得这些信息都是一样的
		    if (!map.get("hdtable").equals("") && !map.get("dttable").equals("")) {// 这个语句还是有错的
			// 不可能这样写的就不知怎样了
			inTableString = " from " + map.get("hdtable") + " a left joi n " + map.get("dttable")// 从表不一定有formid，没法确定知道两个表通过什么关联
				+ " b on(a.formid=b.formid) where a.formid=" + formid;
		    } else if (!map.get("hdtable").equals("")) {
			inTableString = " from " + map.get("hdtable") + " a  where a.formid=" + formid;
		    } else if (!map.get("dttable").equals("")) {
			inTableString = " from " + map.get("dttable") + " b  where b.formid=" + formid;
		    }
		}
	    }
	    sql = sql.substring(0, sql.lastIndexOf(",")) + inTableString;
	    lists = sqlDBHelper.getHashMap(sql);// sql拼写完成后再次查询返回在inFormid中查询信息了
	}
	return lists;
    }

    @Override
	public List<Map<String, Object>> getListIn(int formid, String inTable) {
	String sql = "set nocount on ; select * from " + inTable + " where formid=" + formid;
	return this.jdbcTemplate.queryForList(sql);
    }

 /*   public List<Map<String, Object>> getStatisDetail(int formid) {// 38类型获得筛选重复之后，显示的复选框信息
	String sql = " select FormID,StatisID,fieldalias,addtodecision,perccols,FieldID,StatisType,FieldCaption,Sequence,DisplayWidth,displayformat,displayYN "+
              " from _sysStatisDetail where formid=?";// proc_sysStatisDetail 添加到存储过程
	return jdbcTemplate.queryForList(sql, new Object[] { formid });
    }*/
    /*
     * 修改后的查询数据方法
     * @see com.yc.service.panel.SystemSettingsDao#getStatisDetail(int)
     */
    public List<Map<String, Object>> getStatisDetail(int formid) {// 38类型获得筛选重复之后，显示的复选框信息
    	String sql = "set nocount on ; SELECT StatisID,FieldID,FieldCaption,FieldAlias,StatisType,Sequence,DisplayWidth,displayformat,displayYN,isFilterZero,jionFlag,jionFlagGroup,conFlag,modfvalues,modfvalues2 FROM _sysStatisDetail WHERE FormID=? and displayyn=1 order by Sequence asc";// proc_sysStatisDetail 添加到存储过程
    	return jdbcTemplate.queryForList(sql, new Object[] { formid });
        }

    /**
     * 处理F11 使其默认成面板集合信息处理
     * 
     * @param sql sql语句中的字符串
     * @param tihuan 是否进行替换（即是否为F11面板）
     * @param as 是否（如查询字段加as替换，而条件字段直接替换）
     * @return 返回查询11的面板集合（在是查F11的情况下）
     */
    private String repPanTo(String sql, boolean tihuan, boolean as) {
	sql = sql.toLowerCase();
	if (tihuan) {
	    if (as) {
		sql = sql.replace("rowno", "detailrowno as rowno");
		sql = sql.replace("colno", "detailcolno as colno");
		sql = sql.replace("heightnum", "detailheight as heightnum");
		sql = sql.replace("lengthnum", "detaillength as lengthnum");
	    } else {
		sql = sql.replace("rowno", "detailrowno");
		sql = sql.replace("colno", "detailcolno");
		sql = sql.replace("heightnum", "detailheight");
		sql = sql.replace("lengthnum", "detaillength");
	    }
	}
	return sql;
    }

    @Override
	public List<Map<String, Object>> getCustomHTML(int fromid, String fieldid) {
	String sql = "set nocount on ; select seq,fieldid,htmlcontent from _sys_CustomHTML2 where formid = " + fromid + " and fieldid='" + fieldid + "' order by seq ";
	return this.jdbcTemplate.queryForList(sql);
    }

    public List<Map<String, Object>> getJs(int fromid) {
	String sql = "set nocount on ; select eventtype,jscontent from _sys_CustomJSFunction where formid = " + fromid;
	return this.jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getDetailTable(int formId, int formType, int no) {
	String sql = "";
	boolean reto = true;
	switch (formType) {
	    case 499:
	    case 498:
		sql = "set nocount on ; select hdtable from gform where formid in( select formid from _sys_TabPageFormid where mainformid=" + formId + " and formgroupid=" + no + " and formid<>" + formId + ") group by hdtable,dttable";
	    break;
	    case 15:
		sql = "set nocount on ; select hdtable from gform where formid in(select detailformid from _sysmasterdetail where formid=" + formId + " and detailformid<>" + formId + ")";
	    break;
	    default:
		reto = false;
	    break;
	}
	if (reto) { return this.jdbcTemplate.queryForList(sql); }
	return null;
    }

    @Override
	public List<Map<String, Object>> getFieldFormId(int formId) {
	return getListSystemSettings("set nocount on ; select " + ("MasterFieldShowLocation").toLowerCase() + " from gfield where formid=" + formId + " and MasterFieldShowLocation >1 group by MasterFieldShowLocation");
    }
    
	
	
	@Override
	public List<Map<String, Object>> getIntValue(String field, String formId) {
		String sql = " from gform m ,gfield d where m.formid=d.formid  and m.formid="+ formId;		
		    sql = "set nocount on ; select "+repPanTo(getXin(),true,true)+sql;
		    return getListSystemSettings(sql);		
	}
   
}