/// <summary>
/// SearchDataBase
/// </summary>
/// <param name="RLOC"></param>
/// <param name="Surname"></param>
/// <returns></returns>
DiskRecord[] SearchDataBase(string RLOC, string Surname)
{
string outError = "";
ViewBag.OutError = "";
ViewBag.OutError = "";
DiskRecord[] rv = null;
Surname = Surname.ToUpper();
try
{
// now calculate the MD5 connection string
//MySqlConnection Conn = new MySqlConnection(GetDataSourceMySQL());
//Conn.Open();
//string SQL = "";
DateTime tm = DateTime.Now.AddMonths(GetSeachMonths());
DateTime ThreeMonths = new DateTime(tm.Year, tm.Month, 1, 0, 0, 0, 0);
DataSet DS = new DataSet();
MailRecordsBAL objMailRecordsBAL = new MailRecordsBAL("");
List <MailRecordsModel> lstMailRecordsModel = objMailRecordsBAL.SearchDataBase(RLOC, ThreeMonths); //.ToString("yyyy-MM-dd HH:mm")
DataTable dt = Common.ListToDataTable(lstMailRecordsModel);
DS.Tables.Add(dt);
//SQL = "SELECT ID, RLOC, Subject, TicketNumber, Created, Method, DocumentNumber, FormName, FileName FROM MailRecords where RLOC='{0}' and Created > '{1}' and PreprocStatus > 0 order by created";
//SQL = string.Format(SQL, RLOC, ThreeMonths.ToString("yyyy-MM-dd HH:mm"));
//MySqlCommand selectCMD = new MySqlCommand(SQL, Conn);
//MySqlDataAdapter custDA = new MySqlDataAdapter(selectCMD);
//DataSet DS = new DataSet();
//custDA.Fill(DS);
//Conn.Close();
if (DS == null || DS.Tables == null || DS.Tables[0].Rows.Count == 0)
{
logger.Debug("No records found in mailrecords table matching criteria");
// logger.Debug(SQL);
return(rv);
}
// need to replace all angle brackets with <>
string Created = "";
string LastSubject = "";
logger.Debug("Mailrecords found : " + DS.Tables[0].Rows.Count.ToString());
foreach (DataRow myRow in DS.Tables[0].Rows)
{
string Subject = myRow["Subject"].ToString().ToUpper();
string FormName = myRow["FormName"].ToString().ToUpper();
string CreatedTime = myRow["Created"].ToString();
if (!(FormName.IndexOf("INV") != -1 || FormName.IndexOf("CRD") != -1))
{
continue;
}
if (myRow["Created"].ToString() == Created && LastSubject == Subject)
{
// must be a copy record
continue;
}
// fill with latest record
Created = myRow["Created"].ToString();
LastSubject = Subject;
DiskRecord rec = new DiskRecord();
rec.Row = myRow;
Match Ma = Regex.Match(Subject, GetNameCommand());
if (!Ma.Success)
{
continue;
}
string[] Names;
if (Ma.Groups["detail"].ToString().IndexOf("_") != -1)
{
Names = Ma.Groups["detail"].ToString().Split('_');
}
else
{
Names = Ma.Groups["detail"].ToString().Split('/');
}
string NewName = Names[0].Trim();
// if (NewName == Surname)
if (LastSubject.IndexOf(Surname) != -1)
{
//bFound = true;
rec.NameFound = true;
if (m_InvoiceArray == null)
{
m_InvoiceArray = new DataRow[1];
m_InvoiceArray[0] = myRow;
}
else
{
DataRow[] Invoice = new DataRow[m_InvoiceArray.Length + 1];
m_InvoiceArray.CopyTo(Invoice, 0);
Invoice[Invoice.Length - 1] = myRow;
m_InvoiceArray = Invoice;
}
}
if (rv == null)
{
rv = new DiskRecord[1];
rv[0] = rec;
}
else
{
DiskRecord[] Invoice = new DiskRecord[rv.Length + 1];
rv.CopyTo(Invoice, 0);
Invoice[Invoice.Length - 1] = rec;
rv = Invoice;
}
}
if (m_InvoiceArray != null)
{
logger.Debug("Records found for Invoice in DB: " + m_InvoiceArray.Length.ToString() + "");
}
else
{
logger.Debug("Records found for Invoice in DB: zero");
}
}
catch (MySqlException oleExp)
{
//display error details
ViewBag.OutError = "<b>* Error while accessing data</b>.<br />"
+ oleExp.Message + "<br />" + oleExp.Source + "<p />";
logger.Error("Exception in Check Surname : " + oleExp.ToString());
return(rv); // and stop execution
}
catch (Exception objError)
{
//display error details
ViewBag.OutError = "<b>* Error while accessing data</b>.<br />"
+ objError.Message + "<br />" + objError.Source + "<p />";
logger.Error("Exception in Check Surname : " + objError.ToString());
return(rv); // and stop execution
}
return(rv);
}
/// <summary>
/// SearchDataBase
/// </summary>
/// <param name="RLOC"></param>
/// <param name="Surname"></param>
/// <returns></returns>
DiskRecord[] SearchDataBase(string RLOC, string Surname)
{
string outError = "";
ViewBag.OutError = "";
ViewBag.OutError = "";
DiskRecord[] rv = null;
Surname = Surname.ToUpper();
try
{
// now calculate the MD5 connection string
//MySqlConnection Conn = new MySqlConnection(GetDataSourceMySQL());
//Conn.Open();
//string SQL = "";
DateTime tm = DateTime.Now.AddMonths(GetSeachMonths());
DateTime ThreeMonths = new DateTime(tm.Year, tm.Month, 1, 0, 0, 0, 0);
DataSet DS = new DataSet();
MailRecordsBAL objMailRecordsBAL = new MailRecordsBAL("");
List<MailRecordsModel> lstMailRecordsModel = objMailRecordsBAL.SearchDataBase(RLOC, ThreeMonths); //.ToString("yyyy-MM-dd HH:mm")
DataTable dt = Common.ListToDataTable(lstMailRecordsModel);
DS.Tables.Add(dt);
//SQL = "SELECT ID, RLOC, Subject, TicketNumber, Created, Method, DocumentNumber, FormName, FileName FROM MailRecords where RLOC='{0}' and Created > '{1}' and PreprocStatus > 0 order by created";
//SQL = string.Format(SQL, RLOC, ThreeMonths.ToString("yyyy-MM-dd HH:mm"));
//MySqlCommand selectCMD = new MySqlCommand(SQL, Conn);
//MySqlDataAdapter custDA = new MySqlDataAdapter(selectCMD);
//DataSet DS = new DataSet();
//custDA.Fill(DS);
//Conn.Close();
if (DS == null || DS.Tables == null || DS.Tables[0].Rows.Count == 0)
{
logger.Debug("No records found in mailrecords table matching criteria");
// logger.Debug(SQL);
return rv;
}
// need to replace all angle brackets with <>
string Created = "";
string LastSubject = "";
logger.Debug("Mailrecords found : " + DS.Tables[0].Rows.Count.ToString());
foreach (DataRow myRow in DS.Tables[0].Rows)
{
string Subject = myRow["Subject"].ToString().ToUpper();
string FormName = myRow["FormName"].ToString().ToUpper();
string CreatedTime = myRow["Created"].ToString();
if (!(FormName.IndexOf("INV") != -1 || FormName.IndexOf("CRD") != -1))
continue;
if (myRow["Created"].ToString() == Created && LastSubject == Subject)
{
// must be a copy record
continue;
}
// fill with latest record
Created = myRow["Created"].ToString();
LastSubject = Subject;
DiskRecord rec = new DiskRecord();
rec.Row = myRow;
Match Ma = Regex.Match(Subject, GetNameCommand());
if (!Ma.Success)
continue;
string[] Names;
if (Ma.Groups["detail"].ToString().IndexOf("_") != -1)
{
Names = Ma.Groups["detail"].ToString().Split('_');
}
else
{
Names = Ma.Groups["detail"].ToString().Split('/');
}
string NewName = Names[0].Trim();
// if (NewName == Surname)
if (LastSubject.IndexOf(Surname) != -1)
{
//bFound = true;
rec.NameFound = true;
if (m_InvoiceArray == null)
{
m_InvoiceArray = new DataRow[1];
m_InvoiceArray[0] = myRow;
}
else
{
DataRow[] Invoice = new DataRow[m_InvoiceArray.Length + 1];
m_InvoiceArray.CopyTo(Invoice, 0);
Invoice[Invoice.Length - 1] = myRow;
m_InvoiceArray = Invoice;
}
}
if (rv == null)
{
rv = new DiskRecord[1];
rv[0] = rec;
}
else
{
DiskRecord[] Invoice = new DiskRecord[rv.Length + 1];
rv.CopyTo(Invoice, 0);
Invoice[Invoice.Length - 1] = rec;
rv = Invoice;
}
}
if (m_InvoiceArray != null)
logger.Debug("Records found for Invoice in DB: " + m_InvoiceArray.Length.ToString() + "");
else
logger.Debug("Records found for Invoice in DB: zero");
}
catch (MySqlException oleExp)
{
//display error details
ViewBag.OutError = "<b>* Error while accessing data</b>.<br />"
+ oleExp.Message + "<br />" + oleExp.Source + "<p />";
logger.Error("Exception in Check Surname : " + oleExp.ToString());
return rv; // and stop execution
}
catch (Exception objError)
{
//display error details
ViewBag.OutError = "<b>* Error while accessing data</b>.<br />"
+ objError.Message + "<br />" + objError.Source + "<p />";
logger.Error("Exception in Check Surname : " + objError.ToString());
return rv; // and stop execution
}
return rv;
}