LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@anchorIndigo_215632(crossfish21)

Spring Boot 自分用メモ crossfish21 6_3

Posted at

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;
  }

}
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?