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

using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.IRepositories;
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 PurchasePrintRepository : BaseRepository<SysStock>, IPurchasePrintRepository
    {
        public PurchasePrintRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public List<SysStock> 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", "dbo.sys_stock");
                Params.Add("@Column", "*");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", OrderBy);
                Params.Add("@Group", 0);

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


        /// <summary>
        /// 获取采购单信息，用于下拉列表框填充数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getBuyNo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT DISTINCT UPPER(xw_po_nbr) as [value],UPPER(xw_po_nbr) as [key] FROM xw_po_mstr ORDER BY [value]");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取单据编号
        /// </summary>
        /// <param name="orderType">单据类型，dbo.sys_sn_seed 表中的 sn_name 字段</param>
        /// <param name="orderPrefix">单据前缀</param>
        /// <returns>单据编号</returns>
        public String GetOrderNo(string orderType, string orderPrefix)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", orderType);
                list.Add("@order_prefix", orderPrefix);
                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<dynamic> getPartNo(String buyNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT  p.part_id as [value],p.part_no +' | '+ p.part_spec as [key],p.part_no,p.default_box_qty ");
                strSql.Append(" FROM xw_pod_det det join sys_part p on det.xw_pod_part = p.part_no AND p.part_type = 0 ");
                strSql.Append(" WHERE UPPER(xw_pod_nbr)=@xw_pod_nbr ");
                DynamicParameters ht = new DynamicParameters();
                ht.Add("@xw_pod_nbr", buyNo);
                List<dynamic> result = dbConn.Query<dynamic>(strSql.ToString(), ht).ToList();
                return result;
            }
        }

        public bool saveBuyOrder(Hashtable htData)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                int loginUserFactory = Convert.ToInt32(htData["loginFactory"]);
                string orderNo = Convert.ToString(htData["orderNo"]);
                int vendorId = Convert.ToInt32(htData["vendor"].ToString());
                string seDate = Convert.ToString(htData["dtpSeDate"]);
                string shipNo = Convert.ToString(htData["shipNo"]);
                string typeId = Convert.ToString(htData["modelType"]);
                string factory = Convert.ToString(htData["factory"]);
                string buyNo = Convert.ToString(htData["buyNo"]);
                string partId = Convert.ToString(htData["partId"]);
                int qty = Convert.ToInt32(htData["qty"].ToString());
                int snpQty = Convert.ToInt32(htData["snpQty"].ToString());
                int loginUserId = Convert.ToInt32(htData["loginUserId"].ToString());

                int cartonQty = (int)Math.Ceiling(Convert.ToDouble(qty) / snpQty);
                string lotNo = seDate.Replace("-", "").Substring(2);

                string sqlPart = "select top 1 part_no from sys_part where part_id = @part_id";
                DynamicParameters htPart = new DynamicParameters();
                htPart.Add("@part_id", partId);

                string partNo = Convert.ToString(dbConn.ExecuteScalar(sqlPart, htPart));

                StringBuilder sql = new StringBuilder(1024);
                List<string> sqlStrings = new List<string>();
                List<DynamicParameters> parameters = new List<DynamicParameters>();
                DynamicParameters param = new DynamicParameters();

                //删除订单
                deleteBuyOrder(htData);

                sql.Remove(0, sql.Length);
                sql.Append("INSERT INTO g_buy_day_fact (order_no,weekly_order_no,type_id,buy_no,vendor_id,se_date,part_id,lot_no ");
                sql.Append(" ,qty,snp_qty,carton_qty,rec_qty,rec_carton_qty,diff_qty,status,is_closed,type,factory_id,enabled,create_userid, create_time) ");
                sql.Append(" VALUES (@order_no,@order_no,@type_id,@buy_no,@vendor_id,@se_date,@part_id,@lot_no ");
                sql.Append(" ,@qty,@snpQty,@carton_qty,0,0,@qty,@status,@is_closed,@type,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");

                param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@type_id", typeId);
                param.Add("@buy_no", buyNo);
                param.Add("@vendor_id", vendorId);
                param.Add("@se_date", seDate.Replace("-", "/"));
                param.Add("@part_id", partId);
                param.Add("@lot_no", lotNo);
                param.Add("@qty", qty);
                param.Add("@snpQty", snpQty);
                param.Add("@carton_qty", cartonQty);
                param.Add("@status", "0");
                param.Add("@is_closed", "N");
                param.Add("@type", "1");
                param.Add("@factory_id", factory);
                param.Add("@enabled", "Y");
                param.Add("@user_id", loginUserId);

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);

                sql.Remove(0, sql.Length);
                sql.Append("SELECT TOP 1 RIGHT(carton_no,3) AS carton_no,lot_no FROM sys_stock (NOLOCK) WHERE 1=1 ");
                sql.Append("  AND part_id=@part_id  ORDER BY carton_no DESC");
                param = new DynamicParameters();
                param.Add("@type_id", typeId);
                //param.Add("@se_date", seDate.Replace("-", "/"));
                param.Add("@part_id", partId);

                int iLastQty = 0;
                List<SysStock> dtCarton = dbConn.Query<SysStock>(sql.ToString(), param).ToList();
                if (dtCarton != null && dtCarton.Count > 0)
                {
                    iLastQty = Convert.ToInt32(dtCarton[0].CartonNo.ToString());
                }

                int iRema = qty % snpQty;

                for (int j = 1; j <= cartonQty; j++)
                {
                    iLastQty++;
                    string strCartonNo = GetCartonNo(partNo, lotNo, iLastQty);
                    string strCartonQty = snpQty.ToString();

                    if (j == cartonQty && iRema > 0)
                    {
                        strCartonQty = iRema.ToString();
                    }

                    sql.Remove(0, sql.Length);
                    sql.Append("INSERT INTO sys_stock (vendor_id,part_id ");
                    sql.Append(" ,carton_no,lot_no,qty,snp_qty,status,factory_id,enabled,create_userid, create_time) ");
                    sql.Append("  VALUES (@vendor_id,@part_id ");
                    sql.Append(" ,@carton_no,@lot_no,@qty,@snpQty,@status,@factory_id,@enabled,@user_id, CONVERT(varchar(50), GETDATE(), 21))");
                    param = new DynamicParameters();
                    //param.Add("@order_no", orderNo);
                    //param.Add("@type_id", typeId);
                    //param.Add("@buy_no", buyNo);
                    param.Add("@vendor_id", vendorId);
                    //param.Add("@se_date", seDate.Replace("-", "/"));
                    param.Add("@part_id", partId);
                    param.Add("@carton_no", strCartonNo);
                    param.Add("@lot_no", lotNo);
                    param.Add("@qty", strCartonQty);
                    param.Add("@snpQty", snpQty.ToString());
                    param.Add("@status", "A");
                    //param.Add("@type", "1");
                    //param.Add("@ship_no", shipNo);
                    param.Add("@factory_id", loginUserFactory);
                    param.Add("@enabled", "Y");
                    param.Add("@user_id", loginUserId);

                    sqlStrings.Add(sql.ToString());
                    parameters.Add(param);
                }
                IDbTransaction transaction = dbConn.BeginTransaction();

                for (int i = 0; i < sqlStrings.Count; i++)
                {
                    dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                }
                transaction.Commit();
                return true;
            }
        }

        /// <summary>
        /// 箱条码生成(零件号(18)+批号(6)+流水号(3))
        /// </summary>
        /// <param name="partNo">零件号</param>
        /// <param name="lotNo">批号</param>
        /// <param name="seq">流水号</param>
        /// <returns></returns>
        private string GetCartonNo(string partNo, string lotNo, int seq)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                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="row"></param>
        /// <returns></returns>
        public bool deleteBuyOrder(Hashtable htData)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                IDbTransaction transaction = dbConn.BeginTransaction();
                string orderNo = Convert.ToString(htData["orderNo"]);
                int vendorId = Convert.ToInt32(htData["vendor"].ToString());
                string seDate = Convert.ToString(htData["dtpSeDate"]);
                string shipNo = Convert.ToString(htData["shipNo"]);
                string buyNo = Convert.ToString(htData["buyNo"]);
                string partId = Convert.ToString(htData["partId"]);

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

                StringBuilder sql = new StringBuilder();
                sql.Append("update g_buy_day_fact set Enabled='N' WHERE order_no=@order_no AND part_id = @part_id AND vendor_id=@vendor_id AND buy_no=@buy_no AND se_date=@se_date");
                DynamicParameters param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@part_id", partId);
                param.Add("@vendor_id", vendorId);
                param.Add("@buy_no", buyNo);
                param.Add("@se_date", seDate.Replace("-", "/"));

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);

                sql.Clear();
                sql.Append(" update sys_stock set Enabled='N'  WHERE status = 'A' AND order_no=@order_no AND part_id=@part_id  ");
                sql.Append(" AND se_date=@se_date AND ship_no=@ship_no ");
                param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@part_id", partId);
                param.Add("@ship_no", shipNo);
                param.Add("@se_date", seDate.Replace("-", "/"));

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);
                try
                {
                    for (int i = 0; i < sqlStrings.Count; i++)
                    {
                        dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }
        
            }
        }
        //启用
        public bool EnableData(Hashtable htData)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                IDbTransaction transaction = dbConn.BeginTransaction();
                string orderNo = Convert.ToString(htData["orderNo"]);
                int vendorId = Convert.ToInt32(htData["vendor"].ToString());
                string seDate = Convert.ToString(htData["dtpSeDate"]);
                string shipNo = Convert.ToString(htData["shipNo"]);
                string buyNo = Convert.ToString(htData["buyNo"]);
                string partId = Convert.ToString(htData["partId"]);

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

                StringBuilder sql = new StringBuilder();
                sql.Append("update g_buy_day_fact set Enabled='Y' WHERE order_no=@order_no AND part_id = @part_id AND vendor_id=@vendor_id AND buy_no=@buy_no AND se_date=@se_date");
                DynamicParameters param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@part_id", partId);
                param.Add("@vendor_id", vendorId);
                param.Add("@buy_no", buyNo);
                param.Add("@se_date", seDate.Replace("-", "/"));

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);

                sql.Clear();
                sql.Append(" update sys_stock set Enabled='Y'  WHERE status = 'A' AND order_no=@order_no AND part_id=@part_id  ");
                sql.Append(" AND se_date=@se_date AND ship_no=@ship_no ");
                param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@part_id", partId);
                param.Add("@ship_no", shipNo);
                param.Add("@se_date", seDate.Replace("-", "/"));

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);
                try
                {
                    for (int i = 0; i < sqlStrings.Count; i++)
                    {
                        dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }

            }
        }
        //禁用
        public bool DisableData(Hashtable htData)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                IDbTransaction transaction = dbConn.BeginTransaction();
                string orderNo = Convert.ToString(htData["orderNo"]);
                int vendorId = Convert.ToInt32(htData["vendor"].ToString());
                string seDate = Convert.ToString(htData["dtpSeDate"]);
                string shipNo = Convert.ToString(htData["shipNo"]);
                string buyNo = Convert.ToString(htData["buyNo"]);
                string partId = Convert.ToString(htData["partId"]);

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

                StringBuilder sql = new StringBuilder();
                sql.Append("update g_buy_day_fact set Enabled='N' WHERE order_no=@order_no AND part_id = @part_id AND vendor_id=@vendor_id AND buy_no=@buy_no AND se_date=@se_date");
                DynamicParameters param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@part_id", partId);
                param.Add("@vendor_id", vendorId);
                param.Add("@buy_no", buyNo);
                param.Add("@se_date", seDate.Replace("-", "/"));

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);

                sql.Clear();
                sql.Append(" update sys_stock set Enabled='N'  WHERE status = 'A' AND order_no=@order_no AND part_id=@part_id  ");
                sql.Append(" AND se_date=@se_date AND ship_no=@ship_no ");
                param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@part_id", partId);
                param.Add("@ship_no", shipNo);
                param.Add("@se_date", seDate.Replace("-", "/"));

                sqlStrings.Add(sql.ToString());
                parameters.Add(param);
                try
                {
                    for (int i = 0; i < sqlStrings.Count; i++)
                    {
                        dbConn.Execute(sqlStrings[i], parameters[i], transaction);
                    }
                    transaction.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return false;
                }

            }
        }

        /// <summary>
        /// 获取采购单数据
        /// </summary>
        /// <param name="htData"></param>
        /// <returns></returns>
        public List<GBuyDayFact> getBuyOrder(Hashtable htData)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string orderNo = Convert.ToString(htData["orderNo"]);
                string seDate = Convert.ToString(htData["dtpSeDate"]);

                StringBuilder sql = new StringBuilder();
                sql.Remove(0, sql.Length);
                sql.Append("SELECT a.*,b.part_no,b.part_spec ");
                sql.Append(" ,a.lot_no,a.qty,a.snp_qty,a.carton_qty,a.factory_id FROM g_buy_day_fact(NOLOCK) AS a ");
                sql.Append(" LEFT JOIN sys_part(NOLOCK) AS b ON a.part_id=b.part_id WHERE a.order_no=@order_no ");
                sql.Append(" AND se_date=@se_date ORDER BY b.part_no");

                DynamicParameters param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                param.Add("@se_date", seDate.Replace("-", "/"));
                List<GBuyDayFact> result = dbConn.Query<GBuyDayFact>(sql.ToString(), param).ToList();
                return result;
            }
        }
        /// <summary>
        /// 返回采购单明细
        /// </summary>
        /// <param name="htData"></param>
        /// <returns></returns>
        public List<dynamic> getBuyOrderStock(Hashtable htData)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string orderNo = Convert.ToString(htData["orderNo"]);

                StringBuilder sql = new StringBuilder();
                sql.Remove(0, sql.Length);
                sql.Append("SELECT ruid,vendor_id,vendor_name ");
                sql.Append(" ,part_id,part_no,part_spec,carton_no,lot_no,qty,snp_qty,status ");
                sql.Append(" ,group_no,locate_id,date_code,factory_id ");
                sql.Append(" FROM vw_sys_stock WHERE ref_order_no=@order_no AND enabled='Y'  ORDER BY carton_no");

                DynamicParameters param = new DynamicParameters();
                param.Add("@order_no", orderNo);
                List<dynamic> result = dbConn.Query<dynamic>(sql.ToString(), param).ToList();
                return result;
            }
        }
		#endregion  成员方法
	}
}