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