コード例 #1
0
ファイル: checkexist.aspx.cs プロジェクト: eagle2014/Tustena
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Login())
            {
                ClientScript.RegisterStartupScript(this.GetType(), "redirect", "<script>opener.location.href=opener.location.href;self.close();</script>");
            }
            else
            {
                StringBuilder query = new StringBuilder();
                query.Append("SELECT COMPANYNAME, ");
                string[] valori     = Request.QueryString["Company"].Split(' ');
                string   tempQuery  = String.Empty;
                string   tempQuery2 = String.Empty;
                for (int i = 0; i < valori.Length; i++)
                {
                    tempQuery  += "(SELECT COUNT(*) FROM BASE_COMPANIES WHERE COMPANYNAME LIKE '%" + DatabaseConnection.FilterInjection(valori[i]) + "%' AND ID=CONF.ID) + ";
                    tempQuery2 += "COMPANYNAME LIKE '%" + DatabaseConnection.FilterInjection(valori[i]) + "%' OR ";
                }

                query.Append(tempQuery.Substring(0, tempQuery.Length - 2));
                query.AppendFormat(" AS RANK ");

                query.Append("FROM BASE_COMPANIES AS CONF ");
                query.AppendFormat("WHERE LIMBO=0 AND ({0}) AND ", tempQuery2.Substring(0, tempQuery2.Length - 3));
                if (valori.Length > 1)
                {
                    query.AppendFormat("({0})>1 ORDER BY RANK DESC", tempQuery.Substring(0, tempQuery.Length - 2));
                }
                else
                {
                    query.AppendFormat("({0})>0 ORDER BY RANK DESC", tempQuery.Substring(0, tempQuery.Length - 2));
                }

                CompaniesRep.DataSource = DatabaseConnection.CreateDataset(query.ToString());
                CompaniesRep.DataBind();
                if (CompaniesRep.Items.Count > 0)
                {
                    ImgOK.Text = Root.rm.GetString("CRMcontxt83");
                }
                else
                {
                    CompaniesRep.Visible = false;
                    ImgOK.Text           = Root.rm.GetString("CRMcontxt84");
                }
            }
        }
コード例 #2
0
        private void Findcompany(string name, bool full)
        {
            string searchfiltered = StaticFunctions.FilterSearch(name);
            string fullText       = String.Empty;

            if (RadioList1.Items[1].Selected || full)
            {
                fullText = "%";
            }
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("SELECT TOP {0} 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.COMMERCIALZONE, BASE_COMPANIES.SALESPERSONID, BASE_COMPANIES.OWNERID FROM BASE_COMPANIES ", NRes.SelectedValue);
            sb.AppendFormat("WHERE (LIMBO=0 AND ({1})) AND (BASE_COMPANIES.COMPANYNAME LIKE '{0}%' OR BASE_COMPANIES.PHONE LIKE '{0}%' OR BASE_COMPANIES.EMAIL LIKE '{0}%') ", fullText + name, GroupsSecure("BASE_COMPANIES.GROUPS"));
            if (UC.Zones.Length > 0)
            {
                sb.AppendFormat(" AND ({0})", ZoneSecure("BASE_COMPANIES.COMMERCIALZONE"));
            }

            sb.Append("ORDER BY BASE_COMPANIES.COMPANYNAME");
            CompaniesRep.DataSource = DatabaseConnection.CreateDataset(sb.ToString());
            CompaniesRep.DataBind();
        }
コード例 #3
0
        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;
            }
        }