private void PrepareAdvanced()
{
DropDownList Advanced_CompanyType = (DropDownList)ReSearchAdvanced.FindControl("Advanced_CompanyType");
DropDownList Advanced_ContactType = (DropDownList)ReSearchAdvanced.FindControl("Advanced_ContactType");
DropDownList Advanced_Estimate = (DropDownList)ReSearchAdvanced.FindControl("Advanced_Estimate");
DropDownList Advanced_Category = (DropDownList)ReSearchAdvanced.FindControl("Advanced_Category");
Fill_Sectors(Advanced_CompanyType);
Fill_ContactType(Advanced_ContactType);
Fill_Evaluation(Advanced_Estimate);
Advanced_Category.DataTextField = "Description";
Advanced_Category.DataValueField = "id";
Advanced_Category.DataSource = DatabaseConnection.CreateDataset("SELECT ID,DESCRIPTION FROM CRM_CONTACTCATEGORIES WHERE (FLAGPERSONAL=0 OR (FLAGPERSONAL=1 AND CREATEDBYID=" + UC.UserId + "))");
Advanced_Category.DataBind();
Advanced_Category.Items.Insert(0, Root.rm.GetString("CRMcontxt53"));
Advanced_Category.Items[0].Value = "0";
CompaniesRep.Visible = false;
ReSearchSimple.Visible = false;
ReSearchAdvanced.Visible = true;
}
public void btn_Click(object sender, EventArgs e)
{
StringBuilder sqlMaster = new StringBuilder();
StringBuilder sqlString = new StringBuilder();
bool avBool = false;
sqlMaster.Append("SELECT BASE_COMPANIES.ID,BASE_COMPANIES.COMPANYNAME, BASE_COMPANIES.INVOICINGADDRESS, BASE_COMPANIES.INVOICINGCITY, BASE_COMPANIES.INVOICINGSTATEPROVINCE, BASE_COMPANIES.INVOICINGZIPCODE, BASE_COMPANIES.PHONE, BASE_COMPANIES.EMAIL, BASE_COMPANIES.SALESPERSONID, BASE_COMPANIES.OWNERID FROM BASE_COMPANIES ");
sqlMaster.Append("LEFT OUTER JOIN ACCOUNT ON BASE_COMPANIES.OWNERID = ACCOUNT.UID ");
sqlMaster.AppendFormat("WHERE LIMBO=0 ");
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_CompanyName")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.COMPANYNAME LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_CompanyName")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Address")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.INVOICINGADDRESS LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTADDRESS LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSEADDRESS LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Address")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_City")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.INVOICINGCITY LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTCITY LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSECITY LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_City")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_State")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.INVOICINGSTATEPROVINCE LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTSTATEPROVINCE LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSESTATEPROVINCE LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_State")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Zip")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.INVOICINGZIPCODE LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTZIPCODE LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSEZIPCODE LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Zip")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Zip")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.INVOICINGZIPCODE LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTZIPCODE LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSEZIPCODE LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Zip")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Phone")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.PHONE LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTPHONE LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSEPHONE LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Phone")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Fax")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.FAX LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTFAX LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSEFAX LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Fax")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Email")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.EMAIL LIKE '%{0}%' OR BASE_COMPANIES.SHIPMENTEMAIL LIKE '%{0}%' OR BASE_COMPANIES.WAREHOUSEEMAIL LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Email")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Site")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.WEBSITE LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Site")).Text);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Code")).Text.Length > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.COMPANYCODE LIKE '%{0}%') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Code")).Text);
avBool = true;
}
if (((DropDownList)ReSearchAdvanced.FindControl("Advanced_CompanyType")).SelectedIndex > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.COMPANYTYPEID = '{0}') AND ", ((DropDownList)ReSearchAdvanced.FindControl("Advanced_CompanyType")).SelectedValue);
avBool = true;
}
if (((DropDownList)ReSearchAdvanced.FindControl("Advanced_ContactType")).SelectedIndex > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.CONTACTTYPEID = '{0}') AND ", ((DropDownList)ReSearchAdvanced.FindControl("Advanced_ContactType")).SelectedValue);
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Billed")).Text.Length > 0)
{
RadioButtonList Advanced_BillCheck = (RadioButtonList)ReSearchAdvanced.FindControl("Advanced_BillCheck");
switch (Advanced_BillCheck.SelectedValue)
{
case "1":
sqlString.AppendFormat("(BASE_COMPANIES.BILLED = '{0}') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Billed")).Text);
break;
case "2":
sqlString.AppendFormat("(BASE_COMPANIES.BILLED < '{0}') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Billed")).Text);
break;
case "3":
sqlString.AppendFormat("(BASE_COMPANIES.BILLED > '{0}') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Billed")).Text);
break;
}
avBool = true;
}
if (((TextBox)ReSearchAdvanced.FindControl("Advanced_Employees")).Text.Length > 0)
{
RadioButtonList Advanced_EmployeesCheck = (RadioButtonList)ReSearchAdvanced.FindControl("Advanced_EmployeesCheck");
switch (Advanced_EmployeesCheck.SelectedValue)
{
case "1":
sqlString.AppendFormat("(BASE_COMPANIES.BILLED = '{0}') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Employees")).Text);
break;
case "2":
sqlString.AppendFormat("(BASE_COMPANIES.BILLED < '{0}') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Employees")).Text);
break;
case "3":
sqlString.AppendFormat("(BASE_COMPANIES.BILLED > '{0}') AND ", ((TextBox)ReSearchAdvanced.FindControl("Advanced_Employees")).Text);
break;
}
avBool = true;
}
if (((DropDownList)ReSearchAdvanced.FindControl("Advanced_Estimate")).SelectedIndex > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.ESTIMATE = {0}) AND ", ((DropDownList)ReSearchAdvanced.FindControl("Advanced_Estimate")).SelectedValue);
avBool = true;
}
if (((DropDownList)ReSearchAdvanced.FindControl("Advanced_Category")).SelectedIndex > 0)
{
sqlString.AppendFormat("(BASE_COMPANIES.CATEGORIES LIKE '%|{0}|%') AND ", ((DropDownList)ReSearchAdvanced.FindControl("Advanced_Category")).SelectedValue);
avBool = true;
}
string queryGroup = GroupsSecure("BASE_COMPANIES.GROUPS");
if (queryGroup.Length > 0)
{
sqlString.AppendFormat("({0}) AND ", queryGroup);
}
if (UC.Zones.Length > 0)
{
sqlString.AppendFormat(" ({0}) AND ", ZoneSecure("BASE_COMPANIES.COMMERCIALZONE"));
}
if (sqlString.ToString().Length > 0)
{
string sqlAv = sqlString.ToString();
if (avBool)
{
sqlAv = sqlAv.Substring(0, sqlAv.Length - 4);
}
CompaniesRep.DataSource = DatabaseConnection.CreateDataset(sqlMaster.ToString() + " AND (" + sqlAv + ")" + " ORDER BY BASE_COMPANIES.COMPANYNAME");
}
else
{
CompaniesRep.DataSource = DatabaseConnection.CreateDataset(sqlMaster.ToString() + " ORDER BY BASE_COMPANIES.COMPANYNAME");
}
CompaniesRep.DataBind();
if (CompaniesRep.Items.Count > 0)
{
CompaniesRep.Visible = true;
ReSearchAdvanced.Visible = false;
ReSearchSimple.Visible = true;
}
else
{
CompaniesRep.Visible = false;
}
}