public DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView Query(Guid id)
{
DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView view = null;
string sql = "SELECT [Id],[Airlines],[Departure],[Arrival],[PolicyType],[CityLimit],[EffectiveLowerDate],[EffectiveUpperDate]," +
"[IsVIP],[DeductionType],[HarmonyValue],[Account],[AddTime],[Remark] FROM [dbo].[T_PolicyHarmony] WHERE [Id]=@Id";
using (var dbOperator = new DbOperator(Provider, ConnectionString)) {
dbOperator.AddParameter("Id", id);
using (var reader = dbOperator.ExecuteReader(sql)) {
while (reader.Read())
{
view = new DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView(id);
view.Account = reader.GetString(11);
view.AddTime = reader.GetDateTime(12);
view.DeductionType = (DeductionType)reader.GetInt32(9);
view.HarmonyValue = reader.GetDecimal(10);
view.IsVIP = reader.GetBoolean(8);
view.PolicyType = (PolicyType)reader.GetInt32(4);
view.EffectiveDate = new Core.Range <DateTime>(reader.GetDateTime(6), reader.GetDateTime(7));
view.Airlines = reader.GetString(1).Split(new char[] { ',' });
view.Arrival = reader.GetString(3).Split(new char[] { ',' });
view.Departure = reader.GetString(2).Split(new char[] { ',' });
view.CityLimit = reader.GetString(5).Split(new char[] { ',' });
view.Remark = reader.IsDBNull(13) ? string.Empty : reader.GetString(13);
}
}
}
return(view);
}
public IEnumerable <DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView> Query(DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyQueryCondition condition)
{
var result = new List <DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView>();
string sql = "SELECT [Id],[Airlines],[Departure],[Arrival],[PolicyType],[CityLimit],[EffectiveLowerDate],[EffectiveUpperDate]," +
"[IsVIP],[DeductionType],[HarmonyValue],[Account],[AddTime] FROM [dbo].[T_PolicyHarmony] WHERE [PolicyType]=@PolicyType";
using (var dbOperator = new DbOperator(Provider, ConnectionString)) {
StringBuilder strWhere = new StringBuilder();
dbOperator.AddParameter("PolicyType", (int)condition.PolicyType);
if (!string.IsNullOrWhiteSpace(condition.Airline))
{
strWhere.Append(" AND [Airlines] LIKE '%'+@AIRLINE+'%'");
dbOperator.AddParameter("AIRLINE", condition.Airline);
}
if (!string.IsNullOrWhiteSpace(condition.Departure))
{
strWhere.Append(" AND [Departure] LIKE '%'+@DEPARTURE+'%'");
dbOperator.AddParameter("DEPARTURE", condition.Departure);
}
if (!string.IsNullOrWhiteSpace(condition.Arrival))
{
strWhere.Append(" AND [Arrival] LIKE '%'+@ARRIVAL+'%'");
dbOperator.AddParameter("ARRIVAL", condition.Arrival);
}
if (condition.EffectiveDate.Lower.HasValue)
{
strWhere.Append(" AND [EffectiveLowerDate]=@EffectiveLowerDate");
dbOperator.AddParameter("EffectiveLowerDate", condition.EffectiveDate.Lower.Value.Date);
}
if (condition.EffectiveDate.Upper.HasValue)
{
strWhere.Append(" AND [EffectiveUpperDate]=@EffectiveUpperDate");
dbOperator.AddParameter("EffectiveUpperDate", condition.EffectiveDate.Upper.Value.Date);
}
if (strWhere.Length > 0)
{
sql += strWhere;
}
using (var reader = dbOperator.ExecuteReader(sql)) {
while (reader.Read())
{
DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView view = new DataTransferObject.SystemSetting.PolicyHarmony.PolicyHarmonyView(reader.GetGuid(0));
view.Account = reader.GetString(11);
view.AddTime = reader.GetDateTime(12);
view.DeductionType = (DeductionType)reader.GetInt32(9);
view.HarmonyValue = reader.GetDecimal(10);
view.IsVIP = reader.GetBoolean(8);
view.PolicyType = (Common.Enums.PolicyType)reader.GetInt32(4);
view.EffectiveDate = new Core.Range <DateTime>(reader.GetDateTime(6), reader.GetDateTime(7));
view.Airlines = reader.GetString(1).Split(new char[] { ',' });
view.Arrival = reader.GetString(3).Split(new char[] { ',' });
view.Departure = reader.GetString(2).Split(new char[] { ',' });
view.CityLimit = reader.GetString(5).Split(new char[] { ',' });
result.Add(view);
}
}
}
return(result);
}
