public static Stock Get(Database db, int id)
{
DataSet ds = db.ExecDataSet(
string.Format("select * from {0} where {1}=?id", Mapper.TableName, Mapper.StockId),
new string[]{"id"}, new object[]{ id }
);
if(ds==null || ds.Tables.Count<=0 || ds.Tables[0].Rows.Count<=0) return null;
return new Stock(ds.Tables[0].Rows[0]);
}
public static IList<Stock> FindAll(Database db)
{
List<Stock> result = new List<Stock>();
DataSet ds = db.ExecDataSet(string.Format("select * from {0}", Mapper.TableName), null, null);
if(ds.Tables.Count<=0 || ds.Tables[0].Rows.Count<=0) return result;
foreach(DataRow row in ds.Tables[0].Rows){
result.Add(new Stock(row));
}
return result;
}
/// <summary>
/// 获取最新topRows条K线数据，返回的列表按交易日期<b>【升序】</b>排序。
/// </summary>
/// <param name="db"></param>
/// <param name="stockId"></param>
/// <param name="topRows"></param>
/// <returns></returns>
public static IList<KJapaneseData> FindLatest(Database db, int stockId, int topRows)
{
DateTime start = DateTime.Now;
DataSet ds = db.ExecDataSet(
string.Format("select * from {0} where {1}=?stoId order by {2} desc limit ?rows"
, Mapper.TableName, Mapper.StockId, Mapper.TxDate),
new string[]{"stoId", "rows"}, new object[]{ stockId, topRows }
);
DateTime loaded = DateTime.Now;
List<KJapaneseData> result = new List<KJapaneseData>(topRows);
if(ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0){
foreach(DataRow row in ds.Tables[0].Rows){
result.Add(new KJapaneseData(row));
}
}
result.Reverse();
_dbTime += Convert.ToInt64((loaded - start).TotalMilliseconds);
_entityTime += Convert.ToInt64((DateTime.Now - loaded).TotalMilliseconds);
return result;
}
public static IList<KJapaneseData> FindAll(Database db, int stockId)
{
DataSet ds = db.ExecDataSet(
string.Format("select * from {0} where {1}=?stoId"
, Mapper.TableName, Mapper.StockId),
new string[]{"stoId"}, new object[]{ stockId }
);
IList<KJapaneseData> result = new List<KJapaneseData>();
if(ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0){
foreach(DataRow row in ds.Tables[0].Rows){
result.Add(new KJapaneseData(row));
}
}
return result;
}
public static IList<KJapaneseData> Find(Database db, int stockId, DateTime start, DateTime end)
{
DataSet ds = db.ExecDataSet(
string.Format("select * from {0} where {1}=?stoId and {2}>=?start and {2}<=?end order by {2}"
, Mapper.TableName, Mapper.StockId, Mapper.TxDate),
new string[]{"stoId", "start", "end"}, new object[]{ stockId, start, end }
);
List<KJapaneseData> result = new List<KJapaneseData>(ds.Tables[0].Rows.Count);
if(ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0){
foreach(DataRow row in ds.Tables[0].Rows){
result.Add(new KJapaneseData(row));
}
}
return result;
}
public static IList<ShareholdersNumEntity> FindLatest(Database db, DateTime startDate)
{
DataSet ds = db.ExecDataSet(
"select * from sto_holder_num where report_date>=?date order by sto_id asc, report_date desc",
new string[]{"date"}, new object[]{startDate}
);
IList<ShareholdersNumEntity> result = new List<ShareholdersNumEntity>();
if(ds!=null && ds.Tables.Count>0 && ds.Tables[0].Rows.Count>0){
foreach(DataRow row in ds.Tables[0].Rows){
result.Add(BuildEntity(row));
}
}
return result;
}
/// <summary>
/// 创建股东数数据。前提条件：<br />
/// 1. 每次调用，<paramref name="entities"/>只能包含同一只股票的股东数数据；<br />
/// 2. 调用时，必须确保StockId, PublishDate, HolderCount, AverageStockNumber, Source属性值有效；
/// </summary>
/// <param name="db"></param>
/// <param name="entities"></param>
public static int Create(Database db, IList<ShareholdersNumEntity> entities)
{
if(entities==null || entities.Count<=0) return 0;
DateTime minDate = DateTime.MaxValue, effectiveDate = new DateTime(1990, 1, 1);
int stockId = 0, exists=0, insertedRows = 0;
try{
db.BeginTransaction();
//添加数据
foreach(ShareholdersNumEntity entity in entities){
//数据校验
if(entity.StockId<=0 || entity.ReportDate<=effectiveDate
// || entity.HolderCount<=0 || entity.AverageStockNumber<=0
|| (entity.Source==null || entity.Source.Trim().Length<=0))
throw new EntityException("[holder-num] [create] 属性无效，无法更新数据库，[id:"
+ entity.StockId + ", date:" + entity.ReportDate.ToString("yyyyMMdd"));
if(stockId==0) stockId = entity.StockId;
if(stockId!=entity.StockId)
throw new EntityException("[holder-num] [create] entities中包含了多只股票的股东数数据");
entity.CreateTime = DateTime.Now;
entity.VarNum = 0;
//插入数据
exists = Convert.ToInt32(db.ExecScalar(
"select count(*) from sto_holder_num where sto_id=?id and report_date=?date",
new string[] {"id", "date"},
new object[] { entity.StockId, entity.ReportDate}
));
if(exists<=0){
insertedRows += db.ExecNonQuery(INSERT_SQL,
new string[]{"id", "date", "holdersNum", "varNum", "avgShares", "totalShares", "transShares", "time", "source"},
new object[]{entity.StockId, entity.ReportDate, entity.HoldersNum, entity.VarNum
, entity.AvgShares, entity.TotalShares, entity.TransShares
, entity.CreateTime, entity.Source});
if(minDate>entity.ReportDate) minDate = entity.ReportDate;
}
}
//更新股东数增长量
int prevCount = 0;
exists = Convert.ToInt32(db.ExecScalar(
"select count(*) from sto_holder_num where sto_id=?id and report_date<?date",
new string[] {"id", "date"},
new object[] { stockId, minDate}
));
if(exists>0){
prevCount = Convert.ToInt32(db.ExecScalar(
"select holders_num from sto_holder_num where sto_id=?id and report_date<?date order by report_date desc limit 1",
new string[] {"id", "date"},
new object[] { stockId, minDate}
));
}
DataSet ds = db.ExecDataSet(
"select * from sto_holder_num where sto_id=?id and report_date>=?date order by report_date asc",
new string[] { "id", "date" }, new object[] { stockId, minDate }
);
foreach(DataRow row in ds.Tables[0].Rows){
if(prevCount==0){
prevCount = Convert.ToInt32(row["holders_num"]);
continue;
}
int curCount = Convert.ToInt32(row["holders_num"]);
db.ExecNonQuery(
"update sto_holder_num set var_num=?varNum where sto_id=?id and report_date=?date",
new string[] { "id", "date", "varNum" },
new object[] { stockId, row["report_date"], curCount-prevCount}
);
prevCount = curCount;
}
db.CommitTransaction();
}catch(Exception ex){
db.RollbackTransaction();
throw ex;
}
return insertedRows;
}
protected static DataSet FindAll(Database db, string tableName, int stockId)
{
return db.ExecDataSet(
string.Format("select * from " + tableName + " where {0}=?stoid order by {1}",
Mapper.StockId, Mapper.StartDate),
new string[] { "stoid" },
new object[] { stockId }
);
}
