|
|
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 SerialNumberQueryRepository : BaseRepository<GSnKeyparts>, ISerialNumberQueryRepository
|
|
|
{
|
|
|
public SerialNumberQueryRepository(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();
|
|
|
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> result = dbConn.Query<GSnKeyparts>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<SerialNumberQuery> getTableListByPage(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
|
|
|
WHEN a.work_flag=-1 AND a.current_status=0 THEN '未上线良品'
|
|
|
WHEN a.work_flag=0 AND a.current_status=0 THEN '上线良品'
|
|
|
WHEN a.work_flag=1 AND a.current_status=0 THEN '下线良品'
|
|
|
WHEN a.work_flag=2 AND a.current_status=0 THEN '发运良品'
|
|
|
WHEN a.work_flag=3 AND a.current_status=0 THEN '报废'
|
|
|
WHEN a.work_flag=-1 AND a.current_status=1 THEN '未上线不良'
|
|
|
WHEN a.work_flag=0 AND a.current_status=1 THEN '上线不良'
|
|
|
WHEN a.work_flag=1 AND a.current_status=1 THEN '下线不良'
|
|
|
WHEN a.work_flag=2 AND a.current_status=1 THEN '退回不良'
|
|
|
WHEN a.work_flag=3 AND a.current_status=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<SerialNumberQuery> dt = dbConn.Query<SerialNumberQuery>("Com_Pagination", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
|
|
|
if ((dt == null || dt.Count < 1) && this.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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
|
|
|
WHEN a.work_flag=-1 AND a.current_status=0 THEN '未上线良品'
|
|
|
WHEN a.work_flag=0 AND a.current_status=0 THEN '上线良品'
|
|
|
WHEN a.work_flag=1 AND a.current_status=0 THEN '下线良品'
|
|
|
WHEN a.work_flag=2 AND a.current_status=0 THEN '发运良品'
|
|
|
WHEN a.work_flag=3 AND a.current_status=0 THEN '报废'
|
|
|
WHEN a.work_flag=-1 AND a.current_status=1 THEN '未上线不良'
|
|
|
WHEN a.work_flag=0 AND a.current_status=1 THEN '上线不良'
|
|
|
WHEN a.work_flag=1 AND a.current_status=1 THEN '下线不良'
|
|
|
WHEN a.work_flag=2 AND a.current_status=1 THEN '退回不良'
|
|
|
WHEN a.work_flag=3 AND a.current_status=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());
|
|
|
dt = dbConn.Query<SerialNumberQuery>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
}
|
|
|
|
|
|
totalCount = dt.Count;
|
|
|
return dt;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <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
|
|
|
WHEN a.work_flag=-1 AND a.current_status=0 THEN '未上线良品'
|
|
|
WHEN a.work_flag=0 AND a.current_status=0 THEN '上线良品'
|
|
|
WHEN a.work_flag=1 AND a.current_status=0 THEN '下线良品'
|
|
|
WHEN a.work_flag=2 AND a.current_status=0 THEN '发运良品'
|
|
|
WHEN a.work_flag=3 AND a.current_status=0 THEN '报废'
|
|
|
WHEN a.work_flag=-1 AND a.current_status=1 THEN '未上线不良'
|
|
|
WHEN a.work_flag=0 AND a.current_status=1 THEN '上线不良'
|
|
|
WHEN a.work_flag=1 AND a.current_status=1 THEN '下线不良'
|
|
|
WHEN a.work_flag=2 AND a.current_status=1 THEN '退回不良'
|
|
|
WHEN a.work_flag=3 AND a.current_status=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> dt = dbConn.Query<GSnStatus>("Com_Pagination", parameter, commandType: CommandType.StoredProcedure).ToList();
|
|
|
|
|
|
if ((dt == null || dt.Count < 1) && this.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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
|
|
|
WHEN a.work_flag=-1 AND a.current_status=0 THEN '未上线良品'
|
|
|
WHEN a.work_flag=0 AND a.current_status=0 THEN '上线良品'
|
|
|
WHEN a.work_flag=1 AND a.current_status=0 THEN '下线良品'
|
|
|
WHEN a.work_flag=2 AND a.current_status=0 THEN '发运良品'
|
|
|
WHEN a.work_flag=3 AND a.current_status=0 THEN '报废'
|
|
|
WHEN a.work_flag=-1 AND a.current_status=1 THEN '未上线不良'
|
|
|
WHEN a.work_flag=0 AND a.current_status=1 THEN '上线不良'
|
|
|
WHEN a.work_flag=1 AND a.current_status=1 THEN '下线不良'
|
|
|
WHEN a.work_flag=2 AND a.current_status=1 THEN '退回不良'
|
|
|
WHEN a.work_flag=3 AND a.current_status=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());
|
|
|
dt = dbConn.Query<GSnStatus>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
}
|
|
|
|
|
|
totalCount = dt.Count;
|
|
|
return dt;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public string kpsnGetSN(string sn)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("SELECT TOP 1 serial_number FROM dbo.g_sn_keyparts WHERE kpsn ='" + sn + "' ");
|
|
|
|
|
|
object obj = dbConn.ExecuteScalar(SqlStringBuilder.ToString());
|
|
|
return obj.ToString();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 关键数据查询是否查询历史表数据
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public bool IsSerialNumberQueryHistory()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT TOP 1 param_value FROM dbo.sys_base(NOLOCK) WHERE param_name='IsSerialNumberQueryHistory'";
|
|
|
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();
|
|
|
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_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
|
|
|
WHEN a.work_flag=-1 AND a.current_status=0 THEN '未上线良品'
|
|
|
WHEN a.work_flag=0 AND a.current_status=0 THEN '上线良品'
|
|
|
WHEN a.work_flag=1 AND a.current_status=0 THEN '下线良品'
|
|
|
WHEN a.work_flag=2 AND a.current_status=0 THEN '发运良品'
|
|
|
WHEN a.work_flag=3 AND a.current_status=0 THEN '报废'
|
|
|
WHEN a.work_flag=-1 AND a.current_status=1 THEN '未上线不良'
|
|
|
WHEN a.work_flag=0 AND a.current_status=1 THEN '上线不良'
|
|
|
WHEN a.work_flag=1 AND a.current_status=1 THEN '下线不良'
|
|
|
WHEN a.work_flag=2 AND a.current_status=1 THEN '退回不良'
|
|
|
WHEN a.work_flag=3 AND a.current_status=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> depts = dbConn.Query<GSnTravel>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <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);
|
|
|
|
|
|
StringBuilder SqlStringBuilder1 = new StringBuilder(1024);
|
|
|
SqlStringBuilder1.Append(" g_sn_keydata (NOLOCK) a ");
|
|
|
SqlStringBuilder1.Append(" LEFT JOIN sys_emp (NOLOCK) b ON a.create_userid = b.emp_id ");
|
|
|
SqlStringBuilder1.Append(" LEFT JOIN dbo.sys_keydata AS c ON a.keydata_desc = c.keydata_desc ");
|
|
|
SqlStringBuilder1.Append(" LEFT JOIN dbo.sys_terminal AS d ON a.terminal_id = d.terminal_id ");
|
|
|
|
|
|
|
|
|
parameters.Add("@Table", SqlStringBuilder1.ToString());
|
|
|
|
|
|
StringBuilder SqlStringBuilder2 = new StringBuilder(1024);
|
|
|
SqlStringBuilder2.Append(" a.keydata_desc AS name , ");
|
|
|
SqlStringBuilder2.Append(" a.serial_number , ");
|
|
|
SqlStringBuilder2.Append(" a.keydata_desc , ");
|
|
|
SqlStringBuilder2.Append(" a.keydata_value , ");
|
|
|
SqlStringBuilder2.Append(" CONVERT(VARCHAR, min_value) + '-' + CONVERT(VARCHAR, max_value) AS value , ");
|
|
|
SqlStringBuilder2.Append(" a.create_time , ");
|
|
|
SqlStringBuilder2.Append(" b.emp_name , ");
|
|
|
SqlStringBuilder2.Append(" d.terminal_name ");
|
|
|
|
|
|
parameters.Add("@Column", SqlStringBuilder2.ToString());
|
|
|
parameters.Add("@OrderColumn", " a.terminal_id ");
|
|
|
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());
|
|
|
Hashtable values = new Hashtable(2);
|
|
|
List<GSnKeydata> depts = dbConn.Query<GSnKeydata>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysKeydata> DTtemp(string sn)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("SELECT keydata_name AS name , ");
|
|
|
SqlStringBuilder.Append(" CONVERT(VARCHAR, min_value) + '-' + CONVERT(VARCHAR, max_value) AS value,keydata_id ");
|
|
|
SqlStringBuilder.Append("FROM dbo.sys_keydata ");
|
|
|
SqlStringBuilder.Append("WHERE keydata_id IN ( ");
|
|
|
SqlStringBuilder.Append(" SELECT keydata_id ");
|
|
|
SqlStringBuilder.Append(" FROM dbo.sys_part_keydata ");
|
|
|
SqlStringBuilder.Append(" WHERE part_id = ( SELECT TOP 1 ");
|
|
|
SqlStringBuilder.Append(" part_id FROM dbo.g_sn_status ");
|
|
|
SqlStringBuilder.Append(" WHERE serial_number = '" + sn + "' ");
|
|
|
SqlStringBuilder.Append(" UNION ");
|
|
|
SqlStringBuilder.Append(" SELECT TOP 1 part_id ");
|
|
|
SqlStringBuilder.Append(" FROM dbo.g_ht_sn_status ");
|
|
|
SqlStringBuilder.Append(" WHERE serial_number = '" + sn + "' ) ) ");
|
|
|
SqlStringBuilder.Append(" AND keydata_name NOT LIKE '%气囊%' AND keydata_name NOT LIKE '%铰链%' AND keydata_name NOT LIKE '%卷簧%' ");
|
|
|
SqlStringBuilder.Append("UNION ALL ");
|
|
|
SqlStringBuilder.Append("SELECT keydata_name AS name , ");
|
|
|
SqlStringBuilder.Append(" CONVERT(VARCHAR, min_value) + '-' + CONVERT(VARCHAR, max_value) AS value,keydata_id ");
|
|
|
SqlStringBuilder.Append("FROM dbo.sys_keydata ");
|
|
|
SqlStringBuilder.Append("WHERE keydata_id IN ( ");
|
|
|
SqlStringBuilder.Append(" SELECT keydata_id ");
|
|
|
SqlStringBuilder.Append(" FROM dbo.sys_part_keydata ");
|
|
|
SqlStringBuilder.Append(" WHERE part_id = ( SELECT part_id ");
|
|
|
SqlStringBuilder.Append(" FROM dbo.g_sn_status ");
|
|
|
SqlStringBuilder.Append(" WHERE serial_number = '" + sn + "' ");
|
|
|
SqlStringBuilder.Append(" UNION ");
|
|
|
SqlStringBuilder.Append(" SELECT TOP 1 part_id ");
|
|
|
SqlStringBuilder.Append(" FROM dbo.g_ht_sn_status ");
|
|
|
SqlStringBuilder.Append(" WHERE serial_number = '" + sn + "' ) ) ");
|
|
|
SqlStringBuilder.Append(" AND (keydata_name LIKE '%气囊%' OR keydata_name LIKE '%铰链%' OR keydata_name LIKE '%卷簧%') ORDER BY sys_keydata.keydata_id ");
|
|
|
|
|
|
List<SysKeydata> result = dbConn.Query<SysKeydata>(SqlStringBuilder.ToString()).ToList();
|
|
|
return result == null ? null : 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", @" (
|
|
|
select TOP 100 a.serial_number, a.kpsn ,b.part_no,b.part_spec,c.terminal_name, a.create_time
|
|
|
from g_sn_keyparts(NOLOCK) a
|
|
|
LEFT JOIN sys_part(NOLOCK) b ON a.item_part_id=b.part_id LEFT JOIN dbo.sys_terminal(NOLOCK) c ON a.terminal_id =c.terminal_id where 1=1 and a." + whereStr +
|
|
|
@" UNION
|
|
|
select TOP 100 a.serial_number, a.kpsn ,b.part_no,b.part_spec,c.terminal_name, a.create_time
|
|
|
from g_sn_keyparts(NOLOCK) a
|
|
|
LEFT JOIN sys_part(NOLOCK) b ON a.item_part_id=b.part_id LEFT JOIN dbo.sys_terminal(NOLOCK) c ON a.terminal_id =c.terminal_id where a.serial_number IN(select kpsn from g_sn_keyparts ");
|
|
|
parameters.Add("@Column", @" serial_number, kpsn ,part_no,part_spec,terminal_name, create_time ");
|
|
|
parameters.Add("@OrderColumn", " 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 and " + whereStr + " )) t");
|
|
|
parameters.Add("@Condition", SqlStringBuilder.ToString());
|
|
|
Hashtable values = new Hashtable(2);
|
|
|
List<GSnKeyparts> depts = dbConn.Query<GSnKeyparts>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <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());
|
|
|
Hashtable values = new Hashtable(2);
|
|
|
List<GSnDefect> depts = dbConn.Query<GSnDefect>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
|
|
|
totalCount = parameters.Get<int>("@TotalCount");
|
|
|
return depts;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
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.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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)
|
|
|
{
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
public List<GSnKeyparts> valiDationKPSN(string strKPSN)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sqlString = "select * from g_sn_keydata(NOLOCK) where serial_number = '" + strKPSN + "'";
|
|
|
List<GSnKeyparts> keyparts = dbConn.Query<GSnKeyparts>(sqlString).ToList();
|
|
|
return keyparts;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <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.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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 ");
|
|
|
|
|
|
Hashtable para = new Hashtable(1);
|
|
|
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.IsSerialNumberQueryHistory())
|
|
|
{
|
|
|
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<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;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
}
|
|
|
}
|