public void SaveArea(string Id, string Address, string Comment)
{
string comm = Globals.StringIsEmpty(Comment) ? "NULL" : "'" + Comment + "'";
string query = "UPDATE Areas SET Address='" + Address + "', Comment=" + comm + " WHERE IdArea=" + Id;
DbMess.DoAction(query);
}
public void AddArea(string Id, string Address, string Comment)
{
if (!Globals.StringIsEmpty(Address))
{
string comm = Globals.StringIsEmpty(Comment) ? "NULL" : "'" + Comment + "'";
string query = "INSERT INTO Areas VALUES('" + Address + "'," + comm + ", 0)";
DbMess.DoAction(query);
}
}
public void SaveOrganization(string Id, string Name, string Mail, string Phone, string Fio)
{
string mail = Globals.StringIsEmpty(Mail) ? "NULL" : "'" + Mail + "'";
string phone = Globals.StringIsEmpty(Phone) ? "NULL" : "'" + Phone + "'";
string fio = Globals.StringIsEmpty(Fio) ? "NULL" : "'" + Fio + "'";
string query = "UPDATE Organizations SET Name='" + Name + "', Email=" + mail + ",Phone=" + phone + ", ContactFio=" + fio + " WHERE IdOrganization=" + Id;
DbMess.DoAction(query);
}
public void AddOrganization(string Id, string Name, string Mail, string Phone, string Fio)
{
string mail = Globals.StringIsEmpty(Mail) ? "NULL" : "'" + Mail + "'";
string phone = Globals.StringIsEmpty(Phone) ? "NULL" : "'" + Phone + "'";
string fio = Globals.StringIsEmpty(Fio) ? "NULL" : "'" + Fio + "'";
string query = "INSERT INTO Organizations VALUES('" + Name + "'," + mail + "," + phone + "," + fio + ", 0)";
DbMess.DoAction(query);
}
public ActionResult SaveLecturer(string Id, string FIO, string OrganizationId, string Email, string Phone, HttpPostedFileBase newAvatar)
{
Console.WriteLine("bp");
//1) сохранить фотографию
string query = "";
if (newAvatar != null)
{
string fileName = Globals.GetRandomName(10);
string avatarExtension = "." + newAvatar.FileName.Split('.').Last();
string picturePath = null;
string normalPath = "/Files/Avatars/" + fileName + avatarExtension;
picturePath = Server.MapPath("~/Files/Avatars/" + fileName + avatarExtension);
newAvatar.SaveAs(picturePath);
//2) проверить, есть ли уже у этого препода фотография
query = "SELECT AvatarFileId FROM Lecturers WHERE IdLecturer=" + Id;
string fileId = DbMess.GetValue(query);
if (!Globals.StringIsEmpty(fileId))
{
//3.a) если есть, заменить путь
query = "UPDATE Files SET Path='" + picturePath + "' WHERE IdFile=" + fileId;
DbMess.DoAction(query);
}
else
{
//3.b) если нет, инсёртнуть, забрать айдишник, и обновить поле у проеподавателя
query = "INSERT INTO FILES VALUES('" + picturePath + "', '" + normalPath + "', GETDATE())";
DbMess.DoAction(query);
query = "SELECT MAX(IdFile) FROM Files";
query = "UPDATE Lecturers SET AvatarFileId=" + DbMess.GetValue(query);
DbMess.DoAction(query);
}
}
string org = Globals.StringIsEmpty(OrganizationId) ? "NULL" : OrganizationId;
string mail = Globals.StringIsEmpty(Email) ? "NULL" : "'" + Email + "'";
string phone = Globals.StringIsEmpty(Phone) ? "NULL" : "'" + Phone + "'";
//теперь обновить остальные поля ))
query = "UPDATE Lecturers SET FIO='" + FIO + "', OrganizationId=" + org + ", Email=" + mail + ", ContactPhone=" + phone + " WHERE IdLecturer=" + Id;
DbMess.DoAction(query);
return(Redirect("/catalog/lecturers"));
}
public void addCouse(string Id, string Name, bool IsRemote, string Length, string Type)
{
double finalLength = 0;
string length = "";
if (double.TryParse(Length, out finalLength))
{
length = finalLength.ToString();
}
else
{
length = "NULL";
}
string query = "INSERT INTO Courses VALUES('" + Name + "', " + Convert.ToInt32(IsRemote) + ", " + length + ", " + Type + ", 0)";
DbMess.DoAction(query);
}
public void SaveCouse(string Id, string Name, bool IsRemote, string Length, string Type)
{
double finalLength = 0;
string length = "";
if (double.TryParse(Length, out finalLength))
{
length = finalLength.ToString();
}
else
{
length = "NULL";
}
string query = "UPDATE Courses SET Name='" + Name + "', IsRemote=" + Convert.ToInt32(IsRemote) + ", LengthInHours=" + length + ", ResultType=" + Type + " WHERE IdCourse=" + Id;
DbMess.DoAction(query);
}
public AreasModel()
{
Areas = new List <Area>();
string query = "SELECT IdArea, Address, Comment FROM Areas WHERE IsDeleted=0 ORDER BY 1 ASC";
var table = DbMess.GetTable(query);
for (int i = 0; i < table.Rows.Count; i++)
{
Areas.Add(new Area()
{
Id = table.Rows[i][0].ToString(),
Address = table.Rows[i][1].ToString(),
Comment = table.Rows[i][2].ToString()
});
}
}
public OrganizationsModel()
{
Organizations = new List <Organization>();
string query = "SELECT * FROM Organizations WHERE IsDeleted=0";
var res = DbMess.GetTable(query);
for (int i = 0; i < res.Rows.Count; i++)
{
Organizations.Add(new Organization()
{
Id = Convert.ToInt32(res.Rows[i][0].ToString()),
Name = res.Rows[i][1].ToString(),
Email = res.Rows[i][2].ToString(),
Phone = res.Rows[i][3].ToString(),
ContactFio = res.Rows[i][4].ToString()
});
}
}
public CoursesModel()
{
Courses = new List <Course>();
string query = "SELECT IdCourse, Name, IsRemote, LengthInHours, ResultType FROM Courses WHERE IsDeleted=0";
var table = DbMess.GetTable(query);
for (int i = 0; i < table.Rows.Count; i++)
{
Courses.Add(new Course()
{
Id = Convert.ToInt32(table.Rows[i][0]),
Name = table.Rows[i][1].ToString(),
IsRemote = Convert.ToBoolean(table.Rows[i][2].ToString()),
LengthInHours = Globals.StringIsEmpty(table.Rows[i][3].ToString()) ? -1 : Convert.ToDouble(table.Rows[i][3].ToString()),
ResultType = Globals.StringIsEmpty(table.Rows[i][4].ToString()) ? CourseResultType.Presence : Convert.ToBoolean(table.Rows[i][4].ToString()) ? CourseResultType.Mark : CourseResultType.Sertificate
});
}
}
public void DeleteArea(string Id)
{
string query = "UPDATE Areas SET IsDeleted=1 WHERE IdArea=" + Id;
DbMess.DoAction(query);
}
public void DeleteOrganization(string id)
{
string query = "UPDATE Organizations SET IsDeleted=1 WHERE IdOrganization=" + id;
DbMess.DoAction(query);
}
public void DeleteCourse(string Id)
{
string query = "UPDATE Courses SET IsDeleted=1 WHERE IdCourse=" + Id;
DbMess.DoAction(query);
}
public LecturersModel()
{
Lecturers = new List <Lecturer>();
OrganizationIds = new List <string>();
OrganizationNames = new List <string>();
string query = "SELECT L.IdLecturer, L.FIO, L.OrganizationId, L.AvatarFileId, L.Email, L.ContactPhone, F.RelativePath, O.Name FROM Lecturers AS L LEFT JOIN Organizations AS O ON L.OrganizationId=O.IdOrganization LEFT JOIN Files AS F ON L.AvatarFileId=F.IdFile WHERE L.IsDeleted=0";
var table = DbMess.GetTable(query);
for (int i = 0; i < table.Rows.Count; i++)
{
Lecturers.Add(new Lecturer()
{
Id = Convert.ToInt32(table.Rows[i][0].ToString()),
FIO = table.Rows[i][1].ToString(),
OrganizationId = Globals.GetSafeIntVal(table.Rows[i][2].ToString()),
FileId = Globals.GetSafeIntVal(table.Rows[i][3].ToString()),
Email = table.Rows[i][4].ToString(),
ContactPhone = table.Rows[i][5].ToString(),
FilePath = table.Rows[i][6].ToString(),
OrganizationName = table.Rows[i][7].ToString()
});
}
query = "SELECT IdOrganization, Name FROM Organizations WHERE IsDeleted=0";
table = DbMess.GetTable(query);
for (int i = 0; i < table.Rows.Count; i++)
{
OrganizationIds.Add(table.Rows[i][0].ToString());
OrganizationNames.Add(table.Rows[i][1].ToString());
}
JSCode1 = "var availableOrgs = [";
for (int i = 0; i < OrganizationIds.Count; i++)
{
JSCode1 += " {label:\"" + OrganizationNames[i] + "\", idwka:\"" + OrganizationIds[i] + "\", value:\"" + OrganizationNames[i] + "\"}";
if (i != OrganizationIds.Count - 1)
{
JSCode1 += ",";
}
}
JSCode1 += "];";
JSCode2 = "var availableCourses = [";
query = "SELECT IdCourse, Name FROM Courses WHERE IsDeleted=0";
table = DbMess.GetTable(query);
for (int i = 0; i < table.Rows.Count; i++)
{
JSCode2 += " {label:\"" + table.Rows[i][1].ToString() + "\", idwka:\"" + table.Rows[i][0].ToString() + "\", value:\"" + table.Rows[i][1].ToString() + "\"}";
if (i != table.Rows.Count - 1)
{
JSCode2 += ",";
}
}
JSCode2 += "];";
}