Untitled

mail@pastecode.io avatar
unknown
typescript
2 years ago
4.5 kB
1
Indexable
Never
import { Request, Response } from 'lambda-api';
import { ClientBase } from 'pg';
import { failure, success } from '../../../shared/libs/response';
import { getAuth } from '../../../shared/libs/auth';
import { Page, pageMeta, pageSorting } from '../../../shared/libs/page';
import code from '../../../shared/libs/code';
import { AlbumModel } from '../../../shared/models/album.model';

/**
 *
 * @param db
 * @param auth
 * @param page
 */
async function queryAlbumList(
  db: ClientBase,
  auth: any,
  page: Page = new Page()
): Promise<Array<AlbumModel>> {
  try {
    const sorting = pageSorting(page, ['published_on_dashboard', 'published_on_photo_gallery']);

    const queryResult = await db.query(
      `
            SELECT DISTINCT
              ga.gallery_album_id,
              ga.album_name,
              ga.album_description,
              ga.school_level_relation_id,
              ga.school_location_id,
              ga.school_level_id,
              ga.created_by,
              (SELECT COUNT(*) FROM gallery_mapping_photo gmp WHERE gmp.gallery_album_id = ga.gallery_album_id) AS total_photo,
              gp.gallery_photo_id,
              gp.gallery_photo_pathfile AS thumbnail_photo_pathfile,
              (CASE WHEN gppod.publish_date IS NULL THEN FALSE ELSE TRUE END) AS published_on_dashboard,
              (CASE WHEN gppog.publish_date IS NULL THEN FALSE ELSE TRUE END) AS published_on_gallery,
              to_char(gppod.publish_date, 'YYYY-MM-DD') AS dashboard_publish_date_format,
              to_char(gppod.publish_date, 'DD Month YYYY') AS dashboard_publish_date,
              to_char(gppod.end_date, 'YYYY-MM-DD') AS dashboard_end_date_format,
              to_char(gppog.publish_date, 'YYYY-MM-DD') AS gallery_publish_date_format,
              to_char(gppog.publish_date, 'DD Month YYYY') AS gallery_publish_date,
              to_char(gppog.end_date, 'YYYY-MM-DD') AS gallery_end_date_format,
              CAST(count(*) OVER() AS INT) AS total
            FROM gallery_album ga
              JOIN gallery_mapping_photo gmp ON ga.gallery_album_id = gmp.gallery_album_id
                AND gmp.deleted_at IS NULL
              JOIN gallery_photo gp ON gmp.gallery_photo_id = gp.gallery_photo_id
                AND gp.deleted_at IS NULL
              JOIN gallery_publish_photo_on_dashboard gppod ON ga.gallery_album_id = gppod.gallery_album_id
                AND gppod.deleted_at IS NULL
              JOIN gallery_publish_photo_on_gallery gppog ON ga.gallery_album_id = gppog.gallery_album_id
                AND gppog.deleted_at IS NULL
            WHERE ga.deleted_at IS NULL
              AND ga.tenant_uuid = $1
              AND gp.gallery_photo_id =
                  (SELECT MIN(gmp.gallery_photo_id)
                   FROM gallery_mapping_photo gmp
                   WHERE gmp.gallery_album_id = gallery_album_id
                     AND gmp.deleted_at IS NULL)
            GROUP BY
              ga.gallery_album_id,
              ga.album_name,
              ga.album_description,
              ga.school_level_relation_id,
              ga.school_location_id,
              ga.school_level_id,
              ga.created_by,
              gp.gallery_photo_pathfile,
              gppod.publish_date,
              gppod.end_date,
              gppog.publish_date,
              gppog.end_date,
              gp.gallery_photo_id,
              published_on_dashboard,
              dashboard_end_date,
              published_on_photo_gallery,
              gallery_end_date
            ${sorting}
            OFFSET $2
            LIMIT $3
        `,
      [auth.tenant_uuid, page.offset, page.size]
    );

    if (queryResult.rowCount === 0) {
      return [];
    }

    return queryResult.rows;
  } catch (e) {
    console.error('[ERROR-QUERY] - func: queryAlbumList', e);
    throw Error(code.database_error);
  }
}

/**
 *
 * @param req
 * @param res
 */
export default async function getAlbumList(req: Request, res: Response): Promise<any> {
  try {
    const auth = getAuth(req);
    const page = new Page(req.query);
    // console.log(page);
    const albumList = await queryAlbumList(req.namespace.db, auth, page);

    success(res, {
      status: true,
      version: req.version,
      message: 'success',
      data: albumList,
      meta: {
        page: pageMeta(page, albumList),
      },
    });
  } catch (e) {
    failure(res, e);
  }
}