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

namespace FrameworkDAL.Query.Formula
{
    public static class FormulaQB
    {
        public const string GET_FORMULA = @"  
         SELECT
         MF.FORMULAID AS FormulaId,
         MF.FORMULACODE AS FormulaCode,
         MF.FORMULANAME AS FormulaName,
         MF.FORMULATYPE AS FormulaFormulaType,
         MF.DATATYPE AS FormulaDataType,
         MF.DATASIZE AS FormulaDataSize,
         MF.EXPRESSION AS FormulaExpression,
         MF.MODULEID AS ModuleId,
         MF.SECTION AS FormulaSection,
         MF.PHASE AS FormulaPhase,
         MF.FUNCTIONNAME AS FormulaFunctionName,
         MF.SORTORDER AS FormulaSortOrder,
         MF.VERSION AS FormulaVersion,
         MF.STATUS AS FormulaStatus,
         MF.SOURCETYPE AS FormulaSourceType,
         MF.CREATEDBYID AS FormulaCreatedById,
         MF.CREATEDON AS FormulaCreatedOn,
         MF.MODIFIEDBYID AS FormulaModifiedById,
         MF.MODIFIEDON AS FormulaModifiedOn,
         MF.PRINTINGNAME AS FormulaPrintingName,
         MF.REMARKS AS FormulaRemarks,
         MF.TENANTID AS TenantId,
         MF.ISRUNNINGTOTAL AS FormulaIsRunningTotal,
         MF.ISPROCESSDURINGARREAR AS FormulaIsProcessDuringArrear
         FROM 
         MFORMULA MF
         WHERE 
         MF.FORMULAID = @formulaid;

        ";


        public const string SAVE_FORMULA = @"INSERT INTO MFORMULA 
                                    (
                                        FORMULAID,
                                        FORMULACODE,
                                        FORMULANAME,
                                        FORMULATYPE,
                                        DATATYPE,
                                        DATASIZE,
                                        EXPRESSION,
                                        MODULEID,
                                        SECTION,
                                        PHASE,
                                        FUNCTIONNAME,
                                        SORTORDER,
                                        VERSION,
                                        STATUS,
                                        SOURCETYPE,
                                        CREATEDBYID,
                                        CREATEDON,
                                        MODIFIEDBYID,
                                        MODIFIEDON,
                                        PRINTINGNAME,
                                        REMARKS,
                                        ISRUNNINGTOTAL,
                                        ISPROCESSDURINGARREAR
                                    )
                                    VALUES
                                    (
                                        @FormulaId,
                                        @FormulaCode,
                                        @FormulaName,
                                        @FormulaFormulaType,
                                        @FormulaDataType,
                                        @FormulaDataSize,
                                        @FormulaExpression,
                                        @ModuleId,
                                        @FormulaSection,
                                        @FormulaPhase,
                                        @FormulaFunctionName,
                                        @FormulaSortOrder,
                                        @FormulaVersion,
                                        @FormulaStatus,
                                        @FormulaSourceType,
                                        @FormulaCreatedById,
                                        @FormulaCreatedOn,
                                        @FormulaModifiedById,
                                        @FormulaModifiedOn,
                                        @FormulaPrintingName,
                                        @FormulaRemarks,
                                        @FormulaIsRunningTotal,
                                        @FormulaIsProcessDuringArrear
                                    );
                                    ";



        public const string UPDATE_FORMULA = @"UPDATE MFORMULA
                                                SET
                                                    FORMULACODE = @FormulaCode,
                                                    FORMULANAME = @FormulaName,
                                                    FORMULATYPE = @FormulaFormulaType,
                                                    DATATYPE = @FormulaDataType,
                                                    DATASIZE = @FormulaDataSize,
                                                    EXPRESSION = @FormulaExpression,
                                                    MODULEID = @ModuleId,
                                                    SECTION = @FormulaSection,
                                                    PHASE = @FormulaPhase,
                                                    FUNCTIONNAME = @FormulaFunctionName,
                                                    SORTORDER = @FormulaSortOrder,
                                                    VERSION = @FormulaVersion,
                                                    STATUS = @FormulaStatus,
                                                    SOURCETYPE = @FormulaSourceType,
                                                    MODIFIEDBYID = @FormulaModifiedById,
                                                    MODIFIEDON = @FormulaModifiedOn,
                                                    PRINTINGNAME = @FormulaPrintingName,
                                                    REMARKS = @FormulaRemarks,
                                                    ISRUNNINGTOTAL = @FormulaIsRunningTotal,
                                                    ISPROCESSDURINGARREAR = @FormulaIsProcessDuringArrear
                                                WHERE FORMULAID = @FormulaId;
                                                ";
        public const string DELETE_FORMULA = @"DELETE FROM MFORMULA
                                            WHERE FORMULAID = @formulaid;";

        public const string GET_SELECTLIST_FORMULA = @"WITH Formula AS (
                                            SELECT
                                                A.FORMULAID     AS Id, 
                                                A.FORMULACODE   AS Code,
                                                A.FORMULANAME   AS Name,
                                                A.SECTION       AS Section,
                                                'F_' + A.FORMULACODE AS FormulaCode,
                                                ROW_NUMBER() OVER (ORDER BY A.FORMULAID) AS RowNum
                                            FROM MFORMULA A
                                        )
                                        SELECT Id, Code, Name, Section, FormulaCode
                                        FROM Formula
                                       WHERE 
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";




    }
}