/// <summary>
/// Returns a SQL Script that will Set the destination column in the Table
/// to the values of the associated Extended Date field, converted from "mmddyyyy (no spaces)" format.
/// The Values will only be set for rows that contain the extended data.
/// </summary>
private string FromMMDDYYYY()
{
return(string.Format(UpdateDateTime,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
Util.InsertDelimiters(Util.Clean(Util.PullValue(ExtendedFieldKey)), Delimiters.DASH), //convert the "mmddyyyy" to "mm-dd-yyyy", which SQL can process
ExtendedFieldKey,
SqlDateStyles.MMDDYYYY_DASH));
}
/// /// <summary>
/// Returns a SQL Script that will Set the destination column in the Table
/// to the values of the associated Extended Date field, converted from "yyyy-mm-ddThh:mi:ss.mmm (no spaces)" format.
/// The Values will only be set for rows that contain the extended data.
/// </summary>
private string FromStandardDateSyle(int standardDateStyle)
{
return(string.Format(
UpdateDateTime,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
Util.Clean(Util.PullValue(ExtendedFieldKey)),
ExtendedFieldKey,
standardDateStyle));
}
/// <summary>
/// Returns a SQL Script that will test Setting the destination column in the Table
/// to the values of the associated Extended Date field, converted from "yyyy-mm-ddThh:mi:ss.mmm (no spaces)" format.
/// The Values will only be set for rows that contain the extended data.
/// </summary>
public string FromStandardDateSyle(int dateStyle)
{
string contextName = ExtendedFieldDataScriptor.UsingTableScriptor.Context;
//if a context was specified, we can give more specific information back about any conversion failures
switch (contextName)
{
case ContextNames.Reports:
return(string.Format(
ProcessExtractedDateWithContextScript,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
//create value script to get value and strip whitespace/double quotes
Util.Clean(Util.PullValue(ExtendedFieldDataScriptor.Key)),
ExtendedFieldDataScriptor.Key,
dateStyle,
dateStyle, //not a typo, source date style and target sql date style are the same for built in T-SQL date styles
//TODO: Refactor this to use dbNames() then switch on the current context. Harder to program initially, but much easier to use in the future.
"dbo.Reports",
"Report_Id"));
case ContextNames.Summaries:
return(string.Format(
ProcessExtractedDateWithContextScript,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
//create value script to get value and strip whitespace/double quotes
Util.Clean(Util.PullValue(ExtendedFieldDataScriptor.Key)),
ExtendedFieldDataScriptor.Key,
dateStyle,
dateStyle,
"dbo.Summaries",
"Summary_Id"));
case ContextNames.Administration:
case ContextNames.Audit:
case ContextNames.ExternalApi:
case ContextNames.Media:
case ContextNames.Messaging:
case ContextNames.Metadata:
case ContextNames.RecentInfo:
default:
return(string.Format(ProcessExtractedDateWithoutContextScript,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
//create value script to get value and strip whitespace/double quotes
Util.Clean(Util.PullValue(ExtendedFieldDataScriptor.Key)),
ExtendedFieldDataScriptor.Key,
dateStyle,
dateStyle));
}
}
/// <summary>
/// Returns a SQL Script that will test Setting the destination column in the Table
/// to the values of the associated Extended Date field, converted from "mmddyyyy (no spaces)" format.
/// The Values will only be set for rows that contain the extended data.
/// </summary>
public string FromMMDDYYYY()
{
string contextName = ExtendedFieldDataScriptor.UsingTableScriptor.Context;
//if a context was specified, we can give more specific information back about any conversion failures
switch (contextName)
{
case ContextNames.Reports:
return(string.Format(
ProcessExtractedDateWithContextScript,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
//create value script to get value, strip outer whitespace/double quotes, and insert dashes so we can match against a built in SQL date style
Util.InsertDelimiters(Util.Clean(Util.PullValue(ExtendedFieldDataScriptor.Key)), Delimiters.DASH),
ExtendedFieldDataScriptor.Key,
SqlDateStyles.MMDDYYYY_DASH,
DateStyles.MMDDYYYY,
"dbo.Reports",
"Report_Id"));
case ContextNames.Summaries:
return(string.Format(
ProcessExtractedDateWithContextScript,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
//create value script to get value, strip outer whitespace/double quotes, and insert dashes so we can match against a built in SQL date style
Util.InsertDelimiters(Util.Clean(Util.PullValue(ExtendedFieldKey)), Delimiters.DASH),
ExtendedFieldDataScriptor.Key,
SqlDateStyles.MMDDYYYY_DASH,
DateStyles.MMDDYYYY,
"dbo.Summaries",
"Summary_Id"));
case ContextNames.Administration:
case ContextNames.Audit:
case ContextNames.ExternalApi:
case ContextNames.Media:
case ContextNames.Messaging:
case ContextNames.Metadata:
case ContextNames.RecentInfo:
default:
return(string.Format(
ProcessExtractedDateWithoutContextScript,
ExtendedFieldDataScriptor.UsingTableScriptor.TableName,
Column,
Util.InsertDelimiters(Util.Clean(Util.PullValue(ExtendedFieldKey)), Delimiters.DASH), //convert the "mmddyyyy" to "mm-dd-yyyy", which SQL can process
ExtendedFieldKey,
SqlDateStyles.MMDDYYYY_DASH,
DateStyles.MMDDYYYY));
}
}
