API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 19th, 2014
71
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.80 KB | None | 0 0
raw download clone embed print report
  1. import java.sql.ResultSet;
  2. import java.sql.SQLException;
  3.  
  4.  
  5. public class Config {
  6.  
  7. public static String aktPeriodeStart = "2014-3-1";
  8.  
  9. public static String aktPeriodeEnde = "2014-4-1";
  10.  
  11. public static String gepPeriodeEnde = "2014-7-1";
  12.  
  13. public static String statementArzttabelleAusgeben =
  14. "SELECT a.id, a.ansage_id, a.name, a.vorname, a.strasse, a.hausnummer, a.stadt, a.aktiv, a.wertung, a.startpunkte "+
  15. "FROM arzt a "+
  16. "ORDER BY a.name ";
  17.  
  18. public static String statementArzttabelleAusgebenAktiv =
  19. "SELECT a.id, a.ansage_id, a.name, a.vorname, a.strasse, a.hausnummer, a.stadt, a.aktiv, a.wertung, a.startpunkte "+
  20. "FROM arzt a "+
  21. "WHERE a.aktiv=1 "+
  22. "ORDER BY a.name ";
  23.  
  24. public static String statementdienstpunkteZaehlenAlleAerzte = "SELECT temp.id AS id, temp.name, temp.did, SUM(t.punkte) "+
  25. "FROM tag t RIGHT JOIN "+
  26. "(SELECT a.id AS id, a.name AS name, d.tag_id AS did "+
  27. "FROM arzt a LEFT JOIN dienst d "+
  28. "ON d.arzt_id=a.id "+
  29. "ORDER BY a.name)AS temp "+
  30. "ON t.id=temp.did "+
  31. "GROUP BY id " +
  32. "ORDER BY temp.name";
  33.  
  34. public static String statementdienstpunkteDurchschnitt = "SELECT AVG(temp.punkte)"+
  35. "FROM (SELECT a.id AS id, SUM(t.punkte) AS punkte"+
  36. " FROM arzt a, dienst d, tag t"+
  37. " WHERE a.aktiv = 1"+
  38. " AND d.arzt_id = a.id"+
  39. " AND t.id = d.tag_id"+
  40. " GROUP BY id"+
  41. " UNION"+
  42. " SELECT a.id AS id, '0' AS punkte"+
  43. " FROM arzt a, dienst d, tag t"+
  44. " WHERE a.aktiv = 1"+
  45. " AND a.id NOT IN (SELECT a.id AS id"+
  46. " FROM arzt a, dienst d, tag t"+
  47. " WHERE a.aktiv = 1"+
  48. " AND d.arzt_id = a.id"+
  49. " AND t.id = d.tag_id"+
  50. " GROUP BY id"+
  51. " )"+
  52. " )AS temp";
  53.  
  54. public static String dienstpunkteNurAktive="SELECT a.id AS id, SUM(t.punkte) AS punkte"+
  55. " FROM arzt a, dienst d, tag t"+
  56. " WHERE a.aktiv = 1"+
  57. " AND d.arzt_id = a.id"+
  58. " AND t.id = d.tag_id"+
  59. " GROUP BY id"+
  60. " ORDER BY a.name";
  61.  
  62. public static String diensteAktuellePeriode = "SELECT temp.datum, temp.aid, a.name, temp.punkte, temp.did "+
  63. "FROM arzt a, ( "+
  64. "SELECT t.punkte AS punkte, d.arzt_id AS aid, t.datum AS datum, d.tag_id AS did "+
  65. "FROM dienst d, tag t, periode p "+
  66. "WHERE p.aktiv = 1 "+
  67. "AND d.tag_id = t.id "+
  68. "AND t.datum >= start_datum "+
  69. "AND t.datum <= ende_datum "+
  70. ") AS temp "+
  71. "WHERE a.id = temp.aid "
  72. + "ORDER BY temp.datum";
  73.  
  74. public static String diensteLaufendePeriode = "SELECT temp.datum, temp.aid, a.name, temp.punkte, temp.did "+
  75. "FROM arzt a, ( "+
  76. "SELECT t.punkte AS punkte, d.arzt_id AS aid, t.datum AS datum, d.tag_id AS did "+
  77. "FROM dienst d, tag t, periode p ( "+
  78. "SELECT MAX(p.id) AS id "+
  79. "FROM periode p) AS temp1 "+
  80. "WHERE p.id = temp1.id-1 "+
  81. "AND d.tag_id = t.id "+
  82. "AND t.datum >= start_datum "+
  83. "AND t.datum <= ende_datum "+
  84. ") AS temp "+
  85. "WHERE a.id = temp.aid";
  86.  
  87. public static String diensteLaufendePeriodeZaehlen = "SELECT COUNT(temp.datum) "+
  88. "FROM arzt a, ( "+
  89. "SELECT t.punkte AS punkte, d.arzt_id AS aid, t.datum AS datum, d.tag_id AS did "+
  90. "FROM dienst d, tag t, periode p ( "+
  91. "SELECT MAX(p.id) AS id "+
  92. "FROM periode p) AS temp1 "+
  93. "WHERE p.id = temp1.id-1 "+
  94. "AND d.tag_id = t.id "+
  95. "AND t.datum >= start_datum "+
  96. "AND t.datum <= ende_datum "+
  97. ") AS temp "+
  98. "WHERE a.id = temp.aid";
  99.  
  100. public static String diensteAktuellePeriodeZaehlen = "SELECT COUNT(temp.datum)"+
  101. "FROM arzt a, ( "+
  102. "SELECT t.punkte AS punkte, d.arzt_id AS aid, t.datum AS datum "+
  103. "FROM dienst d, tag t, periode p "+
  104. "WHERE p.aktiv = 1 "+
  105. "AND d.tag_id = t.id "+
  106. "AND t.datum >= start_datum "+
  107. "AND t.datum <= ende_datum "+
  108. ") AS temp "+
  109. "WHERE a.id = temp.aid";
  110.  
  111. public static String statementAerzteZaehlen = "SELECT COUNT(a.id)"
  112. + " FROM arzt a";
  113.  
  114. public static String statementAerzteZaehlenAktive = "SELECT COUNT(a.id)"
  115. + " FROM arzt a"
  116. + " WHERE a.aktiv = 1";
  117.  
  118.  
  119.  
  120. Config(){
  121.  
  122.  
  123. }
  124.  
  125. public int gibPunkte(int aid){
  126. String statement= "SELECT a.wertung, temp.punkte, a.startpunkte "
  127. + "FROM arzt a, ( SELECT a.id AS id, SUM(t.punkte) AS punkte "
  128. + "FROM arzt a, tag t, dienst d "
  129. + "WHERE a.id = "+aid+" "
  130. + "AND d.arzt_id = a.id "
  131. + "AND t.id = d.tag_id) AS temp "
  132. + "WHERE a.id = temp.id";
  133. ResultSet ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  134. int i = 0;
  135. try {
  136. ergebnis.next();
  137. double wertung = Double.parseDouble(ergebnis.getString(1));
  138. i= (int) ((ergebnis.getInt(2)*wertung)+ergebnis.getInt(3));
  139. } catch (SQLException e) {
  140. // TODO Auto-generated catch block
  141. e.printStackTrace();
  142. }
  143. System.out.println(aid+" hat "+i+" Punkte");
  144. return i;
  145. }
  146.  
  147. public double durchschnittAktive(){
  148. String statement = Config.statementdienstpunkteDurchschnitt;
  149. ResultSet ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  150. double durchschnittDienste= 0.00;
  151. try {
  152. ergebnis.next();
  153. durchschnittDienste= ergebnis.getDouble(1);
  154. } catch (NumberFormatException e) {
  155. // TODO Auto-generated catch block
  156. e.printStackTrace();
  157. } catch (SQLException e) {
  158. // TODO Auto-generated catch block
  159. e.printStackTrace();
  160. }
  161. statement = "SELECT AVG(a.startpunkte) "+
  162. "FROM arzt a "
  163. +"WHERE a.aktiv = 1";
  164. ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  165. double durchschnittStartpunkte = 0.00;
  166. try {
  167. ergebnis.next();
  168. durchschnittStartpunkte = ergebnis.getDouble(1);
  169. } catch (SQLException e) {
  170. // TODO Auto-generated catch block
  171. e.printStackTrace();
  172. }
  173. return durchschnittDienste+durchschnittStartpunkte;
  174. }
  175.  
  176. public int[][] allePunkte(){
  177. String statement = this.statementArzttabelleAusgebenAktiv;
  178. ResultSet ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  179. statement=this.dienstpunkteNurAktive;
  180. ResultSet ergebnis1 = Main.getDbConnection().executeSelectQuery(statement);
  181. statement= this.statementAerzteZaehlenAktive;
  182. ResultSet anzahlSet = Main.getDbConnection().executeSelectQuery(statement);
  183. int anzahl=0;
  184. try {
  185. anzahlSet.next();
  186. anzahl=anzahlSet.getInt(1);
  187. System.out.println(anzahlSet.getInt(1));
  188. } catch (SQLException e) {
  189. // TODO Auto-generated catch block
  190. e.printStackTrace();
  191. }
  192. int[][] erg = new int[1][anzahl];
  193. int i = 0;
  194. try {
  195. while(ergebnis.next()&&ergebnis1.next()){
  196. erg[0][i]=(int) (ergebnis.getInt(10)+(ergebnis1.getInt(2)/ergebnis.getDouble(9)));
  197.  
  198. i++;
  199. }
  200. } catch (SQLException e) {
  201. // TODO Auto-generated catch block
  202. e.printStackTrace();
  203. }
  204. return erg;
  205. }
  206.  
  207. public ResultSet alleSpeziellen(String jahr){
  208. int jahrInt = Integer.parseInt(jahr);
  209. int minusFuenf = jahrInt-5;
  210. String statement = "SELECT a.id, t.datum, t.typ "
  211. + "FROM arzt a, tag t, dienst d "
  212. + "WHERE t.typ='Weihnachten' OR t.typ = 'Ostern' "
  213. + "AND t.datum>"+minusFuenf+"-01-01 "
  214. + "AND t.datum<"+jahrInt+"-01-01 "
  215. + "AND d.tag_id = t.id "
  216. + "AND a.id = d.arzt_id";
  217. ResultSet ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  218. return ergebnis;
  219. }
  220.  
  221. public List alleInaktiven(){
  222. String statement = "SELECT a.id "+
  223. "FROM arzt a "+
  224. "WHERE a.aktiv = 0 ";
  225. ResultSet ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  226. List l = new List();
  227. try {
  228. ergebnis.next();
  229. l.append(ergebnis.getInt(1));
  230. } catch (SQLException e) {
  231. // TODO Auto-generated catch block
  232. e.printStackTrace();
  233. }
  234. return l;
  235.  
  236. }
  237.  
  238. public String[] getNamenArray() {
  239. String statement= this.statementArzttabelleAusgebenAktiv;
  240. ResultSet ergebnis = Main.getDbConnection().executeSelectQuery(statement);
  241. statement= this.statementAerzteZaehlenAktive;
  242. ResultSet anzahlSet = Main.getDbConnection().executeSelectQuery(statement);
  243. int anzahl=0;
  244. try {
  245. anzahlSet.next();
  246. anzahl=anzahlSet.getInt(1);
  247. System.out.println(anzahlSet.getInt(1));
  248. } catch (SQLException e) {
  249. // TODO Auto-generated catch block
  250. e.printStackTrace();
  251. }
  252. String[] erg = new String[anzahl];
  253. int i= 0;
  254. try {
  255. while(ergebnis.next()){
  256. erg[i]=ergebnis.getString(3);
  257. i++;
  258. }
  259. } catch (SQLException e) {
  260. // TODO Auto-generated catch block
  261. e.printStackTrace();
  262. }
  263. return erg;
  264. }
  265.  
  266. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!