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

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

use HelpFuncs;
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 '$name'";
  if ( $dbh->execute_rownum( $sql ) 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) = @_;

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

  my $result;
  if ( $dbh->execute_rownum($sql) 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) = @_;

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

  my @result;
  if ( $dbh->execute_rownum($sql) and my $ptrRow = $dbh->fetchrow() ) {
    @result = @{$ptrRow};
  }
  $dbh->finish();
  return \@result;
}

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

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

  my %result;
  if ( $dbh->execute_rownum($sql) 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 = shift;
  my $sql = shift;

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

  if ( $dbh->execute_rownum($sql) ) {
    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 = shift;
  my $sql = shift;

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

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

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

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

  my %hash;
  __walkRows {
    my $ptrRow = shift;
    $hash{$ptrRow->[0]} = $ptrRow->[1];
  } $sql;
  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) = @_;

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

  my @result;
  __walkHashes { push @result, shift } $sql;
  return \@result;
}

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

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

  my @result;
  __walkRows { push @result, shift } $sql;
  return \@result;
}

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

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

  my @result;
  __walkRows { push @result, shift->[0] } $sql;
  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 = '$domainId' AND DomainServices.type = '$service'";

  return __getArrayOfValues($sql);
}

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

  my $sql = "SELECT smb_users.*, smb_roles.name AS SmbRoleName, mail.id AS mailId, mail.dom_id AS mailDomId "
            . "FROM smb_users INNER JOIN smb_roles ON smb_users.roleId = smb_roles.id "
            . "LEFT JOIN mail ON smb_users.id = mail.userId "
            . "WHERE smb_users.login = " . $dbh->quote($user);

  my $hash = __getHash($sql);

  if (keys %$hash) { # if hash is not empty (no error occurred)
    my $passwordType = ((PleskVersion::atLeast( 11, 0, 0 ) and isSecurePasswords()) ? "sym" : "plain");
    $hash->{'passwordType'} = $passwordType;
    $hash->{'isBase64'} = '0' unless $passwordType eq 'plain';
    $hash->{'isLegacyUser'} = 1 if PleskVersion::isSmb();
    $hash->{'fileSharingId'} = getFileSharingId($hash->{'login'});
  }

  return $hash;
}

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

  my $sql = "SELECT login FROM smb_users WHERE roleId = '$roleId' ";

  return __getArrayOfValues($sql);
}

sub selectLoginFromSmbUsers {
  return __getArrayOfValues('SELECT login FROM smb_users');
}

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 = '$roleId' ";
  return __getPairsAsHash($sql);
}

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 = '$service' AND smb_roleServicePermissions.roleId = '$roleId'";
  my $count = __getScalar($sql);
  return 0 unless defined $count;
  return ($count > 0)? 1 : 0 ;
}

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

  if ( PleskVersion::atLeast( 10, 3, 0 ) ) {
    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 = '$roleId'";

    return __getArrayOfHashes($sql);
  } else {
    return [];
  }
}

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

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

  if ( PleskVersion::atLeast( 10, 3, 0 ) ) {
    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 = $userId ";
    return __getArrayOfValues($sql);
  } else {
    return [];
  }
}

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

sub getDomainKeysPublicKey {
  my ($domainName, $dnsZoneId) = @_;
  my $sql = "SELECT val FROM dns_recs WHERE dns_zone_id=$dnsZoneId AND displayHost = BINARY 'default._domainkey.$domainName.'";
  my $publicKey = __getScalar($sql);
  if( defined $publicKey ) {
    chop $publicKey;
    $publicKey = substr( $publicKey, 2 );
  }
  return $publicKey;
}

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 = '$filterId'";

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

sub getPanelMode {
  my $val = __getScalar("SELECT val FROM misc WHERE param = 'power_user_panel'");
  if( (defined $val) && ($val eq 'true') ){
    return 'PowerUser';
  }
  return;
}

# if (PleskVersion::atLeast(9, 0, 0) and not PleskVersion::isSmb())
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;
  __walkHashes {
    my $ptrHash = shift;
    push @{$events{$ptrHash->{'action_id'}}}, $ptrHash;
  } $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 ) and not PleskVersion::isSmb() )
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 ) and not PleskVersion::isSmb() )
sub getSiteAppPackages {
  return __getArrayOfRows('SELECT `name`, `version`, `release` FROM SiteAppPackages');
}

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

sub getSmbApsPackages {
  return __getArrayOfRows('SELECT `name`, `version`, `release`, `cacheId` 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='$repoId' ORDER BY c.id";
  return __getArrayOfValues($sql);
}

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 {
  my $sqlQuery = 'SELECT param, val FROM misc';

  if ( PleskVersion::atLeast( 11, 1, 13 ) ) {
    $sqlQuery = $sqlQuery . ' UNION SELECT name, value FROM ServiceNodeConfiguration AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = "local" AND section IN ("mailServer", "ftpServer")';
  }elsif ( PleskVersion::atLeast( 11, 1, 0 ) ) {
    $sqlQuery = $sqlQuery . ' UNION SELECT name, value FROM MailServerProperties AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = "local"';
  }

  return __getPairsAsHash($sqlQuery);
}

sub getIPAddressesFtps {
  if ( PleskVersion::atLeast( 9, 0, 0 ) ) {
    my $sqlQuery = 'SELECT CONCAT("ftps_", ip_address), ftps FROM IP_Addresses';
    return __getPairsAsHash($sqlQuery);
  } else {
    return {};
  }
}

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 {
  if ( PleskVersion::atLeast( 11, 1, 13) ) {
    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_'");
  }elsif ( PleskVersion::atLeast( 11, 1, 0 ) ) {
    return __getPairsAsHash("SELECT name, value FROM MailServerProperties AS c LEFT JOIN ServiceNodes AS n ON c.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND name RLIKE '^spamfilter_'");
  }
  return __getPairsAsHash("SELECT param, val FROM misc WHERE param 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(',',@certs);
      $sql .= ')';
    }
  }
  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 = '$spamfilter_id' ORDER BY length(preference), preference");
}

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

sub getMailnameSpamfilter {
  my ($mailName) = @_;
  return __getHash("SELECT * FROM spamfilter WHERE username = " . $dbh->quote($mailName));
}

sub getDomain71Ptr {
  my ($domainName) = @_;
  return __getHash("SELECT * FROM domains WHERE displayName = BINARY '$domainName'");
}

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) = @_;

  if ( PleskVersion::atLeast( 10, 4, 0 ) ) {
    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'");
  }

  return __getHash("SELECT * FROM domains WHERE name = '$domainAsciiName'");
}

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

  if ( PleskVersion::atLeast( 10, 4, 0 ) ) {
	return getDomain1040Ptr($domainName);
  }

  return getDomain71Ptr($domainName);
}

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

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

  my @certIds;
  my $certId = __getScalar(  "SELECT c.id FROM domains d, hosting h, IP_Addresses ip, certificates c "
                           . "WHERE c.id=ip.ssl_certificate_id AND ip.id=h.ip_address_id AND h.dom_id=d.id AND d.id=$domainId");
  if ( defined $certId) {
    push @certIds, $certId;
  }
  return \@certIds;
}

#PleskVersion::atLeast( 10, 2, 0) and not PleskVersion::isSmb()
sub getDomainDefaultCert102 {
  my ($domainId) = @_;

  my @certIds;
  my @ips = @{getServiceIps($domainId, 'web')};
  if( @ips ) {
    @ips = map {"'$_'"}@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=$domainId";
    $sql .= ' AND ip.ip_address IN (';
    $sql .= join(',',@ips);
    $sql .= ')';
    my @ids = @{__getArrayOfValues($sql)};
    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=$domainId AND param='$param'" );
}

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

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

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

  my $sql;

  if ( PleskVersion::isSmb() ) {
    $sql = "SELECT m.id, a.password, a.type FROM mail m LEFT JOIN accounts a ON m.account_id=a.id LEFT JOIN smb_mailLists ON smb_mailLists.mailListId = m.id WHERE smb_mailLists.id IS NULL ";
    if (defined $domainId) {
      $sql .= " AND m.dom_id=$domainId ORDER BY m.mail_name";
    }
  }
  else {
    $sql = "SELECT m.id, a.password, a.type FROM mail m LEFT JOIN accounts a ON m.account_id=a.id ";
    if (defined $domainId) {
      $sql .= " WHERE m.dom_id=$domainId ORDER BY m.mail_name";
    }
  }

  return __getArrayOfRows($sql);
}

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

  if ( PleskVersion::isSmb() ) {
    return __getArrayOfRows("SELECT m.`mailListId`, m.`name`, 0 FROM `smb_mailLists` m, `domains` d WHERE d.`id`=$domainId AND m.`name` LIKE BINARY CONCAT('%".'@'."',d.`displayName`)");
  }

  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=$domainId AND type='$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");
}

# ppsmbe
sub getSmbMailnameUserId {
  my ($mnId) = @_;
  return __getScalar("SELECT uuid FROM smb_users INNER JOIN smb_emails ON smb_emails.userId=smb_users.id WHERE smb_emails.mailNameId=$mnId");
}

sub getSubdomainWwwRoot {
  my ($subdomainId) = @_;
  return __getScalar("SELECT www_root FROM subdomains WHERE id = $subdomainId");
}

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 = " . $dbh->quote($userLogin);
  return __getScalar($sql);
}

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

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 getPhpSettingsNoticeText {
  my ($settingsId) = @_;
  return __getScalar("SELECT n.text FROM Notes n, PhpSettings p WHERE n.id = p.noteId AND p.id=$settingsId");
}

sub getWebServerSettings {
  my ($settingsId) = @_;
  CACHED { return __getArrayOfRows("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 isInstalledApplicationsWithDatabases {
  my ($domainId) = @_;

  my $sql;
  if (PleskVersion::atLeast( 10, 3, 0 )) {
    $sql = "SELECT COUNT(*) FROM data_bases INNER JOIN apsResources ON apsResources.pleskId = data_bases.`id` AND apsResources.pleskType = 'db' WHERE data_bases.dom_id = $domainId";
  } elsif (PleskVersion::isSmb() or PleskVersion::atLeast( 10, 2, 0 )) {
    my $apsResourceClass = PleskVersion::isSmb() ? 'Smb_Plesk_Resource_Database' : 'CommonPanel_Aps_Resource_Plesk_Database';
    $sql = "SELECT COUNT(*) FROM data_bases INNER JOIN smb_apsResourceParameters ON smb_apsResourceParameters.value = data_bases.`id` AND smb_apsResourceParameters.name = 'PleskId' INNER JOIN smb_apsResources ON smb_apsResourceParameters.resourceId = smb_apsResources.id AND smb_apsResources.class = '$apsResourceClass' WHERE data_bases.dom_id = $domainId";
  } else {
    return 1;
  }

  my $count = __getScalar($sql);
  return 0 unless defined $count;
  return ($count > 0)? 1 : 0;
}

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

  my $sql;
  my $count;

  if (PleskVersion::atLeast( 10, 3, 0 )) {
    #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 = $domainId OR domains.webspace_id = $domainId)";
    $count = __getScalar($sql);

    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 = $domainId";
      $count = __getScalar($sql);

      unless ($count > 0) {
        #Check applications on subdomains
        $sql = "SELECT COUNT(*) FROM apsResources INNER JOIN subdomains ON apsResources.pleskId = subdomains.id INNER JOIN domains ON (subdomains.dom_id = domains.id OR subdomains.dom_id = domains.webspace_id) WHERE (apsResources.pleskType = 'subdomain') AND (domains.id = $domainId OR domains.webspace_id = $domainId)";
        $count = __getScalar($sql);
      }
    }

  } elsif (not PleskVersion::isSmb() and PleskVersion::atLeast( 10, 2, 0 )) {
    $sql = "SELECT COUNT(*) FROM smb_apsContexts INNER JOIN domains ON (smb_apsContexts.requirementContextId = domains.id OR  smb_apsContexts.requirementContextId = domains.webspace_id)  AND (requirementContextClass = 'CommonPanel_Aps_Context_Plesk_Hosting' OR requirementContextClass = 'CommonPanel_Aps_Context_Plesk_Domain') WHERE domains.id = $domainId OR domains.webspace_id = $domainId";
    $count = __getScalar($sql);

    unless ($count > 0) {
      $sql = "SELECT COUNT(*) FROM smb_apsContexts INNER JOIN subdomains ON smb_apsContexts.requirementContextId = subdomains.id INNER JOIN domains ON (subdomains.dom_id = domains.id OR subdomains.dom_id = domains.webspace_id) WHERE requirementContextClass = 'CommonPanel_Aps_Context_Plesk_Subdomain' AND (domains.id = $domainId OR domains.webspace_id = $domainId)";
      $count = __getScalar($sql);
    }
  } elsif (PleskVersion::isSmb()) {
    $sql = "SELECT COUNT(*) FROM smb_apsContexts apsCtx, domains d WHERE apsCtx.requirementContextId = d.id AND (apsCtx.requirementContextClass = 'Smb_Aps_Requirements_Context_Plesk_Hosting' OR apsCtx.requirementContextClass = 'Smb_Aps_Requirements_Context_Plesk_Domain') AND d.id = $domainId";
    $count = __getScalar($sql);

    unless ($count > 0) {
      $sql = "SELECT COUNT(*) FROM smb_apsContexts apsCtx, subdomains sd, domains d WHERE apsCtx.requirementContextId = sd.id AND d.id = sd.dom_id AND (apsCtx.requirementContextClass = 'Smb_Aps_Requirements_Context_Plesk_Hosting' OR apsCtx.requirementContextClass = 'Smb_Aps_Context_Plesk_Domain') AND d.id = $domainId";
      $count = __getScalar($sql);
    }
  } else {
    return 1;
  }

  return ($count > 0)? 1 : 0;
}

# $databaseId = 0 means that db user belong to any database
sub getDatabaseUsers {
  my ($domainId, $databaseId) = @_;

  my $arrayRef = CACHED { return __getArrayOfHashes("SELECT dbu.id, dbu.login, a.type, a.password, dbs.host, dbs.port, dbs.type AS dbservertype FROM db_users dbu, accounts a, DatabaseServers dbs WHERE dbu.account_id = a.id AND dbu.db_id = $databaseId AND dbu.dom_id = $domainId AND dbu.db_server_id = dbs.id"); };
  return (ref($arrayRef) =~ /ARRAY/) ? $arrayRef : [];
}

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

  my $sql;
  if ( PleskVersion::atLeast( 11, 1, 0 ) ) {
    $sql = "SELECT * FROM domain_aliases";
  } else {
    $sql = "SELECT * FROM domainaliases";
  }

  if (defined $domainId) {
    $sql .= " WHERE dom_id = '$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=$domainId ORDER BY id";
  }

  CACHED { return __getArrayOfHashes($sql); }
}

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

  if ( PleskVersion::atLeast(10, 0, 0) and not PleskVersion::isSmb() ) {
    my $sql = "SELECT * FROM ftp_users";

    if (defined $domainId) {
      $sql .= " WHERE dom_id=$domainId";
    }

    CACHED { return __getArrayOfHashes($sql); }
  } else {
    return [];
  }
}

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

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

  CACHED { return __getPairsAsHash($sql); }
}

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

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

  CACHED { return __getArrayOfHashes($sql); }
}

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

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

  CACHED { return __getHash($sql); }
}

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='$domainAsciiName'";

  CACHED { return __getScalar($sql); }
}

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='$domainId'";

  CACHED { return __getScalar($sql); }
}

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='$level' AND level_id='$owner_id'";

  CACHED { return __getArray($sql); }
}

sub getMailNameInfo {
  my ($mailNameId) = @_;
  
  my $sql = "SELECT * FROM mail WHERE id = $mailNameId";
  
  CACHED { return __getHash($sql); }
}

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

sub getSoftwarePackages {
  if ( PleskVersion::atLeast( 11, 0, 0 ) ) {
    return __getPairsAsHash("SELECT name, value FROM ServiceNodeEnvironment AS e INNER JOIN ServiceNodes AS n ON e.serviceNodeId = n.id WHERE n.ipAddress = 'local' AND section = 'componentsPackages'");
  } elsif ( PleskVersion::atLeast( 10, 4, 0 ) ) {
    return __getPairsAsHash("SELECT SUBSTRING(p.name, 17), p.value FROM ServiceNodeProperties AS p INNER JOIN ServiceNodes AS n ON p.serviceNodeId = n.id WHERE n.name = 'local' AND p.name LIKE 'server.packages.%'");
  } else {
    return __getPairsAsHash("SELECT name, version FROM Components WHERE version <> 'not_installed'");
  }
}

1;