
#!/bin/bash
##### {COPYRIGHT-TOP} #####
# Licensed Materials - Property of IBM
# 5724-I68
#
# (C) Copyright IBM Corp. 2000, 2006
#
# US Government Users Restricted Rights - Use, duplication, or
# disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
##### {COPYRIGHT-END} #####
## %DISCLAIMER%

# The purpose of this script is to migrate the database schema used by WPC from any FP of 5.3.2 to 5.3.2 Current FP installed
# Please consult WPC support before attempting to use this script



. ${TOP}/src/db/schema/util/set_db_localized.sh
_ERROR_LOGS=$TOP/logs/errfile.log

EXISTS=1;
NOTEXISTS=0;
count=0;

if [ -d $TOP/tmp ]; then
  TMPDIREXISTS=$EXISTS
else
  TMPDIREXISTS=$NOTEXISTS
fi

getErrorCountInLogFile()
{
    if [ ! -f $_ERROR_LOGS ]; then
        errorCount=0
    else
        errorCount=`cat $_ERROR_LOGS | wc -c`
    fi
}

DoStartAndPrepare()
{
    ${ECHO} "-----------------------------------------" 
    ${ECHO} $1
    ${ECHO} "-----------------------------------------" 

    getErrorCountInLogFile
    initial=$errorCount
}

DoEndAndReportError()
{

    getErrorCountInLogFile
    final=$errorCount

    if [ ! $initial -eq $final ]; then

        ${ECHO} "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
        ${ECHO} $1
        ${ECHO} "!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
        ${ECHO} "MIGRATION_INFO_CHECK_ERROR_LOG_FILE_SH" $_ERROR_LOGS
        
    fi
    	${ECHO} ""
        ${ECHO} "-----------------------------------------"
        ${ECHO} $2
        ${ECHO} "-----------------------------------------"

}

#
# Define the various migration functions. New functions can be added in this section.
#

alterCategoryItemAttributes() 
{
		DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH Category Item Attributes"		
			. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/alter_occurrence_id-oracle.sql 2>> $_ERROR_LOGS			
		DoEndAndReportError  "MIGRATION_INFO_FAILED_SH Category Item Attributes" "MIGRATION_INFO_SUCCEEDED_SH Category Item Attributes"
		${ECHO} "describe ita;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "describe cab;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
}


addDateColumns()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH Profiles"     	
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/add_date_column.sql 2>> $_ERROR_LOGS
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH Profiles" "MIGRATION_INFO_SUCCEEDED_SH Profiles"
	if [ ${CCD_DB} = "oracle" ]; then
		${ECHO} "describe ppi;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "describe ppr;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "describe psi;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "describe psd;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	else
		${ECHO} "select COLNAME from syscat.columns where TABNAME = 'TPFM_PPI_PROFILE_INFO' AND TABSCHEMA = CURRENT SCHEMA;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select COLNAME from syscat.columns where TABNAME = 'TPFM_PPR_PROFILE' AND TABSCHEMA = CURRENT SCHEMA;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select COLNAME from syscat.columns where TABNAME = 'TPFM_PSI_PROFILE_SCHEDULE_INFO' AND TABSCHEMA = CURRENT SCHEMA;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select COLNAME from syscat.columns where TABNAME = 'TPFM_PSD_SCHEDULE_DETAIL' AND TABSCHEMA = CURRENT SCHEMA;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	fi
}

updateSequences()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH Sequences"     	
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/update_seq_table.sql 2>> $_ERROR_LOGS	
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH Sequences" "MIGRATION_INFO_SUCCEEDED_SH Sequences"
}


updateLCKIndex()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH LCK_Index"     	
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/tutl_lck_lock_indexes.sql 2>> $_ERROR_LOGS
		if [ ${CCD_DB} = "oracle" ]; then
			${ECHO} "column column_name format a30;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
			${ECHO} "select index_name, column_name, column_position from user_ind_columns where table_name= 'TUTL_LCK_LOCK' order by 1, 3;" >> ${DB_SCRIPTS}/verifyFPMigration.sql			
			${ECHO} "select UNIQUENESS from user_indexes where index_name='IUTL_LCK_0';" >> ${DB_SCRIPTS}/verifyFPMigration.sql	
		else
			${ECHO} "select indname, colname, colseq from syscat.indexcoluse where indschema= CURRENT SCHEMA and indname like '%LCK%'order by indname, colseq;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
			${ECHO} "select UNIQUERULE from syscat.indexes where indschema= CURRENT SCHEMA and indname='IUTL_LCK_0';" >> ${DB_SCRIPTS}/verifyFPMigration.sql
			${ECHO} "select volatile from syscat.tables where tabschema= CURRENT SCHEMA and tabname='TUTL_LCK_LOCK';" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		fi
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH LCK_Index" "MIGRATION_INFO_SUCCEEDED_SH LCK_Index"
}


updateWFE_NOMIndex()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH WFE_NOM_Index"
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/create_wfe_index.sql 2>> $_ERROR_LOGS
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/tctg_nom_node_mapping_index.sql 2>> $_ERROR_LOGS
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH WFE_NOM_Index" "MIGRATION_INFO_SUCCEEDED_SH WFE_NOM_Index"
	if [ ${CCD_DB} = "oracle" ]; then
		${ECHO} "column column_name format a30;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select index_name, column_name, column_position from user_ind_columns where table_name= 'TWFL_WFE_WORKFLOW_EVENT' order by 1, 3;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select index_name, column_name, column_position from user_ind_columns where table_name= 'TCTG_NOM_NODE_MAPPING' order by 1, 3;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	else
		${ECHO} "select indname, colname, colseq from syscat.indexcoluse where indschema= CURRENT SCHEMA and indname like '%WFE%'order by indname, colseq;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select indname, colname, colseq from syscat.indexcoluse where indschema= CURRENT SCHEMA and indname like '%NOM%'order by indname, colseq;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	fi
}


createIndexExplainObject()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH ExplainObject_Index"
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/create_index_explain_object.sql 2>> $_ERROR_LOGS
		${ECHO} "select indname, colname, colseq from syscat.indexcoluse where indschema= CURRENT SCHEMA and indname like '%EXP_OBJ_IND%' order by indname, colseq;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH ExplainObject_Index" "MIGRATION_INFO_SUCCEEDED_SH ExplainObject_Index"
}


updateITACABColumns()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH ITA_CAB_Columns"
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/alter_ita_cab_columns.sql 2>> $_ERROR_LOGS
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH ITA_CAB_Columns" "MIGRATION_INFO_SUCCEEDED_SH ITA_CAB_Columns"
	${ECHO} "describe ita;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	${ECHO} "describe cab;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
}


updateCollabAttributes()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH Collab Attributes"
		$JAVA_RT com.ibm.ccd.common.migration.FixCollaborationAttributes 2>> $_ERROR_LOGS | ${GREP} -v '.*'
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH Collab Attributes" "MIGRATION_INFO_SUCCEEDED_SH Collab Attributes"	
}


alterEntryTabbedPane()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH EntryTabbedPane"
		. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/alter_etp_table.sql --errorfilter=yes 2>> $_ERROR_LOGS | ${GREP} -v '.*'
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH EntryTabbedPane" "MIGRATION_INFO_SUCCEEDED_SH EntryTabbedPane"	

	if [ ${CCD_DB} = "oracle" ]; then
		${ECHO} "describe tctg_etp_entry_tabbed_pane;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select etp_tab_attr_order from tctg_etp_entry_tabbed_pane where etp_tab_attr_order != 'SPEC_ORDER';" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	else
		${ECHO} "select COLNAME from syscat.columns where TABNAME = 'TCTG_ETP_ENTRY_TABBED_PANE' AND TABSCHEMA = CURRENT SCHEMA;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
		${ECHO} "select etp_tab_attr_order from tctg_etp_entry_tabbed_pane where etp_tab_attr_order != 'SPEC_ORDER';" >> ${DB_SCRIPTS}/verifyFPMigration.sql
	fi
}


updateACGs()
{
	DoStartAndPrepare "MIGRATION_INFO_UPDATING_SH ACGs"
		exportACGRoles
		recreateObjectActions
		importACGRoles
		updateAdminRoles
	DoEndAndReportError  "MIGRATION_INFO_FAILED_SH ACGs" "MIGRATION_INFO_SUCCEEDED_SH ACGs"	
}

exportACGRoles() 
{
	${ECHO} "EXPORTING..."
	for CMP_CODE_COL in $COMPANIES; 
	do
		CMP_CODE=`${ECHO} ${CMP_CODE_COL} | ${GREP} 'cmpcode%' | ${CUT} -f2 -d "%"`         
        if [ "$CMP_CODE" != "" ]; then        	
			${ECHO} $CMP_CODE
			${ECHO} 
			
			$JAVA_RT com.ibm.ccd.envexpimp.common.EnvironmentExporter -companycode="$CMP_CODE" -scriptpath=$TOP/bin/exportACGRolesScript.wpcs 2>> $_ERROR_LOGS	
								
			$JAVA_RT com.ibm.ccd.docstore.util.DocDownloader -companycode="$CMP_CODE" -subtree=archives/fpmigration/ -xmlfile=$TOP/tmp/abc.xml -rootlocalpath=$TOP/tmp/ 2>> $_ERROR_LOGS | ${GREP} -v '.*'
			
			`mv $TOP/tmp/__archives__fpmigration__Export.zip $TOP/tmp/__archives__fpmigration__cmp"$count".zip `
		      	
			count=`expr $count + 1`
				
			`rm -f $TOP/tmp/abc.xml `
		fi            
	done
}

recreateObjectActions()
{
	. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_CMN_SCRIPTS/recreateOAC_OBT.sql --errorfilter=yes 2>> $_ERROR_LOGS | ${GREP} -v '.*'
	${ECHO} "select count(*) NO_OF_OAC_RECORDS from oac;" > ${DB_SCRIPTS}/verifyFPMigration.sql
	${ECHO} "select count(*) NO_OF_OBT_RECORDS from obt;" >> ${DB_SCRIPTS}/verifyFPMigration.sql
}

importACGRoles()
{
	count=0;
	${ECHO} "IMPORTING..."
	for CMP_CODE_COL in $COMPANIES; 
		do
            CMP_CODE=`${ECHO} ${CMP_CODE_COL} | ${GREP} 'cmpcode%' | ${CUT} -f2 -d "%"`         
         	if [ "$CMP_CODE" != "" ]; then
            	${ECHO} $CMP_CODE
            	${ECHO}
            	$JAVA_RT com.ibm.ccd.envexpimp.common.EnvironmentImporter -companycode="$CMP_CODE" -zipfilepath=$TOP/tmp/__archives__fpmigration__cmp"$count".zip 2>> $_ERROR_LOGS | ${GREP} -v '.*'
            	            	
		      	if [ $TMPDIREXISTS -eq $EXISTS ]; then
		     		`rm $TOP/tmp/__archives__fpmigration__cmp"$count".zip `
		   		fi		
		   		
		   		count=`expr $count + 1`
            fi            
    done
	
	if [ $TMPDIREXISTS -eq $NOTEXISTS ]; then
   		`rm -rf $TOP/tmp/ `
	fi
}

updateAdminRoles()
{	
	`rm -f $DB_SCRIPTS/updateAdminORM.sql `
	${ECHO} " " > ${DB_SCRIPTS}/updateAdminORM.sql
	for CMP_CODE_COL in $COMPANIES; 
	do
        CMP_CODE=`${ECHO} ${CMP_CODE_COL} | ${GREP} 'cmpcode%' | ${CUT} -f2 -d "%"`         
     	if [ "$CMP_CODE" != "" ]; then    		
			${ECHO} "update orm set orm_access = 'feffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff0f'
					 where orm_company_id=(select cmp_company_id from cmp where cmp_company_code='$CMP_CODE') 
						and orm_role_id=(select rol_role_id from rol 
                  						 where rol_company_id=(select cmp_company_id from cmp where cmp_company_code='$CMP_CODE') 
                    						and rol_role_name like '%_admin');" >> ${DB_SCRIPTS}/updateAdminORM.sql
     	fi            
    done  
    . ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/updateAdminORM.sql --errorfilter=yes 2>> $_ERROR_LOGS 
}


GET_COMPANIES=`. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_CMN_SCRIPTS/get_cmpcode.sql`
COMPANIES="${GET_COMPANIES}"
`rm -f $DB_SCRIPTS/verifyFPMigration.sql `


#
# Call the appropriate migration functions depending upon the source version
#

#New pages are added in FP10. So this is called for all Fixpacks prior to FP10.
updateACGs

if  [ "${FROMVERSION}" == "BASE" ] || [ "${FROMVERSION}" == "IF1" ] || [ "${FROMVERSION}" == "IF2" ]; then
	#This was added in FP1. So Fixpacks before FP1 should run this.
	addDateColumns
	if [ ${CCD_DB} == "oracle" ]; then
		alterCategoryItemAttributes
	fi
fi	

if  [ "${FROMVERSION}" == "BASE" ]  || [ "${FROMVERSION}" == "IF1" ] || [ "${FROMVERSION}" == "IF2" ] || [ "${FROMVERSION}" == "FP1" ]; then
	#This was added in FP2. So Fixpacks below FP2 should run this.
	updateSequences
fi	

if  [ "${FROMVERSION}" != "FP8" ]  && [ "${FROMVERSION}" != "FP9" ] && [ "${FROMVERSION}" != "IF8" ]; then	
	#This was added in FP8. So need not be run for Fixpacks from FP8 onwards. 
	updateWFE_NOMIndex
fi

if [ "${FROMVERSION}" != "FP9" ] && [ "${FROMVERSION}" != "IF8" ]; then
	#This was added in FP9. So need not be run for Fixpacks from FP9 onwards.
	if [ ${CCD_DB} == "db2" ]; then		
		createIndexExplainObject
	fi
fi

if [ "${FROMVERSION}" != "IF8" ] && [ ${CCD_DB} == "oracle" ]; then
	#This was added in IF8. So need not be run for Fixpacks from IF8 onwards.
	updateITACABColumns
fi	

#This is added in FP10. So needs to be run for all previous Fixpacks.
updateLCKIndex
alterEntryTabbedPane
updateCollabAttributes
	



#
# Verification step
#

`rm -f $TOP/logs/verify.log ` 
. ${DB_DIR}/util/run_db_cmd.sh --file=$DB_SCRIPTS/verifyFPMigration.sql --errorfilter=yes >> $TOP/logs/verify.log




