[PATCH] Add a flat clocktable formatter useful for tables whose CSV export can be imported into accounting/invoiving tools such as Zoho Invoicing.

[PATCH] Add a flat clocktable formatter useful for tables whose CSV export can be imported into accounting/invoiving tools such as Zoho Invoicing.

[Ross Patterson]

---
 lisp/org-clock.el |   77 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 77 insertions(+)

diff --git a/lisp/org-clock.el b/lisp/org-clock.el
index 8ac215e..bad653e 100644
--- a/lisp/org-clock.el
+++ b/lisp/org-clock.el
@@ -325,6 +325,14 @@ play with them."
   :group 'org-clocktable
   :type 'plist)
 
+(defcustom org-clock-clocktable-flat-columns
+  '("Invoice Date" "Invoice Number" "Customer Name"
+    "Item Name" "Item Desc" "Quantity" "Item Price")
+  "Columns for the org-clocktable-write-flat clocktable formatter.
+Default value matches the ZOHO CSV import format."
+  :group 'org-clocktable
+  :type '(repeat string))
+
 (defcustom org-clock-idle-time nil
   "When non-nil, resolve open clocks if the user is idle more than X minutes."
   :group 'org-clock
@@ -2546,6 +2554,75 @@ from the dynamic block definition."
       (org-table-delete-column))
     total-time))
 
+(defun org-clocktable-write-flat (ipos tables params)
+  "Write out a flat clock table at position IPOS in the current
+buffer.  Useful to render a table which can be exported to CSV and
+then imported into accounting/invoiving tools such as Zoho Invoicing.
+TABLES is a list of tables with clocking data as produced by
+`org-clock-get-table-data'.
+
+PARAMS is the parameter property list obtained from the dynamic block
+definition.  Several parameters are used beyond those used by the
+clocktable dynamic block:
+    :customer A string to be inserted into the 'Customer Name' column
+    :price A number to be inserted into the 'Item Price' column as a rate
+    :number A string to be inserter into the Invoice Number column
+    :columns A list which overrides the default CSV columns from
+             `org-clock-clocktable-flat-columns'
+
+Several other columns are calculated automatically:
+    Item Name: The path to the parent headline
+    Item Desc: The headline
+    Quantity: The clocktime.  Useful with the `;t' formatter to get a
+              decimal billable time.
+"
+  (let ((date (format-time-string "%Y-%m-%d"))
+	(customer (plist-get params :customer))
+	(number (plist-get params :number))
+	(price (plist-get params :price))
+	(columns (or (plist-get params :columns)
+		     org-clock-clocktable-flat-columns)))
+    (insert "\n|")
+    (dolist (column columns)
+      (insert column "|"))
+    (insert "\n|-\n")
+    (dolist (table tables)
+      (let ((rows (nth 2 table))
+	    (parents (list "")))
+	(dotimes (row-idx (length rows))
+	  (let ((row (nth row-idx rows)))
+	    (if (listp row)
+		(progn
+		  (dotimes (parent-idx
+			    (- (nth 0 (nth (- row-idx 1) rows)) (nth 0 row)))
+		    (setcdr (last parents 2) nil))
+		  (if (and (< row-idx (- (length rows) 1))
+			   (< (nth 0 row) (nth 0 (nth (+ row-idx 1) rows))))
+		      (nconc parents (list (cadr row)))
+		    (insert "|")
+		    (dolist (column columns)
+		      (cond
+		       ((equal column "Invoice Date") (insert date))
+		       ((equal column "Invoice Number") (insert number))
+		       ((equal column "Customer Name") (insert customer))
+		       ((equal column "Item Name")
+			(dotimes (parent-idx (length parents))
+			  (insert (nth parent-idx parents))
+			  (if (not (or (= parent-idx 0)
+				       (= parent-idx (- (length parents) 1))))
+			      (insert "/"))))
+		       ((equal column "Item Desc") (insert (cadr row)))
+		       ((equal column "Quantity")
+			(insert (org-minutes-to-hh:mm-string (nth 3 row))))
+		       ((equal column "Item Price")
+			(insert (format "%s" price))))
+		      (insert "|"))
+		    (insert "\n"))))))))
+    (insert "#+TBLFM: " (plist-get params :formula))
+    (org-ctrl-c-ctrl-c)
+    (goto-char ipos)
+    (skip-chars-forward "^|")))
+
 (defun org-clocktable-indent-string (level)
   (if (= level 1)
       ""
-- 
1.7.9.5

Re: [PATCH] Add a flat clocktable formatter useful for tables whose CSV export can be imported into accounting/invoiving tools such as Zoho Invoicing.

[Bastien]

Hi Ross,

thanks for the patch.  Do you mind filling the copyright
assignment so we can accept the patch ?

  http://orgmode.org/cgit.cgi/org-mode.git/plain/request-assign-future.txt

Also, please have a look at this page with guidance on how
to submit a patch with a ChangeLog etc.

  http://orgmode.org/worg/org-contribute.html#sec-4-2

Thanks again,

-- 
 Bastien

Re: [PATCH] Add a flat clocktable formatter useful for tables whose CSV export can be imported into accounting/invoiving tools such as Zoho Invoicing.

[Ross Patterson]

Bastien <bzg@gnu.org> writes:

> thanks for the patch.  Do you mind filling the copyright
> assignment so we can accept the patch ?

I already have an FSF Emacs Assignment from 2008-12-22, RT 393650.  Is
there something else I need to do?

>   http://orgmode.org/cgit.cgi/org-mode.git/plain/request-assign-future.txt
>
> Also, please have a look at this page with guidance on how
> to submit a patch with a ChangeLog etc.
>
>   http://orgmode.org/worg/org-contribute.html#sec-4-2

I did a search for such docs on orgmode.org but only found a pointer to
the list.  Perhaps someone should make these links more obvious.

Thanks,
Ross

Re: [PATCH] Add a flat clocktable formatter useful for tables whose CSV export can be imported into accounting/invoiving tools such as Zoho Invoicing.

[Bastien]

Hi Ross,

Ross Patterson <me@rpatterson.net> writes:

> Bastien <bzg@gnu.org> writes:
>
>> thanks for the patch.  Do you mind filling the copyright
>> assignment so we can accept the patch ?
>
> I already have an FSF Emacs Assignment from 2008-12-22, RT 393650.  Is
> there something else I need to do?

Nope.  I didn't know you had a FSF assignment.

That said, the patch looks more like an add-on to Org for a very
specific use, so putting this code in contrib/ would be better.

Also, `org-clock-clocktable-flat-columns' defines some customizable
values that are hardcoded in `org-clocktable-write-flat'.  Either the
values should not be customizable, or the `org-clocktable-write-flat'
should call them differently?  Just a suggestion.

>>   http://orgmode.org/cgit.cgi/org-mode.git/plain/request-assign-future.txt
>>
>> Also, please have a look at this page with guidance on how
>> to submit a patch with a ChangeLog etc.
>>
>>   http://orgmode.org/worg/org-contribute.html#sec-4-2
>
> I did a search for such docs on orgmode.org but only found a pointer to
> the list.  Perhaps someone should make these links more obvious.

I promoted the org-contribute.html page in the homepage of Org,
thanks for bringing this up!

-- 
 Bastien