public static string uploadFile(HttpPostedFile postedFile, string directoryToUpload)
{
try
{
string localName = postedFile.FileName;
string extension = postedFile.FileName.Remove(postedFile.FileName.LastIndexOf('.'));
extension = postedFile.FileName.Replace(extension, "").ToLower();
string name = Guid.NewGuid().ToString();
postedFile.SaveAs(HttpContext.Current.Server.MapPath(directoryToUpload) + name + extension);
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "INSERT INTO [uploadedFiles] (name, extension, path, userLocalFileName, ownerId, uploadDate) VALUES (@name, @extension, @path, @userLocalFileName, @ownerId, @uploadDate)";
dboManager.command.Parameters.AddWithValue("@name", name);
dboManager.command.Parameters.AddWithValue("@extension", extension);
dboManager.command.Parameters.AddWithValue("@path", directoryToUpload);
dboManager.command.Parameters.AddWithValue("@userLocalFileName", localName);
dboManager.command.Parameters.AddWithValue("@ownerId", user.uniqueId);
dboManager.command.Parameters.AddWithValue("@uploadDate", DateTime.Now);
dboManager.command.ExecuteNonQuery();
}
return(directoryToUpload + name + extension);
}
catch { return(null); }
}
protected void deleteLongTimePreregisteredUsers()
{
string taskName = "Mazání předregistrovaných uživatelů bez autorizace e-mailem";
try
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT * FROM [aspnet_UsersDetails] WHERE [userStatus] = 'PreRegistered'";
SqlDataReader reader = dboManager.command.ExecuteReader();
while (reader.Read())
{
if (Membership.GetUser(new Guid(reader["userId"].ToString())).CreationDate < DateTime.Now.AddDays(2))
{
Membership.DeleteUser(Membership.GetUser(new Guid(reader["userId"].ToString())).UserName);
dboManager.command2.CommandText = "DELETE [aspnet_UsersDetails] WHERE [userId] = @userId";
dboManager.command2.Parameters.Clear();
dboManager.command2.Parameters.AddWithValue("@userId", reader["userId"].ToString());
dboManager.command2.ExecuteNonQuery();
}
}
}
userManagementUL.InnerHtml += "<li><i class='icon-ok'></i> " + taskName + "</li>";
}
catch (Exception ex) { userManagementUL.InnerHtml += "<li><span class='alert-danger'><i class='icon-warning-sign'></i> " + taskName + "<ul style='padding-left: 20px;'><li>" + ex.Message + "</li></ul><span></li>"; }
}
public static List <string> groupNames(string groupIds)
{
string[] pomStr = groupIds.Split('-');
List <string> names = new List <string>();
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT * FROM [exampleGroups] WHERE ";
int i = 0;
foreach (string str in pomStr)
{
if (string.IsNullOrEmpty(str))
{
continue;
}
if (i > 0)
{
dboManager.command.CommandText += " OR ";
}
dboManager.command.CommandText += "[id] = " + str;
i = 1;
}
SqlDataReader reader = dboManager.command.ExecuteReader();
while (reader.Read())
{
names.Add(reader["name"].ToString());
}
return(names);
}
}
public void fillTreeView(string dboTableName, TreeView treeViewToFillId)
{
List <oneNodeObject> list = new List <oneNodeObject>();
using (dboManager dboManager = new dboManager())
{
SqlDataReader reader = dboManager.ExecuteQuery("SELECT * FROM [" + dboTableName + "] ORDER BY [position],[name]");
while (reader.Read())
{
bool pomHasCheckBox = false;
bool pomChecked = false;
try { if (reader["checked"] != null)
{
pomHasCheckBox = true; pomChecked = Convert.ToBoolean(reader["checked"].ToString());
}
}
catch { }
oneNodeObject pomNode = new oneNodeObject()
{
Id = Convert.ToInt64(reader["id"].ToString()), Name = reader["name"].ToString(), ParentId = Convert.ToInt64(reader["parentId"].ToString()), hasCheckBox = pomHasCheckBox
};
list.Add(pomNode);
}
}
treeViewToFillId.Nodes.Clear();
tvRecursive(list, null, treeViewToFillId);
}
public static void changeExamplePosition(Int64 collectionId, Int64 exampleId, double newPosition)
{
using (dboManager dboManager = new dboManager())
{
double oldPosition = Convert.ToDouble(dboManager.getScalar("examplesInCollections", "position", "exampleId = " + exampleId + " AND collectionId = " + collectionId));
if (oldPosition == newPosition)
{
return;
}
if (oldPosition > newPosition)
{
newPosition -= .5;
}
if (oldPosition < newPosition)
{
newPosition += .5;
}
dboManager.command.CommandText = "UPDATE [examplesInCollections] SET [position] = @newPosition WHERE [collectionId] = @collectionId AND [exampleId] = @exampleId";
dboManager.command.Parameters.AddWithValue("@newPosition", newPosition);
dboManager.command.Parameters.AddWithValue("@collectionId", collectionId);
dboManager.command.Parameters.AddWithValue("@exampleId", exampleId);
dboManager.command.ExecuteNonQuery();
recomputePositions(collectionId);
}
}
public static void addNewExample(string exampleName, string enter, string solution, string result, string groupIds, string schoolGradeIds, Int32 difficultyValue, string labels)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "INSERT INTO [examples] (name, enter, solution, result, groupIds, schoolGrade, difficulty, labels, ownerId, insertingDate, lastUpdate) VALUES (@name, @enter, @solution, @result, @groupIds, @schoolGrade, @difficulty, @labels, @ownerId, @insertingDate, @insertingDate)";
dboManager.command.Parameters.AddWithValue("@name", exampleName);
dboManager.command.Parameters.AddWithValue("@enter", enter);
dboManager.command.Parameters.AddWithValue("@solution", solution);
dboManager.command.Parameters.AddWithValue("@result", result);
dboManager.command.Parameters.AddWithValue("@groupIds", groupIds);
dboManager.command.Parameters.AddWithValue("@schoolGrade", schoolGradeIds);
if (schoolGradeIds.Split('-').Count() > 3)
{
dboManager.command.Parameters.AddWithValue("@difficulty", -1);
}
else
{
dboManager.command.Parameters.AddWithValue("@difficulty", difficultyValue);
}
dboManager.command.Parameters.AddWithValue("@labels", labels);
dboManager.command.Parameters.AddWithValue("@ownerId", user.uniqueId.ToString());
dboManager.command.Parameters.AddWithValue("@insertingDate", DateTime.Now);
dboManager.command.ExecuteNonQuery();
}
}
public static void updateExample(Int64 exampleId, string exampleName, string enter, string solution, string result, string groupIds, string schoolGradeIds, Int32 difficultyValue, string labels)
{
using (dboManager dboManager = new dboManager())
{
solution = solution.Trim();
if (string.IsNullOrEmpty(solution.Replace(" ", "").Replace("<br>", "").Replace("<br/>", "").Replace(" ", "").Replace("<p>", "").Replace("</p>", "").Replace("\n", "")))
{
solution = string.Empty; //test as solution is empty
}
dboManager.command.CommandText = "UPDATE [examples] SET [name] = @name, [enter] = @enter, [solution] = @solution, [result] = @result, [groupIds] = @groupIds, [schoolGrade] = @schoolGrade, [difficulty] = @difficulty, [labels] = @labels, [lastUpdate] = @lastupdate WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", exampleId);
dboManager.command.Parameters.AddWithValue("@name", exampleName);
dboManager.command.Parameters.AddWithValue("@enter", enter);
dboManager.command.Parameters.AddWithValue("@solution", solution);
dboManager.command.Parameters.AddWithValue("@result", result);
dboManager.command.Parameters.AddWithValue("@groupIds", groupIds);
dboManager.command.Parameters.AddWithValue("@schoolGrade", schoolGradeIds);
if (schoolGradeIds.Split('-').Count() > 3)
{
dboManager.command.Parameters.AddWithValue("@difficulty", -1);
}
else
{
dboManager.command.Parameters.AddWithValue("@difficulty", difficultyValue);
}
dboManager.command.Parameters.AddWithValue("@labels", labels);
//dboManager.command.Parameters.AddWithValue("@ownerId", user.uniqueId.ToString());
dboManager.command.Parameters.AddWithValue("@lastUpdate", DateTime.Now);
dboManager.command.ExecuteNonQuery();
}
}
public static string getStatus(Guid userUniqueId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT * FROM [aspnet_UsersDetails] WHERE [UserId] = @userId";
dboManager.command.Parameters.AddWithValue("@userId", userUniqueId.ToString());
SqlDataReader reader = dboManager.command.ExecuteReader();
if (!reader.Read())
{
return(null);
}
switch (reader["userStatus"].ToString())
{
case "Active": return("Aktivní");
case "Baned": if (string.IsNullOrEmpty(reader["banedTil"].ToString()))
{
return("Zakázán");
}
else
{
return(string.Format("Zakázán do {0:dd.MM.yyyy}", Convert.ToDateTime(reader["banedTil"].ToString())));
}
}
return(reader["userStatus"].ToString());
}
}
protected void Page_Load(object sender, EventArgs e)
{
header1.Visible = false;
Response.ContentType = "text/xml; charset=utf-8";
using (dboManager dboManager = new dboManager())
{
SqlDataReader reader = dboManager.ExecuteQuery("SELECT * FROM [examples] WHERE [status] = 'Active' ORDER BY [lastUpdate] DESC");
XmlDocument xmlDoc = new XmlDocument();
XmlNode declaration = xmlDoc.CreateNode(XmlNodeType.XmlDeclaration, null, null);
xmlDoc.AppendChild(declaration);
XmlNode urlset = xmlDoc.CreateNode(XmlNodeType.Element, "urlset", "http://www.sitemaps.org/schemas/sitemap/0.9");
while (reader.Read())
{
XmlNode url = xmlDoc.CreateNode(XmlNodeType.Element, "url", null);
XmlNode loc = xmlDoc.CreateNode(XmlNodeType.Element, "loc", null);
loc.InnerXml = "http://www.sbirkaprikladu.eu/p/" + reader["id"].ToString();
XmlNode lastmod = xmlDoc.CreateNode(XmlNodeType.Element, "lastmod", null);
DateTime lastUpdate = Convert.ToDateTime(reader["lastUpdate"].ToString());
lastmod.InnerXml = lastUpdate.ToString("yyyy-MM-dd");
url.AppendChild(loc);
url.AppendChild(lastmod);
urlset.AppendChild(url);
}
xmlDoc.AppendChild(urlset);
Response.Write(xmlDoc.OuterXml.Replace(" xmlns=\"\"", ""));
}
}
protected void fillControls()
{
if (Page.RouteData.Values["exampleId"] == null)
{
return;
}
ratingStarsUC.exampleId = exampleId;
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT * FROM [examples] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", exampleId);
SqlDataReader reader = dboManager.command.ExecuteReader();
if (!reader.Read())
{
return;
}
char[] pomChar = reader["name"].ToString().Trim().ToCharArray();
pomChar[0] = char.ToUpper(pomChar[0]); // first letter to uppercase
nameLB.InnerText = new string(pomChar);
pomChar = reader["name"].ToString().Trim().ToCharArray();
pomChar[0] = char.ToUpper(pomChar[0]); // first letter to uppercase
Header.Title = new string(pomChar);
exampleNrLB.Text = reader["id"].ToString();
List <string> groupsNames = exampleManager.groupNames(reader["groupIds"].ToString());
exampleGroupsTag.InnerHtml = "";
foreach (string name in groupsNames)
{
exampleGroupsTag.InnerHtml += name + "<br />";
}
schoolGradeLB.Text = exampleManager.schoolGradeToText(reader["schoolGrade"].ToString());
exampleAuthorLB.Text = exampleManager.authorFullName(exampleId);
difficulty = Convert.ToInt32(reader["difficulty"].ToString());
difficultyProgressBarUC.difficulty = difficulty;
enterPN.InnerHtml = reader["enter"].ToString();
solutionPN.InnerHtml = reader["solution"].ToString();
if (string.IsNullOrEmpty(solutionPN.InnerHtml))
{
solutionDiv.InnerHtml = "<div class='alert alert-info'><span type='button' class='close' data-dismiss='alert'>×</span><h3>Řešení příkladu není k dispozici</h3></div>";
}
resultPN.InnerHtml = reader["result"].ToString();
if (!user.isAnonymous && (reader["ownerId"].ToString() == user.uniqueId.ToString() || Roles.GetRolesForUser()[0] == "Moderator" || Roles.GetRolesForUser()[0] == "Administrator"))
{
editBtDiv.Visible = true;
}
else
{
editBtDiv.Visible = false;
}
}
}
public static int getChangedExamplesNrInCollection(Int64 collectionId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT COUNT(examplesInCollections.exampleId) AS nr FROM examplesInCollections INNER JOIN examples ON examplesInCollections.exampleId = examples.id AND examplesInCollections.compareDate < examples.lastUpdate WHERE (examplesInCollections.collectionId = @cId)";
dboManager.command.Parameters.AddWithValue("@cId", collectionId);
return(Convert.ToInt32(dboManager.command.ExecuteScalar().ToString()));
}
}
public static Int64 getInHowManyCollectionsIsExampleIncluded(Int64 exampleId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT COUNT(id) AS inCollectionsCount FROM [examplesInCollections] WHERE [exampleId] = @exId";
dboManager.command.Parameters.AddWithValue("@exId", exampleId);
return(Convert.ToInt64(dboManager.command.ExecuteScalar().ToString()));
}
}
public static Int64 getExamplePosition(Int64 exampleId, Int64 collectionId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [position] FROM [examplesInCollections] WHERE [exampleId] = @exId AND [collectionId] = @colId";
dboManager.command.Parameters.AddWithValue("@colId", collectionId);
dboManager.command.Parameters.AddWithValue("@exId", exampleId);
return(Convert.ToInt64(dboManager.command.ExecuteScalar().ToString()));
}
}
public static String getCollectionOwnerName(Int64 collectionId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [ownerId] FROM [collections] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", collectionId);
string pomId = dboManager.command.ExecuteScalar().ToString();
return(user.fullNameFromId(pomId));
}
}
public static bool getHideExaplesHeaders(Int64 collectionId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [hideExamplesHeaders] FROM [collections] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", collectionId);
var pom = Convert.ToBoolean(dboManager.command.ExecuteScalar().ToString());
return(pom);
}
}
public static string getLabels(Int64 collectionId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [labels] FROM [collections] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", collectionId);
string pom = dboManager.command.ExecuteScalar().ToString();
return(pom);
}
}
public static void changeLabels(Int64 collectionId, string labels)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [collections] SET [labels] = @labels WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@labels", labels.RemoveDiacritics());
dboManager.command.Parameters.AddWithValue("@id", collectionId);
dboManager.command.ExecuteNonQuery();
}
}
public static void renameCollection(Int64 collectionId, string newCollectionName)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [collections] SET [name] = @newName WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@newName", newCollectionName);
dboManager.command.Parameters.AddWithValue("@id", collectionId);
dboManager.command.ExecuteNonQuery();
}
}
public static void renameGroup(Int64 groupId, string newName)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [exampleGroups] SET [name] = @name WHERE [id] = @groupId";
dboManager.command.Parameters.AddWithValue("@name", newName);
dboManager.command.Parameters.AddWithValue("@groupId", groupId);
dboManager.command.ExecuteNonQuery();
}
}
public static Guid getExampleOwnerId(Int64 exampleId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [ownerId] FROM [examples] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", exampleId);
string pom = dboManager.command.ExecuteScalar().ToString();
return(new Guid(pom));
}
}
public static bool isCollectionPublic(Int64 collectionId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [isPublic] FROM [collections] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", collectionId);
string pomPublic = dboManager.command.ExecuteScalar().ToString();
return(Convert.ToBoolean(pomPublic));
}
}
public static DateTime getExampleLastUpdate(Int64 exampleId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT [lastUpdate] FROM [examples] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", exampleId);
string pom = dboManager.command.ExecuteScalar().ToString();
return(Convert.ToDateTime(pom));
}
}
public static void setBanTil(DateTime tilDateTime, Guid updatedUserUniqueId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [aspnet_UsersDetails] SET [userStatus] = 'Baned', [banedTil] = @banedTil WHERE [UserId] = @userId";
dboManager.command.Parameters.AddWithValue("@userId", updatedUserUniqueId.ToString());
dboManager.command.Parameters.AddWithValue("@banedTil", tilDateTime);
dboManager.command.ExecuteNonQuery();
}
}
public static void insertExampleGroup(string name, Int64 parentId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "INSERT INTO [exampleGroups] (name, parentId, position) VALUES (@name, @parentId, " + Int64.MaxValue + ")";
dboManager.command.Parameters.AddWithValue("@name", name);
dboManager.command.Parameters.AddWithValue("@parentId", parentId);
dboManager.command.ExecuteNonQuery();
}
recomputeGroupPositions(parentId);
}
public static void deleteExampleFromCollection(Int64 collectionId, Int64 exampleId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "DELETE [examplesInCollections] WHERE [collectionId] = @collectionId AND [exampleId] = @exampleId";
dboManager.command.Parameters.AddWithValue("@collectionId", collectionId);
dboManager.command.Parameters.AddWithValue("@exampleId", exampleId);
dboManager.command.ExecuteNonQuery();
recomputePositions(collectionId);
}
}
public static void setIsPublic(Int64 collectionId, bool isPublic)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [collections] SET [isPublic] = @isPublic, [lastUpdate] = @lastUpdate WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", collectionId);
dboManager.command.Parameters.AddWithValue("@lastUpdate", DateTime.Now);
dboManager.command.Parameters.AddWithValue("@isPublic", isPublic);
dboManager.command.ExecuteNonQuery();
}
}
public static void hideExamplesResults(Int64 collectionId, bool hidden)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [collections] SET [hideExamplesResults] = @hidden, [lastUpdate] = @lastUpdate WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", collectionId);
dboManager.command.Parameters.AddWithValue("@lastUpdate", DateTime.Now);
dboManager.command.Parameters.AddWithValue("@hidden", hidden);
dboManager.command.ExecuteNonQuery();
}
}
public static void setUserToActive(Guid userUniqueId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "UPDATE [aspnet_UsersDetails] SET [userStatus] = 'Active', [banedTil] = NULL WHERE [UserId] = @userId";
dboManager.command.Parameters.AddWithValue("@userId", userUniqueId.ToString());
dboManager.command.ExecuteNonQuery();
MembershipUser mu = Membership.GetUser(userUniqueId);
mu.IsApproved = true;
Membership.UpdateUser(mu);
}
}
public static string authorFullName(Int64 exampleId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT * FROM [examples] WHERE [id] = @id";
dboManager.command.Parameters.AddWithValue("@id", exampleId);
SqlDataReader reader = dboManager.command.ExecuteReader();
if (!reader.Read())
{
return(null);
}
return(user.fullNameFromId(reader["ownerId"].ToString()));
}
}
public static string fullNameFromId(string userId)
{
using (dboManager dboManager = new dboManager())
{
dboManager.command.CommandText = "SELECT * FROM [aspnet_UsersDetails] WHERE [UserId] = @userId";
dboManager.command.Parameters.AddWithValue("@userId", userId);
SqlDataReader reader = dboManager.command.ExecuteReader();
if (!reader.Read())
{
return(null);
}
return(reader["name"].ToString() + " " + reader["surName"].ToString());
}
}
