#!/usr/bin/python
#
# processCSV.py
#
# Dale Peters, II
# November 6, 2008
#
# This script creates and populates HUD's Insured Multifamily Mortgages
# database (migrated from dBASE III to mySQL).
#
# Some ways to run this script:
#   ./processCSV.py | mysql -u root -p
#   ./processCSV.py > /tmp/_my.sql;  mysql -u root -p </tmp/_my.sql
#
# Copyright (C) 2008 Hak Chinoy, Inc
# This software may be used and distributed according to the terms of the GNU
# General Public License, incorporated herein by reference.
################################################################################
# Modification History:
#
# 2008-11-07 by:  Dale Peters, II.
# Code updated:   I updated my documentation.
#

import csv, formatter, htmllib, os, pdb, sre, sys, urllib, urllib2
from string import capwords, lower, ascii_lowercase as letters

# open the CSV file and extract table description
a=csv.reader(open('/tmp/F47D.csv', 'rb'), quotechar='\'')
b=a.next()

# create housing (the db) and stats (the table)
e="""create database housing;
use housing;
DROP TABLE IF EXISTS `stats`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `stats` (
  `id` int(11) unsigned NOT NULL auto_increment,"""
f="""  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=121 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
alter table stats AUTO_INCREMENT=1;"""
headers=[]
nFields=0
dates=[]
print e
for c in csv.reader(b, quotechar='\''):
  _id='`'+lower(c[0])+'`'
  headers.append(lower(c[0]))
  if lower(c[1])=='c': _tp='char('+c[2]+')'
  elif lower(c[1])=='n':
    if len(c)==3: _tp='int('+c[2]+')'
    else: _tp='decimal('+c[2]+', '+c[3]+')'
  else:
    _tp='date'
    dates.append(nFields)
  print '  '+_id+' '+_tp+' default NULL,'
  nFields+=1
print f

# write remainder of the CSV file and normalize the capitalization
d=open('/tmp/.F47D.csv', 'w')
for e in a:
  str=''
  _i=0
  for f in e:
    if _i in dates and len(f)>3:
      ss=f.split('/')
      if int(ss[2])>8: str+=('19'+ss[2]+'-'+ss[0]+'-'+ss[1]+', ')
      else: str+=('20'+ss[2]+'-'+ss[0]+'-'+ss[1]+', ')
    else:
      if len(f)>2: str+=(capwords(f)+', ')
      else: str+=(f+', ')
    _i+=1
  d.write(str[0:len(str)-2]+'\n')
d.close()

# import the normalized CSV file into housing.stats
str='load data local infile \'/tmp/.F47D.csv\' into table stats fields terminated by \',\' lines terminated by \'\\n\' ('
for c in headers: str+=(c+', ')
print str[0:len(str)-2]+')'