=============================================================================
/**
* 根据条件统计公司指定月份每天的库存情况
* @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);
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+"'";
System.out.println("测试##1 根据条件统计公司指定月份每天的库存 sql="+sql);
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;
System.out.println("测试##2 根据条件统计公司指定月份每天的库存 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 = 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";
System.out.println("测试##3 根据条件统计公司指定月份每天的库存 sql="+sql);
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();
}
if(flag==1)
System.out.println("测试## 有数据");
}
}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));
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();
}
return list;
}
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝