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();
}

}