SQL Query to get purchase invoice GST Details in Ax 2012

Description:-

In this article we will see about how to get GST Tax Rate and Tax amount through SQL Query.

Here I have given sample demonstration to get Purchase invoice GST Tax rate and Tax Amount in Ax 2012. Using SQL query you can check GST tax rate and tax amount.

Here I have create SQL Query for get Sales tax, Voucher, Currency Code, Exchange Rate, Item Number, item Quantity, Purch Price, Line Amount, Discount Amount, Charges Amount, Discount Percentage, GST Rate and Tax Amount.

Here also you can get Tax Registration Number by party Name in Ax 2012. Intermediate table for DirPartyTable and taxRegistration is DirPartyLocation and registrationNumber is the field to get value of tax registration Number.

You can also get Line Amount in INR also if you’re sales order in foreign currency. If you get IGST Amount then here I have also get IGST in INR.

SQL Query

SELECT vendinvoicejour.purchid
       AS
       'PURCHASE ORDER NUMBER',
       vendinvoicejour.invoiceaccount
       AS VENDACCOUNT,
       dirpartytable.NAME
       AS 'VENDOR NAME',
       taxregistrationnumbers_in.registrationnumber
       AS GSTIN,
       hsncodetable_in.code
       AS 'HSN CODE',
       vendinvoicejour.invoiceid
       AS INVOICEID,
       vendinvoicejour.ledgervoucher
       AS VOUCHER,
       Cast(vendinvoicejour.sumtax AS DECIMAL(10, 2))
       AS 'SALES TAX',
       vendinvoicejour.currencycode
       AS CURRENCY,
       Cast(vendinvoicejour.exchrate / 100 AS DECIMAL(10, 2))
       AS 'EXCHANGE RATE',
       taxtrans.sourcerecid
       AS 'VENDINVOICETRANS (RECID)',
       Cast(vendinvoicetrans.linenum AS DECIMAL(10, 2))
       AS 'LINE NUMBER',
       vendinvoicetrans.itemid
       AS ITEMID,
       vendinvoicetrans.NAME
       AS 'ITEM NAME',
       Cast(vendinvoicetrans.qty AS DECIMAL(10, 2))
       AS QUANTITY,
       Cast(vendinvoicetrans.priceunit AS DECIMAL(10, 2))
       AS 'PRICE UNIT',
       Cast(vendinvoicetrans.lineamount AS DECIMAL(10, 2))
       AS 'LINE AMOUNT',
       Cast(vendinvoicetrans.lineamountmst AS DECIMAL(10, 2))
       AS 'LINE AMOUNT (INR)',
       Cast(vendinvoicetrans.discamount AS DECIMAL(10, 2))
       AS DICOUNT,
       Cast(vendinvoicetrans.discamount * vendinvoicejour.exchrate / 100 AS
            DECIMAL(10, 2))
       AS 'DICOUNT (INR)',
       Cast(vendinvoicetrans.discpercent AS DECIMAL(10, 2))
       AS 'DISCOUNT PERCENTAGE',
       Cast(markuptrans.calculatedamount AS DECIMAL(10, 2))
       AS 'TOTAL CHARGES',
       Cast(markuptrans.calculatedamount * vendinvoicejour.exchrate / 100 AS
            DECIMAL(10, 2))
       AS 'TOTAL CHARGES (INR)',
       taxtrans.taxcode
       AS 'TAX CODE',
       taxtrans.taxaccounttype,
       Cast(taxtrans.taxvalue AS DECIMAL(10, 2))
       AS 'RATE',
       Cast(taxtrans.sourceregulateamountcur AS DECIMAL(10, 2))
       AS 'TAX AMOUNT',
       Cast(taxtrans.taxamount AS DECIMAL(10, 2))
       AS 'TAX AMOUNT (INR)',
       Cast(vendinvoicejour.invoiceamount AS DECIMAL(10, 2))
       AS 'INVOICE AMOUNT',
       Cast(vendinvoicejour.invoiceamountmst AS DECIMAL(10, 2))
       AS
       'INVOICE AMOUNT (INR)',
       Cast(( taxwithholdtrans_in.taxwithholdamountcur * -1 ) AS DECIMAL(10, 2))
       AS
       'TCS AMOUNT'
FROM   vendinvoicetrans
       JOIN vendinvoicejour
         ON vendinvoicejour.invoiceid = vendinvoicetrans.invoiceid
            AND vendinvoicejour.purchid = vendinvoicetrans.purchid
            AND vendinvoicejour.invoicedate = vendinvoicetrans.invoicedate
            AND vendinvoicejour.numbersequencegroup =
                vendinvoicetrans.numbersequencegroup
            AND vendinvoicejour.internalinvoiceid =
                vendinvoicetrans.internalinvoiceid
       LEFT OUTER JOIN taxwithholdtrans_in
                    ON taxwithholdtrans_in.voucher =
                       vendinvoicejour.ledgervoucher
       LEFT OUTER JOIN taxtrans
                    ON taxtrans.sourcerecid = vendinvoicetrans.recid
                       AND taxtrans.voucher = vendinvoicejour.ledgervoucher
                       AND taxtrans.transdate = vendinvoicejour.invoicedate
                       AND taxtrans.taxaccounttype = 1
                       AND taxtrans.taxcode IN ( 'IGST', 'CGST', 'SGST' )
       --,'BCD','ECESS C','IGST CUS','SHECESS C')
       LEFT OUTER JOIN markuptrans
                    ON markuptrans.transrecid = vendinvoicetrans.recid
       LEFT OUTER JOIN vendtable
                    ON vendtable.accountnum = vendinvoicejour.invoiceaccount
       LEFT OUTER JOIN dirpartytable
                    ON dirpartytable.recid = vendtable.party
       LEFT OUTER JOIN taxinformation_in
                    ON taxinformation_in.registrationlocation =
                       dirpartytable.primaryaddresslocation
                       AND taxinformation_in.isprimary = 1
       LEFT OUTER JOIN taxregistrationnumbers_in
                    ON taxregistrationnumbers_in.recid = taxinformation_in.gstin
       LEFT OUTER JOIN inventtable
                    ON inventtable.itemid = vendinvoicetrans.itemid
       LEFT OUTER JOIN hsncodetable_in
                    ON hsncodetable_in.recid = inventtable.hsncodetable_in
WHERE  --VENDINVOICEJOUR.PURCHID = 'Purchase Order Number'
  vendinvoicejour.invoiceid = 'Invoice Number'
GROUP  BY taxwithholdtrans_in.taxwithholdamountcur,
          taxtrans.taxcode,
          taxtrans.taxaccounttype,
          taxtrans.taxamount,
          taxtrans.sourceregulateamountcur,
          vendinvoicejour.invoiceid,
          vendinvoicejour.purchid,
          vendinvoicejour.invoiceaccount,
          taxtrans.sourcerecid,
          taxtrans.taxvalue,
          dirpartytable.NAME,
          dirpartytable.primaryaddresslocation,
          taxregistrationnumbers_in.registrationnumber,
          taxinformation_in.gstin,
          taxinformation_in.registrationlocation,
          hsncodetable_in.code,
          vendtable.party,
          vendtable.accountnum,
          markuptrans.calculatedamount,
          markuptrans.transrecid,
          vendinvoicetrans.itemid,
          vendinvoicetrans.NAME,
          vendinvoicetrans.lineamount,
          vendinvoicetrans.lineamountmst,
          vendinvoicetrans.recid,
          vendinvoicejour.invoiceamount,
          vendinvoicejour.invoiceamountmst,
          vendinvoicejour.invoiceaccount,
          vendinvoicejour.currencycode,
          vendinvoicejour.exchrate,
          vendinvoicejour.ledgervoucher,
          vendinvoicejour.sumtax,
          vendinvoicetrans.qty,
          vendinvoicetrans.priceunit,
          vendinvoicetrans.discamount,
          vendinvoicetrans.discpercent,
          vendinvoicetrans.linenum
ORDER  BY vendinvoicejour.invoiceid ASC  

How to Change Selected Row Color in Gridview in Asp.Net

Description:-

As we know there are many events in Gridview for different Operation here we want to change row color when we mouse over on row for that we will user RowDatabound event of Gridview. RowDatabound event read each row of Gridview and bind in Gridview Control. So ser can see dynamically when he/she over mouse control in Gridview row. So let’s start to achieve this functionality.

Create table in Database and Fill Some Data.

CREATE TABLE [dbo].[Employeee](

                [Number] [int] IDENTITY(1,1) NOT NULL,

                [Name] [varchar](50) NOT NULL,

                [Gender] [varchar](50) NOT NULL,

                [Email] [varchar](50) NOT NULL,

                [MobileNumber] [bigint] NOT NULL,

                [Bdate] [date] NULL,

 CONSTRAINT [PK_Employeee] PRIMARY KEY CLUSTERED

(

                [Number] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Design your Webpage like below.

<div>

<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound"

            BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"

            CellPadding="4" ForeColor="Black" GridLines="Horizontal"     OnSelectedIndexChanged="OnSelectedIndexChanged" Height="153px" Width="681px">

            <FooterStyle BackColor="#CCCC99" ForeColor="Black" />

            <HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />

            <PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />

            <SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />

            <SortedAscendingCellStyle BackColor="#F7F7F7" />

            <SortedAscendingHeaderStyle BackColor="#4B4B4B" />

            <SortedDescendingCellStyle BackColor="#E5E5E5" />

            <SortedDescendingHeaderStyle BackColor="#242121" />

</asp:GridView>

<br />

<asp:Label ID="msg" runat="server" Text=""></asp:Label>

</div>

Now Go to Code behind and Bind Data in Gridview so we can see data in Gridview Control. Bind data in Page_load () event.

protected void Page_Load(object sender, EventArgs e)

        {

            SqlDataAdapter da = new SqlDataAdapter();

            DataSet ds = new DataSet();

            string sql = null;

            string connetionString = "Data Source=UMESH-PC\\SQLEXPRESS;Initial Catalog=UH;Integrated Security=True";

            sql = "select * from Employeee";

            SqlConnection connection = new SqlConnection(connetionString);

            connection.Open();

            SqlCommand command = new SqlCommand(sql, connection);

            da.SelectCommand = command;

            da.Fill(ds);

            da.Dispose();

            command.Dispose();

            connection.Close();

            GridView1.DataSource = ds.Tables[0];

            GridView1.DataBind();

        }

Now generate Gridview_RowDataBound () event of Gridview so we can read each row and change color or particular row from Gridview.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

{

   if (e.Row.RowType == DataControlRowType.DataRow)

   {

     e.Row.Attributes["onmouseover"] = "this.style.backgroundColor='aquamarine';";

     e.Row.Attributes["onmouseout"] = "this.style.backgroundColor='white';";

   }

}

Now check in your browser and over mouse control in each row to change color of Gridview row.

Fileupload with validation, Download and Delete in Asp.net

Description:-

Here we will Check when we Upload file using File Upload Control in dot net and also we will check size of that file, type of file and if file selected or not when all condition successfully pass that whatever file we have selected for upload that file will upload in server. Here I have given download when you select file from Gridview then file download automatically and when you want delete file from server also you can delete as well.

Create table in Sql and Name it File_Mst.

CREATE TABLE [dbo].[File_Mst](
      [FileID] [bigint] IDENTITY(1,1) NOT NULL,
      [FileName] [varchar](50) NOT NULL,
      [FileType] [varchar](50) NOT NULL,
      [FileData] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_File_Mst] PRIMARY KEY CLUSTERED
(
      [FileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Default.aspx:-

<div style="width: 400px; margin-left: 240px; margin-top: 90px;">
<table>
<tr>
  <td>
    <asp:FileUpload ID="FileUpload2" runat="server" />
  </td>
  <td>
    <asp:Button ID="btnFUClick" runat="server" Text="Button"OnClick="btnFUClick_Click" />
  </td>
</tr>
<tr>
  <td>
    <asp:Label ID="lblmessage" runat="server" />
  </td>
</tr>
</table>
</div>
<div style="width: 400px; margin-left: 240px; margin-top: 20px;">
  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnSelectedIndexChanged="GridView1_SelectedIndexChanged">
    <Columns>
      <asp:TemplateField HeaderText="File ID">
        <ItemTemplate>
          <asp:Label ID="lblfileID" runat="server" Text='<%#Eval("FileID") %>'></asp:Label>
        </ItemTemplate>
      </asp:TemplateField>
      <asp:TemplateField HeaderText="File Name">
        <ItemTemplate>
          <asp:Image ID="Image1" runat="server" ImageUrl='<%#Eval("FileName") %>' Height="30px" Width="50px" />
          <asp:LinkButton ID="lnkDownload1" Text='<%#Eval("FileName") %>' CommandArgument='<%# Eval("FileName") %>' runat="server" OnClick="DownloadFile"></asp:LinkButton>
        </ItemTemplate>
      </asp:TemplateField>
      <asp:TemplateField HeaderText="File Type">
        <ItemTemplate>
          <asp:Label ID="lblfiletype" runat="server" Text='<%#Eval("FileType") %>'></asp:Label>
        </ItemTemplate>
      </asp:TemplateField>
      <asp:TemplateField HeaderText="Action">
        <ItemTemplate>
          <asp:LinkButton ID="lnkDelete" Text="Delete"CommandArgument='<%# Eval("FileID") %>' runat="server" OnClick="DeleteFile" />
        </ItemTemplate>
      </asp:TemplateField>
      <%--<asp:TemplateField>
        <ItemTemplate>
          <asp:Label ID="lblfiledata" runat="server" Text='<%# Eval("FileData") %>'></asp:Label>
        </ItemTemplate>
      </asp:TemplateField>--%>
    </Columns>
  </asp:GridView>
</div>

Default.aspx.cs:-

protected void btnFUClick_Click(object sender, EventArgs e)
{
  if (FileUpload2.HasFile)
  {
    try
    {
      if (FileUpload2.PostedFile.ContentType == "image/jpeg" || FileUpload2.PostedFile.ContentType == "image/png")
      {
        if (FileUpload2.PostedFile.ContentLength < 102400)
        {
          string FileName = FileUpload2.FileName;
          string filename = "~/MyFiles/" +Path.GetFileName(FileName);
          string FileExt = Path.GetExtension(FileName);
          string filetype = string.Empty;
          Stream str = FileUpload2.PostedFile.InputStream;
          BinaryReader br = new BinaryReader(str);
          Byte[] size = br.ReadBytes((int)str.Length);
          FileUpload2.SaveAs(Server.MapPath("~/MyFiles/") + FileName);
          lblmessage.ForeColor = Color.Green;
          lblmessage.Text = "Upload status: File uploaded!";
          con = new SqlConnection(CS);
          cmd = new SqlCommand();
          cmd.CommandText = "insert into File_Mst(FileName,FileType,FileData) values(@Name,@Type,@Data)";
          cmd.Connection = con;
          //cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@Name", filename);
          cmd.Parameters.AddWithValue("@Type", FileExt);
          cmd.Parameters.AddWithValue("@Data", size);
          con.Open();
          cmd.ExecuteNonQuery();
          BindGridviewData();
        }
        else
        {
          lblmessage.ForeColor = Color.Red;
          lblmessage.Text = "Upload status: The file has to be less than 100 kb!";
        }
      }
      else
      {
        lblmessage.ForeColor = Color.Red;
        lblmessage.Text = "Upload status: Only JPEG files are accepted!";
      }
    }
    catch (Exception ex)
    {
      lblmessage.ForeColor = Color.Red;
      lblmessage.Text = "Upload status: The file could not be uploaded. The following error occured: " +ex.Message;
    }
  }
}

private void BindGridviewData()
{
  using (con = new SqlConnection(CS))
  {
    using (SqlCommand cmd = new SqlCommand())
    {
      cmd.CommandText = "select * from File_Mst";
      cmd.Connection = con;
      con.Open();
      GridView1.DataSource = cmd.ExecuteReader();
      GridView1.DataBind();
      con.Close();
    }
  }
}

protected void DownloadFile(object sender, EventArgs e)
{
  string filePath = (sender as LinkButton).CommandArgument;
  Response.ContentType = ContentType;
  Response.AppendHeader("Content-Disposition", "attachment; filename=" +Path.GetFileName(filePath));
  Response.WriteFile(filePath);
  Response.End();
}

protected void DeleteFile(object sender, EventArgs e)
{
  string filePath1 = (sender as LinkButton).CommandArgument;
  using (con = new SqlConnection(CS))
  {
    using (SqlCommand cmd = new SqlCommand())
    {
      cmd.CommandText = "delete from File_Mst where FileID=" + filePath1;
      cmd.Connection = con;
      con.Open();
      cmd.ExecuteNonQuery();
      con.Close();
    }
  }
  //Response.ContentType = ContentType;
  //File.Delete(filePath1);
  Response.Redirect(Request.Url.AbsoluteUri);
}

calculate time for running process in Ax 2012

Description:-

In this article we will see about how to calculate time for running process in dynamics ax. Here I have created simple Job to get running time for getting data in dynamics ax.

Code:-

static void CalculateTime(Args _args)
{
    int startTime = timeNow();
    int endTime;
    SalesTable SalesTable;
    int countrecords=0;
    while select SalesTable
    {
        countrecords++;
    }
    endTime = timeNow();
    
    info(strFmt("Process took %1", timeConsumed(startTime, endTime
    info(strFmt("Tome : %1  to %2",time2str(startTime,1,1),time2str(endTime,1,1)));
    info(strFmt("Count records - %1",countrecords));    
}

Output:-

Get all the file name from a given Directory including sub directories in asp.net

Description:-

In this article we will see about how to get files name from directory or subdirectory which we assign in directory. It’s simple to get all files name from the directory using the DirectoryInfo class and get the all files from that directory in dot.net. we have to set for Subdirectory if there are sub directory is there than it will not check for sub directory it will count from root directory only and return file name from that root directory. Here is the demo to count from directory or subdirectory also.

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    DirectoryInfo d = new DirectoryInfo(Server.MapPath("~/FileToRead/"));
    string d2 = d.FullName.ToString();
    List<string> Files = Directory.GetFiles(d2, "*", SearchOption.AllDirectories).ToList();
    foreach (List<string> li in Files)
    {
      Response.Write(Files[0].ToString());
    }
  }
}

Format Time using Dataset fields in SSRS report

Description:-

In this article we will see about how to format time using dataset fields in SSRS report. here is the expression to format Time using Dataset fields in SSRS report.

Expression:-

Format(IIF(Fields!New_Time.Value = "NO CHANGE","00:00",Fields!New_Time.Value),"HH:mm tt")  

Or

Format(IIF(Fields!New_Time.Value = "NO CHANGE","00:00",Fields!New_Time.Value),"HH:MM tt")