OBJECT Report 50001 Import Item Journal from Excel
{
  OBJECT-PROPERTIES
  {
    Date=18/10/14;
    Time=11:55:52;
    Modified=Yes;
    Version List=NAVW17.10;
  }
  PROPERTIES
  {
    CaptionML=[ENU=Import Item Journal from Excel;
               ENG=Import Item Journal from Excel];
    ProcessingOnly=Yes;
    OnPreReport=VAR
                  BusUnit@1000 : Record 220;
                BEGIN
                  TempExcelBuf.LOCKTABLE;
                  TempExcelBuf.OpenBook(ServerFileName,SheetName);
                  TempExcelBuf.ReadSheet;

                  AnalyzeData;
                END;

    OnPostReport=BEGIN
                   TempExcelBuf.DELETEALL;
                 END;

  }
  DATASET
  {
  }
  REQUESTPAGE
  {
    PROPERTIES
    {
      SaveValues=Yes;
      OnQueryClosePage=VAR
                         FileMgt@1001 : Codeunit 419;
                       BEGIN
                         IF CloseAction = ACTION::OK THEN BEGIN

                           //Has the User Selected a Template Name
                           IF JnlTemplateName = '' THEN
                             ERROR(Text001,JnlTemplate.TABLECAPTION,JnlTemplate.FIELDCAPTION(Name));

                           //Has the User Selected a Batch Name
                           IF JnlBatchName = '' THEN
                             ERROR(Text001,JnlBatch.TABLECAPTION,JnlBatch.FIELDCAPTION(Name));

                           //Has the User Entered a Document No
                           IF DocNoActive AND (DocNo = '') THEN
                             ERROR(Text002,JnlLine.FIELDCAPTION("Document No."),JnlBatch.TABLECAPTION,JnlBatch.Name);

                           ServerFileName := FileMgt.UploadFile(Text006,ExcelFileExtensionTok);
                           IF ServerFileName = '' THEN
                             EXIT(FALSE);

                           SheetName := TempExcelBuf.SelectSheetsName(ServerFileName);
                           IF SheetName = '' THEN
                             EXIT(FALSE);
                         END;
                       END;

    }
    CONTROLS
    {
      { 1900000001;0;Container;
                  ContainerType=ContentArea }

      { 1900000002;1;Group  ;
                  CaptionML=[ENU=Options;
                             ENG=Options] }

      { 1000000000;2;Field  ;
                  CaptionML=[ENU=Item Journal Template;
                             ENG=Item Journal Template];
                  SourceExpr=JnlTemplateName;
                  TableRelation="Item Journal Template".Name WHERE (Type=CONST(Item),
                                                                    Recurring=CONST(No));
                  OnValidate=BEGIN
                               IF JnlTemplateName <> '' THEN BEGIN
                                 JnlTemplate.GET(JnlTemplateName);
                                 JnlTemplate.TESTFIELD(Recurring,FALSE);
                                 JnlTemplate.TESTFIELD(Type,JnlTemplate.Type::Item);
                                 CLEAR(JnlBatchName);
                                 BatchActive := TRUE;
                                 DocNoActive := FALSE;
                               END ELSE BEGIN
                                 CLEAR(JnlBatchName);
                                 BatchActive := FALSE;
                                 DocNoActive := FALSE;
                               END;
                               RequestOptionsPage.UPDATE;
                             END;
                              }

      { 1000000001;2;Field  ;
                  CaptionML=[ENU=Item Journal Batch;
                             ENG=Item Journal Batch];
                  SourceExpr=JnlBatchName;
                  Editable=BatchActive;
                  OnValidate=BEGIN
                               IF JnlBatch.GET(JnlTemplateName,JnlBatchName) THEN BEGIN
                                 JnlBatchName := JnlBatch.Name;
                                 DocNoActive := JnlBatch."No. Series" = '';
                                 DocNo := '';
                               END ELSE BEGIN
                                 JnlBatchName := '';
                                 DocNoActive := FALSE;
                                 DocNo := '';
                               END;

                               RequestOptionsPage.UPDATE;
                             END;

                  OnAssistEdit=BEGIN
                                 GetJournalBatch;
                                 RequestOptionsPage.UPDATE;
                               END;
                                }

      { 1000000002;2;Field  ;
                  CaptionML=[ENU=Document No.;
                             ENG=Document No.];
                  SourceExpr=DocNo;
                  Editable=DocNoActive }

    }
  }
  LABELS
  {
  }
  CODE
  {
    VAR
      Text001@1005 : TextConst 'ENU=You must specify a %1 %2;ENG=You must specify a %1 %2';
      Text002@1000000011 : TextConst 'ENU=You must specify a %1 for %2 %3;ENG=You must specify a %1 for %2 %3';
      Text006@1006 : TextConst 'ENU=Import Excel File;ENG=Import Excel File';
      Text007@1007 : TextConst 'ENU=Analyzing Data...\\;ENG=Analysing Data...\\';
      JnlTemplate@1000000010 : Record 82;
      JnlBatch@1000000005 : Record 233;
      JnlLine@1000000004 : Record 83;
      Item@1000000013 : Record 27;
      Location@1000000014 : Record 14;
      TempExcelBuf@1027 : Record 370;
      JnlTemplateName@1000000006 : Code[10];
      JnlBatchName@1000000003 : Code[10];
      DocNo@1000000009 : Code[20];
      ServerFileName@1033 : Text;
      SheetName@1000000012 : Text[250];
      BatchActive@1000000007 : Boolean INDATASET;
      DocNoActive@1000000008 : Boolean INDATASET;
      TotalRowNo@1000000000 : Integer;
      RowNo@1000000001 : Integer;
      NextLineNo@1000000002 : Integer;
      Window@1046 : Dialog;
      ExcelFileExtensionTok@1037 : TextConst '@@@={Locked};ENU=.xlsx;ENG=.xlsx';

    LOCAL PROCEDURE AnalyzeData@2();
    VAR
      NoSeriesMgt@1000000000 : Codeunit 396;
      LineQty@1000000001 : Decimal;
    BEGIN
      //Did the user Import an empty Worksheet
      IF TempExcelBuf.ISEMPTY THEN
        EXIT;

      //Move to the last record
      TempExcelBuf.FINDLAST;

      //Assign the Total Rows to a Variable
      TotalRowNo := TempExcelBuf."Row No.";

      //Only imported the Column Captions
      IF TotalRowNo = 1 THEN
        EXIT;

      //Open a window with a progress bar
      Window.OPEN(
        Text007 +
        '@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
      Window.UPDATE(1,0);

      //Get the Template if Values Saved from Last Run
      IF JnlTemplate.Name <> JnlTemplateName THEN
        JnlTemplate.GET(JnlTemplateName);

      //Get the Template Batch if Values Saved from Last Run
      IF (JnlBatch."Journal Template Name" <> JnlTemplateName)OR
        (JnlBatch.Name <> JnlBatchName) THEN
        JnlBatch.GET(JnlTemplateName,JnlBatchName);

      //Get the Last Line No.
      JnlLine.RESET;
      JnlLine.SETRANGE("Journal Template Name",JnlTemplate.Name);
      JnlLine.SETRANGE("Journal Batch Name",JnlBatch.Name);
      IF JnlLine.FINDLAST THEN
        NextLineNo := JnlLine."Line No."
      ELSE
        NextLineNo := 0;

      //Set the Document Number
      IF DocNo = '' THEN BEGIN
        CLEAR(NoSeriesMgt);
        DocNo := NoSeriesMgt.TryGetNextNo(JnlBatch."No. Series",TODAY);
      END;

      //Loop through the Rows
      FOR RowNo := 2 TO TotalRowNo DO BEGIN
        Window.UPDATE(1,ROUND(RowNo / TotalRowNo * 10000,1));

        //See if we have a Quantity to Import
        IF TempExcelBuf.GET(RowNo,6)THEN
          IF EVALUATE(LineQty,TempExcelBuf."Cell Value as Text") THEN BEGIN
            WITH JnlLine DO BEGIN
              INIT;
              "Journal Template Name" := JnlTemplate.Name;
              "Journal Batch Name" := JnlBatch.Name;
              NextLineNo := NextLineNo + 10000;
              JnlLine."Line No." := NextLineNo;
              INSERT(TRUE);
              "Source Code" := JnlTemplate."Source Code";
              "Reason Code" := JnlBatch."Reason Code";
              "Posting No. Series" := JnlBatch."Posting No. Series";
              IF LineQty > 0 THEN
                VALIDATE("Entry Type",JnlLine."Entry Type"::"Positive Adjmt.")
              ELSE
                VALIDATE("Entry Type",JnlLine."Entry Type"::"Negative Adjmt.");

              //Posting Date
              TempExcelBuf.GET(RowNo,1);
              EVALUATE("Posting Date",TempExcelBuf."Cell Value as Text");

              //Document No.
              "Document No." := DocNo;

              //Item No.
              TempExcelBuf.GET(RowNo,2);
              Item.GET(UPPERCASE(TempExcelBuf."Cell Value as Text"));
              Item.TESTFIELD(Blocked,FALSE);
              VALIDATE("Item No.",Item."No.");

              //Description
              Description := Item.Description;

              //Item Variant
              IF TempExcelBuf.GET(RowNo,3)THEN
                VALIDATE("Variant Code",UPPERCASE(TempExcelBuf."Cell Value as Text"));

              //Location Code
              IF TempExcelBuf.GET(RowNo,4)THEN
                VALIDATE("Location Code",UPPERCASE(TempExcelBuf."Cell Value as Text"));

              //Bin Code
              IF TempExcelBuf.GET(RowNo,5)THEN
                VALIDATE("Bin Code",UPPERCASE(TempExcelBuf."Cell Value as Text"));

              //Reason Code
              IF TempExcelBuf.GET(RowNo,7)THEN
                VALIDATE("Reason Code",UPPERCASE(TempExcelBuf."Cell Value as Text"));

              //Validate the Quantity
              VALIDATE(Quantity,ABS(LineQty));

              //Update the Line
              MODIFY(TRUE);
            END;
          END;
      END;
    END;

    LOCAL PROCEDURE GetJournalBatch@1000000000();
    BEGIN
      IF JnlTemplateName <> '' THEN BEGIN
        JnlBatch.RESET;
        JnlBatch.SETRANGE("Journal Template Name",JnlTemplateName);
        IF PAGE.RUNMODAL(PAGE::"Item Journal Batches",JnlBatch) = ACTION::LookupOK THEN BEGIN
          JnlBatchName := JnlBatch.Name;
          DocNoActive := JnlBatch."No. Series" = '';
          DocNo := '';
        END;
      END;
    END;

    BEGIN
    END.
  }
  RDLDATA
  {
  }
}

<!-- WP Super Cache is installed but broken. The constant WPCACHEHOME must be set in the file wp-config.php and point at the WP Super Cache plugin directory. -->