protected void BuidTable()
{
IList <data.indexdefinition> indexList = indexService.FindAll().Where(p => p.state == 1).ToList();
string dropSql = "drop table if exists `reco_object_rank`";
string sql = @"create table if not exists `reco_object_rank` ({0}, PRIMARY KEY (`code`));";
string sql1 = @"code varchar(50) NOT NULL, date datetime NULL,category_code varchar(50) null,object_code varchar(50) null,rank int default 0";
foreach (var index in indexList)
{
sql1 += "," + index.code + " int default 0";
}
string result = string.Format(sql, sql1);
using (var entity = new StockManDBEntities())
{
this.Log().Info("删除表：reco_stock_rank");
entity.Database.ExecuteSqlCommand(dropSql);
this.Log().Info("构建表：reco_stock_rank");
entity.Database.ExecuteSqlCommand(result);
}
}
public virtual void Delete(T data)
{
using (StockManDBEntities db = new StockManDBEntities())
{
Type type = data.GetType();
PropertyInfo[] members = type.GetProperties();
List <string> ids = new List <string>();
foreach (PropertyInfo m in members)
{
var temp = m.GetCustomAttribute <KeyAttribute>();
if (temp != null)
{
object obj = m.GetValue(data);
ids.Add(obj.ToString());
continue;
}
}
T entity = db.Set <T>().Find(ids.ToArray());
if (entity != default(T))
{
db.Set <T>().Remove(entity);
db.SaveChanges();
}
}
}
public virtual void DeleteRange(IList <T> datas)
{
using (StockManDBEntities db = new StockManDBEntities())
{
IList <T> list = new List <T>();
foreach (T data in datas)
{
Type type = data.GetType();
PropertyInfo[] members = type.GetProperties();
List <string> ids = new List <string>();
foreach (PropertyInfo m in members)
{
var temp = m.GetCustomAttribute <KeyAttribute>();
if (temp != null)
{
object obj = m.GetValue(data);
ids.Add(obj.ToString());
continue;
}
}
T entity = db.Set <T>().Find(ids.ToArray());
list.Add(entity);
}
if (list.Count > 0)
{
foreach (var l in list)
{
db.Set <T>().Remove(l);
}
db.SaveChanges();
}
}
}
public IList <MyCycleObject> GetMyObject(string userId)
{
//三类，大盘，行业，经济数据
// string sql = @"SELECT code
// ,name
// ,state
// ,date
// ,open
// ,low
// ,price
// ,updown
// ,yestclose
// ,volume
// ,turnover
// ,high
// ,percent
// FROM customobject a inner join Object_User_Map b on a.code=b.object_code
// where b.user_id='0'";
string sql = @"select a.code,a.name,a.price,a.yestclose,b.object_type as 'type' from customobject a
inner join object_user_map b on a.code=b.object_code and b.object_type='3' and b.user_id=@p0
union
select a.code,a.name,a.price,a.yestclose,b.object_type as 'type' from stockcategory a
inner join object_user_map b on a.code=b.object_code and b.object_type='2' and b.user_id=@p0
union
select a.code,a.name,a.value as 'price',a.last_value as 'yestclose',b.object_type as 'type' from related_object_define a
inner join object_user_map b on a.code =b.object_code and b.object_type='4' and b.user_id=@p0";
using (var entity = new StockManDBEntities())
{
return(entity.Database.SqlQuery <MyCycleObject>(sql, userId).ToList());
}
}
// GET api/values
public IEnumerable <string> Get()
{
StockManDBEntities entity = new StockManDBEntities();
var stock = entity.stock.Find("1300332");
return(new string[] { stock.name });
}
public long GetUserCount()
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.users.Count());
}
}
public override related_object_define Find(string code)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.related_object_define.Include("related_object_fields").Where(p => p.code == code).FirstOrDefault());
}
}
public IList <indexdefinition> GetIndexByCodes(string[] codes)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.indexdefinition.Where(p => codes.Contains(p.code)).ToList());
}
}
protected IList <IndexData> GetLastIndexData(stock stock, IndexDefinitionInfo define)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
string sql = @"select * from " + define.table_name + " where f_code='" + stock.code + "' order by code desc limit 50";
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql;
IDataReader reader = commond.ExecuteReader();
IList <IndexData> list = new List <IndexData>();
while (reader.Read())
{
IndexData id = new IndexData();
id.date = DateTime.Parse(reader["date"] + "");
foreach (var filed in define.fields)
{
id.Add(double.Parse(reader[filed.name] + ""));
}
list.Add(id);
}
entity.Database.Connection.Close();
return(list);
}
}
}
public IHttpActionResult ReplySuggest(dm.sys_goodidea sys_GoodIdea)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
using (StockManDBEntities db = new StockManDBEntities())
{
var item = db.sys_goodidea.FirstOrDefault(p => p.code == sys_GoodIdea.code);
if (item == null)
{
db.sys_goodidea.Add(sys_GoodIdea);
}
else
{
item.description = sys_GoodIdea.description;
}
try
{
db.SaveChanges();
}
catch (Exception)
{
throw;
}
}
return(Ok());
}
private void SaveTechContext(string context, IndexDefinitionInfo define)
{
string objCode = (int)this.ObjectType + "";
string code = objCode + "_" + this.ObjectCode + "_" + define.code + "_" + this.Cycle;
using (var entity = new StockManDBEntities())
{
var item = entity.tech_context.FirstOrDefault(p => p.code == code);
if (item == null)
{
entity.tech_context.Add(new tech_context()
{
code = code,
category_code = objCode,
object_code = this.ObjectCode,
index_code = define.code,
context = context,
createtime = DateTime.Now
});
}
else
{
item.context = context;
item.createtime = DateTime.Now;
}
try
{
entity.SaveChanges();
}
catch (Exception ex)
{
this.Log().Error(ex.Message);
}
}
}
// GET api/values/5
public string Get(string id)
{
StockManDBEntities entity = new StockManDBEntities();
var stock = entity.stock.Find(id);
return(stock.name);
}
public void UpdateCategoryPrice(IList <PriceInfo> spiList)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
string sql = string.Empty;
foreach (PriceInfo p in spiList)
{
// code = item.code,
//date = today,
//price = index,
//high = 0,
//low = 0,
//yestclose = yestclose,
//volume = vol,
//turnover = turnover
sql += string.Format(@"UPDATE `stockcategory` SET
`price` = '{0}',
`yestclose` ='{1}',
`volume` = '{2}',
`turnover` = '{3}',
`high` = '{4}',
`low` ='{5}',
`date` = '{6}',
`percent` = '{7}'
WHERE code = '{8}';", p.price, p.yestclose, p.volume, p.turnover,
p.high, p.low, p.date.ToString("yyyy-MM-dd"), p.percent, p.code);
}
entity.Database.ExecuteSqlCommand(sql);
}
}
public void SetStrategy(string user_id, IList <string> strategry)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
if (strategry == null)
{
strategry = new List <string>();
}
var us = entity.user_strategy.FirstOrDefault(p => p.user_id == user_id);
if (us == null)
{
entity.user_strategy.Add(new user_strategy
{
user_id = user_id,
strategy = JsonConvert.SerializeObject(strategry)
});
}
else
{
us.strategy = JsonConvert.SerializeObject(strategry);
}
entity.SaveChanges();
}
}
public IList <user_position> GetUserPosition(string user_id)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.user_position.Where(p => p.user_id == user_id).ToList());
}
}
public user_amount GetAmount(string user_id)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.user_amount.FirstOrDefault(p => p.id == user_id));
}
}
public virtual IList <T> FindAll()
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.Set <T>().ToList());
}
}
public DateTime GetLatestDate(ObjectType objType, string code)
{
using (var entity = new StockManDBEntities())
{
string tableName = string.Format("data_{0}_{1}_latest", objType.ToString(), TechCycle.day);
string where = string.Empty;
string sql = string.Format(@"SELECT date FROM {0} where object_code='{1}' {2} order by date desc limit 1", tableName, code, where);
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql;
object val = commond.ExecuteScalar();
entity.Database.Connection.Close();
if (string.IsNullOrEmpty(val + ""))
{
return(DateTime.Now);
}
return(DateTime.Parse(val + ""));
}
}
}
/// <summary>
/// {date:'',a:0,b:0}
/// </summary>
/// <param name="defineCode"></param>
/// <param name="data"></param>
public void InsertData(string defineCode, string jsonData)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var data = JsonConvert.DeserializeObject(jsonData) as JObject;
var define = entity.related_object_define
.Include("related_object_fields").FirstOrDefault(p => p.code == defineCode);
string sql = @"insert into related_object_data (code,type,date,createtime,{{0}}) values ('{0}','{1}','{2}','{3}',{{1}})"
.Format(Guid.NewGuid(), defineCode, data["date"], DateTime.Now);
string fieldStr = "", fieldValue = "";
foreach (var field in define.related_object_fields)
{
if (fieldStr.Length == 0)
{
fieldStr = "pro" + field.map;
}
else
{
fieldStr += ",pro" + field.map;
}
if (fieldValue.Length == 0)
{
fieldValue = "'" + data[field.name] + "'";
}
else
{
fieldValue += ",'" + data[field.name] + "'";
}
}
sql = string.Format(sql, fieldStr, fieldValue);
entity.Database.ExecuteSqlCommand(sql);
}
}
public void Execute(IJobExecutionContext context)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var msgList = entity.user_message.Where(p => p.notice_state == 0).ToList();
foreach (var u in msgList)
{
string email = "";
var config = entity.sys_userconfig.Where(p => p.code == u.user_id).FirstOrDefault();
if (config != null && !string.IsNullOrEmpty(config.config))
{
var configDetail = JsonConvert.DeserializeObject <UserConfigItem>(config.config);
email = configDetail.email;
}
if (string.IsNullOrEmpty(email))
{
if (u.user_id.IndexOf("@") > 0)
{
email = u.user_id;
}
}
if (!string.IsNullOrEmpty(email))
{
sendmail("慢牛提醒", u.content, "*****@*****.**", email, "*****@*****.**", "18669040658");
u.notice_state = 1;
}
}
entity.SaveChanges();
}
}
public sys_userconfig GetUserConfig(string userId)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.sys_userconfig.Find(userId));
}
}
public override IList <indexdefinition> FindAll()
{
using (var entity = new StockManDBEntities())
{
return(entity.indexdefinition.Include("indexdefinegroup").ToList <indexdefinition>());
}
}
public IList <indexdefinegroup> GetGroups()
{
using (StockManDBEntities entity = new StockManDBEntities())
{
return(entity.indexdefinegroup.ToList());
}
}
public string GetData <TM>(string code, DateTime start, DateTime end) where TM : ObjectDataBase, new()
{
//e[0],//日期
//e[1],//开盘
//e[2],//收盘
//e[3],//最高
//e[4],//最低
//e[5],//成交量
//e[6],//涨跌额
//e[7],//涨跌幅
string tableName = typeof(TM).Name;
string sql = string.Format(@"SELECT
date
,open
,price
,high
,low
,volume
,updown
,percent
,yestclose
,turnover
FROM {0} where object_code='{1}' and date between '{2}' and '{3}' order by date", tableName, code, start.ToString("yyyy-MM-dd"), end.ToString("yyyy-MM-dd"));
using (var entity = new StockManDBEntities())
{
entity.Database.Connection.Open();
using (entity.Database.Connection)
{
System.Data.IDbCommand commond = entity.Database.Connection.CreateCommand();
commond.CommandText = sql;
IDataReader reader = commond.ExecuteReader();
string dataStr = "";
while (reader.Read())
{
string fstr = "\"" + DateTime.Parse(reader["date"] + "").ToString("yyyyMMdd") + "\"";
for (var i = 1; i < reader.FieldCount; i++)
{
fstr += "," + reader[i];
}
fstr = "[" + fstr + "]";
if (dataStr.Length == 0)
{
dataStr = fstr;
}
else
{
dataStr += "," + fstr;
}
}
dataStr = "[" + dataStr + "]";
entity.Database.Connection.Close();
return(dataStr);
}
}
}
private void SaveTechState(IndexDefinitionInfo define, IndexState result)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
var cate = (int)this.ObjectType + "";
var code = cate + "_" + this.ObjectCode + "_" + define.code;
var objectState = entity.objectstate.FirstOrDefault(p => p.code == code);
if (objectState == null)
{
var temp = new objectstate()
{
code = code,
category_code = cate,
object_code = this.ObjectCode,
index_code = define.code,
date = DateTime.Now
};
switch (this.Cycle)
{
case TechCycle.day:
temp.day = (int)result;
break;
case TechCycle.week:
temp.week = (int)result;
break;
default:
temp.month = (int)result;
break;
}
entity.objectstate.Add(temp);
}
else
{
switch (this.Cycle)
{
case TechCycle.day:
objectState.last_day = objectState.day;
objectState.day = (int)result;
break;
case TechCycle.week:
objectState.last_week = objectState.week;
objectState.week = (int)result;
break;
default:
objectState.last_month = objectState.month;
objectState.month = (int)result;
break;
}
objectState.date = DateTime.Now;
}
entity.SaveChanges();
}
}
public virtual void Update(T data)
{
using (StockManDBEntities db = new StockManDBEntities())
{
db.Entry <T>(data).State = EntityState.Modified;
db.SaveChanges();
}
}
private void clearTechData(data.stock stock)
{
using (StockManDBEntities entity = new StockManDBEntities())
{
string sql = string.Format("delete from tech_context where object_code='{0}'", stock.code);
entity.Database.ExecuteSqlCommand(sql);
}
}
public virtual T Find(string id)
{
using (StockManDBEntities db = new StockManDBEntities())
{
T entity = db.Set <T>().Find(id);
return(entity);
}
}
/// <summary>
/// 技术状态计算之前的初始化工作
/// </summary>
private void Init()
{
using (StockManDBEntities entity = new StockManDBEntities())
{
string sql = "DELETE FROM `object_tag_map`;";
entity.Database.ExecuteSqlCommand(sql);
}
}
private void ClearRecormend()
{
string sql = string.Format(@"delete from `reco_stock_category_index`");
using (var entity = new StockManDBEntities())
{
entity.Database.ExecuteSqlCommand(sql);
}
}
