connect 'jdbc:derby://localhost:1527/c:/DB/zPosDb;create=true;user=pos;password=pos';




CREATE TABLE CLASS_MST
(
    CLASS_CD char(4) NOT NULL,
    CLASS_NAME varchar(60) NOT NULL,
    RECYCLE_FLAG char(1),
    CONSTRAINT CLASS_MST_PK PRIMARY KEY(CLASS_CD)
);

CREATE TABLE GENRE_MST
(
    GENRE_CD char(4) NOT NULL,
    GENRE_NAME varchar(60) NOT NULL,
    CONSTRAINT GENRE_MST_PK PRIMARY KEY(GENRE_CD)
);

CREATE TABLE LABEL_MST
(
    LABEL_CD char(6) NOT NULL,
    LABEL_NAME varchar(60) NOT NULL,
    CONSTRAINT LABEL_MST_PK PRIMARY KEY(LABEL_CD)
);

CREATE TABLE RANK_MST
(
    CLASS_CD char(4) NOT NULL,
    RANK_CD char(1) NOT NULL,
    RANK_NAME varchar(4) NOT NULL,
    START_DAYS INTEGER NOT NULL,
    END_DAYS INTEGER NOT NULL,
    RECYCLE_BUY_PRICE BIGINT NOT NULL,
    RECYCLE_SALE_PRICE BIGINT NOT NULL,
    CONSTRAINT RANK_MST_PK PRIMARY KEY(CLASS_CD,RANK_CD)
);

CREATE TABLE SUPPLIER_MST
(
    SUPP_CD char(4) NOT NULL,
    SUPP_LONG_NAME varchar(60) NOT NULL,
    SUPP_NAME varchar(60) NOT NULL,
    CONSTRAINT SUPPLIER_MST_PK PRIMARY KEY(SUPP_CD)
);

CREATE TABLE STORE_MST
(
    STORE_CD char(3) NOT NULL,
    STORE_LONG_NAME varchar(60) NOT NULL,
    STORE_NAME varchar(60) NOT NULL,
    POS_POLLING_TIME INTEGER NOT NULL,
    POS_RECT_HEAD varchar(255) NOT NULL,
    SALES_MESSAGE varchar(100),
    POS_MONEY BIGINT NOT NULL,
    POS_VERSION varchar(8) NOT NULL,
    TAX_RATE DOUBLE NOT NULL,
    STORE_KIND char(1),
    CONSTRAINT STORE_MST_PK PRIMARY KEY(STORE_CD)
);

CREATE TABLE SET_MST
(
    PARENT_BARCODE char(13) NOT NULL,
    BARCODE char(13) NOT NULL,
    ITEM_NAME varchar(120) NOT NULL,
    ITEM_NUM INTEGER,
    PLUS_PRICE BIGINT NOT NULL,
    CONSTRAINT SET_MST_PK PRIMARY KEY(PARENT_BARCODE,BARCODE)
);

CREATE TABLE EMP_MST
(
    EMP_CD char(5) NOT NULL,
    EMP_NAME varchar(60) NOT NULL,
    EMP_KANA varchar(60),
    STORE_CD char(3),
    POS_EMP_FLAG char(1) NOT NULL,
    EMP_BARCODE varchar(13) NOT NULL,
    FUNC3 char(1) NOT NULL,
    CONSTRAINT EMP_MST_PK PRIMARY KEY(EMP_CD)
);

CREATE TABLE KIND_MST
(
    KIND_CD char(2) NOT NULL,
    KIND_NAME varchar(20) NOT NULL,
    CANCEL_ENABLE_FLAG char(1),
    SORT INTEGER NOT NULL,
    KIND_FLAG char(1) NOT NULL,
    CONSTRAINT KIND_MST_PK PRIMARY KEY(KIND_CD)
);

CREATE TABLE ITEM_MST
(
    ITEM_CD varchar(40) NOT NULL,
    SUPP_CD char(4) NOT NULL,
    STORE_CD char(3) NOT NULL,
    MAKER_CD varchar(30) NOT NULL,
    ITEM_NAME varchar(120) NOT NULL,
    ITEM_DETAIL1 varchar(120),
    ITEM_DETAIL2 varchar(120),
    LABEL_CD char(6) NOT NULL,
    CLASS_CD char(4) NOT NULL,
    GENRE_CD char(4) NOT NULL,
    ONSALE_DATE date NOT NULL,
    ITEM_NUM INTEGER,
    PRICE BIGINT NOT NULL,
    PRICE_TAX_KIND char(1) NOT NULL,
    MAKER_PRICE BIGINT NOT NULL,
    COST BIGINT NOT NULL,
    COST_TAX_KIND char(1) NOT NULL,
    BARCODE char(13) NOT NULL,
    OFFICIAL_BARCODE char(13) NOT NULL,
    STOCK_KIND char(1) NOT NULL,
    SET_FLAG char(1) NOT NULL,
    END_FLAG char(1) NOT NULL,
    USED_FLAG char(1) NOT NULL,
    RANK_CD char(1),
    STOCK_QUANTITY INTEGER,
    SUM_SALE_QUANTITY INTEGER,
    SUM_RECEIVED_QUANTITY INTEGER,
    FIRST_DATE date,
    FIRST_QUANTITY INTEGER,
    LAST_DATE date,
    LAST_QUANTITY INTEGER,
    MOVE_DATE date,
    MOVE_QUANTITY INTEGER,
    VALIDITY_FLAG char(1),
    CONSTRAINT ITEM_MST_PK PRIMARY KEY(ITEM_CD,SUPP_CD,STORE_CD)
);

CREATE TABLE SLIP
(
    SLIP_NO char(16) NOT NULL,
    STORE_CD char(3) NOT NULL,
    CREATE_DATE date NOT NULL,
    KIND_CD char(2) NOT NULL,
    DEPEND_SLIP_NO char(16),
    EMP_CD char(5) NOT NULL,
    SEND_FLAG char(1),
    CONSTRAINT SLIP_PK PRIMARY KEY(SLIP_NO)
);

CREATE TABLE JOURNAL_HEAD
(
    SLIP_NO char(16) NOT NULL,
    DETAILS_COUNT INTEGER NOT NULL,
    TOTAL_ITEM_QUANTITY INTEGER,
    TOTAL_SALE_AMOUNT BIGINT,
    TOTAL_SALE_TAX BIGINT,
    TOTAL_AMOUNT BIGINT,
    SALE_RECEIVE_AMOUNT BIGINT,
    SALE_BACK_AMOUNT BIGINT,
    CREDIT_KIND char(1),
    WEB_SALE_KIND char(1),
    CREDIT_NAME varchar(40),
    CREDIT_DIVISION INTEGER,
    CREDIT_SLIP_NO varchar(40),
    NOW_POINT INTEGER,
    ADD_POINT INTEGER,
    USE_POINT INTEGER,
    POINT_AMOUNT BIGINT,
    TICKET_NO varchar(40),
    TICKET_AMOUNT BIGINT,
    SALE_KIND char(1),
    TOTAL_DISCOUNT_AMOUNT BIGINT,
    TOTAL_COST_AMOUNT BIGINT,
    EMP_CD char(5) NOT NULL,
    CHANGED_EMP_CD char(5),
    CUSTOMER_CD char(13),
    CUSTOMER_QUANTITY INTEGER,
    CUSTOMER_HOUR char(4),
    OUT_MONEY BIGINT,
    IN_MONEY BIGINT,
    CUSTOMER_SEX char(1),
    CUSTOMER_AGE char(2),
    REPEATER char(1),
    INFO varchar(255),
    POS_VERSION varchar(8) NOT NULL,
    CONSTRAINT JOURNAL_HEAD_PK PRIMARY KEY(SLIP_NO)
);

CREATE TABLE JOURNAL_DETAIL
(
    SLIP_NO char(16) NOT NULL,
    SEQ INTEGER NOT NULL,
    ITEM_QUANTITY INTEGER,
    SALE_AMOUNT BIGINT,
    TAX BIGINT,
    AMOUNT BIGINT,
    SALE_KIND char(1),
    DISCOUNT_AMOUNT BIGINT,
    COST_AMOUNT BIGINT,
    RECYCLE_INIT_COST BIGINT,
    RECYCLE_INIT_PRICE BIGINT,
    RECYCLE_INIT_RANK_CD char(1),
    FROM_STORE_CD char(3),
    TO_STORE_CD char(3),
    ITEM_CD varchar(40),
    SUPP_CD char(4),
    MAKER_CD varchar(30),
    ITEM_NAME varchar(120),
    ITEM_DETAIL1 varchar(120),
    ITEM_DETAIL2 varchar(120),
    LABEL_CD char(6),
    CLASS_CD char(4),
    GENRE_CD char(4),
    ONSALE_DATE date,
    ITEM_NUM INTEGER,
    PRICE BIGINT,
    PRICE_TAX_KIND char(1),
    MAKER_PRICE BIGINT,
    COST BIGINT,
    COST_TAX_KIND char(1),
    BARCODE char(13),
    OFFICIAL_BARCODE char(13),
    STOCK_KIND char(1),
    SET_FLAG char(1),
    END_FLAG char(1),
    USED_FLAG char(1),
    RANK_CD char(1),
    CONSTRAINT JOURNAL_DETAIL_PK PRIMARY KEY(SLIP_NO,SEQ)
);

CREATE TABLE RECYCLE_BUY_UP
(
    CLASS_CD char(4) NOT NULL,
    START_DATE date NOT NULL,
    END_DATE date NOT NULL,
    RATE INTEGER NOT NULL,
    CONSTRAINT RECYCLE_BUY_UP_PK PRIMARY KEY(CLASS_CD)
);


-- Sample Data
INSERT INTO CLASS_MST ( CLASS_CD, CLASS_NAME, RECYCLE_FLAG ) VALUES ('1031','化粧品',null);
INSERT INTO CLASS_MST ( CLASS_CD, CLASS_NAME, RECYCLE_FLAG ) VALUES ('0002','書籍',null);
INSERT INTO CLASS_MST ( CLASS_CD, CLASS_NAME, RECYCLE_FLAG ) VALUES ('0003','雑誌',null);
INSERT INTO GENRE_MST ( GENRE_CD, GENRE_NAME ) VALUES ('1031','化粧水');
INSERT INTO GENRE_MST ( GENRE_CD, GENRE_NAME ) VALUES ('0101','書籍');
INSERT INTO GENRE_MST ( GENRE_CD, GENRE_NAME ) VALUES ('0102','雑誌');
INSERT INTO LABEL_MST ( LABEL_CD, LABEL_NAME ) VALUES ('000000','−−−');
INSERT INTO LABEL_MST ( LABEL_CD, LABEL_NAME ) VALUES ('000001','＊＊＊');
INSERT INTO SUPPLIER_MST ( SUPP_CD, SUPP_LONG_NAME, SUPP_NAME ) VALUES ('0001','＊＊＊','');
INSERT INTO SUPPLIER_MST ( SUPP_CD, SUPP_LONG_NAME, SUPP_NAME ) VALUES ('0101','書籍・雑誌','');

INSERT INTO RANK_MST ( CLASS_CD, RANK_CD, RANK_NAME, START_DAYS, END_DAYS, RECYCLE_BUY_PRICE, RECYCLE_SALE_PRICE ) VALUES ('1031','D','D',0,10000,0,0);
INSERT INTO RANK_MST ( CLASS_CD, RANK_CD, RANK_NAME, START_DAYS, END_DAYS, RECYCLE_BUY_PRICE, RECYCLE_SALE_PRICE ) VALUES ('0002','D','D',0,10000,0,0);
INSERT INTO RANK_MST ( CLASS_CD, RANK_CD, RANK_NAME, START_DAYS, END_DAYS, RECYCLE_BUY_PRICE, RECYCLE_SALE_PRICE ) VALUES ('0003','D','D',0,10000,0,0);

INSERT INTO ITEM_MST ( ITEM_CD, SUPP_CD, STORE_CD, MAKER_CD, ITEM_NAME, ITEM_DETAIL1, ITEM_DETAIL2, LABEL_CD, CLASS_CD, GENRE_CD, ONSALE_DATE, ITEM_NUM, PRICE, PRICE_TAX_KIND, MAKER_PRICE, COST, COST_TAX_KIND, BARCODE, OFFICIAL_BARCODE, STOCK_KIND, SET_FLAG, END_FLAG, USED_FLAG, RANK_CD, STOCK_QUANTITY, SUM_SALE_QUANTITY, SUM_RECEIVED_QUANTITY, FIRST_DATE, FIRST_QUANTITY, LAST_DATE, LAST_QUANTITY, MOVE_DATE, MOVE_QUANTITY, VALIDITY_FLAG ) VALUES ('1','0001','028','120135456','クリアアップウォーター n','','つるんと透明感アップ','000001','1031','1031','1988-12-01',null,2100,'0',2100,1000,'1','4571153574534','4571153574534','1','0','0','0','D',0,0,0,null,null,'2009-05-10',null,null,null,null);
INSERT INTO ITEM_MST ( ITEM_CD, SUPP_CD, STORE_CD, MAKER_CD, ITEM_NAME, ITEM_DETAIL1, ITEM_DETAIL2, LABEL_CD, CLASS_CD, GENRE_CD, ONSALE_DATE, ITEM_NUM, PRICE, PRICE_TAX_KIND, MAKER_PRICE, COST, COST_TAX_KIND, BARCODE, OFFICIAL_BARCODE, STOCK_KIND, SET_FLAG, END_FLAG, USED_FLAG, RANK_CD, STOCK_QUANTITY, SUM_SALE_QUANTITY, SUM_RECEIVED_QUANTITY, FIRST_DATE, FIRST_QUANTITY, LAST_DATE, LAST_QUANTITY, MOVE_DATE, MOVE_QUANTITY, VALIDITY_FLAG ) VALUES ('2','0001','028','120013556','ローション I','','瞬時にみずみずしさめぐる','000001','1031','1031','1988-12-01',null,2835,'0',2835,1000,'1','2017000022879','2017000022879','1','0','0','0','D',0,0,0,null,null,'2009-05-10',null,null,null,null);
INSERT INTO ITEM_MST ( ITEM_CD, SUPP_CD, STORE_CD, MAKER_CD, ITEM_NAME, ITEM_DETAIL1, ITEM_DETAIL2, LABEL_CD, CLASS_CD, GENRE_CD, ONSALE_DATE, ITEM_NUM, PRICE, PRICE_TAX_KIND, MAKER_PRICE, COST, COST_TAX_KIND, BARCODE, OFFICIAL_BARCODE, STOCK_KIND, SET_FLAG, END_FLAG, USED_FLAG, RANK_CD, STOCK_QUANTITY, SUM_SALE_QUANTITY, SUM_RECEIVED_QUANTITY, FIRST_DATE, FIRST_QUANTITY, LAST_DATE, LAST_QUANTITY, MOVE_DATE, MOVE_QUANTITY, VALIDITY_FLAG ) VALUES ('3','0001','028','140013556','ローション II','','豊潤なうるおいで満たす','000001','1031','1031','1988-12-01',null,2835,'0',2835,1000,'1','2017000098652','2017000098652','1','0','0','0','D',0,0,0,null,null,'2009-05-10',null,null,null,null);
INSERT INTO ITEM_MST ( ITEM_CD, SUPP_CD, STORE_CD, MAKER_CD, ITEM_NAME, ITEM_DETAIL1, ITEM_DETAIL2, LABEL_CD, CLASS_CD, GENRE_CD, ONSALE_DATE, ITEM_NUM, PRICE, PRICE_TAX_KIND, MAKER_PRICE, COST, COST_TAX_KIND, BARCODE, OFFICIAL_BARCODE, STOCK_KIND, SET_FLAG, END_FLAG, USED_FLAG, RANK_CD, STOCK_QUANTITY, SUM_SALE_QUANTITY, SUM_RECEIVED_QUANTITY, FIRST_DATE, FIRST_QUANTITY, LAST_DATE, LAST_QUANTITY, MOVE_DATE, MOVE_QUANTITY, VALIDITY_FLAG ) VALUES ('4','0001','028','250013556','エマルジョン I','','さらりとうるおいバランス保つ','000001','1031','1031','1988-12-01',null,2940,'0',2940,1000,'1','2017346459292','2017346459292','1','0','0','0','D',0,0,0,null,null,'2009-05-10',null,null,null,null);
INSERT INTO ITEM_MST ( ITEM_CD, SUPP_CD, STORE_CD, MAKER_CD, ITEM_NAME, ITEM_DETAIL1, ITEM_DETAIL2, LABEL_CD, CLASS_CD, GENRE_CD, ONSALE_DATE, ITEM_NUM, PRICE, PRICE_TAX_KIND, MAKER_PRICE, COST, COST_TAX_KIND, BARCODE, OFFICIAL_BARCODE, STOCK_KIND, SET_FLAG, END_FLAG, USED_FLAG, RANK_CD, STOCK_QUANTITY, SUM_SALE_QUANTITY, SUM_RECEIVED_QUANTITY, FIRST_DATE, FIRST_QUANTITY, LAST_DATE, LAST_QUANTITY, MOVE_DATE, MOVE_QUANTITY, VALIDITY_FLAG ) VALUES ('5','0001','028','353453556','エマルジョン II','','ふっくらと弾力キープする','000001','1031','1031','1988-12-01',null,2940,'0',2940,1000,'1','2017000022817','2017000022817','1','0','0','0','D',0,0,0,null,null,'2009-05-10',null,null,null,null);
INSERT INTO STORE_MST ( STORE_CD, STORE_LONG_NAME, STORE_NAME, POS_POLLING_TIME, POS_RECT_HEAD, SALES_MESSAGE, POS_MONEY, POS_VERSION, TAX_RATE, STORE_KIND ) VALUES ('001','デモ店','デモ店',10,'＜デモ版＞','',150000,'1.0.0',0.05,null);
INSERT INTO EMP_MST ( EMP_CD, EMP_NAME, EMP_KANA, STORE_CD, POS_EMP_FLAG, EMP_BARCODE, FUNC3 ) VALUES ('00001','歩巣太郎',null,null,'1','12345678','1');
INSERT INTO EMP_MST ( EMP_CD, EMP_NAME, EMP_KANA, STORE_CD, POS_EMP_FLAG, EMP_BARCODE, FUNC3 ) VALUES ('00002','歩巣太郎',null,null,'1','22222222','2');

INSERT INTO KIND_MST ( KIND_CD, KIND_NAME, CANCEL_ENABLE_FLAG, SORT, KIND_FLAG ) VALUES ('T0','担当',null,1,'0');
INSERT INTO KIND_MST ( KIND_CD, KIND_NAME, CANCEL_ENABLE_FLAG, SORT, KIND_FLAG ) VALUES ('U0','売上','1',2,'0');
INSERT INTO KIND_MST ( KIND_CD, KIND_NAME, CANCEL_ENABLE_FLAG, SORT, KIND_FLAG ) VALUES ('R0','釣銭',null,3,'0');
INSERT INTO KIND_MST ( KIND_CD, KIND_NAME, CANCEL_ENABLE_FLAG, SORT, KIND_FLAG ) VALUES ('K0','点検',null,4,'0');
INSERT INTO KIND_MST ( KIND_CD, KIND_NAME, CANCEL_ENABLE_FLAG, SORT, KIND_FLAG ) VALUES ('E0','両替',null,5,'0');