Function to Search on dDf with Tdatabase

Function to Search on dDf with Tdatabase

Postby Silvio.Falconi » Fri Sep 15, 2023 9:13 am

I have this structure dbf and I use Tdatabase

Code: Select all  Expand view  RUN
aStructure = { { "date"       , "D",    8, 0 },;
                          { "rooms_id"   , "C",    4, 0 } ,;
                          { "check_in"   , "D",    8, 0 } ,;
                          { "check_out"  , "D",    8, 0 } ,;
                          { "status"     , "C",    2, 0 } ,;
                          { "type"       , "C",    2, 0 } ,;
                          { "morning"    , "L",    1, 0 } ,;
                          { "evening"    , "L",    1, 0 } ,;
                          { "first"      , "C",   15, 0 } ,;
                          { "last"       , "C",   15, 0 } ,;
                          { "guest"      , "C",   30, 0 } ,;
                          { "numpre"     , "C",   18, 0 } ,;
                          { "invoice"    , "C",   18, 0 } ,;
                          { "subtotal"   , "N",   12, 2 } ,;
                          { "descount"   , "N",   12, 2 } ,;
                          { "datapayed"  , "d",    8, 0 } ,;
                          { "deposit"    , "N",   12, 2 } ,;
                          { "topay"      , "N",   12, 2 } ,;
                          { "total"      , "N",   12, 2 } ,;
                          { "note"       , "C",   80, 0 } ,;
                          { "omaggio"    , "L",    1, 0 } ,;
                          { "listino"    , "C",    2, 0 } ,;
                          { "sector"     , "C",    1, 0 } }


and the dbf archive index is

Code: Select all  Expand view  RUN
USE RESERVA ALIAS RESERVA VIA "DBFCDX" NEW
   INDEX ON ROOMS_ID + DToS( CHECK_IN ) TAG room_in TO reserva  for ! deleted()
   INDEX ON ROOMS_ID + TYPE + DToS( CHECK_IN ) TAG  room_intype TO RESERVA for ! deleted()



sample of Record
Code: Select all  Expand view  RUN

Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "06"
   Replace  RESERVA->check_in   With  ctod("07/06/2023")
   Replace  RESERVA->check_out  With  ctod("09/06/2023")
   Replace  RESERVA->Status     With  "02"
   Replace  RESERVA->guest      With  "Bill Gates"
   Replace  RESERVA->morning    With  .f.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "01"


I wish search on dbf archive if the rooms_id+type is free checking these parameter

Check_in
Check_out
morning
evening

for the date field (Check_in,Check_out) are passed at search time

morning and evening fields means whether they are busy in the morning or in the afternoon or all day sample

by default ( all day)

morning := .t.
evening := .t

sample of Record
Code: Select all  Expand view  RUN

Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "06"
   Replace  RESERVA->check_in   With  ctod("07/06/2023")
   Replace  RESERVA->check_out  With  ctod("09/06/2023")
   Replace  RESERVA->Status     With  "02"
   Replace  RESERVA->guest      With  "Bill Gates"
   Replace  RESERVA->morning    With  .f.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "01"


it means that room no. 6 type 01 is occupied (status 2) from 7 June 2023 to 9 June 2023 only in the afternoon


last year NAges made a sample of function but now that sample not run with new parameters (morning and evening) and I need it because I have to make a software for a hotel that rents rooms in the morning or in the afternoon or all day
Code: Select all  Expand view  RUN
#include "FiveWin.ch"
#include "dtpicker.ch"

request dbfcdx
request dbffpt

request hb_lang_it
request hb_codepage_itwin

function Main()

   RddSetDefault( "DBFCDX" )
   HB_LANGSELECT( "IT" )
   HB_SETCODEPAGE( "ITWIN" )
   SetHandleCount( 100 )
   FWNumFormat( "E", .t. )
   SetGetColorFocus()

   SET DATE FORMAT "dd-mm-yyyy"
   SET DELETED     ON
   SET CENTURY     ON
   SET EPOCH TO    year( date() ) - 20
   SET MULTIPLE    OFF

      BuildDbf()

   Test()

return nil

function test()

   local oReserva
   local oDlg, oBar, oBrw, oFont

   oReserva := TReserva():New()
   oReserva :setorder( 0 )
   oReserva:GoTop()

/*
   oReserva:goto(6) // nrecord number 6
   oReserva:Edit()
*/


   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-12
   DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL FONT oFont TITLE "RESERVA"
   DEFINE BUTTONBAR oBar OF oDlg SIZE 80,32 2007

   @ 32,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg DATASOURCE oReserva ;
      COLUMNS "DATE","ROOMS_ID","CHECK_IN","CHECK_OUT","STATUS","TYPE","MORNING","EVENING" ;
      LINES NOBORDER

   WITH OBJECT oBrw
      :nMarqueeStyle := MARQSTYLE_HIGHLROW
      :RecSelShowRecNo()
      :AddVar( "nBooked", 0 )
      :bClrStd := { || If( oBrw:BookMark == oBrw:nBooked, { CLR_WHITE, CLR_HRED }, { CLR_BLACK, CLR_WHITE } ) }
      //
      :CreateFromCode()
   END

   DEFINE BUTTON OF oBar PROMPT "New"    CENTER ACTION oBrw:EditSource( .t. )
   DEFINE BUTTON OF oBar PROMPT "Modify" CENTER ACTION oBrw:EditSource()

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil


function Edit_Reservation( oRec )

   local oDlg, oFont, oBold, oSay, oGrp, oBtn, bCheck, cText := ""
   local aGet[6]
   local oBrw  := oRec:oBrw
   local lNew  := ( oRec:RecNo == 0 )
   local lSave := .f.
   local lFree := .t.

   local nTypeday



   DEFINE FONT oFont NAME "MS Sans Serif" SIZE 0,-8
   oBold := oFont:Bold()

   DEFINE DIALOG oDlg SIZE 276,280 PIXEL TRUEPIXEL ;
                   TITLE If( lNew, "New","Modifica" ) + " record" FONT oFont

   @ 0, 8 GROUP oGrp TO 240, 268 OF oDlg PIXEL

   @  24, 20 SAY "Number:" OF oDlg SIZE 52, 16 PIXEL FONT oFont
   @  20, 94 GET aGet[1]  VAR oRec:Rooms_Id  PICTURE "9999" OF oDlg SIZE 40, 24 PIXEL FONT oFont

   @  52, 20 SAY "Type:" OF oDlg SIZE 36, 16 PIXEL FONT oFont
   @  48, 94 GET aGet[2]  VAR oRec:Type   PICTURE "99" OF oDlg SIZE 20, 24 PIXEL FONT oFont

   @  80, 20 SAY "Check In:" OF oDlg SIZE 54, 16 PIXEL FONT oFont
   @  76, 94 DTPICKER aGet[3]  VAR oRec:Check_In OF oDlg SIZE 108, 24 PIXEL FONT oFont

   @ 108, 20 SAY "Check Out:" OF oDlg SIZE 64,16 PIXEL FONT oFont
   @ 104, 94 DTPICKER aGet[4]  VAR oRec:Check_Out OF oDlg SIZE 108, 24 PIXEL FONT oFont   ;

   @  152, 20 SAY "Type of Day:" OF oDlg SIZE 64,16 PIXEL FONT oFont
   @  148, 115 CHECKBOX aGet[5] VAR oRec:morning Prompt "&Morning"  OF oDlg PIXEL SIZE 60, 25
   @  148, 185 CHECKBOX aGet[6] VAR oRec:evening Prompt "&Evening"  OF oDlg PIXEL SIZE 60, 25

   @ 208,  8 SAY oSay PROMPT cText SIZE 260,24 PIXEL OF oDlg CENTER VCENTER FONT oBold

   @ 252,  96 BUTTON oBtn PROMPT "Confirm" OF oDlg SIZE 84, 24 PIXEL FONT oFont ;
      WHEN oRec:Modified() .and. lFree ;
   DEFAULT ACTION ( lSave := .t., oDlg:End() )

   @ 252, 184 BUTTON oBtn PROMPT "Exit"    OF oDlg SIZE 84, 24 PIXEL FONT oFont ;
      CANCEL ACTION (oDlg:End())

   bCheck   := { || lFree := IsFree( oRec, oSay ), oDlg:AEvalWhen(), .t. }

   AEval( aGet, { |o| o:bValid := bCheck } )
   AEval( aGet, { |o| o:bChange := bCheck }, 3, 2 )


   ACTIVATE DIALOG oDlg CENTERED ON INIT Eval( bCheck )
   RELEASE FONT oFont, oBold

   if oBrw != nil .and. oBrw:nBooked != 0
      oBrw:nBooked   := 0
      oBrw:Refresh()
   endif

   IF lSave
      oREc:date:=date()
      oRec:SAVE()
   ENDIF

return nil

//----------------------------------------------------------------------------//

function Isfree( oRec, oSay )
   local lNew     := ( oRec:RecNo == 0 )
   local oDbf     := oRec:uSource
   local oBrw     := oRec:oBrw
   local lreturn := .t.
   local cSearch, nBooked := 0, hBooked

   cSearch  := "ROOMS_ID == ? .AND. ALLTRIM(TYPE) == ? .AND. RECNO() != ? .AND. " + ;
               "CHECK_IN <= ? .AND. CHECK_OUT >= ?  .AND. MORNING !=? .AND. EVENING !=?"

   if Empty( oRec:rooms_id ) .or. Empty( oRec:Type ) .or. Empty( oRec:check_in ) .or. ;
      Empty( oRec:check_out ) .or. oRec:check_in > oRec:check_out  .or.;
      Empty(oRec:morning) .or. Empty(oRec:evening)

      lreturn     := .f.
      oSay:VarPut( "INVALID DATA" )
      oSay:SetColor( CLR_WHITE, CLR_RED )
      oSay:Refresh()

   else
      cSearch  := oDbf:ApplyParams( cSearch, { oRec:rooms_id, oRec:type, oRec:RecNo, ;
                                               oRec:check_out, oRec:check_in, oRec:morning,oRec:evening } )

      if oDbf:LookUp( cSearch, nil, { || nBooked := RECNO(), hBooked := FW_RecToHash(), .t. } ) == .t.
         lreturn  := .f.
         oSay:VarPut( "BOOKED FROM " + DTOC( hBooked[ "check_in" ] ) + " TO " + ;
                       DTOC( hBooked[ "check_out" ] ) )
         oSay:SetColor( CLR_WHITE, CLR_HRED )
         oSay:Refresh()
      elseif oRec:Modified()
         lreturn  := .t.
         oSay:VarPut( "FREE" )
         oSay:SetColor( CLR_WHITE, CLR_GREEN )
         oSay:Refresh()

      else
         lreturn  := .f.
         oSay:VarPut( "" )
         oSay:SetColor( CLR_BLACK, oSay:oWnd:nClrPane )
         oSay:Refresh()

      endif
   endif

   if oBrw != nil .and. oBrw:nBooked != nBooked
      oBrw:nBooked   := nBooked
      oBrw:Refresh()
   endif
return lreturn


Function BuildDbf()
      field ID, ROOMS_ID, CHECK_IN
      local aStructure
      local i

       if ! File( "rooms.dbf" )
          aStructure = { { "id"   , "C",   4, 0 },;
                      { "name" , "C",  30, 0 },;
                      { "type" , "C",   2, 0 } }
          DBCreate( "rooms", aStructure, "DBFCDX" )
       endif

       if ! File( "reserva.dbf" )
           aStructure = { { "date"       , "D",    8, 0 },;
                          { "rooms_id"   , "C",    4, 0 } ,;
                          { "check_in"   , "D",    8, 0 } ,;
                          { "check_out"  , "D",    8, 0 } ,;
                          { "status"     , "C",    2, 0 } ,;
                          { "type"       , "C",    2, 0 } ,;
                          { "morning"    , "L",    1, 0 } ,;
                          { "evening"    , "L",    1, 0 } ,;
                          { "first"      , "C",   15, 0 } ,;
                          { "last"       , "C",   15, 0 } ,;
                          { "guest"      , "C",   30, 0 } ,;
                          { "numpre"     , "C",   18, 0 } ,;
                          { "invoice"    , "C",   18, 0 } ,;
                          { "subtotal"   , "N",   12, 2 } ,;
                          { "descount"   , "N",   12, 2 } ,;
                          { "datapayed"  , "d",    8, 0 } ,;
                          { "deposit"    , "N",   12, 2 } ,;
                          { "topay"      , "N",   12, 2 } ,;
                          { "total"      , "N",   12, 2 } ,;
                          { "note"       , "C",   80, 0 } ,;
                          { "omaggio"    , "L",    1, 0 } ,;
                          { "listino"    , "C",    2, 0 } ,;
                          { "sector"     , "C",    1, 0 } }

          DBCreate( "reserva", aStructure, "DBFCDX" )
        ENDIF

   USE ROOMS ALIAS ROOMS VIA "DBFCDX" NEW
   INDEX ON ID TAG rooms_id TO rooms

   USE RESERVA ALIAS RESERVA VIA "DBFCDX" NEW
   INDEX ON ROOMS_ID + DToS( CHECK_IN ) TAG room_in TO reserva  for ! deleted()
   INDEX ON ROOMS_ID + TYPE + DToS( CHECK_IN ) TAG  room_intype TO RESERVA for ! deleted()

   if ROOMS->( LastRec() ) == 0
      for i = 1 to 30
         ROOMS->( DbAppend() )
         ROOMS->ID   = StrZero( i, 2 )
         ROOMS->NAME = "Camera " + StrZero( i, 2 )
         ROOMS->TYPE = StrZero( i % 4, 2 )
      next
   endif


   // only for demo

   SELECT RESERVA

  IF  RESERVA->(EOF())

   RESERVA->(DbAppend())
   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "06"
   Replace  RESERVA->check_in   With  ctod("07/06/2023")
   Replace  RESERVA->check_out  With  ctod("09/06/2023")
   Replace  RESERVA->Status     With  "02"
   Replace  RESERVA->guest      With  "Bill Gates"
   Replace  RESERVA->morning    With  .f.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "01"
   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "03"
   Replace  RESERVA->check_in   With  ctod("06/06/2023")
   Replace  RESERVA->check_out  With  ctod("06/06/2023")
   Replace  RESERVA->Status     With  "01"
   Replace  RESERVA->guest      With  "Tom"
   Replace  RESERVA->morning    With  .f.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "01"
   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "03"
   Replace  RESERVA->check_in   With  ctod("06/06/2023")
   Replace  RESERVA->check_out  With  ctod("06/06/2023")
   Replace  RESERVA->Status     With  "02"
   Replace  RESERVA->guest      With  "Jerry"
   Replace  RESERVA->morning    With  .t.
   Replace  RESERVA->evening    With  .f.
   Replace  RESERVA->type       With  "01"


   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "07"
   Replace  RESERVA->check_in   With  ctod("6/06/2023")
   Replace  RESERVA->check_out  With  ctod("8/06/2023")
   Replace  RESERVA->Status     With  "03"
   Replace  RESERVA->guest      With  "Donald duck"
   Replace  RESERVA->morning    With  .t.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "02"
   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "2"
   Replace  RESERVA->check_in   With  ctod("4/06/2023")
   Replace  RESERVA->check_out  With  ctod("6/06/2023")
   Replace  RESERVA->Status     With  "04"
   Replace  RESERVA->guest      With  "Mc Otto"
   Replace  RESERVA->morning    With  .t.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "03"
   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "8"
   Replace  RESERVA->check_in   With  ctod("9/06/2023")
   Replace  RESERVA->check_out  With  ctod("11/06/2023")
   Replace  RESERVA->Status     With  "02"
   Replace  RESERVA->guest      With  "NagesWarao Gunupudi"
   Replace  RESERVA->morning    With  .t.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "04"

   RESERVA->(DbAppend())

   Replace  RESERVA->date       With  date()
   Replace  RESERVA->roomS_id   With  "10"
   Replace  RESERVA->check_in   With  ctod("4/06/2023")
   Replace  RESERVA->check_out  With  ctod("7/06/2023")
   Replace  RESERVA->Status     With  "04"
   Replace  RESERVA->guest      With  "Antonio Linares"
   Replace  RESERVA->morning    With  .t.
   Replace  RESERVA->evening    With  .t.
   Replace  RESERVA->type       With  "02"
   RESERVA->(DbCommit())

ENDIF


   CLOSE DATA

Return nil

//------------------------------------------------------------------------//








//-------------------------------------------------------------------------------------------------------------------------------------------//
// CLASSES
//----------------------------------------------------------------------------//

CLASS TXData from TDataBase
   DATA cDbfPath INIT cFilePath( ExeName() )
ENDCLASS

//----------------------------------------------------------------------------//

CLASS TReserva from TXData

   METHOD New()
   METHOD Record( cFieldList, lNew )

ENDCLASS

METHOD New( lShared ) CLASS TReserva

   Default lShared := .t.
   ::Super:Open(,::cDbfPath +"Reserva" ,"DBFCDX", lShared)
   if ::Used()
      ::setOrder(1)
      ::GoTop()
   endif

   ::bEdit  := { |oRec| Edit_Reservation( oRec ) }

return Self

METHOD Record( cFieldList, lNew ) CLASS TReserva

   local oRec  := ::Super:Record( cFieldList, lNew )

   if oRec:RecNo == 0 // lNew
      WITH OBJECT oRec
         :Rooms_id   := "01"
         :Type       := "01"
         :Check_in   := Date()
         :Check_Out  := Date()
         :morning    := .t.       //default
         :evening    := .t.       //default
      END
   endif

return oRec

//----------------------------------------------------------------------------//
















 



I have problem with this function

I add only the new paramters morning and evening


Code: Select all  Expand view  RUN
function Isfree( oRec, oSay )
   local lNew     := ( oRec:RecNo == 0 )
   local oDbf     := oRec:uSource
   local oBrw     := oRec:oBrw
   local lreturn := .t.
   local cSearch, nBooked := 0, hBooked

   cSearch  := "ROOMS_ID == ? .AND. ALLTRIM(TYPE) == ? .AND. RECNO() != ? .AND. " + ;
               "CHECK_IN <= ? .AND. CHECK_OUT >= ?  .AND. MORNING !=? .AND. EVENING !=?"

   if Empty( oRec:rooms_id ) .or. Empty( oRec:Type ) .or. Empty( oRec:check_in ) .or. ;
      Empty( oRec:check_out ) .or. oRec:check_in > oRec:check_out  .or.;
      Empty(oRec:morning) .or. Empty(oRec:evening)

      lreturn     := .f.
      oSay:VarPut( "INVALID DATA" )
      oSay:SetColor( CLR_WHITE, CLR_RED )
      oSay:Refresh()

   else
      cSearch  := oDbf:ApplyParams( cSearch, { oRec:rooms_id, oRec:type, oRec:RecNo, ;
                                               oRec:check_out, oRec:check_in, oRec:morning,oRec:evening } )

      if oDbf:LookUp( cSearch, nil, { || nBooked := RECNO(), hBooked := FW_RecToHash(), .t. } ) == .t.
         lreturn  := .f.
         oSay:VarPut( "BOOKED FROM " + DTOC( hBooked[ "check_in" ] ) + " TO " + ;
                       DTOC( hBooked[ "check_out" ] ) )
         oSay:SetColor( CLR_WHITE, CLR_HRED )
         oSay:Refresh()
      elseif oRec:Modified()
         lreturn  := .t.
         oSay:VarPut( "FREE" )
         oSay:SetColor( CLR_WHITE, CLR_GREEN )
         oSay:Refresh()

      else
         lreturn  := .f.
         oSay:VarPut( "" )
         oSay:SetColor( CLR_BLACK, oSay:oWnd:nClrPane )
         oSay:Refresh()

      endif
   endif

   if oBrw != nil .and. oBrw:nBooked != nBooked
      oBrw:nBooked   := nBooked
      oBrw:Refresh()
   endif
return lreturn


When It make the search

when the function searches if a room number/type is free with the two new fields (morning and afternoon) it returns an incorrect value
unpacking the whole procedure
is there anyone good soul who could help me solve this problem please ?
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7066
Joined: Thu Oct 18, 2012 7:17 pm

Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: Google [Bot] and 92 guests

cron