📄 pldrhandler.java
字号:
rs.close();
}
// 保管员
sql = " select k_xh from "
+ tableName
+ " where k_xh not in ("
+ " select k_xh from "
+ tableName
+ " a,gd_yhxxb b where b.k_dwbh=a.k_dwbh and a.k_bgy=b.k_name ) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]卡片的单位不存在此保管员\n";
}
rs.close();
// 管理员
sql = " select k_xh from "
+ tableName
+ " where k_xh not in ("
+ " select k_xh from "
+ tableName
+ " a,gd_yhxxb b where b.k_dwbh=a.k_dwbh and a.k_gly=b.k_name ) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]卡片的单位不存在此管理员\n";
}
rs.close();
// 区域编码： 对于非空的区域编码进行校验060817
// 取系统配置参数，是否必填
boolean isMustQyCode = SysPath.isMustQyCode();
if (isMustQyCode) {
sql = " select k_xh from " + tableName
+ " where k_qybm not in ("
+ " select a.k_qybm from gd_qybmb a," + tableName
+ " b where a.k_dwbh=b.k_dwbh) or k_qybm is null ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片区域编码不存在或不能为空\n";
}
rs.close();
} else {
sql = " select k_xh from "
+ tableName
+ " where nvl(k_qybm,'jstrd') <> 'jstrd' and k_qybm not in ("
+ " select a.k_qybm from gd_qybmb a," + tableName
+ " b where a.k_dwbh=b.k_dwbh)";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片区域编码不存在\n";
}
rs.close();
}
// 增加类型
sql = " select k_xh from " + tableName + " where k_xh not in ("
+ " select k_xh from " + tableName
+ " a,gd_zjlxb b where a.k_zjlx=b.k_zjlx) ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片增加类型不存在\n";
}
rs.close();
// 判断父卡片是否存在子卡片
sql = "select distinct(k_xh) from " + tableName + " where k_fz=1";
rs = db.executeQuery(sql);
while (rs.next()) {
long fkpxh = rs.getLong(1);
sql = "select count(*) from " + tableName + " where k_fkpxh="
+ fkpxh;
rs2 = stmt.executeQuery(sql);
if (rs2.next()) {
if (rs2.getInt(1) == 0) {
message += "父卡片序号" + fkpxh + "不存在子卡片";
}
}
rs2.close();
}
rs.close();
// 判断父卡片编号是否存在
sql = " select distinct(k_fkpbh) from " + tableName
+ " where k_fkpbh!=0 ";
rs = db.executeQuery(sql);
while (rs.next()) {
long fkpbh = rs.getLong(1);
sql = " select count(*) from " + kpbName + " where k_kpbh ="
+ fkpbh + " and k_fz=1";
rs2 = stmt.executeQuery(sql);
if (rs2.next()) {
if (rs2.getInt(1) == 0) {
message += "父卡片编号" + fkpbh + "不存在或非父卡片";
}
}
rs2.close();
}
rs.close();
// 判断父卡片序号是否存在于Excel表中
sql = " select distinct(k_fkpxh) from " + tableName
+ " where k_fkpxh!=0 ";
rs = db.executeQuery(sql);
while (rs.next()) {
long fkpxh = rs.getLong(1);
sql = " select count(*) from " + tableName + " where k_xh ="
+ fkpxh + " and k_fz=1 ";
rs2 = stmt.executeQuery(sql);
if (rs2.next()) {
if (rs2.getInt(1) == 0) {
message += "父卡片序号" + fkpxh + "不存在于Excel中或非父卡片";
}
}
rs2.close();
}
rs.close();
if (message.equals("")) {
// 更新折旧年限、尚可使用年限、全部耐用年限、ABC分类'
/*
* update HOU_FU_FEI a set
* (a.dept,a.BUSINESS_EARNING_TYPE)=(select
* b.dept,b.business_earning_type from BUSINESS_DEPT_DIV b where
* a.BUSINESS_TYPE=b.BUSINESS_TYPE and rownum<2) where
* a.mymonth=v_mymonth and exists (select 1 from
* BUSINESS_DEPT_DIV b where a.BUSINESS_TYPE=b.BUSINESS_TYPE);
*/
sql = " select distinct(k_zcml) from " + tableName;
rs = db.executeQuery(sql);
while (rs.next()) {
String zcml = rs.getString(1);
sql = " update "
+ tableName
+ " a set (a.k_zjnx,a.k_sksynx,"
+ " a.k_qbnynx,a.k_abc)=(select b.k_zjnx,"
+ "b.k_zjnx*12,b.k_zjnx,b.k_abc from "
+ " gd_zcmlb b "
+ " where a.k_zcml=b.k_zcml and a.k_zcml='"
+ zcml
+ "') where exists"
+ " (select 1 from gd_zcmlb b where a.k_zcml=b.k_zcml and a.k_zcml="
+ zcml + ")";
stmt.executeUpdate(sql);
}
rs.close();
// 折旧年限
sql = " select k_xh from "
+ tableName
+ " where k_zjnx=0 and k_fz<>1 and k_zcml not like '15%' ";
rs = db.executeQuery(sql);
while (rs.next()) {
message += "序号[" + rs.getLong(1) + "]的卡片根据资产目录获得的折旧年限为0\n";
}
rs.close();
if (message.equals("")) {
// 计算月折旧额、月折旧率(资产类型：固定资产)
sql = " update "
+ tableName
+ " set k_yzje=round(k_yz*(1-k_czl)/k_sksynx,2),k_yzjl=round((1-k_czl)/k_sksynx,12) "
+ " where k_fz!=1 and k_zclx=1 ";
db.executeUpdate(sql);
// 计算月折旧额、月折旧率、残值率(资产类型：无形资产、长期待摊费用)
sql = " update "
+ tableName
+ " set k_yzje=round(k_yz/k_sksynx,2),k_yzjl=round(1/k_sksynx,12),k_czl=0 "
+ " where k_fz!=1 and (k_zclx=5 or k_zclx=6) ";
db.executeUpdate(sql);
// 计算父卡片信息
sql = " select distinct(k_fkpxh) from " + tableName
+ " where k_fz=2 and k_fkpxh!=0 ";
rs = db.executeQuery(sql);
while (rs.next()) {
long k_fkpxh = rs.getLong(1);
sql = " select sum(k_yz) as yz,sum(k_jz) as jz,sum(k_jcz) as jcz,"
+ " sum(k_yzje) as yzje,sum(k_ljzj) as ljzj, sum(k_ljjz) as ljjz from "
+ tableName
+ " where k_fkpxh="
+ k_fkpxh
+ " and k_fz=2 ";
rs2 = stmt.executeQuery(sql);
rs2.next();
double yz = rs2.getDouble("yz");
double jz = rs2.getDouble("jz");
double jcz = rs2.getDouble("jcz");
double yzje = rs2.getDouble("yzje");
double ljzj = rs2.getDouble("ljzj");
double ljjz = rs2.getDouble("ljjz");
rs2.close();
sql = " update " + tableName + " set k_yz =?,k_jz=?,"
+ " k_jcz=?,k_yzje=?," + " k_ljzj=?,k_ljjz=? "
+ " where k_xh= ? and k_fz = 1";
pstmt = db.getConnection().prepareStatement(sql);
pstmt.setDouble(1, yz);
pstmt.setDouble(2, jz);
pstmt.setDouble(3, jcz);
pstmt.setDouble(4, yzje);
pstmt.setDouble(5, ljzj);
pstmt.setDouble(6, ljjz);
pstmt.setLong(7, k_fkpxh);
pstmt.executeUpdate();
pstmt.close();
}
rs.close();
}
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("单位" + k_dwbh + "批量导入建立临时表发生错误，原因：" + message
+ e.getMessage());
throw new Exception("建立临时表发生错误");
} finally {
try {
rs.close();
} catch (Exception e) {
}
try {
rs2.close();
} catch (Exception e) {
}
try {
stmt.close();
} catch (Exception e) {
}
try {
pstmt.close();
} catch (Exception e) {
}
}
return tableName;
}
// 删除临时表
private void delTable(String tableName, String k_dwbh, DBConnection db)
throws Exception {
try {
stmt = db.getConnection().createStatement();
String sql = "TRUNCATE TABLE " + tableName.toUpperCase();
stmt.executeUpdate(sql);
sql = "drop table " + tableName.toUpperCase();
stmt.executeUpdate(sql);
} catch (Exception e) {
System.out.println("单位" + k_dwbh + "批量导入删除临时表" + tableName
+ "发生错误，原因：" + e.getMessage());
} finally {
try {
stmt.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
// 系统自动产生的字段
private void Auto(PLDRKP rowkp, Date jkrq) {
rowkp.setJkrq(jkrq);
rowkp.setJcz(rowkp.getYz() * rowkp.getCzl());
rowkp.setJz(rowkp.getYz() - rowkp.getLjzj() - rowkp.getLjjz());
}
private String getErrorMessage(int row, int col, String message) {
return "第" + row + "行" + "第" + col + "列" + message + "\n";
}
// 验证Excel文件的信息
private ArrayList validate(String path, String k_dwbh, Date jkrq,
DBConnection db, Token token) throws Exception {
ArrayList arr = new ArrayList();
String k_ywrq = GdzcDate.getYwrq(k_dwbh);
if (k_ywrq == null || k_ywrq.equals(""))
message += "查询业务日期出错!";
Workbook workbook = null;
try {
workbook = Workbook.getWorkbook(new File(path));
} catch (Exception e) {
message += "打开文件失败\n";
return null;
}
Sheet sheet = null;
try {
sheet = workbook.getSheet(startSheet);
} catch (Exception e) {
message += "文件选择错误，请重新选择 \n";
return null;
}
if (!sheet.getCell(0, startRow - 2).getContents().equals("序号")) {
message += "文件选择错误，请重新选择 \n";
return null;
}
rows = sheet.getRows();
while (sheet.getCell(0, rows - 1).getContents() == null
|| sheet.getCell(0, rows - 1).getContents().equals("")) {
rows--;
}
PLDRValidateBean validate = new PLDRValidateBean(db);
// InitialDepreciation ID = null;
for (int row = startRow; row <= rows; row++) {
PLDRKP rowkp = new PLDRKP();
for (int col = 1; col <= columns; col++) {
Cell cell = sheet.getCell(col - 1, row - 1);
String tempStr = cell.getContents().trim();
switch (col) {
case 1: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "序号不能为空");
break;
}
if (cell.getType() != CellType.NUMBER) {
message += getErrorMessage(row, col, "数据类型不匹配");
break;
}
NumberCell nc = (NumberCell) cell;
try {
rowkp.setXh((long) nc.getValue());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 2: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "单位编号不能为空");
break;
}
rowkp.setDwbh(tempStr);
try {
validate.validateDwbh(rowkp.getDwbh());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 3: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "资产目录不能为空");
break;
}
rowkp.setZcml(tempStr);
try {
validate.validateZcml(rowkp.getZcml());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 4: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "资产分类不能为空");
break;
}
if (cell.getType() != CellType.NUMBER) {
message += getErrorMessage(row, col, "数据类型不匹配");
break;
}
NumberCell nc = (NumberCell) cell;
try {
rowkp.setZcfl((int) nc.getValue());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 5: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "科目代号不能为空");
break;
}
rowkp.setKmdh(tempStr);
break;
}
case 6: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "资产归属不能为空");
break;
}
if (cell.getType() != CellType.NUMBER) {
message += getErrorMessage(row, col, "数据类型不匹配");
break;
}
NumberCell nc = (NumberCell) cell;
try {
rowkp.setZcgs((int) nc.getValue());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 7: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "专业归属不能为空");
break;
}
if (cell.getType() != CellType.NUMBER) {
message += getErrorMessage(row, col, "数据类型不匹配");
break;
}
NumberCell nc = (NumberCell) cell;
try {
rowkp.setZygs((int) nc.getValue());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 8: {
if (tempStr.equals("")) {
rowkp.setZyft("");
break;
}
rowkp.setZyft(tempStr);
break;
}
case 9: {
if (tempStr.equals("")) {
rowkp.setCpft("");
break;
}
rowkp.setCpft(tempStr);
break;
}
case 10: {
if (tempStr.equals("")) {
rowkp.setCpsx("N999999");
break;
}
rowkp.setCpsx(tempStr);
try {
validate.validateWysx(tempStr);
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 11: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "暂估否不能为空");
break;
}
if (cell.getType() != CellType.NUMBER) {
message += getErrorMessage(row, col, "数据类型不匹配");
break;
}
NumberCell nc = (NumberCell) cell;
try {
rowkp.setZgf((int) nc.getValue());
validate.validateZgf(rowkp.getZgf());
} catch (Exception e) {
message += getErrorMessage(row, col, e.getMessage());
}
break;
}
case 12: {
if (tempStr.equals("")) {
message += getErrorMessage(row, col, "父子不能为空");
break;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -