using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.NOCList
{
    public static class NOCListQB
    {
        public const string GET_NOCLIST = @"SELECT
    A.NOCLISTID AS NOCListId,
    A.NOCLISTCODE AS NOCListCode,
    A.NOCLISTNAME AS NOCListName,
    A.SORTORDER AS NOCListSortOrder,
    A.STATUS AS NOCListStatus,
    A.VERSION AS NOCListVersion,
    A.CREATEDBYID AS NOCListCreatedById,
    A.CREATEDON AS NOCListCreatedOn,
    A.MODIFIEDBYID AS NOCListModifiedById,
    A.MODIFIEDON AS NOCListModifiedOn
FROM MNOCLIST A
WHERE A.NOCLISTID = @noclistid";

        public const string GET_NOCLISTDETAIL = @"SELECT
    D.NOCLISTDETAILID AS NOCListDetailId,
    D.NOCLISTID AS NOCListId,
    D.SLNO AS NOCListDetailSlNo,
    D.PARTICULARS AS NOCListDetailParticulars,
    D.REMARKS AS NOCListDetailRemarks,

    D.DEPARTMENTID AS DepartmentId,
    DEPT.DEPARTMENTCODE AS DepartmentCode,
    DEPT.DEPARTMENTNAME AS DepartmentName

FROM MNOCLISTDETAIL D
LEFT JOIN MDEPARTMENT DEPT ON D.DEPARTMENTID = DEPT.DEPARTMENTID
WHERE D.NOCLISTID = @noclistid
ORDER BY D.SLNO";

        public const string SAVE_NOCLIST = @"INSERT INTO MNOCLIST
(
    NOCLISTID, NOCLISTCODE, NOCLISTNAME, SORTORDER,
    STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @NOCListId, @NOCListCode, @NOCListName, @NOCListSortOrder,
    @NOCListStatus, 1, @NOCListCreatedById, @NOCListCreatedOn, @NOCListModifiedById, @NOCListModifiedOn
);";

        public const string UPDATE_NOCLIST = @"UPDATE MNOCLIST
SET
    NOCLISTCODE   = @NOCListCode,
    NOCLISTNAME   = @NOCListName,
    SORTORDER     = @NOCListSortOrder,
    STATUS        = @NOCListStatus,
    VERSION       = VERSION + 1,
    MODIFIEDBYID  = @NOCListModifiedById,
    MODIFIEDON    = @NOCListModifiedOn
WHERE NOCLISTID = @NOCListId;";

        public const string DELETE_NOCLIST = @"DELETE FROM MNOCLIST
                                            WHERE NOCLISTID = @noclistid;";

        public const string SAVE_NOCLISTDETAIL = @"INSERT INTO MNOCLISTDETAIL
(
    NOCLISTDETAILID, NOCLISTID, SLNO, PARTICULARS, DEPARTMENTID, REMARKS
)
VALUES
(
    @NOCListDetailId, @NOCListId, @NOCListDetailSlNo, @NOCListDetailParticulars, @DepartmentId, @NOCListDetailRemarks
);";

        public const string DELETE_NOCLISTDETAIL = @"DELETE FROM MNOCLISTDETAIL
                                            WHERE NOCLISTID = @noclistid;";

        public const string GET_SELECTLIST_NOCLIST = @"SELECT
    A.NOCLISTID   AS Id,
    A.NOCLISTCODE AS Code,
    A.NOCLISTNAME AS Name
FROM MNOCLIST A
WHERE 1 = 1";
    }
}