# $SnapHashLicense:
#
# SnapLogic - Open source data services
#
# Copyright (C) 2008 - 2009, SnapLogic, Inc. All rights reserved.
#
# See http://www.snaplogic.org for more information about
# the SnapLogic project.
#
# This program is free software, distributed under the terms of
# the GNU General Public License Version 2. See the LEGAL file
# at the top of the source tree.
#
# "SnapLogic" is a trademark of SnapLogic, Inc.
#
#
# $
# $Id: DateDimension.py 10330 2009-12-24 22:13:38Z grisha $
"""
DateDimension Module and Resource Definition.
This component produces date dimension sequences, used in time based analysis.
That is essentially just a large number of records, each record representing
a single date. Besides the date itself, each records contains a large number
of flags, or other, indicating particular characteristics about the
date. For example, is this date a weekday or weekend day? Is this day located
in the last 30 days from today? Having these attributes explicitly spelled out
simplifies the querying for particular types of dates.
"""
# Imports
import re, time, datetime
from decimal import Decimal
from snaplogic.common.snap_exceptions import *
from snaplogic.common.data_types import SnapString,SnapNumber,SnapDateTime
from snaplogic.common import version_info
import snaplogic.cc.prop as prop
from snaplogic.cc import component_api
from snaplogic.cc.component_api import ComponentAPI
from snaplogic.snapi_base import keys
# Public names
__all__ = [ "DateDimension" ]
class DateDimension(ComponentAPI):
"""
Class DateDimension.
"""
api_version = '1.0'
component_version = '1.1'
# Some definitions that are needed for the component container
# and general use of this as a SnapLogic component.
capabilities = {
ComponentAPI.CAPABILITY_INPUT_VIEW_LOWER_LIMIT : 0,
ComponentAPI.CAPABILITY_INPUT_VIEW_UPPER_LIMIT : 0,
ComponentAPI.CAPABILITY_OUTPUT_VIEW_LOWER_LIMIT : 1,
ComponentAPI.CAPABILITY_OUTPUT_VIEW_UPPER_LIMIT : 1,
ComponentAPI.CAPABILITY_ALLOW_PASS_THROUGH : False
}
component_description = "Outputs a date dimension sequence"
component_label = "Date Dimension"
component_doc_uri = "https://www.snaplogic.org/trac/wiki/Documentation/%s/ComponentRef/DateDimension" % \
version_info.doc_uri_version
# Some definitions that we use as part of our date calculations.
_DATE_FORMAT = '%Y-%m-%d'
_QUARTERS = [
u'Q1', u'Q1', u'Q1',
u'Q2', u'Q2', u'Q2',
u'Q3', u'Q3', u'Q3',
u'Q4', u'Q4', u'Q4',
]
_MONTHS = [
u'January',
u'February',
u'March',
u'April',
u'May',
u'June',
u'July',
u'August',
u'September',
u'October',
u'November',
u'December',
]
def create_resource_template(self):
"""
Create DateDimension resource definition template. It consists of:
StartDate: The first date of the sequence.
EndDate: The last date of the sequence.
Today: The date that is considered as 'today' for the purpose of the sequence generation.
FiscalOffset: The offset for the start of the fiscal year, measured in months.
"""
self.set_property_def('StartDate',
prop.SimpleProp("StartDate",
SnapString,
"The first date of the sequence.",
None,
True))
self.set_property_def('EndDate',
prop.SimpleProp("EndDate",
SnapString,
"The last date of the sequence.",
None,
True))
# Today is not marked as a required property because '' (a null string) is a valid value which will be
# interpreted as the current date. The required property flag requires that the property is not null.
# So we will validate this property ourselves in the validate method.
self.set_property_def('Today',
prop.SimpleProp("Today",
SnapString,
"The date seen as 'today' for the purpose of the sequence generation."))
self.set_property_def('FiscalOffset',
prop.SimpleProp("FiscalOffset",
SnapNumber,
"The offset for the start of the fiscal year, measured in months.",
{"min_value": -11, "max_value" : 11},
True))
# Give them default values
self.set_property_value('StartDate', '2000-01-01')
self.set_property_value('EndDate', '2037-12-31')
self.set_property_value('Today', '')
self.set_property_value('FiscalOffset', 0)
# Set the non-modifiable default output view.
self.add_record_output_view_def("dim_date",
[
( 'date_value', SnapDateTime, "Date type, the date value." ),
( 'year', SnapString, "The 4 digit year." ),
( 'quarter', SnapString, "The calendar quarter (Q1, Q2, Q3, Q4)." ),
( 'month', SnapString, "The full month name (January, February, etc.)." ),
( 'full_quarter', SnapString, "The year and quarter (Q107, Q207, etc)." ),
( 'day_number', SnapNumber, "The day number in the calendar year, starting at 1." ),
( 'month_number', SnapNumber, "The month number in the calendar year, starting at 1." ),
( 'day_of_month', SnapNumber, "The day of the current month, starting at 1." ),
( 'day_of_quarter', SnapNumber, "The day of the current quarter, starting at 1." ),
( 'is_today', SnapString, "'Y' if the date is today." ),
( 'is_yesterday', SnapString, "'Y' if the date is yesterday." ),
( 'is_this_month', SnapString, "'Y' if the date is in the current calendar month." ),
( 'is_last_month', SnapString, "'Y' if the date is in the previous calendar month." ),
( 'is_this_quarter', SnapString, "'Y' if the date is in the current calendar quarter." ),
( 'is_last_quarter', SnapString, "'Y' if the date is in the previous calendar quarter." ),
( 'is_this_year', SnapString, "'Y' if the date is in the current calendar year." ),
( 'is_last_year', SnapString, "'Y' if the date is in the previous calendar year." ),
( 'is_rolling_month', SnapString, "'Y' if the date is in the previous 30 days." ),
( 'is_rolling_last_month', SnapString, "'Y' if the date is in the previous 31-60 days." ),
( 'is_rolling_quarter', SnapString, "'Y' if the date is in the previous 90 days." ),
( 'is_rolling_last_quarter', SnapString, "'Y' if the date is in the previous 91-180 days." ),
( 'is_rolling_year', SnapString, "'Y' if the date is in the previous 365 days." ),
( 'is_rolling_last_year', SnapString, "'Y' if the date is in the previous 366-730 days." ),
( 'is_mtd', SnapString, "'Y' if the date is in the current calendar month and <= today." ),
( 'is_last_mtd', SnapString, "'Y' if the date is in the previous calendar month and today <= day of month." ),
( 'is_qtd', SnapString, "'Y' if the date is in the current calendar quarter and <= today." ),
( 'is_last_qtd', SnapString, "'Y' if the date is in the previous calendar quarter today <= day_of_quarter." ),
( 'is_ytd', SnapString, "'Y' if the date is in the current calendar year and <= today." ),
( 'is_last_ytd', SnapString, "'Y' if the date is in the previous calendar year and today <= day_number." ),
( 'fiscal_year', SnapString, "The fiscal year. (2006, 2007, etc)" ),
( 'fiscal_short', SnapString, "The abbreviated fiscal year. (FY06, FY07, etc)." ),
( 'fiscal_long', SnapString, "The long fiscal year. (FY06-07, FY07-08, etc)." ),
( 'fiscal_quarter', SnapString, "The fiscal quarter (Q1, FQ2, etc)." ),
( 'fiscal_day_number', SnapNumber, "The day number in the current fiscal year, starting at 1." ),
( 'fiscal_month_number', SnapNumber, "The month number in the current fiscal year, starting at 1." ),
( 'fiscal_day_of_quarter', SnapNumber, "The day of the current fiscal quarter, starting at 1." ),
( 'is_this_fiscal_quarter', SnapString, "'Y' if date_value is in the current fiscal quarter." ),
( 'is_last_fiscal_quarter', SnapString, "'Y' if date_value is in the previous fiscal quarter." ),
( 'is_this_fiscal_year', SnapString, "'Y' if date_value is in the current fiscal year." ),
( 'is_last_fiscal_year', SnapString, "'Y' if date_value is in the previous fiscal year." )
], "Date Dimension output", False)
def validate(self, err_obj):
"""
Validate a proposed resource definition for this component.
We use the functions of the time module to do a simple operation
on the time string. If they throw an exception, we can take that
as an indication that the time strings were malformed.
"""
# StartDate and EndDate just have to be reasonably formatted dates.
try:
prop_name = "StartDate"
start_val = val = self.get_property_value(prop_name)
if not component_api.has_param(val):
time.strptime(start_val, DateDimension._DATE_FORMAT)
prop_name = "EndDate"
end_val = val = self.get_property_value(prop_name)
if not component_api.has_param(val):
time.strptime(end_val, DateDimension._DATE_FORMAT)
# If start date and end date could be converted correctly, check they don't conflict
if not component_api.has_param(start_val) and end_val < start_val:
err_obj.get_property_err("EndDate").set_message(
"End Date '%s' cannot be earlier than Start Date '%s'." % (end_val, start_val))
except:
err_obj.get_property_err(prop_name).set_message("Invalid Date: '%s', valid format is 'yyyy-mm-dd'." % (val))
# Today is a bit more complex, since it can be '', an absolute time string or
# a relative definition.
try:
val = self.get_property_value("Today")
if val is None or val.strip() == '' or component_api.has_param(val):
pass
elif val.startswith('+') or val.startswith('-'):
# See if we can do an exception-free today calculation if it is a relative definition
tdate = datetime.date.today()
delta = datetime.timedelta(int(str(val)))
tdate += delta
else:
# See if the date given formats correctly
time.strptime(val, DateDimension._DATE_FORMAT)
except:
err_obj.get_property_err("Today").set_message(
"Invalid Today: '%s', valid format is '', 'yyyy-mm-dd', '+days', or '-days'." % (val))
def execute(self, input_views, output_views):
"""
Produce a Date Dimension list as output records.
"""
# Initializations before we start writing
try:
output_view = output_views.values()[keys.SINGLE_VIEW]
except IndexError:
raise SnapComponentError("No output view connected.")
# We only need to pre-create the record once
r = output_view.create_record()
# Some time calculation related constants we need
datetup = time.strptime(self.get_property_value("StartDate"), DateDimension._DATE_FORMAT)
start_date = datetime.date(datetup[0], datetup[1], datetup[2])
datetime.date(2008, 1, 1)
datetup = time.strptime(self.get_property_value("EndDate"), DateDimension._DATE_FORMAT)
end_date = datetime.date(datetup[0], datetup[1], datetup[2])
t = self.get_property_value("Today")
if t is None or t.strip() == '':
today = datetime.date.today()
elif t.startswith('+') or t.startswith('-'):
delta = datetime.timedelta(eval(t))
today = datetime.date.today() + delta
else:
datetup = time.strptime(t, DateDimension._DATE_FORMAT)
today = datetime.date(datetup[0], datetup[1], datetup[2])
fiscal_offset = int(self.get_property_value("FiscalOffset"))
next_day = datetime.timedelta(1)
# Get days of (calendar) quarter and year of today.
d1 = datetime.date(today.year, self._get_quarter_starting_month(today.month), 1)
d2 = datetime.date(today.year, 1, 1)
today_day_of_quarter = Decimal((today - d1).days + 1)
today_day_of_year = Decimal((today - d2).days + 1)
# Get fisical year/quarter/month of today.
# - fy_today: fiscal year of today
# - fq_today: fiscal quarter of today
# - fm_today: fiscal month of today
fy_today = today.year
fm_today = today.month - fiscal_offset
if fm_today <= 0:
fy_today -= 1
fm_today += 12
elif fm_today > 12:
fy_today += 1
fm_today -= 12
fq_today = self._get_quarter_number(fm_today)
# Generate a record for each date from the start date to the end date.
now = start_date
while now <= end_date:
datetup = now.timetuple()
year = unicode(now.year)
r['date_value'] = datetime.datetime(now.year, now.month, now.day)
r['year'] = year
r['quarter'] = DateDimension._QUARTERS[now.month-1]
r['month'] = DateDimension._MONTHS[now.month-1]
r['full_quarter'] = unicode(r['quarter'] + year[2:])
r['day_number'] = Decimal(datetup[7])
r['month_number'] = Decimal(datetup[1])
r['day_of_month'] = Decimal(datetup[2])
d1 = datetime.date(now.year, self._get_quarter_starting_month(now.month), 1)
r['day_of_quarter'] = Decimal((now - d1).days + 1)
now_day_of_quarter = r['day_of_quarter']
d2 = datetime.date(now.year, 1, 1)
now_day_of_year = (now - d2).days + 1
# Initialization of 'Y'/'N' flags.
r['is_today'] = u'N'
r['is_yesterday'] = u'N'
r['is_this_month'] = u'N'
r['is_last_month'] = u'N'
r['is_this_quarter'] = u'N'
r['is_last_quarter'] = u'N'
r['is_this_year'] = u'N'
r['is_last_year'] = u'N'
r['is_rolling_month'] = u'N'
r['is_rolling_last_month'] = u'N'
r['is_rolling_quarter'] = u'N'
r['is_rolling_last_quarter'] = u'N'
r['is_rolling_year'] = u'N'
r['is_rolling_last_year'] = u'N'
r['is_mtd'] = u'N'
r['is_last_mtd'] = u'N'
r['is_qtd'] = u'N'
r['is_last_qtd'] = u'N'
r['is_ytd'] = u'N'
r['is_last_ytd'] = u'N'
delta = today - now
# Same day
if delta.days == 0:
r['is_today'] = u'Y'
r['is_this_month'] = u'Y'
r['is_this_quarter'] = u'Y'
r['is_this_year'] = u'Y'
r['is_rolling_month'] = u'Y'
r['is_rolling_quarter'] = u'Y'
r['is_rolling_year'] = u'Y'
r['is_mtd'] = u'Y'
r['is_qtd'] = u'Y'
r['is_ytd'] = u'Y'
# Different day
else:
if delta.days == 1:
r['is_yesterday'] = u'Y'
# Same year
if today.year == now.year:
r['is_this_year'] = u'Y'
if today_day_of_year >= now_day_of_year:
r['is_ytd'] = u'Y'
# Same month
if today.month == now.month:
r['is_this_month'] = u'Y'
r['is_this_quarter'] = u'Y'
if today.day >= now.day:
r['is_mtd'] = u'Y'
if today_day_of_quarter >= now_day_of_quarter:
r['is_qtd'] = u'Y'
if today_day_of_year >= now_day_of_year:
r['is_ytd'] = u'Y'
# Different month
else:
if (today.month - now.month) == 1:
r['is_last_month'] = u'Y'
if today.day >= now.day:
r['is_last_mtd'] = u'Y'
# Same quarter
if self._get_quarter_number(today.month) == self._get_quarter_number(now.month):
r['is_this_quarter'] = u'Y'
if today_day_of_quarter >= now_day_of_quarter:
r['is_qtd'] = u'Y'
# Different quarter
else:
if self._get_quarter_number(today.month) - self._get_quarter_number(now.month) == 1:
r['is_last_quarter'] = u'Y'
if today_day_of_quarter >= now_day_of_quarter:
r['is_last_qtd'] = u'Y'
# Different year
else:
# Previous year
if (today.year - now.year) == 1:
r['is_last_year'] = u'Y'
if today_day_of_year >= now_day_of_year:
r['is_last_ytd'] = u'Y'
# Today is first quarter
if self._get_quarter_number(today.month) == 1:
# Now is last quarter of previous year
if self._get_quarter_number(now.month) == 4:
r['is_last_quarter'] = u'Y'
if today_day_of_quarter >= now_day_of_quarter:
r['is_last_qtd'] = u'Y'
if today.month == 1 and now.month == 12:
r['is_last_month'] = u'Y'
if today.day >= now.day:
r['is_last_mtd'] = u'Y'
delta = today - now
if (delta.days <= 30) and (delta.days >= 0):
r['is_rolling_month'] = u'Y'
r['is_rolling_quarter'] = u'Y'
r['is_rolling_year'] = u'Y'
elif (delta.days <= 60) and (delta.days >= 31):
r['is_rolling_last_month'] = u'Y'
r['is_rolling_quarter'] = u'Y'
r['is_rolling_year'] = u'Y'
elif (delta.days <= 90) and (delta.days >= 31):
r['is_rolling_quarter'] = u'Y'
r['is_rolling_year'] = u'Y'
elif (delta.days <= 180) and (delta.days >= 91):
r['is_rolling_last_quarter'] = u'Y'
r['is_rolling_year'] = u'Y'
elif (delta.days <= 365) and (delta.days >= 181):
r['is_rolling_year'] = u'Y'
elif (delta.days <= 730) and (delta.days >= 366):
r['is_rolling_last_year'] = u'Y'
# Fiscal year ...
# Calculate starting date of the fiscal year
# - cy: calendar year of the start of fiscal year of now
# - cm: calendar month of the fiscal month of now
# - fdate: date object for the start of fiscal year
cy = now.year
cm = fiscal_offset + 1
if cm <= 0:
cm += 12
if cm > now.month:
cy -= 1
fdate = datetime.date(cy, cm, 1)
# - fy: fiscal year of now
# - fq: fiscal quarter of now
# - fm: fiscal month of now
# - fqm: 1st calendar month of the fiscal quarter of now
# - fqdate: date object for the start of the fiscal quarter of now
if fiscal_offset < 0:
fy = fdate.year + 1
else:
fy = fdate.year
fm = now.month - fiscal_offset
if fm <= 0:
fm += 12
elif fm > 12:
fm -= 12
fq = self._get_quarter_number(fm)
fqm = ((fq - 1) * 3) + fiscal_offset + 1
if fqm <= 0:
fqm += 12
elif fqm > 12:
fqm -= 12
if fqm > now.month:
fqdate = datetime.date(now.year-1, fqm, 1)
else:
fqdate = datetime.date(now.year, fqm, 1)
r['fiscal_year'] = unicode(fy)
r['fiscal_short'] = unicode('FY' + r['fiscal_year'][2:])
if fiscal_offset == 0:
r['fiscal_long'] = unicode(r['fiscal_short'] + '-' + str(fy)[2:])
else:
r['fiscal_long'] = unicode(r['fiscal_short'] + '-' + str(fy+1)[2:])
r['fiscal_quarter'] = unicode('FQ' + str(fq))
r['fiscal_day_number'] = Decimal((now - fdate).days + 1)
r['fiscal_month_number'] = Decimal(fm)
r['fiscal_day_of_quarter'] = Decimal((now - fqdate).days + 1)
# Initialize all flags to be 'N' first.
r['is_this_fiscal_year'] = u'N'
r['is_last_fiscal_year'] = u'N'
r['is_this_fiscal_quarter'] = u'N'
r['is_last_fiscal_quarter'] = u'N'
if fy_today == fy:
r['is_this_fiscal_year'] = u'Y'
if fq_today == fq:
r['is_this_fiscal_quarter'] = u'Y'
elif fq_today - fq == 1:
r['is_last_fiscal_quarter'] = u'Y'
else:
if fy_today - fy == 1:
r['is_last_fiscal_year'] = u'Y'
if (fq_today == 1) and (fq == 4):
r['is_last_fiscal_quarter'] = u'Y'
# Write the record to output view.
output_view.write_record(r)
# Go to the next day.
now += next_day
# Closing the output stream when all done
output_view.completed()
def _get_quarter_number(self, mon):
"""
Get the quarter that the given month belongs to.
@param mon: The given month.
@type mon: int
@return: The quarter number, from 1 to 4.
@rtype: int
"""
return ((mon - 1) / 3) + 1
def _get_quarter_starting_month(self, mon):
"""
Get the starting month of the quarter that the given month belongs to.
@param mon: The given month.
@type mon: int
@return: The starting month of the quarter.
@rtype: int
"""
return (((mon - 1) / 3) * 3) + 1
def upgrade_1_0_to_1_1(self):
"""
No-op upgrade only to change component doc URI during the upgrade
which will be by cc_info before calling this method.
"""
pass
|