Example #1
0
        /// <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 &lt;&gt;

                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);
        }
Example #2
0
        /// <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 &lt;&gt;

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