private static string ToIso8601(DateTime datetime, CommenceFieldType fieldType)
{
switch (fieldType)
{
case CommenceFieldType.Date:
return(datetime.Year + "-" + datetime.Month.ToString("D2") + "-" + datetime.Day.ToString("D2"));
case CommenceFieldType.Time:
return(datetime.Hour.ToString("D2") + ":" + datetime.Minute.ToString("D2") + ":00");
default:
return(string.Empty); // risky?
}
}
/// <summary>
/// Converts canonical value to string representation ISO8601-compliant value.
/// </summary>
/// <param name="canonicalValue">canonical Commence value.</param>
/// <param name="fieldType">Commence fieldtype.</param>
/// <returns></returns>
// TODO: we should decouple this from Canonical.
// As it is now, when ISO8601 is selected, so is Canonical
// That leads to unnecessarily complex code.
// Also, it masks the fact that ISO8601 is only for date/time values
// It is still not trivial though, because complex exports expect values in a format that Commence may not export
// Also, converting them back will require some extra plumbing (see UseThids stuff)
internal static string ToIso8601(string canonicalValue, CommenceFieldType fieldType)
{
string retval = canonicalValue;
switch (fieldType)
{
case CommenceFieldType.Date: // expects "yyyymmdd", returns "yyyy-MM-dd"
retval = canonicalValue.Substring(0, 4) + '-' + canonicalValue.Substring(4, 2) + '-' + canonicalValue.Substring(6, 2);
break;
case CommenceFieldType.Time: // expects "hh:mm", returns "hh:mm:ss"
string[] s = canonicalValue.Split(':');
retval = s[0] + ':' + s[1] + ":00";
break;
}
return(retval);
}
/// <summary>
/// Translates the Commence Field Type to the property that returns the Style Index to use with it.
/// </summary>
/// <param name="cft">Commence fieldtype.</param>
/// <returns>Property.</returns>
private uint GetStyleIndexForCommenceType(CommenceFieldType cft)
{
switch (cft)
{
case CommenceFieldType.Number:
case CommenceFieldType.Sequence:
case CommenceFieldType.Calculation:
return(this.CellFormatStyleIndexNumber);
case CommenceFieldType.Date:
return(this.CellFormatStyleIndexDate);
case CommenceFieldType.Time:
return(this.CellFormatStyleIndexTime);
default:
return(this.CellFormatStyleIndexGeneral);
}
}
internal static string GetSQLiteTypeForCommenceField(this CommenceFieldType fieldType)
{
switch (fieldType)
{
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
case CommenceFieldType.Sequence:
return("REAL");
case CommenceFieldType.Date:
case CommenceFieldType.Time:
return("TEXT"); // TEXT expects an ISO8601 value. see: https://www.sqlite.org/datatype3.html
case CommenceFieldType.Checkbox:
return("INTEGER");
default:
return("TEXT");
}
}
internal static OleDbType GetOleDbTypeForCommenceField(this CommenceFieldType fieldType)
{
switch (fieldType)
{
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
case CommenceFieldType.Sequence:
return(OleDbType.Double);
case CommenceFieldType.Date:
case CommenceFieldType.Time:
return(OleDbType.Date);
case CommenceFieldType.Checkbox:
return(OleDbType.Boolean);
default:
return(OleDbType.LongVarWChar);
}
}
internal static DbType GetDbTypeForCommenceField(this CommenceFieldType fieldType)
{
switch (fieldType)
{
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
case CommenceFieldType.Sequence:
return(DbType.Double);
case CommenceFieldType.Date:
case CommenceFieldType.Time:
return(DbType.DateTime);
case CommenceFieldType.Checkbox:
return(DbType.Boolean);
default:
return(DbType.String);
}
}
/// <summary>
/// Converts canonical value to object that ADO.NET understands.
/// </summary>
/// <param name="canonicalValue">Canonical Commence value.</param>
/// <param name="fieldType">Commence fieldtype.</param>
/// <returns>Object of type that Ado understands, DBNull.Value on empty.</returns>
internal static object ToAdoNet(string canonicalValue, CommenceFieldType fieldType)
{
if (string.IsNullOrEmpty(canonicalValue))
{
return(DBNull.Value);
}
object retval;
switch (fieldType)
{
case CommenceFieldType.Date:
canonicalValue = canonicalValue.Replace("-", string.Empty); // if ISO8601, canonical value is overridden
int yearPart = Convert.ToInt32(canonicalValue.Substring(0, 4));
int monthPart = Convert.ToInt32(canonicalValue.Substring(4, 2));
int dayPart = Convert.ToInt32(canonicalValue.Substring(6, 2));
retval = new DateTime(yearPart, monthPart, dayPart);
break;
case CommenceFieldType.Time:
int hourPart = Convert.ToInt32(canonicalValue.Substring(0, 2));
int minutePart = Convert.ToInt32(canonicalValue.Substring(3, 2));
retval = new DateTime(1970, 1, 1, hourPart, minutePart, 0);
break;
case CommenceFieldType.Sequence:
retval = Convert.ToDecimal(canonicalValue);
break;
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
retval = RemoveCurrencySymbol(canonicalValue);
retval = Convert.ToDecimal(retval);
break;
default:
retval = canonicalValue;
break;
}
return(retval);
}
//internal static DocumentFormat.OpenXml.Spreadsheet.CellValues GetTypeForOpenXml(this CommenceFieldType fieldType)
//{
// switch (fieldType)
// {
// case CommenceFieldType.Number:
// case CommenceFieldType.Calculation:
// case CommenceFieldType.Sequence:
// return DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
// case CommenceFieldType.Date:
// case CommenceFieldType.Time:
// return DocumentFormat.OpenXml.Spreadsheet.CellValues.Date;
// case CommenceFieldType.Checkbox:
// return DocumentFormat.OpenXml.Spreadsheet.CellValues.Boolean;
// default:
// return DocumentFormat.OpenXml.Spreadsheet.CellValues.String; // should ideally be SharedString for filesize
// }
//}
internal static string GetOleDbTypeStringForCommenceField(this CommenceFieldType fieldType)
{
switch (fieldType)
{
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
case CommenceFieldType.Sequence:
return("double");
case CommenceFieldType.Date:
case CommenceFieldType.Time:
return("datetime");
case CommenceFieldType.Checkbox:
return("bit");
case CommenceFieldType.Name:
return("char (50)");
default:
return("memo"); // just make it big :)
}
}
/// <summary>
/// Gets the System.Type for the Commence fieldtype
/// </summary>
/// <param name="fieldType">Commence field type</param>
/// <returns>System.Type</returns>
internal static Type GetTypeForCommenceField(this CommenceFieldType fieldType)
{
// String type DataTable columns can be specified to have a certain length,
// but by default they take any length,
// so there is no need to request the length from Commence.
switch (fieldType)
{
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
case CommenceFieldType.Sequence: // when a sequence is gotten canonical it will contain decimals
return(typeof(double));
case CommenceFieldType.Date:
case CommenceFieldType.Time:
return(typeof(DateTime));
case CommenceFieldType.Checkbox:
return(typeof(bool));
default:
return(typeof(string));
}
}
/// <summary>
/// Returns the valid qualifiermembers for a given Commence field-type
/// </summary>
internal static IList <QualifierMember> GetQualifierMembers
(Dictionary <FilterQualifier, QualifierMember> dict, CommenceFieldType fieldType)
{
switch (fieldType)
{
// note the absence of HasDuplicates for Name
// you can't check duplicates in a dde filter!
case CommenceFieldType.Name:
case CommenceFieldType.Text:
case CommenceFieldType.Email:
case CommenceFieldType.URL:
default:
return(dict.Where(w => w.Key.Equals(FilterQualifier.Contains) ||
w.Key.Equals(FilterQualifier.DoesNotContain) ||
w.Key.Equals(FilterQualifier.EqualTo) ||
w.Key.Equals(FilterQualifier.NotEqualTo) ||
w.Key.Equals(FilterQualifier.Between) ||
w.Key.Equals(FilterQualifier.Blank))
.Select(s => s.Value).ToList());
case CommenceFieldType.Date:
return(dict.Where(w => w.Key.Equals(FilterQualifier.On) ||
w.Key.Equals(FilterQualifier.After) ||
w.Key.Equals(FilterQualifier.Before) ||
w.Key.Equals(FilterQualifier.Between) ||
w.Key.Equals(FilterQualifier.Blank))
.Select(s => s.Value).ToList());
case CommenceFieldType.Time:
return(dict.Where(w => w.Key.Equals(FilterQualifier.At) ||
w.Key.Equals(FilterQualifier.After) ||
w.Key.Equals(FilterQualifier.Before) ||
w.Key.Equals(FilterQualifier.Between) ||
w.Key.Equals(FilterQualifier.Blank))
.Select(s => s.Value).ToList());
case CommenceFieldType.Checkbox:
return(dict.Where(w => w.Key.Equals(FilterQualifier.Checked) ||
w.Key.Equals(FilterQualifier.NotChecked))
.Select(s => s.Value).ToList());
case CommenceFieldType.Telephone:
return(dict.Where(w => w.Key.Equals(FilterQualifier.Contains) ||
w.Key.Equals(FilterQualifier.DoesNotContain) ||
w.Key.Equals(FilterQualifier.EqualTo) ||
w.Key.Equals(FilterQualifier.Blank))
.Select(s => s.Value).ToList());
case CommenceFieldType.Number:
case CommenceFieldType.Calculation:
return(dict.Where(w => w.Key.Equals(FilterQualifier.EqualTo) ||
w.Key.Equals(FilterQualifier.NotEqualTo) ||
w.Key.Equals(FilterQualifier.GreaterThan) ||
w.Key.Equals(FilterQualifier.LessThan) ||
w.Key.Equals(FilterQualifier.Between))
.Select(s => s.Value).ToList());
case CommenceFieldType.Selection:
return(dict.Where(w => w.Key.Equals(FilterQualifier.EqualTo) ||
w.Key.Equals(FilterQualifier.NotEqualTo))
.Select(s => s.Value).ToList());
}
}
