public string GetSQL()
{
string investorCode = commonClass.SysLibs.sysLoginCode;
databases.baseDS.transactionsDataTable transactionsTbl = new databases.baseDS.transactionsDataTable();
databases.baseDS.stockCodeDataTable stockCodeTbl = new databases.baseDS.stockCodeDataTable();
databases.baseDS.portfolioDataTable portfolioTbl = new databases.baseDS.portfolioDataTable();
string sqlCmd = "SELECT * FROM " + transactionsTbl.TableName;
string filter = "";
string portfolioCond = "";
if (investorCode.Trim() != "")
{
portfolioCond += (portfolioCond == "" ? "" : " AND ") +
portfolioTbl.investorCodeColumn.ColumnName + "=N'" + investorCode.Trim() + "'";
}
if (portfolioCond != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.portfolioColumn.ColumnName + " IN " + common.Consts.constCRLF +
"(SELECT " + portfolioTbl.codeColumn.ColumnName + " FROM " + portfolioTbl.TableName +
" WHERE " + portfolioCond + ")";
}
if (this.stockExchangeChk.Checked && this.stockExchangeCb.myValue != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.stockCodeColumn.ColumnName + " IN (SELECT " + stockCodeTbl.codeColumn.ColumnName +
" FROM " + stockCodeTbl.TableName + " WHERE " + stockCodeTbl.stockExchangeColumn.ColumnName + "=N'" + this.stockExchangeCb.myValue.Trim() + "')";
}
if (this.dateRangeChk.Checked && this.dateRange.GetDateRange())
{
filter += (filter == "" ? "" : " AND ") +
"(" + transactionsTbl.onTimeColumn.ColumnName +
" BETWEEN '" + common.system.ConvertToSQLDateString(this.dateRange.frDate) + "' AND '" +
common.system.ConvertToSQLDateString(this.dateRange.toDate) + "')";
}
if (this.stockCodeEd.Enabled && this.stockCodeEd.Text.Trim() != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.stockCodeColumn.ColumnName + " =N'" + this.stockCodeEd.Text.Trim() + "'";
}
if (portfolioChk.Checked && portfolioCb.myValue.Trim() != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.portfolioColumn.ColumnName + " =N'" + portfolioCb.myValue.Trim() + "'";
}
if (filter != "")
{
sqlCmd += " WHERE " + filter;
}
return(sqlCmd);
}
public string GetSQL()
{
string investorCode = commonClass.SysLibs.sysLoginCode;
databases.baseDS.transactionsDataTable transactionsTbl = new databases.baseDS.transactionsDataTable();
databases.baseDS.stockCodeDataTable stockCodeTbl = new databases.baseDS.stockCodeDataTable();
databases.baseDS.portfolioDataTable portfolioTbl = new databases.baseDS.portfolioDataTable();
string sqlCmd = "SELECT * FROM " + transactionsTbl.TableName;
string filter = "";
string portfolioCond = "";
if (investorCode.Trim() != "")
{
portfolioCond +=(portfolioCond == "" ? "" : " AND ") +
portfolioTbl.investorCodeColumn.ColumnName + "=N'" + investorCode.Trim() + "'";
}
if (portfolioCond != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.portfolioColumn.ColumnName + " IN " + common.Consts.constCRLF +
"(SELECT " + portfolioTbl.codeColumn.ColumnName + " FROM " + portfolioTbl.TableName +
" WHERE " + portfolioCond + ")";
}
if (this.stockExchangeChk.Checked && this.stockExchangeCb.myValue != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.stockCodeColumn.ColumnName + " IN (SELECT " +stockCodeTbl.codeColumn.ColumnName +
" FROM " + stockCodeTbl.TableName + " WHERE " + stockCodeTbl.stockExchangeColumn.ColumnName + "=N'" + this.stockExchangeCb.myValue.Trim() + "')";
}
if (this.dateRangeChk.Checked && this.dateRange.GetDateRange())
{
filter += (filter == "" ? "" : " AND ") +
"("+ transactionsTbl.onTimeColumn.ColumnName +
" BETWEEN '" + common.system.ConvertToSQLDateString(this.dateRange.frDate) + "' AND '" +
common.system.ConvertToSQLDateString(this.dateRange.toDate) + "')";
}
if (this.stockCodeEd.Enabled && this.stockCodeEd.Text.Trim()!="")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.stockCodeColumn.ColumnName + " =N'" + this.stockCodeEd.Text.Trim() + "'";
}
if (portfolioChk.Checked && portfolioCb.myValue.Trim() != "")
{
filter += (filter == "" ? "" : " AND ") +
transactionsTbl.portfolioColumn.ColumnName + " =N'" + portfolioCb.myValue.Trim() + "'";
}
if (filter != "") sqlCmd += " WHERE " + filter;
return sqlCmd;
}
protected virtual bool Save()
{
if (statusCb.myValue != AppTypes.CommonStatus.New)
{
common.system.ShowErrorMessage(Languages.Libs.GetString("transactionIsClosed"));
return(false);
}
databases.baseDS.transactionsDataTable transTbl =
DataAccess.Libs.MakeTransaction(transTypeCb.myValue, codeEd.Text, portfolioCb.myValue,
(int)qtyEd.Value, feePercEd.Value);
if (transTbl == null)
{
return(false);
}
SetEditData(transTbl[0]);
return(true);
}
/// <summary>
/// Create records to keep stock transaction (buy,sell...)
/// - transactions
/// - investorStock
/// </summary>
/// <param name="onDate"></param>
/// <param name="type"></param>
/// <param name="stockCode"></param>
/// <param name="portfolio"></param>
/// <param name="qty"></param>
/// <param name="amt"></param>
public static databases.baseDS.transactionsDataTable MakeTransaction(AppTypes.TradeActions type, string stockCode, string portfolioCode, int qty, decimal feePerc, out string errorText)
{
errorText = "";
databases.baseDS.stockExchangeRow marketRow = databases.DbAccess.GetStockExchange(stockCode);
if (marketRow == null)
{
return(null);
}
errorText = "";
DateTime onTime = DateTime.Now;
//Price
databases.baseDS.priceDataRow priceRow = databases.DbAccess.GetLastPriceData(stockCode);
if (priceRow == null)
{
errorText = Languages.Libs.GetString("cannotDoTransaction");
return(null);
}
decimal amt = qty * priceRow.closePrice * marketRow.priceRatio;
decimal feeAmt = (decimal)Math.Round(feePerc * amt / 100, common.system.GetPrecisionFromMask(Settings.sysMaskLocalAmt));
databases.baseDS.portfolioRow portfolioRow = databases.DbAccess.GetPortfolio(portfolioCode);
if (portfolioRow == null)
{
errorText = String.Format(Languages.Libs.GetString("dataNotFound"), "[portfolio]");
return(null);
}
switch (type)
{
case AppTypes.TradeActions.Buy:
case AppTypes.TradeActions.Accumulate:
portfolioRow.usedCapAmt += amt;
portfolioRow.usedCapAmt += feeAmt;
break;
default: //Sell
portfolioRow.usedCapAmt -= amt;
portfolioRow.usedCapAmt += feeAmt;
break;
}
if (portfolioRow.startCapAmt - portfolioRow.usedCapAmt < 0)
{
portfolioRow.CancelEdit();
errorText = String.Format(Languages.Libs.GetString("outOfMoney"), portfolioRow.startCapAmt - portfolioRow.usedCapAmt - amt - feeAmt);
return(null);
}
//Create records to store data
databases.baseDS.transactionsDataTable transTbl = new databases.baseDS.transactionsDataTable();
databases.baseDS.investorStockDataTable investorStockTbl = new databases.baseDS.investorStockDataTable();
databases.baseDS.transactionsRow transRow;
databases.baseDS.investorStockRow stockRow;
transRow = transTbl.NewtransactionsRow();
databases.AppLibs.InitData(transRow);
transRow.onTime = onTime;
transRow.tranType = (byte)type;
transRow.stockCode = stockCode;
transRow.portfolio = portfolioCode;
transRow.qty = qty;
transRow.amt = amt;
transRow.feeAmt = feeAmt;
transRow.status = (byte)AppTypes.CommonStatus.Close;
transTbl.AddtransactionsRow(transRow);
//Update stock
DateTime onDate = onTime.Date;
switch (type)
{
case AppTypes.TradeActions.Buy:
case AppTypes.TradeActions.Accumulate:
investorStockTbl.Clear();
databases.DbAccess.LoadData(investorStockTbl, stockCode, portfolioCode, onDate);
if (investorStockTbl.Count == 0)
{
stockRow = investorStockTbl.NewinvestorStockRow();
databases.AppLibs.InitData(stockRow);
stockRow.buyDate = onDate;
stockRow.stockCode = stockCode;
stockRow.portfolio = portfolioCode;
investorStockTbl.AddinvestorStockRow(stockRow);
}
stockRow = investorStockTbl[0];
stockRow.qty += qty; stockRow.buyAmt += amt;
break;
default: //Sell
DateTime applicableDate = onDate.AddDays(-marketRow.minBuySellDay);
investorStockTbl.Clear();
databases.DbAccess.LoadData(investorStockTbl, stockCode, portfolioCode);
decimal remainQty = qty;
for (int idx = 0; idx < investorStockTbl.Count; idx++)
{
if (investorStockTbl[idx].buyDate > applicableDate)
{
continue;
}
if (investorStockTbl[idx].qty >= remainQty)
{
investorStockTbl[idx].buyAmt = (investorStockTbl[idx].qty - remainQty) * (investorStockTbl[idx].qty == 0 ? 0 : investorStockTbl[idx].buyAmt / investorStockTbl[idx].qty);
investorStockTbl[idx].qty = (investorStockTbl[idx].qty - remainQty);
remainQty = 0;
}
else
{
remainQty -= investorStockTbl[idx].qty;
investorStockTbl[idx].buyAmt = 0;
investorStockTbl[idx].qty = 0;
}
if (remainQty == 0)
{
break;
}
}
if (remainQty > 0)
{
errorText = String.Format(Languages.Libs.GetString("outOfQty"), qty - remainQty);
return(null);
}
break;
}
//Delete empty stock
for (int idx = 0; idx < investorStockTbl.Count; idx++)
{
if (investorStockTbl[idx].qty != 0)
{
continue;
}
investorStockTbl[idx].Delete();
}
//Update data with transaction support
TransactionScopeOption tranOption;
tranOption = (commonClass.SysLibs.sysUseTransactionInUpdate ? TransactionScopeOption.Required : TransactionScopeOption.Suppress);
using (TransactionScope scope = new TransactionScope(tranOption))
{
databases.DbAccess.UpdateData(portfolioRow);
databases.DbAccess.UpdateData(investorStockTbl);
databases.DbAccess.UpdateData(transTbl);
scope.Complete();
}
return(transTbl);
}
public static databases.baseDS.transactionsRow UpdateData(databases.baseDS.transactionsRow row)
{
try
{
lock (myClient)
{
databases.baseDS.transactionsDataTable tbl = new databases.baseDS.transactionsDataTable();
tbl.ImportRow(row);
myClient.UpdateTransactions(ref tbl);
row.AcceptChanges();
return tbl[0];
}
}
catch (Exception er)
{
if (OnError != null) OnError(er);
}
return null;
}
public bool UpdateTransactions(ref databases.baseDS.transactionsDataTable transactionsTbl)
{
return(base.Channel.UpdateTransactions(ref transactionsTbl));
}
public databases.baseDS.transactionsDataTable GetTransaction_BySQL(string sql)
{
try
{
databases.baseDS.transactionsDataTable tbl = new databases.baseDS.transactionsDataTable();
databases.DbAccess.LoadFromSQL(tbl, sql);
return tbl;
}
catch (Exception ex)
{
WriteSysLogLocal("WS052", ex);
}
return null;
}
/// <summary>
/// Create records to keep stock transaction (buy,sell...)
/// - transactions
/// - investorStock
/// </summary>
/// <param name="onDate"></param>
/// <param name="type"></param>
/// <param name="stockCode"></param>
/// <param name="portfolio"></param>
/// <param name="qty"></param>
/// <param name="amt"></param>
public static databases.baseDS.transactionsDataTable MakeTransaction(AppTypes.TradeActions type, string stockCode, string portfolioCode, int qty, decimal feePerc,out string errorText)
{
errorText="";
databases.baseDS.stockExchangeRow marketRow = databases.DbAccess.GetStockExchange(stockCode);
if (marketRow == null) return null;
errorText="";
DateTime onTime = DateTime.Now;
//Price
databases.baseDS.priceDataRow priceRow = databases.DbAccess.GetLastPriceData(stockCode);
if (priceRow == null)
{
errorText = Languages.Libs.GetString("cannotDoTransaction");
return null;
}
decimal amt = qty * priceRow.closePrice * marketRow.priceRatio;
decimal feeAmt = (decimal)Math.Round(feePerc * amt / 100, common.system.GetPrecisionFromMask(Settings.sysMaskLocalAmt));
databases.baseDS.portfolioRow portfolioRow = databases.DbAccess.GetPortfolio(portfolioCode);
if (portfolioRow == null)
{
errorText = String.Format(Languages.Libs.GetString("dataNotFound"), "[portfolio]");
return null;
}
switch (type)
{
case AppTypes.TradeActions.Buy:
case AppTypes.TradeActions.Accumulate:
portfolioRow.usedCapAmt += amt;
portfolioRow.usedCapAmt += feeAmt;
break;
default: //Sell
portfolioRow.usedCapAmt -= amt;
portfolioRow.usedCapAmt += feeAmt;
break;
}
if (portfolioRow.startCapAmt - portfolioRow.usedCapAmt < 0)
{
portfolioRow.CancelEdit();
errorText = String.Format(Languages.Libs.GetString("outOfMoney"), portfolioRow.startCapAmt - portfolioRow.usedCapAmt - amt - feeAmt);
return null;
}
//Create records to store data
databases.baseDS.transactionsDataTable transTbl = new databases.baseDS.transactionsDataTable();
databases.baseDS.investorStockDataTable investorStockTbl = new databases.baseDS.investorStockDataTable();
databases.baseDS.transactionsRow transRow;
databases.baseDS.investorStockRow stockRow;
transRow = transTbl.NewtransactionsRow();
databases.AppLibs.InitData(transRow);
transRow.onTime = onTime;
transRow.tranType = (byte)type;
transRow.stockCode = stockCode;
transRow.portfolio = portfolioCode;
transRow.qty = qty;
transRow.amt = amt;
transRow.feeAmt = feeAmt;
transRow.status = (byte)AppTypes.CommonStatus.Close;
transTbl.AddtransactionsRow(transRow);
//Update stock
DateTime onDate = onTime.Date;
switch (type)
{
case AppTypes.TradeActions.Buy:
case AppTypes.TradeActions.Accumulate:
investorStockTbl.Clear();
databases.DbAccess.LoadData(investorStockTbl, stockCode, portfolioCode, onDate);
if (investorStockTbl.Count == 0)
{
stockRow = investorStockTbl.NewinvestorStockRow();
databases.AppLibs.InitData(stockRow);
stockRow.buyDate = onDate;
stockRow.stockCode = stockCode;
stockRow.portfolio = portfolioCode;
investorStockTbl.AddinvestorStockRow(stockRow);
}
stockRow = investorStockTbl[0];
stockRow.qty += qty; stockRow.buyAmt += amt;
break;
default: //Sell
DateTime applicableDate = onDate.AddDays(-marketRow.minBuySellDay);
investorStockTbl.Clear();
databases.DbAccess.LoadData(investorStockTbl, stockCode, portfolioCode);
decimal remainQty = qty;
for (int idx = 0; idx < investorStockTbl.Count; idx++)
{
if (investorStockTbl[idx].buyDate > applicableDate) continue;
if (investorStockTbl[idx].qty >= remainQty)
{
investorStockTbl[idx].buyAmt = (investorStockTbl[idx].qty - remainQty) * (investorStockTbl[idx].qty == 0 ? 0 : investorStockTbl[idx].buyAmt / investorStockTbl[idx].qty);
investorStockTbl[idx].qty = (investorStockTbl[idx].qty - remainQty);
remainQty = 0;
}
else
{
remainQty -= investorStockTbl[idx].qty;
investorStockTbl[idx].buyAmt = 0;
investorStockTbl[idx].qty = 0;
}
if (remainQty == 0) break;
}
if (remainQty > 0)
{
errorText = String.Format(Languages.Libs.GetString("outOfQty"), qty - remainQty);
return null;
}
break;
}
//Delete empty stock
for (int idx = 0; idx < investorStockTbl.Count; idx++)
{
if (investorStockTbl[idx].qty != 0) continue;
investorStockTbl[idx].Delete();
}
//Update data with transaction support
TransactionScopeOption tranOption;
tranOption = (commonClass.SysLibs.sysUseTransactionInUpdate ? TransactionScopeOption.Required : TransactionScopeOption.Suppress);
using (TransactionScope scope = new TransactionScope(tranOption))
{
databases.DbAccess.UpdateData(portfolioRow);
databases.DbAccess.UpdateData(investorStockTbl);
databases.DbAccess.UpdateData(transTbl);
scope.Complete();
}
return transTbl;
}
