package com.demo.contract;

import java.io.IOException;
import java.io.PrintWriter;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Time;
import java.sql.Date;
import java.sql.SQLException;

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

import com.appcom.VjExport;
import com.appcom.VjBuffer;
import com.appcom.VjSelect;
import com.common.CommFunc;
import com.common.JdbcPools;
import com.common.dto.WhereDTO;
import com.google.gson.Gson;

/**
 * 合同列表
 *
 * 新建人: 微简
 * 新建时间: 2019-11-14
 * 修改人: 
 * 修改时间: 
 */
@WebServlet("/ContractList")
public class ContractList extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ContractList() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();

		// 校验参数是否为空，参数逗号分隔如："ID","Name"
		// if (!CommFunc.checkParameterExist(request, out, Arrays.asList(""))){
		// 	return;
		// }

		// 获取请求参数		
		int CurrentOrgID = CommFunc.getCurrentOrgID(request);//当前机构ID
		int CurrentDepartmentID = CommFunc.getCurrentDepartmentID(request);// 当前部门ID
		String Title = CommFunc.getRequestString(request, "Title", "");// 合同标题
		String ContractNumber = CommFunc.getRequestString(request, "ContractNumber", "");// 合同编号
		int Type = CommFunc.getRequestInt(request, "Type", 0);// 合同类型
		int DepartmentID = CommFunc.getRequestInt(request, "DepartmentID", 0);// 所属部门
		double Cost = CommFunc.getRequestDouble(request, "Cost", 0);// 合同总金额
		int Leader = CommFunc.getRequestInt(request, "Leader", 0);// 负责人
		String StartDate = CommFunc.getRequestString(request, "StartDate", "");// 合同开始日期（日期段）
		String ContractDate = CommFunc.getRequestString(request, "ContractDate", "");// 签约日期（日期段）
		String Createtime = CommFunc.getRequestString(request, "Createtime", "");// 创建时间（时间段）
		int page = CommFunc.getRequestInt(request, "page", 1);//当前页
		int rows = CommFunc.getRequestInt(request, "rows", 20);//每页行数		
		String sidx = CommFunc.getRequestString(request, "sidx", "");//排序字段
		String sord = CommFunc.getRequestString(request, "sord", "");//desc降序、其它升序  
		String action = CommFunc.getRequestString(request, "action", "");

		// 获取token
		String token = CommFunc.getToken(request);
		// 获取当前登录用户id
		int logonUserid = CommFunc.getUserFormToken(token);
		// 获取登录ip
		String logonIp = CommFunc.getIp(request);
		// 获取请求的终端类型
		String loginTerminal = CommFunc.getTerminal(request);
		// 校验token是否合法
		if (CommFunc.tokenIsValid(token, logonUserid, "")) {
			Connection con = null;
			PreparedStatement ps = null;
			ResultSet rs = null;

			try {
				con = JdbcPools.getConnection();
				String sql = "";

				// 根据request添加where参数
				List<WhereDTO> paList = new ArrayList<WhereDTO>();
				// 添加where参数
				paList.add(new WhereDTO("Title", Title, "varchar", "like"));// 合同标题
				paList.add(new WhereDTO("ContractNumber", ContractNumber, "varchar", "like"));// 合同编号
				paList.add(new WhereDTO("Type", Type, "int", "="));// 合同类型
				paList.add(new WhereDTO("DepartmentID", DepartmentID, "int", "="));// 所属部门
				paList.add(new WhereDTO("Cost", Cost, "decimal", "="));// 合同总金额
				paList.add(new WhereDTO("Leader", Leader, "int", "="));// 负责人
				paList.add(new WhereDTO("StartDate", StartDate, "date", ">=,<="));// 合同开始日期（日期段）
				paList.add(new WhereDTO("ContractDate", ContractDate, "date", ">=,<="));// 签约日期（日期段）
				paList.add(new WhereDTO("Createtime", Createtime, "datetime", ">=,<="));// 创建时间（时间段）
				CommFunc.deleteNoExistParameter(request, paList);	
				String where = CommFunc.addWhereByRequest_Parameters(request, paList);
				//增加数据范围
				where = VjSelect.addWhere_Org(request, logonUserid, where, "Contract", CurrentOrgID, CurrentDepartmentID);
				// 其它where参数
				//where = VjSelect.addWhere(where, "Name='张三'");//示例

				int pageTotal = 0;
				int recordNumber = 0;
				sql = "select count(1) from Contract" + where + ";";
				ps = con.prepareStatement(sql);
				// 根据请求参数动态循环生成参数赋值
				CommFunc.addPsParamByRequest_real_obj(ps, paList);
				rs = ps.executeQuery();
				if (rs.next()) {
					recordNumber = rs.getInt(1);
				}
				pageTotal = CommFunc.getPageTotal(recordNumber, rows);
				// 得到limit
				String limit = CommFunc.getLimit(page, rows, action);
				// 得到orderby
				String orderby = CommFunc.getOrderby(sidx, sord, "Updatetime desc");

				// 清理上一次除con连接外的资源
				JdbcPools.closeResultSetPreparedStatement(rs, ps);

				sql = "select * from Contract" + where.toString() + orderby + limit + ";";
				ps = con.prepareStatement(sql);
				// 根据请求参数动态生成参数赋值
				CommFunc.addPsParamByRequest_real_obj(ps, paList);
				// 其它参数
				// ps.setInt(6, 0);//示例
				rs = ps.executeQuery();

				// data列表
				List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
				// json返回列表
				Map<String, Object> JsonResult = new LinkedHashMap<String, Object>();
				// 添加返回信息
				JsonResult.putAll(CommFunc.getCodeMsg("001", "成功"));
				JsonResult.put("total", pageTotal);// 总页数
				JsonResult.put("page", page);// 当前页
				JsonResult.put("records", recordNumber);// 查询出的记录数


				if (loginTerminal.equals("pc")) { // pc端
					while (rs.next()) {
						Map<String, Object> row = new LinkedHashMap<String, Object>();// 每一行数据

						row.put("ID", rs.getInt("ID"));// ID
						row.put("Title", CommFunc.filterStr(rs.getString("Title")));// 合同标题
						row.put("ContractNumber", CommFunc.filterStr(rs.getString("ContractNumber")));// 合同编号
						row.put("Type", CommFunc.getNameById("ContractType", rs.getInt("Type")));// 合同类型_名称
						row.put("DepartmentID", CommFunc.getNameById("Org", rs.getInt("DepartmentID")));// 所属部门_名称
						row.put("Cost", rs.getDouble("Cost"));// 合同总金额
						row.put("Leader", CommFunc.getNameById("User", rs.getInt("Leader")));// 负责人_名称
						row.put("StartDate", CommFunc.dateToString(rs.getDate("StartDate")));// 合同开始日期
						row.put("EndDate", CommFunc.dateToString(rs.getDate("EndDate")));// 合同结束日期
						row.put("ContractDate", CommFunc.dateToString(rs.getDate("ContractDate")));// 签约日期
						row.put("OurContractor", CommFunc.getNameById("User", rs.getInt("OurContractor")));// 我方签约人_名称
						row.put("ClientContractor", CommFunc.filterStr(rs.getString("ClientContractor")));// 客户方签约人
						row.put("Appendix", CommFunc.filterStr(rs.getString("Appendix")));// 合同附件
						row.put("Remarks", CommFunc.filterStr(rs.getString("Remarks")));// 备注
						row.put("OrgID", CommFunc.getNameById("Org", rs.getInt("OrgID")));// 机构_名称
						row.put("Createtime", CommFunc.datetimeToString(rs.getTimestamp("Createtime")));// 创建时间
						row.put("CreateUser", CommFunc.getNameById("User", rs.getInt("CreateUser")));// 创建人_名称
						row.put("Updatetime", CommFunc.datetimeToString(rs.getTimestamp("Updatetime")));// 修改时间
						row.put("UpdateUser", CommFunc.getNameById("User", rs.getInt("UpdateUser")));// 修改人_名称

						data.add(row);
					}
					
					JsonResult.put("rows", data);// jqgrid格式

				} else {
					while (rs.next()) {
						Map<String, Object> row = new LinkedHashMap<String, Object>();// 每一行数据

						data.add(row);
					}
					Map<String, List<Map<String, Object>>> items = new LinkedHashMap<String, List<Map<String, Object>>>();
					items.put("items", data);
					JsonResult.put("data", items);
				}

				// 转换json
				Gson gson = new Gson();
				String jsonStr = gson.toJson(JsonResult);

				if (action.equals("export")) { // excel报表导出
					String[] rowsName = new String[] { "ID","合同标题","合同编号","合同类型","所属部门","合同总金额","负责人",
						"合同开始日期","合同结束日期","签约日期","我方签约人","客户方签约人","合同附件","备注","机构","创建时间",
						"创建人","修改时间","修改人" };
					String[] FieldNames = new String[] { "ID","Title","ContractNumber","Type","DepartmentID","Cost","Leader",
						"StartDate","EndDate","ContractDate","OurContractor","ClientContractor","Appendix","Remarks","OrgID","Createtime",
						"CreateUser","Updatetime","UpdateUser" };

					VjExport.export(response, "", rowsName, FieldNames, "Contract", data);
				} else {
					// 以json格式输出
					out.print(jsonStr);
				}

				// 日志
				CommFunc.writeLog("ContractList", request, "", logonUserid, logonIp);

			} catch (SQLException e) {
				out.print(CommFunc.sqlError());
				CommFunc.error(e);
			} catch (Exception e) {
				out.print(CommFunc.otherError());
				CommFunc.error(e);
			} finally {
				JdbcPools.closeAll(rs, ps, con);
			}
		} else {
		    out.print(CommFunc.tokenError());
		}	
	}

}