using Dapper; using Estsh.Core.Dapper; using Estsh.Core.Model.ExcelModel; using Estsh.Core.Model.Result; using Estsh.Core.Models; using Estsh.Core.Repositories; using Estsh.Core.Repository.IRepositories; using Estsh.Core.Util; using System.Collections; using System.Data; using System.Text; /*************************************************************************************************** * * 更新人：sitong.dong * 描述：报表模块不良条码查询DAL * 修改时间：2022.06.22 * 修改日志：系统迭代升级 * **************************************************************************************************/ namespace Estsh.Core.Repositories { public class RepairQueryRepository : BaseRepository, IRepairQueryRepository { public RepairQueryRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext) { } /// /// 产线 /// /// 数据集 public List GetPDLineName() { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append("select pdline_name as [value],pdline_desc as [key] from sys_pdline where enabled='Y' "); List result = dbConn.Query(SqlStringBuilder.ToString()).ToList(); return result; } } /// /// 车型 /// /// public List GetModelTypeList() { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.Append(" SELECT DISTINCT type_name as [value],type_name as [key] FROM dbo.sys_model_type WHERE enabled = 'Y' "); List result = dbConn.Query(SqlStringBuilder.ToString()).ToList(); return result; } } /// /// 配置 /// /// public List GetModelList(string typeName) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); string quernSen = ""; if (string.IsNullOrEmpty(typeName)) { quernSen = @" SELECT DISTINCT model_name as [value],model_name as [key] FROM dbo.sys_model WHERE enabled = 'Y' "; } else { quernSen = @" SELECT DISTINCT model_name as [value],model_name as [key] FROM dbo.sys_model a JOIN dbo.sys_model_type b ON a.model_type_id = b.type_id WHERE a.enabled = 'Y' AND b.type_name = '" + typeName + "' "; } List result = dbConn.Query(quernSen).ToList(); return result; } } public List GetRepairDataList(string whereStr, Pager pager, ref int totalCount) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); DynamicParameters parameters = new DynamicParameters(); parameters.Add( "@TotalCount", 100, DbType.Int32, ParameterDirection.Output); parameters.Add( "@TotalPage", 100, DbType.Int32, ParameterDirection.Output); parameters.Add( "@Table", @" dbo.g_sn_status a LEFT JOIN sys_part b ON a.part_id = b.part_id LEFT JOIN dbo.sys_pdline c ON a.pdline_id = c.pdline_id LEFT JOIN sys_model d ON d.model_id = a.model_id LEFT JOIN dbo.sys_model_type e ON e.type_id = d.model_type_id LEFT JOIN dbo.sys_emp f ON f.emp_id = a.update_userid "); parameters.Add("@Column", @" a.ruid AS lsn , c.pdline_name ,e.type_name , d.model_name ,a.serial_number , b.part_no ,b.part_spec ,b.cust_part_no ,a.remark , CASE a.prod_type WHEN 'S' THEN '订单生产' WHEN 'E' THEN '紧急插单' WHEN 'C'THEN '试制' WHEN 'Z'THEN '试制插单' WHEN 'P'THEN '培训' WHEN 'Q'THEN '培训插单' ELSE '未知' END AS prod_type, a.update_time AS utime ,f.emp_name, CONVERT(varchar(100),a.in_pdline_time,120) as in_pdline_time, CONVERT(varchar(100),a.out_pdline_time,120) as out_pdline_time"); parameters.Add( "@OrderColumn", " a.update_time DESC"); parameters.Add( "@GroupColumn", ""); parameters.Add("@PageSize", pager.pageSize); parameters.Add("@CurrentPage", pager.pageNo); parameters.Add("@Group", 0); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.AppendLine(" a.enabled = 'Y' "); SqlStringBuilder.AppendLine(" AND current_status IN(1) "); SqlStringBuilder.AppendLine(whereStr); parameters.Add("@Condition", SqlStringBuilder.ToString()); List result = dbConn.Query("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList(); totalCount= parameters.Get("@TotalCount"); return result; } } public List getTableListByPage(string whereStr, Pager pager, ref int totalCount) { using (IDbConnection dbConn = dapperDbContext.GetDbConnection()) { dbConn.Open(); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@TotalCount", 100, DbType.Int32, ParameterDirection.Output); parameters.Add("@TotalPage", 100, DbType.Int32, ParameterDirection.Output); parameters.Add("@Table", @" dbo.g_sn_status a LEFT JOIN sys_part b ON a.part_id = b.part_id LEFT JOIN dbo.sys_pdline c ON a.pdline_id = c.pdline_id LEFT JOIN sys_model d ON d.model_id = a.model_id LEFT JOIN dbo.sys_model_type e ON e.type_id = d.model_type_id LEFT JOIN dbo.sys_emp f ON f.emp_id = a.update_userid "); parameters.Add("@Column", @" a.ruid AS lsn , c.pdline_name ,e.type_name , d.model_name ,a.serial_number , b.part_no ,b.part_spec ,b.cust_part_no ,a.remark , CASE a.prod_type WHEN 'S' THEN '订单生产' WHEN 'E' THEN '紧急插单' WHEN 'C'THEN '试制' WHEN 'Z'THEN '试制插单' WHEN 'P'THEN '培训' WHEN 'Q'THEN '培训插单' ELSE '未知' END AS prod_type, a.update_time AS utime ,f.emp_name, CONVERT(varchar(100),a.in_pdline_time,120) as in_pdline_time, CONVERT(varchar(100),a.out_pdline_time,120) as out_pdline_time"); parameters.Add("@OrderColumn", " a.update_time DESC"); parameters.Add("@GroupColumn", ""); parameters.Add("@PageSize", pager.pageSize); parameters.Add("@CurrentPage", pager.pageNo); parameters.Add("@Group", 0); StringBuilder SqlStringBuilder = new StringBuilder(1024); SqlStringBuilder.AppendLine(" a.enabled = 'Y' "); SqlStringBuilder.AppendLine(" AND current_status IN(1) "); SqlStringBuilder.AppendLine(whereStr); parameters.Add("@Condition", SqlStringBuilder.ToString()); List result = dbConn.Query("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList(); totalCount = parameters.Get("@TotalCount"); return result; } } } }