private void getWatchStock()
{
try {
//找到前六個交易日的日期
//WARNING!! Problems happen around Chinese new year!!
string dStr = "";
//DataView dv = DeriLib.Util.ExecSqlQry("SELECT TOP 6 CONVERT(VARCHAR, TradeDate,112) TD FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<CONVERT(VARCHAR,GETDATE(),112) ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
DataTable dv = MSSQL.ExecSqlQry("SELECT TOP 6 CONVERT(VARCHAR, TradeDate,112) TD FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<CONVERT(VARCHAR,GETDATE(),112) ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
foreach (DataRow dr in dv.Rows)
{
dStr += "'" + dr["TD"].ToString() + "',";
}
if (dStr.Length > 0)
{
dStr = dStr.Substring(0, dStr.Length - 1);
}
string sql = "SELECT [股票代號], IsNull([注意交易資訊],'') 注意 FROM [注意股票] WHERE [日期] IN (" + dStr + ") AND ";
string cStr = "";
foreach (string cID in data.Keys)
{
cStr += "'" + cID + "',";
}
if (cStr.Length > 0)
{
cStr = cStr.Substring(0, cStr.Length - 1);
}
sql += "[股票代號] IN (" + cStr + ") ORDER BY [股票代號]";
ADODB.Recordset rs = cn.CMExecute(ref arg, srvLocation, cnPort, sql);
for (; !rs.EOF; rs.MoveNext())
{
string stockID = rs.Fields["股票代號"].Value;
CommodityData d = data[stockID];
string x = rs.Fields["注意"].Value;
if (x != "")
{
d.watchCount++;
}
}
} catch (Exception ex) {
MessageBox.Show("getWatchStock" + ex.Message);
//GlobalVar.errProcess.Add(1, "[CMoneyWork_getWatchStock][" + ex.Message + "][" + ex.StackTrace + "]");
}
}
public CommodityEarning(CommodityData commodityData)
{
this.commodityData = commodityData;
this.sumEarning = 0.0;
this.sumCount = 0;
}
private void getPricesAndPERatio()
{
try {
string dStr = DateTime.Today.ToString("yyyyMMdd");
string qStr = DateTime.Today.AddMonths(-3).ToString("yyyyMMdd");
string yStr = DateTime.Today.AddYears(-1).ToString("yyyyMMdd");
//昨天，三個月前，一年前的交易日
//DataView dDv = DeriLib.Util.ExecSqlQry("SELECT TOP 1 TradeDate FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<'" + dStr + "' ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
//DataView qDv = DeriLib.Util.ExecSqlQry("SELECT TOP 1 TradeDate FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<'" + qStr + "' ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
//DataView yDv = DeriLib.Util.ExecSqlQry("SELECT TOP 1 TradeDate FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<'" + yStr + "' ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
DataTable dDv = MSSQL.ExecSqlQry("SELECT TOP 1 TradeDate FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<'" + dStr + "' ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
DataTable qDv = MSSQL.ExecSqlQry("SELECT TOP 1 TradeDate FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<'" + qStr + "' ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
DataTable yDv = MSSQL.ExecSqlQry("SELECT TOP 1 TradeDate FROM [TradeDate] WHERE IsTrade='Y' AND CONVERT(VARCHAR,TradeDate,112)<'" + yStr + "' ORDER BY TradeDate desc", GlobalVar.loginSet.tsquoteSqlConnString);
//實際前一交易日，前三個月的交易日，前一年的交易日
DateTime dDT = Convert.ToDateTime(dDv.Rows[0]["TradeDate"]);
DateTime qDT = Convert.ToDateTime(qDv.Rows[0]["TradeDate"]);
DateTime yDT = Convert.ToDateTime(yDv.Rows[0]["TradeDate"]);
string sql = "SELECT [日期], [股票代號], IsNull([收盤價],0) 收盤價, IsNull([本益比],0) 本益比 FROM [日收盤表排行] WHERE [日期] IN ('" + dDT.ToString("yyyyMMdd") + "','" + qDT.ToString("yyyyMMdd") + "','" + yDT.ToString("yyyyMMdd") + "') AND ";
string cStr = "";
foreach (string cID in data.Keys)
{
cStr += "'" + cID + "',";
}
//把最後一個逗點刪掉
if (cStr.Length > 0)
{
cStr = cStr.Substring(0, cStr.Length - 1);
}
sql += "[股票代號] IN (" + cStr + ") ORDER BY [股票代號], [日期]";
ADODB.Recordset rs = cn.CMExecute(ref arg, srvLocation, cnPort, sql);
for (; !rs.EOF; rs.MoveNext())
{
string stockID = rs.Fields["股票代號"].Value;
string date = rs.Fields["日期"].Value;
double price = Convert.ToDouble(rs.Fields["收盤價"].Value);
double pe = Convert.ToDouble(rs.Fields["本益比"].Value);
CommodityData d = data[stockID];
if (date == dDT.ToString("yyyyMMdd"))
{
d.peRatio = pe;
d.price = price;
}
else if (date == qDT.ToString("yyyyMMdd"))
{
d.priceQuarter = price;
}
else if (date == yDT.ToString("yyyyMMdd"))
{
d.priceYear = price;
}
}
foreach (CommodityData d in data.Values)
{
if (d.priceQuarter == 0)
{
d.returnQuarter = 0;
}
else
{
d.returnQuarter = d.price / d.priceQuarter - 1.0;
}
if (d.priceYear == 0)
{
d.returnYear = 0;
}
else
{
d.returnYear = d.price / d.priceYear - 1.0;
}
}
} catch (Exception ex) {
MessageBox.Show("getPriceAndPERatio" + ex.Message);
//GlobalVar.errProcess.Add(1, "[CMoneyWork_getPricesAndPERatio][" + ex.Message + "][" + ex.StackTrace + "]");
}
}