/*****************************************************************

*

* filename: chg_acd_office.pc

*

* purpose:  changes ofc_short_name in acd_groups

*          

* usage info:     chg_acd_office    -f <full name>

*                       -o <new office>

*                       -i interactive  mode.

*

*           where:      <full name> REQUIRED.

*                    is a string that will find a

*                    match in ACD_GROUPS FULL_NAME

*

*                 <new office> REQUIRED.

*                    is a 6 character string that will be the

*                    new OFC_SHORT_NAME in ACD_GROUPS.

*                

*                 -i prompts for confirmation before

*                    performing the update.

*

******************************************************************

* history

* -------

*

* when                  what                                who

* ----                  ----                                ---

* 06-Mar-1998     v1.0 - started                      JJM

*

******************************************************************/

#include <stdio.h>

#include <time.h>

#include <string.h>

 

EXEC SQL INCLUDE SQLCA;

EXEC SQL INCLUDE ORACA;

 

#define VERSION "v1.0"

#define NODATA 1403

 

int   major_counter;                /* general purpose int's      */

int   minor_counter;         

int   status;                             /* generic status             */

int   target_set      = 0;    /* set if -f arg given        */

int   new_set         = 0;    /* set if -o arg given        */   

int   interactive_set = 0;    /* set if -i arg given        */

 

char  *msg_text[132];         /* bufer for messages         */

char  *real_name[132];  /* name of this process       */

 

time_t      *curtime;               /* for time functions         */

struct tm *MyTime;                  /* time structure             */

 

/* declare host and indicator varibles for SQLCA            */

 

EXEC SQL BEGIN DECLARE SECTION;

 

VARCHAR     in_full_name[80];

VARCHAR in_new_name[7];

VARCHAR out_ofc_short_name[7];

VARCHAR out_ent_sub_type_code[8];

VARCHAR out_ent_name[7];

VARCHAR out_name[7];

VARCHAR     out_short_name[7];

char ind_comm;

 

EXEC SQL END DECLARE SECTION;

 

/***************** end global/preprocessing *********************/

Àpar int main (argc,argv)

int argc;

char **argv;

{

 

/* annouce ourselves                            */

sprintf(msg_text, "Starting %s\n", VERSION );

put_msg(msg_text, argv[0], 0 );

 

/* check args                                   */

decode_arguments( argc, argv );

 

/* connect to oracle                            */

connect_to_oracle("/", " ");

 

/* find valid relationships                     */

get_acd_entity();

 

/* update acd_groups short_ofc_name             */

update_acd_groups();

 

exit(0);

}

/******************* end of main *********************************/

Àpar /****************** update_acd_groups  *****************************

*

* purpose:  updates ACD_GROUPS OFC_SHORT_NAME from -o arg.

*           Seeks confirmation if -i arg given.

*

* arguments:    command line arguments in_full_name and in_new_name

*           should be validated from get_acd_entity();

*

* history:

*

* when            what                                who

* ----            ----                                ---

* 06-Mar-1998     started                                   jjm

*

************************************************************************/

 

update_acd_groups()

 

{

 

int  enable_update = 0; /* zero to enable update sql */

 

char key;

 

if( interactive_set )

   {

      fprintf(stdout,"\nAbout to modify ACD_GROUP: %s %s \n",

                   in_full_name.arr,

                   out_ofc_short_name.arr);

      fprintf(stdout,"                       TO: %s %s \n\n",

                   in_full_name.arr,

                   in_new_name.arr);

 

      fprintf(stdout,"Is this OK (Y/N/D:N)?  ");

 

            key = fgetc(stdin);

            key = toupper(key);

 

              enable_update = key - 'Y';

     }

 

if( enable_update == 0 )

   {

     EXEC SQL WHENEVER SQLERROR DO check_sql("update_acd_groups");

 

     EXEC SQL UPDATE acd_groups

              SET    ofc_short_name = :in_new_name

            WHERE  full_name      = :in_full_name;

 

     EXEC SQL COMMIT;  

    

   }

 

/* show new relationships           */

fprintf(stdout,"\n\nNew relationship follows:\n");

get_acd_entity();

 

return(0);

}/***************** end of update_acd_groups ***************************/

 

Àpar

/****************** get_acd_entity ***************************************

*

* purpose:  steps up from ACD_GROUPS to ENTITIES walking along

*           foriegn keys. 

*

* arguments:      Global in_full_name should be valid from decode_arguments.

*

* notes:    although it is possible to transverse the relationships

*           in one short query, I wanted to know the specific point

*           where query returned no data in order to provide

*           more specific debug verbage.

* history:

*

* when            what                                who

* ----            ----                                ---

* Mar 1998  started                                   jjm

*

*************************************************************************/

 

int get_acd_entity()

{

 

int   has_office  = 1;  /* cleared if acd group has no office        */

int   oentity           = 1;  /* cleared if acd group has no office entity */

int   entity            = 1;  /* cleared if acd group has no entity        */

 

/* init string arrays */

 

memset( out_ofc_short_name.arr,    '\0', out_ofc_short_name.len );

memset( out_ent_sub_type_code.arr, '\0', out_ent_sub_type_code.len );

memset( out_ent_name.arr,          '\0', out_ent_name.len );

memset( out_short_name.arr,        '\0', out_short_name.len );

memset( out_name.arr,              '\0', out_name.len);

 

 

EXEC SQL WHENEVER SQLERROR DO check_sql("get_acd_entity");

 

/* get the ACD group          */

 

      EXEC SQL  

          select distinct nvl( rtrim(ofc_short_name), ' ' )

          into :out_ofc_short_name

          from acd_groups

          where full_name = :in_full_name;

     

      if( sqlca.sqlcode == NODATA )

         {

           sprintf(msg_text,

                 "can't find %s in ACD_GROUPS\n",

                in_full_name.arr);

 

           put_msg(msg_text,"get_acd_entity",0);

           exit(-2);

         }

Àpar /* get the office        */

 

      EXEC SQL

         select distinct nvl( rtrim(short_name),'NULL'),

                     nvl( rtrim(ent_sub_type_code),'NULL')

         into   :out_short_name,

              :out_ent_sub_type_code

         from offices

         where short_name = :out_ofc_short_name;

 

      if( sqlca.sqlcode == NODATA )

         {

           has_office = 0;

           sprintf(msg_text,

                 "%s%s's assigned office (%s) has no link to OFFICES.\n",

                 "WARNING: ",

                in_full_name.arr,

                 out_ofc_short_name.arr);

           put_msg(msg_text,"get_acd_entity",0);

         }

 

      /* get the office entity if ACD_GROUPS-OFFICES linked */

      else

         {

            EXEC SQL

                  select distinct nvl( rtrim(ent_name),'NULL')

                  into :out_ent_name

                  from office_entities

                  where ofc_short_name    = :out_short_name

                  and ent_sub_type_code = :out_ent_sub_type_code;

 

            if( sqlca.sqlcode == NODATA )

                 {

                       oentity = 0;

                   entity  = 0;     /* w/o an oentity then no entity */

                 sprintf(msg_text,

                 "%s%s's assigned office (%-6.6s) has no office entity\n",

                 "WARNING: ",

                 in_full_name.arr,

                     out_short_name.arr);

                     put_msg(msg_text,"get_acd_entity",0);

               }

            else 

               {

                 /* get the entity type   */

                 EXEC SQL

                     select distinct nvl( rtrim(sub_type_code),'NULL')

                   into :out_ent_sub_type_code

                   from entities

                   where name = :out_ent_name;

 

                   if( sqlca.sqlcode == NODATA )

                      {

                       entity = 0;

                       sprintf(msg_text,

                       "%s%s's office entity (%-6.6s) has no entity\n",

                     "WARNING: ",

                     in_full_name.arr,

                     out_ent_name.arr);

                       put_msg(msg_text,"get_acd_entity",0);

                       }

     

                  }

 

   } /* sqlca.sqlcode == NODATA else */

 

/**************************************************************************/

Àpar put_msg(" ","get_acd_entity",0);

 

      fprintf(stdout,"%-6.6s is part of %-6.6s\n",

                  in_full_name.arr,

                  out_short_name.arr);

 

      if( has_office )

        {

        if( oentity )

            {

            fprintf(stdout,"%-6.6s is comprised of %-6.6s\n",

                  out_ent_name.arr,

                  out_ofc_short_name.arr);

            }

           if( entity )

            {

            fprintf(stdout,"%-6.6s is a type %-7.7s\n",

                  out_ent_name.arr,

                  out_ent_sub_type_code.arr);

            }

        }

 

return(0);

}/***********************************************************************/

 

Àpar /************** decode_arguments *****************************************

*

* routine:  decode_arguments

*

* arguments:      argument count and pointer to char buffer of arguments

*

* purpose:  checks arguments for the occurance of required and

*           optional arguments and decodes them into global symbols.

*           arguments can be given in any order or format.

*

* returns:  0 successful normal completion.

*

* history

*

* when            what                                who

* ----            ----                                ---

* 25-feb-1998     started                                   jjm  

*

*************************************************************************/

 

int decode_arguments( Dargc, Dargv )

 

int  Dargc;

char *Dargv[];

 

{

int   min_args    = Dargc - 1;

int   valid_arg   = 0;  /* set if known arg     */

 

if( Dargc == 1 )

   {

     sprintf(msg_text,"No arguments.\n");

     put_msg(msg_text,"decode_arguments",1);

     exit(-1);

   }

 

for( major_counter = 1; major_counter < Dargc; major_counter++)

    {

      /* reset valid_arg, iterate thru arg list */

 

      valid_arg = 0;

 

/***************************************************************************/

 

      if( strncmp(Dargv[major_counter], "?", 1) == 0)

         {

             sprintf(msg_text,"See %s document.\n",Dargv[0]);

           put_msg(msg_text,"decode_arguments",1);

           exit(-1);

           }

/***************************************************************************/

 

      if( strncmp(Dargv[major_counter], "-f", 2) == 0)

          {

            if( (major_counter+1) > min_args                ||

                 strncmp(Dargv[major_counter+1], "-i", 2) == 0    ||

                 strncmp(Dargv[major_counter+1], "-o", 2) == 0)

               {

                  sprintf(msg_text,"-f switch requires an argument\n");

                  put_msg(msg_text,"decode_arguments", 1);

                  exit(-1);

               }

             

            for( minor_counter =  0;

                 minor_counter <= strlen(Dargv[major_counter+1]);

                 minor_counter++ )

                {

                  Dargv[major_counter+1][minor_counter] =

                  toupper(Dargv[major_counter+1][minor_counter]);

                    }

 

            strcpy((char *)in_full_name.arr,Dargv[major_counter+1]);

            in_full_name.len = strlen(in_full_name.arr);

 

            major_counter++; /* don't decode -f arg   */

            target_set = 1;

            valid_arg  = 1;  

 

           } /* end of -f argument decode       */

/***************************************************************************/

Àpar if( strncmp(Dargv[major_counter], "-o", 2) == 0)

          {

            if( (major_counter+1) > min_args                ||

                 strncmp(Dargv[major_counter+1], "-i", 2) == 0    ||

                 strncmp(Dargv[major_counter+1], "-f", 2) == 0)

               {

                  sprintf(msg_text,"-o switch requires an argument\n");

                  put_msg(msg_text,"decode_arguments", 1);

                  exit(-1);

               }

             

            for( minor_counter =  0;

                 minor_counter <= strlen(Dargv[major_counter+1]);

                 minor_counter++ )

                {

                  Dargv[major_counter+1][minor_counter] =

                  toupper(Dargv[major_counter+1][minor_counter]);

                    }

 

            strcpy((char *)in_new_name.arr,Dargv[major_counter+1]);

            in_new_name.len = strlen(in_new_name.arr);

 

            major_counter++; /* don't decode -o arg   */

            new_set = 1;

            valid_arg  = 1;  

 

           } /* end of -o argument decode       */

/***************************************************************************/

Àpar if( strncmp(Dargv[major_counter], "-i", 2) == 0)

          {

            interactive_set = 1;

            valid_arg  = 1;

          } /* end of -i argument decode        */

/***************************************************************************/

            if( !valid_arg )

               {

                 sprintf(msg_text, "Can`t decode %s \n",

                 Dargv[major_counter] );

                 put_msg(msg_text,"decode_arguments",1);

                 exit(-1);

               }            

/***************************************************************************/

 

     }/* end of arguments           */

 

if( !target_set )

   {

      sprintf(msg_text,"Must supply -f argument.\n");

      put_msg(msg_text,"decode_arguments",1);

      exit(-1);

   }

 

if( !new_set )

   {

      sprintf(msg_text,"Must supply -o argument.\n");

      put_msg(msg_text,"decode_arguments",1);

      exit(-1);

   }

 

/*

sprintf(msg_text,

"ACD GROUP is:\t%s\nNEW OFFICE is: \t%s\nINTERACTIVE is:\t%s\n",

in_full_name.arr,

in_new_name.arr,

interactive_set?"ON ":"OFF");

put_msg(msg_text,"decode_argumnets", 0);

*/

 

return(0);

}

/********************** end of decode_argrments *******************************/

 

Àpar

/******************** connect_to_oracle **************************

*

* purpose:  initates a connection to a previoulsy configured

*           oracle instance.

*

* arguments:      username    string

*           password    string

*

* returns:  -1 if an error occured, 0 is successful.

*

* history:

*

* when            what                                who

* ----            ----                                ---

*

* Feb-1998  I found it in a CA/TS Dev. directory            jjm

*

*******************************************************************/

 

connect_to_oracle(pusrnm,ppasswd)

char *pusrnm,*ppasswd;

{

 

EXEC SQL BEGIN DECLARE SECTION;

      VARCHAR    username[10],password[10];

EXEC SQL END DECLARE SECTION;

 

EXEC SQL WHENEVER SQLERROR DO check_sql("connect_to_oracle");

 

strcpy((char *)username.arr,pusrnm);

strcpy((char *)password.arr,ppasswd);

 

EXEC SQL CONNECT :username IDENTIFIED BY :password;

 

}

/********************* end of connect_to_oracle *****************/

Àpar

/********************** check_sql *********************************

*

* purpose:  formats a message for put_msg if sql.sqlcode !0

*           then exits with -1.

*

* arguments:      char pointer to a string that names the caller.

*

* returns:  sql.sqlcode

*

* history:

*

* when            what                          who

* ----            ----                          ---

* 25-feb-1998     started                             jjm  

*

******************************************************************/

int check_sql(caller)

char *caller;

{

 

if( sqlca.sqlcode != 0)

   {

 

    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL COMMIT WORK RELEASE;         

 

     sprintf(msg_text,"%s:\n %70s\n",

     sqlca.sqlerrm.sqlerrmc); 

     put_msg(msg_text,caller,0);

     

     exit(-1);

   }

return(0);

}

/************************** end of check_sql **********************/

Àpar /**************** put_msg *************************************************

*

* routine:  put_msg

*

* arguments:      msg_text:   pointer to a char buffer containing

*                       some text for stderr.

*

*           caller:           pointer to char buffer that gives

*                       name of caller.

*

*           show_usage: enable usage verbage.

*

* purpose:  writes an error message using the supplied format to stderr.

*

* returns:  0 successful normal completion.

*

*****************************************************************************/

 

int put_msg(msg_text, caller, show_usage )

char *msg_text;

char *caller;

int show_usage;

 

{

/* time at entry to this module                 */   

 

curtime=time(NULL);

MyTime = localtime(&curtime);

 

fprintf(stdout,"\nFROM:\t%s \n",caller);

 

fprintf(stdout,"AT:\t%2.2d:%2.2d:%2.2d %2.2d:%2.2d.%2.2d\n\n",

            MyTime->tm_year,

            MyTime->tm_mon + 1,

            MyTime->tm_mday, 

            MyTime->tm_hour, 

            MyTime->tm_min,

            MyTime->tm_sec);

 

fprintf(stdout,"%s\n",msg_text);

 

if( show_usage )

   {

fprintf(stdout,"Usage: %s -i -f <FULL_NAME> -o <OFC_SHORT_NAME>\n\n",

real_name);

fprintf(stdout,"Where: -i OPTIONAL.     Ask for confirmation before updates\n");

fprintf(stdout,"                        Default is NO CONFIRMATION. \n");

fprintf(stdout,"       <FULL_NAME>      REQUIRED. String that will find \n");

fprintf(stdout,"                        a match in ACD_GROUPS FULL_NAME.\n");

fprintf(stdout,"       <OFC_SHORT_NAME> REQUIRED. String to change \n");

fprintf(stdout,"                        ACD_GROUPS OFC_SHORT_NAME to.\n\n");

   }

 

return(0);

}/*********** end of put_msg *********************************************/