From 8ce54d9163507e8b1a6759be60363551d3b131bd Mon Sep 17 00:00:00 2001 From: Xia Kaixiang Date: Mon, 29 Apr 2019 16:59:08 +0800 Subject: [PATCH] =?utf8?q?=E7=A7=91=E7=9B=AEsql?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../dlpay/framework/dao/SettleCtlDao.java | 2 +- .../dlpay/framework/dao/VouchernoCtlDao.java | 2 +- .../db/migration/V1.2__init_data.sql | 64 +++++++++++++++++++ 3 files changed, 66 insertions(+), 2 deletions(-) create mode 100644 src/main/resources/db/migration/V1.2__init_data.sql diff --git a/src/main/java/com/supwisdom/dlpay/framework/dao/SettleCtlDao.java b/src/main/java/com/supwisdom/dlpay/framework/dao/SettleCtlDao.java index 07dcd701..f6d4c02d 100644 --- a/src/main/java/com/supwisdom/dlpay/framework/dao/SettleCtlDao.java +++ b/src/main/java/com/supwisdom/dlpay/framework/dao/SettleCtlDao.java @@ -18,7 +18,7 @@ public interface SettleCtlDao extends JpaRepository { @Query(value = "from TSettlectl where booksetno=:booksetno ") TSettlectl findByBooksetnoWithLock(@Param("booksetno") Integer booksetno); - @Query(value = "update TB_SETTLECTL set PERIOD_YEAR=:peridyear,PERIOD_MONTH=:peridmonth where BOOKSETNO=1 ", nativeQuery = true) + @Query(value = "update TB_SETTLECTL set PERIODYEAR=:peridyear,PERIODMONTH=:peridmonth where BOOKSETNO=1 ", nativeQuery = true) void updateSettlePeriod(@Param("peridyear") int peridyear, @Param("peridmonth") int peridmonth); } diff --git a/src/main/java/com/supwisdom/dlpay/framework/dao/VouchernoCtlDao.java b/src/main/java/com/supwisdom/dlpay/framework/dao/VouchernoCtlDao.java index 46b4b230..fe3744dc 100644 --- a/src/main/java/com/supwisdom/dlpay/framework/dao/VouchernoCtlDao.java +++ b/src/main/java/com/supwisdom/dlpay/framework/dao/VouchernoCtlDao.java @@ -11,7 +11,7 @@ import javax.persistence.LockModeType; @Repository public interface VouchernoCtlDao extends JpaRepository { - @Query(value = "update TB_VOUCHERNOCTL set PERIOD_MONTH=:peridmonth,VOUCHERNO=:voucherno where VOUCHERTYPE=1 ", nativeQuery = true) + @Query(value = "update TB_VOUCHERNOCTL set PERIODMONTH=:peridmonth,VOUCHERNO=:voucherno where VOUCHERTYPE=1 ", nativeQuery = true) void updateVoucherno(@Param("peridmonth") int peridmonth, @Param("voucherno") int voucherno); @Lock(LockModeType.PESSIMISTIC_WRITE) diff --git a/src/main/resources/db/migration/V1.2__init_data.sql b/src/main/resources/db/migration/V1.2__init_data.sql new file mode 100644 index 00000000..37ee13ce --- /dev/null +++ b/src/main/resources/db/migration/V1.2__init_data.sql @@ -0,0 +1,64 @@ +-- 资产类 +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('1001','库存现金',1,1,null,1,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('1002','银行存款',1,1,null,1,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('1121','应收票据',1,1,null,1,0,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112101','支票',1,1,'1121',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112102','经费本',1,1,'1121',2,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('1122','应收账款',1,1,null,1,0,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112201','现金充值款',1,1,'1122',2,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112210','支付宝充值款',1,1,'1122',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112211','微信充值款',1,1,'1122',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112212','银联充值款',1,1,'1122',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112213','一卡通充值款',1,1,'1122',2,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112230','支付宝支付款',1,1,'1122',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112231','微信支付款',1,1,'1122',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112232','银联支付款',1,1,'1122',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('112233','一卡通支付款',1,1,'1122',2,1,null,'y'); + +-- 负债类 +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('2001','用户押金',2,2,null,1,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('2004','商户营业款',2,2,null,1,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('2202','应付账款',2,2,null,1,0,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('220201','个人存款',2,2,'2202',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('220211','销户退款',2,2,'2202',2,1,null,'y'); + +-- 损益类 +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('6021','手续费收入',6,2,null,1,0,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('602101','支付宝充值手续费',6,2,'6021',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('602102','微信充值手续费',6,2,'6021',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('602103','银联充值手续费',6,2,'6021',2,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('6601','销售费用',6,2,null,1,0,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('660101','折扣优惠款',6,2,'6601',2,1,null,'y'); +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('660102','积分抵扣款',6,2,'6601',2,1,null,'y'); + +Insert into TB_SUBJECT (SUBJNO,SUBJNAME,SUBJTYPE,BALFLAG,FSUBJNO,SUBJLEVEL,ENDFLAG,OPENDATE,DISPLAYFLAG) values ('6602','管理费收入',6,2,null,1,1,null,'y'); +-- -- oracle -- +-- update TB_SUBJECT set opendate = to_char(sysdate,'yyyyMMdd'); +update TB_SUBJECT set opendate = to_number(to_char(CURRENT_TIMESTAMP,'yyyymmdd'),'99999999'); +commit; + + + -- oracle -- +/* +insert into TB_SETTLECTL(BOOKSETNO,PERIODYEAR,PERIODMONTH,STATDATE,SETTLEDATE,STATUS,updtime) +values (1,to_number(to_char(sysdate,'yyyy')),to_number(to_char(sysdate,'MM')),to_number(to_char(sysdate,'yyyyMMdd')),to_number(to_char(sysdate,'yyyyMMdd')),0,to_char(sysdate,'yyyyMMddhh24miss')); + +insert into TB_VOUCHERNOCTL(VOUCHERTYPE,PERIODMONTH,VOUCHERNO) +values (1,to_number(to_char(sysdate,'MM')),0); + +commit; +*/ + +-- --pg-- +insert into TB_SETTLECTL(BOOKSETNO,PERIODYEAR,PERIODMONTH,STATDATE,SETTLEDATE,STATUS,updtime) +values (1,to_number(to_char(CURRENT_TIMESTAMP,'yyyy'),'9999'),to_number(to_char(CURRENT_TIMESTAMP,'MM'),'99'),to_number(to_char(CURRENT_TIMESTAMP,'yyyyMMdd'),'99999999'),to_number(to_char(CURRENT_TIMESTAMP,'yyyyMMdd'),'99999999'),0,to_char(CURRENT_TIMESTAMP,'yyyyMMddhh24miss')); + +insert into TB_VOUCHERNOCTL(VOUCHERTYPE,PERIODMONTH,VOUCHERNO) +values (1,to_number(to_char(CURRENT_TIMESTAMP,'MM'),'99'),0); +commit; -- 2.17.1