· 7 years ago · Mar 02, 2019, 04:38 PM
1package DatabaseManager;
2
3import java.sql.*;
4import java.text.NumberFormat;
5import java.util.*;
6
7/*
8 * Created by Marco-Edoardo Palma.
9 */
10import Commons.*;
11import static DatabaseManager.Stringifiable.globalDateFormat;
12import java.text.ParseException;
13import java.util.Date;
14import java.util.logging.Level;
15import java.util.logging.Logger;
16
17/*
18 * This class will process queries from QueryComposer.java into DatabaseManager.
19 * The 'interface' between frontend and backend
20 */
21public class DataExchange
22{
23
24 private DatabaseManager dbM;
25
26 public DataExchange(DatabaseManager databaseManager)
27 {
28 this.dbM = databaseManager;
29 }
30
31 /**
32 * formats the price according to the UK currency.
33 *
34 * @param price
35 * @return
36 */
37 public static String formatPrice(double price)
38 {
39 NumberFormat priceNumberFormat = NumberFormat.getCurrencyInstance(Locale.UK);
40 return priceNumberFormat.format(price);
41 }
42
43 /**
44 * formats the percentage to 2dp
45 *
46 * @param percentage
47 * @return
48 */
49 public static String formatPercentage(double percentage)
50 {
51 NumberFormat numberFormat = NumberFormat.getPercentInstance();
52 numberFormat.setMaximumFractionDigits(2);
53 numberFormat.setMinimumFractionDigits(2);
54 return numberFormat.format(percentage / 100);
55 }
56
57 /**
58 * closes the connection of the database. Do this only before exiting the
59 * application!
60 */
61 public void close()
62 {
63 this.dbM.close();
64 }
65
66 /**
67 * closes the ResultSet of a query. this should be called at the end of
68 * every parsing routine.
69 *
70 * @param resultSet
71 */
72 public static void close(ResultSet resultSet)
73 {
74 try
75 {
76 resultSet.close();
77 } catch (SQLException e)
78 {
79 e.printStackTrace();
80 }
81 }
82
83// public void writeQuery(String q)
84// {
85// this.dbM.writeQuery(q);
86// }
87// public void writeQuery(List<String> list)
88// {
89// this.dbM.writeQuery(list);
90// }
91 /**
92 * Executes the batch of a Statement, then closes the Statement.
93 *
94 * @param s
95 * @throws SQLException
96 */
97 public void writeSqlStatement(Statement s) throws SQLException
98 {
99 s.executeBatch();
100 s.close();
101 }
102
103 /**
104 * Returns a statement generated by the Database connection.
105 *
106 * @return
107 * @throws SQLException
108 */
109 public Statement getSqlStatement() throws SQLException
110 {
111 return this.dbM.getDbCon().createStatement();
112 }
113
114 /**
115 * turns on and off the pragma on foreign keys. This might speed up the
116 * insertion process of multiple insert statements. it is important for this
117 * to be turned on after the insertion was carried out, in order to ensure
118 * the integrity of the data in the database.
119 *
120 * @param b
121 */
122 public void setForiegnKeyPragma(boolean b)
123 {
124 if (b)
125 {
126 this.dbM.writeQuery("PRAGMA foreign_keys = ON;");
127 } else
128 {
129 this.dbM.writeQuery("PRAGMA foreign_keys = OFF;");
130 }
131 }
132
133 /**
134 * Enables and disables the auto commit function of the connection with the
135 * database. When off, no commits will be performed until the commit
136 * function is called. Useful when inserting large amount of data, is should
137 * be turned on whenever possible.
138 *
139 * @param b
140 * @throws SQLException
141 */
142 public void setAutoCommit(boolean b) throws SQLException
143 {
144 this.dbM.getDbCon().setAutoCommit(b);
145 }
146
147 /**
148 * Commits immediately pending statements on the database connection.
149 *
150 * @throws SQLException
151 */
152 public void commitNow() throws SQLException
153 {
154 this.dbM.getDbCon().commit();
155 }
156
157 /**
158 * returns the last id generated by the database. This is especially useful
159 * when after insert statement with a auto increment id.
160 *
161 * @return
162 */
163 private int getLastID()
164 {
165 try
166 {
167 ResultSet resultSet = this.dbM.query(QueryComposer.GETLASTID);
168 int c = resultSet.getInt("id");
169 close(resultSet);
170 return c;
171 } catch (SQLException e)
172 {
173 e.printStackTrace();
174 }
175 System.exit(8);
176 return -1;
177 }
178
179 /*
180 PARSERS:
181
182 SINGLE RETURNS (parse only one object from a ResultSet)
183 */
184 /**
185 * Parses a ResultSet into a UserEntry and does not close the ResultSet.
186 * returns null if a parsing error occurred.
187 *
188 * @param rset
189 * @return
190 */
191 private UserEntry parseUserEntry(ResultSet rset)
192 {
193 try
194 {
195 return new UserEntry(
196 rset.getString("id"),
197 UserEntry.Gender.valueOf(rset.getString("gender")),
198 UserEntry.Age.valueOf(rset.getString("age")),
199 UserEntry.Income.valueOf(rset.getString("income")));
200 } catch (SQLException ex)
201 {
202 Logger.getLogger(DataExchange.class.getName()).log(Level.SEVERE, null, ex);
203 }
204 return null;
205 }
206
207 /**
208 * Parses a ResultSet into a ImpressionEntry and does not close the
209 * ResultSet. returns null if a parsing error occurred.
210 *
211 * @param rset
212 * @return
213 */
214 private ImpressionEntry parseImpressionEntry(ResultSet rset)
215 {
216 try
217 {
218 return new ImpressionEntry(
219 rset.getInt("id"),
220 rset.getString("userId"),
221 globalDateFormat.parse(rset.getString("date")),
222 ImpressionEntry.Context.valueOf(rset.getString("context")),
223 rset.getDouble("impressionCost"));
224 } catch (SQLException ex)
225 {
226 Logger.getLogger(DataExchange.class.getName()).log(Level.SEVERE, null, ex);
227 } catch (ParseException ex)
228 {
229 Logger.getLogger(DataExchange.class.getName()).log(Level.SEVERE, null, ex);
230 }
231 return null;
232 }
233
234 /**
235 * Parses a ResultSet into a ClickEntry and does not close the ResultSet.
236 * returns null if a parsing error occurred.
237 *
238 * @param rset
239 * @return
240 */
241 private ClickEntry parseClickEntry(ResultSet rset)
242 {
243 try
244 {
245 return new ClickEntry(
246 rset.getInt("id"),
247 rset.getString("userId"),
248 globalDateFormat.parse(rset.getString("date")),
249 rset.getDouble("clickCost"));
250 } catch (SQLException | ParseException ex)
251 {
252 Logger.getLogger(DataExchange.class.getName()).log(Level.SEVERE, null, ex);
253 }
254 return null;
255 }
256
257 /**
258 * Parses a ResultSet into a ServerEntry and does not close the ResultSet.
259 * returns null if a parsing error occurred.
260 *
261 * @param rset
262 * @return
263 */
264 private ServerEntry parseServerEntry(ResultSet rset)
265 {
266 try
267 {
268 return new ServerEntry(
269 rset.getInt("id"),
270 rset.getString("userId"),
271 globalDateFormat.parse(rset.getString("entryDate")),
272 globalDateFormat.parse(rset.getString("exitDate")),
273 rset.getInt("pagesViewed"),
274 ServerEntry.Conversion.valueOf(rset.getString("conversion")));
275 } catch (SQLException | ParseException ex)
276 {
277 Logger.getLogger(DataExchange.class.getName()).log(Level.SEVERE, null, ex);
278 }
279 return null;
280 }
281
282 /*
283 MULTIPLE RETURNS (parse a list of objects from a ResultSet)
284 */
285 /**
286 * Parses a result set into a list of UserEntrys, returns an empty list if a
287 * parsing error occurred. It does not close the ResultSet.
288 *
289 * @param rset
290 * @return
291 */
292 private List<UserEntry> parseUserEntrys(ResultSet rset)
293 {
294 try
295 {
296 List<UserEntry> users = new LinkedList<UserEntry>();
297 while (rset.next())
298 {
299 users.add(parseUserEntry(rset));
300 }
301
302 return users;
303 } catch (Exception e)
304 {
305 e.printStackTrace();
306 return new LinkedList<UserEntry>();
307 }
308 }
309
310 /**
311 * Parses a result set into a list of ImpressionEntry, returns an empty list
312 * if a parsing error occurred. It does not close the ResultSet.
313 *
314 * @param rset
315 * @return
316 */
317 private List<ImpressionEntry> parseImpressionEntrys(ResultSet rset)
318 {
319 try
320 {
321 List<ImpressionEntry> impressions = new LinkedList<ImpressionEntry>();
322 while (rset.next())
323 {
324 impressions.add(parseImpressionEntry(rset));
325 }
326
327 return impressions;
328 } catch (Exception e)
329 {
330 e.printStackTrace();
331 return new LinkedList<ImpressionEntry>();
332 }
333 }
334
335 /**
336 * Parses a result set into a list of ClickEntry, returns an empty list if a
337 * parsing error occurred. It does not close the ResultSet.
338 *
339 * @param rset
340 * @return
341 */
342 private List<ClickEntry> parseClickEntrys(ResultSet rset)
343 {
344 try
345 {
346 List<ClickEntry> clicks = new LinkedList<ClickEntry>();
347 while (rset.next())
348 {
349 clicks.add(parseClickEntry(rset));
350 }
351
352 return clicks;
353 } catch (SQLException e)
354 {
355 return new LinkedList<ClickEntry>();
356 }
357 }
358
359 /**
360 * Parses a result set into a list of ServerEntry, returns an empty list if
361 * a parsing error occurred. It does not close the ResultSet.
362 *
363 * @param rset
364 * @return
365 */
366 private List<ServerEntry> parseServerEntrys(ResultSet rset)
367 {
368 try
369 {
370 List<ServerEntry> servers = new LinkedList<ServerEntry>();
371 while (rset.next())
372 {
373 servers.add(parseServerEntry(rset));
374 }
375
376 return servers;
377 } catch (Exception e)
378 {
379 e.printStackTrace();
380 return new LinkedList<ServerEntry>();
381 }
382 }
383
384 /**
385 * Parses a result set into a map of settings in the form name -> value,
386 * returns an empty map if a parsing error occurred. It does not close the
387 * ResultSet.
388 *
389 * @param rset
390 * @return
391 */
392 private Map<String, String> parseSettings(ResultSet rset)
393 {
394 try
395 {
396 Map<String, String> settings = new HashMap<String, String>();
397 while (rset.next())
398 {
399 settings.put(rset.getString("name"), rset.getString("value"));
400 }
401
402 return settings;
403 } catch (Exception e)
404 {
405 e.printStackTrace();
406 return new HashMap<String, String>();
407 }
408 }
409
410 /*
411 INSERT STATEMENTS
412 */
413 public void insertUserStmt(UserEntry user)
414 {
415 this.dbM.writeQuery(QueryComposer.insertUserStmt(user));
416 }
417
418 public void insertImpressionStmt(ImpressionEntry ie)
419 {
420 this.dbM.writeQuery(QueryComposer.insertImpressionStmt(ie));
421 ie.setId(this.getLastID());
422 }
423
424 public void insertClickStmt(ClickEntry ce)
425 {
426 this.dbM.writeQuery(QueryComposer.insertClickStmt(ce));
427 ce.setId(this.getLastID());
428 }
429
430 public void insertServerStmt(ServerEntry se)
431 {
432 this.dbM.writeQuery(QueryComposer.insertServerStmt(se));
433 se.setId(this.getLastID());
434 }
435
436 public void insertSettingStmt(String name, String value)
437 {
438 this.dbM.writeQuery(QueryComposer.insertSettingStmt(name, value));
439 }
440
441 /*
442 UPDATE STATEMENTS
443 */
444 // if you need them call me up (Marco)
445 /*
446 DELETE STATEMENTS
447 */
448 // if you need them call me up (Marco)
449 /*
450 DROP ALL STATEMENTS
451 */
452 public void dropAllFrom_USERS()
453 {
454 this.dbM.writeQuery(QueryComposer.dropAllFrom_USERS);
455 }
456
457 public void dropAllFrom_IMPRESSION_LOGS()
458 {
459 this.dbM.writeQuery(QueryComposer.dropAllFrom_IMPRESSION_LOGS);
460 }
461
462 public void dropAllFrom_CLICK_LOGS()
463 {
464 this.dbM.writeQuery(QueryComposer.dropAllFrom_CLICK_LOGS);
465 }
466
467 public void dropAllFrom_SERVER_LOGS()
468 {
469 this.dbM.writeQuery(QueryComposer.dropAllFrom_SERVER_LOGS);
470 }
471
472 public void dropAllFrom_SETTINGS()
473 {
474 this.dbM.writeQuery(QueryComposer.dropAllFrom_SETTINGS);
475 }
476
477 public void dropAll_noSettings()
478 {
479 this.dbM.writeQuery(QueryComposer.dropAll_noSettings);
480 }
481
482 /*
483 COUNT STATEMENTS
484 */
485
486 private int parseCountQuery(ResultSet resultSet)
487 {
488 try
489 {
490 int tmp = resultSet.getInt("c");
491 close(resultSet);
492 return tmp;
493 } catch (SQLException e)
494 {
495 e.printStackTrace();
496 return 0;
497 }
498 }
499
500 public int countAllFrom_USERS()
501 {
502 return this.parseCountQuery(this.dbM.query(QueryComposer.countAllFrom_USERS));
503 }
504
505 public int countAllFrom_IMPRESSION_LOGS()
506 {
507 return this.parseCountQuery(this.dbM.query(QueryComposer.countAllFrom_IMPRESSION_LOGS));
508 }
509
510 public int countAllFrom_CLICK_LOGS()
511 {
512 return this.parseCountQuery(this.dbM.query(QueryComposer.countAllFrom_CLICK_LOGS));
513 }
514
515 public int countAllFrom_SERVER_LOGS()
516 {
517 return this.parseCountQuery(this.dbM.query(QueryComposer.countAllFrom_SERVER_LOGS));
518 }
519
520 public int countAllFrom_SETTINGS()
521 {
522 return this.parseCountQuery(this.dbM.query(QueryComposer.countAllFrom_SETTINGS));
523 }
524
525 /*
526 SELECT ALL STATEMENTS
527 */
528 public List<UserEntry> selectAllFrom_USERS()
529 {
530 ResultSet rset = this.dbM.query(QueryComposer.selectAllFrom_USERS);
531 List tmp = parseUserEntrys(rset);
532 close(rset);
533 return tmp;
534 }
535
536 public List<ImpressionEntry> selectAllFrom_IMPRESSION_LOGS()
537 {
538 ResultSet rset = this.dbM.query(QueryComposer.selectAllFrom_IMPRESSION_LOGS);
539 List tmp = parseImpressionEntrys(rset);
540 close(rset);
541 return tmp;
542 }
543
544 public List<ClickEntry> selectAllFrom_CLICK_LOGS()
545 {
546 ResultSet rset = this.dbM.query(QueryComposer.selectAllFrom_CLICK_LOGS);
547 List tmp = parseClickEntrys(rset);
548 close(rset);
549 return tmp;
550 }
551
552 public List<ServerEntry> selectAllFrom_SERVER_LOGS()
553 {
554 ResultSet rset = this.dbM.query(QueryComposer.selectAllFrom_SERVER_LOGS);
555 List tmp = parseServerEntrys(rset);
556 close(rset);
557 return tmp;
558 }
559
560 public Map<String, String> selectAllFrom_SETTINGS()
561 {
562 ResultSet rset = this.dbM.query(QueryComposer.selectAllFrom_SETTINGS);
563 Map tmp = parseSettings(rset);
564 close(rset);
565 return tmp;
566 }
567
568 /*
569 SELECT BY ID
570 */
571 public UserEntry selectByIdFrom_USERS(String id)
572 {
573 ResultSet rset = this.dbM.query(QueryComposer.selectByIdFrom_USERS(id));
574
575 UserEntry tmp = (UserEntry) this.parseUserEntry(rset);
576
577 close(rset);
578 return tmp;
579 }
580
581 public ImpressionEntry selectByIdFrom_IMPRESSION_LOGS(int id)
582 {
583 ResultSet rset = this.dbM.query(QueryComposer.selectByIdFrom_IMPRESSION_LOGS(id));
584
585 ImpressionEntry tmp = (ImpressionEntry) this.parseImpressionEntry(rset);
586
587 close(rset);
588 return tmp;
589 }
590
591 public ClickEntry selectByIdFrom_CLICK_LOGS(int id)
592 {
593 ResultSet rset = this.dbM.query(QueryComposer.selectByIdFrom_CLICK_LOGS(id));
594
595 ClickEntry tmp = (ClickEntry) this.parseClickEntry(rset);
596
597 close(rset);
598 return tmp;
599 }
600
601 public ServerEntry selectByIdFrom_SERVER_LOGS(int id)
602 {
603 ResultSet rset = this.dbM.query(QueryComposer.selectByIdFrom_SERVER_LOGS(id));
604
605 ServerEntry tmp = (ServerEntry) this.parseServerEntry(rset);
606
607 close(rset);
608 return tmp;
609 }
610
611 /*
612 SELECT BY userId
613 */
614 public List<ImpressionEntry> selectByUserIdFrom_IMPRESSION_LOGS(String userId)
615 {
616 ResultSet rset = this.dbM.query(QueryComposer.selectByUserIdFrom_IMPRESSION_LOGS(userId));
617 List tmp = parseImpressionEntrys(rset);
618 close(rset);
619 return tmp;
620 }
621
622 public List<ClickEntry> selectByUserIdFrom_CLICK_LOGS(String userId)
623 {
624 ResultSet rset = this.dbM.query(QueryComposer.selectByUserIdFrom_CLICK_LOGS(userId));
625 List tmp = parseClickEntrys(rset);
626 close(rset);
627 return tmp;
628 }
629
630 public List<ServerEntry> selectByUserIdFrom_SERVER_LOGS(String userId)
631 {
632 ResultSet rset = this.dbM.query(QueryComposer.selectByUserIdFrom_SERVER_LOGS(userId));
633 List tmp = parseServerEntrys(rset);
634 close(rset);
635 return tmp;
636 }
637
638 public String selectByNameFrom_SETTINGS(String name)
639 {
640 ResultSet resultSet = this.dbM.query(QueryComposer.selectByNameFrom_SETTINGS(name));
641 try
642 {
643 return resultSet.getString("value");
644 } catch (SQLException ex)
645 {
646 return null;
647 }
648 }
649
650
651
652 /*
653 Method that avoids duplicates for lists;
654 */
655 private List<Number> getNumbersInfo(ResultSet resultSetC) {
656 try {
657 List<Number> numberC=new LinkedList<>();
658 while(resultSetC.next())
659 {
660 numberC.add(resultSetC.getDouble("GroupedValues"));
661 }
662
663 return numberC;
664 }
665 catch (SQLException ex)
666 {
667 return null;
668 }
669 }
670
671 /*
672 Method that avoids duplicates for ints
673 */
674 private Integer getIntegerInfo(ResultSet resultInts) {
675 int unique=0;
676 try {
677 unique=resultInts.getInt("GroupedValues");
678 return unique;
679 }catch (SQLException ex)
680 {
681 return null;
682 }
683 }
684
685 private Double getDoubleInfo(ResultSet resultInts) {
686 double unique=0;
687 try {
688 unique=resultInts.getInt("GroupedValues");
689 System.out.println(unique);
690 return unique;
691 }catch (SQLException ex)
692 {
693 return null;
694 }
695 }
696
697 private List<Tuple<String,Number>> getInfoTuple(ResultSet resultTuple)
698 {
699 List<Tuple<String,Number>> root=new LinkedList<>();
700 List<Date> hour=new LinkedList<>();
701 //Time hr;
702 List<Number> number=new LinkedList<>();
703 try{
704 //hr=resultPerHour.getTime("GroupedValues");
705 while(resultTuple.next())
706 {
707 root.add(new Tuple<String,Number>(resultTuple.getString("d") ,(resultTuple.getFloat("c"))));
708 }
709 for (Tuple<String, Number> stringNumberTuple : root) {
710 System.out.println(stringNumberTuple.getX()+" "+stringNumberTuple.getY());
711 }
712
713 //return <>
714 }catch (SQLException ex)
715 {
716 return null;
717 }
718 return new LinkedList<>();
719 }
720 private List<Tuple<String,Number>> getInfoTupleDivision(ResultSet resultTuple1, ResultSet resultTuple2)
721 {
722 List<Tuple<String,Number>> root1=new LinkedList<>();
723 List<Tuple<String,Number>> root2=new LinkedList<>();
724 List<Date> hour=new LinkedList<>();
725 //Time hr;
726 List<Number> number=new LinkedList<>();
727 try{
728 //hr=resultPerHour.getTime("GroupedValues");
729 while(resultTuple1.next())
730 {
731 root1.add(new Tuple<String,Number>(resultTuple1.getString("d") ,(resultTuple1.getFloat("c"))));
732
733 }
734 while(resultTuple2.next())
735 {
736 root2.add(new Tuple<>(resultTuple2.getString("d"),(resultTuple2.getFloat("c"))));
737 }
738 for(int i=0;i<root1.size();i++)
739 {
740 System.out.println(root1.get(i).getX()+" "+formatPrice(root1.get(i).getY().floatValue()/root2.get(i).getY().floatValue()));
741 }
742
743 //return <>
744 }catch (SQLException ex)
745 {
746 return null;
747 }
748 return new LinkedList<>();
749 }
750
751 private List<Tuple<String,Number>> getInfoTupleDivisionNoPrice(ResultSet resultTuple1, ResultSet resultTuple2)
752 {
753 List<Tuple<String,Number>> root1=new LinkedList<>();
754 List<Tuple<String,Number>> root2=new LinkedList<>();
755
756 try{
757
758 while(resultTuple1.next())
759 {
760 root1.add(new Tuple<String,Number>(resultTuple1.getString("d") ,(resultTuple1.getFloat("c"))));
761
762 }
763 while(resultTuple2.next())
764 {
765 root2.add(new Tuple<>(resultTuple2.getString("d"),(resultTuple2.getFloat("c"))));
766 }
767 for(int i=0;i<root1.size();i++)
768 {
769 System.out.println(root1.get(i).getX()+" "+(root1.get(i).getY().floatValue()/root2.get(i).getY().floatValue()));
770 }
771
772 //return <>
773 }catch (SQLException ex)
774 {
775 return null;
776 }
777 return new LinkedList<>();
778 }
779
780 /*
781 FOR TOTAL CPM--DIVIDE BY 1000
782 */
783 private List<Tuple<String,Number>> getInfoTupleDivisionCPM(ResultSet resultTuple1, ResultSet resultTuple2)
784 {
785 List<Tuple<String,Number>> root1=new LinkedList<>();
786 List<Tuple<String,Number>> root2=new LinkedList<>();
787
788 try{
789
790 while(resultTuple1.next())
791 {
792 root1.add(new Tuple<String,Number>(resultTuple1.getString("d") ,(resultTuple1.getFloat("c"))));
793
794 }
795 while(resultTuple2.next())
796 {
797 root2.add(new Tuple<>(resultTuple2.getString("d"),(resultTuple2.getFloat("c")/1000)));
798 }
799 for(int i=0;i<root1.size();i++)
800 {
801 System.out.println(root1.get(i).getX()+" "+formatPrice(root1.get(i).getY().floatValue()/root2.get(i).getY().floatValue()));
802 }
803
804 //return <>
805 }catch (SQLException ex)
806 {
807 return null;
808 }
809 return new LinkedList<>();
810 }
811
812
813
814 /*
815 Data call for number of impressions per week.
816 Maybe return the actual list?--> number
817 */
818 public List<Tuple<String ,Number > >getNumberOfImpressionsPerWeek()
819 {
820 ResultSet resultPerWeek=this.dbM.query(QueryComposer.getNumberOfImpressionsPerWeek);
821 return getInfoTuple(resultPerWeek);
822 }
823
824 /*
825 Number of Clicks per week
826 CHANGE TO BE PER WEEK====ATM IS OVER ALL PERIOD.
827 */
828
829 public List<Tuple<String ,Number > >getNumberOfClicksPerWeek()
830 {
831 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfClicksPerWeek);
832 return getInfoTuple(resultPerHour);
833 }
834
835
836
837 public Integer getNumberOfUniques()
838 {
839 ResultSet resultSetUniques=this.dbM.query(QueryComposer.getNumberOfUniques);
840 return getIntegerInfo(resultSetUniques);
841
842 }
843
844
845
846 public Integer getNumberOfBounces()
847 {
848 ResultSet resultSetBounce=this.dbM.query(QueryComposer.getNumberOfBounces);
849 return getIntegerInfo(resultSetBounce);
850 }
851
852 public Integer getNumberOfConversions()
853 {
854 ResultSet resultSetConversions=this.dbM.query(QueryComposer.getNumberOfConversions);
855 return getIntegerInfo(resultSetConversions);
856 }
857
858 public Double getTotalCost()
859 {
860 ResultSet resultTotalCost=this.dbM.query(QueryComposer.getTotalCost);
861 return getDoubleInfo(resultTotalCost);
862 }
863
864
865 /*
866 NUMBER OF IMPRESSIONS
867 */
868 public List<Tuple<String ,Number > >getNumberOfImpressionsPerHour()
869 {
870 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfImpressionsPerHour);
871 return getInfoTuple(resultPerHour);
872 }
873
874 public List<Tuple<String ,Number > >getNumberOfImpressionsPerDay()
875 {
876 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfImpressionsPerDay);
877 return getInfoTuple(resultPerHour);
878 }
879
880 /*
881 NUMBER OF CLICKS
882 */
883 public List<Tuple<String ,Number > >getNumberOfClicksPerHour()
884 {
885 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfClicksPerHour);
886 return getInfoTuple(resultPerHour);
887 }
888
889 public List<Tuple<String ,Number > >getNumberOfClicksPerDay()
890 {
891 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfClicksPerDay);
892 return getInfoTuple(resultPerHour);
893 }
894
895 /*
896 NUMBER OF UNIQUES
897 */
898 public List<Tuple<String ,Number > >getNumberOfUniquesPerHour()
899 {
900 ResultSet resultPerHour=this.dbM.query(QueryComposer.getGetNumberOfUniquesPerHours);
901 return getInfoTuple(resultPerHour);
902 }
903 public List<Tuple<String ,Number > >getNumberOfUniquesPerDay()
904 {
905 ResultSet resultPerHour=this.dbM.query(QueryComposer.getGetNumberOfUniquesPerDay);
906 return getInfoTuple(resultPerHour);
907 }
908 public List<Tuple<String ,Number > >getNumberOfUniquesPerWeek()
909 {
910 ResultSet resultPerHour=this.dbM.query(QueryComposer.getGetNumberOfUniquesPerWeek);
911 return getInfoTuple(resultPerHour);
912 }
913
914
915 /*
916 NUMBER OF BOUNCES
917 */
918
919
920 public List<Tuple<String ,Number > >getNumberOfBouncesPerHour()
921 {
922 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfBouncesPerHour);
923 return getInfoTuple(resultPerHour);
924 }
925 public List<Tuple<String ,Number > >getNumberOfBouncesPerDay()
926 {
927 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfBouncesPerDay);
928 return getInfoTuple(resultPerHour);
929 }
930 public List<Tuple<String ,Number > >getNumberOfBouncesPerWeek()
931 {
932 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfBouncesPerWeek);
933 return getInfoTuple(resultPerHour);
934 }
935
936 /*
937 NUMBER OF CONVERSIONS
938 */
939
940 public List<Tuple<String ,Number > >getNumberOfConversionsPerHour()
941 {
942 ResultSet resultPerHour=this.dbM.query(QueryComposer.getNumberOfConversionsPerHour);
943 return getInfoTuple(resultPerHour);
944 }
945 public List<Tuple<String ,Number > >getNumberOfConversionsPerDay()
946 {
947 ResultSet resultPerDay=this.dbM.query(QueryComposer.getNumberOfConversionsPerDay);
948 return getInfoTuple(resultPerDay);
949 }
950 public List<Tuple<String ,Number > >getNumberOfConversionsPerWeek()
951 {
952 ResultSet resultPerWeek=this.dbM.query(QueryComposer.getNumberOfConversionsPerWeek);
953 return getInfoTuple(resultPerWeek);
954 }
955
956 /*
957 TOTAL COST NUMBER
958 */
959 public List<Tuple<String ,Number > >getTotalCostPerHour()
960 {
961 ResultSet resultPerHour=this.dbM.query(QueryComposer.getTotalCostPerHour);
962 return getInfoTuple(resultPerHour);
963 }
964 public List<Tuple<String ,Number > >getTotalCostPerDay()
965 {
966 ResultSet resultPerDay=this.dbM.query(QueryComposer.getTotalCostPerDay);
967 return getInfoTuple(resultPerDay);
968 }
969 public List<Tuple<String ,Number > >getTotalCostPerWeek()
970 {
971 ResultSet resultPerWeek=this.dbM.query(QueryComposer.getTotalCostPerWeek);
972 return getInfoTuple(resultPerWeek);
973 }
974
975 /*
976 CTR the average number of clicks per impression
977 */
978 public List<Tuple<String ,Number>> getCTRPerHour()
979 {
980
981 //ResultSet resultPerHour=this.dbM.query(QueryComposer.getCTRPerHour);
982 //return getInfoTuple(resultPerHour);
983 ResultSet resultTotalImpressionPerHour =this.dbM.query(QueryComposer.getNumberOfImpressionsPerHour);
984 ResultSet resultTotalClicksPerHour=this.dbM.query(QueryComposer.getNumberOfClicksPerHour);
985 List<Tuple<String ,Number>> tmp = getInfoTupleDivisionNoPrice(resultTotalClicksPerHour,resultTotalImpressionPerHour);
986 close(resultTotalClicksPerHour);
987 close(resultTotalImpressionPerHour);
988 return tmp;
989 }
990 public List<Tuple<String ,Number > >getCTRPerDay()
991 {
992// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
993// return getInfoTuple(resultPerDay);
994 ResultSet resultTotalImpressionPerDay =this.dbM.query(QueryComposer.getNumberOfImpressionsPerDay);
995 ResultSet resultTotalClicksPerDay=this.dbM.query(QueryComposer.getNumberOfClicksPerDay);
996 List<Tuple<String ,Number>> tmp= getInfoTupleDivisionNoPrice(resultTotalClicksPerDay,resultTotalImpressionPerDay);
997 close(resultTotalClicksPerDay);
998 close(resultTotalImpressionPerDay);
999 return tmp;
1000 }
1001 public List<Tuple<String ,Number > >getCTRPerWeek()
1002 {
1003// ResultSet resultPerWeek=this.dbM.query(QueryComposer.getCTRPerWeek);
1004// return getInfoTuple(resultPerWeek);
1005 ResultSet resultTotalImpressionPerWeek =this.dbM.query(QueryComposer.getNumberOfImpressionsPerWeek);
1006 ResultSet resultTotalClicksPerWeek=this.dbM.query(QueryComposer.getNumberOfClicksPerWeek);
1007 List<Tuple<String ,Number>> tmp= getInfoTupleDivisionNoPrice(resultTotalClicksPerWeek,resultTotalImpressionPerWeek);
1008 close(resultTotalClicksPerWeek);
1009 close(resultTotalImpressionPerWeek);
1010 return tmp;
1011 }
1012
1013 /*
1014 CPA--COST PER ACQUISITION
1015 */
1016 public List<Tuple<String ,Number>> getCPAPerHour()
1017 {
1018
1019 //ResultSet resultPerHour=this.dbM.query(QueryComposer.getCTRPerHour);
1020 //return getInfoTuple(resultPerHour);
1021 ResultSet resultTotalConversionPerHour =this.dbM.query(QueryComposer.getNumberOfConversionsPerHour);
1022 return getTuples(resultTotalConversionPerHour);
1023 }
1024
1025 private List<Tuple<String, Number>> getTuples(ResultSet resultTotalConversionPerHour) {
1026 ResultSet restultTotalCost=this.dbM.query(QueryComposer.getTotalCostPerHour);
1027 List<Tuple<String ,Number>> tmp = getInfoTupleDivision(restultTotalCost,resultTotalConversionPerHour);
1028 close(resultTotalConversionPerHour);
1029 close(restultTotalCost);
1030 return tmp;
1031 }
1032
1033 public List<Tuple<String ,Number > >getCPAPerDay()
1034 {
1035// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1036// return getInfoTuple(resultPerDay);
1037 ResultSet resultTotalConversionPerDay =this.dbM.query(QueryComposer.getNumberOfConversionsPerDay);
1038 ResultSet resultTotalCostPerDay=this.dbM.query(QueryComposer.getTotalCostPerDay);
1039 List<Tuple<String ,Number>> tmp= getInfoTupleDivision(resultTotalCostPerDay,resultTotalConversionPerDay);
1040 close(resultTotalConversionPerDay);
1041 close(resultTotalCostPerDay);
1042 return tmp;
1043 }
1044 public List<Tuple<String ,Number > >getCPAPerWeek()
1045 {
1046// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1047// return getInfoTuple(resultPerDay);
1048 ResultSet resultTotalConversionPerWeek =this.dbM.query(QueryComposer.getNumberOfConversionsPerWeek);
1049 ResultSet resultTotalCostPerWeek=this.dbM.query(QueryComposer.getTotalCostPerWeek);
1050 List<Tuple<String ,Number>> tmp= getInfoTupleDivision(resultTotalCostPerWeek,resultTotalConversionPerWeek);
1051 close(resultTotalCostPerWeek);
1052 close(resultTotalConversionPerWeek);
1053 return tmp;
1054 }
1055
1056 /*
1057 CPC=the average amount of money spent on an ad for each click
1058 */
1059
1060 public List<Tuple<String ,Number>> getCPCPerHour()
1061 {
1062
1063 //ResultSet resultPerHour=this.dbM.query(QueryComposer.getCTRPerHour);
1064 //return getInfoTuple(resultPerHour);
1065 ResultSet resultTotalClicksPerHour =this.dbM.query(QueryComposer.getNumberOfClicksPerHour);
1066 return getTuples(resultTotalClicksPerHour);
1067 }
1068 public List<Tuple<String ,Number > >getCPCPerDay()
1069 {
1070// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1071// return getInfoTuple(resultPerDay);
1072 ResultSet resultTotalClickPerDay =this.dbM.query(QueryComposer.getNumberOfClicksPerDay);
1073 ResultSet resultTotalCostPerDay=this.dbM.query(QueryComposer.getTotalCostPerDay);
1074 List<Tuple<String ,Number>> tmp= getInfoTupleDivision(resultTotalCostPerDay,resultTotalClickPerDay);
1075 close(resultTotalClickPerDay);
1076 close(resultTotalCostPerDay);
1077 return tmp;
1078 }
1079 public List<Tuple<String ,Number > >getCPCPerWeek()
1080 {
1081// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1082// return getInfoTuple(resultPerDay);
1083 ResultSet resultTotalConversionPerWeek =this.dbM.query(QueryComposer.getNumberOfClicksPerWeek);
1084 ResultSet resultTotalCostPerWeek=this.dbM.query(QueryComposer.getTotalCostPerWeek);
1085 List<Tuple<String ,Number>> tmp= getInfoTupleDivision(resultTotalCostPerWeek,resultTotalConversionPerWeek);
1086 close(resultTotalCostPerWeek);
1087 close(resultTotalConversionPerWeek);
1088 return tmp;
1089 }
1090
1091 /*
1092 CPM- THE AVERAGE AMOUNT OF MONEY SPENT ON AN AD FOR EVERY THOUSAND IMPRESSION
1093 */
1094
1095 public List<Tuple<String ,Number>> getCPMPerHour()
1096 {
1097
1098 //ResultSet resultPerHour=this.dbM.query(QueryComposer.getCTRPerHour);
1099 //return getInfoTuple(resultPerHour);
1100 ResultSet resultTotalImpressionsPerHour =this.dbM.query(QueryComposer.getNumberOfImpressionsPerHour);
1101 ResultSet resultTotalCostPerHour=this.dbM.query(QueryComposer.getTotalCostPerHour);
1102 List<Tuple<String ,Number>> tmp = getInfoTupleDivisionCPM(resultTotalCostPerHour,resultTotalImpressionsPerHour);
1103 close(resultTotalCostPerHour);
1104 close(resultTotalImpressionsPerHour);
1105 return tmp;
1106 }
1107 public List<Tuple<String ,Number > >getCPMPerDay()
1108 {
1109// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1110// return getInfoTuple(resultPerDay);
1111 ResultSet resultTotalImpressionsPerDay =this.dbM.query(QueryComposer.getNumberOfImpressionsPerDay);
1112 ResultSet resultTotalCostPerDay=this.dbM.query(QueryComposer.getTotalCostPerDay);
1113 List<Tuple<String ,Number>> tmp= getInfoTupleDivisionCPM(resultTotalCostPerDay,resultTotalImpressionsPerDay);
1114 close(resultTotalImpressionsPerDay);
1115 close(resultTotalCostPerDay);
1116 return tmp;
1117 }
1118 public List<Tuple<String ,Number > >getCPMPerWeek()
1119 {
1120// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1121// return getInfoTuple(resultPerDay);
1122 ResultSet resultTotalImpressionPerWeek =this.dbM.query(QueryComposer.getNumberOfImpressionsPerWeek);
1123 ResultSet resultTotalCostPerWeek=this.dbM.query(QueryComposer.getTotalCostPerWeek);
1124 List<Tuple<String ,Number>> tmp= getInfoTupleDivisionCPM(resultTotalCostPerWeek,resultTotalImpressionPerWeek);
1125 close(resultTotalCostPerWeek);
1126 close(resultTotalImpressionPerWeek);
1127 return tmp;
1128 }
1129
1130 /*
1131 BOUNCE RATE
1132 */
1133
1134
1135 public List<Tuple<String ,Number>> getBounceRatePerHour()
1136 {
1137
1138 //ResultSet resultPerHour=this.dbM.query(QueryComposer.getCTRPerHour);
1139 //return getInfoTuple(resultPerHour);
1140 ResultSet resultBounceRatePerHour =this.dbM.query(QueryComposer.getNumberOfImpressionsPerHour);
1141 ResultSet resultClickPerHour=this.dbM.query(QueryComposer.getNumberOfClicksPerHour);
1142 List<Tuple<String ,Number>> tmp = getInfoTupleDivisionNoPrice(resultBounceRatePerHour,resultClickPerHour);
1143 close(resultBounceRatePerHour);
1144 close(resultClickPerHour);
1145 return tmp;
1146 }
1147 public List<Tuple<String ,Number > >getBounceRatePerDay()
1148 {
1149// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1150// return getInfoTuple(resultPerDay);
1151 ResultSet resultBounceRatePerDay =this.dbM.query(QueryComposer.getNumberOfBouncesPerDay);
1152 ResultSet resultClickPerDay=this.dbM.query(QueryComposer.getNumberOfClicksPerDay);
1153 List<Tuple<String ,Number>> tmp= getInfoTupleDivisionNoPrice(resultBounceRatePerDay,resultClickPerDay);
1154 close(resultBounceRatePerDay);
1155 close(resultClickPerDay);
1156 return tmp;
1157 }
1158 public List<Tuple<String ,Number > >getBounceRatePerWeek()
1159 {
1160// ResultSet resultPerDay=this.dbM.query(QueryComposer.getCTRPerDay);
1161// return getInfoTuple(resultPerDay);
1162 ResultSet resultBouncePerWeek =this.dbM.query(QueryComposer.getNumberOfBouncesPerWeek);
1163 ResultSet resultClickPerWeek=this.dbM.query(QueryComposer.getNumberOfClicksPerWeek);
1164 List<Tuple<String ,Number>> tmp= getInfoTupleDivisionNoPrice(resultBouncePerWeek,resultClickPerWeek);
1165 close(resultBouncePerWeek);
1166 close(resultClickPerWeek);
1167 return tmp;
1168 }
1169
1170
1171
1172
1173
1174}
1175
1176
1177
1178
1179package DatabaseManager;
1180
1181/*
1182 * Created by Marco-Edoardo Palma.
1183 */
1184import Commons.*;
1185
1186import java.text.NumberFormat;
1187import java.util.Locale;
1188
1189/*
1190 * This class is a container for all of the queries that can be made to the database.
1191 */
1192public class QueryComposer
1193{
1194
1195 private static String CREATE_TABLE_USERS
1196 = "CREATE TABLE IF NOT EXISTS USERS(\n"
1197 + "id VARCHAR(255) PRIMARY KEY NOT NULL,\n"
1198 + "gender VARCHAR(55),\n"
1199 + "age VARCHAR(55),\n"
1200 + "income VARCHAR(55)\n"
1201 + ");";
1202
1203 private static String CREATE_TABLE_IMPRESSION_LOGS
1204 = "CREATE TABLE IF NOT EXISTS IMPRESSION_LOGS(\n"
1205 + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
1206 + "userId VARCHAR(255),\n"
1207 + "date DATE,\n"
1208 + "context VARCHAR(255),\n"
1209 + "impressionCost NUMERIC,\n"
1210 + "FOREIGN KEY (userId) REFERENCES USERS(id)\n"
1211 + ");";
1212
1213 private static String CREATE_TABLE_CLICK_LOGS
1214 = "CREATE TABLE IF NOT EXISTS CLICK_LOGS(\n"
1215 + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
1216 + "userId VARCHAR(255),\n"
1217 + "date DATE,\n"
1218 + "clickCost NUMERIC,\n"
1219 + "FOREIGN KEY (userId) REFERENCES USERS(id)\n"
1220 + ");";
1221
1222 private static String CREATE_TABLE_SERVER_LOGS
1223 = "CREATE TABLE IF NOT EXISTS SERVER_LOGS(\n"
1224 + "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n"
1225 + "userId VARCHAR(255),\n"
1226 + "entryDate DATE,\n"
1227 + "exitDate DATE,\n"
1228 + "pagesViewed INTEGER,\n"
1229 + "conversion VARCHAR(55),\n"
1230 + "FOREIGN KEY (userId) REFERENCES USERS(id)\n"
1231 + ");";
1232
1233 private static String CREATE_TABLE_SETTINGS
1234 = "CREATE TABLE IF NOT EXISTS SETTINGS (\n"
1235 + "name VARCHAR(255) NOT NULL,\n"
1236 + "value TEXT,\n"
1237 + "PRIMARY KEY (name)\n"
1238 + ");";
1239
1240 public static String[] CREATE_TABLES =
1241 {
1242 "PRAGMA foreign_keys = ON;",
1243 CREATE_TABLE_USERS,
1244 CREATE_TABLE_IMPRESSION_LOGS,
1245 CREATE_TABLE_CLICK_LOGS,
1246 CREATE_TABLE_SERVER_LOGS,
1247 CREATE_TABLE_SETTINGS
1248 };
1249
1250 public static String[] CREATE_INDEXES =
1251 {
1252 "CREATE INDEX IF NOT EXISTS IMPRESSION_LOGS_DATE_INDEX ON IMPRESSION_LOGS (date);",
1253 "CREATE INDEX IF NOT EXISTS CLICK_LOGS_DATE_INDEX ON CLICK_LOGS (date);",
1254 "CREATE INDEX IF NOT EXISTS SERVER_LOGS_ENTRY_DATE_INDEX ON SERVER_LOGS (entryDate);",
1255 "CREATE INDEX IF NOT EXISTS SERVER_LOGS_EXIT_DATE_INDEX ON SERVER_LOGS (exitDate);"
1256 };
1257
1258 public static String GETLASTID = "SELECT last_insert_rowid() as id";
1259
1260 /*
1261 INSERT STATEMENTS
1262 */
1263 public static String insertUserStmt(UserEntry user)
1264 {
1265 return "INSERT INTO USERS VALUES (" + user.stringify()+ ");";
1266 }
1267
1268 public static String insertImpressionStmt(ImpressionEntry ie)
1269 {
1270 return "INSERT INTO IMPRESSION_LOGS VALUES (" + ie.stringify() + ");";
1271 }
1272
1273 public static String insertClickStmt(ClickEntry ce)
1274 {
1275 return "INSERT INTO CLICK_LOGS VALUES (" + ce.stringify() + ");";
1276 }
1277
1278 public static String insertServerStmt(ServerEntry se)
1279 {
1280 return "INSERT INTO SERVER_LOGS VALUES (" + se.stringify() + ");";
1281 }
1282
1283 public static String insertSettingStmt(String name, String value)//TODO check... looks shitty!
1284 {
1285 return "INSERT INTO SETTINGS VALUES ('" + name + "', '" + value + "');";
1286 }
1287
1288 /*
1289 UPDATE STATEMENTS
1290 */
1291 // if you need them call me up (Marco)
1292 /*
1293 DELETE STATEMENTS
1294 */
1295 // if you need them call me up (Marco)
1296 /*
1297 DROP ALL STATEMENTS
1298 */
1299 public static String dropAllFrom_USERS = "DELETE FROM USERS;";
1300 public static String dropAllFrom_IMPRESSION_LOGS = "DELETE FROM IMPRESSION_LOGS;";
1301 public static String dropAllFrom_CLICK_LOGS = "DELETE FROM CLICK_LOGS;";
1302 public static String dropAllFrom_SERVER_LOGS = "DELETE FROM SERVER_LOGS;";
1303 public static String dropAllFrom_SETTINGS = "DELETE FROM SETTINGS;";
1304 public static String[] dropAll_noSettings =
1305 {
1306 dropAllFrom_USERS,
1307 dropAllFrom_IMPRESSION_LOGS,
1308 dropAllFrom_CLICK_LOGS,
1309 dropAllFrom_SERVER_LOGS
1310 };
1311
1312 /*
1313 COUNT STATEMENTS
1314 */
1315 public static String countAllFrom_USERS = "SELECT COUNT(*) as c FROM USERS;";
1316 public static String countAllFrom_IMPRESSION_LOGS = "SELECT COUNT(*) as c FROM IMPRESSION_LOGS;";
1317 public static String countAllFrom_CLICK_LOGS = "SELECT COUNT(*) as c FROM CLICK_LOGS;";
1318 public static String countAllFrom_SERVER_LOGS = "SELECT COUNT(*) as c FROM SERVER_LOGS;";
1319 public static String countAllFrom_SETTINGS = "SELECT COUNT(*) as c FROM SETTINGS;";
1320
1321 /*
1322 SELECT ALL STATEMENTS
1323 */
1324 public static String selectAllFrom_USERS = "SELECT * FROM USERS;";
1325 public static String selectAllFrom_IMPRESSION_LOGS = "SELECT * FROM IMPRESSION_LOGS;";
1326 public static String selectAllFrom_CLICK_LOGS = "SELECT * FROM CLICK_LOGS;";
1327 public static String selectAllFrom_SERVER_LOGS = "SELECT * FROM SERVER_LOGS;";
1328 public static String selectAllFrom_SETTINGS = "SELECT * FROM SETTINGS;";
1329
1330 /*
1331 SELECT BY ID
1332 */
1333 public static String selectByIdFrom_USERS(String id)
1334 {
1335 return "SELECT * FROM USERS WHERE USERS.id='" + id + "' LIMIT 1;";
1336 }
1337
1338 public static String selectByIdFrom_IMPRESSION_LOGS(int id)
1339 {
1340 return "SELECT * FROM IMPRESSION_LOGS WHERE IMPRESSION_LOGS.id=" + id + " LIMIT 1;";
1341 }
1342
1343 public static String selectByIdFrom_CLICK_LOGS(int id)
1344 {
1345 return "SELECT * FROM CLICK_LOGS WHERE CLICK_LOGS.id=" + id + " LIMIT 1;";
1346 }
1347
1348 public static String selectByIdFrom_SERVER_LOGS(int id)
1349 {
1350 return "SELECT * FROM SERVER_LOGS WHERE SERVER_LOGS.id=" + id + " LIMIT 1;";
1351 }
1352
1353 /*
1354 SELECT BY userId
1355 */
1356 public static String selectByUserIdFrom_IMPRESSION_LOGS(String userId)
1357 {
1358 return "SELECT * FROM IMPRESSION_LOGS WHERE IMPRESSION_LOGS.userId='" + userId + "';";
1359 }
1360
1361 public static String selectByUserIdFrom_CLICK_LOGS(String userId)
1362 {
1363 return "SELECT * FROM CLICK_LOGS WHERE CLICK_LOGS.userId='" + userId + "';";
1364 }
1365
1366 public static String selectByUserIdFrom_SERVER_LOGS(String userId)
1367 {
1368 return "SELECT * FROM SERVER_LOGS WHERE SERVER_LOGS.userId='" + userId + "';";
1369 }
1370
1371 public static String selectByNameFrom_SETTINGS(String name)
1372 {
1373 return "SELECT * FROM SETTINGS WHERE SETTINGS.name='" + name + "' LIMIT 1;";
1374 }
1375 /*
1376 Number of impressions by week query.
1377 */
1378
1379 public static String getNumberOfImpressionsPerWeek = "select Date as d,count(impressionCost) as c from impression_logs group by strftime('%W', Date) order by Date;";
1380 public static String getNumberOfImpressionsPerHour="select Date as d,count(impressionCost) as c from impression_logs group by strftime('%H:%d', Date) order by Date;";
1381 public static String getNumberOfImpressionsPerDay="select Date as d,count(impressionCost) as c from impression_logs group by strftime('%d', Date) order by Date;";
1382
1383 /*
1384 Number of clicks
1385 */
1386 public static String getNumberOfClicksPerWeek="select Date as d,count(ClickCost) as c from click_logs group by strftime('%W', Date) order by Date;";
1387 public static String getNumberOfClicksPerHour="select Date as d,count(ClickCost) as c from click_logs group by strftime('%H:%d', Date) order by Date;";
1388 public static String getNumberOfClicksPerDay="select Date as d,count(ClickCost) as c from click_logs group by strftime('%d', Date) order by Date;";
1389
1390 /*
1391 *Number of uniques
1392 */
1393 public static String getNumberOfUniques="select count (distinct ID) as GroupedValues from click_logs;";
1394 public static String getGetNumberOfUniquesPerDay="select Date as d,count( id) as c from click_logs group by strftime('%d', Date) order by Date;";
1395 public static String getGetNumberOfUniquesPerWeek="select Date as d,count(distinct id) as c from click_logs group by strftime('%W', Date) order by Date;";
1396 public static String getGetNumberOfUniquesPerHours="select Date as d,count( id) as c from click_logs group by strftime('%H:%d', Date) order by Date;";
1397
1398
1399 /*
1400 Number of Bounces
1401 */
1402 public static String getNumberOfBounces="select count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as GroupedValues from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1;";
1403 public static String getNumberOfBouncesPerDay="select EntryDate as Entry,count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as c from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1 group by strftime('%d',EntryDate) order by EntryDate;";
1404 public static String getNumberOfBouncesPerHour="select EntryDate as d,count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as c from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1 group by strftime('%H:%d',EntryDate) order by EntryDate;";
1405 public static String getNumberOfBouncesPerWeek="select EntryDate as d,count(strftime('%M', ExitDate)-strftime('%M', EntryDate)) as c from server_logs where strftime('%M', ExitDate)-strftime('%M', EntryDate)=0 AND PagesViewed=1 group by strftime('%W',EntryDate);";
1406
1407
1408 /*
1409 Number of Conversions
1410 */
1411 public static String getNumberOfConversions="select count(Conversion) as GroupedValues from server_logs where Conversion='No';";
1412 public static String getNumberOfConversionsPerHour="select EntryDate as d, count(Conversion) as c from server_logs where Conversion='No' group by strftime('%H:%d',EntryDate) order by EntryDate;";
1413 public static String getNumberOfConversionsPerDay="select EntryDate as d, count(Conversion) as c from server_logs where Conversion='No' group by strftime('%d',EntryDate) order by EntryDate;";
1414 public static String getNumberOfConversionsPerWeek="select EntryDate as d, count(Conversion) as c from server_logs where Conversion='No' group by strftime('%W',EntryDate) order by EntryDate;";
1415
1416 /*
1417 Total Cost
1418 */
1419 public static String getTotalCost="select (select sum(impressionCost) from impression_logs) +(select sum(ClickCost) from click_logs) as GroupedValues;";
1420 public static String getTotalCostPerHour="select d,sum(total) as c from (select date as d,ClickCost as total from click_logs union all select date as d,impressionCost as total from impression_logs) as u group by strftime('%H:%d',d) order by d;";
1421 public static String getTotalCostPerWeek="select d,sum(total) as c from (select date as d,ClickCost as total from click_logs union all select date as d,impressionCost as total from impression_logs) as u group by strftime('%W',d) order by d;";
1422 public static String getTotalCostPerDay="select d,sum(total) as c from (select date as d,ClickCost as total from click_logs union all select date as d,impressionCost as total from impression_logs) as u group by strftime('%d',d) order by d";
1423 /*
1424 Gets per hour--24 hours
1425 For time granularity!!!
1426 */
1427 public static String getTotalNumberOfImpressionsPerHour="select count(impressionCost) as GroupedValues from impression_logs group by strftime('%H',Date);";
1428
1429 /*
1430 CTR per week the closest i've got ok=>>>>wrong result tho
1431 */
1432 public static String getCTRPerWeek="SELECT date as d ,cast(count(date) AS FLOAT)/cast((SELECT count(date) FROM impression_logs group by strftime('%W',date) order by date) AS FLOAT) as c FROM click_logs group by strftime('%W',date) order by date;";
1433 public static String getCTRPerDay="SELECT date as d ,cast(count(date) AS FLOAT)/cast((SELECT count(date) FROM impression_logs group by strftime('%d',date) order by date) AS FLOAT) as c FROM click_logs group by strftime('%d',date) order by date;";
1434 public static String getCTRPerHour="SELECT date as d ,cast(count(date) AS FLOAT)/cast((SELECT count(date) FROM impression_logs group by strftime('%H:%d',date) order by date) AS FLOAT) as c FROM click_logs group by strftime('%H:%d',date) order by date;";
1435
1436
1437}