looping through a set of data

asembereng

hi i have a data set of the type below:

PrKey BioID DateTime CheckType CheckOut
1 1 15/12/08 09:00 I
2 1 15/12/08 17:00 O
3 2 15/12/08 08:00 I
4 2 15/12/08 16:00 O
5 1 12/12/08 21:45 I
6 1 13/12/08 02:19 I
7 1 13/12/08 02:19 I
8 1 13/12/08 02:25 I
9 1 13/12/08 02:31 I
10 1 13/12/08 03:12 I
11 1 13/12/08 03:14 I
12 1 13/12/08 06:15 I
13 1 13/12/08 07:05 I
14 1 13/12/08 08:05 I
15 1 13/12/08 08:06 I
16 1 13/12/08 15:40 I
17 1 13/12/08 15:55 I
18 1 13/12/08 16:11 I
19 1 13/12/08 16:15 I
20 1 13/12/08 16:15 I
21 1 13/12/08 16:16 I

and i want to loop through and insert in a table below

userid check in checkout

1 15/12/08 09:00 15/12/08 17:00
2 15/12/08 08:00 15/12/08 16:00
3 15/12/08 08:00



How can i achieve this? Check in corresponds to the value of I, Check Out corresponds to O.
Thank you.

garak

1. Loop over the first DataSet (is this a file or datas in a table?)
2. In the Loop do following: if not Data found in "userid check in checkout" table for the UserID and Date -> create it (its the checkin), if found -> update it (its the check out)
3. Don't forgett the following situatuion: user doesn't check in or check out so you have not the ful Entry in the "userid check in checkout" table.

Regards

asembereng

The data set is a table i am getting from MSSQL server 2005. Below is the code i am using to get it from the mssql server
can you help me look into it and see where i am doing it wrongly?

IF ISCLEAR(ADOConnection) THEN BEGIN
      IF NOT CREATE(ADOConnection) THEN BEGIN
      ERROR('Cannot create ADO Connection automation variable.');
   END;
END;

IF ISCLEAR(ADORecSet) THEN BEGIN
      IF NOT CREATE(ADORecSet) THEN BEGIN
         ERROR('Cannot create ADO Recordset automation variable.');
   END;
END;

IF ISCLEAR(ADOStream) THEN BEGIN
      IF NOT CREATE(ADOStream) THEN BEGIN
         ERROR('Cannot create ADO Stream automation variable.');
   END;
END;



//don't forget to modify this connectionstring to reflect YOUR setup
ADOConnection.ConnectionString('Provider=SQLOLEDB;Data Source=MSSQLSERVER;'
     + 'Initial Catalog=msaccess2mssql;User ID=sa;Password=*********;');
ADOConnection.Open;
RecordsAffected := '';
RSOption := 0; 

//handling TYPICAL PROBLEM 1: spaces in result set names
SQLString := 'select userid, checktime,checktype from checkinout group by userid,checktime,checktype order by userid,checktime asc';

ADORecSet := ADOConnection.Execute(SQLString,RecordsAffected,RSOption);
ADORecSet.MoveFirst;
REPEAT
   IF bio.FIND('+') THEN BEGIN key := bio.PrKey;    END;
   //handling TYPICAL PROBLEM 2: null values
   //although an ADOField variable would be nicer, but I could not instantiate that,
   //maybe something is not installed on my PC
   //so I think we can just stick to this uglier, but safer method

   IF ADORecSet.Fields.Item('CHECKTYPE').Value.ISTEXT THEN BEGIN
      ADOStream.Open;
      ADOStream.WriteText(ADORecSet.Fields.Item('CHECKTYPE').Value);
      ADOStream.Position:= 0;
      FieldValue:= ADOStream.ReadText;
      ADOStream.Close;
      checktype := FieldValue;
   END;

   IF ADORecSet.Fields.Item('USERID').Value.ISINTEGER THEN BEGIN
      //Handling TYPICAL PROBLEM 3: conventing binary ADO data to Navision text data
      ADOStream.Open;
      ADOStream.WriteText(ADORecSet.Fields.Item('USERID').Value);
      ADOStream.Position:= 0;
      FieldValue:= ADOStream.ReadText;
      ADOStream.Close;
      IF(EVALUATE(bioID,FieldValue)) THEN BEGIN
      bio.BioID := bioID;
      END;
   END;

   IF ADORecSet.Fields.Item('CHECKTIME').Value.ISDATE THEN BEGIN
      ADOStream.Open;
      ADOStream.WriteText(ADORecSet.Fields.Item('CHECKTIME').Value);
      ADOStream.Position:= 0;
      FieldValue:= ADOStream.ReadText;
      ADOStream.Close;
      IF(EVALUATE(datet,FieldValue)) THEN BEGIN
      checkt := datet;
      END;
   END;

   ADORecSet.MoveNext;
   IF (checktype='I') THEN BEGIN
   bio.DateTime := checkt;
   bio.CheckOut := 0DT;
   END ELSE IF (checktype='O') THEN BEGIN
   bio.CheckOut := checkt;
   bio.DateTime := 0DT;
   END;
   bio.PrKey := key + 1;
   bio.CheckType := checktype;
   bio.INSERT;
UNTIL ADORecSet.EOF;
ADORecSet.Close;
ADOConnection.Close;

//and then you can use normal string manipulation
// functions like EVALUATE, DT2DATE, COPYSTR, CONVERTSTR, DELCHAR etc. to handle the data

garak

With your solution you store every rec from "checkinOut" in "Bio".If i understand it correct, you want to store in in "BIO" like
"1 15/12/08 09:00 15/12/08 17:00". So you need to find the BIO Record for the BioID and " checkt"

is the table "checkinout" in the same database or is this a other database on the same server / different server.
Why i ask this? you can create a table in NAV (with same name and same fields) and set the property to LinkedObject (if you are under sql and not native).
if it is a SQL table or a SQL view (this u must before create). Then you doesn't need ADO and u handle all wit C/AL ;-)

zulq

Here's the code that I wrote and so far it looks it's doing what we want, just needs more testing:

Steps := bio.NEXT(1);
Steps := bio2.NEXT();
REPEAT
IF times.FIND('+') THEN BEGIN
key := times."Entry No.";
END;
times."Entry No." := key + 1;
times."Time In" := bio.CheckIn;
bio2.NEXT();
times."Time Out" := bio2.CheckOut;
IF (bio2.CheckOut = 0DT) OR (bio.CheckIn = 0DT)
THEN BEGIN
times.Pending := TRUE;
times."Total Time" := 0;
END ELSE BEGIN
times.Pending := FALSE;
times."Total Time" := times."Time Out" - times."Time In";
END;
IF NOT ((times."Time In" = 0DT) AND (times."Time Out" =0DT)) THEN BEGIN
emp.SETRANGE(BID,bio.BioID);
IF emp.FIND('-') THEN BEGIN
times."Employee No." := emp."No.";
times.Name := emp."First Name" + ' ' + emp."Middle Name" + ' '+ emp."Last Name";
END ELSE BEGIN
times."Employee No." := '';
times.Name := '';
END;
IF times."Time In" = 0DT THEN BEGIN
dateconv := FORMAT(times."Time Out",0,'<Day,2>/<Month,2>/<Year>');
END ELSE IF times."Time Out" = 0DT THEN BEGIN
dateconv := FORMAT(times."Time In",0,'<Day,2>/<Month,2>/<Year>');
END;
IF(EVALUATE(dateback,dateconv)) THEN BEGIN
times.Date := dateback;
END;
times.INSERT;
END;
UNTIL bio.NEXT = 0;

Thanks.

zulq

Hi,
I want an additional feature to check if the duration is more than one day then the previous time in should end at 23:59:59
and the remaining time be added to next day duration.

Any ideas how to achieve this.

Thanks.

zulq

Hi,
I want an additional feature to check if the duration is more than one day then the previous time in should end at 23:59:59
and the remaining time be added to next day duration.

Any ideas how to achieve this.

Thanks.

Administrator

[Topic moved from NAV Tips & Tricks to NAV/Navision forum]