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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：WMS采购订单管理模块数据库访问类
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 处理菜单模块的业务数据
    /// </summary>
    public class OrderManageRepository : BaseRepository<SapDspo>, IOrderManageRepository
    {
        public OrderManageRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        //获取采购订单数据
        public List<SapDspo> getDataList(string weekNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select * from SAP_DSPO where WEEKNO='" + weekNo + "'  order by EVRTP ";
                List<SapDspo> list = dbConn.Query<SapDspo>(sql).ToList();
                return list;
            }
        }


        public List<SysPart> getPartByNo(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select * from sys_part where part_no='" + partNo + "' ";
                List<SysPart> list = dbConn.Query<SysPart>(sql).ToList();
                return list;
            }
        }
        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);// this._remotingProxy.ExecuteSotreProcedure("dbo.sys_create_orderno", list);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }

        public List<SysVendor> getVendorByNo(string vendor_code)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select * from sys_vendor where vendor_code='" + vendor_code + "' ";
                List<SysVendor> list = dbConn.Query<SysVendor>(sql).ToList();
                return list;
            }
        }

        public string GetOrderNo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameter = new DynamicParameters();
                parameter.Add("@orderNo", null, DbType.String, ParameterDirection.Output, 255);
                List<dynamic> depts = dbConn.Query<dynamic>("sys_create_sapo_no", parameter, commandType: CommandType.StoredProcedure).ToList();
                return parameter.Get<string>("@orderNo");
            }
        }

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getQueryMainListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy, int empId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                StringBuilder table = new StringBuilder();
                table.Append(" ( ");
                table.Append(" SELECT DISTINCT a.weekno,d.vendor_name, a.recymd, a.recusr, b.emp_name, a.werks  AS factory_code, c.factory_name, ");
                table.Append("        a.lgort,(CASE  a.staflg WHEN '1' THEN '已发布' WHEN '2' THEN '已确认' WHEN '3' THEN '已备货' WHEN '4' THEN '已发货' ELSE '已导入' END) AS staflg ");
                table.Append(" FROM dbo.SAP_DSPO a ");
                table.Append(" LEFT JOIN sys_emp b ON a.recusr = b.emp_id ");
                table.Append(" LEFT JOIN dbo.sys_factory c ON a.werks = c.factory_code ");
                table.Append(" LEFT JOIN dbo.sys_vendor AS d ON a.lifnr=d.vendor_code ");
                table.Append("WHERE 1=1 and a.weekno<>'' AND a.recusr=" + empId);
                table.Append(strWhere);
                table.Append(" ) a ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", "*");
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", "");
                List<SapDspo> depts = dbConn.Query<SapDspo>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", depts);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }


        /// <summary>
        /// 根据分页条件获取分页数据列表（明细)
        /// </summary>
        public List<SapDspo> getQueryDetailList(string weekNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.ebeln AS order_no, a.evrtp, a.lifnr AS vendor_code, d.vendor_name, ");
                SqlStringBuilder.Append("        a.matnr AS part_no, f.part_spec, f.part_id, a.zdev_num, a.unit, ");
                SqlStringBuilder.Append("        dbo.sap2wms_ymd(a.zdate) AS zdate, dbo.sap2wms_hms(a.zcjsj) AS zcjsj, ");
                SqlStringBuilder.Append("        a.werks AS factory_code, c.factory_name, a.lgort, a.bstrf, a.prnnum, ");
                SqlStringBuilder.Append("        a.recusr, b.emp_name, a.weekno ");
                SqlStringBuilder.Append("FROM    dbo.SAP_DSPO a ");
                SqlStringBuilder.Append("        LEFT JOIN sys_emp b ON a.recusr = b.emp_id ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_factory c ON a.werks = c.factory_code ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_vendor d ON a.lifnr = d.vendor_code ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part f ON a.matnr = f.part_no ");
                SqlStringBuilder.Append("WHERE   1 = 1 ");
                SqlStringBuilder.Append("        and a.weekno = @weekno");
                SqlStringBuilder.Append(" Order by a.ebeln desc ");

                DynamicParameters ht = new DynamicParameters();
                ht.Add("@weekno", weekNo == null ? "" : weekNo);
                List<SapDspo> result = dbConn.Query<SapDspo>(SqlStringBuilder.ToString(), ht).ToList();
                return result;
            }
        }

        /// <summary>
        /// 判断是否已生成条码
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public bool IsBuildCode(string weekNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT TOP 1 1 ");
                SqlStringBuilder.Append("FROM    wms_dspod a ");
                SqlStringBuilder.Append("        JOIN sap_dspo b ON a.ebeln = b.ebeln ");
                SqlStringBuilder.Append("WHERE   b.weekno = @weekno  ");

                DynamicParameters ht = new DynamicParameters();
                ht.Add("@weekno", weekNo);
                List<WmsDspod> result = dbConn.Query<WmsDspod>(SqlStringBuilder.ToString(), ht).ToList();
                return result.Count > 0 ? true : false;
            }
        }

        public string getUpdateSapDspo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update dbo.SAP_DSPO set STAFLG = '1' where WEEKNO = @weekno");
                return SqlStringBuilder.ToString();
            }
        }

        /// <summary>
        /// 获得插入条码数据的sql语句
        /// </summary>
        /// <returns></returns>
        public string getInsertBuildCodeSql()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder();
                SqlStringBuilder.Append("INSERT INTO WMS_DSPOD(EBELN,EVRTP,MATNR,ZDEV_NUM,BSTRF,ZTXM,PRNSTA) ");
                SqlStringBuilder.Append("VALUES(@EBELN,@EVRTP,@MATNR,@ZDEV_NUM,@BSTRF,@ZTXM,@PRNSTA) ");
                return SqlStringBuilder.ToString();
            }
        }

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sqlStrings"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public bool ExecuteSqlTransaction(List<string> sqlStrings, List<DynamicParameters> parameters)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                IDbTransaction transaction = dbConn.BeginTransaction();
                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="weekNo"></param>
        /// <returns></returns>
        public List<WmsDspod> GetCartonNo(string weekNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select DISTINCT a.ebeln AS order_no,a.lifnr AS vendor_code,d.vendor_name,  ");
                SqlStringBuilder.Append("        a.matnr AS part_no, f.part_spec,w.zdev_num,  ");
                SqlStringBuilder.Append("        dbo.sap2wms_ymd(a.zdate) AS zdate, ");
                SqlStringBuilder.Append("        f.option3 ,w.ZTXM as carton_no,a.REVNO as version ,w.evrtp ");
                SqlStringBuilder.Append("from WMS_DSPOD w ");
                SqlStringBuilder.Append("        left join SAP_DSPO a on w.EBELN = a.ebeln and w.evrtp=a.evrtp ");
                SqlStringBuilder.Append("        LEFT JOIN sys_emp b ON a.recusr = b.emp_id  ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_vendor d ON a.lifnr = d.vendor_code ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part f ON a.matnr = f.part_no  ");
                SqlStringBuilder.Append("WHERE   1 = 1 ");
                SqlStringBuilder.Append("        and a.weekno = @weekno");
                SqlStringBuilder.Append("        order by a.ebeln,zdate,w.ZTXM asc");

                DynamicParameters ht = new DynamicParameters();
                ht.Add("@weekno", weekNo);

                List<WmsDspod> result = dbConn.Query<WmsDspod>(SqlStringBuilder.ToString(), ht).ToList();
                return result;
            }
        }

        /// <summary>
        /// 判断条码是否已导出
        /// </summary>
        /// <param name="weekNo"></param>
        /// <returns></returns>
        public bool GetDspoYN(string weekNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select top 1 1 from SAP_DSPO  where ACTFLG <> 'Y' and weekno = '" + weekNo + "'";
                List<SapDspo> result = dbConn.Query<SapDspo>(sql).ToList();
                return result.Count > 0 ? true : false;
            }
        }

        /// <summary>
        /// 修改SAP打印标志
        /// </summary>
        /// <param name="POnum"></param>
        /// <returns></returns>
        public bool UpdateData(string weekNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "update SAP_DSPO  SET PRNNUM=ZDEV_NUM,ACTFLG='Y',ACTYMD=DBO.GET_YMD(),ACTHMS=DBO.GET_HMS() where weekno= '" + weekNo + " ' ";
                int obj = dbConn.Execute(sql);
                if (obj == 0)
                    return false;
                else
                    return true;
            }
        }

        /// <summary>
        ///获取客户名称
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public string GetFactoryName()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select DISTINCT a.factory_name from sys_factory a  WHERE a.enabled='Y' ");
                object obj = dbConn.ExecuteScalar(SqlStringBuilder.ToString());

                if (obj == null)
                    return string.Empty;
                else
                    return obj.ToString();
            }
        }

        /// <summary>
        /// 获取单号条码数量
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public int GetWmsCodeCount(string orderNo, string partNo, string evrtp)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select COUNT(1) from WMS_DSPOD where ebeln='" + orderNo + "' and matnr='" + partNo + "' and evrtp='" + evrtp + "'";
                object obj = dbConn.ExecuteScalar(sql);
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取第一模板明细
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<List<SapDspo>> GetExpInfo(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                List<List<SapDspo>> resultSet = new List<List<SapDspo>>();

                DynamicParameters param = new DynamicParameters();
                param.Add("@orderNo", orderNo);

                string strSqlo = @" select DISTINCT a.lifnr,a.recymd,sv.vendor_name,sv.vendor_tel,sv.vendor_fax,sv.vendor_mtel,sv.vendor_sale,
                                se.emp_name,a.lgort from SAP_DSPO a left join sys_vendor sv on a.lifnr=sv.vendor_code left join sys_emp se on a.recusr=se.emp_id
                                where a.ebeln=@orderNo";
                List<SapDspo> result1 = dbConn.Query<SapDspo>(strSqlo, param).ToList();
                resultSet.Add(result1);
                string strSqlt = @" select distinct a.matnr as part_no,sp.part_spec as PartDesc,a.prnnum as Qty,dbo.sap2wms_ymd(a.zdate) as InDate 
                                 from SAP_DSPO a left join sys_part sp on sp.part_no=a.matnr where a.ebeln=@orderNo 
                                 order by a.matnr,dbo.sap2wms_ymd(a.zdate)";
                List<SapDspo> result2 = dbConn.Query<SapDspo>(strSqlt, param).ToList();
                resultSet.Add(result2);
                string strSqls = @" select distinct a.matnr,sp.part_spec from SAP_DSPO a left join sys_part sp on sp.part_no=a.matnr where a.ebeln=@orderNo";
                List<SapDspo> result3 = dbConn.Query<SapDspo>(strSqls, param).ToList();
                resultSet.Add(result3);

                return resultSet;
            }
        }

        /// <summary>
        /// 获取导入数据最早时间
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<SapDspo> GetStartDate(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = @"select distinct dbo.sap2wms_ymd(a.zdate) as InDate from SAP_DSPO a 
                            where a.ebeln='" + orderNo + "' order by dbo.sap2wms_ymd(a.zdate) asc";
                List<SapDspo> result = dbConn.Query<SapDspo>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 条码明细
        /// </summary>
        /// <returns></returns>
        public List<SapDspo> GetCodeDetail(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = @"SELECT  f.factory_name, v.vendor_name, p.part_spec, p.option3,
                                   a.EBELN + '_' + a.REVNO as order_no, a.EVRTP, a.LIFNR, a.MATNR, a.ZDATE, a.WERKS,
                                   b.ZTXM, b.ZDEV_NUM, a.ZDEV_NUM, a.BSTRF,
                                   CONVERT(VARCHAR(4),CONVERT(INT,SUBSTRING(b.ztxm,14,4)))+'/'+CONVERT(VARCHAR(4),CEILING(a.ZDEV_NUM/a.BSTRF)) as seq
                             FROM  dbo.SAP_DSPO a
                                   JOIN dbo.WMS_DSPOD b ON a.EBELN = b.EBELN
                                                           AND a.EVRTP = b.EVRTP
                                   JOIN dbo.sys_factory f ON a.werks = f.factory_code
                                   JOIN dbo.sys_part p ON a.matnr = p.part_no
                                   JOIN dbo.sys_vendor v ON a.lifnr = v.vendor_code
                            WHERE  a.EBELN = '" + orderNo + "'order by a.ZDATE,a.ebeln,b.ZTXM asc";

                List<SapDspo> result = dbConn.Query<SapDspo>(sql).ToList();
                return result;
            }
        }

        /// <summary>
        /// 删除用户数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteOrder(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder delStr = new StringBuilder();
                delStr.Append(" update SAP_DSPO set Enabled='N'  WHERE weekno IN ( @weekno ) ");
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@weekno", ids);
                int result = dbConn.Execute(delStr.ToString(), htparams);
                return result;
            }
        }

        //启用
        public int EnableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update SAP_DSPO set Enabled='Y'  WHERE weekno in (@weekno)";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@weekno", ids);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        //禁用
        public int DisableData(String ids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String delStr = "update SAP_DSPO set Enabled='N'  WHERE weekno in (@weekno)";
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@weekno", ids);
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }
        #endregion  成员方法
    }
}