t00-multiuser/server/repos/BadgeRepo.ts

import Badge from "../entities/Badge";
import Database from "../objects/Database";
import RepoBase from "./RepoBase";

export default abstract class BadgeRepo {
	public static async selectAll() {
		const dbBadge = await Database.Instance.query("SELECT * FROM Badge WHERE IsDeleted = 0", []);
		const badges = new Array<Badge>();

		for (const row of dbBadge) {
			const badge = new Badge();
			populateBadgeFromDB(badge, row);
			badges.push(badge);
		}

		return badges;
	}

	public static async selectById(id:number) {
		const dbBadge = await Database.Instance.query("SELECT * FROM Badge WHERE Id = ? AND IsDeleted = 0 LIMIT 1", [id]);
		if (dbBadge == null || dbBadge.length === 0) {
			return null;
		} else {
			const badge = new Badge();
			populateBadgeFromDB(badge, dbBadge[0]);
			return badge;
		}
	}

	public static async selectBadgeCount() {
		const countResult = await Database.Instance.query("SELECT COUNT(Id) FROM `Badge` WHERE IsDeleted = 0 LIMIT 1", []);

		return countResult[0]["COUNT(Id)"] as number;
	}

	public static async selectList(query?: string) {
		const dbBadge = await Database.Instance.query("SELECT * FROM Badge WHERE IsDeleted = 0 AND Name LIKE ?", [ query ? `%${query}%` : "%%" ]);
		const badges = new Array<Badge>();

		for (const row of dbBadge) {
			const badge = new Badge();
			populateBadgeFromDB(badge, row);
			badges.push(badge);
		}

		return badges;
	}

	public static async insertUpdate(badge:Badge) {
		if (badge.Id === Number.MIN_VALUE) {
			badge.Id = (await Database.Instance.query("INSERT Badge (Name, Description, Hint, ImageUrl, ForUrl, IsSecret, CreatedByUserId, CreatedDatetime, LastModifiedByUserId, LastModifiedDatetime, DeletedByUserId, DeletedDatetime, IsDeleted) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) RETURNING Id;", [
				badge.Name, badge.Description, badge.Hint, badge.ImageUrl, badge.ForUrl, Number(badge.IsSecret), badge.CreatedByUserId, badge.CreatedDatetime.getTime(), badge.LastModifiedByUserId ?? null, badge.LastModifiedDatetime?.getTime() ?? null, badge.DeletedByUserId ?? null, badge.DeletedDatetime?.getTime() ?? null, Number(badge.IsDeleted)
			]))[0]["Id"];
		} else {
			await Database.Instance.query(`UPDATE Badge SET Name = ?, Description = ?, Hint = ?, ImageUrl = ?, ForUrl = ?, IsSecret = ?, CreatedByUserId = ?, CreatedDatetime = ?, LastModifiedByUserId = ?, LastModifiedDatetime = ?, DeletedByUserId = ?, DeletedDatetime = ?, IsDeleted = ? WHERE Id = ?`, [
				badge.Name, badge.Description, badge.Hint, badge.ImageUrl, badge.ForUrl, Number(badge.IsSecret), badge.CreatedByUserId, badge.CreatedDatetime.getTime(), badge.LastModifiedByUserId ?? null, badge.LastModifiedDatetime?.getTime() ?? null, badge.DeletedByUserId ?? null, badge.DeletedDatetime?.getTime() ?? null, Number(badge.IsDeleted), badge.Id
			]);
		}

		return badge;
	}
}

function populateBadgeFromDB(badge:Badge, dbBadge:any) {
	badge.Id = dbBadge.Id;
	badge.Name = dbBadge.Name;
	badge.Description = dbBadge.Description;
	badge.Hint = dbBadge.Hint;
	badge.ImageUrl = dbBadge.ImageUrl;
	badge.ForUrl = dbBadge.ForUrl;
	badge.IsSecret = dbBadge.IsSecret[0] === 1;
	badge.CreatedByUserId = dbBadge.CreatedByUserId;
	badge.CreatedDatetime = new Date(dbBadge.CreatedDatetime);
	badge.LastModifiedByUserId = dbBadge.LastModifiedByUserId;
	badge.LastModifiedDatetime = RepoBase.convertNullableDatetimeIntToDate(dbBadge.LastModifiedDatetime);
	badge.DeletedByUserId = dbBadge.DeletedByUserId;
	badge.DeletedDatetime = RepoBase.convertNullableDatetimeIntToDate(dbBadge.DeletedDatetime);
	badge.IsDeleted = dbBadge.IsDeleted[0] === 1;
}