<?php
/**
 * @property NotificationComponent $Notification
 */
class Backup extends AppModel
{
    /*
     * For restoring a backup from archieve database to main database
     */
    public function restore($unique_url = null, $showMessagge = 0)
    {
        if(!empty($unique_url)){
        $expenseType = substr($unique_url, 0, 1);

        try {
          $db = ConnectionManager::getDataSource('default');

        } catch (Exception $ex) {

            echo json_encode([
                'status' => '500',
                'type' => 'Db connection error',
                'message' => $ex->getMessage(),
            ]);

            exit;
        }
        $backup_expense = $db->query("select * from backup_expenses where unique_url='".$unique_url."'");

        //check  if  the record already in backup tables or not
        if (!empty($backup_expense)) {
            $prod_expense = $db->query("select * from expenses where unique_url= '".$unique_url."'");
            if(empty($prod_expense)) {
                // if there is no such  expense  on production theb proceed to shift expense data  from backup to production

                $expense_id = $backup_expense[0]["backup_expenses"]["id"];

                try{

                    $db->query("insert into expenses select * from backup_expenses where id =".$expense_id);
                    $db->query("delete from backup_expenses where id =".$expense_id); // delete from expenses
                } catch(Exception $ex){
                    if($showMessagge){
                    echo "shifting from  backup expense to expense failed!<br>";
                    }
                }
                
                if($expenseType == "1" || $expenseType == "2"){

                    try{
                        $db->query("insert into group_expenses select * from backup_group_expenses where expense_id =".$expense_id);
                        $db->query("delete from backup_group_expenses where expense_id =".$expense_id); // delete from group expense
                    } catch(Exception $ex){
                        if($showMessagge){
                        echo "backup group expenses delete failed<br>";
                        }
                    }
                    
    
                    try{
                        $db->query("insert into participants select * from backup_participants where expense_id =".$expense_id);
                        $db->query("delete from backup_participants where expense_id =".$expense_id); // delete from backup participants
                    } catch(Exception $ex){
                        if($showMessagge){
                        echo "backup participants expenses delete failed<br>";
                        }
                    }
                    
                    if($expenseType == "2"){
                        
                        try{
                            $db->query("insert into meals select * from backup_meals where expense_id =".$expense_id);
                            $db->query("delete from backup_meals where expense_id =".$expense_id); // delete from backup meals
                        } catch(Exception $ex){
                            if($showMessagge){
                            echo "backup meals expenses insert/delete failed<br>";
                            }
                        }
                        
                    }
                
                } else if($expenseType == "3"){

                    try{
                        $db->query("insert into family_expenses select * from backup_family_expenses where expense_id =".$expense_id);
                        $db->query("delete from backup_family_expenses where expense_id =".$expense_id); // delete from backup_family_expenses
                    } catch(Exception $ex){
                        if($showMessagge){
                        echo "backup MyXpense insert/delete failed<br>";
                        }
                    }
                
                }

                try{
                    $db->query("insert into logs select * from backup_logs where expense_id =".$expense_id);
                    $db->query("delete from backup_logs where expense_id =".$expense_id); // delete from backup_logs
                } catch(Exception $ex){
                    if($showMessagge){
                    echo "backup logs insert/delete failed<br>";
                    }
                }

                // restore group settings
                try{
                    $db->query("insert into group_settings select * from backup_group_settings where expense_id =".$expense_id);
                    $db->query("delete from backup_group_settings where expense_id =".$expense_id); // delete from backup_group_settings
                } catch(Exception $ex){
                    if($showMessagge){
                        echo "backup group_settings insert/delete failed<br>";
                    }
                }

                // restore permissions

                try{
                    $db->query("insert into permissions select * from backup_permissions where expense_id =".$expense_id);
                    $db->query("delete from backup_permissions where expense_id =".$expense_id); // delete from backup_permissions
                } catch(Exception $ex){
                    if($showMessagge){
                        echo "backup permissions insert/delete failed<br>";
                    }
                }

            } else {
                if($showMessagge){
                echo "Expense is already in production  database. Could not perform restore!";
             
                exit;
                }
            }

        } else {

            try {
                $archieve_db = ConnectionManager::getDataSource('archieve');

            }catch (Exception $ex){

                echo json_encode([
                    'status' => '500',
                    'type' => 'Db connection error',
                    'message' => $ex->getMessage(),
                ]);

                exit;
            }

            $archieve_expense = $archieve_db->query("select * from expenses where unique_url='".$unique_url."'");
            //if the expense already in archieve  database, then proceed
            if(!empty($archieve_expense)) {
                try{
                    $prod_expense = $db->query("select * from expenses where unique_url='".$unique_url."'");
                } catch (Exception $ex){
                    if($showMessagge){
                    echo "production expense not found";
                    }
                }
                if(empty($prod_expense)) {
                    // if there is no such  expense  on production then proceed to shift expense data from  archeive to production

                    $expense_id = $archieve_expense[0]["expenses"]["id"];

                    $expenseArray  =  $archieve_expense[0]["expenses"];

                    if(!empty($expenseArray)){
                        try{
                            App::import('model','Expense');
                            $expense = new Expense();
                            $expense->save($expenseArray);
                            $archieve_db->query("delete from expenses where id =".$expense_id); // delete from expenses
                        } catch (Exception $ex) {
                            if($showMessagge){
                            echo "insert into expenses failed";
                            
                            
                            exit;
                            }
                        }
                    }
                    
                    if($expenseType == "1" || $expenseType == "2"){

                        //insert into  group_expense from archieve
                        $groupExpenseArray = array();
                        try {
                            $prod_group_expense = $archieve_db->query("select * from group_expenses where expense_id=".$expense_id);
                        } catch (Exception $ex) {
                            $prod_group_expense = null;
                        }
                        foreach($prod_group_expense as $key=>$value){
                            $groupExpenseArray["GroupExpense"][$key] = $value["group_expenses"];
                        }
                        if(!empty($groupExpenseArray)){
                            App::import('model','GroupExpense');
                            $group_expense = new GroupExpense();
    
                            $group_expense->saveMany($groupExpenseArray["GroupExpense"]);
                            try {
                                $archieve_db->query("delete from group_expenses where expense_id =".$expense_id); // delete from expenses
                            } catch (Exception $ex) {
    
                            }
                        }
    
                        //insert into participants from archieve
                        $participantsArray = array();
                        try {
                            $prod_participants = $archieve_db->query("select * from participants where expense_id=".$expense_id);
                        } catch (Exception $ex) {
                            $prod_participants = null;
                        }
                        foreach($prod_participants as $key=>$value){
    
                            $participantsArray["Participant"][$key] = $value["participants"];
                        }
                        if(!empty($participantsArray)){
                            App::import('model','Participant');
                            $participant = new Participant();
                            $participant->saveMany($participantsArray["Participant"]);
                            try {
                                $archieve_db->query("delete from participants where expense_id =".$expense_id); // delete from expenses
                            } catch (Exception $ex) {
    
                            }
                        }
                        
                        if($expenseType == "2"){
                            
                            //insert into meals from archieve
                            $mealsArray = array();
                            try {
                                $prod_meals = $archieve_db->query("select * from meals where expense_id=".$expense_id);
                            } catch (Exception $ex) {
                                $prod_meals = null;
                            }
                            foreach($prod_meals as $key=>$value) {
                                $mealsArray["Meal"][$key] = $value["meals"];
                            }
                            if(!empty($mealsArray)){
                                App::import('model','Meal');
                                $meal = new Meal();
                                $meal->saveMany($mealsArray["Meal"]);
                                try {
                                    $archieve_db->query("delete from meals where expense_id =".$expense_id); // delete from expenses
                                } catch (Exception $ex) {
                                }
                            }
                            
                        }
                    
                    } else if($expenseType == "3"){

                        //insert into family_expenses from archieve
                        $family_expensesArray = array();
                        try {
                            $prod_family_expenses = $archieve_db->query("select * from family_expenses where expense_id=".$expense_id);
                        } catch (Exception $ex) {
    
                        }
                        foreach($prod_family_expenses as $key=>$value){
                            $family_expensesArray["FamilyExpense"][$key] = $value["family_expenses"];
                        }
    
                        if(!empty($family_expensesArray)){
                            App::import('model','FamilyExpense');
                            $familyexpense = new FamilyExpense();
                            $familyexpense->saveMany($family_expensesArray["FamilyExpense"]);
                            try {
                                $archieve_db->query("delete from family_expenses where expense_id =".$expense_id); // delete from expenses
                            } catch (Exception $ex) {
    
                            }
                        }
                    
                    }

                    //insert into logs from archieve
                    $logsArray = array();
                    try {
                        $prod_logs = $archieve_db->query("select * from logs where expense_id=".$expense_id);
                    } catch (Exception $ex) {
                        $prod_logs = null;
                    }
                    foreach($prod_logs as $key=>$value){
                        $logsArray["Log"][$key] = $value["logs"];
                    }
                    if(!empty($logsArray)){
                        App::import('model','Log');
                        $log = new Log();
                        $log->saveMany($logsArray["Log"]);
                        try {
                            $archieve_db->query("delete from logs where expense_id =".$expense_id); // delete from expenses
                        } catch (Exception $ex) {

                        }
                    }


                    //insert into group_settings from archieve
                    $groupSettingArray = array();
                    try {
                        $archive_group_settings = $archieve_db->query("select * from group_settings where expense_id=".$expense_id);
                    } catch (Exception $ex) {
                        $archive_group_settings = null;
                    }
                    foreach($archive_group_settings as $key=>$value){
                        $groupSettingArray["GroupSetting"][$key] = $value["group_settings"];
                    }
                    if(!empty($groupSettingArray)){
                        App::import('model','GroupSetting');
                        $groupSetting = new GroupSetting();
                        $groupSetting->saveMany($groupSettingArray["GroupSetting"]);
                        try {
                            $archieve_db->query("delete from group_settings where expense_id =".$expense_id); // delete from archive group_settings
                        } catch (Exception $ex) {

                        }
                    }

                    //insert into permissions from archieve
                    $permissionArray = array();
                    try {
                        $archive_permissions = $archieve_db->query("select * from permissions where expense_id=".$expense_id);
                    } catch (Exception $ex) {
                        $archive_permissions = null;
                    }
                    foreach($archive_permissions as $key=>$value){
                        $permissionArray["Permission"][$key] = $value["permissions"];
                    }
                    if(!empty($permissionArray)){
                        App::import('model','PermissionTable');
                        $permission = new PermissionTable();
                        $permission->saveMany($permissionArray["Permission"]);
                        try {
                            $archieve_db->query("delete from permissions where expense_id =".$expense_id); // delete from archive permissions
                        } catch (Exception $ex) {

                        }
                    }

                } else {
                    if($showMessagge){
                    echo "Expense is already in production  database. Could not perform restore!";
                    exit;
                    }
                }

            }
            else {
                if($showMessagge){
                echo  "Expense not available in Archieve database";
                 
                exit;
                }
            }
        }
        
        }
    }

    /*
     * For making automatic data backup to backup tables and delete them after backup
     */
    public function processBackupAndRemove($expiredRecordList, $showMessagge = 0) {
        //get all the expense_id
        foreach($expiredRecordList as $key=>$value){
            if(isset($value["expired_records"]) && !empty($value["expired_records"])) {

                $expenseIdList[] = $value["expired_records"]["id"];
                $expenseType = substr($value["expired_records"]["unique_url"], 0, 1);

                if ($expenseType == "1" || $expenseType == "2") {
                    if ($expenseType == "2") {
                        $messExpenseList[] = $value["expired_records"]["id"];
                    }
                    $groupAndMessExpenseList[] = $value["expired_records"]["id"];
                } else if ($expenseType == "3") {
                    $familyExpenseList[] = $value["expired_records"]["id"];
                }
            } else {

                $expenseIdList[] = $value["expenses"]["id"];
                $expenseType = substr($value["expenses"]["unique_url"], 0, 1);

                if ($expenseType == "1" || $expenseType == "2") {
                    if ($expenseType == "2") {
                        $messExpenseList[] = $value["expenses"]["id"];
                    }
                    $groupAndMessExpenseList[] = $value["expenses"]["id"];
                } else if ($expenseType == "3") {
                    $familyExpenseList[] = $value["expenses"]["id"];
                }
            }
        }

        $expenseIds = implode(",",$expenseIdList);

        //check existing data in the backup tables
        $db = ConnectionManager::getDataSource('default');
        $backup_expenses = $db->query("select id,unique_url from backup_expenses where id in(".$expenseIds.")");

        if(!empty($backup_expenses)){
            $this->deleteExistingExpense($backup_expenses, $db);
        }

        try{
            //process to shift expenses table to backup_expenses table
            // echo "insert into backup_expenses select * from expenses where id in(".$expenseIds.")";
            $db->query("insert into backup_expenses select * from expenses where id in(".$expenseIds.")");
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to insert backup_expenses.'. $ex->getMessage(), 0, $ex);
            }
        }

        try{
            //delete from expenses
            $db->query("delete from expenses where id in(".$expenseIds.")"); // delete from expenses
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed delete expenses.'. $ex->getMessage(), 0, $ex);
            }
        }

        try{
            //process to shift expense_emails table to backup_expense_emails table
            $db->query("insert into backup_expense_emails select * from expense_emails where id in(".$expenseIds.")");
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to insert backup_expense_emails.'. $ex->getMessage(), 0, $ex);
            }
        }

        try{
            // delete from expense_emails
            $db->query("delete from expense_emails where id in(".$expenseIds.")"); // delete from participants
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed delete expense_emails.'. $ex->getMessage(), 0, $ex);
            }
        }

        if(!empty($groupAndMessExpenseList)){

            $groupExpenseIds = implode(",",$groupAndMessExpenseList);

            try{
                //process to shift group_expenses table to backup_group_expenses table
                $db->query("insert into backup_group_expenses select * from group_expenses where expense_id in(".$groupExpenseIds.")");
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed to insert backup_group_expenses.'. $ex->getMessage(), 0, $ex);
                }
            }

            try{
                // delete from group_expenses
                $db->query("delete from group_expenses where expense_id in(".$groupExpenseIds.")"); // delete from group_expenses
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed delete group_expenses.'. $ex->getMessage(), 0, $ex);
                }
            }

            try{
                //process to shift participants table to backup_participants table
                $db->query("insert into backup_participants select * from participants where expense_id in(".$groupExpenseIds.")");
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed to insert backup_participants.'. $ex->getMessage(), 0, $ex);
                }
            }

            try{
                // delete from participants
                $db->query("delete from participants where expense_id in(".$groupExpenseIds.")"); // delete from participants
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed delete participants.'. $ex->getMessage(), 0, $ex);
                }
            }
        }

        //For  mess expense specially
        if(!empty($messExpenseList)){

            $messExpenseIds = implode(",",$messExpenseList);

            try{
                //process to shift meals table to backup_meal table
                $db->query("insert into backup_meals select * from meals where expense_id in(".$messExpenseIds.")");
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed to insert backup_meals.'. $ex->getMessage(), 0, $ex);
                }
            }

            try{
                //delete from meals
                $db->query("delete from meals where expense_id in(".$messExpenseIds.")"); // delete from meals
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed delete meals.'. $ex->getMessage(), 0, $ex);
                }
            }
        }


        //if it is family expense
        if(!empty($familyExpenseList)){

            $familyExpenseIds = implode(",",$familyExpenseList);
            try{
                //process to shift family_expenses table to backup_family_expenses table
                $db->query("insert into backup_family_expenses select * from family_expenses where expense_id in(".$familyExpenseIds.")");
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed to insert backup_family_expenses.'. $ex->getMessage(), 0, $ex);
                }
            }

            try{
                //delete from family_expenses
                $db->query("delete from family_expenses where expense_id in(".$familyExpenseIds.")"); // delete from family_expenses
            } catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed to delete family_expenses.'. $ex->getMessage(), 0, $ex);
                }
            }
        }

        try{
            //process to shift logs table to backup_logs table
            $db->query("insert into backup_logs select * from logs where expense_id in(".$expenseIds.")");
        } catch(Exception $ex){
            if($showMessagge){
                if($showMessagge){
                    throw new Exception('Failed to insert backup_logs.'. $ex->getMessage(), 0, $ex);
                }
            }
        }

        try{
            //delete from logs
            $db->query("delete from logs where expense_id in(".$expenseIds.")"); // delete from logs
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to delete logs.'. $ex->getMessage(), 0, $ex);
            }
        }

        // Group Settings backup and delete processs start

        try{
                //process to shift group_settings table to backup_group_settings table
                $db->query("insert into backup_group_settings select * from group_settings where expense_id in(".$expenseIds.")");
            }
        catch(Exception $ex){
                if($showMessagge){
                    throw new Exception('Failed to insert backup_group_settings.'. $ex->getMessage(), 0, $ex);
                }
            }

        try{
            //delete from table group_settings
            $db->query("delete from group_settings where expense_id in(".$expenseIds.")"); // delete from group_settings
        }
        catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to delete group_settings.'. $ex->getMessage(), 0, $ex);
            }
        }
        // Group Settings backup and delete end


        // Permission table backup and delete start

        try{
            //process to shift permissions table to backup_permissions table
            $db->query("insert into backup_permissions select * from permissions where expense_id in(".$expenseIds.")");
        }
        catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to insert backup_permissions.'. $ex->getMessage(), 0, $ex);
            }
        }
        try{
            //delete from table permissions
            $db->query("delete from permissions where expense_id in(".$expenseIds.")"); // delete from permissions
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to delete permissions.'. $ex->getMessage(), 0, $ex);
            }
        }
        // permissions backup and delete end

        try{
            //process to delete records from table expired_records
            $db->query("delete from expired_records where id in(".$expenseIds.")"); // delete from expired_records
        } catch(Exception $ex){
            if($showMessagge){
                throw new Exception('Failed to delete expired_records.'. $ex->getMessage(), 0, $ex);
            }
        }


    }

    /*
     * For deleting existing expenses in the backup table
     */
    private function deleteExistingExpense($backup_expenses, $db)
    {
        foreach($backup_expenses as $key=>$value){
            $backup_expenseIdList[] = $value["backup_expenses"]["id"];
            $expenseType = substr($value["backup_expenses"]["unique_url"], 0, 1);

            if($expenseType == "1" || $expenseType == "2"){
                if($expenseType == "2"){
                    $messExpenseList[] = $value["backup_expenses"]["id"];
                }
                $groupAndMessExpenseList[] = $value["backup_expenses"]["id"];
            } else if($expenseType == "3" ){
                $familyExpenseList[] = $value["backup_expenses"]["id"];
            }
        }
        $backup_expenseIds = implode(",", $backup_expenseIdList);
        //echo $backup_expenseIds;exit;

        try {
            //delete   records  from  backup_expenses  in case any previous  records avilable
            $db->query("delete from backup_expenses where id in(".$backup_expenseIds.")"); // delete from expenses
        } catch (Exception $ex) {

        }

        if(!empty($groupAndMessExpenseList)) {
            try {
                //delete   records  from  backup_group_expenses  in case any previous  records avilable
                $db->query("delete from backup_group_expenses where expense_id in(" . $backup_expenseIds . ")");
            } catch (Exception $ex) {

            }

            try {
                //delete   records  from  backup_participants  in case any previous  records avilable
                $db->query("delete from backup_participants where expense_id in(" . $backup_expenseIds . ")");
            } catch (Exception $ex) {

            }
        }

        //For  mess expense specially
        if(!empty($messExpenseList)) {
            try {
                //delete   records  from  backup_meals  in case any previous  records avilable
                $db->query("delete from backup_meals where expense_id in(" . $backup_expenseIds . ")");
            } catch (Exception $ex) {

            }
        }

        if(!empty($familyExpenseList)) {
            try {
                //delete   records  from  backup_family_expenses  in case any previous  records avilable
                $db->query("delete from backup_family_expenses where expense_id in(".$backup_expenseIds.")");
            } catch (Exception $ex) {

            }
        }

        try {
            //delete   records  from  backup_logs  in case any previous  records avilable
            $db->query("delete from backup_logs where expense_id in(" . $backup_expenseIds . ")");
        } catch (Exception $ex) {

        }

        try {
            //delete records from  backup_group_settings  in case any previous  records avilable
            $db->query("delete from backup_group_settings where expense_id in(" . $backup_expenseIds . ")");
        } catch (Exception $ex) { }

        try {
            //delete records from  backup_permissions in case any previous  records avilable
            $db->query("delete from backup_permissions where expense_id in(" . $backup_expenseIds . ")");
        } catch (Exception $ex) {

        }
    }

    /*
     * For archieve expenses from main database to archieve
     */

    public function archieveSingleExpense($unique_url, $showMessagge = 0)
    {
        $db = ConnectionManager::getDataSource('default');

        $backup_expense = $db->query("select * from backup_expenses where unique_url='".$unique_url."'");

        if (!empty($backup_expense)) {
            $archieve_db = ConnectionManager::getDataSource('archieve');
            $archieve_expense = $archieve_db->query("select * from expenses where unique_url='".$unique_url."'");
            if (!empty($archieve_expense)) {
                $this->deleteExistingArchievedExpense($archieve_expense , $archieve_db);
            }

            $expense_id = $backup_expense[0]["backup_expenses"]["id"];
            $expenseArray  =  $backup_expense;

            if(!empty($expenseArray)){
                try{
                    $this->insertIntoArchieve("backup_expenses","expenses", $expenseArray, $archieve_db);
                    $db->query("delete from backup_expenses where id =".$expense_id); // delete from expenses
                } catch (Exception $ex) {
                    if($showMessagge){
                        throw new Exception('Failed inserting expenses.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }
            try {
                $prod_group_expense = $db->query("select * from backup_group_expenses where expense_id=".$expense_id);
            } catch (Exception $ex) {
                $prod_group_expense = null;
            }

            if(!empty($prod_group_expense)){

                try {
                    $this->insertIntoArchieve("backup_group_expenses","group_expenses", $prod_group_expense, $archieve_db);
                    $db->query("delete from backup_group_expenses where expense_id =".$expense_id); // delete from expenses
                } catch (Exception $ex) {
                    if($showMessagge){
                        throw new Exception('Failed inserting group_expenses.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }

            //insert into participants from archieve
            try {
                $prod_participants = $db->query("select * from backup_participants where expense_id=".$expense_id);
            } catch (Exception $ex) {
                $prod_participants = null;
            }

            if(!empty($prod_participants)){
                try {
                    $this->insertIntoArchieve("backup_participants","participants", $prod_participants, $archieve_db);
                    $db->query("delete from backup_participants where expense_id =".$expense_id); // delete from expenses
                } catch (Exception $ex) {
                    if($showMessagge) {
                        throw new Exception('Failed inserting participants.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }

            //insert into meals from archieve
            try {
                $prod_meals = $db->query("select * from backup_meals where expense_id=".$expense_id);
            } catch (Exception $ex) {
                $prod_meals = null;
            }

            if(!empty($prod_meals)){
                try {
                    $this->insertIntoArchieve("backup_meals","meals", $prod_meals, $archieve_db);
                    $db->query("delete from backup_meals where expense_id =".$expense_id); // delete from expenses
                } catch (Exception $ex) {
                    if($showMessagge) {
                        throw new Exception('Failed inserting participants.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }

            //insert into family_expenses from archieve
            try {
                $prod_family_expenses = $db->query("select * from backup_family_expenses where expense_id=".$expense_id);
            } catch (Exception $ex) {

            }

            if(!empty($prod_family_expenses)){
                try {
                    $this->insertIntoArchieve("backup_family_expenses","family_expenses", $prod_family_expenses, $archieve_db);
                    $db->query("delete from backup_family_expenses where expense_id =".$expense_id); // delete from expenses
                } catch (Exception $ex) {
                    if($showMessagge) {
                        throw new Exception('Failed inserting family_expenses.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }

            //insert into logs from archieve
            try {
                $prod_logs = $db->query("select * from backup_logs where expense_id=".$expense_id);
            } catch (Exception $ex) {
                $prod_logs = null;
            }
            if(!empty($prod_logs)){
                try {
                    $this->insertIntoArchieve("backup_logs","logs", $prod_logs, $archieve_db);
                    $db->query("delete from backup_logs where expense_id =".$expense_id); // delete from expenses
                } catch (Exception $ex) {
                    if($showMessagge) {
                        throw new Exception('Failed inserting logs.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }

            //insert into group_settings from archieve
            try {
                $prod_group_settings = $db->query("select * from backup_group_settings where expense_id=".$expense_id);
            } catch (Exception $ex) {
                $prod_group_settings = null;
            }
            if(!empty($prod_group_settings)){
                try {
                    $this->insertIntoArchieve("backup_group_settings","group_settings", $prod_group_settings, $archieve_db);
                    $db->query("delete from backup_group_settings where expense_id =".$expense_id); // delete from backup_group_settings
                } catch (Exception $ex) {
                    if($showMessagge) {
                        throw new Exception('Failed inserting group_settings.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }

            //insert into permissions from archieve
            try {
                $prod_permissions = $db->query("select * from backup_permissions where expense_id=".$expense_id);
            } catch (Exception $ex) {
                $prod_permissions = null;
            }
            if(!empty($prod_permissions)){
                try {
                    $this->insertIntoArchieve("backup_permissions","permissions", $prod_permissions, $archieve_db);
                    $db->query("delete from backup_permissions where expense_id =".$expense_id); // delete from backup_permissions
                } catch (Exception $ex) {
                    if($showMessagge) {
                        throw new Exception('Failed inserting permissions.'. $ex->getMessage(), 0, $ex);
                    }
                }
            }


        } else {
            if($showMessagge){
            echo  "Expense not available in backup table";

            exit;
            }
        }
    }

    private function insertIntoArchieve($table, $insert_table, $data, $archieve_db)
    {

        foreach($data as $single_data) {
            $insertStr = "";
            $index = 0;
            foreach ($single_data[$table] as $key => $value) {
                if (strpos($value, "'") !== false) {
                    $value = addslashes($value);
                }
                $value = "'" . $value . "'";
                if ($index++ == 0) {
                    $insertStr = $value;
                } else {
                    $insertStr .= "," . $value;
                }
            }
            $archieve_db->query("insert into " . $insert_table . " values(" . $insertStr . ")");
        }
    }

    private function deleteExistingArchievedExpense($archieve_expense_id, $db)
    {
        foreach($archieve_expense_id as $key=>$value){
            $backup_expenseIdList[] = $value["expenses"]["id"];
            $expenseType = substr($value["expenses"]["unique_url"], 0, 1);

            if($expenseType == "1" || $expenseType == "2"){
                if($expenseType == "2"){
                    $messExpenseList[] = $value["expenses"]["id"];
                }
                $groupAndMessExpenseList[] = $value["expenses"]["id"];
            } else if($expenseType == "3" ){
                $familyExpenseList[] = $value["expenses"]["id"];
            }
        }
        $backup_expenseIds = implode(",", $backup_expenseIdList);

        //echo $backup_expenseIds;exit;

        try {
            //delete   records  from  backup_expenses  in case any previous  records avilable
            $db->query("delete from expenses where id in(".$backup_expenseIds.")"); // delete from expenses
        } catch (Exception $ex) {

        }

        if(!empty($groupAndMessExpenseList)) {
            try {
                //delete   records  from  backup_group_expenses  in case any previous  records avilable
                $db->query("delete from group_expenses where expense_id in(" . $backup_expenseIds . ")");
            } catch (Exception $ex) {

            }

            try {
                //delete   records  from  backup_participants  in case any previous  records avilable
                $db->query("delete from participants where expense_id in(" . $backup_expenseIds . ")");
            } catch (Exception $ex) {

            }
        }

        //For  mess expense specially
        if(!empty($messExpenseList)) {
            try {
                //delete   records  from  backup_meals  in case any previous  records avilable
                $db->query("delete from meals where expense_id in(" . $backup_expenseIds . ")");
            } catch (Exception $ex) {

            }
        }

        if(!empty($familyExpenseList)) {
            try {
                //delete   records  from  backup_family_expenses  in case any previous  records avilable
                $db->query("delete from family_expenses where expense_id in(".$backup_expenseIds.")");
            } catch (Exception $ex) {

            }
        }

        try {
            //delete   records  from  backup_logs  in case any previous  records avilable
            $db->query("delete from logs where expense_id in(" . $backup_expenseIds . ")");
        } catch (Exception $ex) {}

        try {
            //delete records from archive group_settings in case any previous records avilable
            $db->query("delete from group_settings where expense_id in(" . $backup_expenseIds . ")");
        } catch (Exception $ex) {

        }

        try {
            //delete records from archive permissions in case any previous records avilable
            $db->query("delete from permissions where expense_id in(" . $backup_expenseIds . ")");
        } catch (Exception $ex) {

        }

    }

    /*
  * For making automatic data backup to the archive database and delete from backup tables
  */
    public function processArchiveAndRemove($backupRecordList,$showMessagge) {
        $db = ConnectionManager::getDataSource('default');
        $archiveDb = ConnectionManager::getDataSource('archieve');

        $db->begin();
        $archiveDb->begin();

        try {
            foreach ($backupRecordList as $value) {
                $backup_record = $value['backup_expenses'];
                if (empty($backup_record['unique_url'])) {
                    throw new Exception('unique_url missing');
                }

                $this->archieveSingleExpense($backup_record['unique_url'], $showMessagge);
            }

            $db->commit();
            $archiveDb->commit();

            if ($showMessagge) {
                echo "Archive completed successfully";
            }

        } catch (Exception $ex) {
            $db->rollback();
            $archiveDb->rollback();
            $message = "Archive process failed. Rolled back. Error: " . $ex->getMessage();
            $this->emailSend($message,"Archive process failed");
            echo $message;

        }
    }

    /*
 * For making automatic data removed from archive database
 */
    public function processRemoveArchive($archiveRecordList,$showMessagge) {

        $archiveDb = ConnectionManager::getDataSource('archieve');

        $archiveDb->begin();

        try {
            $this->deleteExistingArchievedExpense($archiveRecordList, $archiveDb);
            $archiveDb->commit();

            if ($showMessagge) {
                echo "Record removed from the archive database successfully.";
            }

        } catch (Exception $ex) {
            $archiveDb->rollback();

            if ($showMessagge) {
                echo "Record removed from the archive failed. Rolled back. Error: " . $ex->getMessage();
            }
        }
    }


    private function emailSend($message,$subject)
    {
        App::uses('ComponentCollection', 'Controller');
        App::uses('NotificationComponent', 'Controller/Component');

        $this->Notification = new NotificationComponent(new ComponentCollection());
        $this->Notification->sendEmail(array(
            "email_from" => SUPPORT_EMAIL,
            "email_to" => REPORTING_EMAIL,
            "title" => $message,
            "template" => "backup_archive_process_error",
            "subject" => $subject,
        ));

    }
}