remove first characters in D365 ax 2012 X++ code

X++ CODE TO REMOVE first or starting CHARACTERS.

use the below code to remove the last character of a string in ax .

strDel(noteDescription,1,1)

str strDel(
    str _text,
    int _position,
    int _number)

Example:

using strDel function Note: removes 2 letters from 4th letter onwards

info(strDel("1Description",1,1));

Result : Description

Similarly the number of characters can be increased or descreased.
Below code will remove the last 3 characters from the string.
Str example = 'Example123';
info(strDel(example, strLen(example) -2, 3));

Output : 'Example'

Thanks,
Vikas Mehta.

Failed to execute query because no root data source on the form matches the root datasource on the query.

AX 2012 D365 FORM LOOKUP ERROR

It might be the case that you get the below error.
Failed to execute query because no root data source on the form matches the root datasource on the query.

In order to fix it make sure that the datasource used should be the same as defined .
For example.
        Query query = new Query();
        QueryBuildDataSource qbds = query.addDataSource(tableNum(InventSite));
       

        SysTableLookup sysTableLookup =  sysTableLookup::newParameters(tableNum(VendTable), _formControl);
        sysTableLookup.addLookupfield(fieldNum(InventSite, SiteId));
        sysTableLookup.addLookupfield(fieldNum(InventSite, Name));

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();

In the above site lookup code you will get the same error because VendTable was used in the parameter instead of the inventsite so to fix , it should be defined as below.
  Query query = new Query();
        QueryBuildDataSource qbds = query.addDataSource(tableNum(InventSite));
       

        SysTableLookup sysTableLookup =  sysTableLookup::newParameters(tableNum(InventSite), _formControl);
        sysTableLookup.addLookupfield(fieldNum(InventSite, SiteId));
        sysTableLookup.addLookupfield(fieldNum(InventSite, Name));

        sysTableLookup.parmQuery(query);

        sysTableLookup.performFormLookup();

thanks,
Vikas Mehta.

how to add range the query of the contract class in D365 code.

how to add range the query of the contract class in D365 code.

 private void initQuery(Contractclass _contract)
    {
        query   = _contract.getQuery();
        wmsQBDS = query.dataSourceTable(tableNum(inventtable));

        QueryBuildRange      errorRange = wmsQBDS.addRange(fieldNum(inventtable, Isopen));

        errorRange.value(queryValue(NoYes::No));
        errorRange.status(RangeStatus::Locked);

        QueryBuildDataSource QBDS =

       QBDS.addDataSource(tableNum(NewTable));
        wmsHeaderQBDS.relations(true);
        wmsHeaderQBDS.joinMode(JoinMode::ExistsJoin);
        SysQuery::findOrCreateRange(wmsHeaderQBDS, fieldNum(NewTable, Newfield)).value('newrange');
     
    }
Please note that NewTable needs to have a relation with the InventTable or else you will also need to add a new relation.

Thanks,
Vikas Mehta.

DIFFERENCE BETWEEN PRE AND POST EVENT HANDLERS IN DYNAMICS D365 AX 2012

DIFFERENCE BETWEEN PRE AND POST EVENT HANDLERS IN DYNAMICS D365 AX 2012 

Below are the dfferences.

OnValidatingField: Happen when Validating Field, sync event

OnValidatedField: happen after field is validated, async event

Validatefield(Pre): Happen before Validatefield

Validatefield(post) Happen after Validatefield

Thanks,

Vikas Mehta.

how to write validateField method in Dynamics AX, d365

validateField method in Dynamics AX, d365

 public boolean validateField(FieldId _fieldIdToCheck)
    {
        boolean ret;

        ret = super(_fieldIdToCheck);

        switch(_fieldIdToCheck)
        {
            case fieldNum(InventTable, HSNCodeTable_IN) :
                  if (this.HSNCodeTable_IN)
                {
                    ret = ret && checkFailed("@TaxGST:LabelCheck");
                }
                break;
        }
        return ret;
    }


Below is an event handler of validate field in D365
/// <summary>
///
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
[DataEventHandler(tableStr(InventTable), DataEventType::ValidatedField)]
public static void InventTable_onValidatedField(Common sender, DataEventArgs e)
{     
                ValidateFieldEventArgs event = e as ValidateEventArgs;
                InventTable InventTable= sender as InventTable;
                //do anything
}

Similarly we can have Posthandler for salesline or inventtable as an example.

[PostHandlerFor(tableStr(SalesLine), tableMethodStr(SalesLine, validateField))]
    public static void SalesLine_Post_validateField(XppPrePostArgs args)
    {
        SalesLine salesLine = args.getThis();
        FieldId fieldId = args.getArg("_fieldId");
        boolean ret = args.getReturnValue();

        switch(fieldId)
        {
            case fieldNum(SalesLine, LinePercent):
                if (salesLine.LinePercent > 100)
                {
                    ret = ret && checkFailed("Wrong");
                }
                break;
        }

        args.setReturnValue(ret);
    }

Thanks,
Vikas Mehta.

TRY CATCH CODE IN DYNAMICS AX ,D365 ,AX 2012 CODE

TRY CATCH CODE IN DYNAMICS AX ,D365 ,AX 2012 CODE

System.Exception ex;

   try

   {

       FormletterService.Run()      

   }

   catch(Exception::CLRError)

   {

       ex = ClrInterop::getLastException();

        if (ex != null)

        {

           ex = ex.get_InnerException();

           if (ex != null)

           {

               throw error(ex.ToString());

           }

    }

}

Thanks,

Vikas Mehta.  

HOW TO GET GRID RECORDS IN DYNAMICS AX

HOW TO GET ALL THE GRID RECORDS IN DYNAMICS AX 

If you want to get the records of all the selected records in the grid on a form then write the below code in the clicked method of the form,

we can loop it for multiple records and can do any task required.

for (custInvoiceJourLoc = getFirstSelection(custInvoiceJour_ds);

custInvoiceJourLoc;

custInvoiceJourLoc = custInvoiceJour_ds.getNext())

{

// do some tasks here.

}

Thanks,

Vikas Mehta.

Get worker name from Employee position with SQL Queries in dynamics AX 2012

 Get worker name from Employee position with SQL Queries in dynamics AX 2012

select top 1 name from DIRPARTYTABLE

inner join HcmWorker ON HcmWorker.PERSON = DIRPARTYTABLE.RECID

AND HcmWorker.RECID =

(select top 1 worker from HCMPOSITIONWORKERASSIGNMENT 

INNER JOIN HCMPOSITION ON HCMPOSITIONWORKERASSIGNMENT.POSITION =  HCMPOSITION.RECID

AND HCMPOSITION.POSITIONID = 'POS123'

where GETDATE() between HCMPOSITIONWORKERASSIGNMENT.VALIDFROM AND HCMPOSITIONWORKERASSIGNMENT.VALIDTO);

thanks,

Vikas Mehta.

REINDEX IN DYNAMICS AX D365 SCRIPT TO REINDEX TABLE ON DATABASE

REINDEX IN DYNAMICS AX D365 SCRIPT TO REINDEX TABLE ON DATABASE

DECLARE @Database VARCHAR(255)   

DECLARE @Table VARCHAR(255) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fillfactor INT

SET @fillfactor = 90

DECLARE DatabaseCursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases  
WHERE name IN ('DATABASENAME')   ///YOUR TABLE NAME HERE
ORDER BY 1 

OPEN DatabaseCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
BEGIN 

SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' +
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES
  WHERE table_type = ''BASE TABLE'''  

-- create table cursor 
   EXEC (@cmd) 
   OPEN TableCursor  

FETCH NEXT FROM TableCursor INTO @Table  
   WHILE @@FETCH_STATUS = 0  
   BEGIN  

Print  ('ALTER INDEX ALL ON ' + @Table)
                SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
                EXEC (@cmd)

FETCH NEXT FROM TableCursor INTO @Table  
   END  

CLOSE TableCursor  
   DEALLOCATE TableCursor 

FETCH NEXT FROM DatabaseCursor INTO @Database 
END 
CLOSE DatabaseCursor  
DEALLOCATE DatabaseCursor

RUN REPORT FROM BUTTON CLICK IN AX 2012

RUN REPORT FROM BUTTON CLICK IN AX 2012 

void clicked()

{

    Args                args;

    ReportRun       reportRun;

    CustTrans       custTrans;

    ;

     select firstonly custTrans where custTrans.recid == '1231232';

    args    = new args();

    args.name(reportstr(ReportName)); // Report name to be called

    args.record(custTrans); 

    args.parm(custTrans.transID); // In case there are additional paramters..

    reportRun = classFactory.reportRunClass(args);

    reportRun.init();

    reportRun.run();

}

Thanks,

Vikas Mehta

METHOD TO LINK TO PARENT FORM IN D365

HOW TO CREATE A LINK TO ITEM FORM ON A FIELD.

public void jumpRef()
{
Args args;
MenuFunction menuFunction;

args = new args();
args.record(InventTable::find(this.valueStr()));
menuFunction = new MenuFunction(menuitemDisplayStr(EcoResProductDetailsExtended), MenuItemType::Display);
menuFunction.run(args);
}

THANKS,

Vikas Mehta

CREATE AND POST PRODUCTION ROUTE JOURNAL USING PRODUCTION ORDER

CREATE AND PRODUCTION ROUTE JOURNAL USING PRODUCTION ORDER

static void createRouteCardJournalSample(Args _args)
{
    ProdJournalTable                      prodJournalTable;
    ProdJournalRoute                     prodJournalRoute;
    ProdRoute                               prodRoute;
    ProdId                                     prodId     = 'PO001';
    OprNum                                  oprNum   = 20;
    RouteOprId                              routeOprId;
    ProdJournalCheckPostRoute     prodJournalCheckPostRoute;

    select firstonly prodRoute
                where prodRoute.ProdId == prodId  && prodRoute.OprNum == oprNum;

    prodJournalTable.clear();
    prodJournalTable.initValue();
    prodJournalTable.JournalType            = prodjournaltype::RouteCard;
    prodJournalTable.ProdId                     = prodId;
    prodJournalTable.JournalNameId       = ProdParametersDim::findDefault().RouteJournalNameId;
    prodJournalTable.Description             =                                                  ProdJournalName::find(prodJournalTable.JournalNameId).Description;
    prodJournalTable.VoucherSeqRecId   = ProdJournalName::find(prodJournalTable.JournalNameId).VoucherSeqRecId;
    prodJournalTable.VoucherDraw        = journalVoucherDraw::Post;
    prodJournalTable.NumOfLines         = 1;
    prodJournalTable.insert();

    //Route card entry
    prodJournalRoute.clear();
    prodJournalRoute.JournalId               = prodJournalTable.journalId;
    prodJournalRoute.ProdId                   = prodId;
    prodJournalRoute.initValue();
    prodJournalRoute.OprNum               = oprNum;
    prodJournalRoute.OprId                    = prodRoute.OprId;
    prodJournalRoute.JobType                = RouteJobType::Process;
    prodJournalRoute.CategoryHoursId  = prodRoute.SetUpCategoryId;
    prodJournalRoute.CategoryQtyId      = prodRoute.ProcessCategoryId;
    prodJournalRoute.DefaultDimension = prodRoute.DefaultDimension;
    prodJournalRoute.WrkCtrId               = prodRoute.WrkCtrIdCost;
    prodJournalRoute.QtyGood               = 2;
    prodJournalRoute.Hours                    = 1;
    prodJournalRoute.insert();
    info(prodJournalTable.JournalId);
 
    ProdJournalCheckPostRoute = ProdJournalCheckPostRoute::newPostJournal(prodJournalRoute.journalId,true);
    ProdJournalCheckPostRoute.run();
}

SQL CODE TO SCHEDULING SQL BACKUP SCHEDULING

SQL CODE TO SCHEDULING SQL BACKUP SCHEDULING

vikas mehta

DECLARE @FOLDERPATH VARCHAR(500)

DECLARE @FOLDERPATHWITHNAME VARCHAR(500)

Declare @NAME Varchar(200)

Declare @DATE Varchar(200)

Declare @TIME Varchar(200)

Declare @TIME1 Varchar(200)

--JUST NEED TO UPDATE BELOW PATH FOR DB BACKUP LOCATION

Set @FOLDERPATH = '\\path\'

select @TIME1 = Convert(time, getdate())

select @DATE = (select convert(varchar,getDate(),112))

select @TIME = ((select Convert(Varchar, DATEPART(mm, @TIME1))) + (Select CONVERT(Varchar, DATEPART(ss, @TIME1))))

Select @NAME = ax + '_' + @DATE --+ @TIME

Set @FOLDERPATHWITHNAME = @FOLDERPATH + @NAME + '.bak'

BACKUP DATABASE [ax] TO DISK = @FOLDERPATHWITHNAME WITH NOFORMAT, NOINIT, NAME = @NAME, SKIP, NOREWIND, NOUNLOAD, STATS = 10

**********script*******************************

USE [msdb]

GO

/****** Object:  Job [Ax Prod Backup Schedule]    Script Date: 6/12/2015 3:17:03 PM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 6/12/2015 3:17:03 PM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Ax Prod Backup Schedule',

                                @enabled=1,

                                @notify_level_eventlog=0,

                                @notify_level_email=0,

                                @notify_level_netsend=0,

                                @notify_level_page=0,

                                @delete_level=0,

                                @description=N'No description available.',

                                @category_name=N'[Uncategorized (Local)]',

                                @owner_login_name=N'vikasvikas\axaosadmin', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Ax prod Step 1]    Script Date: 6/12/2015 3:17:04 PM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Ax prod Step 1',

                                @step_id=1,

                                @cmdexec_success_code=0,

                                @on_success_action=1,

                                @on_success_step_id=0,

                                @on_fail_action=2,

                                @on_fail_step_id=0,

                                @retry_attempts=0,

                                @retry_interval=0,

                                @os_run_priority=0, @subsystem=N'TSQL',

                                @command=N'DECLARE @FOLDERPATH VARCHAR(500)

DECLARE @FOLDERPATHWITHNAME VARCHAR(500)

Declare @NAME Varchar(200)

Declare @DATE Varchar(200)

Declare @TIME Varchar(200)

Declare @TIME1 Varchar(200)

--JUST NEED TO UPDATE BELOW PATH FOR DB BACKUP LOCATION

Set @FOLDERPATH = ''\\sql bkup\''

select @TIME1 = Convert(time, getdate())

select @DATE = (select convert(varchar,getDate(),112))

select @TIME = ((select Convert(Varchar, DATEPART(mm, @TIME1))) + (Select CONVERT(Varchar, DATEPART(ss, @TIME1))))

Select @NAME = ''MicrosoftDynamicsAX'' + ''_'' + @DATE --+ @TIME

Set @FOLDERPATHWITHNAME = @FOLDERPATH + @NAME + ''.bak''

BACKUP DATABASE [MicrosoftDynamicsAX] TO DISK = @FOLDERPATHWITHNAME WITH NOFORMAT, NOINIT, NAME = @NAME, SKIP, NOREWIND, NOUNLOAD, STATS = 10

',

                                @database_name=N'master',

                                @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'AX 2012 Prod Job Schedule',

                                @enabled=1,

                                @freq_type=4,

                                @freq_interval=1,

                                @freq_subday_type=1,

                                @freq_subday_interval=0,

                                @freq_relative_interval=0,

                                @freq_recurrence_factor=0,

                                @active_start_date=20150421,

                                @active_end_date=99991231,

                                @active_start_time=235900,

                                @active_end_time=235959,

                                @schedule_uid=N'cddf7f74-3c12-414c-ab66-c12b7bc4628f'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

AX 2012 SALES TRADE AGGREMENTS UPLOAD FROM EXCEL X++ CODE

        class VIK_SalesMaster_ExcelImport extends RunBaseBatch

        {

             DialogField                         dialogfile;

             FilenameOpen                        fileName;

             localmacro.CurrentList

              fileName

            endmacro

        }

     

       dialog

        protected Object dialog()

        {

            DialogRunbase  _dialog = super();

       

            _dialog.caption("Sales : Trade Aggrement Upload");

            dialogfile = _dialog.addField("FilenameOpen","Enter file path : ");

            dialogfile.value(fileName);

       

            return _dialog;

        }

     

       getFromDialog

        public boolean getFromDialog()

        {

            ;

            fileName = dialogfile.value();

            return true;

        }

     

       run

        public void run()

        {

            SysExcelApplication excel;

            SysExcelWorkbooks workbooks;

            SysExcelWorkbook workbook;

            SysExcelWorksheets worksheets;

            SysExcelWorksheet worksheet;

            SysExcelCells cells;

            COMVariantType type;

            int row =1;

            int _noofprints;

            CustName name;

            int i,j;

            //sales price upload

            str queryPricingMaster,itemId,existingItemIdsList,tempValue;

            ResultSet resultSet;

            InventTable inventTable;

            AifEntityKeyList                        keys;

            DD_HI_RRP_Staging                       RRPStaging;

            PricePriceDiscJournalService            PriceDiscSvc;

            PricePriceDiscJournal                   PriceDiscJour;

            PricePriceDiscJournal_PriceDiscAdmTrans PriceDiscJourAdmTrans;

            PricePriceDiscJournal_InventDim         PriceDiscJourDim;

            PriceDiscAdmTrans   _PriceDiscAdmTrans;

            //vikas

            PriceDiscAdmTable                   priceDiscAdmTable;

            PriceDiscAdmTrans                   PriceDiscAdmTrans;

            PriceDiscAdmName                    PriceDiscAdmName;

            PriceDiscJournalName                pricediscjournalName;

            NumberSeq journalNum;

            //sales price upload//

            str _relation,_accountcode,_accountselection,_itemcode,_temrelation,_unit,_curcode;

            real _from,_amtincur;

            date _todate,_fromdate;

            PriceType _pricetype;

            itemId tempItemId;

            AviFiles

            SysOperationProgress progress1 = new SysOperationProgress();

            ;

       

        //excel

        define.filename(fileName)

        excel = SysExcelApplication::construct();

        workbooks = excel.workbooks();

        try

        {

        workbooks.open(filename);

        }

        catch (Exception::Error)

        {

        throw error("File cannot be opened");

        }

       

       

                workbook = workbooks.item(1);

                worksheets = workbook.worksheets();

                worksheet = worksheets.itemFromNum(1);

                cells = worksheet.cells();

                type = cells.item(row+1, 1).value().variantType();

       

            //progress1.setCaption("Updation of pricing master is in progress…");

            //progress1.setAnimation(AviUpdate);

       

       

       

         ttsBegin;

       

            Select firstOnly  PriceDiscAdmName;

            priceDiscAdmTable.clear();

            priceDiscAdmTable.initFromPriceDiscAdmName(PriceDiscAdmName);

            journalNum      = NumberSeq::newGetNum(SalesParameters::numRefPriceDiscJournalNum());

            pricediscjournalName        =   journalNum.num();

            priceDiscAdmTable.JournalNum = pricediscjournalName;

            priceDiscAdmTable.insert();

       

           while (type != COMVariantType::VT_EMPTY)

        {

            row++;

            tempItemId  = cells.item(Row,5).value().bStr();

                // Set PriceDiscJourAdmTrans

                  while select forUpdate * from inventTable where inventTable.ItemId == tempItemId

                {

       

                   // progress1.setText(strfmt("Item ID  %1", tempItemId));

                    PriceDiscAdmTrans.clear();

                    PriceDiscAdmTrans.JournalNum        = pricediscjournalName;

                    PriceDiscAdmTrans.relation          = real2int(cells.item(Row,1).value().double()) ;//relation

                    PriceDiscAdmTrans.InventDimId       = "Allblank";

                    PriceDiscAdmTrans.DisregardLeadTime = NoYes::Yes;

                    PriceDiscAdmTrans.AccountCode       = real2int(cells.item(Row,2).value().double()) ; //account code

                    PriceDiscAdmTrans.AccountRelation   = cells.item(Row,3).value().bStr();     //account relation

                    PriceDiscAdmTrans.ItemCode          = real2int(cells.item(Row,4).value().double());

                    PriceDiscAdmTrans.ItemRelation      = cells.item(Row,5).value().bStr();         //item realtaion item id

                    PriceDiscAdmTrans.QuantityAmountFrom= cells.item(Row,6).value().double();        //from quantity

                    PriceDiscAdmTrans.UnitId            = cells.item(Row,7).value().bStr();            //unit id

                    PriceDiscAdmTrans.Amount            = cells.item(Row,8).value().double();         //Amount in Curr

                    PriceDiscAdmTrans.Currency          = cells.item(Row,9).value().bStr();        //currency code

                    PriceDiscAdmTrans.FromDate          = cells.item(Row,10).value().date();        //from date

                    PriceDiscAdmTrans.ToDate            = cells.item(Row,11).value().date();         //todate

                    PriceDiscAdmTrans.insert();

                    itemId=inventTable.ItemId;

       

                }

                 type = cells.item(row+1, 1).value().variantType();

        }

            ttsCommit;

        excel.quit();

        }

     

       main

        public static void main(Args _args)

        {

            VIK_SalesMaster_ExcelImport    VIK_SalesMaster_ExcelImport = new VIK_SalesMaster_ExcelImport();

       

       

             if(VIK_SalesMaster_ExcelImport.prompt())

                VIK_SalesMaster_ExcelImport.run();

        }

      

AX 2012 MAP X++ FOR EXISTING RECORDS MAP INSERT MAP CHECK MAP LOOKUP

AX 2012 MAP X++ FOR EXISTING RECORDS

MAP for Exist in excel upload

public void run()

{

    SysExcelApplication     excel;

    SysExcelWorkbooks       workbooks;

    SysExcelWorkbook        workbook;

    SysExcelWorksheets      worksheets;

    SysExcelWorksheet       worksheet;

    SysExcelCells           cells;

    SysExcelCell            cell;

    SysExcelStyles          styles;

    SysExcelStyle           estyle;

    SysExcelFont            font;

    int                     row = 1;

    //vikas

    ProdTable               prodTable,prodTableRef,prodtabledate;

    InventDim               inventDim;

    int                     total1,total2,total3,total4,total5,total6;

    SysExcelWorksheetHelper worksheetHelper;

    SysExcelRange           range;

    ItemId                  itemId;

    InventOnhand            inventonHand;

    inventDim               inventDimRef;

    InventDimParm           inventDimParm;

    WrkCtrCapRes            WrkCtrCapRes1,WrkCtrCapRes2,WrkCtrCapRes3;

    WrkCtrTable             wrkCtrTable1,wrkCtrTable2,wrkCtrTable3;

    int                     x,y,z,j,k,l,fordays,mapi,mapint;

    WrkCtrIdBase            machine;

    SchedFromDate           mindate,mindateLess,mapdatefind,fordate;

    SchedToDate             maxdate1;

    date               strdate;

    str                 dateinstring;

    TransDate               transdate;

    Map                     map = new Map(Types::String,Types::Integer);

    Map                     mapintkey = new Map(Types::Integer,Types::String);

    InventTrans            inventTransTable;

   

    int                     days;

    str itemOrginStatus;

    excel = SysExcelApplication::construct();

    workbooks = excel.workbooks();

    workbook = workbooks.add();

    worksheets = workbook.worksheets();

    worksheet = worksheets.itemFromNum(1);

    cells = worksheet.cells();

    styles = workbook.styles();

    estyle  = styles.add("Header");

    font = estyle.font();

    font.bold(true);

    cell = cells.item(1, 1);

    cell.font().bold(true);

    cell.value('MONTH PRODUCTION PLANNING');

    //ITEM    PLAN    OPN STK    BAL TO BE PRODUCED    MTD    Operation    M/C

    cell = cells.item(3, 1);

    cell.value('SIZE');

    cell = cells.item(3, 2);

    cell.value('TYPE');

    cell = cells.item(3, 3);

    cell.value('PLAN');

    cell = cells.item(3, 4);

    cell.value('OPN STK');

    cell = cells.item(3, 5);

    cell.value('BAL TO BE PRODUCED');

    cell = cells.item(3, 6);

    cell.value('MTD    Operation');//NO OF

    cell = cells.item(3, 7);

    cell.value('M/C');

    row = 3;

    select maxof(SchedStart) from prodtabledate where prodtabledate.InventRefId == 'P000179';

    maxdate1 = prodtabledate.schedstart;

    prodtabledate.clear();

    select minof(SchedStart) from prodtabledate where prodtabledate.InventRefId == 'P000179';

    mindate = prodtabledate.schedstart;

    mindateLess = mindate -1;

     days = date2num(maxdate1) - date2num(mindate);

     fordays = days+10;

    for(j=10;j<fordays;j++)

    {

        map.insert(mindateLess+1,j);

        mindateLess = mindateLess+1;

    }

    //mapintkey

    for(mapi=10;mapi<fordays;mapi++)

    {

        mapintkey.insert(mapi,mindateLess+1);

        mindateLess = mindateLess+1;

    }

    //mapdate = 2015/9/23  ...9/28/2015

    for(k=10;k<fordays;k++)

    {

        cell = cells.item(3, k);

         if (Mapintkey.exists(k))

        {

            strdate = Mapintkey.lookup(k);

            dateinstring = Mapintkey.lookup(k);

        }

       // strdate = date2str(transdate,321,DateDay::Digits2,DateSeparator::Slash,DateMonth::Digits1or2,dateSeparator::Slash, DateYear::Digits4);

        cell.value(Mapintkey.lookup(dateinstring));

    }

    //map.insert('2', 'ew');

    //info(strFmt("%1",map.lookup(2)));

 while select prodTable join InventDim   where prodTable.ProdId == 'P000179' && prodTable.InventDimId == InventDim.inventDimId

   {

           // itemId = prodTable.ItemId;

             row= row+1;

             cell = cells.item(row, 1);

             cell.value(InventDim.InventSizeId);

             cell = cells.item(row, 2);

             cell.value(InventDim.InventColorId);

    while select prodTableRef join inventDimRef where prodTableRef.InventRefId == prodTable.ProdId

       {

            //row = row+1;

            cell = cells.item(row, 3); //plan

            cell.value(prodTableRef.qtysched);

            itemId = prodTable.ItemId;

            InventDimParm.initFromInventDim(inventDimRef);

            inventonHand = inventonHand::newParameters(itemId,inventDimRef,InventDimParm);

            cell = cells.item(row, 4); //

            cell.value(inventonHand.availPhysical());

            itemId = prodTable.ItemId;

            cell = cells.item(row, 5); //plan

            cell.value(prodTableRef.qtysched - inventonHand.availPhysical());

           //cell = cells.item(row, 6); //plan

            //cell.value(prodTableRef.qtysched - inventonHand.availPhysical());

            cell = cells.item(row, 7); //pool

            cell.value(prodTableRef.ProdPoolId);

            while select WrkCtrCapRes1 order by WrkCtrId,transdate where WrkCtrCapRes1.RefId == prodTableRef.ProdId && WrkCtrCapRes1.RefType == 1 //&& WrkCtrCapRes1.WrkCtrId == "BREHM-1"

           {   x = 8;y =10;//z=2;

               wrkCtrTable1.clear();

               select wrkCtrTable1 where wrkCtrTable1.Name == WrkCtrCapRes1.WrkCtrId;

               machine = WrkCtrCapRes1.WrkCtrId;

               cell = cells.item(row,8 ); //machine

               cell.value(machine);

                cell = cells.item(row, 9); //actual

                cell.value('Actual');

                row = row +1;

                cell = cells.item(row, 9); //plan

                cell.value('Plan');

                 if (Map.exists(WrkCtrCapRes1.TransDate))

                {

                    mapint = Map.lookup(WrkCtrCapRes1.TransDate);

                }

                cell = cells.item(row,mapint);

                cell.value(WrkCtrCapRes1.WrkCtrHours()*wrkCtrTable1.capacity);

            }

       }

   

        }

    worksheet.columns().autoFit();

    excel.visible(true);

}