public void UpdateEndTime(Database db)
{
db.ExecNonQuery(
string.Format("update {0} set {1}=?time where {2}=?id", Mapper.TableName, Mapper.EndTime, Mapper.PickId)
, new string[]{ "time", "id" }
, new object[]{ this.EndTime, this.PickId });
}
public void Create(Database db)
{
db.ExecNonQuery(
string.Format(
"insert into {0} ({1},{2},{3}) values(?id, ?code, ?name)"
, Mapper.TableName, Mapper.StockId, Mapper.StockCode, Mapper.StockName
)
, new string[] {"id","code","name"}
, new object[] {this.StockId, this.StockCode, this.StockName});
}
public void Create(Database db)
{
db.ExecNonQuery(
string.Format(
"insert into {0} ({1},{2},{3},{4},{5}) values(?strategy, ?stime, ?etime, ?mode, ?param)"
, Mapper.TableName, Mapper.Strategy, Mapper.StartTime, Mapper.EndTime, Mapper.PickingMode, Mapper.Params
)
, new string[] {"strategy","stime","etime", "mode", "param"}
, new object[] {this.Strategy, this.StartTime, this.EndTime, this.PickingMode, this.Params});
this.PickId = Convert.ToInt32(db.ExecScalar("select last_insert_id()", null, null));
}
private bool UpdateMA(Database db)
{
return db.ExecNonQuery(
string.Format("update {0} set {2}=?macs, {3}=?macl, {4}=?vmacs, {5}=?vmacl where {1}=?id"
, Mapper.TableName, Mapper.Id, Mapper.MAShort, Mapper.MALong, Mapper.VMAShort, Mapper.VMALong),
new string[] { "id", "macs", "macl", "vmacs", "vmacl" },
new object[] { this.Id, this.MAShort, this.MALong, this.VMAShort, this.VMALong }
) > 0;
}
private bool Remove(Database db)
{
return db.ExecNonQuery(
string.Format("delete from {0} where {1}=?id", Mapper.TableName, Mapper.Id),
new string[] { "id" }, new object[] { this.Id }
) > 0;
}
/// <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;
}
void BtnImpPlateClick(object sender, EventArgs e)
{
if(this.txtPlateFile.Text.Trim().Length<=0) return;
if(!File.Exists(this.txtPlateFile.Text)) return;
string plate = (string)this.comboPlate.SelectedItem;
if(String.IsNullOrEmpty(plate)) return;
int plateId = 0;
switch(plate){
case "上证50": plateId = 3; break;
case "沪深300": plateId = 1; break;
case "融资融券": plateId = 2; break;
}
if(plateId<=0) return;
Database db = new Database(this.txtDatabase.Text);
db.Open();
db.ExecNonQuery("delete from bas_plate_stocks where plate_id=?id"
, new string[] { "id" }, new object[] { plateId });
using(StreamReader sr = new StreamReader(this.txtPlateFile.Text, Encoding.Default)){
string line = "";
while((line = sr.ReadLine()) != null){
if(string.IsNullOrEmpty(line)) continue;
string[] fields = line.Trim().Split('\t');
if(fields.Length<2 || fields[0].Trim().Length!=6 ||
(fields[0].Trim()[0]!= '6' && fields[0].Trim()[0]!= '3' && fields[0].Trim()[0]!= '0'))
continue;
string code = fields[0].Trim();
int stockId = int.Parse(code);
if(stockId<=0) continue;
db.ExecNonQuery("insert into bas_plate_stocks(plate_id, sto_id) values(?pid, ?sid)"
, new string[] {"pid", "sid"}, new object[] {plateId, stockId} );
}
}
db.Close();
MessageBox.Show("导入成功", "导入成功");
}
public void UpdateInfo(Database db)
{
db.ExecNonQuery(
string.Format(
"update {0} set {1}=?date,{2}=?tCap,{3}=?cCap,{4}=?eps,{5}=?navps,{6}=?growth,{7}=?loc,{8}=?plat, {9}=?industry where {10}=?id",
Mapper.TableName,
Mapper.ListDate, Mapper.TotalCapital, Mapper.CirculatingCapital, Mapper.EarningsPerShare,
Mapper.NetAssetValuePerShare, Mapper.NetProfitGrowth, Mapper.CompanyLocation, Mapper.Plate, Mapper.Industry,
Mapper.StockId)
, new string[]{"date","tCap","cCap","eps","navps","growth","loc","plat","industry","id"}
, new object[]{
this.ListDate, this.TotalCapital, this.CirculatingCapital, this.EarningsPerShare,
this.NetAssetValuePerShare, this.NetProfitGrowth, this.CompanyLocation,
this.Plate, this.Industry, this.StockId
});
}
protected static int BatchImport(Database db, string tableName, IList entities)
{
if(entities == null || entities.Count<=0) return 0;
int stockId = (entities[0] as KTrend).StockId;
db.ExecNonQuery(string.Format("delete from {0} where {1}=?stoId", tableName, Mapper.StockId),
new string[] { "stoId" }, new object[] { stockId });
BulkInserter<KTrend> bi = CreateBulkInserter(db, tableName, 200); //初步验证，一批次插入200条性能最好
for(int i=0; i<entities.Count; i++){
bi.Push(entities[i] as KTrend);
}
bi.Flush();
return entities.Count;
}
