///// <summary> ///// Get Payment Type ///// </summary> ///// <param name="paymentTypeID"></param> ///// <returns></returns> //public PaymentType GetPaymentType(Guid paymentTypeID) //{ // var paymentInformation = dbConnection.PaymentTypes.First(p => p.PaymentTypeID == paymentTypeID); // PaymentType paymentType = paymentInformation as PaymentType; // return paymentInformation; //} /// <summary> /// SysLog Inquiry /// </summary> /// <param name="firstName"></param> /// <param name="lastName"></param> /// <param name="paging"></param> /// <returns></returns> public List <SysLogInquiry> SysLogInquiry(string queryStr, DataGridPagingInformation paging) { string sortExpression = paging.SortExpression; if (paging.SortDirection != string.Empty) { sortExpression = sortExpression + " " + paging.SortDirection; } var sysLogQuery = dbConnection.SysLogs.AsQueryable(); if (queryStr != null && queryStr.Trim().Length > 0) { sysLogQuery = sysLogQuery.Where(c => c.Id.StartsWith(queryStr)); } //if (lastName != null && lastName.Trim().Length > 0) //{ // sysLogQuery = sysLogQuery.Where(c => c.LastName.StartsWith(lastName)); //} var sysLogs = from c in sysLogQuery //join p in dbConnection.PaymentTypes on c.PaymentTypeID equals p.PaymentTypeID select new { c.Id, c.Operator, c.Message, c.Result, c.Type, c.Module, c.CreateTime }; int numberOfRows = sysLogs.Count(); sysLogs = sysLogs.OrderBy(sortExpression); var sysLogList = sysLogs.Skip((paging.CurrentPageNumber - 1) * paging.PageSize).Take(paging.PageSize); paging.TotalRows = numberOfRows; paging.TotalPages = Utilities.CalculateTotalPages(numberOfRows, paging.PageSize); List <SysLogInquiry> sysLogInquiry = new List <SysLogInquiry>(); foreach (var sysLog in sysLogList) { SysLogInquiry sysLogData = new SysLogInquiry(); sysLogData.Id = sysLog.Id; sysLogData.Operator = sysLog.Operator; sysLogData.Message = sysLog.Message; sysLogData.Result = sysLog.Result; sysLogData.Type = sysLog.Type; sysLogData.Module = sysLog.Module; sysLogData.CreateTime = sysLog.CreateTime; sysLogInquiry.Add(sysLogData); } return(sysLogInquiry); }
/// <summary> /// SysLog Inquiry /// </summary> /// <returns></returns> public List <SysLogInquiry> SysLogInquiry(string queryStr, DataGridPagingInformation paging) { List <SysLog> sysLogs = new List <SysLog>(); string sortExpression = paging.SortExpression; int maxRowNumber; int minRowNumber; minRowNumber = (paging.PageSize * (paging.CurrentPageNumber - 1)) + 1; maxRowNumber = paging.PageSize * paging.CurrentPageNumber; StringBuilder sqlBuilder = new StringBuilder(); StringBuilder sqlWhereBuilder = new StringBuilder(); string sqlWhere = string.Empty; if (queryStr != null && queryStr.Trim().Length > 0) { sqlWhereBuilder.Append(" c.Id LIKE @Id AND "); } if (sqlWhereBuilder.Length > 0) { sqlWhere = " WHERE " + sqlWhereBuilder.ToString().Substring(0, sqlWhereBuilder.Length - 4); } sqlBuilder.Append(" SELECT COUNT(*) as total_records FROM SysLog c "); sqlBuilder.Append(sqlWhere); sqlBuilder.Append(";"); sqlBuilder.Append(" SELECT* FROM("); sqlBuilder.Append(" SELECT (ROW_NUMBER() OVER (ORDER BY " + paging.SortExpression + " " + paging.SortDirection + ")) as record_number,"); sqlBuilder.Append(" c.*"); sqlBuilder.Append(" FROM SysLog c"); sqlBuilder.Append(sqlWhere); sqlBuilder.Append(" ) Rows"); sqlBuilder.Append(" where record_number between " + minRowNumber + " and " + maxRowNumber); string sql = sqlBuilder.ToString(); SqlCommand sqlCommand = new SqlCommand(); sqlCommand.Connection = dbConnection; sqlCommand.CommandText = sql; if (queryStr != null && queryStr.Trim().Length > 0) { sqlCommand.Parameters.Add("@Id", System.Data.SqlDbType.NVarChar); sqlCommand.Parameters["@Id"].Value = queryStr + "%"; } SqlDataReader reader = sqlCommand.ExecuteReader(); reader.Read(); paging.TotalRows = Convert.ToInt32(reader["Total_Records"]); paging.TotalPages = Utilities.CalculateTotalPages(paging.TotalRows, paging.PageSize); reader.NextResult(); List <SysLogInquiry> sysLogList = new List <SysLogInquiry>(); while (reader.Read()) { DataReader dataReader = new DataReader(reader); SysLogInquiry sysLog = new SysLogInquiry(); sysLog.Id = dataReader.GetString("Id"); sysLog.Operator = dataReader.GetString("Operator"); sysLog.Message = dataReader.GetString("Message"); sysLog.Result = dataReader.GetString("Result"); sysLog.Type = dataReader.GetString("Type"); sysLog.Module = dataReader.GetString("Module"); sysLog.CreateTime = dataReader.GetDateTime("CreateTime"); sysLogList.Add(sysLog); } reader.Close(); return(sysLogList); }