public bool AddTransaction(Transaction tran)
{
OleDbCommand cmd = dbConn.CreateCommand();
if (tran.GetType() == typeof(Transaction))
{
cmd.CommandText = "INSERT INTO Investment(code, tran_date, amount, payment, description) VALUES ('" +
tran.code + "','" + tran.date.ToString() + "'," + tran.amount + "," + tran.payment + ",'" +
tran.description + "')";
}
else if (tran.GetType() == typeof(FutureTransaction))
{
FutureTransaction ftran = (FutureTransaction)tran;
cmd.CommandText = "INSERT INTO Future(code, contract_code, tran_date, amount, payment, description) VALUES ('" +
ftran.code + "','" + ftran.contractCode + "','" + ftran.date.ToString() + "'," + ftran.amount + "," +
ftran.payment + ",'" + ftran.description + "')";
}
else
throw new NotImplementedException("Unknow transaction");
cmd.ExecuteNonQuery();
return true;
}
private void btnAdd_Click(object sender, EventArgs e)
{
Transaction newTran = null;
if (cmbType.Text == "期貨/期權合約")
{
newTran = new FutureTransaction();
((FutureTransaction)newTran).contractCode = txtContractCode.Text;
}
else
newTran = new Transaction();
if (string.IsNullOrEmpty(txtCode.Text))
{
MessageBox.Show("編號輸入錯誤！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
newTran.code = txtCode.Text;
newTran.date = dtpDate.Value.Date;
if (!string.IsNullOrEmpty(txtAmount.Text) && !double.TryParse(txtAmount.Text, out newTran.amount))
{
MessageBox.Show("數量輸入錯誤！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!string.IsNullOrEmpty(txtPayment.Text) && !double.TryParse(txtPayment.Text, out newTran.payment))
{
MessageBox.Show("交易金額輸入錯誤！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
newTran.description = txtDescription.Text;
try
{
_profolioMgr.AddTransaction(newTran);
}
catch
{
MessageBox.Show("交易加入失敗！", "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
MessageBox.Show("交易加入成功", "加入交易", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public LinkedList<Transaction> GetAggregatedTransaction(Type tranType, string code, DateTime from, DateTime to)
{
OleDbCommand cmd = dbConn.CreateCommand();
LinkedList<Transaction> result = new LinkedList<Transaction>();
string filterStr = _MakeFilterString(code, from, to);
if (!string.IsNullOrEmpty(filterStr))
filterStr = " WHERE " + filterStr;
if (tranType == typeof(Transaction))
{
cmd.CommandText = "SELECT code, SUM(amount), SUM(payment) FROM Investment" + filterStr + " GROUP BY code" +
" ORDER BY code";
}
else if (tranType == typeof(FutureTransaction))
{
cmd.CommandText = "SELECT code, SUM(amount), SUM(payment), contract_code FROM Future" + filterStr + " GROUP BY code, contract_code" +
" ORDER BY code, contract_code";
}
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Transaction curTran;
if (tranType == typeof(Transaction))
curTran = new Transaction();
else
{
curTran = new FutureTransaction();
((FutureTransaction)curTran).contractCode = (string)reader["contract_code"];
}
curTran.code = (string)reader["code"];
curTran.date = to;
curTran.amount = (double)reader[1];
curTran.payment = (double)reader[2];
curTran.description = "綜合交易";
result.AddLast(curTran);
}
return result;
}
private void _DoQuery(object param)
{
object[] plist = (object[])param;
DateTime from = (DateTime)plist[0];
DateTime to = (DateTime)plist[1];
try
{
if (from != DateTime.MinValue)
{
LinkedList<Transaction> oldTran = _profolioMgr.GetAggregatedTransaction(typeof(Transaction), txtCode.Text, DateTime.MinValue, from.AddDays(-1));
foreach (Transaction t in oldTran)
{
if (t.amount != 0)
{
_prevBalance.Add(t.code, t);
Transaction curPostBalance = new Transaction();
curPostBalance.code = t.code;
curPostBalance.date = to;
curPostBalance.amount = -t.amount;
_postBalance.Add(t.code, curPostBalance);
}
}
LinkedList<Transaction> oldFutureTran = _profolioMgr.GetAggregatedTransaction(typeof(FutureTransaction), txtCode.Text, DateTime.MinValue, from.AddDays(-1));
foreach (Transaction ft in oldFutureTran)
{
if (ft.amount != 0)
{
SortedDictionary<string, FutureTransaction> curPrevBalanceFutureDict;
if (!_prevBalanceFuture.TryGetValue(ft.code, out curPrevBalanceFutureDict))
{
curPrevBalanceFutureDict = new SortedDictionary<string, FutureTransaction>();
_prevBalanceFuture.Add(ft.code, curPrevBalanceFutureDict);
}
curPrevBalanceFutureDict.Add(((FutureTransaction)ft).contractCode, (FutureTransaction)ft);
SortedDictionary<string, FutureTransaction> curPostBalanceFutureDict;
if (!_postBalanceFuture.TryGetValue(ft.code, out curPostBalanceFutureDict))
{
curPostBalanceFutureDict = new SortedDictionary<string, FutureTransaction>();
_postBalanceFuture.Add(ft.code, curPostBalanceFutureDict);
}
FutureTransaction curPostFutureBalance = new FutureTransaction();
curPostFutureBalance.code = ft.code;
curPostFutureBalance.contractCode = ((FutureTransaction)ft).contractCode;
curPostFutureBalance.date = to;
curPostFutureBalance.amount = -ft.amount;
curPostBalanceFutureDict.Add(((FutureTransaction)ft).contractCode, curPostFutureBalance);
}
}
}
_tran = _profolioMgr.GetTransaction(typeof(Transaction), txtCode.Text, from, to);
_futureTran = _profolioMgr.GetTransaction(typeof(FutureTransaction), txtCode.Text, from, to);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
_UpdateTransactionCB updateTranCB = new _UpdateTransactionCB(_UpdateDgvTransaction);
List<Transaction> updateTranCbData = new List<Transaction>();
//Process transaction data
{
LinkedListNode<Transaction> t = _tran.First, ft = _futureTran.First;
while (t != null || ft != null)
{
if (ft == null || (t != null && t.Value.date <= ft.Value.date))
{
updateTranCbData.Add(t.Value);
List<Transaction> curTransactionLst;
if (!_tranDct.TryGetValue(t.Value.code, out curTransactionLst))
{
curTransactionLst = new List<Transaction>();
_tranDct.Add(t.Value.code, curTransactionLst);
}
curTransactionLst.Add(t.Value);
t = t.Next;
}
else
{
updateTranCbData.Add(ft.Value);
SortedDictionary<string, List<FutureTransaction>> curDct;
if (!_tranFutureDct.TryGetValue(ft.Value.code, out curDct))
{
curDct = new SortedDictionary<string, List<FutureTransaction>>();
_tranFutureDct.Add(ft.Value.code, curDct);
}
List<FutureTransaction> curTransactionFutureLst;
if (!curDct.TryGetValue(((FutureTransaction)ft.Value).contractCode, out curTransactionFutureLst))
{
curTransactionFutureLst = new List<FutureTransaction>();
curDct.Add(((FutureTransaction)ft.Value).contractCode, curTransactionFutureLst);
}
curTransactionFutureLst.Add((FutureTransaction)ft.Value);
ft = ft.Next;
}
}
}
foreach (KeyValuePair<string, List<Transaction>> kwp in _tranDct)
{
Transaction curNetTran = new Transaction();
curNetTran.code = kwp.Key;
curNetTran.date = to;
curNetTran.amount = 0;
curNetTran.payment = 0;
curNetTran.description = "淨交易";
foreach (Transaction t in kwp.Value)
{
curNetTran.amount += t.amount;
curNetTran.payment += t.payment;
}
_netTran.Add(curNetTran.code, curNetTran);
if (curNetTran.amount != 0)
{
Transaction curPostBalance;
if (!_postBalance.TryGetValue(curNetTran.code, out curPostBalance))
{
curPostBalance = new Transaction();
curPostBalance.code = curNetTran.code;
curPostBalance.date = to;
curPostBalance.amount = 0;
_postBalance.Add(curPostBalance.code, curPostBalance);
}
curPostBalance.amount -= curNetTran.amount;
if (curPostBalance.amount == 0) _postBalance.Remove(curPostBalance.code);
}
}
foreach (KeyValuePair<string, SortedDictionary<string, List<FutureTransaction>>> kwp in _tranFutureDct)
{
FutureTransaction curNetTranFuture = new FutureTransaction();
curNetTranFuture.code = kwp.Key;
curNetTranFuture.contractCode = "淨期貨交易";
curNetTranFuture.date = to;
curNetTranFuture.amount = 0;
curNetTranFuture.payment = 0;
curNetTranFuture.description = "淨交易";
foreach (KeyValuePair<string, List<FutureTransaction>> kwp2 in kwp.Value)
{
double tmpAmount = 0;
double tmpPayment = 0;
foreach (FutureTransaction ft in kwp2.Value)
{
tmpAmount += ft.amount;
tmpPayment += ft.payment;
}
curNetTranFuture.amount += tmpAmount;
curNetTranFuture.payment += tmpPayment;
if (tmpAmount != 0)
{
SortedDictionary<string, FutureTransaction> curPostBalanceDct;
if (!_postBalanceFuture.TryGetValue(kwp.Key, out curPostBalanceDct))
{
curPostBalanceDct = new SortedDictionary<string, FutureTransaction>();
_postBalanceFuture.Add(kwp.Key, curPostBalanceDct);
}
FutureTransaction curPostBalance;
if (!curPostBalanceDct.TryGetValue(kwp2.Key, out curPostBalance))
{
curPostBalance = new FutureTransaction();
curPostBalance.code = kwp.Key;
curPostBalance.contractCode = kwp2.Key;
curPostBalance.date = to;
curPostBalance.amount = 0;
curPostBalanceDct.Add(kwp2.Key, curPostBalance);
}
curPostBalance.amount -= tmpAmount;
if (curPostBalance.amount == 0) curPostBalanceDct.Remove(kwp2.Key);
if (curPostBalanceDct.Count == 0) curPostBalanceDct.Remove(kwp.Key);
}
}
_netTranFurture.Add(curNetTranFuture.code, curNetTranFuture);
}
Invoke(updateTranCB, new object[] { updateTranCbData });
//Process return data
if (_prevBalance.Count > 0)
{
SortedDictionary<Pair<string, string>, double> asset = new SortedDictionary<Pair<string, string>, double>();
foreach (string code in _prevBalance.Keys)
asset.Add(new Pair<string, string>(code, "-"), 0);
try
{
_profolioMgr.GetHistorialPrice(asset, from.AddDays(-1));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
foreach (KeyValuePair<Pair<string, string>, double> kwp in asset)
{
Transaction curTran = _prevBalance[kwp.Key.First];
curTran.payment = -curTran.amount * kwp.Value;
}
}
if (_prevBalanceFuture.Count > 0)
{
SortedDictionary<Pair<string, string>, double> asset = new SortedDictionary<Pair<string, string>, double>();
foreach (KeyValuePair<string, SortedDictionary<string, FutureTransaction>> stock in _prevBalanceFuture)
{
foreach (string contractCode in stock.Value.Keys)
asset.Add(new Pair<string, string>(stock.Key, contractCode), 0);
}
try
{
_profolioMgr.GetHistorialPrice(asset, from.AddDays(-1));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
foreach (KeyValuePair<Pair<string, string>, double> kwp in asset)
{
Transaction curTran = _prevBalanceFuture[kwp.Key.First][kwp.Key.Second];
curTran.payment = -curTran.amount * kwp.Value;
}
}
if (_postBalance.Count > 0)
{
if (to < DateTime.Today)
{
SortedDictionary<Pair<string, string>, double> asset = new SortedDictionary<Pair<string, string>, double>();
foreach (string code in _postBalance.Keys)
asset.Add(new Pair<string, string>(code, "-"), 0);
try
{
_profolioMgr.GetHistorialPrice(asset, to);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
foreach (KeyValuePair<Pair<string, string>, double> kwp in asset)
{
Transaction curTran = _postBalance[kwp.Key.First];
curTran.payment = -curTran.amount * kwp.Value;
}
}
else
{
List<StockData> asset = new List<StockData>();
foreach (string code in _postBalance.Keys)
{
StockData stockData = new StockData();
stockData.code = code;
asset.Add(stockData);
}
try
{
_stockMgr.GetQuote(asset.ToArray());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
foreach (StockData stock in asset)
{
Transaction curTran = _postBalance[stock.code];
curTran.date = DateTime.Today;
curTran.payment = -curTran.amount * stock.close;
}
}
}
if (_postBalanceFuture.Count > 0)
{
SortedDictionary<Pair<string, string>, double> asset = new SortedDictionary<Pair<string, string>, double>();
foreach (KeyValuePair<string, SortedDictionary<string, FutureTransaction>> stock in _postBalanceFuture)
{
foreach (string contractCode in stock.Value.Keys)
asset.Add(new Pair<string, string>(stock.Key, contractCode), 0);
}
try
{
_profolioMgr.GetHistorialPrice(asset, to);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
foreach (KeyValuePair<Pair<string, string>, double> kwp in asset)
{
Transaction curTran = _postBalanceFuture[kwp.Key.First][kwp.Key.Second];
if (to >= DateTime.Today) curTran.date = DateTime.Today;
curTran.payment = -curTran.amount * kwp.Value;
}
}
Invoke(new _UpdateReturnCB(_UpdateReturn));
}
private void _AddTransactionToDgvTransaction(Transaction tran)
{
dgvTransaction.Rows.Add();
DataGridViewRow newRow = dgvTransaction.Rows[dgvTransaction.Rows.Count - 1];
newRow.Cells["colTCode"].Value = tran.code;
newRow.Cells["colTTranDate"].Value = tran.date.ToShortDateString();
newRow.Cells["colTAmount"].Value = tran.amount;
newRow.Cells["colTPayment"].Value = tran.payment;
newRow.Cells["colTDescription"].Value = tran.description;
if (tran.GetType() == typeof(FutureTransaction))
{
newRow.Cells["colTContractCode"].Value = ((FutureTransaction)tran).contractCode;
}
}
private void _AddTransactionToDgvBalance(DataGridView dgvBalance, Transaction tran)
{
dgvBalance.Rows.Add();
DataGridViewRow newRow = dgvBalance.Rows[dgvBalance.Rows.Count - 1];
newRow.Cells[0].Value = tran.code;
newRow.Cells[2].Value = tran.date.ToShortDateString();
newRow.Cells[3].Value = tran.amount;
newRow.Cells[4].Value = -tran.payment / tran.amount;
newRow.Cells[5].Value = tran.payment;
if (tran.GetType() == typeof(FutureTransaction))
{
newRow.Cells[1].Value = ((FutureTransaction)tran).contractCode;
}
}
public LinkedList<Transaction> GetTransaction(Type tranType, string code, DateTime from, DateTime to)
{
OleDbCommand cmd = dbConn.CreateCommand();
LinkedList<Transaction> result = new LinkedList<Transaction>();
string filterStr = _MakeFilterString(code, from, to);
if (!string.IsNullOrEmpty(filterStr))
filterStr = " WHERE " + filterStr;
if (tranType == typeof(Transaction))
{
cmd.CommandText = "SELECT code, tran_date, amount, payment, description FROM Investment" + filterStr +
" ORDER BY tran_date";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Transaction curTran = new Transaction();
curTran.code = (string)reader["code"];
curTran.date = DateTime.Parse(reader["tran_date"].ToString());
curTran.amount = (double)reader["amount"];
curTran.payment = (double)reader["payment"];
curTran.description = (string)reader["description"];
result.AddLast(curTran);
}
}
else if (tranType == typeof(FutureTransaction))
{
cmd.CommandText = "SELECT code, contract_code, tran_date, amount, payment, description FROM Future" +
filterStr + " ORDER BY tran_date";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
FutureTransaction curTran = new FutureTransaction();
curTran.code = (string)reader["code"];
curTran.contractCode = (string)reader["contract_code"];
curTran.date = DateTime.Parse(reader["tran_date"].ToString());
curTran.amount = (double)reader["amount"];
curTran.payment = (double)reader["payment"];
curTran.description = (string)reader["description"];
result.AddLast(curTran);
}
}
else
throw new NotImplementedException("Unknow transaction");
return result;
}