When the Search button is pressed in the screen depicted, the getSales() function from the PosDAO.java class is called, the parameters of this function are the from date and to date.
The getSales () function should return a list of Pos objects that matches the criteria specified, Note this function has to perform two INNER JOINs in to combine all three tables in order to get all the needed data to fill the Pos object. The database schema is also depicted. The PosDAO, Product, SalesDetails and Pos classes are shown BELOW

Write the getSales() function

public class PosDAO {

private Connection conn;

PreparedStatement stmt = null;

public boolean openConnection(){

try {

// db parameters

String url = "jdbc:mysql://localhost:4306/swen2005";

String user = "root";

String password = "";

// create a connection to the database

conn = DriverManager.getConnection(url, user, password);

if (conn!= null){

return true;

}

}

catch(SQLException e){

System.out.println(e.getMessage());

return false;

}

return false;

}

public void closeConnection()

{

try

{

if(conn!=null)

conn.close();

}

catch(SQLException ex){

System.out.println(ex.getMessage());

}

}

public void saveTransaction(List <Salesdetails> items) throws SQLException{

}

public List<Pos> getSales(String fromDate, String toDate){

}

}

public class Pos {

public String salesDate;

public int salesNumber;

public String prodcode;

public String prodname;

public double salesTotal;

public double unit;

public int qty;

public Pos(String sd,int sn,String pcode,String pname,double ucost, int q, double st)

{

this.salesDate = sd;

this.salesNumber = sn;

this.prodcode = pcode;

this.qty = q;

this.prodname = pname;

this.unit = ucost;

this.salesTotal = st;

}

}

class Salesdetails {

public String prodcode;

public String prodname;

public double unit;

public int qty;

public Salesdetails(String pcode,String pname,double ucost, int q)

{

this.prodcode = pcode;

this.qty = q;

this.prodname = pname;

this.unit = ucost;

}

}

class Product{

public int number;

public String prodID;

public String prodName;

public float prodPrice;

public int prodOnHand;

Product(int num,String id, String name, float price, int qty)

{

number=num;

prodID=id;

prodName=name;

prodPrice=price;

prodOnHand=qty;

}

}

Transcribed Image Text:Sales Finder Screen Sales date from: 2021-03-09 Sales Date 2021-03-15 2021-03-15 2021-03-15 2021-03-20 2021-03-20 2021-03-20 2021-03-20 2021-03-20 2021-03-21 Sales date to: 2021-03-23 Sales Number Product Code 4 4225 4 9813 4 3700 5 4225 5 9813 5 3700 6 5486 6 6097 7 4225 Search Product Name Cheese Feta Wine Muscadet Sur Lie Compound - Rum Cheese Feta Wine Muscadet Sur Lie Compound Rum Plasticknivesblack Sage Ground Wiberg Cheese Feta Price 21.77 34.1 32.15 21.77 34.1 32.15 11.08 16.49 21.77 Qty 15 7 2 10 15 4 12 6 5 Total X 326.55 238.7 64.3 217.7 511.5 128.6 132.96 98.94 108.85
Transcribed Image Text:swen2005 sales SalesNumber: int(10) SalesDate : date # Sales Total : decimal(7,2) V swen2005 salesdetails number: int(10) # SalesNumber : int(10) Ⓒ prodid: varchar(20) # price : decimal(7,2) # qty: int(10) swen2005 products number : int(11) prodid: varchar(20) Ⓒ prodname : varchar(30) # price : decimal(10,2) #onhand int(11)

Question

When the Search button is pressed in the screen depicted, the getSales() function from the PosDAO.java class is called, the parameters of this function are the from date and to date.
The getSales () function should return a list of Pos objects that matches the criteria specified, Note this function has to perform two INNER JOINs in to combine all three tables in order to get all the needed data to fill the Pos object. The database schema is also depicted. The PosDAO, Product, SalesDetails and Pos classes are shown BELOW

Write the getSales() function

public class PosDAO {

private Connection conn;

PreparedStatement stmt = null;

public boolean openConnection(){

try {

// db parameters

String url = "jdbc:mysql://localhost:4306/swen2005";

String user = "root";

String password = "";

// create a connection to the database

conn = DriverManager.getConnection(url, user, password);

if (conn!= null){

return true;

}

catch(SQLException e){

System.out.println(e.getMessage());

return false;

}

return false;

}

public void closeConnection()

{

try

{

if(conn!=null)

conn.close();

}

catch(SQLException ex){

System.out.println(ex.getMessage());

}

public void saveTransaction(List <Salesdetails> items) throws SQLException{

}

public List<Pos> getSales(String fromDate, String toDate){

}

public class Pos {

public String salesDate;

public int salesNumber;

public String prodcode;

public String prodname;

public double salesTotal;

public double unit;

public int qty;

public Pos(String sd,int sn,String pcode,String pname,double ucost, int q, double st)

{

this.salesDate = sd;

this.salesNumber = sn;

this.prodcode = pcode;

this.qty = q;

this.prodname = pname;

this.unit = ucost;

this.salesTotal = st;

}

class Salesdetails {

public String prodcode;

public String prodname;

public double unit;

public int qty;

public Salesdetails(String pcode,String pname,double ucost, int q)

{

this.prodcode = pcode;

this.qty = q;

this.prodname = pname;

this.unit = ucost;

}

class Product{

public int number;

public String prodID;

public String prodName;

public float prodPrice;

public int prodOnHand;

Product(int num,String id, String name, float price, int qty)

{

number=num;

prodID=id;

prodName=name;

prodPrice=price;

prodOnHand=qty;

}

Sales Finder Screen
Sales date from:
2021-03-09
Sales Date
2021-03-15
2021-03-15
2021-03-15
2021-03-20
2021-03-20
2021-03-20
2021-03-20
2021-03-20
2021-03-21
Sales date to:
2021-03-23
Sales Number Product Code
4 4225
4
9813
4 3700
5 4225
5 9813
5 3700
6 5486
6 6097
7 4225
Search
Product Name
Cheese Feta
Wine Muscadet Sur Lie
Compound - Rum
Cheese Feta
Wine Muscadet Sur Lie
Compound Rum
Plasticknivesblack
Sage Ground Wiberg
Cheese Feta
Price
21.77
34.1
32.15
21.77
34.1
32.15
11.08
16.49
21.77
Qty
15
7
2
10
15
4
12
6
5
Total
X
326.55
238.7
64.3
217.7
511.5
128.6
132.96
98.94
108.85

swen2005 sales
SalesNumber: int(10)
SalesDate : date
# Sales Total : decimal(7,2)
V swen2005 salesdetails
number: int(10)
# SalesNumber : int(10)
Ⓒ prodid: varchar(20)
# price : decimal(7,2)
# qty: int(10)
swen2005 products
number : int(11)
prodid: varchar(20)
Ⓒ prodname : varchar(30)
# price : decimal(10,2)
#onhand int(11)

Accepted Answer

In this scenario, we were asked to write the getSales() function in the PosDAO class that retrieves…