/// <summary>
/// The AllClubs
/// </summary>
/// <param name="sortOrder">The sortOrder<see cref="string"/></param>
/// <param name="currentFilter">The currentFilter<see cref="string"/></param>
/// <param name="searchString">The searchString<see cref="string"/></param>
/// <param name="page">The page<see cref="int?"/></param>
/// <returns>The <see cref="ActionResult"/></returns>
public ActionResult AllClubs(string sortOrder, string currentFilter, string searchString, int?page)
{
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
//Sets the title
ViewBag.NavTag = "Clubs";
// Required for stored proc, default is blank
string search = Request.QueryString["search"];
if (search == null)
{
search = "";
}
int pageSize = 6;
int pageNumber = (page ?? 1);
var clubs = SQLUtilities.GetAllClubs(search);
ViewBag.SearchTerm = search;
return(View(clubs.ToPagedList(pageNumber, pageSize)));
}
public void CreateGeographyInsertCommandTest()
{
DataColumn dc1 = new DataColumn("ID", typeof(int));
DataColumn dc2 = new DataColumn("GEOG", typeof(SqlGeography));
DataColumn dc3 = new DataColumn("Name", typeof(string));
List <DataColumn> list = new List <DataColumn>()
{
dc1, dc2, dc3
};
ICoordinate[] coords = new List <Coordinate>()
{
new Coordinate(90, -90), new Coordinate(-90, 90), new Coordinate(180, -90), new Coordinate(90, -90)
}.ToArray();
MemPolygonFeature feature = new MemPolygonFeature(new LinearRing(coords), null);
feature.Attributes.SetValue("ID", 123);
feature.Attributes.SetValue("Name", "Vish");
SqlCommand sqlCommand = SQLUtilities.CreateGeographyInsertCommand("GeographyTable", "SHAPE", list.GetEnumerator(), feature);
Assert.IsNotNull(sqlCommand, "Failed to generate INSERT geography command SQL.");
Assert.AreEqual(sqlCommand.Parameters.Count, 4, "INSERT SQL parameter count does not match");
dc1.AutoIncrement = true;
sqlCommand = SQLUtilities.CreateGeographyInsertCommand("GeographyTable", "SHAPE", list.GetEnumerator(), feature);
Assert.IsNotNull(sqlCommand, "Failed to generate INSERT geography command SQL.");
Assert.AreEqual(sqlCommand.Parameters.Count, 3, "INSERT SQL with auto increment column parameter count does not match");
}
/// <summary>
/// The AllNews
/// </summary>
/// <returns>The <see cref="ActionResult"/></returns>
public ActionResult AllNews(string sortOrder, string currentFilter, string searchString, int?page)
{
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.NavTag = "News";
string search = Request.QueryString["search"];
if (search == null)
{
search = "";
}
int pageSize = 9;
int pageNumber = (page ?? 1);
var news = SQLUtilities.GetAllNews(search);
//look below @AsianCarp let me know if this may help ?? not sure how you plan but yeah
//FindArticleLogo(news);
ViewBag.SearchTerm = search;
return(View(news.ToPagedList(pageNumber, pageSize)));
}
public void Add(IGISFeature feature)
{
IDbCommand dbCommand = GetDBCommand();
SqlCommand sqlCommand = SQLUtilities.CreateGeographyInsertCommand(_tableName, GetShapeFieldName(), _gisFields, feature);
sqlCommand.Connection = dbCommand.Connection as SqlConnection;
sqlCommand.ExecuteNonQuery();
}
public void Add(IGISFeature feature)
{
IDbCommand dbCommand = GetDBCommand();
SqlCommand sqlCommand = SQLUtilities.CreateGeometryInsertCommand(GetTableName(), GetShapeFieldName(), GetGISFields().GetEnumerator(), feature);
sqlCommand.Connection = dbCommand.Connection as SqlConnection;
sqlCommand.ExecuteNonQuery();
}
public void DeleteBuilding(int buildingId)
{
string script = ReadSQLScript("DeleteBuilding.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId)
};
SQLUtilities.ExecuteSqlCommand(_ClientProtalConnection, script, parameters);
}
public void DeleteBuildingFiles(List <Guid> fileIds)
{
string script = "update BuildingDocument set IsActive = 0 where Id = @Id";
foreach (Guid id in fileIds)
{
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@Id", id),
};
SQLUtilities.ExecuteSqlCommand(_ClientProtalConnection, script, parameters);
}
}
public byte[] SaveBuildingImage(int buildingId, byte[] image)
{
string script = ReadSQLScript("SaveBuildingImage.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId),
new System.Data.SqlClient.SqlParameter("@ImageData", image)
};
SQLUtilities.ExecuteSqlCommand(_ClientProtalConnection, script, parameters);
return(image);
}
public void CreateUserRecord(string emailAddress, string password, string accountNumber)
{
password = Cipher.Encrypt(password);
string script = ReadSQLScript("CreateUserRecord.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@EmailAddress", emailAddress),
new System.Data.SqlClient.SqlParameter("@PasswordHash", password),
new System.Data.SqlClient.SqlParameter("@AccountNumber", accountNumber),
};
SQLUtilities.ExecuteSqlCommand(_ClientProtalConnection, script, parameters);
}
public void UpdatePrimaryEmail(int buildingId, string accountNumber, string oldEmail, string newEmail)
{
if (!String.IsNullOrWhiteSpace(oldEmail) && !string.IsNullOrWhiteSpace(newEmail))
{
string script = ReadSQLScript("UpdatePrimaryEmail.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId),
new System.Data.SqlClient.SqlParameter("@AccountNumber", accountNumber),
new System.Data.SqlClient.SqlParameter("@OldEmail", oldEmail),
new System.Data.SqlClient.SqlParameter("@NewEmail", newEmail),
};
SQLUtilities.ExecuteSqlCommand(_ClientProtalConnection, script, parameters);
}
}
public string GetLoginPassword(string emailAddress)
{
string script = ReadSQLScript("GetLoginPassword.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@EmailAddress", emailAddress)
};
var ds = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 1)
{
string password = (string)ds.Tables[0].Rows[0]["PasswordHash"];
return(Cipher.Decrypt(password));
}
return(string.Empty); //no login found
}
public void SQLforGeometryTest()
{
IGeometry geometry = new Polygon(null, null, new GeometryFactory());
ICoordinate[] coords = new List <Coordinate>()
{
new Coordinate(90, -90), new Coordinate(-90, 90), new Coordinate(180, -90), new Coordinate(90, -90)
}.ToArray();
geometry = new Polygon(new LinearRing(coords));
string result = SQLUtilities.SQLforGeometry(geometry, null);
Assert.IsNotNull(result, "Error generating SQL for geometry.");
result = SQLUtilities.SQLforGeography(geometry, 4326);
Assert.IsNotNull(result, "Error generating SQL for geometry.");
}
public byte[] GetUnitFile(Guid unitDocumentId)
{
string script = ReadSQLScript("GetUnitFile.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@UnitDocumentId", unitDocumentId),
};
var ds = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 1)
{
var val = ds.Tables[0].Rows[0]["DocumentData"];
return(ReadBinaryValue(val));
}
return(null);
}
public string UploadUnitDocument(DocumentCategoryType documentType, DateTime fileDate, int buildingId,
string accountNumber, string filename, string title, byte[] data, string emailAddress)
{
if (string.IsNullOrWhiteSpace(title))
{
title = "Correspondence ";
}
filename = Path.GetFileName(filename);
filename = filename.Replace(" ", "");
//return a URL to the uploaded document
Guid documentId = System.Guid.NewGuid();
if (title.Length > MAX_TITLE_LENGTH)
{
title = title.Substring(0, MAX_TITLE_LENGTH);
}
if (filename.Length > MAX_FILENAME_LENGTH)
{
filename = Path.GetFileNameWithoutExtension(filename).Substring(0, MAX_FILENAME_LENGTH - 4) + "." + Path.GetExtension(filename);
}
string script = ReadSQLScript("UploadUnitDocument.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@DocumentType", (int)documentType),
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId),
new System.Data.SqlClient.SqlParameter("@AccountNumber", accountNumber),
new System.Data.SqlClient.SqlParameter("@Filename", filename),
new System.Data.SqlClient.SqlParameter("@Description", title),
new System.Data.SqlClient.SqlParameter("@FileDate", fileDate),
new System.Data.SqlClient.SqlParameter("@DocumentId", documentId),
new System.Data.SqlClient.SqlParameter("@Data", data),
};
var result = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
documentId = (Guid)result.Tables[0].Rows[0]["Id"];
return(GetUnitDocumentLink(documentId, emailAddress));
}
/// <summary>
/// The CreateHomePage
/// </summary>
/// <returns>The <see cref="HomePageViewModel"/></returns>
public static HomePageViewModel CreateHomePage()
{
HomePageViewModel HPModel = new HomePageViewModel();
List <Club> clubs = SQLUtilities.GetAllClubs(String.Empty).ToList();
HPModel.Clubs = new ObservableCollection <Club>(clubs.Take(4));
List <News> news = SQLUtilities.GetAllNews(String.Empty).ToList();
HPModel.News = new ObservableCollection <News>(news.Take(5));
List <Event> events = SQLUtilities.GetAllEvents(String.Empty).ToList();
HPModel.UpcomingEvents = new ObservableCollection <Event>(events.Take(4));
return(HPModel);
}
public List <WebDocumentAccessLogItem> GetUnitFileAccessHistory(Guid unitDocumentId)
{
string script = ReadSQLScript("GetUnitFileAccesHistory.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@UnitDocumentId", unitDocumentId),
};
var ds = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
List <WebDocumentAccessLogItem> result = new List <WebDocumentAccessLogItem>();
foreach (DataRow row in ds.Tables[0].Rows)
{
result.Add(new WebDocumentAccessLogItem(row));
}
return(result);
}
/// <summary>
/// Gets Creates the view for approving or denying all the pending events
/// </summary>
/// <param name="currentFilter">The currentFilter<see cref="string"/></param>
/// <param name="searchString">The searchString<see cref="string"/></param>
/// <param name="page">The page<see cref="int?"/></param>
/// <returns></returns>
public ActionResult PendingEvents(string currentFilter, string searchString, int?page)
{
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.NavTag = "Pending Events";
int pageSize = 5;
int pageNumber = (page ?? 1);
var events = SQLUtilities.GetPendingEvents();
return(View(events.ToPagedList(pageNumber, pageSize)));
}
private void ConnectToDatabase()
{
SQLUtilities scoQuery = new SQLUtilities();
//scoQuery.ConnectionString = string.Format("Server = {0}; Database = {1}; User ID = {2}; Password = {3}; Trusted_Connection = False;", _dbServer, _dbName, _dbUserID, _dbPassword);
scoQuery.ConnectionString = string.Format("Data Source = {0}; Initial Catalog = {1}; Integrated Security=SSPI", _dbServer, _dbName);
_myConnection = scoQuery.OpenConnection();
buttonGetDetails.Enabled = false;
SelectionWindowText = string.Empty;
if (!String.IsNullOrEmpty(IPUtilities.ExtensionsPath))
{
dataGridView1.Rows.Clear();
_ipList.Clear();
textBoxConnectedTo.Text = string.Format("Connected to Management Server: [{0}] / SQL Server: [{1}]", _managementServerName, _dbServer);
EnableDependentControls();
LoadGridView();
}
}
public List <FileDetail> BuildingDocumentList(int buildingId, bool activeOnly = true)
{
string script = ReadSQLScript("BuildingDocumentList.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId),
new System.Data.SqlClient.SqlParameter("@ActiveOnly", activeOnly)
};
var data = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
List <FileDetail> result = new List <FileDetail>();
foreach (DataRow row in data.Tables[0].Rows)
{
result.Add(new FileDetail(row));
}
return(result);
}
public List <string> GetLinkedUnits(int buildingId, string emailAddress)
{
List <string> accountNumbers = new List <string>();
string script = ReadSQLScript("GetLinkedUnits.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@EmailAddress", emailAddress),
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId)
};
var ds = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
accountNumbers.Add((string)row["AccountNumber"]);
}
}
return(accountNumbers);
}
public byte[] GetBuildingImage(int buildingId)
{
byte[] result = null;
string script = ReadSQLScript("GetBuildingImage.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId)
};
var ds = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count == 1)
{
var val = ds.Tables[0].Rows[0]["BuildingImage"];
result = ReadBinaryValue(val);
}
if (result == null)
{
result = ReadDefaultImage();
}
return(result);
}
public string UploadBuildingDocument(DocumentCategoryType documentType, DateTime fileDate,
int buildingId, string description, string filename, byte[] fileData, string emailAddress)
{
filename = Path.GetFileName(filename);
//return a URL to the uploaded document
if (description.Length > MAX_TITLE_LENGTH)
{
description = description.Substring(0, MAX_TITLE_LENGTH);
}
if (filename.Length > MAX_FILENAME_LENGTH)
{
filename = Path.GetFileNameWithoutExtension(filename).Substring(0, MAX_FILENAME_LENGTH - 4) + "." + Path.GetExtension(filename);
}
Guid documentId = System.Guid.NewGuid();
string script = ReadSQLScript("UploadBuildingDocument.sql");
var parameters = new List <System.Data.SqlClient.SqlParameter>()
{
new System.Data.SqlClient.SqlParameter("@DocumentType", (int)documentType),
new System.Data.SqlClient.SqlParameter("@BuildingId", buildingId),
new System.Data.SqlClient.SqlParameter("@Filename", filename),
new System.Data.SqlClient.SqlParameter("@Description", description),
new System.Data.SqlClient.SqlParameter("@FileDate", fileDate),
new System.Data.SqlClient.SqlParameter("@DocumentId", documentId),
new System.Data.SqlClient.SqlParameter("@Data", fileData),
};
var result = SQLUtilities.FetchData(_ClientProtalConnection, script, parameters);
documentId = (Guid)result.Tables[0].Rows[0]["Id"];
return(GetBuildingDocumentLink(documentId, emailAddress));
}
/// <summary>
/// Gets Creates the view for approving or denying all the pending news
/// </summary>
/// <returns></returns>
public ActionResult PendingNews(string currentFilter, string searchString, int?page)
{
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
//filtering in the future...
//ViewBag.CurrentFilter = searchString;
ViewBag.NavTag = "Pending News";
int pageSize = 5;
int pageNumber = (page ?? 1);
var news = SQLUtilities.GetPendingNews();
return(View(news.ToPagedList(pageNumber, pageSize)));
}
/// <summary>
/// Populates the selection lists for the events controller
/// </summary>
private void PopulateSelectionLists()
{
//populates the host dropdown with clubs drom db
List <SelectListItem> clubs = SQLUtilities.GetAllHosts();
ViewData["ClubHosts"] = clubs;
//Gets all the categories and creates selection list
List <SelectListItem> cats = SQLUtilities.GetCategories();
ViewData["cats"] = cats;
List <SelectListItem> camp = new List <SelectListItem>();
camp.Add(new SelectListItem()
{
Value = "1", Text = "Stevens Point"
});
camp.Add(new SelectListItem()
{
Value = "2", Text = "Wisconsin Rapids"
});
camp.Add(new SelectListItem()
{
Value = "3", Text = "Marshfield"
});
camp.Add(new SelectListItem()
{
Value = "4", Text = "Adams"
});
camp.Add(new SelectListItem()
{
Value = "5", Text = "Off Campus"
});
ViewData["campuses"] = camp;
}
public static string GetDataFromServer()
{
int counter = 0;
DataSet ds = new DataSet();
List <Event> events = SQLUtilities.GetAllEvents();
ds = SQLUtilities.ToDataSet(events);
// After pulling the events its builds it out into a table
string resp = string.Empty;
for (int i = 1; i <= ds.Tables[0].Rows.Count; i++)
{
string strComment = string.Empty;
if (ds.Tables != null)
{
if (ds.Tables[0] != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows.Count >= i - 1)
{
if (ds.Tables[0].Rows[i - 1][0] != DBNull.Value)
{
strComment = ds.Tables[0].Rows[i - 1][0].ToString();
}
}
}
}
}
}
return(resp);
}
/// <summary>
/// Populates the posted by selection list
/// </summary>
private void PopulateSelectionList()
{
List <SelectListItem> clubs = SQLUtilities.GetAllHosts();
ViewData["ClubHosts"] = clubs;
}
/// <summary>
/// The AllEvents
/// </summary>
/// <param name="sortOrder">The sortOrder<see cref="string"/></param>
/// <param name="currentFilter">The currentFilter<see cref="string"/></param>
/// <param name="searchString">The searchString<see cref="string"/></param>
/// <param name="page">The page<see cref="int?"/></param>
/// <returns>The <see cref="ActionResult"/></returns>
public ActionResult AllEvents(string sortOrder, string currentFilter, string searchString, int?page)
{
ViewBag.NavTag = "Events";
// Gets the categories for the filters
MintContext db = new MintContext();
ViewData["Categories"] = SQLUtilities.GetCategories();
// Searching
string search = null;
if (Request.QueryString["search"] != null)
{
search = Request.QueryString["search"];
}
// Categories
int?Category = null;
if (Request.QueryString["category"] == "0")
{
Category = null;
}
else if (Request.QueryString["category"] != null)
{
Category = Convert.ToInt32(Request.QueryString["category"]);
}
//Free Stuff/Food
bool?FreeFood = null;
bool?FreeStuff = null;
if (Request.QueryString["freefood"] != null)
{
FreeFood = true;
}
if (Request.QueryString["freestuff"] != null)
{
FreeStuff = true;
}
// Date
string Date = null;
if (Request.QueryString["Date"] != null)
{
Date = Request.QueryString["Date"].ToString();
}
bool?Weekend = null;
if (Request.QueryString["Weekend"] != null)
{
Weekend = true;
}
// Paging info
if (searchString != null)
{
page = 1;
}
else
{
searchString = currentFilter;
}
int pageSize = 9;
int pageNumber = (page ?? 1);
// Gets all the events for the page
var events = SQLUtilities.GetAllEvents(search, Category, FreeFood, FreeStuff, Date, Weekend);
//Sets the viewbag info
ViewBag.SearchTerm = search;
ViewBag.Category = Category;
ViewBag.Date = Date;
ViewBag.FreeStuff = FreeStuff == null ? "" : "checked";
ViewBag.FreeFood = FreeFood == null ? "" : "checked";
ViewBag.Weekend = Weekend == null ? "" : "checked";
// Returns the view
return(View(events.ToPagedList(pageNumber, pageSize)));
}
private string ReadSQLScript(string scriptName)
{
string resourcePath = "Astrodon.ClientPortal.Scripts." + scriptName;
return(SQLUtilities.ReadResourceScript(this.GetType().Assembly, resourcePath));
}
/*Update all building records and client records*/
public void SyncBuildings()
{
string script = ReadSQLScript("SyncBuildingsAndUnits.sql");
SQLUtilities.ExecuteSqlCommand(_ClientProtalConnection, script);
}