Server : Apache/2.4.62 (Unix) OpenSSL/1.0.2k-fips System : Linux server1.richineiserver.com 3.10.0-1160.119.1.el7.x86_64 #1 SMP Tue Jun 4 14:43:51 UTC 2024 x86_64 User : fdshops ( 1003) PHP Version : 8.2.21 Disable Function : NONE Directory : /home/fdshops/public_html/app/Utils/ |
<?php
namespace App\Utils;
use App\Business;
use App\BusinessLocation;
use App\Discount;
use App\Media;
use App\Product;
use App\ProductRack;
use App\ProductVariation;
use App\PurchaseLine;
use App\TaxRate;
use App\Transaction;
use App\TransactionSellLine;
use App\TransactionSellLinesPurchaseLines;
use App\Unit;
use App\Variation;
use App\VariationGroupPrice;
use App\VariationLocationDetails;
use App\VariationTemplate;
use App\VariationValueTemplate;
use Illuminate\Support\Facades\DB;
class ProductUtil extends Util
{
/**
* Create single type product variation
*
* @param (int or object) $product
* @param $sku
* @param $purchase_price
* @param $dpp_inc_tax (default purchase pric including tax)
* @param $profit_percent
* @param $selling_price
* @param $combo_variations = []
* @return bool
*/
public function createSingleProductVariation($product, $sku, $purchase_price, $dpp_inc_tax, $profit_percent, $selling_price, $selling_price_inc_tax, $combo_variations = [])
{
if (! is_object($product)) {
$product = Product::find($product);
}
//create product variations
$product_variation_data = [
'name' => 'DUMMY',
'is_dummy' => 1,
];
$product_variation = $product->product_variations()->create($product_variation_data);
//create variations
$variation_data = [
'name' => 'DUMMY',
'product_id' => $product->id,
'sub_sku' => $sku,
'default_purchase_price' => $this->num_uf($purchase_price),
'dpp_inc_tax' => $this->num_uf($dpp_inc_tax),
'profit_percent' => $this->num_uf($profit_percent),
'default_sell_price' => $this->num_uf($selling_price),
'sell_price_inc_tax' => $this->num_uf($selling_price_inc_tax),
'combo_variations' => $combo_variations,
];
$variation = $product_variation->variations()->create($variation_data);
Media::uploadMedia($product->business_id, $variation, request(), 'variation_images');
return true;
}
/**
* Create variable type product variation
*
* @param (int or object) $product
* @param $input_variations
* @return bool
*/
public function createVariableProductVariations($product, $input_variations, $sku_type, $business_id = null, )
{
if (! is_object($product)) {
$product = Product::find($product);
}
//create product variations
foreach ($input_variations as $key => $value) {
$images = [];
$variation_template_name = ! empty($value['name']) ? $value['name'] : null;
$variation_template_id = ! empty($value['variation_template_id']) ? $value['variation_template_id'] : null;
if (empty($variation_template_id)) {
if ($variation_template_name != 'DUMMY') {
$variation_template = VariationTemplate::where('business_id', $business_id)
->whereRaw('LOWER(name)="'.strtolower($variation_template_name).'"')
->with(['values'])
->first();
if (empty($variation_template)) {
$variation_template = VariationTemplate::create([
'name' => $variation_template_name,
'business_id' => $business_id,
]);
}
$variation_template_id = $variation_template->id;
}
} else {
$variation_template = VariationTemplate::with(['values'])->find($value['variation_template_id']);
$variation_template_id = $variation_template->id;
$variation_template_name = $variation_template->name;
}
$product_variation_data = [
'name' => $variation_template_name,
'product_id' => $product->id,
'is_dummy' => 0,
'variation_template_id' => $variation_template_id,
];
$product_variation = ProductVariation::create($product_variation_data);
//create variations
if (! empty($value['variations'])) {
$variation_data = [];
$c = Variation::withTrashed()
->where('product_id', $product->id)
->count() + 1;
foreach ($value['variations'] as $k => $v) {
//skip hidden variations
if (isset($v['is_hidden']) && $v['is_hidden'] == 1) {
continue;
}
$sub_sku = empty($v['sub_sku']) ? $this->generateSubSku($product->sku, $c, $product->barcode_type, $v['value'], $sku_type) : $v['sub_sku'];
$variation_value_id = ! empty($v['variation_value_id']) ? $v['variation_value_id'] : null;
$variation_value_name = ! empty($v['value']) ? $v['value'] : null;
if (! empty($variation_value_id)) {
$variation_value = $variation_template->values->filter(function ($item) use ($variation_value_id) {
return $item->id == $variation_value_id;
})->first();
$variation_value_name = $variation_value->name;
} else {
if (! empty($variation_template)) {
$variation_value = VariationValueTemplate::where('variation_template_id', $variation_template->id)
->whereRaw('LOWER(name)="'.$variation_value_name.'"')
->first();
if (empty($variation_value)) {
$variation_value = VariationValueTemplate::create([
'name' => $variation_value_name,
'variation_template_id' => $variation_template->id,
]);
}
$variation_value_id = $variation_value->id;
$variation_value_name = $variation_value->name;
} else {
$variation_value_id = null;
$variation_value_name = $variation_value_name;
}
}
$variation_data[] = [
'name' => $variation_value_name,
'variation_value_id' => $variation_value_id,
'product_id' => $product->id,
'sub_sku' => $sub_sku,
'default_purchase_price' => $this->num_uf($v['default_purchase_price']),
'dpp_inc_tax' => $this->num_uf($v['dpp_inc_tax']),
'profit_percent' => $this->num_uf($v['profit_percent']),
'default_sell_price' => $this->num_uf($v['default_sell_price']),
'sell_price_inc_tax' => $this->num_uf($v['sell_price_inc_tax']),
];
$c++;
$images[] = 'variation_images_'.$key.'_'.$k;
}
$variations = $product_variation->variations()->createMany($variation_data);
$i = 0;
foreach ($variations as $variation) {
Media::uploadMedia($product->business_id, $variation, request(), $images[$i]);
$i++;
}
}
}
}
/**
* Update variable type product variation
*
* @param $product_id
* @param $input_variations_edit
* @return bool
*/
public function updateVariableProductVariations($product_id, $input_variations_edit, $sku_type)
{
$product = Product::find($product_id);
//Update product variations
$product_variation_ids = [];
$variations_ids = [];
foreach ($input_variations_edit as $key => $value) {
$product_variation_ids[] = $key;
$product_variation = ProductVariation::find($key);
$product_variation->name = $value['name'];
$product_variation->save();
//Update existing variations
if (! empty($value['variations_edit'])) {
foreach ($value['variations_edit'] as $k => $v) {
$data = [
'name' => $v['value'],
'default_purchase_price' => $this->num_uf($v['default_purchase_price']),
'dpp_inc_tax' => $this->num_uf($v['dpp_inc_tax']),
'profit_percent' => $this->num_uf($v['profit_percent']),
'default_sell_price' => $this->num_uf($v['default_sell_price']),
'sell_price_inc_tax' => $this->num_uf($v['sell_price_inc_tax']),
];
if (! empty($v['sub_sku'])) {
$data['sub_sku'] = $v['sub_sku'];
}
$variation = Variation::where('id', $k)
->where('product_variation_id', $key)
->first();
$variation->update($data);
Media::uploadMedia($product->business_id, $variation, request(), 'edit_variation_images_'.$key.'_'.$k);
$variations_ids[] = $k;
}
}
//Add new variations
if (! empty($value['variations'])) {
$variation_data = [];
$c = Variation::withTrashed()
->where('product_id', $product->id)
->count() + 1;
$media = [];
foreach ($value['variations'] as $k => $v) {
$sub_sku = empty($v['sub_sku']) ? $this->generateSubSku($product->sku, $c, $product->barcode_type, $v['value'] , $sku_type) : $v['sub_sku'];
$variation_value_name = ! empty($v['value']) ? $v['value'] : null;
$variation_value_id = null;
if (! empty($product_variation->variation_template_id)) {
$variation_value = VariationValueTemplate::where('variation_template_id', $product_variation->variation_template_id)
->whereRaw('LOWER(name)="'.$v['value'].'"')
->first();
if (empty($variation_value)) {
$variation_value = VariationValueTemplate::create([
'name' => $v['value'],
'variation_template_id' => $product_variation->variation_template_id,
]);
}
$variation_value_id = $variation_value->id;
}
$variation_data[] = [
'name' => $variation_value_name,
'variation_value_id' => $variation_value_id,
'product_id' => $product->id,
'sub_sku' => $sub_sku,
'default_purchase_price' => $this->num_uf($v['default_purchase_price']),
'dpp_inc_tax' => $this->num_uf($v['dpp_inc_tax']),
'profit_percent' => $this->num_uf($v['profit_percent']),
'default_sell_price' => $this->num_uf($v['default_sell_price']),
'sell_price_inc_tax' => $this->num_uf($v['sell_price_inc_tax']),
];
$c++;
$media[] = 'variation_images_'.$key.'_'.$k;
}
$new_variations = $product_variation->variations()->createMany($variation_data);
$i = 0;
foreach ($new_variations as $new_variation) {
$variations_ids[] = $new_variation->id;
Media::uploadMedia($product->business_id, $new_variation, request(), $media[$i]);
$i++;
}
}
}
//get removed variations
$removed_variations = Variation::whereNotIn('id', $variations_ids)
->where('product_id', $product->id)
->pluck('id');
foreach ($removed_variations as $removed_variation_id) {
//Check if purchase or sell exist for the deletable variation
$count_purchase = PurchaseLine::join(
'transactions as T',
'purchase_lines.transaction_id',
'=',
'T.id'
)
->where('T.type', 'purchase')
->where('T.status', 'received')
->where('T.business_id', $product->business_id)
->where('purchase_lines.product_id', $product->id)
->where('purchase_lines.variation_id', $removed_variation_id)
->count();
$count_sell = TransactionSellLine::join(
'transactions as T',
'transaction_sell_lines.transaction_id',
'=',
'T.id'
)
->where('T.type', 'sell')
->where('T.status', 'final')
->where('T.business_id', $product->business_id)
->where('transaction_sell_lines.product_id', $product->id)
->where('transaction_sell_lines.variation_id', $removed_variation_id)
->count();
$is_variation_delatable = $count_purchase > 0 || $count_sell > 0 ? false : true;
//if purchase sell dont exists delete the variation
if ($is_variation_delatable) {
Variation::where('id', $removed_variation_id)
->delete();
} else {
throw new \Exception(__('lang_v1.purchase_already_exist'));
}
}
ProductVariation::where('product_id', $product_id)
->whereNotIn('id', $product_variation_ids)
->delete();
}
/**
* Checks if products has manage stock enabled then Updates quantity for product and its
* variations
*
* @param $location_id
* @param $product_id
* @param $variation_id
* @param $new_quantity
* @param $old_quantity = 0
* @param $number_format = null
* @param $uf_data = true, if false it will accept numbers in database format
* @return bool
*/
public function updateProductQuantity($location_id, $product_id, $variation_id, $new_quantity, $old_quantity = 0, $number_format = null, $uf_data = true)
{
if ($uf_data) {
$qty_difference = $this->num_uf($new_quantity, $number_format) - $this->num_uf($old_quantity, $number_format);
} else {
$qty_difference = $new_quantity - $old_quantity;
}
$product = Product::find($product_id);
//Check if stock is enabled or not.
if ($product->enable_stock == 1 && $qty_difference != 0) {
$variation = Variation::where('id', $variation_id)
->where('product_id', $product_id)
->first();
//Add quantity in VariationLocationDetails
$variation_location_d = VariationLocationDetails::where('variation_id', $variation->id)
->where('product_id', $product_id)
->where('product_variation_id', $variation->product_variation_id)
->where('location_id', $location_id)
->first();
if (empty($variation_location_d)) {
$variation_location_d = new VariationLocationDetails();
$variation_location_d->variation_id = $variation->id;
$variation_location_d->product_id = $product_id;
$variation_location_d->location_id = $location_id;
$variation_location_d->product_variation_id = $variation->product_variation_id;
$variation_location_d->qty_available = 0;
}
$variation_location_d->qty_available += $qty_difference;
$variation_location_d->save();
}
return true;
}
/**
* Checks if products has manage stock enabled then Decrease quantity for product and its variations
*
* @param $product_id
* @param $variation_id
* @param $location_id
* @param $new_quantity
* @param $old_quantity = 0
* @return bool
*/
public function decreaseProductQuantity($product_id, $variation_id, $location_id, $new_quantity, $old_quantity = 0)
{
$qty_difference = $new_quantity - $old_quantity;
$product = Product::find($product_id);
//Check if stock is enabled or not.
if ($product->enable_stock == 1) {
//Decrement Quantity in variations location table
$details = VariationLocationDetails::where('variation_id', $variation_id)
->where('product_id', $product_id)
->where('location_id', $location_id)
->first();
//If location details not exists create new one
if (empty($details)) {
$variation = Variation::find($variation_id);
$details = VariationLocationDetails::create([
'product_id' => $product_id,
'location_id' => $location_id,
'variation_id' => $variation_id,
'product_variation_id' => $variation->product_variation_id,
'qty_available' => 0,
]);
}
$details->decrement('qty_available', $qty_difference);
}
return true;
}
/**
* Decrease the product quantity of combo sub-products
*
* @param $combo_details
* @param $location_id
* @return void
*/
public function decreaseProductQuantityCombo($combo_details, $location_id)
{
//product_id = child product id
//variation id is child product variation id
foreach ($combo_details as $details) {
$this->decreaseProductQuantity(
$details['product_id'],
$details['variation_id'],
$location_id,
$details['quantity']
);
}
}
/**
* Get all details for a product from its variation id
*
* @param int $variation_id
* @param int $business_id
* @param int $location_id
* @param bool $check_qty (If false qty_available is not checked)
* @return array
*/
public function getDetailsFromVariation($variation_id, $business_id, $location_id = null, $check_qty = true)
{
$variation = Variation::with('media')->findOrFail($variation_id);
$query = Variation::join('products AS p', 'variations.product_id', '=', 'p.id')
->join('product_variations AS pv', 'variations.product_variation_id', '=', 'pv.id')
->leftjoin('variation_location_details AS vld', 'variations.id', '=', 'vld.variation_id')
->leftjoin('units', 'p.unit_id', '=', 'units.id')
->leftjoin('units as u', 'p.secondary_unit_id', '=', 'u.id')
->leftjoin('brands', function ($join) {
$join->on('p.brand_id', '=', 'brands.id')
->whereNull('brands.deleted_at');
})
->where('p.business_id', $business_id)
->where('variations.id', $variation_id);
//Add condition for check of quantity. (if stock is not enabled or qty_available > 0)
if ($check_qty) {
$query->where(function ($query) {
$query->where('p.enable_stock', '!=', 1)
->orWhere('vld.qty_available', '>', 0);
});
}
if (! empty($location_id) && $check_qty) {
//Check for enable stock, if enabled check for location id.
$query->where(function ($query) use ($location_id) {
$query->where('p.enable_stock', '!=', 1)
->orWhere('vld.location_id', $location_id);
});
}
$product = $query->select(
DB::raw("IF(pv.is_dummy = 0, CONCAT(p.name,
' (', pv.name, ':',variations.name, ')'), p.name) AS product_name"),
'p.id as product_id',
'p.brand_id',
'p.category_id',
'p.tax as tax_id',
'p.enable_stock',
'p.enable_sr_no',
'p.type as product_type',
'p.name as product_actual_name',
'p.warranty_id',
'p.image as product_image',
'p.product_custom_field1',
'p.product_custom_field2',
'p.product_custom_field3',
'p.product_custom_field4',
'p.product_custom_field5',
'p.product_custom_field6',
'p.product_custom_field7',
'p.product_custom_field8',
'p.product_custom_field9',
'p.product_custom_field10',
'p.product_custom_field11',
'p.product_custom_field12',
'p.product_custom_field13',
'p.product_custom_field14',
'p.product_custom_field15',
'p.product_custom_field16',
'p.product_custom_field17',
'p.product_custom_field18',
'p.product_custom_field19',
'p.product_custom_field20',
'pv.name as product_variation_name',
'pv.is_dummy as is_dummy',
'variations.name as variation_name',
'variations.sub_sku',
'p.barcode_type',
'vld.qty_available',
'variations.default_sell_price',
'variations.sell_price_inc_tax',
'variations.id as variation_id',
'variations.combo_variations', //Used in combo products
'units.short_name as unit',
'units.id as unit_id',
'units.allow_decimal as unit_allow_decimal',
'u.short_name as second_unit',
'brands.name as brand',
DB::raw('(SELECT purchase_price_inc_tax FROM purchase_lines WHERE
variation_id=variations.id ORDER BY id DESC LIMIT 1) as last_purchased_price')
)
->firstOrFail();
$product->media = $variation->media;
if ($product->product_type == 'combo') {
if ($check_qty) {
$product->qty_available = $this->calculateComboQuantity($location_id, $product->combo_variations);
}
$product->combo_products = $this->calculateComboDetails($location_id, $product->combo_variations);
}
return $product;
}
/**
* Calculates the quantity of combo products based on
* the quantity of variation items used.
*
* @param int $location_id
* @param array $combo_variations
* @return int
*/
public function calculateComboQuantity($location_id, $combo_variations)
{
//get stock of the items and calcuate accordingly.
$combo_qty = 0;
foreach ($combo_variations as $key => $value) {
$variation = Variation::with(['product', 'variation_location_details' => function ($q) use ($location_id) {
$q->where('location_id', $location_id);
}])->findOrFail($value['variation_id']);
$product = $variation->product;
//Dont calculate stock if disabled
if ($product->enable_stock != 1) {
continue;
}
$vld = $variation->variation_location_details
->first();
$variation_qty = ! empty($vld) ? $vld->qty_available : 0;
$multiplier = $this->getMultiplierOf2Units($product->unit_id, $value['unit_id']);
if ($combo_qty == 0) {
$combo_qty = ($variation_qty / $multiplier) / $combo_variations[$key]['quantity'];
} else {
$combo_qty = min($combo_qty, ($variation_qty / $multiplier) / $combo_variations[$key]['quantity']);
}
}
return floor($combo_qty);
}
/**
* Calculates the quantity of combo products based on
* the quantity of variation items used.
*
* @param int $location_id
* @param array $combo_variations
* @return int
*/
public function calculateComboDetails($location_id, $combo_variations)
{
$details = [];
foreach ($combo_variations as $key => $value) {
$variation = Variation::with(['product', 'variation_location_details' => function ($q) use ($location_id) {
$q->where('location_id', $location_id);
}])->findOrFail($value['variation_id']);
$vld = $variation->variation_location_details->first();
$variation_qty = ! empty($vld) ? $vld->qty_available : 0;
$multiplier = $this->getMultiplierOf2Units($variation->product->unit_id, $value['unit_id']);
$details[] = [
'variation_id' => $value['variation_id'],
'product_id' => $variation->product_id,
'qty_required' => $this->num_uf($value['quantity']) * $multiplier,
'enable_stock' => $variation->product->enable_stock,
];
}
return $details;
}
/**
* Calculates the total amount of invoice
*
* @param array $products
* @param int $tax_id
* @param array $discount['discount_type', 'discount_amount']
* @return mixed (false, array)
*/
public function calculateInvoiceTotal($products, $tax_id, $discount = null, $uf_number = true)
{
if (empty($products)) {
return false;
}
$output = ['total_before_tax' => 0, 'tax' => 0, 'discount' => 0, 'final_total' => 0];
//Sub Total
foreach ($products as $product) {
$unit_price_inc_tax = $uf_number ? $this->num_uf($product['unit_price_inc_tax']) : $product['unit_price_inc_tax'];
$quantity = $uf_number ? $this->num_uf($product['quantity']) : $product['quantity'];
$output['total_before_tax'] += $quantity * $unit_price_inc_tax;
//Add modifier price to total if exists
if (! empty($product['modifier_price'])) {
foreach ($product['modifier_price'] as $key => $modifier_price) {
$modifier_price = $uf_number ? $this->num_uf($modifier_price) : $modifier_price;
$uf_modifier_price = $uf_number ? $this->num_uf($modifier_price) : $modifier_price;
$modifier_qty = isset($product['modifier_quantity'][$key]) ? $product['modifier_quantity'][$key] : 0;
$modifier_total = $uf_modifier_price * $modifier_qty;
$output['total_before_tax'] += $modifier_total;
}
}
}
//Calculate discount
if (is_array($discount)) {
$discount_amount = $uf_number ? $this->num_uf($discount['discount_amount']) : $discount['discount_amount'];
if ($discount['discount_type'] == 'fixed') {
$output['discount'] = $discount_amount;
} else {
$output['discount'] = ($discount_amount / 100) * $output['total_before_tax'];
}
}
//Tax
$output['tax'] = 0;
if (! empty($tax_id)) {
$tax_details = TaxRate::find($tax_id);
if (! empty($tax_details)) {
$output['tax_id'] = $tax_id;
$output['tax'] = ($tax_details->amount / 100) * ($output['total_before_tax'] - $output['discount']);
}
}
//Calculate total
$output['final_total'] = $output['total_before_tax'] + $output['tax'] - $output['discount'];
return $output;
}
/**
* Generates product sku
*
* @param string $string
* @return generated sku (string)
*/
public function generateProductSku($string)
{
$business_id = request()->session()->get('user.business_id');
$sku_prefix = Business::where('id', $business_id)->value('sku_prefix');
return $sku_prefix.str_pad($string, 4, '0', STR_PAD_LEFT);
}
/**
* Gives list of trending products
*
* @param int $business_id
* @param array $filters
* @return Obj
*/
public function getTrendingProducts($business_id, $filters = [])
{
$query = Transaction::join(
'transaction_sell_lines as tsl',
'transactions.id',
'=',
'tsl.transaction_id'
)
->join('products as p', 'tsl.product_id', '=', 'p.id')
->leftjoin('units as u', 'u.id', '=', 'p.unit_id')
->where('transactions.business_id', $business_id)
->where('transactions.type', 'sell')
->where('transactions.status', 'final');
$permitted_locations = auth()->user()->permitted_locations();
if ($permitted_locations != 'all') {
$query->whereIn('transactions.location_id', $permitted_locations);
}
if (! empty($filters['location_id'])) {
$query->where('transactions.location_id', $filters['location_id']);
}
if (! empty($filters['category'])) {
$query->where('p.category_id', $filters['category']);
}
if (! empty($filters['sub_category'])) {
$query->where('p.sub_category_id', $filters['sub_category']);
}
if (! empty($filters['brand'])) {
$query->where('p.brand_id', $filters['brand']);
}
if (! empty($filters['unit'])) {
$query->where('p.unit_id', $filters['unit']);
}
if (! empty($filters['limit'])) {
$query->limit($filters['limit']);
} else {
$query->limit(5);
}
if (! empty($filters['product_type'])) {
$query->where('p.type', $filters['product_type']);
}
if (! empty($filters['start_date']) && ! empty($filters['end_date'])) {
$query->whereBetween(DB::raw('date(transaction_date)'), [$filters['start_date'],
$filters['end_date'], ]);
}
// $sell_return_query = "(SELECT SUM(TPL.quantity) FROM transactions AS T JOIN purchase_lines AS TPL ON T.id=TPL.transaction_id WHERE TPL.product_id=tsl.product_id AND T.type='sell_return'";
// if ($permitted_locations != 'all') {
// $sell_return_query .= ' AND T.location_id IN ('
// . implode(',', $permitted_locations) . ') ';
// }
// if (!empty($filters['start_date']) && !empty($filters['end_date'])) {
// $sell_return_query .= ' AND date(T.transaction_date) BETWEEN \'' . $filters['start_date'] . '\' AND \'' . $filters['end_date'] . '\'';
// }
// $sell_return_query .= ')';
$products = $query->select(
DB::raw('(SUM(tsl.quantity) - COALESCE(SUM(tsl.quantity_returned), 0)) as total_unit_sold'),
'p.name as product',
'u.short_name as unit',
'p.sku'
)->whereNull('tsl.parent_sell_line_id')
->groupBy('tsl.product_id')
->orderBy('total_unit_sold', 'desc')
->get();
return $products;
}
/**
* Gives list of products based on products id and variation id
*
* @param int $business_id
* @param int $product_id
* @param int $variation_id = null
* @return Obj
*/
public function getDetailsFromProduct($business_id, $product_id, $variation_id = null)
{
$product = Product::leftjoin('variations as v', 'products.id', '=', 'v.product_id')
->whereNull('v.deleted_at')
->where('products.business_id', $business_id);
if (! is_null($variation_id) && $variation_id !== '0') {
$product->where('v.id', $variation_id);
}
$product->where('products.id', $product_id);
$products = $product->select(
'products.id as product_id',
'products.name as product_name',
'v.id as variation_id',
'v.name as variation_name'
)
->get();
return $products;
}
/**
* F => D (Previous product Increase)
* D => F (All product decrease)
* F => F (Newly added product drerease)
*
* @param object $transaction_before
* @param object $transaction
* @param array $input
* @return void
*/
public function adjustProductStockForInvoice($status_before, $transaction, $input, $uf_data = true)
{
if ($status_before == 'final' && $transaction->status == 'draft') {
foreach ($input['products'] as $product) {
if (! empty($product['transaction_sell_lines_id'])) {
$this->updateProductQuantity($input['location_id'], $product['product_id'], $product['variation_id'], $product['quantity'], 0, null, false);
//Adjust quantity for combo items.
if (isset($product['product_type']) && $product['product_type'] == 'combo') {
//Giving quantity in minus will increase the qty
foreach ($product['combo'] as $value) {
$this->updateProductQuantity($input['location_id'], $value['product_id'], $value['variation_id'], $value['quantity'], 0, null, false);
}
// $this->updateEditedSellLineCombo($product['combo'], $input['location_id']);
}
}
}
} elseif ($status_before == 'draft' && $transaction->status == 'final') {
foreach ($input['products'] as $product) {
$uf_quantity = $uf_data ? $this->num_uf($product['quantity']) : $product['quantity'];
$this->decreaseProductQuantity(
$product['product_id'],
$product['variation_id'],
$input['location_id'],
$uf_quantity
);
//Adjust quantity for combo items.
if (isset($product['product_type']) && $product['product_type'] == 'combo') {
$this->decreaseProductQuantityCombo($product['combo'], $input['location_id']);
//$this->decreaseProductQuantityCombo($product['variation_id'], $input['location_id'], $uf_quantity);
}
}
} elseif ($status_before == 'final' && $transaction->status == 'final') {
foreach ($input['products'] as $product) {
if (empty($product['transaction_sell_lines_id'])) {
$uf_quantity = $uf_data ? $this->num_uf($product['quantity']) : $product['quantity'];
$this->decreaseProductQuantity(
$product['product_id'],
$product['variation_id'],
$input['location_id'],
$uf_quantity
);
//Adjust quantity for combo items.
if (isset($product['product_type']) && $product['product_type'] == 'combo') {
$this->decreaseProductQuantityCombo($product['combo'], $input['location_id']);
//$this->decreaseProductQuantityCombo($product['variation_id'], $input['location_id'], $uf_quantity);
}
}
}
}
}
/**
* Updates variation from purchase screen
*
* @param array $variation_data
* @return void
*/
public function updateProductFromPurchase($variation_data)
{
$variation_details = Variation::where('id', $variation_data['variation_id'])
->with(['product', 'product.product_tax'])
->first();
$tax_rate = 0;
if (! empty($variation_details->product->product_tax->amount)) {
$tax_rate = $variation_details->product->product_tax->amount;
}
if (! isset($variation_data['sell_price_inc_tax'])) {
$variation_data['sell_price_inc_tax'] = $variation_details->sell_price_inc_tax;
}
if (($variation_details->default_purchase_price != $variation_data['pp_without_discount']) ||
($variation_details->sell_price_inc_tax != $variation_data['sell_price_inc_tax'])
) {
//Set default purchase price exc. tax
$variation_details->default_purchase_price = $variation_data['pp_without_discount'];
//Set default purchase price inc. tax
$variation_details->dpp_inc_tax = $this->calc_percentage($variation_details->default_purchase_price, $tax_rate, $variation_details->default_purchase_price);
//Set default sell price inc. tax
$variation_details->sell_price_inc_tax = $variation_data['sell_price_inc_tax'];
//set sell price inc. tax
$variation_details->default_sell_price = $this->calc_percentage_base($variation_details->sell_price_inc_tax, $tax_rate);
//set profit margin
$variation_details->profit_percent = $this->get_percent($variation_details->default_purchase_price, $variation_details->default_sell_price);
$variation_details->save();
}
}
/**
* Generated SKU based on the barcode type.
*
* @param string $sku
* @param string $c
* @param string $barcode_type
* @return void
*/
public function generateSubSku($sku, $c, $barcode_type, $value, $sku_type)
{
// old formate
if($sku_type == 'with_out_variation'){
$sub_sku = $sku.$c;
if (in_array($barcode_type, ['C128', 'C39'])) {
$sub_sku = $sku.'-'.$c;
}
}else {
// new formate
$sub_sku = $sku.preg_replace('/[^a-zA-Z0-9]/', '', $value);
if (in_array($barcode_type, ['C128', 'C39'])) {
$sub_sku = $sku.preg_replace('/[^a-zA-Z0-9]/', '', $value);
}
}
return $sub_sku;
}
/**
* Add rack details.
*
* @param int $business_id
* @param int $product_id
* @param array $product_racks
* @param array $product_racks
* @return void
*/
public function addRackDetails($business_id, $product_id, $product_racks)
{
if (! empty($product_racks)) {
$data = [];
foreach ($product_racks as $location_id => $detail) {
$data[] = ['business_id' => $business_id,
'location_id' => $location_id,
'product_id' => $product_id,
'rack' => ! empty($detail['rack']) ? $detail['rack'] : null,
'row' => ! empty($detail['row']) ? $detail['row'] : null,
'position' => ! empty($detail['position']) ? $detail['position'] : null,
'created_at' => \Carbon::now()->toDateTimeString(),
'updated_at' => \Carbon::now()->toDateTimeString(),
];
}
ProductRack::insert($data);
}
}
/**
* Get rack details.
*
* @param int $business_id
* @param int $product_id
* @return void
*/
public function getRackDetails($business_id, $product_id, $get_location = false)
{
$query = ProductRack::where('product_racks.business_id', $business_id)
->where('product_id', $product_id);
if ($get_location) {
$racks = $query->join('business_locations AS BL', 'product_racks.location_id', '=', 'BL.id')
->select(['product_racks.rack',
'product_racks.row',
'product_racks.position',
'BL.name', ])
->get();
} else {
$racks = collect($query->select(['rack', 'row', 'position', 'location_id'])->get());
$racks = $racks->mapWithKeys(function ($item, $key) {
return [$item['location_id'] => $item->toArray()];
})->toArray();
}
return $racks;
}
/**
* Update rack details.
*
* @param int $business_id
* @param int $product_id
* @param array $product_racks
* @return void
*/
public function updateRackDetails($business_id, $product_id, $product_racks)
{
if (! empty($product_racks)) {
foreach ($product_racks as $location_id => $details) {
ProductRack::where('business_id', $business_id)
->where('product_id', $product_id)
->where('location_id', $location_id)
->update(['rack' => ! empty($details['rack']) ? $details['rack'] : null,
'row' => ! empty($details['row']) ? $details['row'] : null,
'position' => ! empty($details['position']) ? $details['position'] : null,
]);
}
}
}
/**
* Retrieves selling price group price for a product variation.
*
* @param int $variation_id
* @param int $price_group_id
* @param int $tax_id
* @return decimal
*/
public function getVariationGroupPrice($variation_id, $price_group_id, $tax_id)
{
$price_group = VariationGroupPrice::where('variation_id', $variation_id)
->where('price_group_id', $price_group_id)
->select(['price_inc_tax', 'price_type'])
->first();
if(!$price_group){
return [
'price_inc_tax' => '',
'price_exc_tax' => '',
];
}
if(isset($price_group->price_type) && $price_group->price_type == 'percentage'){
//calculate the price
$variation = Variation::find($variation_id);
$price_inc_tax = $this->calc_percentage($variation->sell_price_inc_tax, $price_group->price_inc_tax);
} else {
$price_inc_tax = $price_group->price_inc_tax;
}
$price_exc_tax = $price_inc_tax;
if (! empty($price_inc_tax) && ! empty($tax_id)) {
$tax_amount = TaxRate::where('id', $tax_id)->value('amount');
$price_exc_tax = $this->calc_percentage_base($price_inc_tax, $tax_amount);
}
return [
'price_inc_tax' => $price_inc_tax,
'price_exc_tax' => $price_exc_tax,
];
}
/**
* Creates new variation if not exists.
*
* @param int $business_id
* @param string $name
* @return obj
*/
public function createOrNewVariation($business_id, $name)
{
$variation = VariationTemplate::where('business_id', $business_id)
->where('name', 'like', $name)
->with(['values'])
->first();
if (empty($variation)) {
$variation = VariationTemplate::create([
'business_id' => $business_id,
'name' => $name,
]);
}
return $variation;
}
/**
* Adds opening stock to a single product.
*
* @param int $business_id
* @param obj $product
* @param array $input
* @param obj $transaction_date
* @param int $user_id
* @return void
*/
public function addSingleProductOpeningStock($business_id, $product, $input, $transaction_date, $user_id)
{
$locations = BusinessLocation::forDropdown($business_id)->toArray();
$tax_percent = ! empty($product->product_tax->amount) ? $product->product_tax->amount : 0;
$tax_id = ! empty($product->product_tax->id) ? $product->product_tax->id : null;
foreach ($input as $key => $value) {
$location_id = $key;
$purchase_total = 0;
//Check if valid location
if (array_key_exists($location_id, $locations)) {
$purchase_lines = [];
$purchase_price = $this->num_uf(trim($value['purchase_price']));
$item_tax = $this->calc_percentage($purchase_price, $tax_percent);
$purchase_price_inc_tax = $purchase_price + $item_tax;
$qty = $this->num_uf(trim($value['quantity']));
$exp_date = null;
if (! empty($value['exp_date'])) {
$exp_date = \Carbon::createFromFormat('d-m-Y', $value['exp_date'])->format('Y-m-d');
}
$lot_number = null;
if (! empty($value['lot_number'])) {
$lot_number = $value['lot_number'];
}
if ($qty > 0) {
$qty_formated = $this->num_f($qty);
//Calculate transaction total
$purchase_total += ($purchase_price_inc_tax * $qty);
$variation_id = $product->variations->first()->id;
$purchase_line = new PurchaseLine();
$purchase_line->product_id = $product->id;
$purchase_line->variation_id = $variation_id;
$purchase_line->item_tax = $item_tax;
$purchase_line->tax_id = $tax_id;
$purchase_line->quantity = $qty;
$purchase_line->pp_without_discount = $purchase_price;
$purchase_line->purchase_price = $purchase_price;
$purchase_line->purchase_price_inc_tax = $purchase_price_inc_tax;
$purchase_line->exp_date = $exp_date;
$purchase_line->lot_number = $lot_number;
$purchase_lines[] = $purchase_line;
$this->updateProductQuantity($location_id, $product->id, $variation_id, $qty_formated);
}
//create transaction & purchase lines
if (! empty($purchase_lines)) {
$transaction = Transaction::create(
[
'type' => 'opening_stock',
'opening_stock_product_id' => $product->id,
'status' => 'received',
'business_id' => $business_id,
'transaction_date' => $transaction_date,
'total_before_tax' => $purchase_total,
'location_id' => $location_id,
'final_total' => $purchase_total,
'payment_status' => 'paid',
'created_by' => $user_id,
]
);
$transaction->purchase_lines()->saveMany($purchase_lines);
}
}
}
}
/**
* Add/Edit transaction purchase lines
*
* @param object $transaction
* @param array $input_data
* @param array $currency_details
* @param bool $enable_product_editing
* @param string $before_status = null
* @return array
*/
public function createOrUpdatePurchaseLines($transaction, $input_data, $currency_details, $enable_product_editing, $before_status = null)
{
$updated_purchase_lines = [];
$updated_purchase_line_ids = [0];
$exchange_rate = ! empty($transaction->exchange_rate) ? $transaction->exchange_rate : 1;
foreach ($input_data as $data) {
$multiplier = 1;
if (isset($data['sub_unit_id']) && $data['sub_unit_id'] == $data['product_unit_id']) {
unset($data['sub_unit_id']);
}
if (! empty($data['sub_unit_id'])) {
$unit = Unit::find($data['sub_unit_id']);
$multiplier = ! empty($unit->base_unit_multiplier) ? $unit->base_unit_multiplier : 1;
}
$new_quantity = $this->num_uf($data['quantity']) * $multiplier;
$new_quantity_f = $this->num_f($new_quantity);
$old_qty = 0;
//update existing purchase line
if (isset($data['purchase_line_id'])) {
$purchase_line = PurchaseLine::findOrFail($data['purchase_line_id']);
$updated_purchase_line_ids[] = $purchase_line->id;
$old_qty = $purchase_line->quantity;
$this->updateProductStock($before_status, $transaction, $data['product_id'], $data['variation_id'], $new_quantity, $purchase_line->quantity, $currency_details);
} else {
//create newly added purchase lines
$purchase_line = new PurchaseLine();
$purchase_line->product_id = $data['product_id'];
$purchase_line->variation_id = $data['variation_id'];
//Increase quantity only if status is received
if ($transaction->status == 'received') {
$this->updateProductQuantity($transaction->location_id, $data['product_id'], $data['variation_id'], $new_quantity_f, 0, $currency_details);
}
}
$purchase_line->quantity = $new_quantity;
$purchase_line->pp_without_discount = ($this->num_uf($data['pp_without_discount'], $currency_details) * $exchange_rate) / $multiplier;
$purchase_line->discount_percent = $this->num_uf($data['discount_percent'], $currency_details);
$purchase_line->purchase_price = ($this->num_uf($data['purchase_price'], $currency_details) * $exchange_rate) / $multiplier;
$purchase_line->purchase_price_inc_tax = ($this->num_uf($data['purchase_price_inc_tax'], $currency_details) * $exchange_rate) / $multiplier;
$purchase_line->item_tax = ($this->num_uf($data['item_tax'], $currency_details) * $exchange_rate) / $multiplier;
$purchase_line->tax_id = $data['purchase_line_tax_id'];
$purchase_line->lot_number = ! empty($data['lot_number']) ? $data['lot_number'] : null;
$purchase_line->mfg_date = ! empty($data['mfg_date']) ? $this->uf_date($data['mfg_date']) : null;
$purchase_line->exp_date = ! empty($data['exp_date']) ? $this->uf_date($data['exp_date']) : null;
$purchase_line->sub_unit_id = ! empty($data['sub_unit_id']) ? $data['sub_unit_id'] : null;
$purchase_line->purchase_order_line_id = ! empty($data['purchase_order_line_id']) ? $data['purchase_order_line_id'] : null;
$purchase_line->purchase_requisition_line_id = ! empty($data['purchase_requisition_line_id']) && $transaction->type == 'purchase_order' ? $data['purchase_requisition_line_id'] : null;
if (! empty($data['secondary_unit_quantity'])) {
$purchase_line->secondary_unit_quantity = $this->num_uf($data['secondary_unit_quantity']);
}
$updated_purchase_lines[] = $purchase_line;
//Edit product price
if ($enable_product_editing == 1 && $transaction->type == 'purchase') {
if (isset($data['default_sell_price'])) {
$variation_data['sell_price_inc_tax'] = ($this->num_uf($data['default_sell_price'], $currency_details)) / $multiplier;
}
$variation_data['pp_without_discount'] = ($this->num_uf($data['pp_without_discount'], $currency_details) * $exchange_rate) / $multiplier;
$variation_data['variation_id'] = $purchase_line->variation_id;
$variation_data['purchase_price'] = $purchase_line->purchase_price;
$this->updateProductFromPurchase($variation_data);
}
if ($transaction->type == 'purchase_order') {
//Update purchase requisition line quantity received
$this->updatePurchaseOrderLine($purchase_line->purchase_requisition_line_id, $purchase_line->quantity, $old_qty);
}
//Update purchase order line quantity received
$this->updatePurchaseOrderLine($purchase_line->purchase_order_line_id, $purchase_line->quantity, $old_qty);
}
//unset deleted purchase lines
$delete_purchase_line_ids = [];
$delete_purchase_lines = null;
if (! empty($updated_purchase_line_ids)) {
$delete_purchase_lines = PurchaseLine::where('transaction_id', $transaction->id)
->whereNotIn('id', $updated_purchase_line_ids)
->get();
if ($delete_purchase_lines->count()) {
foreach ($delete_purchase_lines as $delete_purchase_line) {
$delete_purchase_line_ids[] = $delete_purchase_line->id;
//decrease deleted only if previous status was received
if ($before_status == 'received') {
$this->decreaseProductQuantity(
$delete_purchase_line->product_id,
$delete_purchase_line->variation_id,
$transaction->location_id,
$delete_purchase_line->quantity
);
}
//If purchase order line set decrease quntity
if (! empty($delete_purchase_line->purchase_order_line_id)) {
$this->updatePurchaseOrderLine($delete_purchase_line->purchase_order_line_id, 0, $delete_purchase_line->quantity);
}
//If purchase order line set decrease quntity
if (! empty($delete_purchase_line->purchase_requisition_line_id)) {
$this->updatePurchaseOrderLine($delete_purchase_line->purchase_requisition_line_id, 0, $delete_purchase_line->quantity);
}
}
//unset if purchase order line from purchase lines if exists
if ($transaction->type == 'purchase_order') {
PurchaseLine::whereIn('purchase_order_line_id', $delete_purchase_line_ids)
->update(['purchase_order_line_id' => null]);
}
//Delete deleted purchase lines
PurchaseLine::where('transaction_id', $transaction->id)
->whereIn('id', $delete_purchase_line_ids)
->delete();
}
}
//update purchase lines
if (! empty($updated_purchase_lines)) {
$transaction->purchase_lines()->saveMany($updated_purchase_lines);
}
return $delete_purchase_lines;
}
public function updatePurchaseOrderLine($purchase_order_line_id, $new_qty, $old_qty = 0)
{
$diff = $new_qty - $old_qty;
if (! empty($purchase_order_line_id) && ! empty($diff)) {
$purchase_order_line = PurchaseLine::find($purchase_order_line_id);
$purchase_order_line->po_quantity_purchased += ($diff);
$purchase_order_line->save();
}
}
/**
* Updates product stock after adding or updating purchase
*
* @param string $status_before
* @param obj $transaction
* @param int $product_id
* @param int $variation_id
* @param decimal $new_quantity in database format
* @param decimal $old_quantity in database format
* @param array $currency_details
*/
public function updateProductStock($status_before, $transaction, $product_id, $variation_id, $new_quantity, $old_quantity, $currency_details)
{
$new_quantity_f = $this->num_f($new_quantity);
$old_qty = $this->num_f($old_quantity);
//Update quantity for existing products
if ($status_before == 'received' && $transaction->status == 'received') {
//if status received update existing quantity
$this->updateProductQuantity($transaction->location_id, $product_id, $variation_id, $new_quantity_f, $old_qty, $currency_details);
} elseif ($status_before == 'received' && $transaction->status != 'received') {
//decrease quantity only if status changed from received to not received
$this->decreaseProductQuantity(
$product_id,
$variation_id,
$transaction->location_id,
$old_quantity
);
} elseif ($status_before != 'received' && $transaction->status == 'received') {
$this->updateProductQuantity($transaction->location_id, $product_id, $variation_id, $new_quantity_f, 0, $currency_details);
}
}
/**
* Recalculates purchase line data according to subunit data
*
* @param int $purchase_line
* @param int $business_id
* @return array
*/
public function changePurchaseLineUnit($purchase_line, $business_id)
{
$base_unit = $purchase_line->product->unit;
$sub_units = $base_unit->sub_units;
$sub_unit_id = $purchase_line->sub_unit_id;
$sub_unit = $sub_units->filter(function ($item) use ($sub_unit_id) {
return $item->id == $sub_unit_id;
})->first();
if (! empty($sub_unit)) {
$multiplier = $sub_unit->base_unit_multiplier;
$purchase_line->quantity = $purchase_line->quantity / $multiplier;
$purchase_line->pp_without_discount = $purchase_line->pp_without_discount * $multiplier;
$purchase_line->purchase_price = $purchase_line->purchase_price * $multiplier;
$purchase_line->purchase_price_inc_tax = $purchase_line->purchase_price_inc_tax * $multiplier;
$purchase_line->item_tax = $purchase_line->item_tax * $multiplier;
$purchase_line->quantity_returned = $purchase_line->quantity_returned / $multiplier;
$purchase_line->quantity_sold = $purchase_line->quantity_sold / $multiplier;
$purchase_line->quantity_adjusted = $purchase_line->quantity_adjusted / $multiplier;
}
//SubUnits
$purchase_line->sub_units_options = $this->getSubUnits($business_id, $base_unit->id, false, $purchase_line->product_id);
return $purchase_line;
}
/**
* Recalculates sell line data according to subunit data
*
* @param int $unit_id
* @return array
*/
public function changeSellLineUnit($business_id, $sell_line)
{
$unit_details = $this->getSubUnits($business_id, $sell_line->unit_id, false, $sell_line->product_id);
$sub_unit = null;
$sub_unit_id = $sell_line->sub_unit_id;
foreach ($unit_details as $key => $value) {
if ($key == $sub_unit_id) {
$sub_unit = $value;
}
}
if (! empty($sub_unit)) {
$multiplier = $sub_unit['multiplier'];
$sell_line->quantity_ordered = $sell_line->quantity_ordered / $multiplier;
$sell_line->item_tax = $sell_line->item_tax * $multiplier;
$sell_line->default_sell_price = $sell_line->default_sell_price * $multiplier;
$sell_line->unit_price_before_discount = $sell_line->unit_price_before_discount * $multiplier;
$sell_line->sell_price_inc_tax = $sell_line->sell_price_inc_tax * $multiplier;
$sell_line->sub_unit_multiplier = $multiplier;
$sell_line->unit_details = $unit_details;
}
return $sell_line;
}
/**
* Retrieves current stock of a variation for the given location
*
* @param int $variation_id, int location_id
* @return float
*/
public function getCurrentStock($variation_id, $location_id)
{
$current_stock = VariationLocationDetails::where('variation_id', $variation_id)
->where('location_id', $location_id)
->value('qty_available');
if (null == $current_stock) {
$current_stock = 0;
}
return $current_stock;
}
/**
* Adjusts stock over selling with purchases, opening stocks andstock transfers
* Also maps with respective sells
*
* @param obj $transaction
* @return void
*/
public function adjustStockOverSelling($transaction)
{
if ($transaction->status != 'received') {
return false;
}
foreach ($transaction->purchase_lines as $purchase_line) {
if ($purchase_line->product->enable_stock == 1) {
//Available quantity in the purchase line
$purchase_line_qty_avlbl = $purchase_line->quantity_remaining;
if ($purchase_line_qty_avlbl <= 0) {
continue;
}
//update sell line purchase line mapping
$sell_line_purchase_lines =
TransactionSellLinesPurchaseLines::where('purchase_line_id', 0)
->join('transaction_sell_lines as tsl', 'tsl.id', '=', 'transaction_sell_lines_purchase_lines.sell_line_id')
->join('transactions as t', 'tsl.transaction_id', '=', 't.id')
->where('t.location_id', $transaction->location_id)
->where('tsl.variation_id', $purchase_line->variation_id)
->where('tsl.product_id', $purchase_line->product_id)
->select('transaction_sell_lines_purchase_lines.*')
->get();
foreach ($sell_line_purchase_lines as $slpl) {
if ($purchase_line_qty_avlbl > 0) {
if ($slpl->quantity <= $purchase_line_qty_avlbl) {
$purchase_line_qty_avlbl -= $slpl->quantity;
$slpl->purchase_line_id = $purchase_line->id;
$slpl->save();
//update purchase line quantity sold
$purchase_line->quantity_sold += $slpl->quantity;
$purchase_line->save();
} else {
$diff = $slpl->quantity - $purchase_line_qty_avlbl;
$slpl->purchase_line_id = $purchase_line->id;
$slpl->quantity = $purchase_line_qty_avlbl;
$slpl->save();
//update purchase line quantity sold
$purchase_line->quantity_sold += $slpl->quantity;
$purchase_line->save();
TransactionSellLinesPurchaseLines::create([
'sell_line_id' => $slpl->sell_line_id,
'purchase_line_id' => 0,
'quantity' => $diff,
]);
break;
}
}
}
}
}
}
/**
* Finds out most relevant descount for the product
*
* @param obj $product, int $business_id, int $location_id, bool $is_cg,
* bool $is_spg
* @return obj discount
*/
public function getProductDiscount($product, $business_id, $location_id, $is_cg = false, $price_group = null, $variation_id = null)
{
$now = \Carbon::now()->toDateTimeString();
//Search if both category and brand matches
$query = Discount::where('business_id', $business_id)
->where('location_id', $location_id)
->where('is_active', 1)
->where('starts_at', '<=', $now)
->where('ends_at', '>=', $now)
->where(function ($q) use ($product, $variation_id) {
$q->where(function ($sub_q) use ($product) {
if (! empty($product->brand_id)) {
$sub_q->where('brand_id', $product->brand_id);
}
if (! empty($product->category_id)) {
$sub_q->where('category_id', $product->category_id);
}
})
->orWhere(function ($sub_q) use ($product) {
$sub_q->whereRaw('(brand_id="'.$product->brand_id.'" AND category_id IS NULL)')
->orWhereRaw('(category_id="'.$product->category_id.'" AND brand_id IS NULL)');
});
if (! empty($variation_id)) {
$q->orWhereHas('variations', function ($sub_q) use ($variation_id) {
$sub_q->where('variation_id', $variation_id);
});
}
})
->orderBy('priority', 'desc')
->latest();
if ($is_cg) {
$query->where('applicable_in_cg', 1);
}
if (! is_null($price_group)) {
$query->where(function ($q) use ($price_group) {
$q->whereNull('spg')
->orWhere('spg', (string) $price_group);
});
} else {
$query->whereNull('spg');
}
$discount = $query->first();
if (! empty($discount)) {
$discount->formated_starts_at = $this->format_date($discount->starts_at->toDateTimeString(), true);
$discount->formated_ends_at = $this->format_date($discount->ends_at->toDateTimeString(), true);
}
return $discount;
}
/**
* Filters product as per the given inputs and return the details.
*
* @param string $search_type (like or exact)
* @return object
*/
public function filterProduct($business_id, $search_term, $location_id = null, $not_for_selling = null, $price_group_id = null, $product_types = [], $search_fields = [], $check_qty = false, $search_type = 'like')
{
$query = Product::join('variations', 'products.id', '=', 'variations.product_id')
->active()
->whereNull('variations.deleted_at')
->leftjoin('units as U', 'products.unit_id', '=', 'U.id')
->leftjoin(
'variation_location_details AS VLD',
function ($join) use ($location_id) {
$join->on('variations.id', '=', 'VLD.variation_id');
//Include Location
if (! empty($location_id)) {
$join->where(function ($query) use ($location_id) {
$query->where('VLD.location_id', '=', $location_id);
//Check null to show products even if no quantity is available in a location.
//TODO: Maybe add a settings to show product not available at a location or not.
$query->orWhereNull('VLD.location_id');
});
}
}
);
if (! is_null($not_for_selling)) {
$query->where('products.not_for_selling', $not_for_selling);
}
if (! empty($price_group_id)) {
$query->leftjoin(
'variation_group_prices AS VGP',
function ($join) use ($price_group_id) {
$join->on('variations.id', '=', 'VGP.variation_id')
->where('VGP.price_group_id', '=', $price_group_id);
}
);
}
$query->where('products.business_id', $business_id)
->where('products.type', '!=', 'modifier');
if (! empty($product_types)) {
$query->whereIn('products.type', $product_types);
}
if (in_array('lot', $search_fields)) {
$query->leftjoin('purchase_lines as pl', 'variations.id', '=', 'pl.variation_id');
}
//Include search
if (! empty($search_term)) {
//Search with like condition
if ($search_type == 'like') {
$query->where(function ($query) use ($search_term, $search_fields) {
if (in_array('name', $search_fields)) {
$query->where('products.name', 'like', '%'.$search_term.'%');
}
if (in_array('sku', $search_fields)) {
$query->orWhere('sku', 'like', '%'.$search_term.'%');
}
if (in_array('sub_sku', $search_fields)) {
$query->orWhere('sub_sku', 'like', '%'.$search_term.'%');
}
if (in_array('lot', $search_fields)) {
$query->orWhere('pl.lot_number', 'like', '%'.$search_term.'%');
}
if (in_array('product_custom_field1', $search_fields)) {
$query->orWhere('product_custom_field1', 'like', '%'.$search_term.'%');
}
if (in_array('product_custom_field2', $search_fields)) {
$query->orWhere('product_custom_field2', 'like', '%'.$search_term.'%');
}
if (in_array('product_custom_field3', $search_fields)) {
$query->orWhere('product_custom_field3', 'like', '%'.$search_term.'%');
}
if (in_array('product_custom_field4', $search_fields)) {
$query->orWhere('product_custom_field4', 'like', '%'.$search_term.'%');
}
});
}
//Search with exact condition
if ($search_type == 'exact') {
$query->where(function ($query) use ($search_term, $search_fields) {
if (in_array('name', $search_fields)) {
$query->where('products.name', $search_term);
}
if (in_array('sku', $search_fields)) {
$query->orWhere('sku', $search_term);
}
if (in_array('sub_sku', $search_fields)) {
$query->orWhere('sub_sku', $search_term);
}
if (in_array('lot', $search_fields)) {
$query->orWhere('pl.lot_number', $search_term);
}
});
}
}
//Include check for quantity
if ($check_qty) {
$query->where('VLD.qty_available', '>', 0);
}
if (! empty($location_id)) {
$query->ForLocation($location_id);
}
$query->select(
'products.id as product_id',
'products.name',
'products.type',
'products.enable_stock',
'variations.id as variation_id',
'variations.name as variation',
'VLD.qty_available',
'variations.sell_price_inc_tax as selling_price',
'variations.sub_sku',
'U.short_name as unit'
);
if (! empty($price_group_id)) {
$query->addSelect(DB::raw('IF (VGP.price_type = "fixed", VGP.price_inc_tax, VGP.price_inc_tax * variations.sell_price_inc_tax / 100) as variation_group_price'));
}
if (in_array('lot', $search_fields)) {
$query->addSelect('pl.id as purchase_line_id', 'pl.lot_number');
}
$query->groupBy('variations.id');
return $query->orderBy('VLD.qty_available', 'desc')
->get();
}
public function getProductStockDetails($business_id, $filters, $for)
{
$query = Variation::join('products as p', 'p.id', '=', 'variations.product_id')
->join('units', 'p.unit_id', '=', 'units.id')
->leftjoin('variation_location_details as vld', 'variations.id', '=', 'vld.variation_id')
->leftjoin('business_locations as l', 'vld.location_id', '=', 'l.id')
->leftjoin('categories as c', 'p.category_id', '=', 'c.id')
->join('product_variations as pv', 'variations.product_variation_id', '=', 'pv.id')
->where('p.business_id', $business_id)
->whereIn('p.type', ['single', 'variable']);
$permitted_locations = auth()->user()->permitted_locations();
$location_filter = '';
if ($permitted_locations != 'all') {
$query->whereIn('vld.location_id', $permitted_locations);
$locations_imploded = implode(', ', $permitted_locations);
$location_filter .= "AND transactions.location_id IN ($locations_imploded) ";
}
if (! empty($filters['location_id'])) {
$location_id = $filters['location_id'];
$query->where('vld.location_id', $location_id);
$location_filter .= "AND transactions.location_id=$location_id";
//If filter by location then hide products not available in that location
$query->join('product_locations as pl', 'pl.product_id', '=', 'p.id')
->where(function ($q) use ($location_id) {
$q->where('pl.location_id', $location_id);
});
}
if (! empty($filters['category_id'])) {
$query->where('p.category_id', $filters['category_id']);
}
if (! empty($filters['sub_category_id'])) {
$query->where('p.sub_category_id', $filters['sub_category_id']);
}
if (! empty($filters['brand_id'])) {
$query->where('p.brand_id', $filters['brand_id']);
}
if (! empty($filters['unit_id'])) {
$query->where('p.unit_id', $filters['unit_id']);
}
if (! empty($filters['tax_id'])) {
$query->where('p.tax', $filters['tax_id']);
}
if (! empty($filters['type'])) {
$query->where('p.type', $filters['type']);
}
if (isset($filters['only_mfg_products']) && $filters['only_mfg_products'] == 1) {
$query->join('mfg_recipes as mr', 'mr.variation_id', '=', 'variations.id');
}
if (isset($filters['active_state']) && $filters['active_state'] == 'active') {
$query->where('p.is_inactive', 0);
}
if (isset($filters['active_state']) && $filters['active_state'] == 'inactive') {
$query->where('p.is_inactive', 1);
}
if (isset($filters['not_for_selling']) && $filters['not_for_selling'] == 1) {
$query->where('p.not_for_selling', 1);
}
if (! empty($filters['repair_model_id'])) {
$query->where('p.repair_model_id', request()->get('repair_model_id'));
}
//TODO::Check if result is correct after changing LEFT JOIN to INNER JOIN
$pl_query_string = $this->get_pl_quantity_sum_string('pl');
if ($for == 'view_product' && ! empty(request()->input('product_id'))) {
$location_filter = 'AND transactions.location_id=l.id';
}
$products = $query->select(
// DB::raw("(SELECT SUM(quantity) FROM transaction_sell_lines LEFT JOIN transactions ON transaction_sell_lines.transaction_id=transactions.id WHERE transactions.status='final' $location_filter AND
// transaction_sell_lines.product_id=products.id) as total_sold"),
DB::raw("(SELECT SUM(TSL.quantity - TSL.quantity_returned) FROM transactions
JOIN transaction_sell_lines AS TSL ON transactions.id=TSL.transaction_id
WHERE transactions.status='final' AND transactions.type='sell' AND transactions.location_id=vld.location_id
AND TSL.variation_id=variations.id) as total_sold"),
DB::raw("(SELECT SUM(IF(transactions.type='sell_transfer', TSL.quantity, 0) ) FROM transactions
JOIN transaction_sell_lines AS TSL ON transactions.id=TSL.transaction_id
WHERE transactions.status='final' AND transactions.type='sell_transfer' AND transactions.location_id=vld.location_id AND (TSL.variation_id=variations.id)) as total_transfered"),
DB::raw("(SELECT SUM(IF(transactions.type='stock_adjustment', SAL.quantity, 0) ) FROM transactions
JOIN stock_adjustment_lines AS SAL ON transactions.id=SAL.transaction_id
WHERE transactions.type='stock_adjustment' AND transactions.location_id=vld.location_id
AND (SAL.variation_id=variations.id)) as total_adjusted"),
DB::raw("(SELECT SUM( COALESCE(pl.quantity - ($pl_query_string), 0) * purchase_price_inc_tax) FROM transactions
JOIN purchase_lines AS pl ON transactions.id=pl.transaction_id
WHERE (transactions.status='received' OR transactions.type='purchase_return') AND transactions.location_id=vld.location_id
AND (pl.variation_id=variations.id)) as stock_price"),
DB::raw('SUM(vld.qty_available) as stock'),
'variations.sub_sku as sku',
'p.name as product',
'p.type',
'p.alert_quantity',
'p.id as product_id',
'units.short_name as unit',
'p.enable_stock as enable_stock',
'variations.sell_price_inc_tax as unit_price',
'pv.name as product_variation',
'variations.name as variation_name',
'l.name as location_name',
'l.id as location_id',
'variations.id as variation_id',
'c.name as category_name',
'p.product_custom_field1',
'p.product_custom_field2',
'p.product_custom_field3',
'p.product_custom_field4'
)->groupBy('variations.id', 'vld.location_id');
if (isset($filters['show_manufacturing_data']) && $filters['show_manufacturing_data']) {
$pl_query_string = $this->get_pl_quantity_sum_string('PL');
$products->addSelect(
DB::raw("(SELECT COALESCE(SUM(PL.quantity - ($pl_query_string)), 0) FROM transactions
JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.status='received' AND transactions.type='production_purchase' AND transactions.location_id=vld.location_id
AND (PL.variation_id=variations.id)) as total_mfg_stock")
);
}
if (! empty($filters['product_id'])) {
$products->where('p.id', $filters['product_id'])
->groupBy('l.id');
}
if ($for == 'view_product') {
return $products->get();
} elseif ($for == 'api') {
return $products->paginate();
} else {
return $products;
}
}
/**
* Gives the details of combo product
*
* @param array $combo_variations
* @param int $business_id
* @return array
*/
public function __getComboProductDetails($combo_variations, $business_id)
{
foreach ($combo_variations as $key => $value) {
$combo_variations[$key]['variation'] =
Variation::with(['product'])
->find($value['variation_id']);
$combo_variations[$key]['sub_units'] = $this->getSubUnits($business_id, $combo_variations[$key]['variation']['product']->unit_id, true);
$combo_variations[$key]['multiplier'] = 1;
if (! empty($combo_variations[$key]['sub_units'])) {
if (isset($combo_variations[$key]['sub_units'][$combo_variations[$key]['unit_id']])) {
$combo_variations[$key]['multiplier'] = $combo_variations[$key]['sub_units'][$combo_variations[$key]['unit_id']]['multiplier'];
$combo_variations[$key]['unit_name'] = $combo_variations[$key]['sub_units'][$combo_variations[$key]['unit_id']]['name'];
}
}
}
return $combo_variations;
}
public function getVariationStockDetails($business_id, $variation_id, $location_id)
{
$purchase_details = Variation::join('products as p', 'p.id', '=', 'variations.product_id')
->join('units', 'p.unit_id', '=', 'units.id')
->leftjoin('units as u', 'p.secondary_unit_id', '=', 'u.id')
->leftjoin('product_variations as pv', 'variations.product_variation_id', '=', 'pv.id')
->leftjoin('purchase_lines as pl', 'pl.variation_id', '=', 'variations.id')
->leftjoin('transactions as t', 'pl.transaction_id', '=', 't.id')
->where('t.location_id', $location_id)
//->where('t.status', 'received')
->where('p.business_id', $business_id)
->where('variations.id', $variation_id)
->select(
DB::raw("SUM(IF(t.type='purchase' AND t.status='received', pl.quantity, 0)) as total_purchase"),
DB::raw("SUM(IF(t.type='purchase' OR t.type='purchase_return', pl.quantity_returned, 0)) as total_purchase_return"),
DB::raw('SUM(pl.quantity_adjusted) as total_adjusted'),
DB::raw("SUM(IF(t.type='opening_stock', pl.quantity, 0)) as total_opening_stock"),
DB::raw("SUM(IF(t.type='purchase_transfer', pl.quantity, 0)) as total_purchase_transfer"),
'variations.sub_sku as sub_sku',
'p.name as product',
'p.type',
'p.sku',
'p.id as product_id',
'units.short_name as unit',
'u.short_name as second_unit',
'pv.name as product_variation',
'variations.name as variation_name',
'variations.id as variation_id'
)
->get()->first();
$sell_details = Variation::join('products as p', 'p.id', '=', 'variations.product_id')
->leftjoin('transaction_sell_lines as sl', 'sl.variation_id', '=', 'variations.id')
->join('transactions as t', 'sl.transaction_id', '=', 't.id')
->where('t.location_id', $location_id)
->where('t.status', 'final')
->where('p.business_id', $business_id)
->where('variations.id', $variation_id)
->select(
DB::raw("SUM(IF(t.type='sell', sl.quantity, 0)) as total_sold"),
DB::raw("SUM(IF(t.type='sell', sl.quantity_returned, 0)) as total_sell_return"),
DB::raw("SUM(IF(t.type='sell_transfer', sl.quantity, 0)) as total_sell_transfer")
)
->get()->first();
$current_stock = VariationLocationDetails::where('variation_id',
$variation_id)
->where('location_id', $location_id)
->first();
if ($purchase_details->type == 'variable') {
$product_name = $purchase_details->product.' - '.$purchase_details->product_variation.' - '.$purchase_details->variation_name.' ('.$purchase_details->sub_sku.')';
} else {
$product_name = $purchase_details->product.' ('.$purchase_details->sku.')';
}
$output = [
'variation' => $product_name,
'unit' => $purchase_details->unit,
'second_unit' => $purchase_details->second_unit,
'total_purchase' => $purchase_details->total_purchase,
'total_purchase_return' => $purchase_details->total_purchase_return,
'total_adjusted' => $purchase_details->total_adjusted,
'total_opening_stock' => $purchase_details->total_opening_stock,
'total_purchase_transfer' => $purchase_details->total_purchase_transfer,
'total_sold' => $sell_details->total_sold,
'total_sell_return' => $sell_details->total_sell_return,
'total_sell_transfer' => $sell_details->total_sell_transfer,
'current_stock' => $current_stock->qty_available ?? 0,
];
return $output;
}
public function getVariationStockHistory($business_id, $variation_id, $location_id)
{
$stock_history = Transaction::leftjoin('transaction_sell_lines as sl',
'sl.transaction_id', '=', 'transactions.id')
->leftjoin('purchase_lines as pl',
'pl.transaction_id', '=', 'transactions.id')
->leftjoin('stock_adjustment_lines as al',
'al.transaction_id', '=', 'transactions.id')
->leftjoin('transactions as return', 'transactions.return_parent_id', '=', 'return.id')
->leftjoin('purchase_lines as rpl',
'rpl.transaction_id', '=', 'return.id')
->leftjoin('transaction_sell_lines as rsl',
'rsl.transaction_id', '=', 'return.id')
->leftjoin('contacts as c', 'transactions.contact_id', '=', 'c.id')
->where('transactions.location_id', $location_id)
->where(function ($q) use ($variation_id) {
$q->where('sl.variation_id', $variation_id)
->orWhere('pl.variation_id', $variation_id)
->orWhere('al.variation_id', $variation_id)
->orWhere('rpl.variation_id', $variation_id)
->orWhere('rsl.variation_id', $variation_id);
})
->whereIn('transactions.type', ['sell', 'purchase', 'stock_adjustment', 'opening_stock', 'sell_transfer', 'purchase_transfer', 'production_purchase', 'purchase_return', 'sell_return', 'production_sell'])
->select(
'transactions.id as transaction_id',
'transactions.type as transaction_type',
'sl.quantity as sell_line_quantity',
'pl.quantity as purchase_line_quantity',
'rsl.quantity_returned as sell_return',
'rpl.quantity_returned as purchase_return',
'al.quantity as stock_adjusted',
'pl.quantity_returned as combined_purchase_return',
'transactions.return_parent_id',
'transactions.transaction_date',
'transactions.status',
'transactions.invoice_no',
'transactions.ref_no',
'transactions.additional_notes',
'c.name as contact_name',
'c.supplier_business_name',
'pl.secondary_unit_quantity as purchase_secondary_unit_quantity',
'sl.secondary_unit_quantity as sell_secondary_unit_quantity'
)
->orderBy('transactions.transaction_date', 'asc')
->get();
$stock_history_array = [];
$stock = 0;
$stock_in_second_unit = 0;
foreach ($stock_history as $stock_line) {
$temp_array = [
'date' => $stock_line->transaction_date,
'transaction_id' => $stock_line->transaction_id,
'contact_name' => $stock_line->contact_name,
'supplier_business_name' => $stock_line->supplier_business_name,
];
if ($stock_line->transaction_type == 'sell') {
if ($stock_line->status != 'final') {
continue;
}
$quantity_change = -1 * $stock_line->sell_line_quantity;
$stock += $quantity_change;
$stock_in_second_unit -= $stock_line->sell_secondary_unit_quantity;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'sell',
'type_label' => __('sale.sale'),
'ref_no' => $stock_line->invoice_no,
'sell_secondary_unit_quantity' => ! empty($stock_line->sell_secondary_unit_quantity) ? $this->roundQuantity($stock_line->sell_secondary_unit_quantity) : 0,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'purchase') {
if ($stock_line->status != 'received') {
continue;
}
$quantity_change = $stock_line->purchase_line_quantity;
$stock += $quantity_change;
$stock_in_second_unit += $stock_line->purchase_secondary_unit_quantity;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'purchase',
'type_label' => __('lang_v1.purchase'),
'ref_no' => $stock_line->ref_no,
'purchase_secondary_unit_quantity' => ! empty($stock_line->purchase_secondary_unit_quantity) ? $this->roundQuantity($stock_line->purchase_secondary_unit_quantity) : 0,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'stock_adjustment') {
$quantity_change = -1 * $stock_line->stock_adjusted;
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'stock_adjustment',
'type_label' => __('stock_adjustment.stock_adjustment'),
'ref_no' => $stock_line->ref_no,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'opening_stock') {
$quantity_change = $stock_line->purchase_line_quantity;
$stock += $quantity_change;
$stock_in_second_unit += $stock_line->purchase_secondary_unit_quantity;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'opening_stock',
'type_label' => __('report.opening_stock'),
'ref_no' => $stock_line->ref_no ?? '',
'additional_notes' => $stock_line->additional_notes,
'purchase_secondary_unit_quantity' => ! empty($stock_line->purchase_secondary_unit_quantity) ? $this->roundQuantity($stock_line->purchase_secondary_unit_quantity) : 0,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'sell_transfer') {
if ($stock_line->status != 'final') {
continue;
}
$quantity_change = -1 * $stock_line->sell_line_quantity;
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'sell_transfer',
'type_label' => __('lang_v1.stock_transfers').' ('.__('lang_v1.out').')',
'ref_no' => $stock_line->ref_no,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'purchase_transfer') {
if ($stock_line->status != 'received') {
continue;
}
$quantity_change = $stock_line->purchase_line_quantity;
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'purchase_transfer',
'type_label' => __('lang_v1.stock_transfers').' ('.__('lang_v1.in').')',
'ref_no' => $stock_line->ref_no,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'production_sell') {
if ($stock_line->status != 'final') {
continue;
}
$quantity_change = -1 * $stock_line->sell_line_quantity;
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'sell',
'type_label' => __('manufacturing::lang.ingredient'),
'ref_no' => '',
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'production_purchase') {
$quantity_change = $stock_line->purchase_line_quantity;
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'production_purchase',
'type_label' => __('manufacturing::lang.manufactured'),
'ref_no' => $stock_line->ref_no,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'purchase_return') {
$quantity_change = -1 * ($stock_line->combined_purchase_return + $stock_line->purchase_return);
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'purchase_return',
'type_label' => __('lang_v1.purchase_return'),
'ref_no' => $stock_line->ref_no,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
} elseif ($stock_line->transaction_type == 'sell_return') {
$quantity_change = $stock_line->sell_return;
$stock += $quantity_change;
$stock_history_array[] = array_merge($temp_array, [
'quantity_change' => $quantity_change,
'stock' => $this->roundQuantity($stock),
'type' => 'purchase_transfer',
'type_label' => __('lang_v1.sell_return'),
'ref_no' => $stock_line->invoice_no,
'stock_in_second_unit' => $this->roundQuantity($stock_in_second_unit),
]);
}
}
return array_reverse($stock_history_array);
}
/**
* Function to calculate stock mismatches for all the variations or
* the given variation in a location
*/
public function getVariationStockMisMatch($business_id, $variation_id, $location_id)
{
$query = Variation::leftjoin('products as p', 'p.id', '=', 'variations.product_id')
->leftjoin('units', 'p.unit_id', '=', 'units.id')
->leftjoin('variation_location_details as vld', 'variations.id', '=', 'vld.variation_id')
->leftjoin('product_variations as pv', 'variations.product_variation_id', '=', 'pv.id')
->where('p.business_id', $business_id)
->where('vld.location_id', $location_id);
if (! is_null($variation_id)) {
$query->where('variations.id', $variation_id);
}
$stock_details = $query->select(
DB::raw("(SELECT SUM(COALESCE(TSL.quantity, 0)) FROM transactions
LEFT JOIN transaction_sell_lines AS TSL ON transactions.id=TSL.transaction_id
WHERE transactions.status='final' AND transactions.type='sell' AND transactions.location_id=$location_id
AND TSL.variation_id=variations.id) as total_sold"),
DB::raw("(SELECT SUM(COALESCE(TSL.quantity_returned, 0)) FROM transactions
LEFT JOIN transaction_sell_lines AS TSL ON transactions.id=TSL.transaction_id
WHERE transactions.status='final' AND transactions.type='sell' AND transactions.location_id=$location_id
AND TSL.variation_id=variations.id) as total_sell_return"),
DB::raw("(SELECT SUM(COALESCE(TSL.quantity,0)) FROM transactions
LEFT JOIN transaction_sell_lines AS TSL ON transactions.id=TSL.transaction_id
WHERE transactions.status='final' AND transactions.type='sell_transfer' AND transactions.location_id=$location_id
AND TSL.variation_id=variations.id) as total_sell_transfered"),
DB::raw("(SELECT SUM(COALESCE(PL.quantity,0)) FROM transactions
LEFT JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.status='received' AND transactions.type='purchase_transfer' AND transactions.location_id=$location_id
AND PL.variation_id=variations.id) as total_purchase_transfered"),
DB::raw("(SELECT SUM(COALESCE(SAL.quantity, 0)) FROM transactions
LEFT JOIN stock_adjustment_lines AS SAL ON transactions.id=SAL.transaction_id
WHERE transactions.type='stock_adjustment' AND transactions.location_id=$location_id
AND SAL.variation_id=variations.id) as total_adjusted"),
DB::raw("(SELECT SUM(COALESCE(PL.quantity, 0)) FROM transactions
LEFT JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.status='received' AND transactions.type='purchase' AND transactions.location_id=$location_id
AND PL.variation_id=variations.id) as total_purchased"),
DB::raw("(SELECT SUM(COALESCE(PL.quantity_returned, 0)) FROM transactions
LEFT JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.status='received' AND transactions.type='purchase' AND transactions.location_id=$location_id
AND PL.variation_id=variations.id) as total_purchase_return"),
DB::raw("(SELECT SUM(COALESCE(PL.quantity_returned, 0)) FROM transactions
LEFT JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.type='purchase_return' AND transactions.location_id=$location_id
AND PL.variation_id=variations.id) as total_combined_purchase_return"),
DB::raw("(SELECT SUM(COALESCE(PL.quantity, 0)) FROM transactions
LEFT JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.type='opening_stock' AND transactions.status='received' AND transactions.location_id=$location_id
AND PL.variation_id=variations.id) as total_opening_stock"),
DB::raw("(SELECT SUM(COALESCE(PL.quantity, 0)) FROM transactions
LEFT JOIN purchase_lines AS PL ON transactions.id=PL.transaction_id
WHERE transactions.status='received' AND transactions.type='production_purchase' AND transactions.location_id=$location_id
AND PL.variation_id=variations.id) as total_manufactured"),
DB::raw("(SELECT SUM(COALESCE(TSL.quantity, 0)) FROM transactions
LEFT JOIN transaction_sell_lines AS TSL ON transactions.id=TSL.transaction_id
WHERE transactions.status='final' AND transactions.type='production_sell' AND transactions.location_id=$location_id
AND TSL.variation_id=variations.id) as total_ingredients_used"),
DB::raw('SUM(vld.qty_available) as stock'),
'variations.sub_sku as sub_sku',
'p.name as product',
'p.id as product_id',
'p.type',
'p.sku as sku',
'units.short_name as unit',
'p.enable_stock as enable_stock',
'variations.sell_price_inc_tax as unit_price',
'pv.name as product_variation',
'variations.name as variation_name',
'variations.id as variation_id'
)
->groupBy('variations.id')
->get();
foreach ($stock_details as $index => $row) {
$total_sold = $row->total_sold ?: 0;
$total_sell_return = $row->total_sell_return ?: 0;
$total_sell_transfered = $row->total_sell_transfered ?: 0;
$total_purchase_transfered = $row->total_purchase_transfered ?: 0;
$total_adjusted = $row->total_adjusted ?: 0;
$total_purchased = $row->total_purchased ?: 0;
$total_purchase_return = $row->total_purchase_return ?: 0;
$total_combined_purchase_return = $row->total_combined_purchase_return ?: 0;
$total_opening_stock = $row->total_opening_stock ?: 0;
$total_manufactured = $row->total_manufactured ?: 0;
$total_ingredients_used = $row->total_ingredients_used ?: 0;
$total_stock_calculated = $total_opening_stock + $total_purchased + $total_purchase_transfered + $total_sell_return + $total_manufactured
- ($total_sold + $total_sell_transfered + $total_adjusted + $total_purchase_return + $total_combined_purchase_return + $total_ingredients_used);
$stock_details[$index]->total_stock_calculated = $total_stock_calculated;
}
return $stock_details;
}
public function fixVariationStockMisMatch($business_id, $variation_id, $location_id, $stock)
{
$vld = VariationLocationDetails::join(
'business_locations as bl',
'bl.id',
'=',
'variation_location_details.location_id'
)
->where('variation_location_details.location_id', $location_id)
->where('variation_id', $variation_id)
->where('bl.business_id', $business_id)
->select('variation_location_details.*')
->first();
if (! empty($vld)) {
$vld->qty_available = $stock;
$vld->save();
}
//check if there are more such rows then delete it because one location can have only one variation_location_details
$vld_duplicate = VariationLocationDetails::join(
'business_locations as bl',
'bl.id',
'=',
'variation_location_details.location_id'
)
->where('variation_location_details.location_id', $location_id)
->where('variation_id', $variation_id)
->where('bl.business_id', $business_id)
->where('variation_location_details.id', '!=', $vld->id)
->delete();
}
/**
* Return the products less than alert quntity.
*
* @return array
*/
public function getProductAlert($business_id, $permitted_locations = null)
{
$query = VariationLocationDetails::join(
'product_variations as pv',
'variation_location_details.product_variation_id',
'=',
'pv.id'
)
->join(
'variations as v',
'variation_location_details.variation_id',
'=',
'v.id'
)
->join(
'products as p',
'variation_location_details.product_id',
'=',
'p.id'
)
->leftjoin(
'business_locations as l',
'variation_location_details.location_id',
'=',
'l.id'
)
->leftjoin('units as u', 'p.unit_id', '=', 'u.id')
->where('p.business_id', $business_id)
->where('p.enable_stock', 1)
->where('p.is_inactive', 0)
->whereNull('v.deleted_at')
->whereNotNull('p.alert_quantity')
->whereRaw('variation_location_details.qty_available <= p.alert_quantity');
//Check for permitted locations of a user
if(!empty($permitted_locations)){
if ($permitted_locations != 'all') {
$query->whereIn('variation_location_details.location_id', $permitted_locations);
}
}
if (! empty(request()->input('location_id'))) {
$query->where('variation_location_details.location_id', request()->input('location_id'));
}
$products = $query->select(
'p.name as product',
'p.type',
'p.sku',
'pv.name as product_variation',
'v.name as variation',
'v.sub_sku',
'l.name as location',
'variation_location_details.qty_available as stock',
'u.short_name as unit'
)
->groupBy('variation_location_details.id')
->orderBy('stock', 'asc');
return $products;
}
}