Showing posts with label FROM. Show all posts
Showing posts with label FROM. Show all posts

Tuesday, November 21, 2017

SAVE REPORT TO PDF FROM AX JOB CONTROLLER CLASS : VIKAS MEHTA HOW TO CALL A REPORT FROM JOB AND SAVE IT IN PDF

SAVE REPORT TO PDF FROM AX JOB CONTROLLER CLASS : VIKAS MEHTA
HOW TO CALL A REPORT FROM JOB AND SAVE IT IN PDF

public static void SaveReportToPDFFromController(Args _args)
{
    VIKSalesInvoiceFinanceController  salesInvoiceController;
    VIKSalesInvoiceFinanceContract    salesInvoiceContract;
    Args                    args = new Args();
    SrsReportRunImpl        srsReportRun;
    CustInvoiceJour         custInvoiceJour;
    SRSReportExecutionInfo  reportExecutionInfo;
    ReportName              reportName = "VIKSalesFinance.PrecisionDesign1";//PrecisionDesign1_backup
    #define.ReportName('VIKSalesFinance.PrecisionDesign1')
    ;
    //SRSReportExecutionInfo = new SRSReportExecutionInfo();d
    select firstOnly custInvoiceJour where custInvoiceJour.SalesId == "1234”;
    args.record(custInvoiceJour);

    salesInvoiceController = new VIKSalesInvoiceFinanceController();
    salesInvoiceController.parmReportName(reportName);

    reportExecutionInfo = salesInvoiceController.parmReportContract().parmReportExecutionInfo() as SrsPrintMgmtExecutionInfo;

    if (!reportExecutionInfo)
    {
       reportExecutionInfo = new SrsPrintMgmtExecutionInfo();
    }
    //reportExecutionInfo.parmOriginalDestinationFileName("c:\\SalesInvoice.pdf");
    salesInvoiceContract = salesInvoiceController.parmReportContract().parmRdpContract();
    salesInvoiceContract.parmReportCopyName('');
    salesInvoiceContract.parmTenderTypeId('21');
    salesInvoiceContract.parmInvoiceId(custInvoiceJour.InvoiceId);
    salesInvoiceContract.parmSalesId(custInvoiceJour.SalesId);

  //  salesInvoiceContract.
   // salesInvoiceContract.parmRecordId(custInvoiceJour.RecId); // Record id must be passed otherwise the report will be empty
   // salesInvoiceContract.parmInvoiceIdparm(custInvoiceJour.RecId); // Record id must be passed otherwise the report will be empty
    salesInvoiceController.parmArgs(args);
    salesInvoiceController.parmReportName(#ReportName);
    salesInvoiceController.parmDialogCaption("Sales Finance Invoice");
     args.parm(strFmt("Copy - 1"));

    srsReportRun = salesInvoiceController.parmReportRun() as SrsReportRunImpl;

    salesInvoiceController.parmReportRun(srsReportRun);
    salesInvoiceController.parmReportContract().parmReportExecutionInfo(reportExecutionInfo);
    salesInvoiceController.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    salesInvoiceController.parmReportContract().parmPrintSettings().overwriteFile(true);
    salesInvoiceController.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    salesInvoiceController.parmReportContract().parmPrintSettings().fileName("c:\\test123\\SREPORT.pdf");
    salesInvoiceController.runReport();
}

static void SaveReportToPDFFromController(Args _args)
{
    SalesInvoiceController  salesInvoiceController;
    SalesInvoiceContract    salesInvoiceContract;
    SRSPrintDestinationSettings     settings;
    Args                    args = new Args();
    SrsReportRunImpl        srsReportRun;
    CustInvoiceJour         custInvoiceJour;
    ReportName              reportName = "SalesInvoice.Report";
    ;

    select firstOnly custInvoiceJour where custInvoiceJour.invoiceid == "sid";
    args.record(custInvoiceJour);

    salesInvoiceController = new SalesInvoiceController();
    salesInvoiceController.parmReportName(reportName);

    salesInvoiceContract = salesInvoiceController.parmReportContract().parmRdpContract();
    salesInvoiceContract.parmRecordId(custInvoiceJour.RecId); // Record id must be passed otherwise the report will be empty
  //  salesInvoiceContract.parmCountryRegionISOCode(SysCountryRegionCode::countryInfo()); // comment this code if tested in pre release
    salesInvoiceController.parmArgs(args);

    srsReportRun = salesInvoiceController.parmReportRun() as SrsReportRunImpl;

     salesInvoiceController.parmReportName(ssrsReportStr(VIKSalesFinance, PrecisionDesign1));
    // Use execution mode appropriate to your situation
  //  salesInvoiceController.parmExecutionMode(SysOperationExecutionMode::ScheduledBatch);
    // Suppress report dialog
    salesInvoiceController.parmShowDialog(false);

    salesInvoiceController.parmReportRun(srsReportRun);
    salesInvoiceController.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File);
    salesInvoiceController.parmReportContract().parmPrintSettings().overwriteFile(true);
    salesInvoiceController.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF);
    salesInvoiceController.parmReportContract().parmPrintSettings().fileName("c:\\VIKAS\\sdc.pdf");


    settings = salesInvoiceController.parmReportContract().parmPrintSettings();
    settings.printMediumType(SRSPrintMediumType::File);
    settings.fileFormat(SRSReportFileFormat::PDF);
    settings.fileName(@'c:\\TEST\1234.pdf');

    salesInvoiceController.startOperation();
}



MULTISELECT VALUES FROM GRID AND EXECUTE ONE BY ONE :VIKAS MEHTA : MICROSOFT DYNAMICS AX x++ CODE MULTISELECT IN FORMS

MULTISELECT VALUES FROM GRID AND EXECUTE ONE BY ONE  :VIKAS MEHTA : MICROSOFT DYNAMICS AX x++ CODE MULTISELECT IN FORMS

Multiple select location warehouse ax x++ code helper class multiselect
Just write the below code on the clicked method event and the code will consider all the selected records in the grid and execute as per the requirement.
void clicked()
{
    InventLocation InventLocationLoc;
    MultiSelectionHelper helper = MultiSelectionHelper::construct();

    super();

    helper.parmDatasource(InventLocation_DS);

    InventLocationLoc = helper.getFirst();
    while (InventLocationLoc.RecId != 0)
    {
        select firstOnly selectWH where selectWH.InventLocationId == InventLocationLoc.InventLocationId
        ;
        if(!selectWH)
        {
            // your execution here
        }
        else
            info(strFmt("@VIK178",selectWH.InventLocationId));//warehouse already exist
        InventLocationLoc = helper.getNext();
    }

    info("warehouse assigned”);
}

Delete Excess Data in Table TaxDocumentJson from SQL command in Microsoft Dynamics AX

Delete Excess Data in Table TaxDocumentJson from SQL command in Microsoft Dynamics AX


Remove the excess data in TaxDocumentJson at in AX HQ. Please carry this out on a UAT environment. Once this is tested successfully, you can do the same steps on Production.
Run the following SQL query to remove excess data
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
  BEGIN
   BEGIN TRANSACTION
   -- Delete some small number of rows at a time
     DELETE TOP (10) j from taxdocumentjson j
inner join TaxDocumentRow r on r.TAXDOCUMENTJSON = j.ID;
SET @Deleted_Rows = @@ROWCOUNT;   
   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END
You can count the number of the records required to be cleaned via another session to understand the progress
Select count(*) from taxdocumentjson j inner join TaxDocumentRow r on r.TAXDOCUMENTJSON = j.ID;

HOW TO CONNECT TO SQL FROM AX AND UPDATE HOW TO CONNECT TO EXTERNAL DATABASE FROM AX HOW TO UPDATE RECORDS IN SQL FROM AX : DYNAMICS AX X++ CODE

HOW TO CONNECT TO SQL FROM AX AND UPDATE
HOW TO CONNECT TO EXTERNAL DATABASE FROM AX
HOW TO UPDATE RECORDS IN SQL FROM AX

1) fIRST CREATE AN ODBC CONNECTION FROM THE SYSTEM TO CONNECT TO THE REQUIRED DATABASES.
// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("databaseName");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

Thursday, May 4, 2017

MICROSOFT DYNAMICS AX 2012 : CODE TO GET ON HAND OF AN ITEM STOCK COUNT

MICROSOFT DYNAMICS AX 2012 : GET ON HAND OF AN ITEM STOCK COUNT
static void stockOnHand(Args _args)
{
    InventOnhand        inventOnHand;
    InventDim           inventDim;
    InventDimParm       inventDimParm;
    InventQty           stockOnHand;
    ItemId              itemid = "00001aTestInter";
 
    inventDim.InventSiteId = "SITE"; // site
    inventDim.InventLocationId = "WAREHOUSE"; // warehouse
 
 
    inventDimParm.initFromInventDim(inventDim);
 
    inventOnHand = InventOnhand::newParameters(itemid,inventDim,inventDimParm);
 
    stockOnHand = inventOnHand.availPhysical();
 
    info (strFmt(" Stock on Hand for Item %1 is %2",itemid,stockOnHand));
 
}

MICROSOFT DYNAMICS AX 2012 : CODE TO DEPLOY SERVICE FROM AX

DEPLOY AX SERVICES FROM CODE

Below is the script for automatically deploy or undeploy  AIF service.
In this example update the port with current company and deploy the service.
static void AIFservices(Args _args)
{
    AifPort  port;
    AifPortName portName = "servicename";
    AifPortManager::undeployPort(portName);
    port = AifPort::find(portName,true);

    if(port)
    {
        ttsBegin;
        if(port.Company != curext() )
        {
            port.Company = curext();
            port.update();
        }
        ttsCommit;
    }
    AifPortManager::deployPort(portName);
}

Friday, April 15, 2016

IMPORT JOURNALS FROM EXCEL USING X++ IN AX 2009 ledgerJournalTable VOUCHER SYSEXCEL

Importing Journals from Excel using X++ in AX 2009

class VIKAS_ImportVendInvoiceJournalLines
{
    Dialog                         dialog;
    DialogField                    dialogfield;
    Filename                       filename;

    #AviFiles
    SysExcelApplication            application;
    SysExcelWorkbooks              workbooks;
    SysExcelWorkbook               workbook;
    SysExcelWorksheets             worksheets;
    SysExcelWorksheet              worksheet;
    SysExcelCells                  cells;
    COMVariantType                 type;
    COMVariantType                 typeModule;
    NumberSeq                      numberSeq;
    NumberSequenceTable            numSeqTable;

    str                            Name, num,text, currency,businessunit,account,accountType, department,offsetaccount,OffsetAccounttype;
    int64                          costcenter;
    real                           debit, credit;
    container                      Account1, offsetaccount1;
    str                            acc;
    date                           transdate;

    LedgerJournalName              ledgerJournalName;
    LedgerJournalTable             ledgerJournalTable;
    LedgerJournalTrans             ledgerJournalTrans;



    container                      cont1,cont2,offSetAcctPattern;
    int                            cnt;

    LedgerJournalAC                AccountNumb, offsetAccountnum;

    container                      ledgerDimension;

    LedgerJournalACType            LedgerJournalACType;
    boolean                        ret;

}
--------------------------------------------------------------------------------------------------------------------------


public void DataImport()
{
    str         mSBU,mDepartment,mCostCenter,mPurpose;
    str         oSBU,oDepartment,oCostCenter,oPurpose;
    str         invoice;
    str         TDSgroup,salesTaxGroup,itemSalesTax;
    date        documentdate;
    Voucher     voucher;






    SysOperationProgress progress = new SysOperationProgress();
    int                 row = 0;
    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();


    ledgerJournalName = ledgerjournalname::find(ledgerjournaltable.JournalName);
    row = 1;

    do
    {
        row++;
        transdate       = cells.item(row, 1).value().date();
        Currency        = cells.item(row, 2).value().bStr();
        accountType     = cells.item(row, 3).value().bStr();
        switch(cells.item(row, 4).value().variantType())
        {
            case COMVariantType::VT_BSTR:
                AccountNumb = strFmt("%1", cells.item(row, 4).value().bStr());
                break;
            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
                AccountNumb = strFmt("%1", any2int(cells.item(row, 4).value().double()));
                break;
            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
                AccountNumb = strFmt("%1", cells.item(row, 4).value().int());
                break;
            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
                AccountNumb = strFmt("%1", cells.item(row, 4).value().uLong());
                break;
            case COMVariantType::VT_EMPTY:
                AccountNumb = '';
                break;
            default:
                throw error(strfmt('Unhandled variant type (%1).', cells.item(row+1, 1).value().variantType()));
        }
        mSBU            = cells.item(row, 5).value().bStr();
        mDepartment     = cells.item(row, 6).value().bStr();
        mCostCenter     = cells.item(row, 7).value().bStr();
        mPurpose        = cells.item(row, 8).value().bStr();
        invoice         = cells.item(row, 9).value().bStr();
        Text            = cells.item(row, 10).value().bStr();
        Debit           = any2real(cells.item(row, 11).value().double());
        Credit          = any2real(cells.item(row, 12).value().double());
        OffsetAccounttype = cells.item(row, 13).value().bStr();

        switch(cells.item(row, 14).value().variantType())
        {
            case COMVariantType::VT_BSTR:
                offsetAccountnum = strFmt("%1", cells.item(row, 14).value().bStr());
                break;
            case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
                offsetAccountnum = strFmt("%1", any2int(cells.item(row, 14).value().double()));
                break;
            case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
                offsetAccountnum = strFmt("%1", cells.item(row, 14).value().int());
                break;
            case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
                offsetAccountnum = strFmt("%1", cells.item(row, 14).value().uLong());
                break;
            case COMVariantType::VT_EMPTY:
                offsetAccountnum = '';
                break;
            default:
                throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 1).value().variantType()));
        }
        oSBU            = cells.item(row, 15).value().bStr();
        oDepartment     = cells.item(row, 16).value().bStr();
        oCostCenter     = cells.item(row, 17).value().bStr();
        oPurpose        = cells.item(row, 18).value().bStr();
        TDSgroup        = cells.item(row, 19).value().bStr();
        salesTaxGroup   = cells.item(row, 20).value().bStr();
        itemSalesTax    = cells.item(row, 21).value().bStr();
        documentdate    = cells.item(row, 22).value().date();

        //In Excel cell should be in Text format
        try
        {
            ttsbegin;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum   = ledgerJournalTable.JournalNum;
            ledgerJournalTrans.TransDate    = transdate;
            ledgerJournalTrans.Dimension[1] = mSBU;
            ledgerJournalTrans.Dimension[2] = mDepartment;
            ledgerJournalTrans.Dimension[3] = mCostCenter;
            ledgerJournalTrans.Dimension[4] = mPurpose;

            //select firstOnly numSeqTable
            //    where numSeqTable.RecId  == ledgerJournalName.NumberSequenceTable;
            if (!voucher)
            {
                numberseq = numberseq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
                voucher = numberseq.voucher();
            }
            ledgerJournalTrans.Voucher              = voucher;
            ledgerJournalTrans.AccountType          = str2enum(LedgerJournalACType, accountType);


            ledgerJournalTrans.AccountNum           = AccountNumb;
            ledgerJournalTrans.Txt                  = Text;
            ledgerJournalTrans.CurrencyCode         = Currency;
            ledgerJournalTrans.AmountCurDebit       = Debit;
            ledgerJournalTrans.AmountCurCredit      = Credit;
            ledgerJournalTrans.OffsetAccountType    = str2enum(LedgerJournalACType, offsetaccountType);
            ledgerJournalTrans.OffsetAccount        = offsetAccountnum;
            ledgerJournalTrans.Invoice              = invoice;
            ledgerJournalTrans.TaxGroup             = salesTaxGroup;
            ledgerJournalTrans.TaxItemGroup         = itemSalesTax;
            ledgerJournalTrans.DocumentDate         = documentdate;
            ledgerJournalTrans.TDSGroup_IN          = TDSgroup;


            if (ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();

            }

            ttscommit;


            type = cells.item(row+1, 1).value().variantType();
        }
        catch(Exception::Error)
        {
            info(strFmt('Catched an error in row: %1',row));
        }


        info(strFmt('journal inserted %1',ledgerJournalTable.JournalNum));

    }

    while (type != COMVariantType::VT_EMPTY);
    application.quit();
}

-------------------------------------------------------------------------------------------------------------------------

public void run(Args _args)
{

    ;
    LedgerJournalTable = _args.record();
    dialog = new dialog('Excel Import');
    dialogfield = dialog.addField(TypeId(FilenameOpen), 'File Name');

    dialog.run();

    if(dialog.run())
    {
        filename =(dialogfield.value());
    }

    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    if(filename)
    {
        try
        {
            workbooks.open(filename);
        }
        catch (Exception::Error)
        {
            throw error('File cannot be opened.');
        }
        this.DataImport();



    }
}

-------------------------------------------------------------------------------------------------------------------------

public static void main(Args args)
{
    VIKAS_ImportVendInvoiceJournalLines import = new VIKAS_ImportVendInvoiceJournalLines();
    import.run(args);

}

--------------------------------------------------------------------------------------------------------------------------

void clicked()
{
    FormRun callerForm;
    Args args = new Args();

    VIKAS_ImportVendInvoiceJournalLines VIKAS_ImportVendInvoiceJournalLines = new         VIKAS_ImportVendInvoiceJournalLines();
    ;
    args.record(ledgerjournaltable);
    VIKAS_ImportVendInvoiceJournalLines.run(args);

    callerForm          = element.args().caller();
    callerForm.dataSource().refresh();
    callerForm.dataSource().reread();

}


Related Posts Plugin for WordPress, Blogger...