package com.ibm.nzna.shared.util;
/*-----------------------------------------------------------*/
/*   MapOverDocs0                                            */
/*                                                           */
/*   Description:                                            */
/*      Program to loop thru documents looking to survey     */
/*        and count                                          */
/*        ftp links there for new Boulder SDF site           */
/*                                                           */
/*   @Copyright  2003 IBM                                    */
/*                                                           */
/*-----------------------------------------------------------*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Specific program for particular document updates within FTP Migration project
 *   but skeleton might be reusable.
 * Namely, links to ftp.pc.ibm.com will be updated for new SDF site in Boulder.
 * This includes doc body, additional info and attachment links in docfiles
 * and multimedia tables.
 *
 * usage: java com.ibm.nzna.shared.util.MapOverDocs0
 *   (CLASSPATH needs db2java.zip & javaroot containing the MapOverDocs0.class)
 *
 * @version 1.0, 05/13/2003 
 * @author  Mike Rychener
 * 
 * HISTORY:
 * mdr 05/15/03 created to survey the actual tables, from MapOverDocs
 * mdr 05/31/03 mod to show only exceptions in docfiles/multimed. loops
 * mdr 06/09/03 mod to count special URLs too + unlike previously,
 *                OR in UC in queries
 * mdr 06/10/03 mod to display exceptions (excs) and truncate previous prints
 * 
 */

public class MapOverDocs0 extends Object {

   public static void main(String[] args) throws Exception {
			// was /pub in the next two (mdr, 31 July):
      String oldUrl = "ftp.pc.ibm.com";
      String oldUrlUC = "FTP.pc.ibm.com";
      String newUrl = "ftp.software.ibm.com/pc";

      // data source?
      Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

      // connect
      Connection docdb =
         DriverManager.getConnection("jdbc:db2:QUEST408", "quest", "k1ngarthur");

      // define statement to fetch list of documents to change
      Statement query = docdb.createStatement();

      // run query -- survey docbody fields
      ResultSet res =
         query.executeQuery(
            "select aa.docind,lndocid,substr(aa.title,1,48),body "
               + "from tigris.documents aa, tigris.docbody bb "
               + "where aa.docind = bb.docind and (  body like '%" + oldUrl
							 + "%' OR body like '%" + oldUrlUC + "%' )");

      int hits = 0;
      int ftpsUC = 0;
      int ftpp = 0;
			int specst = 0;
			int excs = 0;
      // loop thru results list, displaying where update would be . . .
      while (res.next()) {
         String bod = res.getString(4);
         System.out.println(res.getInt(1) + "=" + res.getString(2) + " :: "
            + res.getString(3)
            + "... [" + bod.length() + "]");
         hits++;
         int dubs = 0;
         int ftps = 0;
         int ftpsp2 = 0;
				 int specs = 0;
         for (int ii = 0; ii < bod.length(); ii++) {
             if (bod.charAt(ii) == ' ' && (ii+1) < bod.length() && bod.charAt(ii+1) == ' ') {
                 dubs++;
             }
             if ((bod.charAt(ii) == 'f' || bod.charAt(ii) == 'F')
                 && (ii + oldUrl.length()) < bod.length()
                 && bod.substring(ii, ii + oldUrl.length()).compareToIgnoreCase(oldUrl) == 0) {
                 ftps++;
                 if (bod.charAt(ii) == 'F') {
                     ftpsUC++;
                     System.out.println("-------------------- UPPER CASE: "
                                        + bod.substring(ii, ii + oldUrl.length()));
                 }
								 if ((ii + oldUrl.length() + 8 < bod.length())
										 && bod.substring(
												 ii + oldUrl.length(), ii + oldUrl.length() + 8
												 ).compareTo("/special") == 0) {
										 specs++;
										 specst++;
										 System.out.println("-------------------- Special: " 
												 + bod.substring(ii, ii + 48));
								 } else if (bod.indexOf(".", ii+oldUrl.length()) > 0
                     && ( bod.substring(ii, bod.indexOf(".", ii+oldUrl.length()
												   )).indexOf("private") > 0
                        || bod.substring(ii, bod.indexOf(".", ii+oldUrl.length()
												   )).indexOf("prism") > 0 )) {
										 // assuming here that file names always have a dot as a point of reference
                     ftpp++;
                     ftpsp2++;
                     System.out.println("-------------------- Private/Prism: "
                        + bod.substring(ii, bod.indexOf(".", ii+oldUrl.length())
												) + "...");
                 } else {  // exceptional match
										 excs++;
										 System.out.println("-------!------!----- Exceptional: "
                        + bod.substring(ii, bod.indexOf(".", ii+oldUrl.length())
												) + "...");
								 }
             } // end if we have an ftp link
         } // end for ii
         System.out.println(res.getInt(1) + "=" + res.getString(2) + " .. " 
                            + "ftp occurs: " + ftps + " private/prism: " + ftpsp2
                            + " specials: " + specs);
      } // end while results exist for the docbody query
      System.out.println("Found Docbody " + hits + " records in docbody(QUEST408).");
      System.out.println("Upper Case occurrences: " + ftpsUC 
					+ ", private/prism: " + ftpp + ", specials: " + specst + ".");
			if (excs > 0) System.out.println("================= Exceptions: " + excs + "...!...");

      // run query -- survey addinfo column
      res = query.executeQuery(
            "select aa.docind,lndocid,substr(aa.title,1,48),addinfo "
               + "from tigris.documents aa "
               + "where aa.addinfo like '%" + oldUrl
							 + "%' OR aa.addinfo like '%" + oldUrlUC + "%'");

      hits = 0;
      ftpsUC = 0;
      ftpp = 0;
			specst = 0;
			excs = 0;
      // loop thru results list, displaying where update would be . . .
      while (res.next()) {
         String bod = res.getString(4);
         System.out.println(res.getInt(1) + "=" + res.getString(2) + " :: "
            + res.getString(3)
            + "... [" + bod.length() + "]");
         hits++;
         int dubs = 0;
         int ftps = 0;
         int ftpsp2 = 0;
				 int specs = 0;
         for (int ii = 0; ii < bod.length(); ii++) {
             if (bod.charAt(ii) == ' ' && (ii+1) < bod.length() && bod.charAt(ii+1) == ' ') {
                 dubs++;
             }
             if ((bod.charAt(ii) == 'f' || bod.charAt(ii) == 'F')
                 && (ii + oldUrl.length()) < bod.length()
                 && bod.substring(ii, ii + oldUrl.length()).compareToIgnoreCase(oldUrl) == 0) {
                 ftps++;
                 if (bod.charAt(ii) == 'F') {
                     ftpsUC++;
                     System.out.println("-------------------- UPPER CASE: "
                                   + bod.substring(ii, ii+oldUrl.length()));
                 }
								 if ((ii + newUrl.length() + 8 < bod.length())
										 && bod.substring(
												 ii + newUrl.length(), ii + newUrl.length() + 8
												 ).compareTo("/special") == 0) {
										 specs++;
										 specst++;
										 System.out.println("-------------------- Special: " 
												 + bod.substring(ii, ii + 48));
								 } else if (bod.indexOf(".", ii+oldUrl.length()) > 0
                     && ( bod.substring(ii, bod.indexOf(".", ii+oldUrl.length())
													 ).indexOf("private") > 0
                       || bod.substring(ii, bod.indexOf(".", ii+oldUrl.length())
													 ).indexOf("prism") > 0 )) {
                     ftpp++;
                     ftpsp2++;
                     System.out.println("-------------------- Private/Prism: "
												+ bod.substring(ii, bod.indexOf(".", ii+oldUrl.length())
												) + "...");
                 } else {  // exceptional match
										 excs++;
										 System.out.println("-------!------!----- Exceptional: "
                        + bod.substring(ii, bod.indexOf(".", ii+oldUrl.length())
												) + "...");
								 }
             } // end if we have an ftp link
         } // end for ii
         System.out.println(res.getInt(1) + "=" + res.getString(2) + " .. " 
                            + "ftp occurs: " + ftps + " private/prism: " + ftpsp2
                            + " specials: " + specs);
      } // end while results exist for the addinfo query
      System.out.println("Found Addinfo " + hits + " records in documents(QUEST408).");
      System.out.println("Upper Case occurrences: " + ftpsUC + ", private/prism: "
					+ ftpp + ", specials: " + specst + ".");
			if (excs > 0) System.out.println("================= Exceptions: " + excs + "...!...");

      // survey docfiles URL column -- total rows + selected ones
      res = query.executeQuery("select count(docind) from tigris.docfiles");
      while (res.next()) {
          System.out.println("Docfiles table has " + res.getInt(1) + " rows.");
      }
      res = query.executeQuery(
            "select docind,substr(filename,1,10),url from tigris.docfiles "
               + "where url like '%" + oldUrl
							 + "%' OR url like '%" + oldUrlUC + "%'");

      hits = 0;
      ftpsUC = 0;
      ftpp = 0;
			specst = 0;
			excs = 0;
		  boolean seen = false;
      // loop thru results list, displaying where update would be . . .
      while (res.next()) {
         String bod = res.getString(3);
         hits++;
         seen = false;
         if (bod.indexOf("FTP") > 0) {
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
             ftpsUC++;
             System.out.println("-------------------- UPPER CASE");
         }
				 if (bod.indexOf("special") > 0) {
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
						 specst++;
             System.out.println("-------------------- special");
				 }
         else if (bod.indexOf("private") > 0 || bod.indexOf("prism") > 0) {
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
             ftpp++;
             System.out.println("-------------------- Private/Prism");
         } else { excs++;
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
             System.out.println("-------!------!----- Exception ! ! !");
				 }
      } // end while results exist for the docfiles query
      System.out.println("Found Docfiles " + hits + " records in docfiles(QUEST408).");
      System.out.println("Upper Case occurrences: " + ftpsUC + ", private/prism: " 
					+ ftpp + ", specials: " + specst + ".");
			if (excs > 0) System.out.println("================= Exceptions: " + excs + "...!...");

      // survey multimedia FILEURL column -- total rows + selected ones
      res = query.executeQuery("select count(multimediaind) from tigris.multimedia");
      while (res.next()) {
          System.out.println("Multimedia table has " + res.getInt(1) + " rows.");
      }
      res = query.executeQuery(
            "select multimediaind,substr(filename,1,10),fileurl from tigris.multimedia "
               + "where fileurl like '%" + oldUrl
							 + "%' OR fileurl like '%" + oldUrlUC + "%'");

      hits = 0;
      ftpsUC = 0;
      ftpp = 0;
			specst = 0;
			excs = 0;
      // loop thru results list, displaying where update would be . . .
      while (res.next()) {
         String bod = res.getString(3);
         hits++;
         seen = false;
         if (bod.indexOf("FTP") > 0) {
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
             ftpsUC++;
             System.out.println("-------------------- UPPER CASE");
         }
				 if (bod.indexOf("special") > 0) {
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
						 specst++;
             System.out.println("-------------------- special");
				 } else if (bod.indexOf("private") > 0 || bod.indexOf("prism") > 0) {
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
             ftpp++;
             System.out.println("-------------------- Private/Prism");
         } else { excs++;
            if (!seen)
               System.out.println(
                 res.getInt(1) + "=" + res.getString(2) + " :: " + bod);
             seen = true;
             System.out.println("-------!-----!------ Exception ! ! !");
				 }
      } // end while results exist for the multimedia query
      System.out.println("Found Multimedia " + hits + " records in multimedia(QUEST408).");
      System.out.println("Upper Case occurrences: " + ftpsUC + ", private/prism: " 
					+ ftpp + ", specials: " + specst + ".");
			if (excs > 0) System.out.println("================= Exceptions: " + excs + "...!...");

   } // end main(args)

} /* class MapOverDocs0 */