<?php
    include_once("CUserSession.php");
    include_once("CProject.php");
    include_once("helper.php");
    include_once("CUtil.php");
    include_once("CStandard.php");
    include_once("CEnums.php");
    include_once("CProjectSolutions.php");
    include_once("CProjectTeams.php");

    // error_reporting(0);

    class UD_Project_Info{

        public $sl_no;
        public $_id;
        public $creating_user_id;
        public $project_title;
        public $project_owner;
        public $project_architect;
        public $habitable_floor_area;
        public $site_area;
        public $pre_certification;
        public $project_cost;
        public $project_isud_creation_date;
        public $project_a_start_date;               // Anticipated start date
        public $project_a_end_date;                 // Anticipated end date
        public $building_type;
        public $project_scope;                  // 0 - Building, 1 - Building and Site
        public $contact_person;
        public $contact_email;
        public $contact_telephone;
        public $contact_telephone_c_code;
        public $project_address_line_1;
        public $project_address_line_2;
        public $project_city;
        public $project_state;
        public $project_country;
        public $project_zipcode;                    // Zipcode/Pincode
        public $isud_status;                        // 0 - in-progress, 1 - completed, 2 - under review, 3- under review but oprn for discussion
        public $demo_project;
        public $project_submission_type;
        public $project_certification_cost;
        public $project_type;                       // 1- Entirely New Facility, 2- Addition to Existing Facility, 3- Alteration to Existing Facility, 4- Existing Facility As-Built, 5- Facility Registered as Historic.
        public $project_phase;
 
        function populateFromDBRow( $row ){

            $this->sl_no = $row["sl_pr_no"];
            $this->_id = $row["_id"];
            $this->creating_user_id = $row["creating_user_id"];
            $this->project_title = $row["project_title"];
            $this->project_owner = $row["project_owner"];
            $this->project_architect = $row["project_architect"];
            $this->habitable_floor_area = $row["habitable_floor_area"];
            $this->site_area = $row["site_area"];
            $this->pre_certification = $row["pre_certification"];
            $this->project_cost = $row["project_cost"];
            $this->project_isud_creation_date = $row["project_isud_creation_date"];
            $this->project_a_start_date = $row["project_a_start_date"];
            $this->project_a_end_date = $row["project_a_end_date"];
            $this->building_type = $row["building_type"];
            $this->project_scope = $row["project_scope"];
            $this->contact_person = $row["project_contact_person"];
            $this->contact_email = $row["project_contact_email"];
            $this->contact_telephone = $row["project_contact_telephone"];
            $this->contact_telephone_c_code = $row["project_contact_telephone_c_code"];
            $this->project_address_line_1 = $row["project_address_line_1"];
            $this->project_address_line_2 = $row["project_address_line_2"];
            $this->project_city = $row["project_city"];
            $this->project_state = $row["project_state"];
            $this->project_country = $row["project_country"];
            $this->project_zipcode = $row["project_zipcode"];
            $this->isud_status = $row["isud_status"];
            $this->demo_project = $row["demo_project"];
            $this->project_submission_type = $row["project_submission_type"];
            $this->project_certification_cost = $row["project_certification_cost"];
            $this->project_type = $row["project_type"];
            $this->project_phase = $row["project_phase"];
        }

    }

    // Validate that current user is project owner
    function validateCurrentUser($project_id){

        $sql_conn = mysqli_connection();
        if(isLoggedIn()){

            // $where = "WHERE `user_id` like '".$_SESSION['user']->_id."' AND status=1 AND project_permission_id IN ('1','2') AND `project_id` LIKE '".$project_id."'";

            // $sql = "SELECT * FROM  project_team  ".$where;

            $where = "WHERE `user_id` like '".$_SESSION['user']->_id."' AND status= 1 AND project_permission_id IN ('1','2') AND `project_id` LIKE '".$project_id."'";

            $sql = "SELECT * FROM  project_team a JOIN (SELECT _id, project_title from UD_P_Info) b on a.project_id = b._id ".$where;
            $res = array();
            $res["response"] = false;
            $res["data"] = [];
            try{
                $result = mysqli_query($sql_conn, $sql);
                $res["response"] = mysqli_num_rows($result) >= 1;
                while($row = mysqli_fetch_array($result)) {

                    // hide fields
                    $res["project_title"] = $row["project_title"];
                    $res["data"][] = $row;
                }
                mysqli_close($sql_conn);
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }
        } else {
            $res['message'] = "Error : User not logged in";
        }

        $r = json_encode($res);
        return $r;

	}


	// Getting Projects
    // updateRetainedProject(['pr_4e814507fa', 'pr_4e814507fa'], 'sreedhar redy-88be0900bd');

    function updateRetainedProject($_id, $creating_user_id){
        $sql_conn = mysqli_connection();

        $project = new UD_Project_Info();
        $project->_id = $_id;
        $project->creating_user_id = $creating_user_id;
        // print($project->_id);
        // print(list(['pr_4e814507fa', 'pr_4e814507fa']));
        $projectIds = implode("','", $_id);
        $project = Util::escapeObject($project);
        $update_fields = " `isud_status` = 4 "; // 4 is inactive. Please refer to DB for other details.
        // print($projectIds);
        $param = "s";
        $query = "UPDATE `UD_P_Info` SET ".$update_fields." WHERE _id NOT IN ('".$projectIds."') and creating_user_id = ? and isud_status <> 1";
        // print($query);
        if (!($stmt = $sql_conn->prepare($query))){
            $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $resp["response"] = false;
        }

        if(!($stmt->bind_param($param,
                            $project->creating_user_id
                      ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if (!($ret = $stmt->execute())) {
            $resp["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($ret){
            $resp["response"] = true;
            $resp["message"] = "Success";
        }

        $stmt->close();
        mysqli_close($sql_conn);
        // echo($ret);
        // die();
        return json_encode($resp);
    }

	function getProjectInfo($project_id){

        

		$sql_conn = mysqli_connection();
        if(isLoggedIn()){

            if($_SESSION['user']->authtype_id == TypeOfUser::CLIENT_USER || $_SESSION['user']->authtype_id == TypeOfUser::CLIENT_ORGANIZATION)  
            {


                // $where = "WHERE `creating_user_id` like '".$_SESSION['user']->_id."'";
                if($project_id != -1){
                    // $where = $where." AND `_id` like '".$project_id."'";
                    $where = " WHERE b.`_id` like '".$project_id."'";
                     $sql = "SELECT *, 
                                case 
                                    when project_submission_type = 0 then 'Self'
                                    when project_submission_type = 1 then 'isUD'
                                end as project_submission_type_text,
                                case 
                                    when isud_status = 0 then 'Ongoing'
                                    when isud_status = 1 then 'Completed'
                                    when isud_status = 2 then 'Review'
                                    when isud_status = 3 then 'Pending'
                                end as isud_status_text 
                        FROM (SELECT * FROM `project_team` WHERE `user_id` like '".$_SESSION['user']->_id."' AND status = 1) as a 
                        JOIN `UD_P_Info` as b ON a.project_id = b._id
                        INNER JOIN UD_P_Projects as c ON (b.`_id` = c.`project_id`)
                        JOIN (SELECT _id as c_user_id, subscription_type, free_trial_expiry_date, supporter_expiry_date, innovator_expiry_date from `Users_temp`) as d ON d.c_user_id = b.creating_user_id
                        
                        ".$where." ORDER BY b.`sl_pr_no`";

                        // JOIN subscription_types as e ON e.type = d.subscription_type
                    
                }
                else{
                    $sql = "SELECT *, 
                                case 
                                    when project_submission_type = 0 then 'Self'
                                    when project_submission_type = 1 then 'isUD'
                                end as project_submission_type_text,
                                case 
                                    when isud_status = 0 then 'Ongoing'
                                    when isud_status = 1 then 'Completed'
                                    when isud_status = 2 then 'Review'
                                    when isud_status = 3 then 'Pending'
                                end as isud_status_text 
                        FROM (SELECT * FROM `project_team` WHERE `user_id` like '".$_SESSION['user']->_id."' AND status = 1) as a 
                        JOIN `UD_P_Info` as b ON a.project_id = b._id
                        INNER JOIN UD_P_Projects ON (b.`_id` = `UD_P_Projects`.`project_id`)
                        JOIN (SELECT _id as c_user_id, subscription_type, free_trial_expiry_date, supporter_expiry_date, innovator_expiry_date from `Users_temp`) as d ON d.c_user_id = b.creating_user_id
                        ORDER BY b.`sl_pr_no`";
               
                    }
            }
            // my change to remove where clause to list all the project for admin
            // if user is admin show him all the projects
            // to do : replace the 1 with the usertype ENUM
            if($_SESSION['user']->authtype_id == TypeOfUser::ADMIN || $_SESSION['user']->authtype_id == TypeOfUser::SUPER_ADMIN)  // If the user is super admin or admin
            {

                // "SELECT *, 
                //             case 
                //                 when project_submission_type = 0 then 'Self'
                //                 when project_submission_type = 1 then 'isUD'
                //             end as project_submission_type_text,
                //             case 
                //                 when isud_status = 0 then 'Ongoing'
                //                 when isud_status = 1 then 'Completed'
                //                 when isud_status = 2 then 'Review'
                //                 when isud_status = 3 then 'Pending'
                //             end as isud_status_text,
                //             case
                //                 when b.`creating_user_id` = '".$_SESSION['user']->_id."' then 4 else 5
                //             end as is_admin_project

                //         FROM (SELECT * FROM `project_team`) as a 
                //         JOIN `UD_P_Info` as b ON a.project_id = b._id
                //         INNER JOIN UD_P_Projects as c ON (b.`_id` = c.`project_id`)
                //         JOIN (SELECT _id as c_user_id, subscription_type, free_trial_expiry_date, supporter_expiry_date, innovator_expiry_date from `Users_temp`) as d ON d.c_user_id = b.creating_user_id
                //         JOIN subscription_types as e ON e.type = d.subscription_type
                //         ".$where." ORDER BY b.`sl_pr_no`"
                 
                $where = "";
                if($project_id != -1)
                {
                    $where = $where." WHERE `_id` like '".$project_id."'";
                    $sql = "SELECT *, 
                            case 
                                when project_submission_type = 0 then 'Self'
                                when project_submission_type = 1 then 'isUD'
                            end as project_submission_type_text,
                            case 
                                when isud_status = 0 then 'Ongoing'
                                when isud_status = 1 then 'Completed'
                                when isud_status = 2 then 'Review'
                                when isud_status = 3 then 'Pending'
                            end as isud_status_text,
                            case
                                when b.`creating_user_id` = '".$_SESSION['user']->_id."' then 4 else 5
                            end as is_admin_project

                        FROM `UD_P_Info` as b
                        INNER JOIN UD_P_Projects as c ON (b.`_id` = c.`project_id`)
                        JOIN (SELECT _id as c_user_id, subscription_type, free_trial_expiry_date, supporter_expiry_date, innovator_expiry_date from `Users_temp`) as d ON d.c_user_id = b.creating_user_id
                        
                        ".$where." ORDER BY b.`sl_pr_no`";

                        // JOIN subscription_types as e ON e.type = d.subscription_type
                }
                else {$sql = "SELECT *, 
                            case 
                                when project_submission_type = 0 then 'Self'
                                when project_submission_type = 1 then 'isUD'
                            end as project_submission_type_text,
                            case 
                                when isud_status = 0 then 'Ongoing'
                                when isud_status = 1 then 'Completed'
                                when isud_status = 2 then 'Review'
                                when isud_status = 3 then 'Pending'
                            end as isud_status_text,
                            case
                                when `UD_P_Info`.`creating_user_id` = '".$_SESSION['user']->_id."' then 4 else 5
                            end as is_admin_project
                        FROM  UD_P_Info 
                        INNER JOIN UD_P_Projects ON (`UD_P_Info`.`_id` = `UD_P_Projects`.`project_id`) 
                        ".$where."ORDER BY `UD_P_Info`.`sl_pr_no`";
                    }
            }
            else if($_SESSION['user']->authtype_id == TypeOfUser::REVIEWER){  // If the user is reviewer
                
              $user_email = $_SESSION['user']->email_id;
              $where = "WHERE `creating_user_id` like '".$_SESSION['user']->_id."' OR `reviewer` like '".$user_email."'";
              if($project_id != -1)
              {
                 $where = "WHERE `reviewer` like '".$user_email."'";
                 $where = $where." AND `_id` like '".$project_id."'";
              }
              $sql = "SELECT *, 
                            case 
                                when project_submission_type = 0 then 'Self'
                                when project_submission_type = 1 then 'isUD'
                            end as project_submission_type_text,
                            case 
                                when isud_status = 0 then 'Ongoing'
                                when isud_status = 1 then 'Completed'
                                when isud_status = 2 then 'Review'
                                when isud_status = 3 then 'Pending'
                            end as isud_status_text 
                    FROM  UD_P_Info INNER JOIN UD_P_Projects ON (`UD_P_Info`.`_id` = `UD_P_Projects`.`project_id`) ".$where."ORDER BY `UD_P_Info`.`sl_pr_no`";
            }
            $udProjectInfo = array();
            $res = array();
          
            try{
               
                $result = mysqli_query($sql_conn, $sql);
                
                while($row = mysqli_fetch_array($result)) {
                    //$project = new UD_Project_Info();
                    //$project->populateFromDBRow($row);
                  
                    // hide fields
                    // unset($row["creating_user_id"]);
                    unset($row["user_id"]);
                    unset($row["sl_no"]);
                    foreach($row as $key => $val){
                        if(gettype($key) === "integer"){
                            unset($row[$key]);
                        }
                    }
                    $row["is_requesting_user_owner"] = $_SESSION['user']->_id == $row["creating_user_id"];
                    $row["is_requesting_user_reviewer"] = $_SESSION['user']->email_id== $row["reviewer"];
                    
                    $udProjectInfo[] = $row;
                }
                mysqli_close($sql_conn);
                $res = $udProjectInfo;
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }
        } else {
            $res['message'] = "Error : User not logged in";
        }
       
        $r = json_encode($res);
        // print($r);
        return $r;
	}

    function createProjectInfo($project_title, $project_owner, $project_architect, $habitable_floor_area, $site_area, $pre_certification, $project_cost, $project_a_start_date, $project_a_end_date, $building_type, $def_service_type, $def_certification_type, $project_scope, $contact_person, $contact_email, $contact_telephone, $contact_telephone_c_code, $project_address_line_1, $project_address_line_2, $project_city, $project_state, $project_country, $project_zipcode, $project_submission_type, $project_type, $demo_project = 0, $project_phase='NONE'){

        $sql_conn = mysqli_connection();
        $sql_conn->begin_transaction();
 
        if(isLoggedIn()){
            $project = new UD_Project_Info();
            $project->_id = "pr_".Util::genRandText(10);
            $project->creating_user_id = $_SESSION['user']->_id;
            $project->project_title = $project_title;
            $project->project_owner = $project_owner;
            $project->project_architect = $project_architect;
            $project->habitable_floor_area = $habitable_floor_area;
            $project->site_area = $site_area;
            $project->pre_certification = $pre_certification;
            $project->project_cost = $project_cost;
            $project->project_a_start_date = !empty($project_a_start_date) ? $project_a_start_date : "0001:01:01";
            $project->project_a_end_date = !empty($project_a_end_date) ? $project_a_end_date : "0001:01:01";
            // $project->project_a_start_date = new DateTime($project_a_start_date);
            // $project->project_a_end_date = new DateTime($project_a_end_date);
            $project->building_type = $building_type;
            $project->service_type=$def_service_type;
            $project->certification_type=$def_certification_type;
            $project->project_scope = $project_scope;
            $project->contact_person = $contact_person;
            $project->contact_email = $contact_email;
            $project->contact_telephone = $contact_telephone;
            $project->contact_telephone_c_code = $contact_telephone_c_code;
            $project->project_address_line_1 = $project_address_line_1;
            $project->project_address_line_2 = $project_address_line_2;
            $project->project_city = $project_city;
            $project->project_state = $project_state;
            $project->project_country = $project_country;
            $project->project_zipcode = $project_zipcode;
            $project->demo_project = $demo_project;
            $project->project_submission_type = $project_submission_type;
            $project->project_type = $project_type;
            $project->project_phase = $project_phase;
            $project->project_certification_cost = getCertificationCost($project_submission_type,$habitable_floor_area);


            $project = Util::escapeObject($project);
            $ret["response"] = false;
            //$ret["data"] = var_dump($project);
            if (!($stmt = $sql_conn->prepare("INSERT INTO `UD_P_Info`(_id, creating_user_id, project_title, project_owner, project_architect, habitable_floor_area, site_area, pre_certification, project_cost, project_a_start_date, project_a_end_date, building_type, service_type, certification_type, project_scope, project_contact_person, project_contact_email, project_contact_telephone, project_contact_telephone_c_code,project_address_line_1, project_address_line_2, project_city, project_state, project_country, project_zipcode, demo_project, project_submission_type,project_certification_cost, project_type, project_phase) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"))){
                $ret["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error." ll";                
                
                return $ret;
            }            

            if(!($stmt->bind_param("sssssddsdsssssdsssssssssssddds",
                            $project->_id,
                            $project->creating_user_id,
                            $project->project_title,
                            $project->project_owner,
                            $project->project_architect,
                            $project->habitable_floor_area,
                            $project->site_area,
                            $project->pre_certification,
                            $project->project_cost,
                            $project->project_a_start_date,
                            $project->project_a_end_date,
                            $project->building_type,
                            $project->service_type,
                            $project->certification_type,
                            $project->project_scope,
                            $project->contact_person,
                            $project->contact_email,
                            $project->contact_telephone,
                            $project->contact_telephone_c_code,
                            $project->project_address_line_1,
                            $project->project_address_line_2,
                            $project->project_city,
                            $project->project_state,
                            $project->project_country,
                            $project->project_zipcode,
                            $project->demo_project,
                            $project->project_submission_type,
                            $project->project_certification_cost,
                            $project->project_type,
                            $project->project_phase
                        ))){
                $ret["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
                return ret;                
            }
            // print( "It is stmt being prepared-,$stmt");

            $res = $stmt->execute();
            $stmt->close();

            //Setup main project
            $projectReturnVal = createProject($sql_conn, $project->_id, $project->creating_user_id, 100, 0, 0, 0, 0, 0.0, "None");

            $projectTeamReturnVal = createProjectTeam($project->_id, $project->creating_user_id, 1, 1, 1);
            // && $projectTeamReturnVal["response"]            
            if (!($res)) {
                $ret["message"] =  "CProjectInfo res Execute failed: (" . $stmt->errno . ") " . $stmt->error;
                $ret["message"] .= "CProjectInfo res Execute failed: ".$projectReturnVal["message"];
                $sql_conn->rollBack();
            } 
            else if (!($res && $projectReturnVal["response"] && $projectTeamReturnVal["response"])) {
                $ret["message"] =  "CProjectInfo non res Execute failed: (" . $stmt->errno . ") " . $stmt->error;
                $ret["message"] .= "CProjectInfo Execute failed: ".$projectReturnVal["message"];
                $sql_conn->rollBack();
            } 
            else {
                $ret["response"] = true;
                $ret["message"] = $res;
                $ret["project_id"] = $project->_id;
                $sql_conn->commit();
            }
        } else {
            $ret["message"] = "Error : User not logged in";
        }
        mysqli_close($sql_conn);

        // create rows in UD_solutions table 
        // This creates row for each solution for this project. 
        // used for storing values of review checkbox implementation.
        create_project_solution_rows($project->_id);

        return $ret;
    }

   
 
    //Edited for updating the projectPhase alone from the project Overview Page.
    // updateProjectPhase('pr_e0fcee53e6', $project_phase='Pre_Design');
    function updateProjectPhase($id, $project_phase=''){
        $sql_conn = mysqli_connection();

        $ret = array();
        $ret['response'] = false;

        $project = new UD_Project_Info();
        $project->_id = $id;
        $project->project_phase = $project_phase;

        $project = Util::escapeObject($project);

        $update_clause .= " `project_phase` = '{$project->project_phase}'";

        $query = "UPDATE `UD_P_Info` SET ".$update_clause." WHERE `_id` like '".$project->_id."' ";
        
        // $query = "UPDATE `UD_P_Info` SET project_phase = ".$project->project_phase." WHERE _id = ".$project->_id;

        // $query = "INSERT INTO UD_P_Info(_id, project_phase) VALUES (".$project->_id.",".'$project->project_phase'.") ON DUPLICATE KEY UPDATE name = ".'$project->project_phase'.";"

        // print($query);

        if (!($stmt = $sql_conn->prepare($query))){
            $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $resp["response"] = false;
        }

        if (!($ret = $stmt->execute())) {
            $resp["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($ret){
            $resp["response"] = true;
            $resp["message"] = "";
        }

        $stmt->close();

        mysqli_close($sql_conn);
        // print(json_encode($resp));
        return json_encode($resp);

    }


    function updateProjectInfo($_id, $project_title, $project_owner, $project_architect, $habitable_floor_area, $site_area, $pre_certification, $project_cost,
                               $project_a_start_date, $project_a_end_date, $building_type, $project_scope, $contact_person, $contact_email, $contact_telephone, $contact_telephone_c_code,
                               $project_address_line_1, $project_address_line_2, $project_city, $project_state, $project_country, $project_zipcode, $project_submission_type, $isud_status = 0){
        $sql_conn = mysqli_connection();

        $project = new UD_Project_Info();
        $project->_id = $_id;
        $project->project_title = $project_title;
        $project->project_owner = $project_owner;
        $project->project_architect = $project_architect;
        $project->habitable_floor_area = $habitable_floor_area;
        $project->site_area = $site_area;
        $project->pre_certification = $pre_certification;
        $project->project_cost = $project_cost;
        $project->project_a_start_date = !empty($project_a_start_date) ? $project_a_start_date : "0001:01:01";
        $project->project_a_end_date = !empty($project_a_end_date) ? $project_a_end_date : "0001:01:01";
        $project->building_type = $building_type;
        $project->project_scope = $project_scope;
        $project->contact_person = $contact_person;
        $project->contact_email = $contact_email;
        $project->contact_telephone = $contact_telephone;
        $project->contact_telephone_c_code = $contact_telephone_c_code;
        $project->project_address_line_1 = $project_address_line_1;
        $project->project_address_line_2 = $project_address_line_2;
        $project->project_city = $project_city;
        $project->project_state = $project_state;
        $project->project_country = $project_country;
        $project->project_zipcode = $project_zipcode;
        $project->isud_status = $isud_status;
        $project->project_submission_type = $project_submission_type;
        $project->project_certification_cost = getCertificationCost($project_submission_type,$habitable_floor_area);

        $old_project_scope = get_project_scope($_id);

        $project = Util::escapeObject($project);
        $fields = " `project_title` = ?,
                    `project_owner` = ?,
                    `project_architect` = ?,
                    `habitable_floor_area` = ?,
                    `site_area` = ?,
                    `pre_certification` = ?,
                    `project_cost` = ?,
                    `project_a_start_date` = ?,
                    `project_a_end_date` = ?,
                    `building_type` = ?,
                    `project_scope` = ?,
                    `project_contact_person` = ?,
                    `project_contact_email` = ?,
                    `project_contact_telephone` = ?,
                    `project_contact_telephone_c_code` = ?,
                    `project_address_line_1` = ?,
                    `project_address_line_2` = ?,
                    `project_city` = ?,
                    `project_state` = ?,
                    `project_country` = ?,
                    `project_zipcode` = ?,
                    `project_submission_type` = ?,
                    `project_certification_cost` = ?";
        $param = "sssddsdsssdssssssssssdd";
        $query = "UPDATE `UD_P_Info` SET ".$fields." WHERE _id = ?";
        $param .= "s";

        if (!($stmt = $sql_conn->prepare($query))){
            $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $resp["response"] = false;
        }

        if(!($stmt->bind_param($param,
                            $project->project_title,
                            $project->project_owner,
                            $project->project_architect,
                            $project->habitable_floor_area,
                            $project->site_area,
                            $project->pre_certification,
                            $project->project_cost,
                            $project->project_a_start_date,
                            $project->project_a_end_date,
                            $project->building_type,
                            $project->project_scope,
                            $project->contact_person,
                            $project->contact_email,
                            $project->contact_telephone,
                            $project->contact_telephone_c_code,
                            $project->project_address_line_1,
                            $project->project_address_line_2,
                            $project->project_city,
                            $project->project_state,
                            $project->project_country,
                            $project->project_zipcode,
                            $project->project_submission_type,
                            $project->project_certification_cost,
                            $_id
                      ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if (!($ret = $stmt->execute())) {
            $resp["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($ret){
            $resp["response"] = true;
            $resp["message"] = $building_type;
        }

        $stmt->close();
        mysqli_close($sql_conn);
        // toggle site chapter based on the $project_scope
        toggle_site_chapter($_id,$old_project_scope,$project_scope);
        return json_encode($resp);
    }

    function getCertificationCost($project_submission_type, $habitable_floor_area)
    {
        $project_certification_cost = 500;
        $project_submission_type = (int)$project_submission_type;
        $habitable_floor_area = (int)$habitable_floor_area;
        if($project_submission_type == 0)
        {
            $project_certification_cost = 500;
        }
        else if($habitable_floor_area < 50000 )
        {
            $project_certification_cost = 2500;
        }
        else if($habitable_floor_area < 100000 )
        {
            $project_certification_cost = 3500;
        }
        else
        {
            $project_certification_cost = 0;
        }
        return $project_certification_cost;
    }

    function toggle_site_chapter($project_id,$old_project_scope,$new_project_scope)
    {
        if($old_project_scope != $new_project_scope)
        {
            if($new_project_scope == "0")
            {
                ToggleProjectChapter($project_id,"5","1",false);
            }
            else
            {
                ToggleProjectChapter($project_id,"5","1",true);
            }
        }
    }

    function get_project_scope($_id)
    {
        $project_info = getProjectInfo($_id);
        $project_info = json_decode($project_info,true);
        $project_info = $project_info[0];
        //echo "project scope is :" .$project_info["project_scope"];
        return $project_info["project_scope"];
    }

    //get_project_scope("pr_0956aa1511");

     

    //     // SELECT count(*), 
    //     //        CASE 
    //     //             WHEN isud_status = 0 THEN "Ongoing" 
    //     //             WHEN isud_status = 1 THEN "Review" 
    //     //             WHEN isud_status = 2 THEN "Pending" 
    //     //             WHEN isud_status = 3 THEN "Completed" 
    //     //         END AS status,               
    //     // FROM `UD_P_Info` 
    //     // GROUP BY status;


    //     // SELECT 
    //     //     count(DISTINCT userTable._id) as numUsers,
    //     //     count(DISTINCT projectTable._id) as Projs,
    //     //     SUM(CASE
    //     //         when projectTable._id is NULL then 1
    //     //         else 0
    //     //     end) as zeroprojCounts,
    //     //     CASE
    //     //         when datediff(CURDATE(), `ts_last_login_success`) >= 180 then 'inactive' 
    //     //         else 'active' 
    //     //     end as activity, 
    //     //     CASE 
    //     //         WHEN authtype_id = 1 THEN "Admin" 
    //     //         WHEN authtype_id = 2 THEN "Reviewer" 
    //     //         WHEN authtype_id = 3 THEN "User" 
    //     //         WHEN authtype_id = 4 THEN "Organization" 
    //     //     END AS stat
    //     // FROM UD_P_Info as projectTable
    //     // LEFT JOIN `Users_temp` as userTable on  userTable._id = projectTable.creating_user_id
    //     // GROUP BY activity, stat


        // SELECT 
        //     COUNT(DISTINCT userTable._id) as numUsers,
        //     CASE
        //         WHEN datediff(CURDATE(), `ts_last_login_success`) >= 180 then 'inactive' 
        //         ELSE 'active' 
        //     END AS activity, 
        //     CASE 
        //         WHEN authtype_id = 1 THEN "Admin" 
        //         WHEN authtype_id = 2 THEN "DB Admin" 
        //         WHEN authtype_id = 3 THEN "User" 
        //         WHEN authtype_id = 4 THEN "Organization" 
        //     END AS stat
        // FROM UD_P_Info as projectTable
        // LEFT JOIN `Users_temp` as userTable on  userTable._id = projectTable.creating_user_id
        // GROUP BY activity, stat

       

    // getProjectStats();
    // function getProjectStats(){

       
    //     $sql_conn = mysqli_connection();
    //     if(isLoggedIn()){

    //         if($_SESSION['user']->authtype_id == TypeOfUser::ADMIN || $_SESSION['user']->authtype_id == TypeOfUser::SUPER_ADMIN)  // If the user is super admin or admin
    //     {
            
    //         $sql = "SELECT count(*) as rowCounts, 
    //                 CASE 
    //                     WHEN isud_status = 0 THEN 'Ongoing' 
    //                     WHEN isud_status = 1 THEN 'Review' 
    //                     WHEN isud_status = 2 THEN 'Pending' 
    //                     WHEN isud_status = 3 THEN 'Completed' 
    //                 END AS stat               
    //             FROM `UD_P_Info` 
    //             GROUP BY stat";
           
    //         print($sql);
    //         $udProjectStats = array();
    //         $res = array();
    //         try{
    //             print('yes');
    //             $result = mysqli_query($sql_conn, $sql);
    //             print('not');
    //             print($result);
    //             print(mysqli_query($sql_conn, $sql));
    //             while($row = mysqli_fetch_array($result)) {
                    
    //                 print('inside while');
    //                 foreach($row as $key => $val){
    //                     print('inside for');
    //                     if(gettype($key) === "integer"){
    //                         unset($row[$key]);
    //                     }
    //                 }
    //                  print('outside for');
    //                 $row["status"] = $_SESSION['user']->_id == $row["status"];
    //                 $row["rowCounts"] = $_SESSION['user']->email_id== $row["rowCounts"];
    //                 print($row["status"] );
    //                 print($row["rowCounts"] );
    //                 $udProjectStats[] = $row;
    //             }
    //             mysqli_close($sql_conn);
    //             $res = $udProjectStats;
    //         } catch (Exception $e){
    //             $res['message'] = "Error : ".$e->getMessage();
    //         }
    //     } else {
    //         $res['message'] = "Error : User not Admin";
    //     }

        

    //     } else {
    //         $res['message'] = "Error : User not logged in";
    //     }

    //     $r = json_encode($res);
    //     print($r);

    //     return $r;

    // }

//getProjectStats();
function getProjectStats(){

        $sql_conn = mysqli_connection();
        if(isLoggedIn()){

            if($_SESSION['user']->authtype_id == TypeOfUser::ADMIN || $_SESSION['user']->authtype_id == TypeOfUser::SUPER_ADMIN)  // If the user is super admin or admin
            {
              
                $sql = "SELECT  
                            CASE 
                                WHEN isud_status = 0 THEN 'Ongoing' 
                                WHEN isud_status = 1 THEN 'Completed' 
                                WHEN isud_status = 2 THEN 'Review' 
                                WHEN isud_status = 3 THEN 'Pending' 
                            END AS stat,
                            CASE 
                                WHEN project_submission_type = 0 THEN 'Self' 
                                WHEN project_submission_type = 1 THEN 'isUD' 
                            END AS CertType,
                            count(*) as rowCounts           
                        FROM `UD_P_Info` 
                        GROUP BY stat, CertType";
            
        
            $udProjectStats = array();
            $res = array();
            try{
                $result = mysqli_query($sql_conn, $sql);
                while($row = mysqli_fetch_array($result)) {
                    //$project = new UD_Project_Info();
                    //$project->populateFromDBRow($row);

                    // hide fields
                    // unset($row["creating_user_id"]);
                    
                    foreach($row as $key => $val){
                        //print($val);
                        //print($val);
                        if(gettype($key) === "integer"){
                            unset($row[$key]);

                        }


                    }
                    //array_push($udProjectStats, $row['stat'], (int) $row['rowCounts']);
                    // $udProjectStats['stat'] = $row['stat'];
                    // $udProjectStats['rowCounts'] = (int) $row['rowCounts'];
                    // foreach($r in $row){
                    //     echo($r);
                    // }
                    $udProjectStats[] = $row;
                }

                mysqli_close($sql_conn);
                $res = $udProjectStats;
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }
            } else {
            $res['message'] = "Error : User not Admin";
        }

        } else {
            $res['message'] = "Error : User not logged in";
        }

        // $format = '{
        //                 "cols":
        //                 [
        //                 {"id":"","label":"Type","pattern":"","type":"string"},
        //                 {"id":"","label":"Counts","pattern":"","type":"number"}
        //                 ],
        //                 "rows":'.json_encode($res).'}';

        $r = json_encode($res, JSON_NUMERIC_CHECK);
        //print($r);

        return $r;

    }

    function getSolutionStats(){

        $sql_conn = mysqli_connection();
        if(isLoggedIn()){

            if($_SESSION['user']->authtype_id == TypeOfUser::ADMIN || $_SESSION['user']->authtype_id == TypeOfUser::SUPER_ADMIN)  // If the user is super admin or admin
            {
              
                $sql = "SELECT  
                            CASE 
                                WHEN isud_status = 0 THEN 'Ongoing' 
                                WHEN isud_status = 1 THEN 'Completed' 
                                WHEN isud_status = 2 THEN 'Review' 
                                WHEN isud_status = 3 THEN 'Pending' 
                            END AS stat,
                            CASE 
                                WHEN project_submission_type = 0 THEN 'Self' 
                                WHEN project_submission_type = 1 THEN 'isUD' 
                            END AS CertType,
                            count(*) as rowCounts           
                        FROM `UD_P_Info` 
                        GROUP BY stat, CertType";
            
        
            $udProjectStats = array();
            $res = array();
            try{
                $result = mysqli_query($sql_conn, $sql);
                while($row = mysqli_fetch_array($result)) {
                    //$project = new UD_Project_Info();
                    //$project->populateFromDBRow($row);

                    // hide fields
                    // unset($row["creating_user_id"]);
                    
                    foreach($row as $key => $val){
                        //print($val);
                        //print($val);
                        if(gettype($key) === "integer"){
                            unset($row[$key]);

                        }


                    }
                    //array_push($udProjectStats, $row['stat'], (int) $row['rowCounts']);
                    // $udProjectStats['stat'] = $row['stat'];
                    // $udProjectStats['rowCounts'] = (int) $row['rowCounts'];
                    // foreach($r in $row){
                    //     echo($r);
                    // }
                    $udProjectStats[] = $row;
                }

                mysqli_close($sql_conn);
                $res = $udProjectStats;
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }
            } else {
            $res['message'] = "Error : User not Admin";
        }

        } else {
            $res['message'] = "Error : User not logged in";
        }

        // $format = '{
        //                 "cols":
        //                 [
        //                 {"id":"","label":"Type","pattern":"","type":"string"},
        //                 {"id":"","label":"Counts","pattern":"","type":"number"}
        //                 ],
        //                 "rows":'.json_encode($res).'}';

        $r = json_encode($res, JSON_NUMERIC_CHECK);
        //print($r);

        return $r;

    }


   

?>