using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：流程管理模块数据库访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理流程管理模块的业务数据
    /// </summary>
    public class RouteDefineRepository : BaseRepository<SysRoute>, IRouteDefineRepository
    {
        public RouteDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 获取流程信息
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getRoute(String factoryId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select route_id as [value], route_name as [key],route_desc from sys_route where enabled='Y' and factory_id=@factoryId");
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@factoryId", factoryId);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString(), Params).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取区段信息
        /// </summary>
        /// <returns></returns>
        public List<SysStage> getStageInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.stage_id ");
                SqlStringBuilder.Append("	 , a.stage_name ");
                SqlStringBuilder.Append("	 , a.stage_code ");
                SqlStringBuilder.Append("	 , a.stage_desc ");
                SqlStringBuilder.Append("FROM sys_stage a ");
                SqlStringBuilder.Append("WHERE a.enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY a.stage_code ");

                List<SysStage> result = dbConn.Query<SysStage>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取制程信息
        /// </summary>
        /// <returns></returns>
        public List<SysProcess> getProcessInfo(string factoryId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.process_id ");
                SqlStringBuilder.Append("	 , a.factory_id ");
                SqlStringBuilder.Append("	 , a.stage_id ");
                SqlStringBuilder.Append("	 , a.process_code ");
                SqlStringBuilder.Append("	 , a.process_name ");
                SqlStringBuilder.Append("	 , a.process_desc ");
                SqlStringBuilder.Append("FROM sys_process a ");
                SqlStringBuilder.Append("WHERE a.enabled = 'Y' ");
                SqlStringBuilder.Append("	AND a.factory_id = @factoryId ");
                SqlStringBuilder.Append("ORDER BY a.process_code ");
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@factoryId", factoryId);
                List<SysProcess> result = dbConn.Query<SysProcess>(SqlStringBuilder.ToString(), Params).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取流程信息
        /// </summary>
        /// <returns></returns>
        public List<SysRoute> getRouteById(String routeId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select top 1 * from sys_route where enabled='Y' and route_id='" + routeId + "'");
                List<SysRoute> result = dbConn.Query<SysRoute>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得流程明细数据
        /// </summary>
        /// <param name="factoryId"></param>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public List<SysRouteDetail> getRouteDetail(string factoryId, string routeId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.process_id ");
                SqlStringBuilder.Append("	 , b.process_name ");
                SqlStringBuilder.Append("	 , a.next_process_id ");
                SqlStringBuilder.Append("	 , c.process_name AS next_process_name ");
                SqlStringBuilder.Append("	 , a.result ");
                SqlStringBuilder.Append("	 , a.seq ");
                SqlStringBuilder.Append("	 , a.pd_code ");
                SqlStringBuilder.Append("	 , a.necessary ");
                SqlStringBuilder.Append("	 , a.step ");
                SqlStringBuilder.Append("FROM sys_route_detail a ");
                SqlStringBuilder.Append("	LEFT JOIN sys_process b ON a.process_id = b.process_id, sys_process c, sys_route d ");
                SqlStringBuilder.Append("WHERE a.Enabled='Y' and a.next_process_id = c.process_id ");
                SqlStringBuilder.Append("	AND a.route_id = d.route_id ");
                SqlStringBuilder.Append("	AND d.factory_id = @factoryId ");
                SqlStringBuilder.Append("	AND a.route_id = @routeId ");
                SqlStringBuilder.Append("ORDER BY seq ");
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@factoryId", factoryId);
                Params.Add("@routeId", routeId);
                List<SysRouteDetail> result = dbConn.Query<SysRouteDetail>(SqlStringBuilder.ToString(), Params).ToList();
                return result;
            }
        }

        /// <summary>
        /// 插入流程明细数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public bool saveRouteDetail(List<Hashtable> htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_route_detail ");
                SqlStringBuilder.Append("        (route_id ");
                SqlStringBuilder.Append("        ,process_id ");
                SqlStringBuilder.Append("        ,next_process_id ");
                SqlStringBuilder.Append("        ,result ");
                SqlStringBuilder.Append("        ,seq ");
                SqlStringBuilder.Append("        ,necessary ");
                SqlStringBuilder.Append("        ,step ");
                SqlStringBuilder.Append("        ,enabled ");
                SqlStringBuilder.Append("        ,create_userid ");
                SqlStringBuilder.Append("        ,create_time ");
                SqlStringBuilder.Append("        ) ");
                SqlStringBuilder.Append("  VALUES ");
                SqlStringBuilder.Append("        (@routeId ");
                SqlStringBuilder.Append("        ,@processId ");
                SqlStringBuilder.Append("        ,@nextProcessId ");
                SqlStringBuilder.Append("        ,@result ");
                SqlStringBuilder.Append("        ,@seq ");
                SqlStringBuilder.Append("        ,'Y' ");
                SqlStringBuilder.Append("        ,@necessary ");
                SqlStringBuilder.Append("        ,@step ");
                SqlStringBuilder.Append("        ,@createUserid ");
                SqlStringBuilder.Append("        ,CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("        ) ");

                List<DynamicParameters> listData = new List<DynamicParameters>();
                for (int i = 0; i < htParams.Count; i++)
                {
                    DynamicParameters dataPar = new DynamicParameters();
                    for (int j = 0; j < htParams[i].Count; j++)
                    {
                        dataPar.Add(htParams[i].Keys.ToString(), htParams[i].Values);
                    }
                    listData.Add(dataPar);
                }

                IDbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    for (int i = 0; i < listData.Count; i++)
                    {
                        dbConn.Execute(SqlStringBuilder.ToString(), listData[i], transaction);
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }

                return true;
            }
        }

        /// <summary>
        /// 删除流程明细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deleteRouteDetail(int id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "update sys_route_detail set Enabled='N'  where route_id=@route_id";

                DynamicParameters Params = new DynamicParameters();
                Params.Add("@route_id", id);
                int result = dbConn.Execute(SqlString, Params);
                return result > 0 ? true : false;
            }
        }

        /// <summary>
        /// 插入流程信息
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveRouteDefine(SysRoute modelparams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_route ");
                SqlStringBuilder.Append("           (factory_id ");
                SqlStringBuilder.Append("           ,route_name ");
                SqlStringBuilder.Append("           ,route_desc ");
                SqlStringBuilder.Append("           ,enabled ");
                SqlStringBuilder.Append("           ,create_userid ");
                SqlStringBuilder.Append("           ,create_time ");
                SqlStringBuilder.Append("           ) ");
                SqlStringBuilder.Append("     VALUES ");
                SqlStringBuilder.Append("           (@factoryId ");
                SqlStringBuilder.Append("           ,@routeName ");
                SqlStringBuilder.Append("           ,@routeDesc ");
                SqlStringBuilder.Append("           ,'Y' ");
                SqlStringBuilder.Append("           ,@createUserid ");
                SqlStringBuilder.Append("           ,CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("          ) ");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), modelparams);
                return result;
            }
        }

        /// <summary>
        /// 更新流程信息
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateRouteDefine(SysRoute modelparams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE sys_route ");
                SqlStringBuilder.Append("   SET factory_id = @factoryId ");
                SqlStringBuilder.Append("      ,route_name = @routeName ");
                SqlStringBuilder.Append("      ,route_desc = @routeDesc ");
                SqlStringBuilder.Append("      ,update_userid = @updateUserid ");
                SqlStringBuilder.Append("      ,update_time = CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append(" WHERE route_id = @routeId ");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), modelparams);
                return result;
            }
        }

        /// <summary>
        /// 删除流程信息
        /// </summary>
        /// <param name="routeId"></param>
        /// <returns></returns>
        public int deleteRouteDefine(string routeId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" update sys_route_detail set Enabled='N'  where route_id=@route_id  ");
                SqlStringBuilder.Append(" update sys_route set Enabled='N'  where route_id=@route_id1  ");
                DynamicParameters Params = new DynamicParameters(2);
                Params.Add("@route_id", routeId);
                Params.Add("@route_id1", routeId);
                int result = dbConn.Execute(SqlStringBuilder.ToString(), Params);
                return result;
            }
        }
        //启用
        public int EnableData(String routeId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" update sys_route_detail set Enabled='Y'  where route_id=@route_id  ");
                SqlStringBuilder.Append(" update sys_route set Enabled='Y'  where route_id=@route_id1  ");
                DynamicParameters Params = new DynamicParameters(2);
                Params.Add("@route_id", routeId);
                Params.Add("@route_id1", routeId);
                int result = dbConn.Execute(SqlStringBuilder.ToString(), Params);
                return result;
            }
        }

        //禁用
        public int DisableData(String routeId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" update sys_route_detail set Enabled='N'  where route_id=@route_id  ");
                SqlStringBuilder.Append(" update sys_route set Enabled='N'  where route_id=@route_id1  ");
                DynamicParameters Params = new DynamicParameters(2);
                Params.Add("@route_id", routeId);
                Params.Add("@route_id1", routeId);
                int result = dbConn.Execute(SqlStringBuilder.ToString(), Params);
                return result;
            }
        }
        #endregion  成员方法

    }
}