public static ResGst1List GetB2CSGst1(ReqGst1 reqGst1, String spName)
{
ResGst1List resGst1List = new ResGst1List();
List <ResGst1> _resGstList = new List <ResGst1>();
DataSet dsB2B = new DataSet();
DataTable dtInvoice = new DataTable();
DataTable dtInvoiceItemRate = new DataTable();
dtInvoice.Columns.AddRange(new DataColumn[] {
new DataColumn("InvoiceId", typeof(String)),
new DataColumn("Type", typeof(String)),
new DataColumn("PlaceOfSupply", typeof(String)),
new DataColumn("Cess", typeof(String)),
new DataColumn("EcommerceGstinNo", typeof(String))
});
var dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("InvoiceId", typeof(String)),
new DataColumn("TaxableValue", typeof(String)),
new DataColumn("Rate", typeof(String))
});
try
{
using (SqlConnection con = new SqlConnection(StaticConst.connectionString))
{
using (SqlCommand cmd = new SqlCommand(spName, con))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add(new SqlParameter("@UserId", reqGst1.UserId));
cmd.Parameters.Add(new SqlParameter("@Year", reqGst1.Year));
cmd.Parameters.Add(new SqlParameter("@Month", reqGst1.Month));
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsB2B);
dtInvoice = dsB2B.Tables[0];
dtInvoiceItemRate = dsB2B.Tables[1];
resGst1List.InvoiceCount = Convert.ToString(dtInvoice.Rows.Count);
for (int i = 0; i < dtInvoice.Rows.Count; i++)
{
dt.Clear();
string invoiceId = Convert.ToString(dtInvoice.Rows[i]["InvoiceId"]);
var dr = dtInvoiceItemRate.Select("InvoiceId =" + invoiceId);// fetch data from Datatable with current looping invoiceId
for (int k = 0; k < dr.Count(); k++)
{
dt.Rows.Add(dr[k].ItemArray);
}
for (int j = 0; j < dt.Rows.Count; j++)
{
ResGst1 resGst1 = new ResGst1();
resGst1.InvoiceId = Convert.ToString(dtInvoice.Rows[i]["InvoiceId"]);
resGst1.Cess = Convert.ToString(dtInvoice.Rows[i]["Cess"]);
resGst1.EcommerceGstinNo = Convert.ToString(dtInvoice.Rows[i]["EcommerceGstinNo"]);
resGst1.Type_E_OE = Convert.ToString(dtInvoice.Rows[i]["Type"]);
resGst1.PlaceOfSupply = Convert.ToString(dtInvoice.Rows[i]["PlaceOfSupply"]);
resGst1.TaxableValue = Convert.ToString(dt.Rows[j]["TaxableValue"]);
resGst1.Rate = Convert.ToString(dt.Rows[j]["Rate"]);
_resGstList.Add(resGst1);
}
}
if (dtInvoice.Rows.Count > 0 && dtInvoiceItemRate.Rows.Count > 0)
{
resGst1List.resGst1 = _resGstList;
resGst1List.ResponseCode = 0;
resGst1List.ResponseMessage = "B2CS detail Fetched Sucessfully";
}
else
{
resGst1List.ResponseCode = 0;
resGst1List.ResponseMessage = "No records found for B2CS";
}
}
}
}
}
catch (Exception ex)
{
}
return(resGst1List);
}
public static ResGst1List GetB2BGst1(ReqGst1 reqGst1, String spName)
{
ResGst1List resGst1List = new ResGst1List();
List <ResGst1> _resGstList = new List <ResGst1>();
DataSet dsB2B = new DataSet();
DataTable dtInvoice = new DataTable();
DataTable dtInvoiceItemRate = new DataTable();
dtInvoice.Columns.AddRange(new DataColumn[] {
new DataColumn("GstinNo", typeof(String)),
new DataColumn("InvoiceId", typeof(String)),
new DataColumn("InvoiceNo", typeof(String)),
new DataColumn("InvoiceDate", typeof(String)),
new DataColumn("TotalAmount", typeof(decimal)),
new DataColumn("PlaceOfSupply", typeof(String)),
new DataColumn("IsReverseCharge", typeof(String)),
new DataColumn("InvoiceType", typeof(String)),
new DataColumn("EcommerceGstinNo", typeof(String)),
new DataColumn("Cess", typeof(String)),
});
var dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("InvoiceId", typeof(String)),
new DataColumn("TaxableValue", typeof(String)),
new DataColumn("Rate", typeof(String))
});
try
{
using (SqlConnection con = new SqlConnection(StaticConst.connectionString))
{
using (SqlCommand cmd = new SqlCommand(spName, con))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add(new SqlParameter("@UserId", reqGst1.UserId));
cmd.Parameters.Add(new SqlParameter("@Year", reqGst1.Year));
cmd.Parameters.Add(new SqlParameter("@Month", reqGst1.Month));
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dsB2B);
dtInvoice = dsB2B.Tables[0];
dtInvoiceItemRate = dsB2B.Tables[1];
//var a = (decimal)dtInvoice.Compute("Sum(TotalAmount)", "");
// var sum = dtInvoice.AsEnumerable().ToList().Sum(r => r.Field<decimal>("TotalAmount"));
//int omarks = dtInvoice.AsEnumerable().Select(dr => (int)dr["TotalAmount"]).Sum();
//tbobtained.Text = omarks.ToString();
resGst1List.InvoiceCount = Convert.ToString(dtInvoice.Rows.Count);
for (int i = 0; i < dtInvoice.Rows.Count; i++)
{
//var tempdt = dtInvoice;
//dt = tempdt.Clone();
dt.Clear();
string invoiceId = Convert.ToString(dtInvoice.Rows[i]["InvoiceId"]);
var dr = dtInvoiceItemRate.Select("InvoiceId =" + invoiceId);// fetch data from Datatable with current looping invoiceId
//dt.Rows.Add(dtInvoice.Select("InvoiceId =" + invoiceId));
for (int k = 0; k < dr.Count(); k++)
{
dt.Rows.Add(dr[k].ItemArray);
}
for (int j = 0; j < dt.Rows.Count; j++)
{
ResGst1 resGst1 = new ResGst1();
resGst1.InvoiceId = Convert.ToString(dtInvoice.Rows[i]["invoiceId"]);
resGst1.Cess = Convert.ToString(dtInvoice.Rows[i]["Cess"]);
resGst1.EcommerceGstinNo = Convert.ToString(dtInvoice.Rows[i]["EcommerceGstinNo"]);
resGst1.GstinNo = Convert.ToString(dtInvoice.Rows[i]["GstinNo"]);
resGst1.InvoiceDate = Convert.ToString(dtInvoice.Rows[i]["InvoiceDate"]);
resGst1.InvoiceNo = Convert.ToString(dtInvoice.Rows[i]["InvoiceNo"]);
resGst1.InvoiceType = Convert.ToString(dtInvoice.Rows[i]["InvoiceType"]);
if (!(dtInvoice.Rows[i]["IsReverseCharge"] is DBNull))
{
resGst1.IsReverseCharge = Convert.ToBoolean(dtInvoice.Rows[i]["IsReverseCharge"]);
}
resGst1.PlaceOfSupply = Convert.ToString(dtInvoice.Rows[i]["PlaceOfSupply"]);
resGst1.TotalAmount = Convert.ToDecimal(dtInvoice.Rows[i]["TotalAmount"]);
resGst1.TaxableValue = Convert.ToString(dt.Rows[j]["TaxableValue"]);
resGst1.Rate = Convert.ToString(dt.Rows[j]["Rate"]);
_resGstList.Add(resGst1);
}
}
if (dtInvoice.Rows.Count > 0 && dtInvoiceItemRate.Rows.Count > 0)
{
resGst1List.resGst1 = _resGstList;
resGst1List.ResponseCode = 0;
resGst1List.ResponseMessage = "B2B detail Fetched Sucessfully";
}
else
{
resGst1List.ResponseCode = 0;
resGst1List.ResponseMessage = "No records found for B2B";
}
}
}
}
}
catch (Exception ex)
{
}
return(resGst1List);
}
