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 AddAnswers(int form_id)
{
List <question> quest = db.question.Where(p => p.id_form == form_id).ToList(); //Список вопросов данной формы
List <question_else> list_else = db.question_else.Where(p => p.question.id_form == form_id).ToList(); //Список вариантов "Другое" у вопросов данной формы
List <answer_text> answer_text = new List <answer_text>(); //Список для текстовых ответов на вопрос
List <answer_table> answer_table = new List <answer_table>(); //Список для ответов на табличные вопросы
List <answer_variant> asnwer_variant = new List <answer_variant>(); //Список ответов на вопросы с типами от 2 до 4
List <answer_else> answer_else = new List <answer_else>(); //Список для ответов, содержащихся в поле "другое"
int id1 = (db.answer_text.Count() > 0)?(db.answer_text.Max(p => p.id) + 1):1, id2 = (db.answer_table.Count() > 0) ? (db.answer_table.Max(p => p.id) + 1) : 1, id3 = (db.answer_variant.Count() > 0) ? (db.answer_variant.Max(p => p.id) + 1) : 1, id4 = (db.answer_else.Count() > 0) ? (db.answer_else.Max(p => p.id) + 1) : 1;
user user = new user();
user.id = (db.user.Count() > 0) ? (db.user.Max(p => p.id) + 1) : 1;
System.Web.HttpContext context = System.Web.HttpContext.Current;
string ipAddress = context.Request.ServerVariables["HTTP_X_FORWARDED_FOR"];
if (!string.IsNullOrEmpty(ipAddress))
{
string[] addresses = ipAddress.Split(',');
if (addresses.Length != 0)
{
user.ip = addresses[0];
}
}
else
{
user.ip = context.Request.ServerVariables["REMOTE_ADDR"];;
}
user.id_subject = Convert.ToInt32(Request.Form["subject"]);
user.id_school = Convert.ToInt32(Request.Form["school"]);
db.user.Add(user);
db.SaveChanges();
foreach (var item in quest)
{
if (item.id_type_question == 1)
{
string result = Request.Form["text_" + item.id];
if (result != null)
{
answer_text a = new answer_text();
a.id = id1++;
a.id_question = item.id;
a.name = result;
a.id_user = user.id;
answer_text.Add(a);
}
}
else if (item.id_type_question == 2)
{
string result = Request.Form["radio_" + item.id];
if (result != null)
{
answer_variant a = new answer_variant();
a.id = id3++;
a.id_variant = Convert.ToInt32(result);
a.id_user = user.id;
asnwer_variant.Add(a);
}
}
else if (item.id_type_question == 3)
{
string result = Request.Form["checkbox_" + item.id];
if (result != null)
{
string[] arr = result.Split(',');
foreach (var i in arr)
{
answer_variant a = new answer_variant();
a.id = id3++;
a.id_variant = Convert.ToInt32(i);
a.id_user = user.id;
asnwer_variant.Add(a);
}
}
}
else if (item.id_type_question == 4)
{
string result = Request.Form["select_" + item.id];
if (result != null)
{
answer_variant a = new answer_variant();
a.id = id3++;
a.id_variant = Convert.ToInt32(result);
a.id_user = user.id;
asnwer_variant.Add(a);
}
}
else if (item.id_type_question == 5)
{
List <table_question> t_q = db.table_question.Where(p => p.id_question == item.id).ToList();
foreach (var q in t_q)
{
string result = Request.Form["table_" + item.id + "_" + q.id];
if (result != null)
{
answer_table a = new answer_table();
a.id = id2++;
a.id_table_variant = Convert.ToInt32(result);
a.id_table_question = q.id;
a.id_user = user.id;
answer_table.Add(a);
}
}
}
else if (item.id_type_question == 6)
{
List <table_question> t_q = db.table_question.Where(p => p.id_question == item.id).ToList();
foreach (var q in t_q)
{
string result = Request.Form["table_" + item.id + "_" + q.id];
if (result != null)
{
string[] arr = result.Split(',');
foreach (var i in arr)
{
answer_table a = new answer_table();
a.id = id2++;
a.id_table_variant = Convert.ToInt32(i);
a.id_table_question = q.id;
a.id_user = user.id;
answer_table.Add(a);
}
}
}
}
else if (item.id_type_question == 7)
{
if (Request.Files.Count > 0)
{
var file = Request.Files["file_" + item.id];
if (file != null && file.ContentLength > 0)
{
string newName = String.Format("File_{0}_{1}{2}", DateTime.Now.ToString("yyyyMMddHHmmssfff"), Guid.NewGuid(), Path.GetExtension(file.FileName));
var fileName = Path.GetFileName(file.FileName);
var path = Path.Combine(Server.MapPath("~/Content/files/"), newName);
file.SaveAs(path);
variant_file v = db.variant_file.First(p => p.question_id == item.id);
answer_file a = new answer_file();
a.id = (db.answer_file.Count() > 0) ? (db.answer_file.Max(p => p.id) + 1) : 1;
a.link = "~/Content/files/" + newName;
a.user_id = user.id;
a.variant_file_id = v.id;
db.answer_file.Add(a);
db.SaveChanges();
}
}
}
if ((item.id_type_question == 1 || item.id_type_question == 2 || item.id_type_question == 3) && list_else.Count(p => p.id_question == item.id) > 0)
{
string result = Request.Form["else_" + item.id];
if (result != null && !result.Equals(""))
{
question_else q_e = list_else.First(p => p.id_question == item.id);
answer_else a_e = new answer_else();
a_e.id = id4++;
a_e.id_question_else = q_e.id;
a_e.name = result;
a_e.id_user = user.id;
answer_else.Add(a_e);
}
}
}
db.answer_text.AddRange(answer_text);
db.answer_variant.AddRange(asnwer_variant);
db.answer_table.AddRange(answer_table);
db.answer_else.AddRange(answer_else);
db.SaveChanges();
return(Redirect("/Anketa/Send?form_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));
}
//Результаты. Статистическая информация
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));
}
