package com.huigou.uasp.form.application.impl;

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

import com.huigou.uasp.bpm.managment.domain.model.ProcDefinition;
import com.huigou.uasp.bpm.managment.repository.ProcDefinitionRespository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.google.common.base.CaseFormat;
import com.huigou.data.query.model.QueryDescriptor;
import com.huigou.data.query.model.QueryModel;
import com.huigou.uasp.bmp.common.application.BaseApplication;
import com.huigou.uasp.builder.vo.SeleListCustomVO;
import com.huigou.uasp.form.application.CommonUtilApplication;
import com.huigou.uasp.form.domain.model.FormSearchSql;
import com.huigou.uasp.form.domain.model.FormTables;
import com.huigou.uasp.form.domain.query.SafFormsQueryRequest;
import com.huigou.uasp.form.repository.FormSearchSqlRepository;
import com.huigou.uasp.form.repository.FormTablesRepository;
import com.huigou.uasp.model.domain.query.DbTablesQueryRequest;
import com.huigou.uasp.model.repository.DbTablesRepository;
import com.huigou.uasp.util.Utils;


@Service("commonUtilApplication")
public class CommonUtilApplicationImpl extends BaseApplication implements CommonUtilApplication { 

	
	@Autowired
	private FormTablesRepository  formTablesRepository;
	@Autowired
	private DbTablesRepository dbTablesRepository;
	
	@Autowired
	private FormSearchSqlRepository  formSearchSqlRepository;

    @Autowired
    private ProcDefinitionRespository procDefinitionRespository;


	private QueryDescriptor getQueryDescriptor(String queryName) {
	    return this.sqlExecutorDao.getQuery("config/formdesign/safFormslist.xml", queryName);
	}
	
	private QueryDescriptor getQueryDescriptorTables(String queryName) {
	    return this.sqlExecutorDao.getQuery("config/formdesign/dbtables.xml", queryName);
	}
	
	//查询数据库的当前时间
	 public   String queryDBCurrentDate(){

	     QueryModel queryModel = this.sqlExecutorDao.getQueryModel(getQueryDescriptorTables("queryDefaultDate"), new DbTablesQueryRequest());
	    //Map <String, Object>mapData = this.sqlExecutorDao.executeSlicedQuery(queryModel);
	     List <?>rqList=this.generalRepository.queryByNativeSql(queryModel.getSql(),new HashMap<String,Object>());
        return rqList.get(0).toString().substring(0,19);
    }
	 //查询表单中的表 
     @Override
    /*
     * tableType:0 获取所有表，1 主表   2 明细表 
     */
	 public String getTablesByFormId(String formId,int tableType){
		 List <FormTables> tableList=formTablesRepository.findByFormId(formId);
		   String tableName="";
		   for (FormTables table:tableList){
			   if (!"".equals(tableName)){
				   tableName+=",";
			   }
			   if (tableType==0){
				   tableName+=table.getTableName(); 
			   } else if (tableType==1){
			     if (table.getTableType()==1){
			        tableName+=table.getTableName();
			     }
			   } else if (tableType==2){
				  if (table.getTableType()==2){
				    tableName+=table.getTableName();
				  } 
			   }
		   }
		   tableName=tableName.toLowerCase();
		   return tableName;
	 }
     
     
     
     /*
      * 查询表单属性字段
      * @see 字段个数默认最大100
      */
     @Override
     public List<Map<String, Object>> queryDataRecordSingleFields(String formId,String tableName) {
         Map<String, Object> mapData = new HashMap<String, Object>();
         //safFormlist.xml -->queryFormDataRecordFields
         SafFormsQueryRequest queryRequest=new SafFormsQueryRequest();
         QueryModel queryModel = this.sqlExecutorDao.getQueryModel(getQueryDescriptor("queryFormDataRecordFields"), queryRequest);
         queryModel.setPageSize(101);//默认100
         queryModel.setPageIndex(1);
         String sql=queryModel.getSql();
         //查询单值字段
         StringBuffer sb = new StringBuffer("");
       
         // if (queryRequest.getDisplayListing()==1){ //只查询列表显示字段   05.18.2020 lxh close
         //	sb.append(" and a.display_listing=").append(queryRequest.getDisplayListing());
         // } 
           sql+=" and a.form_id='"+formId+"'";
           if (tableName.indexOf(",")>0){  //存在多个表
        	   String []tables=tableName.split(",");
        	   tableName="";
        	   for (int u=0;u<tables.length;u++){
        		   tableName+="'"+tables[u]+"'";
        		   if (u<tables.length-1){
        			   tableName+=",";
        		   }
        	   }
        	   sql+=" and a.table_name in ("+tableName+")";
           } else {
              sql+=" and a.table_name='"+tableName+"'";
           }
           sql+=" order by a.table_type,a.sequence ";
         queryModel.setSortFieldName("sequence");
         queryModel.setSql(sql);
         if ("all".equals(queryModel.getExportType())) {//导出Excel
             queryModel.setExportType(null);
         }
         mapData = this.sqlExecutorDao.executeSlicedQuery(queryModel);
         @SuppressWarnings("unchecked")
         List<Map<String, Object>> list = (List<Map<String, Object>>) mapData.get("Rows");
         return list;
     }
     
     /*
      * 查询明细表中的字段属性
      * @see 字段个数默认最大100
      */
     @Override
     public List<Map<String, Object>> queryDataRecordMultsFields(String formId,String tableName) {
         Map<String, Object> mapData = new HashMap<String, Object>();
         SafFormsQueryRequest queryRequest=new SafFormsQueryRequest();
         QueryModel queryModel = this.sqlExecutorDao.getQueryModel(getQueryDescriptor("queryFormDataRecordFields"), queryRequest);
         queryModel.setPageSize(101);//默认100
         queryModel.setPageIndex(1);
         String sql=queryModel.getSql();
         //查询单值字段
         StringBuffer sb = new StringBuffer(sql);
       
         // if (queryRequest.getDisplayListing()==1){ //只查询列表显示字段   05.18.2020 lxh close
         //	sb.append(" and a.display_listing=").append(queryRequest.getDisplayListing());
         // } 
        
          sb.append(" and a.table_type=2 ");
          sb.append(" and a.form_id='").append(formId).append("'");
          sb.append(" and a.table_name='").append(tableName).append("'");
          sb.append(" order by a.sequence ");
       //  if (queryRequest.getAttrId() != null && !"".endsWith(queryRequest.getAttrId())) {
       //      sb.append(" and a.id='").append(queryRequest.getAttrId()).append("'");   05.18.2020 lxh close
        // }
    /*     if (queryRequest.getSubTab() != null && !"".endsWith(queryRequest.getSubTab())) {
             String attrCode = queryRequest.getSubTab();
             attrCode = attrCode.substring(queryRequest.getMaiTab().length()+1);
           //  if (attrCode.startsWith("_")) {  //如果传递的真实表名 则会有下划线，则去掉
            //     attrCode = attrCode.substring(1);
             //    attrCode=CaseFormat.UPPER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, attrCode);//明细属性可能存在下划线 ， 转回下划线
             //}
             sb.append(" and a.attr_code='").append(attrCode).append("'");
         }*/
        // sb.append(" order by a.sequence ");
         queryModel.setSortFieldName("sequence"); //避免其它的查询把查询字段带过来了 导致查询出错
         queryModel.setSql(sb.toString());
         mapData = this.sqlExecutorDao.executeSlicedQuery(queryModel);
         @SuppressWarnings("unchecked")
         List<Map<String, Object>> list = (List<Map<String, Object>>) mapData.get("Rows");
         return list;
     }
     
     /*
      * 查询表单模型包含的表字段
      * @see 字段个数默认最大100
      */
     @Override
     public List<Map<String, Object>> queryFormTableFields(String formNo,String tableName){
    	 Map<String, Object> mapData = new HashMap<String, Object>();
         //safFormlist.xml -->queryFormTableFields
         SafFormsQueryRequest queryRequest=new SafFormsQueryRequest();
         queryRequest.setFormNo(formNo);
         queryRequest.setTableName(tableName.toUpperCase());
         QueryModel queryModel = this.sqlExecutorDao.getQueryModel(getQueryDescriptor("queryFormTableFields"), queryRequest);
         queryModel.setPageSize(101);//默认100
         queryModel.setPageIndex(1);
         queryModel.setSortFieldName("nums");
         //queryModel.setOrderBy("nums");
        // String sql=queryModel.getSql();
         mapData = this.sqlExecutorDao.executeSlicedQuery(queryModel);
         @SuppressWarnings("unchecked")
         List<Map<String, Object>> list = (List<Map<String, Object>>) mapData.get("Rows");
         return list;
    }
     
     /**
	    * 获取隐藏字段
	    *  param: tableId,tableName ;toCamel:1 转驼峰法  0下划线 表示返回类型
	    */
	 public   List<String> queryTableHiddenFields(DbTablesQueryRequest queryRequest,int toCamel){
		// DbTablesQueryRequest queryRequest=new DbTablesQueryRequest();
		// queryRequest.setTableId(tableId);
		 queryRequest.setIsHidden(1);
	     QueryModel queryModel = this.sqlExecutorDao.getQueryModel(getQueryDescriptorTables("dbTableFields"), queryRequest);
         queryModel.setPageSize(101);//默认100
         queryModel.setPageIndex(1);
       //  String sql=queryModel.getSql();
         Map <String, Object>mapData = this.sqlExecutorDao.executeSlicedQuery(queryModel);
         @SuppressWarnings("unchecked")
		List<Map<String, Object>> list = (List<Map<String, Object>>) mapData.get("Rows");
         List<String> slist=new ArrayList<String>();
         String hiddenField="";
         for (Map <String, Object>map:list){
        	 hiddenField=map.get("fieldCode").toString();
        	 if (toCamel==1){
        	   hiddenField= CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL,hiddenField); //转成驼峰法
        	 }
        	 slist.add(hiddenField);
         }
         return slist;
	 }
	 /**
	  * 获取检索SQL中的隐藏字段
	  */
	 public   List<String> querySearchSQLHiddenFields(String  formNo){
	 
	  List <FormSearchSql> customlists=formSearchSqlRepository.searchSqlByFormNo(formNo);
     // List <SeleListCustomVO> listCustoms=new  ArrayList<SeleListCustomVO>();
      SeleListCustomVO customVO=null;Map <String, Object>map=null;
     //String []fieldEns=new String[singleList.size()];
      List<String> hiddenFields=new ArrayList<String>();
      for (int i=0;i<customlists.size();i++){
   	     customVO=new SeleListCustomVO();
	     Utils.CopyBean(customlists.get(i),customVO);
	     hiddenFields.addAll(Utils.getHiddenFields(customVO));
      }
       return   hiddenFields;
	 }

     //获取流程定义的信息
     @Override
    public String getProcDefinitionByFormNo(String formNo) {
        ProcDefinition procDefinition = this.procDefinitionRespository.findOne(((root, query, cb) ->
                cb.and(cb.equal(root.get("formNo"), formNo),
                        cb.equal(root.get("nodeKindId"), "proc"))
        ));
        String procId = "";
        if (procDefinition != null) {
            procId = procDefinition.getProcId();
        }
        return  procId;
    }


    /*
     * 公共查询表单数据的值方法
     * 返回 List <Map<String, Object>>
     */
    @Override
    public List<Map<String, Object>> queryFormTableDatas(String sql){

        Map<String, Object> mapData = new HashMap<String, Object>();
        SafFormsQueryRequest queryRequest=new SafFormsQueryRequest();
        QueryModel queryModel = this.sqlExecutorDao.getQueryModel(getQueryDescriptor("queryFormTableFields"), queryRequest);
        queryModel.setPageSize(101);//默认100
        queryModel.setPageIndex(1);
        sql+=" order by id ";
        queryModel.setSortFieldName("");
        queryModel.setSql(sql);
        if ("all".equals(queryModel.getExportType())) {//导出Excel
            queryModel.setExportType(null);
        }
        mapData = this.sqlExecutorDao.executeSlicedQuery(queryModel);
       // @SuppressWarnings("unchecked")
        return  (List<Map<String, Object>>) mapData.get("Rows");

    }
}
