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

using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.IRepositories;
using Estsh.Core.Model.ExcelModel;
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
 * 描述：采购日程单维护DAL层
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class PurchaseScheduleSingleRepository : BaseRepository<SysStock>, IPurchaseScheduleSingleRepository
    {
        public PurchaseScheduleSingleRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        /// <summary>
        /// 根据查询条件得出日程单收货数据结果集
        /// </summary>
        /// <param name="aWhere">查询条件</param>
        /// <returns>符合条件的结果集</returns>
        public Hashtable GetQuery(string aWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable hs = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.* , ");
                SqlStringBuilder.Append("      b.type_name , ");
                SqlStringBuilder.Append("      c.vendor_name , ");
                SqlStringBuilder.Append("      d.part_no , ");
                SqlStringBuilder.Append("      d.part_spec , ");
                SqlStringBuilder.Append("      e.factory_name , ");
                SqlStringBuilder.Append("      f.enum_desc AS day_status , ");
                SqlStringBuilder.Append("      g.enum_desc AS day_type ");
                SqlStringBuilder.Append("FROM  dbo.g_buy_day_fact a ");
                SqlStringBuilder.Append("      LEFT JOIN sys_model_type b ON a.type_id = b.type_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_vendor c ON a.vendor_id = c.vendor_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_factory e ON a.factory_id = e.factory_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_enum f ON a.status = f.enum_value ");
                SqlStringBuilder.Append("       AND f.enum_type = 'g_buy_day_fact_status' ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_enum g ON a.type = g.enum_value ");
                SqlStringBuilder.Append("       AND g.enum_type = 'g_buy_day_fact_type' WHERE   a.enabled = 'Y' ");

                if (!string.IsNullOrEmpty(aWhere))
                {
                    SqlStringBuilder.Append(aWhere);
                }

                List<GBuyDayFact> result = dbConn.Query<GBuyDayFact>(SqlStringBuilder.ToString()).ToList();
                hs.Add("data", result);
                hs.Add("dataList", result);
                return hs;
            }
        }
        public List<PurchaseScheduleSingle> getTableListByPage(string aWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable hs = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  a.* , ");
                SqlStringBuilder.Append("      b.type_name , ");
                SqlStringBuilder.Append("      c.vendor_name , ");
                SqlStringBuilder.Append("      d.part_no , ");
                SqlStringBuilder.Append("      d.part_spec , ");
                SqlStringBuilder.Append("      e.factory_name , ");
                SqlStringBuilder.Append("      f.enum_desc AS day_status , ");
                SqlStringBuilder.Append("      g.enum_desc AS day_type ");
                SqlStringBuilder.Append("FROM  dbo.g_buy_day_fact a ");
                SqlStringBuilder.Append("      LEFT JOIN sys_model_type b ON a.type_id = b.type_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_vendor c ON a.vendor_id = c.vendor_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_factory e ON a.factory_id = e.factory_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_enum f ON a.status = f.enum_value ");
                SqlStringBuilder.Append("       AND f.enum_type = 'g_buy_day_fact_status' ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_enum g ON a.type = g.enum_value ");
                SqlStringBuilder.Append("       AND g.enum_type = 'g_buy_day_fact_type' WHERE   a.enabled = 'Y' ");

                if (!string.IsNullOrEmpty(aWhere))
                {
                    SqlStringBuilder.Append(aWhere);
                }

                List<PurchaseScheduleSingle> result = dbConn.Query<PurchaseScheduleSingle>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
        /// <summary>
        /// 根据单号和车型查询库存明细
        /// </summary>
        /// <param name="orderNo">单据号</param>
        /// <param name="typeName">车型名称</param>
        /// <returns></returns>
        public Hashtable GetStockDetail(string orderNo, string typeName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable hs = new Hashtable();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELEC a.* , ");
                SqlStringBuilder.Append("      b.type_name , ");
                SqlStringBuilder.Append("      c.vendor_name , ");
                SqlStringBuilder.Append("      d.part_no as part_no1 , ");
                SqlStringBuilder.Append("      d.part_spec , ");
                SqlStringBuilder.Append("      e.factory_name , ");
                SqlStringBuilder.Append("      f.enum_desc AS stock_status , ");
                SqlStringBuilder.Append("      g.enum_desc AS stock_type , ");
                SqlStringBuilder.Append("      j.locate_name ");
                SqlStringBuilder.Append("FROM  dbo.sys_stock a ");
                SqlStringBuilder.Append("      LEFT JOIN sys_model_type b ON a.type_id = b.type_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_vendor c ON a.vendor_id = c.vendor_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_part d ON a.part_id = d.part_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_factory e ON a.factory_id = e.factory_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_enum f ON a.status = f.enum_value ");
                SqlStringBuilder.Append("      AND f.enum_type = 'sys_stock_status' ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_enum g ON a.type = g.enum_value ");
                SqlStringBuilder.Append("      AND g.enum_type = 'g_buy_day_fact_type' ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_locate j ON a.locate_id = j.locate_id ");
                SqlStringBuilder.Append("WHERE a.enabled = 'Y' and a.order_no=@order_no and b.type_name=@type_name ");

                DynamicParameters values = new DynamicParameters();
                values.Add("@order_no", orderNo);
                values.Add("@type_name", typeName);

                List<SysStock> result = dbConn.Query<SysStock>(SqlStringBuilder.ToString(), values).ToList();
                hs.Add("data", result);
                hs.Add("dataList", result);
                return hs;
            }
        }

        /// <summary>
        /// 获取所有的零件号
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetPart(string part_no)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select  part_no as [value],part_no as [key] from sys_part where enabled = 'Y' AND part_type=0 AND part_no LIKE '" + part_no + "%'";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取所有的供应商名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetVendor(string key)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " select vendor_name as [value],vendor_name as [key] from sys_vendor  where enabled = 'Y' and vendor_name LIKE '" + key + "%'";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取所有的厂区名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetFactory()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " select factory_name as [value],factory_name as [key] from sys_factory where enabled = 'Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取所有的车型名称
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " select type_name as [value] ,type_name as [key] from sys_model_type where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result; ;
            }
        }

        /// <summary>
        /// 更新日程收获数据的is_close字段
        /// </summary>
        /// <param name="ruid">编号</param>
        /// <returns></returns>
        public bool UpdateIsClose(string ruid)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " update g_buy_day_fact set is_closed='Y' where ruid = @ruid";
                DynamicParameters values = new DynamicParameters();
                values.Add("@ruid", ruid);

                int result = dbConn.Execute(SqlString, values);
                return result > 0;
            }
        }
        /// <summary>
        ///更改可用状态
        /// </summary>
        /// <param name="status">true or false</param>
        /// <param name="dr">当前行</param>
        /// <returns></returns>
        public bool ChangeDetailStatus(bool status, DataRow dr)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string guid = dr["cust_pdline_id"].ToString();
                string enabled = string.Empty;
                //string partID = GetPartMessage(partNo).Rows[0][0].ToString();

                string SqlString = "update dbo.sys_cust_pdline set enabled=@status where cust_pdline_id=@part_id";
                if (status)
                {
                    enabled = "Y";
                }
                else
                {
                    enabled = "N";
                }

                DynamicParameters values = new DynamicParameters();
                values.Add("@status", enabled);
                values.Add("@part_id", guid);
                int result = dbConn.Execute(SqlString, values);
                return result > 0;
            }

        }

        public bool DeleteDetail(DataRow dr)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                //string partID = GetPartMessage(dr[0].ToString()).Rows[0][0].ToString();

                string guid = dr["cust_pdline_id"].ToString();
                String delStr = "update sys_cust_pdline set Enabled='N'  where cust_pdline_id = @guid";
                DynamicParameters values = new DynamicParameters();
                values.Add("@guid", guid);
                int result = dbConn.Execute(delStr, values);
                return result > 0;
            }
        }

        /// <summary>
        /// 获取采购订单关闭的密码
        /// </summary>
        /// <returns>密码</returns>
        public string GetSysBase()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "select top 1 param_value from sys_base where param_name='CloseOrderPassWord' and enabled = 'Y' ";
                object obj = dbConn.ExecuteScalar(SqlString);
                if (obj==null)
                {
                    return "";
                }
                return obj.ToString();
            }
        }
    }
}