/*
   Create TShirtPerCompetitor.PRG
   
   Author: Ruth Bromer 6/27/2019

   Create Packet Table from Competitor Table.  There will be a
   record per competitor for each type of t-shirt ordered.  If no
	t-shirts are ordered, there will be one record.
   
   NOTE : The values used for colours in packet2.dbf are not the same as 
        the values used in colours.dbf.  As you are saving the size name e.g XXS,
        SM and so on rather than a number you may just as well be consitent and
        save the colour name instead of a number.  This means changing the field
        specification for TShirtColor  in packet2 from numeric (2) to character(6).
        You will also need to change the value saved in lines 115 and 216 if you 
        change from  numeric to character.        
   
      I have used field numbers 9 to 99   in lines 74 and 123.  You will need to check
      the numbers in the competitor table and edit them to use the correct values. 
      
      
      
      
      
*/


clear

//? "Emptying Tables "
//d = _app.databases[1]
//d.emptyTable( "Packet2" )
//   //d = null
//msgbox( "Packet2 is empty" )

//It is better to drop the table and then rebuild it rather than to empty it
/*
try
  drop table packet2
catch(exception e)
  msgbox(e.message)
endtry 
*/
If file('Packet2.dbf')
   drop table Packet2
endif
/*
Create Packet2.dbf from Competitor.dbf
*/

c = new query()
c.sql  = [Select * from Competitor ]
c.active = true

If not file('Packet2.dbf')
   create table Packet2 (First character(20), Last character(20), Name character(40),;
	Waiver character(6), BibNum numeric(4), Address1 character(40), Address2 character(40),;
	City character(20), State character(2), Zip character(10), Country character(25),;
	EPunchID numeric(8), RentPunch character(1), FoxPracCourse character(9),;
	SprintPracCourse character(9), M80PracCourse character(9), M2PracCourse character(9),;
	BlindPracCourse character(9), FoxClass character(4), FoxStart character(8),;
	SprintClass character(4), SprintStart character(8), M2Class character(4),;
	M2Start character(8), M80Class character(4), M80Start character(8),;
	CallSign character(10), USAYN character(1), RegIIYN character(1), Dinner numeric(2),;
	LunchYN character(1), TotalFee numeric(5), AmtPaid numeric(5), Balance numeric(5),;
	Notes character(140), TShirtSize character(3), TShirtColor character (7),;
   TShirtQuant numeric(2), TShirtAmt numeric(4))
Endif

p = new query()
p.sql = "select * from Packet2"
p.active = true
cf = c.rowset.fields
pf = p.rowset.fields
c.rowset.first()
p.rowset.first()
 
/*
Create all records.
*/

do while not c.rowset.endofset	
	nShirtTot = 0
   for n = 96 to 274 step 2//these fields are t-shirt sizes and fee. Check for any shirts ordered
		 nShirtTot += cf[n].value
   next 
	If nShirtTot = 0
	   p.rowset.beginappend()
		pf["First"].value = cf["First"].value
		pf["Last"].value = cf["Last"].value
		pf["Name"].value = cf["Last"].value.rightTrim()+", "+ cf["First"].value.rightTrim()
		pf["BibNum"].value = cf["BibNum"].value
		pf["Waiver"].value = cf["Waiver"].value
		pf["Address1"].value = cf["Address1"].value
		pf["Address2"].value = cf["Address2"].value
		pf["City"].value = cf["City"].value
		pf["State"].value = cf["State"].value
		pf["Zip"].value = cf["Zip"].value
		pf["Country"].value = cf["Country"].value
		pf["EPunchID"].value = cf["EPunchID"].value
		pf["RentPunch"].value = cf["RentPunch"].value
		pf["FoxPracCourse"].value = cf["FoxPracCourse"].value
		pf["SprintPracCourse"].value = cf["SprintPracCourse"].value
		pf["M80PracCourse"].value = cf["M80PracCourse"].value
		pf["M2PracCourse"].value = cf["M2PracCourse"].value
		pf["BlindPracCourse"].value = cf["BlindPracCourse"].value
		pf["FoxClass"].value = cf["FoxClass"].value
		pf["FoxStart"].value = cf["FoxStart"].value
		pf["SprintClass"].value = cf["SprintClass"].value
		pf["SprintStart"].value = cf["SprintStart"].value
		pf["M2Class"].value = cf["M2Class"].value
		pf["M2Start"].value = cf["M2Start"].value
		pf["M80Class"].value = cf["M80Class"].value
		pf["M80Start"].value = cf["M80Start"].value
		pf["CallSign"].value = cf["CallSign"].value
		pf["USAYN"].value = cf["USAYN"].value
		pf["RegIIYN"].value = cf["RegIIYN"].value
		pf["Dinner"].value = cf["Dinner"].value
		pf["LunchYN"].value = cf["LunchYN"].value
		pf["TotalFee"].value = cf["TotalFee"].value
		pf["AmtPaid"].value = cf["AmtPaid"].value
		pf["Balance"].value = cf["Balance"].value
		pf["Notes"].value = cf["Notes"].value
		pf["TShirtSize"].value = ' '
		pf["TShirtColor"].value = ' ' 
		pf["TShirtQuant"].value = 0
		pf["TShirtAmt"].value = 0
*		p.rowset.save()
	Else
/*
Create with t-shirts
*/
    for n = 96 to 274 step 2 //t-shirt fields, skipping the t-shirt fee fields
		if cf[n].value > 0
         p.rowset.beginappend()
         pf["First"].value = cf["First"].value
         pf["Last"].value = cf["Last"].value
			pf["Name"].value = cf["Last"].value.rightTrim()+", "+ cf["First"].value.rightTrim()
			pf["BibNum"].value = cf["BibNum"].value
			pf["Waiver"].value = cf["Waiver"].value
			pf["Address1"].value = cf["Address1"].value
			pf["Address2"].value = cf["Address2"].value
			pf["City"].value = cf["City"].value
			pf["State"].value = cf["State"].value
			pf["Zip"].value = cf["Zip"].value
			pf["Country"].value = cf["Country"].value
			pf["EPunchID"].value = cf["EPunchID"].value
			pf["RentPunch"].value = cf["RentPunch"].value
			pf["FoxPracCourse"].value = cf["FoxPracCourse"].value
			pf["SprintPracCourse"].value = cf["SprintPracCourse"].value
			pf["M80PracCourse"].value = cf["M80PracCourse"].value
			pf["M2PracCourse"].value = cf["M2PracCourse"].value
			pf["BlindPracCourse"].value = cf["BlindPracCourse"].value
			pf["FoxClass"].value = cf["FoxClass"].value
			pf["FoxStart"].value = cf["FoxStart"].value
			pf["SprintClass"].value = cf["SprintClass"].value
			pf["SprintStart"].value = cf["SprintStart"].value
			pf["M2Class"].value = cf["M2Class"].value
			pf["M2Start"].value = cf["M2Start"].value
			pf["M80Class"].value = cf["M80Class"].value
			pf["M80Start"].value = cf["M80Start"].value
			pf["CallSign"].value = cf["CallSign"].value
			pf["USAYN"].value = cf["USAYN"].value
			pf["RegIIYN"].value = cf["RegIIYN"].value
			pf["Dinner"].value = cf["Dinner"].value
			pf["LunchYN"].value = cf["LunchYN"].value
			pf["TotalFee"].value = cf["TotalFee"].value
			pf["AmtPaid"].value = cf["AmtPaid"].value
			pf["Balance"].value = cf["Balance"].value
			pf["Notes"].value = cf["Notes"].value
 
 			if 'TShirtBlue'$cf[n].fieldname
			    cSize=substr(cf[n].fieldname,11)
             nColour = 1
             cColour = 'Blue'
         elseif  'TShirtSlate'$cf[n].fieldname
			    cSize =substr(cf[n].fieldname,12)
             nColour = 2
             cColour = 'Slate'
          elseif  'TShirtFuschia'$cf[n].fieldname
			    cSize =substr(cf[n].fieldname,14)
             nColour = 3
             cColour = 'Fuschia'
         elseif  'TShirtYellow'$cf[n].fieldname
			    cSize =substr(cf[n].fieldname,13)
             nColour = 4
             cColour = 'Yellow'
         elseif  'TShirtRed'$cf[n].fieldname
			    cSize =substr(cf[n].fieldname,10)
             nColour = 5
             cColour = 'Red'
         elseif  'TShirtGreen'$cf[n].fieldname
			    cSize =substr(cf[n].fieldname,12)
             nColour = 6
             cColour = 'Green'
         endif

         if cSize = '3XL'
            nAmt = 18
         else
            nAmt = 15
         endif  
			
			pf["TShirtSize"].value = cSize
			pf["TShirtColor"].value = cColour //change to cColour if the field is changed to character
         pf["TShirtQuant"].value = cf[n].value
         pf["TShirtAmt"].value = nAmt * cf[n].value
*         p.rowset.save()
		endif
*	  endfor	
   next
endif
	p.rowset.save()
   c.rowset.next()
*  	next
 
enddo
 
c.active = false
p.active = false

msgbox( "The Packet table should now have data in it" )