namespace WiDAL.Query.Wi;

public class WiProcessQB
{
    // Index: IX_MWIPROCESS_TENANTID on (TENANTID, STATUS, DISPLAYORDER)
    public const string GET_LIST = @" SELECT
                                        p.WIPROCESSID            AS WiProcessId,
                                        p.WIPROCESSCODE          AS WiProcessCode,
                                        p.WIPROCESSNAME          AS WiProcessName,
                                        p.DEPARTMENTID           AS DepartmentId,
                                        d.DEPARTMENTCODE         AS DepartmentCode,
                                        d.DEPARTMENTNAME         AS DepartmentName,
                                        p.DISPLAYORDER           AS DisplayOrder,
                                        p.DESCRIPTION            AS Description,
                                        p.VERSION,
                                        p.STATUS,
                                        p.SORTORDER,
                                        p.TENANTID
                                    FROM WI.MWIPROCESS p
                                    LEFT JOIN MDEPARTMENT d
                                        ON p.DEPARTMENTID = d.DEPARTMENTID
                                    WHERE p.TENANTID = @TenantId
                                      AND p.STATUS   = 1
                                    ORDER BY p.DISPLAYORDER, p.WIPROCESSCODE";

    public const string GET_BY_ID = @"SELECT
                                        p.WIPROCESSID           AS WiProcessId,
                                        p.WIPROCESSCODE         AS WiProcessCode,
                                        p.WIPROCESSNAME         AS WiProcessName,
                                        p.DEPARTMENTID          AS DepartmentId,
                                        d.DEPARTMENTCODE        AS DepartmentCode,
                                        d.DEPARTMENTNAME        AS DepartmentName,
                                        p.DISPLAYORDER          AS DisplayOrder,
                                        p.DESCRIPTION           AS Description,
                                        p.VERSION               AS Version,
                                        p.STATUS                AS Status,
                                        p.SORTORDER             AS SortOrder,
                                        p.CREATEDBYID           AS CreatedById,
                                        p.MODIFIEDBYID          AS ModifiedById,
                                        p.SOURCETYPE            AS SourceType,
                                        p.TENANTID              AS TenantId
                                    FROM WI.MWIPROCESS p
                                    LEFT JOIN MDEPARTMENT d
                                        ON p.DEPARTMENTID = d.DEPARTMENTID
                                    WHERE p.WIPROCESSID = @WiProcessId
                                      AND p.TENANTID    = @TenantId";

    public const string INSERT = @"INSERT INTO WI.MWIPROCESS
                                    (WIPROCESSID, WIPROCESSCODE, WIPROCESSNAME, DEPARTMENTID, DISPLAYORDER,
                                     DESCRIPTION, VERSION, STATUS, SORTORDER,
                                     CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
                                VALUES
                                    (@WiProcessId, @WiProcessCode, @WiProcessName, @DepartmentId, @DisplayOrder,
                                     @Description, @Version, @Status, @SortOrder,
                                     @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @SourceType, @TenantId)";

    public const string UPDATE = @"UPDATE WI.MWIPROCESS
                                    SET    WIPROCESSCODE = @WiProcessCode,
                                           WIPROCESSNAME = @WiProcessName,
                                           DEPARTMENTID  = @DepartmentId,
                                           DISPLAYORDER  = @DisplayOrder,
                                           DESCRIPTION   = @Description,
                                           STATUS        = @Status,
                                           SORTORDER     = @SortOrder,
                                           VERSION       = @Version,
                                           MODIFIEDBYID  = @ModifiedById,
                                           MODIFIEDON    = GETDATE()
                                    WHERE  WIPROCESSID = @WiProcessId";

    public const string GET_SELECTLIST_WIPROCESS= @"SELECT 
                                                        WIPROCESSID AS Id,
                                                        WIPROCESSCODE AS Code,
                                                        WIPROCESSNAME AS Name
                                                    FROM WI.MWIPROCESS
                                                    ORDER BY WIPROCESSNAME;";
    public const string DELETE_WIPROCESS = @"DELETE FROM WI.MWIPROCESS 
                                            WHERE WIPROCESSID = @wiProcessId;";
}