protected void Timer1_Tick(object sender, EventArgs e) { if (Fp_Fuel.Sheets[0].RowCount > 0) { for (int i = 0; i < Fp_Fuel.Rows.Count; i++) { if (Convert.ToDouble(Fp_Fuel.Sheets[0].Cells[i, 6].Text.ToString()) <= 10) { if (Fp_Fuel.Sheets[0].Cells[i, 2].BackColor == Color.Salmon) { Fp_Fuel.Sheets[0].Cells[i, 2].BackColor = Color.Yellow; Fp_Fuel.Sheets[0].Cells[i, 3].BackColor = Color.Yellow; Fp_Fuel.Sheets[0].Cells[i, 4].BackColor = Color.Yellow; Fp_Fuel.Sheets[0].Cells[i, 5].BackColor = Color.Yellow; Fp_Fuel.Sheets[0].Cells[i, 6].BackColor = Color.Yellow; } else { Fp_Fuel.Sheets[0].Cells[i, 2].BackColor = Color.Salmon; Fp_Fuel.Sheets[0].Cells[i, 3].BackColor = Color.Salmon; Fp_Fuel.Sheets[0].Cells[i, 4].BackColor = Color.Salmon; Fp_Fuel.Sheets[0].Cells[i, 5].BackColor = Color.Salmon; Fp_Fuel.Sheets[0].Cells[i, 6].BackColor = Color.Salmon; } } else { Fp_Fuel.Sheets[0].Rows[i].BackColor = Color.White; } } Fp_Fuel.SaveChanges(); } }
protected void btnMainGo_Click(object sender, EventArgs e) { string vech_all = string.Empty; string sess_all = string.Empty; string route_all = string.Empty; string stage_all = string.Empty; string display_all = string.Empty; string stage_header = string.Empty; string route_header = string.Empty; errmsg.Visible = false; for (int vech_count = 0; vech_count < vehiclechecklist.Items.Count; vech_count++) { if (vehiclechecklist.Items[vech_count].Selected == true) { if (vech_all == "") { vech_all = vehiclechecklist.Items[vech_count].Text; } else { vech_all = vech_all + "','" + vehiclechecklist.Items[vech_count].Text; } } } con.Close(); con.Open(); string[] spitfrom = txtfromdate.Text.Split('/'); DateTime dtfrom = Convert.ToDateTime(spitfrom[1] + '/' + spitfrom[0] + '/' + spitfrom[2]); string[] spilttodate = txttodate.Text.Split('/'); DateTime dtto = Convert.ToDateTime(spilttodate[1] + '/' + spilttodate[0] + '/' + spilttodate[2]); // SqlCommand cmd_km_count = new SqlCommand("select vehicle_type,vehicle_id,sum(travel_km) as km_total,sum(fuel) as fu_total from vehicle_usage where vehicle_id in('" + vech_all + "') and month(date) between '" + ddlMonth.SelectedValue.ToString() + "' and '" + DropDownList1.SelectedValue.ToString() + "' and year(date) between '" + ddlYear.SelectedItem.ToString() + "' and '" + DropDownList2.SelectedItem.ToString() + "' group by vehicle_type,vehicle_id order by Vehicle_type,vehicle_id", con); SqlCommand cmd_km_count = new SqlCommand(); if (Chkdate.Checked == true) { //Modified by strinath 1262014 // cmd_km_count = new SqlCommand("select vehicle_type,vehicle_id,sum(travel_km) as km_total,sum(fuel) as fu_total from vehicle_usage where vehicle_id in('" + vech_all + "') and date between '" + dtfrom.ToString() + "' and '" + dtto.ToString() + "' group by vehicle_type,vehicle_id order by Vehicle_type,vehicle_id", con); //modified by prabha //existing //cmd_km_count = new SqlCommand("select vehicle_type,vehicle_id,v.Reg_No,sum(travel_km) as km_total,sum(fuel) as fu_total from vehicle_usage vu,Vehicle_Master v where v.Veh_ID=vu.Vehicle_Id and vehicle_id in('" + vech_all + "') and date between '" + dtfrom.ToString() + "' and '" + dtto.ToString() + "' group by vehicle_type,v.Reg_No,vehicle_id order by Vehicle_type,vehicle_id", con); //new cmd_km_count = new SqlCommand("select vehicle_type,vehicle_id,v.Reg_No,sum(travel_km) as km_total,sum(fuel) as fu_total from vehicle_usage vu,Vehicle_Master v where v.Veh_ID=vu.Vehicle_Id and vehicle_id in('" + vech_all + "') and arrivalpdate between '" + dtfrom.ToString() + "' and '" + dtto.ToString() + "' group by vehicle_type,v.Reg_No,vehicle_id order by Vehicle_type,vehicle_id", con); } else {//Modified by strinath 1262014 //cmd_km_count = new SqlCommand("select vehicle_type,vehicle_id,sum(travel_km) as km_total,sum(fuel) as fu_total from vehicle_usage where vehicle_id in('" + vech_all + "') group by vehicle_type,vehicle_id order by Vehicle_type,vehicle_id", con); cmd_km_count = new SqlCommand("select vehicle_type,vehicle_id,v.Reg_No,sum(travel_km) as km_total,sum(fuel) as fu_total from vehicle_usage vu,Vehicle_Master v where v.Veh_ID=vu.Vehicle_Id and vehicle_id in('" + vech_all + "') group by vehicle_type,vehicle_id,v.Reg_No order by Vehicle_type,vehicle_id", con); } SqlDataAdapter ad_km_count = new SqlDataAdapter(cmd_km_count); DataTable dt_km_count = new DataTable(); ad_km_count.Fill(dt_km_count); if (dt_km_count.Rows.Count > 0) { Fp_Fuel.Visible = true; btnprintmaster.Visible = true; //added by SRinath 8/10/2013 Fp_Fuel.Sheets[0].RowCount = 0; Fp_Fuel.Sheets[0].ColumnCount = 9; //Modified by SRinath 12/6/2014 Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 0].Text = "S.No"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 1].Text = "Vehicle Type"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 2].Text = "Vehicle Id"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 3].Text = "Driver Name"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 4].Text = "Registration No"; //Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 3].Text = "Date"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 5].Text = "Mileage"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 6].Text = "Total Travel KM"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 7].Text = "Total Fuel-(Lt)"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 8].Text = "Total Remaining KM"; Fp_Fuel.Sheets[0].ColumnHeader.Cells[Fp_Fuel.Sheets[0].ColumnHeader.RowCount - 1, 0].Column.HorizontalAlign = HorizontalAlign.Center; Fp_Fuel.Sheets[0].SetColumnMerge(1, FarPoint.Web.Spread.Model.MergePolicy.Always); Fp_Fuel.Sheets[0].SetColumnMerge(2, FarPoint.Web.Spread.Model.MergePolicy.Always); con.Close(); con.Open(); SqlCommand cmd_get_mileage = new SqlCommand("select * from vehicle_master where veh_id in('" + vech_all + "')", con); SqlDataAdapter ad_get_mileage = new SqlDataAdapter(cmd_get_mileage); DataTable dt_get_mileage = new DataTable(); ad_get_mileage.Fill(dt_get_mileage); if (dt_get_mileage.Rows.Count > 0) { int sno = 0; DataTable dt_remain = new DataTable(); DataColumn dc = new DataColumn(); dc.ColumnName = "vehid"; dt_remain.Columns.Add(dc); DataColumn dc_min = new DataColumn(); dc_min.ColumnName = "totkm"; dt_remain.Columns.Add(dc_min); DataRow dr; double tot_remain_km = 0; string temp_vehid = string.Empty; for (int i = 0; i < dt_km_count.Rows.Count; i++) { sno++; string veh_id = dt_km_count.Rows[i]["vehicle_id"].ToString(); string veh_type = dt_km_count.Rows[i]["vehicle_type"].ToString(); string travel_km = dt_km_count.Rows[i]["km_total"].ToString(); string fuel_tot = dt_km_count.Rows[i]["fu_total"].ToString(); string regno = dt_km_count.Rows[i]["Reg_No"].ToString(); string set_date = ""; string drivername = d2.GetFunction("select sm.staff_name from DriverAllotment da,staffmaster sm where sm.staff_code=da.Staff_Code and da.Vehicle_Id in('" + veh_id + "') "); DataView dv_mileage = new DataView(); dt_get_mileage.DefaultView.RowFilter = "veh_id='" + veh_id + "'"; dv_mileage = dt_get_mileage.DefaultView; string mileage = "0"; if (dv_mileage.Count > 0) { mileage = dv_mileage[0]["mileage"].ToString(); if (mileage == "") { mileage = "0"; } } if (fuel_tot == "") { fuel_tot = "0"; } if (temp_vehid == "" && dt_km_count.Rows.Count == 1) { tot_remain_km = tot_remain_km + ((Convert.ToDouble(mileage) * Convert.ToDouble(fuel_tot)) - Convert.ToDouble(travel_km)); dr = dt_remain.NewRow(); dr["vehid"] = veh_id; dr["totkm"] = tot_remain_km.ToString(); dt_remain.Rows.Add(dr); } else if ((temp_vehid != "" && temp_vehid != veh_id)) { dr = dt_remain.NewRow(); dr["vehid"] = temp_vehid; dr["totkm"] = tot_remain_km.ToString(); dt_remain.Rows.Add(dr); tot_remain_km = 0; } double plan_km = Convert.ToDouble(mileage) * Convert.ToDouble(fuel_tot); double actual_km = 0; double.TryParse(Convert.ToString(travel_km), out actual_km); double remain_km = plan_km - actual_km; tot_remain_km = tot_remain_km + remain_km; if (temp_vehid != "" && dt_km_count.Rows.Count != 1 && i == dt_km_count.Rows.Count - 1) { temp_vehid = veh_id; dr = dt_remain.NewRow(); dr["vehid"] = veh_id; dr["totkm"] = tot_remain_km.ToString(); dt_remain.Rows.Add(dr); } double remain_km_x = Math.Round(remain_km, 2); temp_vehid = veh_id; Fp_Fuel.Sheets[0].RowCount = Convert.ToInt32(Fp_Fuel.Sheets[0].RowCount) + 1; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 0].Text = sno.ToString(); Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 0].HorizontalAlign = HorizontalAlign.Center; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 1].Text = veh_type; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 1].VerticalAlign = VerticalAlign.Middle; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 2].Text = veh_id; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 2].VerticalAlign = VerticalAlign.Middle; //Added by Rajasekar 22/10/2018 Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 3].Text = drivername; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 3].VerticalAlign = VerticalAlign.Middle; //=================// //Added by srinath 12/6/2014 Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 4].Text = regno; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 4].VerticalAlign = VerticalAlign.Middle; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 5].Text = mileage; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 5].HorizontalAlign = HorizontalAlign.Center; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 5].VerticalAlign = VerticalAlign.Middle; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 6].Text = travel_km; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 6].HorizontalAlign = HorizontalAlign.Center; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 7].Text = fuel_tot; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 7].HorizontalAlign = HorizontalAlign.Center; Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 8].Text = remain_km_x.ToString(); Fp_Fuel.Sheets[0].Cells[Fp_Fuel.Sheets[0].RowCount - 1, 8].HorizontalAlign = HorizontalAlign.Center; } Fp_Fuel.Sheets[0].PageSize = Fp_Fuel.Sheets[0].RowCount; Fp_Fuel.Visible = true; btnprintmaster.Visible = true;//added by SRinath 8/10/2013 Fp_Fuel.SaveChanges(); FarPoint.Web.Spread.LabelCellType lbl_cell = new FarPoint.Web.Spread.LabelCellType(); lbl_cell.CssClass = "blinkytext"; for (int k = 0; k < Fp_Fuel.Sheets[0].RowCount; k++) { string vehi_id = Fp_Fuel.Sheets[0].Cells[k, 2].Text; DataView dv_remain = new DataView(); dt_remain.DefaultView.RowFilter = "vehid='" + vehi_id + "'"; dv_remain = dt_remain.DefaultView; if (dv_remain.Count > 0) { Fp_Fuel.Sheets[0].Cells[k, 6].CellType = lbl_cell; if (dv_remain.Count > 0) { double tot_km = Math.Round(Convert.ToDouble(dv_remain[0]["totkm"].ToString()), 2); Fp_Fuel.Sheets[0].Cells[k, 8].Text = tot_km.ToString(); } else { Fp_Fuel.Sheets[0].Cells[k, 8].Text = "0"; } Fp_Fuel.Sheets[0].Cells[k, 8].HorizontalAlign = HorizontalAlign.Center; Fp_Fuel.Sheets[0].Cells[k, 8].VerticalAlign = VerticalAlign.Middle; } } } } else { errmsg.Text = "No data found"; errmsg.Visible = true; Fp_Fuel.Sheets[0].RowCount = 0; errmsg.Font.Bold = true; Fp_Fuel.Visible = false; btnprintmaster.Visible = false;//added by SRinath 8/10/2013 } }