<?php
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationTable.php") ;
require_once (PATH_TABLES . "activity/invoice/InvoiceHeaderTable.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, $revision_number, $new_quot_number, $customer_revise_number, $customer_quot_number, $revise_type){
        $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);
        
        // if(strpos($quot_number, "-") > 0) {
        //     $new_quot_number = substr($quot_number, 0, strpos($quot_number, "-"))."-".$revision_number;
        // } else {
        //     $new_quot_number = $quot_number."-".$revision_number;
        // }

        // $new_quot_number = str_replace($internal_prefix, "", $new_quot_number);
        // $new_quot_number = $internal_prefix.$new_quot_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_TYPE.",".
                SalesQuotationTable::C_REVISE_DATE.",".
                SalesQuotationTable::C_REVISE_VERSION.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_DATE.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_VERSION.",".
                SalesQuotationTable::C_CUSTOMER_NUMBER.",".
                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_EXPECTED_ACCEPT_DATE.",".
                SalesQuotationTable::C_EXPECTED_PAY_DATE.",".
                SalesQuotationTable::C_PERCENTAGE_PAYMENT.",".
                SalesQuotationTable::C_PROBABILITY.",".
                SalesQuotationTable::C_USER_DEFINED_1.",".
                SalesQuotationTable::C_USER_DEFINED_2.",".
                SalesQuotationTable::C_USER_DEFINED_3.",".
                SalesQuotationTable::C_USER_DEFINED_4.",".
                SalesQuotationTable::C_SUBMIT_DATE.",".
                SalesQuotationTable::C_APPROVED_DATE.",".
                SalesQuotationTable::C_APPROVED_BY.",".
                SalesQuotationTable::C_REJECT_REASON.",".
                SalesQuotationTable::C_VIEWED.",".
                SalesQuotationTable::C_VIEWED_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.",".
                "'".$revise_type."',".
                "'".$modifydate."',".
                "'".$revision_number."',".
                "'".NULL_DATE."',".
                "'".$customer_revise_number."',".
                "'".$customer_quot_number."',".
                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."',".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "0,".
                "0,".
                SalesQuotationTable::C_USER_DEFINED_1.",".
                SalesQuotationTable::C_USER_DEFINED_2.",".
                SalesQuotationTable::C_USER_DEFINED_3.",".
                SalesQuotationTable::C_USER_DEFINED_4.",".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "0,".
                "'',".
                "0,".
                "'".NULL_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, $new_customer_quot_number, $key_code){
        $modifydate = date_create('now')->format('Y-m-d H:i:s') ;
        $modifyby   = $_SESSION[SE_USERID];
        $ws         = $_SESSION[SE_REMOTE_IP];

        $quotation_ref_id = Util::randomKey($key_code);

        $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_TYPE.",".
                SalesQuotationTable::C_REVISE_DATE.",".
                SalesQuotationTable::C_REVISE_VERSION.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_DATE.",".
                SalesQuotationTable::C_CUSTOMER_REVISE_VERSION.",".
                SalesQuotationTable::C_CUSTOMER_NUMBER.",".
                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_EXPECTED_ACCEPT_DATE.",".
                SalesQuotationTable::C_EXPECTED_PAY_DATE.",".
                SalesQuotationTable::C_PERCENTAGE_PAYMENT.",".
                SalesQuotationTable::C_PROBABILITY.",".
                SalesQuotationTable::C_USER_DEFINED_1.",".
                SalesQuotationTable::C_USER_DEFINED_2.",".
                SalesQuotationTable::C_USER_DEFINED_3.",".
                SalesQuotationTable::C_USER_DEFINED_4.",".
                SalesQuotationTable::C_SUBMIT_DATE.",".
                SalesQuotationTable::C_APPROVED_DATE.",".
                SalesQuotationTable::C_APPROVED_BY.",".
                SalesQuotationTable::C_REJECT_REASON.",".
                SalesQuotationTable::C_VIEWED.",".
                SalesQuotationTable::C_VIEWED_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."',".
                "0,".
                "'".NULL_DATE."',".
                "'',".
                "'".NULL_DATE."',".
                "'',".
                "'".$new_customer_quot_number."',".
                "'".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.",".
                "'".$quotation_ref_id."',".
                SalesQuotationTable::C_TITLE.",".
                SalesQuotationTable::C_ADDITIONAL_CONTACT.",".
                "'0',".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "0,".
                "0,".
                SalesQuotationTable::C_USER_DEFINED_1.",".
                SalesQuotationTable::C_USER_DEFINED_2.",".
                SalesQuotationTable::C_USER_DEFINED_3.",".
                SalesQuotationTable::C_USER_DEFINED_4.",".
                "'".NULL_DATE."',".
                "'".NULL_DATE."',".
                "0,".
                "'',".
                "0,".
                "'".NULL_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, $status="") {
        // $sql = "SELECT TOP 1 * FROM " . SalesQuotationTable::C_TABLE . " ";

        $sql = "SELECT S.*, I.".InvoiceHeaderTable::C_INVOICE_NUMBER.", I.".InvoiceHeaderTable::C_CREATE_DATE." AS 'INVOICE_DATE'".
                " FROM " .SalesQuotationTable::C_TABLE." S ".
                " LEFT OUTER JOIN " .InvoiceHeaderTable::C_TABLE." I ON I.".InvoiceHeaderTable::C_ID." = S.".SalesQuotationTable::C_ID.
                " WHERE S." .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 S.".SalesQuotationTable::C_CUSTOMER_ID . " = " . $custId;
        }

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

        if($status == "") {
            $sql .= " AND S.".SalesQuotationTable::C_STATUS . " IN (".SalesQuotationStatus::Open.",".SalesQuotationStatus::Issued.",".SalesQuotationStatus::Revised.") AND S.".SalesQuotationTable::C_IS_CLOSED . " = 0 ";
        } else {
            if($status != -1) {
                if($status == SalesQuotationStatus::Closed)
                    $sql .= " AND S.".SalesQuotationTable::C_IS_CLOSED . " = 1 ";
                else
                    $sql .= " AND S.".SalesQuotationTable::C_STATUS . " = ".$status." AND S.".SalesQuotationTable::C_IS_CLOSED . " = 0 ";
            }
        }

        if($fromDate != "" && $toDate != "") {
            switch ($filterDate) {
                case 0:
                    $sql 	.= " AND S.".SalesQuotationTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY S." .SalesQuotationTable::C_CREATE_DATE. " DESC";
                    break;
                case 1:
                    $sql 	.= " AND S.".SalesQuotationTable::C_ISSUE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_ISSUE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY S." .SalesQuotationTable::C_ISSUE_DATE. " DESC";
                    break;
                case 2:
                    $sql 	.= " AND S.".SalesQuotationTable::C_REVISE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_REVISE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY S." .SalesQuotationTable::C_REVISE_DATE. " DESC";
                    break;
                case 3:
                    $sql 	.= " AND S.".SalesQuotationTable::C_ACCEPT_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_ACCEPT_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY S." .SalesQuotationTable::C_ACCEPT_DATE. " DESC";
                    break;
                case 4:
                    $sql 	.= " AND S.".SalesQuotationTable::C_CLOSED_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_CLOSED_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY S." .SalesQuotationTable::C_CLOSED_DATE. " DESC";
                    break;
                case 5:
                    $sql 	.= " AND S.".SalesQuotationTable::C_PREFERRED_INVOICE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_PREFERRED_INVOICE_DATE." <> '".NULL_DATE."' AND DATEPART(HOUR, S.".SalesQuotationTable::C_PREFERRED_INVOICE_DATE.") = '00'" ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_PREFERRED_INVOICE_DATE. " DESC";
                    break;
                case 6: //proforma invoice date
                    $sql 	.= " AND I.".InvoiceHeaderTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND I.".InvoiceHeaderTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY I." .InvoiceHeaderTable::C_CREATE_DATE. " DESC";
                    break;
                case 7://submit date
                    $sql 	.= " AND S.".SalesQuotationTable::C_SUBMIT_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_SUBMIT_DATE." <> '".NULL_DATE."'" ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_SUBMIT_DATE. " DESC";
                    break;
                case 8://approval date
                    $sql 	.= " AND S.".SalesQuotationTable::C_APPROVED_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_APPROVED_DATE." <> '".NULL_DATE."'" ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_APPROVED_DATE. " DESC";
                    break;
                case 9:
                    $sql 	.= " AND ".SalesQuotationTable::C_EXPECTED_ACCEPT_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_EXPECTED_ACCEPT_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_EXPECTED_ACCEPT_DATE. " DESC";
                    break;
                case 10:
                    $sql 	.= " AND ".SalesQuotationTable::C_EXPECTED_PAY_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND ".SalesQuotationTable::C_EXPECTED_PAY_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY " .SalesQuotationTable::C_EXPECTED_PAY_DATE. " DESC";
                    break;
                default:
                    $sql 	.= " AND S.".SalesQuotationTable::C_CREATE_DATE." BETWEEN '".$fromDate." 00:00' AND '".$toDate." 23:59' AND S.".SalesQuotationTable::C_CREATE_DATE." <> '".NULL_DATE."' " ;
                    $orderby = " ORDER BY S." .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 * FROM ". SalesQuotationTable::C_TABLE ;
        $sql .= " WHERE " . SalesQuotationTable::C_ID . " = " . $id;
        $sql .= " ORDER BY " . SalesQuotationTable::C_ID ." ASC";
        
        $rows = $this->db->getTable($sql);
        
        if(!is_null($rows) && count($rows) > 0){
            $sql1 = "SELECT TOP 1 " . SalesQuotationTable::C_REVISE_VERSION . " FROM " ;
            $sql1 .= SalesQuotationTable::C_TABLE . " WHERE " . SalesQuotationTable::C_REF_ID . " = '" .$rows[0][SalesQuotationTable::C_REF_ID]. "' ";
            $sql1 .= " ORDER BY " . SalesQuotationTable::C_ID ." DESC";

            $rows1 = $this->db->getTable($sql1);

            $new_revision_number = "R" . (intval(substr($rows1[0][SalesQuotationTable::C_REVISE_VERSION],1))+1);

            return $new_revision_number;
        }
        
        return "R1";
    }

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

        if(!is_null($rows) && count($rows) > 0){
            $sql1 = "SELECT TOP 1 " . SalesQuotationTable::C_CUSTOMER_REVISE_VERSION . " FROM " ;
            $sql1 .= SalesQuotationTable::C_TABLE . " WHERE " . SalesQuotationTable::C_REF_ID . " = '" .$rows[0][SalesQuotationTable::C_REF_ID]. "' ";
            $sql1 .= " ORDER BY " . SalesQuotationTable::C_ID ." DESC";
            
            $rows1 = $this->db->getTable($sql1);

            $new_revision_number = "R" . (intval(substr($rows1[0][SalesQuotationTable::C_CUSTOMER_REVISE_VERSION],1))+1);

            return $new_revision_number;
        }
        
        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";        
    }
    function getPendingApprovalList($user_id){
        $sql = "SELECT * ";
        $sql .= "FROM ".SalesQuotationTable::C_TABLE." ";
        $sql .= "WHERE ".SalesQuotationTable::C_APPROVED_BY." = ".$user_id." ".
                " AND ".SalesQuotationTable::C_STATUS." IN (".SalesQuotationStatus::Submitted.") ";
        
        $rows = $this->db->getTable($sql);
        return $rows;        
    }
    function getApprovalList($user_id){
        $sql = "SELECT * ";
        $sql .= "FROM ".SalesQuotationTable::C_TABLE." ";
        $sql .= "WHERE ".SalesQuotationTable::C_CREATE_BY." = ".$user_id." ".
                " AND ".SalesQuotationTable::C_STATUS." IN (".SalesQuotationStatus::Approved.", ".SalesQuotationStatus::Rejected.") ".
                " AND ".SalesQuotationTable::C_VIEWED." = 0 ";
        
        $rows = $this->db->getTable($sql);
        return $rows;        
    }
    function calculateGrandTotal($id){
        $sql = "SELECT SUM(A.GRAND_ITEMS + A.GRAND_MS + A.GRAND_PROJECT) AS 'GRAND_TOTAL' FROM (".
                "SELECT ".
                "CASE WHEN S.".SalesQuotationTable::C_ITEMS_PRINT." = 1 THEN I.".SalesQuotationItemsTable::C_GRAND_TOTAL." ELSE 0 END AS 'GRAND_ITEMS', ".
                "CASE WHEN S.".SalesQuotationTable::C_MAINTENANCE_PRINT." = 1 THEN M.".SalesQuotationMaintenanceTable::C_GRAND_TOTAL." ELSE 0 END AS 'GRAND_MS', ".
                "CASE WHEN S.".SalesQuotationTable::C_PROJECT_PLAN_PRINT." = 1 THEN P.".SalesQuotationProjectPlanTable::C_AMOUNT." ELSE 0 END AS 'GRAND_PROJECT' ".
                "FROM ".SalesQuotationTable::C_TABLE." S ".
                "LEFT OUTER JOIN ".SalesQuotationItemsTable::C_TABLE." I ON I.".SalesQuotationItemsTable::C_ID." = S.".SalesQuotationTable::C_ID." ".
                "LEFT OUTER JOIN ".SalesQuotationMaintenanceTable::C_TABLE." M ON M.".SalesQuotationMaintenanceTable::C_QUOTATION_ID." = S.".SalesQuotationTable::C_ID." ".
                "LEFT OUTER JOIN ".SalesQuotationProjectPlanTable::C_TABLE." P ON P.".SalesQuotationProjectPlanTable::C_QUOTATION_ID." = S.".SalesQuotationTable::C_ID." ".
                "WHERE S.".SalesQuotationTable::C_ID." = ".$id." ) A ";
        $row = $this->db->getTable($sql);
        return $row[0]['GRAND_TOTAL'];
    }
}
?>