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

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

namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class PullGroupMESRepository : BaseRepository<WmsPullingGroupMes>, IPullGroupMESRepository
    {
        public PullGroupMESRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法
        /// <summary>
        /// 获得菜单列表数据
        /// </summary>
        public List<WmsPullingGroup> getList(string ruid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from wms_pulling_group_mes (nolock) ");
                if (!ruid.Trim().Equals(""))
                {
                    strSql.Append(" where ruid=" + ruid);
                }

                List<WmsPullingGroup> result = dbConn.Query<WmsPullingGroup>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getPullGroupListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select * from wms_pulling_group_mes     " + strWhere);

                List<WmsPullingGroupMes> dataList = dbConn.Query<WmsPullingGroupMes>(SqlStringBuilder.ToString()).ToList();

                result.Add("dataList", dataList);
                result.Add("totalCount", dataList.Count());
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getPullGroupDetailListByPage(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.* from wms_pulling_group_part a (nolock)   ");
                SqlStringBuilder.Append("where " + strWhere);

                List<WmsPullingGroupPart> dataList = dbConn.Query<WmsPullingGroupPart>(SqlStringBuilder.ToString()).ToList();
                result.Add("dataList", dataList);
                result.Add("totalCount", dataList.Count());
                return result;
            }
        }
        public List<WmsPullingGroupPart> getPullGroupDetailList(string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select a.* from wms_pulling_group_part a (nolock)  ");
                SqlStringBuilder.Append("where " + strWhere);
                List<WmsPullingGroupPart> dataList = dbConn.Query<WmsPullingGroupPart>(SqlStringBuilder.ToString()).ToList();
                return dataList;
            }
        }

        public List<SysPart> GetPartInfo(string part_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y'  AND part_no LIKE '" + part_no + "%' ORDER BY part_no";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetErpwarehouse()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "select distinct erp_warehouse as [key] , erp_warehouse as value from sys_zone where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取下拉框菜单数据   这里显示的是待添加的厂区信息，厂区名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getSelectFactory()
        {
            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;
            }
        }

        public List<KeyValueResult> getSelectWarehouse()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select warehouse_id as [value],warehouse_desc as [key] from sys_warehouse where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<SysWarehouse> getSelectWarehouse(string warehouseid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_warehouse where Enabled = 'Y' and warehouse_id='" + warehouseid + "'");
                List<SysWarehouse> result = dbConn.Query<SysWarehouse>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<SysZone> getSelectZone(string zoneid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_zone where Enabled = 'Y' and zone_id='" + zoneid + "'");
                List<SysZone> result = dbConn.Query<SysZone>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<KeyValueResult> getSelectZone()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select zone_id as [value],zone_name as [key] from sys_zone where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<KeyValueResult> getSelectVendor()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select vendor_id as [value],vendor_name as [key] from sys_Vendor where Enabled = 'Y'");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<SysVendor> getSelectVendor(string vendorId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_Vendor where Enabled = 'Y' and vendor_id='" + vendorId + "'");
                List<SysVendor> result = dbConn.Query<SysVendor>(strSql.ToString()).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetPart(int type)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id as [value],part_no as [key] FROM sys_part (NOLOCK) WHERE enabled='Y'  ORDER BY part_no";
                DynamicParameters param = new DynamicParameters();
                param.Add("@part_type", type);
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql, param).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetOrderType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum  where enum_type='wms_PullGroup_order_type' and enabled='Y'";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }
        public List<SysPart> GetPart(int type, string PartNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM sys_part (NOLOCK) WHERE enabled='Y'  and part_no='" + PartNo + "' ORDER BY part_no";
                DynamicParameters param = new DynamicParameters();
                param.Add("@part_type", type);
                List<SysPart> result = dbConn.Query<SysPart>(sql, param).ToList();
                return result;
            }
        }

        public List<SysPdline> GetPdlineByPdlineId(int pdlineId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM sys_pdline (NOLOCK) WHERE enabled='Y' and pdline_id='" + pdlineId + "'";
                List<SysPdline> result = dbConn.Query<SysPdline>(sql).ToList();
                return result;
            }
        }
        public SysPdline GetPdlineByPdlineCode(string pdlineCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM sys_pdline (NOLOCK) WHERE pdline_code='" + pdlineCode + "'";
                SysPdline result = dbConn.Query<SysPdline>(sql).FirstOrDefault();
                return result;
            }
        }
        public List<KeyValueResult> GetPdlineByKey()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT distinct pdline_code as [value],pdline_code as [key] FROM sys_pdline (NOLOCK) WHERE enabled='Y'  ORDER BY pdline_code";
                DynamicParameters param = new DynamicParameters();
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql, param).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取订单编号
        /// </summary>
        /// <returns></returns>
        public string GetOrderNo(string stockOrder, string p)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", stockOrder);
                list.Add("@order_prefix", p);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

                var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }

        public bool saveHandPullGroup(string pdline_id, string PullValue)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();

                #region

                StringBuilder stringBuilderSQL = new StringBuilder(1024);
                stringBuilderSQL.Append("insert into WMS_MES_PULL_ORDER(pdline_id,src_move_qty,syn_fig,create_time) values(@pdline_id,@src_move_qty,@syn_fig,@create_time) ");
                sqlList.Add(stringBuilderSQL.ToString());

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pdline_id", pdline_id);//产线  100001
                parameters.Add("@src_move_qty", PullValue);// 需求数量
                parameters.Add("@syn_fig", "0");// WMS生成为 0
                parameters.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));// 创建时间
                parametersList.Add(parameters);


                return InsertData(sqlList, parametersList);
                #endregion
            }
        }

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int savePullGroup(WmsPullingGroupMes htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<string> SqlStrings = new List<string>();
                List<object> Parameters = new List<object>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_pulling_group_mes ");
                SqlStringBuilder.Append("           (pdline_code,pdline_name,cumulative_value,trigger_value,pull_value,max_pull_value,enabled) ");
                SqlStringBuilder.Append("     VALUES(@pdline_code,@pdline_name,@cumulative_value,@trigger_value,@pull_value,@max_pull_value,@enabled) ");

                SqlStrings.Add(SqlStringBuilder.ToString());
                Parameters.Add(htParams);


                IDbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    for (int i = 0; i < SqlStrings.Count; i++)
                    {
                        dbConn.Execute(SqlStrings[i], Parameters[i], transaction);
                    }
                    transaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return 0;
                }
            }
        }

        public int updatePullGroup(WmsPullingGroupMes htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {

                dbConn.Open();
                List<string> SqlStrings = new List<string>();
                List<object> Parameters = new List<object>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE dbo.wms_pulling_group_mes ");
                SqlStringBuilder.Append("   SET pdline_name = @pdline_name ");
                SqlStringBuilder.Append("      ,cumulative_value = @cumulative_value,trigger_value = @trigger_value ");
                SqlStringBuilder.Append("      ,pull_value = @pull_value,max_pull_value = @max_pull_value ");
                SqlStringBuilder.Append("      ,enabled = @enabled,update_userid = @update_userid ");
                SqlStringBuilder.Append("      ,update_time = @update_time ");
                SqlStringBuilder.Append(" WHERE pdline_code=@pdline_code ");

                SqlStrings.Add(SqlStringBuilder.ToString());
                Parameters.Add(htParams);

                IDbTransaction transaction = dbConn.BeginTransaction();
                try
                {
                    for (int i = 0; i < SqlStrings.Count; i++)
                    {
                        dbConn.Execute(SqlStrings[i], Parameters[i], transaction);
                    }
                    transaction.Commit();
                    return 1;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return 0;
                }
            }
        }

        /// <summary>
        /// 获得零件信息
        /// </summary>
        /// <returns></returns>
        public List<SysPart> GetPartNoInfo(string part_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_no LIKE '%" + part_no + "%' ORDER BY part_no";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获得零件简码信息
        /// </summary>
        /// <returns></returns>
        public List<SysPart> GetPartSpecInfo(string partSpec)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_spec LIKE '%" + partSpec + "%' ORDER BY part_no";
                List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
                return result;
            }
        }

        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String detailStr = "update wms_pulling_group_mes set Enabled='Y'  WHERE pdline_code in (" + ids + ")";
                int datailResult = dbConn.Execute(detailStr);
                return datailResult;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String detailStr = "update wms_pulling_group_mes set Enabled='N'  WHERE pdline_code in (" + ids + ")";
                int datailResult = dbConn.Execute(detailStr);
                return datailResult;
            }
        }
        public PullGroup ifExistsPullGroup(string groupCode)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM wms_pulling_group (NOLOCK) WHERE group_code = '" + groupCode + "'";
                PullGroup result = dbConn.Query<PullGroup>(sql).FirstOrDefault();
                return result;
            }
        }

        public PullGroup ifExistsPullGroupPart(string groupCode, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE group_code = '" + groupCode + "' and part_no='" + partNo + "'";
                PullGroup result = dbConn.Query<PullGroup>(sql).FirstOrDefault();
                return result;
            }
        }

        public List<PullGroup> getExportList(string strWhere, string orderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = @"select b.group_code,b.group_name,case when b.pulling_type=10 then 'JIT蓝卡队列' when b.pulling_type=20 then 'JIT指示票队列' end pulling_type
                              ,b.pdline_code,b.kitting_qty,b.triggerValue,b.pullValue,a.part_id,part_no,part_spec,a.pull_qty 
                               from wms_pulling_group_part a (nolock) left join wms_pulling_group b (nolock) on a.group_code=b.group_code 
                              where " + strWhere + orderBy + " ";
                List<PullGroup> result = dbConn.Query<PullGroup>(sql).ToList();
                return result;
            }
        }

        //事务批量执行添加、修改
        public bool InsertData(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return true;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }




        #endregion  成员方法
    }
}