def pmWorkOrdersCompleted(userId):
pmWorkOrders = db.execute("SELECT SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '1' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS january, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '2' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS february, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '3' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS march, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '4' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS april, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '5' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS may, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '6' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS june, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '7' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS july, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '8' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS august, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '9' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS september, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '10' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS octomber, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '11' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS november, SUM(totalprice) FILTER (WHERE EXTRACT(MONTH FROM dateofissue) = '12' AND date_trunc('year', dateofissue) = date_trunc('year', CURRENT_DATE) ) AS december FROM invoice WHERE user_id = :user_id", {'user_id': userId}).fetchall()
pmWorkOrders = convertTupleSQLtoDict(pmWorkOrders)
return pmWorkOrders
def curentMonthPm(userId):
report = db.execute("SELECT COUNT(jobpriority) FILTER (WHERE date_trunc('month', start_event)::date = date_trunc('month', CURRENT_DATE)::date AND status = 'Open' AND jobPriority = 'Low Priority') AS lowpriority, COUNT(jobpriority) FILTER (WHERE date_trunc('month', start_event)::date = date_trunc('month', CURRENT_DATE)::date AND status = 'Open' AND jobPriority = 'Medium Priority' ) AS mediumpriority, COUNT(jobpriority) FILTER (WHERE date_trunc('month', start_event)::date = date_trunc('month', CURRENT_DATE)::date AND status = 'Open' AND jobPriority = 'Critical Priority' ) AS criticalpriority, COUNT(status) FILTER (WHERE date_trunc('month', start_event)::date = date_trunc('month', CURRENT_DATE)::date AND status = 'Complete' ) AS complete FROM jobDetails WHERE user_id = :user_id", {'user_id': userId}).fetchall()
report = convertTupleSQLtoDict(report)
return report
def getUserCompanyVaxRate(userId):
companyVaxRate = db.execute(
"SELECT userCompanyVatRate FROM usersDetails WHERE user_id = :user_id",
{
'user_id': userId
}).fetchall()
companyVaxRate = convertTupleSQLtoDict(companyVaxRate)
return companyVaxRate[0]["usercompanyvatrate"]
def userCompanyDetails(userId):
getUserCompanyDetails = db.execute(
"SELECT userCompanyName, userAddress1, userAddress2, userAddress3, userCity, userCountry, userZipCode, userCompanyRegistrationNumber, userPhoneNumber, userLandlineNumber, userEmail, userContactName FROM usersDetails WHERE user_id = :user_id",
{
'user_id': userId
}).fetchall()
getUserCompanyDetails = convertTupleSQLtoDict(getUserCompanyDetails)
return getUserCompanyDetails
def showUserDetails(userId):
userDetails = db.execute(
"SELECT username, to_char(register, 'YYYY-MM-DD HH24:MI:SS') register, userCompanyName, userAddress1, userAddress2, userAddress3, userCity, userCountry, userZipCode, userCompanyRegistrationNumber, userCompanyVatRate, userPhoneNumber, userLandlineNumber, userEmail, userContactName FROM users INNER JOIN usersDetails ON users.id = usersDetails.user_id WHERE users.id = :id AND usersDetails.user_id = :user_id",
{
'id': userId,
'user_id': userId
}).fetchall()
userDetails = convertTupleSQLtoDict(userDetails)
return userDetails
def getAllPm(userId):
allPm = db.execute(
"SELECT companyName, address1, address2, address3, phoneNumber, landlineNumber, email, contactName, city, zipCode, technician, title, jobDescription, quantity, jobPrice, to_char(start_event, 'YYYY-MM-DD HH24:MI') start_event, to_char(end_event, 'YYYY-MM-DD HH24:MI') end_event, labourDescription, labourHours, labourRate, labourTotalPrice, jobPriority, status FROM companyDetails INNER JOIN jobDetails ON companyDetails.id = jobDetails.companyDetails_id INNER JOIN joblabor ON jobDetails.id = joblabor.jobDetails_id WHERE companyDetails.user_id = :companyDetailsUser_id AND jobDetails.user_id = :jobDetailsUuser_id AND joblabor.user_id = :jobLabourUser_id ORDER BY companyDetails.id",
{
'companyDetailsUser_id': userId,
'jobDetailsUuser_id': userId,
'jobLabourUser_id': userId
}).fetchall()
allPm = convertTupleSQLtoDict(allPm)
return allPm
def index():
"""Show PM schedule of day/week/month"""
# User reached route via GET or via redirect
if request.method == "GET":
rows = db.execute("SELECT * FROM jobdetails WHERE user_id = :user_id",
{
"user_id": session["user_id"]
}).fetchall()
if rows:
rows = convertTupleSQLtoDict(rows)
return render_template("schedule.html", rows=rows)
def getLastInvoice(userId):
invoiceNumber = db.execute(
"SELECT invoiceNumber FROM invoice WHERE user_id = :user_id ORDER BY id DESC;",
{
'user_id': userId
}).fetchall()
invoiceNumber = convertTupleSQLtoDict(invoiceNumber)
if not invoiceNumber:
return None
return invoiceNumber[0]['invoicenumber']
def completePm(userId):
getCompletePm = db.execute(
"SELECT companyName, landlineNumber, contactName, technician, title, to_char(dateofissue, 'YYYY-MM-DD') dateofissue, to_char(duedate, 'YYYY-MM-DD') duedate, invoiceNumber FROM companyDetails INNER JOIN jobDetails ON companyDetails.id = jobDetails.companyDetails_id INNER JOIN joblabor ON jobDetails.id = joblabor.jobDetails_id INNER JOIN invoice ON jobLabor.id = invoice.jobLabor_id WHERE companyDetails.user_id = :companyDetailsUser_id AND jobDetails.user_id = :jobDetailsUuser_id AND joblabor.user_id = :jobLabourUser_id AND invoice.user_id = :invoiceUser_id AND status = 'Complete' ORDER BY invoice.id",
{
'companyDetailsUser_id': userId,
'jobDetailsUuser_id': userId,
'jobLabourUser_id': userId,
'invoiceUser_id': userId
}).fetchall()
getCompletePm = convertTupleSQLtoDict(getCompletePm)
return getCompletePm
def companyInvoices(userId):
getCompanyInvoicesDetails = db.execute(
"SELECT companyName, address1, address2, address3, city, zipCode, title, quantity, jobPrice, labourDescription, labourHours, labourRate, labourTotalPrice, invoiceNumber, to_char(dateOfIssue, 'YYYY-MM-DD') dateOfIssue, to_char(dueDate, 'YYYY-MM-DD') dueDate, subTotal, taxRate, tax, totalPrice FROM companyDetails INNER JOIN jobDetails ON companyDetails.id = jobDetails.companyDetails_id INNER JOIN jobLabor ON jobDetails.id = jobLabor.jobDetails_id INNER JOIN invoice ON jobLabor.id = invoice.jobLabor_id WHERE companyDetails.user_id = :companyDetailsUser_id AND jobDetails.user_id = :jobDetailsUuser_id AND joblabor.user_id = :jobLabourUser_id AND invoice.user_id = :invoiceUser_id AND status = 'Complete' ORDER BY companyDetails.id",
{
'companyDetailsUser_id': userId,
'jobDetailsUuser_id': userId,
'jobLabourUser_id': userId,
'invoiceUser_id': userId
}).fetchall()
getCompanyInvoicesDetails = convertTupleSQLtoDict(
getCompanyInvoicesDetails)
if not getCompanyInvoicesDetails:
return None
# create a nested dictiionary with all data
invoices = allCompanyInvoices(getCompanyInvoicesDetails)
return invoices
def createpm():
"""Show Create PM Form"""
if request.method == "GET":
# get the public holiday for user registration country if country is supported in the API call
getPublicHoliday = pm_scheduler.getpublicHoliday(session["user_id"])
if not getPublicHoliday:
holidayList = []
return render_template("createpm.html",
getPublicHoliday=holidayList)
return render_template("createpm.html",
getPublicHoliday=getPublicHoliday)
# if method is "POST"
else:
formData = list(request.form.items())
# insert new PM in DB
jobLababor_id = pm_scheduler.insertNewPm(formData, session["user_id"])
# if succesufull insert PM
if jobLababor_id:
# check if user create PM with status complete
checkStatus = request.form.get('status').strip()
if not checkStatus == 'Complete':
# Query database jobDetails table to display all PM on scheduler
rows = db.execute(
"SELECT * FROM jobdetails WHERE user_id = :user_id", {
"user_id": session["user_id"]
}).fetchall()
# Flash message if successfully created a new PM
flash('Your PM was successfully created!',
category='alert-success')
# rows = [dict(row) for row in rows]
rows = convertTupleSQLtoDict(rows)
return render_template("schedule.html", rows=rows)
# create a dict for pmDetails
pmDetails = {'jobPrice': None, 'labourTotalPrice': None}
pmDetails['jobPrice'] = request.form.get('jobPrice').strip()
labourHours = request.form.get('labourHours').strip()
labourRate = request.form.get('labourHours').strip()
pmDetails['labourTotalPrice'] = float(str(labourHours)) * float(
str(labourRate))
# create invoice details and return invoice id if succesufull
getInvoice_id = pm_records.createInvoice(jobLababor_id, pmDetails,
session["user_id"])
if not getInvoice_id:
return apology(
"Oops! Something went wrong and we couldn't generate invoice for your PM."
)
# Get all PM that are complete
getCompletePm = pm_records.completePm(session["user_id"])
# get user company details for pdf make
userCompanyDetails = pm_invoice.userCompanyDetails(
session["user_id"])
# make a structure from each company to access their own invoice
invoices = pm_invoice.companyInvoices(session["user_id"])
# Flash message if successfully updated PM
flash('Your new invoice was succesfully created.',
category='alert-success')
return render_template("records.html",
records=getCompletePm,
userCompanyDetails=userCompanyDetails,
invoices=invoices)
else:
return apology(
"Oops! Something went wrong and we couldn't create your PM. Try again!"
)