//Set the query text area to the selected SQL statement
function setQuery(caller)
{
  //Get the selected text
  var selectedText = caller.options[caller.selectedIndex].text;

  //Create an associative array, where each key is the same as the text
  //in the select boxes
  var sqlStatements = new Array();


  //////////////////////////////
  // LENDER
  //////////////////////////////
  sqlStatements["My Lender Data"] =
"select *\n\
from lender\n\
where lender_id = 'jonandlisa'";

  sqlStatements["Lenders In My City"] =
"--Replace DES MOINES with your city.  The whereabouts field is free text so the results won't be very accurate.\n\
select *\n\
from lender\n\
where upper(whereabouts) like upper('%DES MOINES%')\n\
order by loan_count desc nulls last";


  sqlStatements["My Lender Rank"] =
"with lenderName as (select 'jonandlisa' lender_id from dual)\n\
select\n\
  '<img src=\"http://chart.apis.google.com/chart?chs=225x125&cht=gom&chd=t:'||lendingRank||'&chl='||lender_id||'\">' image\n\
  ,lendingRank \"Lending Rank\"\n\
  ,loan_count \"Number of Loans\"\n\
  ,totalNumberOfLenders \"Total Number of Lenders\"\n\
  ,trim(to_char((1 - (lendingRank / totalNumberOfLenders)) * 100, '999.999')) \"Lending Percentile\"\n\
  ,lender_id\n\
from\n\
(\n\
  select lender_id, loan_count, dense_rank() over (order by loan_count desc, lender_id) lendingRank\n\
    ,count(lender_id) over () totalNumberOfLenders\n\
  from lender\n\
  --Only count people who have made at least one loan\n\
  where loan_count is not null\n\
  order by loan_count desc, lender_id\n\
) lenders\n\
where lenders.lender_id = (select lender_id from lenderName)";


  sqlStatements["Similar Lenders"] =
"--This doesn't work very well.  If the number is close it's usually because the\n\
--lender has many loans.  If the percent is close it's usually because the\n\
--lender has very few loans.  Try changing the order by clause below and\n\
--look for rows where some of the numbers change significantly.\n\
select loan_lender.lender_id, lender.loan_count, count(*) numberOfSameLoans\n\
  ,trim(to_char(count(*) / lender.loan_count * 100, '990.0')) percentOfSameLoans\n\
from loan_lender\n\
  inner join lender on loan_lender.lender_id = lender.lender_id\n\
where loan_id in\n\
(\n\
  select loan_id\n\
  from loan_lender\n\
  where lender_id = 'jonandlisa'\n\
)\n\
group by loan_lender.lender_id, lender.loan_count\n\
--order by percentOfSameLoans desc, numberOfSameLoans desc\n\
order by numberOfSameLoans desc, percentOfSameLoans desc";


  //////////////////////////////
  // LOAN
  //////////////////////////////
  sqlStatements["My Loans"] =
"select loan.*\n\
from loan_lender\n\
  inner join loan on loan_lender.loan_id = loan.id\n\
where loan_lender.lender_id = 'jonandlisa'\n\
order by loan.id";


  sqlStatements["Loan History Chart"] =
"with lenderName as (select 'jonandlisa' lender_id from dual)\n\
select '<img src=\"http://chart.apis.google.com/chart?cht=lc&chs=650x450&chtt=Loans+per+month'||\n\
  '+for+'||(select lender_id from lenderName)||\n\
  '&chds=0,'||mostLoansPerMonth||'&chxt=x,y&chxl=0:|'||first_funded_date||'|'||last_funded_date||\n\
  '|1:|0|'||mostLoansPerMonth||'&chd=t:'||loanCounts||'\">' image\n\
from\n\
(\n\
  select to_string2(cast(collect(to_char(total) order by yearAndMonth) as varchar2_ntt), ',') loanCounts\n\
    ,min(first_funded_date) first_funded_date, max(last_funded_date) last_funded_date\n\
    ,max(total) mostLoansPerMonth\n\
  from\n\
  (\n\
    select yearAndMonth, total\n\
      ,first_funded_date, last_funded_date\n\
    from\n\
    (\n\
      select mv_months.yearAndMonth, nvl(loansPerMonth.total, 0) total\n\
        ,min(loansPerMonth.yearAndMonth) over () first_funded_date\n\
        ,max(loansPerMonth.yearAndMonth) over () last_funded_date\n\
      from mv_months\n\
        left outer join\n\
        (\n\
          select to_char(loan.funded_date, 'YYYYMM') yearAndMonth, count(*) total\n\
          from loan_lender\n\
            inner join loan on loan_lender.loan_id = loan.id\n\
          where loan_lender.lender_id = (select max(lender_id) from lenderName)\n\
          group by to_char(loan.funded_date, 'YYYYMM')\n\
        ) loansPerMonth\n\
          on mv_months.yearAndMonth = loansPerMonth.yearAndMonth\n\
    )\n\
    where yearAndMonth between first_funded_date and last_funded_date\n\
    order by yearAndMonth\n\
  )\n\
)";


  sqlStatements["My Countries (map)"] =
"--Color each country for a lender\n\
select '<img src=\"http://chart.apis.google.com/chart?'|| --Google charts\n\
  'cht=t&chtm=world'|| --map chart type\n\
  '&chs=440x220'|| --maximum map size\n\
  '&chd=t:'||colorLevels|| --color levels, always 100 - all countries are the same color\n\
  '&chco=FFFFFF,000000,FFFF00,00FF00'|| --countries with loans are green, else white\n\
  '&chld='||isoCodes|| --comma separated list of countries\n\
  '&chf=bg,s,EAF7FE\">' my_countries --set the water color to light blue\n\
from\n\
(\n\
  select\n\
    to_string2(cast(collect(iso_code order by iso_code) as varchar2_ntt), '') isoCodes\n\
    ,to_string2(cast(collect(to_char(colorLevel)) as varchar2_ntt)) colorLevels\n\
  from\n\
  (\n\
    select distinct iso_code, 100 colorLevel\n\
    from loan\n\
      inner join country on loan.country = country.name\n\
    where id in (select loan_id from loan_lender where lender_id = 'jonandlisa')\n\
  )\n\
)";


  //////////////////////////////
  // OTHER
  //////////////////////////////

  sqlStatements["Average Loan Size"] =
"select avg(loan_amount) from loan";

  sqlStatements["Lenders With Most Loans"] = 
"select lender.lender_id, count(loan_lender.loan_id) numberOfLoans\n\
from lender\n\
  inner join loan_lender on lender.lender_id = loan_lender.lender_id\n\
group by lender.lender_id\n\
order by numberOfLoans desc";

  sqlStatements["Borrowers For A Specific Loan"] = 
"select * from borrower where loan_id = 98865";

  sqlStatements["Loans Disbursed First Or Funded First"] = 
"--Count of loans disbursed first or funded first.\n\
--Change 'YYYY-MM' to 'YYYY', 'YYYY-IW', or 'YYYY-MM-DD' for year, week, or day totals.\n\
select date_disbursed\n\
  ,sum(disbursedFirst) disbursedFirst\n\
  ,sum(fundedFirst) fundedFirst\n\
from\n\
(\n\
  select to_char(disbursal_date, 'YYYY-MM') date_disbursed\n\
    ,case when loan.disbursal_date < funded_date then 1 else 0 end disbursedFirst\n\
    ,case when loan.disbursal_date >= funded_date then 1 else 0 end fundedFirst\n\
  from loan\n\
  where funded_date is not null\n\
)\n\
group by grouping sets (date_disbursed, ())\n\
order by date_disbursed";

  sqlStatements["Loans Funded (Activity)"] = 
"--Number of loans funded.  This is the best way of measuring activity.\n\
--Change 'YYYY-MM-DD' to 'YYYY', 'YYYY-MM', or 'YYYY-IW' for year, month, or week totals.\n\
--(Data may have gaps)\n\
select fundedDate, count(*) numberOfLoansFunded\n\
from\n\
(\n\
  select to_char(trunc(funded_date), 'YYYY-MM-DD') fundedDate\n\
  from loan\n\
  where funded_date is not null\n\
)\n\
group by fundedDate\n\
order by fundedDate desc";

  sqlStatements["Loans Per Country"] = 
"select country, numberOfLoans\n\
  ,trim(to_char(ratio_to_report(numberOfLoans) over () * 100, '90.00'))||'%' percentOfAllLoans\n\
  --,sum(numberOfLoans) over () totalLoans\n\
  ,rank() over (order by numberOfLoans desc nulls last) loanRank\n\
  ,numberOfLenders\n\
  ,trim(to_char(ratio_to_report(numberOfLenders) over () * 100, '90.00'))||'%' percentOfAllLenders\n\
  --,sum(numberOfLenders) over () totalLenders\n\
  ,rank() over (order by numberOfLenders desc nulls last) lenderRank\n\
from\n\
(\n\
  select country.name country, nvl(sum(loan_count), 0) numberOfLoans, count(lender.lender_id) numberOfLenders\n\
  from lender\n\
    left outer join country on lender.country_code = country.iso_code\n\
  group by country.name\n\
)\n\
order by loanRank, lenderRank";

  sqlStatements["Top Lenders Per Country"] = 
"select country, loan_count, lenderName, lender_id, loanRank\n\
  ,trim(to_char(percentOfCountryLoans*100, '990.0'))||'%' percentOfCountryLoans\n\
from\n\
(\n\
  select country.name country, lender.loan_count, lender.name lenderName, lender.lender_id\n\
    ,rank() over (partition by country.name order by loan_count desc nulls last) loanRank\n\
    ,ratio_to_report(lender.loan_count) over (partition by country.name) percentOfCountryLoans\n\
  from lender\n\
    left outer join country on lender.country_code = country.iso_code\n\
)\n\
where loanRank <= 5\n\
  and loan_count is not null\n\
order by country, loanRank";


  sqlStatements["Users Without A Loan"] =
"select totalUsers, usersWithALoan\n\
  ,trim(to_char(usersWithALoan/totalUsers*100, '990.0'))||'%' percentOfUsersWithALoan\n\
from\n\
(\n\
  select count(lender.lender_id) totalUsers\n\
    ,count(case when loan_count > 0 then lender.lender_id else null end) usersWithALoan\n\
  from lender\n\
)\n";

  sqlStatements["Average Loan Term"] =
"--Average loan term (repayment term): the number of months between the disbursal_date and the last scheduled date.\n\
--This is the loan schedule from the lender's point of view.  You can change the table scheduledPayment to localPayment\n\
--to get the schedule from the borrower's perspective.\n\
select avg(repaymentMonths) averageRepaymentMonths\n\
  ,sum(repaymentMonths * loan_amount) / sum(loan_amount) weightedAvgRepaymentMonths\n\
from\n\
(\n\
  select loan_id, loan_amount, disbursal_date, lastPayment\n\
    --There is no universally accepted method of calculating the number of months between dates.  months_between()\n\
    --tries to return a natural number of months when possible.  For example, the difference between 'Jan 28' and\n\
    --'Feb 28' is 1, and 'Jan 31' and 'Feb 28' is also 1, but 'Jan 29' and 'Feb 28' is .97.  I'm not sure how this\n\
    --compares to the method Kiva uses.\n\
    --The Repayment Term displayed on each loan page appears to always round up fractional results, but this query\n\
    --does not do that.  Add the function ceil() around the months_between() to always round up.\n\
    ,months_between(lastPayment, disbursal_date) repaymentMonths\n\
  from\n\
    loan\n\
    ,(select loan_id, max(due_date) lastPayment\n\
      from scheduledPayment\n\
      group by loan_id\n\
      order by loan_id\n\
    ) schedule\n\
  where loan.id = schedule.loan_id\n\
  order by loan.id\n\
)";


  sqlStatements["Amount Given and Received"] =
"select '<img border=\"0\" src=\"http://chart.apis.google.com/chart?cht=bhg'||\n\
  '&chtt=Total+loan+amount+in+local+purchasing+power&chd=t:'||givenPercent||','||receivedPercent||'&chs=450x100'||\n\
  '&chco=FF9900,000000&chxt=y&chxl=0:|Amount+received:+$'||received||'|Amount+given:+$'||given||'|\"/>' image\n\
from\n\
(\n\
  select trim(to_char(totalLoanAmount.totalLoanAmount / 1000000, '999'))||' million' given\n\
    ,trim(to_char(totalLoanAmount * moneyMultiplier / 1000000000, '9.99'))||' billion' received\n\
    ,trim(to_char(totalLoanAmount.totalLoanAmount / \n\
      (totalLoanAmount * moneyMultiplier + totalLoanAmount) * 100, '99')) givenPercent\n\
    ,trim(to_char(totalLoanAmount * moneyMultiplier / \n\
      (totalLoanAmount * moneyMultiplier + totalLoanAmount) * 100, '99')) receivedPercent\n\
  from\n\
  (\n\
    --Find the average money multiplier\n\
    select avg(lenderCountry.gdpPerCapitaPPP / loanCountry.gdpPerCapitaPPP) moneyMultiplier\n\
    from loan_lender\n\
      inner join loan on loan_lender.loan_id = loan.id\n\
      inner join country loanCountry on loan.country = loanCountry .name\n\
      inner join lender on loan_lender.lender_id = lender.lender_id\n\
      inner join country lenderCountry on lender.country_code = lenderCountry.iso_code\n\
    where loan.status not in ('refunded', 'fundraising')\n\
  ) averageMoneyMultiplier\n\
  cross join\n\
  (\n\
  --Total amount of money loaned\n\
    select sum(loan_amount) totalLoanAmount\n\
    from loan\n\
    where loan.status not in ('refunded', 'fundraising')\n\
  ) totalLoanAmount\n\
)";



  //Replace 'jonandlisa' if the user entered their own lender_id
  var queryText = sqlStatements[selectedText];
  if(document.getElementById("LenderID").value.length > 0)
  {
    queryText = queryText.replace("jonandlisa", document.getElementById("LenderID").value);
  }

  //Set the text area to the relevant SQL statement
  document.getElementById("query").value = queryText;



}