package com.yc.action.tree;

import com.yc.action.BaseAction;
import com.yc.action.grid.GTGrid;
import com.yc.factory.FactoryBean;
import com.yc.sdk.password.action.ChangePassword;
import com.yc.service.BaseService;
import com.yc.service.tree.TreeJson;
import com.yc.utils.JsonUtil;
import com.yc.utils.SessionKey;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.StringJoiner;

/**
 * 树操作数据SQL 拼装类
 * @author heqing.hbs@gmail.com
 * @updatedBy
 * @updateTime 2011-5-20 上午09:54:55
 */
public class TreeUtils extends BaseAction{
	/**
	 * 将json数据转换成删除的sql
	 * @param list
	 * @param tableName
	 * @param sql
	 */
	public void convert2DeleteSQL(List<HashMap<String,String>> list,String tableName,List<String> sql,String where){
		StringBuilder key=null;
		for(HashMap<String,String> map:list){
			key=new StringBuilder();
			if("1".equals(map.get("isDeletes"))){
				key.append("delete ").append(tableName).append(" where treeControl like (select treeControl from ")
				.append(tableName).append(" where rowid='").append(map.get("rowid"))
				.append("'").append(" and ").append(where).append(")+'").append("%'").append(" and ").append(where);
			}else{
				if(StringUtils.isBlank(map.get("primaryKey"))||StringUtils.isBlank(map.get("primaryValue")))continue;
				key.append("delete ").append(tableName).append(" where ").append(map.get("primaryKey")).append("='")
				.append(map.get("primaryValue").replace("'", "''")).append("'");
			}
			sql.add(key.toString());
		}
	}
	/**
	 * 将json数据转换成拖拽的sql
	 * @param update
	 * @param tableName
	 * @param sql
	 */
	@SuppressWarnings("unchecked")
	public void convert2DragSQL(List<HashMap<String,String>> update,String tableName,List<String> sql,String where){
		StringBuilder temp=null;
		for(HashMap map:update){
			temp=new StringBuilder();
			temp.append("update ").append(tableName).append(" set parentrowid='")
			.append(map.get("parentrowid")).append("',treeControl=");
			if(!"".equals(map.get("parentrowid"))){
			temp.append("(select top 1 isnull(treeControl,'') as treeControl from ")
			.append(tableName).append(" where rowid='").append(map.get("parentrowid")).append("' ")
			.append("  and ").append(where).append(")+");
			}
			temp.append("'").append(map.get("rowid")).append("' where rowid='").append(map.get("rowid")).append("'")
			.append(" and ").append(where);
			sql.add(temp.toString());
		}
	}
	
	/**
	 * sql语句的值是不是需要加上双引号，通过当前的列名查找表结构里的列数据类型
	 * 
	 * */
	@SuppressWarnings("unchecked")
	public void parserType(HashMap map, StringBuilder key, Object k,SqlRowSetMetaData md)throws NumberFormatException {
	    Object obj = null;
		int i = this.getType(md, (String) k);
		if (i == 3 || i == 2 || i == 4) {// 类型为varchar date
		    if (map.get(k) == null || map.get(k).toString().length() == 0) {
			if (i == 4 || i == 2) key.append(obj);
			else key.append("''");
		    } else {
			if ((((String) k).equalsIgnoreCase("enterdate") && ((String) map.get("enterdate")).equalsIgnoreCase("getdate()")) || (((String) k).equalsIgnoreCase("modifydate") && ((String) map.get("modifydate")).equalsIgnoreCase("getdate()"))) {// 为了把getdate()作为
			    // 一个function而增加
			    key.append(map.get(k));
			} else {
			    if("null".equalsIgnoreCase((String)map.get(k))){
				 key.append(obj);
			    }else{

			    	if(((String) k).equalsIgnoreCase("ImageBinary")){
						key.append("'");
			    	InputStream photoStream=null;
		    	        try {
		    	        	 String url=this.getClass().getResource("/").getPath();		
					url=url.substring(0, url.indexOf("WEB-INF"));	
					 photoStream = new FileInputStream(url+map.get("ImagePath"));	
					String imagebyte=input2bytet(photoStream);
		    	            key.append(imagebyte);
		    		    	key.append("'");
		    	        } catch (Exception e) {
		    	          e.printStackTrace();
		    	        }finally{
		    	            
		    	        try {
				    photoStream.close();
				} catch (IOException e) {
				    // TODO Auto-generated catch block
				    e.printStackTrace();
				}
		    	        }
		    	}else {
						key.append("N'");//by danaus 增加国标化处理
			    if (i == 3){
				String s=map.get(k)+"";
				if(s.lastIndexOf(JsonUtil.IMGRANG)>0){//处理图片格式问题，去掉|100|100
				    String ls=s.split("\\|")[1];
				   s=ls.replaceAll(".*uuid=", "");
				    s=s.replaceAll("&r=.*+", "");
				}
				key.append(s.replaceAll("'", "''").replaceAll("&gt;", ">").replaceAll("&lt;", "<").replaceAll("&amp;", "&"));
			    
			    }else //text,ntext类型
			    key.append(((String)map.get(k)).replaceAll("'", "''").replaceAll("&gt;", ">").replaceAll("&lt;", "<").replaceAll("&amp;", "&"));
				    key.append("'");
		    	}
		    	}
			}
		    }
		}else if(i==5){
		    
		    key.append("1".equalsIgnoreCase(((String)map.get(k)))?1:0);
		}
		else {
		    String kk = (String) k;
		    key.append((map.get(k) == null || map.get(k).toString().trim().length() == 0) ? obj : map.get(k));
		}
	}
	/**
	 * 把客户端数据 转换成    更新数据的 sql
	 * @param list 数据集
	 * @param tableName 表名
	 * @param sql sql集合 用于装载sql
	 * @param autoColumns 自动增长列和计算列    在拼sql时对其进行排除
	 * @param md //列的信息
	 */
	public void convert2UpdateSQL(List<HashMap<String,String>> update,String name,List<String> sql,List<String> autoColumns,SqlRowSetMetaData md,String where,HttpSession session){
		StringBuilder key = null; 
		StringBuilder value = null;
		boolean isAutoCoulmn = false;//用于判断是否为自动增长或计算列
		for(int i=0;i<update.size();i++){
			key = new StringBuilder();
			value = new StringBuilder();
			key.append(" set ");
//			value.append(" where 1=1 ");//不要添加1=1条件，影响数据查询效率 xin 2021-8-12 11:49:37
			value.append(" where ");
			if(where != null && !"".equals(where)) {
				value.append(where + " and ");//添加多where xin 2021-8-12 11:54:19
			}
			HashMap<String,String> up=update.get(i);
			//if(StringUtils.isBlank(up.get("primaryKey"))||StringUtils.isBlank(up.get("primaryValue"))) continue;
			value.append(up.get("primaryKey")).append("='").append(up.get("primaryValue").replace("'", "''"))
			.append("'");
			for (String k : up.keySet()) {
				 isAutoCoulmn = false;
				 for(int j=0;j<autoColumns.size();j++){
					if(k.equalsIgnoreCase(autoColumns.get(j))){
						isAutoCoulmn = true;
						break;
					}
				 }
				 if(isAutoCoulmn){
						continue;
				 }
//				 if(k.equals("rowid"))
//				 {
//					//value.append(" and rowid='"+up.get(k)+"'");
//					continue;
//				 }
				if (k.equals("primaryKey")
						|| k.equals("primaryValue")
						|| k.equals("rowid")
						|| k.equals("parentrowid")
						|| k.equals("docversion")
						|| k.equals("treecontrol"))
					continue;
				if("modifyname".equalsIgnoreCase((String) k) &&up.get("modifyname")!=null){
					key.append("[" + k + "]");
					key.append("='"+session.getAttribute(SessionKey.HRNAME)+"'");
				}else if("modifycode".equalsIgnoreCase((String) k) &&up.get("modifycode")!=null){
					key.append("[" + k + "]");
					key.append("='"+session.getAttribute(SessionKey.HRCODE)+"'");
				}else if("modifydate".equalsIgnoreCase((String) k) &&up.get("modifydate")!=null){
					key.append("[" + k + "]");
					key.append("=getdate()");
				}else {
					//其他情况
					key.append("[" + k + "]");
					key.append("=");
					parserType(up, key, k, md);
				}
				key.append(",");

			}
			String updateSql = "update " + name + key.substring(0, key.length() - 1) + value.toString();
			if (up.containsKey("docversion")) {
				updateSql += " and isnull(docversion,0)=isnull(" + (("null".equals(up.get("docversion")) || org.apache.commons.lang3.StringUtils.isBlank(up.get("docversion"))) ? "0" : up.get("docversion")) + ",0) \n" +
						"  if @@rowcount=0  \n " +
						" begin \n" +
						"  raiserror('当前单据内容已发生变化，请刷新页面然后重试此操作',16,1) \n" +
						" return \n" +
						" end \n";
			}
			// xin 2021-8-12 11:53:52
			updateSql += "\n if @@rowcount > 1 \n" +
					"  begin \n" +
					"     raiserror('由于表/视图缺少正确的主键（Primary Key），将导致多条记录被更新（update）为相同的值，解决办法：为表/视图【" + name + "】设置正确的主键（Primary Key)',16,1)\n" +
					"\t return \n" +
					"  end ";
			sql.add(updateSql);
		}
	}
	
	 /**
	     * 组装审计数据成inert into后提交到数据库
	     * 
	     * */
	    public void convert2AuditSQL(List<HashMap<String, String>> list,SqlRowSetMetaData m,HttpServletRequest request,List<String> sql) {
		if (list != null) {
		    for (HashMap map : list) {
			StringBuilder key = new StringBuilder();
			StringBuilder value = new StringBuilder();
			key.append("(");
			value.append("values(");
			for (Object k : map.keySet()) {
			    boolean flag = true;// false;
				if (("auditindex_expr".equalsIgnoreCase((String) k)||"auditindex".equalsIgnoreCase((String) k))&&map.get("auditIndex_expr")!=null&& org.apache.commons.lang3.StringUtils.isNotBlank(map.get("auditIndex_expr")+"")) {
					//解密密文,且把auditIndex_expr和auditIndex拼成一个
					StringJoiner auditIndex_expr=new StringJoiner(",");
					String[] expr=(map.get("auditIndex_expr")+"").split(",");
					for(String str:expr){
						try {
							if(org.apache.commons.lang3.StringUtils.isNotBlank(str)) {
								auditIndex_expr.add(ChangePassword.getDecryptPassword(str));
							}
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}
					map.put("auditIndex_expr","");
					map.put("auditIndex", ","+auditIndex_expr+","+map.get("auditIndex")+",");
				}
				if ("auditindex_expr".equalsIgnoreCase((String) k) ||"_ConstWidth".equalsIgnoreCase((String) k) || "_YCAUDIT_".equalsIgnoreCase((String) k)) {
					continue;
				}
			    if (flag) {
				if ("usercode".equalsIgnoreCase((String) k)) {
				    map.put(k, request.getSession().getAttribute(SessionKey.USERCODE));
				}
				if ("username".equalsIgnoreCase((String) k)) {
				    map.put(k, request.getSession().getAttribute(SessionKey.USERNAME));
				}
				key.append("[").append(k);
				parserType(map, value, k, m);
				key.append("],");
				value.append(",");
			    }
			}
				key.append("[auditDateTime],[auditDate],");
				value.append("getDate(),convert(varchar(10),getdate(),120),");
			sql.add("insert into _sysAudit"  + key.substring(0, key.length() - 1) + ")" + value.substring(0, value.length() - 1) + ")");
		    }
		}
		list = null;
	    }	
	
	/**
	 * 把客户端数据 转换成    插入数据的 sql
	 * @param list 数据集
	 * @param tableName 表名
	 * @param sql sql集合 用于装载sql
	 * @param autoColumns 自动增长列和计算列    在拼sql时对其进行排除
	 * @param md //列的信息
	 */
	public void convert2InsertSQL(List<HashMap<String,String>> list, String tableName, List<String> sql,
								  List<String> autoColumns, SqlRowSetMetaData md, HttpSession session){
		StringBuilder key = null; 
		StringBuilder value = null;
		boolean isAutoCoulmn = false;//用于判断是否为自动增长或计算列
		for(HashMap<String,String> map:list){
			key = new StringBuilder();
			value = new StringBuilder();
			key.append("(");
			value.append(" values( ");//原值 select 
			for (String k : map.keySet()) {
				isAutoCoulmn = false;
				for(int i=autoColumns.size()-1; i>=0; i--){
					if(k.equalsIgnoreCase(autoColumns.get(i))){
						isAutoCoulmn = true;
						break;
					}
				}
				if(isAutoCoulmn || k.equals("treecontrol")){
					continue;
				}


				if("enterdate".equalsIgnoreCase((String) k) &&map.get("enterdate")!=null){
					key.append("["+k+"]");
					value.append("getdate()");
					key.append(",");
					value.append(",");
				}
				else if("entername".equalsIgnoreCase((String) k) &&map.get("entername")!=null){
					key.append("["+k+"]");
					value.append("'"+session.getAttribute(SessionKey.HRNAME)+"'");
					key.append(",");
					value.append(",");
				}
				else if("entercode".equalsIgnoreCase((String) k) &&map.get("entercode")!=null){
					key.append("["+k+"]");
					value.append("'"+session.getAttribute(SessionKey.HRCODE)+"'");
					key.append(",");
					value.append(",");
				}
				else if("modifyname".equalsIgnoreCase((String) k) &&map.get("modifyname")!=null){
					key.append("["+k+"]");
					value.append("'"+session.getAttribute(SessionKey.HRNAME)+"'");
					key.append(",");
					value.append(",");
				}
				else if("modifycode".equalsIgnoreCase((String) k) &&map.get("modifycode")!=null){
					key.append("["+k+"]");
					value.append("'"+session.getAttribute(SessionKey.HRCODE)+"'");
					key.append(",");
					value.append(",");
				}
				else if("modifydate".equalsIgnoreCase((String) k) &&map.get("modifydate")!=null){
					key.append("["+k+"]");
					value.append("getdate()");
					key.append(",");
					value.append(",");
				}else {
					//其他情况
					key.append("["+k+"]");
					parserType(map, value, k,md);
					key.append(",");
					value.append(",");
				}
		    }	
			key.append("[treeControl])");
			if("".equals(map.get("parentrowid")))
			{
				value.append("'").append(map.get("rowid")).append("'");
			}else
			{
				BaseService bs=(BaseService)FactoryBean.getBean("BaseService");
				//没有查出父节点在表，但是前端又有这个父节点值传过来（前端同时添加父子节点情况） 那么就直接赋值 xin 2023-5-18 14:29:36
				String parentSql="set nocount on ; declare @treeControl varchar(50) ;\n" +
						"select top 1 @treeControl=treeControl from "+tableName+" where rowid='"+map.get("parentrowid")+"'\n" +
						"if @@rowcount = 0 \n" +
						"begin \n" +
						"  set @treeControl='"+map.get("parentrowid")+"'\n" +
						"end \n" +
						" select @treeControl as treeControl";
				String tcontrol=bs.getSimpleJdbcTemplate().queryForObject(parentSql, String.class);
				value.append("'").append(tcontrol).append(map.get("rowid")).append("'");
			}
			value.append(")");
			sql.add("insert into "+tableName+key+value);
		}
	}

	/**
	 * 拼接保存时执行过程，每行取对应的参数值，最后组成一串传过去 p120201Save 'DD001;DD002'
	 * @param treeJson
	 * @param dProcValuesParm
	 * @return
	 */
	public  String getDProcValues(TreeJson treeJson,String dProcValuesParm) {
	StringJoiner dProcValues = new StringJoiner(";");
	GTGrid grid = (GTGrid) FactoryBean.getBean("GTGrid");
	if (treeJson.getInsertedRecords() != null && treeJson.getInsertedRecords().size() > 0) {
		for (HashMap<String, String> map : treeJson.getInsertedRecords()) {
					dProcValues.add(getResultValues(grid,map,dProcValuesParm));
		}
	}
		if (treeJson.getUpdatedRecords() != null && treeJson.getUpdatedRecords().size() > 0) {
			for (HashMap<String, String> map : treeJson.getUpdatedRecords()) {
				dProcValues.add(getResultValues(grid,map,dProcValuesParm));
			}
		}
		if (treeJson.getDeleteRecords() != null && treeJson.getDeleteRecords().size() > 0) {
			for (HashMap<String, String> map : treeJson.getDeleteRecords()) {
				dProcValues.add(getResultValues(grid,map,dProcValuesParm));
			}
		}
		if (treeJson.getDragRecords() != null && treeJson.getDragRecords().size() > 0) {
			for (HashMap<String, String> map : treeJson.getDragRecords()) {
				dProcValues.add(getResultValues(grid,map,dProcValuesParm));
			}
		}
	return  dProcValues.length()==0?"":(" '"+dProcValues.toString()+"' ");
}
private  String getResultValues(GTGrid grid,HashMap map, String parm){
	String str = grid.getRecordValues(map, parm);
	str = str.replaceAll("','", ",");
	return str.substring(1, str.length() - 1);//去掉'引号
}
	protected int getType(SqlRowSetMetaData md,String id){
		int s=3;
		for(int i=1;i<=md.getColumnCount();i++){
			if(id.equalsIgnoreCase(md.getColumnName(i))) {
				s= md.getColumnType(i);
				break;
			}
		}
		switch(s){
		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:
		return 1;
		case Types.BINARY:
		case Types.BLOB:
		case Types.CLOB:
		case Types.NCLOB:
			return 2;
		case Types.DATE:
		case Types.TIME:
		case Types.TIMESTAMP:
			return 4;//日期
		default:
			return 3;
		}
	}
}