using DocumentFormat.OpenXml.ExtendedProperties;
using iText.StyledXmlParser.Jsoup.Select;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GB5Shared.Query.ExcelQuery
{
    public static class ExcelQueryQB
    {
        public const string GET_REPORTVIEW_HEADER = @"SELECT
                            a.REPORTHEADERID AS ReportHeaderId,
                            c.ISHEADERREQUIRED AS IsHeaderRequired,
                            b.SLNO AS SlNo,
                            b.FROMROWNUMBER AS FromRowNumber,
                            b.TOROWNUMBER AS ToRowNumber,
                            b.ROWHEIGHT AS RowHeight,
                            b.FROMCOLUMNNUMBER AS FromColumnNumber,
                            b.TOCOLUMNNUMBER AS ToColumnNumber,
                            b.CELLWIDTH AS CellWidth,
                            b.EXPRESSION AS Expression,
                            b.ALIGNMENT AS Alignment,
                            b.FONT AS Font,
                            b.FONTSIZE AS FontSize,
                            b.COLOR AS Color,
                            b.STYLE AS Style,
                            b.ISEMPTYSUPPRESS AS IsEmptySuppress
                        FROM Mreportheader a
                        INNER JOIN Mreportheaderdetail b ON a.REPORTHEADERID = b.REPORTHEADERID
                        INNER JOIN Mreportview c ON a.REPORTHEADERID = c.REPORTHEADERID
                        WHERE c.Reportviewid = @reportviewid
                          AND a.REPORTHEADERID <> -1
                        ORDER BY b.SLNO;
                        ";

        public const string GET_HEADER_TITLE = @"
                        SELECT 
                            ou.Name AS OUDisplayName,
                            branch.Name AS BranchName,
                            branch.Address AS AddressDetail
                        FROM OrganizationUnit ou
                        INNER JOIN Branch branch ON branch.OUId = ou.Id
                        WHERE ou.Id = @OUId AND branch.Id = @BranchId
                    ;";

        public const string GET_REPORTVIEW_VISIBLE_FIELDS_BASED_ON_REPORTVIEWID = @"SELECT
                            MR.REPORTVIEWFIELDSID AS ReportViewFieldsId,
                            MR.REPORTID AS ReportId,
                            MR.REPORTVIEWID AS ReportViewId,
                            MR.REPORTVSFIELDSID AS ReportVsFieldsId,
                            MR.SLNO AS ReportViewFieldsSlNo,
                            MR.FIELDTITLE AS ReportViewFieldsFieldTitle,
                            MR.FIELDWIDTH AS ReportViewFieldsFieldWidth,
                            MR.ISDISPLAY AS ReportViewFieldsIsDisplay,
                            MR.ISMERGECOLUMN AS ReportViewFieldsIsMergeColumn,
                            MR.ISGROUPCOLUMN AS ReportViewFieldsIsGroupcolumn,
                            MR.ISSUBTOTAL AS ReportViewFieldsIsSubTotal,
                            MR.ISGRANDTOTAL AS ReportViewFieldsIsGrandTotal,
                            MR.CALFORMAT AS ReportViewFieldsCalFormat,
                            MR.DISPLAYSLNO AS ReportViewFieldsDisplaySlNo,
                            MR.ALIGNMENT AS ReportViewFieldsAlignment,
                            MR.DISPLAYTYPE AS ReportViewFieldsDisplayType,
                            MR.AGGREGATIONTYPE AS ReportViewFieldsAggregationType,
                            MR.ISCOLUMNTOTAL AS ReportViewFieldsIsColumnTotal,
                            MR.ISROWTOTAL AS ReportViewFieldsIsRowTotal,
                            MR.PIVOTLEVEL AS ReportViewFieldsPivotLevel,
                            MR.SUPPRESSIFEMPTY AS ReportViewFieldsSuppressIfEmpty,
                            MR.DIMENSIONFIELDID AS DimensionFieldId
                        FROM MReportViewFields MR
                        WHERE MR.REPORTVIEWID = -1499999369
                          AND MR.ISDISPLAY = 0
                        ORDER BY MR.DISPLAYSLNO;
                        ";
       
        public const string GET_ADDRESS_DETAIL_EXCEL_HEADER_PURPOSE =
                      @"Select 
                                address  
                       from    
                              Address address 
                       where    
                              address.ObjectTypeId=:objecttypeid  
                              and address.ObjectId =:objectid" + "\r\n";


        public const string GET_OU_AND_BRANCH_DETAIL = @"SELECT
                        a.DISPLAYNAME AS OUDisplayName,
                        c.BRANCHNAME AS BranchName
                    FROM MORGANIZATIONUNIT a
                    INNER JOIN MCOMPANY b ON a.COMPANYID = b.COMPANYID
                    INNER JOIN MBRANCH c ON a.BRANCHID = c.BRANCHID
                    WHERE a.OUID = @ouid;";
        
    }
}