public DataTransferObject.Organization.WithholdingView GetWithholding(WithholdingAccountType accountType, Guid company) { DataTransferObject.Organization.WithholdingView withholding = null; //[AccountNo] = @AccountNo AND string sql = "SELECT [AccountNo],[AccountType],[Time],[Status],[Amount],[Company] FROM [T_Withholding] WHERE [AccountType]=@AccountType AND [Company] = @Company "; using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { //dbOperator.AddParameter("AccountNo", withholdingView.AccountNo); dbOperator.AddParameter("AccountType", accountType); dbOperator.AddParameter("Company", company); using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader(sql)) { if (reader.Read()) { withholding = new DataTransferObject.Organization.WithholdingView(); withholding.AccountNo = reader.GetString(0); withholding.AccountType = (WithholdingAccountType)reader.GetByte(1); withholding.Time = reader.GetDateTime(2); withholding.Status = (WithholdingProtocolStatus)reader.GetByte(3); withholding.Amount = reader.GetDecimal(4); withholding.Company = reader.GetGuid(5); } } } return(withholding); }
private static Domain.IncomeGroupLimitGroup loadIncomeGroup(System.Data.Common.DbDataReader reader) { Domain.IncomeGroupLimitGroup income = new Domain.IncomeGroupLimitGroup(); income.Limitation = new List <Domain.IncomeGroupLimit>(); Domain.IncomeGroupLimit limit = null; while (reader.Read()) { var currentId = reader.GetGuid(0); if (income.Id != currentId) { income.Id = currentId; income.CompanyId = reader.GetGuid(1); income.IncomeGroupId = reader.IsDBNull(2) ? (Guid?)null : reader.GetGuid(2); income.Remark = reader.GetString(3); income.Limitation = new List <Domain.IncomeGroupLimit>(); } var currlimitId = reader.GetGuid(4); if (!income.Limitation.Any(it => it.Id == currlimitId)) { limit = new Domain.IncomeGroupLimit(); limit.Id = currlimitId; limit.IncomeId = reader.GetGuid(5); limit.Type = (Common.Enums.PeriodType)reader.GetByte(6); limit.Price = reader.GetInt32(7); limit.Airlines = reader.GetString(12); limit.IsOwnerPolicy = reader.GetBoolean(13); limit.Period = new List <Domain.IncomeGroupPeriod>(); income.Limitation.Add(limit); } Domain.IncomeGroupPeriod period = new Domain.IncomeGroupPeriod(); period.DeductId = reader.GetGuid(8); period.StartPeriod = reader.GetDecimal(9); period.EndPeriod = reader.GetDecimal(10); period.Period = reader.GetDecimal(11); limit.Period.Add(period); } return(income); }
public IEnumerable <DataTransferObject.Foundation.BasicPriceView> QueryBasicPrice(string airline, string departure, string arrival, Core.Pagination pagination) { IList <DataTransferObject.Foundation.BasicPriceView> result = new List <DataTransferObject.Foundation.BasicPriceView>(); string fields = "[Id],[Airline],[Departure],[Arrival],[FlightDate],[ETDZDate],[Price],[Mileage],ModifyTime"; string catelog = "[dbo].[T_BasicPrice]"; string orderbyFiled = "[Airline] DESC,[FlightDate] DESC,[Departure],[Arrival],ModifyTime desc"; string where = ""; if (!string.IsNullOrWhiteSpace(airline)) { where += "([Airline] IS NULL OR [Airline] = '" + airline + "') AND "; } if (!string.IsNullOrWhiteSpace(departure) || !string.IsNullOrWhiteSpace(arrival)) { if (!string.IsNullOrWhiteSpace(departure) && string.IsNullOrWhiteSpace(arrival)) { where += "([Departure] = '" + departure + "' OR [Arrival] = '" + departure + "') AND "; } if (!string.IsNullOrWhiteSpace(arrival) && string.IsNullOrWhiteSpace(departure)) { where += "([Departure] = '" + arrival + "' OR [Arrival] = '" + arrival + "') AND "; } if (!string.IsNullOrWhiteSpace(arrival) && !string.IsNullOrWhiteSpace(departure)) { where += "(([Departure] = '" + arrival + "' AND [Arrival] = '" + departure + "') OR ([Departure] = '" + departure + "' AND [Arrival] = '" + arrival + "')) AND "; } } if (where.Length > 0) { where = where.Remove(where.Length - 5, 5); } using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("@iField", fields); dbOperator.AddParameter("@iCatelog", catelog); dbOperator.AddParameter("@iCondition", where); dbOperator.AddParameter("@iOrderBy", orderbyFiled); dbOperator.AddParameter("@iPagesize", pagination.PageSize); dbOperator.AddParameter("@iPageIndex", pagination.PageIndex); dbOperator.AddParameter("@iGetCount", pagination.GetRowCount); System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount"); totalCount.DbType = System.Data.DbType.Int32; totalCount.Direction = System.Data.ParameterDirection.Output; using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_Pagination", System.Data.CommandType.StoredProcedure)) { while (reader.Read()) { DataTransferObject.Foundation.BasicPriceView basicPriceView = new DataTransferObject.Foundation.BasicPriceView(); basicPriceView.Id = reader.GetGuid(0); basicPriceView.Airline = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); basicPriceView.Departure = reader.GetString(2); basicPriceView.Arrival = reader.GetString(3); basicPriceView.FlightDate = reader.GetDateTime(4); basicPriceView.ETDZDate = reader.GetDateTime(5); basicPriceView.Price = reader.GetDecimal(6); basicPriceView.Mileage = reader.GetDecimal(7); basicPriceView.ModifyTime = reader.IsDBNull(8) ? new DateTime(2013, 10, 26, 0, 0, 0) : reader.GetDateTime(8); result.Add(basicPriceView); } } if (pagination.GetRowCount) { pagination.RowCount = (int)totalCount.Value; } } return(result); }