import { Controller, Post, Route, Security, Tags, Request, UploadedFile, Path } from "tsoa";
import { AppDataSource } from "../database/data-source";
import { In, IsNull, LessThanOrEqual, Not, Between } from "typeorm";
import HttpSuccess from "../interfaces/http-success";
import { UseInterceptors } from "@nestjs/common";
import { Profile } from "../entities/Profile";
import { ProfileEmployee } from "../entities/ProfileEmployee";
import { ProfileSalary } from "../entities/ProfileSalary";
import { ProfileFamilyCouple } from "../entities/ProfileFamilyCouple";
import { ProfileFamilyMother } from "../entities/ProfileFamilyMother";
import { ProfileFamilyFather } from "../entities/ProfileFamilyFather";
import { ProfileEducation } from "../entities/ProfileEducation";
import { PosLevel } from "../entities/PosLevel";
import { PosType } from "../entities/PosType";
import { FileInterceptor } from "@nestjs/platform-express";
import * as xlsx from "xlsx";
import Extension from "../interfaces/extension";
import { calculateRetireDate, calculateRetireLaw } from "../interfaces/utils";
import * as fs from "fs";
import * as path from "path";
const { createConnection } = require("typeorm");
import csvParser from "csv-parser";
import { HR_PERSONAL_OFFICER_FAMILY } from "../entities/mis/HR_PERSONAL_OFFICER_FAMILY";

const BATCH_SIZE = 1000;
// import { EducationMis } from "../entities/EducationMis";
import { EducationMis } from "../entities/mis/EducationMis";
import { ProvinceImport } from "../entities/mis/ProvinceImport";
import { AmphurImport } from "../entities/mis/AmphurImport";
import { SubDistrictImport } from "../entities/mis/SubDistrictImport";
import { Province } from "../entities/Province";
import { District } from "../entities/District";
import { SubDistrict } from "../entities/SubDistrict";
import { HR_EDUCATION } from "../entities/mis/HR_EDUCATION";
import { HR_PERSONAL_OFFICER_ADDRESS } from "../entities/mis/HR_PERSONAL_OFFICER_ADDRESS";
import { HR_EDUCATION_EMP } from "../entities/mis/HR_EDUCATION_EMP";
import { HR_PERSONAL_EMP_ADDRESS } from "../entities/mis/HR_PERSONAL_EMP_ADDRESS";
import { HR_PERSONAL_EMP_FAMILY } from "../entities/mis/HR_PERSONAL_EMP_FAMILY";
import { OrgRoot } from "../entities/OrgRoot";
import { OrgChild1 } from "../entities/OrgChild1";
import { OrgChild2 } from "../entities/OrgChild2";
import { OrgChild3 } from "../entities/OrgChild3";
import { OrgChild4 } from "../entities/OrgChild4";
import { IMPORT_ORG } from "../entities/mis/IMPORT_ORG";
import { OrgRevision } from "../entities/OrgRevision";
import { OFFICER } from "../entities/mis/OFFICER";
import { Position } from "../entities/Position";
import { PosMaster } from "../entities/PosMaster";
import { PosExecutive } from "../entities/PosExecutive";
import { EducationLevel } from "../entities/EducationLevel";
import { HR_FUND_COURSE_CODE } from "../entities/mis/HR_FUND_COURSE_CODE";
import { HR_MAJOR_CODE } from "../entities/mis/HR_MAJOR_CODE";
// import { uuidv7 } from "uuidv7";
import { ProfileSalaries } from "../entities/mis/ProfileSalaries";
import { HR_POSITION_OFFICER } from "../entities/mis/HR_POSITION_OFFICER";
import { EMPLOYEE } from "../entities/mis/EMPLOYEE";
import { EmployeePosLevel } from "../entities/EmployeePosLevel";
import { EmployeePosType } from "../entities/EmployeePosType";
import { EmployeePosMaster } from "../entities/EmployeePosMaster";
import { EmployeePosition } from "../entities/EmployeePosition";
import { HR_POSITION_EMPLOYEE } from "../entities/mis/HR_POSITION_EMPLOYEE";
import { ProfileDiscipline } from "../entities/ProfileDiscipline";
import { ProfileInsignia } from "../entities/ProfileInsignia";
import { HR_DISCIPLINE_EMP } from "../entities/mis/HR_DISCIPLINE_EMP";
import { HR_DISCIPLINE } from "../entities/mis/HR_DISCIPLINE";
import { HR_INSIGNIA } from "../entities/mis/HR_INSIGNIA";
import { HR_INSIGNIA_EMP } from "../entities/mis/HR_INSIGNIA_EMP";
import { Insignia } from "../entities/Insignia";
import { ProfileChildren } from "../entities/ProfileChildren";
import { ProfileChangeName } from "../entities/ProfileChangeName";
import { HR_CHILDEN } from "../entities/mis/HR_CHILDEN";
import { HR_CHILDEN_EMP } from "../entities/mis/HR_CHILDEN_EMP";
import { HR_CHANGENAME } from "../entities/mis/HR_CHANGENAME";
import { HR_CHANGENAME_EMP } from "../entities/mis/HR_CHANGENAME_EMP";
import { ProfileHonor } from "../entities/ProfileHonor";
import { ProfileAbility } from "../entities/ProfileAbility";
import { ProfileDuty } from "../entities/ProfileDuty";
import { ProfileNopaid } from "../entities/ProfileNopaid";
import { ProfileOther } from "../entities/ProfileOther";
import { ProfileCertificate } from "../entities/ProfileCertificate";
import { ProfileAbilitys } from "../entities/mis/ProfileAbilitys";
import { ProfileCertificates } from "../entities/mis/ProfileCertificates";
import { ProfileDutys } from "../entities/mis/ProfileDutys";
import { ProfileHonors } from "../entities/mis/ProfileHonors";
import { ProfileNopaids } from "../entities/mis/ProfileNopaids";
import { ProfileOthers } from "../entities/mis/ProfileOthers";
import { ProfileDisciplines } from "../entities/mis/ProfileDisciplines";
import { ProfileChangeNames } from "../entities/mis/ProfileChangeNames";
import { ProfileChildrens } from "../entities/mis/ProfileChildrens";
import { ProfileEducations } from "../entities/mis/ProfileEducations";
import { ProfileInsignias } from "../entities/mis/ProfileInsignias";
import { ProfileLeave } from "../entities/ProfileLeave";
import { ProfileLeaves } from "../entities/mis/ProfileLeaves";
import { ProfileSalaryTemp } from "../entities/ProfileSalaryTemp";
import { ProfileTraining } from "../entities/ProfileTraining";
import { ProfileTrainings } from "../entities/mis/ProfileTrainings";
import { EMPLOYEETEMP } from "../entities/mis/EMPLOYEETEMP";
import { HR_CHANGENAME_EMPTEMP } from "../entities/mis/HR_CHANGENAME_EMPTEMP";
import { HR_DISCIPLINE_EMPTEMP } from "../entities/mis/HR_DISCIPLINE_EMPTEMP";
import { HR_EDUCATION_EMPTEMP } from "../entities/mis/HR_EDUCATION_EMPTEMP";
import { HR_PERSONAL_EMPTEMP_ADDRESS } from "../entities/mis/HR_PERSONAL_EMPTEMP_ADDRESS";
import { HR_PERSONAL_EMPTEMP_FAMILY } from "../entities/mis/HR_PERSONAL_EMPTEMP_FAMILY";
import { HR_POSITION_EMPLOYEETEMP } from "../entities/mis/HR_POSITION_EMPLOYEETEMP";
import { positionOfficer } from "../entities/mis/positionOfficer";
import { ProvinceMaster } from "../entities/ProvinceMaster";
import { SubDistrictMaster } from "../entities/SubDistrictMaster";
import { DistrictMaster } from "../entities/DistrictMaster";
import { RequestWithUser } from "../middlewares/user";
@Route("api/v1/org/upload")
@Tags("UPLOAD")
@Security("bearerAuth")
export class ImportDataController extends Controller {
  private profileEducationRepo = AppDataSource.getRepository(ProfileEducation);
  private profileDisciplineRepo = AppDataSource.getRepository(ProfileDiscipline);
  private profileInsigniaRepo = AppDataSource.getRepository(ProfileInsignia);
  private profileFamilyCoupleRepository = AppDataSource.getRepository(ProfileFamilyCouple);
  private profileFamilyMotherRepository = AppDataSource.getRepository(ProfileFamilyMother);
  private profileFamilyFatherRepository = AppDataSource.getRepository(ProfileFamilyFather);
  private salaryRepo = AppDataSource.getRepository(ProfileSalary);
  private profileChildrenRepo = AppDataSource.getRepository(ProfileChildren);
  private profileChangeNameRepo = AppDataSource.getRepository(ProfileChangeName);
  private profileRepo = AppDataSource.getRepository(Profile);
  private profileEmpRepo = AppDataSource.getRepository(ProfileEmployee);
  private posLevelRepo = AppDataSource.getRepository(PosLevel);
  private posTypeRepo = AppDataSource.getRepository(PosType);
  private posLevelEmpRepo = AppDataSource.getRepository(EmployeePosLevel);
  private posTypeEmpRepo = AppDataSource.getRepository(EmployeePosType);
  private positionOfficerRepo = AppDataSource.getRepository(positionOfficer);
  private ProfileSalariesRepo = AppDataSource.getRepository(ProfileSalaries);
  private HR_PERSONAL_OFFICER_FAMILYRepo = AppDataSource.getRepository(HR_PERSONAL_OFFICER_FAMILY);
  private HR_EDUCATIONRepo = AppDataSource.getRepository(HR_EDUCATION);
  private HR_DISCIPLINERepo = AppDataSource.getRepository(HR_DISCIPLINE);
  private HR_INSIGNIARepo = AppDataSource.getRepository(HR_INSIGNIA);
  private HR_CHILDENRepo = AppDataSource.getRepository(HR_CHILDEN);
  private HR_CHANGENAMERepo = AppDataSource.getRepository(HR_CHANGENAME);
  private HR_PERSONAL_OFFICER_ADDRESSRepo = AppDataSource.getRepository(
    HR_PERSONAL_OFFICER_ADDRESS,
  );
  private HR_DISCIPLINE_EMPRepo = AppDataSource.getRepository(HR_DISCIPLINE_EMP);
  private HR_INSIGNIA_EMPRepo = AppDataSource.getRepository(HR_INSIGNIA_EMP);
  private HR_CHILDEN_EMPRepo = AppDataSource.getRepository(HR_CHILDEN_EMP);
  private HR_CHANGENAME_EMPRepo = AppDataSource.getRepository(HR_CHANGENAME_EMP);
  private HR_EDUCATION_EMPRepo = AppDataSource.getRepository(HR_EDUCATION_EMP);
  private HR_PERSONAL_EMP_ADDRESSRepo = AppDataSource.getRepository(HR_PERSONAL_EMP_ADDRESS);
  private HR_PERSONAL_EMP_FAMILYRepo = AppDataSource.getRepository(HR_PERSONAL_EMP_FAMILY);

  private HR_DISCIPLINE_EMPTEMPRepo = AppDataSource.getRepository(HR_DISCIPLINE_EMPTEMP);
  private HR_CHANGENAME_EMPTEMPRepo = AppDataSource.getRepository(HR_CHANGENAME_EMPTEMP);
  private HR_EDUCATION_EMPTEMPRepo = AppDataSource.getRepository(HR_EDUCATION_EMPTEMP);
  private HR_PERSONAL_EMPTEMP_ADDRESSRepo = AppDataSource.getRepository(
    HR_PERSONAL_EMPTEMP_ADDRESS,
  );
  private HR_PERSONAL_EMPTEMP_FAMILYRepo = AppDataSource.getRepository(HR_PERSONAL_EMPTEMP_FAMILY);

  private educationMisRepo = AppDataSource.getRepository(EducationMis);
  private educationLevelRepo = AppDataSource.getRepository(EducationLevel);
  private HR_MAJOR_CODERepo = AppDataSource.getRepository(HR_MAJOR_CODE);
  private HR_FUND_COURSE_CODERepo = AppDataSource.getRepository(HR_FUND_COURSE_CODE);
  private HR_POSITION_OFFICERRepo = AppDataSource.getRepository(HR_POSITION_OFFICER);
  private HR_POSITION_EMPLOYEERepo = AppDataSource.getRepository(HR_POSITION_EMPLOYEE);
  private HR_POSITION_EMPLOYEETEMPRepo = AppDataSource.getRepository(HR_POSITION_EMPLOYEETEMP);
  private provincsRepo = AppDataSource.getRepository(ProvinceImport);
  private amphurRepo = AppDataSource.getRepository(AmphurImport);
  private subDistrictRepo = AppDataSource.getRepository(SubDistrictImport);
  private provinceIdRepo = AppDataSource.getRepository(Province);
  private districtIdRepo = AppDataSource.getRepository(District);
  private subDistrictIdRepo = AppDataSource.getRepository(SubDistrict);

  private orgRevisionRepo = AppDataSource.getRepository(OrgRevision);
  private orgRootRepo = AppDataSource.getRepository(OrgRoot);
  private orgChild1Repo = AppDataSource.getRepository(OrgChild1);
  private orgChild2Repo = AppDataSource.getRepository(OrgChild2);
  private orgChild3Repo = AppDataSource.getRepository(OrgChild3);
  private orgChild4Repo = AppDataSource.getRepository(OrgChild4);
  private IMPORT_ORGRepo = AppDataSource.getRepository(IMPORT_ORG);
  private OFFICERRepo = AppDataSource.getRepository(OFFICER);
  private EMPLOYEERepo = AppDataSource.getRepository(EMPLOYEE);
  private EMPLOYEETEMPRepo = AppDataSource.getRepository(EMPLOYEETEMP);

  private positionEmpRepo = AppDataSource.getRepository(EmployeePosition);
  private positionRepo = AppDataSource.getRepository(Position);
  private posMasterRepo = AppDataSource.getRepository(PosMaster);
  private posMasterEmpRepo = AppDataSource.getRepository(EmployeePosMaster);
  private posExecutiveRepo = AppDataSource.getRepository(PosExecutive);
  private insigniaRepo = AppDataSource.getRepository(Insignia);

  private ProfileHonorsRepo = AppDataSource.getRepository(ProfileHonors);
  private HonorRepo = AppDataSource.getRepository(ProfileHonor);
  private ProfileOthersRepo = AppDataSource.getRepository(ProfileOthers);
  private OtherRepo = AppDataSource.getRepository(ProfileOther);
  private ProfileNopaidsRepo = AppDataSource.getRepository(ProfileNopaids);
  private NopaidRepo = AppDataSource.getRepository(ProfileNopaid);
  private ProfileAbilitysRepo = AppDataSource.getRepository(ProfileAbilitys);
  private AbilityRepo = AppDataSource.getRepository(ProfileAbility);
  private ProfileDutysRepo = AppDataSource.getRepository(ProfileDutys);
  private DutyRepo = AppDataSource.getRepository(ProfileDuty);
  private ProfileDisciplinesRepo = AppDataSource.getRepository(ProfileDisciplines);
  private DisciplineRepo = AppDataSource.getRepository(ProfileDiscipline);
  private ProfileCertificatesRepo = AppDataSource.getRepository(ProfileCertificates);
  private CertificateRepo = AppDataSource.getRepository(ProfileCertificate);
  private ProfileChangeNamesRepo = AppDataSource.getRepository(ProfileChangeNames);
  private ChangeNameRepo = AppDataSource.getRepository(ProfileChangeName);
  private ProfileChildrensRepo = AppDataSource.getRepository(ProfileChildrens);
  private ChildrenRepo = AppDataSource.getRepository(ProfileChildren);
  private ProfileEducationsRepo = AppDataSource.getRepository(ProfileEducations);
  private EducationRepo = AppDataSource.getRepository(ProfileEducation);
  private ProfileLeaveSummarysRepo = AppDataSource.getRepository(ProfileLeaves);
  private LeaveSummaryRepo = AppDataSource.getRepository(ProfileLeave);
  private ProfileInsigniasRepo = AppDataSource.getRepository(ProfileInsignias);
  private InsigniaRepo = AppDataSource.getRepository(ProfileInsignia);
  private ProfileTrainingsRepo = AppDataSource.getRepository(ProfileTrainings);
  private TrainingRepo = AppDataSource.getRepository(ProfileTraining);
  private salaryTempRepo = AppDataSource.getRepository(ProfileSalaryTemp);

  /**
   * @summary ทะเบียนประวัติ ข้าราชการ
   */
  @Post("uploadProfile-Officer")
  async UploadFileSqlOfficer(@Request() request: { user: Record<string, any> }) {
    const OFFICER = await this.OFFICERRepo.find();
    let rowCount = 0;
    let _null: any = null;
    let profile: any;
    for await (const item of OFFICER) {
      rowCount++;
      let type_: any = null;
      let level_: any = null;
      profile = new Profile();

      const existingProfile = await this.profileRepo.findOne({
        where: { citizenId: item.CIT.toString() },
      });
      if (existingProfile) {
        profile.id = existingProfile.id;
        if (profile.typeId != null) {
          continue;
        }
        // continue;
      }

      var positionType = "";
      var positionLevel = "";
      if (item.MP_CEE == "21") {
        positionType = "ทั่วไป";
        positionLevel = "ปฏิบัติงาน";
      } else if (item.MP_CEE == "22") {
        positionType = "ทั่วไป";
        positionLevel = "ชำนาญงาน";
      } else if (item.MP_CEE == "23") {
        positionType = "ทั่วไป";
        positionLevel = "อาวุโส";
      } else if (item.MP_CEE == "24") {
        positionType = "ทั่วไป";
        positionLevel = "อาวุโสเฉพาะสายงานที่กำหนด";
      } else if (item.MP_CEE == "25") {
        positionType = "ทั่วไป";
        positionLevel = "ทักษะพิเศษ";
      } else if (item.MP_CEE == "26") {
        positionType = "วิชาการ";
        positionLevel = "ปฏิบัติการ";
      } else if (item.MP_CEE == "27") {
        positionType = "วิชาการ";
        positionLevel = "ชำนาญการ";
      } else if (item.MP_CEE == "28") {
        positionType = "วิชาการ";
        positionLevel = "ชำนาญการพิเศษ";
      } else if (item.MP_CEE == "29") {
        positionType = "วิชาการ";
        positionLevel = "เชี่ยวชาญ";
      } else if (item.MP_CEE == "30") {
        positionType = "วิชาการ";
        positionLevel = "ทรงคุณวุฒิ";
      } else if (item.MP_CEE == "31") {
        positionType = "วิชาการ";
        positionLevel = "ทรงคุณวุฒิเฉพาะสายงานที่กำหนด";
      } else if (item.MP_CEE == "32") {
        positionType = "อำนวยการ";
        positionLevel = "ต้น";
      } else if (item.MP_CEE == "33") {
        positionType = "อำนวยการ";
        positionLevel = "สูง";
      } else if (item.MP_CEE == "34") {
        positionType = "บริหาร";
        positionLevel = "ต้น";
      } else if (item.MP_CEE == "35") {
        positionType = "บริหาร";
        positionLevel = "สูง";
      }
      if (positionType) {
        type_ = await this.posTypeRepo.findOne({
          where: { posTypeName: positionType },
        });
      }
      if (positionLevel) {
        if (type_ == null) {
          level_ = await this.posLevelRepo.findOne({
            where: {
              posLevelName: positionLevel,
            },
          });
        } else {
          level_ = await this.posLevelRepo.findOne({
            where: {
              posLevelName: positionLevel,
              posTypeId: type_.id,
            },
          });
        }
      }

      let BORN = item.BORN;
      // if (item.BORN) {
      //   const [datePart] = item.BORN.split(" ");
      //   const [day, month, year] = datePart.split("/");
      //   BORN = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
      // }
      let BEGIN_ENTRY_DATE = item.BEGIN_ENTRY_DATE;
      // if (item.BEGIN_ENTRY_DATE) {
      //   const [datePart] = item.BEGIN_ENTRY_DATE.split(" ");
      //   const [day, month, year] = datePart.split("/");
      //   BEGIN_ENTRY_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
      // }

      profile.citizenId = item.CIT;
      profile.rank =
        item.RANK_NAME == "" ||
        item.RANK_NAME == "นาย" ||
        item.RANK_NAME == "นาง" ||
        item.RANK_NAME == "นางสาว"
          ? null
          : item.RANK_NAME;
      profile.prefix = item.RANK_NAME == "" ? null : item.RANK_NAME;
      profile.prefixMain =
        item.RANK_NAME == "" ||
        (item.RANK_NAME != "นาย" && item.RANK_NAME != "นาง" && item.RANK_NAME != "นางสาว")
          ? null
          : item.RANK_NAME;
      profile.firstName = item.FNAME == "" ? null : item.FNAME;
      profile.lastName = item.LNAME == "" ? null : item.LNAME;
      profile.gender = item.SEX == "1" ? "ชาย" : item.SEX == "2" ? "หญิง" : _null;
      profile.birthDate = BORN == "" ? _null : new Date(BORN);
      profile.dateAppoint = BEGIN_ENTRY_DATE == "" ? _null : new Date(BEGIN_ENTRY_DATE);
      profile.dateStart = BEGIN_ENTRY_DATE == "" ? _null : new Date(BEGIN_ENTRY_DATE);
      profile.dateRetire = BORN == "" ? _null : calculateRetireDate(new Date(BORN));
      profile.dateRetireLaw = BORN == "" ? _null : calculateRetireLaw(new Date(BORN));
      profile.position = item.WORK_LINE_NAME == "" ? null : item.WORK_LINE_NAME;
      profile.posTypeId =
        type_ != null && type_.posTypeName == positionType && type_ ? type_.id : null;
      profile.posLevelId =
        level_ != null && level_.posLevelName == positionLevel && level_ ? level_.id : null;
      profile.amount = item.SALARY == "" ? 0 : Number(item.SALARY);
      profile.createdUserId = request.user.sub;
      profile.createdFullName = request.user.name;
      profile.lastUpdateUserId = request.user.sub;
      profile.lastUpdateFullName = request.user.name;
      profile.createdAt = new Date();
      profile.lastUpdatedAt = new Date();

      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

      await this.profileRepo.save(profile);
    }
    return new HttpSuccess();
  }

  /**
   * @summary ทะเบียนประวัติ ลูกจ้างประจำ
   */
  @Post("uploadProfile-Employee")
  async UploadFileSQL(@Request() request: { user: Record<string, any> }) {
    const existingProfile = await this.profileEmpRepo.find({
      select: ["citizenId"],
    });

    const EMPLOYEE = await this.EMPLOYEERepo.find({
      where: { CIT: Not(In(existingProfile.map((x) => x.citizenId))) },
    });
    let rowCount = 0;
    let _null: any = null;
    let profile: any;
    for await (const item of EMPLOYEE) {
      rowCount++;
      let type_: any = null;
      let level_: any = null;
      profile = null;
      profile = new ProfileEmployee();

      const existingProfile = await this.profileEmpRepo.findOne({
        where: { citizenId: item.CIT.toString() },
      });
      if (existingProfile) {
        profile.id = existingProfile.id;
        continue;
      } else {
        // continue;
      }

      var positionType = "";
      // var positionLevel = 0;
      var positionLevel = "0";
      const workLevel = item.WORK_LEVEL;
      const part1 = workLevel.split("/")[0]; // "ส 2"
      const value2 = part1.split(" ")[1]; // "2"
      if (value2) {
        // positionLevel = parseInt(value2);
        positionLevel = value2;
      }
      if (item.CATEGORY_SAL_CODE == "11") {
        positionType = "บริการพื้นฐาน";
        // positionLevel = "ปฏิบัติงาน";
      } else if (item.CATEGORY_SAL_CODE == "12") {
        positionType = "สนับสนุน";
        // positionLevel = "ชำนาญงาน";
      } else if (item.CATEGORY_SAL_CODE == "13") {
        positionType = "ช่าง";
        // positionLevel = "อาวุโส";
      }
      if (positionType) {
        type_ = await this.posTypeEmpRepo.findOne({
          where: { posTypeName: positionType },
        });
      }
      if (positionLevel) {
        if (type_ == null) {
          level_ = await this.posLevelEmpRepo.findOne({
            where: {
              posLevelName: positionLevel,
            },
          });
        } else {
          level_ = await this.posLevelEmpRepo.findOne({
            where: {
              posLevelName: positionLevel,
              posTypeId: type_.id,
            },
          });
        }
      }

      let BORN = item.BORN;
      if (item.BORN) {
        // const [datePart] = item.BORN.split(" ");
        // const [day, month, year] = datePart.split("/");
        // BORN = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
      }
      let BEGIN_ENTRY_DATE = item.BEGIN_ENTRY_DATE;
      if (item.BEGIN_ENTRY_DATE) {
        // const [datePart] = item.BEGIN_ENTRY_DATE.split(" ");
        // const [day, month, year] = datePart.split("/");
        // BEGIN_ENTRY_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
      }

      profile.citizenId = item.CIT;
      profile.rank =
        item.RANK_NAME == "" ||
        item.RANK_NAME == "นาย" ||
        item.RANK_NAME == "นาง" ||
        item.RANK_NAME == "นางสาว"
          ? null
          : item.RANK_NAME;
      profile.prefix = item.RANK_NAME == "" ? null : item.RANK_NAME;
      profile.prefixMain =
        item.RANK_NAME == "" ||
        (item.RANK_NAME != "นาย" && item.RANK_NAME != "นาง" && item.RANK_NAME != "นางสาว")
          ? null
          : item.RANK_NAME;
      profile.firstName = item.FNAME == "" ? null : item.FNAME;
      profile.lastName = item.LNAME == "" ? null : item.LNAME;
      profile.employeeClass = "PERM";
      profile.gender = item.SEX == "1" ? "ชาย" : item.SEX == "2" ? "หญิง" : _null;
      profile.birthDate = BORN == "" ? _null : new Date(BORN);
      profile.dateAppoint = BEGIN_ENTRY_DATE == "" ? _null : new Date(BEGIN_ENTRY_DATE);
      profile.dateStart = BEGIN_ENTRY_DATE == "" ? _null : new Date(BEGIN_ENTRY_DATE);
      profile.dateRetire = BORN == "" ? _null : calculateRetireDate(new Date(BORN));
      profile.dateRetireLaw = BORN == "" ? _null : calculateRetireLaw(new Date(BORN));
      profile.position = item.WORK_LINE_NAME == "" ? null : item.WORK_LINE_NAME.split(" ")[0];
      profile.posTypeId =
        type_ != null && type_.posTypeName == positionType && type_ ? type_.id : null;
      profile.posLevelId =
        level_ != null && level_.posLevelName == positionLevel && level_ ? level_.id : null;

      profile.amount = item.SALARY == "" ? 0 : Number(item.SALARY);
      profile.salaryLevel = item.SALARY == "" ? null : Number(item.SALARY_LEVEL_CODE);
      profile.createdUserId = request.user.sub;
      profile.createdFullName = request.user.name;
      profile.lastUpdateUserId = request.user.sub;
      profile.lastUpdateFullName = request.user.name;
      profile.createdAt = new Date();
      profile.lastUpdatedAt = new Date();

      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

      await this.profileEmpRepo.save(profile);
    }
    return new HttpSuccess();
  }

  /**
   * @summary ทะเบียนประวัติ ลูกจ้างชั่วคราว
   */
  @Post("uploadProfile-EmployeeTemp")
  async UploadFileSQLTemp(@Request() request: { user: Record<string, any> }) {
    // const existingProfile = await this.profileEmpRepo.find({
    //   where: { employeeClass: "TEMP" },
    //   select: ["citizenId"],
    // });

    const EMPLOYEE = await this.EMPLOYEETEMPRepo.find({
      // where: { CIT: Not(In(existingProfile.map((x) => x.citizenId))) },
    });
    let rowCount = 0;
    let _null: any = null;
    let profile: any;
    for await (const item of EMPLOYEE) {
      rowCount++;
      let type_: any = null;
      let level_: any = null;
      profile = null;
      profile = new ProfileEmployee();

      if (item.CIT.toString() == "1101801164891") {
        // profile.id = existingProfile.id;
        continue;
      }
      const existingProfile = await this.profileEmpRepo.findOne({
        where: { employeeClass: "TEMP", citizenId: item.CIT.toString() },
      });
      if (existingProfile) {
        profile.id = existingProfile.id;
        // continue;
      } else {
        continue;
      }

      var positionType = "";
      // var positionLevel = 0;
      var positionLevel = "0";
      const value2 = item.POSITION_LEVEL;
      // const part1 = workLevel.split("/")[0]; // "ส 2"
      // const value2 = part1.split(" ")[1]; // "2"
      if (value2) {
        // positionLevel = parseInt(value2);
        positionLevel = value2;
      }
      if (item.CATEGORY_SAL_CODE == "11") {
        positionType = "บริการพื้นฐาน";
        // positionLevel = "ปฏิบัติงาน";
      } else if (item.CATEGORY_SAL_CODE == "12") {
        positionType = "สนับสนุน";
        // positionLevel = "ชำนาญงาน";
      } else if (item.CATEGORY_SAL_CODE == "13") {
        positionType = "ช่าง";
        // positionLevel = "อาวุโส";
      }
      if (positionType) {
        type_ = await this.posTypeEmpRepo.findOne({
          where: { posTypeName: positionType },
        });
      }
      if (positionLevel) {
        if (type_ == null) {
          level_ = await this.posLevelEmpRepo.findOne({
            where: {
              posLevelName: positionLevel,
            },
          });
        } else {
          level_ = await this.posLevelEmpRepo.findOne({
            where: {
              posLevelName: positionLevel,
              posTypeId: type_.id,
            },
          });
        }
      }

      let BORN = item.BORN;
      if (item.BORN) {
        // const [datePart] = item.BORN.split(" ");
        // const [day, month, year] = datePart.split("/");
        // BORN = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
      }
      let BEGIN_ENTRY_DATE = item.BEGIN_ENTRY_DATE;
      if (item.BEGIN_ENTRY_DATE) {
        // const [datePart] = item.BEGIN_ENTRY_DATE.split(" ");
        // const [day, month, year] = datePart.split("/");
        // BEGIN_ENTRY_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
      }

      profile.citizenId = item.CIT;
      profile.rank =
        item.RANK_NAME == "" ||
        item.RANK_NAME == "นาย" ||
        item.RANK_NAME == "นาง" ||
        item.RANK_NAME == "นางสาว"
          ? null
          : item.RANK_NAME;
      profile.prefix = item.RANK_NAME == "" ? null : item.RANK_NAME;
      profile.prefixMain =
        item.RANK_NAME == "" ||
        (item.RANK_NAME != "นาย" && item.RANK_NAME != "นาง" && item.RANK_NAME != "นางสาว")
          ? null
          : item.RANK_NAME;
      profile.firstName = item.FNAME == "" ? null : item.FNAME;
      profile.lastName = item.LNAME == "" ? null : item.LNAME;
      profile.employeeClass = "TEMP";
      profile.gender = item.SEX == "1" ? "ชาย" : item.SEX == "2" ? "หญิง" : _null;
      profile.birthDate = BORN == "" ? _null : new Date(BORN);
      profile.dateAppoint = BEGIN_ENTRY_DATE == "" ? _null : new Date(BEGIN_ENTRY_DATE);
      profile.dateStart = BEGIN_ENTRY_DATE == "" ? _null : new Date(BEGIN_ENTRY_DATE);
      profile.dateRetire = BORN == "" ? _null : calculateRetireDate(new Date(BORN));
      profile.dateRetireLaw = BORN == "" ? _null : calculateRetireLaw(new Date(BORN));
      profile.position = item.WORK_LINE_NAME == "" ? null : item.WORK_LINE_NAME.split(" ")[0];
      profile.posTypeId =
        type_ != null && type_.posTypeName == positionType && type_ ? type_.id : null;
      profile.posLevelId =
        level_ != null && level_.posLevelName == positionLevel && level_ ? level_.id : null;

      profile.amount = item.SALARY == "" ? 0 : Number(item.SALARY);
      profile.salaryLevel = item.SALARY == "" ? null : Number(item.SALARY_LEVEL_CODE);
      profile.createdUserId = request.user.sub;
      profile.createdFullName = request.user.name;
      profile.lastUpdateUserId = request.user.sub;
      profile.lastUpdateFullName = request.user.name;
      profile.createdAt = new Date();
      profile.lastUpdatedAt = new Date();

      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

      await this.profileEmpRepo.save(profile);
    }
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ข้าราชการ
   */
  @Post("uploadProfileSalary-Officer")
  async UploadFileSQLSalary(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const batchSize = 200; // เพิ่ม batch size เพื่อประสิทธิภาพที่ดีขึ้น

    // นับจำนวน profiles ทั้งหมดก่อน
    const profileRepo = AppDataSource.getRepository(Profile);
    const totalProfiles = await profileRepo.count();

    console.log(
      `Starting OPTIMIZED batch processing: ${totalProfiles} profiles, batch size: ${batchSize}`,
    );

    for (let offset = 0; offset < totalProfiles; offset += batchSize) {
      const profiles = await profileRepo
        .createQueryBuilder("profile")
        .select(["profile.citizenId", "profile.id"])
        .orderBy("profile.citizenId", "ASC")
        .skip(offset)
        .take(batchSize)
        .getMany();

      console.log(
        `Processing batch ${Math.floor(offset / batchSize) + 1}/${Math.ceil(totalProfiles / batchSize)} - Querying ${profiles.length} profiles`,
      );

      const batchRecords = []; // Array สำหรับเก็บ records ที่จะ batch insert

      // OPTIMIZATION 1: ดึงข้อมูล HR ทั้งหมดของ batch ในครั้งเดียว
      const citizenIds = profiles.map((p) => p.citizenId);
      const allHrData = await this.HR_POSITION_OFFICERRepo.createQueryBuilder("hr")
        .where("hr.CIT IN (:...citizenIds)", { citizenIds })
        .andWhere("hr.FLAG_PERSON_TYPE = :flag", { flag: "1" })
        .orderBy("hr.CIT", "ASC")
        .addOrderBy(
          "CASE WHEN hr.MP_POS_DATE IS NULL OR hr.MP_POS_DATE = '' THEN 1 ELSE 0 END",
          "ASC",
        )
        .addOrderBy("STR_TO_DATE(SUBSTRING_INDEX(hr.MP_POS_DATE, ' ', 1), '%d/%m/%Y')", "ASC")
        .addOrderBy("hr.ORDER_MOVE_POSITION", "ASC")
        .getMany();

      // จัดกลุ่มข้อมูล HR ตาม citizenId
      const hrDataByCitizenId = new Map();
      allHrData.forEach((hr) => {
        if (!hrDataByCitizenId.has(hr.CIT)) {
          hrDataByCitizenId.set(hr.CIT, []);
        }
        hrDataByCitizenId.get(hr.CIT).push(hr);
      });

      // OPTIMIZATION 2: ดึง existing ProfileSalary ทั้งหมดของ batch ในครั้งเดียว
      const allHrIds = allHrData.map((hr) => hr.id.toString());
      const existingSalaries = await this.salaryRepo.find({
        where: { refId: In(allHrIds) },
        select: ["refId"],
      });
      const existingSalaryRefIds = new Set(existingSalaries.map((s) => s.refId));

      for (const _item of profiles) {
        const existingProfile = hrDataByCitizenId.get(_item.citizenId) || [];

        let order = 1;
        for (const item of existingProfile) {
          rowCount++;

          // ใช้ Set lookup แทนการ query database
          if (existingSalaryRefIds.has(item.id.toString())) continue;
          const profileSalary: any = new ProfileSalary();
          profileSalary.profileId = _item.id;
          profileSalary.order = order;
          order = order + 1;
          profileSalary.commandNo = isNaN(item.MP_COMMAND_NUM) ? null : item.MP_COMMAND_NUM;

          // แก้ไข logic การกำหนด commandYear
          if (isNaN(item.CUR_YEAR) || item.CUR_YEAR == null) {
            profileSalary.commandYear = null;
          } else {
            profileSalary.commandYear = item.CUR_YEAR > 2500 ? item.CUR_YEAR - 543 : item.CUR_YEAR;
          }

          let MP_COMMAND_DATE = "";
          if (item.MP_COMMAND_DATE && item.MP_COMMAND_DATE.trim() !== "") {
            try {
              const [datePart] = item.MP_COMMAND_DATE.split(" ");
              const [day, month, year] = datePart.split("/");
              if (day && month && year) {
                MP_COMMAND_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
              }
            } catch (error) {
              console.warn(`Invalid MP_COMMAND_DATE format: ${item.MP_COMMAND_DATE}`);
            }
          }
          let MP_POS_DATE = "";
          if (item.MP_POS_DATE && item.MP_POS_DATE.trim() !== "") {
            try {
              const [datePart] = item.MP_POS_DATE.split(" ");
              const [day, month, year] = datePart.split("/");
              if (day && month && year) {
                MP_POS_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
              }
            } catch (error) {
              console.warn(`Invalid MP_POS_DATE format: ${item.MP_POS_DATE}`);
            }
          }
          profileSalary.commandDateSign =
            MP_COMMAND_DATE == null ? _null : new Date(MP_COMMAND_DATE);
          profileSalary.commandDateAffect = MP_POS_DATE == null ? _null : new Date(MP_POS_DATE);
          if (
            [
              "0",
              "11",
              "22",
              "31",
              "39",
              "45",
              "46",
              "47",
              "49",
              "50",
              "51",
              "60",
              "61",
              "62",
              "99",
            ].includes(item.FLAG_TO_NAME_CODE)
          ) {
            profileSalary.commandCode = "0";
            profileSalary.commandName = "อื่น ๆ";
          } else if (["1", "58"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "1";
            profileSalary.commandName = "บรรจุและแต่งตั้งผู้สอบแข่งขันได้";
          } else if (["23"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "2";
            profileSalary.commandName = "บรรจุและแต่งตั้งผู้ได้รับคัดเลือก";
          } else if (["3", "6", "34", "36", "37"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "3";
            profileSalary.commandName = "แต่งตั้ง ย้าย";
          } else if (["10", "55", "56"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "4";
            profileSalary.commandName = "เลื่อน";
          } else if (["14"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "5";
            profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
          } else if (
            ["8", "20", "24", "25", "43", "44", "52", "66", "67"].includes(item.FLAG_TO_NAME_CODE)
          ) {
            profileSalary.commandCode = "6";
            profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
          } else if (["-"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "7";
            profileSalary.commandName = "เงินพิเศษอื่น ๆ";
          } else if (["38", "40", "53", "54"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "8";
            profileSalary.commandName = "ปรับโครงสร้าง";
          } else if (["12"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "9";
            profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
          } else if (["2", "18"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "10";
            profileSalary.commandName = "บรรจุกลับ";
          } else if (["4", "32", "33"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "11";
            profileSalary.commandName = "รับโอน";
          } else if (["5"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "12";
            profileSalary.commandName = "ให้โอน";
          } else if (["15", "95"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "13";
            profileSalary.commandName = "แก้ไขคำสั่ง";
          } else if (["19"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "14";
            profileSalary.commandName = "ยกเลิกคำสั่ง";
          } else if (["27", "35"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "15";
            profileSalary.commandName = "ลาออกจากราชการ";
          } else if (["13", "17", "21", "28", "29", "30", "59"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "16";
            profileSalary.commandName = "พ้นจากราชการ";
          } else if (["7", "9", "16", "26", "63", "68"].includes(item.FLAG_TO_NAME_CODE)) {
            profileSalary.commandCode = "17";
            profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
          }
          if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ลาศึกษาต่อ") {
            profileSalary.commandCode = "0";
            profileSalary.commandName = "อื่น ๆ";
          } else if (
            item.FLAG_TO_NAME_CODE == null &&
            (item.FLAG_TO_NAME == "เลื่อน 1 ขั้นและเลื่อนระดับ" ||
              item.FLAG_TO_NAME == "เลื่อน 0.5 ขั้นและเลื่อนระดับ")
          ) {
            profileSalary.commandCode = "4";
            profileSalary.commandName = "เลื่อน";
          } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "เลื่อนเงินเดือน") {
            profileSalary.commandCode = "5";
            profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
          } else if (
            item.FLAG_TO_NAME_CODE == null &&
            (item.FLAG_TO_NAME == "ปรับตามบัญชีเงินเดือนใหม่" ||
              item.FLAG_TO_NAME == "เลื่อนเงินเดือน" ||
              item.FLAG_TO_NAME == "ปรับเงินเดือนตาม กพ.")
          ) {
            profileSalary.commandCode = "6";
            profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
          } else if (
            item.FLAG_TO_NAME_CODE == null &&
            item.FLAG_TO_NAME == "แต่งตั้งตามการปรับปรุงโครงฯ"
          ) {
            profileSalary.commandCode = "8";
            profileSalary.commandName = "ปรับโครงสร้าง";
          } else if (
            item.FLAG_TO_NAME_CODE == null &&
            item.FLAG_TO_NAME == "พ้นทดลองปฏิบัติราชการ"
          ) {
            profileSalary.commandCode = "9";
            profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
          } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ให้โอนมา") {
            profileSalary.commandCode = "11";
            profileSalary.commandName = "รับโอน";
          } else if (
            item.FLAG_TO_NAME_CODE == null &&
            item.FLAG_TO_NAME == "โอนไปปฏิบัติราชการที่อื่น"
          ) {
            profileSalary.commandCode = "12";
            profileSalary.commandName = "ให้โอน";
          } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ยกเลิกคำสั่ง") {
            profileSalary.commandCode = "14";
            profileSalary.commandName = "ยกเลิกคำสั่ง";
          } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "รักษาการในตำแหน่ง") {
            profileSalary.commandCode = "17";
            profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
          }
          if (
            (profileSalary.commandCode == null || profileSalary.commandCode == undefined) &&
            (profileSalary.commandName == null || profileSalary.commandName == undefined)
          ) {
            if (
              [
                "อื่นๆ",
                "กลับไปปฏิบัติงานทางต้นสังกัด",
                "เปลี่ยนประเภทข้าราชการ",
                "โอนสับเปลี่ยน",
                "เข้ารับฝึกอบรม",
                "ดูงาน",
                "ศึกษาต่อ",
                "ขยายเวลาเข้ารับการฝึกอบรม",
                "ขยายเวลาศึกษาต่อ",
                "รายงานตัวกลับเข้าปฏิบัติราชการ",
                "ไม่ได้เลื่อนขั้น",
                "ตัดเงินเดือน",
                "ลดขั้นเงินเดือน",
                "ให้ข้าราชการกลับเข้ารับราชการ",
                "ไม่ระบุ",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "0";
              profileSalary.commandName = "อื่น ๆ";
            } else if (
              [
                "บรรจุและแต่งตั้งผู้สอบแข่งขันได้",
                "ทดลองปฎิบัติราชการ",
                "ทดลองปฏิบัติราชการและปรับวุฒิ",
                "บรรจุใหม่",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "1";
              profileSalary.commandName = "บรรจุและแต่งตั้งผู้สอบแข่งขันได้";
            } else if (["บรรจุและแต่งตั้งผู้ได้รับการคัดเลือก"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "2";
              profileSalary.commandName = "บรรจุและแต่งตั้งผู้ได้รับคัดเลือก";
            } else if (
              [
                "แต่งตั้ง (ย้ายสับเปลี่ยน)",
                "แต่งตั้ง (ย้าย)",
                "แต่งตั้ง",
                "เปลี่ยนสายงาน",
                "เปลี่ยนตำแหน่ง",
                "ตัดโอนตำแหน่ง",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "3";
              profileSalary.commandName = "แต่งตั้ง ย้าย";
            } else if (
              ["เลื่อนและแต่งตั้ง", "เลื่อนระดับ", "เลื่อนเงินเดือนและระดับ"].includes(
                item.FLAG_TO_NAME,
              )
            ) {
              profileSalary.commandCode = "4";
              profileSalary.commandName = "เลื่อน";
            } else if (["เลื่อนขั้นเงินเดือน", "เลื่อนเงินเดือน"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "5";
              profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
            } else if (
              [
                "ปรับเงินเดือนตามคุณวุฒิ",
                "ได้รับเงินตอบแทนพิเศษ",
                "เงินเพิ่มการครองชีพชั่วคราว",
                "เลื่อนขั้นเงินเดือนกรณีพิเศษ",
                "ปรับอัตราเงินเดือนตามบัญชีอัตราเงินเดือนใหม่ ท้าย พ.ร.บ. เงินเดือนและเงินประจำตำ",
                "ปรับอัตราเงินเดือนตามพระราชกฤษฎีกา การปรับอัตราเงินเดือนของข้าราชการ",
                "เลื่อนขั้นเงินเดือน (เพิ่มเติม)",
                "ปรับอัตราเงินเดือน",
                "ให้ข้าราชการได้รับเงินเดือนตามคุณวุฒิ",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "6";
              profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
            } else if (["--"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "7";
              profileSalary.commandName = "เงินพิเศษอื่น ๆ";
            } else if (
              ["ปรับโครงสร้าง", "แต่งตั้ง (จัดคนลงกรอบ)", "แต่งตั้งตามแผนอัตรากำลังฯ"].includes(
                item.FLAG_TO_NAME,
              )
            ) {
              profileSalary.commandCode = "8";
              profileSalary.commandName = "ปรับโครงสร้าง";
            } else if (["พ้นทดลองปฏิบัติราชการ"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "9";
              profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
            } else if (
              [
                "บรรจุกลับ",
                "บรรจุกลับข้าราชการ",
                "บรรจุและแต่งตั้งผู้ไปรับราชการทหารกลับเข้ารับราชการ",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "10";
              profileSalary.commandName = "บรรจุกลับ";
            } else if (
              [
                "รับโอนข้าราชการตามกฎหมายอื่น",
                "รับโอนข้าราชการตามกฎหมายอื่น ผู้สอบแข่งขันได้",
                "รับโอนข้าราชการตามกฏหมายอื่น โดยการคัดเลือก",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "11";
              profileSalary.commandName = "รับโอน";
            } else if (["ให้โอน"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "12";
              profileSalary.commandName = "ให้โอน";
            } else if (["แก้ไขคำสั่ง"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "13";
              profileSalary.commandName = "แก้ไขคำสั่ง";
            } else if (["ยกเลิกคำสั่ง"].includes(item.FLAG_TO_NAME)) {
              profileSalary.commandCode = "14";
              profileSalary.commandName = "ยกเลิกคำสั่ง";
            } else if (
              [
                "ลาออกจากราชการ",
                "พ้นจากราชการ/ลาออกจากราชการตามมาตรการพัฒนาและบริหารกำลังคน",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "15";
              profileSalary.commandName = "ลาออกจากราชการ";
            } else if (
              [
                "พ้นจากราชการ/เพื่อไปปฏิบัติราชการทหาร",
                "เกษียณ",
                "ไม่พ้นทดลองปฏิบัติราชการ",
                "พ้นจากราชการ/ให้ออก",
                "พ้นจากราชการ/ไล่ออก",
                "พ้นจากราชการ/เสียชีวิต",
                "พ้นจากราชการ/ปลดออก",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "16";
              profileSalary.commandName = "พ้นจากราชการ";
            } else if (
              [
                "แต่งตั้งข้าราชการรักษาราชการแทน",
                "ช่วยราชการ",
                "รักษาการ",
                "รักษาราชการแทน",
                "มอบหมายให้ปฏิบัติหน้าที่",
                "มอบหมายข้าราชการปฏิบัติหน้าที่แทน",
              ].includes(item.FLAG_TO_NAME)
            ) {
              profileSalary.commandCode = "17";
              profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
            } else {
              profileSalary.commandCode = "0";
              profileSalary.commandName = item.FLAG_TO_NAME;
            }
          }
          profileSalary.posNoAbb = item.POS_NUM_NAME;
          profileSalary.posNo = item.POS_NUM_CODE;
          profileSalary.positionName = item.WORK_LINE_NAME;
          var positionType = _null;
          var positionLevel = _null;
          if (item.MP_CEE == "21") {
            positionType = "ทั่วไป";
            positionLevel = "ปฏิบัติงาน";
          } else if (item.MP_CEE == "22") {
            positionType = "ทั่วไป";
            positionLevel = "ชำนาญงาน";
          } else if (item.MP_CEE == "23") {
            positionType = "ทั่วไป";
            positionLevel = "อาวุโส";
          } else if (item.MP_CEE == "24") {
            positionType = "ทั่วไป";
            positionLevel = "อาวุโสเฉพาะสายงานที่กำหนด";
          } else if (item.MP_CEE == "25") {
            positionType = "ทั่วไป";
            positionLevel = "ทักษะพิเศษ";
          } else if (item.MP_CEE == "26") {
            positionType = "วิชาการ";
            positionLevel = "ปฏิบัติการ";
          } else if (item.MP_CEE == "27") {
            positionType = "วิชาการ";
            positionLevel = "ชำนาญการ";
          } else if (item.MP_CEE == "28") {
            positionType = "วิชาการ";
            positionLevel = "ชำนาญการพิเศษ";
          } else if (item.MP_CEE == "29") {
            positionType = "วิชาการ";
            positionLevel = "เชี่ยวชาญ";
          } else if (item.MP_CEE == "30") {
            positionType = "วิชาการ";
            positionLevel = "ทรงคุณวุฒิ";
          } else if (item.MP_CEE == "31") {
            positionType = "วิชาการ";
            positionLevel = "ทรงคุณวุฒิเฉพาะสายงานที่กำหนด";
          } else if (item.MP_CEE == "32") {
            positionType = "อำนวยการ";
            positionLevel = "ต้น";
          } else if (item.MP_CEE == "33") {
            positionType = "อำนวยการ";
            positionLevel = "สูง";
          } else if (item.MP_CEE == "34") {
            positionType = "บริหาร";
            positionLevel = "ต้น";
          } else if (item.MP_CEE == "35") {
            positionType = "บริหาร";
            positionLevel = "สูง";
          } else {
            profileSalary.positionCee = item.MP_CEE;
          }
          profileSalary.positionType = positionType;
          profileSalary.positionLevel = positionLevel;
          profileSalary.orgRoot = item.DEPARTMENT_NAME;
          profileSalary.orgChild1 = item.DIVISION_NAME;
          profileSalary.orgChild2 = item.SECTION_NAME;
          profileSalary.orgChild3 = item.JOB_NAME;
          if (item.DEPARTMENT_CODE == "50") {
            profileSalary.orgRoot = item.DIVISION_NAME;
            profileSalary.orgChild1 = item.SECTION_NAME;
            profileSalary.orgChild2 = item.JOB_NAME;
          }
          profileSalary.positionExecutive = item.ADMIN_NAME ?? _null;
          profileSalary.amount = isNaN(item.SALARY) ? null : item.SALARY;
          profileSalary.remark = item.REMARK;
          profileSalary.refId = item.id;
          profileSalary.isEntry = false;

          const sal_pos_amount_1: any =
            item.SAL_POS_AMOUNT_1 == null || item.SAL_POS_AMOUNT_1 == ""
              ? _null
              : isNaN(Number(item.SAL_POS_AMOUNT_1))
                ? _null
                : Number(item.SAL_POS_AMOUNT_1);
          const sal_pos_amount_2: any =
            item.SAL_POS_AMOUNT_2 == null || item.SAL_POS_AMOUNT_2 == ""
              ? _null
              : isNaN(Number(item.SAL_POS_AMOUNT_2))
                ? _null
                : Number(item.SAL_POS_AMOUNT_2);
          profileSalary.positionSalaryAmount = sal_pos_amount_1 ?? sal_pos_amount_2;

          const special_amt: any =
            item.SPECIAL_AMT == null || item.SPECIAL_AMT == ""
              ? _null
              : isNaN(Number(item.SPECIAL_AMT))
                ? _null
                : Number(item.SPECIAL_AMT);
          profileSalary.amountSpecial = special_amt;
          profileSalary.posNumCodeSit = item.POS_NUM_CODE_SIT;
          profileSalary.posNumCodeSitAbb = item.POS_NUM_CODE_SIT_ABB;

          profileSalary.createdUserId = request.user.sub;
          profileSalary.createdFullName = request.user.name;
          profileSalary.lastUpdateUserId = request.user.sub;
          profileSalary.lastUpdateFullName = request.user.name;
          profileSalary.createdAt = new Date().toISOString().split("T")[0];
          profileSalary.lastUpdatedAt = new Date().toISOString().split("T")[0];

          // เพิ่มลงใน batch array แทนการ save ทันที
          batchRecords.push(profileSalary);
        }
        order = 1;
      }

      // Batch insert สำหรับ batch นี้
      if (batchRecords.length > 0) {
        try {
          // Debug: ตรวจสอบ NaN values ก่อน save
          const hasNaN = batchRecords.some((record) => {
            return Object.values(record).some((value) => typeof value === "number" && isNaN(value));
          });

          if (hasNaN) {
            console.warn(
              `Warning: Found NaN values in batch ${Math.floor(offset / batchSize) + 1}`,
            );
            // แทนที่ NaN ด้วย null
            batchRecords.forEach((record) => {
              Object.keys(record).forEach((key) => {
                if (typeof record[key] === "number" && isNaN(record[key])) {
                  console.warn(`Replacing NaN in field ${key} with null`);
                  record[key] = null;
                }
              });
            });
          }

          await this.salaryRepo.save(batchRecords);
          console.log(
            `Saved ${batchRecords.length} records for batch ${Math.floor(offset / batchSize) + 1}`,
          );
        } catch (error) {
          console.error(`Error saving batch ${Math.floor(offset / batchSize) + 1}:`, error);
        }
      }
    }

    console.log(`Total processed: ${rowCount} records`);
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ลูกจ้างประจำ
   */
  @Post("uploadProfileSalary-Employee")
  async UploadFileSQLSalaryEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    let sqlStatements: string[] = [];

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      //   .where("profile.citizenId = '3101702379675'")
      // .where({
      //   citizenId: In([
      //     //   "1100600109451",
      //     //   "1209900075508",
      //     //   "1739900231556",
      //     //   "1809900305214",
      //     //   "1920600228762",
      //     //   "3101600963742",
      //     //   "3102401171243",
      //     //   "3120100454406",
      //     //   "3180100306172",
      //     //   "3700100094722",
      //     //   "3809900116957",
      //     "3940900213929",
      //   ]),
      // })
      .leftJoinAndSelect("profile.profileSalary", "profileSalary")
      // .where({employeeClass: "P"})
      .where("profileSalary.id IS NULL")
      // .skip(0)
      // .take(10000)
      .getManyAndCount();
    var _profiles: ProfileSalary[] = [];
    const filePath = path.join(__dirname, "salaryProfile1.csv");
    // CSV Header
    let csvData = `"id","createdAt","createdUserId","lastUpdatedAt","lastUpdateUserId","createdFullName","lastUpdateFullName","profileId","profileEmployeeId","order","commandNo","commandYear","commandDateSign","commandDateAffect","commandCode","commandName","posNoAbb","posNo","positionName","positionType","positionLevel","positionCee","orgRoot","orgChild1","orgChild2","orgChild3","orgChild4","positionExecutive","amount","amountSpecial","positionSalaryAmount","mouthSalaryAmount","remark","dateGovernment","isGovernment","commandId","refId"\n`;

    fs.appendFile(filePath, csvData, (err) => {
      if (err) {
        console.error("Error writing CSV file:", err);
      } else {
        console.log("Salary profiles successfully written to salaryProfile.csv");
      }
    });
    for await (const _item of profiles) {
      const existingProfile = await this.HR_POSITION_EMPLOYEERepo.find({
        where: { CIT: _item.citizenId, FLAG_PERSON_TYPE: "6" },
      });

      // sort ด้วย JavaScript
      existingProfile.sort((a, b) => {
        let dateA = new Date().getTime();
        let dateB = new Date().getTime();

        if (a.MP_POS_DATE) {
          const [datePart] = a.MP_POS_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          dateA = new Date(`${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`).getTime();
        }
        if (b.MP_POS_DATE) {
          const [datePart] = b.MP_POS_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          dateB = new Date(`${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`).getTime();
        }
        if (dateA !== dateB) {
          return dateA - dateB; // ASC
        }
        return a.ORDER_MOVE_POSITION - b.ORDER_MOVE_POSITION; // ASC
      });
      let order = 1;
      for await (const item of existingProfile) {
        rowCount++;
        const profileSalary: any = new ProfileSalary();
        profileSalary.profileEmployeeId = _item.id;
        profileSalary.order = order;
        order = order + 1;
        profileSalary.commandNo = isNaN(item.MP_COMMAND_NUM) ? null : item.MP_COMMAND_NUM;
        profileSalary.commandYear = isNaN(item.MP_COMMAND_NUM)
          ? null
          : item.CUR_YEAR > 2500
            ? item.CUR_YEAR - 543
            : item.CUR_YEAR;

        let MP_COMMAND_DATE = "";
        if (item.MP_COMMAND_DATE) {
          const [datePart] = item.MP_COMMAND_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          MP_COMMAND_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        let MP_POS_DATE = "";
        if (item.MP_POS_DATE) {
          const [datePart] = item.MP_POS_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          MP_POS_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        profileSalary.commandDateSign = MP_COMMAND_DATE == null ? _null : new Date(MP_COMMAND_DATE);
        profileSalary.commandDateAffect = MP_POS_DATE == null ? _null : new Date(MP_POS_DATE);
        if (
          [
            "0",
            "11",
            "22",
            "31",
            "39",
            "45",
            "46",
            "47",
            "49",
            "50",
            "51",
            "60",
            "61",
            "62",
            "99",
          ].includes(item.FLAG_TO_NAME_CODE)
        ) {
          profileSalary.commandCode = "0";
          profileSalary.commandName = "อื่น ๆ";
        } else if (["1", "58"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "1";
          profileSalary.commandName = "บรรจุและแต่งตั้งผู้สอบแข่งขันได้";
        } else if (["23"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "2";
          profileSalary.commandName = "บรรจุและแต่งตั้งผู้ได้รับคัดเลือก";
        } else if (["3", "6", "34", "36", "37"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "3";
          profileSalary.commandName = "แต่งตั้ง ย้าย";
        } else if (["10", "55", "56"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "4";
          profileSalary.commandName = "เลื่อน";
        } else if (["14"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "5";
          profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
        } else if (
          ["8", "20", "24", "25", "43", "44", "52", "66", "67"].includes(item.FLAG_TO_NAME_CODE)
        ) {
          profileSalary.commandCode = "6";
          profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
        } else if (["-"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "7";
          profileSalary.commandName = "เงินพิเศษอื่น ๆ";
        } else if (["38", "40", "53", "54"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "8";
          profileSalary.commandName = "ปรับโครงสร้าง";
        } else if (["12"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "9";
          profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
        } else if (["2", "18"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "10";
          profileSalary.commandName = "บรรจุกลับ";
        } else if (["4", "32", "33"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "11";
          profileSalary.commandName = "รับโอน";
        } else if (["5"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "12";
          profileSalary.commandName = "ให้โอน";
        } else if (["15", "95"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "13";
          profileSalary.commandName = "แก้ไขคำสั่ง";
        } else if (["19"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "14";
          profileSalary.commandName = "ยกเลิกคำสั่ง";
        } else if (["27", "35"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "15";
          profileSalary.commandName = "ลาออกจากราชการ";
        } else if (["13", "17", "21", "28", "29", "30", "59"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "16";
          profileSalary.commandName = "พ้นจากราชการ";
        } else if (["7", "9", "16", "26", "63", "68"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "17";
          profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
        }
        if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ลาศึกษาต่อ") {
          profileSalary.commandCode = "0";
          profileSalary.commandName = "อื่น ๆ";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          (item.FLAG_TO_NAME == "เลื่อน 1 ขั้นและเลื่อนระดับ" ||
            item.FLAG_TO_NAME == "เลื่อน 0.5 ขั้นและเลื่อนระดับ")
        ) {
          profileSalary.commandCode = "4";
          profileSalary.commandName = "เลื่อน";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "เลื่อนเงินเดือน") {
          profileSalary.commandCode = "5";
          profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          (item.FLAG_TO_NAME == "ปรับตามบัญชีเงินเดือนใหม่" ||
            item.FLAG_TO_NAME == "เลื่อนเงินเดือน" ||
            item.FLAG_TO_NAME == "ปรับเงินเดือนตาม กพ.")
        ) {
          profileSalary.commandCode = "6";
          profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          item.FLAG_TO_NAME == "แต่งตั้งตามการปรับปรุงโครงฯ"
        ) {
          profileSalary.commandCode = "8";
          profileSalary.commandName = "ปรับโครงสร้าง";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "พ้นทดลองปฏิบัติราชการ") {
          profileSalary.commandCode = "9";
          profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ให้โอนมา") {
          profileSalary.commandCode = "11";
          profileSalary.commandName = "รับโอน";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          item.FLAG_TO_NAME == "โอนไปปฏิบัติราชการที่อื่น"
        ) {
          profileSalary.commandCode = "12";
          profileSalary.commandName = "ให้โอน";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ยกเลิกคำสั่ง") {
          profileSalary.commandCode = "14";
          profileSalary.commandName = "ยกเลิกคำสั่ง";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "รักษาการในตำแหน่ง") {
          profileSalary.commandCode = "17";
          profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
        }
        if (
          (profileSalary.commandCode == null || profileSalary.commandCode == undefined) &&
          (profileSalary.commandName == null || profileSalary.commandName == undefined)
        ) {
          if (
            [
              "อื่นๆ",
              "กลับไปปฏิบัติงานทางต้นสังกัด",
              "เปลี่ยนประเภทข้าราชการ",
              "โอนสับเปลี่ยน",
              "เข้ารับฝึกอบรม",
              "ดูงาน",
              "ศึกษาต่อ",
              "ขยายเวลาเข้ารับการฝึกอบรม",
              "ขยายเวลาศึกษาต่อ",
              "รายงานตัวกลับเข้าปฏิบัติราชการ",
              "ไม่ได้เลื่อนขั้น",
              "ตัดเงินเดือน",
              "ลดขั้นเงินเดือน",
              "ให้ข้าราชการกลับเข้ารับราชการ",
              "ไม่ระบุ",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "0";
            profileSalary.commandName = "อื่น ๆ";
          } else if (
            [
              "บรรจุและแต่งตั้งผู้สอบแข่งขันได้",
              "ทดลองปฎิบัติราชการ",
              "ทดลองปฏิบัติราชการและปรับวุฒิ",
              "บรรจุใหม่",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "1";
            profileSalary.commandName = "บรรจุและแต่งตั้งผู้สอบแข่งขันได้";
          } else if (["บรรจุและแต่งตั้งผู้ได้รับการคัดเลือก"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "2";
            profileSalary.commandName = "บรรจุและแต่งตั้งผู้ได้รับคัดเลือก";
          } else if (
            [
              "แต่งตั้ง (ย้ายสับเปลี่ยน)",
              "แต่งตั้ง (ย้าย)",
              "แต่งตั้ง",
              "เปลี่ยนสายงาน",
              "เปลี่ยนตำแหน่ง",
              "ตัดโอนตำแหน่ง",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "3";
            profileSalary.commandName = "แต่งตั้ง ย้าย";
          } else if (
            ["เลื่อนและแต่งตั้ง", "เลื่อนระดับ", "เลื่อนเงินเดือนและระดับ"].includes(
              item.FLAG_TO_NAME,
            )
          ) {
            profileSalary.commandCode = "4";
            profileSalary.commandName = "เลื่อน";
          } else if (["เลื่อนขั้นเงินเดือน", "เลื่อนเงินเดือน"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "5";
            profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
          } else if (
            [
              "ปรับเงินเดือนตามคุณวุฒิ",
              "ได้รับเงินตอบแทนพิเศษ",
              "เงินเพิ่มการครองชีพชั่วคราว",
              "เลื่อนขั้นเงินเดือนกรณีพิเศษ",
              "ปรับอัตราเงินเดือนตามบัญชีอัตราเงินเดือนใหม่ ท้าย พ.ร.บ. เงินเดือนและเงินประจำตำ",
              "ปรับอัตราเงินเดือนตามพระราชกฤษฎีกา การปรับอัตราเงินเดือนของข้าราชการ",
              "เลื่อนขั้นเงินเดือน (เพิ่มเติม)",
              "ปรับอัตราเงินเดือน",
              "ให้ข้าราชการได้รับเงินเดือนตามคุณวุฒิ",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "6";
            profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
          } else if (["--"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "7";
            profileSalary.commandName = "เงินพิเศษอื่น ๆ";
          } else if (
            ["ปรับโครงสร้าง", "แต่งตั้ง (จัดคนลงกรอบ)", "แต่งตั้งตามแผนอัตรากำลังฯ"].includes(
              item.FLAG_TO_NAME,
            )
          ) {
            profileSalary.commandCode = "8";
            profileSalary.commandName = "ปรับโครงสร้าง";
          } else if (["พ้นทดลองปฏิบัติราชการ"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "9";
            profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
          } else if (
            [
              "บรรจุกลับ",
              "บรรจุกลับข้าราชการ",
              "บรรจุและแต่งตั้งผู้ไปรับราชการทหารกลับเข้ารับราชการ",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "10";
            profileSalary.commandName = "บรรจุกลับ";
          } else if (
            [
              "รับโอนข้าราชการตามกฎหมายอื่น",
              "รับโอนข้าราชการตามกฎหมายอื่น ผู้สอบแข่งขันได้",
              "รับโอนข้าราชการตามกฏหมายอื่น โดยการคัดเลือก",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "11";
            profileSalary.commandName = "รับโอน";
          } else if (["ให้โอน"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "12";
            profileSalary.commandName = "ให้โอน";
          } else if (["แก้ไขคำสั่ง"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "13";
            profileSalary.commandName = "แก้ไขคำสั่ง";
          } else if (["ยกเลิกคำสั่ง"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "14";
            profileSalary.commandName = "ยกเลิกคำสั่ง";
          } else if (
            [
              "ลาออกจากราชการ",
              "พ้นจากราชการ/ลาออกจากราชการตามมาตรการพัฒนาและบริหารกำลังคน",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "15";
            profileSalary.commandName = "ลาออกจากราชการ";
          } else if (
            [
              "พ้นจากราชการ/เพื่อไปปฏิบัติราชการทหาร",
              "เกษียณ",
              "ไม่พ้นทดลองปฏิบัติราชการ",
              "พ้นจากราชการ/ให้ออก",
              "พ้นจากราชการ/ไล่ออก",
              "พ้นจากราชการ/เสียชีวิต",
              "พ้นจากราชการ/ปลดออก",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "16";
            profileSalary.commandName = "พ้นจากราชการ";
          } else if (
            [
              "แต่งตั้งข้าราชการรักษาราชการแทน",
              "ช่วยราชการ",
              "รักษาการ",
              "รักษาราชการแทน",
              "มอบหมายให้ปฏิบัติหน้าที่",
              "มอบหมายข้าราชการปฏิบัติหน้าที่แทน",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "17";
            profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
          } else {
            profileSalary.commandCode = "0";
            profileSalary.commandName = item.FLAG_TO_NAME;
          }
        }
        profileSalary.posNoAbb = item.POS_NUM_NAME;
        profileSalary.posNo = item.POS_NUM_CODE;
        profileSalary.positionName = item.WORK_LINE_NAME_F;
        var positionType = _null;
        var positionLevel = _null;
        if (item.CATEGORY_SAL_CODE == "11") {
          positionType = "บริการพื้นฐาน";
        } else if (item.CATEGORY_SAL_CODE == "12") {
          positionType = "สนับสนุน";
        } else if (item.CATEGORY_SAL_CODE == "13") {
          positionType = "ช่าง";
        }

        profileSalary.positionType = positionType;
        profileSalary.positionLevel = item.LEVEL_NAME;
        profileSalary.orgRoot = item.DEPARTMENT_NAME;
        profileSalary.orgChild1 = item.DIVISION_NAME;
        profileSalary.orgChild2 = item.SECTION_NAME;
        profileSalary.orgChild3 = item.JOB_NAME;
        if (item.DEPARTMENT_CODE == "50") {
          profileSalary.orgRoot = item.DIVISION_NAME;
          profileSalary.orgChild1 = item.SECTION_NAME;
          profileSalary.orgChild2 = item.JOB_NAME;
        }
        // profileSalary.positionExecutive = item.ADMIN_NAME ?? _null;
        profileSalary.amount = isNaN(item.SALARY) ? null : item.SALARY;
        profileSalary.remark = item.REMARK;
        profileSalary.refId = item.id;
        profileSalary.isEntry = false;
        // const sal_pos_amount_1: any =
        //   item.SAL_POS_AMOUNT_1 == null || item.SAL_POS_AMOUNT_1 == ""
        //     ? _null
        //     : Number(item.SAL_POS_AMOUNT_1);
        // const sal_pos_amount_2: any =
        //   item.SAL_POS_AMOUNT_2 == null || item.SAL_POS_AMOUNT_2 == ""
        //     ? _null
        //     : Number(item.SAL_POS_AMOUNT_2);
        // profileSalary.positionSalaryAmount = sal_pos_amount_1 ?? sal_pos_amount_2;
        const special_amt: any =
          item.SPECIAL_AMT == null || item.SPECIAL_AMT == "" ? _null : Number(item.SPECIAL_AMT);
        profileSalary.amountSpecial = special_amt;
        profileSalary.posNumCodeSit = item.POS_NUM_CODE_SIT;
        profileSalary.posNumCodeSitAbb = item.POS_NUM_CODE_SIT_ABB;

        profileSalary.createdUserId = request.user.sub;
        profileSalary.createdFullName = request.user.name;
        profileSalary.lastUpdateUserId = request.user.sub;
        profileSalary.lastUpdateFullName = request.user.name;
        profileSalary.createdAt = new Date().toISOString().split("T")[0];
        profileSalary.lastUpdatedAt = new Date().toISOString().split("T")[0];
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

        try {
          await this.salaryRepo.save(profileSalary);
        } catch (error) {
          console.error("Error executing function from controller:", item.CIT);
        }
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ลูกจ้างชั่วคราว
   */
  @Post("uploadProfileSalary-EmployeeTemp")
  async UploadFileSQLSalaryEmpTemp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    // let sqlStatements: string[] = [];

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .leftJoinAndSelect("profile.profileSalary", "profileSalary")
      .where({ employeeClass: "TEMP" })
      .where("profileSalary.id IS NULL")
      .getManyAndCount();
    // var _profiles: ProfileSalary[] = [];
    // const filePath = path.join(__dirname, "salaryProfile1.csv");
    // CSV Header
    // let csvData = `"id","createdAt","createdUserId","lastUpdatedAt","lastUpdateUserId","createdFullName","lastUpdateFullName","profileId","profileEmployeeId","order","commandNo","commandYear","commandDateSign","commandDateAffect","commandCode","commandName","posNoAbb","posNo","positionName","positionType","positionLevel","positionCee","orgRoot","orgChild1","orgChild2","orgChild3","orgChild4","positionExecutive","amount","amountSpecial","positionSalaryAmount","mouthSalaryAmount","remark","dateGovernment","isGovernment","commandId","refId"\n`;

    // fs.appendFile(filePath, csvData, (err) => {
    //   if (err) {
    //     console.error("Error writing CSV file:", err);
    //   } else {
    //     console.log("Salary profiles successfully written to salaryProfile.csv");
    //   }
    // });
    for await (const _item of profiles) {
      const existingProfile = await this.HR_POSITION_EMPLOYEETEMPRepo.find({
        where: { CIT: _item.citizenId, FLAG_PERSON_TYPE: "7" },
      });

      // sort ด้วย JavaScript
      existingProfile.sort((a, b) => {
        let dateA = new Date().getTime();
        let dateB = new Date().getTime();

        if (a.MP_POS_DATE) {
          const [datePart] = a.MP_POS_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          dateA = new Date(`${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`).getTime();
        }
        if (b.MP_POS_DATE) {
          const [datePart] = b.MP_POS_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          dateB = new Date(`${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`).getTime();
        }
        if (dateA !== dateB) {
          return dateA - dateB; // ASC
        }
        return a.ORDER_MOVE_POSITION - b.ORDER_MOVE_POSITION; // ASC
      });
      let order = 1;
      for await (const item of existingProfile) {
        rowCount++;
        const profileSalary: any = new ProfileSalary();
        profileSalary.profileEmployeeId = _item.id;
        profileSalary.order = order;
        order = order + 1;
        profileSalary.commandNo = isNaN(item.MP_COMMAND_NUM) ? null : item.MP_COMMAND_NUM;
        profileSalary.commandYear = isNaN(item.MP_COMMAND_NUM)
          ? null
          : item.CUR_YEAR > 2500
            ? item.CUR_YEAR - 543
            : item.CUR_YEAR;

        let MP_COMMAND_DATE = "";
        if (item.MP_COMMAND_DATE) {
          const [datePart] = item.MP_COMMAND_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          MP_COMMAND_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        let MP_POS_DATE = "";
        if (item.MP_POS_DATE) {
          const [datePart] = item.MP_POS_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          MP_POS_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        profileSalary.commandDateSign = MP_COMMAND_DATE == null ? _null : new Date(MP_COMMAND_DATE);
        profileSalary.commandDateAffect = MP_POS_DATE == null ? _null : new Date(MP_POS_DATE);
        if (
          [
            "0",
            "11",
            "22",
            "31",
            "39",
            "45",
            "46",
            "47",
            "49",
            "50",
            "51",
            "60",
            "61",
            "62",
            "99",
          ].includes(item.FLAG_TO_NAME_CODE)
        ) {
          profileSalary.commandCode = "0";
          profileSalary.commandName = "อื่น ๆ";
        } else if (["1", "58"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "1";
          profileSalary.commandName = "บรรจุและแต่งตั้งผู้สอบแข่งขันได้";
        } else if (["23"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "2";
          profileSalary.commandName = "บรรจุและแต่งตั้งผู้ได้รับคัดเลือก";
        } else if (["3", "6", "34", "36", "37"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "3";
          profileSalary.commandName = "แต่งตั้ง ย้าย";
        } else if (["10", "55", "56"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "4";
          profileSalary.commandName = "เลื่อน";
        } else if (["14"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "5";
          profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
        } else if (
          ["8", "20", "24", "25", "43", "44", "52", "66", "67"].includes(item.FLAG_TO_NAME_CODE)
        ) {
          profileSalary.commandCode = "6";
          profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
        } else if (["-"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "7";
          profileSalary.commandName = "เงินพิเศษอื่น ๆ";
        } else if (["38", "40", "53", "54"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "8";
          profileSalary.commandName = "ปรับโครงสร้าง";
        } else if (["12"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "9";
          profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
        } else if (["2", "18"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "10";
          profileSalary.commandName = "บรรจุกลับ";
        } else if (["4", "32", "33"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "11";
          profileSalary.commandName = "รับโอน";
        } else if (["5"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "12";
          profileSalary.commandName = "ให้โอน";
        } else if (["15", "95"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "13";
          profileSalary.commandName = "แก้ไขคำสั่ง";
        } else if (["19"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "14";
          profileSalary.commandName = "ยกเลิกคำสั่ง";
        } else if (["27", "35"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "15";
          profileSalary.commandName = "ลาออกจากราชการ";
        } else if (["13", "17", "21", "28", "29", "30", "59"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "16";
          profileSalary.commandName = "พ้นจากราชการ";
        } else if (["7", "9", "16", "26", "63", "68"].includes(item.FLAG_TO_NAME_CODE)) {
          profileSalary.commandCode = "17";
          profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
        }
        if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ลาศึกษาต่อ") {
          profileSalary.commandCode = "0";
          profileSalary.commandName = "อื่น ๆ";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          (item.FLAG_TO_NAME == "เลื่อน 1 ขั้นและเลื่อนระดับ" ||
            item.FLAG_TO_NAME == "เลื่อน 0.5 ขั้นและเลื่อนระดับ")
        ) {
          profileSalary.commandCode = "4";
          profileSalary.commandName = "เลื่อน";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "เลื่อนเงินเดือน") {
          profileSalary.commandCode = "5";
          profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          (item.FLAG_TO_NAME == "ปรับตามบัญชีเงินเดือนใหม่" ||
            item.FLAG_TO_NAME == "เลื่อนเงินเดือน" ||
            item.FLAG_TO_NAME == "ปรับเงินเดือนตาม กพ.")
        ) {
          profileSalary.commandCode = "6";
          profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          item.FLAG_TO_NAME == "แต่งตั้งตามการปรับปรุงโครงฯ"
        ) {
          profileSalary.commandCode = "8";
          profileSalary.commandName = "ปรับโครงสร้าง";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "พ้นทดลองปฏิบัติราชการ") {
          profileSalary.commandCode = "9";
          profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ให้โอนมา") {
          profileSalary.commandCode = "11";
          profileSalary.commandName = "รับโอน";
        } else if (
          item.FLAG_TO_NAME_CODE == null &&
          item.FLAG_TO_NAME == "โอนไปปฏิบัติราชการที่อื่น"
        ) {
          profileSalary.commandCode = "12";
          profileSalary.commandName = "ให้โอน";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "ยกเลิกคำสั่ง") {
          profileSalary.commandCode = "14";
          profileSalary.commandName = "ยกเลิกคำสั่ง";
        } else if (item.FLAG_TO_NAME_CODE == null && item.FLAG_TO_NAME == "รักษาการในตำแหน่ง") {
          profileSalary.commandCode = "17";
          profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
        }
        if (
          (profileSalary.commandCode == null || profileSalary.commandCode == undefined) &&
          (profileSalary.commandName == null || profileSalary.commandName == undefined)
        ) {
          if (
            [
              "อื่นๆ",
              "กลับไปปฏิบัติงานทางต้นสังกัด",
              "เปลี่ยนประเภทข้าราชการ",
              "โอนสับเปลี่ยน",
              "เข้ารับฝึกอบรม",
              "ดูงาน",
              "ศึกษาต่อ",
              "ขยายเวลาเข้ารับการฝึกอบรม",
              "ขยายเวลาศึกษาต่อ",
              "รายงานตัวกลับเข้าปฏิบัติราชการ",
              "ไม่ได้เลื่อนขั้น",
              "ตัดเงินเดือน",
              "ลดขั้นเงินเดือน",
              "ให้ข้าราชการกลับเข้ารับราชการ",
              "ไม่ระบุ",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "0";
            profileSalary.commandName = "อื่น ๆ";
          } else if (
            [
              "บรรจุและแต่งตั้งผู้สอบแข่งขันได้",
              "ทดลองปฎิบัติราชการ",
              "ทดลองปฏิบัติราชการและปรับวุฒิ",
              "บรรจุใหม่",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "1";
            profileSalary.commandName = "บรรจุและแต่งตั้งผู้สอบแข่งขันได้";
          } else if (["บรรจุและแต่งตั้งผู้ได้รับการคัดเลือก"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "2";
            profileSalary.commandName = "บรรจุและแต่งตั้งผู้ได้รับคัดเลือก";
          } else if (
            [
              "แต่งตั้ง (ย้ายสับเปลี่ยน)",
              "แต่งตั้ง (ย้าย)",
              "แต่งตั้ง",
              "เปลี่ยนสายงาน",
              "เปลี่ยนตำแหน่ง",
              "ตัดโอนตำแหน่ง",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "3";
            profileSalary.commandName = "แต่งตั้ง ย้าย";
          } else if (
            ["เลื่อนและแต่งตั้ง", "เลื่อนระดับ", "เลื่อนเงินเดือนและระดับ"].includes(
              item.FLAG_TO_NAME,
            )
          ) {
            profileSalary.commandCode = "4";
            profileSalary.commandName = "เลื่อน";
          } else if (["เลื่อนขั้นเงินเดือน", "เลื่อนเงินเดือน"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "5";
            profileSalary.commandName = "เลื่อนเงินเดือนตามปกติ";
          } else if (
            [
              "ปรับเงินเดือนตามคุณวุฒิ",
              "ได้รับเงินตอบแทนพิเศษ",
              "เงินเพิ่มการครองชีพชั่วคราว",
              "เลื่อนขั้นเงินเดือนกรณีพิเศษ",
              "ปรับอัตราเงินเดือนตามบัญชีอัตราเงินเดือนใหม่ ท้าย พ.ร.บ. เงินเดือนและเงินประจำตำ",
              "ปรับอัตราเงินเดือนตามพระราชกฤษฎีกา การปรับอัตราเงินเดือนของข้าราชการ",
              "เลื่อนขั้นเงินเดือน (เพิ่มเติม)",
              "ปรับอัตราเงินเดือน",
              "ให้ข้าราชการได้รับเงินเดือนตามคุณวุฒิ",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "6";
            profileSalary.commandName = "เลื่อนเงินเดือนกรณีอื่น ๆ";
          } else if (["--"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "7";
            profileSalary.commandName = "เงินพิเศษอื่น ๆ";
          } else if (
            ["ปรับโครงสร้าง", "แต่งตั้ง (จัดคนลงกรอบ)", "แต่งตั้งตามแผนอัตรากำลังฯ"].includes(
              item.FLAG_TO_NAME,
            )
          ) {
            profileSalary.commandCode = "8";
            profileSalary.commandName = "ปรับโครงสร้าง";
          } else if (["พ้นทดลองปฏิบัติราชการ"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "9";
            profileSalary.commandName = "พ้นทดลองปฏิบัติราชการ";
          } else if (
            [
              "บรรจุกลับ",
              "บรรจุกลับข้าราชการ",
              "บรรจุและแต่งตั้งผู้ไปรับราชการทหารกลับเข้ารับราชการ",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "10";
            profileSalary.commandName = "บรรจุกลับ";
          } else if (
            [
              "รับโอนข้าราชการตามกฎหมายอื่น",
              "รับโอนข้าราชการตามกฎหมายอื่น ผู้สอบแข่งขันได้",
              "รับโอนข้าราชการตามกฏหมายอื่น โดยการคัดเลือก",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "11";
            profileSalary.commandName = "รับโอน";
          } else if (["ให้โอน"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "12";
            profileSalary.commandName = "ให้โอน";
          } else if (["แก้ไขคำสั่ง"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "13";
            profileSalary.commandName = "แก้ไขคำสั่ง";
          } else if (["ยกเลิกคำสั่ง"].includes(item.FLAG_TO_NAME)) {
            profileSalary.commandCode = "14";
            profileSalary.commandName = "ยกเลิกคำสั่ง";
          } else if (
            [
              "ลาออกจากราชการ",
              "พ้นจากราชการ/ลาออกจากราชการตามมาตรการพัฒนาและบริหารกำลังคน",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "15";
            profileSalary.commandName = "ลาออกจากราชการ";
          } else if (
            [
              "พ้นจากราชการ/เพื่อไปปฏิบัติราชการทหาร",
              "เกษียณ",
              "ไม่พ้นทดลองปฏิบัติราชการ",
              "พ้นจากราชการ/ให้ออก",
              "พ้นจากราชการ/ไล่ออก",
              "พ้นจากราชการ/เสียชีวิต",
              "พ้นจากราชการ/ปลดออก",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "16";
            profileSalary.commandName = "พ้นจากราชการ";
          } else if (
            [
              "แต่งตั้งข้าราชการรักษาราชการแทน",
              "ช่วยราชการ",
              "รักษาการ",
              "รักษาราชการแทน",
              "มอบหมายให้ปฏิบัติหน้าที่",
              "มอบหมายข้าราชการปฏิบัติหน้าที่แทน",
            ].includes(item.FLAG_TO_NAME)
          ) {
            profileSalary.commandCode = "17";
            profileSalary.commandName = "รักษาราชการ, ช่วยราชการ";
          } else {
            profileSalary.commandCode = "0";
            profileSalary.commandName = item.FLAG_TO_NAME;
          }
        }
        profileSalary.posNoAbb = item.POS_NUM_NAME;
        profileSalary.posNo = item.POS_NUM_CODE;
        profileSalary.positionName = item.WORK_LINE_NAME_F;
        var positionType = _null;
        var positionLevel = _null;
        if (item.CATEGORY_SAL_CODE == "11") {
          positionType = "บริการพื้นฐาน";
        } else if (item.CATEGORY_SAL_CODE == "12") {
          positionType = "สนับสนุน";
        } else if (item.CATEGORY_SAL_CODE == "13") {
          positionType = "ช่าง";
        }

        profileSalary.positionType = positionType;
        profileSalary.positionLevel = item.LEVEL_NAME;
        profileSalary.orgRoot = item.DEPARTMENT_NAME;
        profileSalary.orgChild1 = item.DIVISION_NAME;
        profileSalary.orgChild2 = item.SECTION_NAME;
        profileSalary.orgChild3 = item.JOB_NAME;
        if (item.DEPARTMENT_CODE == "50") {
          profileSalary.orgRoot = item.DIVISION_NAME;
          profileSalary.orgChild1 = item.SECTION_NAME;
          profileSalary.orgChild2 = item.JOB_NAME;
        }
        // profileSalary.positionExecutive = item.ADMIN_NAME ?? _null;
        profileSalary.amount = isNaN(item.SALARY) ? null : item.SALARY;
        profileSalary.remark = item.REMARK;
        profileSalary.refId = item.id;
        profileSalary.isEntry = false;
        // const sal_pos_amount_1: any =
        //   item.SAL_POS_AMOUNT_1 == null || item.SAL_POS_AMOUNT_1 == ""
        //     ? _null
        //     : Number(item.SAL_POS_AMOUNT_1);
        // const sal_pos_amount_2: any =
        //   item.SAL_POS_AMOUNT_2 == null || item.SAL_POS_AMOUNT_2 == ""
        //     ? _null
        //     : Number(item.SAL_POS_AMOUNT_2);
        // profileSalary.positionSalaryAmount = sal_pos_amount_1 ?? sal_pos_amount_2;
        // const special_amt: any =
        //   item.SPECIAL_AMT == null || item.SPECIAL_AMT == "" ? _null : Number(item.SPECIAL_AMT);
        // profileSalary.amountSpecial = special_amt;
        profileSalary.posNumCodeSit = item.POS_NUM_CODE_SIT;
        profileSalary.posNumCodeSitAbb = item.POS_NUM_CODE_SIT_ABB;

        profileSalary.createdUserId = request.user.sub;
        profileSalary.createdFullName = request.user.name;
        profileSalary.lastUpdateUserId = request.user.sub;
        profileSalary.lastUpdateFullName = request.user.name;
        profileSalary.createdAt = new Date().toISOString().split("T")[0];
        profileSalary.lastUpdatedAt = new Date().toISOString().split("T")[0];
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

        try {
          await this.salaryRepo.save(profileSalary);
        } catch (error) {
          console.error("Error executing function from controller:", item.CIT);
        }
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ครอบครัว ข้าราชการ
   */
  @Post("uploadProfileFamily-Officer")
  async UploadFileSQLFamily(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let fathers: any = [];
    let mothers: any = [];
    let couples: any = [];

    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      //   .skip(0)
      //   .take(1000)
      .getManyAndCount();
    // for (var i = 1; i <= total / BATCH_SIZE; i++) {
    //   const profiles = await AppDataSource.getRepository(Profile)
    //     .createQueryBuilder("profile")
    //     .select(["profile.citizenId", "profile.id"])
    //     .orderBy("profile.citizenId", "ASC")
    //     .skip((i - 1) * BATCH_SIZE)
    //     .take(BATCH_SIZE)
    //     .getMany();

    for await (const _item of profiles) {
      const existingProfile = await this.HR_PERSONAL_OFFICER_FAMILYRepo.findOne({
        where: { CIT: _item.citizenId },
        select: [
          "CIT",
          "FATHER_RANK_NAME",
          "FATHER_FNAME",
          "FATHER_LNAME",
          "MOTHER_RANK_NAME",
          "MOTHER_FNAME",
          "MOTHER_LNAME",
          "SPOUSE_RANK_NAME",
          "SPOUSE_FNAME",
          "SPOUSE_LNAME",
          "SPOUSE_ID",
          "MARRIAGE_STATE",
        ],
      });
      if (!existingProfile) {
        continue;
      }

      rowCount++;
      const profileFather = new ProfileFamilyFather();
      const profileMother = new ProfileFamilyMother();
      const profileCouple = new ProfileFamilyCouple();

      profileFather.profileId = _item.id;
      profileFather.fatherPrefix = existingProfile.FATHER_RANK_NAME ?? "";
      profileFather.fatherFirstName = existingProfile.FATHER_FNAME ?? "";
      profileFather.fatherLastName = existingProfile.FATHER_LNAME ?? "";
      profileFather.createdUserId = request.user.sub;
      profileFather.createdFullName = request.user.name;
      profileFather.lastUpdateUserId = request.user.sub;
      profileFather.lastUpdateFullName = request.user.name;
      profileFather.createdAt = new Date();
      profileFather.lastUpdatedAt = new Date();

      profileMother.profileId = _item.id;
      profileMother.motherPrefix = existingProfile.MOTHER_RANK_NAME ?? "";
      profileMother.motherFirstName = existingProfile.MOTHER_FNAME ?? "";
      profileMother.motherLastName = existingProfile.MOTHER_LNAME ?? "";
      profileMother.createdUserId = request.user.sub;
      profileMother.createdFullName = request.user.name;
      profileMother.lastUpdateUserId = request.user.sub;
      profileMother.lastUpdateFullName = request.user.name;
      profileMother.createdAt = new Date();
      profileMother.lastUpdatedAt = new Date();

      profileCouple.profileId = _item.id;
      profileCouple.couplePrefix = existingProfile.SPOUSE_RANK_NAME ?? "";
      profileCouple.coupleFirstName = existingProfile.SPOUSE_FNAME ?? "";
      profileCouple.coupleLastName = existingProfile.SPOUSE_LNAME ?? "";
      profileCouple.coupleCitizenId = existingProfile.SPOUSE_ID ?? "";
      profileCouple.relationship =
        existingProfile.MARRIAGE_STATE == "1"
          ? "โสด"
          : existingProfile.MARRIAGE_STATE == "2"
            ? "สมรส"
            : existingProfile.MARRIAGE_STATE == "3"
              ? "หย่าร้าง"
              : existingProfile.MARRIAGE_STATE == "4"
                ? "หม้าย"
                : "-";
      //   profileCouple.coupleLive = existingProfile.LIFE_SPOUSE;
      profileCouple.createdUserId = request.user.sub;
      profileCouple.createdFullName = request.user.name;
      profileCouple.lastUpdateUserId = request.user.sub;
      profileCouple.lastUpdateFullName = request.user.name;
      profileCouple.createdAt = new Date();
      profileCouple.lastUpdatedAt = new Date();

      // fathers.push(profileFather);
      // mothers.push(profileMother);
      // couples.push(profileCouple);
      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

      //   if (fathers.length === BATCH_SIZE) {
      await this.profileFamilyFatherRepository.save(profileFather);
      //     fathers = await [];
      //   }
      //   if (mothers.length === BATCH_SIZE) {
      await this.profileFamilyMotherRepository.save(profileMother);
      //     mothers = await [];
      //   }
      //   if (couples.length === BATCH_SIZE) {
      await this.profileFamilyCoupleRepository.save(profileCouple);
      //     couples = await [];
      //   }
    }
    // }
    // console.log(rowCount);

    // await Promise.all([
    //   this.profileFamilyFatherRepository.save(fathers),
    //   this.profileFamilyMotherRepository.save(mothers),
    //   this.profileFamilyCoupleRepository.save(couples),
    // ]);

    return new HttpSuccess();
  }

  /**
   * @summary ครอบครัว ลูกจ้างประจำ
   */
  @Post("uploadProfileFamily-Employee")
  async UploadFileSQLFamilyEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let fathers: any = [];
    let mothers: any = [];
    let couples: any = [];

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    // for (var i = 1; i <= total / BATCH_SIZE; i++) {
    //   const profiles = await AppDataSource.getRepository(ProfileEmployee)
    //     .createQueryBuilder("profile")
    //     .select(["profile.citizenId", "profile.id"])
    //     .orderBy("profile.citizenId", "ASC")
    //     .skip((i - 1) * BATCH_SIZE)
    //     .take(BATCH_SIZE)
    //     .getMany();

    for await (const _item of profiles) {
      const existingProfile = await this.HR_PERSONAL_EMP_FAMILYRepo.findOne({
        where: { CIT: _item.citizenId },
        select: [
          "CIT",
          "FATHER_RANK_NAME",
          "FATHER_FNAME",
          "FATHER_LNAME",
          "MOTHER_RANK_NAME",
          "MOTHER_FNAME",
          "MOTHER_LNAME",
          "SPOUSE_RANK_NAME",
          "SPOUSE_FNAME",
          "SPOUSE_LNAME",
          "SPOUSE_ID",
          "MARRIAGE_STATE",
        ],
      });
      if (!existingProfile) {
        continue;
      }

      rowCount++;
      const profileFather = new ProfileFamilyFather();
      const profileMother = new ProfileFamilyMother();
      const profileCouple = new ProfileFamilyCouple();

      profileFather.profileEmployeeId = _item.id;
      profileFather.fatherPrefix = existingProfile.FATHER_RANK_NAME ?? "";
      profileFather.fatherFirstName = existingProfile.FATHER_FNAME ?? "";
      profileFather.fatherLastName = existingProfile.FATHER_LNAME ?? "";
      profileFather.createdUserId = request.user.sub;
      profileFather.createdFullName = request.user.name;
      profileFather.lastUpdateUserId = request.user.sub;
      profileFather.lastUpdateFullName = request.user.name;
      profileFather.createdAt = new Date();
      profileFather.lastUpdatedAt = new Date();

      profileMother.profileEmployeeId = _item.id;
      profileMother.motherPrefix = existingProfile.MOTHER_RANK_NAME ?? "";
      profileMother.motherFirstName = existingProfile.MOTHER_FNAME ?? "";
      profileMother.motherLastName = existingProfile.MOTHER_LNAME ?? "";
      profileMother.createdUserId = request.user.sub;
      profileMother.createdFullName = request.user.name;
      profileMother.lastUpdateUserId = request.user.sub;
      profileMother.lastUpdateFullName = request.user.name;
      profileMother.createdAt = new Date();
      profileMother.lastUpdatedAt = new Date();

      profileCouple.profileEmployeeId = _item.id;
      profileCouple.couplePrefix = existingProfile.SPOUSE_RANK_NAME ?? "";
      profileCouple.coupleFirstName = existingProfile.SPOUSE_FNAME ?? "";
      profileCouple.coupleLastName = existingProfile.SPOUSE_LNAME ?? "";
      profileCouple.coupleCitizenId = existingProfile.SPOUSE_ID ?? "";
      profileCouple.relationship =
        existingProfile.MARRIAGE_STATE == "1"
          ? "โสด"
          : existingProfile.MARRIAGE_STATE == "2"
            ? "สมรส"
            : existingProfile.MARRIAGE_STATE == "3"
              ? "หย่าร้าง"
              : existingProfile.MARRIAGE_STATE == "4"
                ? "หม้าย"
                : "-";
      //   profileCouple.coupleLive = existingProfile.LIFE_SPOUSE;
      profileCouple.createdUserId = request.user.sub;
      profileCouple.createdFullName = request.user.name;
      profileCouple.lastUpdateUserId = request.user.sub;
      profileCouple.lastUpdateFullName = request.user.name;
      profileCouple.createdAt = new Date();
      profileCouple.lastUpdatedAt = new Date();

      // fathers.push(profileFather);
      // mothers.push(profileMother);
      // couples.push(profileCouple);
      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

      // if (fathers.length === BATCH_SIZE) {
      await this.profileFamilyFatherRepository.save(profileFather);
      //   fathers = await [];
      // }
      // if (mothers.length === BATCH_SIZE) {
      await this.profileFamilyMotherRepository.save(profileMother);
      //   mothers = await [];
      // }
      // if (couples.length === BATCH_SIZE) {
      await this.profileFamilyCoupleRepository.save(profileCouple);
      //   couples = await [];
      // }
    }
    // }
    // console.log(rowCount);

    // await Promise.all([
    //   this.profileFamilyFatherRepository.save(fathers),
    //   this.profileFamilyMotherRepository.save(mothers),
    //   this.profileFamilyCoupleRepository.save(couples),
    // ]);

    return new HttpSuccess();
  }

  /**
   * @summary ครอบครัว ลูกจ้างชั่วคราว
   */
  @Post("uploadProfileFamily-EmployeeTemp")
  async UploadFileSQLFamilyEmpTemp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let fathers: any = [];
    let mothers: any = [];
    let couples: any = [];

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .where({ employeeClass: "TEMP" })
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    // for (var i = 1; i <= total / BATCH_SIZE; i++) {
    //   const profiles = await AppDataSource.getRepository(ProfileEmployee)
    //     .createQueryBuilder("profile")
    //     .select(["profile.citizenId", "profile.id"])
    //     .orderBy("profile.citizenId", "ASC")
    //     .skip((i - 1) * BATCH_SIZE)
    //     .take(BATCH_SIZE)
    //     .getMany();

    for await (const _item of profiles) {
      const existingProfile = await this.HR_PERSONAL_EMPTEMP_FAMILYRepo.findOne({
        where: { CIT: _item.citizenId },
        select: [
          "CIT",
          "FATHER_RANK_NAME",
          "FATHER_FNAME",
          "FATHER_LNAME",
          "MOTHER_RANK_NAME",
          "MOTHER_FNAME",
          "MOTHER_LNAME",
          "SPOUSE_RANK_NAME",
          "SPOUSE_FNAME",
          "SPOUSE_LNAME",
          "SPOUSE_ID",
          "MARRIAGE_STATE",
        ],
      });
      if (!existingProfile) {
        continue;
      }

      rowCount++;
      const profileFather = new ProfileFamilyFather();
      const profileMother = new ProfileFamilyMother();
      const profileCouple = new ProfileFamilyCouple();

      profileFather.profileEmployeeId = _item.id;
      profileFather.fatherPrefix = existingProfile.FATHER_RANK_NAME ?? "";
      profileFather.fatherFirstName = existingProfile.FATHER_FNAME ?? "";
      profileFather.fatherLastName = existingProfile.FATHER_LNAME ?? "";
      profileFather.createdUserId = request.user.sub;
      profileFather.createdFullName = request.user.name;
      profileFather.lastUpdateUserId = request.user.sub;
      profileFather.lastUpdateFullName = request.user.name;
      profileFather.createdAt = new Date();
      profileFather.lastUpdatedAt = new Date();

      profileMother.profileEmployeeId = _item.id;
      profileMother.motherPrefix = existingProfile.MOTHER_RANK_NAME ?? "";
      profileMother.motherFirstName = existingProfile.MOTHER_FNAME ?? "";
      profileMother.motherLastName = existingProfile.MOTHER_LNAME ?? "";
      profileMother.createdUserId = request.user.sub;
      profileMother.createdFullName = request.user.name;
      profileMother.lastUpdateUserId = request.user.sub;
      profileMother.lastUpdateFullName = request.user.name;
      profileMother.createdAt = new Date();
      profileMother.lastUpdatedAt = new Date();

      profileCouple.profileEmployeeId = _item.id;
      profileCouple.couplePrefix = existingProfile.SPOUSE_RANK_NAME ?? "";
      profileCouple.coupleFirstName = existingProfile.SPOUSE_FNAME ?? "";
      profileCouple.coupleLastName = existingProfile.SPOUSE_LNAME ?? "";
      profileCouple.coupleCitizenId = existingProfile.SPOUSE_ID ?? "";
      profileCouple.relationship =
        existingProfile.MARRIAGE_STATE == "1"
          ? "โสด"
          : existingProfile.MARRIAGE_STATE == "2"
            ? "สมรส"
            : existingProfile.MARRIAGE_STATE == "3"
              ? "หย่าร้าง"
              : existingProfile.MARRIAGE_STATE == "4"
                ? "หม้าย"
                : "-";
      //   profileCouple.coupleLive = existingProfile.LIFE_SPOUSE;
      profileCouple.createdUserId = request.user.sub;
      profileCouple.createdFullName = request.user.name;
      profileCouple.lastUpdateUserId = request.user.sub;
      profileCouple.lastUpdateFullName = request.user.name;
      profileCouple.createdAt = new Date();
      profileCouple.lastUpdatedAt = new Date();

      // fathers.push(profileFather);
      // mothers.push(profileMother);
      // couples.push(profileCouple);
      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);

      // if (fathers.length === BATCH_SIZE) {
      await this.profileFamilyFatherRepository.save(profileFather);
      //   fathers = await [];
      // }
      // if (mothers.length === BATCH_SIZE) {
      await this.profileFamilyMotherRepository.save(profileMother);
      //   mothers = await [];
      // }
      // if (couples.length === BATCH_SIZE) {
      await this.profileFamilyCoupleRepository.save(profileCouple);
      //   couples = await [];
      // }
    }
    // }
    // console.log(rowCount);

    // await Promise.all([
    //   this.profileFamilyFatherRepository.save(fathers),
    //   this.profileFamilyMotherRepository.save(mothers),
    //   this.profileFamilyCoupleRepository.save(couples),
    // ]);

    return new HttpSuccess();
  }

  /**
   * @summary Import Education
   */
  @Post("ImportEducation")
  @UseInterceptors(FileInterceptor("file"))
  async UploadFileSQLEducationCode(
    @UploadedFile() file: Express.Multer.File,
    @Request() request: { user: Record<string, any> },
  ) {
    const workbook = xlsx.read(file.buffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const getExcel = xlsx.utils.sheet_to_json(sheet);
    let educationMis_: any = [];

    await Promise.all(
      getExcel.map(async (item: any) => {
        const educationMis = new EducationMis();
        educationMis.EDUCATION_CODE = item.EDUCATION_CODE;
        educationMis.EDUCATION_NAME = item.EDUCATION_NAME;
        educationMis.EDUCATION_ABB_NAME = item.EDUCATION_ABB_NAME;
        educationMis.createdUserId = request.user.sub;
        educationMis.createdFullName = request.user.name;
        educationMis.lastUpdateUserId = request.user.sub;
        educationMis.lastUpdateFullName = request.user.name;
        educationMis.createdAt = new Date();
        educationMis.lastUpdatedAt = new Date();
        educationMis_.push(educationMis);
      }),
    );
    await this.educationMisRepo.save(educationMis_);
    return new HttpSuccess(educationMis_);
  }

  /**
   * @summary ประวัติการศึกษา ข้าราชการ
   */
  @Post("uploadProfileEducation-Officer")
  async UploadFileSQLEducation(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let educations: any = [];
    let _null: any = null;
    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    // for (var i = 1; i <= 2; i++) {
    //   const profiles = await AppDataSource.getRepository(Profile)
    //     .createQueryBuilder("profile")
    //     .select(["profile.citizenId", "profile.id"])
    //     .orderBy("profile.citizenId", "ASC")
    //     .skip((i - 1) * 5)
    //     .take(5)
    //     .getMany();

    for (const _item of profiles) {
      const existingProfile = await this.HR_EDUCATIONRepo.find({
        where: { CIT: _item.citizenId },
        // select: [
        //   "CIT",
        //   "EDUCATION_CODE",
        //   "START_EDUCATION_YEAR",
        //   "EDUCATION_YEAR",
        //   "INSTITUE",
        //   "EDUCATION_SEQ",
        // ],
        order: { EDUCATION_SEQ: "ASC" },
      });

      const educationLevel = await this.profileEducationRepo.findOne({
        select: ["id", "level", "profileId", "isDeleted"],
        where: { profileId: _item.id, isDeleted: false },
        order: { level: "DESC" },
      });

      // educations = await [];
      for (const item of existingProfile) {
        rowCount++;
        const education = new ProfileEducation();
        const educationCode = await this.educationMisRepo.findOne({
          where: { EDUCATION_CODE: item.EDUCATION_CODE },
        });
        const hrMajorCode = await this.HR_MAJOR_CODERepo.findOne({
          where: { MAJOR_CODE: item.MAJOR_CODE },
        });
        const hrFundCourseCode = await this.HR_FUND_COURSE_CODERepo.findOne({
          where: { FUND_COURSE_CODE: item.FUND_COURSE_CODE },
        });

        // let startDate = item.START_EDUCATION_YEAR
        //   ? Extension.ConvertToDateTimeV2(item.START_EDUCATION_YEAR)
        //   : _null;
        let startDate =
          item.START_EDUCATION_YEAR != "" && Number(item.START_EDUCATION_YEAR) > 2500
            ? new Date(Number(item.START_EDUCATION_YEAR) - 543, 0, 1)
            : _null;

        // let endDate = item.EDUCATION_YEAR
        //   ? Extension.ConvertToDateTimeV2(item.EDUCATION_YEAR)
        //   : _null;
        let endDate =
          item.EDUCATION_YEAR != "" && Number(item.EDUCATION_YEAR) > 2500
            ? new Date(Number(item.EDUCATION_YEAR) - 543, 0, 1)
            : _null;
        if (item.FLAG_EDUCATION == "1") {
          const checkData = await this.HR_EDUCATIONRepo.find({
            where: { CIT: _item.citizenId, FLAG_EDUCATION: "1" },
            order: { EDUCATION_SEQ: "DESC" },
          });
          if ((checkData.length > 1 && checkData[0].id == item.id) || checkData.length == 1) {
            education.isEducation = true;
            const findIsHigh = await this.HR_EDUCATIONRepo.findOne({
              where: { CIT: _item.citizenId, FLAG_EDUCATION: "3" },
            });
            if (findIsHigh == null) {
              education.isHigh = true;
            }
          }
        } else if (item.FLAG_EDUCATION == "2") {
        } else if (item.FLAG_EDUCATION == "3") {
          const checkData = await this.HR_EDUCATIONRepo.find({
            where: { CIT: _item.citizenId, FLAG_EDUCATION: "3" },
            order: { EDUCATION_SEQ: "DESC" },
          });
          if ((checkData.length > 1 && checkData[0].id == item.id) || checkData.length == 1) {
            education.isHigh = true;
            const findIsEducation = await this.HR_EDUCATIONRepo.findOne({
              where: { CIT: _item.citizenId, FLAG_EDUCATION: "1" },
            });
            if (findIsEducation == null) {
              education.isEducation = true;
            }
          }
        }
        education.level = educationLevel == null ? 1 : educationLevel.level + 1;
        education.profileId = _item.id;
        education.degree = educationCode ? educationCode.EDUCATION_NAME : _null;
        education.field = hrMajorCode ? hrMajorCode.MAJOR_NAME : _null;
        education.educationLevel = hrFundCourseCode ? hrFundCourseCode.FUND_COURSE_NAME : _null;
        education.educationLevelId = hrFundCourseCode ? hrFundCourseCode.refId : _null;
        education.institute = item.INSTITUE;
        education.level = item.EDUCATION_SEQ ? Number(item.EDUCATION_SEQ) : _null;
        education.startDate = startDate;
        education.endDate = endDate;
        education.createdUserId = request.user.sub;
        education.createdFullName = request.user.name;
        education.lastUpdateUserId = request.user.sub;
        education.lastUpdateFullName = request.user.name;
        education.createdAt = new Date();
        education.lastUpdatedAt = new Date();
        // await educations.push(await education);
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileEducationRepo.save(await education);
      }
      // await this.profileEducationRepo.save(educations);
      // educations = await [];
    }
    // }
    // console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
    // await this.profileEducationRepo.save(educations);
    return new HttpSuccess();
  }

  /**
   * @summary ประวัติการศึกษา ลูกจ้างประจำ
   */
  @Post("uploadProfileEducation-Employee")
  async UploadFileSQLEducationEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let educations: any = [];
    let _null: any = null;
    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    // for (var i = 1; i <= total / BATCH_SIZE; i++) {
    //   const profiles = await AppDataSource.getRepository(ProfileEmployee)
    //     .createQueryBuilder("profile")
    //     .select(["profile.citizenId", "profile.id"])
    //     .orderBy("profile.citizenId", "ASC")
    //     .skip((i - 1) * BATCH_SIZE)
    //     .take(BATCH_SIZE)
    //     .getMany();

    for (const _item of profiles) {
      const existingProfile = await this.HR_EDUCATION_EMPRepo.find({
        where: { CIT: _item.citizenId },
        // select: [
        //   "CIT",
        //   "EDUCATION_CODE",
        //   "START_EDUCATION_YEAR",
        //   "EDUCATION_YEAR",
        //   "INSTITUE",
        //   "EDUCATION_SEQ",
        // ],
        order: { EDUCATION_SEQ: "ASC" },
      });

      const educationLevel = await this.profileEducationRepo.findOne({
        select: ["id", "level", "profileEmployeeId", "isDeleted"],
        where: { profileEmployeeId: _item.id, isDeleted: false },
        order: { level: "DESC" },
      });

      // educations = await [];
      for (const item of existingProfile) {
        rowCount++;
        const education = new ProfileEducation();
        const educationCode = await this.educationMisRepo.findOne({
          where: { EDUCATION_CODE: item.EDUCATION_CODE },
        });
        const hrMajorCode = await this.HR_MAJOR_CODERepo.findOne({
          where: { MAJOR_CODE: item.MAJOR_CODE },
        });
        const hrFundCourseCode = await this.HR_FUND_COURSE_CODERepo.findOne({
          where: { FUND_COURSE_CODE: item.FUND_COURSE_CODE },
        });

        // let startDate = item.START_EDUCATION_YEAR
        //   ? Extension.ConvertToDateTimeV2(item.START_EDUCATION_YEAR)
        //   : _null;
        let startDate =
          item.START_EDUCATION_YEAR != "" && Number(item.START_EDUCATION_YEAR) > 2500
            ? new Date(Number(item.START_EDUCATION_YEAR) - 543, 0, 1)
            : _null;

        // let endDate = item.EDUCATION_YEAR
        //   ? Extension.ConvertToDateTimeV2(item.EDUCATION_YEAR)
        //   : _null;
        let endDate =
          item.EDUCATION_YEAR != "" && Number(item.EDUCATION_YEAR) > 2500
            ? new Date(Number(item.EDUCATION_YEAR) - 543, 0, 1)
            : _null;
        if (item.FLAG_EDUCATION == "1") {
          const checkData = await this.HR_EDUCATIONRepo.find({
            where: { CIT: _item.citizenId, FLAG_EDUCATION: "1" },
            order: { EDUCATION_SEQ: "DESC" },
          });
          if ((checkData.length > 1 && checkData[0].id == item.id) || checkData.length == 1) {
            education.isEducation = true;
            const findIsHigh = await this.HR_EDUCATIONRepo.findOne({
              where: { CIT: _item.citizenId, FLAG_EDUCATION: "3" },
            });
            if (findIsHigh == null) {
              education.isHigh = true;
            }
          }
        } else if (item.FLAG_EDUCATION == "2") {
        } else if (item.FLAG_EDUCATION == "3") {
          const checkData = await this.HR_EDUCATIONRepo.find({
            where: { CIT: _item.citizenId, FLAG_EDUCATION: "3" },
            order: { EDUCATION_SEQ: "DESC" },
          });
          if ((checkData.length > 1 && checkData[0].id == item.id) || checkData.length == 1) {
            education.isHigh = true;
            const findIsEducation = await this.HR_EDUCATIONRepo.findOne({
              where: { CIT: _item.citizenId, FLAG_EDUCATION: "1" },
            });
            if (findIsEducation == null) {
              education.isEducation = true;
            }
          }
        }
        education.level = educationLevel == null ? 1 : educationLevel.level + 1;
        education.profileEmployeeId = _item.id;
        education.degree = educationCode ? educationCode.EDUCATION_NAME : _null;
        education.field = hrMajorCode ? hrMajorCode.MAJOR_NAME : _null;
        education.educationLevel = hrFundCourseCode ? hrFundCourseCode.FUND_COURSE_NAME : _null;
        education.educationLevelId = hrFundCourseCode ? hrFundCourseCode.refId : _null;
        education.institute = item.INSTITUE;
        education.level = item.EDUCATION_SEQ ? Number(item.EDUCATION_SEQ) : _null;
        education.startDate = startDate;
        education.endDate = endDate;
        education.createdUserId = request.user.sub;
        education.createdFullName = request.user.name;
        education.lastUpdateUserId = request.user.sub;
        education.lastUpdateFullName = request.user.name;
        education.createdAt = new Date();
        education.lastUpdatedAt = new Date();
        // await educations.push(await education);
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileEducationRepo.save(await education);
      }
      // await this.profileEducationRepo.save(educations);
      // educations = await [];
    }
    // }
    // console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
    // await this.profileEducationRepo.save(educations);
    return new HttpSuccess();
  }

  /**
   * @summary ประวัติการศึกษา ลูกจ้างชั่วคราว
   */
  @Post("uploadProfileEducation-EmployeeTemp")
  async UploadFileSQLEducationEmpTemp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let educations: any = [];
    let _null: any = null;
    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .where({ employeeClass: "TEMP" })
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    // for (var i = 1; i <= total / BATCH_SIZE; i++) {
    //   const profiles = await AppDataSource.getRepository(ProfileEmployee)
    //     .createQueryBuilder("profile")
    //     .select(["profile.citizenId", "profile.id"])
    //     .orderBy("profile.citizenId", "ASC")
    //     .skip((i - 1) * BATCH_SIZE)
    //     .take(BATCH_SIZE)
    //     .getMany();

    for (const _item of profiles) {
      const existingProfile = await this.HR_EDUCATION_EMPTEMPRepo.find({
        where: { CIT: _item.citizenId },
        // select: [
        //   "CIT",
        //   "EDUCATION_CODE",
        //   "START_EDUCATION_YEAR",
        //   "EDUCATION_YEAR",
        //   "INSTITUE",
        //   "EDUCATION_SEQ",
        // ],
        order: { EDUCATION_SEQ: "ASC" },
      });

      const educationLevel = await this.profileEducationRepo.findOne({
        select: ["id", "level", "profileEmployeeId", "isDeleted"],
        where: { profileEmployeeId: _item.id, isDeleted: false },
        order: { level: "DESC" },
      });

      // educations = await [];
      for (const item of existingProfile) {
        rowCount++;
        const education = new ProfileEducation();
        const educationCode = await this.educationMisRepo.findOne({
          where: { EDUCATION_CODE: item.EDUCATION_CODE },
        });
        const hrMajorCode = await this.HR_MAJOR_CODERepo.findOne({
          where: { MAJOR_CODE: item.MAJOR_CODE },
        });
        const hrFundCourseCode = await this.HR_FUND_COURSE_CODERepo.findOne({
          where: { FUND_COURSE_CODE: item.FUND_COURSE_CODE },
        });

        // let startDate = item.START_EDUCATION_YEAR
        //   ? Extension.ConvertToDateTimeV2(item.START_EDUCATION_YEAR)
        //   : _null;
        let startDate =
          item.START_EDUCATION_YEAR != "" && Number(item.START_EDUCATION_YEAR) > 2500
            ? new Date(Number(item.START_EDUCATION_YEAR) - 543, 0, 1)
            : _null;

        // let endDate = item.EDUCATION_YEAR
        //   ? Extension.ConvertToDateTimeV2(item.EDUCATION_YEAR)
        //   : _null;
        let endDate =
          item.EDUCATION_YEAR != "" && Number(item.EDUCATION_YEAR) > 2500
            ? new Date(Number(item.EDUCATION_YEAR) - 543, 0, 1)
            : _null;
        if (item.FLAG_EDUCATION == "1") {
          const checkData = await this.HR_EDUCATIONRepo.find({
            where: { CIT: _item.citizenId, FLAG_EDUCATION: "1" },
            order: { EDUCATION_SEQ: "DESC" },
          });
          if ((checkData.length > 1 && checkData[0].id == item.id) || checkData.length == 1) {
            education.isEducation = true;
            const findIsHigh = await this.HR_EDUCATIONRepo.findOne({
              where: { CIT: _item.citizenId, FLAG_EDUCATION: "3" },
            });
            if (findIsHigh == null) {
              education.isHigh = true;
            }
          }
        } else if (item.FLAG_EDUCATION == "2") {
        } else if (item.FLAG_EDUCATION == "3") {
          const checkData = await this.HR_EDUCATIONRepo.find({
            where: { CIT: _item.citizenId, FLAG_EDUCATION: "3" },
            order: { EDUCATION_SEQ: "DESC" },
          });
          if ((checkData.length > 1 && checkData[0].id == item.id) || checkData.length == 1) {
            education.isHigh = true;
            const findIsEducation = await this.HR_EDUCATIONRepo.findOne({
              where: { CIT: _item.citizenId, FLAG_EDUCATION: "1" },
            });
            if (findIsEducation == null) {
              education.isEducation = true;
            }
          }
        }
        education.level = educationLevel == null ? 1 : educationLevel.level + 1;
        education.profileEmployeeId = _item.id;
        education.degree = educationCode ? educationCode.EDUCATION_NAME : _null;
        education.field = hrMajorCode ? hrMajorCode.MAJOR_NAME : _null;
        education.educationLevel = hrFundCourseCode ? hrFundCourseCode.FUND_COURSE_NAME : _null;
        education.educationLevelId = hrFundCourseCode ? hrFundCourseCode.refId : _null;
        education.institute = item.INSTITUE;
        education.level = item.EDUCATION_SEQ ? Number(item.EDUCATION_SEQ) : _null;
        education.startDate = startDate;
        education.endDate = endDate;
        education.createdUserId = request.user.sub;
        education.createdFullName = request.user.name;
        education.lastUpdateUserId = request.user.sub;
        education.lastUpdateFullName = request.user.name;
        education.createdAt = new Date();
        education.lastUpdatedAt = new Date();
        // await educations.push(await education);
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileEducationRepo.save(await education);
      }
      // await this.profileEducationRepo.save(educations);
      // educations = await [];
    }
    // }
    // console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
    // await this.profileEducationRepo.save(educations);
    return new HttpSuccess();
  }

  /**
   * @summary Import Province
   */
  @Post("ImportProvince")
  @UseInterceptors(FileInterceptor("file"))
  async ImportProvince(
    @UploadedFile() file: Express.Multer.File,
    @Request() request: { user: Record<string, any> },
  ) {
    const workbook = xlsx.read(file.buffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const getExcel = xlsx.utils.sheet_to_json(sheet);
    let provinces_: any = [];

    await Promise.all(
      getExcel.map(async (item: any) => {
        const prov = new ProvinceImport();
        prov.PROVINCE_CODE = item.PROVINCE_CODE;
        prov.PROVINCE_NAME = item.PROVINCE_NAME;
        prov.createdUserId = request.user.sub;
        prov.createdFullName = request.user.name;
        prov.lastUpdateUserId = request.user.sub;
        prov.lastUpdateFullName = request.user.name;
        prov.createdAt = new Date();
        prov.lastUpdatedAt = new Date();
        provinces_.push(prov);
      }),
    );
    await this.provincsRepo.save(provinces_);
    return new HttpSuccess(provinces_);
  }

  /**
   * @summary Import Amphur
   */
  @Post("ImportAmphur")
  @UseInterceptors(FileInterceptor("file"))
  async ImportAmphur(
    @UploadedFile() file: Express.Multer.File,
    @Request() request: { user: Record<string, any> },
  ) {
    const workbook = xlsx.read(file.buffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const getExcel = xlsx.utils.sheet_to_json(sheet);
    let amphur_: any = [];

    await Promise.all(
      getExcel.map(async (item: any) => {
        const amh = new AmphurImport();
        amh.PROVINCE_CODE = item.PROVINCE_CODE;
        amh.AMPHUR_CODE = item.AMPHUR_CODE;
        amh.AMPHUR_NAME = item.AMPHUR_NAME;
        amh.createdUserId = request.user.sub;
        amh.createdFullName = request.user.name;
        amh.lastUpdateUserId = request.user.sub;
        amh.lastUpdateFullName = request.user.name;
        amh.createdAt = new Date();
        amh.lastUpdatedAt = new Date();
        amphur_.push(amh);
      }),
    );
    await this.amphurRepo.save(amphur_);
    return new HttpSuccess(amphur_);
  }

  /**
   * @summary Import SubDistrict
   */
  @Post("ImportSubDistrict")
  @UseInterceptors(FileInterceptor("file"))
  async ImportSubDistrict(
    @UploadedFile() file: Express.Multer.File,
    @Request() request: { user: Record<string, any> },
  ) {
    const workbook = xlsx.read(file.buffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const getExcel = xlsx.utils.sheet_to_json(sheet);
    let subDistrict_: any = [];

    await Promise.all(
      getExcel.map(async (item: any) => {
        const subD = new SubDistrictImport();
        subD.PROVINCE_CODE = item.PROVINCE_CODE;
        subD.AMPHUR_CODE = item.AMPHUR_CODE;
        subD.DISTRICT_CODE = item.DISTRICT_CODE;
        subD.DISTRICT_NAME = item.DISTRICT_NAME;
        subD.createdUserId = request.user.sub;
        subD.createdFullName = request.user.name;
        subD.lastUpdateUserId = request.user.sub;
        subD.lastUpdateFullName = request.user.name;
        subD.createdAt = new Date();
        subD.lastUpdatedAt = new Date();
        subDistrict_.push(subD);
      }),
    );
    await this.subDistrictRepo.save(subDistrict_);
    return new HttpSuccess(subDistrict_);
  }

  /**
   * @summary ที่อยู่ตามทะเบียนบ้าน-ปัจจุบัน ข้าราชการ
   */
  @Post("uploadProfileAddress-Officer")
  async UploadFileSQLAddress(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      // .where("profile.citizenId = '3101702379675'")
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    const meta = {
      createdUserId: request.user.sub,
      createdFullName: request.user.name,
      lastUpdateUserId: request.user.sub,
      lastUpdateFullName: request.user.name,
      createdAt: new Date(),
      lastUpdatedAt: new Date(),
    };
    for (const _item of profiles) {
      const existingProfile = await this.HR_PERSONAL_OFFICER_ADDRESSRepo.findOne({
        where: { CIT: _item.citizenId },
      });

      if (!existingProfile) {
        continue;
      }
      let PROVINCE_CODE = Number(existingProfile.PROVINCE_CODE).toString();
      let DISTRICT_CODE = Number(existingProfile.DISTRICT_CODE).toString();
      let AMPHUR_CODE = Number(existingProfile.AMPHUR_CODE).toString();
      let CONTACT_PROVINCE_CODE = Number(existingProfile.CONTACT_PROVINCE_CODE).toString();
      let CONTACT_DISTRICT_CODE = Number(existingProfile.CONTACT_DISTRICT_CODE).toString();
      let CONTACT_AMPHUR_CODE = Number(existingProfile.CONTACT_AMPHUR_CODE).toString();
      rowCount++;
      //registration address
      if (PROVINCE_CODE) {
        let provinceRegis_ = await this.provincsRepo.findOne({
          where: { PROVINCE_CODE: PROVINCE_CODE },
        });
        if (provinceRegis_) {
          let provinceId = await this.provinceIdRepo.findOne({
            where: {
              name: provinceRegis_.PROVINCE_NAME,
            },
          });
          if (provinceId == null) {
            provinceId = new Province();
            Object.assign(provinceId, {
              ...meta,
              name: provinceRegis_.PROVINCE_NAME,
            });
            await this.provinceIdRepo.save(provinceId);
          }
          _item.registrationProvinceId = provinceId ? provinceId.id : _null;
          if (AMPHUR_CODE) {
            let districtRegis_ = await this.amphurRepo.findOne({
              where: {
                AMPHUR_CODE: AMPHUR_CODE,
                PROVINCE_CODE: provinceRegis_.PROVINCE_CODE,
              },
            });
            if (districtRegis_) {
              let districtId = await this.districtIdRepo.findOne({
                where: {
                  name: districtRegis_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                },
              });
              if (districtId == null) {
                districtId = new District();
                Object.assign(districtId, {
                  ...meta,
                  name: districtRegis_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                });
                await this.districtIdRepo.save(districtId);
              }
              _item.registrationDistrictId = districtId ? districtId.id : _null;
              if (DISTRICT_CODE) {
                let subDistrictRegis_ = await this.subDistrictRepo.findOne({
                  where: {
                    DISTRICT_CODE: DISTRICT_CODE,
                    AMPHUR_CODE: districtRegis_.AMPHUR_CODE,
                    PROVINCE_CODE: provinceRegis_.PROVINCE_CODE,
                  },
                });
                if (subDistrictRegis_) {
                  let subDistrictId = await this.subDistrictIdRepo.findOne({
                    where: {
                      name: subDistrictRegis_.DISTRICT_NAME,
                      districtId: districtId.id,
                    },
                  });
                  if (subDistrictId == null) {
                    subDistrictId = new SubDistrict();
                    Object.assign(subDistrictId, {
                      ...meta,
                      name: subDistrictRegis_.DISTRICT_NAME,
                      zipCode: existingProfile.ZIPCODE,
                      districtId: districtId.id,
                    });
                    await this.subDistrictIdRepo.save(subDistrictId);
                  }
                  _item.registrationSubDistrictId = subDistrictId ? subDistrictId.id : _null;
                }
              }
            }
          }
        }
      }
      //current address
      if (CONTACT_PROVINCE_CODE) {
        let provinceCurr_ = await this.provincsRepo.findOne({
          where: { PROVINCE_CODE: CONTACT_PROVINCE_CODE },
        });
        if (provinceCurr_) {
          let provinceId = await this.provinceIdRepo.findOne({
            where: {
              name: provinceCurr_.PROVINCE_NAME,
            },
          });
          if (provinceId == null) {
            provinceId = new Province();
            Object.assign(provinceId, {
              ...meta,
              name: provinceCurr_.PROVINCE_NAME,
            });
            await this.provinceIdRepo.save(provinceId);
          }
          _item.currentProvinceId = provinceId ? provinceId.id : _null;
          if (CONTACT_AMPHUR_CODE) {
            let districtCurr_ = await this.amphurRepo.findOne({
              where: {
                AMPHUR_CODE: CONTACT_AMPHUR_CODE,
                PROVINCE_CODE: provinceCurr_.PROVINCE_CODE,
              },
            });
            if (districtCurr_) {
              let districtId = await this.districtIdRepo.findOne({
                where: {
                  name: districtCurr_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                },
              });
              if (districtId == null) {
                districtId = new District();
                Object.assign(districtId, {
                  ...meta,
                  name: districtCurr_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                });
                await this.districtIdRepo.save(districtId);
              }
              _item.currentDistrictId = districtId ? districtId.id : _null;
              if (CONTACT_DISTRICT_CODE) {
                let subDistrictCurr_ = await this.subDistrictRepo.findOne({
                  where: {
                    DISTRICT_CODE: CONTACT_DISTRICT_CODE,
                    AMPHUR_CODE: districtCurr_.AMPHUR_CODE,
                    PROVINCE_CODE: provinceCurr_.PROVINCE_CODE,
                  },
                });
                if (subDistrictCurr_) {
                  let subDistrictId = await this.subDistrictIdRepo.findOne({
                    where: {
                      name: subDistrictCurr_.DISTRICT_NAME,
                      districtId: districtId.id,
                    },
                  });
                  if (subDistrictId == null) {
                    subDistrictId = new SubDistrict();
                    Object.assign(subDistrictId, {
                      ...meta,
                      name: subDistrictCurr_.DISTRICT_NAME,
                      zipCode: existingProfile.CONTACT_ZIPCODE,
                      districtId: districtId.id,
                    });
                    await this.subDistrictIdRepo.save(subDistrictId);
                  }
                  _item.currentSubDistrictId = subDistrictId ? subDistrictId.id : _null;
                }
              }
            }
          }
        }
      }
      _item.registrationAddress = existingProfile.H_NUMBER;
      _item.registrationZipCode = existingProfile.ZIPCODE;
      _item.currentAddress = existingProfile.CONTACT_H_NUMBER;
      _item.currentZipCode = existingProfile.CONTACT_ZIPCODE;
      _item.createdUserId = request.user.sub;
      _item.createdFullName = request.user.name;
      _item.lastUpdateUserId = request.user.sub;
      _item.lastUpdateFullName = request.user.name;
      _item.createdAt = new Date();
      _item.lastUpdatedAt = new Date();
      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
      await this.profileRepo.save(_item);
    }
    return new HttpSuccess();
  }

  /**
   * @summary ที่อยู่ตามทะเบียนบ้าน-ปัจจุบัน ลูกจ้างประจำ
   */
  @Post("uploadProfileAddress-Employee")
  async UploadFileSQLAddressEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let profileDatas: any = [];
    let _null: any = null;
    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      // .where("profile.citizenId = '3610700119735'")
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    const meta = {
      createdUserId: request.user.sub,
      createdFullName: request.user.name,
      lastUpdateUserId: request.user.sub,
      lastUpdateFullName: request.user.name,
      createdAt: new Date(),
      lastUpdatedAt: new Date(),
    };
    for (const _item of profiles) {
      const existingProfile = await this.HR_PERSONAL_EMP_ADDRESSRepo.findOne({
        where: { CIT: _item.citizenId },
      });

      if (!existingProfile) {
        continue;
      }
      let PROVINCE_CODE = Number(existingProfile.PROVINCE_CODE).toString();
      let DISTRICT_CODE = Number(existingProfile.DISTRICT_CODE).toString();
      let AMPHUR_CODE = Number(existingProfile.AMPHUR_CODE).toString();
      let CONTACT_PROVINCE_CODE = Number(existingProfile.CONTACT_PROVINCE_CODE).toString();
      let CONTACT_DISTRICT_CODE = Number(existingProfile.CONTACT_DISTRICT_CODE).toString();
      let CONTACT_AMPHUR_CODE = Number(existingProfile.CONTACT_AMPHUR_CODE).toString();
      rowCount++;
      //registration address
      if (PROVINCE_CODE) {
        let provinceRegis_ = await this.provincsRepo.findOne({
          where: { PROVINCE_CODE: PROVINCE_CODE },
        });
        if (provinceRegis_) {
          let provinceId = await this.provinceIdRepo.findOne({
            where: {
              name: provinceRegis_.PROVINCE_NAME,
            },
          });
          if (provinceId == null) {
            provinceId = new Province();
            Object.assign(provinceId, {
              ...meta,
              name: provinceRegis_.PROVINCE_NAME,
            });
            await this.provinceIdRepo.save(provinceId);
          }
          _item.registrationProvinceId = provinceId ? provinceId.id : _null;
          if (AMPHUR_CODE) {
            let districtRegis_ = await this.amphurRepo.findOne({
              where: {
                AMPHUR_CODE: AMPHUR_CODE,
                PROVINCE_CODE: provinceRegis_.PROVINCE_CODE,
              },
            });
            if (districtRegis_) {
              let districtId = await this.districtIdRepo.findOne({
                where: {
                  name: districtRegis_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                },
              });
              if (districtId == null) {
                districtId = new District();
                Object.assign(districtId, {
                  ...meta,
                  name: districtRegis_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                });
                await this.districtIdRepo.save(districtId);
              }
              _item.registrationDistrictId = districtId ? districtId.id : _null;
              if (DISTRICT_CODE) {
                let subDistrictRegis_ = await this.subDistrictRepo.findOne({
                  where: {
                    DISTRICT_CODE: DISTRICT_CODE,
                    AMPHUR_CODE: districtRegis_.AMPHUR_CODE,
                    PROVINCE_CODE: provinceRegis_.PROVINCE_CODE,
                  },
                });
                if (subDistrictRegis_) {
                  let subDistrictId = await this.subDistrictIdRepo.findOne({
                    where: {
                      name: subDistrictRegis_.DISTRICT_NAME,
                      districtId: districtId.id,
                    },
                  });
                  if (subDistrictId == null) {
                    subDistrictId = new SubDistrict();
                    Object.assign(subDistrictId, {
                      ...meta,
                      name: subDistrictRegis_.DISTRICT_NAME,
                      zipCode: existingProfile.ZIPCODE,
                      districtId: districtId.id,
                    });
                    await this.subDistrictIdRepo.save(subDistrictId);
                  }
                  _item.registrationSubDistrictId = subDistrictId ? subDistrictId.id : _null;
                }
              }
            }
          }
        }
      }
      //current address
      if (CONTACT_PROVINCE_CODE) {
        let provinceCurr_ = await this.provincsRepo.findOne({
          where: { PROVINCE_CODE: CONTACT_PROVINCE_CODE },
        });
        if (provinceCurr_) {
          let provinceId = await this.provinceIdRepo.findOne({
            where: {
              name: provinceCurr_.PROVINCE_NAME,
            },
          });
          if (provinceId == null) {
            provinceId = new Province();
            Object.assign(provinceId, {
              ...meta,
              name: provinceCurr_.PROVINCE_NAME,
            });
            await this.provinceIdRepo.save(provinceId);
          }
          _item.currentProvinceId = provinceId ? provinceId.id : _null;
          if (CONTACT_AMPHUR_CODE) {
            let districtCurr_ = await this.amphurRepo.findOne({
              where: {
                AMPHUR_CODE: CONTACT_AMPHUR_CODE,
                PROVINCE_CODE: provinceCurr_.PROVINCE_CODE,
              },
            });
            if (districtCurr_) {
              let districtId = await this.districtIdRepo.findOne({
                where: {
                  name: districtCurr_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                },
              });
              if (districtId == null) {
                districtId = new District();
                Object.assign(districtId, {
                  ...meta,
                  name: districtCurr_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                });
                await this.districtIdRepo.save(districtId);
              }
              _item.currentDistrictId = districtId ? districtId.id : _null;
              if (CONTACT_DISTRICT_CODE) {
                let subDistrictCurr_ = await this.subDistrictRepo.findOne({
                  where: {
                    DISTRICT_CODE: CONTACT_DISTRICT_CODE,
                    AMPHUR_CODE: districtCurr_.AMPHUR_CODE,
                    PROVINCE_CODE: provinceCurr_.PROVINCE_CODE,
                  },
                });
                if (subDistrictCurr_) {
                  let subDistrictId = await this.subDistrictIdRepo.findOne({
                    where: {
                      name: subDistrictCurr_.DISTRICT_NAME,
                      districtId: districtId.id,
                    },
                  });
                  if (subDistrictId == null) {
                    subDistrictId = new SubDistrict();
                    Object.assign(subDistrictId, {
                      ...meta,
                      name: subDistrictCurr_.DISTRICT_NAME,
                      zipCode: existingProfile.CONTACT_ZIPCODE,
                      districtId: districtId.id,
                    });
                    await this.subDistrictIdRepo.save(subDistrictId);
                  }
                  _item.currentSubDistrictId = subDistrictId ? subDistrictId.id : _null;
                }
              }
            }
          }
        }
      }
      _item.registrationAddress = existingProfile.H_NUMBER;
      _item.registrationZipCode = existingProfile.ZIPCODE;
      _item.currentAddress = existingProfile.CONTACT_H_NUMBER;
      _item.currentZipCode = existingProfile.CONTACT_ZIPCODE;
      _item.createdUserId = request.user.sub;
      _item.createdFullName = request.user.name;
      _item.lastUpdateUserId = request.user.sub;
      _item.lastUpdateFullName = request.user.name;
      _item.createdAt = new Date();
      _item.lastUpdatedAt = new Date();
      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
      await this.profileEmpRepo.save(_item);
    }
    return new HttpSuccess();
  }

  /**
   * @summary ที่อยู่ตามทะเบียนบ้าน-ปัจจุบัน ลูกจ้างชั่วคราว
   */
  @Post("uploadProfileAddress-EmployeeTemp")
  async UploadFileSQLAddressEmpTemp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let profileDatas: any = [];
    let _null: any = null;
    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .where({ employeeClass: "TEMP" })
      // .where("profile.citizenId = '3610700119735'")
      //   .skip(0)
      //   .take(20)
      .getManyAndCount();
    const meta = {
      createdUserId: request.user.sub,
      createdFullName: request.user.name,
      lastUpdateUserId: request.user.sub,
      lastUpdateFullName: request.user.name,
      createdAt: new Date(),
      lastUpdatedAt: new Date(),
    };
    for (const _item of profiles) {
      const existingProfile = await this.HR_PERSONAL_EMPTEMP_ADDRESSRepo.findOne({
        where: { CIT: _item.citizenId },
      });

      if (!existingProfile) {
        continue;
      }
      let PROVINCE_CODE = Number(existingProfile.PROVINCE_CODE).toString();
      let DISTRICT_CODE = Number(existingProfile.DISTRICT_CODE).toString();
      let AMPHUR_CODE = Number(existingProfile.AMPHUR_CODE).toString();
      let CONTACT_PROVINCE_CODE = Number(existingProfile.CONTACT_PROVINCE_CODE).toString();
      let CONTACT_DISTRICT_CODE = Number(existingProfile.CONTACT_DISTRICT_CODE).toString();
      let CONTACT_AMPHUR_CODE = Number(existingProfile.CONTACT_AMPHUR_CODE).toString();
      rowCount++;
      //registration address
      if (PROVINCE_CODE) {
        let provinceRegis_ = await this.provincsRepo.findOne({
          where: { PROVINCE_CODE: PROVINCE_CODE },
        });
        if (provinceRegis_) {
          let provinceId = await this.provinceIdRepo.findOne({
            where: {
              name: provinceRegis_.PROVINCE_NAME,
            },
          });
          if (provinceId == null) {
            provinceId = new Province();
            Object.assign(provinceId, {
              ...meta,
              name: provinceRegis_.PROVINCE_NAME,
            });
            await this.provinceIdRepo.save(provinceId);
          }
          _item.registrationProvinceId = provinceId ? provinceId.id : _null;
          if (AMPHUR_CODE) {
            let districtRegis_ = await this.amphurRepo.findOne({
              where: {
                AMPHUR_CODE: AMPHUR_CODE,
                PROVINCE_CODE: provinceRegis_.PROVINCE_CODE,
              },
            });
            if (districtRegis_) {
              let districtId = await this.districtIdRepo.findOne({
                where: {
                  name: districtRegis_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                },
              });
              if (districtId == null) {
                districtId = new District();
                Object.assign(districtId, {
                  ...meta,
                  name: districtRegis_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                });
                await this.districtIdRepo.save(districtId);
              }
              _item.registrationDistrictId = districtId ? districtId.id : _null;
              if (DISTRICT_CODE) {
                let subDistrictRegis_ = await this.subDistrictRepo.findOne({
                  where: {
                    DISTRICT_CODE: DISTRICT_CODE,
                    AMPHUR_CODE: districtRegis_.AMPHUR_CODE,
                    PROVINCE_CODE: provinceRegis_.PROVINCE_CODE,
                  },
                });
                if (subDistrictRegis_) {
                  let subDistrictId = await this.subDistrictIdRepo.findOne({
                    where: {
                      name: subDistrictRegis_.DISTRICT_NAME,
                      districtId: districtId.id,
                    },
                  });
                  if (subDistrictId == null) {
                    subDistrictId = new SubDistrict();
                    Object.assign(subDistrictId, {
                      ...meta,
                      name: subDistrictRegis_.DISTRICT_NAME,
                      zipCode: existingProfile.ZIPCODE,
                      districtId: districtId.id,
                    });
                    await this.subDistrictIdRepo.save(subDistrictId);
                  }
                  _item.registrationSubDistrictId = subDistrictId ? subDistrictId.id : _null;
                }
              }
            }
          }
        }
      }
      //current address
      if (CONTACT_PROVINCE_CODE) {
        let provinceCurr_ = await this.provincsRepo.findOne({
          where: { PROVINCE_CODE: CONTACT_PROVINCE_CODE },
        });
        if (provinceCurr_) {
          let provinceId = await this.provinceIdRepo.findOne({
            where: {
              name: provinceCurr_.PROVINCE_NAME,
            },
          });
          if (provinceId == null) {
            provinceId = new Province();
            Object.assign(provinceId, {
              ...meta,
              name: provinceCurr_.PROVINCE_NAME,
            });
            await this.provinceIdRepo.save(provinceId);
          }
          _item.currentProvinceId = provinceId ? provinceId.id : _null;
          if (CONTACT_AMPHUR_CODE) {
            let districtCurr_ = await this.amphurRepo.findOne({
              where: {
                AMPHUR_CODE: CONTACT_AMPHUR_CODE,
                PROVINCE_CODE: provinceCurr_.PROVINCE_CODE,
              },
            });
            if (districtCurr_) {
              let districtId = await this.districtIdRepo.findOne({
                where: {
                  name: districtCurr_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                },
              });
              if (districtId == null) {
                districtId = new District();
                Object.assign(districtId, {
                  ...meta,
                  name: districtCurr_.AMPHUR_NAME,
                  provinceId: provinceId.id,
                });
                await this.districtIdRepo.save(districtId);
              }
              _item.currentDistrictId = districtId ? districtId.id : _null;
              if (CONTACT_DISTRICT_CODE) {
                let subDistrictCurr_ = await this.subDistrictRepo.findOne({
                  where: {
                    DISTRICT_CODE: CONTACT_DISTRICT_CODE,
                    AMPHUR_CODE: districtCurr_.AMPHUR_CODE,
                    PROVINCE_CODE: provinceCurr_.PROVINCE_CODE,
                  },
                });
                if (subDistrictCurr_) {
                  let subDistrictId = await this.subDistrictIdRepo.findOne({
                    where: {
                      name: subDistrictCurr_.DISTRICT_NAME,
                      districtId: districtId.id,
                    },
                  });
                  if (subDistrictId == null) {
                    subDistrictId = new SubDistrict();
                    Object.assign(subDistrictId, {
                      ...meta,
                      name: subDistrictCurr_.DISTRICT_NAME,
                      zipCode: existingProfile.CONTACT_ZIPCODE,
                      districtId: districtId.id,
                    });
                    await this.subDistrictIdRepo.save(subDistrictId);
                  }
                  _item.currentSubDistrictId = subDistrictId ? subDistrictId.id : _null;
                }
              }
            }
          }
        }
      }
      _item.registrationAddress = existingProfile.H_NUMBER;
      _item.registrationZipCode = existingProfile.ZIPCODE;
      _item.currentAddress = existingProfile.CONTACT_H_NUMBER;
      _item.currentZipCode = existingProfile.CONTACT_ZIPCODE;
      _item.createdUserId = request.user.sub;
      _item.createdFullName = request.user.name;
      _item.lastUpdateUserId = request.user.sub;
      _item.lastUpdateFullName = request.user.name;
      _item.createdAt = new Date();
      _item.lastUpdatedAt = new Date();
      console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
      await this.profileEmpRepo.save(_item);
    }
    return new HttpSuccess();
  }
  /**
   * @summary ทะเบียนประวัติ ข้าราชการ
   */
  @Post("cleardataupload")
  async ClearDataUpload(@Request() request: { user: Record<string, any> }) {
    const profileOff = await this.profileRepo.find({
      select: ["citizenId"],
    });
    const profileEmp = await this.profileEmpRepo.find({
      select: ["citizenId"],
    });
    const _profileOff = profileOff.map((x) => x.citizenId);
    const _profileEmp = profileEmp.map((x) => x.citizenId);
    const allId = _profileOff.concat(_profileEmp);

    for (var i = 1; i <= 1000; i++) {
      const positionOfficer = await this.positionOfficerRepo.find({
        where: { citizenId: Not(In(allId)) },
        take: 1000,
        skip: 0,
      });
      this.positionOfficerRepo.remove(positionOfficer);
      //   const HR_PERSONAL_OFFICER_FAMILY = await this.HR_PERSONAL_OFFICER_FAMILYRepo.find({
      //     where: { CIT: Not(In(allId)) },
      //     take: 1000,
      //     skip: 0,
      //   });
      //   this.HR_PERSONAL_OFFICER_FAMILYRepo.remove(HR_PERSONAL_OFFICER_FAMILY);
      //   const HR_EDUCATION = await this.HR_EDUCATIONRepo.find({
      //     where: { CIT: Not(In(allId)) },
      //     take: 1000,
      //     skip: 0,
      //   });
      //   this.HR_EDUCATIONRepo.remove(HR_EDUCATION);
      //   const HR_PERSONAL_OFFICER_ADDRESS = await this.HR_PERSONAL_OFFICER_ADDRESSRepo.find({
      //     where: { CIT: Not(In(allId)) },
      //     take: 1000,
      //     skip: 0,
      //   });
      //   this.HR_PERSONAL_OFFICER_ADDRESSRepo.remove(HR_PERSONAL_OFFICER_ADDRESS);
      //   const HR_EDUCATION_EMP = await this.HR_EDUCATION_EMPRepo.find({
      //     where: { CIT: Not(In(allId)) },
      //     take: 1000,
      //     skip: 0,
      //   });
      //   this.HR_EDUCATION_EMPRepo.remove(HR_EDUCATION_EMP);
      //   const HR_PERSONAL_EMP_ADDRESS = await this.HR_PERSONAL_EMP_ADDRESSRepo.find({
      //     where: { CIT: Not(In(allId)) },
      //     take: 1000,
      //     skip: 0,
      //   });
      //   this.HR_PERSONAL_EMP_ADDRESSRepo.remove(HR_PERSONAL_EMP_ADDRESS);
      //   const HR_PERSONAL_EMP_FAMILY = await this.HR_PERSONAL_EMP_FAMILYRepo.find({
      //     where: { CIT: Not(In(allId)) },
      //     take: 1000,
      //     skip: 0,
      //   });
      //   this.HR_PERSONAL_EMP_FAMILYRepo.remove(HR_PERSONAL_EMP_FAMILY);
    }
    return new HttpSuccess();
  }

  /**
   * @summary Import Org
   */
  @Post("ImportOrg")
  async ImportOrg(@Request() request: { user: Record<string, any> }) {
    const orgRevision = await this.orgRevisionRepo.findOne({
      // where: { orgRevisionIsCurrent: true, orgRevisionIsDraft: false },
      where: { id: "30e594c9-a65b-485e-a4c5-71aa497e6b8a" },
    });
    if (orgRevision == null) return new HttpSuccess();
    //create root
    const IMPORT_CHILD = await this.IMPORT_ORGRepo.find({
      where: {
        orgRoot: Not(""),
        orgChild1: "",
        orgChild2: "",
        orgChild3: "",
      },
    });
    let order = 1;
    for (const item of IMPORT_CHILD) {
      const orgRoot = new OrgRoot();
      orgRoot.orgRootOrder = order;
      orgRoot.orgRootName = item.orgRoot;
      orgRoot.orgRootShortName = item.orgShortname;
      const rank: any = item.orgRank;
      orgRoot.orgRootRank = rank;
      orgRoot.orgRootRankSub = item.orgSubRank;
      orgRoot.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
      orgRoot.DIVISION_CODE = item.DIVISION_CODE;
      orgRoot.SECTION_CODE = item.SECTION_CODE;
      orgRoot.JOB_CODE = item.JOB_CODE;
      orgRoot.orgRevisionId = orgRevision.id;
      orgRoot.createdUserId = request.user.sub;
      orgRoot.createdFullName = request.user.name;
      orgRoot.lastUpdateUserId = request.user.sub;
      orgRoot.lastUpdateFullName = request.user.name;
      orgRoot.createdAt = new Date();
      orgRoot.lastUpdatedAt = new Date();
      await this.orgRootRepo.save(orgRoot);
      order++;
    }

    //create child1
    const IMPORT_CHILD1 = await this.IMPORT_ORGRepo.find({
      where: {
        orgRoot: Not(""),
        orgChild1: Not(""),
        orgChild2: "",
        orgChild3: "",
      },
    });

    for (const item of IMPORT_CHILD1) {
      const orgChild1 = new OrgChild1();
      let orgRoot = await this.orgRootRepo.findOne({
        where: { orgRootName: item.orgRoot, orgRevisionId: orgRevision.id },
        relations: ["orgChild1s"],
      });
      if (orgRoot == null) {
        orgRoot = new OrgRoot();
        orgRoot.orgRootOrder = order;
        order = order + 1;
        orgRoot.orgRootName = item.orgRoot;
        orgRoot.orgRootShortName = item.orgShortname;
        const rank: any = item.orgRank;
        orgRoot.orgRootRank = rank;
        orgRoot.orgRootRankSub = item.orgSubRank;
        orgRoot.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
        orgRoot.DIVISION_CODE = item.DIVISION_CODE;
        orgRoot.SECTION_CODE = item.SECTION_CODE;
        orgRoot.JOB_CODE = item.JOB_CODE;
        orgRoot.orgRevisionId = orgRevision.id;
        orgRoot.createdUserId = request.user.sub;
        orgRoot.createdFullName = request.user.name;
        orgRoot.lastUpdateUserId = request.user.sub;
        orgRoot.lastUpdateFullName = request.user.name;
        orgRoot.createdAt = new Date();
        orgRoot.lastUpdatedAt = new Date();
        await this.orgRootRepo.save(orgRoot);
      }

      orgChild1.orgChild1Order =
        orgRoot == null || orgRoot.orgChild1s == null || orgRoot.orgChild1s.length == 0
          ? 1
          : orgRoot.orgChild1s.length + 1;
      orgChild1.orgRootId = orgRoot.id;
      orgChild1.orgChild1Name = item.orgChild1;
      orgChild1.orgChild1ShortName = item.orgShortname;
      const rank: any = item.orgRank;
      orgChild1.orgChild1Rank = rank;
      orgChild1.orgChild1RankSub = item.orgSubRank;
      orgChild1.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
      orgChild1.DIVISION_CODE = item.DIVISION_CODE;
      orgChild1.SECTION_CODE = item.SECTION_CODE;
      orgChild1.JOB_CODE = item.JOB_CODE;
      orgChild1.orgRevisionId = orgRevision.id;
      orgChild1.createdUserId = request.user.sub;
      orgChild1.createdFullName = request.user.name;
      orgChild1.lastUpdateUserId = request.user.sub;
      orgChild1.lastUpdateFullName = request.user.name;
      orgChild1.createdAt = new Date();
      orgChild1.lastUpdatedAt = new Date();
      await this.orgChild1Repo.save(orgChild1);
    }

    //create child2
    const IMPORT_CHILD2 = await this.IMPORT_ORGRepo.find({
      where: {
        orgRoot: Not(""),
        orgChild1: Not(""),
        orgChild2: Not(""),
        orgChild3: "",
      },
    });
    for (const item of IMPORT_CHILD2) {
      const orgChild2 = new OrgChild2();
      let orgRoot = await this.orgRootRepo.findOne({
        where: { orgRootName: item.orgRoot, orgRevisionId: orgRevision.id },
        relations: ["orgChild1s"],
      });
      if (orgRoot == null) {
        orgRoot = new OrgRoot();
        orgRoot.orgRootOrder = order;
        order = order + 1;
        orgRoot.orgRootName = item.orgRoot;
        orgRoot.orgRootShortName = item.orgShortname;
        const rank: any = item.orgRank;
        orgRoot.orgRootRank = rank;
        orgRoot.orgRootRankSub = item.orgSubRank;
        orgRoot.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
        orgRoot.DIVISION_CODE = item.DIVISION_CODE;
        orgRoot.SECTION_CODE = item.SECTION_CODE;
        orgRoot.JOB_CODE = item.JOB_CODE;
        orgRoot.orgRevisionId = orgRevision.id;
        orgRoot.createdUserId = request.user.sub;
        orgRoot.createdFullName = request.user.name;
        orgRoot.lastUpdateUserId = request.user.sub;
        orgRoot.lastUpdateFullName = request.user.name;
        orgRoot.createdAt = new Date();
        orgRoot.lastUpdatedAt = new Date();
        await this.orgRootRepo.save(orgRoot);
      }
      let orgChild1 = await this.orgChild1Repo.findOne({
        where: {
          orgChild1Name: item.orgChild1,
          orgRootId: orgRoot.id,
          orgRevisionId: orgRevision.id,
        },
        relations: ["orgChild2s"],
      });
      if (orgChild1 == null) {
        orgChild1 = new OrgChild1();
        orgChild1.orgRootId = orgRoot.id;
        orgChild1.orgChild1Order =
          orgRoot == null || orgRoot.orgChild1s == null || orgRoot.orgChild1s.length == 0
            ? 1
            : orgRoot.orgChild1s.length + 1;
        orgChild1.orgChild1Name = item.orgChild1;
        orgChild1.orgChild1ShortName = item.orgShortname;
        const rank: any = item.orgRank;
        orgChild1.orgChild1Rank = rank;
        orgChild1.orgChild1RankSub = item.orgSubRank;
        orgChild1.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
        orgChild1.DIVISION_CODE = item.DIVISION_CODE;
        orgChild1.SECTION_CODE = item.SECTION_CODE;
        orgChild1.JOB_CODE = item.JOB_CODE;
        orgChild1.orgRevisionId = orgRevision.id;
        orgChild1.createdUserId = request.user.sub;
        orgChild1.createdFullName = request.user.name;
        orgChild1.lastUpdateUserId = request.user.sub;
        orgChild1.lastUpdateFullName = request.user.name;
        orgChild1.createdAt = new Date();
        orgChild1.lastUpdatedAt = new Date();
        await this.orgChild1Repo.save(orgChild1);
      }
      orgChild2.orgChild2Order =
        orgChild1 == null || orgChild1.orgChild2s == null || orgChild1.orgChild2s.length == 0
          ? 1
          : orgChild1.orgChild2s.length + 1;
      orgChild2.orgRootId = orgRoot.id;
      orgChild2.orgChild1Id = orgChild1.id;
      orgChild2.orgChild2Name = item.orgChild2;
      orgChild2.orgChild2ShortName = item.orgShortname;
      const rank: any = item.orgRank;
      orgChild2.orgChild2Rank = rank;
      orgChild2.orgChild2RankSub = item.orgSubRank;
      orgChild2.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
      orgChild2.DIVISION_CODE = item.DIVISION_CODE;
      orgChild2.SECTION_CODE = item.SECTION_CODE;
      orgChild2.JOB_CODE = item.JOB_CODE;
      orgChild2.orgRevisionId = orgRevision.id;
      orgChild2.createdUserId = request.user.sub;
      orgChild2.createdFullName = request.user.name;
      orgChild2.lastUpdateUserId = request.user.sub;
      orgChild2.lastUpdateFullName = request.user.name;
      orgChild2.createdAt = new Date();
      orgChild2.lastUpdatedAt = new Date();
      await this.orgChild2Repo.save(orgChild2);
    }

    //create child3
    const IMPORT_CHILD3 = await this.IMPORT_ORGRepo.find({
      where: {
        orgRoot: Not(""),
        orgChild1: Not(""),
        orgChild2: Not(""),
        orgChild3: Not(""),
      },
    });

    for (const item of IMPORT_CHILD3) {
      const orgChild3 = new OrgChild3();
      let orgRoot = await this.orgRootRepo.findOne({
        where: { orgRootName: item.orgRoot, orgRevisionId: orgRevision.id },
        relations: ["orgChild1s"],
      });
      if (orgRoot == null) {
        orgRoot = new OrgRoot();
        orgRoot.orgRootOrder = order;
        order = order + 1;
        orgRoot.orgRootName = item.orgRoot;
        orgRoot.orgRootShortName = item.orgShortname;
        const rank: any = item.orgRank;
        orgRoot.orgRootRank = rank;
        orgRoot.orgRootRankSub = item.orgSubRank;
        orgRoot.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
        orgRoot.DIVISION_CODE = item.DIVISION_CODE;
        orgRoot.SECTION_CODE = item.SECTION_CODE;
        orgRoot.JOB_CODE = item.JOB_CODE;
        orgRoot.orgRevisionId = orgRevision.id;
        orgRoot.createdUserId = request.user.sub;
        orgRoot.createdFullName = request.user.name;
        orgRoot.lastUpdateUserId = request.user.sub;
        orgRoot.lastUpdateFullName = request.user.name;
        orgRoot.createdAt = new Date();
        orgRoot.lastUpdatedAt = new Date();
        await this.orgRootRepo.save(orgRoot);
      }
      let orgChild1 = await this.orgChild1Repo.findOne({
        where: {
          orgChild1Name: item.orgChild1,
          orgRootId: orgRoot.id,
          orgRevisionId: orgRevision.id,
        },
        relations: ["orgChild2s"],
      });
      if (orgChild1 == null) {
        orgChild1 = new OrgChild1();
        orgChild1.orgChild1Order =
          orgRoot == null || orgRoot.orgChild1s == null || orgRoot.orgChild1s.length == 0
            ? 1
            : orgRoot.orgChild1s.length + 1;
        orgChild1.orgRootId = orgRoot.id;
        orgChild1.orgChild1Name = item.orgChild1;
        orgChild1.orgChild1ShortName = item.orgShortname;
        const rank: any = item.orgRank;
        orgChild1.orgChild1Rank = rank;
        orgChild1.orgChild1RankSub = item.orgSubRank;
        orgChild1.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
        orgChild1.DIVISION_CODE = item.DIVISION_CODE;
        orgChild1.SECTION_CODE = item.SECTION_CODE;
        orgChild1.JOB_CODE = item.JOB_CODE;
        orgChild1.orgRevisionId = orgRevision.id;
        orgChild1.createdUserId = request.user.sub;
        orgChild1.createdFullName = request.user.name;
        orgChild1.lastUpdateUserId = request.user.sub;
        orgChild1.lastUpdateFullName = request.user.name;
        orgChild1.createdAt = new Date();
        orgChild1.lastUpdatedAt = new Date();
        await this.orgChild1Repo.save(orgChild1);
      }
      let orgChild2 = await this.orgChild2Repo.findOne({
        where: {
          orgChild2Name: item.orgChild2,
          orgChild1Id: orgChild1.id,
          orgRootId: orgRoot.id,
          orgRevisionId: orgRevision.id,
        },
        relations: ["orgChild3s"],
      });
      if (orgChild2 == null) {
        orgChild2 = new OrgChild2();
        orgChild2.orgChild2Order =
          orgChild1 == null || orgChild1.orgChild2s == null || orgChild1.orgChild2s.length == 0
            ? 1
            : orgChild1.orgChild2s.length + 1;
        orgChild2.orgRootId = orgRoot.id;
        orgChild2.orgChild1Id = orgChild1.id;
        orgChild2.orgChild2Name = item.orgChild2;
        orgChild2.orgChild2ShortName = item.orgShortname;
        const rank: any = item.orgRank;
        orgChild2.orgChild2Rank = rank;
        orgChild2.orgChild2RankSub = item.orgSubRank;
        orgChild2.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
        orgChild2.DIVISION_CODE = item.DIVISION_CODE;
        orgChild2.SECTION_CODE = item.SECTION_CODE;
        orgChild2.JOB_CODE = item.JOB_CODE;
        orgChild2.orgRevisionId = orgRevision.id;
        orgChild2.createdUserId = request.user.sub;
        orgChild2.createdFullName = request.user.name;
        orgChild2.lastUpdateUserId = request.user.sub;
        orgChild2.lastUpdateFullName = request.user.name;
        orgChild2.createdAt = new Date();
        orgChild2.lastUpdatedAt = new Date();
        await this.orgChild2Repo.save(orgChild2);
      }
      orgChild3.orgChild3Order =
        orgChild2 == null || orgChild2.orgChild3s == null || orgChild2.orgChild3s.length == 0
          ? 1
          : orgChild2.orgChild3s.length + 1;
      orgChild3.orgRootId = orgRoot.id;
      orgChild3.orgChild1Id = orgChild1.id;
      orgChild3.orgChild2Id = orgChild2.id;
      orgChild3.orgChild3Name = item.orgChild3;
      orgChild3.orgChild3ShortName = item.orgShortname;
      const rank: any = item.orgRank;
      orgChild3.orgChild3Rank = rank;
      orgChild3.orgChild3RankSub = item.orgSubRank;
      orgChild3.DEPARTMENT_CODE = item.DEPARTMENT_CODE;
      orgChild3.DIVISION_CODE = item.DIVISION_CODE;
      orgChild3.SECTION_CODE = item.SECTION_CODE;
      orgChild3.JOB_CODE = item.JOB_CODE;
      orgChild3.orgRevisionId = orgRevision.id;
      orgChild3.createdUserId = request.user.sub;
      orgChild3.createdFullName = request.user.name;
      orgChild3.lastUpdateUserId = request.user.sub;
      orgChild3.lastUpdateFullName = request.user.name;
      orgChild3.createdAt = new Date();
      orgChild3.lastUpdatedAt = new Date();
      await this.orgChild3Repo.save(orgChild3);
    }
    return new HttpSuccess();
  }
  /**
   * @summary ทะเบียนประวัติ ข้าราชการ
   */
  @Post("mapposition-Officer")
  async MapPositionOfficer(@Request() request: { user: Record<string, any> }) {
    const officer = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .leftJoinAndSelect("profile.next_holders", "posMaster")
      // .where({citizenId: "1720700018869"})
      .where("posMaster.id IS NULL")
      .getMany();
    let rowCount = 0;
    let _null: any = null;
    let type_: any = null;
    let level_: any = null;
    const orgRevision = await this.orgRevisionRepo.findOne({
      where: { orgRevisionIsCurrent: true, orgRevisionIsDraft: false },
      // where: { id: "30e594c9-a65b-485e-a4c5-71aa497e6b8a" },
    });
    if (orgRevision == null) return new HttpSuccess();
    for await (const _item of officer) {
      rowCount++;
      console.log(rowCount);
      const item = await AppDataSource.getRepository(OFFICER)
        .createQueryBuilder("OFFICER")
        .where({ CIT: _item.citizenId })
        .getOne();

      if (item == null) {
        continue;
      }
      let posMaster = new PosMaster();
      let orgRoot = (await this.orgRootRepo.findOne({
        where: {
          DEPARTMENT_CODE: item.DEPARTMENT_CODE,
          DIVISION_CODE: item.DIVISION_CODE,
          SECTION_CODE: item.SECTION_CODE,
          JOB_CODE: item.JOB_CODE,
          orgRevisionId: orgRevision.id,
        },
      })) as OrgRoot;
      if (orgRoot != null) {
        posMaster.orgRootId = orgRoot.id ?? _null;
      } else {
        let orgChild1 = await this.orgChild1Repo.findOne({
          where: {
            DEPARTMENT_CODE: item.DEPARTMENT_CODE,
            DIVISION_CODE: item.DIVISION_CODE,
            SECTION_CODE: item.SECTION_CODE,
            JOB_CODE: item.JOB_CODE,
            orgRevisionId: orgRevision.id,
          },
        });
        if (orgChild1 != null) {
          posMaster.orgRootId = orgChild1.orgRootId ?? _null;
          posMaster.orgChild1Id = orgChild1.id ?? _null;
        } else {
          let orgChild2 = await this.orgChild2Repo.findOne({
            where: {
              DEPARTMENT_CODE: item.DEPARTMENT_CODE,
              DIVISION_CODE: item.DIVISION_CODE,
              SECTION_CODE: item.SECTION_CODE,
              JOB_CODE: item.JOB_CODE,
              orgRevisionId: orgRevision.id,
            },
          });
          if (orgChild2 != null) {
            posMaster.orgRootId = orgChild2.orgRootId ?? _null;
            posMaster.orgChild1Id = orgChild2.orgChild1Id ?? _null;
            posMaster.orgChild2Id = orgChild2.id ?? _null;
          } else {
            let orgChild3 = await this.orgChild3Repo.findOne({
              where: {
                DEPARTMENT_CODE: item.DEPARTMENT_CODE,
                DIVISION_CODE: item.DIVISION_CODE,
                SECTION_CODE: item.SECTION_CODE,
                JOB_CODE: item.JOB_CODE,
                orgRevisionId: orgRevision.id,
              },
            });
            if (orgChild3 != null) {
              posMaster.orgRootId = orgChild3.orgRootId ?? _null;
              posMaster.orgChild1Id = orgChild3.orgChild1Id ?? _null;
              posMaster.orgChild2Id = orgChild3.orgChild2Id ?? _null;
              posMaster.orgChild3Id = orgChild3.id ?? _null;
            } else {
              let orgChild4 = await this.orgChild4Repo.findOne({
                where: {
                  DEPARTMENT_CODE: item.DEPARTMENT_CODE,
                  DIVISION_CODE: item.DIVISION_CODE,
                  SECTION_CODE: item.SECTION_CODE,
                  JOB_CODE: item.JOB_CODE,
                  orgRevisionId: orgRevision.id,
                },
              });
              if (orgChild4 != null) {
                posMaster.orgRootId = orgChild4.id ?? _null;
                posMaster.orgChild1Id = orgChild4.id ?? _null;
                posMaster.orgChild2Id = orgChild4.id ?? _null;
                posMaster.orgChild3Id = orgChild4.id ?? _null;
                posMaster.orgChild4Id = orgChild4.id ?? _null;
              }
            }
          }
        }
      }

      posMaster.statusReport = "PENDING";
      posMaster.posMasterOrder =
        item.POS_NUM_CODE == "" || item.POS_NUM_CODE == null ? _null : Number(item.POS_NUM_CODE);
      posMaster.isCondition = false;
      posMaster.isStaff = false;
      posMaster.isDirector = false;
      posMaster.isSit = false;
      posMaster.current_holderId = _item.id;
      posMaster.posMasterNo =
        item.POS_NUM_CODE == "" || item.POS_NUM_CODE == null ? _null : Number(item.POS_NUM_CODE);
      posMaster.orgRevisionId = orgRevision.id;
      posMaster.posMasterCreatedAt = new Date();
      posMaster.createdUserId = request.user.sub;
      posMaster.createdFullName = request.user.name;
      posMaster.lastUpdateUserId = request.user.sub;
      posMaster.lastUpdateFullName = request.user.name;
      posMaster.createdAt = new Date();
      posMaster.lastUpdatedAt = new Date();
      if (posMaster.orgRootId == null) {
        continue;
      }
      await this.posMasterRepo.save(posMaster);
      posMaster.ancestorDNA = posMaster.id;
      await this.posMasterRepo.save(posMaster);

      let position = new Position();
      var positionType = "";
      var positionLevel = "";
      if (item.MP_CEE == "21") {
        positionType = "ทั่วไป";
        positionLevel = "ปฏิบัติงาน";
      } else if (item.MP_CEE == "22") {
        positionType = "ทั่วไป";
        positionLevel = "ชำนาญงาน";
      } else if (item.MP_CEE == "23") {
        positionType = "ทั่วไป";
        positionLevel = "อาวุโส";
      } else if (item.MP_CEE == "24") {
        positionType = "ทั่วไป";
        positionLevel = "อาวุโสเฉพาะสายงานที่กำหนด";
      } else if (item.MP_CEE == "25") {
        positionType = "ทั่วไป";
        positionLevel = "ทักษะพิเศษ";
      } else if (item.MP_CEE == "26") {
        positionType = "วิชาการ";
        positionLevel = "ปฏิบัติการ";
      } else if (item.MP_CEE == "27") {
        positionType = "วิชาการ";
        positionLevel = "ชำนาญการ";
      } else if (item.MP_CEE == "28") {
        positionType = "วิชาการ";
        positionLevel = "ชำนาญการพิเศษ";
      } else if (item.MP_CEE == "29") {
        positionType = "วิชาการ";
        positionLevel = "เชี่ยวชาญ";
      } else if (item.MP_CEE == "30") {
        positionType = "วิชาการ";
        positionLevel = "ทรงคุณวุฒิ";
      } else if (item.MP_CEE == "31") {
        positionType = "วิชาการ";
        positionLevel = "ทรงคุณวุฒิเฉพาะสายงานที่กำหนด";
      } else if (item.MP_CEE == "32") {
        positionType = "อำนวยการ";
        positionLevel = "ต้น";
      } else if (item.MP_CEE == "33") {
        positionType = "อำนวยการ";
        positionLevel = "สูง";
      } else if (item.MP_CEE == "34") {
        positionType = "บริหาร";
        positionLevel = "ต้น";
      } else if (item.MP_CEE == "35") {
        positionType = "บริหาร";
        positionLevel = "สูง";
      }
      if (positionType) {
        type_ = await this.posTypeRepo.findOne({
          where: { posTypeName: positionType },
        });
      }
      if (positionLevel) {
        if (type_ == null) {
          level_ = await this.posLevelRepo.findOne({
            where: {
              posLevelName: positionLevel,
            },
          });
        } else {
          level_ = await this.posLevelRepo.findOne({
            where: {
              posLevelName: positionLevel,
              posTypeId: type_.id,
            },
          });
        }
      }

      position.posMasterId = posMaster.id;
      position.isSpecial = false;
      position.positionIsSelected = true;
      position.createdUserId = request.user.sub;
      position.createdFullName = request.user.name;
      position.lastUpdateUserId = request.user.sub;
      position.lastUpdateFullName = request.user.name;
      position.createdAt = new Date();
      position.lastUpdatedAt = new Date();

      const posExecutive = await this.posExecutiveRepo.findOne({
        where: {
          posExecutiveName: item.ADMIN_NAME,
        },
      });
      if (posExecutive == null && item.ADMIN_NAME != "" && item.ADMIN_NAME != null) {
        const posExecutiveOrder = await this.posExecutiveRepo.findOne({
          where: { posExecutivePriority: Not(IsNull()) },
          order: { posExecutivePriority: "DESC" },
        });
        let posExecutive = new PosExecutive();

        posExecutive.posExecutivePriority =
          posExecutiveOrder == null ? _null : posExecutiveOrder.posExecutivePriority + 1;
        posExecutive.posExecutiveName = item.ADMIN_NAME;
        posExecutive.createdUserId = request.user.sub;
        posExecutive.createdFullName = request.user.name;
        posExecutive.lastUpdateUserId = request.user.sub;
        posExecutive.lastUpdateFullName = request.user.name;
        posExecutive.createdAt = new Date();
        posExecutive.lastUpdatedAt = new Date();
        await this.posExecutiveRepo.save(posExecutive);
      }
      position.posExecutiveId = posExecutive == null ? _null : posExecutive.id;
      position.positionName = item.WORK_LINE_NAME == "" ? _null : item.WORK_LINE_NAME;
      position.posTypeId =
        type_ != null && type_.posTypeName == positionType && type_ ? type_.id : null;
      position.posLevelId =
        level_ != null && level_.posLevelName == positionLevel && level_ ? level_.id : null;
      if (position.posTypeId == null || position.posLevelId == null) {
        await this.posMasterRepo.delete({ id: posMaster.id });
        continue;
      }
      await this.positionRepo.save(position);
    }
    return new HttpSuccess();
  }
  /**
   * @summary ทะเบียนประวัติ ลูกจ้าง
   */
  @Post("mapposition-Employee")
  async MapPositionEmployee(@Request() request: { user: Record<string, any> }) {
    const employee = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .leftJoinAndSelect("profile.next_holders", "posMaster")
      // .where({citizenId: "1720700018869"})
      .where("posMaster.id IS NULL")
      .getMany();
    let rowCount = 0;
    let _null: any = null;
    let type_: any = null;
    let level_: any = null;
    const orgRevision = await this.orgRevisionRepo.findOne({
      where: { orgRevisionIsCurrent: true, orgRevisionIsDraft: false },
      // where: { id: "30e594c9-a65b-485e-a4c5-71aa497e6b8a" },
    });
    if (orgRevision == null) return new HttpSuccess();
    for await (const _item of employee) {
      rowCount++;
      console.log(rowCount);
      const item = await AppDataSource.getRepository(EMPLOYEE)
        .createQueryBuilder("EMPLOYEE")
        .where({ CIT: _item.citizenId })
        .getOne();

      if (item == null) {
        continue;
      }

      item.DEPARTMENT_CODE = item.DEPARTMENT_CODE.toString().padStart(2, "0");
      item.DIVISION_CODE = item.DIVISION_CODE.toString().padStart(2, "0");
      item.SECTION_CODE = item.SECTION_CODE.toString().padStart(2, "0");
      item.JOB_CODE = item.JOB_CODE.toString().padStart(2, "0");
      let posMaster = new EmployeePosMaster();
      let orgRoot = (await this.orgRootRepo.findOne({
        where: {
          DEPARTMENT_CODE: item.DEPARTMENT_CODE,
          DIVISION_CODE: item.DIVISION_CODE,
          SECTION_CODE: item.SECTION_CODE,
          JOB_CODE: item.JOB_CODE,
          orgRevisionId: orgRevision.id,
        },
      })) as OrgRoot;
      if (orgRoot != null) {
        posMaster.orgRootId = orgRoot.id ?? _null;
      } else {
        let orgChild1 = await this.orgChild1Repo.findOne({
          where: {
            DEPARTMENT_CODE: item.DEPARTMENT_CODE,
            DIVISION_CODE: item.DIVISION_CODE,
            SECTION_CODE: item.SECTION_CODE,
            JOB_CODE: item.JOB_CODE,
            orgRevisionId: orgRevision.id,
          },
        });
        if (orgChild1 != null) {
          posMaster.orgRootId = orgChild1.orgRootId ?? _null;
          posMaster.orgChild1Id = orgChild1.id ?? _null;
        } else {
          let orgChild2 = await this.orgChild2Repo.findOne({
            where: {
              DEPARTMENT_CODE: item.DEPARTMENT_CODE,
              DIVISION_CODE: item.DIVISION_CODE,
              SECTION_CODE: item.SECTION_CODE,
              JOB_CODE: item.JOB_CODE,
              orgRevisionId: orgRevision.id,
            },
          });
          if (orgChild2 != null) {
            posMaster.orgRootId = orgChild2.orgRootId ?? _null;
            posMaster.orgChild1Id = orgChild2.orgChild1Id ?? _null;
            posMaster.orgChild2Id = orgChild2.id ?? _null;
          } else {
            let orgChild3 = await this.orgChild3Repo.findOne({
              where: {
                DEPARTMENT_CODE: item.DEPARTMENT_CODE,
                DIVISION_CODE: item.DIVISION_CODE,
                SECTION_CODE: item.SECTION_CODE,
                JOB_CODE: item.JOB_CODE,
                orgRevisionId: orgRevision.id,
              },
            });
            if (orgChild3 != null) {
              posMaster.orgRootId = orgChild3.orgRootId ?? _null;
              posMaster.orgChild1Id = orgChild3.orgChild1Id ?? _null;
              posMaster.orgChild2Id = orgChild3.orgChild2Id ?? _null;
              posMaster.orgChild3Id = orgChild3.id ?? _null;
            } else {
              let orgChild4 = await this.orgChild4Repo.findOne({
                where: {
                  DEPARTMENT_CODE: item.DEPARTMENT_CODE,
                  DIVISION_CODE: item.DIVISION_CODE,
                  SECTION_CODE: item.SECTION_CODE,
                  JOB_CODE: item.JOB_CODE,
                  orgRevisionId: orgRevision.id,
                },
              });
              if (orgChild4 != null) {
                posMaster.orgRootId = orgChild4.id ?? _null;
                posMaster.orgChild1Id = orgChild4.id ?? _null;
                posMaster.orgChild2Id = orgChild4.id ?? _null;
                posMaster.orgChild3Id = orgChild4.id ?? _null;
                posMaster.orgChild4Id = orgChild4.id ?? _null;
              }
            }
          }
        }
      }

      // posMaster.statusReport = "PENDING";
      posMaster.posMasterOrder =
        item.POS_NUM_CODE == "" || item.POS_NUM_CODE == null ? _null : Number(item.POS_NUM_CODE);
      // posMaster.isCondition = false;
      posMaster.isStaff = false;
      posMaster.isDirector = false;
      posMaster.isSit = false;
      posMaster.next_holderId = _item.id;
      posMaster.posMasterNo =
        item.POS_NUM_CODE == "" || item.POS_NUM_CODE == null ? _null : Number(item.POS_NUM_CODE);
      posMaster.orgRevisionId = orgRevision.id;
      posMaster.posMasterCreatedAt = new Date();
      posMaster.createdUserId = request.user.sub;
      posMaster.createdFullName = request.user.name;
      posMaster.lastUpdateUserId = request.user.sub;
      posMaster.lastUpdateFullName = request.user.name;
      posMaster.createdAt = new Date();
      posMaster.lastUpdatedAt = new Date();
      if (posMaster.orgRootId == null) {
        continue;
      }
      await this.posMasterEmpRepo.save(posMaster);
      posMaster.ancestorDNA = posMaster.id;
      await this.posMasterEmpRepo.save(posMaster);

      let position = new EmployeePosition();
      var positionType = "";
      // var positionLevel = 0;
      var positionLevel = "0";
      const workLevel = item.WORK_LEVEL;
      const part1 = workLevel.split("/")[0]; // "ส 2"
      const value2 = part1.split(" ")[1]; // "2"
      if (value2) {
        // positionLevel = parseInt(value2);
        positionLevel = value2;
      }
      if (item.CATEGORY_SAL_CODE == "11") {
        positionType = "บริการพื้นฐาน";
      } else if (item.CATEGORY_SAL_CODE == "12") {
        positionType = "สนับสนุน";
      } else if (item.CATEGORY_SAL_CODE == "13") {
        positionType = "ช่าง";
      }
      if (positionType) {
        type_ = await this.posTypeEmpRepo.findOne({
          where: { posTypeName: positionType },
        });
      }
      if (positionType) {
        type_ = await this.posTypeEmpRepo.findOne({
          where: { posTypeName: positionType },
        });
      }
      if (positionLevel) {
        if (type_ == null) {
          level_ = await this.posLevelEmpRepo.findOne({
            where: {
              posLevelName: positionLevel,
            },
          });
        } else {
          level_ = await this.posLevelEmpRepo.findOne({
            where: {
              posLevelName: positionLevel,
              posTypeId: type_.id,
            },
          });
        }
      }

      position.posMasterId = posMaster.id;
      // position.isSpecial = false;
      position.positionIsSelected = true;
      position.createdUserId = request.user.sub;
      position.createdFullName = request.user.name;
      position.lastUpdateUserId = request.user.sub;
      position.lastUpdateFullName = request.user.name;
      position.createdAt = new Date();
      position.lastUpdatedAt = new Date();

      // const posExecutive = await this.posExecutiveRepo.findOne({
      //   where: {
      //     posExecutiveName: item.ADMIN_NAME,
      //   },
      // });
      // if (posExecutive == null && item.ADMIN_NAME != "" && item.ADMIN_NAME != null) {
      //   const posExecutiveOrder = await this.posExecutiveRepo.findOne({
      //     where: { posExecutivePriority: Not(IsNull()) },
      //     order: { posExecutivePriority: "DESC" },
      //   });
      //   let posExecutive = new PosExecutive();

      //   posExecutive.posExecutivePriority =
      //     posExecutiveOrder == null ? _null : posExecutiveOrder.posExecutivePriority + 1;
      //   posExecutive.posExecutiveName = item.ADMIN_NAME;
      //   posExecutive.createdUserId = request.user.sub;
      //   posExecutive.createdFullName = request.user.name;
      //   posExecutive.lastUpdateUserId = request.user.sub;
      //   posExecutive.lastUpdateFullName = request.user.name;
      //   posExecutive.createdAt = new Date();
      //   posExecutive.lastUpdatedAt = new Date();
      //   await this.posExecutiveRepo.save(posExecutive);
      // }
      // position.posExecutiveId = posExecutive == null ? _null : posExecutive.id;
      position.positionName = item.WORK_LINE_NAME == "" ? _null : item.WORK_LINE_NAME.split(" ")[0];
      position.posTypeId =
        type_ != null && type_.posTypeName == positionType && type_ ? type_.id : null;
      position.posLevelId =
        level_ != null && level_.posLevelName == positionLevel && level_ ? level_.id : null;
      if (position.posTypeId == null || position.posLevelId == null) {
        await this.posMasterEmpRepo.delete({ id: posMaster.id });
        continue;
      }
      await this.positionEmpRepo.save(position);
    }
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ข้าราชการ
   */
  @Post("uploadProfileSalary-OfficerEntry")
  async UploadFileSQLSalaryEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    let sqlStatements: string[] = [];

    // const [profiles, total] = await AppDataSource.getRepository(Profile)
    //   .createQueryBuilder("profile")
    //   .select(["profile.citizenId", "profile.id"])
    //   .orderBy("profile.citizenId", "ASC")
    //   //   .skip(0)
    //   //   .take(1000)
    //   .getManyAndCount();
    const profiles = await this.profileRepo.find({
      where: { profileSalary: { isEntry: false } },
      order: {
        citizenId: "ASC",
      },
      select: ["citizenId", "id"],
    });
    // return new HttpSuccess(profiles.length);
    // const filePath = path.join(__dirname, "salaryProfile1.csv");
    // CSV Header
    // let csvData = `"id","createdAt","createdUserId","lastUpdatedAt","lastUpdateUserId","createdFullName","lastUpdateFullName","profileId","profileEmployeeId","order","commandNo","commandYear","commandDateSign","commandDateAffect","commandCode","commandName","posNoAbb","posNo","positionName","positionType","positionLevel","positionCee","orgRoot","orgChild1","orgChild2","orgChild3","orgChild4","positionExecutive","amount","amountSpecial","positionSalaryAmount","mouthSalaryAmount","remark","dateGovernment","isGovernment","commandId","refId","isEntry"\n`;

    // fs.appendFile(filePath, csvData, (err) => {
    //   if (err) {
    //     console.error("Error writing CSV file:", err);
    //   } else {
    //     console.log("Salary profiles successfully written to salaryProfile.csv");
    //   }
    // });
    // await Promise.all(
    //   profiles.map(async (_item) => {
    for await (const _item of profiles) {
      const existingProfile = await this.ProfileSalariesRepo.find({
        where: { posNo: _item.citizenId },
        order: {
          Order: "ASC",
        },
      });
      let order = 1;
      //   await Promise.all(
      //     existingProfile.map(async (item) => {
      for await (const item of existingProfile) {
        rowCount++;
        const profileSalary: any = new ProfileSalary();
        profileSalary.profileId = _item.id;
        profileSalary.order = item.Order;

        profileSalary.commandDateAffect =
          item.Date == null
            ? _null
            : new Date(item.Date.setDate(item.Date.getDate() + 1))
                .toISOString()
                .replace("T", " ")
                .substring(0, 19);
        profileSalary.remark = `${item.SalaryRef} ${item.PositionName}`;
        profileSalary.amount = item.Amount;
        profileSalary.positionSalaryAmount = item.PositionSalaryAmount;
        // const str = item.PosNoName;
        // const parts = str.split(" ");
        // if (parts.length > 1) {
        //   const posNo = parts.at(-1);
        //   const posNoAbb = parts.slice(0, -1).join(" ");
        //   profileSalary.posNoAbb = posNoAbb;
        //   profileSalary.posNo = posNo;
        // }
        profileSalary.posNo = item.PosNoName;
        profileSalary.positionLevel = item.PositionLevelName;
        profileSalary.positionCee = this.canConvertToInt(item.PositionLevelName)
          ? item.PositionLevelName
          : _null;
        profileSalary.positionType = item.PositionTypeName;
        profileSalary.isEntry = true;

        profileSalary.createdUserId = request.user.sub;
        profileSalary.createdFullName = request.user.name;
        profileSalary.lastUpdateUserId = request.user.sub;
        profileSalary.lastUpdateFullName = request.user.name;
        profileSalary.createdAt = new Date().toISOString().split("T")[0];
        profileSalary.lastUpdatedAt = new Date().toISOString().split("T")[0];
        // const result = uuidv7();
        // profileSalary.id = result;

        await this.salaryRepo.save(profileSalary);
        // console.log(profileSalary.commandDateSign);
        // csvData = `"${profileSalary.id || "NULL"}","${profileSalary.createdAt || "NULL"}","${profileSalary.createdUserId || "NULL"}","${profileSalary.lastUpdatedAt || "NULL"}","${profileSalary.lastUpdateUserId || "NULL"}","${profileSalary.createdFullName || "NULL"}","${profileSalary.lastUpdateFullName || "NULL"}","${profileSalary.profileId || "NULL"}","${profileSalary.profileEmployeeId || "NULL"}","${profileSalary.order || "NULL"}","${profileSalary.commandNo || "NULL"}","${profileSalary.commandYear || "NULL"}","${profileSalary.commandDateSign || "NULL"}","${profileSalary.commandDateAffect || "NULL"}","${profileSalary.commandCode || "NULL"}","${profileSalary.commandName || "NULL"}","${profileSalary.posNoAbb || "NULL"}","${profileSalary.posNo || "NULL"}","${profileSalary.positionName || "NULL"}","${profileSalary.positionType || "NULL"}","${profileSalary.positionLevel || "NULL"}","${profileSalary.positionCee || "NULL"}","${profileSalary.orgRoot || "NULL"}","${profileSalary.orgChild1 || "NULL"}","${profileSalary.orgChild2 || "NULL"}","${profileSalary.orgChild3 || "NULL"}","${profileSalary.orgChild4 || "NULL"}","${profileSalary.positionExecutive || "NULL"}","${profileSalary.amount || 0}","${profileSalary.amountSpecial || 0}","${profileSalary.positionSalaryAmount || 0}","${profileSalary.mouthSalaryAmount || 0}","${profileSalary.remark || "NULL"}","${profileSalary.dateGovernment || "NULL"}","${profileSalary.isGovernment || "NULL"}","${profileSalary.commandId || "NULL"}","${profileSalary.refId || "NULL"}","${profileSalary.isEntry || "NULL"}"\n`;

        // fs.appendFile(filePath, csvData.replace('"NULL"', "NULL"), (err) => {
        //   if (err) {
        //     console.error("Error writing CSV file:", err);
        //   } else {
        console.log("Salary profiles successfully written to salaryProfile.csv: " + rowCount);
        //   }
        // });
      }
      console.log(">>>>>>>>>>>>>>>>>>> " + _item.citizenId);
      order = 1;
    }
    return new HttpSuccess();
  }
  canConvertToInt(str: string) {
    try {
      const num = Number(str);
      return Number.isInteger(num);
    } catch (error) {
      return null;
    }
  }

  /**
   * @summary เงินเดือน ข้าราชการ
   */
  @Post("mark-director")
  async UpdateMarkDirector(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    const posMaster = await this.posMasterRepo.find({
      where: { positions: { posExecutiveId: Not(IsNull()) } },
    });

    for await (const _item of posMaster) {
      rowCount++;
      _item.isDirector = true;
      await this.posMasterRepo.save(_item);
      console.log(rowCount);
    }
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ข้าราชการ
   */
  @Post("update-amount")
  async UpdateAmount(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    const profile: any = await this.profileRepo.find({ where: { amount: LessThanOrEqual(0) } });

    for await (const _item of profile) {
      const OFFICER = await this.OFFICERRepo.findOne({
        select: ["CIT", "SALARY"],
        where: { CIT: _item.citizenId },
      });
      if (OFFICER) {
        rowCount++;
        _item.amount = Number(OFFICER.SALARY);
        await this.profileRepo.save(_item);
        console.log(rowCount);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ข้าราชการ
   */
  @Post("update-profileSalary")
  async UpdateProfileSalary(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    const profileSalarys: any = await this.salaryRepo.find({
      where: { refId: Not(IsNull()), profileEmployeeId: IsNull(), posNumCodeSit: IsNull() },
    });

    for await (const _item of profileSalarys) {
      const OFFICER = await this.HR_POSITION_OFFICERRepo.findOne({
        select: ["id", "POS_NUM_CODE_SIT", "POS_NUM_CODE_SIT_ABB"],
        where: { id: _item.refId },
      });
      if (OFFICER) {
        rowCount++;
        _item.posNumCodeSit = OFFICER.POS_NUM_CODE_SIT;
        _item.posNumCodeSitAbb = OFFICER.POS_NUM_CODE_SIT_ABB;
        await this.salaryRepo.save(_item);
        console.log(rowCount);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เงินเดือน ข้าราชการ
   */
  @Post("update-profileSalaryEmp")
  async UpdateProfileSalaryEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    const profileSalarys: any = await this.salaryRepo.find({
      where: { refId: Not(IsNull()), profileId: IsNull(), posNumCodeSit: IsNull() },
    });

    for await (const _item of profileSalarys) {
      const EMPLOYEE = await this.HR_POSITION_EMPLOYEERepo.findOne({
        select: ["id", "POS_NUM_CODE_SIT", "POS_NUM_CODE_SIT_ABB"],
        where: { id: _item.refId },
      });
      if (EMPLOYEE) {
        rowCount++;
        _item.posNumCodeSit = EMPLOYEE.POS_NUM_CODE_SIT;
        _item.posNumCodeSitAbb = EMPLOYEE.POS_NUM_CODE_SIT_ABB;
        await this.salaryRepo.save(_item);
        console.log(rowCount);
      }
    }
    return new HttpSuccess();
  }

  OrgRank(value: string) {
    switch (value.trim().toUpperCase()) {
      case "DEPARTMENT":
        return "หน่วยงาน";
      case "OFFICE":
        return "ส่วนราชการระดับกอง/สำนักงาน หรือเทียบเท่า";
      case "DIVISION":
        return "ส่วนราชการระดับส่วน/กลุ่มภารกิจ";
      case "SECTION":
        return "ส่วนราชการระดับฝ่าย/กลุ่มงาน หรือเทียบเท่า";
      default:
        return "";
    }
  }
  OrgRankSub(value: string) {
    switch (value.trim().toUpperCase()) {
      case "BUREAU":
        return "สำนัก";
      case "OFFICE":
        return "สำนักงาน";
      case "DISTRICT":
        return "สำนักงานเขต";
      case "DIVISION":
        return "กอง";
      case "INSTITUTION":
        return "สถาบัน";
      case "HOSPITAL":
        return "โรงพยาบาล";
      case "CENTER":
        return "ศูนย์";
      case "MEDICAL":
        return "ศูนย์บริการการแพทย์";
      case "HEALTHMAJOR":
        return "ศูนย์บริการสาธารณสุข";
      case "UNIT":
        return "หน่วย";
      case "SECTION":
        return "ส่วน";
      case "FACTION":
        return "ฝ่าย";
      case "GROUPWORK":
        return "กลุ่มงาน";
      case "HEALTHBRANCH":
        return "ศูนย์บริการสาธารณสุขสาขา";
      case "TRAINING":
        return "ศูนย์ฝึกอาชีพ";
      case "SCHOOL":
        return "โรงเรียนฝึกอาชีพ";
      case "ELDERLY":
        return "บ้านพักผู้สูงอายุ";
      case "PARK":
        return "สวนสาธารณะ";
      case "FIRESTATION":
        return "สถานีดับเพลิง";
      case "WORK":
        return "งาน";
      case "PRIMARYSCHOOL":
        return "โรงเรียนประถมศึกษา";
      case "SECONDARYSCHOOL":
        return "โรงเรียนมัธยมศึกษา";
      case "MISSION":
        return "กลุ่มภารกิจ";
      default:
        return "";
    }
  }
  /**
   * @summary Import Org
   */
  @Post("ExportOrg")
  async ExportOrg(@Request() request: { user: Record<string, any> }) {
    const orgRevision = await this.orgRevisionRepo.findOne({
      where: { orgRevisionIsCurrent: false, orgRevisionIsDraft: true },
      // where: { id: "30e594c9-a65b-485e-a4c5-71aa497e6b8a" },
    });
    if (orgRevision == null) return new HttpSuccess();
    //create root
    const orgRaw = await this.orgRootRepo.find({
      where: { orgRevisionId: orgRevision.id },
      order: {
        orgRootOrder: "ASC",
      },
    });
    let order = 1;
    let result = [];
    for await (const item of orgRaw) {
      console.log(item.orgRootOrder);
      const data = {
        ORDER: order,
        HRMS_DEP_CODE: "",
        HRMS_DIV_CODE: "",
        DEPARTMENT_CODE: item.DEPARTMENT_CODE,
        DIVISION_CODE: item.DIVISION_CODE,
        SECTION_CODE: item.SECTION_CODE,
        JOB_CODE: item.JOB_CODE,
        orgRoot: item.orgRootName,
        orgChild1: "",
        orgChild2: "",
        orgChild3: "",
        orgChild4: "",
        orgShortname: item.orgRootShortName,
        orgRank: this.OrgRank(item.orgRootRank),
        orgSubRank: this.OrgRankSub(item.orgRootRankSub),
      };
      await result.push(data);
      order++;
      const org1Raw = await this.orgChild1Repo.find({
        where: { orgRevisionId: orgRevision.id, orgRootId: item.id },
        order: {
          orgChild1Order: "ASC",
        },
        relations: ["orgRoot"],
      });
      for await (const item1 of org1Raw) {
        const data1 = {
          ORDER: order,
          HRMS_DEP_CODE: "",
          HRMS_DIV_CODE: "",
          DEPARTMENT_CODE: item1.DEPARTMENT_CODE,
          DIVISION_CODE: item1.DIVISION_CODE,
          SECTION_CODE: item1.SECTION_CODE,
          JOB_CODE: item1.JOB_CODE,
          orgRoot: item1.orgRoot.orgRootName,
          orgChild1: item1.orgChild1Name,
          orgChild2: "",
          orgChild3: "",
          orgChild4: "",
          orgShortname: item1.orgChild1ShortName,
          orgRank: this.OrgRank(item1.orgChild1Rank),
          orgSubRank: this.OrgRankSub(item1.orgChild1RankSub),
        };
        await result.push(data1);
        order++;
        const org2Raw = await this.orgChild2Repo.find({
          where: { orgRevisionId: orgRevision.id, orgRootId: item.id, orgChild1Id: item1.id },
          order: {
            orgChild2Order: "ASC",
          },
          relations: ["orgRoot", "orgChild1"],
        });
        for await (const item2 of org2Raw) {
          const data2 = {
            ORDER: order,
            HRMS_DEP_CODE: "",
            HRMS_DIV_CODE: "",
            DEPARTMENT_CODE: item2.DEPARTMENT_CODE,
            DIVISION_CODE: item2.DIVISION_CODE,
            SECTION_CODE: item2.SECTION_CODE,
            JOB_CODE: item2.JOB_CODE,
            orgRoot: item2.orgRoot.orgRootName,
            orgChild1: item2.orgChild1.orgChild1Name,
            orgChild2: item2.orgChild2Name,
            orgChild3: "",
            orgChild4: "",
            orgShortname: item2.orgChild2ShortName,
            orgRank: this.OrgRank(item2.orgChild2Rank),
            orgSubRank: this.OrgRankSub(item2.orgChild2RankSub),
          };
          await result.push(data2);
          order++;
          const org3Raw = await this.orgChild3Repo.find({
            where: {
              orgRevisionId: orgRevision.id,
              orgRootId: item.id,
              orgChild1Id: item1.id,
              orgChild2Id: item2.id,
            },
            order: {
              orgChild3Order: "ASC",
            },
            relations: ["orgRoot", "orgChild1", "orgChild2"],
          });
          for await (const item3 of org3Raw) {
            const data3 = {
              ORDER: order,
              HRMS_DEP_CODE: "",
              HRMS_DIV_CODE: "",
              DEPARTMENT_CODE: item3.DEPARTMENT_CODE,
              DIVISION_CODE: item3.DIVISION_CODE,
              SECTION_CODE: item3.SECTION_CODE,
              JOB_CODE: item3.JOB_CODE,
              orgRoot: item3.orgRoot.orgRootName,
              orgChild1: item3.orgChild1.orgChild1Name,
              orgChild2: item3.orgChild2.orgChild2Name,
              orgChild3: item3.orgChild3Name,
              orgChild4: "",
              orgShortname: item3.orgChild3ShortName,
              orgRank: this.OrgRank(item3.orgChild3Rank),
              orgSubRank: this.OrgRankSub(item3.orgChild3RankSub),
            };
            await result.push(data3);
            order++;
            const org4Raw = await this.orgChild4Repo.find({
              where: {
                orgRevisionId: orgRevision.id,
                orgRootId: item.id,
                orgChild1Id: item1.id,
                orgChild2Id: item2.id,
                orgChild3Id: item3.id,
              },
              order: {
                orgChild4Order: "ASC",
              },
              relations: ["orgRoot", "orgChild1", "orgChild2", "orgChild3"],
            });
            for await (const item4 of org4Raw) {
              const data4 = {
                ORDER: order,
                HRMS_DEP_CODE: "",
                HRMS_DIV_CODE: "",
                DEPARTMENT_CODE: item4.DEPARTMENT_CODE,
                DIVISION_CODE: item4.DIVISION_CODE,
                SECTION_CODE: item4.SECTION_CODE,
                JOB_CODE: item4.JOB_CODE,
                orgRoot: item4.orgRoot.orgRootName,
                orgChild1: item4.orgChild1.orgChild1Name,
                orgChild2: item4.orgChild2.orgChild2Name,
                orgChild3: item4.orgChild3.orgChild3Name,
                orgChild4: item4.orgChild4Name,
                orgShortname: item4.orgChild4ShortName,
                orgRank: this.OrgRank(item4.orgChild4Rank),
                orgSubRank: this.OrgRankSub(item4.orgChild4RankSub),
              };
              await result.push(data4);
              order++;
            }
          }
        }
      }
    }
    return new HttpSuccess({
      template: "exportRawOrg",
      reportName: "exportRawOrg",
      data: { data: result },
    });
  }

  /**
   * @summary วินัย ข้าราชการ
   */
  @Post("uploadProfileDiscipline-Officer")
  async UploadFileSQLDiscipline(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_DISCIPLINERepo.find({
        where: { CIT: _item.citizenId },
      });

      for (const item of existingProfile) {
        let CREATE_DATE = "";
        if (item.CREATE_DATE) {
          const [datePart] = item.CREATE_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          CREATE_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        rowCount++;
        const discipline = new ProfileDiscipline();
        discipline.profileId = _item.id;
        discipline.detail = item.REASON_FLAW;
        discipline.date = CREATE_DATE == "" ? _null : new Date(CREATE_DATE);
        discipline.createdUserId = request.user.sub;
        discipline.createdFullName = request.user.name;
        discipline.lastUpdateUserId = request.user.sub;
        discipline.lastUpdateFullName = request.user.name;
        discipline.createdAt = new Date();
        discipline.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileDisciplineRepo.save(await discipline);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary วินัย ลูกจ้างประจำ
   */
  @Post("uploadProfileDiscipline-Employee")
  async UploadFileSQLDisciplineEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_DISCIPLINE_EMPRepo.find({
        where: { CIT: _item.citizenId },
      });

      for (const item of existingProfile) {
        let CREATE_DATE = "";
        if (item.CREATE_DATE) {
          const [datePart] = item.CREATE_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          CREATE_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        rowCount++;
        const discipline = new ProfileDiscipline();
        discipline.profileEmployeeId = _item.id;
        discipline.detail = item.REASON_FLAW;
        discipline.date = CREATE_DATE == "" ? _null : new Date(CREATE_DATE);
        discipline.createdUserId = request.user.sub;
        discipline.createdFullName = request.user.name;
        discipline.lastUpdateUserId = request.user.sub;
        discipline.lastUpdateFullName = request.user.name;
        discipline.createdAt = new Date();
        discipline.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileDisciplineRepo.save(await discipline);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary วินัย ลูกจ้างชั่วคราว
   */
  @Post("uploadProfileDiscipline-EmployeeTemp")
  async UploadFileSQLDisciplineEmpTemp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .where({ employeeClass: "TEMP" })
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_DISCIPLINE_EMPTEMPRepo.find({
        where: { CIT: _item.citizenId },
      });

      for (const item of existingProfile) {
        let CREATE_DATE = "";
        if (item.CREATE_DATE) {
          const [datePart] = item.CREATE_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          CREATE_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        rowCount++;
        const discipline = new ProfileDiscipline();
        discipline.profileEmployeeId = _item.id;
        discipline.detail = item.REASON_FLAW;
        discipline.date = CREATE_DATE == "" ? _null : new Date(CREATE_DATE);
        discipline.createdUserId = request.user.sub;
        discipline.createdFullName = request.user.name;
        discipline.lastUpdateUserId = request.user.sub;
        discipline.lastUpdateFullName = request.user.name;
        discipline.createdAt = new Date();
        discipline.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileDisciplineRepo.save(await discipline);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เครื่องราช ข้าราชการ
   */
  @Post("uploadProfileInsignia-Officer")
  async UploadFileSQLInsignia(@Request() request: { user: Record<string, any> }) {
    console.log(">>>>>>>>>>>>>>>>>>>");
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_INSIGNIARepo.find({
        where: { CIT: _item.citizenId },
      });

      for (const item of existingProfile) {
        let DECORATION_DATE = "";
        if (item.DECORATION_DATE) {
          const [datePart] = item.DECORATION_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          DECORATION_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        let PERMISSION_DATE = "";
        if (item.PERMISSION_DATE) {
          const [datePart] = item.PERMISSION_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          PERMISSION_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        let CREATE_DATE = "";
        let _year = null;
        if (item.CREATE_DATE) {
          const [datePart] = item.CREATE_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          CREATE_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
          _year = year;
        }
        rowCount++;
        const insignia = new ProfileInsignia();
        insignia.profileId = _item.id;
        insignia.year = _year == null ? _null : Number(_year);
        insignia.volume = item.BOOK;
        insignia.section = item.PART;
        insignia.page = item.PAGE;
        insignia.receiveDate = PERMISSION_DATE == "" ? _null : new Date(PERMISSION_DATE);
        insignia.dateAnnounce = DECORATION_DATE == "" ? _null : new Date(DECORATION_DATE);
        insignia.issue = item.ISSUE;
        const insigniaMeta = await this.insigniaRepo.findOne({
          where: { name: item.DECORATIONS_NAME },
        });
        if (insigniaMeta != null) {
          insignia.insigniaId = insigniaMeta.id;
        }
        insignia.createdUserId = request.user.sub;
        insignia.createdFullName = request.user.name;
        insignia.lastUpdateUserId = request.user.sub;
        insignia.lastUpdateFullName = request.user.name;
        insignia.createdAt = new Date();
        insignia.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileInsigniaRepo.save(await insignia);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เครื่องราช ลูกจ้างประจำ
   */
  @Post("uploadProfileInsignia-Employee")
  async UploadFileSQLInsigniaEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_INSIGNIA_EMPRepo.find({
        where: { CIT: _item.citizenId },
      });

      for (const item of existingProfile) {
        let DECORATION_DATE = "";
        if (item.DECORATION_DATE) {
          const [datePart] = item.DECORATION_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          DECORATION_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        let CREATE_DATE = "";
        let _year = null;
        if (item.CREATE_DATE) {
          const [datePart] = item.CREATE_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          CREATE_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
          _year = year;
        }
        rowCount++;
        const insignia = new ProfileInsignia();
        insignia.profileEmployeeId = _item.id;
        insignia.year = _year == null ? _null : Number(_year);
        insignia.volume = item.BOOK;
        insignia.section = item.PART;
        insignia.page = item.PAGE;
        insignia.receiveDate = CREATE_DATE == "" ? _null : new Date(CREATE_DATE);
        insignia.dateAnnounce = DECORATION_DATE == "" ? _null : new Date(DECORATION_DATE);
        insignia.issue = item.ISSUE;
        const insigniaMeta = await this.insigniaRepo.findOne({
          where: { name: item.DECORATIONS_NAME },
        });
        if (insigniaMeta != null) {
          insignia.insigniaId = insigniaMeta.id;
        }
        insignia.createdUserId = request.user.sub;
        insignia.createdFullName = request.user.name;
        insignia.lastUpdateUserId = request.user.sub;
        insignia.lastUpdateFullName = request.user.name;
        insignia.createdAt = new Date();
        insignia.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileInsigniaRepo.save(await insignia);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เปลี่ยนชื่อ ข้าราชการ
   */
  @Post("uploadProfileChangeName-Officer")
  async UploadFileSQLChangeName(@Request() request: { user: Record<string, any> }) {
    console.log(">>>>>>>>>>>>>>>>>>>");
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_CHANGENAMERepo.find({
        where: { CIT: _item.citizenId },
        // order: { SEQ: "ASC" },
      });

      for (const item of existingProfile) {
        let EFFECT_DATE = "";
        if (item.EFFECT_DATE) {
          const [datePart] = item.EFFECT_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          EFFECT_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        rowCount++;
        const changeName = new ProfileChangeName();
        changeName.profileId = _item.id;
        changeName.prefix = item.NEW_RANK_NAME == "" ? item.LAST_RANK_NAME : item.NEW_RANK_NAME;
        changeName.firstName = item.NEW_FNAME == "" ? item.LAST_FNAME : item.NEW_FNAME;
        changeName.lastName = item.NEW_LNAME == "" ? item.LAST_LNAME : item.NEW_LNAME;
        changeName.createdUserId = request.user.sub;
        changeName.createdFullName = request.user.name;
        changeName.lastUpdateUserId = request.user.sub;
        changeName.lastUpdateFullName = request.user.name;
        changeName.createdAt = EFFECT_DATE == "" ? new Date() : new Date(EFFECT_DATE);
        changeName.lastUpdatedAt = EFFECT_DATE == "" ? new Date() : new Date(EFFECT_DATE);
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileChangeNameRepo.save(await changeName);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เปลี่ยนชื่อ ลูกจ้างประจำ
   */
  @Post("uploadProfileChangeName-Employee")
  async UploadFileSQLChangeNameEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_CHANGENAME_EMPRepo.find({
        where: { CIT: _item.citizenId },
        // order: { SEQ: "ASC" },
      });

      for (const item of existingProfile) {
        let EFFECT_DATE = "";
        if (item.EFFECT_DATE) {
          const [datePart] = item.EFFECT_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          EFFECT_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        rowCount++;
        const changeName = new ProfileChangeName();
        changeName.profileEmployeeId = _item.id;
        changeName.prefix = item.NEW_RANK_NAME == "" ? item.LAST_RANK_NAME : item.NEW_RANK_NAME;
        changeName.firstName = item.NEW_FNAME == "" ? item.LAST_FNAME : item.NEW_FNAME;
        changeName.lastName = item.NEW_LNAME == "" ? item.LAST_LNAME : item.NEW_LNAME;
        changeName.createdUserId = request.user.sub;
        changeName.createdFullName = request.user.name;
        changeName.lastUpdateUserId = request.user.sub;
        changeName.lastUpdateFullName = request.user.name;
        changeName.createdAt = EFFECT_DATE == "" ? new Date() : new Date(EFFECT_DATE);
        changeName.lastUpdatedAt = EFFECT_DATE == "" ? new Date() : new Date(EFFECT_DATE);
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileChangeNameRepo.save(await changeName);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เปลี่ยนชื่อ ลูกจ้างชั่วคราว
   */
  @Post("uploadProfileChangeName-EmployeeTemp")
  async UploadFileSQLChangeNameEmpTemp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .where({ employeeClass: "TEMP" })
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_CHANGENAME_EMPTEMPRepo.find({
        where: { CIT: _item.citizenId },
        // order: { SEQ: "ASC" },
      });

      for (const item of existingProfile) {
        let EFFECT_DATE = "";
        if (item.EFFECT_DATE) {
          const [datePart] = item.EFFECT_DATE.split(" ");
          const [day, month, year] = datePart.split("/");
          EFFECT_DATE = `${year}-${month.padStart(2, "0")}-${day.padStart(2, "0")}`;
        }
        rowCount++;
        const changeName = new ProfileChangeName();
        changeName.profileEmployeeId = _item.id;
        changeName.prefix = item.NEW_RANK_NAME == "" ? item.LAST_RANK_NAME : item.NEW_RANK_NAME;
        changeName.firstName = item.NEW_FNAME == "" ? item.LAST_FNAME : item.NEW_FNAME;
        changeName.lastName = item.NEW_LNAME == "" ? item.LAST_LNAME : item.NEW_LNAME;
        changeName.createdUserId = request.user.sub;
        changeName.createdFullName = request.user.name;
        changeName.lastUpdateUserId = request.user.sub;
        changeName.lastUpdateFullName = request.user.name;
        changeName.createdAt = EFFECT_DATE == "" ? new Date() : new Date(EFFECT_DATE);
        changeName.lastUpdatedAt = EFFECT_DATE == "" ? new Date() : new Date(EFFECT_DATE);
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileChangeNameRepo.save(await changeName);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary บุตร ข้าราชการ
   */
  @Post("uploadProfileChild-Officer")
  async UploadFileSQLChild(@Request() request: { user: Record<string, any> }) {
    console.log(">>>>>>>>>>>>>>>>>>>");
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(Profile)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_CHILDENRepo.find({
        where: { CIT: _item.citizenId },
        order: { SEQ: "ASC" },
      });

      for (const item of existingProfile) {
        rowCount++;
        const child = new ProfileChildren();
        child.profileId = _item.id;
        child.childrenPrefix = item.RANK_NAME;
        child.childrenFirstName = item.CHILD_FNAME;
        child.childrenLastName = item.CHILD_LNAME;
        child.childrenLive = item.LIFE_STATUS == "2" ? false : true;
        child.createdUserId = request.user.sub;
        child.createdFullName = request.user.name;
        child.lastUpdateUserId = request.user.sub;
        child.lastUpdateFullName = request.user.name;
        child.createdAt = new Date();
        child.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileChildrenRepo.save(await child);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary บุตร ลูกจ้างประจำ
   */
  @Post("uploadProfileChild-Employee")
  async UploadFileSQLChildEmp(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;

    const [profiles, total] = await AppDataSource.getRepository(ProfileEmployee)
      .createQueryBuilder("profile")
      .select(["profile.citizenId", "profile.id"])
      .orderBy("profile.citizenId", "ASC")
      .getManyAndCount();

    for (const _item of profiles) {
      const existingProfile = await this.HR_CHILDEN_EMPRepo.find({
        where: { CIT: _item.citizenId },
        order: { SEQ: "ASC" },
      });

      for (const item of existingProfile) {
        rowCount++;
        const child = new ProfileChildren();
        child.profileEmployeeId = _item.id;
        child.childrenPrefix = item.RANK_NAME;
        child.childrenFirstName = item.CHILD_FNAME;
        child.childrenLastName = item.CHILD_LNAME;
        child.childrenLive = item.LIFE_STATUS == "2" ? false : true;
        child.createdUserId = request.user.sub;
        child.createdFullName = request.user.name;
        child.lastUpdateUserId = request.user.sub;
        child.lastUpdateFullName = request.user.name;
        child.createdAt = new Date();
        child.lastUpdatedAt = new Date();
        console.log(">>>>>>>>>>>>>>>>>>>" + rowCount);
        await this.profileChildrenRepo.save(await child);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary ประกาศเกียรติคุณ ข้าราชการ
   */
  @Post("uploadProfileHonor-OfficerEntry")
  async UploadFileSQLHonorEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileHonorsRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileHonor();
        profile.profileId = item.id;
        profile.detail = _item.detail;
        profile.issueDate = _item.issueDate;
        profile.issuer = _item.issuer;
        profile.refCommandDate = _item.refCommandDate;
        profile.refCommandNo = _item.refCommandNo;
        profile.isDate = _item.isDate;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.HonorRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ข้อมูลอื่นๆ ข้าราชการ
   */
  @Post("uploadProfileOther-OfficerEntry")
  async UploadFileSQLOtherEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileOthersRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileOther();
        profile.profileId = item.id;
        profile.detail = _item.detail;
        profile.date = _item.date;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.OtherRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary บันทึกวันที่ไม่ได้รับเงินเงินเดือนฯ ข้าราชการ
   */
  @Post("uploadProfileNopaid-OfficerEntry")
  async UploadFileSQLNopaidEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileNopaidsRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileNopaid();
        profile.profileId = item.id;
        profile.date = _item.date;
        profile.detail = _item.detail;
        profile.reference = _item.reference;
        profile.refCommandDate = _item.refCommandDate;
        profile.refCommandNo = _item.refCommandNo;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.NopaidRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ความสามารถพิเศษ ข้าราชการ
   */
  @Post("uploadProfileAbility-OfficerEntry")
  async UploadFileSQLAbilityEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileAbilitysRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileAbility();
        profile.profileId = item.id;
        profile.remark = _item.remark;
        profile.detail = _item.detail;
        profile.reference = _item.reference;
        profile.dateStart = _item.dateStart;
        profile.dateEnd = _item.dateEnd;
        profile.field = _item.field;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.AbilityRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ปฏิบัติราชการพิเศษ ข้าราชการ
   */
  @Post("uploadProfileDuty-OfficerEntry")
  async UploadFileSQLDutyEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileDutysRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileDuty();
        profile.profileId = item.id;
        profile.dateStart = _item.dateStart;
        profile.dateEnd = _item.dateEnd;
        profile.detail = _item.detail;
        profile.reference = _item.reference;
        profile.refCommandDate = _item.refCommandDate;
        profile.refCommandNo = _item.refCommandNo;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.DutyRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary วินัย ข้าราชการ
   */
  @Post("uploadProfileDiscipline-OfficerEntry")
  async UploadFileSQLDisciplineEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileDisciplinesRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileDiscipline();
        profile.profileId = item.id;
        profile.date = _item.date;
        profile.level = _item.level;
        profile.detail = _item.detail;
        profile.refCommandDate = _item.refCommandDate;
        profile.refCommandNo = _item.refCommandNo;
        profile.unStigma = _item.unStigma;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.DisciplineRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ใบอนุญาตประกอบวิชาชีพ ข้าราชการ
   */
  @Post("uploadProfileCertificate-OfficerEntry")
  async UploadFileSQLCertificateEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileCertificatesRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileCertificate();
        profile.profileId = item.id;
        profile.expireDate = _item.expireDate;
        profile.issueDate = _item.issueDate;
        profile.certificateNo = _item.certificateNo;
        profile.certificateType = _item.certificateType;
        profile.issuer = _item.issuer;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.CertificateRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ประวัติการเปลี่ยนชื่อ - นามสกุล ข้าราชการ
   */
  @Post("uploadProfileChangeName-OfficerEntry")
  async UploadFileSQLChangeNameEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileChangeNamesRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileChangeName();
        profile.profileId = item.id;
        profile.prefix = _item.prefix;
        profile.firstName = _item.firstName;
        profile.lastName = _item.lastName;
        profile.status = _item.status;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.ChangeNameRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary ข้อมูลบุตร ข้าราชการ
   */
  @Post("uploadProfileChildren-OfficerEntry")
  async UploadFileSQLChildrenEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileChildrensRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileChildren();
        profile.profileId = item.id;
        profile.childrenCareer = _item.childrenCareer;
        profile.childrenFirstName = _item.childrenFirstName;
        profile.childrenLastName = _item.childrenLastName;
        profile.childrenPrefix = _item.childrenPrefix;
        profile.childrenLive = _item.childrenLive;
        profile.childrenCitizenId = _item.childrenCitizenId;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.ChildrenRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary การศึกษา ข้าราชการ
   */
  @Post("uploadProfileEducation-OfficerEntry")
  async UploadFileSQLEducationEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.profileRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      // const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.ProfileEducationsRepo.find({
        where: { createdFullName: _item.citizenId },
        order: {
          startDate: "ASC",
        },
      });
      const eduLevel = await this.profileEducationRepo.findOne({
        where: { profileId: _item.id, isDeleted: false },
        order: {
          startDate: "DESC",
        },
      });
      let order = 1;
      if (eduLevel != null) {
        order = eduLevel.level + 1;
      }
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileEducation();
        profile.profileId = _item.id;
        profile.country = item.country;
        profile.degree = item.degree;
        profile.duration = item.duration;
        profile.durationYear = item.durationYear;
        profile.field = item.field;
        profile.finishDate = item.finishDate;
        profile.fundName = item.fundName;
        profile.gpa = item.gpa;
        profile.institute = item.institute;
        profile.other = item.other;
        profile.startDate = item.startDate;
        profile.endDate = item.endDate;
        profile.educationLevel = item.educationLevel;
        // profile.educationLevelId = item.educationLevelId;
        profile.positionPath = item.positionPath;
        profile.note = item.note;
        profile.positionPathId = item.positionPathId;
        profile.isDate = item.isDate;
        profile.isEducation = item.isEducation;
        profile.level = order;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.EducationRepo.save(profile);
        order = order + 1;
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary เครื่องราช ข้าราชการ
   */
  @Post("uploadProfileInsignia-OfficerEntry")
  async UploadFileSQLInsigniaEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileInsigniasRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileInsignia();
        profile.profileId = item.id;
        profile.year = _item.year;
        profile.no = _item.no;
        profile.volume = _item.volume;
        profile.section = _item.section;
        profile.page = _item.page;
        profile.receiveDate = _item.receiveDate;
        profile.dateAnnounce = _item.dateAnnounce;
        profile.issue = _item.issue;
        profile.volumeNo = _item.volumeNo;
        profile.refCommandDate = _item.refCommandDate;
        profile.refCommandNo = _item.refCommandNo;
        profile.note = _item.note;
        profile.insigniaId = _item.insigniaId;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.InsigniaRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary เครื่องราช ข้าราชการ
   */
  @Post("uploadProfileTraining-OfficerEntry")
  async UploadFileSQLTrainingEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileTrainingsRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileTraining();
        profile.profileId = item.id;
        profile.startDate = _item.startDate;
        profile.endDate = _item.endDate;
        profile.numberOrder = _item.numberOrder;
        profile.topic = _item.topic;
        profile.place = _item.place;
        profile.dateOrder = _item.dateOrder;
        profile.department = _item.department;
        profile.duration = _item.duration;
        profile.name = _item.name;
        profile.yearly = _item.yearly;
        profile.isDate = _item.isDate;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.TrainingRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary การลา ข้าราชการ
   */
  @Post("uploadProfileLeaveSummary-OfficerEntry")
  async UploadFileSQLLeaveSummaryEntry(@Request() request: { user: Record<string, any> }) {
    let rowCount = 0;
    let _null: any = null;
    const existingProfile = await this.ProfileLeaveSummarysRepo.find({
      // order: {
      //   citizenId: "ASC",
      // },
    });
    for await (const _item of existingProfile) {
      const citizenId: any = _item.createdFullName ?? "";
      const profiles = await this.profileRepo.find({
        where: { citizenId: citizenId },
        // order: {
        //   Order: "ASC",
        // },
      });
      let order = 1;
      for await (const item of profiles) {
        rowCount++;
        const profile: any = new ProfileLeave();
        profile.profileId = item.id;
        // profile.totalLeave = _item.insigniaId;
        profile.status = "approve";
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;
        // profile.totalLeave = _item.insigniaId;

        profile.isEntry = true;
        profile.createdUserId = request.user.sub;
        profile.createdFullName = request.user.name;
        profile.lastUpdateUserId = request.user.sub;
        profile.lastUpdateFullName = request.user.name;
        profile.createdAt = new Date().toISOString().split("T")[0];
        profile.lastUpdatedAt = new Date().toISOString().split("T")[0];
        await this.LeaveSummaryRepo.save(profile);
        console.log("profiles successfully written to Profile.csv: " + rowCount);
      }
      order = 1;
    }
    return new HttpSuccess();
  }

  /**
   * @summary รวม entry กับตัวหลัก
   */
  @Post("tranferEntryToMain")
  async tranferEntryToMain(@Request() request: { user: Record<string, any> }) {
    const _profiles = await this.profileRepo
      .createQueryBuilder("profile")
      .leftJoinAndSelect("profile.profileSalary", "profileSalary")
      .leftJoinAndSelect("profile.profileSalaryTemp", "profileSalaryTemp")
      .where("profileSalary.isEntry = :isEntry", { isEntry: true })
      .andWhere("profile.statusCheckEdit != :status", { status: "EDITED" })
      .andWhere("profileSalaryTemp.id IS NULL") // 💥 filter ให้ profileSalaryTemp ว่าง
      .orderBy("profileSalary.commandDateAffect", "ASC")
      .addOrderBy("profileSalary.order", "ASC")
      .getMany();
    // const _profiles = await this.salaryTempRepo
    //   .createQueryBuilder("profileSalaryTemp")
    //   .select("profileSalaryTemp.profileId", "profileId")
    //   .where("profileSalaryTemp.createdAt > :date", { date: "2025-06-24" })
    //   .groupBy("profileSalaryTemp.profileId")
    //   .getRawMany();

    const profiles = await this.profileRepo.find({
      where: {
        id: In(_profiles.map((x) => x.id)),
        // profileSalary: { isEntry: Not(true) },
        statusCheckEdit: Not("EDITED"),
      },
      order: { profileSalary: { commandDateAffect: "ASC", order: "ASC" } },
      relations: ["profileSalary"],
    });
    console.log(profiles.length);
    for await (const item of profiles) {
      console.log(item.id);
      // 2. จัดกลุ่มข้อมูลตามวันที่คำสั่งมีผล
      const groupedByDate = this.groupOrdersByDate(item.profileSalary);
      // 3. ประมวลผลแต่ละกลุ่ม
      const processedOrders: any = [];
      let num = 0;
      for (let [date, orders] of Object.entries(groupedByDate) as any) {
        if (orders.length == 1) {
          num = num + 1;
          //save
          orders[0].isDelete = false;
          processedOrders.push(orders[0]);
          continue;
        }
        // หาแถวหลัก (ที่มีเลขที่คำสั่ง)
        const mainOrder: ProfileSalary[] = orders.filter((order: ProfileSalary) => order.commandNo);
        const orderOrigi: ProfileSalary[] = [];
        // ข้ามถ้าไม่มีแถวหลัก
        if (mainOrder.length == 0) {
          for await (const _item of orders) {
            num = num + 1;
            _item.isDelete = false;
            processedOrders.push(_item);
            //save
          }
          continue;
        }

        for (const _item1 of mainOrder) {
          let _orders = orders.filter((x: ProfileSalary) => !x.id.includes(_item1.id));
          for (const _item of _orders) {
            num = num + 1;

            const chkEditCommandNo =
              _item.commandName == "แก้ไขคำสั่ง"
                ? _item.remark.includes("แก้ไข ") || _item.remark.includes("แก้ไขคำสั่ง")
                : !(_item.remark.includes("แก้ไข ") || _item.remark.includes("แก้ไขคำสั่ง"));

            const chkCanCommandNo =
              _item.commandName == "ยกเลิกคำสั่ง"
                ? _item.remark.includes("ยกเลิก")
                : !_item.remark.includes("ยกเลิก");

            _item.remark?.includes(_item1.commandNo + "/" + (_item1.commandYear + 543));

            //ปี 4หลัก
            const findCommandNo = _item.remark?.includes(
              _item1.commandNo + "/" + (_item1.commandYear + 543),
            );
            if (findCommandNo && (chkEditCommandNo == true || chkCanCommandNo == true)) {
              _item.isDelete = true;
              processedOrders.push(_item);
              orders = orders.filter((x: ProfileSalary) => x.id != _item.id);
              orderOrigi.push(_item);
              continue;
            }

            //ปี 2หลัก
            const _findCommandNo = _item.remark?.includes(
              _item1.commandNo + "/" + (_item1.commandYear + 543).toString().slice(-2),
            );
            if (_findCommandNo && (chkEditCommandNo == true || chkCanCommandNo == true)) {
              _item.isDelete = true;
              processedOrders.push(_item);
              orders = orders.filter((x: ProfileSalary) => x.id != _item.id);
              orderOrigi.push(_item);
              continue;
            }
          }
        }
        for (const _item of orders) {
          num = num + 1;
          //หาแก้ไขคำสั่ง
          //ปี 4หลัก
          const chk1 = orderOrigi.filter(
            (x) =>
              x.remark.includes(_item.commandNo + "/" + (_item.commandYear + 543)) &&
              (_item.commandName == "แก้ไขคำสั่ง"
                ? x.remark.includes("แก้ไข ") || x.remark.includes("แก้ไขคำสั่ง")
                : !(x.remark.includes("แก้ไข ") || x.remark.includes("แก้ไขคำสั่ง"))),
          );
          if (chk1.length > 0) {
            _item.isDelete = false;
            _item.remark = chk1[0].remark;
            //commandName
            processedOrders.push(_item);
            continue;
          }

          //ปี 2หลัก
          const chk2 = orderOrigi.filter(
            (x) =>
              x.remark.includes(
                _item.commandNo + "/" + (_item.commandYear + 543).toString().slice(-2),
              ) &&
              (_item.commandName == "แก้ไขคำสั่ง"
                ? x.remark.includes("แก้ไข ") || x.remark.includes("แก้ไขคำสั่ง")
                : !(x.remark.includes("แก้ไข ") || x.remark.includes("แก้ไขคำสั่ง"))),
          );
          if (chk2.length > 0) {
            _item.isDelete = false;
            _item.remark = chk2[0].remark;
            //commandName
            processedOrders.push(_item);
            continue;
          }

          //หายกเลิกคำสั่ง
          //ปี 4หลัก
          const chkCan1 = orderOrigi.filter(
            (x) =>
              x.remark.includes(_item.commandNo + "/" + (_item.commandYear + 543)) &&
              (_item.commandName == "ยกเลิกคำสั่ง"
                ? x.remark.includes("ยกเลิก")
                : !x.remark.includes("ยกเลิก")),
          );
          if (chkCan1.length > 0) {
            _item.isDelete = false;
            _item.remark = chkCan1[0].remark;
            //commandName
            processedOrders.push(_item);
            continue;
          }

          //ปี 2หลัก
          const chkCan2 = orderOrigi.filter(
            (x) =>
              x.remark.includes(
                _item.commandNo + "/" + (_item.commandYear + 543).toString().slice(-2),
              ) &&
              (_item.commandName == "ยกเลิกคำสั่ง"
                ? x.remark.includes("ยกเลิก")
                : !x.remark.includes("ยกเลิก")),
          );
          if (chkCan2.length > 0) {
            _item.isDelete = false;
            _item.remark = chkCan2[0].remark;
            //commandName
            processedOrders.push(_item);
            continue;
          }
          _item.isDelete = false;
          processedOrders.push(_item);
        }
      }
      let salaryNew = processedOrders.map(({ id, ...rest }: ProfileSalary) => ({
        ...rest,
        isEdit: false,
        createdUserId: request.user.sub,
        createdFullName: request.user.name,
        lastUpdateUserId: request.user.sub,
        lastUpdateFullName: request.user.name,
        createdAt: new Date(),
        lastUpdatedAt: new Date(),
      }));
      await this.salaryTempRepo.save(salaryNew);
      // return new HttpSuccess(salaryNew.length);
    }
    return new HttpSuccess();
  }

  /**
   * @summary รวม entry กับตัวหลัก
   */
  @Post("tranferEntryToMainEdit")
  async tranferEntryToMainEdit(@Request() request: { user: Record<string, any> }) {
    const profiles = await this.salaryTempRepo.find({
      where: {
        // profileId: In([
        //   "529558c4-5c5d-431a-8b40-940cded51a41",
        //   "649f2d08-2a01-48b1-afcc-8a4c99091b17",
        //   "9ac31312-441a-4cdf-a778-974d9232ca55",
        //   "9c6b5005-ee8e-432f-a83d-cd41fb199e56",
        //   "c8c664de-e792-4fbd-85c1-fbeefdc860d4",
        //   "c9639f3b-26d0-47fc-a53d-1a0e4926697e",
        //   "e6d40cd6-c77b-4229-8e8d-a6df3f83394b",
        // ]),
        isEntry: true,
        isDelete: true,
      },
    });
    for await (const item of profiles) {
      const findAct = item.remark?.includes("รักษาการ");
      if (findAct) {
        item.commandName = "รักษาการในตำแหน่ง";
        item.commandCode = "17";
        item.isEdit = true;
        await this.salaryTempRepo.save(item);
      }
      const findSalary =
        (item.remark?.includes("เลื่อนขั้นเงินเดือน") ||
          item.remark?.includes("เลื่อนเงินเดือน")) &&
        !item.remark?.includes("แก้ไข") &&
        item.commandDateAffect != null &&
        ((item.commandDateAffect.getMonth() == 3 && item.commandDateAffect.getDate() == 1) ||
          (item.commandDateAffect.getMonth() == 9 && item.commandDateAffect.getDate() == 1));
      if (findSalary) {
        item.commandName = "เลื่อนเงินเดือน/ค่าจ้างตามปกติ";
        item.commandCode = "5";
        item.isEdit = true;
        await this.salaryTempRepo.save(item);
      }
    }
    return new HttpSuccess();
  }

  /**
   * @summary รวม entry กับตัวหลัก
   */
  @Post("tranferEntryToMainEditEntry")
  async tranferEntryToMainEditEntry(@Request() request: { user: Record<string, any> }) {
    const profiles = await this.salaryTempRepo.find({
      where: {
        // profileId: In([
        //   "529558c4-5c5d-431a-8b40-940cded51a41",
        //   "649f2d08-2a01-48b1-afcc-8a4c99091b17",
        //   "9ac31312-441a-4cdf-a778-974d9232ca55",
        //   "9c6b5005-ee8e-432f-a83d-cd41fb199e56",
        //   "c8c664de-e792-4fbd-85c1-fbeefdc860d4",
        //   "c9639f3b-26d0-47fc-a53d-1a0e4926697e",
        //   "e6d40cd6-c77b-4229-8e8d-a6df3f83394b",
        // ]),
        // isEntry: true,
      },
      relations: ["profile"],
    });
    let num = 1;
    for await (const item of profiles) {
      console.log(num);
      num += 1;
      const existingProfile = await this.ProfileSalariesRepo.createQueryBuilder("profile")
        .where("profile.posNo = :posNo", { posNo: item.profile.citizenId })
        .andWhere("CONCAT(profile.SalaryRef, ' ', profile.PositionName) = :remark", {
          remark: item.remark,
        })
        .getOne();

      if (existingProfile) {
        item.remark = existingProfile.SalaryRef;
        await this.salaryTempRepo.save(item);
      }
    }
    return new HttpSuccess();
  }
  // ฟังก์ชันจัดกลุ่มตามวันที่
  groupOrdersByDate(orders: ProfileSalary[]) {
    return orders.reduce((groups: any, order) => {
      const date = order?.commandDateAffect?.toDateString() || ""; // เปลี่ยนชื่อฟิลด์ตามจริง
      if (!groups[date]) {
        groups[date] = [];
      }
      groups[date].push(order);
      return groups;
    }, {});
  }

  @Post("updateCommandYearNull")
  async updateCommandYearNull(@Request() request: { user: Record<string, any> }) {
    const profiles = await this.salaryRepo.find({
      where: {
        createdAt: Between(
          new Date("2025-10-23T00:00:00.000Z"),
          new Date("2025-10-25T00:00:00.000Z"),
        ),
        commandYear: IsNull(),
      },
    });

    for (const item of profiles) {
      console.log(item.id);
      if (item.commandDateAffect) {
        item.commandYear = item.commandDateAffect.getFullYear();
        await this.salaryRepo.save(item);
      }
    }
    return new HttpSuccess();
  }

  @Post("updateRetireClearOrg")
  async updateRetireClearOrg(@Request() request: { user: Record<string, any> }) {
    const profiles = await this.profileRepo.find({
      where: { isLeave: true },
      select: ["id"],
    });
    const profileIds = profiles.map((p) => p.id);
    if (profileIds.length > 0) {
      const posmasters = await this.posMasterRepo.find({
        where: {
          orgRevision: {
            orgRevisionIsCurrent: false,
            orgRevisionIsDraft: true,
          },
          next_holderId: In(profileIds),
        },
      });
      for (const posmaster of posmasters) {
        posmaster.next_holderId = null;
      }
      if (posmasters.length > 0) {
        await this.posMasterRepo.save(posmasters);
      }
    }

    const profileEmps = await this.profileEmpRepo.find({
      where: { isLeave: true },
      select: ["id"],
    });
    const profileEmpIds = profileEmps.map((p) => p.id);
    if (profileEmpIds.length > 0) {
      const posmasterEmps = await this.posMasterEmpRepo.find({
        where: {
          orgRevision: {
            orgRevisionIsCurrent: false,
            orgRevisionIsDraft: true,
          },
          next_holderId: In(profileEmpIds),
        },
      });
      for (const posmasterEmp of posmasterEmps) {
        posmasterEmp.next_holderId = null;
      }
      if (posmasterEmps.length > 0) {
        await this.posMasterEmpRepo.save(posmasterEmps);
      }
    }
    return new HttpSuccess();
  }

  //   /**
  //    * @summary Update importUpdate for provinces matching JSON
  //    */
  //   @Post("updateProvinceImportOld")
  //   async updateProvinceImportOld() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/provinces.json");
  //     const provinceJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     let num = 0;
  //     const Fuse = require("fuse.js");
  //     const fuse = new Fuse(
  //       provinceJson.map((x: any) => x.name_th),
  //       {
  //         includeScore: true,
  //         threshold: 0.2, // ยิ่งต่ำยิ่งเข้มงวด
  //       },
  //     );
  //     const provinces = await this.provinceIdRepo.find({ where: { importUpdate: IsNull() } });
  //     for (const prov of provinces) {
  //       console.log("11");
  //       const result = fuse.search(prov.name);
  //       const repoProvince = AppDataSource.getRepository(ProvinceMaster);
  //       console.log("1221");
  //       if (result.length > 0) {
  //         let foundItem = result[0];
  //         if (result.length > 1) {
  //           foundItem = result.filter((x: any) => x.score == 0)[0];
  //           if (foundItem == null) {
  //             continue;
  //           }
  //         }
  //         const province = await repoProvince.findOne({
  //           where: { name_th: foundItem.item },
  //         });
  //         if (province == null) {
  //           console.log(foundItem.item);
  //           num += 1;
  //           continue;
  //         }
  //         console.log("1441");
  //         prov.importUpdate = 1;
  //         prov.refId = province.id;
  //         prov.name = province.name_th;
  //         await this.provinceIdRepo.save(prov);
  //       } else {
  //         console.log("qqqqq");
  //         console.log(prov.name);
  //         console.log(result);
  //         num += 1;
  //       }
  //     }
  //     console.log("not found province count:", num);
  //     return new HttpSuccess();
  //   }

  //   /**
  //    * @summary Update importUpdate for districts matching JSON
  //    */
  //   @Post("updateDistrictImportOld")
  //   async updateDistrictImportOld() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/tambons.json");
  //     const districtJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     let num = 0;
  //     const Fuse = require("fuse.js");
  //     const districts = await this.districtIdRepo.find({
  //       relations: ["province"],
  //       where: { province: { refId: Not(IsNull()) }, importUpdate: IsNull() },
  //     });
  //     for (const prov of districts) {
  //       const fuse = new Fuse(
  //         districtJson
  //           .filter((x: any) => x.province_id === prov.province?.refId)
  //           .map((x: any) => x.name_th),
  //         {
  //           includeScore: true,
  //           threshold: 0.2, // ยิ่งต่ำยิ่งเข้มงวด
  //         },
  //       );
  //       const result = fuse.search(prov.name);
  //       const repoDistrict = AppDataSource.getRepository(DistrictMaster);
  //       if (result.length > 0) {
  //         let foundItem = result[0];
  //         if (result.length > 1) {
  //           foundItem = result.filter((x: any) => x.score == 0)[0];
  //           if (foundItem == null) {
  //             continue;
  //           }
  //         }
  //         const district = await repoDistrict.findOne({
  //           where: { name_th: foundItem.item, province_id: prov.province?.refId },
  //         });
  //         if (district == null) {
  //           num += 1;
  //           continue;
  //         }
  //         prov.importUpdate = 1;
  //         prov.refId = district.id;
  //         prov.name = district.name_th;
  //         await this.districtIdRepo.save(prov);
  //       } else {
  //         num += 1;
  //       }
  //     }
  //     console.log("not found district count:", num);
  //     return new HttpSuccess();
  //   }

  //   /**
  //    * @summary Update importUpdate for subdistricts matching JSON
  //    */
  //   @Post("updateSubDistrictImportOld")
  //   async updateSubDistrictImportOld() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/amphures.json");
  //     const subDistrictJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     let num = 0;
  //     const Fuse = require("fuse.js");
  //     const subDistricts = await this.subDistrictIdRepo.find({
  //       relations: ["district"],
  //       where: { district: { refId: Not(IsNull()) }, importUpdate: IsNull() },
  //     });
  //     for (const prov of subDistricts) {
  //       const fuse = new Fuse(
  //         subDistrictJson
  //           .filter((x: any) => x.district_id === prov.district?.refId)
  //           .map((x: any) => x.name_th),
  //         {
  //           includeScore: true,
  //           threshold: 0.2, // ยิ่งต่ำยิ่งเข้มงวด
  //         },
  //       );
  //       const result = fuse.search(prov.name);
  //       const repoSubDistrict = AppDataSource.getRepository(SubDistrictMaster);
  //       if (result.length > 0) {
  //         let foundItem = result[0];
  //         if (result.length > 1) {
  //           foundItem = result.filter((x: any) => x.score == 0)[0];
  //           if (foundItem == null) {
  //             continue;
  //           }
  //         }
  //         const subdistrict = await repoSubDistrict.findOne({
  //           where: { name_th: foundItem.item, district_id: prov.district?.refId },
  //         });
  //         if (subdistrict == null) {
  //           num += 1;
  //           continue;
  //         }
  //         prov.importUpdate = 1;
  //         prov.refId = subdistrict.id;
  //         prov.name = subdistrict.name_th;
  //         prov.zipCode = subdistrict.zip_code.toString();
  //         await this.subDistrictIdRepo.save(prov);
  //       } else {
  //         num += 1;
  //       }
  //     }
  //     console.log("not found subdistrict count:", num);
  //     return new HttpSuccess();
  //   }

  //   /**
  //    * @summary Update importUpdate for provinces matching JSON
  //    */
  //   @Post("updateProvinceImportFlag")
  //   async updateProvinceImportFlag() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/provinces.json");
  //     const provinceJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     let num = 0;
  //     for (const province of provinceJson) {
  //       const dbProvince = await this.provinceIdRepo.findOne({
  //         where: {
  //           name: province.name_th,
  //           importUpdate: IsNull(),
  //         },
  //       });
  //       if (dbProvince) {
  //         dbProvince.importUpdate = 1;
  //         dbProvince.refId = province.id;
  //         await this.provinceIdRepo.save(dbProvince);
  //       } else {
  //         num += 1;
  //       }
  //     }
  //     console.log("not found province count:", num);
  //     return new HttpSuccess();
  //   }

  //   /**
  //    * @summary Update importUpdate for districts matching JSON
  //    */
  //   @Post("updateDistrictImportFlag")
  //   async updateDistrictImportFlag() {
  //     const meta = {
  //       createdUserId: "",
  //       createdFullName: "system",
  //       lastUpdateUserId: "",
  //       lastUpdateFullName: "system",
  //       createdAt: new Date(),
  //       lastUpdatedAt: new Date(),
  //     };
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/tambons.json");
  //     const tambonJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     let num = 0;
  //     for (const tambon of tambonJson) {
  //       const dbDistrict = await this.districtIdRepo.findOne({
  //         where: {
  //           name: tambon.name_th,
  //           province: { refId: Not(tambon.province_id) },
  //           importUpdate: IsNull(),
  //         },
  //       });
  //       if (dbDistrict) {
  //         const dbProvince = await this.provinceIdRepo.findOne({
  //           where: {
  //             refId: tambon.province_id,
  //             importUpdate: Not(IsNull()),
  //           },
  //         });
  //         if (dbProvince) {
  //           const checkOld = await this.districtIdRepo.findOne({
  //             where: {
  //               name: tambon.name_th,
  //               provinceId: dbProvince.id,
  //             },
  //           });
  //           if (!checkOld) {
  //             let districtId = new District();
  //             Object.assign(districtId, {
  //               ...meta,
  //               name: tambon.name_th,
  //               provinceId: dbProvince.id,
  //               importUpdate: 2,
  //               refId: tambon.id,
  //             });
  //             await this.districtIdRepo.save(districtId);
  //             num += 1;
  //           }
  //         }
  //       }
  //     }
  //     console.log("not found province count:", num);
  //     return new HttpSuccess();
  //   }

  //   /**
  //    * @summary Update importUpdate for subdistricts matching JSON
  //    */
  //   @Post("updateSubDistrictImportFlag")
  //   async updateSubDistrictImportFlag() {
  //     const meta = {
  //       createdUserId: "",
  //       createdFullName: "system",
  //       lastUpdateUserId: "",
  //       lastUpdateFullName: "system",
  //       createdAt: new Date(),
  //       lastUpdatedAt: new Date(),
  //     };
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/amphures.json");
  //     const amphureJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     let num = 0;
  //     for (const amphure of amphureJson) {
  //       const dbSubDistrict = await this.subDistrictIdRepo.findOne({
  //         where: {
  //           name: amphure.name_th,
  //           district: { refId: Not(amphure.district_id) },
  //           importUpdate: IsNull(),
  //         },
  //       });
  //       if (dbSubDistrict) {
  //         const dbDistrict = await this.districtIdRepo.findOne({
  //           where: {
  //             refId: amphure.district_id,
  //             importUpdate: Not(IsNull()),
  //           },
  //         });
  //         if (dbDistrict) {
  //           const checkOld = await this.subDistrictIdRepo.findOne({
  //             where: {
  //               name: amphure.name_th,
  //               districtId: dbDistrict.id,
  //             },
  //           });
  //           if (!checkOld) {
  //             let districtId = new District();
  //             Object.assign(districtId, {
  //               ...meta,
  //               name: amphure.name_th,
  //               districtId: dbDistrict.id,
  //               zipCode: amphure.zip_code,
  //               importUpdate: 2,
  //               refId: amphure.id,
  //             });
  //             await this.subDistrictIdRepo.save(districtId);
  //             num += 1;
  //           }
  //         }
  //       }
  //     }
  //     console.log("not found province count:", num);
  //     return new HttpSuccess();
  //   }
  //   /**
  //    * @summary ตรวจสอบ district ใน profile ว่าตรงกับ tambons.json หรือไม่
  //    */
  //   @Post("checkProfileDistrictMismatch")
  //   async checkProfileDistrictMismatch() {
  //     const mismatches = [];
  //     const profiles = await this.profileRepo.find({
  //       relations: ["registrationProvince", "registrationDistrict", "registrationSubDistrict"],
  //       where: {
  //         registrationProvince: Not(IsNull()),
  //       },
  //     });
  //     for (const profile of profiles) {
  //       console.log("profile count:", profiles.indexOf(profile) + 1);
  //       console.log("mismatches count:", mismatches.length);
  //       // if (mismatches.length >= 10) {
  //       //   break;
  //       // }
  //       const repoProvince = AppDataSource.getRepository(ProvinceMaster);
  //       const repoDistrict = AppDataSource.getRepository(DistrictMaster);
  //       const repoSubDistrict = AppDataSource.getRepository(SubDistrictMaster);

  //       const registrationProvince = profile.registrationProvince?.name;
  //       const registrationDistrict = profile.registrationDistrict?.name;
  //       const registrationSubDistrict = profile.registrationSubDistrict?.name;

  //       const province = await repoProvince.findOne({
  //         where: { name_th: registrationProvince },
  //       });
  //       if (province == null) {
  //         mismatches.push({ profileId: profile.id });
  //         continue;
  //       }
  //       const district = await repoDistrict.findOne({
  //         where: { name_th: registrationDistrict, province_id: province.id },
  //       });
  //       if (district == null) {
  //         mismatches.push({ profileId: profile.id });
  //         continue;
  //       }
  //       const subdistrict = await repoSubDistrict.findOne({
  //         where: { name_th: registrationSubDistrict, district_id: district.id },
  //       });
  //       if (subdistrict == null) {
  //         mismatches.push({ profileId: profile.id });
  //         continue;
  //       }
  //     }
  //     return mismatches.map((x) => x.profileId);
  //   }

  //   /**
  //    * @summary Import provinces.json into ProvinceMaster table
  //    */
  //   @Post("importProvinceMaster")
  //   async importProvinceMaster() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/provinces.json");
  //     const provinceJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     const repo = AppDataSource.getRepository(ProvinceMaster);
  //     let imported = [];
  //     for (const prov of provinceJson) {
  //       const newProv = repo.create({
  //         id: prov.id,
  //         name_th: prov.name_th,
  //         name_en: prov.name_en,
  //         geography_id: prov.geography_id,
  //         created_at: prov.created_at,
  //         updated_at: prov.updated_at,
  //       });
  //       await repo.save(newProv);
  //       imported.push(newProv);
  //     }
  //     return imported;
  //   }

  //   /**
  //    * @summary Import tambons.json into DistrictMaster table
  //    */
  //   @Post("importDistrictMaster")
  //   async importDistrictMaster() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/tambons.json");
  //     const tambonJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     const repo = AppDataSource.getRepository(DistrictMaster);
  //     let imported = [];
  //     for (const tambon of tambonJson) {
  //       const newSub = repo.create({
  //         id: tambon.id,
  //         name_th: tambon.name_th,
  //         name_en: tambon.name_en,
  //         province_id: tambon.province_id,
  //         created_at: tambon.created_at,
  //         updated_at: tambon.updated_at,
  //       });
  //       await repo.save(newSub);
  //       imported.push(newSub);
  //     }
  //     return imported;
  //   }

  //   /**
  //    * @summary Import amphures.json into SubDistrictMaster table
  //    */
  //   @Post("importSubDistrictMaster")
  //   async importSubDistrictMaster() {
  //     const fs = require("fs");
  //     const path = require("path");
  //     const filePath = path.join(__dirname, "../data/amphures.json");
  //     const amphureJson = JSON.parse(fs.readFileSync(filePath, "utf8"));
  //     const repo = AppDataSource.getRepository(SubDistrictMaster);
  //     // Prepare all entities first
  //     const entities = amphureJson.map((amphure: any) =>
  //       repo.create({
  //         id: amphure.id,
  //         zip_code: amphure.zip_code,
  //         name_th: amphure.name_th,
  //         name_en: amphure.name_en,
  //         district_id: amphure.district_id,
  //         lat: amphure.lat,
  //         long: amphure.long,
  //         created_at: amphure.created_at,
  //         updated_at: amphure.updated_at,
  //       }),
  //     );
  //     // Bulk insert for performance
  //     await repo.save(entities);
  //     return entities;
  //   }

  /**
   * @summary Import ข้อมูลประวัติตำแหน่งเงินเดือนของข้าราชการเข้าตาราง ProfileSalaryTemp
   * @param profileId Id โปรไฟล์ข้าราชการ
   * @param file Excel file with salary history data
   */
  @Post("office-profileSalaryTemp/{profileId}")
  @UseInterceptors(FileInterceptor("file"))
  async UploadProfileSalaryTemp(
    @Path() profileId: string,
    @Request() req: RequestWithUser,
    @UploadedFile() file: Express.Multer.File,
  ) {
    if (!profileId) {
      throw new Error("profileId is required");
    }

    // อ่านไฟล์ Excel ก่อน (นอก transaction)
    const workbook = xlsx.read(file.buffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const getExcel = xlsx.utils.sheet_to_json(sheet, { header: 1 }) as any[][];

    let salaryTemps: ProfileSalaryTemp[] = [];
    let dateTime = new Date();

    // เริ่มจาก index 1 เพื่อข้าม header row
    for (let i = 1; i < getExcel.length; i++) {
      const row = getExcel[i];

      // ข้าม empty rows
      if (!row || row.length === 0) {
        continue;
      }

      // ข้ามแถวที่ไม่มีลำดับ (row[0] เป็น null, undefined หรือค่าว่าง)
      if (!row[0]) {
        continue;
      }

      const salaryTemp = new ProfileSalaryTemp();

      // ฟังก์ชันแปลงวันที่จาก Excel รองรับทั้ง string format และ serial number
      const parseExcelDate = (value: any): Date | null => {
        if (!value) return null;

        // กรณี 1: Excel serial number (ตัวเลข)
        if (typeof value === "number") {
          // Excel serial number = จำนวนวันตั้งแต่ 1 ม.ค. 1900
          // แปลงเป็น JavaScript Date (epoch 1970)
          let jsDate = new Date(Math.round((value - 25569) * 86400 * 1000));

          // ตรวจสอบและแปลงปี พ.ศ. เป็น ค.ศ. (ถ้าปี > 2500)
          if (jsDate.getFullYear() > 2500) {
            const newYear = jsDate.getFullYear() - 543;
            jsDate = new Date(
              newYear,
              jsDate.getMonth(),
              jsDate.getDate(),
              jsDate.getHours(),
              jsDate.getMinutes(),
              jsDate.getSeconds(),
              jsDate.getMilliseconds(),
            );
          }
          return jsDate;
        }

        // กรณี 2: String format (dd/mm/yyyy หรือ d/m/yyyy)
        const dateStr = value.toString().trim();

        // ตรวจสอบว่าเป็น serial number ที่เป็น string หรือไม่
        if (/^\d+$/.test(dateStr)) {
          const serialNum = parseInt(dateStr);
          let jsDate = new Date(Math.round((serialNum - 25569) * 86400 * 1000));

          // ตรวจสอบและแปลงปี พ.ศ. เป็น ค.ศ. (ถ้าปี > 2500)
          if (jsDate.getFullYear() > 2500) {
            const newYear = jsDate.getFullYear() - 543;
            jsDate = new Date(
              newYear,
              jsDate.getMonth(),
              jsDate.getDate(),
              jsDate.getHours(),
              jsDate.getMinutes(),
              jsDate.getSeconds(),
              jsDate.getMilliseconds(),
            );
          }
          return jsDate;
        }

        // String format ปกติ (dd/mm/yyyy)
        const dateParts = dateStr.split("/");
        if (dateParts.length === 3) {
          // แปลงเป็นตัวเลขแล้วค่อยจัดรูปแบบใหม่ เพื่อรองรับทั้ง 1 หลักและ 2 หลัก
          const day = parseInt(dateParts[0].trim()).toString().padStart(2, "0");
          const month = parseInt(dateParts[1].trim()).toString().padStart(2, "0");
          let year = parseInt(dateParts[2].trim());
          if (year > 2500) {
            year -= 543;
          }
          const result = new Date(`${year}-${month}-${day}`);
          return result;
        }

        return null;
      };

      // Index 1: วันที่คำสั่งมีผล
      let commandDateAffect: Date | null = null;
      if (row[1]) {
        commandDateAffect = parseExcelDate(row[1]);
      }

      // Index 25: วันที่ลงนาม
      let commandDateSign: Date | null = null;
      if (row[25]) {
        commandDateSign = parseExcelDate(row[25]);
      }

      // Map ข้อมูลจาก Excel ไปยัง ProfileSalaryTemp ตามลำดับ column
      // ข้อมูลระบบ
      salaryTemp.profileId = profileId;
      salaryTemp.profileEmployeeId = null as any;

      // Index 0: ลำดับ
      salaryTemp.order = row[0] ? parseInt(row[0].toString()) : (null as any);

      // Index 1: วันที่คำสั่งมีผล
      salaryTemp.commandDateAffect = commandDateAffect as any;

      // Index 2: ตำแหน่งในสายงาน
      salaryTemp.positionName = row[2] || null;

      // Index 3: ตำแหน่งประเภท
      salaryTemp.positionType = row[3] || null;

      // Index 4: ระดับ
      salaryTemp.positionLevel = row[4] || null;

      // Index 5: ระดับซี
      salaryTemp.positionCee = row[5] || null;

      // Index 6: สายงาน
      salaryTemp.positionLine = row[6] || null;

      // Index 7: ด้าน/สาขา
      salaryTemp.positionPathSide = row[7] || null;

      // Index 8: ตำแหน่งทางการบริหาร
      salaryTemp.positionExecutive = row[8] || null;

      // Index 9: ด้านทางการบริหาร
      salaryTemp.positionExecutiveField = row[9] || null;

      // Index 10: เงินเดือน
      salaryTemp.amount = row[10] || 0;

      // Index 11: เงินค่าตอบแทนรายเดือน
      salaryTemp.mouthSalaryAmount = row[11] || 0;

      // Index 12: เงินประจำตำแหน่ง
      salaryTemp.positionSalaryAmount = row[12] || 0;

      // Index 13: เงินค่าตอบแทนพิเศษ
      salaryTemp.amountSpecial = row[13] || 0;

      // Index 14: หน่วยงาน
      salaryTemp.orgRoot = row[14] || null;

      // Index 15: ส่วนราชการระดับ 1
      salaryTemp.orgChild1 = row[15] || null;

      // Index 16: ส่วนราชการระดับ 2
      salaryTemp.orgChild2 = row[16] || null;

      // Index 17: ส่วนราชการระดับ 3
      salaryTemp.orgChild3 = row[17] || null;

      // Index 18: ส่วนราชการระดับ 4
      salaryTemp.orgChild4 = row[18] || null;

      // Index 19: ตัวย่อเลขที่ตำแหน่ง
      salaryTemp.posNoAbb = row[19] || null;

      // Index 20: เลขที่ตำแหน่ง
      salaryTemp.posNo = row[20] ? row[20].toString() : null;

      // Index 21: หน่วยงานที่ออกคำสั่ง
      salaryTemp.posNumCodeSit = row[21] || null;

      // Index 22: ตัวย่อหน่วยงานที่ออกคำสั่ง
      salaryTemp.posNumCodeSitAbb = row[22] || null;

      // Index 23: เลขที่คำสั่ง
      salaryTemp.commandNo = row[23] || null;

      // Index 24: ปีเลขที่คำสั่ง (แปลงเป็น ค.ศ.)
      let commandYearValue: number | null = null;
      if (row[24]) {
        commandYearValue = parseInt(row[24].toString());
        // ถ้าปีเป็น พ.ศ. (มากกว่า 2500) ให้แปลงเป็น ค.ศ.
        if (commandYearValue > 2500) {
          commandYearValue -= 543;
        }
      }
      salaryTemp.commandYear = commandYearValue as any;

      // Index 25: วันที่ลงนาม (แปลงแล้ว)
      salaryTemp.commandDateSign = commandDateSign as any;

      // Index 26: ประเภทคำสั่ง
      salaryTemp.commandName = row[26] || null;

      // Index 27: หมายเหตุ
      salaryTemp.remark = row[27] || null;

      // Index 28: commandId
      salaryTemp.commandId = row[28] || null;

      // Index 29: commandCode
      salaryTemp.commandCode = row[29] || null;

      // ข้อมูลระบบ
      salaryTemp.isDelete = false;
      salaryTemp.isEdit = false;
      salaryTemp.isGovernment = false;
      salaryTemp.isEntry = false;
      salaryTemp.createdAt = dateTime;
      salaryTemp.createdUserId = req.user?.sub || "";
      salaryTemp.createdFullName = req.user?.name || "System Administrator";
      salaryTemp.lastUpdatedAt = dateTime;
      salaryTemp.lastUpdateUserId = req.user?.sub || "";
      salaryTemp.lastUpdateFullName = req.user?.name || "System Administrator";

      // 12,15,16 isGovernment = false & dateGovernment = salaryTemp.commandDateAffect
      if (["12", "15", "16"].includes(salaryTemp.commandCode ?? "")) {
        salaryTemp.isGovernment = false;
        salaryTemp.dateGovernment = salaryTemp.commandDateAffect;
      }
      // 1,2,3,4,10,11,20 isGovernment = true & dateGovernment = salaryTemp.commandDateAffect
      else if (["1", "2", "3", "4", "10", "11", "20"].includes(salaryTemp.commandCode ?? "")) {
        salaryTemp.isGovernment = true;
        salaryTemp.dateGovernment = salaryTemp.commandDateAffect;
      }

      salaryTemps.push(salaryTemp);
    }

    // ใช้ Transaction เพื่อความปลอดภัย
    await AppDataSource.transaction(async (transactionalEntityManager) => {
      // ล้างข้อมูลทั้งหมดในตาราง profileSalaryTemp ของ profileId นั้น
      await transactionalEntityManager.delete(ProfileSalaryTemp, { profileId });
      // Insert ข้อมูลใหม่
      await transactionalEntityManager.save(ProfileSalaryTemp, salaryTemps);
    });

    return new HttpSuccess({ message: "Import ข้อมูลเรียบร้อย", count: salaryTemps.length });
  }

  /**
   * @summary Import ข้อมูลประวัติตำแหน่งเงินเดือนของลูกจ้างประจำเข้าตาราง ProfileSalaryTemp
   * @param profileEmployeeId Id โปรไฟล์ลูกจ้างประจำ
   * @param file Excel file with salary history data
   */
  @Post("employee-profileSalaryTemp/{profileEmployeeId}")
  @UseInterceptors(FileInterceptor("file"))
  async UploadProfileEmployeeSalaryTemp(
    @Path() profileEmployeeId: string,
    @Request() req: RequestWithUser,
    @UploadedFile() file: Express.Multer.File,
  ) {
    if (!profileEmployeeId) {
      throw new Error("profileEmployeeId is required");
    }

    // อ่านไฟล์ Excel ก่อน (นอก transaction)
    const workbook = xlsx.read(file.buffer, { type: "buffer" });
    const sheetName = workbook.SheetNames[0];
    const sheet = workbook.Sheets[sheetName];
    const getExcel = xlsx.utils.sheet_to_json(sheet, { header: 1 }) as any[][];

    let salaryTemps: ProfileSalaryTemp[] = [];
    let dateTime = new Date();

    // เริ่มจาก index 1 เพื่อข้าม header row
    for (let i = 1; i < getExcel.length; i++) {
      const row = getExcel[i];

      // ข้าม empty rows
      if (!row || row.length === 0) {
        continue;
      }

      // ข้ามแถวที่ไม่มีลำดับ (row[0] เป็น null, undefined หรือค่าว่าง)
      if (!row[0]) {
        continue;
      }

      const salaryTemp = new ProfileSalaryTemp();

      // ฟังก์ชันแปลงวันที่จาก Excel รองรับทั้ง string format และ serial number
      const parseExcelDate = (value: any): Date | null => {
        if (!value) return null;

        // กรณี 1: Excel serial number (ตัวเลข)
        if (typeof value === "number") {
          // Excel serial number = จำนวนวันตั้งแต่ 1 ม.ค. 1900
          // แปลงเป็น JavaScript Date (epoch 1970)
          let jsDate = new Date(Math.round((value - 25569) * 86400 * 1000));

          // ตรวจสอบและแปลงปี พ.ศ. เป็น ค.ศ. (ถ้าปี > 2500)
          if (jsDate.getFullYear() > 2500) {
            const newYear = jsDate.getFullYear() - 543;
            jsDate = new Date(
              newYear,
              jsDate.getMonth(),
              jsDate.getDate(),
              jsDate.getHours(),
              jsDate.getMinutes(),
              jsDate.getSeconds(),
              jsDate.getMilliseconds(),
            );
          }
          return jsDate;
        }

        // กรณี 2: String format (dd/mm/yyyy หรือ d/m/yyyy)
        const dateStr = value.toString().trim();

        // ตรวจสอบว่าเป็น serial number ที่เป็น string หรือไม่
        if (/^\d+$/.test(dateStr)) {
          const serialNum = parseInt(dateStr);
          let jsDate = new Date(Math.round((serialNum - 25569) * 86400 * 1000));

          // ตรวจสอบและแปลงปี พ.ศ. เป็น ค.ศ. (ถ้าปี > 2500)
          if (jsDate.getFullYear() > 2500) {
            const newYear = jsDate.getFullYear() - 543;
            jsDate = new Date(
              newYear,
              jsDate.getMonth(),
              jsDate.getDate(),
              jsDate.getHours(),
              jsDate.getMinutes(),
              jsDate.getSeconds(),
              jsDate.getMilliseconds(),
            );
          }
          return jsDate;
        }

        // String format ปกติ (dd/mm/yyyy)
        const dateParts = dateStr.split("/");
        if (dateParts.length === 3) {
          // แปลงเป็นตัวเลขแล้วค่อยจัดรูปแบบใหม่ เพื่อรองรับทั้ง 1 หลักและ 2 หลัก
          const day = parseInt(dateParts[0].trim()).toString().padStart(2, "0");
          const month = parseInt(dateParts[1].trim()).toString().padStart(2, "0");
          let year = parseInt(dateParts[2].trim());
          if (year > 2500) {
            year -= 543;
          }
          const result = new Date(`${year}-${month}-${day}`);
          return result;
        }

        return null;
      };

      // Index 1: วันที่คำสั่งมีผล
      let commandDateAffect: Date | null = null;
      if (row[1]) {
        commandDateAffect = parseExcelDate(row[1]);
      }

      // Index 25: วันที่ลงนาม
      let commandDateSign: Date | null = null;
      if (row[25]) {
        commandDateSign = parseExcelDate(row[25]);
      }

      // Map ข้อมูลจาก Excel ไปยัง ProfileSalaryTemp ตามลำดับ column
      // ข้อมูลระบบ
      salaryTemp.profileEmployeeId = profileEmployeeId;
      salaryTemp.profileId = null as any;

      // Index 0: ลำดับ
      salaryTemp.order = row[0] ? parseInt(row[0].toString()) : (null as any);

      // Index 1: วันที่คำสั่งมีผล
      salaryTemp.commandDateAffect = commandDateAffect as any;

      // Index 2: ตำแหน่งในสายงาน
      salaryTemp.positionName = row[2] || null;

      // Index 3: ตำแหน่งประเภท
      salaryTemp.positionType = row[3] || null;

      // Index 4: ระดับ
      salaryTemp.positionLevel = row[4] || null;

      // Index 5: ระดับซี
      salaryTemp.positionCee = row[5] || null;

      // Index 6: สายงาน
      salaryTemp.positionLine = row[6] || null;

      // Index 7: ด้าน/สาขา
      salaryTemp.positionPathSide = row[7] || null;

      // Index 8: ตำแหน่งทางการบริหาร
      salaryTemp.positionExecutive = row[8] || null;

      // Index 9: ด้านทางการบริหาร
      salaryTemp.positionExecutiveField = row[9] || null;

      // Index 10: เงินเดือน
      salaryTemp.amount = row[10] || 0;

      // Index 11: เงินค่าตอบแทนรายเดือน
      salaryTemp.mouthSalaryAmount = row[11] || 0;

      // Index 12: เงินประจำตำแหน่ง
      salaryTemp.positionSalaryAmount = row[12] || 0;

      // Index 13: เงินค่าตอบแทนพิเศษ
      salaryTemp.amountSpecial = row[13] || 0;

      // Index 14: หน่วยงาน
      salaryTemp.orgRoot = row[14] || null;

      // Index 15: ส่วนราชการระดับ 1
      salaryTemp.orgChild1 = row[15] || null;

      // Index 16: ส่วนราชการระดับ 2
      salaryTemp.orgChild2 = row[16] || null;

      // Index 17: ส่วนราชการระดับ 3
      salaryTemp.orgChild3 = row[17] || null;

      // Index 18: ส่วนราชการระดับ 4
      salaryTemp.orgChild4 = row[18] || null;

      // Index 19: ตัวย่อเลขที่ตำแหน่ง
      salaryTemp.posNoAbb = row[19] || null;

      // Index 20: เลขที่ตำแหน่ง
      salaryTemp.posNo = row[20] ? row[20].toString() : null;

      // Index 21: หน่วยงานที่ออกคำสั่ง
      salaryTemp.posNumCodeSit = row[21] || null;

      // Index 22: ตัวย่อหน่วยงานที่ออกคำสั่ง
      salaryTemp.posNumCodeSitAbb = row[22] || null;

      // Index 23: เลขที่คำสั่ง
      salaryTemp.commandNo = row[23] || null;

      // Index 24: ปีเลขที่คำสั่ง (แปลงเป็น ค.ศ.)
      let commandYearValue: number | null = null;
      if (row[24]) {
        commandYearValue = parseInt(row[24].toString());
        // ถ้าปีเป็น พ.ศ. (มากกว่า 2500) ให้แปลงเป็น ค.ศ.
        if (commandYearValue > 2500) {
          commandYearValue -= 543;
        }
      }
      salaryTemp.commandYear = commandYearValue as any;

      // Index 25: วันที่ลงนาม (แปลงแล้ว)
      salaryTemp.commandDateSign = commandDateSign as any;

      // Index 26: ประเภทคำสั่ง
      salaryTemp.commandName = row[26] || null;

      // Index 27: หมายเหตุ
      salaryTemp.remark = row[27] || null;

      // Index 28: commandId
      salaryTemp.commandId = row[28] || null;

      // Index 29: commandCode
      salaryTemp.commandCode = row[29] || null;

      // ข้อมูลระบบ
      salaryTemp.isDelete = false;
      salaryTemp.isEdit = false;
      salaryTemp.isGovernment = false;
      salaryTemp.isEntry = false;
      salaryTemp.createdAt = dateTime;
      salaryTemp.createdUserId = req.user?.sub || "";
      salaryTemp.createdFullName = req.user?.name || "System Administrator";
      salaryTemp.lastUpdatedAt = dateTime;
      salaryTemp.lastUpdateUserId = req.user?.sub || "";
      salaryTemp.lastUpdateFullName = req.user?.name || "System Administrator";

      // 12,15,16 isGovernment = false & dateGovernment = salaryTemp.commandDateAffect
      if (["12", "15", "16"].includes(salaryTemp.commandCode ?? "")) {
        salaryTemp.isGovernment = false;
        salaryTemp.dateGovernment = salaryTemp.commandDateAffect;
      }
      // 1,2,3,4,10,11,20 isGovernment = true & dateGovernment = salaryTemp.commandDateAffect
      else if (["1", "2", "3", "4", "10", "11", "20"].includes(salaryTemp.commandCode ?? "")) {
        salaryTemp.isGovernment = true;
        salaryTemp.dateGovernment = salaryTemp.commandDateAffect;
      }
      salaryTemps.push(salaryTemp);
    }

    // ใช้ Transaction เพื่อความปลอดภัย
    await AppDataSource.transaction(async (transactionalEntityManager) => {
      // ล้างข้อมูลทั้งหมดในตาราง profileSalaryTemp ของ profileEmployeeId นั้น
      await transactionalEntityManager.delete(ProfileSalaryTemp, { profileEmployeeId });
      // Insert ข้อมูลใหม่
      await transactionalEntityManager.save(ProfileSalaryTemp, salaryTemps);
    });

    return new HttpSuccess({ message: "Import ข้อมูลเรียบร้อย", count: salaryTemps.length });
  }
}
