public void editMOUBasicInfo(int mou_id, MOUBasicInfo newBasicInfo)
{
using (DbContextTransaction transaction = db.Database.BeginTransaction())
{
try
{
DateTime mou_end_date = DateTime.ParseExact(newBasicInfo.mou_end_date_string, "dd/MM/yyyy", CultureInfo.InvariantCulture);
//update basicInfo
MOU mou = db.MOUs.Find(mou_id);
mou.mou_code = newBasicInfo.mou_code;
mou.mou_end_date = mou_end_date;
mou.mou_note = newBasicInfo.mou_note;
mou.evidence = newBasicInfo.evidence;
mou.office_id = newBasicInfo.office_id;
db.Entry(mou).State = EntityState.Modified;
db.SaveChanges();
//update MOUStatusHistory
MOUStatusHistory m = new MOUStatusHistory();
m.mou_status_id = newBasicInfo.mou_status_id;
m.reason = newBasicInfo.reason;
m.mou_id = mou_id;
m.datetime = DateTime.Now;
db.MOUStatusHistories.Add(m);
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
return;
}
public void UpdateStatusMOU()
{
//get current date
//get all expired ActiveMOU.
//if number > 0: update status for MOU: Active => Inactive.
using (DbContextTransaction transaction = db.Database.BeginTransaction())
{
try
{
string sql_expired = @"select mou_id from IA_Collaboration.MOU tb1 where tb1.mou_end_date < @current_date
and tb1.mou_id in(
select t1.mou_id from IA_Collaboration.MOUStatusHistory t1
inner join
(select max(datetime) as max_date,mou_id from IA_Collaboration.MOUStatusHistory
group by mou_id) t2 on
t1.datetime = t2.max_date and t1.mou_id = t2.mou_id
where mou_status_id = 1)";
List <int> mouIdList = db.Database.SqlQuery <int>(sql_expired,
new SqlParameter("current_date", DateTime.Now)).ToList();
if (mouIdList.Count > 0)
{
foreach (int id in mouIdList)
{
MOUStatusHistory mou = new MOUStatusHistory();
mou.mou_id = id;
mou.mou_status_id = 2;
mou.datetime = DateTime.Now;
db.MOUStatusHistories.Add(mou);
db.SaveChanges();
}
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
