1.  private static final String SPARK_DMS_SQL =
    2.             "(SELECT " +
    3.                     "cm.id_car as idCar," +
    4.                     "cm.id_customer as idCustomer," +
    5.                     "c.brand_id as brandId," +
    6.                     "c.factory_id as factoryId," +
    7.                     "c.series_id as seriesId," +
    8.                     "c.year_id as yearId," +
    9.                     "c.main_id as mainId," +
    10.                     "m.NAME as name," +
    11.                     "m.sex as sex," +
    12.                     "m.id_number as idNumber," +
    13.                     "m.customer_source as customerSource," +
    14.                     "m.customer_type as customerType," +
    15.                     "m.company_name as companyName," +
    16.                     "m.cell_phone as cellPhone," +
    17.                     "m.birthday as birthday," +
    18.                     "m.province as province," +
    19.                     "m.city as city," +
    20.                     "m.area as area," +
    21.                     "m.credentials_type as credentialsType," +
    22.                     "c.car_no as carNo," +
    23.                     "c.car_prefix as carPrefix," +
    24.                     "c.vin as vin," +
    25.                     "DATE_FORMAT(c.card_date,'%Y-%m-%d') as cardDate," +
    26.                     "DATE_FORMAT(c.register_date,'%Y-%m-%d') as registerDate," +
    27.                     "c.factory_year as factoryYear," +
    28.                     "c.next_factory_year as nextFactoryYear," +
    29.                     "c.car_model as carModel," +
    30.                     "c.car_model_short as carModelShort," +
    31.                     "c.car_color as carColor," +
    32.                     "c.insurance_expiry_date as insuranceExpiryDate," +
    33.                     "c.insurance_expiry_date_tci as insuranceExpiryDateTci," +
    34.                     "c.insurancepolicy_no as insurancepolicyNo," +
    35.                     "c.insurancepolicy_no_tci as insurancepolicyNoTci," +
    36.                     "c.effect_insurance as effectInsurance," +
    37.                     "c.annual_date as annualDate," +
    38.                     "c.engine_number as engineNumber," +
    39.                     "c.car_brand_name as carBrandName," +
    40.                     "c.car_series_mame as carSeriesName," +
    41.                     "c.contact_name as contactName," +
    42.                     "c.contact_cellphone as contactCellphone," +
    43.                     "c.memo as memo," +
    44.                     "c.contact_credentials_type as contactCredentialsType," +
    45.                     "c.contact_credentials_number as contactCredentialsNumber," +
    46.                     "c.contact_detail_address as contactDetailAddress," +
    47.                     "c.contact_sex as contactSex," +
    48.                     "c.id_employee as idEmployee," +
    49.                     "c.id_own_org as idOwnOrg," +
    50.                     "c.creationtime as creationtime," +
    51.                     "c.creator as creator," +
    52.                     "cm.modifier as modifier," +
    53.                     "cm.modifiedtime as modifiedtime," +
    54.                     "m.points as points," +
    55.                     "m.level as level," +
    56.                     "m.detail_address as detailAddress," +
    57.                     "m.member_card_no as memberCardNo," +
    58.                     "c.car_type as carType," +
    59.                     "c.car_category_id as carCategoryId," +
    60.                     "c.last_enter_mileage as lastEnterMileage," +
    61.                     "date_format(c.last_enter_date,'%Y-%m-%d') as lastEnterDate," +
    62.                     "IFNULL(date_format(c.info_complete_date,'%Y-%m-%d'),date_format(c.creationtime,'%Y-%m-%d')) as infoCompleteDate," +
    63.                     "cs.customer_source as customerSourceName," +
    64.                     "tcc.name as customerLevel," +
    65.                     "cc.name as carCategoryName," +
    66.                     "tib.name as insuranceCompany," +
    67.                     "tic.pk_id as idInsuranceCompany," +
    68.                     "tic.contacts as contacts," +
    69.                     "tic.contact_telephone as contactTelephone," +
    70.                     "tib.id as idInsuranceBase," +
    71.                     "CONCAT(IFNULL(car_prefix, ''), IFNULL(car_no, '')) as showCarNo," +
    72.                     "cm.id_customer as fromIdCustomer," +
    73.                     "c.product_date as productDate," +
    74.                     "c.car_source_id as carSourceId," +
    75.                     "c.car_source_name as carSourceName," +
    76.                     "c.promote_employee_id as carPromoteEmployeeId," +
    77.                     "c.promote_employee_name as carPromoteEmployeeName," +
    78.                     "m.promote_employee_id as customerPromoteEmployeeId," +
    79.                     "m.promote_employee_name as customerPromoteEmployeeName," +
    80.                     "c.id_own_group as idOwnGroup," +
    81.                     "c.car_exclusive_org_name as carExclusiveOrgName," +
    82.                     "c.car_exclusive_org_id as carExclusiveOrgId " +
    83.                     "from tm_car c " +
    84.                     "STRAIGHT_JOIN tm_customer_car cm ON cm.id_car = c.pk_id " +
    85.                     "AND c.is_del = 0 " +
    86.                     "STRAIGHT_JOIN tm_customer m ON cm.id_customer = m.pk_id " +
    87.                     "AND m.is_del = 0 " +
    88.                     "LEFT JOIN tm_customer_source cs ON cs.pk_id = m.customer_source " +
    89.                     "LEFT JOIN member_level tcc ON m.LEVEL = tcc.id " +
    90.                     "LEFT JOIN tb_car_category cc on cc.id = c.car_category_id " +
    91.                     "LEFT JOIN tm_insurance_base tib ON tib.id = c.id_insurance_base " +
    92.                     "LEFT JOIN tm_insurance_company tic ON tic.id_insurance_base = tib.id) dms ";
    94.     private static final String SPARK_MEMBER_SQL = "(select cso.customer_id as idCustomer, cso.id_own_org as idOwnOrg from customer_service_org cso) member";
    97.     /**
    98.      * 通过spark查询
    99.      *
    100.      * @param so
    101.      * @return
    102.      */
    103.     public Workbook sparkQuery(CarListSo so) {
    104.         orgMapTL.set(getOrgNameAsMap());
    105.         StaffSearchGroupCacheSo staffSearchGroupCacheSo = new StaffSearchGroupCacheSo();
    106.         staffSearchGroupCacheSo.setGroupId(new BigInteger(F6Static.getGroupId()));
    107.         staffSearchGroupCacheSo.setIncludeDeletedEmployee(true);
    108.         staffListTL.set(staffCacheApi.searchStaffInGroup(staffSearchGroupCacheSo));
    110.         String memberUrl = sparkConfig.getMemberUrl();
    111.         String memberUser = sparkConfig.getMemberUser();
    112.         String memberPwd = sparkConfig.getMemberPwd();
    113.         String memberDriverClass = sparkConfig.getMemberDriverClass();
    114.         Properties member = new Properties();
    115.         member.setProperty("user", memberUser);
    116.         member.setProperty("password", memberPwd);
    117.         member.setProperty("driver", memberDriverClass);
    118.         member.setProperty("fetchsize", String.valueOf(sparkConfig.getFetchsize()));
    119.         Dataset<Row> memberDS = sparkSession.read().jdbc(memberUrl, SPARK_MEMBER_SQL, member).select("idCustomer", "idOwnOrg");
    120.         Dataset<Row> memberDsc = memberFilter(memberDS, so);
    121.         List<Row> maps = memberDsc.collectAsList();
    122.         List<BigInteger> idList = Lists.newArrayList();
    123.         MultiValueMap<String, String> orgMap = new LinkedMultiValueMap<>();
    124.         for (Row row : maps) {
    125.             idList.add(new BigInteger(row.get(0).toString()));
    126.             orgMap.add(row.get(0).toString(), row.get(1).toString());
    127.         }
    128.         serviceOrgMapTL.set(orgMap);
    130.         String dmsUrl = sparkConfig.getDmsUrl();
    131.         String dmsUser = sparkConfig.getDmsUser();
    132.         String dmsPwd = sparkConfig.getDmsPwd();
    133.         String dmsDriver = sparkConfig.getDmsDriverClass();
    134.         Properties dms = new Properties();
    135.         dms.setProperty("user", dmsUser);
    136.         dms.setProperty("password", dmsPwd);
    137.         dms.setProperty("driver", dmsDriver);
    138.         dms.setProperty("fetchsize", String.valueOf(sparkConfig.getFetchsize()));
    139.         Dataset<Row> dmsDS = sparkSession.read().jdbc(dmsUrl, SPARK_DMS_SQL, dms).select("idCustomer", "idCar", "idCustomer", "brandId", "factoryId", "seriesId", "yearId", "mainId", "name", "sex", "idNumber", "customerSource", "customerType", "companyName", "cellPhone", "birthday", "province", "city", "area", "credentialsType", "carNo", "carPrefix", "vin", "cardDate", "registerDate", "factoryYear", "nextFactoryYear", "carModel", "carModelShort", "carColor", "insuranceExpiryDate", "insuranceExpiryDateTci", "insurancepolicyNo", "insurancepolicyNoTci", "effectInsurance", "annualDate", "engineNumber", "carBrandName", "carSeriesName", "contactName", "contactCellphone", "memo", "contactCredentialsType", "contactCredentialsNumber", "contactDetailAddress", "contactSex", "idEmployee", "idOwnOrg", "creationtime", "creator", "modifier", "modifiedtime", "points", "level", "detailAddress", "memberCardNo", "carType", "carCategoryId", "lastEnterMileage", "lastEnterDate", "infoCompleteDate", "customerSourceName", "customerLevel", "carCategoryName", "insuranceCompany", "idInsuranceCompany", "contactTelephone", "idInsuranceBase", "showCarNo", "fromIdCustomer", "productDate", "carSourceId", "carSourceName", "carPromoteEmployeeId", "carPromoteEmployeeName", "customerPromoteEmployeeId", "customerPromoteEmployeeName", "idOwnGroup", "carExclusiveOrgName", "carExclusiveOrgId");
    140.         Dataset<Row> dmsDsc = dmsFilter(dmsDS, so, idList);
    142.         Dataset<Row> rss = handleSort(dmsDsc, so);
    143.         return this.getWorkbookForSparkBigDataByFilter(so, rss, CarVo.class, "车辆信息", sparkFifoMaxAmount);
    144.     }