Untitled

<html>
<head>
<link rel="stylesheet"
    href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<script src="jbPivot.min.js"></script>
<link rel="stylesheet" href="jbPivot.css">


</head>
<body>
    <marquee>
        <h1>This is an example of ajax</h1>
    </marquee>

    <p>
        Date: <input type="text" id="startDatePicker">
    </p>
    <p>
        Date: <input type="text" id="endDatePicker">
    </p>
    <span id="somediv"> <span id="err">Select Correct Dates</span>
    </span>
    <script type="text/javascript" src="tableGenerator.js"></script>

</body>
</html>

var startDate = $("#startDatePicker").datepicker({
    dateFormat : 'yy-mm-dd',
    onClose : function(selectedDate) {
        $("#endDatePicker").datepicker("option", "minDate", selectedDate);
    }
});
var endDate = $("#endDatePicker").datepicker({
    dateFormat : 'yy-mm-dd',
    onClose : function(selectedDate) {
        $("#startDatePicker").datepicker("option", "maxDate", selectedDate);
    }
});
$(document).on('change', '#startDatePicker,#endDatePicker', function() {
    $('#somediv > table').remove();
    $.getJSON('Controller', {
        'startDate' : startDate.val(),
        'endDate' : endDate.val()
    }, function(searchList) {
        console.log(JSON.stringify(searchList));
        if (searchList.length < 1) {
            $('#err').show();
            $('#err').text("No Records found in Specified Dates");
        } else {
            $("#somediv").jbPivot({
                fields : {
                    caseOwner : {
                        field : 'caseOwner',
                        sort : "asc",
                        showAll : true,
                        agregateType : "distinct"
                    },
                    finalStatus : {
                        field : 'finalStatus',
                        sort : "asc",
                        showAll : false,
                        agregateType : "distinct"
                    },
                    status : {
                        field : 'status',
                        sort : "asc",
                        showAll : false,
                        agregateType : "distinct"
                    },
                    Count : {
                        agregateType : "count",
                        groupType : "none"
                    },
                },
                xfields : [ "caseOwner" ],
                yfields : [ "finalStatus" ],
                statfields : [ "status" ],
                zfields : [ "Count" ],
                data : searchList
            });

        }

    });
});

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.bean.UserBean;

import com.dao.DataDao;
import com.google.gson.Gson;

@WebServlet("/Controller")
public class Controller extends HttpServlet {
    private static final long serialVersionUID = 1L;

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            /* Date Start */
            String startDateStr = request.getParameter("startDate");
            String endDateStr = request.getParameter("endDate");
            System.out.println("Start date got is " + startDateStr + " and end date is " + endDateStr);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat print = new SimpleDateFormat("yyyy-MM-dd");
            Date startParsedDate = null, endParsedDate = null;
            String startDate = null, endDate = null;
            if (startDateStr != null && !startDateStr.equals("")) {
                startParsedDate = sdf.parse(startDateStr);
                startDate = print.format(startParsedDate);
            }
            if (endDateStr != null && !endDateStr.equals("")) {
                endParsedDate = sdf.parse(endDateStr);
                endDate = print.format(endParsedDate);
            }

            System.out.println(startDate + " value and " + endDate);
            /* Date End */
            DataDao dataDao = new DataDao();
            ArrayList<UserBean> list = dataDao.getFrameWork(startDate, endDate);
            String searchList = new Gson().toJson(list);
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(searchList);
            System.out.println("servlet Done");
        } catch (Exception e) {
            System.err.println(e.getMessage() + " servlet Errotr");
        }
    }
}

package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.bean.UserBean;

public class DataDao {
    private Connection connection;

    public DataDao() throws Exception {
        connection = DBUtility.getConnection();
    }

    public ArrayList<UserBean> getFrameWork(String startDate, String endDate) throws SQLException {
        String startDateFromController = startDate;
        String endDateFromController = endDate;
        System.out.println("DAO VAlues are " + startDateFromController + " and " + endDateFromController);
        ArrayList<UserBean> list = new ArrayList<UserBean>();
        PreparedStatement ps = null;
        try {
            String query;

            if (!(startDateFromController == null) && endDateFromController == null) {
                System.out.println("first block imvoked");
                query = "select * from StatusTable where convert(date, [Start Time]) >= ?";
                ps = connection.prepareStatement(query);
                ps.setString(1, startDateFromController);
            } else if (startDateFromController == null && !(endDateFromController == null)) {
                System.out.println("end date is " + endDateFromController);
                System.out.println("second");
                query = "select * from StatusTable where convert(date, [Start Time]) <= ?";
                ps = connection.prepareStatement(query);
                ps.setString(1, endDateFromController);
            } else if (startDateFromController == null && endDateFromController == null) {
                System.out.println("Thuird block");
                query = "select * from StatusTable where [Start Time] Is NOT NULL";
                ps = connection.prepareStatement(query);
            } else {
                System.out.println("Forth block");
                query = "select * from StatusTable where convert(date, [Start Time]) between ? and ?";
                ps = connection.prepareStatement(query);
                ps.setString(1, startDate);
                ps.setString(2, endDate);
            }
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                System.out.println("No records found");
            } else {
                do {
                    UserBean userBean = new UserBean();
                    userBean.setCaseNumber(rs.getString("Case Number"));
                    userBean.setCaseOwner(rs.getString("Case Owner"));
                    userBean.setStatus(rs.getString("Status"));
                    userBean.setIssue(rs.getString("Issue"));
                    userBean.setReason(rs.getString("Reason"));
                    userBean.setDateOpened(rs.getString("Date/Time Opened"));
                    userBean.setAge(rs.getInt("Age"));
                    userBean.setFinalStatus(rs.getString("Final Status"));
                    list.add(userBean);
                } while (rs.next());
            }
        } catch (Exception e) {
            System.out.println(e + " DAO Error");
        }
        return list;
    }
}

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBUtility {
    private static Connection connection = null;

    public static Connection getConnection() throws Exception {
        if (connection != null)
            return connection;
        else {
            // Store the database URL in a string
            String userName = "sa";
            String password = "T!ger123";
            String url = "jdbc:sqlserver://U0138039-TPD-A\SQLEXPRESS;DatabaseName=TEST";

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

            // set the url, username and password for the databse
            connection = DriverManager.getConnection(url, userName, password);
            return connection;
        }
    }
}