#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    3

    How to print report using multiple unique numbers(primary keys)


    i have a project where , the system is supposed to print an excel report based on the franchise no, month and year.
    as of now my project print the excel report only if you key in one franchise number , moth , year.
    I would like to know how my servlet can accept different franchise numbers, separated by commas and prints an excel report with all the franchise results ..here is my index and servlet.

    here is the servlet

    public class reportexcel extends HttpServlet {
    @Override
    public void init(ServletConfig config) throws ServletException {
    super.init(config);
    }
    @Override
    public void destroy() {
    }
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    doGet(request, response);

    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {

    PrintWriter out = response.getWriter();

    out.print( request.getRequestURI() );



    out.close();


    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType("application/vnd.ms-excel");
    PrintWriter out = response.getWriter();
    HSSFWorkbook wb = new HSSFWorkbook ();
    HSSFSheet sheet = wb.createSheet("Mighty Report");

    out.print("\t\tMighty Auto Part Report\n");
    out.print("Franchise ID");
    out.print("\tFranchise Name");
    out.print("\tMonth\tyear");
    out.print("\tESR V");
    out.print("\tReported Values");
    out.print("\tDifference\n");

    Statement st2= null;
    Statement st3 = null;
    Statement st4 = null;

    ResultSet rs2;
    ResultSet rs3;
    ResultSet rs4;

    try {
    Connection con;
    Connection conn;

    Class.forName("org.postgresql.Driver").newInstance();
    con = DriverManager.getConnection("jdbcostgresql://216.77.96.20:5432/ESR","postgres","postgres");
    conn = DriverManager.getConnection("jdbcostgresql://216.77.96.20:5432/FranchiseDB","postgres","postgres");

    st2 = conn.createStatement();
    st3 = con.createStatement();
    st4 = con.createStatement();


    String franno = request.getParameter("franno");
    String month = request.getParameter("month");
    String year = request.getParameter("year");


    //Returns names of the franchises based on the farnchise number
    String query2 = "Select corpname from franchise where frannum = '"+franno+"'order by frannum";

    // to print ESR VALUES
    String query3 = "select franno, sum((ticket.sub_nontax + ticket.sub_tax + ticket.misc + ticket.disc + ticket.core_nontax + ticket.core_tax) - getxlinebycust(ticket.cust_id, ticket.inv_num::integer, date(ticket.inv_date))) AS ESRVALUE, date_part('month',inv_date) as salesmonth , date_part('year',inv_date) as salesyear from ticket, salesperson where ticket.mightyspid = salesperson.mightyspid and date_part('month',inv_date) = '"+month+"' and date_part('year',inv_date) = '"+year+"' and franno::integer in ('"+franno+"') group by franno, salesmonth,salesyear order by franno";
    //to print Reported Values
    String query4 = "select franno, sum(territory + custodial) as reported,salesmonth,salesyear from salesdata,salesperson where salesperson.mightyspid = salesdata.mightyspid and salesmonth = '"+month+"' and salesyear = '"+year+"' and franno::integer in ('"+franno+"') group by franno,salesmonth,salesyear order by franno";

    rs2 = st2.executeQuery(query2);
    rs3 = st3.executeQuery(query3);
    rs4 = st4.executeQuery(query4);



    if(rs2.next()){
    out.print(""+franno+"");
    out.print("\t");
    out.print(""+rs2.getString(1)+"");
    out.print("\t");
    out.print(""+month+"");
    out.print("\t");
    out.print(""+year+"");
    out.print("\t");

    if (rs3.next()){
    out.print(rs3.getDouble(2)+"");
    out.print("\t");
    if (rs4.next()){
    out.print(rs4.getDouble(2)+"");
    out.print("\t");
    double diff = 0.0;
    diff = (rs3.getDouble(2) - rs4.getDouble(2));
    out.print(diff);
    }

    FileOutputStream fileOut = new FileOutputStream("c:\\excelFile.xls");
    wb.write(fileOut);
    fileOut.close();
    out.print("\n");
    }
    }

    }
    catch (Exception e) {
    out.print(e);
    out.print("<p\"><a href=\"");
    out.print( request.getRequestURI() );
    out.print("\">Back</a></p>");
    //out.print("</body></html>");
    out.close();
    }


    }
    }

    here is the index..
    <html>
    <body>
    <form action="reportexcel" method="post">

    <table border="0" width="300" align="center" bgcolor="#CDFFFF">
    <tr><td colspan=2 style="font-size:12pt;color:#00000;" align="center"><h3>Search Record</h3></td></tr>
    <tr><td ><b>franchise Number</b></td><td>: <input type="text" name="franno" id="franno">
    </td></tr>
    <tr><td ><b>month</b></td><td>: <input type="text" name="month" id="month">
    </td></tr>
    <tr><td ><b>year</b></td><td>: <input type="text" name="year" id="year">
    </td></tr>
    <tr><td colspan=2 align="center"><input type="submit" name="submit" value="Submit"></td></tr>
    </table>
    </form>
    </body>
    </html>
  2. #2
  3. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Feb 2004
    Location
    Mobile, Alabama
    Posts
    4,023
    Rep Power
    1285
    You could modify your index file to ask to a list of franchise numbers seperated by a comma. Then in the servlet, tokenize the input (split it into seperate Strings), and then perform the query for each.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    3

    thank you


    Originally Posted by bullet
    You could modify your index file to ask to a list of franchise numbers seperated by a comma. Then in the servlet, tokenize the input (split it into seperate Strings), and then perform the query for each.
    Thank you bullet , i have never used tokens and delimiters but let me do a quick research and get back to you
  6. #4
  7. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Feb 2004
    Location
    Mobile, Alabama
    Posts
    4,023
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    Thank you bullet , i have never used tokens and delimiters but let me do a quick research and get back to you
    Look at the JavaDocs for String.java
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    3

    invalid input syntax for integer: "140,381 "


    Originally Posted by bullet
    Look at the JavaDocs for String.java
    this is what i changed part of my code in the servlet to look like and it gave me an error

    //this is supposed to display the franchise number
    String franno = request.getParameter("franno");
    String[] tokens = franno.split(",");
    for (int i = 0; i<tokens.length; i++){
    out.print(tokens[i]+"");}
  10. #6
  11. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Feb 2004
    Location
    Mobile, Alabama
    Posts
    4,023
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    this is what i changed part of my code in the servlet to look like and it gave me an error

    //this is supposed to display the franchise number
    String franno = request.getParameter("franno");
    String[] tokens = franno.split(",");
    for (int i = 0; i<tokens.length; i++){
    out.print(tokens[i]+"");}
    What error did it give you?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    3
    Originally Posted by bullet
    What error did it give you?
    invalid input syntax for integer: "140,381"
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    7
    Rep Power
    0

    Helpppppppppppp


    hey guys, can u check this code and tell me y, these two javascripts arent working together, if i open it in a browser
    the comments sections works and the twitter doesnt but when i refresh the page the tiwtter works and the comments doesnt
    i didnt align anything so it seems sporadic
    i just need help getting the two scripts to work properly


    i would post the code but for some reason url's are blocked

    sry i am new to forums

    Comments on this post

    • stdunbar disagrees : (-0) Start a new post, don't hijack an exising one
  16. #9
  17. Java Junkie
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Feb 2004
    Location
    Mobile, Alabama
    Posts
    4,023
    Rep Power
    1285
    Originally Posted by nicholas.omosa
    invalid input syntax for integer: "140,381"
    Are you sure that's the error you get from that code?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    34
    Rep Power
    3
    Originally Posted by bullet
    Are you sure that's the error you get from that code?
    yes this is the error i get , when i run my index file and key in the different user inputs,and submit to the servlet

IMN logo majestic logo threadwatch logo seochat tools logo