package com.product.org.admin.service;

import com.product.common.lang.StringUtils;
import com.product.core.service.support.QueryFilterService;

import com.product.module.sys.entity.SystemUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.exception.BaseException;
import com.product.core.permission.PermissionService;
import com.product.core.service.support.AbstractBaseService;
import com.product.core.spring.context.SpringMVCContextHolder;
import com.product.core.transfer.Transactional;
import com.product.org.admin.config.CmnConst;
import com.product.org.admin.config.SystemCode;
import com.product.org.admin.service.idel.IProcedureService;
import com.product.org.admin.util.DatabaseUtil;
import com.product.util.BaseUtil;

/**
 * Copyright © LX-BASE
 * 
 * @Title: ProcedureService
 * @Project: LX-BASE-SERVER
 * @Date: 2020-05-29 11:42:06
 * @Author: Xin.Luo
 * @Description: 存储过程管理Service层
 */
@Service
public class ProcedureService extends AbstractBaseService implements IProcedureService {
	@Autowired
	public BaseDao baseDao;

	@Override
	public BaseDao getBaseDao() {
		return baseDao;
	}

	@Override
	public void setBaseDao(BaseDao baseDao) {
		this.baseDao = baseDao;
	}

	@Autowired
	PermissionService permissionService;

	@Autowired
	QueryFilterService queryFilterService;
	
	@Autowired
	ProcedureService thisProcedureService;

	/**
	 * 	解析SQL
	 */
	@Override
	@Transactional
	public FieldSetEntity runProcedure(FieldSetEntity fse) throws BaseException {
		//获取SQL
		String procedureSql = fse.getString(CmnConst.PROCEDURE_SQL);
		String uuid = fse.getString("uuid");
//		String org_level_uuid = fse.getString("org_level_uuid");
		//转大写
		String procedureSqlBig = procedureSql.toUpperCase();
		//验证SQL是否包含存储过程关键字
		if (procedureSqlBig.indexOf("CREATE") == -1 || procedureSqlBig.indexOf("PROCEDURE") == -1 || procedureSqlBig.indexOf(CmnConst.BEGIN) == -1 || procedureSqlBig.indexOf(CmnConst.END) == -1) {
			throw new BaseException(SystemCode.SYSTEM_SQL_START_FAIL.getValue(), SystemCode.SYSTEM_SQL_START_FAIL.getText());
		}
		String headStr = procedureSql.substring(0, procedureSqlBig.indexOf("PROCEDURE") + 10);
		String str = procedureSql.substring(procedureSqlBig.indexOf("PROCEDURE"), procedureSqlBig.indexOf(CmnConst.BEGIN));
		procedureSql = procedureSql.substring(procedureSqlBig.indexOf(CmnConst.BEGIN), procedureSqlBig.length());
		// 提取表名
		String procedureName = str.substring(str.indexOf(' '), str.indexOf('('));
		String parameter = str.substring(str.indexOf('(') + 1);
		procedureName = procedureName.replace(" ", "");// 清除空格
		procedureName = procedureName.replace("`", "");// 清除`号
		FieldSetEntity fSet;
		if (StringUtils.isEmpty(uuid)) {
			fSet = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE, CmnConst.PROCEDURE_NAME + " = ? ", new String[] { procedureName }, false);
		} else {
			fSet = baseDao.getFieldSetEntityByFilter(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE, CmnConst.PROCEDURE_NAME + " = ? AND uuid != ?", new String[] { procedureName, uuid }, false);
		}
		if (fSet != null) {
			throw new BaseException(SystemCode.SYSTEM_PROCEDURE_REUSE_FAIL.getValue(), SystemCode.SYSTEM_PROCEDURE_REUSE_FAIL.getText());
		}
		// 如果选择公司那存储过程名称要变
		/*
		if (!StringUtils.isEmpty(org_level_uuid)) {
			FieldSetEntity fseOrg = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_ORG_LEVELS, org_level_uuid, false);
			if (fseOrg != null) {
				// 添加对应公司
				procedureName = procedureName + "_" + fseOrg.getInteger("org_level_id");
			}
		}
		*/
		procedureSql = headStr + procedureName + "(" + parameter + procedureSql;
		if (DatabaseUtil.runProcedure(procedureSql, procedureName)) {
			FieldSetEntity fSetEntity = new FieldSetEntity();
			fSetEntity.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE);
			fSetEntity.setValue(CmnConst.PROCEDURE_NAME, procedureName);
			fSetEntity.setValue(CmnConst.PROCEDURE_SQL, procedureSql);
			// 事务调用
			IProcedureService service = (IProcedureService) getProxyInstance(thisProcedureService);
			service.getParameter(parameter, fSetEntity);
			return fSetEntity;
		} else {
			throw new BaseException(SystemCode.SYSTEM_SQL_START_FAIL.getValue(), SystemCode.SYSTEM_SQL_START_FAIL.getText());
		}
	}

	/**
	 * 存储过程语句提取参数
	 * 
	 * @param parameter  参数字符串
	 * @param fSetEntity 存储过程Field
	 */
	@Override
	public void getParameter(String parameter, FieldSetEntity fSetEntity) {
		// 提取参数
		if (parameter.indexOf("in") != -1 || parameter.indexOf("out") != -1 || parameter.indexOf("IN") != -1 || parameter.indexOf("OUT") != -1) {
			String[] para = parameter.split(" ");
			DataTableEntity paraData = new DataTableEntity();
			for (int i = 0; i < para.length; ++i) {
				String p = para[i];
				String para_name;
				if ((i + 1) < para.length) {
					para_name = para[++i];
				} else {
					break;
				}
				FieldSetEntity field = new FieldSetEntity();
				field.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE);
				String para_desc = "";
				switch (p.toUpperCase()) {
				case "IN":
					para_desc = "输入参数";
					break;
				case "OUT":
					para_desc = "输出参数";
					break;
				case "INOUT":
					para_desc = "输入输出参数";
					break;
				default:
					break;
				}
				if (!StringUtils.isEmpty(para_desc)) {
					field.setValue("para_name", para_name);
					field.setValue("para_desc", para_desc);
					paraData.addFieldSetEntity(field);
				}
			}
			paraData.setMeta(paraData.getFieldSetEntity(0).getMeta());
			fSetEntity.addSubDataTable(paraData);
		}
	}

	@Override
	@Transactional
	public String saveProcedure(FieldSetEntity fieldSetEntity) throws BaseException {
		String uuid = fieldSetEntity.getString("uuid");
		if (uuid == null || uuid.isEmpty()) {
			return baseDao.add(fieldSetEntity);
		} else {
			baseDao.update(fieldSetEntity);
			DataTableEntity dataTable = fieldSetEntity.getSubDataTable(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE_SUB);
			// 判断是否有子表
			if (!BaseUtil.dataTableIsEmpty(dataTable)) {
				// 删除修改之前存储过程参数子表
				baseDao.delete(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE_SUB, CmnConst.PROCEDURE_UUID + " = ?", new String[] { uuid });
				baseDao.add(dataTable);
			}
			return uuid;
		}

	}

	/**
	 * 	存储过程保存
	 */
	@Override
	@Transactional
	public boolean saveProcedureSub(DataTableEntity dataTableEntity) throws BaseException {
		return baseDao.add(dataTableEntity);
	}

	/**
	 * 	存储过程删除
	 */
	@Override
	@Transactional
	public boolean deleteProcedure(FieldSetEntity fs) throws BaseException {
		String uuid = fs.getString(CmnConst.UUID);
		if (BaseUtil.strIsNull(uuid)) {
			SpringMVCContextHolder.getSystemLogger().error(SystemCode.SYSTEM_FORM_NODATA.getValue(), SystemCode.SYSTEM_FORM_NODATA.getText());
			throw new BaseException(SystemCode.SYSTEM_FORM_NODATA.getValue(), SystemCode.SYSTEM_FORM_NODATA.getText(), this.getClass(), "public boolean deleteProcedure(FieldSetEntity fs) throws BaseException");
		}
		FieldSetEntity fsn = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE, uuid, false);
		// 删除储存过程表
		baseDao.delete(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE, CmnConst.UUID + " = ?", new String[] { uuid });
		// 删除参数表
		baseDao.delete(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE_SUB, CmnConst.PROCEDURE_UUID + " = ?",
				new String[] { uuid });
		// 数据库删除存储过程
		DatabaseUtil.deleteProcedure(fsn.getString(CmnConst.PROCEDURE_NAME));

		return true;
	}

	/**
	 * 	存储过程详情
	 */
	@Override
	public FieldSetEntity getProcedureInfo(String uuid) throws BaseException {
		// 获取存储过程
		FieldSetEntity fieldSetEntity = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE, uuid, false);
		// 获取输入输出参数表
		DataTableEntity data = baseDao.listTable(CmnConst.PRODUCT_SYS_DATAMODEL_PROCEDURE_SUB, CmnConst.PROCEDURE_UUID + " = ?", new String[] { uuid });
		fieldSetEntity.addSubDataTable(data);
		return fieldSetEntity;
	}

	/**
	 * 	存储过程列表
	 */
	@Override
	public DataTableEntity getProcedureAll(FieldSetEntity fse) throws BaseException {
		SystemUser currentUser = SpringMVCContextHolder.getCurrentUser();
		String queryFilter = permissionService.getDataFilter(CmnConst.ORG_LEVEL_UUID);
		if (currentUser.getUserType() == 1) {
			queryFilter = CmnConst.CREATED_BY + " = " + SpringMVCContextHolder.getCurrentUser().getUser_id();
		}
		if (!BaseUtil.dataTableIsEmpty(fse.getSubDataTable("systemSeniorQueryString"))) {
			queryFilter = queryFilter + " AND " + queryFilterService.getQueryFilter(fse);
		}

		// 获取存储过程列表
		DataTableEntity data = baseDao.listTable(fse.getTableName(), queryFilter, null, null, null, fse.getInteger("pagesize"), fse.getInteger("cpage"));
		baseDao.loadPromptData(data);
		return data;
	}
}