public DataSet DataOutputMethod(string Query)
{
SQLConnection ConnectToDataBase;
string SQLConnect = "";
DataSet IncidentDataSet = new DataSet();
ConnectToDataBase = new SQLConnection();
ConnectToDataBase.connection_string = SQLConnect;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = Query;
IncidentDataSet = ConnectToDataBase.GetConnection;
//use adapter to populate dataset...
SqlDataAdapter DataAdapter = new SqlDataAdapter(Query, SQLConnect); //sql statement and connections string fed into the SqlConnection class to bring and connect to data
DataAdapter.Fill(IncidentDataSet);
return IncidentDataSet;
}
/// <summary>
/// NarrativeLog Method: using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
/// </summary>
/// <param name="incidentnumber">Requires you to pass the incident number to bring back relevant data (String)</param>
/// /// <param name="FilterIndex">Requires you to pass either 0 or depending on when the incident was (pre or post NWFC (int)</param>
public DataSet NarrativeLog(string incidentnumber, int FilterIndex)
{
//using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
var Query = new SelectQueryBuilder();
string strSQLconnection;
SQLConnection ConnectToDataBase;
Query.SelectColumns("nar_act_time as 'Time'", "nar_log_text as 'Narrative Text'"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "nar_inc_id", Comparison.Equals, "incidentdim", "id_in_ref");
string IncidentDate = IncidentData.Tables[0].Rows[0]["Date Created"].ToString();
//string Date = IncidentDate.Substring(0, 10);
DateTime Incidentdt = Convert.ToDateTime(IncidentDate);
const string FireControlDate = "29/05/2014"; //date that we went to new control, used for narrative log formating
DateTime Controldt = Convert.ToDateTime(FireControlDate);
// int results = DateTime.Compare(dt, Fdt);
if (Incidentdt.Date < Controldt.Date && FilterIndex == 1)
{
Query.AddWhere("nar_log_entry", Comparison.Equals, "TEXT");
}
else
{
}
if (Incidentdt.Date > Controldt.Date && FilterIndex == 1)
{
WhereClause clause =
Query.AddWhere("nar_log_text", Comparison.Like, "%Message%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%INFORMATIVE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%RADIO%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%ASSISTANCE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%FROM%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%OTHER%");
}
else
{
}
Query.AddWhere("id_in_ref", Comparison.Equals, incidentnumber);
Query.AddOrderBy("nar_act_time", Sorting.Ascending);
var statement = Query.BuildQuery();
if (statement == null)
{
throw new ArgumentNullException("incidentnumber");
}
//connect to the database
ConnectToDataBase = new SQLConnection();
strSQLconnection = SQLConnect;
ConnectToDataBase.connection_string = strSQLconnection;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = statement;
DataSet narDataset;
using (narDataset = ConnectToDataBase.GetConnection)
{
if (narDataset == null)
{
throw new ArgumentNullException("incidentnumber");
}
return(narDataset);
}
}
/// <summary>
/// 获取所有快速启动项
/// </summary>
/// <returns></returns>
public async Task <List <KeyValuePair <QuickStartType, QuickStartItem> > > GetQuickStartItemAsync()
{
using (SQLConnection Connection = await ConnectionPool.GetConnectionFromDataBasePoolAsync().ConfigureAwait(true))
{
List <Tuple <string, string, string> > ErrorList = new List <Tuple <string, string, string> >();
List <KeyValuePair <QuickStartType, QuickStartItem> > Result = new List <KeyValuePair <QuickStartType, QuickStartItem> >();
using (SqliteCommand Command = Connection.CreateDbCommandFromConnection <SqliteCommand>("Select * From QuickStart"))
using (SqliteDataReader Reader = await Command.ExecuteReaderAsync().ConfigureAwait(true))
{
while (Reader.Read())
{
try
{
if (Convert.ToString(Reader[1]).StartsWith("ms-appx"))
{
StorageFile BitmapFile = await StorageFile.GetFileFromApplicationUriAsync(new Uri(Reader[1].ToString()));
BitmapImage Bitmap = new BitmapImage();
using (IRandomAccessStream Stream = await BitmapFile.OpenAsync(FileAccessMode.Read))
{
await Bitmap.SetSourceAsync(Stream);
}
if ((QuickStartType)Enum.Parse(typeof(QuickStartType), Reader[3].ToString()) == QuickStartType.Application)
{
Result.Add(new KeyValuePair <QuickStartType, QuickStartItem>(QuickStartType.Application, new QuickStartItem(Bitmap, Convert.ToString(Reader[2]), QuickStartType.Application, Reader[1].ToString(), Reader[0].ToString())));
}
else
{
Result.Add(new KeyValuePair <QuickStartType, QuickStartItem>(QuickStartType.WebSite, new QuickStartItem(Bitmap, Convert.ToString(Reader[2]), QuickStartType.WebSite, Reader[1].ToString(), Reader[0].ToString())));
}
}
else
{
StorageFile ImageFile = await StorageFile.GetFileFromPathAsync(Path.Combine(ApplicationData.Current.LocalFolder.Path, Convert.ToString(Reader[1])));
using (IRandomAccessStream Stream = await ImageFile.OpenAsync(FileAccessMode.Read))
{
BitmapImage Bitmap = new BitmapImage();
await Bitmap.SetSourceAsync(Stream);
if ((QuickStartType)Enum.Parse(typeof(QuickStartType), Reader[3].ToString()) == QuickStartType.Application)
{
Result.Add(new KeyValuePair <QuickStartType, QuickStartItem>(QuickStartType.Application, new QuickStartItem(Bitmap, Convert.ToString(Reader[2]), QuickStartType.Application, Reader[1].ToString(), Reader[0].ToString())));
}
else
{
Result.Add(new KeyValuePair <QuickStartType, QuickStartItem>(QuickStartType.WebSite, new QuickStartItem(Bitmap, Convert.ToString(Reader[2]), QuickStartType.WebSite, Reader[1].ToString(), Reader[0].ToString())));
}
}
}
}
catch (Exception)
{
ErrorList.Add(new Tuple <string, string, string>(Convert.ToString(Reader[0]), Convert.ToString(Reader[1]), Convert.ToString(Reader[3])));
}
}
}
foreach (var ErrorItem in ErrorList)
{
using (SqliteCommand Command = Connection.CreateDbCommandFromConnection <SqliteCommand>("Delete From QuickStart Where Name = @Name And FullPath = @FullPath And Type=@Type"))
{
_ = Command.Parameters.AddWithValue("@Name", ErrorItem.Item1);
_ = Command.Parameters.AddWithValue("@FullPath", ErrorItem.Item2);
_ = Command.Parameters.AddWithValue("@Type", ErrorItem.Item3);
_ = await Command.ExecuteNonQueryAsync().ConfigureAwait(true);
}
}
return(Result);
}
}
public IActionResult getMode()
{
SerialCon serialCon = SQLConnection.GetLightMode();
return(Json(serialCon));
}
public static GameData GetAllGameData(int id)
{
//Copied ad pasted from other script.
//Maybe make the manager able to perform all stored procedures?
//Should there be a seperate output screen for the results?
GameData gameData = new GameData();
if (id < 0 || id > 55793)
{
gameData.rank = -1;
return(gameData);
}
//allGameDataByRank(gameRank)
try
{
MySqlCommand gameCall = new MySqlCommand();
gameCall.Connection = SQLConnection.connection;
gameCall.CommandType = CommandType.StoredProcedure;
gameCall.CommandText = "allGameDataByRank";
gameCall.Parameters.AddWithValue("@gameRank", id);
gameCall.Parameters["@gameRank"].Direction = ParameterDirection.Input;
Debug.Log("Getting game data of rank " + id);
// Loading Game Data:
MySqlDataReader reader = gameCall.ExecuteReader();
while (reader.Read()) // this should only run once because of only having one row
{
try
{
// I don't think a loop is needed
if (!int.TryParse(reader[0].ToString(), out gameData.rank))
{
gameData.rank = -1;
}
gameData.name = reader[1].ToString();
gameData.platform = reader[2].ToString();
gameData.genre = reader[3].ToString();
gameData.esrb = reader[4].ToString();
if (!int.TryParse(reader[5].ToString(), out gameData.year))
{
gameData.year = -1;
}
gameData.url = reader[6].ToString();
gameData.urlImg = reader[7].ToString();
if (!int.TryParse(reader[8].ToString(), out gameData.devID))
{
gameData.devID = -1;
}
gameData.pub = reader[9].ToString();
if (!float.TryParse(reader[10].ToString(), out gameData.ratingCritic))
{
gameData.ratingCritic = -1;
}
if (!float.TryParse(reader[11].ToString(), out gameData.ratingUser))
{
gameData.ratingUser = -1;
}
if (!float.TryParse(reader[13].ToString(), out gameData.salesGlobal))
{
gameData.salesGlobal = -1;
}
if (gameData.salesGlobal > 0)
{
// spread data exists, load it
if (!float.TryParse(reader[14].ToString(), out gameData.salesNA))
{
gameData.salesNA = -1;
}
if (!float.TryParse(reader[15].ToString(), out gameData.salesPAL))
{
gameData.salesPAL = -1;
}
if (!float.TryParse(reader[16].ToString(), out gameData.salesJP))
{
gameData.salesJP = -1;
}
if (!float.TryParse(reader[17].ToString(), out gameData.salesOther))
{
gameData.salesOther = -1;
}
}
else
{
// only total shipped exists, put that in for global
if (!float.TryParse(reader[12].ToString(), out gameData.salesGlobal))
{
gameData.salesGlobal = -1;
}
gameData.salesNA = gameData.salesPAL = gameData.salesJP = gameData.salesOther = -1;
}
}
catch (MySqlException ex)
{
Debug.LogWarning(ex.ToString());
}
}
reader.Close();
// check for dev
if (gameData.devID != -1)
{
// do another query getting the dev name
gameData.dev = SQLConnection.GetAllDevData(gameData.devID).name;
}
else
{
gameData.dev = "---";
}
}
catch (MySqlException ex)
{
Debug.LogWarning(ex.ToString());
}
Debug.Log("Finished single game query.");
return(gameData);
}
private void ReadCsvFile(string path)
{
//String[] csv = File.ReadAllLines(path);
//foreach (string csvrow in csv)
//{
// var fields = csvrow.Split(','); // csv delimiter
// InsertDatabase(fields[0], fields[1], fields[2], fields[3]);
//}
TextFieldParser parser = new TextFieldParser(path);
parser.Delimiters = new string[] { "," };
while (true)
{
string[] fields = parser.ReadFields();
if (fields == null)
{
break;
}
if (InsertDatabase(fields[0], fields[1], fields[2], fields[3]))
{
this.counter++;
continue;
}
else if (Utils.InputValidation.checkStringLength(20, fields[0]))
{
MessageBox.Show("Sorry the name: " + fields[0] + " is to long!", "Format Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
else if (fields[3] != "Museum" || fields[3] != "museum")
{
MessageBox.Show("Sorry failed to add the site: " + fields[0] + " due to incurred site Type: " + fields[3], "Format Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
else if (Utils.InputValidation.isStringPositiveNumeric(fields[2]))
{
MessageBox.Show("Sorry failed to add the site: " + fields[0] + " due to incurred Founded Year: " + fields[2], "Format Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
else if (Utils.InputValidation.checkStringLength(150, fields[1]))
{
MessageBox.Show("Sorry failed to add the site: " + fields[0] + " the Description is to long!", "Format Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
else if (SQLConnection.GetDataContextInstance().tblSites.Where(u => u.name == fields[0] && u.siteDescription == fields[1] && u.foundedYear == int.Parse(fields[2]) && u.siteType == fields[3]).Any())
{
Utils.Alerts.errorMessage("The record: " + fields[0] + fields[1] + fields[2] + fields[3] + " already exists in system!");
continue;
}
else
{
MessageBox.Show("Sorry failed to add the site: " + fields[0] + " due to fismatch data format", "Format Mismatch", MessageBoxButtons.OK, MessageBoxIcon.Error);
continue;
}
}
if (counter != 0) // check if at least one record from CSV file is inserted to the table
{
Utils.Alerts.dataSavedSuccessfully();
this.filePath = "";
txtPath.Text = "";
this.counter = 0;
}
else
{
Utils.Alerts.errorMessage("data from CSV file is already exists in the system!!");
}
Utils.GridViewRefresh.RefreshTable(this.bs, dataGridView, "tblSite");
}
public List <LadderDTO> GetAllLadders()
{
var result = SQLConnection.ExecuteSearchQuery("SELECT * FROM Ladder");
return(GenerateDTOsFromRows(result));
}
//Load feeds in selection
async Task LoadSelectionFeeds(List <TableFeeds> LoadTableFeeds, List <TableItems> LoadTableItems, bool Silent, bool EnableUI)
{
try
{
if (!Silent)
{
await ProgressDisableUI("Loading selection feeds...", true);
}
Debug.WriteLine("Loading selection feeds, silent: " + Silent);
combobox_FeedSelection.IsHitTestVisible = false;
combobox_FeedSelection.Opacity = 0.30;
await ClearObservableCollection(List_FeedSelect);
//Wait for busy database
await ApiUpdate.WaitForBusyDatabase();
//Check if received lists are empty
if (LoadTableFeeds == null)
{
LoadTableFeeds = await SQLConnection.Table <TableFeeds>().OrderBy(x => x.feed_folder).ToListAsync();
}
if (LoadTableItems == null)
{
LoadTableItems = await SQLConnection.Table <TableItems>().ToListAsync();
}
//Filter un/ignored feeds
List <String> IgnoredFeedList = LoadTableFeeds.Where(x => x.feed_ignore_status == true).Select(x => x.feed_id).ToList();
List <TableFeeds> UnignoredFeedList = LoadTableFeeds.Where(x => x.feed_ignore_status == false).ToList();
if (!(bool)AppVariables.ApplicationSettings["DisplayReadMarkedItems"])
{
//Add unread feeds selection
Feeds TempFeed = new Feeds();
TempFeed.feed_id = "2";
Int32 TotalItemsUnread = ProcessItemLoad.FilterNewsItems(IgnoredFeedList, LoadTableItems, TempFeed, 0, AppVariables.ItemsMaximumLoad).Count();
Feeds FeedItemUnread = new Feeds();
FeedItemUnread.feed_icon = await AVImage.LoadBitmapImage("ms-appx:///Assets/iconRSS-Dark.png", false);
FeedItemUnread.feed_title = "All unread items";
FeedItemUnread.feed_item_count = TotalItemsUnread;
FeedItemUnread.feed_collection_status = true;
FeedItemUnread.feed_id = "2";
List_FeedSelect.Add(FeedItemUnread);
//Add read feeds selection
TempFeed.feed_id = "1";
Int32 TotalItemsRead = ProcessItemLoad.FilterNewsItems(IgnoredFeedList, LoadTableItems, TempFeed, 0, AppVariables.ItemsMaximumLoad).Count();
Feeds FeedItemRead = new Feeds();
FeedItemRead.feed_icon = await AVImage.LoadBitmapImage("ms-appx:///Assets/iconRSS-Dark.png", false);
FeedItemRead.feed_title = "Already read items";
FeedItemRead.feed_item_count = TotalItemsRead;
FeedItemRead.feed_collection_status = true;
FeedItemRead.feed_id = "1";
List_FeedSelect.Add(FeedItemRead);
}
else
{
//Add all feeds selection
Feeds TempFeed = new Feeds();
TempFeed.feed_id = "0";
Int32 TotalItemsAll = ProcessItemLoad.FilterNewsItems(IgnoredFeedList, LoadTableItems, TempFeed, 0, AppVariables.ItemsMaximumLoad).Count();
Feeds FeedItemAll = new Feeds();
FeedItemAll.feed_icon = await AVImage.LoadBitmapImage("ms-appx:///Assets/iconRSS-Dark.png", false);
FeedItemAll.feed_title = "All feed items";
FeedItemAll.feed_item_count = TotalItemsAll;
FeedItemAll.feed_collection_status = true;
FeedItemAll.feed_id = "0";
List_FeedSelect.Add(FeedItemAll);
}
//Feeds that are not ignored and contain items
foreach (TableFeeds Feed in UnignoredFeedList)
{
Feeds TempFeed = new Feeds();
TempFeed.feed_id = Feed.feed_id;
Int32 TotalItems = ProcessItemLoad.FilterNewsItems(IgnoredFeedList, LoadTableItems, TempFeed, 0, AppVariables.ItemsMaximumLoad).Count();
if (TotalItems > 0)
{
//Add folder
string FeedFolder = Feed.feed_folder;
if (String.IsNullOrWhiteSpace(FeedFolder))
{
FeedFolder = "No folder";
}
Feeds FolderUpdate = List_FeedSelect.Where(x => x.feed_folder_title == FeedFolder && x.feed_folder_status).FirstOrDefault();
if (FolderUpdate == null)
{
//Load folder icon
BitmapImage FolderIcon = await AVImage.LoadBitmapImage("ms-appx:///Assets/iconFolder-Dark.png", false);
//Add folder
Feeds FolderItem = new Feeds();
FolderItem.feed_icon = FolderIcon;
FolderItem.feed_folder_title = FeedFolder;
FolderItem.feed_folder_status = true;
List_FeedSelect.Add(FolderItem);
//Debug.WriteLine("Added folder...");
}
//Add feed
//Load feed icon
BitmapImage FeedIcon = null;
if (Feed.feed_id.StartsWith("user/"))
{
FeedIcon = await AVImage.LoadBitmapImage("ms-appx:///Assets/iconUser-Dark.png", false);
}
else
{
FeedIcon = await AVImage.LoadBitmapImage("ms-appdata:///local/" + Feed.feed_id + ".png", false);
}
if (FeedIcon == null)
{
FeedIcon = await AVImage.LoadBitmapImage("ms-appx:///Assets/iconRSS-Dark.png", false);
}
//Get the current feed item count
Feeds FeedItem = new Feeds();
FeedItem.feed_icon = FeedIcon;
FeedItem.feed_title = Feed.feed_title;
FeedItem.feed_item_count = TotalItems;
FeedItem.feed_id = Feed.feed_id;
List_FeedSelect.Add(FeedItem);
//Update folder
FolderUpdate = List_FeedSelect.Where(x => x.feed_folder_title == FeedFolder && x.feed_folder_status).FirstOrDefault();
if (FolderUpdate != null)
{
FolderUpdate.feed_folder_ids.Add(Feed.feed_id);
FolderUpdate.feed_item_count = FolderUpdate.feed_item_count + FeedItem.feed_item_count;
//Debug.WriteLine("Updated folder...");
}
}
}
combobox_FeedSelection.IsHitTestVisible = true;
combobox_FeedSelection.Opacity = 1;
}
catch { }
if (EnableUI)
{
await ProgressEnableUI();
}
}
//重写构造函数，包含注入的配置信息
public SampleDataController(IOptions <SQLConnection> conection)
{
LinkSQL = conection.Value;
}
//Sync offline changes
public static async Task SyncOfflineChanges(bool Silent, bool EnableUI)
{
try
{
if (!Silent)
{
await EventProgressDisableUI("Syncing offline changes...", true);
}
Debug.WriteLine("Syncing offline changes...");
//Wait for busy database
await ApiUpdate.WaitForBusyDatabase();
//Get current offline sync items
List <TableOffline> OfflineSyncItemList = await SQLConnection.Table <TableOffline>().ToListAsync();
//Sync read items
List <String> ReadStringList = OfflineSyncItemList.Where(x => !String.IsNullOrWhiteSpace(x.item_read_status)).Select(x => x.item_read_status).ToList();
if (ReadStringList.Any())
{
bool Result = await MarkItemReadStringList(ReadStringList, true);
//Remove from list when succeeded
if (Result)
{
Int32 DeletedItems = await SQLConnection.ExecuteAsync("DELETE FROM TableOffline WHERE item_read_status");
Debug.WriteLine("Removed " + DeletedItems + " Read offline synced items...");
}
else
{
Debug.WriteLine("Failed to sync offline read items.");
}
}
//Sync unread items
List <String> UnreadStringList = OfflineSyncItemList.Where(x => !String.IsNullOrWhiteSpace(x.item_unread_status)).Select(x => x.item_unread_status).ToList();
if (UnreadStringList.Any())
{
bool Result = await MarkItemReadStringList(UnreadStringList, false);
//Remove from list when succeeded
if (Result)
{
Int32 DeletedItems = await SQLConnection.ExecuteAsync("DELETE FROM TableOffline WHERE item_unread_status");
Debug.WriteLine("Removed " + DeletedItems + " Unread offline synced items...");
}
else
{
Debug.WriteLine("Failed to sync offline unread items.");
}
}
//Sync starred items
List <String> StarStringList = OfflineSyncItemList.Where(x => !String.IsNullOrWhiteSpace(x.item_star_status)).Select(x => x.item_star_status).ToList();
if (StarStringList.Any())
{
bool Result = await MarkItemStarStringList(StarStringList, true);
//Remove from list when succeeded
if (Result)
{
Int32 DeletedItems = await SQLConnection.ExecuteAsync("DELETE FROM TableOffline WHERE item_star_status");
Debug.WriteLine("Removed " + DeletedItems + " Star offline synced items...");
}
else
{
Debug.WriteLine("Failed to sync offline star items.");
}
}
//Sync Unstarred items
List <String> UnstarStringList = OfflineSyncItemList.Where(x => !String.IsNullOrWhiteSpace(x.item_unstar_status)).Select(x => x.item_unstar_status).ToList();
if (UnstarStringList.Any())
{
bool Result = await MarkItemStarStringList(UnstarStringList, false);
//Remove from list when succeeded
if (Result)
{
Int32 DeletedItems = await SQLConnection.ExecuteAsync("DELETE FROM TableOffline WHERE item_unstar_status");
Debug.WriteLine("Removed " + DeletedItems + " unstar offline synced items...");
}
else
{
Debug.WriteLine("Failed to sync offline unstar items.");
}
}
if (EnableUI)
{
await EventProgressEnableUI();
}
}
catch { await EventProgressEnableUI(); }
}
static void Main()
{
//TODOL: Refactoring komplett
//TODO: Trennung von Input und Logik für WPF
//TODOL: csv path anpassen + generic?
//TODO: List 2.2 2.3 3.2 3.3 - Import - Edit - general check again
ShowConsoleOutput showList = new ShowConsoleOutput();
ContactBookLogic contactbooklogic = new ContactBookLogic();
CsvReader reader = new CsvReader();
SQLConnection sql = new SQLConnection();
while (true)
{
Console.WriteLine("\nType 'Add', 'Edit', 'Remove', 'List', 'Quit', 'Help', 'Clear' or 'Import'");
long countContacts = sql.GetTableRowCount("contacts");
long countLocations = sql.GetTableRowCount("locations");
Console.WriteLine($"There are currently {countContacts} contacts and {countLocations} locations in the database.\n");
string input = Console.ReadLine();
// ADD METHOD
if (input == "Add")
{
Console.WriteLine("\nWhat do you want to add?\n1. Contact\n2. Location\n");
input = Console.ReadLine();
if (input == "1")
{
ContactbookConsoleInputControl.AddContactCommand(contactbooklogic, sql, countLocations);
}
else if (input == "2")
{
ContactbookConsoleInputControl.AddOrGetLocationCommand(contactbooklogic, sql, countLocations);
}
else
{
Console.WriteLine("WARNING: Invalid Input.\n");
}
}
// EDIT AND MERGE METHODS
else if (input == "Edit")
{
if (countContacts > 0 || countLocations > 0)
{
Console.WriteLine("\nWhat do you want to do?\n1. Edit a contact or location\n2. Merge a contact\n");
var i = Console.ReadLine();
//EDIT
//TODO: hier edit refactoren
if (i == "1")
{
Console.WriteLine("\nWhat do you want to edit?\n1. Contact\n2. Location\n");
var a = Console.ReadLine();
if (a == "1")
{
if (countContacts > 0)
{
ContactbookConsoleInputControl.EditContactCommand(contactbooklogic, sql, countContacts);
}
else
{
Console.WriteLine("\nWARNING: There is no contact that can be edited.\n");
}
}
else if (a == "2")
{
if (countLocations > 0)
{
ContactbookConsoleInputControl.EditLocationCommand(contactbooklogic, sql, countLocations);
}
else
{
Console.WriteLine("\nWARNING: There is no location that can be edited.\n");
}
}
else
{
Console.WriteLine("WARNING: Invalid Input");
}
}
//MERGE
else if (i == "2" && countContacts > 1)
{
ContactbookConsoleInputControl.MergeCommand(contactbooklogic, sql);
}
else
{
Console.WriteLine("\nWARNING: Invalid Input or not enough contacts.");
}
}
else
{
Console.WriteLine("\nWARNING: You need atleast a contact or a location to edit or merge something.");
}
}
// REMOVE METHOD
else if (input == "Remove")
{
if (countContacts > 0 || countLocations > 0)
{
Console.WriteLine("\nWhat do you want to remove?\n1. Contact\n2. Location\n3. Everything\n");
var b = Console.ReadLine();
if (b == "1")
{
if (countContacts > 0)
{
Console.WriteLine("Please enter the index of the contact you want to remove.");
sql.ReadContactsTable();
Console.WriteLine("");
bool numberCheck = int.TryParse(Console.ReadLine(), out var value);
if (numberCheck)
{
contactbooklogic.RemoveContact(contactbooklogic, countContacts, sql, value);
}
else
{
Console.WriteLine("\nWARNING: There is no contact that can be removed.\n");
}
}
}
else if (b == "2")
{
if (countLocations > 0)
{
Console.WriteLine("\nPlease enter the index of the location you want to remove.\n");
List <Location> locationsList = sql.ReadLocationsTable();
foreach (var loc in locationsList)
{
Console.WriteLine($"{loc.LocationID} {loc.Address} {loc.CityName}, has contact: {loc.HasContact} ");
}
Console.WriteLine("");
bool numberCheck = int.TryParse(Console.ReadLine(), out var value);
if (numberCheck)
{
contactbooklogic.RemoveLocation(contactbooklogic, countLocations, sql, value);
}
else
{
Console.WriteLine("WARNING: Invalid Input");
}
}
else
{
Console.WriteLine("\nWARNING: There is no location that can be removed.\n");
}
}
else if (b == "3")
{
if (countContacts > 0 || countLocations > 0)
{
Console.WriteLine("\nIf you really want to empty the entire database enter 'y' now.\n");
var confirmation = Console.ReadLine();
if (confirmation == "y")
{
contactbooklogic.RemoveEverything(sql);
}
}
else
{
Console.WriteLine("\nWARNING: There is nothing that can be deleted from the table.\n");
}
}
else
{
Console.WriteLine("WARNING: Invalid Input.\n");
}
}
else
{
Console.WriteLine("\nWARNING: There is nothing that can be removed.\n");
}
}
// LIST METHOD
else if (input == "List")
{
if (countContacts > 0 || countLocations > 0)
{
showList.ListWanted(contactbooklogic, sql, countContacts, countLocations);
}
else
{
Console.WriteLine("\nWARNING: There is nothing that can be listed.\n");
}
}
// QUIT METHOD
else if (input == "Quit")
{
break;
}
else if (input == "Clear")
{
Console.Clear();
}
// HELP METHOD
else if (input == "Help")
{
ContactbookConsoleInputControl.HelpCommand();
}
//IMPORT METHOD
else if (input == "Import")
{
Console.WriteLine("Do you want to import 1. testfile.csv or 2. errortestfile.csv?\nType 1 or 2\n");
string csvFileName = "";
string fileNameInput = Console.ReadLine();
Console.WriteLine("");
if (fileNameInput == "1")
{
csvFileName = "testfile";
reader.ImportEntriesFromCsvIntoList(contactbooklogic, csvFileName, sql);
}
else if (fileNameInput == "2")
{
csvFileName = "errortestfile";
reader.ImportEntriesFromCsvIntoList(contactbooklogic, csvFileName, sql);
}
else
{
Console.WriteLine("WARNING: Wrong input.");
}
}
else
{
Console.WriteLine($"\nWARNING: {input} is not a valid input. \n");
}
}
}
private void Hauptfenster_FormClosing(object sender, FormClosingEventArgs e)
{
SQLConnection.DisConnect();
SSHConnection.DisConnect();
}
private void btnAdd_Click(object sender, EventArgs e)
{
// input checks
if (OrigintimePicker.Value > DestinationtimePicker.Value)
{
Utils.Alerts.errorMessage("Sorry the Destination arrival time must be after Origin time! ");
return;
}
if (cbUserTickets.Text != "" && cbOriginStstion.Text != "" && cbDestinationSation.Text != "" && cbLins.Text != "")
{
if (SQLConnection.GetDataContextInstance().tblActivities.Where(u => u.cardNumber == long.Parse(cbUserTickets.Text) && u.cardPurchaseDate == ticketPurchaseDate &&
u.activityDate == OrigintimePicker.Value).Any())
{
Utils.Alerts.errorMessage("This Ticket have an order for this time!");
return;
}
if (SQLConnection.GetDataContextInstance().tblActivities.Where(u => u.cardNumber == long.Parse(cbUserTickets.Text) && u.cardPurchaseDate == ticketPurchaseDate &&
u.activityDate == DestinationtimePicker.Value).Any())
{
Utils.Alerts.errorMessage("This Ticket have an order for this time!");
return;
}
dataCommand.Connection = SQLConnection.con;
dataCommand.CommandText = ("insert tblActivity (cardNumber, cardPurchaseDate, activityDate, activityType, stationID, lineName) values (@TNUMBER, @PDATE, @ADATE, @ATYPE, @SID, @LN)");
dataCommand.Parameters.AddWithValue("@TNUMBER", long.Parse(cbUserTickets.Text));
dataCommand.Parameters.AddWithValue("@PDATE", ticketPurchaseDate);
dataCommand.Parameters.AddWithValue("@ADATE", OrigintimePicker.Value);
dataCommand.Parameters.AddWithValue("@ATYPE", 'I');
dataCommand.Parameters.AddWithValue("@SID", int.Parse(OriginStationID));
dataCommand.Parameters.AddWithValue("@LN", cbLins.Text);
dataCommand1.Connection = SQLConnection.con;
dataCommand1.CommandText = ("insert tblActivity (cardNumber, cardPurchaseDate, activityDate, activityType, stationID, lineName) values (@TNUMBER1, @PDATE1, @ADATE1, @ATYPE1, @SID1, @LN1)");
dataCommand1.Parameters.AddWithValue("@TNUMBER1", long.Parse(cbUserTickets.Text));
dataCommand1.Parameters.AddWithValue("@PDATE1", ticketPurchaseDate);
dataCommand1.Parameters.AddWithValue("@ADATE1", DestinationtimePicker.Value);
dataCommand1.Parameters.AddWithValue("@ATYPE1", 'O');
dataCommand1.Parameters.AddWithValue("@SID1", int.Parse(DestinationStationID));
dataCommand1.Parameters.AddWithValue("@LN1", cbLins.Text);
try
{
SQLConnection.con.Open();
dataCommand.ExecuteNonQuery();
dataCommand1.ExecuteNonQuery();
Utils.Alerts.dataSavedSuccessfully();
mainWindow.Enabled = true;
this.Dispose();
}
catch (Exception)
{
Utils.Alerts.errorMessage("Something is wrong, couldn't add this data!");
}
finally { SQLConnection.con.Close(); }
}
else
{
Utils.Alerts.errorMessage("You Must Fill All Fields!!");
}
}
private void btnConnection_Click(object sender, EventArgs e)
{
this.conManager = this.rbRemote.Checked ? new SQLConnection(this.txtServer.Text, this.txtDataBase.Text, this.txtUserName.Text, this.txtPassword.Text) : (this.conManager = new SQLConnection(this.txtServer.Text, this.txtDataBase.Text));
this.btnConnection.Enabled = false;
this.dt = DateTime.Now;
this.timer1.Start();
TimedMutipleThreadTester con = new TimedMutipleThreadTester();
con.Timeout = (int)this.numericUpDown1.Value;
//con.ConnectionString = this.conManager.ConnectionString;
// con.AfterTest += new EventHandler(mt_AfterTest);
con.StartTest();
}
static public async Task <bool> ItemsRead(ObservableCollection <Items> UpdateList, bool Silent, bool EnableUI)
{
try
{
if (!Silent)
{
await EventProgressDisableUI("Downloading read status...", true);
}
System.Diagnostics.Debug.WriteLine("Downloading read status...");
//Get all stored items from the database
List <TableItems> CurrentItems = await SQLConnection.Table <TableItems>().ToListAsync();
if (CurrentItems.Any())
{
//Get last stored item date minus starred items
TableItems LastStoredItem = CurrentItems.Where(x => x.item_star_status == false).OrderByDescending(x => x.item_datetime).LastOrDefault();
if (LastStoredItem != null)
{
//Date time calculations
DateTime RemoveItemsRange = LastStoredItem.item_datetime.AddHours(-1);
//System.Diagnostics.Debug.WriteLine("Downloading read items till: " + LastStoredItem.item_title + "/" + RemoveItemsRange);
long UnixTimeTicks = (RemoveItemsRange.Ticks - DateTime.Parse("01/01/1970 00:00:00").Ticks) / 10000000; //Second
string[][] RequestHeader = new string[][] { new[] { "Authorization", "GoogleLogin auth=" + AppVariables.ApplicationSettings["ConnectApiAuth"].ToString() } };
Uri DownloadUri = new Uri(ApiConnectionUrl + "stream/items/ids?output=json&s=user/-/state/com.google/read&n=" + AppVariables.ItemsMaximumLoad + "&ot=" + UnixTimeTicks);
string DownloadString = await AVDownloader.DownloadStringAsync(10000, "News Scroll", RequestHeader, DownloadUri);
if (!string.IsNullOrWhiteSpace(DownloadString))
{
JObject WebJObject = JObject.Parse(DownloadString);
if (WebJObject["itemRefs"] != null && WebJObject["itemRefs"].HasValues)
{
if (!Silent)
{
await EventProgressDisableUI("Updating " + WebJObject["itemRefs"].Count() + " read status...", true);
}
System.Diagnostics.Debug.WriteLine("Updating " + WebJObject["itemRefs"].Count() + " read status...");
//Check and set the received read item ids
string ReadUpdateString = string.Empty;
List <string> ReadItemsList = new List <string>();
foreach (JToken JTokenRoot in WebJObject["itemRefs"])
{
string FoundItemId = JTokenRoot["id"].ToString().Replace(" ", string.Empty).Replace("tag:google.com,2005:reader/item/", string.Empty);
ReadUpdateString += "'" + FoundItemId + "',";
ReadItemsList.Add(FoundItemId);
}
//Update the read status in database
if (ReadItemsList.Any())
{
ReadUpdateString = AVFunctions.StringRemoveEnd(ReadUpdateString, ",");
int UpdatedItems = await SQLConnection.ExecuteAsync("UPDATE TableItems SET item_read_status = ('1') WHERE item_id IN (" + ReadUpdateString + ") AND item_read_status = ('0')");
System.Diagnostics.Debug.WriteLine("Updated read items: " + UpdatedItems);
}
//Update the read status in list
await CoreApplication.MainView.CoreWindow.Dispatcher.RunAsync(CoreDispatcherPriority.Normal, () =>
{
try
{
List <Items> ReadItemsIDList = UpdateList.Where(x => x.item_read_status == Visibility.Collapsed && ReadItemsList.Any(y => y == x.item_id)).ToList();
foreach (Items ReadItem in ReadItemsIDList)
{
ReadItem.item_read_status = Visibility.Visible;
}
}
catch { }
});
//Update the unread status in database
string UnreadUpdateString = string.Empty;
List <string> UnreadItemsList = (await SQLConnection.Table <TableItems>().ToListAsync()).Where(x => x.item_read_status == true && x.item_datetime > RemoveItemsRange).Select(x => x.item_id).Except(ReadItemsList).ToList();
foreach (string UnreadItem in UnreadItemsList)
{
UnreadUpdateString += "'" + UnreadItem + "',";
}
if (UnreadItemsList.Any())
{
UnreadUpdateString = AVFunctions.StringRemoveEnd(UnreadUpdateString, ",");
int UpdatedItems = await SQLConnection.ExecuteAsync("UPDATE TableItems SET item_read_status = ('0') WHERE item_id IN (" + UnreadUpdateString + ") AND item_read_status = ('1')");
System.Diagnostics.Debug.WriteLine("Updated unread items: " + UpdatedItems);
}
//Update the unread status in list
await CoreApplication.MainView.CoreWindow.Dispatcher.RunAsync(CoreDispatcherPriority.Normal, () =>
{
try
{
List <Items> UnreadItemsIDList = UpdateList.Where(x => x.item_read_status == Visibility.Visible && UnreadItemsList.Any(y => y == x.item_id)).ToList();
foreach (Items UnreadItem in UnreadItemsIDList)
{
UnreadItem.item_read_status = Visibility.Collapsed;
}
}
catch { }
});
}
}
}
}
if (EnableUI)
{
await EventProgressEnableUI();
}
return(true);
}
catch
{
await EventProgressEnableUI();
return(false);
}
}
public static SQLConnection GetConnection()
{
SQLConnection con = new SQLConnection();
return(con);
}
public MappingFiles()
{
_bdd = SQLConnection.Instance();
}
public static Boolean LoadAllCoursesAsObjects()
{
CourseSQL.courses = null;
SqlConnection sqlConnection = SQLConnection.GetSqlConnection();
if (sqlConnection == null)
{
MessageBox.Show("SQL Connection hasn't been initialized yet.");
return(false);
}
SqlCommand sqlCommand = new SqlCommand("SELECT * FROM CourseTable", sqlConnection);
try
{
SqlDataReader dataReader = sqlCommand.ExecuteReader();
int i = 0;
int counter = 0;
while (dataReader.Read() == true)
{
counter++;
}
courses = new Course[counter];
dataReader.Close();
dataReader = sqlCommand.ExecuteReader();
while (dataReader.Read() == true)
{
courses[i] = new Course();
courses[i].CourseID = Convert.ToInt32(dataReader["courseID"].ToString());
courses[i].CourseName = dataReader["courseName"].ToString();
courses[i].YearTaught = Convert.ToInt32(dataReader["yearTaught"].ToString());
int semester = Convert.ToInt32(dataReader["SemesterTaught"].ToString());
switch (semester)
{
case 1:
courses[i].SemesterTaught = Semester.First;
break;
case 2:
courses[i].SemesterTaught = Semester.Second;
break;
case 3:
courses[i].SemesterTaught = Semester.Third;
break;
default:
courses[i].SemesterTaught = Semester.First;
break;
}
if (Convert.ToInt32(dataReader["isCourseOpen"]) == 1)
{
courses[i].IsOpen = true;
}
else
{
courses[i].IsOpen = false;
}
i++;
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return(true);
}
/// <summary>
/// TrackerSql Method: This method will track a user and comit it to the datalayer.
/// </summary>
public void TrackerSql()
{
foreach (var area in _Areas)
{
switch (area) //add new enum here
{
case PerformancePortal.Mos:
AreaChosen = "MOS";
break;
case PerformancePortal.Ckpi:
AreaChosen = "Corporate KPI Scorecards";
break;
case PerformancePortal.Framework:
AreaChosen = "Incident KPI Framework";
break;
case PerformancePortal.Iat:
AreaChosen = "Incident Analysis Tool";
break;
case PerformancePortal.Ibt:
AreaChosen = "Internal Benchmarking Tool";
break;
case PerformancePortal.Iqt:
AreaChosen = "Incident Query Tool";
break;
case PerformancePortal.Kpi:
AreaChosen = "Incident KPI Tool";
break;
case PerformancePortal.Qrt:
AreaChosen = "Quarterly Reporting Tool";
break;
case PerformancePortal.Ss:
AreaChosen = "Station Scorecard";
break;
case PerformancePortal.Portal:
AreaChosen = "Performance Portal";
break;
case PerformancePortal.Er:
AreaChosen = "Emergency Reponse";
break;
}
string INSERTstatement;
string Selectstatement;
DataSet TrackerDataSet;
string strSQLconnection;
SQLConnection ConnectToDataBase = new SQLConnection();
SQLConnection InsertQuery = new SQLConnection();
strSQLconnection = SqlConnect;
InsertQueryBuilder insertQuery = new InsertQueryBuilder();
SelectQueryBuilder selectQuery = new SelectQueryBuilder();
//create select statement
selectQuery.SelectColumns("Name", "Department", "[Rank]");
selectQuery.SelectFromTable("OrganisationalStructure");
selectQuery.AddWhere("[Login]", Comparison.Equals, UserName);
Selectstatement = selectQuery.BuildQuery();
//retrieve select statement dataset
ConnectToDataBase.connection_string = strSQLconnection;
ConnectToDataBase.Sql = Selectstatement;
TrackerDataSet = ConnectToDataBase.GetConnection;
//get data from dataset table
var name = TrackerDataSet.Tables[0].Rows[0]["Name"].ToString();
var department = TrackerDataSet.Tables[0].Rows[0]["Department"].ToString();
var rank = TrackerDataSet.Tables[0].Rows[0]["Rank"].ToString();
//create insert statmement
insertQuery.Table = "PerfPortal_Tracker";
insertQuery.SetField("LoginName", UserName);
insertQuery.SetField("FullName", name);
insertQuery.SetField("Department", department);
insertQuery.SetField("JobTitle", rank);
insertQuery.SetField("[DateTime]", DateTime.Now.ToString(CultureInfo.InvariantCulture));
insertQuery.SetField("AreaAccessed", AreaChosen);
INSERTstatement = insertQuery.BuildQuery();
//send sql statement to server
InsertQuery.Query(INSERTstatement, SqlConnect);
}
}
/// <summary>
/// IncidentEquipment Method: Using the supplied incident number this method brings back the equipment used at the incident
/// </summary>
/// <param name="IncidentNumber">Requires you to pass the incident number to bring back relevant data (String)</param>
public DataSet IncidentEquipment(string IncidentNumber)
{
//Using the supplied incident number this method brings back the equipment used at the incident
SQLConnection ConnectToDataBase = new SQLConnection();
DataSet dataset = new DataSet();
SelectQueryBuilder Query = new SelectQueryBuilder();
Query.SelectColumns("Equip_Description as 'Equipment Description'", "Equip_Number_Used as 'Amount Used'");
Query.SelectFromTable("incidentdim" + " left OUTER JOIN Equipment_Used ON incidentdim.Inc_Pk = Equipment_Used.Equ_Fk_Inc");
//Query.AddJoin(JoinType.OuterJoin, "Equipment_Used ", "Equ_Fk_Inc", Comparison.Equals, "incidentdim", "Inc_Pk");
Query.AddWhere("id_in_ref", Comparison.Equals, IncidentNumber);
var EquipMentStatement = Query.BuildQuery();
ConnectToDataBase.connection_string = SQLConnect;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = EquipMentStatement;
dataset = ConnectToDataBase.GetConnection;
return dataset;
}
internal override bool Query()
{
string query1_1 = string.Format(
"USE {0};\n" +
"SELECT \'{1}\' as [ComputerName],\n" +
"\'{2}\' as [Instance],",
database, computerName, instance
);
string query1_3 = string.Format(
"FROM [{0}].[INFORMATION_SCHEMA].[SCHEMATA]",
database
);
using (SQLConnection sql = new SQLConnection(instance))
{
sql.BuildConnectionString(credentials);
if (!sql.Connect())
{
return(false);
}
StringBuilder sb = new StringBuilder();
sb.Append(query1_1);
sb.Append(QUERY1_2);
sb.Append(query1_3);
if (!string.IsNullOrEmpty(schemaFilter))
{
sb.Append(schemaFilter);
}
sb.Append(QUERY1_4);
#if DEBUG
Console.WriteLine(sb.ToString());
#endif
//table = sql.Query(sb.ToString());
schemas = sql.Query <Schema>(sb.ToString(), new Schema());
}
/*
* foreach (DataRow row in table.AsEnumerable())
* {
* try
* {
* Schema s = new Schema
* {
* ComputerName = (string)row["ComputerName"],
* Instance = (string)row["Instance"],
* DatabaseName = (string)row["DatabaseName"],
* SchemaName = (string)row["SchemaName"],
* SchemaOwner = (string)row["SchemaOwner"],
* };
#if DEBUG
* Misc.PrintStruct<Schema>(s);
#endif
* schemas.Add(s);
* }
* catch (Exception ex)
* {
* if (ex is ArgumentNullException)
* Console.WriteLine("Empty Response");
* else
* Console.WriteLine(ex);
* return false;
* }
* }
*/
return(true);
}
/// <summary>
/// IncidentMobilisation Method: using the incident number thsi method brings back the mobilisations to the incident..
/// </summary>
/// <param name="Incidentnumber">Requires you to pass the incident number to bring back relevant data (String)</param>
public DataSet IncidentMobilisation(string Incidentnumber)
{
//using the incident number thsi method brings back the mobilisations to the incident.
SelectQueryBuilder Query = new SelectQueryBuilder(); //creates the query to be used
DataSet Dataset = new DataSet();
SQLConnection ConnectToDataBase = new SQLConnection();
Query.SelectColumns("num_vehicles_deployed", "MB_CALL_SIGN", "ApplianceType", "MB_ARRIVE", "MB_LEAVE", "MB_MOBILE", "MB_OIC", "MB_RETURN",
"MobilisationStatus", "RIGHT(CONVERT(CHAR(8),DATEADD(SECOND,Send_to_Mobile_int,0),108),5) AS Send_to_Mobile_int", "RIGHT(CONVERT(CHAR(8),DATEADD(SECOND,Mobile_to_Arrive_int,0),108),5) AS Mobile_to_Arrive_int", "Send_to_Leave_int", "OfficerCount",
"PFStatus", "TheOrder", "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS].Emergency", "ArrLatLong", "MobLatLong"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[MB_MOBILISATIONS]", "MB_IN_REF", Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddJoin(JoinType.LeftJoin, "[dmtreporting_dev].[dbo].[vw_mobilisation_counts]", "MB_IN_REF", Comparison.Equals, "incidentdim", "id_in_ref");
Query.AddWhere("id_in_ref", Comparison.Equals, Incidentnumber);
Query.AddOrderBy("MB_ARRIVE", Sorting.Ascending);
var statement = Query.BuildQuery();
ConnectToDataBase.connection_string = SQLConnect;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = statement;
Dataset = ConnectToDataBase.GetConnection;
return Dataset;
}
private void OnApplicationQuit()
{
SQLConnection.EndConnection();
}
/// <summary>
/// NarrativeLog Method: using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
/// </summary>
/// <param name="incidentnumber">Requires you to pass the incident number to bring back relevant data (String)</param>
/// /// <param name="FilterIndex">Requires you to pass either 0 or depending on when the incident was (pre or post NWFC (int)</param>
public DataSet NarrativeLog(string incidentnumber, int FilterIndex)
{
//using the supplied incident number and the desired filter selected by the user, this method will retrieve the required dataset ready for populating the reports section
var Query = new SelectQueryBuilder();
string strSQLconnection;
SQLConnection ConnectToDataBase;
Query.SelectColumns("nar_act_time as 'Time'", "nar_log_text as 'Narrative Text'"); //Select Statement
Query.SelectFromTable("incidentdim"); //table name for the FROM section
Query.AddJoin(JoinType.LeftJoin, "[threetc_mis].[dbo].[nar_rative_log]", "nar_inc_id", Comparison.Equals, "incidentdim", "id_in_ref");
string IncidentDate = IncidentData.Tables[0].Rows[0]["Date Created"].ToString();
//string Date = IncidentDate.Substring(0, 10);
DateTime Incidentdt = Convert.ToDateTime(IncidentDate);
const string FireControlDate = "29/05/2014"; //date that we went to new control, used for narrative log formating
DateTime Controldt = Convert.ToDateTime(FireControlDate);
// int results = DateTime.Compare(dt, Fdt);
if (Incidentdt.Date < Controldt.Date && FilterIndex == 1)
{
Query.AddWhere("nar_log_entry", Comparison.Equals, "TEXT");
}
else
{
}
if (Incidentdt.Date > Controldt.Date && FilterIndex == 1)
{
WhereClause clause =
Query.AddWhere("nar_log_text", Comparison.Like, "%Message%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%INFORMATIVE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%RADIO%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%ASSISTANCE%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%FROM%");
clause.AddClause(LogicOperator.Or, Comparison.Like, "%OTHER%");
}
else
{
}
Query.AddWhere("id_in_ref", Comparison.Equals, incidentnumber);
Query.AddOrderBy("nar_act_time", Sorting.Ascending);
var statement = Query.BuildQuery();
if (statement == null) throw new ArgumentNullException("incidentnumber");
//connect to the database
ConnectToDataBase = new SQLConnection();
strSQLconnection = SQLConnect;
ConnectToDataBase.connection_string = strSQLconnection;
//set the sql statement to bind to the gridview
ConnectToDataBase.Sql = statement;
DataSet narDataset;
using (narDataset = ConnectToDataBase.GetConnection)
{
if (narDataset == null) throw new ArgumentNullException("incidentnumber");
return narDataset;
}
}
internal override bool Query()
{
using (SQLConnection sql = new SQLConnection(instance))
{
sql.BuildConnectionString(credentials);
if (!sql.Connect())
{
return(false);
}
string query1_1 = string.Format("USE {0};", database);
string query1_3 = string.Format(
"FROM [{0}].[sys].[triggers] WHERE 1=1",
database);
StringBuilder sb = new StringBuilder();
if (showAll)
{
sb.Append(QUERY2_1);
}
else
{
sb.Append(query1_1);
sb.Append(QUERY1_2);
sb.Append(query1_3);
if (!string.IsNullOrEmpty(assemblyNameFilter))
{
sb.Append(assemblyNameFilter);
}
}
#if DEBUG
Console.WriteLine(sb.ToString());
#endif
//table = sql.Query(sb.ToString());
files = sql.Query <AssemblyFiles>(sb.ToString(), new AssemblyFiles());
}
/*
* foreach (DataRow row in table.AsEnumerable())
* {
* try
* {
* AssemblyFiles af = new AssemblyFiles
* {
* ComputerName = computerName,
* Instance = instance,
* DatabaseName = database,
* schema_name = (string)row["schema_name"],
* file_id = (int)row["file_id"],
* file_name = (string)row["file_name"],
* clr_name = (string)row["clr_name"],
* assembly_id = (int)row["assembly_id"],
* assembly_name = (string)row["assembly_name"],
* assembly_class = (string)row["assembly_class"],
* assembly_method = (string)row["assembly_method"],
* sp_object_id = (int)row["sp_object_id"],
* sp_name = (string)row["sp_name"],
* sp_type = (string)row["sp_type"],
* permission_set_desc = (string)row["permission_set_desc"],
* create_date = (DateTime)row["create_date"],
* modify_date = (DateTime)row["modify_date"],
* content = (string)row["content"],
* };
#if DEBUG
* Misc.PrintStruct<AssemblyFiles>(af);
#endif
* files.Add(af);
* }
* catch (Exception ex)
* {
* if (ex is ArgumentNullException)
* Console.WriteLine("Empty Response");
* else
* Console.WriteLine(ex.Message);
* return false;
* }
* }
*/
return(true);
}
/// <summary>
/// PopulateIncidentData Method: Using the Building BuildIncidentQuery this method populates a dataset that contains the wanted incidents to display in the Reports Section
/// </summary>
/// <param name="IncidentNumber">Requires you to pass the incident number to bring back relevant data (String)</param>
public DataSet PopulateIncidentData(string IncidentNumber)
{
//Using the Building BuildIncidentQuery this method populates a dataset that contains the wanted incidents to display in the Reports Section
DataSet incidentDataSet = new DataSet();
string strSQLconnection;
SQLConnection ConnectToDataBase;
//connect to the database
ConnectToDataBase = new SQLConnection();
strSQLconnection = SQLConnect;
ConnectToDataBase.Sql = BuildIncidentQuery(IncidentNumber);
ConnectToDataBase.connection_string = strSQLconnection;
using (incidentDataSet = ConnectToDataBase.GetConnection)
{
if (incidentDataSet == null) throw new ArgumentNullException("IncidentNumber");
return incidentDataSet;
}
}
internal override bool Query()
{
bool isSysAdmin = false;
using (SQLConnection sql = new SQLConnection(instance))
{
sql.BuildConnectionString(credentials);
if (!sql.Connect())
{
return(false);
}
isSysAdmin = SQLSysadminCheck.Query(instance, computerName, credentials);
string query = query1_1;
if (isSysAdmin)
{
query += query1_2;
}
query += string.Format("SELECT \'{0}\' as [ComputerName],\n", computerName);;
query += query1_4;
if (isSysAdmin)
{
query += query1_5;
}
query += query1_6;
table = sql.Query(query);
}
foreach (DataRow row in table.AsEnumerable())
{
try
{
details = new Details
{
ComputerName = (string)row["ComputerName"],
Instance = (string)row["Instance"],
DomainName = (string)row["DomainName"],
ServiceProcessID = (int)row["ServiceProcessID"],
ServiceName = (string)row["ServiceName"],
ServiceAccount = (string)row["ServiceAccount"],
AuthenticationMode = (string)row["AuthenticationMode"],
ForcedEncryption = (int)row["ForcedEncryption"],
Clustered = (string)row["Clustered"],
SQLServerVersionNumber = (string)row["SQLServerVersionNumber"],
SQLServerMajorVersion = (string)row["SQLServerMajorVersion"],
SQLServerEdition = (string)row["SQLServerEdition"],
SQLServerServicePack = (string)row["SQLServerServicePack"],
OSArchitecture = (string)row["OSArchitecture"],
OsVersionNumber = (string)row["OsVersionNumber"],
Currentlogin = (string)row["Currentlogin"]
};
if (isSysAdmin)
{
details.OsMachineType = (string)row["OsMachineType"];
details.OSVersionName = (string)row["OSVersionName"];
}
#if DEBUG
Misc.PrintStruct <Details>(details);
#endif
return(true);
}
catch (Exception ex)
{
if (ex is ArgumentNullException)
{
Console.WriteLine("Empty Response");
}
else
{
Console.WriteLine(ex.Message);
}
return(false);
}
}
return(false);
}
/// <summary>
/// GetAdvancedSearchResults Method: Returns a dataset with search results
/// </summary>
/// <param name="Query"> type 1 = Pass the select query from AdvancedSearchQuery </param>
public DataSet GetAdvancedSearchResults(string Query)
{
DataSet returnedResults = new DataSet();
SQLConnection ConnectToDataBase;
//connect to the database
ConnectToDataBase = new SQLConnection
{
Sql = Query,
connection_string = SQLConnect
};
//set the sql statement to bind to the gridview
returnedResults = ConnectToDataBase.GetConnection;
return returnedResults;
}
internal override bool Query()
{
using (SQLConnection sql = new SQLConnection(instance))
{
sql.BuildConnectionString(credentials);
if (!sql.Connect())
{
return(false);
}
string query1_1 = string.Format(
"USE {0};\n" +
"SELECT \'{1}\' as [ComputerName],\n" +
"\'{2}\' as [Instance],\n" +
"\'{0}\' as [DatabaseName],",
database, computerName, instance);
string query1_3 = string.Format(
"FROM [{0}].[sys].[database_principals]\n" +
"WHERE type like \'R\'",
database);
StringBuilder sb = new StringBuilder();
sb.Append(query1_1);
sb.Append(QUERY1_2);
sb.Append(query1_3);
if (!string.IsNullOrEmpty(rolePrincipalNameFilter))
{
sb.Append(rolePrincipalNameFilter);
}
if (!string.IsNullOrEmpty(roleOwnerFilter))
{
sb.Append(roleOwnerFilter);
}
#if DEBUG
Console.WriteLine(sb.ToString());
#endif
//table = sql.Query(sb.ToString());
databaseRoles = sql.Query <DatabaseRole>(sb.ToString(), new DatabaseRole());
}
/*
* foreach (DataRow row in table.AsEnumerable())
* {
* try
* {
* DatabaseRole dr = new DatabaseRole
* {
* ComputerName = computerName,
* Instance = instance,
* DatabaseName = database,
* RolePrincipalId = (int)row["RolePrincipalId"],
* RolePrincipalSid = (byte[])row["RolePrincipalSid"],
* RolePrincipalName = (string)row["RolePrincipalName"],
* RolePrincipalType = (string)row["RolePrincipalType"],
* OwnerPrincipalId = (int)row["OwnerPrincipalId"],
* OwnerPrincipalName = (string)row["OwnerPrincipalName"],
* is_fixed_role = (bool)row["is_fixed_role"],
* create_date = (DateTime)row["create_date"],
* modify_Date = (DateTime)row["modify_Date"],
* default_schema_name = (object)row["default_schema_name"]
* };
#if DEBUG
* Misc.PrintStruct<DatabaseRole>(dr);
#endif
* databaseRoles.Add(dr);
* }
* catch (Exception ex)
* {
* if (ex is ArgumentNullException)
* Console.WriteLine("Empty Response");
* else
* Console.WriteLine(ex.Message);
* return false;
* }
* }
*/
return(true);
}
public MappingUsers()
{
_bdd = SQLConnection.Instance();
}
//
// @Override
// public ConcurrentHashIndex<SurfacePattern> readPatternIndex(String dir){
// //dir parameter is not used!
// try{
// Connection conn = SQLConnection.getConnection();
// //Map<Integer, Set<Integer>> pats = new ConcurrentHashMap<Integer, Set<Integer>>();
// String query = "Select index from " + patternindicesTable + " where tablename=\'" + tableName + "\'";
// Statement stmt = conn.createStatement();
// ResultSet rs = stmt.executeQuery(query);
// ConcurrentHashIndex<SurfacePattern> index = null;
// if(rs.next()){
// byte[] st = (byte[]) rs.getObject(1);
// ByteArrayInputStream baip = new ByteArrayInputStream(st);
// ObjectInputStream ois = new ObjectInputStream(baip);
// index = (ConcurrentHashIndex<SurfacePattern>) ois.readObject();
// }
// assert index != null;
// return index;
// }catch(SQLException e){
// throw new RuntimeException(e);
// } catch (ClassNotFoundException e) {
// throw new RuntimeException(e);
// } catch (IOException e) {
// throw new RuntimeException(e);
// }
// }
//
// @Override
// public void savePatternIndex(ConcurrentHashIndex<SurfacePattern> index, String file) {
// try {
// createUpsertFunctionPatternIndex();
// Connection conn = SQLConnection.getConnection();
// PreparedStatement st = conn.prepareStatement("select upsert_patternindex(?,?)");
// st.setString(1,tableName);
// ByteArrayOutputStream baos = new ByteArrayOutputStream();
// ObjectOutputStream oos = new ObjectOutputStream(baos);
// oos.writeObject(index);
// byte[] patsAsBytes = baos.toByteArray();
// ByteArrayInputStream bais = new ByteArrayInputStream(patsAsBytes);
// st.setBinaryStream(2, bais, patsAsBytes.length);
// st.execute();
// st.close();
// conn.close();
// System.out.println("Saved the pattern hash index for " + tableName + " in DB table " + patternindicesTable);
// }catch (SQLException e){
// throw new RuntimeException(e);
// } catch (IOException e) {
// throw new RuntimeException(e);
// }
// }
//batch processing below is copied from Java Ranch
//TODO: make this into an iterator!!
public override IDictionary <string, IDictionary <int, ICollection <E> > > GetPatternsForAllTokens(ICollection <string> sampledSentIds)
{
try
{
IDictionary <string, IDictionary <int, ICollection <E> > > pats = new Dictionary <string, IDictionary <int, ICollection <E> > >();
IConnection conn = SQLConnection.GetConnection();
IEnumerator <string> iter = sampledSentIds.GetEnumerator();
int totalNumberOfValuesLeftToBatch = sampledSentIds.Count;
while (totalNumberOfValuesLeftToBatch > 0)
{
int batchSize = SingleBatch;
if (totalNumberOfValuesLeftToBatch >= LargeBatch)
{
batchSize = LargeBatch;
}
else
{
if (totalNumberOfValuesLeftToBatch >= MediumBatch)
{
batchSize = MediumBatch;
}
else
{
if (totalNumberOfValuesLeftToBatch >= SmallBatch)
{
batchSize = SmallBatch;
}
}
}
totalNumberOfValuesLeftToBatch -= batchSize;
StringBuilder inClause = new StringBuilder();
for (int i = 0; i < batchSize; i++)
{
inClause.Append('?');
if (i != batchSize - 1)
{
inClause.Append(',');
}
}
IPreparedStatement stmt = conn.PrepareStatement("select sentid, patterns from " + tableName + " where sentid in (" + inClause.ToString() + ")");
for (int i_1 = 0; i_1 < batchSize && iter.MoveNext(); i_1++)
{
stmt.SetString(i_1 + 1, iter.Current);
}
// or whatever values you are trying to query by
stmt.Execute();
IResultSet rs = stmt.GetResultSet();
while (rs.Next())
{
string sentid = rs.GetString(1);
byte[] st = (byte[])rs.GetObject(2);
ByteArrayInputStream baip = new ByteArrayInputStream(st);
ObjectInputStream ois = new ObjectInputStream(baip);
pats[sentid] = (IDictionary <int, ICollection <E> >)ois.ReadObject();
}
}
conn.Close();
return(pats);
}
catch (Exception e)
{
throw new Exception(e);
}
}
public IHttpActionResult Get(int Oid)
{
//create a list of BestellungSummen
List <BestellungSummen> bestellungSummenList = new List <BestellungSummen>();
decimal summe = 0;
//Bestellung
var bestellung = unitOfWork.FindObject <Bestellung>(CriteriaOperator.Parse("Oid==?", Oid));
//list of all bestellArtikels
//var bestellArtikels = unitOfWork.GetObjects(unitOfWork.GetClassInfo(typeof(BestellArtikel)), CriteriaOperator.Parse("Bestellung==?", bestellung), null, 10, null, true);
var bestellArtikels = new XPCollection <BestellArtikel>(unitOfWork, CriteriaOperator.Parse("Bestellung==?", bestellung));
foreach (BestellArtikel bestellArtikel in bestellArtikels)
{
Artikelstamm _artikelstamm = unitOfWork.FindObject <Artikelstamm>(CriteriaOperator.Parse("Oid==?", bestellArtikel.Artikel.Oid));
//ArtikelLieferbar artikelLieferbar = unitOfWork.FindObject<ArtikelLieferbar>(CriteriaOperator.Parse("Artikel.Oid==?", _artikelstamm.Oid));
//_artikelstamm.Stueckzahl = artikelLieferbar.StueckzahlLieferbar;
summe = Convert.ToDecimal(unitOfWork.Evaluate <BestellArtikel>(CriteriaOperator.Parse("sum(Stueckzahl)"), CriteriaOperator.Parse("Bestellung.Status == false AND Bestellung.Fertig == true AND Artikel == ?", _artikelstamm)));
if (summe > 0)
{
BestellungSummen bestellungSummen = new BestellungSummen(unitOfWork)
{
StueckSumme = summe,
Artikel = _artikelstamm
};
decimal difference = bestellungSummen.Artikel.Bestand - bestellungSummen.StueckSumme;
if (difference < 0)
{
bestellungSummen.Lieferbar = false;
}
else
{
bestellungSummen.Lieferbar = true;
}
bestellungSummenList.Add(bestellungSummen);
}
}
//bestellungSummenList is ready
if (bestellungSummenList.Count != 0)
{
//var newbestellungSummenList = bestellungSummenList.Where(i => i.Lieferbar == false);
MassBestellung _massbestellung;
BestellAuftraege _bestellauftrag;
SQLConnection _sqlconnection = unitOfWork.FindObject <SQLConnection>(CriteriaOperator.Parse("Oid==?", 1));
if (_sqlconnection.Mitarbeiter != 0)
{
if (bestellung.Fertig == true && bestellung.Status == false)
{
int newQty = 0;
foreach (BestellArtikel bestellArtikel in bestellArtikels)
{
_massbestellung = new MassBestellung(externalUow);
_massbestellung.mboid = _sqlconnection.Mitarbeiter + DateTime.Now.ToString("ddMMyyyy") + bestellArtikel.BestellKunden.Bestellung.Oid.ToString() + bestellArtikel.BestellKunden.KDNr;
_massbestellung.ArtikelNr = bestellArtikel.ArtikelNr;
_massbestellung.BestellNr = bestellArtikel.BestellKunden.Bestellung.Oid;
_massbestellung.KDNr = bestellArtikel.BestellKunden.KDNr;
_massbestellung.Stueck = bestellArtikel.Stueckzahl;
_massbestellung.Mitarbeiter = _sqlconnection.Mitarbeiter.ToString();
_massbestellung.Datum = DateTime.Now.Date;
bestellArtikel.mboid = _sqlconnection.Mitarbeiter + DateTime.Now.ToString("ddMMyyyy") + bestellArtikel.BestellKunden.Bestellung.Oid.ToString() + bestellArtikel.BestellKunden.KDNr;
_massbestellung.Save();
bestellArtikel.Save();
bestellArtikel.BestellKunden.Bestellung.Save();
bestellArtikel.Bestellung.Status = true;
// Neue Bestellung für BestellAuftrag - Zuweisung
_bestellauftrag = new BestellAuftraege(unitOfWork);
_bestellauftrag.mboid = _massbestellung.mboid;
_bestellauftrag.Bestellung = bestellArtikel.Bestellung;
//update available quantity
//newQty = bestellArtikel.Artikel.Bestand - Convert.ToInt32(bestellArtikel.Stueckzahl);
//bestellArtikel.Artikel.Bestand = newQty;
//bestellArtikel.Artikel.Save();
externalUow.CommitChanges();
unitOfWork.CommitChanges();
}
bestellung.Fertig = true;
unitOfWork.CommitChanges();
return(Json("Bestellungen erfolgreich abgesendet"));
}
else
{
return(Json("No Bistell to send"));
}
}
else
{
return(Json("Bitte tragen Sie unter 'Einstellungen' einen Mitarbeiter ein"));
}
}
else
{
return(Json("Es wurde keine Bestellung versendet"));
}
}
private void iTalk_Button_21_Click(object sender, EventArgs e)
{
String errorMsg = "";
if (courseName_textBox.Text.Length == 0)
{
errorMsg += "-No course name was entered.\n";
}
if (yearTaught_checkedListBox.SelectedItems.Count == 0)
{
errorMsg += "-No year was chosen.\n";
}
if (semesterTaught_checkedListBox.SelectedItems.Count == 0)
{
errorMsg += "-No semester was chosen.\n";
}
if (errorMsg.Length > 0)
{
MessageBox.Show("The following errors occured:\n" + errorMsg);
return;
}
course.CourseName = this.courseName_textBox.Text;
course.YearTaught = this.yearTaught_checkedListBox.SelectedIndex + 1;
int semesterTaught = this.semesterTaught_checkedListBox.SelectedIndex + 1;
switch (semesterTaught)
{
case 1:
course.SemesterTaught = Semester.First;
break;
case 2:
course.SemesterTaught = Semester.Second;
break;
case 3:
course.SemesterTaught = Semester.Third;
break;
default:
course.SemesterTaught = Semester.First;
break;
}
MessageBox.Show("Course name = " + course.CourseName + "\n" +
"Year Taught = " + course.YearTaught + "\n" +
"SemesterTaught = " + course.SemesterTaught);
SqlConnection connection = SQLConnection.GetSqlConnection();
if (connection == null)
{
MessageBox.Show("SQL connection hasn't been initialized or has been closed.");
return;
}
HeadOfDepartmentSQL sql = new HeadOfDepartmentSQL(connection);
//MessageBox.Show("Last Added Course ID= " + sql.GetLastAddedCourseID());
sql.UpdateCourse(course);
this.Close();
}
public DeleteCourse_Form()
{
InitializeComponent();
CourseSQL.LoadCoursesToListView(ref this.listView);
this.sqlConnection = SQLConnection.GetSqlConnection();
}
//Update feeds in selection
async Task UpdateSelectionFeeds(List <TableFeeds> LoadTableFeeds, List <TableItems> LoadTableItems, bool Silent, bool EnableUI)
{
try
{
if (!Silent)
{
await ProgressDisableUI("Updating selection feeds...", true);
}
Debug.WriteLine("Updating selection feeds, silent: " + Silent);
combobox_FeedSelection.IsHitTestVisible = false;
combobox_FeedSelection.Opacity = 0.30;
//Wait for busy database
await ApiUpdate.WaitForBusyDatabase();
//Check if received lists are empty
if (LoadTableFeeds == null)
{
LoadTableFeeds = await SQLConnection.Table <TableFeeds>().ToListAsync();
}
if (LoadTableItems == null)
{
LoadTableItems = await SQLConnection.Table <TableItems>().ToListAsync();
}
//Filter un/ignored feeds
List <String> IgnoredFeedList = LoadTableFeeds.Where(x => x.feed_ignore_status == true).Select(x => x.feed_id).ToList();
//Update the currently loaded feeds
foreach (Feeds FeedUpdate in List_FeedSelect.Where(x => !x.feed_folder_status))
{
Feeds TempFeed = new Feeds();
TempFeed.feed_id = FeedUpdate.feed_id;
FeedUpdate.feed_item_count = ProcessItemLoad.FilterNewsItems(IgnoredFeedList, LoadTableItems, TempFeed, 0, AppVariables.ItemsMaximumLoad).Count();
}
//Reset the loaded folders item count
foreach (Feeds FolderReset in List_FeedSelect.Where(x => x.feed_folder_status))
{
FolderReset.feed_item_count = 0;
}
//Update the currently loaded folders
foreach (Feeds FolderUpdate in List_FeedSelect.Where(x => x.feed_folder_status))
{
foreach (string FeedId in FolderUpdate.feed_folder_ids)
{
Feeds Feed = List_FeedSelect.Where(x => x.feed_id == FeedId).FirstOrDefault();
if (Feed != null && Feed.feed_item_count > 0)
{
FolderUpdate.feed_item_count = FolderUpdate.feed_item_count + Feed.feed_item_count;
//Debug.WriteLine("Added folder count: " + Feed.feed_item_count);
}
}
}
//Remove empty feeds and folders from combobox
bool FeedFolderRemoved = false;
if (!(bool)AppVariables.ApplicationSettings["DisplayReadMarkedItems"])
{
foreach (Feeds Feed in List_FeedSelect.ToList())
{
if (Feed.feed_item_count == 0 && !Feed.feed_collection_status)
{
Debug.WriteLine("Removing feed or folder: " + Feed.feed_title + Feed.feed_folder_title + " from the list.");
List_FeedSelect.Remove(Feed);
FeedFolderRemoved = true;
}
}
}
//Check if selected feed has been removed and set to read items feed
if (FeedFolderRemoved && combobox_FeedSelection.SelectedIndex == -1 || (combobox_FeedSelection.SelectedIndex == 0 && vCurrentLoadingFeedFolder.feed_item_count == 0))
{
Feeds TempFeed = new Feeds();
TempFeed.feed_id = "1";
//Change the selection feed
ChangeSelectionFeed(TempFeed, false);
//Load all the items
await LoadItems(false, false);
}
else
{
//Update the total item count
UpdateTotalItemsCount();
}
combobox_FeedSelection.IsHitTestVisible = true;
combobox_FeedSelection.Opacity = 1;
}
catch { }
if (EnableUI)
{
await ProgressEnableUI();
}
}
internal override bool Query()
{
bool isSysAdmin = false;
using (SQLConnection sql = new SQLConnection(instance))
{
sql.BuildConnectionString(credentials);
if (!sql.Connect())
{
return(false);
}
isSysAdmin = SQLSysadminCheck.Query(instance, computerName, credentials);
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("USE master;\nSELECT \'{0}\' as [ComputerName],\n\'{1}\' as [Instance],", computerName, instance));
sb.Append(QUERY1_2);
if (!string.IsNullOrEmpty(credentialFilter))
{
sb.Append(credentialFilter);
}
#if DEBUG
Console.WriteLine(sb.ToString());
#endif
//table = sql.Query(sb.ToString());
serverCredentials = sql.Query <ServerCredential>(sb.ToString(), new ServerCredential());
}
/*
* foreach (DataRow row in table.AsEnumerable())
* {
* try
* {
* ServerCredential sc = new ServerCredential
* {
* ComputerName = (string)row["ComputerName"],
* Instance = (string)row["Instance"],
* credential_id = (int)row["credential_id"],
* CredentialName = (string)row["CredentialName"],
* credential_identity = (string)row["credential_identity"],
* create_date = (DateTime)row["create_date"],
* modify_date = (DateTime)row["modify_date"],
* target_type = (string)row["target_type"],
* target_id = (int)row["target_id"]
* };
#if DEBUG
* Misc.PrintStruct<ServerCredential>(sc);
#endif
* serverCredentials.Add(sc);
* return true;
* }
* catch (Exception ex)
* {
* if (ex is ArgumentNullException)
* Console.WriteLine("Empty Response");
* else
* Console.WriteLine(ex.Message);
* return false;
* }
* }
*/
return(false);
}
protected void Onloadsql()
{
SQLConnection RetrieveUpdates = new SQLConnection();
SelectQueryBuilder Query = new SelectQueryBuilder();
DataSet ds = new DataSet();
RetrieveUpdates.connection_string =
"Server=hq-ict-12580s;Database=dmtreporting_dev;User Id=perfportal;Password=rouser;";
Query.TopRecords = 3;
Query.SelectColumns("*"); //Select Statement
Query.SelectFromTable("PerfPortal_LatestUpdates");
Query.AddOrderBy("LU_Created",Sorting.Descending);
var _statement = Query.BuildQuery();
RetrieveUpdates.Sql = _statement;
ds = RetrieveUpdates.GetConnection;
MyRepeater.DataSource = ds;
MyRepeater.DataBind();
}
/// <summary>
/// TrackerSql Method: This method will track a user and comit it to the datalayer.
/// </summary>
public void TrackerSql()
{
try
{
foreach (var area in _Areas)
{
switch (area) //add new enum here
{
case PerformancePortal.Mos:
AreaChosen = "MOS";
break;
case PerformancePortal.Ckpi:
AreaChosen = "Corporate KPI Scorecards";
break;
case PerformancePortal.Framework:
AreaChosen = "Incident KPI Framework";
break;
case PerformancePortal.Iat:
AreaChosen = "Incident Analysis Tool";
break;
case PerformancePortal.Ibt:
AreaChosen = "Internal Benchmarking Tool";
break;
case PerformancePortal.Iqt:
AreaChosen = "Incident Query Tool";
break;
case PerformancePortal.Kpi:
AreaChosen = "Incident KPI Tool";
break;
case PerformancePortal.Qrt:
AreaChosen = "Quarterly Reporting Tool";
break;
case PerformancePortal.Ss:
AreaChosen = "Station Scorecard";
break;
case PerformancePortal.Portal:
AreaChosen = "Performance Portal";
break;
case PerformancePortal.Er:
AreaChosen = "Emergency Reponse";
break;
}
string INSERTstatement;
string Selectstatement;
DataSet TrackerDataSet;
string strSQLconnection;
SQLConnection ConnectToDataBase = new SQLConnection();
SQLConnection InsertQuery = new SQLConnection();
strSQLconnection = SqlConnect;
InsertQueryBuilder insertQuery = new InsertQueryBuilder();
SelectQueryBuilder selectQuery = new SelectQueryBuilder();
//create select statement
selectQuery.SelectColumns("Name", "Department", "[Rank]");
selectQuery.SelectFromTable("OrganisationalStructure");
if (UserName.Length < 5)
{
selectQuery.AddWhere("[Login]", Comparison.Equals, @"GMFS\Hughesm");
}
else
{
selectQuery.AddWhere("[Login]", Comparison.Equals, UserName);
}
Selectstatement = selectQuery.BuildQuery();
//retrieve select statement dataset
ConnectToDataBase.connection_string = strSQLconnection;
ConnectToDataBase.Sql = Selectstatement;
TrackerDataSet = ConnectToDataBase.GetConnection;
//get data from dataset table
var name = TrackerDataSet.Tables[0].Rows[0]["Name"].ToString();
var department = TrackerDataSet.Tables[0].Rows[0]["Department"].ToString();
var rank = TrackerDataSet.Tables[0].Rows[0]["Rank"].ToString();
//create insert statmement
insertQuery.Table = "PerfPortal_Tracker";
insertQuery.SetField("LoginName", UserName);
insertQuery.SetField("FullName", name);
insertQuery.SetField("Department", department);
insertQuery.SetField("JobTitle", rank);
insertQuery.SetField("[DateTime]", DateTime.Now.ToString(CultureInfo.InvariantCulture));
insertQuery.SetField("AreaAccessed", AreaChosen);
INSERTstatement = insertQuery.BuildQuery();
//send sql statement to server
InsertQuery.Query(INSERTstatement, SqlConnect);
}
}
catch (Exception exec)
{
Console.WriteLine(exec);
}
}
