<?php

/*
 * lib_statistics相关函数库
 * ============================================================================
 ============================================================================
 * 版权所有 2012-2020 深圳朝九科技有限公司，并保留所有权利。
 * ----------------------------------------------------------------------------
 * 这不是一个自由软件！您只能在不用于商业目的的前提下对程序代码进行修改和
 * 使用；不允许对程序代码以任何形式任何目的的再发布。
 * ============================================================================
 * $Author: Yukey  QQ:2787720874
*/
if (!defined('YUKEY'))
{
    die('Hacking attempt');
}

/**
 * 获得statistics下的项目列表
 *
*/
function get_arc_count($fid,$start,$end,$pro)
{

		($pro !='') ? $conp = " AND a.tbProductName='".$pro."'" : $conp ='';
		if($_SESSION['tbRank']>'50'){
		   $rank='';//公司内部
		}elseif($_SESSION['tbRank']<='50' && $_SESSION['tbRank']>'40'){
			$rank=" AND tbClientID='" . $_SESSION['tbCompanyID'] . "'"; //客人权限,无联合查询,所以与别处不同,字段前无a.前缀
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank='';
		   //$rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'"; //工厂权限
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND tbClientID='" . $_SESSION['tbCompanyID'] . "'";//客人的客人,无联合查询,所以与别处不同,字段前无a.前缀
		}
		$str = "SELECT CASE WHEN tbResult='Accepted' THEN 'ac' WHEN tbResult='Rejected' THEN 're' 
		WHEN tbResult='Concessed' THEN 'sc' ELSE NULL END 
		AS result,COUNT(*) AS a_count FROM" . $GLOBALS['prefix']->table('reports') .
            "WHERE tbReport='3'" . $conp . " AND tbFactoryID='" . $fid . "'". $rank . " 
            AND tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' 
            GROUP BY CASE WHEN tbResult='Accepted' THEN 'ac' 
            WHEN tbResult='Rejected' THEN 're' 
            WHEN tbResult='Concessed' THEN 'sc' ELSE NULL END";
     if ($str!=''){
     return $GLOBALS['db']->getAll($str);
     }else{
     return false;
     }
}

//取得某一时间段内的出货前验货数量
function get_ship_qty($fac,$pro,$start,$end)
{
	$con=" AND b.tbCheckone IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection')";
	($fac !='') ? $conf = " AND a.tbFactoryID='".$fac."'" : $conf ='';
	($pro !='') ? $conp = " AND a.tbProductName='".$pro."'" : $conp ='';
		if($_SESSION['tbRank']>='50'){
		   $rank='';
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'";
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'";
		}
 	$sql = "SELECT SUM(tbLotQty) AS ship_qty FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3'" . $con . $conf . $conp . $rank . " AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "'";

	 if ($sql!=''){
     return $GLOBALS['db']->getOne($sql);
     }else{
     return false;
     }
}

//取得以厂家为分组的统计清单
function get_qp($fac,$pro,$start,$end)
{
	($fac !='') ? $conf = " AND a.tbFactoryID='".$fac."'" : $conf ='';
	($pro !='') ? $conp = " AND a.tbProductName='".$pro."'" : $conp ='';
		if($_SESSION['tbRank']>'50'){
		   $rank='';//公司内部
		}elseif($_SESSION['tbRank']<='50' && $_SESSION['tbRank']>'40'){
			$rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'"; //客人权限
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'"; //工厂权限
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'";//客人的客人
		}
 		$sql = "SELECT a.tbClientID,a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbNickName FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['si']->table('companys') . " AS b ON a.tbFactoryID = b.tbCompanyID WHERE a.tbReport='3'" . $conf . $conp . $rank . " AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' GROUP BY a.tbFactoryID";

	 if ($sql!=''){
     return $GLOBALS['db']->getAll($sql);
     }else{
     return false;
     }
}

function get_first_arc($fid,$start,$end,$pro)
{
		  ($pro !='') ? $conp = " AND a.tbProductName='".$pro."'" : $conp ='';
		if($_SESSION['tbRank']>'50'){
		   $rank='';//公司内部
		}elseif($_SESSION['tbRank']<='50' && $_SESSION['tbRank']>'40'){
			$rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'"; //客人权限
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank='';//公司内部
		   //$rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'"; //工厂权限
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'";//客人的客人
		}
		   $str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' 
		   WHEN a.tbResult='Rejected' THEN 're' 
		   WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,
		   COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') .
               "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " 
               AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3'" . $conp . " 
               AND a.tbFactoryID='" . $fid . "'" . $rank . " AND a.tbInspectionDate 
               BETWEEN '" .  $start . "' AND '" . $end . "' AND 
               b.tbCheckone IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') 
               GROUP BY CASE 
               WHEN a.tbResult='Accepted' THEN 'ac' 
               WHEN a.tbResult='Rejected' THEN 're' 
               WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
     if ($str!=''){
     return $GLOBALS['db']->getAll($str);
     }else{
     return false;
     }
}

function get_re_arc($f_id,$start,$end,$pro_select)
{
	if($pro_select=='All' || $pro_select=='')
	{
		if($_SESSION['tbRank']>='50'){
		   $str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND a.tbFactoryID='" . $f_id ."' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
			$str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND a.tbFactoryID='" . $f_id ."' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
		}elseif($_SESSION['tbRank']<='20'){
			$str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND a.tbFactoryID='" . $f_id ."' AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
		}
	}
	else
	{
	    if($_SESSION['tbRank']>='50'){
		   $str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND tbProductName='" . $pro_select . "' AND a.tbFactoryID='" . $f_id ."' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
			$str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND tbProductName='" . $pro_select . "' AND a.tbFactoryID='" . $f_id ."' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
		}elseif($_SESSION['tbRank']<='20'){
			$str = "SELECT CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END AS result,COUNT(*) AS a_count,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND tbProductName='" . $pro_select . "' AND a.tbFactoryID='" . $f_id ."' AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY CASE WHEN a.tbResult='Accepted' THEN 'ac' WHEN a.tbResult='Rejected' THEN 're' WHEN a.tbResult='Concessed' THEN 'sc' ELSE NULL END";
		}
	}
     if ($str!=''){
     return $GLOBALS['db']->getAll($str);
     }else{
     return false;
     }
}

function get_first_list($fid,$start,$end,$pro='')
{
		($pro !='') ? $conp = " AND a.tbProductName='".$pro."'" : $conp ='';
		if($_SESSION['tbRank']>'50'){
		   $rank='';//公司内部
		}elseif($_SESSION['tbRank']<='50' && $_SESSION['tbRank']>'40'){
			$rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'"; //客人权限
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'"; //工厂权限
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'";//客人的客人
		}
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3'" . $conp . $rank . " AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection','During Production Inspection') AND a.tbFactoryID = '" . $fid . "' GROUP BY a.tbFactoryID";
	 if ($sql!=''){
     return $GLOBALS['db']->getAll($sql);
     }else{
     return false;
     }
}

function get_re_list($f_id,$start,$end,$pro_select)
{
	if($pro_select=='All' || $pro_select=='')
	{
		if($_SESSION['tbRank']>='50'){
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') AND a.tbFactoryID = '" . $f_id . "' GROUP BY a.tbFactoryID";
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY a.tbFactoryID";
		}elseif($_SESSION['tbRank']<='20'){
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "' a.tbReport='3' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') AND a.tbFactoryID = '" . $f_id . "' GROUP BY a.tbFactoryID";
		}
	}
	else
	{
	    if($_SESSION['tbRank']>='50'){
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND tbProductName='" . $pro_select . "' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') AND a.tbFactoryID = '" . $f_id . "' GROUP BY a.tbFactoryID";
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND tbProductName='" . $pro_select . "' AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone NOT IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') GROUP BY a.tbFactoryID";
		}elseif($_SESSION['tbRank']<='20'){
		   $sql = "SELECT a.tbFactoryID,COUNT(*) AS all_reports,SUM(tbLotQty) AS lot_qty,b.tbCheckone FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber WHERE a.tbReport='3' AND tbProductName='" . $pro_select . "' AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "' a.tbReport='3' AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end . "' AND b.tbCheckone IN('Final Pre-Shipment Inspection','B Stock Inspection','In-Process Inspection','Online Inspection','Full-Check Inspection','During Production Inspection') AND a.tbFactoryID = '" . $f_id . "' GROUP BY a.tbFactoryID";
		}
	}
	 if ($sql!=''){
     return $GLOBALS['db']->getAll($sql);
     }else{
     return false;
     }
}

function get_qd($fac,$pro_select,$start,$end)
{
		$factory=($fac != '') ? " AND a.tbFactoryID ='" . $fac ."'" : '';
		$product=($pro_select != '') ? " AND a.tbProductName='" . $pro_select . "'" : '';
		if($_SESSION['tbRank']>'50'){
		   $rank='';//公司内部
		}elseif($_SESSION['tbRank']<='50' && $_SESSION['tbRank']>'40'){
			$rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'"; //客人权限
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'"; //工厂权限
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'";//客人的客人
		}   	   
		   
	$sql = "SELECT a.tbPN,a.tbReportNumber,a.tbProductName,a.tbInspectionDate,a.tbClientID,a.tbFactoryID,a.tbLotQty,a.tbSampleSize,a.tbResult,b.tbCheckone,b.tbPM,b.tbTeam,c.tbNickName,d.tbReportNumber,SUM(d.tbCritical) AS CR,SUM(d.tbMajor) AS MA,SUM(d.tbMinor) AS MI FROM" . $GLOBALS['prefix']->table('reports') . "AS a LEFT JOIN" . $GLOBALS['prefix']->table('projects') . " AS b ON a.tbPONumber = b.tbPONumber LEFT JOIN " . $GLOBALS['si']->table('companys') . " AS c ON a.tbFactoryID = c.tbCompanyID LEFT JOIN ".$GLOBALS['prefix']->table('find_defects')."  AS d ON a.tbReportNumber=d.tbReportNumber WHERE a.tbReport='3' " . $product . $rank . " AND a.tbInspectionDate BETWEEN '" .  $start . "' AND '" . $end ."'". $factory . " GROUP BY a.tbReportNumber ORDER BY a.tbInspectionDate ASC";
	 if ($sql!=''){
     	 return $GLOBALS['db']->getAll($sql);
     }else{
     	 return false;
     }
} 

function get_top($fac,$pro_select,$start,$end)
{
		$factory=($fac != '') ? " AND a.tbFactoryID ='" . $fac ."'" : '';
		$product=($pro_select != '') ? " AND a.tbProductName='" . $pro_select . "'" : '';
		if($_SESSION['tbRank']>'50'){
		   $rank='';//公司内部
		}elseif($_SESSION['tbRank']<='50' && $_SESSION['tbRank']>'40'){
			$rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'"; //客人权限
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   $rank=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID'] . "'"; //工厂权限
		}elseif($_SESSION['tbRank']<='20'){
		   $rank=" AND a.tbClientID='" . $_SESSION['tbCompanyID'] . "'";//客人的客人
		}   	   
	 	 $sql = "SELECT a.tbPN,a.tbReportNumber,a.tbProductName,a.tbInspectionDate,a.tbClientID,a.tbFactoryID,b.tbReportNumber,b.tbCategory,b.tbTopical,b.tbDefection,SUM(b.tbCritical) AS TCR,SUM(b.tbMajor) AS TMA,SUM(b.tbMinor) AS TMI,c.tbNickName FROM" . $GLOBALS['prefix']->table('reports') . "AS a RIGHT JOIN" . $GLOBALS['prefix']->table('find_defects') . " AS b ON a.tbReportNumber = b.tbReportNumber LEFT JOIN " . $GLOBALS['si']->table('companys') . " AS c ON a.tbFactoryID=c.tbCompanyID  WHERE a.tbInspectionDate BETWEEN '" . $start . "' AND '" . $end . "'" . $product . $rank . $factory . " GROUP BY b.tbCategory,b.tbTopical ORDER BY FIELD(b.tbCategory,'Function','Cosmetic','Packing'),TCR DESC,TMA DESC,TMI DESC";
	 if ($sql!=''){
     	 return $GLOBALS['db']->getAll($sql);
     }else{
     	 return false;
     }
} 

function get_topd($start,$end,$key,$topic)
{
		if ($key=='All' || $key==''){
		$product='';
		}else{
		$product=" AND a.tbProductName='" . $key . "'";
		}
		
		if($_SESSION['tbRank']>='50'){
		   //公司内部及客人查看
		  $factory='';
		}elseif($_SESSION['tbRank']<='40' && $_SESSION['tbRank']>'20'){
		   //工厂人员查看
		   $factory=" AND a.tbFactoryID='" . $_SESSION['tbCompanyID']."'";
		}elseif($_SESSION['tbRank']<='20'){
		   //客人的客人查看
		}
	 	 $sql = "SELECT a.tbPN,a.tbReportNumber,a.tbProductName,a.tbInspectionDate,a.tbClientID,a.tbFactoryID,b.tbReportNumber,b.tbCategory,b.tbTopical,b.tbDefection,b.tbSerial_NO,b.tbCritical,b.tbMajor,b.tbMinor FROM" . $GLOBALS['prefix']->table('reports') . "AS a RIGHT JOIN" . $GLOBALS['prefix']->table('find_defects') . " AS b ON a.tbReportNumber = b.tbReportNumber LEFT JOIN " . $GLOBALS['si']->table('companys') . " AS c ON a.tbFactoryID=c.tbCompanyID  WHERE b.tbTopical='" . $topic . "' AND a.tbInspectionDate BETWEEN '" . $start . "' AND '" . $end . "'" . $product . $factory . " ORDER BY a.tbReportNumber DESC";
	 if ($sql!=''){
     	 return $GLOBALS['db']->getAll($sql);
     }else{
     	 return false;
     }
} 
?>