// Main.prg
#Include "FiveWin.ch"
#Include "xBrowse.Ch"
//----------------------------------------
Func Main()
Local cFile,nStart,cDefa,nYear
Local xProvider,xSource,xPassword,xString
Local catNewDB,Saying,oCn,cSql,oRs
Local oDLG1,lOK1
Local cSay,oSay
Local oRsPtrips, oRsVeh
Local nCol
PUBLIC xAgency,xPool,aData,xConnect
SET DELETED on
SET CENTURY on
SET 3DLOOK on
nYear := ( year( DATE() )-1 )
SET EPOCH to ( nYEAR )
//fix for readonly gets
TGet():lDisColors := .f.
// where .exe started from is default directory //
cFile := GetModuleFileName( GetInstance() )
nStart := Rat( "\", cFile )
cDefa := Substr(cFile,1,nStart-1)
SET DEFA to ( cDefa )
xAgency := "J12"
xPool := "1500"
aData := {}
xProvider := "Microsoft.Jet.OLEDB.4.0"
xSource := cDEFA+"\Vehicle.mdb"
xPassword := "mar2016"
xSTRING := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Jet OLEDB:Database Password='+xPASSWORD
If .not. file( cDefa+"\Vehicle.Mdb" )
// create the adox object
Try
catNewDB := CreateObject("ADOX.Catalog")
Catch
MsgInfo( "Could not Create ADOX object")
Return(.f.)
End try
// create the table Vehicle.Mdb
Try
catNewDB:Create('Provider='+xProvider+';Data Source='+xSource+';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password='+xPASSWORD )
Catch
MsgInfo( "Could not create the table "+xSource )
oDLG:End()
Return(.f.)
End Try
catNewDB:ActiveConnection:Close()
catNewDB := nil
// create tables and test data
Try
oCn := CREATEOBJECT( "ADODB.Connection" )
Catch
MsgInfo( "Could not create the ADO object for connection")
Return(.f.)
End Try
TRY
oCn:Open( xString ) // xString
CATCH oErr
MsgInfo( "Could not open a Connection to Database "+xSource )
RETURN(.F.)
END TRY
cSql := "CREATE TABLE [Vehicles] "
cSql += "( "
cSql += "[VEHEID] char(18) NOT NULL, "
cSql += "[AGENEID] char(18) NULL, "
cSql += "[AGENCY] char(10) NULL, "
cSql += "[VNUMBER] char(5) NULL, "
cSql += "[LICENSE] char(8) NULL, "
cSql += "[POOLID] char(18) NULL, "
cSql += "[MOTORPOOL] char(10) NULL, "
cSql += "[NEXTSERVCE] datetime NULL, "
cSql += "[NEXTMILAGE] decimal(7,0) NULL, "
cSql += "[ACTIVE] char(8) NULL, "
cSql += "[V_TYPE] char(1) NULL, "
cSql += "[READONLY] char(1) NULL, "
cSql += "CONSTRAINT PK_VEHICLES PRIMARY KEY ( VEHEID )"
cSql += " )"
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Create Table VEHICLES Failed" )
oCn:CLose()
Return(.f.)
End try
cSql := "CREATE TABLE [P_trips] "
cSql += "( "
cSql += "[P_TRIPSEID] char(18) NOT NULL, "
cSql += "[AGENEID] char(18) NULL, "
cSql += "[AGENCY] char(10) NULL, "
cSql += "[POOLID] char(18) NULL, "
cSql += "[MTRPOOL] char(10) NULL, "
cSql += "[DATE] datetime NULL, "
cSql += "[DATE_BACK] datetime NULL, "
cSql += "[TIME] char(5) NULL, "
cSql += "[OUT_AMPM] char(2) NULL, "
cSql += "[TIME_BACK] char(5) NULL, "
cSql += "[MIL_OUT] decimal(4,0) NULL, "
cSql += "[MIL_IN] decimal(4,0) NULL, "
cSql += "[LNAME] char(15) NULL, "
cSql += "[VEHEID] char(18) NULL, "
cSql += "[VNUMBER] char(5) NULL, "
cSql += "[LICENSE] char(8) NULL, "
cSql += "CONSTRAINT [PK_P_TRIPS] PRIMARY KEY ( P_TRIPSEID )"
cSql += " )"
Try
oCn:Execute( cSQL )
Catch
MsgInfo( "Create Table P_TRIPS Failed" )
oCn:CLose()
Return(.f.)
End try
oCn:CLose()
oCn := nil
// global connection string
xConnect := CREATEOBJECT( "ADODB.Connection" )
TRY
xConnect:Open( xString )
CATCH oErr
Saying := "Could not open a Global Connection to Database "+xSource
MsgInfo( Saying )
RETURN(.F.)
END TRY
// add test data
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
cSQL := "SELECT * FROM Vehicles"
Try
oRS:Open(cSQL,xConnect )
CATCH oErr
MsgInfo( "Error in Opening VEHICLES table" )
oCn:CLose()
RETURN(.F.)
END TRY
oRs:AddNew()
oRs:Fields("VehEid"):Value := " 1406855386365952"
oRs:Fields("AgenEid"):Value := " 1664636899"
oRs:Fields("Agency"):Value := "J12"
oRs:Fields("Vnumber"):Value := "01001"
oRs:Fields("License"):Value := "SG12345"
oRs:Fields("PooliD"):Value := " 67918863443290"
oRs:Fields("MotorPool"):Value := "1500"
oRs:Fields("NextServce"):Value := ctod("01/01/2017")
oRs:Fields("NextMilage"):Value := 21000
oRs:Fields("Active"):Value := "ACTIVE"
oRs:Fields("V_Type"):Value := "V"
oRs:Fields("ReadOnly"):Value := "N"
oRs:Update()
oRs:AddNew()
oRs:Fields("VehEid"):Value := " 7905723512029186"
oRs:Fields("AgenEid"):Value := " 1664636899"
oRs:Fields("Agency"):Value := "J12"
oRs:Fields("Vnumber"):Value := "02085"
oRs:Fields("License"):Value := "SG86049"
oRs:Fields("PooliD"):Value := " 67918863443290"
oRs:Fields("MotorPool"):Value := "1500"
oRs:Fields("NextServce"):Value := ctod("08/25/2016")
oRs:Fields("NextMilage"):Value := 86430
oRs:Fields("Active"):Value := "ACTIVE"
oRs:Fields("V_Type"):Value := "V"
oRs:Fields("ReadOnly"):Value := "N"
oRs:Update()
oRs:CLose()
oRs := nil
// add test data
oRs := TOleAuto():New( "ADODB.Recordset" )
oRs:CursorType := 1 // opendkeyset
oRs:CursorLocation := 3 // local cache
oRs:LockType := 3 // lockoportunistic
cSQL := "SELECT * FROM P_Trips"
Try
oRS:Open(cSQL,xConnect )
CATCH oErr
MsgInfo( "Error in Opening P_TRIPS table" )
oCn:CLose()
RETURN(.F.)
END TRY
oRs:AddNew()
oRs:Fields("P_TripsEid"):Value := " 1416563490"
oRs:Fields("AgenEid"):Value := " 1664636899"
oRs:Fields("Agency"):Value := "J12"
oRs:Fields("Vnumber"):Value := "01001"
oRs:Fields("License"):Value := "SG12345"
oRs:Fields("PooliD"):Value := " 67918863443290"
oRs:Fields("MtrPool"):Value := "1500"
oRs:Fields("Date"):Value := ctod("02/21/2017")
oRs:Fields("Date_Back"):Value := ctod("02/22/2017")
oRs:Fields("Mil_Out"):Value := 1026
oRs:Fields("Mil_In"):Value := 1700
oRs:Fields("Lname"):Value := "Smith"
oRs:Fields("VehEid"):Value := " 1406855386365952"
oRs:Fields("VNumber"):Value := "01001"
oRs:Fields("License"):Value := "SG12345"
oRs:Update()
oRs:AddNew()
oRs:Fields("P_TripsEid"):Value := " 6816226149751042"
oRs:Fields("AgenEid"):Value := " 1664636899"
oRs:Fields("Agency"):Value := "J12"
oRs:Fields("Vnumber"):Value := "02085"
oRs:Fields("License"):Value := "SG86049"
oRs:Fields("PooliD"):Value := " 67918863443290"
oRs:Fields("MtrPool"):Value := "1500"
oRs:Fields("Date"):Value := ctod("02/21/2017")
oRs:Fields("Date_Back"):Value := ctod("02/23/2017")
oRs:Fields("Mil_Out"):Value := 1027
oRs:Fields("Mil_In"):Value := 1700
oRs:Fields("Lname"):Value := "Fischer"
oRs:Fields("VehEid"):Value := " 7905723512029186"
oRs:Fields("VNumber"):Value := "02085"
oRs:Fields("License"):Value := "SG86049"
oRs:Update()
oRs:CLose()
oRs := nil
xConnect:Close()
xConnect := nil
Endif
// global connection string
xConnect := CREATEOBJECT( "ADODB.Connection" )
TRY
xConnect:Open( xString )
CATCH oErr
Saying := "Could not open a Global Connection to Database "+xSource
MsgInfo( Saying )
RETURN(.F.)
END TRY
_LightGreyGrad()
lOK1 := .F.
oRsPTrips := " "
oRsVeh := " "
oRsTrips := " "
nCol := 0
aData := {} // defined public
SysReFresh()
lOK1 := .F.
DEFINE DIALOG oDlg1 ;
FROM 5, 8 to 10, 75 ;
TITLE "Please be patient " ;
STYLE nOr( WS_POPUP,WS_CAPTION,WS_THICKFRAME )
cSAY := "Opening Initial Reservation Data for Motorpool "+alltrim( xPOOL )
@ 1,10 SAY oSay var cSAY of oDLG1 COLOR "N/W"
oDLG1:bStart := { | | lOK1 := _OpenUm( oDlg1,@cSay,oSay,;
@oRsPTrips,@oRsVeh,@nCol ) }
ACTIVATE DIALOG oDLG1 CENTERED
IF lOK1 = .F.
SysReFresh()
RETURN(.F.)
ENDIF
_Rbrow( oRsPTrips,oRsVeh,nCol )
RETURN(.T.) // ends program
//----------------------------
Static Func _OpenUM( oDLG1,cSay,oSay,oRsPTrips,oRsVeh,nCol ) //, cTEMP )
// second parameter = 'Y' .. create temp database
// second parameter = 'N' .. do not create
LOCAL SAYING, DBF_STRU, cDAY, i
LOCAL nSPREAD,dTODAY
LOCAL cSQL, oERR
SysReFresh()
cSAY := "Opening Initial Reservation Data for Motorpool "+alltrim( xPOOL )+" P_Trips"
oSay:ReFresh()
SysReFresh()
cSQL := "SELECT * from P_TRIPS where AGENCY = '"+xAGENCY+"' "
cSql += "and MTRPOOL = '"+xPOOL+"' order by LICENSE,MIL_OUT,Date" // DATE DESC" //,MIL_OUT"
oRsPTRIPS := TOleAuto():New( "ADODB.Recordset" )
oRsPTRIPS:CursorType := 1 // opendkeyset
oRsPTRIPS:CursorLocation := 3 // local cache
oRsPTRIPS:LockType := 3 // lockoportunistic
TRY
oRsPTRIPS:Open( cSQL,xConnect )
CATCH oErr
MsgInfo( "Error in Opening P_TRIPS table" )
oDLG1:END()
RETURN(.F.)
END TRY
IF oRsPtrips:eof
SAYING := "SORRY ..No Reservations found .. "+CHR(10)
SAYING += "for Agency "+alltrim(xAGENCY)+" and Motorpool "+alltrim( xPOOL )+CHR(10)
SAYING += "Would you like to Continue ?"+CHR(10)
IF MsgYesNo( SAYING )
ELSE
oRsPtrips:Close()
oDLG1:END()
RETURN(.F.)
ENDIF
ENDIF
cSAY := "Opening Initial Reservation Data for Motorpool "+alltrim( xPOOL )+" Vehicles"
oSay:ReFresh()
SysReFresh()
cSQL := "SELECT * from VEHICLES where AGENCY = '"+xAGENCY+"' and MOTORPOOL = '"+xPOOL+"'order by VNUMBER"
oRsVeh := TOleAuto():New( "ADODB.Recordset" )
oRsVeh:CursorType := 1 // opendkeyset
oRsVeh:CursorLocation := 3 // local cache
oRsVeh:LockType := 3 // lockoportunistic
TRY
oRsVeh:Open( cSQL,xConnect )
CATCH oErr
MsgInfo( "Error in Opening VEHICLES table" )
oRsPtrips:Close()
oDLG1:END()
RETURN(.F.)
END TRY
IF oRsVeh:eof
SAYING := "Sorry ... no Vehicles are assigned to the "
SAYING += Alltrim(xPOOL)+" Motorpool"
MsgInfo( SAYING )
oRsPtrips:Close()
oDLG1:END()
RETURN(.F.)
ENDIF
oDLG1:END()
RETURN(.T.)
//------------------------------
Static Func _Rbrow( oRsPTrips,oRsVeh,nCol )
LOCAL oBTN1,oBTN2,oBTN3,oBTN4,oBTN5,oBTN6,oBTN7
LOCAL cVNUMBER
Local oIco,cTitle,oCol,oCol1
Local oSay1,oSay2,oSay3,oSay4,oSay5,oSay6
Local cSay1,cSay2,cSay3,cSay4,cSay5,cSay6
Local oFontB,i
Local cMOTORPOOL, oMOTORPOOL
Local oMOTOR, oDLG, oDATE1, oDATE2
Local oSAY, cSAY
Local oEmp,oLbx,xMotor,xTitle
Local aHeader//,aData
Local dDate1,dDate2
dDate1 := Date()
dDate2 := Date()
aData := {} // is public
aHeader := {} // is local and passed by reference
msginfo( "Before ReFresh" )
xbrowse( aData )
//------- refreshes aData
SysReFresh()
_ReFrsh( @dDATE1, @dDate2, oRsPtrips, oRsVeh, "RESERVATION", @aHeader ) // resets aData and refreshes reservations
SysReFresh()
//------- motorpool browse
msginfo( "After Refresh" )
xbrowse( aData )
oFontB := TFont():New("Ms Sans Serif",,-6,.F.,.T. ,,,,.F. )
cMOTORPOOL := xPOOL
xMOTOR := "Agency "+alltrim(xAGENCY)+" for Motorpool "+alltrim( xPOOL )
xTitle := xMotor
cTitle := "Reservation Information"
cSay1 := "Motorpool"
cSay2 := "For Dates"
cSay3 := " thru "
cSay4 := "Legend"
cSay5 := "Within 500 miles of needing Service"
cSay6 := "Service is Over Due by Date or Mileage"
DEFINE DIALOG oEMP RESOURCE "RESBROW" ;
TITLE cTitle
REDEFINE SAY oSay1 var cSay1 ID 121 of oEMP // motorpool
REDEFINE SAY oSay2 var cSay2 ID 122 of oEMP // for dates
REDEFINE SAY oSay3 var cSay3 ID 123 of oEMP // thru
REDEFINE SAY oSay4 var cSay4 ID 124 of oEMP // legend
REDEFINE GET oSay5 var cSay5 MEMO ID 125 of oEMP COLOR CLR_BLACK, CLR_YELLOW READONLY // within 500 miles
oSay5:SetFont( oFontB )
REDEFINE GET oSay6 var cSay6 MEMO ID 126 of oEMP COLOR CLR_WHITE, RGB(192,3,51) READONLY // past due
oSay6:SetFont( oFontB )
REDEFINE SAY oMOTORPOOL var cMOTORPOOL ID 114 of oEMP
REDEFINE SAY oMOTOR var xMOTOR ID 113 of oEMP
REDEFINE SAY oDATE1 var dDATE1 ID 119 of oEMP
REDEFINE SAY oDATE2 var dDATE2 ID 120 of oEMP
REDEFINE xBROWSE oLbx ;
ARRAY aData ;
COLUMNS aHeader ; // aHeader
ID 111 OF oEMP ;
AUTOSORT AUTOCOLS LINES CELL
WITH OBJECT oLbx
:lTabLikeExcel := .t.
END
* For i = 6 to LEN( oLbx:aCols )
* oCol1 := oLbx:aCols[ i ]
* oLbx:aCols[ i ]:bClrStd := _ChangeColor( oLbx, i, oCol1 )
* Next
oLbx:lRecordSelector := .f.
oLbx:nRowHeight := 30
oLbx:nFREEZE := 5
* oLbx:lFooter := .t.
oCol := oLbx:aCols[ 1 ]
oCol:bFooter := { || Ltrim( Str( oLbx:KeyNo() ) ) + " / " + LTrim( Str( oLbx:KeyCount() ) ) }
oLbx:bChange := { || oCol:RefreshFooter() }
_BrowColor( oLbx )
oLbx:bClrGrad := { | lInvert | If( ! lInvert, ;
{ { 0.50, 15790320, 15790320 }, ;
{ 0.50, 15790320, 15790320 } }, ;
{ { 0.50, 15790320, 15790320 }, ;
{ 0.50, 15790320, 15790320 } } ) }
* oLbx:bClrStd := { || { _SelFore( A->CUR_MIL, A->SERV_DUE,A->NEXTSERVCE ), _SelBack( A->CUR_MIL, A->SERV_DUE, A->NEXTSERVCE ) }}
REDEFINE BTNBMP oBtn1 ID 112 of oEmp ; // details
PROMPT "Details" LEFT 2007;
ACTION ( nil );
GRADIENT GreyButtonGrad()
REDEFINE BTNBMP oBtn2 ID 117 of oEmp ; // add reservation
PROMPT "Add Reserv" LEFT 2007;
ACTION ( nil );
GRADIENT GreyButtonGrad()
REDEFINE BTNBMP oBtn3 ID 115 of oEmp ; // quit
RESOURCE "CANCEL","DCANCEL","DCANCEL" ;
PROMPT "Quit" LEFT 2007;
ACTION ( oEmp:End() );
GRADIENT GreyButtonGrad()
REDEFINE BTNBMP oBtn4 ID 116 of oEmp ; // change motorpool
PROMPT "Chg Mtrpool" LEFT 2007;
ACTION ( Nil ) ;
GRADIENT GreyButtonGrad()
REDEFINE BTNBMP oBtn5 ID 118 of oEmp ;
PROMPT "Refresh" LEFT 2007;
ACTION( nil );
GRADIENT GreyButtonGrad()
ACTIVATE DIALOG oEMP ;
ON INIT ( _XbrGoToCol( oLbx, 1),oLbx:SetFocus());
VALID(!GETKEYSTATE( 27 ))
oRsPTrips:CLose()
oRsVeh:Close()
RELEASE oFontB
RETURN( NIL )
//---------------------------------------
Static Func _ReFrsh( dDATE1, dDate2, oRsPtrips, oRsVeh, cFrom, aHeader )
Local cSay,oSay,oDlg2
SysReFresh()
DEFINE DIALOG oDlg2 ;
FROM 5, 8 to 10, 72 ;
COLOR "N/W" ;
Title "Please Wait" ;
STYLE nOr( WS_POPUP,WS_CAPTION,WS_THICKFRAME )
cSay := "Generating\Refreshing Reservation Info"
@ 1,1 SAY oSay var cSay of oDLG2 COLOR "N/W"
oDLG2:bStart := { || _Doit( @dDATE1, @dDate2, oRsPtrips, oRsVeh, cFrom, @aHeader, @cSay,oSay, oDlg2 ) }
ACTIVATE DIALOG oDLG2 CENTERED
RETURN(.T.)
//-------------------------
Static Func _Doit( dDATE1, dDate2, oRsPtrips, oRsVeh, cFrom, aHeader, cSay, oSay,oDlg2 )
LOCAL cNAME,cLICENSE
LOCAL dTODAY, nENDMILES
Local cColor,cLast,nTimes,dLast
Local xDay
Local xName
Local nRec
Local nCol,nSpread,i,aLine,cDay,cHeader,cData
Local dNextService,nServiceDue
Local cBlank,dStart,dEnd
Local cVehicleInfo,dDay
SysReFresh()
If Empty(cFrom)
cFrom := "RESERVATION"
Endif
nColor := -1
aSize( aData,0 ) // contains the appointments
aSize( aHeader,0 ) // contains the header
dDATE1 := DATE()
dDATE2 := DATE()
IF .not. oRsPtrips:eof
oRsPtrips:MoveFirst()
// get the first record for begining date spread
// and check for over 45 days
IF ( DATE() - oRsPtrips:Fields("DATE"):Value ) > 45
IF empty(oRsPtrips:Fields("DATE"):Value)
ELSE
SAYING := "There are Reservations over 45 days old that must be "+chr(10)
SAYING += "RESOLVED "+DTOC(oRsPtrips:Fields("DATE"):Value)+" "+oRsPtrips:Fields("VNUMBER"):Value+chr(10)
SAYING += " "+oRsPtrips:Fields("LNAME"):Value+chr(10)
MsgInfo( SAYING )
ENDIF
dDATE1 := DATE()-45
ELSE
dDATE1 := _TtoDate(oRsPtrips:Fields("DATE"):Value)
ENDIF
IF dDATE1 > DATE()
dDATE1 := DATE()
ENDIF
dDATE2 := DATE()+30
// reset to top
oRsPtrips:MoveFirst()
ELSE
dDATE1 := DATE()-1
dDATE2 := DATE()+30
ENDIF
nSPREAD := ( dDATE2 - dDATE1 )
dTODAY := dDATE1
nCOL := 0
// create the array header
cHeader := "{ "
cHeader += "{ 1, 'Vnumber', nil, 90 }, "
cHeader += "{ 2, 'License', nil, 90 }, "
cHeader += "{ 3, 'Cur_Mil', nil, 90 }, "
cHeader += "{ 4, 'NextService', nil, 90 }, "
cHeader += "{ 5, 'Serv_Due', nil, 90 }, "
For i = 0 to nSPREAD
cDAY := substr( _Dom(dTODAY),1,3)+"_" +;
substr( CMONTH(dTODAY),1,3)+"_"+;
strzero( DAY(dTODAY),2)+"_"+;
str( Year(dToday),4)
nRec := ( 6+i )
nRec := alltrim(str(nRec))
If i = nSpread
cHeader += "{ '"+nRec+"', '"+cDay+"', nil, 115 } "
Else
cHeader += "{ '"+nRec+"', '"+cDay+"', nil, 115 }, "
Endif
IF dTODAY = DATE()
IF i > 3
nCOL := (i+2)
ENDIF
ENDIF
dTODAY++
NEXT
cHeader += " }"
aHeader := &cHeader
*xbrowse( aHeader )
Do Case
Case cFrom = "RESERVATION"
* oRsTrips:Filter := ""
* oRsTrips:ReQuery()
* oRsTrips:Sort := "LICENSE,DATE_BACK DESC" // vnumber
Case cFrom = "MOTORPOOL"
oRsVeh:CLose()
* oRsTrips:CLose()
oRsPtrips:CLose()
cSQL := "SELECT * from P_TRIPS where AGENCY = '"+xAGENCY+"' and MTRPOOL = '"+xPOOL+"' order by VNUMBER,MIL_OUT"
TRY
oRsPTRIPS:Open( cSQL,xConnect )
CATCH oErr
MsgInfo( "Error in Opening P_TRIPS table" )
oDlg2:End()
RETURN(.F.)
END TRY
cSQL := "SELECT * from VEHICLES where AGENCY = '"+xAGENCY+"' and MOTORPOOL = '"+xPOOL+"'order by VNUMBER"
TRY
oRsVeh:Open( cSQL,xConnect )
CATCH oErr
MsgInfo( "Error in Opening VEHICLES table" )
oDlg2:End()
RETURN(.F.)
END TRY
EndCase
// only has vehicles selected by xpool
*nRec := oRsVeh:RecordCount()
*msginfo( nRec )
If oRsVeh:Eof // aData will be empty
oDlg2:End()
Return(.f.)
Endif
// create the data array
oRsVeh:MoveFirst()
nTimes := 0
DO WHILE .not. oRsVeh:eof
cVnumber := oRsVeh:Fields("VNUMBER"):Value
cSay := "Generating\Refreshing Reservation Info "+cVnumber
oSay:ReFresh()
SysReFresh()
IF (oRsVeh:Fields("ACTIVE"):Value = "ACT" .or. ;
oRsVeh:Fields("ACTIVE"):Value = "ACQ" .or. ;
oRsVeh:Fields("ACTIVE"):Value = "SELL" ) .and.;
(oRsVeh:Fields("V_TYPE"):Value = "V" .and. oRsVeh:Fields("readonly"):Value <> 'Y' )
ELSE
oRsVeh:MoveNext()
LOOP
ENDIF
If cVnumber = "01001" .or. cVnumber = "02085"
ELse
oRsVeh:MoveNext()
Loop
Endif
nTimes++
cLICENSE := oRsVeh:fields("license"):Value
If nTimes = 1
nENDMILES := 0
Else
nEndMiles := 85378
Endif
* oRsTrips:Filter := ""
* oRsTrips:Filter := "license = '"+cLICENSE+"'"
// trips should be selected by license, date desc
* IF .not. oRsTrips:eof
* nENDMILES := if(empty(oRsTrips:Fields("endmiles"):Value),0,;
* oRsTrips:Fields("endmiles"):Value)
* ENDIF
nEndMiles := int(nEndMiles)
cVnumber := oRsVeh:Fields("VNUMBER"):Value
cLicense := oRsVeh:Fields("LICENSE"):Value
dNextService := if(empty(oRsVeh:Fields("NEXTSERVCE"):Value), (date()-1), oRsVeh:Fields("NEXTSERVCE"):Value )
nServiceDue := if(empty(oRsVeh:Fields("NEXTMILAGE"):Value),0,;
oRsVeh:Fields("NEXTMILAGE"):Value)
nServiceDue := int(nServiceDue)
cVehicleInfo := cVnumber+"', '"+cLicense+"', '"+ltrim(str(nEndMiles))+"', '"+dtoc(dNextService)+"', '"+ltrim(str(nServiceDue))
oRsPTrips:Filter := "[Vnumber] = '"+cVnumber+"'"
* xbrowse( oRsPtrips )
// not found
If oRsPTrips:Eof
msginfo( "Not Found" )
cData := "{ '"+cVehicleInfo+"'"
cBlank := " "
For i = 6 to len( aHeader )
If i = len(aHeader)
cData := cData+", '"+cBlank+" }"
Else
cData := cData+", '"+cBlank+"'"
Endif
Next
AAdd( aData, &cData )
oRsVeh:MoveNext()
Loop
Endif
// found Vehicle
* msginfo( "Found Vehicle "+cVnumber )
oRsPtrips:MoveFirst() // license,mil_out,date
* msginfo( cVehicleInfo )
cData := "{ '"+cVehicleInfo+"'"
Do While .not. oRsPtrips:EOF
For i = 6 to len( aHeader )
cDay := aHeader[i][2]
xDay := _ChkMth( substr(cDay,5,3)) // 02
dDay := ctod( xDay+"/"+substr(cDay,9,2)+"/"+substr(cDay,12,4)) // 02/20/2017
dStart := _TtoDate( oRsPtrips:Fields("Date"):Value ) // 02/20/2017
dEnd := _TtoDate( oRsPtrips:Fields("Date_Back"):Value ) // 02/20/2017
If dDay >= dStart .and. dDay <= dEnd
cName := Alltrim( oRsPtrips:Fields("LNAME"):Value )
Else
cName := " "
Endif
cData += ", "+"'"+cName+"'"
* msginfo( "In For Next"+str(i) )
* msginfo( cData )
Next
cData += " }"
msginfo( "See Data" )
msginfo( cData )
AAdd( aData, &cData )
oRsPtrips:MoveNext()
Enddo
* msginfo( "MoveNext Vehicle" )
oRsVeh:MoveNext()
Enddo
*msginfo( "End Run" )
*xbrowse(AdATA)
oDlg2:End()
Return(.t.)
//------------------
Static Func _LightGreyGrad()
SetDlgGradient( { { .50, nRGB( 216, 216, 216 ), nRGB( 255, 255, 255 ) } } )
Return(nil)
//-------------------------
Static Func _ChkMth( cMonth )
Local xMonth
xMonth := "00"
DO Case
Case upper(cMonth) = "JAN"
xMonth := "01"
Case upper(cMonth) = "FEB"
xMonth := "02"
Case upper(cMonth) = "MAR"
xMonth := "03"
Case upper(cMonth) = "APR"
xMonth := "04"
Case upper(cMonth) = "MAY"
xMonth := "05"
Case upper(cMonth) = "JUN"
xMonth := "06"
Case upper(cMonth) = "JUL"
xMonth := "07"
Case upper(cMonth) = "AUG"
xMonth := "08"
Case upper(cMonth) = "SEP"
xMonth := "09"
Case upper(cMonth) = "OCT"
xMonth := "10"
Case upper(cMonth) = "NOV"
xMonth := "11"
Case upper(cMonth) = "DEC"
xMonth := "12"
ENdCase
Return(xMonth)
//-------------------------------------
Static Func _XbrGoToCol( oBrw, nCol )
oBrw:nColOffSet := nCol - oBrw:nFreeze
oBrw:nColSel := nCol
oBrw:ReFresh()
SysReFresh()
return nil
//-------------------
Static Func GreyButtonGrad()
// 2010 grey button skin
Local bGrad
bGrad := { | lInvert | If( ! lInvert, ;
{ { 1, nRGB( 255, 255, 255 ), nRGB( 207, 207, 207 ) } }, ;
{ { 1/3, nRGB( 255, 253, 222 ), nRGB( 255, 231, 151 ) }, ;
{ 2/3, nRGB( 255, 215, 84 ), nRGB( 255, 233, 162 ) } } ) }
Return( bGrad )
//--------------------------
Static Func _TtoDate( tDate )
If empty( tDate)
Return( ctod("00/00/00"))
Endif
If ValType( tDate ) = "D"
Return(tDate )
Endif
Return( stod( substr( ttos( tDate ), 1, 8 ) ))
//----------------------
Static Func _BrowColor( oLbx )
local aGradBarSelFocus := {{1, RGB(0,128,255) , RGB(0,128,255) }} // in focus
local aGradBarSel := {{1, RGB(255,255,255), RGB(192,192,192) }} // not in focus 192
WITH OBJECT oLbx
:bClrSel := {|| { CLR_BLACK, aGradBarSel } } // not in focus
:bClrSelFocus := { || { CLR_WHITE, aGradBarSelFocus } } // in focus
END
Return(nil)
//-----------------------
Static Func _Dom( dDATE)
LOCAL nDAY, cDAY
nDAY := DOW( dDATE )
DO CASE
CASE nDAY = 1
cDAY := "Sunday"
CASE nDAY = 2
cDAY := "Monday"
CASE nDAY = 3
cDAY := "Tuesday"
CASE nDAY = 4
cDAY := "Wednesday"
CASE nDAY = 5
cDAY := "Thursday"
CASE nDAY = 6
cDAY := "Friday"
CASE nDAY = 7
cDAY := "Saturday"
ENDCASE
RETURN(cDAY)
// end Main.prg
