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
	}
}