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

namespace MMDAL.Query.Defect
{
    public class DefectQB
    {
        public const string GET_DEFECT = @"SELECT
                                            D.DEFECTID             AS DefectId,
                                            D.DEFECTCODE           AS DefectCode,
                                            D.DEFECTNAME           AS DefectName,
                                            D.POINTPERDEFECT       AS DefectPointPerDefect,
                                            D.REMARKS              AS DefectRemarks,
                                            D.SORTORDER            AS DefectSortOrder,
                                            D.STATUS               AS DefectStatus,
                                            D.VERSION              AS DefectVersion,
                                            D.CREATEDBYID          AS DefectCreatedById,
                                            D.CREATEDON            AS DefectCreatedOn,
                                            CU.USERNAME            AS DefectCreatedByName,
                                            D.MODIFIEDBYID         AS DefectModifiedById,
                                            D.MODIFIEDON           AS DefectModifiedOn,
                                            MU.USERNAME            AS DefectModifiedByName
                                        FROM MDEFECT D
                                        LEFT JOIN MUSER CU ON CU.USERID = D.CREATEDBYID
                                        LEFT JOIN MUSER MU ON MU.USERID = D.MODIFIEDBYID
                                        WHERE D.DEFECTID = @defectid;";

        public const string SAVE_DEFECT = @"INSERT INTO MDEFECT
                                            (
                                                DEFECTID,
                                                DEFECTCODE,
                                                DEFECTNAME,
                                                POINTPERDEFECT,
                                                REMARKS,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON
                                            )
                                            VALUES
                                            (
                                                @DefectId,
                                                @DefectCode,
                                                @DefectName,
                                                @DefectPointPerDefect,
                                                @DefectRemarks,
                                                @DefectSortOrder,
                                                @DefectStatus,
                                                @DefectVersion,
                                                @DefectCreatedById,
                                                @DefectCreatedOn,
                                                @DefectModifiedById,
                                                @DefectModifiedOn
                                            );";

        public const string UPDATE_DEFECT = @"UPDATE MDEFECT
                                            SET
                                                DEFECTCODE        = @DefectCode,
                                                DEFECTNAME        = @DefectName,
                                                POINTPERDEFECT    = @DefectPointPerDefect,
                                                REMARKS           = @DefectRemarks,
                                                SORTORDER         = @DefectSortOrder,
                                                STATUS            = @DefectStatus,
                                                VERSION           = @DefectVersion,
                                                MODIFIEDBYID      = @DefectModifiedById,
                                                MODIFIEDON        = @DefectModifiedOn
                                            WHERE
                                                DEFECTID = @DefectId;";

        public const string DELETE_DEFECT = @"DELETE FROM MDEFECT
                                              WHERE DEFECTID = @defectid;";

        public const string GET_SELECTLIST_DEFECT = @"SELECT
                                                        DEFECTID AS Id,
                                                        DEFECTCODE AS Code,
                                                        DEFECTNAME AS Name
                                                        FROM MDEFECT;";

    }
}