function get_item_edit_info($stock_id)
{
- $sql = "SELECT material_cost + labour_cost + overhead_cost AS standard_cost, units, decimals
- FROM ".TB_PREF."stock_master,".TB_PREF."item_units
+ $sql = "SELECT item.material_cost, item.units, unit.decimals
+ FROM ".TB_PREF."stock_master item,"
+ .TB_PREF."item_units unit
WHERE stock_id=".db_escape($stock_id)
- ." AND ".TB_PREF."stock_master.units=".TB_PREF."item_units.abbr";
+ ." AND item.units=unit.abbr";
$result = db_query($sql, "The standard cost cannot be retrieved");
- return db_fetch($result);
+ $row = db_fetch($result);
+ if (is_array($row) && $row['decimals'] == -1)
+ $row['decimals'] = user_qty_dec();
+ return $row;
}
//--------------------------------------------------------------------------------------
-function get_standard_cost($stock_id)
+function get_unit_cost($stock_id)
{
- $sql = "SELECT (material_cost + labour_cost + overhead_cost) AS std_cost
- FROM ".TB_PREF."stock_master s WHERE stock_id=".db_escape($stock_id);
+ $sql = "SELECT material_cost
+ FROM ".TB_PREF."stock_master
+ WHERE stock_id=".db_escape($stock_id);
$result = db_query($sql, "The standard cost cannot be retrieved");
$myrow = db_fetch_row($result);
- return $myrow[0];
+ return is_array($myrow) ? $myrow[0] : false;
+}
+
+//--------------------------------------------------------------------------------------
+
+function get_purchase_cost($stock_id)
+{
+ $sql = "SELECT purchase_cost
+ FROM ".TB_PREF."stock_master
+ WHERE stock_id=".db_escape($stock_id);
+ $result = db_query($sql, "The purchase cost cannot be retrieved");
+
+ $myrow = db_fetch_row($result);
+
+ return is_array($myrow) ? $myrow[0] : false;
}
//--------------------------------------------------------------------------------------
function is_inventory_item($stock_id)
{
- $sql = "SELECT stock_id FROM ".TB_PREF."stock_master
+ $sql = "SELECT stock_id FROM "
+ .TB_PREF."stock_master
WHERE stock_id=".db_escape($stock_id)." AND mb_flag <> 'D'";
$result = db_query($sql, "Cannot query is inventory item or not");
AND type=".ST_CUSTDELIVERY." AND trans_no=".db_escape($trans_no);
$result = db_query($sql, "Could not get stock moves");
$row = db_fetch_row($result);
- return $row[0];
+ return is_array($row) ? $row[0] : false;
}
-
+/*
+ Returns start move_id in latest negative status period for $stock_id
+ FIXME: $to ?
+*/
function last_negative_stock_trans_id($stock_id, $to)
{
$sql = "SELECT * from ".TB_PREF."stock_moves
return db_fetch_row($result);
}
-function get_deliveries_from_trans($stock_id, $from)
+/*
+ Returns quantity and total cost of $stock_id sales, entered after record with $move_id
+*/
+function get_deliveries_from_trans($stock_id, $move_id)
{
// -ve qty is delivery either by ST_CUSTDELIVERY or inventory adjustment
//Price for GRN and SUPPCREDIT and std_cost for other trans_types
$sql = "SELECT SUM(-qty), SUM(-qty*IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost))
FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)." AND qty < 0 AND
- trans_id>='$from' GROUP BY stock_id";
+ trans_id>='$move_id' GROUP BY stock_id";
$result = db_query($sql, "The deliveries could not be updated");
$row = db_fetch_row($result);
$sql = "SELECT IF(type=".ST_SUPPRECEIVE." OR type=".ST_SUPPCREDIT.", price, standard_cost)
FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)
- ." AND trans_id ='$from'";
+ ." AND trans_id ='$move_id'";
$result = db_query($sql, "The deliveries could not be updated");
$cost = db_fetch_row($result);
$sql = "SELECT SUM(qty)
FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)." AND
- trans_id<'$from' GROUP BY stock_id";
+ trans_id<'$move_id' GROUP BY stock_id";
$result = db_query($sql, "The deliveries could not be updated");
$qoh = db_fetch_row($result);
return array($qty,$final_cost);
}
-function get_purchases_from_trans($stock_id, $from)
+/*
+ Returns quantity and total cost of $stock_id purchases, entered after record with $move_id
+*/
+function get_purchases_from_trans($stock_id, $move_id)
{
// Calculate All inward stock moves i.e. qty > 0
$sql = "SELECT SUM(qty), SUM(qty*standard_cost)
FROM ".TB_PREF."stock_moves
WHERE stock_id=".db_escape($stock_id)." AND qty > 0 AND
- trans_id>'$from' GROUP BY stock_id";
+ trans_id>'$move_id' GROUP BY stock_id";
$result = db_query($sql, "Could not get get_purchases_from_trans");
$row = db_fetch_row($result);
}
//-------------------------------------------------------------------
-
+/*
+ This routine fixes stock and COGS balances for all $stock_id sales made during negative inventory status.
+ This is called when delivery is received causing inventory status to be positive again.
+*/
function adjust_deliveries($stock_id, $material_cost, $to)
{
global $Refs;
if (!is_inventory_item($stock_id))
return;
- $from = last_negative_stock_trans_id($stock_id, $to);
- if ($from == false || $from == -1)
+ $move_id = last_negative_stock_trans_id($stock_id, $to);
+ if ($move_id == false || $move_id == -1)
return;
- $row = get_deliveries_from_trans($stock_id, $from);
-
+ $row = get_deliveries_from_trans($stock_id, $move_id);
+
if ($row == false)
return;
$old_sales_cost = $row[1];
$new_sales_cost = $row[0] * $material_cost;
$sales_diff = $new_sales_cost - $old_sales_cost;
- $row = get_purchases_from_trans($stock_id, $from);
+ $row = get_purchases_from_trans($stock_id, $move_id);
$purchase_diff = 0;
$old_purchase_cost = $new_purchase_cost = 0;
if ($row != false)
{
/*Gets the GL Codes relevant to the item account */
$sql = "SELECT mb_flag, inventory_account, cogs_account,
- adjustment_account, sales_account, assembly_account, dimension_id, dimension2_id FROM
+ adjustment_account, sales_account, wip_account, dimension_id, dimension2_id FROM
".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
$get = db_query($sql,"retreive stock gl code");
return db_fetch($get);
}
+function get_purchase_value($stock_id)
+{
+ $sql = "SELECT purchase_cost FROM
+ ".TB_PREF."stock_master WHERE stock_id = ".db_escape($stock_id);
+
+ $result = db_query($sql,"retreive stock purchase price");
+ $row = db_fetch_row($result);
+ return is_array($row) ? $row[0] : false;
+}
+
+function update_purchase_value($stock_id, $price)
+{
+ $price = round2($price, user_price_dec());
+ $sql = "UPDATE ".TB_PREF."stock_master SET purchase_cost=".db_escape($price)
+ ." WHERE stock_id=".db_escape($stock_id);
+ db_query($sql, "The stock master purchase_cost cannot be updated");
+}
//-----------------------------------------------------------------------------------------
function handle_negative_inventory($stock_id, $quantity, $standard_cost, $date_)
$id = get_next_trans_no(ST_JOURNAL);
$ref = $Refs->get_next(ST_JOURNAL, null, $date_);
- $diff = round($qoh*get_standard_cost($stock_id) + $quantity*$standard_cost, user_price_dec());
+ $diff = round($qoh*get_unit_cost($stock_id) + $quantity*$standard_cost, user_price_dec());
if ($diff != 0)
{
function get_stock_moves($type, $type_no)
{
- $sql = "SELECT move.*, item.description, item.mb_flag, item.units, stock.location_name,
- item.material_cost + item.labour_cost + item.overhead_cost AS FixedStandardCost
+ $sql = "SELECT move.*, item.description, item.mb_flag, item.units, stock.location_name
FROM ".TB_PREF."stock_moves move,"
.TB_PREF."locations stock,"
.TB_PREF."stock_master item
projects / fa-stable.git / blobdiff