/// <summary> /// Populate the Report_Object List /// </summary> public void PopulateReportObject() { string strOutput = ""; Company_Report obj_report; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "SELECT DISTINCT Companies.CompanyName, Contacts.ContactName, Contacts.FirstName + ' ' + Contacts.LastName As ContactName2, Contacts.DEFAULT_CountryCode + ' ' + Contacts.DEFAULT_AreaCode + ' ' + Contacts.DEFAULT_Number As TelephoneNumber , Orders.OrderedDateTime, Orders.OrderedBy, Contacts.PrimaryContact FROM dbo.Contacts INNER JOIN dbo.Companies ON Contacts.CompanyID = Companies.CompanyID INNER JOIN dbo.Orders ON Orders.CompanyID = Companies.CompanyID AND Orders.ContactID = Contacts.ContactID WHERE Orders.OrderedDateTime <= DATEADD(mm, -3, GETDATE()) AND Contacts.PrimaryContact = 'Y'"; cmd.Connection = conn; conn.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { obj_report = new Company_Report(); while (sdr.Read()) { //Populate the Object obj_report.CompanyName = sdr["CompanyName"].ToString(); obj_report.ContactName = sdr["ContactName2"].ToString(); obj_report.TelePhoneNumber = sdr["TelephoneNumber"].ToString(); obj_report.OrderedDateTime = sdr["OrderedDateTime"].ToString(); obj_report.OrderedBy = sdr["OrderedBy"].ToString(); //Add it to the Collection Obj_List.Add(obj_report); } } } } }
/// <summary> /// Gets the data from UI. /// </summary> private void GetDataFromUI() { //get existing record GetInitData(); if (modCompanyReport == null) { modCompanyReport = new Company_Report(); isNew = true; } modCompanyReport.DOW = Convert.ToInt32(ddlDOW.SelectedValue); modCompanyReport.TOD = Convert.ToInt32(ddlTOD.SelectedValue); modCompanyReport.SenderRoleId = Convert.ToInt32(ddlRole.SelectedValue); modCompanyReport.SenderEmail = tbSenderEmail.Text; modCompanyReport.SenderName = tbSenderName.Text; }
/// <summary> /// Gets the init data. /// </summary> private void GetInitData() { try { modCompanyReport = bllCompanyReport.GetModel(); LPWeb.BLL.Roles broles = new LPWeb.BLL.Roles(); DataSet dsRole = broles.GetList("1>0 order by Name"); if (dsRole.Tables[0].Rows.Count > 0) { foreach (DataRow dr in dsRole.Tables[0].Rows) { ddlRole.Items.Add(new ListItem(dr["Name"].ToString(), dr["RoleId"].ToString())); } } } catch (Exception exception) { modCompanyReport = null; LPLog.LogMessage(exception.Message); } }
/// <summary> /// ResultSet of Orders giveny by RepID,DateRangeID /// </summary> /// <param name="RepID"></param> /// <param name="DateRangeID"></param> public void getData(string RepID, string DateRangeID) { //string strOutput = ""; Company_Report obj_report; using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStringDeltoneCRM"].ConnectionString; using (SqlCommand cmd = new SqlCommand()) { if (RepID == "ALL") { cmd.CommandText = "SELECT DISTINCT Companies.CompanyName, Contacts.ContactName, Contacts.FirstName + ' ' + Contacts.LastName As ContactName2, Contacts.DEFAULT_CountryCode + ' ' + Contacts.DEFAULT_AreaCode + ' ' + Contacts.DEFAULT_Number As TelephoneNumber , Orders.OrderedDateTime, Orders.OrderedBy, Contacts.PrimaryContact FROM dbo.Contacts INNER JOIN dbo.Companies ON Contacts.CompanyID = Companies.CompanyID INNER JOIN dbo.Orders ON Orders.CompanyID = Companies.CompanyID AND Orders.ContactID = Contacts.ContactID WHERE Orders.OrderedDateTime <= DATEADD(mm, -" + DateRangeID.ToString() + ", GETDATE()) AND Contacts.PrimaryContact = 'Y'"; } else { cmd.CommandText = "SELECT DISTINCT Companies.CompanyName, Contacts.ContactName, Contacts.FirstName + ' ' + Contacts.LastName As ContactName2, Contacts.DEFAULT_CountryCode + ' ' + Contacts.DEFAULT_AreaCode + ' ' + Contacts.DEFAULT_Number As TelephoneNumber , Orders.OrderedDateTime, Orders.OrderedBy, Contacts.PrimaryContact FROM dbo.Contacts INNER JOIN dbo.Companies ON Contacts.CompanyID = Companies.CompanyID INNER JOIN dbo.Orders ON Orders.CompanyID = Companies.CompanyID AND Orders.ContactID = Contacts.ContactID WHERE Orders.OrderedDateTime <= DATEADD(mm, -" + DateRangeID.ToString() + ", GETDATE()) AND Contacts.PrimaryContact = 'Y' AND Companies.OwnershipAdminID = " + RepID; } cmd.Connection = conn; conn.Open(); using (SqlDataReader sdr = cmd.ExecuteReader()) { obj_report = new Company_Report(); while (sdr.Read()) { if (Obj_List.Count == 0) { //Populate the Object and add it to the List obj_report.CompanyName = sdr["CompanyName"].ToString(); obj_report.ContactName = sdr["ContactName2"].ToString(); obj_report.TelePhoneNumber = sdr["TelephoneNumber"].ToString(); obj_report.OrderedDateTime = sdr["OrderedDateTime"].ToString(); obj_report.OrderedBy = sdr["OrderedBy"].ToString(); obj_report.PrimaryContact = sdr["PrimaryContact"].ToString(); Obj_List.Add(obj_report); } else { if (CompanyExsists(sdr["CompanyName"].ToString())) { var obj_record = (from o in Obj_List where o.CompanyName.ToLower() == sdr["CompanyName"].ToString().ToLower() select o).First(); int obj_Index = Obj_List.FindIndex(a => a.CompanyName.ToString().ToLower().Equals(sdr["CompanyName"].ToString().ToLower())); //Fetch the object OrderedDateTime of the Object DateTime obj_DateTimeOrdered = Convert.ToDateTime(obj_record.OrderedDateTime); DateTime sql_DateTimeOrdered = Convert.ToDateTime(sdr["OrderedDateTime"].ToString()); //Compare two Dates int result = DateTime.Compare(sql_DateTimeOrdered, obj_DateTimeOrdered); if (result > 0) { //Remove it the Previous Object from the List Obj_List.RemoveAt(obj_Index); //Update the Object and Add it to the List obj_report = new Company_Report(); obj_report.CompanyName = sdr["CompanyName"].ToString(); obj_report.ContactName = sdr["ContactName2"].ToString(); obj_report.TelePhoneNumber = sdr["TelephoneNumber"].ToString(); obj_report.OrderedDateTime = sdr["OrderedDateTime"].ToString(); obj_report.OrderedBy = sdr["OrderedBy"].ToString(); obj_report.PrimaryContact = sdr["PrimaryContact"].ToString(); Obj_List.Add(obj_report); } } else { //If Company not Exsists then add the Company to the ObjectList obj_report = new Company_Report(); obj_report.CompanyName = sdr["CompanyName"].ToString(); obj_report.ContactName = sdr["ContactName2"].ToString(); obj_report.TelePhoneNumber = sdr["TelephoneNumber"].ToString(); obj_report.OrderedDateTime = sdr["OrderedDateTime"].ToString(); obj_report.OrderedBy = sdr["OrderedBy"].ToString(); obj_report.PrimaryContact = sdr["PrimaryContact"].ToString(); Obj_List.Add(obj_report); } } //strOutput = strOutput + sdr["CompanyName"].ToString() + "|" + sdr["ContactName2"].ToString() + "|" + sdr["TelephoneNumber"].ToString() + "|" + sdr["OrderedDateTime"].ToString() + "|" + sdr["OrderedBy"].ToString() + "~"; } } //int Length = strOutput.Length; //strOutput = strOutput.Substring(0, (Length - 1)); } } //Response.Write(strOutput); }