Estsh-Yiri/Estsh.Core.Services/Repositories/TerminalDefineRepository.cs

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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：工位管理模块数据库访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
	public class TerminalDefineRepository : BaseRepository<SysTerminal>, ITerminalDefineRepository
    {
        public TerminalDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public List<SysTerminal> getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@TotalCount", 100);
                Params.Add("@TotalPage", 100);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", @" sys_Terminal a left join sys_stage b on a.stage_id=b.stage_id left join sys_process c on a.process_id=c.process_id 
                                      left join sys_pdline d on a.pdline_id = d.pdline_id left join sys_factory e on a.factory_id = e.factory_id  ");
                Params.Add("@Column", " terminal_id,terminal_name,pdline_name,stage_name,factory_name,process_name,a.enabled ");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

                List<SysTerminal> result = dbConn.Query<SysTerminal>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
                return result;

            }
        }

        /// <summary>
        /// 插入工站数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveTerminal(SysTerminal htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder(1024);
                strSql.Append("INSERT INTO dbo.sys_Terminal(terminal_name ,stage_id,process_id,pdline_id ,enabled, create_userid, create_time )VALUES ");
                strSql.Append("(@TerminalName,@stageId,@processId,@pdlineId,@enabled, @createUserid, CONVERT(varchar(50), GETDATE(), 21))");
                int result = dbConn.Execute(strSql.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 厂区名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getFactoryInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT factory_id as [value],factory_name as [key] from sys_factory where enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;

            }
        }
        /// <summary>
        /// 产线名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getPdlineInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select pdline_id as [value],pdline_name as [key] from sys_pdline where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 区段名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getStageInfoByKey()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select stage_id as [value],stage_name as [key] from sys_stage where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;

            }
        }

        /// <summary>
        /// 制程名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getProcessInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select process_id as [value],process_name as [key] from sys_process where enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;

            }
        }

        /// <summary>
        /// 更新工站数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateTerminal(SysTerminal htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder(1024);
                strSql.Append("UPDATE sys_Terminal SET  ");
                strSql.Append("terminal_name=@TerminalName,stage_id=@stageId,process_id=@processId,pdline_id=@pdlineId,enabled=@enabled ");
                strSql.Append("	,update_userid = @updateUserId ");
                strSql.Append("	,update_time = CONVERT(varchar(50), GETDATE(), 21)");
                strSql.Append("WHERE terminal_id=@TerminalId ");

                int result = dbConn.Execute(strSql.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 删除工站数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteTerminal(String Terminal_id )
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_Terminal set Enabled='N'  WHERE terminal_id = @Terminal_id";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@Terminal_id", Terminal_id);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }
        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_Terminal set Enabled='Y'  WHERE terminal_id in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update sys_Terminal set Enabled='N'  WHERE terminal_id in (" + ids + ")";
                int result = dbConn.Execute(delStr);
                return result;
            }
        }

        #endregion  成员方法

        #region 树状结构实现方式

        /// <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>
        /// <param name="factoryId"></param>
        /// <returns></returns>
        public List<KeyValueResult> getPdLine()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select pdline_id as [value],pdline_name as [key] from sys_pdline where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得工站信息
        /// </summary>
        /// <param name="pdLineId"></param>
        /// <returns></returns>
        public List<SysTerminal> getTerminalInfo(string pdLineId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT convert(VARCHAR, a.terminal_id) AS id ");
                SqlStringBuilder.Append(", a.terminal_name AS name ");
                SqlStringBuilder.Append(", 'P' + convert(VARCHAR, a.process_id) AS parentId ");
                SqlStringBuilder.Append(", 'true' AS noR ");
                SqlStringBuilder.Append("FROM sys_terminal a ");
                SqlStringBuilder.Append("WHERE a.pdline_id = @pdline_id ");
                SqlStringBuilder.Append("	AND a.enabled = 'Y' ");
                SqlStringBuilder.Append("UNION ");
                SqlStringBuilder.Append("SELECT DISTINCT 'S' + convert(VARCHAR, a.stage_id) AS id ");
                SqlStringBuilder.Append(" , a.stage_name AS name ");
                SqlStringBuilder.Append(" , '0' AS parentId ");
                SqlStringBuilder.Append(" , 'false' AS noR ");
                SqlStringBuilder.Append("FROM sys_stage a ");
                SqlStringBuilder.Append("	INNER JOIN sys_terminal b ON a.stage_id = b.stage_id ");
                SqlStringBuilder.Append("WHERE b.pdline_id = @pdline_id1 ");
                SqlStringBuilder.Append("	AND b.enabled = 'Y' ");
                SqlStringBuilder.Append("UNION ");
                SqlStringBuilder.Append("SELECT DISTINCT 'P' + convert(VARCHAR, a.process_id) AS id ");
                SqlStringBuilder.Append(" , a.process_name name ");
                SqlStringBuilder.Append(" , 'S' + convert(VARCHAR, a.stage_id) AS parentId ");
                SqlStringBuilder.Append(" , 'false' AS noR ");
                SqlStringBuilder.Append("FROM sys_process a ");
                SqlStringBuilder.Append("	INNER JOIN sys_terminal b ON a.process_id = b.process_id ");
                SqlStringBuilder.Append("WHERE b.pdline_id = @pdline_id2 ");
                SqlStringBuilder.Append("	AND b.enabled = 'Y' ");
                DynamicParameters htParams = new DynamicParameters();
                htParams.Add("@pdline_id", pdLineId);
                htParams.Add("@pdline_id1", pdLineId);
                htParams.Add("@pdline_id2", pdLineId);

                List<SysTerminal> result = dbConn.Query<SysTerminal>(SqlStringBuilder.ToString(), htParams).ToList();
                return result;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="processName"></param>
        /// <returns></returns>
        public List<SysTerminal> GetTerminalByTerminalId(string terminalId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select * from sys_terminal where terminal_id =" + terminalId;
                List<SysTerminal> result = dbConn.Query<SysTerminal>(SqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得默认工站名称
        /// </summary>
        /// <param name="processName"></param>
        /// <returns></returns>
        public string GetDefaultTerminalName(string processName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select max(terminal_name) from sys_terminal where terminal_name like '" + processName + "%'";
                object result = dbConn.ExecuteScalar(SqlString);
                if (result == null)
                    return string.Format("{0}01", processName);

                int  id = Convert.ToInt16(result.ToString().Replace(processName, string.Empty).Replace("-", string.Empty));
               
                return string.Format("{0}{1:D2}", processName, id + 1);
            }
        }
        /// <summary>
        /// 获取汇总信息
        /// </summary>
        /// <param name="aWhere">查询条件</param>
        /// <returns></returns>
        public List<SysTerminalControl> GetSumMessage(string aWhere, ref Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 100, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 100, DbType.Int32, ParameterDirection.Output);
                StringBuilder table = new StringBuilder();
                table.Append(" dbo.sys_terminal_control a ");
                table.Append(" LEFT JOIN sys_part b ON a.part_id = b.part_id and a.enabled = 'Y' ");
                table.Append(" LEFT JOIN sys_model c ON b.model_id = c.model_id ");
                parameters.Add("@Table", table.ToString());

                StringBuilder column = new StringBuilder();
                column.Append(" a.car_no,");
                column.Append(" c.model_name ,");
                column.Append(" d.type_name ,");
                column.Append(" h.tray_no , h.tray_loc,");
                //column.Append(" CONVERT(VARCHAR(16),j.cur_time,120) as cust_time ,");
                column.Append(" j.cur_time,");
                column.Append(" j.cust_pdline , a.out_pdline_ymd+'  '+a.out_pdline_hms as out_time,");
                column.Append(" a.shipping_ymd+' '+a.shipping_hms as shipping_time ,");
                column.Append(" j.order_seq ,");
                column.Append(" a.create_time as create_time  ");
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", "a.create_time");
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", aWhere);

                List<SysTerminalControl> result = dbConn.Query<SysTerminalControl>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                pager.totalRows = parameters.Get<int>("@TotalCount");
                return result;
            }
        }
        #endregion
    }
}