594 lines
22 KiB
C#
594 lines
22 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.OleDb;
|
|
using System.Windows.Forms;
|
|
|
|
namespace AdvertsingProfitControl
|
|
{
|
|
/// <summary>
|
|
/// Deprecated!
|
|
/// </summary>
|
|
class RetrieveFromDatabase
|
|
{
|
|
DatabaseTracker gDatabaseLocation = new DatabaseTracker();
|
|
LogConsole console = LogConsole.GetStaticInstance;
|
|
private string gConnectionString;
|
|
|
|
public RetrieveFromDatabase()
|
|
{
|
|
//First thing, get the connection string from the DatabaseTracker.
|
|
gConnectionString = gDatabaseLocation.DatabaseConnectionString;
|
|
}
|
|
|
|
|
|
private DataTable ExecuteQuery(OleDbCommand sqlCommand)
|
|
{
|
|
DataTable table = new DataTable();
|
|
|
|
try
|
|
{
|
|
sqlCommand.Connection.Open();
|
|
OleDbDataAdapter a = new OleDbDataAdapter(sqlCommand);
|
|
a.Fill(table);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
|
|
console.WriteToLog(LogConsole.Level.Error, "Error attempting to retrieve database from the database.");
|
|
console.WriteToLog(LogConsole.Level.Debug, "Retrieval error: " + e.Message);
|
|
}
|
|
finally
|
|
{
|
|
sqlCommand.Connection.Close();
|
|
}
|
|
|
|
return table;
|
|
}
|
|
|
|
public string RetrieveMostRecentDateId()
|
|
{
|
|
string dateID = "";
|
|
string selectString = "SELECT WeekEnding.ID FROM WeekEnding WHERE WeekEnding.EndOfWeekDate = (SELECT MAX(WeekEnding.EndOfWeekDate) FROM WeekEnding)";
|
|
DataTable table = new DataTable();
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Connection = connection;
|
|
connection.Open();
|
|
var reader = command.ExecuteReader();
|
|
while (reader != null && reader.Read())
|
|
{
|
|
dateID = reader[0].ToString();
|
|
}
|
|
connection.Close();
|
|
|
|
return dateID;
|
|
}
|
|
|
|
public string RetrieveAdItemId(string adItem)
|
|
{
|
|
string ID = "-1";
|
|
DataTable table = new DataTable();
|
|
string selectString = "SELECT AdItem.ID FROM AdItem WHERE AdItem.AdItem = @ID";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.AddWithValue("@ID", adItem);
|
|
table = ExecuteQuery(command);
|
|
if (table.Rows[0][0].ToString() != "")
|
|
{
|
|
ID = table.Rows[0][0].ToString();
|
|
}
|
|
return ID;
|
|
}
|
|
|
|
public string RetrieveDateIdByDateString(string dateString)
|
|
{
|
|
string dateID = "-1";
|
|
DataTable table = new DataTable();
|
|
string selectString = "SELECT WeekEnding.ID FROM WeekEnding WHERE WeekEnding.EndOfWeekDate = @dateString";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.AddWithValue("@dateString", dateString);
|
|
table = ExecuteQuery(command);
|
|
try
|
|
{
|
|
if (table.Rows[0][0].ToString() != "")
|
|
{
|
|
dateID = table.Rows[0][0].ToString();
|
|
}
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
dateID = "-1";
|
|
}
|
|
|
|
return dateID;
|
|
}
|
|
|
|
public string RetrieveComments(string dateId)
|
|
{
|
|
string comments;
|
|
DataTable table = new DataTable();
|
|
string selectString = "SELECT Comment.Comment FROM Comment WHERE Comment.FK_DateID = @dateString";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.AddWithValue("@dateString", dateId);
|
|
table = ExecuteQuery(command);
|
|
|
|
if (table.Rows.Count == 0)
|
|
{
|
|
DateTime time = Convert.ToDateTime(RetrieveDateStringByID(dateId));
|
|
comments = "No comments to display for " + time.ToLongDateString() + ".";
|
|
}
|
|
else
|
|
{
|
|
comments = table.Rows[0][0].ToString();
|
|
}
|
|
|
|
return comments;
|
|
}
|
|
|
|
public string RetrieveDateStringByID(string dateID)
|
|
{
|
|
string dateString = "";
|
|
DataTable table = new DataTable();
|
|
string selectString = "SELECT WeekEnding.EndOfWeekDate FROM WeekEnding WHERE WeekEnding.ID = dateID ORDER BY EndOfWeekDate ASC";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.AddWithValue("dateID", dateID);
|
|
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
|
|
command.Connection = connection;
|
|
connection.Open();
|
|
adapter.Fill(table);
|
|
connection.Close();
|
|
table = ExecuteQuery(command);
|
|
dateString = table.Rows[0][0].ToString();
|
|
|
|
return dateString;
|
|
}
|
|
|
|
public string RetrieveGroupIDByString(string groupName)
|
|
{
|
|
string groupID = "";
|
|
string sqlCommand = "SELECT GroupCategory.ID FROM GroupCategory WHERE GroupCategory.GroupDescription = groupString";
|
|
var connection = new OleDbConnection(gConnectionString);
|
|
var command = new OleDbCommand(sqlCommand, connection);
|
|
command.Parameters.AddWithValue("groupString", groupName);
|
|
try
|
|
{
|
|
var reader = command.ExecuteReader();
|
|
|
|
while (reader != null && reader.Read())
|
|
{
|
|
groupID = reader[0].ToString();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
return "";
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return groupID;
|
|
}
|
|
|
|
public List<string> ReturnGroupNameList()
|
|
{
|
|
string sqlCommand = "SELECT GroupCategory.GroupDescription FROM GroupCategory WHERE NOT GroupDescription = 'HeaderRow' AND NOT GroupDescription = 'MemberRow'";
|
|
var connection = new OleDbConnection(gConnectionString);
|
|
var command = new OleDbCommand(sqlCommand, connection);
|
|
List<string> groupNames = new List<string>();
|
|
try
|
|
{
|
|
connection.Open();
|
|
var reader = command.ExecuteReader();
|
|
|
|
while (reader != null && reader.Read())
|
|
{
|
|
groupNames.Add(reader[0].ToString());
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
return groupNames;
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return groupNames;
|
|
}
|
|
|
|
public AutoCompleteStringCollection GetAdItemsSuggestionList()
|
|
{
|
|
AutoCompleteStringCollection autoCompleteList = new AutoCompleteStringCollection();
|
|
var command = "SELECT AdItem.AdItem FROM AdItem ORDER BY AdItem.AdItem ASC";
|
|
|
|
DataTable table = new DataTable();
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand commandObject = new OleDbCommand(command, connection);
|
|
commandObject.Connection.Open();
|
|
OleDbDataAdapter a = new OleDbDataAdapter(commandObject);
|
|
a.Fill(table);
|
|
commandObject.Connection.Close();
|
|
int i = 0;
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
if (row[0].ToString() != "")
|
|
{
|
|
autoCompleteList.Add(row[0].ToString());
|
|
}
|
|
|
|
i++;
|
|
}
|
|
return autoCompleteList;
|
|
}
|
|
|
|
public AutoCompleteStringCollection GetSupplierSuggestionList()
|
|
{
|
|
AutoCompleteStringCollection autoCompleteList = new AutoCompleteStringCollection();
|
|
string command = "SELECT SupplierName FROM Supplier ORDER BY SupplierName ASC";
|
|
|
|
DataTable table = new DataTable();
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand commandObject = new OleDbCommand(command, connection);
|
|
commandObject.Connection.Open();
|
|
OleDbDataAdapter a = new OleDbDataAdapter(commandObject);
|
|
a.Fill(table);
|
|
commandObject.Connection.Close();
|
|
int i = 0;
|
|
foreach (DataRow row in table.Rows)
|
|
{
|
|
if (row[0].ToString() != "")
|
|
{
|
|
autoCompleteList.Add(row[0].ToString());
|
|
}
|
|
|
|
i++;
|
|
}
|
|
return autoCompleteList;
|
|
}
|
|
public DataTable ReturnProjectionsTable(string dateID)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
string sqlCommand = "SELECT AdItem.AdItem, APC.ProjectionSold, APC.ProjectionSalePrice, APC.ProjectionTotalSales, APC.ProjectionCost, APC.ProjectionProfitReturn, APC.ProjectionTotalProfitReturn, FK_GroupID FROM (AdItem INNER JOIN APC ON AdItem.ID = APC.FK_AdItemID) WHERE FK_DateID = DateID";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(sqlCommand, connection);
|
|
command.Parameters.Add(new OleDbParameter("DateID", dateID));
|
|
dataTable = ExecuteQuery(command);
|
|
|
|
return dataTable;
|
|
}
|
|
|
|
public DataTable ReturnActualSales(string dateID)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
string selectString = "SELECT AdItem.AdItem, APC.ActualSold, APC.ActualSalePrice, APC.ActualTotalSales, APC.ActualCost, APC.ActualProfitReturn, APC.ActualTotalProfitReturn, FK_GroupID FROM (AdItem INNER JOIN APC ON AdItem.ID = APC.FK_AdItemID) WHERE FK_DateID = DateID";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.Add(new OleDbParameter("DateID", dateID));
|
|
dataTable = ExecuteQuery(command);
|
|
return dataTable;
|
|
}
|
|
|
|
public DataTable ReturnInventoryTable(string dateID)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
string selectString = "SELECT AdItem.AdItem, APC.BeginingInventory, APC.Received, APC.TotalInventory, APC.EndingInventory, FK_GroupID FROM (AdItem INNER JOIN APC ON AdItem.ID = APC.FK_AdItemID) WHERE FK_DateID = DateID";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.Add(new OleDbParameter("DateID", dateID));
|
|
dataTable = ExecuteQuery(command);
|
|
return dataTable;
|
|
}
|
|
|
|
public DataTable ReturnGroupIDTable(string dateID)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
string[] groupNameCollection;
|
|
string selectString = "SELECT APC.FK_GroupID FROM APC WHERE FK_DateID = DateID";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.Add(new OleDbParameter("DateID", dateID));
|
|
|
|
try
|
|
{
|
|
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return dataTable;
|
|
}
|
|
|
|
public string ReturnGroupNameFromGroupId(string id)
|
|
{
|
|
string name = "";
|
|
string selectString = "SELECT GroupDescription FROM GroupCategory WHERE ID = ?";
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
OleDbCommand command = new OleDbCommand(selectString, connection);
|
|
command.Parameters.AddWithValue("GroupID", id);
|
|
|
|
try
|
|
{
|
|
connection.Open();
|
|
OleDbDataReader reader = command.ExecuteReader();
|
|
while (reader != null && reader.Read())
|
|
{
|
|
name = reader[0].ToString();
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return name;
|
|
}
|
|
|
|
public DataTable ReturnInvoiceTable(string dateID)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
OleDbCommand oleDbCommand = new OleDbCommand();
|
|
oleDbCommand.CommandText = "SELECT FK_Supplier, InvoiceNumber, InvoiceDate, InvoiceNetAmountAtCost, InvoiceNetAmount, InvoiceNote FROM Invoice WHERE FK_DateID = ?";
|
|
OleDbConnection oleDbConnection = new OleDbConnection(gConnectionString);
|
|
oleDbCommand.Connection = oleDbConnection;
|
|
oleDbCommand.Parameters.AddWithValue("DateID", dateID);
|
|
try
|
|
{
|
|
OleDbDataAdapter adapater = new OleDbDataAdapter(oleDbCommand);
|
|
int index = 0;
|
|
oleDbCommand.Connection.Open();
|
|
adapater.Fill(dataTable);
|
|
string[] supplierNames = new string[dataTable.Rows.Count];
|
|
foreach (DataRow row in dataTable.Rows)
|
|
{
|
|
supplierNames[index] = RetrieveSupplierNameByID(row[0].ToString());
|
|
index++;
|
|
}
|
|
dataTable.Columns.RemoveAt(0);
|
|
dataTable.Columns.Add("SupplierName");
|
|
dataTable.Columns[5].SetOrdinal(0);
|
|
dataTable.Columns[0].DataType = typeof (string);
|
|
index = 0;
|
|
foreach (string name in supplierNames)
|
|
{
|
|
dataTable.Rows[index][0] = name;
|
|
index++;
|
|
}
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
MessageBox.Show(e.Message);
|
|
}
|
|
finally
|
|
{
|
|
oleDbCommand.Connection.Close();
|
|
}
|
|
|
|
return dataTable;
|
|
}
|
|
/// <summary>
|
|
/// Infrastructure
|
|
/// </summary>
|
|
/// <param name="supplierID"></param>
|
|
/// <returns></returns>
|
|
private string RetrieveSupplierNameByID(string supplierID)
|
|
{
|
|
string supplierName = "";
|
|
OleDbCommand oleDbCommand = new OleDbCommand();
|
|
oleDbCommand.CommandText = "SELECT SupplierName FROM Supplier WHERE ID = ?";
|
|
oleDbCommand.Parameters.AddWithValue("ID", supplierID);
|
|
OleDbConnection oleDbConnection = new OleDbConnection(gConnectionString);
|
|
oleDbCommand.Connection = oleDbConnection;
|
|
oleDbCommand.Connection.Open();
|
|
OleDbDataReader reader = oleDbCommand.ExecuteReader();
|
|
|
|
while (reader != null && reader.Read())
|
|
{
|
|
supplierName = reader[0].ToString();
|
|
}
|
|
oleDbConnection.Close();
|
|
return supplierName;
|
|
}
|
|
|
|
public DataTable ReturnWeeklySalesFromDateID(string dateID)
|
|
{
|
|
DataTable dataTable = new DataTable();
|
|
OleDbCommand oleDbCommand = new OleDbCommand();
|
|
oleDbCommand.CommandText = "SELECT Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, TotalSales FROM WeeklySales WHERE FK_DateID = ?";
|
|
oleDbCommand.Parameters.AddWithValue("dateID", dateID);
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
oleDbCommand.Connection = connection;
|
|
OleDbDataAdapter a = new OleDbDataAdapter(oleDbCommand);
|
|
try
|
|
{
|
|
oleDbCommand.Connection.Open();
|
|
a.Fill(dataTable);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
console.WriteToLog(LogConsole.Level.Error, e.Message);
|
|
}
|
|
finally
|
|
{
|
|
|
|
oleDbCommand.Connection.Close();
|
|
}
|
|
|
|
return dataTable;
|
|
}
|
|
|
|
public DataTable ReturnWeekEndingTable()
|
|
{
|
|
var weekendingDatesTeable = new DataTable();
|
|
var oleDbCommand = new OleDbCommand {CommandText = "SELECT EndOfWeekDate FROM WeekEnding ORDER BY EndOfWeekDate ASC"};
|
|
var connection = new OleDbConnection(gConnectionString);
|
|
oleDbCommand.Connection = connection;
|
|
|
|
try
|
|
{
|
|
var a = new OleDbDataAdapter(oleDbCommand);
|
|
a.Fill(weekendingDatesTeable);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return weekendingDatesTeable;
|
|
|
|
}
|
|
|
|
public List<string> ReturnUniqueYearsList()
|
|
{
|
|
var datesList = new List<string>();
|
|
var uniqueDateSelection = new OleDbCommand();
|
|
uniqueDateSelection.CommandText =
|
|
"SELECT Distinct YEAR(EndOfWeekDate) FROM WeekEnding";
|
|
var connection = new OleDbConnection(gConnectionString);
|
|
uniqueDateSelection.Connection = connection;
|
|
|
|
try
|
|
{
|
|
connection.Open();
|
|
var reader = uniqueDateSelection.ExecuteReader();
|
|
while (reader != null && reader.Read())
|
|
{
|
|
datesList.Add(reader[0].ToString());
|
|
}
|
|
}
|
|
catch (OleDbException e)
|
|
{
|
|
console.WriteToLog(LogConsole.Level.Error, "An error has occurred trying to retrieve the list of dates.");
|
|
console.WriteToLog(LogConsole.Level.Debug, e.Message);
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return datesList;
|
|
}
|
|
/// <summary>
|
|
/// Returns a list of months available based on the year supplied.
|
|
/// </summary>
|
|
/// <param name="year"></param>
|
|
/// <returns></returns>
|
|
public List<string> ReturnUniqueMonthsList(string year)
|
|
{
|
|
var datesList = new List<string>();
|
|
var uniqueDateSelection = new OleDbCommand();
|
|
uniqueDateSelection.CommandText =
|
|
"SELECT Distinct MONTH(EndOfWeekDate) FROM WeekEnding WHERE YEAR(EndOfWeekDate) = YEAR(#" + year + "#)";
|
|
var connection = new OleDbConnection(gConnectionString);
|
|
uniqueDateSelection.Connection = connection;
|
|
|
|
try
|
|
{
|
|
connection.Open();
|
|
var reader = uniqueDateSelection.ExecuteReader();
|
|
while (reader != null && reader.Read())
|
|
{
|
|
datesList.Add(reader[0].ToString());
|
|
}
|
|
|
|
|
|
}
|
|
catch (OleDbException e)
|
|
{
|
|
console.WriteToLog(LogConsole.Level.Error, "An error has occurred trying to retrieve the list of dates.");
|
|
console.WriteToLog(LogConsole.Level.Debug, e.Message);
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return datesList;
|
|
}
|
|
|
|
public DateTime ReturnMostRecentDateString()
|
|
{
|
|
DateTime dateString = new DateTime();
|
|
var oleDbCommand = new OleDbCommand();
|
|
oleDbCommand.CommandText = "SELECT WeekEnding.EndOfWeekDate FROM WeekEnding WHERE WeekEnding.EndOfWeekDate = (SELECT MAX(WeekEnding.EndOfWeekDate) FROM WeekEnding)";
|
|
var connection = new OleDbConnection(gConnectionString);
|
|
oleDbCommand.Connection = connection;
|
|
|
|
try
|
|
{
|
|
connection.Open();
|
|
var reader = oleDbCommand.ExecuteReader();
|
|
while (reader != null && reader.Read())
|
|
{
|
|
dateString = (DateTime) reader[0];
|
|
}
|
|
}
|
|
catch (OleDbException e)
|
|
{
|
|
console.WriteToLog(LogConsole.Level.Error, "An error has occurred trying to retrieve the most recent date string.");
|
|
console.WriteToLog(LogConsole.Level.Debug, e.Message);
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return dateString;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Pattern Must Be: MM/*/YYYY
|
|
/// </summary>
|
|
/// <param name="dateString"></param>
|
|
/// <returns></returns>
|
|
public DataTable ReturnDateStringsLike(string dateString)
|
|
{
|
|
//Grabbing the year supplied and the last years dates:
|
|
//SELECT EndOfWeekDate FROM WeekEnding WHERE YEAR(DATEADD('yyyy', -1, #" + dateString + "#)) = YEAR(EndOFWeekDate) OR YEAR(#" + dateString + "#) = YEAR(EndOfWeekDate) ORDER BY EndOfWeekDate ASC
|
|
DataTable table = new DataTable();
|
|
OleDbCommand oleDbCommand = new OleDbCommand();
|
|
oleDbCommand.CommandText = "SELECT EndOfWeekDate FROM WeekEnding WHERE YEAR(#" + dateString + "#) = YEAR(EndOFWeekDate) ORDER BY EndOfWeekDate ASC";
|
|
//oleDbCommand.Parameters.AddWithValue("datePattern", dateString);
|
|
OleDbConnection connection = new OleDbConnection(gConnectionString);
|
|
oleDbCommand.Connection = connection;
|
|
connection.Open();
|
|
try
|
|
{
|
|
OleDbDataAdapter a = new OleDbDataAdapter(oleDbCommand);
|
|
a.Fill(table);
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
|
|
}
|
|
finally
|
|
{
|
|
connection.Close();
|
|
}
|
|
|
|
return table;
|
|
}
|
|
}
|
|
}
|