public static data.baseDS.transactionsRow UpdateData(data.baseDS.transactionsRow row) { data.baseDS.transactionsDataTable tbl = new data.baseDS.transactionsDataTable(); tbl.ImportRow(row); myClient.UpdateTransactions(ref tbl); row.AcceptChanges(); return(tbl[0]); }
public string GetSQL() { string investorCode = commonClass.SysLibs.sysLoginCode; data.baseDS.transactionsDataTable transactionsTbl = new data.baseDS.transactionsDataTable(); data.baseDS.stockCodeDataTable stockCodeTbl = new data.baseDS.stockCodeDataTable(); data.baseDS.portfolioDataTable portfolioTbl = new data.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 static data.baseDS.transactionsDataTable MakeTransaction(AppTypes.TradeActions type, string stockCode, string portfolioCode, int qty, decimal feePerc) { string errorText = ""; data.baseDS.transactionsDataTable retVal = myClient.MakeTransaction(out errorText, type, stockCode, portfolioCode, qty, feePerc); if (retVal == null) { common.system.ShowErrorMessage(errorText); } return(retVal); }
public string GetSQL() { string investorCode = commonClass.SysLibs.sysLoginCode; data.baseDS.transactionsDataTable transactionsTbl = new data.baseDS.transactionsDataTable(); data.baseDS.stockCodeDataTable stockCodeTbl = new data.baseDS.stockCodeDataTable(); data.baseDS.portfolioDataTable portfolioTbl = new data.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); } data.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); }
public void UpdateTransactions(ref data.baseDS.transactionsDataTable tbl) { DbAccess.UpdateData(tbl); }
public data.baseDS.transactionsDataTable GetTransaction_BySQL(string sql) { data.baseDS.transactionsDataTable tbl = new data.baseDS.transactionsDataTable(); DbAccess.LoadFromSQL(tbl, sql); return(tbl); }
public static data.baseDS.transactionsRow UpdateData(data.baseDS.transactionsRow row) { data.baseDS.transactionsDataTable tbl = new data.baseDS.transactionsDataTable(); tbl.ImportRow(row); myClient.UpdateTransactions(ref tbl); row.AcceptChanges(); return tbl[0]; }
public data.baseDS.transactionsDataTable GetTransaction_BySQL(string sql) { data.baseDS.transactionsDataTable tbl = new data.baseDS.transactionsDataTable(); DbAccess.LoadFromSQL(tbl, sql); return tbl; }
/// <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 data.baseDS.transactionsDataTable MakeTransaction(AppTypes.TradeActions type, string stockCode, string portfolioCode, int qty, decimal feePerc,out string errorText) { errorText=""; data.baseDS.stockExchangeRow marketRow = GetStockExchange(stockCode); if (marketRow == null) return null; errorText=""; DateTime onTime = DateTime.Now; //Price data.baseDS.priceDataRow priceRow = 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, Settings.sysPrecisionLocal); data.baseDS.portfolioRow portfolioRow = 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 data.baseDS.transactionsDataTable transTbl = new data.baseDS.transactionsDataTable(); data.baseDS.investorStockDataTable investorStockTbl = new data.baseDS.investorStockDataTable(); data.baseDS.transactionsRow transRow; data.baseDS.investorStockRow stockRow; transRow = transTbl.NewtransactionsRow(); commonClass.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(); DbAccess.LoadData(investorStockTbl, stockCode, portfolioCode, onDate); if (investorStockTbl.Count == 0) { stockRow = investorStockTbl.NewinvestorStockRow(); commonClass.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(); 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)) { DbAccess.UpdateData(portfolioRow); DbAccess.UpdateData(investorStockTbl); DbAccess.UpdateData(transTbl); scope.Complete(); } return transTbl; }
public static void UpdateData(data.baseDS.transactionsDataTable tbl) { transactionsTA.Update(tbl); tbl.AcceptChanges(); }
public static void LoadData(data.baseDS.transactionsDataTable tbl, string portfolio, string stockCode) { transactionsTA.ClearBeforeFill = false; transactionsTA.FillByPortfolioStockCode(tbl, portfolio, stockCode); }