/// <summary>
/// 获取日志列表
/// </summary>
/// <param name="userID">用户ID</param>
/// <param name="operateType">操作类型</param>
/// <param name="content">记录内容</param>
/// <param name="startDateTime">开始时间</param>
/// <param name="endDateTime">结束时间</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public DataTable GetFileZilla(string userID, string operateType, string content, string startDateTime, string endDateTime, string fileName, int pageIndex, int pageSize, out int totalAmount)
{
totalAmount = 0;
StringBuilder strSql = new StringBuilder(@"
/* SELECT fz.OPERATEUSERID,fz.content,fz.operatedate,fz.filename,fz.ipaddress,cfg.configvalue operatetype FROM fileZillaLog fz
LEFT JOIN
(
select configkey,configvalue from configvalue cv
left join configtype ct
on cv.configtypeid=ct.configtypeid
) cfg
ON fz.operatetype=cfg.configkey
WHERE 1=1 */
SELECT fz.OPERATEUSERID,fz.content,fz.operatedate,fz.filename,fz.ipaddress,cfg.configvalue operatetype FROM fileZillaLog fz
LEFT JOIN
(
select configkey,configvalue from configvalue cv
left join configtype ct
on cv.configtypeid=ct.configtypeid
) cfg
ON fz.operatetype=cfg.configkey
WHERE fz.ID in
( SELECT Id FROM
(
SELECT ID
FROM fileZillaLog fz
WHERE 1 = 1
");
StringBuilder sbWhere = new StringBuilder();
if (!string.IsNullOrEmpty(userID))
{
sbWhere.AppendFormat(" AND OPERATEUSERID='{0}'", userID);
}
if (!string.IsNullOrEmpty(operateType))
{
sbWhere.AppendFormat(" AND OPERATETYPE='{0}'", operateType);
}
if (!string.IsNullOrEmpty(content))
{
//sbWhere.AppendFormat(" AND CONTENT LIKE '%{0}%'", content);
sbWhere.AppendFormat(" AND LOCATE('{0}', CONTENT) > 0 ", content);
}
if (!string.IsNullOrEmpty(startDateTime))
{
sbWhere.AppendFormat(" AND OPERATEDATE>='{0}'", startDateTime);
}
if (!string.IsNullOrEmpty(endDateTime))
{
sbWhere.AppendFormat(" AND OPERATEDATE<='{0}'", endDateTime);
}
if (!string.IsNullOrEmpty(fileName))
{
//sbWhere.AppendFormat(" AND FILENAME LIKE '%{0}%'", fileName);
sbWhere.AppendFormat(" AND LOCATE('{0}', FILENAME) > 0 ", fileName);
}
//DataSet dsRowsCount = MySqlHelper.GetDataSet(strSql.ToString());
//if (dsRowsCount != null && dsRowsCount.Tables.Count > 0)
//{
// totalAmount = dsRowsCount.Tables[0].Rows.Count;
//}
totalAmount = GetTotalCount(sbWhere.ToString());
//strSql.AppendFormat(" ORDER BY OPERATEDATE DESC LIMIT {0},{1}", (pageIndex - 1) * pageSize, pageSize);
strSql.Append(sbWhere).AppendFormat(" ORDER BY operatedate DESC LIMIT {0},{1}) as aa ) ", (pageIndex - 1) * pageSize, pageSize);
DataSet ds = MySqlHelper.GetDataSet(strSql.ToString());
if (ds != null && ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}