Spring Boot 自分用メモ crossfish21 6_3

SQL

```
create table schemah.user_master(
ldap_code varchar(10) NOT NULL,
password varchar(20) NOT NULL,
role smallint NOT NULL,
primary key(ldap_code)
);


create table schemah.department_master(
department_code varchar(18) NOT NULL,
department_name varchar(100) NOT NULL,
abbreviated_name varchar(100) NOT NULL,
primary key(department_code)
);



create table schemah.user_department_authority(
ldap_code varchar(18) NOT NULL,
department_code varchar(100) NOT NULL,
authority smallint NOT NULL,
primary key(ldap_code,department_code)
);





create table schemah.campany_staff_overtime(
version_name varchar(100) NOT NULL,
department_code varchar(18) NOT NULL,
staff_number varchar(9) NOT NULL, 
revision smallint NOT NULL,
update_date date NOT NULL,
job_group_code varchar(10) NOT NULL,
staff_name varchar(20) NOT NULL,
classfy varchar(50) NOT NULL,
overtime4 numeric(12,2) default null,
overtime_benefit4 numeric(12,2) default null,
overtime5 numeric(12,2) default null,
overtime_benefit5 numeric(12,2) default null,
overtime6 numeric(12,2) default null,
overtime_benefit6 numeric(12,2) default null,
overtime7 numeric(12,2) default null,
overtime_benefit7 numeric(12,2) default null,
overtime8 numeric(12,2) default null,
overtime_benefit8 numeric(12,2) default null,
overtime9 numeric(12,2) default null,
overtime_benefit9 numeric(12,2) default null,
overtime10 numeric(12,2) default null,
overtime_benefit10 numeric(12,2) default null,
overtime11 numeric(12,2) default null,
overtime_benefit11 numeric(12,2) default null,
overtime12 numeric(12,2) default null,
overtime_benefit12 numeric(12,2) default null,
overtime1 numeric(12,2) default null,
overtime_benefit1 numeric(12,2) default null,
overtime2 numeric(12,2) default null,
overtime_benefit2 numeric(12,2) default null,
overtime3 numeric(12,2) default null,
overtime_benefit3 numeric(12,2) default null,
deleted_flag smallint not null,
primary key(version_name,department_code,staff_number,revision)
);
```
---
page
index.html

```
<body>
<div id="wrapper">
	<h3>ユーザ認証</h3>
  <div th:if="${errorMessage != null}" th:text="${errorMessage}" class="errorMessageArea"></div>
	<form id="mainForm" action="check_user" method="POST">
		<table>
			<tr>
				<th>LDAP</th>
				<td><input type="text" id="ldap_code" name="ldap_code"></td>
			</tr>
			<tr>
				<th>パスワード</th>
				<td><input type="text" id="password" name="password"></td>
			</tr>
		</table>
		<br>
		<input type="submit" class="btn btn-info" value="送信">
	</form>
	<br>
</div>
</body>
```
---
company_staff_overtime_register.html

```
<body>
<div id="wrapper">
	<h3>社員の追加時間予定登録</h3>

	<p th:text="'部署コード' +  ${departmentCode}"></p>
	<p th:text="'部署名' +  ${departmentName}"></p>

	<form id="mainForm" action="company_staff_overtime_register_execute" enctype="multipart/form-data" method="POST">
		<p><input type="file" name="upFile"></p>
		<p><input type="submit" value="送信"></p>
	</form>
</div>
</body>
```
---
company_staff_overtime_department_select.html

```
<body>
<div id="wrapper">
	<h3>社員の追加時間予定登録の部署選択</h3>

	<form id="mainForm" action="company_staff_overtime_department_select_after" method="POST">
		<p><label for="department">部署を選択してください</label>
		  <select id="departmentCode" name="departmentCode">
		    <option th:each="department : ${departmentList}" th:value="${department}">[[${department}]]</option>
		  </select>
		</p>
		<p><input type="submit" value="送信"></p>
	</form>
</div>
</body>
```
---
CostClass

```
package com.jst.mikage.kokyaku.constvalue;

public final class ConstValues {

	public static final String COMPANY_STAFF_OVERTIME_WORK_FILENAME = "社員の追加時間予定の登録フォーマット";

	public static final String COMPANY_STAFF_OVERTIME_WORK_SHEETNAME = "社員の追加時間予定の登録フォーマット";

	private ConstValues(){   // デフォルトコンストラクタ
		// privateで継承不可にする
	}
}
```
---
Controller

```
package com.jst.mikage.kokyaku.controller;

import java.util.List;

import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.jst.mikage.kokyaku.service.CompanyStaffOvertimeRegisterService;
import com.jst.mikage.kokyaku.service.db.CampanyStaffOvertimeServiceTransaction;
import com.jst.mikage.kokyaku.service.db.DepartmentMasterServiceReadOnly;
import com.jst.mikage.kokyaku.service.db.UserDepartmentAuthorityServiceReadOnly;
import com.jst.mikage.kokyaku.service.db.UserMasterServiceReadOnly;

/*
 * @author Benten
 * DBへデータを登録する処理のコントローラー
 */
@Controller
public class ExecuteController {

  @Autowired
  HttpSession session;

  @Autowired
  private UserMasterServiceReadOnly userMasterServiceReadOnly;

  @Autowired
  private UserDepartmentAuthorityServiceReadOnly userDepartmentAuthorityServiceReadOnly;

  @Autowired
  private CampanyStaffOvertimeServiceTransaction campanyStaffOvertimeServiceTransaction;

  @Autowired
  private DepartmentMasterServiceReadOnly departmentMasterServiceReadOnly;


  // ログイン時の処理を実行する
  @RequestMapping(value = "check_user",method = RequestMethod.POST)
  public String checkUser(
      @RequestParam("ldap_code") String ldapCode,
      @RequestParam("password") String password,
      Model model) {

	// ldapCodeのユーザ確認
    if (userMasterServiceReadOnly.checkLdapCodeExists(ldapCode) == 0) {
      // user_masterテーブルにldapCodeが存在しない場合
      model.addAttribute("errorMessage", "LDAPコード・パスワードの組み合わせが間違っています");
      return "/index";
    }


    // パスワード確認
    String dbPassword = userMasterServiceReadOnly.checkPassword(ldapCode);
    if (!dbPassword.equals(password)) {
      // DBに登録されているパスワードと一致しない場合
      model.addAttribute("errorMessage", "LDAPコード・パスワードの組み合わせが間違っています");
      return "/index";
    }


    // role確認
    Integer role = userMasterServiceReadOnly.getRole(ldapCode);
    if (role == 0) {
        // 無効となっているユーザの場合
        model.addAttribute("errorMessage", "このユーザは無効なユーザとなっています");
        return "/index";

    } else if(role == 1) {
    	session.setAttribute("role", 1);
    	// session変数"role"に1(一般ユーザ)をセット
    	model.addAttribute("role", 1);
    } else if(role == 2) {
    	session.setAttribute("role", 2);
    	// session変数"role"に1(管理ユーザ)をセット
    	model.addAttribute("role", 2);
    }


    // 権限のある部署の取得

    List<String> refferenceDepartmetCodeList =
    	userDepartmentAuthorityServiceReadOnly.getRefferenceDepartmetCodeList(ldapCode);
    // 参照権限のある部署のリストを取得

    if (refferenceDepartmetCodeList.size() == 0) {
        // 参照権限のある部署が1つも無い場合
        model.addAttribute("errorMessage", "このユーザは権限のある部署が1つもありません");
        return "/index";
    } else {
    	session.setAttribute("refferenceDepartmetCodeList", refferenceDepartmetCodeList);
    }

    List<String> updateDepartmetCodeList =
    	userDepartmentAuthorityServiceReadOnly.getUpdateDepartmetCodeList(ldapCode);
    // 更新権限のある部署のリストを取得
    session.setAttribute("updateDepartmetCodeList", updateDepartmetCodeList);

    return "menu";
  }



  // 社員の追加時間予定の部署選択後の処理
  @RequestMapping(value = "company_staff_overtime_department_select_after",method = RequestMethod.POST)
  public String companyStaffOvertimedepartmentSelectAfter(
		  @RequestParam("departmentCode") String departmentCode, Model model) {

	  model.addAttribute("departmentCode",departmentCode);
	  String departmentName = departmentMasterServiceReadOnly.getDepartmentName(departmentCode);
	  //部署コードから部署名を取得
	  model.addAttribute("departmentName",departmentName);
	  return "company_staff_overtime_register";
	  // 社員の追加時間予定の登録画面へ
  }



  // 社員の追加時間予定の登録画面、部署選択画面の分岐)
  @RequestMapping("company_staff_overtime_register_switch")
  public String companyStaffOvertimeRegisterSwitch(Model model) {

    List<String> updateDepartmetCodeList =
       (List<String>)session.getAttribute("updateDepartmetCodeList");

    if(updateDepartmetCodeList.size() > 1) {
    // 複数部署に更新権限がある場合
    	model.addAttribute("departmentList",updateDepartmetCodeList);
    	return "company_staff_overtime_department_select";
        // 部署選択画面へ

    }else {
    	model.addAttribute("departmentCode",updateDepartmetCodeList.get(0));

    	String departmentName = departmentMasterServiceReadOnly.getDepartmentName(updateDepartmetCodeList.get(0));
    	//部署コードから部署名を取得
    	model.addAttribute("departmentName",departmentName);
    	return "company_staff_overtime_register";
        // 社員の追加時間予定の登録画面へ
    }

  }



  /*
   * @param multipartFile  アップされたファイル
   * @param model  Modelインスタンス
   * @return String 移動先のページのパス
   */
  @RequestMapping(value = "company_staff_overtime_register_execute",method = RequestMethod.POST)
  public String companyStaffOvertimeRegisterExecute(
      @RequestParam("upFile") MultipartFile upFile, Model model) {

    if (upFile.isEmpty()) {
      // ファイルがアップされていない場合
      model.addAttribute("errorMessage", "ファイルが指定されていません");
      return "company_staff_overtime_register";
    }

    CompanyStaffOvertimeRegisterService companyStaffOvertimeRegisterService =
    	new CompanyStaffOvertimeRegisterService();
    List<Object[]> dataList = (List<Object[]>) companyStaffOvertimeRegisterService.register(upFile);

    for(int i=0; i<dataList.size(); i++) {
    	campanyStaffOvertimeServiceTransaction.insert(dataList.get(i));
    }

    return "company_staff_overtime_register";
  }
}




package com.jst.mikage.kokyaku.controller;

import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
public class PageController {

  @Autowired
  HttpSession session;

  // ログイン画面
  @RequestMapping("index")
  public String mainMenu() {
    return "index";
    // ログイン画面へ
  }
}


```
---
Entity

```
package com.jst.mikage.kokyaku.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;

import lombok.Data;

@Entity
@Data
@IdClass(value = CampanyStaffOvertimeKey.class)
@Table(name = "campany_staff_overtime",schema="schemah")
public class CampanyStaffOvertime implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @Column(name = "version_name")
  private String versionName;

  @Id
  @Column(name = "department_code")
  private String departmentCode;

  @Id
  @Column(name = "staff_number")
  private String staffNumber;

  @Id
  @Column(name = "revision")
  private Integer revision;

  @NotNull
  @Column(name = "update_date")
  private Date updateDate;

  @NotNull
  @Column(name = "job_group_code")
  private String jobGroupCode;

  @NotNull
  @Column(name = "staff_name")
  private String staffName;

  @NotNull
  @Column(name = "classfy")
  private String classfy;

  @Column(name = "overtime4")
  private Double overtime4;

  @Column(name = "overtime_benefit4")
  private Double overtimeBenefit4;

  @Column(name = "overtime5")
  private Double overtime5;

  @Column(name = "overtime_benefit5")
  private Double overtimeBenefit5;

  @Column(name = "overtime6")
  private Double overtime6;

  @Column(name = "overtime_benefit6")
  private Double overtimeBenefit6;

  @Column(name = "overtime7")
  private Double overtime7;

  @Column(name = "overtime_benefit7")
  private Double overtimeBenefit7;

  @Column(name = "overtime8")
  private Double overtime8;

  @Column(name = "overtime_benefit8")
  private Double overtimeBenefit8;

  @Column(name = "overtime9")
  private Double overtime9;

  @Column(name = "overtime_benefit9")
  private Double overtimeBenefit9;

  @Column(name = "overtime10")
  private Double overtime10;

  @Column(name = "overtime_benefit10")
  private Double overtimeBenefit10;

  @Column(name = "overtime11")
  private Double overtime11;

  @Column(name = "overtime_benefit11")
  private Double overtimeBenefit11;

  @Column(name = "overtime12")
  private Double overtime12;

  @Column(name = "overtime_benefit12")
  private Double overtimeBenefit12;

  @Column(name = "overtime1")
  private Double overtime1;

  @Column(name = "overtime_benefit1")
  private Double overtimeBenefit1;

  @Column(name = "overtime2")
  private Double overtime2;

  @Column(name = "overtime_benefit2")
  private Double overtimeBenefit2;

  @Column(name = "overtime3")
  private Double overtime3;

  @Column(name = "overtime_benefit3")
  private Double overtimeBenefit3;

  @Column(name = "deleted_flag")
  private Integer deletedFlag;

}




package com.jst.mikage.kokyaku.entity;
import java.io.Serializable;
import lombok.Data;

@Data
public class CampanyStaffOvertimeKey implements Serializable {
// CampanyStaffOvertimeエンティティの複合主キーを設定するクラス

  private String versionName;
  private String departmentCode;
  private String staffNumber;
  private Integer revision;

}




package com.jst.mikage.kokyaku.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Entity
@Data
@Table(name = "department_master",schema="schemah")
public class DepartmentMaster implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @Column(name = "department_code")
  private String departmentCode;

  @Column(name = "department_name")
  private String departmentName;

  @Column(name = "abbreviated_name")
  private String abbreviatedName;

}




package com.jst.mikage.kokyaku.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.IdClass;
import javax.persistence.Table;

import lombok.Data;

@Entity
@Data
@IdClass(value = UserDepartmentAuthorityKey.class)
@Table(name = "user_department_authority",schema="schemah")
public class UserDepartmentAuthority implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @Column(name = "ldap_code")
  private String ldapCode;

  @Id
  @Column(name = "department_code")
  private String departmentCode;

  @Column(name = "authority")
  private Integer authority;

}




package com.jst.mikage.kokyaku.entity;

import java.io.Serializable;

import lombok.Data;

@Data
public class UserDepartmentAuthorityKey implements Serializable {
// UserDepartmentAuthorityエンティティの複合主キーを設定するクラス

  private String ldapCode;
  private String departmentCode;
  private Integer authority;
}




package com.jst.mikage.kokyaku.entity;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.Data;

@Entity
@Data
@Table(name = "user_master",schema="schemah")
public class UserMaster implements Serializable {

  private static final long serialVersionUID = 1L;

  @Id
  @Column(name = "ldap_code")
  private String ldapCode;

  @Column(name = "password")
  private String password;

  @Column(name = "role")
  private Integer role;

}
```
---
Repository

```
package com.jst.mikage.kokyaku.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.jst.mikage.kokyaku.entity.CampanyStaffOvertime;
import com.jst.mikage.kokyaku.entity.CampanyStaffOvertimeKey;

public interface CampanyStaffOvertimeRepository
    extends JpaRepository<CampanyStaffOvertime, CampanyStaffOvertimeKey> {

// 最終のリビジョン番号を取得する
  @Query(value = "SELECT DISTINCT revision FROM outsourcing_working_time o WHERE version_name=:versionName AND "
		  + "department_code=:departmentCode ORDER BY revision DESC LIMIT 1", nativeQuery = true)
// 先頭にvalue = を、末尾に, nativeQuery = trueをつける。valueはObject型になる。メソッドの戻り値として使える
// outsourcing_working_timeはテーブル名だが、Entityクラスの名前ではなくDBのテーブル名をそのまま記述することに注意
  public Integer getLastRivisionNumber(
		  @Param("versionName")String  versionName,
		  @Param("departmentCode")String  departmentCode);

}



package com.jst.mikage.kokyaku.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.jst.mikage.kokyaku.entity.DepartmentMaster;

public interface DepartmentMasterRepository
    extends JpaRepository<DepartmentMaster, String> {

  // departmentCodeからdepartment_nameを取得する
  @Query(value = "SELECT department_name FROM department_master d "
  		+ "WHERE department_code=:departmentCode ", nativeQuery = true)
  public String getDepartmentName(@Param("departmentCode")String  departmentCode);

}





package com.jst.mikage.kokyaku.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.jst.mikage.kokyaku.entity.UserDepartmentAuthority;
import com.jst.mikage.kokyaku.entity.UserDepartmentAuthorityKey;

public interface UserDepartmentAuthorityRepository
    extends JpaRepository<UserDepartmentAuthority, UserDepartmentAuthorityKey> {

  // ldapCodeで指定したユーザの参照権限がある部署コードのリストを取得する
  @Query(value = "SELECT  department_code FROM user_department_authority u "
  		+ "WHERE ldap_code=:ldapCode ", nativeQuery = true)
  public List<String> getRefferenceDepartmetCodeList(@Param("ldapCode")String  ldapCode);


  // ldapCodeで指定したユーザの更新権限がある部署コードのリストを取得する
  @Query(value = "SELECT  department_code FROM user_department_authority u "
		  + "WHERE ldap_code=:ldapCode AND authority=1", nativeQuery = true)
  public List<String> getUpdateDepartmetCodeList(@Param("ldapCode")String  ldapCode);

}




package com.jst.mikage.kokyaku.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.jst.mikage.kokyaku.entity.UserMaster;

public interface UserMasterRepository
    extends JpaRepository<UserMaster, String> {

// user_masterテーブルにldapCodeが存在するかをチェックする
  @Query(value = "SELECT Count(*) ldap_code FROM user_master u "
  		+ "WHERE ldap_code=:ldapCode ", nativeQuery = true)
  public Integer checkLdapCodeExists(@Param("ldapCode")String  ldapCode);


  //user_masterテーブルからldapCodeに結びつくpasswordを取得する
  @Query(value = "SELECT password FROM user_master u "
        + "WHERE ldap_code=:ldapCode ", nativeQuery = true)
  public String checkPassword(@Param("ldapCode")String  ldapCode);


  //user_masterテーブルからldapCodeに結びつくroleを取得する
  @Query(value = "SELECT role FROM user_master u "
		  + "WHERE ldap_code=:ldapCode ", nativeQuery = true)
  public Integer getRole(@Param("ldapCode")String  ldapCode);

}
```
---
Service

```
package com.jst.mikage.kokyaku.service;

import static com.jst.mikage.kokyaku.constvalue.ConstValues.*;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.jst.mikage.kokyaku.service.db.CampanyStaffOvertimeServiceTransaction;

@Service
public class CompanyStaffOvertimeRegisterService {

  @Autowired
  private CampanyStaffOvertimeServiceTransaction campanyStaffOvertimeServiceTransaction;

	public List<Object[]> register(MultipartFile upFile){

//		List<String> errorMessages = new ArrayList<>();
        List<Object[]> dataList = new ArrayList<Object[]>();
        Object[] dataArray;

        try(FileInputStream input = (FileInputStream) upFile.getInputStream()){

            try(XSSFWorkbook targetBook = (XSSFWorkbook) WorkbookFactory.create(input)){

                XSSFSheet targetSheet = targetBook.getSheet(COMPANY_STAFF_OVERTIME_WORK_SHEETNAME);
                Row row02 = targetSheet.getRow(1);
                Cell cell52 = row02.getCell(51);

                String departmentCode = cell52.getStringCellValue();  // 所属部署コード

                Row row03 = targetSheet.getRow(2);
                Cell cell03 = row03.getCell(2);
                String versionName = cell03.getStringCellValue();  // バージョン名

//                Double double01 = cell02.getNumericCellValue();


                Integer revision = 1;
                Row rowData;  // データ部の読み込みに使うRow
                Cell cellData; // データ部の読み込みに使うCell

                for(int i=9; i<10000; i++) {

                	dataArray = new Object[21];
                	rowData = targetSheet.getRow(i);

                	if(rowData == null) {  // その行が入力なしになったら、そこで読み込み終了
                		break;
                	}

                	dataArray[0] = versionName;
                	dataArray[1] = departmentCode;

                	cellData = rowData.getCell(1);
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		break;  // 氏名が入力されていない行になったら、そこで読み込み終了
                	}else {
                		dataArray[2] = cellData.getStringCellValue();
                	}

                	dataArray[3] = revision;
                	dataArray[4] = new Date();
                	dataArray[5] = "jobGRCode";  // 職群コード。TODO

                	cellData = rowData.getCell(2);
                	dataArray[6] = cellData.getStringCellValue();

                	cellData = rowData.getCell(3);
                	dataArray[7] = cellData.getStringCellValue();

                	// ここから数値部
                	cellData = rowData.getCell(4);  // 4月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[8] = new Double(0.00);
                	}else {
                		dataArray[8] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(5);  // 5月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[9] = new Double(0.00);
                	}else {
                		dataArray[9] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(6);  // 6月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[10] = new Double(0.00);
                	}else {
                		dataArray[10] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(7);  // 7月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[11] = new Double(0.00);
                	}else {
                		dataArray[11] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(8);  // 8月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[12] = new Double(0.00);
                	}else {
                		dataArray[12] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(9);  // 9月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[13] = new Double(0.00);
                	}else {
                		dataArray[13] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(10);  // 10月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[14] = new Double(0.00);
                	}else {
                		dataArray[14] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(11);  // 11月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[15] = new Double(0.00);
                	}else {
                		dataArray[15] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(12);  // 12月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[16] = new Double(0.00);
                	}else {
                		dataArray[16] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(13);  // 1月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[17] = new Double(0.00);
                	}else {
                		dataArray[17] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(14);  // 2月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[18] = new Double(0.00);
                	}else {
                		dataArray[18] = cellData.getNumericCellValue();
                	}
                	cellData = rowData.getCell(15);  // 3月
                	if(cellData == null || cellData.getCellType() == Cell.CELL_TYPE_BLANK) {
                		dataArray[19] = new Double(0.00);
                	}else {
                		dataArray[19] = cellData.getNumericCellValue();
                	}

                	dataArray[20] = new Integer(0);

                	dataList.add(dataArray);
                }


            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

		return dataList;
	}
}




package com.jst.mikage.kokyaku.service.db;

public interface CampanyStaffOvertimeServiceTransaction{

    public void insert(Object[] dataArray);
}




package com.jst.mikage.kokyaku.service.db;

import java.util.Date;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jst.mikage.kokyaku.entity.CampanyStaffOvertime;
import com.jst.mikage.kokyaku.repository.CampanyStaffOvertimeRepository;

@Service
@Transactional(readOnly = false)
public class CampanyStaffOvertimeServiceTransactionImple implements CampanyStaffOvertimeServiceTransaction {

    @Autowired
    private CampanyStaffOvertimeRepository campanyStaffOvertimeRepository;

    // 1レコードを挿入する
    @Override
    public void insert(Object[] dataArray) {
      CampanyStaffOvertime campanyStaffOvertime = new CampanyStaffOvertime();
      campanyStaffOvertime.setVersionName((String)dataArray[0]);
      campanyStaffOvertime.setDepartmentCode((String)dataArray[1]);
      campanyStaffOvertime.setStaffNumber((String)dataArray[2]);
      campanyStaffOvertime.setRevision((Integer)dataArray[3]);
      campanyStaffOvertime.setUpdateDate(new Date());
      campanyStaffOvertime.setJobGroupCode((String)dataArray[5]);
      campanyStaffOvertime.setStaffName((String)dataArray[6]);
      campanyStaffOvertime.setClassfy((String)dataArray[7]);
      campanyStaffOvertime.setOvertime4((Double)dataArray[8]);
      campanyStaffOvertime.setOvertimeBenefit4((Double)dataArray[8] * 2000);
      campanyStaffOvertime.setOvertime5((Double)dataArray[9]);
      campanyStaffOvertime.setOvertimeBenefit5((Double)dataArray[9] * 2000);
      campanyStaffOvertime.setOvertime6((Double)dataArray[10]);
      campanyStaffOvertime.setOvertimeBenefit6((Double)dataArray[10] * 2000);
      campanyStaffOvertime.setOvertime7((Double)dataArray[11]);
      campanyStaffOvertime.setOvertimeBenefit7((Double)dataArray[11] * 2000);
      campanyStaffOvertime.setOvertime8((Double)dataArray[12]);
      campanyStaffOvertime.setOvertimeBenefit8((Double)dataArray[12] * 2000);
      campanyStaffOvertime.setOvertime9((Double)dataArray[13]);
      campanyStaffOvertime.setOvertimeBenefit9((Double)dataArray[13] * 2000);
      campanyStaffOvertime.setOvertime10((Double)dataArray[14]);
      campanyStaffOvertime.setOvertimeBenefit10((Double)dataArray[14] * 2000);
      campanyStaffOvertime.setOvertime11((Double)dataArray[15]);
      campanyStaffOvertime.setOvertimeBenefit11((Double)dataArray[15] * 2000);
      campanyStaffOvertime.setOvertime12((Double)dataArray[16]);
      campanyStaffOvertime.setOvertimeBenefit12((Double)dataArray[16] * 2000);
      campanyStaffOvertime.setOvertime1((Double)dataArray[17]);
      campanyStaffOvertime.setOvertimeBenefit1((Double)dataArray[17] * 2000);
      campanyStaffOvertime.setOvertime2((Double)dataArray[18]);
      campanyStaffOvertime.setOvertimeBenefit2((Double)dataArray[18] * 2000);
      campanyStaffOvertime.setOvertime3((Double)dataArray[19]);
      campanyStaffOvertime.setOvertimeBenefit3((Double)dataArray[19] * 2000);

      campanyStaffOvertime.setDeletedFlag(0);
      campanyStaffOvertime = campanyStaffOvertimeRepository.saveAndFlush(campanyStaffOvertime);
    }

}




package com.jst.mikage.kokyaku.service.db;

import org.springframework.stereotype.Service;

@Service
public interface DepartmentMasterServiceReadOnly {

  public String getDepartmentName(String departmentCode);

}



package com.jst.mikage.kokyaku.service.db;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jst.mikage.kokyaku.repository.DepartmentMasterRepository;

@Service
@Transactional(readOnly = false)
public class DepartmentMasterServiceReadOnlyImple implements DepartmentMasterServiceReadOnly {

  @Autowired
  private DepartmentMasterRepository departmentMasterRepository;

    //departmentCodeからdepartment_nameを取得する
	@Override
	public String getDepartmentName(String departmentCode) {

		return departmentMasterRepository.getDepartmentName(departmentCode);
	}

}



package com.jst.mikage.kokyaku.service.db;

import java.util.List;

import org.springframework.stereotype.Service;

@Service
public interface UserDepartmentAuthorityServiceReadOnly {

  public List<String> getRefferenceDepartmetCodeList(String  ldapCode);

  public List<String> getUpdateDepartmetCodeList(String  ldapCode);

}




package com.jst.mikage.kokyaku.service.db;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jst.mikage.kokyaku.repository.UserDepartmentAuthorityRepository;

@Service
@Transactional(readOnly = false)
public class UserDepartmentAuthorityServiceReadOnlyImple implements UserDepartmentAuthorityServiceReadOnly {

  @Autowired
  private UserDepartmentAuthorityRepository userDepartmentAuthorityRepository;

  //ldapCodeで指定したユーザの参照権限がある部署コードのリストを取得する
  @Override
  public List<String> getRefferenceDepartmetCodeList(String ldapCode) {
    List<String> refferenceDepartmetCodeList =
      userDepartmentAuthorityRepository.getRefferenceDepartmetCodeList(ldapCode);
    return refferenceDepartmetCodeList;
  }

  //ldapCodeで指定したユーザの更新権限がある部署コードのリストを取得する
  @Override
  public List<String> getUpdateDepartmetCodeList(String ldapCode) {
    List<String> updateDepartmetCodeList =
      userDepartmentAuthorityRepository.getUpdateDepartmetCodeList(ldapCode);
    return updateDepartmetCodeList;
  }

}




package com.jst.mikage.kokyaku.service.db;

import org.springframework.stereotype.Service;

@Service
public interface UserMasterServiceReadOnly {

  public Integer checkLdapCodeExists(String  ldapCode);

  public String checkPassword(String  ldapCode);

  public Integer getRole(String  ldapCode);

}




package com.jst.mikage.kokyaku.service.db;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.jst.mikage.kokyaku.repository.UserMasterRepository;

@Service
@Transactional(readOnly = false)
public class UserMasterServiceReadOnlyImple implements UserMasterServiceReadOnly {

  @Autowired
  private UserMasterRepository userMasterRepository;

  // LDAPコードがuser_masterに存在するかをチェック
  @Override
  public Integer checkLdapCodeExists(String  ldapCode) {
	  Integer count = userMasterRepository.checkLdapCodeExists(ldapCode);
	  return count;
  };

  // user_masterテーブルからldapCodeに結びつくpasswordを取得する
  @Override
  public String checkPassword(String  ldapCode) {
	  String password = userMasterRepository.checkPassword(ldapCode);
	  return password;
  };

  //user_masterテーブルからldapCodeに結びつくroleを取得する
  @Override
  public Integer getRole(String  ldapCode) {
	  Integer role = userMasterRepository.getRole(ldapCode);
	  return role;
  }

}

```