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); }
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); }