221 lines
8.1 KiB
PHP
221 lines
8.1 KiB
PHP
<?php
|
|
class MStocks extends CI_Model
|
|
{
|
|
public function __construct()
|
|
{
|
|
parent::__construct();
|
|
}
|
|
public function isDeleteable($item_id)
|
|
{
|
|
$this->db->where("items_id", $item_id);
|
|
$t = $this->db->get("tbl_salesdetails")->row();
|
|
if ($t) return false;
|
|
$this->db->where("items_id", $item_id);
|
|
$t = $this->db->get("tbl_purchasedetails")->row();
|
|
if ($t) return false;
|
|
return true;
|
|
}
|
|
public function deleteOpeningStock($item_id)
|
|
{
|
|
$this->db->where("items_id", $item_id);
|
|
$this->db->delete("tbl_stocks");
|
|
}
|
|
public function updateOpeningStock($item_id, $qty, $rate)
|
|
{
|
|
$this->db->where("items_id", $item_id);
|
|
$this->db->delete("tbl_stocks");
|
|
$this->db->insert("tbl_stocks", [
|
|
'stocklocations_id' => 1,
|
|
'ref_id' => 0,
|
|
"title" => "Opening Stock",
|
|
"items_id" => $item_id,
|
|
"qty" => $qty,
|
|
'price' => $rate,
|
|
'status' => 1,
|
|
'created_on' => date("y-m-d H:i:s")
|
|
]);
|
|
}
|
|
public function addOpeningStock($item_id, $qty, $rate)
|
|
{
|
|
$this->db->insert("tbl_stocks", [
|
|
'stocklocations_id' => 1,
|
|
'ref_id' => 0,
|
|
"title" => "Opening Stock",
|
|
"items_id" => $item_id,
|
|
"qty" => $qty,
|
|
'price' => $rate,
|
|
'status' => 1,
|
|
'created_on' => date("y-m-d H:i:s")
|
|
]);
|
|
}
|
|
public function getSellableItems()
|
|
{
|
|
$subquery = "(SELECT SUM(qty) FROM tbl_stocks WHERE item_id = tbl_items.item_id) AS total_qty";
|
|
$this->db->select("tbl_items.*, $subquery", false);
|
|
$this->db->having('total_qty >', 0);
|
|
return $this->db->get("tbl_items")->result();
|
|
}
|
|
|
|
public function getItemSalesStock($item_id)
|
|
{
|
|
|
|
$this->db->where("status", 1);
|
|
$this->db->where("items_id", $item_id);
|
|
// $this->db->where("title='Opening Stock'");
|
|
$Items = $this->db->get("tbl_salesdetails")->result();
|
|
$qty = 0;
|
|
$rate = 0;
|
|
$nos = 0;
|
|
foreach ($Items as $Item) {
|
|
$qty += $Item->qty;
|
|
$nos++;
|
|
$rate += $Item->rate;
|
|
}
|
|
if($nos!=0) $rate = $rate / $nos;
|
|
|
|
$SalesStock = new stdClass;
|
|
$SalesStock->qty = $qty;
|
|
$SalesStock->rate = $rate;
|
|
$SalesStock->amount = $qty * $rate;
|
|
return $SalesStock;
|
|
}
|
|
public function getItemPurchaseStock($item_id)
|
|
{
|
|
|
|
$this->db->where("status", 1);
|
|
$this->db->where("items_id", $item_id);
|
|
// $this->db->where("title='Opening Stock'");
|
|
$Items = $this->db->get("tbl_purchasedetails")->result();
|
|
$qty = 0;
|
|
$rate = 0;
|
|
$nos = 0;
|
|
foreach ($Items as $Item) {
|
|
$qty += $Item->qty;
|
|
$nos++;
|
|
$rate += $Item->rate;
|
|
}
|
|
if($nos!=0) $rate = $rate / $nos;
|
|
$PurchaseStock = new stdClass;
|
|
$PurchaseStock->qty = $qty;
|
|
$PurchaseStock->rate = $rate;
|
|
$PurchaseStock->amount = $qty * $rate;
|
|
return $PurchaseStock;
|
|
}
|
|
|
|
public function getOpeningStock($item_id)
|
|
{
|
|
$this->db->where("status", 1);
|
|
$this->db->where("items_id", $item_id);
|
|
$this->db->where("title='Opening Stock'");
|
|
$Items = $this->db->get("tbl_stocks")->result();
|
|
$qty = 0;
|
|
$rate = 0;
|
|
$nos = 0;
|
|
foreach ($Items as $Item) {
|
|
$qty += $Item->qty;
|
|
$nos++;
|
|
$rate += $Item->price;
|
|
}
|
|
if($nos!=0) $rate = $rate / $nos;
|
|
$OpeningStock = new stdClass;
|
|
$OpeningStock->qty = $qty;
|
|
$OpeningStock->rate = $rate;
|
|
$OpeningStock->amount = $qty * $rate;
|
|
return $OpeningStock;
|
|
}
|
|
public function getClosingStock($item_id)
|
|
{
|
|
$OpeningStock=$this->getOpeningStock($item_id);
|
|
$PurchaseStock=$this->getItemPurchaseStock($item_id);
|
|
$SalesStock=$this->getItemSalesStock($item_id);
|
|
$ClosingStock = new stdClass;
|
|
$ClosingStock->qty =$PurchaseStock->qty+$OpeningStock->qty-$SalesStock->qty;
|
|
$ClosingStock->rate = $PurchaseStock->rate+$OpeningStock->rate-$SalesStock->rate;
|
|
$ClosingStock->amount = $ClosingStock->qty * $ClosingStock->rate;
|
|
return $ClosingStock;
|
|
}
|
|
public function getStockItemSummary($item_id)
|
|
{
|
|
$this->db->where("status", 1);
|
|
$this->db->where("items_id", $item_id);
|
|
$Item = $this->db->get("tbl_stocks")->row();
|
|
$qty = 0;
|
|
$price = 0;
|
|
$Item->Item = $this->db->where("item_id", $item_id)->get("tbl_items")->row();
|
|
$Item->qty = $this->db->where("items_id", $item_id)->select("sum(qty) as qty")->get("tbl_stocks")->row()->qty;
|
|
$Item->amount = $this->db->where("items_id", $item_id)->select("sum(price * qty) as amount")->get("tbl_stocks")->row()->amount;
|
|
$Item->Unit = $this->db->where("unit_id", $Item->Item->units_id)->get("tbl_units")->row();
|
|
$Item->Opening = $this->getOpeningStock($item_id);
|
|
$Item->Purchase = $this->getItemPurchaseStock($item_id);
|
|
$Item->Sales = $this->getItemSalesStock($item_id);
|
|
$Item->Closing = $this->getClosingStock($item_id);
|
|
return $Item;
|
|
}
|
|
public function getStockSummary($stocklocation_id = 0)
|
|
{
|
|
$this->db->where("status", 1);
|
|
if ($stocklocation_id != 0) {
|
|
$this->db->where("stocklocations_id", $stocklocation_id);
|
|
}
|
|
$StockRecords = $this->db->select("distinct(items_id) as items_id")->get("tbl_stocks")->result();
|
|
foreach ($StockRecords as $StockRecord) {
|
|
$StockRecord->Summary = $this->getStockItemSummary($StockRecord->items_id);
|
|
// $StockRecord->StockLocation = $this->db->where("stocklocation_id", $StockRecord->stocklocations_id)->get("tbl_stocklocations")->row();
|
|
$StockRecord->Item = $this->db->where("item_id", $StockRecord->items_id)->get("tbl_items")->row();
|
|
}
|
|
return $StockRecords;
|
|
}
|
|
public function getStockRecords($stocklocation_id = 0)
|
|
{
|
|
$this->db->where("status", 1);
|
|
if ($stocklocation_id != 0) {
|
|
$this->db->where("stocklocations_id", $stocklocation_id);
|
|
}
|
|
$StockRecords = $this->db->get("tbl_stocks")->result();
|
|
foreach ($StockRecords as $StockRecord) {
|
|
$StockRecord->StockLocation = $this->db->where("stocklocation_id", $StockRecord->stocklocations_id)->get("tbl_stocklocations")->row();
|
|
$StockRecord->Item = $this->db->where("item_id", $StockRecord->items_id)->get("tbl_items")->row();
|
|
$StockRecord->Item->Unit = $this->db->where("unit_id", $StockRecord->Item->units_id)->get("tbl_units")->row();
|
|
}
|
|
return $StockRecords;
|
|
}
|
|
public function addPurchaseToStock($PurchaseDetails)
|
|
{
|
|
foreach ($PurchaseDetails as $PurchaseDetail) {
|
|
$TableData = array(
|
|
'stocklocations_id' => 1,
|
|
'ref_id' => $PurchaseDetail->purchases_id,
|
|
'title' => "Purchase",
|
|
'items_id' => $PurchaseDetail->items_id,
|
|
'qty' => $PurchaseDetail->qty,
|
|
'price' => $PurchaseDetail->rate,
|
|
'status' => 1,
|
|
);
|
|
$this->db->insert("tbl_stocks", $TableData);
|
|
}
|
|
}
|
|
public function addSalesToStock($SalesDetails)
|
|
{
|
|
foreach ($SalesDetails as $SalesDetail) {
|
|
$TableData = array(
|
|
'stocklocations_id' => 1,
|
|
'ref_id' => $SalesDetail->sales_id,
|
|
'title' => "Sales",
|
|
'items_id' => $SalesDetail->items_id,
|
|
'qty' => (-$SalesDetail->qty),
|
|
'price' => $SalesDetail->rate,
|
|
'status' => 1,
|
|
);
|
|
$this->db->insert("tbl_stocks", $TableData);
|
|
}
|
|
}
|
|
public function getUnitByItemId($id)
|
|
{
|
|
$this->db->where('status', 1);
|
|
$Item = $this->db->where("item_id", $id)->get("tbl_items")->row();
|
|
$Unit = $this->db->where("unit_id", $Item->units_id)->get("tbl_units")->row();
|
|
return $Unit;
|
|
|
|
}
|
|
}
|