public static ArrayList<Venta> getVentas(
Calendar fechaInicio, Calendar fechaFin, int idEmpleado) {
ArrayList<Venta> ventas = new ArrayList<Venta>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date fechaIni = fechaInicio.getTime();
Date fechaF = fechaFin.getTime();
String fechaIniString = sdf.format(fechaIni);
String fechaFinString = sdf.format(fechaF);
String consulta =
"SELECT * FROM venta WHERE id_empleado = '"
+ idEmpleado
+ "' AND julianday(fecha)>=julianday('"
+ fechaIniString
+ "') AND julianday(fecha)<=julianday('"
+ fechaFinString
+ "')";
try {
SqlConnection.conectar();
ResultSet rs = SqlConnection.ejecutarResultado(consulta);
while (rs.next()) {
int idEmpl = rs.getInt("id_empleado");
Date date = rs.getDate("fecha");
Calendar fecha = Calendar.getInstance();
fecha.setTime(date);
double monto = rs.getDouble("monto");
Venta venta = new Venta(fecha, monto, idEmpl);
ventas.add(venta);
}
SqlConnection.desconectar();
} catch (Exception e) {
System.out.printf(e.getMessage());
}
return ventas;
}
public long cacuAddr(String flightNum) {
long remark = 0;
try {
String sqlString = "select remark from flight where flight='" + flightNum + "'";
ResultSet rs = sqlConnection.executeQuery(sqlString);
while (rs.next()) remark = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
}
return (remark - 1) * 4;
}
public static boolean guardarVenta(Venta venta) {
boolean exito = true;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date fecha = venta.getFecha().getTime();
String fechaString = sdf.format(fecha);
String consulta =
"INSERT INTO venta (id_empleado, fecha, monto) VALUES ('"
+ venta.getIdEmpleado()
+ "','"
+ fechaString
+ "','"
+ venta.getMonto()
+ "')";
try {
SqlConnection.conectar();
SqlConnection.ejecutar(consulta);
SqlConnection.desconectar();
} catch (Exception e) {
System.out.printf(e.getMessage());
exito = false;
}
return exito;
}
// method to retrieve all the skills that are stored in the database and add it to arraylist
public void getSkills() throws SQLException {
skill_list = new ArrayList<JCheckBox>(); // initialise list
SqlConnection.connect(); // connect to database
SqlConnection.statement = SqlConnection.connection.createStatement();
SqlConnection.statement.executeQuery("SELECT skill_id FROM SKILL"); // get all skills
SqlConnection.result = SqlConnection.statement.getResultSet(); // store skills
while (SqlConnection.result.next()) {
skill_list.add(
new JCheckBox(SqlConnection.result.getString("skill_id"))); // add skill skill to list
}
}
public int dingPiao(String flightNum, String day, int seats) {
int leftSeats = 0;
try {
long index = cacuIndex(day);
long address = cacuAddr(flightNum);
long absoluteAddress = index + address;
raf.seek(absoluteAddress);
int bookedSeats = raf.readInt();
String sqlString = "select seat,week from flight where flight='" + flightNum + "' ";
ResultSet rs = sqlConnection.executeQuery(sqlString);
int totalSeats = 0;
String week = "";
while (rs.next()) {
totalSeats = rs.getInt(1);
week = rs.getString(2);
}
String c = isAbsence(day);
int flag = 0;
for (int i = 0; i < week.length(); i++) {
String w = week.substring(i, i + 1);
if (c.equals(w)) {
flag = 1;
break;
}
}
if (flag == 1) {
leftSeats = totalSeats - bookedSeats;
if (leftSeats >= seats) {
raf.seek(absoluteAddress);
raf.writeInt(bookedSeats + seats);
return -1;
} else return leftSeats;
} else return -2;
} catch (Exception e) {
e.printStackTrace();
}
return leftSeats;
}
public boolean isFull(String flightNum, String day) {
try {
long index = cacuIndex(day);
long address = cacuAddr(flightNum);
long absoluteAddress = index + address;
raf.seek(absoluteAddress);
int bookedSeats = raf.readInt();
String sqlString = "select seat from flight where flight='" + flightNum + "'";
ResultSet rs = sqlConnection.executeQuery(sqlString);
int totalSeats = 0;
while (rs.next()) totalSeats = rs.getInt(1);
if (totalSeats == bookedSeats) return true;
else return false;
} catch (Exception e) {
return false;
}
}
public void actionPerformed(ActionEvent event) {
// if clear button is pressed, clear all fields
if (event.getSource() == clear_button) {
// erase all fields
taskname_field.setText("");
taskdesc_field.setText("");
priority_checkbox.setSelected(false);
numberofdays_field.setText("");
// isallocatedcheckbox.setSelected(false);
duedate_field.setText("");
}
// if submit button is pressed, save all data onto database
if (event.getSource() == submit_button) {
try {
{
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date()); // set to current date
calendar.add(
Calendar.DAY_OF_MONTH,
Integer.parseInt(this.numberofdays_field.getText())
- 1); // add days required... then:
// ^ subtract one day, because if task is created today and allocated tomorrow for one
// day,
// then it should be done by end of day tomorrow
Date date = calendar.getTime(); // (see next *1) use this date to be the earliest
// because of expected task duration
/*This is a constraint
* the date and the length of days that user expected to take
* if not feasible then the program will say so
* */
// method compares the current date and the date that was entered by the user
if (this.task_idfield.getText().length() != 7 // check chars
|| this.taskname_field.getText().length() > 50 // check chars
|| this.taskdesc_field.getText().length() > 50
|| ((Date) this.duedate_field.getValue()).compareTo(date)
< 0 // check that the date is possible (see last *1)
|| this.numberofdays_field.getText().length() > 2
|| this.numberofdays_field.getText().length() == 0 // check number of digits
|| Integer.parseInt(this.numberofdays_field.getText())
< 1) // check number of days required for task
{
JOptionPane.showMessageDialog(
this,
"Task ID has to be 7 chars; "
+ "\n"
+ "Description <= 50 chars; "
+ "\n"
+ "Because the expected duration of the task is "
+ Integer.parseInt(this.numberofdays_field.getText())
+ " days, the due date cannot be earlier than: "
+ new StringBuffer(new SimpleDateFormat("dd/MM/yyyy").format(date)).toString()
+ "; "
+ "\n"
+ "Task duration must be 2 digit number maximum and must be an integer greater than 0");
} else
// check if taskid_field is equals to any of the fields on the database
if (task_idfield.equals("")) {
JOptionPane.showMessageDialog(
this, "Please insert an identification number for a task");
} else // add constraint and tell customer to enter 7 values
if (taskname_field.equals("")) {
JOptionPane.showMessageDialog(this, "Please insert a name to identity the task");
} else {
// get all inputs from user inputs and store in variables
taskid_input = task_idfield.getText();
taskname_input = taskname_field.getText();
taskdesc_input = taskdesc_field.getText();
if (priority_checkbox.isSelected()) {
priority_input = 1;
} else {
priority_input = 0;
}
// create and store date from user input
date = (Date) duedate_field.getValue();
// store number of days input from user
numberofdays_input = Integer.parseInt(numberofdays_field.getText());
// String[] column_names = { "Task ID", "Task Name", "Task Description", "Task
// Priority", "Due Date", "Number of Days", "Is Allocated" , "Unallocateable" };
task = new Object[8];
task[0] = taskid_input;
task[1] = taskname_input;
task[2] = taskdesc_input;
task[3] = priority_input;
task[4] = date;
task[5] = numberofdays_input;
task[6] = 0;
task[7] = 0;
try {
//
SqlConnection.connect();
SqlConnection.ps =
SqlConnection.connection.prepareStatement(
"INSERT INTO TASK VALUES (?,?,?,?,?,?,?,?)");
SqlConnection.ps.setString(1, taskid_input);
SqlConnection.ps.setString(2, taskname_input);
SqlConnection.ps.setString(3, taskdesc_input);
SqlConnection.ps.setInt(4, priority_input);
SqlConnection.ps.setDate(5, new java.sql.Date(date.getTime()));
SqlConnection.ps.setInt(6, numberofdays_input);
SqlConnection.ps.setInt(7, 0);
SqlConnection.ps.setInt(8, 0);
SqlConnection.ps.executeUpdate();
// now enter skills into TASK SKILL database
for (int i = 0; i < skill_list.size(); i++) {
if (skill_list.get(i).isSelected()) {
String skillid = skill_list.get(i).getText();
SqlConnection.statement.executeUpdate(
"INSERT INTO TASKSKILL VALUES ('" + taskid_input + "', '" + skillid + "')");
}
}
SqlConnection.ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// SqlConnection.ps.close();
SqlConnection.closeConnection();
this.setVisible(false);
}
}
} catch (Exception exc) {
JOptionPane.showMessageDialog(
this,
"Check your inputs:"
+ "\n"
+ "Priority has to be a 1-digit number;"
+ "\n"
+ "Date has to be in the format dd/MM/yyyy;"
+ "\n"
+ "Task length has to be 2-digit number");
System.out.println(exc);
}
}
// display dialog box asking task manager if they want to quit,
// if task manager does not want to quit, go back to adding more tasks
// if customer quits, close the dialog box and the frame
// now close the window after saving
}
