public IEnumerable <Survey_Question> GetSurvey_Questions(Survey s)
{
var survey_questions = new List <Survey_Question>();
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString)) // using permet de refermer la connection après ouverture
{
//ouvre la connection à la base de donnée.
var cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = "SELECT QuestionId FROM Survey_Question WHERE SurveyId = @surveyId ;", // stock la requete sql dans commandText. SCOPE_IDENTITY renvoie l'Id de la question inseré.
Connection = sqlConnection1, // etablie la connection.
};
// permet de definir les variables values dans CommandText.
cmd.Parameters.AddWithValue("@SurveyId", s.Id);
sqlConnection1.Open();
var result = cmd.ExecuteReader(); // execute la requete et return l'element de la première ligne à la première colonne
while (result.Read())
{
var survey_question = new Survey_Question()
{
SurveyId = Convert.ToInt32(result["SurveyId"]),
QuestionId = Convert.ToInt32(result["QuestionId"])
};
survey_questions.Add(survey_question);
}
;
}
return(survey_questions);
}
/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Survey_Question model)
{
using (var connection = OpenConnection())
{
const string sql = @"INSERT INTO dbo.Survey_Question
(
ExampaperID,
QuestionType,
QuestionContent,
QuestionOrder,
UpdateTime,
userID,
Status,
LinkSortPayGrade
)
VALUES
(
@exampaperid,
@questiontype,
@questioncontent,
@questionorder,
getdate(),
@userid,
0,
@linkSortPayGrade
);SELECT @@IDENTITY ID" ;
var param = new
{
exampaperid = model.ExampaperID,
questiontype = model.QuestionType,
questioncontent = model.QuestionContent,
questionorder = model.QuestionOrder,
userid = model.UserID,
linkSortPayGrade = model.LinkSortPayGrade
};
dynamic item = connection.Query <dynamic>(sql, param).FirstOrDefault();
model.QuestionID = decimal.ToInt32(item.ID);
}
}
public JsonResult SubmitImportQuestion(int order)
{
string folder = ConfigurationManager.AppSettings["SurveyUrl"];
string filename = "";
string resultName = "";
string result = "0";
try
{
HttpFileCollectionBase FileData = Request.Files;
filename = Path.GetFileName(FileData[0].FileName); //获得文件名
string fullPathname = Path.Combine(folder, filename); //文件后缀名
string suffix = FileData[0].FileName.Substring(FileData[0].FileName.LastIndexOf(".") + 1).ToLower();
resultName = Guid.NewGuid() + "." + suffix;
saveFile(FileData, folder, resultName);
List <DataTable> dtList = new Spreadsheet().LoadExcel(HttpContext.Server.MapPath(folder) + resultName, 1);
var questionlist = new List <Survey_Question>();
var answerList = new Survey_QuestionAnswer();
var newQuestionList = new List <Survey_Question>();
//题型数
var single = 0;
var Multi = 0;
var subject = 0;
var xingp = 0;
var flag = true;
int rowcount = 0;
var message = "";
if (IsTrueTemplate(dtList[0]))
{
foreach (DataRow row in dtList[0].Rows)
{
rowcount++;
var question = new Survey_Question();
//不为空的时候是题目，否则是选项
if (row[0].ToString() != "")
{
if (row[1].ToString() != "")
{
order = order + 1;
question.QuestionType = (int)((Enums.QuestionType)Enum.Parse(typeof(Enums.QuestionType), row[0].ToString()));
switch (question.QuestionType)
{
case 0:
single++;
break;
case 1:
Multi++;
break;
case 2:
subject++;
break;
case 3:
xingp++;
break;
}
question.QuestionContent = row[1].ToString();
question.QuestionOrder = order;
questionlist.Add(question);
}
else
{
flag = false;
message = message + "," + rowcount;
}
}
else
{
if (questionlist[questionlist.Count - 1].QuestionType != 2)
{
var ordr = questionlist[questionlist.Count - 1].Answers.Count == 0 ? 64 : (int)(System.Text.Encoding.ASCII.GetBytes(questionlist[questionlist.Count - 1].Answers.LastOrDefault().ShowOrder)[0]);
questionlist[questionlist.Count - 1].Answers.Add(new Survey_QuestionAnswer()
{
AnswerContent = row[1].ToString(),
ShowOrder = ((char)(ordr + 1)).ToString()
});
}
}
}
message = message == "" ? "" : message.TrimStart(',') + "行，题目为空";
foreach (var item in questionlist)
{
if (item.QuestionType == 0 || item.QuestionType == 1)
{
if (item.Answers.Count != 0 && item.Answers.Count(p => p.AnswerContent != "") > 2)
{
newQuestionList.Add(item);
}
else
{
flag = false;
var mess = "题目：" + item.QuestionContent + "导入失败，客观题至少需要2个答案";
message = message == "" ? mess : message + ";" + mess;
}
}
else
{
newQuestionList.Add(item);
}
}
//questionlist = questionlist.OrderBy(p => p.QuestionOrder).ToList();
message = " 导入成功" + newQuestionList.Count + "题" + message;
}
else
{
return(Json(new
{
result = 0,
content = "请使用正确的模板"
}, "text/html", JsonRequestBehavior.AllowGet));
}
return(Json(new
{
questionlist = newQuestionList,
single = single,
Multi = Multi,
subject = subject,
xingp = xingp,
result = 1,
content = message
}, "text/html", JsonRequestBehavior.AllowGet));
}
catch
{
return(Json(new
{
questionlist = new List <Survey_Question>(),
result = 0,
content = "导入失败"
}, "text/html", JsonRequestBehavior.AllowGet));
}
}
public JsonResult SaveExampaper()
{
try
{
string data = Request.Form["exampaper"];
dynamic examdata = Newtonsoft.Json.JsonConvert.DeserializeObject(data);
Survey_Exampaper exampaper;
int examId = examdata.examId;
if (examId > 0)
{
exampaper = SeBl.GetSurveyExampaper(examId);
}
exampaper = new Survey_Exampaper
{
ExamDescription = examdata.examDesc,
ExamTitle = examdata.examTitle,
ExamType = examdata.examType,
ExampaperID = examdata.examId,
SortID = examdata.sortID,
LastUpdateTime = DateTime.Now,
Questions = new List <Survey_Question>()
};
if (exampaper.ExampaperID == 0)
{
exampaper.UserID = CurrentUser.UserId;
}
for (int i = 0; i < examdata.questions.Count; i++)
{
var question = new Survey_Question
{
QuestionContent = examdata.questions[i].title,
QuestionOrder = examdata.questions[i].order,
QuestionType = examdata.questions[i].type,
Status = 0,
UpdateTime = DateTime.Now,
UserID = CurrentUser.UserId,
LinkSortPayGrade = examdata.questions[i].sortpayGrade,
Answers = new List <Survey_QuestionAnswer>()
};
question.QuestionContent = question.QuestionContent.HtmlDecode();
foreach (var answer in examdata.questions[i].answers)
{
var a = new Survey_QuestionAnswer
{
AnswerContent = answer.content,
ShowOrder = answer.order
};
question.Answers.Add(a);
}
exampaper.Questions.Add(question);
}
SeBl.AddExampaper(exampaper);
return(Json(new
{
result = 1
}, JsonRequestBehavior.AllowGet));
}
catch
{
return(Json(new
{
result = 0
}, JsonRequestBehavior.AllowGet));
}
}
public IEnumerable <Question> GetSurvey_Questions(int surveyId)
{
IEnumerable <Question> questions = new List <Question>();
IEnumerable <Answer> answers = new List <Answer>();
using (SqlConnection sqlConnection1 = new SqlConnection(_connectionString)) // using permet de refermer la connection après ouverture
{
//ouvre la connection à la base de donnée.
var cmd = new SqlCommand // objet cmd me permet d'exécuter des requêtes SQL
{
CommandType = CommandType.Text, // methode permettant de definir le type de commande (text = une commande sql; Storeprocedure= le nom de la procedure stockée; TableDirect= le nom d'une table.
CommandText = "SELECT QuestionId FROM Survey_Question WHERE SurveyId = @surveyId;", // stock la requete sql dans commandText. SCOPE_IDENTITY renvoie l'Id de la question inseré.
Connection = sqlConnection1, // etablie la connection.
};
// permet de definir les variables values dans CommandText.
cmd.Parameters.AddWithValue("@SurveyId", surveyId);
sqlConnection1.Open();
var result = cmd.ExecuteReader(); // execute la requete et return l'element de la première ligne à la première colonne
var survey_questions = new List <Survey_Question>();
while (result.Read())
{
var survey_question = new Survey_Question()
{
SurveyId = surveyId,
QuestionId = Convert.ToInt32(result["QuestionId"])
};
survey_questions.Add(survey_question);
}
if (survey_questions.Any())
{
questions = _questionDao.GetAll()?.Where(q => survey_questions.Any(sq => sq.QuestionId == q.Id));
}
//une autre manière de recupération de question.
//foreach (var element in survey_questions)
//{
// var getQuestionById = _questionDao.GetBydId(element.QuestionId);
// var
// foreach (var question in questions)
// {
// var q = new Question()
// {
// Id = getQuestionById.Id,
// Content = getQuestionById.Content,
// Category = getQuestionById.Category,
// Difficulty = getQuestionById.Difficulty,
// Type = getQuestionById.Type
// };
// questions.Add(q);
// }
//}
//};
}
return(questions);
}
