\n"; if( $search ) { $sel = ""; if( $tag == -1 ) { $sel = " selected=\"selected\""; } echo "\n"; $sel = ""; if( $tag == 0 ) { $sel = " selected=\"selected\""; } echo "\n"; } else if( $insert ) { $sel = ""; if( $tag == 0 ) { $sel = " selected=\"selected\""; } echo "\n"; } foreach( $tags as $tag ) { $nr = $tag[0]; $name = htmlentities( $tag[1] ); if( $tag[2] > 0 ) { $name = " - " . $name; } $sel = ""; if( $seltag == $nr ) { $sel = " selected=\"selected\""; } echo "\n"; } echo ""; } function cmd_parts_get_post_data( $neednr=false, $needtag=false ) { $nr = get_POST_var( "nr" ); if( !ereg( "^[1-9][0-9]*\$", $nr ) ) { if( $neednr || $nr != "" ) { print_error( "missing or invalid nr ($nr)" ); return null; } } $tag = get_POST_var( "tag" ); if( !ereg( "^[1-9][0-9]*\$", $tag ) ) { if( $needtag ) { print_error( "missing or invalid tag ($tag)" ); return null; } $tag = 0; } $supplier = get_POST_var( "supplier" ); if( !ereg( "^[1-9][0-9]*\$", $supplier ) ) { print_error( "missing or invalid supplier ($supplier)" ); return null; } $partnr = get_POST_var( "partnr" ); if( !$partnr ) { print_error( "missing partnr ($partnr)" ); return null; } $webpartnr = get_POST_var( "webpartnr" ); $name = get_POST_var( "name" ); if( !$name ) { print_error( "missing name ($name)" ); return null; } $descr = get_POST_var( "descr" ); $price = get_POST_var( "price" ); if( !ereg( "^[0-9][0-9]*\\.[0-9][0-9]\$", $price ) ) { print_error( "missing or invalid price ($price)" ); return null; } $price = str_replace( ".", "", $price ); $pricedrop = get_POST_var( "pricedrop" ); if( !ereg( "^[0-9][0-9]*\$", $pricedrop ) ) { print_error( "missing or invalid pricedrop ($pricedrop)" ); return null; } $pricedate = get_POST_var( "pricedate" ); if( $pricedate != "" && !ereg( "^[0-9][0-9]?\\.[0-9][0-9]\\.20[0-1][0-9]\$", $pricedate ) ) { print_error( "missing or invalid pricedate ($pricedate)" ); return null; } $longdescr = get_POST_var( "longdescr", "", false ); $part = array( "nr" => $nr, "tag" => $tag, "supplier" => $supplier, "partnr" => $partnr, "webpartnr" => $webpartnr, "name" => $name, "descr" => $descr, "longdescr" => $longdescr, "price" => $price, "pricedrop" => $pricedrop, "pricedate" => $pricedate ); return $part; } function cmd_parts_from_db( $nr ) { $sql = "select " . "p.op_nr, " . "p.os_nr, " . "s.os_name, " . "s.os_part_url, " . "p.op_part_nr, " . "p.op_web_part_nr, " . "p.op_name, " . "p.op_descr, " . "p.op_long_descr, " . "p.op_price, " . "p.op_price_drop, " . "to_char( p.op_price_date, 'DD.MM.YYYY' ) as op_price_date " . "from " . "orderpart p, " . "ordersupplier s ". "where " . "p.os_nr = s.os_nr and " . "p.op_nr = $nr"; $ress = db_query( $sql ); if( !$ress ) { return null; } $row = db_next( $ress ); if( !$row ) { return null; } $part = array( "nr" => $row["op_nr"], "supplier" => $row["os_nr"], "suppliername" => $row["os_name"], "parturl" => $row["os_part_url"], "partnr" => $row["op_part_nr"], "webpartnr" => $row["op_web_part_nr"], "name" => $row["op_name"], "descr" => $row["op_descr"], "longdescr" => $row["op_long_descr"], "price" => $row["op_price"], "pricedrop" => $row["op_price_drop"], "pricedate" => $row["op_price_date"] ); return $part; } function cmd_parts_form_vars( $part ) { global $OP_CURRENCIES; $size = 40; // $tags = cmd_parts_read_tags(); $suppliers = db_read_suppliers(); print_tab_select( "supplier", "supplier", $suppliers, $part["supplier"], null, "part_supplier_change()" ); echo "URL"; echo ""; echo ""; echo "\n"; print_tab_input( "part nr", "partnr", $size, $part["partnr"] ); print_tab_input( "web part nr", "webpartnr", $size, $part["webpartnr"] ); print_tab_input( "name", "name", $size, $part["name"] ); print_tab_input( "description", "descr", $size, $part["descr"] ); print_tab_input( "price", "price", $size, make_price_str($part["price"]) ); print_tab_input( "price drops for #", "pricedrop", $size, $part["pricedrop"] ); print_tab_input( "date", "pricedate", $size, $part["pricedate"] ); print_tab_textarea( "long description", "longdescr", 10, $size, $part["longdescr"] ); } function cmd_parts_insert() { global $userid; $part = cmd_parts_get_post_data(); if( !$part ) { return null; } $ret = array( "tag" => $part["tag"], "supplier" => $part["supplier"] ); $nr = db_next_nr( "orderpart", "op_nr" ); db_begin(); $sql = "insert into orderpart ( " . "op_nr, os_nr, op_part_nr, op_web_part_nr, " . "op_name, op_descr, op_long_descr, op_price, " . "op_price_drop, op_price_date, op_del_flag " . ") values ( " . $nr . ", " . $part["supplier"] . ", " . db_escape_str($part["partnr"]) . ", " . db_escape_str($part["webpartnr"]) . ", " . db_escape_str($part["name"]) . ", " . db_escape_str($part["descr"]) . ", " . db_escape_str($part["longdescr"]) . ", " . db_escape_str($part["price"]) . ", " . db_escape_str($part["pricedrop"]) . ", CURRENT_TIMESTAMP, 0 )"; if( db_update( $sql ) <= 0 ) { db_rollback(); return $ret; } if( $part["tag"] ) { $sql = "insert into orderparttag ( op_nr, ot_nr, opt_primary ) values ( $nr, " . $part["tag"] . ", 1 )"; if( db_update( $sql ) <= 0 ) { db_rollback(); return $ret; } } $type = get_POST_var( "type" ); $count = get_POST_var( "count" ); if( ereg( "^[1-3]\$", $type ) && ereg( "^[0-9][0-9]*\$", $count ) ) { $sql = "insert into orderpartuser ( ou_nr, op_nr, oo_nr, opu_type, opu_count ) values ( $userid, $nr, 0, $type, $count )"; db_update( $sql ); } db_commit(); echo "

inserted: " . htmlentities($part["name"]) . " " . $part["descr"] . "

"; return $ret; } function cmd_parts_update() { global $userid; $part = cmd_parts_get_post_data( true, false ); if( !$part ) { return; } $pricedate = $part["pricedate"]; if( $pricedate == "" ) { $pricedate = "CURRENT_TIMESTAMP"; } else { $pricedate = "to_timestamp( '$pricedate 00:00:00', 'DD.MM.YYYY HH24:MI:SS' )"; } $sql = "update orderpart set " . "os_nr = " . $part["supplier"] . ", " . "op_part_nr = " . db_escape_str( $part["partnr"] ) . ", " . "op_web_part_nr = " . db_escape_str( $part["webpartnr"] ) . ", " . "op_name = " . db_escape_str( $part["name"] ) . ", " . "op_descr = " . db_escape_str( $part["descr"] ) . ", " . "op_long_descr = " . db_escape_str( $part["longdescr"] ) . ", " . "op_price = " . $part["price"] . ", " . "op_price_drop = " . $part["pricedrop"] . ", " . "op_price_date = $pricedate " . "where op_nr = " . $part["nr"]; db_update( $sql ); } function cmd_parts_addtag() { $nr = get_POST_var( "nr" ); $tag = get_POST_var( "tag" ); $primary = get_POST_var( "primary" ); if( !ereg( "^[0-9][0-9]*\$", $nr ) || !ereg( "^[0-9][0-9]*\$", $tag ) ) { print_error( "invalid or missing nr ($nr) or tag ($tag)" ); return; } if( $primary != "1" ) { $primary = 0; } db_begin(); $sql = "insert into orderparttag ( op_nr, ot_nr, opt_primary ) values ( $nr, $tag, $primary )"; if( db_update( $sql ) <= 0 ) { print_error( "insert failed" ); db_rollback(); return; } if( $primary ) { $sql = "update orderparttag set opt_primary = 0 where op_nr = $nr and ot_nr != $tag"; if( db_update( $sql ) < 0 ) { print_error( "update failed" ); db_rollback(); return; } } db_commit(); } function cmd_parts_deltag() { $nr = get_POST_var( "nr" ); $tag = get_POST_var( "tag" ); if( !ereg( "^[0-9][0-9]*\$", $nr ) || !ereg( "^[0-9][0-9]*\$", $tag ) ) { return; } db_begin(); $sql = "delete from orderparttag where op_nr = $nr and ot_nr = $tag"; if( db_update( $sql ) <= 0 ) { db_rollback(); return; } $sql = "select min(ot_nr) as ot_nr, count(*) as cnt, sum(opt_primary) as primsum from orderparttag where op_nr = $nr"; $ress = db_query( $sql ); if( !$ress ) { db_rollback(); return; } $row = db_next( $ress ); if( !$row ) { db_rollback(); return; } if( $row["cnt"] > 0 && $row["primsum"] == 0 ) { $tag = $row["ot_nr"]; $sql = "update orderparttag set opt_primary = 1 where op_nr = $nr and ot_nr = $tag"; if( db_update( $sql ) <= 0 ) { db_rollback(); return; } } db_commit(); } function cmd_parts_primtag() { $nr = get_POST_var( "nr" ); $tag = get_POST_var( "tag" ); if( !ereg( "^[0-9][0-9]*\$", $nr ) || !ereg( "^[0-9][0-9]*\$", $tag ) ) { return; } db_begin(); $sql = "update orderparttag set opt_primary = 1 where op_nr = $nr and ot_nr = $tag"; if( !db_update( $sql ) ) { db_rollback(); return; } $sql = "update orderparttag set opt_primary = 0 where op_nr = $nr and ot_nr != $tag"; if( !db_update( $sql ) ) { db_rollback(); return; } db_commit(); } function cmd_parts_new( $part=null ) { global $OP_CURRENCIES, $OPU_TYPES; if( !$part ) { $part = array(); } echo "\n"; echo "\n"; } function cmd_parts_edit() { global $OP_CURRENCIES, $OPU_TYPES; $nr = get_FORM_var( "nr" ); if( !ereg( "^[1-9][0-9]*\$", $nr ) ) { cmd_parts_searchform(); return; } $part = cmd_parts_from_db( $nr ); echo "\n"; echo "\n"; echo "\n"; echo "\n"; cmd_parts_form_vars( $part ); print_tab_input( "", null, null, "update", "submit" ); echo "\n"; echo "\n"; echo "\n"; echo "\n"; $sql = "select t.ot_nr, t.ot_long_name, pt.opt_primary from ordertag t, orderparttag pt where t.ot_nr = pt.ot_nr and pt.op_nr = $nr order by pt.opt_primary desc, t.ot_long_name"; $ress = db_query( $sql ); $tagcount = 0; if( $ress ) { $row = db_next( $ress ); while( $row ) { $tagcount++; $tag = $row["ot_nr"]; $name = htmlentities($row["ot_long_name"]); $primary = $row["opt_primary"]; echo "\n"; $row = db_next( $ress ); } } echo ""; echo ""; echo ""; echo ""; echo "\n"; echo "\n"; $sql = "select " . "u.ou_nr, u.ou_name, " . "pu.opu_type, pu.opu_count " . "from " . "orderuser u " . "left join orderpartuser pu on " . "u.ou_nr = pu.ou_nr and " . "pu.op_nr = $nr and " . "pu.oo_nr = 0" . "order by u.ou_name"; $ress = db_query( $sql ); if( $ress ) { $row = db_next( $ress ); while( $row ) { $user = $row["ou_nr"]; $name = htmlentities($row["ou_name"]); $type = $row["opu_type"]; $count = $row["opu_count"]; if( !$type ) { $type = "0"; $count = "0"; } echo ""; $row = db_next( $ress ); } } echo "

tags:

$name

"; echo "

"; echo ""; if( $primary == 0 ) { echo "

"; echo ""; } echo "

"; echo "

"; $tags = cmd_parts_read_tags(); cmd_parts_option_tags( "tag", $tags ); echo "

"; if( $tagcount == 0 ) { echo "primary "; } else { echo "primary "; } echo ""; echo "

users:

$name

"; echo "

\n"; } function cmd_parts_searchform() { global $OPU_TYPES_S; echo "\n"; } function cmd_parts_search() { global $OPU_TYPES_S; $tag = get_GET_var( "tag", "0" ); $supplier = get_GET_var( "supplier", "0" ); $user = get_GET_var( "user", "0" ); if( !ereg( "^([0-9][0-9]*|-1)\$", $tag ) || !ereg( "^[0-9][0-9]*\$", $supplier ) || !ereg( "^([0-9][0-9]*|-1)\$", $user ) ) { cmd_parts_searchform(); return; } $partnr = get_GET_var( "partnr" ); $types = ""; foreach( $OPU_TYPES_S as $type => $text ) { if( get_GET_var( "type$type" ) == "1" ) { if( $types != "" ) { $types .= "," . $type; } else { $types = $type; } } } $sql = "select distinct " . "t.ot_long_name, " . "p.op_nr, " . "p.os_nr, " . "s.os_name, " . "s.os_part_url, " . "p.op_part_nr, " . "p.op_web_part_nr, " . "p.op_name, " . "p.op_descr, " . "p.op_price, " . "s.os_currency " . "from " . "orderpart p " . "left join orderparttag pt on " . "p.op_nr = pt.op_nr and " . "pt.opt_primary = 1 " . "left join ordertag t on " . "pt.ot_nr = t.ot_nr, " . "ordersupplier s ". "where " . "p.os_nr = s.os_nr and " . "p.op_del_flag = 0"; if( $tag > 0 ) { $sql .= " and exists ( select spt.op_nr from orderparttag spt, ordertag sot where p.op_nr = spt.op_nr and spt.ot_nr = sot.ot_nr and ( sot.ot_nr = $tag or sot.ot_parent_nr = $tag ) )"; } else if( $tag < 0 ) { $sql .= " and not exists ( select spt.op_nr from orderparttag spt where p.op_nr = spt.op_nr )"; } if( $supplier > 0 ) { $sql .= " and p.os_nr = $supplier"; } if( $partnr != "" ) { $partnrlike = db_escape_str_like($partnr); $sql .= " and ( p.op_part_nr like " . $partnrlike . " or " . "p.op_web_part_nr like " . $partnrlike . " )"; } if( $user > 0 ) { if( $types == "" ) { $sql .= " and exists ( select spu.op_nr from orderpartuser spu where p.op_nr = spu.op_nr and spu.ou_nr = $user and spu.oo_nr = 0 )"; } else { $sql .= " and exists ( select spu.op_nr from orderpartuser spu where p.op_nr = spu.op_nr and spu.ou_nr = $user and spu.opu_type in ( $types ) )"; } } else if( $user < 0 ) { $sql .= " and not exists ( select spu.op_nr from orderpartuser spu where p.op_nr = spu.op_nr and spu.oo_nr = 0 )"; } if( $user == 0 && $types != "" ) { $sql .= " and exists ( select spu.op_nr from orderpartuser spu where p.op_nr = spu.op_nr and spu.opu_type in ( $types ) )"; } $sql .= " order by t.ot_long_name, p.op_name"; $ress = db_query( $sql ); if( $ress ) { $row = db_next( $ress ); if( $row ) { echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; $prevlongname = ""; while( $row ) { $longname = $row["ot_long_name"]; if( $prevlongname != $longname ) { $prevlongname = $longname; echo "\n"; } $nr = $row["op_nr"]; $supname = htmlentities( $row["os_name"] ); $partnr = htmlentities( $row["op_part_nr"] ); $name = htmlentities( $row["op_name"] ); $descr = htmlentities( $row["op_descr"] ); $price = make_price_str( $row["op_price"] ); $currency = htmlentities( $row["os_currency"] ); $supurl = cmd_parts_make_sup_url( $row["os_part_url"], $row["op_part_nr"], $row["op_web_part_nr"] ); echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; $row = db_next( $ress ); } echo "

supplier	part nr	name	description	price
" . htmlentities($longname) . "
$supname	$partnr	$name	$descr	$price $currency	edit

\n"; } else { echo "

no data found

\n"; cmd_parts_searchform(); } } else { echo "

SQL error '" . htmlentities($sql) . "' :(

\n"; } } function cmd_parts_detail() { global $OPU_TYPES; $nr = get_FORM_var( "nr" ); if( !ereg( "^[1-9][0-9]*\$", $nr ) ) { cmd_parts_searchform(); return; } $part = cmd_parts_from_db( $nr ); if( !$part ) { cmd_parts_searchform(); return; } $supurl = cmd_parts_make_sup_url( $part["parturl"], $part["partnr"], $part["webpartnr"] ); echo "

" . htmlentities($part["name"]) . "

\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; echo "\n"; $sql = "select " . "u.ou_nr, " . "u.ou_name, " . "pu.opu_type, " . "pu.opu_count " . "from " . "orderuser u, " . "orderpartuser pu " . "where " . "u.ou_nr = pu.ou_nr and " . "u.ou_del_flag = 0 and " . "pu.oo_nr = 0 and " . "pu.op_nr = $nr"; $mypartuser = array( "type" => 0, "count" => 0 ); $ress = db_query( $sql ); if( $ress ) { $row = db_next( $ress ); while( $row ) { if( $row["ou_nr"] == $GLOBALS["userid"] ) { $mypartuser["type"] = $row["opu_type"]; $mypartuser["count"] = $row["opu_count"]; } else { $name = htmlentities($row["ou_name"]); $type = $OPU_TYPES[$row["opu_type"]]; $count = $row["opu_count"]; echo "\n"; } $row = db_next( $ress ); } } echo "\n"; echo "

supplier	" . htmlentities($part["suppliername"]) . "
url	$supurl
part nr	" . htmlentities($part["partnr"]) . "
web part nr	" . htmlentities($part["webpartnr"]) . "
price	" . make_price_str($part["price"]) . "
price drops at #	" . $part["pricedrop"] . "
price date	" . $part["pricedate"] . "
description	" . htmlentities($part["descr"]) . "
long description	" . str_replace("\n"," ",htmlentities($part["longdescr"])) . "
tags	"; $sql = "select ot_long_name from ordertag t, orderparttag pt where t.ot_nr = pt.ot_nr and pt.op_nr = $nr order by pt.opt_primary desc, t.ot_long_name"; $ress = db_query( $sql ); if( $ress ) { $row = db_next( $ress ); $pfx = ""; while( $row ) { echo $pfx . htmlentities($row["ot_long_name"]); $pfx = " "; $row = db_next( $ress ); } } echo "
$name	$type $count
" . $GLOBALS["username"] . "	"; echo " "; echo ""; echo ""; print_select( "type", $OPU_TYPES, $mypartuser["type"] ); echo " "; echo " "; echo "

\n"; echo "edit part\n"; } function cmd_parts_partuser( $user ) { $nr = get_POST_var( "nr" ); $prevtype = get_POST_var( "prevtype" ); $type = get_POST_var( "type" ); $count = get_POST_var( "count" ); if( !ereg( "^[1-9][0-9]*\$", $nr ) || !ereg( "^[0-3]\$", $prevtype ) || !ereg( "^[0-3]\$", $type ) || !ereg( "^[0-9][0-9]*\$", $count ) ) { return; } $sql = null; if( $prevtype == 0 && $type > 0 ) { $sql = "insert into orderpartuser ( ou_nr, op_nr, oo_nr, opu_type, opu_count ) values ( $user, $nr, 0, $type, $count )"; } else if( $prevtype > 0 && $type > 0 ) { $sql = "update orderpartuser set opu_type = $type, opu_count = $count where ou_nr = $user and op_nr = $nr and oo_nr = 0"; } else if( $prevtype > 0 && $type == 0 ) { $sql = "delete from orderpartuser where ou_nr = $user and op_nr = $nr and oo_nr = 0"; } else { return; } db_update( $sql ); } function cmd_parts_partother() { $user = get_POST_var( "user" ); if( !ereg( "^[1-9][0-9]*\$", $user ) ) { print_error( "missing or invalid user ($user)" ); return; } cmd_parts_partuser( $user ); } function cmd_parts( $subcmd ) { global $userid; echo "

parts

\n"; switch( $subcmd ) { /* case "delete": cmd_parts_delete(); cmd_parts_list(); break; */ case "insert": $part = cmd_parts_insert(); cmd_parts_new( $part ); break; case "update": cmd_parts_update(); cmd_parts_edit(); break; case "addtag": cmd_parts_addtag(); cmd_parts_edit(); break; case "deltag": cmd_parts_deltag(); cmd_parts_edit(); break; case "primtag": cmd_parts_primtag(); cmd_parts_edit(); break; case "partother": cmd_parts_partother(); cmd_parts_edit(); break; case "new": cmd_parts_new(); break; case "edit": cmd_parts_edit(); break; case "search": cmd_parts_search(); break; case "detail": cmd_parts_detail(); break; case "partuser": cmd_parts_partuser( $userid ); cmd_parts_detail(); break; default: cmd_parts_searchform(); break; } } ?>

tag	\n"; $tags = cmd_parts_read_tags(); cmd_parts_option_tags( "tag", $tags, false, true, $part["tag"] ); echo "
" . $GLOBALS["username"] . "	"; print_select( "type", $OPU_TYPES, $mypartuser["type"] ); echo " "; echo "

tag	\n"; $tags = cmd_parts_read_tags(); cmd_parts_option_tags( "tag", $tags, true ); echo "
supplier	\n"; $suppliers = db_read_suppliers(); print_select( "supplier", $suppliers, null, array( 0 => "all" ) ); echo "
user	\n"; $users = db_read_users(); print_select( "user", $users, 0, array( -1 => "none", 0 => "all" ) ); echo "
type	\n"; foreach( $OPU_TYPES_S as $type => $text ) { echo "$text "; } echo "