AX Retail 2012 R2: Customization of End-of-day Reports (X & Z Reports)

This blog will show you how to customize a Retail POS report focusing on X / Z report.  

Scenario

Standard X / Z report don't show the total paid of customer order deposits.  Outlet manager doesn't know why there's more money in drawer.  Customize those reports by inserting a line called "Sales order deposit".    

What is X / Z Report

They are in the group of drawer (and shift) operations. 

• X Report shows the register’s activity since the register opened.  It shows information such as the sales, the sum of all discounts given, and the customer count for the customers who have visited the store during the current shift (see above picture).  Printing by "Print X" operation.  
• Z Report shows information about the last closed shift.  Physically, it is similar to the X Report but consists of "Tenders" section to conclude all tenders of that shift.  POS automatically prints a Z report when a user closes a shift.  Reprinting by "Reprint Z" operation. 

Customer order payments   

Let me briefly explain about the sales order deposit.  The "Default deposit percentage" is in Retail parameters.  Once you create a new customer order and pay, the default deposit will be calculated from sales price including tax.  

Also when the customer picks up order, we will recall order and click "Pick up order".  That payment amount won't be shown on X/Z report too.    

End-of-Day customization

Before customize Retail POS, we need to prepare language texts by opening AX form "RetailLanguageText" from AOT.  All new text shown on POS have to prepare here.  And then, run N-Job "Registers" to push data to store DB.  Because some users in Singapore want to see POS screen as Chinese texts, I usually create 3 languages; en-us, en-sg, zh-hans (my Chinese colleague will help to edit text later on). 

Noted: normally this form isn't shown in any menus but I often use it.  So, I link it under Retail > Setup > POS   

All End-of-Day things are in "Retail SDK\POS Plug-ins\Services\EOD".  There are 3 modified classes in this blog: -

• BatchCalculation.cs manages batch calculation 
• BatchPrinting.cs manage printing 
• EOD.cs manage End-of-Day operations     

Batch Calculation

There are 3 main tables to be noticed: -

• Table PosSeedValues keeps next running number, TypeID for batch = 7 
• Table RetailTransactionTable keeps transaction header.  BatchID and payment amount are marked here.  When we open new shift, all retail transactions will be marked with the new Batch ID.   
• Table RetailPosBatchTable keeps total amount / count of fields after close shift.  

In class BatchCalculation.cs. First, declare constant variable to get payment amount summary (including tax) of customer order type from table RetailTransactionTable. And create a function to get data.    

        private const string sqlPKADepositTotal = "SELECT SUM(PAYMENTAMOUNT) " +
            "FROM RETAILTRANSACTIONTABLE AS H " +
            sqlWhereBatchClause +
            "AND [TYPE] = 19  AND ENTRYSTATUS <> 1 ";

        public static decimal PKACalculateDepositTotal(this Batch batch)
        {
            decimal ret = 0;
            DbConnection connection = ApplicationSettings.Database.LocalConnection;

            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                using (DbCommand dBCommand = new SqlCommand())
                {
                    dBCommand.Connection = connection;

                    dBCommand.Initialize(sqlPKADepositTotal, batch);
                    dBCommand.AddParameter("@TRANSACTIONSTATUS", TransactionStatus.Normal);
                    dBCommand.AddParameter("@TAXINCLUSIVE", ApplicationSettings.Terminal.TaxIncludedInPrice);
                    ret = DBUtil.ToDecimal(dBCommand.ExecuteScalar());
                }
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
            return ret;

        }

Batch Printing

In class BatchPrinting.cs.  Insert the deposit total line in Print function, I also insert sub total of (SalesTotal - Return + Deposit) 

       private static readonly string pkaDoubleLine = string.Empty.PadLeft(paperWidth, '=');

       public static void Print(this Batch batch, ReportType reportType)
        {
            // TextID's for the Z/X Report are reserved at 7000 - 7099

            StringBuilder reportLayout = new StringBuilder(2500);

            // Header
            reportLayout.PrepareHeader(batch, reportType);

            decimal pkaDepositTotal = BatchCalculation.PKACalculateDepositTotal(batch);

            // Total Amounts
            reportLayout.AppendReportLine(7015);
            reportLayout.AppendReportLine(7016, RoundDecimal(batch.SalesTotal));
            reportLayout.AppendReportLine(7017, RoundDecimal(-(batch.ReturnsTotal)));
            reportLayout.AppendReportLine(22284, RoundDecimal(pkaDepositTotal));  //Deposit
            reportLayout.AppendLine(singleLine);
            reportLayout.AppendReportLine(22278, RoundDecimal(batch.SalesTotal - batch.ReturnsTotal + pkaDepositTotal));  //Deposit
            reportLayout.AppendLine(pkaDoubleLine);
  
Then, the result in Total amounts section is as below: -

Save Deposit Total When Close Shift

Moreover, when we close shift, all total amount / count of fields will be created to RetailPosBatchTable.  We may save the deposit total to the table for later reference.  So, I add new field named PKASalesDepositTotal to the table.  Then, add constant variable for Update statement in BatchCalculation.cs and create a function to update field.           

        private const string sqlPKAWhereBatchUpdateClause = "WHERE STOREID = @STOREID " +
            "AND TERMINALID = @TERMINALID " +
            "AND DATAAREAID = @DATAAREAID " +
            "AND BATCHID = @BATCHID ";

        private const string sqlPKAUpdateBatch = "UPDATE RETAILPOSBATCHTABLE " +
            "SET PKASALESDEPOSITTOTAL = {0} " +

            sqlMSCWhereBatchUpdateClause;

        public static void PKACalculate(this Batch batch)
        {
            DbConnection connection = ApplicationSettings.Database.LocalConnection;

            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                using (DbCommand dBCommand = new SqlCommand())
                {
                    dBCommand.Connection = connection;
                    PKACalculateHeaderAdditional(dBCommand, batch);
                }
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                    connection.Close();
            }
        }

        private static void PKACalculateHeaderAdditional(DbCommand dBCommand, Batch batch)
        {
            decimal batchValue = PKACalculateDepositTotal(batch); 

            string commandText = string.Format(sqlPKAUpdateBatch, batchValue);
            dBCommand.CommandText = commandText;
            dBCommand.Parameters.Clear();
            dBCommand.Parameters.Add(new SqlParameter("@STOREID", batch.StoreId));
            dBCommand.Parameters.Add(new SqlParameter("@TERMINALID", batch.TerminalId));
            dBCommand.Parameters.Add(new SqlParameter("@BATCHID", batch.BatchId));
            dBCommand.Parameters.Add(new SqlParameter("@DATAAREAID", ApplicationSettings.Database.DATAAREAID));
            dBCommand.ExecuteNonQuery();

        }

Last step, modify class EOD.cs --> method CloseShift.  Before printing Z report, insert codes to calculate and update the new total field.
                // Calculate additional fields
                batch.PKACalculate();

                // Print Z report if user has permissions.
                IUserAccessSystem userAccessSystem = Application.BusinessLogic.UserAccessSystem;
                if (userAccessSystem.UserHasAccess(ApplicationSettings.Terminal.TerminalOperator.OperatorId, PosisOperations.PrintZ))
                {
                    POSFormsManager.ShowPOSMessageWithBackgroundWorker(99, delegate { batch.Print(ReportType.ZReport); });
                }

Finally, compile EOD.dll and replace it in Retail POS services directory (C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail POS\Services).