API tools faq
paste
fadlyshafa

Untitled

fadlyshafa
Apr 5th, 2020
653
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 36.94 KB
raw download clone embed print report
  1. <?php
  2.  
  3. defined('BASEPATH') OR exit('No direct script access allowed');
  4.  
  5. class Sales_model extends CI_Model {
  6.  
  7.     function __construct() {
  8.         parent::__construct();
  9.     }
  10.  
  11.     /*
  12.       return all sales details to display list
  13.      */
  14.  
  15.     public function get_sales_customer($customer_id){
  16.         return $this->db->select('s.customer_id,s.date,s.reference_no,si.quantity,sum(si.quantity) as total_qty,sum(si.gross_total) as total')
  17.                 ->from('sales s')
  18.                 ->join('customer c','s.customer_id = c.customer_id','left')
  19.                 ->join('sales_items si','si.sales_id = s.sales_id','left')
  20.                 ->where('s.customer_id = ',$customer_id)
  21.                 ->group_by('s.date')
  22.                 ->get()
  23.                 ->result();
  24.     }
  25.  
  26.     public function get_sales_customer_filter($customer_id,$awal,$akhir){
  27.         return $this->db->select('s.customer_id,s.date,s.reference_no,si.quantity,sum(si.quantity) as total_qty,sum(si.gross_total) as total')
  28.                 ->from('sales s')
  29.                 ->join('customer c','s.customer_id = c.customer_id','left')
  30.                 ->join('sales_items si','si.sales_id = s.sales_id','left')
  31.                 ->where('s.customer_id = ',$customer_id)
  32.                 ->where('date >=',$awal)
  33.                 ->where('date <= ',$akhir)
  34.                 ->group_by('s.date')
  35.                 ->get()
  36.                 ->result();
  37.     }
  38.  
  39.     public function getSales2(){
  40.         return $this->db->select('s.*,b.*,c.*,i.*,s.sales_id as sales_id')
  41.                 ->from('sales s')
  42.                 ->join('biller b', 's.biller_id=b.biller_id', 'left')
  43.                 ->join('customer c', 's.customer_id=c.customer_id', 'left')
  44.                 ->join('invoice i ', 's.sales_id=i.sales_id', 'left')
  45.                 ->order_by('s.currentdate', 'DESC');
  46.     }
  47.  
  48.     public function getSales_detail($sales_id){
  49.         return $this->db->select('s.*,b.*,c.*,i.*,s.sales_id as sales_id')
  50.                 ->from('sales s')
  51.                 ->join('biller b', 's.biller_id=b.biller_id', 'left')
  52.                 ->join('customer c', 's.customer_id=c.customer_id', 'left')
  53.                 ->join('invoice i ', 's.sales_id=i.sales_id', 'left')
  54.                 ->where('s.sales_id = ',$sales_id)
  55.                 ->order_by('s.currentdate', 'DESC');
  56.     }
  57.  
  58.     public function get_sales_line($sales_id){
  59.         return $this->db->select('s.*,b.*,c.*,i.*,s.sales_id as sales_id,products.name as nama_produk,si.quantity,products.unit,si.price,si.tax,si.gross_total')
  60.                     ->from('sales s')
  61.                     ->join('sales_items si', 'si.sales_id=s.sales_id', 'left')
  62.                     ->join('products', 'products.product_id=si.product_id', 'left')
  63.                     ->join('biller b', 's.biller_id=b.biller_id', 'left')
  64.                     ->join('customer c', 's.customer_id=c.customer_id', 'left')
  65.                     ->join('invoice i ', 's.sales_id=i.sales_id', 'left')
  66.                     ->where('products.quantity >=', 'si.quantity')
  67.                     ->group_by('s.sales_id')
  68.                     ->order_by('s.currentdate', 'DESC');
  69.     }
  70.  
  71.     public function getSales($filter) {
  72.         if ($filter != null) {
  73.             if ($filter == "order_ready_to_package") {
  74.                 $this->db->select('s.*,b.*,c.*,i.*,s.sales_id as sales_id')
  75.                     ->from('sales s')
  76.                     ->join('sales_items', 'sales_items.sales_id=s.sales_id', 'left')
  77.                     ->join('products', 'products.product_id=sales_items.product_id', 'left')
  78.                     ->join('biller b', 's.biller_id=b.biller_id', 'left')
  79.                     ->join('customer c', 's.customer_id=c.customer_id', 'left')
  80.                     ->join('invoice i ', 's.sales_id=i.sales_id', 'left')
  81.                     ->where('products.quantity >=', 'sales_items.quantity')
  82.                     ->group_by('s.sales_id')
  83.                     ->order_by('s.currentdate', 'DESC');
  84.             } else {
  85.                 $this->db->select('s.*,b.*,c.*,i.*,s.sales_id as sales_id')
  86.                     ->from('sales s')
  87.                     ->join('biller b', 's.biller_id=b.biller_id', 'left')
  88.                     ->join('customer c', 's.customer_id=c.customer_id', 'left')
  89.                     ->join('invoice i ', 's.sales_id=i.sales_id', 'left')
  90.                     ->where("s.status_rev", $filter)
  91.                     ->order_by('s.currentdate', 'DESC');
  92.             }
  93.         } else {
  94.         $this->db->select('s.*,b.*,c.*,i.*,s.sales_id as sales_id')
  95.                 ->from('sales s')
  96.                 ->join('biller b', 's.biller_id=b.biller_id', 'left')
  97.                 ->join('customer c', 's.customer_id=c.customer_id', 'left')
  98.                 ->join('invoice i ', 's.sales_id=i.sales_id', 'left')
  99.                 ->order_by('s.currentdate', 'DESC');
  100.             }
  101.         return $this->db->get()->result();
  102.     }
  103.  
  104.     public function getProducts2() {
  105.         return $this->db->get('products')->result();
  106.     }
  107.  
  108.     public function getProducts3() {
  109.         return $this->db->get('product_grouping')->result();
  110.     }
  111.  
  112.     public function getService() {
  113.         return $this->db->get('service')->result();
  114.     }
  115.  
  116.     /*
  117.       return warehouse detail use drop down
  118.      */
  119.  
  120.     public function getWarehouse() {
  121.         if ($this->session->userdata('type') == "1") {
  122.             return $this->db->get('warehouse')->result();
  123.         } else {
  124.             $this->db->select('w.*')
  125.                     ->from('warehouse w')
  126.                     ->join('warehouse_management wm', 'wm.warehouse_id = w.warehouse_id', 'left')
  127.                     ->where('wm.user_id', $this->session->userdata('user_id'));
  128.             return $this->db->get()->result();
  129.         }
  130.     }
  131.  
  132.     /*
  133.       return warehouse details available in warehouse products
  134.      */
  135.  
  136.     public function getWarehouseProducts() {
  137.         $this->db->select('warehouse.warehouse_id,warehouses_products.product_id,quantity')
  138.                 ->from('warehouse')
  139.                 ->join('warehouses_products', 'warehouse.warehouse_id = warehouses_products.warehouse_id', 'left');
  140.         return $this->db->get()->result();
  141.     }
  142.  
  143.     /*
  144.       return biller detail use drop down
  145.      */
  146.  
  147.     public function getBiller() {
  148.         return $this->db->get('biller')->result();
  149.     }
  150.  
  151.     public function insert($table,$array)
  152.     {
  153.         return $this->db->insert($table,$array);
  154.     }
  155.  
  156.     /*
  157.       return customer detail use drop down
  158.      */
  159.  
  160.     public function getCustomer() {
  161.         return $this->db->get('customer')->result();
  162.     }
  163.  
  164.     public function getQuatation() {
  165.         return $this->db->get('quotation')->result();
  166.     }
  167.  
  168.  
  169.     /*
  170.       return discount detail use drop down
  171.      */
  172.  
  173.     public function getDiscount() {
  174.         return $this->db->get('discount')->result();
  175.     }
  176.  
  177.     public function getBonus12() {
  178.         return $this->db->get('bonus_items')->result();
  179.     }
  180.  
  181.     public function getBonus($product_id) {
  182.         $this->db->select('si.*')
  183.             ->from('bonus_items si')
  184.             ->join('products p', 'si.product = p.product_id', 'left')
  185.             ->where('si.product', $product_id);
  186.         if ($query = $this->db->get()) {
  187.             return $query->result();
  188.         } else {
  189.             return FALSE;
  190.         }
  191.     }
  192.  
  193.     public function getBonus1($product_id) {
  194.         return $this->db->select('p.*, d.id as product_grouping, d.list')
  195.                         ->from('products p')
  196.                         ->join('product_grouping d', 'd.product_id = p.product_id', 'left')
  197.                         ->join('bonus_items bi', 'bi.product = p.product_id','left')
  198.                         ->where('p.product_id',$product_id)
  199.                         ->get()
  200.                         ->result();
  201.     }
  202.  
  203.  
  204.     /*
  205.       return tax detail use dynamic table
  206.      */
  207.  
  208.     public function getTax() {
  209.         return $this->db->get_where('tax', array('delete_status' => 0))->result();
  210.     }
  211.  
  212.     /*
  213.       generate invoive no
  214.      */
  215.  
  216.     public function generateInvoiceNo() {
  217.         $query = $this->db->query("SELECT * FROM invoice ORDER BY id DESC LIMIT 1");
  218.         $result = $query->result();
  219.         if ($result == null) {
  220.             $no = sprintf('%06d', intval(1));
  221.         } else {
  222.             foreach ($result as $value) {
  223.                 $no = sprintf('%06d', intval($value->id) + 1);
  224.             }
  225.         }
  226.         return "INV-" . $no;
  227.     }
  228.  
  229.     /*  
  230.       generate payment reference no
  231.      */
  232.  
  233.     public function generateReferenceNo() {
  234.         $query = $this->db->query("SELECT * FROM payment ORDER BY id DESC LIMIT 1");
  235.         $result = $query->result();
  236.         return $result;
  237.     }
  238.  
  239.     /*
  240.       return last purchase id
  241.      */
  242.  
  243.     public function createReferenceNo() {
  244.         $query = $this->db->query("SELECT * FROM sales ORDER BY sales_id DESC LIMIT 1");
  245.         $result = $query->result();
  246.         return $result;
  247.     }
  248.  
  249.     /*
  250.       return sales record
  251.      */
  252.  
  253.     public function getRecord($id) {
  254.         $sql = "select * from sales where sales_id = ?";
  255.         if ($query = $this->db->query($sql, array($id))) {
  256.             return $query->result();
  257.         } else {
  258.             return FALSE;
  259.         }
  260.     }
  261.  
  262.     /*
  263.       add new sales record in database
  264.      */
  265.  
  266.     public function addModel($data, $invoice) {
  267.         if ($this->db->insert('sales', $data)) {
  268.             $insert_id = $this->db->insert_id();
  269.             $invoice['sales_id'] = $insert_id;
  270.             $this->db->insert('invoice', $invoice);
  271.             return $insert_id;
  272.         } else {
  273.             echo $this->db->last_query();
  274.             print_r($this->db->error());
  275.             return FALSE;
  276.         }
  277.     }
  278.  
  279.     public function getTerminSalesId($id) {
  280.         $sql = "select * from sales_termin where sales_id = ?";
  281.         return $this->db->query($sql, array($id))->result();
  282.     }
  283.  
  284.     public function addTerminModel($data) {
  285.         if ($this->db->insert('sales_termin', $data)) {
  286.             $insert_id = $this->db->insert_id();
  287.             return $insert_id;
  288.         } else {
  289.             return FALSE;
  290.         }
  291.         echo $this->db->last_query();
  292.     }
  293.  
  294.     public function deleteTerminModel($id) {
  295.         $sql = "delete from sales_termin where sales_id = ?";
  296.         if ($this->db->query($sql, array($id))) {
  297.             return TRUE;
  298.         } else {
  299.             return FALSE;
  300.         }
  301.     }
  302.  
  303.     public function getCurrencyId($id) {
  304.         $sql = "select * from currency where kurs_id = ?";
  305.         return $this->db->query($sql, array($id))->result();
  306.     }
  307.  
  308.     /*
  309.       return discount detail use drop down when discount change
  310.      */
  311.  
  312.     public function getDiscountAjax($id) {
  313.         $sql = "select * from discount where discount_id = ?";
  314.         return $this->db->query($sql, array($id))->result();
  315.     }
  316.  
  317.     /*
  318.       check product available in sales or not
  319.      */
  320.  
  321.     public function checkProductInSales($sales_id, $product_id) {
  322.         $sql = "select * from sales_items where sales_id = ? AND product_id = ?";
  323.         if ($quantity = $this->db->query($sql, array($sales_id, $product_id))->num_rows() > 0) {
  324.  
  325.             $sql = "select * from sales_items where sales_id = ? AND product_id = ?";
  326.             $quantity = $this->db->query($sql, array($sales_id, $product_id));
  327.             return $quantity->row()->quantity;
  328.         } else {
  329.             return false;
  330.         }
  331.     }
  332.  
  333.     /*
  334.       update quantity in product table
  335.      */
  336.  
  337.     public function updateQuantity($sales_id, $product_id, $warehouse_id, $quantity, $old_quantity, $data) {
  338.         $where = "sales_id = $sales_id AND product_id = $product_id";
  339.         $this->db->where($where);
  340.         $this->db->update('sales_items', $data);
  341.  
  342.         $sql = "select * from warehouses_products where warehouse_id = ? AND product_id = ?";
  343.         $warehouse_quantity = $this->db->query($sql, array($warehouse_id, $product_id))->row()->quantity;
  344.  
  345.         $wquantity = $warehouse_quantity - $quantity + $old_quantity;
  346.         $sql = "update warehouses_products set quantity = ? where warehouse_id = ? AND product_id = ?";
  347.         $this->db->query($sql, array($wquantity, $warehouse_id, $product_id));
  348.  
  349.  
  350.         $sql = "select * from products where product_id = ?";
  351.         $product_quantity = $this->db->query($sql, array($product_id))->row()->quantity;
  352.  
  353.         $pquantity = $product_quantity - $quantity + $old_quantity;
  354.         $sql = "update products set quantity = ? where product_id = ?";
  355.         $this->db->query($sql, array($pquantity, $product_id));
  356.     }
  357.  
  358.     /*
  359.       check product available in warehouse or not
  360.      */
  361.  
  362.     public function checkProductInWarehouse($product_id, $quantity, $warehouse_id) {
  363.         $sql = "select * from warehouses_products where product_id = ? AND warehouse_id = ?";
  364.         $query = $this->db->query($sql, array($product_id, $warehouse_id));
  365.  
  366.         if ($query->num_rows() > 0) {
  367.             $warehouse_quantity = $query->row()->quantity;
  368.             if ($warehouse_quantity >= $quantity) {
  369.                 $wquantity = $warehouse_quantity - $quantity;
  370.                 $sql = "update warehouses_products set quantity = ? where product_id = ? AND warehouse_id = ?";
  371.                 $this->db->query($sql, array($wquantity, $product_id, $warehouse_id));
  372.  
  373.                 $sql = "select * from products where product_id = ?";
  374.                 $product_quantity = $this->db->query($sql, array($product_id))->row()->quantity;
  375.  
  376.                 $pquantity = $product_quantity - $quantity;
  377.                 $sql = "update products set quantity = ? where product_id = ?";
  378.                 $this->db->query($sql, array($pquantity, $product_id));
  379.             }
  380.         }
  381.     }
  382.  
  383.     /*
  384.       add newly sales items record in database
  385.      */
  386.  
  387.     public function addSalesItem($data) {
  388.        
  389.         $sql = "insert into sales_items (product_id,quantity,price,gross_total,discount_id,discount_value,discount,tax_id,tax_value,tax,sales_id) values (?,?,?,?,?,?,?,?,?,?,?)";
  390.         if ($this->db->query($sql, $data)) {
  391.             return true;
  392.         } else {
  393.           echo $this->db->last_query();
  394.       print_r($this->db->error());
  395.           return false;
  396.         }
  397.        
  398.     }
  399.  
  400.     public function addSalesServiceItem($data) {
  401.         $sql = "insert into salesservice_items (service_id,quantity,price,gross_total,discount_id,discount_value,discount,sales_id) values (?,?,?,?,?,?,?,?)";
  402.         if ($this->db->query($sql, $data)) {
  403.             return true;
  404.         } else {
  405.             return false;
  406.         }
  407.     }
  408.  
  409.     /*
  410.       return sales item data when edited
  411.      */
  412.  
  413.    
  414.     public function getSalesItems($sales_id) {
  415.         $this->db->select('si.*,sum(si.quantity) as quantity, wp.quantity as warehouses_quantity,p.product_id,p.code,p.name,si.price as qprice,p.unit,p.price,p.cost,p.item_namebyp')
  416.                 ->from('sales_items si')
  417.                 ->join('products p', 'si.product_id = p.product_id', 'left')
  418.                 ->join('warehouses_products wp', 'wp.product_id = p.product_id', 'left')
  419.                 ->where('si.sales_id', $sales_id)
  420.                 ->group_by('si.product_id');
  421.         if ($query = $this->db->get()) {
  422.             return $query->result();
  423.  
  424.         } else {
  425.             return FALSE;
  426.         }
  427.     }
  428.  
  429.     public function getServiceItems($sales_id) {
  430.         $this->db->select('si.*,p.service_id,p.code,p.service_title,p.service_spec,p.price as pprice,p.cost')
  431.                 ->from('salesservice_items si')
  432.                 ->join('service p', 'si.service_id = p.service_id', 'left')
  433.                 ->where('si.sales_id', $sales_id);
  434.         if ($query = $this->db->get()) {
  435.             return $query->result();
  436.         } else {
  437.             return FALSE;
  438.         }
  439.     }
  440.  
  441.     /*
  442.       return  single product to add dynamic table
  443.      */
  444.  
  445.     public function getProduct($product_id, $warehouse_id) {
  446.         return $this->db->select('p.product_id,p.code,p.hsn_sac_code,p.unit,p.name,p.size,p.cost,p.price,p.alert_quantity,p.image,p.category_id,p.subcategory_id,p.tax_id,wp.quantity,wp.warehouse_id,t.tax_value')
  447.                         ->from('products p')
  448.                         ->join('warehouses_products wp', 'p.product_id = wp.product_id', 'left')
  449.                         ->join('tax t', 'p.tax_id = t.tax_id', 'left')
  450.                         ->where('wp.warehouse_id', $warehouse_id)
  451.                         ->where('wp.product_id', $product_id)
  452.                         ->get()
  453.                         ->result();
  454.     }
  455.  
  456.     public function getServicetable($service_id) {
  457.         return $this->db->select('p.code,p.service_id,p.service_title,p.cost,p.price,p.category_id,p.subcategory_id,p.tax_id,t.tax_value')
  458.                         ->from('service p')
  459.                         ->join('tax t', 'p.tax_id = t.tax_id', 'left')
  460.                         ->where('p.service_id', $service_id)
  461.                         ->get()
  462.                         ->result();
  463.     }
  464.  
  465.     public function getProducts212($product_id) {
  466.         return $this->db->select('p.product_id,p.code,p.item_namebyp,p.unit,p.name,p.size,p.cost,p.price,p.alert_quantity,p.quantity qty, p.quantity quantity, p.image,p.category_id,p.subcategory_id,p.tax_id,wp.quantity wquantity,wp.warehouse_id,t.tax_value')
  467.                         ->from('products p')
  468.                         ->join('warehouses_products wp', 'p.product_id = wp.product_id', 'left')
  469.                         ->join('tax t', 'p.tax_id = t.tax_id', 'left')
  470.                         ->where('wp.product_id', $product_id)
  471.                         ->get()
  472.                         ->result();
  473.     }
  474.     public function getProducts21($product_id) {
  475.         return $this->db->select('p.*, d.id as product_grouping, d.list')
  476.                         ->from('products p')
  477.                         ->join('product_grouping d', 'd.product_id = p.product_id', 'left')
  478.                         ->join('bonus_items bi', 'bi.product = p.product_id','left')
  479.                         ->where('p.product_id',$product_id)
  480.                         ->get()
  481.                         ->result();
  482.     }
  483.  
  484.     /*
  485.       return  product list to add product
  486.      */
  487.  
  488.     public function getProducts() {
  489.         return $this->db->select('p.*')
  490.                         ->from('products p')
  491.                         ->join('warehouses_products wp', 'p.product_id = wp.product_id', 'left')
  492. //                   ->where('wp.warehouse_id',$warehouse_id)
  493. //                   ->where('wp.quantity > 0')
  494.                         ->get()
  495.                         ->result();
  496.     }
  497.  
  498.     public function getProducts222($warehouse_id) {
  499.         return $this->db->select('p.*')
  500.                         ->from('products p')
  501.                         ->join('warehouses_products wp', 'p.product_id = wp.product_id', 'left')
  502.                         ->where('wp.warehouse_id', $warehouse_id)
  503.                         ->where('wp.quantity > 0')
  504.                         ->get()
  505.                         ->result();
  506.     }
  507.  
  508.     /*
  509.       return  product list category to add product
  510.       public function getProductsCategory($warehouse_id,$category_id){
  511.       $category_id = 1;
  512.       return $this->db->select('p.*')
  513.       ->from('products p')
  514.       ->join('warehouses_products wp', 'p.product_id = wp.product_id', 'left')
  515.       ->join('category_product c', 'c.product_id = p.product_id', 'left')
  516.       ->where('wp.warehouse_id', $warehouse_id)
  517.       ->where('wp.quantity > 0')
  518.       ->where('c.category_id', $category_id)
  519.       ->get()
  520.       ->result();
  521.       }
  522.       /*
  523.       save edited record in database
  524.      */
  525.  
  526.     public function getPartNumber($brand_id) {
  527.         return $this->db->select('code, name')
  528.                         ->from('products')
  529.                         ->where('brand_id', $brand_id)
  530.                         ->group_by('code')
  531.                         ->get()
  532.                         ->result();
  533.     }
  534.  
  535.     public function getCodeProduct($code) {
  536.         return $this->db->select('product_id, name')
  537.                         ->from('products')
  538.                         ->where('code', $code)
  539.                         ->get()
  540.                         ->result();
  541.     }
  542.  
  543.     public function editModel($id, $data) {
  544.         /* $data['sales_id'] = $id;
  545.           $sql = "update sales set date = ?,reference_no = ?,warehouse_id = ?,customer_id = ?,biller_id = ?,total = ?,discount_value=?,tax_value=?,note = ?,shipping_city_id = ?,shipping_state_id= ?,shipping_country_id =?,shipping_address =?,shipping_charge =?,internal_note = ?,mode_of_transport=?,transporter_name=?,transporter_code=?,vehicle_regn_no=?,user = ? where sales_id = ?";
  546.           if($this->db->query($sql,$data)){ */
  547.         $this->db->where('sales_id', $id);
  548.         if ($this->db->update('sales', $data)) {
  549.             return true;
  550.         } else {
  551.             return false;
  552.         }
  553.     }
  554.  
  555.     /*
  556.       delete old purchase item when edit purchse
  557.      */
  558.  
  559.     public function deleteSalesItems($sales_id, $product_id, $warehouse_id, $old_warehouse_id) {
  560.  
  561.         $sql = "select * from sales_items where sales_id = ? AND product_id = ?";
  562.         $delete_quantity = $this->db->query($sql, array($sales_id, $product_id))->row()->quantity;
  563.  
  564.         $sql = "select * from warehouses_products where warehouse_id = ? AND product_id = ?";
  565.         $warehouse_quantity = $this->db->query($sql, array($warehouse_id, $product_id))->row()->quantity;
  566.  
  567.         $wquantity = $warehouse_quantity + $delete_quantity;
  568.         $sql = "update warehouses_products set quantity = ? where warehouse_id = ? AND product_id = ?";
  569.         $this->db->query($sql, array($wquantity, $warehouse_id, $product_id));
  570.  
  571.  
  572.         $sql = "select * from products where product_id = ?";
  573.         $product_quantity = $this->db->query($sql, array($product_id))->row()->quantity;
  574.  
  575.         $pquantity = $product_quantity + $delete_quantity;
  576.         $sql = "update products set quantity = ? where product_id = ?";
  577.         $this->db->query($sql, array($pquantity, $product_id));
  578.  
  579.         $sql = "delete from sales_items where sales_id = ? AND product_id = ?";
  580.         if ($this->db->query($sql, array($sales_id, $product_id))) {
  581.             return true;
  582.         } else {
  583.             return false;
  584.         }
  585.     }
  586.  
  587.     public function deleteSalesServiceItems($sales_id, $service_id) {
  588.         $sql = "select * from service_items where sales_id = ? AND service_id = ?";
  589.         $delete_quantity = $this->db->query($sql, array($sales_id, $service_id))->row()->quantity;
  590.  
  591.         $sql = "delete from service_items where sales_id = ? AND service_id = ?";
  592.         if ($this->db->query($sql, array($sales_id, $service_id))) {
  593.             return true;
  594.         } else {
  595.             return false;
  596.         }
  597.     }
  598.    
  599.     public function deleteSalesItemsAll($sales_id) {
  600.     $sql = "delete from sales_items where sales_id = ?";
  601.     if ($this->db->query($sql, array($sales_id))) {
  602.       return true;
  603.     } else {
  604.       return false;
  605.     }
  606.   }
  607.  
  608.   public function deleteSalesAdditionalAll($sales_id) {
  609.     $sql = "delete from sales_additional_items where sales_id = ?";
  610.     if ($this->db->query($sql, array($sales_id))) {
  611.       return true;
  612.     } else {
  613.       return false;
  614.     }
  615.   }
  616.  
  617.   public function deleteSalesServiceItemsAll($sales_id) {
  618.     $sql = "delete from salesservice_items where sales_id = ?";
  619.     if ($this->db->query($sql, array($sales_id))) {
  620.       return true;
  621.     } else {
  622.       return false;
  623.     }
  624.   }
  625.  
  626.   /*
  627.       when warehouse change selected items is delete this function
  628.      */
  629.  
  630.     public function changeWarehouseDeleteSalesItems($sales_id, $product_id, $warehouse_id, $old_warehouse_id) {
  631.  
  632.         $sql = "select * from sales_items where sales_id = ? AND product_id = ?";
  633.         $delete_quantity = $this->db->query($sql, array($sales_id, $product_id))->row()->quantity;
  634.  
  635.         $sql = "select * from warehouses_products where warehouse_id = ? AND product_id = ?";
  636.         $warehouse_quantity = $this->db->query($sql, array($old_warehouse_id, $product_id))->row()->quantity;
  637.  
  638.         $wquantity = $warehouse_quantity + $delete_quantity;
  639.         $sql = "update warehouses_products set quantity = ? where warehouse_id = ? AND product_id = ?";
  640.         $this->db->query($sql, array($wquantity, $old_warehouse_id, $product_id));
  641.  
  642.         $sql = "select * from products where product_id = ?";
  643.         $product_quantity = $this->db->query($sql, array($product_id))->row()->quantity;
  644.  
  645.         $pquantity = $product_quantity + $delete_quantity;
  646.         $sql = "update products set quantity = ? where product_id = ?";
  647.         $this->db->query($sql, array($pquantity, $product_id));
  648.  
  649.         $sql = "delete from sales_items where sales_id = ? AND product_id = ?";
  650.         if ($this->db->query($sql, array($sales_id, $product_id))) {
  651.             return true;
  652.         } else {
  653.             return false;
  654.         }
  655.     }
  656.  
  657.     /*
  658.       delete sales record in database
  659.      */
  660.  
  661.     public function deleteModel($id) {
  662.         $sql = "delete from sales where sales_id = ?";
  663.         if ($this->db->query($sql, array($id))) {
  664.             $sql = "delete from sales_items where sales_id = ?";
  665.             if ($this->db->query($sql, array($id))) {
  666.                 return TRUE;
  667.             }
  668.         } else {
  669.             return FALSE;
  670.         }
  671.     }
  672.  
  673.     /*
  674.       return all details of sales
  675.      */
  676.  
  677.     public function getSalesData() {
  678.         return $this->db->get('sales')->result();
  679.     }
  680.  
  681.     /*
  682.       return all details of purchase
  683.      */
  684.  
  685.     public function getPurchaseData() {
  686.         return $this->db->get('purchases')->result();
  687.     }
  688.  
  689.     /*
  690.       return sales data for calendar
  691.      */
  692.  
  693.     public function getCalendarData() {
  694.         return $this->db->get('sales')->result();
  695.     }
  696.  
  697.     /*
  698.       return sales details
  699.      */
  700.  
  701.     public function getDetails($id) {
  702.  
  703.         $this->db->select('s.*,
  704.                                   i.invoice_no,
  705.                                   i.invoice_date,
  706.                                   i.paid_amount,
  707.                                   c.customer_name,
  708.                                   c.address as customer_address,
  709.                                   c.mobile as customer_mobile,
  710.                                   c.email as customer_email,
  711.                                   c.company_name as customer_company,
  712.                                   c.postal_code as customer_postal_code,
  713.                                   c.gstid as customer_gstid,
  714.                                   c.state_id as customer_state_id,
  715.                                   c.tan_no as tan_no,
  716.                                   c.cst_reg_no as cst_reg_no,
  717.                                   c.excise_reg_no as excise_reg_no,
  718.                                   c.lbt_reg_no as lbt_reg_no,
  719.                                   c.servicetax_reg_no as servicetax_reg_no,
  720.                                   ct.name as customer_city,
  721.                                   c.country_id as customer_country,
  722.                                   b.biller_name,
  723.                                   b.address as biller_address,
  724.                                   cb.name as biller_city,
  725.                                   co.name as biller_country,
  726.                                   b.mobile as biller_mobile,
  727.                                   b.email as biller_email,
  728.                                   b.company_name as biller_company,
  729.                                   b.fax as biller_fax,
  730.                                   b.telephone as biller_telephone,
  731.                                   b.gstid as biller_gstid,
  732.                                   b.state_id as biller_state_id,
  733.                                   w.warehouse_name,
  734.                                   br.address as branch_address,
  735.                                   br.city as branch_city,
  736.                                   u.first_name,
  737.                                   k.kurs,
  738.                                   k.nominal,
  739.                                   k.symbol,
  740.                                   u.last_name')
  741.                 ->from('sales s')
  742.                 ->join('kurs k', 'k.kurs_id = s.kurs_id', 'left')
  743.                 ->join('invoice i', 'i.sales_id = s.sales_id', 'left')
  744.                 ->join('customer c', 's.customer_id = c.customer_id', 'left')
  745.                 ->join('cities ct', 'c.city_id = ct.id', 'left')
  746.                 ->join('states cs', 'c.state_id = cs.id', 'left')
  747.                 ->join('biller b', 's.biller_id = b.biller_id', 'left')
  748.                 ->join('cities cb', 'b.city_id = cb.id', 'left')
  749.                 ->join('states bs', 'b.state_id = bs.id', 'left')
  750.                 ->join('countries co', 'b.country_id = co.id', 'left')
  751.                 ->join('warehouse w', 's.warehouse_id = w.warehouse_id', 'left')
  752.                 ->join('branch br', 'w.branch_id = br.branch_id', 'left')
  753.                 ->join('users u', 's.user = u.id', 'left')
  754.                 ->where('s.sales_id', $id);
  755.  
  756.  
  757.         if ($query = $this->db->get()) {
  758.             return $query->result();
  759.         } else {
  760.             return FALSE;
  761.         }
  762.     }
  763.  
  764.  
  765.     public function checkInvoice($id) {
  766.         return $this->db->select('s.sales_id')
  767.                         ->from('sales s')
  768.                         ->join('invoice i', 'i.sales_id = s.sales_id', 'left')
  769.                         ->where('s.sales_id', $id)
  770.                         ->get()
  771.                         ->row();
  772.     }
  773.     /*
  774.       return details for payment
  775.      */
  776.  
  777.     public function getDetailsPayment($id) {
  778.         return $this->db->select('s.*,
  779.                                   c.customer_name,
  780.                                   c.address as customer_address,
  781.                                   c.mobile as customer_mobile,
  782.                                   c.email as customer_email,
  783.                                   c.gstid as customer_gstid,
  784.                                   ct.name as customer_city,
  785.                                   cco.name as customer_country,
  786.                                   b.biller_name,
  787.                                   b.address as biller_address,
  788.                                   cb.name as biller_city,
  789.                                   co.name as biller_country,
  790.                                   b.mobile as biller_mobile,
  791.                                   b.email as biller_email,
  792.                                   b.gstid as biller_gstid,
  793.                                   w.warehouse_name,
  794.                                   br.address as branch_address,
  795.                                   br.city as branch_city,
  796.                                   u.first_name,
  797.                                   u.last_name')
  798.                         ->from('sales s')
  799.                         ->join('customer c', 's.customer_id = c.customer_id', 'left')
  800.                         ->join('cities ct', 'c.city_id = ct.id', 'left')
  801.                         ->join('countries cco', 'c.country_id = cco.id', 'left')
  802.                         ->join('biller b', 's.biller_id = b.biller_id', 'left')
  803.                         ->join('cities cb', 'b.city_id = cb.id', 'left')
  804.                         ->join('countries co', 'b.country_id = co.id', 'left')
  805.                         ->join('warehouse w', 's.warehouse_id = w.warehouse_id', 'left')
  806.                         ->join('branch br', 'w.branch_id = br.branch_id', 'left')
  807.                         ->join('users u', 's.user = u.id', 'left')
  808.                         ->where('s.sales_id', $id)
  809.                         ->get()
  810.                         ->result();
  811.     }
  812.  
  813.     /*
  814.       return sales item details
  815.      */
  816.     public function getItems($id) {
  817.         return $this->db->select('si.*, si.quantity as quantity_item, pr.quantity as quantity_product, pr.name, pr.code,pr.unit,pr.unit,pr.item_namebyp, pr.quantity as pquantity, pr.p_detail as detail, pr.image as image, d.id as product_grouping, d.list')
  818.                     ->from('sales_items si')
  819.                     ->join('sales s', 'si.sales_id = s.sales_id', 'left')
  820.                     ->join('products pr', 'si.product_id = pr.product_id', 'left')
  821.                     ->join('product_grouping d', 'd.product_id = pr.product_id', 'left')
  822.                     ->where('si.sales_id', $id)
  823.                     ->order_by('si.id asc')
  824.                     ->get()
  825.                     ->result();
  826.     }
  827.  
  828.     public function getAdditional($id) {
  829.         return $this->db->select('si.*, si.price as qprice,si.quantity as quantity_item, pr.quantity as quantity_product, pr.name,pr.code,pr.unit,pr.unit,pr.item_namebyp, pr.quantity as pquantity, pr.p_detail as detail, pr.image as image, d.id as product_grouping, d.list')
  830.                     ->from('sales_additional_items si')
  831.                     ->join('sales s', 'si.sales_id = s.sales_id', 'left')
  832.                     ->join('products pr', 'si.product_id = pr.product_id', 'left')
  833.                     ->join('product_grouping d', 'd.product_id = pr.product_id', 'left')
  834.                     ->where('si.sales_id', $id)
  835.                     ->order_by('si.id asc')
  836.                     ->get()
  837.                     ->result();
  838.     }
  839.  
  840.     /*
  841.       return supplier details
  842.      */
  843.  
  844.     public function getCustomerEmail($id) {
  845.  
  846.         return $this->db->select('*')
  847.                         ->from('sales s')
  848.                         ->join('customer c', 'c.customer_id = s.customer_id', 'left')
  849.                         ->where('s.sales_id', $id)
  850.                         ->get()
  851.                         ->result();
  852.     }
  853.  
  854.     /*
  855.       add payment details
  856.      */
  857.  
  858.     public function addPayment($data) {
  859.  
  860.         $sql = "INSERT INTO payment (sales_id,date,reference_no,amount,paying_by,bank_name,cheque_no,description) VALUES (?,?,?,?,?,?,?,?)";
  861.         if ($this->db->query($sql, $data)) {
  862.             /* if($this->db->insert('payment',$data)){ */
  863.  
  864.             $this->db->where('sales_id', $data['sales_id']);
  865.             $this->db->update('invoice', array("paid_amount" => $data['amount']));
  866.             return true;
  867.         } else {
  868.             return false;
  869.         }
  870.     }
  871.  
  872.     /*
  873.  
  874.      */
  875.  
  876.     public function invoice() {
  877.         return $this->db->select('*')
  878.                         ->from('invoice i')
  879.                         ->join('sales s', 's.sales_id = i.sales_id', 'left')
  880.                         ->get()
  881.                         ->result();
  882.     }
  883.  
  884.     /*
  885.       return SMTP server Data
  886.      */
  887.  
  888.     public function getSmtpSetup() {
  889.         return $this->db->get('email_setup')->row();
  890.     }
  891.  
  892.     /*
  893.       return customer data for shipping address
  894.      */
  895.  
  896.     public function getCustomerData($id) {
  897.         $this->db->where('customer_id', $id);
  898.         return $this->db->get_where('customer')->row();
  899.     }
  900.  
  901.     /*
  902.       return country
  903.      */
  904.  
  905.     public function getCountry() {
  906.         return $this->db->get('countries')->result();
  907.     }
  908.  
  909.     /*
  910.       return state
  911.      */
  912.  
  913.     public function getState($id) {
  914.         return $this->db->select('s.*')
  915.                         ->from('states s')
  916.                         ->join('countries c', 'c.id = s.country_id', 'left')
  917.                         ->where('s.country_id', $id)
  918.                         ->get()
  919.                         ->result();
  920.     }
  921.  
  922.     /*
  923.       return city
  924.      */
  925.  
  926.     public function getCity($id) {
  927.         return $this->db->select('c.*')
  928.                         ->from('cities c')
  929.                         ->join('states s', 's.id = c.state_id', 'left')
  930.                         ->where('c.state_id', $id)
  931.                         ->get()
  932.                         ->result();
  933.     }
  934.  
  935.     // choerul
  936.     // Generate Autonumber Revision
  937.     public function generateAutoNumberRevision($id) {
  938.         $sql = "SELECT
  939.                    MAX(po_new) AS po_new
  940.                FROM
  941.                    sales
  942.                WHERE
  943.                    po = (
  944.                            SELECT po FROM sales
  945.                            WHERE sales_id = " . $id . ")";
  946.  
  947.         $query = $this->db->query($sql);
  948.  
  949.         $counter = "-R1";
  950.  
  951.         if ($query->num_rows() > 0 && $query->row()->po_new != "") {
  952.             $new_trans_number = $query->row()->po_new;
  953.             ++$new_trans_number;
  954.         } else {
  955.             $sql_reference = "SELECT po FROM sales WHERE sales_id = " . $id;
  956.             $query_reference = $this->db->query($sql_reference);
  957.             $transaction_number = $query_reference->row()->po;
  958.             $new_trans_number = $transaction_number . $counter;
  959.         }
  960.  
  961.         return $new_trans_number;
  962.     }
  963.  
  964.     public function getSalesByProduct(){
  965.         return $this->db->select('s.*,si.*,si.quantity as siqty, si.price as siprc, p.*')
  966.                         ->from('sales s')
  967.                         ->join('sales_items si', 'si.sales_id = s.sales_id', 'left')
  968.                         ->join('products p', 'p.product_id = si.product_id', 'left')
  969.                         ->group_by('si.product_id')
  970.                         ->get()
  971.                         ->result();
  972.     }
  973.  
  974. }
  975.  
  976. ?>
RAW Paste Data
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!