package com.ruoyi.imports;

import java.io.File;
import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mchange.io.FileUtils;
import com.ruoyi.common.config.RuoYiConfig;
import com.ruoyi.common.constant.IpcType;
import com.ruoyi.common.constant.LocStr;
import com.ruoyi.common.utils.http.HttpUtils;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.imports.util.DbConfig;
import com.ruoyi.imports.util.DbUtils;
import com.ruoyi.imports.util.GsonUtils;
import com.ruoyi.reportpub.domain.DPatentPubInfo;
import com.ruoyi.reportpub.service.IDPatentPubInfoService;
import com.ruoyi.system.domain.BladeRegion;
import com.ruoyi.system.domain.DFixAddressArea;
import com.ruoyi.system.domain.DIpcType;
import com.ruoyi.system.service.IBladeRegionService;
import com.ruoyi.system.service.IDIpcTypeService;

public class PatentPubImportUtils {

	private static final Logger logger = LoggerFactory.getLogger(PatentPubImportUtils.class);

	private static class PubSyncThread extends Thread {
		private ResClass mod;
		RuoYiConfig ruoYiConfig;
		IDPatentPubInfoService dPatentPubInfoService;
		IDIpcTypeService dIpcTypeService;
		IBladeRegionService bladeRegionService;

		public PubSyncThread(ResClass mod, RuoYiConfig ruoYiConfig, IDPatentPubInfoService dPatentPubInfoService, IDIpcTypeService dIpcTypeService,
				IBladeRegionService bladeRegionService) {
			this.mod = mod;
			this.ruoYiConfig = ruoYiConfig;
			this.dPatentPubInfoService = dPatentPubInfoService;
			this.dIpcTypeService = dIpcTypeService;
			this.bladeRegionService = bladeRegionService;
		}

		public void run() {

			DbUtils dbUtils;
			Connection conn;
			try {
				DbConfig dbConfig = new DbConfig();

				String jdbcurl = SpringUtils.getApplicationContext().getEnvironment().getProperty("spring.datasource.druid.master.url");
				logger.debug("重置数据源:" + jdbcurl);
				if (StringUtils.isNotBlank(jdbcurl)) {
					dbConfig.setUrl(jdbcurl);
				}
				dbUtils = DbUtils.newDbUtil(dbConfig);
				conn = dbUtils.getConnections();
			} catch (Exception e) {
				e.printStackTrace();
				logger.error("同步失败，数据库失败," + e.getMessage(), e);
				throw new RuntimeException(e.getMessage(), e);
			}

			try {
				for (ResItem item : mod.results) {
//				TODO	
//				DPatentPubInfo exist = dPatentPubInfoService.queryByPid(item._id, true);
//				DPatentPubInfo exist = dPatentPubInfoService.queryByPidFromCache(item._id);
					DPatentPubInfo exist = null;
					if (exist != null) {
						logger.info("存在公开数据,pid:" + item._id);
						if (StringUtils.isNotBlank(item.pub_date)) {
							if (20230703 < Integer.parseInt(item.pub_date)) {
								logger.info("查询到20230703以后的公开数据:" + item._id);
							}
						}
						updatePub(dPatentPubInfoService, bladeRegionService, exist, item);

					} else {
						logger.debug("不存在公开数据,pid:" + item._id);

//					TODO	匹配一下固定地址	20230705
						String modify_appli_city = item.modify_appli_city;
						if (StringUtils.isBlank(modify_appli_city)) {
							modify_appli_city = item.appli_city;
						}
						String address = ResItem.getAddress(item.appli_address);
						DFixAddressArea area = dPatentPubInfoService.matchByAddress(address, modify_appli_city);
						if (area != null) {
							item.yunnan_modify_appli_city = area.getCityName();
							item.yunnan_modify_appli_county = area.getAreaName();
							item.yunnan_modify_appli_province = area.getProvinceName();
						}

						List param = new ArrayList();
						String sql = ResItem.toSql(item, param, bladeRegionService, dIpcTypeService);
						int ext = dbUtils.executeUpdate(conn, sql, param);

					}
				}
			} catch (Exception e) {
				logger.error("公开数据同步失败," + e.getMessage(), e);
				throw new RuntimeException("同步失败," + e.getMessage(), e);

			} finally {
				dbUtils.close(conn);
			}
		}
	}

	public static void impByIprd(RuoYiConfig ruoYiConfig, IDPatentPubInfoService dPatentPubInfoService, IDIpcTypeService dIpcTypeService,
			IBladeRegionService bladeRegionService, String time_update) throws Exception {

		List<PubSyncThread> threads = new ArrayList<PubSyncThread>();

		logger.debug("准备同步专利公开数据,time_update:" + time_update);

		logger.debug("开始加载公开数据...");
		long begin = System.currentTimeMillis();
//		dPatentPubInfoService.loadToCacheWidthPid();
		dPatentPubInfoService.clearData();
		long endTime = System.currentTimeMillis();
		logger.debug("公开数据清理完成,用时:" + (endTime - begin));

		try {
			// TODO Auto-generated method stub
//		http://111.198.99.166:20000/pat/iprd_patent?project_code=530000&ak=77f39e749%4020230406&time_update=20210431&size=10000
			String baseUrl = ruoYiConfig.getPressApiServer();
			baseUrl = baseUrl + "pat/iprd_patent";
			baseUrl = baseUrl + "?project_code=530000";
			baseUrl = baseUrl + "&ak=77f39e749%4020230406";
			baseUrl = baseUrl + "&time_update=" + time_update;
			baseUrl = baseUrl + "&size=10000";

			String code = "200";
			String scroll_id = null;

			int i = 0;
			while ("200".equals(code)) {
				String url = baseUrl;
				if (StringUtils.isNotBlank(scroll_id)) {
					url = baseUrl + "&scroll_id=" + scroll_id;
				}
				i++;
				logger.debug("开始查询第" + i + "页专利公开数据,url:" + url);
				long beginTime = System.currentTimeMillis();
				String httpRes = HttpUtils.sendGet(url);
//				logger.debug("第" + i + "页专利公开数据查询结果,用时:" + (System.currentTimeMillis() - beginTime) + ",res:" + httpRes);
				logger.debug("第" + i + "页专利公开数据查询结果,用时:" + (System.currentTimeMillis() - beginTime));
				logger.debug("公开数据结果:" + httpRes);
				ResClass mod = GsonUtils.formJson(httpRes, ResClass.class);
				scroll_id = mod.scroll_id;
				code = mod.code;
				if (!"200".equals(code) && !"204".equals(code)) {
					throw new RuntimeException("同步第[" + i + "]页公开数据同步失败，请求响应码[" + code + "]不正常");
				}
				if (mod.results == null || mod.results.isEmpty()) {
					break;
				}

				PubSyncThread thread = new PubSyncThread(mod, ruoYiConfig, dPatentPubInfoService, dIpcTypeService, bladeRegionService);
				thread.setDaemon(true);
				thread.setName(Thread.currentThread().getName() + "-ps-" + i);
				threads.add(thread);
				thread.start();
			}

			for (PubSyncThread thread : threads) {
				thread.join();
			}

			logger.info("公开专利数据同步完成...");
		} catch (Throwable e) {
			logger.error("同步专利公开数据失败," + e.getMessage(), e);
			throw new RuntimeException(e);
		}

	}

	private static void updatePub(IDPatentPubInfoService dPatentPubInfoService, IBladeRegionService bladeRegionService, DPatentPubInfo old, ResItem item) {

		DPatentPubInfo up = new DPatentPubInfo();
		up.setId(old.getId());

		String modify_appli_province_code = null;
		String modify_appli_city_code = null;
		String modify_appli_county_code = null;

		if (StringUtils.isNotBlank(item.modify_appli_province)) {
			BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.modify_appli_province);
			if (bladeRegion != null) {
				modify_appli_province_code = bladeRegion.getCode();
			} else {
				modify_appli_province_code = "";
			}
		} else {
			modify_appli_province_code = "";
		}
		if (StringUtils.isNotBlank(item.modify_appli_city) && StringUtils.isNotBlank(modify_appli_province_code)) {
			BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.modify_appli_city, modify_appli_province_code);
			if (bladeRegion != null) {
				modify_appli_city_code = bladeRegion.getCode();
			} else {
				modify_appli_city_code = "";
			}
		} else {
			modify_appli_city_code = "";
		}
		if (StringUtils.isNotBlank(item.modify_appli_county) && StringUtils.isNotBlank(modify_appli_city_code)) {
			BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.modify_appli_county, modify_appli_city_code);
			if (bladeRegion != null) {
				modify_appli_county_code = bladeRegion.getCode();
			} else {
				modify_appli_county_code = "";
			}
		} else {
			modify_appli_county_code = "";
		}

//		if (StringUtils.isNotBlank(modify_appli_province_code)) {
		up.setModifyAppliProvince(item.modify_appli_province == null ? "" : item.modify_appli_province);
		up.setModifyAppliProvinceCode(modify_appli_province_code);
//		}
//		if (StringUtils.isNotBlank(modify_appli_city_code)) {
		up.setModifyAppliCity(item.modify_appli_city == null ? "" : item.modify_appli_city);
		up.setModifyAppliCityCode(modify_appli_city_code);
//		}
//		if (StringUtils.isNotBlank(modify_appli_county_code)) {
		up.setModifyAppliCounty(item.modify_appli_county == null ? "" : item.modify_appli_county);
		up.setModifyAppliCountyCode(modify_appli_county_code);
//		}

		if (StringUtils.isNotBlank(item.appli_type)) {
			up.setAppliType(item.appli_type);
			up.setModifyAppliType(item.appli_type);
		}

		if (StringUtils.isNotBlank(item.status)) {
			up.setStatus(item.status);
			up.setPatentStatus(item.status);
		}

		if (StringUtils.isNotBlank(item.app_date)) {
			up.setAppDate(item.app_date);
		}
		if (StringUtils.isNotBlank(item.app_number)) {
			String appNumber2 = item.app_number;
			if (appNumber2.startsWith("CN")) {
				appNumber2 = appNumber2.replace("CN", "");
			}

			if (appNumber2.contains(".")) {
				appNumber2 = appNumber2.replace(".", "");
			}
			up.setAppNumber2(appNumber2);
		}

		if (StringUtils.isNotBlank(item.pri_date)) {
			up.setPriDate(item.pri_date);
		}
		if (StringUtils.isNotBlank(item.pri_number)) {
			up.setPriNumber(item.pri_number);
		}
		if (StringUtils.isNotBlank(item.pub_date)) {
			up.setPubDate(item.pub_date);
		}
		if (StringUtils.isNotBlank(item.pub_number)) {
			up.setPubNumber(item.pub_number);
		}
		if (StringUtils.isNotBlank(item.time_update)) {
			up.setTimeUpdate(item.time_update);
		}
		if (StringUtils.isNotBlank(item.high_value)) {
			up.setHighValue(item.high_value);
			if (item.high_value.contains("1")) {
				up.setHvSeisDept(1);
			}
//			20230710 2,3 对调
			if (item.high_value.contains("3")) {
				up.setHvKeep10Dept(1);
			}
			if (item.high_value.contains("2")) {
				up.setHvHaveOverseasDept(1);
			}

//			if (item.high_value.contains("2")) {
//				up.setHvKeep10Dept(1);
//			}
//			if (item.high_value.contains("3")) {
//				up.setHvHaveOverseasDept(1);
//			}
			if (item.high_value.contains("4")) {
				up.setHvHighPledgeDept(1);
			}
			if (item.high_value.contains("5")) {
				up.setHvTechnologyAwardDept(1);
			}
		}
		if (StringUtils.isNotBlank(up.getPubDate())) {
			String year = up.getPubDate().trim().substring(0, 4);
			String month = up.getPubDate().trim().substring(4, 6);
			up.setCreateMonth(year + "-" + month);
		}

		String cowner_province_code = null;
		String cowner_city_code = null;
		String cowner_county_code = null;
		String modify_cowner_province_code = null;
		String modify_cowner_city_code = null;
		String modify_cowner_county_code = null;

//		20230616
		if (StringUtils.isNotBlank(item.cowner_province)) {
			BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.cowner_province);
			if (bladeRegion != null) {
				cowner_province_code = bladeRegion.getCode();
			} else {
				cowner_province_code = "";
			}
		} else {
			cowner_province_code = "";
		}
		if (StringUtils.isNotBlank(item.cowner_city) && StringUtils.isNotBlank(cowner_province_code)) {
			BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.cowner_city, cowner_province_code);
			if (bladeRegion != null) {
				cowner_city_code = bladeRegion.getCode();
			} else {
				cowner_city_code = "";
			}
		} else {
			cowner_city_code = "";
		}
		if (StringUtils.isNotBlank(item.cowner_county) && StringUtils.isNotBlank(cowner_city_code)) {
			BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.cowner_county, cowner_city_code);
			if (bladeRegion != null) {
				cowner_county_code = bladeRegion.getCode();
			} else {
				cowner_county_code = "";
			}
		} else {
			cowner_county_code = "";
		}

		if (StringUtils.isNotBlank(item.modify_cowner_province)) {
			BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.modify_cowner_province);
			if (bladeRegion != null) {
				modify_cowner_province_code = bladeRegion.getCode();
			} else {
				modify_cowner_province_code = "";
			}
		} else {
			modify_cowner_province_code = "";
		}
		if (StringUtils.isNotBlank(item.modify_cowner_city) && StringUtils.isNotBlank(modify_cowner_province_code)) {
			BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.modify_cowner_city, modify_cowner_province_code);
			if (bladeRegion != null) {
				modify_cowner_city_code = bladeRegion.getCode();
			} else {
				modify_cowner_city_code = "";
			}
		} else {
			modify_cowner_city_code = "";
		}
		if (StringUtils.isNotBlank(item.modify_cowner_county) && StringUtils.isNotBlank(modify_cowner_city_code)) {
			BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.modify_cowner_county, modify_cowner_city_code);
			if (bladeRegion != null) {
				modify_cowner_county_code = bladeRegion.getCode();
			} else {
				modify_cowner_county_code = "";
			}
		} else {
			modify_cowner_county_code = "";
		}

		up.setCownerProvince(item.cowner_province == null ? "" : item.cowner_province);
		up.setCownerCity(item.cowner_city == null ? "" : item.cowner_city);
		up.setCownerCounty(item.cowner_county == null ? "" : item.cowner_county);
		up.setModifyCownerProvince(item.modify_cowner_province == null ? "" : item.modify_cowner_province);
		up.setModifyCownerCity(item.modify_cowner_city == null ? "" : item.modify_cowner_city);
		up.setModifyCownerCounty(item.modify_cowner_county == null ? "" : item.modify_cowner_county);

		up.setCownerProvinceCode(cowner_province_code);
		up.setCownerCityCode(cowner_city_code);
		up.setCownerCountyCode(cowner_county_code);
		up.setModifyCownerProvinceCode(modify_cowner_province_code);
		up.setModifyCownerCityCode(modify_cowner_city_code);
		up.setModifyCownerCountyCode(modify_cowner_county_code);

		dPatentPubInfoService.updateById(up);
	}

	private static void doImp(File file, IBladeRegionService bladeRegionService, IDIpcTypeService dIpcTypeService, boolean iprd) throws Exception {
		String str = FileUtils.getContentsAsString(file);
		ResClass res = GsonUtils.formJson(str, ResClass.class);
		DbUtils dbUtils = DbUtils.newDbUtil(new DbConfig());

		Connection conn = dbUtils.getConnections();

		for (int i = 0; i < res.results.size(); i++) {
			ResItem item = res.results.get(i);
			List param = new ArrayList();
			String sql = ResItem.toSql(item, param, bladeRegionService, dIpcTypeService);

//			logger.debug("准备执行sql:"+sql);
//			logger.debug("参数:"+GsonUtils.toJson(param));

			int ext = dbUtils.executeUpdate(conn, sql, param);

			System.out.println("第i:" + i + "条执行结果,file:" + file.getName() + ",:" + ext + ",一共" + res.results.size() + "条");
		}

		dbUtils.close(conn);
	}

	public static void imp(IBladeRegionService bladeRegionService, IDIpcTypeService dIpcTypeService) throws Exception {
		// TODO Auto-generated method stub

//		String filePath = "D:\\9fu\\业务\\云南省知识产权数据统计系统\\iprd_patent _20230328.json";
//		String filePath = "/data/iprd_patent _20230328.json";
		String filePathStr = "/data/yunnan-report-admin/data";

		File filePath = new File(filePathStr);

		List<File> fileList = new ArrayList<File>();
		File[] files = filePath.listFiles();
		for (File file : files) {
			if (file.isFile() && file.getName().endsWith("json")) {
				fileList.add(file);
			}
		}

		Collections.sort(fileList, new Comparator<File>() {
			@Override
			public int compare(File o1, File o2) {
				return o1.getName().compareTo(o2.getName());
			}
		});

		for (File file : fileList) {
			doImp(file, bladeRegionService, dIpcTypeService, false);
		}

	}

	public static String getKeyByAppNumber(String appNumber2) {
		String key = "DPatentPubInfo:AppNumber:" + appNumber2.trim();
		return key;
	}

	public static class ResClass {
		public String code;
		public Integer total;
		public String scroll_id;
		public List<ResItem> results;

	}

	public static class ResItem {
		public String _id;
		public int pre_counts;
		public String high_value;
		public String appli_province;
		public int lic_counts;
		public String title;
		public String ipc_main;
		public String appli_city;
		public int ple_counts;
		public String appli;
		public String app_date;
		public int app_tran_counts;
		public String loc_str;
		public String modify_appli_city;
		public int curt_counts;
		public String national_eco_str;
		public String agency_name;
		public String modify_appli_province;
		public String appli_county;
		public String pub_number;
		public String appli_address;
		public int pat_tran_counts;
		public String app_number;
		public String modify_appli_county;
		public String patent_type;
		public String appli_type;
		public String status;

		public String cowner_province;
		public String cowner_city;
		public String cowner_county;
		public String modify_cowner_province;
		public String modify_cowner_city;
		public String modify_cowner_county;

		public String pub_date;
		public String pri_number;
		public String pri_date;

		public String yunnan_modify_appli_province;
		public String yunnan_modify_appli_city;
		public String yunnan_modify_appli_county;
		public String time_create;
		public String time_update;
		public String current_owners;
		public String cowner_address;

		public Integer hv_seis_dept = 0;
		public Integer hv_have_overseas_dept = 0;
		public Integer hv_keep10_dept = 0;
		public Integer hv_high_pledge_dept = 0;
		public Integer hv_technology_award_dept = 0;

		public String app_number2;

//		,,,pub_date,pri_number,pri_date,ple_counts

		public static String toSql(ResItem item, List param, IBladeRegionService bladeRegionService, IDIpcTypeService dIpcTypeService) {
			String sql = "insert into d_patent_pub_info(pub_id,pre_counts,high_value,appli_province,lic_counts,"
					+ "title,ipc_main,appli_city,ple_counts,appli,app_date,app_tran_counts,loc_str,"
					+ "modify_appli_city,curt_counts,national_eco_str,agency_name,"
					+ "modify_appli_province,appli_county,pub_number,appli_address,pat_tran_counts,app_number,modify_appli_county,"
					+ "patent_type,appli_type,status,pub_date,pri_number,pri_date,"
					+ "yunnan_modify_appli_province,yunnan_modify_appli_city,yunnan_modify_appli_county,"
					+ "time_create,time_update,current_owners,cowner_address," + "hv_seis_dept,hv_have_overseas_dept,hv_keep10_dept,hv_high_pledge_dept,"
					+ "hv_technology_award_dept,app_number2,create_time,patent_holder_pub," + "appli_province_code,appli_city_code,appli_county_code,"
					+ "modify_appli_province_code,modify_appli_city_code,modify_appli_county_code,"
					+ "yunnan_modify_appli_province_code,yunnan_modify_appli_city_code,yunnan_modify_appli_county_code,"
					+ "modify_appli_address,modify_cowner_address,modify_loc_str,modify_loc_str_desc,"
					+ "modify_ipc_type,modify_ipc_sub_type,modify_ipc_type_name,modify_ipc_sub_type_name,modify_appli_type,"
					+ "year_batch,month_batch,patent_status,create_month,"
//ALTER TABLE d_patent_pub_info add cowner_province varchar ( 100 ) comment '当前第一专利权人省份';
//ALTER TABLE d_patent_pub_info add cowner_city varchar ( 100 ) comment '当前第一专利权人城市';
//ALTER TABLE d_patent_pub_info add cowner_county varchar ( 100 ) comment '当前第一专利权人区县';
//ALTER TABLE d_patent_pub_info add modify_cowner_province varchar ( 100 ) comment '修改后当前第一专利权人省份';
//ALTER TABLE d_patent_pub_info add modify_cowner_city varchar ( 100 ) comment '修改后当前第一专利权人城市';
//ALTER TABLE d_patent_pub_info add modify_cowner_county varchar ( 100 ) comment '修改后当前第一专利权人区县';
//ALTER TABLE d_patent_pub_info add cowner_province_code varchar ( 100 ) comment '当前第一专利权人省份代码';
//ALTER TABLE d_patent_pub_info add cowner_city_code varchar ( 100 ) comment '当前第一专利权人城市代码';
//ALTER TABLE d_patent_pub_info add cowner_county_code varchar ( 100 ) comment '当前第一专利权人区县代码';
//ALTER TABLE d_patent_pub_info add modify_cowner_province_code varchar ( 100 ) comment '修改后当前第一专利权人省份代码';
//ALTER TABLE d_patent_pub_info add modify_cowner_city_code varchar ( 100 ) comment '修改后当前第一专利权人城市代码';
//ALTER TABLE d_patent_pub_info add modify_cowner_county_code varchar ( 100 ) comment '修改后当前第一专利权人区县代码';
					+ "cowner_province,cowner_city,cowner_county,modify_cowner_province,modify_cowner_city,modify_cowner_county,"
					+ "cowner_province_code,cowner_city_code,cowner_county_code,"
					+ "modify_cowner_province_code,modify_cowner_city_code,modify_cowner_county_code" + ")values(" + "?,?,?,?,?," + "?,?,?,?,?,?,?,?,"
					+ "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,?,?,?,sysdate(),?,?,?,?,?,?,?," + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,"
					+ "?,?,?,?,?,?,?,?,?,?,?,?)";

//			public String _id;
			param.add(item._id);
//			public String pre_counts;
			param.add(item.pre_counts);
//			public String high_value;
			param.add(item.high_value);
//			public String appli_province;
			if (StringUtils.isBlank(item.appli_province)) {
				if (StringUtils.isNotBlank(item.modify_appli_province)) {
					item.appli_province = item.modify_appli_province;
				} else if (StringUtils.isNotBlank(item.yunnan_modify_appli_province)) {
					item.appli_province = item.yunnan_modify_appli_province;
					item.modify_appli_province = item.yunnan_modify_appli_province;
				}
			}

			if (StringUtils.isBlank(item.modify_appli_province) && StringUtils.isNotBlank(item.yunnan_modify_appli_province)) {
				item.modify_appli_province = item.yunnan_modify_appli_province;
			}
			if (StringUtils.isBlank(item.modify_appli_province) && StringUtils.isNotBlank(item.appli_province)) {
				item.modify_appli_province = item.appli_province;
			}
			param.add(item.appli_province);
//			public String lic_counts;
			param.add(item.lic_counts);
//			public String title;
			param.add(item.title);
//			public String ipc_main;
//			public String appli_city;
//			public String ple_counts;
//			public String appli;
//			public String app_date;
//			public String app_tran_counts;
//			public String loc_str;
//			public String modify_appli_city;
//			public String curt_counts;
//			public String national_eco_str;
//			public String agency_name;
//			public String modify_appli_province;
//			public String appli_county;
//			public String pub_number;
//			public String appli_address;
//			public String pat_tran_counts;
//			public String app_number;
//			public String modify_appli_county;
//			public String patent_type;
//			public String appli_type;
//			public String status;

			param.add(item.ipc_main);
			if (StringUtils.isBlank(item.appli_city)) {
				if (StringUtils.isNotBlank(item.modify_appli_city)) {
					item.appli_city = item.modify_appli_city;
				} else if (StringUtils.isNotBlank(item.yunnan_modify_appli_city)) {
					item.appli_city = item.yunnan_modify_appli_city;
					item.modify_appli_city = item.yunnan_modify_appli_city;
				}
			}
			if (StringUtils.isBlank(item.modify_appli_city) && StringUtils.isNotBlank(item.yunnan_modify_appli_city)) {
				item.modify_appli_city = item.yunnan_modify_appli_city;
			}
			if (StringUtils.isBlank(item.modify_appli_city) && StringUtils.isNotBlank(item.appli_city)) {
				item.modify_appli_city = item.appli_city;
			}
			param.add(item.appli_city);

			param.add(item.ple_counts);
			param.add(item.appli);
			param.add(item.app_date);
			param.add(item.app_tran_counts);
			param.add(item.loc_str);

			param.add(item.modify_appli_city);
			param.add(item.curt_counts);
			param.add(item.national_eco_str);
			param.add(item.agency_name);

			param.add(item.modify_appli_province);

			if (StringUtils.isBlank(item.appli_county)) {
				if (StringUtils.isNotBlank(item.modify_appli_county)) {
					item.appli_county = item.modify_appli_county;
				} else if (StringUtils.isNotBlank(item.yunnan_modify_appli_county)) {
					item.appli_county = item.yunnan_modify_appli_county;
					item.modify_appli_county = item.yunnan_modify_appli_county;
				}
			}

			if (StringUtils.isBlank(item.modify_appli_county)) {
				if (StringUtils.isNotBlank(item.appli_county)) {
					item.modify_appli_county = item.appli_county;
				}
			}

			param.add(item.appli_county);

			param.add(item.pub_number);
			param.add(item.appli_address);
			param.add(item.pat_tran_counts);
			param.add(item.app_number);

			param.add(item.modify_appli_county);
			param.add(item.patent_type);
			param.add(item.appli_type);
			param.add(item.status);

//			public String pub_date;
//			public String pri_number;
//			public String pri_date;
			param.add(item.pub_date);
			param.add(item.pri_number);
			param.add(item.pri_date);

			if (StringUtils.isBlank(item.yunnan_modify_appli_province)) {
				if (StringUtils.isNotBlank(item.modify_appli_province)) {
					item.yunnan_modify_appli_province = item.modify_appli_province;
				} else if (StringUtils.isNotBlank(item.appli_province)) {
					item.yunnan_modify_appli_province = item.appli_province;
				}
			}
			param.add(item.yunnan_modify_appli_province);

			if (StringUtils.isBlank(item.yunnan_modify_appli_city)) {
				if (StringUtils.isNotBlank(item.modify_appli_city)) {
					item.yunnan_modify_appli_city = item.modify_appli_city;
				} else if (StringUtils.isNotBlank(item.appli_city)) {
					item.yunnan_modify_appli_city = item.appli_city;
				}
			}
			param.add(item.yunnan_modify_appli_city);

			if (StringUtils.isBlank(item.yunnan_modify_appli_county)) {
				if (StringUtils.isNotBlank(item.modify_appli_county)) {
					item.yunnan_modify_appli_county = item.modify_appli_county;
				} else if (StringUtils.isNotBlank(item.appli_county)) {
					item.yunnan_modify_appli_county = item.appli_county;
				}
			}
			param.add(item.yunnan_modify_appli_county);

			param.add(item.time_create);
			param.add(item.time_update);
			param.add(item.current_owners);
			param.add(item.cowner_address);

			if (StringUtils.isNotBlank(item.high_value)) {
				if (item.high_value.contains("1")) {
					item.hv_seis_dept = 1;
				}
//				if (item.high_value.contains("2")) {
//					item.hv_keep10_dept = 1;
//				}
//				if (item.high_value.contains("3")) {
//					item.hv_have_overseas_dept = 1;
//				}
//				20230710 2,3 对调
				if (item.high_value.contains("3")) {
					item.hv_keep10_dept = 1;
				}
				if (item.high_value.contains("2")) {
					item.hv_have_overseas_dept = 1;
				}
				if (item.high_value.contains("4")) {
					item.hv_high_pledge_dept = 1;
				}
				if (item.high_value.contains("5")) {
					item.hv_technology_award_dept = 1;
				}

			}

			param.add(item.hv_seis_dept);
			param.add(item.hv_have_overseas_dept);
			param.add(item.hv_keep10_dept);
			param.add(item.hv_high_pledge_dept);
			param.add(item.hv_technology_award_dept);

			String appNumber2 = item.app_number;
			if (appNumber2.startsWith("CN")) {
				appNumber2 = appNumber2.replace("CN", "");
			}

			if (appNumber2.contains(".")) {
				appNumber2 = appNumber2.replace(".", "");
			}
			item.app_number2 = appNumber2;

			param.add(appNumber2);
			param.add(item.current_owners);

			// 省代码
			String appli_province_code = null;
			if (StringUtils.isNotBlank(item.appli_province)) {
//				TODO	
				BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.appli_province);
				if (bladeRegion != null) {
					appli_province_code = bladeRegion.getCode();
				}
			}
			param.add(appli_province_code);

			// 市代码
			String appli_city_code = null;
			if (StringUtils.isNotBlank(appli_province_code) && StringUtils.isNotBlank(item.appli_city)) {
				BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.appli_city, appli_province_code);
				if (bladeRegion != null) {
					appli_city_code = bladeRegion.getCode();
				}
			}
			param.add(appli_city_code);

			// 区县
			String appli_county_code = null;
			if (StringUtils.isNotBlank(appli_city_code) && StringUtils.isNotBlank(item.appli_county)) {
				BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.appli_county, appli_city_code);
				if (bladeRegion != null) {
					appli_county_code = bladeRegion.getCode();
				}
			}
			param.add(appli_county_code);

			// 省
			String modify_appli_province_code = null;
			if (StringUtils.isNotBlank(item.modify_appli_province)) {
//				TODO	
				BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.modify_appli_province);
				if (bladeRegion != null) {
					modify_appli_province_code = bladeRegion.getCode();
				}
			}
			param.add(modify_appli_province_code);

//			
			// 市
			String modify_appli_city_code = null;
			if (StringUtils.isNotBlank(modify_appli_province_code) && StringUtils.isNotBlank(item.modify_appli_city)) {
				BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.modify_appli_city, modify_appli_province_code);
				if (bladeRegion != null) {
					modify_appli_city_code = bladeRegion.getCode();
				}
			}
			param.add(modify_appli_city_code);

//			

			// 区县
			String modify_appli_county_code = null;
			if (StringUtils.isNotBlank(modify_appli_city_code) && StringUtils.isNotBlank(item.modify_appli_county)) {
				BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.modify_appli_county, modify_appli_city_code);
				if (bladeRegion != null) {
					modify_appli_county_code = bladeRegion.getCode();
				}
			}
			param.add(modify_appli_county_code);

//			yunnan_modify_appli_province_code,yunnan_modify_appli_city_code,yunnan_modify_appli_county_code"
//			+ "yunnan_modify_appli_province_code,yunnan_modify_appli_city_code,yunnan_modify_appli_county_code"

			// 省
			String yunnan_modify_appli_province_code = null;
			if (StringUtils.isNotBlank(item.yunnan_modify_appli_province)) {
//				TODO	
				BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.yunnan_modify_appli_province);
				if (bladeRegion != null) {
					yunnan_modify_appli_province_code = bladeRegion.getCode();
				}
			}
			param.add(yunnan_modify_appli_province_code);

			// 市
			String yunnan_modify_appli_city_code = null;
			if (StringUtils.isNotBlank(yunnan_modify_appli_province_code) && StringUtils.isNotBlank(item.yunnan_modify_appli_city)) {
				BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.yunnan_modify_appli_city, yunnan_modify_appli_province_code);
				if (bladeRegion != null) {
					yunnan_modify_appli_city_code = bladeRegion.getCode();
				}
			}
			param.add(yunnan_modify_appli_city_code);

			// 区县
			String yunnan_modify_appli_county_code = null;
			if (StringUtils.isNotBlank(yunnan_modify_appli_city_code) && StringUtils.isNotBlank(item.yunnan_modify_appli_county)) {
				BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.yunnan_modify_appli_county, yunnan_modify_appli_city_code);
				if (bladeRegion != null) {
					yunnan_modify_appli_county_code = bladeRegion.getCode();
				}
			}
			param.add(yunnan_modify_appli_county_code);

			param.add(getAddress(item.appli_address));
			param.add(getAddress(item.cowner_address));

			String locStr = item.loc_str;
			String locStr2 = toModifyLocStr(locStr);
			if (StringUtils.isNotBlank(locStr2)) {
				param.add(locStr2);
				param.add(LocStr.ALL.get(locStr2));
			} else {
				param.add(null);
				param.add(null);
			}

			if ("3".equals(item.patent_type)) {// 外观专利
				param.add(locStr2);// modify_ipc_type
				param.add(null);// modify_ipc_sub_type
				param.add(LocStr.ALL.get(locStr2));// modify_ipc_type_name
				param.add(null);// modify_ipc_sub_type_name
			} else if (StringUtils.isNotBlank(item.ipc_main) && item.ipc_main.trim().length() >= 3) {
				// 非外观专利
				String ipcType = item.ipc_main.trim().substring(0, 1);
				String ipcSubType = item.ipc_main.trim().substring(0, 3);

				String ipcTypeName = null;
				String ipcSubTypeName = null;

				IpcType ipcTypeEnum = IpcType.valueOf(ipcType);
				if (ipcTypeEnum != null) {
					ipcTypeName = ipcTypeEnum.getTypeName();
				}

				DIpcType dIpcType = dIpcTypeService.getDIpcTypeByCode(ipcSubType);
				if (dIpcType != null) {
					ipcSubTypeName = dIpcType.getName();
				}

				param.add(ipcType);
				param.add(ipcSubType);
				param.add(ipcTypeName);
				param.add(ipcSubTypeName);

			} else {
				param.add(null);
				param.add(null);
				param.add(null);
				param.add(null);

			}
			param.add(item.appli_type);

			String year = null;
			String month = null;
			if (StringUtils.isNotBlank(item.pub_date) && item.pub_date.trim().length() >= 6) {
				year = item.pub_date.trim().substring(0, 4);
				month = item.pub_date.trim().substring(4, 6);
				param.add(year);
				param.add(month);
			} else {
				param.add(null);
				param.add(null);
			}
			param.add(item.status);
			if (StringUtils.isNotBlank(year) && StringUtils.isNotBlank(month)) {
				param.add(year + "-" + month);
			} else {
				param.add(new SimpleDateFormat("yyyyMM").format(new Date()));
			}

//			ALTER TABLE d_patent_pub_info add modify_ipc_type varchar ( 10 );
//			ALTER TABLE d_patent_pub_info add modify_ipc_sub_type varchar ( 10 );
//			ALTER TABLE d_patent_pub_info add modify_ipc_type_name varchar ( 500 );
//			ALTER TABLE d_patent_pub_info add modify_ipc_sub_type_name varchar ( 500 );

			String cowner_province_code = null;
			String cowner_city_code = null;
			String cowner_county_code = null;
			String modify_cowner_province_code = null;
			String modify_cowner_city_code = null;
			String modify_cowner_county_code = null;

//			20230616
			if (StringUtils.isNotBlank(item.cowner_province)) {
				BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.cowner_province);
				if (bladeRegion != null) {
					cowner_province_code = bladeRegion.getCode();
				}
			}
			if (StringUtils.isNotBlank(item.cowner_city) && StringUtils.isNotBlank(cowner_province_code)) {
				BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.cowner_city, cowner_province_code);
				if (bladeRegion != null) {
					cowner_city_code = bladeRegion.getCode();
				}
			}
			if (StringUtils.isNotBlank(item.cowner_county) && StringUtils.isNotBlank(cowner_city_code)) {
				BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.cowner_county, cowner_city_code);
				if (bladeRegion != null) {
					cowner_county_code = bladeRegion.getCode();
				}
			}

			if (StringUtils.isNotBlank(item.modify_cowner_province)) {
				BladeRegion bladeRegion = bladeRegionService.selectByProvinceName(item.modify_cowner_province);
				if (bladeRegion != null) {
					modify_cowner_province_code = bladeRegion.getCode();
				}
			}
			if (StringUtils.isNotBlank(item.modify_cowner_city) && StringUtils.isNotBlank(modify_cowner_province_code)) {
				BladeRegion bladeRegion = bladeRegionService.selectByCityName(item.modify_cowner_city, modify_cowner_province_code);
				if (bladeRegion != null) {
					modify_cowner_city_code = bladeRegion.getCode();
				}
			}
			if (StringUtils.isNotBlank(item.modify_cowner_county) && StringUtils.isNotBlank(modify_cowner_city_code)) {
				BladeRegion bladeRegion = bladeRegionService.selectByAreaName(item.modify_cowner_county, modify_cowner_city_code);
				if (bladeRegion != null) {
					modify_cowner_county_code = bladeRegion.getCode();
				}
			}

			param.add(item.cowner_province);
			param.add(item.cowner_city);
			param.add(item.cowner_county);
			param.add(item.modify_cowner_province);
			param.add(item.modify_cowner_city);
			param.add(item.modify_cowner_county);
			param.add(cowner_province_code);
			param.add(cowner_city_code);
			param.add(cowner_county_code);
			param.add(modify_cowner_province_code);
			param.add(modify_cowner_city_code);
			param.add(modify_cowner_county_code);

//			+"cowner_province,cowner_city,cowner_county,modify_cowner_province,modify_cowner_city,modify_cowner_county,"
//			+ "cowner_province_code,cowner_city_code,cowner_county_code,"
//			+ "modify_cowner_province_code,modify_cowner_city_code,modify_cowner_county_code"
//			
			return sql;
		}

		public static void main(String[] args) {
			String str = "202212";
			String year = str.substring(0, 4);
			String month = str.substring(4, 6);
			System.out.println("year:" + year + ",month:" + month);
		}

		public static String toModifyLocStr(String locStr) {
			if (StringUtils.isBlank(locStr)) {
				return null;
			}
			String locStr2 = locStr.trim();
			if (locStr.contains(";")) {
//	    		14-03;14-04
				locStr2 = locStr2.substring(0, locStr2.indexOf(";"));
			}
			if (locStr2.contains("-")) {
				locStr2 = locStr2.replace("-", "");
			}
			String locStr3 = null;
			if (locStr2.length() <= 3) {
				locStr3 = locStr2.substring(0, 1);
			} else {
				locStr3 = locStr2.substring(0, 2);
			}

			return Integer.parseInt(locStr3) + "";
		}

		private static String getAddress(String addrSrc) {
			if (StringUtils.isBlank(addrSrc)) {
				return null;
			}
			if (addrSrc.contains("云南省") && !addrSrc.startsWith("云南省")) {
				return addrSrc.substring(addrSrc.indexOf("云南省"));
			}
			return null;
		}

//		public Integer hv_seis_dept;
//		public Integer hv_have_overseas_dept;
//		public Integer hv_keep10_dept;
//		public Integer hv_high_pledge_dept;
//		public Integer hv_technology_award_dept;

//		public static DPatentPubInfo toDPatentPubInfo(ResItem item) {
//			DPatentPubInfo d = new DPatentPubInfo();
//
////			public String _id;
//			d.setPubId(item._id);
////			public String pre_counts;
//			d.setPreCounts(item.pre_counts);
////			public String high_value;
//			d.setHighValue(item.high_value);
////			public String appli_province;
//			d.setAppliProvince(item.appli_province);
////			public String lic_counts;
//			d.setLicCounts(item.lic_counts);
////			public String title;
//			d.setTitle(item.title);
////			public String ipc_main;
//			d.setIpcMain(item.ipc_main);
////			public String appli_city;
//			d.setAppliCity(item.appli_city);
////			public String ple_counts;
//			d.setPleCounts(item.ple_counts);
////			public String appli;
//			d.setAppli(item.appli);
////			public String app_date;
//			d.setAppDate(item.app_date);
////			public String app_tran_counts;
//			d.setAppTranCounts(item.app_tran_counts);
////			public String loc_str;
//			d.setLocStr(item.loc_str);
////			public String modify_appli_city;
//			d.setModifyAppliCity(item.modify_appli_city);
////			public String curt_counts;
//			d.setCurtCounts(item.curt_counts);
////			public String national_eco_str;
//			d.setNationalEcoStr(item.national_eco_str);
////			public String agency_name;
//			d.setAgencyName(item.agency_name);
////			public String modify_appli_province;
//			d.setModifyAppliProvince(item.modify_appli_province);
////			public String appli_county;
//			d.setAppliCounty(item.appli_county);
////			public String pub_number;
//			d.setPubNumber(item.pub_number);
////			public String appli_address;
//			d.setAppliAddress(item.appli_address);
////			public String pat_tran_counts;
//			d.setPatTranCounts(item.pat_tran_counts);
////			public String app_number;
//			d.setAppNumber(item.app_number);
////			public String modify_appli_county;
//			d.setModifyAppliCounty(item.modify_appli_county);
////			public String patent_type;
//			d.setPatentType(item.patent_type);
////			public String appli_type;
//			d.setAppliType(item.appli_type);
////			public String status;
//			d.setStatus(item.status);
//			return d;
//		}

	}

}
