<?php
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationTable.php") ;
require_once (PATH_MODELS . "base/MasterBase.php") ;

class SalesQuotationClass extends MasterBase {
	function __construct($db) {
		$this->db 		= $db ;
		$this->tbl 		= SalesQuotationTable::C_TABLE ;
		$this->fldid 	= SalesQuotationTable::C_ID ;
		$this->fldorg 	= SalesQuotationTable::C_ORG_ID ;
	}
	function __destruct() {
	}
    function copySalesQuotation($quot_id, $quot_number){
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $modifyby   = $_SESSION[SE_USERID];
        $ws         = $_SESSION[SE_REMOTE_IP];
        $revision_number = $this->getMaxReviseNumber($quot_id);
        log::write(strpos($quot_number, "-"));
        // if(strpos($quot_number, "-") === true) {
        if(strpos($quot_number, "-") > 0) {
            $new_quot_number = substr($quot_number, 0, strpos($quot_number, "-"))."-".$revision_number;
            log::write(substr($quot_number, 0, strpos($quot_number, "-")));
            log::write($new_quot_number);
        } else {
            $new_quot_number = $quot_number."-".$revision_number;
            log::write($new_quot_number);
        }

        $sql = "INSERT INTO ".SalesQuotationTable::C_TABLE." (" .
                SalesQuotationTable::C_BU.",".
                SalesQuotationTable::C_TYPE.",".
                SalesQuotationTable::C_NUMBER.",".
                SalesQuotationTable::C_DATE.",".
                SalesQuotationTable::C_CUSTOMER_ID.",".
                SalesQuotationTable::C_CONTACT_ID.",".
                SalesQuotationTable::C_CONTACT_EMAIL.",".
                SalesQuotationTable::C_CONTACT_MOBILE.",".
                SalesQuotationTable::C_CONTACT_TELEPHONE.",".
                SalesQuotationTable::C_CURRENCY.",".
                SalesQuotationTable::C_PROSPECT.",".
                SalesQuotationTable::C_PROSPECT_NAME.",".
                SalesQuotationTable::C_PROSPECT_CONTACT.",".
                SalesQuotationTable::C_STATUS.",".
                SalesQuotationTable::C_ISSUE_DATE.",".
                SalesQuotationTable::C_REVISE_DATE.",".
                SalesQuotationTable::C_REVISE_VERSION.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_DATE.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_VERSION.",".
                SalesQuotationTable::C_ACCEPT_DATE.",".
                SalesQuotationTable::C_FOOTER.",".
                SalesQuotationTable::C_ISSUE.",".
                SalesQuotationTable::C_ITEMS_TOTAL.",".
                SalesQuotationTable::C_ITEMS_GST.",".
                SalesQuotationTable::C_ISSUE_PRINT.",".
                SalesQuotationTable::C_ISSUE_PRINT_PAGE.",".
                SalesQuotationTable::C_ITEMS_PRINT.",".
                SalesQuotationTable::C_ITEMS_PRINT_PAGE.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT_PAGE.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT_PAGE.",".
                SalesQuotationTable::C_COVER_PRINT.",".
                SalesQuotationTable::C_COVER_PRINT_PAGE.",".
                SalesQuotationTable::C_FOOTER_PRINT.",".
                SalesQuotationTable::C_FOOTER_PRINT_PAGE.",".
                SalesQuotationTable::C_APPENDIX_PRINT.",".
                SalesQuotationTable::C_APPENDIX_PRINT_PAGE.",".
                SalesQuotationTable::C_ADDITIONAL_APPENDIX.",".
                SalesQuotationTable::C_REF_ID.",".
                SalesQuotationTable::C_TITLE.",".
                SalesQuotationTable::C_ADDITIONAL_CONTACT.",".
                SalesQuotationTable::C_IS_CLOSED.",".
                SalesQuotationTable::C_CLOSED_DATE.",".
                SalesQuotationTable::C_PREFERRED_INVOICE_DATE.",".
                SalesQuotationTable::C_ORG_ID.",".
                SalesQuotationTable::C_COY_ID.",".
                SalesQuotationTable::C_ACTIVE.",".
                SalesQuotationTable::C_WS_ID.",".
                SalesQuotationTable::C_MODIFY_BY.",".
                SalesQuotationTable::C_MODIFY_DATE.",".
                SalesQuotationTable::C_CREATE_BY.",".
                SalesQuotationTable::C_CREATE_DATE.
                ") SELECT " .
                SalesQuotationTable::C_BU.",".
                SalesQuotationTable::C_TYPE.",".
                "'".$new_quot_number."',".
                SalesQuotationTable::C_DATE.",".
                SalesQuotationTable::C_CUSTOMER_ID.",".
                SalesQuotationTable::C_CONTACT_ID.",".
                SalesQuotationTable::C_CONTACT_EMAIL.",".
                SalesQuotationTable::C_CONTACT_MOBILE.",".
                SalesQuotationTable::C_CONTACT_TELEPHONE.",".
                SalesQuotationTable::C_CURRENCY.",".
                SalesQuotationTable::C_PROSPECT.",".
                SalesQuotationTable::C_PROSPECT_NAME.",".
                SalesQuotationTable::C_PROSPECT_CONTACT.",".
                SalesQuotationStatus::Open.",".
                SalesQuotationTable::C_ISSUE_DATE.",".
                "'".$modifydate."',".
                "'".$revision_number."',".
                "'".NULL_DATE."',".
                "'',".
                SalesQuotationTable::C_ACCEPT_DATE.",".
                SalesQuotationTable::C_FOOTER.",".
                SalesQuotationTable::C_ISSUE.",".
                SalesQuotationTable::C_ITEMS_TOTAL.",".
                SalesQuotationTable::C_ITEMS_GST.",".
                SalesQuotationTable::C_ISSUE_PRINT.",".
                SalesQuotationTable::C_ISSUE_PRINT_PAGE.",".
                SalesQuotationTable::C_ITEMS_PRINT.",".
                SalesQuotationTable::C_ITEMS_PRINT_PAGE.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT_PAGE.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT_PAGE.",".
                SalesQuotationTable::C_COVER_PRINT.",".
                SalesQuotationTable::C_COVER_PRINT_PAGE.",".
                SalesQuotationTable::C_FOOTER_PRINT.",".
                SalesQuotationTable::C_FOOTER_PRINT_PAGE.",".
                SalesQuotationTable::C_APPENDIX_PRINT.",".
                SalesQuotationTable::C_APPENDIX_PRINT_PAGE.",".
                SalesQuotationTable::C_ADDITIONAL_APPENDIX.",".
                SalesQuotationTable::C_REF_ID.",".
                SalesQuotationTable::C_TITLE.",".
                SalesQuotationTable::C_ADDITIONAL_CONTACT.",".
                "'0',".
                "'".NULL_DATE."',".
                SalesQuotationTable::C_PREFERRED_INVOICE_DATE.",".
                SalesQuotationTable::C_ORG_ID.",".
                SalesQuotationTable::C_COY_ID.",".
                true.",".
                "'".$ws."',".
                $modifyby.",".
                "'".$modifydate."',".
                $modifyby.",".
                "'".$modifydate."'".
                " FROM ".SalesQuotationTable::C_TABLE.
                " WHERE " . SalesQuotationTable::C_ID . " = " .$quot_id ;
        
        $this->db->executeSql($sql);
        
        $sql = " SELECT TOP 1 * ".
                " FROM " . SalesQuotationTable::C_TABLE . " ".
                " WHERE " . SalesQuotationTable::C_REF_ID . " = " .
                " ( SELECT TOP 1 " . SalesQuotationTable::C_REF_ID . 
                " FROM " . SalesQuotationTable::C_TABLE . " ".
                " WHERE " . SalesQuotationTable::C_ID . " = " .$quot_id." )".
                " ORDER BY " . SalesQuotationTable::C_ID . " DESC ";
        // log::write($sql);
        $row = $this->db->getTable($sql);

        return $row[0][SalesQuotationTable::C_ID];
    }

    function copySalesQuotationNew($quot_id, $new_quot_number){
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $modifyby   = $_SESSION[SE_USERID];
        $ws         = $_SESSION[SE_REMOTE_IP];

        $sql = "INSERT INTO ".SalesQuotationTable::C_TABLE." (" .
                SalesQuotationTable::C_BU.",".
                SalesQuotationTable::C_TYPE.",".
                SalesQuotationTable::C_NUMBER.",".
                SalesQuotationTable::C_DATE.",".
                SalesQuotationTable::C_CUSTOMER_ID.",".
                SalesQuotationTable::C_CONTACT_ID.",".
                SalesQuotationTable::C_CONTACT_EMAIL.",".
                SalesQuotationTable::C_CONTACT_MOBILE.",".
                SalesQuotationTable::C_CONTACT_TELEPHONE.",".
                SalesQuotationTable::C_CURRENCY.",".
                SalesQuotationTable::C_PROSPECT.",".
                SalesQuotationTable::C_PROSPECT_NAME.",".
                SalesQuotationTable::C_PROSPECT_CONTACT.",".
                SalesQuotationTable::C_STATUS.",".
                SalesQuotationTable::C_ISSUE_DATE.",".
                SalesQuotationTable::C_REVISE_DATE.",".
                SalesQuotationTable::C_REVISE_VERSION.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_DATE.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_VERSION.",".
                SalesQuotationTable::C_ACCEPT_DATE.",".
                SalesQuotationTable::C_FOOTER.",".
                SalesQuotationTable::C_ISSUE.",".
                SalesQuotationTable::C_ITEMS_TOTAL.",".
                SalesQuotationTable::C_ITEMS_GST.",".
                SalesQuotationTable::C_ISSUE_PRINT.",".
                SalesQuotationTable::C_ISSUE_PRINT_PAGE.",".
                SalesQuotationTable::C_ITEMS_PRINT.",".
                SalesQuotationTable::C_ITEMS_PRINT_PAGE.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT_PAGE.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT_PAGE.",".
                SalesQuotationTable::C_COVER_PRINT.",".
                SalesQuotationTable::C_COVER_PRINT_PAGE.",".
                SalesQuotationTable::C_FOOTER_PRINT.",".
                SalesQuotationTable::C_FOOTER_PRINT_PAGE.",".
                SalesQuotationTable::C_APPENDIX_PRINT.",".
                SalesQuotationTable::C_APPENDIX_PRINT_PAGE.",".
                SalesQuotationTable::C_REF_ID.",".
                SalesQuotationTable::C_TITLE.",".
                SalesQuotationTable::C_ADDITIONAL_CONTACT.",".
                SalesQuotationTable::C_IS_CLOSED.",".
                SalesQuotationTable::C_CLOSED_DATE.",".
                SalesQuotationTable::C_PREFERRED_INVOICE_DATE.",".
                SalesQuotationTable::C_ORG_ID.",".
                SalesQuotationTable::C_COY_ID.",".
                SalesQuotationTable::C_ACTIVE.",".
                SalesQuotationTable::C_WS_ID.",".
                SalesQuotationTable::C_MODIFY_BY.",".
                SalesQuotationTable::C_MODIFY_DATE.",".
                SalesQuotationTable::C_CREATE_BY.",".
                SalesQuotationTable::C_CREATE_DATE.
                ") SELECT " .
                SalesQuotationTable::C_BU.",".
                SalesQuotationTable::C_TYPE.",".
                "'".$new_quot_number."',".
                "'".$modifydate."',".
                SalesQuotationTable::C_CUSTOMER_ID.",".
                SalesQuotationTable::C_CONTACT_ID.",".
                SalesQuotationTable::C_CONTACT_EMAIL.",".
                SalesQuotationTable::C_CONTACT_MOBILE.",".
                SalesQuotationTable::C_CONTACT_TELEPHONE.",".
                SalesQuotationTable::C_CURRENCY.",".
                SalesQuotationTable::C_PROSPECT.",".
                SalesQuotationTable::C_PROSPECT_NAME.",".
                SalesQuotationTable::C_PROSPECT_CONTACT.",".
                SalesQuotationStatus::Open.",".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "'',".
                "'".NULL_DATE."',".
                "'',".
                "'".NULL_DATE."',".
                SalesQuotationTable::C_FOOTER.",".
                SalesQuotationTable::C_ISSUE.",".
                SalesQuotationTable::C_ITEMS_TOTAL.",".
                SalesQuotationTable::C_ITEMS_GST.",".
                SalesQuotationTable::C_ISSUE_PRINT.",".
                SalesQuotationTable::C_ISSUE_PRINT_PAGE.",".
                SalesQuotationTable::C_ITEMS_PRINT.",".
                SalesQuotationTable::C_ITEMS_PRINT_PAGE.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT.",".
                SalesQuotationTable::C_MAINTENANCE_PRINT_PAGE.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT.",".
                SalesQuotationTable::C_PROJECT_PLAN_PRINT_PAGE.",".
                SalesQuotationTable::C_COVER_PRINT.",".
                SalesQuotationTable::C_COVER_PRINT_PAGE.",".
                SalesQuotationTable::C_FOOTER_PRINT.",".
                SalesQuotationTable::C_FOOTER_PRINT_PAGE.",".
                SalesQuotationTable::C_APPENDIX_PRINT.",".
                SalesQuotationTable::C_APPENDIX_PRINT_PAGE.",".
                "'".Util::randomKey()."',".
                SalesQuotationTable::C_TITLE.",".
                SalesQuotationTable::C_ADDITIONAL_CONTACT.",".
                "'0',".
                "'".NULL_DATE."',".
                SalesQuotationTable::C_PREFERRED_INVOICE_DATE.",".
                SalesQuotationTable::C_ORG_ID.",".
                SalesQuotationTable::C_COY_ID.",".
                true.",".
                "'".$ws."',".
                $modifyby.",".
                "'".$modifydate."',".
                $modifyby.",".
                "'".$modifydate."'".
                " FROM ".SalesQuotationTable::C_TABLE.
                " WHERE " . SalesQuotationTable::C_ID . " = " .$quot_id ;
        
        $this->db->executeSql($sql);
        
        $sql = " SELECT TOP 1 * ".
                " FROM " . SalesQuotationTable::C_TABLE . " ".
                " WHERE " . SalesQuotationTable::C_NUMBER . " = '" .$new_quot_number."' ".
                " ORDER BY " . SalesQuotationTable::C_ID . " DESC ";
        // log::write($sql);
        $row = $this->db->getTable($sql);

        return $row[0][SalesQuotationTable::C_ID];
    }

    function getSalesQuotationList($custId, $prospect, $filterDate, $fromDate, $toDate) {
        // $sql = "SELECT TOP 1 * FROM " . SalesQuotationTable::C_TABLE . " ";

        $sql = "SELECT * FROM " .SalesQuotationTable::C_TABLE.
                " WHERE " .SalesQuotationTable::C_ID. " IN (".
                " SELECT TOP 1 WITH TIES " .SalesQuotationTable::C_ID.
                " FROM " .SalesQuotationTable::C_TABLE.
                " ORDER BY ".
                " ROW_NUMBER() OVER(PARTITION BY " .SalesQuotationTable::C_REF_ID. " ORDER BY " .SalesQuotationTable::C_ID. " DESC)".
                ")";

        if($custId > 0) {
            $sql .= " AND ".SalesQuotationTable::C_CUSTOMER_ID . " = " . $custId;
        }

        if($prospect != "") {
            $sql .= " AND ".SalesQuotationTable::C_PROSPECT_NAME . " LIKE '%" . $prospect . "%'";
        }

        if($fromDate != "" && $toDate != "") {
            switch ($filterDate) {
                case 0:
                    $sql 	.= " AND ".SalesQuotationTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_CREATE_DATE. " DESC";
                    break;
                case 1:
                    $sql 	.= " AND ".SalesQuotationTable::C_ISSUE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_ISSUE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_ISSUE_DATE. " DESC";
                    break;
                case 2:
                    $sql 	.= " AND ".SalesQuotationTable::C_REVISE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_REVISE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_REVISE_DATE. " DESC";
                    break;
                case 3:
                    $sql 	.= " AND ".SalesQuotationTable::C_ACCEPT_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_ACCEPT_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_ACCEPT_DATE. " DESC";
                    break;
                case 4:
                    $sql 	.= " AND ".SalesQuotationTable::C_CLOSED_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_CLOSED_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_CLOSED_DATE. " DESC";
                    break;
                case 5:
                    $sql 	.= " AND ".SalesQuotationTable::C_PREFERRED_INVOICE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_PREFERRED_INVOICE_DATE." <> '".NULL_DATE."' AND DATEPART(HOUR, ".SalesQuotationTable::C_PREFERRED_INVOICE_DATE.") = '00'" ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_PREFERRED_INVOICE_DATE. " DESC";
                    break;
                default:
                    $sql 	.= " AND ".SalesQuotationTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_CREATE_DATE. " DESC";
                    break;
            }            
        }
        // $orderby = " ORDER BY " .SalesQuotationTable::C_MODIFY_DATE. " DESC";
        // $orderby = " ORDER BY " .SalesQuotationTable::C_DATE. " DESC," .SalesQuotationTable::C_ISSUE_DATE. " DESC," .SalesQuotationTable::C_REVISE_DATE. " DESC," .SalesQuotationTable::C_ACCEPT_DATE. " DESC";
        // log::write($sql . $orderby);
        $rows = $this->db->getTable($sql . $orderby);

        if(!is_null($rows) && count($rows) > 0){
            return $rows;
        } else {
            return null;
        }
    }

	function updateItemsTotal($id, $itemsTotal, $itemsGST){
        $sql = "UPDATE " .SalesQuotationTable::C_TABLE.
		" SET " . SalesQuotationTable::C_ITEMS_TOTAL . " = '" .$itemsTotal. "', " . SalesQuotationTable::C_ITEMS_GST . " = '" .$itemsGST. "' ".
		" WHERE " . SalesQuotationTable::C_ID . " = " .$id ;

        $this->db->executeSql($sql);
    }

    function getMaxReviseNumber($id) {
    	$sql = "SELECT " . SalesQuotationTable::C_REVISE_VERSION . " FROM " ;
        $sql .= SalesQuotationTable::C_TABLE . " WHERE " . SalesQuotationTable::C_ID . " = " . $id;
        $sql .= "ORDER BY " . SalesQuotationTable::C_ID ." ASC";
        
        $rows = $this->db->getTable($sql);

        if(!is_null($rows) && count($rows) > 0){
            return "R" . (substr($rows[0][SalesQuotationTable::C_REVISE_VERSION],1)+1) . "";
        }
        
        return "R1";
    }

    function getCustomerMaxReviseNumber($id) {
    	$sql = "SELECT " . SalesQuotationTable::C_CUSTOMER_REVISE_VERSION . " FROM " ;
        $sql .= SalesQuotationTable::C_TABLE . " WHERE " . SalesQuotationTable::C_ID . " = " . $id;
        $sql .= "ORDER BY " . SalesQuotationTable::C_ID ." ASC";
        
        $rows = $this->db->getTable($sql);

        if(!is_null($rows) && count($rows) > 0){
            return "R" . (substr($rows[0][SalesQuotationTable::C_CUSTOMER_REVISE_VERSION],1)+1) . "";
        }
        
        return "R1";
    }

    function getMaxID(){
        $sql = "SELECT MAX ( " . SalesQuotationTable::C_ID . ")+1 AS " . SalesQuotationTable::C_ID . "  ";
        $sql .= "FROM " . SalesQuotationTable::C_TABLE . " ";
        
        $rows = $this->db->getTable($sql);
        return $rows;
        
    }
    
    function getMaxSalesQuotationNumber($bu_id){
        $day = date_create('now')->format('d') ;
        $month = date_create('now')->format('m') ;
        $year = date_create('now')->format('Y') ;
        // log::write($day);

        if($day == "01" || $day == "1") {
            return "10";
        } else {
            $sql = "SELECT ". SalesQuotationTable::C_NUMBER;
            $sql .= " FROM ". SalesQuotationTable::C_TABLE ;
            $sql .= " WHERE ". SalesQuotationTable::C_BU ." = ". $bu_id ." AND YEAR(". SalesQuotationTable::C_DATE. ") = '".$year."' AND MONTH(". SalesQuotationTable::C_DATE. ") = '".$month."' ";
            $sql .= " ORDER BY ". SalesQuotationTable::C_ID ." DESC";
            // log::write($sql);
            $rows = $this->db->getTable($sql);
            
            if(!is_null($rows) && count($rows) > 0){
                // log::write(strpos($rows[0][SalesQuotationTable::C_NUMBER], "-R"));
                // log::write(strpos($rows[0][SalesQuotationTable::C_NUMBER], "-R") + 1);
                // log::write(substr($rows[0][SalesQuotationTable::C_NUMBER], 0, 8) + 1);

                if(strpos($rows[0][SalesQuotationTable::C_NUMBER], "-R") > 0)
                    return (substr($rows[0][SalesQuotationTable::C_NUMBER],-5) + 1);
                else
                    return (substr($rows[0][SalesQuotationTable::C_NUMBER],-2) + 1);
            }
        }
        
        return "10";        
    }
}
?>