public ActionResult AddQuestion(int type_q, int form_id)
{
question q = new question(); //Создание нового вопроса
q.id = (db.question.Count() > 0) ? (db.question.Max(p => p.id) + 1) : 1; //идентификатор для нового вопроса
q.id_form = form_id; //для какой формы создается вопрос
q.id_type_question = type_q; //тип вопроса
q.number = Convert.ToInt32(Request.Form["number_ask"]); //номер вопроса в форме
q.name = Request.Unvalidated.Form["text_quest"]; //вопрос
q.video_url = Request.Form["video"]; //ссылка на видео (если есть)
q.is_required = (Convert.ToInt32(Request.Form["is_required"]) == 1) ? true : false; //обязательно или нет отвечать на вопрос
var f = Request.Files["file"]; //прикреплен ли файл (картинка)
if (f != null && f.ContentLength > 0) //если файл прикреплен и он не пустой
{
string newName = String.Format("File_{0}_{1}{2}", DateTime.Now.ToString("yyyyMMddHHmmssfff"), Guid.NewGuid(), Path.GetExtension(f.FileName)); //изменение имени файла
var fileName = Path.GetFileName(f.FileName);
var path = Path.Combine(Server.MapPath("~/Content/quest_file/"), newName); //путь для сохранения файла с новым именем
f.SaveAs(path); //сохранение файла
q.img_url = "/Content/quest_file/" + newName; //относительный путь к файлу
}
q.comment = Request.Unvalidated.Form["comment"]; //комментарий к вопросу
db.question.Add(q); //добавление вопроса в бд
db.SaveChanges(); //сохранение
if (type_q == 2 || type_q == 3 || type_q == 4) //если тип вопроса от 2 до 4
{
string result = Request.Unvalidated.Form["variant"]; //список вариантов для ответа в одной строке через запятую
String[] variant = result.Split(','); //преобразуем из строки в массив вариантов ответов
List <variant> list_q = new List <variant>(); //для хранения вариантов ответа в экземплярах класса variant, для дальнейшего сохранения в бд
int id_variant = (db.variant.Count() > 0) ? (db.variant.Max(p => p.id) + 1) : 1; //идентификатор варианта
for (int i = 0; i < variant.Count(); ++i) //перебор вариантов ответов из массива
{
if (!variant[i].Equals("")) //если вариант не пустой
{
variant quest = new variant(); //создание нового экземпляра класса variant
quest.id = id_variant++; //уникальный идентификатор
quest.name = variant[i].Replace(",", ",").Trim();; //вариант (текст)
quest.number = i + 1; //номер варианта
quest.id_question = q.id; //идентификатор вопроса, одним из возможных ответов которого может быть данный вариант
list_q.Add(quest); //добавление созданного и проинициализированного варианта в список
}
}
db.variant.AddRange(list_q); //добавления всех вариантов списка в бд
db.SaveChanges(); //сохранение
string else_v = Request.Unvalidated.Form["else"]; //вариант "другое"
if (else_v != null) //если добавлен вариант "другой"
{
question_else q_else = new question_else(); //создание экземпляра класса question_else
q_else.id = (db.question_else.Count() > 0) ? (db.question_else.Max(p => p.id) + 1) : 1; //идентификатор
q_else.id_question = q.id; //идентификатор вопроса, у коготого будет в вариантах ответа поле "другое"
q_else.name = else_v.Trim(); //название для поля "другое"
db.question_else.Add(q_else); //добавление в бд
db.SaveChanges(); //сохранение
}
}
else if (type_q == 5 || type_q == 6) //если тип вопроса 5 или 6
{
string row = Request.Unvalidated.Form["table_row"]; //список вопросов в виде строки через запятую
string[] rows = row.Split(','); //список вопросов в виде массива
string col = Request.Unvalidated.Form["table_col"]; //список вариантов ответов через запятую
string[] cols = col.Split(','); //список вариантов ответов в виде массива
List <table_question> list_rows = new List <table_question>();
int id = (db.table_question.Count() > 0) ? (db.table_question.Max(p => p.id) + 1) : 1;
for (int i = 0; i < rows.Count(); ++i)
{
table_question t_q = new table_question();
t_q.id = id++;
t_q.id_question = q.id;
t_q.number = i + 1;
t_q.name = rows[i].Replace(",", ",").Trim();
list_rows.Add(t_q);
}
List <table_variant> list_cols = new List <table_variant>();
id = (db.table_variant.Count() > 0) ? (db.table_variant.Max(p => p.id) + 1) : 1;
for (int i = 0; i < cols.Count(); ++i)
{
table_variant t_v = new table_variant();
t_v.id = id++;
t_v.id_question = q.id;
t_v.number = i + 1;
t_v.name = cols[i].Replace(",", ",").Trim();
list_cols.Add(t_v);
}
db.table_question.AddRange(list_rows);
db.table_variant.AddRange(list_cols);
db.SaveChanges();
}
else if (type_q == 7) //если тип вопроса = 7
{
string row = Request.Form["file"];
if (row != null)
{
variant_file file = new variant_file();
file.id = (db.variant_file.Count() > 0) ? (db.variant_file.Max(p => p.id) + 1) : 1;
file.question_id = q.id;
file.max_size_file = Convert.ToInt32(row);
db.variant_file.Add(file);
db.SaveChanges();
}
}
return(Redirect("/forms/details?id=" + form_id + "#addask"));
}
//Результаты. Статистическая информация
public ActionResult ExportStatistics(int form_id)
{
int cols = 1;
List <answer_text> answer_text = db.answer_text.Where(p => p.question.id_form == form_id).ToList();
List <answer_variant> answer_variant = db.answer_variant.Where(p => p.variant.question.id_form == form_id).ToList();
List <answer_table> answer_table = db.answer_table.Where(p => p.table_question.question.id_form == form_id).ToList();
List <answer_file> answer_file = db.answer_file.Where(p => p.variant_file.question.id_form == form_id).ToList();
List <answer_else> answer_else = db.answer_else.Where(p => p.question_else.question.id_form == form_id).ToList();
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Статистическая информация");
List <question> list = db.question.Where(p => p.id_form == form_id).ToList();
List <user> users = db.user.Where(p => p.answer_text.Count(s => s.question.id_form == form_id) > 0 || p.answer_variant.Count(s => s.variant.question.id_form == form_id) > 0 || p.answer_table.Count(s => s.table_question.question.id_form == form_id) > 0 || p.answer_file.Count(s => s.variant_file.question.id_form == form_id) > 0).ToList();
for (int i = 0; i < list.Count(); ++i)
{
question q = list.ElementAt(i);
workSheet.Cells[1, cols].Value = list.ElementAt(i).name;
if (list.ElementAt(i).id_type_question == 1)
{
workSheet.Cells[4, cols].Value = answer_text.Count(p => p.id_question == q.id);
cols++;
}
else if (list.ElementAt(i).id_type_question == 2 || list.ElementAt(i).id_type_question == 3 || list.ElementAt(i).id_type_question == 4)
{
List <variant> a2 = db.variant.Where(p => p.id_question == q.id).ToList();
for (int j = 0; j < a2.Count(); ++j)
{
variant v = a2.ElementAt(j);
workSheet.Cells[3, cols].Value = v.name;
workSheet.Cells[4, cols].Value = users.Count(p => p.answer_variant.Count(d => d.id_variant == v.id) > 0);
cols++;
}
if (q.question_else.Count() > 0)
{
question_else q_e = db.question_else.First(p => p.id_question == q.id);
workSheet.Cells[3, cols].Value = q_e.name;
workSheet.Cells[4, cols].Value = users.Count(p => p.answer_else.Count(d => d.id_question_else == q_e.id) > 0);
cols++;
}
}
else if (list.ElementAt(i).id_type_question == 5 || list.ElementAt(i).id_type_question == 6)
{
List <table_question> table_question = db.table_question.Where(p => p.id_question == q.id).ToList();
for (int k = 0; k < table_question.Count(); ++k)
{
table_question t_q = table_question.ElementAt(k);
workSheet.Cells[2, cols].Value = t_q.name;
List <table_variant> t_V = db.table_variant.Where(p => p.id_question == t_q.id_question).ToList();
for (int l = 0; l < t_V.Count(); ++l)
{
table_variant t = t_V.ElementAt(l);
workSheet.Cells[3, cols].Value = t.name;
workSheet.Cells[4, cols].Value = users.Count(p => p.answer_table.Count(d => d.id_table_variant == t.id && d.id_table_question == t_q.id) > 0);
cols++;
}
}
}
else if (list.ElementAt(i).id_type_question == 7)
{
workSheet.Cells[4, cols].Value = answer_file.Count(p => p.variant_file.question_id == q.id);
cols++;
}
}
var path = Path.Combine(Server.MapPath("~/Content/files/"), "static_information_" + form_id + "_" + DateTime.Now.ToString("dd_MM_yyyy") + ".xlsx");
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + path);
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
FileInfo fi = new FileInfo(path);
long sz = fi.Length;
Response.ClearContent();
Response.ContentType = Path.GetExtension(path);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename = {0}", System.IO.Path.GetFileName(path)));
Response.AddHeader("Content-Length", sz.ToString("F0"));
Response.TransmitFile(path);
Response.End();
System.IO.File.Delete(path);
return(Redirect("/Forms/Details?id=" + form_id));
}
public ActionResult EditQuestion(int id, string text_quest, int is_required, int number_ask, string comment)
{
question question = db.question.Find(id);
question.name = text_quest;
question.is_required = (is_required == 1) ? true : false;
question.number = number_ask;
question.comment = comment;
question.video_url = Request.Form["video"];
var f = Request.Files["file"];
if (f != null && f.ContentLength > 0)
{
string newName = String.Format("File_{0}_{1}{2}", DateTime.Now.ToString("yyyyMMddHHmmssfff"), Guid.NewGuid(), Path.GetExtension(f.FileName));
var fileName = Path.GetFileName(f.FileName);
var path = Path.Combine(Server.MapPath("~/Content/quest_file/"), newName);
f.SaveAs(path);
question.img_url = "/Content/quest_file/" + newName;
}
else
{
question.img_url = null;
}
db.question.AddOrUpdate(question);
db.SaveChanges();
if (question.id_type_question == 2 || question.id_type_question == 3 || question.id_type_question == 4) //RADIO, CHECKBOX, SELECT
{
string result = Request.Unvalidated.Form["variant"];
String[] variant = result.Split(','); //список вариантов в виде массива
List <variant> v = db.variant.Where(p => p.id_question == id).ToList();
for (int i = 0; i < variant.Count(); ++i)
{
if (!variant[i].Equals(""))
{
int k = 0;
variant[i] = variant[i].Replace(",", ",");
foreach (var item in v)
{
if (item.name.CompareTo(variant[i]) == 0)
{
k = 1;
break;
}
}
if (k == 0)
{
variant quest = new variant();
quest.id = (db.variant.Count() > 0) ? (db.variant.Max(p => p.id) + 1) : 1;
quest.name = variant[i];
quest.number = i + 1;
quest.id_question = question.id;
db.variant.Add(quest);
db.SaveChanges();
}
}
}
for (int j = 0; j < v.Count(); ++j)
{
int k = 0;
for (int i = 0; i < variant.Count(); ++i)
{
if (v.ElementAt(j).name.CompareTo(variant[i]) == 0)
{
k = 1;
break;
}
}
if (k == 0)
{
int var_id = v.ElementAt(j).id;
List <answer_variant> a = db.answer_variant.Where(p => p.variant.id == var_id).ToList();
db.answer_variant.RemoveRange(a);
db.SaveChanges();
variant del = v.ElementAt(j);
db.variant.Remove(del);
v.RemoveAt(j);
db.SaveChanges();
--j;
}
}
//Вариант ДРУГОЕ
string else_v = Request.Form["else"];
int db_else = db.question_else.Count(p => p.id_question == id);
if (db_else > 0 && else_v == null)
{
if (db.answer_else.Count(p => p.question_else.id_question == id) > 0)
{
List <answer_else> a = db.answer_else.Where(p => p.question_else.id_question == id).ToList();
db.answer_else.RemoveRange(a);
db.SaveChanges();
}
List <question_else> b = db.question_else.Where(p => p.id_question == id).ToList();
db.question_else.RemoveRange(b);
db.SaveChanges();
}
else if (db_else == 0 && else_v != null)
{
question_else q_else = new question_else();
q_else.id = (db.question_else.Count() > 0) ? (db.question_else.Max(p => p.id) + 1) : 1;
q_else.id_question = question.id;
q_else.name = else_v;
db.question_else.Add(q_else);
db.SaveChanges();
}
else if (db_else > 0 && else_v != null)
{
question_else q_else = db.question_else.First(p => p.id_question == id);
q_else.name = else_v;
db.question_else.AddOrUpdate(q_else);
db.SaveChanges();
}
//Вариант ДРУГОЕ (end)
}
else if (question.id_type_question == 5 || question.id_type_question == 6) //TYPE TABLE_RADIO AND TABLE_CHECKBOX
{
//DEL TABLE ROW
string row = Request.Unvalidated.Form["table_row"];
string[] rows = row.Split(',');
List <table_question> q = db.table_question.Where(p => p.id_question == question.id).ToList();
for (int i = 0; i < rows.Count(); ++i)
{
int k = 0;
foreach (var item in q)
{
if (item.name.CompareTo(rows[i]) == 0)
{
k = 1;
break;
}
}
if (k == 0)
{
table_question t_q = new table_question();
t_q.id = (db.table_question.Count() > 0) ? (db.table_question.Max(p => p.id) + 1) : 1;;
t_q.id_question = question.id;
t_q.number = i + 1;
t_q.name = rows[i].Replace(",", ",");
db.table_question.Add(t_q);
db.SaveChanges();
}
}
for (int i = 0; i < q.Count(); ++i)
{
int k = 0;
foreach (var item in rows)
{
string name = q.ElementAt(i).name;
if (item.CompareTo(name) == 0)
{
k = 1;
break;
}
}
if (k == 0)
{
int var_id = q.ElementAt(i).id;
List <answer_table> a = db.answer_table.Where(p => p.id_table_question == var_id).ToList();
db.answer_table.RemoveRange(a);
db.SaveChanges();
table_question del = q.ElementAt(i);
db.table_question.Remove(del);
q.RemoveAt(i);
db.SaveChanges();
--i;
}
}
//DEL TABLE ROW (END)
//DEL TABLE COL
string col = Request.Unvalidated.Form["table_col"];
string[] cols = col.Split(',');
List <table_variant> v = db.table_variant.Where(p => p.id_question == question.id).ToList();
for (int i = 0; i < cols.Count(); ++i)
{
int k = 0;
foreach (var item in v)
{
if (item.name.CompareTo(cols[i]) == 0)
{
k = 1;
break;
}
}
if (k == 0)
{
table_variant t_v = new table_variant();
t_v.id = (db.table_variant.Count() > 0) ? (db.table_variant.Max(p => p.id) + 1) : 1;;
t_v.id_question = question.id;
t_v.number = i + 1;
t_v.name = cols[i].Replace(",", ",");
db.table_variant.Add(t_v);
db.SaveChanges();
}
}
for (int i = 0; i < v.Count(); ++i)
{
int k = 0;
foreach (var item in cols)
{
string name = v.ElementAt(i).name;
if (item.CompareTo(name) == 0)
{
k = 1;
break;
}
}
if (k == 0)
{
int var_id = v.ElementAt(i).id;
List <answer_table> a = db.answer_table.Where(p => p.id_table_variant == var_id).ToList();
db.answer_table.RemoveRange(a);
db.SaveChanges();
table_variant del = v.ElementAt(i);
db.table_variant.Remove(del);
v.RemoveAt(i);
db.SaveChanges();
--i;
}
}
//DEL TABLE COL (END)
}
if (question.id_type_question == 7) //TYPE FILE
{
string row = Request.Form["file"];
if (row != null)
{
variant_file file = db.variant_file.First(p => p.question_id == question.id);
file.max_size_file = Convert.ToInt32(row);
db.variant_file.AddOrUpdate(file);
db.SaveChanges();
}
}
return(View(question));
}
//Результаты (ответы) по выбранной анкете в файле .excel
public ActionResult ExportExcel(int form_id)
{
int rows = 1;
int cols = 5;
List <answer_text> answer_text = db.answer_text.Where(p => p.question.id_form == form_id).ToList();
List <answer_variant> answer_variant = db.answer_variant.Where(p => p.variant.question.id_form == form_id).ToList();
List <answer_table> answer_table = db.answer_table.Where(p => p.table_question.question.id_form == form_id).ToList();
List <answer_file> answer_file = db.answer_file.Where(p => p.variant_file.question.id_form == form_id).ToList();
List <answer_else> answer_else = db.answer_else.Where(p => p.question_else.question.id_form == form_id).ToList();
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Результаты");
List <question> list = db.question.Where(p => p.id_form == form_id).OrderBy(p => p.number).ToList();
List <user> users = db.user.Where(p => p.answer_text.Count(s => s.question.id_form == form_id) > 0 || p.answer_variant.Count(s => s.variant.question.id_form == form_id) > 0 || p.answer_table.Count(s => s.table_question.question.id_form == form_id) > 0 || p.answer_file.Count(s => s.variant_file.question.id_form == form_id) > 0).ToList();
workSheet.Cells[1, 1].Value = "Область";
workSheet.Cells[1, 2].Value = "Район";
workSheet.Cells[1, 3].Value = "Школа";
workSheet.Cells[1, 4].Value = "Предмет";
rows = 3;
for (int s = 0; s < users.Count(); ++s)
{
user u = users.ElementAt(s);
workSheet.Cells[rows, 1].Value = u.schools.districts.areas.name;
workSheet.Cells[rows, 2].Value = u.schools.districts.name;
workSheet.Cells[rows, 3].Value = u.schools.name;
workSheet.Cells[rows++, 4].Value = u.subjects.name;
}
for (int i = 0; i < list.Count(); ++i)
{
question q = list.ElementAt(i);
workSheet.Cells[1, cols].Value = list.ElementAt(i).name;
rows = 3;
if (list.ElementAt(i).id_type_question == 1)
{
for (int j = 0; j < users.Count(); ++j)
{
user u = users.ElementAt(j);
answer_text a1 = answer_text.FirstOrDefault(p => p.id_user == u.id && p.id_question == q.id);
workSheet.Cells[rows++, cols].Value = (a1 != null) ? a1.name : "";
}
cols++;
}
else if (list.ElementAt(i).id_type_question == 2 || list.ElementAt(i).id_type_question == 4)
{
for (int j = 0; j < users.Count(); ++j)
{
user u = users.ElementAt(j);
answer_variant a2 = answer_variant.FirstOrDefault(p => p.id_user == u.id && p.variant.id_question == q.id);
string ask = (a2 != null) ? a2.variant.name : "";
if (answer_else.FirstOrDefault(p => p.question_else.id_question == q.id && p.id_user == u.id) != null)
{
if (a2 != null)
{
ask += ", ";
}
ask += answer_else.FirstOrDefault(p => p.question_else.id_question == q.id && p.id_user == u.id).name;
}
workSheet.Cells[rows++, cols].Value = ask;
}
cols++;
}
else if (list.ElementAt(i).id_type_question == 3)
{
for (int j = 0; j < users.Count(); ++j)
{
user u = users.ElementAt(j);
List <answer_variant> a2 = answer_variant.Where(p => p.id_user == u.id && p.variant.id_question == q.id).ToList();
string res = "";
for (int k = 0; k < a2.Count(); ++k)
{
res += a2.ElementAt(k).variant.name;
if (k != (a2.Count() - 1))
{
res += ", ";
}
}
answer_else el = answer_else.FirstOrDefault(p => p.question_else.id_question == q.id && p.id_user == u.id);
if (el != null)
{
res += ", " + el.name;
}
workSheet.Cells[rows++, cols].Value = res;
}
cols++;
}
else if (list.ElementAt(i).id_type_question == 5)
{
List <table_question> table_question = db.table_question.Where(p => p.id_question == q.id).ToList();
for (int k = 0; k < table_question.Count(); ++k)
{
table_question t_q = table_question.ElementAt(k);
workSheet.Cells[2, cols].Value = t_q.name;
rows = 3;
for (int j = 0; j < users.Count(); ++j)
{
user u = users.ElementAt(j);
answer_table a3 = answer_table.FirstOrDefault(p => p.id_user == u.id && p.id_table_question == t_q.id);
workSheet.Cells[rows++, cols].Value = (a3 != null) ? a3.table_variant.name : "";
}
cols++;
}
}
else if (list.ElementAt(i).id_type_question == 6)
{
List <table_question> table_question = db.table_question.Where(p => p.id_question == q.id).ToList();
for (int k = 0; k < table_question.Count(); ++k)
{
table_question t_q = table_question.ElementAt(k);
workSheet.Cells[2, cols].Value = t_q.name;
rows = 3;
for (int j = 0; j < users.Count(); ++j)
{
user u = users.ElementAt(j);
List <answer_table> a3 = answer_table.Where(p => p.id_user == u.id && p.id_table_question == t_q.id).ToList();
string res = "";
for (int w = 0; w < a3.Count(); ++w)
{
res += a3.ElementAt(w).table_variant.name;
if (w < (a3.Count() - 1))
{
res += ", ";
}
}
workSheet.Cells[rows++, cols].Value = res;
}
cols++;
}
}
else if (list.ElementAt(i).id_type_question == 7)
{
for (int j = 0; j < users.Count(); ++j)
{
user u = users.ElementAt(j);
answer_file a4 = answer_file.FirstOrDefault(p => p.user_id == u.id && p.variant_file.question_id == q.id);
workSheet.Cells[rows++, cols].Value = (a4 != null) ? a4.link : "";
}
cols++;
}
}
var path = Path.Combine(Server.MapPath("~/Content/files/"), "ask_" + form_id + "_" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx");
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + path);
excel.SaveAs(memoryStream);
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
FileInfo fi = new FileInfo(path);
long sz = fi.Length;
Response.ClearContent();
Response.ContentType = Path.GetExtension(path);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename = {0}", System.IO.Path.GetFileName(path)));
Response.AddHeader("Content-Length", sz.ToString("F0"));
Response.TransmitFile(path);
Response.End();
System.IO.File.Delete(path);
return(Redirect("/Forms/Details?id=" + form_id));
}