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
}
}
