<% if (isNull(request.cookies("ckyUserID"))) or (request.cookies("ckyUserID")="") then response.redirect "index.asp" end if QID = request.querystring("QID") strID = request.Form("arrIDM") 'strID = ("arrIDM") arrIDM = split(strID, ",") %> <% 'READ!!!!!!!!!!!!!!! 'whatever changes you made on the normal view can just be copied accross to this page and the coloring disabled 'cept for the query which should pull back only selected items 'note: the strWhere is different 'and after "if QID > 0" then is missing some more stuff 'response.write request.QueryString response.write "" UID = clng(getUserID(request.cookies("ckyUserID"))) if QID > 0 then blnBeat = false strBeatCrit = "" strBeatOrderBy = "" blnDistributionList = false strDistributionListCrit = "" strDistributionListOrderBy = "" strarrLabels = "" strarrFields = "" s = "SELECT tblQuery.refUserID, tblQueryFields.*, tblFields.* From tblQuery, tblQueryFields, tblFields " & _ "WHERE QueryID = refQueryID and tblQueryFields.refFieldsID = tblFields.FieldID AND refQueryID = " & QID & _ " Order by intOrder, QueryFieldsID" 'response.Write s set rst = cn.execute(s) while not rst.eof strarrLabels = strarrLabels & rst("strLabel") & "," if rst("strLabel") = "Beat" then strarrFields = strarrFields & "Beat" & "," blnBeat = true trimTmp = trim(rst("strSort")) if trimTmp <> "" then strBeatOrderBy = " ORDER BY strDescription " & rst("strSort") end if trimTmp = trim(rst("strCriteria")) if trimTmp <> "" then strBeatCrit = replace(rst("strCriteria"), "^~#", "''") isMulti = Instr(1, strBeatCrit, ";", 1) if isMulti > 0 then arrBeatCrit = split(strBeatCrit, ";") strBeatCrit = "" for i = 0 to ubound(arrBeatCrit) strBeatCrit = strBeatCrit & " or (tblBeat.strDescription like '%" & trim(arrBeatCrit(i)) & "%')" next strBeatCrit = " AND (" & right(strBeatCrit, len(strBeatCrit) - 4) & ")" else strBeatCrit = " AND (tblBeat.strDescription like '%" & trim(strBeatCrit) & "%')" end if end if elseif rst("strLabel") = "Distribution List" then strarrFields = strarrFields & "DistributionList" & "," blnDistributionList = true trimTmp = trim(rst("strSort")) if trimTmp <> "" then strDistributionListOrderBy = " ORDER BY strDescription " & rst("strSort") end if trimTmp = trim(rst("strCriteria")) if trimTmp <> "" then strDistributionListCrit = replace(rst("strCriteria"), "^~#", "''") isMulti = Instr(1, strDistributionListCrit, ";", 1) if isMulti > 0 then arrDistributionListCrit = split(strDistributionListCrit, ";") strDistributionListCrit = "" for i = 0 to ubound(arrDistributionListCrit) strDistributionListCrit = strDistributionListCrit & " or (tblDistributionList.strDescription like '%" & trim(arrDistributionListCrit(i)) & "%')" next strDistributionListCrit = " AND (" & right(strDistributionListCrit, len(strDistributionListCrit) - 4) & ")" else strDistributionListCrit = " AND (tblDistributionList.strDescription like '%" & trim(strDistributionListCrit) & "%')" end if end if else strarrFields = strarrFields & rst("strName") & "," strFields = strFields & rst("strName") & ", " offset = instr(1, rst("strName"), " ", 1) trimTmp = trim(rst("strSort")) strOR = "" if trimTmp <> "" then strOrderBy = strOrderBy & left(rst("strName"), offset) & " " & rst("strSort") & ", " end if trimTmp = trim(rst("strCriteria")) if trimTmp <> "" then offset = instr(1, rst("strName"), " ", 1) strCrit = replace(rst("strCriteria"), "^~#", "''") isMulti = Instr(1, strCrit, ";", 1) if isMulti > 0 then arrCrit = split(strCrit, ";") strCrit = "" 'response.write "

" strOR = "" for i = 0 to ubound(arrCrit) strCrit = strCrit & strOR & " (" & left(rst("strName"), offset) & " LIKE '%" & trim(arrCrit(i)) & "%') " strOR = "OR" 'response.write strCrit & "
" next strWhere = strWhere & " AND (" & strCrit & ")" 'WTF!? -> " & right(strCrit, len(strCrit) - 4) & ")" else strWhere = strWhere & " AND " & left(rst("strName"), offset) & " LIKE '%" & trim(strCrit) & "%'" end if end if end if rst.movenext wend rst.close 'if strWhere <> "" then strWhere = " WHERE ContactID IN (" & strID & ") " & strWhere 'end if 'response.Write strWhere if strFields <> "" then strFields = ", " & left(strFields, len(strFields) - 2) end if if strOrderBy <> "" then strOrderBy = " ORDER BY " & left(strOrderBy, len(strOrderBy) - 2) end if if len(strarrFields) > 0 then strarrFields = left(strarrFields, len(strarrFields) - 1) arrFields = split(strarrFields, ",") else dim arrFields() end if if len(strarrLabels) > 0 then strarrLabels = left(strarrLabels, len(strarrLabels) - 1) arrLabels = split(strarrLabels, ",") else dim arrLabels() end if else response.write "Please save the query first!" end if if uBound(arrFields) >= 0 then response.write "" for i = 0 to uBound(arrLabels) response.write "" next response.write "" arrCID = "" if blnBeat OR blnDistributionList then if blnBeat AND blnDistributionList then if ((strBeatCrit = "") AND (strDistributionListCrit = "")) then s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT OUTER JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN (tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID " & _ strWhere & strOrderBy else if ((strBeatCrit <> "") AND (strDistributionListCrit <> "")) then s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblContactDistributionList INNER JOIN tblDistributionList ON tblContactDistributionList.refDistributionListID = tblDistributionList.DistributionListID) INNER JOIN ((tblContactBeat INNER JOIN tblBeat ON tblContactBeat.refBeatID = tblBeat.BeatID) INNER JOIN ((tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN " & _ "(tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID) ON tblContactBeat.refContactID = tblContact.ContactID) ON tblContactDistributionList.refContactID = tblContact.ContactID " & _ strWhere & strBeatCrit & " " & strDistributionListCrit & strOrderBy else if (strBeatCrit <> "") then s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblContactBeat INNER JOIN tblBeat ON tblContactBeat.refBeatID = tblBeat.BeatID) INNER JOIN ((tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN " & _ "(tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID) ON tblContactBeat.refContactID = tblContact.ContactID " & _ strWhere & strBeatCrit & strOrderBy else s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblContactDistributionList INNER JOIN tblDistributionList ON tblContactDistributionList.refDistributionListID = tblDistributionList.DistributionListID) INNER JOIN ((tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN " & _ "(tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID) ON tblContactDistributionList.refContactID = tblContact.ContactID " & _ strWhere & strDistributionListCrit & strOrderBy end if end if end if else if blnBeat then if strBeatCrit = "" then s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT OUTER JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN (tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID " & _ strWhere & strOrderBy else s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblContactBeat INNER JOIN tblBeat ON tblContactBeat.refBeatID = tblBeat.BeatID) INNER JOIN ((tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN " & _ "(tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID) ON tblContactBeat.refContactID = tblContact.ContactID " & _ strWhere & strBeatCrit & strOrderBy end if else if strDistributionListCrit = "" then s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT OUTER JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN (tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID " & _ strWhere & strOrderBy else s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblContactDistributionList INNER JOIN tblDistributionList ON tblContactDistributionList.refDistributionListID = tblDistributionList.DistributionListID) INNER JOIN ((tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN " & _ "(tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID) ON tblContactDistributionList.refContactID = tblContact.ContactID " & _ strWhere & strDistributionListCrit & strOrderBy end if end if end if else s = "SELECT DISTINCT tblContact.ContactID " & strFields & _ " FROM (tblTitle INNER JOIN (tblDesignation INNER JOIN (tblShow RIGHT OUTER JOIN tblContact ON tblShow.ShowID = tblContact.refShowID) ON tblDesignation.DesignationID = tblContact.refDesignationID) ON tblTitle.TitleID = tblContact.refTitleID) INNER JOIN (tblSuburb INNER JOIN (tblLanguage INNER JOIN (tblCity INNER JOIN (tblCategory INNER JOIN (tblCountry INNER JOIN tblPublication ON tblCountry.CountryID = tblPublication.refPhysCountryID) ON tblCategory.CategoryID = tblPublication.refCategoryID) ON tblCity.CityID = tblPublication.refPhysCityID) ON tblLanguage.LanguageID = tblPublication.refLanguageID) ON tblSuburb.SuburbID = tblPublication.refPhysSuburbID) ON tblContact.refPublicationID = tblPublication.PublicationID " & _ strWhere & strOrderBy end if 'response.Write s set rst = cn.execute(s) while not rst.eof x = swapIt(x) arrCID = arrCID & "'" & rst("ContactID") & "', " response.write "" for i = 0 to uBound(arrFields) response.write "" next response.write "" rst.movenext wend rst.close response.write "

" & ucase(arrLabels(i)) & "

" if trim(arrFields(i)) = "Beat" then s = "SELECT tblBeat.strDescription, tblContactBeat.refContactID " & _ "FROM tblBeat INNER JOIN tblContactBeat ON tblBeat.BeatID = tblContactBeat.refBeatID " & _ "WHERE (((tblContactBeat.refContactID)=" & rst("ContactID") & ")) " & strBeatOrderBy set rst2 = cn.execute(s) if not rst2.eof then response.write "" do response.write "" rst2.movenext loop while not rst2.eof response.write " " & rst2("strDescription") & "  " else response.write " " end if else if trim(arrFields(i)) = "DistributionList" then s = "SELECT tblDistributionList.strDescription, tblContactDistributionList.refContactID " & _ "FROM tblDistributionList INNER JOIN tblContactDistributionList ON tblDistributionList.DistributionListID = tblContactDistributionList.refDistributionListID " & _ "WHERE (((tblContactDistributionList.refContactID)=" & rst("ContactID") & ")) " & strDistributionListOrderBy set rst2 = cn.execute(s) if not rst2.eof then response.write "" do response.write "" rst2.movenext loop while not rst2.eof response.write " " & rst2("strDescription") & "  " else response.write " " end if else offset = instrrev(arrFields(i), " ", -1, 1) strField = right(arrFields(i), len(arrFields(i)) - offset) if mid(strField, 4, 5) = "Email" then response.write "" & rst(strField) & "" else response.write rst(strField) end if end if end if response.write "

" if len(arrCID) > 0 then response.write "" end if else response.write "Please select the fields you want to query" end if %>