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

namespace FrameworkDAL.Query.FormulaField
{
    public static class FormulaFieldQB
    {
        public const string GET_FORMULAFIELD = @"SELECT 
                                                FF.FORMULAFIELDID AS FormulaFieldId,
                                                FF.FORMULAFIELDCODE AS FormulaFieldCode,
                                                FF.FORMULAFIELDNAME AS FormulaFieldName,
                                                FF.MODULEID AS ModuleId,
                                                M.MODULECODE AS ModuleCode,
                                                M.MODULENAME AS ModuleName,
                                                FF.SECTION AS FormulaFieldSection,
                                                FF.DATATYPE AS FormulaFieldDataType,
                                                FF.DATASIZE AS FormulaFieldDataSize,
                                                FF.ADDEDTYPE AS FormulaFieldAddedType,
                                                FF.ENTITYID AS EntityId,
                                                E.ENTITYCODE AS EntityCode,
                                                E.ENTITYNAME AS EntityName,
                                                FF.CREATEDBYID AS FormulaFieldCreatedById,
                                                CUSER.USERNAME AS FormulaFieldCreatedByName,
                                                FF.CREATEDON AS FormulaFieldCreatedOn,
                                                FF.MODIFIEDBYID AS FormulaFieldModifiedById,
                                                MUSER.USERNAME AS FormulaFieldModifiedByName,
                                                FF.MODIFIEDON AS FormulaFieldModifiedOn,
                                                FF.SORTORDER AS FormulaFieldSortOrder,
                                                FF.STATUS AS FormulaFieldStatus,
                                                FF.VERSION AS FormulaFieldVersion,
                                                FF.SOURCETYPE AS FormulaFieldSourceType
                                            FROM 
                                                MFORMULAFIELD FF
                                                LEFT JOIN MMODULE M ON FF.MODULEID = M.MODULEID
                                                LEFT JOIN MENTITY E ON FF.ENTITYID = E.ENTITYID
                                                LEFT JOIN MUSER CUSER ON FF.CREATEDBYID = CUSER.USERID
                                                LEFT JOIN MUSER MUSER ON FF.MODIFIEDBYID = MUSER.USERID
                                                WHERE
                                                FF.FORMULAFIELDID = @formulafieldid;
                                            ";

        public const string SAVE_FORMULAFIELD = @"INSERT INTO MFORMULAFIELD
(
    FORMULAFIELDID,
    FORMULAFIELDCODE,
    FORMULAFIELDNAME,
    MODULEID,
    SECTION,
    DATATYPE,
    DATASIZE,
    ADDEDTYPE,
    ENTITYID,
    SORTORDER,
    VERSION,
    STATUS,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @FormulaFieldId,
    @FormulaFieldCode,
    @FormulaFieldName,
    @ModuleId,
    @FormulaFieldSection,
    @FormulaFieldDataType,
    @FormulaFieldDataSize,
    @FormulaFieldAddedType,
    @EntityId,
    @FormulaFieldSortOrder,
    @FormulaFieldVersion,
    @FormulaFieldStatus,
    @FormulaFieldSourceType,
    @FormulaFieldCreatedById,
    @FormulaFieldCreatedOn,
    @FormulaFieldModifiedById,
    @FormulaFieldModifiedOn
);
";

        public const string UPDATE_FORMULAFIELD = @"UPDATE MFORMULAFIELD
SET
    FORMULAFIELDCODE      = @FormulaFieldCode,
    FORMULAFIELDNAME      = @FormulaFieldName,
    MODULEID              = @ModuleId,
    SECTION               = @FormulaFieldSection,
    DATATYPE              = @FormulaFieldDataType,
    DATASIZE              = @FormulaFieldDataSize,
    ADDEDTYPE             = @FormulaFieldAddedType,
    ENTITYID              = @EntityId,
    SORTORDER             = @FormulaFieldSortOrder,
    VERSION               = @FormulaFieldVersion,
    STATUS                = @FormulaFieldStatus,
    SOURCETYPE            = @FormulaFieldSourceType,
    MODIFIEDBYID          = @FormulaFieldModifiedById,
    MODIFIEDON            = @FormulaFieldModifiedOn
WHERE
    FORMULAFIELDID = @FormulaFieldId;
";

        public const string DELETE_FORMULAFIELD = @"DELETE FROM MFORMULAFIELD
                                            WHERE FORMULAFIELDID = @FormulaFieldId;";

        public const string GET_SELECTLIST_FORMULAFIELD = @"WITH FormulaField AS (
                                            SELECT
                                                A.FORMULAFIELDID     AS Id, 
                                                A.FORMULAFIELDCODE   AS Code,
                                                A.FORMULAFIELDNAME   AS Name,
                                                A.SECTION       AS Section,
                                                ROW_NUMBER() OVER (ORDER BY A.FORMULAFIELDID) AS RowNum
                                            FROM MFORMULAFIELD A
                                        )
                                        SELECT Id, Code, Name, Section
                                        FROM FormulaField
                                       WHERE 
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_FORMULAFIELD_SECTION = @"SELECT 
                                                                    ff.FORMULAFIELDID              AS FormulaFieldId,
                                                                    ff.FORMULAFIELDCODE           AS FormulaFieldCode,
                                                                    ff.FORMULAFIELDNAME           AS FormulaFieldName,
                                                                    ff.MODULEID                   AS ModuleId,
                                                                    m.MODULECODE                  AS ModuleCode,
                                                                    m.MODULENAME                  AS ModuleName,
                                                                    ff.SECTION                    AS FormulaFieldSection,
                                                                    ff.DATATYPE                   AS FormulaFieldDataType,
                                                                    ff.DATASIZE                   AS FormulaFieldDataSize,
                                                                    ff.ADDEDTYPE                  AS FormulaFieldAddedType,
                                                                    ff.ENTITYID                  AS EntityId,
                                                                    e.ENTITYCODE                 AS EntityCode,
                                                                    e.ENTITYNAME                 AS EntityName,
                                                                    ff.CREATEDBYID              AS FormulaFieldCreatedById,
                                                                    cb.USERNAME                 AS FormulaFieldCreatedByName,
                                                                    ff.CREATEDON                AS FormulaFieldCreatedOn,
                                                                    ff.MODIFIEDBYID             AS FormulaFieldModifiedById,
                                                                    mb.USERNAME                 AS FormulaFieldModifiedByName,
                                                                    ff.MODIFIEDON               AS FormulaFieldModifiedOn,
                                                                    ff.SORTORDER                AS FormulaFieldSortOrder,
                                                                    ff.STATUS                   AS FormulaFieldStatus,
                                                                    ff.VERSION                  AS FormulaFieldVersion,
                                                                    ff.SOURCETYPE              AS FormulaFieldSourceType,
                                                                    ff.TENANTID                AS TenantId
                                                                FROM 
                                                                    MFORMULAFIELD ff
                                                                LEFT JOIN MMODULE m ON ff.MODULEID = m.MODULEID
                                                                LEFT JOIN MENTITY e ON ff.ENTITYID = e.ENTITYID
                                                                LEFT JOIN MUSER cb ON ff.CREATEDBYID = cb.USERID
                                                                LEFT JOIN MUSER mb ON ff.MODIFIEDBYID = mb.USERID";
    }
}