import&export

Controller:

@RequestMapping(value = "/import")
    public void importBlackWhiteList(MultipartFile file, HttpServletRequest request, HttpServletResponse response) {
        String tenantId = CurrentTenant.getInstance(request).getTenant().getId();
        OutputStream os = null;
        InputStream is = null;
        try {
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            is = file.getInputStream();
            os = response.getOutputStream();
            iPAssignService.importIPAssigns(new TenantId(tenantId), is, os);
        } catch (Exception e) {
            _logger.error(e.getMessage(), e);
        } finally {
            try {
                if (os != null) {
                    os.close();
                }
                if (is != null) {
                    is.close();
                }
            } catch (IOException e) {
            }
        }
    }

    @RequestMapping(value = "/export")
    public void exportAuthLogs(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String tenantId = CurrentTenant.getInstance(request).getTenant().getId();
        OutputStream os = null;
        String fileName = "ip_assignment_template.xlsx";
        File excelFile = null;
        try {
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            os = response.getOutputStream();
            String fileAbsolutePath = request.getSession().getServletContext().getRealPath("/") + "/page/resources/" + fileName;
            excelFile = iPAssignService.exportIPAssigns(new TenantId(tenantId), fileAbsolutePath, os);
        } catch (Exception e) {
            _logger.error(e.getMessage(), e);
        } finally {
            if (os != null) {
                os.close();
            }
            if (excelFile != null) {
                FileUtils.forceDelete(excelFile);
            }
        }
    }

Service:

package com.ndkey.am.access.ip;

import com.ndkey.am.access.AccessSession;
import com.ndkey.am.access.device.AccessServer;
import com.ndkey.am.access.device.AccessServerId;
import com.ndkey.am.access.device.AccessServerRepository;
import com.ndkey.am.access.device.Ssid;
import com.ndkey.am.access.device.SsidId;
import com.ndkey.am.access.device.SsidRepository;
import com.ndkey.am.access.identity.IdentityStore;
import com.ndkey.am.access.identity.IdentityStoreRepository;
import com.ndkey.am.access.identity.User;
import com.ndkey.am.access.identity.UserInspectionService;
import com.ndkey.am.access.identity.UserRepository;
import com.ndkey.am.tenant.TenantId;
import com.ndkey.mq.client.util.StringUtils;
import com.ndkey.net.MacAddress;
import com.ndkey.utils.RandomString;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.UnknownHostException;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.transaction.annotation.Transactional;

public class IPAssignService {

    private final static int LOGIN_NAME_INDEX = 0;
    private final static int IDENTITY_STORE_NAME_INDEX = 1;
    private final static int TERMINAL_MAC_INDEX = 2;
    private final static int NAS_INDEX = 3;
    private final static int IP_INDEX = 4;
    private final static int NET_MASK_INDEX = 5;
    private final static int GATE_WAY_INDEX = 6;
    private final static int RESULT_INDEX = 7;
    private final static int FAILURE_REASON_INDEX = 8;

    private final static int TERMINAL_MAC_POWER = 4;
    private final static int ACCESS_SERVER_POWER = 1;
    private final static int SSID_POWER = 2;

    private IPAssignPolicyRepository iPAssignPolicyRepository;
    private UserRepository userRepository;
    private IdentityStoreRepository identityStoreRepository;
    private SsidRepository ssidRepository;
    private AccessServerRepository accessServerRepository;
    private UserInspectionService userInspectionService;

    public IPAssignment assignIP(AccessSession accessSession) throws UnknownHostException {
        List<IPAssignPolicy> list = iPAssignPolicyRepository.list(accessSession.getUser().getId());
        if (list.isEmpty()) {
            return null;
        }
        Collections.sort(list, new Comparator<IPAssignPolicy>() {
            @Override
            public int compare(IPAssignPolicy o1, IPAssignPolicy o2) {
                return calcIPAssignPower(o2) - calcIPAssignPower(o1);  //降序
            }
        });
        for (IPAssignPolicy item : list) {
            if (matchsMacAddress(accessSession.getTerminalMac(), item.getTerminalMac())
                    && matchsNAS(accessSession.getAccessServer(), item.getAccessServerId())
                    && matchsSsid(accessSession.getSsid(), item.getSsidId())) {
                return new IPAssignment(item.getIp(), item.getNetmask(), item.getGateway());
            }
        }
        return null;
    }

    private boolean matchsMacAddress(MacAddress terminalEnd, MacAddress deliverMac) {
        if (deliverMac == null) {
            return true;
        }
        if (terminalEnd != null) {
            return deliverMac.equals(terminalEnd);
        } else {
            return false;
        }
    }

    private boolean matchsNAS(AccessServer terminalEnd, AccessServerId accessServerId) {
        if (accessServerId == null) {
            return true;
        }
        if (terminalEnd != null) {
            return accessServerId.getId().equals(terminalEnd.getId().getId());
        } else {
            return false;
        }
    }

    private boolean matchsSsid(Ssid terminalEnd, SsidId ssidId) {
        if (ssidId == null) {
            return true;
        }
        if (terminalEnd != null) {
            return ssidId.getId().equals(terminalEnd.getId().getId());
        } else {
            return false;
        }
    }

    private int calcIPAssignPower(IPAssignPolicy ipAssign) {
        int power = 0;
        power += ipAssign.getTerminalMac() == null ? 0 : IPAssignService.TERMINAL_MAC_POWER;
        power += ipAssign.getAccessServerId() == null ? 0 : IPAssignService.ACCESS_SERVER_POWER;
        power += ipAssign.getSsidId() == null ? 0 : IPAssignService.SSID_POWER;
        return power;
    }

    @Transactional(timeout = 60 * 60 * 8)
    public void importIPAssigns(TenantId tenantId, InputStream is, OutputStream os) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook(is);
        XSSFSheet sheet = wb.getSheetAt(0);
        int maxRowNum = sheet.getLastRowNum();
        XSSFRow xSSFRow = null;
        for (int index = 3; index <= maxRowNum; index++) {
            xSSFRow = sheet.getRow(index);
            if (xSSFRow != null) {
                try {
                    String identityStoreName = getCellValue(xSSFRow.getCell(IDENTITY_STORE_NAME_INDEX));
                    IdentityStore identityStore = identityStoreRepository.getByName(tenantId, identityStoreName);
                    if (StringUtils.isBlank(identityStoreName) || identityStore == null) {
                        markFailureRow(xSSFRow, "identity store not exist");
                        continue;
                    }
                    String loginName = getCellValue(xSSFRow.getCell(LOGIN_NAME_INDEX));
                    User user = userRepository.getByLoginName(identityStore.getId(), loginName);
                    if (StringUtils.isBlank(loginName) || user == null) {
                        markFailureRow(xSSFRow, "user not exist");
                        continue;
                    }
                    String macAddress = getCellValue(xSSFRow.getCell(TERMINAL_MAC_INDEX));
                    MacAddress terminalMac = StringUtils.isBlank(macAddress) ? null : MacAddress.parseMacAddress(macAddress);
                    String nasName = getCellValue(xSSFRow.getCell(NAS_INDEX));
                    AccessServer nas = StringUtils.isBlank(nasName) ? null : accessServerRepository.getByName(tenantId, nasName);
                    AccessServerId accessServerId = nas == null ? null : nas.getId();
                    String ipAddress = getCellValue(xSSFRow.getCell(IP_INDEX));
                    String netmaskAddress = getCellValue(xSSFRow.getCell(NET_MASK_INDEX));
                    String gatewayAddress = getCellValue(xSSFRow.getCell(GATE_WAY_INDEX));
                    if (StringUtils.isBlank(ipAddress) || StringUtils.isBlank(netmaskAddress) || StringUtils.isBlank(gatewayAddress)) {
                        markFailureRow(xSSFRow, "ip netmask gateway is null");
                        continue;
                    }
                    IPAssignPolicy ipAssignPolicy = new IPAssignPolicy(tenantId, user.getId(), terminalMac, accessServerId, null, ipAddress, netmaskAddress, gatewayAddress);
                    iPAssignPolicyRepository.save(ipAssignPolicy);
                    // String mobileOperator = xSSFRow.getCell(USER_MOBILE_OPERATOR_INDEX) == null ? "" : getCellValue(xSSFRow.getCell(USER_MOBILE_OPERATOR_INDEX));
                    // String mobileLocation = xSSFRow.getCell(USER_MOBILE_LOCATION_INDEX) == null ? "" : getCellValue(xSSFRow.getCell(USER_MOBILE_LOCATION_INDEX));
                    this.markSuccessRow(xSSFRow);
                } catch (Exception e) {
                    markFailureRow(xSSFRow, e.getMessage());
                }
            }
        }
        wb.write(os);
        wb = null;
    }

    public File exportIPAssigns(TenantId tenantId, String templatePath, OutputStream os) throws IOException, InvalidFormatException {
        String tempPath = FileUtils.getTempDirectoryPath();
        File excelFile = new File(tempPath + "\\" + generateFileName() + ".xlsx");
        File templateFile = new File(templatePath);
        FileUtils.copyFile(templateFile, excelFile);
        XSSFWorkbook wb;
        try (OPCPackage opk = OPCPackage.open(excelFile)) {
            wb = new XSSFWorkbook(opk);
            XSSFSheet sheet = wb.getSheetAt(0);
            XSSFRow row = null;
            List<IPAssignPolicy> list = iPAssignPolicyRepository.list(tenantId, 0, Integer.MAX_VALUE);
            int i = 3;
            for (IPAssignPolicy item : list) {
                row = sheet.createRow(i);
                User user = userRepository.get(item.getUserId());
                IdentityStore identityStore = userInspectionService.inspectForIdentityStore(user);
                AccessServer accessServer = item.getAccessServerId() == null ? null : accessServerRepository.get(item.getAccessServerId());
                row.createCell(LOGIN_NAME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(user.getLoginName());
                row.createCell(IDENTITY_STORE_NAME_INDEX, Cell.CELL_TYPE_STRING).setCellValue(identityStore == null ? "" : identityStore.getName());
                row.createCell(TERMINAL_MAC_INDEX, Cell.CELL_TYPE_STRING).setCellValue(item.getTerminalMac() == null ? "" : item.getTerminalMac().getAddress());
                row.createCell(NAS_INDEX, Cell.CELL_TYPE_STRING).setCellValue(accessServer == null ? "" : accessServer.getName());
                row.createCell(IP_INDEX, Cell.CELL_TYPE_STRING).setCellValue(StringUtils.isBlank(item.getIp())?"":item.getIp());
                row.createCell(NET_MASK_INDEX, Cell.CELL_TYPE_STRING).setCellValue(StringUtils.isBlank(item.getNetmask())?"":item.getNetmask());
                row.createCell(GATE_WAY_INDEX, Cell.CELL_TYPE_STRING).setCellValue(StringUtils.isBlank(item.getGateway())?"":item.getGateway());
                i++;
            }
            wb.write(os);
        }
        wb = null;
        // 3. return file
        return excelFile;
    }

    public String getCellValue(Cell cell) {
        cell.setCellType(Cell.CELL_TYPE_STRING);
        return cell.getStringCellValue();
    }

    private void markFailureRow(XSSFRow row, String message) {
        if (row.getCell(FAILURE_REASON_INDEX) == null) {
            row.createCell(FAILURE_REASON_INDEX).setCellValue(message);
        } else {
            row.getCell(FAILURE_REASON_INDEX).setCellValue(message);
        }
        if (row.getCell(RESULT_INDEX) == null) {
            row.createCell(RESULT_INDEX).setCellValue("失败");
        } else {
            row.getCell(RESULT_INDEX).setCellValue("失败");
        }
    }

    private void markSuccessRow(XSSFRow row) {
        if (row.getCell(RESULT_INDEX) == null) {
            row.createCell(RESULT_INDEX).setCellValue("成功");
        } else {
            row.getCell(RESULT_INDEX).setCellValue("成功");
        }
    }

    private String generateFileName() {
        return RandomString.getRandomString("qazwsxedcrfvtgbyhnujmikl1234567890", 6);
    }

    public void setiPAssignPolicyRepository(IPAssignPolicyRepository iPAssignPolicyRepository) {
        this.iPAssignPolicyRepository = iPAssignPolicyRepository;
    }

    public void setUserRepository(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public void setIdentityStoreRepository(IdentityStoreRepository identityStoreRepository) {
        this.identityStoreRepository = identityStoreRepository;
    }

    public void setSsidRepository(SsidRepository ssidRepository) {
        this.ssidRepository = ssidRepository;
    }

    public void setAccessServerRepository(AccessServerRepository accessServerRepository) {
        this.accessServerRepository = accessServerRepository;
    }

    public void setUserInspectionService(UserInspectionService userInspectionService) {
        this.userInspectionService = userInspectionService;
    }

}