public string Insert(QuestionnaireDetailData data)
{
string sqlStr;
SqlCommand cmd;
SqlParameter para;
string detailPK;
sqlStr = "Insert Into QuestionnaireDetail(masterId,groupId,topic,description,answerType," +
"answerDefine,nextAnswer,needed,sort, type) Values(@masterId,@groupId,@topic,@descr" +
"iption,@answerType,@answerDefine,@nextAnswer,@needed,@sort, @type) SET @detailPK = SCOPE_IDENTITY()";
cmd = new SqlCommand(sqlStr, this.mCon);
cmd.Parameters.AddWithValue("@masterId", data.MasterId);
cmd.Parameters.AddWithValue("@groupId", data.GroupId);
cmd.Parameters.AddWithValue("@topic", data.Topic);
cmd.Parameters.AddWithValue("@description", data.Description ?? "");
cmd.Parameters.AddWithValue("@answerType", data.AnswerType);
cmd.Parameters.AddWithValue("@answerDefine", data.AnswerDefine);
cmd.Parameters.AddWithValue("@nextAnswer", data.NextAnswer);
cmd.Parameters.AddWithValue("@needed", data.Needed ? "Y" : "N");
cmd.Parameters.AddWithValue("@sort", data.Sort);
cmd.Parameters.AddWithValue("@type", data.Type);
para = new SqlParameter("@detailPK", System.Data.SqlDbType.Int);
para.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(para);
this.mCon.Open();
cmd.ExecuteNonQuery();
this.mCon.Close();
cmd.Dispose();
detailPK = para.Value.ToString();
return(detailPK);
}
public virtual void Update(QuestionnaireDetailData data)
{
string sqlStr;
SqlCommand cmd;
sqlStr = "Update QuestionnaireDetail Set masterId= @masterId,groupId= @groupId,topic= @topi" +
"c,description= @description,answerType= @answerType,answerDefine= @answerDefine," +
"nextAnswer= @nextAnswer,needed= @needed,sort= @sort, type= @type Where id =@id";
cmd = new SqlCommand(sqlStr, this.mCon);
cmd.Parameters.AddWithValue("@masterId", data.MasterId);
cmd.Parameters.AddWithValue("@groupId", data.GroupId);
cmd.Parameters.AddWithValue("@topic", data.Topic);
cmd.Parameters.AddWithValue("@description", data.Description);
cmd.Parameters.AddWithValue("@answerType", data.AnswerType);
cmd.Parameters.AddWithValue("@answerDefine", data.AnswerDefine);
cmd.Parameters.AddWithValue("@nextAnswer", data.NextAnswer);
cmd.Parameters.AddWithValue("@needed", data.Needed ? "Y" : "N");
cmd.Parameters.AddWithValue("@sort", data.Sort);
cmd.Parameters.AddWithValue("@type", data.Type);
cmd.Parameters.AddWithValue("@id", data.Id);
this.mCon.Open();
cmd.ExecuteNonQuery();
this.mCon.Close();
cmd.Dispose();
}
public string SaveDetailGroup(string value)
{
QuestionnaireDetailManager qdm = new QuestionnaireDetailManager();
List <QuestionnaireDetailData> qds = new List <QuestionnaireDetailData>();
string[] values = value.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var val in values)
{
var qd = new QuestionnaireDetailData();
qd = Element.ToElement(qd, val);
qds.Add(qd);
}
if (qds.Count > 0)
{
//先刪除
qdm.DeleteByGroupId(qds[0].GroupId.ToString());
//新增
foreach (var qd in qds)
{
qdm.Insert(qd);
}
}
return("Y");
}
private QuestionnaireDetailData GetQuestionnaireDetail(SqlDataReader dr)
{
QuestionnaireDetailData data = new QuestionnaireDetailData();
data.Id = ((int)(dr["id"]));
data.MasterId = ((int)(dr["masterId"]));
data.GroupId = ((int)(dr["groupId"]));
data.Topic = dr["topic"].ToString();
data.Description = dr["description"].ToString();
data.AnswerType = dr["answerType"].ToString();
data.AnswerDefine = dr["answerDefine"].ToString();
data.NextAnswer = dr["nextAnswer"].ToString();
data.Needed = dr["needed"].ToString() == "Y";
data.Sort = ((int)(dr["sort"]));
data.Type = dr["type"].ToString();
data.No = dr["no"].Equals(DBNull.Value) ? 0 : (int)dr["no"];
return(data);
}
public string UpdateSort(string value)
{
QuestionnaireDetailManager qm = new QuestionnaireDetailManager();
List <QuestionnaireDetailData> qds = new List <QuestionnaireDetailData>();
QuestionnaireDetailData qd;
string[] values = value.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
foreach (var val in values)
{
qd = new QuestionnaireDetailData();
qd = Element.ToElement(qd, val);
qds.Add(qd);
}
qm.UpdateSortBatch(qds);
return("Y");
}
//
// GET: /Questionnaire/Details/5
public ActionResult Details(int id = 0)
{
var viewModel = new QuestionnaireDetailData();
/* Add Fully Loaded (.Included) Questionnaire to the ViewModel */
viewModel.Questionnaire =
_db.Questionnaires
.Include(a => a.QuestionnaireQuestions
.Select(b => b.Question.QType))
.Include(a => a.QuestionnaireQuestions
.Select(b => b.QuestionnaireQCategory))
.Include(a => a.QuestionnaireQCategories
.Select(b => b.QCategory))
.Where(n => n.QuestionnaireId == id)
.Single();
/* Sort by Questionnaire Question Categories: Step 1 - Left Join QuestionnaireQuestions to QuestionnaireQCategories on QCategoryId */
var query = from questionnaireQuestions in viewModel.Questionnaire.QuestionnaireQuestions
join questionnaireQCategories in viewModel.Questionnaire.QuestionnaireQCategories
on questionnaireQuestions.QuestionnaireQCategory.QCategoryId equals
questionnaireQCategories.QCategoryId
into qqJoinQqc
from subset in qqJoinQqc.DefaultIfEmpty()
select new
{
questionnaireQuestions.Id,
questionnaireQuestions.QuestionnaireId,
questionnaireQuestions.QuestionId,
questionnaireQuestions.QuestionnaireQCategory.QCategoryId,
questionnaireQuestions.Ordinal,
questionnaireQuestions.Question,
questionnaireQuestions.QuestionnaireQCategory.QCategory,
QQCOrdinal = (subset == null ? 99 : subset.Ordinal)
};
/* Sort by Questionnaire Question Categories: Step 2 - Sort on Ordinal from QuestionnaireQCategories */
var sortedQuery = query.OrderBy(q => q.QQCOrdinal);
/* Sort by Questionnaire Question Categories: Step 3 - Build List<QuestionnaireQuestion> (because QuestionnaireQuestions is an ICollection) */
var sortedQuestionnaireQuestions = new List <QuestionnaireQuestion>();
foreach (var item in sortedQuery)
{
var questionnaireQuestion = new QuestionnaireQuestion
{
Id = item.Id,
Ordinal = item.Ordinal,
QuestionnaireId = item.QuestionnaireId,
QuestionId = item.QuestionId,
QuestionnaireQCategory = { QCategoryId = item.QCategoryId },
Question = item.Question
};
questionnaireQuestion.QuestionnaireQCategory.QCategory = item.QCategory;
sortedQuestionnaireQuestions.Add(questionnaireQuestion);
}
/* Sort by Questionnaire Question Categories: Step 4 - Replace Questionnaire.QuestionnaireQuestions with Sorted List */
viewModel.Questionnaire.QuestionnaireQuestions = sortedQuestionnaireQuestions;
viewModel.Questionnaire.QuestionnaireQCategories =
viewModel.Questionnaire.QuestionnaireQCategories.OrderBy(o => o.Ordinal).ToList();
var detailRecords = new List <QuestionnaireDetailRecord>();
foreach (QuestionnaireQuestion qq in viewModel.Questionnaire.QuestionnaireQuestions)
{
List <Answer> answers = _db.Answers.Where(x => x.QTypeName == qq.Question.QTypeName).ToList();
var selectListItems = new List <SelectListItem>();
for (int i = 0; i < answers.Count(); i++)
{
var selectListItem = new SelectListItem
{
Text = answers[i].AnswerText,
Value = answers[i].AnswerId.ToString()
};
selectListItems.Add(selectListItem);
}
var selectList = new SelectList(selectListItems, "Value", "Text");
detailRecords.Add(new QuestionnaireDetailRecord
{
Question = qq.Question,
QCategory = qq.QuestionnaireQCategory.QCategory,
Answers = selectList
});
}
viewModel.DetailRecords = detailRecords;
return(View(viewModel));
}
