You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
255 lines
10 KiB
Plaintext
255 lines
10 KiB
Plaintext
1 year ago
|
<%@ page import="weaver.general.*" %>
|
||
|
|
<%@page import="weaver.conn.RecordSet"%>
|
||
|
|
<%@ page import="weaver.hrm.User" %>
|
||
|
|
<%@ page import="weaver.hrm.HrmUserVarify" %>
|
||
|
|
<%@ page import="org.apache.commons.lang.StringUtils" %>
|
||
|
|
<%@ page import="com.alibaba.fastjson.JSONObject" %>
|
||
|
|
<%@ page import="java.util.*" %>
|
||
|
|
<%@ page import="java.math.BigDecimal" %>
|
||
|
|
<%@ page import="java.text.DecimalFormat" %>
|
||
|
|
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
|
||
|
|
|
||
|
|
<%
|
||
|
|
BaseBean bb = new BaseBean();
|
||
|
|
String begindate = "" ;
|
||
|
|
String enddate = "" ;
|
||
|
|
int year = 0 ;
|
||
|
|
User user = null;
|
||
|
|
String type = Util.null2String(request.getParameter("type"));
|
||
|
|
if("0".equals(type)){
|
||
|
|
begindate = Util.null2String(request.getParameter("begindate"));
|
||
|
|
enddate = Util.null2String(request.getParameter("enddate"));
|
||
|
|
year = Util.getIntValue(request.getParameter("year"));
|
||
|
|
int userid = Util.getIntValue(request.getParameter("userid"));
|
||
|
|
user = new User(userid);
|
||
|
|
}else{
|
||
|
|
begindate = TimeUtil.getFirstDayOfTheYear();
|
||
|
|
enddate = TimeUtil.getCurrentDateString();
|
||
|
|
Calendar calendar = Calendar.getInstance();
|
||
|
|
calendar.setTime(new Date());
|
||
|
|
year = calendar.get(Calendar.YEAR);
|
||
|
|
user = HrmUserVarify.getUser(request , response) ;
|
||
|
|
}
|
||
|
|
|
||
|
|
int userid = user.getUID();
|
||
|
|
Map<String,Object> dataMap = getYsAndJhwcl(userid,begindate,enddate);
|
||
|
|
JSONObject jsonObject = new JSONObject();
|
||
|
|
jsonObject.putAll(dataMap);
|
||
|
|
|
||
|
|
%>
|
||
|
|
<%=jsonObject.toString() %>
|
||
|
|
|
||
|
|
<%!
|
||
|
|
|
||
|
|
//本年完成验收、计划完成率、本年计划验收
|
||
|
|
public Map<String,Object> getYsAndJhwcl(int userid,String startdate,String endate)
|
||
|
|
{
|
||
|
|
Map<String,Object> map = new HashMap<String, Object>();
|
||
|
|
RecordSet rs = new RecordSet();
|
||
|
|
|
||
|
|
double bnwcys = 0.00;
|
||
|
|
double bnjhys = 0.00;
|
||
|
|
double jhyswcl = 0.00;
|
||
|
|
try {
|
||
|
|
String sql = queryReportWhereWithDate(userid+"",startdate,endate);
|
||
|
|
rs.execute(sql);
|
||
|
|
if(rs.next())
|
||
|
|
{
|
||
|
|
bnwcys = Util.getDoubleValue(Util.null2String(rs.getString("thisyearfinishamount"),"0"),0.00);
|
||
|
|
bnjhys = Util.getDoubleValue(Util.null2String(rs.getString("thisyearplanamount"),"0"),0.00);
|
||
|
|
}
|
||
|
|
if(bnjhys > 0){
|
||
|
|
jhyswcl = getRate(bnwcys, bnjhys);
|
||
|
|
}
|
||
|
|
map.put("sql", sql);
|
||
|
|
}catch (Exception e) {
|
||
|
|
e.printStackTrace();
|
||
|
|
}
|
||
|
|
map.put("bnwcys", MathUtil.div(bnwcys,10000.0,2));
|
||
|
|
map.put("jhyswcl", jhyswcl);
|
||
|
|
map.put("bnjhys", bnjhys);
|
||
|
|
|
||
|
|
return map;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/***
|
||
|
|
*
|
||
|
|
* @param userids
|
||
|
|
* @param startdate
|
||
|
|
* @param endate
|
||
|
|
* @return
|
||
|
|
*/
|
||
|
|
public String queryReportWhereWithDate(String userids,String startdate,String endate) {
|
||
|
|
BaseBean bb = new BaseBean();
|
||
|
|
String userWhere = " and (t3t.hrmid in("+userids+") or t3t.qyjl in("+userids+") or t3t.dqzj in("+userids+") or t3t.qyzj in("+userids+")) " ;
|
||
|
|
|
||
|
|
String starMonth = startdate.substring(0,7);
|
||
|
|
String endMonth = endate.substring(0,7) ;
|
||
|
|
|
||
|
|
StringBuffer sb = new StringBuffer();
|
||
|
|
|
||
|
|
// bnwcys = Util.getDoubleValue(rs.getString("thisyearfinishamount"));
|
||
|
|
// thismonthfinishamount = Util.getDoubleValue(rs.getString("thismonthfinishamount"));
|
||
|
|
// thismonthplanamount = Util.getDoubleValue(rs.getString("thismonthplanamount"));
|
||
|
|
|
||
|
|
//计划验收
|
||
|
|
sb.append(" SELECT SUM(cie.sumAmount) AS thisyearplanamount,0 as thisyearfinishamount " +
|
||
|
|
" FROM proj_CardBaseInfo ci " +
|
||
|
|
" LEFT JOIN proj_CardBaseInfoExec cie ON cie.cbi_id = ci.id " +
|
||
|
|
" LEFT JOIN lf_prjhrminfo t3t ON t3t.hrmid = ci.projmanager " +
|
||
|
|
" where (lastprjstate in ('正常', '预警', '延期') or (lastprjstate in ('验收','结案') and gdrq is not null and gdrq<>'' and substring(gdrq, 0, 8)>='"+starMonth+"' and substring(gdrq, 0, 8)<='"+endMonth+"')) " +
|
||
|
|
userWhere +
|
||
|
|
" and exists(select 1 from proj_CardBaseInfoPlan where cbi_id=ci.id " +
|
||
|
|
" and yearMonth >='"+starMonth+"' and yearMonth<='" + endMonth +"' "+
|
||
|
|
" and substring(planapprovaldate2, 0, 8)>='"+starMonth+"' and substring(planapprovaldate2, 0, 8)<='"+endMonth+"' ) ");
|
||
|
|
|
||
|
|
//本年完成验收
|
||
|
|
sb.append(" union all " +
|
||
|
|
" SELECT 0 AS thisyearplanamount,sum(isnull(t.htzje, 0)) as thisyearfinishamount " +
|
||
|
|
" FROM proj_CardBaseInfo ci " +
|
||
|
|
" LEFT JOIN proj_CardBaseInfoExec cie ON cie.cbi_id = ci.id " +
|
||
|
|
" LEFT JOIN lf_prjhrminfo t3t ON t3t.hrmid = ci.projmanager " +
|
||
|
|
" LEFT JOIN (select t1.id,t1.hth,(case when substring(t1.hth,6,1) in ('2','6') then isnull(t4.htzje,0)-isnull(t4.bgje,0) else isnull(t2.htzje,0)-isnull(t2.bgje,0) end) as htzje from formtable_main_190 t1 left join formtable_main_180 t2 on t1.hth=t2.hth and t2.hth<>'' and t2.hth is not null left join formtable_main_183 t4 on t1.hth=t4.hth and t4.hth<>'' and t4.hth is not null " +
|
||
|
|
" where t1.hsny>='"+starMonth+"' AND t1.hsny<='"+endMonth+"') t on t.hth=cie.hth " +
|
||
|
|
" where 1=1 "+ userWhere +
|
||
|
|
" ");
|
||
|
|
|
||
|
|
String sql = " SELECT convert(decimal(18,2),isnull((sum(isnull(p.thisyearplanamount,0))),0)) AS thisyearplanamount,\n" +
|
||
|
|
" convert(decimal(18,2),isnull((sum(isnull(p.thisyearfinishamount,0))),0)) AS thisyearfinishamount\n " +
|
||
|
|
" FROM( \n" +
|
||
|
|
sb.toString()+
|
||
|
|
" ) p ";
|
||
|
|
bb.writeLog(sql);
|
||
|
|
|
||
|
|
return sql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
public static double getRate(double a,double b){
|
||
|
|
double rate=0.0;
|
||
|
|
if(b==0.0){
|
||
|
|
return rate;
|
||
|
|
}
|
||
|
|
try{
|
||
|
|
rate=MathUtil.div(a*100,b,2);
|
||
|
|
}catch (Exception e) {
|
||
|
|
e.printStackTrace();
|
||
|
|
}
|
||
|
|
return rate;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/***
|
||
|
|
*
|
||
|
|
* @param userid
|
||
|
|
* @param startdate
|
||
|
|
* @param endate
|
||
|
|
* @return
|
||
|
|
*/
|
||
|
|
public double getWCSKDataNew(int userid,String startdate,String endate)
|
||
|
|
{
|
||
|
|
BaseBean bb = new BaseBean();
|
||
|
|
bb.writeLog("getWCSKDataNew"); // 核算公司 只留 泛微网络 项目经理
|
||
|
|
RecordSet rs = new RecordSet();
|
||
|
|
/******************完成收款****************/
|
||
|
|
double totalInAmt = 0.00 ;
|
||
|
|
try{
|
||
|
|
String userWhere = " and exists( select 1 from lf_prjhrminfo t3t where t3t.hrmid = cbi.projManager and (t3t.hrmid in("+userid+") or t3t.qyjl in("+userid+") or t3t.dqzj in("+userid+") or t3t.qyzj in("+userid+"))) " ;
|
||
|
|
String where = getJHSKwhere(userid);
|
||
|
|
String datewhere = "" ;
|
||
|
|
if(StringUtils.isNotEmpty(startdate) && StringUtils.isNotEmpty(endate))
|
||
|
|
{
|
||
|
|
datewhere = " and cbiead.inDate >='"+startdate+"' and cbiead.inDate <='"+endate+"'";
|
||
|
|
}
|
||
|
|
//bb.writeLog("where:"+where);
|
||
|
|
String sql =" select Convert(decimal(18,2),(sum(isnull(w.inAmt,0))/10000.0)) as inAmt \n " +
|
||
|
|
" from( " +
|
||
|
|
" select cbiead.inAmt,cbi.projManager \n" +
|
||
|
|
" from proj_CardBaseInfoExec cbie \n" +
|
||
|
|
" inner join proj_CardBaseInfoExecDtl cbied on cbied.cbie_id = cbie.id \n" +
|
||
|
|
" inner join proj_CardBaseInfoExecAmtDtl cbiead on cbiead.cbied2_id = cbied.id \n" +
|
||
|
|
" inner join proj_CardBaseInfo cbi on cbie.cbi_id = cbi.id \n" +
|
||
|
|
" where 1=1 " + userWhere + datewhere + where +
|
||
|
|
" ) w " ;
|
||
|
|
bb.writeLog("sql:"+sql);
|
||
|
|
rs.execute(sql);
|
||
|
|
if(rs.next()){
|
||
|
|
totalInAmt = Util.getDoubleValue(Util.null2String(rs.getString("inAmt")));
|
||
|
|
}
|
||
|
|
}catch (Exception e){
|
||
|
|
|
||
|
|
}
|
||
|
|
return totalInAmt;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/***
|
||
|
|
*
|
||
|
|
* @param userid
|
||
|
|
* @return
|
||
|
|
*/
|
||
|
|
public String getJHSKwhere(int userid)
|
||
|
|
{
|
||
|
|
StringBuffer sqlWhere_t = new StringBuffer();
|
||
|
|
|
||
|
|
try{
|
||
|
|
sqlWhere_t.append( " AND ( 1 = 2 "+
|
||
|
|
" OR ( cbi.projManager IN ( SELECT id "+
|
||
|
|
" FROM getallchilds("+userid+") ) "+
|
||
|
|
" OR cbi.projManager = "+userid+
|
||
|
|
" ) "+
|
||
|
|
" OR ( EXISTS ( SELECT 1 "+
|
||
|
|
" FROM proj_CardBaseInfo_members cbim "+
|
||
|
|
" WHERE cbim.cbi_id = cbi.id "+
|
||
|
|
" AND cbim.userId = "+userid+" ) ) "+
|
||
|
|
" OR ( EXISTS ( SELECT 1 "+
|
||
|
|
" FROM CRM_CustomerInfo ci "+
|
||
|
|
" WHERE ci.id = cbi.projCustomer "+
|
||
|
|
" AND ( ci.manager IN ( SELECT "+
|
||
|
|
" id "+
|
||
|
|
" FROM "+
|
||
|
|
" getallchilds("+userid+") ) "+
|
||
|
|
" OR ci.manager = "+userid+
|
||
|
|
" ) ) ) "+
|
||
|
|
" OR ( EXISTS ( SELECT 1 "+
|
||
|
|
" FROM proj_default_share pds "+
|
||
|
|
" JOIN HrmResource hr ON pds.shared_deptids = hr.departmentid "+
|
||
|
|
" WHERE ( pds.share_pid IN ( SELECT "+
|
||
|
|
" id "+
|
||
|
|
" FROM getallchilds("+userid+") ) "+
|
||
|
|
" OR pds.share_pid = "+userid+
|
||
|
|
" ) "+
|
||
|
|
" AND hr.id = cbi.projManager ) ) "+
|
||
|
|
" ) ") ;
|
||
|
|
|
||
|
|
}catch (Exception e){
|
||
|
|
|
||
|
|
}
|
||
|
|
return sqlWhere_t.toString();
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/***
|
||
|
|
*
|
||
|
|
* @param value
|
||
|
|
* @return
|
||
|
|
*/
|
||
|
|
public String formatBigDecimal(String value){
|
||
|
|
String formatValue = "" ;
|
||
|
|
try{
|
||
|
|
DecimalFormat decimalFormat = new DecimalFormat("0.00");
|
||
|
|
BigDecimal _decimal = new BigDecimal(value);
|
||
|
|
formatValue = decimalFormat.format(_decimal);
|
||
|
|
}catch (Exception e){
|
||
|
|
|
||
|
|
}
|
||
|
|
return formatValue;
|
||
|
|
}
|
||
|
|
%>
|
||
|
|
|
||
|
|
|
||
|
|
|