public bool UpdateSurveyNameAndDetail(SurveyDirectory model)
{
if (model != null)
{
_dbQuery.ExecuteSql("UPDATE dbo.SurveyDirectory SET SurveyName=@surveyname WHERE id =@id and IsValid =1", new { id = model.Id, surveyname = Utils.HtmlEncode(Utils.UrlDecode(model.SurveyName)) });
model.SurveyDetail.DirId = model.Id;
model.SurveyDetail.Id = model.SurveyDetailId;
model.SurveyDetail.SurveyNote = Utils.HtmlEncode(Utils.UrlDecode(model.SurveyDetail.SurveyNote));
var rst = _surveyDetailServices.Update(model.SurveyDetail);
if (rst)
{
if (_cacheManager.IsSet(string.Format(SurveyConfig.DIR, model.Id)))
{
_cacheManager.Remove(string.Format(SurveyConfig.DIR, model.Id));
}
if (_cacheManager.IsSet(string.Format(SurveyConfig.DIRPOLICY, model.Id)))
{
_cacheManager.Remove(string.Format(SurveyConfig.DIRPOLICY, model.Id));
}
if (_cacheManager.IsSet(string.Format(SurveyConfig.DIRDETAILSTYLE, model.Id)))
{
_cacheManager.Remove(string.Format(SurveyConfig.DIRDETAILSTYLE, model.Id));
}
}
return(rst);
}
return(false);
}
/// <summary>
/// 设计界面 - 更新问卷设置
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public ActionResult Update(SurveyDirectory model)
{
var userServeyModel = _surveyDirectoryServices.GetDirectoryWithQuestion(model.Id, WebWorkContext.AdminUser.Uid);
if (userServeyModel != null && userServeyModel.Id > 0)
{
userServeyModel.SurveyName = model.SurveyName;
userServeyModel.SurveyDetail = model.SurveyDetail;
var detail = _surveyDirectoryServices.UpdateSurveyNameAndDetail(userServeyModel);
return(Json(detail.ToString().ToLower()));
}
return(Json("false"));
}
public ActionResult StyleSave(SurveyDirectory model)
{
var userServeyModel = _surveyDirectoryServices.GetDirectoryWithQuestion(model.Id, WebWorkContext.AdminUser.Uid);
if (userServeyModel != null && userServeyModel.Id > 0)
{
model.SurveyDetail.Id = userServeyModel.SurveyDetailId;
model.SurveyDetail.DirId = userServeyModel.Id;
var detail = _surveyDirectoryServices.UpdateSurveyStyle(model);
return(Json(detail.ToString().ToLower()));
}
return(Json("false"));
}
/// <summary>
/// 创建问卷
/// </summary>
/// <param name="surveyName"></param>
/// <returns></returns>
public ActionResult Create(string surveyName)
{
SurveyDirectory surveyDirectory = new SurveyDirectory
{
SurveyName = Utils.UrlDecode(surveyName),
DirType = 2,
Uid = WebWorkContext.AdminUser.Uid,
Sid = Utils.GetCheckCode(8)
};
long surveyId = _surveyDirectoryServices.Insert(surveyDirectory);
return(RedirectToAction("Index", "SurveyDesign", new RouteValueDictionary()
{
{ "surveyId", surveyId }
}));
}
/// <summary>
/// 新建问卷
/// </summary>
/// <param name="surveyDirectory"></param>
/// <returns></returns>
public long Insert(SurveyDirectory surveyDirectory)
{
var dirid = _repository.Add(surveyDirectory);
if (dirid > 0)
{
var detailId = _surveyDetailServices.Add(new SurveyDetail()
{
DirId = (int)dirid,
SurveyNote = surveyDirectory.DirType == 2 ? "非常感谢您的参与！如有涉及个人信息，我们将严格保密。" : string.Empty,
EndTime = DateTime.Now.AddMonths(1)
});
_dbQuery.ExecuteSql("UPDATE dbo.SurveyDirectory SET SurveyDetailId = @surveydetailid WHERE id=@id", new { surveydetailid = detailId, id = dirid });
}
return(dirid);
}
public bool UpdateSurveyStyle(SurveyDirectory model)
{
if (model != null)
{
bool rst = true;
if (model.SurveyDetail != null)
{
model.SurveyDetail.SurveyNote = Utils.HtmlEncode(Utils.UrlDecode(model.SurveyDetail.SurveyNote));
rst = _surveyDetailServices.Update(model.SurveyDetail);
}
if (model.SurveyStyle != null)
{
var existStyle = _dbQuery.QuerySingle <SurveyStyle>("SELECT * FROM dbo.SurveyStyle WHERE DirId = @dirid and IsValid =1", new { dirid = model.Id });
var sourceStyle = _dbQuery.QuerySingle <SurveyStyle>("SELECT * FROM dbo.SurveyStyle WHERE id = @id and IsValid =1", new { id = model.SurveyStyle.Id });
if (existStyle != null)
{
sourceStyle.Id = existStyle.Id;
sourceStyle.DirId = model.Id;
var updateRst = _surveyStyleServices.Update(sourceStyle);
rst = rst || updateRst;
}
else
{
sourceStyle.DirId = model.Id;
var istrst = _surveyStyleServices.Insert(sourceStyle);
rst = rst || istrst > 0;
}
}
if (rst)
{
if (_cacheManager.IsSet(string.Format(SurveyConfig.DIRDETAILSTYLE, model.Id)))
{
_cacheManager.Remove(string.Format(SurveyConfig.DIRDETAILSTYLE, model.Id));
}
}
return(rst);
}
return(false);
}
public List <Question> GetStatistics(SurveyDirectory survey)
{
var questions = _questionServices.Get(survey.Id);
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);
foreach (QuestionRadio item in question.QuRadios) //从问题中取出单个单选按钮选项
{
item.AnswerCount = (radioItemAnswerlist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount;
}
}
#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);
foreach (QuestionCheckbox item in question.QuCheckboxes)
{
item.AnswerCount = (checklist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount;
question.AnswerCount += item.AnswerCount;
}
}
#endregion
break;
case QuType.SCORE:
#region 逻辑同上
var scorelist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuRowId GroupKey,COUNT(QuRowId) GroupCount,AVG(AnswserScore) AvgNum FROM dbo.AnswerScore WHERE QuId IN @quid AND IsValid =1 GROUP BY QuRowId;", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem)
{
question.QuName = Utils.UrlDecode(question.QuName);
foreach (QuestionScore item in question.QuScores)
{
var model = scorelist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel();
item.AnswerCount = model.GroupCount;
item.AvgNum = model.AvgNum;
question.AnswerCount += item.AnswerCount;
}
}
#endregion
break;
case QuType.ORDERQU:
#region 逻辑同上
var orderbylist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuRowId GroupKey,SUM(OrderyNum) SumNum FROM dbo.AnswerOrder WHERE QuId IN @quid AND IsValid =1 GROUP BY QuRowId", new
{
quid = groupItem.Select(p => p.Id)
}).ToList();
foreach (Question question in groupItem)
{
question.QuOrderbies = question.QuOrderbies.OrderByDescending(p => p.SumNum).ToList();
question.QuName = Utils.UrlDecode(question.QuName);
foreach (QuestionOrderby item in question.QuOrderbies)
{
item.SumNum = (orderbylist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).SumNum;
}
}
#endregion
break;
case QuType.FILLBLANK:
#region 逻辑同上
var fbklist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuId GroupKey, 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;
question.AnswerCount = question.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);
foreach (QuestionMultiFillblank item in question.QuMultiFillblanks)
{
item.AnswerCount = (mulitFillBlanklist.FirstOrDefault(p => p.GroupKey == item.Id) ?? new AnswerGroupViewModel()).GroupCount;
}
}
#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);
foreach (QuestionChenRow item in question.QuChenRows)
{
item.AnswerCount = chenradiolist.Where(p => p.GroupKey == item.Id).Sum(p => p.GroupCount);
question.AnswerCount += item.AnswerCount;
}
foreach (var answerItem in chenradiolist)
{
question.AnChenRadio.Add(new AnswerChenRadio()
{
QuRowId = answerItem.GroupKey,
QuColId = answerItem.GroupColKey,
Count = answerItem.GroupCount
});
}
}
#endregion
break;
case QuType.CHENCHECKBOX:
#region 逻辑同上
var chencheckboxlist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuRowId GroupKey, QuColId GroupColKey,COUNT(Id) GroupCount FROM dbo.AnswerChenCheckbox 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);
foreach (QuestionChenRow item in question.QuChenRows)
{
item.AnswerCount = chencheckboxlist.Where(p => p.GroupKey == item.Id).Sum(p => p.GroupCount);
question.AnswerCount += item.AnswerCount;
}
foreach (var answerItem in chencheckboxlist)
{
question.AnChenCheckbox.Add(new AnswerChenCheckbox()
{
QuRowId = answerItem.GroupKey,
QuColId = answerItem.GroupColKey,
Count = answerItem.GroupCount
});
}
}
#endregion
break;
case QuType.CHENSCORE:
#region 逻辑同上
//SELECT GroupKey, GroupColKey, AVG(totalNum) AvgNum FROM(SELECT QuRowId GroupKey , QuColId GroupColKey, SUM(AnswserScore) totalNum FROM dbo.AnswerChenScore WHERE QuId IN @quid AND IsValid = 1 GROUP BY QuRowId , QuColId)AS t GROUP BY GroupKey , GroupColKey
var chenscorelist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuRowId GroupKey, QuColId GroupColKey,ROUND(AVG(CONVERT(DECIMAL(18,2),AnswserScore)),2) AvgNum FROM dbo.AnswerChenScore 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);
foreach (var answerItem in chenscorelist)
{
question.AnChenScore.Add(new AnswerChenScore()
{
QuRowId = answerItem.GroupKey,
QuColId = answerItem.GroupColKey,
AvgScore = answerItem.AvgNum
});
}
}
#endregion
break;
case QuType.CHENFBK:
#region 逻辑同上
var chenFBKlist = _dbQuery.QueryList <AnswerGroupViewModel>("SELECT QuRowId GroupKey, QuColId GroupColKey,COUNT(Id) GroupCount FROM dbo.AnswerChenFillblank 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);
foreach (QuestionChenRow item in question.QuChenRows)
{
item.AnswerCount = chenFBKlist.Where(p => p.GroupKey == item.Id).Sum(p => p.GroupCount);
question.AnswerCount += item.AnswerCount;
}
foreach (var answerItem in chenFBKlist)
{
question.AnChenFillblank.Add(new AnswerChenFillblank()
{
QuRowId = answerItem.GroupKey,
QuColId = answerItem.GroupColKey,
Count = answerItem.GroupCount
});
}
}
#endregion
break;
}
}
}
return(questions);
}
