· 9 years ago · Dec 04, 2016, 06:52 AM
1package pht;
2
3import javafx.collections.FXCollections;
4import javafx.collections.ObservableList;
5import javafx.scene.control.Alert;
6import javafx.scene.control.ButtonType;
7import javafx.scene.control.Label;
8import javafx.scene.control.TextArea;
9import javafx.scene.layout.GridPane;
10import javafx.scene.layout.Priority;
11
12import java.io.*;
13import java.sql.*;
14import java.time.LocalDate;
15import java.util.Optional;
16import java.util.Scanner;
17
18/**
19 * Created by discovered on 6/15/16.
20 */
21public class Common {
22 private Connection connection;
23 public ObservableList<String> observableList= FXCollections.observableArrayList();
24 public ObservableList<String> observableList2= FXCollections.observableArrayList();
25 public ObservableList<String> monthList = FXCollections.observableArrayList("January","February",
26 "March","Appril","May","June",
27 "July","August","September","October","November","December");
28 public int getID = 0;
29 private String sqlQuery = null;
30 ResultSet resultSet = null;
31 PreparedStatement preparedStatement = null;
32 public String loginFailed = null;
33 public String info = null;
34 public int getMonth;
35 LocalDate localDate = LocalDate.now();
36 public int year = localDate.getYear();
37 public int month=localDate.getMonthValue();
38 String curDate= localDate.getDayOfMonth()+"-"+localDate.getMonthValue()+"-"+localDate.getYear();
39 String thisMonth = localDate.getMonthValue()+"-"+localDate.getYear();
40 ObservableList<Integer> yearList = FXCollections.observableArrayList();
41 ObservableList<Integer> daysList = FXCollections.observableArrayList();
42
43 public void Items(){
44 sqlQuery = "SELECT PAYTYPE,QUICKSELECT FROM FUNC";
45
46 try {
47 connection = SqlConnect.con();
48 preparedStatement = connection.prepareStatement(sqlQuery);
49 resultSet = preparedStatement.executeQuery();
50
51 while (resultSet.next()){
52 observableList.add(resultSet.getString(1));
53 observableList2.add(resultSet.getString(2));
54
55 System.out.println(resultSet.getString(1));
56 }
57
58 }
59 catch (SQLException e){
60 return;
61 } catch (ClassNotFoundException e) {
62 e.printStackTrace();
63 }
64
65 finally {
66 try {
67 //preparedStatement.close();
68 //resultSet.close();
69 connection.close();
70 } catch (SQLException e) {
71 e.printStackTrace();
72 }
73 }
74 }
75
76 public boolean SetupDB() throws ClassNotFoundException, SQLException {
77
78 String dropTransaction = "DROP TABLE IF EXISTS `transaction`;";
79 String dropfunc = "DROP TABLE IF EXISTS `func`;";
80 String dropcurrency = "DROP TABLE IF EXISTS `currency`;";
81 //String droplogin = "DROP TABLE IF EXISTS `login`;";
82
83
84 String transaction = "CREATE TABLE TRANSACTION \n" +
85 "(\n" +
86 " ID IDENTITY,\n" +
87 " MTCN CLOB ,\n" +
88 " NAME CLOB,\n" +
89 " COUNTRY CLOB,\n" +
90 " EMAIL CLOB,\n" +
91 " AMOUNT REAL,\n" +
92 " AMOUNTBDT REAL,\n" +
93 " DETAILS CLOB,\n" +
94 " PAYTYPE CLOB,\n" +
95 " PAYDATE CLOB,\n" +
96 ");\n" ;
97 String func = "CREATE TABLE FUNC\n" +
98 "(\n" +
99 " id IDENTITY ,\n" +
100 " PAYTYPE CLOB,\n" +
101 " QUICKSELECT CLOB,\n" +
102 ");\n" ;
103 String currency = "CREATE TABLE CURRENCY\n" +
104 "(\n" +
105 " id IDENTITY ,\n" +
106 " name CLOB,\n" +
107 " rate DOUBLE,\n" +
108 ");\n" ;
109 /**
110 String login = "CREATE TABLE LOGIN\n" +
111 "(\n" +
112 " ID CLOB NOT NULL,\n" +
113 " PASSWORD CLOB NOT NULL\n" +
114 ");\n";
115
116 String defaultUser="INSERT INTO login(id,password) VALUES(?,?) ";
117 **/
118
119 try{
120 Statement statement=null;
121 connection = SqlConnect.con();
122 statement=connection.createStatement();
123
124 statement.addBatch(dropTransaction);
125 statement.addBatch(dropfunc);
126 statement.addBatch(dropcurrency);
127 //statement.addBatch(droplogin);
128 statement.addBatch(transaction);
129 statement.addBatch(func);
130 statement.addBatch(currency);
131 //statement.addBatch(login);
132
133 statement.executeBatch();
134
135
136 //preparedStatement = connection.prepareStatement(defaultUser);
137 //preparedStatement.setString(1,"sales");
138 //preparedStatement.setString(2,"admin");
139 //preparedStatement.executeUpdate();
140
141 }
142 catch (SQLException e){
143 System.out.println(e);
144 }
145
146 finally {
147 //connection.setAutoCommit(true);
148 connection.close();
149 }
150
151 return false;
152 }
153
154
155
156
157 public boolean loginFirst(String user, String pass) throws SQLException {
158 sqlQuery = "select * from users where username = ? and password = ?";
159 try{
160 connection = SqlConnect.con();
161 preparedStatement = connection.prepareStatement(sqlQuery);
162 preparedStatement.setString(1,user);
163 preparedStatement.setString(2,pass);
164 resultSet = preparedStatement.executeQuery();
165 if (resultSet.next()){
166 loginFailed ="Success";
167 return true;
168 }
169 else {
170 loginFailed = "Username/Password is Wrong!";
171 return false;
172 }
173 }
174 catch (SQLException e){
175 System.out.println(e);
176 } catch (ClassNotFoundException e) {
177 e.printStackTrace();
178 }
179
180 finally {
181 preparedStatement.close();
182 resultSet.close();
183 connection.close();
184 }
185 return false;
186 }
187
188 public boolean empExist(int empID,String currentMonth) throws SQLException {
189 sqlQuery = "select * from mainsalary where emp_id = ? and salarydate LIKE ?";
190 System.out.println(sqlQuery);
191 try{
192 connection = SqlConnect.con();
193 preparedStatement = connection.prepareStatement(sqlQuery);
194 preparedStatement.setInt(1,empID);
195 preparedStatement.setString(2,"%"+currentMonth);
196 resultSet = preparedStatement.executeQuery();
197 System.out.println(preparedStatement.toString());
198 if (resultSet.next()){
199 info ="Already Exist!";
200
201 return false;
202
203 }
204 else {
205 info = "OK";
206
207
208 }
209
210 while (resultSet.next()){
211 System.out.println(resultSet.getInt(1));
212 }
213 }
214 catch (SQLException e){
215 System.out.println(e);
216 } catch (ClassNotFoundException e) {
217 e.printStackTrace();
218 }
219
220 finally {
221 preparedStatement.close();
222 resultSet.close();
223 connection.close();
224 }
225 return false;
226 }
227
228 public void CloseCon() throws SQLException {
229
230 if(connection!=null){
231 connection.close();
232
233 }
234
235 }
236
237 public void CustomException(Exception exception){
238 Alert alert = new Alert(Alert.AlertType.ERROR);
239 alert.setTitle("Errors");
240 alert.setHeaderText("Error");
241 alert.setContentText("Error Message");
242
243 String exceptionText = exception.toString();
244
245 Label label = new Label("Details:");
246
247 TextArea textArea = new TextArea(exceptionText);
248 textArea.setEditable(false);
249 textArea.setWrapText(true);
250
251 textArea.setMaxWidth(Double.MAX_VALUE);
252 textArea.setMaxHeight(Double.MAX_VALUE);
253 GridPane.setVgrow(textArea, Priority.ALWAYS);
254 GridPane.setHgrow(textArea, Priority.ALWAYS);
255
256 GridPane expContent = new GridPane();
257 expContent.setMaxWidth(Double.MAX_VALUE);
258 expContent.add(label, 0, 0);
259 expContent.add(textArea, 0, 1);
260
261// Set expandable Exception into the dialog pane.
262 alert.getDialogPane().setExpandableContent(expContent);
263
264 alert.showAndWait();
265 }
266
267 public void dialogAlert(String title,String header,String msg){
268 Alert alert = new Alert(Alert.AlertType.ERROR);
269 alert.setTitle(title);
270 alert.setHeaderText(header);
271 alert.setContentText(msg);
272 alert.showAndWait();
273 }
274
275 public void dialogInfo(String title,String header,String msg){
276 Alert alert = new Alert(Alert.AlertType.INFORMATION);
277 alert.setTitle(title);
278 alert.setHeaderText(header);
279 alert.setContentText(msg);
280 alert.showAndWait();
281 }
282
283 public boolean dialogConfirm(String title,String header,String msg){
284 Alert alert = new Alert(Alert.AlertType.CONFIRMATION);
285 alert.setTitle(title);
286 alert.setHeaderText(header);
287 alert.setContentText(msg);
288 Optional<ButtonType> result=alert.showAndWait();
289 if(result.get()==ButtonType.OK){
290 return true;
291 }
292 else {
293 return false;
294 }
295 }
296
297
298
299 public void loadMonths(String listCurrentItem){
300
301
302 switch (listCurrentItem){
303 case "January":
304
305 getMonth = 1;
306 break;
307 case "February":
308
309 getMonth = 2;
310 break;
311 case "March":
312 getMonth = 3;
313 break;
314 case "Appril":
315 getMonth = 4;
316 break;
317 case "May":
318 getMonth = 5;
319 break;
320 case "June":
321 getMonth = 6;
322 break;
323 case "July":
324 getMonth = 7;
325 break;
326 case "August":
327 getMonth = 8;
328 break;
329 case "September":
330 getMonth = 9;
331 break;
332 case "October":
333 getMonth = 10;
334 break;
335 case "November":
336 getMonth = 11;
337 break;
338 case "December":
339 getMonth = 12;
340 break;
341 }
342
343
344
345
346 }
347
348 public void yearsDayList(){
349 int y = year;
350 do {
351 yearList.add(y);
352
353 if(y==year+20){
354
355 break;
356 }
357 y++;
358 //y++;
359 }while (y>year);
360 int days = 1;
361 while (days<32){
362 daysList.add(days);
363 //System.out.println(days);
364 days++;
365 }
366 }
367}