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

namespace FrameworkDAL.Query.File
{
    public static class FileQB
    {
        public const string GET_FILE = @"SELECT
                                            F.FILEID        AS FileId,
                                            F.FILENAME      AS FileName,
                                            F.FILELOCATION  AS FileLocation,
                                            F.REMARKS       AS FileRemarks,
                                            F.FILETYPE      AS FileType,
                                            F.REPORTID      AS ReportId,
                                            R.REPORTNAME    AS ReportName,
                                            F.WEBSERVICEID  AS WebServiceId,
                                            WS.WEBSERVICENAME AS WebServiceName,    
                                            F.TENANTID      AS TenantId,
                                            T.USERCODE      AS TenantCode,
                                            T.USERNAME      AS TenantName,
                                            F.ENTITYID      AS EntityId,
                                            E.ENTITYCODE    AS EntityCode,
                                            E.ENTITYNAME    AS EntityName,
                                            F.CREATEDBYID   AS FileCreatedById,
                                            F.CREATEDON     AS FileCreatedOn,
                                            CU.USERNAME     AS FileCreatedByName,
                                            F.MODIFIEDBYID  AS FileModifiedById,
                                            F.MODIFIEDON    AS FileModifiedOn,
                                            MU.USERNAME     AS FileModifiedByName,
                                            F.SORTORDER     AS FileSortOrder,
                                            F.STATUS        AS FileStatus,
                                            F.VERSION       AS FileVersion,
                                            F.SOURCETYPE    AS FileSourceType
                                        FROM MFILE F
                                        LEFT JOIN MREPORT R ON R.REPORTID = F.REPORTID
                                        LEFT JOIN MWEBSERVICE WS ON WS.WEBSERVICEID = F.WEBSERVICEID
                                        LEFT JOIN MUSER T ON T.USERID = F.TENANTID
                                        LEFT JOIN MENTITY E ON E.ENTITYID = F.ENTITYID
                                        LEFT JOIN MUSER CU ON CU.USERID = F.CREATEDBYID
                                        LEFT JOIN MUSER MU ON MU.USERID = F.MODIFIEDBYID
                                        WHERE F.FILEID = @fileid;";


        public const string SAVE_FILE = @"INSERT INTO MFILE (
                                            FILEID,
                                            FILENAME,
                                            FILELOCATION,
                                            REMARKS,
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            SOURCETYPE,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON,
                                            FILETYPE,
                                            REPORTID,
                                            WEBSERVICEID,
                                            TENANTID,
                                            ENTITYID
                                        )
                                        VALUES (
                                            @FileId,    
                                            @FileName,          
                                            @FileLocation,  
                                            @FileRemarks,      
                                            @FileSortOrder,     
                                            @FileStatus,           
                                            @FileVersion,      
                                            @FileSourceType,       
                                            @FileCreatedById,       
                                            @FileCreatedOn,        
                                            @FileModifiedById,     
                                            @FileModifiedOn,    
                                            @FileType,              
                                            @ReportId,           
                                            @WebServiceId,       
                                            @TenantId,           
                                            @EntityId          
                                        );";

        public const string UPDATE_FILE = @"UPDATE MFILE
                                            SET
                                                FILENAME = @FileName,          
                                                FILELOCATION = @FileLocation,    
                                                REMARKS = @FileRemarks,           
                                                SORTORDER = @FileSortOrder,        
                                                STATUS = @FileStatus,              
                                                VERSION = @FileVersion,      
                                                SOURCETYPE = @FileSourceType,    
                                                MODIFIEDBYID = @FileModifiedById,  
                                                MODIFIEDON = @FileModifiedOn,     
                                                FILETYPE = @FileType,               
                                                REPORTID = @ReportId,              
                                                WEBSERVICEID = @WebServiceId,   
                                                TENANTID = @TenantId,              
                                                ENTITYID = @EntityId 
                                            WHERE 
                                                FILEID = @FileId;";

        public const string GET_SELECTLIST_FILE = @"SELECT
                                                    MF.FILEID AS Id,
                                                    MF.FILENAME AS Name
                                                    FROM MFILE MF
                                                    WHERE MF.STATUS = 1 ORDER BY MF.FILENAME;";
        

        public const string DELETE_FILE = @"DELETE FROM MFILE
                                            WHERE FILEID = @FileId;";
    }
}