<?php //include('../../itangazo.worldsoft.rw/sms-api/connect.php');
//include('../../ITANGAZO/sms-api/connect.php');
function setTimeZone(){

  date_default_timezone_set("Africa/Kigali");
  //echo "<h1>The time is " . date("h:i:sa")."</h1>";

}
setTimeZone();

//include('../../ITANGAZO/sms-api/function.php');
//include('../../itangazo.worldsoft.rw/sms-api/function.php');


///////////////////////////Authorization Function  ////////////////////////////////

  // (C) GET JWT FROM AUTH HEADER
  
  function AuthorizeMe($headers){

    $jwt = $headers;
  
    if(isset($jwt)){

      return $jwt;

    }else{

      return false;
    }
  
  }





///////////////////////////////////////////// BACKUP FUNCTIONS/////////////////////////////////

function doBackup(){

    $dump = new Ifsnop\Mysqldump\Mysqldump('mysql:host=localhost;dbname=cellfix-v13', 'root', '');

    $time =  date("h:i:sa");

    $file = '../db/woclassic-'.date("Y-m-d").'-'.$time.'.sql';  
    $dump->start($file);

    //$dump->start('../db/woclassic.sql');



    //SEND AN EMAIL   
    $from ="worldsoftlimited@gmail.com";

    // Recipient 
    $to = 'worldsoftlimited@gmail.com'; 
 
    // Sender 
    $fromName = 'WoClassic'; 
 
    // Email subject 
    $subject = 'Database of WoClassic on '.date("Y-m-d")."-".$time;  
 
    // Email body content 
    $htmlContent = ' 
        <h3>Wo Classic</h3> 
        center>
          <a href="worldsoft.rw">
            Visit Us
          </a> 
        </center>
    '; 
 
    // Header for sender info 
    $headers = "From: $fromName"." <".$from.">"; 
 
    // Boundary  
    $semi_rand = md5(time());  
    $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";  
 
    // Headers for attachment  
    $headers .= "\nMIME-Version: 1.0\n" . "Content-Type: multipart/mixed;\n" . " boundary=\"{$mime_boundary}\""; 
 
    // Multipart boundary  
    $message = "--{$mime_boundary}\n" . "Content-Type: text/html; charset=\"UTF-8\"\n" . 
"Content-Transfer-Encoding: 7bit\n\n" . $htmlContent . "\n\n";  
 
    // Preparing attachment 
    if(!empty($file) > 0){ 
      if(is_file($file)){ 
        $message .= "--{$mime_boundary}\n"; 
        $fp =    @fopen($file,"rb"); 
        $data =  @fread($fp,filesize($file)); 
 
        @fclose($fp); 
        $data = chunk_split(base64_encode($data)); 
        $message .= "Content-Type: application/octet-stream; name=\"".basename($file)."\"\n" .  
        "Content-Description: ".basename($file)."\n" . 
        "Content-Disposition: attachment;\n" . " filename=\"".basename($file)."\"; size=".filesize($file).";\n" .  
        "Content-Transfer-Encoding: base64\n\n" . $data . "\n\n"; 
      } 
    } 

    $message .= "--{$mime_boundary}--"; 
    $returnpath = "-f" . $from; 
 
    // Send email 
    $mail = @mail($to, $subject, $message, $headers, $returnpath);  

    if($mail){

      //send notifications
      $allUsers = find_all_users(1);

      foreach ($allUsers as $userData) {    

        //find user type by id
        $UserType =  find_user_categ_by_id(mysql_prep($userData->tbl_user_categId));

        //if he/she is admin or director 
        if( (strtolower(mysql_prep($UserType->tbl_user_categName)) =="wsadmin")  ){  

            
                $title = "Backup ya WORLDSOFT CLASSIC YAKOZWE Saa ".$time;
                NotifyMe(0, $title, $userData->tbl_userId);

                return true;

        }

      }



    }else{

      return false;
    }


  }



  ///////////////////////////////////////////////  PARTNERS FUNCTIONS ////////////////////////


  //ALL partners
  function find_all_partners( $status ){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_partner WHERE (status = ?) ORDER BY id ASC LIMIT 40");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }


  ///////////////////////////////////////////////  STATISTICS FUNCTIONS ////////////////////////


  //club statistics
  function find_club_by_general_stat_for_club($clubId, $status){

    global $connectMe;

    $stmt = $connectMe->prepare("SELECT C.id, C.clubId, C.seasonId, C.games, C.win, C.fail, C.draw, C.date,
      C.userId, C.status, S.name AS season FROM tbl_club_stat C, tbl_season S WHERE (C.seasonId = S.id) AND (C.clubId = ?) AND (C.status = ?) ORDER BY C.id DESC LIMIT 40");
    $stmt->bindParam(1, $clubId);

    $stmt->bindParam(2, $status);
    $stmt->execute();
        
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
    

  }



  //ALL other club stats
  function find_all_other_club_stats($clubId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT O.id, S.name AS label, O.value, 1 AS bar, '#534AB7' AS barColor, T.name AS type, SE.name AS season FROM tbl_other_club_stats O, tbl_stat_sub_type S, tbl_stat_type T, tbl_season SE  WHERE (O.subTypeId = S.id) AND (S.typeId = T.id) AND (O.seasonId = SE.id) AND  (O.clubId =?) AND (O.status = ?) ORDER BY O.id, T.id DESC LIMIT 40");

    $stmt->bindParam(1, $clubId);
    $stmt->bindParam(2, $status);

    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }


  //ALL other club stats for type
  function find_all_other_club_stats_for_type($clubId, $typeId,  $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT S.name AS label, O.value, 1 AS bar, '#534AB7' AS barColor FROM tbl_other_club_stats O, tbl_stat_sub_type S WHERE (O.subTypeId = S.id) AND  (O.clubId =?) AND (O.status = ?) AND (O.typeId =?) ORDER BY O.id DESC LIMIT 40");

    $stmt->bindParam(1, $clubId);
    $stmt->bindParam(2, $status);

    $stmt->bindParam(3, $typeId);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }


  //club statistics
  function find_club_by_stat_for_club($clubId, $status){

    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_club_stat WHERE (clubId = ?) AND (status = ?) ORDER BY id DESC LIMIT 1");
    $stmt->bindParam(1, $clubId);

    $stmt->bindParam(2, $status);
    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }

  }


  //all statistic types
  function find_all_statistic_types($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_stat_type WHERE  (status = ?) ORDER BY id ASC LIMIT 40");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }



  ////////////////////////////////////////////// PREDICTION FUNCTIONS /////////////////////////


  //ALL PREDICTION COMMENTS
  function find_all_prediction_comments($predictId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_prediction_result P, tbl_user U WHERE (P.userId = U.tbl_userId) AND (P.predictionId =?) AND  (P.status = ?) ORDER BY P.id ASC ");

    $stmt->bindParam(1, $predictId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 
    return $data;
  }


  //all predictions
  function find_all_predictions($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_prediction WHERE  (status = ?) ORDER BY id DESC LIMIT 40");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }


  ////////////////////////////////////////////// QUIZ FUNCTIONS /////////////////////////


  //all fan points
  function find_all_fan_points($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT 
    name,
    LEFT(name, 1) AS initials,
    club,
    COALESCE(quiz, 0)       AS quiz,
    COALESCE(prediction, 0) AS prediction,
    COALESCE(quiz, 0) + COALESCE(prediction, 0) AS xp,
    ROW_NUMBER() OVER (
        ORDER BY COALESCE(quiz, 0) + COALESCE(prediction, 0) DESC, 
        name ASC
    ) AS rank
FROM (
    SELECT 
        CONCAT(U.tbl_userFname, ' ', U.tbl_userLname) AS name,
        (SELECT SUM(R.marks) 
         FROM tbl_result R 
         WHERE R.userId = U.tbl_userId) AS quiz,
        (SELECT SUM(P.marks) 
         FROM tbl_prediction_result PR 
         JOIN tbl_prediction P ON PR.predictionId = P.id 
         WHERE PR.userId = U.tbl_userId 
           AND PR.correct = 1 
           AND PR.status = 1 
           AND P.status = 1) AS prediction,
        (SELECT C.name 
         FROM tbl_club C 
         WHERE C.id = U.clubId) AS club
    FROM tbl_user U
    WHERE 
        EXISTS (
            SELECT 1 FROM tbl_result R 
            WHERE R.userId = U.tbl_userId
        )
        OR
        EXISTS (
            SELECT 1 
            FROM tbl_prediction_result PR 
            JOIN tbl_prediction P ON PR.predictionId = P.id 
            WHERE PR.userId = U.tbl_userId 
              AND PR.correct = 1 
              AND PR.status = 1 
              AND P.status = 1
        )
) AS leaderboard
ORDER BY rank ASC
LIMIT 20 ");

    //$stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }


  function find_question_by_id($questionId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_question WHERE id = ?  LIMIT 1");
    $stmt->bindParam(1, $questionId);

    $stmt->execute();
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }


  function find_question_option_by_id($optionId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_question_option WHERE id = ?  LIMIT 1");
    $stmt->bindParam(1, $optionId);

    $stmt->execute();
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }



  //ALL QUIZ QUESTION OPTIONS
  function find_all_quiz_question_options($questionId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_question_option WHERE (questionId =?) AND  (status = ?) ORDER BY id ASC ");

    $stmt->bindParam(1, $questionId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;

  }

  //ALL QUIZ QUESTIONS
  function find_all_quiz_questions($quizId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_question WHERE (quizId =?) AND  (status = ?) ORDER BY id ASC ");

    $stmt->bindParam(1, $quizId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;

  }


  //all quizes
  function find_all_quizes($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_quiz WHERE  (status = ?) ORDER BY id DESC LIMIT 40");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }



  ///////////////////////////////////////////// VOTE FUNCTIONS ////////////////////////


  function find_vote_option_by_id($optionId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_vote_option WHERE id = ?  LIMIT 1");
    $stmt->bindParam(1, $optionId);

    $stmt->execute();
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }


  //update vote option by id
  function update_vote_option_by_id($votes, $optionId){

    global $connectMe;
    $stmt = $connectMe->prepare("UPDATE tbl_vote_option SET votes = ? WHERE id = ? ");

    $stmt->bindParam(1, $votes);
    $stmt->bindParam(2, $optionId);

    if( $stmt->execute()){

      return true;
 
    }else{

      return false;
    }
  }



//vote options
function find_all_vote_options($voteId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_vote_option WHERE (voteId =?) AND (status = ?) ORDER BY id ASC LIMIT 40");

 $stmt->bindParam(1, $voteId);
 $stmt->bindParam(2, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



//all votes
function find_all_votes($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_vote WHERE  (status = ?) ORDER BY id DESC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


///////////////////////////////////////////// STANDING FUNCTIONS ////////////////////////


//all standings
function find_all_standings($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT S.id, S.clubId, S.seasonId, S.gp, S.gd, S.pts, S.date, S.status, C.name AS club, SE.name AS season FROM tbl_standing S, tbl_club C, tbl_season SE 
  WHERE  (S.clubId = C.id) AND (S.seasonId = SE.id) AND  (S.status = ?) AND (SE.active = ?) ORDER BY S.pts DESC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



///////////////////////////////////////////// REFEREES FUNCTIONS ////////////////////////


//all referees
function find_all_referees($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_referee WHERE  (status = ?) ORDER BY id ASC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}





  /////////////////////////////////////////// PLAYERS FUNCTIONS //////////////////////////


//ALL Positions
function find_all_positions($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_position WHERE (status = ?) ORDER BY id ASC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;

}


//ALL club players for position
function find_all_club_players_for_position($clubId, $positionId,  $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT  P.id, P.clubId, P.name, P.image, P.positionId, P.date, P.status,  C.name AS club, PO.name AS position FROM tbl_player P, tbl_position PO, tbl_club C  WHERE (P.positionId = PO.id) AND (P.clubId = C.id) AND  (P.clubId =?) AND (P.status = ?) AND (P.positionId =?) ORDER BY P.id DESC LIMIT 40");

 $stmt->bindParam(1, $clubId);
 $stmt->bindParam(2, $status);

 $stmt->bindParam(3, $positionId);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;

}



//ALL club players
function find_all_club_players($clubId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT  P.id, P.clubId, P.name, P.image, P.positionId, P.date, P.status,  C.name AS club, PO.name AS position FROM tbl_player P, tbl_position PO, tbl_club C  WHERE (P.positionId = PO.id) AND (P.clubId = C.id) AND  (P.clubId =?) AND (P.status = ?) ORDER BY P.positionId ASC LIMIT 40");

 $stmt->bindParam(1, $clubId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


///////////////////////////////////////////// MANAGERS FUNCTIONS ////////////////////////


//all managers
function find_all_managers($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_manager WHERE  (status = ?) ORDER BY id ASC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



  ///////////////////////////////////////////// CLUBS FUNCTIONS ////////////////////////


  function find_club_by_id($clubId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_club WHERE id = ?  LIMIT 1");
    $stmt->bindParam(1, $clubId);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }


//all clubs
function find_all_clubs($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_club WHERE  (status = ?) ORDER BY name ASC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


///////////////////////////////////////////// BOARDS FUNCTIONS ////////////////////////


//all board members
function find_all_board_memebers($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_board WHERE  (status = ?) ORDER BY id ASC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


  ///////////////////////////////////////////// POSTS FUNCTIONS ////////////////////////


//ALL POSTS COMMENTS
function find_all_post_comments($postId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_post_comment P, tbl_user U WHERE (P.userId = U.tbl_userId) AND (P.postId =?) AND  (P.status = ?) ORDER BY P.id ASC ");

 $stmt->bindParam(1, $postId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


//all posts
function find_all_posts($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_post WHERE  (status = ?) ORDER BY id DESC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


  ///////////////////////////////////////////// VIDEOS FUNCTIONS ////////////////////////


//all videos
function find_all_videos($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_video WHERE  (status = ?) ORDER BY id DESC LIMIT 40");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


  ///////////////////////////////////////////// SHORTS FUNCTIONS ////////////////////////


//all shorts
function find_all_shorts($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_short WHERE  (status = ?) ORDER BY id DESC LIMIT 6");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



  ////////////////////////////////////////////// FIXTURES FUNCTIONS ///////////////////////

//ALL club results
function find_all_club_results($clubId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT G.id, G.date, G.hour, G.tk, G.status, R.name AS receiver, V.name AS visitor, G.finished, G.visitorGoals, G.receiverGoals, G.video  FROM tbl_game G, tbl_club R, tbl_club V WHERE (G.receiverId = R.id) AND (G.visitorId = V.id) AND (G.receiverId = ? OR G.visitorId = ?) AND  (G.status = ?) AND (G.finished = ?) ORDER BY G.id DESC LIMIT 20");

 $stmt->bindParam(1, $clubId);
 $stmt->bindParam(2, $clubId);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}




//ALL results
function find_all_results($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT G.id, G.date, G.hour, G.tk, G.status, R.name AS receiver, V.name AS visitor, G.finished, G.visitorGoals, G.receiverGoals, G.video  FROM tbl_game G, tbl_club R, tbl_club V WHERE (G.receiverId = R.id) AND (G.visitorId = V.id) AND  (G.status = ?) AND (G.finished = ?) ORDER BY G.id DESC LIMIT 20");

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


//ALL CLUB FIXTURES
function find_all_club_fixtures($clubId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT G.id, G.date, G.hour, G.tk, G.status, R.name AS receiver, V.name AS visitor, G.finished, G.visitorGoals, G.receiverGoals, G.video  FROM tbl_game G, tbl_club R, tbl_club V WHERE (G.receiverId = R.id) AND (G.visitorId = V.id) AND (G.receiverId = ? OR G.visitorId = ?) AND  (G.status = ?) AND (G.finished = 0) ORDER BY G.id DESC LIMIT 20");

 $stmt->bindParam(1, $clubId);
 $stmt->bindParam(2, $clubId);

 $stmt->bindParam(3, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



//ALL FIXTURES
function find_all_fixtures($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT G.id, G.date, G.hour, G.tk, G.status, R.name AS receiver, V.name AS visitor, G.finished, G.visitorGoals, G.receiverGoals, G.video  FROM tbl_game G, tbl_club R, tbl_club V WHERE (G.receiverId = R.id) AND (G.visitorId = V.id) AND  (G.status = ?) AND (G.finished = 0) ORDER BY G.id DESC LIMIT 20");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


  ///////////////////////////////////////////// GALLERY FUNCTIONS ////////////////////////


//all galleries
function find_all_galleries($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_gallery WHERE  (status = ?) ORDER BY id DESC LIMIT 6");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



  ///////////////////////////////////////////// NEWS FUNCTIONS ////////////////////////


//ALL NEWS COMMENTS
function find_all_comments($newsId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_comment C, tbl_user U WHERE (C.userId = U.tbl_userId) AND (C.newsId =?) AND  (C.status = ?) ORDER BY C.id DESC ");

 $stmt->bindParam(1, $newsId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


//find_news_by_tk
function find_news_by_tk($tk){

 global $connectMe;

 $stmt = $connectMe->prepare("SELECT * FROM tbl_news WHERE (tk = ?)  LIMIT 1");
 $stmt->bindParam(1, $tk);
 $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//ALL club NEWS
function find_all_club_news($clubId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_news WHERE (clubId =?) AND (status = ?) ORDER BY id DESC LIMIT 40");

 $stmt->bindParam(1, $clubId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}



//ALL NEWS
function find_all_news($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_news WHERE  (status = ?) ORDER BY id DESC LIMIT 100");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


//ALL old NEWS
function find_all_old_news($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_news WHERE  (status = ?) ORDER BY id DESC LIMIT 20");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


//HOT NEWS
function find_all_hot_news($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_news WHERE  (status = ?) ORDER BY id DESC LIMIT 4");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



  ///////////////////////////////////////////// STORY FUNCTIONS ////////////////////////


//all stories
function find_all_stories($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_story WHERE  (status = ?) ORDER BY id DESC LIMIT 20");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}



  //////////////////////// USER FUNCTIONS /////////////////////////////////////////////


  function find_user_by_id($userId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_user U, tbl_role R WHERE  U.tbl_roleId = R.tbl_roleId AND U.tbl_userId = ?  LIMIT 1");
    $stmt->bindParam(1, $userId);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }


  //all roles
  function find_all_roles($status){
    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_role R WHERE (R.tbl_roleStatus = ?) ORDER BY R.tbl_roleName ASC ");
 
    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }


  //all  users
  function find_all_users($status){
    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_user U, tbl_role R WHERE (U.tbl_roleId = R.tbl_roleId) AND (U.tbl_userStatus = ?) ORDER BY U.tbl_userId DESC ");
 
    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }


  /////////////////////// PAYMENT FUNCTIONS //////////////////////////////////////////


//all payments
function find_all_payments($status)
 {
 global $connectMe;
 $userStmt = $connectMe->prepare("SELECT * FROM tbl_paid_course P, tbl_user U, tbl_enrol E, tbl_course C WHERE (P.tbl_enrolId = E.tbl_enrolId) AND (U.tbl_userId = E.tbl_userId) AND (E.tbl_courseId = C.tbl_courseId) AND (P.tbl_paid_courseStatus = ?) ORDER BY P.tbl_paid_courseId DESC ");
 
 $userStmt->bindParam(1, $status);
 $userStmt->execute();

 $user = $userStmt->fetchAll(PDO::FETCH_OBJ);
 return $user;
}




  /////////////////////// COURSE FUNCTIONS //////////////////////////////////////////


  function find_course_by_id($courseId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_course C WHERE C.tbl_courseId = ?  LIMIT 1");
    $stmt->bindParam(1, $courseId);

    $stmt->execute();
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }


//all courses
function find_all_courses($status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_course C WHERE (C.tbl_courseStatus = ?) ORDER BY C.tbl_courseName ASC ");
 
 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


//count courses 
function countCourses($status){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT COUNT(*) as qty FROM  tbl_course WHERE (tbl_courseStatus  = ?)");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}






  /////////////////////// STUDENT FUNCTIONS //////////////////////////////////////////


  function find_student_by_id($studentId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_user U, tbl_enrol E, tbl_course C WHERE (U.tbl_userId = E.tbl_userId) AND (E.tbl_courseId = C.tbl_courseId) AND E.tbl_enrolId = ?  LIMIT 1");
    $stmt->bindParam(1, $studentId);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }



  function find_certificate_by_id($certificateId){
 
    global $connectMe;

    $stmt = $connectMe->prepare("SELECT * FROM tbl_certified CT, tbl_user U, tbl_enrol E, tbl_course C WHERE (CT.tbl_enrolId = E.tbl_enrolId) AND (U.tbl_userId = E.tbl_userId) AND (E.tbl_courseId = C.tbl_courseId) AND CT.tbl_certifiedId = ?  LIMIT 1");
    $stmt->bindParam(1, $certificateId);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }


  //all certified students
  function find_all_all_certified_students($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_certified CT, tbl_user U, tbl_enrol E, tbl_course C WHERE (CT.tbl_enrolId = E.tbl_enrolId) AND (U.tbl_userId = E.tbl_userId) AND (E.tbl_courseId = C.tbl_courseId) AND (CT.tbl_certifiedStatus = ?) ORDER BY CT.tbl_certifiedId DESC ");
 
    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }


//all students
function find_all_students($status)
 {
 global $connectMe;
 $userStmt = $connectMe->prepare("SELECT * FROM tbl_user U, tbl_enrol E, tbl_course C WHERE (U.tbl_userId = E.tbl_userId) AND (E.tbl_courseId = C.tbl_courseId) AND (E.tbl_enrolStatus = ?) ORDER BY E.tbl_enrolId DESC ");
 
 $userStmt->bindParam(1, $status);
 $userStmt->execute();

 $user = $userStmt->fetchAll(PDO::FETCH_OBJ);
 return $user;
}


//count student 
function countStudents($status){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT COUNT(*) as qty FROM tbl_enrol WHERE (tbl_enrolStatus  = ?)");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}






  /////////////////////// PAYMENT FUNCTIONS //////////////////////////////////////////


//calculate paid amount 
function calculatePaidAmount($status){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_paid_courseAmt)  as amount FROM  tbl_paid_course WHERE (tbl_paid_courseStatus  = ?)");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}



////////////////////// STOCK REQUEST  FUNCTIONS


//find_transfer_request_by_id
function find_transfer_request_by_id($requestId){
 
  global $connectMe;

  $stmt = $connectMe->prepare("SELECT * FROM tbl_request WHERE tbl_requestId = ?  LIMIT 1");
  $stmt->bindParam(1, $requestId);

  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }
}


//all requests for businesss
function find_all_requests_for_business($businesssId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("
    SELECT 
      R.tbl_requestId,
      R.qty,
      R.tbl_requestDate,
      R.tbl_requestTime,
      R.tbl_requestStatus,
      BP.tbl_branch_productAvail,
      P.tbl_productId,
      P.tbl_productBrand,
      S.tbl_specificationYear,
      C.tbl_product_categName,
      B.tbl_brandName AS source,
      B2.tbl_brandName AS distination,
      E.tbl_employeeFname,
      E.tbl_employeeLname

    FROM tbl_request R, tbl_branch_product BP, tbl_product P, tbl_specification S, 
    tbl_product_categ C, tbl_brand B, tbl_brand B2, tbl_employee E   WHERE (R.tbl_branch_productId = BP.tbl_branch_productId)  AND (BP.tbl_productId = P.tbl_productId) 
    AND (P.tbl_specificationId = S.tbl_specificationId) AND (S.tbl_product_categId = C.tbl_product_categId)  AND (R.tbl_brandId = B.tbl_brandId) AND (BP.tbl_brandId = B2.tbl_brandId) AND (R.tbl_employeeId = E.tbl_employeeId) AND (B.tbl_businessId = ?) AND (BP.tbl_branch_productAvail > 0)  ORDER BY R.tbl_requestId  DESC LIMIT 50 ");

  $stmt->bindParam(1, $businesssId);
  //$stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}



//all requests for branch
function find_all_requests_for_branch($branchId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT R.tbl_requestId,
      R.qty,
      R.tbl_requestDate,
      R.tbl_requestTime,
      R.tbl_requestStatus,
      BP.tbl_branch_productAvail,
      P.tbl_productId,
      P.tbl_productBrand,
      S.tbl_specificationYear,
      C.tbl_product_categName,
      B.tbl_brandName AS source,
      B2.tbl_brandName AS distination,
      E.tbl_employeeFname,
      E.tbl_employeeLname

    FROM tbl_request R, tbl_branch_product BP, tbl_product P, tbl_specification S, 
    tbl_product_categ C, tbl_brand B, tbl_brand B2, tbl_employee E   WHERE (R.tbl_branch_productId = BP.tbl_branch_productId)  AND (BP.tbl_productId = P.tbl_productId) 
    AND (P.tbl_specificationId = S.tbl_specificationId) AND (S.tbl_product_categId = C.tbl_product_categId)  AND (R.tbl_brandId = B.tbl_brandId) AND (BP.tbl_brandId = B2.tbl_brandId) AND (R.tbl_employeeId = E.tbl_employeeId) AND (R.tbl_brandId = ?) AND (BP.tbl_branch_productAvail > 0)  ORDER BY R.tbl_requestId  DESC LIMIT 50");

  $stmt->bindParam(1, $branchId);
  //$stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}










/////////////////////////////////// CHATS FUNCTION ////////////////////////////////

  //all chats
  function find_all_chats($senderId, $receiverId, $status ){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_chat_msg WHERE ( ((tbl_userId = ?) AND (tbl_receiverId = ?) ) OR ((tbl_userId = ?) AND (tbl_receiverId = ?) )) AND (tbl_chat_msgStatus = ?)   ORDER BY tbl_chat_msgId ASC ");
 
    $stmt->bindParam(1, $senderId);
    $stmt->bindParam(2, $receiverId);

    $stmt->bindParam(3, $receiverId);
    $stmt->bindParam(4, $senderId);

    $stmt->bindParam(5, $status);
    $stmt->execute();

    $res = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $res;
  }



//////////////////////////////////////////// CURRENCY FUNCTIONS  //////////////////////////////////


  //find_formula_by_currencyId_and_categ
  function find_formula_by_currencyId_and_categ($currencyId, $categId){
 
    global $connectMe;

    $currencyId = mysql_prep($currencyId);
    $categId = mysql_prep($categId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_formula WHERE tbl_currecyId = ? AND tbl_product_categId =?  LIMIT 1");
    $stmt->bindParam(1, $currencyId);
    
    $stmt->bindParam(2, $categId);
    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }



  //find_currency_by_id
  function find_currency_by_id($currencyId){
 
    global $connectMe;
    $safe = mysql_prep($currencyId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_currecy WHERE tbl_currecyId = ?  LIMIT 1");
    $stmt->bindParam(1, $safe);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }



  //all formula for currency 
  function find_all_formula_for_currency($currencyId, $status ){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_formula WHERE (tbl_currecyId = ?) AND (tbl_formulaStatus = ?)   ORDER BY tbl_formulaId  DESC ");
 
    $stmt->bindParam(1, $currencyId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $res;
  }



  //find_currency_by_token
  function find_currency_by_token($currencyId){
 
    global $connectMe;
    $safe = mysql_prep($currencyId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_currecy WHERE tbl_currecyToken = ?  LIMIT 1");
    $stmt->bindParam(1, $safe);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }



  //all currencies
  function all_currencies($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_currecy WHERE tbl_currecyStatus = ? ORDER BY tbl_currecyId DESC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }

  //all currencies for business
  function all_currencies_for_business($businessId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_currecy WHERE (tbl_businessId = ?) AND (tbl_currecyStatus = ?) ORDER BY tbl_currecyId DESC ");

    $stmt->bindParam(1, $businessId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;

  }


///////////////////////////////////////////SMS FUNCTIONS////////////////////////////////


//count all  used sms 
function count_all_used_sms($businessId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_sms WHERE (tbl_businessId = ?) AND (tbl_smsStatus = ?)  ORDER BY tbl_smsId DESC ");

  $stmt->bindParam(1, $businessId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  return $stmt->rowCount();
}




//////////////////////////////////////  sms token///////////////////////////////////////

function savecurrentSMSToken($date, $time, $token ){

  global $connectMe;
  $stmt = $connectMe->prepare("UPDATE tbl_sms_data SET tbl_sms_dataDate =?, tbl_sms_dataTime =?, tbl_sms_dataToken =?");
 
  $stmt->bindParam(1, $date);
  $stmt->bindParam(2, $time);

  $stmt->bindParam(3, $token);
  if( $stmt->execute()){

    return true;

  }else{

    return false;

  }
}



//RequestSMSToken
function RequestSMSToken(){


  ///////////// request token////////////////////////////

  $curl = curl_init();
  curl_setopt_array($curl, array(

    CURLOPT_URL => "https://auth.oltranz.com/auth/realms/api/protocol/openid-connect/token",
    CURLOPT_RETURNTRANSFER => true,

    CURLOPT_ENCODING => "",
    CURLOPT_MAXREDIRS => 10,

    CURLOPT_TIMEOUT => 30,
    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,

    CURLOPT_CUSTOMREQUEST => "POST",
    CURLOPT_POSTFIELDS => "client_id=worldsoftog&client_secret=ab0af43e-140f-42bd-8af3-1e7238dc3a46&grant_type=client_credentials",

    CURLOPT_HTTPHEADER => array(
      "cache-control: no-cache",
      "content-type: application/x-www-form-urlencoded"
    ),

  ));

  $response = curl_exec($curl);
  $err = curl_error($curl);

  curl_close($curl);

  if (!$err){

    $resArray = json_decode($response); 

    if(isset($resArray)){
    
      if(isset($resArray->access_token)){

        if(savecurrentSMSToken(date("Y-m-d"), date("h:i:s") , $resArray->access_token )){

          return $resArray->access_token;

        }else{

          return "FAILED";

        }
        
        
        
      }else{

        return "FAILED";
        
      }

    
    }else{ 
    
      return "FAILED";
    }



  }else{

    return "FAILED";


  }



}





//saveSMS
function saveSMS($businessId, $message, $date, $time, $status ){
  global $connectMe;
  $stmt = $connectMe->prepare("INSERT INTO tbl_sms ( tbl_businessId, tbl_smsDate, tbl_smsContent, tbl_smsTime, tbl_smsStatus)  VALUES(?,?, ?,?, ?)");
 
  $stmt->bindParam(1, $businessId);
  $stmt->bindParam(2, $date);

  $stmt->bindParam(3, $message);
  $stmt->bindParam(4, $time);

  $stmt->bindParam(5, $status);
  if( $stmt->execute()){

    return true;

  }else{

    return false;


  }
}



////////////////////// send sms

function sendSMSToClient($compName, $message, $phone, $token ){
        
  $data = array(
      'title' => $compName,
      'message' => $message,
      'receivers' => array('25'.$phone),
  );

  // Data should be passed as json format
  $data_json = json_encode($data); 


  $url = "https://sms.api.oltranz.com/api/v1/sms/send";    
  $curl = curl_init($url);
  curl_setopt($curl, CURLOPT_URL, $url);
  curl_setopt($curl, CURLOPT_POST, true);
  curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

  $headers = array(
    "Content-Type: application/json",
    "Authorization: Bearer ".$token,
   
  );

  curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
  curl_setopt($curl, CURLOPT_POSTFIELDS, $data_json );

  //for debug only!
  curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
  curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

  $resp = curl_exec($curl);
  curl_close($curl);

  return $resp;


}


//find sms token by id
function find_sms_token_by_id(){
  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_sms_data  LIMIT 1");

  $stmt->execute(); 
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}



///////////////////////////////////////// Business ////////////////////////////


  //find my businesses
  function find_my_businesses($businessId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_business WHERE (tbl_businessId = ?) AND (tbl_businessStatus = ?) ORDER BY tbl_businessId DESC ");

    $stmt->bindParam(1, $businessId);
    $stmt->bindParam(2, $status);

    $stmt->execute();   
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;

  }


  //all businesses
  function all_businesses($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_business WHERE tbl_businessStatus = ? ORDER BY tbl_businessId DESC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();
       
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }

  //find_business_by_token
  function find_business_by_token($businessId){
 
    global $connectMe;
    $safe = mysql_prep($businessId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_business WHERE tbl_businessToken = ?  LIMIT 1");
    $stmt->bindParam(1, $safe);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }
  
  //find_business_by_id
  function find_business_by_id($businessId){
 
    global $connectMe;
    $safe = mysql_prep($businessId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_business WHERE tbl_businessId = ?  LIMIT 1");
    $stmt->bindParam(1, $safe);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }





////////////////////////////////////////////////////////////////////////////////////////////////









////////////////// repair  //////////////////

//repair report for branch 

function find_all_repaired_report_for_branch($from, $to, $status, $branchId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device R , tbl_brand B, tbl_employee E 
    WHERE (R.tbl_employeeId  = E.tbl_employeeId ) AND  (R.tbl_brandId = B.tbl_brandId ) AND  (R.tbl_repair_deviceDate BETWEEN ? AND  ?  ) AND (R.tbl_repair_deviceStatus =?) AND (R.tbl_brandId =?)  ORDER BY R.tbl_repair_deviceId DESC ");


  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $branchId);

  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}


//calculate repair amount for branch
function calculateRepairAmountForBranch($status, $from, $to, $branchId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_repair_deviceFee)  as amount FROM  tbl_repair_device RP, tbl_employee E WHERE (RP.tbl_repair_deviceTkDate BETWEEN ? AND  ?  ) AND  (RP.tbl_repair_deviceStatus  = ?)  AND  (RP.tbl_employeeId  = E.tbl_employeeId)  AND  (RP.tbl_brandId  = ?)   ORDER BY RP.tbl_repair_deviceId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $branchId);

  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}

//calculate repair amount for business
function calculateRepairAmountForBusId($status, $from, $to, $taken, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_repair_deviceFee)  as amount FROM  tbl_repair_device RP, tbl_employee E WHERE (RP.tbl_repair_deviceTkDate BETWEEN ? AND  ?  ) AND  (RP.tbl_repair_deviceTaken  = ?) AND  (RP.tbl_repair_deviceStatus  = ?)  AND  (RP.tbl_employeeId  = E.tbl_employeeId)  AND  (E.tbl_businessId  = ?)   ORDER BY RP.tbl_repair_deviceId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $taken);
  $stmt->bindParam(4, $status);

  $stmt->bindParam(5, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//calculate repair amount
function calculateRepairAmount($status, $from, $to, $taken){

 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(tbl_repair_deviceFee)  as amount FROM  tbl_repair_device WHERE (tbl_repair_deviceTkDate BETWEEN ? AND  ?  ) AND  (tbl_repair_deviceTaken  = ?) AND  (tbl_repair_deviceStatus  = ?)   ORDER BY tbl_repair_deviceId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $taken);
 $stmt->bindParam(4, $status);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}

//find repaired by id
function find_repaired_by_id($repairId)
{
global $connectMe;
 $repairId = mysql_prep($repairId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device R, tbl_brand B 
  WHERE (R.tbl_brandId  = B.tbl_brandId) AND  (R.tbl_repair_deviceId = ?)  LIMIT 1");
 $stmt->bindParam(1, $repairId);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


//find repaired by token
function find_repaired_by_token($token)
{
global $connectMe;
 $token = mysql_prep($token);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device WHERE tbl_repair_deviceToken = ?  LIMIT 1");
 $stmt->bindParam(1, $token);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}

//find_all_repaired_products for taken status
function find_all_repaired_products_for_taken($status, $businessId, $taken){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device R, tbl_employee E, tbl_brand B 
    WHERE (R.tbl_employeeId  = E.tbl_employeeId) AND (R.tbl_brandId = B.tbl_brandId) AND  (R.tbl_repair_deviceStatus = ?) 
    AND (E.tbl_businessId =?) AND (R.tbl_repair_deviceTaken =?) ORDER BY R.tbl_repair_deviceId  DESC LIMIT 20 
  ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);
  
  $stmt->bindParam(3, $taken);
  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}

//find_all_repaired_products
function find_all_repaired_products($status, $businessId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device R, tbl_employee E, tbl_brand B  WHERE (R.tbl_employeeId  = E.tbl_employeeId) AND (R.tbl_brandId = B.tbl_brandId) AND  (R.tbl_repair_deviceStatus = ?) AND (E.tbl_businessId =?) ORDER BY R.tbl_repair_deviceId  DESC LIMIT 20 ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $data;
}

//search repaired products
function search_repaired_product($search, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device R, tbl_employee E WHERE  (R.tbl_employeeId = E.tbl_employeeId)  AND ( R.tbl_repair_deviceClient LIKE  '%".$search."%'   OR  R.tbl_repair_deviceTel LIKE  '%".$search."%' OR R.tbl_repair_deviceName LIKE  '%".$search."%'  OR R.tbl_repair_deviceProblm LIKE  '%".$search."%' OR R.tbl_repair_deviceFee LIKE  '%".$search."%' OR R.tbl_repair_deviceDate LIKE  '%".$search."%'  OR E.tbl_employeeFname LIKE  '%".$search."%'   )    ORDER BY R.tbl_repair_deviceId DESC ");

 //$stmt->bindParam(1, $status);
 $stmt->execute();

 $res = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $res;

}


//search repaired products for business
function search_repaired_product_for_business($search, $status, $businessId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device R, tbl_employee E, tbl_brand B 
    WHERE (R.tbl_brandId = B.tbl_brandId ) AND  (R.tbl_employeeId = E.tbl_employeeId)  AND ( R.tbl_repair_deviceClient LIKE  '%".$search."%'   OR  R.tbl_repair_deviceTel LIKE  '%".$search."%' OR R.tbl_repair_deviceName LIKE  '%".$search."%'  OR R.tbl_repair_deviceProblm LIKE  '%".$search."%' OR R.tbl_repair_deviceFee LIKE  '%".$search."%' OR R.tbl_repair_deviceDate LIKE  '%".$search."%'  OR E.tbl_employeeFname LIKE  '%".$search."%'   ) AND (E.tbl_businessId = ?)   ORDER BY R.tbl_repair_deviceId DESC ");

  $stmt->bindParam(1, $businessId);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;

}


//count all  repaired devices
function count_all_repaired_devices($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_repair_device WHERE tbl_repair_deviceStatus = ?  ORDER BY tbl_repair_deviceId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  return $stmt->rowCount();
}




//calculate paid sold amount  
function calculatePaidSoldAmt($status, $soldId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_paid_debtAmt )  as amount FROM  tbl_paid_debt WHERE (tbl_sold_prodId  = ?)  AND  (tbl_paid_debtStatus  = ?)   ORDER BY tbl_paid_debtId  DESC ");

  $stmt->bindParam(1, $soldId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);
  
  return $data;
}


//ccalculateLastSoldAmountForProd
function calculateLastSoldAmountForProd($status, $from, $to, $productId){

 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty )  as qty FROM  tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId = P.tbl_productId)  AND (S.tbl_sold_prodDate <= ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (P.tbl_productId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $to);
 $stmt->bindParam(2, $status);

 $stmt->bindParam(3, $productId);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;

}


//ccalculateAfterSoldAmountForProd
function calculateAfterSoldAmountForProd($status, $from, $to, $productId){

 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty )  as qty FROM  tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId = P.tbl_productId)  AND (S.tbl_sold_prodDate > ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (P.tbl_productId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $to);
 $stmt->bindParam(2, $status);

 $stmt->bindParam(3, $productId);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;

}



function find_all_sold_product_for_tid($status, $tid){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod WHERE (tbl_sold_prodStatus =?)  AND ( tbl_sold_prodTid =?)  ORDER BY tbl_sold_prodId DESC ");


 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $tid);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 
 return $data;
}


function find_all_sold_product_report_for_empl($from, $to, $status, $empId)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod WHERE  (tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (tbl_sold_prodStatus =?)  AND (tbl_employeeId =?)  ORDER BY tbl_sold_prodId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $empId);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 
 return $data;
}

//calculate salary advance amount for emp 

function calculateSalaryAdvanceForEmp($status, $paid, $employeeId ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_advanceRemain )  as amount FROM  tbl_advance WHERE  (tbl_advanceStatus  = ?)  AND  (tbl_advancePaid = ?)  AND  (tbl_employeeId = ?)   ORDER BY tbl_advanceId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $paid);

  $stmt->bindParam(3, $employeeId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}



//calculate paid sold qty  
function calculatePaidSoldQty($status, $soldId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_paid_productQty )  as qty FROM  tbl_paid_product WHERE (tbl_sold_prodId  = ?)  AND  (tbl_paid_productStatus  = ?)   ORDER BY tbl_paid_productId  DESC ");

  $stmt->bindParam(1, $soldId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);
  
  return $data;
}

//transferred to branch


//calculate  transfer for stock Id  

function calculateTransferForStockId($status, $stockId ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_branch_productAvail )  as qty FROM  tbl_branch_product WHERE  (tbl_inventoryId  = ?)  AND  (tbl_branch_productStatus = ?)   ORDER BY tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $stockId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetch(PDO::FETCH_OBJ);

  return $res;
}

  //update transfer  by id
  function update_transfer_by_id($soldQty, $transferId){

    global $connectMe;
    $safeProId = mysql_prep($transferId);

    $safeSoldQty = mysql_prep($soldQty);
    $proStmt = $connectMe->prepare("UPDATE tbl_branch_product SET tbl_branch_productAvail = ? WHERE tbl_branch_productId = ? ");

    $proStmt->bindParam(1, $safeSoldQty);
    $proStmt->bindParam(2, $safeProId);

    if( $proStmt->execute()){

      return true;
 
    }else{

      return false;
    }
  }


//add new stock action
function add_new_stock_action($transferId, $totalBQty, $bQty, $availQty, $empId, $reason, $date, $time, $totalAfterQty){
 global $connectMe;

 $id = uniqid();
 $status = 1;

 $stmt = $connectMe->prepare("INSERT INTO `tbl_stock_action`(`tbl_branch_productId`, `tbl_stock_actionTotBQty`, `tbl_stock_actionBQty`, `tbl_stock_actionQty`, `tbl_employeeId`, `tbl_stock_actionReason`, `tbl_stock_actionDate`, `tbl_stock_actionTime`, `tbl_stock_actionToken`, `tbl_stock_actionStatus`, `tbl_stock_actionTotAfterQty`)
  VALUES(?,?,?, ?,?,?, ?,?,?, ?,?) ");
 
 $stmt->bindParam(1, $transferId);
 $stmt->bindParam(2, $totalBQty);

 $stmt->bindParam(3, $bQty);
 $stmt->bindParam(4, $availQty);

 $stmt->bindParam(5, $empId);
 $stmt->bindParam(6, $reason);

 $stmt->bindParam(7, $date);
 $stmt->bindParam(8, $time);

 $stmt->bindParam(9, $id);
 $stmt->bindParam(10, $status);

 $stmt->bindParam(11, $totalAfterQty);

 if($stmt->execute()){

  return true;


 }else {

  return false;

 }

 
}




  //find transfer by id
  function find_transfer_by_id($transId){

    global $connectMe;
    $safeProId = mysql_prep($transId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product WHERE tbl_branch_productId = ?  LIMIT 1");
    $stmt->bindParam(1, $safeProId);

    $stmt->execute();
    
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }

  }


  //find one transfer  by token
  function find_one_transfer_by_token($transferToken){

    global $connectMe;
    $safeProId = mysql_prep($transferToken);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product WHERE  (tbl_branch_productToken = ?)  LIMIT 1");

    $stmt->bindParam(1, $safeProId);
    $stmt->execute();
    
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }

  }


  //find transfer by token
  function find_transfer_by_token($transId){

    global $connectMe;
    $safeProId = mysql_prep($transId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_employee E, tbl_supplier SU, tbl_color CO  WHERE (I.tbl_colorId = CO.tbl_colorId) AND (I.tbl_supplierId = SU.tbl_supplierId) AND (E.tbl_employeeId = I.tbl_employeeId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (BP.tbl_productId = P.tbl_productId)  AND (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_branch_productToken = ?)  LIMIT 1");

    $stmt->bindParam(1, $safeProId);
    $stmt->execute();
    
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }

  }


  //find transfer by id
  function find_transfer_detail_by_id($transId){

    global $connectMe;
    $safeProId = mysql_prep($transId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_employee E, tbl_supplier SU, tbl_color CO  WHERE (I.tbl_colorId = CO.tbl_colorId) AND (I.tbl_supplierId = SU.tbl_supplierId) AND (E.tbl_employeeId = I.tbl_employeeId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (BP.tbl_productId = P.tbl_productId)  AND (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_branch_productId = ?)  LIMIT 1");

    $stmt->bindParam(1, $safeProId);
    $stmt->execute();
    
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }

  }



  //all other transferred for product and Branch
  function find_all_other_transferred_for_product_and_branch($proId, $status, $branchId){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product WHERE (tbl_productId = ?) AND (tbl_branch_productStatus = ?)  AND (tbl_brandId != ?)  ORDER BY tbl_branch_productId  DESC ");
 
    $stmt->bindParam(1, $proId);
    $stmt->bindParam(2, $status);

    $stmt->bindParam(3, $branchId);
    $stmt->execute();

    $res = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $res;
  }


  //all sold out transfer for product and Branch
  function find_all_sold_out_transfer_for_product_and_branch($proId, $status, $branchId){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_color CO WHERE (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_productId = P.tbl_productId) AND (P.tbl_specificationId = S.tbl_specificationId)  AND (S.tbl_product_categId = C.tbl_product_categId) AND (I.tbl_colorId = CO.tbl_colorId) AND (BP.tbl_productId = ?) AND (BP.tbl_branch_productStatus = ?)  AND (BP.tbl_brandId = ?) AND (BP.tbl_branch_productAvail= 0)  ORDER BY BP.tbl_branch_productId  DESC ");
 
    $stmt->bindParam(1, $proId);
    $stmt->bindParam(2, $status);

    $stmt->bindParam(3, $branchId);
    $stmt->execute();

    $res = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $res;
  }


  //all transferred for product and Branch
  function find_all_transferred_for_product_and_branch($proId, $status, $branchId){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_color CO WHERE (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_productId = P.tbl_productId) AND (P.tbl_specificationId = S.tbl_specificationId)  AND (S.tbl_product_categId = C.tbl_product_categId) AND (I.tbl_colorId = CO.tbl_colorId)  AND (BP.tbl_branch_productStatus = ?)  AND (BP.tbl_brandId = ?) AND (BP.tbl_productId = ?)  AND (BP.tbl_branch_productAvail > 0)  ORDER BY BP.tbl_branch_productId  DESC ");
 
    $stmt->bindParam(1, $status);
    $stmt->bindParam(2, $branchId);

    $stmt->bindParam(3, $proId);
    $stmt->execute();

    $res = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $res;
  }


  //all transferred for product and business
  function find_other_transferred_for_product_and_business($proId, $status, $branchId, $businessId){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_color CO WHERE (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_productId = P.tbl_productId) AND (P.tbl_specificationId = S.tbl_specificationId)  AND (S.tbl_product_categId = C.tbl_product_categId) AND (I.tbl_colorId = CO.tbl_colorId)  AND (BP.tbl_branch_productStatus = ?)  AND (BP.tbl_brandId != ?) AND (BP.tbl_productId = ?) AND (B.tbl_businessId = ?)  AND (BP.tbl_branch_productAvail > 0)  ORDER BY BP.tbl_branch_productId  DESC ");
 
    $stmt->bindParam(1, $status);
    $stmt->bindParam(2, $branchId);

    $stmt->bindParam(3, $proId);
    $stmt->bindParam(4, $businessId);

    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $res;
  }


  //all sold out transferred for product 
  function find_all_soldout_transfer_for_product($proId, $status ){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_color CO WHERE (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_productId = P.tbl_productId) AND (P.tbl_specificationId = S.tbl_specificationId)  AND (S.tbl_product_categId = C.tbl_product_categId) AND (I.tbl_colorId = CO.tbl_colorId) AND (BP.tbl_productId = ?) AND (BP.tbl_branch_productStatus = ?)  AND (BP.tbl_branch_productAvail = 0)   ORDER BY BP.tbl_branch_productId  DESC LIMIT 20");
 
    $stmt->bindParam(1, $proId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $res;
  }


  //all transferred for product 
  function find_all_transferred_for_product($proId, $status ){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product BP, tbl_inventory I, tbl_brand B, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_color CO WHERE (BP.tbl_inventoryId = I.tbl_inventoryId) AND (BP.tbl_brandId = B.tbl_brandId) AND (BP.tbl_productId = P.tbl_productId) AND (P.tbl_specificationId = S.tbl_specificationId)  AND (S.tbl_product_categId = C.tbl_product_categId) AND (I.tbl_colorId = CO.tbl_colorId) AND (BP.tbl_productId = ?) AND (BP.tbl_branch_productStatus = ?)  AND (BP.tbl_branch_productAvail > 0)   ORDER BY BP.tbl_branch_productId  DESC ");
 
    $stmt->bindParam(1, $proId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $res = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $res;
  }


////////////////// loan ////////////////////////////////////////


//calculate loan amount for type and business

function calculateLoanAmountForTypeAndBusId($status, $from, $to, $loanType, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_loanAmt )  as amount FROM  tbl_loan WHERE (tbl_loanDate BETWEEN ? AND  ?  ) AND  (tbl_loanStatus  = ?) AND  (tbl_loanPaid  = 0)  AND  (tbl_loan_typeId  = ?)  AND  (tbl_businessId  = ?)   ORDER BY tbl_loanId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $loanType);

  $stmt->bindParam(5, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}


//calculate loan amount for type

function calculateLoanAmountForType($status, $from, $to, $loanType){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_loanAmt )  as amount FROM  tbl_loan WHERE (tbl_loanDate BETWEEN ? AND  ?  ) AND  (tbl_loanStatus  = ?) AND  (tbl_loanPaid  = 0)  AND  (tbl_loan_typeId  = ?)   ORDER BY tbl_loanId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $loanType);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}



//find loan type by name
function find_loan_type_by_name($typeName){

  global $connectMe;
  $typeName = mysql_prep($typeName);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan_type  WHERE tbl_loan_typeName = ?  LIMIT 1");
  $stmt->bindParam(1, $typeName);
  $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}




//find loan type by id
function find_loan_type_by_id($proId){

  global $connectMe;
  $safeProId = mysql_prep($proId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan_type  WHERE tbl_loan_typeId = ?  LIMIT 1");
  $stmt->bindParam(1, $safeProId);
  $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


//all  loan for type and business
function find_all_loan_for_type_and_business($type, $paid, $status, $businessId ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan WHERE (tbl_loanPaid = ?)  AND (tbl_loanStatus = ?) AND (tbl_loan_typeId = ?)  AND (tbl_businessId = ?)  ORDER BY tbl_loanId  DESC ");

  $stmt->bindParam(1, $paid);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $type);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}



//all  loan for type
function find_all_loan_for_type($type, $paid, $status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan WHERE (tbl_loanPaid = ?)  AND (tbl_loanStatus = ?) AND (tbl_loan_typeId = ?)  ORDER BY tbl_loanId  DESC ");

  $stmt->bindParam(1, $paid);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $type);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//all  loan type
function find_all_loan_type($status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan_type WHERE (tbl_loan_typeStatus = ?)  ORDER BY tbl_loan_typeId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//find loan by id
function find_loan_by_id($proId){

  global $connectMe;
  $safeProId = mysql_prep($proId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan  WHERE tbl_loanId = ?  LIMIT 1");
  $stmt->bindParam(1, $safeProId);
  $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


//find loan by token
function find_loan_by_token($proId){

  global $connectMe;
  $safeProId = mysql_prep($proId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan  WHERE tbl_loanToken = ?  LIMIT 1");
  $stmt->bindParam(1, $safeProId);
  $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}



//all  loan 
function find_all_loan($paid, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan WHERE (tbl_loanPaid = ?)  AND (tbl_loanStatus = ?)   ORDER BY tbl_loanId  DESC ");

  $stmt->bindParam(1, $paid);
  $stmt->bindParam(2, $status);

  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//all  loan for business
function find_all_loan_for_business($paid, $status, $businessId ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_loan WHERE (tbl_loanPaid = ?)  AND (tbl_loanStatus = ?) AND (tbl_businessId = ?)  ORDER BY tbl_loanId  DESC ");

  $stmt->bindParam(1, $paid);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $businessId);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}



///////////////////////////////////////////////////////////////



///////////////////  salary //////////////////////////////////



//all paid amount for advance
function find_all_paid_amount_for_advance($advanceId, $status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM  tbl_paid WHERE (tbl_advanceId = ?) AND (tbl_paidStatus = ?)   ORDER BY tbl_paidId  DESC ");
 
  $stmt->bindParam(1, $advanceId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  
  return $res;
}



//find salary  advance  by id
function update_advance_by_id($remain, $proId, $paid){

  global $connectMe;
  $safeProId = mysql_prep($proId);
  $remain = mysql_prep($remain);

  $stmt = $connectMe->prepare("UPDATE tbl_advance SET tbl_advanceRemain = ?, tbl_advancePaid = ?  WHERE  tbl_advanceId = ? ");
  $stmt->bindParam(1, $remain);
  $stmt->bindParam(2, $paid);

  $stmt->bindParam(3, $safeProId);

  if( $stmt->execute()){

    return true;
 
  }else{

    return false;
  }

}




//find advance by id
function find_advance_by_id($proId){

 global $connectMe;
 $safeProId = mysql_prep($proId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_advance  WHERE tbl_advanceId = ?  LIMIT 1");
 $stmt->bindParam(1, $safeProId);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


//find advance by token
function find_advance_by_token($proId){

 global $connectMe;
 $safeProId = mysql_prep($proId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_advance  WHERE tbl_advanceToken = ?  LIMIT 1");
 $stmt->bindParam(1, $safeProId);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}



//calculate paid salary advance  

function calculatePaidSalaryAdvance($status, $advanceId ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_paidAmt )  as amount FROM  tbl_paid WHERE  (tbl_advanceId  = ?)  AND  (tbl_paidStatus = ?)   ORDER BY tbl_paidId  DESC ");

  $stmt->bindParam(1, $advanceId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetch(PDO::FETCH_OBJ);

  return $res;
}


//all  advance  for employee
function find_all_advance_for_employee($empId, $paid, $status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_advance WHERE (tbl_employeeId = ?) AND (tbl_advancePaid = ?) AND (tbl_advanceStatus   = ?)  ORDER BY tbl_advanceId  DESC ");

  $stmt->bindParam(1, $empId);
  $stmt->bindParam(2, $paid);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}



//calculate salary advance amount for business

function calculateSalaryAdvanceForBusId($status, $from, $to, $paid, $businessId ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(A.tbl_advanceRemain )  as amount FROM  tbl_advance A, tbl_employee E WHERE (A.tbl_advanceDate BETWEEN ? AND  ?  ) AND  (A.tbl_advanceStatus  = ?)  AND  (A.tbl_advancePaid = ?)  AND  (E.tbl_businessId = ?)  AND  (A.tbl_employeeId = E.tbl_employeeId)   ORDER BY A.tbl_advanceId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $paid);

  $stmt->bindParam(5, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}

//calculate salary advance USD amount 

function calculateSalaryAdvance($status, $from, $to, $paid ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_advanceRemain )  as amount FROM  tbl_advance WHERE (tbl_advanceDate BETWEEN ? AND  ?  ) AND  (tbl_advanceStatus  = ?)  AND  (tbl_advancePaid = ?)   ORDER BY tbl_advanceId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $paid);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}



//////////////////////////////////////////////////////////////

////////////////////////// transit ///////////////////////////

//find transit by id
function find_transit_by_id($proId){
  
global $connectMe;
 $safeProId = mysql_prep($proId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_transit  WHERE tbl_transitId = ?  LIMIT 1");
 $stmt->bindParam(1, $safeProId);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


//find transit by token
function find_transit_by_token($proId){

global $connectMe;
 $safeProId = mysql_prep($proId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_transit  WHERE tbl_transitToken = ?  LIMIT 1");
 $stmt->bindParam(1, $safeProId);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}



//all  transit  for categories
function find_all_transit_for_categ($category, $arrived, $status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_transit WHERE (tbl_product_categId = ?) AND (tbl_transitArrived = ?) AND (tbl_transitStatus = ?)  ORDER BY tbl_transitId  DESC ");

  $stmt->bindParam(1, $category);
  $stmt->bindParam(2, $arrived);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//calculate transit amount for business
function calculateTransitAmountForBusId($status, $from, $to, $arrived, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( T.tbl_transitCost )  as amount FROM  tbl_transit T, tbl_product_categ C WHERE (T.tbl_transitDate BETWEEN ? AND  ?  ) AND  (T.tbl_transitStatus  = ?)  AND  (T.tbl_transitArrived  = ?)  AND  (C.tbl_businessId  = ?)  AND  (C.tbl_product_categId  = T.tbl_product_categId)    ORDER BY T.tbl_transitId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $arrived);

  $stmt->bindParam(5, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//calculate transit amount 
function calculateTransitAmount($status, $from, $to, $arrived ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( tbl_transitCost )  as amount FROM  tbl_transit WHERE (tbl_transitDate BETWEEN ? AND  ?  ) AND  (tbl_transitStatus  = ?)  AND  (tbl_transitArrived  = ?)   ORDER BY tbl_transitId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $arrived);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}



/////////////////////////////////////////////////////////////


////////////////////////// bank /////////////////////////////

//find bank amount by token
function find_bank_amount_by_token($bankToken){

  global $connectMe;
  $bankToken = mysql_prep($bankToken);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_bank WHERE tbl_bankToken = ?  LIMIT 1");
  $stmt->bindParam(1, $bankToken);

  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}

//all bank amount for business
function find_all_bank_amount_for_business($businessId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_bank WHERE (tbl_businessId = ?) AND (tbl_bankStatus = ?)  ORDER BY tbl_bankId DESC ");

  $stmt->bindParam(1, $businessId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}

//all bank amount
function find_all_bank_amount($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_bank WHERE tbl_bankStatus = ?  ORDER BY tbl_bankId DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//calculate bank USD amount for business

function calculateBankUsdAmountForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_bankUSD )  as amount FROM  tbl_bank WHERE (tbl_bankDate BETWEEN ? AND  ?  ) AND  (tbl_bankStatus  = ?)  AND  (tbl_businessId  = ?)   ORDER BY tbl_bankId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//calculate bank USD amount 

function calculateBankUsdAmount($status, $from, $to){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_bankUSD )  as amount FROM  tbl_bank WHERE (tbl_bankDate BETWEEN ? AND  ?  ) AND  (tbl_bankStatus  = ?)   ORDER BY tbl_bankId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//convert bank USD amount for business

function convertBankUsdAmountForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_bankUSD * tbl_bankUSDValue )  as amount FROM  tbl_bank WHERE (tbl_bankDate BETWEEN ? AND  ?  ) AND  (tbl_bankStatus  = ?) AND  (tbl_businessId  = ?)   ORDER BY tbl_bankId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//convert bank USD amount 

function convertBankUsdAmount($status, $from, $to){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_bankUSD * tbl_bankUSDValue )  as amount FROM  tbl_bank WHERE (tbl_bankDate BETWEEN ? AND  ?  ) AND  (tbl_bankStatus  = ?)   ORDER BY tbl_bankId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//calculate bank amount for business

function calculateBankAmountForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_bankRWF )  as amount FROM  tbl_bank WHERE (tbl_bankDate BETWEEN ? AND  ?  ) AND  (tbl_bankStatus  = ?) AND  (tbl_businessId  = ?)   ORDER BY tbl_bankId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate bank amount 

function calculateBankAmount($status, $from, $to){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_bankRWF )  as amount FROM  tbl_bank WHERE (tbl_bankDate BETWEEN ? AND  ?  ) AND  (tbl_bankStatus  = ?)   ORDER BY tbl_bankId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


/////////////////////////////////////////////////////////////


//////////////// income  ///////////////////


//find income   by token
function find_income_by_token($incomeToken){

  global $connectMe;
  $incomeToken = mysql_prep($incomeToken);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_income WHERE tbl_incomeToken = ?  LIMIT 1");
  $stmt->bindParam(1, $incomeToken);

  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}

//all income for business
function find_all_income_for_business($businessId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_income I, tbl_brand B  WHERE (I.tbl_brandId = B.tbl_brandId) AND (B.tbl_businessId = ?)  AND (I.tbl_incomeStatus = ?)  ORDER BY I.tbl_incomeId DESC ");

  $stmt->bindParam(1, $businessId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}


//all income
function find_all_income($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_income WHERE tbl_incomeStatus = ?  ORDER BY tbl_incomeId DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}



//count all  income
function count_all_income($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_income WHERE tbl_incomeStatus = ?  ORDER BY tbl_incomeId DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  return $stmt->rowCount();
}


//calculate income amount for business

function calculateIncomeAmountForBusiness($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_incomeAmt )  as amount FROM  tbl_income I, tbl_brand B  WHERE (I.tbl_incomeDate BETWEEN ? AND  ?  ) AND  (I.tbl_incomeStatus  = ?) AND  (B.tbl_businessId  = ?) AND  (B.tbl_brandId  = I.tbl_brandId)    ORDER BY I.tbl_incomeId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate income amount 

function calculateIncomeAmount($status, $from, $to){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_incomeAmt )  as amount FROM  tbl_income WHERE (tbl_incomeDate BETWEEN ? AND  ?  ) AND  (tbl_incomeStatus  = ?)   ORDER BY tbl_incomeId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}

/////////////////////////////////////////////////



//////////////// expenses  ///////////////////


//find expense   by token
function find_expense_by_token($expenseToken)
{
global $connectMe;
 $expenseToken = mysql_prep($expenseToken);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_expense WHERE tbl_expenseToken = ?  LIMIT 1");
 $stmt->bindParam(1, $expenseToken);
 $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//count all  expenses
function count_all_expenses($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_expense WHERE tbl_expenseStatus = ?  ORDER BY tbl_expenseId DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  return $stmt->rowCount();
}


//all expenses for businesss
function find_all_expenses_for_business($businesssId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_expense E, tbl_brand B  WHERE (E.tbl_brandId = B.tbl_brandId) AND (B.tbl_businessId = ?) AND (E.tbl_expenseStatus = ?)  ORDER BY E.tbl_expenseId DESC ");

  $stmt->bindParam(1, $businesssId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}


//all expenses
function find_all_expenses($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_expense WHERE tbl_expenseStatus = ?  ORDER BY tbl_expenseId DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//calculate expense amount for business
function calculateExpenseAmountForBus($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_expenseAmt )  as amount FROM  tbl_expense E, tbl_brand B WHERE (E.tbl_brandId  = B.tbl_brandId) AND (E.tbl_expenseDate BETWEEN ? AND  ?  ) AND  (E.tbl_expenseStatus  = ?)  AND  (B.tbl_businessId  = ?)   ORDER BY E.tbl_expenseId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate expense amount 
function calculateExpenseAmount($status, $from, $to){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_expenseAmt )  as amount FROM  tbl_expense WHERE (tbl_expenseDate BETWEEN ? AND  ?  ) AND  (tbl_expenseStatus  = ?)   ORDER BY tbl_expenseId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}

/////////////////////////////////////////////////


//calculate sold qty for brand
function calculateSoldQtyForBrand($status, $from, $to, $brandId)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty )  as qty FROM  tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId = P.tbl_productId)  AND (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (P.tbl_specificationId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $brandId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}




//calculate bought amount for brand
function calculateRemaingBoughtAmountForBrand($status, $from, $to, $brandId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT SUM( (I.tbl_inventoryCost) * ( S.tbl_sold_prodQty)   )  as amount FROM  tbl_inventory I, tbl_sold_prod S, tbl_product P  WHERE (I.tbl_inventoryId = S.tbl_inventoryId) AND (I.tbl_productId = P.tbl_productId) AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  )  AND (S.tbl_sold_prodStatus  = ?)  AND (P.tbl_specificationId  = ?)     ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $brandId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate sold amount for sell type and business
function calculateSoldAmountForSellTypeAndBusId($status, $from, $to, $sellType, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodAmount * S.tbl_sold_prodQty )  as amount FROM  tbl_sold_prod S, tbl_product P, tbl_branch_product BP, tbl_brand B WHERE (S.tbl_productId = P.tbl_productId)  AND (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (S.tbl_sell_typeId  = ?) AND (S.tbl_branch_productId = BP.tbl_branch_productId)  AND (B.tbl_brandId = BP.tbl_brandId)  AND (B.tbl_businessId = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $sellType);

  $stmt->bindParam(5, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}



//calculate sold amount for sell type
function calculateSoldAmountForSellType($status, $from, $to, $sellType)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodAmount * S.tbl_sold_prodQty )  as amount FROM  tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId = P.tbl_productId)  AND (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (S.tbl_sell_typeId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $sellType);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate bought amount for sell type and business

function calculateRemaingBoughtAmountForSellTypeAndBusId($status, $from, $to, $sellType, $businessId){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT SUM( (I.tbl_inventoryCost) * ( S.tbl_sold_prodQty)   )  as amount FROM  tbl_inventory I, tbl_sold_prod S,  tbl_branch_product BP, tbl_brand B  WHERE (I.tbl_inventoryId = S.tbl_inventoryId)  AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  )  AND (S.tbl_sold_prodStatus  = ?)  AND (S.tbl_sell_typeId  = ?) AND (S.tbl_branch_productId = BP.tbl_branch_productId)  AND (B.tbl_brandId = BP.tbl_brandId)  AND (B.tbl_businessId = ?)     ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $sellType);

  $stmt->bindParam(5, $businessId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}

//calculate bought amount for sell type

function calculateRemaingBoughtAmountForSellType($status, $from, $to, $sellType){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT SUM( (I.tbl_inventoryCost) * ( S.tbl_sold_prodQty)   )  as amount FROM  tbl_inventory I, tbl_sold_prod S  WHERE (I.tbl_inventoryId = S.tbl_inventoryId)  AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  )  AND (S.tbl_sold_prodStatus  = ?)  AND (S.tbl_sell_typeId  = ?)     ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $sellType);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}



//calculate sold amount for brand
function calculateSoldAmountForBrand($status, $from, $to, $brandId)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodAmount * S.tbl_sold_prodQty )  as amount FROM  tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId = P.tbl_productId)  AND (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (P.tbl_specificationId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $brandId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//find branch product by id 
function find_branch_product_by_id ($token){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product WHERE (tbl_branch_productId = ?)   LIMIT 1");
  $stmt->bindParam(1, $token);
  $stmt->execute();

  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


//find branch product by token 
function find_branch_product_by_token ($token){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product WHERE (tbl_branch_productToken = ?)   LIMIT 1");
  $stmt->bindParam(1, $token);
  $stmt->execute();

  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}

//all BOUGHT TRANSACTION FOR PRODUCT AND BRANCH
function find_all_bought_transaction_for_products_and_branch($proId, $status, $branchId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory WHERE (tbl_productId = ?) AND (tbl_inventoryStatus = ?)  AND (tbl_brandId = ?)  ORDER BY tbl_inventoryId  DESC ");
 
  $stmt->bindParam(1, $proId);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $branchId);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}

///////////////////////// colors function////////////////////


  //all color
  function all_color($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_color WHERE tbl_colorStatus = ? ORDER BY tbl_colorId ASC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();
       
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }



//find color  by id
function find_color_by_id($colorId){

  global $connectMe;
  $colorId = mysql_prep($colorId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_color  WHERE tbl_colorId = ?  LIMIT 1");
  $stmt->bindParam(1, $colorId);
 
  $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}



/////////////////////////////////////////////////////////////


///////////////// made function  ////////////////////////////


  //all made
  function all_made($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_made WHERE tbl_madeStatus = ? ORDER BY tbl_madeId DESC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();
       
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }



//find made  by id
function find_made_by_id($madeId){

  global $connectMe;
  $madeId = mysql_prep($madeId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_made  WHERE tbl_madeId = ?  LIMIT 1");
  $stmt->bindParam(1, $madeId);
 
  $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}



//////////////////////////////////////////////////////

function Auth($user){
  if (!isset($user)) {
  
  echo'<script> window.location = "../user/login"; </script>';
  exit();
 }
}


function FullAuth($user1, $user2, $user3 ){
  if ( (!isset($user1)) &&  (!isset($user2))  &&  (!isset($user3)) ) {
  
  echo'<script> window.location = "../user/login"; </script>';
  exit();
 }
}


function FullUserAuth($user1, $user2, $user3){
  if ( (!isset($user1)) &&  (!isset($user2))  &&  (!isset($user3)) ) {
  
  echo'<script> window.location = "../user/login"; </script>';
  exit();
 }
}

function FullSpecialUserAuth($user1, $user2, $user3, $user4, $user5){
  if ( (!isset($user1)) &&  (!isset($user2))  &&  (!isset($user3))  &&  (!isset($user4))  &&  (!isset($user5)) ) {
  
  echo'<script> window.location = "../user/login"; </script>';
  exit();
 }
}



function ForAllUserAuth($user1, $user2, $user3, $user4, $user5, $user6){

  if ( (!isset($user1)) &&  (!isset($user2))  &&  (!isset($user3))  &&  (!isset($user4))  &&  (!isset($user5))  &&  (!isset($user6))   ) {
  
      echo'<script> window.location = "../user/login"; </script>';
      exit();
    }

}




//check if other user is set
function otherUserSet(){
  if (isset($_SESSION['U_id'])) {
  
  echo'<script> window.location = "../other_member/home"; </script>';
  exit();
 }
}

//check if admin user is set
function adminUserSet(){
  if (isset($_SESSION['adminId'])) {
      echo'<script> window.location = "../user/index"; </script>';
  
  exit();
 }
}

//check if other user is not set
function otherUserNotSet(){
  if (!isset($_SESSION['U_id'])) {
  //header("location:");
  echo'<script> window.location = "../user/login"; </script>';
  exit();
 }
}


//check if admin user is set
function adminUserNotSet(){
  if (!isset($_SESSION['adminId'])) {
  //header("location:");
    echo'<script> window.location = "../user/login"; </script>';
  exit();
 }
}

function confirm_query($result_set)
{
if (!$result_set) {
   die("<h1>There is something wrong</h1>");
}
}

function mysql_prep($string){

  global $connectMe;

  $escaped_string = htmlentities($string);
  $escaped_string = stripslashes($string);

  $escaped_string = htmlspecialchars($string);
  $escaped_string = filter_var($string , FILTER_SANITIZE_STRING);

  $escaped_string = strip_tags($string);

  if(str_replace("/","", $escaped_string)){

    $newString = str_replace("/","", $escaped_string);

    $escaped_string = trim($newString);

  } else{

    $escaped_string = trim($string);

  }

  return $escaped_string ;
}


/*
function mysql_prep($string){

global $connectMe;

  $escaped_string = htmlentities($string);
  $escaped_string = stripslashes($string);

  $escaped_string = htmlspecialchars($string);
  $escaped_string = filter_var($string , FILTER_SANITIZE_STRING);

  $escaped_string = strip_tags($string);
  //$replacedString = str_replace("/","", $escaped_string);
  
  $escaped_string = trim($string);
  return $escaped_string ;
}

*/

/******  technician functions *******************************************/

//all technician  
function find_all_technician($status)  {

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_technician WHERE tbl_technicianStatus  = ? ORDER BY tbl_technicianId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}



/* users functions */

//all active users
function find_all_active_users()
 {
 global $connectMe;
 $userStmt = $connectMe->prepare("SELECT * FROM tbl_user WHERE tbl_userStatus = ? AND tbl_userLogIn = ?  ORDER BY tbl_userName ASC ");
 $status = 1;
 $online = 1;
 $userStmt->bindParam(1, $status);
 $userStmt->bindParam(2, $online);
 $userStmt->execute();

 $user = $userStmt->fetchAll(PDO::FETCH_OBJ);
 return $user;
}

//all new users
function find_all_new_users()
 {
 global $connectMe;
 $userStmt = $connectMe->prepare("SELECT * FROM tbl_user WHERE tbl_userStatus = ?  AND tbl_userNew = ?  ORDER BY tbl_userName ASC ");
 $status = 1;
 $new = 1;
 $userStmt->bindParam(1, $status);
 $userStmt->bindParam(2, $new);
 $userStmt->execute();

 $user = $userStmt->fetchAll(PDO::FETCH_OBJ);
 return $user;
}






//count all  users
function count_all_users($status)
 {
 global $connectMe;
 $userStmt = $connectMe->prepare("SELECT * FROM tbl_user WHERE tbl_userStatus = ? ORDER BY tbl_userName ASC ");
 
 $userStmt->bindParam(1, $status);
 $userStmt->execute();

 return $userStmt->rowCount();
}


//find_all_users_for_business
function find_all_users_for_business($businessId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_user U, tbl_employee E WHERE (U.tbl_employeeId = E.tbl_employeeId ) AND (E.tbl_businessId = ?) AND (U.tbl_userStatus = ?) ORDER BY U.tbl_userName ASC ");

  $stmt->bindParam(1, $businessId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $data;
}


//count_all_users_for_branch
function count_all_users_for_branch($branch, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_user WHERE (tbl_brandId = ?) AND (tbl_userStatus = ?) ORDER BY tbl_userName ASC ");

  $stmt->bindParam(1, $branch);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  return $stmt->rowCount();
}






function find_user_by_token($userId)
 {
 
 global $connectMe;
 $safeUserId = mysql_prep($userId);

 $userStmt = $connectMe->prepare("SELECT * FROM tbl_user WHERE tbl_userToken = ?  LIMIT 1");
 $userStmt->bindParam(1, $safeUserId);

 $userStmt->execute();
        
 if ($user = $userStmt->fetch(PDO::FETCH_OBJ)) {
  return $user;
 }
}



//all  users category
function find_all_user_category()
 {
 global $connectMe;
 $userTypeStmt = $connectMe->prepare("SELECT * FROM tbl_user_categ WHERE tbl_user_categStatus = ? ORDER BY tbl_user_categName ASC ");
 $status = 1;
 
 $userTypeStmt->bindParam(1, $status);
 $userTypeStmt->execute();

 $userType = $userTypeStmt->fetchAll(PDO::FETCH_OBJ);
 return $userType;
}


// find user category by id
function find_user_categ_by_id($typeId)
 {
 
 global $connectMe;
 $safeTypeId = mysql_prep($typeId);

 $typeStmt = $connectMe->prepare("SELECT * FROM tbl_role WHERE tbl_roleId = ?  LIMIT 1");
 $typeStmt->bindParam(1, $safeTypeId);
 $typeSet = $typeStmt->execute();
        
 confirm_query($typeSet);
 if ($type = $typeStmt->fetch(PDO::FETCH_OBJ)) {
  return $type;
 }
}


function AllUserAuth($user1, $user2, $user3, $user4){
  if ( (!isset($user1)) &&  (!isset($user2))  &&  (!isset($user3))  &&  (!isset($user4))   ) {
  
  echo'<script> window.location = "../user/login.php"; </script>';
  exit();
 }
}


//add user to active
function add_user_to_active($userId)
  {
   global $connectMe;
   $safeUserId = mysql_prep($userId);

   $stmt = $connectMe->prepare("UPDATE tbl_user  SET tbl_userLogIn = 1  WHERE tbl_userId = ?");
   $stmt->bindParam(1, $safeUserId);
    $stmt->execute();
  }

 //REMOVE user to active

 function remove_user_from_active($userId)
  {
   global $connectMe;
   $safeUserId = mysql_prep($userId);
   $stmt = $connectMe->prepare("UPDATE tbl_user  SET tbl_userLogIn = 0  WHERE tbl_userId = ?");
   
   $stmt->bindParam(1, $safeUserId);
   $stmt->execute();
  }

// check if user credential is set
function check_user_gredential($userId, $username, $userTypeId)
{
global $connectMe;
 $safeUserId = mysql_prep($userId);
 $safeUsername = mysql_prep($username);
 $safeUserTypeId = mysql_prep($userTypeId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_user  WHERE tbl_userId= ? AND tbl_userName = ? AND tbl_user_categId = ? LIMIT 1");
 $stmt->bindParam(1, $safeUserId);
 $stmt->bindParam(2, $safeUsername);
 $stmt->bindParam(3, $safeUserTypeId);
 $userSet = $stmt->execute();
        
 confirm_query($userSet);
 if ($user = $stmt->fetch(PDO::FETCH_OBJ)) {
 return $user;
 }

}




// find employee by id information

function find_employee_by_id($empId)
{
global $connectMe;
 $safeEmpId = mysql_prep($empId);

 $empStmt = $connectMe->prepare("SELECT * FROM tbl_employee  WHERE tbl_employeeId = ?  LIMIT 1");
 $empStmt->bindParam(1, $safeEmpId);
 $empStmt->execute();
        
  if ($emp = $empStmt->fetch(PDO::FETCH_OBJ)) {
    return $emp;
  }

}


// find employee by token information

function find_employee_by_token($empId)
{
global $connectMe;
 $safeEmpId = mysql_prep($empId);

 $empStmt = $connectMe->prepare("SELECT * FROM tbl_employee  WHERE tbl_employeeToken = ?  LIMIT 1");
 $empStmt->bindParam(1, $safeEmpId);
 $empStmt->execute();
        
  if ($emp = $empStmt->fetch(PDO::FETCH_OBJ)) {
    return $emp;
  }

}

//find employee type by id
function find_employee_type_by_id($typeId)
{
global $connectMe;
 $safeTypeId = mysql_prep($typeId);

 $empTypeStmt = $connectMe->prepare("SELECT * FROM tbl_emp_type  WHERE tbl_emp_typeId = ?  LIMIT 1");
 $empTypeStmt->bindParam(1, $safeTypeId);
 $empTypeStmt->execute();
        
  if ($empType = $empTypeStmt->fetch(PDO::FETCH_OBJ)) {
    return $empType;
  }

}


//find employee type by id
function find_all_employee_types($status)
{
global $connectMe;
 $safeStatus = mysql_prep($status);

 $empTypeStmt = $connectMe->prepare("SELECT * FROM tbl_emp_type  WHERE tbl_emp_typeStatus  = ?  ");
 $empTypeStmt->bindParam(1, $safeStatus);
 $empTypeStmt->execute();
        
 $empType = $empTypeStmt->fetchAll(PDO::FETCH_OBJ);
 return $empType;
  

}




/* village functions  */

//find village  by id
function find_village_by_id($villId)
{
global $connectMe;
 $safeVillId = mysql_prep($villId);

 $villStmt = $connectMe->prepare("SELECT * FROM tbl_village  WHERE tbl_villageId = ?  LIMIT 1");
 $villStmt->bindParam(1, $safeVillId);
 $villStmt->execute();
        
  if ($vill = $villStmt->fetch(PDO::FETCH_OBJ)) {
    return $vill;
  }

}


//find villages for cell  by id
function find_village_for_cell($cellId)
{
global $connectMe;
 $safeCellId = mysql_prep($cellId);

 $villStmt = $connectMe->prepare("SELECT * FROM tbl_village  WHERE tbl_cellId = ? ");
 $villStmt->bindParam(1, $safeCellId);
 $villStmt->execute();
        
 $vill = $villStmt->fetchAll(PDO::FETCH_OBJ);
 return $vill;
}







/* customers function  */

//search customers for business
function search_customers_for_business($search, $status, $businessId){
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE ( tbl_customerFname  LIKE '%".$search."%' OR  tbl_customerLname  LIKE '%".$search."%' OR tbl_customerPhone  LIKE '%".$search."%' ) AND (tbl_customerStatus = ?) AND  (tbl_businessId = ?) ORDER BY tbl_customerId DESC ");

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $businessId);

 $stmt->execute();
 $cust = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $cust;
}



//all customers for business
function find_all_customers_for_business($businessId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE (tbl_businessId = ?) AND (tbl_customerStatus = ?)  ORDER BY tbl_customerId DESC ");

 $stmt->bindParam(1, $businessId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $cust = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $cust;
}

//all new  customers
function find_all_new_customers()
 {
 global $connectMe;
 $custStmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerStatus = ? AND tbl_customerNew = ?  ORDER BY tbl_customerFname ASC ");
 $status = 1;
 $new = 1;
 $custStmt->bindParam(1, $status);
 $custStmt->bindParam(2, $new);

 $custStmt->execute();

 $cust = $custStmt->fetchAll(PDO::FETCH_OBJ);
 return $cust;
}


//all customers
function find_all_customers($status)
 {
 global $connectMe;
 $custStmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerStatus = ?  ORDER BY tbl_customerId DESC ");

 $custStmt->bindParam(1, $status);
 $custStmt->execute();

 $cust = $custStmt->fetchAll(PDO::FETCH_OBJ);
 return $cust;
}


//count all  customers
function count_all_customers($status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerStatus = ?  ORDER BY tbl_customerId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 return $stmt->rowCount();
}


//search customers
function search_customers($search, $status)
 {
 global $connectMe;
 $custStmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE ( tbl_customerFname  LIKE '%".$search."%' OR  tbl_customerLname  LIKE '%".$search."%' OR tbl_customerPhone  LIKE '%".$search."%' ) AND  tbl_customerStatus = ?  ORDER BY tbl_customerId DESC ");

 $custStmt->bindParam(1, $status);
 $custStmt->execute();

 $cust = $custStmt->fetchAll(PDO::FETCH_OBJ);
 return $cust;
}



//find customer   by id
function find_customer_by_id($custId)
{
global $connectMe;
 $safeCustId = mysql_prep($custId);

 $customerStmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerId = ?  LIMIT 1");
 $customerStmt->bindParam(1, $safeCustId);
 $customerStmt->execute();
        
  if ($cust = $customerStmt->fetch(PDO::FETCH_OBJ)) {
    return $cust;
  }

}


//find customer   by token
function find_customer_by_token($custToken)
{
global $connectMe;
 $safeCustId = mysql_prep($custToken);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerToken = ?  LIMIT 1");
 $stmt->bindParam(1, $safeCustId);
 $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//all new products
function find_customer_by_name($name)
 {
 global $connectMe;
 $custStmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerFname LIKE %'?'%    ORDER BY tbl_customerFname ASC ");

 $custStmt->bindParam(1, $name);
 $custStmt->execute();

 while($cust = $custStmt->fetch(PDO::FETCH_OBJ)){
    
    echo '<tr><td>'.$cust->tbl_customerFname. '</td></tr>';

 }

}

//all new products
function find_bought_products_for_customer($custId)
 {
 global $connectMe;
  
 $bgtProStmt = $connectMe->prepare("SELECT * FROM  tbl_sold_prod WHERE  tbl_customerId  = ?   ORDER BY tbl_sold_prodDate  DESC ");

 $bgtProStmt->bindParam(1, $custId);
 $bgtProStmt->execute();

 $bgtPro = $bgtProStmt->fetchAll(PDO::FETCH_OBJ);
 return $bgtPro;
}



/* products functions */



//all  products
function find_all_products($status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE tbl_productStatus = ?  ORDER BY tbl_productId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


//find_all_selected_products
function find_all_selected_products($spec1, $spec2, $status, $businessId){

 global $connectMe;
 // $stmt = $connectMe->prepare("SELECT  P.tbl_productId, P.tbl_productBrand, P.tbl_productDate,
 //        P.tbl_productQty, SP.tbl_specificationYear, C.tbl_product_categName,  COALESCE(SUM(S.tbl_sold_prodQty), 0) AS total_sales  

 //        FROM tbl_product P, tbl_specification SP, tbl_product_categ C, tbl_sold_prod S WHERE (SP.tbl_specificationId = P.tbl_specificationId) AND (SP.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_productId = S.tbl_productId) AND (P.tbl_productStatus = ?)  AND  (C.tbl_businessId = ?)  
 //        GROUP BY P.tbl_productId, P.tbl_productBrand, P.tbl_productDate,
 //        P.tbl_productQty, SP.tbl_specificationYear, C.tbl_product_categName
 //        ORDER BY total_sales DESC LIMIT 40 ");

$stmt = $connectMe->prepare("
    SELECT 
        P.tbl_productId,
        P.tbl_productBrand,
        P.tbl_productDate,
        P.tbl_productQty,
        COALESCE(S.total_sales, 0) AS total_sales,
        P.tbl_specificationId,
        C.tbl_product_categId,
        SP.tbl_specificationYear, 
        C.tbl_product_categName
    FROM tbl_product P
    INNER JOIN tbl_specification SP ON SP.tbl_specificationId = P.tbl_specificationId
    INNER JOIN tbl_product_categ C ON SP.tbl_product_categId = C.tbl_product_categId
    LEFT JOIN (
        SELECT tbl_productId, SUM(tbl_sold_prodQty) AS total_sales
        FROM tbl_sold_prod
        GROUP BY tbl_productId
    ) S ON P.tbl_productId = S.tbl_productId
    WHERE P.tbl_productStatus = ? 
      AND C.tbl_businessId = ?
    ORDER BY total_sales DESC LIMIT 40
");


 // $stmt->bindParam(1, $spec1);
 // $stmt->bindParam(2, $spec2);

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $businessId);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 
 return $data;
}




//find_all_products_for_spec
function find_all_products_for_spec($spec, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_specification S, tbl_product_categ C WHERE (S.tbl_specificationId = P.tbl_specificationId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = ?) AND (P.tbl_productStatus = ?)  ORDER BY P.tbl_productBrand  ASC ");

 // $stmt = $connectMe->prepare("SELECT         
 //        P.tbl_productId,
 //        P.tbl_productBrand,
 //        P.tbl_productDate,
 //        P.tbl_productQty,
 //        COALESCE(S.total_sales, 0) AS total_sales,
 //        P.tbl_specificationId,
 //        C.tbl_product_categId,
 //        SP.tbl_specificationYear, 
 //        C.tbl_product_categName 
 //        FROM tbl_product P
 //        INNER JOIN tbl_specification SP ON SP.tbl_specificationId = P.tbl_specificationId
 //        INNER JOIN tbl_product_categ C ON SP.tbl_product_categId = C.tbl_product_categId
 //        LEFT JOIN (
 //        SELECT tbl_productId, SUM(tbl_sold_prodQty) AS total_sales
 //        FROM tbl_sold_prod
 //        GROUP BY tbl_productId
 //    ) S ON P.tbl_productId = S.tbl_productId
 //        WHERE (P.tbl_specificationId = ?) AND (P.tbl_productStatus = ?)  ORDER BY total_sales DESC 
 // ");

 $stmt->bindParam(1, $spec);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 
 return $data;
}






//count all  products
function count_all_products($status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE tbl_productStatus = ?  ORDER BY tbl_productId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 return $stmt->rowCount();
}


//count all product Qty for branch
function countAllProductsQtyForCurrentBranch( $productId, $status, $branchId ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( tbl_branch_productAvail )  as qty FROM  tbl_branch_product WHERE (tbl_productId =?  ) AND  (tbl_branch_productStatus  = ?)  AND  (tbl_brandId  = ?)   ORDER BY tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $branchId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}


//count all product Qty
function countAllProductsQty( $productId, $status ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( tbl_branch_productAvail )  as qty FROM  tbl_branch_product WHERE (tbl_productId =?  ) AND  (tbl_branch_productStatus  = ?)   ORDER BY tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}


//count all product Qty AND DATE
function countAllProductsQtyAndDate( $productId, $status, $to ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( BP.tbl_branch_productAvail )  as qty FROM  tbl_branch_product BP, tbl_inventory I  WHERE (I.tbl_inventoryId = BP.tbl_inventoryId) AND  (BP.tbl_productId =?  ) AND  (BP.tbl_branch_productStatus  = ?)     ORDER BY BP.tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  //$stmt->bindParam(3, $to);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}



//count all store product Qty For branch
function countAllStoreProductsQtyForCurrentBranch( $productId, $status, $branchId ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( tbl_inventoryAvailQty )  as qty FROM  tbl_inventory WHERE (tbl_productId =?  ) AND  (tbl_inventoryStatus  = ?)  AND  (tbl_brandId = ?)    ORDER BY tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $branchId);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}




//count all store product Qty
function countAllStoreProductsQty( $productId, $status ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( tbl_inventoryAvailQty )  as qty FROM  tbl_inventory WHERE (tbl_productId =?  ) AND  (tbl_inventoryStatus  = ?)   ORDER BY tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}


//count all store product Qty And date
function countAllStoreProductsQtyAndDate( $productId, $status, $to ){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM( tbl_inventoryAvailQty )  as qty FROM  tbl_inventory WHERE (tbl_productId =?  ) AND  (tbl_inventoryStatus  = ?)     ORDER BY tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  //$stmt->bindParam(3, $to);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}


//count all product for branch
function countAllProductsQtyForBranch($branchId, $productId, $status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT SUM( tbl_branch_productAvail )  as qty FROM  tbl_branch_product WHERE (tbl_productId =?  ) AND  (tbl_branch_productStatus  = ?)  AND  (tbl_brandId  = ?)    ORDER BY tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $branchId);
  //$stmt->bindParam(4, $to);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}



//overal quantity for selling branches 
function overalSellingStoreQty($productId, $status, $to ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT SUM( tbl_branch_productAvail )  as qty FROM  tbl_branch_product WHERE (tbl_productId =?  ) AND  (tbl_branch_productStatus  = ?)     ORDER BY tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  //$stmt->bindParam(3, $to);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}


//count all product for store
function countAllProductsQtyForStore($branchId, $productId, $status ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT SUM( tbl_inventoryAvailQty )  as qty FROM  tbl_inventory WHERE (tbl_productId =?  ) AND  (tbl_inventoryStatus  = ?)  AND  (tbl_brandId  = ?)    ORDER BY tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  $stmt->bindParam(3, $branchId);
  //$stmt->bindParam(4, $to);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);
  
  return $data;

}


//overal quantity all product for store
function overalAllProductsQtyForStore($productId, $status, $to ){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT SUM( tbl_inventoryAvailQty )  as qty FROM  tbl_inventory WHERE (tbl_productId =?  ) AND  (tbl_inventoryStatus  = ?)    ORDER BY tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $productId);
  $stmt->bindParam(2, $status);

  //$stmt->bindParam(3, $to);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}



//find_all_products_for_branch
function find_all_products_for_branch($branch, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE (tbl_brandId = ?) AND (tbl_productStatus = ?)  ORDER BY tbl_productBrand  ASC ");

  $stmt->bindParam(1, $branch);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $data;
}


//find_all_products_for_spec_and_branch
function find_all_products_for_spec_and_branch($spec, $branch, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_branch_product BP WHERE (P.tbl_productId = BP.tbl_productId) AND (P.tbl_specificationId = ?) AND (BP.tbl_brandId = ?) AND (BP.tbl_branch_productStatus = ?)  ORDER BY P.tbl_productBrand  ASC ");

  $stmt->bindParam(1, $spec);
  $stmt->bindParam(2, $branch);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}




//count_all_products_for_branch
function count_all_products_for_branch($branch, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE (tbl_brandId = ?) AND (tbl_productStatus = ?)  ORDER BY tbl_productId  DESC ");

  $stmt->bindParam(1, $branch);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  return $stmt->rowCount();
}


//find  find_product_for_spec 
 function find_product_for_spec($categId)
 {
 global $connectMe;

 $safeCategId = mysql_prep($categId);
 $proStmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE tbl_specificationId = ? ORDER BY tbl_productBrand ASC ");

 $proStmt->bindParam(1, $safeCategId);
 $proStmt->execute();
       
 $proSet = $proStmt->fetchAll(PDO::FETCH_OBJ);
 return $proSet;

}



//find  find_product_for_category 
 function find_products_for_category($categId)
 {
 global $connectMe;

 $safeCategId = mysql_prep($categId);
 $proStmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE tbl_product_categId = ? ORDER BY tbl_productId DESC ");

 $proStmt->bindParam(1, $safeCategId);
 $proStmt->execute();
       
 $proSet = $proStmt->fetchAll(PDO::FETCH_OBJ);
 return $proSet;

}


//find  find_specification_for_category 
function find_specification_for_category($categId){

 global $connectMe;

 $safeCategId = mysql_prep($categId);
 $stmt = $connectMe->prepare("SELECT * FROM tbl_specification WHERE tbl_product_categId = ? ORDER BY tbl_specificationId DESC ");

 $stmt->bindParam(1, $safeCategId);
 $stmt->execute();
       
 $res = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $res;

}




//all expired products
function find_all_expired_products()
 {
 global $connectMe;
 $proSetStmt = $connectMe->prepare("SELECT * FROM tbl_expired_prod WHERE tbl_expired_prodStatus = ?  ORDER BY tbl_expired_prodDate  DESC ");
 $status = 1;

 $proSetStmt->bindParam(1, $status);
 $proSetStmt->execute();

 $pro = $proSetStmt->fetchAll(PDO::FETCH_OBJ);
 return $pro;
}

//find expired product  by id
function find_expired_product_by_id($exproId)
{
global $connectMe;
 $safeProId = mysql_prep($exproId);

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_expired_prod  WHERE tbl_expired_prodId = ?  LIMIT 1");
 $proStmt->bindParam(1, $safeProId);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}



function find_img_for_product($proId)
 {
 global $connectMe;
 $proId = mysql_prep($proId);

 $proImgStmt = $connectMe->prepare("SELECT * FROM tbl_prod_img WHERE tbl_prod_imgStatus   = ?  AND   tbl_productId = ?  ORDER BY tbl_prod_imgFile  ASC ");
 $status = 1;

 $proImgStmt->bindParam(1, $status);
 $proImgStmt->bindParam(2, $proId);
 $proImgStmt->execute();

 $proImg = $proImgStmt->fetchAll(PDO::FETCH_OBJ);
 return $proImg;
}


//all sold new products
function find_all_new_sold_products()
 {
 global $connectMe;
 $proStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod WHERE tbl_sold_prodStatus   = ?  AND   tbl_sold_prodNew = ?  ORDER BY tbl_sold_prodDate  DESC ");
 $status = 1;
 $new = 1;
 $proStmt->bindParam(1, $status);
 $proStmt->bindParam(2, $new);
 $proStmt->execute();

 $pro = $proStmt->fetchAll(PDO::FETCH_OBJ);
 return $pro;
}


//all sold new products
function add_new_sold_products($prodId, $custId, $typeId, $amount, $empId, $qty, $date, $token, $telephone, $inventoryId, $transferId, $tid, $warranty, $branchId ){
 global $connectMe;

 $stmt = $connectMe->prepare("INSERT INTO tbl_sold_prod ( tbl_productId, tbl_customerId, tbl_sell_typeId, tbl_sold_prodAmount, tbl_employeeId, tbl_sold_prodQty, tbl_sold_prodDate, tbl_sold_prodToken, tbl_sold_prodStatus, tbl_sold_prodTel, tbl_inventoryId, tbl_branch_productId, tbl_sold_prodTid, tbl_sold_prodWarranty, tbl_brandId ) VALUES(?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?) ");

 $status = 1;
 
 $stmt->bindParam(1, $prodId);
 $stmt->bindParam(2, $custId);

 $stmt->bindParam(3, $typeId);
 $stmt->bindParam(4, $amount);

 $stmt->bindParam(5, $empId);
 $stmt->bindParam(6, $qty);

 $stmt->bindParam(7, $date);
 $stmt->bindParam(8, $token);

 $stmt->bindParam(9, $status);
 $stmt->bindParam(10, $telephone);

 $stmt->bindParam(11, $inventoryId);
 $stmt->bindParam(12, $transferId);

 $stmt->bindParam(13, $tid);
 $stmt->bindParam(14, $warranty);

 $stmt->bindParam(15, $branchId);
 if($stmt->execute()){

  $lastId = $connectMe->lastInsertId();

  return $lastId;


 }else {

  return false;

 }

 
}



//add product to branch 
function add_product_to_branch($prodId, $branchId, $token, $date, $qty, $status ){

 global $connectMe;
 $stmt = $connectMe->prepare("INSERT INTO tbl_branch_product ( tbl_branch_productDate, tbl_branch_productQty, tbl_branch_productToken, tbl_brandId, tbl_productId, tbl_branch_productStatus) VALUES (?,?,?, ?,?,?) ");
 
 $stmt->bindParam(1, $date);
 $stmt->bindParam(2, $qty);

 $stmt->bindParam(3, $token);
 $stmt->bindParam(4, $branchId);

 $stmt->bindParam(5, $prodId);
 $stmt->bindParam(6, $status);

 if($stmt->execute()){

    return true;

 }else {

    return false;

 }

 
}


//all sold products for product
function find_all_sold_pro_for_product($productId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId = P.tbl_productId) AND  (S.tbl_sold_prodStatus = ?)  AND (S.tbl_productId  = ?)  ORDER BY S.tbl_sold_prodId  DESC LIMIT 5 ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $productId);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//all sold products for sell type
function find_all_sold_pro_for_sell_type($sellId, $status){

 global $connectMe;
 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod WHERE tbl_sold_prodStatus   = ?  AND  tbl_sell_typeId  = ?  ORDER BY tbl_sold_prodId  DESC ");
      
  $soldProStmt->bindParam(1, $status);
  $soldProStmt->bindParam(2, $sellId);
  $soldProStmt->execute();

  $pro = $soldProStmt->fetchAll(PDO::FETCH_OBJ);
  return $pro;
}


//all sold products for phone and business
function find_all_sold_prod_for_phone_and_bus($businessId, $phone, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_customer C WHERE (S.tbl_sold_prodTel   = C.tbl_customerPhone) AND  (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_sold_prodTel  = ?)  AND  (C.tbl_businessId  = ?)  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $phone);

  $stmt->bindParam(3, $businessId);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//all sold products for sell type and specification
function find_all_sold_pro_for_sell_type_and_spec($spec, $sellId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P WHERE (S.tbl_productId   = P.tbl_productId) AND  (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_sell_typeId  = ?)  AND  (P.tbl_specificationId  = ?)  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $sellId);

  $stmt->bindParam(3, $spec);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//find_all_sold_pro_for_sell_type_and_branch
function find_all_sold_pro_for_sell_type_and_branch($sellId, $branch,  $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P WHERE (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_sell_typeId  = ?)  AND  (S.tbl_productId  = P.tbl_productId) AND (P.tbl_brandId  = ?)  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $sellId);

  $stmt->bindParam(3, $branch);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//find_all_sold_pro_for_sell_type_and_branch_and_spec
function find_all_sold_pro_for_sell_type_and_branch_and_spec($spec, $sellId, $branch,  $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_branch_product BP, tbl_inventory I, tbl_sell_type ST, tbl_employee E, tbl_specification SP, tbl_product_categ C  WHERE (SP.tbl_product_categId  = C.tbl_product_categId) AND (SP.tbl_specificationId  = P.tbl_specificationId) AND (S.tbl_employeeId  = E.tbl_employeeId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (BP.tbl_inventoryId  = I.tbl_inventoryId) AND (S.tbl_productId  = P.tbl_productId) AND  (S.tbl_branch_productId  = BP.tbl_branch_productId) AND (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_sell_typeId  = ?)  AND (BP.tbl_brandId  = ?)  AND (P.tbl_specificationId  = ?)  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $sellId);

  $stmt->bindParam(3, $branch);
  $stmt->bindParam(4, $spec);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}


//search_all_sold_pro_for_business
function search_all_sold_pro_for_business($search, $businessId,  $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_product_categ C, tbl_specification SP, tbl_branch_product BP  WHERE (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_productId  = P.tbl_productId)  AND  (P.tbl_product_categId  = C.tbl_product_categId) AND  (P.tbl_specificationId  = SP.tbl_specificationId) AND (S.tbl_branch_productId  = BP.tbl_branch_productId) AND (C.tbl_businessId  = ?) AND (P.tbl_productBrand LIKE '%".$search."%'  OR  C.tbl_product_categName LIKE '%".$search."%'  OR SP.tbl_specificationYear LIKE '%".$search."%'  )  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $pro = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $pro;
}




//search_all_sold_pro_for_branch
function search_all_sold_pro_for_branch($search, $branch,  $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_product_categ C, tbl_specification SP, tbl_branch_product BP  WHERE (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_productId  = P.tbl_productId)  AND  (P.tbl_product_categId  = C.tbl_product_categId) AND  (P.tbl_specificationId  = SP.tbl_specificationId) AND (S.tbl_branch_productId  = BP.tbl_branch_productId) AND (BP.tbl_brandId  = ?) AND (P.tbl_productBrand LIKE '%".$search."%'  OR  C.tbl_product_categName LIKE '%".$search."%'  OR SP.tbl_specificationYear LIKE '%".$search."%'  )  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $branch);

  $stmt->execute();
  $pro = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $pro;
}


//search_all_sold_pro
function search_all_sold_pro($search,  $status){

 global $connectMe;
 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_product_categ C, tbl_specification SP  WHERE (S.tbl_sold_prodStatus   = ?)  AND  (S.tbl_productId  = P.tbl_productId)  AND  (P.tbl_product_categId  = C.tbl_product_categId) AND  (P.tbl_specificationId  = SP.tbl_specificationId)  AND (P.tbl_productBrand LIKE '%".$search."%'  OR  C.tbl_product_categName LIKE '%".$search."%'  OR SP.tbl_specificationYear LIKE '%".$search."%'  )  ORDER BY S.tbl_sold_prodId  DESC ");
      
  $soldProStmt->bindParam(1, $status);
  $soldProStmt->execute();

  $pro = $soldProStmt->fetchAll(PDO::FETCH_OBJ);
  return $pro;
}

//find_all_return_for_action_and_business
function find_all_return_for_action_and_business($action, $businessId,  $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_return_prod R, tbl_brand BR  WHERE (R.tbl_return_prodStatus   = ?)  AND  (R.tbl_actionId  = ?)  AND (BR.tbl_businessId  = ?)  AND (R.tbl_brandId  = BR.tbl_brandId)   ORDER BY R.tbl_return_prodId  DESC ");
      
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $action);

  $stmt->bindParam(3, $businessId);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;
}


//find_all_return_for_action
function find_all_return_for_action($action,  $status){

 global $connectMe;
 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_return_prod   WHERE (tbl_return_prodStatus   = ?)  AND  (tbl_actionId  = ?)   ORDER BY tbl_return_prodId  DESC ");
      
  $soldProStmt->bindParam(1, $status);
  $soldProStmt->bindParam(2, $action);
  
  $soldProStmt->execute();

  $pro = $soldProStmt->fetchAll(PDO::FETCH_OBJ);
  return $pro;
}


//find sold product  by id
function find_sold_product_by_id($soldId)
{
global $connectMe;
 $safeSoldId = mysql_prep($soldId);

 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_branch_product BP, tbl_inventory I, tbl_sell_type ST, tbl_employee E, tbl_specification SP, tbl_product_categ C, tbl_brand B  WHERE (SP.tbl_product_categId  = C.tbl_product_categId) AND (SP.tbl_specificationId  = P.tbl_specificationId) AND (S.tbl_employeeId  = E.tbl_employeeId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (BP.tbl_inventoryId  = I.tbl_inventoryId) AND (S.tbl_productId  = P.tbl_productId) AND  (S.tbl_branch_productId  = BP.tbl_branch_productId) AND  (S.tbl_brandId = B.tbl_brandId ) AND tbl_sold_prodId = ?  LIMIT 1");
 $soldProStmt->bindParam(1, $safeSoldId);
 $soldProStmt->execute();
        
  if ($sold = $soldProStmt->fetch(PDO::FETCH_OBJ)) {
    return $sold;
  }

}

//find action   by id
function find_action_by_id($actionId)
{
global $connectMe;
 $safeSoldId = mysql_prep($actionId);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_action  WHERE tbl_actionId = ?  LIMIT 1");
 $stmt->bindParam(1, $safeSoldId);
 $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//find sold product  by token
function find_sold_product_by_token($soldId){

  global $connectMe;
  $safeSoldId = mysql_prep($soldId);

  $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_branch_product BP, tbl_inventory I, tbl_sell_type ST, tbl_employee E, tbl_specification SP, tbl_product_categ C  WHERE (SP.tbl_product_categId  = C.tbl_product_categId) AND (SP.tbl_specificationId  = P.tbl_specificationId) AND (S.tbl_employeeId  = E.tbl_employeeId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (BP.tbl_inventoryId  = I.tbl_inventoryId) AND (S.tbl_productId  = P.tbl_productId) AND  (S.tbl_branch_productId  = BP.tbl_branch_productId) AND S.tbl_sold_prodToken = ?  LIMIT 1");
  
  $soldProStmt->bindParam(1, $safeSoldId);
  $soldProStmt->execute();
        
  if ($sold = $soldProStmt->fetch(PDO::FETCH_OBJ)) {
    return $sold;
  }

}



//find sold product  by tid
function find_sold_product_by_tid($soldTId)
{
global $connectMe;
 $soldTId = mysql_prep($soldTId);

 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod  WHERE tbl_sold_prodTid = ?  LIMIT 1");
 $soldProStmt->bindParam(1, $soldTId);
 $soldProStmt->execute();
        
  if ($sold = $soldProStmt->fetch(PDO::FETCH_OBJ)) {
    return $sold;
  }

}



//find sold product  by token
function find__product_by_token($soldId)
{
global $connectMe;
 $safeSoldId = mysql_prep($soldId);

 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod  WHERE tbl_sold_prodToken = ?  LIMIT 1");
 $soldProStmt->bindParam(1, $safeSoldId);
 $soldProStmt->execute();
        
  if ($sold = $soldProStmt->fetch(PDO::FETCH_OBJ)) {
    return $sold;
  }

}





//all new products
function find_all_returned_products()
 {
 global $connectMe;
 $returnStmt = $connectMe->prepare("SELECT * FROM tbl_return_prod WHERE tbl_return_prodStatus = ?   ORDER BY tbl_return_prodDate  DESC ");
 $status = 1;

 $returnStmt->bindParam(1, $status);
 $returnStmt->execute();

 $returnPro = $returnStmt->fetchAll(PDO::FETCH_OBJ);
 return $returnPro;
}


//find product   by id
function find_product_by_id($proId)
{
global $connectMe;
 $safeProId = mysql_prep($proId);

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_product  WHERE tbl_productId = ?  LIMIT 1");
 $proStmt->bindParam(1, $safeProId);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}


//find_product_by_prod_id
function find_product_by_prod_id($proId)
{
global $connectMe;
 $safeProId = mysql_prep($proId);

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_product P,  tbl_specification S, tbl_product_categ C  WHERE (P.tbl_specificationId  = S.tbl_specificationId ) AND (S.tbl_product_categId   = C.tbl_product_categId  ) AND (P.tbl_productId = ?)  LIMIT 1");
 $proStmt->bindParam(1, $safeProId);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}




//find product for branch   by id
function check_product_at_branch($proId, $branch){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product WHERE (tbl_productId = ?) AND (tbl_brandId = ?)  LIMIT 1");
  $stmt->bindParam(1, $proId);
  $stmt->bindParam(2, $branch);

  $stmt->execute();
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}





//find product   by token
function find_product_by_token($proId){

  global $connectMe;
  $safeProId = mysql_prep($proId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_specification S, tbl_product_categ C WHERE(P.tbl_specificationId = S.tbl_specificationId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_productToken = ?)  LIMIT 1");

  $stmt->bindParam(1, $safeProId);
  $stmt->execute();
        
  if ($pro = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}

  //last transferred for product 
  function find_last_transfer_for_product($proId, $status ){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM  tbl_branch_product WHERE (tbl_productId = ?) AND (tbl_branch_productStatus = ?)   ORDER BY tbl_branch_productId  ASC LIMIT 1 ");
 
    $stmt->bindParam(1, $proId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $res = $stmt->fetch(PDO::FETCH_OBJ);

    return $res;
  }

//find return by token
function find_return_by_token($proId){
global $connectMe;
 $safeProId = mysql_prep($proId);

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_return_prod  WHERE tbl_return_prodToken = ?  LIMIT 1");
 $proStmt->bindParam(1, $safeProId);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}


//find return by id
function find_return_by_id($proId){
global $connectMe;
 $safeProId = mysql_prep($proId);

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_return_prod  WHERE tbl_return_prodId = ?  LIMIT 1");
 $proStmt->bindParam(1, $safeProId);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}


//find transaction  by code
function find_transaction_by_code($transCode){

  global $connectMe;
  $transCode = mysql_prep($transCode);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory  WHERE tbl_inventoryCode = ?  LIMIT 1");
  $stmt->bindParam(1, $transCode);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}



//find transaction  by id
function find_transaction_by_id($transId){

  global $connectMe;
  $safeProId = mysql_prep($transId);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory  WHERE tbl_inventoryId = ?  LIMIT 1");
  $stmt->bindParam(1, $safeProId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//find transaction  by token
function find_transaction_by_token($transId){

  global $connectMe;
  $safeProId = mysql_prep($transId);

  $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_brand B, tbl_color CO, tbl_supplier SU, tbl_employee E   WHERE ((E.tbl_employeeId = I.tbl_employeeId) AND (SU.tbl_supplierId = I.tbl_supplierId) AND (CO.tbl_colorId = I.tbl_colorId) AND (B.tbl_brandId = I.tbl_brandId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (I.tbl_productId = P.tbl_productId))  AND (I.tbl_inventoryToken = ?)  LIMIT 1");

  $stmt->bindParam(1, $safeProId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//find product category  by id
function update_product_by_id($soldQty, $proId)
{
global $connectMe;
 $safeProId = mysql_prep($proId);
 $safeSoldQty = mysql_prep($soldQty);

 $proStmt = $connectMe->prepare("UPDATE tbl_product  SET tbl_productQty = ? WHERE  tbl_productId = ? ");
 $proStmt->bindParam(1, $safeSoldQty);
 $proStmt->bindParam(2, $safeProId);


  if( $proStmt->execute()){

    return true;
 
  }else{

    return false;
  }
}

//find stock by id
function update_stock_by_id($stockId, $qty)
{
global $connectMe;
 $stockId = mysql_prep($stockId);
 $safeSoldQty = mysql_prep($qty);

 $proStmt = $connectMe->prepare("UPDATE tbl_inventory  SET tbl_inventoryAvailQty = ? WHERE  tbl_inventoryId = ? ");
 $proStmt->bindParam(1, $safeSoldQty);
 $proStmt->bindParam(2, $stockId);


  if( $proStmt->execute()){

    return true;
 
  }else{

    return false;
  }
}


//update branch product  
function update_branch_product($soldQty, $proId, $branch){

  global $connectMe;
  $safeProId = mysql_prep($proId);
  $safeSoldQty = mysql_prep($soldQty);

  $branch = mysql_prep($branch);

  $stmt = $connectMe->prepare("UPDATE tbl_branch_product  SET tbl_branch_productQty = ? WHERE  (tbl_productId = ?) AND ( tbl_brandId = ? ) ");
  $stmt->bindParam(1, $safeSoldQty);
  $stmt->bindParam(2, $safeProId);

  $stmt->bindParam(3, $branch);
  if( $stmt->execute()){

    return true;
 
  }else{

    return false;
  }

}


//find product category  by id
function update_inventory_by_id($soldQty, $inventoryId){

  global $connectMe;
  $inventoryId = mysql_prep($inventoryId);
  $safeSoldQty = mysql_prep($soldQty);

  $stmt = $connectMe->prepare("UPDATE tbl_inventory SET tbl_inventoryAvailQty = ? WHERE  tbl_inventoryId = ? ");
  $stmt->bindParam(1, $safeSoldQty);

  $stmt->bindParam(2, $inventoryId);

  if( $stmt->execute()){

    return true;
 
  }else{

    return false;
  }

}



//remove sale
function remove_sale_by_id($soldId,  $status){

  global $connectMe;

  $stmt = $connectMe->prepare("UPDATE tbl_sold_prod SET tbl_sold_prodStatus = ? WHERE tbl_sold_prodId = ? ");
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $soldId);

  if( $stmt->execute()){

    return true;
 
  }else{

    return false;
  }
}



//update sale
function update_sale_by_id($soldId, $qty, $status){

  global $connectMe;

  $stmt = $connectMe->prepare("UPDATE tbl_sold_prod SET tbl_sold_prodStatus = ?, tbl_sold_prodQty =? WHERE tbl_sold_prodId = ? ");
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $qty);

  $stmt->bindParam(3, $soldId);

  if( $stmt->execute()){

    return true;
 
  }else{

    return false;
  }
}



//find product category  by id
function find_prod_categ_by_id($categId)
{
global $connectMe;
 $safeCategId = mysql_prep($categId);

 $ctgStmt = $connectMe->prepare("SELECT * FROM tbl_product_categ  WHERE tbl_product_categId = ?  LIMIT 1");
 $ctgStmt->bindParam(1, $safeCategId);
 $ctgStmt->execute();
        
  if ($categ = $ctgStmt->fetch(PDO::FETCH_OBJ)) {
    return $categ;
  }

}


//find specification  by id
function find_specification_by_id($specification){

  global $connectMe;
  $safeCategId = mysql_prep($specification);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_specification  WHERE tbl_specificationId = ?  LIMIT 1");
  $stmt->bindParam(1, $safeCategId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//all  specification
function find_all_specification()
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_specification WHERE tbl_specificationStatus  = ? ORDER BY tbl_specificationId  DESC ");
 $status = 1;

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $ctg = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $ctg;
}

//all  specification for business
function find_all_specification_for_business($businessId)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_specification S, tbl_product_categ C  WHERE (C.tbl_product_categId  = S.tbl_product_categId) AND (S.tbl_specificationStatus  = ?) AND (C.tbl_businessId  = ?)  ORDER BY S.tbl_specificationId  DESC ");

 $status = 1;
 $stmt->bindParam(1, $status);

 $stmt->bindParam(2, $businessId);
 $stmt->execute();

 $res = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $res;
}

//search products for business
function search_product_for_business($search, $status, $businessId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_specification S, tbl_product_categ C WHERE  (P.tbl_specificationId = S.tbl_specificationId)  AND (P.tbl_product_categId = C.tbl_product_categId) AND ( P.tbl_productStatus = ? )    AND ( S.tbl_specificationYear LIKE  '%".$search."%'   OR  P.tbl_productBrand LIKE  '%".$search."%' OR C.tbl_product_categName LIKE  '%".$search."%'   ) AND  (C.tbl_businessId = ?)   ORDER BY P.tbl_productBrand ASC ");
  
  //$stmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_specification S, tbl_product_categ C WHERE  (P.tbl_specificationId = S.tbl_specificationId)  AND (S.tbl_product_categId = C.tbl_product_categId) AND ( P.tbl_productStatus = ? )    AND (  P.tbl_productBrand LIKE  '%".$search."%'  ) AND  (C.tbl_businessId = ?)   ORDER BY P.tbl_productBrand ASC  ");
  


  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;

}


//search products
function search_product($search, $status)
 {
 global $connectMe;
 $prodStmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_specification S, tbl_product_categ C WHERE  (P.tbl_specificationId = S.tbl_specificationId)  AND (P.tbl_product_categId = C.tbl_product_categId) AND ( P.tbl_productStatus = ? )    AND ( S.tbl_specificationYear LIKE  '%".$search."%'   OR  P.tbl_productBrand LIKE  '%".$search."%' OR C.tbl_product_categName LIKE  '%".$search."%'   )    ORDER BY P.tbl_productBrand ASC ");

 $prodStmt->bindParam(1, $status);
 $prodStmt->execute();

 $pro = $prodStmt->fetchAll(PDO::FETCH_OBJ);
 return $pro;

}



//search  products for branch
function search_product_for_branch($search, $branch, $status)
 {
 global $connectMe;
 $prodStmt = $connectMe->prepare("SELECT * FROM tbl_product P, tbl_specification S, tbl_product_categ C WHERE  (P.tbl_specificationId = S.tbl_specificationId) AND (P.tbl_product_categId = C.tbl_product_categId)  AND ( P.tbl_productStatus = ? )    AND ( S.tbl_specificationYear LIKE  '%".$search."%'   OR  P.tbl_productBrand LIKE  '%".$search."%' OR C.tbl_product_categName LIKE  '%".$search."%'   )    ORDER BY P.tbl_productBrand ASC ");
 
 $prodStmt->bindParam(1, $status);
 //$prodStmt->bindParam(2, $branch);

 $prodStmt->execute();
 $pro = $prodStmt->fetchAll(PDO::FETCH_OBJ);

 return $pro;

}



//search bought products
function search_bought_product($prod)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_supplier SP WHERE (P.tbl_productBrand LIKE  '%".$prod."%' OR S.tbl_specificationYear LIKE  '%".$prod."%'  OR C.tbl_product_categName LIKE  '%".$prod."%' OR I.tbl_inventoryDate LIKE  '%".$prod."%'  OR SP.tbl_supplierFname LIKE  '%".$prod."%' ) AND (P.tbl_specificationId = S.tbl_specificationId) AND (P.tbl_product_categId = C.tbl_product_categId)  AND (I.tbl_productId = P.tbl_productId) AND (I.tbl_supplierId = SP.tbl_supplierId)  AND (I.tbl_inventoryStatus = 1)   ORDER BY I.tbl_inventoryId DESC ");

 $stmt->execute();
 $pro = $stmt->fetchAll(PDO::FETCH_OBJ);
 
 return $pro;

}


//search bought products for business
function search_bought_product_for_business($prod, $businessId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_supplier SP WHERE (P.tbl_productBrand LIKE  '%".$prod."%' OR S.tbl_specificationYear LIKE  '%".$prod."%'  OR C.tbl_product_categName LIKE  '%".$prod."%' OR I.tbl_inventoryDate LIKE  '%".$prod."%'  OR SP.tbl_supplierFname LIKE  '%".$prod."%' ) AND (P.tbl_specificationId = S.tbl_specificationId) AND (P.tbl_product_categId = C.tbl_product_categId)  AND (I.tbl_productId = P.tbl_productId) AND (I.tbl_supplierId = SP.tbl_supplierId)  AND (I.tbl_inventoryStatus = 1)   AND (C.tbl_businessId = ?)   ORDER BY I.tbl_inventoryId DESC ");

  $stmt->bindParam(1, $businessId);
  $stmt->execute();

  $pro = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $pro;

}




function find_all_product_report($from, $to, $status)
 {
 global $connectMe;
 //$stmt = $connectMe->prepare("SELECT * FROM tbl_product  WHERE  (tbl_productDate BETWEEN ? AND  ?  ) AND ( tbl_productStatus =?)   ORDER BY tbl_productId DESC ");

 $stmt = $connectMe->prepare("SELECT * FROM tbl_product  WHERE  ( tbl_productStatus =?)   ORDER BY tbl_productId DESC ");


 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


function find_all_product_report_for_branch($from, $to, $branch, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_product  WHERE  (tbl_productDate BETWEEN ? AND  ?  ) AND ( tbl_brandId =?)  AND ( tbl_productStatus =?)   ORDER BY tbl_productId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $branch);
 $stmt->bindParam(4, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


function search_sold_product_by_branch($search, $branchId, $status){
  
 global $connectMe;
$stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_branch_product BP, 
  tbl_sell_type ST, tbl_employee E, tbl_product P, tbl_specification SP, tbl_product_categ C, tbl_brand B  WHERE (S.tbl_branch_productId = BP.tbl_branch_productId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (S.tbl_employeeId  = E.tbl_employeeId)  AND (BP.tbl_productId  = P.tbl_productId) 

   AND (P.tbl_specificationId  = SP.tbl_specificationId) AND  (SP.tbl_product_categId  = C.tbl_product_categId)
   AND  (S.tbl_brandId = B.tbl_brandId)

   AND ( SP.tbl_specificationYear LIKE  '%".$search."%'   OR  P.tbl_productBrand LIKE  '%".$search."%' OR C.tbl_product_categName LIKE  '%".$search."%' OR S.tbl_customerId LIKE  '%".$search."%'   ) AND (S.tbl_sold_prodStatus =?) AND (S.tbl_brandId =?)  AND S.tbl_sold_prodQty > 0 ORDER BY S.tbl_sold_prodId DESC LIMIT 30");


 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $branchId);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


function search_sold_product_by_business($search, $businessId, $status){
  
 global $connectMe;
$stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_branch_product BP, 
  tbl_sell_type ST, tbl_employee E, tbl_product P, tbl_specification SP, tbl_product_categ C, tbl_brand B  WHERE (S.tbl_branch_productId = BP.tbl_branch_productId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (S.tbl_employeeId  = E.tbl_employeeId)  AND (BP.tbl_productId  = P.tbl_productId) 

   AND (P.tbl_specificationId  = SP.tbl_specificationId) AND  (SP.tbl_product_categId  = C.tbl_product_categId)
   AND  (S.tbl_brandId = B.tbl_brandId)

   AND ( SP.tbl_specificationYear LIKE  '%".$search."%'   OR  P.tbl_productBrand LIKE  '%".$search."%' OR C.tbl_product_categName LIKE  '%".$search."%' OR S.tbl_customerId LIKE  '%".$search."%'  ) AND (S.tbl_sold_prodStatus =?) AND (B.tbl_businessId =?)  AND S.tbl_sold_prodQty > 0 ORDER BY S.tbl_sold_prodId DESC LIMIT 30 ");


 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $businessId);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}

function find_all_sold_product_report_for_branch_and_type($from, $to, $status, $branchId, $typeId){
  
 global $connectMe;
$stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_branch_product BP, 
  tbl_sell_type ST, tbl_employee E, tbl_product P, tbl_specification SP, tbl_product_categ C, tbl_brand B  WHERE (S.tbl_branch_productId = BP.tbl_branch_productId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (S.tbl_employeeId  = E.tbl_employeeId)  AND (BP.tbl_productId  = P.tbl_productId) 

   AND (P.tbl_specificationId  = SP.tbl_specificationId) AND  (SP.tbl_product_categId  = C.tbl_product_categId)
   AND  (S.tbl_brandId = B.tbl_brandId)

   AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?) AND (S.tbl_brandId =?)  AND S.tbl_sold_prodQty > 0 ORDER BY S.tbl_sold_prodId DESC");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branchId);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


function calculate_sold_product_report_for_branch_and_type($from, $to, $status, $branchId, $typeId){
  
 global $connectMe;
$stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty * S.tbl_sold_prodAmount) as amount FROM tbl_sold_prod S, tbl_branch_product BP, tbl_sell_type ST WHERE (S.tbl_branch_productId = BP.tbl_branch_productId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId)  

   AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?) AND (S.tbl_brandId =?)  AND S.tbl_sold_prodQty > 0 ORDER BY S.tbl_sold_prodId DESC");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branchId);


 $stmt->execute();

  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }
}


function calculate_sold_product_report_for_branch_by_type($from, $to, $status, $branchId, $typeId){
  
 global $connectMe;
$stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty * S.tbl_sold_prodAmount) as amount FROM tbl_sold_prod S, tbl_sell_type ST WHERE (S.tbl_sell_typeId  = ST.tbl_sell_typeId) 

   AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?) AND (S.tbl_brandId =?)  AND (S.tbl_sell_typeId =?)  AND S.tbl_sold_prodQty > 0 ORDER BY S.tbl_sold_prodId DESC");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branchId);

 $stmt->bindParam(5, $typeId);


 $stmt->execute();

  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }
}




function calculate_profit_for_branch_and_type($from, $to, $status, $branchId, $typeId){
  
 global $connectMe;
$stmt = $connectMe->prepare("SELECT SUM( (S.tbl_sold_prodQty * S.tbl_sold_prodAmount)  - (S.tbl_sold_prodQty * I.tbl_inventoryCost) ) as amount FROM tbl_sold_prod S, tbl_branch_product BP, tbl_sell_type ST, tbl_inventory I  WHERE (S.tbl_branch_productId = BP.tbl_branch_productId) AND (BP.tbl_inventoryId  = I.tbl_inventoryId ) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) 

   AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?) AND (S.tbl_brandId =?)  AND S.tbl_sold_prodQty > 0 ORDER BY S.tbl_sold_prodId DESC");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branchId);

 $stmt->execute();

  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }
}


function find_all_sold_product_report_for_branch($from, $to, $status, $branchId)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_branch_product BP WHERE (S.tbl_branch_productId = BP.tbl_branch_productId)  AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?) AND (BP.tbl_brandId =?)  ORDER BY S.tbl_sold_prodId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branchId);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


function find_all_sold_product_report($from, $to, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_branch_product BP, tbl_inventory I, tbl_sell_type ST, tbl_employee E, tbl_specification SP, tbl_product_categ C  WHERE (SP.tbl_product_categId  = C.tbl_product_categId) AND (SP.tbl_specificationId  = P.tbl_specificationId) AND (S.tbl_employeeId  = E.tbl_employeeId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (BP.tbl_inventoryId  = I.tbl_inventoryId) AND (S.tbl_productId  = P.tbl_productId) AND  (S.tbl_branch_productId  = BP.tbl_branch_productId) AND (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?)  ORDER BY S.tbl_sold_prodId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


function find_all_sold_product_report_and_type($from, $to, $status, $type){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod S, tbl_product P, tbl_branch_product BP, tbl_inventory I, tbl_sell_type ST, tbl_employee E, tbl_specification SP, tbl_product_categ C  WHERE (SP.tbl_product_categId  = C.tbl_product_categId) AND (SP.tbl_specificationId  = P.tbl_specificationId) AND (S.tbl_employeeId  = E.tbl_employeeId) AND (S.tbl_sell_typeId  = ST.tbl_sell_typeId) AND (BP.tbl_inventoryId  = I.tbl_inventoryId) AND (S.tbl_productId  = P.tbl_productId) AND  (S.tbl_branch_productId  = BP.tbl_branch_productId) AND (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus =?)  AND (S.tbl_sell_typeId =?)  ORDER BY S.tbl_sold_prodId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $type);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 
 return $data;
}



function find_all_bought_product_report($from, $to, $status, $branchId){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory WHERE  (tbl_inventoryDate BETWEEN ? AND  ?  ) AND ( tbl_inventoryStatus =?)  AND ( tbl_brandId =?)   ORDER BY tbl_inventoryId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branchId);
 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


function find_all_bought_product_report_for_branch($from, $to, $branch, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory WHERE  (tbl_inventoryDate BETWEEN ? AND  ?  ) AND ( tbl_brandId =?)  AND ( tbl_inventoryStatus =?)   ORDER BY tbl_inventoryId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $branch);
 $stmt->bindParam(4, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}


function find_all_transfer_product_report_for_branch($from, $to, $branch, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_branch_product WHERE  (tbl_branch_productDate BETWEEN ? AND  ?  ) AND ( tbl_brandId =?)  AND ( tbl_branch_productStatus =?)   ORDER BY tbl_branch_productId DESC ");


 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $branch);
 $stmt->bindParam(4, $status);

 $stmt->execute();

 $data = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $data;
}




//all recently added products
function find_all_products_for_report($empId, $to, $from)
 {
 global $connectMe;
 $trnsStmt = $connectMe->prepare("SELECT * FROM tbl_inventory WHERE tbl_employeeId = ?  AND tbl_inventoryDate BETWEEN ? AND   ?   ORDER BY tbl_inventoryDate  DESC  ");
 $status = 1;
 
 $trnsStmt->bindParam(1, $empId);
 $trnsStmt->bindParam(2, $to);

 $trnsStmt->bindParam(3, $from);
 $trnsStmt->execute();

 $trans = $trnsStmt->fetchAll(PDO::FETCH_OBJ);
 return $trans;
}



//all  products categories for business
function find_all_products_categ_for_business($businessId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_product_categ WHERE (tbl_product_categStatus   = ?) AND (tbl_businessId   = ?) ORDER BY tbl_product_categName  ASC ");
  $status = 1;

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;

}



//all  products categories
function find_all_products_categ(){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_product_categ WHERE tbl_product_categStatus   = ? ORDER BY tbl_product_categName  ASC ");
  $status = 1;

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $res = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $res;

}



//find ALL sell type  
function find_all_sell_type()
{
global $connectMe;
 $sellTypeStmt = $connectMe->prepare("SELECT * FROM tbl_sell_type  WHERE tbl_sell_typeStatus = ?  ORDER BY tbl_sell_typeName ASC");
 $status = 1;
 $sellTypeStmt->bindParam(1, $status);
 $sellTypeStmt->execute();

 $type = $sellTypeStmt->fetchAll(PDO::FETCH_OBJ);
 return $type;

}


// all actions  
function all_actions($status){
    
global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_action  WHERE tbl_actionStatus = ?  ORDER BY tbl_actionId DESC");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $res = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $res;

}



//find all sell type   by id
function find_sell_type_by_id($sTypeId)
{
global $connectMe;
 $safeTypeId = mysql_prep($sTypeId);

 $sellTypeStmt = $connectMe->prepare("SELECT * FROM tbl_sell_type  WHERE tbl_sell_typeId = ?  LIMIT 1");
 $sellTypeStmt->bindParam(1, $safeTypeId);
 $sellTypeStmt->execute();
        
  if ($sellType = $sellTypeStmt->fetch(PDO::FETCH_OBJ)) {
    return $sellType;
  }

}


//find all sell type   by name
function find_sell_type_by_name($sTypeName)
{
global $connectMe;
 $sTypeName = mysql_prep($sTypeName);

 $stmt = $connectMe->prepare("SELECT * FROM tbl_sell_type WHERE tbl_sell_typeName = ?  LIMIT 1");
 $stmt->bindParam(1, $sTypeName);
 $stmt->execute();
        
  if ($res = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $res;
  }

}


/*  ALL TRANSACTIONS  */

//all BOUGHT products
function find_all_bought_products($status)
 {
 global $connectMe;
 $proStmt = $connectMe->prepare("SELECT * FROM  tbl_inventory WHERE tbl_inventoryStatus = ? ORDER BY tbl_inventoryId  DESC ");
 
 $proStmt->bindParam(1, $status);
 $proStmt->execute();

 $pro = $proStmt->fetchAll(PDO::FETCH_OBJ);
 return $pro;
}


//all BOUGHT TRANSACTION FOR PRODUCT
function find_all_bought_transaction_for_products($proId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_brand B, tbl_color CO   WHERE ((CO.tbl_colorId = I.tbl_colorId) AND (B.tbl_brandId = I.tbl_brandId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (I.tbl_productId = P.tbl_productId)) AND ((I.tbl_productId = ?) AND (I.tbl_inventoryStatus = ?) AND (I.tbl_inventoryAvailQty > 0) )  ORDER BY I.tbl_inventoryId  DESC ");
 
  $stmt->bindParam(1, $proId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;
}



//count all BOUGHT products
function count_all_bought_products($status)
 {
 global $connectMe;
 $proStmt = $connectMe->prepare("SELECT * FROM  tbl_inventory WHERE tbl_inventoryStatus = ? ORDER BY tbl_inventoryId  DESC ");
 
 $proStmt->bindParam(1, $status);
 $proStmt->execute();

 return $proStmt->rowCount();
}



//search all BOUGHT products for branch
function search_all_bought_products_for_branch($search, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_brand B, tbl_color CO, tbl_supplier SP   WHERE ((CO.tbl_colorId = I.tbl_colorId) AND (B.tbl_brandId = I.tbl_brandId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (I.tbl_productId = P.tbl_productId) AND (I.tbl_supplierId = SP.tbl_supplierId)) AND (P.tbl_productBrand LIKE  '%".$search."%' OR S.tbl_specificationYear LIKE  '%".$search."%'  OR C.tbl_product_categName LIKE  '%".$search."%' OR I.tbl_inventoryDate LIKE  '%".$search."%'  OR SP.tbl_supplierFname LIKE  '%".$search."%' ) AND (I.tbl_inventoryStatus = ?) ORDER BY I.tbl_inventoryId  DESC LIMIT 100 ");
 
 $stmt->bindParam(1, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}



//all BOUGHT products report for branch
function find_all_bought_products_report_for_branch($from, $to, $branch, $status){
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_brand B, tbl_color CO   WHERE ((CO.tbl_colorId = I.tbl_colorId) AND (B.tbl_brandId = I.tbl_brandId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (I.tbl_productId = P.tbl_productId)) AND (I.tbl_inventoryDate BETWEEN ? AND  ? ) AND (I.tbl_brandId = ?) AND (I.tbl_inventoryStatus = ?)  ORDER BY I.tbl_inventoryId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);
 
 $stmt->bindParam(3, $branch);
 $stmt->bindParam(4, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}



//all BOUGHT products for branch
function find_all_bought_products_for_branch($branch, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory I, tbl_product P, tbl_specification S, tbl_product_categ C, tbl_brand B, tbl_color CO   WHERE ((CO.tbl_colorId = I.tbl_colorId) AND (B.tbl_brandId = I.tbl_brandId) AND (S.tbl_product_categId = C.tbl_product_categId) AND (P.tbl_specificationId = S.tbl_specificationId) AND (I.tbl_productId = P.tbl_productId)) AND (I.tbl_brandId = ?) AND (I.tbl_inventoryStatus = ?) AND (I.tbl_inventoryAvailQty > 0) ORDER BY I.tbl_inventoryId  DESC LIMIT 20 ");
 
 $stmt->bindParam(1, $branch);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


//COUNT all BOUGHT products for branch
function count_all_bought_products_for_branch($branch, $status)
 {
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM  tbl_inventory WHERE (tbl_brandId = ?) AND (tbl_inventoryStatus = ?) ORDER BY tbl_inventoryId  DESC ");
 
 $stmt->bindParam(1, $branch);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 return $stmt->rowCount();
}






/* supplier function  */

//find supplier   by id
function find_supplier_by_id($supplId)
{
global $connectMe;
 $safeSupplId = mysql_prep($supplId);

 $supplStmt = $connectMe->prepare("SELECT * FROM tbl_supplier  WHERE tbl_supplierId = ?  LIMIT 1");
 $supplStmt->bindParam(1, $safeSupplId);
 $supplStmt->execute();
        
  if ($suppl = $supplStmt->fetch(PDO::FETCH_OBJ)) {
    return $suppl;
  }

}


//find supplier   by token
function find_supplier_by_token($supplId)
{
global $connectMe;
 $safeSupplId = mysql_prep($supplId);

 $supplStmt = $connectMe->prepare("SELECT * FROM tbl_supplier  WHERE tbl_supplierToken = ?  LIMIT 1");
 $supplStmt->bindParam(1, $safeSupplId);
 $supplStmt->execute();
        
  if ($suppl = $supplStmt->fetch(PDO::FETCH_OBJ)) {
    return $suppl;
  }

}


//all suppliers for business
function find_all_suppliers_for_business($businessId, $status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_supplier WHERE (tbl_businessId  = ?) AND (tbl_supplierStatus  = ?) ORDER BY tbl_supplierId  DESC ");
 
  $stmt->bindParam(1, $businessId);
  $stmt->bindParam(2, $status);

  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  
  return $data;

}


//all suppliers 
function find_all_suppliers($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_supplier WHERE tbl_supplierStatus  = ? ORDER BY tbl_supplierId  DESC ");
 
  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;

}

//count all  suppliers for business
function countAllSuppliersForBusId($status, $businessId){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_supplier WHERE (tbl_supplierStatus = ?) AND (tbl_businessId = ?)  ORDER BY tbl_supplierId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  return $stmt->rowCount();
}


//count all  suppliers
function count_all_suppliers($status){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_supplier WHERE tbl_supplierStatus = ?  ORDER BY tbl_supplierId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  return $stmt->rowCount();
}


//all products for suppliers 
function find_products_for_suppliers($supplId){
 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_inventory WHERE tbl_supplierId  = ? ORDER BY tbl_inventoryId  DESC ");
 
 $stmt->bindParam(1, $supplId);
 $stmt->execute();

 $supplPro = $stmt->fetchAll(PDO::FETCH_OBJ);
 return $supplPro;

}

//all suppliers 
function find_all_sold_products_for_employee($emplId)
 {
 global $connectMe;
 $soldProStmt = $connectMe->prepare("SELECT * FROM tbl_sold_prod WHERE tbl_employeeId  = ? ORDER BY tbl_sold_prodId  DESC ");
 $status = 1;

 $soldProStmt->bindParam(1, $emplId);
 $soldProStmt->execute();

 $soldPro = $soldProStmt->fetchAll(PDO::FETCH_OBJ);
 return $soldPro;

}


//find_my_employee_details
function find_my_employee_details($employeeId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_employee WHERE (tbl_employeeId  = ?) AND (tbl_employeeStatus  = ?) ORDER BY tbl_employeeId  DESC ");

 $stmt->bindParam(1, $employeeId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


//find_all_employees 
function find_all_employees_for_business($businessId, $status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_employee WHERE (tbl_businessId  = ?) AND (tbl_employeeStatus  = ?) ORDER BY tbl_employeeId  DESC ");

 $stmt->bindParam(1, $businessId);
 $stmt->bindParam(2, $status);

 $stmt->execute();
 $data = $stmt->fetchAll(PDO::FETCH_OBJ);

 return $data;
}


//find_all_employees 
function find_all_employees($status)
 {
 global $connectMe;
 $empStmt = $connectMe->prepare("SELECT * FROM tbl_employee WHERE tbl_employeeStatus  = ? ORDER BY tbl_employeeId  DESC ");

 $empStmt->bindParam(1, $status);
 $empStmt->execute();

 $empl = $empStmt->fetchAll(PDO::FETCH_OBJ);
 return $empl;
}


//count_all_employees 
function count_all_employees($status){

 global $connectMe;
 $stmt = $connectMe->prepare("SELECT * FROM tbl_employee WHERE tbl_employeeStatus  = ? ORDER BY tbl_employeeId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 return $stmt->rowCount();
}

//find product category  by id
function find_employee_categ_by_id($categId)
{
global $connectMe;
 $safeCategId = mysql_prep($categId);

 $ctgStmt = $connectMe->prepare("SELECT * FROM tbl_emp_type  WHERE tbl_emp_typeId = ?  LIMIT 1");
 $ctgStmt->bindParam(1, $safeCategId);
 $ctgStmt->execute();
        
  if ($categ = $ctgStmt->fetch(PDO::FETCH_OBJ)) {
    return $categ;
  }

}


//find_all_ category  by id
function find_all_employee_categ($status)
{
global $connectMe;
 $safeStatusId = mysql_prep($status);

 $ctgStmt = $connectMe->prepare("SELECT * FROM tbl_emp_type  WHERE tbl_emp_typeStatus = ? ORDER BY tbl_emp_typeName ASC ");
 $ctgStmt->bindParam(1, $safeStatusId);
 $ctgStmt->execute();
        
 $ctg = $ctgStmt->fetchAll(PDO::FETCH_OBJ);
 return $ctg;

}


//all employee phone  
function find_employee_phone($empId)
 {
 global $connectMe;
 $safeEmpId = mysql_prep($empId);

 $empStmt = $connectMe->prepare("SELECT * FROM tbl_emp_tel  WHERE tbl_employeeId = ? LIMIT 1 ");
 $empStmt->bindParam(1, $safeEmpId);
 $empStmt->execute();

 $empl = $empStmt->fetchAll(PDO::FETCH_OBJ);
 return $empl;
}



//all employee phone  
function find_employee_contract($empId)
 {
 global $connectMe;
 $safeEmpId = mysql_prep($empId);

 $empStmt = $connectMe->prepare("SELECT * FROM tbl_emp_contract  WHERE tbl_employeeId = ? ");
 $empStmt->bindParam(1, $safeEmpId);
 $empStmt->execute();

 $empl = $empStmt->fetchAll(PDO::FETCH_OBJ);
 return $empl;
}



/* education level function  */

//all  employee degree

function find_all_degrees($status)
 {
 global $connectMe;
 $status = mysql_prep($status);

 $dgrStmt = $connectMe->prepare("SELECT * FROM tbl_degree WHERE tbl_degreeStatus   = ? ORDER BY tbl_degreeName  ASC ");


 $dgrStmt->bindParam(1, $status);
 $dgrStmt->execute();

 $dgr = $dgrStmt->fetchAll(PDO::FETCH_OBJ);
 return $dgr;
}


//find degree by id
 function find_degree_by_id($degreeId)
 {
 global $connectMe;

 $safeDegreeId = mysql_prep($degreeId);
 $degreeStmt = $connectMe->prepare("SELECT * FROM tbl_degree WHERE tbl_degreeId = ? ORDER BY tbl_degreeName ASC ");

 $degreeStmt->bindParam(1, $safeDegreeId);
 $degreeStmt->execute();
       
  if ($degree = $degreeStmt->fetch(PDO::FETCH_OBJ)) {
    return $degree;
  }


}








/* province functions */

//all provinces
 function find_all_provinces($status)
 {
 global $connectMe;
 $provStmt = $connectMe->prepare("SELECT * FROM tbl_province  WHERE  tbl_provinceStatus = ? ORDER BY tbl_provinceName ");

 $provStmt->bindParam(1, $status);
 $provStmt->execute();
       
 $provSet = $provStmt->fetchAll(PDO::FETCH_OBJ);
 return $provSet;

}



//find_all_dist
 function find_all_dist($status)
 {
 global $connectMe;
 $provStmt = $connectMe->prepare("SELECT * FROM tbl_district  WHERE  tbl_districtStatus = ? ORDER BY tbl_districtName ");

 $provStmt->bindParam(1, $status);
 $provStmt->execute();
       
 $provSet = $provStmt->fetchAll(PDO::FETCH_OBJ);
 return $provSet;

}




//find_all_sectors
 function find_all_sectors($status)
 {
 global $connectMe;
 $provStmt = $connectMe->prepare("SELECT * FROM tbl_sector  WHERE  tbl_sectorStatus = ? ORDER BY tbl_sectorName ");

 $provStmt->bindParam(1, $status);
 $provStmt->execute();
       
 $provSet = $provStmt->fetchAll(PDO::FETCH_OBJ);
 return $provSet;

}


//find_all_cells
 function find_all_cells($status)
 {
 global $connectMe;
 $provStmt = $connectMe->prepare("SELECT * FROM tbl_cell  WHERE  tbl_cellStatus = ? ORDER BY tbl_cellName ");

 $provStmt->bindParam(1, $status);
 $provStmt->execute();
       
 $provSet = $provStmt->fetchAll(PDO::FETCH_OBJ);
 return $provSet;

}



//findAllVillage
 function findAllVillage($status)
 {
 global $connectMe;
 $provStmt = $connectMe->prepare("SELECT * FROM tbl_village  WHERE  tbl_villageStatus = ? ORDER BY tbl_villageName ");

 $provStmt->bindParam(1, $status);
 $provStmt->execute();
       
 $provSet = $provStmt->fetchAll(PDO::FETCH_OBJ);
 return $provSet;

}



/* province functions */

//all districts for the province
 function find_distrs_for_province($provId)
 {
 global $connectMe;

 $safeProvId = mysql_prep($provId);
 $distStmt = $connectMe->prepare("SELECT * FROM tbl_district  WHERE  tbl_provinceId = ? ORDER BY tbl_districtName ASC ");

 $distStmt->bindParam(1, $safeProvId);
 $distStmt->execute();
       
 $distSet = $distStmt->fetchAll(PDO::FETCH_OBJ);
 return $distSet;

}



/* sector s function  */

//find  find_sector_for_district 
 function find_sector_for_district($distId)
 {
 global $connectMe;

 $safeDistId = mysql_prep($distId);
 $secStmt = $connectMe->prepare("SELECT * FROM tbl_sector WHERE tbl_districtId = ? ORDER BY tbl_sectorName ASC ");

 $secStmt->bindParam(1, $safeDistId);
 $secStmt->execute();
       
 $secSet = $secStmt->fetchAll(PDO::FETCH_OBJ);
 return $secSet;

}

//find district  by id
function find_district_by_id($distId)
{
global $connectMe;
 $safeDistId = mysql_prep($distId);

 $stmt = $connectMe->prepare("SELECT * FROM  tbl_district  WHERE tbl_districtId = ?  LIMIT 1");
 $stmt->bindParam(1, $safeDistId);
 $stmt->execute();
        
  if ($dist = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $dist;
  }

}

//find province  by id
function find_province_by_id($provId)
{
global $connectMe;
 $safe = mysql_prep($provId);

 $stmt = $connectMe->prepare("SELECT * FROM  tbl_province  WHERE tbl_provinceId = ?  LIMIT 1");
 $stmt->bindParam(1, $safe);
 $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}



/* sector s function  */

//find  find_sector_for_district 
 function find_cell_for_sector($sectorId)
 {
 global $connectMe;

 $safeSecId = mysql_prep($sectorId);
 $cellStmt = $connectMe->prepare("SELECT * FROM tbl_cell WHERE tbl_sectorId = ? ORDER BY tbl_cellName ASC ");

 $cellStmt->bindParam(1, $safeSecId);
 $cellStmt->execute();
       
 $cellSet = $cellStmt->fetchAll(PDO::FETCH_OBJ);
 return $cellSet;

}


//find sector  by id
function find_sector_by_id($sectId)
{
global $connectMe;
 $safeSectId = mysql_prep($sectId);

 $secStmt = $connectMe->prepare("SELECT * FROM  tbl_sector  WHERE tbl_sectorId = ?  LIMIT 1");
 $secStmt->bindParam(1, $safeSectId);
 $secStmt->execute();
        
  if ($sect = $secStmt->fetch(PDO::FETCH_OBJ)) {
    return $sect;
  }

}



//find cell  by id
function find_cell_by_id($cellId)
{
global $connectMe;
 $safeCellId = mysql_prep($cellId);

 $cellStmt = $connectMe->prepare("SELECT * FROM tbl_cell  WHERE tbl_cellId = ?  LIMIT 1");
 $cellStmt->bindParam(1, $safeCellId);
 $cellStmt->execute();
        
  if ($cell = $cellStmt->fetch(PDO::FETCH_OBJ)) {
    return $cell;
  }

}



/* messages functions */


//all new messages
function find_all_new_messages()
 {
 global $connectMe;
 $msgStmt = $connectMe->prepare("SELECT * FROM tbl_message WHERE tbl_messageStatus = ? AND tbl_messageRead = ? ORDER BY tbl_messageDate DESC ");
 $status = 1;
 $read = 0;

 $msgStmt->bindParam(1, $status);
 $msgStmt->bindParam(2, $read);
 $msgStmt->execute();

 $msg = $msgStmt->fetchAll(PDO::FETCH_OBJ);
 return $msg;
}


















/* all function for other user   */

//all recently added products
function find_all_products_for_employ($empId)
 {
 global $connectMe;
 $proStmt = $connectMe->prepare("SELECT * FROM tbl_product WHERE tbl_productStatus = ?  AND tbl_productId = ?   ORDER BY tbl_productDate  DESC LIMIT 5 ");
 $status = 1;
 
 $proStmt->bindParam(1, $status);
 $proStmt->bindParam(2, $empId);
 $proStmt->execute();

 $pro = $proStmt->fetchAll(PDO::FETCH_OBJ);
 return $pro;
}


//search customer
function search_customer($cust)
 {
 global $connectMe;
 $custStmt = $connectMe->prepare("SELECT * FROM tbl_customer WHERE tbl_customerFname LIKE  ?  ");
 
 $custStmt->bindParam(1, $cust);
 $custStmt->execute();

 $customer = $custStmt->fetchAll(PDO::FETCH_OBJ);
 return $customer;

}


//search supplier
function search_supplier($supplInfo)
 {
 global $connectMe;
 $supplStmt = $connectMe->prepare("SELECT * FROM tbl_supplier WHERE tbl_supplierFname LIKE  ?  ");
 
 $supplStmt->bindParam(1, $supplInfo);
 $supplStmt->execute();

 $suppl = $supplStmt->fetchAll(PDO::FETCH_OBJ);
 return $suppl;

}


//find message by category
function find_message_by_category($msgType, $empId)
 {
 global $connectMe;
 $msgStmt = $connectMe->prepare("SELECT * FROM tbl_message WHERE tbl_messageStatus  = ?  AND  tbl_employeeId  = ? AND tbl_messageRead = ?  ORDER BY tbl_messageDate  DESC ");
 $status = 1;
      
 $msgStmt->bindParam(1, $status);
 $msgStmt->bindParam(2, mysql_prep($empId));

 $msgStmt->bindParam(3, mysql_prep($msgType));
 $msgStmt->execute();

 $msg = $msgStmt->fetchAll(PDO::FETCH_OBJ);
 return $msg;
}



//////////////////////////////// vehicles /////////////////////////


//all vehicles 
function find_all_vehicles($status)  {

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle WHERE tbl_vehicleStatus  = ? ORDER BY tbl_vehicleId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}



//find_veh_categ_by_id
function find_veh_categ_by_id($category){

  global $connectMe;
  $safeCategId = mysql_prep($category);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle_type WHERE tbl_vehicle_typeId = ?  LIMIT 1");
  $stmt->bindParam(1, $safeCategId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//find_veh_by_token
function find_veh_by_token($veh){

  global $connectMe;
  $safeCategId = mysql_prep($veh);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle WHERE tbl_vehicleToken = ?  LIMIT 1");
  $stmt->bindParam(1, $safeCategId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//all vehicle types 
function find_all_vehicle_types($status)  {

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle_type WHERE tbl_vehicle_typeStatus  = ? ORDER BY tbl_vehicle_typeId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}


//find_veh_by_id
function find_veh_by_id($veh){

  global $connectMe;
  $safeCategId = mysql_prep($veh);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle WHERE tbl_vehicleId = ?  LIMIT 1");
  $stmt->bindParam(1, $safeCategId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}


//find_veh_by_plate
function find_veh_by_plate($vehPlate){

  global $connectMe;
  $safeCategId = mysql_prep($vehPlate);

  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle WHERE tbl_vehiclePlate = ?  LIMIT 1");
  $stmt->bindParam(1, $safeCategId);
  $stmt->execute();
        
  if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
    return $data;
  }

}



//all vehicles  for customer
function find_all_vehicles_for_customer($customer, $status)  {

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_vehicle WHERE tbl_customerId = ? AND tbl_vehicleStatus  = ? ORDER BY tbl_vehicleId  DESC ");

  $stmt->bindParam(1, $customer);
  $stmt->bindParam(2, $status);

  $stmt->execute();

  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  return $data;
}


/////////////////////////////////////////// branches function/////////////////////


  //all branches for branch and type
  function all_branches_for_branch_and_type($type, $branchId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE (tbl_brandId = ?) AND (tbl_brandStatus = ?)  AND (tbl_brandType = ?) ORDER BY tbl_brandId DESC ");

    $stmt->bindParam(1, $branchId);
    $stmt->bindParam(2, $status);

    $stmt->bindParam(3, $type);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }


  //all branches for business and type
  function all_branches_for_business_and_type($type, $businessId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE (tbl_businessId = ?) AND (tbl_brandStatus = ?)  AND (tbl_brandType = ?) ORDER BY tbl_brandId DESC ");

    $stmt->bindParam(1, $businessId);
    $stmt->bindParam(2, $status);

    $stmt->bindParam(3, $type);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }


  //all_branches_for_type_and_branch
  function all_branches_for_type_and_branch($type, $status, $branchId){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE tbl_brandType = ?  AND tbl_brandStatus = ?  AND tbl_brandId = ? ORDER BY tbl_brandId DESC ");

    $stmt->bindParam(1, $type);
    $stmt->bindParam(2, $status);

    $stmt->bindParam(3, $branchId);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }


  //all selling branches 
  function all_branches_for_type($type, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE tbl_brandType = ?  AND tbl_brandStatus = ? ORDER BY tbl_brandId DESC ");

    $stmt->bindParam(1, $type);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;

  }



  //all branches
  function all_branches($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE tbl_brandStatus = ? ORDER BY tbl_brandId DESC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;

  }

  //all branches for business
  function all_branches_for_business($businessId, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE (tbl_businessId = ?) AND (tbl_brandStatus = ?) ORDER BY tbl_brandId DESC ");

    $stmt->bindParam(1, $businessId);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;

  }


  //find_branch_by_token
  function find_branch_by_token($branchId){
 
    global $connectMe;
    $safe = mysql_prep($branchId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE tbl_brandToken = ?  LIMIT 1");
    $stmt->bindParam(1, $safe);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }
  
  //find_branch_by_token
  function find_branch_by_id($branchId){
 
    global $connectMe;
    $safe = mysql_prep($branchId);

    $stmt = $connectMe->prepare("SELECT * FROM tbl_brand WHERE tbl_brandId = ?  LIMIT 1");
    $stmt->bindParam(1, $safe);

    $stmt->execute();
        
    if ($data = $stmt->fetch(PDO::FETCH_OBJ)) {
      return $data;
    }
  }



  //count_all_transfer_for_branch
  function count_all_transfer_for_branch($branch, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_transfer WHERE (tbl_brandId = ?) AND (tbl_transferStatus = ?) ORDER BY tbl_transferId DESC ");

    $stmt->bindParam(1, $branch);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    return $stmt->rowCount();
  }


  //count_all_transfer
  function count_all_transfer($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_transfer WHERE (tbl_transferStatus = ?) ORDER BY tbl_transferId DESC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    return $stmt->rowCount();
  }



  //find_all_transfer_for_branch
  function find_all_transfer_for_branch($branch, $status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_transfer WHERE (tbl_brandId = ?) AND (tbl_transferStatus = ?) ORDER BY tbl_transferId DESC ");

    $stmt->bindParam(1, $branch);
    $stmt->bindParam(2, $status);

    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_OBJ);

    return $data;
  }


  //find_all_transfer
  function find_all_transfer($status){

    global $connectMe;
    $stmt = $connectMe->prepare("SELECT * FROM tbl_transfer WHERE  (tbl_transferStatus = ?) ORDER BY tbl_transferId DESC ");

    $stmt->bindParam(1, $status);
    $stmt->execute();

    $data = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $data;
  }




//transfer_to
function transfer_to($specification, $branch, $size, $brand )
{
global $connectMe;

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_product   WHERE (tbl_specificationId = ?) AND (tbl_brandId = ?) AND (tbl_product_categId = ?)  AND (tbl_productBrand = ?)  LIMIT 1");
 $proStmt->bindParam(1, $specification);

 $proStmt->bindParam(2, $branch);
 $proStmt->bindParam(3, $size);

$proStmt->bindParam(4, $brand);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}



//new functions

//calculate sold amount for business
function calculateSoldAmountForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodAmount * S.tbl_sold_prodQty )  as amount FROM  tbl_sold_prod S, tbl_branch_product BP, tbl_brand B  WHERE (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (S.tbl_sold_prodStatus  = ?)  AND  (S.tbl_branch_productId  = BP.tbl_branch_productId)  AND  (BP.tbl_brandId  = B.tbl_brandId)  AND  (B.tbl_businessId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate sold amount
function calculateSoldAmount($status, $from, $to){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(tbl_sold_prodAmount * tbl_sold_prodQty )  as amount FROM  tbl_sold_prod WHERE (tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (tbl_sold_prodStatus  = ?)   ORDER BY tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;
}


//calculate sold quantity for business
function calculateSoldQtyForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty)  as qty FROM  tbl_sold_prod S, tbl_branch_product BP, tbl_brand B WHERE  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus  = ?)  AND  (S.tbl_branch_productId  = BP.tbl_branch_productId)  AND  (BP.tbl_brandId  = B.tbl_brandId)  AND  (B.tbl_businessId  = ?)  ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}



//calculate sold quantity
function calculateSoldQty($status, $from, $to)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(tbl_sold_prodQty)  as qty FROM  tbl_sold_prod WHERE  (tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (tbl_sold_prodStatus  = ?)   ORDER BY tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}


//calculate bought amount for business
function calculateBoughtAmountForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryQty )  as amount FROM  tbl_inventory I, tbl_brand B WHERE  (I.tbl_inventoryDate BETWEEN ? AND  ?  ) AND (I.tbl_inventoryStatus  = ?)  AND (I.tbl_brandId  = B.tbl_brandId) AND (B.tbl_businessId  = ?)  ORDER BY I.tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}


//calculate bought amount
function calculateBoughtAmount($status, $from, $to)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(tbl_inventoryCost * tbl_inventoryQty )  as amount FROM  tbl_inventory WHERE  (tbl_inventoryDate BETWEEN ? AND  ?  ) AND (tbl_inventoryStatus  = ?)   ORDER BY tbl_inventoryId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}


//calculate value of stock qty for business 
function calculateQtyOfStockForBusId($status, $from, $to, $businessId){
  global $connectMe;

  $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryAvailQty )  as qty FROM  tbl_inventory I, tbl_brand B  WHERE  (I.tbl_inventoryStatus  = ?) AND (B.tbl_businessId  = ?)  AND (B.tbl_brandId  = I.tbl_brandId)   ORDER BY I.tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}



//calculate value of stock amount for business 
function calculateValueOfStockForBusId($status, $from, $to, $businessId){
  global $connectMe;

  $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryAvailQty )  as amount FROM  tbl_inventory I, tbl_brand B  WHERE  (I.tbl_inventoryStatus  = ?) AND (B.tbl_businessId  = ?)  AND (B.tbl_brandId  = I.tbl_brandId)   ORDER BY I.tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate value of stock amount
function calculateValueOfStock($status, $from, $to){
 global $connectMe;

 $stmt = $connectMe->prepare("SELECT SUM(tbl_inventoryCost * tbl_inventoryAvailQty )  as amount FROM  tbl_inventory WHERE  (tbl_inventoryStatus  = ?)   ORDER BY tbl_inventoryId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}

//calculate value of stock  FOR ALL BRANCHES 
function calculateValueOfStockForAllBranch($status, $from, $to){
 global $connectMe;
  
 //$stmt = $connectMe->prepare("SELECT SUM(tbl_inventoryCost * tbl_inventoryAvailQty )  as amount FROM  tbl_inventory WHERE  (tbl_inventoryDate BETWEEN ? AND  ?  ) AND (tbl_inventoryStatus  = ?)   ORDER BY tbl_inventoryId  DESC ");

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * BP.tbl_branch_productAvail )  as amount FROM  tbl_inventory I, tbl_branch_product BP  WHERE (I.tbl_inventoryId  = BP.tbl_inventoryId) AND   (BP.tbl_branch_productStatus  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}



//calculate value of stock  FOR ALL BRANCHES And business 
function calculateValueOfStockForAllBranchAndBusId($status, $from, $to, $businessId){
  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * BP.tbl_branch_productAvail )  as amount FROM  tbl_inventory I, tbl_branch_product BP, tbl_brand B  WHERE (I.tbl_inventoryId  = BP.tbl_inventoryId) AND (B.tbl_brandId  = BP.tbl_brandId) AND (BP.tbl_branch_productStatus  = ?)  AND (B.tbl_businessId  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate value of stock  FOR ALL BRANCHES And business 
function calculateQtyOfStockForAllBranchAndBusId($status, $from, $to, $businessId){
  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(BP.tbl_branch_productAvail )  as qty FROM  tbl_inventory I, tbl_branch_product BP, tbl_brand B  WHERE (I.tbl_inventoryId  = BP.tbl_inventoryId) AND (B.tbl_brandId  = BP.tbl_brandId) AND (BP.tbl_branch_productStatus  = ?)  AND (B.tbl_businessId  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate value of stock amount for product
function calculateValueOfStockForProd($status, $from, $to, $prodId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryAvailQty )  as amount FROM  tbl_inventory I, tbl_product P WHERE  (I.tbl_inventoryStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId) AND (I.tbl_productId  = ?)   ORDER BY I.tbl_inventoryId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $prodId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate value of stock amount for product and date
function calculateValueOfStockForProdAndDate($status, $from, $to, $prodId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryAvailQty )  as amount FROM  tbl_inventory I, tbl_product P WHERE  (I.tbl_inventoryStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId) AND (I.tbl_productId  = ?)    ORDER BY I.tbl_inventoryId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $prodId);

 //$stmt->bindParam(3, $to);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}


//calculate value of stock amount for brand
function calculateValueOfStockForBrand($status, $from, $to, $brandId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryAvailQty )  as amount FROM  tbl_inventory I, tbl_product P WHERE  (I.tbl_inventoryStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId) AND (P.tbl_specificationId  = ?)   ORDER BY I.tbl_inventoryId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $brandId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate value of branch stock amount for brand
function calculateValueOfBranchStockForBrand($status, $from, $to, $brandId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * BP.tbl_branch_productAvail )  as amount FROM  tbl_inventory I, tbl_product P, tbl_branch_product BP  WHERE (I.tbl_inventoryId  = BP.tbl_inventoryId) AND (BP.tbl_branch_productStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId)  AND (P.tbl_specificationId  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $brandId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;

}

//calculate value of branch stock amount for product and date
function calculateValueOfBranchStockForProdAndDate($status, $from, $to, $prodId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * BP.tbl_branch_productAvail )  as amount FROM  tbl_inventory I, tbl_product P, tbl_branch_product BP  WHERE (I.tbl_inventoryId  = BP.tbl_inventoryId)   AND (BP.tbl_branch_productStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId)  AND (BP.tbl_productId  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(1, $to);
 $stmt->bindParam(1, $status);

 $stmt->bindParam(2, $prodId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate current value of main stock amount for product and date
function currentValueOfMainStockForProdAndDate($status, $from, $to, $prodId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryAvailQty )  as amount FROM  tbl_inventory I, tbl_product P  WHERE     (I.tbl_inventoryStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId)  AND (I.tbl_productId  = ?)   ORDER BY I.tbl_inventoryId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(1, $to);
 $stmt->bindParam(1, $status);

 $stmt->bindParam(2, $prodId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}



//calculate value of branch stock amount for product
function calculateValueOfBranchStockForProd($status, $from, $to, $prodId){
 global $connectMe;
  

 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * BP.tbl_branch_productAvail )  as amount FROM  tbl_inventory I, tbl_product P, tbl_branch_product BP  WHERE (I.tbl_inventoryId  = BP.tbl_inventoryId) AND (BP.tbl_branch_productStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId)  AND (BP.tbl_productId  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $prodId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate value of stock qty for brand
function calculateQtyOfStockForBrand($status, $from, $to, $brandId){
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryAvailQty )  as qty FROM  tbl_inventory I, tbl_product P WHERE  (I.tbl_inventoryStatus  = ?)  AND (I.tbl_productId  = P.tbl_productId) AND (P.tbl_specificationId  = ?)   ORDER BY I.tbl_inventoryId  DESC ");

 //$stmt->bindParam(1, $from);
 //$stmt->bindParam(2, $to);

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $brandId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}



//calculate value of branch stock qty for brand
function calculateQtyOfBranchStockForBrand($status, $from, $to, $brandId){
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(BP.tbl_branch_productAvail )  as qty FROM  tbl_product P, tbl_branch_product BP  WHERE  (BP.tbl_branch_productStatus  = ?)  AND (BP.tbl_productId  = P.tbl_productId) AND (P.tbl_specificationId  = ?)   ORDER BY BP.tbl_branch_productId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $brandId);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate bought amount for business

function calculateRemainBoughtAmountForBusId($status, $from, $to, $businessId){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT SUM( (I.tbl_inventoryCost) * ( S.tbl_sold_prodQty)   )  as amount FROM  tbl_inventory I, tbl_sold_prod S, tbl_branch_product BP, tbl_brand B  WHERE (I.tbl_inventoryId = S.tbl_inventoryId) AND (S.tbl_branch_productId = BP.tbl_branch_productId) AND (BP.tbl_brandId = B.tbl_brandId)  AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  )  AND (S.tbl_sold_prodStatus  = ?)  AND (B.tbl_businessId  = ?)     ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->bindParam(4, $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;

}



//calculate bought amount

function calculateRemaingBoughtAmount($status, $from, $to){

  global $connectMe;

  $stmt = $connectMe->prepare("SELECT SUM( (I.tbl_inventoryCost) * ( S.tbl_sold_prodQty)   )  as amount FROM  tbl_inventory I, tbl_sold_prod S  WHERE (I.tbl_inventoryId = S.tbl_inventoryId)  AND  (S.tbl_sold_prodDate BETWEEN ? AND  ?  )  AND (S.tbl_sold_prodStatus  = ?)     ORDER BY S.tbl_sold_prodId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to);

  $stmt->bindParam(3, $status);
  $stmt->execute();

  $data = $stmt->fetch(PDO::FETCH_OBJ);
  return $data;

}


//calculate bought quantity for business
function calculateBoughtQtyForBusId($status, $from, $to, $businessId){

  global $connectMe;
  
  $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryQty)  as qty FROM  tbl_inventory I, tbl_brand B  WHERE (I.tbl_inventoryDate BETWEEN ? AND  ?  ) AND (I.tbl_inventoryStatus  = ?) AND (I.tbl_brandId  = B.tbl_brandId) AND (B.tbl_businessId  = ?)  ORDER BY I.tbl_inventoryId  DESC ");

  $stmt->bindParam(1, $from);
  $stmt->bindParam(2, $to );

  $stmt->bindParam(3,  $status);
  $stmt->bindParam(4,  $businessId);

  $stmt->execute();
  $data = $stmt->fetch(PDO::FETCH_OBJ);

  return $data;
}


//calculate bought quantity
function calculateBoughtQty($status, $from, $to)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(tbl_inventoryQty)  as qty FROM  tbl_inventory WHERE (tbl_inventoryDate BETWEEN ? AND  ?  ) AND (tbl_inventoryStatus  = ?)   ORDER BY tbl_inventoryId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to );

 $stmt->bindParam(3,  $status);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}




//calculate remaining product
function calculateRemainingProd($status)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(tbl_productQty)  as qty FROM tbl_product WHERE (tbl_productStatus  = ?)   ORDER BY tbl_productId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}



//New functions

//calculate sold amount for branch
function calculateSoldAmountForBranch($status, $from, $to, $branch)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodAmount * S.tbl_sold_prodQty )  as amount FROM  tbl_sold_prod S, tbl_product P  WHERE (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND  (S.tbl_sold_prodStatus  = ?)   AND  (S.tbl_productId  = P.tbl_productId) AND  (P.tbl_brandId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branch);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}



//calculate sold quantity for branch
function calculateSoldQtyForBranch($status, $from, $to, $branch)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(S.tbl_sold_prodQty)  as qty FROM  tbl_sold_prod S, tbl_product P WHERE  (S.tbl_sold_prodDate BETWEEN ? AND  ?  ) AND (S.tbl_sold_prodStatus  = ?) AND  (S.tbl_productId  = P.tbl_productId) AND  (P.tbl_brandId  = ?)   ORDER BY S.tbl_sold_prodId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branch);

 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}



//calculate bought amount for branch
function calculateBoughtAmountForBranch($status, $from, $to, $branch)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryCost * I.tbl_inventoryQty )  as amount FROM  tbl_inventory I, tbl_product P WHERE  (I.tbl_inventoryDate BETWEEN ? AND  ?  ) AND (I.tbl_inventoryStatus  = ?)  AND  (I.tbl_productId  = P.tbl_productId) AND  (P.tbl_brandId  = ?)   ORDER BY I.tbl_inventoryId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branch);

 $stmt->execute();

 $data = $stmt->fetch(PDO::FETCH_OBJ);
 return $data;
}



//calculate bought quantity for branch
function calculateBoughtQtyForBranch($status, $from, $to, $branch)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(I.tbl_inventoryQty)  as qty FROM  tbl_inventory I, tbl_product P WHERE (I.tbl_inventoryDate BETWEEN ? AND  ?  ) AND (I.tbl_inventoryStatus  = ?) AND  (I.tbl_productId  = P.tbl_productId) AND  (P.tbl_brandId  = ?)   ORDER BY I.tbl_inventoryId  DESC ");

 $stmt->bindParam(1, $from);
 $stmt->bindParam(2, $to);

 $stmt->bindParam(3, $status);
 $stmt->bindParam(4, $branch);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}


//calculate remaining product for branch
function calculateRemainingProdForBranch($status, $branch)
 {
 global $connectMe;
  
 $stmt = $connectMe->prepare("SELECT SUM(tbl_productQty)  as qty FROM tbl_product WHERE (tbl_productStatus  = ?) AND (tbl_brandId  = ?)   ORDER BY tbl_productId  DESC ");

 $stmt->bindParam(1, $status);
 $stmt->bindParam(2, $branch);

 $stmt->execute();
 $data = $stmt->fetch(PDO::FETCH_OBJ);

 return $data;
}





//new functions

//search notifications
function searchNotification($search, $status, $user){
  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_notification WHERE  ( tbl_notificationTitle LIKE  '%".$search."%'    ) AND ((tbl_notificationStatus = ?  ) AND ( tbl_userId =?))   ORDER BY tbl_notificationId  DESC ");

  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $user);

  $stmt->execute();
  $res = $stmt->fetchAll(PDO::FETCH_OBJ);

  return $res;

}


function allNotifications($type, $user){

  global $connectMe;
  $stmt = $connectMe->prepare("SELECT * FROM tbl_notification  WHERE  (tbl_notificationStatus = ?  ) AND ( tbl_userId =?)   ORDER BY tbl_notificationId DESC LIMIT 50 ");


  $stmt->bindParam(1, $type);
  $stmt->bindParam(2, $user);

  $stmt->execute();
  $data = $stmt->fetchAll(PDO::FETCH_OBJ);
  
  return $data;
}



//NotifyMe
function NotifyMe($status, $title, $user ){
  
  global $connectMe;
  $date = date("Y-m-d");

  $time =  date("h:i:sa");

  $stmt = $connectMe->prepare(" 
        INSERT INTO  tbl_notification ( tbl_userId,  tbl_notificationTitle, tbl_notificationDate, tbl_notificationTime, tbl_notificationStatus)  VALUES (?,?,? ,?,?) ");
  $stmt->bindParam(1, $user);
  $stmt->bindParam(2, $title);

  $stmt->bindParam(3, $date);
  $stmt->bindParam(4, $time);

  $stmt->bindParam(5, $status);
  if($stmt->execute()){

    return true;

  }else{

  return false;

}

 
}



//find notification   by id
function find_notification_by_id($notificationId)
{
global $connectMe;
 $safeProId = mysql_prep($notificationId);

 $proStmt = $connectMe->prepare("SELECT * FROM tbl_notification  WHERE tbl_notificationId = ?  LIMIT 1");
 $proStmt->bindParam(1, $safeProId);
 $proStmt->execute();
        
  if ($pro = $proStmt->fetch(PDO::FETCH_OBJ)) {
    return $pro;
  }

}



//viewNotification
function viewNotification($status, $notification ){
  
  global $connectMe;

  $stmt = $connectMe->prepare(" UPDATE  tbl_notification SET  tbl_notificationStatus =? WHERE tbl_notificationId =? ");
  $stmt->bindParam(1, $status);
  $stmt->bindParam(2, $notification);

  if($stmt->execute()){

    return true;

  }else{

    return false;

  }

 
}


?>


