ttimesheet - timesheetpy - simple timesheet generator
HTML git clone git://parazyd.org/timesheetpy.git
DIR Log
DIR Files
DIR Refs
DIR README
DIR LICENSE
---
ttimesheet (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)