debugInfoToTxt

public class FinanceSalarySearchToCashier extends AbstractFinanceSalarySearchToCashier
{
private static final Logger logger = CoreUIObject.getLogger(FinanceSalarySearchToCashier.class);
private String whereSql;


/**
* output class constructor
*/
public FinanceSalarySearchToCashier() throws Exception
{
super();
}

@Override
protected ICoreBase getBizInterface() throws Exception {
return null;
}

@Override
protected String getEditUIName() {
return null;
}

@Override
public void onLoad() throws Exception {
super.onLoad();
buildAdminOrgUnitTree(false);
kDSearch.setEnabled(true);
kDTreeView1.setEnabled(true);
kDTree1.setEnabled(true);

kDTable1.setScrollStateVertical(KDTStyleConstants.SCROLL_STATE_SHOW);

//初始化次数
kDTime.setSelectedIndex(10);

//初始化日期
DateFormat df = new SimpleDateFormat("yyyyMM");
Date now = new Date();
kDEndDate.setValue(Integer.parseInt(df.format(now)));
kDStartDate.setValue(Integer.parseInt(df.format(now)));


this.kDTree1.addTreeSelectionListener(new TreeSelectionListener() {
public void valueChanged(TreeSelectionEvent e) {
setWhereSql();
try {
fillTable();
} catch (Exception e1) {
e1.printStackTrace();
}
}
});

kDCheckChild.addItemListener(new ItemListener(){

public void itemStateChanged(ItemEvent e) {
setWhereSql();
try {
fillTable();
} catch (Exception e1) {
e1.printStackTrace();
}
}

});

kDStartDate.addChangeListener(new ChangeListener(){

public void stateChanged(ChangeEvent e) {
int value = kDStartDate.getIntegerVlaue();
if(value%100 == 0){
value = (value/100-1)*100+12;
kDStartDate.setValue(value);
}
if(value%100 >= 13){
value = (value/100+1)*100+1;
kDStartDate.setValue(value);
}

}

});
kDEndDate.addChangeListener(new ChangeListener(){

public void stateChanged(ChangeEvent e) {
int value = kDEndDate.getIntegerVlaue();
if(value%100 == 0){
value = (value/100-1)*100+12;
kDEndDate.setValue(value);
}
if(value%100 >= 13){
value = (value/100+1)*100+1;
kDEndDate.setValue(value);
}

}

});

//设置表头排序
KDTSortManager sm = new KDTSortManager(kDTable1);
sm.setSortAuto(true);
sm.setClickCount(1);
for (int i = 0, count = kDTable1.getColumnCount(); i < count; i++) {
kDTable1.getColumn(i).setSortable(true);//setSortable for sort data
}
}


/**
* data filter method
*/
protected void setWhereSql() {
//?whereSql do what
whereSql = "";
//人员条件查询
String number = null;
if(kDPerson.getValue() != null){
number = ((PersonInfo)kDPerson.getValue()).getNumber();//取用户交互 值
whereSql += "AND PERSON.FNUMBER = '"+number+"' ";//判断数据库的职员值和输入的职员值后过滤
}

//薪酬方案过滤
String cmpScheme = null;
if(CmpScheme.getValue() != null){
cmpScheme = ((CmpSchemeInfo) CmpScheme.getValue()).getName();//取用户交互 值
whereSql += "AND CMP.FNAME_L2 = '"+cmpScheme+"' ";//
}

//薪酬方案次数过滤
int time = 1;
if(kDTime.getSelectedIndex() != 10){
time = kDTime.getSelectedIndex() + 1;//取用户交互 值
whereSql += "AND SCHM.ftime = '"+time+"' ";
}

//部门树查询
DefaultKingdeeTreeNode selectNode = null;
TreePath path = kDTree1.getSelectionPath();
if(path != null){
selectNode = (DefaultKingdeeTreeNode) path.getLastPathComponent();
}
if(selectNode != null && (selectNode.getUserObject() instanceof AdminOrgUnitInfo)){
AdminOrgUnitInfo adminInfo = (AdminOrgUnitInfo) selectNode.getUserObject();

if(kDCheckChild.isSelected()){
whereSql += "AND ( ADMIN.flongnumber like '" + adminInfo.getLongNumber()
+ "!%' or ADMIN.flongnumber='" + adminInfo.getLongNumber() + "' )";
}else{
whereSql += "AND ADMIN.FID ='" + adminInfo.getId().toString() + "'";
}
}//部门树查询 hardPoint
}

private String mainSql() throws EASBizException, BOSException {
String start ;
if(kDStartDate.getValue()==null)
{
start = "197901";

}else{
start = kDStartDate.getValue().toString();

}

String end ;
if(kDStartDate.getValue()==null)
{
end = "197901";

}else{
end = kDEndDate.getValue().toString();

}

StringBuilder sql = new StringBuilder();
sql.setLength(0);


sql.append("/*dialect*/").append("\r\n");
sql.append("Select").append("\r\n");
sql.append("CMP.FNAME_L2 AS CMPNAME, --薪酬方案名称").append("\r\n");

sql.append("PERIOD.fnumber AS PERIOD, --年月").append("\r\n");
sql.append("ADMIN.FNAME_L2 AS ADMIN, -- 部门名称").append("\r\n");
sql.append("PERSON.FNUMBER AS PERSONNUM, -- 职员代码").append("\r\n");
sql.append("PERSON.FNAME_L2 AS PERSONNAME, -- 职员姓名 ").append("\r\n");
sql.append("SCHM.FFSAL235 AS FFSAL235 , -- 银行转账（含暂缓） ").append("\r\n");
sql.append("SCHM.ftime AS time, --薪酬方案次数").append("\r\n");
sql.append("SCHM.FFSAL259 AS FFSAL259 , -- 是否发放上月止暂缓 ").append("\r\n");
sql.append("SCHM.FFSAL233 AS FFSAL233 , -- 截止上月薪资未发放").append("\r\n");
sql.append("SCHM.FFSAL234 AS FFSAL234 , -- 截止本月薪资未发放 ").append("\r\n");
sql.append("SCHM.FFSAL236 AS FFSAL236 , -- 发放上月止暂缓工资 ").append("\r\n");
sql.append("SCHM.FFSAL256 AS FFSAL256 , -- 是否暂缓 ").append("\r\n");
sql.append("SCHM.FFSAL252 AS FFSAL252 , -- 实领工资（含暂缓）").append("\r\n");
sql.append("SCHM.FFSAL251 AS FFSAL251 , -- 现金 ").append("\r\n");
sql.append("SCHM.FFSAL254 AS FFSAL254 , -- 领现金 ").append("\r\n");
sql.append("PCMP.FACCOUNTONENAME_L2 AS FPER054 , -- 银行名称1 ").append("\r\n");
sql.append("PCMP.FACCOUNTONE AS FPER025 -- 个人账号1 ").append("\r\n");

if(getSql()==null || "".equals(getSql())){
return null;
}

sql.append("FROM ("+getSql()+") SCHM").append("\r\n");
sql.append("LEFT OUTER JOIN t_hr_cmpscheme CMP ").append("\r\n");
sql.append("ON CMP.FHISTABLENAME = SCHM.TABLENAME ").append("\r\n");
sql.append("OR CMP.FHORTABLENAME = SCHM.TABLENAME").append("\r\n");
sql.append("LEFT OUTER JOIN T_BD_PERSON PERSON ").append("\r\n");
sql.append("ON PERSON.FID = SCHM.FPERSONID").append("\r\n");
sql.append("LEFT OUTER JOIN T_HR_PersonPosition PERSONPOSITION ").append("\r\n");
sql.append("ON PERSON.FID = PERSONPOSITION.FPERSONID ").append("\r\n");
sql.append("LEFT OUTER JOIN T_ORG_Admin ADMIN ").append("\r\n");
sql.append("ON ADMIN.FID = PERSONPOSITION.FPERSONDEP ").append("\r\n");
sql.append("LEFT OUTER JOIN T_HR_PersonCmp PCMP ").append("\r\n");
sql.append("ON PCMP.FPERSONID = PERSON.FID ").append("\r\n");
sql.append("LEFT OUTER JOIN T_HR_CMPPERIOD PERIOD ").append("\r\n");
sql.append("ON PERIOD.FID = SCHM.FPERIOD").append("\r\n");
sql.append("WHERE SCHM.FFSAL256 IS NULL AND PERIOD.FNUMBER >= '"+start+"' AND PERIOD.FNUMBER <= '"+end+"' ").append("\r\n");


if(!"".equals(whereSql)){
sql.append(whereSql);
}
//print sql statement
System.out.println(sql.toString());
debugInfoToTxt(sql);
return sql.toString();
}



private void buildAdminOrgUnitTree(boolean isShowSealUp) throws Exception {
AdminTreeBuilder adminTree = new AdminTreeBuilder(false, null);
adminTree.buildTree(this.kDTree1, isShowSealUp);
}

private void fillFootRow(IRow footRow, String sql) {
YMSQLBuilder builder = new YMSQLBuilder();
builder.clear();
builder.appendSql(sql);
IRowSet irs = null;
try {
irs = builder.executeQuery();
if(irs.next()){

footRow.getCell("FFSAL235" ).setValue(irs.getString("FFSAL235" ));
footRow.getCell("FFSAL233" ).setValue(irs.getString("FFSAL233" ));
footRow.getCell("FFSAL234" ).setValue(irs.getString("FFSAL234" ));
footRow.getCell("FFSAL236" ).setValue(irs.getString("FFSAL236" ));
footRow.getCell("FFSAL252" ).setValue(irs.getString("FFSAL252" ));
footRow.getCell("FFSAL251" ).setValue(irs.getString("FFSAL251" ));
footRow.getCell("FFSAL254" ).setValue(irs.getString("FFSAL254" ));


}
}catch (BOSException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* do database T_HR_SCHM_71448264184074 records display
* @throws Exception
*/
private void fillTable() throws Exception {
kDTable1.removeRows();
YMSQLBuilder builder = new YMSQLBuilder();
builder.clear();
StringBuilder sql = new StringBuilder();
int totalCount = getTotalCount(mainSql());//mainSql return String
setMessageBarText("符合条件的共计"+totalCount+"条");
int page = totalCount%5000==0?totalCount/5000:totalCount/5000+1;//count page number

for(int n=0;n<page;n++){
// select t.* from tablename t where rownum >= and rownum<=
sql.append("/*dialect*/ select t.* from (").append(mainSql()).append(")t where rownum >="+n*5000+" and rownum<="+(n+1)*5000+" ");//?
if(sql == null || "".equals(sql)){
return ;
}
debugInfoToTxt(sql);
System.out.println(sql);
builder.appendSql(sql.toString());

try {
IRowSet irs = builder.executeQuery();
while(irs.next()){
IRow row = kDTable1.addRow();
row.getCell("FFSAL235" ).setValue(irs.getString("FFSAL235" ));
row.getCell("FFSAL259" ).setValue(irs.getString("FFSAL259" ));
row.getCell("FFSAL233" ).setValue(irs.getString("FFSAL233" ));
row.getCell("FFSAL234" ).setValue(irs.getString("FFSAL234" ));
row.getCell("FFSAL236" ).setValue(irs.getString("FFSAL236" ));
row.getCell("FFSAL256" ).setValue(irs.getString("FFSAL256" ));
row.getCell("FFSAL252" ).setValue(irs.getString("FFSAL252" ));
row.getCell("FFSAL251" ).setValue(irs.getString("FFSAL251" ));
row.getCell("FFSAL254" ).setValue(irs.getString("FFSAL254" ));

row.getCell("FPER005" ).setValue(irs.getString("ADMIN" ));
row.getCell("cmpName" ).setValue(irs.getString("CMPNAME" ));
row.getCell("time" ).setValue(irs.getString("time" ));
row.getCell("month" ).setValue(irs.getString("PERIOD" ));
row.getCell("FPER002" ).setValue(irs.getString("PERSONNUM" ));
row.getCell("FPER001" ).setValue(irs.getString("PERSONNAME" ));

row.getCell("FPER054" ).setValue(irs.getString("FPER054" ));
row.getCell("FPER025" ).setValue(irs.getString("FPER025" ));

}
} catch (BOSException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
setFootRow();
}


private int getTotalCount(String mainSql) throws Exception {
int count = 0;
String sql = "/*dialect*/ select count(1) from ("+mainSql+")";

YMSQLBuilder builder = new YMSQLBuilder(sql);
IRowSet irs = builder.executeQuery();
if(irs.next()){
count = irs.getInt(1);
}

return count;
}

//查询横表或历史表中的数据
private String getSql() {
/**
* columnName 数组链表
*/
//设置标准模板的字段名称，用于和横表或历史表中的字段进行比较
List<String> columnName = new ArrayList<String>();//ArrayList&LinkedList
columnName.add("FFSAL235");
columnName.add("FFSAL259");
columnName.add("FFSAL233");
columnName.add("FFSAL234");
columnName.add("FFSAL236");
columnName.add("FFSAL256");
columnName.add("FFSAL252");
columnName.add("FFSAL251");
columnName.add("FFSAL254");
columnName.add("FPER054");
columnName.add("FPER025");
columnName.add("FPERSONID");
columnName.add("FPERIOD");
columnName.add("FTIME");

String start ;
if(kDStartDate.getValue()==null)
{
start = "197901";

}else{
start = kDStartDate.getValue().toString();

}

String end ;
if(kDStartDate.getValue()==null)
{
end = "300000";

}else{
end = kDEndDate.getValue().toString();

}
/**
* yama.basedata.YMSQLBuilder
* @see lesliech YMSQLBuilder CLASS files
* function:connecting to YM database
*/
YMSQLBuilder builder = new YMSQLBuilder();
IRowSet irs = null;
StringBuilder sql = new StringBuilder();
sql.setLength(0);
sql.append("SELECT FHORTABLENAME AS TABLENAME FROM t_hr_cmpscheme CMP ").append("\n\t");
sql.append("WHERE CMP.FENABLE = 1 ").append("\n\t");
sql.append(" UNION").append("\n\t");
sql.append("SELECT FHISTABLENAME AS TABLENAME FROM t_hr_cmpscheme CMP").append("\n\t");
sql.append("WHERE CMP.FENABLE = 1 ").append("\n\t");
builder.clear();
builder.appendSql(sql.toString());

StringBuilder sb = new StringBuilder();
sb.setLength(0);

try {
irs = builder.executeQuery();//builder execute sql statement return result set
int index = 0;
/**
* use irs resultset get biz tables
* e.g:T_HR_SCHM_71448264184074
*/
while(irs.next()){
String tableName = irs.getString("TABLENAME");
if(!"".equals(tableName) && tableName!= null){
sql.setLength(0);
sql.append("SELECT * FROM "+tableName+" where rownum = 1").append("\n\t");
builder.clear();
builder.appendSql(sql.toString());
IRowSet irs2 = builder.executeQuery();

//获取横表或者历史表的所有字段，并和模板的columnName比较，存在则添加到column中

ResultSetMetaData rsmd = irs2.getMetaData();//get database resultSet
int count = rsmd.getColumnCount();//return resultSet records count
List<String> s = new ArrayList<String>();
for(int i=1;i<count ;i++){
s.add(rsmd.getColumnName(i));
}

/**
* columms 数组链表
* 用于存储需要查询的字段名称
*/
/*FFSAL477-->银行转账(含暂缓)
FFSAL479-->是否发放上月止暂缓
FFSAL481-->截止上月薪资未发放
FFSAL483-->截止本月薪资未发放
FFSAL485-->发放上月止暂缓工资
FFSAL487-->是否暂缓
FFSAL475-->实领工资(含暂缓)
FFSAL495-->现金发放
FFSAL490-->领现金*/
//用于存储需要查询的字段名称 :
List<String> columns = new ArrayList<String>();
//获取查询的字段名称，没有的字段为Null
for(String cn : columnName){//String cn = '';columnName<=list.length;list++
if(s.contains(cn)){//存在则添加到column中
//是暂缓||是发放上月止暂缓
if("FFSAL256".equals(cn) || "FFSAL259".equals(cn))
columns.add("TO_CHAR("+cn+") AS "+cn);//
else{
/*判断是否为莆田薪酬方案,是的话执行莆田薪酬方案项目值字段替换
* T_HR_SCHM_61446536527612
**/

if(tableName.equals("T_HR_SCHM_61446536527612"))
{
columns.add("T_HR_SCHM_61446536527612.FFSAL481 AS"+ cn);
}else if
columns.add(cn);
}
}else//不存在则null AS
columns.add("null AS "+cn);
}

String column = columns.toString().replaceAll("(\\[)|(\\])", " ");

if(index != 0){
sb.append(" UNION ALL").append("\n\t");
}
sb.append("SELECT '"+tableName+"' as tableName,"+column+" FROM "+tableName).append("\n\t");

index++;
}
}
} catch (BOSException e1) {
e1.printStackTrace();
} catch (SQLException e1) {
e1.printStackTrace();

}
debugInfoToTxt(sb);
System.out.println(sb.toString());
return sb.toString();
}


@Override
public void actionSearch_actionPerformed(ActionEvent e) throws Exception {
setWhereSql();
fillTable();
}

//汇总
private String getTotalSql(String whereSql) throws EASBizException, BOSException {
String mainSql = mainSql().toString();
//Debug info
StringBuilder sql = new StringBuilder();
if(mainSql.length() > 0){
sql.append("/*dialect*/").append("\n\t");
sql.append("SELECT").append("\n\t");

sql.append("SUM(FFSAL235) AS FFSAL235, ").append("\n\t");
sql.append("SUM(FFSAL233) AS FFSAL233, ").append("\n\t");
sql.append("SUM(FFSAL234) AS FFSAL234, ").append("\n\t");
sql.append("SUM(FFSAL236) AS FFSAL236, ").append("\n\t");
sql.append("SUM(FFSAL252 ) AS FFSAL252, ").append("\n\t");
sql.append("SUM(FFSAL251 ) AS FFSAL251 , ").append("\n\t");
sql.append("SUM(FFSAL254 ) AS FFSAL254 ").append("\n\t");


sql.append("FROM ").append("\n\t");
//table is sql compensation scheme.
sql.append("("+mainSql+")").append("\n\t");

// if(whereSql != null && !"".equals(whereSql)){
// sql.append(whereSql).append("\n\t");
// sql.append("GROUP BY FFSAL256").append("\n\t");
// }


}
debugInfoToTxt(sql);
System.out.println(sql.toString());
return sql.toString();
}


//设置底部汇总行
private void setFootRow() throws EASBizException, BOSException {
IRow footRow1 = null;

KDTFootManager footRowManager = kDTable1.getFootManager();
if (footRowManager == null) {
footRowManager = new KDTFootManager(kDTable1);
footRowManager.addFootView();
kDTable1.setFootManager(footRowManager);
}
footRow1 = footRowManager.getFootRow(0);

String whereSql = null;

if (footRow1 == null) {
footRow1 = footRowManager.addFootRow(0);
}


footRowManager.addIndexText(0, "合计");




//总计
whereSql = null ;
String sql = getTotalSql(whereSql).toString();
if(sql!=null && !"".equals(sql)){
fillFootRow(footRow1, sql );
}

footRow1.getStyleAttributes().setBackground(new Color(246, 246, 191));

}
public void debugInfoToTxt(StringBuilder sb)throws FileNotFoundException,IOException{
sb = new StringBuilder();
sb.append("JAVADEBUGINFO粑葛");
int n = 100;
//声明流对象
FileOutputStream fos = null;
fos = new FileOutputStream("d:\\ur0652\\sysOutPrint\\sysOutPrint.txt");
//转换为字节数组
byte[] b1 = sb.toString().getBytes();
//追加回车和换行符
byte[] b2 = "\r\n".getBytes();
byte[] b3 = String.valueOf(n).getBytes();
//依次写入文件
fos.write(b1);
fos.write(b2);
fos.write(b3);
//close file output stream object
fos.close();
}

}