zhengxuan.zhang
08e623410f
新到文件是否仍然正常出现 匹配成功 -> 待解析区 -> 解析成功 -> 正式区 -> 双侧完成 -> 客户报告生成。 历史遗留文件是否出现 已补充分发到正式区 这类新日志，并从源目录消失。 已经真正分发完成的文件，是否仍然保持 已处理且目标区域已存在，跳过，没有重复搬运。
634 lines
25 KiB
C#
634 lines
25 KiB
C#
using NSAnalysis.Model;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Text;
|
|
|
|
namespace NSAnalysis.DAL
|
|
{
|
|
public class CjlrDAL
|
|
{
|
|
#region Select Function
|
|
|
|
public int SelectTMeasureResultCount()
|
|
{
|
|
string strSql = "select COUNT(*) from TMeasureResult";
|
|
DataTable dt = SQLHelper.ExecuteQuery(strSql, CommandType.Text);
|
|
return int.Parse(dt.Rows[0][0].ToString());
|
|
}
|
|
|
|
public DataTable SelectNewestTMeasureResult()
|
|
{
|
|
string strSql = "select top(1) Id,CarID,MeasureDate,Remark from TMeasureResult order by MeasureDate DESC";
|
|
DataTable dt = SQLHelper.ExecuteQuery(strSql, CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable SelectCJLRMeaDataByCarID(string strCarID)
|
|
{
|
|
string strSql = "select * from CJLR_MeaData where ProductNum ='" + strCarID + "'";
|
|
DataTable dt = SQLHelper.ExecuteQuery(strSql, CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
// 查询指定车辆ID的测量结果
|
|
public DataTable SelectTMeasureResultByTime(string strCarID, string strStartTime, string strEndTime)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select CarID,SumMeasureItems,GoodMeasureItems,NoGoodMeasureItems,RejectMeasureItems,FPY,MeasureDate,case Result when 1 then '合格' else '不合格' end as Result from TMeasureResult where CarID like '%" + strCarID + "%' and MeasureDate >= '" + strStartTime + "' and MeasureDate <= '" + strEndTime + "'";
|
|
dt = SQLHelper.ExecuteQuery(strSql, CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable SelectTaskByCondition(string strModelName, string strModelCode, string strStatus)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
StringBuilder strSql = new StringBuilder(@"SELECT id, modelsName, modelsCode, position,
|
|
sourceFile, targetFile, status, create_date,
|
|
readType
|
|
FROM CJLR.dbo.CJLR_TASK_RELEASE
|
|
WHERE is_delete = 1 "); // 默认只查询未删除记录，避免历史 readType 数据导致任务被遗漏
|
|
|
|
List<SqlParameter> paras = new List<SqlParameter>();
|
|
|
|
if (!string.IsNullOrEmpty(strModelName))
|
|
{
|
|
strSql.Append(" AND modelsName LIKE '%' + @ModelName + '%'");
|
|
paras.Add(new SqlParameter("@ModelName", strModelName));
|
|
}
|
|
|
|
if (!string.IsNullOrEmpty(strModelCode))
|
|
{
|
|
strSql.Append(" AND modelsCode LIKE '%' + @ModelCode + '%'");
|
|
paras.Add(new SqlParameter("@ModelCode", strModelCode));
|
|
}
|
|
|
|
// 状态查询优化
|
|
if (!string.IsNullOrEmpty(strStatus) && strStatus != "all")
|
|
{
|
|
strSql.Append(" AND status = @Status");
|
|
paras.Add(new SqlParameter("@Status", strStatus));
|
|
}
|
|
|
|
// 增加 ORDER BY create_date DESC
|
|
strSql.Append(" ORDER BY create_date DESC");
|
|
|
|
dt = SQLHelper.ExecuteQuery(strSql.ToString(), paras.ToArray(), CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
// 判断 CJLR_TASK_RELEASE 是否存在指定的任务
|
|
public bool CheckTaskExit(string strModelsName, string strModelsCode, string strReadType)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
|
|
// 构建 SQL 查询语句
|
|
string strSql = $"SELECT Id FROM CJLR.dbo.CJLR_TASK_RELEASE " +
|
|
$"WHERE modelsName = '{strModelsName}' " +
|
|
$"AND modelsCode = '{strModelsCode}' " +
|
|
$"AND readType = {(strReadType)}";
|
|
|
|
// 执行查询
|
|
dt = SQLHelper.ExecuteQuery(strSql, CommandType.Text);
|
|
|
|
// 检查结果
|
|
return dt.Rows.Count > 0; // 任务存在返回 true，否则返回 false
|
|
}
|
|
|
|
// 从CJLR_MeaData 中查询 总测量项 Good测量项 NoGood测量项 Reject测量项 FPY
|
|
public DataTable SelectMeasureResultByCarID(string strCarID, string strGroupName)
|
|
{
|
|
string strSql = @"
|
|
SELECT
|
|
ProductNum AS CarID,
|
|
COUNT(*) AS TotalCount,
|
|
SUM(CASE WHEN UPPER(Classification) IN ('OK','OK ','Acceptable','Acceptable ') THEN 1 ELSE 0 END) AS PassCount,
|
|
SUM(CASE WHEN UPPER(Classification) IN ('NG1','NG1 ','NG2','NG2 ') THEN 1 ELSE 0 END) AS NGCount,
|
|
SUM(CASE WHEN Classification = 'Rejected' THEN 1 ELSE 0 END) AS RejectCount,
|
|
CAST(
|
|
SUM(CASE WHEN UPPER(Classification) IN ('OK','OK ','Acceptable','Acceptable ') THEN 1 ELSE 0 END) AS FLOAT
|
|
) /
|
|
|
|
NULLIF(
|
|
SUM(CASE WHEN UPPER(Classification) IN ('NG1','NG1 ','NG2','NG2 ','OK','OK ','ACCEPTABLE','ACCEPTABLE ','Rejected') THEN 1 ELSE 0 END),
|
|
0
|
|
) * 100 AS FPY
|
|
|
|
FROM CJLR.dbo.CJLR_MeaData
|
|
WHERE ProductNum = @CarID and GroupName = @GroupName
|
|
GROUP BY ProductNum
|
|
|
|
";
|
|
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@CarID", strCarID),
|
|
new SqlParameter("@GroupName", strGroupName)
|
|
};
|
|
|
|
DataTable dt = SQLHelper.ExecuteQuery(strSql, parameters, CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable SelectMeasureResultByCarID(string strCarID)
|
|
{
|
|
string strSql = @"
|
|
SELECT
|
|
ProductNum AS CarID,
|
|
COUNT(*) AS TotalCount,
|
|
SUM(CASE WHEN UPPER(Classification) IN ('OK','OK ','Acceptable','Acceptable ') THEN 1 ELSE 0 END) AS PassCount,
|
|
SUM(CASE WHEN UPPER(Classification) IN ('NG1','NG1 ','NG2','NG2 ') THEN 1 ELSE 0 END) AS NGCount,
|
|
SUM(CASE WHEN Classification = 'Rejected' THEN 1 ELSE 0 END) AS RejectCount,
|
|
CAST(
|
|
SUM(CASE WHEN UPPER(Classification) IN ('OK','OK ','Acceptable','Acceptable ') THEN 1 ELSE 0 END) AS FLOAT
|
|
) /
|
|
|
|
NULLIF(
|
|
SUM(CASE WHEN UPPER(Classification) IN ('NG1','NG1 ','NG2','NG2 ','OK','OK ','ACCEPTABLE','ACCEPTABLE ','Rejected') THEN 1 ELSE 0 END),
|
|
0
|
|
) * 100 AS FPY
|
|
|
|
FROM CJLR.dbo.CJLR_MeaData
|
|
WHERE ProductNum = @CarID
|
|
GROUP BY ProductNum
|
|
|
|
";
|
|
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@CarID", strCarID)
|
|
};
|
|
|
|
DataTable dt = SQLHelper.ExecuteQuery(strSql, parameters, CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
// 每个测量点的上下限和偏差分析（包含合并的测量时间）
|
|
public DataTable SelectMeasureDataByCarID(string strCarID)
|
|
{
|
|
string strSql = @"SELECT
|
|
PointName,
|
|
DimensionName,
|
|
DimensionValue,
|
|
NominalValue,
|
|
-- 计算上下限
|
|
|
|
ToleranceLower0 AS LowerLimit,
|
|
ToleranceUpper0 AS UpperLimit,
|
|
Classification,
|
|
-- 合并日期和时间
|
|
CAST(MeasureDate AS DATETIME) + CAST(MeasureTime AS DATETIME) AS MeasureDateTime
|
|
FROM CJLR.dbo.CJLR_MeaData
|
|
WHERE ProductNum = @ProductNum
|
|
ORDER BY MeasureDateTime";
|
|
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@ProductNum", strCarID),
|
|
};
|
|
|
|
DataTable dt = SQLHelper.ExecuteQuery(strSql, parameters, CommandType.Text);
|
|
return dt;
|
|
}
|
|
|
|
// 获取特定车辆ID和时间范围内的测量数据
|
|
public DataTable SelectTMeasureDataByCarIDAndTime(string strCarID, string strStartTime, string strEndTime)
|
|
{
|
|
StringBuilder strSql = new StringBuilder(@"
|
|
SELECT
|
|
ProductNum,
|
|
Model,
|
|
PointName,
|
|
DimensionName,
|
|
NominalValue,
|
|
ToleranceLower0 AS LowerLimit,
|
|
ToleranceUpper0 AS UpperLimit,
|
|
DimensionValue,
|
|
Classification,
|
|
CAST(MeasureDate AS DATETIME) + CAST(MeasureTime AS DATETIME) AS MeasureDateTime
|
|
FROM CJLR.dbo.CJLR_MeaData
|
|
WHERE 1=1
|
|
");
|
|
|
|
List<SqlParameter> parameters = new List<SqlParameter>();
|
|
|
|
if (!string.IsNullOrWhiteSpace(strCarID))
|
|
{
|
|
strSql.Append(" AND ProductNum = @ProductNum");
|
|
parameters.Add(new SqlParameter("@ProductNum", strCarID));
|
|
}
|
|
|
|
strSql.Append(@"
|
|
AND (CAST(MeasureDate AS datetime) + CAST(MeasureTime AS datetime)) >= @StartTime
|
|
AND (CAST(MeasureDate AS datetime) + CAST(MeasureTime AS datetime)) <= @EndTime
|
|
ORDER BY MeasureDate, MeasureTime
|
|
");
|
|
|
|
parameters.Add(new SqlParameter("@StartTime", DateTime.Parse(strStartTime)));
|
|
parameters.Add(new SqlParameter("@EndTime", DateTime.Parse(strEndTime)));
|
|
|
|
return SQLHelper.ExecuteQuery(strSql.ToString(), parameters.ToArray(), CommandType.Text);
|
|
}
|
|
|
|
// check 是否有左右两侧的测量结果
|
|
public bool HasBothSidesMeasureResult(string carId)
|
|
{
|
|
if (string.IsNullOrEmpty(carId))
|
|
return false;
|
|
|
|
string sql = @"
|
|
SELECT CASE
|
|
WHEN COUNT(DISTINCT Remark) = 2 THEN 1
|
|
ELSE 0
|
|
END AS HasBothSides
|
|
FROM CJLR.dbo.TMeasureResult
|
|
WHERE CarID = @CarID AND Remark IN ('L', 'R')";
|
|
SqlParameter[] paras = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@CarID", carId)
|
|
};
|
|
DataTable dt = SQLHelper.ExecuteQuery(sql, paras, CommandType.Text);
|
|
// 防御性检查：空表或列不存在时返回 false，避免 Column 'HasBothSides' does not belong to table 异常
|
|
if (dt == null || dt.Rows.Count == 0 || !dt.Columns.Contains("HasBothSides"))
|
|
return false;
|
|
return Convert.ToInt32(dt.Rows[0]["HasBothSides"]) == 1;
|
|
}
|
|
|
|
// 查询指定车辆ID的测量点维度数据
|
|
public DataTable SelectPointDimensionByCarID(string carId)
|
|
{
|
|
string sql = @"
|
|
SELECT PointName, DimensionName, DimensionValue
|
|
FROM CJLR.dbo.CJLR_MeaData
|
|
WHERE ProductNum = @CarID
|
|
ORDER BY Id";
|
|
SqlParameter[] paras = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@CarID", carId)
|
|
};
|
|
return SQLHelper.ExecuteQuery(sql, paras, CommandType.Text);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Insert Function
|
|
|
|
// 插入分发配置
|
|
public int InsertTask(CjlrTaskReleaseModel model)
|
|
{
|
|
const string strSql = @"INSERT INTO CJLR.dbo.CJLR_TASK_RELEASE
|
|
(modelsName, modelsCode, position, sourceFile, targetFile,
|
|
status, create_date, is_delete, readType)
|
|
VALUES
|
|
(@modelsName, @modelsCode, @position, @sourceFile, @targetFile,
|
|
@status, @create_date, @is_delete, @readType);
|
|
SELECT SCOPE_IDENTITY();";
|
|
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@modelsName", model.ModelsName ?? (object)DBNull.Value),
|
|
new SqlParameter("@modelsCode", model.ModelsCode ?? (object)DBNull.Value),
|
|
new SqlParameter("@position", model.Position ?? (object)DBNull.Value),
|
|
new SqlParameter("@sourceFile", model.SourceFile ?? (object)DBNull.Value),
|
|
new SqlParameter("@targetFile", model.TargetFile ?? (object)DBNull.Value),
|
|
new SqlParameter("@status", model.Status ?? (object)DBNull.Value),
|
|
new SqlParameter("@create_date", model.CreateDate == DateTime.MinValue ?
|
|
DateTime.Parse("2024-01-31 14:37:00"): model.CreateDate),
|
|
new SqlParameter("@is_delete", model.IsDelete),
|
|
new SqlParameter("@readType", model.ReadType)
|
|
};
|
|
|
|
object result = SQLHelper.ExecuteNonQuery(strSql, parameters, CommandType.Text);
|
|
return Convert.ToInt32(result);
|
|
}
|
|
|
|
// 插入分发详细记录
|
|
public int InsertTaskDetail(CjlrTaskReleaseDetailModel model)
|
|
{
|
|
const string strSql = @"
|
|
INSERT INTO CJLR.dbo.CJLR_TASK_RELEASE_DETAIL
|
|
(modelsName, modelsCode, position, sourceFile, targetFile,
|
|
taskFileName, taskStatus, taskDetail, createDate)
|
|
VALUES (@modelsName, @modelsCode, @position, @sourceFile, @targetFile,
|
|
@taskFileName, @taskStatus, @taskDetail, @createDate)";
|
|
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@modelsName", model.ModelsName ?? (object)DBNull.Value),
|
|
new SqlParameter("@modelsCode", model.ModelsCode ?? (object)DBNull.Value),
|
|
new SqlParameter("@position", model.Position ?? (object)DBNull.Value),
|
|
new SqlParameter("@sourceFile", model.SourceFile ?? (object)DBNull.Value),
|
|
new SqlParameter("@targetFile", model.TargetFile ?? (object)DBNull.Value),
|
|
new SqlParameter("@taskFileName", model.TaskFileName ?? (object)DBNull.Value),
|
|
new SqlParameter("@taskStatus", model.TaskStatus),
|
|
new SqlParameter("@taskDetail", model.TaskDetail ?? (object)DBNull.Value),
|
|
new SqlParameter("@createDate", model.CreateDate == DateTime.MinValue ? DateTime.Now : model.CreateDate)
|
|
};
|
|
|
|
object result = SQLHelper.ExecuteNonQuery(strSql, parameters, CommandType.Text);
|
|
return Convert.ToInt32(result);
|
|
}
|
|
|
|
public int InsertTMeasureResult(TMeasureResultModel tmrm)
|
|
{
|
|
string strSql = "insert into TMeasureResult (CarID,CarType,SumMeasureItems,GoodMeasureItems,NoGoodMeasureItems,RejectMeasureItems,FPY,MeasureDate,Result,Remark) values " +
|
|
"(@CarID,@CarType,@SumMeasureItems,@GoodMeasureItems,@NoGoodMeasureItems,@RejectMeasureItems,@FPY,@MeasureDate,@Result,@Remark)";
|
|
SqlParameter[] paras = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@CarID",tmrm.CarID),
|
|
new SqlParameter("@CarType",tmrm.CarType),
|
|
new SqlParameter("@SumMeasureItems",tmrm.SumMeasureItems),
|
|
new SqlParameter("@GoodMeasureItems",tmrm.GoodMeasureItems),
|
|
new SqlParameter("@NoGoodMeasureItems",tmrm.NoGoodMeasureItems),
|
|
new SqlParameter("@RejectMeasureItems",tmrm.RejectMeasureItems),
|
|
new SqlParameter("@FPY",tmrm.FPY),
|
|
new SqlParameter("@MeasureDate",tmrm.MeasureDate),
|
|
new SqlParameter("@Result",tmrm.Result),
|
|
new SqlParameter("@Remark",tmrm.Remark),
|
|
};
|
|
return SQLHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
// 插入原始测量数据记录
|
|
public int InsertCJLRMeaData(CJLR_MeaDataModel record)
|
|
{
|
|
string strSql = @"
|
|
INSERT INTO CJLR_MeaData (
|
|
PointName, GroupName, ProductNum, Model, Station, Method,
|
|
Standard, DimensionName, DimensionValue, DimensionUnit,
|
|
IsManual, Classification, ToleranceName0,
|
|
ToleranceLower0, ToleranceUpper0, ToleranceName1,
|
|
ToleranceLower1, ToleranceUpper1, NominalValue, MeasureDate, MeasureTime, SequenceNum,CreatedAt
|
|
) VALUES (
|
|
@MeasPointName, @MeasGroupName, @ProdNum, @Model, @Station, @MeasMethod,
|
|
@TestStandard, @DimensionName, @DimensionValue, @DimensionUnit,
|
|
@DimensionManualOverride, @DimensionClassification, @ToleranceName0,
|
|
@ToleranceLower0, @ToleranceUpper0, @ToleranceName1,
|
|
@ToleranceLower1, @ToleranceUpper1, @MeasPointNominal, @Date, @Time, @SeqNr,@CreatedAt
|
|
)";
|
|
|
|
SqlParameter[] paras = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@MeasPointName", record.PointName ?? (object)DBNull.Value),
|
|
new SqlParameter("@MeasGroupName", record.GroupName ?? (object)DBNull.Value),
|
|
new SqlParameter("@ProdNum", record.ProductNum ?? (object)DBNull.Value),
|
|
new SqlParameter("@Model", record.Model ?? (object)DBNull.Value),
|
|
new SqlParameter("@Station", record.Station ?? (object)DBNull.Value),
|
|
new SqlParameter("@MeasMethod", record.Method ?? (object)DBNull.Value),
|
|
new SqlParameter("@TestStandard", record.Standard ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionName", record.DimensionName ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionValue", record.DimensionValue),
|
|
new SqlParameter("@DimensionUnit", record.DimensionUnit ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionManualOverride", record.IsManual ? 1 : 0),
|
|
new SqlParameter("@DimensionClassification", record.Classification ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceName0", record.ToleranceName0 ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceLower0", record.ToleranceLower0),
|
|
new SqlParameter("@ToleranceUpper0", record.ToleranceUpper0),
|
|
new SqlParameter("@ToleranceName1", record.ToleranceName1 ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceLower1", record.ToleranceLower1),
|
|
new SqlParameter("@ToleranceUpper1", record.ToleranceUpper1),
|
|
new SqlParameter("@MeasPointNominal", record.NominalValue),
|
|
new SqlParameter("@Date", record.MeasureDate == DateTime.MinValue ? DateTime.Now : record.MeasureDate),
|
|
new SqlParameter("@Time", record.MeasureTime == TimeSpan.Zero ? DateTime.Now.TimeOfDay : record.MeasureTime),
|
|
new SqlParameter("@SeqNr", record.SequenceNum),
|
|
new SqlParameter("@CreatedAt", DateTime.Now )
|
|
};
|
|
|
|
return SQLHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
public int InsertOrUpdateCJLRMeaData(CJLR_MeaDataModel record)
|
|
{
|
|
// 判断是否存在
|
|
string checkSql = @"
|
|
SELECT Id FROM CJLR_MeaData
|
|
WHERE ProductNum = @ProdNum
|
|
AND PointName = @MeasPointName
|
|
AND DimensionName = @DimensionName
|
|
AND MeasureDate = @Date
|
|
AND MeasureTime = @Time";
|
|
SqlParameter[] checkParas = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@ProdNum", record.ProductNum),
|
|
new SqlParameter("@MeasPointName", record.PointName),
|
|
new SqlParameter("@DimensionName", record.DimensionName),
|
|
new SqlParameter("@Date", record.MeasureDate),
|
|
new SqlParameter("@Time", record.MeasureTime)
|
|
};
|
|
DataTable dt = SQLHelper.ExecuteQuery(checkSql, checkParas, CommandType.Text);
|
|
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
// 已存在，执行更新
|
|
int id = Convert.ToInt32(dt.Rows[0]["Id"]);
|
|
string updateSql = @"
|
|
UPDATE CJLR_MeaData SET
|
|
GroupName = @MeasGroupName,
|
|
Model = @Model,
|
|
Station = @Station,
|
|
Method = @MeasMethod,
|
|
Standard = @TestStandard,
|
|
DimensionValue = @DimensionValue,
|
|
DimensionUnit = @DimensionUnit,
|
|
IsManual = @DimensionManualOverride,
|
|
Classification = @DimensionClassification,
|
|
ToleranceName0 = @ToleranceName0,
|
|
ToleranceLower0 = @ToleranceLower0,
|
|
ToleranceUpper0 = @ToleranceUpper0,
|
|
ToleranceName1 = @ToleranceName1,
|
|
ToleranceLower1 = @ToleranceLower1,
|
|
ToleranceUpper1 = @ToleranceUpper1,
|
|
NominalValue = @MeasPointNominal,
|
|
SequenceNum = @SeqNr,
|
|
CreatedAt = @CreatedAt
|
|
WHERE Id = @Id";
|
|
SqlParameter[] updateParas = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@MeasGroupName", record.GroupName ?? (object)DBNull.Value),
|
|
new SqlParameter("@Model", record.Model ?? (object)DBNull.Value),
|
|
new SqlParameter("@Station", record.Station ?? (object)DBNull.Value),
|
|
new SqlParameter("@MeasMethod", record.Method ?? (object)DBNull.Value),
|
|
new SqlParameter("@TestStandard", record.Standard ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionValue", record.DimensionValue),
|
|
new SqlParameter("@DimensionUnit", record.DimensionUnit ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionManualOverride", record.IsManual ? 1 : 0),
|
|
new SqlParameter("@DimensionClassification", record.Classification ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceName0", record.ToleranceName0 ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceLower0", record.ToleranceLower0),
|
|
new SqlParameter("@ToleranceUpper0", record.ToleranceUpper0),
|
|
new SqlParameter("@ToleranceName1", record.ToleranceName1 ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceLower1", record.ToleranceLower1),
|
|
new SqlParameter("@ToleranceUpper1", record.ToleranceUpper1),
|
|
new SqlParameter("@MeasPointNominal", record.NominalValue),
|
|
new SqlParameter("@SeqNr", record.SequenceNum),
|
|
new SqlParameter("@CreatedAt", DateTime.Now),
|
|
new SqlParameter("@Id", id)
|
|
};
|
|
return SQLHelper.ExecuteNonQuery(updateSql, updateParas, CommandType.Text);
|
|
}
|
|
else
|
|
{
|
|
// 不存在，执行插入
|
|
string insertSql = @"
|
|
INSERT INTO CJLR_MeaData (
|
|
PointName, GroupName, ProductNum, Model, Station, Method,
|
|
Standard, DimensionName, DimensionValue, DimensionUnit,
|
|
IsManual, Classification, ToleranceName0,
|
|
ToleranceLower0, ToleranceUpper0,ToleranceName1,
|
|
ToleranceLower1, ToleranceUpper1, NominalValue, MeasureDate, MeasureTime, SequenceNum,CreatedAt
|
|
) VALUES (
|
|
@MeasPointName, @MeasGroupName, @ProdNum, @Model, @Station, @MeasMethod,
|
|
@TestStandard, @DimensionName, @DimensionValue, @DimensionUnit,
|
|
@DimensionManualOverride, @DimensionClassification, @ToleranceName0,
|
|
@ToleranceLower0, @ToleranceUpper0,@ToleranceName1,
|
|
@ToleranceLower1, @ToleranceUpper1,@MeasPointNominal, @Date, @Time, @SeqNr,@CreatedAt
|
|
)";
|
|
SqlParameter[] insertParas = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@MeasPointName", record.PointName ?? (object)DBNull.Value),
|
|
new SqlParameter("@MeasGroupName", record.GroupName ?? (object)DBNull.Value),
|
|
new SqlParameter("@ProdNum", record.ProductNum ?? (object)DBNull.Value),
|
|
new SqlParameter("@Model", record.Model ?? (object)DBNull.Value),
|
|
new SqlParameter("@Station", record.Station ?? (object)DBNull.Value),
|
|
new SqlParameter("@MeasMethod", record.Method ?? (object)DBNull.Value),
|
|
new SqlParameter("@TestStandard", record.Standard ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionName", record.DimensionName ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionValue", record.DimensionValue),
|
|
new SqlParameter("@DimensionUnit", record.DimensionUnit ?? (object)DBNull.Value),
|
|
new SqlParameter("@DimensionManualOverride", record.IsManual ? 1 : 0),
|
|
new SqlParameter("@DimensionClassification", record.Classification ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceName0", record.ToleranceName0 ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceLower0", record.ToleranceLower0),
|
|
new SqlParameter("@ToleranceUpper0", record.ToleranceUpper0),
|
|
new SqlParameter("@ToleranceName1", record.ToleranceName1 ?? (object)DBNull.Value),
|
|
new SqlParameter("@ToleranceLower1", record.ToleranceLower1),
|
|
new SqlParameter("@ToleranceUpper1", record.ToleranceUpper1),
|
|
new SqlParameter("@MeasPointNominal", record.NominalValue),
|
|
new SqlParameter("@Date", record.MeasureDate == DateTime.MinValue ? DateTime.Now : record.MeasureDate),
|
|
new SqlParameter("@Time", record.MeasureTime == TimeSpan.Zero ? DateTime.Now.TimeOfDay : record.MeasureTime),
|
|
new SqlParameter("@SeqNr", record.SequenceNum),
|
|
new SqlParameter("@CreatedAt", DateTime.Now)
|
|
};
|
|
return SQLHelper.ExecuteNonQuery(insertSql, insertParas, CommandType.Text);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update Function
|
|
|
|
// 更新分发配置
|
|
public int UpdateTaskRelease(CjlrTaskReleaseModel cjlrTaskRelease)
|
|
{
|
|
string strSql = @"
|
|
UPDATE CJLR.dbo.CJLR_TASK_RELEASE
|
|
SET
|
|
ModelsName = @ModelsName,
|
|
ModelsCode = @ModelsCode,
|
|
Position = @Position,
|
|
SourceFile = @SourceFile,
|
|
TargetFile = @TargetFile,
|
|
Status = @Status,
|
|
create_date = @create_date,
|
|
is_delete = @is_delete,
|
|
readType = @readType
|
|
WHERE
|
|
Id = @Id;";
|
|
|
|
SqlParameter[] paras = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@ModelsName", cjlrTaskRelease.ModelsName ?? (object)DBNull.Value),
|
|
new SqlParameter("@ModelsCode", cjlrTaskRelease.ModelsCode ?? (object)DBNull.Value),
|
|
new SqlParameter("@position", cjlrTaskRelease.Position ?? (object)DBNull.Value),
|
|
new SqlParameter("@sourceFile", cjlrTaskRelease.SourceFile ?? (object)DBNull.Value),
|
|
new SqlParameter("@targetFile", cjlrTaskRelease.TargetFile ?? (object)DBNull.Value),
|
|
new SqlParameter("@status", cjlrTaskRelease.Status ?? (object)DBNull.Value),
|
|
new SqlParameter("@create_date", cjlrTaskRelease.CreateDate),
|
|
new SqlParameter("@is_delete", cjlrTaskRelease.IsDelete),
|
|
new SqlParameter("@readType", cjlrTaskRelease.ReadType),
|
|
new SqlParameter("@id", cjlrTaskRelease.Id)
|
|
};
|
|
|
|
return SQLHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
// 更新的方式标记删除
|
|
public int UpdateIsDelete(string modelsName, string modelsCode)
|
|
{
|
|
// SQL 更新语句
|
|
string strOle = "UPDATE CJLR_TASK_RELEASE SET is_delete = 0 WHERE modelsName = @modelsName AND modelsCode = @modelsCode";
|
|
|
|
// 创建参数
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@modelsName", modelsName),
|
|
new SqlParameter("@modelsCode", modelsCode)
|
|
};
|
|
|
|
// 执行更新操作
|
|
return SQLHelper.ExecuteNonQuery(strOle, parameters, CommandType.Text);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete Function
|
|
|
|
// 删除指定车型的分发配置
|
|
public int DeleteOneTolerance(string modelsName)
|
|
{
|
|
// 使用参数化查询以防止 SQL 注入
|
|
string strOle = "DELETE FROM CJLR_TASK_RELEASE WHERE modelsName = @modelsName";
|
|
|
|
// 创建一个 SqlParameter 来替代直接拼接字符串
|
|
SqlParameter[] parameters = new SqlParameter[]
|
|
{
|
|
new SqlParameter("@modelsName", modelsName),
|
|
};
|
|
|
|
// 执行非查询操作
|
|
return SQLHelper.ExecuteNonQuery(strOle, parameters, CommandType.Text);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region 记录文件是否已经处理
|
|
|
|
// 判断文件是否已处理
|
|
public bool IsFileProcessed(string fileName)
|
|
{
|
|
string sql = "SELECT COUNT(*) FROM CJLR_ProcessedFiles WHERE FileName = @FileName";
|
|
SqlParameter[] paras = { new SqlParameter("@FileName", fileName) };
|
|
DataTable dt = SQLHelper.ExecuteQuery(sql, paras, CommandType.Text);
|
|
return dt.Rows.Count > 0 && Convert.ToInt32(dt.Rows[0][0]) > 0;
|
|
}
|
|
|
|
// 记录已处理文件
|
|
public int InsertProcessedFile(string fileName, string taskCode = null, string position = null)
|
|
{
|
|
string sql = @"INSERT INTO CJLR_ProcessedFiles (FileName, TaskCode, Position) VALUES (@FileName, @TaskCode, @Position)";
|
|
SqlParameter[] paras = {
|
|
new SqlParameter("@FileName", fileName),
|
|
new SqlParameter("@TaskCode", (object)taskCode ?? DBNull.Value),
|
|
new SqlParameter("@Position", (object)position ?? DBNull.Value)
|
|
};
|
|
return SQLHelper.ExecuteNonQuery(sql, paras, CommandType.Text);
|
|
}
|
|
|
|
// 获取已处理文件名列表
|
|
public List<string> GetProcessedFileNames()
|
|
{
|
|
string sql = "SELECT FileName FROM CJLR_ProcessedFiles";
|
|
DataTable dt = SQLHelper.ExecuteQuery(sql, CommandType.Text);
|
|
var list = new List<string>();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
list.Add(row["FileName"].ToString());
|
|
}
|
|
return list;
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
}
|