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