package net.tencent.tickets.service;

import java.util.List;

import net.tencent.tickets.dao.CityDao;
import net.tencent.tickets.entity.City;

public class CityService {
	
	//属性依赖cityDao
	private CityDao cityDao = CityDao.getInstance();
	
	/**
	 * 
	 * <p>Title: getCityByProvinceNum</p>  
	 * <p>
	 *	Description: 
	 *	获取指定省份的城市信息的业务方法
	 * </p> 
	 * @param provinceNum
	 * @return
	 */
	public List<City> getCityByProvinceNum(String provinceNum) {
		return cityDao.queryCityByProvinceNum(provinceNum);
	}
	/**
	 * <p>Title: queryCityByCityNum</p>
	 * <p>
	 *    Description:
	 * </p>
	 * <p>Copyright: Copyright (c) 2017</p>
	 * <p>Company: www.baidudu.com</p>
	 * @param cityNum
	 * @return
	 * @author xianxian
	 * @date 2023年3月2日下午6:08:03
	 * @version 1.0
	 */
	public City queryCityByCityNum(String cityNum) {
		return cityDao.queryCityByCityNum(cityNum);
	}
	
	private CityService(){}
	
	private static CityService cityService;
	
	public static CityService getInstance() {
		if (cityService == null) {
			cityService = new CityService();
		}
		return cityService;
	}

}

package net.tencent.tickets.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.tencent.tickets.entity.City;
import net.tencent.tickets.entity.Province;
import net.tencent.tickets.service.ProvinceService;
import net.tencent.tickets.servlet.other.ProvinceServlet;
import net.tencent.tickets.util.DBUtils_pool;

public class CityDao {
	
	/** 根据省份编号查询所有城市的sql语句 **/
	private static final String QUERY_CITY_BY_PROVINCENUM = 
			"SELECT CITY_ID,CITY_NUM,CITY_NAME,CITY_FATHER from tickets_city where CITY_FATHER=?";
	
	/** 根据城市编号查询城市所有  **/
	
	private static final String QUERY_CITY_BY_CITYNUM = 
			"SELECT CITY_ID,CITY_NUM,CITY_NAME,CITY_FATHER from tickets_city where CITY_NUM=?";
	
	
	/**
	 * <p>Title: queryCityByCityNum</p>
	 * <p>
	 *    Description:
	 * </p>
	 * <p>Copyright: Copyright (c) 2017</p>
	 * <p>Company: www.baidudu.com</p>
	 * @param cityNum
	 * @return
	 * @author xianxian
	 * @date 2023年3月2日下午6:08:47
	 * @version 1.0
	 */
	public City queryCityByCityNum(String cityNum) {
		City city = new City();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtils_pool.getConnection();
			stmt = conn.prepareStatement(QUERY_CITY_BY_CITYNUM);
			stmt.setString(1, cityNum);
			rs = stmt.executeQuery();
			while (rs.next()) {
				city.setCityNum(rs.getString("CITY_NUM"));
				city.setId(rs.getInt("CITY_ID"));
				city.setCityName(rs.getString("CITY_NAME"));
				
				ProvinceService provinceService = ProvinceService.getInstance();
				Province province = provinceService.queryProvinceByProvinceNum(rs.getString("CITY_FATHER"));
				
				city.setProvince(province);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils_pool.release(conn, stmt, rs);
		}
		return city;
	}
	

	
	/**
	 * (non-Javadoc)
	 * <p>Title: queryCityByProvinceNum</p>
	 * <p>
	 *    Description:获取指定省份的所有城市信息
	 * </p>
	 * <p>Copyright: Copyright (c) 2017</p>
	 * <p>Company: www.baidudu.com</p>
	 * @param provinceNum
	 * @return
	 * @see net.tencent.tickets.dao.ifac.CityDaoIfac#queryCityByProvinceNum(java.lang.String)
	 * @author xianxian
	 * @date 2023年2月25日下午9:10:45
	 * @version 1.0
	 */
	public List<City> queryCityByProvinceNum(String provinceNum) {
		List<City> cities = new ArrayList<>();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {

			conn = DBUtils_pool.getConnection();
			stmt = conn.prepareStatement(QUERY_CITY_BY_PROVINCENUM);
			stmt.setString(1, provinceNum);
			rs = stmt.executeQuery();
			while (rs.next()) {
				City c = new City();

				c.setCityNum(rs.getString("CITY_NUM"));
				c.setId(rs.getInt("CITY_ID"));
				c.setCityName(rs.getString("CITY_NAME"));
				c.setProvince(new Province(null,rs.getString("CITY_FATHER"),null));

				cities.add(c);

			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils_pool.release(conn, stmt, rs);
		}
		return cities;
	}

//	 单例模式实现步骤：
//	 1.构造器私有
//	 2.提供私有的静态的当前类类型的变量
//	 3.提供一个公共的静态方法，返回刚才定义的变量，如果这个变量为null，那么给他赋值
	private CityDao() {};
	private static CityDao cityDao;
	public static CityDao getInstance() {
		if(cityDao == null) {
			cityDao = new CityDao();
		}
		return cityDao;
	}
}

CityService cityService = CityService.getInstance();
City city = cityService.queryCityByCityNum(cityNum);

PageUtil pageUtil = new PageUtil(users, Integer.parseInt(pageCount), 1);
request.setAttribute("userList", pageUtil.getUsers_page());// 把查询结果users传给userlist.jsp页面
request.setAttribute("pagesum", pageUtil.getPagesum());// 总页数
request.setAttribute("pageNumber", pageUtil.getPageNumber());// 页码
request.getRequestDispatcher("/admin/userlist.jsp").forward(request, response);

private void exportExcel(HttpServletRequest request,
                         HttpServletResponse response) throws ServletException, IOException{

    HttpSession session = request.getSession();

    @SuppressWarnings("unchecked")
    List<Users> users = (List<Users>)session.getAttribute("users");

    if(users == null || users.size() == 0)
    {
        response.setContentType("text/html;charset=utf-8");
        response.getWriter().println("<script>alert('请先查询');</script>");
    }else
    {
        response.setHeader("Content-disposition", "attachment; filename="
                           + new String("用户".getBytes("GB2312"), "8859_1") + 
                           ".xls");
        response.setHeader("pragma", "no-cache");
        response.setContentType("application/msexcel");
        ServletOutputStream os = response.getOutputStream();
        WritableWorkbook workbook = Workbook.createWorkbook(os);

        WritableSheet ws = workbook.createSheet("用户列表", 0);

        try {
            //首先写表头：id username
            ws.addCell(new Label(0, 0, "id"));
            ws.addCell(new Label(1, 0, "用户名"));
            ws.addCell(new Label(2, 0, "性别"));
            ws.addCell(new Label(3, 0, "证件类型"));
            ws.addCell(new Label(4, 0, "证件号码"));
            ws.addCell(new Label(5, 0, "旅客类型"));


            for(int row = 1;row <= users.size();row++)
            {
                Users user = users.get(row-1);
                ws.addCell(new Label(0, row, user.getId()+""));
                ws.addCell(new Label(1, row, user.getUserName()));
                ws.addCell(new Label(2, row, user.getUserSex() == 49 ? "男" : "女"));
                ws.addCell(new Label(3, row, user.getCertType().getContent()));
                ws.addCell(new Label(4, row, user.getUserCert()));
                ws.addCell(new Label(5, row, user.getUserType().getContent()));
            }

            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        }finally
        {
            try {
                workbook.close();
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
    }	
}

	/**
	 * 统计上线人数
	 * @param request
	 */
	private void online(HttpServletRequest request) {
		ServletContext application = request.getServletContext();
		Object obj = application.getAttribute("onlineCount");
		//第一次进来
		if(obj == null)
		{
			application.setAttribute("onlineCount", 1);
		}
		//下一次进来
		else
		{
			application.setAttribute("onlineCount", (Integer)obj + 1);
		}
	}

// 保存用户登陆信息-----------------------------session:在一次会话中保存数据
session.setAttribute("user", user);

String update_user_sql="UPDATE tickets_user \r\n"
    + "SET tickets_user.USER_REAL_NAME =?,\r\n"
    + "tickets_user.USER_SEX =?,\r\n"
    + "tickets_user.USER_CITY_ID =?,\r\n"
    + "tickets_user.USER_CERTTYPE_ID =?,\r\n"
    + "tickets_user.USER_CERT =?,\r\n"
    + "tickets_user.USER_BIRTHDAY =?,\r\n"
    + "tickets_user.USER_USERTYPE_ID =?,\r\n"
    + "tickets_user.USER_CONTENT =? \r\n"
    + "WHERE\r\n"
    + "	tickets_user.USER_ID =?";

package net.tencent.tickets.servlet.user;

import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.http.Part;

import net.tencent.tickets.entity.Users;
import net.tencent.tickets.service.UserService;

/**
 * 负责处理上传照片的servlet
 */
@WebServlet("/UploadPhotoServlet")
@MultipartConfig
public class UploadPhotoServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Override
	protected void service(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		/*
		 * 1.后台使用servlet3.0技术上传照片
		 * 2.上传照片需要指定服务器上照片保存的地址，photos文件夹保存照片
		 * 3.所以要拿到照片的存储路径，方便回显数据
		 */
		
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		

		// 获取上传的文件集合
		//Collection<Part> parts = request.getParts();
		// 上传单个文件
	
			// Servlet3.0将multipart/form-data的POST请求封装成Part，通过Part对上传的文件进行操作。
			// Part part = parts[0];//从上传的文件集合中获取Part对象
			Part part = request.getPart("uploadFile");// 通过表单file控件(<input type="file" name="file">)的名字直接获取Part对象
			// Servlet3没有提供直接获取文件名的方法,需要从请求头中解析出来
			// 获取请求头，请求头的格式：form-data; name="file"; filename="snmp4j--api.zip"
			String header = part.getHeader("content-disposition");
			
			// 获取文件名
			String fileName = getFileName(header);
			
			// 存储路径 存到了服务器的路径 客户端的文件夹是看不到的 布署完成后可直接使用
			String saveServletPath = request.getServletContext().getRealPath("/photos") + File.separator + fileName;
			
			// 把文件写到指定路径
			//UUID.randomUUID()+".jpg"
			part.write(saveServletPath);

			//把这个照片路径保存到数据库
			HttpSession session = request.getSession();
			Users user_session = (Users)session.getAttribute("user");
			UserService userService = UserService.getInstance();
			Users user = userService.login(user_session.getUserName(), user_session.getUserPassword());
			

			//判断是否上传成功
			if(UserService.getInstance().saveImage(user.getId(),fileName)) {
				//回到更新用户信息页面，让用户看到自己的照片
				response.sendRedirect("ToUpdateUserServlet");
			}else {
				PrintWriter pw = response.getWriter();
				pw.println("<script>alert('上传照片失败，请稍后再试！');</script>");
				pw.flush();
				pw.close();
			}
			
	}
	/**
	 * 
	 * <p>Title: getFileName</p>  
	 * <p>
	 *	Description: 
	 *	根据请求头解析出文件名 请求头的格式：火狐和google浏览器下：form-data; name="file";
	 * filename="snmp4j--api.zip" IE浏览器下：form-data; name="file";
	 * filename="E:\snmp4j--api.zip"
	 * </p> 
	 * @param header 请求头
	 * @return 文件名
	 */
	public String getFileName(String header) {
		/**
		 * String[] tempArr1 =
		 * header.split(";");代码执行完之后，在不同的浏览器下，tempArr1数组里面的内容稍有区别
		 * 火狐或者google浏览器下：
		 * tempArr1={form-data,name="file",filename="snmp4j--api.zip"}
		 * IE浏览器下：tempArr1={form-data,name="file",filename="E:\snmp4j--api.zip"}
		 */
		String[] tempArr1 = header.split(";");
		/**
		 * 火狐或者google浏览器下：tempArr2={filename,"snmp4j--api.zip"}
		 * IE浏览器下：tempArr2={filename,"E:\snmp4j--api.zip"}
		 */
		String[] tempArr2 = tempArr1[2].split("=");
		// 获取文件名，兼容各种浏览器的写法
		String fileName = tempArr2[1].substring(tempArr2[1].lastIndexOf("\\") + 1).replaceAll("\"", "");
		return fileName;
	}
	
}

String update_user_sql="UPDATE tickets_user SET USER_PASSWORD =? WHERE	USER_ID =?";