Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package com.enzen.cis.dao;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.Calendar;
- import java.util.Date;
- import com.enzen.cis.bean.Consumer;
- import com.enzen.cis.bean.DomainTypeBean;
- public class DAOConsumerAddEdit {
- String strdateVar,strdateVar1;
- int nCurrentUser=1;
- Date dtVar;
- SimpleDateFormat df2=new SimpleDateFormat("dd-MMM-yyyy");
- Date dtd2;
- Calendar dtd4=Calendar.getInstance();
- SimpleDateFormat formatter=
- new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
- java.sql.Date sqlDate;
- private ArrayList<DomainTypeBean> dtList=new ArrayList<DomainTypeBean>();
- public void setDtList(ArrayList<DomainTypeBean> dtList) {
- this.dtList = dtList;
- }
- public ArrayList<DomainTypeBean> getDtList() {
- try{
- Connection con= DBConnector.getConnection();
- if (con != null){
- String strQuery1=("select domain.description,domain.objectid from domain inner join domaintype on (domain.domaintype_oid=domaintype.objectid) where domaintype.code='STATUS'");
- Statement st = con.createStatement();
- ResultSet rs=st.executeQuery(strQuery1);
- System.out.println("Before while loop");
- while (rs.next()){
- DomainTypeBean bean=new DomainTypeBean();
- bean.setDescription(rs.getString("description"));
- bean.setObjid(rs.getInt("objectid"));
- dtList.add(bean);
- }
- System.out.println(dtList);
- }
- }
- catch(Exception e){
- e.printStackTrace();
- }
- return dtList;
- }
- public int InsertionDB (Consumer bean) throws Exception{
- Connection con = null;
- PreparedStatement stmt = null;
- PreparedStatement stmt1 = null;
- PreparedStatement stmt2 = null;
- ResultSet rs = null;
- int nconsumer_id=0;
- try {
- con= DBConnector.getConnection();
- con.setAutoCommit(false);
- String query= "INSERT INTO DB_CONSUMER (WENEXA_ID,RR_NO,CONSUMER_NAME,RESIDING_VILLAGE,CONTACT_NO, CONTACT_PERSON,REP_DATE,STATUS_ID) VALUES (?,?,?,?,?,?,?,?)";
- stmt = con.prepareStatement(query);
- stmt.setString(1, bean.getWenexa_id());
- stmt.setString(2, bean.getRr_number());
- stmt.setString(3, bean.getConsumer_name());
- stmt.setString(4, bean.getResiding_village());
- stmt.setString(5,bean.getContact_no());
- stmt.setString(6,bean.getContact_person());
- if(bean.getRep_date()==null||bean.getRep_date()==""){
- bean.setRep_date(null);
- }
- if(bean.getRep_date()!=null){
- System.out.println("DAte before Insert"+bean.getRep_date());
- dtd2= df2.parse(bean.getRep_date());
- sqlDate = new java.sql.Date(dtd2.getTime());
- }
- stmt.setDate(7, (java.sql.Date) sqlDate);
- stmt.setInt(8,bean.getStatus());
- stmt.executeUpdate();
- String query2="select max(consumer_id) as consumer_id from db_consumer";
- stmt2 = con.prepareStatement(query2);
- rs= stmt2.executeQuery();
- while (rs.next()){
- nconsumer_id=(rs.getInt("consumer_id"));
- }
- bean.setConsumer_id(nconsumer_id);
- String query1="INSERT INTO DB_CONSUMER_HISTORY (WENEXA_ID,RR_NO,CONSUMER_NAME,RESIDING_VILLAGE,CONTACT_NO, CONTACT_PERSON,REP_DATE,STATUS_ID,MODIFIED_BY,MODIFIED_DATE,CONSUMER_ID) VALUES (?,?,?,?,?,?,?,?,?,now(),?)";
- stmt1 = con.prepareStatement(query1);
- stmt1.setString(1, bean.getWenexa_id());
- stmt1.setString(2, bean.getRr_number());
- stmt1.setString(3, bean.getConsumer_name());
- stmt1.setString(4, bean.getResiding_village());
- stmt1.setString(5,bean.getContact_no());
- stmt1.setString(6,bean.getContact_person());
- if(bean.getRep_date()==null||bean.getRep_date()==""){
- bean.setRep_date(null);
- }
- if(bean.getRep_date()!=null){
- System.out.println("DAte before Insert"+bean.getRep_date());
- dtd2= df2.parse(bean.getRep_date());
- sqlDate = new java.sql.Date(dtd2.getTime());
- }
- stmt1.setDate(7, (java.sql.Date) sqlDate);
- stmt1.setInt(8,bean.getStatus());
- stmt1.setInt(9,nCurrentUser);
- stmt1.setInt(10,bean.getConsumer_id());
- stmt1.executeUpdate();
- con.commit();
- }
- catch (Exception sqe) {
- sqe.printStackTrace();
- try {
- //An error occurred so we roll back the changes.
- con.rollback();
- } catch (SQLException ex1) {
- ex1.printStackTrace();
- }
- } finally {
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- return nconsumer_id;
- }
- public Consumer obtainConsumerData(int consumer_id) throws Exception {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- Consumer bean =new Consumer();
- try {
- con= DBConnector.getConnection();
- if (con != null){
- String query="SELECT CONSUMER_ID,WENEXA_ID,RR_NO,CONSUMER_NAME,RESIDING_VILLAGE,CONTACT_NO,CONTACT_PERSON, to_char(REP_DATE, 'DD-Mon-YYYY') REP_DATE ,STATUS_ID FROM db_consumer WHERE CONSUMER_ID="+consumer_id ;
- stmt = con.prepareStatement(query);
- rs= stmt.executeQuery();
- while (rs.next()){
- bean.setConsumer_id(rs.getInt("CONSUMER_ID"));
- bean.setWenexa_id(rs.getString("WENEXA_ID"));
- bean.setRr_number(rs.getString("RR_NO"));
- bean.setConsumer_name(rs.getString("CONSUMER_NAME"));
- bean.setResiding_village(rs.getString("RESIDING_VILLAGE"));
- bean.setContact_no(rs.getString("CONTACT_NO"));
- bean.setContact_person(rs.getString("CONTACT_PERSON"));
- bean.setRep_date(rs.getString("REP_DATE"));// Chances of conflict
- System.out.println("Before formatting"+strdateVar);
- /*
- * Coding logic
- */
- bean.setStatus(rs.getInt("STATUS_ID"));
- System.out.println("Retreived Data"+bean.getConsumer_id()+bean.getConsumer_name()+bean.getStatus());
- }
- }
- } catch (SQLException sqe) {
- sqe.printStackTrace();
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- return bean;
- }
- public void updateDB(Consumer bean) throws Exception {
- String query;
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- PreparedStatement stmt1 = null;
- System.out.println(bean.getConsumer_id());
- System.out.println(bean.getConsumer_name());
- try {
- con= DBConnector.getConnection();
- if (con != null){
- con= DBConnector.getConnection();
- con.setAutoCommit(false);
- System.out.println("Connected");
- if(bean.getRep_date()==null||bean.getRep_date()==""){
- bean.setRep_date(null);
- }
- /*
- * The Reason for two queries- To allow value of repdate to be passed without quotes
- * if it is NULL.
- */
- if(bean.getRep_date()!=null){
- query="UPDATE db_consumer SET WENEXA_ID='"+bean.getWenexa_id()+"',RR_NO='"+bean.getRr_number()+"',CONSUMER_NAME='"+bean.getConsumer_name()+"',RESIDING_VILLAGE='"+bean.getResiding_village()+"',CONTACT_NO='"+bean.getContact_no()+"',CONTACT_PERSON='"+bean.getContact_person()+"',REP_DATE='"+bean.getRep_date()+"',STATUS_ID='"+bean.getStatus()+"' WHERE CONSUMER_ID="+bean.getConsumer_id()+"";
- }
- else{
- query="UPDATE db_consumer SET WENEXA_ID='"+bean.getWenexa_id()+"',RR_NO='"+bean.getRr_number()+"',CONSUMER_NAME='"+bean.getConsumer_name()+"',RESIDING_VILLAGE='"+bean.getResiding_village()+"',CONTACT_NO='"+bean.getContact_no()+"',CONTACT_PERSON='"+bean.getContact_person()+"',REP_DATE="+bean.getRep_date()+",STATUS_ID="+bean.getStatus()+" WHERE CONSUMER_ID="+bean.getConsumer_id()+"";
- }
- System.out.println(query);
- stmt = con.prepareStatement(query);
- stmt.executeUpdate();
- String query1="INSERT INTO DB_CONSUMER_HISTORY (WENEXA_ID,RR_NO,CONSUMER_NAME,RESIDING_VILLAGE,CONTACT_NO, CONTACT_PERSON,REP_DATE,STATUS_ID,MODIFIED_BY,MODIFIED_DATE,CONSUMER_ID) VALUES (?,?,?,?,?,?,?,?,?,now(),?)";
- stmt1 = con.prepareStatement(query1);
- stmt1.setString(1, bean.getWenexa_id());
- stmt1.setString(2, bean.getRr_number());
- stmt1.setString(3, bean.getConsumer_name());
- stmt1.setString(4, bean.getResiding_village());
- stmt1.setString(5,bean.getContact_no());
- stmt1.setString(6,bean.getContact_person());
- if(bean.getRep_date()==null||bean.getRep_date()==""){
- bean.setRep_date(null);
- }
- if(bean.getRep_date()!=null){
- System.out.println("DAte before Insert"+bean.getRep_date());
- dtd2= df2.parse(bean.getRep_date());
- sqlDate = new java.sql.Date(dtd2.getTime());
- }
- stmt1.setDate(7, (java.sql.Date) sqlDate);
- stmt1.setInt(8,bean.getStatus());
- stmt1.setInt(9,nCurrentUser);
- stmt1.setInt(10,bean.getConsumer_id());
- stmt1.executeUpdate();
- con.commit();
- }
- }
- catch (SQLException sqe) {
- sqe.printStackTrace();
- try {
- //An error occurred so we roll back the changes.
- con.rollback();
- } catch (SQLException ex1) {
- ex1.printStackTrace();
- }
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- }
- public int getInsertedConsumerID() throws Exception{
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int consumer_id=0;
- try {
- con= DBConnector.getConnection();
- if (con != null){
- String query="select max(consumer_id) as consumer_id from db_consumer";
- // String query1="select nextval('consumer_id_sequence') from db_consumer LIMIT 1";
- stmt = con.prepareStatement(query);
- rs= stmt.executeQuery();
- while (rs.next()){
- consumer_id=(rs.getInt("consumer_id"));
- }
- }
- }
- catch (SQLException sqe) {
- sqe.printStackTrace();
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- return consumer_id;
- }
- /*
- * In progress.
- */
- public ArrayList<Consumer> getConsumerList(int consumer_id) throws Exception {
- ArrayList<Consumer> resultList=new ArrayList<Consumer>();
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- Consumer bean;
- try {
- con= DBConnector.getConnection();
- if (con != null){
- String query="SELECT CONSUMER_ID,WENEXA_ID,RR_NO,CONSUMER_NAME,RESIDING_VILLAGE,CONTACT_NO,CONTACT_PERSON, to_char(REP_DATE, 'DD-Mon-YYYY') REP_DATE ,coalesce(DB_EMPLOYEE.FIRST_NAME,' ')||coalesce(DB_EMPLOYEE.MIDDLE_NAME,' ')||coalesce(DB_EMPLOYEE.LAST_NAME,' ') MODIFIED_BY,to_char(MODIFIED_DATE, 'DD-Mon-YYYY HH MI AM')MODIFIED_DATE,(DOMAIN.DESCRIPTION) AS STATUS FROM db_consumer_history INNER JOIN domain on DOMAIN.OBJECTID=DB_CONSUMER_HISTORY.STATUS_ID INNER JOIN db_employee on DB_EMPLOYEE.EMP_ID=DB_CONSUMER_HISTORY.MODIFIED_BY WHERE CONSUMER_ID="+consumer_id+" ORDER BY MODIFIED_DATE DESC";
- stmt = con.prepareStatement(query);
- rs= stmt.executeQuery();
- while(rs.next()){
- bean =new Consumer();
- bean.setConsumer_id(rs.getInt("CONSUMER_ID"));
- bean.setWenexa_id(rs.getString("WENEXA_ID"));
- bean.setRr_number(rs.getString("RR_NO"));
- bean.setConsumer_name(rs.getString("CONSUMER_NAME"));
- bean.setResiding_village(rs.getString("RESIDING_VILLAGE"));
- bean.setContact_no(rs.getString("CONTACT_NO"));
- bean.setContact_person(rs.getString("CONTACT_PERSON"));
- bean.setRep_date(rs.getString("REP_DATE"));// Chances of conflict
- bean.setStrStatus(rs.getString("STATUS"));
- bean.setDtmodifiedDate(rs.getString("MODIFIED_DATE"));
- bean.setStrmodifiedBy(rs.getString("MODIFIED_BY"));
- resultList.add(bean);
- }
- for (Consumer c:resultList){
- System.out.println(c.getConsumer_name());
- System.out.println(c.getContact_no());
- }
- }
- }
- catch (SQLException sqe) {
- sqe.printStackTrace();
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- return resultList;
- }
- public ArrayList<Consumer> getlistedMeterDetails(int consumer_id) throws Exception {
- ArrayList<Consumer> resultList=new ArrayList<Consumer>();
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- int serial;
- Consumer bean;
- try {
- con= DBConnector.getConnection();
- if (con != null){
- String query="SELECT db_consumer_meter_details.consumer_id as CONSUMER_ID,db_consumer_meter_details.meter_id AS METER_ID, db_meter.meter_serial AS METER_SERIAL,DB_POLE.POLE_NUMBER AS POLE_NUMBER,db_consumer_meter_details.ACTIVE AS ACTIVE from db_consumer_meter_details INNER JOIN db_meter on DB_METER.METER_ID=db_consumer_meter_details.METER_ID INNER JOIN DB_POLE on DB_POLE.POLE_ID=db_consumer_meter_details.POLE_ID WHERE CONSUMER_ID= "+consumer_id ;
- stmt = con.prepareStatement(query);
- System.out.println(query);
- rs= stmt.executeQuery();
- while(rs.next()){
- bean =new Consumer();
- serial=rs.getRow();
- bean.setSerial(serial);
- bean.setConsumer_id(rs.getInt("CONSUMER_ID"));
- bean.setNmeterID(rs.getInt("METER_ID"));
- bean.setStrmeterSerial(rs.getString("METER_SERIAL"));
- bean.setStrpoleNumber(rs.getString("POLE_NUMBER"));
- bean.setBlactive(rs.getBoolean("ACTIVE"));
- if(bean.getBlactive()==true){
- bean.setStrstatusActive("Yes");
- }
- else if(bean.getBlactive()==false){
- bean.setStrstatusActive("No");
- }
- resultList.add(bean);
- }
- }
- }
- catch (SQLException sqe) {
- sqe.printStackTrace();
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- for (Consumer c:resultList){
- System.out.println(c.getNmeterID());
- }
- return resultList;
- }
- public void insertMeterDetails (Consumer bean) throws Exception{
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- con= DBConnector.getConnection();
- if (con != null){
- String query= "INSERT INTO DB_CONSUMER_METER_DETAILS (CONSUMER_ID,METER_ID,POLE_ID,CREATED_DATE,CREATED_BY,REMARKS,ACTIVE)VALUES(?,?,?,now(),?,?,?) ";
- System.out.println(query);
- stmt = con.prepareStatement(query);
- stmt.setInt(1,bean.getConsumer_id());
- stmt.setInt(2,bean.getNmeterID());
- stmt.setInt(3,bean.getNpoleID());
- stmt.setInt(4,bean.getNcreated_by());
- stmt.setString(5,bean.getStrRemarks());
- stmt.setBoolean(6, bean.getBlactive());
- stmt.executeUpdate();
- }
- }
- catch (Exception sqe) {
- sqe.printStackTrace();
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- }
- public Consumer obtainMeterData(int nmeter_id) throws Exception {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- Consumer bean =new Consumer();
- try {
- con= DBConnector.getConnection();
- if (con != null){
- String query="SELECT DB_CONSUMER.CONSUMER_NAME,DB_CONSUMER_METER_DETAILS.POLE_ID AS POLE_ID,DB_CONSUMER_METER_DETAILS.CONSUMER_ID AS CONSUMER_ID, DB_METER.METER_SERIAL AS METER_SERIAL,DB_POLE.POLE_NUMBER AS POLE_NUMBER,DB_CONSUMER_METER_DETAILS.ACTIVE AS ACTIVE,DB_CONSUMER_METER_DETAILS.REMARKS AS REMARKS FROM DB_CONSUMER_METER_DETAILS INNER JOIN DB_CONSUMER ON DB_CONSUMER.CONSUMER_ID=DB_CONSUMER_METER_DETAILS.CONSUMER_ID INNER JOIN DB_METER ON DB_METER.METER_ID=DB_CONSUMER_METER_DETAILS.METER_ID INNER JOIN DB_POLE ON DB_POLE.POLE_ID=DB_CONSUMER_METER_DETAILS.POLE_ID WHERE DB_CONSUMER_METER_DETAILS.METER_ID= "+nmeter_id;
- stmt = con.prepareStatement(query);
- rs= stmt.executeQuery();
- while (rs.next()){
- bean.setConsumer_name(rs.getString("CONSUMER_NAME"));
- bean.setConsumer_id(rs.getInt("CONSUMER_ID"));
- bean.setStrmeterSerial(rs.getString("METER_SERIAL"));
- bean.setStrpoleNumber(rs.getString("POLE_NUMBER"));
- bean.setBlactive(rs.getBoolean("ACTIVE"));
- bean.setStrRemarks(rs.getString("REMARKS"));
- bean.setNpoleID(rs.getInt("POLE_ID"));
- if(bean.getBlactive()==true){
- bean.setStrstatusActive("Yes");
- }
- else{
- bean.setStrstatusActive("No");
- }
- }
- }
- }catch (SQLException sqe) {
- sqe.printStackTrace();
- } finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- return bean;
- }
- public void updatemeterDetails(Consumer bean,int hdnmeterid) throws Exception {
- Connection con = null;
- PreparedStatement stmt = null;
- ResultSet rs = null;
- try {
- con= DBConnector.getConnection();
- if (con != null){
- con= DBConnector.getConnection();
- String query="UPDATE DB_CONSUMER_METER_DETAILS SET CONSUMER_ID = "+bean.getConsumer_id()+ " ,METER_ID = "+bean.getNmeterID()+",POLE_ID ="+bean.getNpoleID()+ " ,ACTIVE = "+bean.getBlactive()+ ",REMARKS = '" +bean.getStrRemarks()+ "' WHERE METER_ID = "+hdnmeterid;
- System.out.println(query);
- stmt = con.prepareStatement(query);
- stmt.executeUpdate();
- }
- }
- catch (SQLException sqe) {
- sqe.printStackTrace();
- }finally {
- DBConnector.closeResultSet(rs);
- DBConnector.closeStatement(stmt);
- DBConnector.closeConnection(con);
- }
- //
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement