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

namespace FrameworkDAL.Query.DrillDown
{
    public static class DrillDownQB
    {
        public const string GET_DRILLDOWN = @"SELECT
                            DD.DRILLDOWNID AS DrillDownId,
                            DD.DRILLDOWNCODE AS DrillDownCode,
                            DD.DRILLDOWNDESCRIPTION AS DrillDownDescription,
                            DD.DRILLTYPE AS DrillDownType,
                            DD.FROMTYPE AS DrillDownFromType,
                            DD.FROMMENUID AS FromMenuId,
                            FM.MENUCODE AS FromMenuCode,
                            FM.MENUNAME AS FromMenuName,
                            FMR.WEBSERVICEID AS FromMenuReportWebServiceId,
                            DD.FROMPORTLETID AS FromPortletId,
                            FP.PORTLETCODE AS FromPortletCode,
                            FP.PORTLETNAME AS FromPortletName,
                            DD.TOTYPE AS DrillDownToType,
                            DD.TOMENUID AS ToMenuId,
                            TM.MENUCODE AS ToMenuCode,
                            TM.MENUNAME AS ToMenuName,
                            TMR.WEBSERVICEID AS ToMenuReportWebServiceId,
                            DD.TOPORTLETID AS ToPortletId,
                            TP.PORTLETCODE AS ToPortletCode,
                            TP.PORTLETNAME AS ToPortletName,
                            DD.SORTORDER AS DrillDownSortOrder,
                            DD.VERSION AS DrillDownVersion,
                            DD.STATUS AS DrillDownStatus,
                            DD.CREATEDBYID AS DrillDownCreatedById,
                            DD.CREATEDON AS DrillDownCreatedOn,
                            DD.MODIFIEDBYID AS DrillDownModifiedById,
                            DD.MODIFIEDON AS DrillDownModifiedOn,
                            DD.FROMREPORTID AS FromReportId,
                            FR.REPORTCODE AS FromReportCode,
                            FR.REPORTNAME AS FromReportName,
                            DD.TOREPORTID AS ToReportId,
                            TR.REPORTCODE AS ToReportCode,
                            TR.REPORTNAME AS ToReportName,
                            DD.TOWEBFORMID AS ToWebFormId,
                            WF.WEBFORMNAME AS ToWebFormName,
                            WF.WEBFORMURL AS ToWebFormURL,
                            DD.BIZTRANSACTIONTYPEFIELD AS DrillDownBIZTransactionTypeField,
                            DD.MODULEID AS ModuleId,
                            MO.MODULECODE AS ModuleCode,
                            MO.MODULENAME AS ModuleName,

                            DDD.DRILLDOWNDETAILLINEID AS DrillDownDetailId,
                            DDD.DRILLDOWNID AS DrillDownId,
                            DD.DRILLDOWNCODE AS DrillDownCode,
                            DD.DRILLDOWNDESCRIPTION AS DrillDownDescription,
                            DDD.MAPTYPE AS DrillDownDetailMapType,
                            DDD.SLNO AS DrillDownDetailSlNo,
                            DDD.ATTRIBUTEID AS AttributeId,
                            CA.CRITERIAATTRIBUTENAME AS AttributeName,
                            DDD.ASSIGNTYPE AS DrillDownDetailAssignType,
                            DDD.SOURCEFIELD AS DrillDownDetailSourceField,
                            DDD.FILTERVALUE AS DrillDownDetailFilterValue,
                            DDD.OPERATIONTYPE AS DrillDownDetailOperationType,
                            DDD.JOINTYPE AS DrillDownDetailJoinType
                            FROM MDRILLDOWN DD
                            LEFT JOIN MDRILLDOWNDETAIL DDD ON DD.DRILLDOWNID = DDD.DRILLDOWNID
                            LEFT JOIN MMENU FM ON DD.FROMMENUID = FM.MENUID
                            LEFT JOIN MREPORT FMR ON FM.REPORTID = FMR.REPORTID
                            LEFT JOIN MPORTLET FP ON DD.FROMPORTLETID = FP.PORTLETID
                            LEFT JOIN MMENU TM ON DD.TOMENUID = TM.MENUID
                            LEFT JOIN MREPORT TMR ON TM.REPORTID = TMR.REPORTID
                            LEFT JOIN MPORTLET TP ON DD.TOPORTLETID = TP.PORTLETID
                            LEFT JOIN MREPORT FR ON DD.FROMREPORTID = FR.REPORTID
                            LEFT JOIN MREPORT TR ON DD.TOREPORTID = TR.REPORTID
                            LEFT JOIN MWEBFORM WF ON DD.TOWEBFORMID = WF.WEBFORMID
                            LEFT JOIN MMODULE MO ON DD.MODULEID = MO.MODULEID
                            LEFT JOIN MCRITERIAATTRIBUTE CA ON DDD.ATTRIBUTEID = CA.CRITERIAATTRIBUTEID
                            WHERE DD.DRILLDOWNID = @drilldownid;
                            ";

        public const string SAVE_DRILLDOWN = @"INSERT 
                            INTO MDRILLDOWN (
                                 DRILLDOWNID,
                                 DRILLDOWNCODE,
                                 DRILLDOWNDESCRIPTION,
                                 DRILLTYPE,
                                 FROMTYPE,
                                 FROMMENUID,
                                 FROMPORTLETID,
                                 TOTYPE,
                                 TOMENUID,
                                 TOPORTLETID,
                                 SORTORDER,
                                 VERSION,
                                 STATUS,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 FROMREPORTID,
                                 TOREPORTID,
                                 TOWEBFORMID,
                                 BIZTRANSACTIONTYPEFIELD,
                                 MODULEID
                            ) OUTPUT INSERTED.DrillDownId VALUES (
                                 @DrillDownId,
                                 @DrillDownCode,
                                 @DrillDownDescription,
                                 @DrillDownType,
                                 @DrillDownFromType,
                                 @FromMenuId,
                                 @FromPortletId,
                                 @DrillDownToType,
                                 @ToMenuId,
                                 @ToPortletId,
                                 @DrillDownSortOrder,
                                 @DrillDownVersion,
                                 @DrillDownStatus,
                                 @DrillDownCreatedById,
                                 @DrillDownCreatedOn,
                                 @DrillDownModifiedById,
                                 @DrillDownModifiedOn,
                                 @FromReportId,
                                 @ToReportId,
                                 @ToWebFormId,
                                 @DrillDownBIZTransactionTypeField,
                                 @ModuleId
                            );
                            ";

        public const string SAVE_DRILLDOWNDETAIL = @"INSERT 
                            INTO MDRILLDOWNDETAIL (
                                 DRILLDOWNDETAILLINEID,
                                 DRILLDOWNID,
                                 MAPTYPE,
                                 SLNO,
                                 ATTRIBUTEID,
                                 ASSIGNTYPE,
                                 SOURCEFIELD,
                                 FILTERVALUE,
                                 OPERATIONTYPE,
                                 JOINTYPE
                            ) OUTPUT INSERTED.DrillDownDetailLineId VALUES (
                                 @DrillDownDetailId,
                                 @DrillDownId,
                                 @DrillDownDetailMapType,
                                 @DrillDownDetailSlNo,
                                 @AttributeId,
                                 @DrillDownDetailAssignType,
                                 @DrillDownDetailSourceField,
                                 @DrillDownDetailFilterValue,
                                 @DrillDownDetailOperationType,
                                 @DrillDownDetailJoinType
                            );
                            ";

        public const string UPDATE_DRILLDOWN = @"UPDATE 
                            MDRILLDOWN SET
                            DRILLDOWNCODE = @DrillDownCode,
                            DRILLDOWNDESCRIPTION = @DrillDownDescription,
                            DRILLTYPE = @DrillDownType,
                            FROMTYPE = @DrillDownFromType,
                            FROMMENUID = @FromMenuId,
                            FROMPORTLETID = @FromPortletId,
                            TOTYPE = @DrillDownToType,
                            TOMENUID = @ToMenuId,
                            TOPORTLETID = @ToPortletId,
                            SORTORDER = @DrillDownSortOrder,
                            VERSION = @DrillDownVersion,
                            STATUS = @DrillDownStatus,
                            MODIFIEDBYID = @DrillDownModifiedById,
                            MODIFIEDON = @DrillDownModifiedOn,
                            FROMREPORTID = @FromReportId,
                            TOREPORTID = @ToReportId,
                            TOWEBFORMID = @ToWebFormId,
                            BIZTRANSACTIONTYPEFIELD = @DrillDownBIZTransactionTypeField,
                            MODULEID = @ModuleId
                            WHERE DrillDownId = @DrillDownId;
                            ";

        public const string UPDATE_DRILLDOWNDETAIL = @"UPDATE 
                            MDRILLDOWNDETAIL SET
                            DRILLDOWNID = @DrillDownId,
                            MAPTYPE = @DrillDownDetailMapType,
                            SLNO = @DrillDownDetailSlNo,
                            ATTRIBUTEID = @AttributeId,
                            ASSIGNTYPE = @DrillDownDetailAssignType,
                            SOURCEFIELD = @DrillDownDetailSourceField,
                            FILTERVALUE = @DrillDownDetailFilterValue,
                            OPERATIONTYPE = @DrillDownDetailOperationType,
                            JOINTYPE = @DrillDownDetailJoinType
                            WHERE DrillDownDetailLineId = @DrillDownDetailId AND DrillDownId = @DrillDownId;
                            ";

        public const string DELETE_DRILLDOWN = @"DELETE 
                            FROM MDRILLDOWN
                            WHERE DRILLDOWNID = @drilldownid;
                            ";

        public const string DELETE_DRILLDOWNDETAIL = @"DELETE 
                            FROM MDRILLDOWNDETAIL
                            WHERE DRILLDOWNID = @drilldownid;
                            ";

        public const string GET_MAX_DRILLDOWNDETAILID = @"SELECT
                            ISNULL(MIN(DRILLDOWNDETAILLINEID), 0)
                            FROM MDRILLDOWNDETAIL
                            WHERE DRILLDOWNID = @drilldownid;";

        public const string GET_SELECTLIST_DRILLDOWN = @"
                            WITH PagedDrillDown AS (
                            SELECT 
                            DD.DRILLDOWNID AS Id,
                            DD.DRILLDOWNCODE AS Code,
                            DD.DRILLDOWNDESCRIPTION AS Description,
                            ROW_NUMBER() OVER (ORDER BY DD.DRILLDOWNID) AS RowNum
                            FROM MDRILLDOWN DD
                            )
                            SELECT
                            Id,
                            Code,
                            Description
                            FROM PagedDrillDown
                            WHERE 
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}