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

namespace FrameworkDAL.Query.Module
{
    public class ModuleQB
    {
        public const string GET_MODULE_LIST_OLD = @"SELECT 
                            rolevsmodule.ModuleId,
                            module.ModuleCode,
                            module.ModuleName,
                            tr_module.MODULENAME AS ModuleTranslationName,
                            module.DEFAULTMENUID AS DefaultMenuId,
                            menu.MenuCode AS DefaultMenuCode,
                            menu.MenuName AS DefaultMenuName,
                            rolevsmodule.DEFAULTMENUID AS RoleModuleDefaultMenuId,
                            rolemodulemenu.MenuCode AS RoleModuleDefaultMenuCode,
                            rolemodulemenu.MenuName AS RoleModuleDefaultMenuName,
                            rolemodulemenu.MenuNature AS RoleModuleDefaultMenuMenuNature,
                            rolemodulemenu.MenuType AS RoleModuleDefaultMenuMenuType,
                            rolemodulemenu.Section AS RoleModuleDefaultMenuSection,
                            rolemodulemenu.ReportId AS RoleModuleDefaultMenuReportId,
                            rolemodulemenu.EntityId AS RoleModuleDefaultMenuEntityId,
                            rolemodulemenu.BizTransactionClassId AS RoleModuleDefaultMenuBizTransactionClassId,
                            defclass.BIZTRANSACTIONCLASSNAME AS RoleModuleDefaultMenuBizTransactionClassName,
                            rolemodulemenu.GCMTYPEID AS RoleModuleDefaultMenuGCMTypeId,
                            rolemodulemenu.WebFormId AS RoleModuleDefaultMenuWebFormId,
                            defwebfrm.WEBFORMNAME AS RoleModuleDefaultMenuWebFormName,
                            defwebfrm.WEBFORMURL AS RoleModuleDefaultMenuWebFormUrl,
                            rolemodulemenu.HelpUrl AS RoleModuleDefaultMenuHelpUrl,
                            rolemodulemenu.ImageId AS RoleModuleDefaultMenuImageId,
                            muser.DEFAULTMENUID AS UserDefaultMenuId,
                            usermenu.MenuCode AS UserDefaultMenuCode,
                            usermenu.MenuName AS UserDefaultMenuName,
                            usermenu.MenuNature AS UserDefaultMenuMenuNature,
                            usermenu.MenuType AS UserDefaultMenuMenuType,
                            usermenu.Section AS UserDefaultMenuSection,
                            usermenu.ReportId AS UserDefaultMenuReportId,
                            usermenu.EntityId AS UserDefaultMenuEntityId,
                            usermenu.BizTransactionClassId AS UserDefaultMenuBizTransactionClassId,
                            userdefclass.BIZTRANSACTIONCLASSNAME AS UserDefaultMenuBizTransactionClassName,
                            usermenu.GCMTYPEID AS UserDefaultMenuGCMTypeId,
                            usermenu.WebFormId AS UserDefaultMenuWebFormId,
                            userdefwebfrm.WEBFORMNAME AS UserDefaultMenuWebFormName,
                            userdefwebfrm.WEBFORMURL AS UserDefaultMenuWebFormUrl,
                            usermenu.HelpUrl AS UserDefaultMenuHelpUrl,
                            usermenu.ImageId AS UserDefaultMenuImageId
                        FROM 
                            mRoleVsModules rolevsmodule
                            JOIN mModule module ON rolevsmodule.ModuleId = module.moduleId
                            JOIN TR_MMODULE tr_module 
                               ON tr_module.MODULEID = module.ModuleId AND tr_module.LANGUAGEID = @languageid
                            JOIN mMenu menu ON module.DEFAULTMENUID = menu.menuId
                            JOIN mMenu rolemodulemenu ON rolevsmodule.DEFAULTMENUID = rolemodulemenu.menuId
                            JOIN MBIZTRANSACTIONCLASS defclass ON rolemodulemenu.BIZTRANSACTIONCLASSID = defclass.BIZTRANSACTIONCLASSID
                            JOIN MWEBFORM defwebfrm ON rolemodulemenu.WEBFORMID = defwebfrm.WEBFORMID
                            JOIN MUSER muser ON muser.USERID = @userid
                            JOIN mMenu usermenu ON muser.DEFAULTMENUID = usermenu.MENUID
                            JOIN MBIZTRANSACTIONCLASS userdefclass ON usermenu.BIZTRANSACTIONCLASSID = userdefclass.BIZTRANSACTIONCLASSID
                            JOIN MWEBFORM userdefwebfrm ON usermenu.WEBFORMID = userdefwebfrm.WEBFORMID
                        WHERE 
                            rolevsmodule.RoleId = @roleid
                            AND module.moduleId NOT IN (-1399999915)
                        ORDER BY 
                            rolevsmodule.SLNO;
                        ";


        public const string GET_MODULE_LIST = @"select
                                        a.moduleid AS ModuleId,
                                        a.modulecode AS ModuleCode,
                                        COALESCE(NULLIF(d.MODULENAME,''), NULLIF(b.MODULENAME,''), a.MODULENAME) AS ModuleName,
                                        COALESCE(NULLIF(d.REMARKS,''), NULLIF(b.REMARKS,''), a.REMARKS) AS Remarks,
                                        a.DEFAULTMENUID AS DefaultMenuId,
                                        menu.MenuCode AS DefaultMenuCode,
                                        menu.MenuName AS DefaultMenuName,
                                        c.DEFAULTMENUID AS RoleModuleDefaultMenuId
                                        FROM
                                        mmodule a
                                        LEFT OUTER JOIN mMenu menu ON a.DEFAULTMENUID = menu.menuId
                                        LEFT OUTER JOIN tr_mmodule b ON (a.MODULEID = b.MODULEID AND b.LANGUAGEID = @languageid AND b.PARTNERPRODUCTID IS NULL)
                                        LEFT OUTER JOIN tr_mmodule d ON (a.MODULEID = d.MODULEID AND d.LANGUAGEID = @languageid AND d.PARTNERPRODUCTID = @partnerproductid)
                                        ,mRoleVsModules c
                                        WHERE
                                        a.moduleid = c.moduleid
                                        AND c.roleid = @roleid ";


        public const string GET_SELECTLIST_MODULE = @"SELECT
                                            MODULEID AS Id,
                                            MODULECODE AS Code,
                                            MODULENAME AS Name 
                                            FROM MMODULE";


        public const string GET_MODULE = @"SELECT 
                                            M.MODULEID                AS ModuleId,
                                            M.MODULECODE              AS ModuleCode,
                                            M.MODULENAME              AS ModuleName,
                                            M.REMARKS                 AS ModuleRemarks,

                                            M.MODULEIMAGEID           AS ImageId,
                                            I.IMAGENAME               AS ImageName,

                                            M.PROJECTID               AS ProjectId,
                                            P.PROJECTCODE             AS ProjectCode,
                                            P.PROJECTNAME             AS ProjectName,

                                            M.DEFAULTMENUID           AS MenuId,
                                            MN.MENUCODE               AS MenuCode,
                                            MN.MENUNAME               AS MenuName,

                                            M.CREATEDBYID             AS ModuleCreatedById,
                                            M.CREATEDON               AS ModuleCreatedOn,
                                            M.MODIFIEDBYID            AS ModuleModifiedById,
                                            M.MODIFIEDON              AS ModuleModifiedOn,

                                            C.USERNAME                AS ModuleCreatedByName,
                                            U.USERNAME                AS ModuleModifiedByName,

                                            M.SORTORDER               AS ModuleSortOrder,
                                            M.STATUS                  AS ModuleStatus,
                                            M.VERSION                 AS ModuleVersion,
                                            M.SOURCETYPE              AS ModuleSourceType

                                        FROM MMODULE M
                                        LEFT JOIN MIMAGE I ON M.MODULEIMAGEID = I.IMAGEID
                                        LEFT JOIN MPROJECT P ON M.PROJECTID = P.PROJECTID
                                        LEFT JOIN MMENU MN ON M.DEFAULTMENUID = MN.MENUID
                                        LEFT JOIN MUSER C ON M.CREATEDBYID = C.USERID
                                        LEFT JOIN MUSER U ON M.MODIFIEDBYID = U.USERID
                                        WHERE M.MODULEID = @ModuleId;
                                        ";

        public const string SAVE_MODULE = @"INSERT INTO MMODULE 
                                                (
                                                    MODULEID,
                                                    MODULECODE,
                                                    MODULENAME,
                                                    MODULEIMAGEID,
                                                    PROJECTID,
                                                    REMARKS,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    DEFAULTMENUID
                                                )
                                                VALUES 
                                                (
                                                    @ModuleId,     
                                                    @ModuleCode,        
                                                    @ModuleName,        
                                                    @ImageId,           
                                                    @ProjectId,         
                                                    @ModuleRemarks,     
                                                    @ModuleSortOrder,   
                                                    @ModuleStatus,      
                                                    @ModuleVersion,     
                                                    @ModuleSourceType,  
                                                    @ModuleCreatedById, 
                                                    @ModuleCreatedOn,   
                                                    @ModuleModifiedById,
                                                    @ModuleModifiedOn,  
                                                    @MenuId
                                                );";

        public const string UPDATE_MODULE = @"UPDATE MMODULE
                                            SET
                                                MODULECODE        = @ModuleCode,
                                                MODULENAME        = @ModuleName,
                                                MODULEIMAGEID     = @ImageId,
                                                PROJECTID         = @ProjectId,
                                                REMARKS           = @ModuleRemarks,
                                                SORTORDER         = @ModuleSortOrder,
                                                STATUS            = @ModuleStatus,
                                                VERSION           = @ModuleVersion,
                                                SOURCETYPE        = @ModuleSourceType,
                                                MODIFIEDBYID      = @ModuleModifiedById,
                                                MODIFIEDON        = @ModuleModifiedOn,
                                                DEFAULTMENUID     = @MenuId
                                            WHERE 
                                                MODULEID = @ModuleId;";

        public const string DELETE_MODULE = @"DELETE FROM MMODULE
                                            WHERE MODULEID = @ModuleId;";
    }
}