def createYear(year):
begin = datetime.datetime.strptime(year + '-01-01 00:00:00', formatStr)
end = begin + datetime.timedelta(days=yearDays(year))
end -= datetime.timedelta(seconds=1)
session.execute("select time_id from dw_time where time_code='" +
str(int(year) - 1) + "'")
pre_id = session.fetchone()[0]
time_code = year
time_name = '%s年' % year
time_type_id = type_y
time_timetype = getToTime(begin.strftime(formatStr))
begin_time = time_timetype
end_time = getToTime(end.strftime(formatStr))
duration = yearDays(year)
order_in_year = '1'
parent_term_id = '0'
previous_term_id = pre_id
lastyear_term_id = pre_id
sql = '''insert into dw_time(time_code,time_name,time_type_id,time_timetype,begin_time,end_time,duration,order_in_year,parent_term_id,previous_term_id,lastyear_term_id)
values('%s','%s','%s',%s,%s,%s,'%s','%s','%s','%s','%s') ON conflict(time_name) DO nothing''' % (
time_code, time_name, time_type_id, time_timetype, begin_time,
end_time, duration, order_in_year, parent_term_id, previous_term_id,
lastyear_term_id)
session.execute(sql)
CONN.commit()
def createArea():
session.execute(
'SELECT DISTINCT area_name from temp1 WHERE area_id IS NULL')
for row in session.fetchall():
session.execute("INSERT INTO dw_area(area_name) VALUES('" + row[0] +
"') ON conflict(area_name) DO nothing")
CONN.commit()
print 'createArea', 'complete!'
def createZbfm():
createZbzm()
session.execute(
'SELECT DISTINCT zbfm_name from temp1 where zbfm_id is null')
for row in session.fetchall():
session.execute("INSERT into dw_zhs_d_zbfm(zbfm_name) VALUES('" +
row[0] + "') ON conflict(zbfm_name) DO nothing")
CONN.commit()
print 'createZbfm', 'complete!'
def createZbzm():
createObject()
session.execute(
'SELECT DISTINCT zbzm_name,table_id from temp1 WHERE zbzm_id IS NULL')
for row in session.fetchall():
session.execute(
"INSERT into dw_zhs_d_zbzm(zbzm_name,table_id,zbzm_code) VALUES('"
+ row[0] + "','" + str(row[1]) + "','" + getFirstChars(row[0]) +
"') ON conflict(zbzm_name,table_id) DO nothing")
CONN.commit()
print 'createZbzm', 'complete!'
def createObject():
createTable()
session.execute(
'SELECT DISTINCT object_name from temp1 WHERE object_id IS NULL')
for row in session.fetchall():
session.execute("INSERT into dw_zhs_d_object(object_name) VALUES('" +
row[0] + "') ON conflict(object_name) DO nothing")
session.execute(
"UPDATE dw_zhs_d_object set object_id=object_id1, object_code=object_id1,object_order=object_id1 where object_name='"
+ row[0] + "' and object_code is NULL")
CONN.commit()
print 'createObject', 'complete!'
def createTable():
createSheet()
session.execute(
'SELECT DISTINCT table_name, sheet_id from temp1 WHERE table_id IS NULL'
)
for row in session.fetchall():
session.execute(
"INSERT into dw_zhs_d_table(table_name,sheet_id,table_code) VALUES('"
+ row[0] + "','" + str(row[1]) + "','" + getFirstChars(row[0]) +
"') ON conflict(table_name,sheet_id) DO nothing")
CONN.commit()
print 'createTable', 'complete!'
def createExcel():
createArea()
session.execute(
'SELECT DISTINCT excel_name, area_id from temp1 WHERE excel_id2 IS NULL'
)
for row in session.fetchall():
session.execute(
"INSERT INTO dw_zhs_d_excel(excel_name, excel_code, area_id) VALUES('"
+ row[0] + "','" + getFirstChars(row[0]) + "'," + str(row[1]) +
") ON conflict(excel_name, area_id) DO nothing")
CONN.commit()
print 'createExcel', 'complete!'
def createMonth(year):
createQuarter(year)
for i in range(1, 13):
begin = datetime.datetime.strptime(year + '-%02d' % i + '-01 00:00:00',
formatStr)
end = begin + datetime.timedelta(days=monthDays(year, i))
end -= datetime.timedelta(seconds=1)
session.execute("select time_id from dw_time where begin_time<=" +
getToTime(begin.strftime(formatStr)) +
" and end_time>=" +
getToTime(begin.strftime(formatStr)) +
" and time_type_id='" + type_q + "'")
parent = session.fetchone()[0]
session.execute("select time_id from dw_time where time_code='" +
str(int(year) - 1) + '%02d' % i +
"' and time_type_id='" + type_m + "'")
lastYear = session.fetchone()[0]
pre_id = '-1'
if i > 1:
session.execute("select time_id from dw_time where time_code='" +
year + '%02d' % (i - 1) + "' and time_type_id='" +
type_m + "'")
pre_id = session.fetchone()[0]
time_code = year + '%02d' % i
time_name = '%s年%02d月' % (year, i)
time_type_id = type_m
time_timetype = getToTime(begin.strftime(formatStr))
begin_time = time_timetype
end_time = getToTime(end.strftime(formatStr))
duration = monthDays(year, i)
order_in_year = str(i)
parent_term_id = parent
previous_term_id = '0' if i == 1 else pre_id
lastyear_term_id = lastYear
sql = '''insert into dw_time(time_code,time_name,time_type_id,time_timetype,begin_time,end_time,duration,order_in_year,parent_term_id,previous_term_id,lastyear_term_id)
values('%s','%s','%s',%s,%s,%s,'%s','%s','%s','%s','%s') ON conflict(time_name) DO nothing''' % (
time_code, time_name, time_type_id, time_timetype, begin_time,
end_time, duration, order_in_year, parent_term_id,
previous_term_id, lastyear_term_id)
session.execute(sql)
CONN.commit()
def createHalfYear(year):
createYear(year)
for i in range(1, 3):
begin = datetime.datetime.strptime(
year + '-' + str(6 * i - 5) + '-01 00:00:00', formatStr)
end = begin + datetime.timedelta(days=halfYearDays(year, i))
end -= datetime.timedelta(seconds=1)
session.execute("select time_id from dw_time where time_code='" +
year + "'")
parent = session.fetchone()[0]
session.execute("select time_id from dw_time where time_code='" +
str(int(year) - 1) + str(i) + "' and time_type_id='" +
type_hy + "'")
lastYear = session.fetchone()[0]
pre_id = '-1'
if i > 1:
session.execute("select time_id from dw_time where time_code='" +
year + str(i - 1) + "' and time_type_id='" +
type_hy + "'")
pre_id = session.fetchone()[0]
time_code = year + str(i)
time_name = '%s年上半年' % year if i == 1 else '%s年下半年' % year
time_type_id = type_hy
time_timetype = getToTime(begin.strftime(formatStr))
begin_time = time_timetype
end_time = getToTime(end.strftime(formatStr))
duration = halfYearDays(year, i)
order_in_year = str(i)
parent_term_id = parent
previous_term_id = '0' if i == 1 else pre_id
lastyear_term_id = lastYear
sql = '''insert into dw_time(time_code,time_name,time_type_id,time_timetype,begin_time,end_time,duration,order_in_year,parent_term_id,previous_term_id,lastyear_term_id)
values('%s','%s','%s',%s,%s,%s,'%s','%s','%s','%s','%s') ON conflict(time_name) DO nothing''' % (
time_code, time_name, time_type_id, time_timetype, begin_time,
end_time, duration, order_in_year, parent_term_id,
previous_term_id, lastyear_term_id)
session.execute(sql)
CONN.commit()