// TRANSACTION RELATED INTERFACE
public static void addTransaction(Transaction transaction)
{
//openConnection();
// TODO: adjust price of product
MySqlCommand command = l_DBConn.CreateCommand();
String query = null;
bool isNew = true;
if (transaction.getId() != null)
{
query = "SELECT * FROM transaction WHERE id='" + transaction.getId() + "'";
command.CommandText = query;
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
isNew = false;
}
reader.Close();
}
query = "REPLACE transaction(id, money_receive,money_change,date,total_price) VALUES('" +
transaction.getId() + "','" + transaction.getMoneyReceive() + "','" + transaction.getMoneyChange() +
"','" + transaction.getDate() + "','" + transaction.getTotalPrice() + "')";
command.CommandText = query;
command.ExecuteNonQuery();
var transactionId = command.LastInsertedId;
// add Transaction-Product relation if this is new transaction
if (isNew)
{
Dictionary<string, Dictionary<int, double>> shopping_bag = transaction.getShoppingBag();
for (int i = 0; i < shopping_bag.Count; i++)
{
string productId = shopping_bag.ElementAt(i).Key;
Dictionary<int, double> amountPrice = shopping_bag.ElementAt(i).Value;
query = "INSERT INTO product_transaction_relation VALUES('" +
productId + "','" + transactionId +
"','" + amountPrice.ElementAt(0).Key +
"','" + amountPrice.ElementAt(0).Value + "')";
command.CommandText = query;
command.ExecuteNonQuery();
// remove number in stock of product
removeProductNumberInStock(productId, amountPrice.ElementAt(i).Key);
// add number of product sold today
}
}
//closeConnection();
}
public static Transaction getTransaction(string transactionId)
{
//openConnection();
MySqlCommand command = l_DBConn.CreateCommand();
String query = null;
query = "SELECT * FROM transaction WHERE id='" + transactionId + "'";
command.CommandText = query;
MySqlDataReader reader = command.ExecuteReader();
Transaction transaction = null;
while (reader.Read())
{
double money_receive = 0;
try
{
money_receive = Double.Parse(reader.GetValue(1).ToString());
}
catch { };
double money_change = 0;
try
{
money_change = Double.Parse(reader.GetValue(2).ToString());
}
catch { };
string date = reader.GetValue(3).ToString();
string member_id = reader.GetValue(4).ToString();
double total_price = 0;
try
{
total_price = Double.Parse(reader.GetValue(5).ToString());
}
catch { };
transaction = new Transaction(transactionId, date, money_receive, money_change, total_price);
}
reader.Close();
query = "SELECT * FROM product_transaction_relation WHERE transaction_id='" + transactionId + "'";
command.CommandText = query;
reader = command.ExecuteReader();
while (reader.Read())
{
string product_id = reader.GetValue(0).ToString();
int amount = (int)Int64.Parse(reader.GetValue(2).ToString());
double total_price = Double.Parse(reader.GetValue(3).ToString());
transaction.insertProductIntoShoppingBag(product_id, amount, total_price);
}
reader.Close();
//closeConnection();
return transaction;
}
public static void readSerial()
{
while (true)
{
byte tmp = Convert.ToByte(_serialPort.ReadByte());
string binstr;
convertToBinary(out binstr, tmp);
Console.WriteLine("--Rec: " + binstr);
if (tmp == startSig && receiveStartSig == 0)
{
receiveStartSig = 1;
Console.WriteLine("Received start signal");
mode = 0;
idx = 0;
subtotal = 0.0;
//Create Transaction
tempTransaction= new Transaction();
}
else if (tmp == paySig)
{
Console.WriteLine("Received pay signal");
mode = 1;
idx = 0;
}
else if (tmp == endSig)
{
receiveStartSig = 0;
Console.WriteLine("Received end signal");
// send 10 + id of cash register to end communication
byte[] output = new byte[4];
output[0] = (byte)(cashRegisterID | (endPadding << 6));
_serialPort.Write(output, 0, 1);
receiveStartSig = 0;
// start comm with price tag
Product p = LocalDBInterface.getProduct(priceTagLCD);
string toSend;
if (p != null)
{
connectLCD(3855, "1100");
toSend = formatDisplay(p.getName(), p.getPrice(), p.getNumberInStock());
}
else
{
toSend = "No valid product";
int size = toSend.Length;
for (int i = 0; i < 32 - size; i++)
{
toSend += " ";
}
}
sendToLED(toSend);
// send signal to start comm with cash register again
output[0] = (byte)(cashRegisterID | (startPadding << 6));
_serialPort.Write(output, 0, 1);
receiveStartSig = 0;
mode = 2;
}
else if (tmp == contSig)
{
receiveStartSig = 1;
Console.WriteLine("Received cont signal");
mode = 3;
idx = 0;
}
if (idx < bufferSize)
{
int lowerNibble = tmp & 15;
int higherNibble = tmp >> 4;
if (lowerNibble < 10 && lowerNibble >= 0
&& higherNibble < 10 && higherNibble >= 0)
{
buffer[idx++] = tmp;
if (idx == 6) // get full data
{
// full buffer -> get enough data
// mode = 0: barcode and quantity
// mode = 1: payment and change
// mode = 2: payment and change
if (mode == 0)
{
// get barcode and quantity
convertBCDToNumber(new byte[] { buffer[0], buffer[1], buffer[2] }, out barcode);
convertBCDToNumber(new byte[] { buffer[3], buffer[4], buffer[5] }, out quantity);
Console.WriteLine("Received raw: " + buffer[0] + "." + buffer[1] + "." + buffer[2]
+ "." + buffer[3] + "." + buffer[4] + "." + buffer[5]);
Console.WriteLine("Received: Barcode: " + barcode + " Quantity: " + quantity);
//Get Price of product with barcode
Product temp2 = LocalDBInterface.getProduct(Convert.ToString(barcode));
double price = 0;
if (temp2 == null) price = 0;
else
{
price = temp2.getPrice();
// Add product to transaction
tempTransaction.insertProductIntoShoppingBag(Convert.ToString(barcode), quantity, price * quantity);
}
subtotal += quantity * price; // suppose 10 dollars per item
int tmpSubtt = (int)(subtotal * 100);
byte[] output = new byte[4];
convertToBCD(tmpSubtt, out output, dataPadding);
Console.Write("Sent: Subtotal: " + subtotal + " in BCD: ");
for (int j = 3; j >= 0; j--)
{
string tmpBCDByte;
convertToBinary(out tmpBCDByte, output[j]);
Console.Write(tmpBCDByte + ", ");
}
Console.WriteLine();
// send back subtotal
_serialPort.Write(output, 0, 4);
idx = 0;
}
else if (mode == 1 || mode == 2)// if (mode == 1 || mode == 2)
{
int tmpPay = 0;
// get payment and change
convertBCDToNumber(new byte[] { buffer[0], buffer[1], buffer[2] }, out tmpPay);
pay = ((double)tmpPay) / 100;
convertBCDToNumber(new byte[] { buffer[3], buffer[4], buffer[5] }, out tmpPay);
change = ((double)tmpPay) / 100;
Console.WriteLine("Received: pay: " + pay + " change: " + change);
{
// send ack
byte[] output = new byte[4];
output[0] = output[1] = output[2] = output[3] = 63;
Console.WriteLine("Ack sent");
_serialPort.Write(output, 0, 4);
idx = 0;
mode = 0;
//Add Transaction
DateTime time = DateTime.Now; // Use current time
string format = "yyyy-MM-dd"; // Use this format
Console.WriteLine(time.ToString(format));
if (tempTransaction != null)
{
tempTransaction.setDate(time.ToString(format));
tempTransaction.setTotalPrice(subtotal);
tempTransaction.setMoneyReceive(pay);
tempTransaction.setMoneyChange(change);
LocalDBInterface.addTransaction(tempTransaction);
Console.WriteLine("Transaction completed successfully");
}
else
{
Console.WriteLine("Transaction is null");
}
}
}
} // if idx == 6
//else if (mode == 2) // end signal, stop communication with cash register, start comm with price tag
//{
// // send 10 + id of cash register to end communication
// byte[] output = new byte[4];
// output[0] = (byte)(cashRegisterID | (endPadding << 6));
// _serialPort.Write(output, 0, 1);
// receiveStartSig = 0;
// // start comm with price tag
// connectLCD(3855, "1100");
// string toSend = formatDisplay("Panda Candy", 5.8, 100);
// sendToLED(toSend);
// //// send signal to start comm with cash register again
// //output[0] = (byte)(cashRegisterID | (startPadding << 6));
// //_serialPort.Write(output, 0, 1);
// //receiveStartSig = 0;
// mode = 0;
//}
else if (mode == 3 && idx == 3) // continue transaction
{
int transID;
convertBCDToNumber(new byte[] { buffer[0], buffer[1], buffer[2] }, out transID);
Console.WriteLine("Recieved: transID: " + transID);
int subtotalInt = 0;
tempTransaction = LocalDBInterface.getTransaction(Convert.ToString(transID));
if (tempTransaction == null)
subtotal = 0;
else
subtotal = tempTransaction.getTotalPrice();
subtotalInt = (int)(subtotal * 100);
byte[] output = new byte[4];
convertToBCD(subtotalInt, out output, dataPadding);
_serialPort.Write(output, 0, 4); // sent back subtotal
idx = 0;
mode = 0;
}
} // if data is bcd (data)
}
}
}
