Користанням рнр-сценаріїв та створена база даних „osbb db з використанням системи управління реляційними базами даних Mysql, яка ідеально інтегрується з рнр

Вид материала

Документы

Подобный материал:

• Тема. Поняття про бази даних. Системи управління базами даних (субд) Мета, 35.04kb.
• План уроку: Порівняльна характеристика типів баз даних. Особливості реляційних баз, 83.01kb.
• Інтерфейс системи керування базами даних access. Створення бази даних. Таблиці. Запити, 156.05kb.
• Методичні рекомендації до вивчення теми 4 Державний стандарт освіти «Бази даних. Системи, 1008.1kb.
• Вступ. База даних у Access, 257.75kb.
• Системи управління базами даних (субд), 222.23kb.
• Лекція 21 "Інформатика та комп'ютерна техніка" Тема Бази даних та системи керування, 106.88kb.
• Урок 1 тема. Поняття про бази даних. Системи управління базами даних (субд) Мета: показати, 36.11kb.
• "Вычислительные системы" с применением программы pc virtual, 36.24kb.
• Затверджено, 191.24kb.

`password` = '" . $this->db->escape ( md5 ( $data ['password'] ) ) . "' ,

`status` = '" . ( int ) $data ['status'] . "' ,

`date_added` = NOW()" );

$customer_id = $this->db->getLastId ();


}

public function editCustomer($customer_id, $data) {


$this->db->query ( "UPDATE " . DB_PREFIX . "customer SET

firstname = '" . $this->db->escape ( $data ['firstname'] ) . "',

lastname = '" . $this->db->escape ( $data ['lastname'] ) . "',

`middlename` = '" . $this->db->escape ( $data ['middlename'] ) . "' ,

`email` = '" . $this->db->escape ( $data ['email'] ) . "' ,

`house_id` = '" . $this->db->escape ( $data ['house_id'] ) . "' ,

`flat_id` = '" . $this->db->escape ( $data ['flat_id'] ) . "' ,

`telephone` = '" . $this->db->escape ( $data ['telephone'] ) . "' ,

`fax` = '" . $this->db->escape ( $data ['fax'] ) . "' ,

`newsletter` = '" . ( int ) $data ['newsletter'] . "' ,

`status` = '" . ( int ) $data ['status'] . "'

WHERE customer_id = '" . ( int ) $customer_id . "'" );

if ($data ['password']) {

$this->db->query ( "UPDATE " . DB_PREFIX . "customer SET password = '" . $this->db->escape ( md5 ( $data ['password'] ) ) . "' WHERE customer_id = '" . ( int ) $customer_id . "'" );

}


}

public function editToken($customer_id, $token) {

$this->db->query ( "UPDATE " . DB_PREFIX . "customer SET token = '" . $this->db->escape ( $token ) . "' WHERE customer_id = '" . ( int ) $customer_id . "'" );

}

public function deleteCustomer($customer_id) {

$this->db->query ( "DELETE FROM " . DB_PREFIX . "customer WHERE customer_id = '" . ( int ) $customer_id . "'" );

$this->db->query ( "DELETE FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . ( int ) $customer_id . "'" );

$this->db->query ( "DELETE FROM " . DB_PREFIX . "customer_ip WHERE customer_id = '" . ( int ) $customer_id . "'" );

}

public function getCustomer($customer_id){


$query = $this->db->query ( "SELECT DISTINCT * FROM " . DB_PREFIX . "customer WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->row;

}

public function getCustomerByEmail($email) {

$query = $this->db->query ( "SELECT DISTINCT * FROM " . DB_PREFIX . "customer WHERE email = '" . $this->db->escape ( $email ) . "'" );

return $query->row;

}

public function getCustomers( $data = array() ){


/*

$sql = "SELECT *, CONCAT(c.firstname, ' ', c.lastname) AS name, cg.name AS customer_group

FROM " . DB_PREFIX . "customer c

LEFT JOIN " . DB_PREFIX . "customer_group cg ON (c.customer_group_id = cg.customer_group_id)";

*/

$sql = "SELECT *, CONCAT(firstname, ' ', lastname) AS name FROM " . DB_PREFIX . "customer ";

$implode = array ();

if (! empty ( $data ['filter_name'] )) {

$implode [] = "LCASE(CONCAT(c.firstname, ' ', c.lastname)) LIKE '" . $this->db->escape ( utf8_strtolower ( $data ['filter_name'] ) ) . "%'";

}

if (! empty ( $data ['filter_email'] )) {

$implode [] = "LCASE(c.email) LIKE '" . $this->db->escape ( utf8_strtolower ( $data ['filter_email'] ) ) . "%'";

}

if (! empty ( $data ['filter_customer_group_id'] )) {

$implode [] = "cg.customer_group_id = '" . $this->db->escape ( $data ['filter_customer_group_id'] ) . "'";

}

if (isset ( $data ['filter_status'] ) && ! is_null ( $data ['filter_status'] )) {

$implode [] = "c.status = '" . ( int ) $data ['filter_status'] . "'";

}

if (isset ( $data ['filter_approved'] ) && ! is_null ( $data ['filter_approved'] )) {

$implode [] = "c.approved = '" . ( int ) $data ['filter_approved'] . "'";

}

if (! empty ( $data ['filter_ip'] )) {

$implode [] = "c.customer_id IN (SELECT customer_id FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape ( $data ['filter_ip'] ) . "')";

}

if (! empty ( $data ['filter_date_added'] )) {

$implode [] = "DATE(c.date_added) = DATE('" . $this->db->escape ( $data ['filter_date_added'] ) . "')";

}

if ($implode) {

$sql .= " WHERE " . implode ( " AND ", $implode );

}

$sort_data = array ('name', 'c.email', 'customer_group', 'c.status', 'c.ip', 'c.date_added' );

if (isset ( $data ['sort'] ) && in_array ( $data ['sort'], $sort_data )) {

$sql .= " ORDER BY " . $data ['sort'];

} else {

$sql .= " ORDER BY name";

}

if (isset ( $data ['order'] ) && ($data ['order'] == 'DESC')) {

$sql .= " DESC";

} else {

$sql .= " ASC";

}

if (isset ( $data ['start'] ) || isset ( $data ['limit'] )) {

if ($data ['start'] < 0) {

$data ['start'] = 0;

}

if ($data ['limit'] < 1) {

$data ['limit'] = 20;

}

$sql .= " LIMIT " . ( int ) $data ['start'] . "," . ( int ) $data ['limit'];

}

$query = $this->db->query ( $sql );

return $query->rows;

}

public function approve($customer_id) {

$customer_info = $this->getCustomer ( $customer_id );

if ($customer_info) {

$this->db->query ( "UPDATE " . DB_PREFIX . "customer SET approved = '1' WHERE customer_id = '" . ( int ) $customer_id . "'" );

$this->load->language ( 'mail/customer' );

$this->load->model ( 'setting/store' );

$store_info = $this->model_setting_store->getStore ( $customer_info ['store_id'] );

if ($store_info) {

$store_name = $store_info ['name'];

$store_url = $store_info ['url'] . 'index.php?route=account/login';

} else {

$store_name = $this->config->get ( 'config_name' );

$store_url = HTTP_CATALOG . 'index.php?route=account/login';

}

$message = sprintf ( $this->language->get ( 'text_approve_welcome' ), $store_name ) . "\n\n";

$message .= $this->language->get ( 'text_approve_login' ) . "\n";

$message .= $store_url . "\n\n";

$message .= $this->language->get ( 'text_approve_services' ) . "\n\n";

$message .= $this->language->get ( 'text_approve_thanks' ) . "\n";

$message .= $store_name;

$mail = new Mail ();

$mail->protocol = $this->config->get ( 'config_mail_protocol' );

$mail->parameter = $this->config->get ( 'config_mail_parameter' );

$mail->hostname = $this->config->get ( 'config_smtp_host' );

$mail->username = $this->config->get ( 'config_smtp_username' );

$mail->password = $this->config->get ( 'config_smtp_password' );

$mail->port = $this->config->get ( 'config_smtp_port' );

$mail->timeout = $this->config->get ( 'config_smtp_timeout' );

$mail->setTo ( $customer_info ['email'] );

$mail->setFrom ( $this->config->get ( 'config_email' ) );

$mail->setSender ( $store_name );

$mail->setSubject ( sprintf ( $this->language->get ( 'text_approve_subject' ), $store_name ) );

$mail->setText ( html_entity_decode ( $message, ENT_QUOTES, 'UTF-8' ) );

$mail->send ();

}

}

public function getCustomersByNewsletter() {

$query = $this->db->query ( "SELECT * FROM " . DB_PREFIX . "customer WHERE newsletter = '1' ORDER BY firstname, lastname, email" );

return $query->rows;

}

public function getCustomersByCustomerGroupId($customer_group_id) {

$query = $this->db->query ( "SELECT * FROM " . DB_PREFIX . "customer WHERE customer_group_id = '" . ( int ) $customer_group_id . "' ORDER BY firstname, lastname, email" );

return $query->rows;

}

public function getCustomersByProduct($product_id) {

if ($product_id) {

$query = $this->db->query ( "SELECT DISTINCT `email` FROM `" . DB_PREFIX . "order` o LEFT JOIN " . DB_PREFIX . "order_product op ON (o.order_id = op.order_id) WHERE op.product_id = '" . ( int ) $product_id . "' AND o.order_status_id <> '0'" );

return $query->rows;

} else {

return array ();

}

}

public function getAddress($address_id) {

$address_query = $this->db->query ( "SELECT * FROM " . DB_PREFIX . "address WHERE address_id = '" . ( int ) $address_id . "'" );

$default_query = $this->db->query ( "SELECT address_id FROM " . DB_PREFIX . "customer WHERE customer_id = '" . ( int ) $address_query->row ['customer_id'] . "'" );

if ($address_query->num_rows) {

$country_query = $this->db->query ( "SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . ( int ) $address_query->row ['country_id'] . "'" );

if ($country_query->num_rows) {

$country = $country_query->row ['name'];

$iso_code_2 = $country_query->row ['iso_code_2'];

$iso_code_3 = $country_query->row ['iso_code_3'];

$address_format = $country_query->row ['address_format'];

} else {

$country = '';

$iso_code_2 = '';

$iso_code_3 = '';

$address_format = '';

}

$zone_query = $this->db->query ( "SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . ( int ) $address_query->row ['zone_id'] . "'" );

if ($zone_query->num_rows) {

$zone = $zone_query->row ['name'];

$code = $zone_query->row ['code'];

} else {

$zone = '';

$code = '';

}

return array ('address_id' => $address_query->row ['address_id'], 'customer_id' => $address_query->row ['customer_id'], 'firstname' => $address_query->row ['firstname'], 'lastname' => $address_query->row ['lastname'], 'company' => $address_query->row ['company'], 'address_1' => $address_query->row ['address_1'], 'address_2' => $address_query->row ['address_2'], 'postcode' => $address_query->row ['postcode'], 'city' => $address_query->row ['city'], 'zone_id' => $address_query->row ['zone_id'], 'zone' => $zone, 'zone_code' => $code, 'country_id' => $address_query->row ['country_id'], 'country' => $country, 'iso_code_2' => $iso_code_2, 'iso_code_3' => $iso_code_3, 'address_format' => $address_format, 'default' => ($default_query->row ['address_id'] == $address_query->row ['address_id']) ? true : false );

}

}

public function getAddresses($customer_id) {

$address_data = array ();

$query = $this->db->query ( "SELECT address_id FROM " . DB_PREFIX . "address WHERE customer_id = '" . ( int ) $customer_id . "'" );

foreach ( $query->rows as $result ) {

$address_info = $this->getAddress ( $result ['address_id'] );

if ($address_info) {

$address_data [] = $address_info;

}

}

return $address_data;

}

public function getTotalCustomers($data = array()) {


$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer";

$implode = array ();

if (! empty ( $data ['filter_name'] )) {

$implode[] = "LCASE(CONCAT(firstname, ' ', lastname)) LIKE '" . $this->db->escape ( utf8_strtolower ( $data ['filter_name'] ) ) . "%'";

}

if (! empty ( $data ['filter_email'] )) {

$implode[] = "LCASE(email) LIKE '" . $this->db->escape ( utf8_strtolower ( $data ['filter_email'] ) ) . "%'";

}

if (! empty ( $data ['filter_customer_group_id'] )) {

$implode [] = "customer_group_id = '" . $this->db->escape ( $data ['filter_customer_group_id'] ) . "'";

}

if (isset ( $data ['filter_status'] ) && ! is_null ( $data ['filter_status'] )) {

$implode [] = "status = '" . ( int ) $data ['filter_status'] . "'";

}

if (isset ( $data ['filter_approved'] ) && ! is_null ( $data ['filter_approved'] )) {

$implode [] = "approved = '" . ( int ) $data ['filter_approved'] . "'";

}

if (! empty ( $data ['filter_date_added'] )) {

$implode [] = "DATE(date_added) = DATE('" . $this->db->escape ( $data ['filter_date_added'] ) . "')";

}

if ($implode) {

$sql .= " WHERE " . implode ( " AND ", $implode );

}

$query = $this->db->query ( $sql );

return $query->row ['total'];

}

public function getTotalCustomersAwaitingApproval() {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE status = '0' OR approved = '0'" );

return $query->row ['total'];

}

public function getTotalAddressesByCustomerId($customer_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "address WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->row ['total'];

}

public function getTotalAddressesByCountryId($country_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "address WHERE country_id = '" . ( int ) $country_id . "'" );

return $query->row ['total'];

}

public function getTotalAddressesByZoneId($zone_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "address WHERE zone_id = '" . ( int ) $zone_id . "'" );

return $query->row ['total'];

}

public function getTotalCustomersByCustomerGroupId($customer_group_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer WHERE customer_group_id = '" . ( int ) $customer_group_id . "'" );

return $query->row ['total'];

}

public function addTransaction($customer_id, $description = '', $amount = '', $order_id = 0) {

$customer_info = $this->getCustomer ( $customer_id );

if ($customer_info) {

$this->db->query ( "INSERT INTO " . DB_PREFIX . "customer_transaction SET customer_id = '" . ( int ) $customer_id . "', order_id = '" . ( int ) $order_id . "', description = '" . $this->db->escape ( $description ) . "', amount = '" . ( float ) $amount . "', date_added = NOW()" );

$this->language->load ( 'mail/customer' );

if ($customer_info ['store_id']) {

$this->load->model ( 'setting/store' );

$store_info = $this->model_setting_store->getStore ( $customer_info ['store_id'] );

if ($store_info) {

$store_name = $store_info ['store_name'];

} else {

$store_name = $this->config->get ( 'config_name' );

}

} else {

$store_name = $this->config->get ( 'config_name' );

}

$message = sprintf ( $this->language->get ( 'text_transaction_received' ), $this->currency->format ( $amount, $this->config->get ( 'config_currency' ) ) ) . "\n\n";

$message .= sprintf ( $this->language->get ( 'text_transaction_total' ), $this->currency->format ( $this->getTransactionTotal ( $customer_id ) ) );

$mail = new Mail ();

$mail->protocol = $this->config->get ( 'config_mail_protocol' );

$mail->parameter = $this->config->get ( 'config_mail_parameter' );

$mail->hostname = $this->config->get ( 'config_smtp_host' );

$mail->username = $this->config->get ( 'config_smtp_username' );

$mail->password = $this->config->get ( 'config_smtp_password' );

$mail->port = $this->config->get ( 'config_smtp_port' );

$mail->timeout = $this->config->get ( 'config_smtp_timeout' );

$mail->setTo ( $customer_info ['email'] );

$mail->setFrom ( $this->config->get ( 'config_email' ) );

$mail->setSender ( $store_name );

$mail->setSubject ( sprintf ( $this->language->get ( 'text_transaction_subject' ), $this->config->get ( 'config_name' ) ) );

$mail->setText ( $message );

$mail->send ();

}

}

public function deleteTransaction($order_id) {

$this->db->query ( "DELETE FROM " . DB_PREFIX . "customer_transaction WHERE order_id = '" . ( int ) $order_id . "'" );

}

public function getTransactions($customer_id, $start = 0, $limit = 10) {

$query = $this->db->query ( "SELECT * FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . ( int ) $customer_id . "' ORDER BY date_added DESC LIMIT " . ( int ) $start . "," . ( int ) $limit );

return $query->rows;

}

public function getTotalTransactions($customer_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->row ['total'];

}

public function getTransactionTotal($customer_id) {

$query = $this->db->query ( "SELECT SUM(amount) AS total FROM " . DB_PREFIX . "customer_transaction WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->row ['total'];

}

public function getTotalTransactionsByOrderId($order_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_transaction WHERE order_id = '" . ( int ) $order_id . "'" );

return $query->row ['total'];

}

public function addReward($customer_id, $description = '', $points = '', $order_id = 0) {

$customer_info = $this->getCustomer ( $customer_id );

if ($customer_info) {

$this->db->query ( "INSERT INTO " . DB_PREFIX . "customer_reward SET customer_id = '" . ( int ) $customer_id . "', order_id = '" . ( int ) $order_id . "', points = '" . ( int ) $points . "', description = '" . $this->db->escape ( $description ) . "', date_added = NOW()" );

$this->language->load ( 'mail/customer' );

if ($order_id) {

$this->load->model ( 'sale/order' );

$order_info = $this->model_sale_order->getOrder ( $order_id );

if ($order_info) {

$store_name = $order_info ['store_name'];

} else {

$store_name = $this->config->get ( 'config_name' );

}

} else {

$store_name = $this->config->get ( 'config_name' );

}

$message = sprintf ( $this->language->get ( 'text_reward_received' ), $points ) . "\n\n";

$message .= sprintf ( $this->language->get ( 'text_reward_total' ), $this->getRewardTotal ( $customer_id ) );

$mail = new Mail ();

$mail->protocol = $this->config->get ( 'config_mail_protocol' );

$mail->parameter = $this->config->get ( 'config_mail_parameter' );

$mail->hostname = $this->config->get ( 'config_smtp_host' );

$mail->username = $this->config->get ( 'config_smtp_username' );

$mail->password = $this->config->get ( 'config_smtp_password' );

$mail->port = $this->config->get ( 'config_smtp_port' );

$mail->timeout = $this->config->get ( 'config_smtp_timeout' );

$mail->setTo ( $customer_info ['email'] );

$mail->setFrom ( $this->config->get ( 'config_email' ) );

$mail->setSender ( $store_name );

$mail->setSubject ( sprintf ( $this->language->get ( 'text_reward_subject' ), $store_name ) );

$mail->setText ( $message );

$mail->send ();

}

}

public function deleteReward($order_id) {

$this->db->query ( "DELETE FROM " . DB_PREFIX . "customer_reward WHERE order_id = '" . ( int ) $order_id . "'" );

}

public function getRewards($customer_id, $start = 0, $limit = 10) {

$query = $this->db->query ( "SELECT * FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . ( int ) $customer_id . "' ORDER BY date_added DESC LIMIT " . ( int ) $start . "," . ( int ) $limit );

return $query->rows;

}

public function getTotalRewards($customer_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->row ['total'];

}

public function getRewardTotal($customer_id) {

$query = $this->db->query ( "SELECT SUM(points) AS total FROM " . DB_PREFIX . "customer_reward WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->row ['total'];

}

public function getTotalCustomerRewardsByOrderId($order_id) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_reward WHERE order_id = '" . ( int ) $order_id . "'" );

return $query->row ['total'];

}

public function getIpsByCustomerId($customer_id) {

$query = $this->db->query ( "SELECT * FROM " . DB_PREFIX . "customer_ip WHERE customer_id = '" . ( int ) $customer_id . "'" );

return $query->rows;

}

public function getTotalCustomersByIp($ip) {

$query = $this->db->query ( "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "customer_ip WHERE ip = '" . $this->db->escape ( $ip ) . "'" );

return $query->row ['total'];

}


}

3. Таблиці БД «osbb_db»