package com.alatus.web;import com.alatus.constant.Constants;import com.alatus.model.TCustomer;import com.alatus.query.CustomerQuery;import com.alatus.result.CustomerExcel;import com.alatus.result.Result;import com.alatus.service.CustomerService;import com.alibaba.excel.EasyExcel;import com.github.pagehelper.PageInfo;import jakarta.annotation.Resource;import jakarta.servlet.http.HttpServletResponse;import org.springframework.util.ObjectUtils;import org.springframework.web.bind.annotation.*;import java.io.IOException;import java.net.URLEncoder;import java.util.Arrays;import java.util.List;@RestControllerpublic class CustomerController {@Resourceprivate CustomerService customerService;@PostMapping(value = "/api/clue/customer")public Result transferCustomer(@RequestBody CustomerQuery customerQuery, @RequestHeader(value = Constants.TOKEN_NAME)String token){customerQuery.setToken(token);Boolean convert = customerService.convertCustomer(customerQuery);return convert ? Result.OK() : Result.FAIL();}@GetMapping(value = "/api/customers")public Result CustomersByPages(@RequestParam(value = Constants.CURRENT,required = false)Integer current){if(current == null){current = 1;}PageInfo pageInfo = customerService.getCustomerByPage(current);return Result.OK(pageInfo);}@GetMapping(value = "/api/exportExcel")public void exportExcel(@RequestParam(value = Constants.IDS,required = false)String ids, HttpServletResponse response) throws IOException {List idList = null;if(!ObjectUtils.isEmpty(ids)){idList = Arrays.asList(ids.split(","));}//要想让浏览器弹出下载框，后端要设置一下响应头信息response.setContentType("application/octet-stream");response.setCharacterEncoding(Constants.UTF_8);response.setHeader("X-Frame-Options", "allow-from uri");// 解决IFrame拒绝的问题response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(Constants.CUSTOMER_FILE_NAME,Constants.UTF_8)+System.currentTimeMillis() +".xlsx");List dataList = customerService.getCustomerByExcel(idList);EasyExcel.write(response.getOutputStream(), CustomerExcel.class).sheet().doWrite(dataList);}}package com.alatus.web;import com.alatus.constant.Constants;import com.alatus.model.TCustomer;import com.alatus.query.CustomerQuery;import com.alatus.result.CustomerExcel;import com.alatus.result.Result;import com.alatus.service.CustomerService;import com.alibaba.excel.EasyExcel;import com.github.pagehelper.PageInfo;import jakarta.annotation.Resource;import jakarta.servlet.http.HttpServletResponse;import org.springframework.util.ObjectUtils;import org.springframework.web.bind.annotation.*;import java.io.IOException;import java.net.URLEncoder;import java.util.Arrays;import java.util.List;@RestControllerpublic class CustomerController {@Resourceprivate CustomerService customerService;@PostMapping(value = "/api/clue/customer")public Result transferCustomer(@RequestBody CustomerQuery customerQuery, @RequestHeader(value = Constants.TOKEN_NAME)String token){customerQuery.setToken(token);Boolean convert = customerService.convertCustomer(customerQuery);return convert ? Result.OK() : Result.FAIL();}@GetMapping(value = "/api/customers")public Result CustomersByPages(@RequestParam(value = Constants.CURRENT,required = false)Integer current){if(current == null){current = 1;}PageInfo pageInfo = customerService.getCustomerByPage(current);return Result.OK(pageInfo);}@GetMapping(value = "/api/exportExcel")public void exportExcel(@RequestParam(value = Constants.IDS,required = false)String ids, HttpServletResponse response) throws IOException {List idList = null;if(!ObjectUtils.isEmpty(ids)){idList = Arrays.asList(ids.split(","));}//要想让浏览器弹出下载框，后端要设置一下响应头信息response.setContentType("application/octet-stream");response.setCharacterEncoding(Constants.UTF_8);response.setHeader("X-Frame-Options", "allow-from uri");// 解决IFrame拒绝的问题response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(Constants.CUSTOMER_FILE_NAME,Constants.UTF_8)+System.currentTimeMillis() +".xlsx");List dataList = customerService.getCustomerByExcel(idList);EasyExcel.write(response.getOutputStream(), CustomerExcel.class).sheet().doWrite(dataList);}}
id, clue_id, product, description, next_contact_time, create_time, create_by, edit_time, edit_byselect from t_customerwhere id = #{id,jdbcType=INTEGER}selecttct.*,tc.id clueId, tc.full_name, tc.phone, tc.weixin,tu1.id ownerId, tu1.name ownerName,ta.id activityId, ta.name activityName,tdv.id appellationId, tdv.type_value appellationName,tdv2.id needLoanId, tdv2.type_value needLoanName,tdv3.id intentionStateId, tdv3.type_value intentionStateName,tdv4.id stateId, tdv4.type_value stateName,tdv5.id sourceId, tdv5.type_value sourceName,tp.id intentionProductId, tp.name intentionProductNamefrom t_customer tct left join t_clue tc on tct.clue_id = tc.idleft join t_user tu1 on tc.owner_id = tu1.idleft join t_activity ta on tc.activity_id = ta.idleft join t_dic_value tdv on tc.appellation = tdv.idleft join t_dic_value tdv2 on tc.need_loan = tdv2.idleft join t_dic_value tdv3 on tc.intention_state = tdv3.idleft join t_dic_value tdv4 on tc.state = tdv4.idleft join t_dic_value tdv5 on tc.source = tdv5.idleft join t_product tp on tct.product = tp.idselecttct.*,tc.id clueId, tc.full_name, tc.phone, tc.weixin,tc.qq,tc.email,tc.age,tc.job,tc.address,tc.year_income,tu1.id ownerId, tu1.name ownerName,ta.id activityId, ta.name activityName,tdv.id appellationId, tdv.type_value appellationName,tdv2.id needLoanId, tdv2.type_value needLoanName,tdv3.id intentionStateId, tdv3.type_value intentionStateName,tdv4.id stateId, tdv4.type_value stateName,tdv5.id sourceId, tdv5.type_value sourceName,tp.id intentionProductId, tp.name intentionProductNamefrom t_customer tct left join t_clue tc on tct.clue_id = tc.idleft join t_user tu1 on tc.owner_id = tu1.idleft join t_activity ta on tc.activity_id = ta.idleft join t_dic_value tdv on tc.appellation = tdv.idleft join t_dic_value tdv2 on tc.need_loan = tdv2.idleft join t_dic_value tdv3 on tc.intention_state = tdv3.idleft join t_dic_value tdv4 on tc.state = tdv4.idleft join t_dic_value tdv5 on tc.source = tdv5.idleft join t_product tp on tct.product = tp.id 0">and tct.id in#{id}delete from t_customerwhere id = #{id,jdbcType=INTEGER}insert into t_customer (clue_id, product, description, next_contact_time, create_time, create_by, edit_time, edit_by)values (#{clueId,jdbcType=INTEGER}, #{product,jdbcType=INTEGER}, #{description,jdbcType=VARCHAR}, #{nextContactTime,jdbcType=TIMESTAMP}, #{createTime,jdbcType=TIMESTAMP}, #{createBy,jdbcType=INTEGER}, #{editTime,jdbcType=TIMESTAMP}, #{editBy,jdbcType=INTEGER})insert into t_customerclue_id,product,description,next_contact_time,create_time,create_by,edit_time,edit_by,#{clueId,jdbcType=INTEGER},#{product,jdbcType=INTEGER},#{description,jdbcType=VARCHAR},#{nextContactTime,jdbcType=TIMESTAMP},#{createTime,jdbcType=TIMESTAMP},#{createBy,jdbcType=INTEGER},#{editTime,jdbcType=TIMESTAMP},#{editBy,jdbcType=INTEGER},update t_customerclue_id = #{clueId,jdbcType=INTEGER},product = #{product,jdbcType=INTEGER},description = #{description,jdbcType=VARCHAR},next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},create_time = #{createTime,jdbcType=TIMESTAMP},create_by = #{createBy,jdbcType=INTEGER},edit_time = #{editTime,jdbcType=TIMESTAMP},edit_by = #{editBy,jdbcType=INTEGER},where id = #{id,jdbcType=INTEGER}update t_customerset clue_id = #{clueId,jdbcType=INTEGER},product = #{product,jdbcType=INTEGER},description = #{description,jdbcType=VARCHAR},next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},create_time = #{createTime,jdbcType=TIMESTAMP},create_by = #{createBy,jdbcType=INTEGER},edit_time = #{editTime,jdbcType=TIMESTAMP},edit_by = #{editBy,jdbcType=INTEGER}where id = #{id,jdbcType=INTEGER}id, clue_id, product, description, next_contact_time, create_time, create_by, edit_time, edit_byselect from t_customerwhere id = #{id,jdbcType=INTEGER}selecttct.*,tc.id clueId, tc.full_name, tc.phone, tc.weixin,tu1.id ownerId, tu1.name ownerName,ta.id activityId, ta.name activityName,tdv.id appellationId, tdv.type_value appellationName,tdv2.id needLoanId, tdv2.type_value needLoanName,tdv3.id intentionStateId, tdv3.type_value intentionStateName,tdv4.id stateId, tdv4.type_value stateName,tdv5.id sourceId, tdv5.type_value sourceName,tp.id intentionProductId, tp.name intentionProductNamefrom t_customer tct left join t_clue tc on tct.clue_id = tc.idleft join t_user tu1 on tc.owner_id = tu1.idleft join t_activity ta on tc.activity_id = ta.idleft join t_dic_value tdv on tc.appellation = tdv.idleft join t_dic_value tdv2 on tc.need_loan = tdv2.idleft join t_dic_value tdv3 on tc.intention_state = tdv3.idleft join t_dic_value tdv4 on tc.state = tdv4.idleft join t_dic_value tdv5 on tc.source = tdv5.idleft join t_product tp on tct.product = tp.idselecttct.*,tc.id clueId, tc.full_name, tc.phone, tc.weixin,tc.qq,tc.email,tc.age,tc.job,tc.address,tc.year_income,tu1.id ownerId, tu1.name ownerName,ta.id activityId, ta.name activityName,tdv.id appellationId, tdv.type_value appellationName,tdv2.id needLoanId, tdv2.type_value needLoanName,tdv3.id intentionStateId, tdv3.type_value intentionStateName,tdv4.id stateId, tdv4.type_value stateName,tdv5.id sourceId, tdv5.type_value sourceName,tp.id intentionProductId, tp.name intentionProductNamefrom t_customer tct left join t_clue tc on tct.clue_id = tc.idleft join t_user tu1 on tc.owner_id = tu1.idleft join t_activity ta on tc.activity_id = ta.idleft join t_dic_value tdv on tc.appellation = tdv.idleft join t_dic_value tdv2 on tc.need_loan = tdv2.idleft join t_dic_value tdv3 on tc.intention_state = tdv3.idleft join t_dic_value tdv4 on tc.state = tdv4.idleft join t_dic_value tdv5 on tc.source = tdv5.idleft join t_product tp on tct.product = tp.id 0">and tct.id in#{id}delete from t_customerwhere id = #{id,jdbcType=INTEGER}insert into t_customer (clue_id, product, description, next_contact_time, create_time, create_by, edit_time, edit_by)values (#{clueId,jdbcType=INTEGER}, #{product,jdbcType=INTEGER}, #{description,jdbcType=VARCHAR}, #{nextContactTime,jdbcType=TIMESTAMP}, #{createTime,jdbcType=TIMESTAMP}, #{createBy,jdbcType=INTEGER}, #{editTime,jdbcType=TIMESTAMP}, #{editBy,jdbcType=INTEGER})insert into t_customerclue_id,product,description,next_contact_time,create_time,create_by,edit_time,edit_by,#{clueId,jdbcType=INTEGER},#{product,jdbcType=INTEGER},#{description,jdbcType=VARCHAR},#{nextContactTime,jdbcType=TIMESTAMP},#{createTime,jdbcType=TIMESTAMP},#{createBy,jdbcType=INTEGER},#{editTime,jdbcType=TIMESTAMP},#{editBy,jdbcType=INTEGER},update t_customerclue_id = #{clueId,jdbcType=INTEGER},product = #{product,jdbcType=INTEGER},description = #{description,jdbcType=VARCHAR},next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},create_time = #{createTime,jdbcType=TIMESTAMP},create_by = #{createBy,jdbcType=INTEGER},edit_time = #{editTime,jdbcType=TIMESTAMP},edit_by = #{editBy,jdbcType=INTEGER},where id = #{id,jdbcType=INTEGER}update t_customerset clue_id = #{clueId,jdbcType=INTEGER},product = #{product,jdbcType=INTEGER},description = #{description,jdbcType=VARCHAR},next_contact_time = #{nextContactTime,jdbcType=TIMESTAMP},create_time = #{createTime,jdbcType=TIMESTAMP},create_by = #{createBy,jdbcType=INTEGER},edit_time = #{editTime,jdbcType=TIMESTAMP},edit_by = #{editBy,jdbcType=INTEGER}where id = #{id,jdbcType=INTEGER}
package com.alatus.service;import com.alatus.model.TCustomer;import com.alatus.query.CustomerQuery;import com.alatus.result.CustomerExcel;import com.github.pagehelper.PageInfo;import java.util.List;public interface CustomerService {Boolean convertCustomer(CustomerQuery customerQuery);PageInfo getCustomerByPage(Integer current);List getCustomerByExcel(List idList);}package com.alatus.service;import com.alatus.model.TCustomer;import com.alatus.query.CustomerQuery;import com.alatus.result.CustomerExcel;import com.github.pagehelper.PageInfo;import java.util.List;public interface CustomerService {Boolean convertCustomer(CustomerQuery customerQuery);PageInfo getCustomerByPage(Integer current);List getCustomerByExcel(List idList);}
package com.alatus.service.impl;import com.alatus.constant.Constants;import com.alatus.manager.CustomerManager;import com.alatus.mapper.TCustomerMapper;import com.alatus.model.TCustomer;import com.alatus.query.CustomerQuery;import com.alatus.result.CustomerExcel;import com.alatus.service.CustomerService;import com.github.pagehelper.PageHelper;import com.github.pagehelper.PageInfo;import jakarta.annotation.Resource;import org.springframework.stereotype.Service;import org.springframework.util.ObjectUtils;import java.math.BigDecimal;import java.util.ArrayList;import java.util.List;@Servicepublic class CustomerServiceImpl implements CustomerService {@Resourceprivate TCustomerMapper tCustomerMapper;@Resourceprivate CustomerManager customerManager;@Overridepublic Boolean convertCustomer(CustomerQuery customerQuery) {return customerManager.transferCustomer(customerQuery);}@Overridepublic PageInfo getCustomerByPage(Integer current) {//设置PageHelper和分页情况PageHelper.startPage(current, Constants.PAGE_SIZE);//查询ArrayList list = tCustomerMapper.selectCustomerByPage();//封装分页到PageInfo中PageInfo info = new PageInfo(list);return info;}@Overridepublic List getCustomerByExcel(List idList) {List customers = tCustomerMapper.selectCustomerByExcel(idList);//把数据转为CustomerExcelList customerExcelList = new ArrayList();customers.forEach(tCustomer -> {CustomerExcel customerExcel = new CustomerExcel();//需要一个一个设置，没有办法，因为没法使用BeanUtils复制customerExcel.setOwnerName(ObjectUtils.isEmpty(tCustomer.getOwnerDO()) ? Constants.EMPTY : tCustomer.getOwnerDO().getName());customerExcel.setActivityName(ObjectUtils.isEmpty(tCustomer.getActivityDO()) ? Constants.EMPTY : tCustomer.getActivityDO().getName());customerExcel.setFullName(tCustomer.getClueDO().getFullName());customerExcel.setAppellationName(ObjectUtils.isEmpty(tCustomer.getAppellationDO()) ? Constants.EMPTY : tCustomer.getAppellationDO().getTypeValue());customerExcel.setPhone(ObjectUtils.isEmpty(tCustomer.getClueDO().getPhone()) ? Constants.EMPTY :tCustomer.getClueDO().getPhone());customerExcel.setWeixin(ObjectUtils.isEmpty(tCustomer.getClueDO().getWeixin()) ? Constants.EMPTY :tCustomer.getClueDO().getWeixin());customerExcel.setQq(ObjectUtils.isEmpty(tCustomer.getClueDO().getQq()) ? Constants.EMPTY :tCustomer.getClueDO().getQq());customerExcel.setEmail(ObjectUtils.isEmpty(tCustomer.getClueDO().getEmail()) ? Constants.EMPTY :tCustomer.getClueDO().getEmail());customerExcel.setAge(ObjectUtils.isEmpty(tCustomer.getClueDO().getAge()) ? 0 :tCustomer.getClueDO().getAge());customerExcel.setJob(ObjectUtils.isEmpty(tCustomer.getClueDO().getJob()) ? Constants.EMPTY :tCustomer.getClueDO().getJob());customerExcel.setYearIncome(ObjectUtils.isEmpty(tCustomer.getClueDO().getYearIncome()) ? new BigDecimal(0) : tCustomer.getClueDO().getYearIncome());customerExcel.setAddress(ObjectUtils.isEmpty(tCustomer.getClueDO().getAddress()) ? Constants.EMPTY : tCustomer.getClueDO().getAddress());customerExcel.setNeedLoadName(ObjectUtils.isEmpty(tCustomer.getNeedLoanDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getNeedLoanDO().getTypeValue());customerExcel.setProductName(ObjectUtils.isEmpty(tCustomer.getIntentionProductDO().getName()) ? Constants.EMPTY : tCustomer.getIntentionProductDO().getName());customerExcel.setSourceName(ObjectUtils.isEmpty(tCustomer.getSourceDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getSourceDO().getTypeValue());customerExcel.setDescription(tCustomer.getDescription());customerExcel.setNextContactTime(tCustomer.getNextContactTime());customerExcelList.add(customerExcel);});return customerExcelList;}}package com.alatus.service.impl;import com.alatus.constant.Constants;import com.alatus.manager.CustomerManager;import com.alatus.mapper.TCustomerMapper;import com.alatus.model.TCustomer;import com.alatus.query.CustomerQuery;import com.alatus.result.CustomerExcel;import com.alatus.service.CustomerService;import com.github.pagehelper.PageHelper;import com.github.pagehelper.PageInfo;import jakarta.annotation.Resource;import org.springframework.stereotype.Service;import org.springframework.util.ObjectUtils;import java.math.BigDecimal;import java.util.ArrayList;import java.util.List;@Servicepublic class CustomerServiceImpl implements CustomerService {@Resourceprivate TCustomerMapper tCustomerMapper;@Resourceprivate CustomerManager customerManager;@Overridepublic Boolean convertCustomer(CustomerQuery customerQuery) {return customerManager.transferCustomer(customerQuery);}@Overridepublic PageInfo getCustomerByPage(Integer current) {//设置PageHelper和分页情况PageHelper.startPage(current, Constants.PAGE_SIZE);//查询ArrayList list = tCustomerMapper.selectCustomerByPage();//封装分页到PageInfo中PageInfo info = new PageInfo(list);return info;}@Overridepublic List getCustomerByExcel(List idList) {List customers = tCustomerMapper.selectCustomerByExcel(idList);//把数据转为CustomerExcelList customerExcelList = new ArrayList();customers.forEach(tCustomer -> {CustomerExcel customerExcel = new CustomerExcel();//需要一个一个设置，没有办法，因为没法使用BeanUtils复制customerExcel.setOwnerName(ObjectUtils.isEmpty(tCustomer.getOwnerDO()) ? Constants.EMPTY : tCustomer.getOwnerDO().getName());customerExcel.setActivityName(ObjectUtils.isEmpty(tCustomer.getActivityDO()) ? Constants.EMPTY : tCustomer.getActivityDO().getName());customerExcel.setFullName(tCustomer.getClueDO().getFullName());customerExcel.setAppellationName(ObjectUtils.isEmpty(tCustomer.getAppellationDO()) ? Constants.EMPTY : tCustomer.getAppellationDO().getTypeValue());customerExcel.setPhone(ObjectUtils.isEmpty(tCustomer.getClueDO().getPhone()) ? Constants.EMPTY :tCustomer.getClueDO().getPhone());customerExcel.setWeixin(ObjectUtils.isEmpty(tCustomer.getClueDO().getWeixin()) ? Constants.EMPTY :tCustomer.getClueDO().getWeixin());customerExcel.setQq(ObjectUtils.isEmpty(tCustomer.getClueDO().getQq()) ? Constants.EMPTY :tCustomer.getClueDO().getQq());customerExcel.setEmail(ObjectUtils.isEmpty(tCustomer.getClueDO().getEmail()) ? Constants.EMPTY :tCustomer.getClueDO().getEmail());customerExcel.setAge(ObjectUtils.isEmpty(tCustomer.getClueDO().getAge()) ? 0 :tCustomer.getClueDO().getAge());customerExcel.setJob(ObjectUtils.isEmpty(tCustomer.getClueDO().getJob()) ? Constants.EMPTY :tCustomer.getClueDO().getJob());customerExcel.setYearIncome(ObjectUtils.isEmpty(tCustomer.getClueDO().getYearIncome()) ? new BigDecimal(0) : tCustomer.getClueDO().getYearIncome());customerExcel.setAddress(ObjectUtils.isEmpty(tCustomer.getClueDO().getAddress()) ? Constants.EMPTY : tCustomer.getClueDO().getAddress());customerExcel.setNeedLoadName(ObjectUtils.isEmpty(tCustomer.getNeedLoanDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getNeedLoanDO().getTypeValue());customerExcel.setProductName(ObjectUtils.isEmpty(tCustomer.getIntentionProductDO().getName()) ? Constants.EMPTY : tCustomer.getIntentionProductDO().getName());customerExcel.setSourceName(ObjectUtils.isEmpty(tCustomer.getSourceDO().getTypeValue()) ? Constants.EMPTY : tCustomer.getSourceDO().getTypeValue());customerExcel.setDescription(tCustomer.getDescription());customerExcel.setNextContactTime(tCustomer.getNextContactTime());customerExcelList.add(customerExcel);});return customerExcelList;}}
package com.alatus.mapper;import com.alatus.model.TCustomer;import java.util.ArrayList;import java.util.List;public interface TCustomerMapper {int deleteByPrimaryKey(Integer id);int insert(TCustomer record);int insertSelective(TCustomer record);TCustomer selectByPrimaryKey(Integer id);int updateByPrimaryKeySelective(TCustomer record);int updateByPrimaryKey(TCustomer record);ArrayList selectCustomerByPage();List selectCustomerByExcel(List idList);}package com.alatus.mapper;import com.alatus.model.TCustomer;import java.util.ArrayList;import java.util.List;public interface TCustomerMapper {int deleteByPrimaryKey(Integer id);int insert(TCustomer record);int insertSelective(TCustomer record);TCustomer selectByPrimaryKey(Integer id);int updateByPrimaryKeySelective(TCustomer record);int updateByPrimaryKey(TCustomer record);ArrayList selectCustomerByPage();List selectCustomerByExcel(List idList);}