public static function getInstance()
{
if (!isset(self::$instance)) {
self::$instance = new PDOQuery(_DB_SERVER_NAME_, _DB_USER_NAME_, _DB_PASSWORD_, _DB_NAME_);
}
return self::$instance;
}
public function getSalesInfo()
{
$res = Sales::getInstance()->getSalesByCategory(intval($_GET['active']), intval($_GET['records']), intval($_GET['id_shop_session']));
while ($row = PDOQuery::getInstance()->next_row($res)) {
$this->sale_info[] = array('quantity' => $row['quantity'], 'total' => $row['total'], 'category' => explode(',', $row['category']), 'category_color' => explode(',', $row['category_color']), 'session_date' => date('d/m/Y', $row['session_date']), 'total_cash' => $this->getTotalBySaleType(1, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'cash_movements' => $this->getOperationsQty(1, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_credit' => $this->getTotalBySaleType(2, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'credit_movements' => $this->getOperationsQty(2, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_check' => $this->getTotalBySaleType(3, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'check_movements' => $this->getOperationsQty(3, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_other' => $this->getTotalBySaleType(4, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'other_movements' => $this->getOperationsQty(4, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_refund' => $this->getTotalBySaleType(5, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'refund_movements' => $this->getOperationsQty(5, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_cancellation' => $this->getTotalBySaleType(6, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'cancellation_movements' => $this->getOperationsQty(6, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_replacement' => $this->getTotalBySaleType(7, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'replacement_movements' => $this->getOperationsQty(7, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_otherC' => $this->getTotalBySaleType(8, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'otherC_movements' => $this->getOperationsQty(8, explode(',', $row['id_payment_type']), explode(',', $row['amount'])), 'total_invoice' => $this->getTotalBySaleType(1, explode(',', $row['invoice']), explode(',', $row['amount'])), 'invoice_movements' => $this->getOperationsQty(1, explode(',', $row['invoice']), explode(',', $row['amount'])), 'total_ticket' => $this->getTotalBySaleType(0, explode(',', $row['invoice']), explode(',', $row['amount'])), 'ticket_movements' => $this->getOperationsQty(0, explode(',', $row['invoice']), explode(',', $row['amount'])));
}
$this->context->smarty->assign("sale_info", $this->sale_info);
$this->context->smarty->assign("pagination", Sales::getInstance()->pagination_render);
}
public function relatedCategoriesQuery()
{
$categories = Category::getInstance();
$res = $categories->getRelatedCategories($_GET['id_category']);
while ($row = PDOQuery::getInstance()->next_row($res)) {
$query .= $row['id_children_category'] . ',';
}
return rtrim($query, ",");
}
public function setCategoriesInSmarty()
{
$product = Category::getInstance();
$res = $product->getCategories();
while ($row = PDOQuery::getInstance()->next_row($res)) {
$this->smarty_categories[] = array('id_category' => $row['id_category'], 'category_name' => $row['category_name'], 'color' => $row['color']);
}
$this->context->smarty->assign("categoryList", $this->smarty_categories);
}
public function getSalesInfo()
{
$records = $this->getRecordsToShow();
$res = Sales::getInstance()->getSessionSales(intval($_GET['active']), $records, intval($_GET['id_shop_session']));
while ($row = PDOQuery::getInstance()->next_row($res)) {
$this->summary_products[] = array('amount' => $row['amount'], 'employee_name' => $row['employee_name'], 'reference' => $row['reference'], 'price' => $row['price'], 'quantity' => $row['quantity'], 'product' => $row['product'], 'related_product' => $row['related_product'], 'related_reference' => $row['related_reference'], 'date_sale' => date('H:i ', $row['date_sale']), 'session_date' => date('d/m/Y', $row['session_date']), 'note' => htmlspecialchars($row['note']), 'payment_type' => $row['payment'], 'invoice' => $row['invoice'], 'color' => $row['color'], 'sale_type' => $row['id_sale_type'], 'id_payment_type' => $row['id_payment_type'], 'id_sale' => $row['id_sale'], 'position' => $row['position']);
}
$this->context->smarty->assign("summary_products", $this->summary_products);
$this->context->smarty->assign("pagination", Sales::getInstance()->pagination_render);
}
public function getShopMessages()
{
$db = PDOQuery::getInstance();
$db->connect();
$sql = "SELECT \n\t\t\t\tmessage, date, viewed\n\t\t\tFROM \tshop_messages\n\t\t\tWHERE id_shop =?";
$stmt = $db->prepareQuery($sql);
$stmt->execute(array($_SESSION['id_shop']));
$db->close();
return $stmt;
}
public function getProducts($searchQuery = null)
{
$product = new Product();
$res = $product->getProductsByIdShop(intval($_GET['records']));
while ($row = PDOQuery::getInstance()->next_row($res)) {
$this->smarty_products[] = array('id_product' => $row['id_product'], 'product' => $row['product'], 'reference' => $row['reference'], 'stock_available' => $row['stock_available'], 'price' => $row['price']);
}
$this->context->smarty->assign("productList", $this->smarty_products);
$this->context->smarty->assign("pagination", $product->pagination_render);
}
public function getSalesHistory()
{
$res = Sales::getInstance()->getTotalSaleList(intval($_GET['orderBy']), intval($_GET['records']));
$odd = true;
while ($row = PDOQuery::getInstance()->next_row($res)) {
$odd = $odd == true ? false : true;
$this->sale_info[] = array('total' => $row['total'], 'id_shop_session' => $row['id_shop_session'], 'date' => date('d/m/Y - H:i', $row['date']), 'odd' => $odd);
}
$this->context->smarty->assign("sale_info", $this->sale_info);
$this->context->smarty->assign("pagination", Sales::getInstance()->pagination_render);
}
private function setMessage()
{
$db = PDOQuery::getInstance();
$db->connect();
$sql = "UPDATE sales \n\t\t\tSET note = :message\n\t\t\tWHERE id_sale= :id";
$stmt = $db->prepareQuery($sql);
$stmt->bindParam(':message', $this->message, PDO::PARAM_STR);
$stmt->bindParam(':id', $this->id, PDO::PARAM_INT);
$stmt->execute();
var_dump($stmt->errorInfo());
}
public function getManufacturerByCategoryID()
{
if (!isset($_GET['id_category']) || !is_numeric($_GET['id_category'])) {
return false;
}
$categories = new Product();
$res = $categories->getManufacturerByCategoryID($_GET['id_category']);
while ($row = PDOQuery::getInstance()->next_row($res)) {
//$category_list .= '<li class="manufacturer-list"><a href="product_sale.php?id_manufacturer='.$row['id_manufacturer'].'">'.$row['manufacturer'].'</a></li>';
$category_list .= '{"id_manufacturer": "' . $row['id_manufacturer'] . '", "manufacturer": "' . $row['manufacturer'] . '"},';
}
$category_list = '{"success": [' . rtrim($category_list, ',') . ']}';
echo $category_list;
}
public static function getCategoryName()
{
if (isset($_GET['id_category']) && is_numeric($_GET['id_category'])) {
$db = PDOQuery::getInstance();
$db->connect();
$id_category = intval($_GET['id_category']);
$sql = "SELECT category_name as category\n\t\t\t\t\tFROM \tcategory_lang\n\t\t\t\t\tWHERE id_category=:id_category\n\t\t\t\t\tAND id_lang=" . _ID_LANG_;
$stmt = $db->prepareQuery($sql);
$stmt->bindParam(':id_category', $id_category, PDO::PARAM_INT);
$stmt->execute();
$db->close();
return $db->next_row($stmt)['category'];
}
return false;
}
public function getCurrentMonthSalesInformation()
{
$month_info = array();
$sales = Sales::getInstance();
$res = $sales->getTotalMonthSales();
$operations = 0;
while ($row = PDOQuery::getInstance()->next_row($res)) {
if (_CURRENT_MONTH_ == date('n', $row['date_sale'])) {
$operations++;
$total += $row['amount'];
}
}
$month_info['operations'] = $operations > 0 ? $operations : 0;
$month_info['total'] = $operations > 0 ? $total : 0;
return $month_info;
}
/**
* Удаление комментария:
*/
public static function RemoveComment($id)
{
$comment = self::GetComment($id);
$kvs = KVS::getInstance();
$cache = KVS::getInstance();
if ($comment) {
if ($kvs->exists('ControlModel', 'timeblock', $comment['ip'])) {
$kvs->set('ControlModel', 'timeban', $comment['ip'], true);
$kvs->expire('ControlModel', 'timeban', $comment['ip'], 60 * 60);
}
if ($kvs->exists('ControlModel', 'timeban', $comment['ip'])) {
$life = $kvs->lifetime('ControlModel', 'timeban', $comment['ip']);
$kvs->expire('ControlModel', 'timeban', $comment['ip'], $life + 60 * 60);
}
$kvs->set('ControlModel', 'timeblock', $comment['ip'], true);
$kvs->expire('ControlModel', 'timeblock', $comment['ip'], 60 * 60);
$dbh = PDOQuery::getInstance();
$dbh->update_insecure('1chan_post', array('comments' => '`comments`-1'), 'id = ' . $dbh->q($comment['post_id']), 1, true);
$cache->listRemove(__CLASS__, null, 'lastComments', $id);
EventModel::getInstance()->Broadcast('remove_comment', array('id' => $id, 'post_id' => $comment['post_id']));
return $dbh->delete('1chan_comment', 'id = ' . $dbh->q($id), 1);
}
return false;
}
public function delete(\Core\Mapped $object)
{
if ($object->id < 1) {
throw new \Core\StorageError("Trying to delete non-existent object.");
}
$query = new PDOQuery(PDOQuery::Delete, $this->_default_table());
$sth = $this->_backend->prepare($query->sql());
$sth->execute(array(':id' => $object->id));
$object->id = '-1';
return $this;
}
public function processcancellation()
{
$db = PDOQuery::getInstance();
$db->connect();
$query = $this->getcancellationType();
$sqlIN = "INSERT INTO sales \t\n\t\t\t\t\t\t(id_product, id_product_related, id_category, id_employee, id_payment_type, id_sale_type, id_shop_session, id_shop, quantity, \n\t\t\t\t\t\t price ,amount, invoice, date_sale, note, position)\n\t\t\t\t\tVALUES\n\t\t\t\t\t\t(:id_product, :id_product_related, :id_category, :id_employee, :id_payment_type, \n\t\t\t\t\t\t\t:id_sale_type, :id_shop_session, :id_shop,\n\t\t\t\t\t\t\t:quantity, :price , :amount,\n\t\t\t\t\t\t\t:invoice, " . time() . ", :note, :product_position)";
$stmt1 = $db->prepareQuery($sqlIN);
$stmt1->execute(array(':id_product' => $this->id_product, ':id_product_related' => $this->id_product_related, ':id_category' => $this->id_category, ':id_employee' => $this->id_employee, ':id_payment_type' => $this->id_payment_type, ':id_sale_type' => $this->id_sale_type, ':id_shop_session' => $this->shop_session_id, ':id_shop' => $_SESSION['id_shop'], ':quantity' => $this->quantity, ':price' => $this->price, ':amount' => $this->amount, ':invoice' => $this->invoice, ':note' => $this->note, ':product_position' => Product::getProductPosition($this->shop_session_id)));
$sqlUP = "UPDATE stock_available\n\t\t\t\t\t" . $query . "\n\t\t\t\t\tWHERE id_shop = :id_shop\n\t\t\t\t\tAND id_product IN (:id_product, :id_product_related)";
$stmt2 = $db->prepareQuery($sqlUP);
$stmt2->execute(array(':id_shop' => $this->id_shop, ':id_product' => $this->id_product, ':id_product_related' => $this->id_product_related));
$db->close();
if ($stmt1 && $stmt2) {
header('Location: cancel.php?success=true');
exit;
}
return false;
}
public function getUser()
{
$db = PDOQuery::getInstance();
$db->connect();
$email = trim($_POST['email']);
$password = sha1($_POST['password']);
$sql = "SELECT user_key, is_admin, id_lang, id_user\n\t\t\t\t\tFROM user\n\t\t\t\tWHERE email =?\n\t\t\t\tAND password =?";
$stmt = $db->prepareQuery($sql);
$stmt->execute(array($email, $password));
$stmt->execute();
if ($stmt->rowCount() > 0) {
$row = $db->next_row($stmt);
$this->context->session->setSessionVar('userKey', $row['user_key']);
$this->context->session->setSessionVar('is_admin', $row['is_admin']);
$this->context->session->setSessionVar('id_lang', $row['id_lang']);
$this->context->session->setSessionVar('id_employee', $row['id_user']);
header("Location: shop_selection.php");
exit;
}
return false;
}
/**
* Установка специального комментария:
*/
public static function SetSpecialComment($id, $comment = '')
{
$dbh = PDOQuery::getInstance();
return $dbh->update('1chan_post', array('special_comment' => $comment), 'id = ' . $dbh->q($id));
}
public static function getProductPosition($id_session)
{
$db = PDOQuery::getInstance();
$db->connect();
$sql = "SELECT position \n\t\t\t\tFROM sales \n\t\t\t\t\tWHERE id_shop_session = ?\n\t\t\t\t\tORDER BY position DESC";
$stmt = $db->prepareQuery($sql);
$stmt->execute(array($id_session));
$row = PDOQuery::getInstance()->next_row($stmt);
return $position = $row['position'] + 1;
}
/**
* Сортировка категорий:
*/
public static function Resort($order)
{
$dbh = PDOQuery::getInstance();
foreach ($order as $id => $posi) {
$dbh->update('1chan_category', array('pos' => $posi), 'id = ' . $dbh->q($id));
}
return true;
}
public function getTotalMonthSales()
{
$db = PDOQuery::getInstance();
$db->connect();
$sql = "SELECT sales.amount, date_sale\n\t\t\tFROM sales\n\t\t\t\tWHERE id_shop =" . $_SESSION['id_shop'];
$stmt = $db->query($sql);
return $stmt;
}
public function isShopUser()
{
$db = PDOQuery::getInstance();
$db->connect();
$id_shop = intval($_GET['id_shop']);
$sql = "SELECT id_user\n\t\t\t\tFROM shop_permissions\n\t\t\tWHERE \n\t\t\t\tid_shop=?\n\t\t\tAND id_user = ?\n\t\t\tAND is_active=1";
$res = $db->prepareQuery($sql);
$res->execute(array($id_shop, $_SESSION['id_employee']));
$db->close();
if ($db->numRows($res) > 0) {
return true;
}
return false;
}
