package com.kingdee.eas.custom.tomking;
import org.apache.log4j.Logger;
import com.kingdee.bos.*;
import com.kingdee.bos.metadata.entity.EntityViewInfo;
import com.kingdee.bos.metadata.entity.FilterInfo;
import com.kingdee.bos.metadata.entity.FilterItemInfo;
import com.kingdee.bos.metadata.entity.SelectorItemCollection;
import com.kingdee.bos.metadata.entity.SelectorItemInfo;
import com.kingdee.bos.metadata.query.util.CompareType;
import com.kingdee.bos.ui.face.UIRuleUtil;
import com.kingdee.eas.basedata.assistant.PeriodCollection;
import com.kingdee.eas.basedata.assistant.PeriodFactory;
import com.kingdee.eas.custom.tomking.util.JDBCUtils;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import java.lang.String;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
public class SynsDataFacadeControllerBean extends AbstractSynsDataFacadeControllerBean
{
private static Logger logger = Logger.getLogger("com.kingdee.eas.custom.tomking.SynsDataFacadeControllerBean");
@Override
protected void _synsData(Context ctx, String param) throws BOSException {
if(UIRuleUtil.isNull(param)){
//根据业务日期获取对应会计期间
Date date = new Date();
EntityViewInfo view=new EntityViewInfo();
FilterInfo filter=new FilterInfo();
filter.getFilterItems().add(new FilterItemInfo("beginDate",date,CompareType.LESS_EQUALS));
filter.getFilterItems().add(new FilterItemInfo("endDate",date,CompareType.GREATER_EQUALS));
filter.setMaskString("#0 and #1");
view.setFilter(filter);
PeriodCollection periodCollection=PeriodFactory.getLocalInstance(ctx).getPeriodCollection(view);
param = String.valueOf(periodCollection.get(0).getNumber());
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Connection conn = null;
PreparedStatement ps = null;
PreparedStatement delps = null;
try {
conn = JDBCUtils.getConnection();
//设置不允许自动提交数据
conn.setAutoCommit(false);
//删除表
String delSql = "delete from T_Fin_Other where PERID >= "+param;
delps = conn.prepareStatement(delSql);
delps.addBatch();
delps.executeBatch();
delps.clearBatch();
String insertSql = "INSERT INTO [T_Fin_Other]([SHOPID], [ACCOUNTID], [COMPANYNAME], [SHOPNAME], [PERID], [DECORATIONAMT], [DEBTAMT], [TRANSAMT], [OTHERAMT], [CONTRACTAMT], [INSERTTIME], [YEARID]) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
ps = conn.prepareStatement(insertSql);
String _sql = "SELECT SHOPID,ACCOUNTID,COMPANYNUMBER,COMPANYNAME,SHOPNAME,PERID,sum(DECORATIONAMT) DECORATIONAMT,sum(DEBTAMT) DEBTAMT,sum(TRANSAMT) TRANSAMT,sum(OTHERAMT) OTHERAMT,sum(CONTRACTAMT) CONTRACTAMT,YEARID\r\n" +
"FROM (select dp.FCODE SHOPID,dp.FNUMBER ACCOUNTID,gs.FNUMBER COMPANYNUMBER,gs.FNAME_l2 COMPANYNAME,dp.FNAME_l2 SHOPNAME,FZ.FPERIOD PERID, \r\n" +
"sum(case when km.FNUMBER='660105' then FZ.FDebitLocal else 0 end) DECORATIONAMT, \r\n" +
"sum(case when km.FNUMBER='660290' then FZ.FDebitLocal else 0 end) DEBTAMT, \r\n" +
"sum(case when km.FNUMBER='6601080101' or km.FNUMBER='6601310101' then FZ.FDebitLocal else 0 end) TRANSAMT, \r\n" +
"sum(case when km.FNUMBER='6601080105' or km.FNUMBER='6601310102' or km.FNUMBER='6601380102' then FZ.FDebitLocal else 0 end) OTHERAMT, \r\n" +
"sum(case when km.FNUMBER LIKE '66%' AND KM.FNAME_L2 = '报销' then FZ.FDebitLocal else 0 end) CONTRACTAMT, \r\n" +
"fz.FPERIODYEAR YEARID \r\n" +
"FROM T_GL_AssistBalance fz \r\n" +
"inner join T_BD_AssistantHG HB ON FZ.FAssistGrpID=HB.FID \r\n" +
"INNER JOIN T_BD_AccountView KM ON KM.FID=FZ.FAccountID \r\n" +
"inner join t_org_company gs on gs.fid=fz.FOrgUnitID \r\n" +
"inner JOIN T_BD_Currency BB ON BB.FID=FZ.FCurrencyID \r\n" +
"inner join T_ORG_BaseUnit dp on hb.FAdminOrgID=dp.fid \r\n" +
"where FZ.FPERIOD >= ?\r\n" +
"and dp.FCODE is not null \r\n" +
"and fz.FBalType='1' \r\n" +
"and BB.fnumber='GLC' \r\n" +
"and km.fisleaf='1' \r\n" +
"and gs.fnumber like '2%' \r\n" +
"and (km.FNUMBER='660105' or km.FNUMBER='660290' or km.FNUMBER='6601080101' or km.FNUMBER='6601310101' or km.FNUMBER='6601080105' or km.FNUMBER='6601310102' or km.FNUMBER='6601380102' OR (km.FNUMBER LIKE '66%' AND KM.FNAME_L2 = '报销')) \r\n" +
"group by dp.FNUMBER,dp.FCODE,gs.FNUMBER,gs.FNAME_l2,dp.FNAME_l2,FZ.FPERIOD,fz.FPERIODYEAR\r\n" +
"union\r\n" +
"select e.FCODE SHOPID,e.FNUMBER ACCOUNTID,c.FNUMBER COMPANYNUMBER,c.FNAME_l2 COMPANYNAME,e.FNAME_l2 SHOPNAME,d.FNUMBER PERID,0 DECORATIONAMT,sum(b.CFJTJE) DEBTAMT,0 TRANSAMT,0 OTHERAMT,0 CONTRACTAMT,d.FPERIODYEAR YEARID FROM CT_TOM_BadDebtsSign a \r\n" +
"left join CT_TOM_BadDebtsSignentry b on a.fid = b.fparentid\r\n" +
"left join T_ORG_COMPANY c on c.FID = a.cfcompanyid\r\n" +
"left join T_BD_PERIOD d on d.FID = b.cfperiodid\r\n" +
"left join T_ORG_BaseUnit e on e.FID = b.cfaccountNumberid\r\n" +
"where d.FNUMBER >= ? and e.FCODE is not null\r\n" +
"group by e.FNUMBER,e.FCODE,c.FNUMBER,c.FNAME_l2,e.FNAME_l2,d.FNUMBER,d.FPERIODYEAR) \r\n" +
"group by SHOPID,ACCOUNTID,COMPANYNUMBER,COMPANYNAME,SHOPNAME,PERID,YEARID";
IRowSet rs = DbUtil.executeQuery(ctx, _sql,new Object[]{param,param});
while(rs.next()){
ps.setObject(1, rs.getString("SHOPID"));
ps.setObject(2, rs.getString("ACCOUNTID"));
ps.setObject(3, rs.getString("COMPANYNAME"));
ps.setObject(4, rs.getString("SHOPNAME"));
ps.setObject(5, rs.getString("PERID"));
ps.setObject(6, rs.getBigDecimal("DECORATIONAMT"));
ps.setObject(7, rs.getBigDecimal("DEBTAMT"));
ps.setObject(8, rs.getBigDecimal("TRANSAMT"));
ps.setObject(9, rs.getBigDecimal("OTHERAMT"));
ps.setObject(10, rs.getBigDecimal("CONTRACTAMT"));
ps.setObject(11, sdf.format(new java.util.Date()));
ps.setObject(12, rs.getString("YEARID"));
//1."积攒"sql
ps.addBatch();
//2.执行batch
ps.executeBatch();
//3.清空batch
ps.clearBatch();
}
//提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (delps != null) {
delps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtils.closeResource(conn, ps);
}
}
}
JDBC批量插入数据
最新推荐文章于 2023-08-06 16:09:47 发布