diff options
-rw-r--r-- | sql/modules/Menu.sql | 870 |
1 files changed, 870 insertions, 0 deletions
diff --git a/sql/modules/Menu.sql b/sql/modules/Menu.sql new file mode 100644 index 00000000..e1f10c76 --- /dev/null +++ b/sql/modules/Menu.sql @@ -0,0 +1,870 @@ +CREATE TABLE menu_node ( + id serial NOT NULL, + label character varying NOT NULL, + parent integer, + "position" integer NOT NULL +); + + +ALTER TABLE public.menu_node OWNER TO ledgersmb; + +-- +-- Name: menu_node_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb +-- + +SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_node', 'id'), 193, true); + + +-- +-- Data for Name: menu_node; Type: TABLE DATA; Schema: public; Owner: ledgersmb +-- + +COPY menu_node (id, label, parent, "position") FROM stdin; +0 Top-level \N 0 +1 AR 0 1 +2 Add Transaction 1 1 +3 Sales Invoice 1 2 +4 Reports 1 3 +5 Transactions 4 1 +6 Outstanding 4 2 +7 AR Aging 4 3 +9 Taxable Sales 4 4 +10 Non-Taxable 4 5 +11 Customers 1 4 +12 Add Customer 11 1 +13 Reports 11 2 +14 Search 13 1 +15 History 13 2 +16 Point of Sale 0 2 +17 Sale 16 1 +18 Open 16 2 +19 Receipts 16 3 +20 Close Till 16 4 +21 AP 0 3 +22 Add Transaction 21 1 +23 Vendor Invoice 21 2 +24 Reports 21 3 +25 Transactions 24 1 +26 Outstanding 24 2 +27 AP Aging 24 3 +28 Taxable 24 4 +29 Non-taxable 24 5 +30 Vendors 21 4 +31 Add Vendor 30 1 +32 Reports 30 2 +33 Search 32 1 +34 History 32 2 +35 Cash 0 4 +36 Receipt 35 1 +38 Payment 35 3 +37 Receipts 35 2 +39 Payments 35 4 +40 Transfer 35 5 +42 Receipts 41 1 +43 Payments 41 2 +44 Reconciliation 41 3 +41 Reports 35 7 +45 Reconciliation 35 6 +46 HR 0 5 +47 Employees 46 1 +48 Add Employee 47 1 +49 Search 47 2 +50 Order Entry 0 6 +51 Sales Order 50 1 +52 Purchase Order 50 2 +53 Reports 50 3 +54 Sales Orders 53 1 +55 Purchase Orders 53 2 +57 Sales Orders 56 1 +58 Purchase Orders 56 2 +56 Generate 50 4 +60 Consolidate 50 5 +61 Sales Orders 60 1 +62 Purchase Orders 60 2 +63 Shipping 0 7 +64 Ship 63 1 +65 Receive 63 2 +66 Transfer 63 3 +67 Quotations 0 8 +68 Quotation 67 1 +69 RFQ 67 2 +70 Reports 67 3 +71 Quotations 70 1 +72 RFQs 70 2 +73 General Journal 0 9 +74 Journal Entry 73 1 +75 Adjust Till 73 2 +76 Reports 73 3 +77 Goods and Services 0 10 +78 Add Part 77 1 +79 Add Service 77 2 +80 Add Assembly 77 3 +81 Add Overhead 77 4 +82 Add Group 77 5 +83 Add Pricegroup 77 6 +84 Stock Assembly 77 7 +85 Reports 77 8 +86 All Items 85 1 +87 Parts 85 2 +88 Requirements 85 3 +89 Services 85 4 +90 Labor 85 5 +91 Groups 85 6 +92 Pricegroups 85 7 +93 Assembly 85 8 +94 Components 85 9 +95 Translations 77 9 +96 Description 95 1 +97 Partsgroup 95 2 +98 Projects 0 11 +99 Add Project 98 1 +100 Add Timecard 98 2 +101 Generate 98 3 +102 Sales Orders 101 1 +103 Reports 98 4 +104 Search 103 1 +105 Transactions 103 2 +106 Time Cards 103 3 +107 Translations 98 5 +108 Description 107 1 +109 Reports 0 12 +110 Chart of Accounts 109 1 +111 Trial Balance 109 2 +112 Income Statement 109 3 +113 Balance Sheet 109 4 +114 Inventory Activity 109 5 +115 Recurring Transactions 0 13 +116 Batch Printing 0 14 +117 Sales Invoices 116 1 +118 Sales Orders 116 2 +119 Checks 116 3 +120 Work Orders 116 4 +121 Quotations 116 5 +122 Packing Lists 116 6 +123 Pick Lists 116 7 +124 Purchase Orders 116 8 +125 Bin Lists 116 9 +126 RFQs 116 10 +127 Time Cards 116 11 +128 System 0 15 +129 Audit Control 128 1 +130 Taxes 128 2 +131 Defaults 128 3 +132 Yearend 128 4 +133 Backup 128 5 +134 Send to File 133 1 +135 Send to Email 133 2 +136 Chart of Accounts 128 6 +137 Add Accounts 136 1 +138 List Accounts 136 2 +139 Add GIFI 136 3 +140 List GIFI 136 4 +141 Warehouses 128 7 +142 Add Warehouse 141 1 +143 List Warehouse 141 2 +144 Departments 128 8 +145 Add Department 144 1 +146 List Departments 144 2 +147 Type of Business 128 9 +148 Add Business 147 1 +149 List Businesses 147 2 +150 Language 128 10 +151 Add Language 150 1 +152 List Languages 150 2 +153 SIC 128 11 +154 Add SIC 153 1 +155 List SIC 153 2 +156 HTML Templates 128 12 +157 Income Statement 156 1 +158 Balance Sheet 156 2 +159 Invoice 156 3 +160 AR Transaction 156 4 +161 AP Transaction 156 5 +162 Packing List 156 6 +163 Pick List 156 7 +164 Sales Order 156 8 +165 Work Order 156 9 +166 Purchase Order 156 10 +167 Bin List 156 11 +168 Statement 156 12 +169 Quotation 156 13 +170 RFQ 156 14 +171 Timecard 156 15 +172 LaTeX Templates 128 13 +173 Invoice 172 1 +174 AR Transaction 172 2 +175 AP Transaction 172 3 +176 Packing List 172 4 +177 Pick List 172 5 +178 Sales Order 172 6 +179 Work Order 172 7 +180 Purchase Order 172 8 +181 Bin List 172 9 +182 Statement 172 10 +183 Check 172 11 +184 Receipt 172 12 +185 Quotation 172 13 +186 RFQ 172 14 +187 Timecard 172 15 +188 Text Templates 128 14 +189 POS Invoice 188 1 +190 Stylesheet 0 16 +191 Preferences 0 17 +192 New Window 0 18 +193 Logout 0 19 +\. + + +-- +-- Name: menu_node_parent_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace: +-- + +ALTER TABLE ONLY menu_node + ADD CONSTRAINT menu_node_parent_key UNIQUE (parent, "position"); + + +-- +-- Name: menu_node_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace: +-- + +ALTER TABLE ONLY menu_node + ADD CONSTRAINT menu_node_pkey PRIMARY KEY (id); + + +-- +-- Name: menu_node_parent_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb +-- + +ALTER TABLE ONLY menu_node + ADD CONSTRAINT menu_node_parent_fkey FOREIGN KEY (parent) REFERENCES menu_node(id); + + + +CREATE TABLE menu_attribute ( + node_id integer NOT NULL, + attribute character varying NOT NULL, + value character varying NOT NULL, + id serial NOT NULL +); + + +-- +-- Name: menu_attribute_id_seq; Type: SEQUENCE SET; Schema: public; Owner: ledgersmb +-- + +SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu_attribute', 'id'), 534, true); + + +-- +-- Data for Name: menu_attribute; Type: TABLE DATA; Schema: public; Owner: ledgersmb +-- + +COPY menu_attribute (node_id, attribute, value, id) FROM stdin; +1 menu 1 1 +2 module ar.pl 2 +2 action add 3 +3 action add 4 +3 module is.pl 5 +3 type invoice 6 +4 menu 1 7 +5 module ar.pl 8 +5 action search 9 +5 nextsub transactions 10 +6 module ar.pl 12 +6 action search 13 +6 nextsub transactions 14 +7 module rp.pl 15 +7 action report 16 +7 report ar_aging 17 +9 module rp.pl 21 +9 action report 22 +9 report tax_collected 23 +10 module rp.pl 24 +10 action report 25 +10 report nontaxable_sales 26 +11 menu 1 27 +12 module ct.pl 28 +12 action add 29 +12 db customer 30 +13 menu 1 31 +14 module ct.pl 32 +14 db customer 34 +15 module ct.pl 35 +15 action add 36 +15 db customer 37 +14 action history 33 +16 menu 1 38 +17 module ps.pl 39 +17 action add 40 +17 nextsub openinvoices 41 +18 action openinvoices 42 +18 module ps.pl 43 +19 module ps.pl 44 +19 action receipts 46 +20 module rc.pl 47 +20 action till_closing 48 +20 pos true 49 +21 menu 1 50 +22 action add 52 +22 module ap.pl 51 +23 action add 53 +23 type invoice 55 +23 module ir.pl 54 +24 menu 1 56 +25 action search 58 +25 nextsub transactions 59 +25 module ap.pl 57 +26 action search 61 +26 nextsub transactions 62 +26 module ap.pl 60 +27 module rp.pl 63 +27 action report 64 +28 module rp.pl 66 +28 action report 67 +28 report tax_collected 68 +27 report tax_paid 65 +29 module rp.pl 69 +29 action report 70 +29 report report 71 +30 menu 1 72 +31 module ct.pl 73 +31 action add 74 +31 db vendor 75 +32 menu 1 76 +33 module ct.pl 77 +33 action history 79 +33 db vendor 78 +34 module ct.pl 80 +34 action add 81 +34 db vendor 82 +35 menu 1 83 +36 module cp.pl 84 +36 action payment 85 +36 type receipt 86 +37 module cp.pl 87 +38 module cp.pl 90 +38 action payment 91 +37 type receipt 89 +37 action payments 88 +38 type check 92 +39 module cp.pl 93 +39 type check 95 +39 action payments 94 +40 module gl.pl 96 +40 action add 97 +40 transfer 1 98 +41 menu 1 99 +42 module rp.pl 100 +42 action report 101 +42 report receipts 102 +43 module rp.pl 103 +43 action report 104 +43 report payments 105 +45 module rc.pl 106 +45 action reconciliation 107 +44 module rc.pl 108 +44 action reconciliation 109 +44 report 1 110 +46 menu 1 111 +47 menu 1 112 +48 module hr.pl 113 +48 action add 114 +48 db employee 115 +49 module hr.pl 116 +49 db employee 118 +49 action search 117 +50 menu 1 119 +51 module oe.pl 120 +51 action add 121 +51 type sales_order 122 +52 module oe.pl 123 +52 action add 124 +52 type purchase_order 125 +53 menu 1 126 +54 module oe.pl 127 +54 type sales_order 129 +54 action search 128 +55 module oe.pl 130 +55 type purchase_order 132 +55 action search 131 +56 menu 1 133 +57 module oe.pl 134 +57 action search 136 +58 module oe.pl 137 +58 action search 139 +57 type generate_sales_order 135 +58 type generate_purchase_order 138 +61 module oe.pl 140 +61 action search 141 +62 module oe.pl 143 +62 action search 144 +62 type consolidate_purchase_order 145 +61 type consolidate_sales_order 142 +63 menu 1 146 +64 module oe.pl 147 +64 action search 148 +65 module oe.pl 150 +65 action search 151 +65 type consolidate_sales_order 152 +64 type receive_order 149 +66 module oe.pl 153 +66 action search_transfer 154 +67 menu 1 155 +68 module oe.pl 156 +68 action add 157 +69 module oe.pl 159 +69 action add 160 +68 type sales_quotation 158 +69 type request_quotation 161 +70 menu 1 162 +71 module oe.pl 163 +71 type sales_quotation 165 +71 action search 164 +72 module oe.pl 166 +72 action search 168 +72 type request_quotation 167 +73 menu 1 169 +74 module gl.pl 170 +74 action add 171 +75 module gl.pl 172 +75 action add_pos_adjust 174 +75 rowcount 3 175 +75 pos_adjust 1 176 +75 reference Adjusting Till: (Till) Source: (Source) 177 +75 descripton Adjusting till due to data entry error 178 +76 module gl.pl 180 +76 action search 181 +77 menu 1 182 +78 module ic.pl 183 +78 action add 184 +78 item part 185 +79 module ic.pl 186 +79 action add 187 +79 item service 188 +80 module ic.pl 189 +80 action add 190 +81 module ic.pl 192 +81 action add 193 +81 item part 194 +80 item labor 191 +82 action add 195 +82 module pe.pl 196 +83 action add 198 +83 module pe.pl 199 +83 type partsgroup 200 +82 type pricegroup 197 +84 module ic.pl 202 +84 action stock_assembly 203 +85 menu 1 204 +86 module ic.pl 205 +87 action search 206 +86 action search 207 +87 module ic.pl 208 +86 searchitems all 209 +88 module ic.pl 211 +88 action requirements 212 +89 action search 213 +89 module ic.pl 214 +89 searchitems service 215 +87 searchitems part 210 +90 action search 216 +90 module ic.pl 217 +90 searchitems labor 218 +91 module pe.pl 221 +91 type pricegroup 222 +91 action search 220 +92 module pe.pl 224 +92 type partsgroup 225 +92 action search 223 +93 action search 226 +93 module ic.pl 227 +93 searchitems assembly 228 +94 action search 229 +94 module ic.pl 230 +94 searchitems component 231 +95 menu 1 232 +96 module pe.pl 233 +96 action translation 234 +96 translation description 235 +97 module pe.pl 236 +97 action translation 237 +97 translation partsgroup 238 +98 menu 1 239 +99 module pe.pl 240 +99 action add 241 +99 type project 242 +100 module jc.pl 243 +100 action add 244 +99 project project 245 +100 project project 246 +100 type timecard 247 +101 menu 1 248 +102 module pe.pl 249 +102 action project_sales_order 250 +102 menu 1 255 +104 module pe.pl 256 +104 type project 258 +104 action search 257 +105 action report 260 +105 report projects 261 +105 module rp.pl 262 +106 module jc.pl 263 +106 action search 264 +106 type timecard 265 +106 project project 266 +107 menu 1 268 +108 module pe.pl 269 +108 action translation 270 +108 translation project 271 +109 menu 1 272 +110 module ca.pl 273 +110 action chart_of_accounts 274 +111 action report 275 +111 module rp.pl 276 +111 report trial_balance 277 +112 action report 278 +112 module rp.pl 279 +112 report income_statement 280 +113 action report 281 +113 module rp.pl 282 +113 report balance_sheet 283 +114 action report 284 +114 module rp.pl 285 +114 report inv_activity 286 +115 action recurring_transactions 287 +115 module am.pl 288 +116 menu 1 289 +119 module bp.pl 290 +119 action search 291 +119 type check 292 +119 vc vendor 293 +117 module bp.pl 294 +117 action search 295 +117 vc customer 297 +118 module bp.pl 298 +118 action search 299 +118 vc customer 300 +118 type invoice 301 +117 type sales_order 296 +120 module bp.pl 302 +120 action search 303 +120 vc customer 304 +121 module bp.pl 306 +121 action search 307 +121 vc customer 308 +122 module bp.pl 310 +122 action search 311 +122 vc customer 312 +120 type work_order 305 +121 type sales_quotation 309 +122 type packing_list 313 +123 module bp.pl 314 +123 action search 315 +123 vc customer 316 +123 type pick_list 317 +124 module bp.pl 318 +124 action search 319 +124 vc vendor 321 +124 type purchase_order 320 +125 module bp.pl 322 +125 action search 323 +125 vc vendor 325 +126 module bp.pl 326 +126 action search 327 +126 vc vendor 329 +127 module bp.pl 330 +127 action search 331 +127 type timecard 332 +125 type bin_list 324 +126 type request_quotation 328 +127 vc employee 333 +128 menu 1 334 +129 module am.pl 337 +130 module am.pl 338 +131 module am.pl 339 +129 action audit_control 340 +130 taxes audit_control 341 +131 action defaults 342 +130 action taxes 343 +132 module am.pl 346 +132 action yearend 347 +133 menu 1 348 +134 module am.pl 349 +135 module am.pl 350 +134 action backup 351 +135 action backup 352 +134 media file 353 +135 media email 354 +137 module am.pl 355 +138 module am.pl 356 +139 module am.pl 357 +140 module am.pl 358 +137 action add_account 359 +138 action list_account 360 +139 action add_gifi 361 +140 action list_gifi 362 +141 menu 1 363 +142 module am.pl 364 +143 module am.pl 365 +142 action add_warehouse 366 +143 action list_warehouse 367 +145 module am.pl 368 +146 module am.pl 369 +145 action add_department 370 +146 action list_department 371 +147 menu 1 372 +148 module am.pl 373 +149 module am.pl 374 +148 action add_business 375 +149 action list_business 376 +150 menu 1 377 +151 module am.pl 378 +152 module am.pl 379 +151 action add_language 380 +152 action list_language 381 +153 menu 1 382 +154 module am.pl 383 +155 module am.pl 384 +154 action add_sic 385 +155 action list_sic 386 +156 menu 1 387 +157 module am.pl 388 +158 module am.pl 389 +159 module am.pl 390 +160 module am.pl 391 +161 module am.pl 392 +162 module am.pl 393 +163 module am.pl 394 +164 module am.pl 395 +165 module am.pl 396 +166 module am.pl 397 +167 module am.pl 398 +168 module am.pl 399 +169 module am.pl 400 +170 module am.pl 401 +171 module am.pl 402 +157 action list_templates 403 +158 action list_templates 404 +159 action list_templates 405 +160 action list_templates 406 +161 action list_templates 407 +162 action list_templates 408 +163 action list_templates 409 +164 action list_templates 410 +165 action list_templates 411 +166 action list_templates 412 +167 action list_templates 413 +168 action list_templates 414 +169 action list_templates 415 +170 action list_templates 416 +171 action list_templates 417 +157 template income_statement 418 +158 template balance_sheet 419 +159 template invoice 420 +160 template ar_transaction 421 +161 template ap_transaction 422 +162 template packing_list 423 +163 template pick_list 424 +164 template sales_order 425 +165 template work_order 426 +166 template purchase_order 427 +167 template bin_list 428 +168 template statement 429 +169 template quotation 430 +170 template rfq 431 +171 template timecard 432 +157 format HTML 433 +158 format HTML 434 +159 format HTML 435 +160 format HTML 436 +161 format HTML 437 +162 format HTML 438 +163 format HTML 439 +164 format HTML 440 +165 format HTML 441 +166 format HTML 442 +167 format HTML 443 +168 format HTML 444 +169 format HTML 445 +170 format HTML 446 +171 format HTML 447 +172 menu 1 448 +173 action list_templates 449 +174 action list_templates 450 +175 action list_templates 451 +176 action list_templates 452 +177 action list_templates 453 +178 action list_templates 454 +179 action list_templates 455 +180 action list_templates 456 +181 action list_templates 457 +182 action list_templates 458 +183 action list_templates 459 +184 action list_templates 460 +185 action list_templates 461 +186 action list_templates 462 +187 action list_templates 463 +173 module am.pl 464 +174 module am.pl 465 +175 module am.pl 466 +176 module am.pl 467 +177 module am.pl 468 +178 module am.pl 469 +179 module am.pl 470 +180 module am.pl 471 +181 module am.pl 472 +182 module am.pl 473 +183 module am.pl 474 +184 module am.pl 475 +185 module am.pl 476 +186 module am.pl 477 +187 module am.pl 478 +173 format LATEX 479 +174 format LATEX 480 +175 format LATEX 481 +176 format LATEX 482 +177 format LATEX 483 +178 format LATEX 484 +179 format LATEX 485 +180 format LATEX 486 +181 format LATEX 487 +182 format LATEX 488 +183 format LATEX 489 +184 format LATEX 490 +185 format LATEX 491 +186 format LATEX 492 +187 format LATEX 493 +173 template invoice 506 +174 template ar_transaction 507 +175 template ap_transaction 508 +176 template packing_list 509 +177 template pick_list 510 +178 template sales_order 511 +179 template work_order 512 +180 template purchase_order 513 +181 template bin_list 514 +182 template statement 515 +185 template quotation 518 +186 template rfq 519 +187 template timecard 520 +183 template check 516 +184 template receipt 517 +188 menu 1 521 +189 module am.pl 522 +189 action list_templates 523 +189 template pos_invoice 524 +189 format TEXT 525 +190 action display_stylesheet 526 +190 module am.pl 527 +191 module am.pl 528 +191 action config 529 +193 module login.pl 532 +193 action logout 533 +193 target _top 534 +192 menu 1 530 +192 new 1 531 +\. + + +-- +-- Name: menu_attribute_id_key; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace: +-- + +ALTER TABLE ONLY menu_attribute + ADD CONSTRAINT menu_attribute_id_key UNIQUE (id); + + +-- +-- Name: menu_attribute_pkey; Type: CONSTRAINT; Schema: public; Owner: ledgersmb; Tablespace: +-- + +ALTER TABLE ONLY menu_attribute + ADD CONSTRAINT menu_attribute_pkey PRIMARY KEY (node_id, attribute); + + +-- +-- Name: menu_attribute_node_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: ledgersmb +-- + +ALTER TABLE ONLY menu_attribute + ADD CONSTRAINT menu_attribute_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id); + + +-- +-- PostgreSQL database dump complete +-- + +-- + +CREATE TABLE menu_acl ( + id serial NOT NULL, + role_name character varying, + acl_type character varying, + node_id integer, + CONSTRAINT menu_acl_acl_type_check CHECK ((((acl_type)::text = 'allow'::text) OR ((acl_type)::text = 'deny'::text))) +); + + + +ALTER TABLE ONLY menu_acl + ADD CONSTRAINT menu_acl_pkey PRIMARY KEY (id); + + +ALTER TABLE ONLY menu_acl + ADD CONSTRAINT menu_acl_node_id_fkey FOREIGN KEY (node_id) REFERENCES menu_node(id); + + +-- +-- PostgreSQL database dump complete +-- + +CREATE TYPE menu_item AS ( + position int, + id int, + level int, + label varchar, + path varchar, + args varchar[] +); + +CREATE OR REPLACE FUNCTION menu_generate() RETURNS SETOF menu_item AS +$$ +DECLARE + item menu_item; + arg menu_attribute%ROWTYPE; + +BEGIN + FOR item IN + SELECT n.position, n.id, c.level, n.label, c.path, '{}' + FROM connectby('menu_node', 'id', 'parent', 'position', '0', + 0, ',') + c(id integer, parent integer, "level" integer, + path text, list_order integer) + JOIN menu_node n USING(id) + LOOP + FOR arg IN + SELECT * + FROM menu_attribute + WHERE node_id = item.id + LOOP + item.args := item.args || + (arg.attribute || '=' || arg.value)::varchar; + END LOOP; + RETURN NEXT item; + END LOOP; +END; +$$ language plpgsql; +-- +-- PostgreSQL database dump +-- + +CREATE VIEW menu_friendly AS + SELECT t."level", t.path, t.list_order, (repeat(' '::text, (2 * t."level")) || (n.label)::text) AS label, n.id, n."position" FROM (connectby('menu_node'::text, 'id'::text, 'parent'::text, 'position'::text, '0'::text, 0, ','::text) t(id integer, parent integer, "level" integer, path text, list_order integer) JOIN menu_node n USING (id)); + + +ALTER TABLE public.menu_friendly OWNER TO ledgersmb; + +-- +-- PostgreSQL database dump complete +-- + |