(:
   file:    dt08_189_enrl.xq
   date:    07-Aug-2009
   author:  Gary Lewis
   purpose: Creates the XML with enrollment data in the project addressing the question:
            Is higher education countercyclical?

            1. Documentation is available at: http://garymlewis.com/instchg/public/xquery/dt08_189_doc.xq.
               Follow the steps in this documentation to Step 11.
            2. Run the current program as follows (Step 11 in documentation):
               zorba -o dt08_189_enrl.xml -f -q dt08_189_enrl.xq -z indent=yes

            Note: This program pipelines through several temporary stages.
:)

<tabn189>
{
let $tmp1 := 
   for $x in doc("dt08_189.xml")/html/body//table[@class = "tabletop"]/tr     (: get table with enrollment data :)
   where not(matches($x/td[1]/@class,'header.*'))                             (: get rid of table headers :)
   and substring(data($x/td[1]),1,4) >= '1967'                                (: get rid of years prior to 1967 :)
   return $x
return
   let $tmp2 := 
      for $y at $i in $tmp1/td
      where $i mod 17 != 0                                                    (: get rid of the null 17th column in Table 189 :)
      return $y
   return
      let $tmp3 :=
         for $z at $j in $tmp2
         let $tmpcol := $j mod 16,                                            (: construct column and row location of each cell :)
             $col := if ($tmpcol = 0) then 16 else $tmpcol,
             $tmprow := floor($j div 16),
             $row := if ($tmpcol = 0) then $tmprow else ($tmprow + 1),
             $value := replace(data($z),',','')
         return <enrl row="{$row}" column="{$col}" cell="{$j}" value="{$value}" />
      return
         let $tmp4 :=                                                         (: create $tmp4 for Years only :)
            for $a in $tmp3
            return if ($a/@column = "1") then 
                      <year value="{$a/@value}" row="{$a/@row}" column="{$a/@column}" cell="{$a/@cell}" name="Year" />
                   else()
         return
            let $tmp5 :=                                                      (: within each year return enrollment data :)
               for $b in $tmp4
               return
                  <year value="{$b/@value}" row="{$b/@row}" column="{$b/@column}" cell="{$b/@cell}" name="{$b/@name}" >
                  {
                  for $c in $tmp3[@row = $b/@row]
                  let $d := doc("dt08_189_metadata.xml")/tabn189/meta[@column = $c/@column]          (: add column headers :)
                  where $c/@column != "1"
                  return
                     <enrl value="{$c/@value}" row="{$c/@row}" column="{$c/@column}" cell="{$c/@cell}" name="{$d/@name}" control="{$d/@control}" type2yr4yr="{$d/@type2yr4yr}" type4yr="{$d/@type4yr}" />
                  }
                  </year>
            return 
               for $i in $tmp5[@value >= "1968"]        (: Table 189 did not include complete data prior to 1967 :)
               where $i/@column = "1"
               let $currYR := $i/@value,
                   $prevYR := $i/@value - 1,
                   $fall_enrl_census_date := concat($currYR,'-','10-01')   (: IPEDS fall enrollment date approximated as 10/1 :)
               return
                  <year value="{$i/@value}" fall_enrl_census_date="{$fall_enrl_census_date}" row="{$i/@row}" column="{$i/@column}" cell="{$i/@cell}" name="{$i/@name}" >
                  {
                  for $curr in $tmp5[@value = $currYR]/enrl
                  let $prev := $tmp5[@value = $prevYR]/enrl[@column = $curr/@column],
                      $pctchg := round-half-to-even((($curr/@value - $prev/@value) div $prev/@value)*100,2) (: calc % change :)
                  return
                     <enrl currValue="{$curr/@value}" prevValue="{$prev/@value}" pctchg="{$pctchg}" row="{$curr/@row}" column="{$curr/@column}" cell="{$curr/@cell}" name="{$curr/@name}" control="{$curr/@control}" type2yr4yr="{$curr/@type2yr4yr}" type4yr="{$curr/@type4yr}" />
                  }
                  </year>
}
</tabn189>