INsert area and user

package com.progoti.surecash.merchant.KBFeatures

import com.progoti.surecash.District
import com.progoti.surecash.scPartners.ScAreaForPartnerUsers
import com.progoti.surecash.scPartners.ScAreaTypeForPartnerUsers
import com.progoti.surecash.scPartners.ScPartnerUser
import groovy.sql.Sql
import org.apache.commons.lang.StringUtils
import org.apache.poi.ss.usermodel.DataFormatter
import org.apache.poi.xssf.usermodel.XSSFRow

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class ExcelReaderController {
    def dataSource

    def index() {}

    def readExcel() {
        DataFormatter df = new DataFormatter()
        String division;
        String region;

        String excelFilePath = "/home/insan/Documents/SURECASH_PROJECTS/surecash-web/kb.xlsx"
        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));

        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet firstSheet = workbook.getSheetAt(3);

        Integer rowCount = firstSheet.getPhysicalNumberOfRows();
        for (int row = 1; row < rowCount; row++) {
            XSSFRow dataRow = firstSheet.getRow(row)
            DtoClass dtoClass

            for (int j = 0; j < 13; j++) {
                dataRow.getCell(0).setCellType(Cell.CELL_TYPE_STRING)
            }

            dtoClass = new DtoClass()
            dtoClass.divisoinFromCell = dataRow.getCell(1).getRichStringCellValue().getString()
            if (StringUtils.isEmpty(dtoClass.divisoinFromCell)) {
                dtoClass.divisoinFromCell = division
            } else {
                division = dtoClass.divisoinFromCell
            }

            dtoClass.regionFromCell = dataRow.getCell(2).getRichStringCellValue().getString()
            if (StringUtils.isEmpty(dtoClass.regionFromCell)) {
                dtoClass.regionFromCell = region
            } else {
                region = dtoClass.regionFromCell
            }

            dtoClass.branchName = dataRow.getCell(3).getRichStringCellValue().getString()
            dtoClass.branchCode = df.formatCellValue(dataRow.getCell(4))
            dtoClass.district = dataRow.getCell(5).getRichStringCellValue().getString()
            dtoClass.address = dataRow.getCell(6).getRichStringCellValue().getString()
            dtoClass.officerName = dataRow.getCell(7).getRichStringCellValue().getString()
            dtoClass.designation = dataRow.getCell(8).getRichStringCellValue().getString()
            dtoClass.telephone = df.formatCellValue(dataRow.getCell(9))
            dtoClass.corporateMobile = df.formatCellValue(dataRow.getCell(10))
            dtoClass.dataMobile = df.formatCellValue(dataRow.getCell(11))
            dtoClass.email = dataRow.getCell(12).getRichStringCellValue().getString()

            dtoClass.cleanOwn()
            ScAreaForPartnerUsers insertedArea = insertArea(dtoClass)
            if (insertedArea != null) {
                insertUser(dtoClass, insertedArea)
            }

        }
        inputStream.close();
    }

    ScAreaForPartnerUsers insertArea(DtoClass dtoClass) {
        ScAreaForPartnerUsers area = new ScAreaForPartnerUsers()
        if (dtoClass.branchName.trim().equalsIgnoreCase("Regional Office")) {
            if (ScAreaForPartnerUsers.findByAreaNameAndAreaType(dtoClass.officerName, ScAreaTypeForPartnerUsers.findByTypeName("Region"))) {
                return ScAreaForPartnerUsers.findByAreaNameAndAreaType(dtoClass.officerName, ScAreaTypeForPartnerUsers.findByTypeName("Region"))
            }

            area.areaType = ScAreaTypeForPartnerUsers.findByTypeName('Region')
            area.parent = ScAreaForPartnerUsers.findByAreaNameAndAreaType(dtoClass.divisoinFromCell.trim(), ScAreaTypeForPartnerUsers.findByTypeName("Division"))

            area.areaName = dtoClass.regionFromCell.trim()
            area.description = area.areaName
        } else {
            if (ScAreaForPartnerUsers.findByAreaNameAndAreaType(dtoClass.branchName, ScAreaTypeForPartnerUsers.findByTypeName("Branch"))) {
                return ScAreaForPartnerUsers.findByAreaNameAndAreaType(dtoClass.branchName, ScAreaTypeForPartnerUsers.findByTypeName("Branch"))
            }

            area.areaType = ScAreaTypeForPartnerUsers.findByTypeName('Branch')
            area.areaCode = dtoClass.branchCode.trim()
            Sql sql = Sql.newInstance(dataSource)
            def r = sql.firstRow("""SELECT * FROM district WHERE name sounds LIKE '""" + dtoClass.district + """'""")
            if (r != null) {
                area.district = District.findById(r.dId)
            }
            area.parent = ScAreaForPartnerUsers.findByAreaNameAndAreaType(dtoClass.regionFromCell.trim(), ScAreaTypeForPartnerUsers.findByTypeName("Region"))

            area.areaName = dtoClass.branchName.trim()
            area.description = area.areaName
        }

        area.createDate = area.updateDate = new Date()
        if (area.save(flush: true, failOnError: true)) {
            return area
        } else {
            return null
        }
    }

    void insertUser(DtoClass dtoClass, ScAreaForPartnerUsers areaForPartnerUsers) {
        ScPartnerUser user = new ScPartnerUser()
        user.area = areaForPartnerUsers
        if (areaForPartnerUsers.areaType.id == ScAreaTypeForPartnerUsers.findByTypeName('Region').id) {
            user.loginName = "kbro_" + areaForPartnerUsers.areaName.replace("[^A-Za-z0-9]", "").toLowerCase()
        } else if (areaForPartnerUsers.areaType.id == ScAreaTypeForPartnerUsers.findByTypeName('Branch').id) {
            user.loginName = "kbbo_" + areaForPartnerUsers.areaName.replace("[^A-Za-z0-9]", "").toLowerCase()
        }
        def userListByLoginName = ScPartnerUser.findAllByLoginName(user.loginName)
        if (userListByLoginName != null && userListByLoginName.size() > 0) {
            user.loginName += "_" + System.currentTimeMillis() %1000
        }

        user.scPartnerUserType = areaForPartnerUsers.areaType.partnerUserType
        user.password = "PWpr5qM7JDw="
        user.name = dtoClass.officerName
        user.officeAddress = dtoClass.address
        user.designation = dtoClass.designation
        user.telephone = dtoClass.telephone
        user.mobileNoCorporate = dtoClass.corporateMobile
        user.mobileNo = dtoClass.dataMobile
        user.eMail = dtoClass.email

        user.createDate = user.latestUpdateDate = new Date()

        user.save(flush: true, failOnError: true)

        System.currentTimeMillis()
    }
}

class DtoClass {
    String divisoinFromCell;
    String regionFromCell
    String branchName
    String branchCode
    String district
    String address
    String officerName
    String designation
    String dataMobile
    String corporateMobile
    String telephone
    String email

    void cleanOwn() {
        divisoinFromCell = divisoinFromCell?.replaceAll("\\P{Print}", "")?.trim()
        regionFromCell = regionFromCell?.replaceAll("\\P{Print}", "")?.trim()
        branchName = branchName?.replaceAll("\\P{Print}", "")?.trim()
        branchCode = branchCode?.replaceAll("\\P{Print}", "")?.trim()
        district = district?.replaceAll("\\P{Print}", "")?.trim()
        address = address?.replaceAll("\\P{Print}", "")?.trim()
        officerName = officerName?.replaceAll("\\P{Print}", "")?.trim()
        designation = designation?.replaceAll("\\P{Print}", "")?.trim()
        dataMobile = dataMobile?.replaceAll("\\P{Print}", "")?.trim()
        corporateMobile = corporateMobile?.replaceAll("\\P{Print}", "")?.trim()
        telephone = telephone?.replaceAll("\\P{Print}", "")?.trim()
        email = email?.replaceAll("\\P{Print}", "")?.trim()

        if (StringUtils.isEmpty(divisoinFromCell)) {
            divisoinFromCell = null
        }
        if (StringUtils.isEmpty(regionFromCell)) {
            regionFromCell = null
        }
        if (StringUtils.isEmpty(branchName)) {
            branchName = null
        }
        if (StringUtils.isEmpty(branchCode)) {
            branchCode = null
        }
        if (StringUtils.isEmpty(district)) {
            district = null
        }
        if (StringUtils.isEmpty(address)) {
            address = null
        }
        if (StringUtils.isEmpty(officerName)) {
            officerName = null
        }
        if (StringUtils.isEmpty(designation)) {
            designation = null
        }
        if (StringUtils.isEmpty(dataMobile)) {
            dataMobile = null
        }
        if (StringUtils.isEmpty(corporateMobile)) {
            corporateMobile = null
        }
        if (StringUtils.isEmpty(telephone) || telephone.trim().equals("-")) {
            telephone = null
        }
        if (StringUtils.isEmpty(email)) {
            email = ""
        }
    }
}

/*
Regex rgx = new Regex("[^a-zA-Z0-9 -]");
str = rgx.Replace(str, "");
* */