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

using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Repository.IRepositories;
using Estsh.Core.Model.EnumUtil;
using System.Globalization;

namespace Estsh.Core.Repositories
{
    public class CarManagerRepository : BaseRepository<WmsCarArrive>, ICarManagerRepository
    {
        public CarManagerRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        public List<WmsCarArrive> getList(string strWhere, string filedOrder)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"  SELECT a.order_no,b.dock as passageway,a.vendor_id,b.vendor_code as supplier_code,b.vendor_name supplier_name ,c.car_no,c.car_driver,c.car_desc FROM wms_purchase as a 
 left join  sys_vendor as b on a.vendor_id=b.vendor_id   left join  wms_car_arrive as c on a.order_no = c.order_no   ");
                if (!strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                List<WmsCarArrive> result = dbConn.Query<WmsCarArrive>(strSql.ToString()).ToList();
                return result;
            }
        }
        public List<WmsCarArrive> getArriveInfoList(string strWhere, string filedOrder)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder strSql = new StringBuilder();
                strSql.Append(@"  select * from wms_car_arrive as a left join wms_car_arrive_info as b on a.order_no = b.order_no     ");
                if (!strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }
                List<WmsCarArrive> result = dbConn.Query<WmsCarArrive>(strSql.ToString()).ToList();
                return result;
            }
        }
        /// <summary>
        /// 获取车辆管理数据列表
        /// </summary>
        public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                Hashtable result = new Hashtable();
                DynamicParameters Params = new DynamicParameters();
                Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@GroupColumn", "");
                Params.Add("@Table", " wms_purchase as a left join wms_car_arrive as b on a.order_no = b.order_no  left join sys_vendor as c on a.vendor_code = c.vendor_code");
                Params.Add("@Column", " a.order_no ," +
                    "a.vendor_code supplier_code," +
                    " c.vendor_name supplier_name," +
                    "a.se_date + a.se_time plan_arrive_time," +
                    //"   cast(left(a.se_date + a.se_time,8) as datetime)+stuff(stuff(right(a.se_date + a.se_time,6),3,0,':'),6,0,':')  plan_arrive_time," +
                    " b.real_arrive_time," +
                    "isnull(b.arrive_status,0) arrive_status," +
                    "isnull(b.car_in_plant_status,10) car_in_plant_status," +
                    "c.dock passageway," +
                    "b.real_in_plant_time, " +
                    "b.start_work_time, " +
                    "isnull(b.is_print_out_plant_list, 'N') is_print_out_plant_list," +
                    " b.print_out_plant_list_time," +
                    " b.out_plant_time, " +
                    " b.enabled");
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@Condition", strWhere);
                Params.Add("@OrderColumn", "se_date + se_time");
                Params.Add("@Group", 0);

                List<WmsCarArrive> dataList = dbConn.Query<WmsCarArrive>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();

                foreach (var item in dataList)
                {
                    item.PlanArriveTime = item.PlanArriveTime.PadRight(14, '0');
                    item.PlanArriveTime = item.PlanArriveTime.Substring(0, 4) + "-" + item.PlanArriveTime.Substring(4, 2) + "-" + item.PlanArriveTime.Substring(6, 2) + " " + item.PlanArriveTime.Substring(8, 2) + ":" + item.PlanArriveTime.Substring(10, 2) + ":" + item.PlanArriveTime.Substring(12); 
                }

                result.Add("dataList", dataList);
                result.Add("totalCount", Params.Get<int>("@TotalCount"));
                return result;
            }
        }

        /// <summary>
        /// 新增进厂订单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public string saveCarArrive(WmsCarArrive htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder strSql2 = new StringBuilder();
                strSql2.Append(@" select * from wms_car_arrive where order_no='" + htParams.OrderNo + "'   ");
                List<WmsPurchase> dataList2 = dbConn.Query<WmsPurchase>(strSql2.ToString()).ToList();
                if (dataList2.Count > 0)
                {
                    return "订单号" + htParams.OrderNo + "已添加，请检查！";
                }


                StringBuilder strSql = new StringBuilder();
                strSql.Append(@" select * from wms_purchase where order_no='" + htParams.OrderNo + "'   ");
                List<WmsPurchase> dataList = dbConn.Query<WmsPurchase>(strSql.ToString()).ToList();
                if (dataList.Count > 0)
                {
                    DateTime date = DateTime.ParseExact(dataList[0].SeDate, "yyyyMMdd", CultureInfo.InvariantCulture);
                    if (dataList[0].SeTime == "")
                        dataList[0].SeTime = "000000";
                    DateTime date2 = DateTime.ParseExact(dataList[0].SeTime, "HHmmss", CultureInfo.InvariantCulture);

                    string ddd = date.ToString("yyyy-MM-dd") + " " + date2.ToString("HH:mm:ss");
                    htParams.PlanArriveTime = ddd;
                    htParams.SupplierCode = dataList[0].VendorCode;

                    //判断到达状态  10 准点 20 早到 30 晚点
                    if (Convert.ToDateTime(ddd) >= DateTime.Now)
                    {
                        htParams.ArriveStatus = 10;
                    }
                    //if (Convert.ToDateTime(ddd) < DateTime.Now &&  DateTime.Now < Convert.ToDateTime(ddd).AddMinutes(20) )
                    //{
                    //    htParams.ArriveStatus = 20;
                    //}
                    if (Convert.ToDateTime(ddd) < DateTime.Now)
                    {
                        htParams.ArriveStatus = 30;
                    }

                    htParams.RealArriveTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                }

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_car_arrive( order_no ,car_no ,car_desc,car_driver,supplier_code , supplier_name, plan_arrive_time,real_arrive_time,arrive_status,car_in_plant_status,passageway,enabled,update_userid,update_time,create_userid,create_time,guid)VALUES ");
                SqlStringBuilder.Append("( @orderNo ,@carNo ,@carDesc,@carDriver,@supplierCode , @supplierName, @planArriveTime,@realArriveTime,@arriveStatus,20,@passageway," +
                    "@enabled,@updateUserid, CONVERT(varchar(50), GETDATE(), 120), @createUserid, CONVERT(varchar(50), GETDATE(), 120),newid()) ");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                if (result > 0)
                {
                    return "添加成功";
                }
                else
                {
                    return "添加失败";
                }

            }
        }

        /// <summary>
        /// 更新进厂订单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateCarArrive(WmsCarArrive htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE wms_car_arrive SET  ");
                SqlStringBuilder.Append("car_no=@CarNo,car_desc=@CarDesc,car_driver=@CarDriver,passageway=@Passageway,enabled=@enabled ,update_userid = @updateUserId ,update_time=CONVERT(varchar(50), GETDATE(), 120)  ");
                SqlStringBuilder.Append("WHERE order_no=@orderNo ");

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


        public int updateCarArriveInfo(WmsCarArrive htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("delete from wms_car_arrive_info where order_no = @OrderNo ; INSERT INTO dbo.wms_car_arrive_info( order_no,operator, outDep, remarks, carNo2, ascription, material, reason, desc1, desc2, desc3, desc4, desc5, desc6, desc7, desc8, desc9, outOperator,outDateTime ) VALUES ");
                SqlStringBuilder.Append("( @OrderNo,@operator,@outDep,@remarks,@carNo,@ascription,@material,@reason,@desc1,@desc2,@desc3,@desc4,@desc5,@desc6,@desc7,@desc8,@desc9,@outOperator,@outDateTime )");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }
        /// <summary>
        /// 打印放行单
        /// </summary>
        /// <param name="orderNo"></param>
        /// <param name="loginId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int PrintOutPlant(String orderNo, int loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set is_print_out_plant_list='Y',car_in_plant_status=@car_in_plant_status,print_out_plant_list_time= CONVERT(varchar(50), GETDATE(), 120),update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 120)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                htparams.Add("@car_in_plant_status", (int)WmsEnumUtil.WmsCarInPlantStatus.EXITING);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        public int ChangeInPlantStatus(String orderNo, int loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set car_in_plant_status=@status,real_in_plant_time= CONVERT(varchar(50), GETDATE(), 120) ,update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 120)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                htparams.Add("@status", (int)WmsEnumUtil.WmsCarInPlantStatus.INPLANT);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }
        public int ChangeOutPlantStatus(String orderNo, int loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set car_in_plant_status=@status,out_plant_time= CONVERT(varchar(50), GETDATE(), 120) ,update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 120)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                htparams.Add("@status", (int)WmsEnumUtil.WmsCarInPlantStatus.OUTPLANT);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        public int ChangeWorkingStatus(String orderNo, int loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set car_in_plant_status=@status,start_work_time= CONVERT(varchar(50), GETDATE(), 120) ,update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 120)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                htparams.Add("@status", (int)WmsEnumUtil.WmsCarInPlantStatus.PROCESSING);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }
        /// <summary>
        /// 修改到达状态
        /// </summary>
        /// <param name="orderNo"></param>
        /// <param name="loginId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int ChangeArriveStatus(String orderNo, int loginId, int status)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set arrive_status=@status,update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 21)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                htparams.Add("@status", status);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        /// <summary>
        /// 修改厂内状态
        /// </summary>
        /// <param name="orderNo"></param>
        /// <param name="loginId"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int ChangeCarInPlantStatus(String orderNo, int loginId, int status)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set car_in_plant_status=@status,update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 21)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                htparams.Add("@status", status);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        //启用
        public int EnableCarArrive(String orderNo, int loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set Enabled='Y',update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 21)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        //禁用
        public int DisableCarArrive(String orderNo, int loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update wms_car_arrive set Enabled='N',update_userid=@update_userid,update_time=CONVERT(varchar(50), GETDATE(), 21)  WHERE order_no = @orderNo";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@orderNo", orderNo);
                htparams.Add("@update_userid", loginId);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        /// <summary>
        /// 获取供应商
        /// </summary>
        /// <returns></returns>
        //public List<KeyValueResult> getSelectSupplier()
        //{
        //    using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
        //    {
        //        dbConn.Open();
        //        StringBuilder strSql = new StringBuilder();
        //        strSql.Append("select vendor_code 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> getSelectSupplier(string vendorId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from sys_Vendor where Enabled = 'Y' and vendor_Name like '%" + vendorId + "%'");
                List<SysVendor> result = dbConn.Query<SysVendor>(strSql.ToString()).ToList();
                return result;
            }
        }
    }
}