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

namespace FMDAL.Query.Meter
{
    public static class MeterQB
    {
        public const string GET_SELECTLIST_METER_SQL = @"
                                                    WITH Meter AS (
                                                        SELECT
                                                            m.METERID   AS Id,
                                                            m.METERNAME AS Name,
                                                            m.METERTYPE AS MeterType,
                                                            ROW_NUMBER() OVER (ORDER BY m.METERID) AS RowNum
                                                        FROM MMETER m
                                                        WHERE m.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Name,
                                                        MeterType
                                                    FROM Meter
                                                    WHERE
                                                          (@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        public const string GET_SELECTLIST_METER_PG = @"
                                                    WITH Meter AS (
                                                        SELECT
                                                            m.METERID   AS ""Id"",
                                                            m.METERNAME AS ""Name"",
                                                            m.METERTYPE AS ""MeterType"",
                                                            ROW_NUMBER() OVER (ORDER BY m.METERID) AS ""RowNum""
                                                        FROM MMETER m
                                                        WHERE m.STATUS = 1
                                                    )
                                                    SELECT
                                                        ""Id"",
                                                        ""Name"",
                                                        ""MeterType""
                                                    FROM Meter
                                                    WHERE
                                                          (:firstnumber = -1 AND :maxresult = -1)
                                                       OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
                                                    ";

        public const string GET_SELECTLIST_METER_ORACLE = @";";
        public const string GET_SELECTLIST_METER_MYSQL = @";";

        // Confirmed against the real MMETER DDL.
        public const string GET_METER = @"SELECT
                M.METERID                    AS MeterId,
                M.METERNAME                   AS MeterName,
                M.METERNATURE                 AS MeterNature,
                M.METERTYPE                   AS MeterType,
                M.UTILITYTYPE                 AS MeterUtilityType,
                M.METERNUMBER                 AS MeterMeterNumber,
                M.METERRUNTYPE                AS MeterRunType,
                M.PROVIDERID                  AS PartyId,
                M.ACCOUNTNUMBER               AS MeterAccountNumber,
                M.METERATTACHEDTYPE           AS MeterAttachedType,
                M.TARIFFCATEGORYID            AS TariffCategoryId,
                M.BILLDATE                    AS MeterBillDate,
                M.BILLCYCLE                   AS MeterBillCycle,
                M.PAYBYDATE                   AS MeterPayByDate,
                M.OUID                        AS OrganizationUnitId,
                M.PARTYBRANCHID               AS PartyBranchId,
                M.BILLPARAMETERSETID          AS ParameterSetId,
                M.METERRESETVALUE             AS MeterSetValue,
                M.CONSUMPTIONPARAMETERID      AS ConsumptionParameterId,
                M.LASTMETERREADINGAT          AS MeterLastMeterReadingAt,
                M.LASTMETERVALUE              AS LastMetervalue,
                M.ASSETID                     AS AssetId,
                M.TYPEOFMETERID               AS TypeOfMeterId,
                M.ISINCLUDEINCONSUMPTION      AS MeterIsIncludeInConsumption,
                M.SORTORDER                   AS MeterSortOrder,
                M.STATUS                      AS MeterStatus,
                M.VERSION                     AS MeterVersion,
                M.SOURCETYPE                  AS MeterSourceType,
                M.CREATEDBYID                 AS MeterCreatedById,
                M.CREATEDON                   AS MeterCreatedOn,
                M.MODIFIEDBYID                AS MeterModifiedById,
                M.MODIFIEDON                  AS MeterModifiedOn,
                M.TENANTID                    AS TenantId
            FROM MMETER M
            WHERE M.METERID = @MeterId
            AND   M.TENANTID = @TenantId;";

        public const string SAVE_METER = @"INSERT INTO MMETER (
                METERID, METERNAME, METERNATURE, METERTYPE, UTILITYTYPE, METERNUMBER, METERRUNTYPE,
                PROVIDERID, ACCOUNTNUMBER, METERATTACHEDTYPE, TARIFFCATEGORYID, BILLDATE, BILLCYCLE, PAYBYDATE,
                OUID, PARTYBRANCHID, BILLPARAMETERSETID, METERRESETVALUE, CONSUMPTIONPARAMETERID,
                LASTMETERREADINGAT, LASTMETERVALUE, ASSETID, TYPEOFMETERID, ISINCLUDEINCONSUMPTION,
                SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES (
                @MeterId, @MeterName, @MeterNature, @MeterType, @MeterUtilityType, @MeterMeterNumber, @MeterRunType,
                @PartyId, @MeterAccountNumber, @MeterAttachedType, @TariffCategoryId, @MeterBillDate, @MeterBillCycle, @MeterPayByDate,
                @OrganizationUnitId, @PartyBranchId, @ParameterSetId, @MeterSetValue, @ConsumptionParameterId,
                @MeterLastMeterReadingAt, @LastMetervalue, @AssetId, @TypeOfMeterId, @MeterIsIncludeInConsumption,
                @MeterSortOrder, @MeterStatus, @MeterVersion, @MeterSourceType, @MeterCreatedById, @MeterCreatedOn, @MeterModifiedById, @MeterModifiedOn, @TenantId);";

        public const string UPDATE_METER = @"UPDATE MMETER SET
                METERNAME = @MeterName, METERNATURE = @MeterNature, METERTYPE = @MeterType,
                UTILITYTYPE = @MeterUtilityType, METERNUMBER = @MeterMeterNumber, METERRUNTYPE = @MeterRunType,
                PROVIDERID = @PartyId, ACCOUNTNUMBER = @MeterAccountNumber, METERATTACHEDTYPE = @MeterAttachedType,
                TARIFFCATEGORYID = @TariffCategoryId, BILLDATE = @MeterBillDate, BILLCYCLE = @MeterBillCycle, PAYBYDATE = @MeterPayByDate,
                OUID = @OrganizationUnitId, PARTYBRANCHID = @PartyBranchId, BILLPARAMETERSETID = @ParameterSetId,
                METERRESETVALUE = @MeterSetValue, CONSUMPTIONPARAMETERID = @ConsumptionParameterId,
                LASTMETERREADINGAT = @MeterLastMeterReadingAt, LASTMETERVALUE = @LastMetervalue,
                ASSETID = @AssetId, TYPEOFMETERID = @TypeOfMeterId, ISINCLUDEINCONSUMPTION = @MeterIsIncludeInConsumption,
                SORTORDER = @MeterSortOrder, STATUS = @MeterStatus, VERSION = VERSION + 1, SOURCETYPE = @MeterSourceType,
                MODIFIEDBYID = @MeterModifiedById, MODIFIEDON = @MeterModifiedOn
            WHERE METERID = @MeterId
            AND   TENANTID = @TenantId;";

        public const string DELETE_METER = @"UPDATE MMETER SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE METERID = @MeterId
            AND   TENANTID = @TenantId;";
    }
}