dealerservice.java

一个直销用的管理软件

package com.susssoft.richjl.operation;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;
import com.susssoft.richjl.operation.Dealer;
import com.susssoft.richjl.common.Unit;
import com.susssoft.richjl.dboperation.DBConnectionManager;

/**
 * @author 付祖远
 * 此类是一个服务类,主要用于处理一些非程序的问题,包括,将表之前字段还原等方法
 */
public class DealerService {
	private static final Logger log = Logger.getLogger(DealerService.class);
	 
	/**
	 * 将file和folder表的数据还原,最初file,folder表中是没有经销商区域,层次信息这些字段的,后来修改了表,
	 * 下面的方法主要就是在不破坏原来数据的同时将表中的字段数据还原
	 */
	 
	public void repaireFileFolder(){
		DBConnectionManager oper = new DBConnectionManager();
	    ResultSet rs = oper.getRs("select * from [file]");
	    int div = 0;
	    try{
	    	while(rs.next()){
	    		String fileName = rs.getString("FileName");
	    		String[] str = getDealerInfo(fileName);
	    		//数据入库
	    		String sql = "update [file] set area='" + str[0] + "',div=" + str[1] + ",dealertype='" + str[2] + "',areaid='" + str[3] + "' where FileName='" + fileName + "'";
	    		oper.execUpdate(sql);
	        }
	        rs.close();
	        
	        rs = oper.getRs("select * from folder");
	        while(rs.next()){
	    		String folderName = rs.getString("FolderName");
	    		String[] str = getDealerInfo(folderName);
	    		//数据入库
	    		String sql = "update [folder] set area='" + str[0] + "',div=" + str[1] + ",dealertype='" + str[2] + "',areaid='" + str[3] + "' where FolderName='" + folderName + "'";
	    		oper.execUpdate(sql);
	        }
	        rs.close();
	        
	    } catch (SQLException e){
	        log.error("将file和folder表的数据还原时出错:" + e);
	    }
	    oper.closeConn();
	}
	
	/**
	 * 根据经销商的编号得到相应的信息
	 * @param dealerId 经销商编号
	 * 返回区域(颜色值)和层次(以字符形式表示)
	 */
	public String[] getDealerInfo(String dealerId){
		Dealer dealer = new Dealer();
		String[] returnStr = new String[4];
		DBConnectionManager oper = new DBConnectionManager();
	    ResultSet rs = oper.getRs("SELECT * FROM v_user where userid='" + dealerId + "'");
	    int div = 0;
	    try{
	    	while(rs.next()){
	    	    int sort = rs.getInt("sort");
	    	    if(sort==2){
	    	        returnStr[2] = "准";
	    	    } else {
	    	        returnStr[2] = "";
	    	    }
	    		String areaid = rs.getString("areaid");
	    		returnStr[0] = dealer.getAreaColor(areaid);
	    		returnStr[1] = String.valueOf(rs.getInt("div"));
	    		returnStr[3] = areaid;
	        }
	        rs.close();
	    } catch (SQLException e){
	        log.error("得到指定经销商信息时出错:" + e);
	    }
	    oper.closeConn();
	    return returnStr;
	}
	
	/**
	 * 计算公司总销售额的服务
	 *
	 */
	public void countCompanySale(){
	    DBConnectionManager oper = new DBConnectionManager();
	    ResultSet rs = oper.getRs("SELECT Userid, sort, Buytime FROM t_usersign order by Buytime");
	    try{
	        while(rs.next()){
	            boolean haveExec = false;
	            String sql = "";
	            int[] date = Unit.getWeekMonthYearOfYear(Unit.getTrimDateNoTimer(rs.getString("Buytime")));
	            ResultSet innerRs = oper.getRs("select * from t_company_sale where years=" + date[0] + " and weeks=" + date[2]);
	            //当t_company_sale表中有此年,此星期的数据时,直接修改数据,在原有的基础上修改数据
	            while(innerRs.next()){
	                if(rs.getInt("sort")==1){
	                    sql = "update t_company_sale set usermoney=" + (innerRs.getInt("usermoney") + 198000) + ",updatetime='" + rs.getString("buytime") + "' where years=" + date[0] + " and weeks=" + date[2];
	                } else {
	                    sql = "update t_company_sale set gusermoney=" + (innerRs.getInt("gusermoney") + 99000) + ",updatetime='" + rs.getString("buytime") + "' where years=" + date[0] + " and weeks=" + date[2];
	                }
	                oper.execUpdate(sql);
	                haveExec = true;
	            }
	            innerRs.close();
	            
	            //当t_company_sale表中没有此年,此星期的数据时,直接添加数据
	            if(haveExec == false){
	                if(rs.getInt("sort")==1){
	                	sql = "insert into t_company_sale(updatetime,usermoney,gusermoney,consumemoney,years,months,weeks) values('" + rs.getString("buytime") + "',198000,0,0," + date[0] + "," + date[1] +  "," + date[2] + ")";
	            	} else {
	                	sql = "insert into t_company_sale(updatetime,usermoney,gusermoney,consumemoney,years,months,weeks) values('" + rs.getString("buytime") + "',0,99000,0," + date[0] + "," + date[1] +  "," + date[2] + ")";
	            	}
	                oper.execUpdate(sql);
	            }
	        }
	        rs.close();
            oper.closeConn();
	    } catch (SQLException e){
	        log.error("计算公司总销售额的服务时出错:" + e);
	    }
	}
	
	/**
	 * 修改三个金额表中的years,months,weeks数据
	 *
	 */
	public void repaireYMW(){
	    DBConnectionManager oper = new DBConnectionManager();
	    ResultSet rs = oper.getRs("SELECT * from t_usermoneyone");
	    try{
		    while(rs.next()){
		        String[] time = rs.getString("gettime").split(" ");
		        int[] value = Unit.getWeekMonthYearOfYear(time[0]);	
		        String sql = "update t_usermoneyone set years=" + value[0] + ",months=" + value[1] + ",weeks=" + value[2] + " where id=" + rs.getInt("id");
		        oper.execUpdate(sql);
		    }
		    rs.close();
		    
		    rs = oper.getRs("SELECT * from t_usermoneytwo");
		    while(rs.next()){
		        String[] time = rs.getString("gettime").split(" ");
		        int[] value = Unit.getWeekMonthYearOfYear(time[0]);	
		        String sql = "update t_usermoneyone set years=" + value[0] + ",months=" + value[1] + ",weeks=" + value[2] + " where id=" + rs.getInt("id");
		        oper.execUpdate(sql);
		    }
		    rs.close();
		    
		    rs = oper.getRs("SELECT * from t_UserMoneyOne_other");
		    while(rs.next()){
		        String[] time = rs.getString("gettime").split(" ");
		        int[] value = Unit.getWeekMonthYearOfYear(time[0]);	
		        String sql = "update t_UserMoneyOne_other set years=" + value[0] + ",months=" + value[1] + ",weeks=" + value[2] + " where id=" + rs.getInt("id");
		        oper.execUpdate(sql);
		    }
		    rs.close();
		    oper.closeConn();
	    } catch (SQLException e){
	        log.error("修改三个金额表中数据时出错:" + e);
	    }
	}

	public static void main(String[] args) {
		DealerService service = new DealerService();
		service.countCompanySale();
		//service.showMsgBox("1221");
	}
}