def populateCampaignRevenue10(wb):
sh = wb.sheet_by_name('Rev10')
for rownum in range(3,881): #sh.nrows):
t = sh.cell(rownum,2).value
product = get_or_create(db.session, Product, product = sh.cell(rownum,3).value)
cp = sh.cell(rownum,4).value
channel = get_or_create(db.session, Channel, channel = sh.cell(rownum,5).value)
agency = sh.cell(rownum,16).value
advertiser = get_or_create(db.session, Advertiser, advertiser = sh.cell(rownum,20).value)
campaign = sh.cell(rownum,21).value
industry = sh.cell(rownum,15).value
if(industry == '(blank)'):
industry = None
repid = sh.cell(rownum,22).value
if(repid == "VB"):
repid = "VV"
rep = get_or_create(db.session, Rep, repID = repid)
try:
start_date = xldate_as_tuple(sh.cell(rownum,24).value,0)[0:3]
py_start = date(*start_date)
end = xldate_as_tuple(sh.cell(rownum,25).value,0)[0:3]
py_end = date(*end)
except:
pass
contracted_deal = sh.cell(rownum,28).value
if not isinstance(contracted_deal, float):
contracted_deal = None
revised_deal = sh.cell(rownum,28).value
if not isinstance(revised_deal, float):
revised_deal = None
# For multiple reps:
instance = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first()
if instance:
instance.rep.append(rep)
db.session.commit()
c = instance
else:
c = Campaign(campaign = campaign, type = t, product = product, channel = channel, advertiser = advertiser, industry = industry,
agency = agency, rep = [rep], cp = cp, start_date = py_start, end_date = py_end, contracted_deal = contracted_deal, revised_deal = revised_deal)
#print(campaign)
db.session.add(c)
db.session.commit()
#campaignObj = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first()
for colnum in range(37,61):
rev = sh.cell(rownum,colnum).value
if isinstance(rev,float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(2,colnum).value,0)[0:3]
pyDate = date(*mydate)
a = Actual(campaign=c, date=pyDate, actualRev=rev)
db.session.add(a)
db.session.commit()
print("PopulateCampaignRevenue10 Finished")
def load_data():
# Create and fill Tables
wb = xlrd.open_workbook(
'C:/Users/rthomas/Desktop/DatabaseProject/SalesMetricData.xls')
wb.sheet_names()
# Fill revenue tables
sh = wb.sheet_by_name('Actual')
for rownum in range(2, sh.nrows):
camp_str = sh.cell(rownum, 0).value
rep = Rep.objects.get(repID=sh.cell(rownum, 1).value)
product = Product.objects.get(product=sh.cell(rownum, 2).value)
channel = Channel.objects.get(channel=sh.cell(rownum, 3).value)
advertiser = Advertiser.objects.get(
advertiser=sh.cell(rownum, 4).value)
start = xldate_as_tuple(sh.cell(rownum, 5).value, 0)[0:3]
py_start = datetime.date(*start)
end = xldate_as_tuple(sh.cell(rownum, 6).value, 0)[0:3]
py_end = datetime.date(*end)
try:
campaign = Campaign.objects.get(campaign=camp_str)
for colnum in range(7, sh.ncols):
rev = sh.cell(rownum, colnum).value
if isinstance(rev, float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(0, colnum).value, 0)[0:3]
pyDate = datetime.date(*mydate)
a = Actual(campaign=campaign, date=pyDate, actualRev=rev)
a.save()
except:
try:
campaign = Campaign.objects.get(campaign=camp_str,
repId=rep,
channel=channel,
product=product,
advertiser=advertiser,
start_date=py_start,
end_date=py_end)
for colnum in range(7, sh.ncols):
rev = sh.cell(rownum, colnum).value
if isinstance(rev, float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(0, colnum).value,
0)[0:3]
pyDate = datetime.date(*mydate)
a = Actual(campaign=campaign,
date=pyDate,
actualRev=rev)
a.save()
except:
pass
def readSFDCexcel():
s = db.session
s.query(Sfdc).delete()
wb = xlrd.open_workbook('C:/Users/rthomas/Desktop/DatabaseProject/SFDC OID Report 1-7-13.xls')
sh = wb.sheet_by_index(0)
for colnum in range(0, sh.ncols):
colname = sh.cell(0, colnum).value
if re.search('Opportunity ID', colname):
oid_index = colnum
if re.search('Channel',colname):
channel_index = colnum
if re.search('Pricing Model', colname):
cp_index = colnum
if re.search('Advertiser',colname):
advertiser_index = colnum
if re.search('Opportunity Owner',colname):
rep_index = colnum
if re.search('Start Date',colname):
start_index = colnum
if re.search('End Date',colname):
end_index = colnum
if re.search('Budget$',colname):
budget_index = colnum
if re.search('Insertion Order',colname):
ioname_index = colnum
if re.search('Currency',colname):
currency_index = colnum
for rownum in range(1,sh.nrows):
oid_val = sh.cell(rownum, oid_index).value
if oid_val is None or oid_val =='':
break
else:
oid = int(oid_val)
channel = sh.cell(rownum, channel_index).value
cp = sh.cell(rownum, cp_index).value
advertiser = sh.cell(rownum, advertiser_index).value
rep_name_temp = sh.cell(rownum, rep_index).value
last = re.search('[A-Z][a-z]*$', rep_name_temp)
first = re.search('^[A-Z][a-z]*', rep_name_temp)
rep_name = last.group() + ", " + first.group()
start = xldate_as_tuple(sh.cell(rownum,start_index).value,0)[0:3]
py_start = date(*start) + timedelta(days=1)
end = xldate_as_tuple(sh.cell(rownum,end_index).value,0)[0:3]
py_end = date(*end)
ioname = sh.cell(rownum, ioname_index).value
budget_val = sh.cell(rownum, budget_index).value
currency = sh.cell(rownum, currency_index).value
if budget_val == '':
budget_val = None
a = Sfdc(oid=oid, channel=channel, cp=cp, advertiser=advertiser, owner_name = rep_name, start_date = py_start, end_date = py_end, budget = budget_val, ioname=ioname, currency=currency, approved = False)
s.add(a)
s.commit()
def __load(self):
sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id]
sheetData = sheetDoc.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetData")
# @type sheetData Element
rows = {}
columns = {}
for rowNode in sheetData:
rowNum = int(rowNode.get("r"))
for columnNode in rowNode:
colType = columnNode.get("t")
cellId = columnNode.get("r")
cellS = columnNode.get("s")
colNum = cellId[:len(cellId)-len(str(rowNum))]
formula = None
data = ''
try:
if columnNode.find('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v') is not None:
if colType == "s":
stringIndex = columnNode[0].text
data = self.workbook.sharedStrings[int(stringIndex)]
#Built in date-formatted fields
elif cellS and int(self.workbook.cellStyles[int(cellS)].get('numFmtId')) in range(14, 22+1):
data = xldate_as_tuple(
float(columnNode[0].text),
datemode=0)
elif cellS and (self.workbook.cellStyles[int(cellS)].get('numFmtId') in self.workbook.numFmts) \
and is_date_format_string(self.workbook.numFmts[self.workbook.cellStyles[int(cellS)].get('numFmtId')]):
data = xldate_as_tuple(
float(columnNode[0].text),
datemode=0)
elif len(columnNode)>0 and columnNode[0] is not None:
data = columnNode.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v").text
if columnNode.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f") is not None:
formula = columnNode.find("{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f").text
except Exception:
raise #pass
if not rowNum in rows:
rows[rowNum] = []
if not colNum in columns:
columns[colNum] = []
cell = Cell(rowNum, colNum, data, formula=formula)
rows[rowNum].append(cell)
columns[colNum].append(cell)
self.__cells[cellId] = cell
self.__rows = rows
self.__cols = columns
self.loaded=True
def get_date_or_none(entry):
my_date = None
if isinstance(entry, float):
try:
date_tuple = xldate_as_tuple(entry, 0)[0:3]
my_date = date(*date_tuple)
except:
my_date = None
return my_date
def load_data():
# Create and fill Tables
wb = xlrd.open_workbook('C:/Users/rthomas/Desktop/DatabaseProject/SalesMetricData.xls')
wb.sheet_names()
# Fill revenue tables
sh = wb.sheet_by_name('Actual')
for rownum in range(2,sh.nrows):
camp_str = sh.cell(rownum,0).value
rep = Rep.objects.get(repID = sh.cell(rownum,1).value)
product = Product.objects.get(product = sh.cell(rownum,2).value)
channel = Channel.objects.get(channel = sh.cell(rownum,3).value)
advertiser = Advertiser.objects.get(advertiser = sh.cell(rownum,4).value)
start = xldate_as_tuple(sh.cell(rownum,5).value,0)[0:3]
py_start = datetime.date(*start)
end = xldate_as_tuple(sh.cell(rownum,6).value,0)[0:3]
py_end = datetime.date(*end)
try:
campaign = Campaign.objects.get(campaign = camp_str)
for colnum in range(7,sh.ncols):
rev = sh.cell(rownum,colnum).value
if isinstance(rev,float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(0,colnum).value,0)[0:3]
pyDate = datetime.date(*mydate)
a = Actual(campaign=campaign, date=pyDate, actualRev=rev)
a.save()
except:
try:
campaign = Campaign.objects.get(campaign = camp_str, repId = rep, channel=channel, product = product, advertiser = advertiser, start_date = py_start, end_date = py_end)
for colnum in range(7,sh.ncols):
rev = sh.cell(rownum,colnum).value
if isinstance(rev,float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(0,colnum).value,0)[0:3]
pyDate = datetime.date(*mydate)
a = Actual(campaign=campaign, date=pyDate, actualRev=rev)
a.save()
except:
pass
def _end_element(self, name):
#print "end element:", name
if name == 'sheetData':
self.is_sheetdata = False
elif self.is_sheetdata and name == 'row':
self.row_count += 1
self.document.row_event(self.current_row)
self.current_row = None
elif name == 'c':
c = self.cell
self.parse_rel(c)
if c[self.TYPE] == self.TYPE_SHARED_STRING:
idx = int(self.data, 10)
c[self.VALUE] = self.shared_strings[idx]
else:
c[self.VALUE] = self.data
#fmt = self.styles.numberFormat(c[self.STYLE_IDX])
#print "cell format is:", str(fmt['numFmt']), c[self.COLUMN], c[self.VALUE]
c[self.STYLE] = self.styles.cell_style(c[self.STYLE_IDX])
c[self.FMT] = cellType = self.styles.cell_type_from_style(
c[self.STYLE])
v = c[self.VALUE]
if (v is not None) and (c[self.FMT]
in (datetime.datetime, datetime.date,
datetime.time)):
try:
d = xldate.xldate_as_tuple(float(v), 0)
c[self.VALUE] = cellType(*d)
except (xldate.XLDateAmbiguous, ValueError), e:
if v == 1.0:
print "value 1.0 for date:", c
c[self.VALUE] = ''
else:
print "Invalid date, assume text or number content:", c[
self.VALUE]
if re.match(r'^\d+$', v):
c[self.TYPE] = cellType = int
elif re.match(r'^\d+\.\d+$', v):
c[self.TYPE] = cellType = float
else:
c[self.TYPE] = cellType = unicode
c[self.VALUE] = cellType(v)
else:
if v is None:
c[self.VALUE] = ''
elif not cellType is unicode:
try:
c[self.VALUE] = cellType(v)
except TypeError, e:
print repr(c)
print str(e), "value:", repr(v)
raise e
def _end_element(self, name):
#print "end element:", name
if name == 'sheetData':
self.is_sheetdata = False
elif self.is_sheetdata and name == 'row':
self.row_count += 1
self.document.row_event(self.current_row)
self.current_row = None
elif name == 'c':
c = self.cell
self.parse_rel(c)
if c[self.TYPE] == self.TYPE_SHARED_STRING:
idx = int(self.data, 10)
c[self.VALUE] = self.shared_strings[idx]
else:
c[self.VALUE] = self.data
#fmt = self.styles.numberFormat(c[self.STYLE_IDX])
#print "cell format is:", str(fmt['numFmt']), c[self.COLUMN], c[self.VALUE]
c[self.STYLE] = self.styles.cell_style(c[self.STYLE_IDX])
c[self.FMT] = cellType = self.styles.cell_type_from_style(c[self.STYLE])
v = c[self.VALUE]
if (v is not None) and (c[self.FMT] in (datetime.datetime,
datetime.date,
datetime.time)):
try:
d = xldate.xldate_as_tuple(float(v), 0)
c[self.VALUE] = cellType(*d)
except (xldate.XLDateAmbiguous, ValueError), e:
if v == 1.0:
print "value 1.0 for date:", c
c[self.VALUE] = ''
else:
print "Invalid date, assume text or number content:", c[self.VALUE]
if re.match(r'^\d+$', v):
c[self.TYPE] = cellType = int
elif re.match(r'^\d+\.\d+$', v):
c[self.TYPE] = cellType = float
else:
c[self.TYPE] = cellType = unicode
c[self.VALUE] = cellType(v)
else:
if v is None:
c[self.VALUE] = ''
elif not cellType is unicode:
try:
c[self.VALUE] = cellType(v)
except TypeError, e:
print repr(c)
print str(e), "value:", repr(v)
raise e
def __load(self):
sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id]
sheetData = sheetDoc.firstChild.getElementsByTagName("sheetData")[0]
# @type sheetData Element
rows = {}
columns = {}
for rowNode in sheetData.childNodes:
rowNum = int(rowNode.getAttribute("r"))
for columnNode in rowNode.childNodes:
colType = columnNode.getAttribute("t")
cellId = columnNode.getAttribute("r")
cellS = columnNode.getAttribute("s")
colName = cellId[:len(cellId)-len(str(rowNum))]
colOffset = columnOffset(colName)
formula = None
data = ''
try:
if colType == "s":
stringIndex = columnNode.firstChild.firstChild.nodeValue
data = self.workbook.sharedStrings[int(stringIndex)]
#Date field
elif cellS in ('1', '2', '3', '4') and colType == "n":
data = xldate_as_tuple(
int(columnNode.firstChild.firstChild.nodeValue),
datemode=0)
elif columnNode.firstChild:
data = getattr(
columnNode.getElementsByTagName("v")[0].firstChild,
"nodeValue", None)
if columnNode.getElementsByTagName("f"):
formula = getattr(
columnNode.getElementsByTagName("f")[0].firstChild,
"nodeValue", None)
except Exception:
pass
if not rowNum in rows:
rows[rowNum] = []
if not colName in columns:
columns[colName] = []
cell = Cell(rowNum, colName, data, formula=formula)
row = rows[rowNum]
while len(row) < colOffset - 1:
row.append(Cell(rowNum, colName, None))
row.append(cell)
columns[colName].append(cell)
self.__cells[cellId] = cell
self.__rows = rows
self.__cols = columns
self.loaded=True
def __load(self):
sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id]
sheetData = sheetDoc.firstChild.getElementsByTagName("sheetData")[0]
# @type sheetData Element
rows = {}
columns = {}
for rowNode in sheetData.childNodes:
rowNum = int(rowNode.getAttribute("r"))
for columnNode in rowNode.childNodes:
colType = columnNode.getAttribute("t")
cellId = columnNode.getAttribute("r")
cellS = columnNode.getAttribute("s")
colNum = cellId[:len(cellId) - len(str(rowNum))]
formula = None
data = ''
try:
if colType == "s":
stringIndex = columnNode.firstChild.firstChild.nodeValue
data = self.workbook.sharedStrings[int(stringIndex)]
#Date field
elif cellS in ('1', '2', '3', '4') and colType == "n":
data = xldate_as_tuple(int(
columnNode.firstChild.firstChild.nodeValue),
datemode=0)
elif columnNode.firstChild:
data = getattr(
columnNode.getElementsByTagName("v")[0].firstChild,
"nodeValue", None)
if columnNode.getElementsByTagName("f"):
formula = getattr(
columnNode.getElementsByTagName("f")[0].firstChild,
"nodeValue", None)
except Exception:
pass
if not rowNum in rows:
rows[rowNum] = []
if not colNum in columns:
columns[colNum] = []
cell = Cell(rowNum, colNum, data, formula=formula)
rows[rowNum].append(cell)
columns[colNum].append(cell)
self.__cells[cellId] = cell
self.__rows = rows
self.__cols = columns
self.loaded = True
def populateCampaignRevenue(wb):
sh = wb.sheet_by_name("Rev041813_585")
for rownum in range(2, 6198): # sh.nrows):
campaign = sh.cell(rownum, 13).value
print(campaign)
date_created = D.now()
t = sh.cell(rownum, 3).value
product = get_or_create(db.session, Product, product=sh.cell(rownum, 4).value)
chan = sh.cell(rownum, 5).value
if chan == "MSLAL":
chan = "Publisher"
channel = get_or_create(db.session, Channel, channel=chan)
try:
advertiser = db.session.query(Advertiser).filter_by(advertiser=sh.cell(rownum, 6).value).first()
except:
advertiser = None
print(sh.cell(rownum, 6).value + " not found")
industry = sh.cell(rownum, 8).value
agency = sh.cell(rownum, 9).value
sfdc_oid = sh.cell(rownum, 10).value
if not isinstance(sfdc_oid, float):
sfdc_oid = None
else:
sfdc_oid = int(sfdc_oid)
repid = sh.cell(rownum, 14).value
if repid == "VB":
repid = "VV"
rep = get_or_create(db.session, Rep, repID=repid)
cp = sh.cell(rownum, 15).value
try:
start_date = xldate_as_tuple(sh.cell(rownum, 16).value, 0)[0:3]
py_start = date(*start_date)
end = xldate_as_tuple(sh.cell(rownum, 17).value, 0)[0:3]
py_end = date(*end)
except:
pass
cpm_price = sh.cell(rownum, 19).value
if not isinstance(cpm_price, float):
cpm_price = None
contracted_impr = sh.cell(rownum, 20).value
if isinstance(contracted_impr, float):
contracted_impr = int(contracted_impr)
else:
contracted_impr = None
contracted_deal = sh.cell(rownum, 21).value
if not isinstance(contracted_deal, float):
contracted_deal = None
revised_deal = sh.cell(rownum, 23).value
if not isinstance(revised_deal, float):
revised_deal = None
# For multiple reps:
camp_instance = (
db.session.query(Campaign).filter_by(campaign=campaign, start_date=py_start, end_date=py_end).first()
)
if camp_instance:
camp_instance.rep.append(rep)
db.session.commit()
c = camp_instance
else:
if sfdc_oid == 11919:
camp_instance = db.session.query(Campaign).filter_by(sfdc_oid=11919).first()
if camp_instance:
c = camp_instance
camp_instance.rep.append(rep)
db.session.commit()
else:
c = Campaign(
campaign=campaign,
date_created=date_created,
type=t,
product=product,
channel=channel,
advertiser=advertiser,
industry=industry,
agency=agency,
sfdc_oid=sfdc_oid,
rep=[rep],
cp=cp,
start_date=py_start,
end_date=py_end,
cpm_price=cpm_price,
contracted_impr=contracted_impr,
contracted_deal=contracted_deal,
revised_deal=revised_deal,
)
db.session.add(c)
db.session.commit()
cc = Campaignchange(
campaign=c,
change_date=D.now(),
start_date=py_start,
end_date=py_end,
cpm_price=cpm_price,
revised_deal=revised_deal,
)
db.session.add(cc)
db.session.commit()
else:
c = Campaign(
campaign=campaign,
date_created=date_created,
type=t,
product=product,
channel=channel,
advertiser=advertiser,
industry=industry,
agency=agency,
sfdc_oid=sfdc_oid,
rep=[rep],
cp=cp,
start_date=py_start,
end_date=py_end,
cpm_price=cpm_price,
contracted_impr=contracted_impr,
contracted_deal=contracted_deal,
revised_deal=revised_deal,
)
db.session.add(c)
db.session.commit()
cc = Campaignchange(
campaign=c,
change_date=date_created,
start_date=py_start,
end_date=py_end,
cpm_price=cpm_price,
revised_deal=revised_deal,
)
db.session.add(cc)
db.session.commit()
for colnum in chain(xrange(26, 38), xrange(56, 68), xrange(88, 100)):
rev = sh.cell(rownum, colnum).value
if isinstance(rev, float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(1, colnum).value, 0)[0:3]
pyDate = date(*mydate)
book_instance = db.session.query(Booked).filter_by(campaign=c, date=pyDate).first()
if book_instance:
rev = book_instance.bookedRev + rev
book_instance.bookedRev = rev
else:
a = Booked(campaign=c, date_created=date_created, date=pyDate, bookedRev=rev)
db.session.add(a)
aa = Bookedchange(campaign=c, change_date=date_created, date=pyDate, bookedRev=rev)
db.session.add(aa)
db.session.commit()
for colnum in chain(xrange(41, 53), xrange(73, 85), xrange(103, 115)):
rev = sh.cell(rownum, colnum).value
if isinstance(rev, float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(1, colnum).value, 0)[0:3]
pyDate = date(*mydate)
actual_instance = db.session.query(Actual).filter_by(campaign=c, date=pyDate).first()
if actual_instance:
rev = actual_instance.actualRev + rev
actual_instance.actualRev = rev
else:
a = Actual(campaign=c, date_created=date_created, date=pyDate, actualRev=rev)
db.session.add(a)
# For CPM, we need to add
if cp == "CPM" and pyDate.year == 2013:
b = Booked(campaign=c, date_created=date_created, date=pyDate, bookedRev=rev)
db.session.add(b)
bb = Bookedchange(campaign=c, change_date=date_created, date=pyDate, bookedRev=rev)
db.session.add(bb)
aa = Actualchange(campaign=c, change_date=date_created, date=pyDate, actualRev=rev)
db.session.add(aa)
db.session.commit()
print("PopulateRevenue Finished")
def populateCampaignRevenue10(wb):
sh = wb.sheet_by_name("Rev10")
for rownum in range(3, 881): # sh.nrows):
date_created = D.now()
t = sh.cell(rownum, 2).value
product = get_or_create(db.session, Product, product=sh.cell(rownum, 3).value)
cp = sh.cell(rownum, 4).value
channel = get_or_create(db.session, Channel, channel=sh.cell(rownum, 5).value)
agency = sh.cell(rownum, 16).value
try:
advertiser = db.session.query(Advertiser).filter_by(advertiser=sh.cell(rownum, 20).value).first()
except:
advertiser = None
print(sh.cell(rownum, 6).value + " not found")
campaign = sh.cell(rownum, 21).value
industry = sh.cell(rownum, 15).value
if industry == "(blank)":
industry = None
repid = sh.cell(rownum, 22).value
if repid == "VB":
repid = "VV"
rep = get_or_create(db.session, Rep, repID=repid)
try:
start_date = xldate_as_tuple(sh.cell(rownum, 24).value, 0)[0:3]
py_start = date(*start_date)
end = xldate_as_tuple(sh.cell(rownum, 25).value, 0)[0:3]
py_end = date(*end)
except:
pass
contracted_deal = sh.cell(rownum, 28).value
if not isinstance(contracted_deal, float):
contracted_deal = None
revised_deal = sh.cell(rownum, 28).value
if not isinstance(revised_deal, float):
revised_deal = None
# For multiple reps:
instance = db.session.query(Campaign).filter_by(campaign=campaign, start_date=py_start, end_date=py_end).first()
if instance:
instance.rep.append(rep)
db.session.commit()
c = instance
else:
c = Campaign(
campaign=campaign,
date_created=date_created,
type=t,
product=product,
channel=channel,
advertiser=advertiser,
industry=industry,
agency=agency,
rep=[rep],
cp=cp,
start_date=py_start,
end_date=py_end,
contracted_deal=contracted_deal,
revised_deal=revised_deal,
)
db.session.add(c)
db.session.commit()
cc = Campaignchange(
campaign=c, change_date=D.now(), start_date=py_start, end_date=py_end, revised_deal=revised_deal
)
db.session.add(cc)
db.session.commit()
for colnum in range(37, 49):
rev = sh.cell(rownum, colnum).value
if isinstance(rev, float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(2, colnum).value, 0)[0:3]
pyDate = date(*mydate)
actual_instance = db.session.query(Actual).filter_by(campaign=c, date=pyDate).first()
if actual_instance:
rev = actual_instance.actualRev + rev
actual_instance.actualRev = rev
else:
a = Actual(campaign=c, date=pyDate, actualRev=rev)
db.session.add(a)
aa = Actualchange(campaign=c, change_date=D.now(), date=pyDate, actualRev=rev)
db.session.add(aa)
db.session.commit()
print("PopulateCampaignRevenue10 Finished")
def populateCampaignRevenue(wb):
sh = wb.sheet_by_name('Rev020113')
for rownum in range(5,5092): #sh.nrows):
campaign = sh.cell(rownum,13).value
t = sh.cell(rownum,3).value
product = get_or_create(db.session, Product, product = sh.cell(rownum,4).value)
chan = sh.cell(rownum,5).value
if(chan == "MSLAL"):
chan = "Publisher"
channel = get_or_create(db.session, Channel, channel = chan)
advertiser = get_or_create(db.session, Advertiser, advertiser = sh.cell(rownum,6).value)
industry = sh.cell(rownum,8).value
agency = sh.cell(rownum,9).value
sfdc_oid = sh.cell(rownum,10).value
if not isinstance(sfdc_oid, float):
sfdc_oid = None
else:
sfdc_oid = int(sfdc_oid)
repid = sh.cell(rownum,14).value
if(repid == "VB"):
repid = "VV"
rep = get_or_create(db.session, Rep, repID = repid)
cp = sh.cell(rownum,15).value
try:
start_date = xldate_as_tuple(sh.cell(rownum,16).value,0)[0:3]
py_start = date(*start_date)
end = xldate_as_tuple(sh.cell(rownum,17).value,0)[0:3]
py_end = date(*end)
except:
pass
cpm_price = sh.cell(rownum,19).value
if not isinstance(cpm_price, float):
cpm_price = None
contracted_impr = sh.cell(rownum,20).value
if isinstance(contracted_impr, float):
contracted_impr = int(contracted_impr)
else:
contracted_impr = None
contracted_deal = sh.cell(rownum,21).value
if not isinstance(contracted_deal, float):
contracted_deal = None
revised_deal = sh.cell(rownum,23).value
if not isinstance(revised_deal, float):
revised_deal = None
# For multiple reps:
instance = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first()
if instance:
instance.rep.append(rep)
db.session.commit()
c = instance
else:
if sfdc_oid == 11919:
instance = db.session.query(Campaign).filter_by(sfdc_oid = 11919).first()
if instance:
c = instance
instance.rep.append(rep)
db.session.commit()
else:
c = Campaign(campaign = campaign, type = t, product = product, channel = channel, advertiser = advertiser,
industry = industry, agency = agency, sfdc_oid = sfdc_oid, rep = [rep], cp = cp, start_date = py_start, end_date = py_end, cpm_price = cpm_price,
contracted_impr = contracted_impr, contracted_deal = contracted_deal, revised_deal =revised_deal)
db.session.add(c)
db.session.commit()
else:
c = Campaign(campaign = campaign, type = t, product = product, channel = channel, advertiser = advertiser,
industry = industry, agency = agency, sfdc_oid = sfdc_oid, rep = [rep], cp = cp, start_date = py_start, end_date = py_end, cpm_price = cpm_price,
contracted_impr = contracted_impr, contracted_deal = contracted_deal, revised_deal =revised_deal)
print(campaign)
db.session.add(c)
db.session.commit()
#campaignObj = db.session.query(Campaign).filter_by(campaign = campaign, start_date = py_start, end_date = py_end).first()
for colnum in range(26,62):
rev = sh.cell(rownum,colnum).value
if isinstance(rev,float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(4,colnum).value,0)[0:3]
pyDate = date(*mydate)
a = Booked(campaign=c, date=pyDate, bookedRev=rev)
db.session.add(a)
db.session.commit()
for colnum in range(62,98):
rev = sh.cell(rownum,colnum).value
if isinstance(rev,float) and rev != 0.0:
mydate = xldate_as_tuple(sh.cell(4,colnum).value,0)[0:3]
pyDate = date(*mydate)
a = Actual(campaign=c, date=pyDate, actualRev=rev)
db.session.add(a)
db.session.commit()
print("PopulateRevenue Finished")
def __load(self):
sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id]
sheetData = sheetDoc.firstChild.getElementsByTagName("sheetData")[0]
# @type sheetData Element
rows = {}
columns = {}
for rowNode in sheetData.childNodes:
rowNum = int(rowNode.getAttribute("r"))
cell_ids = []
for columnNode in rowNode.childNodes:
colType = columnNode.getAttribute("t")
cellId = columnNode.getAttribute("r")
cellS = columnNode.getAttribute("s")
colNum = cellId[:len(cellId)-len(str(rowNum))]
formula = None
data = ''
# malformed files with blank colNums
# if this is the case, we just have to assume we're not
# missing any cells.
if not colNum:
warnings.warn('Blank Column Found')
if not cell_ids:
colNum = u'A'
else:
colNum = num2xlcol(cell_ids[-1] + 1)
cellId = u'{0}{1}'.format(colNum, rowNum)
if not rowNum in rows:
rows[rowNum] = []
if not colNum in columns:
columns[colNum] = []
# fill in missing cells
col_idx = xlcol2num(colNum)
if not cell_ids and col_idx > 1:
for i in range(1, col_idx):
xlcol = num2xlcol(i)
warnings.warn('Filling In Skipped Cell {0}'.format(
xlcol
))
cell = Cell(rowNum, xlcol, u'', formula=None)
rows[rowNum].append(cell)
columns[colNum].append(cell)
self.__cells[u'{0}{1}'.format(xlcol, rowNum)] = cell
cell_ids.append(col_idx)
elif cell_ids and col_idx > cell_ids[-1] + 1:
for i in range(cell_ids[-1] + 1, col_idx):
xlcol = num2xlcol(i)
warnings.warn('Filling In Skipped Cell {0}'.format(
xlcol
))
cell = Cell(rowNum, xlcol, u'', formula=None)
rows[rowNum].append(cell)
columns[colNum].append(cell)
self.__cells[u'{0}{1}'.format(xlcol, rowNum)] = cell
cell_ids.append(col_idx)
cell_ids.append(col_idx)
try:
if colType == "s":
stringIndex = columnNode.firstChild.firstChild.nodeValue
data = self.workbook.sharedStrings[int(stringIndex)]
#Date field
elif cellS in ('1', '2', '3', '4') and colType == "n":
data = xldate_as_tuple(
int(columnNode.firstChild.firstChild.nodeValue),
datemode=0)
elif columnNode.firstChild:
data = getattr(
columnNode.getElementsByTagName("v")[0].firstChild,
"nodeValue", None)
if columnNode.getElementsByTagName("f"):
formula = getattr(
columnNode.getElementsByTagName("f")[0].firstChild,
"nodeValue", None)
except Exception:
pass
cell = Cell(rowNum, colNum, data, formula=formula)
rows[rowNum].append(cell)
columns[colNum].append(cell)
self.__cells[cellId] = cell
self.__rows = rows
self.__cols = columns
self.loaded=True
def rowsIter(self):
sheetDoc = self.workbook.domzip["xl/worksheets/sheet%d.xml" % self.id]
sheetData = sheetDoc.find(
"{http://schemas.openxmlformats.org/spreadsheetml/2006/main}sheetData"
)
# @type sheetData Element
for rowNode in sheetData:
rowNum = int(rowNode.get("r"))
rowCells = []
for columnNode in rowNode:
colType = columnNode.get("t")
cellId = columnNode.get("r")
cellS = columnNode.get("s")
colNum = cellId[:len(cellId) - len(str(rowNum))]
formula = None
data = ''
try:
if len(
columnNode
) > 0 and columnNode[0] is not None and columnNode.find(
'{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v'
) is not None:
if colType == "s":
stringIndex = columnNode[0].text
data = self.workbook.sharedStrings[int(
stringIndex)]
#Built in date-formatted fields
elif cellS and re.match("^[\d\.]+$",
columnNode[0].text):
if int(self.workbook.cellStyles[int(cellS)].get(
'numFmtId')) in range(14, 22 + 1):
data = xldate_as_tuple(float(
columnNode[0].text),
datemode=0)
elif (self.workbook.cellStyles[int(cellS)].get('numFmtId') in self.workbook.numFmts) \
and is_date_format_string(self.workbook.numFmts[self.workbook.cellStyles[int(cellS)].get('numFmtId')]):
data = xldate_as_tuple(float(
columnNode[0].text),
datemode=0)
else:
data = columnNode.find(
"{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v"
).text
else:
data = columnNode.find(
"{http://schemas.openxmlformats.org/spreadsheetml/2006/main}v"
).text
elif columnNode.find(
"{http://schemas.openxmlformats.org/spreadsheetml/2006/main}is"
) is not None:
if colType == "inlineStr":
data = columnNode[0][0].text
if columnNode.find(
"{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f"
) is not None:
formula = columnNode.find(
"{http://schemas.openxmlformats.org/spreadsheetml/2006/main}f"
).text
except Exception:
raise #pass
cell = Cell(rowNum, colNum, data, formula=formula)
rowCells.append(cell)
yield rowNum, rowCells
