从中间表取数更新TW销售合同登记数据(含表体)

package nc.bs.backgroundtasks;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import nc.bs.dao.BaseDAO;
import nc.bs.framework.common.NCLocator;
import nc.bs.pub.pa.PreAlertObject;
import nc.bs.pub.taskcenter.BgWorkingContext;
import nc.bs.pub.taskcenter.IBackgroundWorkPlugin;
import nc.impl.pubapp.pattern.data.bill.BillQuery;
import nc.itf.jych.IXshtdjMaintain;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.vo.jych.oavo.OaXshtBVO;
import nc.vo.jych.oavo.OaXshtVO;
import nc.vo.jych.util.SqlBuilder;
import nc.vo.jych.xshtdj.AggXshtdjVO;
import nc.vo.jych.xshtdj.XshtdjBVO;
import nc.vo.jych.xshtdj.XshtdjVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.SuperVOUtil;
import nc.vo.pub.VOStatus;
import nc.vo.pub.lang.UFDate;
import nc.vo.pub.lang.UFDateTime;
import nc.vo.wgbfb.tool.NullValueUtils;
/**
 * @desc : 从中间表取数更新TW销售合同登记数据
 * @author hanh
 * @date : 2023-10-09
 */

public class OAXshtdjPlugin implements IBackgroundWorkPlugin{

	@Override
	public PreAlertObject executeTask(BgWorkingContext arg0) throws BusinessException {
		//抽取中间表数据
		generateZjXsht();
		return null;
	}
	
	//抽取中间表数据
	@SuppressWarnings("unchecked")
	private void generateZjXsht() throws BusinessException {
		//查询中间表中未执行的主表数据
		String sql = " select * from jych_zj_xsht where nvl(dr,0) = 0 and nvl(iszx,'N') != 'Y'  ";
		//volist存放中间表中未执行的主表数据
		List<OaXshtVO> volist = (List<OaXshtVO>) getDao().executeQuery(sql.toString(), new BeanListProcessor(OaXshtVO.class));
		if(volist == null || volist.size() < 1){
			return;
		}
		
		//idlist存放id+sjly
		List<String> idlist = new ArrayList<String>(); 
		//zjpklist存放中间表主表主键
		List<String> zjpklist = new ArrayList<String>();
		for (OaXshtVO vo : volist) {//给idlist和zjpklist赋值
			String id = vo.getId()+vo.getSjly();
			idlist.add(id);
			zjpklist.add(vo.getPk_xsht());
		}
		
		//根据中间表id+sjly，查询nc系统中已存在的主表主键
		SqlBuilder pksql = new SqlBuilder(); 
		pksql.append(" select pk_xshtdj from jych_xshtdj where nvl(dr,0) = 0 and ");
		pksql.append(" def1||sjly ", idlist.toArray(new String[0]));
		List<String> pklist = (List<String>) getDao().executeQuery(pksql.toString(), new ColumnListProcessor());
		//Map 将已存在的AggVO放入vomap
		Map<String, AggXshtdjVO> vomap = new HashMap<String, AggXshtdjVO>();
		if(pklist != null && pklist.size() > 0){
			//根据已存在的主表主键获取已存在的AggVO
			BillQuery<AggXshtdjVO> billQuery = new BillQuery<AggXshtdjVO>(AggXshtdjVO.class);
			AggXshtdjVO[] aggvos = billQuery.query(pklist.toArray(new String[0]));
			for (AggXshtdjVO aggvo : aggvos) {//将aggvo对应的id和aggvo捆绑
				String wxtbs = aggvo.getParentVO().getDef1();
				String sjly = aggvo.getParentVO().getSjly();
				String id = wxtbs + sjly;
				vomap.put(id, aggvo);
			}
		}
		
		SqlBuilder sqlnameb = new SqlBuilder(); //查询中间表中主表所对应的子表数据
		sqlnameb.append(" select * from jych_zj_xsht_b where nvl(dr,0) = 0 and ");
		sqlnameb.append(" mainid||sjly ", idlist.toArray(new String[0]));
		//bvolist存放已存在主表对应的子表数据
		List<OaXshtBVO> bvolist = (List<OaXshtBVO>) getDao().executeQuery(sqlnameb.toString(), new BeanListProcessor(OaXshtBVO.class));
		//Map 已存在主表对应的子表数据放入bvomap
		Map<String, List<OaXshtBVO>> bvomap = new HashMap<String, List<OaXshtBVO>>();
		if(bvolist != null && bvolist.size() > 0){
			for (OaXshtBVO bvo : bvolist) {//将bvo对应的mainid和bvo捆绑
				String mainid = bvo.getMainid()+bvo.getSjly();
				if(bvomap.containsKey(mainid)){//如果bvomap存在该mainid，直接将该bvo和该mainid捆绑
					List<OaXshtBVO> list = bvomap.get(mainid);
					list.add(bvo);
					bvomap.put(mainid, list);
				} else {//如果bvomap不存在该mainid，则新建一个list，然后再将该bvo和该mainid捆绑
					List<OaXshtBVO> list = new ArrayList<OaXshtBVO>();
					list.add(bvo);
					bvomap.put(mainid, list);
				}
			}
		}
		
		//翻译OaXshtVO
		updateHeadVO(volist.toArray(new OaXshtVO[0])); 
		//insertlist存放执行插入的aggvo
		List<AggXshtdjVO> insertlist = new ArrayList<AggXshtdjVO>();
		//updatelist存放执行更新后的aggvo
		List<AggXshtdjVO> updatelist = new ArrayList<AggXshtdjVO>();
		//orgupdatelist存放执行更新前的aggvo	
		List<AggXshtdjVO> orgupdatelist = new ArrayList<AggXshtdjVO>();		
		for (OaXshtVO vo : volist) {//封装insertlist、updatelist、orgupdatelist
			if(vomap != null && vomap.size() > 0 && vomap.containsKey(vo.getId()+vo.getSjly())){ //修改
				AggXshtdjVO aggvo = vomap.get(vo.getId()+vo.getSjly());
				AggXshtdjVO clone = (AggXshtdjVO) aggvo.clone();
				XshtdjVO headvo = clone.getParentVO();
				headvo.setStatus(VOStatus.UPDATED);
				setHeadvo(headvo, vo);
				updatelist.add(clone);
				orgupdatelist.add(aggvo);
			} else { //新增
				AggXshtdjVO aggvo = new AggXshtdjVO();
				XshtdjVO headvo = new XshtdjVO();
				headvo.setStatus(VOStatus.NEW);
				headvo.setPk_billtype("XSHT");
				headvo.setTranstypecode("XSHT");
				headvo.setPk_org(vo.getPk_org());
				headvo.setPk_org_v(vo.getPk_org_v());
				headvo.setPk_group(vo.getPk_group());
				headvo.setPk_transtype(vo.getPk_transtype());
				headvo.setDbilldate(new UFDate());
				headvo.setVbillstatus(-1);
				setHeadvo(headvo, vo);
				if(bvomap != null && bvomap.size() > 0 && bvomap.containsKey(vo.getId()+vo.getSjly())){
					XshtdjBVO[] bvos = setbodyvo(bvomap.get(vo.getId()+vo.getSjly()));
					aggvo.setChildrenVO(bvos);
				}
				aggvo.setParentVO(headvo);
				insertlist.add(aggvo);
			}
		}
		if(insertlist != null && insertlist.size() > 0){//调新增接口
			AggXshtdjVO[] insertvos = getService().insert(insertlist.toArray(new AggXshtdjVO[0]), insertlist.toArray(new AggXshtdjVO[0]));
			List<AggXshtdjVO> insertvolist = new ArrayList<AggXshtdjVO>();
			for (AggXshtdjVO insertvo : insertvos) {
				AggXshtdjVO clone = (AggXshtdjVO) insertvo.clone();
				XshtdjVO vo = clone.getParentVO();
				vo.setVbillstatus(1);vo.setApprovedate(new UFDateTime());vo.setApprover(vo.getCreator());
				insertvolist.add(clone);
			}
			getService().approve(insertvolist.toArray(new AggXshtdjVO[0]), insertvos);
		}
		if(updatelist != null && updatelist.size() > 0){//调更新接口
			getService().update(updatelist.toArray(new AggXshtdjVO[0]), orgupdatelist.toArray(new AggXshtdjVO[0]));
		}
		
		SqlBuilder updatesql = new SqlBuilder(); //更新中间表中已执行数据的是否执行标识
		updatesql.append(" update jych_zj_xsht set iszx = 'Y' where ");
		updatesql.append(" pk_xsht ", zjpklist.toArray(new String[0]));
		getDao().executeUpdate(updatesql.toString());
	}
	
	private void updateHeadVO(OaXshtVO[] headvos) {
		SuperVOUtil.execFormulaWithVOs(headvos, new String[] {
						
						// 组织
						"pk_org->getcolvalue2(org_orgs,pk_org,code,\"05\",islastversion,\"Y\")",
						// 组织版本
						"pk_org_v->getcolvalue(org_orgs,pk_vid,pk_org,pk_org)",
						// 集团
						"pk_group->getcolvalue(org_group,pk_group,code,\"01\")",
						//申请人
						"lastname->getColValue2(bd_psndoc,pk_psndoc,name,lastname,dr,0)",
						//申请部门
						"departmentname->getColValue2(org_dept,pk_dept,name,departmentname,dr,0)",
						//甲方
						"jfdwbm->getcolvalue2(bd_customer,pk_customer,name,jfdwbm,dr,0)",
						//乙方
						"partyb->getcolvalue2(bd_supplier,pk_supplier,name,\"北京城建智控科技股份有限公司\",dr,0)",
						//币种
						"bz->getcolvalue2(bd_currtype,pk_currtype,name,bz,dr,0)",
						//营销项目
						"xmbh->getColValue2(zkch_marketpro,pk_marketpro,marketprocode,xmbh,dr,0)",
				});
	}
	
	public void setHeadvo(XshtdjVO headvo, OaXshtVO vo){
		headvo.setDef1(vo.getId());
		//项目编号
		headvo.setPk_marketpro(vo.getXmbh());
		//合同编码
		headvo.setHtcode(vo.getHtbh());
		//合同名称
		headvo.setHtname(vo.getHtmc());
		//合同类型
		headvo.setHttype(vo.getHtlx());
		//投标类型
		headvo.setTbtype(vo.getTblx());
		//甲方
		headvo.setPartya(vo.getJfdwbm());
		headvo.setPartyb(vo.getPartyb());
		//合同金额（含税）
		headvo.setNmny(vo.getHtjkhs());
		//合同金额（不含税）
		headvo.setNtmny(vo.getHtjkbhs());
		//税率（%）
		headvo.setTaxrate(NullValueUtils.getNullStringValue(vo.getSl()));
		//税额
		headvo.setPaid(vo.getSe());
		//累计开票金额
		//				headvo.setLjkpmny(rs.getString(1));
		//累计回款金额
		//				headvo.setLjhkmny(rs.getString(1));
		//累计结算金额
		//				headvo.setLjnjsmny(rs.getString(1));
		//申请日期
		headvo.setSqdate(vo.getSqrq());
		//签约日期
		headvo.setQydate(vo.getQyrq());
		//归档日期
		headvo.setGddate(vo.getSjyyrq());
		//所属机构
		headvo.setSsjg(vo.getSzjg());
		//申请人
		headvo.setSqr(vo.getLastname());
		//申请部门
		headvo.setSqdept(vo.getDepartmentname());
		//是否关联交易方
		headvo.setIsgljyf(vo.getSfgljyf());
		//是否包含自研产品
		headvo.setIszycp(vo.getSfbhzycp());
		//合同开工日期
		headvo.setHtkgdate(vo.getHtkgrq());
		//合同竣工日期
		headvo.setHtjgdate(vo.getHtjgrq());
		//合同工期
		headvo.setHtgq(vo.getHtgq());
		//履约地点
		headvo.setLyadress(vo.getLydd());
		//币种
		headvo.setBz(vo.getBz());
		//汇率
		headvo.setHl(vo.getHl());
		//合同份数
		headvo.setHtfs(vo.getHtfs());
		//是否为公司标椎合同模板
		headvo.setIsgsbzmb(vo.getSfwgsbzhtmb());
		//合同章
		headvo.setHtz(vo.getHtz());
		//公章
		headvo.setGz(vo.getGz());
		//法人章
		headvo.setFrz(vo.getFrz());
		//总经理手签章
		headvo.setZjlsqz(vo.getZjlsqz());
		//福州分公司公章
		headvo.setFzfgsgz(vo.getFzfgsgz());
		//总经理印章
		headvo.setZjlyz(vo.getZjlyz());
		//备注说明
		headvo.setMemo(vo.getBzsm());
		headvo.setSjly(vo.getSjly());
	}
	
	public XshtdjBVO[] setbodyvo(List<OaXshtBVO> bvolist){
		XshtdjBVO[] bvos = new XshtdjBVO[bvolist.size()];
		for (int i = 0; i < bvos.length; i++) {
			OaXshtBVO bvo = bvolist.get(i);
			bvos[i] = new XshtdjBVO();
			bvos[i].setRowno(i+1+"0");
			//税率
			bvos[i].setTaxrate(bvo.getSl());
			//含税金额
			bvos[i].setNmny(bvo.getHsje());
			//不含税金额
			bvos[i].setNtmny(bvo.getBhsje());
			//合计税额
			bvos[i].setPaid(bvo.getSey());
			//合同事项
			bvos[i].setHtsx(bvo.getHtsx());
			bvos[i].setStatus(VOStatus.NEW);
		}
		return bvos;
	}
	
	private IXshtdjMaintain service;
	private IXshtdjMaintain getService() {
		if (service == null) {
			service = NCLocator.getInstance().lookup(IXshtdjMaintain.class);
		}
		return service;
	}
	
	private BaseDAO dao;
	private BaseDAO getDao(){
		if(dao == null){
			dao = new BaseDAO();
		}
		return dao;
	}

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305