wiki:SchoolList

School verification reports

Design

A perl script, using DBI, accesses KLPEMS_MIRROR, retrieves the list of schools and formats that into PDFs, one per educational district. The PDFs are generated using the PDF filter of Template::Latex.

Query

select  trim(b2.name) as dist, 
	trim(b1.name) as blk, 
	trim(b.name) as clust,
	trim(s.schoolname) as sname,
	s.id as scode,
	trim(i.medium_name) as moi,
	trim(sc.category_desc) as stype,
	trim(s.inst_scho_gend) as ssex,
	concat(a.streetaddress1, a.streetaddress2) as address,
	a.pincode as pin
from eg_school s, 
     eg_school_category sc, 
     eg_boundary b, eg_boundary b1, eg_boundary b2, 
     eg_boundary_type bt, 
     eg_instr_medium i, 
     egclts_address a
where
	s.categoryid = sc.categoryid (+) and
	s.id_adm_boundary = b.id_bndry and
	b.parent = b1.id_bndry and
	b1.parent = b2.id_bndry and
	b.id_bndry_type = bt.id_bndry_type and
	bt.id_heirarchy_type = 1 and
	s.id_instr_medium = i.id_instr_medium (+) and
	s.addressid = a.addressid (+)
order by b1.name, b.name

Notes

Information Table Field
District eg_boundary name
Block eg_boundary name
Cluster eg_boundary name
School name eg_school schoolname
School code eg_school id
Medium of instruction eg_instr_medium medium_name
School type (hps, ...) eg_school_category category_desc
School sex eg_schoool inst_scho_gend
School address egclts_address concat(streetaddress1, streetaddress2)
School PIN egclts_address pin

Joins

Tables eg_school eg_school_category eg_boundary eg_boundary_type eg_instr_medium egclts_address
Aliases s sc b, b1, b2 bt i a
  • s.categoryid = sc.categoryid (outer join)
  • s.id_adm_boundary = b.id_bndry
  • b.id_bndry_type = bt.id_bndry_type
  • bt.id_heirarchy_type [sic] = 1 (school hierarchy)
  • s.id_instr_medium = i.id_instr_medium (outer join)
  • s.addressid = a.addressid (outer join)

Boundaries are obtained by self-joining the eg_boundary table with itself for as many levels as are necessary.

Last modified 9 years ago Last modified on 07/02/09 13:49:14