public Model.QuestionnaireHead GetModel(int HeadId, int TeacherId)
{
SqlConnection co = SQLSeverOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
Model.QuestionnaireHead Questionnaire = new Model.QuestionnaireHead();
Questionnaire.Item = new List <Model.SelectQuestion>();
cmd.CommandText = "select * from [V_SelectQuestion_Question] where QuestionnaireHeadId=@QuestionnaireHeadId";
cmd.Parameters.AddWithValue("@QuestionnaireHeadId", HeadId);
cmd.Connection = co;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Model.SelectQuestion item = new Model.SelectQuestion();
item.QuestionHeadId = HeadId;
item.QuestionId = Convert.ToInt32(dr["QuestionId"]);
Model.Question question = new Model.Question();
question.Id = item.QuestionId;
question.Content = Convert.ToString(dr["content"]);
item.Question = question;
Questionnaire.Item.Add(item);
Questionnaire.ScoreList = GetQuestionScore(HeadId, TeacherId);
}
dr.Close();
co.Close();
return(Questionnaire);
}
//获取该问卷的表头
public List <Model.QuestionnaireHead> getNaireHead(int HeadId)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = url;
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText =
"select * from T_Base_QuestionnaireHead where Id=" +
HeadId;
List <Model.QuestionnaireHead> lst = new List <Model.QuestionnaireHead>();
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Model.QuestionnaireHead questionnaire = new Model.QuestionnaireHead();
questionnaire.Id = Convert.ToInt32(dr["Id"]);
questionnaire.QuestionnaireName = Convert.ToString(dr["QuestionnaireName"]);
questionnaire.StartTime = Convert.ToDateTime(dr["StartTime"]);
questionnaire.EndTime = Convert.ToDateTime(dr["EndTime"]);
questionnaire.ClassId = Convert.ToInt32(dr["ClassId"]);
questionnaire.SchoolId = Convert.ToInt32(dr["SchoolId"]);
lst.Add(questionnaire);
}
dr.Close();
co.Close();
return(lst);
}
/// <summary>
/// 获取数据库中所有问卷信息
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageNumber"></param>
/// <returns></returns>
public List <Model.QuestionnaireHead> GetList(int CurrentPage, int PageSize, int TeacherId, string keyWord)
{
SqlConnection co = SQLSeverOpen();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
keyWord = "'%" + keyWord + "%'";
cm.CommandText = "select top " + PageSize + " * from [V_Write_Questionnaire] where id not in (select top " + PageSize * (CurrentPage - 1) + " id from [V_Write_Questionnaire] where QuestionnaireName like " + keyWord + " and TeacherId=" + TeacherId + ") and QuestionnaireName like " + keyWord + "and TeacherId=" + TeacherId;
List <Model.QuestionnaireHead> lst = new List <Model.QuestionnaireHead>();
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Model.QuestionnaireHead head = new Model.QuestionnaireHead();
head.Id = Convert.ToInt32(dr["QuestionnaireHeadId"]);
head.SchoolId = Convert.ToInt32(dr["SchoolId"]);
head.ClassId = Convert.ToInt32(dr["ClassId"]);
head.QuestionnaireName = Convert.ToString(dr["QuestionnaireName"]);
head.StartTime = Convert.ToDateTime(dr["StartTime"]);
head.EndTime = Convert.ToDateTime(dr["EndTime"]);
head.WritenTimes = GetWritenTimes(head.Id, TeacherId);
int score = GetTotalScore(head.Id);
head.TotalScore = score / head.WritenTimes;
lst.Add(head);
}
dr.Close();
co.Close();
return(lst);
}
/// <summary>
/// 获取问卷列表
/// </summary>
/// <param name="CurrentPage"></param>
/// <param name="PageSize"></param>
/// <param name="keyWord"></param>
/// <returns></returns>
public List <Model.QuestionnaireHead> GetNaireList(int CurrentPage, int PageSize, string keyWord)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = url;
co.Open();
SqlCommand cm = new SqlCommand();
keyWord = "'%" + keyWord + "%'";
cm.CommandText = "select top " + PageSize + " * from t_base_questionnaireHead where id not in (select top " + PageSize * (CurrentPage - 1) + " id from t_base_questionnaireHead where QuestionnaireName like " + keyWord + ") and QuestionnaireName like " + keyWord;
cm.Connection = co;
List <Model.QuestionnaireHead> lst = new List <Model.QuestionnaireHead>();
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Model.QuestionnaireHead naire = new Model.QuestionnaireHead();
naire.Id = Convert.ToInt32(dr["Id"]);
naire.QuestionnaireName = Convert.ToString(dr["QuestionnaireName"]);
naire.ClassId = Convert.ToInt32(dr["ClassId"]);
//naire.CourseId = Convert.ToInt32(dr["CourseId"]);
lst.Add(naire);
}
dr.Close();
co.Close();
return(lst);
}
public Model.QuestionnaireHead GetQuestionnaire(int Id)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = url;
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select * from t_base_questionnaireHead where Id = " + Id;
SqlDataReader reader = cm.ExecuteReader();
Model.QuestionnaireHead question = new Model.QuestionnaireHead();
while (reader.Read())
{
question.Id = Convert.ToInt32(reader["Id"]);
question.QuestionnaireName = Convert.ToString(reader["QuestionnaireName"]);
// question.TeacherId = Convert.ToInt32(reader["TeacherId"]);
//question.TotalScore = Convert.ToInt32(reader["TotalScore"]);
question.StartTime = Convert.ToDateTime(reader["StartTime"]);
question.EndTime = Convert.ToDateTime(reader["EndTime"]);
//question.CourseId = Convert.ToInt32(reader["CourseId"]);
question.ClassId = Convert.ToInt32(reader["ClassId"]);
question.SchoolId = Convert.ToInt32(reader["SchoolId"]);
}
reader.Close();
co.Close();
return(question);
}
public JsonResult GetModel(int HeadId, int TeacherId)
{
BLL.QuestionnaireHead bll = new BLL.QuestionnaireHead();
Model.QuestionnaireHead model = bll.GetModel(HeadId, TeacherId);
List <object> fullItem = new List <object>();
for (int i = 0; i < model.Item.Count; i++)
{
Model.SelectQuestion sq = model.Item[i];
Model.Question question = new Model.Question();
question.Id = sq.Question.Id;
question.Content = sq.Question.Content;
object obj = new
{
Question = question,
QuestionHeadId = sq.QuestionHeadId,
AVGScore = model.ScoreList[i] / new BLL.QuestionnaireHead().GetWritenTimes(sq.QuestionHeadId, TeacherId)
};
fullItem.Add(obj);
}
return(Json(new { Item = fullItem }));
//return Json(model);
}
public JsonResult GetList(int pageSize, int pageIndex, int TeacherId, string keyWord = "")
{
BLL.QuestionnaireHead bll = new BLL.QuestionnaireHead();
List <Model.QuestionnaireHead> lst = new List <Model.QuestionnaireHead>();
lst = bll.GetList(pageIndex, pageSize, TeacherId, keyWord);
int count = bll.GetCount(TeacherId);
List <object> fullList = new List <object>();
for (int i = 0; i < lst.Count; i++)
{
Model.QuestionnaireHead qstnH = lst[i];
bool isExisted = false;
for (int j = 0; j < i; j++)
{
if (lst[j].Id == qstnH.Id)
{
isExisted = true;
break;
}
}
if (isExisted)
{
continue;
}
//生成一个新obj
object obj = new
{
Id = qstnH.Id,
QuestionnaireName = qstnH.QuestionnaireName,
WritenTimes = qstnH.WritenTimes,
TotalScore = qstnH.TotalScore,
ClassId = qstnH.ClassId,
ClassName = new BLL.Write().getClassName(qstnH.ClassId)
};
fullList.Add(obj);
}
return(Json(new { total = count, rows = fullList, prev = lst }));
}