//= = = = = = = = = = = = = = = CONSTRUCTOR FULL METADATA LIST = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
public QuestionMDViewModel( int qId )
{
q = db.OBS_QUESTION.Find(qId);
if (q != null)
{
// Add all Metadata List to the QuestionMD Object
var tempMD = from t1 in db.OBS_QUESTION_METADATA
join t2 in db.OBS_QUEST_ASSGND_MD.Where(item => item.obs_question_id == qId && DateTime.Today >= item.obs_qad_eff_st_dt && DateTime.Today < item.obs_qad_eff_end_dt)
on t1.obs_quest_md_id equals t2.obs_quest_md_id into t1Group
from t2 in t1Group.DefaultIfEmpty()
select new
{
md_id = t1.obs_quest_md_id,
mdValue = t1.obs_quest_md_value,
mdCat = t1.obs_quest_md_cat,
mdSelected =( (t2 != null) || (DateTime.Today >= t2.obs_qad_eff_st_dt && DateTime.Today < t2.obs_qad_eff_end_dt )? true : false)
//xmdSelected = (t1Group == null) ? false : true
};
foreach (var mdNew in tempMD)
{
metaData x = new metaData();
x.obs_quest_md_id = mdNew.md_id;
x.obs_quest_md_value = mdNew.mdValue;
x.obs_quest_md_cat = mdNew.mdCat;
if (mdNew.mdSelected)
{
qAssignedMD.Add(x);
preMetaDataIds.Add(mdNew.md_id);
}
else
{
qUnassignedMD.Add(x);
}
}
var temp_cft = (from q in db.OBS_QUEST_ANS_TYPES
join f in db.OBS_COL_FORM_QUESTIONS
on q.obs_qat_id equals f.obs_qat_id
where q.obs_question_id == qId && (q.obs_qat_end_eff_dt==null || q.obs_qat_end_eff_dt>DateTime.Now)
select f.obs_cft_id).Distinct();
foreach(var cft in temp_cft)
{
OBS_COLLECT_FORM_TMPLT x = new OBS_COLLECT_FORM_TMPLT();
x = db.OBS_COLLECT_FORM_TMPLT.Single(item => item.obs_cft_id == cft);
forms.Add(x);
}
}
}
public ActionResult Create(OBS_COLLECT_FORM_TMPLT oBS_COLLECT_FORM_TMPLT, FormCollection formData)
{
//if (ModelState.IsValid)
//{
// db.OBS_COLLECT_FORM_TMPLT.Add(oBS_COLLECT_FORM_TMPLT);
// db.SaveChanges();
// return RedirectToAction("Index");
//}
//ViewBag.dsc_cust_id = new SelectList(db.DSC_CUSTOMER, "dsc_cust_id", "dsc_cust_name", oBS_COLLECT_FORM_TMPLT.dsc_cust_id);
//ViewBag.dsc_lc_id = new SelectList(db.DSC_LC, "dsc_lc_id", "dsc_lc_name", oBS_COLLECT_FORM_TMPLT.dsc_lc_id);
//ViewBag.obs_type_id = new SelectList(db.OBS_TYPE, "obs_type_id", "obs_type_name", oBS_COLLECT_FORM_TMPLT.obs_type_id);
//return View(oBS_COLLECT_FORM_TMPLT);
return RedirectToAction("Index");
}
public oCollectionForm(int id)
{//Create the Collection Form Data (Header Info) from the Id passed as a parameter
cft_id = id;
//Initialize required values (Default values for when Id=0 or Id not found in the database)
//The Assumpsion is that we are in "add" mode
screen_Title = "Collection Form Creation";
cft_editMode = "add";
cft_Title = "";
cft_SubTitle = "";
//cft_obsType = q.cft_obsType;
//cft_Cust = q.cft_Cust;
//cft_LC = q.cft_LC;
//cft_Status = q.cft_Status;
cft_isPublished = "NOT PUBLISHED";
hasInstances = false;
//hasNewVersion = false;
cft_new_vers_cft_id = 0;
previous_vers_cft_id = 0;
manageAction = "";
cft_Nbr = 0;
cft_Version = 0;
colFormSections = new List<CollectionFormSection>();
// If the Form Id is greater than zero, populate all values fron the Database
if (cft_id > 0) {
var q = (from A in db.OBS_COLLECT_FORM_TMPLT
join B in db.OBS_TYPE //First Table Left join
on A.obs_type_id equals B.obs_type_id
into tl_b
where A.obs_cft_id == cft_id
from B in tl_b.DefaultIfEmpty()
join C in db.DSC_CUSTOMER //Second Table Left join
on A.dsc_cust_id equals C.dsc_cust_id into tl_c
from C in tl_c.DefaultIfEmpty()
join D in db.DSC_LC //Second Table Left join
on A.dsc_lc_id equals D.dsc_lc_id into tl_d
from D in tl_d.DefaultIfEmpty()
select new
{
cft_id = A.obs_cft_id,
cft_Nbr = A.obs_cft_nbr,
cft_Version = A.obs_cft_ver,
cft_Title = A.obs_cft_title,
cft_SubTitle = A.obs_cft_subtitle,
cft_obsType = B.obs_type_name,
cft_Cust = C.dsc_cust_name,
cft_LC = D.dsc_lc_name,
cft_Status = ((A.obs_cft_eff_st_dt < DateTime.Now) && (A.obs_cft_eff_end_dt > DateTime.Now) && (A.obs_cft_pub_dtm != null && A.obs_cft_pub_dtm < DateTime.Now)) ? "LIVE" : "NOT LIVE",
cft_isPublished = (A.obs_cft_pub_dtm != null && A.obs_cft_pub_dtm < DateTime.Now) ? "PUBLISHED" : "NOT PUBLISHED",
cft_eff_st_dt = A.obs_cft_eff_st_dt,
cft_eff_end_dt = A.obs_cft_eff_end_dt
}).ToList().FirstOrDefault();
// Set the properties from query result
if (q != null)
{ // A matching cft form was found in the database. Assume we are in "edit" mode
screen_Title = "Collection Form Maintenance";
cft_editMode = "edit";
hasInstances = db.OBS_COLLECT_FORM_INST.Count(x => x.obs_cft_id == cft_id) > 0 ? true : false;
//hasNewVersion = db.OBS_COLLECT_FORM_TMPLT.Count(x => x.obs_cft_nbr == q.cft_Nbr && x.obs_cft_ver > q.cft_Version) > 0 ? true : false;
cft_Title = q.cft_Title;
cft_SubTitle = q.cft_SubTitle;
cft_obsType = q.cft_obsType;
cft_Cust = q.cft_Cust;
cft_LC = q.cft_LC;
cft_Status = q.cft_Status;
cft_isPublished = q.cft_isPublished;
cft_Nbr = q.cft_Nbr;
cft_Version = q.cft_Version;
cft_eff_st_dt = q.cft_eff_st_dt;
cft_eff_end_dt = q.cft_eff_end_dt;
previous_vers_cft_id = 0;
OBS_COLLECT_FORM_TMPLT next_version = new OBS_COLLECT_FORM_TMPLT();
if (db.OBS_COLLECT_FORM_TMPLT.Any(x => x.obs_cft_nbr == q.cft_Nbr && x.obs_cft_ver == q.cft_Version + 1))
{
next_version = db.OBS_COLLECT_FORM_TMPLT.Single(x => x.obs_cft_nbr == q.cft_Nbr && x.obs_cft_ver == q.cft_Version + 1);
cft_new_vers_cft_id = next_version.obs_cft_id;
}
//Retrieve the cft_id of the new form's version (If any) (Value greater than zero means that this forms has a newer version)
//cft_new_vers_cft_id = db.OBS_COLLECT_FORM_TMPLT.Where(x => x.obs_cft_nbr == q.cft_Nbr && x.obs_cft_ver == q.cft_Version + 1).Select(y => y.obs_cft_id).FirstOrDefault();
if (cft_Version > 1)
{
OBS_COLLECT_FORM_TMPLT prev_version = db.OBS_COLLECT_FORM_TMPLT.Where(x => x.obs_cft_nbr == q.cft_Nbr && x.obs_cft_ver == q.cft_Version - 1).FirstOrDefault();
previous_vers_cft_id = prev_version.obs_cft_id;
previous_vers_end_eff_dt = (DateTime)prev_version.obs_cft_pub_dtm;
}
//previous_vers_cft_id =
colFormSections = new List<CollectionFormSection>();
retrieveQuestionData();
if (cft_isPublished.Equals("PUBLISHED"))
{
if (cft_new_vers_cft_id > 0)
{
manageAction = next_version.obs_cft_pub_dtm == null ? "RESTRICTED" : "VIEW-ONLY";
}
else
{
manageAction = hasInstances ? "NEW VERSION" : "EDIT";// "VIEW-ONLY", "RESTRICTED"
}
}
else { manageAction = ""; }
//int cft_new_vers_cft_id = db.OBS_COLLECT_FORM_TMPLT.Where(x => x.obs_cft_nbr == q.cft_Nbr && x.obs_cft_ver == q.cft_Version + 1).Select(y => y.obs_cft_id).FirstOrDefault();
}
} // End of getting values from DB when cft_id > 0
//Finally, set the "canBdeleted" property
// If the form is 'not published', it can be deleted!
//str_cft_canBdeleted = (cft_isPublished.Equals("NOT PUBLISHED") && questCount() == 0 && id > 0 && (HttpContext.Current.User.Identity.Name == "delgado_feliciano" || HttpContext.Current.User.Identity.Name == "A_Rasul") );
str_cft_canBdeleted = (cft_isPublished.Equals("NOT PUBLISHED") && cft_id > 0);
}
private int versionForm(oCollectionForm colForm, string form_questions_from_gui, string isPublished, int id)
{
int cft_id = 0;
colForm.previous_vers_cft_id=id;//represents previous version cft_id
ViewBag.exception = "";
using (var transaction = db.Database.BeginTransaction())
{
try
{
//first we need to save OBS_COLLECT_FORM_TMPLT table data
OBS_COLLECT_FORM_TMPLT template_to_save = new OBS_COLLECT_FORM_TMPLT();
OBS_COLLECT_FORM_TMPLT prev_form = db.OBS_COLLECT_FORM_TMPLT.Find(colForm.previous_vers_cft_id);
template_to_save.dsc_cust_id = prev_form.dsc_cust_id;
template_to_save.obs_type_id = prev_form.obs_type_id;
template_to_save.dsc_lc_id = prev_form.dsc_lc_id;
template_to_save.obs_cft_nbr = (short)colForm.cft_Nbr;
template_to_save.obs_cft_ver = (short)colForm.cft_Version;
template_to_save.obs_cft_title = prev_form.obs_cft_title;
template_to_save.obs_cft_subtitle = colForm.cft_SubTitle;
template_to_save.obs_cft_eff_end_dt = (colForm.cft_eff_end_dt == null) || (colForm.cft_eff_end_dt < Convert.ToDateTime("01/01/2000")) ? Convert.ToDateTime("12/31/2060") : colForm.cft_eff_end_dt;
template_to_save.obs_cft_added_dtm = DateTime.Now;
template_to_save.obs_cft_added_uid = User.Identity.Name;
template_to_save.obs_cft_last_saved_dtm = DateTime.Now;
template_to_save.obs_cft_upd_dtm = DateTime.Now;
template_to_save.obs_cft_upd_uid = User.Identity.Name;
if (isPublished == "true")
{
//if (colForm.cft_eff_st_dt != null && (colForm.cft_eff_st_dt < Convert.ToDateTime("01/01/2000")))
if (colForm.cft_eff_st_dt != null)
{
template_to_save.obs_cft_eff_st_dt = colForm.cft_eff_st_dt;
if(prev_form.obs_cft_eff_end_dt > colForm.cft_eff_st_dt)
{
if (prev_form.obs_cft_eff_st_dt == (DateTime)colForm.cft_eff_st_dt)
{
prev_form.obs_cft_eff_end_dt = (DateTime)colForm.cft_eff_st_dt;
}
else
{
prev_form.obs_cft_eff_end_dt = ((DateTime)colForm.cft_eff_st_dt).AddSeconds(-1);
}
}
}
else
{
Session.Add("errorMessage", "ERROR:Can't publish this form. Effective start date is required");
return -1;
}
template_to_save.obs_cft_pub_by_uid = User.Identity.Name;
template_to_save.obs_cft_pub_dtm = DateTime.Now;
}
else if (!(colForm.cft_eff_st_dt == null || (colForm.cft_eff_st_dt < Convert.ToDateTime("01/01/2000"))))
{
template_to_save.obs_cft_eff_st_dt = colForm.cft_eff_st_dt;
}
db.OBS_COLLECT_FORM_TMPLT.Add(template_to_save);
db.SaveChanges();
cft_id = template_to_save.obs_cft_id;
//now we need to save all the form questions
string[] splitterm = { "," };
string[] parsed_questions = form_questions_from_gui.Split(splitterm, StringSplitOptions.RemoveEmptyEntries);
short order_counter = 1;
foreach (string question in parsed_questions)
{
//now lets first save split the string we received from the gui
// string format should be: order,qat_id,section_text
string[] question_items = question.Split(new string[] { "~" }, StringSplitOptions.RemoveEmptyEntries);
short order = order_counter;
int qat_id = Convert.ToInt32(question_items[0]);
int form_section_id = getSectionID(question_items[1]);
OBS_COL_FORM_QUESTIONS new_form_question = new OBS_COL_FORM_QUESTIONS();
new_form_question.obs_cft_id = cft_id;
new_form_question.obs_form_section_id = form_section_id;
new_form_question.obs_qat_id = qat_id;
new_form_question.obs_col_form_quest_order = order;
new_form_question.obs_col_form_quest_wgt = 1;
new_form_question.obs_col_form_quest_na_yn = question_items[2];
db.OBS_COL_FORM_QUESTIONS.Add(new_form_question);
db.SaveChanges();
order_counter++;
}//end of foreach
transaction.Commit();
return cft_id;
}//end of try
catch (Exception e)
{
Session.Add("errorMessage", e.Message);
transaction.Rollback();
return -1;
}
}//end of using (var transaction = db.Database.BeginTransaction())
}
private int saveForm(oCollectionForm colForm, string form_questions_from_gui, string isPublished, int id)
{
int cft_id = id;
if (cft_id <= 0 && db.OBS_COLLECT_FORM_TMPLT.Where(item => item.obs_cft_title == colForm.cft_Title).Count() > 0)
{//we need to check if title passed from user is unique. if it already exists, we need to return the error message back to the screen
Session.Add("errorMessage", "ERROR: The Question Id is either invalid or the Form Title Already Exist.");
return -1;
}
using (var transaction = db.Database.BeginTransaction())
{
try
{
if (cft_id > 0)//this means we are editing an existing form
{
OBS_COLLECT_FORM_TMPLT template_to_edit = db.OBS_COLLECT_FORM_TMPLT.Find(cft_id);
if (colForm.cft_Version == 1)
{
template_to_edit.dsc_cust_id = Convert.ToInt32(colForm.cft_Cust);
template_to_edit.obs_type_id = Convert.ToInt32(colForm.cft_obsType);
template_to_edit.dsc_lc_id = Convert.ToInt32(colForm.cft_LC);
template_to_edit.obs_cft_title = colForm.cft_Title;
}
template_to_edit.obs_cft_eff_end_dt = (colForm.cft_eff_end_dt == null) || (colForm.cft_eff_end_dt < Convert.ToDateTime("01/01/2000")) ? Convert.ToDateTime("12/31/2060") : colForm.cft_eff_end_dt;
template_to_edit.obs_cft_last_saved_dtm = DateTime.Now;
template_to_edit.obs_cft_upd_dtm = DateTime.Now;
template_to_edit.obs_cft_upd_uid = User.Identity.Name;
template_to_edit.obs_cft_subtitle = colForm.cft_SubTitle;
if (isPublished == "true")
{
//if (colForm.cft_eff_st_dt != null && (colForm.cft_eff_st_dt < Convert.ToDateTime("01/01/2000")))
if (colForm.cft_eff_st_dt != null)
{
template_to_edit.obs_cft_eff_st_dt = colForm.cft_eff_st_dt;
if (colForm.previous_vers_cft_id > 0)
{
OBS_COLLECT_FORM_TMPLT prev_form = db.OBS_COLLECT_FORM_TMPLT.Find(colForm.previous_vers_cft_id);
if (prev_form.obs_cft_eff_end_dt > colForm.cft_eff_st_dt)
{
if (prev_form.obs_cft_eff_st_dt == (DateTime)colForm.cft_eff_st_dt)
{
prev_form.obs_cft_eff_end_dt = (DateTime)colForm.cft_eff_st_dt;
}
else
{
prev_form.obs_cft_eff_end_dt = ((DateTime)colForm.cft_eff_st_dt).AddSeconds(-1);
}
}
}// end ofif (colForm.previous_vers_cft_id > 0)
}
else
{
Session.Add("errorMessage", "ERROR:Can't publish this form. Effective start date is required");
return -1;
}
template_to_edit.obs_cft_pub_by_uid = User.Identity.Name;
template_to_edit.obs_cft_pub_dtm = DateTime.Now;
}
else if (!(colForm.cft_eff_st_dt == null || (colForm.cft_eff_st_dt < Convert.ToDateTime("01/01/2000"))))
{
template_to_edit.obs_cft_eff_st_dt = colForm.cft_eff_st_dt;
}
else
{
template_to_edit.obs_cft_eff_st_dt = null;
}
List<OBS_COL_FORM_QUESTIONS> old_form_questions = db.OBS_COL_FORM_QUESTIONS.Where(x => x.obs_cft_id == cft_id).ToList();
db.OBS_COL_FORM_QUESTIONS.RemoveRange(old_form_questions);
//foreach (OBS_COL_FORM_QUESTIONS old_form_question in old_form_questions)
//{
// db.OBS_COL_FORM_QUESTIONS.Remove(old_form_question)
//}
db.SaveChanges();
}
else//this means we're saving new form
{
//first we need to save OBS_COLLECT_FORM_TMPLT table data
OBS_COLLECT_FORM_TMPLT template_to_save = new OBS_COLLECT_FORM_TMPLT();
template_to_save.dsc_cust_id = Convert.ToInt32(colForm.cft_Cust);
template_to_save.obs_type_id = Convert.ToInt32(colForm.cft_obsType);
template_to_save.dsc_lc_id = Convert.ToInt32(colForm.cft_LC);
short cft_number = 1;
if (db.OBS_COLLECT_FORM_TMPLT.Count() > 0)
{ // Verify that at least one form exists in the database
cft_number = (short)(db.OBS_COLLECT_FORM_TMPLT.Max(x => x.obs_cft_nbr) + 1);
}
template_to_save.obs_cft_nbr = cft_number;
template_to_save.obs_cft_ver = 1;
template_to_save.obs_cft_title = colForm.cft_Title;
template_to_save.obs_cft_subtitle = colForm.cft_SubTitle;
template_to_save.obs_cft_eff_end_dt = (colForm.cft_eff_end_dt == null) || (colForm.cft_eff_end_dt < Convert.ToDateTime("01/01/2000")) ? Convert.ToDateTime("12/31/2060") : colForm.cft_eff_end_dt;
template_to_save.obs_cft_added_dtm = DateTime.Now;
template_to_save.obs_cft_added_uid = User.Identity.Name;
template_to_save.obs_cft_last_saved_dtm = DateTime.Now;
template_to_save.obs_cft_upd_dtm = DateTime.Now;
template_to_save.obs_cft_upd_uid = User.Identity.Name;
if (isPublished == "true")
{
if (colForm.cft_eff_st_dt != null && (colForm.cft_eff_st_dt > Convert.ToDateTime("01/01/2000")))
{
template_to_save.obs_cft_eff_st_dt = colForm.cft_eff_st_dt;
}
else
{
Session.Add("errorMessage", "ERROR:Can't publish this form. Effective start date is required");
return -1;
}
template_to_save.obs_cft_pub_by_uid = User.Identity.Name;
template_to_save.obs_cft_pub_dtm = DateTime.Now;
}
else if (!(colForm.cft_eff_st_dt == null || (colForm.cft_eff_st_dt < Convert.ToDateTime("01/01/2000"))))
{
template_to_save.obs_cft_eff_st_dt = colForm.cft_eff_st_dt;
}
db.OBS_COLLECT_FORM_TMPLT.Add(template_to_save);
db.SaveChanges();
cft_id = template_to_save.obs_cft_id;
//now we need to query OBS_COLLECT_FORM_TMPLT table to find CFT ID we just created
//int cft_id = db.OBS_COLLECT_FORM_TMPLT.Single(item => item.obs_cft_nbr == cft_number && item.obs_cft_ver == 1).obs_cft_id;
}//end of else(saving new form header form)
//now we need to save all the form questions
string[] splitterm = { "," };
string[] parsed_questions = form_questions_from_gui.Split(splitterm, StringSplitOptions.RemoveEmptyEntries);
short order_counter = 1;
foreach (string question in parsed_questions)
{
//now lets first save split the string we received from the gui
// string format should be: order,qat_id,section_text
string[] question_items = question.Split(new string[] { "~" }, StringSplitOptions.RemoveEmptyEntries);
short order = order_counter;
int qat_id = Convert.ToInt32(question_items[0]);
int form_section_id = getSectionID(question_items[1]);
OBS_COL_FORM_QUESTIONS new_form_question = new OBS_COL_FORM_QUESTIONS();
new_form_question.obs_cft_id = cft_id;
new_form_question.obs_form_section_id = form_section_id;
new_form_question.obs_qat_id = qat_id;
new_form_question.obs_col_form_quest_order = order;
new_form_question.obs_col_form_quest_wgt = 1;
new_form_question.obs_col_form_quest_na_yn = question_items[2];
db.OBS_COL_FORM_QUESTIONS.Add(new_form_question);
db.SaveChanges();
order_counter++;
}//end of foreach
transaction.Commit();
return cft_id;
}//end of try
catch (Exception e)
{
Session.Add("errorMessage", e.Message);
transaction.Rollback();
return -1;
}
}//end of using (var transaction = db.Database.BeginTransaction())
}
public string getManageAction(bool isPublished, int cft_id, short formNum, short formVer)
{
if(isPublished)
{
OBS_COLLECT_FORM_TMPLT next_version = new OBS_COLLECT_FORM_TMPLT();
if (OBSdb.OBS_COLLECT_FORM_TMPLT.Any(x => x.obs_cft_nbr == formNum && x.obs_cft_ver == formVer + 1))
{
next_version = OBSdb.OBS_COLLECT_FORM_TMPLT.Single(x => x.obs_cft_nbr == formNum && x.obs_cft_ver == formVer + 1);
manageAction = next_version.obs_cft_pub_dtm == null ? "RESTRICTED" : "VIEW-ONLY";
}
else
{
manageAction = OBSdb.OBS_COLLECT_FORM_INST.Count(x => x.obs_cft_id == cft_id) > 0 ? "NEW VERSION" : "EDIT";
}
return manageAction;
}
else
{
return "";
}
}