<?php
include_once $path_to_root."/includes/db/manufacturing_db.inc";
function backup_bom($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_bom_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 can_insert_bom($row) {
    global $unknown;
    $error='';
    $data=array();
    $data['parent'] =$row[0];
    $data['component'] =$row[1];
    $data['quantity'] =$row[2];
    $data['loc_code'] =$row[3];
    $data['workcentre_added'] =$row[4];


    $check_empty =array('Parent Code'=>$row[0],'Item code'=>$row[1],'Quantity'=>$row[2],'Location code'=>$row[3],'Work center'=>$row[4]);
    if($error=check_empty($check_empty)){
        array_push($row, $error);
        array_push($unknown, $row);
        return false;
    }

    if(!GetSingleValue('stock_master','stock_id',array('mb_flag'=>['F','','!='],'stock_id'=>$data['component']))){
        array_push($row, 'Component Does not exists');
        array_push($unknown, $row);
        return false;
    }

    if (is_component_already_on_bom($data['component'], $data['workcentre_added'],
               $data['loc_code'], $data['parent'])){
        array_push($row, 'This component is already in bom');
        array_push($unknown, $row);
        return false;
    }
    return $data;
}

function import_bom($file_name){
    global $unknown  ;
    $file =fopen($file_name, 'r');
    $counter =-1;
    $result='';
    $for_next =array();
    $unformatted_csv=false;
    while ($row=fgetcsv($file)) {

        if($counter!=-1){
            $counter++;
            if($counter<=$_POST["max_rows"]){
                begin_transaction();
                $data= can_insert_bom($row);   
                if(!$data){
                    cancel_transaction();
                }else{
                    add_bom($data['parent'], $data['component'], $data['workcentre_added'],
                    $data['loc_code'], $data['quantity']);  

                    commit_transaction();
                }
            }else{
                 array_push($for_next,$row);
            }
        }else {
            $check_array =array('Parent code','Item Code',"Quantity","location code",'Work center');
            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;
        $remaining_rows =sizeof($for_next);
        foreach ($for_next as $value) {
            array_push($unknown, $value);
        }   
        if($error_rows ==0 && $remaining_rows ==0){
            display_notification($counter-$error_rows-$remaining_rows.' rows are inserted');
        }else{  
            put_csv($unknown);
            display_error($counter-$error_rows-$remaining_rows.' rows are inserted <br> '.$error_rows.' row are having errors while uploading <br> '.$remaining_rows.' rows are yet to be added <br><a href="'.$_SERVER['PHP_SELF'].'?dl='.$_FILES['file']['name'].'" > Click Here </a> Download the CSV File and reupload it again after correcting the errors if any to import the rest of the items');
        }
    }

}


function export_bom(){
    global $path_to_root;
    $export_boms =array();
    $export_boms []=array('Parent code','Item Code',"Quantity","location code",'Work center');

    $boms =GetALL('bom');
    if(!empty($boms)){
        foreach ($boms as  $bom) {
           $export_boms []=array($bom['parent'],$bom['component'],$bom['quantity'],$bom['loc_code'],$bom['workcentre_added']);
        }
        export_csv($export_boms,'bom');
    }
}

?>
