Export data from Retail POS to Excel without installing Microsoft Excel

If you want to export data from Retail POS to Excel without installing Microsoft Excel, you can use one of DevExpress features to achieve this.
Here is my sample code :

using (SaveFileDialog sfdExport = new SaveFileDialog())
{
	sfdExport.Filter = "Excel (2003)(.xls)|*.xls";
	if (sfdExport.ShowDialog() != DialogResult.Cancel)
	{
		try
		{
			DataTable dtExport = new DataTable();
			//You can get your data and fill it here

			using (DevExpress.XtraGrid.GridControl grExport = new DevExpress.XtraGrid.GridControl())
			{
				using (DevExpress.XtraGrid.Views.Grid.GridView gvExport = new DevExpress.XtraGrid.Views.Grid.GridView())
				{
					gvExport.OptionsBehavior.AutoPopulateColumns = true;
					gvExport.GridControl = grExport;

					grExport.BindingContext = new BindingContext();
					grExport.DataSource = dtExport;
					grExport.MainView = gvExport;
					grExport.ForceInitialize();

					gvExport.PopulateColumns();
					gvExport.ExportToXls(strExportFilePath);
				}
			}
		}
		catch (Exception ex)
		{
			LSRetailPosis.ApplicationExceptionHandler.HandleException(this.ToString(), ex);
			throw;
		}
	}
}

You can fill the data table or use other type of datasources.

Advertisements

Get tax amount through x++

static void TIDgetTax(Args _args)
{
    TaxAmount           tax;
    CustInvoiceTrans    CustInvoiceTrans;
    ;

    CustInvoiceTrans =  CustInvoiceTrans::findRecId(5637155209);
    tax = Tax::calcTaxAmount(CustInvoiceTrans.TaxGroup, CustInvoiceTrans.TaxItemGroup, CustInvoiceTrans.InvoiceDate,
                            CustInvoiceTrans.CurrencyCode, CustInvoiceTrans.LineAmount, TaxModuleType::FreeTxtInvoice);

    info(strfmt('%1', tax));
}

Shrink database SQL Query

Shrink database data.

USE DatabaseName;
GO
-- Shrink the truncated file to 1024MB.
DBCC SHRINKFILE (DataBaseFileName, 1024);
GO

Shrink database log.

USE DatabaseName;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE DatabaseName
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (DatabaseFileLogName, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE DatabaseName
SET RECOVERY FULL;
GO

Reindex All Table On Database SQL Query

Script to re-index All Table On Database.

DECLARE @Database VARCHAR(255)   
DECLARE @Table VARCHAR(255)  
DECLARE @cmd NVARCHAR(500)  
DECLARE @fillfactor INT 

SET @fillfactor = 90 

DECLARE DatabaseCursor CURSOR FOR  
SELECT name FROM master.dbo.sysdatabases   
WHERE name IN ('DATABASENAME')   
ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  
WHILE @@FETCH_STATUS = 0  
BEGIN  

   SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + 
  table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES 
  WHERE table_type = ''BASE TABLE'''   

   -- create table cursor  
   EXEC (@cmd)  
   OPEN TableCursor   

   FETCH NEXT FROM TableCursor INTO @Table   
   WHILE @@FETCH_STATUS = 0   
   BEGIN   

		Print  ('ALTER INDEX ALL ON ' + @Table)
		SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' 
		EXEC (@cmd) 

       FETCH NEXT FROM TableCursor INTO @Table   
   END   

   CLOSE TableCursor   
   DEALLOCATE TableCursor  

   FETCH NEXT FROM DatabaseCursor INTO @Database  
END  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor

Consuming webservices (SOAP) from AX example code

public static container UserInfoByIDRequest(str _storeName, str _idType, str _idNo, str _extRef,str _username = "",str _password = "")
{
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.Summary summary;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.RemittanceClient svcClient;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDRequest request;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDResponse response;

    CLRObject clrObject;
    System.Exception ex;
    str errorCode, errorMesg;
    container  returnCon;
    boolean    canConnectAPI;
    str        errorCanConnectAPI;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        clrObject = CLRInterop::getType("TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.RemittanceClient");
        svcClient = AifUtil::createServiceClient(clrObject);

        request = new TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDRequest();

        //if no username or password provided, get from AX
        if(!_username || !_password)
        {
            _username = TIDIDSSimponiWebConsumeMethod::getUserNamePasswordStore(_storeName).IPMS_GaleriUserName;
            _password = TIDIDSSimponiWebConsumeMethod::getUserNamePasswordStore(_storeName).IPMS_GaleriPassword;
        }

        request.set_Username(_username);
        request.set_Password(_password);
        request.set_StoreName(_storeName);
        request.set_IdType(_idType);
        request.set_IdNo(_idNo);
        request.set_ExtRef(_extRef);

        //consuming webservice API
        response = svcClient.GetUserInfoByID(request);
        summary = response.get_Summary();

        //unpack to variable
        errorCode = summary.get_ErrorCode();
        errorMesg = summary.get_ErrorMessage();

        //packing all variable to container and return it
        returnCon += errorCode;
        returnCon += errorMesg;



        //sucess connect to webAPI
        canConnectAPI = true;

    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(ex.ToString());

        canConnectAPI = false;
        errorCanConnectAPI =ex.ToString();
    }

    return [canConnectAPI, errorCanConnectAPI, returnCon];
}

Only foreign key constraints are allowed on this table.

Best Practice Error

Only foreign key constraints are allowed on this table

Capture

How to rectify this best practice error.

For Custom Table

  1. Export table to XPO
  2. Edit it with text editor, and change
    • from: EnforceFKRelation 1
    • to: EnforceFKRelation 0
  3. delete table in AX, synchronize
  4. import  xpo file

Capture

For Standard  Table

Some custom relation table may have some error on standard table, we can fix this  best practice error using same trick as above, but for this one lets check on XPO file and change REFERENCETYPE value.

  1. Export table to XPO
  2. Edit it with text editor, and change
    • from: REFERENCETYPE  NORMAL
    • to: REFERENCETYPE PKFK
  3. delete table in AX, synchronize
  4. import  xpo file

Capture.

Starting Dynamics AX for Retail (POS Development)

One of the most challenging on dynamics AX is to develop POS on AX Retail module.

The Dynamics AX for Retail POS application was designed base on .Net Application using C# language so it’s little bit odd for regular AX technical who always develop using  X++, so since the POS is not using standard AX MorpX so we need to prepare to develop POS.

  1. Visual Studio for development tools.
  2. Dev Express License (Optional for better touch GUI)
  3. Retail SDK (POS Source Code)

Let’s  jump   into number 3. I assume you already have visual studio on dev server.

You can found Retail SDK on Dynamics AX Installation.

Installation

After installation finished you can found the source code  on document folder.

Source Code

For very first time lets  focus at the two folder on the POS Plug-ins, there is Services and Triggers because almost all POS function source code is on that folder.

Services: Services are actually .Net assemblies. POS implements many of the features as services using interfaces and can be modified using Visual Studio. POS loads these services at run time by calling the interfaces. So whenever you modify or extend the standard service keep the assembly name same as original so that POS can recognize it and call it at run time.

Triggers: Triggers are called before and after the operations. There are two types of triggers, Pre-triggers and Post-triggers. Pre-triggers provide a way of validation before a certain operation is executed. Post-triggers are used to respond to an operation after it has finished. You can modify the triggers same way as services.

*I use Dynamics AX 2012 R3 CU8 on this tutorial, also work for AX 2012 Feature Pack, AX 2012 R2, and AX 2o12 R3.

Create Custom Script

So first lets open the Triggers folder and double click Triggers.sln file, you will show all the triggers source code now expand on visual studio, lets pick our first function to modify.

For this tutorial we will add some function when POS starting, so lets’s open ApplicationTriggers Project and open  ApplicationTriggers.cs , at this class you will found the some of methods, this methods will be calling when POS staring, for example  ApplicationStart, ApplicationStop, PreLogon, PostLogon and etc.

Try to add some code at ApplicationStart method, i assume this method will be calling when POS application starting, lets add some simple script at here, let’s try add some dialog box.

 public void ApplicationStart()
{
     //Our Custom Script
     //<begin>
     MessageBox.Show("POS aplication starting....");
     //<end>

     string source = "IApplicationTriggers.ApplicationStart";
     string value = "Application has started";
     LSRetailPosis.ApplicationLog.Log(source, value, LSRetailPosis.LogTraceLevel.Debug);
     LSRetailPosis.ApplicationLog.WriteAuditEntry(source, value);
     ...
     ...
}

To make the script running on the POS we need to build the project in to dll and copy the dll on POS folder.

Try to run your POS, and the message box will be showing when POS starting.

Deploy Custom Script/dll

You should copy all  custom *.dll files on the Extensions folder at Retail POS Folder. Don’t replace the original ones.

We can using two different Extensions Folder on POS .

1. Retail POS\Extensions

Source Code2. Retail POS\Services\Extensions

Source Code

Always use the first one for custom Triggers *.dll, and second one for Services *.dll to make it easy to remember and maintenance.

Lets Starting To Debug POS Custom Code.

For debug the code, we can change some properties on the visual studio project,  use our first sample project, right click on that project and chose Properties.

First open Build  Tab on Properties UI and select the Output Path because this is Triggers so i  select “C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail POS\Extensions\” Folder.

Source Code

And than open Debug tab, at the Start Action group select Start external program and select your POS.exe file.

Source Code

just put your break point on your custom script, and run the project using F5 or Start Button. Lets see the magic happen.

*Note: You can copy all POS Folder to your own folder to make the  original POS Files safety.