public static CondFormat Deserialize(
XmlTextReader tr)
{
string txt;
CondFormat cf = new CondFormat();
txt = tr.GetAttribute("ColumnType");
if (txt != null)
{
EnumUtil.TryParse(txt, out cf.ColumnType);
}
XmlUtil.GetStringAttribute(tr, "Name", ref cf.Name);
XmlUtil.GetBoolAttribute(tr, "Option1", ref cf.Option1);
XmlUtil.GetBoolAttribute(tr, "Option2", ref cf.Option2);
//XmlUtil.GetBoolAttribute(tr, "ShowInHeaders", ref cf.ShowInHeaders);
tr.Read(); // get CondFormatRules element
tr.MoveToContent();
if (!Lex.Eq(tr.Name, "CondFormatRules"))
{
throw new Exception("CondFormat.Deserialize - \"CondFormat\" end element not found");
}
if (!tr.IsEmptyElement)
{
cf.Rules = CondFormatRules.Deserialize(tr);
cf.Rules.InitializeInternalMatchValues(cf.ColumnType);
}
else
{
cf.Rules = new CondFormatRules(); // no rules
}
tr.Read(); // get CondFormat end element
tr.MoveToContent();
if (!Lex.Eq(tr.Name, "CondFormat") || tr.NodeType != XmlNodeType.EndElement)
{
throw new Exception("CondFormat.Deserialize - Expected CondFormat end element");
}
if (cf.ColumnType == MetaColumnType.Date && cf.Rules != null)
{ // store normalized dates
foreach (CondFormatRule rule in cf.Rules)
{
if (!String.IsNullOrEmpty(rule.Value))
{
rule.ValueNormalized = DateTimeMx.Normalize(rule.Value);
}
if (!String.IsNullOrEmpty(rule.Value2))
{
rule.Value2Normalized = DateTimeMx.Normalize(rule.Value2);
}
}
}
return(cf);
}
/// <summary>
/// Convert dates to doubles to allow for databar and color scale Cond formats
/// </summary>
/// <param name="condFormat"></param>
/// <param name="stringValue"></param>
/// <param name="v1Normal"></param>
/// <param name="v2Normal"></param>
/// <param name="v"></param>
/// <param name="v1"></param>
/// <param name="v2"></param>
static void ConvertDateValuesToDoubles(
CondFormat condFormat,
string stringValue,
string v1Normal,
string v2Normal,
out double v,
out double v1,
out double v2)
{
DateTime date, date1, date2; // convert to delta day values relative to date1
if (condFormat.ColumnType == MetaColumnType.Date) // !String.IsNullOrEmpty(r1.ValueNormalized))
{ // build between date values
date1 = DateTimeMx.NormalizedToDateTime(v1Normal);
date2 = DateTimeMx.NormalizedToDateTime(v2Normal);
date = DateTimeMx.NormalizedToDateTime(stringValue);
v = date.Subtract(date1).TotalDays;
v1 = 0;
v2 = date2.Subtract(date1).TotalDays;
}
else
{
v = v1 = v2 = 0; // treat ordinal continuous coloring as numeric conditional formatting
}
return;
}
/// <summary>
/// Clone
/// </summary>
/// <returns></returns>
public new DateTimeMx Clone()
{
DateTimeMx dtMx = (DateTimeMx)this.MemberwiseClone();
if (dtMx.MdtExAttr != null)
{
dtMx.MdtExAttr = MdtExAttr.Clone();
}
return(dtMx);
}
/// <summary>
/// Binary Deserialize
/// </summary>
/// <param name="br"></param>
/// <returns></returns>
public static DateTimeMx DeserializeBinary(BinaryReader br)
{
DateTimeMx dtx = new DateTimeMx();
MobiusDataType.DeserializeBinary(br, dtx);
long ticks = br.ReadInt64();
dtx.Value = new DateTime(ticks);
return(dtx);
}
/// <summary>
/// Custom Compact deserialization
/// </summary>
/// <param name="sa"></param>
/// <param name="sai"></param>
/// <returns></returns>
public static DateTimeMx Deserialize(string[] sa, int sai)
{
DateTimeMx dtx = new DateTimeMx();
if (!Lex.IsNullOrEmpty(sa[sai]))
{
dtx.Value = DateTimeUS.ParseDate(sa[sai]);
}
return(dtx);
}
/// <summary>
/// Try to parse a DateTime
/// </summary>
/// <param name="textValue"></param>
/// <param name="dtEx"></param>
/// <returns></returns>
public static bool TryParse(
string textValue,
out DateTimeMx dtEx)
{
dtEx = new DateTimeMx(textValue);
if (dtEx.Value == DateTime.MinValue)
{
return(false);
}
return(true);
}
/// <summary>
/// Match a DateTime value
/// </summary>
/// <param name="rules"></param>
/// <param name="dtValue"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
DateTime dtValue)
{
CondFormatRules rules = condFormat.Rules;
if (dtValue == DateTime.MinValue)
{
return(MatchNull(condFormat));
}
string dateString = DateTimeMx.Normalize(dtValue);
CondFormatRule matchingRule = Match(condFormat, dateString);
return(matchingRule);
}
/// <summary>
/// Initialize internal match values for a single rule
/// </summary>
/// <param name="columnType"></param>
public void InitializeInternalMatchValues(MetaColumnType columnType)
{
OpCode = ConvertOpNameToCode(Op);
bool calculateEpsilonFromCfValue = false; // if true use cf value (note: may not be same number of decimals as output format)
Epsilon = 0;
if (MetaColumn.IsNumericMetaColumnType(columnType) && !String.IsNullOrEmpty(Value))
{
double.TryParse(Value, out ValueNumber);
if (calculateEpsilonFromCfValue)
{
Epsilon = MobiusDataType.GetEpsilon(Value);
}
else
{
int decimals = 10; // use default epsilon value
Epsilon = MobiusDataType.GetEpsilon(decimals);
}
}
else if (columnType == MetaColumnType.Date && !String.IsNullOrEmpty(Value))
{
ValueNormalized = DateTimeMx.Normalize(Value);
}
if (MetaColumn.IsNumericMetaColumnType(columnType) && !String.IsNullOrEmpty(Value2))
{
double.TryParse(Value2, out Value2Number);
double e2 = MobiusDataType.GetEpsilon(Value2);
if (e2 < Epsilon)
{
Epsilon = e2;
}
}
else if (columnType == MetaColumnType.Date && !String.IsNullOrEmpty(Value2))
{
Value2Normalized = DateTimeMx.Normalize(Value2);
}
}
/// <summary>
/// Convert object to a MobiusDataType
/// </summary>
/// <param name="o"></param>
/// <param name="type"></param>
/// <returns></returns>
public static MobiusDataType ConvertToMobiusDataType(
MetaColumnType type,
Object o)
{
switch (type)
{
case MetaColumnType.Integer:
case MetaColumnType.Number:
case MetaColumnType.DictionaryId:
return(NumberMx.ConvertTo(o));
case MetaColumnType.QualifiedNo:
return(QualifiedNumber.ConvertTo(o));
case MetaColumnType.String:
case MetaColumnType.MolFormula:
case MetaColumnType.Hyperlink:
case MetaColumnType.Html:
case MetaColumnType.Binary:
return(StringMx.ConvertTo(o));
case MetaColumnType.Date:
return(DateTimeMx.ConvertTo(o));
case MetaColumnType.CompoundId:
return(CompoundId.ConvertTo(o));
case MetaColumnType.Structure:
return(MoleculeMx.ConvertTo(o));
case MetaColumnType.Image:
return(ImageMx.ConvertTo(o)); // assume text is dblink
default:
throw new Exception("Unrecognized data type: " + type);
}
}
/// <summary>
/// Try parse of a US format date string string to a DateTime
/// </summary>
/// <param name="dtString"></param>
/// <param name="dateTime"></param>
/// <returns></returns>
public static bool TryParseDate(string dtString, out DateTime dateTime)
{
dateTime = DateTime.MinValue;
try
{
string normalized = DateTimeMx.Normalize(dtString);
if (String.IsNullOrEmpty(normalized))
{
return(false);
}
dateTime = DateTimeMx.NormalizedToDateTime(normalized);
if (dateTime == DateTime.MinValue)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex) { return(false); }
}
/// <summary>
/// Compare two values (IComparable.CompareTo)
/// </summary>
/// <param name="o"></param>
/// <returns></returns>
public override int CompareTo(
object o)
{
if (o == null)
{
return(1);
}
if (!(o is DateTimeMx))
{
throw new Exception("Can't compare a " + GetType().Name + " to a " + o.GetType());
}
DateTimeMx s = this;
DateTimeMx s2 = o as DateTimeMx;
if (s.IsNull && s2.IsNull)
{
return(0);
}
else if (!s.IsNull && s2.IsNull)
{
return(1);
}
else if (s.IsNull && !s2.IsNull)
{
return(-1);
}
else
{
int compareResult = s.Value.Date.CompareTo(s2.Value.Date); // compare dates without times
// if (compareResult == 0) s = s; // debug
return(compareResult);
}
}
/// <summary>
/// Match a string value to conditional formatting & return the first matching item
/// Handles dates converted to normalized string values also (yyyymmdd)
/// </summary>
/// <param name="rules"></param>
/// <param name="value"></param>
/// <returns></returns>
public static CondFormatRule Match(
CondFormat condFormat,
string stringValue)
{
double v, v1, v2;
int ri;
object o;
CondFormatRules rules = condFormat.Rules;
CondFormatRule rule = null;
DateTime dtValue = DateTime.MinValue, dt2;
if (condFormat.ColumnType == MetaColumnType.CompoundId)
{
return(Match(condFormat, new CompoundId(stringValue)));
}
if (String.IsNullOrEmpty(stringValue))
{
return(MatchNull(condFormat));
}
int lowerLimitRule = -1;
int upperLimitRule = -1;
for (ri = 0; ri < rules.Count; ri++)
{
rule = rules[ri];
if (!String.IsNullOrEmpty(stringValue))
{ // check where we have a value
string value = rule.Value;
if (rule.ValueNormalized != null)
{
value = rule.ValueNormalized;
}
string value2 = rule.Value2;
if (rule.Value2Normalized != null)
{
value2 = rule.Value2Normalized;
}
if (rule.OpCode == CondFormatOpCode.Within) // check date range
{
dtValue = DateTimeMx.NormalizedToDateTime(stringValue); // convert to a DateTime
double withinValue = rule.ValueNumber;
if (Lex.Contains(value2, "Day"))
{
dt2 = dtValue.AddDays(withinValue);
}
else if (Lex.Contains(value2, "Week"))
{
dt2 = dtValue.AddDays(withinValue * 7);
}
else if (Lex.Contains(value2, "Month"))
{
dt2 = dtValue.AddMonths((int)withinValue); // note must be integer months
}
else if (Lex.Contains(value2, "Year"))
{
dt2 = dtValue.AddYears((int)withinValue); // note must be integer years
}
else
{
throw new Exception("Unexpected date unit: " + value2);
}
dt2 = dt2.AddDays(1); // add one day to dt2 since it's time is 12:00 AM and the Now date includes the hours passed so far today
if (DateTime.Compare(dt2, DateTime.Now) >= 0)
{
break; // if stored date/time + within value is >= current date/time then condition passes
}
else
{
continue;
}
}
if (rule.OpCode == CondFormatOpCode.Between && value2 != null && value != null)
{
if (Lex.Gt(value, value2))
{
string s1 = value; value = value2; value2 = s1;
}
if (Lex.Ge(stringValue, value) && Lex.Le(stringValue, value2))
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.NotBetween && value2 != null && value != null)
{
if (Lex.Gt(value, value2))
{
string s1 = value; value = value2; value2 = s1;
}
if (Lex.Lt(stringValue, value) || Lex.Gt(stringValue, value2))
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.Eq &&
Lex.Eq(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.NotEq &&
Lex.Ne(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Gt &&
Lex.Gt(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Lt &&
Lex.Lt(stringValue, value))
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Ge &&
Lex.Ge(stringValue, value))
{
if (rules.ColoringStyle == CondFormatStyle.ColorScale || ri > rules.Count - 1)
{
break;
}
else
{
lowerLimitRule = ri;
}
}
else if (rule.OpCode == CondFormatOpCode.Le &&
Lex.Le(stringValue, value))
{
upperLimitRule = ri;
break;
}
else if (rule.OpCode == CondFormatOpCode.Substring &&
stringValue.ToLower().IndexOf(value.ToLower()) >= 0)
{
break;
}
else if (rule.OpCode == CondFormatOpCode.NotNull || rule.OpCode == CondFormatOpCode.Exists) // value is not null or exists
{
break;
}
else if (rule.OpCode == CondFormatOpCode.Unknown && // treat unknown as equal
Lex.Eq(stringValue, value))
{
break;
}
}
else if (rule.OpCode == CondFormatOpCode.Null || rule.OpCode == CondFormatOpCode.NotExists)
{
break; // null value & null conditional format operator
}
}
//DebugLog.Message(stringValue + " " + ri); // debug
if (ri >= rules.Count)
{
return(null); // return null if no rule matches
}
// Matches a rule associated with a particular color
if (rules.ColoringStyle == CondFormatStyle.ColorSet)
{
return(rule);
}
// Calculate a linear gradient between the two color limits
else if (rules.ColoringStyle == CondFormatStyle.ColorScale && rule.OpCode == CondFormatOpCode.Between)
{ // newer single color scale rule
ConvertDateValuesToDoubles(condFormat, stringValue, rule.ValueNormalized, rule.Value2Normalized, out v, out v1, out v2);
rule.ValueNumber = v1;
rule.Value2Number = v2;
rule = BuildColorRuleForColorScale(v, rule);
return(rule);
}
else if (rules.ColoringStyle == CondFormatStyle.ColorScale && lowerLimitRule >= 0 && upperLimitRule >= lowerLimitRule)
{ // older two-rule, double color scale rules
CondFormatRule r1 = rules[lowerLimitRule];
CondFormatRule r2 = rules[upperLimitRule];
ConvertDateValuesToDoubles(condFormat, stringValue, r1.ValueNormalized, r2.ValueNormalized, out v, out v1, out v2);
rule.ValueNumber = v1;
rule.Value2Number = v2;
rule = BuildColorRuleForGradientValue(v, v1, v2, r1.BackColor1, r2.BackColor1);
return(rule);
}
// Calculate a percentage for a data bar for a value between the two color limits
else if (rules.ColoringStyle == CondFormatStyle.DataBar)
{
ConvertDateValuesToDoubles(condFormat, stringValue, rule.ValueNormalized, rule.Value2Normalized, out v, out v1, out v2);
rule.ValueNumber = v1;
rule.Value2Number = v2;
rule = BuildPercentageRuleForDataBarValue(v, rule);
return(rule);
}
else if (rules.ColoringStyle == CondFormatStyle.IconSet)
{
rule = BuildImageRuleForIconSetValue(rule);
return(rule); // image name should be included in rule
}
else
{
return(rule); // shouldn't happen
}
}
/////////////////////////////////////////////////////////////////
//////////////////////////// Methods ////////////////////////////
/////////////////////////////////////////////////////////////////
/// <summary>
/// Create a Mobius data type based on the supplied MetaColumn type
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static MobiusDataType New(
MetaColumnType type)
{
MobiusDataType o;
if (type == MetaColumnType.Integer)
{
o = new NumberMx();
}
else if (type == MetaColumnType.Number)
{
o = new NumberMx();
}
else if (type == MetaColumnType.QualifiedNo)
{
o = new QualifiedNumber();
}
else if (type == MetaColumnType.String)
{
o = new StringMx();
}
else if (type == MetaColumnType.Date)
{
o = new DateTimeMx();
}
else if (type == MetaColumnType.Binary)
{
o = new StringMx();
}
else if (type == MetaColumnType.CompoundId)
{
o = new CompoundId();
}
else if (type == MetaColumnType.Structure)
{
o = new MoleculeMx();
}
else if (type == MetaColumnType.MolFormula)
{
o = new StringMx();
}
else if (type == MetaColumnType.Image)
{
o = new ImageMx();
}
else if (type == MetaColumnType.DictionaryId)
{
o = new NumberMx();
}
else if (type == MetaColumnType.Hyperlink)
{
o = new StringMx();
}
else if (type == MetaColumnType.Html)
{
o = new StringMx();
}
else
{
throw new Exception("Unrecognized data type: " + type);
}
return(o);
}
/// <summary>
/// Custom compact Deserialization of MobiusDataType
/// </summary>
/// <returns></returns>
public static MobiusDataType Deserialize(string[] sa)
{
int sai;
MobiusDataType mdt = null;
char type = sa[0][0];
sai = 5; // first array entry for specific subclass
switch (type)
{
case 'M': // ChemicalStructure (Molecule)
mdt = MoleculeMx.Deserialize(sa, sai);
break;
case 'C': // CompoundId
mdt = CompoundId.Deserialize(sa, sai);
break;
case 'D': // DateTimeEx
mdt = DateTimeMx.Deserialize(sa, sai);
break;
case 'I': // ImageEx
mdt = ImageMx.Deserialize(sa, sai);
break;
case 'N': //NumberEx
mdt = NumberMx.Deserialize(sa, sai);
break;
case 'Q': // QualifiedNumber
mdt = QualifiedNumber.Deserialize(sa, sai);
break;
case 'S': // StringEx
mdt = StringMx.Deserialize(sa, sai);
break;
default:
throw new Exception("Unexpected MobiusDataType: " + type);
}
if (!Lex.IsNullOrEmpty(sa[1]))
{
mdt.BackColor = Color.FromArgb(int.Parse(sa[1]));
}
if (!Lex.IsNullOrEmpty(sa[2]))
{
mdt.ForeColor = Color.FromArgb(int.Parse(sa[2]));
}
if (!Lex.IsNullOrEmpty(sa[3]))
{
mdt.DbLink = sa[3];
}
if (!Lex.IsNullOrEmpty(sa[4]))
{
mdt.Hyperlink = sa[4];
}
return(mdt);
}
/// <summary>
/// Deserialize a single value object
/// </summary>
/// <param name="ba"></param>
/// <returns></returns>
public static object ReadBinaryItem(
BinaryReader br)
{
object vo = null;
VoDataType voType = (VoDataType)br.ReadByte();
switch (voType)
{
case VoDataType.Null:
return(null);
case VoDataType.DbNull:
return(DBNull.Value);
case VoDataType.Byte:
return(br.ReadByte());
case VoDataType.Int16:
return(br.ReadInt16());
case VoDataType.Int32:
return(br.ReadInt32());
case VoDataType.Int64:
return(br.ReadInt64());
case VoDataType.Float:
return(br.ReadSingle());
case VoDataType.Double:
return(br.ReadDouble());
case VoDataType.Char:
return(br.ReadChar());
case VoDataType.String:
return(br.ReadString());
case VoDataType.DateTime:
return(new DateTime(br.ReadInt64()));
case VoDataType.CompoundId:
return(CompoundId.DeserializeBinary(br));
case VoDataType.NumberMx:
return(NumberMx.DeserializeBinary(br));
case VoDataType.QualifiedNo:
return(QualifiedNumber.DeserializeBinary(br));
case VoDataType.StringMx:
return(StringMx.DeserializeBinary(br));
case VoDataType.DateTimeMx:
return(DateTimeMx.DeserializeBinary(br));
case VoDataType.ImageMx:
return(ImageMx.DeserializeBinary(br));
case VoDataType.ChemicalStructure:
return(MoleculeMx.DeserializeBinary(br));
default:
throw new Exception("Unrecognized type: " + voType);
}
}