// JavaScript code for Blood Stock Indicator: BEGIN 
    google.load('visualization', '1', {packages:['gauge']});

    google.setOnLoadCallback(drawVisualization);

    var bloodStockVisualization1;   // Blood-type A
    var bloodStockVisualization2;   // Blood-type B
    var bloodStockVisualization3;   // Blood-type AB
    var bloodStockVisualization4;   // Blood-type O

    var bloodStockDate;             // Date of most recent blood stock update

    function drawVisualization() {
      // Specify data source URL
      var query = new google.visualization.Query("http://spreadsheets.google.com/pub?key=p4del_Qc7xjduJAcAHpVmaA&single=true&gid=9&output=html&pub=1");

      // Request to return only column A-E where all entries contain data.
      query.setQuery("select A, B, C, D, E where A IS NOT NULL and B IS NOT NULL and C IS NOT NULL and D IS NOT NULL and E IS NOT NULL order by A desc limit 1");

      // Send the query with a callback function.
      query.send(handleQueryResponse);
    }

    function handleQueryResponse(response) {
      // Catch errors first.
      if (response.isError()) {
        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        return;
      }
    
      var excelData = response.getDataTable();
      var visualisationData1 = new google.visualization.DataTable();
      var visualisationData2 = new google.visualization.DataTable();
      var visualisationData3 = new google.visualization.DataTable();
      var visualisationData4 = new google.visualization.DataTable();

      // Set date of most recent blood stock update
      bloodStockDate = excelData.getValue(0, 0);

      // Blood group A data
      visualisationData1.addColumn("string", "Blood Group");
      visualisationData1.addColumn("number", "Units in Stock");
      visualisationData1.addRows(1);

      var groupA_value = excelData.getValue(0, 1);
      visualisationData1.setValue( 0, 0, "Group A");
      visualisationData1.setValue( 0, 1, groupA_value);

      var groupA_redMax    = 300;
      var groupA_yellowMax = 450;
      var groupA_greenMax  = 900;
      var groupA_max = Math.ceil ( Math.max (groupA_greenMax, groupA_value) / 100 ) * 100;
      bloodStockVisualization1 = new google.visualization.Gauge(document.getElementById("bloodStockVisualization1_div"));
      var options1 = {width: 250, height: 250, max: groupA_max, redFrom: 0, redTo: groupA_redMax, yellowFrom: groupA_redMax, yellowTo: groupA_yellowMax, greenFrom: groupA_yellowMax, greenTo: groupA_max, majorTicks: ["0", 1 / 6 * groupA_max, 1 / 3 * groupA_max, 1 / 2 * groupA_max, 2 / 3 * groupA_max, 5 / 6 * groupA_max, groupA_max], minorTicks: 5};
      bloodStockVisualization1.draw(visualisationData1, options1);

      // Set blood group A status
      if ( groupA_value <= groupA_redMax ) {
        document.getElementById("bloodStockStatus1_span").innerHTML = "Critical";
      }
      else if ( groupA_value <= groupA_yellowMax ) {
        document.getElementById("bloodStockStatus1_span").innerHTML = "Low";
      }
      else {
        document.getElementById("bloodStockStatus1_span").innerHTML = "Comfortable";
      }

      // Blood group B data
      visualisationData2.addColumn("string", "Blood Group");
      visualisationData2.addColumn("number", "Units in Stock");
      visualisationData2.addRows(1);

      var groupB_value = excelData.getValue(0, 2);
      visualisationData2.setValue( 0, 0, "Group B");
      visualisationData2.setValue( 0, 1, groupB_value);

      var groupB_redMax    = 350;
      var groupB_yellowMax = 700;
      var groupB_greenMax  = 1200;
      var groupB_max = Math.ceil ( Math.max (groupB_greenMax, groupB_value) / 300 ) * 300;
      bloodStockVisualization2 = new google.visualization.Gauge(document.getElementById("bloodStockVisualization2_div"));
      var options2 = {width: 250, height: 250, max: groupB_max, redFrom: 0, redTo: groupB_redMax, yellowFrom: groupB_redMax, yellowTo: groupB_yellowMax, greenFrom: groupB_yellowMax, greenTo: groupB_max, majorTicks: ["0", 1 / 6 * groupB_max, 1 / 3 * groupB_max, 1 / 2 * groupB_max, 2 / 3 * groupB_max, 5 / 6 * groupB_max, groupB_max], minorTicks: 5};
      bloodStockVisualization2.draw(visualisationData2, options2);

      // Set blood group B status
      if ( groupB_value <= groupB_redMax ) {
        document.getElementById("bloodStockStatus2_span").innerHTML = "Critical";
      }
      else if ( groupB_value <= groupB_yellowMax ) {
        document.getElementById("bloodStockStatus2_span").innerHTML = "Low";
      }
      else {
        document.getElementById("bloodStockStatus2_span").innerHTML = "Comfortable";
      }

      // Blood group O data
      visualisationData3.addColumn("string", "Blood Group");
      visualisationData3.addColumn("number", "Units in Stock");
      visualisationData3.addRows(1);

      var groupO_value = excelData.getValue(0, 3);
      visualisationData3.setValue( 0, 0, "Group O");
      visualisationData3.setValue( 0, 1, groupO_value);

      var groupO_redMax    = 400;
      var groupO_yellowMax = 600;
      var groupO_greenMax  = 1200;
      var groupO_max = Math.ceil ( Math.max (groupO_greenMax, groupO_value) / 300 ) * 300;
      bloodStockVisualization3 = new google.visualization.Gauge(document.getElementById("bloodStockVisualization3_div"));
      var options3 = {width: 250, height: 250, max: groupO_max, redFrom: 0, redTo: groupO_redMax, yellowFrom: groupO_redMax, yellowTo: groupO_yellowMax, greenFrom: groupO_yellowMax, greenTo: groupO_max, majorTicks: ["0", 1 / 6 * groupO_max, 1 / 3 * groupO_max, 1 / 2 * groupO_max, 2 / 3 * groupO_max, 5 / 6 * groupO_max, groupO_max], minorTicks: 5};
      bloodStockVisualization3.draw(visualisationData3, options3);

      // Set blood group O status
      if ( groupO_value <= groupO_redMax ) {
        document.getElementById("bloodStockStatus3_span").innerHTML = "Critical";
      }
      else if ( groupO_value <= groupO_yellowMax ) {
        document.getElementById("bloodStockStatus3_span").innerHTML = "Low";
      }
      else {
        document.getElementById("bloodStockStatus3_span").innerHTML = "Comfortable";
      }

      // Blood group AB data
      visualisationData4.addColumn("string", "Blood Group");
      visualisationData4.addColumn("number", "Units in Stock");
      visualisationData4.addRows(1);

      var groupAB_value = excelData.getValue(0, 4);
      visualisationData4.setValue( 0, 0, "Group AB");
      visualisationData4.setValue( 0, 1, groupAB_value);

      var groupAB_redMax    = 100;
      var groupAB_yellowMax = 200;
      var groupAB_greenMax  = 300;
      var groupAB_max = Math.ceil ( Math.max (groupAB_greenMax, groupAB_value) / 300 ) * 300;
      bloodStockVisualization4 = new google.visualization.Gauge(document.getElementById("bloodStockVisualization4_div"));
      var options4 = {width: 250, height: 250, max: groupAB_max, redFrom: 0, redTo: groupAB_redMax, yellowFrom: groupAB_redMax, yellowTo: groupAB_yellowMax, greenFrom: groupAB_yellowMax, greenTo: groupAB_max, majorTicks: ["0", 1 / 6 * groupAB_max, 1 / 3 * groupAB_max, 1 / 2 * groupAB_max, 2 / 3 * groupAB_max, 5 / 6 * groupAB_max, groupAB_max], minorTicks: 5};
      bloodStockVisualization4.draw(visualisationData4, options4);

      // Set blood group AB status
      if ( groupAB_value <= groupAB_redMax ) {
        document.getElementById("bloodStockStatus4_span").innerHTML = "Critical";
      }
      else if ( groupAB_value <= groupAB_yellowMax ) {
        document.getElementById("bloodStockStatus4_span").innerHTML = "Low";
      }
      else {
        document.getElementById("bloodStockStatus4_span").innerHTML = "Comfortable";
      }

      // Set blood stock update date
      document.getElementById("bloodStockUpdate_span").innerHTML = getBloodStockDate();
    }

    function getBloodStockDate() {
      var weekDay = new Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday");
      var monthName = new Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");

      return (weekDay[bloodStockDate.getDay()] + " " + monthName[bloodStockDate.getMonth()] + " " + bloodStockDate.getDate() + ", " + bloodStockDate.getFullYear());
    }

// JavaScript code for Blood Stock Indicator: END