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