public static List<PO> GetPOs(int clientID)
{
if(theConnection.ConnectionString == string.Empty)
{
theConnection.ConnectionString = connectionString;
}
List<PO> thePOs = new List<PO>();
PO thePO;
List<POTransaction> transactions = new List<POTransaction>();
POTransaction theTransaction;
try
{
commandString= "select * from PO_Transactions";
theConnection.Open();
theCommand.Connection = theConnection;
theCommand.CommandText = commandString;
reader = theCommand.ExecuteReader();
while(reader.Read())
{
theTransaction = new POTransaction();
theTransaction.POID = int.Parse(reader["poID"].ToString());
theTransaction.Date = DateTime.Parse(reader["date"].ToString());
theTransaction.Amount = decimal.Parse(reader["amount"].ToString());
transactions.Add(theTransaction);
}
reader.Close();
commandString = "select * from POs where clientID = " + clientID;
theCommand.CommandText = commandString;
reader = theCommand.ExecuteReader();
while (reader.Read())
{
thePO = new PO();
thePO.Amount = decimal.Parse(reader["amount"].ToString());
thePO.ClientID = int.Parse(reader["clientID"].ToString());
thePO.ID = int.Parse(reader["poID"].ToString());
thePO.PONumber = reader["poMTNumber"].ToString();
thePO.Transactions = new List<POTransaction>();
foreach(POTransaction tempTransaction in transactions)
{
if(tempTransaction.POID == thePO.ID)
{
thePO.Transactions.Add(tempTransaction);
}
}
thePOs.Add(thePO);
}
return thePOs;
}
catch (MySqlException ex)
{
return new List<PO>();
}
finally
{
theConnection.Close();
}
}
public static bool UpdatePOTransaction(POTransaction theTransaction)
{
if(theConnection.ConnectionString == string.Empty)
{
theConnection.ConnectionString = connectionString;
}
try
{
theConnection.Open();
theCommand.Connection = theConnection;
commandString = "update PO_Transactions set amount = @amount,date = @date where poID = " + theTransaction.POID;
theCommand.CommandText = commandString;
theCommand.Prepare();
theCommand.Parameters.Clear();
theCommand.Parameters.AddWithValue("@date",theTransaction.Date);
theCommand.Parameters.AddWithValue("@amount",theTransaction.Amount);
theCommand.ExecuteNonQuery();
return true;
}
catch(MySqlException ex)
{
return false;
}
finally
{
theConnection.Close();
}
}
public static bool AddPOTransaction(POTransaction theTransaction)
{
if(theConnection.ConnectionString == string.Empty)
{
theConnection.ConnectionString = connectionString;
}
try
{
theConnection.Open();
theCommand.Connection = theConnection;
commandString = "insert into PO_Transactions (poID,amount,date) Values(@poID,@amount, @date)";
theCommand.CommandText = commandString;
theCommand.Prepare();
theCommand.Parameters.Clear();
theCommand.Parameters.AddWithValue("@poID",theTransaction.POID);
theCommand.Parameters.AddWithValue("@amount",theTransaction.Amount);
string day;
string month;
string date;
if(theTransaction.Date.Day<10)
{
day = "0" + theTransaction.Date.Day;
}
else
{
day = theTransaction.Date.Day.ToString();
}
if(theTransaction.Date.Month<10)
{
month = "0" + theTransaction.Date.Month;
}
else
{
month = theTransaction.Date.Month.ToString();
}
date = theTransaction.Date.Year + "-" + month + "-" + day;
theCommand.Parameters.AddWithValue("@date",theTransaction.Date);
theCommand.ExecuteNonQuery();
return true;
}
catch(MySqlException ex)
{
return false;
}
finally
{
theConnection.Close();
}
}
void BtnATransactionClick(object sender, EventArgs e)
{
if(btnATransaction.Text == "Add Transaction")
{
btnETransaction.Enabled = true;
dtpTDate.Visible = true;
lbxTDates.Visible = false;
btnETransaction.Text = "Cancel";
btnATransaction.Text = "Save";
lblTransactions.Text = "Date";
nudTAmount.Enabled = true;
}
else if(btnATransaction.Text == "Save")
{
dtpTDate.Visible = false;
btnATransaction.Text = "Add Transaction";
lblTransactions.Text = "Transactions";
btnETransaction.Text = "Edit Transaction";
nudTAmount.Enabled = false;
lbxTDates.Visible = true;
POTransaction theTrasaction = new POTransaction();
theTrasaction.Amount = nudTAmount.Value;
theTrasaction.Date = dtpTDate.Value;
theTrasaction.POID = pos[lbxPOs.SelectedIndex].ID;
pos[lbxPOs.SelectedIndex].Transactions.Add(theTrasaction);
if(!Model.AddPOTransaction(theTrasaction))
{
MessageBox.Show("Unable to add PO Transaction");
}
UpdateTransactionsList();
UpdatePOBalance();
}
else
{
dtpTDate.Visible = false;
btnATransaction.Text = "Add Transaction";
lblTransactions.Text = "Transactions";
btnETransaction.Text = "Edit Transaction";
nudTAmount.Enabled = false;
lbxTDates.Visible = true;
}
}