PageRenderTime 38ms CodeModel.GetById 4ms RepoModel.GetById 0ms app.codeStats 1ms

/lib.php

http://part-db.googlecode.com/
PHP | 1817 lines | 1261 code | 197 blank | 359 comment | 84 complexity | a32d5b6df5035e1247376f3c1fafa492 MD5 | raw file
Possible License(s): GPL-2.0

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /*
  3. part-db version 0.1
  4. Copyright (C) 2005 Christoph Lechner
  5. http://www.cl-projects.de/
  6. This program is free software; you can redistribute it and/or
  7. modify it under the terms of the GNU General Public License
  8. as published by the Free Software Foundation; either version 2
  9. of the License, or (at your option) any later version.
  10. This program is distributed in the hope that it will be useful,
  11. but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  13. GNU General Public License for more details.
  14. You should have received a copy of the GNU General Public License
  15. along with this program; if not, write to the Free Software
  16. Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA
  17. $Id: lib.php 421 2012-04-30 17:04:03Z bubbles.red@gmail.com $
  18. this file contain the following functions:
  19. debug_print
  20. partdb_init
  21. smart_escape
  22. smart_escape_for_search
  23. smart_unescape
  24. lookup_device_name
  25. show_bt
  26. is_odd
  27. print_table_image
  28. print_table_row
  29. GetFormatStrings
  30. PrintsFormats
  31. GenerateBOMHeadline
  32. GenerateBOMResult
  33. print_http_charset
  34. get_svn_revision
  35. footprint_build_tree
  36. footprint_add
  37. footprint_del
  38. footprint_rename
  39. footprint_find_child_nodes
  40. footprint_new_parent
  41. footprint_count
  42. footprint_select
  43. footprint_get_id
  44. footprint_exists
  45. footprint_picture_exists
  46. supplier_add
  47. supplier_delete
  48. supplier_rename
  49. suppliers_build_list
  50. suppliers_count
  51. supplier_select
  52. supplier_get_id
  53. supplier_exists
  54. location_tree_build
  55. location_add
  56. location_delete
  57. location_rename
  58. location_find_child_nodes
  59. location_new_parent
  60. location_mark_as_full
  61. location_count
  62. location_select
  63. location_get_id
  64. location_get_name
  65. location_exists
  66. categories_build_tree
  67. categories_build_navtree
  68. category_add
  69. category_del
  70. category_rename
  71. categories_find_child_nodes
  72. categories_or_child_nodes
  73. category_find_child_nodes
  74. category_new_parent
  75. categories_count
  76. category_select
  77. category_get_id
  78. category_get_name
  79. category_exists
  80. part_add
  81. part_update
  82. part_delete
  83. parts_stock_increase
  84. parts_stock_decrease
  85. parts_count
  86. parts_count_sum_value
  87. parts_count_sum_instock
  88. parts_count_with_prices
  89. parts_count_on_category
  90. parts_count_on_storeloc
  91. parts_select
  92. parts_select_category
  93. parts_select_without_price
  94. parts_select_obsolete
  95. parts_select_search
  96. parts_select_order
  97. parts_select_footprint
  98. parts_order_sum
  99. part_get_name
  100. part_get_category_id
  101. picture_add
  102. picture_delete
  103. pictures_select
  104. picture_set_default
  105. picture_exists
  106. device_build_tree
  107. device_add
  108. device_delete
  109. device_rename
  110. device_find_child_nodes
  111. device_new_parent
  112. devices_count
  113. device_select
  114. devices_select
  115. price_add
  116. price_delete
  117. price_select
  118. datasheet_add
  119. datasheet_delete
  120. datasheet_select
  121. */
  122. /*
  123. * debug_print is used for printing the SQL queries
  124. * before submitting the queries to the DB. The
  125. * $partdb_debug var is used to turn debugging off
  126. * during normal "bug-free" usage.
  127. */
  128. function debug_print( $t)
  129. {
  130. $partdb_debug = 0;
  131. if ($partdb_debug == 1)
  132. print $t;
  133. }
  134. /*@@@ some helper functions down below @@@*/
  135. function partdb_init()
  136. {
  137. include( "config.php");
  138. /* Enter your MySQL username and password in config.php */
  139. $link = mysql_connect( $mysql_server, $db_user, $db_password);
  140. if ( $link)
  141. mysql_select_db( $database);
  142. else
  143. {
  144. echo "connect to DB failed: ", mysql_errno(), "<br>", mysql_error(), "<br>";
  145. }
  146. }
  147. /* stolen from the PHP docs */
  148. function smart_escape( $value)
  149. {
  150. // quote it
  151. $value = "'". mysql_real_escape_string( $value) ."'";
  152. return( $value);
  153. }
  154. /* the same like smart_escape, but
  155. * replaces '*' with '%' for SQL queries and
  156. * add an '%' before and after the search string
  157. */
  158. function smart_escape_for_search( $value)
  159. {
  160. $value = str_replace( '*', '%', $value);
  161. $value = "'%". mysql_real_escape_string( $value) ."%'";
  162. return( $value);
  163. }
  164. /* at the moment this function is _very_ smart :) */
  165. function smart_unescape( $value)
  166. {
  167. return stripslashes( $value);
  168. }
  169. function lookup_device_name ($id)
  170. {
  171. $query = "SELECT name FROM devices WHERE id=". smart_escape($id) .";";
  172. debug_print($query);
  173. $r = mysql_query ($query);
  174. $d = mysql_fetch_row ($r);
  175. return (smart_unescape($d[0]));
  176. }
  177. /*
  178. * This function is very special. The $visited_category_ids array
  179. * holds the ids of all categories we've been to. This is used to
  180. * avoid infinite recursion. Nevertheless, error handling if recursion
  181. * happens is still missing.
  182. * Afterwards a backtrace is created, i.e. the branch of the category
  183. * tree the part is in.
  184. */
  185. function show_bt($cat_id)
  186. {
  187. $bt = "";
  188. $cntr = 0;
  189. $visited_category_ids = array();
  190. $visited_category_ids[$cntr] = $cat_id;
  191. while ($visited_category_ids[$cntr])
  192. {
  193. $w = "(1 ";
  194. for ($i = 0; $i < $cntr; $i++)
  195. $w = $w . "AND (id!='".$visited_category_ids[$i]."') ";
  196. $w .= ')';
  197. $query = "SELECT parentnode FROM categories WHERE (id='".$visited_category_ids[$cntr]."') AND ".$w.";";
  198. debug_print ($query."<br>");
  199. $result = mysql_query($query);
  200. if ( mysql_num_rows( $result) == 0)
  201. {
  202. return( "Error");
  203. }
  204. $d = mysql_fetch_row( $result);
  205. $cntr++;
  206. $visited_category_ids[$cntr] = $d[0];
  207. }
  208. /* We've been to all parent categories, so now build up a
  209. * string of those categories' names seperated by colons.
  210. */
  211. for ($i = $cntr-1; $i > 0; $i--)
  212. $bt .= "&quot;<b>".category_get_name($visited_category_ids[$i])."</b>&quot; : ";
  213. $bt .= "&quot;<b>".category_get_name($visited_category_ids[0])."</b>&quot;";
  214. return ($bt);
  215. }
  216. /*
  217. * check if a given number is odd
  218. */
  219. function is_odd( $number)
  220. {
  221. //return $number & 1; // 0 = even, 1 = odd
  222. return( $number & 1) ? true : false; // false = even, true = odd
  223. }
  224. /*
  225. * deliver in image for table view
  226. * check the following conditions:
  227. * 1. use imgae (if exists)
  228. * 2. use footprint (if exists)
  229. * 3. give dummy image
  230. */
  231. function print_table_image( $id, $name, $footprint)
  232. {
  233. if ( picture_exists( $id))
  234. {
  235. $link = "getimage.php?pid=". smart_unescape( $id);
  236. print "<a href=\"javascript:popUp('". $link ."')\">".
  237. "<img class=\"hoverpic\" src=\"". $link . "\" alt=\"". smart_unescape( $name) ."\">".
  238. "</a>";
  239. }
  240. else
  241. {
  242. $link = footprint_picture_exists( smart_unescape( $footprint));
  243. if ( $link)
  244. {
  245. // footprint
  246. print "<a href=\"javascript:popUp('". $link ."')\">".
  247. "<img class=\"hoverpic\" src=\"". $link ."\" alt=\"\">".
  248. "</a>";
  249. }
  250. else
  251. {
  252. // dummy
  253. print '<img src="img/partdb/dummytn.png" alt="">';
  254. }
  255. }
  256. } // end function
  257. /*
  258. This function prints one complete table line.
  259. Its used from showparts.php and search.php
  260. $data is an associate array, following entrys are used:
  261. 'id'
  262. 'name'
  263. 'footprint'
  264. 'supplierpartnr'
  265. 'comment'
  266. 'instock'
  267. 'mininstock'
  268. 'location'
  269. */
  270. function print_table_row( $row_odd, $data)
  271. {
  272. // the alternating background colors are created here
  273. print "<tr class=\"".( $row_odd ? 'trlist_odd': 'trlist_even')."\">". PHP_EOL;
  274. // Pictures
  275. print "<td class=\"tdrow0\">";
  276. print_table_image( $data['id'], $data['name'], $data['footprint']);
  277. print "</td>\n";
  278. // comment
  279. print "<td class=\"tdrow1". ( $data['obsolete'] ? ' backred' : '') ."\"><a title=\"";
  280. print $data['obsolete'] ? "nicht mehr erh&auml;tlich ". PHP_EOL : "";
  281. print $data['comment'] ? "Kommentar: ". htmlspecialchars( smart_unescape( $data['comment'])) : "(kein Kommentar)";
  282. print "\" href=\"javascript:popUp('partinfo.php?pid=". smart_unescape( $data['id']) ."');\">". smart_unescape( $data['name']) ."</a></td>\n";
  283. // instock/ mininstock
  284. print "<td class=\"tdrow2\">". smart_unescape( $data['instock']) ."/". smart_unescape( $data['mininstock']) ."</td>\n";
  285. // footprint
  286. print "<td class=\"tdrow3\">". smart_unescape( $data['footprint']) ."</td>\n";
  287. // store location
  288. print "<td class=\"tdrow4\">". smart_unescape( $data['location']) . "</td>\n";
  289. // id
  290. print "<td class=\"tdrow4 idclass\">". smart_unescape( $data['id']) . "</td>\n";
  291. // datasheet links
  292. print "<td class=\"tdrow5\">";
  293. // with icons
  294. print "<a title=\"alldatasheet.com\" href=\"http://www.alldatasheet.com/view.jsp?Searchword=". urlencode( smart_unescape( $data['name'])) ."\" target=\"_blank\">".
  295. "<img class=\"companypic\" src=\"img/partdb/ads.png\" alt=\"logo\">".
  296. "</a>\n";
  297. $searchfor = ( strlen( $data['supplierpartnr']) > 0) ? $data['supplierpartnr'] : $data['name'];
  298. print "<a title=\"Reichelt.de\" href=\"http://www.reichelt.de/?ACTION=4;START=0;SHOW=1;SEARCH=". urlencode( smart_unescape( $searchfor)) ."\" target=\"_blank\">".
  299. "<img class=\"companypic\" src=\"img/partdb/reichelt.png\" alt=\"logo\">".
  300. "</a>\n";
  301. // without icons
  302. print "<a href=\"http://search.datasheetcatalog.net/key/". urlencode( smart_unescape( $data['name'])) ."\" target=\"_blank\">DC </a>\n";
  303. // show local datasheet if availible
  304. $ds_data = mysql_fetch_assoc( datasheet_select( $data['id']));
  305. if ( ! empty( $ds_data['datasheeturl']) )
  306. {
  307. print "<a href=\"". smart_unescape( $ds_data['datasheeturl']) ."\">Datenblatt</a>\n";
  308. }
  309. print "</td>\n";
  310. //build the "-" button, only if more then 0 parts on stock
  311. print "<td class=\"tdrow6\"><form action=\"\" method=\"post\">";
  312. print "<input type=\"hidden\" name=\"pid\" value=\"".smart_unescape( $data['id'])."\"/>";
  313. print "<input type=\"hidden\" name=\"action\" value=\"dec\"/>";
  314. print "<input type=\"submit\" value=\"-\"";
  315. if ( $data['instock'] <= 0)
  316. {
  317. print " disabled=\"disabled\" ";
  318. }
  319. print "/></form></td>\n";
  320. //build the "+" button
  321. print "<td class=\"tdrow7\"><form action=\"\" method=\"post\">";
  322. print "<input type=\"hidden\" name=\"pid\" value=\"".smart_unescape( $data['id'])."\"/>";
  323. print "<input type=\"hidden\" name=\"action\" value=\"inc\"/>";
  324. print "<input type=\"submit\" value=\"+\"/></form></td>\n";
  325. print "</tr>\n";
  326. }
  327. /*
  328. * no comment
  329. */
  330. function GetFormatStrings()
  331. {
  332. $aRetVal = array("CSV","CSV Reichelt","CSV Farnell");
  333. return $aRetVal;
  334. }
  335. /*
  336. * no comment
  337. */
  338. function PrintsFormats($Request)
  339. {
  340. $Formats = GetFormatStrings();
  341. $NrOfFormats = count($Formats);
  342. for ($i = 0; $i < $NrOfFormats; $i++)
  343. {
  344. if (($i==0 && isset($_REQUEST[$Request])==0 ) ||
  345. (isset($_REQUEST[$Request]) && $i == $_REQUEST[$Request]))
  346. print "<option selected value=\"".smart_unescape($i)."\">".$Formats[$i]."</option>";
  347. else
  348. print "<option value=\"".smart_unescape($i)."\">".$Formats[$i]."</option>";
  349. }
  350. }
  351. /*
  352. * no comment
  353. */
  354. function GenerateBOMHeadline($Format,$Spacer)
  355. {
  356. if( $Format == 0 ) //CSV
  357. {
  358. $strRetVal = "\r\n";
  359. $strRetVal = $strRetVal."Name:";
  360. $strRetVal = $strRetVal.smart_unescape($Spacer);
  361. $strRetVal = $strRetVal."Anzahl:";
  362. $strRetVal = $strRetVal.smart_unescape($Spacer);
  363. $strRetVal = $strRetVal."Lieferant:";
  364. $strRetVal = $strRetVal.smart_unescape($Spacer);
  365. $strRetVal = $strRetVal."Bestellnummer:";
  366. $strRetVal = $strRetVal.smart_unescape($Spacer);
  367. $strRetVal = $strRetVal."Preis:";
  368. $strRetVal = $strRetVal.smart_unescape($Spacer);
  369. $strRetVal = $strRetVal."Lagernd:";
  370. $strRetVal = $strRetVal.smart_unescape($Spacer);
  371. $strRetVal = $strRetVal."\r\n";
  372. return $strRetVal;
  373. }
  374. else if( $Format == 1 ) //CSV Reichelt
  375. {
  376. return "\r\nBestellnummer:;Anzahl:\r\n";
  377. }
  378. else if( $Format == 2 ) //CSV Farnell
  379. {
  380. return "\r\nBestellnummer:,Anzahl:\r\n";
  381. }
  382. else
  383. {
  384. return "Unbekanntes export Format.";
  385. }
  386. }
  387. /*
  388. * no comment
  389. */
  390. function GenerateBOMResult($Format,$Spacer,$PartName,$SupNr,$SupName,$Quantity,$Instock,$Price)
  391. {
  392. if( $Format == 0 ) //CSV
  393. {
  394. $strRetVal = "\r\n";
  395. $strRetVal = $strRetVal.smart_unescape($PartName);
  396. $strRetVal = $strRetVal.smart_unescape($Spacer);
  397. $strRetVal = $strRetVal.smart_unescape($Quantity);
  398. $strRetVal = $strRetVal.smart_unescape($Spacer);
  399. $strRetVal = $strRetVal.smart_unescape($SupName);
  400. $strRetVal = $strRetVal.smart_unescape($Spacer);
  401. $strRetVal = $strRetVal.smart_unescape($SupNr);
  402. $strRetVal = $strRetVal.smart_unescape($Spacer);
  403. $strRetVal = $strRetVal.smart_unescape($Price);
  404. $strRetVal = $strRetVal.smart_unescape($Spacer);
  405. $strRetVal = $strRetVal.smart_unescape($Instock);
  406. return $strRetVal;
  407. }
  408. else if( $Format == 1 ) //CSV Reichelt
  409. {
  410. return "\r\n".smart_unescape($SupNr).";".smart_unescape($Quantity);
  411. }
  412. else if( $Format == 2 ) //CSV Farnell
  413. {
  414. return "\r\n".smart_unescape($SupNr).",".smart_unescape($Quantity);
  415. }
  416. else
  417. {
  418. return "";
  419. }
  420. }
  421. /*
  422. * generate http header line, use
  423. * charset defined in config
  424. */
  425. function print_http_charset()
  426. {
  427. require( 'config.php');
  428. if ( strlen( $http_charset) > 0 )
  429. {
  430. print "<meta http-equiv=\"content-type\" content=\"text/html; charset=". $http_charset ."\">\n";
  431. }
  432. }
  433. function get_svn_revision()
  434. {
  435. $repo_version = shell_exec( 'svnversion');
  436. return( $repo_version);
  437. }
  438. /* ***************************************************
  439. * footprint querys
  440. */
  441. function footprint_build_tree( $id = 0, $level = 0, $select = -1)
  442. {
  443. $query = "SELECT id, name FROM footprints".
  444. " WHERE parentnode=". smart_escape( $id).
  445. " ORDER BY name ASC;";
  446. $result = mysql_query( $query) or die( mysql_error());
  447. while ( $data = mysql_fetch_assoc( $result))
  448. {
  449. $selected = ($select == $data['id']) ? 'selected': '';
  450. print "<option ". $selected ." value=\"". smart_unescape( $data['id']) . "\">";
  451. for ( $i = 0; $i < $level; $i++)
  452. print "&nbsp;&nbsp;&nbsp;";
  453. print smart_unescape( $data['name']).
  454. "</option>\n";
  455. // do the same for the next level.
  456. footprint_build_tree( $data['id'], $level + 1, $select);
  457. }
  458. }
  459. function footprint_add( $new, $parent_node = 0)
  460. {
  461. $query = "INSERT INTO footprints (name, parentnode) VALUES (".
  462. smart_escape( $new) .",".
  463. smart_escape( $parent_node) .");";
  464. mysql_query( $query) or die( mysql_error());
  465. return( mysql_insert_id());
  466. }
  467. function footprint_del( $old)
  468. {
  469. // TODO: lock database
  470. // catch actual parent node
  471. $query = "SELECT parentnode FROM footprints".
  472. " WHERE id=". smart_escape( $old) .";";
  473. $result = mysql_query( $query) or die( mysql_error());
  474. $data = mysql_fetch_assoc( $result);
  475. $parent = $data['parentnode'];
  476. // delete footprint
  477. $query = "DELETE FROM footprints".
  478. " WHERE id=". smart_escape( $old) ." LIMIT 1;";
  479. mysql_query( $query) or die( mysql_error());
  480. // resort all child footprints to parent node
  481. if ( ! is_null( $parent))
  482. {
  483. $query = "UPDATE footprints SET parentnode=". $parent ." WHERE parentnode=". smart_escape( $old) ." ;";
  484. mysql_query( $query) or die( mysql_error());
  485. }
  486. }
  487. function footprint_rename( $id, $new_name)
  488. {
  489. $query = "UPDATE footprints SET name=". smart_escape( $new_name) ." WHERE id=". smart_escape( $id) ." LIMIT 1";
  490. mysql_query( $query) or die( mysql_error());
  491. }
  492. /*
  493. * find all nodes below and given node
  494. */
  495. function footprint_find_child_nodes( $id)
  496. {
  497. $ret_val = array();
  498. $query = "SELECT id FROM footprints WHERE parentnode=". smart_escape( $id) .";";
  499. $result = mysql_query( $query) or die( mysql_error());
  500. while ( $data = mysql_fetch_assoc( $result))
  501. {
  502. // do the same for the next level.
  503. $ret_val[] = $data['id'];
  504. $ret_val = array_merge( $ret_val, footprint_find_child_nodes( $data['id']));
  505. }
  506. return( $ret_val);
  507. }
  508. function footprint_new_parent( $id, $new_parent)
  509. {
  510. // check if new parent is not anywhere in a child node
  511. if ( !(in_array( $new_parent, footprint_find_child_nodes( $id))))
  512. {
  513. /* do transaction */
  514. $query = "UPDATE footprints SET parentnode=". smart_escape( $new_parent) ." WHERE id=". smart_escape( $id) ." LIMIT 1;";
  515. mysql_query( $query) or die( mysql_error());
  516. }
  517. }
  518. function footprint_count()
  519. {
  520. $query = "SELECT count(*) as count FROM footprints;";
  521. $result = mysql_query( $query) or die( mysql_error());
  522. $data = mysql_fetch_array( $result);
  523. return( $data['count']);
  524. }
  525. function footprint_select( $id)
  526. {
  527. $query = "SELECT name, parentnode FROM footprints".
  528. " WHERE id=". smart_escape( $id) .";";
  529. $result = mysql_query( $query) or die( mysql_error());
  530. return( mysql_fetch_assoc( $result));
  531. }
  532. /*
  533. * parameter: string to search
  534. * result: id from database
  535. */
  536. function footprint_get_id( $footprint)
  537. {
  538. $query = "SELECT id FROM footprints WHERE name=". smart_escape( $footprint) ." LIMIT 1;";
  539. $result = mysql_query( $query) or die( mysql_error());
  540. $data = mysql_fetch_array( $result);
  541. return( $data['id']);
  542. }
  543. /*
  544. * parameter: string to search
  545. * result: true if exists in database
  546. * false if not exist
  547. */
  548. function footprint_exists( $footprint)
  549. {
  550. $query = "SELECT count(*) as count FROM footprints WHERE name=". smart_escape( $footprint) .";";
  551. $result = mysql_query( $query) or die( mysql_error());
  552. $data = mysql_fetch_array( $result);
  553. return( ($data['count'] > 0) ? true : false );
  554. }
  555. /*
  556. * search for footprint picture on disk
  557. * result: path and filename if succesful
  558. * false if not exist
  559. */
  560. function footprint_picture_exists( $footprint)
  561. {
  562. // workaround for php 4 with missing glob_recursive
  563. if ( ! function_exists('glob_recursive'))
  564. {
  565. $path[] = 'tools/footprints';
  566. while( count( $path) != 0)
  567. {
  568. $entries = glob( array_shift( $path));
  569. if ( is_array( $entries ) && count( $entries) > 0)
  570. {
  571. foreach( $entries as $diritem)
  572. {
  573. if ( is_dir( $diritem))
  574. {
  575. // search in directory for png file
  576. $result = glob( $diritem. '/'. $footprint. '.png');
  577. if ($result)
  578. return $result[0];
  579. $path[] = $diritem . '/*';
  580. }
  581. }
  582. }
  583. }
  584. // not found
  585. return false;
  586. }
  587. $res = glob_recursive( "tools/footprints/". $footprint .".png");
  588. return( $res ? $res[0] : false);
  589. }
  590. /* ***************************************************
  591. * supplier querys
  592. */
  593. function supplier_add( $new)
  594. {
  595. $query = "INSERT INTO suppliers (name) VALUES (". smart_escape( $new) .");";
  596. mysql_query( $query) or die( mysql_error());
  597. return( mysql_insert_id());
  598. }
  599. function supplier_delete( $supplier)
  600. {
  601. $query = "DELETE FROM suppliers WHERE id=". smart_escape( $supplier) ." LIMIT 1;";
  602. mysql_query( $query) or die( mysql_error());
  603. }
  604. function supplier_rename( $supplier, $new_name)
  605. {
  606. $query = "UPDATE suppliers SET name=". smart_escape( $new_name) ." WHERE id=". smart_escape( $supplier) ." LIMIT 1";
  607. mysql_query( $query) or die( mysql_error());
  608. }
  609. function suppliers_build_list( $select = -1)
  610. {
  611. $query = "SELECT id, name FROM suppliers ORDER BY name ASC;";
  612. $result = mysql_query( $query);
  613. while ( $data = mysql_fetch_assoc( $result))
  614. {
  615. $selected = ($select == $data['id']) ? 'selected': '';
  616. print "<option ". $selected ." value=\"". smart_unescape( $data['id']) ."\">". smart_unescape( $data['name']) ."</option>\n";
  617. }
  618. }
  619. function suppliers_count()
  620. {
  621. $query = "SELECT count(*) as count FROM suppliers;";
  622. $result = mysql_query( $query) or die( mysql_error());
  623. $data = mysql_fetch_array( $result);
  624. return( $data['count']);
  625. }
  626. function supplier_select( $id)
  627. {
  628. $query = "SELECT name FROM suppliers".
  629. " WHERE id=". smart_escape( $id) .";";
  630. $result = mysql_query( $query) or die( mysql_error());
  631. return( mysql_fetch_assoc( $result));
  632. }
  633. function supplier_get_id( $supplier)
  634. {
  635. $query = "SELECT id FROM suppliers WHERE name=". smart_escape( $supplier)." LIMIT 1;";
  636. $result = mysql_query( $query);
  637. $result_array = mysql_fetch_array( $result);
  638. return( $result_array['id']);
  639. }
  640. function supplier_exists( $supplier)
  641. {
  642. $query = "SELECT name FROM suppliers WHERE name=". smart_escape( $supplier).";";
  643. $res = mysql_query( $query);
  644. $data = mysql_num_rows( $res);
  645. return( ($data == 1) ? true : false );
  646. }
  647. /* ***************************************************
  648. * location querys
  649. */
  650. /*
  651. * The buildtree function creates a tree for <select> tags.
  652. * It recurses trough all locations (and sublocations) and
  653. * creates the tree. Deeper levels have more spaces in front.
  654. * As the top-most location (it doesn't exist!) has the ID 0,
  655. * you have to supply id=0 at the very beginning.
  656. */
  657. function location_tree_build( $id = 0, $level = 0, $select = -1, $show_all = true)
  658. {
  659. $query_all = ( $show_all) ? '' : ' AND (is_full=0 OR id='. smart_escape( $select) .')';
  660. $query = "SELECT id, name, is_full FROM storeloc".
  661. " WHERE parentnode=". smart_escape( $id).
  662. $query_all.
  663. " ORDER BY name ASC;";
  664. $r = mysql_query( $query) or die( mysql_error());
  665. while ( $data = mysql_fetch_assoc( $r) )
  666. {
  667. $selected = ($select == $data['id']) ? 'selected': '';
  668. print "<option ". $selected ." value=\"". smart_unescape( $data['id']) . "\">";
  669. for ( $i = 0; $i < $level; $i++)
  670. print "&nbsp;&nbsp;&nbsp;";
  671. print smart_unescape( $data['name']).
  672. ( $data['is_full'] ? ' [voll]' : '').
  673. "</option>\n";
  674. // do the same for the next level.
  675. location_tree_build( $data['id'], $level + 1, $select, $show_all);
  676. }
  677. }
  678. function location_add( $new_location, $parent_node = 0)
  679. {
  680. $query = "INSERT INTO storeloc (name, parentnode) VALUES (".
  681. smart_escape( $new_location) .",".
  682. smart_escape( $parent_node) .");";
  683. mysql_query( $query) or die( mysql_error());
  684. return( mysql_insert_id());
  685. }
  686. function location_delete( $id)
  687. {
  688. // TODO: lock database
  689. // catch actual parent node
  690. $query = "SELECT parentnode FROM storeloc".
  691. " WHERE id=". smart_escape( $id) .";";
  692. $result = mysql_query( $query) or die( mysql_error());
  693. $data = mysql_fetch_assoc( $result);
  694. $parent = $data['parentnode'];
  695. // delete location
  696. $query = "DELETE FROM storeloc".
  697. " WHERE id=". smart_escape( $id) ." LIMIT 1;";
  698. mysql_query( $query) or die( mysql_error());
  699. // resort all child locations
  700. if ( ! is_null( $parent))
  701. {
  702. $query = "UPDATE storeloc".
  703. " SET parentnode=". $parent.
  704. " WHERE parentnode=". smart_escape( $id) .";";
  705. mysql_query( $query) or die( mysql_error());
  706. }
  707. }
  708. function location_rename( $id, $new_name)
  709. {
  710. $query = "UPDATE storeloc".
  711. " SET name=". smart_escape( $new_name).
  712. " WHERE id=". smart_escape( $id) ." LIMIT 1;";
  713. mysql_query( $query) or die( mysql_error());
  714. }
  715. /*
  716. * find all nodes below and given node
  717. */
  718. function location_find_child_nodes( $id)
  719. {
  720. $result = array();
  721. $query = "SELECT id FROM storeloc WHERE parentnode=". smart_escape( $id) .";";
  722. $r = mysql_query( $query);
  723. while ( $data = mysql_fetch_assoc( $r))
  724. {
  725. // do the same for the next level.
  726. $result[] = $data['id'];
  727. $result = array_merge( $result, location_find_child_nodes( $data['id']));
  728. }
  729. return( $result);
  730. }
  731. function location_new_parent( $id, $new_parent)
  732. {
  733. // check if new parent is not anywhere in a child node
  734. if ( !(in_array( $new_parent, location_find_child_nodes( $id))))
  735. {
  736. /* do transaction */
  737. $query = "UPDATE storeloc SET parentnode=". smart_escape( $new_parent) ." WHERE id=". smart_escape( $id) ." LIMIT 1;";
  738. mysql_query( $query) or die( mysql_error());
  739. }
  740. }
  741. function location_mark_as_full( $id, $is_full)
  742. {
  743. $status = ( $is_full) ? '1' : '0';
  744. $query = "UPDATE storeloc".
  745. " SET is_full=". smart_escape( $status).
  746. " WHERE id=". smart_escape( $id) ." LIMIT 1;";
  747. mysql_query( $query) or die( mysql_error());
  748. }
  749. function location_count()
  750. {
  751. $query = "SELECT count(*) as count FROM storeloc;";
  752. $result = mysql_query( $query) or die( mysql_error());
  753. $data = mysql_fetch_array( $result);
  754. return( $data['count']);
  755. }
  756. function location_select( $id)
  757. {
  758. $query = "SELECT name, parentnode, is_full FROM storeloc".
  759. " WHERE id=". smart_escape( $id) .";";
  760. $result = mysql_query( $query) or die( mysql_error());
  761. return( mysql_fetch_assoc( $result));
  762. }
  763. function location_get_id( $storeloc)
  764. {
  765. $query = "SELECT id FROM storeloc WHERE name=". smart_escape( $storeloc)." LIMIT 1;";
  766. $result = mysql_query( $query) or die( mysql_error());
  767. $result_array = mysql_fetch_array( $result);
  768. return( $result_array['id']);
  769. }
  770. function location_get_name( $id)
  771. {
  772. $query = "SELECT name FROM storeloc WHERE id=". smart_escape( $id) .";";
  773. $result = mysql_query( $query) or die( mysql_error());
  774. $data = mysql_fetch_assoc( $result);
  775. return( $data['name']);
  776. }
  777. function location_exists( $storeloc)
  778. {
  779. $query = "SELECT name FROM storeloc WHERE name=". smart_escape( $storeloc).";";
  780. $result = mysql_query( $query) or die( mysql_error());
  781. $data = mysql_num_rows( $result);
  782. return( ($data == 1) ? true : false );
  783. }
  784. /* ***************************************************
  785. * categorie querys
  786. */
  787. /*
  788. * The buildtree function creates a tree for <select> tags.
  789. * It recurses trough all categories (and subcategories) and
  790. * creates the tree. Deeper levels have more spaces in front.
  791. * As the top-most category (it doesn't exist!) has the ID 0,
  792. * you have to supply cid=0 at the very beginning.
  793. */
  794. function categories_build_tree( $cid = 0, $level = 0, $select = -1)
  795. {
  796. $query = "SELECT id, name FROM categories".
  797. " WHERE parentnode=". smart_escape( $cid).
  798. " ORDER BY name ASC;";
  799. $result = mysql_query( $query) or die( mysql_error());
  800. while ( $data = mysql_fetch_assoc( $result))
  801. {
  802. $selected = ($select == $data['id']) ? 'selected': '';
  803. print "<option ". $selected ." value=\"". smart_unescape( $data['id']) . "\">";
  804. for ($i = 0; $i < $level; $i++)
  805. print "&nbsp;&nbsp;&nbsp;";
  806. print smart_unescape( $data['name']) ."</option>\n";
  807. // do the same for the next level.
  808. categories_build_tree( $data['id'], $level + 1, $select);
  809. }
  810. }
  811. /* This recursive procedure builds the tree of categories.
  812. There's nothing special about it, so no more comments.
  813. Warning: Infinite recursion can occur when the DB is
  814. corrupted! But normally everything should be fine. */
  815. function categories_build_navtree( $pid = 0)
  816. {
  817. $query = "SELECT id, name FROM categories".
  818. " WHERE parentnode=". smart_escape( $pid).
  819. " ORDER BY categories.name ASC;";
  820. if ( $result = mysql_query( $query))
  821. {
  822. while ( $data = mysql_fetch_assoc( $result))
  823. {
  824. print "cat_tree.add(". smart_unescape( $data['id']) .",".
  825. smart_unescape( $pid) .",'".
  826. smart_unescape( $data['name']).
  827. "','showparts.php?cid=".
  828. smart_unescape( $data['id']).
  829. "\"','','content_frame');\n";
  830. categories_build_navtree( $data['id']);
  831. }
  832. }
  833. }
  834. function category_add( $new, $parent_node = 0)
  835. {
  836. $query = "INSERT INTO categories (name, parentnode) VALUES (".
  837. smart_escape( $new) .",".
  838. smart_escape( $parent_node) .");";
  839. mysql_query( $query) or die( mysql_error());
  840. return( mysql_insert_id());
  841. }
  842. function category_del( $old)
  843. {
  844. // TODO: lock database
  845. // catch actual parent node
  846. $query = "SELECT parentnode FROM categories".
  847. " WHERE id=". smart_escape( $old) .";";
  848. $result = mysql_query( $query) or die( mysql_error());
  849. $data = mysql_fetch_assoc( $result);
  850. $parent = $data['parentnode'];
  851. // delete footprint
  852. $query = "DELETE FROM categories".
  853. " WHERE id=". smart_escape( $old).
  854. " LIMIT 1;";
  855. mysql_query( $query) or die( mysql_error());
  856. // resort all child footprints to parent node
  857. if ( ! is_null( $parent))
  858. {
  859. $query = "UPDATE categories SET parentnode=". $parent ." WHERE parentnode=". smart_escape( $old) ." ;";
  860. mysql_query( $query) or die( mysql_error());
  861. }
  862. }
  863. function category_rename( $id, $new_name)
  864. {
  865. $query = "UPDATE categories SET name=". smart_escape( $new_name) ." WHERE id=". smart_escape( $id) ." LIMIT 1";
  866. mysql_query( $query) or die( mysql_error());
  867. }
  868. /*
  869. * find all nodes below and given node
  870. */
  871. function categories_find_child_nodes( $id)
  872. {
  873. $ret_val = array();
  874. $query = "SELECT id FROM categories WHERE parentnode=". smart_escape( $id) .";";
  875. $result = mysql_query( $query) or die( mysql_error());
  876. while ( $data = mysql_fetch_assoc( $result))
  877. {
  878. // do the same for the next level.
  879. $ret_val[] = $data['id'];
  880. $ret_val = array_merge( $ret_val, categories_find_child_nodes( $data['id']));
  881. }
  882. return( $ret_val);
  883. }
  884. function categories_or_child_nodes( $cid, $with_subcategories = true)
  885. {
  886. $ret_val = "id_category=". smart_escape( $cid);
  887. if ($with_subcategories)
  888. {
  889. $query = "SELECT id FROM categories WHERE parentnode=". smart_escape( $cid) .";";
  890. $result = mysql_query( $query);
  891. while ( $data = mysql_fetch_assoc( $result))
  892. {
  893. $ret_val .= " OR ". categories_or_child_nodes( smart_unescape( $data['id']));
  894. }
  895. }
  896. return( $ret_val);
  897. }
  898. /*
  899. * find all nodes below and given node
  900. */
  901. function category_find_child_nodes( $id)
  902. {
  903. $ret_val = array();
  904. $query = "SELECT id FROM categories".
  905. " WHERE parentnode=". smart_escape( $id) .";";
  906. $result = mysql_query( $query);
  907. while ( $data = mysql_fetch_assoc( $result))
  908. {
  909. // do the same for the next level.
  910. $ret_val[] = $data['id'];
  911. $ret_val = array_merge( $ret_val, category_find_child_nodes( $data['id']));
  912. }
  913. return( $ret_val);
  914. }
  915. function category_new_parent( $id, $new_parent)
  916. {
  917. // check if new parent is not anywhere in a child node
  918. if ( !(in_array( $new_parent, category_find_child_nodes( $id))))
  919. {
  920. /* do transaction */
  921. $query = "UPDATE categories SET parentnode=". smart_escape( $new_parent) ." WHERE id=". smart_escape( $id) ." LIMIT 1;";
  922. mysql_query( $query) or die( mysql_error());
  923. }
  924. }
  925. function categories_count()
  926. {
  927. $query = "SELECT count(*) as count FROM categories;";
  928. $result = mysql_query( $query) or die( mysql_error());
  929. $data = mysql_fetch_array( $result);
  930. return( $data['count']);
  931. }
  932. function category_select( $id)
  933. {
  934. $query = "SELECT name, parentnode FROM categories".
  935. " WHERE id=". smart_escape( $id) .";";
  936. $result = mysql_query( $query) or die( mysql_error());
  937. return( mysql_fetch_assoc( $result));
  938. }
  939. function category_get_id( $categorie)
  940. {
  941. $query = "SELECT id FROM categories WHERE name=". smart_escape( $categorie)." LIMIT 1;";
  942. $result = mysql_query( $query) or die( mysql_error());
  943. $result_array = mysql_fetch_array( $result);
  944. return( $result_array['id']);
  945. }
  946. /*
  947. * Given the category id this helper-function does a lookup
  948. * and returns the name of the category. At the moment we
  949. * assume that the category id is valid. FIXME
  950. */
  951. function category_get_name( $id)
  952. {
  953. $query = "SELECT name FROM categories WHERE id=". smart_escape($id) .";";
  954. $result = mysql_query( $query) or die( mysql_error());
  955. $data = mysql_fetch_assoc( $result);
  956. return( ( $id == 0) ? "Alles" : smart_unescape( $data['name'] ));
  957. }
  958. function category_exists( $categorie)
  959. {
  960. $query = "SELECT name FROM categories WHERE name=". smart_escape( $categorie) .";";
  961. $result = mysql_query( $query) or die( mysql_error());
  962. $data = mysql_num_rows( $result);
  963. return( ($data == 1) ? true : false );
  964. }
  965. /* ***************************************************
  966. * part querys
  967. */
  968. function part_add( $category_id, $name, $instock, $mininstock, $comment, $obsolete, $footprint_id, $storeloc_id, $supplier_id, $supplierpartnr)
  969. {
  970. $query =
  971. "INSERT INTO parts (".
  972. " id_category,".
  973. " name,".
  974. " instock,".
  975. " mininstock,".
  976. " comment,".
  977. " obsolete,".
  978. " id_footprint,".
  979. " id_storeloc,".
  980. " id_supplier,".
  981. " supplierpartnr) ".
  982. " VALUES (".
  983. smart_escape( $category_id) .",".
  984. smart_escape( $name) .",".
  985. smart_escape( $instock) .",".
  986. smart_escape( $mininstock) .",".
  987. smart_escape( $comment) .",".
  988. smart_escape( $obsolete) .",".
  989. smart_escape( $footprint_id) .",".
  990. smart_escape( $storeloc_id) .",".
  991. smart_escape( $supplier_id) .",".
  992. smart_escape( $supplierpartnr) .");";
  993. $result = mysql_query( $query) or die( mysql_error());
  994. return( mysql_insert_id());
  995. }
  996. function part_update( $part_id, $category_id, $name, $instock, $mininstock, $comment, $obsolete, $footprint_id, $storeloc_id, $supplier_id, $supplierpartnr)
  997. {
  998. $query =
  999. "UPDATE parts".
  1000. " SET".
  1001. " name=". smart_escape( $name) .",".
  1002. " instock=". smart_escape( $instock) .",".
  1003. " mininstock=". smart_escape( $mininstock) .",".
  1004. " comment=". smart_escape( $comment) .",".
  1005. " obsolete=". smart_escape( $obsolete) .",".
  1006. " id_category=". smart_escape( $category_id) .",".
  1007. " id_footprint=". smart_escape( $footprint_id) .",".
  1008. " id_storeloc=". smart_escape( $storeloc_id) .",".
  1009. " id_supplier=". smart_escape( $supplier_id) .",".
  1010. " supplierpartnr=". smart_escape( $supplierpartnr).
  1011. " WHERE id=". smart_escape( $part_id).
  1012. " LIMIT 1;";
  1013. mysql_query( $query) or die( mysql_error());
  1014. }
  1015. function part_delete( $part_id)
  1016. {
  1017. // delete pending datasheets
  1018. $query = "DELETE FROM datasheets".
  1019. " WHERE part_id=". smart_escape( $part_id) .";";
  1020. mysql_query( $query) or die( mysql_error());
  1021. price_delete( $part_id);
  1022. // delete pictures
  1023. $query = "DELETE FROM pictures".
  1024. " WHERE part_id=". smart_escape( $part_id) .";";
  1025. mysql_query( $query) or die( mysql_error());
  1026. // delete part itself
  1027. $query = "DELETE FROM parts".
  1028. " WHERE id=". smart_escape( $part_id). " LIMIT 1";
  1029. mysql_query( $query) or die( mysql_error());
  1030. }
  1031. function parts_stock_increase( $pid, $count = 1)
  1032. {
  1033. $query = "UPDATE parts SET instock=instock+". smart_escape( $count).
  1034. " WHERE id=". smart_escape( $pid) ." LIMIT 1;";
  1035. $result = mysql_query( $query) or die( mysql_error());
  1036. }
  1037. function parts_stock_decrease( $pid, $count = 1)
  1038. {
  1039. $query = "UPDATE parts SET instock=instock-". smart_escape( $count).
  1040. " WHERE id=". smart_escape( $pid) ." AND instock >= ". smart_escape( $count) ." LIMIT 1;";
  1041. $result = mysql_query( $query) or die( mysql_error());
  1042. }
  1043. function parts_count()
  1044. {
  1045. $query = "SELECT count(*) as count FROM parts;";
  1046. $result = mysql_query( $query) or die( mysql_error());
  1047. $data = mysql_fetch_assoc( $result);
  1048. return( $data['count']);
  1049. }
  1050. function parts_count_sum_value()
  1051. {
  1052. $query = "SELECT sum( preise.preis * parts.instock) AS sum FROM parts".
  1053. " LEFT JOIN preise ON parts.id=preise.part_id;";
  1054. $result = mysql_query( $query) or die( mysql_error());
  1055. $data = mysql_fetch_assoc( $result);
  1056. return( $data['sum']);
  1057. }
  1058. function parts_count_sum_instock()
  1059. {
  1060. $query = "SELECT sum( instock) as sum FROM parts;";
  1061. $result = mysql_query( $query) or die( mysql_error());
  1062. $data = mysql_fetch_assoc( $result);
  1063. return( $data['sum']);
  1064. }
  1065. function parts_count_with_prices()
  1066. {
  1067. $query = "SELECT count(*) as count FROM preise".
  1068. " LEFT JOIN parts ON parts.id=preise.part_id;";
  1069. $result = mysql_query( $query) or die( mysql_error());
  1070. $data = mysql_fetch_assoc( $result);
  1071. return( $data['count']);
  1072. }
  1073. function parts_count_on_category( $cid)
  1074. {
  1075. $query = "SELECT count(*) as count FROM parts".
  1076. " WHERE id_category=". smart_escape( $cid) .";";
  1077. $result = mysql_query( $query) or die( mysql_error());
  1078. $data = mysql_fetch_assoc( $result);
  1079. return( $data['count']);
  1080. }
  1081. function parts_count_on_storeloc( $id)
  1082. {
  1083. $query = "SELECT count(*) as count FROM parts".
  1084. " WHERE id_storeloc=". smart_escape( $id) .";";
  1085. $result = mysql_query( $query) or die( mysql_error());
  1086. $data = mysql_fetch_assoc( $result);
  1087. return( $data['count']);
  1088. }
  1089. function parts_select( $pid)
  1090. {
  1091. $query = "SELECT ".
  1092. " parts.id,".
  1093. " parts.name,".
  1094. " parts.instock,".
  1095. " parts.mininstock,".
  1096. " footprints.name AS 'footprint',".
  1097. " storeloc.name AS 'location',".
  1098. " storeloc.is_full AS 'location_is_full',".
  1099. " suppliers.name AS 'supplier',".
  1100. " parts.id_footprint,".
  1101. " parts.id_storeloc,".
  1102. " parts.id_supplier,".
  1103. " parts.supplierpartnr,".
  1104. " preise.preis,".
  1105. " preise.ma,".
  1106. " parts.comment,".
  1107. " parts.obsolete".
  1108. " FROM parts".
  1109. " LEFT JOIN footprints ON parts.id_footprint=footprints.id".
  1110. " LEFT JOIN storeloc ON parts.id_storeloc=storeloc.id".
  1111. " LEFT JOIN suppliers ON parts.id_supplier=suppliers.id".
  1112. " LEFT JOIN preise ON parts.id=preise.part_id".
  1113. " WHERE parts.id=". smart_escape( $pid).
  1114. " ORDER BY preise.ma DESC LIMIT 1;";
  1115. $result = mysql_query( $query) or die( mysql_error());
  1116. return( $result);
  1117. }
  1118. function parts_select_category( $cid, $with_subcategories)
  1119. {
  1120. // check if with or without subcategories
  1121. $catclause = categories_or_child_nodes( $cid, $with_subcategories);
  1122. $query = "SELECT".
  1123. " parts.id,".
  1124. " parts.name,".
  1125. " parts.instock,".
  1126. " parts.mininstock,".
  1127. " footprints.name AS 'footprint',".
  1128. " storeloc.name AS 'location',".
  1129. " parts.comment,".
  1130. " parts.obsolete,".
  1131. " parts.supplierpartnr".
  1132. " FROM parts".
  1133. " LEFT JOIN footprints ON parts.id_footprint=footprints.id".
  1134. " LEFT JOIN storeloc ON parts.id_storeloc=storeloc.id".
  1135. " WHERE (". $catclause .")".
  1136. " ORDER BY name ASC;";
  1137. $result = mysql_query( $query) or die( mysql_error());
  1138. return( $result);
  1139. }
  1140. function parts_select_without_price()
  1141. {
  1142. $query = "SELECT ".
  1143. " parts.id,".
  1144. " parts.name,".
  1145. " parts.instock,".
  1146. " parts.mininstock,".
  1147. " footprints.name AS 'footprint',".
  1148. " storeloc.name AS 'location',".
  1149. " suppliers.name AS 'supplier',".
  1150. " parts.supplierpartnr,".
  1151. " parts.comment".
  1152. " FROM parts".
  1153. " LEFT JOIN footprints ON parts.id_footprint=footprints.id".
  1154. " LEFT JOIN storeloc ON parts.id_storeloc=storeloc.id".
  1155. " LEFT JOIN suppliers ON parts.id_supplier=suppliers.id".
  1156. " LEFT JOIN preise ON parts.id=preise.part_id".
  1157. " WHERE".
  1158. " (preise.id IS NULL) AND".
  1159. " (parts.obsolete = false)".
  1160. " ORDER BY name ASC;";
  1161. $result = mysql_query( $query) or die( mysql_error());
  1162. return( $result);
  1163. }
  1164. function parts_select_obsolete()
  1165. {
  1166. $query = "SELECT ".
  1167. " parts.id,".
  1168. " parts.name,".
  1169. " parts.instock,".
  1170. " parts.mininstock,".
  1171. " footprints.name AS 'footprint',".
  1172. " storeloc.name AS 'location',".
  1173. " suppliers.name AS 'supplier',".
  1174. " parts.supplierpartnr,".
  1175. " parts.comment,".
  1176. " preise.preis".
  1177. " FROM parts".
  1178. " LEFT JOIN footprints ON parts.id_footprint=footprints.id".
  1179. " LEFT JOIN storeloc ON parts.id_storeloc=storeloc.id".
  1180. " LEFT JOIN suppliers ON parts.id_supplier=suppliers.id".
  1181. " LEFT JOIN preise ON parts.id=preise.part_id".
  1182. " WHERE".
  1183. " (parts.obsolete = true)".
  1184. " ORDER BY name ASC;";
  1185. $result = mysql_query( $query) or die( mysql_error());
  1186. return( $result);
  1187. }
  1188. function parts_select_search( $keyword, $search_nam, $search_com, $search_sup, $search_snr, $search_loc, $search_fpr, $export = false)
  1189. {
  1190. // build search strings
  1191. $query_nam = ( $search_nam) ? " OR (parts.name LIKE ". $keyword.")" : "";
  1192. $query_com = ( $search_com) ? " OR (parts.comment LIKE ". $keyword.")" : "";
  1193. $query_sup = ( $search_sup) ? " OR (suppliers.name LIKE ". $keyword.")" : "";
  1194. $query_snr = ( $search_snr) ? " OR (parts.supplierpartnr LIKE ". $keyword.")" : "";
  1195. $query_loc = ( $search_loc) ? " OR (storeloc.name LIKE ". $keyword.")" : "";
  1196. $query_fpr = ( $search_fpr) ? " OR (footprints.name LIKE ". $keyword.")" : "";
  1197. $search = $query_nam. $query_com. $query_sup. $query_snr. $query_loc. $query_fpr;
  1198. $query = ( ! $export) ?
  1199. "SELECT ".
  1200. " parts.id,".
  1201. " parts.name,".
  1202. " parts.instock,".
  1203. " parts.mininstock,".
  1204. " footprints.name AS 'footprint',".
  1205. " storeloc.name AS 'location',".
  1206. " parts.comment,".
  1207. " parts.obsolete,".
  1208. " parts.id_category,".
  1209. " parts.supplierpartnr".
  1210. " FROM parts".
  1211. " LEFT JOIN footprints ON parts.id_footprint=footprints.id".
  1212. " LEFT JOIN storeloc ON parts.id_storeloc=storeloc.id".
  1213. " LEFT JOIN suppliers ON parts.id_supplier=suppliers.id".
  1214. " WHERE FALSE ". $search.
  1215. " ORDER BY parts.id_category, parts.name ASC;"
  1216. :
  1217. "SELECT ".
  1218. " categories.name AS 'category', ".
  1219. " parts.name,".
  1220. " parts.instock AS 'stock', ".
  1221. " footprints.name AS 'footprint',".
  1222. " storeloc.name AS 'location', ".
  1223. " suppliers.name AS 'supplier', ".
  1224. " parts.supplierpartnr AS 'order_number',".
  1225. " parts.comment".
  1226. " FROM parts".
  1227. " LEFT JOIN categories ON parts.id_category=categories.id".
  1228. " LEFT JOIN footprints ON parts.id_footprint=footprints.id".
  1229. " LEFT JOIN storeloc ON parts.id_storeloc=storeloc.id".
  1230. " LEFT JOIN suppliers ON parts.id_supplier=suppliers.id".
  1231. " WHERE FALSE ". $search.
  1232. " ORDER BY parts.id_category, parts.name ASC;";
  1233. $result = mysql_query( $query) or die( mysql_error());
  1234. return( $result);
  1235. }
  1236. function parts_select_order( $supplier_id = 0)
  1237. {
  1238. if ( $supplier_id == 0)
  1239. {
  1240. $query =
  1241. "SELECT ".
  1242. " parts.id,".
  1243. " parts.name,".
  1244. " footprints.name AS 'footprint',".
  1245. " parts.mininstock-parts.instock AS 'diff',".

Large files files are truncated, but you can click here to view the full file