public IHttpActionResult InsertVehicleEntry(VehicleEntryModel vehEntryModel)
{
SqlConnection con;
string strInsert;
SqlCommand cmdInsert;
DateTime dt1 = DateTime.Now;
con = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["Servosms"]);
try
{
con.Open();
strInsert = "Insert Vehicleentry(vehicledetail_id,Vehicle_Type,Vehicle_no,vehicle_name,RTO_Reg_Val_yrs,Model_name,RTO_Reg_No,Vehicle_Man_Date,Insurance_No,Meter_Reading,Insurance_validity,Vehicle_Route,Insurance_Comp_Name,Fuel_Used,Fuel_Used_Qty,start_Fuel_Qty,Engine_Oil,Engine_Oil_Qty,Engine_Oil_Dt,Engine_Oil_km,Gear_Oil,Gear_Oil_Qty,Gear_Oil_Dt,Gear_Oil_Km,Brake_Oil,Brake_Oil_Qty,Brake_Oil_Dt,Brake_Oil_km,Coolent,Coolent_Qty,Coolent_Dt,Coolent_Km,Grease,Grease_Qty,Grease_Dt,Grease_Km,Trans_Oil,Trans_Oil_Qty,Trans_Oil_Dt,Trans_Oil_Km,Vehicle_Avg)"
+ "values(@vehicledetail_id,@Vehicle_Type,@Vehicle_no,@vehicle_name,@RTO_Reg_Val_yrs,@Model_name,@RTO_Reg_No,@Vehicle_Man_Date,@Insurance_No,@Meter_Reading,@Insurance_validity,@Vehicle_Route,@Insurance_Comp_Name,@Fuel_Used,@Fuel_Used_Qty,@start_Fuel_Qty,@Engine_Oil,@Engine_Oil_Qty,@Engine_Oil_Dt,@Engine_Oil_km,@Gear_Oil,@Gear_Oil_Qty,@Gear_Oil_Dt,@Gear_Oil_Km,@Brake_Oil,@Brake_Oil_Qty,@Brake_Oil_Dt,@Brake_Oil_km,@Coolent,@Coolent_Qty,@Coolent_Dt,@Coolent_Km,@Grease,@Grease_Qty,@Grease_Dt,@Grease_Km,@Trans_Oil,@Trans_Oil_Qty,@Trans_Oil_Dt,@Trans_Oil_Km,@Vehicle_Avg)";
cmdInsert = new SqlCommand(strInsert, con);
cmdInsert.Parameters.AddWithValue("@vehicledetail_id", vehEntryModel.Vehicle_ID);
cmdInsert.Parameters.AddWithValue("@Vehicle_Type", vehEntryModel.VehicleType2);
cmdInsert.Parameters.AddWithValue("@Vehicle_no", vehEntryModel.Vehicleno);
cmdInsert.Parameters.AddWithValue("@Vehicle_name", vehEntryModel.Vehiclenm);
cmdInsert.Parameters.AddWithValue("@RTO_Reg_Val_yrs", vehEntryModel.RTO_Reg_Val_yrs);
cmdInsert.Parameters.AddWithValue("@Model_name", vehEntryModel.Model_name);
cmdInsert.Parameters.AddWithValue("@RTO_Reg_No", vehEntryModel.RTO_Reg_No);
cmdInsert.Parameters.AddWithValue("@Vehicle_Man_Date", vehEntryModel.Vehicle_Man_Date);
cmdInsert.Parameters.AddWithValue("@Insurance_No", vehEntryModel.Insurance_No);
cmdInsert.Parameters.AddWithValue("@Meter_Reading", vehEntryModel.Meter_Reading);
cmdInsert.Parameters.AddWithValue("@Insurance_validity", vehEntryModel.Insurance_validity.Trim());
SqlDataReader SqlDtr = null;
string route_id = "";
//Fetch the route id for selected route from route table.
dbobj.SelectQuery("Select route_id from Route where route_name='" + vehEntryModel.RouteName.Trim() + "'", ref SqlDtr);
if (SqlDtr.Read())
{
route_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
cmdInsert.Parameters.AddWithValue("@Vehicle_Route", route_id);
cmdInsert.Parameters.AddWithValue("@Insurance_Comp_name", vehEntryModel.Insurance_Comp_name.Trim());
string prod_id = "";
cmdInsert.Parameters.AddWithValue("@Fuel_Used", vehEntryModel.Fuel_Used.Trim());
cmdInsert.Parameters.AddWithValue("@Fuel_Used_Qty", vehEntryModel.Fuel_Used_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Start_Fuel_Qty", vehEntryModel.Start_Fuel_Qty.Trim());
//Fetch the product id for selected product of type Engine Oil from table products.
if (vehEntryModel.EngineOil.ToString() != "")
{
string[] strArr = vehEntryModel.EngineOil.Split(new char[] { ':' }, vehEntryModel.EngineOil.Length);
prod_id = "";
dbobj.SelectQuery("Select prod_id from products where prod_name='" + strArr[0].Trim() + "' and pack_type ='" + strArr[1].Trim() + "' and Category like 'Engine Oil%'", ref SqlDtr);
if (SqlDtr.Read())
{
prod_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
}
else
{
prod_id = "0";
}
cmdInsert.Parameters.AddWithValue("@Engine_Oil", prod_id);
cmdInsert.Parameters.AddWithValue("@Engine_Oil_Qty", vehEntryModel.Engine_Oil_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Engine_Oil_Dt", vehEntryModel.Engine_Oil_Dt.Trim());
cmdInsert.Parameters.AddWithValue("@Engine_Oil_km", vehEntryModel.Engine_Oil_km);
//Fetch the product id for selected product of type Gear Oil from table products.
if (vehEntryModel.Gear_Oil.ToString() != "")
{
string[] strArr = vehEntryModel.Gear_Oil.Split(new char[] { ':' }, vehEntryModel.Gear_Oil.Length);
prod_id = "";
dbobj.SelectQuery("Select prod_id from products where prod_name='" + strArr[0].Trim() + "' and pack_type ='" + strArr[1].Trim() + "' and Category like 'Gear Oil%'", ref SqlDtr);
if (SqlDtr.Read())
{
prod_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
}
else
{
prod_id = "0";
}
cmdInsert.Parameters.AddWithValue("@Gear_Oil", prod_id);
cmdInsert.Parameters.AddWithValue("@Gear_Oil_Qty", vehEntryModel.Gear_Oil_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Gear_Oil_Dt", vehEntryModel.Gear_Oil_Dt.Trim());
cmdInsert.Parameters.AddWithValue("@Gear_Oil_km", vehEntryModel.Gear_Oil_km.Trim());
//Fetch the product id for selected product of type Brake Oil from table products.
if (vehEntryModel.Brake_Oil.ToString() != "")
{
string[] strArr = vehEntryModel.Brake_Oil.Split(new char[] { ':' }, vehEntryModel.Brake_Oil.Length);
prod_id = "";
dbobj.SelectQuery("Select prod_id from products where prod_name='" + strArr[0].Trim() + "' and pack_type ='" + strArr[1].Trim() + "' and Category like 'Brake Oil%'", ref SqlDtr);
if (SqlDtr.Read())
{
prod_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
}
else
{
prod_id = "0";
}
cmdInsert.Parameters.AddWithValue("@Brake_Oil", prod_id);
cmdInsert.Parameters.AddWithValue("@Brake_Oil_Qty", vehEntryModel.Brake_Oil_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Brake_Oil_Dt", vehEntryModel.Brake_Oil_Dt.Trim());
cmdInsert.Parameters.AddWithValue("@Brake_Oil_km", vehEntryModel.Brake_Oil_km.Trim());
//Fetch the product id for selected product of type Coolent from table products.
if (vehEntryModel.Coolent.ToString() != "")
{
string[] strArr = vehEntryModel.Coolent.Split(new char[] { ':' }, vehEntryModel.Coolent.Length);
prod_id = "";
dbobj.SelectQuery("Select prod_id from products where prod_name='" + strArr[0].Trim() + "' and pack_type ='" + strArr[1].Trim() + "' and Category like 'Collent%'", ref SqlDtr);
if (SqlDtr.Read())
{
prod_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
}
else
{
prod_id = "0";
}
cmdInsert.Parameters.AddWithValue("@Coolent", prod_id);
cmdInsert.Parameters.AddWithValue("@Coolent_Qty", vehEntryModel.Coolent_Oil_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Coolent_Dt", vehEntryModel.Coolent_Oil_Dt.Trim());
cmdInsert.Parameters.AddWithValue("@Coolent_km", vehEntryModel.Coolent_km.Trim());
//Fetch the product id for selected product of type Grease from table products.
if (vehEntryModel.Grease.ToString() != "")
{
string[] strArr = vehEntryModel.Grease.Split(new char[] { ':' }, vehEntryModel.Grease.Length);
prod_id = "";
dbobj.SelectQuery("Select prod_id from products where prod_name='" + strArr[0].Trim() + "' and pack_type ='" + strArr[1].Trim() + "' and Category like 'Grease%'", ref SqlDtr);
if (SqlDtr.Read())
{
prod_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
}
else
{
prod_id = "0";
}
cmdInsert.Parameters.AddWithValue("@Grease", prod_id);
cmdInsert.Parameters.AddWithValue("@Grease_Qty", vehEntryModel.Grease_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Grease_Dt", vehEntryModel.Grease_Dt.Trim());
cmdInsert.Parameters.AddWithValue("@Grease_km", vehEntryModel.Grease_km.Trim());
//Fetch the product id for selected product of type Transmission Oil from table products.
if (vehEntryModel.Trans_Oil.ToString() != "")
{
string[] strArr = vehEntryModel.Trans_Oil.Split(new char[] { ':' }, vehEntryModel.Trans_Oil.Length);
prod_id = "";
dbobj.SelectQuery("Select prod_id from products where prod_name='" + strArr[0].Trim() + "' and pack_type ='" + strArr[1].Trim() + "' and Category like 'Transmission Oil%'", ref SqlDtr);
if (SqlDtr.Read())
{
prod_id = SqlDtr.GetValue(0).ToString();
}
SqlDtr.Close();
}
else
{
prod_id = "0";
}
cmdInsert.Parameters.AddWithValue("@Trans_Oil", prod_id);
cmdInsert.Parameters.AddWithValue("@Trans_Oil_Qty", vehEntryModel.Trans_Oil_Qty.Trim());
cmdInsert.Parameters.AddWithValue("@Trans_Oil_Dt", vehEntryModel.Trans_Oil_Dt.Trim());
cmdInsert.Parameters.AddWithValue("@Trans_Oil_km", vehEntryModel.Trans_Oil_km.Trim());
cmdInsert.Parameters.AddWithValue("@Vehicle_Avg", vehEntryModel.Vehicle_Avg.Trim());
cmdInsert.ExecuteNonQuery();
con.Close();
return(Ok(true));
}
catch (Exception ex)
{
return(Content(HttpStatusCode.NotFound, "Failed to insert Vehicle entry ID."));
}
}
public IHttpActionResult GetDropVehicleID_SelectedData(string VehicleID)
{
VehicleEntryModel vehEntryModel = null;
try
{
SqlDataReader SqlDtr = null;
SqlDataReader SqlDtr1 = null;
dbobj.SelectQuery("Select * from vehicleentry where vehicledetail_id = " + VehicleID.Trim(), ref SqlDtr);
if (SqlDtr.Read())
{
vehEntryModel = new VehicleEntryModel();
vehEntryModel.VehicleType2 = SqlDtr["Vehicle_Type"].ToString().Trim();
vehEntryModel.Vehicleno = SqlDtr["Vehicle_No"].ToString().Trim();
vehEntryModel.Vehiclenm = SqlDtr["Vehicle_Name"].ToString().Trim();
vehEntryModel.RTO_Reg_Val_yrs = SqlDtr["RTO_Reg_Val_Yrs"].ToString().Trim();
vehEntryModel.Model_name = SqlDtr["Model_Name"].ToString().Trim();
vehEntryModel.RTO_Reg_No = SqlDtr["RTO_Reg_No"].ToString().Trim();
vehEntryModel.Vehicle_Man_Date = SqlDtr["Vehicle_man_date"].ToString().Trim();
vehEntryModel.Insurance_No = SqlDtr["Insurance_No"].ToString().Trim();
vehEntryModel.Meter_Reading = SqlDtr["Meter_Reading"].ToString().Trim();
vehEntryModel.Insurance_validity = SqlDtr["Insurance_Validity"].ToString().Trim();
string route_name = "";
dbobj.SelectQuery("Select route_name from route where route_id=" + SqlDtr["Vehicle_Route"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
route_name = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.RouteName = route_name;
vehEntryModel.Insurance_Comp_name = SqlDtr["Insurance_Comp_Name"].ToString().Trim();
vehEntryModel.Fuel_Used = SqlDtr["Fuel_Used"].ToString().Trim();
vehEntryModel.Fuel_Used_Qty = SqlDtr["Fuel_Used_Qty"].ToString().Trim();
vehEntryModel.Start_Fuel_Qty = SqlDtr["Start_Fuel_Qty"].ToString().Trim();
string engine_oil = "";
dbobj.SelectQuery("Select prod_name+':'+pack_type from products where Category like 'Engine Oil%' and prod_id=" + SqlDtr["Engine_Oil"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
engine_oil = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.EngineOil = engine_oil;
vehEntryModel.Engine_Oil_Qty = SqlDtr["Engine_Oil_Qty"].ToString().Trim();
vehEntryModel.Engine_Oil_Dt = SqlDtr["Engine_Oil_Dt"].ToString().Trim();
vehEntryModel.Engine_Oil_km = SqlDtr["Engine_OIl_Km"].ToString().Trim();
string gear_oil = "";
dbobj.SelectQuery("Select prod_name+':'+pack_type from products where Category like 'Gear Oil%' and prod_id=" + SqlDtr["Gear_Oil"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
gear_oil = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.Gear_Oil = gear_oil;
vehEntryModel.Gear_Oil_Qty = SqlDtr["Gear_Oil_Qty"].ToString().Trim();
vehEntryModel.Gear_Oil_Dt = SqlDtr["Gear_Oil_Dt"].ToString().Trim();
vehEntryModel.Gear_Oil_km = SqlDtr["Gear_OIl_Km"].ToString().Trim();
string brake_oil = "";
dbobj.SelectQuery("Select prod_name+':'+pack_type from products where Category like 'Brake Oil%' and prod_id=" + SqlDtr["Brake_Oil"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
brake_oil = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.Brake_Oil = brake_oil;
vehEntryModel.Brake_Oil_Qty = SqlDtr["Brake_Oil_Qty"].ToString().Trim();
vehEntryModel.Brake_Oil_Dt = SqlDtr["Brake_Oil_Dt"].ToString().Trim();
vehEntryModel.Brake_Oil_km = SqlDtr["Brake_OIl_Km"].ToString().Trim();
string coolent = "";
dbobj.SelectQuery("Select prod_name+':'+pack_type from products where Category like 'Collent%' and prod_id=" + SqlDtr["Coolent"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
coolent = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.Coolent = coolent;
vehEntryModel.Coolent_Oil_Qty = SqlDtr["Coolent_Qty"].ToString().Trim();
vehEntryModel.Coolent_Oil_Dt = SqlDtr["Coolent_Dt"].ToString().Trim();
vehEntryModel.Coolent_km = SqlDtr["Coolent_Km"].ToString().Trim();
string grease = "";
dbobj.SelectQuery("Select prod_name+':'+pack_type from products where Category like 'Grease%' and prod_id=" + SqlDtr["Grease"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
grease = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.Grease = grease;
vehEntryModel.Grease_Qty = SqlDtr["Grease_Qty"].ToString().Trim();
vehEntryModel.Grease_Dt = SqlDtr["Grease_Dt"].ToString().Trim();
vehEntryModel.Grease_km = SqlDtr["grease_Km"].ToString().Trim();
string trans_oil = "";
dbobj.SelectQuery("Select prod_name+':'+pack_type from products where Category like 'Transmission Oil%' and prod_id=" + SqlDtr["Trans_OIl"].ToString().Trim(), ref SqlDtr1);
if (SqlDtr1.Read())
{
trans_oil = SqlDtr1.GetValue(0).ToString();
}
SqlDtr1.Close();
vehEntryModel.Trans_Oil = trans_oil;
vehEntryModel.Trans_Oil_Qty = SqlDtr["Trans_OIl_Qty"].ToString().Trim();
vehEntryModel.Trans_Oil_Dt = SqlDtr["Trans_OIl_Dt"].ToString().Trim();
vehEntryModel.Trans_Oil_km = SqlDtr["Trans_Oil_Km"].ToString().Trim();
vehEntryModel.Vehicle_Avg = SqlDtr["Vehicle_Avg"].ToString();
//checkPrevileges();
}
SqlDtr.Close();
if (vehEntryModel == null)
{
return(Content(HttpStatusCode.NotFound, "Failed to get data Vehicle entry ID related data."));
}
return(Ok(vehEntryModel));
}
catch (Exception ex)
{
return(Content(HttpStatusCode.NotFound, "Failed to get data Vehicle entry ID related data."));
}
}
