protected void btngenrateBill_Click(object sender, EventArgs e)
{
string flag = string.Empty;
if (dpReportType.SelectedItem.Value == "1")
{
flag = "Staff";
}
if (dpReportType.SelectedItem.Value == "2")
{
flag = "Agent";
}
double totalamt = 0;
string result = string.Empty;
DS = marketingdata.ItemWiseStaffSalesSummarybyDate((Convert.ToDateTime(txtStartDate.Text)).ToString("dd-MM-yyyy"), (Convert.ToDateTime(txtEndDate.Text)).ToString("dd-MM-yyyy"), Convert.ToInt32(dpEmployee.SelectedItem.Value), flag);
if (!Comman.Comman.IsDataSetEmpty(DS))
{
StringBuilder sb = new StringBuilder();
sb.Append("<style type='text / css'>");
sb.Append(".tg { border - collapse:collapse; border - spacing:0; border: none; }");
sb.Append(".tg .tg-yw4l{vertical-align:top}");
sb.Append(".tg .tg-baqh{text-align:center;vertical-align:top}");
sb.Append("</style>");
//sb.Append("<table class='tg style1' style='page-break-inside:avoid; align:center;'>");
sb.Append("<table class='tg style1' style=' position:relative;align:center;'>");
sb.Append("<colgroup>");
sb.Append("<col style = 'width:120px'>");
sb.Append("<col style = 'width:160px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("</colgroup>");
sb.Append("<tr>");
sb.Append("<th class='tg-yw4l' rowspan='2'>");
sb.Append("<img src='/Theme/img/logo1.png' class='img-circle' alt='Logo' width='50px' hight='50px'>");
sb.Append("</th>");
sb.Append("<th class='tg-baqh' colspan='6' style='text-align:center'>");
sb.Append("<b>Nanjil Integrated Dairy Development, Mulagumoodu, K.K.Dt.</b>");
sb.Append("</th>");
sb.Append("<th class='tg-yw4l' style='text-align:right'>");
sb.Append("GSTIN: 33AAECN2463R1Z2<br>");
sb.Append("</th>");
sb.Append("</tr>");
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:center'>");
if (dpReportType.SelectedItem.Value == "1")
{
sb.Append("<b><u>Itemwise Staff Sales Summary </u><b> <br/>");
}
if (dpReportType.SelectedItem.Value == "2")
{
sb.Append("<b><u>Itemwise Agent Sales Summary </u><b> <br/>");
}
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append("PH:248370,248605");
sb.Append("</td>");
sb.Append("<tr>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:left'>");
sb.Append("Start Date:" + Convert.ToDateTime(txtStartDate.Text).ToString("dd-MM-yyyy"));
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:right'>");
sb.Append("End Date:" + Convert.ToDateTime(txtEndDate.Text).ToString("dd-MM-yyyy"));
sb.Append("</td>");
sb.Append("<tr style='border-bottom:2px solid'>");
sb.Append("<td class='tg-yw4l' colspan='3' style='text-align:left'>");
if (dpReportType.SelectedItem.Value == "1")
{
if (Convert.ToInt32(dpEmployee.SelectedItem.Value) == 0)
{
sb.Append("Employee : " + "All");
}
else
{
sb.Append("Employee : " + dpEmployee.SelectedItem.Text.ToString());
}
}
if (dpReportType.SelectedItem.Value == "2")
{
if (Convert.ToInt32(dpEmployee.SelectedItem.Value) == 0)
{
sb.Append("Agent : " + "All");
}
else
{
sb.Append("Agent : " + dpEmployee.SelectedItem.Text.ToString());
}
}
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='3' style='text-align:middle'>");
//sb.Append("Route : " + "All");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:right'>");
sb.Append("Date : " + DateTime.Now.ToString("dd-MM-yyyy"));
sb.Append("</td>");
sb.Append("</tr>");
sb.Append("<tr style='border-bottom:1px solid'style='page-break-inside:avoid; align:center;'> <td colspan = '9'></td> </tr>");
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append("<b> </b> ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append("<b>ITEM</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append("<b>Rate</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append("<b>Quantity</b>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("<b>Unit Type</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append("<b>Amount</b>");
sb.Append("</td>");
sb.Append("</tr>");
foreach (DataRow row2 in DS.Tables[1].Rows)
{
int count = 0;
double totalquantity = 0;
double quantity = 0;
double amounts = 0;
double totalamounts = 0;
foreach (DataRow row in DS.Tables[0].Rows)
{
if (row2["TypeID"].ToString() == row["TypeID"].ToString())
{
count = count + 1;
sb.Append("<tr>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append("");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append(row["ITEM"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append((Convert.ToDecimal(row["Rate"]).ToString("#.00")));
sb.Append("</td>");
sb.Append("<td>");
sb.Append("");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
quantity = Convert.ToDouble(row["Quantity"]);
totalquantity += quantity;
sb.Append(quantity);
sb.Append("</td>");
sb.Append("<td>");
sb.Append(row["UnitType"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
amounts = Convert.ToDouble(row["Amount"]);
totalamounts += amounts;
sb.Append((Convert.ToDecimal(amounts).ToString("#.00")));
sb.Append("</td>");
sb.Append("</tr>");
}
else
{
}
}
sb.Append("<tr style='border-bottom:1px dotted'> <td colspan = '9'> </td> </tr>");
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append(row2["TypeID"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append(row2["TypeName"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='3' style='text-align:left'>");
sb.Append("Total");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append(totalquantity.ToString());
sb.Append("</td>");
double amt = 0;
amt = Convert.ToDouble(totalamounts);
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append((Convert.ToDecimal(amt).ToString("#.00")));
totalamt += amt;
sb.Append("</td>");
}
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' style='text-align:left'> ");
sb.Append("<b>" + "Total" + "</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append(" ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2'style='text-align:right'> ");
sb.Append(" ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append(" ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:right'> ");
sb.Append("<b>" + "Total Amount" + "</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append("<b>" + (Convert.ToDecimal(totalamt).ToString("#.00")) + "</b>");
sb.Append("</td>");
sb.Append("</tr>");
result = sb.ToString();
genratedBIll.Text = result;
Session["ctrl"] = pnlBill;
}
else
{
result = "Report not found";
genratedBIll.Text = result;
}
}
protected void btngenrateBill_Click(object sender, EventArgs e)
{
double totalamt = 0;
string result = string.Empty;
DS = marketingdata.ItemWiseStaffSalesSummarybyDate((Convert.ToDateTime(txtStartDate.Text)).ToString("dd-MM-yyyy"), (Convert.ToDateTime(txtEndDate.Text)).ToString("dd-MM-yyyy"), Convert.ToInt32(dpEmployee.SelectedItem.Value));
if (!Comman.Comman.IsDataSetEmpty(DS))
{
StringBuilder sb = new StringBuilder();
try
{
DS.Tables[1].PrimaryKey = new[] { DS.Tables[1].Columns["TypeID"] };
DS.Tables[1].PrimaryKey = new[] { DS.Tables[1].Columns["TypeName"] };
}
catch (Exception) { }
try
{
DS.Tables[3].PrimaryKey = new[] { DS.Tables[3].Columns["TypeID"] };
DS.Tables[3].PrimaryKey = new[] { DS.Tables[3].Columns["TypeName"] };
}
catch (Exception) { }
try
{
DS.Tables[1].Merge(DS.Tables[3], false, MissingSchemaAction.Add);
}
catch (Exception) { }
//--------for product quantity
try
{
DS.Tables[0].PrimaryKey = new[] { DS.Tables[0].Columns["TypeID"] };
DS.Tables[0].PrimaryKey = new[] { DS.Tables[0].Columns["ITEM"] };
DS.Tables[0].PrimaryKey = new[] { DS.Tables[0].Columns["Rate"] };
DS.Tables[0].PrimaryKey = new[] { DS.Tables[0].Columns["UnitType"] };
DS.Tables[0].PrimaryKey = new[] { DS.Tables[0].Columns["Quantity"] };
DS.Tables[0].PrimaryKey = new[] { DS.Tables[0].Columns["Amount"] };
}
catch (Exception) { }
try
{
DS.Tables[2].PrimaryKey = new[] { DS.Tables[2].Columns["TypeID"] };
DS.Tables[2].PrimaryKey = new[] { DS.Tables[2].Columns["ITEM"] };
DS.Tables[2].PrimaryKey = new[] { DS.Tables[2].Columns["Rate"] };
DS.Tables[2].PrimaryKey = new[] { DS.Tables[2].Columns["UnitType"] };
DS.Tables[2].PrimaryKey = new[] { DS.Tables[2].Columns["Quantity"] };
DS.Tables[2].PrimaryKey = new[] { DS.Tables[2].Columns["Amount"] };
}
catch (Exception) { }
try
{
DS.Tables[0].Merge(DS.Tables[2], false, MissingSchemaAction.Add);
}
catch (Exception) { }
try
{
var finaltable = (from row in DS.Tables[0].AsEnumerable()
let type = row.Field <int>("TypeID")
let item = row.Field <string>("ITEM")
let rate = row.Field <decimal>("Rate")
let unite = row.Field <string>("UnitType")
let qty = row.Field <double>("Quantity")
let amt = row.Field <double>("Amount")
group row by new { type, item, rate, unite } into grp
select new
{
TypeID = grp.Key.type,
ITEM = grp.Key.item,
Rate = grp.Key.rate,
UnitType = grp.Key.unite,
Quantity = grp.Sum(r => r.Field <double>("Quantity")),
Amount = grp.Sum(r => r.Field <double>("Amount")),
}).OrderBy(type => type.TypeID).ThenBy(item => item.ITEM).ToList();
DataTable dat = new DataTable();
dat.Columns.Add("TypeID");
dat.Columns.Add("ITEM");
dat.Columns.Add("Rate");
dat.Columns.Add("UnitType");
dat.Columns.Add("Quantity");
dat.Columns.Add("Amount");
DataRow setrows = null;
foreach (var rowObj in finaltable)
{
setrows = dat.NewRow();
dat.Rows.Add(rowObj.TypeID, rowObj.ITEM, rowObj.Rate, rowObj.UnitType, rowObj.Quantity, rowObj.Amount);
}
sb.Append("<style type='text / css'>");
sb.Append(".tg { border - collapse:collapse; border - spacing:0; border: none; }");
sb.Append(".tg .tg-yw4l{vertical-align:top}");
sb.Append(".tg .tg-baqh{text-align:center;vertical-align:top}");
sb.Append("</style>");
//sb.Append("<table class='tg style1' style='page-break-inside:avoid; align:center;'>");
sb.Append("<table class='tg style1' style=' position:relative;align:center;'>");
sb.Append("<colgroup>");
sb.Append("<col style = 'width:120px'>");
sb.Append("<col style = 'width:160px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("<col style = 'width:100px'>");
sb.Append("</colgroup>");
sb.Append("<tr>");
sb.Append("<th class='tg-yw4l' rowspan='2'>");
sb.Append("<img src='/Theme/img/logo1.png' class='img-circle' alt='Logo' width='50px' hight='50px'>");
sb.Append("</th>");
sb.Append("<th class='tg-baqh' colspan='6' style='text-align:center'>");
sb.Append("<u>Itemwise Staff Sales Summary </u> <br/>");
sb.Append("</th>");
sb.Append("<th class='tg-yw4l' style='text-align:right'>");
sb.Append("TIN:330761667331<br>");
sb.Append("</th>");
sb.Append("</tr>");
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:center'>");
sb.Append("<b>Nanjil Integrated Dairy Development, Mulagumoodu, K.K.Dt.</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append("PH:248370,248605");
sb.Append("</td>");
sb.Append("<tr>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:left'>");
sb.Append("Start Date:" + Convert.ToDateTime(txtStartDate.Text).ToString("dd-MM-yyyy"));
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:right'>");
sb.Append("End Date:" + Convert.ToDateTime(txtEndDate.Text).ToString("dd-MM-yyyy"));
sb.Append("</td>");
sb.Append("<tr style='border-bottom:2px solid'>");
sb.Append("<td class='tg-yw4l' colspan='3' style='text-align:left'>");
if (Convert.ToInt32(dpEmployee.SelectedItem.Value) == 0)
{
sb.Append("Employee : " + "All");
}
else
{
sb.Append("Employee : " + dpEmployee.SelectedItem.Text.ToString());
}
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='3' style='text-align:middle'>");
sb.Append("Route : " + "All");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='6' style='text-align:right'>");
sb.Append("Date : " + DateTime.Now.ToString("dd-MM-yyyy"));
sb.Append("</td>");
sb.Append("</tr>");
sb.Append("<tr style='border-bottom:1px solid'style='page-break-inside:avoid; align:center;'> <td colspan = '9'></td> </tr>");
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append("<b> </b> ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append("<b>ITEM</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append("<b>Rate</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append("<b>Quantity</b>");
sb.Append("</td>");
sb.Append("<td>");
sb.Append("<b>Unit Type</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append("<b>Amount</b>");
sb.Append("</td>");
sb.Append("</tr>");
foreach (DataRow row2 in DS.Tables[1].Rows)
{
int count = 0;
double totalquantity = 0;
double quantity = 0;
double amounts = 0;
double totalamounts = 0;
foreach (DataRow row in dat.Rows)
{
if (row2["TypeID"].ToString() == row["TypeID"].ToString())
{
count = count + 1;
sb.Append("<tr>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append("");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append(row["ITEM"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append((Convert.ToDecimal(row["Rate"]).ToString("#.00")));
sb.Append("</td>");
sb.Append("<td>");
sb.Append("");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
quantity = Convert.ToDouble(row["Quantity"]);
totalquantity += quantity;
sb.Append(quantity);
sb.Append("</td>");
sb.Append("<td>");
sb.Append(row["UnitType"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
amounts = Convert.ToDouble(row["Amount"]);
totalamounts += amounts;
sb.Append((Convert.ToDecimal(amounts).ToString("#.00")));
sb.Append("</td>");
sb.Append("</tr>");
}
else
{
}
}
sb.Append("<tr style='border-bottom:1px dotted'> <td colspan = '9'> </td> </tr>");
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append(row2["TypeID"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append(row2["TypeName"].ToString());
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='3' style='text-align:left'>");
sb.Append("Total");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:left'>");
sb.Append(totalquantity.ToString());
sb.Append("</td>");
double amt = 0;
amt = Convert.ToDouble(totalamounts);
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append((Convert.ToDecimal(amt).ToString("#.00")));
totalamt += amt;
sb.Append("</td>");
}
sb.Append("<tr style='border-bottom:1px solid'>");
sb.Append("<td class='tg-yw4l' style='text-align:left'> ");
sb.Append("<b>" + "Total" + "</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:left'>");
sb.Append(" ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2'style='text-align:right'> ");
sb.Append(" ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append(" ");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' colspan='2' style='text-align:right'> ");
sb.Append("<b>" + "Total Amount" + "</b>");
sb.Append("</td>");
sb.Append("<td class='tg-yw4l' style='text-align:right'>");
sb.Append("<b>" + (Convert.ToDecimal(totalamt).ToString("#.00")) + "</b>");
sb.Append("</td>");
sb.Append("</tr>");
result = sb.ToString();
genratedBIll.Text = result;
Session["ctrl"] = pnlBill;
}
catch (Exception ex)
{
string msg = ex.ToString();
result = " Report not found";
genratedBIll.Text = result;
}
}
else
{
result = "Report not found";
genratedBIll.Text = result;
}
}