package com.netbusscheme.reportcenter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.netbusscheme.InStock.TabStorageRecord;
import com.netbusscheme.sales.TabSellProjectinfo;
import com.netbusscheme.service.ConnectDao;
public class ReportCenterDao {
private Transaction tx = null;
private Session session = null;
public ReportCenterDao(){};
public List getWuliatoBrand(String onlycode,String[] comid){
List list=null;
try{
String str = "",arr="";
for(int i=0;i<comid.length;i++){
if(i==comid.length-1)
str += comid[i];
else str += comid[i]+",";
}
session = ConnectDao.getSession(onlycode);
String hql = "from TabKcStorage t where t.tabplace.BCompanyinfo.unitId in ("+str+") order by t.cbrandname";
Query query = session.createQuery(hql);
list = query.list();
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定年份每月的销售情况
* @param onlycode
* @param year
* @param company
* @param employee
* @return
*/
public List getMonthSaleMoneyByCom(String onlycode,String year,String[] company,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(to_number(t.cax)),count(t.retailcode),trunc(t.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",(select c.unit_name from B_Companyinfo c where c.unit_id=t.comcode),t.comcode";
}
sql +=" from tab_ls_retail t where 1=1";
if(!year.equals(""))
sql += " and extract(year from t.reretaildate)='"+year+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.comcode in "+str;
}else{
sql += " and 1=0";
}
sql += " group by trunc(t.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",t.comcode";
}
sql += " order by trunc(t.reretaildate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计员工指定年份每月的销售情况
* @param onlycode
* @param year
* @param company
* @param employee
* @return
*/
public List getMonthSaleMoneyByEmp(String onlycode,String year,String[] company,String comflag,String[] employee,String empflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(to_number(t.cax)),count(t.retailcode),trunc(t.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",(select c.unit_name from B_Companyinfo c where c.unit_id=t.comcode),t.comcode";
}else if(empflag.equals("2")){
sql += ",(tt.firstname || tt.lastname),t.sellman";
}
sql +=" from tab_ls_retail t,tab_hr_employee tt where tt.empno=t.sellman";
if(!year.equals(""))
sql += " and extract(year from t.reretaildate)='"+year+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.comcode in "+str;
}
if(employee!=null){
String str = "";
for(int i=0;i<employee.length;i++){
if(i==0)
str += "("+employee[i];
else str += ","+employee[i];
if(i==employee.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.sellman in "+str;
}
sql += " group by trunc(t.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",t.comcode";
}else if(empflag.equals("2")){
sql += ",(tt.firstname || tt.lastname),t.sellman";
}
sql += " order by trunc(t.reretaildate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || empflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位所选员工");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型指定年份每月的销售情况
* @param onlycode
* @param year
* @param company
* @param employee
* @return
*/
public List getMonthSaleMoneyByType(String onlycode,String year,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))";
sql += ",sum(decode(t.selflag,null,(t.cnumber*(select t.cnumber from tab_selproduct tttt where tttt.selid=t.cunit)),t.cnumber))";
sql += ",trunc(r.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",(select c.unit_name from B_Companyinfo c where c.unit_id=r.comcode),r.comcode";
}else if(typeflag.equals("2")){
sql += ",t.typename";
}
sql +=" from tab_selproduct t,tab_ls_retail r where r.retailcode=t.retailcode and extract(year from r.reretaildate)='"+year+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and r.comcode in "+str;
}
if(type!=null){
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.typename in "+str;
}
sql += " group by trunc(r.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",r.comcode";
}else if(typeflag.equals("2")){
sql += ",t.typename";
}
sql += " order by trunc(r.reretaildate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌指定年份每月的销售情况
* @param onlycode
* @param year
* @param company
* @param employee
* @return
*/
public List getMonthSaleMoneyByBrand(String onlycode,String year,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))";
sql += ",sum(decode(t.selflag,null,(t.cnumber*(select t.cnumber from tab_selproduct tttt where tttt.selid=t.cunit)),t.cnumber))";
sql += ",trunc(r.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",(select c.unit_name from B_Companyinfo c where c.unit_id=r.comcode),r.comcode";
}else if(brandflag.equals("2")){
sql += ",s.cbrandname,s.cbrandcode";
}
sql +=" from tab_selproduct t,tab_ls_retail r,tab_kc_storage s where r.retailcode=t.retailcode and t.storano=s.id and extract(year from r.reretaildate)='"+year+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and r.comcode in "+str;
}
if(brand!=null){
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "('"+brand[i]+"'";
else str += ",'"+brand[i]+"'";
if(i==brand.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and s.cbrandcode in "+str;
}
sql += " group by trunc(r.reretaildate,'MM')";
if(comflag.equals("2")){
sql += ",r.comcode";
}else if(brandflag.equals("2")){
sql += ",s.cbrandname,s.cbrandcode";
}
sql += " order by trunc(r.reretaildate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定月份每天的销售情况
* @param onlycode
* @param month
* @param company
* @param employee
* @return
*/
public List getDaySaleMoneyByCom(String onlycode,String year,String month,String[] company,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(to_number(t.cax)),count(t.retailcode),trunc(t.reretaildate,'dd')";
if(comflag.equals("2")){
sql += ",t.storesales,t.comcode";
}
sql +=" from tab_ls_retail t where 1=1";
if(!month.equals(""))
sql += " and extract(year from t.reretaildate)='"+year+"' and extract(month from t.reretaildate)='"+month+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.comcode in "+str;
}
sql += " group by trunc(t.reretaildate,'dd')";
if(comflag.equals("2")){
sql += ",t.storesales,t.comcode";
}
sql += " order by trunc(t.reretaildate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计员工指定月份每天的销售情况
* @param onlycode
* @param month
* @param company
* @param employee
* @return
*/
public List getDaySaleMoneyByEmp(String onlycode,String year,String month,String[] company,String comflag,String[] employee,String empflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(to_number(t.cax)),count(t.retailcode),trunc(t.reretaildate,'dd')";
if(company!=null){
sql += ",t.storesales,t.comcode";
}else if(employee!=null){
sql += ",(select (tt.firstname || tt.lastname) from tab_hr_employee tt where tt.empno=t.sellman),t.sellman";
}
sql +=" from tab_ls_retail t where 1=1";
if(!month.equals(""))
sql += " and extract(year from t.reretaildate)='"+year+"' and extract(month from t.reretaildate)='"+month+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.comcode in "+str;
}else if(employee!=null){
String str = "";
for(int i=0;i<employee.length;i++){
if(i==0)
str += "("+employee[i];
else str += ","+employee[i];
if(i==employee.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.sellman in "+str;
}else{
sql += " and 1=0";
}
sql += " group by trunc(t.reretaildate,'dd')";
if(company!=null){
sql += ",t.storesales,t.comcode";
}else if(employee!=null){
sql += ",t.sellman,t.comcode";
}
sql += " order by trunc(t.reretaildate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计员工指定月份每天的销售情况
* @param onlycode
* @param month
* @param company
* @param employee
* @return
*/
public List getDaySaleMoneyByType(String onlycode,String year,String month,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))";
sql += ",sum(decode(t.selflag,null,(t.cnumber*(select t.cnumber from tab_selproduct tttt where tttt.selid=t.cunit)),t.cnumber))";
sql += ",trunc(r.reretaildate,'dd')";
if(comflag.equals("2")){
sql += ",(select c.unit_name from B_Companyinfo c where c.unit_id=r.comcode),r.comcode";
}else if(typeflag.equals("2")){
sql += ",t.typename";
}
sql +=" from tab_selproduct t,tab_ls_retail r where r.retailcode=t.retailcode and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and r.comcode in "+str;
}
if(type!=null){
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.typename in "+str;
}
sql += " group by trunc(r.reretaildate,'dd')";
if(comflag.equals("2")){
sql += ",r.comcode";
}else if(typeflag.equals("2")){
sql += ",t.typename";
}
sql += " order by trunc(r.reretaildate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计员工指定月份每天的销售情况
* @param onlycode
* @param month
* @param company
* @param employee
* @return
*/
public List getDaySaleMoneyByBrand(String onlycode,String year,String month,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))";
sql += ",sum(decode(t.selflag,null,(t.cnumber*(select t.cnumber from tab_selproduct tttt where tttt.selid=t.cunit)),t.cnumber))";
sql += ",trunc(r.reretaildate,'dd')";
if(comflag.equals("2")){
sql += ",(select c.unit_name from B_Companyinfo c where c.unit_id=r.comcode),r.comcode";
}else if(brandflag.equals("2")){
sql += ",s.cbrandname,s.cbrandcode";
}
sql +=" from tab_selproduct t,tab_ls_retail r,tab_kc_storage s where r.retailcode=t.retailcode and t.storano=s.id and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and r.comcode in "+str;
}
if(brand!=null){
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "('"+brand[i]+"'";
else str += ",'"+brand[i]+"'";
if(i==brand.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and s.cbrandcode in "+str;
}
sql += " group by trunc(r.reretaildate,'dd')";
if(comflag.equals("2")){
sql += ",r.comcode";
}else if(brandflag.equals("2")){
sql += ",s.cbrandname,s.cbrandcode";
}
sql += " order by trunc(r.reretaildate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定月份价格区间销售情况
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param minprice
* @param maxprice
* @return
*/
public ReportBean getMonthSaleMoneyScaleByCom(String onlycode,String year,String month,String key,String[] company,String comflag,String minprice,String maxprice){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(to_number(t.cax)),count(t.retailcode) from tab_ls_retail t where 1=1";
if(!month.equals(""))
sql += " and extract(year from t.reretaildate)='"+year+"' and extract(month from t.reretaildate)='"+month+"'";
if(!minprice.equals(""))
sql += " and to_number(t.cax)>="+minprice;
if(!maxprice.equals(""))
sql += " and to_number(t.cax)<="+maxprice;
if(comflag.equals("2")){
sql += " and t.comcode = '"+key+"'";
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and t.comcode in "+str;
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
if(result.next()){
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计员工指定月份价格区间销售情况
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param minprice
* @param maxprice
* @return
*/
public ReportBean getMonthSaleMoneyScaleByEmp(String onlycode,String year,String month,String key,String[] company,String comflag,String[] empid,String empflag,String minprice,String maxprice){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(to_number(t.cax)),count(t.retailcode) from tab_ls_retail t where 1=1";
if(!month.equals(""))
sql += " and extract(year from t.reretaildate)='"+year+"' and extract(month from t.reretaildate)='"+month+"'";
if(!minprice.equals(""))
sql += " and to_number(t.cax)>="+minprice;
if(!maxprice.equals(""))
sql += " and to_number(t.cax)<="+maxprice;
if(comflag.equals("2")){
sql += " and t.comcode = '"+key+"'";
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and t.comcode in "+str;
}
if(empflag.equals("2")){
sql += " and t.sellman = "+key;
}else{
String str = "";
for(int i=0;i<empid.length;i++){
if(i==0)
str += "("+empid[i];
else str += ","+empid[i];
if(i==empid.length-1)
str += ")";
}
sql += " and t.sellman in "+str;
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
if(result.next()){
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计物料类型指定月份价格区间销售情况
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param minprice
* @param maxprice
* @return
*/
public ReportBean getMonthSaleMoneyScaleByType(String onlycode,String year,String month,String key,String[] company,String comflag,String[] type,String typeflag,String minprice,String maxprice){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))";
sql += ",sum(decode(t.selflag,null,(t.cnumber*(select t.cnumber from tab_selproduct tttt where tttt.selid=t.cunit)),t.cnumber))";
sql +=" from tab_selproduct t,tab_ls_retail r where r.retailcode=t.retailcode and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(!minprice.equals(""))
sql += " and (decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))>="+minprice;
if(!maxprice.equals(""))
sql += " and (decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))<="+maxprice;
if(comflag.equals("2")){
sql += " and r.comcode = '"+key+"'";
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and r.comcode in "+str;
}
if(typeflag.equals("2")){
sql += " and t.typename = '"+key+"'";
}else{
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and t.typename in "+str;
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计物料品牌指定月份价格区间销售情况
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param minprice
* @param maxprice
* @return
*/
public ReportBean getMonthSaleMoneyScaleByBrand(String onlycode,String year,String month,String key,String[] company,String comflag,String[] brand,String brandflag,String minprice,String maxprice){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select sum(decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))";
sql += ",sum(decode(t.selflag,null,(t.cnumber*(select t.cnumber from tab_selproduct tttt where tttt.selid=t.cunit)),t.cnumber))";
sql +=" from tab_selproduct t,tab_ls_retail r,tab_kc_storage s where r.retailcode=t.retailcode and t.storano=s.id and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(!minprice.equals(""))
sql += " and (decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))>="+minprice;
if(!maxprice.equals(""))
sql += " and (decode(t.selflag,null,(t.zprice*t.cnumber)/(select sum(ttt.zprice*ttt.cnumber) from tab_selproduct ttt where ttt.cunit=t.cunit)*(select tt.bprice*tt.cnumber from tab_selproduct tt where tt.selid=t.cunit),(t.bprice*t.cnumber)))<="+maxprice;
if(comflag.equals("2")){
sql += " and r.comcode = '"+key+"'";
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and r.comcode in "+str;
}
if(brandflag.equals("2")){
sql += " and s.cbrandcode = '"+key+"'";
}else{
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "('"+brand[i]+"'";
else str += ",'"+brand[i]+"'";
if(i==brand.length-1)
str += ")";
}
if(str.equals(""))
sql += " and 1=0";
else sql += " and s.cbrandcode in "+str;
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计公司指定月份类型分布
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param typename
* @return
*/
public ReportBean getMonthSaleMoneyTypeScaleByCom(String onlycode,String year,String month,String key,String[] company,String comflag,String typename){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(decode(t.selflag,null,(select tab.cnumber from tab_selproduct tab where tab.selid=t.cunit)*t.cnumber,t.cnumber)),0) from tab_selproduct t where t.typename='"+typename+"' and t.retailcode in (select r.retailcode from tab_ls_retail r where 1=1";
if(!month.equals(""))
sql += " and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(comflag.equals("2")){
sql += " and r.comcode = "+key+")";
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and r.comcode in "+str+")";
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
if(result.next()){
rBean.setNumber(result.getLong(1));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计员工指定月份类型分布
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param typename
* @return
*/
public ReportBean getMonthSaleMoneyTypeScaleByEmp(String onlycode,String year,String month,String key,String[] company,String comflag,String[] empid,String empflag,String typename){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(decode(t.selflag,null,(select tab.cnumber from tab_selproduct tab where tab.selid=t.cunit)*t.cnumber,t.cnumber)),0) from tab_selproduct t where t.typename='"+typename+"' and t.retailcode in (select r.retailcode from tab_ls_retail r where 1=1";
if(!month.equals(""))
sql += " and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(comflag.equals("2")){
sql += " and r.comcode = "+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and r.comcode in "+str;
}
if(empflag.equals("2")){
sql += " and r.sellman = "+key+")";
}else{
String str = "";
for(int i=0;i<empid.length;i++){
if(i==0)
str += "("+empid[i];
else str += ","+empid[i];
if(i==empid.length-1)
str += ")";
}
sql += " and r.sellman in "+str+")";
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
if(result.next()){
rBean.setNumber(result.getLong(1));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计物料品牌指定月份类型分布
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param typename
* @return
*/
public ReportBean getMonthSaleMoneyTypeScaleByBrand(String onlycode,String year,String month,String key,String[] company,String comflag,String[] brand,String brandflag,String typename){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(decode(t.selflag,null,(select tab.cnumber from tab_selproduct tab where tab.selid=t.cunit)*t.cnumber,t.cnumber)),0) from tab_selproduct t,tab_kc_storage s where t.storano=s.id and t.typename='"+typename+"' and t.retailcode in (select r.retailcode from tab_ls_retail r where 1=1";
if(!month.equals(""))
sql += " and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(comflag.equals("2")){
sql += " and r.comcode = "+key+")";
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and r.comcode in "+str+")";
}
if(brandflag.equals("2")){
sql += " and s.cbrandcode = "+key;
}else{
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "("+brand[i];
else str += ","+brand[i];
if(i==brand.length-1)
str += ")";
}
sql += " and s.cbrandcode in "+str;
}
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
if(result.next()){
rBean.setNumber(result.getLong(1));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计指定月份营销方案分布
* @param onlycode
* @param year
* @param month
* @param company
* @param employee
* @param projectid
* @return
*/
public ReportBean getMonthSaleProjectScale(String onlycode,String year,String month,String company,String employee,Long projectid){
ReportBean rBean = new ReportBean();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cnumber*t.bprice),0) from tab_selproduct t,tab_sell_quomodo q,tab_sell_projectinfo p where t.sqmid=q.sqmid and q.sprojectid=p.sprojectid and p.sprojectid='"+projectid+"' and t.retailcode in (select r.retailcode from tab_ls_retail r where 1=1";
if(!month.equals(""))
sql += " and extract(year from r.reretaildate)='"+year+"' and extract(month from r.reretaildate)='"+month+"'";
if(company!=null && !company.equals("")){
sql += " and r.comcode = "+company+")";
}else if(employee!=null){
sql += " and r.sellman = "+employee+")";
}else{
sql += " and 1=0)";
}
sql += " group by p.sprojectid";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
if(result.next()){
rBean.setMoney(result.getDouble(1));
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return rBean;
}
/**
* 根据条件统计公司指定年份每月的入库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInCompanyByCom(String onlycode,String year,String[] company,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!year.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"'";
sql +=" group by trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计指定年份每月物料类型的入库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInCompanyByType(String onlycode,String year,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(type!=null && !type.equals("")){
String typestr = "";
for(int i=0;i<type.length;i++){
if(i==0)
typestr += "('"+type[i]+"'";
else typestr += ",'"+type[i]+"'";
if(i==type.length-1)
typestr += ")";
}
sql += " and t.typename in "+typestr;
}
if(!year.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"'";
sql +=" group by trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计指定年份每月物料品牌的入库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInCompanyByBrand(String onlycode,String year,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brand!=null && !brand.equals("")){
String brandstr = "";
for(int i=0;i<brand.length;i++){
if(i==0)
brandstr += "("+brand[i];
else brandstr += ","+brand[i];
if(i==brand.length-1)
brandstr += ")";
}
sql += " and t.brandno in "+brandstr;
}
if(!year.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"'";
sql +=" group by trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计指定月份每天公司的入库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getDayProductInCompanyByCom(String onlycode,String year,String month,String[] company,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计指定月份每天物料类型的入库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getDayProductInCompanyByType(String onlycode,String year,String month,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "('"+company[i]+"'";
else str += ",'"+company[i]+"'";
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(type!=null && !type.equals("")){
String typestr = "";
for(int i=0;i<type.length;i++){
if(i==0)
typestr += "('"+type[i]+"'";
else typestr += ",'"+type[i]+"'";
if(i==type.length-1)
typestr += ")";
}
sql += " and t.typename in "+typestr;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计指定月份每天物料品牌的入库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getDayProductInCompanyByBrand(String onlycode,String year,String month,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brand!=null && !brand.equals("")){
String brandstr = "";
for(int i=0;i<brand.length;i++){
if(i==0)
brandstr += "("+brand[i];
else brandstr += ","+brand[i];
if(i==brand.length-1)
brandstr += ")";
}
sql += " and t.brandno in "+brandstr;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司入库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getInStorageForModelByCom(String onlycode,String year,String month,String[] company,String key,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),tt.changetype from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by tt.changetype";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型入库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getInStorageForModelByType(String onlycode,String year,String month,String[] company,String[] type,String key,String comflag,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),tt.changetype from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(typeflag.equals("2")){
sql += " and t.typename='"+key+"'";
}else{
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
sql += " and t.typename in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by tt.changetype";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌入库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getInStorageForModelByBrand(String onlycode,String year,String month,String[] company,String[] brand,String key,String comflag,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),tt.changetype from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brandflag.equals("2")){
sql += " and t.brandno="+key;
}else{
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "("+brand[i];
else str += ","+brand[i];
if(i==brand.length-1)
str += ")";
}
sql += " and t.brandno in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by tt.changetype";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司入库物料类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getInStorageByArticleTypeByCom(String onlycode,String year,String month,String[] company,String key,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),t.typename from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "('"+company[i]+"'";
else str += ",'"+company[i]+"'";
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型入库物料类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getInStorageByArticleTypeByType(String onlycode,String year,String month,String[] company,String[] type,String key,String comflag,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),t.typename from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(typeflag.equals("2")){
sql += " and t.typename='"+key+"'";
}else{
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
sql += " and t.typename in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌入库物料类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getInStorageByArticleTypeByBrand(String onlycode,String year,String month,String[] company,String[] brand,String key,String comflag,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),t.typename from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('采购入库单','直接入库单','调拨入库单','外借入库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "('"+company[i]+"'";
else str += ",'"+company[i]+"'";
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brandflag.equals("2")){
sql += " and t.brandno="+key;
}else{
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "('"+brand[i]+"'";
else str += ",'"+brand[i]+"'";
if(i==brand.length-1)
str += ")";
}
sql += " and t.brandno in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定年份每月的出库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductOutCompanyByCom(String onlycode,String year,String[] company,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!year.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"'";
sql +=" group by trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型指定年份每月的出库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductOutCompanyByType(String onlycode,String year,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(type!=null && !type.equals("")){
String typestr = "";
for(int i=0;i<type.length;i++){
if(i==0)
typestr += "('"+type[i]+"'";
else typestr += ",'"+type[i]+"'";
if(i==type.length-1)
typestr += ")";
}
sql += " and t.typename in "+typestr;
}
if(!year.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"'";
sql +=" group by trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型指定年份每月的出库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductOutCompanyByBrand(String onlycode,String year,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brand!=null && !brand.equals("")){
String brandstr = "";
for(int i=0;i<brand.length;i++){
if(i==0)
brandstr += "("+brand[i];
else brandstr += ","+brand[i];
if(i==brand.length-1)
brandstr += ")";
}
sql += " and t.brandno in "+brandstr;
}
if(!year.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"'";
sql +=" group by trunc(tt.changedate,'MM')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定月份每天的出库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getDayProductOutCompanyByCom(String onlycode,String year,String month,String[] company,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型指定月份每天的出库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getDayProductOutCompanyByType(String onlycode,String year,String month,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(type!=null && !type.equals("")){
String typestr = "";
for(int i=0;i<type.length;i++){
if(i==0)
typestr += "('"+type[i]+"'";
else typestr += ",'"+type[i]+"'";
if(i==type.length-1)
typestr += ")";
}
sql += " and t.typename in "+typestr;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(typeflag.equals("2"))
sql += ",t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌指定月份每天的出库情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getDayProductOutCompanyByBrand(String onlycode,String year,String month,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
sql += " from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(company!=null){
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brand!=null && !brand.equals("")){
String brandstr = "";
for(int i=0;i<brand.length;i++){
if(i==0)
brandstr += "("+brand[i];
else brandstr += ","+brand[i];
if(i==brand.length-1)
brandstr += ")";
}
sql += " and t.brandno in "+brandstr;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by trunc(tt.changedate,'dd')";
if(comflag.equals("2"))
sql += ",tttt.unit_name,ttt.unit_id";
else if(brandflag.equals("2"))
sql += ",t.brandname,t.brandno";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司出库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getOutStorageForModelByCom(String onlycode,String year,String month,String[] company,String key,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),tt.changetype from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(!comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by tt.changetype";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型出库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getOutStorageForModelByType(String onlycode,String year,String month,String[] company,String[] type,String key,String comflag,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),tt.changetype from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(typeflag.equals("2")){
sql += " and t.typename='"+key+"'";
}else{
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
sql += " and t.typename in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by tt.changetype";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌出库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getOutStorageForModelByBrand(String onlycode,String year,String month,String[] company,String[] brand,String key,String comflag,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),tt.changetype from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brandflag.equals("2")){
sql += " and t.brandno="+key;
}else{
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "("+brand[i];
else str += ","+brand[i];
if(i==brand.length-1)
str += ")";
}
sql += " and t.brandno in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by tt.changetype";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司出库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getOutStorageByArticleTypeByCom(String onlycode,String year,String month,String[] company,String key,String comflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),t.typename from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "('"+company[i]+"'";
else str += ",'"+company[i]+"'";
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料类型出库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getOutStorageByArticleTypeByType(String onlycode,String year,String month,String[] company,String[] type,String key,String comflag,String typeflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),t.typename from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(typeflag.equals("2")){
sql += " and t.typename='"+key+"'";
}else{
String str = "";
for(int i=0;i<type.length;i++){
if(i==0)
str += "('"+type[i]+"'";
else str += ",'"+type[i]+"'";
if(i==type.length-1)
str += ")";
}
sql += " and t.typename in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌出库单类型比例
* @param onlycode
* @param year
* @param company
* @return
*/
public List getOutStorageByArticleTypeByBrand(String onlycode,String year,String month,String[] company,String[] brand,String key,String comflag,String brandflag){
List list = new ArrayList();
try{
session = ConnectDao.getSession(onlycode);
String sql = "select nvl(sum(t.cprice*t.factnum),0.00),nvl(sum(t.factnum),0.00),t.typename from Tab_Fin_Ordercargodetails t,Tabstoragechange tt,tabplace ttt,b_companyinfo tttt where t.orderno=tt.orderseq and tt.placename=ttt.placeid and ttt.unit_id=tttt.unit_id and tt.changetype in('退货出库单','调拨出库单','外借出库单') and t.factnum is not null ";
if(comflag.equals("2")){
sql += " and ttt.unit_id="+key;
}else{
String str = "";
for(int i=0;i<company.length;i++){
if(i==0)
str += "('"+company[i]+"'";
else str += ",'"+company[i]+"'";
if(i==company.length-1)
str += ")";
}
sql += " and ttt.unit_id in "+str;
}
if(brandflag.equals("2")){
sql += " and t.brandno="+key;
}else{
String str = "";
for(int i=0;i<brand.length;i++){
if(i==0)
str += "('"+brand[i]+"'";
else str += ",'"+brand[i]+"'";
if(i==brand.length-1)
str += ")";
}
sql += " and t.brandno in "+str;
}
if(!month.equals(""))
sql += " and extract(year from tt.changedate)='"+year+"' and extract(month from tt.changedate)='"+month+"'";
sql +=" group by t.typename";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()) {
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setShowname(result.getString(3));
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定年份每月的库存情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInStoreForYearByCom(String onlycode,String year,String[] company,String comflag){
List list = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
Calendar calendar = Calendar.getInstance();
cal.set(Calendar.YEAR, Integer.parseInt(year));
long starttime = System.currentTimeMillis();
try{
String str = "";
if(company!=null){
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
}
//1.循环所要显示的月份
//2.查找记录表，找出循环月份的记录,加入排除表,并加入统计;
//3.根据排除表,查找记录表,找出比循环月份早的最新记录,并加入统计;
session = ConnectDao.getSession(onlycode);
int maxmonth = 0;
if(cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR))
maxmonth = cal.get(Calendar.MONTH)+1;
else if(cal.get(Calendar.YEAR)<calendar.get(Calendar.YEAR))
maxmonth = 12;
for(int month=1;month<=maxmonth;month++){
String sql = "select distinct t.articleid from tab_storage_record t,b_companyinfo tt where t.companyid=tt.unit_id and tt.unit_id in "
+str+" and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"'";
Query query = session.createSQLQuery(sql);
List list1 = query.list();
String productid = "";
for(int i=0;i<list1.size();i++){
if(i==0){
productid += "("+list1.get(i).toString();
}else if(i%900==0 && i!=list1.size()){
productid += ") and tt.articleid not in ("+list1.get(i).toString();
}else{
productid += ","+list1.get(i).toString();
}
if(i==list1.size()-1)
productid += ")";
}
//System.out.println(list1.size()+"----"+productid);
//cprice成本价格 lastnumber 最后的数量
sql = "select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,'MM')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
sql += " from tab_storage_record t where t.rid=(" +
" SELECT max(tt.rid) from tab_storage_record tt " +
" where t.articleid=tt.articleid and trunc(t.recorddate,'MM')=trunc(tt.recorddate,'MM')";
if(company!=null){
sql += " and tt.companyid in "+str;
}
if(!year.equals(""))
sql += " and extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)='"+month+"'";
sql +=" group by trunc(tt.recorddate,'MM')) group by trunc(t.recorddate,'MM')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
sql += " order by ";
if(comflag.equals("2"))
sql += "t.companyid asc,";
sql += "trunc(t.recorddate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
System.out.println("测试1：sql="+sql);
int flag = 0 ;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){ flag = 1 ;
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
if(flag==1){
System.out.println("flag="+flag+" 有数据");
}
flag=0;
sql = "select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00)";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
sql += " from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid";
if(!productid.equals(""))
sql +=" and tt.articleid not in "+productid;
if(company!=null){
sql += " and tt.companyid in "+str;
}
if(!year.equals(""))
sql += " and (extract(year from tt.recorddate)<'"+year+"' or (extract(year from tt.recorddate)='"+year+"' " +
"and extract(month from tt.recorddate)<'"+month+"'))";
sql +=")";
if(comflag.equals("2"))
sql += "group by t.companyname,t.companyid order by t.companyid asc";
System.out.println("测试2：sql="+sql);
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
flag = 1 ;
cal.set(Calendar.MONTH, month-1);
cal.set(Calendar.DAY_OF_MONTH, 1);
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
if(comflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
rBean.setDate(cal.getTime());
int n = list.size();
for(int i=0;i<n;i++){
ReportBean reportBean = (ReportBean)list.get(i);
if(rBean.getShowid().equals(reportBean.getShowid()) && sdf.format(rBean.getDate()).equals(sdf.format(reportBean.getDate()))){
reportBean.setMoney(reportBean.getMoney().doubleValue()+rBean.getMoney().doubleValue());
reportBean.setNumber(reportBean.getNumber().longValue()+rBean.getNumber().longValue());
list.set(i, reportBean);
break;
}
if(i==n-1)
list.add(rBean);
}
if(list.size()==0)
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
if(flag==1){
System.out.println("flag="+flag+" 有数据");
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
long endtime = System.currentTimeMillis();
System.out.println(" 根据条件统计公司指定年份每月的库存情况 getProductInStoreForYearByCom 方法调用JDBC，时间："+(starttime-endtime));
return list;
}
/**
* 根据条件统计物料类型指定年份每月的库存情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInStoreForYearByType(String onlycode,String year,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
Calendar calendar = Calendar.getInstance();
cal.set(Calendar.YEAR, Integer.parseInt(year));
try{
String comstr = "",typestr = "";
if(company!=null){
for(int i=0;i<company.length;i++){
if(i==0)
comstr += "("+company[i];
else comstr += ","+company[i];
if(i==company.length-1)
comstr += ")";
}
}
if(type!=null){
for(int i=0;i<type.length;i++){
if(i==0)
typestr += "('"+type[i]+"'";
else typestr += ",'"+type[i]+"'";
if(i==type.length-1)
typestr += ")";
}
}
//1.循环所要显示的月份
//2.查找记录表，找出循环月份的记录,加入排除表,并加入统计;
//3.根据排除表,查找记录表,找出比循环月份早的最新记录,并加入统计;
session = ConnectDao.getSession(onlycode);
int maxmonth = 0;
if(cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR))
maxmonth = cal.get(Calendar.MONTH)+1;
else if(cal.get(Calendar.YEAR)<calendar.get(Calendar.YEAR))
maxmonth = 12;
for(int month=1;month<=maxmonth;month++){
String sql = "select distinct t.articleid from tab_storage_record t,b_companyinfo tt,tab_kc_storage ttt where t.companyid=tt.unit_id and t.articleid=ttt.id and tt.unit_id in "+comstr+" and ttt.ctypename in "+typestr+" and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"'";
Query query = session.createSQLQuery(sql);
List list1 = query.list();
String productid = "";
for(int i=0;i<list1.size();i++){
if(i==0){
productid += "("+list1.get(i).toString();
}else if(i%900==0 && i!=list1.size()){
productid += ") and tt.articleid not in ("+list1.get(i).toString();
}else{
productid += ","+list1.get(i).toString();
}
if(i==list1.size()-1)
productid += ")";
}
sql = "select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,'MM')";
if(comflag.equals("2")){
sql += ",t.companyname,t.companyid";
}else if(typeflag.equals("2")){
sql += ",ttt.ctypename";
}
sql += " from tab_storage_record t,tab_kc_storage ttt where t.articleid=ttt.id";
if(type!=null){
sql += " and ttt.ctypename in "+typestr;
}
sql += " and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid and trunc(t.recorddate,'MM')=trunc(tt.recorddate,'MM')";
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)='"+month+"'";
sql +=" group by trunc(tt.recorddate,'MM')) group by trunc(t.recorddate,'MM')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
else if(typeflag.equals("2"))
sql += ",ttt.ctypename";
sql += " order by ";
if(comflag.equals("2"))
sql += "t.companyid asc,";
else if(typeflag.equals("2"))
sql += "ttt.ctypename asc,";
sql += "trunc(t.recorddate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
sql = "select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00)";
if(comflag.equals("2"))
sql +=",t.companyname,t.companyid";
else if(typeflag.equals("2"))
sql +=",ttt.ctypename";
sql +=" from tab_storage_record t,tab_kc_storage ttt where t.articleid=ttt.id";
if(type!=null){
sql += " and ttt.ctypename in "+typestr;
}
sql +=" and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid";
if(!productid.equals(""))
sql +=" and tt.articleid not in "+productid;
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and (extract(year from tt.recorddate)<'"+year+"' or (extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)<'"+month+"'))";
sql +=")";
if(comflag.equals("2"))
sql += " group by t.companyname,t.companyid order by t.companyid asc";
else if(typeflag.equals("2"))
sql += " group by ttt.ctypename order by ttt.ctypename asc";
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
cal.set(Calendar.MONTH, month-1);
cal.set(Calendar.DAY_OF_MONTH, 1);
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
if(comflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(4));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(3));
}else{
rBean.setShowname("所选公司单位所选物料类型");
rBean.setShowid("0");
}
rBean.setDate(cal.getTime());
int n = list.size();
for(int i=0;i<n;i++){
ReportBean reportBean = (ReportBean)list.get(i);
if(rBean.getShowid().equals(reportBean.getShowid()) && sdf.format(rBean.getDate()).equals(sdf.format(reportBean.getDate()))){
reportBean.setMoney(reportBean.getMoney().doubleValue()+rBean.getMoney().doubleValue());
reportBean.setNumber(reportBean.getNumber().longValue()+rBean.getNumber().longValue());
list.set(i, reportBean);
break;
}
if(i==n-1)
list.add(rBean);
}
if(list.size()==0)
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌指定年份每月的库存情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInStoreForYearByBrand(String onlycode,String year,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
Calendar calendar = Calendar.getInstance();
cal.set(Calendar.YEAR, Integer.parseInt(year));
try{
String comstr = "",brandstr = "";
if(company!=null){
for(int i=0;i<company.length;i++){
if(i==0)
comstr += "("+company[i];
else comstr += ","+company[i];
if(i==company.length-1)
comstr += ")";
}
}
if(brand!=null){
for(int i=0;i<brand.length;i++){
if(i==0)
brandstr += "('"+brand[i]+"'";
else brandstr += ",'"+brand[i]+"'";
if(i==brand.length-1)
brandstr += ")";
}
}
//1.循环所要显示的月份
//2.查找记录表，找出循环月份的记录,加入排除表,并加入统计;
//3.根据排除表,查找记录表,找出比循环月份早的最新记录,并加入统计;
session = ConnectDao.getSession(onlycode);
int maxmonth = 0;
if(cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR))
maxmonth = cal.get(Calendar.MONTH)+1;
else if(cal.get(Calendar.YEAR)<calendar.get(Calendar.YEAR))
maxmonth = 12;
for(int month=1;month<=maxmonth;month++){
String sql = "select distinct t.articleid from tab_storage_record t,b_companyinfo tt where t.companyid=tt.unit_id and tt.unit_id in "+comstr+" and t.brandid in "+brandstr+" and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"'";
Query query = session.createSQLQuery(sql);
List list1 = query.list();
String productid = "";
for(int i=0;i<list1.size();i++){
if(i==0){
productid += "("+list1.get(i).toString();
}else if(i%900==0 && i!=list1.size()){
productid += ") and tt.articleid not in ("+list1.get(i).toString();
}else{
productid += ","+list1.get(i).toString();
}
if(i==list1.size()-1)
productid += ")";
}
sql = "select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,'MM')";
if(comflag.equals("2")){
sql += ",t.companyname,t.companyid";
}else if(brandflag.equals("2")){
sql += ",t.brand,t.brandid";
}
sql += " from tab_storage_record t where 1=1";
if(brand!=null){
sql += " and t.brandid in "+brandstr;
}
sql += " and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid and trunc(t.recorddate,'MM')=trunc(tt.recorddate,'MM')";
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)='"+month+"'";
sql +=" group by trunc(tt.recorddate,'MM')) group by trunc(t.recorddate,'MM')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
else if(brandflag.equals("2"))
sql += ",t.brand,t.brandid";
sql += " order by ";
if(comflag.equals("2"))
sql += "t.companyid asc,";
else if(brandflag.equals("2"))
sql += "t.brandid asc,";
sql += "trunc(t.recorddate,'MM') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
sql = "select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00)";
if(comflag.equals("2"))
sql +=",t.companyname,t.companyid";
else if(brandflag.equals("2"))
sql +=",t.brand,t.brandid";
sql +=" from tab_storage_record t where 1=1";
if(brand!=null){
sql += " and t.brandid in "+brandstr;
}
sql +=" and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid";
if(!productid.equals(""))
sql +=" and tt.articleid not in "+productid;
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and (extract(year from tt.recorddate)<'"+year+"' or (extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)<'"+month+"'))";
sql +=")";
if(comflag.equals("2"))
sql += " group by t.companyname,t.companyid order by t.companyid asc";
else if(brandflag.equals("2"))
sql += " group by t.brand,t.brandid order by t.brandid asc";
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
cal.set(Calendar.MONTH, month-1);
cal.set(Calendar.DAY_OF_MONTH, 1);
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位所选物料品牌");
rBean.setShowid("0");
}
rBean.setDate(cal.getTime());
int n = list.size();
for(int i=0;i<n;i++){
ReportBean reportBean = (ReportBean)list.get(i);
if(rBean.getShowid().equals(reportBean.getShowid()) && sdf.format(rBean.getDate()).equals(sdf.format(reportBean.getDate()))){
reportBean.setMoney(reportBean.getMoney().doubleValue()+rBean.getMoney().doubleValue());
reportBean.setNumber(reportBean.getNumber().longValue()+rBean.getNumber().longValue());
list.set(i, reportBean);
break;
}
if(i==n-1)
list.add(rBean);
}
if(list.size()==0)
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计公司指定月份每天的库存情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInStoreForMonthByCom(String onlycode,String year,String month,String[] company,String comflag){
List list = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
Calendar calendar = Calendar.getInstance();
cal.set(Calendar.YEAR, Integer.parseInt(year));
cal.set(Calendar.MONTH, Integer.parseInt(month)-1);
long starttime = System.currentTimeMillis();
try{
String str = "";
if(company!=null){
for(int i=0;i<company.length;i++){
if(i==0)
str += "("+company[i];
else str += ","+company[i];
if(i==company.length-1)
str += ")";
}
}
//1.循环所要显示的天数
//2.查找记录表，找出循环日期的记录,加入排除表,并加入统计;
//3.根据排除表,查找记录表,找出比循环日期早的最新记录,并加入统计;
session = ConnectDao.getSession(onlycode);
int maxdays = 0;
if(calendar.get(Calendar.YEAR)>cal.get(Calendar.YEAR) || (cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR) && calendar.get(Calendar.MONTH)>cal.get(Calendar.MONTH)))
maxdays = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
else if(cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR) && cal.get(Calendar.MONTH)==calendar.get(Calendar.MONTH))
maxdays = cal.get(Calendar.DAY_OF_MONTH);
for(int day=1;day<=maxdays;day++){
String sql = "select distinct t.articleid from tab_storage_record t,b_companyinfo tt " +
"where t.companyid=tt.unit_id and tt.unit_id in "+str+" " +
"and extract(year from t.recorddate)='"+year+"' " +
"and extract(month from t.recorddate)='"+month+"' and extract(day from t.recorddate)='"+day+"'";
Query query = session.createSQLQuery(sql);
List list1 = query.list();
String productid = "";
for(int i=0;i<list1.size();i++){
if(i==0){
productid += "("+list1.get(i).toString();
}else if(i%900==0 && i!=list1.size()){
productid += ") and tt.articleid not in ("+list1.get(i).toString();
}else{
productid += ","+list1.get(i).toString();
}
if(i==list1.size()-1)
productid += ")";
}
sql = "select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,'dd')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
sql += " from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt " +
"where t.articleid=tt.articleid and trunc(t.recorddate,'dd')=trunc(tt.recorddate,'dd')";
if(company!=null){
sql += " and tt.companyid in "+str;
}
if(!year.equals(""))
sql += " and extract(year from t.recorddate)='"+year+"' " +
"and extract(month from t.recorddate)='"+month+"' " +
"and extract(day from t.recorddate)='"+day+"'";
sql +=" group by trunc(tt.recorddate,'dd')) group by trunc(t.recorddate,'dd')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
sql += " order by ";
if(comflag.equals("2"))
sql += "t.companyid asc,";
sql += "trunc(t.recorddate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
int flag = 0 ;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
flag = 1 ;
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
flag = 0 ;
sql = "select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00)";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
sql += " from tab_storage_record t where t.rid=(SELECT max(tt.rid) from tab_storage_record tt" +
" where t.articleid=tt.articleid";
if(!productid.equals(""))
sql +=" and tt.articleid not in "+productid;
if(company!=null){
sql += " and tt.companyid in "+str;
}
sql += " and (extract(year from tt.recorddate)<'"+year+"' " +
"or (extract(year from tt.recorddate)='"+year+"' " +
"and extract(month from tt.recorddate)<'"+month+"') " +
"or (extract(year from tt.recorddate)='"+year+"'" +
" and extract(month from tt.recorddate)='"+month+"' " +
"and extract(day from tt.recorddate)<'"+day+"'))";
sql +=")";
if(comflag.equals("2"))
sql += "group by t.companyname,t.companyid order by t.companyid asc";
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
flag = 1 ;
cal.set(Calendar.DAY_OF_MONTH, day);
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
if(comflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位");
rBean.setShowid("0");
}
rBean.setDate(cal.getTime());
int n = list.size();
for(int i=0;i<n;i++){
ReportBean reportBean = (ReportBean)list.get(i);
if(rBean.getShowid().equals(reportBean.getShowid()) && sdf.format(rBean.getDate()).equals(sdf.format(reportBean.getDate()))){
reportBean.setMoney(reportBean.getMoney().doubleValue()+rBean.getMoney().doubleValue());
reportBean.setNumber(reportBean.getNumber().longValue()+rBean.getNumber().longValue());
list.set(i, reportBean);
break;
}
if(i==n-1)
list.add(rBean);
}
if(list.size()==0)
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
long endtime = System.currentTimeMillis();
System.out.println(" getProductInStoreForMonthByCom JDBC方法调用的时间："+(starttime-endtime));
return list;
}
/**
* 根据条件统计物料类型指定月份每天的库存情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInStoreForMonthByType(String onlycode,String year,String month,String[] company,String comflag,String[] type,String typeflag){
List list = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
Calendar calendar = Calendar.getInstance();
cal.set(Calendar.YEAR, Integer.parseInt(year));
cal.set(Calendar.MONTH, Integer.parseInt(month)-1);
try{
String comstr = "",typestr = "";
if(company!=null){
for(int i=0;i<company.length;i++){
if(i==0)
comstr += "("+company[i];
else comstr += ","+company[i];
if(i==company.length-1)
comstr += ")";
}
}
if(type!=null){
for(int i=0;i<type.length;i++){
if(i==0)
typestr += "('"+type[i]+"'";
else typestr += ",'"+type[i]+"'";
if(i==type.length-1)
typestr += ")";
}
}
//1.循环所要显示的天数
//2.查找记录表，找出循环日期的记录,加入排除表,并加入统计;
//3.根据排除表,查找记录表,找出比循环日期早的最新记录,并加入统计;
session = ConnectDao.getSession(onlycode);
int maxdays = 0;
if(calendar.get(Calendar.YEAR)>cal.get(Calendar.YEAR) || (cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR) && calendar.get(Calendar.MONTH)>cal.get(Calendar.MONTH)))
maxdays = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
else if(cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR) && cal.get(Calendar.MONTH)==calendar.get(Calendar.MONTH))
maxdays = cal.get(Calendar.DAY_OF_MONTH);
for(int day=1;day<=maxdays;day++){
String sql = "select distinct t.articleid from tab_storage_record t,b_companyinfo tt,tab_kc_storage ttt where t.companyid=tt.unit_id and t.articleid=ttt.id and tt.unit_id in "+comstr+" and ttt.ctypename in "+typestr+" and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"' and extract(day from t.recorddate)='"+day+"'";
Query query = session.createSQLQuery(sql);
List list1 = query.list();
String productid = "";
for(int i=0;i<list1.size();i++){
if(i==0){
productid += "("+list1.get(i).toString();
}else if(i%900==0 && i!=list1.size()){
productid += ") and tt.articleid not in ("+list1.get(i).toString();
}else{
productid += ","+list1.get(i).toString();
}
if(i==list1.size()-1)
productid += ")";
}
sql = "select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,'dd')";
if(comflag.equals("2")){
sql += ",t.companyname,t.companyid";
}else if(typeflag.equals("2")){
sql += ",ttt.ctypename";
}
sql += " from tab_storage_record t,tab_kc_storage ttt where t.articleid=ttt.id";
if(type!=null){
sql += " and ttt.ctypename in "+typestr;
}
sql += " and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid and trunc(t.recorddate,'dd')=trunc(tt.recorddate,'dd')";
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"' and extract(day from t.recorddate)='"+day+"'";
sql +=" group by trunc(tt.recorddate,'dd')) group by trunc(t.recorddate,'dd')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
else if(typeflag.equals("2"))
sql += ",ttt.ctypename";
sql += " order by ";
if(comflag.equals("2"))
sql += "t.companyid asc,";
else if(typeflag.equals("2"))
sql += "ttt.ctypename asc,";
sql += "trunc(t.recorddate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位所选物料类型");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
sql = "select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00)";
if(comflag.equals("2"))
sql +=",t.companyname,t.companyid";
else if(typeflag.equals("2"))
sql +=",ttt.ctypename";
sql +=" from tab_storage_record t,tab_kc_storage ttt where t.articleid=ttt.id";
if(type!=null){
sql += " and ttt.ctypename in "+typestr;
}
sql +=" and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid";
if(!productid.equals(""))
sql +=" and tt.articleid not in "+productid;
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and (extract(year from tt.recorddate)<'"+year+"' or (extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)<'"+month+"') or (extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)='"+month+"' and extract(day from tt.recorddate)<'"+day+"'))";
sql +=")";
if(comflag.equals("2"))
sql += " group by t.companyname,t.companyid order by t.companyid asc";
else if(typeflag.equals("2"))
sql += " group by ttt.ctypename order by ttt.ctypename asc";
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
cal.set(Calendar.DAY_OF_MONTH, day);
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
if(comflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(4));
}else if(typeflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(3));
}else{
rBean.setShowname("所选公司单位所选物料类型");
rBean.setShowid("0");
}
rBean.setDate(cal.getTime());
int n = list.size();
for(int i=0;i<n;i++){
ReportBean reportBean = (ReportBean)list.get(i);
if(rBean.getShowid().equals(reportBean.getShowid()) && sdf.format(rBean.getDate()).equals(sdf.format(reportBean.getDate()))){
reportBean.setMoney(reportBean.getMoney().doubleValue()+rBean.getMoney().doubleValue());
reportBean.setNumber(reportBean.getNumber().longValue()+rBean.getNumber().longValue());
list.set(i, reportBean);
break;
}
if(i==n-1)
list.add(rBean);
}
if(list.size()==0)
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* 根据条件统计物料品牌指定月份每天的库存情况
* @param onlycode
* @param year
* @param company
* @return
*/
public List getProductInStoreForMonthByBrand(String onlycode,String year,String month,String[] company,String comflag,String[] brand,String brandflag){
List list = new ArrayList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
Calendar calendar = Calendar.getInstance();
cal.set(Calendar.YEAR, Integer.parseInt(year));
cal.set(Calendar.MONTH, Integer.parseInt(month)-1);
try{
String comstr = "",brandstr = "";
if(company!=null){
for(int i=0;i<company.length;i++){
if(i==0)
comstr += "("+company[i];
else comstr += ","+company[i];
if(i==company.length-1)
comstr += ")";
}
}
if(brand!=null){
for(int i=0;i<brand.length;i++){
if(i==0)
brandstr += "('"+brand[i]+"'";
else brandstr += ",'"+brand[i]+"'";
if(i==brand.length-1)
brandstr += ")";
}
}
//1.循环所要显示的天数
//2.查找记录表，找出循环日期的记录,加入排除表,并加入统计;
//3.根据排除表,查找记录表,找出比循环日期早的最新记录,并加入统计;
session = ConnectDao.getSession(onlycode);
int maxdays = 0;
if(calendar.get(Calendar.YEAR)>cal.get(Calendar.YEAR) || (cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR) && calendar.get(Calendar.MONTH)>cal.get(Calendar.MONTH)))
maxdays = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
else if(cal.get(Calendar.YEAR)==calendar.get(Calendar.YEAR) && cal.get(Calendar.MONTH)==calendar.get(Calendar.MONTH))
maxdays = cal.get(Calendar.DAY_OF_MONTH);
for(int day=1;day<=maxdays;day++){
String sql = "select distinct t.articleid from tab_storage_record t,b_companyinfo tt where t.companyid=tt.unit_id and tt.unit_id in "+comstr+" and t.brandid in "+brandstr+" and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"' and extract(day from t.recorddate)='"+day+"'";
Query query = session.createSQLQuery(sql);
List list1 = query.list();
String productid = "";
for(int i=0;i<list1.size();i++){
if(i==0){
productid += "("+list1.get(i).toString();
}else if(i%900==0 && i!=list1.size()){
productid += ") and tt.articleid not in ("+list1.get(i).toString();
}else{
productid += ","+list1.get(i).toString();
}
if(i==list1.size()-1)
productid += ")";
}
sql = "select nvl(sum(t.lastnumber*t.cprice),0.00),nvl(sum(t.lastnumber),0.00),trunc(t.recorddate,'dd')";
if(comflag.equals("2")){
sql += ",t.companyname,t.companyid";
}else if(brandflag.equals("2")){
sql += ",t.brand,t.brandid";
}
sql += " from tab_storage_record t where 1=1";
if(brand!=null){
sql += " and t.brandid in "+brandstr;
}
sql += " and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid and trunc(t.recorddate,'dd')=trunc(tt.recorddate,'dd')";
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and extract(year from t.recorddate)='"+year+"' and extract(month from t.recorddate)='"+month+"' and extract(day from t.recorddate)='"+day+"'";
sql +=" group by trunc(tt.recorddate,'dd')) group by trunc(t.recorddate,'dd')";
if(comflag.equals("2"))
sql += ",t.companyname,t.companyid";
else if(brandflag.equals("2"))
sql += ",t.brand,t.brandid";
sql += " order by ";
if(comflag.equals("2"))
sql += "t.companyid asc,";
else if(brandflag.equals("2"))
sql += "t.brandid asc,";
sql += "trunc(t.recorddate,'dd') asc";
Connection conn = null;
Statement stat = null;
ResultSet result = null;
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
rBean.setDate(result.getDate(3));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(4));
rBean.setShowid(result.getString(5));
}else{
rBean.setShowname("所选公司单位所选物料品牌");
rBean.setShowid("0");
}
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
sql = "select nvl(sum(t.lastnumber*t.cprice),00),nvl(sum(t.lastnumber),0.00)";
if(comflag.equals("2"))
sql +=",t.companyname,t.companyid";
else if(brandflag.equals("2"))
sql +=",t.brand,t.brandid";
sql +=" from tab_storage_record t where 1=1";
if(brand!=null){
sql += " and t.brandid in "+brandstr;
}
sql +=" and t.rid=(SELECT max(tt.rid) from tab_storage_record tt where t.articleid=tt.articleid";
if(!productid.equals(""))
sql +=" and tt.articleid not in "+productid;
if(company!=null){
sql += " and tt.companyid in "+comstr;
}
if(!year.equals(""))
sql += " and (extract(year from tt.recorddate)<'"+year+"' or (extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)<'"+month+"') or (extract(year from tt.recorddate)='"+year+"' and extract(month from tt.recorddate)='"+month+"' and extract(day from tt.recorddate)<'"+day+"'))";
sql +=")";
if(comflag.equals("2"))
sql += " group by t.companyname,t.companyid order by t.companyid asc";
else if(brandflag.equals("2"))
sql += " group by t.brand,t.brandid order by t.brandid asc";
try{
conn = session.connection();
try{
stat = conn.createStatement();
try{
result=stat.executeQuery(sql);
while (result.next()){
cal.set(Calendar.DAY_OF_MONTH, day);
ReportBean rBean = new ReportBean();
rBean.setMoney(result.getDouble(1));
rBean.setNumber(result.getLong(2));
if(comflag.equals("2") || brandflag.equals("2")){
rBean.setShowname(result.getString(3));
rBean.setShowid(result.getString(4));
}else{
rBean.setShowname("所选公司单位所选物料品牌");
rBean.setShowid("0");
}
rBean.setDate(cal.getTime());
int n = list.size();
for(int i=0;i<n;i++){
ReportBean reportBean = (ReportBean)list.get(i);
if(rBean.getShowid().equals(reportBean.getShowid()) && sdf.format(rBean.getDate()).equals(sdf.format(reportBean.getDate()))){
reportBean.setMoney(reportBean.getMoney().doubleValue()+rBean.getMoney().doubleValue());
reportBean.setNumber(reportBean.getNumber().longValue()+rBean.getNumber().longValue());
list.set(i, reportBean);
break;
}
if(i==n-1)
list.add(rBean);
}
if(list.size()==0)
list.add(rBean);
}
}finally{
result.close();
}
}finally{
stat.close();
}
}finally{
conn.close();
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
if(session.isOpen())
session.close();
}
return list;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO 自动生成方法存根
// ReportCenterDao dao = new ReportCenterDao();
// String com = "120047";
// ReportBean list = dao.getMonthSaleMoneyTypeScale("art", "2009","3", com,null,"镜片");
// System.out.println(list.getShowname()+":"+list.getNumber());
//
// ReportBean bean = dao.getMonthSaleProjectScale("art", "2009","3", com,null,Long.valueOf("3260110"));
// System.out.println(bean.getMoney());
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("M");
cal.set(Calendar.MONTH, 1);
System.out.println(cal.getActualMaximum(Calendar.DAY_OF_MONTH));
}
}