5.4 · Service layer + SQL patterns

บทนี้เจาะ service layer ของ centralize-api — รูปแบบการใช้ raw SQL ผ่าน entityManager.query() ทั้งระบบ, CTE 80 บรรทัดที่ copy-paste ซ้ำ 4 ครั้ง, การใช้ WITH (NOLOCK) dirty read, และ error handling anti-pattern พร้อม business logic จริงเพียงจุดเดียวของ centralize-api (getShiftTimeWithWork)

---

Service layer pattern

ทุก service inject EntityManager ผ่าน @InjectEntityManager('center') หรือ 'nbc' แล้วเรียก entityManager.query(sql, params) เพื่อรัน raw SQL

// Pattern ทั่วไปใน service ทุกตัว
@Injectable()
export class EmployeeService {
  constructor(
    @InjectEntityManager('center') private entityManager: EntityManager,
    @InjectEntityManager('nbc') private nbcEntityManager: EntityManager,
  ) {}

  async getEmployees(query: GetEmployeeQueryDto) {
    try {
      return await this.entityManager.query(SQL_STRING, [...params]);
    } catch (error) {
      this.logger.error(error);
      throw new BadRequestException(error);
    }
  }
}

จุดสำคัญ

• ไม่มี TypeORM query builder หรือ repository pattern — ใช้แค่ entityManager.query() ส่ง SQL string + params
• ไม่มี transaction — ไม่มี queryRunner, @Transaction(), entityManager.transaction() ใด ๆ (OK เพราะ read-only)
• try/catch ทุก method → throw BadRequestException (400) สำหรับทุก error รวมถึง DB failure (anti-pattern — ดู §"Error handling")
• logger ทุก method — inject ผ่าน private logger = new I3GatewayLogger(ClassName.name)

---

Raw SQL pattern — parameterized queries

ทุก query ใช้ @0, @1, ... เป็น positional placeholder แล้วส่ง params เป็น array ที่ตำแหน่งตรงกัน

ตัวอย่างจาก hrpayslip.service.ts

async getPaySlipDT(body: EmpCodeListDto): Promise<any> {
  try {
    return await this.entityManager.query(
      `SELECT empCode , incomeCode ,incomeName,income,sourceDB  from  uv_i3_PaySlipDT_reStructure WHERE EmpCode  in (${body.empCode.map(
        (_, index) => `@${index}`,
      )})`,
      body.empCode,
    );
  } catch (error) {
    this.logger.error(error);
    throw new BadRequestException(error);
  }
}

วิเคราะห์

• ${body.empCode.map((_, index) => '@' + index)} สร้าง placeholder names แบบ dynamic — ถ้า empCode มี 3 ตัวจะได้ IN (@0, @1, @2) แล้วส่ง [empCode1, empCode2, empCode3] เป็น params
• ปลอดภัยจาก SQL injection เพราะ placeholder @0 @1 เป็นเพียงชื่อ parameter ไม่ใช่ value interpolation — driver ของ MSSQL (tedious) จะ bind value แยก
• pattern เดียวกันใช้ใน hrTimeTemp.service.ts:48-66 สำหรับ workingDate array

---

80-line org hierarchy CTE — copy-paste 4 ครั้ง

ปัญหาใหญ่ที่สุดใน service layer คือ CTE (Common Table Expression) 80 บรรทัดที่ query org/division/department hierarchy ถูก copy-paste เหมือนกันเป็นจัง ๆ 4 ครั้ง ในไฟล์ต่อไปนี้

Method	ไฟล์:บรรทัด
AppService.getHello	app.service.ts:13-142
EmployeeService.getOrganizations	employee.service.ts:34-103
EmployeeService.getDivisions	employee.service.ts:112-189
EmployeeService.getDepartments	employee.service.ts:191-270

โครงสร้าง CTE คร่าว ๆ

WITH orgCTE AS (
  -- union 2 databases
  select * from dbHRMI_Center.dbo.emOrgUnit_new_reStructure WITH (NOLOCK)
  UNION ALL
  select * from dbHRMI_Center_NBC.dbo.emOrgUnit_new_reStructure WITH (NOLOCK)
),
orgMain AS (
  -- 6 LEFT JOINs เพื่อ derive Org/Divi/Dept code จาก OrgUnitCode prefix
  -- LEFT(...,2) = org code
  -- LEFT(...,4) = divi code
  -- LEFT(...,6) = dept code
  SELECT ...
  FROM orgCTE
  LEFT JOIN emOrg ...
  LEFT JOIN emOrgUnit ...
  -- ... 6 tables
)
SELECT * FROM orgMain WHERE ...

ผลกระทบ

• แก้ logic ต้องแก้ 4 ที่ — ถ้า schema HRMI เปลี่ยน (เช่นเพิ่ม field ใหม่ใน emOrgUnit) ต้องแก้ 4 methods ให้ตรงกัน ถ้าลืมที่ใดที่หนึ่งจะได้ผลต่างกัน
• ภาระการดูแลสูง — ไม่มี test ครอบคลุม ดังนั้นถ้า query ที่ 1 คืน row 100 แถว แต่ที่ 2 คืน 99 แถว จะไม่มีใครสังเกตเห็น
• Performance — query plan cache ของ MSSQL จะเก็บแผนแยก 4 ชุด ถึงแม้ SQL จะเหมือนกัน เพราะ connection pool แยก

การแก้ — สร้าง method private buildOrgHierarchyCTE(): string ใน service ที่ return SQL string แล้วเรียกใช้ในทุก method ที่ต้องการ

---

WITH (NOLOCK) — dirty read hint

ทุกที่ที่ join HRMI tables ใช้ WITH (NOLOCK) hint เสมอ

-- ตัวอย่าง
SELECT * FROM dbHRMI_Center.dbo.emOrgUnit_new_reStructure WITH (NOLOCK)

NOLOCK (หรือ READUNCOMMITTED) เป็น isolation level ที่อนุญาตให้อ่าน row ที่ transaction อื่นกำลังแก้ไขอยู่โดยไม่รอ → เรียกว่า dirty read

ข้อดี	ข้อเสีย
เร็วมาก — ไม่ต้องรอ lock release	อาจได้ข้อมูล inconsistent — row ที่อยู่ระหว่าง UPDATE อาจคืนค่าใหม่บาง column ค่าเก่าบาง column
ลด contention บน HRMI ที่มี write traffic สูง	อาจได้ duplicate rows หรือ missing rows ในบางสถานการณ์ (page split)
เหมาะสำหรับ read-only report ที่ทนความคลาดเคลื่อนได้	ไม่เหมาะสำหรับ transactional read ที่ต้องการ accuracy

เหมาะสมไหม — สำหรับ centralize-api ที่เป็น HR data facade NOLOCK ยอมรับได้เพราะ

• ข้อมูล HR เปลี่ยนไม่บ่อย (org structure, manager) — dirty read ไม่กระทบมาก
• Perf สำคัญกว่า consistency ใน use case ส่วนใหญ่
• เป็น pattern มาตรฐานของ MSSQL read-only report

แต่ต้องเขียนไว้ในคู่มือ — ถ้าได้รับแจ้งว่า "ข้อมูล org ของพนักงาน X ไม่ตรง บางครั้ง" อาจเป็น dirty read ไม่ใช่ bug

---

Error handling anti-pattern

ทุก service method ใช้ pattern เดียวกัน

try {
  return await this.entityManager.query(sql, params);
} catch (error) {
  this.logger.error(error);
  throw new BadRequestException(error);
}

ปัญหา — BadRequestException (HTTP 400) หมายถึง "client ส่งข้อมูลผิด" แต่ error ที่เกิดจาก DB (เช่น connection timeout, SQL syntax error, dead lock) ไม่ใช่ความผิดของ client ควรจะเป็น InternalServerErrorException (500)

ผลกระทบ

• Client สับสน — ได้ 400 ทั้งที่ request ถูกต้อง ทำให้ debug ผิดทาง
• Monitoring พลาด — alert ที่กรองด้วย status code 5xx จะไม่ตรวจจับ error ของ centralize-api
• Retry logic พัง — ถ้า client มี retry เฉพาะ 5xx จะไม่ retry เมื่อเจอ 400 ทั้งที่น่าจะ retry

การแก้ — แยก error type

} catch (error) {
  this.logger.error(error);
  if (error instanceof QueryFailedError) {
    throw new InternalServerErrorException({
      message: 'Database operation failed',
      detail: error.message,
    });
  }
  throw new InternalServerErrorException(error);
}

---

Business logic จริงเพียงจุดเดียวของ centralize-api — getShiftTimeWithWork

HrTimeTempService.getShiftTimeWithWork (hrtimetemp.service.ts:27-128) เป็น business logic แท้จริงเพียงจุดเดียวในระบบ — ทุก service อื่นเป็น thin SQL wrapper

Flow

1. Query hrTimeTempImport บน dbHRMI_Center + dbHRMI_Center_NBC แบบ parallel (Promise.all)
   │
   ▼
2. Merge results จาก 2 databases
   │
   ▼
3. Sort workingDate ascending
   │
   ▼
4. For each day:
   │   4.1 filter check-ins ของวันนั้น
   │   4.2 sort by timestamp
   │   4.3 ถ้ามีหลาย stamp:
   │         - แรก = timeIn
   │         - สุดท้าย = timeOut
   │   4.4 ถ้ามี stamp เดียว:
   │         - timeIn only
   │         - timeOut = null
   │
   ▼
5. Return shape: { empCode, date, timeIn, timeOut }[]

จุดสังเกต

• parallel query ใช้ Promise.all — เร็วกว่า query ทีละ database
• merge logic ที่ฝั่ง application ไม่ใช่ SQL — เพราะถ้า merge ใน SQL ต้อง UNION ALL cross-database ที่ MSSQL ทำได้แต่อ่านยาก
• timeIn/timeOut derivation คือ business rule ที่ไม่ได้เก็บใน HRMI โดยตรง — HRMI เก็บแค่ DateTimeStamp (timestamp ของการแตะบัตร) ส่วนการตีความว่าคือเข้าหรือออกอยู่ที่ logic นี้

Edge cases ที่ logic นี้ไม่ cover

กรณี	ผลลัพธ์	ปัญหา
พนักงานแตะบัตร 1 ครั้ง → timeIn = X, timeOut = null	ทำงานครึ่งวัน? ลืมแตะออก?	ไม่ระบุ
พนักงานแตะบัตร 5 ครั้งในวันเดียวกัน	timeIn = แรก, timeOut = สุดท้าย	ครั้งที่ 2-4 คืออะไร? (ออกกินข้าว? ธุระ?)
พนักงานแตะบัตรข้ามวัน (เช่นเข้า 23:00, ออก 07:00 วันถัดไป)	จัดวันไหน?	ไม่ระบุ — ใช้ workingDate param เป็นตัวกรอง ถ้าไม่ส่งทั้งสองวันจะไม่เห็นครบ
Overnight shift	ดูเหมือนจะถูกตั้งแต่กำหนด — workingDate เป็น array	แต่ถ้าพนักงานทำงานข้ามคืน 2 วัน row จะอยู่ในผลลัพธ์ของวันไหน?

→ โดยทั่วไปจะเริ่ม debug "เวลาออกงานของพนักงาน X ผิด" ที่ method นี้ก่อน เพราะเป็น logic เดียวที่ตีความ timestamp

---

getEmployeeDisaster — disaster recovery lookup

async getEmployeeDisaster(query) {
  // ... SQL ที่ join hrEmpWorkProfile พร้อม date range filter
  // ... WHERE @1 between convert(date, w.StartDate, 103)
  //             and ISNULL(CONVERT(date, w.EndDate, 103), '9999-12-31')
}

query นี้ check ว่า disasterStartDate (เช่นวันเกิดเหตุการณ์ฉุกเฉิน) ตกในช่วง StartDate - EndDate ของงานใดหรือไม่ → ใช้สำหรับ disaster recovery benefit lookup

จุดที่น่าสนใจ

• ISNULL(CONVERT(date, w.EndDate, 103), '9999-12-31') — ถ้า EndDate เป็น NULL (พนักงานยังทำงานอยู่) ให้ใช้ 9999-12-31 เป็น upper bound
• convert(date, ..., 103) — format 103 คือ DD/MM/YYYY (British/French) ซึ่งตรงกับ locale ของ HRMI

---

repository pattern — ใช้บางส่วน

employeeGroup, employeeLevel, technicalPosition modules มี repository class แยก (3 files, 80 LOC รวม)

// ตัวอย่างจาก employeeGroup.repository.ts
@Injectable()
export class EmployeeGroupRepository {
  constructor(
    @InjectEntityManager('center') private entityManager: EntityManager,
    @InjectEntityManager('nbc') private nbcEntityManager: EntityManager,
  ) {}

  async find(query: any): Promise<EmployeeGroup[]> {
    // custom logic
  }
}

แต่ repository เหล่านี้ ไม่ได้ใช้ TypeORM Repository pattern จริง — เป็น class ธรรมดาที่ inject EntityManager เหมือน service ใช้ชื่อว่า "repository" เฉย ๆ

ผล — pattern ไม่ consistent — EmployeeService inject repository แทนที่จะเรียก SQL เอง แต่ service อื่นเรียก SQL ตรง ๆ ทำให้ codebase อ่านยาก

---

สรุปประเด็นสำคัญ

• Service layer ใช้ raw SQL string ผ่าน entityManager.query() ทั้งระบบ — TypeORM ใช้แค่ connection management
• Parameterized queries (@0, @1) ใช้ถูกต้อง → ปลอดภัยจาก SQL injection
• 80-line CTE ถูก copy-paste 4 ครั้ง — ภาระการดูแลสูง ควรดึงออกเป็น method ร่วม
• WITH (NOLOCK) ทุกที่ — dirty read ยอมรับได้สำหรับ HR facade แต่ต้องเขียนไว้ใน doc
• Error handling anti-pattern — BadRequestException สำหรับทุก error รวม DB failure → ควรแยก type
• Business logic จริงเพียงจุดเดียว — getShiftTimeWithWork time attendance merge ที่มี edge cases หลายจุด (overnight, แตะบัตรหลายครั้ง)
• Repository pattern ใช้บางส่วนไม่ consistent — 3 sub-module มี repository ส่วน service อื่นเรียก SQL ตรง ๆ