List of usage examples for java.sql Timestamp getTime
public long getTime()
From source file:com.lp.server.fertigung.ejbfac.FertigungFacBean.java
public void terminVerschieben(Integer losIId, Timestamp tsBeginnNeu, Timestamp tsEndeNeu, TheClientDto theClientDto) {/*from w ww . j av a 2 s . c om*/ Los los = em.find(Los.class, losIId); if (los.getStatusCNr().equals(FertigungFac.STATUS_ANGELEGT) || los.getStatusCNr().equals(FertigungFac.STATUS_AUSGEGEBEN) || los.getStatusCNr().equals(FertigungFac.STATUS_IN_PRODUKTION) || los.getStatusCNr().equals(FertigungFac.STATUS_GESTOPPT) || los.getStatusCNr().equals(FertigungFac.STATUS_TEILERLEDIGT)) { los.setTProduktionsbeginn(new java.sql.Date(tsBeginnNeu.getTime())); los.setTProduktionsende(new java.sql.Date(tsEndeNeu.getTime())); em.merge(los); em.flush(); LossollmaterialDto[] sollmat = lossollmaterialFindByLosIId(losIId); for (int i = 0; i < sollmat.length; i++) { try { getFehlmengeFac().aktualisiereFehlmenge(LocaleFac.BELEGART_LOS, sollmat[i].getIId(), false, theClientDto); ArtikelreservierungDto reservierungDto = getReservierungFac() .artikelreservierungFindByBelegartCNrBelegartPositionIIdOhneExc(LocaleFac.BELEGART_LOS, sollmat[i].getIId()); if (reservierungDto != null) { if (reservierungDto != null && reservierungDto.getNMenge().doubleValue() >= 0) { reservierungDto.setTLiefertermin(tsBeginnNeu); } else { reservierungDto.setTLiefertermin(tsEndeNeu); } getReservierungFac().updateArtikelreservierung(reservierungDto); } } catch (RemoteException e) { throwEJBExceptionLPRespectOld(e); } } } else if (los.getStatusCNr().equals(FertigungFac.STATUS_ERLEDIGT)) { throw new EJBExceptionLP(EJBExceptionLP.FEHLER_FERTIGUNG_DAS_LOS_IST_BEREITS_ERLEDIGT, new Exception("los " + los.getCNr() + " ist bereits erledigt")); } else if (los.getStatusCNr().equals(FertigungFac.STATUS_STORNIERT)) { throw new EJBExceptionLP(EJBExceptionLP.FEHLER_FERTIGUNG_DAS_LOS_IST_STORNIERT, new Exception("los " + los.getCNr() + " ist bereits storniert")); } else if (los.getStatusCNr().equals(FertigungFac.STATUS_ANGELEGT)) { throw new EJBExceptionLP(EJBExceptionLP.FEHLER_FERTIGUNG_DAS_LOS_IST_NOCH_NICHT_AUSGEGEBEN, new Exception("los " + los.getCNr() + " ist noch nicht ausgegeben")); } }
From source file:com.lp.server.fertigung.ejbfac.FertigungFacBean.java
@TransactionAttribute(TransactionAttributeType.NEVER) public ArrayList<LosAusAuftragDto> vorschlagMitUnterlosenAusAuftrag(Integer auftragIId, TheClientDto theClientDto) {// w w w.j a va 2s. co m ArrayList<LosAusAuftragDto> losDtos = new ArrayList<LosAusAuftragDto>(); try { ParametermandantDto parameterVorlaufzeit = getParameterFac().getMandantparameter( theClientDto.getSMandantenwaehrung(), ParameterFac.KATEGORIE_FERTIGUNG, ParameterFac.PARAMETER_DEFAULT_VORLAUFZEIT_INTERNEBESTELLUNG); int iVorlaufzeit = (Integer) parameterVorlaufzeit.getCWertAsObject(); Integer stdFertigungsgruppeIId = null; FertigungsgruppeDto[] fertigungsgruppeDtos = getStuecklisteFac() .fertigungsgruppeFindByMandantCNr(theClientDto.getMandant(), theClientDto); if (fertigungsgruppeDtos.length > 0) { stdFertigungsgruppeIId = fertigungsgruppeDtos[0].getIId(); } AuftragDto auftragDto = getAuftragFac().auftragFindByPrimaryKey(auftragIId); int iLieferdauerKunde = getKundeFac() .kundeFindByPrimaryKey(auftragDto.getKundeIIdAuftragsadresse(), theClientDto).getILieferdauer(); if (auftragDto.getAuftragstatusCNr().equals(LocaleFac.STATUS_ANGELEGT) || auftragDto.getAuftragstatusCNr().equals(LocaleFac.STATUS_OFFEN) || auftragDto.getAuftragstatusCNr().equals(LocaleFac.STATUS_TEILERLEDIGT)) { AuftragpositionDto[] dtos = getAuftragpositionFac().auftragpositionFindByAuftrag(auftragIId); for (int i = 0; i < dtos.length; i++) { ArrayList<LosAusAuftragDto> losDtosPosition = new ArrayList<LosAusAuftragDto>(); if (dtos[i].getPositionsartCNr().equals(AuftragServiceFac.AUFTRAGPOSITIONART_HANDEINGABE)) { Timestamp tAuftragsliefertermin = dtos[i].getTUebersteuerbarerLiefertermin(); if (tAuftragsliefertermin == null) { tAuftragsliefertermin = auftragDto.getDLiefertermin(); } Timestamp tEnde = Helper.addiereTageZuTimestamp(tAuftragsliefertermin, -iLieferdauerKunde); Timestamp tBeginn = Helper.addiereTageZuTimestamp(tEnde, -iVorlaufzeit); LosAusAuftragDto laDto = new LosAusAuftragDto(); if (tBeginn.before(Helper.cutTimestamp(new Timestamp(System.currentTimeMillis())))) { laDto.setBDatumVerschoben(true); tBeginn = Helper.cutTimestamp(new Timestamp(System.currentTimeMillis())); tEnde = Helper.addiereTageZuTimestamp(tBeginn, iVorlaufzeit); } LosDto losDto = new LosDto(); losDto.setAuftragIId(auftragIId); losDto.setAuftragpositionIId(dtos[i].getIId()); losDto.setTProduktionsbeginn(new java.sql.Date(tBeginn.getTime())); losDto.setTProduktionsende(new java.sql.Date(tEnde.getTime())); losDto.setLagerIIdZiel(getLagerFac().getHauptlagerDesMandanten(theClientDto).getIId()); losDto.setFertigungsgruppeIId(stdFertigungsgruppeIId); losDto.setNLosgroesse(dtos[i].getNMenge()); losDto.setKostenstelleIId(auftragDto.getKostenstelleIId()); losDto.setCProjekt(dtos[i].getCBez()); laDto.setLosDto(losDto); laDto.setAuftragpositionDto(dtos[i]); laDto.setAuftragspositionsnummer( getAuftragpositionFac().getPositionNummer(dtos[i].getIId())); losDtos.add(laDto); } else { // SP1107 Setartikelkopf ignorieren Session session = null; SessionFactory factory = FLRSessionFactory.getFactory(); session = factory.openSession(); Criteria crit = session.createCriteria(FLRAuftragposition.class); crit.add(Restrictions.eq("position_i_id_artikelset", dtos[i].getIId())); int iZeilen = crit.list().size(); session.close(); if (iZeilen > 0) { continue; } StuecklisteDto stuecklisteDto = getStuecklisteFac() .stuecklisteFindByMandantCNrArtikelIIdOhneExc(dtos[i].getArtikelIId(), theClientDto); if (stuecklisteDto != null) { if (dtos[i].getNMenge() != null && dtos[i].getNMenge().doubleValue() > 0) { Timestamp tAuftragsliefertermin = dtos[i].getTUebersteuerbarerLiefertermin(); if (tAuftragsliefertermin == null) { tAuftragsliefertermin = auftragDto.getDLiefertermin(); } Timestamp tEnde = Helper.addiereTageZuTimestamp(tAuftragsliefertermin, -iLieferdauerKunde); Timestamp tBeginn = tEnde; if (stuecklisteDto.getNDefaultdurchlaufzeit() != null) { tBeginn = Helper.addiereTageZuTimestamp(tEnde, -stuecklisteDto.getNDefaultdurchlaufzeit().intValue()); } LosAusAuftragDto laDto = new LosAusAuftragDto(); LosDto losDto = new LosDto(); losDto.setAuftragIId(auftragIId); losDto.setAuftragpositionIId(dtos[i].getIId()); losDto.setTProduktionsbeginn(new java.sql.Date(tBeginn.getTime())); losDto.setTProduktionsende(new java.sql.Date(tEnde.getTime())); losDto.setStuecklisteIId(stuecklisteDto.getIId()); losDto.setLagerIIdZiel(stuecklisteDto.getLagerIIdZiellager()); losDto.setCProjekt(auftragDto.getCBezProjektbezeichnung()); losDto.setFertigungsgruppeIId(stuecklisteDto.getFertigungsgruppeIId()); losDto.setNLosgroesse(dtos[i].getNMenge()); losDto.setKostenstelleIId(auftragDto.getKostenstelleIId()); laDto.setLosDto(losDto); laDto.setAuftragpositionDto(dtos[i]); laDto.setAuftragspositionsnummer( getAuftragpositionFac().getPositionNummer(dtos[i].getIId())); laDto.setFehlmengen(getFehlmengeFac().getAnzahlFehlmengeEinesArtikels( stuecklisteDto.getArtikelIId(), theClientDto)); ArtikelreservierungDto eigeneReservierungDto = getReservierungFac() .artikelreservierungFindByBelegartCNrBelegartPositionIIdOhneExc( LocaleFac.BELEGART_AUFTRAG, dtos[i].getIId()); BigDecimal reservierungen = getReservierungFac() .getAnzahlReservierungen(stuecklisteDto.getArtikelIId(), theClientDto); if (eigeneReservierungDto != null && eigeneReservierungDto.getNMenge() != null) { if (reservierungen.subtract(eigeneReservierungDto.getNMenge()) .doubleValue() < 0) { reservierungen = new BigDecimal(0); } else { reservierungen = reservierungen.subtract(eigeneReservierungDto.getNMenge()); } } laDto.setReservierungen(reservierungen); BigDecimal lagerstand = new BigDecimal(0); LagerDto[] allelaegerDtos = getLagerFac() .lagerFindByMandantCNr(theClientDto.getMandant()); for (int j = 0; j < allelaegerDtos.length; j++) { if (Helper.short2boolean(allelaegerDtos[j].getBInternebestellung())) { lagerstand = lagerstand .add(getLagerFac().getLagerstand(stuecklisteDto.getArtikelIId(), allelaegerDtos[j].getIId(), theClientDto)); } } laDto.setLagerstand(lagerstand); laDto.setOffeneFertigungsmenge(getFertigungFac() .getAnzahlInFertigung(stuecklisteDto.getArtikelIId(), theClientDto)); losDtosPosition.add(laDto); ArrayList<?> stuecklisteAufegloest = getStuecklisteFac() .getStrukturDatenEinerStueckliste(stuecklisteDto.getIId(), theClientDto, StuecklisteReportFac.REPORT_STUECKLISTE_OPTION_SORTIERUNG_ARTIKELNR, 0, null, false, true, dtos[i].getNMenge(), null, true); for (int j = 0; j < stuecklisteAufegloest.size(); j++) { StuecklisteMitStrukturDto strukt = (StuecklisteMitStrukturDto) stuecklisteAufegloest .get(j); if (strukt.getStuecklistepositionDto() != null && strukt.getStuecklistepositionDto().getArtikelIId() != null) { losDtosPosition = holeAlleMoeglichenUnterloseEinerStueckliste( strukt.getStuecklistepositionDto().getArtikelIId(), strukt.getStuecklistepositionDto().getNZielmenge(), iVorlaufzeit, tBeginn, losDtosPosition, auftragDto, dtos[i].getNMenge(), theClientDto); } } // Wenn Termin vor Heute, dann aauf nach Heute // verschieben java.sql.Date fruehesterProduktionsbeginnVorHeute = Helper .cutDate(new java.sql.Date(System.currentTimeMillis())); for (int k = 0; k < losDtosPosition.size(); k++) { if (losDtosPosition.get(k).getLosDto().getTProduktionsbeginn() .before(fruehesterProduktionsbeginnVorHeute)) { fruehesterProduktionsbeginnVorHeute = losDtosPosition.get(k).getLosDto() .getTProduktionsbeginn(); } } int iDiffTage = Helper.getDifferenzInTagen(fruehesterProduktionsbeginnVorHeute, new java.sql.Date(System.currentTimeMillis())); if (iDiffTage > 0) { for (int k = 0; k < losDtosPosition.size(); k++) { losDtosPosition.get(k).getLosDto() .setTProduktionsbeginn(Helper.addiereTageZuDatum( losDtosPosition.get(k).getLosDto().getTProduktionsbeginn(), iDiffTage)); losDtosPosition.get(k).getLosDto() .setTProduktionsende(Helper.addiereTageZuDatum( losDtosPosition.get(k).getLosDto().getTProduktionsende(), iDiffTage)); losDtosPosition.get(k).setBDatumVerschoben(true); } } for (int k = 0; k < losDtosPosition.size(); k++) { losDtos.add(losDtosPosition.get(k)); } } } } } } } catch (RemoteException e) { throwEJBExceptionLPRespectOld(e); } return losDtos; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * @param tblWriter//from ww w.j a v a2s. c om * @param treeDef * @param isPaleo * @throws SQLException */ public void convertGTP(final TableWriter tblWriter, final GeologicTimePeriodTreeDef treeDef, final boolean isPaleo) throws SQLException { deleteAllRecordsFromTable("geologictimeperiod", BasicSQLUtils.myDestinationServerType); log.info("Converting old geologic time period records"); int count = 0; // create an ID mapper for the geologictimeperiod table IdTableMapper gtpIdMapper = IdMapperMgr.getInstance().addTableMapper("geologictimeperiod", "GeologicTimePeriodID"); Hashtable<Integer, GeologicTimePeriod> oldIdToGTPMap = new Hashtable<Integer, GeologicTimePeriod>(); // String sql = "SELECT g.GeologicTimePeriodID,g.RankCode,g.Name,g.Standard,g.Remarks,g.TimestampModified,g.TimestampCreated,p1.Age as Upper," + // "p1.AgeUncertainty as UpperUncertainty,p2.Age as Lower,p2.AgeUncertainty as LowerUncertainty FROM geologictimeperiod g, " + // "geologictimeboundary p1, geologictimeboundary p2 WHERE g.UpperBoundaryID=p1.GeologicTimeBoundaryID AND " + // "g.LowerBoundaryID=p2.GeologicTimeBoundaryID ORDER BY Lower DESC, RankCode"; String sql = "SELECT g.GeologicTimePeriodID,g.RankCode,g.Name,g.Standard,g.Remarks,g.TimestampModified,g.TimestampCreated,gb1.Age as Upper," + "gb1.AgeUncertainty as UpperUncertainty,gb2.Age as Lower,gb2.AgeUncertainty as LowerUncertainty FROM geologictimeperiod g " + "LEFT OUTER JOIN geologictimeboundary gb1 ON g.UpperBoundaryID = gb1.GeologicTimeBoundaryID " + "LEFT OUTER JOIN geologictimeboundary gb2 ON g.LowerBoundaryID = gb2.GeologicTimeBoundaryID " + "ORDER BY Lower DESC, RankCode"; Statement statement = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); statement.setFetchSize(Integer.MIN_VALUE); ResultSet rs = statement.executeQuery(sql); Session localSession = HibernateUtil.getCurrentSession(); HibernateUtil.beginTransaction(); Vector<GeologicTimePeriod> newItems = new Vector<GeologicTimePeriod>(); GeologicTimePeriod allTime = new GeologicTimePeriod(); allTime.initialize(); allTime.setDefinition(treeDef); GeologicTimePeriodTreeDefItem rootDefItem = treeDef.getDefItemByRank(0); allTime.setDefinitionItem(rootDefItem); allTime.setRankId(0); allTime.setName("Time"); allTime.setFullName("Time"); allTime.setStartPeriod(100000f); allTime.setEndPeriod(0f); allTime.setEndUncertainty(0f); allTime.setTimestampCreated(now); ++count; newItems.add(allTime); ArrayList<GeologicTimePeriod> needsPlaceHolderList = new ArrayList<GeologicTimePeriod>(); boolean needsTbl = true; if (isPaleo) { while (rs.next()) { Integer id = rs.getInt(1); Integer rank = rs.getInt(2) * 100; String name = rs.getString(3); String std = rs.getString(4); String rem = rs.getString(5); Date modTDate = rs.getDate(6); Date creTDate = rs.getDate(7); Timestamp modT = (modTDate != null) ? new Timestamp(modTDate.getTime()) : null; Timestamp creT = (creTDate != null) ? new Timestamp(creTDate.getTime()) : null; Float upper = (Float) rs.getObject(8); Float uError = (Float) rs.getObject(9); Float lower = (Float) rs.getObject(10); Float lError = (Float) rs.getObject(11); if (isEmpty(name)) { if (needsTbl) { tblWriter.startTable(); tblWriter.logHdr("ID", "Rank Name", "Name", "Reason"); needsTbl = false; } tblWriter.log(id.toString(), rank.toString(), name, "Name is null, Name set to 'XXXX'"); log.error("The Name is empty (or null) for GTP ID[" + id + "] Rank[" + rank + "]"); name = "XXXX"; } if (modT == null && creT == null) { creT = now; modT = now; } else if (modT == null && creT != null) { modT = new Timestamp(creT.getTime()); } else if (modT != null && creT == null) { creT = new Timestamp(modT.getTime()); } // else (neither are null, so do nothing) GeologicTimePeriodTreeDefItem defItem = rank != null ? treeDef.getDefItemByRank(rank) : null; GeologicTimePeriod gtp = new GeologicTimePeriod(); gtp.initialize(); gtp.setName(name); gtp.setFullName(name); gtp.setDefinitionItem(defItem); gtp.setRankId(rank); gtp.setDefinition(treeDef); gtp.setStartPeriod(lower); gtp.setStartUncertainty(lError); gtp.setEndPeriod(upper); gtp.setEndUncertainty(uError); gtp.setStandard(std); gtp.setRemarks(rem); gtp.setTimestampCreated(creT); gtp.setTimestampModified(modT); if (lower == null || upper == null || rank == null) { needsPlaceHolderList.add(gtp); log.debug("PlaceHold Old ID: " + id); } else { newItems.add(gtp); } oldIdToGTPMap.put(id, gtp); if (++count % 500 == 0) { log.info(count + " geologic time period records converted"); } } // now we need to fix the parent/pointers for (int i = 0; i < newItems.size(); ++i) { GeologicTimePeriod gtp = newItems.get(i); for (int j = 0; j < newItems.size(); ++j) { GeologicTimePeriod child = newItems.get(j); if (isParentChildPair(gtp, child)) { gtp.addChild(child); } } } if (needsPlaceHolderList.size() > 0) { int rank = 100; for (GeologicTimePeriodTreeDefItem di : treeDef.getTreeDefItems()) { System.out.println(di.getName() + " -> " + di.getRankId()); } GeologicTimePeriodTreeDefItem defItem = treeDef.getDefItemByRank(rank); GeologicTimePeriod gtp = new GeologicTimePeriod(); gtp.initialize(); gtp.setName("Placeholder"); gtp.setFullName("Placeholder"); gtp.setDefinitionItem(defItem); gtp.setRankId(rank); gtp.setDefinition(treeDef); gtp.setStartPeriod(0.0f); gtp.setStartUncertainty(0.0f); gtp.setEndPeriod(0.0f); gtp.setEndUncertainty(0.0f); gtp.setStandard(null); gtp.setRemarks(null); gtp.setTimestampCreated(now); gtp.setTimestampModified(now); allTime.addChild(gtp); rank = 200; defItem = treeDef.getDefItemByRank(rank); for (GeologicTimePeriod gtpPH : needsPlaceHolderList) { gtpPH.setDefinition(treeDef); gtpPH.setDefinitionItem(defItem); gtpPH.setRankId(rank); gtpPH.setStartPeriod(0.0f); gtpPH.setStartUncertainty(0.0f); gtpPH.setEndPeriod(0.0f); gtpPH.setEndUncertainty(0.0f); gtp.addChild(gtpPH); } } TreeHelper.fixFullnameForNodeAndDescendants(allTime); } // fix node number, child node number stuff allTime.setNodeNumber(1); fixNodeNumbersFromRoot(allTime); localSession.save(allTime); HibernateUtil.commitTransaction(); if (shouldCreateMapTables) { // add all of the ID mappings for (Integer oldId : oldIdToGTPMap.keySet()) { if (oldId != null) { GeologicTimePeriod gtp = oldIdToGTPMap.get(oldId); if (gtp != null) { if (gtp.getId() != null) { gtpIdMapper.put(oldId, gtp.getId()); } else { log.debug("GTP id is null: " + gtp.getName()); } } else { log.debug("GTP missing in hash for Old ID: " + oldId); } } else { log.debug("Old ID in Hash is null: " + oldId); } } } // set up geologictimeperiod foreign key mapping for stratigraphy IdMapperMgr.getInstance().mapForeignKey("Stratigraphy", "GeologicTimePeriodID", "GeologicTimePeriod", "GeologicTimePeriodID"); log.info(count + " geologic time period records converted"); if (!needsTbl) { tblWriter.endTable(); } }
From source file:gov.llnl.lustre.lwatch.PlotFrame2.java
/** * Load historical data for a specific variable. * * @param type data type (MDS, OST, OSS, RTR). * * @param subId sub identifier (MDS or Router group) to load data for. * * @param cat name of MDS, OST, RTR to load data for. * * @param var name of variable to load data for. * * @param curves list of curve names that are selected. * * @param varNum number of the variable (n of m) to be plotted. * * @param varTot total number of variables to be plotted. *//* ww w. ja va 2s . c om*/ void loadHistorical___Data(String type, int subId, String cat, String var, String[] curves, int varNum, int varTot) throws java.lang.Exception { if (localDebug) Debug.out("type = " + type + " subId = " + subId + "\ncat = " + cat + " var = " + var + " curves.length = " + curves.length + "\nvarNum = " + varNum + " varTot = " + varTot); long loadBeg = 0; if (timerOn) { loadBeg = System.currentTimeMillis(); } //double[] divisors = {1000000., 1000000., 1000000., 1000000., 1000.0, 1000.0, //1.0, 1.0, 1.0}; //, 1.0, 1.0}; //String[] yAxisLabs = {"MBytes", "MBytes", "MBytes / Second", "MBytes / Second", //"KBytes Free", "KBytes Used" , "Inodes Free", "Inodes Used", //"Percent CPU"}; //, "Percent KBytes", "Percent INodes"}; if (localDebug) Debug.out("Entering loadHistorical___Data var arg = " + var); int devId = -1; int fsId = -1; int varId = -1; String varName = ""; if ("OST".equals(type)) { if (!isAnAggPlot) devId = calcOSTId(cat); else fsId = database.getFilesystemInfo().filesystemId; varId = calcOSTVariableId(var); } else if ("RTR".equals(type)) { if (!isAnAggPlot) devId = calcRTRId(cat); varId = calcRTRVariableId(var); } else if ("MDS".equals(type)) { devId = subId; varId = calcMDSVariableId(var); } varName = varNames[varId - 1]; if (varNum == 0) yAxisLabel = yAxisLabs[varId - 1]; /*** Database.VariableInfo[] ovi = database.getVariableInfo("ROUTER_VARIABLE_INFO"); if (localDebug) { for (int i = 0; i < ovi.length; i++) { System.out.println(ovi[i].variableId); System.out.println(ovi[i].variableName); System.out.println(ovi[i].variableLabel); System.out.println(ovi[i].threshType); System.out.println(ovi[i].threshVal1); System.out.println(ovi[i].threshVal2); } } ***/ if (isAnAggPlot && ((granularity == Database.RAW) || (granularity == HEARTBEAT))) { LwatchAlert lwAlert = new LwatchAlert(pf2); lwAlert.displayDialog(true, // modal dialog "RAW & HEARTBEAT granularity not supported for " + "aggregate plots.", 1); // type 1 = info with "continue //granularity = Database.HOUR; return; } Timestamp sampNtrvlStart = null; Timestamp sampNtrvlEnd = null; if ((!updateLive) || initialLoad) { sampNtrvlStart = new Timestamp(tsStart.getTime()); sampNtrvlEnd = new Timestamp(tsEnd.getTime()); } else { sampNtrvlEnd = new Timestamp(System.currentTimeMillis()); sampNtrvlStart = new Timestamp(lastLiveNtrvlEnd + 100); //1000); // + 1); } int resolution = granularity; if (resolution == HEARTBEAT) resolution = 1; if (limitedDebug) Debug.out("Calling get" + type + "AggregateData w/\ngranularity = " + resolution + "\nOST Id = " + devId + "\nvarName = " + varName + "\n# of curves = " + curves.length + "\nsampNtrvlStart = " + sampNtrvlStart + "\nsampNtrvlEnd = " + sampNtrvlEnd + "\nupdateLive = " + updateLive + " initialLoad = " + initialLoad + "\nfsId = " + fsId); //Debug.out("Loading " + varName + " over interval " + sampNtrvlStart + //" --> " + sampNtrvlEnd); AggregateData[] aggDat = null; try { long accessBeg = 0; if (timerOn) { accessBeg = System.currentTimeMillis(); } if ("OST".equals(type)) { if (!isAnAggPlot) aggDat = database.getOstAggregateData(resolution, devId, varName, sampNtrvlStart, sampNtrvlEnd); else { if (limitedDebug) Debug.out("\nCalling getFilesystemAggregateData" + " w/\ngranularity = " + resolution + "\nfsId = " + fsId + "\nvarName = " + varName + "\n# of curves = " + curves.length + "\nsampNtrvlStart = " + sampNtrvlStart + "\nsampNtrvlEnd = " + sampNtrvlEnd + "\nupdateLive = " + updateLive + " initialLoad = " + initialLoad); aggDat = database.getFilesystemAggregateData(resolution, fsId, varName, sampNtrvlStart, sampNtrvlEnd); } } else if ("RTR".equals(type)) { if (!isAnAggPlot) aggDat = database.getRouterAggregateData(resolution, devId, varName, sampNtrvlStart, sampNtrvlEnd); else { if (limitedDebug) Debug.out("\nCalling getRouterGroupAggregateData" + " w/\ngranularity = " + resolution + "\nRouter Group = " + this.subIdent + "\nvarName = " + varName + "\n# of curves = " + curves.length + "\nsampNtrvlStart = " + sampNtrvlStart + "\nsampNtrvlEnd = " + sampNtrvlEnd + "\nupdateLive = " + updateLive + " initialLoad = " + initialLoad); aggDat = database.getRouterGroupAggregateData(resolution, this.subIdent, // In this case, router grp varName, sampNtrvlStart, sampNtrvlEnd); } } else if ("MDS".equals(type)) { aggDat = database.getMdsAggregateData(resolution, devId, varName, sampNtrvlStart, sampNtrvlEnd); } if (timerOn) { dbAccess = (System.currentTimeMillis() - accessBeg) / 1000; } } catch (Exception e) { Debug.out("Error detected loading aggregate data for " + devId + " variable = " + varName + "\ngranularity = " + granularity + "\ntStart = " + sampNtrvlStart + "\ntEnd = " + sampNtrvlEnd); e.printStackTrace(); } if (localDebug) { if (aggDat == null) Debug.out("getAggregateData call returned null array for " + varName); else Debug.out("getAggregateData call returned array of size " + aggDat.length + " for " + varName); } if (aggDat.length <= 0) { if (granularity != HEARTBEAT) { LwatchAlert lwAlert = new LwatchAlert(pf2); lwAlert.displayDialog(true, // modal dialog "Database load request yielded no data.", 1); // type 1 = info with "continue } if (localDebug) Debug.out("Zero length return from get" + type + "AggregateData call for type " + granularity + " data."); return; } //if (localDebug) //Debug.out("ResultSet rs = " + rs.toString()); boolean reachedStart = false; boolean reachedEnd = false; //if (localDebug) //Debug.out("Grab times for interval check"); long startT = sampNtrvlStart.getTime(); long endT = sampNtrvlEnd.getTime(); long asizeL = endT - startT; if (localDebug) Debug.out("endT - startT = " + asizeL); if (granularity == Database.HOUR) asizeL /= 3600L * 1000L; else if (granularity == Database.DAY) asizeL /= 24L * 3600L * 1000L; else if (granularity == Database.WEEK) asizeL /= 7L * 24L * 3600L * 1000L; else if (granularity == Database.MONTH) { asizeL /= 31L * 24L * 3600L * 1000L; } else if (granularity == Database.YEAR) { asizeL /= 365L * 24L * 3600L * 1000L; } else // Database.RAW or HEARTBEAT asizeL /= 5000L; if (localDebug) Debug.out("endT = " + endT + " startT = " + startT + "\nasize = " + asizeL); // Add some slop to cover end points int asize = (int) (asizeL + 100L); if (asizeL <= 0L) { if (localDebug) Debug.out("asizeL calculated to be <= 0. Returning."); return; } if (limitedDebug) Debug.out("endT = " + endT + " startT = " + startT + "\nasize = " + asize); if (limitedDebug) Debug.out("# of array elements for " + varId + " = " + asize); if (varNum == 0) { if (localDebug) Debug.out("updateLive = " + updateLive + " initialLoad = " + initialLoad); if ((!updateLive) || initialLoad) { if (localDebug) Debug.out("Dimension rawData to [" + varTot + "][" + asize + "]"); rawData = new float[varTot][asize]; int varDim = varTot / curves.length; if (localDebug) { Debug.out("Dimension rawData to [" + varDim + "][" + asize + "]\n\n"); } rawTimes = new long[varDim][asize]; arrayLimit = asize; } else { if (localDebug) Debug.out("Dimension rawDataN to [" + varTot + "][" + asize + "]"); rawDataN = new float[varTot][asize]; int varDim = varTot / curves.length; if (localDebug) { Debug.out("Dimension rawDataN to [" + varDim + "][" + asize + "]"); Debug.out("varTot = " + varTot + " curves.length = " + curves.length + "\n\n"); } rawTimesN = new long[varDim][asize]; arrayLimitN = asize; } } if (localDebug) Debug.out("Size of aggregate structure array = " + asize); //Debug.out("Grab # of curves/var selected"); int numCurvesPerVar = 0; if (controls == null) { numCurvesPerVar = 1; } else { numCurvesPerVar = curves.length; //controls.getCurvePanel().getNumCurvesSelected(); } //Debug.out("# of curves/var selected = " + numCurvesPerVar); String[] lines = new String[asize]; if (varNum == 0) { if (wideView != null) wideView.first = true; } if (localDebug) Debug.out("varNum = " + varNum + " varTot = " + varTot + " asize = " + asize + " numCurvesPerVar = " + numCurvesPerVar); try { if ((!updateLive) || (initialLoad)) { for (int i = 0; i < numCurvesPerVar; i++) { // curves[i] names the curve to load. (Agg, Max, Min or Avg) int idx1 = varNum * numCurvesPerVar + i; dataPointCount[idx1] = Math.min(asize, arrayLimit); if (limitedDebug) { Debug.out("Data array size for curve " + curves[i] + " = " + dataPointCount[idx1] + "\n varNum = " + varNum + " i = " + i + " numCurvesPerVar = " + numCurvesPerVar + "\nidx1 = " + idx1 + " arrayLimit = " + arrayLimit + "\naggDat.length = " + aggDat.length); int testval = Math.min(arrayLimit, aggDat.length); Debug.out("Math.min(" + arrayLimit + ", " + aggDat.length + ") = " + testval); Debug.out("rawData.length = " + rawData.length + " idx1 = " + idx1); Debug.out("rawData[" + idx1 + "].length = " + rawData[idx1].length); } int ptCount = -1; int badValues = 0; boolean rangeSet = false; for (int j = 0; j < Math.min(arrayLimit, aggDat.length); j++) { //if (localDebug) //Debug.out("i = " + i + " j = " + j); long timeVal = aggDat[j].timestamp.getTime(); if (timeVal > 10000) { ptCount++; if (i == 0) { rawTimes[varNum][ptCount] = timeVal; } //if (localDebug) //Debug.out("i = " + i + " timeVal[" + j + "] = " + //timeVal + " " + new Date((long)timeVal).toString()); if ("Agg".equals(curves[i])) { //Debug.out("Move Agg data to rawData array."); if (aggDat != null && aggDat[0].hasAggregate) rawData[idx1][ptCount] = aggDat[j].aggregate; else rawData[idx1][ptCount] = (float) (-1.0); } else if ("Max".equals(curves[i])) { //Debug.out("Move Max data for " + j + " to rawData array."); rawData[idx1][ptCount] = aggDat[j].maxval; } else if ("Min".equals(curves[i])) { //Debug.out("Move Min data to rawData array."); rawData[idx1][ptCount] = aggDat[j].minval; } else if ("Avg".equals(curves[i])) { //Debug.out("Move Avg data to rawData array."); //if (limitedDebug) { //Debug.out("idx1 = " + idx1 + " ptCount = " + ptCount + //" j = " +j); //if (aggDat[j] == null) //Debug.out("aggDat[" + j + "] = null"); //} rawData[idx1][ptCount] = aggDat[j].average; } if (!rangeSet) { ovRangeMax[idx1] = rawData[idx1][ptCount]; ovRangeMin[idx1] = rawData[idx1][ptCount]; rangeSet = true; } else { ovRangeMax[idx1] = Math.max(ovRangeMax[idx1], rawData[idx1][ptCount]); ovRangeMin[idx1] = Math.min(ovRangeMin[idx1], rawData[idx1][ptCount]); } } else { badValues++; } } // for (int j = 0; j < asize; j++) dataPointCount[idx1] = ptCount + 1; //Math.min(asize, arrayLimit); if ((aggDat.length > 0) && (!aggDat[0].hasAggregate) && ("Agg".equals(curves[i]))) { //Debug.out(varName + " does NOT contain aggregate data"); dataPointCount[idx1] = -999; //continue; // Skip to end of loop. Continue w/ next iteration. } if (limitedDebug) { //(localDebug) { System.out.println("Out of " + Math.min(arrayLimit, aggDat.length) + " pts. " + dataPointCount[idx1] + " were good & " + badValues + " were bad."); Debug.out("Out of " + Math.min(arrayLimit, aggDat.length) + " pts. " + dataPointCount[idx1] + " were good & " + badValues + " were bad."); Debug.out("ov " + idx1 + " Y vals ranges from " + ovRangeMin[idx1] + " to " + ovRangeMax[idx1]); Debug.out("ov " + idx1 + " time ranges from " + rawTimes[idx1][0] + " to " + rawTimes[idx1][dataPointCount[idx1] - 1] + " \n" + new Timestamp(rawTimes[idx1][0]) + " to " + new Timestamp(rawTimes[idx1][dataPointCount[idx1] - 1])); } if (localDebug) { Debug.out("i = " + i + " rawTimes[0] = " + rawTimes[varNum][0] + " " + new Date(rawTimes[varNum][0]).toString()); int lasti = ptCount - 1; if (lasti >= 0) Debug.out("i = " + i + " rawTimes[" + lasti + "] = " + rawTimes[varNum][lasti] + " " + new Date(rawTimes[varNum][lasti]).toString()); } //avgVal[idx1] = aggVal[idx1] / ((double)asize); //if (localDebug) //Debug.out("Computed Avg val for curve " + idx1 + " = " + avgVal[idx1]); // Set the range min/max values used for plotting. yLo = ovRangeMin[idx1]; yHi = ovRangeMax[idx1] + (ovRangeMax[idx1] - ovRangeMin[idx1]) / 10.; //Debug.out("Data range from " + rawTimes[varNum][0] + //" to " + rawTimes[varNum][asize-1] + //"\n y Range : " + //yLo + " to " + yHi); } // for (int i = 0; i < numCurvesPerVar; i++) } else { // update live is true && not initialLoad boolean timesUpdated = false; boolean visited = false; //for (int i = 0; i < numCurvesPerVar; i++) { for (int i = 0; i < curves.length; i++) { // curves[i] names the curve to load. (Agg, Max, Min or Avg) //int idx1 = varNum * numCurvesPerVar + i; int idx1 = varNum * curves.length + i; if (rawDataN == null || idx1 >= rawDataN.length) { //Debug.out(idx1 + " >= rawDataN.length, SKIPPING."); skipThisUpdate = true; return; } //dataPointCount[idx1] = Math.min(asize, arrayLimit); if (limitedDebug) { Debug.out("Data array size for curve " + curves[i] + " = " + dataPointCount[idx1] + "\n varNum = " + varNum + " i = " + i + " numCurvesPerVar = " + numCurvesPerVar + "\nidx1 = " + idx1 + " arrayLimitN = " + arrayLimitN + "\naggDat.length = " + aggDat.length); int testval = Math.min(arrayLimit, aggDat.length); Debug.out("Math.min(" + arrayLimit + ", " + aggDat.length + ") = " + testval); Debug.out("rawDataN[" + idx1 + "].length = " + rawDataN[idx1].length); } int ptCount = 0; //-1; int badValues = 0; //boolean rangeSet = false; for (int j = 0; j < Math.min(arrayLimitN, aggDat.length); j++) { //if (localDebug) //Debug.out("i = " + i + " j = " + j); long timeVal = aggDat[j].timestamp.getTime(); if (timeVal > 10000L) { //ptCount++; if (i == 0) { //if (j == 0) //Debug.out("Loading update data for var = " + varNum + "\n"); if (varNum >= rawTimesN.length) { //Debug.out("updateLive = " + updateLive + " initialLoad = " + initialLoad); //Debug.out("Problem with incremental raw Time array length.\n" + //"varNum = " + varNum + " rawTimesN.length = " + rawTimesN.length); //Thread.dumpStack(); //System.out.println("\n"); //Debug.out("SKIPPING varnum = " + varNum + " idx1 = " + idx1); skipThisUpdate = true; return; } if (timeVal <= rawTimes[varNum][dataPointCount[varNum] - 1]) continue; //Debug.out("Assign " + aggDat[j].timestamp + " to rawTimesN[" + varNum + "][" + ptCount + "]"); rawTimesN[varNum][ptCount] = timeVal; } //if (localDebug) //Debug.out("i = " + i + " timeVal[" + j + "] = " + //timeVal + " " + new Date((long)timeVal).toString()); if ("Agg".equals(curves[i])) { //Debug.out("Move Agg data to rawDataN array."); rawDataN[idx1][ptCount] = aggDat[j].aggregate; } else if ("Max".equals(curves[i])) { //Debug.out("Move Max data for " + j + " to rawDataN array."); rawDataN[idx1][ptCount] = aggDat[j].maxval; } else if ("Min".equals(curves[i])) { //Debug.out("Move Min data to rawDataN array."); rawDataN[idx1][ptCount] = aggDat[j].minval; } else if ("Avg".equals(curves[i])) { //Debug.out("Move Avg data to rawDataN array. " + ptCount + " <= " + j); //if (limitedDebug) { //Debug.out("idx1 = " + idx1 + " ptCount = " + ptCount + //" j = " +j); //if (aggDat[j] == null) //Debug.out("aggDat[" + j + "] = null"); //} rawDataN[idx1][ptCount] = aggDat[j].average; } //if (! rangeSet) { //ovRangeMax[idx1] = rawData[idx1][ptCount]; //ovRangeMin[idx1] = rawData[idx1][ptCount]; //rangeSet = true; //} else { ovRangeMax[idx1] = Math.max(ovRangeMax[idx1], rawDataN[idx1][ptCount]); ovRangeMin[idx1] = Math.min(ovRangeMin[idx1], rawDataN[idx1][ptCount]); //} ptCount++; } else { badValues++; } } // for (int j = 0; j < asize; j++) if (limitedDebug) { Debug.out("# of new data points added = " + ptCount); if (dataPointCount[idx1] > 0) { Debug.out("Length of rawTimes[" + varNum + " = " + rawTimes[varNum].length); Debug.out("Last Time from original dataset = " + new Timestamp(rawTimes[varNum][dataPointCount[idx1] - 1])); Debug.out("New times added :"); for (int j = 0; j < ptCount; j++) Debug.out(j + " = " + new Timestamp(rawTimesN[varNum][j])); } else Debug.out("dataPointCount[" + idx1 + "] = " + dataPointCount[idx1]); } //dataPointCount[idx1] = ptCount + 1; //Math.min(asize, arrayLimit); // Shift off oldest ptCount values from rawData and rawTimes arrays if (ptCount < rawData[idx1].length) { //Debug.out("Update curve " + idx1 + " with " + ptCount + " points."); // Shift off oldest ptCount values from rawData and rawTimes arrays if (ptCount > 0) { for (int j = ptCount; j < dataPointCount[idx1]; j++) { if ((aggDat[0].hasAggregate) || (!"Agg".equals(curves[i]))) { rawData[idx1][j - ptCount] = rawData[idx1][j]; if (!timesUpdated) { //(i == 0) { if (j == ptCount) //Debug.out("Shift " + ptCount + " points off trailing edge for var # " + varNum + //" dataPointCount = " + dataPointCount[idx1]); if (limitedDebug) { int kk = j - ptCount; Debug.out("Shift values to rawTimes[" + varNum + "][" + kk + "] <== rawTimes[" + varNum + "][" + j + "]"); } rawTimes[varNum][j - ptCount] = rawTimes[varNum][j]; } } } // Append new values to end of rawData and rawTimes arrays for (int j = 0; j < ptCount; j++) { int ishift = dataPointCount[idx1] + (j - ptCount); if (limitedDebug) { Debug.out("Assign " + new Timestamp(rawTimesN[varNum][j]) + " from New " + j + " to Old " + ishift); } if ((aggDat[0].hasAggregate) || (!"Agg".equals(curves[i]))) { if (limitedDebug) Debug.out("Assign new values for " + curves[i] + " rawData[" + idx1 + "][" + ishift + "] <== rawDataN[" + idx1 + "][" + j + "]"); rawData[idx1][ishift] = rawDataN[idx1][j]; if (!timesUpdated) { //if (j == 0) //Debug.out("Append " + ptCount + " to end for var # " + //varNum + " ishift = " + ishift + "\n"); if (limitedDebug) Debug.out("Assign new values to rawTimes[" + varNum + "][" + ishift + "] <== rawTimesN[" + varNum + "][" + j + "]\n"); rawTimes[varNum][ishift] = rawTimesN[varNum][j]; visited = true; } } } if (visited) timesUpdated = true; } } else { if (varNum == 0) { rawData = null; rawTimes = null; rawData = new float[varTot][ptCount + 100]; rawTimes = new long[nRowsSelected * nColsSelected][ptCount + 100]; } dataPointCount[idx1] = ptCount; for (int j = 0; j < ptCount; j++) { rawData[idx1][j] = rawDataN[idx1][j]; rawTimes[idx1][j] = rawTimesN[idx1][j]; } } // Set the range min/max values used for plotting. yLo = ovRangeMin[idx1]; yHi = ovRangeMax[idx1] + (ovRangeMax[idx1] - ovRangeMin[idx1]) / 10.; } // for (int i = 0; i < numCurvesPerVar; i++) } // End of if ((!updateLive) || (initialLoad)) { } catch (Exception e) { Debug.out("Exception caught while processing AggregateData array.\n" + e.getMessage()); e.printStackTrace(); } if (timerOn) { loadMethodTot = (System.currentTimeMillis() - loadBeg) / 1000; System.out.println("\nOST Variable varNum = " + cat + " " + var + " " + varNum); if ((!updateLive) || initialLoad) System.out.println("Total number of array values returned from DB = " + arrayLimit); else System.out.println("Total number of array values returned from DB = " + arrayLimitN); System.out.println("Total time for loadHistorical___Data = " + loadMethodTot + " seconds."); System.out.println("Total DB access time for loadHistorical___Data = " + dbAccess + " seconds."); } // Done }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<DisconnectChangeOrdeReportDTO> viewDisconnectionChangeOrderReport( DisconnectChangeOrdeReportDTO objDto) { // Nagarjuna String methodName = "viewDisconnectionChangeOrderReport", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null;/*from w ww .j a v a2s . c om*/ int recordCount = 0; ArrayList<DisconnectChangeOrdeReportDTO> listSearchDetails = new ArrayList<DisconnectChangeOrdeReportDTO>(); DisconnectChangeOrdeReportDTO objReportsDto = null; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetDisconnectionChangeOrderReport); if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) { proc.setString(1, objDto.getOrdermonth().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(4, objDto.getServiceName().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } if (objDto.getOrdersubtype() != null && !"".equals(objDto.getOrdersubtype())) { proc.setInt(5, Integer.parseInt(objDto.getOrdersubtype().trim())); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(6, objDto.getVerticalDetails().trim()); } else { proc.setNull(6, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(7, pagingSorting.getSortByColumn());// columnName proc.setString(8, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(9, pagingSorting.getStartRecordId());// start index proc.setInt(10, pagingSorting.getEndRecordId());// end index proc.setInt(11, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(12, objDto.getIsUsage()); if (objDto.getSrrequest() != null && !"".equals(objDto.getSrrequest())) { proc.setString(13, objDto.getSrrequest()); } else { proc.setNull(13, java.sql.Types.VARCHAR); } // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new DisconnectChangeOrdeReportDTO(); //[270513]Start : Added by Ashutosh for Billing Address setBlank(); //replaceSeperator("BILLING_LOCATION",rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); //[270513]Start objReportsDto.setLogicalCircuitId(rs.getString("LOGICALCIRCUITID")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objReportsDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objReportsDto.setCreditPeriod(rs.getInt("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingformat(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelId(rs.getInt("BILLING_LEVEL_NO")); objReportsDto.setPoNumber(rs.getInt("PONUMBER")); tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setParty(rs.getString("PARTYNAME")); objReportsDto.setPm_pro_date(rs.getString("Pm_Prov_Date")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (!(rs.getString("LOCDATE") == null || rs.getString("LOCDATE") == "")) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); objReportsDto.setChild_act_no(rs.getString("Child_Account_Number")); objReportsDto.setChild_ac_fxstatus(rs.getString("Child_Account_FX_Sataus")); tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objReportsDto.setOrderDate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setOrder_type(rs.getString("ORDERTYPE")); objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setBillingtrigger_createdate(Utility.showDate_Report(tempDate).toUpperCase()); } ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setBillingtrigger_createdate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setActmgrname(rs.getString("ACCOUNTMANAGER")); objReportsDto.setPrjmngname(rs.getString("PROJECTMANAGER")); tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objReportsDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("SERVICE_RFS_DATE"); if (!(rs.getString("SERVICE_RFS_DATE") == null || rs.getString("SERVICE_RFS_DATE") == "")) { objReportsDto.setRfs_date((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objReportsDto.setCust_po_rec_date((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegion(rs.getString("REGION")); objReportsDto.setDemo(rs.getString("Demo_Type")); objReportsDto.setNewOrderRemark(rs.getString("NEWORDER_REMARKS")); objReportsDto.setOrderStageDescription(rs.getString("STAGE")); objReportsDto.setMocn_no(rs.getString("MOCN_NO")); objReportsDto.setDisconnection_remarks(rs.getString("DISCONNECTION_REMARKS")); objReportsDto.setRequest_rec_date(rs.getString("DISCONNECTION_RECEIVE_DATE")); if (!(rs.getString("DISCONNECTION_RECEIVE_DATE") == null || rs.getString("DISCONNECTION_RECEIVE_DATE") == "")) { //Date date=df.parse(objReportsDto.getRequest_rec_date()); //objReportsDto.setRequest_rec_date((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLineno(rs.getInt("Order_Line_Id")); objReportsDto.setOrdermonth(rs.getString("ORDERMONTH")); objReportsDto.setCkt_id(rs.getString("CKTID")); objReportsDto.setStandard_reason(rs.getString("STANDARDREASON")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setBandwidth_att(rs.getString("BANDWIDTH_ATT")); objReportsDto.setUom(rs.getString("UOM")); objReportsDto.setRate_code(rs.getString("RATECODE")); objReportsDto.setLast_mile_media(rs.getString("LAST_MILE_MEDIA")); objReportsDto.setLast_mile_remarks(rs.getString("LAST_MILE_REMARKS")); objReportsDto.setChargeable_Distance(rs.getString("DISTANCE")); objReportsDto.setLast_mile_provider(rs.getString("LAST_MILE_PROVIDER")); objReportsDto.setLink_type(rs.getString("LINK_TYPE")); objReportsDto.setIndicative_value(rs.getString("INDICATIVE_VALUE")); objReportsDto.setProductName(rs.getString("SERVICEDETDESCRIPTION")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); objReportsDto.setLocno(rs.getString("LOCNO")); objReportsDto.setPrimarylocation(VAR_PRIMARYLOCATION); objReportsDto.setProdAlisName(rs.getString("PRODUCTNAME")); objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setSeclocation(VAR_SECONDARYLOCATION); objReportsDto.setSub_linename(rs.getString("SUBPRODUCTNAME")); objReportsDto.setOrderNo(rs.getString("ORDERNO")); objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setAmt(rs.getLong("CUST_TOT_PO_AMT")); objReportsDto.setTotalAmountIncludingCurrent(rs.getDouble("TOTALPOAMOUNT")); objReportsDto.setAdvance(rs.getString("ADVANCE")); objReportsDto.setContractMonths(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); objReportsDto.setPeriodsInMonths(rs.getString("PERIODS_IN_MONTH")); objReportsDto.setTotalPoAmt(rs.getString("CUST_TOT_PO_AMT")); objReportsDto.setParty_id(rs.getInt("PARTY_NO")); objReportsDto.setCust_act_id(rs.getString("CUSTACCOUNTID")); objReportsDto.setM6_prod_id(rs.getString("M6_PRODUCT_ID")); objReportsDto.setM6_order_id(rs.getString("M6ORDERNO")); objReportsDto.setPre_crmorderid(rs.getInt("Pre_Crm_orderNo")); objReportsDto.setM6cktid(rs.getString("M6_CKTID")); objReportsDto.setBilling_location_from(rs.getString("FROM_ADDRESS")); objReportsDto.setBilling_location_to(rs.getString("TO_ADDRESS")); if (objDto.getIsUsage() == 1) { objReportsDto.setOrderStage(rs.getString("ORDERSTAGE")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setLineno(rs.getInt("Order_Line_Id")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); ComponentsDto dto = new ComponentsDto(); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDate(rs.getString("COMPONENT_START_DATE")); if (rs.getString("COMPONENT_START_DATE") != null && !"".equals(rs.getString("COMPONENT_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEnd_date(rs.getString("COMPONENT_END_DATE")); if (rs.getString("COMPONENT_END_DATE") != null && !"".equals(rs.getString("COMPONENT_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((Utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); dto.setEndFxStatus(rs.getString("FX_END_STATUS")); dto.setEndTokenNo(rs.getString("LOCAL_END_COMPONENT_TOKEN_NO")); //dto.setComponentFXStatus(rs.getString("FX_STATUS")); objReportsDto.setStartDateDays(rs.getInt("COMP_START_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("COMP_START_MONTHS")); objReportsDto.setEndDateDays(rs.getInt("COMP_END_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("COMP_END_MONTHS")); objReportsDto.setSourcePartyID(rs.getLong("PARTY_ID")); objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setCopcapprovaldate(rs.getString("COPC_APPROVED_DATE")); ts = rs.getTimestamp("COPC_APPROVED_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setCopcapprovaldate((utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setSrno(rs.getString("SR_NO")); objReportsDto.setCus_segment(rs.getString("CUST_SEGMENT_CODE")); objReportsDto.setDesiredDueDate(rs.getString("DESIRED_DUE_DATE")); objReportsDto.setDerivedDisconnectionDate(rs.getString("DERIVEDDISCONNECTIONDATE")); objReportsDto.setIsTriggerRequired(rs.getString("ISTRIGGERREQUIRED(Y/N)")); objReportsDto.setLineTriggered(rs.getString("LINETRIGGERED(Y/N)")); objReportsDto.setTriggerProcess(rs.getString("TRIGGERPROCESS")); objReportsDto.setTriggerDoneBy(rs.getString("BILLINGTRIGGERDONEBY")); objReportsDto.setAutomaticTriggerError(rs.getString("AUTOMATICTRIGGERERROR")); /*20151224-R1-021980 - Changes in Disconnection Report ||ends*/ objReportsDto.setComponentDto(dto); } else { objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) { String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"), rs.getString("ENTITYID")); objReportsDto.setBill_period(tBillPeriod); } objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setChargeEndDate(rs.getString("END_DATE")); if (!(rs.getString("END_DATE") == null || rs.getString("END_DATE") == "")) { Date date = df.parse(objReportsDto.getChargeEndDate()); objReportsDto.setChargeEndDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setStartDate(rs.getString("START_DATE")); if (!(rs.getString("START_DATE") == null || rs.getString("START_DATE") == "")) { Date date = df.parse(objReportsDto.getStartDate()); objReportsDto.setStartDate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setStore(rs.getString("STORENAME")); objReportsDto.setHardwareType(rs.getString("HARDWARETYPENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setNature_sale(rs.getString("SALENATURE")); objReportsDto.setType_sale(rs.getString("SALETYPE")); objReportsDto.setAnnitation(rs.getString("ANNOTATION")); objReportsDto.setTokenno(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (!(rs.getString("CHALLEN_DATE") == null || rs.getString("CHALLEN_DATE") == "")) { Date date = df.parse(objReportsDto.getChallendate()); objReportsDto.setChallendate((utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setHardware_flag(rs.getString("HARDWARE_FLAG")); objReportsDto.setChargeTypeID(rs.getInt("CHARGETYPEID")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setSrno(rs.getString("SR_NO")); objReportsDto.setDispatchAddress1(rs.getString("DISP_ADDRESS1")); objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO")); objReportsDto.setCharge_hdr_id(rs.getInt("CHARGE_HDR_ID")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setInstallation_addressaa1(rs.getString("INSTALLEMENT")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setAnnualRate(rs.getString("ANNUAL_RATE")); objReportsDto.setPk_charge_id(rs.getString("CHARGEINFOID")); objReportsDto.setBusiness_serial_no(rs.getString("Business_No")); /*20151224-R1-021980 - Changes in Disconnection Report || ROM * CHanged by :satish Start*/ objReportsDto.setDesiredDueDate(rs.getString("DESIRED_DUE_DATE")); objReportsDto.setDerivedDisconnectionDate(rs.getString("DERIVEDDISCONNECTIONDATE")); objReportsDto.setIsTriggerRequired(rs.getString("ISTRIGGERREQUIRED(Y/N)")); objReportsDto.setLineTriggered(rs.getString("LINETRIGGERED(Y/N)")); objReportsDto.setTriggerProcess(rs.getString("TRIGGERPROCESS")); objReportsDto.setTriggerDoneBy(rs.getString("BILLINGTRIGGERDONEBY")); objReportsDto.setAutomaticTriggerError(rs.getString("AUTOMATICTRIGGERERROR")); /*20151224-R1-021980 - Changes in Disconnection Report ||ends*/ } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.funambol.foundation.items.dao.PIMContactDAO.java
/** * Updates a contact./*from w w w. j a va 2 s . c om*/ * * @param cw as a ContactWrapper object. If its last update time is null, * then it's set to the current time. * @return the UID of the contact * @throws DAOException * * @see ContactWrapper */ public String updateItem(ContactWrapper cw) throws DAOException { Connection con = null; PreparedStatement ps = null; PreparedStatement ps1 = null; ResultSet rs = null; int type = 0; PersonalDetail personalDetail = null; BusinessDetail businessDetail = null; Address homeAddressBook = null; Address workAddressBook = null; Address otherAddressBook = null; Name name = null; Phone phone = null; Email email = null; WebPage webPage = null; List<WebPage> webPages = new ArrayList<WebPage>(); List<Email> emails = new ArrayList<Email>(); List<Phone> phones = new ArrayList<Phone>(); List<String[]> labels = new ArrayList<String[]>(); String phoneType = null; String webPageType = null; StringBuffer queryUpdateFunPimContact = null; Short importance = null; Short sensitivity = null; String mileage = null; String subject = null; String folder = null; String anniversary = null; String firstName = null; String middleName = null; String lastName = null; String displayName = null; String birthday = null; String note = null; String categories = null; String hobbies = null; String gender = null; String initials = null; String languages = null; String nickName = null; String spouse = null; String suffix = null; String assistant = null; String company = null; String companies = null; String department = null; String jobTitle = null; String manager = null; String city = null; String state = null; String role = null; String children = null; String salutation = null; String officeLocation = null; String street = null; String postalCode = null; String country = null; String postOfficeAddress = null; String extendedAddress = null; String[] addressFields = null; boolean findRecord = false; boolean emptyAddress = false; short photoType = ContactWrapper.EMPTY_PHOTO; boolean photoToRemove = false; boolean photoToSet = false; boolean photoNothingToDo = false; StringBuffer sqlUpdateFunPimAddress = null; try { Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : cw.getLastUpdate(); // Looks up the data source when the first connection is created con = getUserDataSource().getRoutedConnection(userId); Contact c = cw.getContact(); personalDetail = c.getPersonalDetail(); businessDetail = c.getBusinessDetail(); name = c.getName(); importance = c.getImportance(); sensitivity = c.getSensitivity(); mileage = c.getMileage(); subject = c.getSubject(); languages = c.getLanguages(); folder = c.getFolder(); categories = Property.stringFrom(c.getCategories()); if (personalDetail != null) { homeAddressBook = personalDetail.getAddress(); otherAddressBook = personalDetail.getOtherAddress(); anniversary = personalDetail.getAnniversary(); birthday = personalDetail.getBirthday(); children = personalDetail.getChildren(); spouse = personalDetail.getSpouse(); hobbies = personalDetail.getHobbies(); gender = personalDetail.getGender(); webPages.addAll(personalDetail.getWebPages()); emails.addAll(personalDetail.getEmails()); phones.addAll(personalDetail.getPhones()); } if (businessDetail != null) { assistant = businessDetail.getAssistant(); manager = businessDetail.getManager(); workAddressBook = businessDetail.getAddress(); companies = businessDetail.getCompanies(); company = Property.stringFrom(businessDetail.getCompany()); department = Property.stringFrom(businessDetail.getDepartment()); role = Property.stringFrom(businessDetail.getRole()); officeLocation = businessDetail.getOfficeLocation(); webPages.addAll(businessDetail.getWebPages()); emails.addAll(businessDetail.getEmails()); phones.addAll(businessDetail.getPhones()); } if (name != null) { firstName = Property.stringFrom(name.getFirstName()); middleName = Property.stringFrom(name.getMiddleName()); lastName = Property.stringFrom(name.getLastName()); displayName = Property.stringFrom(name.getDisplayName()); initials = Property.stringFrom(name.getInitials()); nickName = Property.stringFrom(name.getNickname()); suffix = Property.stringFrom(name.getSuffix()); salutation = Property.stringFrom(name.getSalutation()); } if (c.getNotes() != null && c.getNotes().size() > 0) { note = ((Note) c.getNotes().get(0)).getPropertyValueAsString(); } else { note = null; } if (businessDetail.getTitles() != null && businessDetail.getTitles().size() > 0) { jobTitle = ((Title) businessDetail.getTitles().get(0)).getPropertyValueAsString(); } else { jobTitle = null; } queryUpdateFunPimContact = new StringBuffer(); queryUpdateFunPimContact.append( SQL_UPDATE_FNBL_PIM_CONTACT_BEGIN + SQL_FIELD_LAST_UPDATE + SQL_EQUALS_QUESTIONMARK_COMMA); // // Updating photo: // 1. if the contact doesn't have a photo (photo null), // nothing should be done (If there is a photo in the db this will // be kept) // 2. if the contact has a photo (image or url) it must be inserted // in the db // 3. if the photo has a photo but the image and the url are null, // the one in the db must be removed // Photo photo = personalDetail.getPhotoObject(); if (photo == null) { // // nothing to do // photoNothingToDo = true; } else { if (photo.getImage() != null) { photoType = ContactWrapper.PHOTO_IMAGE; photoToSet = true; } else if (photo.getUrl() != null) { photoType = ContactWrapper.PHOTO_URL; photoToSet = true; } else { photoToRemove = true; photoType = ContactWrapper.EMPTY_PHOTO; } queryUpdateFunPimContact.append(SQL_FIELD_PHOTO_TYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (importance != null) { queryUpdateFunPimContact.append(SQL_FIELD_IMPORTANCE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (sensitivity != null) { queryUpdateFunPimContact.append(SQL_FIELD_SENSITIVITY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (subject != null) { queryUpdateFunPimContact.append(SQL_FIELD_SUBJECT + SQL_EQUALS_QUESTIONMARK_COMMA); } if (folder != null) { queryUpdateFunPimContact.append(SQL_FIELD_FOLDER + SQL_EQUALS_QUESTIONMARK_COMMA); } if (anniversary != null) { queryUpdateFunPimContact.append(SQL_FIELD_ANNIVERSARY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (firstName != null) { queryUpdateFunPimContact.append(SQL_FIELD_FIRST_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); } if (middleName != null) { queryUpdateFunPimContact.append(SQL_FIELD_MIDDLE_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); } if (lastName != null) { queryUpdateFunPimContact.append(SQL_FIELD_LAST_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); } if (displayName != null) { queryUpdateFunPimContact.append(SQL_FIELD_DISPLAY_NAME + SQL_EQUALS_QUESTIONMARK_COMMA); } if (birthday != null) { queryUpdateFunPimContact.append(SQL_FIELD_BIRTHDAY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (note != null) { queryUpdateFunPimContact.append(SQL_FIELD_BODY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (categories != null) { queryUpdateFunPimContact.append(SQL_FIELD_CATEGORIES + SQL_EQUALS_QUESTIONMARK_COMMA); } if (children != null) { queryUpdateFunPimContact.append(SQL_FIELD_CHILDREN + SQL_EQUALS_QUESTIONMARK_COMMA); } if (hobbies != null) { queryUpdateFunPimContact.append(SQL_FIELD_HOBBIES + SQL_EQUALS_QUESTIONMARK_COMMA); } if (initials != null) { queryUpdateFunPimContact.append(SQL_FIELD_INITIALS + SQL_EQUALS_QUESTIONMARK_COMMA); } if (languages != null) { queryUpdateFunPimContact.append(SQL_FIELD_LANGUAGES + SQL_EQUALS_QUESTIONMARK_COMMA); } if (nickName != null) { queryUpdateFunPimContact.append(SQL_FIELD_NICKNAME + SQL_EQUALS_QUESTIONMARK_COMMA); } if (spouse != null) { queryUpdateFunPimContact.append(SQL_FIELD_SPOUSE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (suffix != null) { queryUpdateFunPimContact.append(SQL_FIELD_SUFFIX + SQL_EQUALS_QUESTIONMARK_COMMA); } if (salutation != null) { queryUpdateFunPimContact.append(SQL_FIELD_TITLE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (assistant != null) { queryUpdateFunPimContact.append(SQL_FIELD_ASSISTANT + SQL_EQUALS_QUESTIONMARK_COMMA); } if (company != null) { queryUpdateFunPimContact.append(SQL_FIELD_COMPANY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (department != null) { queryUpdateFunPimContact.append(SQL_FIELD_DEPARTMENT + SQL_EQUALS_QUESTIONMARK_COMMA); } if (jobTitle != null) { queryUpdateFunPimContact.append(SQL_FIELD_JOB_TITLE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (manager != null) { queryUpdateFunPimContact.append(SQL_FIELD_MANAGER + SQL_EQUALS_QUESTIONMARK_COMMA); } if (mileage != null) { queryUpdateFunPimContact.append(SQL_FIELD_MILEAGE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (officeLocation != null) { queryUpdateFunPimContact.append(SQL_FIELD_OFFICE_LOCATION + SQL_EQUALS_QUESTIONMARK_COMMA); } if (role != null) { queryUpdateFunPimContact.append(SQL_FIELD_PROFESSION + SQL_EQUALS_QUESTIONMARK_COMMA); } if (companies != null) { queryUpdateFunPimContact.append(SQL_FIELD_COMPANIES + SQL_EQUALS_QUESTIONMARK_COMMA); } if (gender != null) { queryUpdateFunPimContact.append(SQL_FIELD_GENDER + SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimContact .append(SQL_FIELD_STATUS + SQL_EQUALS_QUESTIONMARK + SQL_UPDATE_FNBL_PIM_CONTACT_END); ps = con.prepareStatement(queryUpdateFunPimContact.toString()); int k = 1; // // GENERAL // ps.setLong(k++, lastUpdate.getTime()); // // PHOTO TYPE // if (!photoNothingToDo) { ps.setShort(k++, photoType); } // // CONTACT DETAILS // if (importance != null) { ps.setShort(k++, importance.shortValue()); } if (sensitivity != null) { ps.setShort(k++, sensitivity.shortValue()); } if (subject != null) { if (subject.length() > SQL_SUBJECT_DIM) { subject = subject.substring(0, SQL_SUBJECT_DIM); } ps.setString(k++, subject); } // // folder // if (folder != null) { if (folder.length() > SQL_FOLDER_DIM) { folder = folder.substring(0, SQL_FOLDER_DIM); } ps.setString(k++, folder); } // // PERSONAL DETAILS // // // anniversary // if (anniversary != null) { if (anniversary.length() > SQL_ANNIVERSARY_DIM) { anniversary = anniversary.substring(0, SQL_ANNIVERSARY_DIM); } ps.setString(k++, anniversary); } // // firstName // if (firstName != null) { if (firstName.length() > SQL_FIRSTNAME_DIM) { firstName = firstName.substring(0, SQL_FIRSTNAME_DIM); } ps.setString(k++, firstName); } // // middleName // if (middleName != null) { if (middleName.length() > SQL_MIDDLENAME_DIM) { middleName = middleName.substring(0, SQL_MIDDLENAME_DIM); } ps.setString(k++, middleName); } // // lastName // if (lastName != null) { if (lastName.length() > SQL_LASTNAME_DIM) { lastName = lastName.substring(0, SQL_LASTNAME_DIM); } ps.setString(k++, lastName); } // // displayName // if (displayName != null) { if (displayName.length() > SQL_DISPLAYNAME_DIM) { displayName = displayName.substring(0, SQL_DISPLAYNAME_DIM); } ps.setString(k++, displayName); } // // birthday // if (birthday != null) { if (birthday.length() > SQL_BIRTHDAY_DIM) { birthday = birthday.substring(0, SQL_BIRTHDAY_DIM); } ps.setString(k++, birthday); } // // note // if (note != null) { if (note.length() > SQL_NOTE_DIM) { note = note.substring(0, SQL_NOTE_DIM); } ps.setString(k++, note); } // // categories // if (categories != null) { if (categories.length() > SQL_CATEGORIES_DIM) { categories = categories.substring(0, SQL_CATEGORIES_DIM); } ps.setString(k++, categories); } // // children // if (children != null) { if (children.length() > SQL_CHILDREN_DIM) { children = children.substring(0, SQL_CHILDREN_DIM); } ps.setString(k++, children); } // // hobbies // if (hobbies != null) { if (hobbies.length() > SQL_HOBBIES_DIM) { hobbies = hobbies.substring(0, SQL_HOBBIES_DIM); } ps.setString(k++, hobbies); } // // initials // if (initials != null) { if (initials.length() > SQL_INITIALS_DIM) { initials = initials.substring(0, SQL_INITIALS_DIM); } ps.setString(k++, initials); } // // languages // if (languages != null) { if (languages.length() > SQL_LANGUAGES_DIM) { languages = initials.substring(0, SQL_LANGUAGES_DIM); } ps.setString(k++, languages); } // // nickName // if (nickName != null) { if (nickName.length() > SQL_NICKNAME_DIM) { nickName = nickName.substring(0, SQL_NICKNAME_DIM); } ps.setString(k++, nickName); } // // spouse // if (spouse != null) { if (spouse.length() > SQL_SPOUSE_DIM) { spouse = spouse.substring(0, SQL_SPOUSE_DIM); } ps.setString(k++, spouse); } // // suffix // if (suffix != null) { if (suffix.length() > SQL_SUFFIX_DIM) { suffix = suffix.substring(0, SQL_SUFFIX_DIM); } ps.setString(k++, suffix); } // // salutation // if (salutation != null) { if (salutation.length() > SQL_SALUTATION_DIM) { salutation = salutation.substring(0, SQL_SALUTATION_DIM); } ps.setString(k++, salutation); } // // assistant // if (assistant != null) { if (assistant.length() > SQL_ASSISTANT_DIM) { assistant = assistant.substring(0, SQL_ASSISTANT_DIM); } ps.setString(k++, assistant); } // // company // if (company != null) { if (company.length() > SQL_COMPANY_DIM) { company = company.substring(0, SQL_COMPANY_DIM); } ps.setString(k++, company); } // // department // if (department != null) { if (department.length() > SQL_DEPARTMENT_DIM) { department = department.substring(0, SQL_DEPARTMENT_DIM); } ps.setString(k++, department); } // // jobTitle // if (jobTitle != null) { if (jobTitle.length() > SQL_TITLE_DIM) { jobTitle = jobTitle.substring(0, SQL_TITLE_DIM); } ps.setString(k++, jobTitle); } // // manager // if (manager != null) { if (manager.length() > SQL_MANAGER_DIM) { manager = manager.substring(0, SQL_MANAGER_DIM); } ps.setString(k++, manager); } // // mileage // if (mileage != null) { if (mileage.length() > SQL_MILEAGE_DIM) { mileage = mileage.substring(0, SQL_MILEAGE_DIM); } ps.setString(k++, mileage); } if (officeLocation != null) { if (officeLocation.length() > SQL_OFFICELOCATION_DIM) { officeLocation = officeLocation.substring(0, SQL_OFFICELOCATION_DIM); } ps.setString(k++, officeLocation); } // // role // if (role != null) { if (role.length() > SQL_ROLE_DIM) { role = role.substring(0, SQL_ROLE_DIM); } ps.setString(k++, role); } // // companies // if (companies != null) { if (companies.length() > SQL_COMPANIES_DIM) { companies = companies.substring(0, SQL_COMPANIES_DIM); } ps.setString(k++, companies); } // // gender // if (gender != null) { if (gender.length() > SQL_GENDER_DIM) { gender = gender.substring(0, SQL_GENDER_DIM); } ps.setString(k++, gender); } // // status // ps.setString(k++, String.valueOf('U')); // // id // ps.setLong(k++, Long.parseLong(cw.getId())); // // userId // ps.setString(k++, userId); ps.executeUpdate(); DBTools.close(null, ps, null); // // emails // if (!emails.isEmpty()) { ps1 = con.prepareStatement(SQL_CHECK_IF_IN_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = emails.size(); i < l; i++) { email = emails.get(i); if ((FIELD_EMAIL_1_ADDRESS).equals(email.getEmailType())) { type = TYPE_EMAIL_1_ADDRESS; } else if ((FIELD_EMAIL_2_ADDRESS).equals(email.getEmailType())) { type = TYPE_EMAIL_2_ADDRESS; } else if ((FIELD_EMAIL_3_ADDRESS).equals(email.getEmailType())) { type = TYPE_EMAIL_3_ADDRESS; } else if ((FIELD_INSTANT_MESSENGER).equals(email.getEmailType())) { type = TYPE_INSTANT_MESSENGER; } else { // // no save unknown property // continue; } ps1.setLong(1, Long.parseLong(cw.getId())); ps1.setInt(2, type); rs = ps1.executeQuery(); findRecord = rs.next(); rs.close(); rs = null; String emailValue = email.getPropertyValueAsString(); emailValue = StringUtils.left(emailValue, SQL_EMAIL_DIM); if (!findRecord) { if (emailValue != null && emailValue.length() != 0) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); ps.setString(3, emailValue); ps.executeUpdate(); DBTools.close(null, ps, null); } } else { if (emailValue != null) { ps = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_ITEM); ps.setString(1, emailValue); ps.setLong(2, Long.parseLong(cw.getId())); ps.setInt(3, type); } else { ps = con.prepareStatement(SQL_DELETE_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); } ps.executeUpdate(); DBTools.close(null, ps, null); } } DBTools.close(null, ps1, null); } // // phones // if (!phones.isEmpty()) { ps1 = con.prepareStatement(SQL_CHECK_IF_IN_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = phones.size(); i < l; i++) { phone = phones.get(i); phoneType = phone.getPhoneType(); if ((FIELD_HOME_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_HOME_TELEPHONE_NUMBER; } else if ((FIELD_HOME_2_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_HOME_2_TELEPHONE_NUMBER; } else if ((FIELD_HOME_FAX_NUMBER).equals(phoneType)) { type = TYPE_HOME_FAX_NUMBER; } else if ((FIELD_MOBILE_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_MOBILE_TELEPHONE_NUMBER; } else if ((FIELD_CAR_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_CAR_TELEPHONE_NUMBER; } else if ((FIELD_OTHER_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_OTHER_TELEPHONE_NUMBER; } else if ((FIELD_OTHER_FAX_NUMBER).equals(phoneType)) { type = TYPE_OTHER_FAX_NUMBER; } else if ((FIELD_PRIMARY_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_PRIMARY_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_BUSINESS_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_2_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_BUSINESS_2_TELEPHONE_NUMBER; } else if ((FIELD_BUSINESS_FAX_NUMBER).equals(phoneType)) { type = TYPE_BUSINESS_FAX_NUMBER; } else if ((FIELD_COMPANY_MAIN_TELEPHONE_NUMBER).equals(phoneType)) { type = TYPE_COMPANY_MAIN_TELEPHONE_NUMBER; } else if ((FIELD_PAGER_NUMBER).equals(phoneType)) { type = TYPE_PAGER_NUMBER; } else if ((FIELD_ASSISTANT_NUMBER).equals(phoneType)) { type = TYPE_ASSISTANT_NUMBER; } else if ((FIELD_CALLBACK_NUMBER).equals(phoneType)) { type = TYPE_CALLBACK_NUMBER; } else { // // Unknown property: saves nothing // continue; } ps1.setLong(1, Long.parseLong(cw.getId())); ps1.setInt(2, type); rs = ps1.executeQuery(); findRecord = rs.next(); String phoneValue = phone.getPropertyValueAsString(); phoneValue = StringUtils.left(phoneValue, SQL_EMAIL_DIM); if (!findRecord) { if (phoneValue != null && phoneValue.length() != 0) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); ps.setString(3, phoneValue); ps.executeUpdate(); DBTools.close(null, ps, null); } } else { if (phoneValue != null) { ps = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_ITEM); ps.setString(1, phoneValue); ps.setLong(2, Long.parseLong(cw.getId())); ps.setInt(3, type); } else { ps = con.prepareStatement(SQL_DELETE_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); } ps.executeUpdate(); DBTools.close(null, ps, null); } DBTools.close(null, null, rs); } DBTools.close(null, ps1, null); } // // web pages // if (!webPages.isEmpty()) { ps1 = con.prepareStatement(SQL_CHECK_IF_IN_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = webPages.size(); i < l; i++) { webPage = webPages.get(i); webPageType = webPage.getWebPageType(); if ((FIELD_WEB_PAGE).equals(webPageType)) { type = TYPE_WEB_PAGE; } else if ((FIELD_HOME_WEB_PAGE).equals(webPageType)) { type = TYPE_HOME_WEB_PAGE; } else if ((FIELD_BUSINESS_WEB_PAGE).equals(webPageType)) { type = TYPE_BUSINESS_WEB_PAGE; } else { // // Unknown property: saves nothing // continue; } ps1.setLong(1, Long.parseLong(cw.getId())); ps1.setInt(2, type); rs = ps1.executeQuery(); findRecord = rs.next(); String webPageValue = webPage.getPropertyValueAsString(); webPageValue = StringUtils.left(webPageValue, SQL_WEBPAGE_DIM); if (!findRecord) { if (webPageValue != null && webPageValue.length() != 0) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); ps.setString(3, webPageValue); ps.executeUpdate(); DBTools.close(null, ps, null); } } else { if (webPageValue != null) { ps = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_ITEM); ps.setString(1, webPageValue); ps.setLong(2, Long.parseLong(cw.getId())); ps.setInt(3, type); } else { ps = con.prepareStatement(SQL_DELETE_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); } ps.executeUpdate(); DBTools.close(null, ps, null); } DBTools.close(null, null, rs); } DBTools.close(null, ps1, null); } // // home address // if (homeAddressBook != null) { ps = con.prepareStatement(SQL_SELECT_FROM_FNBL_PIM_ADDRESS); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, ADDRESS_TYPE_HOME); rs = ps.executeQuery(); findRecord = rs.next(); DBTools.close(null, ps, rs); street = Property.stringFrom(homeAddressBook.getStreet()); city = Property.stringFrom(homeAddressBook.getCity()); postalCode = Property.stringFrom(homeAddressBook.getPostalCode()); state = Property.stringFrom(homeAddressBook.getState()); country = Property.stringFrom(homeAddressBook.getCountry()); postOfficeAddress = Property.stringFrom(homeAddressBook.getPostOfficeAddress()); extendedAddress = Property.stringFrom(homeAddressBook.getExtendedAddress()); street = replaceNewLine(StringUtils.left(street, SQL_STREET_DIM)); city = StringUtils.left(city, SQL_CITY_DIM); state = StringUtils.left(state, SQL_STATE_DIM); postalCode = StringUtils.left(postalCode, SQL_POSTALCODE_DIM); country = StringUtils.left(country, SQL_COUNTRY_DIM); postOfficeAddress = StringUtils.left(postOfficeAddress, SQL_POSTALOFFICEADDRESS_DIM); extendedAddress = StringUtils.left(extendedAddress, SQL_EXTENDEDADDRESS_DIM); String homeLabel = Property.stringFrom(homeAddressBook.getLabel()); if (homeLabel != null) { String[] label = { homeLabel, FIELD_HOME_LABEL }; labels.add(label); } addressFields = new String[] { street, city, postalCode, country, state, postOfficeAddress, extendedAddress }; emptyAddress = hasOnlyNullContent(addressFields); if (!emptyAddress) { if (!findRecord && !hasOnlyEmptyOrNullContent(addressFields)) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_ADDRESS); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, ADDRESS_TYPE_HOME); ps.setString(3, street); ps.setString(4, city); ps.setString(5, state); ps.setString(6, postalCode); ps.setString(7, country); ps.setString(8, postOfficeAddress); ps.setString(9, extendedAddress); ps.executeUpdate(); DBTools.close(null, ps, null); } else { sqlUpdateFunPimAddress = new StringBuffer(); sqlUpdateFunPimAddress.append(SQL_UPDATE_FNBL_PIM_ADDRESS_BEGIN); if (street != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_STREET + SQL_EQUALS_QUESTIONMARK_COMMA); } if (city != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_CITY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (state != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_STATE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (postalCode != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_POSTAL_CODE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (country != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_COUNTRY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (postOfficeAddress != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_PO_BOX + SQL_EQUALS_QUESTIONMARK_COMMA); } if (extendedAddress != null) { sqlUpdateFunPimAddress .append(SQL_FIELD_EXTENDED_ADDRESS + SQL_EQUALS_QUESTIONMARK_COMMA); } sqlUpdateFunPimAddress .append(SQL_FIELD_TYPE + SQL_EQUALS_QUESTIONMARK + SQL_UPDATE_FNBL_PIM_ADDRESS_END); ps = con.prepareStatement(sqlUpdateFunPimAddress.toString()); k = 1; if (street != null) { ps.setString(k++, street); } if (city != null) { ps.setString(k++, city); } if (state != null) { ps.setString(k++, state); } if (postalCode != null) { ps.setString(k++, postalCode); } if (country != null) { ps.setString(k++, country); } if (postOfficeAddress != null) { ps.setString(k++, postOfficeAddress); } if (extendedAddress != null) { ps.setString(k++, extendedAddress); } ps.setInt(k++, ADDRESS_TYPE_HOME); ps.setLong(k++, Long.parseLong(cw.getId())); ps.setInt(k++, ADDRESS_TYPE_HOME); ps.executeUpdate(); DBTools.close(null, ps, null); } } } // // other address // if (otherAddressBook != null) { ps = con.prepareStatement(SQL_SELECT_FROM_FNBL_PIM_ADDRESS); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, ADDRESS_TYPE_OTHER); rs = ps.executeQuery(); findRecord = rs.next(); DBTools.close(null, ps, rs); street = Property.stringFrom(otherAddressBook.getStreet()); city = Property.stringFrom(otherAddressBook.getCity()); postalCode = Property.stringFrom(otherAddressBook.getPostalCode()); state = Property.stringFrom(otherAddressBook.getState()); country = Property.stringFrom(otherAddressBook.getCountry()); postOfficeAddress = Property.stringFrom(otherAddressBook.getPostOfficeAddress()); extendedAddress = Property.stringFrom(otherAddressBook.getExtendedAddress()); street = replaceNewLine(StringUtils.left(street, SQL_STREET_DIM)); city = StringUtils.left(city, SQL_CITY_DIM); state = StringUtils.left(state, SQL_STATE_DIM); postalCode = StringUtils.left(postalCode, SQL_POSTALCODE_DIM); country = StringUtils.left(country, SQL_COUNTRY_DIM); postOfficeAddress = StringUtils.left(postOfficeAddress, SQL_POSTALOFFICEADDRESS_DIM); extendedAddress = StringUtils.left(extendedAddress, SQL_EXTENDEDADDRESS_DIM); addressFields = new String[] { street, city, postalCode, country, state, postOfficeAddress, extendedAddress }; String otherLabel = Property.stringFrom(otherAddressBook.getLabel()); if (otherLabel != null) { String[] label = { otherLabel, FIELD_OTHER_LABEL }; labels.add(label); } emptyAddress = hasOnlyNullContent(addressFields); if (!emptyAddress) { if (!findRecord && !hasOnlyEmptyOrNullContent(addressFields)) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_ADDRESS); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, ADDRESS_TYPE_OTHER); ps.setString(3, street); ps.setString(4, city); ps.setString(5, state); ps.setString(6, postalCode); ps.setString(7, country); ps.setString(8, postOfficeAddress); ps.setString(9, extendedAddress); ps.executeUpdate(); DBTools.close(null, ps, null); } else { sqlUpdateFunPimAddress = new StringBuffer(); sqlUpdateFunPimAddress.append(SQL_UPDATE_FNBL_PIM_ADDRESS_BEGIN); if (street != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_STREET + SQL_EQUALS_QUESTIONMARK_COMMA); } if (city != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_CITY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (state != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_STATE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (postalCode != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_POSTAL_CODE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (country != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_COUNTRY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (postOfficeAddress != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_PO_BOX + SQL_EQUALS_QUESTIONMARK_COMMA); } if (extendedAddress != null) { sqlUpdateFunPimAddress .append(SQL_FIELD_EXTENDED_ADDRESS + SQL_EQUALS_QUESTIONMARK_COMMA); } sqlUpdateFunPimAddress .append(SQL_FIELD_TYPE + SQL_EQUALS_QUESTIONMARK + SQL_UPDATE_FNBL_PIM_ADDRESS_END); ps = con.prepareStatement(sqlUpdateFunPimAddress.toString()); k = 1; if (street != null) { ps.setString(k++, street); } if (city != null) { ps.setString(k++, city); } if (state != null) { ps.setString(k++, state); } if (postalCode != null) { ps.setString(k++, postalCode); } if (country != null) { ps.setString(k++, country); } if (postOfficeAddress != null) { ps.setString(k++, postOfficeAddress); } if (extendedAddress != null) { ps.setString(k++, extendedAddress); } ps.setInt(k++, ADDRESS_TYPE_OTHER); ps.setLong(k++, Long.parseLong(cw.getId())); ps.setInt(k++, ADDRESS_TYPE_OTHER); ps.executeUpdate(); DBTools.close(null, ps, null); } } } // // work address // if (workAddressBook != null) { ps = con.prepareStatement(SQL_SELECT_FROM_FNBL_PIM_ADDRESS); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, ADDRESS_TYPE_WORK); rs = ps.executeQuery(); findRecord = rs.next(); DBTools.close(null, ps, rs); street = Property.stringFrom(workAddressBook.getStreet()); city = Property.stringFrom(workAddressBook.getCity()); postalCode = Property.stringFrom(workAddressBook.getPostalCode()); state = Property.stringFrom(workAddressBook.getState()); country = Property.stringFrom(workAddressBook.getCountry()); postOfficeAddress = Property.stringFrom(workAddressBook.getPostOfficeAddress()); extendedAddress = Property.stringFrom(workAddressBook.getExtendedAddress()); street = replaceNewLine(StringUtils.left(street, SQL_STREET_DIM)); city = StringUtils.left(city, SQL_CITY_DIM); state = StringUtils.left(state, SQL_STATE_DIM); postalCode = StringUtils.left(postalCode, SQL_POSTALCODE_DIM); country = StringUtils.left(country, SQL_COUNTRY_DIM); postOfficeAddress = StringUtils.left(postOfficeAddress, SQL_POSTALOFFICEADDRESS_DIM); extendedAddress = StringUtils.left(extendedAddress, SQL_EXTENDEDADDRESS_DIM); String workLabel = Property.stringFrom(workAddressBook.getLabel()); if (workLabel != null) { String[] label = { workLabel, FIELD_BUSINESS_LABEL }; labels.add(label); } addressFields = new String[] { street, city, postalCode, country, state, postOfficeAddress, extendedAddress }; emptyAddress = hasOnlyNullContent(addressFields); if (!emptyAddress) { if (!findRecord && !hasOnlyEmptyOrNullContent(addressFields)) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_ADDRESS); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, ADDRESS_TYPE_WORK); ps.setString(3, street); ps.setString(4, city); ps.setString(5, state); ps.setString(6, postalCode); ps.setString(7, country); ps.setString(8, postOfficeAddress); ps.setString(9, extendedAddress); ps.executeUpdate(); DBTools.close(null, ps, null); } else { sqlUpdateFunPimAddress = new StringBuffer(); sqlUpdateFunPimAddress.append(SQL_UPDATE_FNBL_PIM_ADDRESS_BEGIN); if (street != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_STREET + SQL_EQUALS_QUESTIONMARK_COMMA); } if (city != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_CITY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (state != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_STATE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (postalCode != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_POSTAL_CODE + SQL_EQUALS_QUESTIONMARK_COMMA); } if (country != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_COUNTRY + SQL_EQUALS_QUESTIONMARK_COMMA); } if (postOfficeAddress != null) { sqlUpdateFunPimAddress.append(SQL_FIELD_PO_BOX + SQL_EQUALS_QUESTIONMARK_COMMA); } if (extendedAddress != null) { sqlUpdateFunPimAddress .append(SQL_FIELD_EXTENDED_ADDRESS + SQL_EQUALS_QUESTIONMARK_COMMA); } sqlUpdateFunPimAddress .append(SQL_FIELD_TYPE + SQL_EQUALS_QUESTIONMARK + SQL_UPDATE_FNBL_PIM_ADDRESS_END); ps = con.prepareStatement(sqlUpdateFunPimAddress.toString()); k = 1; if (street != null) { ps.setString(k++, street); } if (city != null) { ps.setString(k++, city); } if (state != null) { ps.setString(k++, state); } if (postalCode != null) { ps.setString(k++, postalCode); } if (country != null) { ps.setString(k++, country); } if (postOfficeAddress != null) { ps.setString(k++, postOfficeAddress); } if (extendedAddress != null) { ps.setString(k++, extendedAddress); } ps.setInt(k++, ADDRESS_TYPE_WORK); ps.setLong(k++, Long.parseLong(cw.getId())); ps.setInt(k++, ADDRESS_TYPE_WORK); ps.executeUpdate(); DBTools.close(null, ps, null); } } } // // labels // if (!labels.isEmpty()) { ps1 = con.prepareStatement(SQL_CHECK_IF_IN_FNBL_PIM_CONTACT_ITEM); for (int i = 0, l = labels.size(); i < l; i++) { String[] label = labels.get(i); String labelType = label[1]; if ((FIELD_HOME_LABEL).equals(labelType)) { type = TYPE_HOME_LABEL; } else if ((FIELD_BUSINESS_LABEL).equals(labelType)) { type = TYPE_BUSINESS_LABEL; } else if ((FIELD_OTHER_LABEL).equals(labelType)) { type = TYPE_OTHER_LABEL; } else { // // Unknown property: saves nothing // continue; } ps1.setLong(1, Long.parseLong(cw.getId())); ps1.setInt(2, type); rs = ps1.executeQuery(); findRecord = rs.next(); String labelValue = StringUtils.left(label[0], SQL_LABEL_DIM); if (!findRecord) { if (labelValue != null && labelValue.length() != 0) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); ps.setString(3, labelValue); ps.executeUpdate(); DBTools.close(null, ps, null); } } else { if (labelValue != null) { ps = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_ITEM); ps.setString(1, labelValue); ps.setLong(2, Long.parseLong(cw.getId())); ps.setInt(3, type); } else { ps = con.prepareStatement(SQL_DELETE_FNBL_PIM_CONTACT_ITEM); ps.setLong(1, Long.parseLong(cw.getId())); ps.setInt(2, type); } ps.executeUpdate(); DBTools.close(null, ps, null); } DBTools.close(null, null, rs); } DBTools.close(null, ps1, null); } if (photoToSet) { setPhoto(con, Long.parseLong(cw.getId()), photo); } else if (photoToRemove) { deletePhoto(con, Long.parseLong(cw.getId())); } } catch (Exception e) { throw new DAOException("Error updating contact.", e); } finally { DBTools.close(con, ps, rs); } return cw.getId(); }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
/** * //w ww . ja v a 2 s . c o m * @param objDto * @return * @throws Exception */ public ArrayList<OrderReportNewDetailCwnDTO> viewOrderReportNew(OrderReportNewDetailCwnDTO objDto) throws Exception { //Nagarjuna String methodName = "viewOrderReportNew", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna ArrayList<OrderReportNewDetailCwnDTO> objUserList = new ArrayList<OrderReportNewDetailCwnDTO>(); Connection conn = null; CallableStatement proc = null; ResultSet rs = null; int recordCount = 0; OrderReportNewDetailCwnDTO objRDto; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { conn = DbConnection.getReportsConnectionObject(); proc = conn.prepareCall(sqlOrderReportNewForUsage); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(2, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(3, formattedDate1); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getVerticalDetails() != null && !"".equals(objDto.getVerticalDetails())) { proc.setString(4, objDto.getVerticalDetails().trim()); } else { proc.setNull(4, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(5, pagingSorting.getSortByColumn());// columnName proc.setString(6, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(7, pagingSorting.getStartRecordId());// start index proc.setInt(8, pagingSorting.getEndRecordId());// end index proc.setInt(9, (pagingSorting.isPagingToBeDone() ? 1 : 0));// end // index proc.setInt(10, (objDto.getIsUsage()));// end if (objDto.getOsp() != null && !"".equals(objDto.getOsp())) { proc.setString(11, objDto.getOsp().trim()); } else { proc.setNull(11, java.sql.Types.VARCHAR); } if (objDto.getFromCopcApprovedDate() != null && !"".equals(objDto.getFromCopcApprovedDate())) { SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr = formatter.parse(objDto.getFromCopcApprovedDate()); String formattedDate = formatter.format(dateStr); Date date1 = formatter.parse(formattedDate); formatter = new SimpleDateFormat("MM-dd-yyyy"); formattedDate = formatter.format(date1); proc.setString(12, formattedDate); //proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(12, java.sql.Types.VARCHAR); } if (objDto.getToCopcApprovedDate() != null && !"".equals(objDto.getToCopcApprovedDate())) { SimpleDateFormat formatter1 = new SimpleDateFormat("dd/MM/yyyy"); Date dateStr1 = formatter1.parse(objDto.getToCopcApprovedDate()); String formattedDate1 = formatter1.format(dateStr1); Date date2 = formatter1.parse(formattedDate1); formatter1 = new SimpleDateFormat("MM-dd-yyyy"); formattedDate1 = formatter1.format(date2); proc.setString(13, formattedDate1); } else { proc.setNull(13, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); int countFlag = 0; while (rs.next() != false) { countFlag++; setBlank(); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objRDto = new OrderReportNewDetailCwnDTO(); objRDto.setPartyName(rs.getString("PARTYNAME")); objRDto.setOrderNo(rs.getString("ORDERNO")); //objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID"));LOGICAL_SI_NO(Commented By :AKS) //objRDto.setServiceId(rs.getInt("SERVICENO"));SERVICEID(Commented By :AKS) objRDto.setQuoteNo(rs.getString("QUOTENO")); objRDto.setProductName(rs.getString("PRODUCTNAME")); objRDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); // objRDto.setPrimarylocation(rs.getString("FROM_SITE"));//AKS:Need To Add in Component View:PRIMARYLOCATION // objRDto.setSeclocation(rs.getString("TO_SITE"));//AKS:Need To Add in Component View:SECONDARYLOCATION //objRDto.setPrjmngname(rs.getString("PMNAME"));(Commented By :AKS)PROJECTMANAGER objRDto.setPrjmgremail(rs.getString("PMEMAIL"));//PMEMAIL:same:(AKS)Need To Add in Component View //objRDto.setActmngname(rs.getString("ACTMNAME"));ACCOUNTMANAGER objRDto.setZoneName(rs.getString("ZONENNAME"));//(AKS)Need To Add in Component View //objRDto.setRegionName(rs.getString("REGIONNAME"));REGION objRDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objRDto.setAct_category(rs.getString("ACCOUNTCATEGORY_DETAILS")); objRDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); //change tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objRDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } ts = rs.getTimestamp("AM_APPROVAL_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setAmApproveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } ts = rs.getTimestamp("PM_APPROVAL_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setPmApproveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } ts = rs.getTimestamp("NIO_APPROVAL_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setNio_approve_date((Utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setDemo_infrastartdate(rs.getString("DEMP_INFRA_START_DATE")); objRDto.setDemo_infra_enddate(rs.getString("DEMO_INFRA_ENDDATE")); /*objRDto.setRfs_date(rs.getString("RFS_DATE"));SERVICE_RFS_DATE if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objRDto.setRfs_date((utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))).toUpperCase()); }*/ //objRDto.setOrdercategory(rs.getString("ORDERCATEGORY"));ORDERTYPE(Commented By :AKS) objRDto.setOrderStatus(rs.getString("STATUS")); //objRDto.setLinename(rs.getString("LINENAME"));SERVICEDETDESCRIPTION(Commented By :AKS) //objRDto.setServiceProductID(rs.getInt("LINENO"));Order_Line_Id(Commented By :AKS) objRDto.setServiceName(rs.getString("SERVICENAME")); //objRDto.setAccountID(rs.getInt("ACCOUNTID"));CRMACCOUNTNO(Commented By :AKS) objRDto.setLicCompanyName(rs.getString("LCOMPANYNAME")); //objRDto.setEntity(rs.getString("COMPANYNAME"));ENTITYNAME objRDto.setServiceType(rs.getString("SERVICETYPE")); objRDto.setUom(rs.getString("UOM")); objRDto.setBandwaidth(rs.getString("BANDWIDTH")); objRDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objRDto.setBilling_uom(rs.getString("BILLING_BANDWIDTH_UOM")); objRDto.setDistance(rs.getString("DISTANCE")); //objRDto.setFrom_city(rs.getString("FROM_CITY"));//(AKS)Need To Add in Component View if (rs.getString("FROM_CITY") != null && !"".equals(rs.getString("FROM_CITY"))) { String ss[] = rs.getString("FROM_CITY").split("~~"); objRDto.setFrom_city(ss[8]); } else { objRDto.setFrom_city(rs.getString("FROM_CITY")); } //objRDto.setTo_city(rs.getString("TO_CITY"));//(AKS)Need To Add in Component View if (rs.getString("TO_CITY") != null && !"".equals(rs.getString("TO_CITY"))) { String ss[] = rs.getString("TO_CITY").split("~~"); objRDto.setTo_city(ss[8]); } else { objRDto.setTo_city(rs.getString("TO_CITY")); } objRDto.setRatio(rs.getString("RATIO")); objRDto.setTaxation(rs.getString("TAXATIONVALUE")); objRDto.setAccountManager(rs.getString("ACTMEMAIL"));//(AKS)Need To Add in Component View //objRDto.setCurrencyCode(rs.getString("CURNAME"));CURRENCYNAME objRDto.setOrderTotal(rs.getDouble("ORDERTOTAL")); //objRDto.setPoAmount(rs.getString("POAMOUNT"));TOTALPOAMOUNT objRDto.setBisource(rs.getString("BISOURCE")); objRDto.setOrderType(rs.getString("ORDERTYPE")); //objRDto.setParent_name(rs.getString("PARENTNAME"));PARENT_NAME objRDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objRDto.setOrdersubtype(rs.getString("ORDERSUBTYPE")); //Saurabh : Changes to separate charge related specific column from common if (objDto.getIsUsage() == 0) { objRDto.setChargeAmount(rs.getDouble("CHARGEAMOUNT")); objRDto.setFrequencyName(rs.getString("PAYMENTTERM")); objRDto.setChargeTypeName(rs.getString("CYCLICNONCYCLIC")); objRDto.setSub_linename(rs.getString("ORDER_SUBLINENAME")); objRDto.setChargeName(rs.getString("CHARGE_NAME")); objDto.setChargeinfoID(rs.getString("CHARGEINFOID")); objRDto.setOldordertotal(rs.getString("OLD_ORDER_TOTAL")); objRDto.setOldlineamt(rs.getString("OLD_LINE_AMT")); objRDto.setOld_contract_period(rs.getString("OLD_CONTRACTPERIOD")); objRDto.setFactory_ckt_id(rs.getString("FACTORY_CKT_ID")); objRDto.setDispatchAddress1(rs.getString("DISPATCHADDRESS")); objRDto.setLine_desc(rs.getString("LINE_ITEM_DESC")); //Start: AKS- Adding Column in Chanrge Section Which are not in View objRDto.setCopcApproveDate(rs.getString("COPC_APPROVAL_DATE")); if (rs.getString("COPC_APPROVAL_DATE") != null && !"".equals(rs.getString("COPC_APPROVAL_DATE"))) { objRDto.setCopcApproveDate( (utility.showDate_Report(new Date(rs.getTimestamp("COPC_APPROVAL_DATE").getTime()))) .toUpperCase()); } objRDto.setLogicalCircuitId(rs.getString("LOGICALCKTID")); objRDto.setServiceId(rs.getInt("SERVICENO")); objRDto.setPrjmngname(rs.getString("PMNAME")); objRDto.setActmngname(rs.getString("ACTMNAME")); objRDto.setRegionName(rs.getString("REGIONNAME")); objRDto.setRfs_date(rs.getString("RFS_DATE")); if (rs.getString("RFS_DATE") != null && !"".equals(rs.getString("RFS_DATE"))) { objRDto.setRfs_date( (utility.showDate_Report(new Date(rs.getTimestamp("RFS_DATE").getTime()))) .toUpperCase()); } objRDto.setOrdercategory(rs.getString("ORDERCATEGORY")); objRDto.setLinename(rs.getString("LINENAME")); objRDto.setServiceProductID(rs.getInt("LINENO")); objRDto.setAccountID(rs.getInt("ACCOUNTID")); objRDto.setEntity(rs.getString("COMPANYNAME")); objRDto.setCurrencyCode(rs.getString("CURNAME")); objRDto.setPoAmount(rs.getString("POAMOUNT")); objRDto.setParent_name(rs.getString("PARENTNAME")); objRDto.setPrimarylocation(rs.getString("FROM_SITE"));//AKS:Need To Add in Component View:PRIMARYLOCATION objRDto.setSeclocation(rs.getString("TO_SITE"));//AKS:Need To Add in Component View:SECONDARYLOCATION objRDto.setCustPoDate(rs.getString("CUST_PODATE")); if (rs.getString("CUST_PODATE") != null && !"".equals(rs.getString("CUST_PODATE"))) { objRDto.setCustPoDate( (utility.showDate_Report(new Date(rs.getTimestamp("CUST_PODATE").getTime()))) .toUpperCase()); } //End AKS } //Meenakshi : Changes for Usage if (objDto.getIsUsage() == 1) { objRDto.setFx_external_acc_id(rs.getString("Child_Account_Number"));//FX_ACCOUNT_EXTERNAL_ID objRDto.setFxInternalId(rs.getInt("INTERNAL_ID")); objRDto.setChild_account_creation_status(rs.getString("Child_Account_FX_Sataus"));//CHILD_ACCOUNT_FX_STATUS objRDto.setPackageID(rs.getInt("PACKAGE_ID")); objRDto.setPackageName(rs.getString("PACKAGE_NAME")); objRDto.setComponentID(rs.getInt("COMPONENT_ID")); objRDto.setComponentName(rs.getString("COMPONENT_NAME")); objRDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); //Start Adding Column in Component Section Which are present in View ts = rs.getTimestamp("COPC_APPROVED_DATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objRDto.setCopcApproveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setLogicalCircuitId(rs.getString("LOGICAL_SI_NO")); objRDto.setServiceId(rs.getInt("SERVICE_NO")); objRDto.setPrjmngname(rs.getString("PROJECTMANAGER")); objRDto.setActmngname(rs.getString("ACCOUNTMANAGER")); objRDto.setRegionName(rs.getString("REGION")); tempDate = rs.getDate("SERVICE_RFS_DATE"); if (tempDate != null) { objRDto.setRfs_date((Utility.showDate_Report(tempDate)).toUpperCase()); } objRDto.setOrdercategory(rs.getString("ORDERTYPE")); objRDto.setLinename(rs.getString("SERVICEDETDESCRIPTION")); objRDto.setServiceProductID(rs.getInt("Order_Line_Id")); objRDto.setAccountID(rs.getInt("CRMACCOUNTNO")); objRDto.setEntity(rs.getString("ENTITYNAME")); objRDto.setCurrencyCode(rs.getString("CURRENCYNAME")); objRDto.setPoAmount(rs.getString("TOTALPOAMOUNT")); objRDto.setParent_name(rs.getString("PARENT_NAME")); objRDto.setPrimarylocation(VAR_PRIMARYLOCATION);//AKS:Need To Add in Component View:PRIMARYLOCATION objRDto.setSeclocation(VAR_SECONDARYLOCATION);//AKS:Need To Add in Component View:SECONDARYLOCATION objRDto.setRE_LOGGED_LSI_NO(rs.getString("RE_LOGGED_LSI_NO")); objRDto.setPARALLEL_UPGRADE_LSI_NO(rs.getString("PARALLEL_UPGRADE_LSI_NO")); objRDto.setCHARGEDISCONNECTIONSTATUS(rs.getString("CHARGEDISCONNECTIONSTATUS")); objRDto.setSubchange_type(rs.getString("NAME_SUBTYPE")); objRDto.setFxAccountExternalId(rs.getString("CHILD_ACCOUNT_NUMBER")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objRDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } //End : AKS ComponentsDto dto = new ComponentsDto(); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); //nagarjuna OB Value Usage objDto.setObValue(BigDecimal.valueOf((rs.getDouble("OB_VALUE"))).toPlainString()); objDto.setObValueLastUpdateDate(rs.getString("OB_VALUE_LAST_UPDATE_DATE")); //nagarjuna OB Value Usage END //<!--GlobalDataBillingEfficiency BFR7 --> objRDto.setTaxExcemption_Reason(rs.getString("TAXEXCEMPTION_REASON")); objRDto.setLastApprovalRemarks(rs.getString("LAST_APPROVAL_REMARKS")); //NANCY objRDto.setePCNNo(rs.getString("EPCN_NO")); objRDto.setBillingTriggerCreateDate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (rs.getString("BILLING_TRIGGER_CREATEDATE") != null && !"".equals(rs.getString("BILLING_TRIGGER_CREATEDATE"))) { objRDto.setBillingTriggerCreateDate((utility .showDate_Report(new Date(rs.getTimestamp("BILLING_TRIGGER_CREATEDATE").getTime()))) .toUpperCase()); } objRDto.setComponentDto(dto); } objRDto.setOsp(rs.getString("OSP")); objRDto.setCustSeg_Description(rs.getString("DESCRIPTION")); /// End objRDto.setOpportunityId((rs.getString("OPPORTUNITYID"))); if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); //recordCount = countFlag; } objUserList.add(objRDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna //ex.printStackTrace(); //throw new Exception("SQL Exception : " + ex.getMessage(), ex); } finally { try { DbConnection.freeConnection(conn); } catch (Exception e) { Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna //e.printStackTrace(); //throw new Exception("Exception : " + e.getMessage(), e); } } return objUserList; }
From source file:com.funambol.foundation.items.dao.PIMCalendarDAO.java
/** * Updates a calendar.// www . ja v a 2 s. co m * * @param cw as a CalendarWrapper object. If its last update time is null, * then it's set to the current time. * @return the UID of the contact * @throws DAOException * @throws java.lang.Exception * @see CalendarWrapper */ public String updateItem(CalendarWrapper cw) throws DAOException, Exception { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; RecurrencePattern rp = null; String id = null; String allDay = null; String body = null; Boolean allDayBoolean = null; Short busyStatus = null; String categories = null; String companies = null; int duration = 0; Date dend = null; short importance = 0; String location = null; Short meetingStatus = null; String mileage = null; Date replyTime = null; short sensitivity = 0; Date dstart = null; String subject = null; short recurrenceType = -1; int interval = 0; short monthOfYear = 0; short dayOfMonth = 0; String dayOfWeekMask = null; String priority = null; short instance = 0; String startDatePattern = null; String noEndDate = null; String endDatePattern = null; int occurrences = -1; Reminder reminder = null; CalendarContent c = null; Date completed = null; String complete = null; short percentComplete = -1; String folder = null; String dstartTimeZone = null; String dendTimeZone = null; String reminderTimeZone = null; StringBuffer queryUpdateFunPimCalendar = null; try { Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : cw.getLastUpdate(); c = cw.getCalendar().getCalendarContent(); rp = c.getRecurrencePattern(); id = cw.getId(); boolean allDayB; allDayBoolean = c.getAllDay(); if (allDayBoolean != null && allDayBoolean.booleanValue()) { allDayB = true; allDay = "1"; } else { allDayB = false; allDay = "0"; } body = Property.stringFrom(c.getDescription()); if (c.getBusyStatus() != null) { busyStatus = new Short(c.getBusyStatus().shortValue()); } categories = Property.stringFrom(c.getCategories()); companies = Property.stringFrom(c.getOrganizer()); if (c.getPriority() != null) { priority = c.getPriority().getPropertyValueAsString(); if (priority != null && priority.length() > 0) { importance = Short.parseShort(priority); } // priority / importance ?? } location = Property.stringFrom(c.getLocation()); meetingStatus = c.getMeetingStatus(); if (c.getMileage() != null) { mileage = String.valueOf(c.getMileage()); } reminder = c.getReminder(); String rt = null; if (c instanceof Event) { rt = Property.stringFrom(((Event) c).getReplyTime()); replyTime = getDateFromString(allDayB, // @todo or false? rt, "000000"); } if (c.getAccessClass() != null) { String classEvent = null; classEvent = c.getAccessClass().getPropertyValueAsString(); if (classEvent != null && classEvent.length() > 0) { sensitivity = Short.parseShort(classEvent); } } if (c.getSummary() != null) { subject = c.getSummary().getPropertyValueAsString(); } else if (body != null && body.length() > 0) { String tmpBody = body; if (tmpBody.indexOf('.') != -1) { tmpBody = tmpBody.substring(0, tmpBody.indexOf('.')); } if (tmpBody.length() > SQL_SUBJECT_DIM) { tmpBody = tmpBody.substring(0, SQL_SUBJECT_DIM); } subject = tmpBody; } folder = Property.stringFrom(c.getFolder()); dstartTimeZone = timeZoneFrom(c.getDtStart()); dendTimeZone = timeZoneFrom(c.getDtEnd()); reminderTimeZone = timeZoneFrom(c.getReminder()); String sd = null; if (c.getDtStart() != null) { sd = c.getDtStart().getPropertyValueAsString(); dstart = getDateFromString(allDayB, sd, "000000"); } String ed = null; if ((sd != null && sd.length() > 0) || (c.getDtEnd() != null)) { ed = c.getDtEnd().getPropertyValueAsString(); // // Fix for Siemens S56 end date issue only for event // @todo: verify if is really need to do this // Due to this fix, in method getTwinItems() if the incoming // Event has an empty EndDate we seek into the db for Events // with EndDate equal to the StartDate value. // if (c instanceof Event) { if (ed == null || ed.length() == 0) { ed = sd; } } dend = getDateFromString(allDayB, ed, "235900"); } if (rp != null) { recurrenceType = rp.getTypeId(); interval = rp.getInterval(); monthOfYear = rp.getMonthOfYear(); dayOfMonth = rp.getDayOfMonth(); dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask()); instance = rp.getInstance(); startDatePattern = rp.getStartDatePattern(); boolean noEndDateB = rp.isNoEndDate(); if (noEndDateB) { noEndDate = "1"; } else { noEndDate = "0"; } endDatePattern = rp.getEndDatePattern(); occurrences = rp.getOccurrences(); } String dc = null; if (c instanceof Task) { Task t = (Task) c; if (t.getDateCompleted() != null) { dc = t.getDateCompleted().getPropertyValueAsString(); completed = getDateFromString(allDayB, dc, "000000"); } complete = Property.stringFrom(t.getComplete()); if (complete != null && "1".equals(complete)) { percentComplete = 100; } else { try { percentComplete = Short.parseShort(Property.stringFrom(t.getPercentComplete())); if (percentComplete < 0 || percentComplete > 100) { throw new NumberFormatException("A percentage can't be " + percentComplete); } } catch (NumberFormatException nfe) { percentComplete = -1; // the short must go on } } meetingStatus = getTaskStatus(t); } queryUpdateFunPimCalendar = new StringBuffer(); queryUpdateFunPimCalendar.append(SQL_UPDATE_FNBL_PIM_CALENDAR_BEGIN).append(SQL_FIELD_LAST_UPDATE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); if (allDayBoolean != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_ALL_DAY).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (body != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_BODY).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_BUSY_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (categories != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_CATEGORIES).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (companies != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPANIES).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_DURATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dend != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (ed != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_NULL_COMMA); } if (priority != null && priority.length() > 0) { queryUpdateFunPimCalendar.append(SQL_FIELD_IMPORTANCE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (location != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_LOCATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (meetingStatus != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_MEETING_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (mileage != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_MILEAGE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (reminder != null) { if (reminder.isActive()) { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER).append(SQL_EQUALS_ONE_COMMA); } else { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER).append(SQL_EQUALS_ZERO_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_TIME).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_REPEAT_COUNT) .append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_SOUND_FILE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_OPTIONS).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (replyTime != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REPLY_TIME).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (rt != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REPLY_TIME).append(SQL_EQUALS_NULL_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_SENSITIVITY).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dstart != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (sd != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_NULL_COMMA); } if (subject != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_SUBJECT).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_RECURRENCE_TYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_INTERVAL).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_MONTH_OF_YEAR).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_DAY_OF_MONTH).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dayOfWeekMask != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DAY_OF_WEEK_MASK).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_INSTANCE).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (startDatePattern != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_START_DATE_PATTERN) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (noEndDate != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_NO_END_DATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (endDatePattern != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_PATTERN).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else { // // When NoEndDate is true, the PatterEndDate must be empty. // if ("1".equals(noEndDate)) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_PATTERN).append(SQL_EQUALS_EMPTY_COMMA); } } queryUpdateFunPimCalendar.append(SQL_FIELD_OCCURRENCES).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (completed != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPLETED).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (dc != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPLETED).append(SQL_EQUALS_NULL_COMMA); } if (percentComplete != -1) { queryUpdateFunPimCalendar.append(SQL_FIELD_PERCENT_COMPLETE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if ("0".equals(complete)) { queryUpdateFunPimCalendar.append(SQL_FIELD_PERCENT_COMPLETE).append(SQL_PERCENT_COMPLETE_FORMULA); } if (folder != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_FOLDER).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (dstartTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_START_DATE_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (dendTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (reminderTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_STATUS).append(SQL_EQUALS_QUESTIONMARK) .append(SQL_UPDATE_FNBL_PIM_CALENDAR_END); con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(queryUpdateFunPimCalendar.toString()); int k = 1; // // lastUpdate // ps.setLong(k++, lastUpdate.getTime()); // // allDay // if (allDayBoolean != null) { ps.setString(k++, allDay); } // // body // if (body != null) { if (body.length() > SQL_BODY_DIM) { body = body.substring(0, SQL_BODY_DIM); } ps.setString(k++, body); } // // busy status // if (busyStatus != null) { ps.setShort(k++, busyStatus.shortValue()); } else { ps.setNull(k++, Types.SMALLINT); } // // categories // if (categories != null) { if (categories.length() > SQL_CATEGORIES_DIM) { categories = categories.substring(0, SQL_CATEGORIES_DIM); } ps.setString(k++, categories); } // // companies // if (companies != null) { if (companies.length() > SQL_COMPANIES_DIM) { companies = companies.substring(0, SQL_COMPANIES_DIM); } ps.setString(k++, companies); } // // duration // ps.setInt(k++, duration); // // date End // if (dend != null) { ps.setTimestamp(k++, new Timestamp(dend.getTime())); } // // priority // if (priority != null && priority.length() > 0) { ps.setShort(k++, importance); } // // location // if (location != null) { if (location.length() > SQL_COMPANIES_DIM) { location = location.substring(0, SQL_LOCATION_DIM); } ps.setString(k++, location); } // // meeting status // if (meetingStatus != null) { ps.setShort(k++, meetingStatus.shortValue()); } // // mileage // if (mileage != null) { ps.setString(k++, mileage); } // // reminder // if (reminder != null) { if (reminder.isActive()) { ps.setTimestamp(k++, getReminderTime(dstart, reminder)); ps.setInt(k++, reminder.getRepeatCount()); String soundFileValue = reminder.getSoundFile(); if (soundFileValue != null && soundFileValue.length() > SQL_SOUNDFILE_DIM) { soundFileValue = soundFileValue.substring(0, SQL_SOUNDFILE_DIM); } ps.setString(k++, soundFileValue); ps.setInt(k++, reminder.getOptions()); } else { ps.setNull(k++, Types.TIMESTAMP); ps.setInt(k++, 0); ps.setString(k++, null); ps.setInt(k++, 0); } } // // reply time // if (replyTime != null) { ps.setTimestamp(k++, new Timestamp(replyTime.getTime())); } // // sensitivity // ps.setShort(k++, sensitivity); // // date start // if (dstart != null) { ps.setTimestamp(k++, new Timestamp(dstart.getTime())); } // // subject // if (subject != null) { if (subject.length() > SQL_SUBJECT_DIM) { subject = subject.substring(0, SQL_SUBJECT_DIM); } ps.setString(k++, subject); } // // recurrence Type // ps.setShort(k++, recurrenceType); // // interval // ps.setInt(k++, interval); // // month of year // ps.setShort(k++, monthOfYear); // // day of month // ps.setShort(k++, dayOfMonth); // // day of week mask // if (dayOfWeekMask != null) { if (dayOfWeekMask.length() > SQL_DAYOFWEEKMASK_DIM) { dayOfWeekMask = dayOfWeekMask.substring(0, SQL_DAYOFWEEKMASK_DIM); } ps.setString(k++, dayOfWeekMask); } // // instance // ps.setShort(k++, instance); // // start date pattern // if (startDatePattern != null) { if (startDatePattern.length() > SQL_STARTDATEPATTERN_DIM) { startDatePattern = startDatePattern.substring(0, SQL_STARTDATEPATTERN_DIM); } ps.setString(k++, startDatePattern); } // // no end date // if (noEndDate != null) { ps.setString(k++, noEndDate); } // // end date pattern // if (endDatePattern != null) { if (endDatePattern.length() > SQL_ENDDATEPATTERN_DIM) { endDatePattern = endDatePattern.substring(0, SQL_ENDDATEPATTERN_DIM); } ps.setString(k++, endDatePattern); } // // occurrences // ps.setInt(k++, occurrences); // // completed // if (completed != null) { ps.setTimestamp(k++, new Timestamp(completed.getTime())); } // // percent completed // if (percentComplete != -1) { ps.setShort(k++, percentComplete); } // // folder // if (folder != null) { if (folder.length() > SQL_FOLDER_DIM) { folder = folder.substring(0, SQL_FOLDER_DIM); } ps.setString(k++, folder); } // // time zones // if (dstartTimeZone != null) { if (dstartTimeZone.length() > SQL_TIME_ZONE_DIM) { dstartTimeZone = dstartTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, dstartTimeZone); } if (dendTimeZone != null) { if (dendTimeZone.length() > SQL_TIME_ZONE_DIM) { dendTimeZone = dendTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, dendTimeZone); } if (reminderTimeZone != null) { if (reminderTimeZone.length() > SQL_TIME_ZONE_DIM) { reminderTimeZone = reminderTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, reminderTimeZone); } // // status // ps.setString(k++, String.valueOf('U')); // // id // ps.setLong(k++, Long.parseLong(id)); // // user id // ps.setString(k++, cw.getUserId()); ps.executeUpdate(); DBTools.close(null, ps, null); ps = con.prepareStatement(SQL_DELETE_CALENDAR_EXCEPTIONS_BY_CALENDAR); ps.setLong(1, Long.parseLong(id)); ps.executeUpdate(); DBTools.close(null, ps, null); if (recurrenceType != -1) { List<ExceptionToRecurrenceRule> exceptions = rp.getExceptions(); for (ExceptionToRecurrenceRule etrr : exceptions) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION); ps.setLong(1, Long.parseLong(id)); ps.setString(2, (etrr.isAddition() ? "1" : "0")); ps.setTimestamp(3, new Timestamp(getDateFromString(allDayB, etrr.getDate(), "000000").getTime())); ps.executeUpdate(); DBTools.close(null, ps, null); } } } catch (Exception e) { throw new DAOException("Error updating a calendar item: " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } return id; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<BillingTriggerDoneButFailedInFXDTO> viewBillingTriggerDoneButFailedInFX( BillingTriggerDoneButFailedInFXDTO objDto) { //Nagarjuna/* w w w. j a v a2 s. co m*/ String methodName = "viewBillingTriggerDoneButFailedInFX", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null; ArrayList<BillingTriggerDoneButFailedInFXDTO> listSearchDetails = new ArrayList<BillingTriggerDoneButFailedInFXDTO>(); BillingTriggerDoneButFailedInFXDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetBillingTriggerDoneButFailedInFX); if (objDto.getOrderType() != null && !"".equals(objDto.getOrderType())) { proc.setString(1, objDto.getOrderType().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getFromDate() != null && !"".equals(objDto.getFromDate())) { proc.setString(2, objDto.getFromDate().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } if (objDto.getToDate() != null && !"".equals(objDto.getToDate())) { proc.setString(3, objDto.getToDate().trim()); } else { proc.setNull(3, java.sql.Types.VARCHAR); } if (objDto.getFromOrderNo() != 0 && !"".equals(objDto.getFromOrderNo())) { proc.setInt(4, objDto.getFromOrderNo()); } else { proc.setNull(4, java.sql.Types.BIGINT); } if (objDto.getToOrderNo() != 0 && !"".equals(objDto.getToOrderNo())) { proc.setInt(5, objDto.getToOrderNo()); } else { proc.setNull(5, java.sql.Types.BIGINT); } if (objDto.getFromAccountNo() != 0 && !"".equals(objDto.getFromAccountNo())) { proc.setInt(6, objDto.getFromAccountNo()); } else { proc.setNull(6, java.sql.Types.BIGINT); } if (objDto.getToAccountNo() != 0 && !"".equals(objDto.getToAccountNo())) { proc.setInt(7, objDto.getToAccountNo()); } else { proc.setNull(7, java.sql.Types.BIGINT); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(8, pagingSorting.getSortByColumn());// columnName proc.setString(9, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(10, pagingSorting.getStartRecordId());// start index proc.setInt(11, pagingSorting.getEndRecordId());// end index proc.setInt(12, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(13, objDto.getIsUsage()); // index rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new BillingTriggerDoneButFailedInFXDTO(); //[270513]Start : Added by Ashutosh for Billing Address setBlank(); replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); //[270513]Start objReportsDto.setLogicalSINumber(rs.getInt("LOGICAL_SI_NO")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION")); //orderStageDescription tempDate = rs.getDate("CONTRACTSTARTDATE"); if (tempDate != null) { objReportsDto.setContractStartDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("CONTRACTENDDATE"); if (tempDate != null) { objReportsDto.setContractEndDate(Utility.showDate_Report(tempDate).toUpperCase()); } objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setEntity(rs.getString("ENTITYNAME")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME")); objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setPenaltyClause(rs.getString("PENALTYCLAUSE")); objReportsDto.setBillingLevel(rs.getString("BILLINGLEVEL")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setPrimaryLocation(VAR_PRIMARYLOCATION); objReportsDto.setSecondaryLocation(VAR_SECONDARYLOCATION); objReportsDto.setPonum(rs.getLong("PONUMBER")); tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setLOC_Date(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setLOC_Date(s5); } objReportsDto.setLocDate(rs.getString("LOCDATE")); if (rs.getString("LOCDATE") != null && !"".equals(rs.getString("LOCDATE"))) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (rs.getString("BILLINGTRIGGERDATE") != null && !"".equals(rs.getString("BILLINGTRIGGERDATE"))) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase()); } tempDate = rs.getDate("ORDERDATE"); if (tempDate != null) { objReportsDto.setOrderDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("ORDER_APPROVED_DATE"); if (tempDate != null) { objReportsDto.setOrderApproveDate(Utility.showDate_Report(tempDate).toUpperCase()); } ts = rs.getTimestamp("COPC_APPROVED_DATE");//Copc date if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto.setCopcApproveDate(Utility.showDate_Report(tempDate).toUpperCase()); } objReportsDto.setOrderType(rs.getString("ORDERTYPE")); // --Order Type Id // --Service Order Type // ''SERVICE ORDER TYPE DESC'' AS SERVICE_ORDER_TYPE_DESC, // "TST3"."TASK_END_DATE" as "COPC_APPROVED_DATE", // --TPOSERVICEDETAILS.BILLINGTRIGGERDATE as BILLINGTRIGGER_CREATE_DATE, // --Cust Logical Si ( Duplicate column) // --Charge Type Id objReportsDto.setServiceStage(rs.getString("SERVICE_STAGE")); objReportsDto.setAccountManager(rs.getString("ACCOUNTMANAGER")); objReportsDto.setProjectManager(rs.getString("PROJECTMANAGER")); // --"TPOMASTER"."ORDERDATE" ORDERCREATION DATE tempDate = rs.getDate("SERVICE_RFS_DATE"); if (tempDate != null) { objReportsDto.setRfsDate((Utility.showDate_Report(tempDate)).toUpperCase()); } tempDate = rs.getDate("PORECEIVEDATE"); if (rs.getString("PORECEIVEDATE") != null && !"".equals(rs.getString("PORECEIVEDATE"))) { objReportsDto.setPoRecieveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } //--Fx Status Ed objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setBillingAddress(VAR_BILLING_ADDRESS); objReportsDto.setFxSiId(rs.getString("FX_SI_ID")); objReportsDto.setCancelBy(rs.getString("CANCEL_BY")); //objReportsDto.setCanceldate(rs.getString("CANCEL_DATE"));//CANCEL_DATE tempDate = rs.getDate("CANCEL_DATE"); if (tempDate != null) { objReportsDto.setCanceldate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCancelReason(rs.getString("CANCEL_RESION")); objReportsDto.setOpms_Account_Id(rs.getString("Opms_Account_Id")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setToLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setFromLocation(rs.getString("TO_ADDRESS")); objReportsDto.setColl_Manager(rs.getString("COLL_MANAGER")); objReportsDto.setColl_Manager_Mail(rs.getString("COLL_MANAGER_MAIL")); objReportsDto.setColl_Manager_Phone(rs.getString("COLL_MANAGER_PHONE")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setOrder_type(rs.getString("DEMO_TYPE")); //--CRM ORDER ID objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); //--Charge Hdr Id objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id")); objReportsDto.setServiceId(rs.getInt("SERVICE_NO")); objReportsDto.setTotalPoAmt(rs.getString("TOTALPOAMOUNT")); // --Installment Rate //--Trai Rate //--Discount objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD")); objReportsDto.setCommitmentPeriod(rs.getInt("COMMITMENTPERIOD")); objReportsDto.setNoticePeriod(rs.getInt("NOTICEPERIOD")); //--Principal Amt // --Intrest Rate // --Period In Month objReportsDto.setPoAmount(rs.getString("CUST_TOT_PO_AMT")); // --party Id // --Cust Account id // --M6 Product Id // --M6 Order Id // --Ib Order Line Id // --Ib Service List Id // --Ib Pk Charges Id // --Fx Sno // --Fx Sno Ed // --Cust Tot Po Amt // --M6 Order No // --Business Serial No // --Bus Serial // --Advance if (objDto.getIsUsage() == 1) { objReportsDto.setOrderStage(rs.getString("ORDERSTAGE")); objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setCancelServiceReason(rs.getString("CANCEL_SERVICE_REASON")); //objReportsDto.setCancelBy(rs.getString("CANCELBY")); //objReportsDto.setCanceldate(rs.getString("CANCELDATE")); ComponentsDto dto = new ComponentsDto(); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDate(rs.getString("COMPONENT_START_DATE")); if (rs.getString("COMPONENT_START_DATE") != null && !"".equals(rs.getString("COMPONENT_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEnd_date(rs.getString("COMPONENT_END_DATE")); if (rs.getString("COMPONENT_END_DATE") != null && !"".equals(rs.getString("COMPONENT_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((Utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("SYSTEM_START_STATUS")); dto.setEndTokenNo(rs.getString("LOCAL_END_COMPONENT_TOKEN_NO")); dto.setEndFxStatus(rs.getString("FX_END_STATUS")); objReportsDto.setStartDateDays(rs.getInt("COMP_START_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("COMP_START_MONTHS")); objReportsDto.setEndDateDays(rs.getInt("COMP_END_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("COMP_END_MONTHS")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); objReportsDto.setComponentDto(dto); } else { objReportsDto.setOrderStage(rs.getString("STAGE")); objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFx_Ed_Chg_Status(rs.getString("CSTATE_FX_CHARGE_END_STATUS"));//Fx_Ed_Chg_Status objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setChallenno(rs.getString("CHALLEN_NO")); objReportsDto.setChallendate(rs.getString("CHALLEN_DATE")); if (rs.getString("CHALLEN_DATE") != null && !"".equals(rs.getString("CHALLEN_DATE"))) { Date date = df.parse(objReportsDto.getChallendate()); objReportsDto.setChallendate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); objReportsDto.setWarrantyStartDate(rs.getString("WARRENTY_START_DATE")); if (rs.getString("WARRENTY_START_DATE") != null && !"".equals(rs.getString("WARRENTY_START_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyStartDate()); objReportsDto.setWarrantyStartDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setWarrantyEndDate(rs.getString("WARRENTY_END_DATE")); if (rs.getString("WARRENTY_END_DATE") != null && !"".equals(rs.getString("WARRENTY_END_DATE"))) { Date date = df.parse(objReportsDto.getWarrantyEndDate()); objReportsDto.setWarrantyEndDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setExtSuportEndDate(rs.getString("EXT_SUPPORT_END_DATE")); if (rs.getString("EXT_SUPPORT_END_DATE") != null && !"".equals(rs.getString("EXT_SUPPORT_END_DATE"))) { Date date = df.parse(objReportsDto.getExtSuportEndDate()); objReportsDto.setExtSuportEndDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setDispatchAddressName(rs.getString("DISPATCHADDNAME")); objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setTokenNoEd(rs.getString("CSTATE_END_DETAILS_FX_TOKEN_NO"));//--Token No Ed objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setFormAvailable(rs.getString("FORM_C_AVAILABLE")); objReportsDto.setSaleNature(rs.getString("SALENATURE")); objReportsDto.setSaleType(rs.getString("SALETYPE")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setFrequencyName(rs.getString("FREQUENCY")); objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); if ((rs.getString("CONFIG_ID")) != null && !"".equals(rs.getString("CONFIG_ID")) && (rs.getString("ENTITYID")) != null && !"".equals(rs.getString("ENTITYID"))) { String tBillPeriod = fnGetUsageReportBIllPeriod(rs.getString("CONFIG_ID"), rs.getString("ENTITYID")); objReportsDto.setBillPeriod(tBillPeriod); } objReportsDto.setDnd_Dispatch_But_Not_Delivered(rs.getString("Dnd_Dispatch_But_Not_Delivered")); objReportsDto.setDnd_Dispatch_And_Delivered(rs.getString("Dnd_Dispatch_And_Delivered")); objReportsDto.setDnd_Disp_Del_Not_Installed(rs.getString("Ddni_Disp_Del_Not_Installed")); objReportsDto.setDnd_Disp_Delivered_Installed(rs.getString("Ddni_Disp_Delivered_Installed")); objReportsDto.setStartDateLogic(rs.getString("STARTDATELOGIC")); objReportsDto.setEndDateLogic(rs.getString("ENDDATELOGIC")); objReportsDto.setStartDate(rs.getString("START_DATE")); if (rs.getString("START_DATE") != null && !"".equals(rs.getString("START_DATE"))) { Date date = df.parse(objReportsDto.getStartDate()); objReportsDto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setEndDate(rs.getString("END_DATE")); if (rs.getString("END_DATE") != null && !"".equals(rs.getString("END_DATE"))) { Date date = df.parse(objReportsDto.getEndDate()); objReportsDto.setEndDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT")); objReportsDto.setCharge_status(rs.getString("CHARGES_STATUS")); objReportsDto.setStartDateDays(rs.getInt("START_DATE_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("START_DATE_MONTH")); objReportsDto.setEndDateDays(rs.getInt("END_DATE_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("END_DATE_MONTH")); objReportsDto.setAnnualRate(rs.getString("ANNUAL_RATE")); } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }
From source file:com.ibm.ioes.dao.ReportsDao_Usage.java
public ArrayList<MigratedApprovedNewOrderDetailReportDTO> viewMigAppNewOrderDetails( MigratedApprovedNewOrderDetailReportDTO objDto) { // Nagarjuna String methodName = "viewMigAppNewOrderDetails", className = this.getClass().getName(), msg = ""; boolean logToFile = true, logToConsole = true; //end Nagarjuna Connection connection = null; CallableStatement proc = null; ResultSet rs = null;//from www . j a v a 2 s. c om ArrayList<MigratedApprovedNewOrderDetailReportDTO> listSearchDetails = new ArrayList<MigratedApprovedNewOrderDetailReportDTO>(); MigratedApprovedNewOrderDetailReportDTO objReportsDto = null; int recordCount = 0; Utility utility = new Utility(); DateFormat df = new SimpleDateFormat("dd/MM/yyyy"); Date tempDate = null; Timestamp ts = null; try { connection = DbConnection.getReportsConnectionObject(); proc = connection.prepareCall(sqlGetMigAppNewOrderDetails); if (objDto.getOrdermonth() != null && !"".equals(objDto.getOrdermonth())) { proc.setString(1, objDto.getOrdermonth().trim()); } else { proc.setNull(1, java.sql.Types.VARCHAR); } if (objDto.getServiceName() != null && !"".equals(objDto.getServiceName())) { proc.setString(2, objDto.getServiceName().trim()); } else { proc.setNull(2, java.sql.Types.VARCHAR); } PagingSorting pagingSorting = objDto.getPagingSorting(); pagingSorting.sync();// To calculate start index and Enc Index proc.setString(3, pagingSorting.getSortByColumn());// columnName proc.setString(4, PagingSorting.DB_Asc_Desc(pagingSorting.getSortByOrder()));// sort order proc.setInt(5, pagingSorting.getStartRecordId());// start index proc.setInt(6, pagingSorting.getEndRecordId());// end index proc.setInt(7, (pagingSorting.isPagingToBeDone() ? 1 : 0)); proc.setInt(8, objDto.getIsUsage()); if (objDto.getOrderyear() != null && !"".equals(objDto.getOrderyear())) { proc.setString(9, objDto.getOrderyear().trim()); } else { proc.setNull(9, java.sql.Types.VARCHAR); } rs = proc.executeQuery(); while (rs.next()) { objReportsDto = new MigratedApprovedNewOrderDetailReportDTO(); //[270513]Start : Added by Ashutosh for Billing Address setBlank(); replaceSeperator("BILLING_LOCATION", rs.getString("BILLING_ADDRESS")); replaceSeperator("PRIMARYLOCATION", rs.getString("PRIMARYLOCATION")); replaceSeperator("SECONDARYLOCATION", rs.getString("SECONDARYLOCATION")); objReportsDto.setOrderNumber(rs.getInt("ORDERNO")); objReportsDto.setOrderType(rs.getString("ORDERTYPE")); objReportsDto.setCrmAccountNoString(rs.getString("CRMACCOUNTNO")); objReportsDto.setPartyName(rs.getString("PARTYNAME")); objReportsDto.setParty_no(rs.getInt("Party_NO")); objReportsDto.setVerticalDetails(rs.getString("VERTICAL_DETAILS")); objReportsDto.setRegionName(rs.getString("REGION")); objReportsDto.setM6OrderNo(rs.getInt("M6ORDERNO")); objReportsDto.setServiceName(rs.getString("SERVICENAME")); objReportsDto.setCustomer_logicalSINumber(rs.getInt("CUSTOMER_LOGICAL_SI_NO")); objReportsDto.setServiceDetDescription(rs.getString("SERVICEDETDESCRIPTION"));//LineName //objReportsDto.setServiceOrderType(rs.getString("SERVICETYPE")); objReportsDto.setLogicalCircuitId(rs.getString("CKTID")); objReportsDto.setFromLocation(rs.getString("FROM_ADDRESS")); objReportsDto.setToLocation(rs.getString("TO_ADDRESS")); objReportsDto.setEntity(rs.getString("ENTITYNAME"));//Legal Entity objReportsDto.setLcompanyname(rs.getString("LCOMPANYNAME")); objReportsDto.setCurrencyName(rs.getString("CURRENCYNAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDITPERIOD")); objReportsDto.setBillingTypeName(rs.getString("BILLING_TYPENAME"));//Bill Type objReportsDto.setBillingFormatName(rs.getString("BILLING_FORMATNAME")); objReportsDto.setBillingLevelName(rs.getString("BILLING_LEVELNAME")); objReportsDto.setBillingLevelNo(rs.getLong("BILLING_LEVEL_NO")); objReportsDto.setTaxation(rs.getString("TAXATIONVALUE")); objReportsDto.setBillingMode(rs.getString("BILLINGMODE")); objReportsDto.setPonum(rs.getLong("PONUMBER")); objReportsDto.setProductName(rs.getString("PRODUCTNAME")); objReportsDto.setSubProductName(rs.getString("SUBPRODUCTNAME")); tempDate = rs.getDate("PODATE"); if (tempDate != null) { objReportsDto.setPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setContractPeriod(rs.getInt("CONTRACTPERIOD"));//Period In Month objReportsDto.setTotalPoAmt(rs.getString("CUST_TOT_PO_AMT")); tempDate = rs.getDate("PORECEIVEDATE"); if (tempDate != null) { objReportsDto.setPoRecieveDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setCustPoDetailNo(rs.getString("CUSTPONUMBER")); tempDate = rs.getDate("CUSTPODATE"); if (tempDate != null) { objReportsDto.setCustPoDate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setLOC_No(rs.getString("LOCNO")); objReportsDto.setLocDate(rs.getString("LOCDATE")); if (!(rs.getString("LOCDATE") == null || rs.getString("LOCDATE") == "")) { Date date = df.parse(objReportsDto.getLocDate()); objReportsDto.setLocDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setPmApproveDate(rs.getString("Pm_Prov_Date")); if (rs.getString("Pm_Prov_Date") != null && !"".equals(rs.getString("Pm_Prov_Date"))) { String s1 = rs.getString("Pm_Prov_Date"); String s3 = s1.substring(0, 7); String s4 = s1.substring(9, 11); String s5 = s3.concat(s4); objReportsDto.setPmApproveDate(s5); } objReportsDto.setBilling_Trigger_Flag(rs.getString("Billing_Trigger_Flag")); objReportsDto.setBilling_bandwidth(rs.getString("BILLING_BANDWIDTH")); objReportsDto.setBillUom(rs.getString("BILLING_BANDWIDTH_UOM")); objReportsDto.setKmsDistance(rs.getString("DISTANCE")); objReportsDto.setBandwaidth(rs.getString("BANDWIDTH")); objReportsDto.setOrderLineNumber(rs.getInt("Order_Line_Id"));//Lineitemnumber objReportsDto.setOrdermonth(rs.getString("ORDERMONTH")); //objReportsDto.setOpms_Account_Id(rs.getString("OPMS_ACCOUNT_ID")); objReportsDto.setPrimaryLocation(VAR_PRIMARYLOCATION); objReportsDto.setSecondaryLocation(VAR_SECONDARYLOCATION); objReportsDto.setFx_external_acc_id(rs.getString("Child_Account_Number")); objReportsDto.setChildAccountFXSataus(rs.getString("Child_Account_FX_Sataus")); if (objDto.getIsUsage() == 1) { objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (!(rs.getString("BILLINGTRIGGERDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase()); } ts = rs.getTimestamp("BILLING_TRIGGER_CREATEDATE"); if (ts != null) { tempDate = new Date(ts.getTime()); objReportsDto .setBillingtrigger_createdate((Utility.showDate_Report(tempDate)).toUpperCase()); } objReportsDto.setComponentInfoID(rs.getInt("COMPONENTINFOID")); objReportsDto.setComponentID(rs.getInt("COMPONENT_ID")); objReportsDto.setComponentName(rs.getString("COMPONENT_NAME")); objReportsDto.setPackageID(rs.getInt("PACKAGE_ID")); objReportsDto.setPackageName(rs.getString("PACKAGE_NAME")); objReportsDto.setCreditPeriodName(rs.getString("CREDIT_PERIODNAME")); objReportsDto.setServiceOrderType(rs.getString("SERVICE_ORDER_TYPE_DESC")); ComponentsDto dto = new ComponentsDto(); dto.setComponentStatus(rs.getString("COMPONENT_STATUS")); dto.setStartLogic(rs.getString("COMPONENT_START_LOGIC")); dto.setStartDate(rs.getString("COMPONENT_START_DATE")); if (rs.getString("COMPONENT_START_DATE") != null && !"".equals(rs.getString("COMPONENT_START_DATE"))) { Date date = df.parse(dto.getStartDate()); dto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } dto.setEndLogic(rs.getString("COMPONENT_END_LOGIC")); dto.setEnd_date(rs.getString("COMPONENT_END_DATE")); if (rs.getString("COMPONENT_END_DATE") != null && !"".equals(rs.getString("COMPONENT_END_DATE"))) { Date date = df.parse(dto.getEnd_date()); dto.setEnd_date((Utility.showDate_Report(date)).toUpperCase()); } dto.setFxTokenNo(rs.getString("START_COMPONENT_TOKEN_NO")); dto.setFxStartStatus(rs.getString("FX_START_STATUS")); dto.setEndTokenNo(rs.getString("LOCAL_END_COMPONENT_TOKEN_NO")); dto.setEndFxStatus(rs.getString("FX_END_STATUS")); //dto.setComponentFXStatus(rs.getString("FX_STATUS")); objReportsDto.setStartDateDays(rs.getInt("COMP_START_DAYS")); objReportsDto.setStartDateMonth(rs.getInt("COMP_START_MONTHS")); objReportsDto.setEndDateDays(rs.getInt("COMP_END_DAYS")); objReportsDto.setEndDateMonth(rs.getInt("COMP_END_MONTHS")); objReportsDto.setSourcePartyID(rs.getLong("PARTY_ID")); objReportsDto.setAccountID(rs.getInt("CUSTACCOUNTID")); objReportsDto.setFx_internal_acc_id(rs.getLong("INTERNAL_ID")); dto.setComponentInstanceID(rs.getString("COMPONENT_INST_ID")); dto.setComponentType(rs.getString("COMPONENT_TYPE")); dto.setComponentAmount(rs.getDouble("COMP_AMOUNT")); objReportsDto.setComponentDto(dto); } else { objReportsDto.setBillingTriggerDate(rs.getString("BILLINGTRIGGERDATE")); if (!(rs.getString("BILLINGTRIGGERDATE") == null || rs.getString("BILLINGTRIGGERDATE") == "")) { Date date = df.parse(objReportsDto.getBillingTriggerDate()); objReportsDto.setBillingTriggerDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setBillingtrigger_createdate(rs.getString("BILLING_TRIGGER_CREATEDATE")); if (!(rs.getString("BILLING_TRIGGER_CREATEDATE") == null || rs.getString("BILLING_TRIGGER_CREATEDATE") == "")) { //Date date=df.parse(objReportsDto.getBillingtrigger_createdate()); objReportsDto.setBillingtrigger_createdate( (Utility.showDate_Report(rs.getDate("BILLING_TRIGGER_CREATEDATE"))).toUpperCase()); } objReportsDto.setRatio(rs.getString("RATIO")); objReportsDto.setBlSource(rs.getString("BL_SOURCE")); objReportsDto.setFx_St_Chg_Status(rs.getString("CSTATE_FX_CHARGE_START_STATUS"));//Fx_St_Chg_Status objReportsDto.setFxStatus(rs.getString("CSTATE_START_DETAILS_FX_STATUS"));//FX_STATUS objReportsDto.setChargeName(rs.getString("CHARGE_NAME")); objReportsDto.setChargeTypeName(rs.getString("CHARGE_TYPE")); objReportsDto.setChargeAmount_String(rs.getString("INV_AMT")); objReportsDto.setLineItemAmount(rs.getString("LINEITEMAMOUNT"));//amt objReportsDto.setStartDate(rs.getString("START_DATE")); if (!(rs.getString("START_DATE") == null || rs.getString("START_DATE") == "")) { Date date = df.parse(objReportsDto.getStartDate()); objReportsDto.setStartDate((Utility.showDate_Report(date)).toUpperCase()); } objReportsDto.setChargeAnnotation(rs.getString("ANNOTATION")); objReportsDto.setTokenNO(rs.getString("CSTATE_START_DETAILS_FX_TOKEN_NO"));//Token_No objReportsDto.setHardwareFlag(rs.getString("HARDWARE_FLAG")); objReportsDto.setHardwaretypeName(rs.getString("HARDWARETYPENAME")); objReportsDto.setStorename(rs.getString("STORENAME")); objReportsDto.setBillPeriod(rs.getString("BILL_PERIOD")); objReportsDto.setBusiness_serial_no(rs.getString("BUSINESS_SERIAL_NO")); //objReportsDto.setSaleType(rs.getString("SALETYPENAME"));//Type Of Sale } if (pagingSorting.isPagingToBeDone()) { recordCount = rs.getInt("FULL_REC_COUNT"); } listSearchDetails.add(objReportsDto); } pagingSorting.setRecordCount(recordCount); } catch (Exception ex) { //ex.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(ex, methodName, className, msg, logToFile, logToConsole);//nagarjuna } finally { try { DbConnection.closeResultset(rs); DbConnection.closeCallableStatement(proc); DbConnection.freeConnection(connection); } catch (Exception e) { // TODO Auto-generated catch block //e.printStackTrace(); Utility.onEx_LOG_RET_NEW_EX(e, methodName, className, msg, logToFile, logToConsole);//nagarjuna } } return listSearchDetails; }