/// <summary>
/// 生成附件集合List
/// </summary>
/// <param name="AttendanceXml">附件信息XML</param>
/// <returns></returns>
private IList <EyouSoft.Model.ComStructure.MComAttach> GetAttachList(string ComAttachXML, string NoticeId, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType)
{
if (string.IsNullOrEmpty(ComAttachXML))
{
return(null);
}
IList <EyouSoft.Model.ComStructure.MComAttach> ResultList = null;
ResultList = new List <EyouSoft.Model.ComStructure.MComAttach>();
XElement root = XElement.Parse(ComAttachXML);
IEnumerable <XElement> xRow = root.Elements("row");
foreach (XElement tmp1 in xRow)
{
EyouSoft.Model.ComStructure.MComAttach model = new EyouSoft.Model.ComStructure.MComAttach()
{
Name = tmp1.Attribute("Name").Value,
FilePath = tmp1.Attribute("FilePath").Value,
Size = int.Parse(tmp1.Attribute("Size").Value),
Downloads = int.Parse(tmp1.Attribute("Downloads").Value),
ItemId = NoticeId,
ItemType = ItemType
};
ResultList.Add(model);
model = null;
}
return(ResultList);
}
/// <summary>
/// 根据接收类型获得公告通知信息列表
/// </summary>
/// <param name="NoticeType">消息类型</param>
/// <param name="CompanyId">公司编号</param>
/// <param name="Type">接收类型</param>
/// <param name="ItemType">附件类型</param>
/// <param name="PageSize">页面数据总记录数</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RecordCount">每页显示的记录数</param>
/// <returns></returns>
public IList <Model.GovStructure.MGovNotice> GetGovNoticeList(string NoticeType, string CompanyId, EyouSoft.Model.EnumType.GovStructure.ItemType Type, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType, int PageSize, int PageIndex, ref int RecordCount)
{
IList <EyouSoft.Model.GovStructure.MGovNotice> ResultList = null;
string tableName = "view_GovNotice";
string identityColumnName = "NoticeId";
string fields = "NoticeId,CompanyId,Title,IsRemind,[Views],DepartId,Operator,OperatorId,IssueTime ";
string query = string.Format(" (NoticeType='{0}') AND CompanyId='{1}'", NoticeType, CompanyId);
query = query + string.Format(" AND CAST(NoticeReceiverXML AS XML).exist('/ROOT/row/@ItemType[.=\"{0}\"]') = 1", (int)Type);
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.MGovNotice>();
while (dr.Read())
{
EyouSoft.Model.GovStructure.MGovNotice model = new EyouSoft.Model.GovStructure.MGovNotice()
{
NoticeId = dr.GetString(dr.GetOrdinal("NoticeId")),
CompanyId = dr.GetString(dr.GetOrdinal("CompanyId")),
Title = dr.IsDBNull(dr.GetOrdinal("Title")) ? "" : dr.GetString(dr.GetOrdinal("Title")),
//Content = dr.IsDBNull(dr.GetOrdinal("Content")) ? "" : dr.GetString(dr.GetOrdinal("Content")),
IsRemind = dr.GetString(dr.GetOrdinal("IsRemind")) == "1" ? true : false,
Views = dr.IsDBNull(dr.GetOrdinal("Views")) ? 0 : dr.GetInt32(dr.GetOrdinal("Views")),
OperatorId = dr.GetString(dr.GetOrdinal("OperatorId")),
IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime")),
Operator = dr.IsDBNull(dr.GetOrdinal("Operator")) ? "" : dr.GetString(dr.GetOrdinal("Operator")),
//MGovNoticeReceiverList = this.GetNoticeReceiveList(dr["NoticeReceiverXML"].ToString()),
//ComAttachList = this.GetAttachList(dr["ComAttachXML"].ToString(), dr.GetString(dr.GetOrdinal("NoticeId")), ItemType)
};
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <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="NoticeId">公告通知ID</param>
/// <param name="ItemType">附件类型</param>
/// <returns></returns>
public EyouSoft.Model.GovStructure.MGovNotice GetGovNoticeModel(string NoticeId, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType)
{
EyouSoft.Model.GovStructure.MGovNotice model = null;
StringBuilder StrSql = new StringBuilder();
StrSql.Append("SELECT NoticeId,CompanyId,Title,[Content],IsRemind,IsMsg,MsgContent,DepartId,[Views],Operator,OperatorId,IssueTime, ");
StrSql.AppendFormat(" (SELECT Name,FilePath,Size,Downloads FROM tbl_ComAttach WHERE ItemType={0} AND ItemId=a.NoticeId FOR XML RAW,ROOT('ROOT'))AS ComAttachXML,", (int)ItemType);
StrSql.Append(" (SELECT ItemType,ItemId FROM tbl_GovNoticeReceiver WHERE NoticeId=a.NoticeId FOR XML RAW,ROOT('ROOT'))AS NoticeReceiverXML ");
StrSql.AppendFormat(" FROM tbl_GovNotice a WHERE NoticeId='{0}' ", NoticeId);
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.MGovNotice()
{
NoticeId = dr.GetString(dr.GetOrdinal("NoticeId")),
CompanyId = dr.GetString(dr.GetOrdinal("CompanyId")),
Title = dr.IsDBNull(dr.GetOrdinal("Title")) ? "" : dr.GetString(dr.GetOrdinal("Title")),
Content = dr.IsDBNull(dr.GetOrdinal("Content")) ? "" : dr.GetString(dr.GetOrdinal("Content")),
IsRemind = dr.GetString(dr.GetOrdinal("IsRemind")) == "1" ? true : false,
IsMsg = dr.GetString(dr.GetOrdinal("IsMsg")) == "1" ? true : false,
MsgContent = dr.IsDBNull(dr.GetOrdinal("MsgContent")) ? "" : dr.GetString(dr.GetOrdinal("MsgContent")),
Views = dr.GetInt32(dr.GetOrdinal("Views")),
OperatorId = dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? "" : dr.GetString(dr.GetOrdinal("OperatorId")),
IssueTime = dr.GetDateTime(dr.GetOrdinal("IssueTime")),
Operator = dr.GetString(dr.GetOrdinal("Operator")),
ComAttachList = this.GetAttachList(dr["ComAttachXML"].ToString(), NoticeId, ItemType),
MGovNoticeReceiverList = this.GetNoticeReceiveList(dr["NoticeReceiverXML"].ToString(), NoticeId)
};
}
};
return(model);
}
/// <summary>
/// 更新合同
/// </summary>
/// <param name="model">合同model</param>
/// <returns></returns>
public bool UpdateGovContract(EyouSoft.Model.GovStructure.MGovContract model, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType)
{
bool IsTrue = false;
DbCommand dc = this._db.GetStoredProcCommand("proc_GovContract_Update");
this._db.AddInParameter(dc, "ComAttachXML", DbType.Xml, CreateComAttachListXML(model.ComAttachList));
this._db.AddInParameter(dc, "ItemType", DbType.Byte, (int)ItemType);
this._db.AddInParameter(dc, "ID", DbType.AnsiStringFixedLength, model.ID);
this._db.AddInParameter(dc, "CompanyId", DbType.AnsiStringFixedLength, model.CompanyId);
this._db.AddInParameter(dc, "Number", DbType.String, model.Number);
this._db.AddInParameter(dc, "Type", DbType.String, model.Type);
this._db.AddInParameter(dc, "SignedTime", DbType.DateTime, model.SignedTime);
this._db.AddInParameter(dc, "MaturityTime", DbType.DateTime, model.MaturityTime);
this._db.AddInParameter(dc, "Company", DbType.String, model.Company);
this._db.AddInParameter(dc, "IsRemind", DbType.AnsiStringFixedLength, model.IsRemind == true ? "1" : "0");//1:是，0:否
this._db.AddInParameter(dc, "Description", DbType.String, model.Description);
this._db.AddInParameter(dc, "signierId", DbType.AnsiStringFixedLength, model.signierId);
this._db.AddInParameter(dc, "SignedDepId", DbType.Int32, model.SignedDepId);
this._db.AddInParameter(dc, "OperatorId", DbType.AnsiStringFixedLength, model.OperatorId);
this._db.AddInParameter(dc, "IssueTime", DbType.DateTime, model.IssueTime);
this._db.AddOutParameter(dc, "Result", DbType.Int32, 4);
EyouSoft.Toolkit.DAL.DbHelper.RunProcedure(dc, this._db);
object Result = this._db.GetParameterValue(dc, "Result");
if (!Result.Equals(null))
{
IsTrue = int.Parse(Result.ToString()) > 0 ? true : false;
}
return(IsTrue);
}
/// <summary>
/// 根据条件合同编号列表
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="Type">合同类型</param>
/// <param name="TimeBegin">签订时间</param>
/// <param name="TimeEnd">到期时间</param>
/// <param name="ItemType">附件类型</param>
/// <param name="PageSize">页面数据总记录数</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RecordCount">每页显示的记录数</param>
/// <returns></returns>
public IList <Model.GovStructure.MGovContract> GetGovContractList(string CompanyId, string Type, DateTime?TimeBegin, DateTime?TimeEnd, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType, int PageSize, int PageIndex, ref int RecordCount)
{
if (CompanyId.Trim() == "")
{
return(null);
}
IList <Model.GovStructure.MGovContract> ResultList = null;
string tableName = "view_GovContract";
string identityColumnName = "ID";
string fields = " ID,CompanyId,Number,Type,MaturityTime,[Description],OperatorId,IssueTime,ComAttachXML ";
string query = string.Format(" CompanyId='{0}' ", CompanyId, (int)ItemType);
if (!string.IsNullOrEmpty(Type))
{
query = query + string.Format(" AND [Type] LIKE '%{0}%' ", Type);
}
if (TimeBegin != null)
{
query = query + string.Format(" AND [MaturityTime] >= '{0}' ", TimeBegin.Value.ToShortDateString() + " 00:00:00");
}
if (TimeEnd != null)
{
query = query + string.Format(" AND [MaturityTime] <= '{0}' ", TimeEnd.Value.ToShortDateString() + " 23:59:59");
}
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 <Model.GovStructure.MGovContract>();
while (dr.Read())
{
EyouSoft.Model.GovStructure.MGovContract model = new EyouSoft.Model.GovStructure.MGovContract();
model.ID = dr.GetString(dr.GetOrdinal("ID"));
model.CompanyId = dr.GetString(dr.GetOrdinal("CompanyId"));
model.Number = dr.GetString(dr.GetOrdinal("Number"));
model.Type = dr.IsDBNull(dr.GetOrdinal("Type")) ? "" : dr.GetString(dr.GetOrdinal("Type"));
model.Description = dr.IsDBNull(dr.GetOrdinal("Description")) ? "" : dr.GetString(dr.GetOrdinal("Description"));
model.OperatorId = !dr.IsDBNull(dr.GetOrdinal("OperatorId")) ? dr.GetString(dr.GetOrdinal("OperatorId")) : "";
if (!dr.IsDBNull(dr.GetOrdinal("MaturityTime")))
{
model.MaturityTime = dr.GetDateTime(dr.GetOrdinal("MaturityTime"));
}
model.ComAttachList = this.GetAttachList(dr["ComAttachXML"].ToString(), dr.GetString(dr.GetOrdinal("ID")), ItemType);
ResultList.Add(model);
model = null;
}
};
return(ResultList);
}
/// <summary>
/// 获得合同实体
/// </summary>
/// <param name="ID">合同ID</param>
/// <returns></returns>
public EyouSoft.Model.GovStructure.MGovContract GetGovContractModel(string ID, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType)
{
EyouSoft.Model.GovStructure.MGovContract model = null;
StringBuilder StrSql = new StringBuilder();
StrSql.Append("SELECT ID,CompanyId,Number,Type,SignedTime,MaturityTime,Company,IsRemind,Description,signierId,SignedDepId,OperatorId,IssueTime,");
StrSql.Append(" (SELECT TOP 1 Name FROM tbl_GovFile WHERE ID=a.signierId )AS signier,");
StrSql.Append(" (SELECT top 1 DepartName FROM tbl_ComDepartment WHERE DepartId=a.SignedDepId )AS SignedDep,");
StrSql.AppendFormat(" (SELECT Name,FilePath,Size,Downloads FROM tbl_ComAttach WHERE ItemType={0} AND ItemId=a.ID FOR XML RAW,ROOT('ROOT'))AS ComAttachXML ", (int)ItemType);
StrSql.AppendFormat(" FROM tbl_GovContract AS a WHERE A.ID = '{0}'", ID);
DbCommand dc = this._db.GetSqlStringCommand(StrSql.ToString());
//this._db.AddInParameter(dc, "ID", DbType.AnsiStringFixedLength, ID);
using (IDataReader dr = EyouSoft.Toolkit.DAL.DbHelper.ExecuteReader(dc, _db))
{
if (dr.Read())
{
model = new EyouSoft.Model.GovStructure.MGovContract();
model.ID = dr.GetString(dr.GetOrdinal("ID"));
model.CompanyId = dr.GetString(dr.GetOrdinal("CompanyId"));
model.Number = dr.IsDBNull(dr.GetOrdinal("Number")) ? "" : dr.GetString(dr.GetOrdinal("Number"));
model.Type = dr.IsDBNull(dr.GetOrdinal("Type")) ? "" : dr.GetString(dr.GetOrdinal("Type"));
if (!dr.IsDBNull(dr.GetOrdinal("SignedTime")))
{
model.SignedTime = dr.GetDateTime(dr.GetOrdinal("SignedTime"));
}
if (!dr.IsDBNull(dr.GetOrdinal("MaturityTime")))
{
model.MaturityTime = dr.GetDateTime(dr.GetOrdinal("MaturityTime"));
}
model.Company = dr.IsDBNull(dr.GetOrdinal("Company")) ? "" : dr.GetString(dr.GetOrdinal("Company"));
model.Description = dr.IsDBNull(dr.GetOrdinal("Description")) ? "" : dr.GetString(dr.GetOrdinal("Description"));
model.IsRemind = dr.GetString(dr.GetOrdinal("IsRemind")) == "1" ? true : false;
model.signierId = dr.IsDBNull(dr.GetOrdinal("signierId")) ? "" : dr.GetString(dr.GetOrdinal("signierId"));
model.SignedDepId = dr.IsDBNull(dr.GetOrdinal("SignedDepId")) ? 0 : dr.GetInt32(dr.GetOrdinal("SignedDepId"));
model.signier = dr.IsDBNull(dr.GetOrdinal("signier")) ? "" : dr.GetString(dr.GetOrdinal("signier"));
model.SignedDep = dr.IsDBNull(dr.GetOrdinal("SignedDep")) ? "" : dr.GetString(dr.GetOrdinal("SignedDep"));
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(), ID, ItemType);
}
}
return(model);
}
/// <summary>
/// 获得规章制度信息列表
/// </summary>
/// <param name="CompanyId">公司编号</param>
/// <param name="SearchModel">查询实体</param>
/// <param name="PageSize">每页记录数</param>
/// <param name="PageIndex">当前页数</param>
/// <param name="RecordCount">总记记录数</param>
/// <returns></returns>
public IList <Model.GovStructure.MGovRegulation> GetGovRegulationList(string CompanyId, EyouSoft.Model.GovStructure.MGovRegSearch SearchModel, EyouSoft.Model.EnumType.ComStructure.AttachItemType ItemType, int PageSize, int PageIndex, ref int RecordCount)
{
IList <Model.GovStructure.MGovRegulation> list = null;
if (!string.IsNullOrEmpty(CompanyId))
{
list = new List <Model.GovStructure.MGovRegulation>();
list = dal.GetGovRegulationList(CompanyId, SearchModel, ItemType, PageSize, PageIndex, ref RecordCount);
}
return(list);
}
