Saturday, December 24, 2011

Import sales orders from master and details text files

The are many code snippets which you can find easily over the internet to import sales orders from a text file or from excel. The purpose of this post is to share my experience of uploading data into sales order form from two different text files.

void ImportSalesOrders()
{
    Container                  con;
    CustTable                 custTable;
    SalesTable                salesTable;
    SalesLine                  salesLine;
    Headers                    headers;
    Stock                        details;

    num                           newSalesId;
    Description                custDescription;
    
    InventTable                inventTable;
    InventDim                  inventDim;
    InventSiteId               inventSiteId;
    InventLocationId         inventLocationId;


    CustAccount             custAccount;
    real                           itemPrice;
    ItemId                       itemId;

    InventDim                  frominventDim,ToinventDim;

    int                             x,y;
    NumberSeq               num;

    SysOperationProgress progressbar = new SysOperationProgress();
    int i;
    ;

    progressbar.setCaption('Importing Journals');
    progressbar.setAnimation(filenameHeader);
    progressbar.setTotal(30000);
    for (i = 1; i <= 30000; i++)
    {
        progressbar.setText(strfmt("@SYS105740", i));
        progressbar.setCount(i, 1);
    }

    this.importData();
    ttsbegin;
    while select headers
    {
        SalesTable = SalesTable::findDocumentNumber(headers.DocumentNumber);

        if(!SalesTable)
        {
            //create sales table
            salesTable.initValue();

            num = NumberSeq::newGetNum(SalesParameters::numRefSalesId());
            newSalesId = num.num();

            if (salesTable::exist(newSalesId))
            {
                num.abort();
                throw error("@SYS23020");
            }

            salesTable.SalesId = newSalesId;
            num.used();

            if(custDescription != "")
            {
                salesTable.SalesName = custDescription;
            }

            custAccount = headers.CustAccount;

            //fetch the customer details from cust table using account no selected from drop-down list
            select * from custTable where custTable.AccountNum == custAccount;

            salesTable.CustAccount = custTable.AccountNum;
            salesTable.InvoiceAccount = custTable.InvoiceAccount;
            salesTable.SalesType = SalesType::Sales;
            salesTable.SalesStatus = SalesStatus::None;
            salesTable.CurrencyCode = custTable.Currency;

            salesTable.CustGroup = custTable.CustGroup;
            salesTable.DeliveryDateControlType = SalesDeliveryDateControlType::SalesLeadTime;

            //Set dates as systemDateTime
            salesTable.ReceiptDateRequested = systemdateget();
            salesTable.ShippingDateRequested = systemdateget();

            salesTable.LanguageId = 'en-us';
            salesTable.DlvMode = '10';

            inventSiteId = headers.InventSiteId;
            inventLocationId = headers.InventSiteId;

            salesTable.InventSiteId = inventSiteId;
            salesTable.InventLocationId = inventLocationId;
            salesTable.DocumentNumber = headers.DocumentNumber;

            //Craete Sales Order
            salesTable.insert();

            while select details
            where headers.DocumentNumber == details.DocumentNumber
            {
                salesLine.clear();

                // Create Sales Order Line
                salesLine.SalesId = salesTable.SalesId;
                salesLine.initFromSalesTable(salesTable);

                itemId = details.ItemId;

                salesLine.ItemId = itemId;
                salesLine.SalesUnit = InventTable::find(salesLine.ItemId).salesUnitId();

                select * from inventTable where inventTable.ItemId == salesLine.ItemId;
                salesLine.initFromInventTable(inventTable);

                inventDim.clear();
                inventDim.InventSiteId = inventSiteId;
                inventDim.InventLocationId = inventSiteId;

                salesLine.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;

                //Set Qty as 1 by default
                salesLine.SalesQty = details.Qty;
                salesLine.ConfirmedDlv = salesTable.ShippingDateConfirmed;
                salesLine.lineNum = SalesLine::lastLineNum(salesLine.salesId) + 1.0;
                salesLine.RemainInventPhysical = 1;
                salesLine.RemainSalesPhysical = 1;
                salesLine.DlvMode = '10';
                salesLine.SalesStatus = SalesStatus::None;
                salesLine.SalesPrice = details.UnitPrice;
                salesLine.LineAmount = salesLine.SalesQty * salesLine.SalesPrice;

                //Insert sales line items
                salesLine.insert();
            }
        }
        ++x;
    }
    ttscommit;
    info(strfmt("%1 Header(s) imported with their details", x));
}

void importData()
{
    Container               con;
    // I created these two table headers and details to make the import process fast I had huge data files to import
    Headers                 headers;
    Stock                   details;

    TextBuffer              tbHeader = new TextBuffer();
    TextBuffer              tbDetails = new TextBuffer();

    int                     cntHeader, cntDetails;
    int                     numLinesHeader, numLinesDetails;
    container               inLineHeader, inLineDetails;

    int i;
    ;

    tbHeader.fromFile(filenameHeader);
    tbDetails.fromFile(filenameDetail);

    numLinesHeader = tbHeader.numLines();
    numLinesDetails = tbDetails.numLines();

    delete_from headers;
    delete_from details;

    if(numLinesHeader)
    {
        ttsbegin;
        inLineHeader = str2Con(tbHeader.nextToken(true));


        for(cntHeader = 1; cntHeader < numLinesHeader; ++cntHeader)
        {
            inLineHeader = str2Con(tbHeader.nextToken(true));

            headers.DocumentNumber = conpeek(inLineHeader, 1);
            headers.CustAccount = conpeek(inLineHeader, 2);
            headers.insert();
        }

        inLineDetails = str2Con(tbDetails.nextToken(true));

        for(cntDetails = 1; cntDetails < numLinesDetails; ++cntDetails)
        {
            inLineDetails = str2Con(tbDetails.nextToken(true));

            details.DocumentNumber = conpeek(inLineDetails, 1);
            details.ItemId = conpeek(inLineDetails, 2);
            details.Qty = conpeek(inLineDetails, 3);
            details.UnitPrice = conpeek(inLineDetails, 4);
            details.Discount = conpeek(inLineDetails, 5);
            details.UnitDiscount = conpeek(inLineDetails, 6);
            details.insert();
        }

        ttscommit;
    }
}