/// <summary>
/// 获取历史数据
/// </summary>
/// <param name="pricecode">行情编码</param>
/// <param name="weekflg">周期（M1,M5,M15,...MN）</param>
/// <param name="weektime">时间数字串</param>
/// <returns></returns>
public List<HisData> GetHisDataWithTime(string pricecode, string weekflg,string weektime)
{
List<HisData> list = new List<HisData>();
System.Data.Common.DbDataReader dbreader = null;
try
{
string weektimecondition = string.Empty;
if (!string.IsNullOrEmpty(weektime))
{
weektimecondition = string.Format(" and weektime>={0}",weektime);
}
string sql = string.Format("select top 1440 weektime, openprice, highprice, lowprice, closeprice, volnum from data_{0} where pricecode='{1}' {2} order by weektime desc",
weekflg, pricecode, weektimecondition);
dbreader = DbHelper2.ExecuteReader(sql);
while (dbreader.Read())
{
HisData hisdata = new HisData();
hisdata.weektime = System.DBNull.Value != dbreader["weektime"] ? dbreader["weektime"].ToString() : string.Empty;
hisdata.openprice = System.DBNull.Value != dbreader["openprice"] ? dbreader["openprice"].ToString() : string.Empty;
hisdata.highprice = System.DBNull.Value != dbreader["highprice"] ? dbreader["highprice"].ToString() : string.Empty;
hisdata.lowprice = System.DBNull.Value != dbreader["lowprice"] ? dbreader["lowprice"].ToString() : string.Empty;
hisdata.closeprice = System.DBNull.Value != dbreader["closeprice"] ? dbreader["closeprice"].ToString() : string.Empty;
hisdata.volnum = System.DBNull.Value != dbreader["volnum"] ? dbreader["volnum"].ToString() : string.Empty;
list.Add(hisdata);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != list && list.Count > 0)
{
list.Clear();
}
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return list;
}
/// <summary>
/// 分页获取历史数据
/// </summary>
/// <param name="HisCon">查询条件</param>
/// <param name="pageindex">第几页,从1开始</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="page">输出参数(总页数)</param>
/// <returns></returns>
public List<HisData> GetHisDataWithPage(HisQueryCon HisCon, int pageindex, int pagesize, ref int page)
{
SqlConnection sqlconn = null;
SqlCommand sqlcmd = null;
SqlDataReader sqldr = null;
List<HisData> list = new List<HisData>();
try
{
string weektime = string.Empty;
if(!string.IsNullOrEmpty(HisCon.weektime))
{
weektime = string.Format(" and weektime={0}", HisCon.weektime);
}
string sqlcondition = string.Format("where pricecode='{0}' {3} and weektime>={1} and weektime<={2} ",
HisCon.pricecode,HisCon.StartTime.ToString("yyyyMMdd0000"),HisCon.EndTime.ToString("yyyyMMdd0000"),weektime);
sqlconn = new SqlConnection(ComFunction.SqlConnectionString);
sqlconn.Open();
sqlcmd = sqlconn.CreateCommand();
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = "GetRecordFromPage";
sqlcmd.Parameters.Add("@selectlist", SqlDbType.VarChar);//选择字段列表
sqlcmd.Parameters.Add("@SubSelectList", SqlDbType.VarChar); //内部子查询字段列表
sqlcmd.Parameters.Add("@TableSource", SqlDbType.VarChar); //表名或视图表
sqlcmd.Parameters.Add("@TableOrder", SqlDbType.VarChar); //排序后的表名称 即子查询结果集的别名
sqlcmd.Parameters.Add("@SearchCondition", SqlDbType.VarChar); //查询条件
sqlcmd.Parameters.Add("@OrderExpression", SqlDbType.VarChar); //排序 表达式
sqlcmd.Parameters.Add("@PageIndex", SqlDbType.Int);
sqlcmd.Parameters.Add("@PageSize", SqlDbType.Int);
sqlcmd.Parameters.Add("@PageCount", SqlDbType.Int);
sqlcmd.Parameters["@SubSelectList"].Value = "weektime, openprice, highprice, lowprice, closeprice, volnum ";
sqlcmd.Parameters["@selectlist"].Value = "weektime, openprice, highprice, lowprice, closeprice, volnum ";
sqlcmd.Parameters["@TableSource"].Value = string.Format("data_{0}", HisCon.weekflg);
sqlcmd.Parameters["@TableOrder"].Value = "a";//取L_Trade_Order的别名
sqlcmd.Parameters["@SearchCondition"].Value = sqlcondition;
sqlcmd.Parameters["@OrderExpression"].Value = "order by weektime desc";
sqlcmd.Parameters["@PageIndex"].Value = pageindex;
sqlcmd.Parameters["@PageSize"].Value = pagesize;
sqlcmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
sqldr = sqlcmd.ExecuteReader();
while (sqldr.Read())
{
HisData hsd = new HisData();
hsd.weektime = sqldr["weektime"].ToString();
hsd.openprice = sqldr["openprice"].ToString();
hsd.highprice = sqldr["highprice"].ToString();
hsd.lowprice = sqldr["lowprice"].ToString();
hsd.closeprice = sqldr["closeprice"].ToString();
hsd.volnum = sqldr["volnum"].ToString();
list.Add(hsd);
}
sqlconn.Close();
page = Convert.ToInt32(sqlcmd.Parameters["@PageCount"].Value);
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != list && list.Count > 0)
{
list.Clear();
}
}
finally
{
if (null != sqlconn)
{
sqlconn.Close();
}
if (null != sqldr)
{
sqldr.Close();
}
}
return list;
}
/// <summary>
/// 修改历史数据
/// </summary>
/// <param name="TdUser"></param>
/// <param name="pricecode"></param>
/// <param name="weekflg"></param>
/// <param name="LoginId"></param>
/// <returns></returns>
public ResultDesc ModifyHisData(HisData hisdata,string pricecode,string weekflg)
{
ResultDesc rsdc = new ResultDesc();
try
{
if (string.IsNullOrEmpty(hisdata.weektime)
|| string.IsNullOrEmpty(hisdata.openprice)
|| string.IsNullOrEmpty(hisdata.highprice)
|| string.IsNullOrEmpty(hisdata.lowprice)
|| string.IsNullOrEmpty(hisdata.closeprice))
{
rsdc.Result = false;
rsdc.Desc = "有空数据，请检查";
return rsdc;
}
//SQL语句
List<string> sqlList = new List<string>();
string mdtime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff");
//构造修改用户信息的sql语句
StringBuilder strbld = new StringBuilder();
strbld.AppendFormat("update data_{0} set ",weekflg);
strbld.AppendFormat("openprice='{0}',", hisdata.openprice);
strbld.AppendFormat("highprice='{0}',", hisdata.highprice);
strbld.AppendFormat("lowprice='{0}',", hisdata.lowprice);
strbld.AppendFormat("closeprice='{0}',", hisdata.closeprice);
strbld.AppendFormat("mdtime='{0}'", mdtime);
strbld.AppendFormat(" where pricecode='{0}' and weektime={1} ",pricecode,hisdata.weektime);
sqlList.Add(strbld.ToString());
sqlList.Add(string.Format(@"if not exists (select mdtime from Data_Stamp where pricecode='{0}' and weekflg='{1}')
insert into Data_Stamp(pricecode, weekflg,mdtime) values('{0}','{1}','{2}')
else update Data_Stamp set mdtime = '{2}' where pricecode='{0}' and weekflg='{1}'", pricecode, weekflg, mdtime));
if (!ComFunction.SqlTransaction(sqlList))
{
rsdc.Result = false;
rsdc.Desc = "修改历史数据出错";
return rsdc;
}
rsdc.Result = true;
rsdc.Desc = "修改历史数据成功";
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
rsdc.Result = false;
rsdc.Desc = "修改历史数据失败";
}
return rsdc;
}
/// <summary>
/// 获取历史数据
/// </summary>
/// <param name="pricecode">行情编码</param>
/// <param name="weekflg">周期（M1,M5,M15,...MN）</param>
/// <param name="weektime">时间数字串</param>
/// <param name="mdtime">修改时间数字字符串</param>
/// <returns></returns>
public ComHisData GetHisDataWithMdtime(string pricecode, string weekflg, string weektime, DateTime mdtime)
{
ComHisData comhisdata = new ComHisData();
comhisdata.HisDataList = new List<HisData>();
System.Data.Common.DbDataReader dbreader = null;
try
{
DateTime nearmdtime = ComFunction.GetMdTime(weekflg, pricecode);
comhisdata.mdtime = nearmdtime;
string nearmdtimecondition = string.Empty;
if (nearmdtime > mdtime)//说明数据被修改过
{
nearmdtimecondition = string.Format(" or (pricecode='{0}' and mdtime>'{1}')",pricecode,mdtime.ToString("yyyy-MM-dd HH:mm:ss.fff"));
}
string weektimecondition = string.Empty;
if (!string.IsNullOrEmpty(weektime))
{
weektimecondition = string.Format(" and weektime>={0}", weektime);
}
string sql = string.Format("select top 1440 weektime, openprice, highprice, lowprice, closeprice, volnum,mdtime from data_{0} where (pricecode='{1}' {2}) {3} order by weektime desc",
weekflg, pricecode, weektimecondition, nearmdtimecondition);
dbreader = DbHelper2.ExecuteReader(sql);
while (dbreader.Read())
{
HisData hisdata = new HisData();
hisdata.weektime = System.DBNull.Value != dbreader["weektime"] ? dbreader["weektime"].ToString() : string.Empty;
hisdata.openprice = System.DBNull.Value != dbreader["openprice"] ? dbreader["openprice"].ToString() : string.Empty;
hisdata.highprice = System.DBNull.Value != dbreader["highprice"] ? dbreader["highprice"].ToString() : string.Empty;
hisdata.lowprice = System.DBNull.Value != dbreader["lowprice"] ? dbreader["lowprice"].ToString() : string.Empty;
hisdata.closeprice = System.DBNull.Value != dbreader["closeprice"] ? dbreader["closeprice"].ToString() : string.Empty;
hisdata.volnum = System.DBNull.Value != dbreader["volnum"] ? dbreader["volnum"].ToString() : string.Empty;
hisdata.mdtime = System.DBNull.Value != dbreader["volnum"] ? Convert.ToDateTime(dbreader["mdtime"]) : new DateTime(1970,1,1);
comhisdata.HisDataList.Add(hisdata);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != comhisdata.HisDataList && comhisdata.HisDataList.Count > 0)
{
comhisdata.HisDataList.Clear();
}
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return comhisdata;
}
/// <summary>
/// 获取历史数据
/// </summary>
/// <param name="loginId">登录ID</param>
/// <param name="pricecode">行情编码</param>
/// <param name="weekflg">周期（M1,M5,M15,...MN）</param>
/// <param name="maxCount">最大返回记录数</param>
/// <returns></returns>
public List<HisData> GetWXHisData(string loginId, string pricecode, string weekflg, int maxCount)
{
var list = new List<HisData>();
DbDataReader dbreader = null;
try
{
string sql = string.Format("select top {2} weektime, openprice, highprice, lowprice, closeprice, volnum from data_{0} where pricecode='{1}' order by weektime desc",
weekflg, pricecode, maxCount);
dbreader = DbHelper2.ExecuteReader(sql);
while (dbreader.Read())
{
var hisdata = new HisData
{
weektime = DBNull.Value != dbreader["weektime"] ? dbreader["weektime"].ToString() : string.Empty,
openprice =
DBNull.Value != dbreader["openprice"] ? dbreader["openprice"].ToString() : string.Empty,
highprice =
DBNull.Value != dbreader["highprice"] ? dbreader["highprice"].ToString() : string.Empty,
lowprice = DBNull.Value != dbreader["lowprice"] ? dbreader["lowprice"].ToString() : string.Empty,
closeprice =
DBNull.Value != dbreader["closeprice"] ? dbreader["closeprice"].ToString() : string.Empty,
volnum = DBNull.Value != dbreader["volnum"] ? dbreader["volnum"].ToString() : string.Empty
};
list.Add(hisdata);
}
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (list.Count > 0)
{
list.Clear();
}
}
finally
{
if (null != dbreader)
{
dbreader.Close();
dbreader.Dispose();
}
}
return list;
}