public KassaItem[] GetAllItems()
{
OleDbCommand cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "SELECT * FROM Kassa;";
cmd.Connection = connection;
OleDbDataReader dr = cmd.ExecuteReader();
List <KassaItem> cassaItems = new List <KassaItem>();
while (dr.Read())
{
DateTime date = Convert.ToDateTime(dr[1]);
DateTime time = Convert.ToDateTime(dr[2]);
DateTime dateTime = date.Date + time.TimeOfDay;
int worker = int.Parse(dr[3].ToString());
decimal total = decimal.Parse(dr[4].ToString());
bool inKassa = (bool)dr[5];
string desc = dr[6].ToString();
KassaItem item = new KassaItem(dateTime, worker, total, inKassa, desc);
cassaItems.Add(item);
}
connection.Close();
return(cassaItems.ToArray());
}
public KassaItem[] GetKassaItemRange(DateTime day1, DateTime day2)
{
OleDbCommand cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "SELECT * FROM Kassa WHERE (Date BETWEEN @startDay AND @endDay );";
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@startDay", day1.Date);
cmd.Parameters.AddWithValue("@endDay", day2.Date);
OleDbDataReader dr = cmd.ExecuteReader();
List <KassaItem> cassaItems = new List <KassaItem>();
while (dr.Read())
{
DateTime date = Convert.ToDateTime(dr[1]);
DateTime time = Convert.ToDateTime(dr[2]);
DateTime dateTime = date.Date + time.TimeOfDay;
int worker = int.Parse(dr[3].ToString());
decimal total = decimal.Parse(dr[4].ToString());
bool inKassa = (bool)dr[5];
string desc = dr[6].ToString();
KassaItem item = new KassaItem(dateTime, worker, total, inKassa, desc);
cassaItems.Add(item);
}
connection.Close();
return(cassaItems.ToArray());
}
private void btnOpslaan_Click(object sender, EventArgs e)
{
decimal total = 0;
string Desc = "";
if (rbKassa.Checked)
{
Desc += "Dag Kassa";
total += cent1 * num1cent.Value;
total += cent2 * num2cent.Value;
total += cent5 * num5cent.Value;
total += cent10 * num10cent.Value;
total += cent20 * num20cent.Value;
total += cent50 * num50cent.Value;
total += euro1 * num1euro.Value;
total += euro2 * num2euro.Value;
total += euro5 * num5euro.Value;
total += euro10 * num10euro.Value;
total += euro20 * num20euro.Value;
total += euro50 * num50euro.Value;
total += euro100 * num100euro.Value;
total += euro200 * num200euro.Value;
total += euro500 * num500euro.Value;
}
else if (rbAfromen.Checked)
{
if (tbTotal.Text == "")
{
MessageBox.Show("Total can't be empty");
return;
}
Desc += "Afromen ";
total = decimal.Parse(tbTotal.Text.Replace(".", ","));
Desc += tbDesc.Text;
}
else if (rbBon.Checked)
{
if (tbTotal.Text == "")
{
MessageBox.Show("Total can't be empty");
return;
}
Desc += "Bon ";
total = decimal.Parse(tbTotal.Text.Replace(".", ","));
Desc += tbDesc.Text;
}
KassaItem cassaItem = new KassaItem(DateTime.Now, Convert.ToInt32(cmbWorker.Text.Split('-')[0]), total, rbKassa.Checked, Desc);
KassaManagement.KassaAddItem(cassaItem);
FormFill();
MessageBox.Show("Saved");
}
public void KassaAddItem(KassaItem cassaItem)
{
if (cassaItem.InKassa)
{
DBaccess.UpdateDayKassa(cassaItem);
}
else
{
DBaccess.AddKassaItem(cassaItem);
}
}
public void AddKassaItem(KassaItem item)
{
OleDbCommand cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "INSERT INTO Kassa(`Date`,`Time`,`Worker`,`Total`,`InKassa`,`Description`) VALUES( @date , @time , @worker , @total , @inKassa , @desc );";
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@date", item.DateTime.Date);
cmd.Parameters.AddWithValue("@time", item.DateTime.TimeOfDay);
cmd.Parameters.AddWithValue("@worker", item.Worker);
cmd.Parameters.AddWithValue("@total", item.Total.ToString().Replace(".", ","));
cmd.Parameters.AddWithValue("@inKassa", item.InKassa);
cmd.Parameters.AddWithValue("@desc", item.Description);
cmd.ExecuteNonQuery();
connection.Close();
}
public void UpdateDayKassa(KassaItem cassaItem)
{
OleDbCommand cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "SELECT * FROM Kassa WHERE `Date` = @date AND `InKassa` = @inKassa;";
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@date", cassaItem.DateTime.Date);
cmd.Parameters.AddWithValue("@inKassa", cassaItem.InKassa);
OleDbDataReader dr = cmd.ExecuteReader();
bool Found = false;
if (dr.HasRows)
{
Found = true;
}
connection.Close();
if (Found)
{
cmd = connection.CreateCommand();
connection.Open();
cmd.CommandText = "UPDATE `Kassa` SET `Worker` = @worker ,`Total` = @total , `Time` = @time,`Description` = @desc WHERE `Date` = @date AND `InKassa` = @inKassa;";
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@worker", cassaItem.Worker);
cmd.Parameters.AddWithValue("@total", cassaItem.Total.ToString().Replace(".", ","));
cmd.Parameters.AddWithValue("@time", cassaItem.DateTime.TimeOfDay);
cmd.Parameters.AddWithValue("@desc", cassaItem.Description);
cmd.Parameters.AddWithValue("@date", cassaItem.DateTime.Date);
cmd.Parameters.AddWithValue("@inKassa", cassaItem.InKassa);
cmd.ExecuteNonQuery();
connection.Close();
}
else
{
AddKassaItem(cassaItem);
}
}