public SurveyDirectory GetDirectoryWithQuestion(int id)
{
var cacheModel = _cacheManager.Get <SurveyDirectory>(string.Format(SurveyConfig.DIR, id));
if (cacheModel == null)
{
var surveyDirectory = _dbQuery.QueryList <SurveyDirectory>("SELECT * FROM SurveyDirectory WHERE id=@id and IsValid =1;", new { Id = id }).FirstOrDefault();
if (surveyDirectory != null)
{
surveyDirectory.Questions = _questionservices.Get(surveyDirectory.Id);
}
if (surveyDirectory.SurveyState > 0)
{
_cacheManager.Set(string.Format(SurveyConfig.DIR, id), surveyDirectory, 60 * 24);
}
return(surveyDirectory);
}
return(cacheModel);
}
public Question GetChart(long questionid)
{
var questions = new List <Question>()
{
_questionServices.Get(questionid)
};
List <AnswerDataCross> dataCrosses = new List <AnswerDataCross>();
if (questions.Count > 0)
{
//分问题类型查询
var groups = from p in questions group p by p.QuType into g select g;
foreach (IGrouping <QuType, Question> groupItem in groups)
{
switch (groupItem.Key)
{
//所有类型处理逻辑类似，仅第一项做注释解析
case QuType.RADIO:
#region 查出radio类型的回答问题统计数，遍历每个单选项，然后分别赋值
var radioItemAnswerlist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuItemId GroupKey,COUNT(QuItemId) GroupCount FROM dbo.AnswerRadio WHERE QuId IN @quid AND IsValid =1 GROUP BY QuItemId;", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem) //从类型组里取出单个问题
{
question.QuName = Utils.UrlDecode(question.QuName);
List <AnswerDataCross> crosses = new List <AnswerDataCross>();
foreach (QuestionRadio item in question.QuRadios) //从问题中取出单个单选按钮选项
{
crosses.Add(new AnswerDataCross()
{
OptionName = Utils.UrlDecode(question.QuName),
Count = (radioItemAnswerlist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount
});
}
question.StatJson = JsonConvert.SerializeObject(crosses);
}
#endregion
break;
case QuType.CHECKBOX:
#region 逻辑同上
var checklist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuItemId GroupKey,COUNT(QuItemId) GroupCount FROM dbo.AnswerCheckbox WHERE QuId IN @quid AND IsValid =1 GROUP BY QuItemId;", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem)
{
question.QuName = Utils.UrlDecode(question.QuName);
List <AnswerDataCross> crosses = new List <AnswerDataCross>();
foreach (QuestionCheckbox item in question.QuCheckboxes)
{
crosses.Add(new AnswerDataCross()
{
OptionName = Utils.UrlDecode(question.QuName),
Count = (checklist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount
});
}
question.StatJson = JsonConvert.SerializeObject(crosses);
}
#endregion
break;
case QuType.FILLBLANK:
#region 逻辑同上
var fbklist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuId, COUNT(CASE WHEN ISNULL(Answers,'') ='' THEN 1 END) EmptyCount,COUNT(CASE WHEN ISNULL(Answers,'') !='' THEN 1 END) NoEmptyCount FROM dbo.AnswerFillblank WHERE QuId IN @quid AND IsValid =1 GROUP BY QuId", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem)
{
question.QuName = Utils.UrlDecode(question.QuName);
question.EmptyCount = (fbklist.FirstOrDefault(p => p.GroupKey == question.Id) ?? new AnswerGroupViewModel()).EmptyCount;
question.NoEmptyCount = (fbklist.FirstOrDefault(p => p.GroupKey == question.Id) ?? new AnswerGroupViewModel()).NoEmptyCount;
}
#endregion
break;
case QuType.MULTIFILLBLANK:
#region 逻辑同上
var mulitFillBlanklist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuItemId GroupKey,COUNT(Id) GroupCount FROM dbo.AnswerMultiFillblank WHERE QuId IN @quid AND IsValid =1 GROUP BY QuItemId", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem)
{
question.QuName = Utils.UrlDecode(question.QuName);
List <AnswerDataCross> crosses = new List <AnswerDataCross>();
foreach (QuestionMultiFillblank item in question.QuMultiFillblanks)
{
crosses.Add(new AnswerDataCross()
{
OptionName = Utils.UrlDecode(question.QuName),
Count = (mulitFillBlanklist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount
});
}
question.StatJson = JsonConvert.SerializeObject(crosses);
}
#endregion
break;
case QuType.CHENRADIO:
#region 逻辑同上
var chenradiolist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuRowId GroupKey, QuColId GroupColKey,COUNT(Id) GroupCount FROM dbo.AnswerChenRadio WHERE QuId IN @quid AND IsValid =1 GROUP BY QuRowId, QuColId", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem)
{
question.QuName = Utils.UrlDecode(question.QuName);
//List<AnswerDataCross> crosses = new List<AnswerDataCross>();
//foreach (QuestionChenRow item in question.QuChenRows)
//{
// crosses.Add(new AnswerDataCross()
// {
// OptionName = Utils.UrlDecode(question.QuName),
// Count = (chenradiolist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount
// });
// item.AnswerCount = (chenradiolist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount;
//}
//question.StatJson = JsonConvert.SerializeObject(crosses);
}
#endregion
break;
}
}
}
var rst = questions.FirstOrDefault();
rst.StatJson = JsonConvert.SerializeObject(dataCrosses);
return(rst);
}
public Question Get(long questionid)
{
var questions = _dbQuery.Query <Question, QuestionLogic, Question>("SELECT q.id , q.Uid , q.DirId , q.QuName , q.QuTitle , q.QuNote , q.Keywords , q.QuType , q.Tag , q.OrderById , q.QuTag , q.ParentQuId , q.YesnoOption , q.IsRequired , q.CheckType , q.ParamInt01 , q.ParamInt02 , q.CopyFromId , q.Hv , q.RandOrder , q.CellCount , q.ContactsAttr , q.ContactsField , q.AnswerInputWidth , q.AnswerInputRow , q.CreateDate , q.IsValid ,l.id, l.QuId , l.Uid , l.QuItemId , l.SkipToQuId , l.LogicType , l.GeLe , l.ScoreNum , l.CreateDate , l.IsValid FROM dbo.Question q LEFT JOIN dbo.QuestionLogic l ON q.id = l.QuId WHERE q.Id = @quid AND q.IsValid = 1 ORDER BY q.OrderById ASC;",
(question, logic) =>
{
if (logic != null)
{
question.QuestionLogics.Add(logic);
}
return(question);
}, new { quid = questionid }, splitOn: "id").ToList();
if (questions.Count > 0)
{
bool hasQueryChenGroup = false;
var group = from p in questions group p by p.QuType into g select g;
foreach (IGrouping <QuType, Question> groupItem in group)
{
switch (groupItem.Key)
{
case QuType.RADIO:
var radiolist = _dbQuery.QueryList <QuestionRadio>("SELECT * FROM dbo.QuestionRadio WHERE QuId in @quid AND IsValid=1", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
radiolist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
foreach (Question question in groupItem)
{
question.QuName = Utils.HtmlDecode(question.QuName);
question.QuRadios.AddRange(radiolist.Where(p => p.QuId == question.Id));
}
break;
case QuType.CHECKBOX:
var checklist = _dbQuery.QueryList <QuestionCheckbox>("SELECT * FROM dbo.QuestionCheckbox WHERE QuId in @quid AND IsValid=1", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
checklist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
foreach (Question question in groupItem)
{
question.QuName = Utils.HtmlDecode(question.QuName);
question.QuCheckboxes.AddRange(checklist.Where(p => p.QuId == question.Id));
}
break;
case QuType.SCORE:
var scorelist = _dbQuery.QueryList <QuestionScore>("SELECT * FROM dbo.QuestionScore WHERE QuId in @quid AND IsValid=1", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
scorelist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
foreach (Question question in groupItem)
{
question.QuName = Utils.HtmlDecode(question.QuName);
question.QuScores.AddRange(scorelist.Where(p => p.QuId == question.Id));
}
break;
case QuType.ORDERQU:
var orderbylist = _dbQuery.QueryList <QuestionOrderby>("SELECT * FROM dbo.QuestionOrderby WHERE QuId in @quid AND IsValid=1", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
orderbylist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
foreach (Question question in groupItem)
{
question.QuName = Utils.HtmlDecode(question.QuName);
question.QuOrderbies.AddRange(orderbylist.Where(p => p.QuId == question.Id));
}
break;
case QuType.MULTIFILLBLANK:
var mulitFillBlanklist = _dbQuery.QueryList <QuestionMultiFillblank>("SELECT * FROM dbo.QuestionMultiFillblank WHERE QuId in @quid AND IsValid=1", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
mulitFillBlanklist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
foreach (Question question in groupItem)
{
question.QuName = Utils.HtmlDecode(question.QuName);
question.QuMultiFillblanks.AddRange(mulitFillBlanklist.Where(p => p.QuId == question.Id));
}
break;
case QuType.CHENRADIO:
case QuType.CHENCHECKBOX:
case QuType.CHENSCORE:
case QuType.CHENFBK:
if (hasQueryChenGroup)
{
continue;
}
var multiItems = questions.Where(q => q.QuType == QuType.CHENRADIO || q.QuType == QuType.CHENCHECKBOX || q.QuType == QuType.CHENSCORE || q.QuType == QuType.CHENFBK).ToList();
var rowlist = _dbQuery.QueryList <QuestionChenRow>("SELECT * FROM dbo.QuestionChenRow WHERE QuId in @quid AND IsValid=1", new
{
quid = multiItems.Select(p => p.Id)
}).ToList();
rowlist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
var columnlist = _dbQuery.QueryList <QuestionChenColumn>("SELECT * FROM dbo.QuestionChenColumn WHERE QuId in @quid AND IsValid=1", new
{
quid = multiItems.Select(p => p.Id)
}).ToList();
columnlist.ForEach(p => p.OptionName = Utils.HtmlDecode(p.OptionName));
foreach (Question question in multiItems)
{
question.QuName = Utils.HtmlDecode(question.QuName);
question.QuChenRows.AddRange(rowlist.Where(p => p.QuId == question.Id));
question.QuChenColumns.AddRange(columnlist.Where(p => p.QuId == question.Id));
}
hasQueryChenGroup = true;
break;
case QuType.PAGETAG:
case QuType.PARAGRAPH:
case QuType.FILLBLANK:
foreach (Question question in groupItem)
{
question.QuName = Utils.HtmlDecode(question.QuName);
}
break;
}
}
}
return(questions.FirstOrDefault());
}