/// <summary>
/// Creates new invoice and adds it to the invoice or recurring invoice table
/// </summary>
/// <param name="conn"></param>
/// <param name="transactionType"></param>
/// <param name="md"></param>
/// <param name="amount"></param>
/// <param name="customerID"></param>
/// <param name="addEntry"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private bool InsertInvoice(MySqlConnection conn, string transactionType, MessageDialog md, double amount, int customerID, bool addEntry, string tableName)
{
string command = "SELECT accID,amount FROM plutos.accounts WHERE (compid=" + App.compID + " AND name LIKE '" + Income_Account_ComboBox.SelectedItem.ToString() + "')";
string errorMessage = "Could not select account data. Please check your connection";
string transactiondate = datePicker.Date.ToString("dd.MM.yyyy");
List <string> accountdata = MySQLCommands.SELECTCOMMALIST(command, errorMessage, conn);
command = "INSERT INTO " + tableName + " (accid,amount,bookdate,transactiondate,custID) VALUES(@accid,@amount,@bookdate,@transactiondate,@custID)";
MySqlCommand newInvoice = new MySqlCommand(command, conn);
newInvoice.Parameters.AddWithValue("@accid", accountdata[0].Split(';')[0]);
newInvoice.Parameters.AddWithValue("@amount", amount);
newInvoice.Parameters.AddWithValue("@bookdate", HelperMethods.GetDate());
newInvoice.Parameters.AddWithValue("@transactiondate", transactiondate);
newInvoice.Parameters.AddWithValue("@custID", customerID);
if (newInvoice.ExecuteNonQuery() == 0)
{
return(false);
}
if (addEntry)
{
return(NewHistoryEntry(conn, Convert.ToDouble(accountdata[0].Split(';')[1]), Convert.ToInt32(accountdata[0].Split(';')[0]), HelperMethods.GetDate(), transactiondate, customerID)); // amount,accid,bookdate,transactiondate,customerID
}
return(true);
}
/// <summary>
/// Returns customer id by searching for the selected customer name
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
private int GetCustomerID(MySqlConnection conn)
{
string firstName = customerBox.SelectedItem.ToString().Split(' ')[0];
string lastName = customerBox.SelectedItem.ToString().Split(' ')[1];
string command = "SELECT custID from plutos.customers WHERE firstName LIKE '" + firstName + "' AND lastName LIKE '" + lastName + "'";
string errorMessage = "Could not find customer";
return(Convert.ToInt32(MySQLCommands.SELECTSINGLELIST(command, errorMessage, conn)[0]));
}
private async System.Threading.Tasks.Task <bool> LoadRecurringIncomes(MySqlConnection conn)
{
string command = $"SELECT amount,transactiondate,custID,account from plutos.sales WHERE transactiondate LIKE '{HelperMethods.GetDate()}' AND compid= {App.compID}";
string errorMessage = "Could not load recurring incomes";
List <string> dueIncomes = MySQLCommands.SELECTCOMMALIST(command, errorMessage, conn);
if (dueIncomes != null)
{
foreach (string dueincome in dueIncomes)
{
double amount = Convert.ToDouble(dueincome.Split(';')[0]);
DateTime bookDate = DateTime.ParseExact(dueincome.Split(';')[1], "dd.MM.yyyy", null);
string customerID = dueincome.Split(';')[2];
string accountName = dueincome.Split(';')[3];
MySqlCommand selectAccount = new MySqlCommand("SELECT name,amount FROM plutos.accounts WHERE name LIKE '" + accountName, conn);
MySqlDataReader readAccount = selectAccount.ExecuteReader();
while (readAccount.Read())
{
amount += Convert.ToDouble(readAccount[1]);
}
readAccount.Close();
// Update amount
MySqlCommand updateAmount = new MySqlCommand("UPDATE plutos.accounts SET amount=@amount WHERE name LIKE '" + accountName + "'", conn);
updateAmount.Parameters.AddWithValue("@amount", amount);
if (updateAmount.ExecuteNonQuery() == 0)
{
return(false);
}
// Set new date
MySqlCommand setNewDate = new MySqlCommand("UPDATE plutos.sales SET transactiondate=@transactiondate WHERE name LIKE '" + accountName + "'", conn);
// Calculate difference and get next transaction date
int year = DateTime.Now.Year; //Or any year you want
DateTime newBookDate = new DateTime(bookDate.Year, bookDate.Month, bookDate.Day).AddDays(30); // Add 30 days so we get our next transaction date
string newDate = newBookDate.ToString("dd.MM.yyyy"); // The date in requested format
setNewDate.Parameters.AddWithValue("@transactiondate", newDate);
if (setNewDate.ExecuteNonQuery() == 0)
{
return(false);
}
// Create new HistoryEntry
}
}
return(true);
}
public static List <string> GetNames(MySqlConnection conn)
{
string command = "SELECT firstName,lastName from plutos.customers WHERE compid=" + App.compID;
string errorMessage = "Could not load customers";
List <string> customerNames = MySQLCommands.SELECTCOMMALIST(command, errorMessage, conn);
List <string> fullNames = new List <string>();
if (customerNames != null)
{
foreach (string value in customerNames)
{
fullNames.Add(value.Split(';')[0] + " " + value.Split(';')[1]);
}
}
return(fullNames);
}
/// <summary>
/// Checks if entered email has already been registered in database
/// </summary>
private async System.Threading.Tasks.Task <bool> CheckExistingEmails(MySqlConnection conn, Windows.UI.Popups.MessageDialog md)
{
string command = "SELECT email from plutos.users WHERE email LIKE '" + EmailTB.Text + "'";
string errorMessage = "Could not check if email has already been registered";
List <string> eMails = MySQLCommands.SELECTSINGLELIST(command, errorMessage, conn);
foreach (string eMail in eMails)
{
if (eMail == EmailTB.Text)
{
md.Content = "Email already exists";
await md.ShowAsync();
return(true);
}
}
return(false);
}
public static ObservableCollection <Customer> CustomerList(MySqlConnection conn)
{
string command = "SELECT firstName,lastName from plutos.customers WHERE compid=" + App.compID;
string errorMessage = "Could not load customers";
List <string> customerNames = MySQLCommands.SELECTCOMMALIST(command, errorMessage, conn);
Customer customer = null;
ObservableCollection <Customer> customers = new ObservableCollection <Customer>(); // We need a ObservableCollection of Customers because we you can not bind a list
if (customerNames != null) // Check if we have customers
{
foreach (string value in customerNames)
{
customer = new Customer(value.Split(';')[0], value.Split(';')[1]); // Set firstName and lastName
customers.Add(customer);
}
}
return(customers);
}
public static ObservableCollection <HistoryEntry> HistoryEntryList(MySqlConnection conn)
{
string command = "SELECT sales.amount, sales.bookdate, sales.transactiondate, customers.firstName,customers.lastName, accounts.name from plutos.sales " +
"Inner Join plutos.accounts ON sales.accid = accounts.accid " +
"Inner Join plutos.customers ON sales.custid = customers.custid " +
"WHERE accounts.compid=" + App.compID;
string errorMessage = "Could not load HistoryEntrys";
List <string> HistoryEntryNames = MySQLCommands.SELECTCOMMALIST(command, errorMessage, conn);
ObservableCollection <HistoryEntry> HistoryEntrys = new ObservableCollection <HistoryEntry>(); // We need a ObservableCollection of HistoryEntrys because we you can not bind a list
if (HistoryEntryNames != null) // Check if we have HistoryEntrys
{
foreach (string value in HistoryEntryNames)
{
HistoryEntrys.Add(new HistoryEntry {
amount = Convert.ToDouble(value.Split(';')[0]), bookdate = value.Split(';')[1], transactiondate = value.Split(';')[2], customer = value.Split(';')[3] + " " + value.Split(';')[4], account = value.Split(';')[5]
}); // Set firstName and lastName
}
}
return(HistoryEntrys);
}
/// <summary>
/// Adds new sale to same named table
/// </summary>
/// <param name="conn"></param>
/// <param name="transactionType"></param>
/// <param name="md"></param>
/// <param name="amount"></param>
/// <param name="customerID"></param>
/// <returns></returns>
private bool NewSale(MySqlConnection conn, string transactionType, MessageDialog md, double amount, int customerID)
{
string command = "SELECT accID,amount FROM plutos.accounts WHERE (compid=" + App.compID + " AND name LIKE '" + Income_Account_ComboBox.SelectedItem.ToString() + "')";
string errorMessage = "Could not select account data. Please check your connection";
List <string> values = MySQLCommands.SELECTCOMMALIST(command, errorMessage, conn); // get the name and amount of th accounts
if (transactionBtn.Tag.ToString() == "Income") // Check which transaction we have here
{
amount += Convert.ToDouble(values[0].Split(';')[1]); // Add existing account amount to entered amount
}
else if (transactionBtn.Tag.ToString() == "Expense")
{
amount -= Convert.ToDouble(values[0].Split(';')[1]);
}
int accID = Convert.ToInt32(values[0].Split(';')[0]);
if (!SetNewAmount(conn, amount, accID))
{
return(false);
}
string bookdate = HelperMethods.GetDate();
string transactiondate = bookdate;
string customerName = "No customer";
if (customerBox.SelectedItem != null)
{
customerName = customerBox.SelectedItem.ToString();
}
entrys_.Add(new HistoryEntry {
customer = customerBox.SelectedItem.ToString(), amount = amount, bookdate = HelperMethods.GetDate(), account = Income_Account_ComboBox.SelectedItem.ToString()
});
return(NewHistoryEntry(conn, Convert.ToDouble(amountTxtBx.Text), Convert.ToInt32(values[0].Split(';')[0]), HelperMethods.GetDate(), transactiondate, customerID)); // amount,accid,bookdate,transactiondate,customerID
}
