public static String getAllLog(
     Connection conn,
     HttpServletRequest request,
     String companyid,
     String limit,
     String offset,
     String searchString,
     String dateString)
     throws ServiceException {
   ResultSet rs1 = null;
   String returnStr = "";
   String[] searchStrObj = new String[] {"params", "actiontext"};
   String myLikeString = StringUtil.getMySearchString(searchString, "and", searchStrObj);
   PreparedStatement pstmt = null;
   JSONObject resobj = new JSONObject();
   JSONObject tempobj = new JSONObject();
   JSONArray jarr = new JSONArray();
   try {
     int count1 = 0;
     pstmt =
         conn.prepareStatement(
             "select count(*) AS count from actionlog al inner join actions a on al.actionid = a.actionid where companyid = ?"
                 + myLikeString
                 + dateString);
     pstmt.setString(1, companyid);
     StringUtil.insertParamSearchString(2, pstmt, searchString, searchStrObj.length);
     rs1 = pstmt.executeQuery();
     if (rs1.next()) {
       count1 = rs1.getInt("count");
     }
     String query1 =
         "select logid, al.actionid, actionby, projectid, params, timestamp from actionlog al inner join actions a on al.actionid = a.actionid where companyid = ?"
             + myLikeString
             + dateString
             + " order by timestamp DESC limit ? offset ?";
     pstmt = conn.prepareStatement(query1);
     pstmt.setString(1, companyid);
     int cnt = StringUtil.insertParamSearchString(2, pstmt, searchString, searchStrObj.length);
     pstmt.setInt(cnt++, Integer.parseInt(limit));
     pstmt.setInt(cnt++, Integer.parseInt(offset));
     rs1 = pstmt.executeQuery();
     while (rs1.next()) {
       tempobj = getJsonForAction(conn, pstmt, rs1, request, "all");
       jarr.put(tempobj);
     }
     resobj.put("data", jarr);
     resobj.put("count", count1);
     returnStr = resobj.toString();
   } catch (ServiceException ex) {
     throw ServiceException.FAILURE("AuditTrail.getAllLog", ex);
   } catch (SQLException ex) {
     throw ServiceException.FAILURE("AuditTrail.getAllLog", ex);
   } finally {
     return returnStr;
   }
 }
Пример #2
0
 public void setCustomData(CrmAccount crmAcc, JSONArray cstmData) {
   StringBuffer fields = new StringBuffer("accountid,company");
   StringBuffer qmarks = new StringBuffer("?,?");
   ArrayList params = new ArrayList();
   params.add(crmAcc.getAccountid());
   params.add(crmAcc.getCompany().getCompanyID());
   boolean hasValue = false;
   try {
     for (int i = 0; i < cstmData.length(); i++) {
       JSONObject jobj = cstmData.getJSONObject(i);
       if (jobj.has(Constants.Crm_custom_field)) {
         String fieldname = jobj.getString(Constants.Crm_custom_field);
         String fielddbname = jobj.getString(fieldname);
         String fieldValue = jobj.getString(fielddbname);
         hasValue = true;
         fielddbname = fielddbname.replace("c", "C");
         Integer xtype = Integer.parseInt(jobj.getString("xtype"));
         if (!StringUtil.isNullOrEmpty(fieldValue)
             && !StringUtil.isNullOrEmpty(fieldValue.trim())
             && !fieldValue.equalsIgnoreCase(Constants.field_data_undefined)) {
           fields.append(',').append(fielddbname);
           qmarks.append(",?");
           params.add(fieldValue);
         } else {
           if (xtype == 7 || xtype == 8 || xtype == 4) {
             fields.append(',').append(fielddbname);
             qmarks.append(",?");
             params.add(null);
           } else {
             fields.append(',').append(fielddbname);
             qmarks.append(",?");
             params.add("");
           }
         }
       }
     }
   } catch (NumberFormatException e) {
     e.printStackTrace();
   } catch (JSONException e) {
     e.printStackTrace();
   }
   if (hasValue) {
     StringBuffer sql =
         new StringBuffer("insert into crmaccountcustomdata (")
             .append(fields)
             .append(")VALUES(")
             .append(qmarks)
             .append(')');
     updateJDBC(sql.toString(), params.toArray());
   }
 }
 private String[] getResourcesColHeader(JSONArray j, JSONArray data) throws JSONException {
   String[] res = new String[(j.length() * 2) + 2];
   int i = 0, k = 0;
   try {
     for (i = 0, k = 0; i < res.length || k < j.length(); i += 2, k++) {
       res[i] = (String) j.getJSONObject(k).getString("type");
       res[i + 1] = (String) j.getJSONObject(k).getString("count");
     }
   } catch (JSONException e) {
   } catch (Exception e) {
   } finally {
     res[i] = "Total Resources";
     res[i + 1] = (String) data.getJSONObject(0).getString("Total Resources");
   }
   return res;
 }
  public static JSONArray getProductTypesJson(HttpServletRequest request, List list)
      throws ServiceException, SessionExpiredException {
    JSONArray jArr = new JSONArray();
    try {
      Iterator itr = list.iterator();
      while (itr.hasNext()) {
        Producttype ptype = (Producttype) itr.next();
        JSONObject obj = new JSONObject();
        obj.put("id", ptype.getID());
        obj.put("name", ptype.getName());
        jArr.put(obj);
      }

    } catch (JSONException ex) {
      throw ServiceException.FAILURE("getProductTypesJson : " + ex.getMessage(), ex);
    }
    return jArr;
  }
  /**
   * this function will return a jsonstring given a resultset in a format that is required for
   * WTF.Grid just use this inside the ur jsp file and set the url for the grid datastore as that
   * jsp file
   */
  public JSONObject GetJsonForGrid(SqlRowSet rs) {
    JSONObject jb = null;
    try {
      SqlRowSetMetaData rsmd = rs.getMetaData();
      JSONArray jArr = new JSONArray();
      while (rs.next()) {
        JSONObject jobj = new JSONObject();
        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          if (rs.getObject(i) != null) jobj.put(rsmd.getColumnName(i), rs.getObject(i));
          else jobj.put(rsmd.getColumnName(i), "");
        }
        jArr.put(jobj);
      }
      if (jArr.length() > 0) jb = new JSONObject().put("data", jArr);
    } catch (Exception ex) {
      System.out.println("exception -->" + ex);
    }

    return jb;
  }
 private String[] getColValues(String[] colIndex, JSONArray j) throws JSONException {
   String[] val = new String[colIndex.length];
   try {
     for (int i = 0; i < val.length; i++) {
       if (colIndex[i].contains("    ")) {
         val[i] = " ";
       } else {
         val[i] = (String) j.getJSONObject(0).getString(colIndex[i]);
       }
     }
   } catch (JSONException e) {
   } catch (Exception e) {
   }
   return val;
 }
  public ModelAndView getDocs(HttpServletRequest request, HttpServletResponse response) {
    JSONObject jobj = new JSONObject();
    JSONObject jobj1 = new JSONObject();
    KwlReturnObject kmsg = null;
    KwlReturnObject result = null;
    try {

      HashMap<String, Object> requestParams = new HashMap<String, Object>();
      ArrayList filter_names = new ArrayList(), filter_values = new ArrayList();
      JSONArray jarr = new JSONArray();
      String userid = "";
      int start, limit;

      userid = request.getParameter("userid");
      String currentuser = AuthHandler.getUserid(request);
      String ss = request.getParameter("ss");
      if (request.getParameter("start") == null) {
        start = 0;
        limit = 15;
      } else {
        start = Integer.parseInt(request.getParameter("start"));
        limit = Integer.parseInt(request.getParameter("limit"));
      }

      if (request.getParameter("applicant").equalsIgnoreCase("applicant")) {

        filter_names.add("recid");
        filter_values.add(userid);

        filter_names.add("docid.deleted");
        filter_values.add(false);

        filter_names.add("docid.referenceid");
        filter_values.add(userid);

        requestParams.put("filter_names", filter_names);
        requestParams.put("filter_values", filter_values);
        requestParams.put("ss", ss);
        requestParams.put("searchcol", new String[] {"docid.docname", "docid.docdesc"});
        requestParams.put("start", start);
        requestParams.put("limit", limit);
        requestParams.put("allflag", false);
        result = hrmsExtApplDocsDAOObj.getDocs(requestParams);

        Iterator ite = result.getEntityList().iterator();
        while (ite.hasNext()) {
          HrmsDocmap docs = (HrmsDocmap) ite.next();
          JSONObject tmpObj = new JSONObject();
          tmpObj.put("docid", docs.getDocid().getDocid());
          tmpObj.put("docname", docs.getDocid().getDocname());
          tmpObj.put("docdesc", docs.getDocid().getDocdesc());
          tmpObj.put("uploadedby", docs.getDocid().getUploadedby());
          Float dsize = Math.max(0, Float.parseFloat(docs.getDocid().getDocsize()) / 1024);
          tmpObj.put("docsize", String.valueOf(dsize));
          tmpObj.put(
              "uploaddate",
              AuthHandler.getDateFormatter(request).format(docs.getDocid().getUploadedon()));
          jarr.put(tmpObj);
        }

      } else {
        filter_names.add("recid");
        filter_values.add(userid);

        filter_names.add("docid.deleteflag");
        filter_values.add(0);

        requestParams.put("filter_names", filter_names);
        requestParams.put("filter_values", filter_values);
        requestParams.put("ss", ss);
        requestParams.put("searchcol", new String[] {"docid.docname", "docid.docdesc"});
        requestParams.put("start", start);
        requestParams.put("limit", limit);
        requestParams.put("allflag", false);

        result = documentDAOObj.getDocs(requestParams);

        Iterator ite = result.getEntityList().iterator();
        while (ite.hasNext()) {
          Docmap docs = (Docmap) ite.next();
          JSONObject tmpObj = new JSONObject();
          tmpObj.put("docid", docs.getDocid().getDocid());
          tmpObj.put("docname", docs.getDocid().getDocname());
          tmpObj.put("docdesc", docs.getDocid().getDocdesc());
          tmpObj.put(
              "uploadedby",
              docs.getDocid().getUserid().getFirstName()
                  + " "
                  + docs.getDocid().getUserid().getLastName());
          Float dsize = Math.max(0, Float.parseFloat(docs.getDocid().getDocsize()) / 1024);
          tmpObj.put("docsize", String.valueOf(dsize));
          tmpObj.put(
              "uploaddate",
              AuthHandler.getDateFormatter(request).format(docs.getDocid().getUploadedon()));
          jarr.put(tmpObj);
        }
      }
      jobj.put("data", jarr);
      jobj.put("count", result.getRecordTotalCount());
      jobj1.put("data", jobj.toString());
      jobj1.put("valid", true);
    } catch (Exception e) {
      System.out.println(e.getMessage());
    }
    return new ModelAndView("jsonView", "model", jobj1.toString());
  }
 public static JSONArray getProductsJson(HttpServletRequest request, List list)
     throws JSONException, ServiceException {
   Iterator itr = list.iterator();
   JSONArray jArr = new JSONArray();
   Producttype producttype = new Producttype();
   String productid = request.getParameter("productid");
   Boolean nonSaleInventory = Boolean.parseBoolean((String) request.getParameter("loadInventory"));
   while (itr.hasNext()) {
     Object[] row = (Object[]) itr.next();
     Product product = (Product) row[0];
     Product parentProduct = product.getParent();
     if (product.getID().equals(productid)) continue;
     ProductCyclecount pcObject = (ProductCyclecount) row[8];
     JSONObject obj = new JSONObject();
     obj.put("productid", product.getID());
     obj.put("productname", product.getName());
     obj.put("desc", product.getDescription());
     UnitOfMeasure uom = product.getUnitOfMeasure();
     obj.put("uomid", uom == null ? "" : uom.getID());
     obj.put("uomname", uom == null ? "" : uom.getName());
     obj.put("precision", uom == null ? 0 : (Integer) uom.getAllowedPrecision());
     obj.put("leadtime", product.getLeadTimeInDays());
     obj.put("syncable", product.isSyncable());
     obj.put("reorderlevel", product.getReorderLevel());
     obj.put("reorderquantity", product.getReorderQuantity());
     obj.put(
         "purchaseaccountid",
         (product.getPurchaseAccount() != null ? product.getPurchaseAccount().getID() : ""));
     obj.put(
         "salesaccountid",
         (product.getSalesAccount() != null ? product.getSalesAccount().getID() : ""));
     obj.put(
         "purchaseretaccountid",
         (product.getPurchaseReturnAccount() != null
             ? product.getPurchaseReturnAccount().getID()
             : ""));
     obj.put(
         "salesretaccountid",
         (product.getSalesReturnAccount() != null ? product.getSalesReturnAccount().getID() : ""));
     obj.put("vendor", (product.getVendor() != null ? product.getVendor().getID() : ""));
     obj.put("vendornameid", (product.getVendor() != null ? product.getVendor().getName() : ""));
     obj.put(
         "producttype",
         (product.getProducttype() != null ? product.getProducttype().getID() : ""));
     obj.put(
         "vendorphoneno",
         (product.getVendor() != null ? product.getVendor().getContactNumber() : ""));
     obj.put("vendoremail", (product.getVendor() != null ? product.getVendor().getEmail() : ""));
     obj.put("type", (product.getProducttype() != null ? product.getProducttype().getName() : ""));
     obj.put("pid", product.getProductid());
     obj.put("parentuuid", parentProduct == null ? "" : parentProduct.getID());
     obj.put("parentid", parentProduct == null ? "" : parentProduct.getProductid());
     obj.put("parentname", parentProduct == null ? "" : parentProduct.getName());
     obj.put("level", row[1]);
     obj.put("leaf", row[2]);
     obj.put("purchaseprice", row[3]);
     obj.put("saleprice", row[4]);
     obj.put("quantity", (row[5] == null ? 0 : row[5]));
     obj.put("initialquantity", (row[6] == null ? 0 : row[6]));
     obj.put("initialprice", (row[7] == null ? 0 : row[7]));
     obj.put("salespricedatewise", (row[9] == null ? 0 : row[9]));
     obj.put("purchasepricedatewise", (row[10] == null ? 0 : row[10]));
     obj.put("initialsalesprice", (row[11] == null ? 0 : row[11]));
     obj.put("ccountinterval", pcObject != null ? pcObject.getCountInterval() : "");
     obj.put("ccounttolerance", pcObject != null ? pcObject.getTolerance() : "");
     SimpleDateFormat sdf = new SimpleDateFormat("MMMM d, yyyy hh:mm:ss aa");
     //  obj.put("createdon", (row[12]==null?"":sdf.format(row[12])));
     //            jArr.put(obj);
     if (nonSaleInventory && obj.get("producttype").equals(producttype.Inventory_Non_Sales)) {
       // Do Nothing
     } else {
       jArr.put(obj);
     }
   }
   return jArr;
 }
 private ByteArrayOutputStream getCsvData(
     JSONArray src, JSONArray store, HttpServletRequest request) throws ServiceException {
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   StringBuilder resSB = new StringBuilder();
   JSONObject temp = null;
   int fieldListLength = 0;
   try {
     String header = "";
     temp =
         new JSONObject(
             "{data:[Project Start,Baseline Start,Actual Start,"
                 + "Start Variance,Project Finish,Baseline Finish,Actual Finish,Finish Variance,"
                 + "Scheduled,Baseline,Variance,Remaining,Actual,Scheduled,Baseline,Variance,"
                 + "Remaining,Actual,Scheduled,Baseline,Variance,Remaining,Actual,Percent Complete,"
                 + "Task not yet Started,Task in progress,Task Completed,Parent Tasks, Total Tasks]}");
     JSONArray colHeader = temp.getJSONArray("data");
     temp =
         new JSONObject(
             "{data:[Project Start Date,Baseline Start Date,Project Actual Start Date,"
                 + "Start Variance,Project End Date,Baseline End Date,Project End Date,End Variance,"
                 + "Scheduled Duration,Baseline Duration,Duration Variance,Remaining Duration,"
                 + "Actual Duration,Scheduled Cost,Baseline Cost,Cost Variance,Remaining Cost,Actual Cost,"
                 + "Scheduled Work,Baseline Work,Work Variance,Remaining Work,Actual Work,Percent Complete,"
                 + "Unstarted Tasks,Inprogress Tasks,Completed Tasks,Parent Tasks,Total Tasks]}");
     JSONArray fieldList = temp.getJSONArray("data");
     fieldListLength = fieldList.length();
     for (int i = 0; i < src.length(); i++) {
       colHeader.put(src.getJSONObject(i).getString("type"));
       fieldList.put("count");
     }
     fieldList.put("Total Resources");
     colHeader.put("Total Resources");
     for (int i = 0; i < colHeader.length(); i++) {
       header += "\"" + colHeader.get(i).toString() + "\",";
     }
     header = header.substring(0, (header.length() - 1));
     header += "\n";
     resSB.append(header);
     String dataIndexArrStr[] = new String[fieldList.length()];
     for (int i = 0; i < fieldList.length(); i++) dataIndexArrStr[i] = fieldList.get(i).toString();
     for (int i = 0; i < store.length(); i++) {
       temp = store.getJSONObject(i);
       String dstr = "";
       for (int j = 0; j < fieldListLength; j++)
         dstr += "\"" + temp.getString(dataIndexArrStr[j]) + "\",";
       dstr = dstr.substring(0, (dstr.length() - 1));
       dstr += "\n";
       resSB.append(dstr);
     }
     for (int i = 0; i < src.length(); i++) {
       temp = src.getJSONObject(i);
       String dstr = "";
       dstr += "\"" + temp.getString("count") + "\",";
       dstr = dstr.substring(0, (dstr.length() - 1));
       resSB.append(dstr);
     }
     String dstr = "";
     dstr += "\"" + store.getJSONObject(0).getString("Total Resources") + "\",";
     resSB.append(dstr);
     baos.write(resSB.toString().getBytes());
     baos.close();
   } catch (IOException ex) {
     throw ServiceException.FAILURE("ExportProjectReport.getCsvData", ex);
   } catch (JSONException e) {
     throw ServiceException.FAILURE("ExportProjectReport.getCsvData", e);
   } catch (Exception e) {
     throw ServiceException.FAILURE("ExportProjectReport.getCsvData", e);
   }
   return baos;
 }
 public static String getActionLog(Connection conn, HttpServletRequest request)
     throws ServiceException {
   ResultSet rs = null;
   String returnStr = "";
   PreparedStatement pstmt = null;
   String searchString = request.getParameter("ss");
   String limit = request.getParameter("limit");
   String offset = request.getParameter("start");
   JSONObject resobj = new JSONObject();
   JSONObject tempobj = new JSONObject();
   JSONArray jarr = new JSONArray();
   String likeClause = "";
   try {
     String companyid = AuthHandler.getCompanyid(request);
     String loginid = AuthHandler.getUserid(request);
     String[] searchStrObj = new String[] {"params", "actiontext"};
     String myLikeString = StringUtil.getMySearchString(searchString, "and", searchStrObj);
     String flag = "";
     int count1 = 0;
     String type = request.getParameter("type");
     String dateSting = getDateString(conn, request, loginid);
     if (StringUtil.isNullOrEmpty(type)) {
       flag = "no";
       returnStr = getAllLog(conn, request, companyid, limit, offset, searchString, dateSting);
     } else {
       switch (Integer.parseInt(type)) {
         case 0:
           flag = "projname";
           returnStr =
               getLog(conn, request, companyid, limit, offset, searchString, dateSting, flag);
           flag = "no";
           break;
         case 1:
           String admintype = request.getParameter("admintype");
           if (admintype.equals("0")) {
             likeClause = "and al.actionid like '31%' ";
           } else if (admintype.equals("1")) {
             likeClause = "and al.actionid like '32%' ";
           } else {
             likeClause = "and al.actionid like '33%' or al.actionid like '40%' ";
           }
           flag = "admin";
           break;
         case 2:
           flag = "doc";
           likeClause = "and al.actionid like '21%' ";
           break;
         case 3:
           flag = "all";
           likeClause = "and projectid != '' ";
           break;
         case 4:
           flag = "admin";
           likeClause =
               "and (al.actionid like '31%' or al.actionid like '32?' or al.actionid like '33?' or al.actionid like '40%')";
           break;
         case 5:
           flag = "all";
           likeClause = "and (al.actionid like '41%')";
           break;
       }
     }
     if (!flag.equals("no")) {
       count1 =
           getCountQuery(
               conn,
               pstmt,
               companyid,
               likeClause,
               myLikeString,
               searchStrObj,
               searchString,
               dateSting);
       String query =
           "select logid, al.actionid, actionby, params, timestamp, projectid from actionlog al inner join actions a on al.actionid = a.actionid where companyid = ? "
               + likeClause
               + myLikeString
               + dateSting
               + " order by timestamp DESC limit ? offset ?";
       pstmt = conn.prepareStatement(query);
       pstmt.setString(1, companyid);
       int cnt = StringUtil.insertParamSearchString(2, pstmt, searchString, searchStrObj.length);
       pstmt.setInt(cnt++, Integer.parseInt(limit));
       pstmt.setInt(cnt++, Integer.parseInt(offset));
       rs = pstmt.executeQuery();
       while (rs.next()) {
         tempobj = getJsonForAction(conn, pstmt, rs, request, flag);
         jarr.put(tempobj);
       }
       resobj.put("data", jarr);
       resobj.append("count", count1);
       returnStr = resobj.toString();
     }
   } catch (ServiceException ex) {
     Logger.getLogger(AuditTrail.class.getName()).log(Level.SEVERE, null, ex);
   } catch (Exception ex) {
     throw ServiceException.FAILURE("AuditTrail.getActionLog :" + ex.getMessage(), ex);
   } finally {
     return returnStr;
   }
 }