<?php

function can_insert_chart_of_accounts($row) {
    global $unknown;
    $error='';
    $data=array();
    $data['code'] =$row[0];
    $data['parent_code'] =$row[1];
    $data['name'] =$row[2];
    $data['parent_type'] =$row[3];
    $data['type'] =$row[4];
    $data['class_name'] =$row[5];
    $data['class_type'] =$row[6];


    $class_types =array('CL_NONE','CL_ASSETS','CL_LIABILITIES','CL_EQUITY','CL_INCOME','CL_COGS','CL_EXPENSE');

    $check_empty =array('Code'=>$row[0],'Name'=>$row[2],'Type / Group'=>$row[4],'Class Name'=>$row[5],'Class Type'=>$row[6]);
    if($error=check_empty($check_empty)){
        array_push($row, $error);
        array_push($unknown, $row);
        return false;
    }


    if(!in_array($data['class_type'], $class_types)){
        array_push($row,'Undefined Class Format');
        array_push($unknown, $row);
        return false;
    }

    return $data;
}

function can_insert_chart_of_accounts_ids($row) {
    global $unknown;
    $error='';
    $data=array();
    $data['code'] =$row[0];
    $data['parent_code'] =$row[1];
    $data['name'] =$row[2];
    $data['class_type'] =$row[3];


    $class_types =array('CL_NONE','CL_ASSETS','CL_LIABILITIES','CL_EQUITY','CL_INCOME','CL_COGS','CL_EXPENSE','TYPE','CHILD TYPE',"");

    $check_empty =array('Code'=>$row[0],'Name'=>$row[2]);
    if($error=check_empty($check_empty)){
        array_push($row, $error);
        array_push($unknown, $row);
        return false;
    }


    if(!in_array($data['class_type'], $class_types)){
        array_push($row,'Undefined Class Format');
        array_push($unknown, $row);
        return false;
    }
    if($data['class_type'] =="TYPE" || $data['class_type'] =="CHILD TYPE"){
        if($data['parent_code']==""){
            array_push($row,'Parent code cannot be empty');
            array_push($unknown, $row);
            return false;
        }
    }
    return $data;
}


function backup_chart_of_accounts($fname="chart_of_accounts"){
    
    global $db_connections; 

    $prefix = $db_connections[$_SESSION["wa_current_user"]->company]["tbpref"]; 

    $tables = array('sys_prefs','chart_class','chart_master','chart_types');
    // $result = db_query('SHOW TABLES');
    // $end_char_count = strlen($prefix);
    // while($row = db_fetch_row($result)) {
    //     if(strpos($row[0], $prefix.'kv_empl') !== false)
    //         $tables[] = substr($row[0], $end_char_count);
    // }

    //print_r($tables);
    //exit;
    $return = "# MySQL dump of database '".$db_connections[$_SESSION["wa_current_user"]->company]["name"]."' on host '".$db_connections[$_SESSION["wa_current_user"]->company]["host"]."'
# Backup Date and Time: ".date('Y-m-d h:i a')."
# Module name : Import export
# Module Author : Kvvaradha
# http://www.kvcodes.com
# Company: ".$db_connections[$_SESSION["wa_current_user"]->company]["name"]."
# User : ".$_SESSION["wa_current_user"]->name."
# Compatibility: ".get_company_pref('version_id')."

SET SQL_MODE='';

# Table Backups ".$db_connections[$_SESSION["wa_current_user"]->company]["dbname"]."

#------------------------------------------------------------------------------------
    ";

    //cycle through
    foreach($tables as $table)  {

        $table_exist = db_query("SHOW TABLES LIKE '".$prefix.$table."'");
        $tbl_ext = db_num_rows($table_exist) > 0;
        
        if($tbl_ext){
            $result = db_query('SELECT * FROM '.$prefix.$table);
            $num_fields = db_num_fields($result);
            if($table !='sys_prefs')
                $return.= "### Structure of table `".$table."` ### \n\n DROP TABLE IF EXISTS 0_".$table.";";

            $row2 = db_fetch_row(db_query('SHOW CREATE TABLE '.$prefix.$table));
            if($table !='sys_prefs')
                $return.= "\n\n".$row2[1].";\n\n";
            $kv = 0; 
            if($table !='sys_prefs'){
                for ($i = 0; $i < $num_fields; $i++) {
                    while($row = db_fetch_row($result)){
                        if($kv == 0)
                            $return .= 'INSERT INTO 0_'.$table.' VALUES(';
                        else
                            $return .= "\n (";
                        for($j=0; $j < $num_fields; $j++) {
                            $row[$j] = addslashes($row[$j]);
                            $row[$j] = preg_replace("#\n#", "\\n", $row[$j]);
                            if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                            if ($j < ($num_fields-1)) { $return.= ','; }
                        }
                        if($kv == 100){
                            $return .= "); \n";
                            $kv = 0;
                        }
                        else{
                            $return .= "),";
                            $kv++; 
                        }                    
                    }
                }
            }else{
                for ($i = 0; $i < $num_fields; $i++) {
                    $sys_prefs =array_keys(get_system_gls());
                    while($row = db_fetch_row($result)){
                        if(in_array($row[0], $sys_prefs))
                            $return .= "UPDATE  0_".$table." SET value='".$row[4]."' WHERE name='".$row[0]."' ;\n";                    
                    }
                }
            }
            if(substr($return , -1) == ',')
                $return = substr($return, 0,-1).';';
            $return.="\n\n # -------------------------------------------------------------------------------------\n\n";
        }        
    }

    $return = str_replace($prefix, '0_', $return);

    //save file
    $handle = fopen(dirname(dirname(__FILE__)).'/backups/'.$fname.'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

function import_coa_sql($fname="chart_of_accounts"){
    global $db_connections; 

   // $prefix = $db_connections[$_SESSION["wa_current_user"]->company]["tbpref"];
    $conn = $db_connections[user_company()];
    
    if (db_import(dirname(dirname(__FILE__)).'/backups/'.$fname.'.sql', $conn)){
        // unlink(dirname(dirname(__FILE__)).'/backups/chart_of_accounts.sql');
        display_notification(_("Chart of accouts restored successfully."));
    }
}


function import_chart_of_accounts($file_name){
    global $unknown ;
    $chart_of_accounts =array();
    $file =fopen($file_name, 'r');
    $counter =-1;
    $result='';
    $for_next =array();
    $unformatted_csv=false;

    //backup the previous chart of accouncts datas
    if(!file_exists(dirname(dirname(__FILE__)).'/backups/chart_of_accounts.sql')){
        backup_chart_of_accounts();
    }
    while ($row=fgetcsv($file)) {

        if($counter!=-1){
            $counter++;
            if(true){

                $data= can_insert_chart_of_accounts($row);   
                if(!$data){
                    
                }else{
                    $chart_of_accounts[]=$data;
                   
                }
            }else{
                 array_push($for_next,$row);
            }
        }else {
            $check_array =array('Code','Parent Code','Name','Parent Type / Group','Type / Group','Class Name',
'Class Type
[CL_NONE,
CL_ASSETS,
CL_LIABILITIES,
CL_EQUITY,
CL_INCOME,
CL_COGS,
CL_EXPENSE]');
            if(array_diff($check_array,$row)){
                display_error('Cant import ! Unformatted CSV file ! You can download and edit sample file');
                $unformatted_csv=true;
                break;
            }
            array_push($row,'Memo');
            $unknown=array();
            array_push($unknown,$row);
            $counter++;
        } 
    }
    if(!$unformatted_csv){

        $error_rows=sizeof($unknown)-1;

        if($error_rows ==0 ){
            if(!empty($chart_of_accounts))
            $_SESSION['chart_of_accounts']=$chart_of_accounts;  
        }else{   
            put_csv($unknown);
            display_error('Some errors are occurs in uploaded csv file!.<br><a href="'.$_SERVER['PHP_SELF'].'?dl='.$_FILES['file']['name'].'" > Click Here </a> Download the CSV File and correct the erros in your csv file');
        }
    }

}

function import_chart_of_accounts_ids($file_name){
    global $unknown ;
    $chart_of_accounts =array();
    $chart_classes =array();
    $chart_types =array();
    $file =fopen($file_name, 'r');
    $counter =-1;
    $result='';
    $for_next =array();
    $unformatted_csv=false;

    $current_class_id="";
    $current_class_name="";

    $current_class_type_id="";
    $current_class_type_name="";

    $current_type_id="";
    $current_type_name="";
    
    $class_types =array(0=>'CL_NONE',1=>'CL_ASSETS',2=>'CL_LIABILITIES',3=>'CL_EQUITY',4=>'CL_INCOME',5=>'CL_COGS',6=>'CL_EXPENSE');

    //backup the previous chart of accouncts datas
    if(!file_exists(dirname(dirname(__FILE__)).'/backups/chart_of_accounts.sql')){
        backup_chart_of_accounts();
    }
    while ($row=fgetcsv($file)) {

        if($counter!=-1){
            $counter++;
            if(true){

                $data= can_insert_chart_of_accounts_ids($row);   
                if(!$data){
                    
                }else{

                    if($data['class_type'] =="TYPE"){
                        // if(isset($chart_types[$data['code']])){
                        //     array_push($row, "Type code is already exsits");
                        //     array_push($unknown, $row);
                        //     break;
                        // }

                        $data['parent']="";
                        $data['class_id']=$data['parent_code'];
                        $chart_types[$data['code']]=$data;

                        $current_type_id =$data['code'];
                        $current_type_name =$data['name'];
                    }elseif($data['class_type'] =="CHILD TYPE"){

                        // if(isset($chart_types[$data['code']])){
                        //     array_push($row, "Type code is already exsits");
                        //     array_push($unknown, $row);
                        //     break;
                        // }

                        $data['parent']=$data['parent_code'];
                        $data['class_id']=$current_class_id;
                        $chart_types[$data['code']]=$data;
                        
                        $current_type_id =$data['code'];
                        $current_type_name =$data['name'];

                       

                    }elseif($data['class_type'] ==""){

                        if(isset($chart_of_accounts[$data['code']])){
                            array_push($row, "Account code is already exsits");
                            array_push($unknown, $row);
                            break;
                        }
                        $data['type']=$current_type_name;
                        $data['type_id']=$current_type_id;

                        $data['class_id']=$current_class_id;
                        $data['class_name']=$current_class_name;
                        $data['class_type_id']=$current_class_type_id;
                        $data['class_type']=$current_class_type_name;

                        $chart_of_accounts[$data['code']]=$data;
                    }else{
                        //  if(isset($chart_classes[$data['code']])){
                        //     array_push($row, "Class code is already exsits");
                        //     array_push($unknown, $row);
                        //     break;
                        // }
                        $chart_classes[]=$data;
                        $current_class_id=$data['code'];
                        $current_class_name=$data['name'];
                        $current_class_type_name=$data['class_type'];
                        $current_class_type_id=is_numeric($data['class_type'])?$data['class_type']: constant($data['class_type']);
                    }

                    
                   
                }
            }else{
                 array_push($for_next,$row);
            }
        }else {
            $check_array =array('Code','Parent Code','Name',
'Class Type
[CL_NONE,
CL_ASSETS,
CL_LIABILITIES,
CL_EQUITY,
CL_INCOME,
CL_COGS,
CL_EXPENSE]');
            if(array_diff($check_array,$row)){
                display_error('Cant import ! Unformatted CSV file ! You can download and edit sample file');
                $unformatted_csv=true;
                break;

            }
            array_push($row,'Memo');
            $unknown=array();
            array_push($unknown,$row);
            $counter++;
        } 
    }
    if(!$unformatted_csv){

        $error_rows=sizeof($unknown)-1;

        if($error_rows ==0 ){
            if(!empty($chart_of_accounts)){
                $_SESSION['chart_of_accounts']=$chart_of_accounts;  
                $_SESSION['chart_classes']=$chart_classes;  
                $_SESSION['chart_types']=$chart_types;  
            }
        }else{   
            put_csv($unknown);
            display_error('Some errors are occurs in uploaded csv file!.<br><a href="'.$_SERVER['PHP_SELF'].'?dl='.$_FILES['file']['name'].'" > Click Here </a> Download the CSV File and correct the erros in your csv file');
        }
    }

}


function import_coa(){
    $error =0;
    $system_gls =get_system_gls();
    $assigned_system_gls=array();
    $dublicate_system_gls =array();
    foreach ($_SESSION['chart_of_accounts'] as $key => $value) {
        if($_POST[$key.'_system_gl'] !=-1){
            if(in_array($_POST[$key.'_system_gl'], $assigned_system_gls)){
                $dublicate_system_gls[$key]= $_POST[$key.'_system_gl'];
            }else{
                $assigned_system_gls[$key]= $_POST[$key.'_system_gl'];
                $_SESSION['chart_of_accounts'][$key]['system_gl']= $system_gls[$_POST[$key.'_system_gl']];
            }
        }

    }
    $_SESSION['dublicate_system_gls']=$dublicate_system_gls;
    if($result=array_diff(array_keys($system_gls),array_values($assigned_system_gls))){
        $error =" Following system gl accounts are must be assigned."."<br>";
        foreach ($result as $key => $value) {
            $error .=$system_gls[$value]."<br>";
        }
        display_error($error);
    }else{
        begin_transaction();
        $sql ="TRUNCATE TABLE ".TB_PREF."chart_class;";

        db_query($sql);
        $sql ="TRUNCATE TABLE ".TB_PREF."chart_master;";

        db_query($sql);
        $sql ="TRUNCATE TABLE ".TB_PREF."chart_types";

        db_query($sql);
        foreach ($_SESSION['chart_of_accounts'] as $key => $value) {
            $class_id =get_chart_class_id($value['class_name'],$value['class_type']);
            $type_id =get_chart_type_id($value['type'],$class_id,$value['parent_type']);
            Insert('chart_master',array('account_code'=>$value['code'],'account_code2'=>$value['parent_code'],'account_name'=>$value['name'],'account_type'=>$type_id));
            if(isset($value['system_gl'])){
                Update('sys_prefs',array('name'=>$value['system_gl']),array('value'=>$value['code']));
            }
        }
        commit_transaction();
        unset($_SESSION['chart_of_accounts']);
        unset($_SESSION['dublicate_system_gls']);
        display_notification("Chart of account is imported successfully.");
    }


}

function import_coa_ids(){
    $error =0;
    $system_gls =get_system_gls();
    $assigned_system_gls=array();
    $dublicate_system_gls =array();
    if(file_exists(dirname(dirname(__FILE__)).'/backups/coa_live_backup.sql')){
        unlink(dirname(dirname(__FILE__)).'/backups/coa_live_backup.sql');
    }
    backup_chart_of_accounts('coa_live_backup');
    foreach ($_SESSION['chart_of_accounts'] as $key => $value) {
        if($_POST[$key.'_system_gl'] !=-1){
            if(in_array($_POST[$key.'_system_gl'], $assigned_system_gls)){
                $dublicate_system_gls[$key]= $_POST[$key.'_system_gl'];
            }else{
                $assigned_system_gls[$key]= $_POST[$key.'_system_gl'];
                $_SESSION['chart_of_accounts'][$key]['system_gl']= $system_gls[$_POST[$key.'_system_gl']];
            }
        }

    }
    $_SESSION['dublicate_system_gls']=$dublicate_system_gls;
    if($result=array_diff(array_keys($system_gls),array_values($assigned_system_gls))){
        $error =" Following system gl accounts are must be assigned."."<br>";
        foreach ($result as $key => $value) {
            $error .=$system_gls[$value]."<br>";
        }
        display_error($error);
    }else{
        $error=0;
        begin_transaction();
        $sql ="TRUNCATE TABLE ".TB_PREF."chart_class;";

        db_query($sql);
        $sql ="TRUNCATE TABLE ".TB_PREF."chart_master;";

        db_query($sql);
        $sql ="TRUNCATE TABLE ".TB_PREF."chart_types";

        db_query($sql);
        foreach ($_SESSION['chart_classes'] as $chart_class) {

            if(GetSingleValue('chart_classes','cid',array('cid'=>$chart_class['code'])))
                continue;

            Insert('chart_class',array('cid'=>$chart_class['code'],"class_name"=>$chart_class['name'],'ctype'=>is_numeric($chart_class['class_type'])?$chart_class['class_type']: constant($chart_class['class_type'])));
        }

        foreach ($_SESSION['chart_types'] as $chart_type) {

            if(GetSingleValue('chart_types','id',array('id'=>$chart_type['code'])))
                continue;
            
            if($chart_type['parent']){
                if(!GetSingleValue('chart_types','id',array('id'=>$chart_type['parent']))){
                    display_error("Parent code not defined at type ".$chart_type['name']);
                    $error=1;
                    break;
                }

            }
            if(!GetSingleValue('chart_class','cid',array('cid'=>$chart_type['class_id']))){
                display_error("Parent code not defined at type ".$chart_type['name']);
                $error=1;
                break;
            }

            Insert('chart_types',array('id'=>$chart_type['code'],"name"=>$chart_type['name'],'parent'=>$chart_type['parent'],'class_id'=>$chart_type['class_id']));

        }

        if(!$error){
            foreach ($_SESSION['chart_of_accounts'] as $key => $value) {
               if(!GetSingleValue('chart_types','id',array('id'=>$value['type_id']))){
                    display_error("Unable to import Chart Of Accounts");
                    $error=1;
                    break;
                }
                Insert('chart_master',array('account_code'=>$value['code'],'account_code2'=>$value['parent_code'],'account_name'=>$value['name'],'account_type'=>$value['type_id']));
                if(isset($value['system_gl'])){
                    Update('sys_prefs',array('name'=>$value['system_gl']),array('value'=>$value['code']));
                }
            }
        }

        unset($_SESSION['chart_of_accounts']);
        unset($_SESSION['chart_classes']);
        unset($_SESSION['chart_types']);
        unset($_SESSION['dublicate_system_gls']);

        if($error){
            cancel_transaction();
            import_coa_sql('coa_live_backup');
        }else{
            commit_transaction();
            display_notification("Chart of account is imported successfully.");
        }
    }
}



function export_chart_of_accounts(){
     global $path_to_root;
   $export_chart_of_accounts =array();
   $export_chart_of_accounts []=array('Code','Parent Code','Name','Parent Type / Group','Type / Group','Class Name',
'Class Type
[CL_NONE,
CL_ASSETS,
CL_LIABILITIES,
CL_EQUITY,
CL_INCOME,
CL_COGS,
CL_EXPENSE]');

   $class_types =array(0=>'CL_NONE',1=>'CL_ASSETS',2=>'CL_LIABILITIES',3=>'CL_EQUITY',4=>'CL_INCOME',5=>'CL_COGS',6=>'CL_EXPENSE');

   $chart_of_accounts = GetDataJoin('chart_master AS chart_master', array( 
                    0 => array('join' => 'INNER', 'table_name' => 'chart_types AS chart_types', 'conditions' => '`chart_master`.`account_type` = `chart_types`.`id`'),
                    1=> array('join' => 'LEFT', 'table_name' => 'chart_types AS chart_types2', 'conditions' => '`chart_types`.`parent` = `chart_types2`.`id`'),

                    2=> array('join' => 'INNER', 'table_name' => 'chart_class AS chart_class', 'conditions' => '`chart_types`.`class_id` = `chart_class`.`cid`'),
                ), 
            array('chart_master.account_code','chart_master.account_code2','chart_master.account_name','chart_types.name','chart_types2.name AS parent_type_name','chart_class.class_name','chart_class.ctype'));
    if(!empty($chart_of_accounts)){
        foreach ($chart_of_accounts as  $chart_of_account) {
           $export_chart_of_accounts []=array($chart_of_account['account_code'],$chart_of_account['account_code2'],$chart_of_account['account_name'],$chart_of_account['parent_type_name'],$chart_of_account['name'],$chart_of_account['class_name'],$class_types[$chart_of_account['ctype']]);
        }

        export_csv($export_chart_of_accounts,'chart_of_accounts');

    }
}


function export_coa_with_ids(){
     global $path_to_root;
   $export_chart_of_accounts =array();
   $export_chart_of_accounts []=array('Code','Parent Code','Name',
'Class Type
[CL_NONE,
CL_ASSETS,
CL_LIABILITIES,
CL_EQUITY,
CL_INCOME,
CL_COGS,
CL_EXPENSE]');

   $class_types =array(0=>'CL_NONE',1=>'CL_ASSETS',2=>'CL_LIABILITIES',3=>'CL_EQUITY',4=>'CL_INCOME',5=>'CL_COGS',6=>'CL_EXPENSE');


    $chart_classes =GetAll('chart_class');

    if(!empty($chart_classes)){
        foreach ($chart_classes as  $chart_class) {
           $export_chart_of_accounts []=array($chart_class['cid'],"",$chart_class['class_name'],$class_types[$chart_class['ctype']]);

           $chart_types =GetAll('chart_types',array('class_id'=>$chart_class['cid']));
           foreach ($chart_types as $chart_type) {
                if(!$chart_type['parent'] ||$chart_type['parent'] ==-1){
                    $export_chart_of_accounts []=array($chart_type['id'],$chart_type['class_id'],$chart_type['name'],'TYPE');
                }else{
                    $export_chart_of_accounts []=array($chart_type['id'],$chart_type['parent'],$chart_type['name'],'CHILD TYPE');
                }
           $chart_of_accounts =GetAll('chart_master',array('account_type'=>$chart_type['id']));
           
           foreach ($chart_of_accounts as $chart_of_account) {
               $export_chart_of_accounts []=array($chart_of_account['account_code'],$chart_of_account['account_code2'],$chart_of_account['account_name'],"");
            }     

           }
        }

        export_csv($export_chart_of_accounts,'chart_of_accounts');

    }
}


?>
