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