mibuso.com
Home› NAV/Navision Classic Client

Getting Date difference problem. Need Help.

webweb Member Posts: 102
edited 2008-03-28 in NAV/Navision Classic Client
Hi
I am trying to calculate the difference between the resumption Date and the rundate to calculate the no. of months. This no. of months should be multiply by the no. of leave an employee is entiled. Each employee is has a category were he/she belongs to and each of these categories has their corresponding leave entitle. For example category 2 employees has a corresponding leave entitlement of 20 days, 3-> 20days etc. These has a table called Categories where you have categories and entitlements.

When an employee has taken leave say have of the entitlement and then resume, he has a remaining leave entitlement. He spend some months and whats to know his entitlement, we run the below report to add the remaining leave to the leave entitlement for the months spent after resumption. The way the next leave is calculate is we divide the entitlement base on the category by 12 and the multiple this by the number of months spent after resumption to work. This answer is then added to the remaining leave and this gives the person's leave entitlement. The absence table is used to subtract the leaves taken
Can some check this report and help me make this work leave i mhave explained.

I am getting problems on the difference in months. I use the resumption date to calculate the no of months spent after resumption which is subtracted from the run date to get the months spent after resumption.
Can anyone help please. 
OBJECT Report 50026 Leave Computation
{
  OBJECT-PROPERTIES
  {
    Date=27/03/08;
    Time=16:34:01;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table5200;
        OnAfterGetRecord=BEGIN
                           Cats.GET(Catagory);
                           EmpName :="First Name" + Space + "Middle Name" + Space + "Last Name";
                             // Testing Below
                           //Cats.GET(Employee.Catagory);
                           //"Employee Absence".SETRANGE("Employee No.",Employee."No.");
                           //Cats.SETRANGE(Catagories, Employee.Catagory);
                           RemLeave := Cats."Leave entitle";
                           AnnLeave := 0;

                           {Emp.RESET;
                           Emp.SETCURRENTKEY("No.","Resumption Date");
                           Emp.SETRANGE("No.","No.");
                           Emp.SETFILTER("Resumption Date",'<>%1',0D);}

                           //IF Emp.FIND('+') THEN
                           Resumedate :="Resumption Date";
                           //LeaveRunDate := VarDate;
                           //Nday := LeaveRunDate - Resumedate;


                           {IF "Resumption Date" <> 0D THEN BEGIN
                                nOfDays := VarDate - "Resumption Date";
                           END;}

                           IF NOT EVALUATE(VarDate,VarDateText) THEN;
                           {IF (VarDate <> 0D) AND ("Resumption Date" <> 0D) THEN
                           NoMonth := 12*((VarDate - "Resumption Date")/365.25);
                           NofMonths := ROUND(NoMonth,1,'<');}



                           VarDate := TODAY;
                           //ExpectedResultDate := CALCDATE('-Resumedate', VarDate);
                           MESSAGE('%1', ExpectedResultDate);

                           IF "Resumption Date" > VarDate THEN BEGIN
                             //MESSAGE(textDateError);
                             //EXIT('00-00-00');
                           END;

                           g1 := DATE2DMY("Resumption Date", 3);
                           m1 := DATE2DMY("Resumption Date", 2);
                           d1 := DATE2DMY("Resumption Date", 1);
                           //m1days := LastDayOfMonth(g1, m1);    // Simple function to find last day of month using Date table

                           g2 := DATE2DMY(VarDate, 3);
                           m2 := DATE2DMY(VarDate, 2);
                           d2 := DATE2DMY(VarDate, 1);

                           IF d2 < d1 THEN BEGIN
                             d2 += m1days;
                             m2 -= 1;
                           END;
                           d := d2 - d1;

                           IF m2 < m1 THEN BEGIN
                             m2 += 12;
                             g2 -= 1;
                           END;
                           m := m2 - m1;

                           g := g2 - g1;

                           RemLeave := Cats."Leave entitle" + ROUND(m *(Cats."Leave entitle")/12,1,'>');

                           //EXIT(CONVERTSTR(FORMAT(g, 2) + '-' + FORMAT(m, 2) + '-' + FORMAT(d, 2), ' ', '0'));









                           //IF "Employee Absence".FIND('-')
                           //THEN BEGIN
                           //AnnLeave := "Employee Absence".Quantity;
                           //IF "Employee Absence".NEXT <> 0
                           //THEN REPEAT
                           //AnnLeave := AnnLeave + "Employee Absence".Quantity;
                           //UNTIL "Employee Absence".NEXT = 0;
                           //RemLeave := Cats."Leave entitle" - "Employee Absence".Quantity;
                           //END
                           //ELSE BEGIN
                           //RemLeave := Cats."Leave entitle";
                           //AnnLeave := 0;
                           //END;
                         END;

        ReqFilterFields=No.,Date Filter;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=17250;
            SectionHeight=2115;
          }
          CONTROLS
          {
            { 1000000011;Label  ;1500 ;1269 ;9150 ;423  ;FontSize=12;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=STAFF LEAVE COMPUTATION REPORT }
            { 1000000016;TextBox;12150;0    ;3750 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000018;TextBox;13050;846  ;2850 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
            { 1000000015;TextBox;0    ;0    ;1800 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000017;TextBox;14400;423  ;1500 ;423  ;SourceExpr=CurrReport.PAGENO }
            { 1000000019;Label  ;12900;423  ;1500 ;423  ;HorzAlign=Right;
                                                         CaptionML=ENU=Page }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=17250;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000014;Label  ;0    ;0    ;2250 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Employee No.: }
            { 1000000000;TextBox;2250 ;0    ;1350 ;423  ;FontSize=8;
                                                         SourceExpr=Employee."No." }
            { 1000000005;Label  ;3900 ;0    ;1200 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Name: }
            { 1000000002;TextBox;5250 ;0    ;6900 ;423  ;FontSize=8;
                                                         SourceExpr=EmpName }
            { 1000000006;Label  ;0    ;423  ;1650 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Category: }
            { 1000000001;TextBox;1800 ;423  ;900  ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         SourceExpr=Employee.Catagory }
            { 1000000003;Label  ;2850 ;423  ;2550 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Leave Entitle To }
            { 1000000007;TextBox;5550 ;423  ;1500 ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         SourceExpr=RemLeave }
            { 1000000030;TextBox;7650 ;423  ;1500 ;423  ;SourceExpr=Resumedate }
            { 1000000032;TextBox;9750 ;423  ;1500 ;423  ;SourceExpr=d }
            { 1000000033;TextBox;11550;423  ;1500 ;423  ;SourceExpr=m }
            { 1000000034;TextBox;13350;423  ;1500 ;423  ;SourceExpr=Nday }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table5207;
        OnAfterGetRecord=BEGIN
                           //Cats.GET(Employee.Catagory);

                           empAbse.SETRANGE("Employee No.",Employee."No.");
                           empAbse.SETRANGE(Deductible,TRUE);
                           Cats.SETRANGE(Catagories, Employee.Catagory);

                           //IF Emp.FIND('+') THEN
                           //Resumedate := Emp."Resumption Date";


                           IF empAbse.FIND('-')
                           THEN BEGIN
                           AnnLeave := empAbse.Quantity;
                           IF empAbse.NEXT <> 0
                           THEN REPEAT
                           AnnLeave := AnnLeave + empAbse.Quantity;
                           UNTIL empAbse.NEXT = 0;
                           RemLeave := Cats."Leave entitle" - AnnLeave;
                           END
                           ELSE BEGIN
                           RemLeave := Cats."Leave entitle";
                           AnnLeave := 0;
                           END;
                         END;

        DataItemLink=Employee No.=FIELD(No.);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=17250;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000020;Label  ;0    ;0    ;1350 ;423  ;HorzAlign=Left;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=From Date: }
            { 1000000022;Label  ;1350 ;0    ;1050 ;423  ;HorzAlign=Left;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=To Date: }
            { 1000000024;Label  ;2850 ;0    ;2100 ;423  ;HorzAlign=Left;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Cause of Absence: }
            { 1000000026;Label  ;5250 ;0    ;4200 ;423  ;HorzAlign=Left;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Description: }
            { 1000000028;Label  ;13350;0    ;1800 ;423  ;HorzAlign=Left;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=No. of days: }
            { 1000000031;Label  ;15150;0    ;1500 ;423  ;HorzAlign=Left;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Deductible }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=17250;
            SectionHeight=423;
          }
          CONTROLS
          {
            { 1000000004;TextBox;0    ;0    ;1200 ;423  ;HorzAlign=Left;
                                                         SourceExpr="From Date" }
            { 1000000021;TextBox;1200 ;0    ;1200 ;423  ;HorzAlign=Left;
                                                         SourceExpr="To Date" }
            { 1000000023;TextBox;2850 ;0    ;2700 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Cause of Absence Code" }
            { 1000000025;TextBox;6150 ;0    ;3450 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description }
            { 1000000027;TextBox;13350;0    ;1800 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Quantity }
            { 1000000029;TextBox;15150;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Deductible }
          }
           }
        { PROPERTIES
          {
            SectionType=Footer;
            SectionWidth=17250;
            SectionHeight=1269;
          }
          CONTROLS
          {
            { 1000000009;Label  ;0    ;0    ;3150 ;423  ;FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Annual Leave Taken: }
            { 1000000010;TextBox;3150 ;0    ;1050 ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         SourceExpr=AnnLeave }
            { 1000000012;Label  ;4200 ;0    ;3900 ;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Annual Leave Remaining: }
            { 1000000013;TextBox;8100 ;0    ;1050 ;423  ;HorzAlign=Left;
                                                         FontSize=8;
                                                         SourceExpr=RemLeave }
            { 1000000008;TextBox;0    ;423  ;17250;423  ;HorzAlign=Left;
                                                         FontSize=9;
                                                         FontBold=Yes;
                                                         SourceExpr=Line2 }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
      { 1000000000;TextBox;3850 ;990  ;1700 ;440  ;SourceExpr=VarDate }
    }
  }
  CODE
  {
    VAR
      Space@1000000001 : TextConst 'ENU=" "';
      Cats@1000000000 : Record 50025;
      RemLeave@1000000002 : Integer;
      EmpName@1000000003 : Text[180];
      AnnLeave@1000000004 : Integer;
      Line1@1000000005 : TextConst 'ENU=......................................................................................................................................................';
      Line2@1000000006 : TextConst 'ENU=------------------------------------------------------------------------------------------------------------------------------------------------------';
      DedOption@1000000007 : Boolean;
      empAbse@1000000008 : Record 5207;
      Resumedate@1000000009 : Date;
      Emp@1000000010 : Record 5200;
      nOfDays@1000000011 : Decimal;
      VarDateText@1000000012 : Text[50];
      VarDate@1000000013 : Date;
      NofMonths@1000000014 : Decimal;
      NoMonth@1000000015 : Decimal;
      ExpectedResultDate@1000000016 : Date;
      g1@1000000017 : Integer;
      m1@1000000018 : Integer;
      d1@1000000019 : Integer;
      g2@1000000020 : Integer;
      m2@1000000021 : Integer;
      d2@1000000022 : Integer;
      d@1000000023 : Integer;
      m@1000000024 : Integer;
      g@1000000025 : Integer;
      m1days@1000000026 : Integer;
      Nday@1000000027 : Integer;
      LeaveRunDate@1000000028 : Date;

    BEGIN
    END.
  }
}

Comments

  • webweb Member Posts: 102
    Hi,
    Can you please check this part of the code for me. Its where i am problems of date difference. 
    RemLeave := Cats."Leave entitle";
AnnLeave := 0; 
Resumedate :="Resumption Date"; 
VarDate := TODAY;
IF "Resumption Date" > VarDate THEN BEGIN

END;

g1 := DATE2DMY("Resumption Date", 3);
m1 := DATE2DMY("Resumption Date", 2);
d1 := DATE2DMY("Resumption Date", 1);
//m1days := LastDayOfMonth(g1, m1);    // Simple function to find last day of month using Date table
g2 := DATE2DMY(VarDate, 3);
m2 := DATE2DMY(VarDate, 2);
d2 := DATE2DMY(VarDate, 1);

IF d2 < d1 THEN BEGIN
d2 += m1days;
m2 -= 1;
END;
d := d2 - d1;
IF m2 < m1 THEN BEGIN
m2 += 12;
g2 -= 1;
END;
m := m2 - m1;
g := g2 - g1;
RemLeave := Cats."Leave entitle" + ROUND(m *(Cats."Leave entitle")/12,1,'>');
Sign In or Register to comment.