public TakeRateData TakeRateDataItemList(TakeRateFilter filter)
{
var retVal = new TakeRateData();
using (var conn = DbHelper.GetDBConnection())
{
try
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = fdpTakeRateDataGetCrossTabStoredProcedureName;
cmd.CommandTimeout = 0;
if (filter.TakeRateId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@FdpVolumeHeaderId", SqlDbType.Int)
{
Value = filter.TakeRateId
});
}
if (filter.DocumentId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@DocumentId", SqlDbType.Int)
{
Value = filter.DocumentId.Value
});
}
cmd.Parameters.Add(new SqlParameter("@ModelIds", SqlDbType.NVarChar, -1) { Value = filter.Models.ToCommaSeperatedString() });
if (filter.MarketGroupId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.NVarChar, 2) { Value = "MG" });
cmd.Parameters.Add(new SqlParameter("@ObjectId", SqlDbType.Int) { Value = filter.MarketGroupId.Value });
}
else if (filter.MarketId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.NVarChar, 2) { Value = "M" });
cmd.Parameters.Add(new SqlParameter("@ObjectId", SqlDbType.Int) { Value = filter.MarketId.Value });
}
else
{
cmd.Parameters.Add(new SqlParameter("@Mode", SqlDbType.NVarChar, 2) { Value = "MG" });
}
if (filter.Mode == TakeRateResultMode.PercentageTakeRate)
{
cmd.Parameters.Add(new SqlParameter("@ShowPercentage", SqlDbType.Bit) { Value = true });
}
if (!string.IsNullOrEmpty(filter.FeatureCode))
{
cmd.Parameters.Add(new SqlParameter("@FeatureCode", SqlDbType.NVarChar, 5) { Value = filter.FeatureCode });
}
if (filter.ShowCombinedPackOptions)
{
cmd.Parameters.Add(new SqlParameter("@ShowCombinedOptions", SqlDbType.Bit) {Value = true});
}
if (filter.ExcludeOptionalPackItems)
{
// If an option is available both in a pack and can be taken seperately, exclude the rows for the take rate for the option
// outside the pack. If combined with "ShowCombinedPackOptions" above, the take rate will have been combined into the overall
// take for the feature
cmd.Parameters.Add(new SqlParameter("@ExcludeOptionalPackItems", SqlDbType.Bit) { Value = true });
}
var adapter = new SqlDataAdapter((SqlCommand)cmd);
var ds = new DataSet();
adapter.Fill(ds);
// 1. Take rate / volume data
retVal.RawData = ds.Tables[0];
// 2. Whether or not features are standard / optional or not applicable for derivative / market
retVal.FeatureApplicabilityData = ds.Tables[1].AsEnumerable();
// 3. Total volumes for each model by market
retVal.TakeRateSummaryByModel = ds.Tables[2].AsEnumerable().Select(d => new ModelTakeRateSummary
{
StringIdentifier = d.Field<string>("StringIdentifier"),
IsFdpModel = d.Field<bool>("IsFdpModel"),
Volume = d.Field<int>("Volume"),
PercentageOfFilteredVolume = d.Field<decimal>("PercentageOfFilteredVolume")
});
// 4. Summary information
var summary = ds.Tables[3].AsEnumerable().FirstOrDefault();
if (summary != null)
{
retVal.TotalVolume = summary.Field<int>("TotalVolume");
retVal.FilteredVolume = summary.Field<int>("FilteredVolume");
retVal.PercentageOfTotalVolume = summary.Field<decimal>("PercentageOfTotalVolume");
retVal.CreatedBy = summary.Field<string>("CreatedBy");
retVal.CreatedOn = summary.Field<DateTime>("CreatedOn");
retVal.ModelMix = summary.Field<decimal>("ModelMix");
retVal.ModelVolume = summary.Field<int>("ModelVolume");
}
// 5. Notes
retVal.NoteAvailability = ds.Tables[4].AsEnumerable().Select(n => new TakeRateDataItemNote
{
MarketId = n.Field<int?>("MarketId"),
MarketGroupId = n.Field<int?>("MarketGroupId"),
ModelId = n.Field<int?>("ModelId"),
//FdpModelId = n.Field<int?>("FdpModelId"),
FeatureId = n.Field<int?>("FeatureId"),
FeaturePackId = n.Field<int?>("FeaturePackId")
//FdpFeatureId = n.Field<int?>("FdpFeatureId")
});
// 6. EFG
retVal.ExclusiveFeatureGroups = ds.Tables[5].AsEnumerable().Select(efg => new ExclusiveFeatureGroup
{
//EfgId = efg.Field<int>("EfgId"),
Name = efg.Field<string>("ExclusiveFeatureGroup"),
FeatureCode = efg.Field<string>("FeatureCode"),
//FeatureId = efg.Field<int>("FeatureId"),
Feature = efg.Field<string>("Feature"),
//FeatureGroup = efg.Field<string>("FeatureGroup"),
//FeatureSubGroup = efg.Field<string>("FeatureSubGroup")
});
// 7. Packs
retVal.PackFeatures = ds.Tables[6].AsEnumerable().Select(p => new PackFeature
{
Id = p.Field<int>("FeatureId"),
PackId = p.Field<int>("PackId"),
PackName = p.Field<string>("PackName"),
BrandDescription = p.Field<string>("Feature")
});
// 8. Multi-mapped features
retVal.MultiMappedFeatureGroups = ds.Tables[7].AsEnumerable().Select(m => new MultiMappedFeatureGroup
{
FeatureCode = m.Field<string>("MappedFeatureCode"),
ImportFeatureCode = m.Field<string>("ImportFeatureCode"),
Description = m.Field<string>("Description")
});
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
return retVal;
}
public TakeRateData TakeRateDataItemFeatureList(TakeRateFilter filter)
{
var retVal = new TakeRateData();
using (var conn = DbHelper.GetDBConnection())
{
try
{
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Fdp_TakeRateData_GetCrossTabFeatures";
cmd.CommandTimeout = 0;
if (filter.TakeRateId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@FdpVolumeHeaderId", SqlDbType.Int)
{
Value = filter.TakeRateId
});
}
if (filter.DocumentId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@DocumentId", SqlDbType.Int)
{
Value = filter.DocumentId.Value
});
}
cmd.Parameters.Add(new SqlParameter("@ModelIds", SqlDbType.NVarChar, -1) { Value = filter.Models.ToCommaSeperatedString() });
if (filter.MarketId.HasValue)
{
cmd.Parameters.Add(new SqlParameter("@MarketId", SqlDbType.Int) { Value = filter.MarketId.Value });
}
var adapter = new SqlDataAdapter((SqlCommand)cmd);
var ds = new DataSet();
adapter.Fill(ds);
// 1. Features
retVal.RawData = ds.Tables[0];
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
return retVal;
}