protected void btnGenerate_Click(object sender, EventArgs e)
{
try
{
devdm = new VehicleDBMgr();
SalesDBManager vdm = new SalesDBManager();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txtTodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
cmd = new SqlCommand("SELECT productid, qty, doe, vehicleno, dieselcost FROM diesel_consumptiondetails WHERE (doe BETWEEN @d1 AND @d2) AND branchid=@branchid");
cmd.Parameters.Add("@d1", GetLowDate(fromdate));
cmd.Parameters.Add("@d2", GetLowDate(Todate));
cmd.Parameters.Add("@branchid", "2");
DataTable dttotal = vdm.SelectQuery(cmd).Tables[0];
grdReports.DataSource = dttotal;
grdReports.DataBind();
string title = "DieselReport From: " + fromdate.ToString() + " To: " + Todate.ToString();
Session["title"] = title;
Session["filename"] = "DieselReport";
Session["xportdata"] = dttotal;
pvisible.Visible = true;
//Session["quantity"] = quantity;
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
protected void btnSMS_Click(object sender, EventArgs e)
{
string MobNo = txtMobNo.Text;
if (MobNo.Length == 10)
{
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
cmd = new MySqlCommand("SELECT ROUND(SUM(tripsubdata.Qty), 2) AS Qty, productsdata.ProductName FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status FROM tripdata WHERE (AssignDate BETWEEN @d1 AND @d2) AND (Status <> 'C')) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripsubdata ON tripdat.Sno = tripsubdata.Tripdata_sno INNER JOIN (SELECT branch_sno, product_sno, unitprice, flag, userdata_sno, DTarget, WTarget, MTarget, BranchQty, LeakQty, Rank FROM branchproducts WHERE (branch_sno = @branch)) brnchprdt ON tripsubdata.ProductId = brnchprdt.product_sno INNER JOIN productsdata ON brnchprdt.product_sno = productsdata.sno WHERE (dispatch.Branch_Id = @branch) GROUP BY productsdata.sno ORDER BY brnchprdt.Rank");
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtTotalDespatch = vdm.SelectQuery(cmd).Tables[0];
double TotalQty = 0;
string ProductName = "";
if (dtTotalDespatch.Rows.Count > 0)
{
foreach (DataRow dr in dtTotalDespatch.Rows)
{
double unitQty = 0;
double.TryParse(dr["Qty"].ToString(), out unitQty);
ProductName += dr["ProductName"].ToString() + "->" + Math.Round(unitQty, 2) + ";" + "\r\n";
TotalQty += Math.Round(unitQty, 2);
}
}
string Date = DateTime.Now.ToString("dd/MM/yyyy");
WebClient client = new WebClient();
string DispatchName = "SRIKALAHASTHI";
string baseurl = "http://103.16.101.52:8080/sendsms/bulksms?username=kapd-vyshnavi&password=vysavi&type=0&dlr=1&destination=" + MobNo + "&source=VYSNAVI&message=%20" + DispatchName + "%20,%20 + Despatch%20For%20" + ProductName + "TotalQty ->" + TotalQty + "";
Stream data = client.OpenRead(baseurl);
StreamReader reader = new StreamReader(data);
string ResponseID = reader.ReadToEnd();
data.Close();
reader.Close();
cmd = new MySqlCommand("SELECT ROUND(SUM(tripsubdata.Qty), 2) AS Qty, products_subcategory.SubCatName, products_category.Categoryname, products_category.sno AS categorysno, products_subcategory.sno FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status FROM tripdata WHERE (AssignDate BETWEEN @d1 AND @d2) AND (Status <> 'C')) tripdat ON triproutes.Tripdata_sno = tripdat.Sno INNER JOIN tripsubdata ON tripdat.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) GROUP BY categorysno, products_subcategory.sno ORDER BY categorysno");
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtTotalDespatch_subcategorywise = vdm.SelectQuery(cmd).Tables[0];
double SubCategoryTotalQty = 0;
string subcategoryName = "";
if (dtTotalDespatch_subcategorywise.Rows.Count > 0)
{
foreach (DataRow dr in dtTotalDespatch_subcategorywise.Rows)
{
double unitQty = 0;
double.TryParse(dr["Qty"].ToString(), out unitQty);
if (dr["categorysno"].ToString() == "10")
{
subcategoryName += dr["SubCatName"].ToString() + "CURD" + "->" + Math.Round(unitQty, 2) + ";" + "\r\n";
}
else
{
subcategoryName += dr["SubCatName"].ToString() + "->" + Math.Round(unitQty, 2) + ";" + "\r\n";
}
SubCategoryTotalQty += Math.Round(unitQty, 2);
}
}
MobNo = "9092691491";
WebClient client1 = new WebClient();
string baseurl1 = "http://103.16.101.52:8080/sendsms/bulksms?username=kapd-vyshnavi&password=vysavi&type=0&dlr=1&destination=" + MobNo + "&source=VYSNAVI&message=%20" + DispatchName + "%20,%20 +Total Despatch" + subcategoryName + "TotalQty ->" + SubCategoryTotalQty + "";
Stream data1 = client.OpenRead(baseurl1);
StreamReader reader1 = new StreamReader(data1);
string ResponseID1 = reader1.ReadToEnd();
data1.Close();
reader1.Close();
lblmsg.Text = "Message Sent Successfully";
txtMobNo.Text = "";
}
else
{
lblmsg.Text = "Please Enter 10 digit Number";
}
}
void Biproductsreport()
{
try
{
lblmsg.Text = "";
Report = new DataTable();
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
//cmd = new MySqlCommand("SELECT tripdata.Sno, tripsubdata.Qty, productsdata.ProductName,tripdata.I_Date, tripdata.VehicleNo,tripdata.Status, dispatch.DispName, products_category.Categoryname FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)");
cmd = new MySqlCommand("SELECT tripdata.Sno,tripdata.Dcno, tripsubdata.Qty, productsdata.ProductName, tripdata.I_Date, tripdata.VehicleNo, tripdata.Status, dispatch.DispName, products_category.Categoryname FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) OR (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (branchdata.SalesOfficeID = @SOID)");
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
cmd.Parameters.AddWithValue("@SOID", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand(" SELECT products_category.Categoryname, products_subcategory.SubCatName,branchproducts.Rank, productsdata.ProductName FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) and (branchproducts.flag=@Flag) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, branchproducts.Rank, productsdata.ProductName, branchproducts.branch_sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN empmanage ON tripdata.DEmpId = empmanage.Sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (empmanage.Branch = @BranchID) AND (branchproducts.branch_sno = @Branch) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
//cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@Branch", Session["branch"].ToString());
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (produtstbl.Rows.Count > 0)
{
DataView view = new DataView(dtble);
//DataTable distinctproducts = view.ToTable(true, "ProductName");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("VehicleNo");
Report.Columns.Add("DC No");
Report.Columns.Add("DC Date");
Report.Columns.Add("Route Name");
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total Milk", typeof(Double));
Report.Columns.Add("Total Curd&BM", typeof(Double));
Report.Columns.Add("Total Lts", typeof(Double));
Report.Columns.Add("Issued Crates", typeof(Double));
Report.Columns.Add("Issued Cans", typeof(Double));
Report.Columns.Add("Total Amount", typeof(Double));
DataTable distincttable = view.ToTable(true, "DispName", "VehicleNo", "Sno", "Status", "I_Date");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
if (branch["Status"].ToString() == "C")
{
}
else
{
cmd = new MySqlCommand("SELECT invid, Qty FROM tripinvdata WHERE (Tripdata_sno = @tripid)");
cmd.Parameters.AddWithValue("@tripid", branch["Sno"].ToString());
DataTable dtissuedinv = vdm.SelectQuery(cmd).Tables[0];
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["VehicleNo"] = branch["VehicleNo"].ToString();
newrow["DC No"] = branch["Sno"].ToString();
newrow["Route Name"] = branch["DispName"].ToString();
string AssignDate = branch["I_Date"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate);
string ChangedTime = dtAssignDate.ToString("dd/MMM/yyyy");
newrow["DC Date"] = ChangedTime;
double total = 0;
double totalcurdandBM = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["Sno"].ToString() == dr["Sno"].ToString())
{
double AssignQty = 0;
double.TryParse(dr["Qty"].ToString(), out AssignQty);
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
}
newrow["Total Milk"] = total;
newrow["Total Curd&BM"] = totalcurdandBM;
newrow["Total Lts"] = total + totalcurdandBM;
double cans = 0;
foreach (DataRow drinv in dtissuedinv.Rows)
{
string invid = drinv["invid"].ToString();
if (invid == "2")
{
invid = "4";
}
if (invid == "3")
{
invid = "4";
}
if (invid == "1")
{
double issuedcrates = 0;
double.TryParse(drinv["Qty"].ToString(), out issuedcrates);
newrow["Issued Crates"] = issuedcrates;
}
if (invid == "4")
{
double issuedcans = 0;
double.TryParse(drinv["Qty"].ToString(), out issuedcans);
cans += issuedcans;
newrow["Issued Cans"] = cans;
}
}
Report.Rows.Add(newrow);
i++;
}
}
DataRow newvartical = Report.NewRow();
newvartical["Route Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
//Session["xportdata"] = Report;
}
else
{
lblmsg.Text = "No DC Found";
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
Report = new DataTable();
pnlHide.Visible = true;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
//cmd = new MySqlCommand("SELECT tripdata.Sno, tripsubdata.Qty, productsdata.ProductName,tripdata.I_Date, tripdata.VehicleNo,tripdata.Status, dispatch.DispName, products_category.Categoryname FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)");
cmd = new MySqlCommand("SELECT result.DispName, result.VehicleNo, result.Sno, tripsubdata.Qty, productsdata.sno, productsdata.tempsubcatsno, productsdata.ProductName, products_category.Categoryname, result.AssignDate, result.Status,result.I_Date, result.DCNo FROM (SELECT dispatch.DispName, tripdat.Sno, tripdat.VehicleNo, tripdat.AssignDate, tripdat.Status, tripdat.I_Date, tripdat.DCNo FROM dispatch INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status, VehicleNo, I_Date, DCNo FROM tripdata WHERE (AssignDate BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno WHERE (dispatch.Branch_Id = @branch) AND (dispatch.DispMode = 'SPL' OR dispatch.DispMode IS NULL) OR (dispatch.DispMode = 'SPL' OR dispatch.DispMode IS NULL) AND (branchdata.SalesOfficeID = @SOID)) result INNER JOIN tripsubdata ON result.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno ORDER BY result.DispName");
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
cmd.Parameters.AddWithValue("@SOID", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT result.DispName, result.VehicleNo, result.Sno, tripsubdata.Qty, productsdata.sno, productsdata.tempsubcatsno, productsdata.ProductName, products_category.Categoryname, result.AssignDate, result.Status,result.I_Date, result.DCNo FROM (SELECT dispatch.DispName, tripdat.Sno, tripdat.VehicleNo, tripdat.AssignDate, tripdat.Status, tripdat.I_Date, tripdat.DCNo FROM dispatch INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status, VehicleNo, I_Date, DCNo FROM tripdata WHERE (AssignDate BETWEEN @d1 AND @d2)) tripdat ON triproutes.Tripdata_sno = tripdat.Sno WHERE (dispatch.Branch_Id = @branch) AND (dispatch.DispMode <> 'SPL' OR dispatch.DispMode IS NOT NULL) OR (dispatch.DispMode <> 'SPL' OR dispatch.DispMode IS NOT NULL) AND (branchdata.SalesOfficeID = @SOID)) result INNER JOIN tripsubdata ON result.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno ORDER BY result.DispName");
//cmd.Parameters.AddWithValue("@branch", Session["branch"]);
//cmd.Parameters.AddWithValue("@SOID", Session["branch"]);
//cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
//cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
//DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
string bids = Session["branch"].ToString();
//cmd = new MySqlCommand(" SELECT products_category.Categoryname, products_subcategory.SubCatName,branchproducts.Rank, productsdata.ProductName FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) and (branchproducts.flag=@Flag) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.description AS SubCategoryName, products_subcategory.SubCatName, products_subcategory.tempsub_catsno AS SubCatSno, branchproducts.Rank, branchproducts.product_sno AS sno, productsdata.ProductName, productsdata.tempsubcatsno, branchproducts.branch_sno FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN branchproducts ON productsdata.sno = branchproducts.product_sno INNER JOIN empmanage ON tripdata.DEmpId = empmanage.Sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (empmanage.Branch = @BranchID) AND (branchproducts.branch_sno = @Branch) GROUP BY productsdata.ProductName ORDER BY productsdata.tempsubcatsno");
//cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@Branch", Session["branch"].ToString());
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
produtstbl1 = produtstbl;
if (produtstbl.Rows.Count > 0)
{
DataView view = new DataView(dtble);
//DataTable distinctproducts = view.ToTable(true, "ProductName");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Route Name");
int count = 0;
foreach (DataRow dr in produtstbl.Rows)
{
string catname = dr["Categoryname"].ToString();
if (catname == "MILK" || catname == "Curd Cups" || catname == "Curd Buckets" || catname == "CURD")
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
count++;
}
}
Report.Columns.Add("Total Milk", typeof(Double));
Report.Columns.Add("Total Curd&BM", typeof(Double));
Report.Columns.Add("Total Lts", typeof(Double));
Report.Columns.Add("Issued Crates", typeof(Double));
Report.Columns.Add("Issued Cans", typeof(Double));
Report.Columns.Add("Total Amount", typeof(Double));
dttempproducts = new DataTable();
dttempproducts.Columns.Add("ProductName");
dttempproducts.Columns.Add("SubCatSno").DataType = typeof(int);;
DataTable distincttable = view.ToTable(true, "DispName", "VehicleNo", "Sno", "Status", "I_Date");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
if (branch["Status"].ToString() == "C")
{
}
else
{
cmd = new MySqlCommand("SELECT invid, Qty FROM tripinvdata WHERE (Tripdata_sno = @tripid)");
cmd.Parameters.AddWithValue("@tripid", branch["Sno"].ToString());
DataTable dtissuedinv = vdm.SelectQuery(cmd).Tables[0];
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["Route Name"] = branch["DispName"].ToString();
string AssignDate = branch["I_Date"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate);
string ChangedTime = dtAssignDate.ToString("dd/MMM/yyyy");
double total = 0;
double totalcurdandBM = 0;
foreach (DataRow dr in dtble.Rows)
{
if (branch["Sno"].ToString() == dr["Sno"].ToString())
{
double assqty = 0;
double curdBm = 0;
double AssignQty = 0;
double.TryParse(dr["Qty"].ToString(), out AssignQty);
if (dr["Categoryname"].ToString() == "MILK")
{
newrow[dr["ProductName"].ToString()] = AssignQty;
double.TryParse(dr["Qty"].ToString(), out assqty);
total += assqty;
DataRow tempnewrow = dttempproducts.NewRow();
tempnewrow["ProductName"] = dr["ProductName"].ToString();
tempnewrow["SubCatSno"] = dr["tempsubcatsno"].ToString();
dttempproducts.Rows.Add(tempnewrow);
}
if (dr["Categoryname"].ToString() == "CURD")
{
newrow[dr["ProductName"].ToString()] = AssignQty;
double.TryParse(dr["Qty"].ToString(), out curdBm);
totalcurdandBM += curdBm;
DataRow tempnewrow = dttempproducts.NewRow();
tempnewrow["ProductName"] = dr["ProductName"].ToString();
tempnewrow["SubCatSno"] = dr["tempsubcatsno"].ToString();
dttempproducts.Rows.Add(tempnewrow);
}
//if (dr["Categoryname"].ToString() == "ButterMilk")
//{
// newrow[dr["ProductName"].ToString()] = AssignQty;
// double.TryParse(dr["Qty"].ToString(), out Buttermilk);
// totalcurdandBM += Buttermilk;
//}
}
}
newrow["Total Milk"] = total;
newrow["Total Curd&BM"] = totalcurdandBM;
newrow["Total Lts"] = total + totalcurdandBM;
double cans = 0;
foreach (DataRow drinv in dtissuedinv.Rows)
{
string invid = drinv["invid"].ToString();
if (invid == "2")
{
invid = "4";
}
if (invid == "3")
{
invid = "4";
}
if (invid == "1")
{
double issuedcrates = 0;
double.TryParse(drinv["Qty"].ToString(), out issuedcrates);
newrow["Issued Crates"] = issuedcrates;
}
if (invid == "4")
{
double issuedcans = 0;
double.TryParse(drinv["Qty"].ToString(), out issuedcans);
cans += issuedcans;
newrow["Issued Cans"] = cans;
}
}
Report.Rows.Add(newrow);
i++;
}
}
DataRow newvartical = Report.NewRow();
newvartical["Route Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
DataView SubCatview = new DataView(dttempproducts);
dtSubCatgory = SubCatview.ToTable(true, "SubCatSno");
DataView dv1 = dtSubCatgory.DefaultView;
dv1.Sort = "SubCatSno ASC";
dtSortedSubCategory = dv1.ToTable();
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
//Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No DC Found";
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
Report = new DataTable();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
//cmd = new MySqlCommand("SELECT SUM(tripsubdata.Qty) AS assignedqty,productsdata.ProductName, products_category.Categoryname,DATE_FORMAT(tripdata.AssignDate, '%d %b %y') as AssignDate FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) GROUP BY productsdata.ProductName, tripdata.AssignDate ORDER BY tripdata.AssignDate");
cmd = new MySqlCommand("SELECT result.AssignDate, SUM(tripsubdata.Qty) AS assignedqty, tripsubdata.ProductId, products_category.Categoryname,productsdata.ProductName FROM (SELECT dispatch.sno AS dissno, tripdat.Sno, tripdat.AssignDate FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, EmpId, DATE_FORMAT(AssignDate, '%d %b %y') AS AssignDate,Status FROM tripdata WHERE (AssignDate BETWEEN @d1 AND @d2) AND (Status <> 'C')) tripdat ON triproutes.Tripdata_sno = tripdat.Sno WHERE (dispatch.Branch_Id = @BranchID)) result INNER JOIN tripsubdata ON result.Sno = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno GROUP BY result.AssignDate, tripsubdata.ProductId");
//cmd = new MySqlCommand("SELECT SUM(tripsubdata.Qty) AS assignedqty, productsdata.ProductName, products_category.Categoryname, DATE_FORMAT(tripdata.AssignDate, '%d %b %y') AS AssignDate FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchdata ON dispatch.Branch_Id = branchdata.sno WHERE (((dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)) OR ((tripdata.AssignDate BETWEEN @d1 AND @d2) AND (branchdata.SalesOfficeID = @SOID))) GROUP BY productsdata.ProductName, products_category.Categoryname,AssignDate ORDER BY AssignDate");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
cmd.Parameters.AddWithValue("@SOID", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtble = vdm.SelectQuery(cmd).Tables[0];
// cmd = new MySqlCommand("SELECT SUM(tripinvdata.Qty) AS invassigned, invmaster.InvName, tripdata.AssignDate, invmaster.sno FROM tripdata INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno INNER JOIN triproutes ON tripinvdata.Tripdata_sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno WHERE (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (dispatch.Branch_Id = @branch) AND (tripdata.Status <> 'C') GROUP BY invmaster.sno, tripdata.I_Date ORDER BY tripdata.AssignDate");
cmd = new MySqlCommand("SELECT SUM(tripinvdata.Qty) AS invassigned, invmaster.InvName, tripdat.AssignDate, invmaster.sno FROM (SELECT Sno, EmpId, DATE_FORMAT(AssignDate, '%d %b %y') AS AssignDate, Status, Userdata_sno, Remarks, VehicleNo, RecieptNo, I_Date, DEmpId, ATripid, InvStatus, GPStatus, PlanStatus, DespatchStatus FROM tripdata WHERE (AssignDate BETWEEN @d1 AND @d2) AND (Status <> 'C') AND (BranchID = @branch)) tripdat INNER JOIN tripinvdata ON tripdat.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno GROUP BY tripinvdata.invid, tripdat.AssignDate ORDER BY tripdat.AssignDate");
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtinventry = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand(" SELECT products_category.Categoryname, products_subcategory.SubCatName,branchproducts.Rank, productsdata.ProductName FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchproducts.branch_sno = @BranchID) and (branchproducts.flag=@Flag) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
// cmd = new MySqlCommand("SELECT products_category.Categoryname, products_subcategory.SubCatName, branchproducts.Rank, productsdata.ProductName, tripdata.AssignDate FROM branchproducts INNER JOIN productsdata ON branchproducts.product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN tripsubdata ON branchproducts.product_sno = tripsubdata.ProductId INNER JOIN tripdata ON tripsubdata.Tripdata_sno = tripdata.Sno WHERE (tripdata.BranchID = @BranchID) AND (branchproducts.flag = @Flag) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) GROUP BY productsdata.ProductName ORDER BY branchproducts.Rank");
cmd.Parameters.AddWithValue("@Flag", "1");
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@Branch", Session["branch"].ToString());
DataTable produtstbl = vdm.SelectQuery(cmd).Tables[0];
if (produtstbl.Rows.Count > 0)
{
DataView view = new DataView(dtble);
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("DC Date");
foreach (DataRow dr in produtstbl.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total Milk", typeof(Double));
Report.Columns.Add("Total Curd&BM", typeof(Double));
Report.Columns.Add("Total Lts", typeof(Double));
Report.Columns.Add("Issued Crates", typeof(Double));
Report.Columns.Add("Issued Cans", typeof(Double));
Report.Columns.Add("Total Amount", typeof(Double));
DataTable distincttable = view.ToTable(true, "AssignDate");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
string AssignDate = branch["AssignDate"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate);
string ChangedTime = dtAssignDate.ToString("dd/MMM/yyyy");
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
newrow["DC Date"] = ChangedTime;
double total = 0;
double totalcurdandBM = 0;
double totalltrs = 0;
foreach (DataRow drdisp in dtble.Rows)
{
string AssignDate2 = drdisp["AssignDate"].ToString();
DateTime dtAssignDate2 = Convert.ToDateTime(AssignDate2);
string ChangedTime2 = dtAssignDate2.ToString("dd/MMM/yyyy");
if (ChangedTime == ChangedTime2)
{
double assqty = 0;
double curdBm = 0;
double Buttermilk = 0;
double AssignQty = 0;
double qtyvalue = 0;
double.TryParse(drdisp["assignedqty"].ToString(), out AssignQty);
newrow[drdisp["ProductName"].ToString()] = Math.Round(AssignQty, 2);
if (drdisp["Categoryname"].ToString() == "MILK")
{
double.TryParse(drdisp["assignedqty"].ToString(), out assqty);
total += assqty;
}
if (drdisp["Categoryname"].ToString() == "CURD")
{
double.TryParse(drdisp["assignedqty"].ToString(), out curdBm);
totalcurdandBM += curdBm;
}
if (drdisp["Categoryname"].ToString() == "ButterMilk")
{
double.TryParse(drdisp["assignedqty"].ToString(), out Buttermilk);
totalcurdandBM += Buttermilk;
}
}
}
double totcans = 0;
foreach (DataRow drinv in dtinventry.Rows)
{
string invAssignDate = drinv["AssignDate"].ToString();
DateTime dtinvAssignDate = Convert.ToDateTime(invAssignDate);
string invChangedTime2 = dtinvAssignDate.ToString("dd/MMM/yyyy");
if (ChangedTime == invChangedTime2)
{
if (drinv["sno"].ToString() == "1")
{
double issuedcrates = 0;
double.TryParse(drinv["invassigned"].ToString(), out issuedcrates);
newrow["Issued Crates"] = issuedcrates;
}
if (drinv["sno"].ToString() == "4")
{
double issuedcans = 0;
double.TryParse(drinv["invassigned"].ToString(), out issuedcans);
// newrow["Issued Cans"] = issuedcans;
totcans += issuedcans;
}
if (drinv["sno"].ToString() == "3")
{
double issuedcans = 0;
double.TryParse(drinv["invassigned"].ToString(), out issuedcans);
totcans += issuedcans;
}
if (drinv["sno"].ToString() == "5")
{
double issuedcans = 0;
double.TryParse(drinv["invassigned"].ToString(), out issuedcans);
totcans += issuedcans;
}
if (drinv["sno"].ToString() == "2")
{
double issuedcans = 0;
double.TryParse(drinv["invassigned"].ToString(), out issuedcans);
totcans += issuedcans;
}
}
}
newrow["Issued Cans"] = totcans;
newrow["Total Milk"] = Math.Round(total, 2);
newrow["Total Curd&BM"] = Math.Round(totalcurdandBM, 2);
newrow["Total Lts"] = Math.Round(total + totalcurdandBM, 2);
Report.Rows.Add(newrow);
i++;
}
foreach (var column in Report.Columns.Cast <DataColumn>().ToArray())
{
if (Report.AsEnumerable().All(dr => dr.IsNull(column)))
{
Report.Columns.Remove(column);
}
}
DataRow newvartical = Report.NewRow();
// newvartical["Route Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
}
}
Report.Rows.Add(newvartical);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
Session["xportdata"] = Report;
}
else
{
pnlHide.Visible = false;
lblmsg.Text = "No DC Found";
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
}
}
protected void btnSMS_Click(object sender, EventArgs e)
{
string MobNo = txtMobNo.Text;
if (MobNo.Length == 10)
{
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_fromDate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "TOTAL DC REPORT";
//cmd = new MySqlCommand("SELECT tripdata.Sno, tripsubdata.Qty, productsdata.ProductName, tripdata.VehicleNo, dispatch.DispName FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno WHERE (branchroutes.BranchID = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2)");
cmd = new MySqlCommand("SELECT ROUND(SUM(tripsubdata.Qty), 2) AS Qty, productsdata.ProductName FROM tripdata INNER JOIN tripsubdata ON tripdata.Sno = tripsubdata.Tripdata_sno INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN branchroutes ON dispatch.Route_id = branchroutes.Sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (dispatch.Branch_Id = @branch) AND (tripdata.AssignDate BETWEEN @d1 AND @d2) AND (triproutes.RouteID = @DispNo) GROUP BY productsdata.ProductName");
if (Session["salestype"].ToString() == "Plant")
{
cmd.Parameters.AddWithValue("@branch", Session["branch"]);
}
else
{
cmd.Parameters.AddWithValue("@branch", ddlSalesOffice.SelectedValue);
}
cmd.Parameters.AddWithValue("@DispNo", ddlDispName.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate));
DataTable dtTotalDespatch = vdm.SelectQuery(cmd).Tables[0];
double TotalQty = 0;
string ProductName = "";
if (dtTotalDespatch.Rows.Count > 0)
{
foreach (DataRow dr in dtTotalDespatch.Rows)
{
double unitQty = 0;
double.TryParse(dr["Qty"].ToString(), out unitQty);
ProductName += dr["ProductName"].ToString() + "->" + Math.Round(unitQty, 2) + ";";
TotalQty += Math.Round(unitQty, 2);
}
}
string Date = DateTime.Now.ToString("dd/MM/yyyy");
WebClient client = new WebClient();
string DispatchName = "";
if (Session["BranchName"] != null)
{
DispatchName = Session["BranchName"].ToString();
}
else
{
DispatchName = "SRIKALAHASTHI";
}
string baseurl = "http://103.16.101.52:8080/sendsms/bulksms?username=kapd-vyshnavi&password=vysavi&type=0&dlr=1&destination=" + MobNo + "&source=VYSNAVI&message=%20" + DispatchName + "%20,%20 + Despatch%20For%20" + ProductName + "TotalQty ->" + TotalQty + "";
Stream data = client.OpenRead(baseurl);
StreamReader reader = new StreamReader(data);
string ResponseID = reader.ReadToEnd();
data.Close();
reader.Close();
lblmsg.Text = "Message Sent Successfully";
txtMobNo.Text = "";
}
else
{
lblmsg.Text = "Please Enter 10 digit Number";
}
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
pvisible.Visible = true;
devdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtFromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txtTodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
int vehcleid = Convert.ToInt32(ddlagentname.SelectedItem.Value);
lblOppBal.Text = "";
lblCloBal.Text = "";
Session["title"] = "Diesel Report";
DataTable Report = new DataTable();
Report.Columns.Add("Sno");
Report.Columns.Add("Date");
Report.Columns.Add("Time");
Report.Columns.Add("Route");
Report.Columns.Add("Vehicle No");
Report.Columns.Add("Vehicle Type");
Report.Columns.Add("Diesel Filled").DataType = typeof(Double);
Report.Columns.Add("Driver Name");
Report.Columns.Add("Load Type");
Report.Columns.Add("Start Reading");
Report.Columns.Add("Pump Reading");
Report.Columns.Add("Token");
vecmd = new MySqlCommand("SELECT sno, fuel, doe, userid, operetedby, costperltr FROM fuel_transaction WHERE (doe BETWEEN @d1 AND @d2) AND (transtype = 2)");
vecmd.Parameters.Add("@d1", GetLowDate(fromdate).AddDays(-1));
vecmd.Parameters.Add("@d2", GetHighDate(Todate).AddDays(-1));
DataTable dtOpp = devdm.SelectQuery(vecmd).Tables[0];
if (dtOpp.Rows.Count > 0)
{
pnlOpp.Visible = true;
lblOppBal.Text = dtOpp.Rows[0]["fuel"].ToString();
}
vecmd = new MySqlCommand("SELECT sno, fuel, doe, userid, operetedby, costperltr FROM fuel_transaction WHERE (doe BETWEEN @d1 AND @d2) AND (transtype = 2)");
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
DataTable dtclo = devdm.SelectQuery(vecmd).Tables[0];
if (dtclo.Rows.Count > 0)
{
pnlClo.Visible = true;
lblCloBal.Text = dtclo.Rows[0]["fuel"].ToString();
}
int BranchID = 1;
if (vehcleid != 0)
{
vecmd = new MySqlCommand("SELECT employdata.employname,tripdata.routeid, tripdata.fueltank,tripdata.pumpreading,tripdata.Tokenno, tripdata.vehiclestartreading,tripdata.refrigeration_fuel, tripdata.gpskms, tripdata.tripsheetno, tripdata.enddate, employdata.Phoneno AS phoneNumber, tripdata.sno, tripdata.loadtype, employdata.emp_licencenum AS LicenseNo, tripdata.routeid AS RouteName, vehicel_master.registration_no AS Vehicleno, vehicel_master.vm_model AS VehicleModel, minimasters.mm_name AS VehicleType, minimasters_1.mm_name AS VehicleMake,tripdata.endfuelvalue FROM tripdata INNER JOIN employdata ON tripdata.driverid = employdata.emp_sno INNER JOIN vehicel_master ON tripdata.vehicleno = vehicel_master.vm_sno INNER JOIN minimasters ON vehicel_master.vhtype_refno = minimasters.sno INNER JOIN minimasters minimasters_1 ON vehicel_master.vhmake_refno = minimasters_1.sno WHERE (tripdata.enddate BETWEEN @d1 AND @d2) AND (tripdata.userid = @BranchID) AND (tripdata.status = 'C') AND (vehicel_master.vm_sno=@vehcleid) order by tripdata.enddate");
vecmd.Parameters.Add("@BranchID", BranchID);
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
vecmd.Parameters.Add("@vehcleid", vehcleid);
}
else
{
vecmd = new MySqlCommand("SELECT employdata.employname,tripdata.routeid, tripdata.fueltank,tripdata.pumpreading,tripdata.Tokenno, tripdata.vehiclestartreading,tripdata.refrigeration_fuel, tripdata.gpskms, tripdata.tripsheetno, tripdata.enddate, employdata.Phoneno AS phoneNumber, tripdata.sno, tripdata.loadtype, employdata.emp_licencenum AS LicenseNo, tripdata.routeid AS RouteName, vehicel_master.registration_no AS Vehicleno, vehicel_master.vm_model AS VehicleModel, minimasters.mm_name AS VehicleType, minimasters_1.mm_name AS VehicleMake,tripdata.endfuelvalue FROM tripdata INNER JOIN employdata ON tripdata.driverid = employdata.emp_sno INNER JOIN vehicel_master ON tripdata.vehicleno = vehicel_master.vm_sno INNER JOIN minimasters ON vehicel_master.vhtype_refno = minimasters.sno INNER JOIN minimasters minimasters_1 ON vehicel_master.vhmake_refno = minimasters_1.sno WHERE (tripdata.enddate BETWEEN @d1 AND @d2) AND (tripdata.userid = @BranchID) AND (tripdata.status = 'C') order by tripdata.enddate");
vecmd.Parameters.Add("@BranchID", BranchID);
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
}
DataTable dtDiesel = devdm.SelectQuery(vecmd).Tables[0];
if (dtDiesel.Rows.Count > 0)
{
int i = 1;
foreach (DataRow dr in dtDiesel.Rows)
{
DataRow newrow = Report.NewRow();
newrow["Sno"] = i++.ToString();
string assigndate = dr["enddate"].ToString();
DateTime dtPlantime = Convert.ToDateTime(assigndate);
string date = dtPlantime.ToString("dd/MMM/yyyy");
string time = dtPlantime.ToString("dd/MMM/yyyy HH:mm");
string strPlantime = dtPlantime.ToString();
string[] PlanDateTime = time.Split(' ');
newrow["Date"] = date;
newrow["Time"] = PlanDateTime[1];
newrow["Route"] = dr["routeid"].ToString();
newrow["Vehicle No"] = dr["Vehicleno"].ToString();
newrow["Vehicle Type"] = dr["VehicleType"].ToString();
double Diesel = 0;
double.TryParse(dr["endfuelvalue"].ToString(), out Diesel);
double refrigeration_fuel = 0;
double.TryParse(dr["refrigeration_fuel"].ToString(), out refrigeration_fuel);
double total = 0;
total = Diesel + refrigeration_fuel;
newrow["Diesel Filled"] = total;
newrow["Start Reading"] = dr["vehicleStartReading"].ToString();
newrow["Pump Reading"] = dr["pumpreading"].ToString();
newrow["Token"] = dr["Tokenno"].ToString();
newrow["Driver Name"] = dr["employname"].ToString();
newrow["Load Type"] = dr["loadtype"].ToString();
Report.Rows.Add(newrow);
}
DataRow New = Report.NewRow();
New["Vehicle No"] = "Total";
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out valnewCash);
New[dc.ToString()] = valnewCash;
quantity = valnewCash;
ViewState["qty"] = quantity;
}
}
Report.Rows.Add(New);
grdReports.DataSource = Report;
grdReports.DataBind();
string title = "DieselReport From: " + fromdate.ToString() + " To: " + Todate.ToString();
Session["title"] = title;
Session["filename"] = "DieselReport";
Session["xportdata"] = Report;
}
else
{
pvisible.Visible = false;
lblmsg.Text = "No data were found";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
private void btnBlkPlk_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DateTime Fromdate;
DateTime Todate;
String strDivision = cmbDivision.SelectedValue.ToString();
if (chkDivision.Checked)
{
MessageBox.Show("Please Select a One Division");
cmbDivision.Focus();
}
else
{
strDivision = cmbDivision.SelectedValue.ToString();
Fromdate = Convert.ToDateTime(dtpFromDate.Value.Date.ToShortDateString());
Todate = Convert.ToDateTime(dtpToDate.Value.Date.ToShortDateString());
ds = clsReports.ListBlockPluckingDetails(Fromdate, Todate, strDivision);
if (ds.Tables[0].Rows.Count > 0)
{
ds.WriteXml("BlockPlkDetails.xml");
DivisionWiseBlockPlkSummary myReportViewe = new DivisionWiseBlockPlkSummary();
myReportViewe.SetDataSource(ds);
myReportViewe.SetParameterValue("Estate", "Estate:" + myEstate.ListEstates().Rows[0][0].ToString() + " / DivisionID:" + cmbDivision.SelectedValue.ToString());
myReportViewe.SetParameterValue("Date", "From: " + Fromdate.ToShortDateString() + " To: " + Todate.ToShortDateString());
myReportViewe.SetParameterValue("Company Name", FTSPayRollBL.Company.getCompanyName());
ReportViewer myReportViewer = new ReportViewer();
myReportViewer.crystalReportViewer1.ReportSource = myReportViewe;
myReportViewer.Show();
}
else
{
MessageBox.Show("No Data To Print", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("SGST%");
Report.Columns.Add("SGST Amount");
Report.Columns.Add("CGST%");
Report.Columns.Add("CGST Amount");
Report.Columns.Add("IGST%");
Report.Columns.Add("IGST Amount");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
devdm = new VehicleDBMgr();
SalesDBManager vdm = new SalesDBManager();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = dtp_FromDate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = dtp_Todate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblFromDate.Text = fromdate.ToString("dd/MM/yyyy");
lbltodate.Text = Todate.ToString("dd/MM/yyyy");
int vehcleid = Convert.ToInt32(ddlvehicle.SelectedItem.Value);
vecmd = new MySqlCommand("SELECT sno, fuel, doe, userid, operetedby, costperltr FROM fuel_transaction WHERE (doe BETWEEN @d1 AND @d2) AND (transtype = 2)");
vecmd.Parameters.Add("@d1", GetLowDate(fromdate).AddDays(-1));
vecmd.Parameters.Add("@d2", GetHighDate(Todate).AddDays(-1));
DataTable dtOpp = devdm.SelectQuery(vecmd).Tables[0];
if (dtOpp.Rows.Count > 0)
{
}
vecmd = new MySqlCommand("SELECT sno, fuel, doe, userid, operetedby, costperltr FROM fuel_transaction WHERE (doe BETWEEN @d1 AND @d2) AND (transtype = 2)");
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
DataTable dtclo = devdm.SelectQuery(vecmd).Tables[0];
if (dtclo.Rows.Count > 0)
{
}
int BranchID = 1;
if (vehcleid != 0)
{
vecmd = new MySqlCommand("SELECT employdata.employname,tripdata.routeid, tripdata.DieselCost, tripdata.fueltank,tripdata.pumpreading,tripdata.Tokenno, tripdata.vehiclestartreading,tripdata.refrigeration_fuel, tripdata.gpskms, tripdata.tripsheetno, tripdata.enddate, employdata.Phoneno AS phoneNumber, tripdata.sno, tripdata.loadtype, employdata.emp_licencenum AS LicenseNo, tripdata.routeid AS RouteName, vehicel_master.registration_no AS Vehicleno, vehicel_master.vm_model AS VehicleModel, minimasters.mm_name AS VehicleType, minimasters_1.mm_name AS VehicleMake,tripdata.endfuelvalue FROM tripdata INNER JOIN employdata ON tripdata.driverid = employdata.emp_sno INNER JOIN vehicel_master ON tripdata.vehicleno = vehicel_master.vm_sno INNER JOIN minimasters ON vehicel_master.vhtype_refno = minimasters.sno INNER JOIN minimasters minimasters_1 ON vehicel_master.vhmake_refno = minimasters_1.sno WHERE (tripdata.enddate BETWEEN @d1 AND @d2) AND (tripdata.userid = @BranchID) AND (tripdata.status = 'C') AND (vehicel_master.vm_sno=@vehcleid) order by tripdata.enddate");
vecmd.Parameters.Add("@BranchID", BranchID);
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
vecmd.Parameters.Add("@vehcleid", vehcleid);
}
else
{
vecmd = new MySqlCommand("SELECT employdata.employname,tripdata.routeid, tripdata.DieselCost, tripdata.fueltank,tripdata.pumpreading,tripdata.Tokenno, tripdata.vehiclestartreading,tripdata.refrigeration_fuel, tripdata.gpskms, tripdata.tripsheetno, tripdata.enddate, employdata.Phoneno AS phoneNumber, tripdata.sno, tripdata.loadtype, employdata.emp_licencenum AS LicenseNo, tripdata.routeid AS RouteName, vehicel_master.registration_no AS Vehicleno, vehicel_master.vm_model AS VehicleModel, minimasters.mm_name AS VehicleType, minimasters_1.mm_name AS VehicleMake,tripdata.endfuelvalue FROM tripdata INNER JOIN employdata ON tripdata.driverid = employdata.emp_sno INNER JOIN vehicel_master ON tripdata.vehicleno = vehicel_master.vm_sno INNER JOIN minimasters ON vehicel_master.vhtype_refno = minimasters.sno INNER JOIN minimasters minimasters_1 ON vehicel_master.vhmake_refno = minimasters_1.sno WHERE (tripdata.enddate BETWEEN @d1 AND @d2) AND (tripdata.userid = @BranchID) AND (tripdata.status = 'C') order by tripdata.enddate");
vecmd.Parameters.Add("@BranchID", BranchID);
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
}
DataTable dtDiesel = devdm.SelectQuery(vecmd).Tables[0];
if (dtDiesel.Rows.Count > 0)
{
int i = 1;
foreach (DataRow dr in dtDiesel.Rows)
{
DataRow newrow = Report.NewRow();
string assigndate = dr["enddate"].ToString();
DateTime dtPlantime = Convert.ToDateTime(assigndate);
string date = dtPlantime.ToString("dd-MMM-yyyy");
string time = dtPlantime.ToString("dd/MMM/yyyy HH:mm");
string strPlantime = dtPlantime.ToString();
string[] PlanDateTime = time.Split(' ');
string ledgertype = "Sales Accounts";
newrow["Ledger Type"] = ledgertype;
newrow["Invoice Date"] = date;
newrow["Customer Name"] = dr["Vehicleno"].ToString();
double DieselCost = 0;
double.TryParse(dr["DieselCost"].ToString(), out DieselCost);
double Diesel = 0;
double.TryParse(dr["endfuelvalue"].ToString(), out Diesel);
double refrigeration_fuel = 0;
double.TryParse(dr["refrigeration_fuel"].ToString(), out refrigeration_fuel);
double total = 0;
total = Diesel + refrigeration_fuel;
newrow["Qty"] = total;
double NETVAL = total * DieselCost;
newrow["Rate"] = DieselCost;
newrow["Invoce No."] = "SVDS/PBK/DSL" + dr["Tokenno"].ToString() + "";
newrow["Item Name"] = "DIESEL";
newrow["Narration"] = "Being Diesel Isuue To " + dr["Vehicleno"].ToString() + " Invoice no: " + dr["Tokenno"].ToString() + ", Date: " + date + "";
newrow["Taxable Value"] = Math.Round(NETVAL, 2);
newrow["Net Value"] = Math.Round(NETVAL, 2);
newrow["CGST%"] = "0";
newrow["CGST Amount"] = "0";
newrow["IGST%"] = "0";
newrow["IGST Amount"] = "0";
newrow["SGST%"] = "0";
newrow["SGST Amount"] = "0";
Report.Rows.Add(newrow);
}
grdReports.DataSource = Report;
grdReports.DataBind();
string title = "DieselReport From: " + fromdate.ToString() + " To: " + Todate.ToString();
Session["title"] = title;
Session["filename"] = "DieselReport";
Session["xportdata"] = Report;
//Session["quantity"] = quantity;
}
else
{
lblmsg.Text = "No data were found";
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Session["RouteName"] = ddlSalesOffice.SelectedItem.Text;
lblRouteName.Text = ddlSalesOffice.SelectedItem.Text;
vdm = new VehicleDBMgr();
DataTable Report = new DataTable();
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] Todatestrig = txtTodate.Text.Split(' ');
if (Todatestrig.Length > 1)
{
if (Todatestrig[0].Split('-').Length > 0)
{
string[] dates = Todatestrig[0].Split('-');
string[] times = Todatestrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lblDate.Text = fromdate.ToString("dd/MMM/yyyy");
string BranchID = ddlSalesOffice.SelectedValue;
if (BranchID == "572")
{
BranchID = "158";
}
cmd = new MySqlCommand("SELECT modifiedroutes.RouteName, SUM(indents_subtable.DeliveryQty) AS saleQty, SUM(indents_subtable.DeliveryQty * indents_subtable.UnitCost) AS salevalue,modifiedroutes.Sno AS routesno FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT IndentNo, I_date, Branch_id FROM indents WHERE (I_date BETWEEN @starttime AND @endtime)) indt ON modifidroutssubtab.BranchID = indt.Branch_id INNER JOIN indents_subtable ON indt.IndentNo = indents_subtable.IndentNo WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifiedroutes.Sno ORDER BY branchdata.sno, routesno");
cmd.Parameters.AddWithValue("@SOID", BranchID);
cmd.Parameters.AddWithValue("@BranchID", BranchID);
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@endtime", GetHighDate(Todate.AddDays(-1)));
DataTable dtroutecollection = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, SUM(colltion.AmountPaid) AS amtpaid FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN (SELECT Branchid, AmountPaid, PaidDate FROM collections WHERE (PaymentType = 'CASH') AND (PaidDate BETWEEN @d1 AND @d2)) colltion ON modifidroutssubtab.BranchID = colltion.Branchid WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifiedroutes.Sno ORDER BY branchdata.sno, routesno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate));
cmd.Parameters.AddWithValue("@d2", GetHighDate(fromdate));
cmd.Parameters.AddWithValue("@SOID", BranchID);
cmd.Parameters.AddWithValue("@BranchID", BranchID);
DataTable dtrouteamount = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT branchdata.BranchName, branchdata.sno, modifiedroutes.RouteName, modifidroutssubtab.BranchID, modifiedroutes.Sno AS routesno, inventory_monitor.Inv_Sno,SUM(inventory_monitor.Qty) AS balqty, invmaster.InvName FROM branchdata INNER JOIN branchdata branchdata_1 ON branchdata.sno = branchdata_1.sno INNER JOIN modifiedroutes ON branchdata.sno = modifiedroutes.BranchID INNER JOIN (SELECT RefNo, Rank, LevelType, BranchID, CDate, EDate FROM modifiedroutesubtable WHERE (EDate IS NULL) AND (CDate <= @starttime) OR (EDate > @starttime) AND (CDate <= @starttime)) modifidroutssubtab ON modifiedroutes.Sno = modifidroutssubtab.RefNo INNER JOIN inventory_monitor ON modifidroutssubtab.BranchID = inventory_monitor.BranchId INNER JOIN invmaster ON inventory_monitor.Inv_Sno = invmaster.sno WHERE (branchdata_1.SalesOfficeID = @SOID) AND (branchdata.SalesType IS NOT NULL) OR (branchdata.SalesType IS NOT NULL) AND (branchdata.sno = @BranchID) GROUP BY modifiedroutes.Sno, invmaster.sno ORDER BY branchdata.sno, routesno");
cmd.Parameters.AddWithValue("@starttime", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@SOID", BranchID);
cmd.Parameters.AddWithValue("@BranchID", BranchID);
DataTable dtrouteinventory = vdm.SelectQuery(cmd).Tables[0];
Report = new DataTable();
Report.Columns.Add("Route Name");
Report.Columns.Add("Sale Qty").DataType = typeof(Double);
Report.Columns.Add("Sale Value").DataType = typeof(Double);
Report.Columns.Add("Received Amount").DataType = typeof(Double);
Report.Columns.Add("Due Amount").DataType = typeof(Double);
Report.Columns.Add("Crates Balance").DataType = typeof(Double);
Report.Columns.Add("Cans Balance").DataType = typeof(Double);
//Report.Columns.Add("Collected Amount").DataType = typeof(Double);
//Report.Columns.Add("Payment Type");
//Report.Columns.Add("Collection Type");
foreach (DataRow branch in dtroutecollection.Rows)
{
DataRow newrow = Report.NewRow();
newrow["Route Name"] = branch["RouteName"].ToString();
double salevalue = 0;
double saleqty = 0;
double crates = 0;
double cans = 0;
double.TryParse(branch["salevalue"].ToString(), out salevalue);
double.TryParse(branch["saleQty"].ToString(), out saleqty);
foreach (DataRow drdtclubtotal in dtrouteamount.Select("routesno='" + branch["routesno"].ToString() + "'"))
{
double receivedamt = 0;
double due = 0;
double.TryParse(drdtclubtotal["amtpaid"].ToString(), out receivedamt);
newrow["Received Amount"] = Math.Round(receivedamt, 2);
due = salevalue - receivedamt;
newrow["Due Amount"] = Math.Round(due, 2);
}
foreach (DataRow drinventory in dtrouteinventory.Select("routesno='" + branch["routesno"].ToString() + "'"))
{
if (drinventory["Inv_Sno"].ToString() == "1")
{
double invcrates = 0;
double.TryParse(drinventory["balqty"].ToString(), out invcrates);
crates += invcrates;
}
if (drinventory["Inv_Sno"].ToString() == "2")
{
double invcans = 0;
double.TryParse(drinventory["balqty"].ToString(), out invcans);
cans += invcans;
}
if (drinventory["Inv_Sno"].ToString() == "3")
{
double invcans = 0;
double.TryParse(drinventory["balqty"].ToString(), out invcans);
cans += invcans;
}
if (drinventory["Inv_Sno"].ToString() == "4")
{
double invcans = 0;
double.TryParse(drinventory["balqty"].ToString(), out invcans);
cans += invcans;
}
if (drinventory["Inv_Sno"].ToString() == "5")
{
double invcans = 0;
double.TryParse(drinventory["balqty"].ToString(), out invcans);
cans += invcans;
}
}
newrow["Sale Qty"] = Math.Round(saleqty, 2);
newrow["Sale Value"] = Math.Round(salevalue, 2);
newrow["Crates Balance"] = crates;
newrow["Cans Balance"] = cans;
Report.Rows.Add(newrow);
}
DataRow TotRow = Report.NewRow();
TotRow["Route Name"] = "Total";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
TotRow[dc.ToString()] = val;
}
}
Report.Rows.Add(TotRow);
grdReports.DataSource = Report;
grdReports.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
void GetReport()
{
try
{
vdm = new VehicleDBMgr();
if (Session["salestype"].ToString() == "Plant")
{
lblDispatchName.Text = ddlSalesOffice.SelectedItem.Text;
lblDate.Text = txtdate.Text;
}
else
{
lblDispatchName.Text = "SALES OFFICE";
lblDate.Text = txtdate.Text;
}
DateTime fromdate = DateTime.Now;
string[] datestrig = txtdate.Text.Split(' ');
if (datestrig.Length > 1)
{
if (datestrig[0].Split('-').Length > 0)
{
string[] dates = datestrig[0].Split('-');
string[] times = datestrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
Session["filename"] = ddlSalesOffice.SelectedItem.Text + " REPORT " + fromdate.AddDays(1).ToString("dd/MM/yyyy");
pnlHide.Visible = true;
cmd = new MySqlCommand("SELECT dispatch.DispName, dispatch.sno, dispatch.BranchID,tripdata.sno as tripid,DATE_FORMAT(tripdata.AssignDate, '%d %b %y') AS AssignDate FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN tripdata ON triproutes.Tripdata_sno = tripdata.Sno WHERE (dispatch.BranchID = @BranchID) AND (tripdata.I_Date BETWEEN @d1 AND @d2)");
cmd.Parameters.AddWithValue("@BranchID", ddlSalesOffice.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
DataTable dtDispnames = vdm.SelectQuery(cmd).Tables[0];
Report = new DataTable();
Report.Columns.Add("Sno");
Report.Columns.Add("DC Date");
Report.Columns.Add("Dispatch Name");
Report.Columns.Add("Received Crates", typeof(Double));
Report.Columns.Add("Return Crates", typeof(Double));
Report.Columns.Add("Difference Crates", typeof(Double));
Report.Columns.Add("Verified Crates", typeof(Double));
Report.Columns.Add("Received Other Crates", typeof(Double));
Report.Columns.Add("Return Other Crates", typeof(Double));
Report.Columns.Add("Difference Other Crates", typeof(Double));
Report.Columns.Add("Verified Other Crates", typeof(Double));
Report.Columns.Add("Received Can40ltr", typeof(Double));
Report.Columns.Add("Return Can40ltr", typeof(Double));
Report.Columns.Add("Difference Can40ltr", typeof(Double));
Report.Columns.Add("Verified Can40ltr", typeof(Double));
Report.Columns.Add("Received Can20ltr", typeof(Double));
Report.Columns.Add("Return Can20ltr", typeof(Double));
Report.Columns.Add("Difference Can20ltr", typeof(Double));
Report.Columns.Add("Verified Can20ltr", typeof(Double));
int i = 1;
foreach (DataRow drSub in dtDispnames.Rows)
{
cmd = new MySqlCommand("SELECT triproutes.Tripdata_sno, tripinvdata.Qty, tripinvdata.Remaining, invmaster.InvName, invmaster.sno FROM tripdata INNER JOIN triproutes ON tripdata.Sno = triproutes.Tripdata_sno INNER JOIN dispatch ON triproutes.RouteID = dispatch.sno INNER JOIN tripinvdata ON tripdata.Sno = tripinvdata.Tripdata_sno INNER JOIN invmaster ON tripinvdata.invid = invmaster.sno WHERE (tripdata.sno = @tripsno)");
cmd.Parameters.AddWithValue("@tripsno", drSub["tripid"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
DataTable DtTripSubData = vdm.SelectQuery(cmd).Tables[0];
cmd = new MySqlCommand("SELECT TransType, FromTran, ToTran, Qty, VQty,B_inv_sno FROM invtransactions12 WHERE (ToTran = @tripsno) AND (FromTran = @soid) AND (TransType= 3)");
cmd.Parameters.AddWithValue("@tripsno", drSub["tripid"].ToString());
cmd.Parameters.AddWithValue("@soid", ddlSalesOffice.SelectedValue);
DataTable DtverifiedData = vdm.SelectQuery(cmd).Tables[0];
string Disp = drSub["DispName"].ToString();
string[] strName = Disp.Split('_');
DataRow newSo = Report.NewRow();
string AssignDate = drSub["AssignDate"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate);
string ChangedTime = dtAssignDate.ToString("dd/MMM/yyyy");
DataRow drinventry = Report.NewRow();
drinventry["Sno"] = i;
drinventry["DC Date"] = ChangedTime;
drinventry["Dispatch Name"] = Disp;
int Ctotcan40ltr = 0;
int Vtotcan40ltr = 0;
int Ctotcrates = 0;
int Vtotcrates = 0;
int Ctotcan20ltr = 0;
int Dtotcrates = 0;
int Dtotcan20ltr = 0;
int Vtotcan20ltr = 0;
int Dtotcan40ltr = 0;
int Dtotothcrts = 0;
int Vtotothcrts = 0;
int Ctotothcrts = 0;
foreach (DataRow drtripinv in DtTripSubData.Rows)
{
if (drtripinv["sno"].ToString() == "1")
{
int Dcrates = 0;
int.TryParse(drtripinv["Qty"].ToString(), out Dcrates);
Dtotcrates += Dcrates;
int Ccrates = 0;
int.TryParse(drtripinv["Remaining"].ToString(), out Ccrates);
Ctotcrates += Ccrates;
foreach (DataRow drdtverifiedtotal in DtverifiedData.Select("B_inv_sno='" + drtripinv["sno"].ToString() + "'"))
{
int Vcrates = 0;
int.TryParse(drdtverifiedtotal["VQty"].ToString(), out Vcrates);
Vtotcrates += Vcrates;
}
}
if (drtripinv["sno"].ToString() == "3")
{
int Dcan20ltr = 0;
int.TryParse(drtripinv["Qty"].ToString(), out Dcan20ltr);
Dtotcan20ltr += Dcan20ltr;
int Ccan20ltr = 0;
int.TryParse(drtripinv["Remaining"].ToString(), out Ccan20ltr);
Ctotcan20ltr += Ccan20ltr;
foreach (DataRow drdtverifiedtotal in DtverifiedData.Select("B_inv_sno='" + drtripinv["sno"].ToString() + "'"))
{
int Vcan20ltr = 0;
int.TryParse(drdtverifiedtotal["VQty"].ToString(), out Vcan20ltr);
Vtotcan20ltr += Vcan20ltr;
}
}
if (drtripinv["sno"].ToString() == "19")
{
int dothcreats = 0;
int.TryParse(drtripinv["Qty"].ToString(), out dothcreats);
Dtotothcrts += dothcreats;
int cothcrts = 0;
int.TryParse(drtripinv["Remaining"].ToString(), out cothcrts);
Ctotothcrts += cothcrts;
foreach (DataRow drdtverifiedtotal in DtverifiedData.Select("B_inv_sno='" + drtripinv["sno"].ToString() + "'"))
{
int Vothcrts = 0;
int.TryParse(drdtverifiedtotal["VQty"].ToString(), out Vothcrts);
Vtotothcrts += Vothcrts;
}
}
if (drtripinv["sno"].ToString() == "4")
{
int Dcan40ltr = 0;
int.TryParse(drtripinv["Qty"].ToString(), out Dcan40ltr);
Dtotcan40ltr += Dcan40ltr;
int Ccan40ltr = 0;
int.TryParse(drtripinv["Remaining"].ToString(), out Ccan40ltr);
Ctotcan40ltr += Ccan40ltr;
foreach (DataRow drdtverifiedtotal in DtverifiedData.Select("B_inv_sno='" + drtripinv["sno"].ToString() + "'"))
{
int Vcan40ltr = 0;
int.TryParse(drdtverifiedtotal["VQty"].ToString(), out Vcan40ltr);
Vtotcan40ltr += Vcan40ltr;
}
}
}
drinventry["Received Crates"] = Dtotcrates;
drinventry["Return Crates"] = Ctotcrates;
drinventry["Difference Crates"] = Dtotcrates - Ctotcrates;
drinventry["Verified Crates"] = Vtotcrates;
drinventry["Received Other Crates"] = Dtotothcrts;
drinventry["Return Other Crates"] = Ctotothcrts;
drinventry["Difference Other Crates"] = Dtotothcrts - Ctotothcrts;
drinventry["Verified Other Crates"] = Vtotothcrts;
drinventry["Received Can40ltr"] = Dtotcan40ltr;
drinventry["Return Can40ltr"] = Ctotcan40ltr;
drinventry["Difference Can40ltr"] = Dtotcan40ltr - Ctotcan40ltr;
drinventry["Verified Can40ltr"] = Vtotcan40ltr;
drinventry["Received Can20ltr"] = Dtotcan20ltr;
drinventry["Return Can20ltr"] = Ctotcan20ltr;
drinventry["Difference Can20ltr"] = Dtotcan20ltr - Ctotcan20ltr;
drinventry["Verified Can20ltr"] = Vtotcan20ltr;
Report.Rows.Add(drinventry);
i++;
}
DataRow brk = Report.NewRow();
brk["Dispatch Name"] = "";
Report.Rows.Add(brk);
DataRow totalinventory = Report.NewRow();
totalinventory["Dispatch Name"] = "TOTAL";
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
totalinventory[dc.ToString()] = val;
}
}
Report.Rows.Add(totalinventory);
foreach (DataColumn col in Report.Columns)
{
string Pname = col.ToString();
string ProductName = col.ToString();
ProductName = GetSpace(ProductName);
Report.Columns[Pname].ColumnName = ProductName;
}
grdReports.DataSource = Report;
grdReports.DataBind();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
grdReports.DataSource = Report;
grdReports.DataBind();
}
}
void GetReport()
{
try
{
lblmsg.Text = "";
pnlHide.Visible = true;
Report = new DataTable();
Session["IDate"] = DateTime.Now.AddDays(1).ToString("dd/MM/yyyy");
vdm = new VehicleDBMgr();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = txtfromdate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = txttodate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
lbl_selfromdate.Text = fromdate.ToString("dd/MM/yyyy");
lbl_selttodate.Text = Todate.ToString("dd/MM/yyyy");
Session["filename"] = "DAY WISE MILK SALE REPORT";
TimeSpan dateSpan = Todate.Subtract(fromdate);
int NoOfdays = dateSpan.Days;
NoOfdays = NoOfdays + 1;
DataTable dtsubcategory = new DataTable();
//if (ddlReportType.SelectedValue == "1")
//{
if (ddlSubCategoryName.SelectedValue == "ALL")
{
cmd = new MySqlCommand("SELECT products_subcategory.rank,DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate, branchdata.BranchName, products_subcategory.tempsub_catsno,products_subcategory.description AS SubCatName, productsdata.tempsubcatsno, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty), 2) AS SaleValue FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.tempsubcatsno = products_subcategory.tempsub_catsno INNER JOIN products_category ON products_subcategory.tempcatsno = products_category.tempcatsno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) AND (indents_subtable.DeliveryQty > 0) AND (products_category.tempcatsno = @CatSno) GROUP BY IndentDate,products_subcategory.tempsub_catsno ORDER BY products_subcategory.tempsub_catsno");
// cmd = new MySqlCommand("SELECT ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, indents_subtable.Product_sno, products_subcategory.sno, products_subcategory.SubCatName, DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate FROM branchmappingtable branchmappingtable_1 INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno INNER JOIN branchmappingtable ON branchmappingtable_1.SuperBranch = branchmappingtable.SubBranch WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) AND (products_category.sno = '9') AND (branchmappingtable_1.SuperBranch <> 538) GROUP BY IndentDate, products_subcategory.sno");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"]);
cmd.Parameters.AddWithValue("@CatSno", ddlReportType.SelectedValue);
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
dtble1 = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT SUM(tripsubdata.Qty) AS dispqty, products_category.Categoryname, products_subcategory.SubCatName, result.IndentDate FROM (SELECT dispatch.sno, dispatch.DispName, tripdat.Sno AS tripid, DATE_FORMAT(tripdat.I_Date, '%d %b %y') AS IndentDate FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2) AND (Status <> 'C')) tripdat ON triproutes.Tripdata_sno = tripdat.Sno WHERE (dispatch.Branch_Id = @branch) AND (dispatch.DispMode IS NOT NULL) AND (dispatch.DispMode <> 'SPL')) result INNER JOIN tripsubdata ON result.tripid = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (products_category.sno = 9) GROUP BY products_subcategory.sno, result.IndentDate");
//cmd.Parameters.AddWithValue("@branch", Session["branch"]);
//cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
//cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
//dtdirect = vdm.SelectQuery(cmd).Tables[0];
DataView dv = dtble1.DefaultView;
dv.Sort = "IndentDate ASC";
DataTable sortedProductDT = dv.ToTable();
if (sortedProductDT.Rows.Count > 0)
{
DataView view = new DataView(sortedProductDT);
DataTable distinctproducts = view.ToTable(true, "SubCatName", "tempsub_catsno");
DataView dv1 = distinctproducts.DefaultView;
dv1.Sort = "tempsub_catsno ASC";
sortedProductDT1 = dv1.ToTable();
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Date");
int count = 0;
foreach (DataRow dr in sortedProductDT1.Rows)
{
Report.Columns.Add(dr["SubCatName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total").DataType = typeof(Double);
DataTable distincttable = view.ToTable(true, "IndentDate");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
string AssignDate = branch["IndentDate"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate).AddDays(1);
string AssigDate = dtAssignDate.ToString("dd MMM yyyy");
newrow["Date"] = AssigDate;
double totmilk = 0;
foreach (DataRow dr in sortedProductDT.Rows)
{
double directdel = 0;
if (dr["IndentDate"].ToString() == AssignDate)
{
//foreach (DataRow drdtdirect in dtdirect.Select("IndentDate='" + AssignDate + "'"))
//{
// if (drdtdirect["SubCatName"].ToString() == dr["SubCatName"].ToString())
// {
// double.TryParse(drdtdirect["dispqty"].ToString(), out directdel);
// }
//}
double delqty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out delqty);
newrow[dr["SubCatName"].ToString()] = Math.Round(delqty + directdel, 2);
totmilk += delqty + directdel;
}
}
newrow["Total"] = Math.Round(totmilk, 2);
Report.Rows.Add(newrow);
i++;
}
}
}
else
{
// AND (branchmappingtable.SubBranch NOT IN (538, 2749, 3928, 1801, 3625))
if (Session["branch"].ToString() == "172")
{
cmd = new MySqlCommand("SELECT products_subcategory.rank,DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate, branchdata.BranchName, products_subcategory.tempcatsno, productsdata.tempsubcatsno, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty), 2) AS SaleValue, products_category.description AS Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.tempsubcatsno = products_subcategory.tempsub_catsno INNER JOIN products_category ON products_subcategory.tempcatsno = products_category.tempcatsno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (branchmappingtable.SubBranch NOT IN (538, 2749, 3928, 1801, 3625)) AND (indents.I_date BETWEEN @d1 AND @d2) AND (indents_subtable.DeliveryQty > 0) AND (products_subcategory.tempsub_catsno = @SubCatsno) GROUP BY IndentDate,productsdata.ProductName ORDER BY products_subcategory.tempsub_catsno,productsdata.rank");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
cmd.Parameters.AddWithValue("@SubCatsno", ddlSubCategoryName.SelectedValue);
}
else
{
cmd = new MySqlCommand("SELECT products_subcategory.rank,DATE_FORMAT(indents.I_date, '%d %b %y') AS IndentDate, branchdata.BranchName, products_subcategory.tempcatsno, productsdata.tempsubcatsno, productsdata.ProductName, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, ROUND(SUM(indents_subtable.UnitCost * indents_subtable.DeliveryQty), 2) AS SaleValue, products_category.description AS Categoryname FROM branchmappingtable INNER JOIN branchdata ON branchmappingtable.SubBranch = branchdata.sno INNER JOIN branchmappingtable branchmappingtable_1 ON branchdata.sno = branchmappingtable_1.SuperBranch INNER JOIN branchdata branchdata_1 ON branchmappingtable_1.SubBranch = branchdata_1.sno INNER JOIN indents ON branchdata_1.sno = indents.Branch_id INNER JOIN indents_subtable ON indents.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.tempsubcatsno = products_subcategory.tempsub_catsno INNER JOIN products_category ON products_subcategory.tempcatsno = products_category.tempcatsno WHERE (branchmappingtable.SuperBranch = @BranchID) AND (indents.I_date BETWEEN @d1 AND @d2) AND (indents_subtable.DeliveryQty > 0) AND (products_subcategory.tempsub_catsno = @SubCatsno) GROUP BY IndentDate,productsdata.ProductName ORDER BY products_subcategory.tempsub_catsno,productsdata.rank");
cmd.Parameters.AddWithValue("@BranchID", Session["branch"].ToString());
cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
cmd.Parameters.AddWithValue("@SubCatsno", ddlSubCategoryName.SelectedValue);
}
dtsubcategory = vdm.SelectQuery(cmd).Tables[0];
//cmd = new MySqlCommand("SELECT SUM(tripsubdata.Qty) AS dispqty, products_category.Categoryname, products_subcategory.SubCatName, result.IndentDate FROM (SELECT dispatch.sno, dispatch.DispName, tripdat.Sno AS tripid, DATE_FORMAT(tripdat.I_Date, '%d %b %y') AS IndentDate FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2) AND (Status <> 'C')) tripdat ON triproutes.Tripdata_sno = tripdat.Sno WHERE (dispatch.Branch_Id = @branch) AND (dispatch.DispMode IS NOT NULL) AND (dispatch.DispMode <> 'SPL')) result INNER JOIN tripsubdata ON result.tripid = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (products_category.sno = 9) GROUP BY products_subcategory.sno, result.IndentDate");
//cmd.Parameters.AddWithValue("@branch", Session["branch"]);
//cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
//cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
//dtdirect = vdm.SelectQuery(cmd).Tables[0];
DataView dv = dtsubcategory.DefaultView;
dv.Sort = "IndentDate ASC";
DataTable sortedProductDT = dv.ToTable();
if (sortedProductDT.Rows.Count > 0)
{
DataView view = new DataView(sortedProductDT);
distinctproducts = view.ToTable(true, "ProductName");
Report = new DataTable();
Report.Columns.Add("SNo");
Report.Columns.Add("Date");
int count = 0;
foreach (DataRow dr in distinctproducts.Rows)
{
Report.Columns.Add(dr["ProductName"].ToString()).DataType = typeof(Double);
}
Report.Columns.Add("Total");
DataTable distincttable = view.ToTable(true, "IndentDate");
int i = 1;
foreach (DataRow branch in distincttable.Rows)
{
DataRow newrow = Report.NewRow();
newrow["SNo"] = i;
string AssignDate = branch["IndentDate"].ToString();
DateTime dtAssignDate = Convert.ToDateTime(AssignDate).AddDays(1);
string AssigDate = dtAssignDate.ToString("dd MMM yyyy");
newrow["Date"] = AssigDate;
double totmilk = 0;
foreach (DataRow dr in sortedProductDT.Rows)
{
double directdel = 0;
if (dr["IndentDate"].ToString() == AssignDate)
{
//foreach (DataRow drdtdirect in dtdirect.Select("IndentDate='" + AssignDate + "'"))
//{
// if (drdtdirect["SubCatName"].ToString() == dr["SubCatName"].ToString())
// {
// double.TryParse(drdtdirect["dispqty"].ToString(), out directdel);
// }
//}
double delqty = 0;
double.TryParse(dr["DeliveryQty"].ToString(), out delqty);
newrow[dr["ProductName"].ToString()] = Math.Round(delqty + directdel, 2);
totmilk += delqty + directdel;
}
}
newrow["Total"] = Math.Round(totmilk, 2);
Report.Rows.Add(newrow);
i++;
}
}
}
DataRow newvartical = Report.NewRow();
newvartical["Date"] = "Total";
DataRow newAvg = Report.NewRow();
newAvg["Date"] = "Avg Per Day";
double Avgval = 0.0;
double val = 0.0;
foreach (DataColumn dc in Report.Columns)
{
if (dc.DataType == typeof(Double))
{
Avgval = 0.0;
val = 0.0;
double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
newvartical[dc.ToString()] = val;
Avgval = val / NoOfdays;
newAvg[dc.ToString()] = Math.Round(Avgval, 2);
}
}
Report.Rows.Add(newvartical);
Report.Rows.Add(newAvg);
//}
//if (ddlReportType.SelectedValue == "2")
//{
// //cmd = new MySqlCommand("SELECT ind.IndentNo, DATE_FORMAT(ind.I_date, '%d %b %y') AS IndentDate, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, indents_subtable.Product_sno,products_subcategory.sno, products_subcategory.SubCatName, products_category.sno AS categorysno FROM branchmappingtable INNER JOIN branchmappingtable branchmappingtable_1 ON branchmappingtable.SubBranch = branchmappingtable_1.SuperBranch INNER JOIN (SELECT IndentNo, Branch_id, TotalQty, TotalPrice, I_date, D_date, Status, UserData_sno, PaymentStatus, I_createdby, I_modifiedby, IndentType FROM indents WHERE (I_date BETWEEN @d1 AND @d2)) ind ON branchmappingtable_1.SubBranch = ind.Branch_id INNER JOIN indents_subtable ON ind.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @branch) AND (products_category.sno = 10) OR (branchmappingtable.SuperBranch = @branch) AND (products_category.sno = 12) GROUP BY IndentDate, products_category.sno");
// //cmd = new MySqlCommand("SELECT ind.IndentNo, DATE_FORMAT(ind.I_date, '%d %b %y') AS IndentDate, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, indents_subtable.Product_sno,products_subcategory.sno, products_subcategory.SubCatName, products_category.sno AS categorysno FROM branchmappingtable INNER JOIN branchmappingtable branchmappingtable_1 ON branchmappingtable.SubBranch = branchmappingtable_1.SuperBranch INNER JOIN (SELECT IndentNo, Branch_id, TotalQty, TotalPrice, I_date, D_date, Status, UserData_sno, PaymentStatus, I_createdby, I_modifiedby, IndentType FROM indents WHERE (I_date BETWEEN @d1 AND @d2)) ind ON branchmappingtable_1.SubBranch = ind.Branch_id INNER JOIN indents_subtable ON ind.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @branch) AND (products_category.sno = 10) AND (branchmappingtable_1.SuperBranch <> 538) OR (branchmappingtable.SuperBranch = @branch) AND (products_category.sno = 12) AND (branchmappingtable_1.SuperBranch <> 538) GROUP BY IndentDate, products_category.sno");
// cmd = new MySqlCommand("SELECT ind.IndentNo, DATE_FORMAT(ind.I_date, '%d %b %y') AS IndentDate, ROUND(SUM(indents_subtable.DeliveryQty), 2) AS DeliveryQty, indents_subtable.Product_sno,products_subcategory.sno, products_subcategory.SubCatName, products_category.sno AS categorysno FROM branchmappingtable INNER JOIN branchmappingtable branchmappingtable_1 ON branchmappingtable.SubBranch = branchmappingtable_1.SuperBranch INNER JOIN (SELECT IndentNo, Branch_id, TotalQty, TotalPrice, I_date, D_date, Status, UserData_sno, PaymentStatus, I_createdby, I_modifiedby, IndentType FROM indents WHERE (I_date BETWEEN @d1 AND @d2)) ind ON branchmappingtable_1.SubBranch = ind.Branch_id INNER JOIN indents_subtable ON ind.IndentNo = indents_subtable.IndentNo INNER JOIN productsdata ON indents_subtable.Product_sno = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (branchmappingtable.SuperBranch = @branch) AND (products_category.sno = 10) AND (branchmappingtable_1.SuperBranch <> 538) AND (productsdata.Units <> @units) OR (branchmappingtable.SuperBranch = @branch) AND (products_category.sno = 12) AND (branchmappingtable_1.SuperBranch <> 538) AND (productsdata.Units <> @units) GROUP BY IndentDate, products_category.sno");
// cmd.Parameters.AddWithValue("@branch", Session["branch"]);
// cmd.Parameters.AddWithValue("@units", "ltr");
// cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
// cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
// dtcurdBM = vdm.SelectQuery(cmd).Tables[0];
// //cmd = new MySqlCommand("SELECT SUM(tripsubdata.Qty) AS dispqty, products_category.Categoryname,products_category.sno AS categorysno,result.IndentDate FROM (SELECT dispatch.sno, dispatch.DispName, tripdat.Sno AS tripid, DATE_FORMAT(tripdat.I_Date, '%d %b %y') AS IndentDate FROM dispatch INNER JOIN triproutes ON dispatch.sno = triproutes.RouteID INNER JOIN (SELECT Sno, AssignDate, Status, I_Date FROM tripdata WHERE (I_Date BETWEEN @d1 AND @d2) AND (Status <> 'C')) tripdat ON triproutes.Tripdata_sno = tripdat.Sno WHERE (dispatch.Branch_Id = @branch) AND (dispatch.DispMode IS NOT NULL) AND (dispatch.DispMode <> 'SPL')) result INNER JOIN tripsubdata ON result.tripid = tripsubdata.Tripdata_sno INNER JOIN productsdata ON tripsubdata.ProductId = productsdata.sno INNER JOIN products_subcategory ON productsdata.SubCat_sno = products_subcategory.sno INNER JOIN products_category ON products_subcategory.category_sno = products_category.sno WHERE (products_category.sno = 10) OR (products_category.sno = 12) GROUP BY result.IndentDate, products_category.sno");
// //cmd.Parameters.AddWithValue("@branch", Session["branch"]);
// //cmd.Parameters.AddWithValue("@d1", GetLowDate(fromdate.AddDays(-1)));
// //cmd.Parameters.AddWithValue("@d2", GetHighDate(Todate.AddDays(-1)));
// //dtdirectcurdBM = vdm.SelectQuery(cmd).Tables[0];
// if (dtcurdBM.Rows.Count > 0)
// {
// DataView view = new DataView(dtcurdBM);
// Report = new DataTable();
// Report.Columns.Add("SNo");
// Report.Columns.Add("Date");
// Report.Columns.Add("Curd", typeof(Double));
// Report.Columns.Add("Butter Milk", typeof(Double));
// Report.Columns.Add("Total Curd & BM", typeof(Double));
// DataTable distincttable = view.ToTable(true, "IndentDate");
// int i = 1;
// foreach (DataRow branch in distincttable.Rows)
// {
// DataRow newrow = Report.NewRow();
// newrow["SNo"] = i;
// string AssignDate = branch["IndentDate"].ToString();
// DateTime dtAssignDate = Convert.ToDateTime(AssignDate).AddDays(1);
// string AssigDate = dtAssignDate.ToString("dd MMM yyyy");
// newrow["Date"] = AssigDate;
// double totmilk = 0;
// foreach (DataRow dr in dtcurdBM.Rows)
// {
// double directdel = 0;
// if (dr["IndentDate"].ToString() == AssignDate)
// {
// //foreach (DataRow drdtdirect in dtdirectcurdBM.Select("IndentDate='" + AssignDate + "'"))
// //{
// // if (drdtdirect["categorysno"].ToString() == dr["categorysno"].ToString())
// // {
// // double.TryParse(drdtdirect["dispqty"].ToString(), out directdel);
// // }
// //}
// double delqty = 0;
// double.TryParse(dr["DeliveryQty"].ToString(), out delqty);
// if (dr["categorysno"].ToString() == "10")
// {
// newrow["Curd"] = Math.Round(delqty + directdel, 2);
// totmilk += delqty + directdel;
// }
// if (dr["categorysno"].ToString() == "12")
// {
// double div = 0;
// div = delqty + directdel;
// newrow["Butter Milk"] = Math.Round(div / 3);
// totmilk += div + directdel;
// }
// }
// }
// newrow["Total Curd & BM"] = Math.Round(totmilk, 2);
// Report.Rows.Add(newrow);
// i++;
// }
// }
// DataRow newvartical = Report.NewRow();
// newvartical["Date"] = "Total";
// DataRow newAvg = Report.NewRow();
// newAvg["Date"] = "Avg Per Day";
// double Avgval = 0.0;
// double val = 0.0;
// foreach (DataColumn dc in Report.Columns)
// {
// if (dc.DataType == typeof(Double))
// {
// Avgval = 0.0;
// val = 0.0;
// double.TryParse(Report.Compute("sum([" + dc.ToString() + "])", "[" + dc.ToString() + "]<>'0'").ToString(), out val);
// newvartical[dc.ToString()] = val;
// Avgval = val / NoOfdays;
// newAvg[dc.ToString()] = Math.Round(Avgval, 2);
// }
// }
// Report.Rows.Add(newvartical);
// Report.Rows.Add(newAvg);
//}
grdtotal_dcReports.DataSource = Report;
grdtotal_dcReports.DataBind();
//for (int jj = 0; jj < grdtotal_dcReports.Rows.Count; jj++)
//{
// DataGridViewRow dgvr =
// DataGridViewCell dr =
// double previousvalue = 0;
// int i = 0;
// int count = 0;
// for (int rowcnt = 2; rowcnt < dgvr.Cells.Count; rowcnt++)
// {
// //int diff = 0;
// //int.TryParse(dgvr.Cells[rowcnt].Text, out diff);
// if (count == 0 || count == 1)
// {
// double presentvalue = 0;
// double.TryParse(dgvr.Cells[rowcnt].Text, out presentvalue);
// if (presentvalue < previousvalue)
// {
// dgvr.Cells[rowcnt].BackColor = Color.SandyBrown;
// // double.TryParse(dgvr.Cells[rowcnt].Text, out previousvalue);
// }
// else
// {
// dgvr.Cells[rowcnt].BackColor = Color.Green;
// double.TryParse(dgvr.Cells[rowcnt].Text, out previousvalue);
// }
// if (dgvr.Cells[1].Text.Contains("Avg Per Day"))
// {
// previousvalue = 0;
// }
// count++;
// }
// else
// {
// count = 0;
// previousvalue = 0;
// double presentvalue = 0;
// double.TryParse(dgvr.Cells[rowcnt].Text, out presentvalue);
// if (presentvalue < previousvalue)
// {
// dgvr.Cells[rowcnt].BackColor = Color.SandyBrown;
// // double.TryParse(dgvr.Cells[rowcnt].Text, out previousvalue);
// }
// else
// {
// dgvr.Cells[rowcnt].BackColor = Color.Green;
// double.TryParse(dgvr.Cells[rowcnt].Text, out previousvalue);
// }
// if (dgvr.Cells[1].Text.Contains("Avg Per Day"))
// {
// previousvalue = 0;
// }
// //double presentvalue = 0;
// //double.TryParse(dgvr.Cells[rowcnt].Text, out presentvalue);
// //if (presentvalue < previousvalue)
// //{
// // dgvr.Cells[rowcnt].BackColor = Color.SandyBrown;
// // // double.TryParse(dgvr.Cells[rowcnt].Text, out previousvalue);
// //}
// //else
// //{
// // dgvr.Cells[rowcnt].BackColor = Color.Green;
// // double.TryParse(dgvr.Cells[rowcnt].Text, out previousvalue);
// //}
// //if (dgvr.Cells[1].Text.Contains("Avg Per Day"))
// //{
// // previousvalue = 0;
// //}
// }
//}
// }
}
catch (Exception ex)
{
}
}
protected void btn_Generate_Click(object sender, EventArgs e)
{
try
{
lblmsg.Text = "";
Report.Columns.Add("Ledger Type");
Report.Columns.Add("Customer Name");
Report.Columns.Add("Invoce No.");
Report.Columns.Add("Invoice Date");
Report.Columns.Add("HSN Code");
Report.Columns.Add("Item Name");
Report.Columns.Add("Qty");
Report.Columns.Add("Rate");
Report.Columns.Add("Taxable Value");
Report.Columns.Add("SGST%");
Report.Columns.Add("SGST Amount");
Report.Columns.Add("CGST%");
Report.Columns.Add("CGST Amount");
Report.Columns.Add("IGST%");
Report.Columns.Add("IGST Amount");
Report.Columns.Add("Net Value");
Report.Columns.Add("Narration");
devdm = new VehicleDBMgr();
SalesDBManager vdm = new SalesDBManager();
DateTime fromdate = DateTime.Now;
string[] dateFromstrig = dtp_FromDate.Text.Split(' ');
if (dateFromstrig.Length > 1)
{
if (dateFromstrig[0].Split('-').Length > 0)
{
string[] dates = dateFromstrig[0].Split('-');
string[] times = dateFromstrig[1].Split(':');
fromdate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
DateTime Todate = DateTime.Now;
string[] dateTostrig = dtp_Todate.Text.Split(' ');
if (dateTostrig.Length > 1)
{
if (dateTostrig[0].Split('-').Length > 0)
{
string[] dates = dateTostrig[0].Split('-');
string[] times = dateTostrig[1].Split(':');
Todate = new DateTime(int.Parse(dates[2]), int.Parse(dates[1]), int.Parse(dates[0]), int.Parse(times[0]), int.Parse(times[1]), 0);
}
}
vecmd = new MySqlCommand("SELECT sno, fuel, doe, userid, operetedby, costperltr FROM fuel_transaction WHERE (doe BETWEEN @d1 AND @d2) AND (transtype = 2)");
vecmd.Parameters.Add("@d1", GetLowDate(fromdate).AddDays(-1));
vecmd.Parameters.Add("@d2", GetHighDate(Todate).AddDays(-1));
DataTable dtOpp = devdm.SelectQuery(vecmd).Tables[0];
if (dtOpp.Rows.Count > 0)
{
}
vecmd = new MySqlCommand("SELECT sno, fuel, doe, userid, operetedby, costperltr FROM fuel_transaction WHERE (doe BETWEEN @d1 AND @d2) AND (transtype = 2)");
vecmd.Parameters.Add("@d1", GetLowDate(fromdate));
vecmd.Parameters.Add("@d2", GetHighDate(Todate));
DataTable dtclo = devdm.SelectQuery(vecmd).Tables[0];
if (dtclo.Rows.Count > 0)
{
}
string product = ddl_product.SelectedItem.Value;
string type = ddl_type.SelectedItem.Value;
DataTable dtDiesel = new DataTable();
if (product == "Spares")
{
if (type == "vehicle")
{
int vehcleid = Convert.ToInt32(ddlvehicle.SelectedItem.Value);
cmd = new SqlCommand("SELECT suboutwarddetails.perunit, suboutwarddetails.totalcost, suboutwarddetails.quantity, productmaster.productname, sectionmaster.name, outwarddetails.inwarddate, productmaster.HSNcode, outwarddetails.issueno FROM outwarddetails INNER JOIN suboutwarddetails ON outwarddetails.sno = suboutwarddetails.in_refno INNER JOIN productmaster ON suboutwarddetails.productid = productmaster.productid INNER JOIN categorymaster ON productmaster.productcode = categorymaster.cat_code INNER JOIN sectionmaster ON outwarddetails.section_id = sectionmaster.sectionid WHERE (categorymaster.cat_code = '15') AND (outwarddetails.inwarddate BETWEEN @fromdate AND @todate) AND (outwarddetails.branchid = '2') AND (sectionmaster.sectionid = @vehcleid)");
cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
cmd.Parameters.Add("@todate", GetLowDate(Todate));
cmd.Parameters.Add("@vehcleid", vehcleid);
}
else
{
cmd = new SqlCommand("SELECT suboutwarddetails.perunit, suboutwarddetails.totalcost, suboutwarddetails.quantity, productmaster.productname, sectionmaster.name, outwarddetails.inwarddate, productmaster.HSNcode, outwarddetails.issueno FROM outwarddetails INNER JOIN suboutwarddetails ON outwarddetails.sno = suboutwarddetails.in_refno INNER JOIN productmaster ON suboutwarddetails.productid = productmaster.productid INNER JOIN categorymaster ON productmaster.productcode = categorymaster.cat_code INNER JOIN sectionmaster ON outwarddetails.section_id = sectionmaster.sectionid WHERE (categorymaster.cat_code = '15') AND (outwarddetails.inwarddate BETWEEN @fromdate AND @todate) AND (outwarddetails.branchid = '2')");
cmd.Parameters.Add("@fromdate", GetLowDate(fromdate));
cmd.Parameters.Add("@todate", GetLowDate(Todate));
}
dtDiesel = vdm.SelectQuery(cmd).Tables[0];
}
else if (product == "Diesel")
{
}
//devdm.SelectQuery(vecmd).Tables[0];
if (dtDiesel.Rows.Count > 0)
{
int i = 1;
foreach (DataRow dr in dtDiesel.Rows)
{
DataRow newrow = Report.NewRow();
string assigndate = dr["inwarddate"].ToString();
DateTime dtPlantime = Convert.ToDateTime(assigndate);
string date = dtPlantime.ToString("dd-MMM-yyyy");
string time = dtPlantime.ToString("dd/MMM/yyyy HH:mm");
string strPlantime = dtPlantime.ToString();
string[] PlanDateTime = time.Split(' ');
string ledgertype = "Sales Accounts";
newrow["HSN Code"] = dr["HSNcode"].ToString(); //"27101930";
newrow["Ledger Type"] = ledgertype;
newrow["Invoice Date"] = date;
newrow["Customer Name"] = dr["name"].ToString();
double Diesel = 0;
double.TryParse(dr["quantity"].ToString(), out Diesel);
//double refrigeration_fuel = 0;
//double.TryParse(dr["refrigeration_fuel"].ToString(), out refrigeration_fuel);
double total = 0;
total = Diesel; //+ refrigeration_fuel;
newrow["Qty"] = total;
double RATE = Convert.ToDouble(dr["perunit"].ToString()); //Convert.ToDouble(PRICE);
double NETVAL = total * RATE;
newrow["Rate"] = RATE;
newrow["Invoce No."] = "SVDS/PBK/DSL" + dr["issueno"].ToString() + "";
newrow["CGST%"] = "0";
newrow["CGST Amount"] = "0";
newrow["IGST%"] = "0";
newrow["IGST Amount"] = "0";
newrow["SGST%"] = "0";
newrow["SGST Amount"] = "0";
newrow["Item Name"] = dr["productname"].ToString(); //"DIESEL";
newrow["Narration"] = "Being " + dr["productname"].ToString() + " Isuue To " + dr["name"].ToString() + " Invoice no: " + dr["issueno"].ToString() + ", Date: " + date + "";
newrow["Taxable Value"] = Math.Round(NETVAL, 2);
newrow["Net Value"] = Math.Round(NETVAL, 2);
Report.Rows.Add(newrow);
}
grdReports.DataSource = Report;
grdReports.DataBind();
string title = "DieselReport From: " + fromdate.ToString() + " To: " + Todate.ToString();
Session["title"] = title;
Session["filename"] = "DieselReport";
Session["xportdata"] = Report;
//Session["quantity"] = quantity;
}
else
{
if (product == "Diesel")
{
lblmsg.Text = "Please Goto Stores Diesel Report";
}
else
{
lblmsg.Text = "No data were found";
}
}
}
catch (Exception ex)
{
lblmsg.Text = ex.Message;
}
}
