z-ㅎㅎㅎ.sql 3.2 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
  1. drop table if exists dbr_stock_io;
  2. CREATE TABLE `dbr_stock_io` (
  3. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  4. `created_on` bigint(20) DEFAULT 0,
  5. `updated_on` bigint(20) DEFAULT 0,
  6. `linked_md5` varchar(32) NOT NULL COMMENT '상위연결 md5(sales_bd등 실제트랜잭션)',
  7. `storage_id` int(10) unsigned NOT NULL,
  8. `item_id` int(10) unsigned NOT NULL DEFAULT 0,
  9. `io_date` char(8) NOT NULL,
  10. `sort_no` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'deal_type에서 복사:매입-매출 역순으로 sort됨.로 되어야함)',
  11. `slip_no` varchar(21) NOT NULL COMMENT '수불장 순서는 io_date, sort_no, slip_no, seq_no 순서로',
  12. `seq_no` tinyint(4) NOT NULL,
  13. `slip_bd_id` int(10) unsigned NOT NULL,
  14. `linked_table` varchar(21) NOT NULL DEFAULT '' COMMENT 'sales-bd, purch-bd',
  15. `in_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '입고량',
  16. `out_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '출고량',
  17. `io_prc` decimal(20,4) NOT NULL DEFAULT 0.0000,
  18. `io_supply` decimal(20,4) NOT NULL DEFAULT 0.0000,
  19. `io_vat` decimal(20,4) NOT NULL DEFAULT 0.0000,
  20. `io_sum` decimal(20,4) NOT NULL DEFAULT 0.0000,
  21. `bad_in_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량입고',
  22. `bad_out_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량출고',
  23. `deal_name` varchar(21) NOT NULL DEFAULT '' COMMENT '거래구분명[비정규화]',
  24. `company_name` varchar(32) NOT NULL DEFAULT '' COMMENT '업체명[비정규화]',
  25. `material_cost_prc` decimal(20,4) NOT NULL DEFAULT 0.0000,
  26. `labor_cost_prc` decimal(20,4) DEFAULT 0.0000,
  27. `expense_cost_prc` decimal(20,4) DEFAULT 0.0000,
  28. PRIMARY KEY (`id`),
  29. UNIQUE KEY `linked_md5` (`linked_md5`),
  30. KEY `item_id` (`item_id`),
  31. KEY `storage_id` (`storage_id`),
  32. KEY `io_date` (`io_date`)
  33. ) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  34. drop table if exists dbr_bal_item;
  35. CREATE TABLE `dbr_bal_item` (
  36. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  37. `created_on` bigint(20) DEFAULT 0,
  38. `updated_on` bigint(20) DEFAULT 0,
  39. `yyyy_mm` varchar(6) NOT NULL DEFAULT '' COMMENT '원장년월(총계:999999)',
  40. `storage_id` int(10) unsigned NOT NULL DEFAULT 0,
  41. `item_id` int(11) NOT NULL DEFAULT 1 COMMENT '품목ID',
  42. `init_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '초기 재고',
  43. `begin_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '이월재고',
  44. `in_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '입고합계량',
  45. `out_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '출고합계량',
  46. `end_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '마감재고',
  47. `bad_init_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량초기재고',
  48. `bad_begin_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량이월재고',
  49. `bad_in_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량입고합계량',
  50. `bad_out_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량출고합계량',
  51. `bad_end_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량마감재고',
  52. `ip` varchar(21) NOT NULL DEFAULT '',
  53. PRIMARY KEY (`id`),
  54. UNIQUE KEY `month_storage_item` (`yyyy_mm`,`storage_id`,`item_id`),
  55. KEY `item_id` (`item_id`)
  56. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;