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

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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：采购负收货模块DAL层
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class PurchaseSubtractiveRepository : BaseRepository<SysStock>, IPurchaseSubtractiveRepository
    {

        public PurchaseSubtractiveRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        { }
        /// <summary>
        /// 获取服务器日期、时间
        /// </summary>
        /// <param name="flag">格式标志</param>
        /// <returns></returns>
        public string GetServerDateTime(string flag)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string serverDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss").Replace("-", "/");

                switch (flag.ToUpper().Replace(" ", ""))
                {
                    case "YMD":
                        return serverDateTime.Substring(0, 10);
                    case "HMS":
                        return serverDateTime.Substring(11);
                    case "YY":
                        return serverDateTime.Substring(2, 2);
                    case "YYYY":
                        return serverDateTime.Substring(0, 4);
                    case "HH":
                        return serverDateTime.Substring(11, 2);
                    default:
                        return serverDateTime;
                }
            }
        }

        public List<GPoCancelMaster> getQueryByPage(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", "g_po_cancel_master");
                Params.Add("@Column", "*");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

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


        /// <summary>
        /// 获取日程单号
        /// </summary>
        /// <param name="item">字段名称</param>
        /// <param name="prefix">前缀</param>
        /// <returns></returns>
        public string GetOrderNo(string item, string prefix)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", item);
                list.Add("@order_prefix", prefix);
                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;
            }
        }

        /// <summary>
        /// 获取厂区内容
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetFactoryInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT factory_id as [value],factory_name as [key] FROM sys_factory (NOLOCK) WHERE enabled='Y' ORDER BY factory_name";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取厂区代码
        /// </summary>
        /// <returns></returns>
        public string GetFactoryId(int userID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 factory_id FROM sys_emp (NOLOCK) WHERE emp_id=@userId AND enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@userId", userID.ToString());
                object obj = dbConn.ExecuteScalar(sql, param);
                if (obj == null)
                {
                    return "";
                }

                return obj.ToString();
            }
        }

        /// <summary>
        /// 获取项目、车型内容
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetModelType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT type_id as [value],type_name as [key] FROM sys_model_type (NOLOCK) WHERE enabled='Y' ORDER BY type_name";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取供应商信息
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetVendorInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT vendor_id as [value],vendor_name as [key] FROM sys_vendor (NOLOCK) WHERE enabled='Y' ORDER BY vendor_name";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取采购单编号
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetBuyNoInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT DISTINCT UPPER(xw_po_nbr) AS [value], xw_po_nbr AS [key] FROM xw_po_mstr (NOLOCK) ORDER BY xw_po_nbr ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取零件信息
        /// </summary>
        /// <param name="type">零件类型</param>
        /// <returns></returns>
        public List<SysPart> GetPartInfo(int type)
        {
            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'  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<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;
            }
        }
        /// <summary>
        /// 是否为采购单内零件
        /// </summary>
        /// <param name="buyNo">采购单</param>
        /// <param name="partNo">零件号</param>
        /// <returns></returns>
        public bool IsPoPartNo(string buyNo, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 xw_pod_part FROM xw_pod_det (NOLOCK) WHERE UPPER(xw_pod_nbr)=@xw_pod_nbr AND UPPER(xw_pod_part)=@xw_pod_part";
                DynamicParameters param = new DynamicParameters();
                param.Add("@xw_pod_nbr", buyNo.ToUpper().Trim());
                param.Add("@xw_pod_part", partNo.ToUpper().Trim());

                object obj = dbConn.ExecuteScalar(sql, param);

                if (obj == null)
                {
                    return false;
                }

                return true;
            }
        }

        public string DataSaveTransact(DataTable dtData, int userID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (dtData.Rows.Count < 1)
                {
                    return "无条码零件采购收货数据为空";
                }

                int userId = userID;
                DataTable dtReturn = new DataTable();
                StringBuilder sql = new StringBuilder(1024);
                IDbTransaction transaction = dbConn.BeginTransaction();

                try
                {
                    List<string> sqlStrings = new List<string>();
                    List<DynamicParameters> parameters = new List<DynamicParameters>();
                    DynamicParameters param = new DynamicParameters();

                    foreach (DataRow dr in dtData.Rows)
                    {
                        string orderNo = dr["order_no"].ToString();
                        string shipNo = dr["ship_no"].ToString();
                        string buyNo = dr["buy_no"].ToString();
                        string partNo = dr["part_no"].ToString();
                        string qty = dr["qty"].ToString();

                        sql.Remove(0, sql.Length);
                        sql.Append("INSERT INTO g_po_cancel_master (order_no,rt_no,po_no,part_no,qty,type,sync_no,enabled,create_userid,guid, create_time) ");
                        sql.Append(" values (@order_no,@rt_no,@po_no,@partNo,@qty,'RCT-PO','','Y',@user_id,NEWID(), CONVERT(varchar(50), GETDATE(), 21)) ");
                        param = new DynamicParameters();
                        param.Add("@order_no", orderNo);
                        param.Add("@rt_no", shipNo);
                        param.Add("@po_no", buyNo);
                        param.Add("@partNo", partNo);
                        param.Add("@qty", qty);
                        param.Add("@user_id", userId.ToString());

                        sqlStrings.Add(sql.ToString());
                        parameters.Add(param);
                    }
                    for (int i = 0; i < sqlStrings.Count; i++)
                    {
                        dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                    }
                    transaction.Commit();

                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "数据提交错误!\r\n" + ex.Message;
                }

                return "";
            }
        }

        /// <summary>
        /// 箱条码生成(零件号(18)+批号(6)+流水号(3))
        /// </summary>
        /// <param name="partNo">零件号</param>
        /// <param name="lotNo">批号</param>
        /// <param name="seq">流水号</param>
        /// <returns></returns>
        public string GetCartonNo(string partNo, string lotNo, int seq)
        {
                string strCartonNo = partNo + "000000000000000000";
                strCartonNo = strCartonNo.Substring(0, 18) + lotNo;
                string strSeq = "000" + seq.ToString();
                return strCartonNo + strSeq.Substring(strSeq.Length - 3);
        }

        /// <summary>
        /// 验证零件是否存在线边库位
        /// </summary>
        /// <param name="partNo">零件号所在行ID</param>
        /// <returns></returns>
        public bool IsExistLineLocate(string partId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "SELECT TOP 1 locate_name FROM sys_locate (NOLOCK) WHERE locate_type='L' AND part_id=@part_id AND enabled='Y'";
                DynamicParameters param = new DynamicParameters();
                param.Add("@part_id", partId);

                object obj = dbConn.ExecuteScalar(sql, param);
                if (obj == null)
                {
                    return false;
                }

                return true;
            }
        }

        /// <summary>
        /// 验证线边库位是否存在相应零件库存
        /// </summary>
        /// <param name="partId">零件号所在行ID</param>
        /// <param name="lotNo">批号</param>
        /// <returns></returns>
        public bool IsExistPartLotNo(string partId, string lotNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                try
                {
                    string sql = "SELECT TOP 1 part_id FROM sys_stock (NOLOCK) WHERE part_id=@part_id AND lot_no=@lot_no AND status='G' and enabled='Y'";
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@part_id", partId);
                    param.Add("@lot_no", lotNo);

                    object obj = dbConn.ExecuteScalar(sql, param);
                    if (obj == null)
                    {
                        return false;
                    }

                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 验证送货单号与采购单号是否匹配
        /// </summary>
        /// <param name="shipNo">送货单号</param>
        /// <param name="buyNo">采购单号</param>
        /// <returns></returns>
        public bool IsExistShipAndBuyNo(string shipNo, string buyNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                try
                {
                    string sql = "SELECT COUNT(1) FROM xw_tr_hist WHERE xw_tr_wo_id=@ship_no AND xw_tr_nbr=@buy_no AND xw_tr_type='RCT-PO' AND xw_tr_data_sync='Yes'";
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@ship_no", shipNo);
                    param.Add("@buy_no", buyNo);

                    object obj = dbConn.ExecuteScalar(sql, param);
                    if (obj == null)
                    {
                        return false;
                    }

                    int iRows = Convert.ToInt32(obj.ToString());
                    if (iRows < 1)
                    {
                        return false;
                    }

                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 验证送货单零件是否一致
        /// </summary>
        /// <param name="shipNo">送货单号</param>
        /// <param name="buyNo">采购单号</param>
        /// <param name="partNo">零件号</param>
        /// <param name="qty">数量</param>
        /// <returns></returns>
        public List<dynamic> ValidateShipAndPart(string shipNo, string buyNo, string partNo, int qty, int userId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                try
                {
                    DynamicParameters param = new DynamicParameters();
                    param.Add("@ship_no", shipNo);
                    param.Add("@buy_no", buyNo);
                    param.Add("@partNo", partNo);
                    param.Add("@qty", qty);
                    param.Add("@user_id", userId);

                    List<dynamic> result = dbConn.Query<dynamic>("validate_ship_part", param, commandType: CommandType.StoredProcedure).ToList();
                    return result;
                }
                catch
                {
                    return null;
                }
            }
        }
    }
}