Calculation code for CGST,GST & IGST

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class View_proformainvoie : System.Web.UI.Page

{

Utility ut = new Utility(); decimal d;

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["aspnetdbConnectionString"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

bindrecords();

populateinvoicetype();

populatecurrency("");

populatebankdetails();

populatecountry();

populatetransportmode();

Addname_FirstGridViewRow();

pnladd.Visible = false;

pnlview.Visible = true ;

}

#region function -----------------------------------------------------------

private void invoicetypeselectedindex()

{

try

{

if (ddlinvoicetype.SelectedValue.ToString() == "1")

{

populatecurrency("India");

divsgst.Visible = true;

divcgst.Visible = true;

divigst.Visible = false;

ddlcurrency.Enabled = false;

ddlcurrency.SelectedIndex = 1;

}

else if (ddlinvoicetype.SelectedValue.ToString() == "2")

{

populatecurrency("India");

divsgst.Visible = false;

divcgst.Visible = false;

divigst.Visible = true;

ddlcurrency.Enabled = false;

ddlcurrency.SelectedIndex = 1;

}

else if (ddlinvoicetype.SelectedValue.ToString() == "3")

{

populatecurrency("");

divsgst.Visible = false;

divcgst.Visible = false;

divigst.Visible = false;

ddlcurrency.Enabled = true;

ddlcurrency.SelectedIndex = 0;

}

else

{

populatecurrency("");

}

catch (Exception ex)

{

throw;

}

private void populatecurrency(string india)

{

DataTable dt_currency = new DataTable();

DataRow dr;

try

{

string sql = "";

if (!string.IsNullOrEmpty(india))

sql = "select id,[currency] as name from Currency where country='" + india + "' order by [currency]";

else

sql = "select id,[currency] as name from Currency where country<>'India' order by [currency]";

dt_currency = ut.SelectRecords(sql);

if (dt_currency.Rows.Count > 0)

{

dr = dt_currency.NewRow();

dr.ItemArray = new object[] { 0, "----Select----" };

dt_currency.Rows.InsertAt(dr, 0);

ddlcurrency.DataValueField = "id";

ddlcurrency.DataTextField = "name";

ddlcurrency.DataSource = dt_currency;

ddlcurrency.DataBind();

}

catch (Exception ex)

{

}

private void populateinvoicetype()

{

DataTable dt_invoice = new DataTable();

DataRow dr;

try

{

dt_invoice = ut.SelectRecords("select id,name from invoicetype");

if(dt_invoice.Rows.Count>0)

{

dr = dt_invoice.NewRow();

dr.ItemArray = new object[] { 0, "----Select----" };

dt_invoice.Rows.InsertAt(dr, 0);

ddlinvoicetype.DataValueField = "id";

ddlinvoicetype.DataTextField = "name";

ddlinvoicetype.DataSource = dt_invoice;

ddlinvoicetype.DataBind();

}

catch (Exception ex)

{

}

private void populatecountry()

{

DataTable dt_invoice = new DataTable();

DataRow dr;

try

{

dt_invoice = ut.SelectRecords("select CountryID as id,CountryName as name from Country");

if (dt_invoice.Rows.Count > 0)

{

dr = dt_invoice.NewRow();

dr.ItemArray = new object[] { 0, "----Select----" };

dt_invoice.Rows.InsertAt(dr, 0);

ddlcoutryorgoods.DataValueField = "id";

ddlcoutryorgoods.DataTextField = "name";

ddlcoutryorgoods.DataSource = dt_invoice;

ddlcoutryorgoods.DataBind();

ddlcountryfinaldest.DataValueField = "id";

ddlcountryfinaldest.DataTextField = "name";

ddlcountryfinaldest.DataSource = dt_invoice;

ddlcountryfinaldest.DataBind();

}

catch (Exception ex)

{

}

private void populatetransportmode()

{

DataTable dt_transport = new DataTable();

DataRow dr;

try

{

dt_transport = ut.SelectRecords("select id,name from transportmode order by name");

if (dt_transport.Rows.Count > 0)

{

dr = dt_transport.NewRow();

dr.ItemArray = new object[] { 0, "----Select----" };

dt_transport.Rows.InsertAt(dr, 0);

ddltransportmode.DataValueField = "id";

ddltransportmode.DataTextField = "name";

ddltransportmode.DataSource = dt_transport;

ddltransportmode.DataBind();

}

catch (Exception ex)

{

}

private void populatebankdetails()

{

DataTable dt_invoice = new DataTable();

DataRow dr;

try

{

dt_invoice = ut.SelectRecords("select id,bankname as name from bankdetails");

if (dt_invoice.Rows.Count > 0)

{

dr = dt_invoice.NewRow();

dr.ItemArray = new object[] { 0, "----Select----" };

dt_invoice.Rows.InsertAt(dr, 0);

ddlbankdetails.DataValueField = "id";

ddlbankdetails.DataTextField = "name";

ddlbankdetails.DataSource = dt_invoice;

ddlbankdetails.DataBind();

}

catch (Exception ex)

{

}

private void bindrecords()

{

DataTable dt = new DataTable();

try

{

string sql = @"select p.id,p.docno,format(p.docdt,'dd/MM/yyyy') as docdate

, c.name as custname,

p.place_of_lading,p.place_of_discharge,p.place_of_final_discharge,p.buyer_ord_no,p.amount,

cur.code as currency

from proforma_invoice p

inner

join customer c on c.custcode = p.cust_code

inner

join Currency cur on cur.id = p.currency_id";

dt = ut.SelectRecords(sql);

if (dt.Rows.Count > 0)

{

grvrecorddata.DataSource = dt;

grvrecorddata.DataBind();

grvrecorddata.HeaderRow.TableSection = TableRowSection.TableHeader;

}

else

{

grvrecorddata.DataSource = null;

grvrecorddata.DataBind();

grvrecorddata.HeaderRow.TableSection = TableRowSection.TableHeader;

}

catch (Exception ex)

{

}

finally

{ }

}

private void resetcontrol()

{

try

{

ddlinvoicetype.SelectedIndex = 0;

ddlcurrency.SelectedIndex = 0;

txtsgst.Text = "0";

txtcgst.Text = "0";

txtigst.Text = "0";

txtexporterrefno.Text = "";

txtotherrefno.Text = "";

txtconsignee.Text = "";

txtconsigneeaddress.Text = "";

txtbuyer.Text = "";

txtshippingaddress.Text = "";

txtbuyersorderno.Text = "";

txtbuyerorderdate.Text = "";

ddlcoutryorgoods.SelectedIndex = 0;

ddlcountryfinaldest.SelectedIndex = 0;

ddltransportmode.SelectedIndex = 0;

txtplacelading.Text = "";

txtplaceoffinaldischarge.Text = "0";

txtplceofdischarge.Text = "";

txttermsdeliverypayment.Text = "";

txtofferterm.Text = "";

txtadvancep.Text = "0";

ddlbankdetails.SelectedIndex = 0;

lblbankname.Text = "";

lblbranch.Text = "";

lblaccounno.Text = "";

lblswiftcode.Text = "";

Addname_FirstGridViewRow();

HiddenField1.Value = "";

}

catch (Exception ex)

{

}

finally

{ }

}

private void populatecurbank()

{

DataTable dt = new DataTable();

try

{

dt = ut.SelectRecords("select * from beneficiary_bank where curr_id='" + ddlcurrency.SelectedValue.ToString() + "' and bank_id='" + ddlbankdetails.SelectedValue.ToString() + "'");

if (dt.Rows.Count > 0)

{

lblbankname.Text = dt.Rows[0]["bankname"].ToString();

lblbranch.Text = "Brach : " + dt.Rows[0]["branch"].ToString();

lblaccounno.Text = dt.Rows[0]["acno"].ToString();

lblswiftcode.Text = dt.Rows[0]["swiftcode"].ToString();

}

else

{

lblbankname.Text = "No Record(s) found";

lblbranch.Text = "";

lblaccounno.Text = "";

lblswiftcode.Text = "";

}

catch (Exception ex)

{

throw;

}

private void editrecords(int id)

{

DataTable dt1 = new DataTable();

try

{

string sql = "select * from proforma_invoice where id=" + id;

dt1 = ut.SelectRecords(sql);

if(dt1.Rows.Count>0)

{

ddlinvoicetype.SelectedValue = dt1.Rows[0]["invoice_type"].ToString();

invoicetypeselectedindex();

ddlcurrency.SelectedValue = dt1.Rows[0]["currency_id"].ToString();

txtsgst.Text = dt1.Rows[0]["sgst"].ToString();

txtcgst.Text = dt1.Rows[0]["cgst"].ToString();

txtigst.Text = dt1.Rows[0]["igst"].ToString();

txtexporterrefno.Text = dt1.Rows[0]["exporter_refno"].ToString();

txtotherrefno.Text = dt1.Rows[0]["oth_refno"].ToString();

DataTable dt_cust = new DataTable();

dt_cust = ut.SelectRecords("select * from customer where custcode='" + dt1.Rows[0]["cust_code"].ToString() + "'");

txtconsignee.Text = dt_cust.Rows[0]["name"].ToString();

txtconsigneeaddress.Text = dt_cust.Rows[0]["addr"].ToString();

DataTable dt_yr = new DataTable();

dt_yr = ut.SelectRecords("select * from customershippingaddress where custcode='" + dt1.Rows[0]["cust_code"].ToString() + "'");

txtshippingaddress.Text = dt_yr.Rows[0]["shipaddress"].ToString();

txtbuyer.Text = dt1.Rows[0]["buyer_othr_consignee"].ToString();

txtbuyersorderno.Text = dt1.Rows[0]["buyer_ord_no"].ToString();

txtbuyerorderdate.Text = dt1.Rows[0]["orddt"].ToString();

ddlcoutryorgoods.SelectedValue = dt1.Rows[0]["ctry_origin_id"].ToString();

ddlcountryfinaldest.SelectedValue = dt1.Rows[0]["ctry_shipping_id"].ToString();

ddltransportmode.SelectedValue = dt1.Rows[0]["Freight_mode"].ToString();//Transport Mode

txtplacelading.Text = dt1.Rows[0]["place_of_lading"].ToString();

txtplaceoffinaldischarge.Text = dt1.Rows[0]["place_of_final_discharge"].ToString();

txtplceofdischarge.Text = dt1.Rows[0]["place_of_discharge"].ToString();

txttermsdeliverypayment.Text = dt1.Rows[0]["delv_pmt_terms"].ToString();

txtofferterm.Text = dt1.Rows[0]["offer_terms"].ToString();

txtadvancep.Text = dt1.Rows[0]["advance_pct"].ToString();//

ddlbankdetails.SelectedValue = dt1.Rows[0]["bankid"].ToString();//

populatecurbank();

DataTable dt = new DataTable();

DataColumn column = new DataColumn();

column.DataType = System.Type.GetType("System.Int32");

column.AutoIncrement = true;

column.AutoIncrementSeed = 1;

column.AutoIncrementStep = 1;

column.ColumnName = "id";

dt.Columns.Add(column);

dt.Columns.Add(new DataColumn("desc", typeof(string)));

dt.Columns.Add(new DataColumn("qty", typeof(decimal)));

dt.Columns.Add(new DataColumn("unit", typeof(int)));

dt.Columns.Add(new DataColumn("rate", typeof(string)));

DataTable dt_new = new DataTable();

dt_new = ut.SelectRecords("select product_name as [desc], qty, uom as unit, rate from proforma_invoice_dtl where docno = '"+ dt1.Rows[0]["docno"].ToString() + "'");

for (int j = 0; j < dt_new.Rows.Count; j++)

{

string desc = dt_new.Rows[j]["desc"].ToString();

string qty = dt_new.Rows[j]["qty"].ToString();

string unit = dt_new.Rows[j]["unit"].ToString();

string rate = dt_new.Rows[j]["rate"].ToString();

DataRow dr = dt.NewRow();

dr["desc"] = desc;

dr["qty"] = qty;

dr["unit"] = unit;

dr["rate"] = rate;

dt.Rows.Add(dr);

}

ViewState["AddnameCurrentTable"] = dt;

grvaddname.DataSource = dt;

grvaddname.DataBind();

DropDownList ddl = (DropDownList)grvaddname.Rows[0].Cells[4].FindControl("ddlproductunit");

FillDropDownList(ddl);

AddName_SetPreviousData();

}

catch (Exception ex)

{

//throw;

}

finally

{

}

protected void lnkprint_Click(object sender, EventArgs e)

{

LinkButton lnkdit = (LinkButton)sender;

GridViewRow Grow = (GridViewRow)lnkdit.NamingContainer;

// Label lblid = (Label)Grow.FindControl("lblid");

Label DOC_NO = (Label)Grow.FindControl("lbldocno");

//Response.Redirect("proformainvoice_print.aspx?id="+lblid.Text);

Response.Redirect("proformainvoice_print.aspx?docno=" + DOC_NO.Text);

}

#endregion

#region event-----------------------------------------------------------------

protected void btnsubmit_Click(object sender, EventArgs e)

{

string strErrorMsg ="";

decimal tot_qty = 0;

decimal tot_amnt = 0;

decimal due_amnt = 0;

try

{

if (connection.State == ConnectionState.Open)

connection.Close();

connection.Open();

DataTable dt_pi_details = new DataTable();

dt_pi_details.Columns.Add("product_name");

dt_pi_details.Columns.Add("qty");

dt_pi_details.Columns.Add("rate");

dt_pi_details.Columns.Add("amount");

dt_pi_details.Columns.Add("uom");

for (int j = 0; j < grvaddname.Rows.Count; j++)

{

TextBox txtdecs = (TextBox)grvaddname.Rows[j].FindControl("txtdecs");

TextBox txtproductqty = (TextBox)grvaddname.Rows[j].FindControl("txtproductqty");

DropDownList ddlproductunit = (DropDownList)grvaddname.Rows[j].FindControl("ddlproductunit");

TextBox txtrate = (TextBox)grvaddname.Rows[j].FindControl("txtrate");

decimal d1=0; decimal d2 = 0;

if (decimal.TryParse(txtproductqty.Text, out d))

d1 = Convert.ToDecimal(txtproductqty.Text);

if (decimal.TryParse(txtrate.Text, out d))

d2 = Convert.ToDecimal(txtrate.Text);

decimal d3 = d1 * d2;

if ((!string.IsNullOrEmpty(txtdecs.Text.Trim())) && d1 != 0 && d2 != 0)

{

DataRow dr4 = dt_pi_details.NewRow();

dr4["product_name"] = txtdecs.Text;

dr4["qty"] = txtproductqty.Text;

dr4["rate"] = txtrate.Text;

dr4["amount"] = d3.ToString();

dr4["uom"] = ddlproductunit.Text;

dt_pi_details.Rows.Add(dr4);

}

tot_qty += Convert.ToDecimal(txtproductqty.Text);

tot_amnt += Convert.ToDecimal(d3);

}

due_amnt = tot_amnt - Convert.ToDecimal(txtadvancep.Text.Trim());

if (HiddenField1.Value == "")

{

SqlCommand cmd = new SqlCommand("Proforma_Invoice_Insert_Final", connection);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter sqlParam = new SqlParameter();

cmd.Parameters.AddWithValue("@doc_date", DateTime.Now.ToString("MM/dd/yyyy"));

cmd.Parameters.AddWithValue("@invoice_type", ddlinvoicetype.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@buyer_ord_no", txtbuyersorderno.Text.Trim());

cmd.Parameters.AddWithValue("@buyer_othr_consignee", txtbuyer.Text.Trim());

cmd.Parameters.AddWithValue("@orddt", ut.ConvertDate(txtbuyerorderdate.Text.Trim())); // date

cmd.Parameters.AddWithValue("@oth_refno", txtotherrefno.Text.Trim());

cmd.Parameters.AddWithValue("@exporter_refno", txtexporterrefno.Text.Trim());

cmd.Parameters.AddWithValue("@cust_name", txtconsignee.Text);

cmd.Parameters.AddWithValue("@cust_addr", txtconsigneeaddress.Text);

cmd.Parameters.AddWithValue("@ship_addr", txtshippingaddress.Text);

cmd.Parameters.AddWithValue("@Freight_mode", ddltransportmode.SelectedValue);

cmd.Parameters.AddWithValue("@freight_details", "");

cmd.Parameters.AddWithValue("@place_of_lading", txtplacelading.Text);

cmd.Parameters.AddWithValue("@place_of_discharge", txtplceofdischarge.Text);

cmd.Parameters.AddWithValue("@place_of_final_discharge", txtplaceoffinaldischarge.Text);

cmd.Parameters.AddWithValue("@delv_pmt_terms", txttermsdeliverypayment.Text);

cmd.Parameters.AddWithValue("@items", dt_pi_details.Rows.Count);

cmd.Parameters.AddWithValue("@qty", tot_qty);

cmd.Parameters.AddWithValue("@currency_id", ddlcurrency.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@amount", tot_amnt);

cmd.Parameters.AddWithValue("@bank_id", ddlbankdetails.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@ctry_origin_id", ddlcoutryorgoods.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@ctry_shipping_id", ddlcountryfinaldest.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@benificiary_bankid", ddlbankdetails.SelectedValue.ToString());

cmd.Parameters.AddWithValue("@advance_percent", txtadvancep.Text.ToString());

cmd.Parameters.AddWithValue("@offer_terms", txtofferterm.Text.ToString());

cmd.Parameters.AddWithValue("@cgst", txtcgst.Text.Trim());

cmd.Parameters.AddWithValue("@sgst", txtsgst.Text.Trim());

cmd.Parameters.AddWithValue("@igst", txtigst.Text.Trim());

cmd.Parameters.AddWithValue("@advance_amount", Convert.ToDecimal(txtadvancep.Text.Trim()));

cmd.Parameters.AddWithValue("@due_amount", due_amnt);

cmd.Parameters.AddWithValue("@typ_PI_details", dt_pi_details);

cmd.Parameters.AddWithValue("@note", "");

cmd.Parameters.AddWithValue("@cbu", "");

cmd.Parameters.Add("@ret", SqlDbType.VarChar, 250);

cmd.Parameters["@ret"].Direction = ParameterDirection.Output;

cmd.Parameters.Add("@out_doc_no", SqlDbType.VarChar, 250);

cmd.Parameters["@out_doc_no"].Direction = ParameterDirection.Output;

int i = cmd.ExecuteNonQuery();

strErrorMsg = cmd.Parameters["@ret"].Value.ToString();

if (strErrorMsg.ToUpper() == "S")

{

//pnladd.Visible = false;

//pnlview.Visible = true;

bindrecords();

resetcontrol();

}

else

{

SqlCommand cmd = new SqlCommand("Proforma_Invoice_Update", connection);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter sqlParam = new SqlParameter();

cmd.Parameters.AddWithValue("@docno", HiddenField1.Value);