| Server IP : 74.208.127.88 / Your IP : 3.129.206.232 Web Server : Apache/2.4.41 (Ubuntu) System : Linux ubuntu 5.4.0-163-generic #180-Ubuntu SMP Tue Sep 5 13:21:23 UTC 2023 x86_64 User : www-data ( 33) PHP Version : 7.4.3-4ubuntu2.29 Disable Function : pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_get_handler,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,pcntl_async_signals,pcntl_unshare, MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /var/www/html/goldencar/sae/model/ |
Upload File : |
<?php
class pos extends dbconn {
public function __construct()
{
$this->initDBO();
}
/******************************************************************************
START TABEL person
*******************************************************************************/
public function autoCompleteC($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM customer a WHERE name like :term order by name asc");
$stmt->bindParam("term",$trm);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
START TABEL stock
*******************************************************************************/
public function getStockId($id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from stock where id = :id ");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
end TABEL stock
*******************************************************************************/
/******************************************************************************
START TABEL m_item
*******************************************************************************/
public function getListItem()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.* FROM m_item t,
(SELECT @rownum := 0) r ORDER BY id_item ASC");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//inventario de tabla stock
public function getAllstock()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `stocks` ORDER BY id ASC");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItem($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from m_item a where a.id_item = :id and status is null");
$stmt->bindParam("id",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getIdProduct($barcode){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT id,name,image,barcode,price_in FROM product WHERE barcode= :barcode");
$stmt->bindParam("barcode",$barcode);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItemCompras($id_item,$kasir){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as compras from operation2 where product_id = :id and operation_type_id = 1 and stock_id = :kasir");
$stmt->bindParam("id",$id_item);
//$kasir
$stmt->bindParam("kasir",$kasir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItemCompras2($id_item,$kasir){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as Ocompras from operation2 where barcode = :barcode and operation_type_id = 1 and stock_id = :kasir");
$stmt->bindParam("barcode",$id_item);
$stmt->bindParam("kasir",$kasir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItemMerma($id_item,$kasir){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as merma from operation2 where product_id = :id and operation_type_id = 2 and stock_id = :kasir");
$stmt->bindParam("id",$id_item);
$stmt->bindParam("kasir",$kasir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItemVentas($barcode,$kasir){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(qty) as ventas from t_sale_detail where barcode = :id and is_draft = 0 and disc_rp = :kasir");
$stmt->bindParam("kasir",$kasir);
$stmt->bindParam("id",$barcode);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItemDev($id,$kasir){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as dev from operation where product_id = :id and operation_type_id = 5 and stock_id = :kasir");
$stmt->bindParam("id",$id);
$stmt->bindParam("kasir",$kasir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getClientMostrador($item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from customer a where a.name = :name ");
$stmt->bindParam("name",$item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getClient($id_cust){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from customer a where a.id_cust = :id_cust ");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getRe(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT MAX(id) AS id FROM re");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItemPrice($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from listprice a where a.barcode = :id ");
$stmt->bindParam("id",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItem3($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from m_item a where a.barcode = :id and status is null");
$stmt->bindParam("id",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getItem2($id_item){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("SELECT COUNT(*) as contador FROM m_item where stock < :id");
$stmt->bindParam("id",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateItem($iditem,$item_name,$price,$unit,$stock,$tax,$note)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE m_item
SET item_name = UPPER(:item_name),
unit= :unit,
stock= :stock,
tax= :tax,
price= :price,
note= :note
WHERE id_item= :iditem;");
$stmt->bindParam("iditem",$iditem);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("price",$price);
$stmt->bindParam("note",$note);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("stock",$stock);
$stmt->bindParam("tax",$tax);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//noviembre/2021
public function saveItem($barcode,$item_name,$price,$unit,$stock,$tax,$note){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveItem(:barcode,:item_name,:unit,:stock,:tax,:price,:note)");
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("price",$price);
$stmt->bindParam("note",$note);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("stock",$stock);
$stmt->bindParam("tax",$tax);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function deleteItem($iditem)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from m_item where id_item = :id");
$stmt->bindParam("id",$iditem);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function autoCompleteItem($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM m_item a WHERE ( item_name like :term or barcode like :term ) and status is NULL order by item_name asc");
$stmt->bindParam("term",$trm);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/*******************************************************************************
END OF TABEL M_Item
*******************************************************************************/
/******************************************************************************
START TABEL Product actualizacion 20/04/2021
*******************************************************************************/
public function saveProduct($barcode,$item_name,$price,$unit,$stock,$note){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveItem(:barcode,:item_name,:unit,:stock,:price,:note)");
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("price",$price);
$stmt->bindParam("note",$note);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("stock",$stock);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/*******************************************************************************
END OF TABEL Product
*******************************************************************************/
/*******************************************************************************
START OF TABEL promotion
*******************************************************************************/
public function getPromo($id_item){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select a.* from promotion a where a.product_id = :product_id ");
$stmt->bindParam("product_id",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
END OF TABEL promotion
*******************************************************************************/
/******************************************************************************
TABEL T_JUAL AND TEMP_JUAL
*******************************************************************************/
public function deleteTempSaleByUser($iduser)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from temp_sale where id_user = :id");
$stmt->bindParam("id",$iduser);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function resetTempSaleByUserSession($iduser,$uniqid)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from temp_sale where id_user = :id and uniqid = :uniqid");
$stmt->bindParam("id",$iduser);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function resetTempSaleByUserSessionOC($iduser,$uniqid)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from purchase_order where id_user = :id and uniqid != :uniqid");
$stmt->bindParam("id",$iduser);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getListTempSale($cashier,$uniqid){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
FROM temp_sale t,
(SELECT @rownum := 0) r where t.id_user= :cashier and t.uniqid= :uniqid ORDER BY input_date DESC");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getListTempSale2($cashier,$uniqid){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
FROM temp_exist t,
(SELECT @rownum := 0) r where t.id_user= :cashier and t.uniqid= :uniqid ORDER BY input_date DESC");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getListTempSale4($cashier,$uniqid){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*
FROM purchase_order t,
(SELECT @rownum := 0) r where t.id_user = :cashier and t.uniqid != :uniqid ORDER BY input_date DESC");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function deleteTempSaleProduct($cashier,$uniqid,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from temp_sale where id_user = :id and uniqid = :uniqid and id_item = :id_item ");
$stmt->bindParam("id",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function deleteTempSaleProduct2($cashier,$uniqid,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from temp_exist where id_user = :id and uniqid = :uniqid and id_item = :id_item ");
$stmt->bindParam("id",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function deleteTempSaleProduct4($cashier,$uniqid,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from purchase_order where id_user = :id and uniqid = :uniqid and id_item = :id_item ");
$stmt->bindParam("id",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSale($cashier,$uniqid,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update temp_sale set qty=qty+1 where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSale4($cashier,$uniqid,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update purchase_order set qty=qty+1 where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSaleHargaSale($cashier,$uniqid,$id_item,$value)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update temp_sale set price = :value
where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("value",$value);
$stmt->execute();
$stat[0] = true;
$stat[1] = "cambio exitoso!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSaleHargaSale4($cashier,$uniqid,$id_item,$value)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update purchase_order set price = :value
where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("value",$value);
$stmt->execute();
$stat[0] = true;
$stat[1] = "cambio exitoso!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSaleQty($cashier,$uniqid,$id_item ,$value)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update temp_sale set qty= :value where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("value",$value);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSaleQty4($cashier,$uniqid,$id_item ,$value)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update purchase_order set qty= :value where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("value",$value);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function deleteSale($sale_id,$note)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call deleteSale(:id,:note)");
$stmt->bindParam("id",$sale_id);
$stmt->bindParam("note",$note);
$stmt->execute();
$stat[0] = true;
$stat[1] = 'Success Delete';
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSaleDisc($cashier,$uniqid,$id_item,$value)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update temp_sale set discprc = :value
where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("value",$value);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateTempSaleDisc4($cashier,$uniqid,$id_item,$value)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update purchase_order set discprc = :value
where uniqid= :uniqid and id_user = :cashier
and id_item = :id_item ");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("value",$value);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveTempSale($cashier,$uniqid, $image,$id_item,$barcode,$unit,$item_name,$qty,$price,$discprn,$discrp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into temp_sale(id_user, uniqid, image, id_item, barcode, item_name, qty, unit, price, discprc, discrp) values (:cashier , :uniqid , :image, :id_item, :barcode, :item_name, :qty, :unit, :price, :discprn, :discrp)");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("image",$image);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("qty",$qty);
$stmt->bindParam("price",$price);
$stmt->bindParam("discprn",$discprn);
$stmt->bindParam("discrp",$discrp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveTempSale4($cashier,$uniqid,$id_item,$barcode,$unit,$item_name,$qty,$price,$discprn,$discrp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into purchase_order(id_user, uniqid, id_item, barcode, item_name, qty, unit, price, discprc, discrp) values
(:cashier , :uniqid , :id_item, :barcode, :item_name, :qty, :unit, :price, :discprn, :discrp)");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("qty",$qty);
$stmt->bindParam("price",$price);
$stmt->bindParam("discprn",$discprn);
$stmt->bindParam("discrp",$discrp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveTempSale2($cashier,$uniqid, $image,$id_item,$barcode,$unit,$item_name,$qty,$price,$discprn,$discrp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into temp_sale(id_user, uniqid, image, id_item, barcode, item_name, qty, unit, price, discprc, discrp) values
(:cashier , :uniqid , :image, :id_item, :barcode, :item_name, :qty, :unit, :price, :discprn, :discrp)");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("image",$image);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("qty",$qty);
$stmt->bindParam("price",$price);
$stmt->bindParam("discprn",$discprn);
$stmt->bindParam("discrp",$discrp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveTempSaleE($cashier,$uniqid,$id_item,$barcode,$item_name,$price,$compras,$ventas,$dev,$ajustes,$existencia)
{ //$cashier,$uniqid,$result['id_item'],$result['barcode'],$result['item_name'],20.00,10.00, $result['price'], 11.00,12.00
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into temp_exist(id_user, uniqid, id_item, barcode, item_name,price, compras, ventas, dev, ajustes, existencia) values
(:cashier , :uniqid , :id_item, :barcode, :item_name, :price, :compras, :ventas, :dev, :ajustes, :existencia)");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("price",$price);
$stmt->bindParam("compras",$compras);
$stmt->bindParam("ventas",$ventas);
$stmt->bindParam("dev",$dev);
$stmt->bindParam("ajustes",$ajustes);
$stmt->bindParam("existencia",$existencia);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSale($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSale( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("f_pago1",$f_pago1);
$stmt->bindParam("f_pago2",$f_pago2);
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("person_id",$person_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSaleFac($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSaleFac( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("f_pago1",$f_pago1);
$stmt->bindParam("f_pago2",$f_pago2);
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("person_id",$person_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSaleStock2($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSaleStock2( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("f_pago1",$f_pago1);
$stmt->bindParam("f_pago2",$f_pago2);
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("person_id",$person_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSaleStock3($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSaleStock3( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("f_pago1",$f_pago1);
$stmt->bindParam("f_pago2",$f_pago2);
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("person_id",$person_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSaleStock4($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSaleStock4( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("f_pago1",$f_pago1);
$stmt->bindParam("f_pago2",$f_pago2);
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("person_id",$person_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSaleS($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id,$stock_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSaleS( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id, :stock_id)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("f_pago1",$f_pago1);
$stmt->bindParam("f_pago2",$f_pago2);
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("person_id",$person_id);
$stmt->bindParam("stock_id",$stock_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSale2($sale_id,$sale_date,$total,$id_client,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$credit_amount)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSale2( :sale_id, :sale_date, :total, :id_client, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :credit_amount)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("id_client",$id_client);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("credit_amount",$credit_amount);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSale3($sale_id,$sale_date,$total,$id_client,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$credit_amount)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSale3( :sale_id, :sale_date, :total, :id_client, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :credit_amount)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("id_client",$id_client);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("credit_amount",$credit_amount);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSale4($sale_id,$sale_date,$total,$id_client,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$credit_amount)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSale4( :sale_id, :sale_date, :total, :id_client, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :credit_amount)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("id_client",$id_client);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("credit_amount",$credit_amount);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveSale5($sale_id,$sale_date,$total,$id_client,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$credit_amount)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSale5( :sale_id, :sale_date, :total, :id_client, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :credit_amount)");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("sale_date",$sale_date);
$stmt->bindParam("total",$total);
$stmt->bindParam("id_client",$id_client);
$stmt->bindParam("paid",$paid);
$stmt->bindParam("disc_prcn",$disc_prcn);
$stmt->bindParam("disc_rp",$disc_rp);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("note",$note);
$stmt->bindParam("credit_amount",$credit_amount);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
$stat[2] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getSubTotalTempSale($cashier,$uniqid){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT SUM((price - (price*(discprc/100)))*qty)AS total
FROM temp_sale where uniqid= :uniqid and id_user = :cashier");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchColumn(0);
$stat[2] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getSubTotalTempSaleOC($cashier,$uniqid){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT SUM(price*qty)AS total
FROM purchase_order where uniqid != :uniqid and id_user = :cashier");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchColumn(0);
$stat[2] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function checkTempSale($cashier,$uniqid){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT id_user,uniqid FROM temp_sale where uniqid= :uniqid and id_user = :cashier");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getCheckProduk($cashier,$uniqid,$id_item ){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from temp_sale where uniqid= :uniqid and id_user = :cashier and id_item = :id_item");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getStockProduk($kasir,$uniqid,$id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from temp_sale where uniqid= :uniqid and id_user = :kasir and id_item = :id_item");
$stmt->bindParam("kasir",$kasir);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getCheckProduk4($cashier,$uniqid,$id_item ){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from purchase_order where uniqid= :uniqid and id_user = :cashier and id_item = :id_item");
$stmt->bindParam("cashier",$cashier);
$stmt->bindParam("uniqid",$uniqid);
$stmt->bindParam("id_item",$id_item);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getTransSale($awal,$akhir,$order = 'desc')
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.`sale_date`,a.`sale_id`,
(SELECT SUM((d.price - (d.price*(d.disc_prc/100)))*d.qty) AS total
FROM t_sale_detail d WHERE d.sale_id = a.sale_id)AS total,
c.`username`,a.sts,a.note,a.paid,a.disc_rp
FROM t_sale a
INNER JOIN m_user c ON a.`id_user` = c.`id_user`
where (a.`sale_date` BETWEEN :awal AND :akhir) and a.sts < 2
ORDER BY sale_id ".$order );
$stmt->bindParam("awal",$awal);
$stmt->bindParam("akhir",$akhir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//detalle de una venta
public function getDetails($sale){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from t_sale_detail where sale_id = :sale_id ");
$stmt->bindParam("sale_id",$sale);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getTransCot($awal,$akhir,$order = 'desc')
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.`sale_date`,a.`sale_id`,
(SELECT SUM((d.price - (d.price*(d.disc_prc/100)))*d.qty) AS total
FROM t_sale_detail d WHERE d.sale_id = a.sale_id)AS total,
c.`username`,a.sts,a.note,a.paid,a.disc_rp
FROM t_sale a
INNER JOIN m_user c ON a.`id_user` = c.`id_user`
where (a.`sale_date` BETWEEN :awal AND :akhir) and a.sts > 1
ORDER BY sale_id ".$order );
$stmt->bindParam("awal",$awal);
$stmt->bindParam("akhir",$akhir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getTransOC_P($awal,$akhir)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare(" SELECT * FROM `re` a WHERE a.`created_at` > :awal AND a.`created_at` < :akhir AND a.`operation_type_id` > 1 ");
$stmt->bindParam("awal",$awal);
$stmt->bindParam("akhir",$akhir);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getTransSaleDev($awal,$akhir,$order = 'desc',$id = '0')
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.`sale_date`,a.`sale_id`,
(SELECT SUM((d.price - (d.price*(d.disc_prc/100)))*d.qty) AS total
FROM t_sale_detail d WHERE d.sale_id = a.sale_id)AS total,
c.`username`,a.sts,a.paid,a.disc_rp
FROM t_sale a
INNER JOIN m_user c ON a.`id_user` = c.`id_user`
where sts = :id AND (a.`sale_date` BETWEEN :awal AND :akhir)
ORDER BY sale_id ".$order );
$stmt->bindParam("awal",$awal);
$stmt->bindParam("akhir",$akhir);
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getSaleId($id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* ,c.`username`
FROM t_sale a
INNER JOIN m_user c ON a.`id_user` = c.`id_user` where a.sale_id = :id");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getSaleDetailIdSale($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan, a.*,
(a.price - ((a.price * a.disc_prc) /100) ) * a.qty as total
from t_sale_detail a,(SELECT @rownum := 0) r where a.sale_id = :id");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchall(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
END TABEL T_JUAL
*******************************************************************************/
/******************************************************************************
START OF pos MENU CODE
*******************************************************************************/
public function getMenu()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from r_menu order by menu_order");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getSubMenu($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from r_menu_sub where id_menu = :id order by sub_menu_order asc");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getIdMenu($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from r_menu_sub where id_sub_menu = :id order by sub_menu_order asc");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/*********************query for system*********************/
public function getLogin($user,$pass)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.*,
(select name_shop from r_ref_system where id = 1) as name_shop,
(select address_shop from r_ref_system where id = 1) as address_shop,
(select phone_shop from r_ref_system where id = 1) as phone_shop
from m_user a where upper(a.username)=upper(:user) and a.pass_user=md5(:id)");
$stmt->bindParam("user",$user);
$stmt->bindParam("id",$pass);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
$stat[2] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = 0;
return $stat;
}
$stmt->close();
}
public function getrefsytem()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from configuration a where id = 28 ");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = 0;
return $stat;
}
}
public function getrefsytem2()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from configuration a where id = 29 ");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = 0;
return $stat;
}
}
public function getrefsytem3()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from configuration a where id = 30 ");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = 0;
return $stat;
}
}
//buscar valor de configuration
public function getByPreffix($short)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.val from configuration a where short=\"$short\" ");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = 0;
return $stat;
}
}
public function updateSettingSaldo($short)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update configuration set val=val-1 where short= :short");
$stmt->bindParam("short",$short);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getrefsytemX()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from r_ref_system a where id = 1 ");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = 0;
return $stat;
}
}
public function getSubMenuById($menu)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT name_sub_menu FROM r_menu_sub WHERE id_sub_menu= :menu");
$stmt->bindParam("menu",$menu);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchColumn(0);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizar usuario unico en tabla m_user
public function updateUniqLogin($id_user,$uniq_login)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update m_user set uniq_login = :uniq_login where id_user = :id_user");
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("uniq_login",$uniq_login);
$stmt->execute();
$stat[0] = true;
$stat[1] = "cambio exitoso!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//insertar usuario que acceso al sistema
public function saveUniqUser($id_user,$access)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into sessions(id_user,access)
values(:id_user,:access)");
$stmt->bindParam("id_user",$id_user);
$stmt->bindParam("access",$access);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//borramos sesiones
public function deleteUniqUser($iduser)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from sessions where id_user = :id");
$stmt->bindParam("id",$iduser);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Delete sessions!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/*********************query for master user*********************/
public function getListUser()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from m_user where username<>'admin' order by username desc");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function getUserMenu($username)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from m_user where username = :username order by username desc");
$stmt->bindParam("username",$username);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveUser($username,$pass_user,$h_menu,$stock_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into m_user(username,pass_user,h_menu,stock_id)
values(:name,MD5(:pass),:hmenu)");
$stmt->bindParam("name",$username);
$stmt->bindParam("pass",$pass_user);
$stmt->bindParam("hmenu",$h_menu);
$stmt->bindParam("stock_id",$stock_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateUser($id_user,$username,$h_menu,$stock_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update m_user set username = :name, h_menu = :hmenu , stock_id=:stock_id where id_user = :id");
$stmt->bindParam("name",$username);
$stmt->bindParam("id",$id_user);
$stmt->bindParam("hmenu",$h_menu);
$stmt->bindParam("stock_id",$stock_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses update!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function updateUserKind($id_user,$kind)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE `m_user` SET `kind` = :kind WHERE `m_user`.`id_user` = :id ");
$stmt->bindParam("id",$id_user);
$stmt->bindParam("kind",$kind);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses update!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function deleteUser($id_user)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from m_user where id_user = :id");
$stmt->bindParam("id",$id_user);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses update!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function checkPassword($id,$pass)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from m_user where id_user = :id and pass_user = md5(:pass) and kind = 1");
$stmt->bindParam("id",$id);
$stmt->bindParam("pass",$pass);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->rowCount();
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function resetPass($iduser,$pass)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update m_user set pass_user = md5(:pass) where id_user=:id");
$stmt->bindParam("id",$iduser);
$stmt->bindParam("pass",$pass);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses reset pass!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
END OF MENU CODE
*******************************************************************************/
/******************************************************************************
INI OF OPERATION2
*******************************************************************************/
public function getbyid($barcode){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.id from product a where a.barcode = :id ");
$stmt->bindParam("id",$barcode);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function searchEntradas($awal,$akhir,$order = 'desc',$id = '0')
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.`sale_date`,a.`sale_id`,
(SELECT SUM((d.price - (d.price*(d.disc_prc/100)))*d.qty) AS total
FROM t_sale_detail d WHERE d.sale_id = a.sale_id)AS total,
c.`username`,a.sts,a.paid,a.disc_rp
FROM t_sale a
INNER JOIN m_user c ON a.`id_user` = c.`id_user`
where sts = :id AND (a.`sale_date` BETWEEN :awal AND :akhir)
ORDER BY sale_id ".$order );
$stmt->bindParam("awal",$awal);
$stmt->bindParam("akhir",$akhir);
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//datos de la venta tabla sell
public function getlistSell($id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from sell where sale_id = :id ");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//detalle de la venta
public function getOperations($id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from t_sale_detail where sale_id = :id ");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//obtenemos la forma de pago
public function getFormakId($id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from formapago where id = :id ");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//insertamos el registro de la factura generada en facturaOK
public function saveFacturaSell($mail,$uuid,$rfc,$sell_id,$sale_id,$subtotal,$impuestos,$total,$iva,$name,$user_id)
{
$db = $this->dblocal;
try
{
// $stmt = $db->prepare("insert into temp_sale(id_user, uniqid, id_item, barcode, item_name, qty, unit, price, discprc, discrp) values (:cashier , :uniqid , :id_item, :barcode, :item_name, :qty, :unit, :price, :discprn, :discrp)");
$stmt = $db->prepare("insert into facturaok( image, name, description, sell_id, sale_id, subtotal, impuestos, price_out, unit, presentation, user_id, created_at,is_service) values ( :mail, :uuid, :rfc, :sell_id, :sale_id, :subtotal, :impuestos, :total, :iva, :name, :user_id,now(),1 ) ");
$stmt->bindParam("mail",$mail);
$stmt->bindParam("uuid",$uuid);
$stmt->bindParam("rfc",$rfc);
$stmt->bindParam("sell_id",$sell_id);
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("subtotal",$subtotal);
$stmt->bindParam("impuestos",$impuestos);
$stmt->bindParam("total",$total);
$stmt->bindParam("iva",$iva);
$stmt->bindParam("name",$name);
$stmt->bindParam("user_id",$user_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//insertamos el registro de la factura HONORARIOS en facturaOK
public function saveFacturaHono($mail,$uuid,$rfc,$sell_id,$sale_id , $subtotal, $impuesto ,$total,$iva,$name,$user_id)
{
$db = $this->dblocal;
try
{
// $stmt = $db->prepare("insert into temp_sale(id_user, uniqid, id_item, barcode, item_name, qty, unit, price, discprc, discrp) values (:cashier , :uniqid , :id_item, :barcode, :item_name, :qty, :unit, :price, :discprn, :discrp)");
$stmt = $db->prepare("insert into facturaok( image, name, description, sell_id, sale_id, subtotal, impuestos, price_out, unit, presentation, user_id, created_at,is_service) values ( :mail, :uuid, :rfc, :sell_id, :sale_id, :subtotal, :impuestos ,:total, :iva, :name, :user_id,now(),2 ) ");
$stmt->bindParam("mail",$mail);
$stmt->bindParam("uuid",$uuid);
$stmt->bindParam("rfc",$rfc);
$stmt->bindParam("sell_id",$sell_id);
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("subtotal",$subtotal);
$stmt->bindParam("impuestos",$impuestos);
$stmt->bindParam("total",$total);
$stmt->bindParam("iva",$iva);
$stmt->bindParam("name",$name);
$stmt->bindParam("user_id",$user_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function list_inventary()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select * from m_item order by stock DESC");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List inventary";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//id maximo de facturas
public function getByFacturaMax(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT MAX(id) AS id from `facturaok` where `is_active`=1 and `is_service`=1");
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
END OF OPERATION2 CODE
*******************************************************************************/
}
?>