/// <summary>
/// 获得意见建议实体
/// </summary>
/// <param name="OpinionId">意见建议ID</param>
/// <returns></returns>
public EyouSoft.Model.GovStructure.MGovOpinion GetGovOpinionModel(string OpinionId)
{
EyouSoft.Model.GovStructure.MGovOpinion model = null;
if (!string.IsNullOrEmpty(OpinionId))
{
model = new EyouSoft.Model.GovStructure.MGovOpinion();
model = dal.GetGovOpinionModel(OpinionId, ItemType);
}
return(model);
}
/// <summary>
/// 获得意见建议实体
/// </summary>
/// <param name="OpinionId">意见建议ID</param>
/// <param name="ItemType">附件类型</param>
/// <returns></returns>
public EyouSoft.Model.GovStructure.MGovOpinion GetGovOpinionModel(string OpinionId, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType)
{
EyouSoft.Model.GovStructure.MGovOpinion model = null;
StringBuilder StrSql = new StringBuilder();
StrSql.Append("SELECT OpinionId,CompanyId,Title,Content,ProcessOpinion,ProcessTime,IsOpen,Submit,SubmitTime,OperatorId,IssueTime, ");
StrSql.Append(" (SELECT TOP 1 Name FROM tbl_GovFile WHERE ID=a.Submit )AS Name,");
StrSql.AppendFormat(" (SELECT Name,FilePath,Size,Downloads FROM tbl_ComAttach WHERE ItemType={0} AND ItemId=a.OpinionId FOR XML RAW,ROOT('ROOT'))AS ComAttachXML,", (int)ItemType);
StrSql.Append(" (SELECT ID,Name FROM tbl_GovFile WHERE ID IN(SELECT UserId from tbl_GovOpinionUser WHERE OpinionId=a.OpinionId) FOR XML RAW,ROOT('ROOT'))AS OpinionUserXML ");
StrSql.AppendFormat(" FROM tbl_GovOpinion a WHERE OpinionId='{0}' ", OpinionId);
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(dc, this._db))
{
if (dr.Read())
{
model = new EyouSoft.Model.GovStructure.MGovOpinion();
model.OpinionId = dr.GetString(dr.GetOrdinal("OpinionId"));
model.CompanyId = dr.GetString(dr.GetOrdinal("CompanyId"));
model.Title = dr.IsDBNull(dr.GetOrdinal("Title")) ? "" : dr.GetString(dr.GetOrdinal("Title"));
model.Content = dr.IsDBNull(dr.GetOrdinal("Content")) ? "" : dr.GetString(dr.GetOrdinal("Content"));
model.ProcessOpinion = dr.IsDBNull(dr.GetOrdinal("ProcessOpinion")) ? "" : dr.GetString(dr.GetOrdinal("ProcessOpinion"));
if (!dr.IsDBNull(dr.GetOrdinal("ProcessTime")))
{
model.ProcessTime = dr.GetDateTime(dr.GetOrdinal("ProcessTime"));
}
model.IsOpen = dr.GetString(dr.GetOrdinal("IsOpen")) == "1" ? true : false;
model.Submit = dr.IsDBNull(dr.GetOrdinal("Submit")) ? "" : dr.GetString(dr.GetOrdinal("Submit"));
if (!dr.IsDBNull(dr.GetOrdinal("SubmitTime")))
{
model.SubmitTime = dr.GetDateTime(dr.GetOrdinal("SubmitTime"));
}
model.OperatorId = dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId"));
model.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
model.ComAttachList = this.GetAttachList(dr["ComAttachXML"].ToString(), OpinionId, ItemType);
model.MGovOpinionUserList = this.GetOpinionUserList(dr["OpinionUserXML"].ToString(), OpinionId);
}
};
return(model);
}
/// <summary>
/// 根据条件意见建议信息列表
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="MSearchMeeting">查询参数类</param>
/// <param name="PageSize">页面数据总记录数</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RecordCount">每页显示的记录数</param>
/// <returns></returns>
public IList <Model.GovStructure.MGovOpinion> GetGovOpinionList(string CompanyId, Model.GovStructure.MSearchOpinion MSearchOpinion, int PageSize, int PageIndex, ref int RecordCount)
{
IList <EyouSoft.Model.GovStructure.MGovOpinion> ResultList = null;
string tableName = "view_GovOpinion";
string identityColumnName = "OpinionId";
string fields = "OpinionId,CompanyId,Title,ProcessTime,IsOpen,Submit,SubmitTime,OperatorId,IssueTime,Name,OpinionUserXML ";
string query = string.Format(" CompanyId='{0}'", CompanyId);
if (MSearchOpinion != null)
{
if (!string.IsNullOrEmpty(MSearchOpinion.Title))
{
query = query + string.Format(" AND Title LIKE '%{0}%'", MSearchOpinion.Title);
}
if (!string.IsNullOrEmpty(MSearchOpinion.Submit))
{
query = query + string.Format(" AND Submit = '{0}'", MSearchOpinion.Submit);
}
if (MSearchOpinion.SubmitTime != null)
{
query = query + string.Format(" AND datediff(dd, '{0}', SubmitTime) = 0", MSearchOpinion.SubmitTime);
}
if (!string.IsNullOrEmpty(MSearchOpinion.OpinionUserId))
{
//query = query + string.Format(" AND CAST(OpinionUserXML AS XML).exist('/ROOT/row[@ID=sql:variable(\"{0}\")]') = 1", MSearchOpinion.OpinionUserId);
query = query + string.Format(" AND CAST(OpinionUserXML AS XML).exist('/ROOT/row/@ID[.=\"{0}\"]') = 1", MSearchOpinion.OpinionUserId);
}
if (MSearchOpinion.ProcessTime != null)
{
query = query + string.Format(" AND datediff(dd, '{0}', ProcessTime) = 0", MSearchOpinion.ProcessTime);
}
if (!string.IsNullOrEmpty(MSearchOpinion.Status))//状态1：未处理,2：已处理
{
if (MSearchOpinion.Status.Equals("1"))
{
query = query + " AND ProcessTime is null ";
}
if (MSearchOpinion.Status.Equals("2"))
{
query = query + " AND ProcessTime is not null ";
}
}
}
string orderByString = " IssueTime DESC";
using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(_db, PageSize, PageIndex, ref RecordCount, tableName, identityColumnName, fields, query, orderByString))
{
ResultList = new List <EyouSoft.Model.GovStructure.MGovOpinion>();
EyouSoft.Model.GovStructure.MGovOpinion model = null;
while (dr.Read())
{
model = new EyouSoft.Model.GovStructure.MGovOpinion();
model.OpinionId = dr.GetString(dr.GetOrdinal("OpinionId"));
model.CompanyId = dr.GetString(dr.GetOrdinal("CompanyId"));
model.Title = dr.IsDBNull(dr.GetOrdinal("Title")) ? "" : dr.GetString(dr.GetOrdinal("Title"));
if (!dr.IsDBNull(dr.GetOrdinal("ProcessTime")))
{
model.ProcessTime = dr.GetDateTime(dr.GetOrdinal("ProcessTime"));
model.Status = "2";
}
else
{
model.Status = "1";
}
model.Operator = dr.IsDBNull(dr.GetOrdinal("Name")) ? "" : dr.GetString(dr.GetOrdinal("Name"));
model.Submit = dr.IsDBNull(dr.GetOrdinal("Submit")) ? "" : dr.GetString(dr.GetOrdinal("Submit"));
if (!dr.IsDBNull(dr.GetOrdinal("Submit")))
{
model.SubmitTime = dr.GetDateTime(dr.GetOrdinal("SubmitTime"));
}
model.OperatorId = dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId"));
model.IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime"));
model.MGovOpinionUserList = this.GetOpinionUserList(dr["OpinionUserXML"].ToString(), dr.GetString(dr.GetOrdinal("OpinionId")));
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
