namespace MaintenanceDAL.Query.ActivityMaterial
{
    // Confirmed against the real MACTIVITYMATERIAL DDL via the legacy NHibernate mapping
    // (DAL/MaintenanceDAL/HibernateMapFile/ActivityMaterial.hbm.xml) — no TENANTID column.
    public static class ActivityMaterialQB
    {
        public const string GET_ACTIVITYMATERIAL = @"SELECT
                AM.ACTIVITYMATERIALID   AS ActivityMaterialId,
                AM.NATURE               AS ActivityMaterialNature,
                AM.ASSETTYPEID          AS AssetTypeId,
                AM.RESOURCETYPEID       AS ResourceTypeId,
                AM.MACHINETYPEID        AS MachineTypeId,
                AM.ACTIVITYID           AS ActivityId,
                AM.REMARKS              AS ActivityMaterialRemarks,
                AM.SLNO                 AS ActivityMaterialSlNo,
                AM.ITEMID               AS ItemId,
                AM.QUANTITY             AS ActivityMaterialQuantity,
                AM.SKUID                AS SkuId,
                AM.USAGETYPE            AS ActivityMaterialUsageType,
                AM.SOURCETYPE           AS ActivityMaterialSourceType,
                AM.SORTORDER            AS ActivityMaterialSortOrder,
                AM.STATUS               AS ActivityMaterialStatus,
                AM.VERSION              AS ActivityMaterialVersion,
                AM.CREATEDBYID          AS ActivityMaterialCreatedById,
                AM.CREATEDON            AS ActivityMaterialCreatedOn,
                AM.MODIFIEDBYID         AS ActivityMaterialModifiedById,
                AM.MODIFIEDON           AS ActivityMaterialModifiedOn,
                AM.DURATION             AS ActivityMaterialDuration
            FROM MACTIVITYMATERIAL AM
            WHERE AM.ACTIVITYMATERIALID = @ActivityMaterialId;";

        public const string GET_ACTIVITYMATERIAL_LIST_BY_ACTIVITY = @"SELECT
                AM.ACTIVITYMATERIALID   AS ActivityMaterialId,
                AM.NATURE               AS ActivityMaterialNature,
                AM.ASSETTYPEID          AS AssetTypeId,
                AM.RESOURCETYPEID       AS ResourceTypeId,
                AM.MACHINETYPEID        AS MachineTypeId,
                AM.ACTIVITYID           AS ActivityId,
                AM.REMARKS              AS ActivityMaterialRemarks,
                AM.SLNO                 AS ActivityMaterialSlNo,
                AM.ITEMID               AS ItemId,
                AM.QUANTITY             AS ActivityMaterialQuantity,
                AM.SKUID                AS SkuId,
                AM.USAGETYPE            AS ActivityMaterialUsageType,
                AM.SOURCETYPE           AS ActivityMaterialSourceType,
                AM.SORTORDER            AS ActivityMaterialSortOrder,
                AM.STATUS               AS ActivityMaterialStatus,
                AM.VERSION              AS ActivityMaterialVersion,
                AM.CREATEDBYID          AS ActivityMaterialCreatedById,
                AM.CREATEDON            AS ActivityMaterialCreatedOn,
                AM.MODIFIEDBYID         AS ActivityMaterialModifiedById,
                AM.MODIFIEDON           AS ActivityMaterialModifiedOn,
                AM.DURATION             AS ActivityMaterialDuration
            FROM MACTIVITYMATERIAL AM
            WHERE AM.ACTIVITYID = @ActivityId
            AND   AM.STATUS <> 2
            ORDER BY AM.SLNO;";

        public const string SAVE_ACTIVITYMATERIAL = @"INSERT INTO MACTIVITYMATERIAL (
                ACTIVITYMATERIALID, NATURE, ASSETTYPEID, RESOURCETYPEID, MACHINETYPEID, ACTIVITYID,
                REMARKS, SLNO, ITEMID, QUANTITY, SKUID, USAGETYPE, SOURCETYPE, SORTORDER, STATUS, VERSION,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DURATION)
            VALUES (
                @ActivityMaterialId, @ActivityMaterialNature, @AssetTypeId, @ResourceTypeId, @MachineTypeId, @ActivityId,
                @ActivityMaterialRemarks, @ActivityMaterialSlNo, @ItemId, @ActivityMaterialQuantity, @SkuId, @ActivityMaterialUsageType,
                @ActivityMaterialSourceType, @ActivityMaterialSortOrder, @ActivityMaterialStatus, @ActivityMaterialVersion,
                @ActivityMaterialCreatedById, @ActivityMaterialCreatedOn, @ActivityMaterialModifiedById, @ActivityMaterialModifiedOn, @ActivityMaterialDuration);";

        public const string UPDATE_ACTIVITYMATERIAL = @"UPDATE MACTIVITYMATERIAL SET
                NATURE = @ActivityMaterialNature, ASSETTYPEID = @AssetTypeId, RESOURCETYPEID = @ResourceTypeId,
                MACHINETYPEID = @MachineTypeId, ACTIVITYID = @ActivityId, REMARKS = @ActivityMaterialRemarks,
                SLNO = @ActivityMaterialSlNo, ITEMID = @ItemId, QUANTITY = @ActivityMaterialQuantity, SKUID = @SkuId,
                USAGETYPE = @ActivityMaterialUsageType, SOURCETYPE = @ActivityMaterialSourceType,
                SORTORDER = @ActivityMaterialSortOrder, STATUS = @ActivityMaterialStatus, VERSION = VERSION + 1,
                MODIFIEDBYID = @ActivityMaterialModifiedById, MODIFIEDON = @ActivityMaterialModifiedOn, DURATION = @ActivityMaterialDuration
            WHERE ACTIVITYMATERIALID = @ActivityMaterialId;";

        public const string DELETE_ACTIVITYMATERIAL = @"DELETE FROM MACTIVITYMATERIAL
            WHERE ACTIVITYMATERIALID = @ActivityMaterialId;";
    }
}