private StrainPercentiles CalculatePercentiles(string strain, short saleYear, string colName)
{
SqlCommand command = BuildCommand("[pb].[CalcPercentilesForSaleBulls]");
command.Parameters.Add(new SqlParameter
{
Value = strain,
ParameterName = "strain",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.VarChar,
Size = 2
});
command.Parameters.Add(new SqlParameter
{
Value = saleYear,
ParameterName = "saleYear",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.SmallInt
});
command.Parameters.Add(new SqlParameter
{
Value = colName,
ParameterName = "colName",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.VarChar,
Size = 20
});
var sp = new StrainPercentiles
{
ColName = colName,
CalculatedOn = DateTime.Today,
SaleYear = saleYear,
Strain = strain,
Percentiles = new Dictionary<int, decimal>()
};
OpenConnection(command.Connection);
using (command.Connection)
{
SqlDataReader dataReader = command.ExecuteReader();
using (dataReader)
{
if (dataReader.HasRows)
{
int ordPercentile = dataReader.GetOrdinal("Percentile");
int ordValue = dataReader.GetOrdinal("Value");
while (dataReader.Read())
{
var per = ((decimal)
ParameterUtils.SafeGetValue(dataReader.GetValue(ordPercentile), typeof (decimal),
Constants.InitializeDecimal));
int peri = Convert.ToInt32(per*100);
var pval = ((decimal)
ParameterUtils.SafeGetValue(dataReader.GetValue(ordValue), typeof (decimal),
Constants.InitializeDecimal));
sp.Percentiles.Add(peri, pval);
}
}
}
}
return sp;
}
public StrainPercentiles Get(string strain, int saleYear, string colName)
{
DateTime calculatedOn = CalculatedOn(strain, saleYear);
SqlCommand cmd = BuildCommand("pb.GetPercentiles");
cmd.Parameters.Add(new SqlParameter
{
Value = strain,
ParameterName = "strain",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.VarChar,
Size = 2
});
cmd.Parameters.Add(new SqlParameter
{
Value = saleYear,
ParameterName = "saleYear",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.SmallInt
});
cmd.Parameters.Add(new SqlParameter
{
Value = colName,
ParameterName = "colName",
Direction = ParameterDirection.Input,
SqlDbType = SqlDbType.VarChar,
Size = 20
});
var sp = new StrainPercentiles
{
ColName = colName,
SaleYear = saleYear,
Strain = strain,
CalculatedOn = calculatedOn,
Percentiles = new Dictionary<int, decimal>()
};
OpenConnection(cmd.Connection);
using (cmd.Connection)
{
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
using (reader)
{
if (reader.HasRows)
{
int ordPercentile = reader.GetOrdinal("Percentile");
int ordValue = reader.GetOrdinal("Value");
while (reader.Read())
{
var p =
((int)
ParameterUtils.SafeGetValue(reader.GetValue(ordPercentile), typeof (int),
Constants.InitializeInt));
var v =
((decimal)
ParameterUtils.SafeGetValue(reader.GetValue(ordValue), typeof (decimal),
Constants.InitializeDecimal));
sp.Percentiles.Add(p, v);
}
}
}
}
return sp;
}
//============================================================================================
//NOTE: as of today (Apr 2013) they can only select desired traits for percentiles as
// 10, 25, or 40
// This translates to 90, 75 or 60
//============================================================================================
public BullTrait WithInPercentile(TraitVM trait, StrainPercentiles strainPercentile, decimal bullVal)
{
var bt = new BullTrait
{
Trait = trait,
BullValue = bullVal.ToString(CultureInfo.InvariantCulture),
};
try
{
//int desiredPercentile = int.Parse(trait.ExactValue);
// the desired percentile is stored in the "ExactValue" column
bool upper = trait.ExactValue.Substring(0, 1) == "T";
int desiredPercentile = int.Parse(trait.ExactValue.Substring(1));
switch (desiredPercentile)
{
case 10:
if (upper)
{
bt.MatchType = MatchTypeEnum.WithinPercentileTop10;
bt.PercentileRangeValue = strainPercentile.Percentiles[90];
bt.Qualifies = bullVal >= bt.PercentileRangeValue;
}
else
{
bt.MatchType = MatchTypeEnum.WithinPercentileBottom10;
bt.PercentileRangeValue = strainPercentile.Percentiles[10];
bt.Qualifies = bullVal <= bt.PercentileRangeValue;
}
break;
case 25:
if (upper)
{
bt.MatchType = MatchTypeEnum.WithinPercentileTop25;
bt.PercentileRangeValue = strainPercentile.Percentiles[75];
bt.Qualifies = bullVal >= bt.PercentileRangeValue;
}
else
{
bt.MatchType = MatchTypeEnum.WithinPercentileBottom25;
bt.PercentileRangeValue = strainPercentile.Percentiles[25];
bt.Qualifies = bullVal <= bt.PercentileRangeValue;
}
break;
case 40:
if (upper)
{
bt.MatchType = MatchTypeEnum.WithinPercentileTop40;
bt.PercentileRangeValue = strainPercentile.Percentiles[60];
bt.Qualifies = bullVal >= bt.PercentileRangeValue;
}
else
{
bt.MatchType = MatchTypeEnum.WithinPercentileBottom40;
bt.PercentileRangeValue = strainPercentile.Percentiles[40];
bt.Qualifies = bullVal <= bt.PercentileRangeValue;
}
break;
default:
throw new Exception("Desired trait percentile is not one of the calcuated percentiles.");
}
}
catch (KeyNotFoundException)
{
bt.PercentileRangeValue = 0;
bt.Qualifies = true;
}
return bt;
}
