feifeirao

NameTypeNullableDefaultStorageComments RID NUMBER(20) N       COMPANYNAME VARCHAR2(50) Y       COMPANYID NUMBER(20) Y       PLACENAME VARCHAR2(50) Y       PLACEID NUMBER(20) Y       ARTICLEID NUMBER(20) Y       ARTICLENAME VARCHAR2(50) Y       BRAND VARCHAR2(50) Y       BRANDID NUMBER(20) Y       CATENA VARCHAR2(50) Y       CATENAID VARCHAR2(50) Y       BALLMODEL VARCHAR2(50) Y       POLECOLOR VARCHAR2(50) Y       INITNUMBER NUMBER(12) Y       LASTNUMBER NUMBER(12) Y       RECORDDATE DATE Y       BPRICE NUMBER(12,2) Y       CPRICE NUMBER(12,2) Y      


==

=============================================================================

 

/**
  * 根据条件统计公司指定月份每天的库存情况
  * @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;
 }
 
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝