Code Sample: Python

Written as part of the accounting software I wrote to help manage the business' finances, this script prints a list of invoices, listed by year or by month. Run through the command line, the script queries the necessary data from the database, prints the data to an HTML file, which is then converted to a PDF and saved to a folder on my computer.

1import sys

2import os

3import calendar

4import MySQLdb

5import datetime

6import pdfkit

7import re

8import string

9from terminaltables import AsciiTable

10from config import *

11

12def strip_un(un_var):

13   vrbl = un_var[2:]

14   return vrbl

15

16def set_paid_vars(arg):

17   if arg.lower() == "unsubmitted" or arg.lower() == "unpaid" or arg.lower() == "submitted" or arg.lower() == "paid" or arg.lower() == "noprint":

18      if re.search(r'^un', arg.lower()) is not None:

19         sql_var = strip_un(arg.lower())

20         value = False

21      else:

22         sql_var = arg.lower()

23         value = True

24   else:

25      print("The Following Parameter You Have Entered is not Valid: %s" % arg.lower())

26      print("Please Try Again. \n")

27      exit()

28   return sql_var, value

29

30def throw_month_error():

31   print("Please Enter a Valid Month Between 1 and 12.\n")

32   exit()

33

34def get_yes_for_true(vrbl):

35   if vrbl == True:

36      output = "Yes"

37      color = "#009933"

38   else:

39      output = "No"

40      color = "#F00"

41   return output, color

42   

43

44now = datetime.datetime.now()

45

46#input python view-invoices.py [year] [month]

47args = sys.argv

48noprint = no_print(args)

49

50query_text = """SELECT DATE_FORMAT(date, '%m/%d'), invoices.invoice_id, clients.client_name, work_period, submitted, paid, DATEDIFF(CURDATE(), date), invoice_items.quantity, clients.hourly_rate

51   FROM invoices

52   JOIN clients ON invoices.client_id = clients.client_id

53   JOIN invoice_items ON invoices.invoice_id = invoice_items.invoice_id"""

54

55if len(args) > 1:

56   year = args[1]

57

58if len(args) == 1: # no parameters given

59   year = now.year

60   month = now.month

61   if month < 10:

62      month_dir = "0{0}".format(month)

63   else:

64      month_dir = month

65   query = "{0} WHERE YEAR(date) = '{1}' AND MONTH(date) = '{2}';".format(query_text, year, month)

66   file_string = "../statements/{0}/{1}/invoice-status".format(year, month_dir)

67   caption = "Invoice Overview: {0}, {1}".format(calendar.month_name[int(month)], year)

68

69elif len(args) == 2: # year only

70   query = "{0} WHERE YEAR(date) = '{1}';".format(query_text, year)

71   file_string = "../statements/{0}/_year/invoice-status".format(year)

72   caption = "All Invoices Issued in {0}".format(year)

73

74elif len(args) == 3: # year and month or paid/unpaid/submitted/unsubmitted or noprint

75   if re.search(r'^[0-9]+$', args[2]): #month given

76      if int(args[2]) <= 12:

77         sql_var = "MONTH(date)"

78         if int(args[2]) < 10:

79            month = "0{0}".format(args[2])

80         else:

81            month = args[2]

82         value = args[2]

83         query = "{0} WHERE YEAR(date) = '{1}' AND MONTH(date) = '{2}'".format(query_text, year, args[2])

84         file_string = "../statements/{0}/{1}/invoice-status".format(year, month)

85         caption = "Invoice Overview: {0}, {1}".format(calendar.month_name[int(args[2])], year)

86      else:

87         throw_month_error()

88   else:  # paid/submitted or noprint given

89      if args[2] == "noprint":

90         query = "{0} WHERE YEAR(date) = '{1}';".format(query_text, year)

91         file_string = "../statements/{0}/_year/invoice-status".format(year)

92         caption = "All Invoices Issued in {0}".format(year)

93      else:

94         sql_var, value = set_paid_vars(args[2])      

95         query = "{0} WHERE YEAR(date) = '{1}' AND {2} = {3};".format(query_text, year, sql_var, value)

96         file_string = "../statements/{0}/_year/invoice-status-{1}".format(year, args[2].lower())

97         caption = "{0} Invoices: {1}".format(string.capwords(args[2].lower()), year)

98

99elif len(args) >= 4: # year, month, and paid/submitted/unsubmitted

100   sql_var, value = set_paid_vars(args[3])

101   if int(args[2]) <= 12:

102      if int(args[2]) < 10:

103         month_dir = "0{0}".format(int(args[2]))

104      else:

105         month_dir = int(args[2])

106         

107      if args[3] == "noprint":

108         query = "{0} WHERE YEAR(date) = '{1}' AND MONTH(date) = '{2}';".format(query_text, year, args[2])

109         file_string = "../statements/{0}/{1}/invoice-status".format(year, month_dir)

110         caption = "{0}, {1} Invoices".format(calendar.month_name[int(args[2])], year)

111      else:

112         sql_var, value = set_paid_vars(args[3])

113         query = "{0} WHERE YEAR(date) = '{1}' AND MONTH(date) = '{2}' AND {3} = {4};".format(query_text, year, args[2], sql_var, value)

114         file_string = "../statements/{0}/{1}/invoice-status-{2}".format(year, month_dir, args[3].lower())

115         caption = "{0} Invoices: {1}, {2}".format(string.capwords(args[3].lower()) ,calendar.month_name[int(args[2])] , year)

116   else:

117      throw_month_error()

118

119# Connect to database

120db = connect_to_mysql()

121

122# Create a cursor so you can execute queries

123cur = db.cursor()

124cur.execute(query)

125print "Successfully Queried Database..."

126 

127# Create and write to your HTML file (file_string set in above if statements)

128file_string_html = file_string + ".html"

129ensure_dir(file_string_html)

130 

131html_file = open(file_string_html, "w")

132writeHtmlHead(html_file, file_string, "Date")

133print "Successfully Opened HTML File..."

134

135html_file.write("""

136     <table>

137         <caption>{0}</caption>

138         <tr><th>Date</th><th>Invoice Number</th><th>Client</th><th>Work/Period</th><th>Amount</th><th>Status</th></tr>

139                 """.format(caption))

140

141total_earned = 0

142total_paid = 0

143total_outstanding = 0

144

145if noprint == "noprint":

146    print("")

147    table_data = []

148    printed_title = 0

149    print("*****  {0} Invoice Status *****").format(year)

150

151for row in cur.fetchall():

152   # date, invoiceID, client, work/period, amount, submitted, paid, datedif

153   submitted, sub_color = get_yes_for_true(row[4])

154   paid, pd_color = get_yes_for_true(row[5])

155   datedif = row[6]

156   amount = row[7] * row[8] * 9/10

157   

158   if submitted == "No":

159      status_color = "#66C2FF"

160      status = "Unsubmitted"

161      total_earned += amount

162      total_outstanding += amount

163

164   else:

165      if paid == "Yes":

166         status_color = "#99E699"

167         status = "Paid"

168         total_earned += amount

169         total_paid += amount

170      else:

171         if datedif < 30:

172            status_color = "#FFFF66"

173            status = "Unpaid"

174         else:

175            status_color = "#FF8080"

176            status = "Overdue"

177         total_earned += amount

178         total_outstanding += amount;

179   

180   html_file.write("""

181      <tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>${4:,.2f}</td><td style="background-color:{5}">{6}</td></tr>

182                   """.format(row[0], row[1], row[2], row[3], amount, status_color, status))

183   

184   if noprint == "noprint":

185      if printed_title == 0:

186          table_data += ['Invoice No', 'Date', 'Amount', 'Description', 'Status'],

187          printed_title = 1

188      table_data += [str(row[1]), str(row[0]), '${:.2f}'.format(amount), str(row[3]), str(status)],

189      table = AsciiTable(table_data)

190      print table.table

191

192if noprint == "noprint":

193    print("")

194

195html_file.write("""

196   </table>

197   <div id="totals">

198      <table>

199         <tr><td>Total Paid:</td><td>${0:,.2f}</td></tr>

200         <tr><td>Total Outstanding:</td><td>${1:,.2f}</td></tr>

201         <tr><td>Total Earned:</td><td>${2:,.2f}</td></tr>

202      </table>

203   </div>

204                """.format(total_paid, total_outstanding, total_earned))

205

206closeHtml(html_file)

207print ("Successfully Wrote Invoices to HTML...")

208

209html_file.close()

210print ("Successfully Closed HTML File...")

211

212# Close the connection to the database

213db.close()

214print ("Successfully Closed Connection to MySQL...")

215

216# Export to PDF

217pdf_file = file_string + ".pdf"

218print(pdf_file)

219pdfkit.from_file(file_string_html, pdf_file)

220print ("Successfully Output to PDF...")

221

222# Remove HTML File

223os.remove(file_string_html)

224if noprint != "noprint":

225    os.system('open {0}'.format(pdf_file))

226print("Successfully Removed HTML File...\n")