from docx import Document from docx.enum.text import WD_ALIGN_PARAGRAPH from docx.enum.table import WD_TABLE_ALIGNMENT from docx.enum.style import WD_STYLE_TYPE from docx.shared import Inches, Pt, RGBColor from docx.oxml import OxmlElement from docx.oxml.ns import qn from docx2pdf import convert import time import os from datetime import datetime, timedelta import mysql.connector import sys import json # def getCompany(): # cursor = connection.cursor() # colname = 'maincompany' # select_query = """ # SELECT type, iname, colname, colvalue # FROM settings # WHERE type = %s # """ # cursor.execute(select_query, (colname,)) # result = cursor.fetchall() # if result: # company = { # 'name': result[0][3], # 'logobig':result[1][3], # 'logosmall':result[2][3], # } # cursor.close() # return company # cursor.close() # return result def getPeriod(date): periodStart = date - timedelta(days=date.weekday()) periodEnd = periodStart + timedelta(days=6) return periodStart, periodEnd def getLoads(pStart, pEnd): cursor = connection.cursor() select_query = """ SELECT id, load_number, pickup_date, price, balance, pickup_city, pickup_state, delivery_city, delivery_state, driver1, owner_id, status, distance FROM loads WHERE pickup_date >= %s AND (delivery_date <= %s OR delivery_realdate <= %s) """ cursor.execute(select_query, (pStart, pEnd, pEnd)) result = cursor.fetchall() if result: loads = [] driversList = [] ownerList = [] for res in result: load = { 'id':res[0], 'load_number':res[1], 'pickupdate':res[2].strftime('%m/%d/%y'), 'price':round(float(res[4]), 2) if res[4] else round(float(res[3]), 2), 'trip':str(res[5])+', '+str(res[6])+' - '+str(res[7])+' '+str(res[8]), 'driver_id': res[9], 'owner_id': res[10], 'status':res[11], 'mileage':res[12], } if res[9] not in driversList and res[9]: driversList.append(res[9]) if res[10] not in ownerList and res[10]: ownerList.append(res[10]) loads.append(load) cursor.close() return loads, driversList, ownerList else: cursor.close() return 0, 0, 0 def getDriverInfo(user_id): cursor = connection.cursor() select_query = """ SELECT users.id, users.firstname, users.lastname, users.email, users.salary, drivers.fuelcard, drivers.owner_id, drivers.truck_id FROM users LEFT JOIN drivers ON drivers.user_id = users.id WHERE users.id = %s """ cursor.execute(select_query, (user_id,)) result = cursor.fetchone() if result: user = { 'id': result[0], 'fullname': str(result[1])+' '+str(result[2]), 'email': str(result[3]), 'salary': round(float(result[4]),2), 'fuelcard': str(result[5]), 'owner': result[6], 'truckId': result[7], } cursor.close return user cursor.close() return None def getFuel(card): totalFuel = 0 cursor = connection.cursor() select_query = """ SELECT d_transdate,d_card,d_location,d_unitprice,d_item,d_quantity,d_amount FROM fuels WHERE d_card = %s """ cursor.execute(select_query, (card,)) result = cursor.fetchall() if result: fuels = [] for res in result: fuel = { 'trans_date':res[0].strftime('%m/%d/%y'), 'card':res[1], 'location':res[2], 'item':res[4], 'unitprice':round(float(res[3]),2), 'quantity':round(float(res[5]),2), 'amount':round(float(res[6]),2), } totalFuel = totalFuel + round(float(res[6]), 2) fuels.append(fuel) jsonFuel = json.dumps(fuels) cursor.close() return jsonFuel, round(float(totalFuel),2) else: cursor.close() return 0, 0 def getMainUsers(): users_dict = {} cursor = connection.cursor() # select_query = "SELECT id, firstname, lastname FROM users WHERE role = %s OR role = %s" select_query = "SELECT id, firstname, lastname FROM users" # cursor.execute(select_query, ('admin', 'accounting')) cursor.execute(select_query) result = cursor.fetchall() for res in result: user = { 'id': res[0], 'fullname': f'{res[1]} {res[2]}', } users_dict[res[0]] = user cursor.close() return users_dict def getIssuedFundUser(userlist,user_id): return userlist[user_id]['fullname'] def getPreviousBalance(user_id, pStart): cursor = connection.cursor() select_query = """SELECT balance FROM statements WHERE driver_id = %s AND period_start < %s ORDER BY id DESC LIMIT 1""" cursor.execute(select_query, (user_id,pStart,)) result = cursor.fetchone() balance = result[0] if result and result[0] is not None else None cursor.close() return balance def getFunds(user_id, pStart, pEnd, usersList): previousBalance = getPreviousBalance(user_id,pStart.strftime('%Y-%m-%d')) totalCash = 0 funds = [] cursor = connection.cursor() select_query = """ SELECT id,fund_date,reason,amount,is_charge,issuedby_id FROM funds WHERE driver_id = %s AND (fund_date >= %s and fund_date <= %s) AND active = 1 """ cursor.execute(select_query, (user_id,pStart.strftime('%Y-%m-%d'),pEnd.strftime('%Y-%m-%d'),)) result = cursor.fetchall() if result: for res in result: fund = { 'fundid': int(res[0]), 'date': res[1].strftime('%m/%d/%y'), 'reason': res[2], 'amount': round(float(res[3]),2), 'is_charge': int(res[4]), 'issuedid': int(res[5]), 'issuedname': getIssuedFundUser(usersList,int(res[5])), } totalCash = totalCash + round(float(res[3]),2) funds.append(fund) if previousBalance: fund = { 'fundid': 0, 'date': '-', 'reason': 'Negative balance from last statement', 'amount': previousBalance, 'is_charge': 1, 'issuedid': 0, 'issuedname': 'AI System', } funds.append(fund) totalCash = totalCash + previousBalance jsonFund = json.dumps(funds) cursor.close() return jsonFund, round(float(totalCash),2) else: cursor.close() if previousBalance: fund = { 'fundid': 0, 'date': '-', 'reason': 'Negative balance from last statement', 'amount': previousBalance, 'is_charge': 1, 'issuedid': 0, 'issuedname': 'AI System', } funds.append(fund) jsonFund = json.dumps(funds) return jsonFund, round(float(previousBalance),2) else: return 0, 0 def getTruckNumber(user_id): cursor = connection.cursor() select_query = """SELECT trucks.number FROM trucks WHERE id = %s""" cursor.execute(select_query, (user_id,)) result = cursor.fetchone()[0] cursor.close() if result: return result return '-' def getCountRecords(): cursor = connection.cursor() count_query = "SELECT COUNT(*) FROM statements" cursor.execute(count_query) result = cursor.fetchone()[0] cursor.close() return result def formatTableCells(table, tablerow, tablecells): for c in range(tablecells): propertyXml = table.rows[tablerow].cells[c]._tc.get_or_add_tcPr() shade_obj = OxmlElement('w:shd') shade_obj.set(qn('w:fill'), 'BFBFBF') propertyXml.append(shade_obj) def statementDriverPDF(driverInfo, driverFunds, totalFund, driverLoads, totalGross, truck, periodStart, periodEnd, company, parentDir, nextRecordId, income): doc = Document() doc.styles['Normal'].font.name = "Tahoma" doc.styles['Normal'].font.size = Pt(8.25) # header tableHeader = doc.add_table(rows=1, cols=2) pR = tableHeader.rows[0].cells[0].paragraphs[0] pR.add_run().add_picture(str(parentDir) + '\\public\\statementsfile\\temp\\biglogo.jpg') pL = tableHeader.rows[0].cells[1].paragraphs[0] pL.add_run('\n\nStatement #'+str(nextRecordId)+'\nPeriod:'+periodStart.strftime('%m/%d/%Y')+' - '+periodEnd.strftime('%m/%d/%Y')+'\nDriver:'+str(truck)+' '+str(driverInfo['fullname'])+'\nEmail:'+str(driverInfo['email'])+'\nRate:'+(str(round(driverInfo['salary'],0))+'%' if driverInfo['salary']>0 else '$'+str(round(driverInfo['salary'],2))+'/mile')) pL.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT # Trips table heading1 = doc.add_paragraph() heading1.add_run('Trips:').bold = True tripTable = doc.add_table(rows=1, cols=6) tripRow11 = tripTable.rows[0].cells[0].paragraphs[0] tripRow11.add_run('Date').bold = True tripRow11.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow12 = tripTable.rows[0].cells[1].paragraphs[0] tripRow12.add_run('Load #').bold = True tripRow12.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow13 = tripTable.rows[0].cells[2].paragraphs[0] tripRow13.add_run('Trip').bold = True tripRow13.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow14 = tripTable.rows[0].cells[3].paragraphs[0] tripRow14.add_run('Mileage').bold = True tripRow14.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow15 = tripTable.rows[0].cells[4].paragraphs[0] tripRow15.add_run('Freight Amt.').bold = True tripRow15.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow16 = tripTable.rows[0].cells[5].paragraphs[0] tripRow16.add_run('Amount').bold = True tripRow16.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER for r in driverLoads: row = tripTable.add_row() row.cells[0].text = str(r['pickupdate']) row.cells[1].text = str(r['load_number']) row.cells[2].text = str(r['trip']) row.cells[3].text = (str(r['mileage']) if r['mileage'] != None else ' ') row.cells[4].text = '${:,.2f}'.format(r['price']) row.cells[5].text = '${:,.2f}'.format(r['price']*(driverInfo['salary']/100)) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].paragraph_format.alignment = 1 row = tripTable.add_row() row.cells[3].paragraphs[0].add_run('Total:').bold = True row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].add_run('${:,.2f}'.format(totalGross)).bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[5].paragraphs[0].add_run('${:,.2f}'.format(totalGross*(driverInfo['salary']/100))) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(34,139,34) row.cells[5].paragraphs[0].paragraph_format.alignment = 1 run1 = row.cells[5].paragraphs[0].add_run() formatTableCells(tripTable, 0, 6) tripTable.style='Table Grid' tripTable.allow_autofit = False tripTable.cell(0,0).width = Inches(1.0) tripTable.cell(0,1).width = Inches(1.0) tripTable.cell(0,2).width = Inches(5.0) tripTable.cell(0,3).width = Inches(1.0) tripTable.cell(0,4).width = Inches(1.0) tripTable.cell(0,5).width = Inches(1.0) doc.add_paragraph() del row # Cash if driverFunds != 0: heading3 = doc.add_paragraph() heading3.add_run('Advances and Deductions:').bold = True CashTable = doc.add_table(0, 3) row = CashTable.add_row() row.cells[0].paragraphs[0].add_run('Description:').bold = True row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].add_run('Date').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].add_run('Amount').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 1 for dc in json.loads(driverFunds): row = CashTable.add_row() row.cells[0].text = str(dc['reason']) row.cells[1].text = str(dc['date']) row.cells[2].text = str('${:,.2f}'.format(dc['amount'])) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row = CashTable.add_row() row.cells[1].paragraphs[0].add_run('Total:').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[2].paragraphs[0].add_run('${:,.2f}'.format(totalFund)) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(255, 0, 0) row.cells[2].paragraphs[0].paragraph_format.alignment = 1 formatTableCells(CashTable, 0, 3) CashTable.style = 'Table Grid' CashTable.cell(0, 0).width = Inches(8.0) CashTable.cell(0, 1).width = Inches(1.0) CashTable.cell(0, 2).width = Inches(1.0) doc.add_paragraph() del row # Summary checkAmount = doc.add_table(1, 2) col1 = checkAmount.rows[0].cells[0].paragraphs[0].add_run('Check Amount') col1.bold = True checkAmount.rows[0].cells[0].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER col2 = checkAmount.rows[0].cells[1].paragraphs[0].add_run(str('${:,.2f}'.format(income))) col2.bold = True checkAmount.rows[0].cells[1].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER font = col2.font if income > 0: font.color.rgb = RGBColor(34,139,34) else: font.color.rgb = RGBColor(255,0,0) checkAmount.cell(0, 0).width = Inches(1.0) checkAmount.cell(0, 1).width = Inches(1.0) checkAmount.style='Table Grid' checkAmount.alignment = WD_TABLE_ALIGNMENT.RIGHT filename = str(truck) +'_'+ str(driverInfo['fullname']) + "_driver_" + str(periodStart.strftime('%Y-%m-%d')) + ".pdf" filename = filename.replace(' ', '_') if os.path.isdir(parentDir + "\\public\\statementsfile\\" + str(driverInfo['id'])) == False: os.mkdir(parentDir + '\\public\\statementsfile\\' + str(driverInfo['id'])) doc.save(parentDir + '\\public\\statementsfile\\temp\\tempstatement.docx') os.chmod(parentDir + '\\public\\statementsfile\\temp\\tempstatement.docx', 0o755) time.sleep(1) convert(parentDir + "\\public\\statementsfile\\temp\\tempstatement.docx", parentDir + "\\public\\statementsfile\\" + str(driverInfo['id']) + "\\" + str(filename)) time.sleep(1) os.remove(parentDir + "\\public\\statementsfile\\temp\\tempstatement.docx") if os.path.isfile(parentDir + "\\public\\statementsfile\\" + str(driverInfo['id']) + "\\" +str(filename)): return '/statementsfile/'+str(driverInfo['id'])+'/'+str(filename) else: return None def statementOwnerDriverPDF(driverInfo, driverFuel, totalFuel, driverFunds, totalFund, driverLoads, totalGross, truck, periodStart, periodEnd, company, parentDir, nextRecordId, income): doc = Document() doc.styles['Normal'].font.name = "Tahoma" doc.styles['Normal'].font.size = Pt(8.25) # header tableHeader = doc.add_table(rows=1, cols=2) pR = tableHeader.rows[0].cells[0].paragraphs[0] pR.add_run().add_picture(str(parentDir) + '\\public\\statementsfile\\temp\\biglogo.jpg') pL = tableHeader.rows[0].cells[1].paragraphs[0] pL.add_run('\n\nStatement #' + str(nextRecordId) + '\nPeriod:' + periodStart.strftime('%m/%d/%Y') + ' - ' + periodEnd.strftime('%m/%d/%Y') + '\nOwner Operator:' + str(truck) + ' ' + str(driverInfo['fullname']) + '\nEmail:' + str(driverInfo['email']) + '\nRate:' + (str(round(driverInfo['salary'], 0)) + '%' if driverInfo['salary'] > 0 else '$' + str(round(driverInfo['salary'], 2)) + '/mile')) pL.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT # Trips table heading1 = doc.add_paragraph() heading1.add_run('Trips:').bold = True tripTable = doc.add_table(rows=1, cols=6) tripRow11 = tripTable.rows[0].cells[0].paragraphs[0] tripRow11.add_run('Date').bold = True tripRow11.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow12 = tripTable.rows[0].cells[1].paragraphs[0] tripRow12.add_run('Load #').bold = True tripRow12.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow13 = tripTable.rows[0].cells[2].paragraphs[0] tripRow13.add_run('Trip').bold = True tripRow13.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow14 = tripTable.rows[0].cells[3].paragraphs[0] tripRow14.add_run('Mileage').bold = True tripRow14.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow15 = tripTable.rows[0].cells[4].paragraphs[0] tripRow15.add_run('Freight Amt.').bold = True tripRow15.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER tripRow16 = tripTable.rows[0].cells[5].paragraphs[0] tripRow16.add_run('Amount').bold = True tripRow16.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER for r in driverLoads: row = tripTable.add_row() row.cells[0].text = str(r['pickupdate']) row.cells[1].text = str(r['load_number']) row.cells[2].text = str(r['trip']) row.cells[3].text = (str(r['mileage']) if r['mileage'] != None else ' ') row.cells[4].text = '${:,.2f}'.format(r['price']) row.cells[5].text = '${:,.2f}'.format(r['price'] * (driverInfo['salary'] / 100)) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].paragraph_format.alignment = 1 row = tripTable.add_row() row.cells[3].paragraphs[0].add_run('Total:').bold = True row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].add_run('${:,.2f}'.format(totalGross)).bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[5].paragraphs[0].add_run('${:,.2f}'.format(totalGross * (driverInfo['salary'] / 100))) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(34, 139, 34) row.cells[5].paragraphs[0].paragraph_format.alignment = 1 run1 = row.cells[5].paragraphs[0].add_run() formatTableCells(tripTable, 0, 6) tripTable.style = 'Table Grid' tripTable.allow_autofit = False tripTable.cell(0, 0).width = Inches(1.0) tripTable.cell(0, 1).width = Inches(1.0) tripTable.cell(0, 2).width = Inches(5.0) tripTable.cell(0, 3).width = Inches(1.0) tripTable.cell(0, 4).width = Inches(1.0) tripTable.cell(0, 5).width = Inches(1.0) doc.add_paragraph() del row # Fuel if driverFuel: heading2 = doc.add_paragraph() heading2.add_run('Fuels:').bold = True FuelTable = doc.add_table(0,6) row = FuelTable.add_row() row.cells[0].paragraphs[0].add_run('Date:').bold = True row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].add_run('Location').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].add_run('Item').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].add_run('$/Gal.').bold = True row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].add_run('Quantity').bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].add_run('Amount').bold = True row.cells[5].paragraphs[0].paragraph_format.alignment = 1 for df in json.loads(driverFuel): row = FuelTable.add_row() row.cells[0].text = str(df['trans_date']) row.cells[1].text = str(df['location']) row.cells[2].text = str(df['item']) row.cells[3].text = '${:,.2f}'.format(df['unitprice']) row.cells[4].text = '${:,.2f}'.format(df['quantity']) row.cells[5].text = '${:,.2f}'.format(df['amount']) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].paragraph_format.alignment = 1 row = FuelTable.add_row() row.cells[4].paragraphs[0].add_run('Total:').bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[5].paragraphs[0].add_run('${:,.2f}'.format(totalFuel)) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(255,0,0) row.cells[5].paragraphs[0].paragraph_format.alignment = 1 formatTableCells(FuelTable, 0, 6) FuelTable.style = 'Table Grid' FuelTable.cell(0, 0).width = Inches(1.0) FuelTable.cell(0, 1).width = Inches(5.0) FuelTable.cell(0, 2).width = Inches(1.0) FuelTable.cell(0, 3).width = Inches(1.0) FuelTable.cell(0, 4).width = Inches(1.0) FuelTable.cell(0, 5).width = Inches(1.0) doc.add_paragraph() del row # Cash if driverFunds != 0: heading3 = doc.add_paragraph() heading3.add_run('Advances and Deductions:').bold = True CashTable = doc.add_table(0, 3) row = CashTable.add_row() row.cells[0].paragraphs[0].add_run('Description:').bold = True row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].add_run('Date').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].add_run('Amount').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 1 for dc in json.loads(driverFunds): row = CashTable.add_row() row.cells[0].text = str(dc['reason']) row.cells[1].text = str(dc['date']) row.cells[2].text = str('${:,.2f}'.format(dc['amount'])) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row = CashTable.add_row() row.cells[1].paragraphs[0].add_run('Total:').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[2].paragraphs[0].add_run('${:,.2f}'.format(totalFund)) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(255, 0, 0) row.cells[2].paragraphs[0].paragraph_format.alignment = 1 formatTableCells(CashTable, 0, 3) CashTable.style = 'Table Grid' CashTable.cell(0, 0).width = Inches(8.0) CashTable.cell(0, 1).width = Inches(1.0) CashTable.cell(0, 2).width = Inches(1.0) doc.add_paragraph() del row # Summary checkAmount = doc.add_table(1, 2) col1 = checkAmount.rows[0].cells[0].paragraphs[0].add_run('Check Amount') col1.bold = True checkAmount.rows[0].cells[0].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER col2 = checkAmount.rows[0].cells[1].paragraphs[0].add_run(str('${:,.2f}'.format(float(income)))) col2.bold = True checkAmount.rows[0].cells[1].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER font = col2.font if income > 0: font.color.rgb = RGBColor(34,139,34) else: font.color.rgb = RGBColor(255,0,0) checkAmount.cell(0, 0).width = Inches(1.0) checkAmount.cell(0, 1).width = Inches(1.0) checkAmount.style='Table Grid' checkAmount.alignment = WD_TABLE_ALIGNMENT.RIGHT filename = str(truck) + '_' + str(driverInfo['fullname']) + "_owner_operator_" + str( periodStart.strftime('%Y-%m-%d')) + ".pdf" filename = filename.replace(' ', '_') if os.path.isdir(parentDir + "\\public\\statementsfile\\" + str(driverInfo['id'])) == False: os.mkdir(parentDir + '\\public\\statementsfile\\' + str(driverInfo['id'])) doc.save(parentDir + '\\public\\statementsfile\\temp\\tempstatement.docx') os.chmod(parentDir + '\\public\\statementsfile\\temp\\tempstatement.docx', 0o755) time.sleep(1) convert(parentDir + "\\public\\statementsfile\\temp\\tempstatement.docx", parentDir + "\\public\\statementsfile\\" + str(driverInfo['id']) + "\\" + str(filename)) time.sleep(1) os.remove(parentDir + "\\public\\statementsfile\\temp\\tempstatement.docx") if os.path.isfile(parentDir + "\\public\\statementsfile\\" + str(driverInfo['id']) + "\\" + str(filename)): return '/statementsfile/' + str(driverInfo['id']) + '/' + str(filename) else: return None def statementOwnerPDF(ownerInfo,loadList,totalGross,fundList,totalFund, fuelList, totalFuel, pStart,pEnd,nextRecordId,income): doc = Document() doc.styles['Normal'].font.name = "Tahoma" doc.styles['Normal'].font.size = Pt(8.25) # header tableHeader = doc.add_table(rows=1, cols=2) pR = tableHeader.rows[0].cells[0].paragraphs[0] pR.add_run().add_picture(str(parentDir) + '\\public\\statementsfile\\temp\\biglogo.jpg') pL = tableHeader.rows[0].cells[1].paragraphs[0] pL.add_run('\n\nStatement #' + str(nextRecordId) + '\nPeriod:' + periodStart.strftime('%m/%d/%Y') + ' - ' + periodEnd.strftime('%m/%d/%Y') + '\nOwner:' + str(ownerInfo['fullname']) + '\nEmail:' + str(ownerInfo['email']) + '\nRate:' + (str(round(ownerInfo['salary'], 0)) + '%' if ownerInfo['salary'] > 0 else '$' + str(round(ownerInfo['salary'], 2)) + '/mile')) pL.paragraph_format.alignment = WD_ALIGN_PARAGRAPH.RIGHT if loadList: # Loads heading1 = doc.add_paragraph() heading1.add_run('Trips:').bold = True TripTable = doc.add_table(0,7) row = TripTable.add_row() row.cells[0].paragraphs[0].add_run('Date').bold = True row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].add_run('Load #').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].add_run('Driver').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].add_run('Trip').bold = True row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].add_run('Mileage').bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].add_run('Freight Amt.').bold = True row.cells[5].paragraphs[0].paragraph_format.alignment = 1 row.cells[6].paragraphs[0].add_run('Amount').bold = True row.cells[6].paragraphs[0].paragraph_format.alignment = 1 for l in loadList: row = TripTable.add_row() row.cells[0].text = str(l['pickupdate']) row.cells[1].text = str(l['load_number']) row.cells[2].text = str(l['driver']) row.cells[3].text = str(l['trip']) row.cells[4].text = (str(l['mileage']) if l['mileage'] != None else ' ') row.cells[5].text = str('${:,.2f}'.format(l['price'])) row.cells[6].text = str('${:,.2f}'.format(l['price']*ownerInfo['salary']/100)) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].paragraph_format.alignment = 1 row.cells[6].paragraphs[0].paragraph_format.alignment = 1 row = TripTable.add_row() row.cells[4].paragraphs[0].add_run('Total:').bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].add_run('${:,.2f}'.format(totalGross)) row.cells[5].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[6].paragraphs[0].add_run('${:,.2f}'.format(totalGross*ownerInfo['salary']/100)) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(34,139,34) row.cells[6].paragraphs[0].paragraph_format.alignment = 1 formatTableCells(TripTable, 0, 7) TripTable.style = 'Table Grid' TripTable.cell(0, 0).width = Inches(1.0) TripTable.cell(0, 1).width = Inches(1.0) TripTable.cell(0, 2).width = Inches(2.0) TripTable.cell(0, 3).width = Inches(5.0) TripTable.cell(0, 4).width = Inches(1.0) TripTable.cell(0, 5).width = Inches(1.0) TripTable.cell(0, 6).width = Inches(1.0) doc.add_paragraph() # FUEL if fuelList: heading2 = doc.add_paragraph() heading2.add_run('Fuels:').bold = True FuelTable = doc.add_table(0, 6) row = FuelTable.add_row() row.cells[0].paragraphs[0].add_run('Date').bold = True row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].add_run('Driver').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].add_run('Location').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].add_run('Item').bold = True row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].add_run('Quantity').bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].add_run('Amount').bold = True row.cells[5].paragraphs[0].paragraph_format.alignment = 1 for fuel in fuelList: row = FuelTable.add_row() row.cells[0].text = str(fuel['trans_date']) row.cells[1].text = str(fuel['driver']) row.cells[2].text = str(fuel['location']) row.cells[3].text = str(fuel['item']) row.cells[4].text = str('{:,.2f}'.format(fuel['quantity'])) row.cells[5].text = str('${:,.2f}'.format(fuel['amount'])) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].paragraph_format.alignment = 1 row = FuelTable.add_row() row.cells[4].paragraphs[0].add_run('Total:').bold = True row.cells[4].paragraphs[0].paragraph_format.alignment = 1 coloredTotal = row.cells[5].paragraphs[0].add_run('${:,.2f}'.format(totalFuel)) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(255, 0, 0) row.cells[4].paragraphs[0].paragraph_format.alignment = 1 row.cells[5].paragraphs[0].paragraph_format.alignment = 1 formatTableCells(FuelTable, 0, 6) FuelTable.style = 'Table Grid' FuelTable.cell(0, 0).width = Inches(1.0) FuelTable.cell(0, 1).width = Inches(3.0) FuelTable.cell(0, 2).width = Inches(5.0) FuelTable.cell(0, 3).width = Inches(1.0) FuelTable.cell(0, 4).width = Inches(1.0) FuelTable.cell(0, 5).width = Inches(1.0) doc.add_paragraph() # Fund if fundList: heading3 = doc.add_paragraph() heading3.add_run('Advances and Deductions:').bold = True CashTable = doc.add_table(0, 4) row = CashTable.add_row() row.cells[0].paragraphs[0].add_run('Date').bold = True row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].add_run('Driver').bold = True row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].add_run('Description').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].add_run('Amount').bold = True row.cells[3].paragraphs[0].paragraph_format.alignment = 1 for fnd in fundList: row = CashTable.add_row() row.cells[0].text = str(fnd['date']) row.cells[1].text = str(fnd['driver']) row.cells[2].text = str(fnd['reason']) row.cells[3].text = str('${:,.2f}'.format(fnd['amount'])) row.cells[0].paragraphs[0].paragraph_format.alignment = 1 row.cells[1].paragraphs[0].paragraph_format.alignment = 1 row.cells[2].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row = CashTable.add_row() row.cells[2].paragraphs[0].add_run('Total:').bold = True row.cells[2].paragraphs[0].paragraph_format.alignment = 2 coloredTotal = row.cells[3].paragraphs[0].add_run('${:,.2f}'.format(totalFund)) coloredTotal.bold = True font = coloredTotal.font font.color.rgb = RGBColor(255, 0, 0) row.cells[3].paragraphs[0].paragraph_format.alignment = 1 row.cells[3].paragraphs[0].paragraph_format.alignment = 1 formatTableCells(CashTable, 0, 4) CashTable.style = 'Table Grid' CashTable.cell(0, 0).width = Inches(1.0) CashTable.cell(0, 1).width = Inches(2.0) CashTable.cell(0, 2).width = Inches(5.0) CashTable.cell(0, 3).width = Inches(1.0) doc.add_paragraph() # Summary checkAmount = doc.add_table(1, 2) col1 = checkAmount.rows[0].cells[0].paragraphs[0].add_run('Check Amount').bold = True checkAmount.rows[0].cells[0].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER col2 = checkAmount.rows[0].cells[1].paragraphs[0].add_run(str('${:,.2f}'.format(income))) col2.bold = True checkAmount.rows[0].cells[1].paragraphs[0].paragraph_format.alignment = WD_ALIGN_PARAGRAPH.CENTER font = col2.font if income > 0: font.color.rgb = RGBColor(34, 139, 34) else: font.color.rgb = RGBColor(255, 0, 0) checkAmount.cell(0, 0).width = Inches(1.0) checkAmount.cell(0, 1).width = Inches(1.0) checkAmount.style = 'Table Grid' checkAmount.alignment = WD_TABLE_ALIGNMENT.RIGHT filename = str(ownerInfo['fullname']) + "_owner_" + str(pStart.strftime('%Y-%m-%d')) + ".pdf" filename = filename.replace(' ', '_') if os.path.isdir(parentDir + "\\public\\statementsfile\\" + str(ownerInfo['id'])) == False: os.mkdir(parentDir + '\\public\\statementsfile\\' + str(ownerInfo['id'])) doc.save(parentDir + '\\public\\statementsfile\\temp\\tempstatement.docx') os.chmod(parentDir + '\\public\\statementsfile\\temp\\tempstatement.docx', 0o755) time.sleep(1) convert(parentDir + "\\public\\statementsfile\\temp\\tempstatement.docx", parentDir + "\\public\\statementsfile\\" + str(ownerInfo['id']) + "\\" + str(filename)) time.sleep(1) os.remove(parentDir + "\\public\\statementsfile\\temp\\tempstatement.docx") if os.path.isfile(parentDir + "\\public\\statementsfile\\" + str(ownerInfo['id']) + "\\" + str(filename)): return '/statementsfile/' + str(ownerInfo['id']) + '/' + str(filename) else: return None def checkBeforeData(driver_id,pStart,pEnd): cursor = connection.cursor() select_query = "SELECT id, period_start, period_end, driver_id FROM statements WHERE driver_id = %s AND period_start = %s AND period_end = %s" cursor.execute(select_query, (driver_id, pStart, pEnd,)) result = cursor.fetchone() cursor.close() if result: return result[0] return 0 def ownerDriversData(driver_id,pStart,pEnd): driversName = [] loadList = [] cashList = [] fuelList = [] cursor = connection.cursor() select_query = "SELECT fullname, loadlist, cashlist, fuellist FROM statements WHERE driver_id = %s AND period_start = %s AND period_end = %s" cursor.execute(select_query, (driver_id, pStart, pEnd,)) result = cursor.fetchone() cursor.close() if result: driversName = 0 if result[0] is None else result[0] loadList = 0 if result[1] is None else result[1] cashList = 0 if result[2] is None else result[2] fuelList = 0 if result[3] is None else result[3] return driversName, loadList, cashList, fuelList def insertData(driverInfo,driverFuel,totalFuel,driverFunds,totalFund,driverLoads,totalGross,truck,pStart,pEnd,statementfile,isOwner,balance,income): cursor = connection.cursor() attr = { 'period_start':pStart.strftime('%Y-%m-%d'), 'period_end':pEnd.strftime('%Y-%m-%d'), 'driver_id':driverInfo['id'], 'fullname': str(driverInfo['fullname']) if isOwner == 1 else str(truck) + ' ' + str(driverInfo['fullname']), 'loadlist':driverLoads if driverLoads else None, 'cashlist':driverFunds if driverFunds else None, 'fuellist':driverFuel if driverFuel else None, 'gross':totalGross if totalGross != 0 else None, 'cash':totalFund if totalFund != 0 else None, 'fuel':totalFuel if totalFuel != 0 else None, 'income': income, 'statementfile':statementfile, 'balance': balance, 'email':driverInfo['email'], 'isowner':1 if isOwner else 0, } sql_query = 'INSERT INTO statements (period_start, period_end, driver_id, fullname, loadlist, cashlist, fuellist, gross, cash, fuel, income, statementfile, balance, email, isowner, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())' data_to_insert = tuple(attr.values()) cursor.execute(sql_query, data_to_insert) connection.commit() def updateData(driverInfo,driverFuel,totalFuel,driverFunds,totalFund,driverLoads,totalGross,truck,pStart,pEnd,statementfile,isOwner,balance,income): cursor = connection.cursor() attr = { 'period_start':pStart.strftime('%Y-%m-%d'), 'period_end':pEnd.strftime('%Y-%m-%d'), 'driver_id':driverInfo['id'], 'fullname': str(driverInfo['fullname']) if isOwner == 1 else str(truck) + ' ' + str(driverInfo['fullname']), 'loadlist':driverLoads if driverLoads else None, 'cashlist':driverFunds if driverFunds else None, 'fuellist':driverFuel if driverFuel else None, 'gross':totalGross if totalGross else None, 'cash':totalFund if totalFund else None, 'fuel':totalFuel if totalFuel else None, 'income': income, 'statementfile':statementfile, 'email':driverInfo['email'], 'isowner': 1 if isOwner else 0, 'balance': balance, 'id':driverInfo['id'], 'dateUpdate': pStart.strftime('%Y-%m-%d'), } sql_query = ''' UPDATE statements SET period_start = %s, period_end = %s, driver_id = %s, fullname = %s, loadlist = %s, cashlist = %s, fuellist = %s, gross = %s, cash = %s, fuel = %s, income = %s, statementfile = %s, email = %s, isowner = %s, balance = %s, updated_at = NOW() WHERE driver_id = %s AND period_start = %s ''' data_to_update = tuple(attr.values()) cursor.execute(sql_query, data_to_update) connection.commit() def writingLog(progressbar, message, pid, parentDir): pylog = open(parentDir+'\\public\\pylogs\\statements.log', 'w') pylog.write(str(progressbar)+'|'+str(message)+'..|'+str(pid)) pylog.close() # 0. Config db_config = { "host": "localhost", "user": "root", "password": "", "database": "truckerbill" } connection = mysql.connector.connect(**db_config) currentPID = os.getpid() parentDir = os.path.dirname(os.getcwd()) progressbar = 10 # Get the current date periodStart, periodEnd = getPeriod(datetime.now()) # 1. Get loads loads, driverIds, ownerIds = getLoads(periodStart.strftime('%Y-%m-%d'), periodEnd.strftime('%Y-%m-%d')) lastRecordId = getCountRecords() nextRecordId = int(lastRecordId)+1 usersList = getMainUsers() # company = getCompany() # if not company: # writingLog(0,'Company does not set up.', currentPID, parentDir) # sys.exit() # 2. Driver Statements # driverIds = None if driverIds: company='' step = int(90/len(driverIds)) if ownerIds: step = int(90 / (len(driverIds)+len(ownerIds))) for dId in driverIds: progressbar = progressbar + step # driver driverInfo = getDriverInfo(dId) if driverInfo: writingLog(progressbar, 'Driver [' + str(driverInfo['id']) + ']: ' + str(driverInfo['fullname']), currentPID, parentDir) # fuel driverFuel, totalFuel = getFuel(driverInfo['fuelcard']) #fund driverFunds, totalFund = getFunds(dId, periodStart, periodEnd, usersList) # load driverLoadList = [] totalGross = 0 for load in loads: if load['driver_id'] == dId: driverLoadList.append(load) totalGross = totalGross + load['price'] driverLoads = json.dumps(driverLoadList) # truck truck = getTruckNumber(driverInfo['truckId']) balance = None # owner driver if driverInfo['owner']: # balance income = totalGross * driverInfo['salary'] / 100 - totalFund if 0 > income: balance = income * -1 # file statementfile = statementDriverPDF(driverInfo, driverFunds, totalFund, driverLoadList, totalGross, truck, periodStart, periodEnd, company, parentDir, nextRecordId, income) else: # company driver # balance income = totalGross * driverInfo['salary'] / 100 - totalFund - totalFuel if 0 > income: balance = income * -1 # file statementfile = statementOwnerDriverPDF(driverInfo, driverFuel, totalFuel, driverFunds, totalFund, driverLoadList, totalGross, truck, periodStart, periodEnd, company, parentDir, nextRecordId, income) # db recordId = checkBeforeData(driverInfo['id'], periodStart.strftime('%Y-%m-%d'), periodEnd.strftime('%Y-%m-%d')) if recordId == 0: insertData(driverInfo, driverFuel, totalFuel, driverFunds, totalFund, driverLoads, totalGross, truck, periodStart, periodEnd, statementfile, 0, balance, income) nextRecordId = nextRecordId + 1 else: updateData(driverInfo, driverFuel, totalFuel, driverFunds, totalFund, driverLoads, totalGross, truck, periodStart, periodEnd, statementfile, 0, balance, income) else: writingLog(progressbar, 'Driver [' + str(dId) + ']: not found or profile not completed.', currentPID, parentDir) connection.close() sys.exit() if ownerIds: if progressbar == 10: step = int(90 / len(ownerIds)) for ownerId in ownerIds: progressbar = progressbar + step # info ownerInfo = getDriverInfo(ownerId) if ownerInfo: writingLog(progressbar, 'Owner [' + str(ownerInfo['id']) + ']: ' + str(ownerInfo['fullname']), currentPID, parentDir) loadList = [] fundList = [] fuelList = [] totalGross = totalFuel = totalFund = 0 driver = 0 for load in loads: if load['owner_id'] == ownerId: driverInfo = getDriverInfo(load['driver_id']) driverTruck = getTruckNumber(driverInfo['truckId']) load['driver'] = str(driverTruck) + ' ' + str(driverInfo['fullname']) totalGross = totalGross + load['price'] loadList.append(load) driverFunds, driverTotalFund = getFunds(load['driver_id'], datetime.strptime(load['pickupdate'], '%m/%d/%y'), periodEnd, usersList) totalFund+=driverTotalFund if driverFunds: for df in json.loads(driverFunds): df['driver'] = str(driverTruck) + ' ' + str(driverInfo['fullname']) fundList.append(df) driverFuels, driverTotalFuel = getFuel(driverInfo['fuelcard']) totalFuel += driverTotalFuel if driverFuels: for dfl in json.loads(driverFuels): dfl['driver'] = str(driverTruck) + ' ' + str(driverInfo['fullname']) fuelList.append(dfl) # owner fund ownerFund, ownerTotalFund = getFunds(ownerId, periodStart, periodEnd, usersList) totalFund+=ownerTotalFund if ownerFund: for of in json.loads(ownerFund): of['driver'] = str(ownerInfo['fullname']) fundList.append(of) # balance balance = None income = totalGross * ownerInfo['salary'] / 100 - totalFund - totalFuel if 0 > income: balance = income * -1 # statementfile statementfile = statementOwnerPDF(ownerInfo, loadList, totalGross, fundList,totalFund, fuelList, totalFuel, periodStart, periodEnd, nextRecordId, income) jsonFuels = jsonFunds = jsonLoads = None if loadList: jsonLoads = json.dumps(loadList) if fundList: jsonFunds = json.dumps(fundList) if fuelList: jsonFuels = json.dumps(fuelList) # db recordId = checkBeforeData(ownerInfo['id'], periodStart.strftime('%Y-%m-%d'), periodEnd.strftime('%Y-%m-%d')) if recordId == 0: insertData(ownerInfo, jsonFuels, totalFuel, jsonFunds, totalFund, jsonLoads, totalGross, 0, periodStart, periodEnd, statementfile, 1, balance, income) nextRecordId = nextRecordId + 1 else: updateData(ownerInfo, jsonFuels, totalFuel, jsonFunds, totalFund, jsonLoads, totalGross, 0, periodStart, periodEnd, statementfile, 1, balance, income) else: writingLog(progressbar, 'Owner [' + str(ownerId) + ']: not found or profile not completed.', currentPID, parentDir) connection.close() sys.exit() writingLog(100, 'done',currentPID, parentDir) time.sleep(5) writingLog(0, '','x', parentDir)