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

namespace AdminDAL.Query.DataAlertSummary
{
    public static class DataAlertSummaryQB
    {
        public const string GET_DATAALERTSUMMARYLIST = @"SELECT
                            F.MODULENAME AS ModuleName, 
			                B.SECTION AS Section, 
			                A.SLNO AS Slno,
			                B.DATAALERTNAME AS DataAlertName,
			                ISNULL(C.NUMBEROFRECORDS,0) AS DataAlertSummaryNumberOfRecords, 
			                ISNULL(C.POSTEDVALUE,0)  AS PostedValue,
			                ISNULL(C.POSTEDON, DATEADD(MINUTE,9,'01-Jan-1899')) AS DataAlertSummaryPostedOn,
			                D.ORGANIZATIONUNITCODE AS OUCode, 
			                E.USERCODE AS UserCode,
                            B.DATAALERTID AS DataAlertId, 
			                ISNULL(C.DATAALERTSUMMARYID,-1) AS DataAlertSummaryId, 
			                ISNULL(C.USERID,-1) AS UserId, 
			                ISNULL(C.OUID,-1) AS OUId,
			                B.FIELDSTODISPLAY AS FieldsToDisplay,
			                B.DESCRIPTION AS Description,
                            CASE B.ALERTLEVEL 
                                         WHEN 0 THEN 'I' 
                                         WHEN 1 THEN 'W' 
                                         ELSE 'C' 
                            END AS AlertLevel,
                            B.NOOFFILTERS AS NoOfFilter
                            FROM MUSERALERT A
                            LEFT OUTER JOIN MDATAALERT B ON (A.DATAALERTID = B.DATAALERTID)
                            LEFT OUTER JOIN TDATAALERTSUMMARY C ON (A.DATAALERTID = C.DATAALERTID),
                            MORGANIZATIONUNIT D, MUSER E, MMODULE F
                            WHERE A.DATAALERTID = B.DATAALERTID           
                            AND ((A.USERID = C.USERID) OR (C.USERID = -1 AND B.USERWISE = 1) OR ISNULL(C.USERID,-1) = -1)
                            AND ((C.OUID = @ouid) OR (C.OUID = -1 AND B.OUWISE = 1) OR ISNULL(C.OUID,-1) = -1)
                            AND ISNULL(C.OUID,-1) = D.OUID
                            AND ISNULL(C.USERID,-1) = E.USERID
                            AND B.MODULEID = F.MODULEID           
                            AND A.USEROPTION NOT IN (2) 
			                ORDER BY F.MODULENAME, A.SLNO
			                OFFSET 
                            CASE WHEN @firstnumber = -1 THEN 0 ELSE @firstnumber END ROWS
                            FETCH NEXT 
                            CASE WHEN @maxresult = -1 THEN (SELECT COUNT(1) FROM MUSERALERT) ELSE @maxresult END ROWS ONLY;"
                            ;

        public const string GET_DATAALERTSUMMARYLIST_COUNT = @"SELECT COUNT(*)
                            FROM MUSERALERT A
                            LEFT OUTER JOIN MDATAALERT B ON (A.DATAALERTID = B.DATAALERTID)
                            LEFT OUTER JOIN TDATAALERTSUMMARY C ON (A.DATAALERTID = C.DATAALERTID), 
                            MORGANIZATIONUNIT D, MUSER E, MMODULE F
                            WHERE A.DATAALERTID = B.DATAALERTID           
                            AND ((A.USERID = C.USERID) OR (C.USERID = -1 AND B.USERWISE = 1) OR ISNULL(C.USERID,-1) = -1)
                            AND ((C.OUID = @ouid) OR (C.OUID = -1 AND B.OUWISE = 1) OR ISNULL(C.OUID,-1) = -1)
                            AND ISNULL(C.OUID,-1) = D.OUID
                            AND ISNULL(C.USERID,-1) = E.USERID
                            AND B.MODULEID = F.MODULEID            
                            AND A.USEROPTION NOT IN (2);"
                            ;

        public const string GET_DATAALERT_ID = @"SELECT 
                            EXPRESSION
                            FROM MDATAALERT
                            WHERE DATAALERTID = @dataalertid;"
                            ;
    }
}