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);

}