<?php
/**********************************************************************
***********************************************************************/



function update_item($stock_id, $description, $long_description, 
                    $category_id, $tax_type_id, $units, 
                    $mb_flag, $sales_account, $inventory_account, 
                    $cogs_account, $coc_account, $cost_account,
                    $adjustment_account, $assembly_account, 
                    $dimension_id, $dimension2_id, 
                    $no_sale, $editable,
                    $is_pos,$is_restaurant,$is_laundry, 
                    $minimum_price, $high_value_item, $sample_item, 
                    $reference_cost, $any_supplier,$parameter1_id, 
                    $country_of_origin, $country_of_manufacture, $local_agent, 
                    $brand, $parameter2_id, $stock_control_method,
                    $search_code, $part_no,
                    $hs_code, $requester_id, $requester_comment)
{
			
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');
        

        $sql = "UPDATE ".TB_PREF."stock_master SET long_description=".db_escape($long_description).",
                description=".db_escape($description).",
                category_id=".db_escape($category_id).",
                sales_account=".db_escape($sales_account).",
                inventory_account=".db_escape($inventory_account).",
                cogs_account=".db_escape($cogs_account).",
                coc_account=".db_escape($coc_account).",
                cost_account=".db_escape($cost_account).",
                adjustment_account=".db_escape($adjustment_account).",
                assembly_account=".db_escape($assembly_account).",
                dimension_id=".db_escape($dimension_id).",
                dimension2_id=".db_escape($dimension2_id).",
                tax_type_id=".db_escape($tax_type_id).",
                no_sale=".db_escape($no_sale).",
                updated_by=".db_escape($id_user).",
                updated_on=".db_escape($datetime).",
                is_pos=".db_escape($is_pos).",
                is_restaurant=".db_escape($is_restaurant).",
                is_laundry=".db_escape($is_laundry).",
                minimum_price=".db_escape($minimum_price).",
                high_value_item=".db_escape($high_value_item).",
                sample_item=".db_escape($sample_item).",
                reference_cost=".db_escape($reference_cost).",
                any_supplier=".db_escape($any_supplier).",    
                parameter1_id=".db_escape($parameter1_id).",
                country_of_origin=".db_escape($country_of_origin).",
                country_of_manuf=".db_escape($country_of_manufacture).",
                local_agent=".db_escape($local_agent).",
                brand=".db_escape($brand).",
                parameter2_id=".db_escape($parameter2_id).",
                stock_control_method=".db_escape($stock_control_method).",  
                search_code=".db_escape($search_code).", 
                part_no=".db_escape($part_no).", 
                editable=".db_escape($editable).",
                hs_code=".db_escape($hs_code).",
                requester_id=".db_escape($requester_id).",
                requester_comment=".db_escape($requester_comment)." ";

        if ($units != '')
                $sql .= ", units=".db_escape($units);

        if ($mb_flag != '')
                $sql .= ", mb_flag=".db_escape($mb_flag);

        $sql .= " WHERE stock_id=".db_escape($stock_id);

        db_query($sql, "The item could not be updated");

        update_item_code(-1, $stock_id, $stock_id, $description, $category_id, 1, 0);
}

function add_item($stock_id, $description, $long_description, 
                    $category_id, $tax_type_id, $units, 
                    $mb_flag, $sales_account, $inventory_account, 
                    $cogs_account, $coc_account, $cost_account, $adjustment_account,	$assembly_account, 
                    $dimension_id, $dimension2_id, 
                    $no_sale, $editable, 
                    $is_pos, $is_restaurant,$is_laundry, 
                    $minimum_price, $high_value_item, $sample_item, 
                    $reference_cost, $any_supplier, $parameter1_id, 
                    $country_of_origin, $country_of_manufacture, $local_agent, 
                    $brand, $parameter2_id, $stock_control_method,
                    $search_code, $part_no, $inactive, 
                    $hs_code, $requester_id, $requester_comment)
{
        $id_user = $_SESSION['wa_current_user']->user;
        $datetime = date('Y-m-d H:i:s');

        $sql = "INSERT INTO ".TB_PREF."stock_master (stock_id, description, long_description, 
                    category_id, tax_type_id, units, 
                    mb_flag, sales_account, inventory_account, 
                    cogs_account, coc_account, cost_account,
                    adjustment_account, assembly_account, 
                    dimension_id, dimension2_id, 
                    no_sale, editable,
                    created_by, created_on,
                    is_pos,is_restaurant,is_laundry, 
                    minimum_price, high_value_item, sample_item, 
                    reference_cost, any_supplier, parameter1_id, 
                    country_of_origin, country_of_manuf, local_agent, 
                    brand, parameter2_id, stock_control_method,
                    search_code, part_no, inactive,
                    hs_code, requester_id, requester_comment)
                VALUES (".db_escape($stock_id).", ".db_escape($description).", ".db_escape($long_description).","
                .db_escape($category_id).", ".db_escape($tax_type_id).", ".db_escape($units).", "
                .db_escape($mb_flag).",".db_escape($sales_account).", ".db_escape($inventory_account).", "
                .db_escape($cogs_account).",".db_escape($coc_account).",".db_escape($cost_account).","
                .db_escape($adjustment_account).", ".db_escape($assembly_account).", "
                .db_escape($dimension_id).", ".db_escape($dimension2_id).","
                .db_escape($no_sale).",".db_escape($editable).","
                .db_escape($id_user).",".db_escape($datetime).","
                .db_escape($is_pos).",".db_escape($is_restaurant).",".db_escape($is_laundry).","
                .db_escape($minimum_price).",".db_escape($high_value_item).",".db_escape($sample_item).","
                .db_escape($reference_cost).",".db_escape($any_supplier).",".db_escape($parameter1_id).","
                .db_escape($country_of_origin).",".db_escape($country_of_manufacture).",".db_escape($local_agent).","
                .db_escape($brand).",".db_escape($parameter2_id).",".db_escape($stock_control_method).","
                .db_escape($search_code).",".db_escape($part_no).",".db_escape($inactive).","
                .db_escape($hs_code).",".db_escape($requester_id).",".db_escape($requester_comment).")";

        db_query($sql, "The item could not be added");

        $sql = "INSERT INTO ".TB_PREF."loc_stock (loc_code, stock_id)
                SELECT ".TB_PREF."locations.loc_code, ".db_escape($stock_id)
                ." FROM ".TB_PREF."locations";

        db_query($sql, "The item locstock could not be added");

        add_item_code($stock_id, $stock_id, $description, $category_id, 1, 0);

}

function delete_item($stock_id)
{
	$sql="DELETE FROM ".TB_PREF."stock_master WHERE stock_id=".db_escape($stock_id);
	db_query($sql, "could not delete stock item");

	/*and cascade deletes in loc_stock */
	$sql ="DELETE FROM ".TB_PREF."loc_stock WHERE stock_id=".db_escape($stock_id);
	db_query($sql, "could not delete stock item loc stock");

	/*and cascade deletes in purch_data */
	$sql ="DELETE FROM ".TB_PREF."purch_data WHERE stock_id=".db_escape($stock_id);
	db_query($sql, "could not delete stock item purch data");

	/*and cascade deletes in prices */
	$sql ="DELETE FROM ".TB_PREF."prices WHERE stock_id=".db_escape($stock_id);
	db_query($sql, "could not delete stock item prices");

	/*and cascade delete the bill of material if any */
	$sql = "DELETE FROM ".TB_PREF."bom WHERE parent=".db_escape($stock_id);
	db_query($sql, "could not delete stock item bom");

	delete_item_kit($stock_id);
}

function get_item($stock_id)
{
	$sql = "SELECT ".TB_PREF."stock_master.*,".TB_PREF."item_tax_types.name AS tax_type_name
		FROM ".TB_PREF."stock_master,".TB_PREF."item_tax_types
		WHERE ".TB_PREF."item_tax_types.id=".TB_PREF."stock_master.tax_type_id
		AND stock_id=".db_escape($stock_id);
	$result = db_query($sql,"an item could not be retreived");

	return db_fetch($result);
}

function get_items()
{
	$sql = "SELECT * FROM ".TB_PREF."stock_master";
	return db_query($sql,"items could not be retreived");
}

function item_in_foreign_codes($stock_id)
{
	$sqls=  array(
	"SELECT COUNT(*) FROM "
		.TB_PREF."stock_moves WHERE stock_id=".db_escape($stock_id) =>
	 _('Cannot delete this item because there are stock movements that refer to this item.'),
	"SELECT COUNT(*) FROM "
		.TB_PREF."bom WHERE component=".db_escape($stock_id)=>
	 _('Cannot delete this item record because there are bills of material that require this part as a component.'),
	"SELECT COUNT(*) FROM "
		.TB_PREF."sales_order_details WHERE stk_code=".db_escape($stock_id) =>
	 _('Cannot delete this item because there are existing purchase order items for it.'),
	"SELECT COUNT(*) FROM "
		.TB_PREF."purch_order_details WHERE item_code=".db_escape($stock_id)=>
	 _('Cannot delete this item because there are existing purchase order items for it.')
	);

	$msg = '';

	foreach($sqls as $sql=>$err) {
		$result = db_query($sql, "could not query stock usage");
		$myrow = db_fetch_row($result);
		if ($myrow[0] > 0) 
		{
			$msg = $err; break;
		}
	}
	if ($msg == '') {	

		$kits = get_where_used($stock_id);
		$num_kits = db_num_rows($kits);
		if ($num_kits) {
			$msg = _("This item cannot be deleted because some code aliases 
				or foreign codes was entered for it, or there are kits defined 
				using this item as component")
				.':<br>';

			while($num_kits--) {
				$kit = db_fetch($kits);
				$msg .= "'".$kit[0]."'";
				if ($num_kits) $msg .= ',';
			}

		}
	}
	return $msg;
}

function update_barcode_change($stock_id)
{
    $updated_on = date('Y-m-d H:i:s');
    
    $sql = "UPDATE ".TB_PREF."stock_master 
            SET barcode_updated_on =".db_escape($updated_on). " 
            WHERE stock_id=".db_escape($stock_id);
    
    db_query($sql,"The item barcode change could not be updated");
}

//function get_item_advance_search_sql($code = "", $category = "", $name ="", $desc = "", $mb_flag = ""){
//    $sql = "SELECT stock_id, description, long_description, units from ".TB_PREF."stock_master";
//    
//    return $sql;
//}

function get_item_advance_search_sql($source, $stock_id, $description, $long_description, $category_id, $mb_flag,
        $item_category, $country_of_origin, $country_of_manufacture, $supplier_id, $local_agent, $brand, 
        $type, $search_code, $part_no, $show_inactive = false)
{
//   $filter = ""; 
   
//    if ($category_id == -1){
//        $filter .= "LEFT JOIN ".TB_PREF."stock_category ON 
//                    sc.category_id = sm.category_id  "; 
//    }
//    
//    if ($category_id != -1) 
//	{
//         $filter .=" ,".TB_PREF."stock_category sc ";
//	} 

    switch ($source) {
        case "MANUFACTURED":
                $sql = "SELECT sm.stock_id, 
                                sm.description, sm.mb_flag,
                                itm_cat.value_name AS item_category_name,
                                sm.search_code,
                                type.value_name AS type_name  ";

            break;
        case "NON_STOCK":
                $sql = "SELECT sm.stock_id, 
                                sm.description, sm.mb_flag,
                                itm_cat.value_name AS item_category_name,
                                sm.search_code,
                                type.value_name AS type_name  ";

            break;
        default:
                $sql = "SELECT sm.stock_id, 
                            sm.description, sm.mb_flag,
                            itm_cat.value_name AS item_category_name,
                            sm.search_code,
                            sm.part_no,
                            brand.value_name AS brand_name ";
            break;
    }
    
            $sql .= " FROM ".TB_PREF."stock_master sm 
                            LEFT JOIN ".TB_PREF."list_values  itm_cat ON 
                                    sm.parameter1_id = itm_cat.id 
                            LEFT JOIN ".TB_PREF."list_values brand ON 
                                    sm.brand = brand.id 
                            LEFT JOIN ".TB_PREF."list_values type ON 
                                    sm.parameter2_id = type.id 
                        WHERE 1 = 1 ";

	if ($stock_id != null) 
	{
                $stock_id = "%".$stock_id."%";
		$sql .= " AND sm.stock_id LIKE ".db_escape($stock_id);
	} 
    
    if ($description != null) 
	{
                $description = "%".$description."%";
		$sql .= " AND sm.description LIKE ".db_escape($description);
	} 
    
    if ($long_description != null) 
	{
		$long_description = "%".$long_description."%";
		$sql .= " AND sm.long_description LIKE ".db_escape($long_description);
	} 
    
    if ($category_id != -1) 
	{
		$category_id = "%".$category_id."%";
		$sql .= " AND sm.category_id = ".db_escape($category_id);
	} 
    
    if ($mb_flag != null) 
	{
		$sql .= " AND sm.mb_flag = ".db_escape($mb_flag);
	} 
    
    if ($item_category != -1) 
	{
		$item_category = "%".$item_category."%";
		$sql .= " AND sm.parameter1_id = ".db_escape($item_category);
	}
    
    if ($country_of_origin != -1) 
	{
		$sql .= " AND sm.country_of_origin = ".db_escape($country_of_origin);
	}
    
    if ($country_of_manufacture != -1) 
	{
		$sql .= " AND sm.country_of_manuf = ".db_escape($country_of_manufacture);
	}
    
    if ($supplier_id != -1) 
	{
		//$sql .= " AND sm.supplier_id = ".db_escape($supplier_id);
	}
    
    if ($local_agent != -1) 
	{
		//$sql .= " AND sm.local_agent = ".db_escape($local_agent);
	}
    
    if ($brand != -1) 
	{
		$sql .= " AND sm.brand = ".db_escape($brand);
	}
    
    if ($type != -1) 
	{
		$sql .= " AND sm.parameter2_id = ".db_escape($type);
	}

    if ($search_code != null) 
	{
		$search_code = "%".$search_code."%";
		$sql .= " AND sm.search_code LIKE ".db_escape($search_code);
	} 
    
    if ($part_no != null) 
	{
		$part_no = "%".$part_no."%";
		$sql .= " AND sm.part_no LIKE ".db_escape($part_no);
	} 
    
    if(!($show_inactive == true)){
        $sql .= " AND sm.inactive = 0  ";
    }
//    $sql .= " AND sm.is_pos = ".db_escape($pos);
//    $sql .= " AND sm.is_restaurant = ".db_escape($resturant);
//    $sql .= " AND sm.is_laundry = ".db_escape($laundry);
	
        $sql .= " ORDER BY sm.stock_id";
        
   	return $sql;
}

function get_items_search($description, $type)
{
	global $SysPrefs;

	$sql = "SELECT COUNT(i.item_code) AS kit, i.item_code, i.description, c.description category
		FROM ".TB_PREF."stock_master s, ".TB_PREF."item_codes i
			LEFT JOIN ".TB_PREF."stock_category c ON i.category_id=c.category_id
		WHERE i.stock_id=s.stock_id
			AND !i.inactive AND !s.inactive
			AND (  i.item_code LIKE " . db_escape("%" . $description. "%") . " OR 
				i.description LIKE " . db_escape("%" . get_post("description"). "%") . " OR 
				c.description LIKE " . db_escape("%" . get_post("description"). "%") . ") ";

	switch ($type) {
		case "sales":
			$sql .= " AND !s.no_sale AND mb_flag <> 'F'";
			break;
		case "manufactured":
			$sql .= " AND mb_flag = 'M'";
			break;
    	case "purchasable":
    		$sql .= " AND NOT no_purchase AND mb_flag <> 'F' AND i.item_code=i.stock_id";
    		break;
		case "costable":
			$sql .= " AND mb_flag <> 'D' AND mb_flag <> 'F' AND  i.item_code=i.stock_id";
			break;
		case "component":
			$parent = $_GET['parent'];
			$sql .= " AND  i.item_code=i.stock_id AND i.stock_id <> '$parent' AND mb_flag <> 'F' ";
			break;
		case "kits":
			$sql .= " AND !i.is_foreign AND i.item_code!=i.stock_id AND mb_flag <> 'F'";
			break;
		case "all":
			$sql .= " AND mb_flag <> 'F' AND i.item_code=i.stock_id";
			break;
	}

	if (isset($SysPrefs->max_rows_in_search))
		$limit = $SysPrefs->max_rows_in_search;
	else
		$limit = 10;

	$sql .= " GROUP BY i.item_code ORDER BY i.description LIMIT 0,".(int)($limit);

	return db_query($sql, "Failed in retreiving item list.");
}

function get_item_code_id($stock_id)
{
	$sql = "SELECT ic.id
		FROM ".TB_PREF."item_codes ic, ".TB_PREF."stock_master sm
		WHERE ic.item_code = ".db_escape($stock_id)." AND ic.item_code = ic.stock_id AND
    	ic.stock_id = sm.stock_id";

	$result = db_query($sql, "could not get item id");
	$row = db_fetch_row($result);

	return is_array($row) ? $row[0] : false;
}

?>