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) = @_;
  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 "
            . "FROM smb_users INNER JOIN smb_roles ON smb_users.roleId = smb_roles.id "
            . "WHERE smb_users.login = '$user'";
  my $hash = __getHash($sql);
  my $passwordType = ((PleskVersion::atLeast( 11, 0, 0 ) and isSecurePasswords()) ? "sym" : "plain");
  $hash->{'passwordType'} = $passwordType;
  $hash->{'isBase64'} = '0' unless $passwordType eq 'plain';
  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 selectSmbRoles {
  CACHED { return __getArrayOfHashes('SELECT id, name, isBuiltIn FROM smb_roles'); }
}

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='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;
}

sub isKavGroupsTable {
  return __tableExists('module_kav_groups');
}

sub isKavParametersTable {
  return __tableExists('module_kav_parameters');
}

sub getKavGroupSettings {
  return __getScalar("SELECT settings FROM module_kav_groups WHERE name='server_default'");
}

sub getKavParameters {
  return __getPairsAsHash("SELECT param, val FROM module_kav_parameters WHERE param='allow_custom_settings' OR param='server_scan_direction'"); }

# 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 {
  return __getPairsAsHash('SELECT param, val FROM misc');
}

sub isSecurePasswords {
  CACHED { 
    my $val = __getScalar("SELECT val FROM misc WHERE param = 'secure_passwords'");
    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 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 {
  return __getArrayOfHashes('SELECT id, host, port, type, admin_login, admin_password FROM DatabaseServers');
}

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 getSkinName {
  my ($id) = @_;
  return __getScalar("SELECT `name` FROM `Skins` where `id`='$id'");
}

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 = '$mailname'");
}

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

sub getDomain71Ptr {
  my ($domainName) = @_;
  return __getHash("SELECT * FROM domains WHERE displayName = '$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 = '$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);
  }
  
  if ( PleskVersion::atLeast( 7, 1, 0 ) ) {
    return getDomain71Ptr($domainName);
  }

  return getDomainOldPtr($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 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 __getArrayOfValues($sql);
}

# ppsmbe
sub getDomainMailsSmb {
  my ($domainId) = @_;
  return __getArrayOfRows("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 m.dom_id=$domainId AND smb_mailLists.id IS NULL ORDER BY m.mail_name");
}

# not ppsmbe
sub getDomainMails {
  my ($domainId) = @_;
  return __getArrayOfRows("SELECT m.id, a.password, a.type FROM mail m LEFT JOIN accounts a ON m.account_id=a.id WHERE m.dom_id=$domainId ORDER BY m.mail_name");
}

# plesk 2.5-5.5
sub getDomainMails55 {
  my ($domainId) = @_;
  return __getArrayOfRows("SELECT id, password, 'plain' FROM mail WHERE dom_id=$domainId ORDER BY mail_name");
}

sub getDomainMaillistsSmb {
  my ($domainId) = @_;
  return __getArrayOfRows("SELECT m.`mailListId`, m.`name`, 0 FROM `smb_mailLists` m, `domains` d WHERE d.`id`=$domainId AND m.`name` LIKE CONCAT('%".'@'."',d.`displayName`)");
}

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

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) = @_;
  return __getScalar("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 = '$userLogin'");
}

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) = @_;
  CACHED { 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 getDomainNameById {
  my ($domainId) = @_;
  CACHED { return __getScalar("SELECT displayName 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 PleskVesrion::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;
}

1;