public void Load()
{
cSensors.Clear();
DAO.Recordset RS;
string SQL = "Select * from tblSensors order by senRecNum";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
clsSensor Sen = new clsSensor(mf);
Sen.Load((short)RS.Fields["senID"].Value);
cSensors.Add(Sen);
RS.MoveNext();
}
RS.Close();
}
public void Load()
{
cControlBoxes.Clear();
DAO.Recordset RS;
string SQL = "Select * from tblControlBoxes order by cbNumber";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
clsControlBox Box = new clsControlBox(mf);
Box.Load((byte)RS.Fields["cbID"].Value);
cControlBoxes.Add(Box);
RS.MoveNext();
}
RS.Close();
}
public void Load()
{
Storages.Clear();
DAO.Recordset RS;
string SQL = "select * from tblStorage order by storRecNum";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
clsStorage Stor = new clsStorage(mf);
Stor.Load((short)RS.Fields["storID"].Value);
Storages.Add(Stor);
RS.MoveNext();
}
RS.Close();
}
public void TrimRecords(DAO.Database DB)
{
DAO.Recordset RS;
string SQL = "select * from tblProps";
RS = DB.OpenRecordset(SQL);
int dbMax = (int)(RS.Fields["dbMaxSize"].Value ?? 0);
int Size = (int)(new System.IO.FileInfo(cDB.Name).Length);
RS.Close();
if (Size > dbMax)
{
SQL = "Select top 20 percent * from tblRecords order by recID desc";
RS = DB.OpenRecordset(SQL);
while (!RS.EOF)
{
RS.Delete();
RS.MoveNext();
}
RS.Close();
}
}
public List <byte> ControlBoxIDs(bool OnlyEnabledSensors = false)
{
List <byte> IDs = new List <byte>();
DAO.Recordset RS;
string SQL = "select senControlBoxID, Max(recTimeStamp) as TS";
SQL += " From tblSensors Left Join tblRecords On tblSensors.senID = tblRecords.recSenID";
SQL += " Group By senControlBoxID";
if (OnlyEnabledSensors)
{
SQL += ", senEnabled having senEnabled = True";
}
SQL += " Order By Max(recTimeStamp) Desc";
RS = mf.Dbase.DB.OpenRecordset(SQL);
while (!RS.EOF)
{
IDs.Add((byte)(RS.Fields["senControlBoxID"].Value ?? 0));
RS.MoveNext();
}
RS.Close();
return(IDs);
}
private string CheckVersion(short Ver, string DBname)
{
string SQL;
DAO.Recordset RS;
string Result = "";
if (Ver > cDBversion)
{
// higher version, program out of date
Result = "Database version does not match, software out of date.";
}
else if (Ver == cDBversion)
{
Result = "true";
}
else
{
// lower version, update
// close current database
try
{
cDB.Close();
}
catch (Exception)
{
}
// copy base file to tmp file in data folder
string NewPath = mf.Tls.DataFolder + "\\NewTmp.mdb";
string BasePath = mf.Tls.SettingsFolder + "\\TempMonBase.mdb";
File.WriteAllBytes(NewPath, Properties.Resources.Base);
FileInfo BaseFile = new FileInfo(BasePath);
// copy current database to tmp file in data folder
string TmpPath = mf.Tls.DataFolder + "\\OldTmp.mdb";
try
{
// delete tmp file, if it exists
File.Delete(TmpPath);
}
catch (Exception Ex)
{
mf.Tls.WriteErrorLog("clsDatabase: CheckVersion: " + Ex.Message);
}
FileInfo OldFile = new FileInfo(DBname);
OldFile.CopyTo(TmpPath);
// open new tmp database and old tmp database
DAO.Database DBnew = cDBE.OpenDatabase(NewPath, DAO.DriverPromptEnum.dbDriverNoPrompt, false, "");
DAO.Database DBold = cDBE.OpenDatabase(TmpPath, DAO.DriverPromptEnum.dbDriverNoPrompt, false, "");
// delete new database properties records
SQL = "select * from tblProps";
RS = DBnew.OpenRecordset(SQL);
while (!RS.EOF)
{
RS.Delete();
RS.MoveNext();
}
RS.Close();
// copy matching data from old database to new database
if (CopyData(DBnew, DBold))
{
// update database properties
SQL = "select * from tblProps";
RS = DBnew.OpenRecordset(SQL);
if (RS.EOF)
{
RS.AddNew();
}
else
{
RS.Edit();
}
RS.Fields["dbType"].Value = DBtype;
RS.Fields["dbVersion"].Value = cDBversion;
RS.Update();
RS.Close();
// copy new updated database to current database name
DBnew.Close();
DBold.Close();
File.Delete(DBname); // delete current file
FileInfo NewFile = new FileInfo(NewPath);
NewFile.CopyTo(DBname); // copy new file to current file name
File.Delete(TmpPath); // delete tmp copy of current file
File.Delete(NewPath); // delete new file
Result = "true";
}
else
{
// failed to copy, remove new database and tmp database
File.Delete(TmpPath); // delete tmp copy of current file
File.Delete(NewPath); // delete new file
Result = "Failed to update database version.";
}
}
return(Result);
}
private void LoadTable()
{
try
{
DateTime StDate;
DateTime EndDate;
string DateFmt;
DAO.Recordset RS;
int ID = 0;
// make tmp table
string SQL1 = "SELECT recTimeStamp, senSensorNumber, recTemp INTO tmpRecs";
SQL1 += " FROM tblSensors LEFT JOIN tblRecords ON tblSensors.senID = tblRecords.recSenID";
SQL1 += " GROUP BY recTimeStamp,senSensorNumber,recTemp,senBinNumber,senCableNumber";
SQL1 += " Having senBinNumber = " + tbBin.Text;
SQL1 += " And senCableNumber = " + tbCable.Text;
// date
StDate = dtpSingle.Value;
EndDate = dtpRange.Value;
if (StDate == EndDate | rbSingle.Checked)
{
// single date, end date is beginning of the next day
EndDate = StDate.AddDays(1);
}
else
{
// date range, end date is beginning of the next day after user selected end date
EndDate = EndDate.AddDays(1);
}
SQL1 += " And recTimeStamp >= " + mf.Dbase.ToAccessDate(StDate) + " And recTimeStamp < " + mf.Dbase.ToAccessDate(EndDate);
SQL1 += " Order By senBinNumber,recTimeStamp,senCableNumber,senSensorNumber";
mf.Dbase.DeleteTable("tmpRecs"); // delete old table, if any
mf.Dbase.DB.Execute(SQL1); // create tmp table
// make crosstab recordset
if (rbHour.Checked)
{
DateFmt = "" + (char)39 + "yyyy/mm/dd hh" + (char)39;
}
else
{
DateFmt = "" + (char)39 + "yyyy/mm/dd" + (char)39;
}
string SQL = "Transform Avg(recTemp) as AvgOfrecTemp";
SQL += " Select Format([recTimeStamp]," + DateFmt + ") as ReadDate";
SQL += " From tmpRecs";
SQL += " Group By Format([recTimeStamp]," + DateFmt + ")";
SQL += " PIVOT senSensorNumber In (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)";
RS = mf.Dbase.DB.OpenRecordset(SQL);
dataSet1.Clear();
try // needed to close recordset after error
{
while (!RS.EOF)
{
ID++;
DataRow Rw = dataSet1.Tables[0].NewRow();
Rw[0] = ID;
Rw[1] = mf.Dbase.FieldToString(RS, "ReadDate");
for (int i = 0; i < 16; i++)
{
Rw[i + 2] = mf.Dbase.FieldToFloat(RS, (i + 1).ToString("N0"));
}
dataSet1.Tables[0].Rows.Add(Rw);
RS.MoveNext();
}
}
catch (Exception)
{
}
RS.Close();
}
catch (Exception ex)
{
mf.Tls.TimedMessageBox(ex.Message);
mf.Tls.WriteErrorLog("frmBinReport:LoadTable " + ex.Message);
}
}
