/// <summary>
/// 根据条件获取ID
/// </summary>
/// <param name="tableType">表名</param>
/// <param name="condition">条件</param>
/// <returns>ID号，-1表示错误</returns>
public int GetTableID(TDBableType tableType, string condition)
{
try
{
string sqlstr = "select ID from " + DBTableName[(int)tableType];
if (condition != null)
{
sqlstr += condition;
}
OleDbCommand myAccessCommand = new OleDbCommand(sqlstr, myAccessConn);
OleDbDataReader myDataReader = myAccessCommand.ExecuteReader();
if (!myDataReader.Read())
{
return(-1);
}
int id = GetData <int>(myDataReader, "ID");
myDataReader.Close();
return(id);
}
catch (Exception ex)
{
ErrorString = ex.Message;
return(-1);
}
}
/// <summary>
/// 获取最大的ID号，主要用于新增数据
/// </summary>
/// <param name="tableType">数据表格类型</param>
/// <param name="IDName">id字段的名称</param>
/// <returns>最新的ID号</returns>
public int GetMaxID(TDBableType tableType, string IDName = "ID")
{
//获取最大的ID号，也就是新增加的ID
OleDbCommand myAccessCommand = null;
OleDbDataReader vOleDbDataReader = null;
try
{
myAccessCommand = new OleDbCommand("select max(" + IDName + ") as maxid from " + DBTableName[(int)tableType], myAccessConn);
vOleDbDataReader = myAccessCommand.ExecuteReader();
vOleDbDataReader.Read();
int maxid = (int)vOleDbDataReader["maxid"];
return(maxid);
}
catch (Exception ex)
{
ErrorString = ex.Message;
return(-1);
}
finally
{
if (vOleDbDataReader != null)
{
vOleDbDataReader.Close();
vOleDbDataReader.Dispose();
}
if (myAccessCommand != null)
{
myAccessCommand.Dispose();
}
}
}
/// <summary>
/// 修改检测结果信息
/// </summary>
/// <param name="data">检测结果信息</param>
/// <param name="isPackData">是否修改一箱的平均结果</param>
/// <returns>操作结果</returns>
public bool UpdateAnalysisResult(ResultDisplayInfo data, bool isPackData = false)
{
TDBableType datatype = isPackData == false ? TDBableType.AnalysisResult : TDBableType.EachPackResult;
string sqlstr = "update " + DBTableName[(int)datatype] + " set ";
sqlstr += " 时间 = convert(DateTime, " + data.time.ToString(Common.SettingData.LongDateTimeString) + ", 20) ,";
sqlstr += " 客户='" + data.custom + "',";
sqlstr += " 等级='" + data.grade + "',";
sqlstr += " 班组='" + data.worker + "',";
sqlstr += " 产地='" + data.place + "',";
sqlstr += " 年份=" + data.year;
sqlstr += " 流量=" + data.output;
sqlstr += " 光谱名称='" + data.filename + "'";
//组分名称和值
for (int i = 0; i < data.values.Length; i++)
{
sqlstr += "," + SettingData.settingData.programParamerter.dbComponentNames[i] + "=" + data.values[i].ToString();
}
sqlstr += " where ID=" + data.id;
if (ExcuteSqlCommand(sqlstr) == false)
{
return(false);
}
return(true);
}
/// <summary>
/// 新增检测结果信息
/// </summary>
/// <param name="data">检测结果信息</param>
/// <param name="isPackData">是否新增一箱的平均结果</param>
/// <returns>操作结果</returns>
public bool InsertAnalysisResult(ResultDisplayInfo data, bool isPackData = false)
{
TDBableType datatype = isPackData == false ? TDBableType.AnalysisResult : TDBableType.EachPackResult;
string sqlstr = "insert into " + DBTableName[(int)datatype];
sqlstr += "(时间, 客户, 等级, 班组, 产地, 年份, 流量, 光谱名称";
//组分名称
foreach (string name in SettingData.settingData.programParamerter.dbComponentNames)
{
sqlstr += ", " + name;
}
sqlstr += ") values(";
//sqlstr += "convert(DateTime,'" + data.time.ToString(Common.SettingData.LongDateTimeString) + "', 20) ,";
sqlstr += "#" + data.time.ToString(Common.SettingData.LongDateTimeString) + "# ,";
sqlstr += "'" + data.custom + "',";
sqlstr += "'" + data.grade + "',";
sqlstr += "'" + data.worker + "',";
sqlstr += "'" + data.place + "',";
sqlstr += data.year + ",";
sqlstr += data.output + ",";
sqlstr += "'" + data.filename + "'";
//组分值
foreach (double value in data.values)
{
sqlstr += ", " + value.ToString();
}
sqlstr += ")";
if (ExcuteSqlCommand(sqlstr) == false)
{
return(false);
}
if ((data.id = GetMaxID(datatype)) < 0)
{
return(false);
}
return(true);
}
public string GetTableName(TDBableType tableType)
{
return(DBTableName[(int)tableType]);
}
/// <summary>
/// 查询检测结果信息，参数可以为空
/// </summary>
/// <param name="isPackData">是否查询一箱的平均结果</param>
/// <returns>查询检测结果信息</returns>
public List <ResultDisplayInfo> GetAnalysisResult(productInfo info, DateTime beginDate, DateTime endDate, bool isPackData = false)
{
try
{
//构建查询语句
TDBableType datatype = isPackData == false ? TDBableType.AnalysisResult : TDBableType.EachPackResult;
string sqlstr = "select * from " + DBTableName[(int)datatype] + " where ID>=1 "; //添加一个ID>=1防止后面的and出现错误
if (((DateTime)beginDate).ToString(Common.SettingData.ShortDateTimeString).Equals(((DateTime)endDate).ToString(Common.SettingData.ShortDateTimeString)))
{
string[] tt = ((DateTime)beginDate).ToString(Common.SettingData.ShortDateTimeString).Split(' ');
sqlstr += " and 时间 >=#" + tt[0] + " 0:0:0#";
sqlstr += " and 时间 <=#" + tt[0] + " 23:59:59#";
}
else
{
sqlstr += " and 时间 >=#" + ((DateTime)beginDate).ToString(Common.SettingData.ShortDateTimeString) + "#";
sqlstr += " and 时间 <=#" + ((DateTime)endDate).ToString(Common.SettingData.ShortDateTimeString) + "#";
}
if (info.custom != "")
{
sqlstr += " and 客户='" + info.custom.Trim() + "'";
}
if (info.grade != "")
{
sqlstr += " and 等级='" + info.grade.Trim() + "'";
}
if (info.worker != "")
{
sqlstr += " and 班组='" + info.worker.Trim() + "'";
}
if (info.place != "")
{
sqlstr += " and 产地='" + info.place.Trim() + "'";
}
if (info.year != "" && info.year != "0")
{
sqlstr += " and 年份=" + info.year.ToString();
}
OleDbCommand myAccessCommand = new OleDbCommand(sqlstr, myAccessConn);
OleDbDataReader myDataReader = myAccessCommand.ExecuteReader();
List <ResultDisplayInfo> analysisList = new List <ResultDisplayInfo>();
while (myDataReader.Read())
{
ResultDisplayInfo data = new ResultDisplayInfo();
data.id = GetData <int>(myDataReader, "ID");
data.time = GetData <DateTime>(myDataReader, "时间");
data.custom = GetData <string>(myDataReader, "客户");
data.grade = GetData <string>(myDataReader, "等级");
data.worker = GetData <string>(myDataReader, "班组");
data.place = GetData <string>(myDataReader, "产地");
data.year = GetData <int>(myDataReader, "年份");
data.output = GetData <int>(myDataReader, "流量");
data.filename = GetData <string>(myDataReader, "光谱名称");
data.values = new double[SettingData.settingData.programParamerter.dbComponentNames.Count];
//读取组分值
for (int i = 0; i < SettingData.settingData.programParamerter.dbComponentNames.Count; i++)
{
data.values[i] = GetData <double>(myDataReader, SettingData.settingData.programParamerter.dbComponentNames[i]);
}
analysisList.Add(data);
}
myDataReader.Close();
for (int i = 0; i < analysisList.Count; i++)
{
for (int j = i; j < analysisList.Count; j++)
{
if (analysisList[i].time > analysisList[j].time)
{
ResultDisplayInfo temp = null;
temp = analysisList[i];
analysisList[i] = analysisList[j];
analysisList[j] = temp;
}
}
}
return(analysisList);
}
catch (Exception ex)
{
ErrorString = ex.Message;
return(null);
}
}
/// <summary>
/// 通用的数据删除过程, 通常情况下是安装ID字段来删除
/// </summary>
/// <param name="tableType">数据表类型（名称）</param>
/// <param name="id">需要删除的数据的ID号</param>
/// <returns></returns>
public bool DeleteData(TDBableType tableType, int id)
{
return(DeleteData(tableType, "ID=" + id));
}
public bool ClearTable(TDBableType tableType)
{
return(ExcuteSqlCommand("delete from " + DBTableName[(int)tableType]));
}
/// <summary>
/// 通用的数据删除过程
/// </summary>
/// <param name="tableType">数据表类型（名称）</param>
/// <param name="conditionstr">删除条件（不带where)</param>
/// <returns>操作结果</returns>
public bool DeleteData(TDBableType tableType, string conditionstr)
{
return(ExcuteSqlCommand("delete from " + DBTableName[(int)tableType] + " where " + conditionstr));
}
