///// <summary>
// /// 获取假日,不统计所有人，只统计当前用户
///// </summary>
///// <param name="pBeginTime"></param>
///// <param name="pEndTime"></param>
///// <param name="pUserid"></param>
///// <returns></returns>
// public DataSet GetRestDateDataSet(string pBeginTime, string pEndTime, int pUserid)
// {
// DataSet _ds = new DataSet("result");
// OleDbParameter[] parms = new OleDbParameter[] {
// new OleDbParameter(Parm_BeginDate,OleDbType.VarChar,50),
// new OleDbParameter(Parm_EndDate,OleDbType.VarChar,50),
// new OleDbParameter(Parm_UserId,OleDbType.Integer)
// };
// parms[0].Value = this.GetFullDate(pBeginTime);
// parms[1].Value = this.GetFullDate(pEndTime);
// parms[2].Value = pUserId;
// }
/// <summary>
/// 获取假日
/// </summary>
/// <param name="pBeginTime">开始日期</param>
/// <param name="pEndTime">结束日期</param>
/// <param name="pUserId">用户Id</param>
/// <param name="pIsAddAll">是否包含全部公用假日</param>
/// <returns></returns>
public DataSet GetRestDateDataSet(string pBeginTime, string pEndTime, int pUserId, bool pIsAddAll)
{
DataSet objDs = new DataSet("result");
string _sqlstr = string.Empty;
OleDbParameter[] parms = null;
parms = new OleDbParameter[] {
new OleDbParameter(Parm_BeginDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar)
};
parms[0].Value = this.GetFullDate(pBeginTime);
parms[1].Value = this.GetFullDate(pEndTime);
if (pIsAddAll)
{
_sqlstr = string.Format(SQL_Select_RestDateAllByBegin_End, "( UserId= " + pUserId.ToString() + " or UserId=-1 )");
}
else
{
_sqlstr = string.Format(SQL_Select_RestDateAllByBegin_End, "( UserId= " + pUserId.ToString() + " )");
}
try
{
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, _sqlstr, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
public DataSet GetUserIdDataSetByRestDate(string pRestDate)
{
DataSet objDs = new DataSet("result");
string _sqlstr = string.Empty;
OleDbParameter[] parms = null;
/*parms = new OleDbParameter[] {
* new OleDbParameter(Parm_BeginDate,OleDbType.VarChar),
* new OleDbParameter(Parm_EndDate,OleDbType.VarChar)
* };
* parms[0].Value = this.GetFullDate(pBeginTime);
* parms[1].Value = this.GetFullDate(pEndTime);
*
*/
/*
* if (pIsAddAll)
* {
* _sqlstr = string.Format(SQL_Select_RestDateAllByBegin_End, "( UserId= " + pUserId.ToString() + " or UserId=-1 )");
* }
* else
* {
* _sqlstr = string.Format(SQL_Select_RestDateAllByBegin_End, "( UserId= " + pUserId.ToString() + " )");
* }*/
_sqlstr = string.Format(SQL_Select_RestDateAll, pRestDate);
try
{
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, _sqlstr, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
/// <summary>
/// 获取某个状态下的任务
/// </summary>
/// <param name="status"></param>
/// <param name="beginTime"></param>
/// <param name="endTime"></param>
/// <param name="KeyWord"></param>
/// <returns></returns>
public DataSet GetMissionDataSet(string status, string beginTime, string endTime, string KeyWord)
{
string tempSqlStr = string.Empty;
DataSet objDs = new DataSet("result");
try
{
OleDbParameter[] parms = new OleDbParameter[] {
new OleDbParameter(Parm_WriteDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar)
};
parms[0].Value = beginTime;
parms[1].Value = endTime;
string sql = string.Empty;
tempSqlStr += " and ExecStatus='" + status + "'";
tempSqlStr += " and MissionDesc like '%" + KeyWord + "%'";
sql = string.Format(SQL_Select_MissionList, tempSqlStr);
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, sql, parms);
}
catch (Exception ex)
{
}
//return objDs;
return(this.CountFinishDateByWorkHour(objDs));
}
/// <summary>
/// 获取数据集合
/// </summary>
/// <param name="uMission">传入UserMission对象</param>
/// <returns></returns>
public DataSet GetMissionDataSet(string userid, string status, string beginTime, string endTime, int pageSize, int currentPage, string IsAsgin, bool IsSearch, string KeyWord, string typeMission)
{
string tempSqlStr = "";
DataSet objDs = new DataSet("result");
try
{
OleDbParameter[] parms = new OleDbParameter[] {
new OleDbParameter(Parm_WriteDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar)
};
parms[0].Value = beginTime;
parms[1].Value = endTime;
string sql = string.Empty;
if (IsAsgin == "1") //已经分配
{
if (userid != "-1")
{
tempSqlStr += " and userid=" + userid + "";
}
else
{
tempSqlStr += " and userid<>0";
}
}
else
{
tempSqlStr += " and userid=0";
}
if (status != "-1")
{
tempSqlStr += " and ExecStatus='" + status + "'";
}
if (IsSearch)
{
tempSqlStr += " and MissionDesc like '%" + KeyWord + "%'";
}
if (typeMission != "-1")
{
tempSqlStr += " and MissionType='" + typeMission + "'";
}
if (currentPage == 1)
{
sql = string.Format(SQL_Select_MissionFirst, pageSize, tempSqlStr, tempSqlStr);
}
else
{
sql = string.Format(SQL_Select_MissionInfo, pageSize, pageSize * (currentPage - 1), tempSqlStr, tempSqlStr);
}
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, sql, parms);
}
catch (Exception ex)
{
}
//return objDs;
return(this.CountFinishDateByWorkHour(objDs));
}
/// <summary>
/// 获取用户的集合信息
/// </summary>
/// <param name="keyword">关键字</param>
/// <param name="inUse"></param>
/// <returns></returns>
public DataSet GetJournalDataSet(string keyword, string beginTime, string endTime, int pageSize, int currentPage, bool isExport)
{
if (keyword == "-1")
{
keyword = string.Empty;
}
DataSet objDs = new DataSet("result");
try
{
OleDbParameter[] parms = new OleDbParameter[] {
new OleDbParameter(Parm_WriteTime, OleDbType.VarChar),
new OleDbParameter(Parm_EndTime, OleDbType.VarChar)
};
parms[0].Value = beginTime;
parms[1].Value = endTime;
if (!string.IsNullOrEmpty(keyword))
{
keyword = "and userid=" + keyword + "";
}
string sql = string.Empty;
if (isExport)
{
//sql = SQL_Select_JournalInfoAll;
sql = string.Format(SQL_Select_JournalList, keyword);
}
else
{
if (currentPage == 1)
{
sql = string.Format(SQL_Select_JournalInfoFirst, pageSize, keyword);
}
else
{
sql = string.Format(SQL_Select_JournalInfo, pageSize, pageSize * (currentPage - 1), keyword, keyword);
}
}
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, sql, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
/// <summary>
/// 获取分页列表内容
/// </summary>
/// <param name="DocType">文档类型</param>
/// <param name="BeginTime">开始时间</param>
/// <param name="EndTime">结束时间</param>
/// <param name="pageSize">当前页大小</param>
/// <param name="currentPage">当前页码</param>
/// <param name="KeyWord">标题关键字</param>
/// <returns></returns>
public DataSet GetFileDataSet(string DocType, string BeginTime, string EndTime, int pageSize, int currentPage, string KeyWord)
{
string tempSqlStr = "";
DataSet objDs = new DataSet("result");
try
{
OleDbParameter[] parms = new OleDbParameter[] {
new OleDbParameter(Parm_WriteDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndTime, OleDbType.VarChar)
};
parms[0].Value = BeginTime;
parms[1].Value = EndTime;
string sql = string.Empty;
tempSqlStr += " and FileTitle like '%" + KeyWord + "%'";
if (DocType != "全部")
{
tempSqlStr += " and FileType='" + DocType + "'";
}
if (currentPage == 1)
{
sql = string.Format(SQL_Select_FileFirst, pageSize, tempSqlStr, tempSqlStr);
}
else
{
sql = string.Format(SQL_Select_FileList, pageSize, pageSize * (currentPage - 1), tempSqlStr, tempSqlStr);
}
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, sql, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
/// <summary>
/// 根据开始日期和结束日期，用户Id，查询期间的所有日志日期，里面只有一个属性是writetime
/// </summary>
/// <param name="pBeginTime"></param>
/// <param name="pEndTime"></param>
/// <param name="pUserid"></param>
/// <returns></returns>
public DataSet GetNoWriteJournal(string pBeginTime, string pEndTime, int pUserid)
{
DataSet _ds = new DataSet("Result");
string _sqlstr = string.Empty;
OleDbParameter[] _param = new OleDbParameter[] {
new OleDbParameter(Parm_BeginTime, OleDbType.VarChar, 50),
new OleDbParameter(Parm_EndTime, OleDbType.VarChar, 50),
new OleDbParameter(Parm_UserId, OleDbType.Integer)
};
_param[0].Value = NetRestDate.GetFullDateString(pBeginTime);
_param[1].Value = NetRestDate.GetFullDateString(pEndTime);
_param[2].Value = pUserid;
try
{
_sqlstr = SQL_Select_SingleJournalByUserId;
_ds = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, _sqlstr, _param);
}
catch (Exception ex)
{
}
return(_ds);
}
/// <summary>
/// 根据用户编号获取执行中的任务
/// </summary>
/// <param name="userid">用户编号</param>
/// <param name="beginTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <returns></returns>
public DataSet GetDataSetByStatus(string userid, string beginTime, string endTime)
{
DataSet objDs = new DataSet("result");
try
{
OleDbParameter[] parms = new OleDbParameter[] {
//new OleDbParameter(Parm_WriteDate,OleDbType.VarChar),
//new OleDbParameter(Parm_EndDate,OleDbType.VarChar),
new OleDbParameter(Parm_UserId, OleDbType.VarChar)
};
//parms[0].Value = beginTime;
//parms[1].Value = endTime;
parms[0].Value = userid;
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, SQL_Select_MissionByStatus, parms);
}
catch (Exception ex)
{
}
//return objDs;
return(this.CountFinishDateByWorkHour(objDs));
}
/// <summary>
/// 获取用户的集合信息
/// </summary>
/// <param name="keyword">关键字</param>
/// <param name="inUse"></param>
/// <returns></returns>
public DataSet GetUserInfos(string keyword, string groupID, int pageSize, int currentPage, bool isView)
{
DataSet objDs = new DataSet("result");
OleDbParameter[] parms = null;
string sql = string.Empty;
try
{
if (isView)
{
sql = SQL_Select_UserAll;
}
else
{
parms = new OleDbParameter[] {
new OleDbParameter(Parm_GroupId, OleDbType.VarChar, 200)
};
parms[0].Value = groupID;
if (currentPage == 1)
{
sql = string.Format(SQL_Select_UserInfoFirst, pageSize);
}
else
{
sql = string.Format(SQL_Select_UserInfo, pageSize, pageSize * (currentPage - 1));
}
}
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, sql, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
public List <string> GetNoWriteList(ref string beginTime)
{
DateTime _begin = Convert.ToDateTime(beginTime);
int i = 30;
NetRestDate _netRestDate = new NetRestDate();
while (i > 0)
{
i--;
int _dateCount = _netRestDate.GetRestDateCount(_begin.ToShortDateString());
if (_dateCount > 0)
{
_begin = _begin.AddDays(-1);
}
else
{
break;
}
}
beginTime = _begin.ToString("yyyy-MM-dd");
DataSet objDs = new DataSet("reslut");
List <string> _nameList = new List <string>();
try
{
OleDbParameter[] parms = new OleDbParameter[] {
new OleDbParameter(Parm_WriteTime, OleDbType.VarChar)
};
parms[0].Value = _begin.ToString("yyyy-MM-dd");
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, SQL_Select_JournalIsWC, parms);
}
catch (Exception ex)
{
}
if (objDs == null || objDs.Tables.Count == 0)
{
return(null);
}
int _count = objDs.Tables[0].Rows.Count;
if (_count > 0)
{
//NetRestDate _netRestDate = new NetRestDate();
DataSet _nameDS = _netRestDate.GetUserIdDataSetByRestDate(_begin.ToString("yyyy-MM-dd"));
for (int j = 0; j < _count; j++)
{
string _userId = objDs.Tables[0].Rows[j]["userid"].ToString();
if (!this.IsNameInRestNameList(_userId, _nameDS))
{
_nameList.Add(objDs.Tables[0].Rows[j]["realName"].ToString());
}
}
}
return(_nameList);
}
/// <summary>
/// 获取假日
/// </summary>
/// <param name="pBeginTime">开始日期</param>
/// <param name="pEndTime">结束日期</param>
/// <param name="pType">假日类型，若想查全部，输入“请选择”</param>
/// <param name="pUserId">用户Id，若想查所有人，输入“-2”</param>
/// <returns></returns>
public DataSet GetRestDateDataSet(string pBeginTime, string pEndTime, string pType, int pUserId)
{
DataSet objDs = new DataSet("result");
string _sqlstr = string.Empty;
OleDbParameter[] parms = null;
if (pEndTime == string.Empty)
{
parms = new OleDbParameter[] {
new OleDbParameter(Parm_BeginDate, OleDbType.VarChar),
new OleDbParameter(Parm_UserId, OleDbType.Integer),
new OleDbParameter(Parm_UserId, OleDbType.Integer)
};
parms[0].Value = this.GetFullDate(pBeginTime);
parms[1].Value = -1;
parms[2].Value = pUserId;
_sqlstr = SQL_Select_RestDateAllByBeginAndUserId;
try
{
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, _sqlstr, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
if (pType != "请选择" && pUserId != -2)
{
parms = new OleDbParameter[] {
new OleDbParameter(Parm_BeginDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar),
new OleDbParameter(Parm_Type, OleDbType.VarChar),
new OleDbParameter(Parm_UserId, OleDbType.Integer)
};
parms[0].Value = this.GetFullDate(pBeginTime);
parms[1].Value = this.GetFullDate(pEndTime);
parms[2].Value = pType;
parms[3].Value = pUserId;
_sqlstr = SQL_Select_RestDateAllByTypeAndUserId;
}
else if (pType == "请选择" && pUserId != -2)
{
parms = new OleDbParameter[] {
new OleDbParameter(Parm_BeginDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar),
new OleDbParameter(Parm_UserId, OleDbType.Integer)
};
parms[0].Value = this.GetFullDate(pBeginTime);
parms[1].Value = this.GetFullDate(pEndTime);
parms[2].Value = pUserId;
_sqlstr = SQL_Select_RestDateByUserId;
}
else if (pType != "请选择" && pUserId == -2)
{
parms = new OleDbParameter[] {
new OleDbParameter(Parm_BeginDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar),
new OleDbParameter(Parm_Type, OleDbType.VarChar)
};
parms[0].Value = this.GetFullDate(pBeginTime);
parms[1].Value = this.GetFullDate(pEndTime);
parms[2].Value = pType;
_sqlstr = SQL_Select_RestDateByType;
}
else
{
parms = new OleDbParameter[] {
new OleDbParameter(Parm_BeginDate, OleDbType.VarChar),
new OleDbParameter(Parm_EndDate, OleDbType.VarChar)
};
parms[0].Value = this.GetFullDate(pBeginTime);
parms[1].Value = this.GetFullDate(pEndTime);
_sqlstr = SQL_Select_RestDateAll;
}
try
{
objDs = AccessHelper.ExcuteDataSet(AccessHelper.CONN_STRING, CommandType.Text, _sqlstr, parms);
}
catch (Exception ex)
{
}
return(objDs);
}
