public Database()
{
myAES = new SimplerAES();
AccessConnection.ConnectionString = myAES.Decrypt(DBcrypt);
myAES = null;
}
public bool WriteData(ArrayList Samples)
{
bool status = true;
if (CurrentCommand.GetCType == "Insert") {
myAES = new SimplerAES();
foreach (Sample Sample in Samples) {
foreach (Analyte Metal in Sample.Metals) {
if ((Metal.WasFoundinDB == true) && (Metal.DO_NOT_UPLOAD == false) && (Metal.Ready2Upload==true)) {
string Line = myAES.Decrypt("I/ydFjDhNoT3OB8O9/qRFos0brdwlM8WUhP7sm8oxDcuaAmUXVbV48wTyKj+x8CGThg2z6bQ9rOayQU3I5VqgD7zhAsdSlqI4rIWActaH+NHATEa2fAHXnPiRdjCTx+TM+V1hoJB1dIV7u1hkzV8jrvNf8LZH87DjOhD6j9gpJuDJ8aDfufqdugnmmoFKBXQ") +
Sample.AuditTimeinSQL + "','" + Sample.DateTimeInSQL + "'," + Convert.ToDouble(((Analyte)Metal).ConcMean.ToString()) + "," +
((Analyte)Metal).ConcMean.ToString() + myAES.Decrypt("KsyRIHLLxSYPzCw53d6liw==");
AccessConnection.Open();
AccessCommand.CommandText = Line;
try {
AccessCommand.ExecuteNonQuery();
}
catch (OleDbException Ex) {
MessageBox.Show("OleDB write error during Insert as follows: " + Ex.Message);
status = false;
}
AccessConnection.Close();
}
}
}
}
CurrentCommand.SetCommand("NULL");
return status;
}
public bool SendData(ArrayList Samples, int HowMany, CommandType Master, RichTextBox RTB, ref string ErrorLog)
{
int Count = 0;
if (Master.GetCType == "UPLOAD") {
myAES = new SimplerAES();
int MetalCounter = 0;
foreach (Sample Item in Samples) {
Count++;
if (Item.QCType == Sample.QCTYPES.QCSet) {
string Line = myAES.Decrypt("I/ydFjDhNoT3OB8O9/qRFos0brdwlM8WUhP7sm8oxDcuaAmUXVbV48wTyKj+x8CGAmD8zewtcSpYMIYTYO1RoZLIVtQzNEQXZLxjsS2//RRFlc2FKNbAyH1BV/wIJlGqNxD86q/8W9VZVKkhAlAMGu7TEGgsPPb2pehC8e3uQS4=") +
Item.AuditTimeinSQL + "," + Item.DateTimeInSQL + "," + (int)Item.QC_SET + "," +
(int)Item.QC_SET + "," + Item.QCSetVARID + myAES.Decrypt("KsyRIHLLxSYPzCw53d6liw==");
AccessConnection.Open();
AccessCommand.CommandText = Line;
try {
AccessCommand.ExecuteNonQuery();
RTB.AppendText(Item.SampleName + " Upload Successfull\n");
}
catch (OleDbException Dup) {
if (Dup.Message == "Duplicate key was ignored.") {
AccessConnection.Close(); //Sample.QCsetRv[]
string fixer = myAES.Decrypt("TcX2Nh7xfpr1fLcS5YoR//NFseQGD6eGLkt6tuP+a4+XvXCw/8E1QCcDwqLzXE4/") +
Item.AuditTimeinSQL + myAES.Decrypt("cihCKk7jSj3u1pi35FjzkA==") +
(int)Item.QC_SET + myAES.Decrypt("L/O0Rp0M+kfULU/4prnipA==") +
(int)Item.QC_SET + myAES.Decrypt("1WYKz9V2S1oIzQW3yxRfA5psCQDIQwfpdyMzCC5oapw=") +
Item.DateTimeInSQL + myAES.Decrypt("K7q/5JYfvbXfVcWjnOwmpA==") + Item.QCSetVARID + "";
AccessConnection.Open();
AccessCommand.Connection = AccessConnection;
AccessCommand.CommandText = fixer;
try {
AccessCommand.ExecuteNonQuery();
RTB.AppendText(Item.SampleName + " Upload Successfull\n");
Count++;
}
catch (Exception Er) {
RTB.AppendText(Item.SampleName + " DB error during Update as follows: " + Er.Message + "\n" + fixer + "\n");
ErrorLog += DateTime.Now + " " + Item.SampleName + " DB error during Insert as follows: " + Er.Message + "\r\n" + fixer + "\r\n";
}
}
else {
RTB.AppendText(Item.SampleName + " on analyte: " + "QC SET" + " DB error during Insert as follows: " + Dup.Message + "\n");
ErrorLog += DateTime.Now + " " + Item.SampleName + " on analyte: " + "QC SET" + " DB error during Insert as follows: " + Dup.Message + "\r\n"+Line+"\r\n";
}
}
AccessConnection.Close();
}
foreach (Analyte Metal in Item.Metals) {
if (Item.QCType == Sample.QCTYPES.Smp) {
Metal.WasFoundinDB = true;
Metal.DO_NOT_UPLOAD = false;
}
if ((Metal.AtomName!="Y") && (Metal.WasFoundinDB == true) && (Metal.DO_NOT_UPLOAD == false) /*&& (Metal.Ready2Upload == true)*/) {
string Line = myAES.Decrypt("I/ydFjDhNoT3OB8O9/qRFos0brdwlM8WUhP7sm8oxDcuaAmUXVbV48wTyKj+x8CGAmD8zewtcSpYMIYTYO1RoZLIVtQzNEQXZLxjsS2//RRFlc2FKNbAyH1BV/wIJlGq8BO8OndhHFXdMqoyurvNGw==") +
myAES.Decrypt("cfGHo18IK/lc8G4NenO7gAmvPlQxklXSoNzxQWfKAwk=") + Item.AuditTimeinSQL + "," + Item.DateTimeInSQL + "," + Metal.ConcMean.ToString() + "," +
Metal.ConcMean.ToString() + "," + Metal.FoundVarID + myAES.Decrypt("KsyRIHLLxSYPzCw53d6liw==");
AccessConnection.Open();
AccessCommand.CommandText = Line;
try {
AccessCommand.ExecuteNonQuery();
RTB.AppendText(Metal.SampleNameForUpload + " Upload Successfull\n");
}
catch (OleDbException Dup) {
if (Dup.Message == "Duplicate key was ignored.") {
AccessConnection.Close();
string fixer = myAES.Decrypt("TcX2Nh7xfpr1fLcS5YoR//NFseQGD6eGLkt6tuP+a4+XvXCw/8E1QCcDwqLzXE4/") +
Item.AuditTimeinSQL + myAES.Decrypt("cihCKk7jSj3u1pi35FjzkA==") + Convert.ToDouble(((Analyte)Metal).ConcMean.ToString()) +
myAES.Decrypt("L/O0Rp0M+kfULU/4prnipA==") + Convert.ToDouble(((Analyte)Metal).ConcMean.ToString()) +
myAES.Decrypt("1WYKz9V2S1oIzQW3yxRfA5psCQDIQwfpdyMzCC5oapw=") + Item.DateTimeInSQL +
myAES.Decrypt("K7q/5JYfvbXfVcWjnOwmpA==") + Metal.FoundVarID + "";
AccessConnection.Open();
AccessCommand.Connection = AccessConnection;
AccessCommand.CommandText = fixer;
try {
AccessCommand.ExecuteNonQuery();
RTB.AppendText(Metal.SampleNameForUpload + " Upload Successfull\n");
Count++;
}
catch (Exception Er) {
RTB.AppendText(Metal.SampleNameForUpload + " DB error during Update as follows: " + Er.Message + "\n" + fixer + "\n");
ErrorLog += DateTime.Now + " " + Metal.SampleNameForUpload + " DB error during Insert as follows: " + Er.Message + "\r\n" + fixer + "\r\n";
}
}
else {
RTB.AppendText(Item.SampleName + " on analyte: " + Metal.AtomName + " DB error during Insert as follows: " + Dup.Message + "\n");
ErrorLog += DateTime.Now + " " + Item.SampleName + " on analyte: " + Metal.AtomName + " DB error during Insert as follows: " + Dup.Message + "\r\n" + Metal.SQLUPLOADSTR + "\r\n" + Line+"\r\n";
}
}
catch (Exception Ex) {
RTB.AppendText(Item.SampleName + " on analyte: " + Metal.AtomName + " DB error during Insert as follows: " + Ex.Message + "\n");
ErrorLog += DateTime.Now + " " + Item.SampleName + " on analyte: " + Metal.AtomName + " DB error during Insert as follows: " + Ex.Message + "\r\n" + Metal.SQLUPLOADSTR + "\r\n"+Line+"\r\n";
}
AccessConnection.Close();
MetalCounter++;
}
}
}
}
if (Count == HowMany) return true;
else return false;
}
public ArrayList RetrieveData(string Comand, ref RichTextBox RTB)
{
ArrayList List = new ArrayList();
DataSet myData = new DataSet();
if (CurrentCommand.GetCType == "GetVarDesc") {
try {
AccessConnection.Open();
SimplerAES myAES = new SimplerAES();
AccessCommand.Connection = AccessConnection;
AccessCommand.CommandText = myAES.Decrypt(Comand);
myAES = null;
AccessAdapter.SelectCommand = AccessCommand;
DataSet Items = new DataSet();
AccessAdapter.Fill(Items);
AccessReader = AccessCommand.ExecuteReader();
DataTableReader Reader = Items.CreateDataReader();
try {
while (Reader.Read()) {
DATATBL_Vars Item = new DATATBL_Vars(Convert.ToInt32((string)Reader.GetValue(Reader.GetOrdinal("VARID")).ToString()),
(string)Reader.GetValue(Reader.GetOrdinal("Name")).ToString(),
(string)Reader.GetValue(Reader.GetOrdinal("Description")).ToString());
List.Add(Item);
}
}
catch (OleDbException Ex2) {
MessageBox.Show("OleDB error during Read operation : \n" + Ex2.Message);
}
AccessReader.Close();
}
catch (OleDbException Ex) {
MessageBox.Show("OleDB error in RetrieveData, ExecuteReader, or Open Connection: \n" + Ex.Message);
}
AccessConnection.Close();
CurrentCommand.SetCommand("NULL");
}
else if (CurrentCommand.GetCType == "GetLC_Samples") {
try
{
AccessConnection.Open();
SimplerAES myAES = new SimplerAES();
AccessCommand.Connection = AccessConnection;
AccessCommand.CommandText = myAES.Decrypt(Comand);
myAES = null;
AccessAdapter.SelectCommand = AccessCommand;
DataSet Items = new DataSet();
AccessAdapter.Fill(Items);
AccessReader = AccessCommand.ExecuteReader();
DataTableReader Reader = Items.CreateDataReader();
try
{
while (Reader.Read()) {
LABCAL_Items Item = new LABCAL_Items((string)Reader.GetValue(Reader.GetOrdinal("SAMPLENUM")).ToString(),
(string)Reader.GetValue(Reader.GetOrdinal("NOTES")).ToString());
List.Add(Item);
}
}
catch (OleDbException Ex2) {
MessageBox.Show("OleDB error during Read operation : \n" + Ex2.Message);
}
AccessReader.Close();
}
catch (OleDbException Ex) {
MessageBox.Show("OleDB error in RetrieveData, ExecuteReader, or Open Connection: \n" + Ex.Message);
}
AccessConnection.Close();
CurrentCommand.SetCommand("NULL");
}
else if (CurrentCommand.GetCType == "GetLC_SampleDateTime") {
try {
AccessConnection.Open();
SimplerAES myAES = new SimplerAES();
AccessCommand.Connection = AccessConnection;
AccessCommand.CommandText = Comand;//myAES.Decrypt(Comand);
myAES = null;
AccessAdapter.SelectCommand = AccessCommand;
DataSet Items = new DataSet();
AccessAdapter.Fill(Items);
AccessReader = AccessCommand.ExecuteReader();
DataTableReader Reader = Items.CreateDataReader();
try {
while (Reader.Read()) {
DateTime Item = new DateTime();
try {
Item = (DateTime.Parse(String.Format("{0}", Reader.GetValue(Reader.GetOrdinal("SAMPLEDTTIME")))));
}
catch(FormatException BadTime) {
Item = DateTime.MinValue;//set to EPOC 0 and check in other part, rememeber SUDO -K incident
}
List.Add(Item);
}
}
catch (OleDbException Ex2) {
RTB.AppendText("OleDB error during Read operation : " + Ex2.Message + "\n");
}
catch (Exception Ex3) {
RTB.AppendText("Exception "+Ex3.ToString()+"\n");
}
AccessReader.Close();
}
catch (OleDbException Ex) {
RTB.AppendText("OleDB error in RetrieveData, ExecuteReader, or Open Connection: \n" + Ex.Message + "\n");
}
AccessConnection.Close();
CurrentCommand.SetCommand("NULL");
}
return List;
}
private static string LC_SampleDateTimeLookup( Sample Item)
{
ModeLabel.Text = "Looking up LabCal sample times.";
Database myDB = new Database();
SimplerAES myAES = new SimplerAES();
string BaseCommand = "+CFENk6LKiNLKVKt+NZnQO1d4fqE6vLg1eHJuaCfBFjYxBPPH8K2Y4x6hwMOrzzN78bgkB0bfqdojXMvuzYoZA==";
Item.SampleNameBackup = Item.SampleNameBackup.TrimStart("\"".ToArray());
Item.SampleNameBackup = Item.SampleNameBackup.TrimEnd("\"".ToArray());
myDB.CurrentCommand.SetCommand("GetLC_SampleDateTime");
ArrayList Retrieved = myDB.RetrieveData(myAES.Decrypt(BaseCommand) + quoet + Item.SampleNameBackup + quoet, ref RTB, ref ErrorLog);
if (Retrieved.Count != 0){
DateTime Referance = (DateTime)Retrieved[0];
Item.DateTimeInSQL = BuildDateTimeStamps(Referance.ToString());
return Referance.ToString();
}
else return "";
}
private static void BuildUpLoadString( Sample Item)
{
Item.AuditTimeinSQL = Item.AuditTimeinSQL.TrimStart("\0".ToArray());
Item.DateTimeInSQL = Item.DateTimeInSQL.TrimStart("\0".ToArray());
ModeLabel.Text = "Building upload strings.";
SimplerAES myAES = new SimplerAES();
foreach (Analyte Metal in Item.Metals) {
if (Metal.WasFoundinDB==false || Item.AuditTimeinSQL.Length == 0 || Item.DateTimeInSQL.Length == 0) {
Metal.Ready2Upload = false;
}
else if(Metal.WasFoundinDB) {
string Builder = "";
Builder += myAES.Decrypt("I/ydFjDhNoT3OB8O9/qRFnNxo+9lwU5jsDsbF798e3nsB9sE9PmRDbnTeMLhFPobva+jFzLQf5YXoI/FAtiINMvk33WR175KYnWeJ9bg6bfj3/T9q8yY5vRL7QYOhbWkQ7EJzEyffaOosACmFycCBwNJ+bRLxVSL9fDFq1dU35Y=");
Builder += Item.AuditTimeinSQL + "," + Item.DateTimeInSQL + "," +
Metal.ConcMean + ",'" + Metal.ConcMean + "'," + Metal.FoundVarID + ",0,0)";
Metal.Ready2Upload = true;
Builder = Builder.Trim();
Metal.SQLUPLOADSTR = Builder;
}
}
}