<?php

function fossee_stats_permission()
{
    return array(
        "access fossee_stats" => array(
            "title" => t("Access fossee_stats"),
            "description" => t("Allows users to view fossee stats.")
        ),
        "view workshop" => array(
            "title" => t("View Workshop"),
            "description" => t("Allows users to view workshop.")
        ),
        "manage workshop" => array(
            "title" => t("Manage Workshop"),
            "description" => t("Allows users to manage workshop.")
        ),
	   "view postalcampaign" => array(
            "title" => t("View postalcampaign"),
            "description" => t("Allows users to view postalcampaign.")
        ),
	   "manage postalcampaign" => array(
            "title" => t("Manage Postalcampaign"),
            "description" => t("Allows users to manage postalcampaign.")
        ),
	   "view tutorial" => array(
            "title" => t("View Spoken Tutorial"),
            "description" => t("Allows users to view spoken tutorial.")
        ),
        "manage tutorial" => array(
            "title" => t("Manage Spoken Tutorial"),
            "description" => t("Allows users to manage spoken tutorial.")
        ),
        "view events" => array(
            "title" => t("View Events"),
            "description" => t("Allows users to view events.")
        ),
        "manage events" => array(
            "title" => t("Manage Events"),
            "description" => t("Allows users to manage events.")
        ),
	    "administer events" => array(
            "title" => t("Events upload setting"),
            "description" => t("Allows users to manage postalcampaign.")
        )
       
    );
}

function fossee_stats_menu()
{
    $items = array();
    //Load main page
    $items["fossee-stats"] = array(
        "title" => "FOSSEE STATS",
        "page callback" => "drupal_get_form",
        "page arguments" => array(
            "fossee_stats_form"
        ),
        "access arguments" => array(
            "access fossee_stats"
        ),
        "type" => MENU_NORMAL_ITEM
    );
    //Fetch the list of TBC and LM which comes after filter is applied.
    $items["get-list"] = array(
        "title" => "",
        "page callback" => "list_tbc_and_lm_detail",
        'page arguments' => array(
            8
        ),
        "access arguments" => array(
            "access fossee_stats"
        ),
        "type" => MENU_CALLBACK
    );
    //Fetch the list of self workshop with all detail after filter is applied and count is received
    $items['completed-workshops-list'] = array(
	'title' => '',
        'page callback' => 'completed_workshops_list',
	'page arguments' => array(
            5
        ),
        "access arguments" => array(
           "access fossee_stats"
        ),
	//'access callback' => TRUE,
	 "type" => MENU_CALLBACK
     );
     //Fetch the Details of self workshop 
     $items['view-completed-workshop'] = array(
     'title' => 'Workshop Details',
     'page callback' => 'view_completed_workshop',
     'access callback' => TRUE,
                );

    //Following are menu of Events (Workshop, Conference along with testimonial and images and speakers)
   
    $items["events/add"] = array(
        "title" => "Add Events",
        "page callback" => "workshop_display_all",
        "access arguments" => array(
            "manage workshop"
        ),
        "type" => MENU_CALLBACK
    );
	//To edit existing events detail
    $items["events/edit"] = array(
        "title" => "Edit Events",
        "page callback" => "workshop_edit_all",
        "access arguments" => array(
            "manage workshop"
        ),
        "type" => MENU_CALLBACK
    );
	//To delete existing events
    $items["events/delete"] = array(
        "title" => "Delete Events",
        "page callback" => "workshop_delete_all",
        "access arguments" => array(
            "manage workshop"
        ),
        "type" => MENU_CALLBACK
    );
    //List of all events
    $items["events/view_details"] = array(
        "title" => "Details of Selected Event",
        "page callback" => "workshop_view_details_all",
        "access arguments" => array(
            "view workshop"
        ),
        "type" => MENU_CALLBACK
    );
	 //List of images added to particular event
    $items["events/images"] = array(
        "title" => "Event's Images",
        "page callback" => "workshop_view_image_all",
        "access arguments" => array(
            "view workshop"
        ),
        "type" => MENU_CALLBACK
    );
	//For changing image
    $items["events/images/change"] = array(
        "title" => "Event's Images",
        "page callback" => "workshop_view_image_change",
        "access arguments" => array(
            "view workshop"
        ),
        "type" => MENU_CALLBACK
    );
    //Display all testimonial added for event in list
    $items["events/testimonials"] = array(
        //"title" => "Testimonials",
        "page callback" => "workshop_view_all_testimonials",
        "access arguments" => array(
            "view workshop"
        ),
        "type" => MENU_CALLBACK
    );
    //Display details of selected testimonial
    $items["events/testimonials/view"] = array(
        "title" => "Testimonial",
        "page callback" => "workshop_view_selected_testimonial",
        "access arguments" => array(
            "view workshop"
        ),
        "type" => MENU_CALLBACK
    );

    $items["events"] = array(
        "title" => "Events",
        "page callback" => "event_seminar_view_all",
        "access arguments" => array(
            "view events"
        ),
        "type" => MENU_CALLBACK
    );

    $items["testconf"] = array(
        "title" => "Activities",
        "page callback" => "event_activities_all",
        "access arguments" => array(
            "view events"
        ),
        "type" => MENU_CALLBACK
    );
    

     // List of all Postal Campaign
     $items["postalcampaign"] = array(
        "title" => "Postal Campaign",
        "page callback" => "postalcampaign_view_withoutfilter",
        "access arguments" => array(
            "view postalcampaign"
        ),
        "type" => MENU_CALLBACK
    );
	 // Add Postal Campaign
    $items["postalcampaign/add"] = array(
        "title" => "Add Postal Campaign",
        "page callback" => "postalcampaign_display_all",
        "access arguments" => array(
            "manage postalcampaign"
        ),
        "type" => MENU_CALLBACK
    );
	// Edit Postal Campaign
      $items["postalcampaign/edit"] = array(
        "title" => "Edit Postal Campaign",
        "page callback" => "postalcampaign_edit_all",
        "access arguments" => array(
            "manage postalcampaign"
        ),
        "type" => MENU_CALLBACK
    );
	// Delete Postal Campaign
    $items["postalcampaign/delete"] = array(
        "title" => "Delete Postal Campaign",
        "page callback" => "postalcampaign_delete_all",
        "access arguments" => array(
            "manage postalcampaign"
        ),
        "type" => MENU_CALLBACK
    );
	// View detail of selected Postal Campaign
    $items["postalcampaign/view_details"] = array(
        "title" => "Postal Campaign Detail",
        "page callback" => "postalcampaign_view_details_all",
        "access arguments" => array(
            "view postalcampaign"
        ),
        "type" => MENU_CALLBACK
    );
	//To download postal campaign poster in zip format
	$items["postalcampaign/download"] = array(
        "title" => "Download Postal Campaign",
        "page callback" => "createZipOfPosterMaterial",
        "access arguments" => array(
            "view postalcampaign"
        ),
        "type" => MENU_CALLBACK
    );

    /*Spoken Tutorial*/

     //Add Spoken Tutorial

    $items["spokentutorial/add"] = array(
        "title" => "Add Spoken Tutorial",
        "page callback" => "spokentutorial_display_all",
        "access arguments" => array(
            "view tutorial"
        ),
        "type" => MENU_CALLBACK
    );

    /* ADMIN SETTINGS */
    //For mentioning extension allowed for image and pdf
    $items['admin/settings/events'] = array(
        'title' => 'Events Settings',
        'description' => 'Events Settings',
        'page callback' => 'drupal_get_form',
        'page arguments' => array(
            'events_settings_form'
        ),
        'access arguments' => array(
            'administer events'
        ),
        'type' => MENU_NORMAL_ITEM,
        'file' => 'settings.inc'
    );
    return $items;
}

//Folder path to save all uploaded images of events
function events_images_path()
 {
   return $_SERVER['DOCUMENT_ROOT'] . base_path() . 'events_images/';
 }

//Folder path to save all uploaded images/PDF of postal campaign
function posters_path() {

  return $_SERVER['DOCUMENT_ROOT'] . base_path() . 'campaign_posters/';
}

//Main Page Form
function fossee_stats_form($form, &$form_state)
{
    /* First Dropdown for Foss type */

    $options_first = _ajax_example_get_first_dropdown_options();
    if (isset($form_state['values']['foss_sub_project']) && isset($form_state['values']['foss_type']) && isset($form_state['values']['foss_sub_project_status'])) {
        $foss_project = isset($form_state['values']['foss_type']) ? $form_state['values']['foss_type'] : key($options_first);
    } else {
        $foss_project = '';
    }
    $form['foss_type'] = array(
        '#type' => 'select',
        '#prefix' => '<div class="content"><div><table ><tr><td>',
        '#suffix' => '</td>',
        '#title' => t('FOSS Type'),
        '#multiple' => false,
        '#options' => $options_first,
        //'#default_value' => $foss_project,
        '#validated' => TRUE,
        '#ajax' => array(
            'callback' => 'ajax_foss_type_dependent_dropdown_callback',
            'wrapper' => 'dropdown-second-replace',
            'progress' => array(
                'message' => ''
            )
        )
    );
	 /* Second Dropdown for Activities like TBC, LM and other */

    if (isset($form_state['values']['foss_sub_project']) && isset($form_state['values']['foss_type']) && isset($form_state['values']['foss_sub_project_status'])) {
        $foss_sub_project = isset($form_state['values']['foss_sub_project']) ? $form_state['values']['foss_sub_project'] : key(get_activities_list($foss_project));
    } else {
        $foss_sub_project = '';
    }
    $form['foss_sub_project'] = array(
        '#type' => 'select',
        '#title' => t('Activities'),
        '#options' => get_activities_list($foss_project),
        '#prefix' => '<td><div id="dropdown-second-replace" style="width:250px;padding-left:25px" >',
        '#suffix' => '</div>',
        '#default_value' => "--------------",
        '#validated' => TRUE,
        '#ajax' => array(
            'callback' => 'ajax_foss_sub_project_dependent_dropdown_callback',
            'wrapper' => 'dropdown-third-replace',
            'progress' => array(
                'message' => ''
            )
        ),
        
        '#states' => array(
            'invisible' => array(
                ':input[name="foss_type"]' => array(
                    'value' => ""
                )
            )
        )
    );

	 /* third Dropdown for Status like Completed and In Progress */
    if (isset($form_state['values']['foss_sub_project']) && isset($form_state['values']['foss_type']) && isset($form_state['values']['foss_sub_project_status'])) {
        $foss_sub_project_status = isset($form_state['values']['$foss_sub_project_status']) ? $form_state['values']['$foss_sub_project_status'] : '';
    } else {
        $foss_sub_project_status = '';
    }
    $form['foss_sub_project_status'] = array(
        '#type' => 'select',
        '#title' => t('Status'),
        '#options' => _ajax_example_get_third_dropdown_options($foss_sub_project),
        '#prefix' => '<td><div id="dropdown-third-replace" style="width:250px">',
        '#suffix' => '</div></td></tr>',
        '#default_value' => $foss_sub_project_status,
        '#validated' => TRUE,
        '#states' => array(
            'invisible' => array(
                array(
                    array(
                        ':input[name="foss_sub_project"]' => array(
                            'value' => 0
                        )
                    ),
                    'or',
                    array(
                        ':input[name="foss_type"]' => array(
                            'value' => ""
                        )
                        
                    )
                )
            )
        )
    );
     /* Start Date for Filter */
    $form['start_date'] = array(
        '#type' => 'date_popup',
        '#title' => t('From Date:'),
        '#date_label_position' => '',
        '#description' => '',
        '#default_value' => '',
        '#date_format' => 'Y-m-d',
        '#date_increment' => 0,
        '#date_year_range' => '2011:+0',
        '#prefix' => '<tr><td><div id="startdate">',
        '#suffix' => '</div></td>',
        '#datepicker_options' => array(
            'maxDate' => 0
        ),
        '#states' => array(
            'invisible' => array(
                ':input[name="foss_type"]' => array(
                    'value' => ""
                )
            )
        )
    );
 /* End Date for Filter */
    $form['end_date'] = array(
        '#type' => 'date_popup',
        '#title' => t('To Date:'),
        '#date_label_position' => '',
        '#description' => '',
        '#default_value' => '',
        '#date_format' => 'Y-m-d',
        '#date_increment' => 0,
        '#date_year_range' => '2011:+0',
        '#prefix' => '<td><div id="enddate" style="padding-left:15px;">',
        '#suffix' => '</div></td>',
        '#datepicker_options' => array(
            'maxDate' => 0
        ),
        '#states' => array(
            'invisible' => array(
                ':input[name="foss_type"]' => array(
                    'value' => ""
                )
            )
        )
    );
     /* Country list for Filter */
    if (isset($form_state['values']['countryname']) && isset($form_state['values']['statename']) && isset($form_state['values']['cityname'])) {
        $countryname = isset($form_state['values']['countryname']) ? $form_state['values']['countryname'] : key(get_country_list($foss_project, $foss_sub_project));
    } else {
        $countryname = '';
    }
    $form['countryname'] = array(
        "#type" => "select",
        "#title" => t("Select Country"),
        '#prefix' => '<td><div style="width:200px;" id= "load_country">',
        '#suffix' => '</div></td></tr>',
        '#options' => get_country_list($foss_project, $foss_sub_project),
        '#states' => array(
            'invisible' => array(
                array(
                    array(
                        ':input[name="foss_sub_project"]' => array(
                            'value' => 0
                        )
                    ),
                    'or',
                    array(
                        ':input[name="foss_type"]' => array(
                            'value' => ""
                        ),
                        ':input[name="foss_type"]' => array(
                            'value' => 3
                        )
                        
                    )
                )
            )
        ),
        '#ajax' => array(
            'callback' => 'ajax_state_list_dependent_dropdown_callback',
            'wrapper' => 'load_state',
            'progress' => array(
                'message' => ''
            )
        )
    );
    
 /* State list for Filter */
    if (isset($form_state['values']['countryname']) && isset($form_state['values']['statename']) && isset($form_state['values']['cityname'])) {
        $statename = isset($form_state['values']['statename']) ? $form_state['values']['statename'] : key(get_state_list($foss_project, $foss_sub_project, $countryname));
    } else {
        $statename = '';
    }
    
    $form['statename'] = array(
        "#type" => "select",
        "#title" => t("State Name"),
        '#validated' => TRUE,
        '#options' => get_state_list($foss_project, $foss_sub_project, $countryname),
        '#prefix' => '<tr><td><div style="width:150px;" id= "load_state">',
        '#suffix' => '</div></td>',
        '#states' => array(
            'invisible' => array(
                array(
                    array(
                        ':input[name="foss_sub_project"]' => array(
                            'value' => 0
                        )
                    ),
                    'or',
                    array(
                        ':input[name="foss_type"]' => array(
                            'value' => ""
                        ),
                        ':input[name="foss_type"]' => array(
                            'value' => 3
                        )
                        
                    )
                )
            )
        ),
        '#ajax' => array(
            'callback' => 'ajax_city_list_dependent_dropdown_callback',
            'wrapper' => 'load_state',
            'progress' => array(
                'message' => ''
            )
        )
    );
    
/* City list for Filter */
    if (isset($form_state['values']['countryname']) && isset($form_state['values']['statename']) && isset($form_state['values']['cityname'])) {
        $cityname = isset($form_state['values']['cityname']) ? $form_state['values']['cityname'] : key(get_city_list($foss_project, $foss_sub_project, $countryname, $statename));
    } else {
        $cityname = '';
    }
    $form['cityname'] = array(
        "#type" => "select",
        "#title" => t("City Name"),
        '#validated' => TRUE,
        '#options' => get_city_list($foss_project, $foss_sub_project, $countryname, $statename),
        '#prefix' => '<td><div style="width:200px ;padding-left:30px;" id= "load_city">',
        '#suffix' => '</div></td></tr>',
        '#states' => array(
            'invisible' => array(
                array(
                    array(
                        ':input[name="foss_sub_project"]' => array(
                            'value' => 0
                        )
                    ),
                    'or',
                    array(
                        ':input[name="foss_type"]' => array(
                            'value' => ""
                        ),
                        ':input[name="foss_type"]' => array(
                            'value' => 3
                        )
                        
                    )
                )
            )
        )
    );
    $form['submit'] = array(
        '#type' => 'submit',
        '#ajax' => array(
            'callback' => 'ajax_example_submit_driven_callback',
            'progress' => array(
                'message' => ''
            )
        ),
        '#value' => t('Filter'),        
        '#prefix' => '<tr><td align="center">',
        '#suffix' => ''
    );
    $form['reset'] = array(
        '#type' => 'submit',
        '#value' => t('Reset'),        
        '#prefix' => '',
        '#suffix' => '</td></tr></table></div>'
    );
    //This is used for displaying text (tab sta)
    $form['displaytext'] = array(
        '#type' => 'markup',
        '#prefix' => '<div><div id="displaytext" style="font-weight:bold;padding-top:10px">',
        '#suffix' => '</div></div>',
        '#markup' => ''
    );
    //For displaying count of TBC of all foss
    $form['tbctable'] = array(
        '#type' => 'item',
        '#prefix' => '<div id="default_load" >',
        '#markup' => '<ul class="nav nav-tabs">                       
                        <li class="active"><a data-toggle="tab" href="#tbctabledata">Textbook Companion</a></li>
                         <li><a data-toggle="tab" href="#lmtabledata">Lab Migration</a></li>
                          <li><a data-toggle="tab" href="#workshopdata">Workshop</a></li>
                           <li><a data-toggle="tab" href="#selfworkshopdata">Self Workshop</a></li>
                            <li><a data-toggle="tab" href="#conferencedata">Conference </a></li>
                            <li><a data-toggle="tab" href="#spokentutorialdata">Spoken Tutorial</a></li>
                      </ul>'
     );
    $TBC_getchart = getchart("TBC");
    $LM_getchart = getchart("LM");
     $form['tab_content'] = array(
        '#type' => 'item',
        '#markup' => '<div class="tab-content">

        <div id="tbctabledata"class="tab-pane fade in active">' . get_tabledata_TBC_or_LM("TBC", "1960/01/01", "").'
        <div id="tbcchartdata" style="float:left;width:300px;height:300px;">' . drupal_render($TBC_getchart) . '</div>
        </div>
     
        <div id="lmtabledata" class="tab-pane fade ">' . get_tabledata_TBC_or_LM("LM", "1960/01/01", "").'
        <div id="lmchartdata" style="float:left;width:350px;height:300px;">' . drupal_render($LM_getchart) . '</div>
        </div>   

        <div id="workshopdata" class="tab-pane fade ">' . workshop_view_all(0,1960-01-01, date("Y-m-d")).'</div>
       
        <div id="selfworkshopdata" class="tab-pane fade">'.getselfworkshoplcount(0,1960-01-01, date("Y-m-d"),"","").'</div>
        
        <div id="conferencedata" class="tab-pane fade ">' . conference_seminar_view_all(0, "", "").'</div>

        <div id="spokentutorialdata" class="tab-pane fade ">'.spokentutorial_view_all("").'</div>
 </div>'
    );
    $form['lastdiv'] = array(
        '#type' => 'item',
        '#markup' => '',
        '#suffix' => '</div></div>'
    );
    return $form;
}

/* This function is used to create chart for TBC/LM */
function getchart($sub_type)
{
    $title = "";
    if ($sub_type == "TBC") {
        $title = "Textbook Companion Statictics";
    } else {
        $title = "Lab Migration Statistics";
    }
    $chart = array(
        '#type' => 'chart',
        '#title' => t($title),
        '#chart_type' => 'pie',
        '#chart_library' => 'google',
        '#legend_position' => 'right',
        '#data_labels' => TRUE,
        '#tooltips' => TRUE,
        '#width' => 700,
        '#height' => 300
    );
    $chart['pie_data'] = array(
        '#type' => 'chart_data',
        '#title' => t($title),
        '#data' => get_data_for_chart_allproject($sub_type)
    );
    $example['chart'] = $chart;
    return $example;
}

//This is used to create a  bar chart for particular LM or TBC and along with filters
function getchartforselectedProject($foss_type, $sub_type, $startdate, $enddate, $countryname, $statename, $cityname)
{
    
    $title = "";
    if ($sub_type == "TBC") {
        $title = $foss_type . "" . "'s Textbook Companion Statictics";
    } else {
        $title = $foss_type . "" . "'s Lab Migration Statistics";
    }
    
    $chart = array(
        '#type' => 'chart',
        '#title' => t($title),
        '#chart_type' => 'column',
        '#chart_library' => 'google',
        '#legend_position' => 'bottom',
        '#data_labels' => TRUE,
        '#tooltips' => TRUE,
          '#width' => 800,
        '#height' => 300
    );
    $chart['pie_data'] = array(
        '#type' => 'chart_data',
        '#title' => t($title),
        '#data' => get_data_for_chart_selectedproject($foss_type, $sub_type, $startdate, $enddate, $countryname, $statename, $cityname)
    );
    $example['chart'] = $chart;
    return $example;
}


//This is used to create a pie chart for particular LM or TBC and along with filters
function getpiechartforselectedProject($foss_type, $sub_type, $startdate, $enddate, $countryname, $statename, $cityname)
{
    
    $title = "";
    if ($sub_type == "TBC") {
        $title = $foss_type . "" . "'s Textbook Companion Statictics";
    } else {
        $title = $foss_type . "" . "'s Lab Migration Statistics";
    }
    $chart = array(
        '#type' => 'chart',
        '#title' => t($title),
        '#chart_type' => 'pie',
        '#chart_library' => 'google',
        '#legend_position' => 'right',
        '#data_labels' => TRUE,
        '#tooltips' => TRUE,
          '#width' => 700,
        '#height' => 300
    );
    $chart['pie_data'] = array(
        '#type' => 'chart_data',
        '#title' => t($title),
        '#data' => get_data_for_chart_selectedproject($foss_type, $sub_type, $startdate, $enddate, $countryname, $statename, $cityname)
    );
    $example['chart'] = $chart;
    return $example;
}


//Function is used to get the count of LM or TBC for all foss type and pass this count to create charts
function get_data_for_chart_allproject($sub_type)
{
    $rows = array();
    if ($sub_type == "TBC") {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'tbc'
        ));
        $query->condition('tbc', 1);
        $result = $query->execute();
        while ($foss_detail = $result->fetchObject()) {
            if ($foss_detail->foss_name != null) {
                if ($foss_detail->foss_name != 'Python') {
                    db_set_active($foss_detail->foss_name); //Active other database
                    //For TBC
                    if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0");
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1");
                    }
                    db_set_active('default'); // We need to call the main (drupal) db back
                    db_set_active();
                    $completedbookcount = $query2->fetchObject()->book_count;
                    $option1 = array(
                        $foss_detail->foss_name,
                        (int) $completedbookcount
                    );
                    array_push($rows, $option1);
                } else {
                    db_set_active($foss_detail->foss_name); //Active other database
                    $query2 = db_select('tbc_book');
                    $query2->addExpression('count(*)', 'count');
                    $query2->condition('approved', 1);
                    $result2 = $query2->execute();
                    db_set_active('default'); // We need to call the main (drupal) db back
                    db_set_active();
                    $completedbookcount = $result2->fetchObject()->count;
                    $option1 = array(
                        $foss_detail->foss_name,
                        (int) $completedbookcount
                    );
                    array_push($rows, $option1);
                }
            }
        }
    } else {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'lab_migration'
        ));
        $query->condition('lab_migration', 1);
        $result = $query->execute();
        while ($foss_detail = $result->fetchObject()) {
            if ($foss_detail->foss_name != null) {
                db_set_active($foss_detail->foss_name); //Active other database
                //For LM
                $query3 = db_select('lab_migration_proposal');
                $query3->addExpression('count(*)', 'count');
                $query3->condition('approval_status', 3);
                $result3 = $query3->execute();
                db_set_active('default'); // We need to call the main (drupal) db back
                db_set_active();
                $completedlabcount = $result3->fetchObject()->count;
                $option1 = array(
                    $foss_detail->foss_name,
                    (int) $completedlabcount
                );
                array_push($rows, $option1);
            }
        }
    }
    return $rows;
}

//This function is used to get list of TBC books/LM Labs after the filter is applied
function list_tbc_and_lm_detail($String, $foss_type, $sub_type, $status, $startdate, $enddate, $countryname, $statename, $cityname)
{
    
    if ($cityname == "" || $cityname == "null") {
        $cityname = "%";
    } else {
        $cityname = $cityname;
    }
    if ($statename == "" || $statename == "null") {
        $statename = "%";
    } else {
        $statename = $statename;
    }
    if ($countryname == "" || $countryname == "null") {
        $countryname = "%";
    } else {
        $countryname = $countryname;
    }
    
    $flag = 1;
    $page_content = "";
    db_set_active($foss_type);
    $query2 = "";
    if ($sub_type == "TBC") {
        
        if ($foss_type != null) {
            if ($foss_type != 'Python') {
                //For TBC
                if ($foss_type != 'eSim' && $foss_type != 'OpenFOAM' && $foss_type != 'OR-Tools') {
                    if ($foss_type != 'DWSIM') {
                        if ($status == "completed") {
                            $query2 = db_query("SELECT pe.book as book,pe.author as author,pe.publisher as publisher,pe.year as year,pe.id as id FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                                ':city' => $cityname,
                                ':state' => $statename,
                                ':country' => $countryname,
                                ':startdate' => $startdate,
                                ':enddate' => $enddate
                            ));
                            
                        }
                        if ($status == "pending") {
                            $query2 = db_query("SELECT pe.book,pe.author,pe.publisher,pe.year,pe.id  FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                                ':city' => $cityname,
                                ':state' => $statename,
                                ':country' => $countryname,
                                ':startdate' => $startdate,
                                ':enddate' => $enddate
                            ));
                            $flag = 0;
                        }
                    } else {
                        if ($status == "completed") {
                            $query2 = db_query("SELECT pe.book,pe.author,pe.publisher,pe.year,pe.id  FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                                ':city' => $cityname,
                                ':state' => $statename,
                                ':country' => $countryname,
                                ':startdate' => $startdate,
                                ':enddate' => $enddate
                            ));
                        }
                        if ($status == "pending") {
                            $query2 = db_query("SELECT pe.book,pe.author,pe.publisher,pe.year,pe.id FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                                ':city' => $cityname,
                                ':state' => $statename,
                                ':country' => $countryname,
                                ':startdate' => $startdate,
                                ':enddate' => $enddate
                            ));
                            $flag = 0;
                        }
                    }
                } else {
                    if ($status == "completed") {
                        $query2 = db_query("SELECT pe.book,pe.author,pe.publisher,pe.year,pe.id FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND po.city LIKE :city  AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                    if ($status == "pending") {
                        $query2 = db_query("SELECT pe.book,pe.author,pe.publisher,pe.year,pe.id FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                        $flag = 0;
                    }
                }
                
            } else {
                if ($status == "completed") {
                    $query2 = db_select('tbc_book', 'tbc');
                    $query2->fields('tbc', array(
                        'title',
                        'author',
                        'id'
                    ));
                    $query2->condition('approved', 1);
                    
                }
                if ($status == "pending") {
                    $query2 = db_select('tbc_book', 'tbc');
                    $query2->fields('tbc', array(
                        'title',
                        'author',
                        'id'
                    ));
                    $query2->condition('approved', 1, '<>');
                    
                }
                
            }
        }
        db_set_active('default'); // We need to call the main (drupal) db back
        db_set_active();
        if ($flag != 0) {
            $page_content .= "<h3>List of Completed Books</h3><br>";
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'tbc_run'
            ));
            $query->condition('tbc', 1);
            $query->condition('foss_name', $foss_type);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            $page_content .= "<ol>";
            $i = 1;
            while ($preference_data = $query2->fetchObject()) {
                $page_content .= "<li>" . $i++ . ". ";
                $page_content .= "<a href=" . $foss_detail->tbc_run . "/" . $preference_data->id . " target='_blank'>" . $preference_data->book . " by " . $preference_data->author . ", " . $preference_data->publisher . ", " . $preference_data->year . "</a>";
                
                
                $page_content .= "</li>";
            }
            $page_content .= "</ol>";
            
        } else {
            $page_content .= "<h3>List of Books in Progress</h3><br>";
            $page_content .= "<ol>";
            $i = 1;
            while ($preference_data = $query2->fetchObject()) {
                $page_content .= "<li>" . $i++ . ". ";
                $page_content .= $preference_data->book . " by " . $preference_data->author . ", " . $preference_data->publisher . ", " . $preference_data->year;
                $page_content .= "</li>";
            }
            $page_content .= "</ol>";
        }
        
    } else {
        
        if ($foss_type != null) {
            
            if ($status == "completed") {
                $query2 = db_query("SELECT * from lab_migration_proposal WHERE approval_status=3 AND city LIKE :city AND 	state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                    ':city' => $cityname,
                    ':state' => $statename,
                    ':country' => $countryname,
                    ':startdate' => $startdate,
                    ':enddate' => $enddate
                ));
            }
            if ($status == "pending") {
                $query2 = db_query("SELECT * from lab_migration_proposal WHERE approval_status=1 AND city LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                    ':city' => $cityname,
                    ':state' => $statename,
                    ':country' => $countryname,
                    ':startdate' => $startdate,
                    ':enddate' => $enddate
                ));
                $flag = 0;
            }
            db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();
            if ($flag != 0) {
                $page_content .= "<h3>List of Completed Labs</h3><br>";
                $query = db_select('foss_type');
                $query->fields('foss_type', array(
                    'id'
                ));
                $query->fields('foss_type', array(
                    'lm_run'
                ));
                $query->condition('lab_migration', 1);
                $query->condition('foss_name', $foss_type);
                $result = $query->execute();
                $foss_detail = $result->fetchObject();
                $page_content .= "<ol>";
                $i = 1;
                while ($row = $query2->fetchObject()) {
                    $page_content .= "<li>" . $i++ . ". ";
                    $approval_date = date("Y", $row->approval_date);
                    $page_content .= "<a href=" . $foss_detail->lm_run . "/" . $row->id . " target='_blank'>" . $row->lab_title . " " . $approval_date . "  (Proposed by " . $row->name . ") </a>";
                    //$page_content .= l($row->lab_title . " " . $approval_date,$foss_detail->lm_run."/". $row->id);
                    $page_content .= "</li>";
                }
                $page_content .= "</ol>";
            } else {
                $page_content .= "<h3>List of Labs in Progress</h3><br>";
                $page_content .= "<ol>";
                $i = 1;
                while ($row = $query2->fetchObject()) {
                    $page_content .= "<li>" . $i++ . ". ";
                    $approval_date = date("Y", $row->approval_date);
                    $page_content .= $row->lab_title . " " . $approval_date . "  (Proposed by " . $row->name . ")";
                    $page_content .= "</li>";
                }
                $page_content .= "</ol>";
            }
            
        }
        
    }
    db_set_active('default'); // We need to call the main (drupal) db back
    db_set_active();
    
    return $page_content;
}

/* This function is used to get count of LM and TBC after a filter is applied and pass this count to create chart function*/
function get_data_for_piechart_selectedproject($foss_type, $sub_type, $startdate, $enddate, $countryname, $statename, $cityname)
{
    
    if ($enddate == "") {
        $enddate = date("Y-m-d");
    }
    $rows = array();
    if ($sub_type == "TBC") {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'tbc'
        ));
        $query->condition('tbc', 1);
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
        if ($foss_detail->foss_name != null) {
            if ($foss_detail->foss_name != 'Python') {
                $rows = array();
                db_set_active($foss_detail->foss_name); //Active other database
                //For TBC
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                        $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                        $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                } else {
                    $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND po.city LIKE :city  AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                    $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                }
                db_set_active('default'); // We need to call the main (drupal) db back
                db_set_active();
                $completedbookcount = $query2->fetchObject()->book_count;
                $pendingbookcount = $query3->fetchObject()->book_count;
                $option1 = array(
                    "Completed TBC",
                    (int) $completedbookcount
                );
                array_push($rows, $option1);
                $option2 = array(
                    "In Progress TBC",
                    (int) $pendingbookcount
                );
                array_push($rows, $option2);
            } else {
                $rows = array();
                db_set_active($foss_detail->foss_name); //Active other database
                $query2 = db_select('tbc_book');
                $query2->addExpression('count(*)', 'count');
                //$query2->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $query2->condition('approved', 1);
                $result2 = $query2->execute();
                $query3 = db_select('tbc_book');
                $query3->addExpression('count(*)', 'count');
                //$query3->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $query3->condition('approved', 1, '<>');
                $result3 = $query3->execute();
                db_set_active('default'); // We need to call the main (drupal) db back
                db_set_active();
                $completedbookcount = $result2->fetchObject()->count;
                $pendingbookcount = $result3->fetchObject()->count;
                $option1 = array(
                    "Completed TBC",
                    (int) $completedbookcount
                );
                array_push($rows, $option1);
                $option2 = array(
                    "In Progress TBC",
                    (int) $pendingbookcount
                );
                array_push($rows, $option2);
            }
        }
    } else {
        $rows = array();
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'lab_migration'
        ));
        $query->condition('lab_migration', 1);
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
        if ($foss_detail->foss_name != null) {
            db_set_active($foss_detail->foss_name); //Active other database
            //For LM
            $query3 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=3 AND city LIKE :city AND 	state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
            $query4 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=1 AND city LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
            db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();
            $completedlabcount = $query3->fetchObject()->count;
            $pendinglabcount = $query4->fetchObject()->count;
            $option1 = array(
                "Completed Labs",
                (int) $completedlabcount
            );
            array_push($rows, $option1);
            $option2 = array(
                "In Progress labs",
                (int) $pendinglabcount
            );
            array_push($rows, $option2);
        }
    }
    return $rows;
}


/* This function is used to get count of LM and TBC after a filter is applied and pass this count to create chart function*/
function get_data_for_chart_selectedproject($foss_type, $sub_type, $startdate, $enddate, $countryname, $statename, $cityname)
{
    
    if ($enddate == "") {
        $enddate = date("Y-m-d");
    }
    $rows = array();
    if ($sub_type == "TBC") {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'tbc'
        ));
        $query->condition('tbc', 1);
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
        if ($foss_detail->foss_name != null) {
            if ($foss_detail->foss_name != 'Python') {
                $rows = array();
                db_set_active($foss_detail->foss_name); //Active other database
                //For TBC
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count ,FROM_UNIXTIME(po.completion_date, '%Y') AS year FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate Group by FROM_UNIXTIME(po.completion_date, '%Y')", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                       /* $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));*/
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count ,FROM_UNIXTIME(po.completion_date, '%Y') AS year FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate Group by FROM_UNIXTIME(po.completion_date, '%Y')", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                       /* $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));*/
                    }
                } else {
                    $query2 = db_query("SELECT COUNT( pe.book ) AS book_count, FROM_UNIXTIME(po.completion_date, '%Y') AS year FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND po.city LIKE :city  AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate Group by FROM_UNIXTIME(po.completion_date, '%Y')", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                    /*$query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));*/
                }
                db_set_active('default'); // We need to call the main (drupal) db back
                db_set_active();

                $result = $query2->fetchAll();

                foreach ($result as $tbc_yearwise_count) {
  
  					$completedbookcount= $tbc_yearwise_count->book_count;
        		$option1 = array(
                    $tbc_yearwise_count->year,
                    (int) $completedbookcount
                );
                array_push($rows, $option1);
				}
               /* while ($tbc_yearwise_count = $result->fetchObject()) {
                	$completedbookcount= $tbc_yearwise_count->book_count;
        		$option1 = array(
                    $tbc_yearwise_count->year,
                    (int) $completedbookcount
                );
                array_push($rows, $option1);

    			}*/


                //$completedbookcount = $query2->fetchObject()->book_count;

                //$pendingbookcount = $query3->fetchObject()->book_count;
                /*$option1 = array(
                    "Completed TBC",
                    (int) $completedbookcount
                );
                array_push($rows, $option1);*/
               /* $option2 = array(
                    "In Progress TBC",
                    (int) $pendingbookcount
                );
                array_push($rows, $option2);*/
            } else {
                $rows = array();
                db_set_active($foss_detail->foss_name); //Active other database
                $query2 = db_select('tbc_book');
                $query2->addExpression('count(*)', 'count');
                //$query2->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $query2->condition('approved', 1);
                $result2 = $query2->execute();

                /*$query3 = db_select('tbc_book');
                $query3->addExpression('count(*)', 'count');
                //$query3->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $query3->condition('approved', 1, '<>');
                $result3 = $query3->execute();*/
                db_set_active('default'); // We need to call the main (drupal) db back
                db_set_active();
                $completedbookcount = $result2->fetchObject()->count;
                //$pendingbookcount = $result3->fetchObject()->count;
                $option1 = array(
                    "Completed TBC",
                    (int) $completedbookcount
                );
                array_push($rows, $option1);
                /*$option2 = array(
                    "In Progress TBC",
                    (int) $pendingbookcount
                );
                array_push($rows, $option2);*/
            }
        }
    } else {
        $rows = array();
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'lab_migration'
        ));
        $query->condition('lab_migration', 1);
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
        if ($foss_detail->foss_name != null) {
            db_set_active($foss_detail->foss_name); //Active other database
            //For LM
            $query3 = db_query("SELECT COUNT(*) AS count ,FROM_UNIXTIME(approval_date, '%Y') AS year from lab_migration_proposal WHERE approval_status=3 AND city LIKE :city AND 	state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate group by FROM_UNIXTIME(approval_date, '%Y')", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
           /* $query4 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=1 AND city LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));*/
            db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();

            $result = $query3->fetchAll();

                foreach ($result as $lm_yearwise_count) {
  
  					$completedlabcount= $lm_yearwise_count->count;
        		$option1 = array(
                    $lm_yearwise_count->year,
                    (int) $completedlabcount
                );
                array_push($rows, $option1);
				}
            
            	/*$result = $query3->execute();

                while ($lm_yearwise_count = $result->fetchObject()) {
                	$completedlabcount= $lm_yearwise_count->count;
        		$option1 = array(
                    $lm_yearwise_count->year,
                    (int) $completedlabcount
                );
                array_push($rows, $option1);

    			}*/

            /*$completedlabcount = $query3->fetchObject()->count;
            //$pendinglabcount = $query4->fetchObject()->count;
            $option1 = array(
                "Completed Labs",
                (int) $completedlabcount
            );
            array_push($rows, $option1);*/
            /*$option2 = array(
                "In Progress labs",
                (int) $pendinglabcount
            );
            array_push($rows, $option2);*/
        }
    }
    return $rows;
}


//List of option to fill first drop down ie. Foss type.
function _ajax_example_get_first_dropdown_options()
{
    $query = db_select('foss_type');
    $query->fields('foss_type', array(
        'id'
    ));
    $query->fields('foss_type', array(
        'foss_name'
    ));
    $result = $query->execute();
    $options = array();
    $options[''] = "--------------";
    while ($foss_detail = $result->fetchObject()) {
        $options[$foss_detail->id] = $foss_detail->foss_name;
    }
    return $options;
}

//List of option to fill secong drop down ie. Activities
/*function _ajax_example_get_second_dropdown_options($foss_project = '')
{
    if ($foss_project != NULL) {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'tbc',
            'lab_migration',
            'workshop',
            'self_workshop',
            'conference'
        ));
        $query->fields('foss_type', array(
            'spoken_tutorial',
            'postal_campaigns'
        ));
        $query->condition('id', $foss_project);
        $result = $query->execute();
        $subproject_detail = $result->fetchObject();
        $optiondata = array(
            "tbc",
            "lab_migration",
            "workshop",
            "self_workshop",
            "conference",
            "spoken_tutorial",
            "postal_campaigns"
        );
        $optionvalue = array(
            " ",
            "Textbook Companion",
            "Lab Migration",
            "Workshop",
            "Self Workshop",
            "Conference",
            "Spoken Tutorial",
            "Postal Campaigns"
        );
        $options = array();
        $options[0] = "--------------";
        $i = 0;
        foreach ($optiondata as $value) {
            $i++;
            if (($subproject_detail->$value) != 0) {
                $options[$i] = $optionvalue[$i];
            }
        }
        return $options;
    } else {
        $options[0] = "--------------";
        return $options;
    }
}*/

//List of option to fill secong drop down ie. Activities
function get_activities_list($foss_project)
{
    if ($foss_project != NULL) {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'tbc',
            'lab_migration',
            'workshop',
            'self_workshop',
            'conference'
        ));
        $query->fields('foss_type', array(
            'spoken_tutorial',
            'postal_campaigns'
        ));
        $query->condition('id', $foss_project);
        $result = $query->execute();
        $subproject_detail = $result->fetchObject();
        $optiondata = array(
            "tbc",
            "lab_migration",
            "workshop",
            "self_workshop",
            "conference",
            "spoken_tutorial",
            "postal_campaigns"
        );
        $optionvalue = array(
            " ",
            "Textbook Companion",
            "Lab Migration",
            "Workshop",
            "Self Workshop",
            "Conference",
            "Spoken Tutorial",
            "Postal Campaigns"
        );
        $options = array();
        $options[0] = "--------------";
        $i = 0;
        foreach ($optiondata as $value) {
            $i++;
            if (($subproject_detail->$value) != 0) {
                $options[$i] = $optionvalue[$i];
            }
        }
        return $options;
    } else {
        $options[0] = "--------------";
        return $options;
    }
    
}


//This is used to populate dropdown third ie Status but it will fill if activity selected is either TBC or LM
function _ajax_example_get_third_dropdown_options($foss_sub_project)
{
    $options = array();
    if ($foss_sub_project != 0) {
        if ($foss_sub_project == 1) {
            $options[0] = "--------------";
            $options[1] = "Books in Progress";
            $options[2] = "Completed Books";
        } else if ($foss_sub_project == 2) {
            $options[0] = "--------------";
            $options[1] = "Labs in Progress";
            $options[2] = "Completed Labs";
        }
    }
    return $options;
}

//this is ajax callback method for first dropdown ie FOSS type
function ajax_foss_type_dependent_dropdown_callback($form, &$form_state)
{
    
    $foss_sub_project = $form_state['values']['foss_type'];
    $foss_sub_project_activities = $form_state['values']['foss_sub_project'];
    $startdate = $form_state['values']['start_date'];
    $enddate = $form_state['values']['end_date'];
    $enddate = trim($enddate);
    $startdate = trim($startdate);
    if ($startdate == "") {
        $startdate = '1960/01/01';
    } else {
        $startdate = $startdate;
    }
    if ($enddate == "") {
        $enddate = date("Y-m-d");
    } else {
        $enddate = $enddate;
    }
    if ($foss_sub_project == "") {
        $form['default_load']['#markup'] = '<ul class="nav nav-tabs">                       
                        <li class="active"><a data-toggle="tab" href="#tbctabledata">Textbook Companion</a></li>
                         <li><a data-toggle="tab" href="#lmtabledata">Lab Migration</a></li>
                          <li><a data-toggle="tab" href="#workshopdata">Workshop</a></li>
                           <li><a data-toggle="tab" href="#selfworkshopdata">Self Workshop</a></li>
                            <li><a data-toggle="tab" href="#conferencedata">Conference </a></li>
                            <li><a data-toggle="tab" href="#spokentutorialdata">Spoken Tutorial</a></li>
                      </ul>
   

    <div class="tab-content">

        <div id="tbctabledata"class="tab-pane fade in active">' . get_tabledata_TBC_or_LM("TBC", $startdate, $enddate) . '
        <div id="tbcchartdata" style="float:left;width:300px;height:300px;">' . drupal_render(getchart("TBC")). '</div>
        </div>
     
        <div id="lmtabledata" class="tab-pane fade ">' . get_tabledata_TBC_or_LM("LM", $startdate, $enddate) . '
        <div id="lmchartdata" style="float:left;width:350px;height:300px;">' . drupal_render(getchart("LM")) . '</div>
        </div>   

        <div id="workshopdata" class="tab-pane fade ">' . workshop_view_all(0, $startdate, $enddate) . '</div>
       
        <div id="selfworkshopdata" class="tab-pane fade">'.getselfworkshoplcount(0,$startdate,$enddate,"","").'</div>
        
        <div id="conferencedata" class="tab-pane fade ">' . conference_seminar_view_all(0, $startdate, $enddate) .'</div>

        <div id="spokentutorialdata" class="tab-pane fade ">'.spokentutorial_view_all("").'</div>
 </div>';
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        $form['load_city']['#markup'] = "";
        $commands[] = ajax_command_html("#load_city", "");
        $form['load_state']['#markup'] = "";
        $commands[] = ajax_command_html("#load_state", "");
        $form['load_country']['#markup'] = "";
        $commands[] = ajax_command_html("#load_country", "");
        $form['startdate']['#markup'] = "";
        $commands[] = ajax_command_html("#startdate", "");
        $form['enddate']['#markup'] = "";
        $commands[] = ajax_command_html("#enddate", "");
        $form['dropdown-third-replace']['#markup'] = "";
        $commands[] = ajax_command_html("#dropdown-third-replace", "");
        
    } else if ($foss_sub_project == '3') {
        $form['startdate']['#markup'] = "";
        $commands[] = ajax_command_html("#startdate", "");
        $form['enddate']['#markup'] = "";
        $commands[] = ajax_command_html("#enddate", "");
        $form['load_city']['#markup'] = "";
        $commands[] = ajax_command_html("#load_city", "");
        $form['load_state']['#markup'] = "";
        $commands[] = ajax_command_html("#load_state", "");
        $form['load_country']['#markup'] = "";
        $commands[] = ajax_command_html("#load_country", "");
    }
    
    else {
        $form['start_date']['#type'] = "date_popup";
        $form['start_date']['#title'] = t('From Date:');
        $form['start_date']['#date_label_position'] = 'within';
        $form['start_date']['#description'] = '';
        $form['start_date']['#default_value'] = '';
        $form['start_date']['#date_format'] = 'Y-m-d';
        $form['start_date']['#date_increment'] = 0;
        $form['start_date']['#date_year_range'] = '2011:+0';
        $form['start_date']['#datepicker_options'] = array(
            'maxDate' => 0
        );
        $commands[] = ajax_command_html("#startdate", drupal_render($form['start_date']));
        $form['end_date']['#type'] = "date_popup";
        $form['end_date']['#title'] = t('To Date:');
        $form['end_date']['#date_label_position'] = '';
        $form['end_date']['#description'] = '';
        $form['end_date']['#default_value'] = '';
        $form['end_date']['#date_format'] = 'Y-m-d';
        $form['end_date']['#date_increment'] = 0;
        $form['end_date']['#date_year_range'] = '2011:+0';
        $form['end_date']['#datepicker_options'] = array(
            'maxDate' => 0
        );
        $commands[] = ajax_command_html("#enddate", drupal_render($form['end_date']));
        
    }
    $form['foss_sub_project']['#default_value'] = "--------------";
    $form['foss_sub_project']['#options'] = get_activities_list($foss_sub_project);
    $commands[] = ajax_command_replace("#dropdown-second-replace", drupal_render($form['foss_sub_project']));
    
    
    if ($foss_sub_project != "" && ($foss_sub_project_activities == 1 || $foss_sub_project_activities == 2)) {
        $form['foss_sub_project_status']['#type'] = "select";
        $form['foss_sub_project_status']['#title'] = t("Status");
        $form['foss_sub_project_status']['#options'] = _ajax_example_get_third_dropdown_options($foss_sub_project_activities);
        $commands[] = ajax_command_html("#dropdown-third-replace", drupal_render($form['foss_sub_project_status']));
    } else {
        $form['dropdown-third-replace']['#markup'] = "";
        $commands[] = ajax_command_html("#dropdown-third-replace", "");
    }
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}

//this is ajax callback method for second dropdown ie Activities type
function ajax_foss_sub_project_dependent_dropdown_callback($form, &$form_state)
{
	$foss_project = $form_state['values']['foss_sub_project'];

    $foss_type = $form_state['values']['foss_type'];
    if ($foss_type != "" && $foss_project != 0 && $foss_type != 3) {

	if($foss_project==7){
	$form['load_city']['#markup'] = "";
        $commands[] = ajax_command_html("#load_city", "");
	$form['load_country']['#markup'] = "";
        $commands[] = ajax_command_html("#load_country", "");
	$form['load_state']['#markup'] = "";
        $commands[] = ajax_command_html("#load_state", "");
	
	}else if($foss_project==6){
	$form['load_city']['#markup'] = "";
        $commands[] = ajax_command_html("#load_city", "");
	$form['load_country']['#markup'] = "";
        $commands[] = ajax_command_html("#load_country", "");
	$form['load_state']['#markup'] = "";
        $commands[] = ajax_command_html("#load_state", "");
	$form['end_date']['#markup'] = "";
        $commands[] = ajax_command_html("#enddate", "");
	$form['start_date']['#markup'] = "";
        $commands[] = ajax_command_html("#startdate", "");

	}else{

        $form['countryname']['#type'] = "select";
        $form['countryname']['#title'] = t("Select Country");
        $form['countryname']['#option'] = get_country_list($foss_type, $foss_project);
        $form['countryname']['#prefix'] = "<div>";
        $form['countryname']['#suffix'] = "</div>";
        $commands[] = ajax_command_html("#load_country", drupal_render($form['countryname']));
        $country_value = $form_state['values']['countryname'];
        
        $form['statename']['#type'] = "select";
        $form['statename']['#title'] = t("State Name");
        $form['statename']['#options'] = get_state_list($foss_type, $foss_project, $country_value);
        $form['statename']['#prefix'] = "<div>";
        $form['statename']['#suffix'] = "</div>";
        $commands[] = ajax_command_html("#load_state", drupal_render($form['statename']));
        $state_value = $form_state['values']['statename'];
        
        $form['cityname']['#type'] = "select";
        $form['cityname']['#title'] = t("City Name");
        $form['cityname']['#options'] = get_city_list($foss_type, $foss_project, $country_value, $state_value);
        $form['cityname']['#prefix'] = "<div style='padding-right:15px;'>";
        $form['cityname']['#suffix'] = "</div>";
        $commands[] = ajax_command_html("#load_city", drupal_render($form['cityname']));
	
	}
        
    } else {
	 if ($foss_project == 3 || $foss_project == 4 || $foss_project == 5) {

	$form['start_date']['#type'] = "date_popup";
        $form['start_date']['#title'] = t('From Date:');
        $form['start_date']['#date_label_position'] = '';
        $form['start_date']['#description'] = '';
        $form['start_date']['#default_value'] = '';
        $form['start_date']['#date_format'] = 'Y-m-d';
        $form['start_date']['#date_increment'] = 0;
        $form['start_date']['#date_year_range'] = '2011:+0';
        $form['start_date']['#datepicker_options'] = array(
            'maxDate' => 0
        );
        $commands[] = ajax_command_html("#startdate", drupal_render($form['start_date']));
        $form['end_date']['#type'] = "date_popup";
        $form['end_date']['#title'] = t('To Date:');
        $form['end_date']['#date_label_position'] = '';
        $form['end_date']['#description'] = '';
        $form['end_date']['#default_value'] = '';
        $form['end_date']['#date_format'] = 'Y-m-d';
        $form['end_date']['#date_increment'] = 0;
        $form['end_date']['#date_year_range'] = '2011:+0';
        $form['end_date']['#datepicker_options'] = array(
            'maxDate' => 0
        );
        $commands[] = ajax_command_html("#enddate", drupal_render($form['end_date']));

	$form['countryname']['#type'] = "select";
       	$form['countryname']['#title'] = t("Select Country");
        $form['countryname']['#option'] = get_country_list($foss_type, $foss_project);
        $form['countryname']['#prefix'] = "<div>";
        $form['countryname']['#suffix'] = "</div>";
        $commands[] = ajax_command_html("#load_country", drupal_render($form['countryname']));

        $country_value = $form_state['values']['countryname'];
        
        $form['statename']['#type'] = "select";
        $form['statename']['#title'] = t("State Name");
        $form['statename']['#options'] = get_state_list($foss_type, $foss_project, $country_value);
        $form['statename']['#prefix'] = "<div>";
        $form['statename']['#suffix'] = "</div>";
        $commands[] = ajax_command_html("#load_state", drupal_render($form['statename']));
        $state_value = $form_state['values']['statename'];
        
        $form['cityname']['#type'] = "select";
        $form['cityname']['#title'] = t("City Name");
        $form['cityname']['#options'] = get_city_list($foss_type, $foss_project, $country_value, $state_value);
        $form['cityname']['#prefix'] = "<div style='padding-right:15px;'>";
        $form['cityname']['#suffix'] = "</div>";
        $commands[] = ajax_command_html("#load_city", drupal_render($form['cityname']));
	

	}else if($foss_project == 7){
	$form['start_date']['#type'] = "date_popup";
        $form['start_date']['#title'] = t('From Date:');
        $form['start_date']['#date_label_position'] = '';
        $form['start_date']['#description'] = '';
        $form['start_date']['#default_value'] = '';
        $form['start_date']['#date_format'] = 'Y-m-d';
        $form['start_date']['#date_increment'] = 0;
        $form['start_date']['#date_year_range'] = '2011:+0';
        $form['start_date']['#datepicker_options'] = array(
            'maxDate' => 0
        );
        $commands[] = ajax_command_html("#startdate", drupal_render($form['start_date']));
        $form['end_date']['#type'] = "date_popup";
        $form['end_date']['#title'] = t('To Date:');
        $form['end_date']['#date_label_position'] = '';
        $form['end_date']['#description'] = '';
        $form['end_date']['#default_value'] = '';
        $form['end_date']['#date_format'] = 'Y-m-d';
        $form['end_date']['#date_increment'] = 0;
        $form['end_date']['#date_year_range'] = '2011:+0';
        $form['end_date']['#datepicker_options'] = array(
            'maxDate' => 0
        );
        $commands[] = ajax_command_html("#enddate", drupal_render($form['end_date']));
	$form['load_city']['#markup'] = "";
        $commands[] = ajax_command_html("#load_city", "");
	$form['load_country']['#markup'] = "";
        $commands[] = ajax_command_html("#load_country", "");
	$form['load_state']['#markup'] = "";
        $commands[] = ajax_command_html("#load_state", "");
	}else if($foss_project == 6){
        $form['load_city']['#markup'] = "";
        $commands[] = ajax_command_html("#load_city", "");
	$form['load_country']['#markup'] = "";
        $commands[] = ajax_command_html("#load_country", "");
	$form['load_state']['#markup'] = "";
        $commands[] = ajax_command_html("#load_state", "");
	$form['end_date']['#markup'] = "";
        $commands[] = ajax_command_html("#enddate", "");
	$form['start_date']['#markup'] = "";
        $commands[] = ajax_command_html("#startdate", "");
	}
    }
    if ($foss_project == 1 || $foss_project == 2) {
        $form['foss_sub_project_status']['#type'] = "select";
        $form['foss_sub_project_status']['#title'] = t("Status");
        $form['foss_sub_project_status']['#options'] = _ajax_example_get_third_dropdown_options($foss_project);
        $commands[] = ajax_command_html("#dropdown-third-replace", drupal_render($form['foss_sub_project_status']));
    } else {
        $form['dropdown-third-replace']['#markup'] = "";
        $commands[] = ajax_command_html("#dropdown-third-replace", "");

    }
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}

//Form Submit
function ajax_example_submit_driven_callback($form, &$form_state)
{
    $foss_type = $form['foss_type']['#options'][$form_state['values']['foss_type']];
    $foss_sub_project = $form['foss_sub_project']['#options'][$form_state['values']['foss_sub_project']];
    $foss_sub_project_status = $form['foss_sub_project_status']['#options'][$form_state['values']['foss_sub_project_status']];
    $startdate = $form_state['values']['start_date'];
    $startdate = trim($startdate);
    $enddate = $form_state['values']['end_date'];
    $enddate = trim($enddate);
    $countryname = $form['countryname']['#options'][$form_state['values']['countryname']];
    $countryname = trim($countryname);
    $cityname = $form['cityname']['#options'][$form_state['values']['cityname']];
    $cityname = trim($cityname);
    $statename = $form['statename']['#options'][$form_state['values']['statename']];
    $statename = trim($statename);
    $link_flag = 0;
    
    if ($cityname == "--------------" || $cityname == "") {
        $cityname = "%";
    } else {
        $cityname = $cityname;
    }
    if ($statename == "--------------" || $statename == "") {
        $statename = "%";
    } else {
        $statename = $statename;
    }
    if ($countryname == "--------------" || $countryname == "") {
        $countryname = "%";
    } else {
        $countryname = $countryname;
    }
    if ($startdate == "") {
        $startdate = '1960-01-01';
    } else {
        $startdate = $startdate;
    }
    if ($enddate == "") {
        $enddate = date("Y-m-d");
    } else {
        $enddate = $enddate;
    }
    if ($foss_type == '--------------') {
        $form['default_load']['#markup'] = '<ul class="nav nav-tabs">                       
                        <li class="active"><a data-toggle="tab" href="#tbctabledata">Textbook Companion</a></li>
                         <li><a data-toggle="tab" href="#lmtabledata">Lab Migration</a></li>
                          <li><a data-toggle="tab" href="#workshopdata">Workshop</a></li>
                           <li><a data-toggle="tab" href="#selfworkshopdata">Self Workshop</a></li>
                            <li><a data-toggle="tab" href="#conferencedata">Conference </a></li>
                            <li><a data-toggle="tab" href="#spokentutorialdata">Spoken Tutorial</a></li>
                      </ul>
                      <div class="tab-content">

        <div id="tbctabledata"class="tab-pane fade in active">' . get_tabledata_TBC_or_LM("TBC", $startdate, $enddate) .'
        <div id="tbcchartdata" style="float:left;width:300px;height:300px;">' . drupal_render(getchart("TBC")) . '</div>
        </div>
     
        <div id="lmtabledata" class="tab-pane fade ">' . get_tabledata_TBC_or_LM("LM", $startdate, $enddate) .'
        <div id="lmchartdata" style="float:left;width:350px;height:300px;">' . drupal_render(getchart("LM")) . '</div>
        </div>   

        <div id="workshopdata" class="tab-pane fade ">' . workshop_view_all(0, $startdate, $enddate) .'</div>
       
        <div id="selfworkshopdata" class="tab-pane fade">' . getselfworkshoplcount(0,$startdate,$enddate,"","").'</div>
        
        <div id="conferencedata" class="tab-pane fade ">' . conference_seminar_view_all(0, "", "") .'</div>

        <div id="spokentutorialdata" class="tab-pane fade ">'.spokentutorial_view_all("").'</div>
 </div> 
					';
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
    } else if ($foss_type != '--------------' && (($foss_sub_project == "--------------") || ($foss_sub_project == "No Sub-Project Available"))) {
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        db_set_active('default'); // We need to call the main (drupal) db back      
        db_set_active(); // without the paramater means set back to the default for the site
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'tbc',
            'lab_migration',
            'workshop',
            'self_workshop',
            'conference'
        ));
        $query->fields('foss_type', array(
            'spoken_tutorial',
            'postal_campaigns'
        ));
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $subproject_detail = $result->fetchObject();
        $optiondata = array(
            "tbc",
            "lab_migration",
            "workshop",
            "self_workshop",
            "conference",
            "spoken_tutorial",
            "postal_campaigns"
        );
        $optionvalue = array(
            " ",
            "Textbook Companion",
            "Lab Migration",
            "Workshop",
            "Self Workshop",
            "Conference",
            "Spoken Tutorial",
            "Postal Campaigns"
        );
        $i = 0;
        $pagecontent = "";
     //    foreach ($optiondata as $value) {
     //        $i++;
     //        //$options[$i]=$optionvalue[$i];
     //         if (($subproject_detail->$value) != 0) {
     //            if ($value == "tbc") {
     //                $pagecontent .= "<div class='tab-preview'>
     //            <input type='radio' id='tab-1' name='tab-group-1' checked>
     //            <label for='tab-1'>Textbook Companion</label>
     //            <div class='tabcontent'><div id='tbctabledata'  
     //      style='float:left;width:280px;height:200px'>" . get_tabledata_selectedFoss_TBC_LM($foss_type, 'TBC', 'all', $startdate, $enddate, $countryname, $statename, $cityname, $link_flag) . "</div><div id='tbcchartdata' style='float:left;width:250px;height:200px;'>" . drupal_render(getchartforselectedProject($foss_type, "TBC", $startdate, $enddate, $countryname, $statename, $cityname)) . "</div></div></div>";
     //            } else if ($value == "lab_migration") {
     //                $pagecontent .= "<div class='tab-preview'>
     //        <input type='radio' id='tab-2' name='tab-group-1'>
     //              <label for='tab-2'>Lab Migration</label>
     //              <div class='tabcontent'>
     //        <div id='lmtabledata' style='float:left;width:280px;height:200px'>" . get_tabledata_selectedFoss_TBC_LM($foss_type, 'LM', "all", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag) . "</div>
     //        <div id='lmchartdata' style='float:left;width:250px;height:200px;'>" . drupal_render(getchartforselectedProject($foss_type, "LM", $startdate, $enddate, $countryname, $statename, $cityname)) . "<div></div></div></div></div>";
     //            } else if ($value == "workshop") {
                    
     //                $event_type = "Workshop";
     //                $pagecontent .= "<div class='tab-preview'>
     //            <input type='radio' id='tab-3' name='tab-group-1' >
     //            <label for='tab-3'>Workshop</label>
     //            <div class='tabcontent'><div>" . events_view_filter($foss_type, $event_type, $startdate, $enddate, $countryname, $statename, $cityname) . "</div></div></div>";
     //            } else if ($value == "self_workshop") {
     //                $pagecontent .= "<div class='tab-preview'>
     //            <input type='radio' id='tab-4' name='tab-group-1' >
     //            <label for='tab-4'>Self Workshop</label>
     //            <div class='tabcontent'><div id='selfworkshop'>".getselfworkshoplcount($foss_type,$startdate,$enddate, $statename, $cityname)."</div></div></div>";
     //            } else if ($value == "conference") {
     //                $event_type = "Conference";
     //                $pagecontent .= "<div class='tab-preview'>
     //            <input type='radio' id='tab-5' name='tab-group-1' >
     //            <label for='tab-5'>Conference</label>
     //            <div class='tabcontent'><div id='conference'>" . events_view_filter($foss_type, $event_type, $startdate, $enddate, $countryname, $statename, $cityname) . "</div></div></div>";
     //            } else if ($value == "spoken_tutorial") {
     //                $pagecontent .= "<div class='tab-preview'>
     //            <input type='radio' id='tab-6' name='tab-group-1' >
     //            <label for='tab-6'>Spoken Tutorial</label>
     //            <div class='tabcontent'>".spokentutorial_view_all($foss_type)."</div></div>";
     //            } 
            
          
     //        } else {
     //            if ($value == "tbc") {
     //                $pagecontent .= "<div class='tab-preview'>
     //   					<input type='radio' id='tab-1' name='tab-group-1' checked>
     //   					<label for='tab-1'>Textbook Companion</label>
     //  				 	<div class='tabcontent'><div id='tbctabledata' 	
					// >" . t("
					// <h5>TextBook Companion Project Not Present</h5>") . "</div></div></div>";
     //            } else if ($value == "lab_migration") {
     //                $pagecontent .= "<div class='tab-preview'>
					// 	<input type='radio' id='tab-2' name='tab-group-1'>
		   //     				<label for='tab-2'>Lab Migration</label>
     //  				 		<div class='tabcontent'>
					// 	<div id='lmtabledata'>" . t("
					// <h5>Lab Migration Project Not Present</h5>") . "<div></div></div></div></div>";
     //            } else if ($value == "workshop") {
     //                $pagecontent .= "<div class='tab-preview'>
     //   					<input type='radio' id='tab-3' name='tab-group-1' >
     //   					<label for='tab-3'>Workshop</label>
     //  				 	<div class='tabcontent'><div>" . t("
					// <h5> No Workshop were conducted </h5>") . "</div></div></div>";
     //            } else if ($value == "self_workshop") {
     //                $pagecontent .= "<div class='tab-preview'>
     //   					<input type='radio' id='tab-4' name='tab-group-1' >
     //   					<label for='tab-4'>Self Workshop</label>
     //  				 	<div class='tabcontent'>" . t("
					// <h5>No Self Workshop were conducted</h5>") . "</div></div>";
     //            } else if ($value == "conference") {
     //                $pagecontent .= "<div class='tab-preview'>
     //   					<input type='radio' id='tab-5' name='tab-group-1' >
     //   					<label for='tab-5'>Conference</label>
     //  				 	<div class='tabcontent'><div id='conference'>" . t("
					// <h5>No Conference were conducted</h5>") . "</div></div></div>";
     //            } else if ($value == "spoken_tutorial") {
     //                $pagecontent .= "<div class='tab-preview'>
     //   					<input type='radio' id='tab-6' name='tab-group-1' >
     //   					<label for='tab-6'>Spoken Tutorial</label>
     //  				 	<div class='tabcontent'>" . t("
					// <h5>Spoken Tutorial Not Present</h5>") . "</div></div>";
     //            } 
     //        }
     //    }
     //    $form['default_load']['#markup'] = $pagecontent;
         $form['default_load']['#markup'] = '<ul class="nav nav-tabs">                       
                        <li class="active"><a data-toggle="tab" href="#tbctabledata">Textbook Companion</a></li>
                         <li><a data-toggle="tab" href="#lmtabledata">Lab Migration</a></li>
                          <li><a data-toggle="tab" href="#workshopdata">Workshop</a></li>
                           <li><a data-toggle="tab" href="#selfworkshopdata">Self Workshop</a></li>
                            <li><a data-toggle="tab" href="#conferencedata">Conference </a></li>
                            <li><a data-toggle="tab" href="#spokentutorialdata">Spoken Tutorial</a></li>
                      </ul>
        <div class="tab-content">

            <div id="tbctabledata"class="tab-pane fade in active">' . get_tabledata_selectedFoss_TBC_LM($foss_type, 'TBC', 'all', $startdate, $enddate, $countryname, $statename, $cityname, $link_flag) .'
            <div id="tbcchartdata" style="float:left;width:300px;height:300px;">' . drupal_render(getchartforselectedProject($foss_type, "TBC", $startdate, $enddate, $countryname, $statename, $cityname)) . '</div>
            </div>
            <div id="lmtabledata" class="tab-pane fade ">' . get_tabledata_selectedFoss_TBC_LM($foss_type, 'LM', "all", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag) . '
        <div id="lmchartdata" style="float:left;width:350px;height:300px;">' .  drupal_render(getchartforselectedProject($foss_type, "LM", $startdate, $enddate, $countryname, $statename, $cityname)) .  '</div>
        </div>   
         <div id="workshopdata" class="tab-pane fade ">
         '. events_view_filter($foss_type, "Workshop", $startdate, $enddate, $countryname, $statename, $cityname) . '</div>
        <div id="selfworkshopdata" class="tab-pane fade">' . getselfworkshoplcount($foss_type,$startdate,$enddate, $statename, $cityname).'</div>
         <div id="conferencedata" class="tab-pane fade ">' . events_view_filter($foss_type,"Conference", $startdate, $enddate, $countryname, $statename, $cityname) .'</div>

        <div id="spokentutorialdata" class="tab-pane fade ">' .spokentutorial_view_all($foss_type).'</div>
        </div> 
                    ';
                  
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    } else if ($foss_type != '--------------' && $foss_sub_project == "Textbook Companion" && $foss_sub_project_status == "--------------") {
       
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        $pagecontent = "";
        $pagecontent .= '<ul class="nav nav-tabs">                       
                        <li class="active"><a data-toggle="tab" href="#tbctabledata">Textbook Companion</a></li></ul>
                        <div class="tab-content">

        <div id="tbctabledata"class="tab-pane fade in active">' . get_tabledata_selectedFoss_TBC_LM($foss_type, 'TBC', 'all', $startdate, $enddate, $countryname, $statename, $cityname, $link_flag) . '
        <div id="tbcchartdata" style="float:left;width:300px;height:300px;">' . drupal_render(getchartforselectedProject($foss_type, "TBC", $startdate, $enddate, $countryname, $statename, $cityname)) . '</div>
        </div></div>';
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    } else if ($foss_type != '--------------' && $foss_sub_project == "Lab Migration" && $foss_sub_project_status == "--------------") {
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        $pagecontent = "";
        $pagecontent .= '<ul class="nav nav-tabs">                       
                       <li><a data-toggle="tab" href="#lmtabledata">Lab Migration</a></li></ul>
                        <div class="tab-content">

       <div id="lmtabledata" class="tab-pane fade in active ">' . get_tabledata_selectedFoss_TBC_LM($foss_type, 'LM', "all", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag) . '
        <div id="lmchartdata" style="float:left;width:350px;height:300px;">' . drupal_render(getchartforselectedProject($foss_type, "LM", $startdate, $enddate, $countryname, $statename, $cityname)) . '</div>
        </div>   </div>';
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    } else if ($foss_type != '--------------' && $foss_sub_project == "Workshop") {
        
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        
        
        $pagecontent = "";
        $pagecontent .= '<ul class="nav nav-tabs">                       
                       <li><a data-toggle="tab" href="#workshopdata">Workshop</a></li></ul>
                        <div class="tab-content">

        <div id="workshopdata" class="tab-pane fade in active">' .events_view_filter($foss_type, $foss_sub_project, $startdate, $enddate, $countryname, $statename, $cityname) .'</div> </div>';
        
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    } else if ($foss_type != '--------------' && $foss_sub_project == "Self Workshop") {
        
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
       
        $pagecontent = "";
        $pagecontent .= '<ul class="nav nav-tabs">                       
                        <li><a data-toggle="tab" href="#selfworkshopdata">Self Workshop</a></li></ul>
                        <div class="tab-content">

       <div id="selfworkshopdata" class="tab-pane fade  in active">' .  getselfworkshoplcount($foss_type,$startdate,$enddate, $statename, $cityname).'</div></div>';
        
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    }else if ($foss_type != '--------------' && $foss_sub_project == "Conference") {
        
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        
        
        $pagecontent = "";
        $pagecontent .= '<ul class="nav nav-tabs">                       
                         <li><a data-toggle="tab" href="#conferencedata">Conference </a></li></ul>
                        <div class="tab-content">

      <div id="conferencedata" class="tab-pane fade  in active">' . events_view_filter($foss_type, $foss_sub_project, $startdate, $enddate, $countryname, $statename, $cityname).'</div></div>';
        
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    }else if ($foss_type != '--------------' && $foss_sub_project == "Spoken Tutorial") {
        
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        
        
        $pagecontent = "";
        $pagecontent .= '<ul class="nav nav-tabs">                       
                         <li><a data-toggle="tab" href="#spokentutorialdata">Spoken Tutorial</a></li></ul>
                        <div class="tab-content">

       <div id="spokentutorialdata" class="tab-pane fade  in active ">'.spokentutorial_view_all($foss_type) .'</div></div>';
        
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load'])); 
}

/* else if ($foss_type != '--------------' && $foss_sub_project == "Postal Campaigns") {
        
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        
        
        $pagecontent = "";
        $pagecontent .= "<div class='tab-preview'>
       					<input type='radio' id='tab-1' name='tab-group-1' >
       					<label for='tab-1'>Postal Campaigns</label>
      				 	<div class='tabcontent'>" . postalcampaign_view_all($foss_type, $startdate, $enddate) . "</div></div>";
        
        $form['default_load']['#markup'] = $pagecontent;
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    }*/

	else if ($foss_type != '--------------' && ($foss_sub_project == "Textbook Companion" || $foss_sub_project == "Lab Migration") && $foss_sub_project_status != "--------------") {
        if ($foss_sub_project == "Textbook Companion" && $foss_sub_project_status == "Completed Books") {
            $title = "Textbook Companion";
            $datatable = get_tabledata_selectedFoss_TBC_LM($foss_type, 'TBC', "completed", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag);
        } else if ($foss_sub_project == "Lab Migration" && $foss_sub_project_status == "Completed Labs") {
            $title = "Lab Migration";
            $datatable = get_tabledata_selectedFoss_TBC_LM($foss_type, 'LM', "completed", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag);
        } else if ($foss_sub_project == "Textbook Companion" && $foss_sub_project_status == "Books in Progress") {
            $title = "Textbook Companion";
            $datatable = get_tabledata_selectedFoss_TBC_LM($foss_type, 'TBC', "pending", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag);
        } else if ($foss_sub_project == "Lab Migration" && $foss_sub_project_status == "Labs in Progress") {
            $title = "Lab Migration";
            $datatable = get_tabledata_selectedFoss_TBC_LM($foss_type, 'LM', "pending", $startdate, $enddate, $countryname, $statename, $cityname, $link_flag);
        }
        $form['displaytext']['#markup'] = "Statistic of project : " . $foss_type;
        $commands[] = ajax_command_html("#displaytext", drupal_render($form['displaytext']));
        $form['default_load']['#markup'] = '<ul class="nav nav-tabs">     
        <li><a data-toggle="tab" href="#tabledata">' . $title . '</a></li></ul>
        <div class="tab-content">
        <div id="tabledata"class="tab-pane fade in active">'. $datatable . '</div>
        </div>';
        $commands[] = ajax_command_html("#default_load", drupal_render($form['default_load']));
        
    }
    
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}

//General Function to create table
function bootstrap_table_format($headers, $rows)
{
    $thead = "";
    $tbody = "";
    foreach ($headers as $header) {
        $thead .= "<th>{$header}</th>";
    }
    foreach ($rows as $row) {
        $tbody .= "<tr>";
        $i = 0;
        foreach ($row as $data) {
            $datalable = $headers[$i++];
            $tbody .= "<td data-label=" . $datalable . ">{$data}</td>";
        }
        $tbody .= "</tr>";
    }
    $table = "
			
            		<table class='table table-bordered table-hover'>
                	<thead>{$thead}</thead>
                	<tbody>{$tbody}</tbody>
            		</table>
			
        		";
    return $table;
}

//Returns table with count of TBC /LM
function get_tabledata_TBC_or_LM($sub_type, $startdate, $enddate)
{
    if ($enddate == "") {
        $enddate = date("Y-m-d");
    }
    $rows = array();
    $headers = array(
        " Project",
        "Completed",
        "In Progress"
    );
    if ($sub_type == 'TBC') {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'tbc'
        ));
        $query->fields('foss_type', array(
            'tbc_completed'
        ));
        $query->fields('foss_type', array(
            'tbc_pending'
        ));
        $query->condition('tbc', 1);
        $result = $query->execute();
        while ($foss_detail = $result->fetchObject()) {
            $foss_type = $foss_detail->foss_name;
            db_set_active($foss_type);
            if ($foss_detail->foss_name != 'Python') {
                db_set_active($foss_detail->foss_name); //Active other database
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                } else {
                    $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                }
                $completedbookcount = $query2->fetchObject()->book_count;
                if ($foss_detail->tbc_completed != "" && $foss_detail->tbc_completed != null) {
                    $clink = "<a href=" . $foss_detail->tbc_completed . " target='_blank'>" . $completedbookcount . "</a>";
                    $completedbookcount = $clink;
                }

                 /* For setting completion date for pending TBC and LM more */
			$pending_enddate = date('Y-m-d', strtotime("+5 months", strtotime( $enddate)));

                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':startdate' => $startdate,
                            ':enddate' => $pending_enddate
                        ));
                    } else {
                        $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1  AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':startdate' => $startdate,
                            ':enddate' => $pending_enddate
                        ));
                    }
                } else {
                    $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                        ':startdate' => $startdate,
                        ':enddate' => $pending_enddate
                    ));
                }
                $pendingbookcount = $query3->fetchObject()->book_count;
                if ($foss_detail->tbc_pending != "" && $foss_detail->tbc_pending != null) {
                    $plink = "<a href=" . $foss_detail->tbc_pending . " target='_blank'>" . $pendingbookcount . "</a>";
                    $pendingbookcount = $plink;
                }
                db_set_active('default'); // We need to call the main (drupal) db back      
                db_set_active(); // without the paramater means set back to the default for the site
                $item = array(
                    $foss_detail->foss_name,
                    $completedbookcount,
                    $pendingbookcount
                );
                array_push($rows, $item);
            } else {
                //For Python TBC
                db_set_active($foss_detail->foss_name); //Active other database
                $query5 = db_select('tbc_book');
                $query5->addExpression('count(*)', 'count');
                $query5->condition('approved', 1);
                $result5 = $query5->execute();
                $completedbookcount = $result5->fetchObject()->count;
                if ($foss_detail->tbc_completed != "" && $foss_detail->tbc_completed != null) {
                    $clink = "<a href=" . $foss_detail->tbc_completed . " target='_blank'>" . $completedbookcount . "</a>";
                    $completedbookcount = $clink;
                }
                $query6 = db_select('tbc_book');
                $query6->addExpression('count(*)', 'count');
                $query6->condition('approved', 1, '<>');
                $result6 = $query6->execute();
                $pendingbookcount = $result6->fetchObject()->count;
                if ($foss_detail->tbc_pending != "" && $foss_detail->tbc_pending != null) {
                    $plink = "<a href=" . $foss_detail->tbc_pending . " target='_blank'>" . $pendingbookcount . "</a>";
                    $pendingbookcount = $plink;
                }
                db_set_active('default'); // We need to call the main (drupal) db back      
                db_set_active(); // without the paramater means set back to the default for the site
                $item = array(
                    $foss_detail->foss_name,
                    $completedbookcount,
                    $pendingbookcount
                );
                array_push($rows, $item);
            }
        }
    } else {
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
        $query->fields('foss_type', array(
            'lab_migration'
        ));
        $query->fields('foss_type', array(
            'lm_completed'
        ));
        $query->fields('foss_type', array(
            'lm_pending'
        ));
        $query->condition('lab_migration', 1);
        $result = $query->execute();
        while ($foss_detail = $result->fetchObject()) {
            db_set_active($foss_detail->foss_name); //Active other database
            $query2 = db_select('lab_migration_proposal');
            $query2->addExpression('count(*)', 'count');
            $query2->condition('approval_status', 3);
            $result2 = $query2->execute();
            $completedlabcount = $result2->fetchObject()->count;
            if ($foss_detail->lm_completed != "" && $foss_detail->lm_completed != null) {
                $clink = "<a href=" . $foss_detail->lm_completed . " target='_blank'>" . $completedlabcount . "</a>";
                $completedlabcount = $clink;
            }
            $query3 = db_select('lab_migration_proposal');
            $query3->addExpression('count(*)', 'count');
            $query3->condition('approval_status', 1);
            $result3 = $query3->execute();
            $pendinglabcount = $result3->fetchObject()->count;
            if ($foss_detail->lm_pending != "" && $foss_detail->lm_pending != null) {
                $plink = "<a href=" . $foss_detail->lm_pending . " target='_blank'>" . $pendinglabcount . "</a>";
                $pendinglabcount = $plink;
            }
            $item = array(
                $foss_detail->foss_name,
                $completedlabcount,
                $pendinglabcount
            );
            array_push($rows, $item);
        }
    }
    db_set_active('default'); // We need to call the main (drupal) db back      
    db_set_active(); // without the paramater means set back to the default for the site
    $count = bootstrap_table_format($headers, $rows);
    return $count;
}

// This is used to return tables with count of TBC and LM but after applying all filters

function get_tabledata_selectedFoss_TBC_LM($foss_type, $sub_type, $status, $startdate, $enddate, $countryname, $statename, $cityname, $link_flag)
{
    
    //This is for sending values through URL as '%' is not allow through url
    
    if ($cityname == "%") {
        $city = "null";
    } else {
        $city = $cityname;
    }
    if ($statename == "%") {
        $state = "null";
    } else {
        $state = $statename;
    }
    if ($countryname == "%") {
        $country = "null";
    } else {
        $country = $countryname;
    }
    
    if ($status == "all") {
        $rows = array();
        if ($sub_type == 'TBC') {
            $headers = array(
                "Completed Book",
                "Book In Progress"
            );
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->fields('foss_type', array(
                'tbc'
            ));
            $query->fields('foss_type', array(
                'tbc_completed'
            ));
            $query->fields('foss_type', array(
                'tbc_pending'
            ));
            $query->condition('foss_name', $foss_type);
            $query->condition('tbc', 1);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            $foss_type = $foss_detail->foss_name;
            db_set_active($foss_type);
            if ($foss_detail->foss_name != 'Python') {
                db_set_active($foss_detail->foss_name); //Active other database
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                } else {
                    $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                }
                $completedbookcount = $query2->fetchObject()->book_count;
                if ($foss_detail->tbc_completed != "" && $foss_detail->tbc_completed != null && $link_flag != 1) {
                    
                    //$clink = "<a href=" . $foss_detail->tbc_completed . " target='_blank'>" . $completedbookcount . "</a>";
                    if ($completedbookcount != 0 || $completedbookcount != "0") {
                        $clink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "completed" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $completedbookcount . "</a>";
                        $completedbookcount = $clink;
                    } else {
                        $completedbookcount = $completedbookcount;
                    }
                    
                }
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    } else {
                        $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                } else {
                    $query3 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND po.city LIKE :city   AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                }
                $pendingbookcount = $query3->fetchObject()->book_count;
                if ($foss_detail->tbc_pending != "" && $foss_detail->tbc_pending != null && $link_flag != 1) {
                    //$plink = "<a href=" . $foss_detail->tbc_pending . " target='_blank'>" . $pendingbookcount . "</a>";
                    if ($pendingbookcount != 0 || $pendingbookcount != "0") {
                        $plink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "pending" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $pendingbookcount . "</a>";
                        $pendingbookcount = $plink;
                    } else {
                        $pendingbookcount = $pendingbookcount;
                    }
                    
                }
                //db_set_active('default'); // We need to call the main (drupal) db back      
                //db_set_active(); // without the paramater means set back to the default for the site
                $item = array(
                    $completedbookcount,
                    $pendingbookcount
                );
                array_push($rows, $item);
            } else {
                //For Python TBC
                db_set_active($foss_detail->foss_name); //Active other database
                $query5 = db_select('tbc_book');
                $query5->addExpression('count(*)', 'count');
                $query5->condition('approved', 1);
                //$query5->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $result5 = $query5->execute();
                $completedbookcount = $result5->fetchObject()->count;
                if ($foss_detail->tbc_completed != "" && $foss_detail->tbc_completed != null && $link_flag != 1) {
                    $clink = "<a href=" . $foss_detail->tbc_completed . " target='_blank'>" . $completedbookcount . "</a>";
                    $completedbookcount = $clink;
                }
                $query6 = db_select('tbc_book');
                $query6->addExpression('count(*)', 'count');
                $query6->condition('approved', 1, '<>');
                //$query6->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $result6 = $query6->execute();
                $pendingbookcount = $result6->fetchObject()->count;
                if ($foss_detail->tbc_pending != "" && $foss_detail->tbc_pending != null && $link_flag != 1) {
                    $plink = "<a href=" . $foss_detail->tbc_pending . " target='_blank'>" . $pendingbookcount . "</a>";
                    $pendingbookcount = $plink;
                }
                //db_set_active('default'); // We need to call the main (drupal) db back      
                //db_set_active(); // without the paramater means set back to the default for the site
                $item = array(
                    $completedbookcount,
                    $pendingbookcount
                );
                array_push($rows, $item);
            }
        } else {
            $headers = array(
                "Completed Labs",
                "Labs In Progress"
            );
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->fields('foss_type', array(
                'lab_migration'
            ));
            $query->fields('foss_type', array(
                'lm_completed'
            ));
            $query->fields('foss_type', array(
                'lm_pending'
            ));
            $query->condition('foss_name', $foss_type);
            $query->condition('lab_migration', 1);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            db_set_active($foss_detail->foss_name); //Active other database
            $query2 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=3 AND city 			LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
            $completedlabcount = $query2->fetchObject()->count;
            if ($foss_detail->lm_completed != "" && $foss_detail->lm_completed != null && $link_flag != 1) {
                //$clink = "<a href=" . $foss_detail->lm_completed . " target='_blank'>" . $completedlabcount . "</a>";
                if ($completedlabcount != 0 || $completedlabcount != "0") {
                    $clink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "completed" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $completedlabcount . "</a>";
                    $completedlabcount = $clink;
                } else {
                    $completedlabcount = $completedlabcount;
                }
            }
            $query3 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=1 AND city 			LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
            $pendinglabcount = $query3->fetchObject()->count;
            if ($foss_detail->lm_pending != "" && $foss_detail->lm_pending != null && $link_flag != 1) {
                //$plink = "<a href=" . $foss_detail->lm_pending . " target='_blank'>" . $pendinglabcount . "</a>";
                if ($pendinglabcount != 0 || $pendinglabcount != "0") {
                    $plink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "pending" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $pendinglabcount . "</a>";
                    $pendinglabcount = $plink;
                } else {
                    $pendinglabcount = $pendinglabcount;
                }
            }
            $item = array(
                $completedlabcount,
                $pendinglabcount
            );
            array_push($rows, $item);
        }
        db_set_active('default'); // We need to call the main (drupal) db back      
        db_set_active(); // without the paramater means set back to the default for the site
        $count = bootstrap_table_format($headers, $rows);
        return $count;
    } else if ($status == "completed") {
        $rows = array();
        if ($sub_type == 'TBC') {
            $headers = array(
                "Completed Book"
            );
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->fields('foss_type', array(
                'tbc'
            ));
            $query->fields('foss_type', array(
                'tbc_completed'
            ));
            $query->condition('foss_name', $foss_type);
            $query->condition('tbc', 1);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            $foss_type = $foss_detail->foss_name;
            db_set_active($foss_type);
            if ($foss_detail->foss_name != 'Python') {
                db_set_active($foss_detail->foss_name); //Active other database
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                } else {
                    $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status =3 AND pe.approval_status =1 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                }
                $completedbookcount = $query2->fetchObject()->book_count;
                if ($foss_detail->tbc_completed != "" && $foss_detail->tbc_completed != null && $link_flag != 1) {
                    //$clink = "<a href=" . $foss_detail->tbc_completed . " target='_blank'>" . $completedbookcount . "</a>";
                    if ($completedbookcount != 0 || $completedbookcount != "0") {
                        $clink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "completed" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $completedbookcount . "</a>";
                        $completedbookcount = $clink;
                    } else {
                        $completedbookcount = $completedbookcount;
                    }
                    
                }
                $item = array(
                    $completedbookcount
                );
                array_push($rows, $item);
            } else {
                //For Python TBC
                db_set_active($foss_detail->foss_name); //Active other database
                $query5 = db_select('tbc_book');
                $query5->addExpression('count(*)', 'count');
                $query5->condition('approved', 1);
                //$query5->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $result5 = $query5->execute();
                $completedbookcount = $result5->fetchObject()->count;
                if ($foss_detail->tbc_completed != "" && $foss_detail->tbc_completed != null && $link_flag != 1) {
                    $clink = "<a href=" . $foss_detail->tbc_completed . " target='_blank'>" . $completedbookcount . "</a>";
                    $completedbookcount = $clink;
                }
                $item = array(
                    $completedbookcount
                );
                array_push($rows, $item);
            }
        } else {
            $headers = array(
                "Completed Labs"
            );
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->fields('foss_type', array(
                'lab_migration'
            ));
            $query->fields('foss_type', array(
                'lm_completed'
            ));
            $query->condition('foss_name', $foss_type);
            $query->condition('lab_migration', 1);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            db_set_active($foss_detail->foss_name); //Active other database
            $query2 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=3 AND city 			LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
            $completedlabcount = $query2->fetchObject()->count;
            if ($foss_detail->lm_completed != "" && $foss_detail->lm_completed != null && $link_flag != 1) {
                //$clink = "<a href=" . $foss_detail->lm_completed . " target='_blank'>" . $completedlabcount . "</a>";
                if ($completedlabcount != "0" || $completedlabcount != 0) {
                    $clink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "completed" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $completedlabcount . "</a>";
                    $completedlabcount = $clink;
                } else {
                    $completedlabcount = $completedlabcount;
                }
                
            }
            $item = array(
                $completedlabcount
            );
            array_push($rows, $item);
        }
        db_set_active('default'); // We need to call the main (drupal) db back      
        db_set_active(); // without the paramater means set back to the default for the site
        $count = bootstrap_table_format($headers, $rows);
    } else if ($status == "pending") {
        $rows = array();
        if ($sub_type == 'TBC') {
            $headers = array(
                "Books in Progress"
            );
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->fields('foss_type', array(
                'tbc'
            ));
            $query->fields('foss_type', array(
                'tbc_pending'
            ));
            $query->condition('foss_name', $foss_type);
            $query->condition('tbc', 1);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            $foss_type = $foss_detail->foss_name;
            db_set_active($foss_type);
            if ($foss_detail->foss_name != 'Python') {
                db_set_active($foss_detail->foss_name); //Active other database
                if ($foss_detail->foss_name != 'eSim' && $foss_detail->foss_name != 'OpenFOAM' && $foss_detail->foss_name != 'OR-Tools') {
                    if ($foss_detail->foss_name != 'DWSIM') {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    } else {
                        $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND pe.category>0 AND po.city LIKE :city AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate ", array(
                            ':city' => $cityname,
                            ':state' => $statename,
                            ':country' => $countryname,
                            ':startdate' => $startdate,
                            ':enddate' => $enddate
                        ));
                    }
                } else {
                    $query2 = db_query("SELECT COUNT( pe.book ) AS book_count FROM textbook_companion_preference pe LEFT JOIN textbook_companion_proposal po ON pe.proposal_id = po.id WHERE po.proposal_status !=3 AND pe.approval_status =1 AND po.city LIKE :city  AND po.state LIKE :state AND po.country LIKE :country AND FROM_UNIXTIME(po.completion_date) >= :startdate AND FROM_UNIXTIME(po.completion_date) <= :enddate", array(
                        ':city' => $cityname,
                        ':state' => $statename,
                        ':country' => $countryname,
                        ':startdate' => $startdate,
                        ':enddate' => $enddate
                    ));
                }
                $pendingbookcount = $query2->fetchObject()->book_count;
                if ($foss_detail->tbc_pending != "" && $foss_detail->tbc_pending != null && $link_flag != 1) {
                    //$plink = "<a href=" . $foss_detail->tbc_pending . " target='_blank'>" . $pendingbookcount . "</a>";
                    if ($pendingbookcount != "0" || $pendingbookcount != 0) {
                        $plink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "pending" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $pendingbookcount . "</a>";
                        $pendingbookcount = $plink;
                    } else {
                        $pendingbookcount = $pendingbookcount;
                    }
                }
                $item = array(
                    $pendingbookcount
                );
                array_push($rows, $item);
            } else {
                //For Python TBC
                db_set_active($foss_detail->foss_name); //Active other database
                $query5 = db_select('tbc_book');
                $query5->addExpression('count(*)', 'count');
                $query5->condition('approved', 1, '<>');
                //$query5->condition('author', '%'.$extrafilter.'%', 'LIKE');
                $result5 = $query5->execute();
                $pendingbookcount = $result5->fetchObject()->count;
                if ($foss_detail->tbc_pending != "" && $foss_detail->tbc_pending != null && $link_flag != 1) {
                    $plink = "<a href=" . $foss_detail->tbc_pending . " target='_blank'>" . $pendingbookcount . "</a>";
                    $pendingbookcount = $plink;
                }
                $item = array(
                    $pendingbookcount
                );
                array_push($rows, $item);
            }
        } else {
            $headers = array(
                "Labs in Progress"
            );
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->fields('foss_type', array(
                'lab_migration'
            ));
            $query->fields('foss_type', array(
                'lm_pending'
            ));
            $query->condition('foss_name', $foss_type);
            $query->condition('lab_migration', 1);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            db_set_active($foss_detail->foss_name); //Active other database
            $query2 = db_query("SELECT COUNT(*) AS count from lab_migration_proposal WHERE approval_status=1 AND city 			LIKE :city AND state LIKE :state AND country LIKE :country AND FROM_UNIXTIME(approval_date) >=:startdate AND FROM_UNIXTIME(approval_date) <=:enddate", array(
                ':city' => $cityname,
                ':state' => $statename,
                ':country' => $countryname,
                ':startdate' => $startdate,
                ':enddate' => $enddate
            ));
            $pendinglabcount = $query2->fetchObject()->count;
            if ($foss_detail->lm_pending != "" && $foss_detail->lm_pending != null && $link_flag != 1) {
                //$plink = "<a href=" . $foss_detail->lm_pending . " target='_blank'>" . $pendinglabcount . "</a>";
                if ($pendinglabcount != "0" || $pendinglabcount != 0) {
                    $plink = "<a href=" . $GLOBALS['base_url'] . "/get-list/" . $foss_type . "/" . $sub_type . "/" . "pending" . "/" . $startdate . "/" . $enddate . "/" . $country . "/" . $state . "/" . $city . " target='_blank'>" . $pendinglabcount . "</a>";
                    $pendinglabcount = $plink;
                } else {
                    $pendinglabcount = $pendinglabcount;
                }
                
            }
            $item = array(
                $pendinglabcount
            );
            array_push($rows, $item);
        }
        db_set_active('default'); // We need to call the main (drupal) db back      
        db_set_active(); // without the paramater means set back to the default for the site
        $count = bootstrap_table_format($headers, $rows);
    }
    return $count;
}

//Get the list of State according to country selection .It is ajax method for state
function ajax_state_list_dependent_dropdown_callback($form, &$form_state)
{
    $foss_type = $form['foss_type']['#options'][$form_state['values']['foss_type']];
    $foss_sub_project = $form['foss_sub_project']['#options'][$form_state['values']['foss_sub_project']];
    $country = $form['countryname']['#options'][$form_state['values']['countryname']];

    $options = array();
    if($country!="--------------"){
    if ($foss_sub_project == "Textbook Companion") {
        $sub_project = "textbook_companion_proposal";
        
        db_set_active($foss_type); //Active other database
        
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country
       ";
        $args = array(
            ":country" => $country
        );
        $result = db_query($query, $args);
        while ($state_detail = $result->fetchObject()) {
            $options[$state_detail->state] = $state_detail->state;
        }
        
    } else if ($foss_sub_project == "Lab Migration") {
        $sub_project = "lab_migration_proposal";
        
        db_set_active($foss_type); //Active other database
        //$options    = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country
       ";
        $args = array(
            ":country" => $country
        );
        $result = db_query($query, $args);
        while ($state_detail = $result->fetchObject()) {
            $options[$state_detail->state] = $state_detail->state;
        }
        
    } else if ($foss_sub_project == "Workshop") {
        $sub_project = "workshop";
        $foss_type_db = 'default';
        
        db_set_active($foss_type_db); //Active other database
        //$options    = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and foss_name = :foss_name
       ";
        $args = array(
            ":country" => $country,
            ":foss_name" => $foss_type
        );
        $result = db_query($query, $args);
        while ($state_detail = $result->fetchObject()) {
            $options[$state_detail->state] = $state_detail->state;
        }
    }else if ($foss_sub_project == "Conference") {
        $sub_project = "workshop";
        $foss_type_db = 'default';
        
        db_set_active($foss_type_db); //Active other database
        //$options    = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and foss_name = :foss_name
       ";
        $args = array(
            ":country" => $country,
            ":foss_name" => $foss_type
        );
        $result = db_query($query, $args);
        while ($state_detail = $result->fetchObject()) {
            $options[$state_detail->state] = $state_detail->state;
        }
    }
else if ($foss_sub_project == "Self Workshop"){


        //Get self workshop foss number for spoken
		
	$query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
	$query->fields('foss_type', array(
            'foss_selfworkshop_no'
        ));
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
	$foss_selfworkshop_no=$foss_detail->foss_selfworkshop_no;

	    db_set_active('selfworkshop'); //Active other database
            $options[0] = '--------------';
            $query = "SELECT distinct (s.name) as state FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id )
       ";
            $args = array(
                ":foss_id" => $foss_selfworkshop_no,
            );
            $result = db_query($query, $args);

            while ($state_detail = $result->fetchObject()) {
                $options[$state_detail->state] = $state_detail->state;
		
            }

    }
    }else{
	 $options[0] = '--------------';
	}
    
    db_set_active('default'); // We need to call the main (drupal) db back      
    
    $form['statename']['#options'] = $options;
    $commands[] = ajax_command_replace("#load_state", drupal_render($form['statename']));
    
    $optionscity = array();
    $optionscity[0] = '--------------';
    $form['cityname']['#options'] = $optionscity;
    $commands[] = ajax_command_replace("#load_city", drupal_render($form['cityname']));
    
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}

//Get the list of City according to country selection .It is ajax method for state
function ajax_city_list_dependent_dropdown_callback($form, &$form_state)
{
    $foss_type = $form['foss_type']['#options'][$form_state['values']['foss_type']];
    $foss_sub_project = $form['foss_sub_project']['#options'][$form_state['values']['foss_sub_project']];
    $country = $form['countryname']['#options'][$form_state['values']['countryname']];
    $state = $form['statename']['#options'][$form_state['values']['statename']];
    if ($foss_sub_project == "Textbook Companion") {
        $sub_project = "textbook_companion_proposal";
        
        db_set_active($foss_type); //Active other database
        $options = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state
       ";
        $args = array(
            ":country" => $country,
            ":state" => $state
        );
        $result = db_query($query, $args);
        while ($city_detail = $result->fetchObject()) {
            $options[$city_detail->city] = $city_detail->city;
        }
        
    } else if ($foss_sub_project == "Lab Migration") {
        $sub_project = "lab_migration_proposal";
        
        db_set_active($foss_type); //Active other database
        $options = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state
       ";
        $args = array(
            ":country" => $country,
            ":state" => $state
        );
        $result = db_query($query, $args);
        while ($city_detail = $result->fetchObject()) {
            $options[$city_detail->city] = $city_detail->city;
        }
        
    } else if ($foss_sub_project == "Workshop") {
        $sub_project = "workshop";
        $foss_type_db = 'default';
        
        db_set_active($foss_type_db); //Active other database
        $options = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state and foss_name = :foss_name
       ";
        $args = array(
            ":country" => $country,
            ":state" => $state,
            ":foss_name" => $foss_type
        );
        $result = db_query($query, $args);
        while ($city_detail = $result->fetchObject()) {
            $options[$city_detail->city] = $city_detail->city;
        }
    }else if ($foss_sub_project == "Conference") {
        $sub_project = "workshop";
        $foss_type_db = 'default';
        
        db_set_active($foss_type_db); //Active other database
        $options = array();
        $options[0] = '--------------';
        $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state and foss_name = :foss_name
       ";
        $args = array(
            ":country" => $country,
            ":state" => $state,
            ":foss_name" => $foss_type
        );
        $result = db_query($query, $args);
        while ($city_detail = $result->fetchObject()) {
            $options[$city_detail->city] = $city_detail->city;
        }
    }else if ($foss_sub_project == "Self Workshop"){

	//Get self workshop foss number for spoken
		
	$query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
	$query->fields('foss_type', array(
            'foss_selfworkshop_no'
        ));
        $query->condition('foss_name', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
	$foss_selfworkshop_no=$foss_detail->foss_selfworkshop_no;
	db_set_active('selfworkshop'); //Active other database
        $options[0] = '--------------';

	$query="SELECT distinct (c.name) as city FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id ) and s.name LIKE :statename";

	$args = array(
                ":foss_id" => $foss_selfworkshop_no,
		":statename"=>$state,
            );
            $result = db_query($query, $args);

            while ($city_detail = $result->fetchObject()) {
                $options[$city_detail->city] = $city_detail->city;
		
            }

     }
    
    
    db_set_active('default'); // We need to call the main (drupal) db back      
    
    $form['cityname']['#options'] = $options;
    $commands[] = ajax_command_replace("#load_city", drupal_render($form['cityname']));
    
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}

//Get list of country
function get_country_list($foss_type, $foss_sub_project)
{
    
     if ($foss_type!= 0) {
        if ($foss_sub_project != 0) {
            
            $query = db_select('foss_type');
            $query->fields('foss_type', array(
                'id'
            ));
            $query->fields('foss_type', array(
                'foss_name'
            ));
            $query->condition('id', $foss_type);
            $result = $query->execute();
            $foss_detail = $result->fetchObject();
            $foss_type_name = $foss_detail->foss_name;
            $options = array();
            
            if ($foss_sub_project == 1 && $foss_type!=3) {
		
                $sub_project = "textbook_companion_proposal";
                
                db_set_active($foss_type_name); //Active other database
                
                $options[0] = '--------------';
                $query = "
       SELECT Distinct(country) FROM " . $sub_project . "
       ";
                
                $result = db_query($query);
                while ($country_detail = $result->fetchObject()) {
                    $options[$country_detail->country] = $country_detail->country;
                }
		
                
            } else if ($foss_sub_project == 2 && $foss_type!=3) {
                $sub_project = "lab_migration_proposal";
                
                db_set_active($foss_type_name); //Active other database
                // $options    = array();
                $options[0] = '--------------';
                $query = "
       SELECT Distinct(country) FROM " . $sub_project . "
       ";
                
                $result = db_query($query);
                while ($country_detail = $result->fetchObject()) {
                    $options[$country_detail->country] = $country_detail->country;
                }
                
            } else if ($foss_sub_project == 3||$foss_sub_project == 5) {
                $sub_project = "workshop";
                $foss_type_db = 'default';
                
                db_set_active($foss_type_db); //Active other database
                //$options    = array();
                $options[0] = '--------------';
                $query = "
       SELECT Distinct(country) FROM " . $sub_project . "
       WHERE foss_name = :foss_name
       ";
                $args = array(
                    ":foss_name" => $foss_type_name
                );
                $result = db_query($query, $args);
                while ($country_detail = $result->fetchObject()) {
                    $options[$country_detail->country] = $country_detail->country;
                }
                
            }else if ($foss_sub_project == 4) {
                
                $options[0] = '--------------';
                $options[1] = 'India';
                              
            }
            
            
            db_set_active('default'); // We need to call the main (drupal) db back 
            
            
            return $options;
        } else {
            $options = array();
            $options[0] = '--------------';
            return $options;
        }
    }
}

//Get list of state
function get_state_list($foss_type, $foss_sub_project, $country)
{
   if (($foss_type != 0) && $foss_sub_project != 0 && $country != "") {
        
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
	$query->fields('foss_type', array(
            'foss_selfworkshop_no'
        ));
        $query->condition('id', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
        $foss_type_name = $foss_detail->foss_name;
	$foss_selfworkshop_no=$foss_detail->foss_selfworkshop_no;
        $options = array();
       
        if ($foss_sub_project == 1 && $foss_type!=3) {
		$options = array();
            $sub_project = "textbook_companion_proposal";
            
            db_set_active($foss_type_name); //Active other database
            
            $options[0] = '--------------';
            $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country
       ";
            $args = array(
                ":country" => $country
            );
            $result = db_query($query, $args);
            while ($state_detail = $result->fetchObject()) {
                $options[$state_detail->state] = $state_detail->state;
            }
            
        } else if ($foss_sub_project == 2 && $foss_type!=3) {
            $sub_project = "lab_migration_proposal";
            $options = array();
            db_set_active($foss_type_name); //Active other database
           
            $options[0] = '--------------';
            $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country
       ";
            $args = array(
                ":country" => $country
            );
            $result = db_query($query, $args);
            while ($state_detail = $result->fetchObject()) {
                $options[$state_detail->state] = $state_detail->state;
            }
            
        } else if ($foss_sub_project == 3||$foss_sub_project == 5) {
$options = array();
            $sub_project = "workshop";
            $foss_type_db = 'default';
            
            db_set_active($foss_type_db); //Active other database
           
            $options[0] = '--------------';
            $query = "SELECT * FROM " . $sub_project . "
       WHERE country = :country and foss_name = :foss_name
       ";
            $args = array(
                ":country" => $country,
                ":foss_name" => $foss_type_name
            );
            $result = db_query($query, $args);
            while ($state_detail = $result->fetchObject()) {
                $options[$state_detail->state] = $state_detail->state;
            }
            
        } else if ($foss_sub_project == 4) {
           
            db_set_active('selfworkshop'); //Active other database
            $options[0] = '--------------';
            $query = "SELECT distinct (s.name) as state FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id )
       ";
            $args = array(
                ":foss_id" => $foss_selfworkshop_no,
            );
            $result = db_query($query, $args);

            while ($state_detail = $result->fetchObject()) {
                $options[$state_detail->state] = $state_detail->state;

            }
           
        }
        db_set_active('default'); // We need to call the main (drupal) db back 
        
        return $options;
    } else {
        $options = array();
        $options[0] = '--------------';
        return $options;
    }
}
//Get List of City
function get_city_list($foss_type, $foss_sub_project, $country, $state)
{
    
     if (($foss_type != 0 ) && $foss_sub_project != 0 && $country != "" && $state != "") {
        
        $query = db_select('foss_type');
        $query->fields('foss_type', array(
            'id'
        ));
        $query->fields('foss_type', array(
            'foss_name'
        ));
	$query->fields('foss_type', array(
            'foss_selfworkshop_no'
        ));
        $query->condition('id', $foss_type);
        $result = $query->execute();
        $foss_detail = $result->fetchObject();
        $foss_type = $foss_detail->foss_name;
        $foss_selfworkshop_no=$foss_detail->foss_selfworkshop_no;
        if ($foss_sub_project == 1  && $foss_type!=3) {
            $sub_project = "textbook_companion_proposal";
            db_set_active($foss_type); //Active other database
            $options = array();
            $options[0] = '--------------';
            $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state
       ";
            $args = array(
                ":country" => $country,
                ":state" => $state
            );
            $result = db_query($query, $args);
            while ($city_detail = $result->fetchObject()) {
                $options[$city_detail->city] = $city_detail->city;
            }
            
        } else if ($foss_sub_project == 2  && $foss_type!=3) {
            $sub_project = "lab_migration_proposal";
            db_set_active($foss_type); //Active other database
            $options = array();
            $options[0] = '--------------';
            $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state
       ";
            $args = array(
                ":country" => $country,
                ":state" => $state
            );
            $result = db_query($query, $args);
            while ($city_detail = $result->fetchObject()) {
                $options[$city_detail->city] = $city_detail->city;
            }
            
            
        } else if ($foss_sub_project == 3||$foss_sub_project == 5) {
            $sub_project = "workshop";
            $foss_type_db = 'default';
            
            db_set_active($foss_type_db); //Active other database
            $options = array();
            $options[0] = '--------------';
            $query = "
       SELECT * FROM " . $sub_project . "
       WHERE country = :country and state = :state and foss_name = :foss_name
       ";
            $args = array(
                ":country" => $country,
                ":state" => $state,
                ":foss_name" => $foss_type
            );
            $result = db_query($query, $args);
            while ($city_detail = $result->fetchObject()) {
                $options[$city_detail->city] = $city_detail->city;
            }
        } else if ($foss_sub_project == 4) {

		
	db_set_active('selfworkshop'); //Active other database
        $options[0] = '--------------';

	$query="SELECT distinct (c.name) as city FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id ) and s.name LIKE :statename";

	$args = array(
                ":foss_id" => $foss_selfworkshop_no,
		":statename"=>$state,
            );
            $result = db_query($query, $args);

            while ($city_detail = $result->fetchObject()) {
                $options[$city_detail->city] = $city_detail->city;
		
            }
	
	}
        
        
        db_set_active('default'); // We need to call the main (drupal) db back 
        
        
        return $options;
    } else {
        $options = array();
        $options[0] = '--------------';
        return $options;
    }
}

/*Add Event form (Workshop and seminar aling with photos testimonials and speakers)
	Same form is used for editing existing events
*/
function workshop_add_form($form, &$form_state, $workshop_id = 0, $no_js_use = FALSE)
{
 
$query = "
       SELECT * FROM workshop
       WHERE w_id = :workshop_id
       ";
    $args = array(
        ":workshop_id" => $workshop_id
    );
    $result = db_query($query, $args);
    $row = $result->fetchObject();
    $form = array();
   
    if ($workshop_id == 0) {
        $form['foss_select'] = array(
            '#type' => 'select',
            '#title' => t('Select FOSS under which event took place '),
            '#options' => get_first_dropdown_options_foss_name(),
            '#default_value' => '',
            "#required" => TRUE
        ); 
        $form['type'] = array(
            '#type' => 'select',
            '#title' => t('Selected Type'),
            '#options' => array(
                'Workshop' => t('Workshop'),
                'Conference' => t('Conference'),
                'Events' => t('Events')
            ),
            '#default_value' => '',
            "#required" => TRUE
        );
        $form["name"] = array(
            "#type" => "textfield",
            "#title" => "Name",
            "#default_value" => '',
            "#required" => TRUE
        );
        $form['start_date'] = array(
            '#type' => 'date_popup',
            '#title' => t('Select Start Date Of Event :'),
            '#date_increment' => 15,
            '#date_year_range' => '2008:+3',
            '#default_value' => '',
            '#date_format' => 'Y-m-d',
            '#attributes' => array(
                'autocomplete' => 'off',
                'readonly' => 'readonly'
            ),
            "#required" => TRUE
        );
        $form['end_date'] = array(
            '#type' => 'date_popup',
            '#title' => t('Select End Date Of Event:'),
            '#date_increment' => 15,
            '#date_year_range' => '2008:+3',
            '#default_value' => '',
            '#date_format' => 'Y-m-d',
            '#attributes' => array(
                'autocomplete' => 'off',
                'readonly' => 'readonly'
            ),
            "#required" => TRUE
        );
        $form["venue"] = array(
            "#type" => "textfield",
            "#title" => "Venue",
            "#default_value" => '',
            "#required" => TRUE
        );
        $form["street"] = array(
            "#type" => "textfield",
            "#title" => "Street",
            "#default_value" => '',
            "#required" => TRUE
        );
        $form['country'] = array(
            '#type' => 'select',
            '#title' => t('Country'),
            '#options' => array(
                'India' => 'India',
                'Others' => 'Others'
            ),
            '#required' => TRUE,
            '#tree' => TRUE,
            '#validated' => TRUE
        );
        $form['other_country'] = array(
            '#type' => 'textfield',
            '#title' => t('Other than India'),
            '#size' => 100,
            '#attributes' => array(
                'placeholder' => t('Enter your country name')
            ),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'Others'
                    )
                )
            )
        );
        $form['other_state'] = array(
            '#type' => 'textfield',
            '#title' => t('State other than India'),
            '#size' => 100,
            '#attributes' => array(
                'placeholder' => t('Enter your state/region name')
            ),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'Others'
                    )
                )
            )
        );
        $form['other_city'] = array(
            '#type' => 'textfield',
            '#title' => t('City other than India'),
            '#size' => 100,
            '#attributes' => array(
                'placeholder' => t('Enter your city name')
            ),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'Others'
                    )
                )
            )
        );
        $form['all_state'] = array(
            '#type' => 'select',
            '#title' => t('State'),
            '#options' => _list_of_states(),
            '#validated' => TRUE,
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'India'
                    )
                )
            )
        );
        $form['city'] = array(
            '#type' => 'select',
            '#title' => t('City'),
            '#options' => _list_of_cities(),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'India'
                    )
                )
            )
        );
        $form['pincode'] = array(
            '#type' => 'textfield',
            '#title' => t('Pincode'),
            '#size' => 30,
            '#maxlength' => 6,
            '#required' => False,
            '#attributes' => array(
                'placeholder' => 'Enter pincode....'
            )
        );
        
        $form["no_of_participant"] = array(
            "#type" => "textfield",
            "#title" => "Number Of Participants",
            "#default_value" => '',
            "#required" => TRUE
        );
        $form["body"] = array(
            "#type" => "textarea",
            "#title" => "Details",
            "#default_value" => '',
            "#required" => TRUE
        );
        $form['speakers_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            '#prefix' => '<div id="speakers-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if (empty($form_state['num_speakers'])) {
            $form_state['num_speakers'] = 1;
        }
        $temp = 0;
        for ($i = 0; $i < $form_state['num_speakers']; $i++) {
            $temp1 = $i;
            $form['speakers_fieldset'][$i]["s_text"] = array(
                "#type" => "item",
                "#markup" => "<h4><label>Speaker : " . ($temp1 + 1) . "</label></h4>"
            );
            $form['speakers_fieldset'][$i]["speakername"] = array(
                "#type" => "textfield",
                "#title" => "Name",
                "#default_value" => ""
            );
            $form['speakers_fieldset'][$i]["institute"] = array(
                "#type" => "textfield",
                "#title" => "Institute",
                "#default_value" => ""
            );
            $form['speakers_fieldset'][$i]["place"] = array(
                "#type" => "textfield",
                "#title" => "Place",
                "#default_value" => ""
            );
        }
        $form["speakers_count"] = array(
            "#type" => "hidden",
            "#value" => $temp1
        );
        $form['speakers_fieldset']['add_speakers'] = array(
            '#type' => 'submit',
            '#value' => t('Add Speaker'),
            '#limit_validation_errors' => array(),
            '#submit' => array(
                'speakers_add_more_add_one'
            ),
            '#ajax' => array(
                'callback' => 'speakers_add_more_callback',
                'wrapper' => 'speakers-fieldset-wrapper'
            )
        );
        if ($form_state['num_speakers'] > 1) {
            $form['speakers_fieldset']['remove_speakers'] = array(
                '#type' => 'submit',
                '#value' => t('Remove Speaker'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'speakers_add_more_remove_one'
                ),
                '#ajax' => array(
                    'callback' => 'speakers_add_more_callback',
                    'wrapper' => 'speakers-fieldset-wrapper'
                )
            );
        }
        if ($no_js_use) {
            if (!empty($form['speakers_fieldset']['remove_speakers']['#ajax'])) {
                unset($form['speakers_fieldset']['remove_speakers']['#ajax']);
            }
            unset($form['speakers_fieldset']['add_speakers']['#ajax']);
        }
        $form['testimonial_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            '#prefix' => '<div id="testimonial-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if (empty($form_state['num_testimonial'])) {
            $form_state['num_testimonial'] = 1;
        }
        $temp = 0;
        for ($i = 0; $i < $form_state['num_testimonial']; $i++) {
            $temp = $i;
            $form['testimonial_fieldset'][$i]["t_text"] = array(
                "#type" => "item",
                "#markup" => "<h4><label>Testimonial : " . ($temp + 1) . "</label></h4>"
            );
            $form['testimonial_fieldset'][$i]["t_name"] = array(
                "#type" => "textfield",
                "#title" => "Name",
                "#default_value" => ""
            );
            $form['testimonial_fieldset'][$i]["t_department"] = array(
                "#type" => "textfield",
                "#title" => "Department",
                "#default_value" => ""
            );
            $form['testimonial_fieldset'][$i]["t_university"] = array(
                "#type" => "textfield",
                "#title" => "University",
                "#default_value" => ""
            );
            $form['testimonial_fieldset'][$i]["t_body"] = array(
                "#type" => "textarea",
                "#title" => "Testimonial"
            );
        }
        $form["testimonial_count"] = array(
            "#type" => "hidden",
            "#value" => $temp
        );
        $form['testimonial_fieldset']['add_testimonial'] = array(
            '#type' => 'submit',
            '#value' => t('Add Testimonial'),
            '#limit_validation_errors' => array(),
            '#submit' => array(
                'testimonial_add_more_add_one'
            ),
            '#ajax' => array(
                'callback' => 'testimonial_add_more_callback',
                'wrapper' => 'testimonial-fieldset-wrapper'
            )
        );
        if ($form_state['num_testimonial'] > 1) {
            $form['testimonial_fieldset']['remove_testimonial'] = array(
                '#type' => 'submit',
                '#value' => t('Remove Testimonial'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'testimonial_add_more_remove_one'
                ),
                '#ajax' => array(
                    'callback' => 'testimonial_add_more_callback',
                    'wrapper' => 'testimonial-fieldset-wrapper'
                )
            );
        }
        if ($no_js_use) {
            if (!empty($form['testimonial_fieldset']['remove_testimonial']['#ajax'])) {
                unset($form['testimonial_fieldset']['remove_testimonial']['#ajax']);
            }
            unset($form['testimonial_fieldset']['add_testimonial']['#ajax']);
        }
        $form['names_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            '#prefix' => '<div id="names-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if (empty($form_state['num_names'])) {
            $form_state['num_names'] = 1;
        }
        for ($i = 0; $i < $form_state['num_names']; $i++) {
            $form['names_fieldset'][$i]['name'] = array(
                '#title' => t('Add Event Image'),
                '#type' => 'file',
                '#weight' => '5',
                '#description' => t('Upload an image'),
                // We need this to know which file element this is.
                // By default drupal would name all as files[names_fieldset]
                '#name' => 'files[names_fieldset_' . $i . '_name]'
            );
        }
        $form['names_fieldset']['add_name'] = array(
            '#type' => 'submit',
            '#value' => t('Add Image'),
            '#limit_validation_errors' => array(),
            '#submit' => array(
                'workshop_add_more_add_one'
            ),
            '#ajax' => array(
                'callback' => 'workshop_add_more_callback',
                'wrapper' => 'names-fieldset-wrapper'
            )
        );
        if ($form_state['num_names'] > 1) {
            $form['names_fieldset']['remove_name'] = array(
                '#type' => 'submit',
                '#value' => t('Remove Image'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'workshop_add_more_remove_one'
                ),
                '#ajax' => array(
                    'callback' => 'workshop_add_more_callback',
                    'wrapper' => 'names-fieldset-wrapper'
                )
            );
        }
        if ($no_js_use) {
            if (!empty($form['names_fieldset']['remove_name']['#ajax'])) {
                unset($form['names_fieldset']['remove_name']['#ajax']);
            }
            unset($form['names_fieldset']['add_name']['#ajax']);
        }
    } else {
        $form = array();
        $form['foss_select'] = array(
            '#type' => 'select',
            '#title' => t('Select FOSS under which event took place '),
            '#options' => get_first_dropdown_options_foss_name(),
            '#default_value' => $row->foss_name,
            "#required" => TRUE
        );
        $form['type'] = array(
            '#type' => 'select',
            '#title' => t('Selected Type'),
            '#options' => array(
                'Workshop' => t('Workshop'),
                'Conference' => t('Conference'),
                'Events' => t('Events')
            ),
            '#default_value' => $row->type,
            "#required" => TRUE
        );
        $form["name"] = array(
            "#type" => "textfield",
            "#title" => "Name",
            "#default_value" => $row->w_name,
            "#required" => TRUE
        );
        $form['start_date'] = array(
            '#type' => 'date_popup',
            '#title' => t('Select Start Date Of Event :'),
            '#date_increment' => 15,
            '#date_year_range' => '2008:+3',
            '#default_value' => $row->startdate,
            '#date_format' => 'Y-m-d',
            '#attributes' => array(
                'autocomplete' => 'off',
                'readonly' => 'readonly'
            ),
            "#required" => TRUE
        );
        $form['end_date'] = array(
            '#type' => 'date_popup',
            '#title' => t('Select End Date Of Event:'),
            '#date_increment' => 15,
            '#date_year_range' => '2008:+3',
            '#default_value' => $row->enddate,
            '#date_format' => 'Y-m-d',
            '#attributes' => array(
                'autocomplete' => 'off',
                'readonly' => 'readonly'
            ),
            "#required" => TRUE
        );
        $form["venue"] = array(
            "#type" => "textfield",
            "#title" => "Venue",
            "#default_value" => $row->venue,
            "#required" => TRUE
        );
        $form["street"] = array(
            "#type" => "textfield",
            "#title" => "Street",
            "#default_value" => $row->street,
            "#required" => TRUE
        );
        $form['country'] = array(
            '#type' => 'select',
            '#title' => t('Country'),
            '#options' => array(
                'India' => 'India',
                'Others' => 'Others'
            ),
            '#required' => TRUE,
            '#tree' => TRUE,
            '#validated' => TRUE,
            "#default_value" => $row->country
        );
        $form['other_country'] = array(
            '#type' => 'textfield',
            '#title' => t('Other than India'),
            '#size' => 100,
            '#attributes' => array(
                'placeholder' => t('Enter your country name')
            ),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'Others'
                    )
                )
            ),
            "#default_value" => $row->country
        );
        $form['other_state'] = array(
            '#type' => 'textfield',
            '#title' => t('State other than India'),
            '#size' => 100,
            '#attributes' => array(
                'placeholder' => t('Enter your state/region name')
            ),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'Others'
                    )
                )
            ),
            "#default_value" => $row->state
        );
        $form['other_city'] = array(
            '#type' => 'textfield',
            '#title' => t('City other than India'),
            '#size' => 100,
            '#attributes' => array(
                'placeholder' => t('Enter your city name')
            ),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'Others'
                    )
                )
            ),
            "#default_value" => $row->city
        );
        $form['all_state'] = array(
            '#type' => 'select',
            '#title' => t('State'),
            '#options' => _list_of_states(),
            '#validated' => TRUE,
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'India'
                    )
                )
            ),
            "#default_value" => $row->state
        );
        $form['city'] = array(
            '#type' => 'select',
            '#title' => t('City'),
            '#options' => _list_of_cities(),
            '#states' => array(
                'visible' => array(
                    ':input[name="country"]' => array(
                        'value' => 'India'
                    )
                )
            ),
            "#default_value" => $row->city
        );
        $form['pincode'] = array(
            '#type' => 'textfield',
            '#title' => t('Pincode'),
            '#size' => 30,
            '#maxlength' => 6,
            '#required' => False,
            '#attributes' => array(
                'placeholder' => 'Enter pincode....'
            ),
            "#default_value" => $row->pincode
        );
        $form["no_of_participant"] = array(
            "#type" => "textfield",
            "#title" => "Number Of Participants",
            "#default_value" => $row->no_of_participant,
            "#required" => TRUE
        );
        $form["body"] = array(
            "#type" => "textarea",
            "#title" => "Details",
            "#default_value" => $row->body,
            "#required" => TRUE
        );
        /*Edit Speakers*/
        /*Edit Testimonial*/
        $query_t = db_select('testimonials');
        $query_t->fields('testimonials');
        $query_t->condition('w_id', $workshop_id);
        $result_t = $query_t->execute();
        $num_of_results = $result_t->rowCount();
        $form['testimonial_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            //'#title' => t('Add Testimonial'),
            '#prefix' => '<div id="testimonial-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if ($num_of_results != 0) {
            $form_state['num_testimonial'] = $num_of_results;
            $temp = 0;
            $i = 0;
            while ($row_t = $result_t->fetchObject()) {
                $temp = $i;
                $form['testimonial_fieldset'][$i]["t_text"] = array(
                    "#type" => "item",
                    "#markup" => "<h4><label>Testimonial : " . ($temp + 1) . "</label></h4>"
                );
                $form['testimonial_fieldset'][$i]["t_id"] = array(
                    "#type" => "hidden",
                    "#default_value" => $row_t->t_id
                );
                $form['testimonial_fieldset'][$i]["t_name"] = array(
                    "#type" => "textfield",
                    "#title" => "Name",
                    "#default_value" => $row_t->name
                );
                $form['testimonial_fieldset'][$i]["t_department"] = array(
                    "#type" => "textfield",
                    "#title" => "Department",
                    "#default_value" => $row_t->department
                );
                $form['testimonial_fieldset'][$i]["t_university"] = array(
                    "#type" => "textfield",
                    "#title" => "University",
                    "#default_value" => $row_t->university
                );
                $form['testimonial_fieldset'][$i]["t_body"] = array(
                    "#type" => "textarea",
                    "#title" => "Testimonial",
                    "#default_value" => $row_t->body
                );
                $i++;
            }
            $form["testimonial_count"] = array(
                "#type" => "hidden",
                "#value" => $temp
            );
        } else {
            if (empty($form_state['num_testimonial'])) {
                $form_state['num_testimonial'] = 1;
            }
            $temp = 0;
            for ($i = 0; $i < $form_state['num_testimonial']; $i++) {
                $temp = $i;
                $form['testimonial_fieldset'][$i]["t_text"] = array(
                    "#type" => "item",
                    "#markup" => "<h4><label>Testimonial : " . ($temp + 1) . "</label></h4>"
                );
                $form['testimonial_fieldset'][$i]["t_name"] = array(
                    "#type" => "textfield",
                    "#title" => "Name",
                    "#default_value" => ""
                );
                $form['testimonial_fieldset'][$i]["t_department"] = array(
                    "#type" => "textfield",
                    "#title" => "Department",
                    "#default_value" => ""
                );
                $form['testimonial_fieldset'][$i]["t_university"] = array(
                    "#type" => "textfield",
                    "#title" => "University",
                    "#default_value" => ""
                );
                $form['testimonial_fieldset'][$i]["t_body"] = array(
                    "#type" => "textarea",
                    "#title" => "Testimonial"
                );
            }
            $form["testimonial_count"] = array(
                "#type" => "hidden",
                "#value" => $temp
            );
            $form['testimonial_fieldset']['add_testimonial'] = array(
                '#type' => 'submit',
                '#value' => t('Add testimonial'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'testimonial_add_more_add_one'
                ),
                '#ajax' => array(
                    'callback' => 'testimonial_add_more_callback',
                    'wrapper' => 'testimonial-fieldset-wrapper'
                )
            );
            if ($form_state['num_testimonial'] > 1) {
                $form['testimonial_fieldset']['remove_testimonial'] = array(
                    '#type' => 'submit',
                    '#value' => t('Remove'),
                    '#limit_validation_errors' => array(),
                    '#submit' => array(
                        'testimonial_add_more_remove_one'
                    ),
                    '#ajax' => array(
                        'callback' => 'testimonial_add_more_callback',
                        'wrapper' => 'testimonial-fieldset-wrapper'
                    )
                );
            }
            if ($no_js_use) {
                if (!empty($form['testimonial_fieldset']['remove_testimonial']['#ajax'])) {
                    unset($form['testimonial_fieldset']['remove_testimonial']['#ajax']);
                }
                unset($form['testimonial_fieldset']['add_testimonial']['#ajax']);
            }
        }
        $form['uploadphotos'] = array(
            '#markup' => '<h5><p> Edit Photos</p></h5>(Select Check box to delete existing photos)',
            '#prefix' => '<div id="uploaded_images"><table><tr>',
            '#suffix' => ''
        );
        $query = db_select('workshop');
        $query->fields('workshop');
        $query->condition('w_id', $workshop_id);
        $result = $query->execute();
        $row = $result->fetchObject();
        $query1 = db_select('workshop_images');
        $query1->fields('workshop_images');
        $query1->condition('w_id', $workshop_id);
        $result1 = $query1->execute();
        while ($row1 = $result1->fetchObject()) {
            $form['imagecheck@' . $row1->id] = array(
                '#type' => 'checkbox',
                '#field_suffix' => '<img style="width:100px; padding-left :20px;height:100px" src=' . $GLOBALS['base_url'] . "/events_images/" . str_replace(' ', '_', $row->w_name) . "/" . $row1->name . ' />'
            );
        }
        $form['enduploadphotos'] = array(
            '#markup' => '',
            '#prefix' => '',
            '#suffix' => '</tr></table></div>'
        );
        
        $form['names_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            // '#title' => t('Add images'),
            '#prefix' => '<div id="names-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if (empty($form_state['num_names'])) {
            $form_state['num_names'] = 1;
        }
        for ($i = 0; $i < $form_state['num_names']; $i++) {
            $form['names_fieldset'][$i]['name'] = array(
                '#title' => t('Add Event Image'),
                '#type' => 'file',
                '#weight' => '5',
                '#description' => t('Upload an image'),
                // We need this to know which file element this is.
                // By default drupal would name all as files[names_fieldset]
                '#name' => 'files[names_fieldset_' . $i . '_name]'
            );
        }
        $form['names_fieldset']['add_name'] = array(
            '#type' => 'submit',
            '#value' => t('Add one more'),
            '#limit_validation_errors' => array(),
            '#submit' => array(
                'workshop_add_more_add_one'
            ),
            '#ajax' => array(
                'callback' => 'workshop_add_more_callback',
                'wrapper' => 'names-fieldset-wrapper'
            )
        );
        if ($form_state['num_names'] > 1) {
            $form['names_fieldset']['remove_name'] = array(
                '#type' => 'submit',
                '#value' => t('Remove one'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'workshop_add_more_remove_one'
                ),
                '#ajax' => array(
                    'callback' => 'workshop_add_more_callback',
                    'wrapper' => 'names-fieldset-wrapper'
                )
            );
        }
        if ($no_js_use) {
            if (!empty($form['names_fieldset']['remove_name']['#ajax'])) {
                unset($form['names_fieldset']['remove_name']['#ajax']);
            }
            unset($form['names_fieldset']['add_name']['#ajax']);
        }
    }
    $form["workshop_id"] = array(
        "#type" => "hidden",
        "#value" => $workshop_id
    );
    $form["submit"] = array(
        "#type" => "submit",
        "#value" => "Submit"
    );
    return $form;
}


function workshop_add_form_validate($form, &$form_state)
{
    if ($form_state['values']['country'] == 'Others') {
        if ($form_state['values']['other_country'] == '') {
            form_set_error('other_country', t('Enter country name'));
            // $form_state['values']['country'] = $form_state['values']['other_country'];
        } else {
            $form_state['values']['country'] = $form_state['values']['other_country'];
        }
        if ($form_state['values']['other_state'] == '') {
            form_set_error('other_state', t('Enter state name'));
            // $form_state['values']['country'] = $form_state['values']['other_country'];
        } else {
            $form_state['values']['all_state'] = $form_state['values']['other_state'];
        }
        if ($form_state['values']['other_city'] == '') {
            form_set_error('other_city', t('Enter city name'));
            // $form_state['values']['country'] = $form_state['values']['other_country'];
        } else {
            $form_state['values']['city'] = $form_state['values']['other_city'];
        }
    } else {
        if ($form_state['values']['country'] == '') {
            form_set_error('country', t('Select country name'));
            // $form_state['values']['country'] = $form_state['values']['other_country'];
        }
        if ($form_state['values']['all_state'] == '') {
            form_set_error('all_state', t('Select state name'));
            // $form_state['values']['country'] = $form_state['values']['other_country'];
        }
        if ($form_state['values']['city'] == '') {
            form_set_error('city', t('Select city name'));
            // $form_state['values']['country'] = $form_state['values']['other_country'];
        }
    }
    if (isset($_FILES['files'])) {
        /* check for valid filename extensions */
        foreach ($_FILES['files']['name'] as $file_form_name => $file_name) {
            if ($file_name) {
                if (strstr($file_form_name, 'names_fieldset'))
                    $file_type = 'I';
                switch ($file_type) {
                    case 'I':
                        $allowed_extensions_str = variable_get('events_image_extensions', '');
                        break;
                }
                $allowed_extensions = explode(',', $allowed_extensions_str);
                $temp_extension = end(explode('.', strtolower($_FILES['files']['name'][$file_form_name])));
                if (!in_array($temp_extension, $allowed_extensions))
                    form_set_error($file_form_name, t('Only file with ' . $allowed_extensions_str . ' extensions can be uploaded.'));
                //if ($_FILES['files']['size'][$file_form_name] <= 0)
                //  form_set_error($file_form_name, t('File size cannot be zero.'));
            }
        }
    }
}


function speakers_add_more_callback($form, $form_state)
{
    return $form['speakers_fieldset'];
}


function speakers_add_more_add_one($form, &$form_state)
{
    $form_state['num_speakers']++;
    $form_state['rebuild'] = TRUE;
    //$form_state['no_redirect'] = TRUE;
}


function speakers_add_more_remove_one($form, &$form_state)
{
    if ($form_state['num_speakers'] > 1) {
        $form_state['num_speakers']--;
    }
    $form_state['rebuild'] = TRUE;
}


function testimonial_add_more_callback($form, $form_state)
{
    return $form['testimonial_fieldset'];
}


function testimonial_add_more_add_one($form, &$form_state)
{
    $form_state['num_testimonial']++;
    $form_state['rebuild'] = TRUE;
    //$form_state['no_redirect'] = TRUE;
}


function testimonial_add_more_remove_one($form, &$form_state)
{
    if ($form_state['num_testimonial'] > 1) {
        $form_state['num_testimonial']--;
    }
    $form_state['rebuild'] = TRUE;
}


function workshop_add_more_callback($form, $form_state)
{
    return $form['names_fieldset'];
}


function workshop_add_more_add_one($form, &$form_state)
{
    $form_state['num_names']++;
    $form_state['rebuild'] = TRUE;
    //$form_state['no_redirect'] = TRUE;
}


function workshop_add_more_remove_one($form, &$form_state)
{
    if ($form_state['num_names'] > 1) {
        $form_state['num_names']--;
    }
    $form_state['rebuild'] = TRUE;
}


function workshop_display_all()
{
    $page_content = "";
    $workshop_add_form = drupal_get_form("workshop_add_form");
    $page_content = drupal_render($workshop_add_form);
    return $page_content;
}


function get_first_dropdown_options_foss_name()
{
    $query = db_select('foss_type');
    $query->fields('foss_type', array(
        'id'
    ));
    $query->fields('foss_type', array(
        'foss_name'
    ));
    $result = $query->execute();
    $options = array();
    while ($foss_detail = $result->fetchObject()) {
        $options[$foss_detail->foss_name] = $foss_detail->foss_name;
    }
    $options["Others"] = "Others";
    return $options;
}


function get_country_dropdown_options()
{
    $query = db_select('Country');
    $query->fields('Country', array(
        'Code'
    ));
    $query->fields('Country', array(
        'Name'
    ));
    $result = $query->execute();
    $options = array();
    while ($country_detail = $result->fetchObject()) {
        $options[$country_detail->Code] = $country_detail->Name;
    }
    return $options;
}


function workshop_add_form_submit($form, &$form_state)
{
    $v = $form_state["values"];
    //$start_datetime= explode(" ",$v["start_date"]);
    //$end_datetime= explode(" ",$v["end_date"]);
    if ($v["workshop_id"]) {
        $query = db_update('workshop');
        $query->fields(array(
            'foss_name' => $v["foss_select"],
            'w_name' => $v["name"],
            'type' => $v["type"],
            'startdate' => $v["start_date"],
            'starttime' => "00:00",
            'enddate' => $v["end_date"],
            'endtime' => "00:00",
            'venue' => $v["venue"],
            'street' => $v["street"],
            'country' => $v["country"],
            'state' => $v["all_state"],
            'city' => $v["city"],
            'pincode' => $v["pincode"],
            'no_of_participant' => $v["no_of_participant"],
            'body' => $v["body"]
        ));
        $query->condition('w_id', $v["workshop_id"]);
        $result = $query->execute();
        /* For editing Testimonials */
        $testimonialupload = 0;
        for ($i = 0; $i <= $v["testimonial_count"]; $i++) {
            if ($v['testimonial_fieldset'][$i]["t_id"] != "") {
                if ($v['testimonial_fieldset'][$i]["t_name"] != "") {
                    $query = db_update('testimonials');
                    $query->fields(array(
                        'body' => $v['testimonial_fieldset'][$i]["t_body"],
                        'name' => $v['testimonial_fieldset'][$i]["t_name"],
                        'department' => $v['testimonial_fieldset'][$i]["t_department"],
                        'university' => $v['testimonial_fieldset'][$i]["t_university"]
                    ));
                    $query->condition('t_id', $v['testimonial_fieldset'][$i]["t_id"]);
                    $result = $query->execute();
                    if ($result != 0) {
                        $testimonialupload++;
                    }
                }
            } else {
                if ($v['testimonial_fieldset'][$i]["t_name"] != "") {
                    $testimonialquery = "
	INSERT INTO testimonials
	(w_id,body,name,department,university)
	VALUES
	(:w_id,:body,:name,:department,:university)
	";
                    $testimonialargs = array(
                        ":w_id" => $v["workshop_id"],
                        ":body" => $v['testimonial_fieldset'][$i]["t_body"],
                        ":name" => $v['testimonial_fieldset'][$i]["t_name"],
                        ":department" => $v['testimonial_fieldset'][$i]["t_department"],
                        ":university" => $v['testimonial_fieldset'][$i]["t_university"]
                    );
                    /* storing the row id in $result */
                    $testimonialresult = db_query($testimonialquery, $testimonialargs, array(
                        'return' => Database::RETURN_INSERT_ID
                    ));
                    if ($testimonialresult != 0) {
                        $testimonialupload++;
                    }
                }
            }
        }
        /* For editing Event images */
        /* For deleting existing images */
        $deletecounter = 0;
        $query_img = db_select('workshop_images');
        $query_img->fields('workshop_images');
        $query_img->condition('w_id', $v["workshop_id"]);
        $result_img = $query_img->execute();
	$root_path=events_images_path();
        while ($row_img = $result_img->fetchObject()) {
            if ($form_state['values']['imagecheck@' . $row_img->id] == 1) {
                if (file_exists($root_path.$row_img->path)) {
                    unlink($root_path.$row_img->path);
                    $query2 = db_delete('workshop_images');
                    $query2->condition('id', $row_img->id);
                    $delete_img = $query2->execute();
                    if ($delete_img != 0) {
                        $deletecounter++;
                    }
                } else {
                    drupal_set_message('Error Could not delete : ' . $filename . ', file does not exist', 'error');
                }
            }
        }
        /* For adding more images to existing event */
        $items = array();
        $root_path = events_images_path();
        $eventfolder = str_replace(' ', '_', $v["name"]);
        $dest_path = $eventfolder . '/';
        if (!is_dir($root_path . $dest_path)) {
            mkdir($root_path . $dest_path);
        }
        /* uploading files */
        foreach ($_FILES['files']['name'] as $file_form_name => $file_name) {
            if ($file_name) {
                if (file_exists($root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    drupal_set_message(t("Error uploading file. File !filename already exists.", array(
                        '!filename' => $_FILES['files']['name'][$file_form_name]
                    )), 'error');
                    return;
                }
                $imageupload = 0;
                /* uploading file */
                if (move_uploaded_file($_FILES['files']['tmp_name'][$file_form_name], $root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    $imagequery = "
	INSERT INTO workshop_images
	(w_id,name,path)
	VALUES
	(:w_id,:name, :path)
	";
                    $imageargs = array(
                        ":w_id" => $v["workshop_id"],
                        ":name" => $_FILES['files']['name'][$file_form_name],
                        ":path" => $dest_path . $_FILES['files']['name'][$file_form_name]
                    );
                    /* storing the row id in $result */
                    $imageresult = db_query($imagequery, $imageargs, array(
                        'return' => Database::RETURN_INSERT_ID
                    ));
                    if ($imageresult != 0) {
                        $imageupload++;
                    }
                    drupal_set_message($file_name . ' uploaded successfully.', 'status');
                } else {
                    drupal_set_message('Error uploading file : ' . $dest_path . $file_name, 'error');
                }
            }
        }
        if (!$result && $imageupload == 0 && $deletecounter == 0) {
            drupal_set_message("Something went wrong, please try again.", "error");
        } else {
            drupal_set_message("Workshop updated successfully", "status");
            if ($imageupload != 0) {
                drupal_set_message("Event's Pictures are added successfully", "status");
            }
            if ($deletecounter != 0) {
                drupal_set_message("Event's Pictures are deleted successfully", "status");
            }
        }
    } else {
        $query = "
	INSERT INTO workshop
	(foss_name,type,w_name, startdate, starttime,enddate, endtime, venue, street, country,state,city,pincode,no_of_participant,body)
	VALUES	(:foss_name,:type,:w_name, :startdate, :starttime,:enddate, :endtime, :venue, :street, :country, :state, :city, :pincode, 
:no_of_participant, :body)
	";
        $args = array(
            ":type" => $v["type"],
            ":foss_name" => $v["foss_select"],
            ":w_name" => $v["name"],
            ":startdate" => $v["start_date"],
            ":starttime" => "00:00",
            ":enddate" => $v["end_date"],
            ":endtime" => "00:00",
            ":venue" => $v["venue"],
            ":street" => $v["street"],
            ":country" => $v["country"],
            ":state" => $v["all_state"],
            ":city" => $v["city"],
            ":pincode" => $v["pincode"],
            ":no_of_participant" => $v["no_of_participant"],
            ":body" => $v["body"]
        );
        /* storing the row id in $result */
        $result = db_query($query, $args, array(
            'return' => Database::RETURN_INSERT_ID
        ));
        /* For adding speakers */
        $speakerupload = 0;
        for ($i = 0; $i <= $v["speakers_count"]; $i++) {
            if ($v['speakers_fieldset'][$i]["speakername"] != "") {
                $speakerquery = "
	INSERT INTO speakers
	(w_id,name,institute,place)
	VALUES
	(:w_id,:name,:institute,:place)
	";
                $speakerargs = array(
                    ":w_id" => $result,
                    ":name" => $v['speakers_fieldset'][$i]["speakername"],
                    ":institute" => $v['speakers_fieldset'][$i]["institute"],
                    ":place" => $v['speakers_fieldset'][$i]["place"]
                );
                /* storing the row id in $result */
                $speakerresult = db_query($speakerquery, $speakerargs, array(
                    'return' => Database::RETURN_INSERT_ID
                ));
                if ($speakerresult != 0) {
                    $speakerupload++;
                }
            }
        }
        /* For adding testimonial */
        $testimonialupload = 0;
        for ($i = 0; $i <= $v["testimonial_count"]; $i++) {
            if ($v['testimonial_fieldset'][$i]["t_name"] != "") {
                $testimonialquery = "
	INSERT INTO testimonials
	(w_id,body,name,department,university)
	VALUES
	(:w_id,:body,:name,:department,:university)
	";
                $testimonialargs = array(
                    ":w_id" => $result,
                    ":body" => $v['testimonial_fieldset'][$i]["t_body"],
                    ":name" => $v['testimonial_fieldset'][$i]["t_name"],
                    ":department" => $v['testimonial_fieldset'][$i]["t_department"],
                    ":university" => $v['testimonial_fieldset'][$i]["t_university"]
                );
                /* storing the row id in $result */
                $testimonialresult = db_query($testimonialquery, $testimonialargs, array(
                    'return' => Database::RETURN_INSERT_ID
                ));
                if ($testimonialresult != 0) {
                    $testimonialupload++;
                }
            }
        }
        /* For adding images of events*/
        $items = array();
        $root_path = events_images_path();
        $eventfolder = str_replace(' ', '_', $v["name"]);
        $dest_path = $eventfolder . '/';
        if (!is_dir($root_path . $dest_path)) {
            mkdir($root_path . $dest_path);
        }
        /* uploading files */
        foreach ($_FILES['files']['name'] as $file_form_name => $file_name) {
            if ($file_name) {
                if (file_exists($root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    drupal_set_message(t("Error uploading file. File !filename already exists.", array(
                        '!filename' => $_FILES['files']['name'][$file_form_name]
                    )), 'error');
                    return;
                }
                $imageupload = 0;
                /* uploading file */
                if (move_uploaded_file($_FILES['files']['tmp_name'][$file_form_name], $root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    $imagequery = "
	INSERT INTO workshop_images
	(w_id,name,path)
	VALUES
	(:w_id,:name, :path)
	";
                    $imageargs = array(
                        ":w_id" => $result,
                        ":name" => $_FILES['files']['name'][$file_form_name],
                        ":path" => $dest_path . $_FILES['files']['name'][$file_form_name]
                    );
                    /* storing the row id in $result */
                    $imageresult = db_query($imagequery, $imageargs, array(
                        'return' => Database::RETURN_INSERT_ID
                    ));
                    if ($imageresult != 0) {
                        $imageupload++;
                    }
                    drupal_set_message($file_name . ' uploaded successfully.', 'status');
                } else {
                    drupal_set_message('Error uploading file : ' . $dest_path . $file_name, 'error');
                }
            }
        }
        if (!$result) {
            drupal_set_message("Something went wrong, please try again.", "error");
        } else {
            drupal_set_message("Workshop added successfully", "status");
            if ($imageupload != 0) {
                drupal_set_message("Event's Pictures are added successfully", "status");
            }
            if ($testimonialupload != 0) {
                drupal_set_message("Testimonials added successfully", "status");
            }
            if ($speakerupload != 0) {
                drupal_set_message("Speaker's Detail are added successfully", "status");
            }
        }
    }
}


function ajax_country_dependent_dropdown_callback($form, $form_state)
{
    $country = $form_state['values']['country'];
    $form['state']['#options'] = ajax_getstate_callback($country);
    $commands[] = ajax_command_replace("#dropdown-state-replace", drupal_render($form['state']));
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}


function ajax_state_dependent_dropdown_callback($form, $form_state)
{
    $state = $form_state['values']['state'];
    $form['city']['#options'] = ajax_getcity_callback($state);
    $commands[] = ajax_command_replace("#dropdown-city-replace", drupal_render($form['city']));
    return array(
        '#type' => 'ajax',
        '#commands' => $commands
    );
}


function ajax_getstate_callback($country = '')
{
    $query = "
       SELECT Distinct (District) as state FROM City
       WHERE CountryCode =:country order by District
       ";
    $args = array(
        ":country" => $country
    );
    $result = db_query($query, $args);
    $options = array();
    while ($row = $result->fetchObject()) {
        $options[$row->state] = $row->state;
    }
    return $options;
}


function ajax_getcity_callback($state = '')
{
    $query = "
       SELECT Name as cityname FROM City
       WHERE District =:state order by Name
       ";
    $args = array(
        ":state" => $state
    );
    $result = db_query($query, $args);
    $options = array();
    while ($row = $result->fetchObject()) {
        $options[$row->cityname] = $row->cityname;
    }
    return $options;
}


function workshop_view_image_all($id = 0)
{
    $page_content = "";
    $query = db_select('workshop_images');
    $query->fields('workshop_images');
    $query->condition('id', $id);
    $result = $query->execute();
    $row1 = $result->fetchObject();
    $query = db_select('workshop');
    $query->fields('workshop');
    $query->condition('w_id', $row1->w_id);
    $result = $query->execute();
    $row = $result->fetchObject();
    $previous = ($row1->id) - 1;
    $page_content .= "<table class='tablew tablew-bordered tablew-hover' >" . $row1->name;
    $page_content .= "<tr><td><img style='width:550px; height:350px' src=" . $GLOBALS['base_url'] . "/events_images/" . str_replace(' ', '_', $row->w_name) . "/" . $row1->name . " /></td></tr>";
    $page_content .= "</table>";
    $page_content .= "<a href=" . $GLOBALS['base_url'] . "/events/images/change/" . $previous . " name=" . $row1->id . " id=" . $row1->id . "  class='autodialog' data-dialog-width='600px'>PREVIOUS</a>";
    return $page_content;
}


function workshop_view_selected_testimonial($id = 0)
{
    $page_content = "";
    $query = db_select('testimonials');
    $query->fields('testimonials');
    $query->condition('t_id', $id);
    $result = $query->execute();
    $row = $result->fetchObject();
    $page_content .= "<br><ul><li><i><p style='margin-top:-7px'>{$row->body}</i></p>
                                              <br>
						<br>
                                                <p style='text-align:right;margin-top:-30px'>{$row->name},{$row->department},</p>
                                                <p style='text-align:right;margin-top:-15px''>{$row->university}.</p>	
                                                </li><ul>";
    return $page_content;
}


function workshop_view_all_testimonials($workshop_id = 0)
{
    $page_content = "";
   
    $query = db_select('testimonials');
    $query->fields('testimonials');
    $query->condition('w_id', $workshop_id);
    $query->orderBy('t_id', 'DESC');
    $result = $query->extend('PagerDefault')->limit(4)->execute();
    $query1 = db_select('workshop');
    $query1->fields('workshop');
    $query1->condition('w_id', $workshop_id);
    $result1 = $query1->execute();
    $row1 = $result1->fetchObject();
    $page_content .= "<h2>Testimonials for " . $row1->w_name . " </h2>";
    $page_content .= "<div id='testimonials-wrapper'>";
    while ($row = $result->fetchObject()) {
        $page_content .= "
                <div class='testimonial'>
                    {$row->body}
                    <div class='author'>
                        - {$row->name}, {$row->department}, {$row->university}<br>
                       
                    </div>
                </div>
            ";
    }
    $page_content .= "</div> <!-- /#testimonials-wrapper -->";
  
    $page_content .= theme('pager', array(
        'header' => NULL,
        'rows' => 4
    ));
    return $page_content;
}


function workshop_view_image_change($id = 0)
{
    $page_content = "";
    $query = db_select('workshop_images');
    $query->fields('workshop_images');
    $query->condition('id', $id);
    $result = $query->execute();
    $row1 = $result->fetchObject();
    $query = db_select('workshop');
    $query->fields('workshop');
    $query->condition('w_id', $row1->w_id);
    $result = $query->execute();
    $row = $result->fetchObject();
    $page_content .= "<table class='tablew tablew-bordered tablew-hover' >" . $row1->name;
    $page_content .= "<tr><td><img style='width:600px; height:500px' src=" . $GLOBALS['base_url'] . "/events_images/" . str_replace(' ', '_', $row->w_name) . "/" . $row1->name . " /></td></tr>";
    $page_content .= "</table>";
    return $page_content;
}


function delete_directory($dirname)
{
    if (is_dir($dirname))
        $dir_handle = opendir($dirname);
    if (!$dir_handle)
        return false;
    while ($file = readdir($dir_handle)) {
        if ($file != "." && $file != "..") {
            if (!is_dir($dirname . "/" . $file))
                unlink($dirname . "/" . $file);
            else
                delete_directory($dirname . '/' . $file);
        }
    }
    closedir($dir_handle);
    rmdir($dirname);
    return true;
}


function workshop_delete_all($workshop_id = 0)
{
    $page_content = "";
    $query = db_select('workshop');
    $query->fields('workshop');
    $query->condition('w_id', $workshop_id);
    $result = $query->execute();
    $row = $result->fetchObject();
    $root_path = events_images_path();
    $eventfolder = str_replace(' ', '_', $row->w_name);
    $dest_path = $eventfolder;
    $dir_path = $root_path . $dest_path;
    if (is_dir($dir_path)) {
        $res = delete_directory($dir_path);
        if (!$res) {
            drupal_set_message(t("Cannot delete Event directory : " . $dir_path . ". Please contact administrator."), 'error');
            return;
        } else {
            $query2 = db_delete('workshop_images');
            $query2->condition('w_id', $workshop_id);
            $result2 = $query2->execute();
        }
    } else {
        drupal_set_message(t("Event directory not present : " . $dir_path . ". Skipping deleting directory."), 'status');
    }
    $query_t = db_delete('testimonials');
    $query_t->condition('w_id', $workshop_id);
    $result_t = $query_t->execute();
    $query3 = db_delete('workshop');
    $query3->condition('w_id', $workshop_id);
    $result3 = $query3->execute();
   
    if (!$result3) {
        drupal_set_message("Something went wrong, please try again.", "error");
    } else {
        if (!$result2 && (!$result_t)) {
            drupal_set_message("Workshop Deleted successfully", "status");
        } else {
            drupal_set_message("Workshop, testimonials and related photos are Deleted successfully", "status");
        }
    }
    return $page_content;
}


function workshop_edit_all($workshop_id = 0)
{
    $page_content = "";
    if ($workshop_id) {
        $workshop_add_form = drupal_get_form("workshop_add_form", $workshop_id);
        $page_content = drupal_render($workshop_add_form);
    } else {
        $query = db_select('workshop');
        $query->fields('workshop');
        $query->orderBy('startdate', 'DESC');
        $result = $query->execute();
        $headers = array(
            "Name",
            "Start Date",
            "Venue",
            "No of Participant",
            ""
        );
        $rows = array();
        $options = array(
            'attributes' => array(
                'class' => 'delete'
            )
        );
        while ($row = $result->fetchObject()) {
            $item = array(
                $row->w_name,
                $row->startdate,
                $row->venue,
                $row->no_of_participant,
                l("Edit", "events/edit/{$row->w_id}") . " | " . l("Delete", "events/delete/{$row->w_id}", $options)
            );
            array_push($rows, $item);
        }
        $page_content = bootstrap_table_format($headers, $rows);
    }
    return $page_content;
}


function events_view_filter($foss_name, $event_type, $startdate, $enddate, 
    $countryname, $statename, $cityname)
{
    // var_dump($foss_name."....". $event_type."....". $startdate."....". $enddate, 
    // $countryname."....". $statename, $cityname);
    // die;
    
    $page_content = "";
    $headers = array(
        "Name",
        "Start Date",
        "Venue",
        "No of Participants",
        ""
    );
    if ($startdate == "") {
        $startdate = '1960-01-01';
    } else {
        $startdate = $startdate;
    }
    
    if ($enddate == "") {
        $enddate = date("Y-m-d");
        //$enddate = "";
    } else {
        $enddate = $enddate;
    }
    
    if (trim($countryname) == "0") {
        $countryname = '%';
    } else {
        $countryname = trim($countryname);
    }
    
    if (trim($statename) == "0") {
        $statename = '%';
    } else {
        $statename = trim($statename);
    }
    
    if (trim($cityname) == "0") {
        $cityname = '%';
    } else {
        $cityname = trim($cityname);
    }
    
   
    $rows = array();
    $query = db_select('workshop');
    $query->fields('workshop');
    $query->condition('type', $event_type);
    $query->condition('foss_name', $foss_name);
    $query->condition('country', $countryname, 'LIKE');
    $query->condition('state', $statename, 'LIKE');
    $query->condition('city', $cityname, 'LIKE');
    $query->condition('startdate', $startdate, '>=');
    $query->condition('startdate', $enddate, '<=');
    $query->orderBy('startdate', 'DESC');
    $result = $query->execute();
    
    while ($row = $result->fetchObject()) {
        $item = array(
            $row->w_name,
            $row->startdate,
            $row->venue,
            $row->no_of_participant,
            "<a href=" . $GLOBALS['base_url'] . "/events/view_details/{$row->w_id} target='_blank' title='Click to view detail'>Details</a>"
        );
        array_push($rows, $item);
    }
    $page_content .= bootstrap_table_format($headers, $rows);
    
    return $page_content;
}


function workshop_view_all($workshop_id, $startdate, $enddate)
{
    if ($startdate == "") {
        $startdate = "2015-08-01";
    } else {
        $startdate = $startdate;
    }
    if ($enddate == "") {
        $enddate = date("Y-m-d");
        //$enddate="";
    } else {
        $enddate = $enddate;
    }
    $page_content = "";
    $headers = array(
        "Name",
        "Start Date",
        "Venue",
        "No of Participants",
        ""
    );
    
    $rows1 = array();
    $query1 = db_select('workshop');
    $query1->fields('workshop');
    $query1->condition('type', "Workshop");
    $query1->condition('startdate', $startdate, '>=');
    $query1->condition('startdate', $enddate, '<=');
    $query1->orderBy('startdate', 'DESC');
    $result1 = $query1->execute();
    while ($row = $result1->fetchObject()) {
        $item = array(
            $row->w_name,
            $row->startdate,
            $row->venue,
            $row->no_of_participant,
          
            "<a href=" . $GLOBALS['base_url'] . "/events/view_details/{$row->w_id} target='_blank' title='Click to view detail'>Details</a>"
        );
        array_push($rows1, $item);
    }
    $page_content .= bootstrap_table_format($headers, $rows1);
    return $page_content;
}


function conference_seminar_view_all($workshop_id, $startdate, $enddate)
{
    $page_content = "";
    $headers = array(
        "Name",
        "Start Date",
        "Venue",
        "No of Participants",
        ""
    );
    $rows1 = array();
    $query1 = db_select('workshop');
    $query1->fields('workshop');
    $query1->condition('type', "Conference");
    $query1->orderBy('startdate', 'DESC');
    $result1 = $query1->execute();
    while ($row = $result1->fetchObject()) {
        $item = array(
            $row->w_name,
            $row->startdate,
            $row->venue,
            $row->no_of_participant,
           
            "<a href=" . $GLOBALS['base_url'] . "/events/view_details/{$row->w_id} target='_blank' title='Click to view detail'>Details</a>"
        );
        array_push($rows1, $item);
    }
    $page_content .= bootstrap_table_format($headers, $rows1);
    return $page_content;
}

function event_activities_all()
{ 
    //This is used for displaying text (tab sta)
    $form['displaytext'] = array(
        '#type' => 'markup',
        '#prefix' => '<div><div id="displaytext" style="font-weight:bold;padding-top:10px">',
        '#suffix' => '</div></div>',
        '#markup' => ''
    );
    //For displaying count of TBC of all foss
    $form['tbctable'] = array(
        '#type' => 'item',
        '#prefix' => '<div id="default_load" >',
        '#markup' => '<ul class="nav nav-tabs">                       
                       
                          <li class="active"><a data-toggle="tab" href="#workshopdata">Workshop</a></li>
                          
                            <li><a data-toggle="tab" href="#conferencedata">Conference </a></li>
                            
                      </ul>'
     );
   
     $form['tab_content'] = array(
        '#type' => 'item',
        '#markup' => '<div class="tab-content">

        
        <div id="workshopdata" class="tab-pane fade  in active">' . workshop_view_all(0,1960-01-01, date("Y-m-d")).'</div>
       
       
        <div id="conferencedata" class="tab-pane fade ">' . conference_seminar_view_all(0, "", "").'</div>

 </div>'
    );
    $form['lastdiv'] = array(
        '#type' => 'item',
        '#markup' => '',
        '#suffix' => '</div></div>'
    );
    return $form;

}

function event_seminar_view_all($workshop_id, $startdate, $enddate)
{
    $page_content = "";
    $headers = array(
        "Name",
        "Start Date",
        "Venue",
        "No of Participants",
        ""
    );
    $rows1 = array();
    $query1 = db_select('workshop');
    $query1->fields('workshop');
    $query1->condition('type', "Events");
    $query1->orderBy('startdate', 'DESC');
    $result1 = $query1->execute();
    while ($row = $result1->fetchObject()) {
        $item = array(
            $row->w_name,
            $row->startdate,
            $row->venue,
            $row->no_of_participant,
           
            "<a href=" . $GLOBALS['base_url'] . "/events/view_details/{$row->w_id} target='_blank' title='Click to view detail'>Details</a>"
        );
        array_push($rows1, $item);
    }
    $page_content .= bootstrap_table_format($headers, $rows1);
    return $page_content;
}

function workshop_view_details_all($workshop_id = 0)
{
    $testimonial_scroll_js = "(function ($) {
$(document).ready(function(){
	
	$('#testimonials_front li').hide().eq(0).show();
	
	(function showNextTestimonial(){
		
		$('#testimonials_front li:visible').delay(12000).fadeOut('slow',function(){
			$(this).appendTo('#testimonials_front ul');
			if($(this).next().length > 0)
				{
				$(this).next().fadeIn('slow',function(){
					showNextTestimonial();
					});
				}
			else
				{
				$(this).siblings(':first').fadeIn('slow',function(){
					showNextTestimonial();
					});
				}
		});
	})();
	
});
})(jQuery);";
    drupal_add_js($testimonial_scroll_js, 'inline', 'header');


    	$testimonial_scroll_CSS="#testimonials-wrapper .testimonial {
    position: relative;
    text-align: justify;
    margin: 25px 0 0 0;
    padding: 0 0 10px 0;
    background: #e5e5e5;
    padding: 10px;
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    -o-border-radius: 5px;
    border-radius: 5px;
}
#testimonials-wrapper .testimonial > .author {
    text-align: right;
    font-weight: bolder;
    margin: 15px 0 0 0;
    background: #ffffff;
    padding: 10px;
    -moz-border-radius: 5px;
    -webkit-border-radius: 5px;
    -o-border-radius: 5px;
    border-radius: 5px;
}


#testimonials_front{
	padding: 10px 20px 15px 20px;
	background:url('../images/open-quote.png') no-repeat ;
	background-size: 22px 22px;
	background-position: 18px 23px;
	text-align:justify;
	min-height:90px;
	margin-top: 10px;
	-moz-border-radius:12px;
	-webkit-border-radius:12px;
	border-radius:12px;
	box-shadow: 1px 1px 4px 1px #888888;
	height:135px;
	
}


#testimonials_front a{
	/* float:left; */
}

.testimonial_head
{
	
	text-align:left;
	margin-top:-14px;
	font-size:16px;
}

#testimonials_front li{ display:none;}
#testimonials_front li:first-child{ display:block;}

#testimonials_front ul{ list-style:none;}


#testimonials_front h4{
color:#424242;
margin-top:-20px;
	
}


#testimonials_one{
	padding: 10px 20px 15px 20px;
	background:url('../images/open-quote.png') no-repeat ;
	background-size: 22px 22px;
	background-position: 18px 23px;
	text-align:justify;
	min-height:90px;
	margin-top: 10px;
	-moz-border-radius:12px;
	-webkit-border-radius:12px;
	border-radius:12px;
	box-shadow: 1px 1px 4px 1px #888888;
	height:135px;
	
}


#testimonials_one a{
	/* float:left; */
}


#testimonials_one li{ display:none;}
#testimonials_one li:first-child{ display:block;}

#testimonials_one ul{ list-style:none;}


#testimonials_one h4{
color:#424242;
margin-top:-20px;
	
}

";
 drupal_add_css($testimonial_scroll_CSS, 'inline', 'header');

    $page_content = "";
    $row = "";
    $row1 = "";
    $query = db_select('workshop');
    $query->fields('workshop');
    $query->condition('w_id', $workshop_id);
    $result = $query->execute();
    $row = $result->fetchObject();
    $speakerquery = db_select('speakers');
    $speakerquery->fields('speakers');
    $speakerquery->condition('w_id', $workshop_id);
    $speakerresult = $speakerquery->execute();
    $num_of_speakercount = $speakerresult->rowCount();
    if($num_of_speakercount!=0){
    $textdisplay = "";
    $counter = 1;
    while ($speakerrow = $speakerresult->fetchObject()) {
        $var = $counter++;
        $textdisplay .= "<p>" . $var . ". " . $speakerrow->name . " <sub class='subclass'>" . $speakerrow->institute . ", " . $speakerrow->place . "</sub></p>";
    }
   }
    //$starttime= strftime('%I:%M %p', strtotime($row->starttime));
    //$endtime= strftime('%I:%M %p', strtotime($row->endtime));
    $startdate = $row->startdate;
    $enddate = $row->enddate;
    $date = $row->startdate . " , " . $row->enddate;
    //$date1=date_create($row->startdate);
    //$date2=date_create($row->enddate);
    //$diff=date_diff($date1,$date2);
    //$date1=date_create($row->startdate);
    //$date2=date_create($row->enddate);
    //$diff=date_diff($date1,$date2);
    //$duration= $diff->format("%a");
    $daylen = 60 * 60 * 24;
    $date1 = $row->enddate;
    $date2 = $row->startdate;
    //echo (strtotime($date1)-strtotime($date2))/$daylen;
    $dayCount = ((strtotime($date1) - strtotime($date2)) / $daylen) + 1;
    //$time=$starttime." - ".$endtime;
    $page_content .= "<table class='table table-bordered table-hover' >";
    $page_content .= "<tr><td><b>Name</b></td><td>{$row->w_name}</td></tr>";
    $page_content .= "<tr><td><b>Start Date</b></td><td>$startdate</td></tr>";
    $page_content .= "<tr><td><b>End Date</b></td><td>$enddate</td></tr>";
    $page_content .= "<tr><td><b>No. of Participants</b></td><td>{$row->no_of_participant}</td></tr>";
    $page_content .= "<tr><td><b>Venue</b></td><td>{$row->venue}</td></tr>";
     if($num_of_speakercount!=0){
    $page_content .= "<tr><td><b>Speakers</b></td><td>$textdisplay</td></tr>";
    }
    $page_content .= "<tr><td><b>Details</b></td><td>{$row->body}</td></tr>";
    $query = db_select('workshop_images');
    $query->fields('workshop_images');
    $query->condition('w_id', $workshop_id);
    $result = $query->execute();
    $num_of_results = $result->rowCount();
    if ($num_of_results != 0) {
        $page_content .= "<tr><td><b>Pictures</b></td><td>";
        while ($row1 = $result->fetchObject()) {
            $page_content .= "<a class='fancybox' rel='gallery1' href=" . $GLOBALS['base_url'] . "/events_images/" . str_replace(' ', '_', $row->w_name) . "/" . $row1->name . "><img style='width:100px; height:100px; padding-right:20px;padding-bottom:10px' src=" . $GLOBALS['base_url'] . "/events_images/" . str_replace(' ', '_', $row->w_name) . "/" . $row1->name . " /></a>";
        }
        $page_content .= "</td></tr></table>";
    } else {
        $page_content .= "</table>";
    }
    $query = db_select('testimonials');
    $query->fields('testimonials');
    $query->condition('w_id', $workshop_id);
    $query->orderBy('t_id', 'DESC');
    $result = $query->execute();
    $num_of_results = $result->rowCount();
    if ($num_of_results != 0) {
        if ($num_of_results > 1) {
            $page_content .= "<div id='testimonials_front'><div><br>
<a href=" . $GLOBALS['base_url'] . "/events/testimonials/{$workshop_id} target='_blank' title='Click to view all testimonials'><h4 style='color:#3399FF'>Testimonials</h4></a>
<br></div><ul>";
            $page_testimonial = "";
            while ($row = $result->fetchObject()) {
                if (strlen($row->body) >= 1) {
                    if (strlen($row->body) >= 260) {
                        $cut1 = substr($row->body, 0, 200);
                        $cut = $cut1 . "  ...   ";
                        $page_testimonial = "<li><i><p style='margin-top:-7px'>{$cut}</i><a href=" . $GLOBALS['base_url'] . "/events/testimonials/view/" . $row->t_id . " name=" . $row1->t_id . " id=" . $row1->t_id . "  class='autodialog' title='Click to read more'>Read more</a></p>
                                              <br>
                                                <p style='text-align:right;margin-top:-30px'>{$row->name},{$row->department},</p>
                                                <p style='text-align:right;margin-top:-15px''>{$row->university}.</p>	
                                                </li>";
                    } else {
                        $page_testimonial = "<li><i><p style='margin-top:-7px'>$row->body</i></p>
                                              <br>
                                                <p style='text-align:right;margin-top:-30px'>{$row->name},{$row->department},</p>
                                                <p style='text-align:right;margin-top:-15px''>{$row->university}.</p>	
                                                </li>";
                    }
                }
                $page_content .= $page_testimonial;
            }
            $page_content .= "</ul></div>";
        } else {
            $page_content .= "<div id='testimonials_one'><div><br>
<a href=" . $GLOBALS['base_url'] . "/events/testimonials/{$workshop_id} target='_blank' title='Click to view all testimonials'><h4 style='color:#3399FF'>Testimonials</h4></a>
<br></div><ul>";
            $page_testimonial = "";
            while ($row = $result->fetchObject()) {
                if (strlen($row->body) >= 1) {
                    if (strlen($row->body) >= 260) {
                        $cut1 = substr($row->body, 0, 200);
                        $cut = $cut1 . "  ...   ";
                        $page_testimonial = "<li><i><p style='margin-top:-7px'>{$cut}</i><a href=" . $GLOBALS['base_url'] . "/events/testimonials/view/" . $row->t_id . " name=" . $row1->t_id . " id=" . $row1->t_id . "  class='autodialog' title='Click to read more'>Read more</a></p>
                                              <br>
                                                <p style='text-align:right;margin-top:-30px'>{$row->name},{$row->department},</p>
                                                <p style='text-align:right;margin-top:-15px''>{$row->university}.</p>	
                                                </li>";
                    } else {
                        $page_testimonial = "<li><i><p style='margin-top:-7px'>$row->body</i></p>
                                              <br>
                                                <p style='text-align:right;margin-top:-30px'>{$row->name},{$row->department},</p>
                                                <p style='text-align:right;margin-top:-15px''>{$row->university}.</p>	
                                                </li>";
                    }
                }
                $page_content .= $page_testimonial;
            }
            $page_content .= "</ul></div>";
        }
    }
    return $page_content;
}


function _list_of_states()
{
    $states = array(
        0 => '-Select-'
    );
    $query = db_select('list_states_of_india');
    $query->fields('list_states_of_india');
    $states_list = $query->execute();
    while ($states_list_data = $states_list->fetchObject()) {
        $states[$states_list_data->state] = $states_list_data->state;
    }
    return $states;
}


function _list_of_cities()
{
    $city = array(
        0 => '-Select-'
    );
    $query = db_select('list_cities_of_india');
    $query->fields('list_cities_of_india');
    $query->orderBy('city', 'ASC');
    $city_list = $query->execute();
    while ($city_list_data = $city_list->fetchObject()) {
        $city[$city_list_data->city] = $city_list_data->city;
    }
    return $city;
}


//Self Workshop from spoken db

function getselfworkshoplcount($foss_name,$startdate,$enddate,$state,$city){

	if($foss_name=="0"||$foss_name==""){
	$foss_name="%";
	}else{
	$foss_name=$foss_name;
	}

	if ($city == "" || $city == "null") {
        $city = "%";
    } else {
        $city = $city;
    }

    if ($state == "" || $state == "null") {
        $state= "%";
    } else {
        $state = $state;
    }
	if ($startdate == "") {
        $startdate = '1960-01-01';
    } else {
        $startdate = $startdate;
    }
    
    if ($enddate == "") {
        $enddate = date("Y-m-d");
        //$enddate = "";
    } else {
        $enddate = $enddate;
    }
    $rows = array();
    $page_content = "";
    $headers = array(
        "Foss Name",
        "No. of Workshop Conducted",
        
    );
    $query = db_select('foss_type');
    $query->fields('foss_type', array(
        'id'
    ));
    $query->fields('foss_type', array(
        'foss_name'
    ));
    $query->fields('foss_type', array(
        'foss_selfworkshop_no'
    ));
    $query->condition('foss_name', $foss_name, 'LIKE');
    $query->condition('foss_selfworkshop_no', "null", '!=');
    $result = $query->execute();
    if($result!=null){
	$page_content = "";

if($city == "%"&&$state!="%"){
$city="NONE";
while ($foss_detail = $result->fetchObject()) {

	    db_set_active('selfworkshop');
	    $query2 = db_query("SELECT count(t.id) as count FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id ) and s.name LIKE :state AND t.tdate >= :startdate AND t.tdate <= :enddate ", array(
                                ':foss_id' => $foss_detail->foss_selfworkshop_no,
				':state'=>$state,
				':startdate'=>$startdate,
				':enddate'=>$enddate,
                            ));

	    $count = $query2->fetchObject()->count;

	    db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();
   	    $item = array(
            $foss_detail->foss_name,
            "<a href=" . $GLOBALS['base_url']. "/completed-workshops-list/" . $foss_detail->foss_selfworkshop_no . "/" .$city."/".$state."/".$startdate."/".$enddate. " target='_blank' title='Click to view workshop list'>".$count."</a>"
        	);
		
        array_push($rows, $item);
    }


}else if($state == "%"&&$city!="%"){
$state="NONE";
while ($foss_detail = $result->fetchObject()) {

	    db_set_active('selfworkshop');
	    $query2 = db_query("SELECT count(t.id) as count FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id ) and c.name LIKE :city  AND t.tdate >= :startdate AND t.tdate <= :enddate ", array(
                                ':foss_id' => $foss_detail->foss_selfworkshop_no,
				':city'=>$city,
				
				':startdate'=>$startdate,
				':enddate'=>$enddate,
                            ));

	    $count = $query2->fetchObject()->count;

	    db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();
   	    $item = array(
            $foss_detail->foss_name,
            "<a href=" . $GLOBALS['base_url']. "/completed-workshops-list/" . $foss_detail->foss_selfworkshop_no . "/" .$city."/".$state."/".$startdate."/".$enddate. " target='_blank' title='Click to view workshop list'>".$count."</a>"
        	);
		
        array_push($rows, $item);
    }

}else if($city == "%"&&$state == "%"){
$city="NONE";
$state="NONE";
while ($foss_detail = $result->fetchObject()) {

	    db_set_active('selfworkshop');
	    $query2 = db_query("SELECT count(t.id) as count FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id ) AND t.tdate >= :startdate AND t.tdate <= :enddate ", array(
                                ':foss_id' => $foss_detail->foss_selfworkshop_no,
				
				':startdate'=>$startdate,
				':enddate'=>$enddate,
                            ));

	    $count = $query2->fetchObject()->count;

	    db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();
   	    $item = array(
            $foss_detail->foss_name,
            "<a href=" . $GLOBALS['base_url']. "/completed-workshops-list/" . $foss_detail->foss_selfworkshop_no . "/" .$city."/".$state."/".$startdate."/".$enddate. " target='_blank' title='Click to view workshop list'>".$count."</a>"
        	);
		
        array_push($rows, $item);
    }

}else{

while ($foss_detail = $result->fetchObject()) {

	    db_set_active('selfworkshop');
	    $query2 = db_query("SELECT count(t.id) as count FROM events_training t, events_academiccenter ac, events_city c, events_state s WHERE t.academic_id=ac.id and ac.city_id=c.id and c.state_id=s.id and (t.status = 4 AND t.foss_id = :foss_id ) and c.name LIKE :city and s.name LIKE :state AND t.tdate >= :startdate AND t.tdate <= :enddate ", array(
                                ':foss_id' => $foss_detail->foss_selfworkshop_no,
				':city'=>$city,
				':state'=>$state,
				':startdate'=>$startdate,
				':enddate'=>$enddate,
                            ));

	    $count = $query2->fetchObject()->count;

	    db_set_active('default'); // We need to call the main (drupal) db back
            db_set_active();
   	    $item = array(
            $foss_detail->foss_name,
            "<a href=" . $GLOBALS['base_url']. "/completed-workshops-list/" . $foss_detail->foss_selfworkshop_no . "/" .$city."/".$state."/".$startdate."/".$enddate. " target='_blank' title='Click to view workshop list'>".$count."</a>"
        	);
		
        array_push($rows, $item);
    }

}
    
    $page_content .= bootstrap_table_format($headers, $rows);
    

   }else{
	$page_content = "No Record Found";
  }
  return $page_content;

}


function completed_workshops_list($String,$foss_id,$city,$state,$startdate,$enddate){
		

		$query = db_select('foss_type');
    		$query->fields('foss_type', array(
        	'id'
    		));
   		 $query->fields('foss_type', array(
        	'foss_name'
    		));
    		
   	 	$query->condition('foss_selfworkshop_no', $foss_id);
   	 	$result = $query->execute();
		$fossdetail = $result->fetchObject();
		$fossname=$fossdetail->foss_name;
		$caption="<h4>List of Workshop under FOSS : ".$fossname."<br><br><h4>";
	
		if($city=="NONE"){
		   $city="%";
		}else{
		   $city=$city;
		}
		if($state=="NONE"){
		   $state="%";
		}else{
		   $state=$state;
		}
	
		$query = "";
		$count_query = "";
		$rows=array();
		db_set_active('selfworkshop');
	

			$header = array(
			array('data'=>'Institution', 'field'=>'ac.institution_name'),
			array('data'=>'City', 'field'=>'c.name'),
			array('data'=>'Workshop_Date', 'field'=>'t.tdate', 'sort'=>'desc'),
			array('data'=>'No. of students', 'field'=>'t.participant_count'),
			array('data'=>''),
		);
		

		db_set_active('workshop_info');
		$query = db_select('events_training', 't');
		$query->join('events_academiccenter','ac','ac.id=t.academic_id');
		$query->join('events_city','c','c.id=ac.city_id');
		$query->join('events_state','s','s.id=c.state_id');
		$query->fields('t', array('id', 'foss_id', 'tdate', 'participant_count'));
		$query->fields('ac', array('institution_name'));
		$query->fields('c', array('name'));
		$query->addField('c','name','city');
		$query->condition('t.status', 4);
		$query->condition('t.foss_id', $foss_id);
		$query->condition('c.name',$city,'LIKE');
		$query->condition('s.name',$state,'LIKE');
		$query->condition('t.tdate', $startdate, '>=');
    		$query->condition('t.tdate', $enddate, '<=');
		$result = $query->extend('PagerDefault')->limit(50)->extend('TableSort')->orderByHeader($header)->execute();
		$number_of_rows = $result->rowCount();
		
		
		while ($row = $result->fetchObject()) {
			$item = array($row->institution_name, $row->city, $row->tdate, $row->participant_count,
		"<a href=".$GLOBALS['base_url']."/view-completed-workshop/".$row->id." target='_blank' title='Click to view detail'>View</a>",
		);
				array_push($rows, $item);
		}
		

		if ($number_of_rows==0) {
			$output .= '<div style="color:red;text-align:center;">List is empty.</div>';
		} else {
			
			$build['pager_table'] = array(
    			'#theme' => 'table',
    			'#header' => $header,
    			'#rows' => $rows,
			'#caption'=>$caption,
			'#attributes' => array (
    			'class' => array('table table-bordered table-hover'),
  				),
  			);

  			// Attach the pager theme.
  			$build['pager_pager'] = array('#theme' => 'pager');
			$output = $build;

			
		}

		db_set_active('default');
		return $output;
		
	}


function view_completed_workshop($id = NULL){

	 	if($id){
	 		
			db_set_active('selfworkshop');
			$query = db_select('events_training', 't');
			$query->join('events_academiccenter','ac','ac.id=t.academic_id');
			$query->join('events_city','c','c.id=ac.city_id');
			$query->join('creation_fosscategory','f','f.id=t.foss_id');
			$query->fields('f', array('foss'));
			$query->fields('ac', array('address', 'academic_code', 'institution_name'));
			$query->fields('c', array('name'));
			$query->addField('c','name','city');
			$query->fields('t', array('organiser_id', 'training_code', 'tdate', 'participant_count'));
			$query->condition('t.id', $id);
			$result = $query->execute();
	 		
	 		if($result){

				$item =$result->fetchObject();

				db_set_active('selfworkshop');
				$query = db_select('events_organiser', 'o');
				$query->join('auth_user','u','u.id=o.user_id');
				$query->fields('u', array('first_name', 'last_name'));
				$query->condition('o.id', 2);
				$result = $query->execute();

	 			//db_set_active('workshop_info');
	 			//$result = db_query($query);
	 			db_set_active('default');
	 			$organiser = $result->fetchObject();
	 			
	 			$output .= '<table width="100%" cellpadding="6" class="table table-bordered table-hover">';
	 			$output .= '<tr><td><b>Workshop Code</b></td><td><b>'.$item->training_code.'</b></td></tr>';
	 			$output .= '<tr><td><b>Foss Category</b></td><td><b>'.$item->foss.'</b></td></tr>';
	 			$output .= '<tr><td><b>Date & Time</b></td><td><b>'.$item->tdate.'</b></td></tr>';
	 			$output .= '<tr><td><b>No.of Participants </b></td><td><b>'.$item->participant_count.'</b></td></tr>';
				$output .= '<tr><td><b>Organiser </b></td><td><b>'.$organiser->first_name.' '.$organiser->last_name.'
				</b></td></tr>';
	 			$output .= '<tr><td colspan="2"><div Style="height:20px"></div></td></tr>';
	 			
	 			$output .= '<tr><td><b>Academic Center Code</b></td><td><b>'.$item->academic_code.'</b></td></tr>';
	 			$output .= '<tr><td><b>Institution Name</b></td><td><b>'.$item->institution_name.'</b></td></tr>';
	 			$output .= '<tr><td><b>Street</b></td><td><b>'.$item->address.'</b></td></tr>';
	 			$output .= '<tr><td><b>City</b></td><td><b>'.$item->city.'</b></td></tr>';
	 			$output .= '</table>';
	 			
	 			return $output;
	 		}else {
	 			drupal_set_message('Error accessing database selfworkshop');
	 		}
	 	}else{
	 		return "<p>Sorry, no data found.</p>";
	 	}
	}

function postalcampaign_display_all()
{
    $page_content = "";
    $postalcampaign_add_form = drupal_get_form("postalcampaign_add_form");
    $page_content = drupal_render($postalcampaign_add_form);
    return $page_content;
}

function postalcampaign_add_form($form, &$form_state, $postalcampaign_id = 0, $no_js_use = FALSE)
{
    $query = "
       SELECT * FROM postercampaign
       WHERE p_id = :postercampaign_id
       ";
    $args = array(
        ":postercampaign_id" => $postalcampaign_id
    );
    $result = db_query($query, $args);
    $row = $result->fetchObject();
    $form = array();
   
    if ($postalcampaign_id == 0) {
        $form['foss_select'] = array(
            '#type' => 'select',
            '#title' => t('Select FOSS under which event took place '),
            '#options' => get_first_dropdown_options_foss_name(),
            '#default_value' => '',
            "#required" => TRUE
        );
     
        $form["name"] = array(
            "#type" => "textfield",
            "#title" => "Name",
            "#default_value" => '',
            "#required" => TRUE
        );
        $form['start_date'] = array(
            '#type' => 'date_popup',
            '#title' => t('Event Date:'),
            '#date_increment' => 15,
            '#date_year_range' => '2008:+3',
            '#default_value' => '',
            '#date_format' => 'Y-m-d',
            '#attributes' => array(
                'autocomplete' => 'off',
                'readonly' => 'readonly'
            ),
            "#required" => TRUE
        );
        
        $form["body"] = array(
            "#type" => "textarea",
            "#title" => "Details",
            "#default_value" => '',
            "#required" => TRUE
        );
        
        $form['poster_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            '#prefix' => '<div id="poster-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if (empty($form_state['num_poster'])) {
            $form_state['num_poster'] = 1;
        }
        for ($i = 0; $i < $form_state['num_poster']; $i++) {
            $form['poster_fieldset'][$i]['name'] = array(
                '#title' => t('Add poster'),
                '#type' => 'file',
                '#weight' => '5',
                '#description' => t('Upload an poster'),
                '#name' => 'files[poster_fieldset_' . $i . '_name]'
            );
        }
        $form['poster_fieldset']['add_name'] = array(
            '#type' => 'submit',
            '#value' => t('Add poster'),
            '#limit_validation_errors' => array(),
            '#submit' => array(
                'poster_add_more_add_one'
            ),
            '#ajax' => array(
                'callback' => 'poster_add_more_callback',
                'wrapper' => 'poster-fieldset-wrapper'
            )
        );
        if ($form_state['num_poster'] > 1) {
            $form['poster_fieldset']['remove_name'] = array(
                '#type' => 'submit',
                '#value' => t('Remove Poster'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'poster_add_more_remove_one'
                ),
                '#ajax' => array(
                    'callback' => 'poster_add_more_callback',
                    'wrapper' => 'poster-fieldset-wrapper'
                )
            );
        }
        if ($no_js_use) {
            if (!empty($form['poster_fieldset']['remove_name']['#ajax'])) {
                unset($form['poster_fieldset']['remove_name']['#ajax']);
            }
            unset($form['poster_fieldset']['add_name']['#ajax']);
        }
    } else {
        $form = array();
        $form['foss_select'] = array(
            '#type' => 'select',
            '#title' => t('Select FOSS under which event took place '),
            '#options' => get_first_dropdown_options_foss_name(),
            '#default_value' => $row->foss_name,
            "#required" => TRUE
        );
        $form["name"] = array(
            "#type" => "textfield",
            "#title" => "Name",
            "#default_value" => $row->p_name,
            "#required" => TRUE
        );
        $form['start_date'] = array(
            '#type' => 'date_popup',
            '#title' => t('Select Start Date Of Event :'),
            '#date_increment' => 15,
            '#date_year_range' => '2008:+3',
            '#default_value' => $row->startdate,
            '#date_format' => 'Y-m-d',
            '#attributes' => array(
                'autocomplete' => 'off',
                'readonly' => 'readonly'
            ),
            "#required" => TRUE
        );
        $form["body"] = array(
            "#type" => "textarea",
            "#title" => "Details",
            "#default_value" => $row->body,
            "#required" => TRUE
        );
        /*Edit Speakers*/
        /*Edit Testimonial*/
        
        $form['uploadphotos'] = array(
            '#markup' => '<h5><p> Edit Poster</p></h5>(Select Check box to delete existing poster)',
            '#prefix' => '<div id="uploaded_poster"><table><tr>',
            '#suffix' => ''
        );
        $query = db_select('postercampaign');
        $query->fields('postercampaign');
        $query->condition('p_id', $postalcampaign_id);
        $result = $query->execute();
        $row = $result->fetchObject();
        $query1 = db_select('postercampaign_poster');
        $query1->fields('postercampaign_poster');
        $query1->condition('p_id', $postalcampaign_id);
        $result1 = $query1->execute();
        while ($row1 = $result1->fetchObject()) {
		$suffix="";
		$ext = explode(".", $row1->name);
		if(($ext[1]=="png")||($ext[1]=="PNG")||($ext[1]=="jpeg")||($ext[1]=="JPEG")||($ext[1]=="jpg")||($ext[1]=="JPG")){
                 $suffix='<img style="width:100px; padding-left :20px;height:100px" src=' . $GLOBALS['base_url'] . "/campaign_posters/" . str_replace(' ', '_', $row->p_name) . "/" . $row1->name . ' />';
		}else{
		$suffix='<a href=' . $GLOBALS['base_url'] . "/campaign_posters/" . str_replace(' ', '_', $row->p_name) . "/" . $row1->name ." target="."_blank >".$row1->name.'</a>';
		}

            $form['postercheck@' . $row1->id] = array(
                '#type' => 'checkbox',
		'#field_suffix' => $suffix,
		
		
            );
        }
        $form['enduploadposter'] = array(
            '#markup' => '',
            '#prefix' => '',
            '#suffix' => '</tr></table></div>'
        );
        
        $form['poster_fieldset'] = array(
            '#type' => 'fieldset',
            '#tree' => TRUE,
            '#prefix' => '<div id="poster-fieldset-wrapper">',
            '#suffix' => '</div>'
        );
        if (empty($form_state['num_poster'])) {
            $form_state['num_poster'] = 1;
        }
        for ($i = 0; $i < $form_state['num_poster']; $i++) {
            $form['poster_fieldset'][$i]['name'] = array(
                '#title' => t('Add poster'),
                '#type' => 'file',
                '#weight' => '5',
                '#description' => t('Upload an poster'),
                '#name' => 'files[poster_fieldset_' . $i . '_name]'
            );
        }
        $form['poster_fieldset']['add_name'] = array(
            '#type' => 'submit',
            '#value' => t('Add poster'),
            '#limit_validation_errors' => array(),
            '#submit' => array(
                'poster_add_more_add_one'
            ),
            '#ajax' => array(
                'callback' => 'poster_add_more_callback',
                'wrapper' => 'poster-fieldset-wrapper'
            )
        );
        if ($form_state['num_poster'] > 1) {
            $form['poster_fieldset']['remove_name'] = array(
                '#type' => 'submit',
                '#value' => t('Remove Poster'),
                '#limit_validation_errors' => array(),
                '#submit' => array(
                    'poster_add_more_remove_one'
                ),
                '#ajax' => array(
                    'callback' => 'poster_add_more_callback',
                    'wrapper' => 'poster-fieldset-wrapper'
                )
            );
        }
        if ($no_js_use) {
            if (!empty($form['poster_fieldset']['remove_name']['#ajax'])) {
                unset($form['poster_fieldset']['remove_name']['#ajax']);
            }
            unset($form['poster_fieldset']['add_name']['#ajax']);
        }
    }
    $form["postercampaign_id"] = array(
        "#type" => "hidden",
        "#value" => $postalcampaign_id
    );
    $form["submit"] = array(
        "#type" => "submit",
        "#value" => "Submit"
    );
    return $form;
}

function postalcampaign_add_form_submit($form, &$form_state)
{
  $v = $form_state["values"];
  $posterupload = 0;
    if ($v["postercampaign_id"]) {
        $query = db_update('postercampaign');
        $query->fields(array(
            'foss_name' => $v["foss_select"],
            'p_name' => $v["name"],
            'startdate' => $v["start_date"],
            'body' => $v["body"]
        ));
        $query->condition('p_id', $v["postercampaign_id"]);
        $result = $query->execute();
  
        /* For editing Event images */
        /* For deleting existing images */
        /* For deleting existing images */
        $deletecounter = 0;
        $query_poster = db_select('postercampaign_poster');
        $query_poster->fields('postercampaign_poster');
        $query_poster->condition('p_id', $v["postercampaign_id"]);
        $result_poster = $query_poster->execute();
	$root_path=posters_path();
        while ($row_poster = $result_poster->fetchObject()) {
            if ($form_state['values']['postercheck@' . $row_poster->id] == 1) {
                if (file_exists($root_path.$row_poster->path)) {
                    unlink($root_path.$row_poster->path);
                    $query2 = db_delete('postercampaign_poster');
                    $query2->condition('id', $row_poster->id);
                    $delete_poster = $query2->execute();
                    if ($delete_poster != 0) {
                        $deletecounter++;
                    }
                } else {
                    drupal_set_message('Error Could not delete : ' . $row_poster->name . ', file does not exist', 'error');
                }
            }
        }
        /* For adding more images to existing event */
           $items = array();
        $root_path = posters_path();
        $eventfolder = str_replace(' ', '_', $v["name"]);
        $dest_path = $eventfolder . '/';
        if (!is_dir($root_path . $dest_path)) {
            mkdir($root_path . $dest_path);
        }
        /* uploading files */
        foreach ($_FILES['files']['name'] as $file_form_name => $file_name) {
            if ($file_name) {
                if (file_exists($root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    drupal_set_message(t("Error uploading file. File !filename already exists.", array(
                        '!filename' => $_FILES['files']['name'][$file_form_name]
                    )), 'error');
                    return;
                }
                $posterupload = 0;
                /* uploading file */
                if (move_uploaded_file($_FILES['files']['tmp_name'][$file_form_name], $root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    $posterquery = "
	INSERT INTO postercampaign_poster
	(p_id,name,path)
	VALUES
	(:p_id,:name, :path)
	";
                    $posterargs = array(
                        ":p_id" => $v["postercampaign_id"],
                        ":name" => $_FILES['files']['name'][$file_form_name],
                        ":path" => $dest_path . $_FILES['files']['name'][$file_form_name]
                    );
                    /* storing the row id in $result */
                    $posterresult = db_query($posterquery, $posterargs, array(
                        'return' => Database::RETURN_INSERT_ID
                    ));
                    if ($posterresult != 0) {
                        $posterupload++;
                    }
                    drupal_set_message($file_name . ' uploaded successfully.', 'status');
                } else {
                    drupal_set_message('Error uploading file : ' . $dest_path . $file_name, 'error');
                }
            }
        }
        if (!$result && $posterupload == 0 && $deletecounter == 0) {
            drupal_set_message("Something went wrong, please try again.", "error");
        } else {
            drupal_set_message(" Postal Campaign updated successfully", "status");
            if ($posterupload != 0) {
                drupal_set_message("Event's Poster are added successfully", "status");
            }
            if ($deletecounter != 0) {
                drupal_set_message("Event's Poster are deleted successfully", "status");
            }
        }
    } else {
        $query = "
	INSERT INTO postercampaign
	(foss_name,p_name, startdate,body)
	VALUES	(:foss_name,:p_name, :startdate,:body)
	";
        $args = array(
            ":foss_name" => $v["foss_select"],
            ":p_name" => $v["name"],
            ":startdate" => $v["start_date"],
            ":body" => $v["body"]
        );
        /* storing the row id in $result */
        $result = db_query($query, $args, array(
            'return' => Database::RETURN_INSERT_ID
        ));
       
        /* For adding poster of events*/
        $items = array();
        $root_path = posters_path();
        $eventfolder = str_replace(' ', '_', $v["name"]);
        $dest_path = $eventfolder . '/';
        if (!is_dir($root_path . $dest_path)) {
            mkdir($root_path . $dest_path);
        }
        /* uploading files */
        foreach ($_FILES['files']['name'] as $file_form_name => $file_name) {
            if ($file_name) {
                if (file_exists($root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    drupal_set_message(t("Error uploading file. File !filename already exists.", array(
                        '!filename' => $_FILES['files']['name'][$file_form_name]
                    )), 'error');
                    return;
                }
                $posterupload = 0;
                /* uploading file */
                if (move_uploaded_file($_FILES['files']['tmp_name'][$file_form_name], $root_path . $dest_path . $_FILES['files']['name'][$file_form_name])) {
                    $posterquery = "
	INSERT INTO postercampaign_poster
	(p_id,name,path)
	VALUES
	(:p_id,:name, :path)
	";
                    $posterargs = array(
                        ":p_id" => $result,
                        ":name" => $_FILES['files']['name'][$file_form_name],
                        ":path" => $dest_path . $_FILES['files']['name'][$file_form_name]
                    );
                    /* storing the row id in $result */
                    $posterresult = db_query($posterquery, $posterargs, array(
                        'return' => Database::RETURN_INSERT_ID
                    ));
                    if ($posterresult != 0) {
                        $posterupload++;
                    }
                    drupal_set_message($file_name . ' uploaded successfully.', 'status');
                } else {
                    drupal_set_message('Error uploading file : ' . $dest_path . $file_name, 'error');
                }
            }
        }
        if (!$result) {
            drupal_set_message("Something went wrong, please try again.", "error");
        } else {
            drupal_set_message("Postal Campaign added successfully", "status");
            if ($posterupload != 0) {
                drupal_set_message("Event's Poster are added successfully", "status");
            }
        }
    }
}


function poster_add_more_callback($form, $form_state)
{
    return $form['poster_fieldset'];
}


function poster_add_more_add_one($form, &$form_state)
{
    $form_state['num_poster']++;
    $form_state['rebuild'] = TRUE;
    //$form_state['no_redirect'] = TRUE;
}


function poster_add_more_remove_one($form, &$form_state)
{
    if ($form_state['num_poster'] > 1) {
        $form_state['num_poster']--;
    }
    $form_state['rebuild'] = TRUE;
}


function postalcampaign_add_form_validate($form, &$form_state)
{
    if (isset($_FILES['files'])) {
        /* check for valid filename extensions */
        foreach ($_FILES['files']['name'] as $file_form_name => $file_name) {
            if ($file_name) {
                if (strstr($file_form_name, 'poster_fieldset'))
                    $file_type = 'I';
                switch ($file_type) {
                    case 'I':
                        $allowed_extensions_str = variable_get('events_poster_extensions', '');
                        break;
                }
                $allowed_extensions = explode(',', $allowed_extensions_str);
		$allowed_ext= explode('.', strtolower($_FILES['files']['name'][$file_form_name]));
                $temp_extension = end($allowed_ext);
                if (!in_array($temp_extension, $allowed_extensions))
                    form_set_error($file_form_name, t('Only file with ' . $allowed_extensions_str . ' extensions can be uploaded.'));
                
            }
        }
    }
}


function postalcampaign_edit_all($postercampaign_id = 0)
{
    $page_content = "";
    if ($postercampaign_id) {
        $postalcampaign_add_form = drupal_get_form("postalcampaign_add_form", $postercampaign_id);
        $page_content = drupal_render($postalcampaign_add_form);
    } else {
        $query = db_select('postercampaign');
        $query->fields('postercampaign');
        $query->orderBy('startdate', 'DESC');
        $result = $query->execute();
        $headers = array(
            "Name",
            "Start Date",
            ""
        );
        $rows = array();
        $options = array(
            'attributes' => array(
                'class' => 'delete'
            )
        );
        while ($row = $result->fetchObject()) {
            $item = array(
                $row->p_name,
                $row->startdate,
                l("Edit", "postalcampaign/edit/{$row->p_id}") . " | " . l("Delete", "postalcampaign/delete/{$row->p_id}", $options)
            );
            array_push($rows, $item);
        }
        $page_content = bootstrap_table_format($headers, $rows);
    }
    return $page_content;
}


function postalcampaign_delete_all($postercampaign_id = 0)
{
    $page_content = "";
    $query = db_select('postercampaign');
    $query->fields('postercampaign');
    $query->condition('p_id', $postercampaign_id);
    $result = $query->execute();
    $row = $result->fetchObject();
    $root_path = posters_path();
    $eventfolder = str_replace(' ', '_', $row->p_name);
    $dest_path = $eventfolder;
    $dir_path = $root_path . $dest_path;
    if (is_dir($dir_path)) {
        $res = delete_directory($dir_path);
        if (!$res) {
            drupal_set_message(t("Cannot delete Campaign directory : " . $dir_path . ". Please contact administrator."), 'error');
            return;
        } else {
            $query2 = db_delete('postercampaign_poster');
            $query2->condition('p_id', $postercampaign_id);
            $result2 = $query2->execute();
        }
    } else {
        drupal_set_message(t("Event directory not present : " . $dir_path . ". Skipping deleting directory."), 'status');
    }
  
    $query3 = db_delete('postercampaign');
    $query3->condition('p_id', $postercampaign_id);
    $result3 = $query3->execute();
   
    if (!$result3) {
        drupal_set_message("Something went wrong, please try again.", "error");
    } else {
        if (!$result2) {
            drupal_set_message("Campaign Deleted successfully", "status");
        } else {
            drupal_set_message("Campaign, related poster material are deleted successfully", "status");
        }
    }
    return $page_content;
}

function postalcampaign_view_details_all($postalcampaign_id = 0)
{

    $page_content = "";
    $row = "";
    $row1 = "";
    $query = db_select('postercampaign');
    $query->fields('postercampaign');
    $query->condition('p_id', $postalcampaign_id);
    $result = $query->execute();
    $row = $result->fetchObject();

    //createZipOfPosterMaterial($row->p_name);
    $startdate = $row->startdate;
    $eventfolder = str_replace(' ', '_',$row->p_name);
    $page_content .= "<table class='table table-bordered table-hover' >";
    $page_content .= "<tr><td><b>Name</b></td><td>{$row->p_name}</td></tr>";
    $page_content .= "<tr><td><b>Date</b></td><td>$startdate</td></tr>";
    $page_content .= "<tr><td><b>Details</b></td><td>{$row->body}</td></tr>";
    $page_content .= "</table>";

    $page_content .= "<table class='table table-bordered table-hover'><tr>";
    $page_content .= "<td style='font-size:20px;padding-bottom:20px'>Campaign Posters

 <a style='float:right' title='Download all material of Campign' href=" . $GLOBALS['base_url'] ."/postalcampaign/download/".$eventfolder.">Click to Download</a></td>";
$page_content .="</tr>";
	
    $query = db_select('postercampaign_poster');
    $query->fields('postercampaign_poster');
    $query->condition('p_id', $postalcampaign_id);
    $result = $query->execute();
    $num_of_results = $result->rowCount();
    if ($num_of_results != 0) {
        $page_content .= "<tr><td>";
        while ($row1 = $result->fetchObject()) {
	$ext = explode(".", $row1->name);
	if(($ext[1]=="png")||($ext[1]=="PNG")||($ext[1]=="jpeg")||($ext[1]=="JPEG")||($ext[1]=="jpg")||($ext[1]=="JPG")){
            $page_content .= "<a class='fancybox' rel='gallery1' href=" . $GLOBALS['base_url'] . "/campaign_posters/" . str_replace(' ', 			'_', $row->p_name) . "/" . $row1->name . "><img title='Click to view' style='width:120px; height:200px; padding-right:20px;padding-bottom:10px' 			src=" . $GLOBALS['base_url'] . "/campaign_posters/" . str_replace(' ', '_', $row->p_name) . "/" . $row1->name . " /></a>";

	}
	}
        $page_content .= "</td></tr></table>";
	$page_content .= "<h4>Poster's PDF</h4>";
	$query = db_select('postercampaign_poster');
        $query->fields('postercampaign_poster');
        $query->condition('p_id', $postalcampaign_id);
        $result = $query->execute();

	while ($row1 = $result->fetchObject()) {
	$ext = explode(".", $row1->name);
	if(($ext[1]=="pdf")||($ext[1]=="PDF")){
            $page_content .= "<a href=" . $GLOBALS['base_url'] . "/campaign_posters/" . str_replace(' ','_', $row->p_name) . "/" . $row1->name . " target='_blank' title='Click to view'>".$row1->name."</a>";

	}
	}
	
    } else {
        $page_content .= "</table>";
    }
    
    return $page_content;
}

function createZipOfPosterMaterial($name = ""){

$root_path = posters_path();
$eventfolder = $name;
$zip_filename=$root_path.$eventfolder.".zip";

$zip = new ZipArchive();
// open archive
if ($zip->open($root_path.$eventfolder.".zip", ZIPARCHIVE::CREATE) !== TRUE) {
die ("Could not open archive");
}
$iterator = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($root_path.$eventfolder."/"));
// iterate over the directory
// add each file found to the archive
foreach ($iterator as $key=>$value) {

//Check for valid file
if(strpos(basename($key),'.')!=0){
$zip->addFile(realpath($key), basename($key)) or die ("ERROR: Could not add file: $key");
}
}
// close and save archive
$zip->close();

 /* download zip file */
    header('Content-Type: application/zip');
    header('Content-disposition: attachment; filename="'.$eventfolder.'.zip"');
    header('Content-Length: ' . filesize($zip_filename));
    ob_clean();
    //flush();
    readfile($zip_filename);
    unlink($zip_filename);
}

function postalcampaign_view_withoutfilter(){
$foss_name="";
$startdate="1960-01-01";
$enddate=date("Y-m-d");
$pagecontent=postalcampaign_view_all($foss_name, $startdate, $enddate);
return $pagecontent;
}

function postalcampaign_view_all($foss_name, $startdate, $enddate)
{

    if ($startdate == "") {
        $startdate = "1960-01-01";
    } else {
        $startdate = $startdate;
    }
    if ($enddate == "") {
        $enddate = date("Y-m-d");
        //$enddate="";
    } else {
        $enddate = $enddate;
    }

  if (strlen($foss_name) == 0 || $foss_name == "null") {
        $foss_name= "%";
    } else {
        $foss_name = $foss_name;
    }
    $page_content = "";
    $headers = array(
        "Name",
        "Date",
        ""
    );
    
    $rows1 = array();
    $query1 = db_select('postercampaign');
    $query1->fields('postercampaign');
    $query1->condition('foss_name', $foss_name,'LIKE');
    $query1->condition('startdate', $startdate, '>=');
    $query1->condition('startdate', $enddate, '<=');
    $query1->orderBy('startdate', 'DESC');
    $result1 = $query1->execute();
    while ($row = $result1->fetchObject()) {
        $item = array(
            $row->p_name,
            $row->startdate,
          
            "<a href=" . $GLOBALS['base_url'] . "/postalcampaign/view_details/{$row->p_id} target='_blank' title='Click to view detail'>Details</a>"
        );
        array_push($rows1, $item);
    }
    $page_content .= bootstrap_table_format($headers, $rows1);
    return $page_content;
}

/* Spoken Tutorials  */


function spokentutorial_display_all()
{
    $page_content = "";
    $spokentutorial_add_form = drupal_get_form("spokentutorial_add_form");
    $page_content = drupal_render($spokentutorial_add_form);
    return $page_content;
}

function spokentutorial_add_form($form, &$form_state, $spokentutorial_id = 0, $no_js_use = FALSE)
{
$query = "
       SELECT * FROM spokentutorial
       WHERE v_id = :spokentutorial_id
       ";
    $args = array(
        ":spokentutorial_id" => $spokentutorial_id
    );
    $result = db_query($query, $args);
    $row = $result->fetchObject();
    $form = array();


	 if ($spokentutorial_id == 0) {
        $form['foss_select'] = array(
            '#type' => 'select',
            '#title' => t('Select FOSS under which event took place '),
            '#options' => get_first_dropdown_options_foss_name(),
            '#default_value' => '',
            "#required" => TRUE
        );
     
        $form["count"] = array(
            "#type" => "textfield",
            "#title" => "Count of Videos :",
            "#default_value" => '',
            "#required" => TRUE
        );
	
	$form["videolink"] = array(
            "#type" => "textfield",
            "#title" => "Link of Video :",
            "#default_value" => '',
            "#required" => TRUE
        );


	}else{
	$form = array();
        $form['foss_select'] = array(
            '#type' => 'select',
            '#title' => t('Select FOSS under which event took place '),
            '#options' => get_first_dropdown_options_foss_name(),
            '#default_value' => $row->foss_name,
            "#required" => TRUE
        );
        $form["count"] = array(
            "#type" => "textfield",
            "#title" => "Enter Count of Videos",
            "#default_value" => $row->count,
            "#required" => TRUE
        );
	$form["videolink"] = array(
            "#type" => "textfield",
            "#title" => "Link of Video :",
            "#default_value" => $row->link,
            "#required" => TRUE
        );


	}
    $form["spokentutorial_id"] = array(
        "#type" => "hidden",
        "#value" => $spokentutorial_id
    );
    $form["submit"] = array(
        "#type" => "submit",
        "#value" => "Submit"
    );
    return $form;

}

function spokentutorial_add_form_submit($form, &$form_state)
{
  $v = $form_state["values"];
 
    if ($v["spokentutorial_id"]) {
	$query = db_update('spokentutorial');
        $query->fields(array(
            'foss_name' => $v["foss_select"],
            'count' => $v["count"],
            'link' => $v["videolink"],
            
        ));
        $query->condition('v_id', $v["spokentutorial_id"]);
        $result = $query->execute();
   }else{
	   $query = "
	INSERT INTO spokentutorial
	(foss_name,count,link)
	VALUES	(:foss_name,:count, :link)
	";
        $args = array(
            ":foss_name" => $v["foss_select"],
            ":count" => $v["count"],
            ":link" => $v["videolink"]
           
        );
        /* storing the row id in $result */
        $result = db_query($query, $args, array(
            'return' => Database::RETURN_INSERT_ID
        ));
	}
	
	 if (!$result) {
            drupal_set_message("Something went wrong, please try again.", "error");
        } else {
            drupal_set_message("Spoken tutorial details added successfully", "status");
            
        }
}
function spokentutorial_view_all($foss_name)
{

  if (strlen($foss_name) == 0 || $foss_name == "null") {
        $foss_name= "%";
    } else {
        $foss_name = $foss_name;
    }
    $page_content = "";
    $headers = array(
        "Foss Type",
        "Count of Video"
        
    );
    
    $rows1 = array();
    $query1 = db_select('spokentutorial');
    $query1->fields('spokentutorial');
    $query1->condition('foss_name', $foss_name,'LIKE');
    $result1 = $query1->execute();
    while ($row = $result1->fetchObject()) {
        $item = array(
            $row->foss_name,
            "<a href='".$row->link ."' target='_blank' title='Click to view tutorial'>". $row->count."</a>",
        );
        array_push($rows1, $item);
    }
    $page_content .= bootstrap_table_format($headers, $rows1);
    return $page_content;
}

function fossee_stats_init()
{
    $path = drupal_get_path('module', 'fossee_stats');
    drupal_add_js($path . '/js/all_charts.js');
   // drupal_add_css(drupal_get_path("module", "fossee_stats") . "/css/fossee_stats_base.css");
   drupal_add_css(drupal_get_path("module", "fossee_stats") . "/css/bootstrap.css");
    //drupal_add_css(drupal_get_path("module", "fossee_stats") . "/css/fossee_stats_main.css");
    drupal_add_js(drupal_get_path("module", "fossee_stats") . "/js/confirm.js");
    drupal_add_js(drupal_get_path("module", "fossee_stats") . "/js/testimonials_script.js");
}