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

namespace FAMDAL.Query.Depreciation
{
    public static class DepreciationQB
    {
        public const string GET_DEPRECIATION = @"SELECT
                                                A.DEPRECIATIONID         AS DepreciationId,
                                                A.DEPRECIATIONCODE       AS DepreciationCode,
                                                A.DEPRECIATIONNAME       AS DepreciationName,
                                                A.CALCULATIONMETHOD      AS DepreciationCalculationMethod,
                                                A.PERIODTYPE             AS DepreciationPeriodType,
                                                A.PARTIALMONTH           AS DepreciationPartialMonth,
                                                A.PARTIALPERCENTAGE      AS DepreciationPartialPercentage,
                                                A.RESIDUALTYPE           AS DepreciationResidualType,
                                                A.RESIDUAL               AS DepreciationResidual,
                                                A.DAYSPERYEAR            AS DepreciationDaysperYear,

                                                A.SORTORDER              AS DepreciationSortOrder,
                                                A.STATUS                 AS DepreciationStatus,
                                                A.VERSION                AS DepreciationVersion,
                                                A.SOURCETYPE             AS DepreciationSourceType,
                                                A.TENANTID               AS DepreciationTenantId,

                                                A.CREATEDBYID            AS DepreciationCreatedById,
                                                A.CREATEDON              AS DepreciationCreatedOn,
                                                CREATEDBY.EMPLOYEENAME   AS DepreciationCreatedByName,

                                                A.MODIFIEDBYID           AS DepreciationModifiedById,
                                                A.MODIFIEDON             AS DepreciationModifiedOn,
                                                MODIFIEDBY.EMPLOYEENAME  AS DepreciationModifiedByName

                                            FROM MDEPRECIATION A
                                            LEFT JOIN MEMPLOYEE CREATEDBY  ON CREATEDBY.EMPLOYEEID = A.CREATEDBYID
                                            LEFT JOIN MEMPLOYEE MODIFIEDBY ON MODIFIEDBY.EMPLOYEEID = A.MODIFIEDBYID
                                            WHERE
                                            A.DEPRECIATIONID = @depreciationid;";

        public const string GET_SELECTLIST_DEPRECIATION = @"
                                                    WITH PagedDepreciation AS (
                                                        SELECT
                                                            A.DEPRECIATIONID   AS Id,
                                                            A.DEPRECIATIONCODE AS Code,
                                                            A.DEPRECIATIONNAME AS Name,
                                                            A.RESIDUAL         AS Residual,
                                                            ROW_NUMBER() OVER (ORDER BY A.DEPRECIATIONID) AS RowNum
                                                        FROM MDEPRECIATION A
                                                        WHERE A.STATUS = 1
                                                          AND (@depreciationname IS NULL OR A.DEPRECIATIONNAME LIKE '%' + @depreciationname + '%')
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name,
                                                        Residual
                                                    FROM PagedDepreciation
                                                    WHERE
                                                          (@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);
                                                    ";

        public const string SAVE_DEPRECIATION = @"INSERT INTO MDEPRECIATION (
                DEPRECIATIONID, DEPRECIATIONCODE, DEPRECIATIONNAME, CALCULATIONMETHOD, PERIODTYPE,
                PARTIALMONTH, PARTIALPERCENTAGE, RESIDUALTYPE, RESIDUAL, DAYSPERYEAR,
                SORTORDER, STATUS, VERSION, SOURCETYPE, TENANTID,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES (
                @DepreciationId, @DepreciationCode, @DepreciationName, @DepreciationCalculationMethod, @DepreciationPeriodType,
                @DepreciationPartialMonth, @DepreciationPartialPercentage, @DepreciationResidualType, @DepreciationResidual, @DepreciationDaysperYear,
                @DepreciationSortOrder, @DepreciationStatus, @DepreciationVersion, @DepreciationSourceType, @DepreciationTenantId,
                @DepreciationCreatedById, @DepreciationCreatedOn, @DepreciationModifiedById, @DepreciationModifiedOn);";

        public const string UPDATE_DEPRECIATION = @"UPDATE MDEPRECIATION SET
                DEPRECIATIONCODE = @DepreciationCode,
                DEPRECIATIONNAME = @DepreciationName,
                CALCULATIONMETHOD = @DepreciationCalculationMethod,
                PERIODTYPE = @DepreciationPeriodType,
                PARTIALMONTH = @DepreciationPartialMonth,
                PARTIALPERCENTAGE = @DepreciationPartialPercentage,
                RESIDUALTYPE = @DepreciationResidualType,
                RESIDUAL = @DepreciationResidual,
                DAYSPERYEAR = @DepreciationDaysperYear,
                SORTORDER = @DepreciationSortOrder,
                STATUS = @DepreciationStatus,
                VERSION = VERSION + 1,
                MODIFIEDBYID = @DepreciationModifiedById,
                MODIFIEDON = @DepreciationModifiedOn
            WHERE DEPRECIATIONID = @DepreciationId
                AND TENANTID = @DepreciationTenantId;";

        public const string DELETE_DEPRECIATION = @"DELETE FROM MDEPRECIATION
            WHERE DEPRECIATIONID = @DepreciationId
                AND TENANTID = @DepreciationTenantId;";

        public const string GET_SELECTLIST_DEPRECIATION_ALL = @"SELECT
                DEPRECIATIONID   AS Id,
                DEPRECIATIONCODE AS Code,
                DEPRECIATIONNAME AS Name,
                RESIDUAL         AS Residual
            FROM MDEPRECIATION
            WHERE STATUS = 1
                AND TENANTID = @DepreciationTenantId;";
    }
}