1 /*
  2  * To change this template, choose Tools | Templates
  3  * and open the template in the editor.
  4  */
  5 
  6 package db_adapter;
  7 
  8 import java.io.*;
  9 import java.sql.Connection;
 10 import java.sql.DriverManager;
 11 import java.sql.PreparedStatement;
 12 import java.sql.ResultSet;
 13 import java.sql.SQLException;
 14 import java.util.ArrayList;
 15 import java.util.Hashtable;
 16 import java.util.MissingResourceException;
 17 import java.util.ResourceBundle;
 18 import java.util.Calendar;
 19 import java.util.Date;
 20 import java.text.SimpleDateFormat;
 21 //import org.apache.log4j.Logger;
 22 //import org.apache.log4j.PropertyConfigurator;
 23 /**
 24  *
 25  * @author sameldin
 26  */
 27 import utils.*;
 28 import constants.*;
 29 
 30 public class Count_Select
 31 {
 32     Connection  localConnection = null;
 33 
 34     public Count_Select()
 35     {
 36         BD_Connector localBD_Connector = new BD_Connector();
 37         localConnection = localBD_Connector.getConnection();
 38     }
 39     /*
 40      * 
 41      */
 42      public int countMaleFemalTolalColumn(String passedTableName)
 43      {
 44         if(null == localConnection)
 45             return(Constants.ERROR_RUN);
 46         PreparedStatement   localPreparedStatement      = null;
 47         //==========================
 48         try
 49         {
 50             //   select count(*) as total,
 51             //  count(gender where gender = 'male') as male, 
 52             //  count(gender where gender = 'female') as female 
 53             //from passedTableName
 54                 String tableName_1        = DatabaseKeysPropertyManager.getParameter(passedTableName);
 55                 String qryString = " SELECT count(*) as total,"
 56                                 + "  count(gender where gender = 'male') as male, "
 57                                 + "  count(gender where gender = 'female') as female "
 58                                 + "  from "
 59                                 + tableName_1;
 60 
 61                 //==========================
 62                 localPreparedStatement = localConnection.prepareStatement(qryString);
 63                 localPreparedStatement.executeQuery();
 64                 localPreparedStatement.close();
 65          }
 66         catch(MissingResourceException eMissingResourceException)
 67         {
 68             eMissingResourceException.printStackTrace();
 69             return(Constants.ERROR_RUN);
 70         }
 71         catch(SQLException eSQLException)
 72         {
 73             eSQLException.printStackTrace();
 74             return(Constants.ERROR_RUN);
 75         }
 76         return(Constants.NORMAL_RUN);
 77     }
 78     /*
 79      *
 80      */
 81      public int countMaleFemalTolalColumn_2(String passedTableName)
 82      {
 83         if(null == localConnection)
 84             return(Constants.ERROR_RUN);
 85         PreparedStatement   localPreparedStatement      = null;
 86         //==========================
 87         try
 88         {
 89                 //select
 90                 //  max(registeredYear) as year,
 91                 //  count(case when gender='Male' then 1 end) as male_cnt,
 92                 //  count(case when gender='Female' then 1 end) as female cnt,
 93                 //  count(*) as total_cnt
 94                 //from tableName_1
 95                 //where registeredYear = 2013
 96                 //group by registeredYear;
 97 
 98                 String tableName_1        = DatabaseKeysPropertyManager.getParameter(passedTableName);
 99                 String qryString = " SELECT max(registeredYear) as year,"
100                                 + "  count(case when gender='Male' then 1 end) as male_cnt, "
101                                 + "  count(case when gender='Female' then 1 end) as female cnt, "
102                                 + "  count(*) as total_cnt "
103                                 + "  from "
104                                 + tableName_1
105                                 + "  where registeredYear = 2013 "
106                                 + "group by registeredYear;";
107                 //==========================
108                 localPreparedStatement = localConnection.prepareStatement(qryString);
109                 localPreparedStatement.executeQuery();
110                 localPreparedStatement.close();
111          }
112         catch(MissingResourceException eMissingResourceException)
113         {
114             eMissingResourceException.printStackTrace();
115             return(Constants.ERROR_RUN);
116         }
117         catch(SQLException eSQLException)
118         {
119             eSQLException.printStackTrace();
120             return(Constants.ERROR_RUN);
121         }
122         return(Constants.NORMAL_RUN);
123     }
124 }
125 
126