659 lines
24 KiB
Plaintext
659 lines
24 KiB
Plaintext
<%@ 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.math.RoundingMode" %>
|
||
<%@ page import="com.api.develop.service.growup.GrowUpListService" %>
|
||
<%@ page import="java.text.DecimalFormat" %>
|
||
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
|
||
|
||
<%
|
||
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) ;
|
||
}
|
||
|
||
|
||
RecordSet rs = new RecordSet();
|
||
int userid = user.getUID();
|
||
|
||
|
||
String posttype = "" ;
|
||
|
||
|
||
List<Map<String,Object>> list = getProjectPerson(user,begindate,enddate,year);
|
||
|
||
JSONObject jsonObject = new JSONObject();
|
||
jsonObject.put("data",list);
|
||
jsonObject.put("posttype",posttype);
|
||
jsonObject.put("userid",user.getUID());
|
||
jsonObject.put("lastname",user.getLastname());
|
||
%>
|
||
<%=jsonObject.toString() %>
|
||
|
||
<%!
|
||
|
||
/***
|
||
* 项目显示6个，内容：验收指标、验收金额、指标达成率、收款金额、验收项目个数、平均项目周期；
|
||
*/
|
||
public List<Map<String,Object>> getProjectPerson(User user,String begindate, String enddate,int year){
|
||
|
||
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
|
||
int userid = user.getUID();
|
||
|
||
Map<String,Object> map = null ;
|
||
double yszb = 0.00;
|
||
try {
|
||
yszb = getAcceptanceIndex(userid,year);
|
||
}catch (Exception e){
|
||
}
|
||
|
||
if(yszb > 0){
|
||
BigDecimal bd = new BigDecimal(yszb/10000.00);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
yszb = bd.doubleValue();
|
||
}
|
||
|
||
String unit = "" ;
|
||
String xmyszb = "" ;
|
||
if(yszb < 10000){
|
||
unit = "万元";
|
||
BigDecimal bd = new BigDecimal(yszb);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
xmyszb = bd.toString();
|
||
|
||
}else{
|
||
unit = "亿元";
|
||
BigDecimal bd = new BigDecimal(yszb/10000.00);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
xmyszb = bd.toString();
|
||
}
|
||
|
||
map = new HashMap<String,Object>();
|
||
map.put("name","yszb");
|
||
map.put("value",xmyszb);
|
||
map.put("title","本年验收指标");
|
||
map.put("unit",unit);
|
||
map.put("url","/cm/data/SearchFrame.jsp?tablename=xmzb");
|
||
list.add(map);
|
||
|
||
double ysje = 0.00 ;
|
||
// try {
|
||
// ysje = getAcceptanceAmount(user,year,begindate,enddate);
|
||
// }catch (Exception e){
|
||
// }
|
||
|
||
String bnjhys = "";
|
||
String jhyswcl = "";
|
||
try {
|
||
//double bnwcys = 0.00;
|
||
// double bnjhys = 0.00;
|
||
// double jhyswcl = 0.00;
|
||
|
||
Map<String,Object> dataMap = getYsAndJhwcl(userid,begindate,enddate);
|
||
ysje = Util.getDoubleValue(Util.null2String(dataMap.get("bnwcys")));
|
||
bnjhys = Util.null2String(dataMap.get("bnjhys"));
|
||
jhyswcl = Util.null2String(dataMap.get("jhyswcl"));
|
||
}catch (Exception e){
|
||
}
|
||
|
||
|
||
unit = "" ;
|
||
String xmysje = "" ;
|
||
if(ysje < 10000){
|
||
unit = "万元";
|
||
BigDecimal bd = new BigDecimal(ysje);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
xmysje = bd.toString();
|
||
|
||
}else{
|
||
unit = "亿元";
|
||
BigDecimal bd = new BigDecimal(ysje/10000.00);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
xmysje = bd.toString();
|
||
}
|
||
|
||
map = new HashMap<String,Object>();
|
||
map.put("name","ysje");
|
||
map.put("value",xmysje+"");
|
||
map.put("title","本年验收金额");
|
||
map.put("unit",unit);
|
||
map.put("url","/cm/data/SearchFrame.jsp?tablename=xmys");
|
||
list.add(map);
|
||
|
||
|
||
map = new HashMap<String,Object>();
|
||
map.put("name","bnjhys");
|
||
map.put("value",bnjhys+"");
|
||
map.put("title","本年计划验收");
|
||
map.put("unit","");
|
||
map.put("url","");
|
||
list.add(map);
|
||
|
||
map = new HashMap<String,Object>();
|
||
map.put("name","jhyswcl");
|
||
map.put("value",jhyswcl+"");
|
||
map.put("title","计划验收完成率");
|
||
map.put("unit","");
|
||
map.put("url","");
|
||
list.add(map);
|
||
|
||
String zbdcl = "0.00" ;
|
||
try{
|
||
if(yszb > 0){
|
||
BigDecimal bd = new BigDecimal((ysje/(yszb*1.00))*100.00);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
zbdcl = bd.toString()+"";
|
||
}
|
||
}catch (Exception e){
|
||
|
||
}
|
||
map = new HashMap<String,Object>();
|
||
map.put("name","zbdcl");
|
||
map.put("value",zbdcl);
|
||
map.put("title","本年指标达成率");
|
||
map.put("unit","%");
|
||
map.put("url","");
|
||
list.add(map);
|
||
|
||
|
||
double skje = 0.00 ;
|
||
try{
|
||
// skje = getPaymentsAmount(user,year,begindate,enddate);
|
||
skje = getWCSKDataNew(userid,begindate,enddate);
|
||
|
||
}catch (Exception e){
|
||
|
||
}
|
||
unit = "" ;
|
||
String xmskje = "" ;
|
||
if(skje < 10000){
|
||
unit = "万元";
|
||
BigDecimal bd = new BigDecimal(skje);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
xmskje = bd.toString();
|
||
|
||
}else{
|
||
unit = "亿元";
|
||
BigDecimal bd = new BigDecimal(skje/10000.00);
|
||
bd = bd.setScale(2, RoundingMode.HALF_UP);
|
||
xmskje = bd.toString();
|
||
}
|
||
map = new HashMap<String,Object>();
|
||
map.put("name","skje");
|
||
map.put("value",xmskje+"");
|
||
map.put("title","本年收款金额");
|
||
map.put("unit",unit);
|
||
map.put("url","/cm/data/SearchFrame.jsp?tablename=sk");
|
||
list.add(map);
|
||
|
||
// String ysxmgs = "0";
|
||
// String pjxmzq = "0.00";
|
||
// try{
|
||
// Map<String,Object> xmMap = getProjectCheckCountAndAveragePeriod(userid,year,begindate,enddate);
|
||
// ysxmgs = Util.null2String(xmMap.get("ysxmgs"));
|
||
// String pjxmyszq = Util.null2String(xmMap.get("pjxmzq"));
|
||
//
|
||
// if(StringUtils.isNotEmpty(pjxmyszq)){
|
||
// pjxmzq = formatBigDecimal(pjxmyszq);
|
||
// }
|
||
// }catch (Exception e){
|
||
//
|
||
// }
|
||
// map = new HashMap<String,Object>();
|
||
// map.put("name","ysxmgs");
|
||
// map.put("value",ysxmgs);
|
||
// map.put("title","验收项目个数");
|
||
// map.put("unit","");
|
||
// map.put("url","");
|
||
// list.add(map);
|
||
//
|
||
// map = new HashMap<String,Object>();
|
||
// map.put("name","ysxmgs");
|
||
// map.put("value",pjxmzq);
|
||
// map.put("title","平均项目周期");
|
||
// map.put("unit","天");
|
||
// map.put("url","");
|
||
// list.add(map);
|
||
return list;
|
||
}
|
||
|
||
|
||
/////##########################项目人员##################################//
|
||
/**
|
||
* 验收指标
|
||
* @param cyear
|
||
* @param hrmid
|
||
* @return
|
||
*/
|
||
public double getAcceptanceIndex(int hrmid,int cyear){
|
||
double yszb = 0.00 ;
|
||
BaseBean bb = new BaseBean();
|
||
bb.writeLog("getAcceptanceIndex");
|
||
RecordSet rs = new RecordSet();
|
||
|
||
// String userids = "" ;
|
||
// String sql = " select * from getallchilds("+hrmid+")" ;
|
||
// rs.executeQuery(sql);
|
||
// while (rs.next()){
|
||
// String userid = rs.getString(1);
|
||
// userids += StringUtils.isEmpty(userids) ? userid : ","+userid ;
|
||
// }
|
||
//
|
||
// StringBuffer sqlWhere = new StringBuffer();
|
||
// sqlWhere.append(" and t1.hsyjnf like '"+cyear+"%'");
|
||
// sqlWhere.append(" and (t1.xmjl in("+userids+") or t1.tdfzr in("+userids+") )");
|
||
//
|
||
// String tablename = "" ;
|
||
// sql = " select bm from uf_cm_right_detail where sign='xmzb' ";
|
||
// bb.writeLog("sql:"+sql);
|
||
// rs.executeQuery(sql);
|
||
// if(rs.next()){
|
||
// tablename = Util.null2String(rs.getString("bm"));
|
||
// }
|
||
// if(StringUtils.isNotEmpty(tablename)){
|
||
// sql =" select sum(yszb) as yszb from "+tablename + " t1 where 1=1 "+sqlWhere.toString();
|
||
// bb.writeLog("sql:"+sql);
|
||
// rs.executeQuery(sql);
|
||
// if(rs.next()){
|
||
// yszb = Util.getDoubleValue(Util.null2String(rs.getString("yszb")));
|
||
// }
|
||
// }
|
||
|
||
try{
|
||
String sql = " select isnull(sum(convert(decimal(18,2),target)),0) as target from lf_prjhrminfo where hrmid ="+hrmid;
|
||
rs.execute(sql);
|
||
if(rs.next()){
|
||
yszb = Util.getDoubleValue(Util.null2String(rs.getString("target")));
|
||
}
|
||
}catch (Exception e){
|
||
bb.writeLog("getSalesTarget-e:"+e);
|
||
}
|
||
|
||
bb.writeLog("yszb:"+yszb);
|
||
return yszb;
|
||
}
|
||
|
||
/***
|
||
* 项目验收金额
|
||
* @param user
|
||
* @param year
|
||
* @param startDate
|
||
* @param endDate
|
||
* @return
|
||
*/
|
||
public double getAcceptanceAmount(User user,int year,String startDate,String endDate){
|
||
BaseBean bb = new BaseBean();
|
||
bb.writeLog("getAcceptanceAmount");
|
||
double _total = 0.00;//总数
|
||
int userid = user.getUID();
|
||
GrowUpListService service = new GrowUpListService();
|
||
//https://www.e-cology.com.cn/api/develop/growup/getData?season=&month=&week=&day=&
|
||
// year=2022&isDecimal=1&grouptype=&datatype=&type=performances&hrmid=5796
|
||
try {
|
||
Map<String,Object> params = new HashMap<String,Object>();
|
||
params.put("year",year);
|
||
params.put("hrmid",userid);
|
||
params.put("type","projacceptance");
|
||
params.put("isDecimal","1");
|
||
params.put("fromdate",startDate);
|
||
params.put("todate",endDate);
|
||
|
||
Map<String,Object> apidatas = service.getProjacceptance(user, params);
|
||
bb.writeLog(JSONObject.toJSONString(apidatas));
|
||
|
||
if(apidatas.containsKey("status") && apidatas.containsKey("num")){
|
||
String status = Util.null2String(apidatas.get("status"));
|
||
if("1".equals(status)){
|
||
_total = Util.getDoubleValue(Util.null2String(apidatas.get("num")));
|
||
}
|
||
}
|
||
bb.writeLog("_total:"+_total);
|
||
}catch (Exception e) {
|
||
bb.writeLog("getCustomerCount-e:"+e);
|
||
}
|
||
return _total;
|
||
}
|
||
|
||
|
||
//本年完成验收、计划完成率、本年计划验收
|
||
public Map<String,Object> getYsAndJhwcl(int userid,String startdate,String endate)
|
||
{
|
||
Map<String,Object> map = new HashMap<String, Object>();
|
||
RecordSet rs = new RecordSet();
|
||
try {
|
||
|
||
double bnwcys = 0.00;
|
||
double bnjhys = 0.00;
|
||
double jhyswcl = 0.00;
|
||
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("bnwcys", MathUtil.div(bnwcys,10000.0,2));
|
||
map.put("jhyswcl", jhyswcl);
|
||
map.put("bnjhys", MathUtil.div(bnjhys,10000.0,2));
|
||
|
||
}catch (Exception e) {
|
||
e.printStackTrace();
|
||
}
|
||
return map;
|
||
}
|
||
|
||
|
||
/***
|
||
* 项目收款金额
|
||
* @param user
|
||
* @param year
|
||
* @param startDate
|
||
* @param endDate
|
||
* @return
|
||
*/
|
||
public double getPaymentsAmount(User user,int year,String startDate,String endDate){
|
||
BaseBean bb = new BaseBean();
|
||
bb.writeLog("getPaymentsAmount");
|
||
|
||
double _total = 0.00;//总数
|
||
int userid = user.getUID();
|
||
GrowUpListService service = new GrowUpListService();
|
||
//https://www.e-cology.com.cn/api/develop/growup/getData?season=&month=&week=&day=&
|
||
// year=2022&isDecimal=1&grouptype=&datatype=&type=performances&hrmid=5796
|
||
try {
|
||
Map<String,Object> params = new HashMap<String,Object>();
|
||
params.put("year",year);
|
||
params.put("hrmid",userid);
|
||
params.put("type","projpayments");
|
||
params.put("isDecimal","1");
|
||
params.put("fromdate",startDate);
|
||
params.put("todate",endDate);
|
||
|
||
Map<String,Object> apidatas = service.getProjpayments(user, params);
|
||
bb.writeLog(JSONObject.toJSONString(apidatas));
|
||
|
||
if(apidatas.containsKey("status") && apidatas.containsKey("num")){
|
||
String status = Util.null2String(apidatas.get("status"));
|
||
if("1".equals(status)){
|
||
_total = Util.getDoubleValue(Util.null2String(apidatas.get("num")));
|
||
}
|
||
}
|
||
|
||
bb.writeLog("_total:"+_total);
|
||
}catch (Exception e) {
|
||
bb.writeLog("getCustomerCount-e:"+e);
|
||
}
|
||
return _total;
|
||
}
|
||
|
||
|
||
/***
|
||
* 项目验收个数，项目验收平均周期
|
||
* @param userid
|
||
* @param startdate
|
||
* @param enddate
|
||
* @return
|
||
*/
|
||
public Map<String,Object> getProjectCheckCountAndAveragePeriod(int userid,int year,String startdate,String enddate)
|
||
{
|
||
BaseBean bb = new BaseBean();
|
||
bb.writeLog("getProjectCheckCountAndAveragePeriod:");
|
||
RecordSet rs = new RecordSet();
|
||
/******************完成收款****************/
|
||
String ysxmgs = "0" ;
|
||
String pjxmzq = "0.00" ;
|
||
Map<String,Object> map = new HashMap<String,Object>();
|
||
try{
|
||
|
||
// String userids = userid+"";
|
||
// String sql =" with tem_table(hrm_id,last_name,manager_id,curlevel) as \n" +
|
||
// " (select id,lastname,managerid,1 as level " +
|
||
// " from HrmResource " +
|
||
// " where id=" + userid +
|
||
// " union all\n" +
|
||
// " select a.id,a.lastname,a.managerid,b.curlevel+1 " +
|
||
// " from HrmResource a " +
|
||
// " inner join tem_table b on (a.managerid = b.hrm_id) \n" +
|
||
// " )\n" +
|
||
// " select * from tem_table" ;
|
||
// rs.execute(sql);
|
||
// while (rs.next()){
|
||
// String hrmid = Util.null2String(rs.getString("hrm_id"));
|
||
// userids += userids == "" ? hrmid : ","+hrmid ;
|
||
// }
|
||
|
||
String userWhere = " and (t3t.hrmid in("+userid+") or t3t.qyjl in("+userid+") or t3t.dqzj in("+userid+") or t3t.qyzj in("+userid+")) " ;
|
||
|
||
String sql =" select convert(decimal(18,2),isnull(sum(datediff(Day,cbi.planstartdate,cbi.gdrq)),0)/(case when count(1)=0 then 1.0 else count(1)*1.0 end)) as pjxmzq,isnull(count(1),0) as ysxmgs " +
|
||
" from proj_CardBaseInfo cbi " +
|
||
" left join lf_prjhrminfo t3t on t3t.hrmid = cbi.projmanager " +
|
||
" where cbi.gdrq >= '"+startdate+"' AND cbi.gdrq <= '"+enddate+"' " +
|
||
" and cbi.gdrq IS NOT NULL AND cbi.gdrq <> '' " +
|
||
" and cbi.planstartdate is not null and cbi.planstartdate <> '' " +
|
||
" and cbi.isYanshou=1 "+userWhere;
|
||
|
||
bb.writeLog("sql:"+sql);
|
||
rs.executeQuery(sql);
|
||
if(rs.next()){
|
||
pjxmzq = Util.null2String(rs.getString("pjxmzq"));
|
||
ysxmgs = Util.null2String(rs.getString("ysxmgs"));
|
||
}
|
||
}catch (Exception e){
|
||
|
||
}
|
||
|
||
bb.writeLog("pjxmzq:"+pjxmzq);
|
||
bb.writeLog("ysxmgs:"+ysxmgs);
|
||
|
||
map.put("ysxmgs",ysxmgs);
|
||
map.put("pjxmzq",pjxmzq);
|
||
return map;
|
||
}
|
||
|
||
|
||
/////##########################项目人员##################################//
|
||
|
||
/***
|
||
*
|
||
* @param userids
|
||
* @param startdate
|
||
* @param enddate
|
||
* @return
|
||
*/
|
||
public String queryReportWhereWithDate(String userids,String startdate,String enddate) {
|
||
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 = enddate.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 enddate
|
||
* @return
|
||
*/
|
||
public double getWCSKDataNew(int userid,String startdate,String enddate)
|
||
{
|
||
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(enddate))
|
||
{
|
||
datewhere = " and cbiead.inDate >='"+startdate+"' and cbiead.inDate <='"+enddate+"'";
|
||
}
|
||
//bb.writeLog("where:"+where);
|
||
String sql =" select Convert(decimal(18,2),isnull((sum(isnull(w.inAmt,0))/10000.0),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"),"0"),0.00);
|
||
}
|
||
}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;
|
||
}
|
||
%>
|
||
|
||
|
||
|