using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using CMS.Controls;
using CMS.GlobalHelper;
using CMS.SiteProvider;
using CMS.PortalControls;


public partial class CMSWebParts_AIS_EquipmentFilter : CMSAbstractQueryFilterControl
{
public bool FilterByQuery = true;

/// <summary>
/// Child control creation.
/// </summary>
protected override void OnInit(EventArgs e)
{
SetupControl();
base.OnInit(e);
}


/// <summary>
/// Pre render event.
/// </summary>
protected override void OnPreRender(EventArgs e)
{
// Set filter settings
if (!RequestHelper.IsPostBack())
{
if(this.FilterByQuery){
GetFilter();
}
}

SetFilter();
base.OnPreRender(e);
}


/// <summary>
/// Setup the inner controls.
/// </summary>
private void SetupControl()
{
if (this.StopProcessing)
{
this.Visible = false;
}
else if (!RequestHelper.IsPostBack())
{
this.ddlCategory.Visible = false;
this.ddlManufacturer.Visible = false;
// Load Price options into filter drop-down list
InitializePrice();
InitializeCertified();
// Load manufacturer options into filter drop-down list
if (!QueryHelper.GetString("cat", "").Equals(String.Empty))
{
this.ddlManufacturer.Visible = true;
InitializeManufacturers();

}
// Load Category options into filter drop-down list
if (!QueryHelper.GetString("manc", "").Equals(String.Empty))
{
this.ddlCategory.Visible = true;
InitializeCategory();
}
}
}

/// <summary>
/// Ensures all manufacturers are loaded within drop-down list as filter options.
/// </summary>
private void InitializeManufacturers()
{
string cs = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
//ahovingh@bizstream.com - BZS - 2-15-2012 - changed the query so that this pulled from SharePoint rather than from Kentico
//string sql = "SELECT ManufacturerName, ManufacturerCode FROM View_CUSTOM_EquipmentManufacturers WHERE ManufacturerCode IN (SELECT eqManufacturer FROM View_Custom_EquipmentUsed_Joined WHERE CategorySEOFriendlyName='" + QueryHelper.GetString("cat", "") + "') ORDER BY ManufacturerName";
string sql = "SELECT Name, Code, MakeNameForTheWeb FROM View_CUSTOM_EquipmentManufacturers_v2 WHERE Code IN (SELECT eqManufacturer FROM View_Custom_EquipmentUsed_Joined WHERE CategorySEOFriendlyName='" + QueryHelper.GetString("cat", "") + "') ORDER BY Name";
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cs);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, cn);
cn.Open();
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();

this.ddlManufacturer.Items.Add(new ListItem("By Manufacturer", String.Empty));
while (dr.Read())
{
//ahovingh@bizstream.com - BZS - 2-15-2012 - changed the query so that this pulled from SharePoint rather than from Kentico
//this.ddlManufacturer.Items.Add(new ListItem(dr["ManufacturerName"].ToString(), dr["ManufacturerCode"].ToString()));
this.ddlManufacturer.Items.Add(new ListItem(dr["Name"].ToString(), dr["Code"].ToString()));
}
dr.Close();
cn.Close();
}


/// <summary>
/// Ensures all categories are loaded within drop-down list as filter options.
/// </summary>
private void InitializeCategory()
{
string cs = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
//ahovingh@bizstream.com - BZS - 1-18-2012 - optimized code to use an inner join rather than a sub select.
//string sql = "SELECT CategoryName, CategorySEOFriendlyName FROM View_CUSTOM_EquipmentCategories WHERE CategorySEOFriendlyName IN (SELECT eqCategoryName FROM View_Custom_EquipmentUsed_Joined WHERE eqManufacturer='" + QueryHelper.GetString("manc", "") + "')
//ahovingh@bizstream.com - BZS - 2-15-2012 - changed the query so that this pulled from SharePoint rather than from Kentico
//string sql = "SELECT DISTINCT C.CategoryName ,C.CategorySEOFriendlyName FROM [kentico].[dbo].View_CUSTOM_EquipmentCategories C INNER JOIN [kentico].[dbo].View_CUSTOM_EquipmentUsed_Joined J ON C.CategorySEOFriendlyName = J.eqCategoryName WHERE J.eqManufacturer = '" + QueryHelper.GetString("manc", "") + "' ORDER BY C.CategoryName"; ORDER BY CategoryName";

string sql = "SELECT DISTINCT C.Name ,C.CategoryNameForTheWeb, C.CategoryUrlFriendlyName " +
"FROM [kentico].[dbo].View_CUSTOM_EquipmentCategories_v2 C " +
"INNER JOIN [kentico].[dbo].View_CUSTOM_EquipmentUsed_Joined J ON " +
"C.CategoryNameForTheWeb = J.eqCategoryName " +
"WHERE J.eqManufacturer = '" + QueryHelper.GetString("manc", "") +
"' ORDER BY C.Name";

//string sql = "SELECT C.CategoryName ,C.CategorySEOFriendlyName FROM [kentico].[dbo].View_CUSTOM_EquipmentCategories C ORDER BY C.CategoryName";
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(cs);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, cn);
cn.Open();
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();

this.ddlCategory.Items.Add(new ListItem("By Category", String.Empty));
while (dr.Read())
{
//ahovingh@bizstream.com - BZS - 2-15-2012 - changed the query so that this pulled from SharePoint rather than from Kentico
//this.ddlCategory.Items.Add(new ListItem(dr["CategoryName"].ToString(),dr["CategorySEOFriendlyName"].ToString()));
this.ddlCategory.Items.Add(new ListItem(dr["Name"].ToString(),dr["CategoryUrlFriendlyName"].ToString()));
}
dr.Close();
cn.Close();
}


/// <summary>
/// Ensures all prices are loaded within drop-down list as filter options.
/// </summary>
private void InitializePrice()
{
// Initialize Price
int[] prices = new int[]{5000, 10000, 15000, 20000, 25000, 50000, 100000, 150000, 200000, 250000, 300000, 350000, 400000};
this.ddlPrice.Items.Add(new ListItem("By Price", "0"));
foreach (int p in prices)
{
this.ddlPrice.Items.Add(new ListItem(String.Format("{0:C}",p), p.ToString()));
}
}


/// <summary>
/// Ensure all certification types are loaded within drop-down list
/// </summary>
private void InitializeCertified()
{
string[,] certs = { {"By Certification", "0"},{"AIS Certified", "AIS"},{"Komatsu Certified", "KOMATSU"} };
for (int i = 0; i < certs.Length/2; i++)
{
this.ddlCertified.Items.Add(new ListItem(certs[i,0], certs[i,1]));
}
}


/// <summary>
/// Generates WHERE condition based on current selection.
/// </summary>
private void SetFilter()
{
string where = null;
string order = null;

// Generate WHERE condition based on price selected
if (!this.ddlPrice.SelectedValue.Equals(String.Empty))
{
int p = ValidationHelper.GetInteger(this.ddlPrice.SelectedValue, 0);
if (p > 0)
{
where = "(eqPrice IS NULL OR eqPrice <= " + p + ")";
//order = "eqPrice ASC, ";
}
}

if (!this.ddlCertified.SelectedValue.Equals(String.Empty))
{
if (this.ddlCertified.SelectedIndex > 0)
{
if (where != null)
{
where += " AND ";
}

switch (this.ddlCertified.SelectedValue.ToUpper())
{
case "AIS":
where += "(eqIsAISCertified = 1)";
break;
case "KOMATSU":
where += "(eqIsKomatsuCertified = 1)";
break;
}
}
}

if (!this.ddlCategory.SelectedValue.Equals(String.Empty))
{
if (where != null)
{
where += " AND ";
}
where += "(CategorySEOFriendlyName IS NULL OR CategorySEOFriendlyName = '" + ValidationHelper.GetString(this.ddlCategory.SelectedValue, String.Empty) + "')";
//order += "eqCategoryName DESC, ";
}

if (!this.ddlManufacturer.SelectedValue.Equals(String.Empty))
{
if (where != null)
{
where += " AND ";
}
where += "(eqManufacturer IS NULL OR eqManufacturer = '" + ValidationHelper.GetString(this.ddlManufacturer.SelectedValue, String.Empty) + "')";
//order += "ManufacturerName DESC, ";
}

if (where != null)
{
// Set where condition
this.WhereCondition = where;
}

if (order != null)
{
// Set orderBy condition (removes trailing ,)
this.OrderBy = order.Substring(0, order.Length-2);
}

//this.lblWhere.Text = this.WhereCondition;
this.RaiseOnFilterChanged();
}

/// <summary>
/// Sets controls to current filter settings
/// </summary>

private void GetFilter()
{
string cat = QueryHelper.GetString("fcat", String.Empty);
string man = QueryHelper.GetString("fman", String.Empty);
string prc = QueryHelper.GetString("fprc", String.Empty);
string cer = QueryHelper.GetString("fcrt", String.Empty);

if (!cat.Equals(String.Empty))
{
this.ddlCategory.SelectedValue = cat;
}

if (!man.Equals(String.Empty))
{
this.ddlManufacturer.SelectedValue = man;
}

if (!prc.Equals(String.Empty))
{
this.ddlPrice.SelectedValue = prc;
}

if (!cer.Equals(string.Empty))
{
this.ddlCertified.SelectedValue = cer;
}
}

protected void btnFilter_Click(object sender, EventArgs e)
{
if (this.FilterByQuery)
{
// Handle all query parameters
string url = UrlHelper.RawUrl;

url = UrlHelper.RemoveParameterFromUrl(url, "fcat");
url = UrlHelper.RemoveParameterFromUrl(url, "fman");
url = UrlHelper.RemoveParameterFromUrl(url, "fprc");
url = UrlHelper.RemoveParameterFromUrl(url, "fcrt");

if (this.ddlCategory.SelectedValue != String.Empty)
{
url = UrlHelper.AddParameterToUrl(url, "fcat", this.ddlCategory.SelectedValue);
}

if (this.ddlManufacturer.SelectedValue != String.Empty)
{
url = UrlHelper.AddParameterToUrl(url, "fman", this.ddlManufacturer.SelectedValue);
}

if (this.ddlPrice.SelectedValue != String.Empty)
{
url = UrlHelper.AddParameterToUrl(url, "fprc", this.ddlPrice.SelectedValue);
}

if (this.ddlCertified.SelectedValue != String.Empty)
{
url = UrlHelper.AddParameterToUrl(url, "fcrt", this.ddlCertified.SelectedValue);
}
// Redirect with new query parameters
UrlHelper.Redirect(url);
}
else
{
// Set filter settings
SetFilter();
}
}
}