using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using Microsoft.Extensions.Primitives;
using System.Data;
using System.Text;
using static Estsh.Core.Model.EnumUtil.WmsEnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 盘点
    /// </summary>
    public class CycleCountRepository : BaseRepository<BaseEntity>, ICycleCountRepository
    {
        public CycleCountRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取盘点单据信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsCheckStock> GetCycleCountOrderList(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.order_no,c.enum_desc AS order_type_desc,a.create_userid, ");
                SqlStringBuilder.Append("a.create_time,d.emp_no,a.factory_code  FROM dbo.wms_check_stock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND	c.enum_type='wms_check_stock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id  ");
                SqlStringBuilder.Append("WHERE a.order_no like '%" + orderNo + "%' and a.order_status in ('20','30') and a.enabled='Y' ");
                List<WmsCheckStock> wmsChecks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return wmsChecks;
            }
        }

        /// <summary>
        /// 获取盘点单据信息通过订单号
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<WmsCheckStock> GetCycleCountOrderListByOrderNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                SetObjectDetail rfInfo = new SetObjectDetail();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.order_no,c.enum_desc AS order_type_desc,b.carton_no,CONVERT(INT, b.qty) qty,CONVERT(INT, b.fact_qty) fact_qty,b.part_no ,a.create_userid,b.locate_id,b.locate_name, ");
                SqlStringBuilder.Append("a.create_time,b.zone_id,b.zone_name,d.emp_no,a.factory_code,p.part_spec   FROM dbo.wms_check_stock a (nolock) ");
                SqlStringBuilder.Append("LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND	c.enum_type='wms_check_stock_order_type'  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id  ");
                SqlStringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
                SqlStringBuilder.Append("WHERE a.order_no = '" + orderNo + "' and a.order_status in ('20','30') and a.enabled='Y' ");

                List<WmsCheckStock> wmsChecks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return wmsChecks;
            }
        }
        //根据库位名称获取库位信息
        public SysLocate GetLocateByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_locate (nolock) WHERE locate_name = '" + locateName + "'");
                SysLocate sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).FirstOrDefault();
                return sysLocates;
            }
        }

        //获取盘点范围
        public WmsCheckStockRange GetWmsCheckStockRangeByLocateName(string orderNo, string locateName, string zoneName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and  locate_name!='' and enabled='Y' ");
                WmsCheckStockRange wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
                if (wmsCheckStockLocate != null)
                {
                    stringBuilder = new StringBuilder();
                    stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and  locate_name='" + locateName + "' and enabled='Y' ");
                    wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
                }
                else
                {
                    stringBuilder = new StringBuilder();
                    stringBuilder.Append("SELECT * FROM dbo.wms_check_stock_range (nolock) WHERE order_no = '" + orderNo + "' and zone_name='" + zoneName + "' and enabled='Y' ");
                    wmsCheckStockLocate = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).FirstOrDefault();
                }
                return wmsCheckStockLocate;
            }
        }

        //根据库位获取应盘和实盘数据
        public List<WmsCheckStock> GetWmsCheckStockQtyFactByLocateName(string orderNo, string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("  SELECT  ISNULL(m.qty, 0) qty, ISNULL(n.fact_qty, 0) AS fact_qty,isnull(m.part_no,n.part_no) part_no,isnull(m.part_spec,n.part_spec) part_spec FROM  ");
                SqlStringBuilder.Append("    (SELECT  CONVERT(INT, SUM(b.qty)) qty,b.part_no ,p.part_spec  FROM dbo.wms_check_stock a (nolock)  ");
                SqlStringBuilder.Append("                LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no and b.carton_no not in (select rack_no from wms_rack)");
                SqlStringBuilder.Append("                LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no  ");
                SqlStringBuilder.Append("                WHERE a.order_no = @orderNo  and b.locate_name=@locateName  and a.order_status in ('20','30') and a.enabled='Y'  GROUP BY b.part_no ,p.part_spec) AS m ");
                SqlStringBuilder.Append("			 FULL JOIN  ");
                SqlStringBuilder.Append("	(SELECT  CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,p.part_spec  FROM dbo.wms_check_stock a (nolock)  ");
                SqlStringBuilder.Append("                LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no and b.carton_no not in (select rack_no from wms_rack)");
                SqlStringBuilder.Append("                LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no  ");
                SqlStringBuilder.Append("                WHERE a.order_no = @orderNo and b.dest_locate_name=@locateName and a.order_status in ('20','30') and a.enabled='Y'  GROUP BY b.part_no ,p.part_spec) AS n ");
                SqlStringBuilder.Append("				ON n.part_no = m.part_no ");

                //StringBuilder query = new StringBuilder(1024);
                //query.Append("SELECT sum(wcsl.qty) AS qty,sum(wcsl.fact_qty) AS fact_qty ,wcsl.part_no,sp.part_spec  FROM wms_check_stock_list wcsl (nolock)  ");
                //query.Append("JOIN wms_check_stock wcs (nolock)  ON wcsl.order_no = wcs.order_no  ");
                //query.Append("JOIN sys_part sp (nolock)  ON wcsl.part_id = sp.part_id  ");
                //query.Append("WHERE wcsl.order_no = @orderNo   ");
                //query.Append("    AND (wcsl.dest_locate_name=@locateName OR wcsl.locate_name = @locateName) ");
                //query.Append("    AND (wcs.order_status = 20 OR wcs.order_status = 30) ");
                //query.Append("    AND wcsl.enabled = 'Y' ");
                //query.Append("    AND wcsl.carton_no NOT IN (SELECT rack_no FROM wms_rack) ");
                //query.Append("GROUP BY wcsl.part_no,sp.part_spec  ");


                List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString(),new { orderNo = orderNo, locateName = locateName }).ToList();
                return checkStocks;
            }
        }

        //获取完成盘点数据
        public List<WmsCheckStock> GetFinishCycleCountData(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("    SELECT  CONVERT(INT, SUM(b.fact_qty)) fact_qty,b.part_no ,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI  FROM dbo.wms_check_stock a (nolock) ");
                SqlStringBuilder.Append("                               LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no    ");
                SqlStringBuilder.Append("                                LEFT JOIN	dbo.sap_cyco c ON c.IBLNR=b.order_no and c.matnr=b.part_no    ");
                SqlStringBuilder.Append("                               WHERE a.order_no = '" + orderNo + "'   ");
                SqlStringBuilder.Append("							   and a.order_status <> '40' and a.enabled='Y'  GROUP BY b.part_no,a.erp_warehouse,a.factory_id,a.factory_Code,a.order_no,c.ZEILI ");
                List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(SqlStringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }

        //获取零件号信息
        public SysPart GetPartNOInfoByPartNo(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("    SELECT * from sys_part (nolock)  where part_no='" + partNo + "' ");
                SysPart sysParts = dbConn.Query<SysPart>(SqlStringBuilder.ToString()).FirstOrDefault();
                return sysParts;
            }
        }

        //根据判断是不是台车
        public List<WmsRack> GetRackInfo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("select * from wms_rack (nolock) where rack_no='" + cartonNo + "' ");
                List<WmsRack> sysStocks = dbConn.Query<WmsRack>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

        //根据条码判断是不是台车
        public List<SysStock> GetStockByCaronNoTro(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE carton_no='" + cartonNo + "' and carton_type=1 ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }



        //根据台车条码列表
        public List<SysStock> GetStockByCaronNoTroList(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("		select b.*,a.rack_no from wms_rack_package a (nolock)  inner join sys_stock b (nolock)    ");
                SqlStringBuilder.Append("            on a.carton_no=b.carton_no and a.enabled='Y' where a.rack_no='" + cartonNo + "' ");

                List<SysStock> sysStocks = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }
        //根据成品号获取库存条码
        public List<SysStock> GetStockByTrayNo(string trayNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE tray_no=@trayNo");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString(),new { trayNo = trayNo}).ToList();
                return sysStocks;
            }
        }
        //根据条码获取库存条码
        public List<SysStock> GetStockByCaronNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE carton_no = @cartonNo");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString(),new { cartonNo = cartonNo}).ToList();
                return sysStocks;
            }
        }
        //判断盘点单范围库区是否存在零件号
        public List<WmsCheckStockRange> GetWmsCheckStockRangeByIsExsistPartNo(string orderNo, string locateName, string zoneName, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT* FROM dbo.wms_check_stock_range (nolock) WHERE order_no = @orderNo and enabled='Y' ");
                List<WmsCheckStockRange> wmsCheckStocks = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString(),new { orderNo = orderNo}).ToList();
                 
                //if (locateName != "" && locateName != null)
                //{
                //    stringBuilder.Append(" and  locate_name='" + locateName + " '");
                //}
                //if (zoneName != "" && zoneName != null)
                //{
                //    stringBuilder.Append("  and zone_name='" + zoneName + "' ");
                //}
                //if (zoneName != "" && zoneName != null)
                //{
                //    stringBuilder.Append("  and zone_name='" + zoneName + "' ");
                //}
                //stringBuilder.Append(" and part_no='" + partNo + "'  ");
                //List<WmsCheckStockRange> wmsCheckStocks = dbConn.Query<WmsCheckStockRange>(stringBuilder.ToString()).ToList();
                return wmsCheckStocks;
            }
        }
        public List<WmsCheckStockList> GetWmsCheckStockListByRackNoExist(string orderNo, string rackNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("	select * from wms_check_stock_list a (nolock) where a.order_no='" + orderNo + "' and a.carton_no='" + rackNo + "' ");

                List<WmsCheckStockList> wmsCheckStocks = dbConn.Query<WmsCheckStockList>(SqlStringBuilder.ToString()).ToList();
                return wmsCheckStocks;
            }
        }

        //判断台车条码是否已盘
        public List<WmsCheckStockList> GetWmsCheckStockListByRackNo(string orderNo, string rackNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("	select * from wms_check_stock_list a (nolock) inner join wms_rack_package b (nolock) on a.carton_no=b.carton_no and b.enabled='Y' where a.order_no='" + orderNo + "' and  b.rack_no='" + rackNo + "' and a.trans_status!='10'  ");

                List<WmsCheckStockList> wmsCheckStocks = dbConn.Query<WmsCheckStockList>(SqlStringBuilder.ToString()).ToList();
                return wmsCheckStocks;
            }
        }

        //根据条码查询盘点单数据
        public List<WmsCheckStock> GetWmsCheckStockByCartonNo(string orderNo, string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT  a.order_no,c.enum_desc AS order_type_desc,CONVERT(INT, b.qty) qty,CONVERT(INT, b.fact_qty) fact_qty,b.part_no ,a.create_userid,b.locate_id,b.locate_name, ");
                stringBuilder.Append("a.create_time,b.zone_id,b.zone_name,d.emp_no,a.factory_code,a.order_status,p.part_spec,a.is_obvious,b.trans_status FROM dbo.wms_check_stock a (nolock) ");
                stringBuilder.Append("LEFT JOIN	dbo.wms_check_stock_list b (nolock) ON a.order_no=b.order_no  ");
                stringBuilder.Append("LEFT JOIN dbo.sys_enum c ON a.order_type=c.enum_value AND	c.enum_type='wms_check_stock_order_type'  ");
                stringBuilder.Append("LEFT JOIN dbo.sys_emp d ON a.create_userid=d.emp_id  ");
                stringBuilder.Append("LEFT JOIN dbo.sys_part p (nolock) ON b.part_no=p.part_no ");
                stringBuilder.Append("WHERE  a.order_no ='" + orderNo + "' AND b.carton_no='" + cartonNo + "' and a.enabled='Y' ");
                List<WmsCheckStock> wmsCheckStocks = dbConn.Query<WmsCheckStock>(stringBuilder.ToString()).ToList();
                return wmsCheckStocks;
            }
        }

        //根据盘点单号获取盘点单
        public List<WmsCheckStock> GetWmsCheckStockByCartonNo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("SELECT  * FROM dbo.wms_check_stock (nolock) ");
                stringBuilder.Append("WHERE order_no ='" + orderNo + "'");
                List<WmsCheckStock> checkStocks = dbConn.Query<WmsCheckStock>(stringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }
        //新增盘点单明细数据
        public int InsertCheckStockListInfo(WmsCheckStockList checkStockList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_check_stock_list(order_no,warehouse_id,warehouse_name,zone_id,zone_name,locate_id,locate_name,dest_warehouse_id,dest_warehouse_name,dest_zone_id,dest_zone_name,dest_locate_id,dest_locate_name,part_id,part_no,part_spec,carton_no,qty,fact_qty,unit,sn_status,trans_status,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
                SqlStringBuilder.Append("     VALUES(@orderNo,@warehouseId,@warehouseName,@zoneId,@zoneName,@locateId,@locateName,@destwarehouseId,@destwarehouseName,@destzoneId,@destzoneName,@destlocateId,@destlocateName,@partId,@partNo,@partSpec,@cartonNo,@qty,@factQty,@unit,@snStatus,@transStatus, ");
                SqlStringBuilder.Append("		   @factoryId,@factoryCode,'Y',@createUserid,CONVERT(varchar(50), GETDATE(), 21),NEWID()) ");
                int recCheckStockListNum = dbConn.Execute(SqlStringBuilder.ToString(), checkStockList);
                return recCheckStockListNum;
            }
        }

        //修改盘点单状态
        public int UpdateCheckStockOrderStatus(string orderNo, string loginId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder(1024);
                stringBuilder.Append("UPDATE dbo.wms_check_stock SET order_status='40',update_userid=" + loginId + ",update_time=CONVERT(VARCHAR(20),GETDATE(),21) WHERE order_no='" + orderNo + "';");
                int refUpdateStatu = dbConn.Execute(stringBuilder.ToString());
                return refUpdateStatu;
            }
        }

        //获取台车盘点零件明细
        public List<WmsCheckStockList> GetCheckStockListByRackPartNo(string orderNo, string trolleyNo, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" select * from wms_check_stock_list a (nolock) left join wms_rack_package b (nolock) on a.carton_no=b.carton_no where a.order_no='" + orderNo + "' and a.rack_no='" + trolleyNo + "' and a.part_no='" + partNo + "' and trans_status!='10' ");
                List<WmsCheckStockList> checkStocks = dbConn.Query<WmsCheckStockList>(SqlStringBuilder.ToString()).ToList();
                return checkStocks;
            }
        }

        public List<SysStock> CheckCartonNoByRackNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from sys_stock as a (nolock) where carton_no=@carton_no ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

                return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
            }
        }

        /// <summary>
        /// 获取此盘点单中箱条码的数据
        /// </summary>
        /// <param name="orderNo"></param>
        /// <param name="cartonNo"></param>
        /// <returns></returns>
        public List<WmsCheckStockList> CheckRackListByCartonNo(string orderNo, string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from wms_check_stock_list as a (nolock) where order_no=@order_no and carton_no=@carton_no ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@order_no", orderNo);
                parameters.Add("@carton_no", cartonNo);

                return dbConn.Query<WmsCheckStockList>(sql.ToString(), parameters).ToList();
            }
        }

        public List<SysStock> GetStockByRackNo(string rackNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                StringBuilder query = new StringBuilder(1024);
                query.Append("SELECT ss.* from sys_stock ss  ");
                query.Append("left join wms_rack_package wrp on ss.carton_no = wrp.carton_no  ");
                query.Append("where (wrp.rack_no = @rackNo ");
                query.Append("and wrp.enabled = 'Y' ) ");
                query.Append("UNION  ");
                query.Append("SELECT * from sys_stock where ruid = ");
                query.Append("(SELECT MAX(ruid)  from sys_stock where carton_no = @rackNo) ");
                      
                return dbConn.Query<SysStock>(query.ToString(), new { rackNo = rackNo }).ToList();
            }
        }

        /// <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 int SQLTransaction(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 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }
    }
}