Esempio n. 1
0
        /// <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);
            }
        }