/*
COPYDATA.PRG
Author: Ken Mayer
Modified: Ruth Bromer 6/4/2019
A simple program to copy the data to the Competitor table
from Ed Despard's registration data.
USAGE: Copy this to the directory that the tables
were created in (i.e., C:Documents/BOK/ARDF dBase 2019 ) for
the tutorial. Double-click the icon in the navigator.
That's all you should have to do, unless an error
occurs ...
Before running this program download data into wordpad from Ed's program.
Delete the first row (heading row).
Select a tab.
Go into EDIT/REPLACE and put "," into the REPLACE field ,
and REPLACE ALL
Save file as a .csv
***********************************************
DEPENDENCIES: BFILE.CC, DBF7FILE.CC
these files must be in the same directory as
this program and the tables!!
***********************************************
*/
close database
clear
// --------------------------------------------------------
// append the data :
? "Copying competitor data"
d = _app.databases[1]
d.emptyTable( "competitor" )
use competitor exclusive
append from competitor.csv delimited
reindex
/*
Convert phone number to 123-456-7890 format
*/
q = new query()
q.sql = "select phone,country from competitor"
q.active = true
q.rowset.first()
do while not q.rowset.endofset
cOldPhoneNo = q.rowset.fields["phone"].value.leftTrim().rightTrim()
cNewPhoneNo = ""
for n = 1 to len(cOldPhoneNo)
if substr(cOldPhoneNo,n,1) >= "0" and substr(cOldPhoneNo,n,1) <= "9"
cNewPhoneNo += substr(cOldPhoneNo,n,1)
endif
next
nLen = len(cNewPhoneNo)
cNewPhoneNo =stuff(cNewPhoneNo,nLen-3,0,"-")
cNewPhoneNo =stuff(cNewPhoneNo,nLen-6,0,"-")
if nLen > 10
cNewPhoneNo =stuff(cNewPhoneNo,nLen-9,0,"-")
endif
if q.rowset.fields["country"].value.toUpperCase() <> "UNITED STATES OF AMERICA" and;
q.rowset.fields["country"].value.toUpperCase() <> "CANADA"
cNewPhoneNo = "+" + cNewPhoneNo
endif
q.rowset.fields["phone"].value = cNewphoneNo
q.rowset.next()
enddo
/*
Create sort order for Classs
*/
q = new query()
q.sql = "select M2Class, M2ClassNum, M80Class, M80ClassNum, FoxClass, FoxClassNum, SprintClass, SprintClassNum from Competitor"
q.active = true
qf = q.rowset.fields
q.rowset.first()
do while not q.rowset.endofset
if qf["M2Class"].value = "M-21"
qf["M2ClassNum"].value = " 1"
elseif qf["M2Class"].value = "W-21"
qf["M2ClassNum"].value = " 2"
elseif qf["M2Class"].value = "M-19" or qf["M2Class"].value = "M-40"
qf["M2ClassNum"].value = " 3"
elseif qf["M2Class"].value = "W-19" or qf["M2Class"].value = "W-35" or qf["M2Class"].value = "M-50"
qf["M2ClassNum"].value = " 4"
elseif qf["M2Class"].value = "W-50" or qf["M2Class"].value = "M-60"
qf["M2ClassNum"].value = " 5"
elseif qf["M2Class"].value = "W-60" or qf["M2Class"].value = "M-70"
qf["M2ClassNum"].value = " 6"
elseif qf["M2Class"].value = "M-12" or qf["M2Class"].value = "W-12"
qf["M2ClassNum"].value = " 7"
elseif qf["M2Class"].value = "M-14" or qf["M2Class"].value = "W-14"
qf["M2ClassNum"].value = " 8"
elseif qf["M2Class"].value = "M-16" or qf["M2Class"].value = "W-16"
qf["M2ClassNum"].value = " 9"
endif
if qf["M80Class"].value = "M-21"
qf["M80ClassNum"].value = " 1"
elseif qf["M80Class"].value = "W-21"
qf["M80ClassNum"].value = " 2"
elseif qf["M80Class"].value = "M-19" or qf["M80Class"].value = "M-40"
qf["M80ClassNum"].value = " 3"
elseif qf["M80Class"].value = "W-19" or qf["M80Class"].value = "W-35" or qf["M80Class"].value = "M-50"
qf["M80ClassNum"].value = " 4"
elseif qf["M80Class"].value = "W-50" or qf["M80Class"].value = "M-60"
qf["M80ClassNum"].value = " 5"
elseif qf["M80Class"].value = "W-60" or qf["M80Class"].value = "M-70"
qf["M80ClassNum"].value = " 6"
elseif qf["M80Class"].value = "M-12" or qf["M80Class"].value = "M-12"
qf["M80ClassNum"].value = " 7"
elseif qf["M80Class"].value = "M-14" or qf["M80Class"].value = "M-14"
qf["M80ClassNum"].value = " 8"
elseif qf["M80Class"].value = "M-16" or qf["M80Class"].value = "M-16"
qf["M80ClassNum"].value = " 9"
endif
if qf["FoxClass"].value = "M-21"
qf["FoxClassNum"].value = " 1"
elseif qf["FoxClass"].value = "M-19" or qf["FoxClass"].value = "M-40"
qf["FoxClassNum"].value = " 2"
elseif qf["FoxClass"].value = "W-19" or qf["FoxClass"].value = "W-35" or qf["FoxClass"].value = "M-60"
qf["FoxClassNum"].value = " 3"
elseif qf["FoxClass"].value = "W-21" or qf["FoxClass"].value = "M-50"
qf["FoxClassNum"].value = " 4"
elseif qf["FoxClass"].value = "W-50" or qf["FoxClass"].value = "M-70"
qf["FoxClassNum"].value = " 5"
elseif qf["FoxClass"].value = "W-60"
qf["FoxClassNum"].value = " 6"
elseif qf["FoxClass"].value = "M-12" or qf["FoxClass"].value = "M-12"
qf["FoxClassNum"].value = " 7"
elseif qf["FoxClass"].value = "M-14" or qf["FoxClass"].value = "M-14"
qf["FoxClassNum"].value = " 8"
elseif qf["FoxClass"].value = "M-16" or qf["FoxClass"].value = "M-16"
qf["FoxClassNum"].value = " 9"
endif
if qf["SprintClass"].value = "M-21"
qf["SprintClassNum"].value = " 1"
elseif qf["SprintClass"].value = "M-19" or qf["SprintClass"].value = "M-40" or qf["SprintClass"].value = "W-21"
qf["SprintClassNum"].value = " 2"
elseif qf["SprintClass"].value = "W-19" or qf["SprintClass"].value = "W-35" or qf["SprintClass"].value = "M-50"
qf["SprintClassNum"].value = " 3"
elseif qf["SprintClass"].value = "W-50" or qf["SprintClass"].value = "M-60"
qf["SprintClassNum"].value = " 4"
elseif qf["SprintClass"].value = "W-60" or qf["SprintClass"].value = "M-70"
qf["SprintClassNum"].value = " 5"
elseif qf["SprintClass"].value = "M-12" or qf["SprintClass"].value = "M-12"
qf["SprintClassNum"].value = " 6"
elseif qf["SprintClass"].value = "M-14" or qf["SprintClass"].value = "M-14"
qf["SprintClassNum"].value = " 7"
elseif qf["SprintClass"].value = "M-16" or qf["SprintClass"].value = "M-16"
qf["SprintClassNum"].value = " 8"
endif
q.rowset.next()
enddo
/*
Compute total amount paid for t-shirts.
*/
/*
q = new query()
q.sql = "select TShirtBlueWS, TShirtSlateWS, TShirtFuschiaWS,;
TShirtYellowWS, TShirtRedWS, TShirtGreenWS,;
TShirtBlueWM, TShirtSlateWM, TShirtFuschiaWM,;
TShirtYellowWM, TShirtRedWM, TShirtGreenWM,;
TShirtBlueWL, TShirtSlateWL, TShirtFuschiaWL,;
TShirtYellowWL, TShirtRedWL, TShirtGreenWL,;
TShirtBlueWXL, TShirtSlateWXL, TShirtFuschiaWXL,;
TShirtYellowWXL, TShirtRedWXL, TShirtGreenWXL,;
TShirtBlueXXS, TShirtSlateXXS, TShirtFuschiaXXS,;
TShirtYellowXXS, TShirtRedXXS, TShirtGreenXXS,;
TShirtBlueXS, TShirtSlateXS, TShirtFuschiaXS,;
TShirtYellowXS, TShirtRedXS, TShirtGreenXS,;
TShirtBlueSM, TShirtSlateSM, TShirtFuschiaSM,;
TShirtYellowSM, TShirtRedSM, TShirtGreenSM,;
TShirtBlueMD, TShirtSlateMD, TShirtFuschiaMD,;
TShirtYellowMD, TShirtRedMD, TShirtGreenMD,;
TShirtBlueLG, TShirtSlateLG, TShirtFuschiaLG,;
TShirtYellowLG, TShirtRedLG, TShirtGreenLG,;
TShirtBlueXL, TShirtSlateXL, TShirtFuschiaXL,;
TShirtYellowXL, TShirtRedXL, TShirtGreenXL,;
TShirtBlueXXL, TShirtSlateXXL, TShirtFuschiaXXL,;
TShirtYellowXXL, TShirtRedXXL, TShirtGreenXXL,;
TShirtBlue3XL, TShirtSlate3XL, TShirtFuschia3XL,;
TShirtYellow3XL, TShirtRed3XL, TShirtGreen3XL,;
TShirtBlueYS, TShirtSlateYS, TShirtFuschiaYS,;
TShirtYellowYS, TShirtRedYS, TShirtGreenYS,;
TShirtBlueYM, TShirtSlateYM, TShirtFuschiaYM,;
TShirtYellowYM, TShirtRedYM, TShirtGreenYM,;
TShirtBlueYL, TShirtSlateYL, TShirtFuschiaYL,;
TShirtYellowYL, TShirtRedYL, TShirtGreenYL from competitor"
msgbox( "sql t-shirts created","Done", 64 )
q.active = true
qf = q.rowset.fields
q.rowset.first()
do while not q.rowset.endofset
qf["ShirtFee"].value = qf["TShirtBlueWSFee"].value + qf["TShirtSlateWSFee"].value +;
qf["TShirtFuschiaWSFee"].value + qf["TShirtYellowWSFee"].value + uf["TShirtRedWSFee"].value +;
uf["TShirtGreenWSFee"].value + qf["TShirtBlueWMFee"].value + qf["TShirtSlateWMFee"].value +;
qf["TShirtFuschiaWMFee"].value + qf["TShirtYellowWMFee"].value + uf["TShirtRedWMFee"].value +;
uf["TShirtGreenWMFee"].value + qf["TShirtBlueWLFee"].value + qf["TShirtSlateWLFee"].value +;
qf["TShirtFuschiaWLFee"].value + qf["TShirtYellowWLFee"].value + uf["TShirtRedWLFee"].value +;
uf["TShirtGreenWLFee"].value + qf["TShirtBlueWXLFee"].value + qf["TShirtSlateWXLFee"].value +;
qf["TShirtFuschiaWXLFee"].value + qf["TShirtYellowWXLFee"].value + uf["TShirtRedWXLFee"].value +;
uf["TShirtGreenWXLFee"].value + qf["TShirtBlueXXSLFee"].value + qf["TShirtSlateWXXSFee"].value +;
qf["TShirtFuschiaXXSFee"].value + qf["TShirtYellowXXSFee"].value + uf["TShirtRedXXSFee"].value +;
uf["TShirtGreenXXSFee"].value + qf["TShirtBlueXSFee"].value + qf["TShirtSlateXSFee"].value +;
qf["TShirtFuschiaXSFee"].value + qf["TShirtYellowXSFee"].value + uf["TShirtRedXSFee"].value +;
uf["TShirtGreenXSFee"].value + qf["TShirtBlueSMFee"].value + qf["TShirtSlateSMFee"].value +;
qf["TShirtFuschiaSMFee"].value + qf["TShirtYellowSMFee"].value + uf["TShirtRedSMFee"].value +;
uf["TShirtGreenSMFee"].value + qf["TShirtBlueMDFee"].value + qf["TShirtSlateMDFee"].value +;
qf["TShirtFuschiaMDFee"].value + qf["TShirtYellowMDFee"].value + uf["TShirtRedMDFee"].value +;
uf["TShirtGreenMDFee"].value + qf["TShirtBlueLGFee"].value + qf["TShirtSlateLGFee"].value +;
qf["TShirtFuschiaLGFee"].value + qf["TShirtYellowLGFee"].value + uf["TShirtRedLGFee"].value +;
uf["TShirtGreenLGFee"].value + qf["TShirtBlueXLFee"].value + qf["TShirtSlateXLFee"].value +;
qf["TShirtFuschiaXLFee"].value + qf["TShirtYellowXLFee"].value + uf["TShirtRedXLFee"].value +;
uf["TShirtGreenXLFee"].value + qf["TShirtBlueXXLFee"].value + qf["TShirtSlateXXLFee"].value +;
qf["TShirtFuschiaXXLFee"].value + qf["TShirtYellowXXLFee"].value + uf["TShirtRedXXLFee"].value +;
uf["TShirtGreenXXLFee"].value + qf["TShirtBlue3XLFee"].value + qf["TShirtSlate3XLFee"].value +;
qf["TShirtFuschia3XLFee"].value + qf["TShirtYellow3XLFee"].value + uf["TShirtRed3XLFee"].value +;
uf["TShirtGreen3XLFee"].value + qf["TShirtBlueYSFee"].value + qf["TShirtSlateYSFee"].value +;
qf["TShirtFuschiaYSFee"].value + qf["TShirtYellowYSFee"].value + uf["TShirtRedYSFee"].value +;
uf["TShirtGreenYSFee"].value + qf["TShirtBlueYMFee"].value + qf["TShirtSlateYMFee"].value +;
qf["TShirtFuschiaYMFee"].value + qf["TShirtYellowYMFee"].value + uf["TShirtRedYMFee"].value +;
uf["TShirtGreenYMFee"].value + qf["TShirtBlueYLFee"].value + qf["TShirtSlateYLFee"].value +;
qf["TShirtFuschiaYLFee"].value + qf["TShirtYellowYLFee"].value + uf["TShirtRedYLFee"].value +;
uf["TShirtGreenYLFee"].value
msgbox( "Shirt Fee created","Done", 64 )
qf["ShirtTotal"].value = qf["TShirtBlueWS"].value + qf["TShirtSlateWS"].value +;
qf["TShirtFuschiaWS"].value + qf["TShirtYellowWS"].value + uf["TShirtRedWS"].value +;
uf["TShirtGreenWS"].value + qf["TShirtBlueWM"].value + qf["TShirtSlateWM"].value +;
qf["TShirtFuschiaWM"].value + qf["TShirtYellowWM"].value + uf["TShirtRedWM"].value +;
uf["TShirtGreenWM"].value + qf["TShirtBlueWL"].value + qf["TShirtSlateWL"].value +;
qf["TShirtFuschiaWL"].value + qf["TShirtYellowWL"].value + uf["TShirtRedWL"].value +;
uf["TShirtGreenWL"].value + qf["TShirtBlueWXL"].value + qf["TShirtSlateWXL"].value +;
qf["TShirtFuschiaWXL"].value + qf["TShirtYellowWXL"].value + uf["TShirtRedWXL"].value +;
uf["TShirtGreenWXL"].value + qf["TShirtBlueXXSL"].value + qf["TShirtSlateWXXS"].value +;
qf["TShirtFuschiaXXS"].value + qf["TShirtYellowXXS"].value + uf["TShirtRedXXS"].value +;
uf["TShirtGreenXXS"].value + qf["TShirtBlueXS"].value + qf["TShirtSlateXS"].value +;
qf["TShirtFuschiaXS"].value + qf["TShirtYellowXS"].value + uf["TShirtRedXS"].value +;
uf["TShirtGreenXS"].value + qf["TShirtBlueSM"].value + qf["TShirtSlateSM"].value +;
qf["TShirtFuschiaSM"].value + qf["TShirtYellowSM"].value + uf["TShirtRedSM"].value +;
uf["TShirtGreenSM"].value + qf["TShirtBlueMD"].value + qf["TShirtSlateMD"].value +;
qf["TShirtFuschiaMD"].value + qf["TShirtYellowMD"].value + uf["TShirtRedMD"].value +;
uf["TShirtGreenMD"].value + qf["TShirtBlueLG"].value + qf["TShirtSlateLG"].value +;
qf["TShirtFuschiaLG"].value + qf["TShirtYellowLG"].value + uf["TShirtRedLG"].value +;
uf["TShirtGreenLG"].value + qf["TShirtBlueXL"].value + qf["TShirtSlateXL"].value +;
qf["TShirtFuschiaXL"].value + qf["TShirtYellowXL"].value + uf["TShirtRedXL"].value +;
uf["TShirtGreenXL"].value + qf["TShirtBlueXXL"].value + qf["TShirtSlateXXL"].value +;
qf["TShirtFuschiaXXL"].value + qf["TShirtYellowXXL"].value + uf["TShirtRedXXL"].value +;
uf["TShirtGreenXXL"].value + qf["TShirtBlue3XL"].value + qf["TShirtSlate3XL"].value +;
qf["TShirtFuschia3XL"].value + qf["TShirtYellow3XL"].value + uf["TShirtRed3XL"].value +;
uf["TShirtGreen3XL"].value + qf["TShirtBlueYS"].value + qf["TShirtSlateYS"].value +;
qf["TShirtFuschiaYS"].value + qf["TShirtYellowYS"].value + uf["TShirtRedYS"].value +;
uf["TShirtGreenYS"].value + qf["TShirtBlueYM"].value + qf["TShirtSlateYM"].value +;
qf["TShirtFuschiaYM"].value + qf["TShirtYellowYM"].value + uf["TShirtRedYM"].value +;
uf["TShirtGreenYM"].value + qf["TShirtBlueYL"].value + qf["TShirtSlateYL"].value +;
qf["TShirtFuschiaYL"].value + qf["TShirtYellowYL"].value + uf["TShirtRedYL"].value +;
uf["TShirtGreenYL"].value
msgbox( "Shirt Total created","Done", 64 )
q.rowset.next()
enddo
use
*/
msgbox( "The Competitor table should now have data in it","Done", 64 )
/*
End of Program; CopyData.prg
*/
Warning: Unknown: write failed: No space left on device (28) in Unknown on line 0
Warning: Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct () in Unknown on line 0