查询设计器 sql java_二开获取yigo设计器里查询集合里中的某个SQL

package com.bokesoft.lrp_v3.mid.dongming.service;

import java.math.BigDecimal;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import com.bokesoft.yigo.common.util.TypeConvertor;

import com.bokesoft.yigo.mid.base.DefaultContext;

import com.bokesoft.yigo.mid.service.IExtService;

import com.bokesoft.yigo.struct.datatable.DataTable;

import com.bokesoft.yigo.struct.document.Document;

import com.bokesoft.yigo.struct.usrpara.Paras;

public class CalOutPrintTable3 implements IExtService {

//private String update = "update Lrp_wmtx_h set OutPrint=33 where salesOrderNumber=?";

@Override

public Object doCmd(DefaultContext context, ArrayList arg1) throws Throwable {

// TODO Auto-generated method stub

Date d1 = TypeConvertor.toDate(arg1.get(0));

Date d2 = TypeConvertor.toDate(arg1.get(1));

Long SalesAdderss = TypeConvertor.toLong(arg1.get(2));

Long warehousecenterid = TypeConvertor.toLong(arg1.get(3));

Map map = createsql(context, arg1);

String query = map.get("query");

String type = map.get("type");

DataTable tt = null;

switch (type) {

case "f":

tt = context.getDBManager().execPrepareQuery(query, SalesAdderss, warehousecenterid, warehousecenterid);

break;

case "ff":

tt = context.getDBManager().execPrepareQuery(query, d1, d2, SalesAdderss, warehousecenterid,

warehousecenterid);

break;

case "fff":

tt = context.getDBManager().execPrepareQuery(query, d1, SalesAdderss, warehousecenterid, warehousecenterid);

break;

case "ffff":

tt = context.getDBManager().execPrepareQuery(query, d2, SalesAdderss, warehousecenterid, warehousecenterid);

System.out.println(query);

break;

}

Paras paras = new Paras();//类似于SetPara

paras.put("StandardCarweight", setlist(context, tt));

return caltable(context, tt);

}

private String billno(DefaultContext context,Long SalesAdderss) throws Throwable {

String SalesOrderNumbers = "";

Document doc = context.getDocument();

DataTable t1 = doc.get("DomesticOutbound_C");

t1.setFilter("shipcustomerNum=="+SalesAdderss);

t1.filter();

while (t1.next()) {

if (t1.getInt("SelectField") == 1) {

String salesOrderNumber = TypeConvertor.toString(t1.getObject("SalesOrderNumber"));

SalesOrderNumbers = SalesOrderNumbers + ",'" + salesOrderNumber + "'";

}

}

String SalesOrderNumber = SalesOrderNumbers.substring(1, SalesOrderNumbers.length());

return SalesOrderNumber;

}

private Map createsql(DefaultContext context, ArrayList arg1) throws Throwable {

String query = context.getVE().getMetaFactory().getMetaForm("DomesticOutbound").getQueryCollection()

.get("PrintTable1").getStatements().get(0).getContent();//获取查询集合里的查询SQL context这里需要好好理解下

String nos = billno(context,TypeConvertor.toLong(arg1.get(2)));

query = query.replace("salesorderNO_para", nos);

Date d1 = TypeConvertor.toDate(arg1.get(0));

Date d2 = TypeConvertor.toDate(arg1.get(1));

String Datesql = " ";

String type = "f";

if (d1 != null && d2 != null) {

Datesql = " and h.createtime between ? and ? ";

type = "ff";

}

if (d1 != null && d2 == null) {

Datesql = " and h.createtime >=? ";

type = "fff";

}

if (d1 == null && d2 != null) {

Datesql = " and h.createtime <=? ";

type = "ffff";

}

query = query.replace("createtime_paras", Datesql);

Map map = new HashMap();

System.out.println(query);

map.put("query", query);

map.put("type", type);

return map;

}

private DataTable caltable(DefaultContext context, DataTable tt) {

List list = new ArrayList();

tt.beforeFirst();

while (tt.next()) {

String taskNumber = tt.getString("TaskNumber");

String BatchNo = tt.getString("BatchNo");

if (BatchNo != null)

if (BatchNo.trim().length() != 0)

tt.setString("BatchNo", splitbatchaNo(BatchNo));

if (!tt.getString("Packing").equals("FCL")) {

if (tt.getNumeric("countqty").compareTo(BigDecimal.ZERO) == 0

&& tt.getNumeric("BoxUnitNum1").compareTo(BigDecimal.ZERO) == 0

&& tt.getNumeric("BoxNum1").compareTo(BigDecimal.ZERO) != 0)

tt.setString("Packing", "OTHER");

else {

tt.setString("BoxSeq",

taskNumber.substring(taskNumber.length() - 8, taskNumber.length() - 4) + "-" + TypeConvertor

.toInteger(taskNumber.substring(taskNumber.length() - 4, taskNumber.length())));

list.add(taskNumber);

}

}

}

System.out.println("打印出库单(更新打印状态)DataTable: "+tt);

return tt;

}

private String splitbatchaNo(String BatchNo) {

String[] nos = BatchNo.split(",");

if (nos.length > 1)

BatchNo = nos[0] + "," + nos[1];

else

BatchNo = nos[0];

return BatchNo;

}

private static BigDecimal calCarweight(List list) {

List newList = new ArrayList();

for (String cd : list) {

if (!newList.contains(cd)) {

newList.add(cd);

}

}

return new BigDecimal(newList.size());

}

private static BigDecimal setlist(DefaultContext context, DataTable tt) {

List list = new ArrayList();

tt.beforeFirst();

while (tt.next()) {

if (!tt.getString("Packing").equals("FCL") && !tt.getString("Packing").equals("OTHER"))

list.add(tt.getString("taskNumber"));

}

return calCarweight(list);

}

}