package cn.com.capcloud.api.action.myself.system.data;

import cn.com.capcloud.api.action.service.ApiDao;
import cn.com.capcloud.api.action.service.ApiService;
import cn.com.capcloud.api.action.service.DataSourceService;
import cn.com.capcloud.api.action.service.util.TextUtil;
import cn.com.capcloud.ccui.core.common.json.JackJSON;
import cn.com.capcloud.ccui.core.common.json.JackJSONArray;
import cn.com.capcloud.ccui.core.common.json.JackJSONObject;
import cn.com.capcloud.ccui.core.common.utils.StringUtil;
import cn.com.capcloud.core.noentitydb.*;
import cn.com.capcloud.core.cache.KHCRedisCache;
import cn.com.capcloud.core.cache.BaseConfigStaticCache;
import cn.com.capcloud.core.system.user.UserService;
import org.joda.time.DateTime;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import org.springframework.beans.factory.annotation.Autowired;
import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class DataVmTableAction extends DefaultAction {
    //查询组件的默认数据(KEY)
    private static final String SYSTEM_SETTINGS_DS_QUERY_JSON="system.settings.ds.query.json";
    //表单组件的默认数据(KEY)
    private static final String SYSTEM_SETTINGS_DS_FORM_JSON="system.settings.ds.form.json";
    @Autowired
    private BaseService baseService;
    @Autowired
    private UserService userService;
    @Autowired
    private ApiService apiService;
    @Autowired
    private KHCRedisCache khcRedisCache;
    @Autowired
    private DataSourceAction dataSourceAction;
    @Autowired
    private DataSourceService dsService;
    private static DataVmTableAction action;
    @PostConstruct
    public void init(){
        action=this;
        action.baseService=this.baseService;
        action.userService=this.userService;
        action.apiService =this.apiService;
        action.dataSourceAction =this.dataSourceAction;
        action.khcRedisCache =this.khcRedisCache;
        action.dsService =this.dsService;
    }

    @Override
    public String buildSql(String jsonStr, JackJSONObject retJson, HttpServletRequest request) {
        JackJSONObject json= JackJSONObject.fromObject(jsonStr);
        String pageParentUuid=String.valueOf(json.getJackJSONObject("queryParam").get("page_parent_uuid"));
        Map<String,Object> dsMap=action.dsService.findDataSourceByUuid(pageParentUuid);
        String tableName=String.valueOf(dsMap.get("ds_table_name"));
        String dsName=String.valueOf(dsMap.get("ds_name"));
        List<Map<String,Object>> tableList=null;
        if(DBCheck.isKingbase()){
            try {
                tableList = this.getJdbcTemplate().queryForList("select sys_relation_filepath('"+tableName+"');");
            } catch (DataAccessException e) {
                tableList=new ArrayList<>();
            }
        }else if(DBCheck.isH2()){
            tableList = this.getJdbcTemplate().queryForList("SELECT TABLE_NAME\n" +
                    "FROM INFORMATION_SCHEMA.TABLES\n" +
                    "WHERE TABLE_SCHEMA = 'PUBLIC'   -- 数据库模式，一般是 PUBLIC\n" +
                    "  AND TABLE_NAME LIKE '"+tableName+"';");
        }else{
            tableList = this.getJdbcTemplate().queryForList("SHOW TABLES LIKE '"+tableName+"'");
        }
        if(tableList.size()==0){
            initTable(tableName,dsName);
        }
        List<Map<String,Object>> list=new ArrayList<>();
        if(dsMap!=null){
            String countSql="SELECT count(1) FROM tbl_data_form WHERE page_parent_uuid='"+dsMap.get("uuid")+"' AND is_bpm_model='Y' AND bpm_model_id!=''";
            Integer count=this.getJdbcTemplate().queryForObject(countSql,Integer.class);
            if(count>0){
                action.dataSourceAction.initBpmField(tableName);
            }
            if(!"null".equals(tableName)&&tableName.length()>0){
                String sql="";
                if(DBCheck.isKingbase()){
                    sql="SELECT a.attnum,\n" +
                            "       a.attname AS column_name,\n" +
                            "       t.typname AS data_type,\n" +
                            "       CASE WHEN a.atttypmod<=0 THEN null ELSE (a.atttypmod-4) END AS lengthvar,\n" +
                            "       a.attnotnull AS notnull,\n" +
                            "       b.description AS comment\n" +
                            "  FROM sys_class c,\n" +
                            "       sys_attribute a\n" +
                            "       LEFT OUTER JOIN sys_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
                            "       sys_type t\n" +
                            " WHERE c.relname = '"+tableName+"'\n" +
                            "       and a.attnum > 0\n" +
                            "       and a.attrelid = c.oid\n" +
                            "       and a.atttypid = t.oid\n";
                    String fieldName=String.valueOf(json.getJackJSONObject("queryParam").get("field"));
                    if(!"null".equals(fieldName)&&fieldName.length()>0){
                        sql+= " and a.attname ='"+fieldName+"'";
                    }
                    sql+=" ORDER BY a.attnum";
                }else if(DBCheck.isH2()){
                    sql="SELECT\n" +
                            "    c.COLUMN_NAME AS \"Field\",\n" +
                            "    c.DATA_TYPE AS \"Type\",\n" +
                            "    c.CHARACTER_MAXIMUM_LENGTH AS \"Length\",\n" +
                            "    c.IS_NULLABLE AS \"Null\",\n" +
                            "    c.COLUMN_DEFAULT AS \"Default\",\n" +
                            "    c.IS_IDENTITY AS \"Extra\",\n" +
                            "    CASE WHEN pk.COLUMN_NAME IS NOT NULL THEN 'PRI' ELSE '' END AS \"Key\",\n" +
                            "    c.REMARKS AS \"Comment\",\n" +
                            "    c.ORDINAL_POSITION AS \"Ordinal\"\n" +
                            "FROM INFORMATION_SCHEMA.COLUMNS c\n" +
                            "LEFT JOIN (\n" +
                            "    SELECT kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.COLUMN_NAME\n" +
                            "    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc\n" +
                            "    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu\n" +
                            "      ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME\n" +
                            "     AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA\n" +
                            "     AND tc.TABLE_NAME = kcu.TABLE_NAME\n" +
                            "    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'\n" +
                            ") pk\n" +
                            "  ON c.TABLE_SCHEMA = pk.TABLE_SCHEMA\n" +
                            " AND c.TABLE_NAME = pk.TABLE_NAME\n" +
                            " AND c.COLUMN_NAME = pk.COLUMN_NAME\n" +
                            "WHERE c.TABLE_SCHEMA = 'PUBLIC'\n" +
                            "  AND c.TABLE_NAME = '"+tableName+"'\n" +
                            "ORDER BY c.ORDINAL_POSITION;";
                }else{
                    sql="SHOW FULL COLUMNS FROM "+tableName;
                    String fieldName=String.valueOf(json.getJackJSONObject("queryParam").get("field"));
                    if(!"null".equals(fieldName)&&fieldName.length()>0){
                        sql+= " where field like '%"+fieldName+"%'";
                    }
                }
                list= JackJSON.parseObject(TextUtil.nullToStrFilter(this.getJdbcTemplate().queryForList(sql)), ArrayList.class);
            }
            if(DBCheck.isKingbase()){
                String pkFieldName=BaseDao.getPkFieldNameByTableName(tableName,getJdbcTemplate());
                for (int i = 0; i < list.size(); i++) {
                    String field=String.valueOf(list.get(i).get("column_name"));
                    list.get(i).put("field",field);
                    if(pkFieldName.equals(field)){
                        list.get(i).put("key","PRI");
                    }else{
                        list.get(i).put("key","");
                    }
                    String type=String.valueOf(list.get(i).get("data_type"));
                    list.get(i).put("type",type);
                    list.get(i).put("length",list.get(i).get("lengthvar"));
                    list.get(i).put("id",field);
                    list.get(i).put("table_name",tableName);
                    list.get(i).remove("notnull");
                    list.get(i).remove("attnum");
                    list.get(i).remove("lengthvar");
                    list.get(i).remove("data_type");
                    list.get(i).remove("notnull");
                    list.get(i).remove("column_name");
                }
            }else{
                for (int i = 0; i < list.size(); i++) {
                    String field=String.valueOf(list.get(i).get("Field"));
                    String defaultValue=String.valueOf(list.get(i).get("Default"));
                    list.get(i).put("field",field);
                    list.get(i).put("comment",list.get(i).get("Comment"));
                    list.get(i).put("key",list.get(i).get("Key"));
                    String type=String.valueOf(list.get(i).get("Type"));
                    String length ="";
                    if(DBCheck.isH2()){
                        length=String.valueOf(list.get(i).get("Length"));
                        // 去掉前后的单引号（如果存在）
                        if (defaultValue.startsWith("'") && defaultValue.endsWith("'")) {
                            defaultValue = defaultValue.substring(1, defaultValue.length() - 1);
                        }
                    }else{
                        if(type.contains("(")){
                            length = type.substring(type.indexOf("(")+1,type.indexOf(")"));
                            type=type.substring(0,type.indexOf("("));
                        }
                    }
                    list.get(i).put("type",type);
                    list.get(i).put("length",length);
                    list.get(i).put("id",field);
                    list.get(i).put("default_value",defaultValue);
                    list.get(i).put("table_name",tableName);
                    list.get(i).remove("Null");
                    list.get(i).remove("Extra");
                    list.get(i).remove("Privileges");
                    list.get(i).remove("Collation");
                    list.get(i).remove("Default");
                    list.get(i).remove("Field");
                    list.get(i).remove("Comment");
                    list.get(i).remove("Key");
                    list.get(i).remove("Type");
                }
            }
        }
        JtPage jtPage=new JtPage();
        jtPage.setContent(list);
        jtPage.setSize(list.size());
        jtPage.setNumber(0);
        jtPage.setTotalPages(1);
        jtPage.setTotalElements(list.size());
        jtPage.setNumberOfElements(list.size());
        retJson.putAll(JackJSONObject.fromObject(action.apiService.getReturnDataByCode("ok")));
        retJson.put("data",jtPage);
        return "-1";
    }

    @Override
    public JackJSONArray saveBefore(String jsonStr, JackJSONObject retJson, HttpServletRequest request, JackJSONArray array) {
        if(array.size()>1){
            retJson.put("message","批量保存待开发（扩展类）");
            return new JackJSONArray();
        }
        String pageParentUuid=String.valueOf(array.getJackJSONObject(0).get("page_parent_uuid"));
        Map<String,Object> dsMap=action.dsService.findDataSourceByUuid(pageParentUuid);
        String tableName=String.valueOf(dsMap.get("ds_table_name"));
        String id=String.valueOf(array.getJackJSONObject(0).get("id"));
        String fieldName=String.valueOf(array.getJackJSONObject(0).get("field"));
        String fieldType=String.valueOf(array.getJackJSONObject(0).get("type"));
        String fieldComment=String.valueOf(array.getJackJSONObject(0).get("comment"));
        String fieldLength=String.valueOf(array.getJackJSONObject(0).get("length"));
        String defaultValue=String.valueOf(array.getJackJSONObject(0).get("default_value"));
        String defaultSql="";
        if(DBCheck.isKingbase()){
            if(!fieldType.equals("text")&&!fieldType.equals("int4")){
                if(!"".equals(fieldLength)){
                    fieldType=fieldType+"("+fieldLength+")";
                }else{
                    retJson.put("message","保存失败，原因是："+fieldName+" 字段未指定长度");
                    return new JackJSONArray();
                }
            }
            //alter table tbl_log_user_password_update alter create_org_name type varchar(103 char);
            String sql="";
            if(!"null".equals(id)&&id.length()>0){//编辑
                sql += "ALTER TABLE " + tableName + " ALTER " + fieldName +
                        " TYPE " + fieldType ;
            }else{
                //新增（这个用不到，新增走的是：addSaveBefore）
            }
            this.getJdbcTemplate().execute(sql);
            this.getJdbcTemplate().execute("comment on column "+tableName+"."+fieldName+" is '"+fieldComment+"'");
        }else if(DBCheck.isH2()){
            if(!fieldType.equals("integer")&&!fieldType.equals("character large object")){
                if(!"".equals(fieldLength)){
                    fieldType=fieldType+"("+fieldLength+")";
                }else{
                    retJson.put("message","保存失败，原因是："+fieldName+" 字段未指定长度");
                    return new JackJSONArray();
                }
            }
            String sql="";
            if(!"null".equals(id)&&id.length()>0){//编辑
                sql += "ALTER TABLE " + tableName + " ALTER " + fieldName +
                        " TYPE " + fieldType ;
            }else{
                //新增（这个用不到，新增走的是：addSaveBefore）
            }
            this.getJdbcTemplate().execute(sql);
            this.getJdbcTemplate().execute("comment on column "+tableName+"."+fieldName+" is '"+fieldComment+"'");
        }else{
            if(!fieldType.equals("text")&&!fieldType.equals("longtext")){
                if("".equals(fieldLength)){
                    retJson.put("message","保存失败，原因是："+fieldName+" 字段未指定长度");
                    return new JackJSONArray();
                }
                if(fieldType.equals("int")&&!defaultValue.equals("")){
                    defaultSql+=" DEFAULT '"+defaultValue+"'";
                }else if(fieldType.equals("varchar")){
                    defaultSql+=" DEFAULT '"+defaultValue+"'";
                }
                fieldType=fieldType+"("+fieldLength+")";
            }
            String sql="";
            if(!"null".equals(id)&&id.length()>0){//编辑
                sql += "ALTER TABLE `" + tableName + "` \n" + "CHANGE COLUMN `" + fieldName + "` `" + fieldName
                        + "` " + fieldType + defaultSql+" COMMENT '"+fieldComment+"'";
            }else{
                //新增（这个用不到，新增走的是：addSaveBefore）
            }
            this.getJdbcTemplate().execute(sql);
        }
        retJson.putAll(JackJSONObject.fromObject(action.apiService.getReturnDataByCode("ok")));
        Map<String,Object> map=new HashMap<>();
        map.put("saveData",array);
        retJson.put("data",map);
        return new JackJSONArray();
    }
    public JackJSONArray addSaveBefore(String jsonStr, JackJSONObject retJson, HttpServletRequest request, JackJSONArray array) {
        String pageParentUuid=String.valueOf(array.getJackJSONObject(0).get("page_parent_uuid"));
        Map<String,Object> dsMap=action.dsService.findDataSourceByUuid(pageParentUuid);
        String tableName=String.valueOf(dsMap.get("ds_table_name"));
        String id=String.valueOf(array.getJackJSONObject(0).get("id"));
        String fieldName=String.valueOf(array.getJackJSONObject(0).get("field"));
        String fieldType=String.valueOf(array.getJackJSONObject(0).get("type"));
        String fieldComment=String.valueOf(array.getJackJSONObject(0).get("comment"));
        String fieldLength=String.valueOf(array.getJackJSONObject(0).get("length"));
        String defaultValue=String.valueOf(array.getJackJSONObject(0).get("default_value"));
        String defaultSql="";
        if(DBCheck.isKingbase()){
            if(!fieldType.equals("text")&&!fieldType.equals("TEXT")){
                if(!"".equals(fieldLength)){
                    fieldType=fieldType+"("+fieldLength+")";
                }else{
                    retJson.put("message","保存失败，原因是："+fieldName+" 字段未指定长度");
                    return new JackJSONArray();
                }
            }
            String querySql="SELECT a.attnum,\n" +
                    "       a.attname AS column_name,\n" +
                    "       t.typname AS data_type,\n" +
                    "       CASE WHEN a.atttypmod<=0 THEN null ELSE (a.atttypmod-4) END AS lengthvar,\n" +
                    "       a.attnotnull AS notnull,\n" +
                    "       b.description AS comment\n" +
                    "  FROM sys_class c,\n" +
                    "       sys_attribute a\n" +
                    "       LEFT OUTER JOIN sys_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,\n" +
                    "       sys_type t\n" +
                    " WHERE c.relname = '"+tableName+"'\n" +
                    "       and a.attnum > 0\n" +
                    "       and a.attrelid = c.oid\n" +
                    "       and a.atttypid = t.oid\n"+
                    "       and a.attname ='"+fieldName+"'"+
                    " ORDER BY a.attnum";
            List<Map<String, Object>> colList=this.getJdbcTemplate().queryForList(querySql);
            if(colList.size()>0){
                retJson.put("status","no_code");
                retJson.put("message","保存失败，原因是："+fieldName+" 字段已存在");
                return new JackJSONArray();
            }
            String sql="";
            if(!"null".equals(id)&&id.length()>0){
                sql += "ALTER TABLE " + tableName + " ALTER " + fieldName +
                        " TYPE " + fieldType ;
            }else{
                sql = "ALTER TABLE " + tableName + " " + "ADD COLUMN " + fieldName
                        + " "+fieldType;
            }
            this.getJdbcTemplate().execute(sql);
            this.getJdbcTemplate().execute("comment on column "+tableName+"."+fieldName+" is '"+fieldComment+"'");
        }else if(DBCheck.isH2()){
            if(!fieldType.equals("integer")&&!fieldType.equals("character large object")){
                if(!"".equals(fieldLength)){
                    fieldType=fieldType+"("+fieldLength+")";
                }else{
                    retJson.put("message","保存失败，原因是："+fieldName+" 字段未指定长度");
                    return new JackJSONArray();
                }
            }
            String sql="";
            if(!"null".equals(id)&&id.length()>0){
                sql += "ALTER TABLE " + tableName + " ALTER " + fieldName +
                        " TYPE " + fieldType ;
            }else{
                sql = "ALTER TABLE " + tableName + " " + "ADD COLUMN " + fieldName
                        + " "+fieldType;
            }
            this.getJdbcTemplate().execute(sql);
            this.getJdbcTemplate().execute("comment on column "+tableName+"."+fieldName+" is '"+fieldComment+"'");
        }else{
            if(!fieldType.equals("text")&&!fieldType.equals("longtext")){
                if("".equals(fieldLength)){
                    retJson.put("message","保存失败，原因是："+fieldName+" 字段未指定长度");
                    return new JackJSONArray();
                }
                if(fieldType.equals("int")&&!defaultValue.equals("")){
                    defaultSql+=" DEFAULT '"+defaultValue+"'";
                }else if(fieldType.equals("varchar")){
                    defaultSql+=" DEFAULT '"+defaultValue+"'";
                }
                fieldType=fieldType+"("+fieldLength+")";
            }
            String sql="";
            String querySql="SHOW FULL COLUMNS FROM `"+tableName+"` WHERE FIELD='"+fieldName+"'";
            List<Map<String, Object>> colList=this.getJdbcTemplate().queryForList(querySql);
            if(colList.size()>0){
                retJson.put("status","no_code");
                retJson.put("message","保存失败，原因是："+fieldName+" 字段已存在");
                return new JackJSONArray();
            }
            if(!"null".equals(id)&&id.length()>0){
                sql += "ALTER TABLE `" + tableName + "` \n" + "CHANGE COLUMN `" + fieldName + "` `" + fieldName
                        + "` " + fieldType + defaultSql+" COMMENT '"+fieldComment+"'";
            }else{
                sql = "ALTER TABLE `" + tableName + "` \n" + "ADD COLUMN `" + fieldName
                        + "` "+fieldType+defaultSql+" COMMENT '"+fieldComment+"'";
            }
            this.getJdbcTemplate().execute(sql);
        }
        initFiled(tableName,fieldName);
        retJson.putAll(JackJSONObject.fromObject(action.apiService.getReturnDataByCode("ok")));
        Map<String,Object> map=new HashMap<>();
        map.put("saveData",array);
        retJson.put("data",map);
        return new JackJSONArray();
    }
    public Map<String,Object> getPkFieldInfo(String jsonStr, JackJSONObject retJson, HttpServletRequest request){
        JackJSONObject json= JackJSONObject.fromObject(jsonStr);
        String uuid= String.valueOf(json.get("page_parent_uuid"));
        String sql="select * from tbl_data_source where uuid='"+uuid+"'";
        List<Map<String, Object>> list= this.getJdbcTemplate().queryForList(sql);
        Map<String,Object> map=new HashMap<>();
        map.put("data","");
        if(list.size()>0){
            String showTableSql="SHOW TABLE STATUS LIKE '"+list.get(0).get("ds_table_name")+"';";
            List<Map<String, Object>> tableList= this.getJdbcTemplate().queryForList(showTableSql);
            Integer autoId=Integer.valueOf(String.valueOf(tableList.get(0).get("Auto_increment")));
            map.put("data",autoId);
        }
        map.put("message","查询成功");
        return map;
    }

    public Map<String,Object> showCreateTableSql(String jsonStr, JackJSONObject retJson, HttpServletRequest request){
        JackJSONObject json= JackJSONObject.fromObject(jsonStr);
        String uuid= String.valueOf(json.get("page_parent_uuid"));
        String sql="select * from tbl_data_source where uuid='"+uuid+"'";
        List<Map<String, Object>> list= this.getJdbcTemplate().queryForList(sql);
        Map<String,Object> map=new HashMap<>();
        map.put("data","");
        if(list.size()>0){
            String showTableSql="SHOW CREATE TABLE "+list.get(0).get("ds_table_name")+";";
            List<Map<String, Object>> tableList= this.getJdbcTemplate().queryForList(showTableSql);
            if(tableList.size()>0){
                String createTableSql=String.valueOf(String.valueOf(tableList.get(0).get("Create Table")));
                map.put("data",createTableSql);
            }else{
                map.put("data","");
            }
        }
        map.put("message","查询成功");
        return map;
    }

    public JackJSONArray editPkFieldSaveBefore(String jsonStr, JackJSONObject retJson, HttpServletRequest request, JackJSONArray array) {
        String pageParentUuid=String.valueOf(array.getJackJSONObject(0).get("page_parent_uuid"));
        Map<String,Object> dsMap=action.dsService.findDataSourceByUuid(pageParentUuid);
        String tableName=String.valueOf(dsMap.get("ds_table_name"));
        String myselfAuto=String.valueOf(array.getJackJSONObject(0).get("myself_auto"));
        String sql = "ALTER TABLE "+tableName+" AUTO_INCREMENT="+myselfAuto;
        this.getJdbcTemplate().execute(sql);
        retJson.putAll(JackJSONObject.fromObject(action.apiService.getReturnDataByCode("ok")));
        Map<String,Object> map=new HashMap<>();
        map.put("saveData",array);
        retJson.put("data",map);
        return new JackJSONArray();
    }

    public void initFiled(String tableName,String fieldName){
        initFieldQuery(tableName,fieldName);
        initFieldForm(tableName,fieldName);
        action.khcRedisCache.deleteAllQueryInfo();
        action.khcRedisCache.deleteAllFormInfo();
    }
    public void initFieldQuery(String tableName,String fieldName){

        List<Map<String,Object>> dsList=this.getJdbcTemplate().queryForList("SELECT * FROM tbl_data_source WHERE STATUS=0 AND ds_table_name='"+tableName+"'");
        for (int i = 0; i < dsList.size(); i++) {
            List<Map<String,Object>> queryList=this.getJdbcTemplate().queryForList("SELECT * FROM tbl_data_query WHERE STATUS=0 AND page_parent_uuid='"+dsList.get(i).get("uuid")+"'");
            for (int j = 0; j < queryList.size(); j++) {
                if(DBCheck.isKingbase()){
                    String whereSql=" AND a.attname='"+fieldName+"' ";//kingbase写法
                    List<Map<String,Object>> colInfoList= BaseDao.getAllCommentsByTableNameNotPkField(tableName, this.getJdbcTemplate(),whereSql);
                    String name=String.valueOf(colInfoList.get(0).get("column_name"));
                    String queryUUID=String.valueOf(queryList.get(j).get("uuid"));
                    String sql="select count(1) from tbl_data_query_info where status=0 and name='"+name+"' and page_parent_uuid='"+queryUUID+"'";
                    Integer count=this.getJdbcTemplate().queryForObject(sql,Integer.class);
                    if(count==0){
                        Map<String,Object> viewMap= JackJSON.parseObject(String.valueOf(BaseConfigStaticCache.get(SYSTEM_SETTINGS_DS_QUERY_JSON)), HashMap.class);
                        if(colInfoList.get(i).get("data_type").toString().indexOf("int4")>=0) {
                            viewMap.put("type","long");
                        }else {
                            viewMap.put("type","String");
                        }
                        if(name.equals("status")){
                            viewMap.put("default_value", "0");
                        }
                        DateTime d = new DateTime();
                        viewMap.put("create_time", d.toString("yyyy-MM-dd HH:mm:ss"));
                        viewMap.put("page_parent_uuid", queryUUID);
                        viewMap.put("name",name);
                        viewMap.put("label_name", String.valueOf(colInfoList.get(0).get("comment")));
                        action.baseService.save("tbl_data_query_info",viewMap);
                    }
                }else{
                    List<Map<String,Object>> colInfoList;
                    if(DBCheck.isH2()){
                        String whereSql=" and c.COLUMN_NAME ='"+fieldName+"'";
                        colInfoList= BaseDao.getAllCommentsByTableName(tableName, this.getJdbcTemplate(),whereSql);
                    }else{
                        String whereSql=" where FIELD='"+fieldName+"'";//mysql写法
                        colInfoList= BaseDao.getAllCommentsByTableName(tableName+whereSql, this.getJdbcTemplate());
                    }
                    String name=String.valueOf(colInfoList.get(0).get("Field"));
                    String queryUUID=String.valueOf(queryList.get(j).get("uuid"));
                    String sql="select count(1) from tbl_data_query_info where status=0 and name='"+name+"' and page_parent_uuid='"+queryUUID+"'";
                    Integer count=this.getJdbcTemplate().queryForObject(sql,Integer.class);
                    if(count==0){
                        Map<String,Object> viewMap= JackJSON.parseObject(String.valueOf(BaseConfigStaticCache.get(SYSTEM_SETTINGS_DS_QUERY_JSON)), HashMap.class);
                        if(colInfoList.get(0).get("Type").toString().indexOf("int")>=0||colInfoList.get(0).get("Type").toString().indexOf("integer")>=0) {
                            viewMap.put("type","long");
                        }else {
                            viewMap.put("type","String");
                        }
                        if(name.equals("status")){
                            viewMap.put("default_value", "0");
                        }
                        DateTime d = new DateTime();
                        viewMap.put("create_time", d.toString("yyyy-MM-dd HH:mm:ss"));
                        viewMap.put("page_parent_uuid", queryUUID);
                        viewMap.put("name",name);
                        viewMap.put("label_name", String.valueOf(colInfoList.get(0).get("Comment")));
                        action.baseService.save("tbl_data_query_info",viewMap);
                    }
                }
            }
        }
    }
    public void initFieldForm(String tableName,String fieldName){
        List<Map<String,Object>> dsList=this.getJdbcTemplate().queryForList("SELECT * FROM tbl_data_source WHERE STATUS=0 AND ds_table_name='"+tableName+"'");
        for (int i = 0; i < dsList.size(); i++) {
            List<Map<String,Object>> formList=this.getJdbcTemplate().queryForList("SELECT * FROM tbl_data_form WHERE STATUS=0 AND page_parent_uuid='"+dsList.get(i).get("uuid")+"'");
            for (int j = 0; j < formList.size(); j++) {
                if(DBCheck.isKingbase()){
                    String whereSql=" AND a.attname='"+fieldName+"' ";//kingbase写法
                    List<Map<String,Object>> colInfoList= BaseDao.getAllCommentsByTableNameNotPkField(tableName, this.getJdbcTemplate(),whereSql);
                    String name=String.valueOf(colInfoList.get(0).get("column_name"));
                    String formUUID=String.valueOf(formList.get(j).get("uuid"));
                    String sql="select count(1) from tbl_data_form_info where status=0 and name='"+name+"' and page_parent_uuid='"+formUUID+"'";
                    Integer count=this.getJdbcTemplate().queryForObject(sql,Integer.class);
                    if(count==0){
                        Map<String,Object> viewMap= JackJSON.parseObject(String.valueOf(BaseConfigStaticCache.get(SYSTEM_SETTINGS_DS_FORM_JSON)), HashMap.class);
                        if(colInfoList.get(i).get("data_type").toString().indexOf("int4")>=0) {
                            viewMap.put("type","long");
                        }else {
                            viewMap.put("type","String");
                        }
                        if(name.equals("status")){
                            viewMap.put("default_value", "0");
                        }
                        DateTime d = new DateTime();
                        viewMap.put("create_time", d.toString("yyyy-MM-dd HH:mm:ss"));
                        viewMap.put("page_parent_uuid", formUUID);
                        viewMap.put("name",name);
                        viewMap.put("label_name", String.valueOf(colInfoList.get(0).get("comment")));
                        action.baseService.save("tbl_data_form_info",viewMap);
                    }
                }else{
                    List<Map<String,Object>> colInfoList;
                    if(DBCheck.isH2()){
                        String whereSql=" and c.COLUMN_NAME ='"+fieldName+"'";
                        colInfoList= BaseDao.getAllCommentsByTableName(tableName, this.getJdbcTemplate(),whereSql);
                    }else{
                        String whereSql=" where FIELD='"+fieldName+"'";//mysql写法
                        colInfoList= BaseDao.getAllCommentsByTableName(tableName+whereSql, this.getJdbcTemplate());
                    }
                    String name=String.valueOf(colInfoList.get(0).get("Field"));
                    String formUUID=String.valueOf(formList.get(j).get("uuid"));
                    String sql="select count(1) from tbl_data_form_info where status=0 and name='"+name+"' and page_parent_uuid='"+formUUID+"'";
                    Integer count=this.getJdbcTemplate().queryForObject(sql,Integer.class);
                    if(count==0){
                        Map<String,Object> viewMap= JackJSON.parseObject(String.valueOf(BaseConfigStaticCache.get(SYSTEM_SETTINGS_DS_FORM_JSON)), HashMap.class);
                        if(colInfoList.get(0).get("Type").toString().indexOf("int")>=0) {
                            viewMap.put("type","long");
                        }else {
                            viewMap.put("type","String");
                        }
                        if(name.equals("status")){
                            viewMap.put("default_value", "0");
                        }
                        DateTime d = new DateTime();
                        viewMap.put("create_time", d.toString("yyyy-MM-dd HH:mm:ss"));
                        viewMap.put("page_parent_uuid", formUUID);
                        viewMap.put("name",name);
                        viewMap.put("label_name", String.valueOf(colInfoList.get(0).get("Comment")));
                        action.baseService.save("tbl_data_form_info",viewMap);
                    }
                }
            }
        }
    }
    public void initTable(String tableName){
        initTable(tableName,"");
    }
    public void initTable(String tableName,String comment){
        if(DBCheck.isKingbase()){
            String createTableSql="CREATE TABLE " + tableName + " ("
                    + "id INT NOT NULL AUTO_INCREMENT,"
                    + "uuid VARCHAR(255) NULL,"
                    + "page_parent_uuid VARCHAR(255) NULL,"
                    + "status VARCHAR(2) NULL,"
                    + "create_user_id VARCHAR(10) NULL,"
                    + "create_org_id VARCHAR(10) NULL,"
                    + "create_org_name VARCHAR(100) NULL,"
                    + "create_user VARCHAR(100) NULL,"
                    + "create_time VARCHAR(100) NULL,"
                    + "create_date VARCHAR(100) NULL,"
                    + "update_user VARCHAR(100) NULL,"
                    + "update_time VARCHAR(100) NULL,"
                    + "update_date VARCHAR(100) NULL,"
                    + "user_area VARCHAR(255) NULL,"
                    + "data_source VARCHAR(10) NULL,"
                    + "form_version VARCHAR(11) NULL,"
                    + "is_history VARCHAR(11) NULL,"
                    + "cron_expression VARCHAR(11) NULL,"
                    + " PRIMARY KEY (id))";
            String updateCommentSql="comment on column "+tableName+".id is '主键' ;" +
                    "comment on column " +tableName+".uuid is 'UUID' ;"+
                    "comment on column " +tableName+".page_parent_uuid is 'ParentUUID' ;"+
                    "comment on column " +tableName+".status is '数据状态' ;"+
                    "comment on column " +tableName+".create_user_id is '创建人id' ;"+
                    "comment on column " +tableName+".create_org_id is '部门id' ;"+
                    "comment on column " +tableName+".create_org_name is '部门名称' ;"+
                    "comment on column " +tableName+".create_user is '创建人' ;"+
                    "comment on column " +tableName+".create_time is '创建时间' ;"+
                    "comment on column " +tableName+".create_date is '创建日期' ;"+
                    "comment on column " +tableName+".update_user is '修改人' ;"+
                    "comment on column " +tableName+".update_time is '修改时间' ;"+
                    "comment on column " +tableName+".update_date is '修改日期' ;"+
                    "comment on column " +tableName+".user_area is '用户查看数据权限' ;"+
                    "comment on column " +tableName+".data_source is '数据来源' ;"+
                    "comment on column " +tableName+".form_version is '表单版本' ;"+
                    "comment on column " +tableName+".is_history is '是否历史' ;"+
                    "comment on column " +tableName+".cron_expression is 'Cron表达式' ;"+
                    "";
            if(StringUtil.isNotEmpty(comment)){
                updateCommentSql+=" COMMENT ON TABLE "+tableName+" IS '"+comment+"';";
            }
            this.getJdbcTemplate().execute(createTableSql);
            this.getJdbcTemplate().execute(updateCommentSql);
            return;
        }
        String createTableSql="CREATE TABLE `" + tableName + "` ("
                + "`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键',"
                + "`uuid` VARCHAR(255) DEFAULT ''  COMMENT 'UUID',"
                + "`page_parent_uuid` VARCHAR(255) DEFAULT ''  COMMENT 'ParentUUID',"
                + "`status` VARCHAR(2) DEFAULT '0' COMMENT '数据状态',"
                + "`create_user_id` VARCHAR(10) DEFAULT '' COMMENT '创建人id',"
                + "`create_org_id` VARCHAR(10) DEFAULT '' COMMENT '部门id',"
                + "`create_org_name` VARCHAR(100) DEFAULT ''  COMMENT '部门名称',"
                + "`create_user` VARCHAR(100) DEFAULT ''  COMMENT '创建人',"
                + "`create_time` VARCHAR(100) DEFAULT ''  COMMENT '创建时间',"
                + "`create_date` VARCHAR(100) DEFAULT ''  COMMENT '创建日期',"
                + "`update_user` VARCHAR(100) DEFAULT ''  COMMENT '修改人',"
                + "`update_time` VARCHAR(100) DEFAULT ''  COMMENT '修改时间',"
                + "`update_date` VARCHAR(100) DEFAULT ''  COMMENT '修改日期',"
                + "`user_area` VARCHAR(255) DEFAULT ''  COMMENT '用户查看数据权限',"
                + "`data_source` VARCHAR(10) DEFAULT ''  COMMENT '数据来源',"
                + "`form_version` VARCHAR(11) DEFAULT '' COMMENT '表单版本',"
                + "`is_history` VARCHAR(11) DEFAULT '' COMMENT '是否历史',"
                + "`cron_expression` VARCHAR(100) DEFAULT '' COMMENT 'Cron表达式',"
                + " PRIMARY KEY (`id`))";
        if(StringUtil.isNotEmpty(comment)){
            createTableSql+=" COMMENT = '"+comment+"'";
        }
        createTableSql+=" DEFAULT CHARACTER SET = utf8;";
        this.getJdbcTemplate().execute(createTableSql);
    }
}

