FinanceSalaryToPutianCashier.

/**
* Bakup java src code.
* FinanceSalarySearchToPutian.java
* {@code: src.com.kingdee.eas.yama.hr.kqcl.client.FinanceSalarySearchToCashier.java}
* @author:Will Jame.
* @since 2016.04.14
* @version:Putian0.1
*/

/**
* FinanceSalarySearchToPutian
* Putian finance salary search scheme
*
*/
public class FinanceSalarySearchToPutian extends AbstractFinanceSalarySearchToPutian
{
    private static final Logger logger = CoreUIObject.getLogger(FinanceSalarySearchToPutian.class);
    private String whereSql;
   
    /**
     * FinanceSalarySearchToPutian class constructor
     *
     */
    public FinanceSalarySearchToPutian() throws Exception
    {
        super();
       
    }
    @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 e2){
    e2.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 sortManager=new KDTSortManager(kDTable1);
    sortManager.setSortAuto(true);
    sortManager.setClickCount(1);
    for(int i=0, count=kDTable1.getColumnCount();i<count;i++){
    kDTable1.getColumn(i).setSortable(true);
    }
    }
    protected void setWhereSql(){
    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()+ "'";
    }
    }
    //System.out.println(whereSql);
    }
   
    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.FFSAL491 AS FFSAL491 ,    -- 银行转账（含暂缓） ").append("\r\n");//FFSAL491-->银行转账(含暂缓)
    sql.append("SCHM.ftime AS time,  --薪酬方案次数").append("\r\n");
    sql.append("SCHM.FFSAL493 AS FFSAL493 ,    -- 是否发放上月止暂缓 ").append("\r\n");//FFSAL493-->是否发放上月止暂缓
    sql.append("SCHM.FFSAL494 AS FFSAL494 ,    -- 截止上月薪资未发放").append("\r\n");//FFSAL494-->截止上月薪资未发放
    sql.append("SCHM.FFSAL495 AS FFSAL495 ,    -- 截止本月薪资未发放  ").append("\r\n");//FFSAL495-->截止本月薪资未发放
    sql.append("SCHM.FFSAL496 AS FFSAL496 ,    -- 发放上月止暂缓工资  ").append("\r\n");//FFSAL496-->发放上月止暂缓工资
    sql.append("SCHM.FFSAL497 AS FFSAL497 ,    -- 是否暂缓     ").append("\r\n");//FFSAL497-->是否暂缓
    sql.append("SCHM.FFSAL490 AS FFSAL490 ,    -- 实领工资（含暂缓）").append("\r\n");//FFSAL490-->实领工资(含暂缓)
    sql.append("SCHM.FFSAL421 AS FFSAL421 ,    -- 现金 发放      ").append("\r\n");//FFSAL421-->现金发放
    sql.append("SCHM.FFSAL515 AS FFSAL515 ,    -- 领现金  ").append("\r\n");//FFSAL515-->领现金
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");
    //FFSAL497-->是否暂缓  FFSAL487-->是否暂缓 为空才过滤数据出来=>暂缓不过滤数据出来
    sql.append("WHERE SCHM.FFSAL497 IS NOT 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());
    return sql.toString();
    }
   
   
    /**
     * buildAdminOrgUnitTree private method
     * @param isShowSealup bool value
     * @throws Exception
     */
    private void buildAdminOrgUnitTree(boolean isShowSealup)throws Exception{
    AdminTreeBuilder adminTree=new AdminTreeBuilder(false,null);
    adminTree.buildTree(this.kDTree1,isShowSealup);
    }
    private void fillTable() throws Exception{
    kDTable1.removeRows();
    YMSQLBuilder builder=new YMSQLBuilder();
    builder.clear();
    StringBuilder sql = new StringBuilder();
    int totalCount = getTotalCount(mainSql());
    setMessageBarText("符合条件的共计："+totalCount+"条");
    int page = totalCount%5000 == 0 ? totalCount/5000 : totalCount/5000+1;
   
    for(int n=0;n<page;n++){
    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;
    }
    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("FFSAL491"));//477--> 银行转账（含暂缓）FFSAL491-->银行转账(含暂缓)
    row.getCell("FFSAL259").setValue(irs.getString("FFSAL493"));//479--> 是否发放上月止暂缓FFSAL493-->是否发放上月止暂缓
    row.getCell("FFSAL233").setValue(irs.getString("FFSAL494"));//481--> 截止上月薪资未发放FFSAL494-->截止上月薪资未发放
    row.getCell("FFSAL234").setValue(irs.getString("FFSAL495"));//483--> 截止本月薪资未发放FFSAL495-->截止本月薪资未发放
    row.getCell("FFSAL236").setValue(irs.getString("FFSAL496"));//485--> 发放上月止暂缓工资FFSAL496-->发放上月止暂缓工资
    row.getCell("FFSAL256").setValue(irs.getString("FFSAL497"));//487--> 是否暂缓                FFSAL497-->是否暂缓
    row.getCell("FFSAL252").setValue(irs.getString("FFSAL490"));//475--> 实领工资（含暂缓）FFSAL490-->实领工资(含暂缓)
    row.getCell("FFSAL251").setValue(irs.getString("FFSAL421"));//495--> 现金发放                 FFSAL421-->现金发放
    row.getCell("FFSAL254").setValue(irs.getString("FFSAL515"));//490--> 领现金                    FFSAL515-->领现金
    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(){
    List<String> columnName = new ArrayList<String>();
    columnName.add("FFSAL491");//银行转账（含暂缓）-->FFSAL491-->银行转账(含暂缓)
    columnName.add("FFSAL493");//是否发放上月止暂缓 -->FFSAL493-->是否发放上月止暂缓
    columnName.add("FFSAL494");//截止上月薪资未发放 -->FFSAL494-->截止上月薪资未发放
    columnName.add("FFSAL495");//截止本月薪资未发放 -->FFSAL495-->截止本月薪资未发放
    columnName.add("FFSAL496");//发放上月止暂缓工资 -->FFSAL496-->发放上月止暂缓工资
    columnName.add("FFSAL497");//是否暂缓 -->FFSAL497-->是否暂缓
    columnName.add("FFSAL490");//实领工资（含暂缓） -->FFSAL490-->实领工资(含暂缓)
    columnName.add("FFSAL421");//现金发放 -->FFSAL421-->现金发放
    columnName.add("FFSAL515");//领现金 -->FFSAL515-->领现金
   
    columnName.add("FPER054");//银行名称1 莆田方案的此字段已默认关联引入勿须另处理
columnName.add("FPER025");//个人账号1 莆田方案的此字段已默认关联引入勿须另处理
    columnName.add("FPERSONID");//横表和历史表里默认有的字段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);
    /**
    * @seemu0414 EAS员工薪酬方案的横表和历史表共有十三张其中八张横表+五张历史表
    * 其中莆田员工薪酬方案只有一张横表
    * 在获取横表或者历史表时只取出莆田员工薪酬方案的横表和历史表
    * 做莆田员工薪酬方案的数据展示
    * SQL:
    * select FANEM_L2,FNUMBER,FHORTABLENAME,
    * FHISTABLENAME,FCREATETIME,FLASTUPDATETIME
    * FROM t_hr_cmpscheme where FNUMBER = 'PT01';
    *
    * SELECT FHORTABLENAME AS TABLENAME FROM t_hr_cmpscheme CMP
    * WHERE CMP.FENABLE = 1 AND FNUMBER = 'PT01'
    * UNION
    * SELECT FHISTABLENAME AS TABLENAME FROM t_hr_cmpscheme CMP
    * WHERE CMP.FENABLE = 1 AND FNUMBER ='PT01'
    */
    sql.append("SELECT FHORTABLENAME AS TABLENAME FROM t_hr_cmpscheme CMP  ").append("\n\t");
    sql.append("WHERE CMP.FENABLE = 1 AND FNUMBER = 'PT01' ").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 AND FNUMBER ='PT01' ").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++){
System.out.println(i+" HorTableField: "+rsmd.getColumnName(i));
s.add(rsmd.getColumnName(i));
}

/**
* columms 数组链表
*/
//用于存储需要查询的字段名称
    List<String> columns = new ArrayList<String>();
//获取查询的字段名称，没有的字段为Null
for(String cn : columnName){
if(s.contains(cn)){//存在则添加到column中
/**
* 如果是字段名为<是否暂缓FFSAL256>或者<是否发放上月止暂缓FFSAL259>
* 则TO_CHAR后在添加到COLUMNS里
* 256 AS 是否暂缓
* 259 AS 是否发放上月止暂缓
* FFSAL487-->是否暂缓
* FFSAL479-->是否发放上月止暂缓
*
* FFSAL497-->是否暂缓
* FFSAL493-->是否发放上月止暂缓 *
*/
if("FFSAL497".equals(cn) || "FFSAL493".equals(cn))
columns.add("TO_CHAR("+cn+") AS "+cn);
else{
/**
* 不是<是否暂缓FFSAL256>并且不是<是否发放上月止暂缓FFSAL259>
* 则直接添加到COLUMNS里
*/
columns.add(cn);
//System.out.println("user define column Name: " +cn);
}
}else{

columns.add("null AS "+cn);
//System.out.println("user define column Name but database table no has this field: " +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();

    }
    System.out.println(sb.toString());
    return sb.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));
    }
   
    private String getTotalSql(String whereSql)throws EASBizException,BOSException{
    String mainSql = mainSql().toString();
    StringBuilder sql = new StringBuilder();
    if(mainSql.length() > 0){
    sql.append("/*dialect*/").append("\n\t");
    sql.append("SELECT ").append("\n\t");
    sql.append("SUM(FFSAL477) AS FFSAL491, ").append("\n\t");//FFSAL491-->银行转账(含暂缓)

    sql.append("SUM(FFSAL494) AS FFSAL494, ").append("\n\t");//FFSAL481-->截止上月薪资未发放 FFSAL494-->截止上月薪资未发放
    sql.append("SUM(FFSAL495) AS FFSAL495, ").append("\n\t");//483--> 截止本月薪资未发放  FFSAL495-->截止本月薪资未发放
    sql.append("SUM(FFSAL496) AS FFSAL496, ").append("\n\t");//485--> 发放上月止暂缓工资  FFSAL496-->发放上月止暂缓工资
    sql.append("SUM(FFSAL490) AS FFSAL490, ").append("\n\t");//475--> 实领工资（含暂缓）  FFSAL490-->实领工资(含暂缓)
    sql.append("SUM(FFSAL421) AS FFSAL421, ").append("\n\t");//495--> 现金发放                   FFSAL421-->现金发放
    sql.append("SUM(FFSAL515) AS FFSAL515  ").append("\n\t");//490--> 领现金                      FFSAL515-->领现金
    sql.append("FROM ").append("\n\t");
    sql.append("("+mainSql+")").append("\n\t");
    }
    //System.out.println(sql.toString());
    return sql.toString();
    }
   
    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("FFSAL491"));//477--> 银行转账（含暂缓）FFSAL491-->银行转账(含暂缓)
    footRow.getCell("FFSAL233").setValue(irs.getString("FFSAL494"));//481--> 截止上月薪资未发放FFSAL494-->截止上月薪资未发放
    footRow.getCell("FFSAL234").setValue(irs.getString("FFSAL495"));//483--> 截止本月薪资未发放FFSAL495-->截止本月薪资未发放
    footRow.getCell("FFSAL236").setValue(irs.getString("FFSAL496"));//485--> 发放上月止暂缓工资FFSAL496-->发放上月止暂缓工资
    footRow.getCell("FFSAL252").setValue(irs.getString("FFSAL490"));//475--> 实领工资（含暂缓）FFSAL490-->实领工资(含暂缓)
    footRow.getCell("FFSAL251").setValue(irs.getString("FFSAL421"));//495--> 现金发放                FFSAL421-->现金发放
    footRow.getCell("FFSAL254").setValue(irs.getString("FFSAL515"));//490--> 领现金                   FFSAL515-->领现金
    }
    }catch(BOSException bose){
    bose.printStackTrace();
    }catch(SQLException sqle){
    sqle.printStackTrace();
    }
    }
   
    /**
     * FinanceSalarySearchToPutian actionSearch_actionPerformed method
     * 不能实现actionSearch_actionPerformed因为这个方法是从~ToCashier.java
     * 继承过来的，要重写actionSearch_actionPerformed方法
     */
    public void actionSearch_actionPerformed(ActionEvent e) throws Exception
    {
    setWhereSql();
    fillTable();
       
    }
}