public ActionResult EditFuel(int consumption_value, string fuel_type, string date1, String equipmentId, int fuelid)
{
QuangHanhManufacturingEntities DBContext = new QuangHanhManufacturingEntities();
using (DbContextTransaction transaction = DBContext.Database.BeginTransaction())
{
try
{
// only taken by each department.
string department_id = Session["departID"].ToString();
//update
Equipment i = DBContext.Equipments.Find(equipmentId);
//check vehicle eq exist in department
FuelDB check = DBContext.Database.SqlQuery <FuelDB>(@"select equipment_id , equipment_name from
(select distinct e.equipment_id, e.equipment_name ,e.department_id from Equipment.Equipment e inner join Equipment.CategoryAttribute ea
on ea.Equipment_category_id = e.Equipment_category_id where
ea.Equipment_category_attribute_name = N'Số khung' or ea.Equipment_category_attribute_name = N'Số máy') as t
where department_id = @department_id
AND equipment_id = @equipmentId "
, new SqlParameter("department_id", department_id)
, new SqlParameter("equipmentId", equipmentId)
).First();
Supply s = DBContext.Database.SqlQuery <Supply>("select * from Supply.Supply where supply_id=@supply_id", new SqlParameter("supply_id", fuel_type)).First();
FuelDB f = DBContext.Database.SqlQuery <FuelDB>("select * from Equipment.FuelActivitiesConsumption where fuel_id=" + fuelid + "").First();
string date = DateTime.ParseExact(date1, "dd/MM/yyyy", null).ToString("MM-dd-yyyy");
DBContext.Database.ExecuteSqlCommand("UPDATE Equipment.FuelActivitiesConsumption set fuel_type =@fuel_type, [date] =@date1, consumption_value = @consumption_value, equipment_id = @equipmentId where fuel_id= @fuelid",
new SqlParameter("fuel_type", fuel_type), new SqlParameter("date1", date), new SqlParameter("consumption_value", consumption_value), new SqlParameter("equipmentId", equipmentId), new SqlParameter("fuelId", fuelid));
EditSupply_duphong(fuel_type, consumption_value);
//get old and new.
DBContext.SaveChanges();
transaction.Commit();
return(new HttpStatusCodeResult(201));
}
catch (Exception ex)
{
transaction.Rollback();
string output = "";
if (DBContext.Database.SqlQuery <Equipment>("SELECT * FROM Equipment.Equipment WHERE equipment_id = N'" + equipmentId + "'").Count() == 0)
{
output += "Mã thiết bị không tồn tại\n";
}
if (DBContext.Supplies.Where(x => (x.supply_id == fuel_type)).Count() == 0)
{
output += "Mã Nhiên Liệu không tồn tại\n";
}
if (output == "")
{
output += "Có lỗi xảy ra, xin vui lòng nhập lại";
}
Response.Write(output);
return(new HttpStatusCodeResult(400));
}
}
}
public ActionResult getFuelID(int fuelid)
{
//DateTime date = DateTime.Parse(date1);
try
{
QuangHanhManufacturingEntities DBContext = new QuangHanhManufacturingEntities();
FuelDB activity = DBContext.Database.SqlQuery <FuelDB>(@"select f.[date], f.equipment_id, t.equipment_name, f.fuel_type, f.consumption_value, s.unit,s.supply_name,fuel_id from(select distinct e.equipment_id, e.equipment_name
from Equipment.Equipment e inner join Equipment.CategoryAttribute ea
on ea.Equipment_category_id = e.Equipment_category_id where
ea.Equipment_category_attribute_name = N'Số khung' or ea.Equipment_category_attribute_name = 'Số máy')
as t join Equipment.FuelActivitiesConsumption f
on t.equipment_id = f.equipment_id
join Supply.Supply s on s.supply_id = f.fuel_type where fuel_id=@fuelid", new SqlParameter("fuelid", fuelid)).First();
activity.stringDate = activity.date.ToString("dd/MM/yyyy");
return(Json(activity));
}
catch (Exception)
{
Response.Write("Có lỗi xảy ra, xin vui lòng nhập lại");
return(new HttpStatusCodeResult(400));
}
}
public ActionResult AddFuel(int consumption_value, string fuel_type, string date1, String equipmentId)
{
QuangHanhManufacturingEntities DBContext = new QuangHanhManufacturingEntities();
//bug : depend on supply_tieuhao.
using (DbContextTransaction transaction = DBContext.Database.BeginTransaction())
{
try
{
// only taken by each department.
string department_id = Session["departID"].ToString();
Equipment e = DBContext.Equipments.Find(equipmentId);
//check vehicle eq exist in department
FuelDB check = DBContext.Database.SqlQuery <FuelDB>(@"select equipment_id , equipment_name from
(select distinct e.equipment_id, e.equipment_name ,e.department_id from Equipment.Equipment e inner join Equipment.CategoryAttribute ea
on ea.Equipment_category_id = e.Equipment_category_id where
ea.Equipment_category_attribute_name = N'Số khung' or ea.Equipment_category_attribute_name = N'Số máy') as t
where department_id = @department_id
AND equipment_id = @equipmentId "
, new SqlParameter("department_id", department_id)
, new SqlParameter("equipmentId", equipmentId)
).First();
DateTime date = DateTime.ParseExact(date1, "dd/MM/yyyy", null);
Supply s = DBContext.Supplies.Where(x => x.supply_id == fuel_type).First();
EditSupply_duphong(fuel_type, consumption_value);
FuelActivitiesConsumption f = DBContext.Database.SqlQuery <FuelActivitiesConsumption>("select * from Equipment.FuelActivitiesConsumption " +
"where fuel_type=@fueltype and equipment_id=@equipmentid and date=@date", new SqlParameter("fueltype", fuel_type), new SqlParameter("equipmentid", equipmentId), new SqlParameter("date", date)).FirstOrDefault();
if (f != null)
{
f.consumption_value = f.consumption_value + consumption_value;
DBContext.Entry(f).State = EntityState.Modified;
}
else
{
FuelActivitiesConsumption fuel_Activities_Consumption = new FuelActivitiesConsumption()
{
department_id = department_id,
consumption_value = consumption_value,
equipment_id = equipmentId,
fuel_type = fuel_type,
date = DateTime.ParseExact(date1, "dd/MM/yyyy", null)
};
DBContext.FuelActivitiesConsumptions.Add(fuel_Activities_Consumption);
DBContext.SaveChanges();
}
//Update :
//get new
DBContext.SaveChanges();
transaction.Commit();
return(Json("", JsonRequestBehavior.AllowGet));
}
catch (Exception)
{
transaction.Rollback();
string output = "";
if (DBContext.Equipments.Where(x => x.equipment_id == equipmentId).Count() == 0)
{
output += "Mã thiết bị không tồn tại\n";
}
if (output == "")
{
output += "Có lỗi xảy ra, xin vui lòng nhập lại";
}
Response.Write(output);
return(new HttpStatusCodeResult(400));
}
}
}
public ActionResult AddActivity(float quantity, string activity_name, int hours_per_day, string date1, String equipmentId)
{
QuangHanhManufacturingEntities DBContext = new QuangHanhManufacturingEntities();
Activity a = new Activity();
using (DbContextTransaction transaction = DBContext.Database.BeginTransaction())
{
string output = "";
//fix bug negative number.
if (quantity < 0 || hours_per_day <= 0)
{
return(new HttpStatusCodeResult(400));
}
try
{
// only taken by each department.
string department_id = Session["departID"].ToString();
//note : need to be fixed
Equipment e = DBContext.Equipments.Find(equipmentId);
//check vehicle eq exist in department
FuelDB f = DBContext.Database.SqlQuery <FuelDB>(@"select equipment_id , equipment_name from
(select distinct e.equipment_id, e.equipment_name ,e.department_id from Equipment.Equipment e inner join Equipment.CategoryAttribute ea
on ea.Equipment_category_id = e.Equipment_category_id where
ea.Equipment_category_attribute_name = N'Số khung' or ea.Equipment_category_attribute_name = N'Số máy') as t
where department_id = @department_id
AND equipment_id = @equipmentId "
, new SqlParameter("department_id", department_id)
, new SqlParameter("equipmentId", equipmentId)
).First();
a.equipment_id = e.equipment_id;
string date = DateTime.ParseExact(date1, "dd/MM/yyyy", null).ToString("yyyy-MM-dd");
a.date = DateTime.Parse(date);
a.quantity = quantity;
a.hours_per_day = hours_per_day;
a.activity_name = activity_name;
DBContext.Activities.Add(a);
DBContext.SaveChanges();
//update total_hour
int count = DBContext.Database.SqlQuery <int>("select total_operating_hours from Equipment.Equipment where equipment_id = @equipmentId", new SqlParameter("equipmentId", equipmentId)).First();
if (count == 0)
{
//add first
DBContext.Database.ExecuteSqlCommand("update Equipment.Equipment set total_operating_hours = @hour where equipment_id = @equipmentId",
new SqlParameter("hour", hours_per_day),
new SqlParameter("equipmentId", equipmentId));
}
else
{
//count total hours.
double hours = DBContext.Database.SqlQuery <double>("" +
" select sum(hours_per_day) as total from Equipment.Activity " +
" where equipment_id = @equipmentId"
, new SqlParameter("equipmentId", equipmentId)).First();
//fix bug
int totalHour = (int)hours;
DBContext.Database.ExecuteSqlCommand("update Equipment.Equipment set total_operating_hours = @hour where equipment_id = @equipmentId",
new SqlParameter("hour", totalHour),
new SqlParameter("equipmentId", equipmentId));
}
transaction.Commit();
return(Json("", JsonRequestBehavior.AllowGet));
}
catch (Exception)
{
transaction.Rollback();
if (DBContext.Database.SqlQuery <Equipment>("SELECT * FROM Equipment.Equipment WHERE equipment_id = N'" + equipmentId + "'").Count() == 0)
{
output += "Mã thiết bị không tồn tại\n";
}
if (output == "")
{
output += "Có lỗi xảy ra, xin vui lòng nhập lại";
}
Response.Write(output);
return(new HttpStatusCodeResult(400));
}
}
}
public ActionResult Edit(float quantity, string activity_name, int hours_per_day, string date1, String equipmentId, int activityid)
{
QuangHanhManufacturingEntities DBContext = new QuangHanhManufacturingEntities();
using (DbContextTransaction transaction = DBContext.Database.BeginTransaction())
{
try
{
// only taken by each department.
string department_id = Session["departID"].ToString();
Equipment i = DBContext.Equipments.Find(equipmentId);
//check vehicle eq exist in department
FuelDB f = DBContext.Database.SqlQuery <FuelDB>(@"select equipment_id , equipment_name from
(select distinct e.equipment_id, e.equipment_name ,e.department_id from Equipment.Equipment e inner join Equipment.CategoryAttribute ea
on ea.Equipment_category_id = e.Equipment_category_id where
ea.Equipment_category_attribute_name = N'Số khung' or ea.Equipment_category_attribute_name = N'Số máy') as t
where department_id = @department_id
AND equipment_id = @equipmentId "
, new SqlParameter("department_id", department_id)
, new SqlParameter("equipmentId", equipmentId)
).First();
//Activity q = DBContext.Activities.Where(x => x.activityid == activityid).SingleOrDefault();
Activity q = DBContext.Activities.Find(activityid);
Activity fixBug = DBContext.Activities.Find(activityid);
string oldEq = fixBug.equipment_id;
q.equipment_id = i.equipment_id;
string date = DateTime.ParseExact(date1, "dd/MM/yyyy", null).ToString("MM-dd-yyyy");
q.date = DateTime.Parse(date);
q.hours_per_day = hours_per_day;
q.quantity = quantity;
q.activity_name = activity_name;
q.activity_id = activityid;
DBContext.Entry(q).State = EntityState.Modified;
DBContext.SaveChanges();
//after update activity.
//get old and new.
//string oldEq = q.equipmentid;
string newEq = equipmentId;
//update old:
double hoursOld = DBContext.Database.SqlQuery <double>(
@" select case when sum(hours_per_day) is null then 0 else sum(hours_per_day) end as total from Equipment.Activity
where equipment_id = @equipmentId"
, new SqlParameter("", oldEq)).First();
int totalHourOld = (int)hoursOld;
DBContext.Database.ExecuteSqlCommand("update Equipment.Equipment set total_operating_hours = @hour where equipment_id = @equipmentId",
new SqlParameter("hour", totalHourOld),
new SqlParameter("equipmentId", oldEq));
//update new:
double hoursNew = DBContext.Database.SqlQuery <double>("" +
" select sum(hours_per_day) as total from Equipment.Activity " +
" where equipment_id = @equipmentId"
, new SqlParameter("equipmentId", newEq)).First();
int totalHourNew = (int)hoursNew;
DBContext.Database.ExecuteSqlCommand("update Equipment.Equipment set total_operating_hours = @hour where equipment_id = @equipmentId",
new SqlParameter("hour", totalHourNew),
new SqlParameter("equipmentId", newEq));
transaction.Commit();
return(new HttpStatusCodeResult(201));
}
catch (Exception ex)
{
transaction.Rollback();
string output = "";
if (DBContext.Database.SqlQuery <Equipment>("SELECT * FROM Equipment.Equipment WHERE equipment_id = N'" + equipmentId + "'").Count() == 0)
{
output += "Mã thiết bị không tồn tại\n";
}
if (output == "")
{
output += "Có lỗi xảy ra, xin vui lòng nhập lại";
}
Response.Write(output);
return(new HttpStatusCodeResult(400));
}
}
}
