useful-qry/z-ㅎㅎㅎ.sql

drop table if exists dbr_stock_io;
CREATE TABLE `dbr_stock_io` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `created_on` bigint(20) DEFAULT 0,
 `updated_on` bigint(20) DEFAULT 0,
 `linked_md5` varchar(32) NOT NULL COMMENT '상위연결 md5(sales_bd등 실제트랜잭션)',
 `storage_id` int(10) unsigned NOT NULL,
 `item_id` int(10) unsigned NOT NULL DEFAULT 0,
 `io_date` char(8) NOT NULL,
 `sort_no` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'deal_type에서 복사:매입-매출 역순으로 sort됨.로 되어야함)',
 `slip_no` varchar(21) NOT NULL COMMENT '수불장 순서는 io_date, sort_no, slip_no, seq_no 순서로',
 `seq_no` tinyint(4) NOT NULL,
 `slip_bd_id` int(10) unsigned NOT NULL,
 `linked_table` varchar(21) NOT NULL DEFAULT '' COMMENT 'sales-bd, purch-bd',
 `in_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '입고량',
 `out_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '출고량',
 `io_prc` decimal(20,4) NOT NULL DEFAULT 0.0000,
 `io_supply` decimal(20,4) NOT NULL DEFAULT 0.0000,
 `io_vat` decimal(20,4) NOT NULL DEFAULT 0.0000,
 `io_sum` decimal(20,4) NOT NULL DEFAULT 0.0000,
 `bad_in_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량입고',
 `bad_out_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량출고',
 `deal_name` varchar(21) NOT NULL DEFAULT '' COMMENT '거래구분명[비정규화]',
 `company_name` varchar(32) NOT NULL DEFAULT '' COMMENT '업체명[비정규화]',
 `material_cost_prc` decimal(20,4) NOT NULL DEFAULT 0.0000,
 `labor_cost_prc` decimal(20,4) DEFAULT 0.0000,
 `expense_cost_prc` decimal(20,4) DEFAULT 0.0000,
 PRIMARY KEY (`id`),
 UNIQUE KEY `linked_md5` (`linked_md5`),
 KEY `item_id` (`item_id`),
 KEY `storage_id` (`storage_id`),
 KEY `io_date` (`io_date`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
drop table if exists dbr_bal_item;
CREATE TABLE `dbr_bal_item` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `created_on` bigint(20) DEFAULT 0,
 `updated_on` bigint(20) DEFAULT 0,
 `yyyy_mm` varchar(6) NOT NULL DEFAULT '' COMMENT '원장년월(총계:999999)',
 `storage_id` int(10) unsigned NOT NULL DEFAULT 0,
 `item_id` int(11) NOT NULL DEFAULT 1 COMMENT '품목ID',
 `init_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '초기 재고',
 `begin_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '이월재고',
 `in_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '입고합계량',
 `out_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '출고합계량',
 `end_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '마감재고',
 `bad_init_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량초기재고',
 `bad_begin_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량이월재고',
 `bad_in_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량입고합계량',
 `bad_out_sum_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량출고합계량',
 `bad_end_bal_qty` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT '불량마감재고',
 `ip` varchar(21) NOT NULL DEFAULT '',
 PRIMARY KEY (`id`),
 UNIQUE KEY `month_storage_item` (`yyyy_mm`,`storage_id`,`item_id`),
 KEY `item_id` (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;