List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.demo.bean.ExportarGestionesBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0);// ww w . j a v a 2s .co m HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:com.demo.bean.ReportePagosBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0);//from w w w .j a va 2 s . c o m HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } LogSistema .guardarlog(this.getClass().getName() + " Method: postProcessXLS, se exporto un reporte de pagos "); }
From source file:com.demo.bean.VisitaDatosBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0);/*from w w w .j ava 2 s .com*/ HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } LogSistema.guardarlog(this.getClass().getName() + " Method: postProcessXLS, se exporto un reporte de visitas domiciliarias"); }
From source file:com.dexter.fms.mbean.FleetMBean.java
License:Open Source License
@SuppressWarnings("unchecked") public void UploadZonMaintenance() { if (getMaintenanceExcel() != null) { GeneralDAO gDAO = new GeneralDAO(); try {/*from www.j ava2 s. co m*/ ByteArrayInputStream byteIn = new ByteArrayInputStream(getMaintenanceExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { String timestamp = null, unit = null, comment1 = null, comment2 = null, comment3 = null, value = null, updateTime = null; Row row = rowIterator.next(); if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: timestamp = val; break; case 1: unit = val; break; case 2: comment1 = val; break; case 3: comment2 = val; break; case 4: comment3 = val; break; case 5: value = val; break; case 7: updateTime = val; break; } String regNo = null; Vehicle v = null; Vendor vendor = null; try { if (unit != null && unit.contains("-")) { regNo = unit.substring(0, unit.indexOf("-")); } else if (unit != null) regNo = unit; if (regNo != null) regNo = regNo.trim(); } catch (Exception ex) { System.out.println("Reg No parsing exception on row index: " + pos); ex.printStackTrace(); } if (regNo != null) { Query q = gDAO.createQuery( "Select e from Vehicle e where e.registrationNo=:regNo and e.partner.id=:partner_id"); q.setParameter("regNo", regNo); q.setParameter("partner_id", partner_id); Object vobj = gDAO.search(q, 1); if (vobj != null) { v = (Vehicle) vobj; } } if (v != null) { if (comment2 != null && comment2.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Vendor e where e.name=:vname and e.partner.id=:partner_id"); q.setParameter("vname", comment2); q.setParameter("partner_id", partner_id); Object venobjs = gDAO.search(q, 0); if (venobjs != null) { List<Vendor> venlist = (List<Vendor>) venobjs; for (Vendor e : venlist) vendor = e; } if (vendor == null) { vendor = new Vendor(); vendor.setCreatedBy(dashBean.getUser()); vendor.setCrt_dt(new Date()); vendor.setName(comment2); vendor.setPartner(getPartner()); gDAO.save(vendor); } } Date start_dt = null, end_dt = null; if (comment3 != null && comment3.trim().length() > 0) { } if (comment1 != null && comment1.trim().length() > 0 && comment1.startsWith("Routine Service")) { VehicleRoutineMaintenance vrm = new VehicleRoutineMaintenance(); } else if (comment1 != null && comment1.trim().length() > 0) { VehicleAdHocMaintenance vahm = new VehicleAdHocMaintenance(); } } } } pos += 1; } } catch (Exception ex) { ex.printStackTrace(); } gDAO.destroy(); } }
From source file:com.dexter.fms.mbean.FleetMBean.java
License:Open Source License
@SuppressWarnings("unchecked") public void BatchLoadVehicles() { if (getVehiclesBatchExcel() != null) { String naration = "Batch load vehicles: "; GeneralDAO gDAO = new GeneralDAO(); try {/*from w ww . jav a 2s .c o m*/ ByteArrayInputStream byteIn = new ByteArrayInputStream(getVehiclesBatchExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { if (!isLicenseAvailable()) { msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "You have used up your license. Please purchase more license to add more vehicles!"); FacesContext.getCurrentInstance().addMessage(null, msg); break; } Row row = rowIterator.next(); String fleet_nm = "", vehicleType = "", vehicleMaker = "", vehicleModel = "", modelYr = "", trackerID = "", regNo = "", engineNo = "", chassisNo = ""; String department = "", region = ""; String purchase_date = "", purchased_amount = "", purchased_from = "", fuel_type = "", tyre_size = "", tank_capacity = ""; String calibrated_capacity = "", color = "", sim_no = "", unit_of_measurement = "", card_no = ""; if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: fleet_nm = val; break; case 1: vehicleType = val; break; case 2: vehicleMaker = val; break; case 3: vehicleModel = val; break; case 4: modelYr = val; break; case 5: trackerID = val; break; case 6: regNo = val; break; case 7: engineNo = val; break; case 8: chassisNo = val; break; case 9: department = val; break; case 10: region = val; break; case 11: purchase_date = val; break; case 12: purchased_amount = val; break; case 13: purchased_from = val; break; case 14: fuel_type = val; break; case 15: tyre_size = val; break; case 16: tank_capacity = val; break; case 17: calibrated_capacity = val; break; case 18: color = val; break; case 19: sim_no = val; break; case 20: unit_of_measurement = val; break; case 21: card_no = val; break; } } boolean createModel = false; VehicleType vt = null; VehicleMake vm = null; VehicleModel vmd = null; Fleet fleet = null; Department deptObj = null; Region regionObj = null; boolean conti = true; if (regNo != null && regNo.trim().length() > 0) { Vehicle v = null; Query q = gDAO.createQuery( "Select e from Vehicle e where e.registrationNo = :registrationNo"); q.setParameter("registrationNo", regNo); Object vObj = gDAO.search(q, 0); if (vObj != null) { Vector<Vehicle> vList = (Vector<Vehicle>) vObj; for (Vehicle e : vList) v = e; } if (v != null) conti = false; } if (conti) { if (fleet_nm != null && fleet_nm.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Fleet e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", fleet_nm); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Fleet> objsList = (Vector<Fleet>) objs; for (Fleet e : objsList) fleet = e; } if (fleet == null && isAutoCreate()) { fleet = new Fleet(); fleet.setCreatedBy(dashBean.getUser()); fleet.setCrt_dt(new Date()); fleet.setName(fleet_nm); fleet.setPartner(getPartner()); fleet.setDefaultFleet(false); ret = gDAO.save(fleet); if (!ret) break; } else if (fleet == null && !isAutoCreate()) { ret = false; gDAO.setMessage( "Fleet: '" + fleet_nm + "' does not exist for vehicle: " + regNo); break; } } if (department != null && department.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Department e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", department); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Department> objsList = (Vector<Department>) objs; for (Department e : objsList) deptObj = e; } if (deptObj == null && isAutoCreate()) { deptObj = new Department(); deptObj.setCreatedBy(dashBean.getUser()); deptObj.setCrt_dt(new Date()); deptObj.setName(department); deptObj.setPartner(getPartner()); ret = gDAO.save(deptObj); if (!ret) break; } else if (deptObj == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Department: '" + department + "' does not exist for vehicle: " + regNo); break; } } if (region != null && region.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Region e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", region); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Region> objsList = (Vector<Region>) objs; for (Region e : objsList) regionObj = e; } if (regionObj == null && isAutoCreate()) { regionObj = new Region(); regionObj.setCreatedBy(dashBean.getUser()); regionObj.setCrt_dt(new Date()); regionObj.setName(region); regionObj.setPartner(getPartner()); ret = gDAO.save(regionObj); if (!ret) break; } else if (regionObj == null && !isAutoCreate()) { ret = false; gDAO.setMessage( "Region: '" + region + "' does not exist for vehicle: " + regNo); break; } } if (vehicleType != null && vehicleType.trim().length() > 0) { // search for existing vehicle type and maker Query q = gDAO.createQuery("Select e from VehicleType e where e.name = :name"); q.setParameter("name", vehicleType.trim()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleType> vtList = (Vector<VehicleType>) qObj; for (VehicleType e : vtList) vt = e; } if (vt == null && isAutoCreate()) { vt = new VehicleType(); vt.setCreatedBy(dashBean.getUser()); vt.setCrt_dt(new Date()); vt.setName(vehicleType.trim()); vt.setPartner(getPartner()); ret = gDAO.save(vt); if (!ret) break; createModel = true; } else if (vt == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Vehicle Type: '" + vehicleType + "' does not exist for vehicle: " + regNo); break; } } if (vehicleMaker != null && vehicleMaker.trim().length() > 0) { Query q = gDAO.createQuery("Select e from VehicleMake e where e.name = :name"); q.setParameter("name", vehicleMaker.trim()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleMake> vmList = (Vector<VehicleMake>) qObj; for (VehicleMake e : vmList) vm = e; } if (vm == null && isAutoCreate()) { vm = new VehicleMake(); vm.setCreatedBy(dashBean.getUser()); vm.setCrt_dt(new Date()); vm.setName(vehicleMaker.trim()); vm.setPartner(getPartner()); ret = gDAO.save(vm); if (!ret) break; createModel = true; } else if (vm == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Vehicle Make: '" + vehicleMaker + "' does not exist for vehicle: " + regNo); break; } } if (vehicleModel != null && vehicleModel.trim().length() > 0) { if (createModel) { vmd = new VehicleModel(); vmd.setCreatedBy(dashBean.getUser()); vmd.setCrt_dt(new Date()); vmd.setMaker(vm); vmd.setType(vt); vmd.setName(vehicleModel); vmd.setYear(modelYr); vmd.setPartner(getPartner()); ret = gDAO.save(vmd); if (!ret) break; } else { Query q = gDAO.createQuery( "Select e from VehicleModel e where e.name = :name and e.year=:year and e.maker=:maker and e.type=:type"); q.setParameter("name", vehicleModel.trim()); q.setParameter("year", modelYr); q.setParameter("maker", vm); q.setParameter("type", vt); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleModel> vmList = (Vector<VehicleModel>) qObj; for (VehicleModel e : vmList) vmd = e; } if (vmd == null && isAutoCreate()) { vmd = new VehicleModel(); vmd.setCreatedBy(dashBean.getUser()); vmd.setCrt_dt(new Date()); vmd.setMaker(vm); vmd.setType(vt); vmd.setName(vehicleModel); vmd.setYear(modelYr); vmd.setPartner(getPartner()); ret = gDAO.save(vmd); if (!ret) break; } else if (vmd == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Vehicle Model: '" + vehicleModel + "' does not exist for vehicle: " + regNo); break; } } } Vendor ven = null; if (purchased_from != null && purchased_from.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Vendor e where e.name = :name and e.partner=:partner"); q.setParameter("name", vehicleModel.trim()); q.setParameter("partner", getPartner()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<Vendor> vmList = (Vector<Vendor>) qObj; for (Vendor e : vmList) ven = e; } if (ven == null && isAutoCreate()) { ven = new Vendor(); ven.setName(purchased_from); ven.setPartner(getPartner()); ven.setCreatedBy(dashBean.getUser()); ven.setCrt_dt(new Date()); ret = gDAO.save(ven); if (!ret) break; else { ServiceType st = null; q = gDAO.createQuery("Select e from ServiceType e where e.name = :name"); q.setParameter("name", "Vehicle Sales"); qObj = gDAO.search(q, 0); if (qObj != null) { Vector<ServiceType> vmList = (Vector<ServiceType>) qObj; for (ServiceType e : vmList) st = e; } if (st != null) { VendorServices vs = new VendorServices(); vs.setCreatedBy(dashBean.getUser()); vs.setCrt_dt(new Date()); vs.setServiceType(st); vs.setVendor(ven); ret = gDAO.save(vs); if (!ret) break; } } } } if (fleet != null && vt != null && vm != null && vmd != null) { Vehicle v = new Vehicle(); v.setFleet(fleet); v.setActive(true); v.setActiveStatus(VehicleStatusEnum.ACTIVE.getStatus()); v.setChasisNo(chassisNo); v.setCreatedBy(dashBean.getUser()); v.setCrt_dt(new Date()); v.setEngineNo(engineNo); v.setModel(vmd); v.setPartner(getPartner()); v.setRegistrationNo(regNo); v.setZonControlId(trackerID); /*try { v.setZonControlId(Integer.parseInt(trackerID)); } catch(Exception ex){}*/ try { v.setPurchaseAmt(new BigDecimal(Double.parseDouble(purchased_amount))); } catch (Exception ex) { } try { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); v.setPurchaseDate(sdf.parse(purchase_date)); } catch (Exception ex) { } v.setVendor(ven); ret = gDAO.save(v); if (!ret) break; else if (regionObj != null || deptObj != null) { VehicleParameters vpm = new VehicleParameters(); vpm.setRegion(regionObj); vpm.setDept(deptObj); try { vpm.setCalibratedcapacity(Double.parseDouble(calibrated_capacity)); } catch (Exception ex) { } vpm.setCardno(card_no); vpm.setColor(color); vpm.setSimno(sim_no); try { vpm.setTankcapacity(Double.parseDouble(tank_capacity)); } catch (Exception ex) { } vpm.setTyresize(tyre_size); vpm.setUnitofmeasure(unit_of_measurement); if (fuel_type != null && fuel_type.trim().length() > 0) { Query q = gDAO.createQuery("Select e from FuelType e where e.name = :name"); q.setParameter("name", fuel_type); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<FuelType> vmList = (Vector<FuelType>) qObj; for (FuelType e : vmList) vpm.setFuelType(e); } } vpm.setCreatedBy(dashBean.getUser()); vpm.setCrt_dt(new Date()); vpm.setVehicle(v); ret = gDAO.save(vpm); if (!ret) break; } } } } else pos += 1; } if (ret) { gDAO.commit(); naration += ", Status: Success"; msg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Success: ", "All vehicles created successfully."); FacesContext.getCurrentInstance().addMessage(null, msg); setVehicleMakes(null); setVehicleTypes(null); setVmodels(null); } else { gDAO.rollback(); naration += ", Status: Failed: " + gDAO.getMessage(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "Failed to create all vehicles: " + gDAO.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } catch (Exception ex) { ex.printStackTrace(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Severe error occured. " + ex.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } finally { gDAO.destroy(); dashBean.saveAudit(naration, "", null); } } }
From source file:com.dexter.fms.mbean.FleetMBean.java
License:Open Source License
@SuppressWarnings("unchecked") public void BatchLoadVModels() { if (getModelsBatchExcel() != null) { String naration = "Batch load vehicle models: "; GeneralDAO gDAO = new GeneralDAO(); try {//from ww w. j a va 2 s . co m ByteArrayInputStream byteIn = new ByteArrayInputStream(getModelsBatchExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { Row row = rowIterator.next(); String model_nm = "", vehicleType = "", vehicleMaker = ""; String model_yr = ""; if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } if (cell.getColumnIndex() == 0) model_nm = val; else if (cell.getColumnIndex() == 1) model_yr = val; else if (cell.getColumnIndex() == 2) vehicleType = val; else if (cell.getColumnIndex() == 3) vehicleMaker = val; } VehicleType vt = null; VehicleMake vm = null; VehicleModel vmd = new VehicleModel(); // search for existing vehicle type and maker Query q = gDAO.createQuery("Select e from VehicleType e where e.name = :name"); q.setParameter("name", vehicleType.trim()); Object qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleType> vtList = (Vector<VehicleType>) qObj; for (VehicleType e : vtList) vt = e; } if (vt == null && isAutoCreate()) { vt = new VehicleType(); vt.setCreatedBy(dashBean.getUser()); vt.setPartner(dashBean.getUser().getPartner()); vt.setCrt_dt(new Date()); vt.setName(vehicleType.trim()); gDAO.save(vt); } q = gDAO.createQuery("Select e from VehicleMake e where e.name = :name"); q.setParameter("name", vehicleMaker.trim()); qObj = gDAO.search(q, 0); if (qObj != null) { Vector<VehicleMake> vmList = (Vector<VehicleMake>) qObj; for (VehicleMake e : vmList) vm = e; } if (vm == null && isAutoCreate()) { vm = new VehicleMake(); vm.setCreatedBy(dashBean.getUser()); vm.setPartner(dashBean.getUser().getPartner()); vm.setCrt_dt(new Date()); vm.setName(vehicleMaker.trim()); gDAO.save(vm); } vmd.setType(vt); vmd.setMaker(vm); vmd.setCreatedBy(dashBean.getUser()); vmd.setPartner(dashBean.getUser().getPartner()); vmd.setCrt_dt(new Date()); vmd.setName(model_nm); vmd.setYear(model_yr); ret = gDAO.save(vmd); if (!ret) break; } else pos += 1; } if (ret) { gDAO.commit(); naration += "Status: Success"; msg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Success: ", "All models created successfully."); FacesContext.getCurrentInstance().addMessage(null, msg); setVehicleMakes(null); setVehicleTypes(null); setVmodels(null); } else { gDAO.rollback(); naration += "Status: Failed: " + gDAO.getMessage(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "Failed to create all models. " + gDAO.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } catch (Exception ex) { ex.printStackTrace(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Severe error occured. " + ex.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } finally { gDAO.destroy(); dashBean.saveAudit(naration, "", null); } } }
From source file:com.dexter.fms.mbean.FleetMBean.java
License:Open Source License
public void save(int i) { String naration = ""; GeneralDAO gDAO = new GeneralDAO(); boolean ret = false; boolean validated = false; switch (i) {//from w w w .ja v a 2s. c om case 1: // vehicle type { if (getVehicleType().getName() != null && getPartner() != null) { naration = "Create vehicle type: " + getVehicleType().getName(); getVehicleType().setPartner(getPartner()); getVehicleType().setCreatedBy(dashBean.getUser()); getVehicleType().setCrt_dt(new Date()); gDAO.startTransaction(); ret = gDAO.save(getVehicleType()); if (ret) { setVehicleType(null); setVehicleTypes(null); } validated = true; } break; } case 2: // vehicle make { if (getVehicleMake().getName() != null && getPartner() != null) { naration = "Create vehicle make: " + getVehicleMake().getName(); getVehicleMake().setPartner(getPartner()); getVehicleMake().setCreatedBy(dashBean.getUser()); getVehicleMake().setCrt_dt(new Date()); gDAO.startTransaction(); ret = gDAO.save(getVehicleMake()); if (ret) { setVehicleMake(null); setVehicleMakes(null); } validated = true; } break; } case 3: // vehicle models { if (getVmodel().getName() != null && getVehicleType_id() != null && getVehicleMake_id() != null && getPartner() != null) { getVmodel().setPartner(getPartner()); getVmodel().setCreatedBy(dashBean.getUser()); getVmodel().setCrt_dt(new Date()); if (getVehicleMake_id() > 0) { Object obj = gDAO.find(VehicleMake.class, getVehicleMake_id()); if (obj != null) { getVmodel().setMaker((VehicleMake) obj); } } if (getVehicleType_id() > 0) { Object obj = gDAO.find(VehicleType.class, getVehicleType_id()); if (obj != null) { getVmodel().setType((VehicleType) obj); } } naration = "Create vehicle model: Type: " + getVmodel().getType().getName() + ", Make: " + getVmodel().getMaker().getName() + ", Model: " + getVmodel().getName() + "(" + getVmodel().getYear() + ")"; gDAO.startTransaction(); ret = gDAO.save(getVmodel()); if (ret) { setVmodel(null); setVmodels(null); } validated = true; } break; } case 4: // vehicle standard routine maintenance { if (getVsrm().getDescription() != null && getVsrm().getOdometer() != null && getVehicleModel_id() != null) { getVsrm().setCreatedBy(dashBean.getUser()); getVsrm().setCrt_dt(new Date()); if (getVehicleModel_id() > 0) { Object obj = gDAO.find(VehicleModel.class, getVehicleModel_id()); if (obj != null) { getVsrm().setModel((VehicleModel) obj); } } naration = "Create standard routine maintenance: " + getVsrm().getModel().getName() + "(" + getVsrm().getOdometer().toPlainString() + ")"; gDAO.startTransaction(); ret = gDAO.save(getVsrm()); if (ret) { setVsrm(null); setVsrmList(null); } validated = true; } break; } case 5: // batch load VSRM { if (getAllvmodels() != null && getAllvmodels().size() > 0) { int count = 0; Vector<VehicleModel> selModels = new Vector<VehicleModel>(); for (VehicleModel e : getAllvmodels()) { if (e.isSelected()) { count++; selModels.add(e); } } if (count == 0) { msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "No vehicle model selected!"); FacesContext.getCurrentInstance().addMessage(null, msg); } else { // Now read the content from excel if (getModelsVSRMBatchExcel() != null) { try { ByteArrayInputStream byteIn = new ByteArrayInputStream( getModelsVSRMBatchExcel().getContents()); HSSFWorkbook workbook = new HSSFWorkbook(byteIn); HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); String odometer = "", maintenance = ""; if (pos > 1) { validated = true; //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } if (cell.getColumnIndex() == 0) odometer = val; else if (cell.getColumnIndex() == 1) maintenance = val; } for (VehicleModel e : selModels) { VehicleStandardRM vsrm = new VehicleStandardRM(); vsrm.setCreatedBy(dashBean.getUser()); vsrm.setCrt_dt(new Date()); vsrm.setDescription(maintenance); vsrm.setModel(e); vsrm.setOdometer(new BigDecimal(Long.parseLong(odometer))); ret = gDAO.save(vsrm); } } } } catch (Exception ex) { ex.printStackTrace(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Severe error occured. " + ex.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } } } else { msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "No vehicle model available!"); FacesContext.getCurrentInstance().addMessage(null, msg); } break; } } if (validated) { if (ret) { gDAO.commit(); naration += ", Status: Success"; msg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Success: ", "Entity created successfully."); FacesContext.getCurrentInstance().addMessage(null, msg); } else { gDAO.rollback(); naration += ", Status: Failed: " + gDAO.getMessage(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "Failed to create entity. " + gDAO.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } else { msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "All fields with the '*' sign are required!"); FacesContext.getCurrentInstance().addMessage(null, msg); } gDAO.destroy(); dashBean.saveAudit(naration, "", null); }
From source file:com.dexter.fms.mbean.UserMBean.java
@SuppressWarnings("unchecked") public void BatchLoadPersonels() { if (getPartner() != null && getPersonelsBatchExcel() != null) { String naration = "Batch load staffs: "; GeneralDAO gDAO = new GeneralDAO(); try {/*from w w w . ja v a 2s . com*/ ByteArrayInputStream byteIn = new ByteArrayInputStream(getPersonelsBatchExcel().getContents()); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(byteIn); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet starting from row 2 Iterator<Row> rowIterator = sheet.iterator(); int pos = 1; gDAO.startTransaction(); boolean ret = false; while (rowIterator.hasNext()) { Row row = rowIterator.next(); String staffID = "", firstname = "", lastname = "", department = "", region = "", address = "", phone = "", email = "", isAUser = ""; String username = "", password = "", cpassword = "", role = ""; String isADriver = "", driver_grade = "", driver_license_no = "", driver_license_expiry_date = "", guarantor = ""; if (pos > 1) { //Get iterator to all cells of current row Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String val = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: val = ""; break; case Cell.CELL_TYPE_BOOLEAN: val = "" + cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_ERROR: val = ""; break; case Cell.CELL_TYPE_NUMERIC: val = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; default: { try { val = cell.getStringCellValue(); } catch (Exception ex) { } break; } } switch (cell.getColumnIndex()) { case 0: staffID = val; break; case 1: firstname = val; break; case 2: lastname = val; break; case 3: department = val; break; case 4: region = val; break; case 5: address = val; break; case 6: phone = val; break; case 7: email = val; break; case 8: isAUser = val; break; case 9: username = val; break; case 10: password = val; break; case 11: cpassword = val; break; case 12: role = val; break; case 13: isADriver = val; break; case 14: driver_grade = val; break; case 15: driver_license_no = val; break; case 16: driver_license_expiry_date = val; break; case 17: guarantor = val; break; } } PartnerPersonel pp = new PartnerPersonel(); pp.setPartner(getPartner()); pp.setCreatedBy(dashBean.getUser()); pp.setCrt_dt(new Date()); pp.setStaff_id(staffID); pp.setFirstname(firstname); pp.setLastname(lastname); pp.setAddress(address); pp.setPhone(phone); pp.setEmail(email); if (department != null && department.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Department e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", department); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Department> objsList = (Vector<Department>) objs; for (Department e : objsList) pp.setDepartment(e); } if (pp.getDepartment() == null && isAutoCreate()) { Department d = new Department(); d.setCreatedBy(dashBean.getUser()); d.setCrt_dt(new Date()); d.setName(department); d.setPartner(getPartner()); ret = gDAO.save(d); if (ret) pp.setDepartment(d); else break; } else if (pp.getDepartment() == null && !isAutoCreate()) { ret = false; gDAO.setMessage( "Department: '" + department + "' does not exist for user: " + username); break; } } if (region != null && region.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from Region e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", region); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<Region> objsList = (Vector<Region>) objs; for (Region e : objsList) pp.setRegion(e); } if (pp.getRegion() == null && isAutoCreate()) { Region r = new Region(); r.setCreatedBy(dashBean.getUser()); r.setCrt_dt(new Date()); r.setName(region); r.setPartner(getPartner()); ret = gDAO.save(r); if (ret) pp.setRegion(r); else break; } else if (pp.getRegion() == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Region: '" + region + "' does not exist for user: " + username); break; } } MRole r = null; if (isAUser != null && isAUser.trim().equalsIgnoreCase("true")) { Query q = gDAO.createQuery( "Select e from MRole e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", role); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<MRole> objsList = (Vector<MRole>) objs; for (MRole e : objsList) r = e; } if (r == null) { ret = false; gDAO.setMessage("Role: '" + role + "' must exist for user: " + username); break; } } ret = gDAO.save(pp); if (!ret) break; else { if (isAUser != null && isAUser.trim().equalsIgnoreCase("true")) { if (username != null && username.trim().length() > 0 && password != null && password.trim().length() > 0 && role != null && role.trim().length() > 0) { if (password.equals(cpassword)) { PartnerUser pu = new PartnerUser(); pu.setActive(true); pu.setAdmin(false); pu.setCreatedBy(dashBean.getUser()); pu.setCrt_dt(new Date()); pu.setPartner(getPartner()); pu.setPartner_code(getPartner().getCode()); pu.setPassword(Hasher.getHashValue(password)); pu.setPersonel(pp); pu.setUsername(username); ret = gDAO.save(pu); if (ret) { if (r != null) { PartnerUserRole pur = new PartnerUserRole(); pur.setCreatedBy(dashBean.getUser()); pur.setCrt_dt(new Date()); pur.setDefaultRole(false); pur.setRole(r); pur.setUser(pu); ret = gDAO.save(pur); if (!ret) break; } else { ret = false; gDAO.setMessage("Role: '" + role + "' does not exist for user: " + username); break; } pp.setHasUser(true); ret = gDAO.update(pp); if (!ret) break; } else break; } else { ret = false; gDAO.setMessage("Passwords are not the same for user: " + username); break; } } else { ret = false; gDAO.setMessage("All fields are required to create a user account for user: " + username); break; } } if (isADriver != null && isADriver.trim().equalsIgnoreCase("true")) { PartnerDriver driver = new PartnerDriver(); driver.setActive(true); driver.setCreatedBy(dashBean.getUser()); driver.setCrt_dt(new Date()); driver.setPartner(getPartner()); driver.setPersonel(pp); driver.setDrvLicenseNo(driver_license_no); driver.setGuarantor(guarantor); if (driver_grade != null && driver_grade.trim().length() > 0) { Query q = gDAO.createQuery( "Select e from DriverGrade e where e.partner = :partner and e.name = :name"); q.setParameter("partner", getPartner()); q.setParameter("name", driver_grade); Object objs = gDAO.search(q, 0); if (objs != null) { Vector<DriverGrade> objsList = (Vector<DriverGrade>) objs; for (DriverGrade e : objsList) driver.setDriverGrade(e); } if (driver.getDriverGrade() == null && isAutoCreate()) { DriverGrade dg = new DriverGrade(); dg.setCreatedBy(dashBean.getUser()); dg.setCrt_dt(new Date()); dg.setName(region); dg.setPartner(getPartner()); ret = gDAO.save(dg); if (ret) driver.setDriverGrade(dg); else break; } else if (driver.getDriverGrade() == null && !isAutoCreate()) { ret = false; gDAO.setMessage("Driver grade: '" + driver_grade + "' does not exist for user: " + username); break; } } ret = gDAO.save(driver); if (!ret) break; else { pp.setHasDriver(true); ret = gDAO.update(pp); if (!ret) break; } if (driver_license_expiry_date != null) { Date expiryDt = null; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); try { expiryDt = sdf.parse(driver_license_expiry_date); } catch (Exception ex) { } if (expiryDt != null) { DriverLicense dl = new DriverLicense(); dl.setDrvLicenseNo(driver_license_no); dl.setCreatedBy(dashBean.getUser()); dl.setCrt_dt(new Date()); dl.setLic_end_dt(expiryDt); boolean active = false, expired = false; if (expiryDt.after(new Date())) { active = true; } else { expired = true; } dl.setActive(active); dl.setExpired(expired); dl.setDriver(driver); ret = gDAO.save(dl); if (!ret) break; } } } } } else pos += 1; } if (ret) { gDAO.commit(); naration += " Status: Success"; msg = new FacesMessage(FacesMessage.SEVERITY_INFO, "Success: ", "All personel created successfully."); FacesContext.getCurrentInstance().addMessage(null, msg); setPersonels(null); setPersonelsWithoutUsers(null); } else { gDAO.rollback(); naration += " Status: Failed: " + gDAO.getMessage(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed: ", "Failed to create all personel. " + gDAO.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } } catch (Exception ex) { ex.printStackTrace(); msg = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error: ", "Severe error occured. " + ex.getMessage()); FacesContext.getCurrentInstance().addMessage(null, msg); } finally { gDAO.destroy(); dashBean.saveAudit(naration, "", null); } } }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
public void offloadCards() { FacesContext curContext = FacesContextImpl.getCurrentInstance(); if (getUploadItem() != null) { try {/* w w w . ja v a 2 s. c o m*/ HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream()); int sheetCount = workbook.getNumberOfSheets(); if (sheetCount >= 1) { String password = getRandomDigitPassword(); HSSFSheet sheet = workbook.getSheetAt(0); // first sheet should be the main sheet sheet.protectSheet(password); ByteArrayOutputStream byout = new ByteArrayOutputStream(); workbook.write(byout); byout.close(); CardRequest cr = new CardRequest(); cr.setRequestRefNum(getActiveUser().getPartner().getCode() + "-" + password); cr.setAdditionalComment(getRequestComment()); cr.setCrt_dt(new Date()); cr.setExcelFile(byout.toByteArray()); cr.setPartner(getActiveUser().getPartner()); cr.setRequest_dt(new Date()); cr.setRequestedBy(getActiveUser()); cr.setRequestType("OFFLOAD-CARDS"); cr.setStatus("PENDING"); GeneralDAO gDAO = new GeneralDAO(); gDAO.startTransaction(); boolean ret = gDAO.save(cr); if (ret) { gDAO.commit(); setRequestComment(null); setMyPendingCardCancelRequests(null); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Success", "Request submitted successfully!")); // Send email to user that submitted that their request is been treated String body = "<html><body><p>Dear " + getActiveUser().getFullname() + ",</p><p>Your request to offload cards has been recieved and will be treated as soon as possible. You will be notified of every progress.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; Emailer.sendEmail("fuelcard@sattrakservices.com", new String[] { getActiveUser().getEmail() }, "Card OffLoad Request - " + cr.getRequestRefNum() + " Received", body); // Send email to sattrak that request is submitted byte[] doc = cr.getExcelFile(); String body2 = "<html><body><p>Hello,</p><p>A request to offload cards has been submitted. The document is attached.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; if (getSattrakPartner() != null && getSattrakPartner().getContactEmails() != null && getSattrakPartner().getContactEmails().trim().length() > 0) { String[] to = getSattrakPartner().getContactEmails().split(","); Emailer.sendEmail("fuelcard@sattrakservices.com", to, "Card OffLoad Request - " + cr.getRequestRefNum() + " Submitted", body2, doc, cr.getRequestRefNum() + ".xls", "application/vnd.ms-excel"); } } else { gDAO.rollback(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", gDAO.getMessage())); } gDAO.destroy(); } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Excel document is not valid!")); } } catch (Exception ex) { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Severe", "Please upload an excel document! Error: " + ex.getMessage())); } } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Please upload an excel document!")); } }
From source file:com.dexter.fuelcard.mbean.UserMBean.java
public void loadCards() { FacesContext curContext = FacesContextImpl.getCurrentInstance(); if (getUploadItem() != null) { try {/*from w w w . j ava 2 s .co m*/ HSSFWorkbook workbook = new HSSFWorkbook(getUploadItem().getInputstream()); int sheetCount = workbook.getNumberOfSheets(); if (sheetCount >= 1) { String password = getRandomDigitPassword(); HSSFSheet sheet = workbook.getSheetAt(0); // first sheet should be the main sheet sheet.protectSheet(password); ByteArrayOutputStream byout = new ByteArrayOutputStream(); workbook.write(byout); byout.close(); CardRequest cr = new CardRequest(); cr.setRequestRefNum(getActiveUser().getPartner().getCode() + "-" + password); cr.setAdditionalComment(getRequestComment()); cr.setCrt_dt(new Date()); cr.setExcelFile(byout.toByteArray()); cr.setPartner(getActiveUser().getPartner()); cr.setRequest_dt(new Date()); cr.setRequestedBy(getActiveUser()); cr.setRequestType("LOAD-CARDS"); cr.setStatus("PENDING"); GeneralDAO gDAO = new GeneralDAO(); gDAO.startTransaction(); boolean ret = gDAO.save(cr); if (ret) { gDAO.commit(); setRequestComment(null); setMyPendingCardCancelRequests(null); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Success", "Request submitted successfully!")); // Send email to user that submitted that their request is been treated String body = "<html><body><p>Dear " + getActiveUser().getFullname() + ",</p><p>Your request to load cards has been recieved and will be treated as soon as possible. You will be notified of every progress.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; Emailer.sendEmail("fuelcard@sattrakservices.com", new String[] { getActiveUser().getEmail() }, "Card Load Request - " + cr.getRequestRefNum() + " Received", body); // Send email to sattrak that request is submitted byte[] doc = cr.getExcelFile(); String body2 = "<html><body><p>Hello,</p><p>A request to load cards has been submitted. The document is attached.</p><p>Regards<br/>Fuel Card Platform</p></body></html>"; if (getSattrakPartner() != null && getSattrakPartner().getContactEmails() != null && getSattrakPartner().getContactEmails().trim().length() > 0) { String[] to = getSattrakPartner().getContactEmails().split(","); Emailer.sendEmail("fuelcard@sattrakservices.com", to, "Card Load Request - " + cr.getRequestRefNum() + " Submitted", body2, doc, cr.getRequestRefNum() + ".xls", "application/vnd.ms-excel"); } } else { gDAO.rollback(); curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", gDAO.getMessage())); } gDAO.destroy(); } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Excel document is not valid!")); } } catch (Exception ex) { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Severe", "Please upload an excel document! Error: " + ex.getMessage())); } } else { curContext.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Failed", "Please upload an excel document!")); } }