/*
Program: CreateSGrid.prg
Author: Ruth Bromer
Date: 9/24/13

Modified: 7/19/19

This program creates the StartGrid table - the grid for the 
start times.
*/

/*

//if updating an existing start grid

If file('entries.dbf')
   if file('preventries.dbf')
      drop table preventries
   endif
   q = new query()
   q.sql = "select* from entries"
   q.active = true
   u = new UpdateSet()
   u.source = q.rowset
   u.destination = "preventries.dbf"
   u.copy()
   q.active = false   
  drop table entries
endif
*/

/*
Delete the exisitng Entries.dbf and StartGrid.dbf
Then empty the PrevEntries.dbf.
*/
If file('entries.dbf')
   if file('preventries.dbf')
      ? "Empty PreviousEntries Tables "
      d = _app.databases[1]
      d.emptyTable( "PrevEntries" )
   endif
   drop table entries
endif

If file('StartGrid.dbf')
   drop table StartGrid
endif

/*
Create the Entries table.
*/

q = new query()
q.sql = "select first, last, ConfNum, EPunchId, Class, CourseNum from Competitor"
q.active = true
u = new UpdateSet()
u.source = q.rowset
u.destination = "Entries.dbf"
u.copy()
q.active = false

/*
If there is not StartGrid, create it.
*/

if not file('startgrid.dbf')

   create table startgrid (STime character (5),;
	White character (40), WhiteConfNum character (3),;
	Yellow character(40), YellowConfNum character (3),;
   Orange character (40), OrangeConfNum character (3),;
	BrownGreen character(40), BrownGreenConfNum character (3))

/*
Set the times in the table.
*/

 *  set procedure to :duflp:time.cc
   set procedure to time.cc
	
   cTime = new time()
   q = new query()
   q.sql = "select * from startgrid"
   q.active = true

/*
   Starting times grid
*/

   cStart = "00:00"       //edit as required
   nStartSlots = 100      //edit as required
   nSecs = nStartSlots * 180
   For n = 0 to nSecs step 180
       cStartTime = left(ctime.sec2time(ctime.time2sec(cStart)+n),5)
       cCmd = [insert into startgrid (STime ) values ("]+cStartTime+ [")]
       &cCmd
   next

   q.active = false

/*
   Populate starting grid with data taken from Entries.dbf  
   
*/

   q = new query()
   q.sql = "select * from startgrid"
   q.active = true

   q1 = new query()
   q1.sql = [SELECT First, Last, ConfNum, EPunchId, Class sc, CourseNum ]
   q1.sql +=[FROM  Entries where Class = :class ]
   q1.sql +=[ORDER BY CourseNum, Last, First]
   q1.params["class"] = "WhiteM"
   q1.active = true
 
/*
    To use place_entry, supply field name, field number and the number of minutes between starting times
    The field number is the number in the table, not the column number in the grid.
    Each class can have a different spacing for the starting times.
*/

   place_entry("WhiteM",2,1,1)
  	place_entry("WhiteF",2,1,1)
	place_entry("YellowM",4,1,1)
   place_entry("YellowF",4,1,1)
	place_entry("OrangeM",6,1,1)
	place_entry("orangeF",6,1,1)
	place_entry("Brown",8,1,1)
	place_entry("Green",8,1,1)

	q.active = false
   q1.active = false

else   //if file startgrid.dbf exists, compare entries with previous entries and only add new entries

   q = new query()
   q.sql = "select * from startgrid"
   q.active = true
   q.requery()
   q1 = new query()
   q1.sql = [SELECT e.First, e.Last, e.ConfNum, e.BibNum, e.EPunchId, e.Class sc, e.CourseNum ]
   q1.sql +=[FROM Entries e ]
   q1.sql +=[LEFT OUTER JOIN preventries ]
   q1.sql +=[ON  (e.First = preventries.First) ]  
   q1.sql +=[AND  (e.Last = preventries.Last)  ]
   q1.sql +=[WHERE   (preventriesm.Last IS NULL) ] 
   q1.sql +=[AND  (e.Last IS NOT NULL) ]
   q1.sql +=[AND e.class = :class ]
   q1.sql +=[ORDER BY  Class, CourseNum, Last, First ]
   q1.params["class"] = "WhiteM"
   q1.active = true
 
/*
    To use place_entry, supply field name, field number and the number of minutes
    between starting times.The field number is the number in the table, not the
    column number in the grid. Each class can have a different spacing for the
    starting times.
*/

   place_entry("WhiteM",2,1,1)
  	place_entry("WhiteF",2,1,1)
	place_entry("YellowM",4,1,1)
   place_entry("YellowF",4,1,1)
	place_entry("OrangeM",6,1,1)
	place_entry("orangeF",6,1,1)
	place_entry("Brown",8,1,1)
	place_entry("Green",8,1,1)


endif

msgbox( "The StartGrid table should now have data in it","Done", 64 )

/*
This places the competitor in the correct box in the grid.  q is StartGrid, q1 is Entries
*/

   function place_entry(cClass,nClass,nSpacing,nStart)
       q1.params["class"] = cClass
       q1.requery() 
       qf = q.rowset.fields
       q1f = q1.rowset.fields
       q.rowset.last()
* Do not process 'NC' or 'no class'
       do while qf[nClass].value <> "" and not q.rowset.endofset
          q.rowset.next(-1)
       enddo
* Start from first valid competitor and starting at the correct first start time
       if q.rowset.endofset
          q.rowset.first()
          if nStart > 0
             q.rowset.next(nStart)
          endif
       else
         q.rowset.next(nSpacing)
       endif
       q1.rowset.first()
       do while not q1.rowset.endofset
          qf[nclass].value = q1f["Sex"].value+" "+q1f["first"].value.rightTrim();
             +" "+q1f["last"].value.rightTrim()+" "+q1f["sc"].value.rightTrim();
             +" "+q1f["EPunchId"].value 
          m = nClass + 1
          qf[m].value = q1f["Confnum"].value
          q.rowset.next(nSpacing)
          q1.rowset.next()
       enddo
   return


<br />
<b>Warning</b>:  Unknown: write failed: No space left on device (28) in <b>Unknown</b> on line <b>0</b><br />
<br />
<b>Warning</b>:  Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct () in <b>Unknown</b> on line <b>0</b><br />