|
|
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<GSnStatus>, IRepairQueryRepository
|
|
|
{
|
|
|
public RepairQueryRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 产线
|
|
|
/// </summary>
|
|
|
/// <returns>数据集</returns>
|
|
|
public List<KeyValueResult> 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<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 车型
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> 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<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 配置
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> 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<KeyValueResult> result = dbConn.Query<KeyValueResult>(quernSen).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public List<GSnStatus> 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<GSnStatus> result = dbConn.Query<GSnStatus>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount= parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<RepairQuery> 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<RepairQuery> result = dbConn.Query<RepairQuery>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
}
|