search.html 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358
  1. <!DOCTYPE html>
  2. <html lang="en">
  3. <head>
  4. <meta charset="UTF-8">
  5. <title>{{ title }}</title>
  6. <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  7. <!-- 新 Bootstrap4 核心 CSS 文件 -->
  8. <link rel="stylesheet" href="https://cdn.bootcss.com/bootstrap/4.1.0/css/bootstrap.min.css">
  9. <!-- jQuery文件。务必在bootstrap.min.js 之前引入 -->
  10. <script src="https://cdn.bootcss.com/jquery/3.2.1/jquery.min.js"></script>
  11. <!-- popper.min.js 用于弹窗、提示、下拉菜单 -->
  12. <script src="https://cdn.bootcss.com/popper.js/1.12.5/umd/popper.min.js"></script>
  13. <!-- 最新的 Bootstrap4 核心 JavaScript 文件 -->
  14. <script src="https://cdn.bootcss.com/bootstrap/4.1.0/js/bootstrap.min.js"></script>
  15. <!--可用來建立使用者小圖示-->
  16. <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
  17. </head>
  18. <style>
  19. .input-cond-delete {
  20. background: #E43030;
  21. border: 1px solid #CFCFCF;
  22. box-sizing: border-box;
  23. border-radius: 5px;
  24. margin-left: 5px;
  25. width: 65px;
  26. height: 33px;
  27. font-size: 16px;
  28. text-align: center;
  29. line-height: 16px;
  30. color: #FFFFFF;
  31. }
  32. .input-cond-add {
  33. background: #008CBA;
  34. border: 1px solid #CFCFCF;
  35. box-sizing: border-box;
  36. border-radius: 5px;
  37. margin-left: 5px;
  38. width: 65px;
  39. height: 33px;
  40. font-size: 16px;
  41. text-align: center;
  42. line-height: 16px;
  43. color: #FFFFFF;
  44. }
  45. </style>
  46. <body>
  47. 會計科目:
  48. <select id="accountingsubjects" onChange="">
  49. <option value="00"></option>
  50. {% for data in AccountingSubjects_data %}
  51. <option value="{{data[0]}}">{{data[0]}}-{{data[1]}}</option>
  52. {% endfor %}
  53. </select>
  54. 類別:
  55. <select id="category" onChange="">
  56. <option value="00"></option>
  57. {% for data in Category_data %}
  58. <option value="{{data[0]}}">{{data[0]}}-{{data[1]}}</option>
  59. {% endfor %}
  60. </select>
  61. 流水號:
  62. <input id="serialnumber" placeholder="0001" style="height: 25px; width: 45px;">
  63. 供應商:
  64. <select id="supplier" onChange="">
  65. <option value="00"></option>
  66. {% for data in Supplier_data %}
  67. <option value="{{data[0]}}">{{data[0]}}-{{data[1]}}</option>
  68. {% endfor %}
  69. </select><br>
  70. 零件名稱:
  71. <select id="component_name_select" onChange="">
  72. <option value="00"></option>
  73. {% for data in Component_data %}
  74. <option value="{{data[0]}}">{{data[0]}}</option>
  75. {% endfor %}
  76. </select>
  77. <input id="component_name_input" placeholder="零件關鍵字輸入" style="height: 25px; width: 120px;">
  78. <input type="button" id="search_component_table" value="零件表查詢" onclick="search_component_table();">
  79. <input type="button" id="search_component_standard_table" value="(零件)規格表查詢" onclick="search_component_standard_table();">
  80. <input type="button" id="search_component_stock_table" value="(零件)庫存表查詢" onclick="search_component_stock_table();">
  81. <input type="button" id="search_component_import_table" value="(零件)進貨表查詢" onclick="search_component_import_table();">
  82. <input type="button" id="search_component_export_table" value="(零件)銷貨表查詢" onclick="search_component_export_table();">
  83. <br>
  84. <a id="create_component_table_item" href="/create_component_table_item">(零件)新增零件</a>
  85. <script>
  86. // (零件)銷貨表查詢
  87. function search_component_export_table() {
  88. var sql = 'SELECT 銷貨表.* FROM 零件表';
  89. sql += ' INNER JOIN 銷貨表';
  90. sql += ' ON 零件表.內部料號 = 銷貨表.內部料號';
  91. search_relation_insertdata(sql)
  92. }
  93. // (零件)進貨表查詢
  94. function search_component_import_table() {
  95. var sql = 'SELECT 進貨表.* FROM 零件表';
  96. sql += ' INNER JOIN 進貨表';
  97. sql += ' ON 零件表.內部料號 = 進貨表.內部料號';
  98. search_relation_insertdata(sql)
  99. }
  100. // (零件)庫存表查詢
  101. function search_component_stock_table() {
  102. var sql = 'SELECT 庫存表.時間, 庫存表.內部料號, 庫存表.數量 FROM 零件表';
  103. sql += ' INNER JOIN 庫存表';
  104. sql += ' ON 零件表.內部料號 = 庫存表.內部料號';
  105. search_relation_insertdata(sql)
  106. }
  107. // (零件)規格表查詢
  108. function search_component_standard_table() {
  109. var sql = 'SELECT 規格表.* FROM 零件表';
  110. sql += ' INNER JOIN 規格表';
  111. sql += ' ON 零件表.內部料號 = 規格表.內部料號';
  112. search_relation_insertdata(sql)
  113. }
  114. // 各資料表←關聯→零件表, 程式
  115. function search_relation_insertdata(sql) {
  116. var accountingsubjects_select = $("#accountingsubjects").val();
  117. var category_select = $("#category").val();
  118. var serialnumber_select = $("#serialnumber").val();
  119. var supplier_select = $("#supplier").val();
  120. var component_name_select = $("#component_name_select").val();
  121. var component_name_input = $("#component_name_input").val();
  122. var AND_list = [];
  123. console.log(accountingsubjects_select, category_select, serialnumber_select, supplier_select)
  124. var sql = sql;
  125. // 篩選 會計科目/類別/供應商 關鍵字
  126. if (accountingsubjects_select != '00') {
  127. AND_list.push('會計科目 = "' + accountingsubjects_select + '"')
  128. }
  129. if (category_select != '00') {
  130. AND_list.push('類別 = "' + category_select + '"')
  131. }
  132. if (serialnumber_select != '') {
  133. AND_list.push('流水號 = "' + serialnumber_select + '"')
  134. }
  135. if (supplier_select != '00') {
  136. AND_list.push('供應商 = "' + supplier_select + '"')
  137. }
  138. if (component_name_select != '00') {
  139. AND_list.push('零件表.名稱 = "' + component_name_select + '"')
  140. } else if (component_name_input != '') {
  141. AND_list.push('零件表.名稱 LIKE "%' + component_name_input + '%"')
  142. }
  143. console.log("AND_list: ", AND_list)
  144. if (AND_list != '') {
  145. sql += ' WHERE ' + AND_list.join(' AND ');
  146. }
  147. if (sql.match('INNER JOIN 庫存表')) {
  148. sql += ' ORDER BY 時間 DESC LIMIT 1'
  149. }
  150. var sql_data = {
  151. "sql":sql
  152. };
  153. console.log("sql_data: ", sql_data)
  154. $.get('/sql_get', sql_data, function (res) {
  155. // console.log("res: " + res)
  156. // console.log("res.labels:" + res.labels)
  157. // console.log("res.labels[0]:" + res.labels[0])
  158. // console.log("res.labels[0][0]:" + res.labels[0][0])
  159. // console.log("res.sql_field:" + res.sql_field)
  160. // console.log("res.sql_field[0]:" + res.sql_field[0])
  161. var data = " ";
  162. if (res.labels == '') {
  163. data += '-- 無符合關鍵字之資料 --'
  164. } else {
  165. data += '<table class="table table-bordered" style="margin-top: 10px;">';
  166. data += '<tr>';
  167. for (let fields = 0; fields < res.sql_field.length; fields++) {
  168. data += '<th>' + res.sql_field[fields][0] + '</th>';
  169. }
  170. data += '</tr>';
  171. for (let items = 0; items < res.labels.length; items++) {
  172. data += '<tr>';
  173. for (let item = 0; item < res.labels[items].length; item++) {
  174. console.log("res.labels[items][item]: ", res.labels[items][item], typeof(res.labels[items][item]))
  175. data += '<td>' + res.labels[items][item] + '</td>';
  176. };
  177. data += '</tr>';
  178. };
  179. data += '</table>';
  180. }
  181. var sectorSelect = document.getElementById("result");
  182. sectorSelect.innerHTML = data;
  183. }, 'json');
  184. }
  185. // 修改確認
  186. function ComfirmUpdate_component(company_id) {
  187. console.log("修改完成: " + company_id)
  188. // var new_name = document.getElementById(company_id + '_5_ta');
  189. // var new_name_value = new_name.val();
  190. var new_name_value = $('#' + company_id + '_5_ta').val();
  191. console.log("new_name_value: " + new_name_value)
  192. sql = '';
  193. sql += 'UPDATE 零件表 SET 名稱 = \'' + new_name_value + '\' WHERE 內部料號 = \'' + company_id + '\';'
  194. console.log('sql: ' + sql)
  195. var sql_data = { "sql":sql };
  196. $.get('/sql_get', sql_data, function (res) {
  197. console.log(res)
  198. }, 'json');
  199. }
  200. // 修改
  201. function update_component(company_id) {
  202. console.log("修改 " + company_id)
  203. var old_name =
  204. target_id = company_id + '_5'
  205. var target_id = document.getElementById(company_id + '_5');
  206. var old_name = target_id.innerText;
  207. console.log('old_name: ', old_name)
  208. target_id.innerHTML = "";
  209. textarea = '';
  210. textarea += '<input id="'+ company_id + '_5_ta' + '" value="' + old_name + '" style="height: 25px; width: auto">';
  211. textarea += '<input type="button" value="OK" onclick="ComfirmUpdate_component(\'' + company_id + '\')">';
  212. target_id.insertAdjacentHTML('beforeend', textarea);
  213. }
  214. // 刪除
  215. function delete_component(company_id) {
  216. console.log("刪除 " + company_id)
  217. }
  218. // 零件表查詢
  219. function search_component_table(){
  220. var accountingsubjects_select = $("#accountingsubjects").val();
  221. var category_select = $("#category").val();
  222. var serialnumber_select = $("#serialnumber").val();
  223. var supplier_select = $("#supplier").val();
  224. var component_name_select = $("#component_name_select").val();
  225. var component_name_input = $("#component_name_input").val();
  226. var AND_list = [];
  227. console.log(accountingsubjects_select, category_select, serialnumber_select, supplier_select)
  228. // sql = 'SELECT * FROM 零件表';
  229. sql = 'SELECT 內部料號, 會計科目表.會計科目_名稱, 類別表.類別_名稱, 流水號, 供應商.公司, 名稱' +
  230. ' FROM (' +
  231. '(零件表 ' +
  232. 'INNER JOIN 供應商' +
  233. ' ON 供應商.供應商 = 零件表.供應商' +
  234. ')' +
  235. ' INNER JOIN 類別表' +
  236. ' ON 類別表.類別_編號 = 零件表.類別' +
  237. ') ' +
  238. 'INNER JOIN 會計科目表 ' +
  239. 'ON 會計科目表.會計科目_編號 = 零件表.會計科目' +
  240. '' ;
  241. // 篩選 會計科目/類別/供應商 關鍵字
  242. if (accountingsubjects_select != '00') {
  243. AND_list.push('會計科目 = "' + accountingsubjects_select + '"')
  244. }
  245. if (category_select != '00') {
  246. AND_list.push('類別 = "' + category_select + '"')
  247. }
  248. if (serialnumber_select != '') {
  249. AND_list.push('流水號 = "' + serialnumber_select + '"')
  250. }
  251. if (supplier_select != '00') {
  252. AND_list.push('零件表.供應商 = "' + supplier_select + '"')
  253. }
  254. if (component_name_select != '00') {
  255. AND_list.push('名稱 = "' + component_name_select + '"')
  256. } else if (component_name_input != '') {
  257. AND_list.push('名稱 LIKE "%' + component_name_input + '%"')
  258. }
  259. console.log("AND_list: ", AND_list)
  260. if (AND_list != '') {
  261. sql += ' WHERE ' + AND_list.join(' AND ');
  262. }
  263. var sql_data = {
  264. "sql":sql
  265. };
  266. console.log("sql_data: ", sql_data)
  267. $.get('/sql_get', sql_data, function (res) {
  268. // console.log("res: " + res)
  269. // console.log("res.labels:" + res.labels)
  270. // console.log("res.labels[0]:" + res.labels[0])
  271. // console.log("res.labels[0][0]:" + res.labels[0][0])
  272. // console.log("res.sql_field:" + res.sql_field)
  273. // console.log("res.sql_field[0]:" + res.sql_field[0])
  274. var data = " ";
  275. if (res.labels == '') {
  276. data += '-- 無符合關鍵字之資料 --'
  277. } else {
  278. data += '<table class="table table-bordered" style="margin-top: 10px;">';
  279. data += '<tr>';
  280. for (let fields = 0; fields < res.sql_field.length; fields++) {
  281. data += '<th>' + res.sql_field[fields][0] + '</th>';
  282. }
  283. data += '</tr>';
  284. for (let items = 0; items < res.labels.length; items++) {
  285. data += '<tr>';
  286. for (let item = 0; item < res.labels[items].length; item++) {
  287. data += '<td id="' + res.labels[items][0] + '_' + item + '">' + res.labels[items][item] + '</td>';
  288. };
  289. data += '<td><input type="button" class="input-cond-add" value="修改" onclick="update_component(\'' + res.labels[items][0] + '\')"></td>';
  290. data += '<td><input type="button" class="input-cond-delete" value="刪除" onclick="delete_component(\'' + res.labels[items][0] + '\')"></td>';
  291. data += '</tr>';
  292. };
  293. data += '</table>';
  294. }
  295. var sectorSelect = document.getElementById("result");
  296. sectorSelect.innerHTML = data;
  297. }, 'json');
  298. }
  299. </script>
  300. <div id="result">
  301. <!-- <table>
  302. </table> -->
  303. </div>
  304. <!-- <table class="table table-bordered" style="margin-top: 20px;">
  305. <tr>
  306. {% for i1 in labels %}
  307. <th>{{i1}}</th>
  308. {% endfor %}
  309. </tr>
  310. {% for i in content %}
  311. <tr>
  312. <td>{{ i[0] }}</td>
  313. <td>{{ i[1] }}</td>
  314. <td>{{ i[2] }}</td>
  315. <td>{{ i[3] }}</td>
  316. <td>{{ i[4] }}</td>
  317. <td>{{ i[5] }}</td>
  318. <td>{{ i[6] }}</td>
  319. <td>{{ i[7] }}</td>
  320. </tr>
  321. {% endfor %}
  322. </table> -->
  323. </body>
  324. </html>