def updateinvo(jo, thisdate):
idata = Invoices.query.filter(Invoices.Jo == jo).all()
total = 0.0
for idat in idata:
each = float(idat.Qty)
amtea = float(idat.Ea)
amount = each * amtea
idat.Amount = nodollar(amount)
db.session.commit()
total = total + amount
for idat in idata:
idat.Date = thisdate
idat.Total = nodollar(total)
db.session.commit()
if jo[1] == 'T':
ldata = Invoices.query.filter(Invoices.Jo == jo).order_by(
Invoices.Ea.desc()).all()
myo = Orders.query.filter(Orders.Jo == jo).first()
cache = myo.Storage + 1
pdata1 = People.query.filter(People.id == myo.Bid).first()
pdata2 = Drops.query.filter(Drops.id == myo.Lid).first()
pdata3 = Drops.query.filter(Drops.id == myo.Did).first()
from make_T_invoice import T_invoice
T_invoice(myo, ldata, pdata1, pdata2, pdata3, cache, thisdate, 0)
if cache > 1:
docref = f'tmp/{scac}/data/vinvoice/INV' + myo.Jo + 'c' + str(
cache) + '.pdf'
# Store for future use
else:
docref = f'tmp/{scac}/data/vinvoice/INV' + myo.Jo + '.pdf'
for ldatl in ldata:
ldatl.Pid = pdata1.id
ldatl.Original = docref
db.session.commit()
myo.Path = docref
myo.Storage = cache
db.session.commit()
if jo[1] == 'O':
odat = OverSeas.query.filter(OverSeas.Jo == jo).first()
invooder = odat.id
cache = nonone(odat.Cache) + 1
from invoices import invoiceO
invo, err, leftscreen, leftsize, docref, invodate = invoiceO(
invooder, 0)
if cache > 1:
docref = f'tmp/{scac}/data/vinvoice/INV' + jo + 'c' + str(
cache) + '.pdf'
# Store for future use
else:
docref = f'tmp/{scac}/data/vinvoice/INV' + jo + '.pdf'
odat.Cache = cache
modata = OverSeas.query.get(invooder)
jo = modata.Jo
booking = modata.Booking
ldata = Invoices.query.filter(Invoices.Jo == jo).order_by(
Invoices.Jo).all()
def main(odata, ldata, pdata1, invojo, involine, paidline, refline, balline, invodate, cache, payment):
# pdata1:Bid (Bill To)
# pdata2:Lid (Load At)
# pdata3:Did (Delv To)
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import letter
from reportlab.lib.pagesizes import landscape
from reportlab.platypus import Image
from reportlab.lib.units import inch
import csv
import math
import datetime
import shutil
from viewfuncs import sdiff, sadd, dollar, nodollar, nononef
from CCC_system_setup import myoslist, addpath, bankdata, scac
joborder = invojo
file1 = addpath(f'tmp/{scac}/data/vinvoice/INV'+joborder+'.pdf')
file2 = addpath(f'tmp/{scac}/data/vinvoice/INV'+joborder+'c'+str(cache)+'.pdf')
type = joborder[1]
#today = datetime.datetime.today().strftime('%m/%d/%Y')
# if invodate is None or invodate==0:
# invodate=today
# else:
try:
invodate = invodate.strftime('%m/%d/%Y')
except:
err = 'ivodate already a string'
billhistory = involine
payhistory = paidline
openbalance = balline
custref = refline
pyopenbal = odata.BalFwd
pyopenbal = nononef(pyopenbal)
pyopenbal = nodollar(pyopenbal)
a = pyopenbal
for j, i in enumerate(openbalance):
if billhistory[j] != '' or payhistory[j] != '':
openbalance[j] = sadd(a, i)
a = openbalance[j]
if j > 0:
b = openbalance[j-1]
else:
b = pyopenbal
# 'a' is the last open balance and should be added to the bill
try:
prevbal = float(b)
except:
prevbal = 0.00
def avg(in1, in2):
out = (in1+in2)/2
return out
def comporname(company, name):
if company is None or company == '':
nameout = name
else:
if len(company) < 4:
nameout = name
else:
nameout = company
return nameout
def fullname(first, middle, last):
if first is not None:
nameout = first
else:
nameout = ''
if middle is not None:
nameout = nameout+' '+middle
if last is not None:
nameout = nameout+' '+last
if len(nameout) > 55:
nameout = first + ' ' + last
return nameout
def address(addr1, addr2, addr3):
street = addr1
if addr3 is None or addr3 == '':
cityst = addr2
else:
if len(addr3) < 5:
cityst = addr2
if addr2 is None or addr2 == '':
cityst = addr3
if len(addr2) < 3:
cityst = addr3
if addr2 and addr3:
if len(addr2) > 3 and len(addr3) > 3:
street = addr1 + ' ' + addr2
cityst = addr3
return street, cityst
def nononestr(input):
if input is None or input == 'None':
input = ''
return input
def nonone(input):
if input is None:
input = 0
return input
billto = list(range(5))
if pdata1 is not None:
billto[0] = comporname(pdata1.Company, fullname(pdata1.First, pdata1.Middle, pdata1.Last))
billto[1] = nononestr(pdata1.Addr1)
billto[2] = nononestr(pdata1.Addr2)
billto[3] = nononestr(pdata1.Telephone)
billto[4] = nononestr(pdata1.Email)
else:
for i in range(5):
billto[i] = ' '
us = list(range(4))
us[0] = 'FIRST EAGLE LOGISTICS INC'
us[1] = '505 HAMPTON PARK BLVD UNIT O'
us[2] = 'CAPITOL HEIGHTS MD 20743'
us[3] = '301-516-3000 [email protected]'
line1 = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
line2 = ['Quantity', 'Item Code', 'Description', 'Price Each', 'Amount']
line3 = billhistory
line41 = payhistory
line42 = custref
line5 = openbalance
note = list(range(3))
note[0] = '*All invoices that are 15 days past due will incurr a $35.00 late fee.'
note[1] = '*After 60 days an additional late fee will be assessed in the amount of $100.00.'
note[2] = '*If your account reaches 90 days past due it will be submitted for collection.'
lab1 = 'Balance Due'
lab2 = 'Add $39.00 for all international wires'
nonote, bank = bankdata('FC')
# ___________________________________________________________
ltm = 36
rtm = 575
ctrall = 310
left_ctr = 170
right_ctr = 480
dl = 17.6
tdl = dl*2
hls = 530
m1 = hls-dl
m2 = hls-2*dl
m3 = hls-3*dl
m4 = hls-4*dl
m5 = hls-5*dl
m61 = hls-6*dl
m62 = hls-7*dl
m63 = hls-8*dl
m7 = hls-20*dl
m8 = hls-23*dl
m9 = hls-27*dl
fulllinesat = [m1, m2, m3, m4, m5, m61, m62, m63, m7, m8, m9]
p = [0]*13
p[0] = ltm+87
for i in range(1, 13):
p[i] = p[i-1]+37.7
n1 = ltm+58
n2 = ltm+128
n3 = rtm-140
n4 = rtm-70
sds2 = [n1, n2, n3, n4]
q1 = ltm+180
q2 = rtm-180
sds3 = [q1, q2]
bump = 2.5
tb = bump*2
c = canvas.Canvas(file1, pagesize=letter)
c.setLineWidth(1)
logo = addpath("tmp/felpics/logo3.jpg")
c.drawImage(logo, 185, 680, mask='auto')
# Date and JO boxes
dateline = m1+8.2*dl
c.rect(rtm-150, m1+7*dl, 150, 2*dl, stroke=1, fill=0)
c.line(rtm-150, dateline, rtm, dateline)
c.line(rtm-75, m1+7*dl, rtm-75, m1+9*dl)
if type == 'S':
# Address boxes
ctm = 218
c.rect(ltm, m1+dl, 175, 5*dl, stroke=1, fill=0)
#c.rect(ctm, m1+dl,175,5*dl, stroke=1, fill=0)
#c.rect(rtm-175, m1+dl,175,5*dl, stroke=1, fill=0)
level1 = m1+5*dl
c.line(ltm, level1, ltm+175, level1)
# c.line(ctm,level1,ctm+175,level1)
# c.line(rtm-175,level1,rtm,level1)
for i in fulllinesat:
c.line(ltm, i, rtm, i)
for k in p[0:12]:
c.line(k, m1, k, m61)
for l in sds2:
c.line(l, m62, l, m7)
for m in sds3:
c.line(m, m8, m, m9)
c.line(ltm, m1, ltm, m9)
c.line(rtm, m1, rtm, m9)
h1 = avg(m8, m9)-3
c.line(q2, h1, rtm, h1)
c.setFont('Helvetica-Bold', 24, leading=None)
c.drawCentredString(rtm-75, dateline+1.5*dl, 'Invoice')
if payment != 0:
c.setFont('Helvetica-Bold', 18, leading=None)
c.drawCentredString(rtm-75, dateline-50, 'Payment Received')
c.setFont('Helvetica', 12, leading=None)
c.drawCentredString(rtm-112.5, dateline+bump, 'Date')
c.drawCentredString(rtm-37.7, dateline+bump, 'Invoice #')
c.drawString(ltm+bump*3, m1+5*dl+bump*2, 'Bill To')
# c.drawString(ctm+bump*3,m1+5*dl+bump*2,'Load At')
# c.drawString(rtm-170+bump*2,m1+5*dl+bump*2,'Delv To')
dh = 12
ct = 305
if payment != 0:
try:
thispay = float(payment[0])
except:
thispay = 0.00
top = m1+4*dl-5
try:
c.drawString(ct, top, 'Your payment of '+payment[0]+', Ref No. '+payment[1])
c.drawString(ct, top-dh, 'was applied on '+payment[2])
except:
c.drawString(ct, top, 'There is no payment data as of yet')
else:
thispay = 0.00
c.drawString(ct, m1+dl, 'Balance Fwd from 2018: $'+pyopenbal)
j = 0
ctr = [avg(ltm, n1), avg(n1, n2), avg(n2, n3), avg(n3, n4), avg(n4, rtm)]
for i in line2:
c.drawCentredString(ctr[j], m63+tb, i)
j = j+1
top = m8-1.5*dh
for i in bank:
c.drawCentredString(ct, top, i)
top = top-dh
top = m1+9*dl-5
for i in us:
c.drawString(ltm+bump, top, i)
top = top-dh
bottomline = m8-23
c.setFont('Helvetica-Bold', 12, leading=None)
c.drawString(q2+tb, bottomline, 'Balance Due:')
c.setFont('Helvetica', 10, leading=None)
c.drawCentredString(avg(q2, rtm), m9+12, 'Add $39.00 for all international wires')
c.setFont('Times-Roman', 9, leading=None)
j = 0
dh = 9.95
top = m7-dh
for i in note:
c.drawString(ltm+tb, top, note[j])
j = j+1
top = top-dh
# _______________________________________________________________________
# Insert data here
# _______________________________________________________________________
c.setFont('Helvetica', 12, leading=None)
dh = 13
top = level1-dh
lft = ltm+bump*3
for i in billto:
c.drawString(lft, top, i)
top = top-dh
x = avg(rtm-75, rtm)
y = dateline-dh-bump
c.drawCentredString(x, y, joborder)
x = avg(rtm-75, rtm-150)
try:
c.drawCentredString(x, y, invodate)
except:
err = 'Date not set yet'
c.setFont('Helvetica', 9, leading=None)
c.drawCentredString(avg(ltm, p[0]), m2+tb, '2019 by Month:')
c.drawCentredString(avg(ltm, p[0]), m3+tb, 'Bill History')
c.drawCentredString(avg(ltm, p[0]), m4+tb, 'Pay History')
c.drawCentredString(avg(ltm, p[0]), m5+tb, 'Cust Ref No.')
c.drawCentredString(avg(ltm, p[0]), m61+tb, 'Open Balances')
for j, i in enumerate(line1):
ctr = avg(p[j], p[j+1])
c.drawCentredString(ctr, m2+tb, i)
c.drawCentredString(ctr, m3+tb, line3[j])
c.drawCentredString(ctr, m4+tb, line41[j])
c.drawCentredString(ctr, m5+tb, line42[j])
c.drawCentredString(ctr, m61+tb, line5[j])
total = 0
top = m63-dh
for data in ldata:
qty = int(nonone(data.Qty))
each = float(nonone(data.Ea))
subtotal = qty*each
total = total+subtotal
line4 = [str(qty), data.Service]
line5 = nononestr(data.Description)
line6 = [each, subtotal]
ctr = [avg(ltm, n1), avg(n1, n2)]
for j, i in enumerate(line4):
c.drawCentredString(ctr[j], top, i)
ctr = [n4-tb*2, rtm-tb*2]
for j, i in enumerate(line6):
c.drawRightString(ctr[j], top, dollar(i))
line5a = line5.splitlines()
for line in line5a:
c.drawString(n2+tb, top, line)
top = top-dh
top = top-dh
if prevbal > 0:
c.drawString(n2+tb, top, 'Open balance from previous month')
c.drawRightString(rtm-tb*2, top, dollar(prevbal))
total = total+prevbal-thispay
c.drawRightString(rtm-tb*2, bottomline, dollar(total))
c.showPage()
c.save()
#
# Now make a cache copy
shutil.copy(file1, file2)
d2last = d2
delta = d2 - d1
chassisdays = delta.days + add
fromjay = chassisdays * 30
netpay = paytojay - fromjay
#print (d1,' to ',d2, data.Jo, chassisdays)
#print(' ')
elif ticket1 is not None or ticket2 is not None:
truckjobcontainers.append(container)
print('Have mismatch in tickets on job')
explain = 'Trucking for ' + patron.Company
itemlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), booking, container, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
odata = OverSeas.query.filter((OverSeas.PuDate >= start)
& (OverSeas.PuDate <= end)).filter(
OverSeas.Driver.contains("Khoder")).order_by(
OverSeas.PuDate).all()
for data in odata:
booking = data.Booking
container = data.Container
ticket1 = Interchange.query.filter(
Interchange.Container == container).first()
def jaycalcs():
today = datetime.datetime.today().strftime('%m/%d/%Y')
invodate = datetime.date.today().strftime('%m/%d/%Y')
sdate = request.values.get('start')
fdate = request.values.get('finish')
start = datetime.datetime.strptime(sdate, '%Y-%m-%d')
end = datetime.datetime.strptime(fdate, '%Y-%m-%d')
startdate = start.date()
itemlist = []
truckjobs = []
oceanjobs = []
jayjobs = []
partjobs = []
jaypartjobs = []
yardmoves = []
conlist = []
def get_take(con):
take = 0
type = 'J'
move = 'deliver'
cdata = Interchange.query.filter(Interchange.Container == con).all()
for cdat in cdata:
driver = cdat.Driver
chassis = cdat.Chassis
if "Khoder" in driver or "Jay" in driver:
take = take + 1
odat = Orders.query.filter(Orders.Container == con).first()
if odat is not None:
type = 'T'
ndat = OverSeas.query.filter(OverSeas.Container == con).first()
if ndat is not None:
type = 'O'
if 'toll' in chassis.lower():
type = 'Y'
if move == 'ym1toll1':
move = 'ym2toll2'
if move == 'ym1':
move = 'ym2toll1'
if move == 'deliver':
move = 'ym1toll1'
if 'yard' in chassis.lower():
type = 'Y'
if move == 'ym1toll1':
move = 'ym2toll1'
if move == 'ym1':
move = 'ym2'
if move == 'deliver':
move = 'ym1'
print(con, chassis, move)
return take, type, move
def get_chassisdays(con):
d1 = None
d2 = None
ticket1 = Interchange.query.filter(
Interchange.Container == con).first()
try:
ticket2 = Interchange.query.filter(
(Interchange.Container == con)
& (Interchange.id != ticket1.id)).first()
except:
ticket2 is None
chassisdays = 0
if ticket1 is not None and ticket2 is not None:
date1 = ticket1.Date
date2 = ticket2.Date
if date1 > date2:
d1 = date2
d2 = date1
else:
d1 = date1
d2 = date2
delta = d2 - d1
chassisdays = delta.days + 1
return d1, d2, chassisdays
def get_special(con):
dispo = 'N'
ticket = Interchange.query.filter(Interchange.Container == con).first()
if ticket is not None:
chassis = ticket.Chassis
if 'yard' in chassis.lower():
dispo = 'Y'
if 'toll' in chassis.lower():
dispo = 'T'
return dispo
def jay_special(con):
dispo = 'N'
booking = 'UNK'
ticket = Interchange.query.filter(Interchange.Container == con).first()
if ticket is not None:
chassis = ticket.Chassis
booking = ticket.Release
if 'jay' in chassis.lower():
dispo = 'J'
return dispo, booking
def jay_special2(con):
dispo = 'N'
booking = 'UNK'
d1 = 'None'
ticket = Interchange.query.filter(Interchange.Container == con).first()
if ticket is not None:
chassis = ticket.Chassis
booking = ticket.Release
d1 = ticket.Date
if 'jay' in chassis.lower():
dispo = 'J'
return dispo, booking, d1
idata = Interchange.query.filter(
(Interchange.Date >= start) & (Interchange.Date <= end)).filter(
(Interchange.Driver.contains("Khoder"))
| (Interchange.Driver.contains("Jay"))).order_by(
Interchange.Date).all()
for idat in idata:
if idat.Status == 'IO':
con = idat.Container
take, type, move = get_take(con)
if move == 'deliver':
if take == 2 and con not in conlist:
conlist.append(con)
if type == 'O':
oceanjobs.append(con)
if type == 'T':
truckjobs.append(con)
if type == 'J':
jayjobs.append(con)
if take == 1:
if type != 'J':
partjobs.append(con)
else:
jaypartjobs.append(con)
else:
yardmoves.append(con)
print('yard=', yardmoves)
#Financial Calcs:Ocean containers
total = 0.00
for con in oceanjobs:
odat = OverSeas.query.filter(OverSeas.Container == con).first()
d1, d2, chassisdays = get_chassisdays(con)
if d1 >= startdate:
booking = odat.Booking
explain = 'Ocean container warehouse + return'
paytojay = 500.00
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), booking, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
#Financial Calcs:Trucking with containers
for con in truckjobs:
odat = Orders.query.filter(Orders.Container == con).first()
d1, d2, chassisdays = get_chassisdays(con)
if d1 >= startdate:
order = odat.Order
booking = odat.Booking
company = odat.Shipper
explain = 'Trucking job ' + company
contractamt = float(odat.Amount)
paytojay = .8 * contractamt
if 'Global' not in explain:
fromjay = 30.0 * chassisdays
else:
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), booking, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
#Financial Calcs:Trucking with dry van
odata = Orders.query.filter((Orders.Date >= start) & (Orders.Date <= end)
& (Orders.Driver.contains("Khoder"))
& (Orders.Container.contains('53DV'))).all()
for odat in odata:
d1 = odat.Date
d2 = odat.Date2
order = odat.Order
booking = odat.Booking
company = odat.Shipper
explain = 'Dry van job ' + company
contractamt = float(odat.Amount)
paytojay = .8 * contractamt
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), order, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
#Financial Calcs:One way Items
for con in partjobs:
odat = Orders.query.filter(Orders.Container == con).first()
if odat is not None:
d1, d2, chassisdays = get_chassisdays(con)
#d1=odat.Date
#d2=odat.Date2
if d1 >= startdate:
chassisday = 'None'
order = odat.Order
booking = odat.Booking
company = odat.Shipper
contractamt = float(odat.Amount)
dispo = get_special(con)
if dispo == 'N':
explain = 'Half-trucking job'
paytojay = .5 * contractamt
if dispo == 'Y':
explain = 'Move to Yard'
paytojay = 50.00
if dispo == 'T':
explain = 'Reposition with Tolls'
paytojay = 100.00
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), order, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
ndat = OverSeas.query.filter(OverSeas.Container == con).first()
if ndat is not None:
d1 = ndat.PuDate
d2 = ndat.RetDate
try:
d1s = d1.strftime('%m/%d/%Y')
except:
d1s = 'None'
try:
d2s = d2.strftime('%m/%d/%Y')
except:
d2s = 'None'
booking = ndat.Booking
dispo = get_special(con)
if dispo == 'N':
explain = 'Half-warehouse job'
paytojay = 250.00
if dispo == 'Y':
explain = 'Move to Yard'
paytojay = 50.00
if dispo == 'T':
explain = 'Reposition with Tolls'
paytojay = 100.00
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1s, d2s, booking, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
#Financial Calcs: Jay containers
for con in jayjobs:
d1, d2, chassisdays = get_chassisdays(con)
if d1 >= startdate:
verify, booking = jay_special(con)
explain = 'Jay job verified as: ' + verify
paytojay = 0.00
fromjay = 30.00 * chassisdays
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), booking, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
#Financial Calcs: Jay part containers
for con in jaypartjobs:
verify, booking, d1 = jay_special2(con)
explain = 'Jay job missing ticket: ' + verify
paytojay = 0.00
chassisdays = 6
fromjay = 30.00 * chassisdays
netpay = paytojay - fromjay
total = total + netpay
itemlist.append([
d1.strftime('%m/%d/%Y'),
d1.strftime('%m/%d/%Y'), booking, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
for con in yardmoves:
idat = Interchange.query.filter(Interchange.Container == con).first()
booking = idat.Release
take, type, move = get_take(con)
d1, d2, chassisdays = get_chassisdays(con)
if move == 'ym2toll2':
explain = 'Two yard moves 2 tolls'
paytojay = 150.00
if move == 'ym2toll1':
explain = 'Two yard moves 1 toll'
paytojay = 125.00
if move == 'ym2':
explain = 'Two yard moves each'
paytojay = 50.00
if move == 'ym1toll1':
explain = 'Yard move with toll'
paytojay = 75.00
if move == 'ym1':
explain = 'Yard move'
paytojay = 50.00
chassisdays = 0
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
print('Here is:', booking, con, explain, move)
itemlist.append([
d1.strftime('%m/%d/%Y'),
d1.strftime('%m/%d/%Y'), booking, con, explain,
nodollar(paytojay),
str(chassisdays),
nodollar(fromjay),
nodollar(netpay)
])
bitemlist = []
#Find expenses paid for Jay
paymentlist = []
servicelist = []
#Financial Calcs:Income Credits to Jay, Items Jay Paid For
odata = OverSeas.query.filter((OverSeas.PuDate >= start)
& (OverSeas.RetDate <= end)
& (OverSeas.BillTo.contains("Jays"))).all()
for odat in odata:
d1 = odat.PuDate
d2 = odat.RetDate
jo = odat.Jo
booking = odat.Booking
print('Booking', booking, jo)
con = odat.Container
chassisdays = ''
explain = 'Credit for Payments made: Overseas Shipping'
idat = Income.query.filter(Income.Jo == jo).first()
print(idat)
if idat is not None:
print('Found Income')
paytojay = float(idat.Amount)
fromjay = 0.00
netpay = paytojay - fromjay
total = total + netpay
paymentlist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), booking, con, explain,
nodollar(paytojay)
])
indat = Invoices.query.filter(Invoices.Jo == jo).first()
if indat is not None:
print('Found Invoices')
explain2 = 'Services Provided: Overseas Shipping'
amount = float(indat.Total)
servicelist.append([
d1.strftime('%m/%d/%Y'),
d2.strftime('%m/%d/%Y'), booking, con, explain2,
nodollar(amount)
])
print(bitemlist)
bdata = FELBills.query.filter(
(FELBills.bDate >= start) & (FELBills.bDate <= end)
& ((FELBills.Co == 'J') | (FELBills.bCat == 'JaysAuto'))).order_by(
FELBills.bDate).all()
for data in bdata:
print(data.bDate, data.Description, data.bAmount)
bitemlist.append(
[data.bDate.strftime('%Y-%m-%d'), data.Description, data.bAmount])
btotal = 0.00
l2 = len(bitemlist)
print('l2=', l2)
for i in range(l2):
newlist = bitemlist[i]
amount = newlist[2]
btotal = btotal + float(amount)
print(amount, btotal)
print(dollar(btotal))
nettotal = total - btotal
print(dollar(nettotal))
return paymentlist, servicelist, itemlist, bitemlist, total, btotal, nettotal
def plcalcs():
today = datetime.datetime.today().strftime('%m/%d/%Y')
invodate = datetime.date.today().strftime('%m/%d/%Y')
sdate = request.values.get('start')
fdate = request.values.get('finish')
start = datetime.datetime.strptime(sdate, '%Y-%m-%d')
end = datetime.datetime.strptime(fdate, '%Y-%m-%d')
itemlist = []
limit = 20
#Need itemlist in date order and job type order: T,O,S,M
invodat = Invoices.query.filter((Invoices.Date >= start)
& (Invoices.Date <= end)
& (Invoices.Jo.contains('FT'))).order_by(
Invoices.Date).all()
for invo in invodat:
jo = invo.Jo
invoamt = float(invo.Amount)
d1 = invo.Date
service = invo.Service
odat = Orders.query.filter(Orders.Jo == jo).first()
if odat is not None:
order = odat.Order
container = odat.Container
if '53D' in container:
container = '53DryV'
loc1 = odat.Company
loc2 = odat.Company2
if len(loc1) > limit:
loc1 = loc1[0:limit - 1]
if len(loc2) > limit:
loc2 = loc2[0:limit - 1]
if container is None:
container == 'None'
itemlist.append([
d1.strftime('%m/%d/%Y'), 'T', service, order, container, loc1,
loc2,
nodollar(invoamt)
])
invodat = Invoices.query.filter((Invoices.Date >= start)
& (Invoices.Date <= end)
& (Invoices.Jo.contains('FO'))).order_by(
Invoices.Date).all()
for invo in invodat:
jo = invo.Jo
invoamt = float(invo.Amount)
d1 = invo.Date
service = invo.Service
odat = OverSeas.query.filter(OverSeas.Jo == jo).first()
if odat is not None:
booking = odat.Booking
container = odat.Container
if container is not None:
if '53D' in container:
container = '53DryV'
else:
container = 'None'
loc1 = odat.Pol
loc2 = odat.Pod
if len(loc1) > limit:
loc1 = loc1[0:limit - 1]
if len(loc2) > limit:
loc2 = loc2[0:limit - 1]
if container is None:
container == 'None'
if service == 'Towing':
try:
desc = invo.Description
vin = desc.split('VIN:', 1)[1]
loc1 = vin.strip()
if len(loc1) > 17:
loc1 = loc1[0:16]
except:
loc1 = 'NoVin'
itemlist.append([
d1.strftime('%m/%d/%Y'), 'O', service, booking, container, loc1,
loc2,
nodollar(invoamt)
])
invodat = Invoices.query.filter((Invoices.Date >= start)
& (Invoices.Date <= end)
& (Invoices.Jo.contains('FS'))).order_by(
Invoices.Date).all()
for invo in invodat:
jo = invo.Jo
invoamt = float(invo.Amount)
d1 = invo.Date
service = invo.Service
container = ''
loc2 = invo.Description
if len(loc2) > limit:
loc2 = loc2[0:limit - 1]
odat = Storage.query.filter(Storage.Jo == jo).first()
if odat is not None:
loc1 = odat.Company
if len(loc1) > limit:
loc1 = loc1[0:limit - 1]
else:
loc1 = 'None'
itemlist.append([
d1.strftime('%m/%d/%Y'), 'S', service, jo, container, loc1, loc2,
nodollar(invoamt)
])
invodat = Invoices.query.filter((Invoices.Date >= start)
& (Invoices.Date <= end)
& (Invoices.Jo.contains('FM'))).order_by(
Invoices.Date).all()
for invo in invodat:
jo = invo.Jo
invoamt = float(invo.Amount)
d1 = invo.Date
service = invo.Service
container = ''
loc2 = invo.Description
if len(loc2) > limit:
loc2 = loc2[0:limit - 1]
odat = Moving.query.filter(Moving.Jo == jo).first()
if odat is not None:
loc1 = odat.Shipper
if len(loc1) > limit:
loc1 = loc1[0:limit - 1]
else:
loc1 = 'None'
itemlist.append([
d1.strftime('%m/%d/%Y'), 'M', service, jo, container, loc1, loc2,
nodollar(invoamt)
])
blist = []
categories = ['Container', 'Towing', 'Fuel', 'Payroll', 'Rentals', 'Other']
for cat in categories:
bdata = FELBills.query.filter(
(FELBills.bDate >= start) & (FELBills.bDate <= end)
& (FELBills.bClass.contains(cat))
& ((FELBills.bType == 'Direct')
| (FELBills.bType == 'JobSp'))).order_by(FELBills.bDate).all()
for bdat in bdata:
bclass = bdat.bClass
d1 = bdat.bDate
company = bdat.Company
if len(company) > limit:
company = company[0:limit - 1]
desc = bdat.Description
desc = desc.replace('\n', ' ')
desc = desc.replace('\r', '')
desc = desc.strip()
if len(desc) > limit + 10:
desc = desc[0:limit + 9]
bamount = float(bdat.bAmount)
acct = bdat.Account
blist.append([
d1.strftime('%m/%d/%Y'), 'D:' + cat, company, desc, acct,
nodollar(bamount)
])
categories = [
'BldRent', 'BldRepMaint', 'Utilities', 'Adv-Mark', 'BankFees', 'Taxes',
'OfficeSupp', 'Insurance', 'ProfFees', 'Other'
]
for cat in categories:
bdata = FELBills.query.filter(
(FELBills.bDate >= start) & (FELBills.bDate <= end)
& (FELBills.bType == 'G-A')
& (FELBills.bClass.contains(cat))).order_by(FELBills.bDate).all()
for bdat in bdata:
bclass = bdat.bClass
d1 = bdat.bDate
company = bdat.Company
if len(company) > limit:
company = company[0:limit - 1]
desc = bdat.Description
desc = desc.replace('\n', ' ')
desc = desc.replace('\r', '')
desc = desc.strip()
if len(desc) > limit + 10:
desc = desc[0:limit + 9]
bamount = float(bdat.bAmount)
acct = bdat.Account
if len(acct) > 12:
acct = acct[0:11]
blist.append([
d1.strftime('%m/%d/%Y'), 'I:' + cat, company, desc, acct,
nodollar(bamount)
])
return itemlist, blist
def custcalcs(thiscomp):
today = datetime.datetime.today().strftime('%m/%d/%Y')
thisday = datetime.date.today()
invodate = datetime.date.today().strftime('%m/%d/%Y')
sdate = request.values.get('start')
fdate = request.values.get('finish')
start = datetime.datetime.strptime(sdate, '%Y-%m-%d')
end = datetime.datetime.strptime(fdate, '%Y-%m-%d')
d2 = None
oceantype = request.values.get('dt1')
trucktype = request.values.get('dt2')
openbalrequest = request.values.get('dc6')
keyname = ['JO', 'Order', 'Booking', 'Container', 'BOL']
plist = [50, 65, 65, 70, 60]
keydataname = []
ptot = 0.0
pstops = []
for i in range(5):
key = request.values.get('dc' + str(i + 1))
if key == 'on':
keydataname.append(keyname[i])
ptot = ptot + plist[i]
pstops.append(plist[i])
tofromavail = 220 - (ptot - 140)
tfeach = round(tofromavail / 12.0 / .8)
print(oceantype, trucktype)
itemlist = []
print(trucktype, oceantype, start, end)
if trucktype == 'on':
if thiscomp == 'ALLT':
odata = Orders.query.filter((Orders.Date >= start)
& (Orders.Date <= end)).order_by(
Orders.Date).all()
elif thiscomp == 'LB' or thiscomp == 'WM' or thiscomp == 'AF' or thiscomp == 'BP':
pdata = People.query.filter(People.Addr3 == thiscomp).all()
companies = []
for pdat in pdata:
companies.append(pdat.Company)
print(companies)
odata = Orders.query.filter(
(Orders.Date >= start) & (Orders.Date <= end)
& (Orders.Shipper.in_(companies))).order_by(Orders.Date).all()
else:
odata = Orders.query.filter(
(Orders.Date >= start) & (Orders.Date <= end)
& (Orders.Shipper == thiscomp)).order_by(Orders.Date).all()
elif oceantype == 'on':
odata = OverSeas.query.filter(
(OverSeas.PuDate >= start) & (OverSeas.PuDate <= end)
& (OverSeas.BillTo == thiscomp)).order_by(OverSeas.PuDate).all()
for odat in odata:
if trucktype == 'on':
d1 = odat.Date
keydata_all = [
odat.Jo, odat.Order, odat.Booking, odat.Container, odat.BOL
]
elif oceantype == 'on':
d1 = odat.PuDate
keydata_all = [
odat.Jo, odat.MoveType, odat.Booking, odat.Container,
odat.ContainerType
]
#keydata_all=[odat.Jo,odat.Order,odat.Booking,odat.Container,odat.BOL]
keydata = []
for i in range(5):
key = request.values.get('dc' + str(i + 1))
if key == 'on':
keydata.append(keydata_all[i])
invodat = Invoices.query.filter(Invoices.Jo == odat.Jo).first()
if invodat is not None:
invoamt = invodat.Total
d2 = invodat.Date
try:
invof = float(invoamt)
except:
invof = 0.00
else:
invof = 0.00
incodat = Income.query.filter(Income.Jo == odat.Jo).first()
if incodat is not None:
incoamt = incodat.Amount
try:
incof = float(incoamt)
except:
incof = 0.00
else:
incof = 0.00
openf = invof - incof
if trucktype == 'on':
shi = odat.Shipper
loc = odat.Company
if shi is None: shi = 'No Shipper'
if loc is None: loc = 'No Location'
if 'seagirt' in loc.lower(): loc = odat.Company2
elif oceantype == 'on':
shi = odat.Pol
loc = odat.Pod
if 'port' in loc.lower() or 'baltimore' in loc.lower():
loc = 'Seagirt'
if 'baltimore' in loc.lower():
loc = 'Seagirt'
if len(shi) > tfeach:
shi = shi[0:tfeach - 1]
if len(loc) > tfeach:
loc = loc[0:tfeach - 1]
shi = shi.title()
loc = loc.title()
print('openbalrequest=', openbalrequest, openf)
if d2 is not None:
d1 = d2
if openbalrequest == 'on' and openf > 0.0:
delta = thisday - d1
ndays = delta.days
itemlist.append(
[d1.strftime('%m/%d/%Y')] + keydata +
[shi, loc,
nodollar(invof),
str(ndays),
nodollar(openf)])
elif openbalrequest == 'off' or openbalrequest is None:
itemlist.append(
[d1.strftime('%m/%d/%Y')] + keydata +
[shi, loc,
nodollar(invof),
nodollar(incof),
nodollar(openf)])
#itemlist.append([d1.strftime('%m/%d/%Y'),order,container,loc1,loc2,nodollar(invof),nodollar(incof),nodollar(openf)])
if openbalrequest == 'on':
if thiscomp == 'ALLT':
headerlist = ['InvoDate'] + keydataname + [
'Company', 'Key-Loc', 'Invo$', 'Days', 'Open$'
]
else:
headerlist = ['InvoDate'] + keydataname + [
'From', 'To', 'Invo$', 'Days', 'Open$'
]
else:
headerlist = ['InvoDate'] + keydataname + [
'From', 'To', 'Invo$', 'Paid$', 'Open$'
]
print('headerlist=', headerlist)
itemlist = sorted(itemlist, key=itemgetter(0))
return itemlist, headerlist, pstops