/// <summary>
/// 新闻公告查询
/// </summary>
/// <param name="lqc">新闻查询条件</param>
/// <param name="pageindex">第几页,从1开始</param>
/// <param name="pagesize">每页多少条</param>
/// <param name="page">输出参数(总页数)</param>
/// <returns>新闻信息</returns>
public TradeNewsInfo GetTradeNewsInfoWithPage(NewsLqc lqc, int pageindex, int pagesize, ref int page)
{
TradeNewsInfo tradeNewsInfo = new TradeNewsInfo();
SqlConnection sqlconn = null;
SqlCommand sqlcmd = null;
SqlDataReader sqldr = null;
tradeNewsInfo.TradeNewsInfoList = new List<TradeNews>();
try
{
TradeUser TdUser = new TradeUser();
#region 判断登陆标识是否过期
if (!ComFunction.ExistUserLoginID(lqc.LoginID, ref TdUser))
{
tradeNewsInfo.Result = false;
tradeNewsInfo.ReturnCode = ResCode.UL003;
tradeNewsInfo.Desc = ResCode.UL003Desc;
return tradeNewsInfo;
}
#endregion
string newsType = string.Empty;
if (lqc.NType != NewsType.ALL)
{
newsType = string.Format(" and NewsType={0}", (int)lqc.NType);
}
string status = string.Empty;
if (UserType.NormalType == TdUser.UType) //客户端只返回启用的新闻
{
status = string.Format(" and Status=1");
}
string sqlcondition = string.Format("where PubTime>='{0}' and PubTime<='{1}' {2} {3}", lqc.StartTime.ToString("yyyy-MM-dd"),
lqc.EndTime.ToString("yyyy-MM-dd"), newsType, status);
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 = "ID,NewsTitle,NewsContent,PubPerson,PubTime,Status,NewsType,OverView ";
sqlcmd.Parameters["@selectlist"].Value = "ID,NewsTitle,NewsContent,PubPerson,PubTime,Status,NewsType,OverView ";
sqlcmd.Parameters["@TableSource"].Value = "Trade_News";
sqlcmd.Parameters["@TableOrder"].Value = "a";//取L_Trade_Order的别名
sqlcmd.Parameters["@SearchCondition"].Value = sqlcondition;
sqlcmd.Parameters["@OrderExpression"].Value = "order by PubTime desc";
sqlcmd.Parameters["@PageIndex"].Value = pageindex;
sqlcmd.Parameters["@PageSize"].Value = pagesize;
sqlcmd.Parameters["@PageCount"].Direction = ParameterDirection.Output;
sqldr = sqlcmd.ExecuteReader();
while (sqldr.Read())
{
TradeNews TdNews = new TradeNews();
TdNews.ID = sqldr["ID"].ToString();
TdNews.NewsTitle = sqldr["NewsTitle"].ToString();
TdNews.NewsContent = sqldr["NewsContent"].ToString();
TdNews.PubPerson = sqldr["PubPerson"].ToString();
TdNews.PubTime = Convert.ToDateTime(sqldr["PubTime"]);
TdNews.Status = (NewsStatus)sqldr["Status"];
TdNews.NType = (NewsType)sqldr["NewsType"];
TdNews.OverView = System.DBNull.Value != sqldr["OverView"] ? sqldr["OverView"].ToString() : string.Empty;
tradeNewsInfo.TradeNewsInfoList.Add(TdNews);
}
sqlconn.Close();
page = Convert.ToInt32(sqlcmd.Parameters["@PageCount"].Value);
tradeNewsInfo.Result = true;
tradeNewsInfo.Desc = "查询成功";
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
if (null != tradeNewsInfo.TradeNewsInfoList && tradeNewsInfo.TradeNewsInfoList.Count > 0)
{
tradeNewsInfo.TradeNewsInfoList.Clear();
}
tradeNewsInfo.Result = false;
tradeNewsInfo.Desc = "查询失败";
}
finally
{
if (null != sqlconn)
{
sqlconn.Close();
}
if (null != sqldr)
{
sqldr.Close();
}
}
return tradeNewsInfo;
}
/// <summary>
/// 新闻公告查询
/// </summary>
/// <param name="LoginId">登陆标识</param>
/// <param name="starttime">开始时间</param>
/// <param name="endtime">结束时间</param>
/// <param name="NType">新闻类型</param>
/// <returns>新闻信息</returns>
public TradeNewsInfo GetTradeNewsInfo(string LoginId, DateTime starttime, DateTime endtime, NewsType NType)
{
TradeNewsInfo tradeNewsInfo = new TradeNewsInfo();
try
{
TradeUser TdUser = new TradeUser();
#region 判断登陆标识是否过期
if (!ComFunction.ExistUserLoginID(LoginId, ref TdUser))
{
tradeNewsInfo.Result = false;
tradeNewsInfo.ReturnCode = ResCode.UL003;
tradeNewsInfo.Desc = ResCode.UL003Desc;
return tradeNewsInfo;
}
#endregion
string newsType = string.Empty;
if(NType!= NewsType.ALL)
{
newsType = string.Format(" and NewsType={0}",(int)NType);
}
string status = string.Empty;
if (UserType.NormalType == TdUser.UType) //客户端只返回启用的新闻
{
status = string.Format(" and Status=1");
}
string sql = string.Format("select * from Trade_News where PubTime>='{0}' and PubTime<='{1}' {2} {3}", starttime.ToString("yyyy-MM-dd"),
endtime.ToString("yyyy-MM-dd"), newsType, status);
tradeNewsInfo.TradeNewsInfoList = ComFunction.GetTradeNewsInfo(sql);
tradeNewsInfo.Result = true;
tradeNewsInfo.Desc = "查询成功";
}
catch (Exception ex)
{
ComFunction.WriteErr(ex);
tradeNewsInfo.Result = false;
tradeNewsInfo.Desc = "查询失败";
}
return tradeNewsInfo;
}