{tipstudb.ace - ALLANS - 6/21/98
 Produces student database import file for TIP.
 command line is  sacego tipstudb '"'
 1/5/01 - added leading hyphen for state assigned student IDs per TIP}

database student end 

define
  variable bld int
  variable stuid char(9)
  param [1] qt char(1)
end

input
  prompt for bld using "BUILDING NUMBER:  "
end
 
output 
  page  length  9999
  left  margin  0 
  right margin  0 
  top   margin  0 
  report to pipe "cat > $SMSRPTDIR/tip/2001/studat.unl" 
end 

select distinct reg.studentid, studentname, homeroom, reg.grade,
  apartment, complex, lot, street, city, state, zipcode,
  phone, with_date
from reg, regentry
where reg.studentid = regentry.studentid and 
             status = 'A' and 
       reg.building = $bld and
          with_date is null and
      reg.studentid is not null and
    reg.studentname is not null 
end 
format
  on every row
 
  if studentid < 0
	then let stuid = "-",studentid using "&&&&&&&&"
	else let stuid = studentid using "&&&&&&&&&"

  print

    qt,stuid,qt,",",
    qt,studentname clipped,qt,",",
    qt,homeroom  clipped,qt,",",
    qt,grade,qt,",",qt;
  if apartment is not null then
    print apartment clipped," ";
  if complex is not null then
    print complex clipped," ";
  if lot is not null then 
    print lot clipped," ";
  if street is not null then
    print street clipped;
  print qt,",";
  print qt,city clipped,qt,",",
    qt,state,qt,",",
    qt,zipcode clipped,qt,",",
    qt,phone clipped,qt,",",
    qt," ",qt 
end