locked
Querying for business closures between two dates javascript Dynamics CRM 2011 RRS feed

  • Question

  • Business closures are used to record the bank holidays and business shutdowns, each record represents a single day.

    I need to query this in Javascript to return the total number of days between two dates, startdate, enddate.

    Am struggling to find where this data is held and also how to query it.

    regards,

    Matt

    Monday, November 14, 2016 4:01 PM

All replies

  • Hi Matt,

    I have similar requirement too and so far i have managed achieve following. I won't say it is a 100% complete yet but i hope this will put you in right direction.

    First of all you need to query Organization entity to retrieve BusinessClosureCalendarId using ORG Name (JS reference to get ORG Name - Xrm.Page.contect.getOrgUniqueName()). Now query Calendar Rule entity where Calendar Id is equal to BusinessClosureCalendarId. This will give you list of all business closure dates. I do not have completed code yet but once it is completed, i will share it here.

    Kind regards,
    Narayan



    • Edited by NeeNarayan Wednesday, November 16, 2016 9:02 AM
    Wednesday, November 16, 2016 9:01 AM
  • I have:

    function getCalendarId()
    {
        // return number of records from business closures with dates equal to startDate and endDate
    
        var serverUrl = document.location.protocol + "//" + document.location.host + "/" + Xrm.Page.context.getOrgUniqueName();
        var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
        oDataEndpointUrl += "CalendarSet?$select=Name, CalendarId&$filter=Name eq 'Business Closure Calendar'";
    
        var service = GetRequestObject();
        if (service != null)
        {
            service.open("GET", oDataEndpointUrl, false);
            service.setRequestHeader("X-Requested-Width", "XMLHttpRequest");
            service.setRequestHeader("Accept", "application/json, text/javascript, */*");
            service.send(null);
            var requestResults = eval('(' + service.responseText + ')').d;
    
            if (requestResults != null && requestResults.results.length > 0)
            {
                for (var i = 0; i < requestResults.results.length; i++) // fetch the results
                {
                    alert("CalendarID : " + requestResults.results[i].CalendarId);
    
                    getCalendarClosures(requestResults.results[i].CalendarId);
                }
            }
            else
            {
                alert("no records between dates");
            }
        }
    }
    
    function getCalendarClosures(IDToSearchFor)
    {
        // return number of records from business closures with dates equal to startDate and endDate
    
        var serverUrl = document.location.protocol + "//" + document.location.host + "/" + Xrm.Page.context.getOrgUniqueName();
        var oDataEndpointUrl = serverUrl + "/XRMServices/2011/OrganizationData.svc/";
        oDataEndpointUrl += "CalendarRuleSet?$select=Name"; 
    
    //&$filter=CalendarId eq guid'"+IDToSearchFor+"'";
    
        var service = GetRequestObject();
        if (service != null)
        {
            service.open("GET", oDataEndpointUrl, false);
            service.setRequestHeader("X-Requested-Width", "XMLHttpRequest");
            service.setRequestHeader("Accept", "application/json, text/javascript, */*");
            service.send(null);
            var requestResults = eval('(' + service.responseText + ')').d;
    
            if (requestResults != null && requestResults.results.length > 0)
            {
                alert("Total Business Closures : " + requestResults.results.length);            
            }
            else
            {
                alert("no records found");
            }
        }
        //return 0;
    }
    
    

    My issue being that at the moment it should return all calendar rule records but is returning nothing - it doesn't error just returns nothing.

    Matt

    Wednesday, November 16, 2016 11:47 AM
  • Hi Matt,

    If you try your query in browser it will return 404. This is because there are no messages supported for ContactRule entity.

    Reference: https://msdn.microsoft.com/en-us/library/gg327912.aspx

    I got another workaround and it is now implemented using custom web service. I have following steps implemented in this service.
    1. Retrieve "businessclosurecalendarid" using organisation name
    2. Retrieve Calendar entity where calendar id is equal to "businessclosurecalendarid".

    var query = new QueryExpression(Calendar.EntityLogicalName);
    query.ColumnSet = new ColumnSet(true);
    query.Criteria = new FilterExpression();
    query.Criteria.AddCondition(new ConditionExpression("calendarid", ConditionOperator.Equal,
    businessclosurecalendarid));

    3. Calendar Entity Record will also return collection of Calendar Rules.

    var calendarRules = (Calendar)_service.RetrieveMultiple(query).Entities[0].CalendarRules;

    4. Then I have compared requested dates with fetched dates and set BusinessClosure flag to true if they both matches.

    foreach (CalendarRule rule in calendarRules) {
         if (DateTime.Compare(dateToCheck, DateTime.Parse(rule.StartTime.Value.ToString()) == 0) { isBusinessClosed = true; break; }
    }

    May not be a best solution for you but I hope this helps.

    Narayan




    • Edited by NeeNarayan Wednesday, November 16, 2016 3:03 PM
    Wednesday, November 16, 2016 2:53 PM
  • This is beyond me...

    So how or where do I put this?

    Matt

    Wednesday, November 16, 2016 3:55 PM
  • Hi Matt,

    You need to create a new custom WCF (Web) service and this code goes in there. You will need to host this service using IIS (You can use same IIS used by MS CRM Server. I have done the same and used same URL but different port. example: http://crmserver:8080/GetNextBusinessWorkingDay.svc). 

    If you are not familiar with WCF service which return data in JSON then following post should help you to get started.
    http://www.codeproject.com/Articles/167159/How-to-create-a-JSON-WCF-RESTful-Service-in-sec

    Kind regards,
    Narayan

    Thursday, November 17, 2016 11:35 AM