List of usage examples for com.google.gson JsonArray JsonArray
public JsonArray()
From source file:account.SalesReturnRegisterDetailAccount.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from www.j a v a 2 s. com * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String ac_cd = request.getParameter("ac_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final int branch_cd = Integer.parseInt(request.getParameter("branch_cd")); final int pmt_mode = Integer.parseInt(request.getParameter("pmt_mode")); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select v.ref_no,'Sales Return' as v_type,v.INV_NO," + "v.V_DATE,v.PMT_MODE,v1.DISC_RATE,v1.MRP," + " case when a.FNAME is null then '' else fname end as fname,s.SR_ALIAS,s.SR_NAME,v1.IMEI_NO,v.branch_cd," + " v1.SERAIL_NO,v1.QTY,v1.RATE,v1.AMT,v.DET_TOT,v1.TAX_AMT,v1.ADD_TAX_AMT,v.NET_AMT,p.CASH_AMT,p.BANK_AMT,p.CARD_AMT,p.bajaj_amt" + ",t.TAX_NAME" + " ,v1.basic_amt from SRHD v left join SRDT v1 on v.REF_NO =v1.REF_NO left join acntmst a on v.AC_CD=a.AC_CD " + " left join SERIESMST s on s.SR_CD=v1.SR_CD left join PAYMENT p on v.REF_NO=p.REF_NO left join TAXMST t on v1.TAX_CD=t.TAX_CD " + " where v_date >='" + from_date + "' " + " and is_del=0 and v.v_date <='" + to_date + "'"; if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "' "; } if (pmt_mode != 2) { sql += " and v.pmt_mode=" + pmt_mode; } if (branch_cd != 0) { sql += " and v.branch_cd=" + branch_cd; } sql += " order by v.V_DATE,v.INV_NO,v1.tag_no,v1.is_main desc"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("ref_no", viewDataRs.getString("REF_NO")); object.addProperty("v_type", viewDataRs.getString("V_TYPE")); object.addProperty("inv_no", viewDataRs.getString("inv_no")); object.addProperty("v_date", viewDataRs.getString("V_DATE")); object.addProperty("fname", viewDataRs.getString("FNAME")); object.addProperty("SR_ALIAS", viewDataRs.getString("SR_ALIAS")); object.addProperty("SR_NAME", viewDataRs.getString("SR_NAME")); object.addProperty("IMEI_NO", viewDataRs.getString("IMEI_NO")); object.addProperty("SERAIL_NO", viewDataRs.getString("SERAIL_NO")); object.addProperty("QTY", viewDataRs.getInt("QTY")); object.addProperty("RATE", viewDataRs.getDouble("RATE")); object.addProperty("DISC_RATE", viewDataRs.getDouble("DISC_RATE")); object.addProperty("MRP", viewDataRs.getDouble("MRP")); object.addProperty("AMT", viewDataRs.getDouble("AMT")); object.addProperty("BASIC_ITEM_AMT", viewDataRs.getDouble("basic_amt")); object.addProperty("det_tot", viewDataRs.getDouble("DET_TOT")); object.addProperty("tax_amt", viewDataRs.getDouble("TAX_AMT")); object.addProperty("add_tax_amt", viewDataRs.getDouble("ADD_TAX_AMT")); object.addProperty("CASH_AMT", viewDataRs.getDouble("CASH_AMT")); object.addProperty("BANK_AMT", viewDataRs.getDouble("BANK_AMT")); object.addProperty("CARD_AMT", viewDataRs.getDouble("CARD_AMT")); object.addProperty("net_amt", viewDataRs.getDouble("NET_AMT")); object.addProperty("tax_name", viewDataRs.getString("tax_name")); object.addProperty("bajaj_amt", viewDataRs.getDouble("bajaj_amt")); object.addProperty("branch_cd", viewDataRs.getDouble("branch_cd")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.SalesReturnReportTax.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./* w w w . j ava 2 s .c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "SELECT v.v_date,v.INV_NO,a.FNAME,a.TIN,t.TAX_NAME,sum(v1.BASIC_AMT) AS basic,sum(v1.tax_amt) AS tax,sum(v1.ADD_TAX_AMT) AS add_tax,sum(v1.DISC_RATE) AS disc,sum(v1.AMT) AS amt \n" + "FROM SRHD v LEFT JOIN SRDT v1 ON v.REF_NO=v1.REF_NO LEFT JOIN seriesmst s ON v1.SR_CD=s.sr_cd LEFT JOIN modelmst m ON s.MODEL_CD=m.MODEL_CD\n" + "LEFT JOIN taxmst t ON m.TAX_CD=t.TAX_CD LEFT JOIN acntmst a ON v.AC_CD=a.AC_CD\n" + "WHERE v.IS_DEL=0 and v_date >='" + from_date + "' " + "and v_date <='" + to_date + "' group by v.v_date,v.inv_no,a.fname,a.tin,t.tax_name ORDER BY v.inv_no"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("v_date", viewDataRs.getString("v_date")); object.addProperty("INV_NO", viewDataRs.getString("INV_NO")); object.addProperty("FNAME", viewDataRs.getString("FNAME")); object.addProperty("TIN", viewDataRs.getString("TIN")); object.addProperty("TAX_NAME", viewDataRs.getString("TAX_NAME")); object.addProperty("basic", viewDataRs.getString("basic")); object.addProperty("tax", viewDataRs.getString("tax")); object.addProperty("add_tax", viewDataRs.getString("add_tax")); object.addProperty("disc", viewDataRs.getString("disc")); object.addProperty("amt", viewDataRs.getString("amt")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.TypeWiseBrandWiseProfitStatement.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//w w w . j a v a2 s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String type_cd = request.getParameter("type_cd"); final String branch_cd = request.getParameter("branch_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select v.branch_cd,brand_name,sum(1) as qty,sum(t.PUR_RATE) as purchase,sum(t.SALE_RATE) as sales" + " from tag t left join SERIESMST s on t.SR_CD=s.SR_CD left join MODELMST m on s.MODEL_CD=m.MODEL_CD " + " left join brandmst b on m.brand_cd=b.brand_cd left join VILSHD v on t.SALE_REF_NO=v.REF_NO" + " where t.is_del =1 and SALE_REF_NO <> '' " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "' "; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd + ""; } sql += " group by brand_name,branch_cd order by brand_name"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("BRAND_NAME", viewDataRs.getString("brand_name")); object.addProperty("QTY", viewDataRs.getInt("qty")); object.addProperty("PURCHASE", viewDataRs.getDouble("purchase")); object.addProperty("SALES", viewDataRs.getDouble("sales")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.TypeWiseProftStatement.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./* w w w . j a va2 s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String type_cd = request.getParameter("type_cd"); final String branch_cd = request.getParameter("branch_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select v.branch_cd,TYPE_NAME,sum(1) as qty,sum(t.PUR_RATE) as purchase,sum(t.SALE_RATE) as sales" + " from tag t left join SERIESMST s on t.SR_CD=s.SR_CD left join MODELMST m on s.MODEL_CD=m.MODEL_CD " + " left join TYPEMST t1 on m.TYPE_CD=t1.TYPE_CD left join VILSHD v on t.SALE_REF_NO=v.REF_NO" + " where t.is_del =1 and SALE_REF_NO <> '' " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "' "; if (!type_cd.equalsIgnoreCase("")) { sql += " and t1.type_cd='" + type_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd + ""; } sql += " group by TYPE_NAME,branch_cd order by type_name"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("TYPE_NAME", viewDataRs.getString("TYPE_NAME")); object.addProperty("QTY", viewDataRs.getInt("qty")); object.addProperty("PURCHASE", viewDataRs.getDouble("purchase")); object.addProperty("SALES", viewDataRs.getDouble("sales")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.TypeWisePurchase.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//w w w .j ava 2 s. c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String ac_cd = request.getParameter("ac_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final String sr_cd = request.getParameter("sr_cd"); final String type_cd = request.getParameter("type_cd"); final String brand_cd = request.getParameter("brnad_cd"); final String model_cd = request.getParameter("model_cd"); final String branch_cd = request.getParameter("branch_cd"); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select v.branch_cd,m.model_name,t.TYPE_NAME,sum(v1.qty) as pcs,sum(v1.RATE) as tot_sales from LBRPHD v left join LBRPDT v1 on v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD \n" + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd; } sql += " group by v.branch_cd,t.TYPE_NAME,m.model_name order by model_name"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs")); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } sql = "select v.branch_cd,m.model_name,t.TYPE_NAME,sum(v1.qty) as pcs,sum(v1.RATE) as tot_sales from prhd v left join prdt v1 on v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD \n" + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd + ""; } sql += " group by t.TYPE_NAME,m.model_name order by m.model_name"; pstLocal = dataConnection.prepareStatement(sql); viewDataRs = pstLocal.executeQuery(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs") * -1); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales") * -1); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.TypeWisePurchaseDetail.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./* w ww . j a va2 s . c o m*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String ac_cd = request.getParameter("ac_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final String sr_cd = request.getParameter("sr_cd"); final String type_cd = request.getParameter("type_cd"); final String brand_cd = request.getParameter("brnad_cd"); final String model_cd = request.getParameter("model_cd"); final String branch_cd = request.getParameter("branch_cd"); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select b.brand_name,m.model_name,v.branch_cd,v.ref_no,a.fname,s.sr_name,v.v_date,t.TYPE_NAME,v1.tag_no as IMEI_NO,(v1.qty) as pcs,(v1.RATE) as tot_sales from LBRPHD v left join LBRPdt v1 on v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD left join acntmst a on v.ac_cd=a.ac_cd " + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD" + " left join BRANDMST b on m.brand_cd=b.brand_cd where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd; } pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("fname", viewDataRs.getString("fname")); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("sr_name", viewDataRs.getString("sr_name")); object.addProperty("v_date", viewDataRs.getString("v_date")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs")); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales")); object.addProperty("IMEI_NO", viewDataRs.getString("IMEI_NO")); object.addProperty("REF_NO", viewDataRs.getString("REF_NO")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); object.addProperty("brand_name", viewDataRs.getString("brand_name")); array.add(object); } sql = "select b.brand_name,m.model_name,v.branch_cd,v.ref_no,a.fname,s.sr_name,v.v_date,t.TYPE_NAME,case when IMEI_NO ='' then SERAIL_NO else IMEI_NO end as IMEI_NO,(v1.qty) as pcs,(v1.RATE) as tot_sales from prhd v left join prdt v1 on" + " v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD left join acntmst a on v.ac_cd=a.ac_cd " + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD" + " left join BRANDMST b on m.brand_cd=b.brand_cd where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd; } pstLocal = dataConnection.prepareStatement(sql); viewDataRs = pstLocal.executeQuery(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("fname", viewDataRs.getString("fname")); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("sr_name", viewDataRs.getString("sr_name")); object.addProperty("v_date", viewDataRs.getString("v_date")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs") * -1); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales") * -1); object.addProperty("IMEI_NO", viewDataRs.getString("IMEI_NO")); object.addProperty("REF_NO", viewDataRs.getString("REF_NO")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); object.addProperty("brand_name", viewDataRs.getString("brand_name")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.TypeWiseSales.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w ww.jav a2s.c om * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String ac_cd = request.getParameter("ac_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final String sr_cd = request.getParameter("sr_cd"); final String type_cd = request.getParameter("type_cd"); final String brand_cd = request.getParameter("brnad_cd"); final String model_cd = request.getParameter("model_cd"); final String branch_cd = request.getParameter("branch_cd"); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select v.branch_cd,b.brand_name,m.model_name,t.TYPE_NAME,sum(v1.qty) as pcs,sum(v1.RATE) as tot_sales from VILSHD v left join VILSDT v1 on v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD \n" + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD" + " left join brandmst b on b.brand_cd=m.brand_cd where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd; } sql += " group by v.branch_cd,t.TYPE_NAME,m.model_name,brand_name order by branch_cd,model_name"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("brand_name", viewDataRs.getString("brand_name")); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs")); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } sql = "select v.branch_cd,b.brand_name,m.model_name,t.TYPE_NAME,sum(v1.qty) as pcs,sum(v1.RATE) as tot_sales from srhd v left join srdt v1 on v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD \n" + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD " + " left join brandmst b on b.brand_cd=m.brand_cd where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd + ""; } sql += " group by t.TYPE_NAME,m.model_name order by m.model_name"; pstLocal = dataConnection.prepareStatement(sql); viewDataRs = pstLocal.executeQuery(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("brand_name", viewDataRs.getString("brand_name")); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs") * -1); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales") * -1); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.TypeWiseSalesDetail.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.// w w w. j av a2 s. com * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String ac_cd = request.getParameter("ac_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final String sr_cd = request.getParameter("sr_cd"); final String type_cd = request.getParameter("type_cd"); final String brand_cd = request.getParameter("brnad_cd"); final String model_cd = request.getParameter("model_cd"); final String branch_cd = request.getParameter("branch_cd"); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "SELECT m.model_name,v.branch_cd,v.ref_no,a.fname,s.sr_name,v.v_date,t.TYPE_NAME,b.brand_name," + " CASE WHEN v1.IMEI_NO ='' THEN v1.SERAIL_NO ELSE v1.IMEI_NO END AS IMEI_NO,(v1.qty) AS pcs,(v1.RATE) AS tot_sales," + " t1.ac_name AS pur_party,MOBILE1,email FROM VILSHD v LEFT JOIN vilsdt v1 ON v.REF_NO=v1.REF_NO " + " LEFT JOIN SERIESMST s ON v1.SR_CD=s.SR_CD LEFT JOIN acntmst a ON v.ac_cd=a.ac_cd " + " LEFT JOIN MODELMST m ON s.MODEL_CD=m.MODEL_CD LEFT JOIN TYPEMST t ON m.TYPE_CD=t.TYPE_CD" + " left join brandmst b on b.brand_cd=m.brand_cd left join phbkmst p1 on p1.ac_cd=a.ac_cd" + " LEFT JOIN tag t1 ON t1.ref_no=v1.pur_tag_no WHERE v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd; } sql += " order by v.branch_cd"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("fname", viewDataRs.getString("fname")); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("sr_name", viewDataRs.getString("sr_name")); object.addProperty("v_date", viewDataRs.getString("v_date")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs")); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales")); object.addProperty("IMEI_NO", viewDataRs.getString("IMEI_NO")); object.addProperty("brand_name", viewDataRs.getString("brand_name")); object.addProperty("PUR_PARTY", viewDataRs.getString("PUR_PARTY")); object.addProperty("REF_NO", viewDataRs.getString("REF_NO")); object.addProperty("MOBILE1", (viewDataRs.getString("MOBILE1") == null) ? "" : viewDataRs.getString("MOBILE1")); object.addProperty("EMAIL", (viewDataRs.getString("EMAIL") == null) ? "" : viewDataRs.getString("EMAIL")); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } sql = "select m.model_name,v.branch_cd,v.ref_no,a.fname,s.sr_name,v.v_date,t.TYPE_NAME,b.brand_name" + " ,case when IMEI_NO ='' then SERAIL_NO else IMEI_NO end as IMEI_NO,(v1.qty) as pcs,(v1.RATE) as tot_sales" + " ,MOBILE1,email from srhd v left join srdt v1 on" + " v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD left join acntmst a on v.ac_cd=a.ac_cd " + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join brandmst b on b.brand_cd=m.brand_cd" + " left join TYPEMST t on m.TYPE_CD=t.TYPE_CD left join phbkmst p1 on p1.ac_cd=a.ac_cd where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "'"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and m.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } if (!branch_cd.equalsIgnoreCase("0")) { sql += " and v.branch_cd=" + branch_cd; } pstLocal = dataConnection.prepareStatement(sql); viewDataRs = pstLocal.executeQuery(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("fname", viewDataRs.getString("fname")); object.addProperty("sr_name", viewDataRs.getString("sr_name")); object.addProperty("model_name", viewDataRs.getString("model_name")); object.addProperty("brand_name", viewDataRs.getString("brand_name")); object.addProperty("v_date", viewDataRs.getString("v_date")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs") * -1); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales") * -1); object.addProperty("IMEI_NO", viewDataRs.getString("IMEI_NO")); object.addProperty("REF_NO", viewDataRs.getString("REF_NO")); object.addProperty("MOBILE1", (viewDataRs.getString("MOBILE1") == null) ? "" : viewDataRs.getString("MOBILE1")); object.addProperty("EMAIL", (viewDataRs.getString("EMAIL") == null) ? "" : viewDataRs.getString("EMAIL")); object.addProperty("PUR_PARTY", ""); object.addProperty("branch_cd", viewDataRs.getString("branch_cd")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:account.WithoutTagSalesReport.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w ww. jav a 2 s.c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final String ac_cd = request.getParameter("ac_cd"); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final String sr_cd = request.getParameter("sr_cd"); final String type_cd = request.getParameter("type_cd"); final String brand_cd = request.getParameter("brnad_cd"); final String model_cd = request.getParameter("model_cd"); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "select v.ref_no,a.fname,s.sr_name,v.v_date,t.TYPE_NAME,case when IMEI_NO ='' then SERAIL_NO else IMEI_NO end as IMEI_NO,(v1.qty) as pcs,(v1.RATE) as tot_sales from VILSHD v left join vilsdt v1 on v.REF_NO=v1.REF_NO " + " left join SERIESMST s on v1.SR_CD=s.SR_CD left join acntmst a on v.ac_cd=a.ac_cd " + " left join MODELMST m on s.MODEL_CD=m.MODEL_CD left join TYPEMST t on m.TYPE_CD=t.TYPE_CD where v.IS_DEL=0 " + " and v.v_date>='" + from_date + "' " + " and v.v_date<='" + to_date + "' and v1.PUR_TAG_NO=''"; if (!type_cd.equalsIgnoreCase("")) { sql += " and m.type_cd='" + type_cd + "' "; } if (!sr_cd.equalsIgnoreCase("")) { sql += " and s.SR_CD='" + sr_cd + "'"; } else if (!brand_cd.equalsIgnoreCase("")) { sql += " and s.brand_cd='" + brand_cd + "'"; } else if (!model_cd.equalsIgnoreCase("")) { sql += " and s.model_cd='" + model_cd + "'"; } if (!ac_cd.equalsIgnoreCase("")) { sql += " and v.ac_cd='" + ac_cd + "'"; } pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("fname", viewDataRs.getString("fname")); object.addProperty("sr_name", viewDataRs.getString("sr_name")); object.addProperty("v_date", viewDataRs.getString("v_date")); object.addProperty("type_name", viewDataRs.getString("type_name")); object.addProperty("pcs", viewDataRs.getInt("pcs")); object.addProperty("tot_sales", viewDataRs.getDouble("tot_sales")); object.addProperty("IMEI_NO", viewDataRs.getString("IMEI_NO")); object.addProperty("REF_NO", viewDataRs.getString("REF_NO")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } } response.getWriter().print(jResultObj); }
From source file:accountMaster.GetAccountMaster.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w ww . ja v a 2 s . c om * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final String ac_name = request.getParameter("AC_NAME"); Library lb = Library.getInstance(); if (dataConnection != null) { try { String sql = "select a.OPB_AMT,a.OPB_EFF,a.AC_CD,FNAME,g.GROUP_NAME,g.GRP_CD,CST,TIN,a1.ADD1,p.MOBILE1,a.card_no,p.EMAIL,gst_no from ACNTMST a left join GROUPMST g on a.GRP_CD=g.GRP_CD \n" + "left join ADBKMST a1 on a.AC_CD=a1.AC_CD left join PHBKMST p on a.AC_CD=p.AC_CD "; if (ac_name != null) { sql += " where fname like '%" + ac_name + "%'"; } sql += " order by fname"; PreparedStatement pstLocal = dataConnection.prepareStatement(sql); ResultSet rsLocal = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (rsLocal.next()) { JsonObject object = new JsonObject(); object.addProperty("AC_CD", rsLocal.getString("AC_CD")); object.addProperty("FNAME", rsLocal.getString("FNAME")); object.addProperty("GROUP_NAME", rsLocal.getString("GROUP_NAME")); object.addProperty("GRP_CD", rsLocal.getString("GRP_CD")); object.addProperty("CST", rsLocal.getString("CST")); object.addProperty("TIN", rsLocal.getString("TIN")); object.addProperty("ADD1", rsLocal.getString("ADD1")); object.addProperty("MOBILE1", rsLocal.getString("MOBILE1")); object.addProperty("EMAIL", rsLocal.getString("EMAIL")); object.addProperty("CARD_NO", rsLocal.getString("CARD_NO")); object.addProperty("OPB_AMT", rsLocal.getString("OPB_AMT")); object.addProperty("OPB_EFF", rsLocal.getString("OPB_EFF")); object.addProperty("GST_NO", rsLocal.getString("GST_NO")); array.add(object); } jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } finally { lb.closeConnection(dataConnection); } } response.getWriter().print(jResultObj); }