zhengxuan.zhang
297 lines
11 KiB
C#
297 lines
11 KiB
C#
using System.Data;
|
|
using System.Data.SQLite;
|
|
using System.Threading;
|
|
|
|
namespace DAL
|
|
{
|
|
public class TMeasureSQLiteDAL
|
|
{
|
|
#region Select Function
|
|
|
|
public DataTable SelectAllTMeasureData()
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select * from TMeasureData";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
public bool CheckVINExistInDB(string strVIN)
|
|
{
|
|
bool bReusult = false;
|
|
string strSql = "select Id from TMeasureResult where CarID = '" + strVIN + "'";
|
|
DataTable dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
if (dt.Rows.Count >= 2)
|
|
{
|
|
bReusult = true;
|
|
}
|
|
return bReusult;
|
|
}
|
|
|
|
public DataTable SelectMeasureValuebyMeasureNameAndSize(string strMeasureName, string strSizeName, int topCount)
|
|
{
|
|
string strSql = "select MeasureValue,NormalValue ,LowerTolVal,UpperTolVal from TMeasureData where MeasPointName='" + strMeasureName + "' and DimensionName='" + strSizeName + "' order by MeasureDate DESC Limit " + topCount.ToString();
|
|
return SQLiteHelper.ExecuteDataTable(strSql);
|
|
}
|
|
|
|
public DataTable SelectAllMeasPointName()
|
|
{
|
|
string strSql = "select distinct MeasPointName from TMeasureData";
|
|
return SQLiteHelper.ExecuteDataTable(strSql);
|
|
}
|
|
|
|
public DataTable SelectTMeasureDataByCarIDAndTime(string strCarID, string strStartTime, string strEndTime)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select CarID, MeasPointName,DimensionName,NormalValue ,LowerTolVal,UpperTolVal,MeasureValue, MeasureDate, MeasureItemResult from TMeasureData where CarID like '%" + strCarID + "%' and MeasureDate >= '" + strStartTime + "' and MeasureDate <= '" + strEndTime + "'";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable SelectTMeasureDataByCarIDAndMPN(string strCarID, string strMeaPointName)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select MeasPointName,DimensionName,LowerTolVal,UpperTolVal,MeasureValue, MeasureItemResult from TMeasureData where CarID like '%" + strCarID + "%' and MeasPointName like '%" + strMeaPointName + "%'";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable SelectAllTMeasureResult()
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select * from TMeasureResult";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
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 = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
public string SelectMaintenanceStation3VIN()
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select Station3PartID from MaintenanceInfo where Id=1";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt.Rows[0][0].ToString();
|
|
}
|
|
|
|
public DataTable SelectAllToleranceByCondition(string strCartType, string strMeaPointName, string strDimensionName)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select Id,CarType,MeasurePointName,DimensionName,TolLower,TolUpper, Remark from TTolerance where CarType like '%" + strCartType + "%' and MeasurePointName like '%" + strMeaPointName + "%' and DimensionName like '%" + strDimensionName + "%'";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
public DataTable SelectOneToleranceByCondition(string strCartType, string strMeaPointName, string strDimensionName)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select TolLower,TolUpper from TTolerance where CarType = '" + strCartType + "' and MeasurePointName = '" + strMeaPointName + "' and DimensionName = '" + strDimensionName + "'";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
return dt;
|
|
}
|
|
|
|
public bool CheckMeaPointNameExit(string strCartType, string strMeaPointName, string strDimensionName)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strSql = "select Id,CarType,MeasurePointName,DimensionName,TolLower,TolUpper, Remark from TTolerance where CarType = '" + strCartType + "' and MeasurePointName = '" + strMeaPointName + "' and DimensionName = '" + strDimensionName + "'";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Insert Function
|
|
|
|
public int InsertTTolerance(TToleranceModel ttm)
|
|
{
|
|
string strSql = "insert into TTolerance (CarType,MeasurePointName,DimensionName,TolLower,TolUpper,CreateTime,Remark) values " +
|
|
"(@CarType,@MeasurePointName,@DimensionName,@TolLower,@TolUpper,@CreateTime,@Remark)";
|
|
SQLiteParameter[] paras = new SQLiteParameter[]
|
|
{
|
|
new SQLiteParameter("@CarType",ttm.CarType),
|
|
new SQLiteParameter("@MeasurePointName",ttm.MeasurePointName),
|
|
new SQLiteParameter("@DimensionName",ttm.DimensionName),
|
|
new SQLiteParameter("@TolLower",ttm.TolLower),
|
|
new SQLiteParameter("@TolUpper",ttm.TolUpper),
|
|
new SQLiteParameter("@CreateTime",ttm.CreateTime),
|
|
new SQLiteParameter("@Remark",ttm.Remark),
|
|
};
|
|
return SQLiteHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
public int InsertTMeasureResultTest()
|
|
{
|
|
TMeasureResultModel tmrm = new TMeasureResultModel();
|
|
tmrm.CarID = "tEST123";
|
|
string strSql = "insert into TMeasureResult (CarID,SumMeasureItems,GoodMeasureItems,NoGoodMeasureItems,RejectMeasureItems,FPY,MeasureDate,Result,Remark) values " +
|
|
"(@CarID,@SumMeasureItems,@GoodMeasureItems,@NoGoodMeasureItems,@RejectMeasureItems,@FPY,@MeasureDate,@Result,@Remark)";
|
|
SQLiteParameter[] paras = new SQLiteParameter[]
|
|
{
|
|
new SQLiteParameter("@CarID",tmrm.CarID),
|
|
new SQLiteParameter("@SumMeasureItems",tmrm.SumMeasureItems),
|
|
new SQLiteParameter("@GoodMeasureItems",tmrm.GoodMeasureItems),
|
|
new SQLiteParameter("@NoGoodMeasureItems",tmrm.NoGoodMeasureItems),
|
|
new SQLiteParameter("@RejectMeasureItems",tmrm.RejectMeasureItems),
|
|
new SQLiteParameter("@FPY",tmrm.FPY),
|
|
new SQLiteParameter("@MeasureDate",tmrm.MeasureDate),
|
|
new SQLiteParameter("@Result",tmrm.Result),
|
|
new SQLiteParameter("@Remark",tmrm.Remark),
|
|
};
|
|
return SQLiteHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
public int InsertTMeasureResult(TMeasureResultModel tmrm)
|
|
{
|
|
string strSql = "insert into TMeasureResult (CarID,SumMeasureItems,GoodMeasureItems,NoGoodMeasureItems,RejectMeasureItems,FPY,MeasureDate,Result,Remark) values " +
|
|
"(@CarID,@SumMeasureItems,@GoodMeasureItems,@NoGoodMeasureItems,@RejectMeasureItems,@FPY,@MeasureDate,@Result,@Remark)";
|
|
SQLiteParameter[] paras = new SQLiteParameter[]
|
|
{
|
|
new SQLiteParameter("@CarID",tmrm.CarID),
|
|
new SQLiteParameter("@SumMeasureItems",tmrm.SumMeasureItems),
|
|
new SQLiteParameter("@GoodMeasureItems",tmrm.GoodMeasureItems),
|
|
new SQLiteParameter("@NoGoodMeasureItems",tmrm.NoGoodMeasureItems),
|
|
new SQLiteParameter("@RejectMeasureItems",tmrm.RejectMeasureItems),
|
|
new SQLiteParameter("@FPY",tmrm.FPY),
|
|
new SQLiteParameter("@MeasureDate",tmrm.MeasureDate),
|
|
new SQLiteParameter("@Result",tmrm.Result),
|
|
new SQLiteParameter("@Remark",tmrm.Remark),
|
|
};
|
|
return SQLiteHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
public int InsertTMeasureDatabyDataTable(DataTable dt)
|
|
{
|
|
string commandText = $"INSERT INTO TMeasureData (CarID,MeasPointName,DimensionName,NormalValue,LowerTolVal,UpperTolVal,MeasureValue,MeasureDate,MeasureItemResult,Remark)" +
|
|
$" VALUES (@CarID,@MeasPointName,@DimensionName,@NormalValue,@LowerTolVal,@UpperTolVal,@MeasureValue,@MeasureDate,@MeasureItemResult,@Remark)";
|
|
return SQLiteHelper.ExecuteMutliQuery(commandText, dt);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Update Function
|
|
|
|
public int UpdateTTolerance(TToleranceModel ttm)
|
|
{
|
|
string strSql = "update TTolerance set CarType=@CarType,MeasurePointName=@MeasurePointName,DimensionName=@DimensionName,TolLower=@TolLower,TolUpper=@TolUpper,CreateTime=@CreateTime,Remark=@Remark where Id=" + ttm.Id.ToString();
|
|
SQLiteParameter[] paras = new SQLiteParameter[]
|
|
{
|
|
new SQLiteParameter("@Id",ttm.Id),
|
|
new SQLiteParameter("@CarType",ttm.CarType),
|
|
new SQLiteParameter("@MeasurePointName",ttm.MeasurePointName),
|
|
new SQLiteParameter("@DimensionName",ttm.DimensionName),
|
|
new SQLiteParameter("@TolLower",ttm.TolLower),
|
|
new SQLiteParameter("@TolUpper",ttm.TolUpper),
|
|
new SQLiteParameter("@CreateTime",ttm.CreateTime),
|
|
new SQLiteParameter("@Remark",ttm.Remark),
|
|
};
|
|
return SQLiteHelper.ExecuteNonQuery(strSql, paras, CommandType.Text);
|
|
}
|
|
|
|
public int updateMaintenceInfoEmpty()
|
|
{
|
|
string strSql = "";
|
|
string strPartID = "empty";
|
|
strSql = "update MaintenanceInfo set Station1PartID = '" + strPartID + "', Station2PartID = '" + strPartID + "', Station3PartID = '" + strPartID + "' where Id=1";
|
|
return SQLiteHelper.ExecuteNonQuery(strSql);
|
|
}
|
|
|
|
public int updateMaintenceInfo(string strPartID, int iStationNo)
|
|
{
|
|
string strSql = "";
|
|
switch (iStationNo)
|
|
{
|
|
case 1:
|
|
strSql = "update MaintenanceInfo set Station1PartID = '" + strPartID + "' where Id=1";
|
|
break;
|
|
|
|
case 2:
|
|
strSql = "update MaintenanceInfo set Station2PartID = '" + strPartID + "' where Id=1";
|
|
break;
|
|
|
|
case 3:
|
|
strSql = "update MaintenanceInfo set Station3PartID = '" + strPartID + "' where Id=1";
|
|
break;
|
|
|
|
default:
|
|
strSql = "update MaintenanceInfo set Station1PartID = '" + strPartID + "' where Id=1";
|
|
break;
|
|
}
|
|
return SQLiteHelper.ExecuteNonQuery(strSql);
|
|
}
|
|
|
|
public int updateMaintence1To2(out string strCarID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strPartID = "";
|
|
string strSql = "";
|
|
strSql = "Select Station1PartID from MaintenanceInfo";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
strPartID = dt.Rows[0][0].ToString();
|
|
strCarID = strPartID;
|
|
if (strPartID.ToLower().Contains("empty"))
|
|
{
|
|
return -1;
|
|
}
|
|
else
|
|
{
|
|
strSql = "update MaintenanceInfo set Station2PartID = '" + strPartID + "' where Id=1";
|
|
int iRes = SQLiteHelper.ExecuteNonQuery(strSql);
|
|
Thread.Sleep(100);
|
|
strPartID = "empty";
|
|
strSql = "update MaintenanceInfo set Station1PartID = '" + strPartID + "' where Id=1";
|
|
int iRes2 = SQLiteHelper.ExecuteNonQuery(strSql);
|
|
return iRes2 + iRes;
|
|
}
|
|
}
|
|
|
|
public int updateMaintence2To3(out string strCarID)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
string strPartID = "";
|
|
string strSql = "";
|
|
strSql = "Select Station2PartID from MaintenanceInfo";
|
|
dt = SQLiteHelper.ExecuteDataTable(strSql);
|
|
strPartID = dt.Rows[0][0].ToString();
|
|
strCarID = strPartID;
|
|
if (strPartID.ToLower().Contains("empty"))
|
|
{
|
|
return -1;
|
|
}
|
|
else
|
|
{
|
|
strSql = "update MaintenanceInfo set Station3PartID = '" + strPartID + "' where Id=1";
|
|
int iRes = SQLiteHelper.ExecuteNonQuery(strSql);
|
|
Thread.Sleep(100);
|
|
strPartID = "empty";
|
|
strSql = "update MaintenanceInfo set Station2PartID = '" + strPartID + "' where Id=1";
|
|
int iRes2 = SQLiteHelper.ExecuteNonQuery(strSql);
|
|
return iRes2 + iRes;
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Delete Function
|
|
|
|
public int DeleteOneTolerance(string strObjIDPk)
|
|
{
|
|
string strOle = "delete from TTolerance where Id = " + strObjIDPk;
|
|
return SQLiteHelper.ExecuteNonQuery(strOle);
|
|
}
|
|
|
|
#endregion
|
|
}
|
|
} |