using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CB.Entity;
using CB.Interface.Infrastructure;
using System.Data.Common;
using System.Data;
using CB.Common;
namespace CB.Data.SqlServer
{
public class FCHN22X5Manage: Repository<FCHN22X5Info>, IFCHN22X5Service
{
public FCHN22X5Manage(string interfaceId)
: base(interfaceId)
{
}
public override bool Save(FCHN22X5Info entity)
{
DbParameter[] pars ={
DbHelper.MakeInParam(InterfaceId,"@Term",(DbType)SqlDbType.Int,4,entity.Term),
DbHelper.MakeInParam(InterfaceId,"@OpenCode1",(DbType)SqlDbType.Int,4,entity.OpenCode1),
DbHelper.MakeInParam(InterfaceId,"@OpenCode2",(DbType)SqlDbType.Int,4,entity.OpenCode2),
DbHelper.MakeInParam(InterfaceId,"@OpenCode3",(DbType)SqlDbType.Int,4,entity.OpenCode3),
DbHelper.MakeInParam(InterfaceId,"@OpenCode4",(DbType)SqlDbType.Int,4,entity.OpenCode4),
DbHelper.MakeInParam(InterfaceId,"@OpenCode5",(DbType)SqlDbType.Int,4,entity.OpenCode5),
DbHelper.MakeInParam(InterfaceId,"@OpenTime",(DbType)SqlDbType.DateTime,0,entity.OpenTime),
DbHelper.MakeInParam(InterfaceId,"@Detail",(DbType)SqlDbType.NVarChar,500,entity.Detail)
};
var isEdit = false;
DbParameter[] para =
{
DbHelper.MakeInParam(InterfaceId,"@Term",(DbType)SqlDbType.Int,4,entity.Term)
};
using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.Text,
"SELECT TOP 1 Id FROM [DT_FCHN22X5] WHERE [Term]= @Term", para))
{
if (reader.Read())
{
if (reader[0] != null && TypeConverter.ObjectToInt(reader["Id"]) > 0)
{
//编辑
isEdit = true;
var sqlString = string.Format(@"UPDATE DT_FCHN22X5 SET Term = @Term,
OpenCode1 = @OpenCode1,
OpenCode2 = @OpenCode2,
OpenCode3 = @OpenCode3,
OpenCode4 = @OpenCode4,
OpenCode5 = @OpenCode5,
OpenTime = @OpenTime,
Detail = @Detail,
AddTime = GETDATE()
WHERE Id = {0}", reader["Id"].ToString());
return TypeConverter.ObjectToInt(DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text, sqlString, pars)) > 0;
}
}
}
if (!isEdit)
{
//新增
var sqlString = string.Format(@"INSERT INTO DT_FCHN22X5
SELECT @Term,@OpenCode1,@OpenCode2,@OpenCode3,@OpenCode4,@OpenCode5,@OpenTime,GETDATE(),@Detail");
return TypeConverter.ObjectToInt(DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text, sqlString, pars)) > 0;
}
return false;
}
public override bool Update(FCHN22X5Info entity)
{
throw new NotImplementedException();
}
/// <summary>
/// 删除河南22选5开奖数据(同时也删除了该期走势图数据)
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public override bool Delete(int Id)
{
var sqlString = string.Empty;
DbParameter[] para =
{
DbHelper.MakeInParam(InterfaceId,"@Id",(DbType)SqlDbType.Int,4,Id)
};
sqlString = string.Format(@"
DELETE A FROM DT_FCHN22X5TrendChartData A
INNER JOIN DT_FCHN22X5 B ON A.Term = B.Term
WHERE B.Id = @Id");
if (TypeConverter.ObjectToInt(DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text, sqlString, para)) > 0)
{
sqlString = string.Format(@"DELETE DT_FCHN22X5 WHERE Id = @Id");
return TypeConverter.ObjectToInt(DbHelper.ExecuteNonQuery(InterfaceId, CommandType.Text, sqlString, para)) > 0;
}
return false;
}
public override FCHN22X5Info Get<TKey>(TKey key)
{
FCHN22X5Info Entity = null;
DbParameter[] para =
{
DbHelper.MakeInParam(InterfaceId,"@Id",(DbType)SqlDbType.Int,4,key)
};
using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.Text,
"SELECT TOP 1 [Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenTime],[Detail],[Addtime] FROM [DT_FCHN22X5] WHERE [Id]= @Id", para))
{
if (reader.Read())
{
Entity = LoadEntity(reader);
}
reader.Dispose();
}
return Entity;
}
public override IList<FCHN22X5Info> ToList(FCHN22X5Info entity)
{
throw new NotImplementedException();
}
public override IList<FCHN22X5Info> ToPaging(FCHN22X5Info entity, int pageSize, int pageIndex, out int recordCount)
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
string where = "1=1 ";
if (null != entity)
{
if (entity.Term > 0)
where += " AND [Term]=" + entity.Term;
}
recordCount = 0;
string field = "[Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenTime],[Detail],[Addtime]"
, orderField = "Term desc"
, tableName = "DT_FCHN22X5";
DbParameter[] para =
{
DbHelper.MakeInParam(InterfaceId,"@pageSize",(DbType)SqlDbType.Int,4,pageSize),
DbHelper.MakeInParam(InterfaceId,"@page",(DbType)SqlDbType.Int,4,pageIndex),
DbHelper.MakeInParam(InterfaceId,"@tableName",(DbType)SqlDbType.NVarChar,500,tableName),
DbHelper.MakeInParam(InterfaceId,"@field",(DbType)SqlDbType.NVarChar,1000,field),
DbHelper.MakeInParam(InterfaceId,"@orderField",(DbType)SqlDbType.NVarChar,50,orderField),
DbHelper.MakeInParam(InterfaceId,"@where",(DbType)SqlDbType.NVarChar,2000,where)
};
using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "usp_st_page", para))
{
while (reader.Read())
{
list.Add(LoadEntity(reader));
}
if (reader.NextResult() && reader.Read())
{
recordCount = reader.GetInt32(0);
}
reader.Dispose();
}
return list;
}
protected override FCHN22X5Info LoadEntity(IDataReader dr)
{
FCHN22X5Info entity = new FCHN22X5Info
{
Id = TypeConverter.ObjectToInt(dr["Id"]),
Term = TypeConverter.ObjectToInt(dr["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
OpenTime = TypeConverter.ObjectToDateTime(dr["Opentime"], DateTime.MinValue),
Detail = dr["Detail"].ToString(),
Addtime = TypeConverter.ObjectToDateTime(dr["Addtime"], DateTime.MinValue)
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5 };
return entity;
}
public IList<FCHN22X5Info> ToListForTrend(long term, LotterySearchField fields)
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
DbParameter[] para;
para = new DbParameter[]
{
DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,200,"SELECT top 2 [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[Opentime] FROM [DT_FCHN22X5] WHERE [Term]<=@qi ORDER BY [Term] DESC"),
DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
};
using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
{
while (reader.Read())
{
var entity = new FCHN22X5Info
{
Term = TypeConverter.ObjectToInt(reader["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
OpenTime = TypeConverter.ObjectToDateTime(reader["Opentime"])
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5};
list.Add(entity);
}
reader.Dispose();
}
return list;
}
public IList<FCHN22X5Info> GetListToEnd(long term, LotterySearchField fields)
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
DbParameter[] para;
para = new DbParameter[]
{
DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,200,"SELECT [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[Opentime] FROM [DT_FCHN22X5] WHERE [Term] >= @qi ORDER BY [Term] ASC"),
DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
};
using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
{
while (reader.Read())
{
var entity = new FCHN22X5Info
{
Term = TypeConverter.ObjectToInt(reader["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
OpenTime = TypeConverter.ObjectToDateTime(reader["Opentime"])
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5 };
list.Add(entity);
}
reader.Dispose();
}
return list;
}
public IList<FCHN22X5Info> GetListToEnd(long term, TrendChartSearchField fields)
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
DbParameter[] para;
int numRepeat = null == fields ? 0 : fields.NumRepeat;
string query = "";
int topSize = 0;
if (null != fields)
{
if (fields.Year > 0)
{
query += " and Term between " + (fields.Year * 1000).ToString() + " and " + ((fields.Year + 1) * 1000).ToString() + " ";
fields.Record = 1500;
}
else if (fields.StartTerm > 0 && fields.EndTerm > 0)
{ query += " and Term between " + fields.StartTerm.ToString() + " and " + fields.EndTerm.ToString() + " "; fields.Record = 1500; }
else if (fields.Record == 0)
{ fields.Record = 30; }
topSize = fields.Record;
}
string topSql = "";
if (topSize > 0) topSql = " top (" + topSize.ToString() + ")";
para = new DbParameter[]
{
DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,2000,"SELECT "+topSql+" [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[Opentime] FROM [DT_FCHN22X5] WHERE OpenCode1 > -1 and [Term]>=@qi "+ query+" ORDER BY [Term] DESC"),
DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
};
using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
{
if (null != dt && 0 < dt.Rows.Count)
{
foreach (DataRow dr in dt.Rows)
{
var entity = new FCHN22X5Info
{
Term = TypeConverter.ObjectToInt(dr["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
OpenTime = TypeConverter.ObjectToDateTime(dr["Opentime"])
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5};
list.Add(entity);
}
}
dt.Dispose();
}
return list;
}
public override IList<FCHN22X5Info> ToList()
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId,
"SELECT [Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[Opentime],[Detail] FROM [DT_FCHN22X5] ORDER BY [Term] ASC"))
{
if (null != dt && 0 < dt.Rows.Count)
{
foreach (DataRow dr in dt.Rows)
{
var entity = new FCHN22X5Info
{
Term = TypeConverter.ObjectToInt(dr["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
Detail = dr["Detail"].ToString(),
OpenTime = TypeConverter.ObjectToDateTime(dr["Opentime"])
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5 };
list.Add(entity);
}
}
dt.Dispose();
}
return list;
}
public int GetMaxTerm()
{
throw new NotImplementedException();
}
public IList<FCHN22X5Info> ToListForNextTrend(long term, LotterySearchField fields)
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
DbParameter[] para;
para = new DbParameter[]
{
DbHelper.MakeInParam(InterfaceId,"@statement",(DbType)SqlDbType.NVarChar,400,"SELECT TOP 2 [Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[Opentime] FROM [DT_FCHN22X5] WHERE [Term]>=@qi ORDER BY [Term] ASC"),
DbHelper.MakeInParam(InterfaceId,"@params",(DbType)SqlDbType.NVarChar,10,"@qi int"),
DbHelper.MakeInParam(InterfaceId,"@qi",(DbType)SqlDbType.Int,4,term)
};
using (IDataReader reader = DbHelper.ExecuteReader(InterfaceId, CommandType.StoredProcedure, "dbo.sp_executesql", para))
{
while (reader.Read())
{
var entity = new FCHN22X5Info
{
Term = TypeConverter.ObjectToInt(reader["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(reader["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(reader["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(reader["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(reader["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(reader["OpenCode5"]),
OpenTime = TypeConverter.ObjectToDateTime(reader["Opentime"])
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5 };
list.Add(entity);
}
reader.Dispose();
}
return list;
}
public FCHN22X5Info GetMaxTermInfo()
{
IList<FCHN22X5Info> list = new List<FCHN22X5Info>();
using (DataTable dt = DbHelper.ExecuteDatatable(InterfaceId,
"SELECT TOP 1 [Id],[Term],[OpenCode1],[OpenCode2],[OpenCode3],[OpenCode4],[OpenCode5],[OpenTime],[Addtime] FROM [DT_FCHN22X5] ORDER BY [Term] desc"))
{
if (null != dt && 0 < dt.Rows.Count)
{
foreach (DataRow dr in dt.Rows)
{
var entity = new FCHN22X5Info
{
Term = TypeConverter.ObjectToInt(dr["Term"]),
OpenCode1 = TypeConverter.ObjectToInt(dr["OpenCode1"]),
OpenCode2 = TypeConverter.ObjectToInt(dr["OpenCode2"]),
OpenCode3 = TypeConverter.ObjectToInt(dr["OpenCode3"]),
OpenCode4 = TypeConverter.ObjectToInt(dr["OpenCode4"]),
OpenCode5 = TypeConverter.ObjectToInt(dr["OpenCode5"]),
OpenTime = TypeConverter.ObjectToDateTime(dr["Opentime"])
};
entity.OpenCode = new List<int>() { entity.OpenCode1, entity.OpenCode2, entity.OpenCode3, entity.OpenCode4, entity.OpenCode5 };
list.Add(entity);
}
}
dt.Dispose();
}
return list[0];
}
}
}