view release on metacpan or  search on metacpan

applications/custom/sde-supportRequest.pm-orig  view on Meta::CPAN

  # DATABASE  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  my $CARTO_SERVER   = 'crinaeadtbs01';
  my $CARTO_DATABASE = 'carto';
  my $CARTO_TABLE    = 'TestRelationships';

  # SOAP  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  my $tool           = 'ASNMTAP';
  my $cluster        = 'http://itiltest1.smals-mvm.be/ivwse';
  my $namespace      = 'http://itiltest1.smals-mvm.be/ivwse/';

  # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
  my ($UserID, $customer, $source) = ('MonitoringOffice', "$tool MONITORING", 'Alarm');

  my %soapService_NS = ('http://schemas.xmlsoap.org/wsdl/mime/'        => 'mime',
                        'http://schemas.xmlsoap.org/wsdl/soap/'        => 'soap',
                        'http://schemas.xmlsoap.org/wsdl/soap11/'      => 'soap11',
                        'http://schemas.xmlsoap.org/soap/encoding/'    => 'soapenc',
                        'http://www.w3.org/2001/XMLSchema'             => 's',
                        'http://schemas.xmlsoap.org/wsdl/'             => 'wsdl',
                        'http://schemas.xmlsoap.org/wsdl/http/'        => 'http',

                        'http://microsoft.com/wsdl/mime/textMatching/' => 'tm',
                        'http://infravision.com/ivwse'                 => 's1'
                       );

  # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

	sub _getEventsData {
    my ($catalogID, $uKey, $hash_valueEventsData, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID, $debug) = @_;

    my $rv  = 1;
    my $dbh = DBI->connect("DBI:mysql:$DATABASE:$SERVERNAMEREADONLY:$SERVERPORTREADONLY", "$SERVERUSERREADONLY", "$SERVERPASSREADONLY") or $rv = error_trap_DBI(*STDOUT, "Cannot connect to the database", $debug, $pagedir, $pageset, $htmlTitle, $subTit...

    if ($dbh and $rv) {
      my $sql = "select SQL_NO_CACHE title, duration, timeslot, endDate, endTime, status, statusMessage, filename from $SERVERTABLEVENTSDISPLAYDT where catalogID = '$catalogID' and uKey = '$uKey' and step <> '0' order by timeslot desc limit 1";
      my $sth = $dbh->prepare( $sql ) or $rv = error_trap_DBI(*STDOUT, "Cannot dbh->prepare: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
      $sth->execute or $rv = error_trap_DBI(*STDOUT, "Cannot sth->execute: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID) if $rv;

      if ( $rv ) {
        while ( my $ref = $sth->fetchrow_hashref() ) {
          $$hash_valueEventsData{found}         = 1;
          $$hash_valueEventsData{title}         = $ref->{title}         if ( $ref->{title} );
          $$hash_valueEventsData{duration}      = $ref->{duration}      if ( $ref->{duration} );
          $$hash_valueEventsData{timeslot}      = $ref->{timeslot}      if ( $ref->{timeslot} );
          $$hash_valueEventsData{endDate}       = $ref->{endDate}       if ( $ref->{endDate} );
          $$hash_valueEventsData{endTime}       = $ref->{endTime}       if ( $ref->{endTime} );
          $$hash_valueEventsData{status}        = $ref->{status}        if ( $ref->{status} );
          $$hash_valueEventsData{statusMessage} = $ref->{statusMessage} if ( $ref->{statusMessage} );
          $$hash_valueEventsData{filename}      = $ref->{filename}      if ( $ref->{filename} );
        }

        $sth->finish() or $rv = error_trap_DBI(*STDOUT, "Cannot sth->finish: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);

        if ( $rv and exists $$hash_valueEventsData{found} and $$hash_valueEventsData{status} !~ /$STATE{$ERRORS{OK}}/ ) {
          my @table = ( "$SERVERTABLEVENTSDISPLAYDT", "$SERVERTABLEVENTS USE INDEX (catalogID, uKey, key_timeslot)" );

          foreach my $table ( @table ) {
            if ( $$hash_valueEventsData{status} =~ /$STATE{$ERRORS{WARNING}}/ ) { # event
              $sql = "select SQL_NO_CACHE endDate, endTime, timeslot, status from $table where catalogID = '$catalogID' and uKey = '$uKey' and step <> '0' and not ( status = '". $STATE{$ERRORS{WARNING}} ."' ) order by timeslot desc limit 1";
            } elsif ( $$hash_valueEventsData{status} =~ /($STATE{$ERRORS{UNKNOWN}}|$STATE{$ERRORS{CRITICAL}}|$STATE{$ERRORS{'NO DATA'}})/ ) { # incident
              $sql = "select SQL_NO_CACHE endDate, endTime, timeslot, status from $table where catalogID = '$catalogID' and uKey = '$uKey' and step <> '0' and not ( status = '". $STATE{$ERRORS{UNKNOWN}} ."' or status = '". $STATE{$ERRORS{CRITICAL}} ....
            }

            my $sth = $dbh->prepare( $sql ) or $rv = error_trap_DBI(*STDOUT, "Cannot dbh->prepare: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
            $sth->execute or $rv = error_trap_DBI(*STDOUT, "Cannot sth->execute: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID) if $rv;

            if ( $rv ) {
              while ( my $ref = $sth->fetchrow_hashref() ) {
                $$hash_valueEventsData{OUT_SCOPE}{found}    = $ERRORS{$ref->{status}};
                $$hash_valueEventsData{OUT_SCOPE}{timeslot} = $ref->{timeslot} if ( $ref->{timeslot} );
                $$hash_valueEventsData{OUT_SCOPE}{endDate}  = $ref->{endDate}  if ( $ref->{endDate} );
                $$hash_valueEventsData{OUT_SCOPE}{endTime}  = $ref->{endTime}  if ( $ref->{endTime} );
              }

              $sth->finish() or $rv = error_trap_DBI(*STDOUT, "Cannot sth->finish: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
              last if ( exists $$hash_valueEventsData{OUT_SCOPE}{found} );
            }
          }

          if ( $rv and exists $$hash_valueEventsData{OUT_SCOPE}{found} and defined $$hash_valueEventsData{OUT_SCOPE}{found} ) {
            my $timeRange = "timeslot > '". $$hash_valueEventsData{OUT_SCOPE}{timeslot} ."'";

            foreach my $table ( @table ) {
              if ( $$hash_valueEventsData{status} =~ /$STATE{$ERRORS{WARNING}}/ ) { # event
                $sql = "select SQL_NO_CACHE endDate, endTime, timeslot, status from $table where catalogID = '$catalogID' and uKey = '$uKey' and step <> '0' and $timeRange and ( status = '". $STATE{$ERRORS{WARNING}} ."' ) order by timeslot asc limit ...
              } elsif ( $$hash_valueEventsData{status} =~ /($STATE{$ERRORS{UNKNOWN}}|$STATE{$ERRORS{CRITICAL}}|$STATE{$ERRORS{'NO DATA'}})/ ) { # incident
                $sql = "select SQL_NO_CACHE endDate, endTime, timeslot, status from $table where catalogID = '$catalogID' and uKey = '$uKey' and step <> '0' and $timeRange and ( status = '". $STATE{$ERRORS{UNKNOWN}} ."' or status = '". $STATE{$ERRORS...
              }

              my $sth = $dbh->prepare( $sql ) or $rv = error_trap_DBI(*STDOUT, "Cannot dbh->prepare: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
              $sth->execute or $rv = error_trap_DBI(*STDOUT, "Cannot sth->execute: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID) if $rv;

              if ( $rv ) {
                while (my $ref = $sth->fetchrow_hashref()) {
                  $$hash_valueEventsData{IN_SCOPE}{found}    = $ERRORS{$ref->{status}};
                  $$hash_valueEventsData{IN_SCOPE}{timeslot} = $ref->{timeslot} if ( $ref->{timeslot} );
                  $$hash_valueEventsData{IN_SCOPE}{endDate}  = $ref->{endDate}  if ( $ref->{endDate} );
                  $$hash_valueEventsData{IN_SCOPE}{endTime}  = $ref->{endTime}  if ( $ref->{endTime} );
                }

                $sth->finish() or $rv = error_trap_DBI(*STDOUT, "Cannot sth->finish: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
                last if ( exists $$hash_valueEventsData{IN_SCOPE}{found} );
              }
            }
          }
        }
      }

      $dbh->disconnect or $rv = error_trap_DBI(*STDOUT, "Sorry, the database was unable to add your entry.", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID) if ($dbh and $rv);
    }

    return $rv;
  }

  # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

	sub _getSupportRequestData {
    my ($serverName, $database, $table, $catalogID, $uKey, $hash_valueSupportRequestData, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID, $debug) = @_;

    $$hash_valueSupportRequestData{code}        = '?';
    $$hash_valueSupportRequestData{slaID}       = '?';
    $$hash_valueSupportRequestData{impact}      = 'High';
    $$hash_valueSupportRequestData{invoicedOrg} = '?';
    $$hash_valueSupportRequestData{service}     = '?';
    $$hash_valueSupportRequestData{priority}    = 'P01';

    my $rv  = 1;
    my $dbh = DBI->connect("DBI:mysql:$database:$serverName:$SERVERPORTREADONLY", "$SERVERUSERREADONLY", "$SERVERPASSREADONLY") or $rv = error_trap_DBI(*STDOUT, "Cannot connect to the database", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $ref...

    if ($dbh and $rv) {
      my $sql = "select SQL_NO_CACHE code, slaID, invoicedOrg, service, impact, priority from $table where catalogID = '$catalogID' and uKey = '$uKey'";
      my $sth = $dbh->prepare( $sql ) or $rv = error_trap_DBI(*STDOUT, "Cannot dbh->prepare: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
      $sth->execute or $rv = error_trap_DBI(*STDOUT, "Cannot sth->execute: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID) if $rv;

      if ( $rv ) {
        while (my $ref = $sth->fetchrow_hashref()) {
          $$hash_valueSupportRequestData{code}        = $ref->{code}        if ( $ref->{code} );
          $$hash_valueSupportRequestData{slaID}       = $ref->{slaID}       if ( $ref->{slaID} );
          $$hash_valueSupportRequestData{impact}      = $ref->{impact}      if ( $ref->{impact} );
          $$hash_valueSupportRequestData{invoicedOrg} = $ref->{invoicedOrg} if ( $ref->{invoicedOrg} );
          $$hash_valueSupportRequestData{service}     = $ref->{service}     if ( $ref->{service} );
          $$hash_valueSupportRequestData{priority}    = $ref->{priority}    if ( $ref->{priority} );
        }

        $sth->finish() or $rv = error_trap_DBI(*STDOUT, "Cannot sth->finish: $sql", $debug, $pagedir, $pageset, $htmlTitle, $subTitle, $refresh, $onload, $sessionID);
      }