public string GetSQL()
{
databases.baseDS.portfolioDataTable portfolioTbl = new databases.baseDS.portfolioDataTable();
databases.baseDS.tradeAlertDataTable tradeAlertTbl = new databases.baseDS.tradeAlertDataTable();
string condCmd = "";
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.portfolioColumn.ColumnName +
" IN (" +
" SELECT " + portfolioTbl.codeColumn.ColumnName +
" FROM " + portfolioTbl.TableName +
" WHERE " + portfolioTbl.investorCodeColumn.ColumnName + "=N'" + commonClass.SysLibs.sysLoginCode + "'))";
if (dateRangeChk.Checked)
{
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.onTimeColumn.ColumnName +
" BETWEEN '" + common.system.ConvertToSQLDateString(dateRange.frDate, false) + "' AND '" +
common.system.ConvertToSQLDateString(dateRange.toDate.AddDays(1).AddSeconds(-1), false) + "')";
}
if (statusChk.Checked)
{
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.statusColumn.ColumnName + " & " + ((byte)statusCb.myValue).ToString() + ">0)";
}
if (portfolioChk.Checked)
{
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.portfolioColumn.ColumnName + "=N'" + portfolioCb.myValue + "')";
}
if (strategyChk.Checked)
{
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.strategyColumn.ColumnName + "=N'" + strategyCb.myValue + "')";
}
if (timeScaleChk.Checked)
{
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.timeScaleColumn.ColumnName + "='" + timeScaleCb.myValue.Code + "')";
}
if (codeChk.Checked)
{
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.stockCodeColumn.ColumnName + "=N'" + codeEd.Text.Trim() + "')";
}
string sqlCmd = "SELECT * FROM " + tradeAlertTbl.TableName +
(condCmd == "" ? "" : " WHERE " + condCmd) +
" ORDER BY " + tradeAlertTbl.onTimeColumn.ColumnName + " DESC";
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);
}
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 static databases.baseDS.portfolioRow FindAndCache(databases.baseDS.portfolioDataTable tbl, string code)
{
databases.baseDS.portfolioRow row = tbl.FindBycode(code);
if (row != null)
{
return(row);
}
databases.baseDSTableAdapters.portfolioTA dataTA = new databases.baseDSTableAdapters.portfolioTA();
dataTA.ClearBeforeFill = false;
dataTA.FillByCode(tbl, code);
row = tbl.FindBycode(code);
if (row != null)
{
return(row);
}
return(null);
}
protected override void SaveData()
{
common.MultiValueString mvString = new common.MultiValueString();
databases.baseDS.portfolioDataTable portfolioTbl = new databases.baseDS.portfolioDataTable();
databases.baseDS.portfolioDetailDataTable portfolioDataTbl;
portfolioTbl.Clear();
for (int portfolioIdx = 0; portfolioIdx < watchListLb.myCheckedValues.Count; portfolioIdx++)
{
databases.baseDS.portfolioRow portfolioRow = DataAccess.Libs.GetPortfolio_ByCode(watchListLb.myCheckedValues[portfolioIdx]);
if (portfolioRow == null)
{
continue;
}
mvString.myFormatString = portfolioRow.interestedStock;
mvString.Add(codeEd.Text);
portfolioRow.interestedStock = mvString.myFormatString;
portfolioDataTbl = DataAccess.Libs.GetPortfolioDetail_ByCode(portfolioRow.code);
databases.baseDS.portfolioDetailRow dataRow;
for (int idx = 0; idx < myStrategyCodes.Count; idx++)
{
dataRow = portfolioDataTbl.FindByportfoliocodesubCode(portfolioRow.code, this.codeEd.Text, this.myStrategyCodes[idx]);
if (dataRow == null)
{
dataRow = portfolioDataTbl.NewportfolioDetailRow();
databases.AppLibs.InitData(dataRow);
dataRow.portfolio = portfolioRow.code;
dataRow.code = codeEd.Text;
dataRow.subCode = this.myStrategyCodes[idx];
portfolioDataTbl.AddportfolioDetailRow(dataRow);
}
mvString.myFormatString = dataRow.data;
mvString.Add(timeScaleCb.myValue.Code);
dataRow.data = mvString.myFormatString;
}
DataAccess.Libs.UpdateData(portfolioRow);
DataAccess.Libs.UpdateData(portfolioDataTbl);
}
common.system.ShowMessage(Languages.Libs.GetString("dataSaved"));
}
protected override void SaveData()
{
common.MultiValueString mvString = new common.MultiValueString();
databases.baseDS.portfolioDataTable portfolioTbl = new databases.baseDS.portfolioDataTable();
databases.baseDS.portfolioDetailDataTable portfolioDataTbl;
portfolioTbl.Clear();
for (int portfolioIdx = 0; portfolioIdx < watchListLb.myCheckedValues.Count; portfolioIdx++)
{
databases.baseDS.portfolioRow portfolioRow = DataAccess.Libs.GetPortfolio_ByCode(watchListLb.myCheckedValues[portfolioIdx]);
if (portfolioRow == null) continue;
mvString.myFormatString = portfolioRow.interestedStock;
mvString.Add(codeEd.Text);
portfolioRow.interestedStock = mvString.myFormatString;
portfolioDataTbl = DataAccess.Libs.GetPortfolioDetail_ByCode(portfolioRow.code);
databases.baseDS.portfolioDetailRow dataRow;
for (int idx = 0; idx < myStrategyCodes.Count; idx++)
{
dataRow = portfolioDataTbl.FindByportfoliocodesubCode(portfolioRow.code, this.codeEd.Text, this.myStrategyCodes[idx]);
if (dataRow == null)
{
dataRow = portfolioDataTbl.NewportfolioDetailRow();
databases.AppLibs.InitData(dataRow);
dataRow.portfolio = portfolioRow.code;
dataRow.code = codeEd.Text;
dataRow.subCode = this.myStrategyCodes[idx];
portfolioDataTbl.AddportfolioDetailRow(dataRow);
}
mvString.myFormatString = dataRow.data;
mvString.Add(timeScaleCb.myValue.Code);
dataRow.data = mvString.myFormatString;
}
DataAccess.Libs.UpdateData(portfolioRow);
DataAccess.Libs.UpdateData(portfolioDataTbl);
}
common.system.ShowMessage(Languages.Libs.GetString("dataSaved"));
}
public static databases.baseDS.portfolioRow UpdateData(databases.baseDS.portfolioRow row)
{
try
{
lock (myClient)
{
databases.baseDS.portfolioDataTable tbl = new databases.baseDS.portfolioDataTable();
tbl.ImportRow(row);
myClient.UpdatePortfolio(ref tbl);
row.AcceptChanges();
return tbl[0];
}
}
catch (Exception er)
{
if (OnError != null) OnError(er);
}
return null;
}
public virtual void LoadData(string investorCode, bool checkedAll)
{
dataTbl = DataAccess.Libs.GetPortfolio_ByInvestorAndType(investorCode, this.WatchType);
this.Items.Clear();
for (int idx = 0; idx < dataTbl.Count; idx++)
this.Items.Add(new common.myComboBoxItem(dataTbl[idx].name.Trim(), dataTbl[idx].code.Trim()), checkedAll);
SaveItems();
}
public void LoadData(string investorCode, bool AddAllItem)
{
databases.baseDS.portfolioDataTable tbl = new databases.baseDS.portfolioDataTable();
if (AddAllItem)
{
databases.baseDS.portfolioRow row = tbl.NewportfolioRow();
databases.AppLibs.InitData(row);
row.investorCode = investorCode;
row.name = Settings.sysString_All_Description;
row.code = Settings.sysString_All_Code;
tbl.AddportfolioRow(row);
}
databases.baseDS.portfolioDataTable tmpTbl = DataAccess.Libs.GetPortfolio_ByInvestor(investorCode);
for (int idx = 0; idx < tmpTbl.Count; idx++) tbl.ImportRow(tmpTbl[idx]);
this.DisplayMember = tbl.nameColumn.ColumnName;
this.ValueMember = tbl.codeColumn.ColumnName;
this.DataSource = tbl;
if (tbl.Count > 0) this.MaxDropDownItems = tbl.Count;
}
public bool UpdatePortfolio(ref databases.baseDS.portfolioDataTable portfolioTbl)
{
return(base.Channel.UpdatePortfolio(ref portfolioTbl));
}
public databases.baseDS.portfolioDataTable GetPortfolio_ByType(AppTypes.PortfolioTypes type)
{
try
{
databases.baseDS.portfolioDataTable tbl = new databases.baseDS.portfolioDataTable();
databases.DbAccess.LoadData(tbl, type);
return tbl;
}
catch (Exception ex)
{
WriteSysLogLocal("WS045", ex);
}
return null;
}
public databases.baseDS.portfolioDataTable GetPortfolio_ByInvestorAndType(string investorCode, AppTypes.PortfolioTypes type)
{
try
{
databases.baseDS.portfolioDataTable tbl = new databases.baseDS.portfolioDataTable();
databases.DbAccess.LoadPortfolioByInvestor(tbl, investorCode, type);
return tbl;
}
catch (Exception ex)
{
WriteSysLogLocal("WS044", ex);
}
return null;
}
public databases.baseDS.portfolioDataTable GetPortfolio_ByCode(string code)
{
try
{
databases.baseDS.portfolioDataTable tbl = new databases.baseDS.portfolioDataTable();
databases.DbAccess.LoadData(tbl, code);
return tbl;
}
catch (Exception ex)
{
WriteSysLogLocal("WS046", ex);
}
return null;
}
public string GetSQL()
{
databases.baseDS.portfolioDataTable portfolioTbl = new databases.baseDS.portfolioDataTable();
databases.baseDS.tradeAlertDataTable tradeAlertTbl = new databases.baseDS.tradeAlertDataTable();
string condCmd = "";
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.portfolioColumn.ColumnName +
" IN (" +
" SELECT " + portfolioTbl.codeColumn.ColumnName +
" FROM " + portfolioTbl.TableName +
" WHERE " + portfolioTbl.investorCodeColumn.ColumnName + "=N'" + commonClass.SysLibs.sysLoginCode + "'))";
if (dateRangeChk.Checked)
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.onTimeColumn.ColumnName +
" BETWEEN '" + common.system.ConvertToSQLDateString(dateRange.frDate,false) + "' AND '"+
common.system.ConvertToSQLDateString(dateRange.toDate.AddDays(1).AddSeconds(-1),false) + "')";
if (statusChk.Checked)
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.statusColumn.ColumnName + " & " + ((byte)statusCb.myValue).ToString() + ">0)";
if (portfolioChk.Checked)
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.portfolioColumn.ColumnName + "=N'" + portfolioCb.myValue + "')";
if (strategyChk.Checked)
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.strategyColumn.ColumnName + "=N'" + strategyCb.myValue + "')";
if (timeScaleChk.Checked)
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.timeScaleColumn.ColumnName + "='" + timeScaleCb.myValue.Code + "')";
if (codeChk.Checked)
condCmd += (condCmd == "" ? "" : " AND ") +
"(" + tradeAlertTbl.stockCodeColumn.ColumnName + "=N'" + codeEd.Text.Trim() + "')";
string sqlCmd = "SELECT * FROM " + tradeAlertTbl.TableName +
(condCmd == "" ? "" : " WHERE " + condCmd) +
" ORDER BY " + tradeAlertTbl.onTimeColumn.ColumnName + " DESC";
return sqlCmd;
}
