import java.sql.ResultSet;
import java.sql.SQLException;

public class checkTblsp {
   private String errAdmin = new String();
   private String errMailbox = new String();
   private String sip = new String();
   private String sid = new String();
   private String suser = new String();
   private String spass = new String();
   private String smtpip = new String();
   private String sFrom = new String();

   public static void main(String[] args) {
      checkTblsp cts = new checkTblsp();
      cts.valid();
   }

   public checkTblsp() {
      this.errAdmin = "fuc@esquel.com";
      this.errMailbox = this.errAdmin;
      this.sFrom = "getlnx14-CheckTS@esquel.com";
      this.sip = "192.168.7.106:1521";
      this.sid = "SCM2";
      this.suser = "STATOWNER";
      this.spass = "CMIFCMJ`BRHbCMN_B(OBB>L\\HRFTFFMUENDK";
      this.smtpip = "192.168.7.172";
   }

   private String chkTS() {
      db dbMain = new db();
      db dbDest = new db();
      new String();
      new String();
      new String();
      String sqlDest = new String();
      StringBuffer nospaceHdr = new StringBuffer();
      StringBuffer nospaceDtl = new StringBuffer();
      new String();
      new String();

      String var19;
      try {
         Exception e3;
         SQLException sqle3;
         try {
            System.out.println("Check tablespace ... ");
            System.out.println("Connecting to main db ...");
            String rtn = dbMain.connect("oracle", this.sip, this.sid, this.suser, decrypt.keyDecrypt(this.spass));
            if (rtn == null) {
               ResultSet rsTmp = dbMain.query("SELECT STATEMENT FROM SQL_STMT WHERE SQL_ID = 3");
               rsTmp.next();
               String sqlAlert = rsTmp.getString(1);
               rsTmp.close();
               String sql = "SELECT DB_ID, IP_ADDR, PORT_NUM, SERVICE_NAME, LOGIN_NAME, LOGIN_PASS FROM DB_LIST WHERE ACTIVE = 'Y'";
               ResultSet rsMain = dbMain.query(sql);
               nospaceHdr.append("<P>Summary of tablespaces nearing their max size</P>");
               nospaceHdr.append("<TABLE border=1 cellpadding=2 cellspacing=1 style=\"border-color: teal; font-family: Tahoma; font-size: 10pt; margin: 5px; border-collapse:collapse; z-index: 1\">");
               nospaceHdr.append("<tr style=\"background-color: teal; color: white\">");
               nospaceHdr.append("<th>DB</th>");
               nospaceHdr.append("<th>Tablespace</th>");
               nospaceHdr.append("<th>Allocated</th>");
               nospaceHdr.append("<th>Free</th>");
               nospaceHdr.append("<th>Used</th>");
               nospaceHdr.append("<th>% Free</th>");
               nospaceHdr.append("<th>% Used</th>");
               nospaceHdr.append("</tr>");

               while(rsMain.next()) {
                  try {
                     System.out.print("Connecting to " + rsMain.getString("IP_ADDR") + ":" + rsMain.getString("PORT_NUM") + ":" + rsMain.getString("SERVICE_NAME") + " ... ");
                     rtn = dbDest.connect("oracle", rsMain.getString("IP_ADDR") + ":" + rsMain.getString("PORT_NUM"), rsMain.getString("SERVICE_NAME"), rsMain.getString("LOGIN_NAME"), decrypt.keyDecrypt(rsMain.getString("LOGIN_PASS")));
                     if (rtn != null) {
                        System.out.println("error");
                        System.out.println("\r\n\r\n" + rtn + "\r\n\r\n");
                        nospaceDtl.append("<tr>");
                        nospaceDtl.append("<td>" + rsMain.getString(2) + ":" + rsMain.getString(3) + ":" + rsMain.getString(4) + "</td>");
                        nospaceDtl.append("<td colspan=6><SPAN style=\"color: red\"><b>chkTS [dbDest.connect]: </b>" + rtn + "</SPAN>" + "</td>");
                        nospaceDtl.append("</tr>");
                     } else {
                        rsTmp = dbMain.query("select count(*) from ts_alert where tablespace_name like 'SELECT %' and db_id = " + rsMain.getString(1));
                        rsTmp.next();
                        boolean isAllTs = rsTmp.getInt(1) == 1;
                        rsTmp.close();
                        String sOrder = " ORDER BY tablespace_name";
                        System.out.print("checking ... ");
                        String sWhere;
                        if (isAllTs) {
                           rsTmp = dbMain.query("select tablespace_name, megs_free, pct_used from ts_alert where db_id = " + rsMain.getString(1));
                           rsTmp.next();
                           sWhere = " tablespace_name in (" + rsTmp.getString(1) + ") and " + (rsTmp.getString(2) == null ? "pct_used > " + rsTmp.getString(3) : "megs_free < " + rsTmp.getString(2));
                           rsTmp.close();
                           sqlDest = sqlAlert + " WHERE " + sWhere + sOrder;
                           rsTmp = dbDest.query(sqlDest);

                           while(rsTmp.next()) {
                              nospaceDtl.append("<tr>");
                              nospaceDtl.append("<td>" + rsMain.getString(2) + ":" + rsMain.getString(3) + ":" + rsMain.getString(4) + "</td>");
                              nospaceDtl.append("<td>" + rsTmp.getString(1) + "</td>");
                              nospaceDtl.append("<td align=\"right\">" + rsTmp.getString(2) + "</td>");
                              nospaceDtl.append("<td align=\"right\">" + rsTmp.getString(3) + "</td>");
                              nospaceDtl.append("<td align=\"right\">" + rsTmp.getString(4) + "</td>");
                              nospaceDtl.append("<td align=\"right\">" + rsTmp.getString(5) + "</td>");
                              nospaceDtl.append("<td align=\"right\">" + rsTmp.getString(6) + "</td>");
                              nospaceDtl.append("</tr>");
                           }

                           rsTmp.close();
                        } else {
                           ResultSet rsTmp2;
                           for(rsTmp = dbMain.query("select tablespace_name, megs_free, pct_used from ts_alert where db_id = " + rsMain.getString(1) + " order by tablespace_name"); rsTmp.next(); rsTmp2.close()) {
                              sWhere = " tablespace_name = '" + rsTmp.getString(1) + "' and " + (rsTmp.getString(2) == null ? "pct_used > " + rsTmp.getString(3) : "megs_free < " + rsTmp.getString(2));
                              sqlDest = sqlAlert + " WHERE " + sWhere;
                              rsTmp2 = dbDest.query(sqlDest);
                              if (rsTmp2.next()) {
                                 nospaceDtl.append("<tr>");
                                 nospaceDtl.append("<td>" + rsMain.getString(2) + ":" + rsMain.getString(3) + ":" + rsMain.getString(4) + "</td>");
                                 nospaceDtl.append("<td>" + rsTmp2.getString(1) + "</td>");
                                 nospaceDtl.append("<td align=\"right\">" + rsTmp2.getString(2) + "</td>");
                                 nospaceDtl.append("<td align=\"right\">" + rsTmp2.getString(3) + "</td>");
                                 nospaceDtl.append("<td align=\"right\">" + rsTmp2.getString(4) + "</td>");
                                 nospaceDtl.append("<td align=\"right\">" + rsTmp2.getString(5) + "</td>");
                                 nospaceDtl.append("<td align=\"right\">" + rsTmp2.getString(6) + "</td>");
                                 nospaceDtl.append("</tr>");
                              }
                           }

                           rsTmp.close();
                        }

                        System.out.println("done");
                     }
                  } catch (SQLException var49) {
                     sqle3 = var49;
                     System.err.println("\r\n\r\ncheckTblsp.chkTS - sqle3: " + sqle3.getMessage());
                     System.err.println("//----------------------------------->");
                     sqle3.printStackTrace();
                     System.err.println("\r\n" + sqlDest);
                     System.err.println("//<-----------------------------------");
                     dbMain.rollback();
                  } catch (Exception var50) {
                     e3 = var50;
                     System.err.println("\r\n\r\ncheckTblsp.chkTS - e3: " + e3.getMessage());
                     System.err.println("//----------------------------------->");
                     e3.printStackTrace();
                     System.err.println("//<-----------------------------------");
                     dbMain.rollback();
                  } finally {
                     try {
                        if (dbDest.getConnection() != null) {
                           dbDest.close();
                        }
                     } catch (Exception var48) {
                        Exception e4 = var48;
                        System.err.println("\r\n\r\ncheckTblsp.chkTS - e4: " + e4.getMessage());
                        System.err.println("//----------------------------------->");
                        e4.printStackTrace();
                        System.err.println("//<-----------------------------------");
                     }

                  }
               }

               rsMain.close();
               return nospaceDtl.length() == 0 ? null : nospaceHdr.toString() + nospaceDtl.toString() + "</TABLE>";
            }

            System.out.println("\r\n\r\n" + rtn + "\r\n\r\n");
            var19 = "<P style=\"color: red\"><b>chkTS [dbMain.connect]: </b>" + rtn + "</P>";
         } catch (SQLException var52) {
            sqle3 = var52;
            System.err.println("\r\n\r\ncheckTblsp.chkTS - sqle: " + sqle3.getMessage());
            System.err.println("//----------------------------------->");
            sqle3.printStackTrace();
            System.err.println("//<-----------------------------------");
            return nospaceDtl.length() == 0 ? null : nospaceHdr.toString() + nospaceDtl.toString() + "</TABLE>";
         } catch (Exception var53) {
            e3 = var53;
            System.err.println("\r\n\r\ncheckTblsp.chkTS - e: " + e3.getMessage());
            System.err.println("//----------------------------------->");
            e3.printStackTrace();
            System.err.println("//<-----------------------------------");
            return nospaceDtl.length() == 0 ? null : nospaceHdr.toString() + nospaceDtl.toString() + "</TABLE>";
         }
      } finally {
         try {
            if (dbMain.getConnection() != null) {
               dbMain.close();
            }
         } catch (Exception var47) {
            Exception e2 = var47;
            System.err.println("\r\n\r\ncheckTblsp.chkTS - e2: " + e2.getMessage());
            System.err.println("//----------------------------------->");
            e2.printStackTrace();
            System.err.println("//<-----------------------------------");
         }

      }

      return var19;
   }

   private String chkExt() {
      db dbMain = new db();
      db dbDest = new db();
      new String();
      new String();
      String sqlDest = new String();
      new String();
      StringBuffer noextendHdr = new StringBuffer();
      StringBuffer noextendDtl = new StringBuffer();

      try {
         Exception e3;
         SQLException sqle3;
         try {
            System.out.println("Check Auto-extend ... ");
            System.out.println("Connecting to main db ...");
            String rtn = dbMain.connect("oracle", this.sip, this.sid, this.suser, decrypt.keyDecrypt(this.spass));
            if (rtn != null) {
               System.out.println("\r\n\r\n" + rtn + "\r\n\r\n");
               String var15 = "<P style=\"color: red\"><b>chkExt [dbMain.connect]: </b>" + rtn + "</P>";
               return var15;
            }

            ResultSet rsTmp = dbMain.query("SELECT STATEMENT FROM SQL_STMT WHERE SQL_ID = 2");
            rsTmp.next();
            String sqlAlert = rsTmp.getString(1);
            rsTmp.close();
            String sql = "SELECT DB_ID, IP_ADDR, PORT_NUM, SERVICE_NAME, LOGIN_NAME, LOGIN_PASS FROM DB_LIST WHERE ACTIVE = 'Y'";
            ResultSet rsMain = dbMain.query(sql);
            noextendHdr.append("<P>Summary of datafiles nearing their limited size</P>");
            noextendHdr.append("<TABLE border=1 cellpadding=2 cellspacing=1 style=\"border-color: teal; font-family: Tahoma; font-size: 10pt; margin: 5px; border-collapse:collapse; z-index: 1\">");
            noextendHdr.append("<tr style=\"background-color: teal; color: white\">");
            noextendHdr.append("<th>DB</th>");
            noextendHdr.append("<th>Datafile</th>");
            noextendHdr.append("<th>Tablespace</th>");
            noextendHdr.append("<th>Allocated</th>");
            noextendHdr.append("<th>Auto-extend</th>");
            noextendHdr.append("<th>Max file size</th>");
            noextendHdr.append("<th>Free</th>");
            noextendHdr.append("<th>Increment by</th>");
            noextendHdr.append("</tr>");

            while(rsMain.next()) {
               try {
                  System.out.print("checking ... ");
                  System.out.print("Connecting to " + rsMain.getString("IP_ADDR") + ":" + rsMain.getString("PORT_NUM") + ":" + rsMain.getString("SERVICE_NAME") + " ... ");
                  rtn = dbDest.connect("oracle", rsMain.getString("IP_ADDR") + ":" + rsMain.getString("PORT_NUM"), rsMain.getString("SERVICE_NAME"), rsMain.getString("LOGIN_NAME"), decrypt.keyDecrypt(rsMain.getString("LOGIN_PASS")));
                  if (rtn != null) {
                     System.out.print("error");
                     System.out.println("\r\n\r\n" + rtn + "\r\n\r\n");
                     noextendDtl.append("<tr>");
                     noextendDtl.append("<td>" + rsMain.getString(2) + ":" + rsMain.getString(3) + ":" + rsMain.getString(4) + "</td>");
                     noextendDtl.append("<td colspan=7><SPAN style=\"color: red\"><b>chkExt [dbDest.connect]: </b>" + rtn + "</SPAN>" + "</td>");
                     noextendDtl.append("</tr>");
                  } else {
                     sqlDest = sqlAlert + " ORDER BY FILE_NAME";
                     rsTmp = dbDest.query(sqlDest);

                     while(rsTmp.next()) {
                        noextendDtl.append("<tr>");
                        noextendDtl.append("<td>" + rsMain.getString(2) + ":" + rsMain.getString(3) + ":" + rsMain.getString(4) + "</td>");
                        noextendDtl.append("<td>" + rsTmp.getString(1) + "</td>");
                        noextendDtl.append("<td>" + rsTmp.getString(2) + "</td>");
                        noextendDtl.append("<td align=\"right\">" + rsTmp.getString(3) + "</td>");
                        noextendDtl.append("<td align=\"center\">" + rsTmp.getString(4) + "</td>");
                        noextendDtl.append("<td align=\"right\">" + rsTmp.getString(5) + "</td>");
                        noextendDtl.append("<td align=\"right\">" + rsTmp.getString(6) + "</td>");
                        noextendDtl.append("<td align=\"right\">" + rsTmp.getString(7) + "</td>");
                        noextendDtl.append("</tr>");
                     }

                     rsTmp.close();
                     System.out.println("done");
                  }
               } catch (SQLException var45) {
                  sqle3 = var45;
                  System.err.println("\r\n\r\ncheckTblsp.chkExt - sqle3: " + sqle3.getMessage());
                  System.err.println("//----------------------------------->");
                  sqle3.printStackTrace();
                  System.err.println("\r\n" + sqlDest);
                  System.err.println("//<-----------------------------------");
                  dbMain.rollback();
               } catch (Exception var46) {
                  e3 = var46;
                  System.err.println("\r\n\r\ncheckTblsp.chkExt - e3: " + e3.getMessage());
                  System.err.println("//----------------------------------->");
                  e3.printStackTrace();
                  System.err.println("//<-----------------------------------");
                  dbMain.rollback();
               } finally {
                  try {
                     if (dbDest.getConnection() != null) {
                        dbDest.close();
                     }
                  } catch (Exception var44) {
                     Exception e4 = var44;
                     System.err.println("\r\n\r\ncheckTblsp.chkExt - e4: " + e4.getMessage());
                     System.err.println("//----------------------------------->");
                     e4.printStackTrace();
                     System.err.println("//<-----------------------------------");
                  }

               }
            }

            rsMain.close();
         } catch (SQLException var48) {
            sqle3 = var48;
            System.err.println("\r\n\r\ncheckTblsp.chkExt - sqle: " + sqle3.getMessage());
            System.err.println("//----------------------------------->");
            sqle3.printStackTrace();
            System.err.println("//<-----------------------------------");
         } catch (Exception var49) {
            e3 = var49;
            System.err.println("\r\n\r\ncheckTblsp.chkExt - e: " + e3.getMessage());
            System.err.println("//----------------------------------->");
            e3.printStackTrace();
            System.err.println("//<-----------------------------------");
         }
      } finally {
         try {
            if (dbMain.getConnection() != null) {
               dbMain.close();
            }
         } catch (Exception var43) {
            Exception e2 = var43;
            System.err.println("\r\n\r\ncheckTblsp.chkExt - e2: " + e2.getMessage());
            System.err.println("//----------------------------------->");
            e2.printStackTrace();
            System.err.println("//<-----------------------------------");
         }

      }

      if (noextendDtl.length() == 0) {
         return null;
      } else {
         return noextendHdr.toString() + noextendDtl.toString() + "</TABLE>";
      }
   }

   private void valid() {
      new String();
      new String();
      simpleMail mail = new simpleMail();
      db dbMail = new db();
      new String();

      try {
         String err = dbMail.connect("oracle", this.sip, this.sid, this.suser, decrypt.keyDecrypt(this.spass));
         mail.setSender(this.sFrom);
         if (err == null) {
            String content1 = this.chkTS();
            System.out.println(" ");
            String content2 = this.chkExt();
            if (content1 == null && content2 == null) {
               mail.setReceiver(this.errAdmin);
               mail.setSubject("Oracle Tablespace Alert - no exception found");
               mail.setContent("<P>All tablespaces are found without error!</P>");
               mail.setSmtpHost(this.smtpip);
               mail.send();
            } else {
               ResultSet rsMail = dbMail.query("SELECT DISTINCT EMAIL_LIST FROM DB_LIST WHERE ACTIVE = 'Y'");
               mail.setSubject("Oracle Tablespace Alert");
               mail.setContent((content1 == null ? "" : content1) + (content2 == null ? "" : content2));
               mail.setSmtpHost(this.smtpip);

               while(rsMail.next()) {
                  mail.setReceiver(rsMail.getString(1) + ";" + this.errMailbox);
                  mail.send();
               }

               rsMail.close();
            }
         } else {
            mail.setReceiver(this.errAdmin);
            mail.setSubject("Oracle Tablespace Alert - database error");
            mail.setContent("<P>192.168.7.106 connection cannot be established</P><P style=\"color: red\">" + err + "</P>");
            mail.setSmtpHost(this.smtpip);
            mail.send();
         }
      } catch (Exception var16) {
         Exception ee = var16;
         System.err.println("\r\n\r\ncheckTblsp.valid - ee: " + ee.getMessage());
         System.err.println("//----------------------------------->");
         ee.printStackTrace();
         System.err.println("//<-----------------------------------");
      } finally {
         try {
            if (dbMail.getConnection() != null) {
               dbMail.close();
            }
         } catch (Exception var15) {
            Exception ee2 = var15;
            System.err.println("\r\n\r\ncheckTblsp.valid - ee2: " + ee2.getMessage());
            System.err.println("//----------------------------------->");
            ee2.printStackTrace();
            System.err.println("//<-----------------------------------");
         }

      }

   }
}