导出:
ClientUI
package nc.ui.jygyl.export;
import java.awt.BorderLayout;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import nc.bs.framework.common.NCLocator;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.beans.UIPanel;
import nc.ui.pub.beans.UIRefPane;
import nc.ui.pub.bill.BillCardPanel;
import nc.ui.pub.tools.BannerDialog;
import nc.vo.jygyl.export.tool.BljhExportUtils;
import nc.vo.jygyl.export.tool.ClckExportUtils;
import nc.vo.jygyl.export.tool.GeneraloutExportUtils;
import nc.vo.jygyl.export.tool.InvCountBillExportUtils;
import nc.vo.jygyl.export.tool.PoOrderExportUtils;
import nc.vo.jygyl.export.tool.QgdExportUtils;
import nc.vo.jygyl.export.tool.SCOrderExportUtils;
import nc.vo.jygyl.export.tool.SapplyBillExportUtils;
import nc.vo.jygyl.export.tool.StoreReqExportUtils;
import nc.vo.jygyl.export.tool.WtjgrkExportUtils;
import nc.vo.jygyl.export.tool.ZkExportUtils;
import nc.vo.jygyl.export.tool.m23.ArriveExportUtils;
import nc.vo.jygyl.export.tool.m45.PurchaseInExportUtils;
import nc.vo.jygyl.export.tool.m4A.GeneralInExportUtils;
import nc.vo.jygyl.tool.NullValueUtils;
import nc.vo.pubapp.AppContext;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
/**
* 业务单据导出
* @author lyh
* @date 2023-05-31
*/
public class ClientUI extends UIPanel{
private static final long serialVersionUID = -7973821104724069917L;
private BillCardPanel billCardPanel;
private UIPanel uipanel;
private JTextField filePath;
private IUAPQueryBS basedao;
public ClientUI() {
// TODO 自动生成的构造函数存根
setLayout(new BorderLayout());
add(getExportOutPanel(), java.awt.BorderLayout.CENTER);
add(getUIPanel(), java.awt.BorderLayout.NORTH);
UIRefPane refPane = (UIRefPane) getBillCardPanel().getHeadItem("jylx").getComponent();
refPane.getRefModel().setWherePart(" pk_billtypecode in ('422x','20','21','23','4455','61','47','55A3','4K','45' ,'4D','4I','4A')");
refPane.setMultiSelectedEnabled(false);
}
private BillCardPanel getBillCardPanel(){
if (billCardPanel == null) {
billCardPanel = new BillCardPanel();
billCardPanel.loadTemplet("zkgyldc", null, AppContext.getInstance().getPkUser(), null);
billCardPanel.setEnabled(true);
}
return billCardPanel;
}
private nc.ui.pub.beans.UIPanel getUIPanel() {
if (uipanel == null) {
uipanel = new UIPanel();
uipanel.setLayout(new BorderLayout());
uipanel.setBorder(BorderFactory.createTitledBorder("查询条件"));
uipanel.add(getBillCardPanel(), java.awt.BorderLayout.CENTER);
}
return uipanel;
}
/**
* 导出模板
* @return
*/
private JPanel getExportOutPanel(){
JPanel jPanel = new JPanel();
jPanel.setLayout(null);
jPanel.setSize(600,300);
jPanel.setBorder(BorderFactory.createTitledBorder("导出"));
JLabel filePathLabel = new JLabel("文件路径:");
filePathLabel.setBounds(10, 15, 100, 20);
filePath = new JTextField();
filePath.setBounds(80, 15, 350, 20);
filePath.setEditable(false);
//选择
getSelectFileButton().setBounds(440, 15, 120, 20);
//导出
getExportTxtButton().setBounds(570, 15, 80, 20);
JTextArea content = new JTextArea(50,10);
content.setText("");
content.setFont(new Font("宋体",1,10));
JScrollPane js = new JScrollPane(content);
js.setBounds(10, 60, 770, 100);
jPanel.add(filePathLabel);
jPanel.add(filePath);
jPanel.add(getSelectFileButton());
jPanel.add(getExportTxtButton());
jPanel.add(js);
jPanel.setVisible(true);
return jPanel;
}
private JButton selectFileBtn = null;
/**
* 选择文件按钮
* @return
*/
private JButton getSelectFileButton(){
if(selectFileBtn == null){
selectFileBtn = new JButton("选择路径");
selectFileBtn.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
fileSelect();
}
});
}
return selectFileBtn;
}
/**
* 选择文件
*
*/
private void fileSelect(){
FileDialog fileDlg = new FileDialog();
if (fileDlg.show()) {
// 得到导入的EXCEL文件路径
String filepath = fileDlg.getFilePath();
filePath.setText(filepath);
}
}
/**
* 导出按钮
*/
private JButton exportTxtBtn = null;
private JButton getExportTxtButton(){
if(exportTxtBtn == null){
exportTxtBtn = new JButton("导出");
exportTxtBtn.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
Thread thread = new Thread(){
@Override
public void run() {
BannerDialog dialog = new BannerDialog(getParent());
try {
dialog.setStartText("正在导出,请稍候...");
dialog.start();
export();
} catch (Exception e) {
MessageDialog.showHintDlg(null, "", "导出失败!"+e.getMessage());
}finally{
dialog.end();
}
}
};
thread.start();
}
});
}
return exportTxtBtn;
}
/**
* 导出处理
* @throws Exception
*/
public void export() throws Exception{
//校验非空
getBillCardPanel().dataNotNullValidate();
if(filePath.getText() == null || filePath.getText().equals("")){
ExceptionUtils.wrappBusinessException("请选择导出文件路径!");
}
//开始日期
String startdate = NullValueUtils.getNullStringValue(getBillCardPanel().getHeadItem("startdate").getValueObject());
//结束日期
String enddate = NullValueUtils.getNullStringValue(getBillCardPanel().getHeadItem("enddate").getValueObject());
//单据类型
String jylx = NullValueUtils.getNullStringValue(getBillCardPanel().getHeadItem("jylx").getValueObject());
String sql = " select pk_billtypecode from bd_billtype where pk_billtypeid = '"+ jylx +"' and isnull(dr,0) = 0 ";
String pk_billtypecode = NullValueUtils.getNullStringValue(getBaseDao().executeQuery(sql,new ColumnProcessor()));
if(pk_billtypecode.equals("55A3")){//55A3备料计划
BljhExportUtils.getInstance().bljhExportUtils(startdate,enddate,filePath,pk_billtypecode);
}else if("61".equals(pk_billtypecode)){
SCOrderExportUtils.getInstance().export(startdate, enddate, filePath);
}else if(pk_billtypecode.equals("422X")){//物资需求申请
StoreReqExportUtils.getNewInstance().zkgylExportUtils(jylx,pk_billtypecode,startdate, enddate,filePath);
}else if(pk_billtypecode.equals("20") ){//请购单
QgdExportUtils.getNewInstance().qgdExportUtils(jylx,startdate, enddate,filePath );
}else if(pk_billtypecode.equals("4K")){//4K转库
ZkExportUtils.getInstance().zkdExportUtils(startdate, enddate, filePath,pk_billtypecode);
}else if(pk_billtypecode.equals("4455")){ // 出库申请单
SapplyBillExportUtils.getInstance().sapplyBillExport(jylx, startdate.substring(0, 10), enddate.substring(0, 10), filePath);
}else if(pk_billtypecode.equals("4I")){ // 其它出库单
GeneraloutExportUtils.getInstance().generaloutExport(jylx, startdate.substring(0, 10), enddate.substring(0, 10), filePath);
}else if(pk_billtypecode.equals("23")){ //到货单
ArriveExportUtils.getInstance().arriveExport(startdate,enddate,pk_billtypecode,filePath);
}else if(pk_billtypecode.equals("45")){ //采购入库单
PurchaseInExportUtils.getInstance().puechaseInExport(startdate,enddate,pk_billtypecode,filePath);
}else if(pk_billtypecode.equals("4A")){ //其他入库单
GeneralInExportUtils.getInstance().generalInExport(startdate,enddate,pk_billtypecode,filePath);
}else if (pk_billtypecode.equals("21")) {//采购订单
PoOrderExportUtils.getInstance().poOrderExportUtils(startdate, pk_billtypecode, enddate, filePath);
}else if(pk_billtypecode.equals("4R-01")){//盘点单
InvCountBillExportUtils.getInstance().invcountBillExport(pk_billtypecode,startdate,enddate,filePath);
}else if(pk_billtypecode.equals("4D")){
ClckExportUtils.getInstance().clckExportUtils(pk_billtypecode,startdate, enddate, filePath);
}else if(pk_billtypecode.equals("47")){
WtjgrkExportUtils.getInstance().wtjgrkExportUtils(pk_billtypecode,startdate, enddate, filePath);
}
MessageDialog.showHintDlg(null, "", "导出成功");
}
private IUAPQueryBS getBaseDao(){
if(basedao == null){
basedao = NCLocator.getInstance().lookup(IUAPQueryBS.class);
}
return basedao;
}
}
FileDialog
package nc.ui.jygyl.export;
import javax.swing.JFileChooser;
public class FileDialog {
JFileChooser jFileChooser = new JFileChooser();
public FileDialog() {
super();
}
public boolean show(){
jFileChooser.removeChoosableFileFilter(jFileChooser.getAcceptAllFileFilter());
jFileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
return JFileChooser.APPROVE_OPTION == jFileChooser.showOpenDialog(null);
}
public String getFilePath(){
return jFileChooser.getSelectedFile().getAbsolutePath();
}
}
导入:
ClientUI
package nc.ui.jygyl.zkgylIn;
import java.awt.BorderLayout;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import javax.swing.BorderFactory;
import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import nc.bs.framework.common.NCLocator;
import nc.itf.jygyl.export.IQgdExportMaintain;
import nc.itf.jygyl.export.IWtjgrkImportMaintain;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.ColumnProcessor;
import nc.ui.pub.beans.MessageDialog;
import nc.ui.pub.beans.UIPanel;
import nc.ui.pub.beans.UIRefPane;
import nc.ui.pub.bill.BillCardPanel;
import nc.vo.jygyl.export.tool.BljhImportUtils;
import nc.vo.jygyl.export.tool.ClckImportUtils;
import nc.vo.jygyl.export.tool.GeneraloutImportUtils;
import nc.vo.jygyl.export.tool.InvCountBillExportUtils;
import nc.vo.jygyl.export.tool.PoOrderImportUtils;
import nc.vo.jygyl.export.tool.SCOrderImportUtils;
import nc.vo.jygyl.export.tool.SapplyBillImportUtils;
import nc.vo.jygyl.export.tool.StoreReqImportUtils;
import nc.vo.jygyl.export.tool.ZkdImportUtils;
import nc.vo.jygyl.export.tool.m23.ArriveImportUtils;
import nc.vo.jygyl.export.tool.m45.PurchaseInImportUtils;
import nc.vo.jygyl.export.tool.m4A.GeneralInImportUtils;
import nc.vo.jygyl.tool.NullValueUtils;
import nc.vo.pubapp.AppContext;
import nc.vo.pubapp.pattern.exception.ExceptionUtils;
import org.apache.commons.io.IOUtils;
/**
* 业务单据导入
* @author lyh
* @date 2023-05-31
*/
public class ClientUI extends UIPanel{
private static final long serialVersionUID = -7973821104724069917L;
private UIPanel uipanel;
private BillCardPanel billCardPanel;
private JTextField filePath;
public ClientUI() {
// TODO 自动生成的构造函数存根
setLayout(new BorderLayout());
add(getExportOutPanel(), java.awt.BorderLayout.CENTER);
add(getUIPanel(), java.awt.BorderLayout.NORTH);
UIRefPane refPane = (UIRefPane) getBillCardPanel().getHeadItem("jylx").getComponent();
refPane.getRefModel().setWherePart(" pk_billtypecode in ('422x','20','21','23','4455','61','47','55A3','4K','45' ,'4D','4I','4A')");
refPane.setMultiSelectedEnabled(false);
}
private BillCardPanel getBillCardPanel(){
if (billCardPanel == null) {
billCardPanel = new BillCardPanel();
billCardPanel.loadTemplet("zkgyldr", null, AppContext.getInstance().getPkUser(), null);
billCardPanel.setEnabled(true);
}
return billCardPanel;
}
private nc.ui.pub.beans.UIPanel getUIPanel() {
if (uipanel == null) {
uipanel = new UIPanel();
uipanel.setLayout(new BorderLayout());
uipanel.setBorder(BorderFactory.createTitledBorder("查询条件"));
uipanel.add(getBillCardPanel(), java.awt.BorderLayout.CENTER);
}
return uipanel;
}
/**
* 导入模板
* @return
*/
private JPanel getExportOutPanel(){
JPanel jPanel = new JPanel();
jPanel.setLayout(null);
jPanel.setSize(600,300);
jPanel.setBorder(BorderFactory.createTitledBorder("导入"));
JLabel filePathLabel = new JLabel("文件路径:");
filePathLabel.setBounds(10, 15, 100, 20);
filePath = new JTextField();
filePath.setBounds(80, 15, 350, 20);
filePath.setEditable(false);
//选择
getSelectFileButton().setBounds(440, 15, 120, 20);
getImportTxtButton().setBounds(570, 15, 80, 20);
JTextArea content = new JTextArea(50,10);
content.setText("");
content.setFont(new Font("宋体",1,10));
JScrollPane js = new JScrollPane(content);
js.setBounds(10, 60, 770, 100);
jPanel.add(filePathLabel);
jPanel.add(filePath);
jPanel.add(getSelectFileButton());
jPanel.add(getImportTxtButton());
jPanel.add(js);
jPanel.setVisible(true);
return jPanel;
}
private JButton selectFileBtn = null;
/**
* 选择文件按钮
* @return
*/
private JButton getSelectFileButton(){
if(selectFileBtn == null){
selectFileBtn = new JButton("选择路径");
selectFileBtn.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
fileSelect();
}
});
}
return selectFileBtn;
}
/**
* 选择文件
*
*/
private void fileSelect(){
FileDialog fileDlg = new FileDialog();
if (fileDlg.show()) {
// 得到导入的EXCEL文件路径
String filepath = fileDlg.getFilePath();
filePath.setText(filepath);
}
}
/**
* 导入按钮
*/
private JButton importTxtBtn = null;
private JButton getImportTxtButton(){
if(importTxtBtn == null){
importTxtBtn = new JButton("导入");
importTxtBtn.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent arg0) {
try {
if(filePath.getText() == null || filePath.getText().equals("")){
ExceptionUtils.wrappBusinessException("请选择入出文件路径!");
}
doImport(filePath.getText().toString());
} catch (Exception e) {
MessageDialog.showHintDlg(null, "", "导入失败"+e.getMessage());
return;
}
MessageDialog.showHintDlg(null, "", "导入成功,请查询");
}
});
}
return importTxtBtn;
}
/**
* 导入处理
* @throws FileNotFoundException
*/
public void doImport(String filepath) throws Exception{
//校验非空
getBillCardPanel().dataNotNullValidate();
if(filePath.getText() == null || filePath.getText().equals("")){
ExceptionUtils.wrappBusinessException("请选择导入文件路径!");
}
//单据类型
String jylx = NullValueUtils.getNullStringValue(getBillCardPanel().getHeadItem("jylx").getValueObject());
String sql = " select pk_billtypecode from bd_billtype where pk_billtypeid = '"+ jylx +"' and isnull(dr,0) = 0 ";
String pk_billtypecode = NullValueUtils.getNullStringValue(getQueryService().executeQuery(sql,new ColumnProcessor()));
if(pk_billtypecode.equals("55A3")){//备料计划
BljhImportUtils.getInstance().bljhImportUtils(filePath);
}else if("61".equals(pk_billtypecode)){//委外订单
SCOrderImportUtils.getInstance().importData(filepath);
}else if(pk_billtypecode.equals("422X")){//物资需求申请单
InputStream input = new FileInputStream(filepath);
StoreReqImportUtils.getInstance().importData(input);
}else if(pk_billtypecode.equals("4455")){ // 出库申请单
SapplyBillImportUtils.getInstance().sapplyBillImport(filepath);
}else if(pk_billtypecode.equals("4I")){ // 其它出库单
GeneraloutImportUtils.getInstance().generaloutImport(filepath);
}else if(pk_billtypecode.equals("20")){//请购单
InputStream input = new FileInputStream(filepath);
byte[] bytes = IOUtils.toByteArray(input);
NCLocator.getInstance().lookup(IQgdExportMaintain.class).importQgd(bytes);
}else if(pk_billtypecode.equals("23")){//到货单
ArriveImportUtils.getInstance().arriveImport(filepath);
}else if(pk_billtypecode.equals("45")){//采购入库单
PurchaseInImportUtils.getInstance().purchaseInImport(filepath);
}else if(pk_billtypecode.equals("4A")){//其他入库单
GeneralInImportUtils.getInstance().generalImport(filepath);
}else if(pk_billtypecode.equals("4R")){//盘点
InvCountBillExportUtils.getInstance().importData(filePath);
}else if(pk_billtypecode.equals("4K")){//4K转库
ZkdImportUtils.getInstance().zkdImportUtils(filePath);
}else if (pk_billtypecode.equals("21")) {//采购订单
PoOrderImportUtils.getInstance().poOrderImportUtils(filePath);
}else if(pk_billtypecode.equals("4D")){//材料出库
ClckImportUtils.getInstance().clckImportUtils(filePath,pk_billtypecode);
}else if(pk_billtypecode.equals("47")){//委托加工入库
InputStream input = new FileInputStream(filepath);
byte[] bytes = IOUtils.toByteArray(input);
NCLocator.getInstance().lookup(IWtjgrkImportMaintain.class).importWtjgrk(bytes);
}
}
private IUAPQueryBS queryservice;
private IUAPQueryBS getQueryService() {
if(queryservice == null) {
queryservice = NCLocator.getInstance().lookup(IUAPQueryBS.class);
}
return queryservice;
}
}
FileDialog
package nc.ui.jygyl.zkgylIn;
import java.io.File;
import javax.swing.JFileChooser;
import javax.swing.filechooser.FileFilter;
public class FileDialog {
JFileChooser jFileChooser = new JFileChooser();
public FileDialog() {
super();
}
public boolean show(){
jFileChooser.removeChoosableFileFilter(jFileChooser.getAcceptAllFileFilter());
jFileChooser.setFileFilter(new FileFilter() {
public boolean accept(File f) {
if(f.getName().endsWith(".xlsx")||f.isDirectory()){
return true;
}
return false;
}
public String getDescription() {
// TODO 自动生成方法存根
return "所有文件(*.xlsx)";
}
});
return JFileChooser.APPROVE_OPTION == jFileChooser.showOpenDialog(null);
}
public String getFilePath(){
return jFileChooser.getSelectedFile().getPath();
}
}
工具类、接口类、实现类:
InvCountBillExportUtils
package nc.vo.jygyl.export.tool;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.swing.JTextField;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.framework.common.NCLocator;
import nc.itf.jygyl.export.IInvCountBillMaintain;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.vo.ic.m4r.entity.InvCountBillVO;
import nc.vo.ic.m4r.entity.InvCountBodyVO;
import nc.vo.ic.m4r.entity.InvCountHeaderVO;
import nc.vo.jygyl.tool.NullValueUtils;
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 org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class InvCountBillExportUtils {
public static InvCountBillExportUtils instance = null;
public static InvCountBillExportUtils getInstance() {
if (instance == null) {
instance = new InvCountBillExportUtils();
}
return instance;
}
/**
* 盘点导出
* @author wf
* @date:2023-06-01
*/
public void invcountBillExport(String pk_billtypecode,String startdate,String enddate,JTextField filePath) throws BusinessException{
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook wb = new SXSSFWorkbook(xssfWorkbook, 100);
// 创建表头工作表
SXSSFSheet wbSheet = (SXSSFSheet) wb.createSheet(getExcelSheetName());
// 创建表体工作表
SXSSFSheet wbSheetbody = (SXSSFSheet) wb.createSheet(getExcelSheetBodyName());
// 设置表头默认行宽
wbSheet.setDefaultColumnWidth(10);
// 设置表体默认行宽
wbSheetbody.setDefaultColumnWidth(10);
// 标题样式(加粗,垂直居中)
XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
XSSFFont fontStyle = (XSSFFont) wb.createFont();
fontStyle.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
fontStyle.setFontHeightInPoints((short) 16); // 设置标题字体大小
cellStyle.setFont(fontStyle);
// 设置表头样式,表头居中
XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle();
// 设置单元格样式
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 设置字体
XSSFFont font = (XSSFFont) wb.createFont();
font.setFontHeightInPoints((short) 9);
style.setFont(font);
// 在表头页签第1行创建rows
SXSSFRow row = (SXSSFRow) wbSheet.createRow((int) 0);
// 在表体页签第1行创建rows
SXSSFRow bodyrow = (SXSSFRow) wbSheetbody.createRow((int) 0);
// 设置列头元素
SXSSFCell cellHead = null;
for (int i = 0; i < getExportFieldsName().length; i++) {
cellHead = (SXSSFCell) row.createCell(i);
cellHead.setCellValue(getExportFieldsName()[i]);
cellHead.setCellStyle(style);
}
for (int k = 0; k < getExportFieldsBodyName().length; k++) {
cellHead = (SXSSFCell) bodyrow.createCell(k);
cellHead.setCellValue(getExportFieldsBodyName()[k]);
cellHead.setCellStyle(style);
}
//根据开始日期和结束日期查询备料计划
InvCountBillVO[] aggvos = getService().querypdd(pk_billtypecode,startdate,enddate);
if(aggvos == null || aggvos.length == 0){
throw new BusinessException("根据开始日期和结束日期未查询盘点单信息,导出失败!");
}
List<InvCountHeaderVO> headvolist = new ArrayList<InvCountHeaderVO>();
List<InvCountBodyVO> bodyvolist = new ArrayList<InvCountBodyVO>();
for (int i = 0; i < aggvos.length; i++) {
InvCountBillVO aggvo = (InvCountBillVO) aggvos[i].clone();
InvCountBodyVO[] bodyvos = (InvCountBodyVO[]) aggvo.getChildrenVO();
headvolist.add(aggvo.getParentVO());
for (int j = 0; j < bodyvos.length; j++) {
bodyvolist.add(bodyvos[j]);
}
}
int nowRow = 1;
if(headvolist.size() > 0){
getExportHead(wbSheet,headvolist,nowRow,style);
getExportbody(wbSheetbody,bodyvolist,nowRow,style);
}
try {
FileOutputStream fout = new FileOutputStream(filePath.getText()
+ "\\盘点信息"
+ startdate.substring(0, 10) + "-" + enddate.substring(0, 10) + ".xlsx");
wb.write(fout);
fout.close();
} catch (Exception e) {
// TODO 自动生成的 catch 块
throw new BusinessException("生成excel出错:" + e.getMessage());
}
}
//写入表头信息
private void getExportHead(SXSSFSheet wbSheet,List<InvCountHeaderVO> headvolist,int nowRow,XSSFCellStyle style) {
// 写入数据
for (InvCountHeaderVO headerVO : headvolist) {
// 在第2行创建rows
SXSSFRow rows = (SXSSFRow) wbSheet.createRow(nowRow++);
// 设置列头元素
SXSSFCell cellHeads = null;
// 写入数据内容
String[] fieldsValue = new String[] {
NullValueUtils.getNullStringValue(headerVO.getCspecialhid()),//主表主键
NullValueUtils.getNullStringValue(headerVO.getPk_org()),//组织
NullValueUtils.getNullStringValue(headerVO.getVbillcode()),//单据号
NullValueUtils.getNullStringValue(headerVO.getDbilldate()),//单据日期
NullValueUtils.getNullStringValue(headerVO.getDcountdate()),//盘点日期
NullValueUtils.getNullStringValue(headerVO.getCwarehouseid()),//仓库==
NullValueUtils.getNullStringValue(headerVO.getCtrantypeid()),//盘点类型==
NullValueUtils.getNullStringValue(headerVO.getCwhsmanagerid()),//库管员==
NullValueUtils.getNullStringValue(headerVO.getCountoperator()),//盘点人==
NullValueUtils.getNullStringValue(headerVO.getVnote()),//备注
NullValueUtils.getNullStringValue(headerVO.getCreator()),//创建人
NullValueUtils.getNullStringValue(headerVO.getCreationtime()),//创建时间
NullValueUtils.getNullStringValue(headerVO.getModifier()),//最后修改人
NullValueUtils.getNullStringValue(headerVO.getModifiedtime()),//最后修改时间
NullValueUtils.getNullStringValue(headerVO.getBillmaker()),//制单人
NullValueUtils.getNullStringValue(headerVO.getDmakedate()),//制单日期
NullValueUtils.getNullStringValue(headerVO.getApprover() ),//审核
NullValueUtils.getNullStringValue(headerVO.getTaudittime() )//审核日期
};
// 开始写入数据,j为列值
for (int j = 0; j < fieldsValue.length; j++) {
cellHeads = (SXSSFCell) rows.createCell(j);
cellHeads.setCellValue(fieldsValue[j]);
cellHeads.setCellStyle(style);
}
}
}
//写入表体信息
private void getExportbody(SXSSFSheet wbSheetbody,List<InvCountBodyVO> bodyvolist,int nowRow,XSSFCellStyle style) {
// 写入数据
for (InvCountBodyVO itemVO : bodyvolist) {
// 在第2行创建rows
SXSSFRow rows = (SXSSFRow) wbSheetbody.createRow(nowRow++);
// 设置列头元素
SXSSFCell cellHeads = null;
// 写入数据内容
String[] fieldsValue = new String[] {
NullValueUtils.getNullStringValue(itemVO.getCspecialhid()),//主表主键
NullValueUtils.getNullStringValue(itemVO.getCspecialbid()),//子表主键
NullValueUtils.getNullStringValue(itemVO.getCrowno()),//行号
NullValueUtils.getNullStringValue(itemVO.getCmaterialvid()),//物料编码==
NullValueUtils.getNullStringValue(itemVO.getCunitid()),//主单位==
NullValueUtils.getNullStringValue(itemVO.getCastunitid()),//单位
NullValueUtils.getNullStringValue(itemVO.getVchangerate()),//换算率
NullValueUtils.getNullStringValue(itemVO.getVbatchcode()),//批次号
NullValueUtils.getNullStringValue(itemVO.getClocationid()),//货位==
NullValueUtils.getNullStringValue(itemVO.getNonhandastnum()),//账面数量
NullValueUtils.getNullStringValue(itemVO.getNonhandnum()),//账面主数量
NullValueUtils.getNullStringValue(itemVO.getNcountastnum()),//盘点数量
NullValueUtils.getNullStringValue(itemVO.getNcountnum()),//盘点主数量
NullValueUtils.getNullStringValue(itemVO.getNdiffastnum()),//差异数量
NullValueUtils.getNullStringValue(itemVO.getNdiffnum()),//差异主数量
NullValueUtils.getNullStringValue(itemVO.getNadjustastnum()),//调整数量
NullValueUtils.getNullStringValue(itemVO.getNadjustnum()),//调整主数量
NullValueUtils.getNullStringValue(itemVO.getNcostprice()),//调整单价
NullValueUtils.getNullStringValue(itemVO.getNcostmny()),//调整金额
NullValueUtils.getNullStringValue(itemVO.getNkeeplosrate()),//保管损失率(%)
NullValueUtils.getNullStringValue(itemVO.getNdiffrate()),//差异率
NullValueUtils.getNullStringValue(itemVO.getVreason()),//原因
NullValueUtils.getNullStringValue(itemVO.getVnotebody()),//行备注
};
// 开始写入数据,j为列值
for (int j = 0; j < fieldsValue.length; j++) {
cellHeads = (SXSSFCell) rows.createCell(j);
cellHeads.setCellValue(fieldsValue[j]);
cellHeads.setCellStyle(style);
}
}
}
private String getExcelSheetName() {
return "盘点单表头信息";
}
private String getExcelSheetBodyName() {
return "盘点单表体信息";
}
private String[] getExportFieldsName() {
return new String[] {"主表主键","组织","单据号","单据日期","盘点日期","仓库","盘点类型","库管员","盘点人","备注", "制单人","制单日期","审批人","审批日期","创建人","创建时间","最后修改人","最后修改时间" };
}
private String[] getExportFieldsBodyName() {
return new String[] {"主表主键","子表主键","行号","物料编码","主单位","单位","换算率","批次号","货位","账面数量","账面主数量","盘点数量","盘点主数量","差异数量","差异主数量","调整数量","调整主数量","调整单价","调整金额","保管损失率(%)","差异率","原因","行备注"};
}
private IInvCountBillMaintain service;
private IInvCountBillMaintain getService() {
if (service == null) {
service = NCLocator.getInstance().lookup(
IInvCountBillMaintain.class);
}
return service;
}
/**
* 盘点导入
* @author wf
* @throws BusinessException
* @date:2023-06-05
*/
@SuppressWarnings("unchecked")
public void importData(JTextField filePath) throws BusinessException {
// TODO 自动生成的方法存根
String filepath = filePath.getText().toString();
List<InvCountBillVO > voslist = new ArrayList<InvCountBillVO >();
InputStream input = null;
XSSFWorkbook wb = null;
try {
input = new FileInputStream(filepath);
wb = new XSSFWorkbook(input);
XSSFSheet xssfSheet = wb.getSheetAt(0);
XSSFSheet bodyxssfSheet = wb.getSheetAt(1);
if (xssfSheet == null) {
throw new BusinessException("解析excel出错:未读取到页签!");
}
int totalRows = xssfSheet.getPhysicalNumberOfRows();// 行数
int totalbodyRows = bodyxssfSheet.getPhysicalNumberOfRows();// 行数
InvCountBillVO aggvo = new InvCountBillVO();
List<InvCountHeaderVO> list = new ArrayList<InvCountHeaderVO>();
//表头信息-读取excel全部的表头信息 key -def10
Map<String, InvCountHeaderVO> map = new HashMap<String, InvCountHeaderVO>();
//将翻译前的vo存到headvoclonelist里
List<InvCountHeaderVO> headvoclonelist = new ArrayList<InvCountHeaderVO>();
//记录未翻译前的表体数据
Map<String, InvCountBodyVO> translatemap = new HashMap<String, InvCountBodyVO>();
//查询已导入的盘点单vdef10
String sql = " select vdef10 from ic_invcount_h where isnull(dr,0) = 0 and fbillflag = 4 ";
List<String> lists = (List<String>)getqueryservice().executeQuery(sql, new ColumnListProcessor());
for (int i = 1; i < totalRows; i++) {
if(!lists.contains(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(0)))){
InvCountHeaderVO vo = new InvCountHeaderVO();
//主表主键
vo.setVdef10(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(0).getStringCellValue()));
//组织
vo.setPk_org(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(1).getStringCellValue()));
//单据号
vo.setCwarehouseid(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(2).getStringCellValue()));
//单据日期
vo.setDbilldate(NullValueUtils.getUFDateNullValue(xssfSheet.getRow(i).getCell(3).getStringCellValue()));
//盘点日期
vo.setDcountdate(NullValueUtils.getUFDateNullValue(xssfSheet.getRow(i).getCell(4).getStringCellValue()));
//仓库
vo.setCwarehouseid(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(5).getStringCellValue()));
//盘点类型
vo.setCtrantypeid(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(6).getStringCellValue()));
//库管员
vo.setCwhsmanagerid(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(7).getStringCellValue()));
//盘点人
vo.setCountoperator(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(8).getStringCellValue()));
//备注
vo.setVnote(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(9).getStringCellValue()));
//制单人
vo.setBillmaker(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(10).getStringCellValue()));
//制单日期
SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
UFDate dmakedate = new UFDate( timeFormat.parse( NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(11))));
vo.setDmakedate(dmakedate);
//审核
vo.setApprover (NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(12).getStringCellValue()));
//审核日期
UFDate taudittime = new UFDate( timeFormat.parse( NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(13))));
vo.setTaudittime(taudittime);
//创建人
vo.setCreator(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(14).getStringCellValue()));
//创建时间
vo.setCreationtime(new UFDateTime(xssfSheet.getRow(i).getCell(15).getStringCellValue()));
//最后修改人
vo.setModifier(NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(16).getStringCellValue()));
//最后修改时间
if(!NullValueUtils.getNullStringValue(xssfSheet.getRow(i).getCell(17)).equals("")){
vo.setModifiedtime(new UFDateTime(xssfSheet.getRow(i).getCell(17).getStringCellValue()));
}
//单据状态
vo.setFbillflag(1);
// //盘点类型(单据类型)
// vo.setCtrantypeid("0001A110000000000LG6");
//盘点类型编码
vo.setVtrantypecode("4R-01");
//集团
vo.setPk_group(InvocationInfoProxy.getInstance().getGroupId());
//组织
vo.setPk_org_v(vo.getPk_org());
vo.setStatus(VOStatus.NEW);
InvCountHeaderVO headvoclone = (InvCountHeaderVO) vo.clone();
headvoclonelist.add(headvoclone);
list.add(vo);
aggvo.setParent(vo);
List<InvCountBodyVO > bvolist = new ArrayList<InvCountBodyVO >();
for (int j = 1; j < totalbodyRows; j++) {
InvCountBodyVO bvos = new InvCountBodyVO();
//主表主键
String zbzj = NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(0));
if(vo.getVdef10().equals(zbzj)){
//子表主键
bvos.setVbdef10(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(1).getStringCellValue()));
//行号
bvos.setCrowno(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(2).getStringCellValue()));
//物料编码
bvos.setCmaterialvid(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(3).getStringCellValue()));
//主单位
bvos.setCunitid(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(4).getStringCellValue()));
//单位
bvos.setCastunitid(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(5).getStringCellValue()));
//换算率
bvos.setVchangerate(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(6).getStringCellValue()));
//批次号
bvos.setVbatchcode(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(7).getStringCellValue()));
//货位
bvos.setClocationid(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(8).getStringCellValue()));
//账面数量
bvos.setNonhandastnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(9).getStringCellValue()));
//账面主数量
bvos.setNonhandnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(10).getStringCellValue()));
//盘点数量
bvos.setNcountastnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(11).getStringCellValue()));
//盘点主数量
bvos.setNcountnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(12).getStringCellValue()));
//差异数量
bvos.setNdiffastnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(13).getStringCellValue()));
//差异主数量
bvos.setNdiffnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(14).getStringCellValue()));
//调整数量
bvos.setNadjustastnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(15).getStringCellValue()));
//调整主数量
bvos.setNadjustnum(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(16).getStringCellValue()));
//调整单价
bvos.setNcostprice(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(17).getStringCellValue()));
//调整金额
bvos.setNcostmny(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(18).getStringCellValue()));
//保管损失率
bvos.setNkeeplosrate(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(19).getStringCellValue()));
//差异率
bvos.setNdiffrate(NullValueUtils.getNullUFdoubleValue(bodyxssfSheet.getRow(j).getCell(20).getStringCellValue()));
//原因
bvos.setVreason(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(21).getStringCellValue()));
//行备注
bvos.setVnotebody(NullValueUtils.getNullStringValue(bodyxssfSheet.getRow(j).getCell(22).getStringCellValue()));
bvolist.add(bvos);
//记录未翻译前的表体
translatemap.put(bvos.getVbdef10(), (InvCountBodyVO) bvos.clone());
}
}
//翻译表头
updateVO(list);
map.put(vo.getVdef10(), vo);
//校验翻译后的字段是否存在因当前系统中不存在对应信息而导致翻译失败的
checkHeadvo(map, headvoclonelist);
//翻译表体
updateBVO(bvolist.toArray(new InvCountBodyVO[0]));
//校验翻译后的字段信息
checkBodyvo(bvolist, translatemap);
aggvo.setChildren(InvCountBodyVO.class, bvolist.toArray(new InvCountBodyVO[bvolist.size()]));
voslist.add(aggvo);
}
}
if(voslist != null && voslist.size() > 0){
// 执行动作脚本
getService().executeData(voslist);
}
} catch (Exception e) {
throw new BusinessException("导入基础数据表报错:"+e.getMessage());
} finally {
try {
if (input != null) {
input.close();
}
} catch (IOException e) {
}
}
}
private void updateVO(List<InvCountHeaderVO> list) {
// TODO 自动生成的方法存根
SuperVOUtil.execFormulaWithVOs(list.toArray(new InvCountHeaderVO[0]), new String[]{
//组织
"pk_org ->getColValue(org_stockorg,pk_stockorg,code,pk_org)",
//组织版本
"pk_org_v ->getColValue(org_stockorg_v,pk_vid,code,pk_org_v)",
// 仓库
"cwarehouseid ->getColValue(bd_stordoc,pk_stordoc,code,cwarehouseid)",
// 盘点类型
"ctrantypeid ->getColValue(bd_billtype,pk_billtypeid,pk_billtypecode,ctrantypeid )",
// 库管员
"cwhsmanagerid ->getColValue(bd_psndoc,pk_psndoc,code,cwhsmanagerid )",
// 盘点人
"countoperator ->getColValue(bd_psndoc,pk_psndoc,code,countoperator)",
// 创建人
"creator ->getColValue(sm_user,cuserid,user_code,creator)",
// 最后修改人
"modifier ->getColValue(sm_user,cuserid,user_code,modifier)",
// 制单人
"billmaker ->getColValue(sm_user,cuserid,user_code,billmaker)",
// 审批人
"approver ->getColValue(sm_user,cuserid,user_code,approver)"
});
}
private void updateBVO(InvCountBodyVO[] bvos) {
// TODO 自动生成的方法存根
SuperVOUtil.execFormulaWithVOs(bvos, new String[]{
//物料编码
"cmaterialvid ->getcolvalue(bd_material,pk_material,code,cmaterialvid)",
//主单位
"cunitid ->getcolvalue(bd_measdoc,pk_measdoc,code,cunitid )",
//货位
"clocationid ->getcolvalue(bd_rack,pk_rack,code,clocationid )"
});
}
/**
* 表头翻译后字段校验
* @author wf
* @date 2023-6-8
*/
private void checkHeadvo(Map<String, InvCountHeaderVO> map,
List<InvCountHeaderVO> headvoclonelist) throws BusinessException{
List<String> errorinfoList = new ArrayList<String>();
String[] codes = new String[]{"pk_org","pk_org_v","cwarehouseid","ctrantypeid","cwhsmanagerid","countoperator","creator","modifier",
"billmaker","approver"};
String[] names = new String[]{"组织","组织版本","仓库","盘点类型","库管员","盘点人","创建人","最后修改人",
"制单人","审批人"};
for(InvCountHeaderVO cloneVO : headvoclonelist){
//翻译后
InvCountHeaderVO vo = map.get(cloneVO.getVdef10());
StringBuffer errorinfo = new StringBuffer();
errorinfo.append("单据" + vo.getVdef10() + " 导入检验失败:");
for (int i = 0; i < codes.length; i++) {
String code = codes[i];
String name = names[i];
//翻译后数据
String voData = NullValueUtils.getNullStringValue(vo.getAttributeValue(code));
//翻译前数据
String cloneVOData = NullValueUtils.getNullStringValue(cloneVO.getAttributeValue(code));
if(!"".equals(cloneVOData) && "".equals(voData)){
errorinfo.append("[" + name + "翻译失败,编码:" + cloneVOData + "]");
}
}
if(!errorinfo.toString().equals("单据" + vo.getVdef10() + " 导入检验失败:")){
errorinfoList.add(errorinfo.toString());
}
}
if(errorinfoList.size() > 0){
throw new BusinessException("导入失败:" + StringUtils.join(errorinfoList.toArray(new String[0]), ","));
}
}
/**
* 表体翻译后字段校验
* 表体信息list
* 导入后的表体map<外系统主键,外系统导入后未翻译过的表体信息>
* @author wf
* @date 2023-6-8
*/
private void checkBodyvo(List<InvCountBodyVO> bvolist, Map<String,InvCountBodyVO> bodyMap) throws BusinessException{
List<String> errorinfoList = new ArrayList<String>();
String[] codes = new String[]{"cmaterialvid","cunitid","clocationid"};
String[] names = new String[]{"物料编码","主单位","货位"};
for(InvCountBodyVO bvo : bvolist){
//翻译前数据
InvCountBodyVO cloneVO = bodyMap.get(NullValueUtils.getNullStringValue(bvo.getVbdef10()));
StringBuffer errorinfo = new StringBuffer();
errorinfo.append("单据子表" + bvo.getVbdef10() + " 导入检验失败:");
for (int i = 0; i < codes.length; i++) {
String code = codes[i];
String name = names[i];
//翻译后数据
String bvoData = NullValueUtils.getNullStringValue(bvo.getAttributeValue(code));
//翻译前数据
String cloneVOData = NullValueUtils.getNullStringValue(cloneVO.getAttributeValue(code));
if(!"".equals(cloneVOData) && "".equals(bvoData)){
errorinfo.append("[" + name + "翻译失败,编码:" + cloneVOData + "]");
}
}
if(!errorinfo.toString().equals("单据子表" + bvo.getVbdef10() + " 导入检验失败:")){
errorinfoList.add(errorinfo.toString());
}
}
if(errorinfoList.size() > 0){
throw new BusinessException("导入失败!" + StringUtils.join(errorinfoList.toArray(new String[0]), ","));
}
}
private IUAPQueryBS queryservice;
private IUAPQueryBS getqueryservice() {
if(queryservice == null) {
queryservice = NCLocator.getInstance().lookup(IUAPQueryBS.class);
}
return queryservice;
}
}
IInvCountBillMaintain
package nc.itf.jygyl.export;
import java.util.List;
import nc.vo.ic.m4r.entity.InvCountBillVO;
import nc.vo.pub.BusinessException;
/**
* 盘点单接口类
* @author wf
* @date:2023-06-01
*/
public interface IInvCountBillMaintain {
/**
* 根据开始日期和结束日期查询盘点导出
* @return
*/
public InvCountBillVO[] querypdd(String pk_billtypecode,String startdate,String enddate) throws BusinessException;
/**
* 执行动作脚本
*/
public void executeData(List<InvCountBillVO> voslist) throws BusinessException ;
}
InvCountBillImpl
package nc.impl.jygyl.export;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import nc.bs.dao.BaseDAO;
import nc.bs.framework.common.InvocationInfoProxy;
import nc.bs.framework.common.NCLocator;
import nc.impl.pubapp.pattern.data.bill.BillQuery;
import nc.impl.pubapp.pattern.database.DataAccessUtils;
import nc.itf.jygyl.export.IInvCountBillMaintain;
import nc.itf.uap.pf.IplatFormEntry;
import nc.jdbc.framework.processor.ColumnListProcessor;
import nc.vo.ic.m4r.entity.InvCountBillVO;
import nc.vo.ic.m4r.entity.InvCountBodyVO;
import nc.vo.ic.m4r.entity.InvCountHeaderVO;
import nc.vo.ic.pub.sql.SqlUtil;
import nc.vo.jygyl.export.tool.base.BaseCallUtils;
import nc.vo.pub.AggregatedValueObject;
import nc.vo.pub.BusinessException;
import nc.vo.pub.JavaType;
import nc.vo.pub.SuperVOUtil;
import nc.vo.pubapp.pattern.pub.SqlBuilder;
/**
* 盘点单导出
* @author wf
* @date:2023-06-01
*/
public class InvCountBillImpl implements IInvCountBillMaintain{
@SuppressWarnings("unchecked")
@Override
public InvCountBillVO[] querypdd(String pk_billtypecode, String startdate, String enddate)
throws BusinessException {
// TODO 自动生成的方法存根
// EJB 就是将需要执行制定任务的类,不放到客户端上,而是打包放到一个服务器上,用C/S 形式的软件客户端对服务器上的"类"进行调用。
String curDsName = InvocationInfoProxy.getInstance().getUserDataSource();
InvocationInfoProxy.getInstance().setUserDataSource(BaseCallUtils.getInstances().getEnvInfo("PMDsName"));
InvCountBillVO[] aggvos = null;
//根据开始日期结束日期查询备料计划主键
SqlBuilder sqlBuilder = new SqlBuilder();
sqlBuilder.append(" select cspecialhid ");
sqlBuilder.append(" from ic_invcount_h ");
sqlBuilder.append(" where creationtime >= '"+ startdate.substring(0, 10) + " 00:00:00" +"'");
sqlBuilder.append(" and creationtime <= '"+ startdate.substring(0, 10) + " 23:59:59" +"'");
sqlBuilder.append(" and vtrantypecode='"+pk_billtypecode+"' ");
sqlBuilder.append(" and isnull(dr, 0) = 0 ");
sqlBuilder.append(" and fbillflag = 4 ");
List<String> list = (List<String>) getBaseDao().executeQuery(sqlBuilder.toString(), new ColumnListProcessor());
if(list != null && list.size() > 0){
BillQuery<InvCountBillVO> billQuery = new BillQuery<InvCountBillVO>(InvCountBillVO.class);
aggvos = billQuery.query(list.toArray(new String[0]));
//存储所有的主表信息
Map<String,InvCountHeaderVO> headMap = new HashMap<String,InvCountHeaderVO>();
//存储所有的子表信息
Map<String,InvCountBodyVO> bodyMap = new HashMap<String,InvCountBodyVO>();
for (int i = 0; i < aggvos.length; i++) {
InvCountBodyVO[] bodyvos = (InvCountBodyVO[]) aggvos[i].getChildrenVO();
//put(主键,值)(该主键下所存放的所有值,一对多关系)
headMap.put(aggvos[i].getPrimaryKey(), (InvCountHeaderVO) aggvos[i].getParentVO());
if(bodyvos == null || bodyvos.length == 0){
continue;
}
for (int j = 0; j < bodyvos.length; j++) {
InvCountBodyVO invCountBodyVO = bodyvos[j];
list.add(invCountBodyVO.getCspecialbid());
bodyMap.put(invCountBodyVO.getCspecialbid(), invCountBodyVO);
}
}
//表头执行翻译
SuperVOUtil
.execFormulaWithVOs(
headMap.values().toArray(new InvCountHeaderVO[0]),
new String[] {
//组织
"pk_org ->getColValue(org_stockorg,code,pk_stockorg,pk_org)",
// 仓库
"cwarehouseid ->getColValue(bd_stordoc,code,pk_stordoc,cwarehouseid)",
// 盘点类型
"ctrantypeid ->getColValue(bd_billtype,pk_billtypecode,pk_billtypeid,ctrantypeid )",
// 库管员
"cwhsmanagerid ->getColValue(bd_psndoc,code,pk_psndoc,cwhsmanagerid )",
// 盘点人
"countoperator ->getColValue(bd_psndoc,code,pk_psndoc,countoperator)",
// 创建人
"creator->getColValue(sm_user,user_code,cuserid,creator)",
// 最后修改人
"modifier->getColValue(sm_user,user_code,cuserid,modifier)",
// 制单人
"billmaker->getColValue(sm_user,user_code,cuserid,billmaker)",
// 审批人
"approver->getColValue(sm_user,user_code,cuserid,approver)"
});
//表体执行翻译
SuperVOUtil.execFormulaWithVOs(bodyMap.values().toArray(new InvCountBodyVO[0]), new String[]{
//物料编码
"cmaterialvid->getcolvalue(bd_material,code,pk_material,cmaterialvid)",
//主单位
"cunitid ->getcolvalue(bd_measdoc,code,pk_measdoc ,cunitid )",
//货位
"clocationid ->getcolvalue(bd_rack,code,pk_rack,clocationid )"
});
}
InvocationInfoProxy.getInstance().setUserDataSource(curDsName);
return aggvos;
}
/**
* 执行动作脚本
*/
@Override
public void executeData(List<InvCountBillVO> voslist) throws BusinessException {
List<List<Object>> updateList = new ArrayList<List<Object>>();
for (int i = 0; i < voslist.size(); i++) {
InvCountBillVO aggvos = voslist.get(i);
//完成后将信息修改为导入信息上的时间
List<Object> updateValue = new ArrayList<Object>();
// 审批人
updateValue.add(aggvos.getParentVO().getApprover());
//审批时间
updateValue.add(aggvos.getParentVO().getTaudittime());
// 创建人
updateValue.add(aggvos.getParentVO().getCreator());
// 创建时间
updateValue.add(aggvos.getParentVO().getCreationtime());
//制单人
updateValue.add(aggvos.getParentVO().getBillmaker());
//制单时间
updateValue.add(aggvos.getParentVO().getDmakedate());
//最后修改人
updateValue.add(aggvos.getParentVO().getModifier());
//最后修改时间
updateValue.add(aggvos.getParentVO().getModifiedtime());
//原单据主键
updateValue.add(aggvos.getParentVO().getVdef10());
updateList.add(updateValue);
//执行保存
InvCountBillVO saveaggvo = (InvCountBillVO) doActionResult(aggvos, "WRITE", "4R");
//执行审批
doAction(saveaggvo, "APPROVE", "4R");
}
//执行更新
String sql = SqlUtil.getUpdateSql("ic_invcount_h",
new String[] { "creator", "creationtime", "modifier", "modifiedtime", "billmaker", "dmakedate", "approver", "taudittime" },
new String[] {
"?",
"?",
"?",
"?",
"?",
"?",
"?",
"?"
}, new String[] { "vdef10" }, null, null);
new DataAccessUtils().update(sql,
new JavaType[] { JavaType.String, JavaType.UFDateTime, JavaType.String, JavaType.UFDateTime, JavaType.String, JavaType.UFDate, JavaType.String, JavaType.UFDate, JavaType.String }, updateList);
}
/**
* 执行动作脚本
*
* @param vo
* @param actionCode
* @param billtype
* @throws BusinessException
*/
protected void doAction(AggregatedValueObject aggvo, String actionCode,
String billtype) throws BusinessException {
IplatFormEntry platform = NCLocator.getInstance().lookup(
IplatFormEntry.class);
platform.processAction(actionCode, billtype, null, aggvo, null, null);
}
/**
* 执行动作脚本
*
* @param vo
* @param actionCode
* @param billtype
* @throws BusinessException
*/
protected AggregatedValueObject doActionResult(AggregatedValueObject aggvo,
String actionCode, String billtype) throws BusinessException {
IplatFormEntry platform = NCLocator.getInstance().lookup(
IplatFormEntry.class);
AggregatedValueObject[] resultvos = (AggregatedValueObject[]) platform
.processAction(actionCode, billtype, null, aggvo, null, null);
return resultvos[0];
}
private BaseDAO basedao;
private BaseDAO getBaseDao(){
if(basedao == null){
basedao = new BaseDAO();
}
return basedao;
}
}