# Copyright 1999-2017. Parallels IP Holdings GmbH. All Rights Reserved.
package DAL;

use strict;
eval{require warnings;1;};

use AgentConfig;
use HelpFuncs;
use IPC::Run;
use Logging;
require PleskVersion;

my $dbh;

sub init {
  $dbh = shift;
  return;
}

my %_CACHE;
sub CACHED (&) {
  my ($code) = @_;
  return $code->(); # Caching disabled, because of changes in Perl interpreter, which break down caching algorithm below

  my $key = scalar($code);
  if ( !exists $_CACHE{$key} ) {
    $_CACHE{$key} = $code->();
  }
  return $_CACHE{$key};
}

sub __tableExists {
  my ($name) = @_;

  unless ( (defined $name) && ($name ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my $exists;
  my $sql = "SHOW TABLES LIKE '?'";
  if ( $dbh->execute_rownum( $sql, $name ) and $dbh->fetchrow() ) {
    $exists = 1;
  }
  $dbh->finish();
  return $exists;

}

# Usage:
# my $www_root = __getScalar("SELECT www_root FROM subdomains WHERE id = $subdomainId");
sub __getScalar {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my $result;
  if ( $dbh->execute_rownum($sql, @params) and my $ptrRow = $dbh->fetchrow() ) {
    $result = $ptrRow->[0];
  }
  $dbh->finish();
  return $result;
}

# Usage:
# my @subdomain = @{__getArray("SELECT name, displayName, www_root, FROM subdomains WHERE id = $subdomainId")};
sub __getArray {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my @result;
  if ( $dbh->execute_rownum($sql, @params) ) {
    while ( my $ptrRow = $dbh->fetchrow() ) {
        push (@result, @{$ptrRow});
    }
  }

  $dbh->finish();
  return \@result;
}

# Usage:
# my %subdomain = %{__getHash("SELECT name, displayName, www_root, FROM subdomains WHERE id = $subdomainId")};
sub __getHash {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my %result;
  if ( $dbh->execute_rownum($sql, @params) and my $ptrHash = $dbh->fetchhash() ) {
    %result = %{$ptrHash};
  }
  $dbh->finish();
  return \%result;
}

# Usage:
# my @domain_names;
# __walkHashes { push @domain_names, shift->{'name'} } "SELECT name, displayName FROM domains";
sub __walkHashes {
  my ($code, $sql, @params) = @_;

  unless ( (ref($code)=~/CODE/) && (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  if ( $dbh->execute_rownum($sql, @params) ) {
    while ( my $ptrHash = $dbh->fetchhash() ) {
      $code->($ptrHash);
    }
  }
  $dbh->finish();
  return;
}

# Usage:
# my @domain_names;
# __walkRows { push @domain_names, @{shift} } "SELECT name, displayName FROM domains";
#
sub __walkRows {
  my ($code, $sql, @params) = @_;

  unless ( (ref($code)=~/CODE/) && (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  if ( $dbh->execute_rownum($sql, @params) ) {
    while ( my $ptrRow = $dbh->fetchrow() ) {
      my @row = @{$ptrRow};
      $code->(\@row);
    }
  }
  $dbh->finish();
  return;
}

sub __getPairsAsHash {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my %hash;
  my $code = sub {
    my $ptrRow = shift;
    $hash{$ptrRow->[0]} = $ptrRow->[1];
  };
  __walkRows($code, $sql, @params);
  return \%hash;
}

# Usage:
# my @siteHashes = @{__getArrayOfHashes("SELECT name, displayName FROM domains WHERE webspace_id=$domainId")};
# @siteHashes array contains references to hash
sub __getArrayOfHashes {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my @result;
  my $code = sub {
    push @result, shift;
  };
  __walkHashes($code, $sql, @params);
  return \@result;
}

# Usage:
# my @siteHashes = @{__getArrayOfHashes("SELECT name, displayName FROM domains WHERE webspace_id=$domainId")};
# @siteHashes array contains references to hash
sub __getHashOfHashes {
  my ($sql, $keyColumn, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my %result;
  my $code = sub {
    my $hash = shift;
    @result{$hash->{$keyColumn}} = $hash
  };
  __walkHashes($code, $sql, @params);
  return \%result;
}

sub __getArrayOfRows {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my @result;
  my $code = sub {
    push @result, shift;
  };
  __walkRows($code, $sql, @params);
  return \@result;
}

sub __getArrayOfValues {
  my ($sql, @params) = @_;

  unless ( (defined $sql) && ($sql ne '') ) {
    die 'Invalid parameters in '. (caller(0))[3];
  }

  my @result;
  my $code = sub {
    push @result, shift->[0];
  };
  __walkRows($code, $sql, @params);
  return \@result;
}

sub getServiceIps {
  my ($domainId, $service) = @_;

  my $sql = "SELECT IP_Addresses.ip_address FROM DomainServices, IpAddressesCollections, IP_Addresses WHERE DomainServices.ipCollectionId = IpAddressesCollections.ipCollectionId AND IpAddressesCollections.ipAddressId = IP_Addresses.id AND DomainServices.dom_id = ? AND DomainServices.type = '?'";

  return __getArrayOfValues($sql, $domainId, $service);
}

sub getUserHash {
  my ($user) = @_;

  my $sql = "SELECT smb_users.*, smb_roles.name AS SmbRoleName, mail.id AS mailId, mail.dom_id AS mailDomId, mail.mail_name AS mailName, domains.displayName AS domainName "
            . "FROM smb_users INNER JOIN smb_roles ON smb_users.roleId = smb_roles.id "
            . "LEFT JOIN mail ON smb_users.id = mail.userId "
            . "LEFT JOIN domains ON mail.dom_id = domains.id "
            . "WHERE smb_users.login = '?'";

  my $hash = __getHash($sql, $user);

  if (!keys %$hash) {
    # hash is empty (error occurred)
    return $hash;
  }

  my $passwordType = isSecurePasswords() ? "sym" : "plain";
  $hash->{'passwordType'} = $passwordType;
  $hash->{'isBase64'} = '0' unless $passwordType eq 'plain';    
  $hash->{'fileSharingId'} = getFileSharingId($hash->{'login'});

  if ($hash->{'mailName'} && $hash->{'domainName'}) {
    # SMB-user uses account`s email, get it due to sync errors
    $hash->{'email'} = $hash->{'mailName'} . '@' . $hash->{'domainName'};
  }

  return $hash;
}

sub getRolePermissions {
  my ($roleId) = @_;

  my $sql = "SELECT smb_generalPermissions.code, smb_roleGeneralPermissions.isAllowed FROM smb_roles "
            . "INNER JOIN smb_roleGeneralPermissions ON smb_roles.id = smb_roleGeneralPermissions.roleId "
            . "INNER JOIN smb_generalPermissions ON smb_generalPermissions.id = smb_roleGeneralPermissions.generalPermissionId "
            . "WHERE smb_roles.id = ? ";
  return __getPairsAsHash($sql, $roleId);
}

sub getRoleServicePermission {
  my ($roleId, $service) = @_;

  my $sql = "SELECT COUNT(*) FROM smb_serviceProviders INNER JOIN smb_serviceInstances ON smb_serviceInstances.serviceProviderId = smb_serviceProviders.id "
            . "INNER JOIN smb_servicePermissions ON smb_servicePermissions.serviceInstanceId = smb_serviceInstances.id AND smb_servicePermissions.serviceProviderId = smb_serviceProviders.id "
            . "INNER JOIN smb_roleServicePermissions ON smb_roleServicePermissions.servicePermissionId = smb_servicePermissions.id "
            . "WHERE smb_serviceProviders.classname = '?' AND smb_roleServicePermissions.roleId = ?";
  my $count = __getScalar($sql, $service, $roleId);
  return 0 unless defined $count;
  return ($count > 0)? 1 : 0 ;
}

sub getRoleServicePermissions {
  my ($roleId) = @_;

  my $sql = "SELECT smb_serviceProviders.classname, smb_serviceInstances.description, smb_serviceInstances.externalId, "
            . "smb_servicePermissions.serviceInstanceId, smb_servicePermissions.permissionCode, smb_servicePermissions.class "
            . "FROM smb_serviceProviders INNER JOIN smb_serviceInstances ON smb_serviceInstances.serviceProviderId = smb_serviceProviders.id "
            . "INNER JOIN smb_servicePermissions ON smb_servicePermissions.serviceInstanceId = smb_serviceInstances.id AND smb_servicePermissions.serviceProviderId = smb_serviceProviders.id "
            . "INNER JOIN smb_roleServicePermissions ON smb_roleServicePermissions.servicePermissionId = smb_servicePermissions.id "
            . "WHERE smb_roleServicePermissions.roleId = ?";

  return __getArrayOfHashes($sql, $roleId);
}

sub selectSmbRoles {
  CACHED { return __getArrayOfHashes('SELECT id, name, isBuiltIn FROM smb_roles'); }
}

sub getUserAssignedApplications {
  my ($userId) = @_;

  my $sql = "SELECT smb_serviceInstances.externalId FROM smb_serviceInstances "
          . "INNER JOIN smb_servicePermissions ON smb_serviceInstances.id = smb_servicePermissions.serviceInstanceId "
          . "INNER JOIN smb_userServicePermissions ON smb_userServicePermissions.servicePermissionId = smb_servicePermissions.id WHERE smb_userServicePermissions.userId = ? ";
  return __getArrayOfValues($sql, $userId);
}

sub getDomainKeysState {
  my ($domainId) = @_;
  my $sql = "SELECT p.value FROM Parameters p, DomainServices ds "
    . "WHERE ds.parameters_id = p.id AND ds.dom_id = ? AND p.parameter = 'domain_keys_sign'";
  return __getScalar($sql, $domainId);
}

sub getDomainKeysPublicKey {
  my ($domainName, $dnsZoneId) = @_;
  my $sql = "SELECT val FROM dns_recs WHERE dns_zone_id=? AND displayHost = BINARY '?'";
  my $publicKey = __getScalar($sql, $dnsZoneId, "default._domainkey.$domainName.");
  if (defined($publicKey) && $publicKey =~ /p=([^\s;]{10,})/) {
    return $1;
  }
  return undef;
}

sub getApsBundleFilterType {
  my ($filterId) = @_;
  return __getScalar("SELECT type FROM smb_apsBundleFilters WHERE id = ?", $filterId);
}

sub getApsBundleFilterItems {
  my ($filterId) = @_;
  my $sql = "SELECT propertyName, propertyValue FROM smb_apsBundleFilterItems WHERE filterId = ?";

  my @items;
  my $code = sub {
    my $ptrHash = shift;
    push @items, {$ptrHash->{'propertyName'} => $ptrHash->{'propertyValue'}};
  };
  __walkHashes($code, $sql, $filterId);
  return \@items;
}

# if (PleskVersion::atLeast(9, 0, 0)
sub getCpAccess {
  return __getArrayOfRows('SELECT type, netaddr, netmask FROM cp_access');
}

sub getNotes {
  return __getPairsAsHash('SELECT id, text FROM Notes');

}

sub getExpirationWarnDays {
  return __getScalar("SELECT val FROM misc WHERE param = 'exp_warn_time'");
}

sub getNotifications {
  return __getArrayOfHashes('SELECT * FROM Notifications');
}

sub getActions {
  return __getArrayOfHashes('SELECT * FROM actions');
}

sub getEvents {
  my $sql = 'SELECT a.name, eh.priority, eh.user, eh.command, eh.action_id FROM event_handlers eh, actions a WHERE eh.action_id = a.id';

  my %events;
  my $code = sub {
    my $ptrHash = shift;
    push @{$events{$ptrHash->{'action_id'}}}, $ptrHash;
  };
  __walkHashes($code, $sql);
  return \%events;
}

sub getGLParams {
  my $glParams = __getPairsAsHash('SELECT param, value FROM GL_settings');
  my @white_domains;
  my @black_domains;
  foreach my $remote ( @{__getArrayOfHashes('SELECT domain, type FROM GL_remote_domains')} ) {
    if( $remote->{'type'} eq 'white' ) {
      push @white_domains, $remote->{'domain'};
    }
    elsif( $remote->{'type'} eq 'black' ) {
      push @black_domains, $remote->{'domain'};
    }
  }
  $glParams->{ 'white_domains' } = \@white_domains;
  $glParams->{ 'black_domains' } = \@black_domains;
  return $glParams;
}

# PleskVersion::atLeast( 8, 3, 0 ) )  and !( PleskVersion::atLeast( 10, 0, 0 ) )
sub getSBConfig {
  return __getArrayOfHashes('SELECT * FROM SBConfig');
}

sub getAPSLicenses {
  return __getArrayOfRows('SELECT l.key_number, l.source, lt.license_type_hash FROM APSLicenses l, APSLicenseTypes lt WHERE l.license_type_id = lt.id');
}

sub getAPSApplicationItems {
  my ($aishared) = @_;

  my $sql = "SELECT lt.license_type_hash AS license_type_id, sap.name AS sapp_name, sap.version AS sapp_version, sap.release "
        . "AS sapp_release, $aishared AS shared, ai.disabled AS disabled "
        . "FROM APSApplicationItems AS ai INNER JOIN SiteAppPackages sap ON (sap.id = ai.pkg_id) "
        . "LEFT JOIN APSLicenseTypes AS lt ON (lt.id = ai.license_type_id)";
  return __getArrayOfHashes($sql);
}

# PleskVersion::atLeast( 8, 3, 0 ) and !( PleskVersion::atLeast( 10, 0, 0 ) )
sub getSiteAppPackages {
  return __getArrayOfRows('SELECT `name`, `version`, `release` FROM SiteAppPackages');
}

sub getApsPackages100 {
  return __getArrayOfRows('SELECT `name`, `version`, `release`, `cacheId`, `isUploaded`, `isVisible` FROM smb_apsPackages');
}

#PleskVersion::atLeast( 8, 3, 0 )
sub getSSOBranding {
  return __getArrayOfRows('SELECT * FROM SSOBranding');
}

sub getCertificateIds {
  my ($repoId) = @_;

  my $sql = "SELECT c.id FROM certificates c, Repository r WHERE c.id=r.component_id AND r.rep_id=? ORDER BY c.id";
  return __getArrayOfValues($sql, $repoId);
}

sub getCertificateIds6 {
  my ($domainId) = @_;

  return __getArrayOfValues("SELECT id FROM certificates WHERE dom_id=?", $domainId);
}

sub getDnsRecsT {
  return __getArrayOfHashes('SELECT * FROM dns_recs_t');
}

sub getExternalWebmail {
  return __getArrayOfHashes('SELECT name, url, enabled FROM externalWebmails');
}

#PleskVersion::atLeast( 8, 1, 0 )?
sub getSmtpPoplocks {
  return __getPairsAsHash('SELECT ip_address, ip_mask FROM smtp_poplocks');
}

#PleskVersion::atLeast( 8, 1, 0 )?
sub getBadmailfrom {
  return __getArrayOfValues('SELECT domain FROM badmailfrom');
}

sub getMisc {
  return __getPairsAsHash('SELECT param, val FROM misc');
}

sub getMailServerSettings {
  return __getPairsAsHash("SELECT name, value FROM ServiceNodeConfiguration AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND section='mailServer'");
}

sub getFtpServerSettings {
  return __getPairsAsHash("SELECT name, value FROM ServiceNodeConfiguration AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND section='ftpServer'");
}

sub getIPAddressesFtps {
  my $sqlQuery = 'SELECT CONCAT("ftps_", ip_address), ftps FROM IP_Addresses';
  return __getPairsAsHash($sqlQuery);
}

sub isSecurePasswords {
  CACHED {
    my $val = __getScalar("SELECT val FROM misc WHERE param = 'secure_passwords'");
    return (defined $val) && ($val eq 'true');
  }
}

# PleskVersion::atLeast( 10, 0, 0 )
sub isBusinessModelUpgraded {
  my $val = __getScalar("SELECT val FROM misc WHERE param = 'buisiness_model_upgraded'");
  return (defined $val) && ($val eq 'true');
}

# PleskVersion::atLeast( 8, 0, 0 )
sub getDefaultServerParams {
  return __getPairsAsHash("SELECT param, val FROM misc WHERE param RLIKE '^default_server_'");
}

sub getSpamServerSettings {
  return __getPairsAsHash("SELECT name, value FROM ServiceNodeConfiguration AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND section='mailServer' AND name RLIKE '^spamfilter_'");
}

sub getAdminMiscParams {
  return __getPairsAsHash("SELECT param, val FROM misc WHERE param RLIKE '^admin' or param='send_announce' or param='max_button_length'");
  # do not use LIKE, bug #106566
}

sub getAdminEmail {
  CACHED { return __getScalar("SELECT val FROM misc WHERE param='admin_email'"); }
}

sub getAdminClientParams {
  return __getPairsAsHash("SELECT cp.param, cp.val FROM cl_param cp, clients c WHERE cp.cl_id = c.id AND c.login = 'admin'");
}

# !PleskVersion::atLeast( 9, 0, 0 )
sub getKeyHistory {
  return __getArrayOfHashes('SELECT * FROM key_history WHERE filename IS NOT NULL');
}

sub getDatabaseServers {
  CACHED { return __getArrayOfHashes('SELECT id, host, port, type, admin_login, admin_password, server_version FROM DatabaseServers'); }
}

sub getDatabaseServer {
  my ($dbServerId) = @_;
  foreach my $ptrHash (@{getDatabaseServers()}) {
    if ($ptrHash->{'id'} == $dbServerId) {
      return $ptrHash;
    }
  }
}

sub getClientExternalId {
  my ($clientId) = @_;
  return __getScalar("SELECT external_id FROM clients WHERE id = ?", $clientId);
}

sub getFullHostName {
  return __getScalar("SELECT val FROM misc WHERE param = 'FullHostName'");
}

sub getServerIps {
  my ($certPtr) = @_;

  my $sql = "SELECT ip_address, mask, iface, c.pvt_key pvtkey FROM IP_Addresses i INNER JOIN certificates c ON c.id = i.ssl_certificate_id";
  if (defined $certPtr) {
    my @certs = @{$certPtr};
    if ( @certs ) {
      $sql .= ' WHERE i.ssl_certificate_id IN (';
      $sql .= join(',',("?")x@certs);
      $sql .= ')';
      return __getArrayOfHashes($sql, @certs);
    }
  }
  return __getArrayOfHashes($sql);
}

sub getPlanItemProperties {
  my ($planItemId) = @_;
  return __getPairsAsHash("SELECT name, value from PlanItemProperties WHERE plan_item_id = ?", $planItemId);
}

sub getPlanItems {
  CACHED { return __getArrayOfHashes('SELECT * from PlanItems'); }
}

sub getClientParams {
  my ($clientId) = @_;
  return __getPairsAsHash("SELECT param, val FROM cl_param WHERE cl_id = ?", $clientId);
}

sub getDefaultIpAddress {
  return __getScalar("SELECT ip_address FROM IP_Addresses ip, misc m WHERE ip.id = m.val AND m.param = 'def_ip_id'");
}

#plesk 2.5 - 5.5
sub getNameVirtualHost {
  return __getScalar("SELECT val FROM misc WHERE param = 'NameVirtualHost'");
}

sub getSpamfilterPreferences {
  my ($spamfilter_id) = @_;
  return __getArrayOfRows("SELECT preference, value FROM spamfilter_preferences WHERE spamfilter_id = ? ORDER BY length(preference), preference", $spamfilter_id);
}

sub getServerSpamfilterId {
  CACHED { return __getScalar("SELECT id FROM spamfilter WHERE username = '*@*'"); }
}

sub getMailnameSpamfilter {
  my ($mailName) = @_;
  return __getHash("SELECT * FROM spamfilter WHERE username = '?'", $mailName);
}

sub getDomain1040Ptr {
  my ($domainName) = @_;
  return __getHash("SELECT d1.*, d2.name AS parentDomainName, d2.displayName AS parentDomainDisplayName FROM domains d1 LEFT JOIN domains d2 ON d1.parentDomainId = d2.id WHERE d1.displayName = BINARY '?'", $domainName);
}

sub getDomainPtrByAsciiName {
  my ($domainAsciiName) = @_;

  return __getHash("SELECT d1.*, d2.name AS parentDomainName, d2.displayName AS parentDomainDisplayName FROM domains d1 LEFT JOIN domains d2 ON d1.parentDomainId = d2.id WHERE d1.name = '?'", $domainAsciiName);
}

sub getDomainPtr {
  my ($domainName) = @_;

  return getDomain1040Ptr($domainName);
}

sub getCertificatePrivateKey {
  my ($certId) = @_;
  return __getScalar("SELECT pvt_key FROM certificates WHERE id = ?", $certId);
}

sub getDomainDefaultCert102 {
  my ($domainId) = @_;

  my @certIds;
  my @ips = @{getServiceIps($domainId, 'web')};
  if( @ips ) {
    my $sql = "SELECT c.id FROM domains d, hosting h, IP_Addresses ip, certificates c "
                           . "WHERE c.id=ip.ssl_certificate_id AND h.dom_id=d.id AND d.id=?";
    $sql .= ' AND ip.ip_address IN (';
    $sql .= join(',', ("'?'")x@ips);
    $sql .= ')';
    my @ids = @{__getArrayOfValues($sql, $domainId, @ips)};
    if ( @ids ) {
      push @certIds, @ids;
    }
  }
  return \@certIds;
}

sub getDomainParams {
  my ($domainId) = @_;
  return __getPairsAsHash( "SELECT param,val FROM dom_param WHERE dom_id=?", $domainId );
}

sub getDomainParam {
  my ($domainId, $param) = @_;
  return __getScalar( "SELECT val FROM dom_param WHERE dom_id=? AND param='?'", $domainId, $param );
}

sub getDomainDatabases {
  my ($domainId, $excluded) = @_;

  my $sql = "SELECT id, type, db_server_id, name FROM data_bases WHERE dom_id=?";
  if( ref($excluded) =~ /ARRAY/ ) {
    my @excludeIds = @{$excluded};
    if ( @excludeIds ) {
      $sql .= " AND id NOT IN (" . join(',',('?')x@excludeIds) . ")";
      return __getArrayOfHashes($sql, $domainId, @excludeIds);
    }
  }
  return __getArrayOfHashes($sql, $domainId);
}

sub getDomainMails {
  my ($domainId) = @_;

  my $sql;

  $sql = "SELECT m.id, a.password, a.type, m.mail_name FROM mail m LEFT JOIN accounts a ON m.account_id=a.id ";
  if (defined $domainId) {
    $sql .= " WHERE m.dom_id=? ORDER BY m.mail_name";
    return __getArrayOfRows($sql, $domainId);
  }

  return __getArrayOfRows($sql);
}

sub DatabaseUserRemoteAccessRules {
    my ($userLogin, $dbId) = @_;

    return __getArrayOfRows(
        "SELECT r.type AS type, r.ipAddress AS ip, r.mask AS mask " .
        "FROM db_users AS u JOIN DatabaseUserRemoteAccessRules AS r " .
        "WHERE u.id = r.databaseUserId AND u.login = '?' AND u.db_id = ?"
        , $userLogin, $dbId
    );
}

sub getDomainMaillists {
  my ($domainId) = @_;

  return __getArrayOfRows("SELECT id,name,status FROM MailLists WHERE dom_id=?", $domainId);
}

sub getDomainServiceStatus {
  my ($domainId, $service ) = @_;
  return __getScalar("SELECT status FROM DomainServices WHERE dom_id=? AND type='?'", $domainId, $service);
}

sub getDomainSuscriptionProperties {
  my ($domainId) = @_;
  CACHED { return __getPairsAsHash("SELECT sp.`name`, sp.`value` FROM `Subscriptions` s INNER JOIN `SubscriptionProperties` sp ON sp.`subscription_id`=s.`id` WHERE s.`object_type`='domain' AND s.`object_id`=?", $domainId); }
}

sub getDomainWebApps {
  my ($domainId) = @_;
  return __getPairsAsHash("SELECT wa.name, wa.status FROM WebApps wa LEFT JOIN DomainServices ds ON wa.domain_service_id=ds.id WHERE ds.dom_id=?", $domainId);
}

sub getMailRedirects {
  my ($mnId) = @_;
  return __getArrayOfValues("SELECT address FROM mail_redir WHERE mn_id=?", $mnId);
}

sub getDomainWwwRoot {
  my ($domainId) = @_;
  return __getScalar("SELECT www_root FROM hosting WHERE dom_id = ?", $domainId);
}

sub getDomainCgiBinMode {
  my ($domainId) = @_;
  return getDomainParam($domainId,'cgi_bin_mode');
}

sub getLogrotation {
  my ($logrotation_id) = @_;
  return __getHash("SELECT * FROM log_rotation WHERE id=?", $logrotation_id);
}

sub getFileSharingId {
  my ($userLogin) = @_;
  my $sql = "SELECT spa.externalId FROM smb_servicePermissionAccounts spa, smb_users u, smb_servicePermissions sp, smb_serviceProviders sps "
  . "WHERE spa.userId = u.id AND spa.servicePermissionId = sp.id AND sp.permissionCode='sharing' AND sp.serviceProviderId = sps.id "
  . "AND sps.classname ='Smb_Service_Provider_FileSharing' AND u.login = '?'";
  return __getScalar($sql, $userLogin);
}

sub getFileSharingSettings {
  return __getPairsAsHash("SELECT name, value FROM smb_settings WHERE name LIKE 'fs%'");
}

sub getFileSharingUnlistedFiles {
  return __getArrayOfHashes("SELECT * FROM smb_fileSharingUnlistedFiles");
}

sub getApsPackages103 {
  return __getArrayOfRows('SELECT `name`, `version`, `release`, `cacheId`, `isUploaded`, `isVisible`, `registryUid` FROM smb_apsPackages');
}

sub getHostingParams {
  my ($domain_id) = @_;
  return __getHash("SELECT * FROM hosting WHERE dom_id=?", $domain_id);
}

sub getPhpSettings {
  my ($settingsId) = @_;
  CACHED { return __getPairsAsHash("SELECT name, value FROM PhpSettingsParameters WHERE id=?", $settingsId); }
}

sub getPhpSettingsCustom {
  my ($settingsId) = @_;
  return __getScalar("SELECT `value` FROM PhpSettingsCustom WHERE `id`=?", $settingsId);
}

sub getManagementNodeWebServerSettingsId {
  return __getScalar("SELECT c.value FROM ServiceNodeConfiguration AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND c.section='webServer' AND c.name='webServerSettingsId'");
}

sub getDomainWebServerSettingsId {
  my ($domainId) = @_;
  return getDomainParam($domainId, 'webServerSettingsId');
}

sub getWebServerSettings {
  my ($settingsId) = @_;
  CACHED { return __getPairsAsHash("SELECT name, value FROM WebServerSettingsParameters WHERE webServerSettingsId=?", $settingsId); }
}

sub getDomainNameById {
  my ($domainId) = @_;
  CACHED { return __getScalar("SELECT displayName FROM domains WHERE id=?", $domainId); }
}

sub getDomainAsciiNameById {
  my ($domainId) = @_;
  CACHED { return __getScalar("SELECT name FROM domains WHERE id=?", $domainId); }
}

sub getSingleSmbSetting {
  my ($settingName) = @_;
  CACHED { return __getScalar("SELECT value FROM smb_settings WHERE name='?'", $settingName); }
}

sub isInstalledApplications {
  my ($domainId) = @_;

  my $sql;
  my $count;

  #Check applications with hosting context
  $sql = "SELECT COUNT(*) FROM apsResources INNER JOIN domains ON (apsResources.pleskId = domains.id OR apsResources.pleskId = domains.webspace_id) WHERE apsResources.pleskType = 'hosting' AND (domains.id = ? OR domains.webspace_id = ?)";
  $count = __getScalar($sql, $domainId, $domainId);

  unless ($count > 0) {
    #Check applications with subscription context
    $sql = "SELECT COUNT(*) FROM apsResources INNER JOIN Subscriptions ON (apsResources.pleskId = Subscriptions.id AND Subscriptions.object_type = 'domain') WHERE apsResources.pleskType = 'subscription' AND Subscriptions.object_id = ?";
    $count = __getScalar($sql, $domainId);
  }
  return ($count > 0)? 1 : 0;
}

sub getCommonDatabaseUsers {
  my ( $domainId, $embeddedInfo ) = @_;
  
  my $sql = "SELECT dbu.id, dbu.login, a.type, a.password, dbs.host, dbs.port, dbs.type AS dbservertype, dbu.external_id " .
    " FROM db_users dbu, accounts a, DatabaseServers dbs WHERE dbu.account_id = a.id AND dbu.db_id = 0 AND dbu.dom_id = ? AND dbu.db_server_id = dbs.id";
  return _getDatabaseUsersInfo( $sql, $embeddedInfo, $domainId );
}

sub getDatabaseUsers {
  my ( $databaseId, $embeddedInfo ) = @_;

  my $sql = "SELECT db_users.id, db_users.login, accounts.password, accounts.type, data_bases.default_user_id, data_bases.type AS dbservertype, db_users.external_id " .
    " FROM db_users, accounts, data_bases WHERE db_users.db_id = ? AND db_users.db_id = data_bases.id AND db_users.account_id = accounts.id";
  
  return _getDatabaseUsersInfo( $sql, $embeddedInfo, $databaseId );
}

sub _getDatabaseUsersInfo {
  my ( $sql, $embeddedInfo, @params ) = @_;
  
  my $arrayRef = CACHED { return __getArrayOfHashes( $sql, @params ); };
  
  if ( ref( $arrayRef ) =~ /ARRAY/ ) {
    my @updatedUsers = map( getMysqlAcl($_, $embeddedInfo), @{$arrayRef} );
    return \@updatedUsers;
  } else {
    return [];
  }
}

sub getDatabaseRelatedSites {
  my ($databaseId, $domainId) = @_;
  my $sql = "SELECT d.displayName AS siteName FROM domains AS w "
    . "INNER JOIN domains AS d ON d.id = w.id OR d.webspace_id = w.id "
    . "INNER JOIN dom_param AS p ON p.dom_id = d.id AND p.param = 'lastDatabaseSelectedId' AND p.val = '?' "
    . "WHERE w.id = ?";
  return __getArrayOfValues($sql, $databaseId, $domainId);
}

sub getMysqlAcl {
  my ( $userHash, $embeddedInfo ) = @_;
  
  if ( $userHash->{'dbservertype'} ne 'mysql') {
    return $userHash;
  }
  
  if (defined($embeddedInfo->{'database-users'}) && ref($embeddedInfo->{'database-users'}[0]) =~ /HASH/) {
    foreach my $dbUserInfo (@{$embeddedInfo->{'database-users'}[0]->{'user'}}) {
      if ( $dbUserInfo->{'id'} ne $userHash->{'id'} ) {
        next;
      }
      if (defined $dbUserInfo->{'acl'} && (ref($dbUserInfo->{'acl'}[0]) =~ /HASH/)) {
        $userHash->{'acl'} = $dbUserInfo->{'acl'}[0]->{'host'};
      }
      if (defined $dbUserInfo->{'privileges'} && (ref($dbUserInfo->{'privileges'}[0]) =~ /HASH/)) {
        $userHash->{'privileges'} = $dbUserInfo->{'privileges'}[0]->{'privilege'};
      }
    }
  }

  return $userHash;
}

# $domainId = undef means return all domain aliases on server
sub getDomainAliases {
  my ($domainId) = @_;

  my $sql = "SELECT * FROM domain_aliases";
  if (defined $domainId) {
    $sql .= " WHERE dom_id = ?";
    return CACHED { return __getArrayOfHashes($sql, $domainId); }
  }

  CACHED { return __getArrayOfHashes($sql); }
}

# $domainId = undef means return all web users on server
sub getWebUsers {
  my ($domainId) = @_;

  my $sql = "SELECT * FROM web_users";

  if (defined $domainId) {
    $sql .= " WHERE dom_id=? ORDER BY id";
    return CACHED { return __getArrayOfHashes($sql, $domainId); }
  }

  CACHED { return __getArrayOfHashes($sql); }
}

sub getAdditionalFtpUsers {
  my ($domainId) = @_;

  my $sql = "SELECT * FROM ftp_users";

  if (defined $domainId) {
    $sql .= " WHERE dom_id=?";
    return CACHED { return __getArrayOfHashes($sql, $domainId); }
  }

  CACHED { return __getArrayOfHashes($sql); }
}

sub getMysqlVariable {
  my ($variable) = @_;

  my $sql = "SHOW VARIABLES LIKE '?'";

  CACHED { return __getPairsAsHash($sql, $variable); }
}

sub getSitesByWebspaceId {
  my ($webspaceId) = @_;

  my $sql = "SELECT name, displayName, id FROM domains WHERE webspace_id=?";

  CACHED { return __getArrayOfHashes($sql, $webspaceId); }
}

sub getSysUserInfo {
  my ($sysUserId) = @_;

  my $sql = "SELECT * FROM sys_users WHERE id=?";

  CACHED { return __getHash($sql, $sysUserId); }
}

sub getSysUserHomeByDomainName {
  my ($domainAsciiName) = @_;

  my $sql = "SELECT s.home FROM sys_users s, hosting h, domains d WHERE h.sys_user_id=s.id AND d.id=h.dom_id AND d.name='?'";

  CACHED { return __getScalar($sql, $domainAsciiName); }
}

sub getSysUserQuotaByDomainId {
  my ($domainId) = @_;

  my $sql = "SELECT s.quota FROM sys_users s, hosting h, domains d WHERE h.sys_user_id=s.id AND d.id=h.dom_id AND d.id=?";

  CACHED { return __getScalar($sql, $domainId); }
}

sub getDomainTomcatStatus {
  my ($domainId) = @_;
  return getDomainServiceStatus( $domainId, 'tomcat' );
}

sub getCustomButtonIdsByOwner71 {
  my ( $owner_type, $owner_id ) = @_;

  my %custom_button_owner_types = (
    "admin"        => 1,     #Session::IS_ADMIN
    "server"       => 1,     #Session::IS_ADMIN
    "reseller"     => 2,     #Session::IS_RESELLER
    "client"       => 4,     #Session::IS_CLIENT
    "domain-admin" => 8,     #Session::IS_DOMAIN_OWNER
    "mailuser"     => 16,    #Session::IS_MAIL_USER
  );

  my $level = $custom_button_owner_types{$owner_type};
  my $sql = "SELECT id FROM custom_buttons WHERE level=? AND level_id=?";

  CACHED { return __getArray($sql, $level, $owner_id); }
}

sub getMailNameInfo {
  my ($mailNameId) = @_;

  my $sql = "SELECT * FROM mail WHERE id = ?";

  CACHED { return __getHash($sql, $mailNameId); }
}

sub getPhpHandlers {
  return __getPairsAsHash("SELECT e.name, e.value FROM ServiceNodeEnvironment AS e INNER JOIN ServiceNodes AS n ON e.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND e.section = 'phphandlers'");
}

sub getPhpHandlersConfiguration {
  return __getPairsAsHash("SELECT c.name, c.value FROM ServiceNodeConfiguration AS c INNER JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND c.section = 'phphandlers'");
}

sub getSoftwarePackages {
  return __getPairsAsHash("SELECT e.name, e.value FROM ServiceNodeEnvironment AS e INNER JOIN ServiceNodes AS n ON e.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND e.section = 'componentsPackages' AND e.value IS NOT NULL AND e.value <> ''");
}

sub getInstalledModules {
  return __getPairsAsHash("SELECT `name`, `release` FROM Modules");
}

sub getRestrictedDomains {
  CACHED { return __getArrayOfHashes('SELECT id, name FROM RestrictedDomains'); }
}

sub getSubscription {
  my ($objectType, $objectId) = @_;

  return __getHash("SELECT * FROM Subscriptions  WHERE object_id = ? AND object_type = '?'", $objectId, $objectType);
}

sub getSubscriptionsAdminDescriptions {
  my $sql = "SELECT name, adminDescription AS description, 'domain' AS type FROM domains";
  return __getArrayOfHashes( $sql );
}

sub getClientsDescriptions {
  my ( $ownerId ) = @_;

  my $sql = "SELECT login AS name, description, type FROM clients WHERE parent_id = ?";
  return __getArrayOfHashes( $sql, $ownerId );
}

sub getSubscriptionsResellerDescriptions {
  my ( $resellerId ) = @_;

  my $sql = "SELECT name, resellerDescription AS description, 'domain' AS type FROM domains INNER JOIN clients ON domains.cl_id = clients.id WHERE domains.cl_id = ? OR clients.parent_id = ?";
  return __getArrayOfHashes( $sql, $resellerId, $resellerId );
}

sub getWebspaceIdByDomainId {
  my ($domainId) = @_;

  my $sql = "SELECT webspace_id FROM domains "
    . " WHERE id=?";
  my $webspaceId;
  if ( $dbh->execute_rownum($sql, $domainId) and my $ptrRow = $dbh->fetchrow() ) {
    $webspaceId = $ptrRow->[0];
  }
  if ( $webspaceId eq '0' ) {
    $webspaceId = $domainId;
  }

  return $webspaceId;
}



sub getIpAddresses {
  my ($ipId) = @_;

  my $sql = "SELECT id, ip_address FROM IP_Addresses";

  return __getHashOfHashes($sql, 'id');
}

sub getClientShortInfo{
  my ($domainName) = @_;

  my $sql = "SELECT id, vendor_id, type FROM clients WHERE login = BINARY '?'";
  return __getHash($sql, $domainName);
}

sub getDomainShortInfo{
  my ($domainName) = @_;

  my $sql = "SELECT id, cl_id, name FROM domains WHERE displayName = BINARY '?'";
  return __getHash($sql, $domainName);
}


sub getEmbeddedInfo {
  my ($args) = @_;
  my $embeddedInfo = {};

  my $cmd = AgentConfig::getBackupRestoreHelperUtil();
  push(@{$cmd}, @{$args});

  my $stdout;
  eval {
    Logging::debug("Execute: @{$cmd}");
    my $stderr;
    IPC::Run::run($cmd, '1>', \$stdout, '2>', \$stderr) or die($stderr);
  };
  if ($@) {
    Logging::warning("Cannot execute backup helper '@{$cmd}': $@, STDOUT: $stdout", 'UtilityError');
    return $embeddedInfo;
  }

  eval {require XML::Simple; 1;};
  my $xs = XML::Simple->new(ForceArray => 1, SuppressEmpty => '');
  my $embeddedInfoIn = '';
  eval {
    $embeddedInfoIn = $xs->XMLin($stdout, KeyAttr => []);
  };
  if ($@) {
    Logging::warning("backup_restore_helper utility returned incorrect result. Some settings have not been dumped. Please, contact your service provider", 'UtilityError');
    Logging::debug("Backup helper returned invalid response, expected is XML: $@\nSTDOUT:\n$stdout", 'UtilityError');
  }
  return $embeddedInfo if '' eq $embeddedInfoIn;

  while (my ($name, $content) = each(%{$embeddedInfoIn})) {
    my ($section) = @{$content};
    if (ref($section) =~ /HASH/ && defined($section->{'failure'})) {
        Logging::warning($section->{'failure'}[0]->{'message'}[0]);
        next;
    }
    $embeddedInfo->{$name} = $content;
  }
  return $embeddedInfo;
}

sub backupExtension {
  my ($extId, $objectType, $objectId) = @_;
  my $cmd = AgentConfig::getBackupRestoreHelperUtil();
  push(@{$cmd}, @{["--backup-extension", $extId, "-object-type", $objectType]});
  push(@{$cmd}, @{["-object-id", $objectId]}) if $objectType ne "server";

  my $stdout;
  eval {
    Logging::debug("Execute: @{$cmd}");
    my $stderr;
    IPC::Run::run($cmd, '1>', \$stdout, '2>', \$stderr) or die($stderr);
  };
  if ($@) {
    Logging::warning("backup_restore_helper utility returned incorrect result. Extension with ID $extId cannot be backuped. Please, contact your service provider", 'UtilityError');
    Logging::debug("Cannot execute backup_restore_helper '@{$cmd}': $@, STDOUT: $stdout", 'UtilityError');
    return undef;
  }

  eval {require XML::Simple; 1;};
  my $xs = XML::Simple->new(ForceArray => 1, SuppressEmpty => '', RootName => 'extension');
  my $result = eval {
    return $xs->XMLin($stdout, KeyAttr => []);
  };
  if ($@) {
    $result = undef;
    Logging::warning("backup_restore_helper utility returned incorrect result. Some settings have not been dumped. Please, contact your service provider", 'UtilityError');
    Logging::debug("Backup helper returned invalid response, expected is XML: $@\nSTDOUT:\n$stdout", 'UtilityError');
  }
  return $result;
}

sub postBackupExtension {
  my ($extId, $objectType, $objectId) = @_;

  my $cmd = AgentConfig::getBackupRestoreHelperUtil();
  push(@{$cmd}, @{["--post-backup-extension", $extId, "-object-type", $objectType]});
  push(@{$cmd}, @{["-object-id", $objectId]}) if $objectType ne "server";

  my $stdout;
  eval {
    Logging::debug("Execute: @{$cmd}");
    my $stderr;
    IPC::Run::run($cmd, '1>', \$stdout, '2>', \$stderr) or die($stderr);
  };
  if ($@) {
    Logging::debug("Cannot execute backup_restore_helper '@{$cmd}': $@, STDOUT: $stdout", 'UtilityError');
  }
}
1;