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