Android Question hi guys, i need very smart brains to find a solution for my problem...

ilan

Expert
Licensed User
Longtime User
hi

i need some help

i cannot find a good way without lots of loops to do my code

i have a table view in my table view i have maybe 200 entries (work shifts)

the first column is a DATE
the second is a TIME (shift entry)
the third entries is a time (shift exit)

1. first thing to search is: i would like to know if there are same DATES in my table for a special month

2. all those same DATES entries should be checked if the time has a conflict.

example:

want to search all entries for september:

if found 8 entries in my tableview:

10/09/2013
11/09/2013
11/09/2013
11/09/2013
15/09/2013
18/09/2013
18/09/2013
21/09/2013

so the dates 11/09/2013 is 3 times in my table, date 18/09/2013 two times all other only one

now i want to check for all 3 entries of 11/09/2013 if there is a conflict between the times

example:

date time in time out
1. 11/09/2013, 08:00, 11:00
2. 11/09/2013, 12:00, 14:00
3. 11/09/2013, 16:00, 21:00

in this example there is no conflict between the time

another example:

1. 18/09/2013, 08:00, 16:00
2. 18/09/2013, 10:00, 17:00

here is a conflict because its cannot be that i worked between 8:00 - 16:00 and also between 10:00 to 17:00 in the same day

if the second entry would be:

1. 18/09/2013, 08:00, 16:00
2. 18/09/2013, 16:30, 18:00

then there would be conflict because i worked a shift from 8:00 to 16:00 and had 30min break and work another shift from 16:30 to 18:00

how could i check this parameters?
i know its not easy, would be great if someone could give me a solution dont need a code only solution how to check it

thank you very much
 
Sort by date Sort by votes

barx

Well-Known Member
Licensed User
Longtime User
Is this info stored in a database?

If so the first step would be to get just the rows that have more than 1 entry. so you SELECT statement would be something like

B4X: 
SELECT * FROM table GROUP BY Date HAVING COUNT(Date) > 1

For step 2 (detecting conflict times)

Without getting too complicated, do for each row in the above statement do another query on the database selecting rows with just that date and ordered by start time. Then in your code do a loop checking if the start time of the next row is < the previous row finish time. If so then a conflict.

Non of this is tested, it's just an idea to get you started
 
Upvote 0

mc73

Well-Known Member
Licensed User
Longtime User
Another way would be to clone the original shifts' table, then join and perform the desired comparisons.
Example:
B4X: 
drop table  if exists tempshifts;
create table tempshifts as (select * from shifts);
select * from shifts join tempshifts on tempshifts.shiftdate=shifts.shiftdate where (shifts.shiftentry>tempshifts.shiftentry and shifts.shiftentry<tempshifts.shiftexit) or (shifts.shiftexit>tempshifts.shiftentry and shifts.shiftexit<tempshifts.shiftexit)
 
Upvote 0

ilan

Expert
Licensed User
Longtime User
i solve the problem in a nother way

when i add a new item or edit one i do the checking, in this way i am saving a few loops

B4X: 
    'check if same date exist to do new sorting
   
    Dim starttimechk As String
    Dim endtimechk As String
    Dim starttimechk2 As String
    Dim endtimechk2 As String

    nvlb19.Text = "0"
   
    For i = 0 To Table1.Size - 1
   
      Table1.SetValue(19,i,"0")
     
          If Table1.GetValue(4,i) = lb4.Text AND Table1.GetValue(18,i) > selecteddateday Then
              tabsort = True
              starttimechk = Table1.GetValue(1,i)
              starttimechk = (starttimechk.SubString2(0,2) * 60) + starttimechk.SubString2(3,5)
             
              starttimechk2 = Table1.GetValue(1,selecteddateday)
              starttimechk2 = (starttimechk2.SubString2(0,2) * 60) + starttimechk2.SubString2(3,5)
             
              endtimechk =  Table1.GetValue(2,selecteddateday)
              endtimechk = (endtimechk.SubString2(0,2) * 60) + endtimechk.SubString2(3,5)
             
              endtimechk2 =  Table1.GetValue(2,i)
              endtimechk2 = (endtimechk2.SubString2(0,2) * 60) + endtimechk2.SubString2(3,5)

              If endtimechk <> "--:--" AND endtimechk2 <> "--:--" Then
                  If endtimechk < starttimechk2 Then
'                    If starttimechk  <> endtimechk Then
                      Table1.SetValue(19,i,"1")
                      nvlb19.Text = "1"
'                      End If
                  Else
                      If endtimechk  > starttimechk Then
                        Table1.SetValue(19,i,"1")
                        nvlb19.Text = "1"
                      End If
                  End If
            End If     
             
            Else If  Table1.GetValue(4,i) = lb4.Text AND Table1.GetValue(18,i) < selecteddateday Then 
           
                tabsort = True
                  starttimechk = Table1.GetValue(1,selecteddateday)
                  starttimechk = (starttimechk.SubString2(0,2) * 60) + starttimechk.SubString2(3,5)
                 
                  starttimechk2 = Table1.GetValue(1,i)
                  starttimechk2 = (starttimechk2.SubString2(0,2) * 60) + starttimechk2.SubString2(3,5)
                 
                  endtimechk =  Table1.GetValue(2,i)
                  endtimechk = (endtimechk.SubString2(0,2) * 60) + endtimechk.SubString2(3,5)
                 
                  endtimechk2 =  Table1.GetValue(2,selecteddateday)
                  endtimechk2 = (endtimechk2.SubString2(0,2) * 60) + endtimechk2.SubString2(3,5)

                  If endtimechk2 <> "--:--" AND endtimechk <> "--:--" Then
                      If endtimechk < starttimechk2  Then
'                        If  endtimechk <> starttimechk Then
                          Table1.SetValue(19,i,"1")
                          nvlb19.Text = "1"
'                        End If
                      Else
                          If endtimechk  > starttimechk Then
                          Table1.SetValue(19,i,"1")
                          nvlb19.Text = "1"
                          End If
                      End If
                  End If
          End If
    Next
   
    'end checking
 
Upvote 0
You must log in or register to reply here.

Similar Threads

D
  • Question
Android Question Support ACR1252 smart card reader
Replies
0
Views
2K
davemorris
D
E
  • Question
Android Question Any Solution for Epson Smart Glass BT-300
Replies
0
Views
899
edwinlau
E
  • Question
Android Question Load text file with SmartString Literal
Replies
0
Views
2K
hibrid0
  • Article
Share My Creation SmartSchems for VW
Replies
0
Views
2K
Rattigan_Roger
  • Article
Share My Creation Brain - Scan - Omatic
Replies
10
Views
3K
Cableguy
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…