- Fixed sql queries from previous merges

Marius Burkard

2016-01-04 18093fde721aeb57682b1d669c33fd0abd1ebfa2

- Fixed sql queries from previous merges

 install/lib/installer_base.lib.php

@@ -400,32 +400,32 @@
                            "riud",
                            "riud",
                            "",
                            ' . $conf['server_id'] . ',
                            ?,
                            0,
                            "'.$ip_type.'",
                            "'.$line.'",
                            ?,
                            ?,
                            "y",
                            "80,443"
                        )');
                        )', $conf['server_id'], $ip_type, $line);
                        $server_ip_id = $this->dbmaster->insertID();
                        $this->db->query('INSERT INTO server_ip (
                            server_php_id, sys_userid, sys_groupid, sys_perm_user, sys_perm_group,
                            sys_perm_other, server_id, client_id, ip_type, ip_address,
                            virtualhost, virtualhost_port
                        ) VALUES (
                            '.$server_ip_id.',
                            ?,
                            1,
                            1,
                            "riud",
                            "riud",
                            "",
                            ' . $conf['server_id'] . ',
                            ?,
                            0,
                            "'.$ip_type.'",
                            "'.$line.'",
                            ?,
                            ?,
                            "y",
                            "80,443"
                        )');
                        )', $server_ip_id, $conf['server_id'], $ip_type, $line);
                    } else {
                        $this->db->query('INSERT INTO server_ip (
                            sys_userid, sys_groupid, sys_perm_user, sys_perm_group,
@@ -437,13 +437,13 @@
                            "riud",
                            "riud",
                            "",
                            ' . $conf['server_id'] . ',
                            ?,
                            0,
                            "'.$ip_type.'",
                            "'.$line.'",
                            ?,
                            ?,
                            "y",
                            "80,443"
                        )');
                        )', $conf['server_id'], $ip_type, $line);
                    }
                }
            }

 interface/lib/classes/functions.inc.php

@@ -423,302 +423,12 @@
        global $app;
        // generate customer no.
        $customer_no = mt_rand(100000, 999999);
        while($app->db->queryOneRecord("SELECT client_id FROM client WHERE customer_no = '".$customer_no."'")){
        while($app->db->queryOneRecord("SELECT client_id FROM client WHERE customer_no = ?", $customer_no)) {
            $customer_no = mt_rand(100000, 999999);
        }
        
        return $customer_no;
    }
	
    public function generate_activation_code(){
		
        $activation_code = str_pad(mt_rand(0, 99999999), 8, '0', STR_PAD_LEFT);
		
        return $activation_code;
    }
	
    public function client_activate($client_id){
        global $app, $conf;
		
        if(!is_file(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php')) return false;
        include(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php');
		
        $context = stream_context_create(array(
            'ssl' => array(
                'verify_peer'       => false,
                'verify_peer_name'  => false,
            )
        ));

        $soap_client = new SoapClient(null, array('location' => $robot_conf['soap']['soap_location'],
                                    'uri'      => $robot_conf['soap']['soap_uri'],
                                    'trace' => 1,
                                    'exceptions' => 1,
                                    'stream_context' => $context));
	
	
        try {
            if($session_id = $soap_client->login($robot_conf['soap']['username'] , $robot_conf['soap']['password'])) {
                //echo 'Logged successfull. Session ID:'.$session_id.'<br />';
            }
            $error = '';
            $client_record = $soap_client->client_get($session_id, $client_id);
					
            $client_record['password'] = $this->password();
            if(trim($client_record['customer_no']) == '') $client_record['customer_no'] = $this->generate_customer_no();
            $client_record['username'] = 'c'.$client_record['customer_no'];
            //die($client_record['customer_no']);
            //$client_record['locked'] = 'n';
            $client_record['canceled'] = 'n';
            $soap_client->client_update($session_id, $client_id, 0, $client_record);
		
            $app->db->query("UPDATE client SET validation_status = 'accept', activation_code = '' WHERE client_id = ".$client_id);
			
            $activation_letter_filename = ISPC_ROOT_PATH.'/pdf/activation_letters/c'.$client_id.'-'.$client_record['activation_code'].'.pdf';
            if(is_file($activation_letter_filename)) unlink($activation_letter_filename);
		
            $webdetails['ispconfiguser'] = $client_record['username'];
            $webdetails['ispconfigpassword'] = $client_record['password'];
            $webdetails['customer_no'] = $client_record['customer_no'];
            $webdetails['contact'] = ($client_record['contact_firstname'] != ''? $client_record['contact_firstname'].' ' : '').$client_record['contact_name'];
            $webdetails['salutation_de'] = ($client_record['gender'] == 'f'? 'Frau' : 'Herr');
            $webdetails['salutation_en'] = ($client_record['gender'] == 'f'? 'Mrs.' : 'Mr.');
            $webdetails['ispconfigurl'] = 'http'.($_SERVER['HTTPS'] == 'on'? 's' : '').'://'.$_SERVER['HTTP_HOST'];
            $webdetails['signature_de'] = $robot_conf['textbaustein']['emailfooter'];
            $webdetails['signature_en'] = $robot_conf['textbaustein_en']['emailfooter'];
		
            if($error == ''){
                // send email with login details
                $invoice_client_settings = $app->db->queryOneRecord("SELECT * FROM invoice_client_settings WHERE client_id = ".intval($client_id));
                $company = $app->db->queryOneRecord("SELECT * FROM invoice_company WHERE invoice_company_id = ".$invoice_client_settings['invoice_company_id']);
				
                $subject = '['.$company['company_name_short'].'] Zugangsdaten zu unserem Kundeninterface / Login details for our customer interface';
			
                $app->uses('tpl');
                $tpl = new tpl;
                $tpl->newTemplate(ISPC_WEB_PATH."/client/templates/ispconfig_login.master");
                $tpl->setVar($webdetails);
                $message = $tpl->grab();
			
                if($robot_conf['production_mode']){
                    $app->functions->mail(trim($client_record['email']), $subject, $message, 'support@timmehosting.de', '', 'application/pdf', '', '', 'f.timme@timmehosting.de,hetzner@timmehosting.de', 'TimmeHosting.de Support');
				
                    $app->db->query("INSERT INTO `th_robot_message` (`sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `client_id`, `subject`, `message`, `message_sent_date`, `message_sent_tstamp`, `email_from`, `email_to`, `email_to_bcc`) VALUES(1, 1, 'riud', 'riud', '', ".intval($client_id).", '".$app->db->quote($subject)."', '".$app->db->quote($message)."', '".date('Y-m-d')."', ".time().", 'support@timmehosting.de', '".trim($client_record['email'])."', 'f.timme@timmehosting.de,hetzner@timmehosting.de')");
                }
            }
		
            if($soap_client->logout($session_id)) {
                //echo 'Logged out.<br />';
            }

        } catch (SoapFault $e) {
            //$error .= $client->__getLastResponse();
            $error .= 'SOAP Error: '.$e->getMessage();
        }
    }
	
    public function client_activation_failed($client){
        global $app, $conf;
		
        if(!is_file(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php')) return false;
        include(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php');
		
        $client_id = intval($client['client_id']);
        $webdetails['contact'] = ($client['contact_firstname'] != ''? $client['contact_firstname'].' ' : '').$client['contact_name'];
        $webdetails['salutation_de'] = ($client['gender'] == 'f'? 'Frau' : 'Herr');
        $webdetails['salutation_en'] = ($client['gender'] == 'f'? 'Mrs.' : 'Mr.');
        $webdetails['signature_de'] = $robot_conf['textbaustein']['emailfooter'];
        $webdetails['signature_en'] = $robot_conf['textbaustein_en']['emailfooter'];
		
		
        // send email with login details
        $invoice_client_settings = $app->db->queryOneRecord("SELECT * FROM invoice_client_settings WHERE client_id = ".intval($client_id));
        $company = $app->db->queryOneRecord("SELECT * FROM invoice_company WHERE invoice_company_id = ".$invoice_client_settings['invoice_company_id']);
        $subject = '['.$company['company_name_short'].'] Aktivierung Ihres Kundenaccounts fehlgeschlagen / Activation of your customer account failed';
			
        $app->uses('tpl');
        $tpl = new tpl;
        $tpl->newTemplate(ISPC_WEB_PATH."/client/templates/ispconfig_client_activation_failed.master");
        $tpl->setVar($webdetails);
        $message = $tpl->grab();
			
        if($robot_conf['production_mode']){
            $app->functions->mail(trim($client['email']), $subject, $message, 'support@timmehosting.de', '', 'application/pdf', '', '', 'f.timme@timmehosting.de,hetzner@timmehosting.de', 'TimmeHosting.de Support');
				
            $app->db->query("INSERT INTO `th_robot_message` (`sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `client_id`, `subject`, `message`, `message_sent_date`, `message_sent_tstamp`, `email_from`, `email_to`, `email_to_bcc`) VALUES(1, 1, 'riud', 'riud', '', ".intval($client_id).", '".$app->db->quote($subject)."', '".$app->db->quote($message)."', '".date('Y-m-d')."', ".time().", 'support@timmehosting.de', '".trim($client['email'])."', 'f.timme@timmehosting.de,hetzner@timmehosting.de')");
        }
    }
	
    public function client_review($client_id){
        global $app, $conf;
		
        if(!is_file(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php')) return false;
        include(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php');
		
        $context = stream_context_create(array(
            'ssl' => array(
                'verify_peer'       => false,
                'verify_peer_name'  => false,
            )
        ));

        $soap_client = new SoapClient(null, array('location' => $robot_conf['soap']['soap_location'],
                                    'uri'      => $robot_conf['soap']['soap_uri'],
                                    'trace' => 1,
                                    'exceptions' => 1,
                                    'stream_context' => $context));
									
        try {
            if($session_id = $soap_client->login($robot_conf['soap']['username'] , $robot_conf['soap']['password'])) {
                //echo 'Logged successfull. Session ID:'.$session_id.'<br />';
            }
            $error = '';
            $client_record = $soap_client->client_get($session_id, $client_id);
					
            if(trim($client_record['customer_no']) == ''){
                $client_record['customer_no'] = $this->generate_customer_no();
                $soap_client->client_update($session_id, $client_id, 0, $client_record);
            }
		
            $activation_code = $this->generate_activation_code();
            $app->db->query("UPDATE client SET activation_code = '".$activation_code."'".($client_record['validation_status'] != 'review'? ", validation_status = 'review'" : "")." WHERE client_id = ".$client_id);
		
            $webdetails['customer_no'] = $client_record['customer_no'];
            $webdetails['contact'] = ($client_record['contact_firstname'] != ''? $client_record['contact_firstname'].' ' : '').$client_record['contact_name'];
            $webdetails['salutation_de'] = ($client_record['gender'] == 'f'? 'Frau' : 'Herr');
            $webdetails['salutation_en'] = ($client_record['gender'] == 'f'? 'Mrs.' : 'Mr.');
            $webdetails['signature_de'] = $robot_conf['textbaustein']['emailfooter'];
            $webdetails['signature_en'] = $robot_conf['textbaustein_en']['emailfooter'];
            $webdetails['email'] = $client_record['email'];
            include ISPC_LIB_PATH.'/lang/'.strtolower($client_record['language']).'.lng';
            $webdetails['latest_activation_date'] = date($wb['conf_format_dateshort'], $client_record['created_at'] + 14 * 86400);
		
            if($error == ''){
                // send email with login details
                $invoice_client_settings = $app->db->queryOneRecord("SELECT * FROM invoice_client_settings WHERE client_id = ".intval($client_id));
                $company = $app->db->queryOneRecord("SELECT * FROM invoice_company WHERE invoice_company_id = ".$invoice_client_settings['invoice_company_id']);
				
                $subject = '['.$company['company_name_short'].'] Aktivierung Ihres Kundenkontos / Activation of your customer account';
                $webdetails['company_name_short'] = $company['company_name_short'];
			
                $app->uses('tpl');
                $tpl = new tpl;
                $tpl->newTemplate(ISPC_WEB_PATH."/client/templates/ispconfig_client_activation_email.master");
                $tpl->setVar($webdetails);
                $message = $tpl->grab();
			
                if($robot_conf['production_mode']){
                    $app->functions->mail(trim($client_record['email']), $subject, $message, 'support@timmehosting.de', '', 'application/pdf', '', '', 'f.timme@timmehosting.de,hetzner@timmehosting.de', 'TimmeHosting.de Support');
				
                    $app->db->query("INSERT INTO `th_robot_message` (`sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `client_id`, `subject`, `message`, `message_sent_date`, `message_sent_tstamp`, `email_from`, `email_to`, `email_to_bcc`) VALUES(1, 1, 'riud', 'riud', '', ".intval($client_id).", '".$app->db->quote($subject)."', '".$app->db->quote($message)."', '".date('Y-m-d')."', ".time().", 'support@timmehosting.de', '".trim($client_record['email'])."', 'f.timme@timmehosting.de,hetzner@timmehosting.de')");
                }
            }
		
            // create activation letter pdf
            $app->uses('pdf');
            $app->pdf->AliasNbPages();
            $app->pdf->createActivationLetter($client_id);

            $pdf_content = $app->pdf->Output('doc.pdf', 'S');

            $activation_letter_filename = ISPC_ROOT_PATH.'/pdf/activation_letters/c'.$client_id.'-'.$activation_code.'.pdf';
            file_put_contents($activation_letter_filename, $pdf_content);
		
            if(is_file($activation_letter_filename)){
                include(ISPC_WEB_PATH.'/billing/lib/onlinebrief24/Net/SFTP.php');
                $sftp = new Net_SFTP('api.letterei-onlinebrief.de');
                if (!$sftp->login($company['onlinebrief24_user'], $company['onlinebrief24_password'])) {
                    $error_msg = $app->lng('onlinebrief24_login_failed_txt');
                    $app->error($error_msg);
                }
                $upload_filename = ($company['onlinebrief24_print'] == 'coloured'? '1' : '0').'00'.($client_record['country'] == 'DE'? '1' : '0').'000000000-c'.$client_id.'-'.$activation_code.'.pdf';
                //die($upload_filename);
                $sftp->chdir('upload/api');
                $sftp->put($upload_filename, $activation_letter_filename, NET_SFTP_LOCAL_FILE);
            }
		
            if($soap_client->logout($session_id)) {
                //echo 'Logged out.<br />';
            }

        } catch (SoapFault $e) {
            //$error .= $client->__getLastResponse();
            $error .= 'SOAP Error: '.$e->getMessage();
        }
    }
	
    public function client_reject($client_id){
        global $app, $conf;
		
        if(!is_file(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php')) return false;
        include(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php');
		
        $context = stream_context_create(array(
            'ssl' => array(
                'verify_peer'       => false,
                'verify_peer_name'  => false,
            )
        ));

        $soap_client = new SoapClient(null, array('location' => $robot_conf['soap']['soap_location'],
                                    'uri'      => $robot_conf['soap']['soap_uri'],
                                    'trace' => 1,
                                    'exceptions' => 1,
                                    'stream_context' => $context));
		
        try {
            if($session_id = $soap_client->login($robot_conf['soap']['username'] , $robot_conf['soap']['password'])) {
                //echo 'Logged successfull. Session ID:'.$session_id.'<br />';
            }
            $error = '';
            $client_record = $soap_client->client_get($session_id, $client_id);
					
            $client_record['locked'] = 'y';
            $client_record['canceled'] = 'y';
            $soap_client->client_update($session_id, $client_id, 0, $client_record);
		
            $app->db->query("UPDATE client SET validation_status = 'reject', activation_code = '' WHERE client_id = ".$client_id);
            $app->db->query("DELETE FROM th_order WHERE client_id = ".$client_id);
			
            $activation_letter_filename = ISPC_ROOT_PATH.'/pdf/activation_letters/c'.$client_id.'-'.$client_record['activation_code'].'.pdf';
            if(is_file($activation_letter_filename)) unlink($activation_letter_filename);
		
            $webdetails['contact'] = ($client_record['contact_firstname'] != ''? $client_record['contact_firstname'].' ' : '').$client_record['contact_name'];
            $webdetails['salutation_de'] = ($client_record['gender'] == 'f'? 'Frau' : 'Herr');
            $webdetails['salutation_en'] = ($client_record['gender'] == 'f'? 'Mrs.' : 'Mr.');
            $webdetails['signature_de'] = $robot_conf['textbaustein']['emailfooter'];
            $webdetails['signature_en'] = $robot_conf['textbaustein_en']['emailfooter'];
		
            if($error == ''){
                // send email with login details
                $invoice_client_settings = $app->db->queryOneRecord("SELECT * FROM invoice_client_settings WHERE client_id = ".intval($client_id));
                $company = $app->db->queryOneRecord("SELECT * FROM invoice_company WHERE invoice_company_id = ".$invoice_client_settings['invoice_company_id']);
				
                $subject = '['.$company['company_name_short'].'] Sperrung Ihres Kundenaccounts / Suspension of your customer account';
			
                $app->uses('tpl');
                $tpl = new tpl;
                $tpl->newTemplate(ISPC_WEB_PATH."/client/templates/ispconfig_client_rejection.master");
                $tpl->setVar($webdetails);
                $message = $tpl->grab();
			
                if($robot_conf['production_mode']){
                    $app->functions->mail(trim($client_record['email']), $subject, $message, 'support@timmehosting.de', '', 'application/pdf', '', '', 'f.timme@timmehosting.de,hetzner@timmehosting.de', 'TimmeHosting.de Support');
				
                    $app->db->query("INSERT INTO `th_robot_message` (`sys_userid`, `sys_groupid`, `sys_perm_user`, `sys_perm_group`, `sys_perm_other`, `client_id`, `subject`, `message`, `message_sent_date`, `message_sent_tstamp`, `email_from`, `email_to`, `email_to_bcc`) VALUES(1, 1, 'riud', 'riud', '', ".intval($client_id).", '".$app->db->quote($subject)."', '".$app->db->quote($message)."', '".date('Y-m-d')."', ".time().", 'support@timmehosting.de', '".trim($client_record['email'])."', 'f.timme@timmehosting.de,hetzner@timmehosting.de')");
                }
            }
		
            if($soap_client->logout($session_id)) {
                //echo 'Logged out.<br />';
            }

        } catch (SoapFault $e) {
            //$error .= $client->__getLastResponse();
            $error .= 'SOAP Error: '.$e->getMessage();
        }
    }

}

?>

 interface/lib/classes/remote.d/client.inc.php

@@ -683,39 +683,6 @@
        
        return $returnval;
    }
	
    public function client_activate($session_id, $params){
        global $app;
        /*
        if (!$this->checkPerm($session_id, 'client_update')){
            throw new SoapFault('permission_denied', 'You do not have the permissions to access this function.');
            return false;
        }
        */
		
        if(!is_file(ISPC_WEB_PATH.'/robot/lib/robot_config.inc.php')){
            throw new SoapFault('permission_denied', 'You do not have the permissions to access this function.');
            return false;
        }
		
        $client = $app->db->queryOneRecord("SELECT * FROM client WHERE customer_no = '".$app->db->quote($params['customer_no'])."' AND email = '".$app->db->quote($params['email'])."' AND activation_code = '".$app->db->quote($params['activation_code'])."' AND validation_status = 'review'");
        //file_put_contents('/tmp/test.txt', "SELECT * FROM client WHERE customer_no = '".$app->db->quote($params['customer_no'])."' AND email = '".$app->db->quote($params['email'])."' AND activation_code = '".$app->db->quote($params['activation_code'])."' AND validation_status = 'review'");
		
        if(is_array($client) && !empty($client)){
            $client_id = intval($client['client_id']);
			
            $app->functions->client_activate($client_id);
	
            return true;
        } else {
            $client = $app->db->queryOneRecord("SELECT * FROM client WHERE email = '".$app->db->quote($params['email'])."' AND validation_status = 'review'");
            if(is_array($client) && !empty($client)){
                $app->functions->client_activation_failed($client);
            }
            return false;
        }
    }

}

?>

 interface/web/admin/directive_snippets_edit.php

@@ -53,7 +53,7 @@
        global $app, $conf;
        
        if($this->id > 0){
            $record = $app->db->queryOneRecord("SELECT * FROM directive_snippets WHERE directive_snippets_id = ".intval($this->id));
            $record = $app->db->queryOneRecord("SELECT * FROM directive_snippets WHERE directive_snippets_id = ?", $this->id);
            if($record['master_directive_snippets_id'] > 0){
                unset($app->tform->formDef["tabs"]['directive_snippets']['fields']['name'], $app->tform->formDef["tabs"]['directive_snippets']['fields']['type'], $app->tform->formDef["tabs"]['directive_snippets']['fields']['snippet'], $app->tform->formDef["tabs"]['directive_snippets']['fields']['required_php_snippets']);
            }
@@ -84,7 +84,7 @@
        global $app, $conf;
        
        if($this->id > 0){
            $record = $app->db->queryOneRecord("SELECT * FROM directive_snippets WHERE directive_snippets_id = ".intval($this->id));
            $record = $app->db->queryOneRecord("SELECT * FROM directive_snippets WHERE directive_snippets_id = ?", $this->id);
            if($record['master_directive_snippets_id'] > 0){
                unset($app->tform->formDef["tabs"]['directive_snippets']['fields']['name'], $app->tform->formDef["tabs"]['directive_snippets']['fields']['type'], $app->tform->formDef["tabs"]['directive_snippets']['fields']['snippet'], $app->tform->formDef["tabs"]['directive_snippets']['fields']['required_php_snippets']);
            }

 interface/web/sites/database_quota_stats.php

@@ -71,11 +71,11 @@

            if ($rec['used'] > 0) $rec['used'] = $app->functions->formatBytes($rec['used']);
        } else {
            $web_database = $app->db->queryOneRecord("SELECT * FROM web_database WHERE database_id = ".$rec[$this->idx_key]);
            $web_database = $app->db->queryOneRecord("SELECT * FROM web_database WHERE database_id = ?", $rec[$this->idx_key]);
            $rec['database'] = $rec['database_name'];
            $rec['server_name'] = $app->db->queryOneRecord("SELECT server_name FROM server WHERE server_id = ?", $web_database['server_id'])['server_name'];
            $sys_group = $app->db->queryOneRecord("SELECT * FROM sys_group WHERE groupid = ".$web_database['sys_groupid']);
            $client = $app->db->queryOneRecord("SELECT * FROM client WHERE client_id = ".$sys_group['client_id']);
            $sys_group = $app->db->queryOneRecord("SELECT * FROM sys_group WHERE groupid = ?", $web_database['sys_groupid']);
            $client = $app->db->queryOneRecord("SELECT * FROM client WHERE client_id = ?", $sys_group['client_id']);
            $rec['client'] = $client['username'];
            $rec['used'] = 'n/a';
            $rec['quota'] = 'n/a';