| Server IP : 74.208.127.88 / Your IP : 216.73.216.82 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/muebles/application/model/nppBackup/ |
Upload File : |
<?php
date_default_timezone_set("America/Mexico_City");
setlocale(LC_ALL,"es_MX.utf8");
class pos extends dbconn {
public function __construct()
{
$this->initDBO();
}
//insertar datos a la cuenta
/*
INSERT INTO `accounts` (`id`, `week`, `CuentasxCobrar`, `PendienteCobrar`, `SaldoCobrado`, `dif`, `txtdatein`,
`txtdateout`, `update_date`, `created_at`) VALUES ('1', '35', '23456', '0', '0', '23456', '2023-10-13', '2023-10-19',
current_timestamp(), current_timestamp());
*/
public function saveAcounts($week,$CuentasxCobrar,$PendienteCobrar,$txtdatein,$txtdateout){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into `accounts`(`week`, `CuentasxCobrar`,`PendienteCobrar`, `txtdatein`, `txtdateout`, created_at) values (:week, :CuentasxCobrar,:PendienteCobrar, :txtdatein, :txtdateout, NOW() )");
$stmt->bindParam("week",$week);
$stmt->bindParam("CuentasxCobrar",$CuentasxCobrar);
$stmt->bindParam("PendienteCobrar",$PendienteCobrar);
$stmt->bindParam("txtdatein",$txtdatein);
$stmt->bindParam("txtdateout",$txtdateout);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save Counts !";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//guardar el pago desde modulo de cobro de cobrador
public function save_pay($week,$CuentasxCobrar,$PendienteCobrar,$txtdatein,$txtdateout){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into `accounts`(`week`, `CuentasxCobrar`,`PendienteCobrar`, `txtdatein`, `txtdateout`, created_at) values (:week, :CuentasxCobrar,:PendienteCobrar, :txtdatein, :txtdateout, NOW() )");
$stmt->bindParam("week",$week);
$stmt->bindParam("CuentasxCobrar",$CuentasxCobrar);
$stmt->bindParam("PendienteCobrar",$PendienteCobrar);
$stmt->bindParam("txtdatein",$txtdatein);
$stmt->bindParam("txtdateout",$txtdateout);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save Counts !";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//condiciones de cobranza
public function getListDataCollection()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `collection_data` order by created_at desc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List collection_data OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//todas las ventas
public function getListCollection($id_empl=false)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `t_sale` where id_client > 100 AND deliver_sts = 0 order by input_date asc");
if($id_empl){
$stmt = $db->prepare("SELECT * FROM `t_sale` where id_client > 100 order by input_date asc");
}
$stmt = $db->prepare("SELECT * FROM `t_sale` where id_client > 100 order by input_date asc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
public function getListSaleNewWithOutCorbador()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `t_sale` where deliver_sts = 0 order by input_date asc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
/*
SELECT t.sale_id, t.id_client, t.id_seller, t.id_collector, date_format(t.sale_date, '%d-%m-%Y') as sale_date, t.total
, t.disc_rp, c.txtpayment_period,c.contract, date_format( c.paydate, '%d-%m-%Y') as paydate,
c.min_pay,c.payday, cli.name as cliente FROM `collection_data` as c
INNER JOIN t_sale as t ON c.contract = t.id_client
INNER JOIN customer cli ON c.contract = cli.contract
WHERE c.txtpayment_period = 4 and t.id_collector = 0;
*/
//ventas que se cobran por mes
public function getListCobranzaMes_OK($periodo)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT t.sale_id, t.id_client, t.id_seller, t.id_collector, date_format(t.sale_date, '%d-%m-%Y') as sale_date, t.total
, t.disc_rp, c.txtpayment_period,c.contract, date_format( c.paydate, '%d-%m-%Y') as paydate,
c.min_pay,c.payday, cli.name as cliente FROM `collection_data` as c
INNER JOIN t_sale as t ON c.contract = t.id_client
INNER JOIN customer cli ON c.contract = cli.contract
WHERE c.txtpayment_period = :periodo and t.id_collector = 0;");
$stmt->bindParam("periodo",$periodo);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//ventas que se cobran por mes
public function getListCobranzaMes($periodo)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT t.sale_id, t.id_client, t.id_seller, t.id_collector, date_format(t.sale_date, '%d-%m-%Y') as sale_date,
t.total, t.disc_rp ,c.contract,c.txtpayment_period, date_format( c.paydate, '%d-%m-%Y') as paydate, c.min_pay,c.payday, emp.name as name_cobrador,cli.name as cliente FROM `t_sale` t
INNER JOIN collection_data c ON t.id_client = c.contract
INNER JOIN employee emp ON t.id_collector = emp.id_cust
INNER JOIN customer cli ON c.contract = cli.contract
WHERE c.txtpayment_period = :periodo");
$stmt->bindParam("periodo",$periodo);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//ventas que se cobran por cobrador
public function getListCobranzaXcobrador($periodo, $id_cust)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT t.sale_id, t.id_client, t.id_seller, t.id_collector, date_format(t.sale_date, '%d-%m-%Y') as sale_date,
t.total, t.disc_rp ,c.txtpayment_period, date_format( c.paydate, '%d-%m-%Y') as paydate, c.min_pay,c.payday, emp.name as name_cobrador,cli.name as cliente FROM `t_sale` t
INNER JOIN collection_data c ON t.id_client = c.contract INNER JOIN employee emp ON t.id_collector = emp.id_cust
INNER JOIN customer cli ON c.contract = cli.contract
WHERE c.txtpayment_period = :periodo and t.id_collector = :id_cust");
$stmt->bindParam("periodo",$periodo);
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//cobranza generada por fechas
public function getListCobranza($txtdatein,$txtdateout)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM t_sale WHERE (sale_date BETWEEN :txtdatein AND :txtdateout) ");
$stmt->bindParam("txtdatein",$txtdatein);
$stmt->bindParam("txtdateout",$txtdateout);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List ventas a credito x fecha para cobranza OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//ventas por cobrador busqueda por delivery_sts = 1 cobranza generada, para asignacion por cobrador
public function getCollectorList($id_collector)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT *,date_format(sale_date, '%d-%m-%Y') as fecha_venta,@rownum := @rownum + 1 AS urutan FROM `t_sale` where id_collector = :id_collector and deliver_sts = 1 order by input_date asc");
$stmt->bindParam("id_collector",$id_collector);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//ventas por cobrador busqueda por delivery_sts = 2 cobranza para recibir, para asignacion por cobrador
public function getCollectorListr($id_collector)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT *,@rownum := @rownum + 1 AS urutan FROM `t_sale` where id_collector = :id_collector and deliver_sts = 2 order by input_date asc");
$stmt->bindParam("id_collector",$id_collector);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//obtener datos de cliente de cobranza
//ventas por cobrador
public function getjobContract($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `jobok` WHERE `contract` = :contract");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List job customer OK";
$stat[2] = $stmt->fetch(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//ventas por cobrador
public function getPendingRouteByColector($id_collector)
{
$db = $this->dblocal;
try
{
//por si no soporta idioma español el servidor
$diassemana = array("Domingo","Lunes","Martes","Miercoles","Jueves","Viernes","Sábado");
$meses = array("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre");
$currentDay=strtoupper($diassemana[date('w')]);
$dayNumber=strftime("%d");
$stmt = $db->prepare("SELECT pr.sale_id,pr.total,pr.pay_min,pr.balance,pr.payday,pr.pay_period,
cus.name,cus.direccion,cus.contract
FROM pending_route pr
left JOIN customer cus ON cus.contract= pr.contract
where collector_id = :id_collector AND pay_period='S' AND UPPER(payday)= :currentDay OR payday =:dayNumber
order by payday asc");
$stmt->bindParam("id_collector",$id_collector);
$stmt->bindParam("currentDay",$currentDay);
$stmt->bindParam("dayNumber",$dayNumber);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK pending_route";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//ventas por cobrador
public function getPendingRouteByColectorTotal($currentDay,$id_collector,$dayNumber,$pay_period,$pay_period2)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT pr.sale_id,pr.total,pr.pay_min,pr.balance,pr.payday,pr.pay_period,
cus.name,cus.direccion,cus.contract
FROM pending_route pr
left JOIN customer cus ON cus.contract= pr.contract
where collector_id = :id_collector and pay_period= :pay_period AND UPPER(payday)= :currentDay AND NumberDay =:dayNumber AND pay_period= :pay_period2
order by payday asc");
$stmt->bindParam("currentDay",$currentDay);
$stmt->bindParam("id_collector",$id_collector);
$stmt->bindParam("dayNumber",$dayNumber);
$stmt->bindParam("pay_period",$pay_period);
$stmt->bindParam("pay_period2",$pay_period2);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK pending_route";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
public function getPays($id_cust){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT SUM(val) AS balance,created_at FROM payment WHERE person_id= :id_cust and payment_type_id = 2");
$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;
}
}
//fecha del ultimo pago por cliente
public function getPaysUltimo($id_cust){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT `id`,`person_id`,MAX(`created_at`) ,date_format(MAX(pay_date), '%d-%m-%Y') as ultimoPago ,MIN(pay_date) as primerPago FROM `payment` WHERE (`payment_type_id`=2) AND `person_id`= :id_cust ORDER BY `created_at` DESC ");
$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;
}
}
//PAGOS ATRAZADOS POR semana
public function getPaysOld($sale_id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT TIMESTAMPDIFF(WEEK,`created_at`,NOW()) as atrazo FROM `payment` WHERE `sale_id` = :sale_id and `payment_type_id`=2 ORDER BY `created_at` DESC LIMIT 1;");
$stmt->bindParam("sale_id",$sale_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;
}
}
//fecha del ultimo pago por cliente
public function getPaysUltimo2($id_cust){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT `id`,`person_id`,payment_type_id,date_format( max(`created_at`), '%d-%m-%Y') as fechaUltimoP, date_format(MAX(pay_date), '%d-%m-%Y') FROM `payment` WHERE `payment_type_id`=2 AND `person_id`= :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;
}
}
//bucamos pago por numero de semana
public function getPaysWeek($semC,$id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT TIMESTAMPDIFF(week, :semC,NOW()) AS sems FROM `payment` WHERE `person_id` = :id_item and `payment_type_id` = 2");
$stmt->bindParam("semC",$semC);
$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;
}
}
//bucamos pago por numero de meses
public function getPaysMonth($semC,$semA,$id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT TIMESTAMPDIFF(month, :semC,:semA) AS month FROM `payment` WHERE `person_id` = :id_item and `payment_type_id` = 2");
$stmt->bindParam("semC",$semC);
$stmt->bindParam("semA",$semA);
$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;
}
}
/******************************************************************************
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 or contract 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;
}
}
//autocompletado proveedor
public function autoCompletePerson($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM supplier WHERE (name like :term or rfc 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;
}
}
//autompletado ruta
public function autoCompleteRoute($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM routes a WHERE (name like :term or description 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;
}
}
public function autoCompleteP($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM supplier 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 a.* from stock a where a.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 getTotalQTempSale(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT SUM(qty) AS total FROM purchase_order");
$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 getListItem()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*,cat.name as categoria FROM m_item t INNER JOIN category cat ON t.category_id = cat.id WHERE t.status is null ORDER BY t.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;
}
}
public function getListItem2()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT @rownum := @rownum + 1 AS urutan,t.*,date_format(date_down, '%d-%m-%Y') as date_down FROM m_item t,
(SELECT @rownum := 0) r where status = 0 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;
}
}
//lista de inventario
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;
}
}
//lista de ventas a credito general
public function list_credit()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sal.*, date_format(sal.sale_date,'%d-%m-%Y') as sale_date,cli.name as cliente,cli.direccion,m.item_name,emp.name as vendedor from t_sale sal INNER JOIN customer cli ON sal.id_client = cli.contract INNER JOIN t_sale_detail m ON sal.sale_id = m.sale_id INNER JOIN employee emp ON sal.id_seller = emp.id_cust and cli.status != 1 order by sal.sale_id DESC");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List sales credit";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//lista de ventas
public function list_creditSeller($id_seller)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sal.*, date_format(sal.sale_date,'%d-%m-%Y') as sale_date,cli.name as cliente,cli.direccion,m.item_name,emp.name as vendedor from t_sale sal INNER JOIN customer cli ON sal.id_client = cli.contract INNER JOIN t_sale_detail m ON sal.sale_id = m.sale_id INNER JOIN employee emp ON sal.id_seller = emp.id_cust WHERE sal.id_seller = :id_seller order by sal.sale_id DESC");
$stmt->bindParam("id_seller",$id_seller);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List sales credit x vendedor";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//lista de ventas a credito por vendedor y por fechas
public function getVentasXFechasCredito($id_seller,$first,$last)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sal.*, date_format(sal.sale_date,'%d-%m-%Y') as sale_date,cli.name as cliente,cli.direccion,m.item_name,emp.name as vendedor from t_sale sal INNER JOIN customer cli ON sal.id_client = cli.contract INNER JOIN t_sale_detail m ON sal.sale_id = m.sale_id INNER JOIN employee emp ON sal.id_seller = emp.id_cust WHERE sal.id_seller = :id_seller and ( date_format(sal.`sale_date`, '%d-%m-%Y') BETWEEN :first AND :last) order by sal.sale_id DESC");
$stmt->bindParam("id_seller",$id_seller);
$stmt->bindParam("first",$first);
$stmt->bindParam("last",$last);
$stmt->execute();
$stat[0] = true;
$stat[1] = "List sales credit x fechas";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
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,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){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as compras from operation2 where product_id = :id and operation_type_id = 1");
$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 getItemCompras2($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as Ocompras from operation2 where barcode = :barcode and operation_type_id = 1");
$stmt->bindParam("barcode",$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 getItemMerma($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as merma from operation2 where product_id = :id and operation_type_id = 2");
$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 getItemVentas($barcode){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(qty) as ventas from t_sale_detail where barcode = :id and is_draft = 0");
$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){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select sum(q) as dev from operation where product_id = :id and operation_type_id = 5");
$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;
}
}
//UPDATE `customer` SET `status` = '1' WHERE `customer`.`contract` = 190;
public function updatecustomer($contract,$date_up){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE `customer` SET `status` = '1', date_up = :date_up WHERE `customer`.`contract` = :contract ");
$stmt->bindParam("contract",$contract);
$stmt->bindParam("date_up",$date_up);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado correcto customer";
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;
}
}
//buscar cliete por id
public function getByIdClient($item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `customer` WHERE `id_cust` = :id ");
$stmt->bindParam("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;
}
}
//buscar cliete por contrato
public function getByIdClientContract($item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from customer a where a.contract = :id ");
$stmt->bindParam("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;
}
}
//ultimo cliente id
public function getMaxC(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT MAX(id_cust) AS id FROM customer");
$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;
}
}
//ultimo aval id
public function getMaxAval(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT MAX(id) AS id FROM endorsement");
$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;
}
}
//mercancia regresada a almacen desde ventas
public function getTransMxV($id_vendedor)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare(" SELECT id,employee_id,created_at FROM `re` WHERE `employee_id` = :id AND `operation_type_id` = 2");
$stmt->bindParam("id",$id_vendedor);
$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;
}
}
//mercancia regresada a almacen desde ventas
public function getTransM_EntregadaAVentas($id_vendedor)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare(" SELECT * FROM `re` WHERE `employee_id` = :id AND `operation_type_id` = 2 and is_draft = 5");
$stmt->bindParam("id",$id_vendedor);
$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;
}
}
//mercancia regresada a almacen desde ventas x fecha
public function getTransM_EntregadaAVentasXF($id_vendedor, $fecha)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare(" SELECT * FROM `re` WHERE `employee_id` = :id AND `operation_type_id` = 2 and is_draft = 5 and (date_format(created_at, '%d-%m-%Y') = :fecha)");
$stmt->bindParam("id",$id_vendedor);
$stmt->bindParam("fecha",$fecha);
$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;
}
}
//obtenemos el folio de mercancia en transito
public function getFolioTransito($folio){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT tran.employee_id, tran.id,emp.name FROM re tran left JOIN employee emp ON emp.id_cust = tran.employee_id where tran.id =:id");
$stmt->bindParam("id",$folio);
$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;
}
}
//ACTUALIZAR MERCANCIA
public function updateItem($iditem,$barcode,$item_name,$price,$unit,$stock,$note,$category_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE m_item
SET barcode = UPPER(:barcode),
item_name = :item_name,
unit= :unit,
stock= :stock,
price= :price,
category_id =:category_id,
note= :note
WHERE id_item= :iditem;");
$stmt->bindParam("barcode",$barcode);
$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("category_id",$category_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Registro Actualizado !";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//ACTUALIZAR MERCANCIA BAJA
public function updateItemB($iditem,$codebar,$item_name,$price,$unit,$stock,$price_cont,$status)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE m_item
SET barcode = UPPER(:barcode),
item_name = UPPER(:item_name),
unit= :unit,
stock= :stock,
price= :price,
note= :note ,
status= :status
WHERE id_item= :iditem;");
$stmt->bindParam("barcode",$codebar);
$stmt->bindParam("iditem",$iditem);
$stmt->bindParam("item_name",$item_name);
$stmt->bindParam("price",$price);
$stmt->bindParam("note",$price_cont);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("stock",$stock);
$stmt->bindParam("status",$status);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado baja mercancia!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
public function saveItem($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;
}
}
//guardar clientes
public function saveClient($nombre,$contrato,$id_collector,$conyugue,$direccion,$payday,$credito,$poblacion, $telefono){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveClient(:nombre,:contrato,:id_collector,:conyugue,:direccion,:payday,:credito,:poblacion, :telefono)");
$stmt->bindParam("nombre",$nombre);
$stmt->bindParam("contrato",$contrato);
$stmt->bindParam("id_collector",$id_collector);
$stmt->bindParam("conyugue",$conyugue);
$stmt->bindParam("conyugue",$conyugue);
$stmt->bindParam("direccion",$direccion);
$stmt->bindParam("payday",$payday);
$stmt->bindParam("credito",$credito);
$stmt->bindParam("poblacion",$poblacion);
$stmt->bindParam("telefono",$telefono);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save Client!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//saveAval name,relationship,colonia,direction,customer_id,country,phone
public function saveAval($name,$relationship,$colonia,$direction,$customer_id,$country,$phone,$contract){
$db = $this->dblocal;
try
{
//INSERT INTO `endorsement` (`id`, `name`, `relationship`, `colonia`, `direction`, `customer_id`, `country`, `phone`) VALUES ('3', 'alama apata', '1', 'allamedas', 'centro de lerdo 2', '1', 'torrreon', '32984384');
$stmt = $db->prepare("insert into endorsement(`name`, `relationship`, `colonia`, `direction`, `customer_id`, `country`, `phone`,contract)
values (:name ,:relationship,:colonia,:direction,:customer_id,:country,:phone, :contract)");
$stmt->bindParam("name",$name);
$stmt->bindParam("relationship",$relationship);
$stmt->bindParam("colonia",$colonia);
$stmt->bindParam("direction",$direction);
$stmt->bindParam("customer_id",$customer_id);
$stmt->bindParam("country",$country);
$stmt->bindParam("phone",$phone);
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save endorsement!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//guardar datos del cliente su trabajo y avales
public function saveJob($name,$contract,$laborOld,$salary,$payment_frequency ,$endorsenments1,$endorsenments2,$user_id,$created_at){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveJob(:name,:contract,:laborOld,:salary,:payment_frequency,:endorsenments1,:endorsenments2,:user_id, :created_at)");
$stmt->bindParam("name",$name);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("laborOld",$laborOld);
$stmt->bindParam("salary",$salary);
$stmt->bindParam("payment_frequency",$payment_frequency);
$stmt->bindParam("endorsenments1",$endorsenments1);
$stmt->bindParam("endorsenments2",$endorsenments2);
$stmt->bindParam("user_id",$user_id);
$stmt->bindParam("created_at",$created_at);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Datos de Trabajo Cliente save !";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//dias de pago y demas valores
public function saveAdicionales2($id_client,$payday,$payment_period,$min_pay,$contract,$paydate){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into collection_data(id_client ,payday , txtpayment_period, min_pay,contract,paydate) values
( :id_client,:payday , :payment_period , :min_pay,:contract,:paydate)");
$stmt->bindParam("id_client",$id_client);
$stmt->bindParam("payday",$payday);
$stmt->bindParam("payment_period",$payment_period);
$stmt->bindParam("min_pay",$min_pay);
$stmt->bindParam("contract",$contract);
//paydate
$stmt->bindParam("paydate",$paydate);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Cobranza cliente save !";
$stat[2] = "Ok insert collection_data";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//obtenemos el pago minimo por ide decliente
public function getCollectionPay($id_item){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select * from collection_data where id_client = :id_item ");
$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;
}
}
//obtenemos el pago minimo por ide decliente
public function getCobranzaContract($contract){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select * from collection_data where contract = :contract ");
$stmt->bindParam("contract",$contract);
$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 el pago minimo por contrato
public function getPayMin($id_item){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select * from collection_data where contract = :id_item ");
$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;
}
}
//actualizacion DE CLIENTE
public function updateClient($id_cust,$contract,$nombre,$conyugue,$direccion,$come_in,$telefono,$credito,$poblacion)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE customer SET contract = :contrato, name= :nombre,conyugue= :conyugue, direccion= :direccion,come_in= :come_in,credito= :credito,poblacion= :poblacion, phone= :telefono WHERE id_cust= :id_cust");
$stmt->bindParam("id_cust",$id_cust);
$stmt->bindParam("contrato",$contract);
$stmt->bindParam("nombre",$nombre);
$stmt->bindParam("conyugue",$conyugue);
$stmt->bindParam("direccion",$direccion);
$stmt->bindParam("come_in",$come_in);
$stmt->bindParam("telefono",$telefono);
$stmt->bindParam("credito",$credito);
$stmt->bindParam("poblacion",$poblacion);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion DE CLIENTE
public function updateClient_colector($contract,$colector_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE customer SET colector_id= :colector_id WHERE contract= :contract");
$stmt->bindParam("contract",$contract);
$stmt->bindParam("colector_id",$colector_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion DE aval
public function updateAval($name,$relationship,$colonia,$direction,$country,$phone,$contract,$indice_aval)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE endorsement SET name= :name,relationship= :relationship, colonia= :colonia,direction= :direction,country=:country,phone= :phone WHERE contract = :contract and id= :indice_aval");
$stmt->bindParam("name",$name);
$stmt->bindParam("relationship",$relationship);
$stmt->bindParam("colonia",$colonia);
$stmt->bindParam("direction",$direction);
$stmt->bindParam("country",$country);
$stmt->bindParam("phone",$phone);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("indice_aval",$indice_aval);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado aval!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion datos adicionales de cliente
//updateCobranza1($contract,$workplace,$salary,$laborOld,$laborOld_periodicity,$payment_periodicity)
public function updateCobranza1($contract,$workplace,$salary,$laborOld,$laborOld_periodicity,$payment_periodicity)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE jobok SET name= :workplace,salary= :salary,laborOld= :laborOld, laborOld_periodicity= :laborOld_periodicity,payment_frequency= :payment_periodicity WHERE contract= :cont");
$stmt->bindParam("cont",$contract);
$stmt->bindParam("workplace",$workplace);
$stmt->bindParam("salary",$salary);
$stmt->bindParam("laborOld",$laborOld);
$stmt->bindParam("laborOld_periodicity",$laborOld_periodicity);
$stmt->bindParam("payment_periodicity",$payment_periodicity);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado datos adicionales de Empleo de cliente!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion datos COBRANZA de cliente
public function updateCobranza2($contract,$payday,$min_pay,$paydate,$payment_period)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE collection_data SET payday= :payday,min_pay= :min_pay, paydate= :paydate,txtpayment_period= :payment_period WHERE contract= :cont");
$stmt->bindParam("cont",$contract);
$stmt->bindParam("payday",$payday);
$stmt->bindParam("min_pay",$min_pay);
$stmt->bindParam("paydate",$paydate);
$stmt->bindParam("payment_period",$payment_period);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Actualizado datos adicionales de Empleo de cliente!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//buscar una categoria
public function getCategoria($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from category a where a.id = :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;
}
}
//eliminar categoria
public function delCategory($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from category where id = :id");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//buscar categoria
public function autoCompleteCat($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM category a WHERE name like :term order by name asc limit 10");
$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;
}
}
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");
$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;
}
}
//
//todas las empleados
public function getListEmployees()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT *,date_format(dateUp, '%d-%m-%Y') as dateUp, date_format(dateBaja, '%d-%m-%Y') as dateBaja, date_format(dateReUp, '%d-%m-%Y') as dateReUp FROM `employee` where status > 0 order by contract asc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//todas las empleados
public function getListEmployeesRoutes()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT emp.*,rou.id as indice,rou.idruta,rou.name as ruta, rou.description as descriptionR FROM `employee` emp INNER JOIN routes rou ON emp.route_id = rou.id where emp.status > 0 and emp.route_id > 0 and rou.status != 1 order by rou.idruta asc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//insertar payment enganche
/*
INSERT INTO `payment` (`id`, `payment_type_id`, `sale_id`, `person_id`, `week`, `val`, `pay_date`, `id_collector`,
`programmed`, `created_at`) VALUES ('89', '1', '23424234', '578757', NULL, '350', '2024-05-18 06:35:44.000000', NULL,
'1', current_timestamp());
*/
public function saveEnganche($contract,$fecha,$val)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into payment(payment_type_id, person_id, val, pay_date, programmed)
values(1,:contract,:val,:fecha,1)");
$stmt->bindParam("contract",$contract);
$stmt->bindParam("val",$val);
$stmt->bindParam("fecha",$fecha);
$stmt->execute();
$stat[0] = true;
$stat[1] = "save enganche!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//guardar enganche efectivo
public function saveEnganche2($contract,$sale_id,$fecha,$val)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into payment(payment_type_id, person_id,sale_id, val, pay_date, programmed)
values(1,:contract,:sale_id,:val,:fecha,0)");
$stmt->bindParam("contract",$contract);
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("val",$val);
$stmt->bindParam("fecha",$fecha);
$stmt->execute();
$stat[0] = true;
$stat[1] = "save enganche!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizar enganche
public function updateEnganche($contract,$sale_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE `payment` SET `sale_id` = :sale_id WHERE `payment`.`person_id` = :contract;");
$stmt->bindParam("contract",$contract);
$stmt->bindParam("sale_id",$sale_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "update enganche!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//registrar nuevo empleado
public function saveEmployee($name,$contract,$dateA,$ruta,$poblacion,$telefono,$puesto,$date_emp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into employee(name,contract,dateUp,id_ruta,poblacion,phone,id_puesto,date_emp)
values(:name,:contract,:dateA,:ruta,:poblacion,:telefono,:puesto,:date_emp)");
$stmt->bindParam("name",$name);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("dateA",$dateA);
$stmt->bindParam("ruta",$ruta);
$stmt->bindParam("poblacion",$poblacion);
$stmt->bindParam("telefono",$telefono);
$stmt->bindParam("puesto",$puesto);
$stmt->bindParam("date_emp",$date_emp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Sukses save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion empleado BAJA
public function updateEmployee($iditem,$name,$contract,$dateBaja,$poblacion,$telefono,$baja,$date_emp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update employee set name= :value, contract=:contract,dateBaja=:dateBaja,id_ruta=NULL,route_id=NULL,poblacion=:poblacion,phone=:telefono,status=:baja,date_emp=:date_emp where id_cust= :iditem");
$stmt->bindParam("value",$name);
$stmt->bindParam("iditem",$iditem);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("dateBaja",$dateBaja);
$stmt->bindParam("poblacion",$poblacion);
$stmt->bindParam("telefono",$telefono);
$stmt->bindParam("baja",$baja);
$stmt->bindParam("date_emp",$date_emp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit baja!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion empleado Reingreso
public function updateEmployeeRe($name,$contract,$dateA,$ruta,$poblacion,$telefono,$ReUp,$date_emp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update employee set contract=:contract,dateReUp=:dateA,id_ruta=:ruta,poblacion=:poblacion,phone=:telefono,status=:ReUp,date_emp=:date_emp where name= :value");
$stmt->bindParam("value",$name);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("dateA",$dateA);
$stmt->bindParam("ruta",$ruta);
$stmt->bindParam("poblacion",$poblacion);
$stmt->bindParam("telefono",$telefono);
$stmt->bindParam("ReUp",$ReUp);
$stmt->bindParam("date_emp",$date_emp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion empleado EDITAR
public function updateEmployeeEditar($iditem,$name,$contract,$dateA,$ruta,$poblacion,$telefono,$puesto,$date_emp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update employee set name= :value, contract=:contract,dateUp=:dateA,id_ruta=:ruta,poblacion=:poblacion,phone=:telefono,id_puesto=:puesto,date_emp=:date_emp where id_cust= :iditem");
$stmt->bindParam("value",$name);
$stmt->bindParam("iditem",$iditem);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("dateA",$dateA);
$stmt->bindParam("ruta",$ruta);
$stmt->bindParam("poblacion",$poblacion);
$stmt->bindParam("telefono",$telefono);
$stmt->bindParam("puesto",$puesto);
$stmt->bindParam("date_emp",$date_emp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion empleado ruta id EDITAR
public function updateEmployeeRouteId($employee_id,$id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update employee set route_id=:id where id_cust= :iditem");
$stmt->bindParam("iditem",$employee_id);
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit! cambio de ruta";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//eliminar empleado permanente
public function delempleyee($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from employee where id_cust = :id");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success delete employe!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//Baja de Empleado
public function updateBajaEmploye($iditem,$name,$contract,$dateA,$ruta,$poblacion,$telefono,$puesto,$date_emp)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update employee set name= :value, contract=:contract,dateUp=:dateA,id_ruta=:ruta,poblacion=:poblacion,phone=:telefono,id_puesto=:puesto,date_emp=:date_emp where contract= :iditem");
$stmt->bindParam("value",$name);
$stmt->bindParam("iditem",$iditem);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("dateA",$dateA);
$stmt->bindParam("ruta",$ruta);
$stmt->bindParam("poblacion",$poblacion);
$stmt->bindParam("telefono",$telefono);
$stmt->bindParam("puesto",$puesto);
$stmt->bindParam("date_emp",$date_emp);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success baja employe!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//todas las empleados datos de baja
public function getListEmployeesBaja()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT emp.id_cust,emp.contract,emp.id_ruta, cus.name,cus.reason,cus.id_puesto,date_format(cus.created_at, '%d-%m-%Y') as created_at FROM `employee` emp left JOIN employee_terminated cus ON cus.name = emp.name where emp.status = 0 order by emp.id_cust asc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//insertar valores terminated_employeee\
public function saveTerminatedEmp($name,$id_puesto,$reason,$created_at)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into employee_terminated(name ,id_puesto, reason, created_at) values
( :name ,:id_puesto, :reason, :created_at)");
$stmt->bindParam("name",$name);
$stmt->bindParam("id_puesto",$id_puesto);
$stmt->bindParam("reason",$reason);
$stmt->bindParam("created_at",$created_at);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save term_emp!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//coleection empleados
public function autoCompleteCollector($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM employee a WHERE name like :term AND status > 0 order by name desc");
$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;
}
}
//coleection empleados
public function autoCompleteEmpleados($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM employee a WHERE name like :term order by name desc");
$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;
}
}
//coleection empleados todos
public function autoCompleteEmpleadosAll($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM employee a WHERE name like :term order by name desc");
$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;
}
}
//cambiar ruta
public function changeEmployee($newCollectorId,$saleId){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update `t_sale` set id_collector=:id where sale_id= :sale");
$stmt->bindParam("id",$newCollectorId);
$stmt->bindParam("sale",$saleId);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//cambiar status deliver_sts
public function changeDeliverSts($newStsId,$saleId){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update `t_sale` set deliver_sts=:id where sale_id= :sale");
$stmt->bindParam("id",$newStsId);
$stmt->bindParam("sale",$saleId);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//buscar repartidores libres
public function getAviableEmployees($id){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select id_cust,name from employee where id_cust != :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;
}
}
//buscar empleado x id
public function getEmployee($id_cust){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select *, date_format(date_emp, '%d-%m-%Y') as date_emp from employee where id_cust = :id_cust and id_puesto>0");
$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;
}
}
//buscar empleado x id ruta
public function getEmployeeRoutes($route_id){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select id_cust,route_id,name from employee where route_id = :id and status >0");
$stmt->bindParam("id",$route_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;
}
}
//buscar empleado por contrato de cliente por id de cobranza
//SELECT cli.colector_id,emp.name,emp.id_ruta FROM `customer` cli INNER JOIN employee emp ON cli.colector_id = emp.id_cust WHERE cli.`contract` = "156789";
public function getEmployeeCustomer($contract){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("SELECT cli.colector_id,emp.name,emp.route_id FROM `customer` cli INNER JOIN employee emp ON cli.colector_id = emp.id_cust WHERE cli.`contract` = :contract");
$stmt->bindParam("contract",$contract);
$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;
}
}
//buscar empleado x id
public function getRoutes1($name){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("SELECT * FROM `routes` WHERE `name` = :id");
$stmt->bindParam("id",$name);
$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;
}
}
//buscar empleado x id
public function getRoutesId($id){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("SELECT * FROM `routes` WHERE `idruta` = :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;
}
}
//autocompletado creditos
public function autoCompleteCredit($term)
{
$trm = "%".$term."%";
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM t_sale a WHERE id_client like :term and is_credit = 1 order by sale_date");
$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 save_item($name,$category_id,$codebar,$stock,$unit,$price,$price_cont){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveItem(:barcode,:name,:unit,:stock,:price,:note,:category_id)");
$stmt->bindParam("barcode",$codebar);
$stmt->bindParam("name",$name);
$stmt->bindParam("price",$price);
$stmt->bindParam("note",$price_cont);
$stmt->bindParam("unit",$unit);
$stmt->bindParam("stock",$stock);
$stmt->bindParam("category_id",$category_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;
}
}
/*******************************************************************************
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
*******************************************************************************/
public function getCustomer($id_item){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select cli.*,cob.name as cobrador from customer cli INNER JOIN employee cob ON cli.colector_id = cob.id_cust where cli.contract = :contract ");
$stmt->bindParam("contract",$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;
}
}
//insertar valores customer terminated\
public function saveTerminatedCustomer($contract,$reason,$fechab)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into customer_terminated(contract , reason, created_at) values ( :contract , :reason, :fechab)");
$stmt->bindParam("contract",$contract);
$stmt->bindParam("reason",$reason);
$stmt->bindParam("fechab",$fechab);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success save customer_terminated!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
// empleado por contrato
public function getEmployeeContract($contract){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select * from employee where contract = :contract ");
$stmt->bindParam("contract",$contract);
$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;
}
}
//buscar cliente por contrato rapido
public function getCustomerCobro($id_item){
$db = $this->dblocal;
try
{
$stmt =$db->prepare("select name,direccion,come_in from customer where contract = :contract ");
$stmt->bindParam("contract",$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;
}
}
/******************************************************************************
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 borrado purchase_order!";
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;
}
}
//mercancia que saldra a transito
public function getListTempTransito(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT *,date_format(input_date, '%d-%m-%Y') as input_date FROM purchase_order ORDER BY input_date 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;
}
}
//ultimo folio de entrega de mercancia a ventas
public function getFolioUltimoSalidaM(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT MAX(re_id) as max FROM operation2 where operation_type_id = 2 ");
$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;
}
}
//ultimo vendedor que se le entrego mercancia
public function getSalidaUltimaMXF($folio){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.*,date_format(a.created_at,'%d-%m-%Y') as created_at,em.name FROM `re` a INNER JOIN employee em ON a.employee_id = em.id_cust WHERE `id` = :id");
$stmt->bindParam("id",$folio);
$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;
}
}
//reporte a transito
public function getReporteTransito(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT OP.*,date_format(OP.created_at, '%d-%m-%Y') as created_at,OP.q,OP.operation_type_id FROM `operation2` OP INNER JOIN re R ON OP.re_id = R.id WHERE R.is_draft = 5 AND DATE(OP.created_at) = DATE(NOW()) AND OP.operation_type_id = 2 ");
$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;
}
}
//SELECT `q` as entradas FROM `operation2` WHERE `barcode` = '3456' AND `re_id` = '3242' AND `operation_type_id` = 1;
public function getReporteTransito_IN($barcode,$re_id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT barcode,`q` FROM `operation2` WHERE `barcode` = :barcode AND `re_id` = :re_id AND `operation_type_id` = 1 AND DATE(created_at) = DATE(NOW()) ");
$stmt->bindParam("barcode",$barcode);
$stmt->bindParam("re_id",$re_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;
}
}
//reporte a transito SALIDAS
public function getReporteTransitoREC_OUT(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT OP.*,date_format(OP.created_at, '%d-%m-%Y') as created_at,OP.q,OP.operation_type_id FROM `operation2` OP INNER JOIN re R ON OP.re_id = R.id WHERE R.is_draft = 6 AND OP.operation_type_id = 2 ");
$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;
}
}
//reporte a transito SALIDAS
public function getReporteTransitoREC_IN(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT OP.*,date_format(OP.created_at, '%d-%m-%Y') as created_at,OP.q AS IN,OP.operation_type_id FROM `operation2` OP INNER JOIN re R ON OP.re_id = R.id WHERE R.is_draft = 6 AND DATE(OP.created_at) = DATE(NOW()) AND OP.operation_type_id = 1 ");
$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;
}
}
//mercancia recibida por vendedor desde ventas //entrada
public function getOperationReRecibida($re_id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT op.*,prod.item_name FROM `operation2` op INNER JOIN m_item prod ON op.barcode = prod.barcode WHERE `re_id` = :id and `operation_type_id` = 1 ORDER BY op.created_at asc");
$stmt->bindParam("id",$re_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;
}
}
//mercancia recibida por vendedor desde almacen //salida
public function getOperationReEntregada($re_id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT op.*,prod.item_name FROM `operation2` op INNER JOIN m_item prod ON op.barcode = prod.barcode WHERE `re_id` = :id and `operation_type_id` = 2 ORDER BY op.created_at asc");
$stmt->bindParam("id",$re_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;
}
}
/*
SELECT op.*,prod.item_name FROM `operation2` op INNER JOIN m_item prod ON op.barcode = prod.barcode WHERE `re_id` = 6175 and `operation_type_id` = 1 and (date_format(op.created_at, '%d-%m-%Y') BETWEEN "07-10-2023" AND "07-10-2023");
*/
//mercancia recibida por vendedor y por fecha desde ventas
//getOperationReRecibidaFecha
public function getOperationReRecibidaFecha($fecha,$re_id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT op.*,prod.item_name FROM `operation2` op INNER JOIN m_item prod ON op.barcode = prod.barcode WHERE `re_id` = :id and `operation_type_id` = 1 and (date_format(op.created_at, '%d-%m-%Y') = :fecha) ");
$stmt->bindParam("fecha",$fecha);
$stmt->bindParam("id",$re_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;
}
}
//reporte mercancia recibida
public function getReporteMercanciaR(){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT *,date_format(created_at, '%d-%m-%Y') as created_at FROM operation2 where operation_type_id = 1 and is_draft = 1 and DATE(created_at) = DATE(NOW()) ORDER BY created_at 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 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 deleteTempPurchaseProduct($cashier,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from purchase_order where id_user = :id and id_item = :id_item ");
$stmt->bindParam("id",$cashier);
$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($value,$id_item)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE `purchase_order` SET `qty` = :valor WHERE `purchase_order`.`id_user` = 6 AND `purchase_order`.`id_item` = :id_item ");
$stmt->bindParam("valor",$value);
$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 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,$id_item,$barcode,$unit,$item_name,$qty,$price,$discprn,$discrp)
{
$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->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 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,$id_item,$barcode,$unit,$item_name,$qty,$price,$discprn,$discrp)
{
$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->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 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 saveSaleCredit($sale_id,$sale_date,$total,$paid,$disc_prcn,$disc_rp,$uniqid,$id_user,$note,$f_pago1,$f_pago2,$id_seller,$person_id, $collector,$re_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("call saveSaleCredit( :sale_id, :sale_date, :total, :paid, :disc_prcn,
:disc_rp, :uniqid, :id_user, :note, :f_pago1, :f_pago2, :id_seller, :person_id, :collector,:re_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("collector",$collector);
$stmt->bindParam("re_id",$re_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;
}
}
//salida de mercancia a ventas en transito
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;
}
}
//salida a reparto para venta por parte de repartidor
public function saveSale6($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 saveSale6( :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;
}
}
//registramos la salida a reparto
public function getSubTotalTempSaleOC2($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;
}
}
//checar temp sale
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;
}
}
//getFolioTranOperation
public function getFolioTranOperation($re_id){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `t_sale_detail` WHERE `re_id` = :re_id");
$stmt->bindParam("re_id",$re_id);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->rowCount();
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
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 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;
}
}
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.* from m_user a where upper(a.username)=upper(:user) and a.pass_user=md5(:pass)");
$stmt->bindParam("user",$user);
$stmt->bindParam("pass",$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();
}
//get user item
public function getUserId($user_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.* from m_user a where id_user=:id_user");
$stmt->bindParam("id_user",$user_id);
$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;
}
}
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;
}
}
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;
}
}
/*********************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,$collector_id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("insert into m_user(username,pass_user,h_menu,stock_id,employee_id)
values(:name,MD5(:pass),:hmenu,:collector_id)");
$stmt->bindParam("name",$username);
$stmt->bindParam("pass",$pass_user);
$stmt->bindParam("hmenu",$h_menu);
$stmt->bindParam("stock_id",$stock_id);
$stmt->bindParam("collector_id",$collector_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;
}
}
//agregamos la ruta pendiente a su tabla correspondiente como status 0 pendiente de cobrar
public function new_route_pending($sale_id,$contract,$date_sale,$total,$pay_min,$pay_period,$balance,$enganche,$date_pay1,$first_pay,$collector_id,$status)
{
$db = $this->dblocal;
try
{
$kind=2;
$stmt = $db->prepare("INSERT INTO `pending_route` (`sale_id`, `contract`, `date_sale`, `total`, `pay_min`, `pay_period`, `balance`, `enganche`, `date_pay1`, `payday`, `collector_id`, `status`) VALUES (:sale_id, :contract, :date_sale, :total, :pay_min, :pay_period, :balance, :enganche, :date_pay1, :first_pay, :collector_id, :status) ");
$stmt->bindParam("sale_id",$sale_id);
$stmt->bindParam("contract",$contract);
$stmt->bindParam("date_sale",$date_sale);
$stmt->bindParam("total",$total);
$stmt->bindParam("pay_min",$pay_min);
$stmt->bindParam("pay_period",$pay_period);
$stmt->bindParam("balance",$balance);
$stmt->bindParam("enganche",$enganche);
$stmt->bindParam("date_pay1",$date_pay1);
$stmt->bindParam("first_pay",$first_pay);
$stmt->bindParam("collector_id",$collector_id);
$stmt->bindParam("status",$status);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Registro exitoso a ruta";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
/******************************************************************************
END OF OPERATION2 CODE
*******************************************************************************/
/******************************************************************************
init OF routes CODE
*******************************************************************************/
//todas las routes
public function getListRoutes()
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * FROM `routes` WHERE status = 0 order by id asc");
$stmt->execute();
$stat[0] = true;
$stat[1] = "List OK";
$stat[2] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//select a.*,emp.route_id,emp.name as cobrador from routes a INNER JOIN employee emp ON a.id = emp.route_id where a.id = 2;
//buscar una routa
public function getRuta($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("select a.*,e.id_cust,e.name as empleado,e.route_id from routes a JOIN employee E ON a.id = e.route_id where a.id = :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;
}
}
//consultar ruta sin asignar cobrador
//SELECT * FROM `routes` WHERE `id` = 4;
public function getIdRoute($id_item){
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT a.* FROM `routes` WHERE a.id = :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;
}
}
//nueva ruta
public function new_route($indice,$name,$country,$created_at)
{
$db = $this->dblocal;
try
{
$kind=2;
$stmt = $db->prepare("insert into routes(id,name,description,status ,created_at) values (:indice,:name,:country,0, :created_at)");
$stmt->bindParam("indice",$indice);
$stmt->bindParam("name",$name);
$stmt->bindParam("country",$country);
$stmt->bindParam("created_at",$created_at);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Registro De ruta";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion ruta completa
public function editRoute($nameitem,$id,$description)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update routes set name= :value, description=:description where id= :id");
$stmt->bindParam("value",$nameitem);
$stmt->bindParam("id",$id);
$stmt->bindParam("description",$description);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//actualizacion solo ciudad
public function editRoute2($id,$idruta,$name,$description)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("update routes set idruta=:idruta, name=:name, description=:description where id= :id");
$stmt->bindParam("id",$id);
$stmt->bindParam("idruta",$idruta);
$stmt->bindParam("name",$name);
$stmt->bindParam("description",$description);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success Edit!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//baja de ruta
public function updaterouteB($id,$dateB)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE `routes` SET `status` = b'1', dateB = :dateB WHERE `routes`.`id` = :id");
$stmt->bindParam("id",$id);
$stmt->bindParam("dateB",$dateB);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success baja route!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//updaterouteAlta
public function updaterouteAlta($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("UPDATE `routes` SET `status` = b'0' WHERE `routes`.`id` = :id");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success alta route!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//eliminar categoria
public function delroute($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("delete from routes where id = :id");
$stmt->bindParam("id",$id);
$stmt->execute();
$stat[0] = true;
$stat[1] = "Success delete!";
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
return $stat;
}
}
//generar estado de cuenta de cliente por id
public function getReport($id_cust)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT
cust.id_cust,cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion,
cust.contract,ts.sale_id, ts.total,
ts.disc_rp ,date_format(ts.sale_date, '%d-%m-%Y') as sale_date,ts.id_collector AS id_collector,
emp.name AS cobrador, ts.total+ ts.disc_rp AS saldo_inicial,
SUM(pay.val) AS suma_valores_payments,
(ts.total+ ts.disc_rp) - SUM(pay.val) AS saldo_actual,
date_format(MAX(pay.created_at),'%d-%m-%Y') AS ultima_fecha_pago,( DATEDIFF(NOW(),
MAX(pay.pay_date)) DIV 7) * cd.min_pay AS valor_por_pagar, cd.min_pay AS pago_minimo,
cd.txtpayment_period as periodo, date_format(cd.paydate,'%d-%m-%Y') as fechaPrimer,
( DATEDIFF(NOW(), MAX(pay.pay_date)) DIV 7) as semanas_atrazo,
cust.status as EstadoContrato FROM customer AS cust
LEFT JOIN t_sale as ts ON cust.contract=ts.id_client
LEFT JOIN payment AS pay ON ts.sale_id = pay.sale_id
LEFT JOIN collection_data AS cd ON cd.contract = cust.contract
LEFT JOIN employee AS emp ON ts.id_collector = emp.id_cust
WHERE pay.payment_type_id = 1
AND cust.id_cust = :id_cust
GROUP BY
cust.id_cust, cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion, cust.contract, ts.sale_id, ts.total,ts.disc_rp;
");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//generar estado de cuenta de cliente por id ENGANCHE PROGRAMADO CON PAGOS
public function getReportP($id_cust)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT
cust.id_cust,cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion,
cust.contract,ts.sale_id, ts.total,
ts.disc_rp ,date_format(ts.sale_date, '%d-%m-%Y') as sale_date,ts.id_collector AS id_collector,
emp.name AS cobrador, ts.total+ ts.disc_rp AS saldo_inicial,
SUM(pay.val) AS suma_valores_payments,
(ts.total+ ts.disc_rp) - SUM(pay.val) AS saldo_actual,
date_format(MAX(pay.created_at),'%d-%m-%Y') AS ultima_fecha_pago,( DATEDIFF(NOW(),
MAX(pay.created_at)) DIV 7) * cd.min_pay AS valor_por_pagar, cd.min_pay AS pago_minimo,
cd.txtpayment_period as periodo, date_format(cd.paydate,'%d-%m-%Y') as fechaPrimer,
( DATEDIFF(NOW(), MAX(pay.created_at)) DIV 7) as semanas_atrazo,
cust.status as EstadoContrato FROM customer AS cust
LEFT JOIN t_sale as ts ON cust.contract=ts.id_client
LEFT JOIN payment AS pay ON ts.sale_id = pay.sale_id
LEFT JOIN collection_data AS cd ON cd.contract = cust.contract
LEFT JOIN employee AS emp ON ts.id_collector = emp.id_cust
WHERE pay.payment_type_id = 2
AND cust.id_cust = :id_cust
GROUP BY
cust.id_cust, cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion, cust.contract, ts.sale_id, ts.total,ts.disc_rp;
");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
public function getReportEfectivo($id_cust)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT
cust.id_cust,cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion,
cust.contract,ts.sale_id, ts.total,
ts.disc_rp ,date_format(ts.sale_date, '%d-%m-%Y') as sale_date,ts.id_collector AS id_collector,
emp.name AS cobrador, ts.total+ ts.disc_rp AS saldo_inicial,
SUM(pay.val) AS suma_valores_payments,
(ts.total+ ts.disc_rp) - SUM(pay.val) AS saldo_actual,
date_format(MAX(pay.created_at),'%d-%m-%Y') AS ultima_fecha_pago,( DATEDIFF(NOW(),
MAX(pay.created_at)) DIV 7) * cd.min_pay AS valor_por_pagar, cd.min_pay AS pago_minimo,
cd.txtpayment_period as periodo, date_format(cd.paydate,'%d-%m-%Y') as fechaPrimer,
( DATEDIFF(NOW(), MAX(pay.created_at)) DIV 7) as semanas_atrazo,
cust.status as EstadoContrato FROM customer AS cust
LEFT JOIN t_sale as ts ON cust.contract=ts.id_client
LEFT JOIN payment AS pay ON ts.sale_id = pay.sale_id
LEFT JOIN collection_data AS cd ON cd.contract = cust.contract
LEFT JOIN employee AS emp ON ts.id_collector = emp.id_cust
WHERE pay.payment_type_id = 1
AND cust.id_cust = :id_cust
GROUP BY
cust.id_cust, cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion, cust.contract, ts.sale_id, ts.total,ts.disc_rp;
");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//generar estado de cuenta de cliente por contrato
public function getReportContract($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT
cust.id_cust,cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion, cust.contract,ts.sale_id, ts.total,
ts.disc_rp ,date_format(ts.sale_date, '%d-%m-%Y') as sale_date,ts.id_collector AS id_collector, emp.name AS cobrador, ts.total+ ts.disc_rp AS saldo_inicial,SUM(pay.val) AS suma_valores_payments,(ts.total+ ts.disc_rp) - SUM(pay.val) AS saldo_actual,
date_format(MAX(pay.created_at),'%d-%m-%Y') AS ultima_fecha_pago,( DATEDIFF(NOW(), MAX(pay.created_at)) DIV 7) * cd.min_pay AS valor_por_pagar, cd.min_pay AS pago_minimo, cd.txtpayment_period as periodo, date_format(cd.paydate,'%d-%m-%Y') as fechaPrimer,
( DATEDIFF(NOW(), MAX(pay.created_at)) DIV 7) as semanas_atrazo
FROM customer AS cust
JOIN t_sale as ts ON cust.contract=ts.id_client
LEFT JOIN payment AS pay ON ts.sale_id = pay.sale_id
LEFT JOIN collection_data AS cd ON cd.contract = cust.contract
LEFT JOIN employee AS emp ON ts.id_collector = emp.id_cust
WHERE pay.payment_type_id = 1
AND cust.contract = :contract
GROUP BY
cust.id_cust, cust.name, cust.conyugue, cust.direccion, cust.come_in, cust.poblacion, cust.contract, ts.sale_id, ts.total,ts.disc_rp;
");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//obtener los avales por id de cliente
public function getEndorsement($id_cust)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT *
from endorsement where customer_id = :id_cust");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//obtener aval dos todos sus datos por id de cliente
public function getEndorsementAval2($id)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT * from endorsement 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();
$stat[2] = [];
return $stat;
}
}
//obtener los avales por contrato
public function getEndorsementC($id_cust)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT *
from endorsement
where contract = :id_cust
");
$stmt->bindParam("id_cust",$id_cust);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//obtener los avales por contrato solo aval q
//SELECT *,MIN(`id`) AS aval1 FROM `endorsement` WHERE `contract` = '129999';
public function getEndorsementContract1($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT *,MIN(`id`) AS aval1 FROM `endorsement` WHERE `contract` = :contract ");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//agregar aval 2
public function getEndorsementContract2($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("SELECT max(`id`) as aval2 FROM `endorsement` WHERE `contract` = :contract");
$stmt->bindParam("contract",$contract);
$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] = [];
return $stat;
}
}
// obtenemos la mercancia de la venta por sale_id
public function getSaleDetails($saleId)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare(" SELECT barcode, item_name, qty, price, disc_rp
from t_sale_detail
where sale_id = :sale_id
");
$stmt->bindParam("sale_id",$saleId);
$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] = [];
return $stat;
}
}
// obtenemos la mercancia de la venta por sale_id
public function getSaleDetails2($saleId)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare(" SELECT barcode, item_name, qty, price, disc_rp
from t_sale_detail
where sale_id = :sale_id
");
$stmt->bindParam("sale_id",$saleId);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
public function getPayments($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT sum( abs(`val`)) as saldo FROM `payment` WHERE `person_id` = :contract and `sale_id` is null and `programmed` is null;
");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
public function getPaymentsAbonos($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT *,date_format(created_at, '%d-%m-%Y') as created_at FROM `payment` WHERE `person_id` = :contract and `sale_id` is null and `programmed` is null;
");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//contador de pagos
public function getPaymentsCount($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT COUNT(*) as contador FROM `payment` WHERE `person_id` = :contract and `sale_id` is null and `programmed` is null;
");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//enganche programado
public function EnganchePaymentProgramado($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT *,date_format(pay_date, '%d-%m-%Y') as pay_date FROM `payment` WHERE `person_id` = :contract and `programmed` = 1;
");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
//enganche programado PAGADO
public function EnganchePaymentProgPAG($contract)
{
$db = $this->dblocal;
try
{
$stmt = $db->prepare("
SELECT *,date_format(pay_date, '%d-%m-%Y') as pay_date FROM `payment` WHERE `person_id` = :contract and `programmed` = 2;
");
$stmt->bindParam("contract",$contract);
$stmt->execute();
$stat[0] = true;
$stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $stat;
}
catch(PDOException $ex)
{
$stat[0] = false;
$stat[1] = $ex->getMessage();
$stat[2] = [];
return $stat;
}
}
}
?>