|
|
using Dapper;
|
|
|
using Estsh.Core.Dapper;
|
|
|
using Estsh.Core.Model.ExcelModel;
|
|
|
using Estsh.Core.Model.Result;
|
|
|
using Estsh.Core.Models;
|
|
|
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 SerialNumberQuery2Repository : BaseRepository<GSnKeyparts>, ISerialNumberQuery2Repository
|
|
|
{
|
|
|
public SerialNumberQuery2Repository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 查找总成零件号
|
|
|
/// </summary>
|
|
|
/// <returns>数据集</returns>
|
|
|
public List<KeyValueResult> GetPartNo()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append(" select part_no as [value],part_no as [key] from sys_part WHERE part_type= 1 AND part_location IN ('01','02') Group by part_no ");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 获得不绑定条码信息
|
|
|
/// </summary>
|
|
|
/// <param name="whereStr"></param>
|
|
|
/// <param name="pager"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnKeyparts> GetSNInfo(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @"dbo.g_sn_keyparts(NOLOCK) a
|
|
|
JOIN sys_part b(NOLOCK) ON b.part_id = a.item_part_id
|
|
|
JOIN sys_emp(NOLOCK) c on a.create_userid = c.emp_id
|
|
|
JOIN sys_model(NOLOCK) d on b.model_id = d.model_id ");
|
|
|
parameters.Add( "@Column", @" a.serial_number,
|
|
|
a.kpsn, b.part_no, b.part_spec,c.emp_name,a.create_time AS ctime,a.update_time AS utime,d.model_alias ");
|
|
|
parameters.Add( "@OrderColumn", " ruid 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.version IN ( 'SubFG' ) " + whereStr);
|
|
|
parameters.Add( "@Condition", SqlStringBuilder.ToString());
|
|
|
List<GSnKeyparts> depts = dbConn.Query<GSnKeyparts>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount= parameters.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
public List<SerialNumberQuery2SN> GetSNInfoByList(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add("@Table", @"dbo.g_sn_keyparts(NOLOCK) a
|
|
|
JOIN sys_part b(NOLOCK) ON b.part_id = a.item_part_id
|
|
|
JOIN sys_emp(NOLOCK) c on a.create_userid = c.emp_id
|
|
|
JOIN sys_model(NOLOCK) d on b.model_id = d.model_id ");
|
|
|
parameters.Add("@Column", @" a.serial_number,
|
|
|
a.kpsn, b.part_no, b.part_spec,c.emp_name,a.create_time AS ctime,a.update_time AS utime,d.model_alias ");
|
|
|
parameters.Add("@OrderColumn", " ruid 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.version IN ( 'SubFG' ) " + whereStr);
|
|
|
parameters.Add("@Condition", SqlStringBuilder.ToString());
|
|
|
List<SerialNumberQuery2SN> depts = dbConn.Query<SerialNumberQuery2SN>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 获得条码当前信息
|
|
|
/// </summary>
|
|
|
/// <param name="whereStr"></param>
|
|
|
/// <param name="pager"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnStatus> GetSNCurrentData(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
DynamicParameters parameter = new DynamicParameters();
|
|
|
parameter.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameter.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameter.Add( "@Table", @" dbo.g_sn_status(NOLOCK) a
|
|
|
LEFT JOIN sys_part(NOLOCK) b ON a.part_id = b.part_id
|
|
|
LEFT JOIN dbo.sys_pdline(NOLOCK) c ON a.pdline_id = c.pdline_id
|
|
|
LEFT JOIN sys_model(NOLOCK) d ON d.model_id = a.model_id
|
|
|
LEFT JOIN dbo.sys_model_type(NOLOCK) e ON e.type_id = d.model_type_id
|
|
|
LEFT JOIN dbo.sys_emp(NOLOCK) f ON f.emp_id = a.update_userid ");
|
|
|
parameter.Add( "@Column", @" 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.current_status WHEN 0 THEN '良品' WHEN 1 THEN '不良品' WHEN -1 THEN '报废品' ELSE '其他' END AS current_status,
|
|
|
a.prod_type ,a.update_time as utime ,f.emp_name,CONVERT(NVARCHAR,a.out_pdline_time,120) AS out_pdline_time ,CONVERT(NVARCHAR,a.in_pdline_time,120) AS in_pdline_time ,CONVERT(NVARCHAR,a.shipping_time,120) AS shipping_time ");
|
|
|
parameter.Add( "@OrderColumn", " a.serial_number ASC ");
|
|
|
parameter.Add( "@GroupColumn", "");
|
|
|
parameter.Add("@PageSize", pager.pageSize);
|
|
|
parameter.Add("@CurrentPage", pager.pageNo);
|
|
|
parameter.Add("@Group", 0);
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
|
|
|
parameter.Add( "@Condition", SqlStringBuilder.ToString());
|
|
|
|
|
|
List<GSnStatus> depts = dbConn.Query<GSnStatus>("Com_Pagination", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
|
|
|
|
|
|
if ((depts == null || depts.Count < 1) && this.IsSerialNumberQuery2History())
|
|
|
{
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @" dbo.vw_g_sn_status_union(NOLOCK) a
|
|
|
LEFT JOIN sys_part(NOLOCK) b ON a.part_id = b.part_id
|
|
|
LEFT JOIN dbo.sys_pdline(NOLOCK) c ON a.pdline_id = c.pdline_id
|
|
|
LEFT JOIN sys_model(NOLOCK) d ON d.model_id = a.model_id
|
|
|
LEFT JOIN dbo.sys_model_type(NOLOCK) e ON e.type_id = d.model_type_id
|
|
|
LEFT JOIN dbo.sys_emp(NOLOCK) f ON f.emp_id = a.update_userid ");
|
|
|
parameters.Add( "@Column", @" c.pdline_name ,
|
|
|
e.type_name ,d.model_name ,a.serial_number ,a.car_no ,b.part_no ,b.part_spec ,b.cust_part_no ,a.remark ,
|
|
|
CASE a.current_status WHEN 0 THEN '良品' WHEN 1 THEN '不良品' WHEN -1 THEN '报废品' ELSE '其他' END AS current_status,
|
|
|
a.prod_type,a.update_time as utime ,f.emp_name");
|
|
|
parameters.Add( "@OrderColumn", " a.serial_number ASC ");
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
|
|
|
parameters.Add( "@Condition", SqlStringBuilder.ToString());
|
|
|
|
|
|
|
|
|
depts = dbConn.Query<GSnStatus>("Com_Pagination", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
}
|
|
|
|
|
|
totalCount = parameter.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 关键数据查询是否查询历史表数据
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public bool IsSerialNumberQuery2History()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT TOP 1 param_value FROM dbo.sys_base(NOLOCK) WHERE param_name='IsSerialNumberQuery2History'";
|
|
|
object obj = dbConn.ExecuteScalar(sql);
|
|
|
|
|
|
if (obj == null)
|
|
|
{
|
|
|
return false;
|
|
|
}
|
|
|
if (obj.ToString().ToUpper() != "Y")
|
|
|
{
|
|
|
return false;
|
|
|
}
|
|
|
return true;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获得历史信息
|
|
|
/// </summary>
|
|
|
/// <param name="whereStr"></param>
|
|
|
/// <param name="pager"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnTravel> GetSNTravelData(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @" dbo.g_sn_travel(NOLOCK) a
|
|
|
LEFT JOIN sys_part(NOLOCK) b ON a.part_id = b.part_id
|
|
|
LEFT JOIN dbo.sys_pdline(NOLOCK) c ON a.pdline_id = c.pdline_id
|
|
|
LEFT JOIN sys_model(NOLOCK) d ON d.model_id = a.model_id
|
|
|
LEFT JOIN dbo.sys_model_type(NOLOCK) e ON e.type_id = d.model_type_id
|
|
|
LEFT JOIN dbo.sys_emp(NOLOCK) f ON f.emp_id = a.update_userid
|
|
|
LEFT JOIN dbo.sys_terminal(NOLOCK) g ON a.terminal_id = g.terminal_id
|
|
|
LEFT JOIN dbo.g_sn_repair(NOLOCK) h ON a.serial_number = h.serial_number
|
|
|
LEFT JOIN dbo.g_sn_defect(NOLOCK) i ON a.serial_number = i.serial_number
|
|
|
LEFT JOIN dbo.sys_defect(NOLOCK) j ON i.defect_id = j.defect_id ");
|
|
|
parameters.Add( "@Column", @" c.pdline_name ,
|
|
|
e.type_name ,d.model_name ,a.serial_number ,a.car_no ,b.part_no ,b.part_spec ,b.cust_part_no ,a.remark ,
|
|
|
CASE a.current_status WHEN 0 THEN '良品' WHEN 1 THEN '不良品' WHEN -1 THEN '报废品' ELSE '其他' END AS current_status,
|
|
|
a.prod_type ,g.terminal_name,CONVERT(VARCHAR(22),out_process_time,120) AS ctime ,j.defect_code ,j.defect_desc ,f.emp_name ");
|
|
|
parameters.Add( "@OrderColumn", " a.out_process_time ASC ");
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
|
|
|
parameters.Add( "@Condition", SqlStringBuilder.ToString());
|
|
|
List<GSnTravel> result = dbConn.Query<GSnTravel>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 关键数据
|
|
|
/// </summary>
|
|
|
/// <param name="serialNumber"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnKeydata> GetKeyData(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @" g_sn_keydata(NOLOCK) a
|
|
|
LEFT JOIN dbo.g_sn_status (NOLOCK) b ON a.serial_number=b.serial_number
|
|
|
LEFT JOIN dbo.sys_pdline (NOLOCK) c ON b.pdline_id =c.pdline_id
|
|
|
LEFT JOIN dbo.sys_part (NOLOCK) d ON b.part_id =d.part_id ");
|
|
|
parameters.Add( "@Column", @" a.serial_number,a.keydata_desc,a.keydata_value ,a.create_time, c.pdline_name, d.part_spec,a.keydata_angle ");
|
|
|
parameters.Add( "@OrderColumn", " a.create_time ");
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
|
|
|
parameters.Add( "@Condition", SqlStringBuilder.ToString());
|
|
|
List<GSnKeydata> result = dbConn.Query<GSnKeydata>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SerialNumberQuery2> getTableListByPage(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add("@Table", @" g_sn_keydata(NOLOCK) a
|
|
|
LEFT JOIN dbo.g_sn_status (NOLOCK) b ON a.serial_number=b.serial_number
|
|
|
LEFT JOIN dbo.sys_pdline (NOLOCK) c ON b.pdline_id =c.pdline_id
|
|
|
LEFT JOIN dbo.sys_part (NOLOCK) d ON b.part_id =d.part_id ");
|
|
|
parameters.Add("@Column", @" a.serial_number,a.keydata_desc,a.keydata_value ,a.create_time, c.pdline_name, d.part_spec,a.keydata_angle ");
|
|
|
parameters.Add("@OrderColumn", " a.create_time ");
|
|
|
parameters.Add("@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
|
|
|
parameters.Add("@Condition", SqlStringBuilder.ToString());
|
|
|
List<SerialNumberQuery2> result = dbConn.Query<SerialNumberQuery2>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
// <summary>
|
|
|
/// 绑定信息
|
|
|
/// </summary>
|
|
|
/// <param name="serialNumber"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnKeyparts> GetKeyParts(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @" g_sn_keyparts(NOLOCK) a
|
|
|
LEFT JOIN sys_part(NOLOCK) b ON a.item_part_id=b.part_id
|
|
|
LEFT JOIN dbo.g_sn_status c ON a.serial_number=c.serial_number
|
|
|
LEFT JOIN dbo.sys_pdline d ON c.pdline_id=d.pdline_id
|
|
|
LEFT JOIN dbo.g_sn_status e ON a.serial_number =e.serial_number
|
|
|
LEFT JOIN sys_part f ON e.part_id=f.part_id where 1=1 and a." + whereStr + "");
|
|
|
parameters.Add( "@Column", " d.pdline_name,a.serial_number,f.part_spec AS ps , a.kpsn ,b.part_no,b.part_spec, a.create_time ");
|
|
|
parameters.Add( "@OrderColumn", " a.create_time ");
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
//StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
//SqlStringBuilder.AppendLine(" and 1=1 and a." + whereStr + " ) t ");
|
|
|
parameters.Add( "@Condition", "");
|
|
|
List<GSnKeyparts> result = dbConn.Query<GSnKeyparts>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 电功能检测
|
|
|
/// </summary>
|
|
|
/// <param name="serialNumber"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnStatus> GetCheck(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @" dbo.i_func_testdata a
|
|
|
LEFT JOIN dbo.g_sn_status b ON REPLACE(REPLACE(a.座椅条形码,CHAR(10),''),CHAR(13),'') = b.serial_number
|
|
|
LEFT JOIN dbo.sys_pdline c ON b.pdline_id=c.pdline_id ");
|
|
|
parameters.Add( "@Column", @" c.pdline_name AS '生产线',CONVERT(VARCHAR,a.测试完成日期+' '+a.测试完成时间,120) AS '时间',a.* ");
|
|
|
parameters.Add( "@OrderColumn", " a.测试完成日期,a.测试完成时间 ");
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.AppendLine(" 1=1 " + 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;
|
|
|
}
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// 不良信息
|
|
|
/// </summary>
|
|
|
/// <param name="serialNumber">条码</param>
|
|
|
/// <returns></returns>
|
|
|
public List<GSnDefect> GetRepairDetials(string whereStr, Pager pager, ref int totalCount)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
parameters.Add( "@Table", @" g_sn_defect(NOLOCK) a
|
|
|
left JOIN g_sn_repair(NOLOCK) b ON a.serial_number=b.serial_number and a.ruid =b.sn_defect_id
|
|
|
left JOIN sys_defect(NOLOCK) c ON a.defect_id=c.defect_id
|
|
|
left JOIN sys_reason(NOLOCK) d ON b.reason_id=d.reason_id
|
|
|
left JOIN sys_duty(NOLOCK) e ON b.duty_id =e.duty_id
|
|
|
left JOIN sys_emp(NOLOCK) f on b.repair_emp_id=f.emp_id ");
|
|
|
parameters.Add( "@Column", @" a.serial_number,c.defect_code,c.defect_desc,d.reason_code,d.reason_desc
|
|
|
,e.duty_code,e.duty_desc,f.emp_name ,a.memo,b.remark,isnull(b.create_time,a.create_time) create_time
|
|
|
");
|
|
|
parameters.Add( "@OrderColumn", " a.serial_number ASC ");
|
|
|
parameters.Add( "@GroupColumn", "");
|
|
|
parameters.Add("@PageSize", pager.pageSize);
|
|
|
parameters.Add("@CurrentPage", pager.pageNo);
|
|
|
parameters.Add("@Group", 0);
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.AppendLine(" 1=1 " + whereStr);
|
|
|
parameters.Add( "@Condition", SqlStringBuilder.ToString());
|
|
|
List<GSnDefect> result = dbConn.Query<GSnDefect>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<GSnKeyparts> GetKPSN(string srcSN)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("SELECT b.kpsn FROM dbo.g_sn_status(NOLOCK) a ");
|
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_keyparts(NOLOCK) b ON a.serial_number = b.serial_number ");
|
|
|
SqlStringBuilder.Append(" WHERE a.serial_number = @srcSN");
|
|
|
DynamicParameters ht = new DynamicParameters();
|
|
|
ht.Add("@srcSN", srcSN);
|
|
|
try
|
|
|
{
|
|
|
List<GSnKeyparts> dt = dbConn.Query<GSnKeyparts>(SqlStringBuilder.ToString(), ht).ToList();
|
|
|
if ((dt == null || dt.Count < 1) && this.IsSerialNumberQuery2History())
|
|
|
{
|
|
|
SqlStringBuilder.Remove(0, SqlStringBuilder.Length);
|
|
|
SqlStringBuilder.Append("SELECT b.kpsn FROM dbo.vw_g_sn_status_union a ");
|
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.g_sn_keyparts(NOLOCK) b ON a.serial_number = b.serial_number ");
|
|
|
SqlStringBuilder.Append(" WHERE a.serial_number = @srcSN");
|
|
|
|
|
|
return dbConn.Query<GSnKeyparts>(SqlStringBuilder.ToString(), ht).ToList();
|
|
|
}
|
|
|
return dt;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
//ex.Message;
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
public List<GSnKeydata> valiDationKPSN(string strKPSN)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "select * from g_sn_keydata(NOLOCK) where serial_number = '" + strKPSN + "'";
|
|
|
List<GSnKeydata> result = dbConn.Query<GSnKeydata>(sqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 通过任意有绑定信息的条码取回过程条码 g_sn_status.serial_number
|
|
|
/// </summary>
|
|
|
/// <param name="srcSN">已绑定的条码</param>
|
|
|
/// <returns>若返回值为空，则没有查到信息</returns>
|
|
|
public string GetPSN(string srcSN)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder querySen = new StringBuilder(1024);
|
|
|
querySen.Append("SELECT TOP 1 ");
|
|
|
querySen.Append(" a.serial_number ");
|
|
|
querySen.Append("FROM dbo.g_sn_status(NOLOCK) a ");
|
|
|
querySen.Append(" LEFT JOIN dbo.g_sn_keyparts(NOLOCK) b ON a.serial_number = b.serial_number ");
|
|
|
querySen.Append("WHERE b.serial_number = @srcSN ");
|
|
|
querySen.Append(" OR b.kpsn = @srcSN ");
|
|
|
querySen.Append(" OR a.serial_number = @srcSN ");
|
|
|
|
|
|
DynamicParameters para = new DynamicParameters();
|
|
|
para.Add("@srcSN", srcSN);
|
|
|
|
|
|
try
|
|
|
{
|
|
|
object obj = dbConn.ExecuteScalar(querySen.ToString(), para);
|
|
|
|
|
|
if (obj == null && this.IsSerialNumberQuery2History())
|
|
|
{
|
|
|
querySen.Remove(0, querySen.Length);
|
|
|
querySen.Append("SELECT TOP 1 ");
|
|
|
querySen.Append(" a.serial_number ");
|
|
|
querySen.Append("FROM dbo.vw_g_sn_status_union a ");
|
|
|
querySen.Append(" LEFT JOIN dbo.g_sn_keyparts b ON a.serial_number = b.serial_number ");
|
|
|
querySen.Append("WHERE b.serial_number = @srcSN ");
|
|
|
querySen.Append(" OR b.kpsn = @srcSN ");
|
|
|
querySen.Append(" OR a.serial_number = @srcSN ");
|
|
|
|
|
|
obj = dbConn.ExecuteScalar(querySen.ToString(), para);
|
|
|
return obj == null ? "" : obj.ToString();
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
return obj == null ? "" : obj.ToString();
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
return "";
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
///
|
|
|
/// </summary>
|
|
|
/// <param name="sn"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SysModel> GetSNModelTypeInfo(string sn)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder querySen = new StringBuilder(1024);
|
|
|
querySen.Append("SELECT b.model_name , ");
|
|
|
querySen.Append(" c.type_name ");
|
|
|
querySen.Append("FROM dbo.g_sn_status(NOLOCK) a ");
|
|
|
querySen.Append(" LEFT JOIN dbo.sys_model(NOLOCK) b ON a.model_id = b.model_id ");
|
|
|
querySen.Append(" JOIN dbo.sys_model_type(NOLOCK) c ON c.type_id = b.model_type_id ");
|
|
|
querySen.Append("WHERE a.serial_number = @SN ");
|
|
|
DynamicParameters para = new DynamicParameters();
|
|
|
para.Add("@SN", sn);
|
|
|
List<SysModel> tmpDT = new List<SysModel>();
|
|
|
try
|
|
|
{
|
|
|
tmpDT = dbConn.Query<SysModel>(querySen.ToString(), para).ToList();
|
|
|
if ((tmpDT == null || tmpDT.Count < 1) && this.IsSerialNumberQuery2History())
|
|
|
{
|
|
|
querySen.Remove(0, querySen.Length);
|
|
|
querySen.Append("SELECT b.model_name , ");
|
|
|
querySen.Append(" c.type_name ");
|
|
|
querySen.Append("FROM dbo.vw_g_sn_status_union(NOLOCK) a ");
|
|
|
querySen.Append(" LEFT JOIN dbo.sys_model(NOLOCK) b ON a.model_id = b.model_id ");
|
|
|
querySen.Append(" JOIN dbo.sys_model_type(NOLOCK) c ON c.type_id = b.model_type_id ");
|
|
|
querySen.Append("WHERE a.serial_number = @SN ");
|
|
|
tmpDT = dbConn.Query<SysModel>(querySen.ToString(), para).ToList();
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
}
|
|
|
return tmpDT;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
///
|
|
|
/// </summary>
|
|
|
/// <param name="sn"></param>
|
|
|
/// <param name="pnInfo"></param>
|
|
|
/// <returns></returns>
|
|
|
public List<SysPart> GetSNPartInfo(string sn)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder querySen = new StringBuilder(1024);
|
|
|
querySen.AppendLine("SELECT a.serial_number , ");
|
|
|
querySen.AppendLine(" b.part_no , ");
|
|
|
querySen.AppendLine(" b.cust_part_no , ");
|
|
|
querySen.AppendLine(" b.part_spec , ");
|
|
|
querySen.AppendLine(" c.enum_desc ");
|
|
|
querySen.AppendLine("FROM dbo.g_sn_status(NOLOCK) a ");
|
|
|
querySen.AppendLine(" JOIN dbo.sys_part(NOLOCK) b ON a.part_id = b.part_id ");
|
|
|
querySen.AppendLine(" LEFT JOIN dbo.sys_enum(NOLOCK) c ON c.enum_value = b.part_location ");
|
|
|
querySen.AppendLine(" AND c.enum_type = 'sys_part_location' ");
|
|
|
querySen.AppendLine("WHERE a.serial_number = @sn ");
|
|
|
|
|
|
DynamicParameters para = new DynamicParameters();
|
|
|
para.Add("@SN", sn);
|
|
|
|
|
|
List<SysPart> tmpDT = new List<SysPart>();
|
|
|
try
|
|
|
{
|
|
|
tmpDT = dbConn.Query<SysPart>(querySen.ToString(), para).ToList();
|
|
|
if ((tmpDT == null || tmpDT.Count < 1) && this.IsSerialNumberQuery2History())
|
|
|
{
|
|
|
querySen.Remove(0, querySen.Length);
|
|
|
querySen.AppendLine("SELECT a.serial_number , ");
|
|
|
querySen.AppendLine(" b.part_no , ");
|
|
|
querySen.AppendLine(" b.cust_part_no , ");
|
|
|
querySen.AppendLine(" b.part_spec , ");
|
|
|
querySen.AppendLine(" c.enum_desc ");
|
|
|
querySen.AppendLine("FROM dbo.vw_g_sn_status_union(NOLOCK) a ");
|
|
|
querySen.AppendLine(" JOIN dbo.sys_part(NOLOCK) b ON a.part_id = b.part_id ");
|
|
|
querySen.AppendLine(" LEFT JOIN dbo.sys_enum(NOLOCK) c ON c.enum_value = b.part_location ");
|
|
|
querySen.AppendLine(" AND c.enum_type = 'sys_part_location' ");
|
|
|
querySen.AppendLine("WHERE a.serial_number = @sn ");
|
|
|
tmpDT = dbConn.Query<SysPart>(querySen.ToString(), para).ToList();
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
|
|
|
}
|
|
|
return tmpDT;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
public List<GSnStatus> GetSNBasicInfo(string sn)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder querySen = new StringBuilder(1024);
|
|
|
querySen.Append("SELECT a.serial_number , ");
|
|
|
querySen.Append(" a.create_time AS ctime , ");
|
|
|
querySen.Append(" CONVERT(varchar(100),a.in_pdline_time,120) as in_pdline_time , ");
|
|
|
querySen.Append(" CONVERT(varchar(100),a.out_pdline_time,120) as out_pdline_time , ");
|
|
|
querySen.Append(" CONVERT(varchar(100),a.shipping_time,120) as shipping_time , ");
|
|
|
querySen.Append(" a.prod_type , ");
|
|
|
querySen.Append(" a.car_no AS shpCarNO , ");
|
|
|
querySen.Append(" a.work_flag , ");
|
|
|
querySen.Append(" a.current_status , ");
|
|
|
querySen.Append(" a.remark , ");
|
|
|
querySen.Append(" c.type as stype , ");
|
|
|
querySen.Append(" d.car_no , ");
|
|
|
querySen.Append(" a.workorder , ");
|
|
|
querySen.Append(" a.slot_no , ");
|
|
|
querySen.Append(" e.pdline_name ");
|
|
|
querySen.Append("FROM ( dbo.g_sn_status(NOLOCK) a ");
|
|
|
querySen.Append(" JOIN dbo.g_workorder_sn(NOLOCK) b ON a.serial_number = b.serial_number ");
|
|
|
querySen.Append(" JOIN dbo.g_workorder_detail(NOLOCK) c ON c.ruid = b.wo_detail_id ");
|
|
|
querySen.Append(" JOIN dbo.g_workorder(NOLOCK) d ON d.ruid = c.workorder_id ");
|
|
|
querySen.Append(" ) ");
|
|
|
querySen.Append(" LEFT JOIN dbo.sys_pdline(NOLOCK) e ON a.pdline_id = e.pdline_id ");
|
|
|
querySen.Append("WHERE a.serial_number = @SN ");
|
|
|
|
|
|
DynamicParameters para = new DynamicParameters();
|
|
|
para.Add("@SN", sn);
|
|
|
|
|
|
List<GSnStatus> tmpDT = new List<GSnStatus>();
|
|
|
try
|
|
|
{
|
|
|
tmpDT = dbConn.Query<GSnStatus>(querySen.ToString(), para).ToList();
|
|
|
if ((tmpDT == null || tmpDT.Count < 1) && this.IsSerialNumberQuery2History())
|
|
|
{
|
|
|
querySen.Remove(0, querySen.Length);
|
|
|
querySen.Append("SELECT a.serial_number , ");
|
|
|
querySen.Append(" a.create_time AS ctime , ");
|
|
|
querySen.Append(" CONVERT(varchar(100),a.in_pdline_time,120) as in_pdline_time , ");
|
|
|
querySen.Append(" CONVERT(varchar(100),a.out_pdline_time,120) as out_pdline_time , ");
|
|
|
querySen.Append(" CONVERT(varchar(100),a.shipping_time,120) as shipping_time , ");
|
|
|
querySen.Append(" a.prod_type , ");
|
|
|
querySen.Append(" a.car_no AS shpCarNO , ");
|
|
|
querySen.Append(" a.work_flag , ");
|
|
|
querySen.Append(" a.current_status , ");
|
|
|
querySen.Append(" a.remark , ");
|
|
|
querySen.Append(" c.type as stype , ");
|
|
|
querySen.Append(" d.car_no , ");
|
|
|
querySen.Append(" a.workorder , ");
|
|
|
querySen.Append(" a.slot_no , ");
|
|
|
querySen.Append(" e.pdline_name ");
|
|
|
querySen.Append("FROM ( dbo.vw_g_sn_status_union a ");
|
|
|
querySen.Append(" JOIN dbo.vw_g_workorder_sn_union b ON a.serial_number = b.serial_number ");
|
|
|
querySen.Append(" JOIN dbo.vw_g_workorder_detail_union c ON c.ruid = b.wo_detail_id ");
|
|
|
querySen.Append(" JOIN dbo.vw_g_workorder_union d ON d.ruid = c.workorder_id ");
|
|
|
querySen.Append(" ) ");
|
|
|
querySen.Append(" LEFT JOIN dbo.sys_pdline(NOLOCK) e ON a.pdline_id = e.pdline_id ");
|
|
|
querySen.Append("WHERE a.serial_number = @SN ");
|
|
|
|
|
|
tmpDT = dbConn.Query<GSnStatus>(querySen.ToString(), para).ToList();
|
|
|
}
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
}
|
|
|
return tmpDT;
|
|
|
}
|
|
|
}
|
|
|
public List<KeyValueResult> GetPDlineInfo()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string Sqlstring = " select pdline_name as [key],pdline_id as [value] from sys_pdline ";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(Sqlstring).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysPartSnRule> getPartSnRule()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("SELECT a.part_id , ");
|
|
|
SqlStringBuilder.Append(" a.from1 , ");
|
|
|
SqlStringBuilder.Append(" a.to1 , ");
|
|
|
SqlStringBuilder.Append(" a.fix1 , ");
|
|
|
SqlStringBuilder.Append(" b.part_no , ");
|
|
|
SqlStringBuilder.Append(" b.part_spec ");
|
|
|
SqlStringBuilder.Append("FROM dbo.sys_part_sn_rule a ");
|
|
|
SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
|
|
|
|
|
|
List<SysPartSnRule> result = dbConn.Query<SysPartSnRule>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|