Exemplo n.º 1
0
        public ApiLogFileQuery Query(ApiLogFileQuery param)
        {
            string WhereClause = string.Empty;
            var    parameters  = BuildCriteria(param, ref WhereClause);

            var strSqlStatment = new StringBuilder();

            strSqlStatment.Append("Select * from ( ");
            strSqlStatment.Append("select rownum as seq , main_data.* from ( ");
            strSqlStatment.Append("Select * from API_LOG  " + WhereClause);
            strSqlStatment.Append(") main_data ) ");
            strSqlStatment.Append($"WHERE seq > ({param.Paging.PageNo - 1}) * {param.Paging.PageSize} AND ROWNUM <= {param.Paging.PageSize}");
            var masterDataTable = this._db.GetData(strSqlStatment.ToString(), parameters);

            if (masterDataTable == null)
            {
                return(null);
            }
            if (masterDataTable.Rows.Count == 0)
            {
                return(null);
            }

            var apiLogFiles = new List <ApiLogFile>();

            foreach (DataRow row in masterDataTable.Rows)
            {
                var obj = ConvertDataRowToDataModel(row);
                apiLogFiles.Add(obj);
            }
            param.Results = apiLogFiles;

            return(param);
        }
Exemplo n.º 2
0
        private List <OracleParameter> BuildCriteria(ApiLogFileQuery param, ref string criteria)
        {
            var WhereClause = new StringBuilder();
            var parameters  = new List <OracleParameter>();

            if (!string.IsNullOrEmpty(param.Data))
            {
                var parm = new OracleParameter {
                    ParameterName = ":logData", OracleDbType = OracleDbType.Varchar2, Value = param.Data
                };
                WhereClause.Append(" WHERE log_data LIKE  '%' ||  :logData || '%' ");
                parameters.Add(parm);
            }
            if (!string.IsNullOrEmpty(param.User))
            {
                var parm = new OracleParameter {
                    ParameterName = ":logUser", OracleDbType = OracleDbType.Varchar2, Value = param.User
                };
                WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE log_user = :logUser " : " AND log_user = :logUser ");
                parameters.Add(parm);
            }
            if (!string.IsNullOrEmpty(param.Ip))
            {
                var parm = new OracleParameter {
                    ParameterName = ":logIp", OracleDbType = OracleDbType.Varchar2, Value = param.Ip
                };
                WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE log_ip = :logIp " : " AND log_ip = :logIp ");
                parameters.Add(parm);
            }
            if (!string.IsNullOrEmpty(param.Action))
            {
                var parm = new OracleParameter {
                    ParameterName = ":logAction", OracleDbType = OracleDbType.Varchar2, Value = param.Action
                };
                WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE log_action = :logAction " : " AND log_action = :logAction ");
                parameters.Add(parm);
            }
            if (param.Level != -1)
            {
                var parm = new OracleParameter {
                    ParameterName = ":logLevel", OracleDbType = OracleDbType.Int32, Value = param.Level
                };
                WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE log_level = :logLevel " : " AND log_level = :logLevel ");
                parameters.Add(parm);
            }

            if (param.From > DateTime.MinValue && param.From != null)
            {
                WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE trunc(createdon)>=:StartDate " : " AND trunc(createdon)>=:StartDate   ");
                var parm = new OracleParameter {
                    ParameterName = "StartDate", OracleDbType = OracleDbType.Date, Value = param.From
                };
                parameters.Add(parm);
            }
            if (param.To > DateTime.MinValue && param.To != null)
            {
                WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE trunc(createdon)<=:EndDate " : " AND trunc(createdon)<=:EndDate   ");
                var parm = new OracleParameter {
                    ParameterName = "EndDate", OracleDbType = OracleDbType.Date, Value = param.To
                };
                parameters.Add(parm);
            }

            WhereClause.Append(string.IsNullOrEmpty(WhereClause.ToString()) ? " WHERE ROWNUM <= 500 " : " AND ROWNUM <= 500   ");

            if (!string.IsNullOrEmpty(param.SortBy))
            {
                WhereClause.Append($" ORDER BY {param.SortBy} ");
                WhereClause.Append(!string.IsNullOrEmpty(param.SortType) ? " DESC " : param.SortType);
            }
            else
            {
                WhereClause.Append($" ORDER BY log_id desc ");
            }

            criteria = WhereClause.ToString();
            return(parameters);
        }