Sql Formatting Tool

 

Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.

For example like this:

exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine1, APAddress.AddressLine2, APAddress.AddressLine3, APAddress.City, APAddress.CountryID, APAddress.State, APAddress.PostalCode, APAddress.IsValidated, APAddress.tstamp, APAddress.CreatedByID, APAddress.CreatedByScreenID, APAddress.CreatedDateTime, APAddress.LastModifiedByID, APAddress.LastModifiedByScreenID, APAddress.LastModifiedDateTime FROM APAddress APAddress WHERE (APAddress.CompanyID = 2) AND  ( APAddress.AddressID = @P0) ORDER BY APAddress.AddressID /* admin@AP.30.40.00 */',N'@P0 int',@P0=-2147483647

If you can read it, congratulations, you are genius and I'm envy of you so you can stop reading further. But if you are just programmer as me, then the next step is the following:

1. remove exec sp_executesql 

2. In some editor find and replace @P0 with value -2147483647

3. remove tail /* admin@AP.30.40.00 */',N'@P0 int',@P0=-2147483647

4. Navigate in your browser to http://www.dpriver.com/pp/sqlformat.htm

5. Copy/Paste in window and choose format mssql

6. Press format SQL.

Compare previous sql with the following:

SELECT apaddress.addressid,
       apaddress.vendorid,
       apaddress.vendoraddressid,
       apaddress.isdefaultaddress,
       apaddress.revisionid,
       apaddress.addressline1,
       apaddress.addressline2,
       apaddress.addressline3,
       apaddress.city,
       apaddress.countryid,
       apaddress.state,
       apaddress.postalcode,
       apaddress.isvalidated,
       apaddress.tstamp,
       apaddress.createdbyid,
       apaddress.createdbyscreenid,
       apaddress.createddatetime,
       apaddress.lastmodifiedbyid,
       apaddress.lastmodifiedbyscreenid,
       apaddress.lastmodifieddatetime
FROM   apaddress APAddress
WHERE  ( apaddress.companyid = 2 )
       AND ( apaddress.addressid = -2147483647 )
ORDER  BY apaddress.addressid 

IMHO the second option is easier to read.

4 Comments

  • Tim Rodman said

    Nice tip. I didn't know about that website.

    I use the SQL Prompt plug-in from Red Gate which installs as a menu in SQL Management Studio. You can also format SQL using this tool.

  • docotor said

    Nice tool. IMHO even better then web site. But according to http://www.red-gate.com/products/sql-development/sql-prompt/

    "SQL Prompt starts at $369 per user" For now I'm not ready to pay to redgate such amount

  • Tim Rodman said

    I agree, it's a little pricey, especially if you can't get your company to pay for it.

    It does save time when writing code because it auto-completes columns, tables, even table joins for you. But, if you're just looking for a formatting tool, the website works great.

  • anonymous said

    Freeware:
    Notepad++ has an add-in called "Poor Man's Sql Formatter"

    http://www.architectshack.com/PoorMansTSqlFormatter.ashx

Add a Comment
 

 

Acumatica Customization Without Acumatica Gui

 

Hello.

Today I want to share some information about Acumatica customization.

Suppose, you want to change your pages not via Acumatica UI, but via Visual Studio. Reasons why you can have such desire can be the following:

1. Visual Studio is faster then Acumatica UI

2. You can put your changes under source control

3. You can easily exchange changes with your co-workers

Let's say you have following situation.

1. You need to make changes to pages ar301000.aspx and  pm301000.aspx and pm304000.aspx.

2. You need to add new page AP508000.aspx.

3. You want to include dll, which is named DS.dll.

In order to do this you'll need the following.

1. Create in your Acumatica instance folder CstPublished

2. Inside created folder create folders pages_ar, pages_pm

3. Copy/Paste into folder pages_ar page ar301000.aspx and into pages_pm pages  pm301000.aspx and pm304000.aspx.

4. Create new page AP508000.aspx in folder AP

5. Build your dll file

6. Create following file: project.xml, with the following records:

<Customization level="0" description="">
    <File AppRelativePath="CstPublished\pages_ar\ar301000.aspx" FileID="0849bd3b-c4ce-473e-8c82-c567112aa96b" />
    <File AppRelativePath="CstPublished\pages_ar\ar301000.aspx.cs" FileID="302c4089-6046-4a17-8411-1023315f5535" />
    <File AppRelativePath="Pages\AP\AP508000.aspx" FileID="b9e88968-7f36-4cfc-9383-caed8363c269" />
    <File AppRelativePath="Pages\AP\AP508000.aspx.cs" FileID="a1ce1918-f569-4ced-8a03-b5571dc26abb" />
    <File AppRelativePath="CstPublished\pages_pm\pm301000.aspx" FileID="8c174bf0-3143-4a86-83e9-c82704c9297a" />
    <File AppRelativePath="CstPublished\pages_pm\pm301000.aspx.cs" FileID="c435350a-89dc-455c-a9cb-6356987b2889" />
    <File AppRelativePath="CstPublished\pages_pm\pm304000.aspx" FileID="65b4f056-859f-463c-99cc-32a9cde11c7d" />
    <File AppRelativePath="CstPublished\pages_pm\pm304000.aspx.cs" FileID="46b0d39b-ba12-486a-be7f-6058a37cdca1" />
</Customization>

7. Create folder with the following structure:

   7.1 Folder CstPublished which has inside of it folders pages_ar and pages_pm. And pages_ar has all ar files, and pages_pm has changes related to pm pages

   7.2 Folder Pages, which has inside of it folder AP, and AP inside of it has AP508000.aspx and AP508000.aspx.cs files

   7.3 Put into folder file project.xml

8. Zip it and your deployment is ready.

Some pitfalls which I faced.

1. xml file should be named project.xml

2. FileId is guid, and each FileID should be unique.

3. All file and folder names shouldn't be capital. Not Pages_pm but pages_pm. And not CstPublished\pages_pm\PM304000.aspx but CstPublished\pages_pm\pm304000.aspx 

4. If you find other surprises with customization let me know.

Especially for Tony I made a screenshot of my customization folder.

11 Comments

  • mazia said

    Hi
    Do you provide development support to partners?

  • docotor said

    Hello Mazia,
    for free to some degree yes. And for money definitely yes :)

  • Tony said

    Hi, I got some questions:

    1) Is there something else to do after adding the file project.xml (I zip it as you said in the /CstPublished folder with the name project.zip)? is this correct or it should go in a sub folder?

    2) The .dll also goes in /CstPublished or in a especific subfolder?

    3) I got embed SQL querys in the project.xml, It will be executed doing this? o I'll have to run in appart.

    The thing is that I added both files "project.xml" and the zipped "project.zip" and in the menu is not showing the pages menu (sitemap), I wonder if it's needed some kind of refresh to get the changes.

    I'm working with Acumatica version 4.20.0935

    Thanks for your help,
    Regards.

  • docotor said

    Hello Tony,
    Inside the folder for customization following structure should be:
    Three folders: a) "Bin", b) "CstPublished", c) "Pages" and file project.xml.
    1. If you putted file project.xml into CstPublished folder, then move it one folder higher in the hierarchy.
    2. The .dll file goes to folder bin.
    3. It's possible to embed sql into customization, but I personally use two different files ( I mean customization file and project.sql which has all db changes )

  • docotor said

    I attached screenshot of my customization. Let me know if you'll need something else

  • Tony said

    Hi again, and thanks for your reply It was very helpful. I had issues inserting the dll but I didnt notice that in the project.xml example was missing (I thought It was there xD).

    I have another question, those GUID for the FileID how are obtained? I'm working with powershell and I can't see a way to get those GUID corresponding for each file.

    Thanks...

  • docotor said

    It can be any new guid, which is not mentioned in project. I used http://createguid.com web site. In case of powershell I can propose either to read from mentioned web site, or as another option to make some simple program which will generate for you guids

  • Fernando said

    Hi Docotor!

    I have a question somehow related to this thread.
    Is it possible to include a ZIP file in the Projet.xml and then extract its content? Let me explain further:

    <File AppRelativePath="(...)\files.ZIP" FileID="0849bd3b-c4ce-473e-8c82-c567112aa96b" />

    We are facing difficulties in terms of time and path length while publishing our customization projects, so we thought that if we could just copy the ZIP file and then execute a command that would extract its content, then we could solve both problems at once. Is this feasible?

    Thanks for your help :)

  • docotor said

    I don't think that it's possible. Actually I don't know. I just can propose you workaround. In your destination Acumatica instance locate folder CstPublished, and inside of it put your modified pages. Then you'll not need to include modified aspx pages into zip file. Just keep in mind naming conventions. In CstPublished should be ar301000 not AR301000. As far as I know Acumatica engine will make analysis of this folder and will consider them as active. If to speak about your pages which you made completely by yourself you can put them into Acumatica directory straight away. I also assume that you can put in Bin folder compiled version of your dll.

  • Ronan Masangcay said

    I need to code on my local machine using Visual Studio (VS) VS2013 but will deploy the DLL on another machine that has an Acumatica ERP instance. Can I code in VS2013 in my PC and see the Acumatica objects (particularly, Data Providers) in my VS2013; my PC has Acumatica Framework already.

    Please let me know how

    TY

  • docotor said

    You can code locally on your VS2013 and then you'll need to put dll and pages into Acumatica customization and then publish it at your Acumatica instance.

 

Add a Comment
 

 

Get All Columns Of Specific Table

 

Today I needed to get all columns of specifict table. 

SQL which you can use for this purpose is the following:

SELECT COLUMN_NAME 'All_Columns' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Specific table'

No Comments

 

Add a Comment
 

 

Screen Numbering In Acumatica

 

Hello everybody.

Today I want in short share screen numering in Acumatica.

Let's say we have screen number like this: xx.yy.zz.tt

in that case

yy is screen sequential number

zz is screen type: 10 - setup, 20 - Maintenance, 30 - data entry, 40 - inquiry, 50 - processing, 60 - reports.

tt is sub screen sequential number

1 Comment

  • maillot france 2014 said

    I am really thankful to the owner of this site who has shared this wonderful post at at this place.

Add a Comment
 

 

Acumatica Unit Test

 

Hello readers of mine blog.

Today I want to share with everybody who wants to make unit test of Acumatica how I achieved it.

For unit testing I use NUnit. In order to start my work with Acumatica Unit testing I wrote the following class:

    [TestFixture]
    public class CATranEntryExtTest
    {
        [Test]
        public void TestAutonumber()
        {
            var gr = PXGraph.CreateInstance<CATranEntry>();
        }
    }

Now with help of Resharper let's start debugging:

And here we go, the first error message:

PX.Data.PXProviderException : Provider cannot be instantiated.

   at PX.Data.PXDatabase.get_Provider() in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Database\Provider.cs: line 388

   at PX.Data.PXDatabase.GetSlot(String key, Type[] tables) in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Database\Provider.cs: line 802

   at PX.Data.PXGraph.a(Type A_0) in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Graph\Graph.cs: line 2684

   at PX.Data.PXGraph.CreateInstance(Type graphType) in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Graph\Graph.cs: line 112

   at PX.Data.PXGraph.CreateInstance() in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Graph\Graph.cs: line 57

So, it means we need to add some connection string to the site.  How to add it exactly? What proper way? I tried two next options:

1.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <remove name="ProjectX" />
    <add name="ProjectX" providerName="System.Data.SqlClient" connectionString="Data Source=DIO222\DIO195;Initial Catalog=Ac20140117;Integrated Security=False;User ID=sa;Password=1234" />
  </connectionStrings>
</configuration>



This way also:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionString value="Data Source=DIO222\DIO195;Initial Catalog=Ac20140117;Integrated Security=False;User ID=sa;Password=1234"></connectionString>
</configuration>

and if you suppose, that error message changed, you are wrong. It didn't go away.

Then I addressed support of Acumatica, and here is the answer:

 you just need this config. That is not necessary to create and use real database. You just need connectionString specified.

In case if with answer you know how to add connectionString to app.config for unit test and it will work, let me congratulate you. For me you are genious. Sadly to admit, I'm not. After a while, I decided to do crazy idea - copy/paste web.config into App.config. Imagine scale of my surprise when I noticed, that error went away!!!!!!

Now I got the next error message:

PX.Data.PXNotLoggedInException : You are not currently logged in.

Do you have any idea how to log in? 

In order to understand this idea lets go to Login.aspx.cs, maybe we can locate there something. For example method NormalLogin:

    private void NormalLogin(string[] companies)
    {
        if (companies != null && companies.Length == 1)
        {
            cmbCompany.Items.Clear();
            cmbCompany.Items.Add(companies[0]);
        }

        string loginText = txtUser.Text.Trim();
        string userName = PXDatabase.Companies.Length > 0 ? loginText + "@" +
            (cmbCompany.SelectedIndex != -1 ? cmbCompany.SelectedItem.Value : PXDatabase.Companies[0]) : loginText;

        if (!PXLogin.LoginUser(ref userName, txtPass.Text))
        {
            // we will change password during next round-trip
            PXContext.Session.SetString("ChangingPassword", txtPass.Text);

            DisablingUserPassword();
            EnablingChangingPassword();

            this.Master.Message = string.Empty;
        }
        else
        {
            PXLogin.InitUserEnvironment(userName, cmbLang.SelectedValue);
        }
    }

I'm considered about mehtod PXLogin.LoginUser. Lets use reflector in order to look there:

 

using (PXLoginScope pxLoginScope = new PXLoginScope(userName, new string[0]))using (PXLoginScope pxLoginScope = new PXLoginScope(userName, new string[0]))

I just think what can mean PXLoginScope. But maybe name is self-explanatory. Some scope where some login considered as working?

Lets write code similar to what reflector showed:

[Test]
public void TestAutonumber()
{
     using (var pxLoginScope = new PXLoginScope("someuserName", new string[0]))
     {
          var gr = PXGraph.CreateInstance<CATranEntry>();
     }
}

and now error message is 

PX.Data.PXUndefinedCompanyException : Unable determine proper company id for the request.

At least we can see, that error message changes during our thinking process :). 

All other conclusions were built on this code:

        MembershipUser membershipUser = (MembershipUser) null;
        try
        {
          if (Membership.ValidateUser(pxLoginScope.UserName, password))
          {
            membershipUser = Membership.GetUser(pxLoginScope.UserName);
            if (membershipUser != null)
            {
              pxLoginScope.UserName = membershipUser.UserName;
              userName = PXContext.PXIdentity.User.Identity.Name;
              if (pxLoginScope.CompanyName != null)
              {
                string[] rolesForUser = System.Web.Security.Roles.GetRolesForUser(pxLoginScope.UserName);
                if (rolesForUser == null || rolesForUser.Length == 0)
                  throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                  {
                    (object) pxLoginScope.CompanyName
                  });
                else if (!Enumerable.Contains<string>((IEnumerable<string>) PXDatabase.AvailableCompanies, pxLoginScope.CompanyName))
                  throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                  {
                    (object) pxLoginScope.CompanyName
                  });
              }
              if (PXLogin.c(pxLoginScope.UserName))
                return false;
              PXLogin.a(pxLoginScope.UserName, false);
              PXLogin.SetBranchID(pxLoginScope.UserName, pxLoginScope.CompanyName, pxLoginScope.Branch);
              PXSessionContextFactory.AuthenticateRequest();
              PXLicenseHelper.OnAuthenticate();
              return true;
            }

The function which has name a if to trust to reflector just updates some info in audit journal, and updates some info in db, which IMHO is not usable for unit test.

So, code, which I use now for initiating Unit test now looks like this:

    [TestFixture]
    public class CATranEntryExtTest
    {
        [Test]
        public void TestAutonumber()
        {
            string userName = "some user name in db";
            using (var pxLoginScope = new PXLoginScope(userName, new string[0]))
            {
                var membershipUser = (MembershipUser) null;
                // Membership.ValidateUser(pxLoginScope.UserName, "123");

                membershipUser = Membership.GetUser(pxLoginScope.UserName);
                if (membershipUser != null)
                {
                    pxLoginScope.UserName = membershipUser.UserName;
                    userName = PXContext.PXIdentity.User.Identity.Name;
                    if (pxLoginScope.CompanyName != null)
                    {
                        string[] rolesForUser = System.Web.Security.Roles.GetRolesForUser(pxLoginScope.UserName);
                        if (rolesForUser == null || rolesForUser.Length == 0)
                            throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                                {
                                    (object) pxLoginScope.CompanyName
                                });
                        else if (
                            !Enumerable.Contains<string>((IEnumerable<string>) PXDatabase.AvailableCompanies,
                                                         pxLoginScope.CompanyName))
                            throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                                {
                                    (object) pxLoginScope.CompanyName
                                });
                    }
                    PXLogin.SetBranchID(pxLoginScope.UserName, pxLoginScope.CompanyName, pxLoginScope.Branch);
                    var gr = PXGraph.CreateInstance<CATranEntry>();
                }
            }
          }
    }

At the end of execution gr looks like this:

Lets continue our way of unit testing for creating instance of our extension. This can be achieved via following way:

var accountsManager = gr.GetExtension<CATranEntryExt>();

Screenshot shows that we created instance of extension successfully:

Now I can test methods, which are inside of the extension CATranEntryExt.

I think it's enough for this article. If you have any comments, requests, wishes, critics, you are wormly welcome to express them.

 

Acumatica Unit Test

Acumatica unit test

Hello readers of mine blog.

Today I want to share with everybody who wants to make unit test of Acumatica how I achieved it.

For unit testing I use NUnit. In order to start my work with Acumatica Unit testing I wrote the following class:

    [TestFixture]
    public class CATranEntryExtTest
    {
        [Test]
        public void TestAutonumber()
        {
            var gr = PXGraph.CreateInstance<CATranEntry>();
        }
    }

Now with help of Resharper let's start debugging:

And here we go, the first error message:

PX.Data.PXProviderException : Provider cannot be instantiated.

   at PX.Data.PXDatabase.get_Provider() in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Database\Provider.cs: line 388

   at PX.Data.PXDatabase.GetSlot(String key, Type[] tables) in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Database\Provider.cs: line 802

   at PX.Data.PXGraph.a(Type A_0) in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Graph\Graph.cs: line 2684

   at PX.Data.PXGraph.CreateInstance(Type graphType) in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Graph\Graph.cs: line 112

   at PX.Data.PXGraph.CreateInstance() in c:\Builders\4_10-2013_12_16-23_17_15-Full\Scripts\BuildTemp\NetTools\PX.Data\Graph\Graph.cs: line 57

So, it means we need to add some connection string to the site.  How to add it exactly? What proper way? I tried two next options:

1.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <remove name="ProjectX" />
    <add name="ProjectX" providerName="System.Data.SqlClient" connectionString="Data Source=DIO222\DIO195;Initial Catalog=Ac20140117;Integrated Security=False;User ID=sa;Password=1234" />
  </connectionStrings>
</configuration>



This way also:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionString value="Data Source=DIO222\DIO195;Initial Catalog=Ac20140117;Integrated Security=False;User ID=sa;Password=1234"></connectionString>
</configuration>

and if you suppose, that error message changed, you are wrong. It didn't go away.

Then I addressed support of Acumatica, and here is the answer:

 you just need this config. That is not necessary to create and use real database. You just need connectionString specified.

In case if with answer you know how to add connectionString to app.config for unit test and it will work, let me congratulate you. For me you are genious. Sadly to admit, I'm not. After a while, I decided to do crazy idea - copy/paste web.config into App.config. Imagine scale of my surprise when I noticed, that error went away!!!!!!

Now I got the next error message:

PX.Data.PXNotLoggedInException : You are not currently logged in.

Do you have any idea how to log in? 

In order to understand this idea lets go to Login.aspx.cs, maybe we can locate there something. For example method NormalLogin:

    private void NormalLogin(string[] companies)
    {
        if (companies != null && companies.Length == 1)
        {
            cmbCompany.Items.Clear();
            cmbCompany.Items.Add(companies[0]);
        }

        string loginText = txtUser.Text.Trim();
        string userName = PXDatabase.Companies.Length > 0 ? loginText + "@" +
            (cmbCompany.SelectedIndex != -1 ? cmbCompany.SelectedItem.Value : PXDatabase.Companies[0]) : loginText;

        if (!PXLogin.LoginUser(ref userName, txtPass.Text))
        {
            // we will change password during next round-trip
            PXContext.Session.SetString("ChangingPassword", txtPass.Text);

            DisablingUserPassword();
            EnablingChangingPassword();

            this.Master.Message = string.Empty;
        }
        else
        {
            PXLogin.InitUserEnvironment(userName, cmbLang.SelectedValue);
        }
    }

I'm considered about mehtod PXLogin.LoginUser. Lets use reflector in order to look there:

using (PXLoginScope pxLoginScope = new PXLoginScope(userName, new string[0]))using (PXLoginScope pxLoginScope = new PXLoginScope(userName, new string[0]))

I just think what can mean PXLoginScope. But maybe name is self-explanatory. Some scope where some login considered as working?

Lets write code similar to what reflector showed:

[Test]
public void TestAutonumber()
{
     using (var pxLoginScope = new PXLoginScope("someuserName", new string[0]))
     {
          var gr = PXGraph.CreateInstance<CATranEntry>();
     }
}

and now error message is 

PX.Data.PXUndefinedCompanyException : Unable determine proper company id for the request.

At least we can see, that error message changes during our thinking process :). 

All other conclusions were built on this code:

        MembershipUser membershipUser = (MembershipUser) null;
        try
        {
          if (Membership.ValidateUser(pxLoginScope.UserName, password))
          {
            membershipUser = Membership.GetUser(pxLoginScope.UserName);
            if (membershipUser != null)
            {
              pxLoginScope.UserName = membershipUser.UserName;
              userName = PXContext.PXIdentity.User.Identity.Name;
              if (pxLoginScope.CompanyName != null)
              {
                string[] rolesForUser = System.Web.Security.Roles.GetRolesForUser(pxLoginScope.UserName);
                if (rolesForUser == null || rolesForUser.Length == 0)
                  throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                  {
                    (object) pxLoginScope.CompanyName
                  });
                else if (!Enumerable.Contains<string>((IEnumerable<string>) PXDatabase.AvailableCompanies, pxLoginScope.CompanyName))
                  throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                  {
                    (object) pxLoginScope.CompanyName
                  });
              }
              if (PXLogin.c(pxLoginScope.UserName))
                return false;
              PXLogin.a(pxLoginScope.UserName, false);
              PXLogin.SetBranchID(pxLoginScope.UserName, pxLoginScope.CompanyName, pxLoginScope.Branch);
              PXSessionContextFactory.AuthenticateRequest();
              PXLicenseHelper.OnAuthenticate();
              return true;
            }

The function which has name a if to trust to reflector just updates some info in audit journal, and updates some info in db, which IMHO is not usable for unit test.

So, code, which I use now for initiating Unit test now looks like this:

    [TestFixture]
    public class CATranEntryExtTest
    {
        [Test]
        public void TestAutonumber()
        {
            string userName = "some user name in db";
            using (var pxLoginScope = new PXLoginScope(userName, new string[0]))
            {
                var membershipUser = (MembershipUser) null;
                // Membership.ValidateUser(pxLoginScope.UserName, "123");

                membershipUser = Membership.GetUser(pxLoginScope.UserName);
                if (membershipUser != null)
                {
                    pxLoginScope.UserName = membershipUser.UserName;
                    userName = PXContext.PXIdentity.User.Identity.Name;
                    if (pxLoginScope.CompanyName != null)
                    {
                        string[] rolesForUser = System.Web.Security.Roles.GetRolesForUser(pxLoginScope.UserName);
                        if (rolesForUser == null || rolesForUser.Length == 0)
                            throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                                {
                                    (object) pxLoginScope.CompanyName
                                });
                        else if (
                            !Enumerable.Contains<string>((IEnumerable<string>) PXDatabase.AvailableCompanies,
                                                         pxLoginScope.CompanyName))
                            throw new PXException("You are not allowed to login to the company {0}.", new object[1]
                                {
                                    (object) pxLoginScope.CompanyName
                                });
                    }
                    PXLogin.SetBranchID(pxLoginScope.UserName, pxLoginScope.CompanyName, pxLoginScope.Branch);
                    var gr = PXGraph.CreateInstance<CATranEntry>();
                }
            }
          }
    }

At the end of execution gr looks like this:

Lets continue our way of unit testing for creating instance of our extension. This can be achieved via following way:

var accountsManager = gr.GetExtension<CATranEntryExt>();

Screenshot shows that we created instance of extension successfully:

Now I can test methods, which are inside of the extension CATranEntryExt.

I think it's enough for this article. If you have any comments, requests, wishes, critics, you are wormly welcome to express them.

14 Comments

  • Steven said

    Acumatica is horrible. It's just bad.

  • docotor said

    Partially agree with you, it has a lot of surprises :). But from my experience with ERP, all of big ERPs had surprises. Starting from Micrsofot Dynamics, SAP. All of them require to be good developer, which can critically apply what he finds in documentation

  • Tim Rodman said

    ERP products definitely aren't "one size fits all". Acumatica isn't for everyone, but I think it can be a good fit for a lot of companies.

    Of course, most of the headaches associated with ERP implementations aren't even related to the specific ERP product chosen:
    http://panorama-consulting.com/who-needs-help-managing-an-erp-implementation/

    So I'm sure that Acumatica become the scapegoat for some failed ERP implementations, but it's the same with every ERP product.

  • docotor said

    Tim, I can't say better then you. I just want to add that depending from task or set of tasks company or person can need totally different product. For somebody will be enough nopcommerce, for somebody just simple web site.

  • Ray Ban 5226 Kolor 2034 said

    Please dont think Im just trying to copy you, but I really like the formatof this site. Could you let me know which theme are you using? Or was it custom made?

  • docotor said

    Hi Ray Ban 5226 Kolor 2034,
    I'm using Orchard, Classig Blog Them

  • Buy Ray Ban Glasses Online In Pakistan said

    Please, can you Mail me and tell me few more thinks about this, I am really fan of your bloggets solved properly asap.

  • anonymous said

    I really like your writing style, excellent info

  • chaussures said

    This actually answered my problem, thanks!

  • Tony said

    Hi again, great article about starting a unit testing in Acumatica, I tried to apply it to my test project but it fails =(
    it says "Test 'M:---.---.UnitTests.MyFirstTestClass.TestAutonumber' failed:
    The type initializer for 'PX.Data.PXCache`1' threw an exception."
    Not sure what I'm missing here (I set an valid user and uncommented the line "Membership.ValidateUser..." and added the password of that user). Thanks in advance.

    -Regards.

  • docotor said

    Hi Tony, mentioned error message I seen, when passwords were hashed. Maybe you have the same. If you wish, I can send you source code of my test project.

  • Tony said

    Well, in my db the passwords are not hashed... =/
    In visual Studio I had to add some references because they were not set by default (maybe some reference in particular I'm missing?). I will appreciate if you send to my email (____) your source code, thanks in advance.

    Keep with the great blog and feedback... =)

  • docotor said

    Just send you with additional comments

Add a Comment

Autonumbering In Acumatica Second Way

 

In one of my previous posts I described how to add autonumbering to acumatica. Described method will work pretty well, if you don't have any pop-ups which also need to have autonumbering. In case if you have such pop-ups, then it is different story.

My next assignment on the job was to add autonumbering to the screen "Fund Transfers" which is marked as "CA301000" to the field Document ref at three places:

There is a saying, that one image is better then thousands of words :).

Here is example of my code for OutExtRefNbr:

        protected void CATransfer_OutExtRefNbr_FieldDefaulting(PXCache sender, PXFieldDefaultingEventArgs e)
        {
            var row = e.Row as CATransfer;
            if (row == null)
                return;
            var refNbr = "001224";
            e.NewValue = refNbr;
        }

 

If you have a question why on earth code below needed:

                   var row = e.Row as "Class for which we will provide extension";

                   if( row == null )

                      return;

the answer is simple: because Acumatica for some unknown reason calls method FieldDefaulting and for mysterious reason passes into e.Row null. If you have any idea why, you are welcome to share them. I don't have and just used workaround ( don't say it to my boss ).

 

The second part of my task was to add some value to pop-up window. That can be done via the following template:

public class "Class Extention name" : PXGraphExtension<"Class for which we will provide extension">

{

      protected void "Class for which we will provide extension"_RowUpdated(PXCache sender, PXRowUpdatedEventArgs e)

      {

             Base.AddFilter.Current."Field in pop-up" = "some default value";

            Base.AddFilter.Update(Base.AddFilter.Current);

       }

"Field in pop-up" you can find also in Design mode of Acumatica forms.

Here is fragment of my source code for changes:

protected void CATransfer_RowUpdated(PXCache sender, PXRowUpdatedEventArgs e)
{
     Base.AddFilter.Current.ExtRefNbr = "0012224";
     Base.AddFilter.Update(Base.AddFilter.Current);
}

In case if you have any comments, ideas don't hesitate to share them via comments line.

There is a saying, that one image is better then thousands of words :).

Here is example of my code for OutExtRefNbr:

        protected void CATransfer_OutExtRefNbr_FieldDefaulting(PXCache sender, PXFieldDefaultingEventArgs e)
        {
            var row = e.Row as CATransfer;
            if (row == null)
                return;
            var refNbr = "001224";
            e.NewValue = refNbr;
        }

 

If you have a question why on earth code below needed:

                   var row = e.Row as "Class for which we will provide extension";

                   if( row == null )

                      return;

the answer is simple: because Acumatica for some unknown reason calls method FieldDefaulting and for mysterious reason passes into e.Row null. If you have any idea why, you are welcome to share them. I don't have and just used workaround ( don't say it to my boss ).

 

The second part of my task was to add some value to pop-up window. That can be done via the following template:

public class "Class Extention name" : PXGraphExtension<"Class for which we will provide extension">

{

protected void "Class for which we will provide extension"_RowUpdated(PXCache sender, PXRowUpdatedEventArgs e)
{
      Base.AddFilter.Current."Field in pop-up" = "some default value";
      Base.AddFilter.Update(Base.AddFilter.Current);
}

"Field in pop-up" you can find also in Design mode of Acumatica forms.

Here is fragment of my source code for changes:

protected void CATransfer_RowUpdated(PXCache sender, PXRowUpdatedEventArgs e)
{
     Base.AddFilter.Current.ExtRefNbr = "0012224";
     Base.AddFilter.Update(Base.AddFilter.Current);
}

In case if you have any comments, ideas don't hesitate to share them via comments line.

 

 

Acumatica Security

 

Hello,

this post is dedicated to understanding how security in Acumatica works. If to be precisive security of accounts. 

Let's say you need in some way filter accounts for some user according to configuration at row level security screen ( GL104000 ).

In my case I had following records in database which correspondent to the following numbers:

Relation group mask : 16; 0; 0; 0

User group mask: 159; 96; 0; 0

Account group mask: 16; 0; 0; 0

How to join them? They are joined not by separated table, which has relations between groups and accounts or groups and users, but with usage of bit mask. It's very effective way to join and is much faster then holding adta in db. But the most complicated from viewpoint of understanding. Lets convert already mentioned numbers into bit masks:

Relation group mask :

00010000; 00000000; 00000000; 00000000

User group mask:

10011111; 11000000; 00000000; 00000000

Account group mask:

00010000; 00000000; 00000000; 00000000

From the bit mask you can notice that there is common bit between first byte in Account group mask and User group mask. Also there is common bit between "User group mask" and Relation group mask which means that user belongs to the group, and account belongs to the group. In order to work with them I wrote the following code:

 public List<Account> GetAvailableCashAccounts()
 {
     var result = new List<Account>();
     var userID = PXAccess.GetUserID();
     var user = GetUsers().First(u => u.PKID == userID);
     var allAccounts = GetAccounts();
     var groups = GetGroups();
     foreach (var account in allAccounts)
     {
           foreach (var relationGroup in groups)
           {
             for (var i = 0; i < relationGroup.GroupMask.Length && i < account.GroupMask.Length; i++)
               {
                  if ((relationGroup.GroupMask[i] & account.GroupMask[i]) == 0) continue;
                  if ((account.GroupMask[i] & user.GroupMask[i]) == 0 || result.Find(a => a.AccountCD.Equals(account.AccountCD)) != null) continue;
                  result.Add(account);
                 }
             }
       }
       return result;
}

As usually all your comments are welcomed, considered, premoderated, banned :)

 

Saxophone Music

 

I liked this music

Saxophone

No Comments

 

Add a Comment
 

 

I Want 3d Printer

 

I want 3D printer

https://www.youtube.com/watch?feature=player_embedded&v=FGSo_I86_lQ

No Comments

 

Add a Comment