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

namespace MMDAL.Query.CalculationFormula
{
    public  class CalculationFormulaQB
    {
        public const string GET_CALCULATIONFORMULA = @"SELECT
                                                        CF.CALCULATIONFORMULAID   AS CalculationFormulaId,
                                                        CF.CALCULATIONFORMULA     AS CalculationFormula,
                                                        CF.SCOPETYPE              AS ScopeType,
                                                        CF.SCOPEID                AS ScopeId,
                                                        CASE CF.SCOPETYPE
                                                            WHEN 0 THEN IT.ITEMCODE
                                                            WHEN 1 THEN SH.SHAPECODE
                                                            WHEN 2 THEN MT.MATERIALCODE
                                                            WHEN 3 THEN PR.PROJECTCODE
                                                            WHEN 4 THEN PA.PARTYCODE
                                                            WHEN 5 THEN 'GLOBAL'
                                                        END AS ScopeCode,

                                                        CASE CF.SCOPETYPE
                                                            WHEN 0 THEN IT.ITEMNAME
                                                            WHEN 1 THEN SH.SHAPENAME
                                                            WHEN 2 THEN MT.MATERIALNAME
                                                            WHEN 3 THEN PR.PROJECTNAME
                                                            WHEN 4 THEN PA.PARTYNAME
                                                            WHEN 5 THEN 'GLOBAL'
                                                        END AS ScopeName,
                                                        CF.PHASENO                AS PhaseNo,
                                                        CF.FORMULATEXT            AS FormulaText,
                                                        CF.OUTPUTUNITID           AS OutputUnitId,
                                                        MU.UOMCODE                AS OutputUnitCode,
                                                        MU.UOMNAME                AS OutputUnitName,
                                                        CF.DESCRIPTION            AS Description,
                                                        CF.SORTORDER              AS CalculationFormulaSortOrder,
                                                        CF.STATUS                 AS CalculationFormulaStatus,
                                                        CF.VERSION                AS CalculationFormulaVersion,
                                                        CF.SOURCETYPE             AS CalculationFormulaSourceType,
                                                        CF.CREATEDBYID            AS CalculationFormulaCreatedById,
                                                        CF.CREATEDON              AS CalculationFormulaCreatedOn,
                                                        CF.MODIFIEDBYID           AS CalculationFormulaModifiedById,
                                                        CF.MODIFIEDON             AS CalculationFormulaModifiedOn
                                                    FROM MCALCULATIONFORMULA CF
                                                    LEFT JOIN MITEM     IT ON IT.ITEMID     = CF.SCOPEID AND CF.SCOPETYPE = 0
                                                    LEFT JOIN MSHAPE    SH ON SH.SHAPEID    = CF.SCOPEID AND CF.SCOPETYPE = 1
                                                    LEFT JOIN MMATERIAL MT ON MT.MATERIALID = CF.SCOPEID AND CF.SCOPETYPE = 2
                                                    LEFT JOIN MPROJECT  PR ON PR.PROJECTID  = CF.SCOPEID AND CF.SCOPETYPE = 3
                                                    LEFT JOIN MPARTY    PA ON PA.PARTYID    = CF.SCOPEID AND CF.SCOPETYPE = 4
                                                    LEFT JOIN MUOM MU ON MU.UOMID = CF.OUTPUTUNITID
                                                    LEFT JOIN MUSER CU ON CU.USERID = CF.CREATEDBYID
                                                    LEFT JOIN MUSER MUU ON MUU.USERID = CF.MODIFIEDBYID
                                                    WHERE CALCULATIONFORMULAID = @calculationformulaid;";

        public const string SAVE_CALCULATIONFORMULA = @"INSERT INTO MCALCULATIONFORMULA
                                                        (
                                                            CALCULATIONFORMULA,
                                                            SCOPETYPE,
                                                            SCOPEID,
                                                            PHASENO,
                                                            FORMULATEXT,
                                                            OUTPUTUNITID,
                                                            DESCRIPTION,
                                                            SORTORDER,
                                                            STATUS,
                                                            VERSION,
                                                            SOURCETYPE,
                                                            CREATEDBYID,
                                                            CREATEDON,
                                                            MODIFIEDBYID,
                                                            MODIFIEDON
                                                        )
                                                        VALUES
                                                        (
                                                            @CalculationFormula,
                                                            @ScopeType,
                                                            @ScopeId,
                                                            @PhaseNo,
                                                            @FormulaText,
                                                            @OutputUnitId,
                                                            @Description,
                                                            @CalculationFormulaSortOrder,
                                                            @CalculationFormulaStatus,
                                                            @CalculationFormulaVersion,
                                                            @CalculationFormulaSourceType,
                                                            @CalculationFormulaCreatedById,
                                                            @CalculationFormulaCreatedOn,
                                                            @CalculationFormulaModifiedById,
                                                            @CalculationFormulaModifiedOn
                                                        );";

        public const string UPDATE_CALCULATIONFORMULA = @"UPDATE MCALCULATIONFORMULA
                                                        SET
                                                            CALCULATIONFORMULA = @CalculationFormula,
                                                            SCOPETYPE          = @ScopeType,
                                                            SCOPEID            = @ScopeId,
                                                            PHASENO            = @PhaseNo,
                                                            FORMULATEXT        = @FormulaText,
                                                            OUTPUTUNITID       = @OutputUnitId,
                                                            DESCRIPTION        = @Description,
                                                            SORTORDER          = @CalculationFormulaSortOrder,
                                                            STATUS             = @CalculationFormulaStatus,
                                                            VERSION            = @CalculationFormulaVersion,
                                                            SOURCETYPE         = @CalculationFormulaSourceType,
                                                            MODIFIEDBYID       = @CalculationFormulaModifiedById,
                                                            MODIFIEDON         = @CalculationFormulaModifiedOn
                                                        WHERE CALCULATIONFORMULAID = @CalculationFormulaId;";

        public const string DELETE_CALCULATIONFORMULA = @"DELETE FROM MCALCULATIONFORMULA
                                                            WHERE CALCULATIONFORMULAID= @calculationformulaid;";

        public const string GET_SELECTLIST_CALCULATIONFORMULA = @"WITH PagedCalculationFormula AS (
                                                        SELECT
                                                            CF.CALCULATIONFORMULAID    AS Id,
                                                            CF.CALCULATIONFORMULA      AS Formula,
                                                            CF.SCOPETYPE               AS ScopeType,
                                                            ROW_NUMBER() OVER (ORDER BY CF.CALCULATIONFORMULAID) AS RowNum
                                                        FROM 
                                                            MCALCULATIONFORMULA CF
                                                        WHERE 
                                                            CF.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Formula,
                                                        ScopeType
                                                    FROM PagedCalculationFormula
                                                   WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

    }

}