API tools faq
paste
Guest User

Untitled

a guest
Oct 19th, 2018
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 34.30 KB | None | 0 0
raw download clone embed print report
  1. import java.io.BufferedReader;
  2. import java.io.IOException;
  3. import java.io.InputStreamReader;
  4. import java.sql.Connection;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.util.regex.Matcher;
  9. import java.util.regex.Pattern;
  10.  
  11. /**
  12. *
  13. * @author Raul
  14. *
  15. */
  16. public class Methods {
  17.  
  18. Menus m;
  19. Musicians mu = new Musicians();
  20. AccesDB a = new AccesDB();
  21. private static BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
  22.  
  23. public void loginMenu() throws ClassNotFoundException, SQLException, IOException{
  24.  
  25. a.openConnection();
  26.  
  27.  
  28. m = new Menus();
  29. String[] i = { "Login","Login as a guest", "Exit"};
  30. m.setN(i);
  31. int op = 0;
  32. while(true){
  33. op = m.menu();
  34. switch(op){
  35. case 1: menuLevel();continue;
  36. case 2: continue;
  37. case 3: break;
  38. default: continue;
  39.  
  40. }
  41. break;
  42. }
  43. }
  44.  
  45.  
  46. private int login() throws IOException, SQLException, ClassNotFoundException {
  47. a.openConnection();
  48. String login,pass;
  49. int level = 0;
  50. while(true){
  51. login = getInputText("login");
  52. pass = getInputText("password");
  53. a.setUsers(login);
  54.  
  55. a.rs = a.st.executeQuery("SELECT * from users where user ='" + login +
  56. "' and password ='" + pass +"';");
  57. if(a.rs.first()){
  58. a.rs = a.st.executeQuery("SELECT * from users where user ='" + login +
  59. "' and password ='" + pass +"';");
  60. while(a.rs.next()){
  61. level = a.rs.getInt(3); a.rs.close();
  62. break;
  63. }
  64. break;
  65.  
  66. }else{
  67.  
  68. println("Incorrect User or Password, try again"); continue;
  69. }
  70.  
  71. }
  72. return level;
  73. }
  74.  
  75. public void menuLevel() throws IOException, SQLException, ClassNotFoundException{
  76. int op = 0;
  77. while(true){
  78. op = login();
  79. switch(op){
  80. case 1: System.out.println("hola"); break;
  81. case 2: System.out.println("hola"); break;
  82. case 3: representantMenu(); break;
  83. case 4: ; break;
  84. case 5: AdminMenu(); break;
  85. }
  86.  
  87. break;
  88. }
  89. }
  90.  
  91. public void representantMenu() throws ClassNotFoundException, SQLException, IOException{
  92.  
  93. Menus m = new Menus();
  94. String[] i = {"See all groups","See all musics", "Modify my group","Logout"};
  95. int op = 0;
  96. while(true){
  97. m.setN(i);
  98. op = m.menu();
  99. switch(op){
  100. case 1: listBands(1);continue;
  101. case 2: listMusics(1,0);continue;
  102. case 3: modifyGroupMenu();continue;
  103. case 4: break;
  104. default: continue;
  105. }
  106. break;
  107. }
  108. }
  109. public void AdminMenu() throws ClassNotFoundException, SQLException, IOException{
  110.  
  111. Menus m = new Menus();
  112. String[] i = {"Admin concerts","Admin bands","Admin musicians","Admin workers","Admin users","Logout"};
  113. int op = 0;
  114. while(true){
  115. m.setN(i);
  116. op = m.menu();
  117. switch(op){
  118. case 1: concertsMenu();continue;
  119. case 2: AdminGroupMenu();continue;
  120. case 3: AdminMusicMenu();continue;
  121. case 4: AdminWorkersMenu();continue;
  122. case 5: continue;
  123. case 6: break;
  124. default: continue;
  125. }
  126. break;
  127. }
  128. }
  129.  
  130. public void AdminWorkersMenu() throws ClassNotFoundException, SQLException, IOException{
  131. Menus m = new Menus();
  132. String[] i = {"See all Workers","See a worker","Modify Workers","Insert Workers","Delete Workers","Back"};
  133. int op = 0;
  134. while(true){
  135. m.setN(i);
  136. op = m.menu();
  137. switch(op){
  138. case 1: listWorkers(1);continue;
  139. case 2: listWorkers(2);continue;
  140. case 3: AdminModifyW();continue;
  141. case 4: AdminInsertsW();continue;
  142. case 5: AdminDeletesW();continue;
  143. case 6: break;
  144. default: continue;
  145. }
  146. break;
  147. }
  148. }
  149.  
  150. public void AdminUsersMenu() throws ClassNotFoundException, SQLException, IOException{
  151. Menus m = new Menus();
  152. String[] i = {"See all users","Modify user","Insert user","Grant level acces","Make representative","Back"};
  153. int op = 0;
  154. while(true){
  155. m.setN(i);
  156. op = m.menu();
  157. switch(op){
  158. case 1: listUsers();continue;
  159. case 2: AdminModifyU();continue;
  160. case 3: AdminModifyW();continue;
  161. case 4: AdminInsertsW();continue;
  162. case 5: AdminDeletesW();continue;
  163. case 6: break;
  164. default: continue;
  165. }
  166. break;
  167. }
  168. }
  169.  
  170. public void listUsers() throws SQLException, ClassNotFoundException{
  171. if(comproveExist("users")){
  172. a.openConnection();
  173. a.rs = a.st.executeQuery("SELECT * FROM users");
  174. while(a.rs.next()){
  175. int level = a.rs.getInt("level");
  176. String name = a.rs.getString("user");
  177. String pass = a.rs.getString("password");
  178. println("\n");
  179. println("\n -----------------" + "ID_USER: "+ name + " \n Password: " + pass + "\n Level: " + level + "\n -----------------");
  180. println("\n");
  181. }
  182. a.rs.close();
  183. a.closeConnection();
  184. }else{
  185. println("No users availables");
  186. }
  187. }
  188.  
  189. public void listWorkers(int v) throws SQLException, ClassNotFoundException{
  190. if(comproveExist("Workers")){
  191.  
  192. a.openConnection();
  193. if(v == 1){
  194. a.rs = a.st.executeQuery("SELECT id_worker,name,surname,phone,TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)," +
  195. "function,salary FROM Workers");
  196. }else if (v == 2){
  197. while(true){
  198. int f = getInputInt("the id fo the worker you want to see");
  199. if(comproveExistWhere("workers","id_worker",f)){
  200. a.rs =a.st.executeQuery("SELECT id_worker,name,surname,phone,TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)," +
  201. "function,salary FROM Workers where id_worker = " + f); break;
  202. }else{
  203. println("This worker doesn't exist, try again"); continue;
  204. }
  205. }
  206.  
  207. }
  208.  
  209. while(a.rs.next()){
  210. int id_music = a.rs.getInt("id_worker");
  211. String name = a.rs.getString("NAME");
  212. String surname = a.rs.getString("SURNAME");
  213. int phone = a.rs.getInt("PHONE");
  214. int old = a.rs.getInt("TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)");
  215. int salary =a.rs.getInt("salary");
  216. String funct = a.rs.getString("function");
  217.  
  218. println("\n");
  219. println("ID_WORKER: "+ id_music + "\n -----------------" + "\n NAME: " + name + " \n SURNAME: " + surname + "\n PHONE: " +
  220. phone + "\n YEARS OLD: " + old + "\n SALARY: " + salary + "\n FUNCTION " + funct + "\n -----------------");
  221. println("\n");
  222. }
  223. a.rs.close();
  224. a.closeConnection();
  225. }else{
  226. println("No workers availables to see");
  227. }
  228. }
  229.  
  230.  
  231. public void AdminMusicMenu() throws ClassNotFoundException, SQLException, IOException{
  232. Menus m = new Menus();
  233. String[] i = {"See all Musicians","See a Musician","Insert Musician","Delete Musician","Back"};
  234. int op = 0;
  235. while(true){
  236. m.setN(i);
  237. op = m.menu();
  238. switch(op){
  239. case 1: listMusics(1,0);continue;
  240. case 2: listMusics(3,0);continue;
  241. case 3: AdminInsertsM();continue;
  242. case 4: AdminDeletesM();continue;
  243. case 5: break;
  244. default: continue;
  245. }
  246. break;
  247. }
  248. }
  249. public void AdminDeletesM() throws ClassNotFoundException, SQLException{
  250.  
  251. listMusics(1,0);
  252. int b = 0;
  253. int id = getInputInt("ID of musician that you want to delete");
  254. a.openConnection();
  255. a.rs = a.st.executeQuery("Select * from bandmusic where id_music =" + id);
  256. if(a.rs.first()){
  257. while(a.rs.next()){
  258. b = a.rs.getInt("id_band");
  259. }
  260. a.openConnection();
  261. a.st.executeUpdate("DELETE from bandmusic where id_music =" + id);
  262. a.st.executeUpdate("DELETE from musicians where id_music =" + id);
  263.  
  264. a.st.executeUpdate("Update bands set num_comp = (num_comp - 1) where id_band = " + b );
  265. }else{
  266. a.st.executeUpdate("DELETE from musicians where id_music =" + id);
  267. }
  268. }
  269. public void AdminDeletesW() throws ClassNotFoundException, SQLException{
  270.  
  271. listWorkers(1);
  272. while(true){
  273.  
  274. int id = getInputInt("ID of Worker that you want to delete");
  275. if(comproveExistWhere("workers","id_worker",id)){
  276. a.openConnection();
  277. a.st.executeUpdate("DELETE from workers where id_worker =" + id);
  278. break;
  279. }else{
  280. println("This worker don't exist, try again"); continue;
  281. }
  282. }
  283.  
  284. }
  285.  
  286.  
  287. public void AdminInsertsW() throws ClassNotFoundException, SQLException, IOException{
  288. a.openConnection();
  289. String name;
  290. String surname;
  291. int phone;
  292. String age;
  293. String function;
  294. int salary;
  295.  
  296. name = getInputText("Name");
  297. surname = getInputText("Surname");
  298. phone = getInputInt("Phone");
  299. age = comproveDate("Born date(YYYY-MM-DD)");
  300. function = getInputText("FUNCTION");
  301. salary = getInputInt("Salary");
  302.  
  303. a.st.executeUpdate("Insert into workers (name,surname,phone,birth,salary,FUNCTION,user) values ('" + name + "','" + surname + "','"
  304. + phone + "','" + age + "'," + salary + " , '" + function + "' , NULL)");
  305.  
  306. }
  307.  
  308.  
  309.  
  310. public void AdminInsertsM() throws ClassNotFoundException, SQLException, IOException{
  311. a.openConnection();
  312. String name;
  313. String surname;
  314. int phone;
  315. String age;
  316. String instrument;
  317.  
  318. name = getInputText("Name");
  319. surname = getInputText("Surname");
  320. phone = getInputInt("Phone");
  321. age = comproveDate("Born date(YYYY-MM-DD)");
  322. instrument = getInputText("Instrument");
  323. a.st.executeUpdate("Insert into musicians (name,surname,phone,birth,instrument,user) values ('" + name + "','" + surname + "','"
  324. + phone + "','" + age + "','" + instrument + "',NULL)");
  325. while(true){
  326. println("Want to add this musician into a band?");
  327. String s = getInputText("Yes or No").toLowerCase();
  328. if(s.equals("yes")){
  329. while(true){
  330. a.openConnection();
  331. int b = getInputInt("the id of the band where you want to add the musician");
  332. if(comproveExistWhere("bands","id_band",b)){
  333. a.openConnection();
  334. a.rs = a.st.executeQuery("select id_music from musicians where name = '" + name + "' and surname = '" + surname + "' and phone = '" + phone + "'");
  335. while(a.rs.next()){
  336. int id_music = a.rs.getInt(1);
  337. a.st.executeUpdate("Insert into bandmusic values (" + b + "," + id_music + ")");
  338. a.st.executeUpdate("Update bands set num_comp = (num_comp + 1) where id_band = " + b);
  339. break;
  340. }
  341. break;
  342. }else{
  343. println("This id of band don't exist. Try again"); continue;
  344. }
  345. }
  346. break;
  347. }else if(s.equals("no")){
  348. break;
  349. }else{
  350. println("Enter only YES or NO"); continue;
  351. }
  352.  
  353. }
  354.  
  355. }
  356.  
  357. public void AdminGroupMenu() throws ClassNotFoundException, SQLException, IOException {
  358. Menus m = new Menus();
  359. String[] i = {"See all groups","See a group", "Modify info of a group","Insert a new Member"
  360. ,"Delete a member","Back"};
  361. int op = 0;
  362. while(true){
  363. m.setN(i);
  364. op = m.menu();
  365. switch(op){
  366. case 1: listBands(1);continue;
  367. case 2: listBands(3);continue;
  368. case 3: modifyInfoGroup('a');continue;
  369. case 4: MenuInsertMember();continue;
  370. case 5: MenuDeleteMember();continue;
  371. case 6: break;
  372. default: continue;
  373. }
  374. break;
  375. }
  376. }
  377.  
  378. public void concertsMenu() throws ClassNotFoundException, SQLException, IOException{
  379. Menus m = new Menus();
  380. String[] i = {"Add concert","Modify Concert","Delete Concert","See all concerts","Back"};
  381. int op = 0;
  382. while(true){
  383. m.setN(i);
  384. op = m.menu();
  385. switch(op){
  386. case 1: Inserts('c');continue;
  387. case 2: MenuModifyConcerts();continue;
  388. case 3: Deletes('c');continue;
  389. case 4: listConcerts();continue;
  390. case 5: break;
  391. default: continue;
  392. }
  393. break;
  394. }
  395. }
  396.  
  397. public void MenuModifyConcerts() throws ClassNotFoundException, SQLException, IOException{
  398. if(comproveExist("concerts")){
  399. Menus m = new Menus();
  400. String[] i = {"Modify Band","Modify Hall","Modify Price","Modify Date and Hour","Modify Attendance","Back"};
  401. m.setN(i);
  402. int op = 0;
  403. while(true){
  404. m.setN(i);
  405. op = m.menu();
  406. switch(op){
  407. case 1: ModifysConcert('b');continue;
  408. case 2: ModifysConcert('h');continue;
  409. case 3: ModifysConcert('p');continue;
  410. case 4: ModifysConcert('d');continue;
  411. case 5: ModifysConcert('a');continue;
  412. case 6: break;
  413. default: continue;
  414. }
  415. break;
  416. }
  417. }else{
  418. println("Don't are any concert to modify, turning back");
  419. }
  420.  
  421. }
  422. public void AdminModifyW() throws ClassNotFoundException, SQLException, IOException{
  423. if(comproveExist("workers")){
  424. Menus m = new Menus();
  425. String[] i = {"Modify Name","Modify surname","Modify phone ","Modify birth","Modify function","Modify Salary","Back"};
  426. m.setN(i);
  427. int op = 0;
  428. while(true){
  429. m.setN(i);
  430. op = m.menu();
  431. switch(op){
  432. case 1: ModifysWorkers('n');continue;
  433. case 2: ModifysWorkers('s');continue;
  434. case 3: ModifysWorkers('p');continue;
  435. case 4: ModifysWorkers('b');continue;
  436. case 5: ModifysWorkers('f');continue;
  437. case 6: ModifysWorkers('$');continue;
  438. case 7: break;
  439. default: continue;
  440. }
  441. break;
  442. }
  443. }else{
  444. println("Don't are any concert to modify, turning back");
  445. }
  446.  
  447. }
  448.  
  449. public void ModifysWorkers(char m) throws ClassNotFoundException, SQLException, IOException {
  450. a.openConnection();
  451. listWorkers(1);
  452. while(true){
  453.  
  454. int s = getInputInt("the id of the member you want to modify");
  455. if(comproveExistWhere("workers","id_worker",s)){
  456. if(m == 'n'){
  457. String name = getInputText("the new name");
  458. a.st.executeUpdate("update workers set name = '" + name + "' where id_worker = " + s);
  459. }else if(m == 's'){
  460. String surname = getInputText("the new surname");
  461. a.st.executeUpdate("update workers set surname = '" + surname + "' where id_worker = " + s);
  462. }else if(m == 'p'){
  463. int phone = getInputInt("the new phone");
  464. a.st.executeUpdate("update workers set phone =" + phone + " where id_worker = " + s);
  465. }else if(m == 'b'){
  466. String birth = comproveDate("New birth date(YYYY-MM-DD)");
  467. a.st.executeUpdate("update workers set birth ='" + birth +"' where id_worker = " + s);
  468. }else if(m == 'f'){
  469. String funct = getInputText("the new function");
  470. a.st.executeUpdate("update workers set function = '" + funct + "' where id_worker = " + s);
  471. }else if(m == '$'){
  472. int salary = getInputInt("the new salary");
  473. a.st.executeUpdate("update workers set salary = " + salary + " where id_worker = " + s);
  474. }
  475. }else{
  476. println("This worker don't exist, try again"); continue;
  477. }
  478. break;
  479. }
  480.  
  481. }
  482.  
  483. public void AdminInsertB(char o) throws IOException, SQLException, ClassNotFoundException{
  484. if(o == 'n'){
  485. a.openConnection();
  486. int b = getInputInt("the id of the group where you want to add the member");
  487. String name = getInputText("Name");
  488. String surname = getInputText("Surname");
  489. int phone = getInputInt("Phone");
  490. String age = comproveDate("Born date(YYYY-MM-DD)");
  491. String instrument = getInputText("Instrument");
  492. a.st.executeUpdate("Insert into musicians (name,surname,phone,birth,instrument,user) values ('" + name + "','" + surname + "','"
  493. + phone + "','" + age + "','" + instrument + "',NULL)");
  494. a.rs = a.st.executeQuery("select id_music from musicians where name = '" + name + "' and surname = '" + surname + "' and phone = '" + phone + "'");
  495. while(a.rs.next()){
  496. int id_music = a.rs.getInt(1);
  497. a.st.executeUpdate("Insert into bandmusic values (" + b + "," + id_music + ")");
  498. a.st.executeUpdate("Update bands set num_comp = (num_comp + 1) where id_band = " + b);
  499. break;
  500. }
  501. }else if (o =='e'){
  502. a.openConnection();
  503. listMusics(1,0);
  504. int b = getInputInt("the id of the group where you want to add the member");
  505. int m = getInputInt("The id of the music that you want to add to the band");
  506. a.openConnection();
  507. a.rs = a.st.executeQuery("select * from bandmusic where id_band =" + b + " and id_music = " + m);
  508. if(a.rs.first()){
  509. println("This musician is already part of this group.");
  510. }else{
  511. a.st.executeUpdate("Insert into bandmusic values (" + b + "," + m + ")");
  512. a.st.executeUpdate("Update bands set num_comp = (num_comp + 1) where id_band = " + b);
  513. }
  514. }
  515.  
  516. }
  517.  
  518. public void AdminDelete1(char o) throws ClassNotFoundException, SQLException{
  519. a.openConnection();
  520. if(o == 'd'){
  521. int b = 0;
  522. listMusics(1,0);
  523. a.openConnection();
  524. int id = getInputInt("ID from member you want to delete");
  525. a.rs = a.st.executeQuery("select id_band from bandmusic where id_music = " + id);
  526. while(a.rs.next()){
  527. b = a.rs.getInt(1);
  528. }
  529. a.openConnection();
  530. a.st.executeUpdate("DELETE from bandmusic where id_music =" + id);
  531. a.st.executeUpdate("DELETE from musicians where id_music =" + id);
  532. a.st.executeUpdate("Update bands set num_comp = (num_comp - 1) where id_band = " + b);
  533. }else if(o == 'n'){
  534. a.openConnection();
  535. int b = 0;
  536. int id = getInputInt("ID from member you want to delete");
  537. a.rs = a.st.executeQuery("select id_band from bandmusic where id_music = " + id);
  538. while(a.rs.next()){
  539. b = a.rs.getInt(1);
  540. }
  541. a.st.executeUpdate("Update bands set num_comp = (num_comp - 1) where id_band = " + b);
  542. a.st.executeUpdate("DELETE from bandmusic where id_music =" + id);
  543. }
  544.  
  545. }
  546. public void MenuInsertMember() throws ClassNotFoundException, IOException, SQLException{
  547. Menus m = new Menus();
  548. String[] i = {"Insert New Member","Add Existing Music to a group","Back"};
  549. int op = 0;
  550. while(true){
  551. m.setN(i);
  552. op = m.menu();
  553. switch(op){
  554. case 1: AdminInsertB('n');continue;
  555. case 2: AdminInsertB('e');continue;
  556. case 3: break;
  557. default: continue;
  558. }
  559. break;
  560. }
  561.  
  562. }
  563. public void MenuDeleteMember() throws ClassNotFoundException, IOException, SQLException{
  564. Menus m = new Menus();
  565. String[] i = {"Delete a member of a group and all his information","Delete a member from a group but not his information","Back"};
  566. int op = 0;
  567. while(true){
  568. m.setN(i);
  569. op = m.menu();
  570. switch(op){
  571. case 1: AdminDelete1('d');continue;
  572. case 2: AdminDelete1('n');continue;
  573. case 3: break;
  574. default: continue;
  575. }
  576. break;
  577. }
  578.  
  579. }
  580.  
  581. public void ModifysConcert(char m) throws ClassNotFoundException, SQLException, IOException{
  582.  
  583. listConcerts();
  584. int concert;
  585. while(true){
  586. a.openConnection();
  587. concert = getInputInt("the id of the concert that you want to modify");
  588. a.rs = a.st.executeQuery("Select * from concerts where id_concert = " + concert);
  589. if(a.rs.first()){
  590. if(m == 'b'){
  591. while(true){
  592. a.openConnection();
  593. int band = getInputInt("the id of the band");
  594. a.rs = a.st.executeQuery("select * from bands where id_Band =" + band);
  595. if(a.rs.first()){
  596. a.openConnection();
  597. a.st.executeUpdate("update concerts set id_band = " + band + " where id_Concert = " + concert);
  598. break;
  599. }else{
  600. println("The id that you entered is not corret or don't exist. Try again"); continue;
  601. }
  602. }
  603. }else if(m == 'h'){
  604.  
  605. while(true){
  606. a.openConnection();
  607. String hall = getInputText("the id of hall");
  608. a.rs = a.st.executeQuery("Select * from halls where id_hall = '" + hall + "'");
  609. if(a.rs.first()){
  610. a.openConnection();
  611. a.st.executeUpdate("update concerts set id_hall = '" + hall + "' where id_concert =" + concert);
  612. break;
  613. }else{
  614. println("The id that you entered is not corret or don't exist. Try again"); continue;
  615. }
  616. }
  617. }else if(m == 'p'){
  618. a.openConnection();
  619. int price = getInputInt("the price that you want");
  620. a.st.executeUpdate("update concerts set price = " + price + " where id_concert = " + concert);
  621. if(price == 0){
  622. a.openConnection();
  623. a.st.executeUpdate("update concerts set free = true where id_concert = " + concert);
  624. }else{
  625. a.openConnection();
  626. a.st.executeUpdate("update concerts set free = false where id_concert = " + concert);
  627. }
  628. }else if(m == 'd'){
  629. a.openConnection();
  630. String date = comproveDate("Insert the new date(YYYY-MM-DD)");
  631. String hour = comproveHour("Insert the new time(HH:MM)");
  632. a.st.executeUpdate("update concerts set date = '" + date +"', time = '" + hour + "' where id_concert = " + concert);
  633. }else if(m == 'a'){
  634. while(true){
  635. a.openConnection();
  636. int aten = getInputInt("How many tickets you want to reserve");
  637. int cap = 0;
  638. a.rs =a.st.executeQuery("Select * from halls h, concerts c where h.id_hall = c.id_hall and id_concert =" + concert);
  639. while(a.rs.next()){
  640. cap = a.rs.getInt(2);
  641. break;
  642. }
  643. a.openConnection();
  644. int aten1 = 0 ;
  645. a.rs = a.st.executeQuery("Select attendance from concerts where id_concert =" + concert);
  646. while(a.rs.next()){
  647. aten1= a.rs.getInt(1);
  648. break;
  649. }
  650. if((aten1 + aten) > cap){
  651. println("There are not enough tickets available. Put a smaller amount or Put 0 to exit."); continue;
  652. }else if(aten == 0){
  653. break;
  654.  
  655. }else{
  656. a.openConnection();
  657. a.st.executeUpdate("Update concerts set attendance = (attendance + " + aten + ") where id_concert = " + concert);
  658. break;
  659. }
  660.  
  661. }
  662. }
  663. break;
  664. }else{
  665. println("Incorrect id of concert. Please try again"); continue;
  666. }
  667. }
  668.  
  669. }
  670.  
  671.  
  672. public void listConcerts() throws ClassNotFoundException, SQLException{
  673. if(comproveExist("concerts")){
  674. a.openConnection();
  675. a.rs = a.st.executeQuery("Select * from concerts");
  676. while(a.rs.next()){
  677. int id_concert = a.rs.getInt("ID_CONCERT");
  678. int id_band = a.rs.getInt("ID_BAND");
  679. String id_hall = a.rs.getString("id_hall");
  680. int price = a.rs.getInt("price");
  681. String date = a.rs.getString("date");
  682. String time = a.rs.getString("time");
  683. int attendance = a.rs.getInt("ATTENDANCE");
  684. println("\n");
  685. println("ID_CONCERT: " + id_concert + "\n ----------------- "+ "\n ID_BAND: " + id_band + "\n HALL: " + id_hall
  686. + "\n PRICE: " + price + "\n DATE: " + date + "\n TIME: " + time + " \n ATTENDANCE: " + attendance + "\n -----------------");
  687. println("\n");
  688. }
  689. a.rs.close();
  690. a.closeConnection();
  691. }else{
  692. println("No concerts to show");
  693. }
  694. }
  695.  
  696. public void modifyGroupMenu() throws ClassNotFoundException, SQLException, IOException {
  697. Menus m = new Menus();
  698. String[] i = {"See my group","See members of my group", "Modify info of my group","Modify info of a member",
  699. "Insert a new Member","Delete a member","Back"};
  700. int op = 0;
  701. while(true){
  702. m.setN(i);
  703. op = m.menu();
  704. switch(op){
  705. case 1: listBands(2);continue;
  706. case 2: listMusics(2,getIdBand());continue;
  707. case 3: modifyInfoGroup('r');continue;
  708. case 4: modifyInfoMusician();continue;
  709. case 5: Inserts('m');continue;
  710. case 6: Deletes('m');continue;
  711. case 7: break;
  712. default: continue;
  713. }
  714. break;
  715. }
  716. }
  717.  
  718. /**
  719. *
  720. * @param s
  721. * @throws ClassNotFoundException
  722. * @throws SQLException
  723. */
  724.  
  725. public void modifyInfoGroup(char s) throws ClassNotFoundException, SQLException{
  726. listBands(2);
  727.  
  728. Menus m = new Menus();
  729. String[] i = {"Modify Name","Modify Style","Modify Representative","Back"};
  730. m.setN(i);
  731. int op = 0;
  732. while(true){
  733. m.setN(i);
  734. op = m.menu();
  735. switch(op){
  736. case 1: ModifysBand('n',s);continue;
  737. case 2: ModifysBand('s',s);continue;
  738. case 3: ModifysBand('r',s);continue;
  739. case 4: break;
  740. default: continue;
  741. }
  742. break;
  743. }
  744.  
  745. }
  746. public void modifyInfoMusician() throws ClassNotFoundException, SQLException, IOException{
  747. Menus m = new Menus();
  748. String[] i = {"Modify name","Modify surname","Modify phone","Modify birth date","Back"};
  749. int op = 0;
  750. while(true){
  751. m.setN(i);
  752. op = m.menu();
  753. switch(op){
  754. case 1: ModifysMusic('n');continue;
  755. case 2: ModifysMusic('s');continue;
  756. case 3: ModifysMusic('p');continue;
  757. case 4: ModifysMusic('b');continue;
  758. case 5: break;
  759. default: continue;
  760. }
  761. break;
  762. }
  763.  
  764. }
  765.  
  766.  
  767. public void listBands(int v) throws SQLException, ClassNotFoundException{
  768. a.openConnection();
  769. if(v == 1){
  770. a.rs = a.st.executeQuery("SELECT * FROM bands");
  771. }else if (v == 2){
  772. int b = getIdBand();
  773. a.rs = a.st.executeQuery("select * from bands where id_band =" + b );
  774. }else if (v == 3){
  775. while(true){
  776. int b = getInputInt("the id of the group you want to see");
  777. if(comproveExistWhere("bands","id_band",b)){
  778. listMusics(2,b);
  779. a.openConnection();
  780. a.rs = a.st.executeQuery("select * from bands where id_band =" + b);
  781. break;
  782. }else{
  783. println("The id that you entered don't exist. Try again"); continue;
  784. }
  785. }
  786.  
  787. }
  788.  
  789.  
  790. while(a.rs.next()){
  791. int id_band = a.rs.getInt("ID_BAND");
  792. String name = a.rs.getString("NAME");
  793. int representative = a.rs.getInt("REPRESENTATIVE");
  794. int num_comp = a.rs.getInt("NUM_COMP");
  795. String style = a.rs.getString("STYLE");
  796. println("\n");
  797. println("ID_BAND: " + id_band + "\n ----------------- "+ "\n NAME: " + name + "\n REPRESENTATIVE: " + representative
  798. + "\n Number of members: " + num_comp + "\n Style: " + style + "\n -----------------");
  799. println("\n");
  800. }
  801. a.rs.close();
  802. a.closeConnection();
  803.  
  804. }
  805.  
  806. public void listMusics(int v,int b) throws ClassNotFoundException, SQLException{
  807. a.openConnection();
  808. if(v == 1){
  809. a.rs = a.st.executeQuery("SELECT id_music,name,surname,phone,TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)," +
  810. "instrument FROM musicians");
  811. }else if (v == 2){
  812. a.rs =a.st.executeQuery("SELECT m.id_music,name,surname,phone,TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)," +
  813. "instrument FROM musicians m,bandmusic bm where m.id_music = bm.id_music and bm.id_band =" + b );
  814. }else if (v == 3) {
  815. int m =getInputInt("Enter the id of the Musician that want to see");
  816. if(comproveExistWhere("musicians","id_music",m)){
  817. a.rs =a.st.executeQuery("SELECT id_music,name,surname,phone,TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)," +
  818. "instrument FROM musicians m where id_music =" + m );
  819. }else{
  820. println("That musician don't exist");
  821. }
  822.  
  823. }
  824. while(a.rs.next()){
  825. int id_music = a.rs.getInt("ID_MUSIC");
  826. String name = a.rs.getString("NAME");
  827. String surname = a.rs.getString("SURNAME");
  828. int phone = a.rs.getInt("PHONE");
  829. int old = a.rs.getInt("TRUNCATE(DATEDIFF(CURDATE(), birth)/365.25, 0)");
  830. String instrument =a.rs.getString("INSTRUMENT");
  831.  
  832. println("\n");
  833. println("ID_MUSIC: "+ id_music + "\n -----------------" + "\n NAME: " + name + " \n SURNAME: " + surname + "\n PHONE: " +
  834. phone + "\n YEARS OLD: " + old + "\n INSTRUMENT: " + instrument + "\n -----------------");
  835. println("\n");
  836. }
  837. a.rs.close();
  838. a.closeConnection();
  839. }
  840.  
  841. private int getIdBand() throws ClassNotFoundException, SQLException{
  842. a.openConnection();
  843. int id = 0;
  844.  
  845. String user = a.getUsers();
  846. a.rs = a.st.executeQuery("Select b.id_band from bands b, musicians m where" +
  847. " m.id_music = b.representative and m.user = '" + user + "';");
  848. while (a.rs.next()){
  849. id = a.rs.getInt("ID_BAND");
  850. }
  851.  
  852. return id;
  853.  
  854. }
  855.  
  856.  
  857.  
  858.  
  859. public void ModifysBand(char m,char s) throws ClassNotFoundException, SQLException{
  860. if(s == 'r'){
  861. a.openConnection();
  862. if(m == 'n'){
  863. String name = getInputText("the new name");
  864. a.st.executeUpdate("update bands set name = '" + name + "' where id_band = " + getIdBand());
  865. }else if(m == 's'){
  866. String style = getInputText("the new style");
  867. a.st.executeUpdate("update bands set style = '" + style + "' where id_band =" + getIdBand());
  868. }else if(m == 'r'){
  869. println("Only admins are allowed to do it");
  870. }
  871. }else if(s == 'a'){
  872. a.openConnection();
  873. while(true){
  874. int b = getInputInt("the id of the group that you want to modify");
  875. if(comproveExistWhere("bands","id_band",b)){
  876. if(m == 'n'){
  877. String name = getInputText("the new name");
  878. a.st.executeUpdate("update bands set name = '" + name + "' where id_band = " + b);
  879. break;
  880. }else if(m == 's'){
  881. String style = getInputText("the new style");
  882. a.st.executeUpdate("update bands set style = '" + style + "' where id_band =" + b);
  883. break;
  884. }else if(m == 'r'){
  885. listMusics(2,b);
  886. int r = 0;
  887. String u = null;
  888. int id = getInputInt("the id of the new presentative");
  889. a.openConnection();
  890. a.rs = a.st.executeQuery("select representative from bands where id_band = " + b);
  891. while(a.rs.next()){
  892. r = a.rs.getInt(1);
  893. }
  894. a.openConnection();
  895. a.rs = a.st.executeQuery("select user from musicians where id_music = " + r );
  896. while(a.rs.next()){
  897. u = a.rs.getString(1);
  898. }
  899. a.openConnection();
  900. a.st.executeUpdate("update musicians set user = null where id_music =" + r);
  901. a.openConnection();
  902. a.st.executeUpdate("Delete from users where user = '" + u + "'");
  903.  
  904.  
  905. a.openConnection();
  906. a.rs = a.st.executeQuery("Select * from bandmusic,bands b where b.id_band = " + b + " and id_music = " + id);
  907. if(a.rs.first()){
  908. a.openConnection();
  909. a.st.executeUpdate("update bands set REPRESENTATIVE = " + id +" where id_band =" + b);
  910. String v = CreateUser(3);
  911. a.openConnection();
  912. a.st.executeUpdate("update musicians set user = '" + v + "' where id_music = " + id);
  913. }
  914. break;
  915.  
  916. }
  917. }else{
  918. println("That id of band don't exist");continue;
  919. }
  920. }
  921. }
  922. }
  923.  
  924. public String CreateUser(int l) throws SQLException, ClassNotFoundException{
  925. String u = getInputText("the user");
  926. String p = getInputText("the password");
  927. a.openConnection();
  928. a.st.executeUpdate("Insert into users values ('" + u + "' , '" + p + "'," + l + ")");
  929. return u;
  930. }
  931.  
  932.  
  933. public void ModifysMusic(char m) throws ClassNotFoundException, SQLException, IOException {
  934. a.openConnection();
  935. int s = getInputInt("the id of the member you want to modify");
  936. if(m == 'n'){
  937. String name = getInputText("the new name");
  938. a.st.executeUpdate("update musicians set name = '" + name + "' where id_music = " + s);
  939. }else if(m == 's'){
  940. String surname = getInputText("the new surname");
  941. a.st.executeUpdate("update musicians set surname = '" + surname + "' where id_music = " + s);
  942. }else if(m == 'p'){
  943. int phone = getInputInt("the new phone");
  944. a.st.executeUpdate("update musicians set phone =" + phone + " where id_music = " + s);
  945. }else if(m == 'b'){
  946. String birth = comproveDate("New birth date(YYYY-MM-DD)");
  947. a.st.executeUpdate("update musicians set birth ='" + birth +"' where id_music = " + s);
  948. }
  949.  
  950. }
  951.  
  952. public void Inserts(char o) throws IOException, SQLException, ClassNotFoundException{
  953. a.openConnection();
  954. String name;
  955. String surname;
  956. int phone;
  957. String age;
  958. String instrument;
  959.  
  960. if(o == 'm'){
  961. name = getInputText("Name");
  962. surname = getInputText("Surname");
  963. phone = getInputInt("Phone");
  964. age = comproveDate("Born date(YYYY-MM-DD)");
  965. instrument = getInputText("Instrument");
  966. a.st.executeUpdate("Insert into musicians (name,surname,phone,birth,instrument,user) values ('" + name + "','" + surname + "','"
  967. + phone + "','" + age + "','" + instrument + "',NULL)");
  968. a.rs = a.st.executeQuery("select id_music from musicians where name = '" + name + "' and surname = '" + surname + "' and phone = '" + phone + "'");
  969. while(a.rs.next()){
  970. int id_music = a.rs.getInt(1);
  971. a.st.executeUpdate("Insert into bandmusic values (" + getIdBand() + "," + id_music + ")");
  972. a.st.executeUpdate("Update bands set num_comp = (num_comp + 1) where id_band = " + getIdBand());
  973. break;
  974. }
  975. }else if( o == 'c'){
  976. int id_band = getInputInt("ID of the band");
  977. String id_hall = getInputText("ID of the hall");
  978. int price = getInputInt("Price of the concert");
  979. String date = comproveDate("Date of the concert(YYYY-MM-DD)");
  980. String time = comproveHour("time when the concert starts(HH:MM)");
  981. boolean free;
  982. if(price > 0){
  983. free = false;
  984. }else{
  985. free = true;
  986. }
  987. a.st.executeUpdate("insert into concerts(id_band,id_hall,free,price,date,time) values (" + id_band +",'" + id_hall
  988. + "'," + free +"," + price + ",'" + date + "','" + time + "')" );
  989. }
  990. a.closeConnection();
  991.  
  992. }
  993.  
  994. private void Deletes(char o) throws SQLException, ClassNotFoundException{
  995. a.openConnection();
  996. if(o == 'm'){
  997.  
  998. listMusics(2,getIdBand());
  999. a.openConnection();
  1000. while(true){
  1001.  
  1002. int id = getInputInt("ID from member you want to delete");
  1003. if(comproveExistWhere("musicians","id_music", id)){
  1004. if(comproveExistWhere2("bandmusic","id_music",id,"id_band",getIdBand())){
  1005. a.openConnection();
  1006. a.st.executeUpdate("Update bands set num_comp = (num_comp - 1) where id_band = " + getIdBand());
  1007. a.st.executeUpdate("DELETE from bandmusic where id_music =" + id);
  1008. break;
  1009. }else{
  1010. println("That musiciant aren't part of your group, try again"); continue;
  1011. }
  1012. }else{
  1013. println("That member don't exist, try again"); continue;
  1014. }
  1015.  
  1016. }
  1017.  
  1018. }else if(o == 'c'){
  1019. while(true){
  1020. int id = getInputInt("ID from concert you want to delete");
  1021. if(comproveExistWhere("concerts","id_concert",id)){
  1022. a.st.executeUpdate("DELETE from concerts where id_concert =" + id);
  1023. break;
  1024. }else{
  1025. println("That concert don't exist, try again.");continue;
  1026. }
  1027. }
  1028.  
  1029. }
  1030. }
  1031.  
  1032.  
  1033.  
  1034. private String comproveDate(String g) throws IOException{
  1035. while (true) {
  1036. println(g);
  1037. String f = br.readLine().toLowerCase();
  1038. Pattern pat = Pattern.compile("^([0-9]{4}-(0[0-9]|10|11|12)-([0-2][0-9]|[3][0-1]))");
  1039. Matcher m = pat.matcher(f);
  1040. if (!m.matches()) {
  1041. println("Error try again(YYYY-MM-DD)"); continue;
  1042. } else {
  1043. return f;
  1044. }
  1045. }
  1046. }
  1047. private String comproveHour(String g) throws IOException{
  1048. while (true) {
  1049. println(g);
  1050. String f = br.readLine().toLowerCase();
  1051. Pattern pat = Pattern.compile("^([0-2][0-9]:[1-6][0-9])");
  1052. Matcher m = pat.matcher(f);
  1053. if (!m.matches()) {
  1054. println("Error try again(HH:MM)"); continue;
  1055. } else {
  1056. return f;
  1057. }
  1058. }
  1059. }
  1060.  
  1061. private String getInputText(String quest){
  1062.  
  1063. String aux = "";
  1064. while(true){
  1065. System.out.println("Enter "+ quest + ":");
  1066. try {
  1067. aux = br.readLine();
  1068. if (aux.isEmpty()){
  1069. continue;
  1070. }else{
  1071. break;
  1072. }
  1073. } catch (IOException e) {
  1074.  
  1075. e.printStackTrace();;
  1076. }
  1077. }
  1078. return aux;
  1079. }
  1080.  
  1081.  
  1082. private int getInputInt(String quest){
  1083.  
  1084. int aux;
  1085. String s;
  1086. while(true){
  1087. System.out.println("Enter "+ quest + ":");
  1088. try {
  1089. s = br.readLine();
  1090. if (s.isEmpty()){
  1091. continue;
  1092. }else{
  1093. aux = Integer.parseInt(s);
  1094. break;
  1095.  
  1096. }
  1097. } catch (IOException e) {
  1098.  
  1099. e.printStackTrace();
  1100. } catch (NumberFormatException e1){
  1101. println("Insert only a number");
  1102. continue;
  1103. }
  1104. }
  1105. return aux;
  1106. }
  1107.  
  1108.  
  1109. public boolean comproveExist(String t) throws SQLException, ClassNotFoundException{
  1110. a.openConnection();
  1111. a.rs = a.st.executeQuery("Select * from " + t);
  1112. if(a.rs.first()){
  1113. return true;
  1114. }else{
  1115. return false;
  1116. }
  1117. }
  1118. public boolean comproveExistWhere(String t,String c,int c1) throws SQLException, ClassNotFoundException{
  1119. a.openConnection();
  1120. a.rs = a.st.executeQuery("Select * from " + t + " where " + c +" = " + c1 );
  1121. if(a.rs.first()){
  1122. return true;
  1123. }else{
  1124. return false;
  1125. }
  1126. }
  1127. public boolean comproveExistWhere2(String t,String c,int c1, String d, int d1) throws SQLException, ClassNotFoundException{
  1128. a.openConnection();
  1129. a.rs = a.st.executeQuery("Select * from " + t + " where " + c +" = " + c1 + " and " + d + " = " + d1);
  1130. if(a.rs.first()){
  1131. return true;
  1132. }else{
  1133. return false;
  1134. }
  1135. }
  1136.  
  1137. private void println(String f){
  1138. System.out.println(f);
  1139.  
  1140. }
  1141. }
Add Comment
Please, Sign In to add comment
Public Pastes
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!