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

}
}