<?php

require_once($path_to_root.'/'.'modules/order_line_extra/includes/sql_set.inc');
require_once($path_to_root.'/'.'sales/includes/sales_db.inc');
function process_picking_flag() {
	$id = find_submit('_chgpck', false);
	if ($id != null && $id != -1 && isset($_POST['pick'])) {
		$id = str_replace("_update", "", $id);
		change_pck_flag($id, isset($_POST['pick'][$id]));
	}
}

function change_pck_flag($id, $value=null)
{
	if($value === null) $value = true;
	global	$Ajax;

	$picker = new Picker();
	list($debtor_no, $branch_code) = explode('_', $id);

	// insert or remove pick order form pick table initial_quantity 
	if($picker->picked($debtor_no, $branch_code)) {
		$picker->deleteAllForBranch($debtor_no, $branch_code);
	}
	else {
		$picker->insertAllForBranch($debtor_no, $branch_code);
	}
	$Ajax->activate('order_line_view');
}


class Picker extends OrderAction {
	function __construct($data=null) {
		if($data === null) $data = array();
		parent::__construct($data);
	}
	function picked($debtor_no, $branch_code) {
		$sql = "SELECT *
			FROM ".TB_PREF."sales_orders so
			NATURAL JOIN ".TB_PREF."sales_order_details sod
			JOIN ".TB_PREF."topick p on (sod.id = p.detail_id)
			WHERE debtor_no = $debtor_no AND branch_code = $branch_code
			";

		return db_num_rows(db_query($sql)) > 0;

	}

	function getBranch($detail_id) {
	$sql = "SELECT stk_code AS stock_id
		, order_no AS order_id
		, debtor_no
		, branch_code
		, quantity-qty_sent AS quantity
		FROM ".TB_PREF."sales_orders so
		NATURAL JOIN ".TB_PREF."sales_order_details sod
		WHERE sod.id = $detail_id
		";
	$result = db_query($sql);
	$row = db_fetch($result);

	return $row;
		
	}
	function insertForDetail($detail_id) {
	display_warning("picked $detail_id");
		$branch = $this->getBranch($detail_id);
		$debtor_no = $branch->debtor_no;
		$branch_code = $branch->branch_code;
		$this->deleteForDetail($detail_id);
		$pick_location = OrderXtraConfig::$default_location;
		$sql = "SELECT  d.stock_id
			,sod.id AS detail_id
			, d.quantity AS quantity
			,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held
			,qoh.quantity AS qoh
			,tp.quantity AS total_picked
			,quantity_before
			,order_id
			FROM ".TB_PREF."sales_order_details sod
			NATURAL JOIN ".TB_PREF."sales_orders so
			JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id)
			JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location')
			LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
			WHERE  sod.id = $detail_id
			";
		$result = db_query($sql, $sql);
		while($row=db_fetch($result)) {
			foreach($row as $key => $value) { $$key = $value; }
				$available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 );

			if($available || OrderXtraConfig::$autopick_null)
				insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity);

		}
	}
	function insertAllForBranch($debtor_no, $branch_code) {
		$this->deleteAllForBranch($debtor_no, $branch_code);
		$pick_location = OrderXtraConfig::$default_location;
		$sql = "SELECT  d.stock_id
			,sod.id AS detail_id
			, d.quantity AS quantity
			,IF(".OrderXtraConfig::sql_held_condition().", d.quantity, 0) held
			,qoh.quantity AS qoh
			,tp.quantity AS total_picked
			,quantity_before
			,order_id
			FROM ".TB_PREF."sales_order_details sod
			NATURAL JOIN ".TB_PREF."sales_orders so
			JOIN ".TB_PREF."denorm_order_details_queue d ON (sod.id = d.id)
			JOIN ".TB_PREF."denorm_qoh qoh ON (qoh.stock_id = sod.stk_code AND loc_code = '$pick_location')
			LEFT JOIN (".totalpick_query().") tp ON (tp.stock_id = sod.stk_code)
			WHERE debtor_no = $debtor_no AND branch_code = $branch_code
			";
		$result = db_query($sql, $sql);
		while($row=db_fetch($result)) {
			foreach($row as $key => $value) { $$key = $value; }
				$available = max(min($quantity-$held, $qoh-$quantity_before-$total_picked-$held) ,0 );

			if($available || OrderXtraConfig::$autopick_null)
				insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $available, $quantity);

		}
	}
	function deleteForDetail($detail_id) {
		$sql = "DELETE ".TB_PREF."topick p
			FROM ".TB_PREF."topick p
			JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id)
			NATURAL JOIN ".TB_PREF."sales_orders so
			WHERE  sod.id = $detail_id
			";
		db_query($sql);
	}

	function deleteAllForBranch($debtor_no, $branch_code) {
		$sql = "DELETE ".TB_PREF."topick p
			FROM ".TB_PREF."topick p
			JOIN ".TB_PREF."sales_order_details sod on (sod.id = p.detail_id)
			NATURAL JOIN ".TB_PREF."sales_orders so
			WHERE debtor_no = $debtor_no AND branch_code = $branch_code
			";
		db_query($sql);
	}

	function pickAllDetails() {
		foreach($this->detail_ids as $detail_id) {
			$this->insertForDetail($detail_id);
		}
	}
	function unpickAllDetails() {
		foreach($this->detail_ids as $detail_id) {
			$this->deleteForDetail($detail_id);
		}
	}

}

function pick_query($detail_id=null) {
	$sql =  "SELECT  detail_id, -sum(quantity) as quantity
		FROM ".TB_PREF."topick
		WHERE type IN ('order', 'booked')";
	if($detail_id) $sql .= " AND detail_id = $detail_id";
	else $sql .= " GROUP BY detail_id ";

	return $sql;

}

/* query to get the total picked quantity */
function totalpick_query($stock_id=null) {
	$sql =  "SELECT  sku AS stock_id, -sum(quantity) as quantity
		FROM ".TB_PREF."topick
		WHERE type IN ('order', 'booked')";
	if($stock_id) $sql .= " AND sku = $stock_id";
	else $sql .= " GROUP BY stock_id ";

	return $sql;
}

function insert_pick($sku, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity, $qoh_location=null) {
	if($qoh_location == null) $qoh_location = OrderXtraConfig::$default_location;
	$item_link = "/modules/order_line_extra/item_schedule.php?stock_id=$sku";
	$order_link = "/sales/sales_order_entry.php?OrderNumber=$order_id";
	$base = substr($sku, 0, 8);
	$variation = substr($sku, 9);
	$branch = get_cust_branch($debtor_no, $branch_code);
	$location = $branch['branch_ref'];
	$booked = $quantity - $to_pick;

	$sql = "INSERT INTO ".TB_PREF."topick  SET
		order_id = $order_id,
		order_link = '$order_link',
		location = '$location',
		sku	= '$sku',
		base = '$base',
		variation = '$variation',
		item_link = '$item_link',
		quantity = -{$quantity},
		detail_id = $detail_id,
		type = 'order'";
	db_query($sql, 'There was a problem inserting the picking information.');

	# add booked quantity. Insert them as picked so they won't have to be picked
	$sql = "INSERT INTO ".TB_PREF."topick  SET
		order_id = $order_id,
		order_link = '$order_link',
		location = '$location',
		sku	= '$sku',
		item_link = '$item_link',
		base = '$base',
		variation = '$variation',
		quantity = $booked,
		detail_id = $detail_id,
		type = 'booked'";
	db_query($sql, 'There was a problem inserting the picking information.');

	# add QOH 
	$qoh = get_qoh_on_date($sku, $qoh_location);
	$sql = "INSERT INTO ".TB_PREF."topick  SET
		order_id = $order_id,
		order_link = '$order_link',
		location = '$location',
		sku	= '$sku',
		item_link = '$item_link',
		base = '$base',
		variation = '$variation',
		quantity = $qoh,
		detail_id = $detail_id,
		type = 'stock'";
	db_query($sql, 'There was a problem inserting the picking information.');

}

function delete_pick($detail_id) {
	$sql = "DELETE FROM ".TB_PREF."topick
		WHERE  detail_id = $detail_id
		";
	return db_query($sql);
}

function update_pick($detail_id, $to_pick) {
	// delete existing
	delete_pick($detail_id);

	if($to_pick ==0) return;

	$sql = "SELECT stk_code AS stock_id
		, order_no AS order_id
		, debtor_no
		, branch_code
		, quantity-qty_sent AS quantity
		FROM ".TB_PREF."sales_orders so
		NATURAL JOIN ".TB_PREF."sales_order_details sod
		WHERE sod.id = $detail_id
		";
	$result = db_query($sql);
	$row = db_fetch($result);

	foreach($row as $key => $value) { $$key = $value; }

	insert_pick($stock_id, $order_id, $detail_id, $debtor_no, $branch_code, $to_pick, $quantity);
}

function clean_pickup_for_dispatch($cart) {
	foreach($cart->line_items as $line) {
		delete_pick($line->src_id);
	}
}