<% ' California Pension Reform Database Script ' ' Author: James Stevens (jstevens@webcommanders.com) ' ' Web Commanders (http://www.webcommanders.com) ' Copyright 2009 Web Commanders ' ' Setup the dim and base ado variables -- These should NOT be changed dim adoConn, adoRS, counter, totcount, strQCount, strSDrop, strQTop, dbtabletmp, name, monthly, annual, emdisplay set adoConn = Server.CreateObject("ADODB.Connection") set adoRS = Server.CreateObject("ADODB.Recordset") ' Declare the DSN to connect to adoConn.Open "DSN=calpers" 'This should NOT be changed ' Declare the Database were using dbbase = "calipers" 'This should NOT be changed ' Declare the Table we will be using and other variables dbtable0 = "CalPERS_100K_Club" 'This is for CalPERS dbdescp0 = "retired California government workers" dbdescp0a = "California Public Employees Retirement System" dbseastr0 = "Emplorer" dbtable1 = "CalSTRS_100K_Club" 'This is for CalSTRS dbdescp1 = "retired teachers and administrators" dbdescp1a = "California State Teachers Retirement System" dbseastr1 = "District" ' See if user sent a dbtable request if LEN(Request.Querystring("vttable")) > 0 then if Request.Querystring("vttable") = "calpers" then dbtable = dbtable0 dbdescp = dbdescp0 dbdescp1 = dbdescp0a dbseastr = dbseastr0 elseif request.Querystring("vttable") = "calstrs" then dbtable = dbtable1 dbdescp = dbdescp1 dbdescpa = dbdescp1a dbseastr = dbseastr1 else dbtable = dbtable0 'We default to CalPERS database if all else fails dbdescp = dbdescp0 dbdescp1 = dbdescp0a dbseastr = dbseastr0 end if else dbtable = dbtable0 'Set this to the default database you want dbdescp = dbdescp0 'users using. This affects which database they dbdescp1 = dbdescp0a 'see initially with no URL query. dbseastr = dbseastr0 end if ' See if user sent a top to display if not default to our top number if LEN(Request.Querystring("vttop")) > 0 then vtpnum = Request.Querystring("vttop") else vtpnum = "10" 'Set this to the default number of top 'pensioners you want displayed on the main page 'our default here is 10 end if ' See if user sent a sort method(s) if LEN(Request.Querystring("vtsort")) > 0 then vtsort = Request.Querystring("vtsort") else vtsort = "DESC" 'Set this to the default sort method we want 'this applies to the way we display name in 'alphabetical order [ASC | DESC] our default is 'DESC end if if LEN(Request.Querystring("vtsearchorder")) > 0 then vtsearch = Request.Querystring("vtsearchorder") else vtsearch = "ASC" 'Set this to the default sort method we want 'this applies to the search box drop down box 'in the search area of the page and controls 'how we populate that drop down in alphabetical 'order [ASC | DESC] our default is ASC end if ' See if user sent a skip record command ' Note: sometimes (most times) for whatever reason we need to skip ' number of records this usally applies when were sorting on figures ' ie: dollar amounts or raw numbers .. mySQL and MSSQL have a habbit of ' thinking 99999 is greater than 100000 there is a fix for this server ' side but we haven't gotten to the point where we need todo that yet ' for now we setup the sql querries to pull between 100k and 900m and ' sort based on that result. if LEN(Request.Querystring("vtskip")) > 0 then vtskip = Request.Querystring("vtskip") else vtskip = "0" 'Set this to the default number of records we 'want to skip. This afftects the display of 'records on the display page. Our default here 'is 0 end if ' Lets build our Query Strings ' Queries Optimized Septempber 2009 strQCount = "SELECT COUNT(RECNO) AS NUMREC FROM "&dbbase&"."&dbtable strQSDrop = "SELECT DISTINCT EmployerName FROM "&dbbase&"."&dbtable&" ORDER BY EmployerName "&vtsearch strQTop = "SELECT FULL_NAME, Annual, EmployerName FROM "&dbbase&"."&dbtable&" WHERE Annual BETWEEN '100000' AND '900000000' ORDER BY Annual "&vtsort&" LIMIT "&vtskip&","&vtpnum ' Total Number of Records adoRS.Open strQCount, adoConn totcount = FormatNumber(adoRS.Fields("NUMREC"),0) adoRS.Close ' Split some variables up so we can use them for dynamic content dbnametmp = Split(dbtable,"_") ' We do a lot of Dynamic Stuff below so be careful when editing below this line ' Note: to update fonts, colors and other effects please edit calpers-database.css ' ' #### USE CAUTION WHEN EDITING BELOW THIS LINE #### %>

<%' Need to setup the navigation menu IF Lcase(dbnametmp(0)) = "calpers" Then %> <%ELSE%> <%END IF%> CalPERS CalSTRS CalPERS CalSTRS

<%=totcount%> <%=dbdescp%> receive pensions in excess of $100,000 from <%=dbnametmp(0)%>. They're all listed here. The information below was obtained under the Freedom of Information Act from the <%=dbdescp1%> (<%=dbnametmp(0)%>). This list may be be updated periodically with more pensioners as more data is obtained. E-mail us with your comments or questions. Search the $100,000 Pension Club database ... Search by First, Last or Full Name Search by <%=dbseastr%> SELECT ONE <%'Drop Down List Loaded Dynamically adoRS.Open strQSDrop, adoConn do while not adoRS.eof employ = Trim(adoRS("EmployerName")) ' Need to trim over length results so they dont blow up ' our tables IF Len(employ) >= 16 Then emdisplay = Left(employ, 16)&" ..." ELSE emdisplay = employ END IF %> <%=emdisplay%> <% adoRS.MoveNext loop adoRS.Close %> THE TOP <%=vtpnum%> LIST <%' Top Pensioners Display adoRS.Open strQTop, adoConn ' Start with class dbresclass1 = "content-resultfield1" dbresclass2 = "content-resultfield2" dbrescount = "0" do while not adoRS.eof name = Trim(adoRS("FULL_NAME")) monthly = Trim(adoRS("Annual")) / 12 annual = Trim(adoRS("Annual")) employ = Trim(adoRS("EmployerName")) ' Need to trim over length results so they dont blow up ' our tables IF Len(name) > 17 Then name = Left(name, 17)&" ..." END IF IF Len(employ) >= 16 Then employ = Left(employ, 16)&" ..." END IF ' Format Currency Results monthly = FormatCurrency(monthly) annual = FormatCurrency(annual) ' Set the background color for this entry IF dbrescount = "0" Then dbresclass = dbresclass1 dbrescount = "1" ELSE dbresclass = dbresclass2 dbrescount = "0" END IF %> <% adoRS.MoveNext loop adoRS.Close %> Name Monthly Annual <%=dbseastr%> <%=name%> <%=monthly%> <%=annual%> <%=employ%> How to utilize this search engine.   TO SEARCH BY NAME Enter one of the following in the NAME field provided above -- first, last, middle, suffix, first+last, or you can enter any letter (for example, a or z). Once you have entered your desired information, click on SEARCH.   TO SEARCH BY [<%=Ucase(dbseastr)%>] Select the <%=Lcase(dbseastr)%> from the drop-down box provided above. When you search by <%=Lcase(dbseastr)%>, you DO NOT need to also enter a name, although you can if you so wish. Once you have selected and/or entered your desired information, click on SEARCH. Subscribe here to receive updates: Email:  Visit  CaliforniaPensionReform.com  to learn more about this issue. Visit  Facebook  to join our network on this issue. Visit  PensionTsunami.com  for daily news on public pensions. HOME |  CONTACT US | WEB MASTERS | ADMIN Copyright <%=YEAR(NOW())%> by the California Foundation for Fiscal Responsibility. All Rights Reserved

<%=dbnametmp(0)%>