private void handlingMOUData(MOUBasicInfo basicInfo, MOUBasicInfo dateAndScopes)
{
//handle date display
basicInfo.mou_end_date_string = basicInfo.mou_end_date.ToString("dd'/'MM'/'yyyy");
basicInfo.mou_start_date_string = dateAndScopes.mou_start_date.ToString("dd'/'MM'/'yyyy");
basicInfo.scopes = dateAndScopes.scopes;
}
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 ActionResult editMOUBasicInfo(MOUBasicInfo input)
{
try
{
string id = Session["mou_detail_id"].ToString();
mou.editMOUBasicInfo(int.Parse(id), input);
return(Json("", JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(400));
}
}
public ActionResult getBasicInfo()
{
try
{
string id = Session["mou_detail_id"].ToString();
MOUBasicInfo data = mou.getBasicInfoMOU(int.Parse(id));
return(Json(data, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(400));
}
}
public MOUBasicInfo getBasicInfoMOU(int mou_id)
{
try
{
string sql_mouBasicInfo =
@"select
t1.mou_id,t1.mou_code,t2.office_abbreviation,t1.mou_end_date,
t5.mou_status_name,t4.reason,t1.evidence,t1.mou_note,
t1.office_id,t5.mou_status_id
from IA_Collaboration.MOU t1
inner join General.Office t2 on t1.office_id = t2.office_id
inner join
(select max([datetime]) as 'maxdate',mou_status_id, mou_id
from IA_Collaboration.MOUStatusHistory
group by mou_status_id, mou_id) t3 on t3.mou_id = t1.mou_id
inner join IA_Collaboration.MOUStatusHistory t4 on
t4.datetime = t3.maxdate and t4.mou_id = t4.mou_id and t4.mou_status_id = t3.mou_status_id
inner join IA_Collaboration.CollaborationStatus t5 on
t5.mou_status_id = t3.mou_status_id
where t1.mou_id = @mou_id ";
string sql_mouStartDateAndScopes =
@"select t2.*,t3.mou_start_date from
(select mou_id, STRING_AGG(scope_abbreviation,', ') as scopes from
(select distinct mou_id,scope_abbreviation from
(select mou_id,partner_id, scope_id
from IA_Collaboration.MOUPartnerScope t1a left join
IA_Collaboration.PartnerScope t2a
on t2a.partner_scope_id = t1a.partner_scope_id) tb1a
left join IA_Collaboration.CollaborationScope tb1b on
tb1a.scope_id = tb1b.scope_id
where mou_id = @mou_id) t1
group by mou_id) t2
left join
(select max(mou_start_date) as mou_start_date,mou_id
from IA_Collaboration.MOUPartner
where mou_id = @mou_id
group by mou_id) t3
on t3.mou_id = t2.mou_id ";
MOUBasicInfo basicInfo = db.Database.SqlQuery <MOUBasicInfo>(sql_mouBasicInfo,
new SqlParameter("mou_id", mou_id)).First();
MOUBasicInfo dateAndScopes = db.Database.SqlQuery <MOUBasicInfo>(sql_mouStartDateAndScopes,
new SqlParameter("mou_id", mou_id)).First();
handlingMOUData(basicInfo, dateAndScopes);
return(basicInfo);
}
catch (Exception ex)
{
throw ex;
}
}
