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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.google.common.base.CaseFormat;
import com.huigou.data.repository.GeneralRepository;
import com.huigou.uasp.bmp.common.application.BaseApplication;


@Service("dbTableService")
public class DbTableService extends BaseApplication {


	@Autowired
	private GeneralRepository generalRepository;

	//查询当前模型对应的表名
	public String getTableName(String modelId) {
		String tablesql = " select m.table_name  from SAF_TABLES m where m.id='" + modelId + "'";
		List<?> talist = this.generalRepository.queryByNativeSql(tablesql, new HashMap<>());
		String tableName = "";
		if (talist != null && talist.size() > 0) {
			tableName = talist.get(0).toString().toLowerCase();
		}
		return tableName;
	}

	//创建表
	public String createTable(String tableName, String fieldCodes, String fieldTypes) {
		Connection conn = null;
		String backStr = "";
		try {
			if (tableName.length() > 24) {
				backStr = "表名称太长，不能大于24个字符！";
				return backStr;
			}
			conn = this.sqlExecutorDao.getDataSource().getConnection();


			String strSql = "create table " + tableName;

			StringBuffer sb = new StringBuffer("");
			sb.append("create table ").append(tableName).append("(");

			String[] fieldCode = fieldCodes.split(";");
			String[] fieldType = fieldTypes.split(";");

			for (int i = 0; i < fieldCode.length; i++) {
				sb.append(fieldCode[i]);
				sb.append(" ");
				sb.append(fieldType[i]);
				if ("id".equals(fieldCode[i])) {
					sb.append(" not null ");
				}
				if (i < fieldCode.length - 1) {
					sb.append(", ");
				}
			}
			sb.append(")");


			PreparedStatement ps = conn.prepareStatement(sb.toString());
			ps.execute();
			String pkId = "PK_" + tableName + "_ID";
			if (pkId.length() > 30) {
				int len = pkId.length() - 30;
				pkId = "PK_" + tableName.substring(len, tableName.length()) + "_ID";
			}
			strSql = "alter table " + tableName + " add constraint " + pkId + "  primary key (ID)";
			ps = conn.prepareStatement(strSql);
			ps.execute();

			//strSql=" comment on column "+tableName+".model_id is '模型ID'";
			//  ps=conn.prepareStatement(strSql);
			// ps.execute();

			// System.out.println("Create tableName==="+tableName+"=fieldCodes="+fieldCodes);

			ps.close();
		} catch (Exception e) {
			backStr = e.getMessage();
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
				backStr = e.getMessage();
				e.printStackTrace();
			}
		}
		return backStr;
	}

	// 组合数据字段、类型等
	/*
	 * hasProcess:1、表示含有流程的
	 */
	public String[] composizeFieldContent(List<Map<String, Object>> fieldList, String hasProcess) {
		//String fieldTypes = "";// string,number,date
		//String fieldCodes = "";
		// String fieldNames="";
		int fieldLength = 10;
		int fieldDecimal = 0;
		String backStr[] = new String[2];
		StringBuffer fieldCodes = new StringBuffer("");
		StringBuffer fieldTypes = new StringBuffer("");

		Map<String, Object> mapData = new HashMap<String, Object>();
		for (int i = 0; i < fieldList.size(); i++) {
			mapData = (Map<String, Object>) fieldList.get(i);
			fieldCodes.append(mapData.get("fieldCode").toString());
			// fieldNames+=mapData.get("fieldName").toString();
			fieldLength = Integer.parseInt(mapData.get("fieldLength").toString());
			if (mapData.get("fieldDecimal") != null && !"".equals(mapData.get("fieldDecimal").toString())) {
				fieldDecimal = Integer.parseInt(mapData.get("fieldDecimal").toString());
			}
			if ("string".equals(mapData.get("fieldDataType").toString())) {
				fieldTypes.append("VARCHAR2(").append(fieldLength).append(")");      //如果是mysql 数据库  则需要修改 ？？？？？？
			}
			if ("number".equals(mapData.get("fieldDataType").toString())) {
				fieldTypes.append("NUMBER(").append(fieldLength).append(",").append(fieldDecimal).append(")"); // NUMBER(10,2)
			}
			if ("date".equals(mapData.get("fieldDataType").toString())) {
				fieldTypes.append("DATE");
			}
			// fieldTypes+=mapData.get("fieldType").toString();
			if (i < fieldList.size() - 1) {
				fieldCodes.append(";");
				fieldTypes.append(";");
			}
		}
		fieldCodes = new StringBuffer(CaseFormat.UPPER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, fieldCodes.toString()));//转成下划线
		//String [] fieldCode=fieldCodes.split(";");
		//if ("1".equals(tableType)) { // 1表示主表  添加默认固定字段
		//fieldCodes +=";status;created_date;created_by_id;created_by_name;version";
		// fieldCodes +=";status;version;created_date;created_by_id;created_by_name";
		// fieldTypes +=";INTEGER;INTEGER;DATE;VARCHAR2(100);VARCHAR2(100)";

		if (hasProcess != null && "1".equals(hasProcess)) {  //含有流程的添加固定审批过程的字段

			if (fieldCodes.indexOf("bill_code") < 0) {
				fieldCodes.append(";bill_code");
				fieldTypes.append(";VARCHAR2(32)");
			}
			if (fieldCodes.indexOf("fillin_date") < 0) {
				fieldCodes.append(";fillin_date");
				fieldTypes.append(";DATE");
			}
			if (fieldCodes.indexOf("organ_id") < 0) {
				fieldCodes.append(";organ_id");
				fieldTypes.append(";VARCHAR2(32)");
			}
			if (fieldCodes.indexOf("organ_name") < 0) {
				fieldCodes.append(";organ_name");
				fieldTypes.append(";VARCHAR2(64)");
			}
			if (fieldCodes.indexOf("dept_id") < 0) {
				fieldCodes.append(";dept_id");
				fieldTypes.append(";VARCHAR2(32)");
			}
			if (fieldCodes.indexOf("dept_name") < 0) {
				fieldCodes.append(";dept_name");
				fieldTypes.append(";VARCHAR2(64)");
			}
			if (fieldCodes.indexOf("position_id") < 0) {
				fieldCodes.append(";position_id");
				fieldTypes.append(";VARCHAR2(64)");
			}
			if (fieldCodes.indexOf("position_name") < 0) {
				fieldCodes.append(";position_name");
				fieldTypes.append(";VARCHAR2(64)");
			}
			if (fieldCodes.indexOf("person_member_id") < 0) {
				fieldCodes.append(";person_member_id");
				fieldTypes.append(";VARCHAR2(65)");
			}
			if (fieldCodes.indexOf("person_member_name") < 0) {
				fieldCodes.append(";person_member_name");
				fieldTypes.append(";VARCHAR2(64)");
			}
			if (fieldCodes.indexOf("full_id") < 0) {
				fieldCodes.append(";full_id");
				fieldTypes.append(";VARCHAR2(1024)");
			}
		}

		if (fieldCodes.indexOf("version") < 0) {
			fieldCodes.append(";version");
			fieldTypes.append(";INTEGER");
		}


		//} else if ("2".equals(tableType)) { //2表示明细表  添加默认固定字段
		//	fieldCodes = "id;" + fieldCodes + ";status;parent_id";
		//	fieldTypes = "VARCHAR2(32);" + fieldTypes + ";INTEGER;VARCHAR2(32)";
		//}
		backStr[0] = fieldCodes.toString();
		backStr[1] = fieldTypes.toString();
		return backStr;
	}

   //增加流程所需要的字段
	public String  addProcessFields(String tableName) {
		String backStr="",strSql="";
		Connection conn=null;
		PreparedStatement ps =null;
     try{
		 conn = this.sqlExecutorDao.getDataSource().getConnection();
	     StringBuffer sb=new StringBuffer("");
		sb.append("ALTER TABLE ").append(tableName).append(" add (bill_code VARCHAR2(32),")
				.append(" fillin_date Date,organ_id VARCHAR2(32),")
				.append(" organ_name VARCHAR2(64),dept_id  VARCHAR2(32),dept_name VARCHAR2(64),")
				.append(" position_id VARCHAR2(32),position_name VARCHAR2(64),person_member_id VARCHAR2(65),")
				.append(" person_member_name VARCHAR2(64),full_id VARCHAR2(1024))");
		ps = conn.prepareStatement(sb.toString());
		ps.execute();

    } catch (Exception e) {
	  backStr = e.getMessage();
	  e.printStackTrace();
   } finally {
	  try {
		conn.close();
	  } catch (Exception e) {
	 	backStr = e.getMessage();
		e.printStackTrace();
	  }
    }
		return backStr;
  }
	
}
