william_869642103
- 浏览: 1030 次
- 性别:
- 来自: 北京
-
最新评论
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();
}
}
{
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();
}
}
- 财务薪酬现金发放BY火0413.rar (5.6 KB)
- 下载次数: 0
分享到：
发表评论
相关推荐
原生js图片圆形排列按钮控制3D旋转切换插件.zip
内含二维数组与三维数组,分别为list2nd,list3rd
原生js颜色随机生成9x9乘法表代码.zip
原生js实现图片叠加滚动切换代码.zip
【Academic tailor】学术小裁缝必备知识点：全局注意力机制（GAM） 注意力机制是深度学习中的重要技术，尤其在序列到序列（sequence-to-sequence）任务中广泛应用，例如机器翻译、文本摘要和问答系统等。这一机制由 Bahdanau 等人在其论文《Neural Machine Translation by Jointly Learning to Align and Translate》中首次提出。以下将详细介绍这一机制的背景、核心原理及相关公式。 全局注意力机制（Global Attention Mechanism, GAM）由 《Global Attention Mechanism: Retain Information to Enhance Channel-Spatial Interactions》提出，是一篇针对计算机视觉任务提出的方法。这篇文章聚焦于增强深度神经网络中通道和空间维度之间的交互，以提高分类任务的性能。与最早由 Bahdanau 等人提出的用于序列到序列任务的注意力机制 不同，这篇文章的重点是针对图像分类任务，并未专注于序
本项目在开发和设计过程中涉及到原理和技术有: B/S、java技术和MySQL数据库等；此文将按以下章节进行开发设计； 第一章绪论；剖析项目背景,说明研究的内容。 第二章开发技术；系统主要使用了java技术， b/s模式和myspl数据库，并对此做了介绍。 第三章系统分析；包罗了系统总体结构、对系统的性能、功能、流程图进行了分析。 第四章系统设计；对软件功能模块和数据库进行详细设计。 第五章系统总体设计；对系统管理员和用户的功能进行描述， 第六章对系统进行测试， 第七章总结心得；在论文最后结束章节总结了开发这个系统和撰写论文时候自己的总结、感想,包括致谢。
1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看README.md文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。
镗夹具总工艺图
原生js树叶数字时钟代码.rar
近代非线性回归分析-韦博成1989
内容概要：本文详细介绍了用 Rust 语言实现冒泡排序算法的具体步骤，以及通过设置标志位来优化算法性能的方法。示例代码包括了函数定义、内外层循环逻辑、标志位的应用，并在主函数中展示了如何调用 bubble_sort 函数并显示排序前后的数组。 适合人群：具有基本 Rust 编程基础的学习者和开发者。 使用场景及目标：适用于想要深入了解 Rust 中冒泡排序实现方式及其优化技巧的技术人员。通过本篇文章，能够掌握 Rust 基本语法以及算法优化的基本思想。 阅读建议：除了仔细阅读和理解每一部分的内容外，还可以尝试修改代码，改变数据集大小，进一步探索冒泡排序的时间复杂度和优化效果。此外，在实际应用时也可以考虑引入并发或其他高级特性以提升性能。
培训课件 -安全隐患分类与排查治理.pptx
中国各地级市的海拔标准差数据集提供了298个地级市的海拔变异性信息。海拔标准差是衡量某地区海拔高度分布离散程度的统计指标，它通过计算各测量点海拔与平均海拔之间的差异来得出。这一数据对于评估地形起伏对网络基础设施建设的影响尤为重要，因为地形的起伏度不仅会增加建设成本，还会影响信号质量。此外，由于地形起伏度是自然地理变量，它与经济社会因素关联性较小，因此被用作“宽带中国”试点政策的工具变量，以研究网络基础设施建设对经济的影响。数据集中包含了行政区划代码、地区、所属省份、所属地域、长江经济带、经度、纬度以及海拔标准差等关键指标。这些数据来源于地理空间数据云，并以Excel和dta格式提供，方便研究者进行进一步的分析和研究。
YOLO算法的原理与实现
视网膜病变是糖尿病和高血压的主要微血管并发症。如果不及时治疗，可能会导致失明。据估计，印度三分之一的成年人患有糖尿病或高血压，他们未来患视网膜病变的风险很高。我们研究的目的是检查糖化血红蛋白 (HbA1c)、血压 (BP) 读数和脂质水平与视网膜病变的相关性。我们的主要假设是，血糖控制不佳（表现为高 HbA1c 水平、高血压和异常脂质水平）会导致视网膜病变风险增加。我们使用眼底照相机筛查了 119 名印度患者的视网膜病变，并获取了他们最近的血压、HbA1c 和血脂谱值。然后，我们应用 XGBoost 机器学习算法根据他们的实验室值预测是否存在视网膜病变。我们能够根据这些关键生物标志物高精度地预测视网膜病变。此外，使用 Shapely Additive Explanations (SHAP)，我们确定了对模型最重要的两个特征，即年龄和 HbA1c。这表明血糖控制不佳的老年患者更有可能出现视网膜病变。因此，这些高风险人群可以成为早期筛查和干预计划的目标，以防止视网膜病变发展为失明。
在强化学习（RL）领域，如何稳定地优化策略是一个核心挑战。2015 年，由 John Schulman 等人提出的信赖域策略优化（Trust Region Policy Optimization, TRPO）算法为这一问题提供了优雅的解决方案。TRPO 通过限制策略更新的幅度，避免了策略更新过大导致的不稳定问题，是强化学习中经典的策略优化方法之一。
1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看README.md文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。
这组数据涵盖了1999至2020年间中国各地区普通小学毕业生的数量。它为我们提供了一个深入了解中国教育领域中普通小学阶段教育水平和教育资源分配情况的窗口。通过分析这些数据，可以为制定科学合理的教育政策提供依据，同时，通过比较不同城市的普通小学毕业生数，也能为城市规划和劳动力市场调查提供参考。数据来源于中国区域统计年鉴和中国各省市统计年鉴，包含了8472个样本，以面板数据的形式呈现。这些数据对于掌握中国教育态势具有重要的参考价值。
原生js制作拖拽排列排序代码.zip