timesheet (7481B)
1 #!/usr/bin/env python2 2 # Copyright (c) 2016 Dyne.org Foundation 3 # timesheetpy is written and maintained by parazyd <parazyd@dyne.org> 4 # 5 # This file is part of timesheetpy 6 # 7 # This source code is free software: you can redistribute it and/or modify 8 # it under the terms of the GNU General Public License as published by 9 # the Free Software Foundation, either version 3 of the License, or 10 # (at your option) any later version. 11 # 12 # This software is distributed in the hope that it will be useful, 13 # but WITHOUT ANY WARRANTY; without even the implied warranty of 14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 15 # GNU General Public License for more details. 16 # 17 # You should have received a copy of the GNU General Public License 18 # along with this source code. If not, see <http://www.gnu.org/licenses/>. 19 20 import argparse 21 from calendar import monthrange 22 from calendar import weekheader 23 import datetime 24 import sys 25 import xlsxwriter 26 27 ## TODO: name parameter 28 29 now = datetime.date.today() 30 31 parser=argparse.ArgumentParser() 32 parser.add_argument("-sm", "--start-month", 33 help="starting month, ex: 9", 34 type=int) 35 parser.add_argument("-sy", "--start-year", 36 help="starting year, ex: 2016", 37 type=int) 38 parser.add_argument("-em", "--end-month", 39 help="ending month, ex: 11", 40 type=int) 41 parser.add_argument("-ey", "--end-year", 42 help="ending year, ex: 2016", 43 type=int) 44 parser.add_argument("-n", "--name", 45 help="name of person, ex: 'John Doe'", 46 type=str) 47 parser.add_argument("-o", "--output-file", 48 help="output file, ex: timesheet.xlsx", 49 default="timesheet.xlsx", 50 type=str) 51 args = parser.parse_args() 52 53 if not args.start_month: 54 args.start_month = now.month 55 if not args.start_year: 56 args.start_year = now.year 57 if not args.end_month: 58 args.end_month = args.start_month 59 if not args.end_year: 60 args.end_year = args.start_year 61 62 global workbook 63 workbook = xlsxwriter.Workbook(args.output_file) 64 65 ## {{{ formatting 66 format_days = workbook.add_format({ 67 'border': True, 68 'bg_color': 'eeeeee', 69 'align': 'right', 70 'font_name': 'Inconsolata' 71 }) 72 73 format_timegridWhite = workbook.add_format({ 74 'border': True, 75 'bg_color': 'ffffff', 76 'align': 'center', 77 'font_name': 'Inconsolata' 78 }) 79 80 format_timegridGrey = workbook.add_format({ 81 'border': True, 82 'bg_color': 'eeeeee', 83 'align': 'center', 84 'font_name': 'Inconsolata' 85 }) 86 87 format_bold = workbook.add_format({ 88 'bold': True 89 }) 90 91 format_centerbold = workbook.add_format({ 92 'bold': True, 93 'align': 'center', 94 'font_name': 'Inconsolata' 95 }) 96 97 format_vcenterbold = workbook.add_format({ 98 'bold': True, 99 'valign': 'vcenter' 100 }) 101 102 format_border = workbook.add_format({ 103 'border': True 104 }) 105 106 format_centerbigger = workbook.add_format({ 107 'bold': True, 108 'align': 'center', 109 'valign': 'vcenter' 110 }) 111 ## }}} 112 113 def putDefaults(mm, yy): 114 worksheet.set_column('A:A', 12) ## make first column wider 115 116 worksheet.set_row(0, 30) 117 worksheet.write('A1', 'STICHTING DYNE.ORG', format_vcenterbold) 118 worksheet.insert_image('E1', 'dyne.png', {'x_scale': 0.55, 'y_scale': 0.55}) 119 120 worksheet.set_row(1, 30) 121 worksheet.write('A2', 'Month:', format_centerbigger) 122 worksheet.write('B2', str(yy) + '-' + str(mm), format_centerbigger) 123 124 worksheet.set_row(2, 30) 125 worksheet.write('A3', 'Name:', format_centerbigger) 126 if args.name: 127 worksheet.merge_range('B3:C3', args.name, format_centerbigger) 128 #worksheet.write('B3', args.name, format_centerbigger) 129 130 worksheet.write('A4', "Total hrs:", format_centerbigger) 131 worksheet.write('A5', "Total days:", format_centerbigger) 132 133 worksheet.set_row(5, 30) 134 worksheet.merge_range('B6:G6', 'TIMESHEET', format_centerbigger) 135 for i in range(1,7): ## cells for project codes 136 worksheet.write(6, i, '', format_centerbold) 137 138 worksheet.write('A7', 'Project', format_bold) 139 worksheet.write('A8', 'W.P.', format_bold) 140 worksheet.write('A9', 'Tag', format_bold) 141 142 ## space from above 143 fieldx = 10 144 fieldy = 0 145 for day in calendar(mm, yy): 146 if fieldx % 2 == 0: 147 worksheet.write(fieldx ,fieldy, day, format_days) 148 for i in range(1,7): 149 worksheet.write(fieldx, i, '', format_timegridWhite) 150 else: 151 worksheet.write(fieldx ,fieldy, day, format_days) 152 for i in range(1,7): 153 worksheet.write(fieldx, i, '', format_timegridGrey) 154 155 sumform = '=SUM(B' + str(fieldx+1) + ':G' + str(fieldx+1) + ')' 156 worksheet.write(fieldx, 7, sumform, format_centerbold) 157 158 fieldx += 1 159 160 ## Bottom: Signature, Date, Free text space 161 worksheet.set_row(43, 30) 162 worksheet.write('A44', 'Signature:', format_vcenterbold) 163 164 #worksheet.set_row(43, 30) 165 #worksheet.write('A44', 'Date:', format_vcenterbold) 166 #worksheet.write('B44', now.isoformat(), format_vcenterbold) 167 168 worksheet.set_row(45, 30) 169 worksheet.write('A46', 'Free text space:', format_vcenterbold) 170 171 ## calculations 172 worksheet.write('H10', 'Total', format_centerbold) 173 174 writetotals() 175 176 def writetotals(): 177 totalsrow = str(42) ## bottom row of totals 178 startrow = str(11) ## first day 179 endrow = str(0) ## last day 180 if total == 30: 181 endrow = str(40) 182 elif total == 31: 183 endrow = str(41) 184 elif total == 28: 185 endrow = str(38) 186 elif total == 29: 187 endrow = str(39) 188 189 fmt = format_centerbold 190 for col in range(66,73): 191 if col == 72: 192 fmt = format_timegridGrey 193 worksheet.write('B4', '=SUM('+chr(col)+startrow+':'+chr(col)+endrow+')', fmt) 194 worksheet.write('B5', '=COUNTIFS('+chr(col)+startrow+':'+chr(col)+endrow+',"<>0")', fmt) 195 196 worksheet.write(chr(col)+totalsrow, '=SUM('+chr(col)+startrow+':'+chr(col)+endrow+')', fmt) 197 198 199 def calendar(mm, yy): 200 month = monthrange(yy, mm) 201 head = weekheader(3) 202 start = month[0] 203 global total 204 total = month[1] 205 206 c = 0 207 montharr = [] 208 for val in range(start, total+start): 209 c = c + 1 210 montharr += ["%s %d" % (head.split()[val % 7], c)] 211 212 return montharr 213 214 def main(mm, yy): 215 global worksheet 216 worksheet = workbook.add_worksheet(str(yy) + '-' + str(mm)) 217 218 #worksheet.set_landscape() 219 worksheet.set_portrait() 220 worksheet.set_paper(9) ## a4 221 worksheet.fit_to_pages(1,1) 222 223 putDefaults(mm, yy) 224 225 if args.start_month != args.end_month or args.start_year != args.end_year: 226 227 228 ## if it's not the same year 229 if args.start_year != args.end_year: 230 year = args.start_year 231 232 ## first year 233 for mnth in range(args.start_month, 13): 234 main(mnth, year) 235 year += 1 236 237 ## until last year 238 while year != args.end_year: 239 for mnth in range(1, 13): 240 main(mnth, year) 241 year += 1 242 243 ## last year 244 if year != args.end_year: 245 year = args.end_year 246 for mnth in range(1, args.end_month+1): 247 main(mnth, year) 248 249 else: 250 year = args.start_year 251 for mnth in range(args.start_month, args.end_month+1): 252 main(mnth, year) 253 254 ## clean up 255 workbook.close() 256 exit(0) 257 else: 258 main(args.start_month, args.start_year) 259 workbook.close() 260 exit(0)