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