<?php
/*======================================================\
|                      FrontKanban                      |
|-------------------------------------------------------|
|  Creator: Phương                                      |
|  Date :   01-Dec-2017                                 |
|  Description: Frontaccounting Project Management Ext  |
|  Free software under GNU GPL                          |
|                                                       |
\======================================================*/

function write_project($id=false, $name, $desc, $type, $created_date, $closed_date, $begin_date, $end_date, $owner, $closed=false, $proj_amt = 0, $proj_ref = '',$gl_account = 0,$dimension = 0) {
    if(!$id)
	    $sql = "INSERT INTO ".TB_PREF."projects VALUES("
	    .db_escape($dimension).", "
	    .db_escape($name).", "
	    .db_escape($desc).", "
	    .db_escape($type).", "
	    .db_escape($proj_amt).", "
	    .db_escape($proj_ref).", '"
	    .date2sql($created_date)."', '"
	    .date2sql($closed_date)."', '"
	    .date2sql($begin_date)."', '"
	    .date2sql($end_date)."', "
	    .db_escape($owner).", "
	    .db_escape($gl_account).", "
	    .db_escape($closed)
	    .")";
	else
	    $sql = "UPDATE ".TB_PREF."projects SET 
	     
	    proj_name = ".db_escape($name).", 
	    proj_description = ".db_escape($desc).", 
	    proj_type = ".db_escape($type).", 
	    created_date = '".date2sql($created_date)."', 
	    closed_date = '".date2sql($closed_date)."', 
	    begin_date = '".date2sql($begin_date)."', 
	    end_date = '".date2sql($end_date)."', 
	    owner_id = ".db_escape($owner).", 
	    closed = ".db_escape($closed).", 
	    proj_amt = ".db_escape($proj_amt).",  
	    gl_account = ".db_escape($gl_account)." ,
	    proj_ref = ".db_escape($proj_ref)." 
	    WHERE proj_id = ".db_escape($id);

	db_query($sql, _('could not write project data'));
}

function write_project_target($id=false,$proj_id,$per_Group,$proj_amt = 0,$end_date = '') {
    if(!$id)
	    $sql = "INSERT INTO ".TB_PREF."project_targets VALUES("
	    .db_escape($id).", "
	    .db_escape($proj_id).", "
	    .db_escape($per_Group).", "
	    .db_escape($proj_amt).", '"
	    .date2sql($end_date)."'"
	    .")";
	else
	    $sql = "UPDATE ".TB_PREF."project_targets SET 
	     
	    proj_id = ".db_escape($proj_id).", 
	    per_Group = ".db_escape($per_Group).", 
	    proj_amt = ".db_escape($proj_amt).", 
	    end_date = '".date2sql($end_date)."' 
	    WHERE id = ".db_escape($id);

	db_query($sql, _('could not write project target data'));
}

//--------------------------------------------------------------------------

function get_targets($id = false, $proj_ID = false, $per_Group = false) {
	
	$sql = "SELECT t.id,p.proj_name,g.name,t.proj_amt,t.end_date FROM ".TB_PREF."project_targets t "
                . " inner join ".TB_PREF."projects p on p.proj_id=t.proj_id "
                . " inner join ".TB_PREF."per_groups g on g.id=t.per_Group ";
	$where = array();
	
	if($id)
		$where[] = "id=".db_escape($id);
        
        if($proj_ID)
		$where[] = "t.proj_id=".db_escape($proj_ID);
        
        if($per_Group)
		$where[] = "per_Group=".db_escape($per_Group);
		
	if(count($where))
		$sql .= ' WHERE '.implode(' AND ', $where);
	else
		$sql .= ' WHERE 1';

	$result = db_query($sql, _('Could not get project data.'));
	
	if($id)
		return db_fetch($result);
	else
		return $sql;
}

function get_projects($id = false, $all = false, $type = false, $exclude = Array()) {
	
	$sql = "SELECT * FROM ".TB_PREF."projects";
	$where = array();
	
	if(!$all && !$id)
		$where[] = "!closed";
	if($id)
		$where[] = "proj_id=".db_escape($id);
	if($type === 0 || $type === 1)
		$where[] = "proj_type = ".db_escape($type);
        
        if(array_count_values($exclude))
		$where[] = "proj_id not in (".  implode(',',$exclude).")";
		
	if(count($where))
		$sql .= ' WHERE '.implode(' AND ', $where);
	else
		$sql .= ' WHERE 1';

	$result = db_query($sql, _('Could not get project data.'));
	
	if($id)
		return db_fetch($result);
	else
		return $sql;
}

function get_project_name($proj_id)
{
	$sql = "SELECT proj_name "
                . " FROM ".TB_PREF."projects WHERE proj_id=".db_escape($proj_id);

	$result = db_query($sql, "could not get project");

	$row = db_fetch_row($result);

	return $row[0];
}

function get_project_target($id) {
	
	$sql = "SELECT * FROM ".TB_PREF."project_targets where id=".  db_escape($id);
	$result = db_query($sql, _('Could not get project target data.'));
	
		return db_fetch($result);
}

//--------------------------------------------------------------------------

function delete_project($id) {
    $sql = "DELETE FROM ".TB_PREF."projects WHERE proj_id = ".db_escape($id);
    db_query($sql, _('could not delete project data'));
}

//--------------------------------------------------------------------------

function get_last_project() {
	$sql = "SELECT MAX(proj_id) FROM ".TB_PREF."projects WHERE !closed";
	$result = db_query($sql, _('could not get project data.'));
	 return db_fetch($result);
}

function get_project_by_ref($ref) {
	$sql = "SELECT * FROM ".TB_PREF."projects WHERE LOWER(proj_ref)=LOWER(".  db_escape($ref) .") ";
	$result = db_query($sql, _('could not get project data.'));
	 return db_fetch($result);
}

//--------------------------------------------------------------------------

function add_project_data_file($id) {
    global $path_to_root;

    if(!file_exists(company_path().'/kanban_data'))
        mkdir(company_path().'/kanban_data');

	$content = "{}";
    $fp = fopen(company_path().'/kanban_data/'.$id, "wb");
    fwrite($fp, $content);
    fclose($fp);

    if(file_exists(company_path().'/kanban_data/'.$id))
        return true;
    else
        return false;
}
