listing code now in function
[cdoot:cdoot.git] / cdootlib.c
1 #include <stdio.h>
2 #include <string.h>
3 #include <stdlib.h>
4 #include <sqlite3.h>
5 #include "cdootlib.h"
6
7 int retval           = 0;
8 int q_cnt            = 5;
9 int q_size           = 150;
10 int ind              = 0;
11 char **queries       = 0;
12 sqlite3_stmt *stmt   = 0;
13 sqlite3      *handle = 0;
14
15 int* list(int print){
16         int cols = sqlite3_column_count(stmt);
17
18         char project[20];
19         int pdone = 0;
20         int pcount = 0;
21         int id = 0;
22         int ids[100];
23         while(1){
24                 retval = sqlite3_step(stmt);
25                 if(retval == SQLITE_ROW){
26                         for(int col=0 ; col<cols;col++){
27                                 const char *val = (const char*)sqlite3_column_text(stmt,col);
28                                 char *cname = sqlite3_column_name(stmt,col);
29
30                                 if(strcmp(cname, "project") == 0){
31                                         if(strcmp(val, (const char*)project) != 0){
32                                                 if(pcount != 0){        
33                                                         pdone /= pcount;
34                                                         if(print)
35                                                                 printf("\E[34m\033[1mProject %i%% done\033[0m\n\n", pdone);
36                                                         pdone = pcount = 0;
37                                                 }
38                                                 strcpy(project, val);
39                                                 if(print)
40                                                         printf("\E[34m\033[1m%s\033[0m\n", val);
41                                         }
42                                         if(strcmp(sqlite3_column_text(stmt,col+2), "1") == 0){
43                                                 if(print)
44                                                         printf("\E[31m\033[1m");
45                                         }
46                                         else if(strcmp(sqlite3_column_text(stmt,col+2), "2") == 0){
47                                                 if(print)
48                                                         printf("\E[33m\033[1m");
49                                         }else if(strcmp(sqlite3_column_text(stmt,col+2), "3") == 0){
50                                                 if(print)
51                                                         printf("\E[32m\033[1m");                                                
52                                         }
53                                         ids[id++] = atoi(sqlite3_column_text(stmt,col-1));
54                                         if(print)
55                                                 printf("  %i | ", id-1);
56                                 }else if(strcmp(cname, "id") != 0){
57                                         if(strcmp(cname, "summary") == 0){
58                                                 if(print){
59                                                         printf("%-50s | ", val);
60                                                 }
61                                         }else if(strcmp(cname, "done") == 0){
62                                                 pcount++;
63                                                 pdone += atoi(val);
64                                                 if(print)
65                                                         printf("%s%% done", val);
66                                         }
67                                 }
68                         }
69                         if(print){
70                                 printf("\033[0m");
71                                 printf("\n");
72                         }
73                 }
74                 else if(retval == SQLITE_DONE){
75                         if(pcount != 0){        
76                                 pdone /= pcount;
77                                 if(print)
78                                         printf("\E[34m\033[1mProject %i%% done\033[0m\n", pdone);
79                         }
80                         break;
81                 }else{
82                         printf("Some error encountered\n");
83                         exit(-1);
84                 }
85         }
86         
87         return ids;     
88 }
89
90 // sets up sqlite, creating the table if not created
91 void setupSQLite(){
92         queries = malloc(sizeof(char) * q_cnt * q_size);
93         retval = sqlite3_open("todo.sqlite3", &handle);
94         if(retval){
95                 printf("Database connection failed\n");
96                 exit(-1);
97         }
98
99         char create_table[200] = "CREATE TABLE IF NOT EXISTS todos (id INTEGER PRIMARY KEY, project TEXT, summary TEXT, priority INTEGER, done INTEGER);";
100         sqlite3_exec(handle,create_table,0,0,0);
101 }
102
103 // lists all todos and colours them
104 int* listAll(int print){
105         queries[ind++] = "SELECT * FROM todos order by project, priority;";
106         retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
107         if(retval){
108             printf("Selecting data from DB Failed\n");
109                 exit(-1);
110         }
111         int *ids = list(print);
112         return ids;
113 }
114
115 // adds a todo
116 void addTodo(char *project, char *summary, int priority){
117         char myq[200];
118         sprintf(myq, "insert into todos(project, summary, priority, done) values (\"%s\", \"%s\", %i, 0);", project, summary, priority);
119         retval = sqlite3_exec(handle,myq,0,0,0);
120         if(retval == 0)
121                 printf("Todo added\n");
122 }
123
124 //removes todo
125 void removeTodo(int id){
126         char myq[200];
127         sprintf(myq, "delete from todos where id=%i;", id);
128         retval = sqlite3_exec(handle, myq, 0, 0, 0);
129         if(retval == 0)
130                 printf("Todo removed\n");
131 }
132
133 // changes a priority of todo
134 void changePriority(int id, int priority){
135         char myq[200];
136         sprintf(myq, "update todos set priority=%i where id=%i;", priority, id);
137         retval = sqlite3_exec(handle, myq, 0, 0, 0);
138         if(retval == 0)
139                 printf("Todo changed\n");
140 }
141
142 // changes how far completed a todo is
143 void changeCompleted(int id, int percent, int incbool){
144         char myq[200];
145         if(incbool){
146                 queries[ind++] = "SELECT * FROM todos order by project, priority;";
147                 retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
148                 if(retval){
149                 printf("Selecting data from DB Failed\n");
150                         exit(-1);
151                 }
152                 int cols = sqlite3_column_count(stmt);
153                 while(1){
154                         retval = sqlite3_step(stmt);
155                         if(retval == SQLITE_ROW){
156                                 for(int col=0 ; col<cols;col++){
157                                         const char *val = (const char*)sqlite3_column_text(stmt,col);
158                                         char *cname = sqlite3_column_name(stmt,col);
159                                         if(strcmp(cname, "done") == 0){
160                                                 percent += atoi(val);
161                                                 break;
162                                         }
163                                 }
164                         }else{
165                                 break;
166                         }
167                 }
168         }               
169         
170
171         sprintf(myq, "update todos set done=%i where id=%i;", percent, id);
172         retval = sqlite3_exec(handle, myq, 0, 0, 0);
173         if(retval == 0)
174                 printf("Todo changed \n");
175 }
176
177 // changes the due date
178 void changeDate(int id, char *date){
179         char myq[200];
180         sprintf(myq, "update todos set date=\"%s\" where id=%i;", date, id);
181         retval = sqlite3_exec(handle, myq, 0, 0, 0);
182         if(retval == 0)
183                 printf("Todo changed\n");       
184 }
185
186 //TODO: get ods export to work
187 void exportToODS(char *fn){
188         system("unzip exampleODS.ods -d exampleODS > /dev/null");
189         system("cp contentExample.xml exampleODS/content.xml");
190         FILE *fp = fopen("exampleODS/content.xml", "a");
191         char text[2000];
192         
193         queries[ind++] = "SELECT * FROM todos order by project, priority;";
194         retval = sqlite3_prepare_v2(handle,queries[ind-1],-1,&stmt,0);
195         if(retval){
196             printf("Selecting data from DB Failed\n");
197                 exit(-1);
198         }
199         int cols = sqlite3_column_count(stmt);
200
201         char project[20];
202         while(1){
203                 retval = sqlite3_step(stmt);
204                 if(retval == SQLITE_ROW){
205                         for(int col=0 ; col<cols;col++){
206                                 const char *val = (const char*)sqlite3_column_text(stmt,col);
207                                 char *cname = sqlite3_column_name(stmt,col);
208
209                                 if(strcmp(cname, "project") == 0){
210                                         if(strcmp(val, (const char*)project) != 0){
211                                                 strcpy(project, val);
212                                                 char stuff[300];
213                                                 sprintf(stuff, "<table:table-column table:style-name=\"co4\" table:default-cell-style-name=\"Default\"/><table:table-row table:style-name=\"ro1\"><table:table-cell table:style-name=\"ce1\" office:value-type=\"string\"><text:p>%s</text:p></table:table-cell><table:table-cell/></table:table-row>", project);
214                                                 strcat(text, stuff);
215                                         }
216                                         if(strcmp(sqlite3_column_text(stmt,col+2), "1") == 0){
217                                                 // red
218                                         }else if(strcmp(sqlite3_column_text(stmt,col+2), "2") == 0){
219                                                 // yellow
220                                         }else if(strcmp(sqlite3_column_text(stmt,col+2), "3") == 0){
221                                                 // green                                        
222                                         }
223                                 }else if(strcmp(cname, "id") != 0){
224                                         if(strcmp(cname, "summary") == 0){
225                                                 // summary here
226                                         }
227                                 }
228                         }
229                 }
230                 else if(retval == SQLITE_DONE){
231                         break;
232                 }else{
233                         printf("Some error encountered\n");
234                         exit(-1);
235                 }
236   }     
237   strcat(text, "</table:table></office:spreadsheet></office:body></office:document-content>");
238   fwrite(text, 1, strlen(text), fp);
239   fclose(fp);
240   printf("%s\n", text);
241 }
242
243 // show only todos matching priority
244 void filterPriority(int priority){
245         char myq[400];
246         sprintf(myq, "SELECT * FROM todos where priority=%i order by project, priority;", priority);
247         queries[0] = myq;
248         retval = sqlite3_prepare_v2(handle,queries[0],-1,&stmt,0);
249         if(retval){
250             printf("Selecting data from DB Failed\n");
251                 exit(-1);
252         }
253         list(1);
254 }
255
256 // show only todos like *pro
257 void filterProject(char *pro){
258         char myq[400];
259         sprintf(myq, "SELECT * FROM todos where project like \"%%%s%%\" order by project, priority;", pro);
260         queries[0] = myq;
261         retval = sqlite3_prepare_v2(handle,queries[0],-1,&stmt,0);
262         if(retval){
263             printf("Selecting data from DB Failed\n");
264                 exit(-1);
265         }
266         list(1);
267 }
268
269 // frees queries and closes handle
270 void killSQLite(){
271         sqlite3_close(handle);
272         free(queries);
273 }