Ferranti DateTime Conversion to Date

Ferranti  DateTime Conversion to Date

IIF (b.bilstartdate = '0','0',CONVERT(VARCHAR(12),DATEADD(day, -2, (36526+(LEFT( b.bilstartdate + 288000000000,11)-63082281600)/(24*3600))),5) )as startdate

Executing SQL Query in Dynamics AX

X++ language supports a number of ways to execute native SQL against any SQL data source.

Note: don't forget to set RunOn Property to Server of the class. Job:

static void BillingQuery(Args _args)
{
    RunSqlQuery q = new RunSqlQuery();
    q.RunSqlQueryMethod();

}

Class:

class RunSqlQuery
{
}

public void RunSqlQueryMethod()
{
     str sql;
    Connection conn;
    SqlStatementExecutePermission permission;
    Statement Stmt;
    ResultSet R;
    str result;
    ;
    sql = "select ACCOUNTNUM from custTable";

    conn = new Connection();
    permission = new SqlStatementExecutePermission(sql);
    permission.assert();

    Stmt = conn.createStatement();
    R = Stmt.executeQuery(sql);
    while ( R.next() )
    {
        result =  R.getString(1);
    }

    // the permissions needs to be reverted back to original condition.
    CodeAccessPermission::revertAssert();
}

Below the useful links.

http://blog.rahulsharma.in/2010/04/dynamics-ax-sqlstatementexecutepermissi.html

https://msdax.wordpress.com/2010/06/21/executing-sql-directly-from-x/

Time elapsed functions in AX 2012

The below links will have good examples of how to know time elapsed by a process in AX 2012,

This is needed sometime when you want to do some performance tuning of a method/ query.

Please refer the below links for more details

https://dynamicsaxgyan.wordpress.com/2012/01/12/timeconsumeda-very-useful-function-in-global-class-in-ax-2012-x/

https://ax-dynamics.com/three-ways-to-count-time-in-dynamics-ax-2012/

https://workflowax.wordpress.com/2009/10/01/timing-an-operation/

Debugging Managed Code - Dynamics AX

I am just sharing video to know how to debug and deploy managed code using Visual studio.

Disable ctrl + p keys to prevent

Disable ctrl + p keys to prevent

Reference: http://forums.asp.net/post/4012820.aspx

function disableCtrlKeyCombination(e)
{
        //list all CTRL + key combinations you want to disable
    var forbiddenkeys = new Array('a', 'n', 'c', 'x', 'v', 'j', 'p');
        var key;
        var isCtrl;
        if(window.event)
        {
                key = window.event.keyCode;     //IE
                if(window.event.ctrlKey)
                        isCtrl = true;
                else
                        isCtrl = false;
        }
        else
        {
                key = e.which;     //firefox
                if(e.ctrlKey)
                        isCtrl = true;
                else
                        isCtrl = false;
        }


        //if ctrl is pressed check if other key is in forbidenKeys array
        if(isCtrl)
        {
                for(i=0; i<forbiddenkeys.length; i++)
                {
                        //case-insensitive comparation
                    if (forbiddenkeys[i].toLowerCase() == String.fromCharCode(key).toLowerCase())
                        {
                                alert("Key combination CTRL + "
                                        +String.fromCharCode(key)
                                        +" has been disabled.");
                                return false;
                        }
                }
        }
        return true;
    }
    var oEventUtil = new Object();
    oEventUtil.AddEventHandler = function (oTarget, sEventType, fnHandler) {
        if (oTarget.addEventListener) {
            oTarget.addEventListener(sEventType, fnHandler, false);
        } else if (oTarget.attachEvent) {
            oTarget.attachEvent('on' + sEventType, fnHandler);
        } else {
            oTarget['on' + sEventType] = fnHandler;
        }
    }
    var oTB = function () {
        document.getElementById("TextBox1").focus();
    }

onkeypress="return disableCtrlKeyCombination(event);" onkeydown="return disableCtrlKeyCombination(event);" >

Finding Missing Table Index - analysis

The below query identifies the missing index on table:

After the post production -  one can run below query to analyze what are the index can be built in order to improve application performance.

SELECT 

       [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans), 

       [Table] = [statement],

       [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_'

              + sys.objects.name COLLATE DATABASE_DEFAULT

              + '_'

              + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns,'')+ISNULL(mid.inequality_columns,''), '[', ''), ']',''), ', ','_')

              + ' ON '

              + [statement]

              + ' ( ' + IsNull(mid.equality_columns, '')

              + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE

                     CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END

              + mid.inequality_columns END + ' ) '

              + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END

              + ';',

       mid.equality_columns,

       mid.inequality_columns,

       mid.included_columns

FROM sys.dm_db_missing_index_group_stats AS migs

       INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle

       INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle

       INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID

WHERE (migs.group_handle IN

              (SELECT TOP (500) group_handle

              FROM sys.dm_db_missing_index_group_stats WITH (nolock)

              ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)) 

       AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1

       AND mid.database_id = DB_ID()

ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

Splitting a Generic List into Multiple Chunks

Refer: http://www.chinhdo.com/20080515/chunking/

Splitting a Generic List into Multiple Chunks

public static List> BreakIntoChunks(List list, int chunkSize)
{
    if (chunkSize <= 0)
    {
        throw new ArgumentException("chunkSize must be greater than 0.");
    }

    List> retVal = new List>();

    while (list.Count > 0)
    {
        int count = list.Count > chunkSize ? chunkSize : list.Count;
        retVal.Add(list.GetRange(0, count));
        list.RemoveRange(0, count);
    }

    return retVal;
}