protected void Page_Load(object sender, EventArgs e) { string strCountry = ((CareerCruisingWeb.PageBase.SuperBase)Page).AbbreviatedCountryCode().ToString(); strDistanceUnit = (strCountry == "US") ? "miles" : "km"; pnlCDOFinder.Visible = false; strOccNumber=CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("occ"); // Main search SQL string strSQL = ""; string strCitySQL = ""; string SuffixCode = "_" + UserLanguage.Substring(0,2); string subOppCat = ""; if ((SearchType == "1") && (OppCat != "")&&(OppCat!=null)) { subOppCat = " and OppCatID=" + OppCat; } switch (SearchType)// { case "1": strPageName = "OpportunitySearchResults"; if (strOccNumber != "" && strOccNumber != null) { strSQL = "select distinct OpportunityID,(Case when IsCurrent=1 then 'CompanyProfile.aspx' else 'DevPotentialProgram.aspx' end) as linkUrl, OpportunityID as TransferedOppID, CompanyStatusID, OppName as program,'" + UserLanguage + "' as UILanguage,'" + LoginID + "' as LoginID, OppStatusDescr" + SuffixCode + " as Status, CompanyName, CompanyName_Student, v.CompanyID, OppCatName" + SuffixCode + " as Type from Con_CareerDevOppView v INNER JOIN "; strSQL += " Con_CareerCoaches AS cc ON cc.PartnerUserID = v.PartnerUserID where v.ConSysID=" + ConSysID + " AND cc.OccNumber =" + strOccNumber + subOppCat; strSQL += " UNION SELECT DISTINCT v.OpportunityID,(Case when IsCurrent=1 then 'CompanyProfile.aspx' else 'DevPotentialProgram.aspx' end) as linkUrl, OpportunityID as TransferedOppID, CompanyStatusID, OppName as program,'" + UserLanguage + "' as UILanguage,'" + LoginID + "' as LoginID, OppStatusDescr" + SuffixCode + " as Status, CompanyName, CompanyName_Student, v.CompanyID, OppCatName" + SuffixCode + " as Type FROM Con_CompanyCareers AS cc INNER JOIN Con_CareerDevOppView AS v ON cc.CompanyID = v.CompanyID "; strSQL += " where v.ConSysID=" + ConSysID + " AND cc.OccNumber =" + strOccNumber + subOppCat; strCitySQL = "select distinct LocCity, LocState from Con_CareerDevOppView v INNER JOIN "; strCitySQL += " Con_CareerCoaches AS cc ON cc.PartnerUserID = v.PartnerUserID where v.ConSysID=" + ConSysID + " AND cc.OccNumber =" + strOccNumber + " and LocCity is not null and LocState is not null " + subOppCat ; strCitySQL += " UNION SELECT Distinct LocCity, LocState FROM Con_CompanyCareers AS cc INNER JOIN Con_CareerDevOppView AS v ON cc.CompanyID = v.CompanyID "; strCitySQL += " where v.ConSysID=" + ConSysID + " AND cc.OccNumber =" + strOccNumber + " and LocCity is not null and LocState is not null " + subOppCat + " order by LocCity, LocState"; } else { strSQL = "select distinct OpportunityID, (Case when IsCurrent=1 then 'CompanyProfile.aspx' else 'DevPotentialProgram.aspx' end) as linkUrl, OpportunityID as TransferedOppID, CompanyStatusID, OppName as program,'" + UserLanguage + "' as UILanguage,'" + LoginID + "' as LoginID, OppStatusDescr" + SuffixCode + " as Status, CompanyName, CompanyName_Student, CompanyID, OppCatName" + SuffixCode + " as Type from "; strSQL += " Con_CareerDevOppView v where v.ConSysID=" + ConSysID + subOppCat; strCitySQL = "Select Distinct LocCity, LocState from Con_CareerDevOppView where LocCity is not null and LocState is not null " + subOppCat + " and ConSysID=" + ConSysID + " order by LocCity, LocState"; } pnlCDOFinder.Visible = true; break; case "2": strPageName = "OpportunityIndustrySearchResult"; strSQL = "select distinct OpportunityID, (Case when IsCurrent=1 then 'CompanyProfile.aspx' else 'DevPotentialProgram.aspx' end) as linkUrl, OpportunityID as TransferedOppID, CompanyStatusID, v.CompanyID, '" + UserLanguage + "' as UILanguage, '" + LoginID + "' as LoginID, Oppname as program, CompanyName, CompanyName_Student, OppCatName" + SuffixCode + " as Type, OppStatusDescr" + SuffixCode + " as Status from Con_CareerDevOppView v join Con_CompanyIndustries i on i.CompanyID=v.CompanyID where i.IndustryID=" + OppCat + " and v.ConSysID=" + ConSysID; strCitySQL = "Select Distinct LocCity, LocState from Con_CareerDevOppView v join Con_CompanyIndustries i on i.CompanyID=v.CompanyID where v.LocCity is not null and v.LocState is not null and v.ConSysID=" + ConSysID + " and i.IndustryID=" + OppCat + " order by LocCity, LocState"; pnlCDOFinder.Visible = true; break; case "3": strPageName = "OpportunityKeywordResults"; strSQL = "SELECT DISTINCT OpportunityID,(Case when IsCurrent=1 then 'CompanyProfile.aspx' else 'DevPotentialProgram.aspx' end) as linkUrl, OpportunityID as TransferedOppID, CompanyStatusID, v.CompanyID, '" + UserLanguage + "' as UILanguage, '" + LoginID + "' as LoginID, Oppname as program , CompanyName, CompanyName_Student, OppCatName" + SuffixCode + " as Type, OppStatusDescr" + SuffixCode + " as Status from Con_CareerDevOppView v where ConSysID=" + ConSysID + " and OppName like '%" + OppCat + "%'"; strCitySQL = "Select Distinct LocCity, LocState from Con_CareerDevOppView where LocCity is not null and LocState is not null and OppName like '%" + OppCat + "%' and ConSysID=" + ConSysID + " order by LocCity, LocState"; pnlCDOFinder.Visible = true; break; } if (ToolType == "3") strPageName = "OpportunitySearchResult"; if (HeaderBGColor == "" || HeaderBGColor == null) HeaderBGColor = "#A2B5CD"; strSearchType = SearchType; string strOppName = CareerCruisingWeb.CCLib.Common.DataAccess.GetValue("Select CDO_ShortAcronym" + SuffixCode + " from Con_Systems where ConSysID=" + ConSysID).ToString(); lblOppName.Text = strOppName; if (City.Length > 0 && State.Length > 0) strSQL += " and LocCity='" + City + "' and LocState='" + State + "'"; string strCompanyIDsinDistance = ""; if (SearchType == "1") { string rdValue = rdlRange.SelectedValue.ToString(); string strCompanyZipCode = ""; if ((rdValue != "") && (rdValue != "All")) {// && strCompanyIDs != "") { string strSQLZipCode = "SELECT a.POSTALCODE FROM SalesLogix.sysdba.ACCOUNT AS acc INNER JOIN "; strSQLZipCode += " SalesLogix.sysdba.ACCWEBACCESS AS web ON web.ACCOUNTID = acc.ACCOUNTID INNER JOIN SalesLogix.sysdba.ADDRESS AS a ON a.ADDRESSID = acc.ADDRESSID "; strSQLZipCode += " where web.SchoolID=" + SchoolID; string strZipCode = CareerCruisingWeb.CCLib.Cache.GetCachedValue("ZipCodeSchool_" + SchoolID, strSQLZipCode).ToString(); strSQLZipCode = "Select Distinct CompanyID, locPostalZip from Con_CareerDevOppView where LocCity is not null and LocState is not null " + subOppCat + " and ConSysID=" + ConSysID; DataTable dtDistanceCompanyIDs = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strSQLZipCode); if (dtDistanceCompanyIDs.Rows.Count > 0) { for (int k = 0; k < dtDistanceCompanyIDs.Rows.Count; k++) { if (dtDistanceCompanyIDs.Rows[k]["locPostalZip"].ToString() != "" && dtDistanceCompanyIDs.Rows[k]["locPostalZip"].ToString() != null) { CareerCruisingWeb.CCLib.Common.AddrLookup.DistanceTypes myDistanceType = (strCountry == "US") ? CareerCruisingWeb.CCLib.Common.AddrLookup.DistanceTypes.Miles : CareerCruisingWeb.CCLib.Common.AddrLookup.DistanceTypes.Kilometeres; strCompanyZipCode = dtDistanceCompanyIDs.Rows[k]["locPostalZip"].ToString(); if (strCountry == "US") { strCompanyZipCode = (strCompanyZipCode.Length > 5) ? strCompanyZipCode.Substring(0, 5) : strCompanyZipCode; strZipCode = (strZipCode.Length > 5) ? strZipCode.Substring(0, 5) : strZipCode; } else { strCompanyZipCode = (strCompanyZipCode.Length.ToString() == "6") ? strCompanyZipCode.Substring(0, 3) + " " + strCompanyZipCode.Substring(3, 3) : strCompanyZipCode; } double strActualDistance = CareerCruisingWeb.CCLib.Common.AddrLookup.CalculateDistance(strZipCode, strCompanyZipCode, myDistanceType); if (strActualDistance <= double.Parse(rdValue)) { strCompanyIDsinDistance += (k != dtDistanceCompanyIDs.Rows.Count - 1) ? dtDistanceCompanyIDs.Rows[k]["CompanyID"].ToString() + "," : dtDistanceCompanyIDs.Rows[k]["CompanyID"].ToString(); } } } } if (strCompanyIDsinDistance != "") { strSQL += " and v.CompanyID in (" + (strCompanyIDsinDistance.Remove(strCompanyIDsinDistance.Length - 1)) + ") ";// removing extra commas-DA } else { strCompanyIDsinDistance = "NO"; } } } // Handle sorting string strSortText = ""; //string strOrder = ""; string strNextOrder = ""; if (Order == "DESC") strNextOrder = "ASC"; else strNextOrder = "DESC"; switch (Sort) { case "": case "1": strSortText = " order by OppName " + Order; litProgramSort.Text = strNextOrder; break; case "2": strSortText = " order by CompanyName " + Order + ", OppName"; litCompanySort.Text = strNextOrder; break; case "3": strSortText = " order by OppCatName" + SuffixCode + " " + Order + ", OppName"; litTypeSort.Text = strNextOrder; break; } sqlDataSource.ConnectionString = ConfigurationManager.AppSettings["strConLocal"]; sqlDataSource.SelectCommand = strSQL + strSortText; rptOppList.DataSourceID = "sqlDataSource"; rptOppList.DataBind(); if (rptOppList.Items.Count == 0 || strCompanyIDsinDistance == "NO") { rptOppList.Visible = false; trHeader.Visible = false; trNoMatches.Visible = true; } else { rptOppList.Visible = true; trNoMatches.Visible = false; trHeader.Style["background-color"] = HeaderBGColor; } if (rptOppList.Items.Count < 10) { pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } DataTable dtCities = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strCitySQL); rptTagList.DataSource = dtCities; rptTagList.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { if (PageT != "Cluster") { if ((CommText != "") && (CommText != null) && (ConSysID != "")) { dtCoachSearchResults = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(CommText); if (dtCoachSearchResults.Rows.Count > 0) { rptCoachSearchResults.DataSource = dtCoachSearchResults; rptCoachSearchResults.DataBind(); rptCoachSearchResults.Visible = true; lblNoResultsFound.Visible = false; if (dtCoachSearchResults.Rows.Count < 7) { pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } } else { lblNoResultsFound.Text = CareerCruisingWeb.CCLib.Common.Strings.TextCode(5692); lblNoResultsFound.Visible = true; rptCoachSearchResults.Visible = false; // insert keyword into freetext keyword search log if (PageT == "KW") { string commandText = "INSERT INTO KeywordSearchLog (Keyword,Section,Country) VALUES ('" + KeywordS + "','Career Coach','" + strCountry + "')"; int rowsUpdated = CareerCruisingWeb.CCLib.Common.DataAccess.ExecuteNonQuery(commandText); } pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } } } else { if (dtCoachClusterResults.Rows.Count > 0) { rptCoachSearchResults.DataSource = dtCoachClusterResults; rptCoachSearchResults.DataBind(); rptCoachSearchResults.Visible = true; lblNoResultsFound.Visible = false; if (dtCoachClusterResults.Rows.Count < 6) { pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } } else { lblNoResultsFound.Text = CareerCruisingWeb.CCLib.Common.Strings.TextCode(5692); lblNoResultsFound.Visible = true; rptCoachSearchResults.Visible = false; pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } } }
void GenerateCompanyList(string strCommText) { lblNoResultsFound.Visible = false; dtCompanyInfo = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strCommText); List<CompanyInfo> ListCompanyRow = new List<CompanyInfo>(); if (dtCompanyInfo.Rows.Count > 0) { int n = dtCompanyInfo.Rows.Count; string[] strIndustryS = new string[n]; for (int j = 0; j < dtCompanyInfo.Rows.Count; j++) { string strCompanyid = dtCompanyInfo.Rows[j]["CompanyID"].ToString(); DataTable dtIndustry = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable("select IndustryDescr" + CareerCruisingWeb.CCLib.Common.Strings.SuffixCode() + " from Con_CompanyIndustries AS ci INNER JOIN Con_IndustriesLookup AS il ON ci.CompanyID=" + strCompanyid + " and ci.IndustryID = il.IndustryID"); strIndustryS.SetValue("", j); for (int i = 0; i < dtIndustry.Rows.Count; i++) { if ((i > 0) && (i < dtIndustry.Rows.Count)) { strIndustryS[j] = strIndustryS[j] + ", " + dtIndustry.Rows[i]["IndustryDescr" + CareerCruisingWeb.CCLib.Common.Strings.SuffixCode()].ToString(); } else { strIndustryS[j] = strIndustryS[j] + dtIndustry.Rows[i]["IndustryDescr" + CareerCruisingWeb.CCLib.Common.Strings.SuffixCode()].ToString(); } } CompanyInfo s = new CompanyInfo(); s.CompanyID = strCompanyid; s.CompanyName = dtCompanyInfo.Rows[j]["CompanyName"].ToString(); s.IndustryName = strIndustryS[j]; ListCompanyRow.Add(s); } rptCompanyInfo.DataSource = ListCompanyRow; rptCompanyInfo.DataBind(); rptCompanyInfo.Visible = true; if (dtCompanyInfo.Rows.Count < 7) { pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } } else { lblNoResultsFound.Text = ((CareerCruisingWeb.PageBase.SuperBase)Page).TextCode(5692); lblNoResultsFound.Visible = true; if (PageT.ToUpper() == "KW") { // insert keyword into freetext keyword search log string strSQL = "INSERT INTO KeywordSearchLog (Keyword,Section,Country) VALUES ('" + KeywordS + "','Company search','" + strCountry + "')"; int rowsUpdated = CareerCruisingWeb.CCLib.Common.DataAccess.ExecuteNonQuery(strSQL); } rptCompanyInfo.Visible = false; pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { lblOrderBy.Text = "Name"; btnDisplaySavedSchools.Text = ((CareerCruisingWeb.PageBase.SuperBase)Page).TextCode(7380); btnSearchForSchool.ImageUrl = "/media1/USSchools/CompareSchools/mout_c_srch" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + ".gif"; btnSearchForSchool.Attributes.Add("onmouseover", "this.src='/media1/USSchools/CompareSchools/mover_c_srch" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + ".gif'"); btnSearchForSchool.Attributes.Add("onmouseout", "this.src='/media1/USSchools/CompareSchools/mout_c_srch" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + ".gif'"); pnlSearchTermEntered.Visible = false; pnlNoResultsFound.Visible = false; if (!Convert.ToBoolean(ViewState["IsSavedSchoolSearch"])) pnlSchoolSelect.Visible = false; } pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; }
protected void Page_Load(object sender, EventArgs e) { string strCountry = ((CareerCruisingWeb.PageBase.SuperBase)Page).AbbreviatedCountryCode().ToString(); strDistanceUnit = (strCountry == "US") ? "miles" : "km"; strSearchType = CareerCruisingWeb.CCLib.Common.Strings.FormatUserInput(CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("Indx").ToUpper()); Sort = CareerCruisingWeb.CCLib.Common.Strings.FormatUserInput(CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("S")); Order = (CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("SO") == "") ? "ASC" : CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("SO"); City = CareerCruisingWeb.CCLib.Common.Strings.FormatUserInput(CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("C")); State = CareerCruisingWeb.CCLib.Common.Strings.FormatUserInput(CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("State")); strSearchKeyContent = CareerCruisingWeb.CCLib.Common.Strings.FormatUserInput(CareerCruisingWeb.CCLib.Common.Strings.GetQueryString("iID")); var strSQLCompanyIDs = ""; divBack.Visible = (CallFrom.ToUpper() == "CAREER") ? false : true; switch (strSearchType) { case "": strSQL = "select DISTINCT cp.CompanyID, cp.CompanyID as TRansCompID,cp.companyName from Con_CompanyProfiles cp "; strSQL += " inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID "; strSQL += " where cp.companyID in (select companyID from Con_CompanyCareers where OccNumber=" + OccNumber + " ) and cp.consysid=" + ConSysID + " and cp.IsApproved=1 and cp.CompanyStatusID=3"; strSQLCompanyIDs = "SELECT DISTINCT cp.CompanyID FROM Con_CompanyProfiles as cp where companyID in (select companyID from Con_CompanyCareers where OccNumber=" + OccNumber + " ) and cp.ConSysID=" + ConSysID + " and cp.IsApproved=1 and cp.CompanyStatusID=3"; break; case "1": string strClusterCode = CareerCruisingWeb.CCLib.Common.DataAccess.GetValue("Select ClusterCode from ClusterCodes where ClusterID=" + strSearchKeyContent).ToString(); //strSQL = "select distinct cp.CompanyID, cp.CompanyID as TRansCompID,cp.CompanyName,il.IndustryDescr" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + " as IndustryName from con_CompanyProfiles cp join Con_CompanyCareers c on cp.CompanyID=c.CompanyID join ClustersUS cl on cl.OccNumber=c.OccNumber "; //strSQL += " inner join Con_CompanyIndustries ci on cp.CompanyID=ci.CompanyID inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID INNER JOIN Con_IndustriesLookup il on il.IndustryID=ci.IndustryID "; strSQL = "select distinct cp.CompanyID, cp.CompanyID as TRansCompID,cp.CompanyName from con_CompanyProfiles cp join Con_CompanyCareers c on cp.CompanyID=c.CompanyID join ClustersUS cl on cl.OccNumber=c.OccNumber "; strSQL += " inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID "; strSQL += " where cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and cl." + ClusterType + " like '%" + strClusterCode + "%' "; strSQLCompanyIDs = "select distinct cp.CompanyID from con_CompanyProfiles cp inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID join Con_CompanyCareers c on cp.CompanyID=c.CompanyID join ClustersUS cl on cl.OccNumber=c.OccNumber where cp.ConSysID=" + ConSysID + " and cl." + ClusterType + " like '%" + strClusterCode + "%'"; break; case "2": strSQL = "SELECT DISTINCT cp.CompanyID,cp.CompanyID as TRansCompID, cp.CompanyName,IndustryDescr" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + " as IndustryName FROM Con_CompanyProfiles as cp inner join Con_CompanyIndustries as ci on cp.CompanyID=ci.CompanyID inner join Con_IndustriesLookup il on il.IndustryID=ci.IndustryID inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID "; strSQL += " where cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and cp.IsApproved=1 and ci.IndustryID=" + strSearchKeyContent; strSQLCompanyIDs = "SELECT DISTINCT cp.CompanyID FROM Con_CompanyProfiles as cp inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID inner join Con_CompanyIndustries as ci on cp.CompanyID=ci.CompanyID where cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and cp.IsApproved=1 and ci.IndustryID=" + strSearchKeyContent; break; case "3": //strSQL = "SELECT DISTINCT cp.CompanyID,cp.CompanyID as TRansCompID,cp.CompanyName,IndustryDescr" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + " as IndustryName FROM Con_CompanyProfiles as cp inner join Con_CompanyIndustries as ci on cp.CompanyID=ci.CompanyID inner join Con_IndustriesLookup il on il.IndustryID=ci.IndustryID inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID WHERE cp.IsApproved=1 and cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and (cp.CompanyName LIKE '" + strSearchKeyContent + "%') "; strSQL = "SELECT DISTINCT cp.CompanyID,cp.CompanyID as TRansCompID,cp.CompanyName FROM Con_CompanyProfiles as cp inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID WHERE cp.IsApproved=1 and cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and (cp.CompanyName LIKE '" + strSearchKeyContent + "%') "; strSQLCompanyIDs = "SELECT DISTINCT cp.CompanyID FROM Con_CompanyProfiles as cp inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID WHERE cp.IsApproved=1 and cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and (cp.CompanyName LIKE '" + strSearchKeyContent + "%') "; break; case "4": //strSQL = "SELECT DISTINCT cp.CompanyID,cp.CompanyID as TRansCompID,cp.CompanyName,IndustryDescr" + ((CareerCruisingWeb.PageBase.SuperBase)Page).SuffixCode() + " as IndustryName FROM Con_CompanyProfiles as cp inner join Con_CompanyIndustries as ci on cp.CompanyID=ci.CompanyID inner join Con_IndustriesLookup il on il.IndustryID=ci.IndustryID inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID "; strSQL = "SELECT DISTINCT cp.CompanyID,cp.CompanyID as TRansCompID,cp.CompanyName FROM Con_CompanyProfiles as cp inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID "; strSQL += " where (IsApproved=1) and cp.CompanyStatusID=3 and (cp.CompanyName like '%" + strSearchKeyContent + "%') and (cp.ConSysID=" + ConSysID + ") "; strSQLCompanyIDs = "SELECT DISTINCT cp.CompanyID FROM Con_CompanyProfiles as cp inner join Con_CompanyLocations AS ccl ON cp.CompanyID = ccl.CompanyID WHERE cp.IsApproved=1 and cp.CompanyStatusID=3 and cp.ConSysID=" + ConSysID + " and (cp.CompanyName LIKE '%" + strSearchKeyContent + "%') "; string strInsKeyStrSQL = "INSERT INTO KeywordSearchLog (Keyword,Section,Country) VALUES ('" + strSearchKeyContent + "','Company search','" + strCountry + "')"; CareerCruisingWeb.CCLib.Common.DataAccess.ExecuteNonQuery(strInsKeyStrSQL); break; } string strCitySQL = ""; if (CallFrom.ToUpper() == "SCHOOL") { strPageName = "Con_CompanySearchResult.aspx?"; } if (CallFrom.ToUpper() == "STUDENT") { strPageName = "CompanySearchResult.aspx?"; } if (CallFrom.ToUpper() == "CAREER") { strPageName = "RelatedCompanyList.aspx?OccNumber="+OccNumber; } if (HeaderBGColor == "" || HeaderBGColor == null) HeaderBGColor = "#A2B5CD"; if (City.Length > 0 && State.Length > 0) strSQL += " and LocCity='" + City + "' and LocState='" + State + "'"; DataTable dtCompanyTemp = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strSQLCompanyIDs); string strCompanyIDs = ""; if (dtCompanyTemp.Rows.Count > 0) { for (int i = 0; i < dtCompanyTemp.Rows.Count; i++) { strCompanyIDs += dtCompanyTemp.Rows[i]["CompanyID"].ToString() + ","; } } if ((strCompanyIDs != "") && (strCompanyIDs != null)) { strCompanyIDs = strCompanyIDs.Remove(strCompanyIDs.Length - 1, 1).ToString(); strCitySQL = "Select Distinct LocCity, LocState from Con_CompanyLocations where LocCity is not null and LocState is not null and CompanyID in (" + strCompanyIDs + ") order by LocCity, LocState"; DataTable dtCities = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strCitySQL); rptTagList.DataSource = dtCities; rptTagList.DataBind(); } rdValue = rdlRange.SelectedValue.ToString(); string strCompanyIDsinDistance = ""; string strCompanyZipCode = ""; if ((rdValue != "") && (rdValue != "All") && strCompanyIDs != "") { string strSQLZipCode = "SELECT a.POSTALCODE FROM SalesLogix.sysdba.ACCOUNT AS acc INNER JOIN "; strSQLZipCode += " SalesLogix.sysdba.ACCWEBACCESS AS web ON web.ACCOUNTID = acc.ACCOUNTID INNER JOIN SalesLogix.sysdba.ADDRESS AS a ON a.ADDRESSID = acc.ADDRESSID "; strSQLZipCode += " where web.SchoolID=" + SchoolID; strZipCode = CareerCruisingWeb.CCLib.Cache.GetCachedValue("ZipCodeSchool_" + SchoolID, strSQLZipCode).ToString(); strSQLZipCode = "Select Distinct locPostalZip,CompanyID from Con_CompanyLocations where LocCity is not null and LocState is not null and CompanyID in (" + strCompanyIDs + ")"; DataTable dtDistanceCompanyIDs = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(strSQLZipCode); if (dtDistanceCompanyIDs.Rows.Count > 0) { for (int k = 0; k < dtDistanceCompanyIDs.Rows.Count; k++) { if (dtDistanceCompanyIDs.Rows[k]["locPostalZip"].ToString() != "" && dtDistanceCompanyIDs.Rows[k]["locPostalZip"].ToString() != null) { CareerCruisingWeb.CCLib.Common.AddrLookup.DistanceTypes myDistanceType = (strCountry == "US") ? CareerCruisingWeb.CCLib.Common.AddrLookup.DistanceTypes.Miles : CareerCruisingWeb.CCLib.Common.AddrLookup.DistanceTypes.Kilometeres; strCompanyZipCode = dtDistanceCompanyIDs.Rows[k]["locPostalZip"].ToString(); if (strCountry == "US") { strCompanyZipCode = (strCompanyZipCode.Length > 5) ? strCompanyZipCode.Substring(0, 5) : strCompanyZipCode; strZipCode = (strZipCode.Length > 5) ? strZipCode.Substring(0, 5) : strZipCode; } else { strCompanyZipCode = (strCompanyZipCode.Length.ToString() == "6") ? strCompanyZipCode.Substring(0, 3) + " " + strCompanyZipCode.Substring(3, 3) : strCompanyZipCode; } double strActualDistance = CareerCruisingWeb.CCLib.Common.AddrLookup.CalculateDistance(strZipCode, strCompanyZipCode, myDistanceType); if (strActualDistance <= double.Parse(rdValue)) { strCompanyIDsinDistance += dtDistanceCompanyIDs.Rows[k]["CompanyID"].ToString() + "," ; } } } } if (strCompanyIDsinDistance != "") { strSQL += " and cp.CompanyID in (" + (strCompanyIDsinDistance.Remove(strCompanyIDsinDistance.Length - 1)) + ") "; // removing extra commas-DA } else { strCompanyIDsinDistance = "NO"; } } // Handle sorting string strSortText = ""; string strNextOrder = Order == "DESC" ? "ASC" : "DESC"; switch (Sort) { case "": case "1": strSortText = " order by CompanyName " + Order; litProgramSort.Text = strNextOrder; break; case "3": //strSortText = " order by IndustryName " + Order + ", CompanyName"; //litTypeSort.Text = strNextOrder; break; } sqlDataSource.ConnectionString = ConfigurationManager.AppSettings["strConLocal"]; sqlDataSource.SelectCommand = strSQL + strSortText; rptCompanyList.DataSourceID = "sqlDataSource"; rptCompanyList.DataBind(); if ((strSearchType == "2") || ((CallFrom.ToUpper() == "CAREER"))) { thIndustry.Visible = true; } else { thIndustry.Visible = true; } if ((rptCompanyList.Items.Count == 0) || (strCompanyIDsinDistance == "NO")) { rptCompanyList.Visible = false; trHeader.Visible = false; trNoMatches.Visible = true; } else { trNoMatches.Visible = false; rptCompanyList.Visible = true; trHeader.Visible = true; trHeader.Style["background-color"] = HeaderBGColor; } if (rptCompanyList.Items.Count < 10) { pgParent = (CareerCruisingWeb.PageBase.SuperBase)this.Page; pgParent.IsLongPage = false; } }