<?php
/*-------------------------- select all section from org ------------------------------*/
$section = mysql_query("SELECT  * FROM orgunit WHERE parentunit = 1 ");
$etl_head = mysql_query("SELECT  * FROM orgunit WHERE parentunit = 0 AND  unittypeid = 5 AND isdeleted <> 1 ORDER BY unittypeid ASC ");
$dept = mysql_query("SELECT  * FROM orgunit WHERE parentunit = 0 AND  unittypeid = 1 AND isdeleted <> 1 ORDER BY unittypeid ASC ");
$branch = mysql_query("SELECT  * FROM orgunit WHERE parentunit = 0  AND unittypeid = 2 AND isdeleted <> 1 ORDER BY unittypeid ASC ");
/*------------ list the document by section -- org ------------------*/
function numDocIN($orgunitid){
	$sql = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  m.receive_status = 1 AND m.receive_person IS NULL AND org.orgunitid = $orgunitid GROUP BY org.orgunitid ";
	$result = mysql_query($sql);
	return $result;
}
function numDocIN_org($orgunitid){
	$sql = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  m.receive_status = 1 AND isdeleted=0 AND inactive=0 AND org.orgunitid = $orgunitid AND d.m_step = 'public'  GROUP BY org.orgunitid ";
	$result = mysql_query($sql);
	return $result;
}
/*-------------------------------- list doc of ETL HEAD ----------------*/
function numDocIN_etl($orgunitid){
	$sql = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  m.receive_status = 1 AND m.receive_person IS NULL AND isdeleted=0 AND inactive=0 AND org.orgunitid = $orgunitid AND d.m_step = 'secretary'  GROUP BY org.orgunitid ";
	$result = mysql_query($sql);
	return $result;
}
/* -------------- user type as counter ----------------*/
function numDocIN_counter($orgunitid){
	$sql = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  m.receive_status = 1 AND m.receive_person IS NULL AND isdeleted=0 AND inactive=0 AND org.orgunitid = $orgunitid AND d.m_step  IN ('counter', 'counter_return')  GROUP BY org.orgunitid ";
	$result = mysql_query($sql);
	return $result;
}
/* -------------- user type as counter ----------------*/
function numDocIN_secure($orgunitid){
	$sql = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  m.receive_status = 1 AND m.receive_person IS NULL AND isdeleted=0 AND inactive=0 AND org.orgunitid = $orgunitid  AND d.m_step = 'secretary'  GROUP BY org.orgunitid ";
	$result = mysql_query($sql);
	return $result;
}
/*------------ list the document by section -- org ------------------*/
function numDocIN_position($position_id){
	$sql = "SELECT COUNT(m.m_id) as num , d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN emp_position p  ON(p.position_id = m.position_id)
		 WHERE   m.receive_status = 1 AND m.receive_person IS NULL AND m.position_id = $position_id GROUP BY m.orgunitid ";
	$result = mysql_query($sql);
	return $result;
}
// ================== num the docin only staff 
function numDocIN_emp($orgunitid){
	$emp_id = $_SESSION['sys_user'][0]['emp_id'];
	$sql = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN emp_personal emp ON(emp.emp_id=m.receive_person)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE   emp.emp_id = $emp_id AND m.receive_status = 1 AND org.orgunitid = $orgunitid GROUP BY emp.emp_id ";
	$result = mysql_query($sql);
	return $result;
}
/*============== select the head of ICT DIVISION  =========*/
function position(){
	global $user_role,$ptn_id; 
	if($user_role == 1){
		$position_id = " IN(1,2) ";
	}else{
		 $position_id = "=".$ptn_id;
	}
	return mysql_query("SELECT  p.*,emp.name,emp.picture FROM emp_position p
	INNER JOIN emp_employment epp ON(epp.position_id=p.position_id)
	INNER JOIN emp_personal emp ON(emp.emp_id=epp.emp_id)
	WHERE p.position_id $position_id AND isdeleted <> 1 AND epp.workingstatus = 1 ORDER BY p.position_id ASC");
}

/*================ this function is called from index popup diaglog document in =========================*/
function num_doc(){
	$position_id = $_SESSION['sys_user'][0]['position_id'];	
	$orgunitid = $_SESSION['sys_user'][0]['orgunitid'];	
	
	if($position_id == 1 || $position_id == 2){
		$sql_ = "SELECT COUNT(m.m_id) as num ,p.position_id, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN emp_position p  ON(p.position_id = m.position_id)
		 WHERE  m.receive_status = 1 AND m.receive_person IS NULL AND m.position_id = $position_id GROUP BY m.orgunitid ";
	}elseif($position_id == 3 || $position_id == 4){
		$sql_="SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  m.receive_status = 1 AND m.receive_person IS NULL AND org.orgunitid = $orgunitid GROUP BY org.orgunitid";
	}else{
		$emp_id = $_SESSION['sys_user'][0]['emp_id'];
		$sql_ = "SELECT COUNT(m.m_id) as num ,org.orgunitid, org.orgunitname, d.doc_id, m.m_date, m.receive_person, m.receive_status
		FROM tbl_in_out_document d INNER JOIN tbl_manage_doc m ON(m.doc_id=d.doc_id)
		INNER JOIN emp_personal emp ON(emp.emp_id=m.receive_person)
		INNER JOIN orgunit org  ON(org.orgunitid = m.orgunitid)
		 WHERE  emp.emp_id = $emp_id AND m.receive_status = 1 AND org.orgunitid = $orgunitid GROUP BY emp.emp_id ";
	} 
	return mysql_query($sql_);
}