Job upload production order

//coded by fanddy
static void uploadProductionOrder(Args _args)
{
    // Progress Bar Variables
    #AviFiles
    SysOperationProgress        _ProgressBar = new SysOperationProgress();

    // Excel Variables
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    str                     Filename;
    COMVariantType          type;
    int                     row;
    FilenameFilter filenameFilter = ['*.xlsx,*.xls'];

    //variable
    ProdId              prodId;
    ItemId              itemid;
    Qty                 qtySched;
    TransDate           StartDate,endDate,deliveryDate,bomDate;
    InventSiteId        inventSiteId;
    InventLocationId    inventLocationId;
    ProdStatus          prodStatus;
    ProdBackStatus      prodBackStatus;
    ProdReservation     prodReservation;
    BOMId               bomId;

    //tables
    ProdTable       prodtable;
    InventTable     inventTable;
    InventDim       inventDim;
      // convert into str from excel cell value
    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
        switch (_cv.variantType())
        {
            case (COMVariantType::VT_BSTR):      return _cv.bStr();
            case (COMVariantType::VT_R4):        return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
            case (COMVariantType::VT_R8):        return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
            case (COMVariantType::VT_DECIMAL):   return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
            case (COMVariantType::VT_DATE):      return date2str(_cv.date(),123,2,1,2,1,4);
            case (COMVariantType::VT_EMPTY):     return '';
            default:
            throw error(strfmt('@SYS26908', _cv.variantType()));
        }
        return '';
    }




    filename = WinAPI::getOpenFileName(0, filenameFilter, '', '');
    if (filename=='')
    {
        warning('Filename must be specified correctly.');
        return;
    }

    //Filename = "C:\\prodorder.xls";
    application = SysExcelApplication::construct();
    workbooks   = application.workbooks();

    // gets the default ExchangeType RecId
    try
    {
        workbooks.open(Filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();
    row         = 1;

    // Start Progress Bar
    _ProgressBar.setCaption("Upload Production Order...");
    _ProgressBar.setAnimation(#AviTransfer);
    ttsBegin;
    do
    {
        //Incrementing the row line to next Row
        row++;

        _ProgressBar.setText(strFmt('Processing %1...', row));
        prodId              = COMVariant2Str(cells.item(row,1).value());
        itemid              = COMVariant2Str(cells.item(row,2).value());
        qtySched            = cells.item(row,3).value().double();
        startDate           = cells.item(row,4).value().date();
        endDate             = cells.item(row,5).value().date();
        inventSiteId        = COMVariant2Str(cells.item(row,6).value());
        inventLocationId    = COMVariant2Str(cells.item(row,7).value());
        prodStatus          = str2enum(ProdStatus,COMVariant2Str(cells.item(row,8).value()));
        prodBackStatus      = str2enum(ProdBackStatus,COMVariant2Str(cells.item(row,9).value()));
        deliveryDate        = cells.item(row,10).value().date();
        prodReservation     = str2enum(prodReservation,COMVariant2Str(cells.item(row,11).value()));
        bomDate             = cells.item(row,12).value().date();
        bomId               = COMVariant2Str(cells.item(row,13).value());


        // Initialize InventTable
        inventTable.clear();
        inventTable = inventTable::find(itemid);

        // Initialize the base values
        prodtable.clear();
        prodtable.initValue();
        prodtable.initFromInventTable(inventTable);
        prodtable.ProdId                = prodId;
        prodtable.ItemId                = inventTable.ItemId;
        prodtable.QtySched              = qtySched;
        prodtable.SchedStart            = StartDate;
        prodtable.SchedEnd              = endDate;
        prodtable.DlvDate               = deliveryDate;
        prodtable.RemainInventPhysical  = qtySched;
        prodtable.BOMDate               = today();
        prodtable.ProdStatus            = prodStatus;
        prodtable.BackorderStatus       = prodBackStatus;
        prodtable.Reservation           = prodReservation;

        // Initialize InventDim (Obrigatory)
        inventDim.clear();
        inventDim.InventSiteId          = inventSiteId;
        inventDim.InventLocationId      = inventLocationId;
        inventDim                       = inventDim::findOrCreate(inventDim);
        prodtable.InventDimId           = inventDim.inventDimId;

        // Set the active BOM and Route
        if(!bomId)
        {
            prodtable.BOMId = BOMVersion::findActive(prodtable.ItemId,
                                                        prodtable.BOMDate,
                                                        prodtable.QtySched,
                                                        inventDim).BOMId;
        }
        if(!prodtable.RouteId)
        {
            prodtable.RouteId = RouteVersion::findActive(prodtable.ItemId,
                                                         prodtable.BOMDate,
                                                         prodtable.QtySched,
                                                         inventDim).RouteId;
        }

        // Initialize BOMVersion
        prodtable.initBOMVersion();
        // Initialize RouteVersion
        prodtable.initRouteVersion();
        //Use ProdTableType class to create the production order
        prodtable.type().insert();
        

        print (strFmt('Line number %1 - Production order %2 - Item ID %3', row,prodtable.ProdId, itemId));

        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    
    ttsCommit;


    // quits the application
    application.quit();

    info('Upload success');
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s