package com.yolotone.report.spweekly;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormatSymbols;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import com.xuanzhi.ms.server.logic.operator.Operator;
import com.xuanzhi.ms.server.logic.operator.concrete.XmlOperatorNodeManager;
import com.xuanzhi.userstat.dailydata.MailUtil;
import com.xuanzhi.userstat.feestat.projects.ProjectConnectionManagerForOracle;
public class getSpContent {
private ProjectConnectionManagerForOracle connectionPoolManager = ProjectConnectionManagerForOracle.getInstance();
private Connection com = null;
private Statement stat = null;
private ResultSet rs = null;
private SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
/**
* 根据时间 sp名称
* @param beginDate
* @param endDate
* @return 此sp在此时间段的收入
* @throws ParseException
*/
public String getSpByDateAndSp(String beginDate,String endDate,String sp) throws ParseException
{
String beginyear = beginDate.substring(0,4);
String endyear = endDate.substring(0,4);
String beginMonth = beginDate.substring(4,6);
String endMonth = endDate.substring(4,6);
String sql = "";
String revenue = "0";
if(beginMonth.equalsIgnoreCase(endMonth) && beginyear.equalsIgnoreCase(endyear))
{
String month = beginDate.substring(0,4)+"_"+beginDate.substring(4,6);
sql = "select sum(t.revenue) from t_hour_"+
month+" t where operator = '"+sp+"' and day between "+beginDate+" and "+endDate;
}
else
{
String beginmonth = beginDate.substring(0,4)+"_"+beginDate.substring(4,6);
String endmonth = endDate.substring(0,4)+"_"+endDate.substring(4,6);
Calendar begincal = Calendar.getInstance();
begincal.setTime(format.parse(beginDate));
int maxday = begincal.getActualMaximum(Calendar.DAY_OF_MONTH); //获取当前月最大日期
String beginmaxDate = beginDate.substring(0,6)+maxday; //得到开始日期的结束日期
String endmindate = endDate.substring(0,6)+"01"; //得到结束日期的开始日期
sql = "select sum(t.revenue) from t_hour_"+beginmonth+" t where operator = '"+sp+"' and day between "+beginDate+" and "+beginmaxDate +
" union " +
"select sum(t.revenue) from t_hour_"+endmonth+" t where operator = '"+sp+"' and day between "+endmindate+" and "+endDate;
}
try {
com = connectionPoolManager.getConnection();
stat = com.createStatement();
rs = stat.executeQuery(sql);
Map<String,String> spmap = null;
while(rs.next())
{
revenue = rs.getString(1)==null?"0":rs.getString(1);
}
} catch (Exception e) {
// TODO: handle exception
System.out.println(e.getMessage());
}
finally
{
if(rs!=null)
try {
rs.close();
} catch (SQLException e1) {}
if(stat!=null)
try {
stat.close();
} catch (SQLException e1) {}
if(com!=null)
try {
com.close();
} catch (SQLException e) {}
}
//System.out.println("sql "+sql);
return revenue;
}
/**
* 传入日期 获取日期之前 每周liu是多少号
* @param date 日期
* @return
* @throws ParseException
*/
private List<String> getWeekDay(String datestr) throws ParseException
{
String yyyyMM = datestr.substring(0,6);
LinkedList<String> daylist = new LinkedList<String>();
Calendar calendar = Calendar.getInstance();
Date date = format.parse(datestr);
calendar.setTime(date);
//int maxday = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
int dayOfWeek = calendar.get(Calendar.DAY_OF_WEEK); //获取今天是星期几 1:星期日,2:星期一,3:星期二,4:星期三,5:星期四,6:星期五,7:星期六
int day = date.getDate();//获取今天是几号
//System.out.println("今天是"+day+"号");
int ri = day - dayOfWeek; //获取上周liu是几号
if(String.valueOf(ri).length()<2)
{
daylist.addFirst(yyyyMM+"0"+ri);
}
daylist.addFirst(yyyyMM+ri); //将上周六de日期加入集合中
int pri = ri - 7;//判断还够不够一周
while(pri>0)
{
daylist.addFirst(String.valueOf(pri).length()<2 ? yyyyMM+"0"+pri : yyyyMM+pri);
pri = pri-7;
}
return daylist;
}
/**
* 判断今天 是否 是本月第一天 并且 是星期一
* @return
* @throws ParseException
*/
public List<String> getWeekDays(Date date) throws ParseException
{
//Date date = format.parse(datestr);
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
if(date.getDate()-1>0)
{
int maxday = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
calendar.set(Calendar.DATE, maxday);
}
else
{
calendar.set(Calendar.DATE, 1);
calendar.add(Calendar.DATE, -1);
}
return this.getWeekDay(format.format(calendar.getTime()));
}
/**
* 获取通道信息（spId,spName）
* @return
*/
public List<Map<String,String>> getCp()
{
XmlOperatorNodeManager onm = XmlOperatorNodeManager.getInstance();
Operator[] opers = onm.getOperators();
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
Map<String,String> map = null;
for(int i =0 ;i<opers.length;i++)
{
map = new HashMap<String,String>();
map.put("spId",opers[i].getSpId());
//String spName = opers[i].getDisplayName().substring(0,(opers[i].getDisplayName().length())-(opers[i].getSpId().length()));
//System.out.println(opers[i].getDisplayName());
//map.put("spName",spName);
map.put("spName", opers[i].getDisplayName());
list.add(map);
}
return list;
}
public Map<String,String> MakeTable(String spId,List<String> datestr)
{
Map<String,String> spmap = new HashMap<String, String>();
double jiezhiRevenue = 0d;
spmap.put("spId", spId);
try {
for (int i = 0; i < datestr.size(); i++) {
//获取开始日期 结束日期
Map<String,String> datemap = this.getbeginDateAndendDate(format.parse(datestr.get(i)));
String begindate = datemap.get("beginDate"); //开始日期
String enddate = datemap.get("endDate"); //结束日期
String value = this.getSpByDateAndSp(begindate, enddate, spId); //获取当前spId在这个时间段中那个的收入
jiezhiRevenue += value == null ? 0 : Double.parseDouble(value);
spmap.put(spId+"_"+i, value == null ? "0" : value);
}
spmap.put("jiezhiRevenue", String.valueOf(jiezhiRevenue));
return spmap;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
System.out.println("MakeTable error:"+e.getMessage());
return null;
}
}
/**
* 返回需要发送的表格
* @return
*/
public String sendMail(String filePath)
{
try {
//getClass().getResource("/");
List<Map<String,String>> exlist = this.getExcelContent(filePath);
List<String> datestr = this.getWeekDays(new Date());
int datestrlength = datestr.size();
StringBuffer sb = new StringBuffer();
sb.append("<table style='text-align:center;font-size:14px' border='1' bordercolor='#000000' cellpadding='2' cellspacing='0'>");
sb.append("<tr bgcolor = '#C2D69A'><td nowrap rowspan='2'>商务负责人</td><td nowrap rowspan='2'>SP名称</td><td nowrap rowspan='2'>收入目标</td>");
sb.append("<td nowrap rowspan='2'>截止日总信息费</td><td nowrap rowspan='2'>收入进度</td>");
for (int i = 0; i < datestrlength; i++) {
Map<String,String> datemap = this.getbeginDateAndendDate(format.parse(datestr.get(i)));
String begindate = datemap.get("beginDate");
String enddate = datemap.get("endDate");
sb.append("<td nowrap colspan='2'>第"+(i+1)+"周("+begindate+"-"+enddate+")</td>");
}
sb.append("</tr><tr bgcolor = '#C2D69A'>");
for (int i = 0; i < datestrlength; i++) {
sb.append("<td>总信息费</td><td>收入占比</td>");
}
sb.append("</tr>");
//汇总
String pson = ""; //负责人
int sumtatget = 0; //汇总 收入目标
double jiezhisumtarget = 0d; //汇总 截止日收入
Map<Integer,Double> summsgtarget = null; //按周汇总收入
//总计
int max_sumtarget = 0; //总计 收入目标
double max_jiezhisumtarget = 0; //总计 截至收入
Map<Integer,Double> max_summsgtarget = new HashMap<Integer, Double>(); //总计 每周总收入
int m =0;
for (Map<String, String> map : exlist) {
m++;
//System.out.println("exlist:"+exlist.size());
//System.out.println(m);
String person = map.get("person");
String spName = map.get("spName");
String target = map.get("target");
if(pson == null || pson == "")
{
pson = person;
sumtatget += Integer.parseInt(target);
summsgtarget = new HashMap<Integer,Double>(); //按周汇总收入
}
else if(pson!="")
{
if(pson!= person && person != null && person.length() > 0)
{
sb.append("<tr bgcolor = '#D6D6D6' ><td colspan='2'>共计</td><td>"+sumtatget+"</td><td>"+jiezhisumtarget+"</td><td>"+formatRevenue(Double.valueOf(jiezhisumtarget),Double.valueOf(sumtatget))+"%</td>");
for (int i = 0; i < datestrlength; i++) {
sb.append("<td>"+summsgtarget.get(i)+"</td><td>"+formatRevenue(Double.valueOf(summsgtarget.get(i)),Double.valueOf(sumtatget))+"%</td>");
if(max_summsgtarget.get(i)!=null)
{
double vl = summsgtarget.get(i)+max_summsgtarget.get(i);
max_summsgtarget.put(i, vl);
}
else
{
max_summsgtarget.put(i, summsgtarget.get(i));
}
}
sb.append("</tr>");
//总计
max_sumtarget += sumtatget;
max_jiezhisumtarget += jiezhisumtarget;
//汇总
summsgtarget = new HashMap<Integer,Double>(); //按周汇总收入
pson = person; //负责人
sumtatget = Integer.parseInt(target); //汇总收入目标
jiezhisumtarget = 0d;
}
else
{
sumtatget += Integer.parseInt(target);
}
}
Map<String,String> spsmap = new HashMap<String, String>();
Map<Integer,Double> spvalue = new HashMap<Integer, Double>();
List<Map<String,String>> cplist = this.getCp();
for (int p=0;p<cplist.size();p++) {
Map<String,String> cpmap = cplist.get(p);
String cpId = cpmap.get("spId");
String cpName = cpmap.get("spName");
String cpName1 = cpName.substring(0,(cpName.length())-(cpId.length()));
//System.out.println("spName: "+spName+"cpName "+cpName1);
if(spName.equalsIgnoreCase(cpName1))
{
Map<String,String> spmap = this.MakeTable(cpId, datestr);
String jiezhi = spmap.get("jiezhiRevenue"); //截止日期总收入
if(jiezhi.indexOf(".")>0)
{
jiezhi = jiezhi.substring(0,jiezhi.indexOf(".")+2);
}
jiezhisumtarget += Double.parseDouble(jiezhi); //汇总截至收入
if(spsmap.get("person")!=null)
{
double jz = Double.parseDouble(spsmap.get("jiezhi"))+Double.parseDouble(jiezhi);
spsmap.put("jiezhi", String.valueOf(jz));
for (int i = 0; i < datestrlength; i++) {
String value = spmap.get(cpId+"_"+i);
double vl = Double.parseDouble(value) + spvalue.get(i);
spvalue.put(i, vl);
}
}
else
{
spsmap.put("person", person);
spsmap.put("spName", spName);
spsmap.put("target", target);
spsmap.put("jiezhi", jiezhi);
for (int i = 0; i < datestrlength; i++) {
String value = spmap.get(cpId+"_"+i);
spvalue.put(i, Double.parseDouble(value));
}
}
if(p==this.getCp().size()-1)
{
sb.append("<tr><td>"+spsmap.get("person")+"</td><td>"+spsmap.get("spName")+"</td><td>"+spsmap.get("target")+"</td><td>"+spsmap.get("jiezhi")+"</td><td>"+formatRevenue(Double.valueOf(spsmap.get("jiezhi")),Double.valueOf(spsmap.get("target")))+"%</td>");
for (int i = 0; i < datestrlength; i++) {
double value = spvalue.get(i);
sb.append("<td>"+value+"</td><td>"+formatRevenue(Double.valueOf(value),Double.valueOf(target))+"%</td>");
if(summsgtarget.get(i)!=null)
{
double vl = value + summsgtarget.get(i);
summsgtarget.put(i, vl);
}
else
{
summsgtarget.put(i,value);
}
}
sb.append("</tr>");
break;
}
}
else if(!spName.equalsIgnoreCase(cpName1) && p==this.getCp().size()-1)
{
if(spsmap!=null && spsmap.size()>0)
{
sb.append("<tr><td>"+spsmap.get("person")+"</td><td>"+spsmap.get("spName")+"</td><td>"+spsmap.get("target")+"</td><td>"+spsmap.get("jiezhi")+"</td><td>"+formatRevenue(Double.valueOf(spsmap.get("jiezhi")),Double.valueOf(spsmap.get("target")))+"%</td>");//
for (int i = 0; i < datestrlength; i++) {
double value = spvalue.get(i);
sb.append("<td>"+value+"</td><td>"+formatRevenue(Double.valueOf(value),Double.valueOf(target))+"%</td>");
if(summsgtarget.get(i)!=null)
{
double vl = value + summsgtarget.get(i);
summsgtarget.put(i, vl);
}
else
{
summsgtarget.put(i,value);
}
}
sb.append("</tr>");
}
else
{
sb.append("<tr><td>"+person+"</td><td>"+spName+"</td><td>"+target+"</td><td>0</td><td>0%</td>");
for (int i = 0; i < datestrlength; i++) {
//String value = spmap.get(cpId+"_"+i);
sb.append("<td>0</td><td>0%</td>");
if(summsgtarget.get(i)!=null)
{
double vl = Double.parseDouble("0") + summsgtarget.get(i);
summsgtarget.put(i, vl);
}
else
{
summsgtarget.put(i, Double.parseDouble("0"));
}
}
sb.append("</tr>");
break;
}
}
}
if(m==exlist.size()) //判断是否是最后一条
{
sb.append("<tr bgColor='#D6D6D6'><td colspan='2'>共计</td><td>"+sumtatget+"</td><td>"+jiezhisumtarget+"</td><td>"+formatRevenue(Double.valueOf(jiezhisumtarget),Double.valueOf(sumtatget))+"%</td>");
for (int i = 0; i < datestrlength; i++) {
sb.append("<td>"+summsgtarget.get(i)+"</td><td>"+formatRevenue(Double.valueOf(summsgtarget.get(i)),Double.valueOf(sumtatget))+"%</td>");
if(max_summsgtarget.get(i)!=null)
{
double vl = summsgtarget.get(i)+max_summsgtarget.get(i);
max_summsgtarget.put(i, vl);
}
else
{
max_summsgtarget.put(i, summsgtarget.get(i));
}
}
sb.append("</tr>");
//总计
max_sumtarget += sumtatget;
max_jiezhisumtarget += jiezhisumtarget;
}
}
//总计
sb.append("<tr bgColor='#919191'><td colspan='2'>总计</td><td>"+max_sumtarget+"</td><td>"+max_jiezhisumtarget+"</td><td>"+formatRevenue(Double.valueOf(max_jiezhisumtarget),Double.valueOf(max_sumtarget))+"%</td>");
for (int i = 0; i < datestrlength; i++) {
sb.append("<td>"+max_summsgtarget.get(i)+"</td><td>"+formatRevenue(Double.valueOf(max_summsgtarget.get(i)),Double.valueOf(max_sumtarget))+"%</td>");
}
sb.append("</tr>");
sb.append("</table>");
return sb.toString();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
System.out.println("sendMail error:"+e.getMessage());
return "发送邮件出错啦！~！ 请联系技术人员！~！";
}
}
/**
* 传入文件路径
* @param filePath
* @return 返回集合List<Map<负责人,sp名称,收入指标>>
*/
public List<Map<String,String>> getExcelContent(String filePath)
{
InputStream is = null;
Workbook book = null;
List<Map<String,String>> exlist = new ArrayList<Map<String,String>>();
Map<String,String> exmap = null;
try {
is = new FileInputStream(filePath);
book = Workbook.getWorkbook(is);
Sheet sheet = book.getSheet(0);
int column = sheet.getColumns(); //列
int row = sheet.getRows(); //行
for (int i = 1; i < row; i++) {
exmap = new HashMap<String, String>();
for (int j = 0; j < column; j++) {
Cell cell = sheet.getCell(j,i);
if(j==0)
{
exmap.put("person", cell.getContents());
}
else if(j==1)
{
exmap.put("spName", cell.getContents());
}
else if(j==2)
{
String target = "0";
try {
target = Integer.toString(Integer.parseInt(cell.getContents().replace(",", "")));
} catch (Exception e) {
e.printStackTrace();
}
exmap.put("target", target);
}
//System.out.println(cell.getContents());
}
exlist.add(exmap);
}
return exlist;
} catch (Exception e) {
// TODO: handle exception
System.out.println("get File method error :"+e.getMessage());
return null;
}
}
/**
* 传入今天日期
* @param date
* @return 返回本日所在周的开始日期 结束日期
*/
public Map<String,String> getbeginDateAndendDate(Date date)
{
Map<String,String> datemap = new HashMap<String, String>();
SimpleDateFormat ff = new SimpleDateFormat("yyyyMMdd");
Calendar cal = Calendar.getInstance();
cal.setTime(date);
int day_of_week = cal.get(Calendar.DAY_OF_WEEK) - 2;
cal.add(Calendar.DATE, -day_of_week);
datemap.put("beginDate", ff.format(cal.getTime()));
cal.add(Calendar.DATE, 6);
datemap.put("endDate", ff.format(cal.getTime()));
return datemap;
}
//格式化字符串
public String formatRevenue(double vl1,double vl2)
{
DecimalFormat df = new DecimalFormat("###.##");
double va1 = (vl1 / vl2)*100;
return df.format(va1);
}
public static void main(String[] args) throws ParseException {
//getSpContent gs = new getSpContent();
//MailUtil.sendMail("mengfanzhi@yolotone.com","","", "测试",gs.MakeTable() , null);
//gs.showTest();
// double aa = Double.valueOf("337730");
// double bb = Double.valueOf("24000000");
// double a = aa / bb;
// System.out.println(a);
System.out.println(System.getProperty("user.dir"));
System.out.println(Integer.parseInt("234d"));
}
}
|
