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);
}
//
}
}