<?php
require_once (PATH_MODELS . "base/MasterBase.php") ;
require_once (PATH_TABLES . "base/TableBase.php") ;
require_once (PATH_TABLES . "admin/CompanyTable.php") ;
require_once (PATH_TABLES . "admin/CompanyLocationTable.php") ;
require_once (PATH_TABLES . "admin/OrganizationTable.php") ;
require_once (PATH_TABLES . "admin/UserTable.php") ;

//activity
require_once (PATH_TABLES . "activity/ActivityConsultantTable.php") ;
require_once (PATH_TABLES . "activity/ActivityDocumentTable.php") ;
require_once (PATH_TABLES . "activity/ActivityExpenseDetailTable.php") ;
require_once (PATH_TABLES . "activity/ActivityHeaderTable.php") ;
require_once (PATH_TABLES . "activity/ActivityStatusTable.php") ;
require_once (PATH_TABLES . "activity/ActivityTimingDetailTable.php") ;
require_once (PATH_TABLES . "activity/ActivityAttachmentTable.php") ;
require_once (PATH_TABLES . "activity/ActivityTypeTable.php") ;
require_once (PATH_TABLES . "activity/BrandTable.php") ;
require_once (PATH_TABLES . "activity/CurrencyTable.php") ;
require_once (PATH_TABLES . "activity/CurrencyRateTable.php") ;
require_once (PATH_TABLES . "activity/CustomerContactTable.php") ;
require_once (PATH_TABLES . "activity/CustomerLocationAssetTable.php") ;
require_once (PATH_TABLES . "activity/CustomerLocationTable.php") ;
require_once (PATH_TABLES . "activity/CustomerTable.php") ;
require_once (PATH_TABLES . "activity/ExpenseItemTable.php") ;
require_once (PATH_TABLES . "activity/JobAssignmentAttachmentTable.php") ;
require_once (PATH_TABLES . "activity/JobAssignmentDetailTable.php") ;
require_once (PATH_TABLES . "activity/JobAssignmentTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreAttachmentTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreTaskTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreBillingTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreEquipmentTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreListEquipmentTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreTable.php") ;
require_once (PATH_TABLES . "activity/JobCentreResourcesTable.php") ;

require_once (PATH_TABLES . "activity/JobQuotationTable.php") ;
require_once (PATH_TABLES . "activity/JobQuotationTaskTable.php") ;
require_once (PATH_TABLES . "activity/JobQuotationResourcesTable.php") ;

require_once (PATH_TABLES . "activity/LocationTable.php") ;
require_once (PATH_TABLES . "activity/ModelTable.php") ;
require_once (PATH_TABLES . "activity/ProductTable.php") ;
require_once (PATH_TABLES . "activity/ProjectMandaysBillTable.php") ;
require_once (PATH_TABLES . "activity/ProjectPhaseTable.php") ;
require_once (PATH_TABLES . "activity/ProjectPhaseUserRightsTable.php") ;
require_once (PATH_TABLES . "activity/ProjectPhaseUserTable.php") ;
require_once (PATH_TABLES . "activity/ProjectTable.php") ;
require_once (PATH_TABLES . "activity/TaskPlanParticipantTable.php") ;
require_once (PATH_TABLES . "activity/TaskPlanScheduleTable.php") ;
require_once (PATH_TABLES . "activity/TaskPlanTable.php") ;
require_once (PATH_TABLES . "activity/TaskScheduleTable.php") ;
require_once (PATH_TABLES . "activity/TaxesTypePercentageTable.php") ;
require_once (PATH_TABLES . "activity/TaxesTypeTable.php") ;
require_once (PATH_TABLES . "activity/JobAssignmentAttachmentTable.php") ;
require_once (PATH_TABLES . "activity/JobAssignmentDetailTable.php") ;

//email approval table
require_once (PATH_TABLES . "activity/ActivityEmailApprovalTable.php") ;
require_once (PATH_TABLES . "activity/ActivityEmailApprovalReminderTable.php") ;

//checklist
require_once (PATH_TABLES . "activity/ChecklistHeaderTable.php") ;
require_once (PATH_TABLES . "activity/ChecklistDetailTable.php") ;
require_once (PATH_TABLES . "activity/ChecklistActivityTable.php") ;


//quotation & goods
// require_once (PATH_TABLES . "activity/quotation/QuotationTable.php") ;
// require_once (PATH_TABLES . "activity/quotation/QuotationDetailTable.php") ;
require_once (PATH_TABLES . "activity/goods/GoodsDeliveryTable.php") ;
require_once (PATH_TABLES . "activity/goods/GoodsReceivingTable.php") ;
require_once (PATH_TABLES . "activity/goods/GoodsReceivingDetailTable.php") ;
require_once (PATH_TABLES . "activity/goods/GoodsReceivingCommunicationTable.php") ;
require_once (PATH_TABLES . "activity/goods/ItemTable.php") ;

require_once (PATH_TABLES . "activity/JobServiceAttendeeChecklistTable.php") ;
require_once (PATH_TABLES . "activity/JobServiceAttendeePictureTable.php") ;
require_once (PATH_TABLES . "activity/JobServiceAttendeeTable.php") ;
require_once (PATH_TABLES . "activity/FooterInformationTable.php") ;

//job quotation template table
require_once (PATH_TABLES . "activity/template/JobQuotationTemplateTable.php") ;
require_once (PATH_TABLES . "activity/template/JobQuotationTaskTemplateTable.php") ;


//job center template table

require_once (PATH_MODELS . "activity/template/JobCentreTemplateClass.php");
require_once (PATH_MODELS . "activity/template/JobCentreTaskTemplateClass.php");

// sales quotation
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationAttachmentTable.php") ;

// sales quotation- items
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationItemsTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationItemsDetailTable.php") ;
// sales quotation - appendix
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationAppendixTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationAppendixTemplateTable.php") ;
// sales quotation - project plan
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationProjectPlanTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationProjectPlanDetailTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationProjectPlanTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationProjectPlanHeaderTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationProjectPlanDetailTemplateTable.php") ;
// sales quotation - maintenance
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationMaintenanceTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationMaintenanceDetailTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationMaintenanceTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationMaintenanceHeaderTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationMaintenanceDetailTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationCoverTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationCoverTemplateTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationCoverDetailTemplateTable.php") ;
// sales quotation - additional
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationAdditionalInformationTable.php") ;
require_once (PATH_TABLES . "activity/salesquotation/SalesQuotationAdditionalAttachmentTable.php") ;
//sales target
require_once (PATH_TABLES . "activity/salestarget/SalesTargetTable.php") ;
//payment terms
require_once (PATH_TABLES . "activity/PaymentTermsTable.php") ;
//invoice
require_once (PATH_TABLES . "activity/invoice/InvoiceHeaderTable.php") ;
require_once (PATH_TABLES . "activity/invoice/InvoiceTitleTable.php") ;

class DatabaseTableListClass {
    public $AllTable = array(
        'UserTable',
        'CompanyLocationTable',
        //Activity
        "ActivityConsultantTable",
        "ActivityDocumentTable",
        "ActivityExpenseDetailTable",
        "ActivityHeaderTable",
        "ActivityTimingDetailTable",
       "ActivityStatusTable",
       // "ActivityTypeTable",
       "BrandTable",
       "CurrencyTable",
       // "CurrencyRateTable",
       "CustomerContactTable",

        "CustomerLocationAssetTable",
        "CustomerLocationTable",
        "CustomerTable",
        "ExpenseItemTable",
        "ActivityAttachmentTable",
    //    "JobAssignmentAttachmentTable",
        "JobAssignmentDetailTable",
    //    "JobAssignmentTable",
    //    "JobCentreBillingTable",
    //    "JobCentreEquipmentTable",
        "JobCentreListEquipmentTable",
     "JobCentreTable",
       "LocationTable",
       "ModelTable",
       "ProductTable",
       "JobCentreTaskTable",
       "JobCentreResourcesTable",

       "JobQuotationTable",
       "JobQuotationTaskTable",
       "JobQuotationTemplateTable",
        "JobQuotationTaskTemplateTable" ,
        "JobQuotationResourcesTable",

        "JobCentreTemplateTable",
        "JobCentreTaskTemplateTable" ,
        "JobCentreResourcesTable",
    //    "ProjectMandaysBillTable",
    //    "ProjectPhaseTable",
    //    "ProjectPhaseUserRightsTable",
    //    "ProjectPhaseUserTable",
    //    "TaskPlanParticipantTable",
    //    "TaskPlanScheduleTable",
    //    "TaskPlanTable",
    //    "TaskScheduleTable",
    //    "TaxesTypePercentageTable",
       "TaxesTypeTable",
    
        //activity
        "ChecklistHeaderTable",
        "ChecklistDetailTable",
        "ChecklistActivityTable",
        "JobAssignmentAttachmentTable",
        "JobAssignmentDetailTable",
        //quotation & goods
        // "QuotationTable",
        // "QuotationDetailTable",
        "GoodsDeliveryTable",
        "GoodsReceivingTable",
        "GoodsReceivingDetailTable",
        "GoodsReceivingCommunicationTable",
        "ItemTable",
        
        //attendee
        "JobServiceAttendeeChecklistTable",
        "JobServiceAttendeePictureTable",
        "JobServiceAttendeeTable",
        "FooterInformationTable",

        //sales quotation
        "SalesQuotationTable",
        "SalesQuotationAttachmentTable",
        
        //sales quotation - items
        "SalesQuotationItemsTable",
        "SalesQuotationItemsDetailTable",
        //sales quotation - appendix
        "SalesQuotationAppendixTable",
        "SalesQuotationTemplateTable",
        "SalesQuotationAppendixTemplateTable",
        //sales quotation - project plan
        "SalesQuotationProjectPlanTable",
        "SalesQuotationProjectPlanDetailTable",
        "SalesQuotationProjectPlanTemplateTable",
        "SalesQuotationProjectPlanHeaderTemplateTable",
        "SalesQuotationProjectPlanDetailTemplateTable",
        //sales quotation - maintenance
        "SalesQuotationMaintenanceTable",
        "SalesQuotationMaintenanceDetailTable",
        "SalesQuotationMaintenanceTemplateTable",
        "SalesQuotationMaintenanceHeaderTemplateTable",
        "SalesQuotationMaintenanceDetailTemplateTable",
        "SalesQuotationCoverTable",
        "SalesQuotationCoverTemplateTable",
        "SalesQuotationCoverDetailTemplateTable",
        //sales quotation - additional
        "SalesQuotationAdditionalInformationTable",
        "SalesQuotationAdditionalAttachmentTable",

        //sales target
        "SalesTargetTable",

        //payment terms
        "PaymentTermsTable",

        //invoice
        "InvoiceHeaderTable",
        "InvoiceTitleTable",

        //email approval
        "ActivityEmailApprovalTable",
        "ActivityEmailApprovalReminderTable"
    );
}
class UpdateDatabaseClass extends MasterBase {
	
	function __construct($db) {
		$this->db = $db ;
	}
	function __destruct() {
	}
    
    function processUpdateDatabase(){
        $this->db->open();
        $result = "";
        try {
            $clsTableList = (new DatabaseTableListClass())->AllTable;
            foreach($clsTableList as $tableName){
                $clsTable = new ReflectionClass($tableName);
                $clsTableDetail = new ReflectionClass($tableName . "Detail");
                $columnList = $clsTable->getConstants();
                $columnDetailList = $clsTableDetail->getConstants();    
                
                $sql = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" .$columnList["C_TABLE"] . "'";
                
                $rows = $this->db->getTable($sql);
                
                if(!is_null($rows) && count($rows)){
                    foreach($columnList as $key => $column){        
                        if ($key != "C_TABLE" && $key != "C_PK" ){
                            $detail = $columnDetailList[$key];                        
                            $found = false;
                            $rowFound = null;
                            foreach($rows as $row){
                                if($row['COLUMN_NAME'] == $column){
                                    $found = true;
                                    $rowFound = $row;
                                }
                            }
                            $detailArr = explode("|", $detail);
                            if($found){
                                $needToUpdateDataType = false;
                                $needToUpdateDefault = false;
                                $needToUpdateNullable = false;
                                $needToUpdateSize = false;                            
                                if($detailArr[0] != 'identity'){                                                    
                                    //check datatype
                                    if($rowFound['DATA_TYPE'] != $detailArr[0]){
                                        $needToUpdateDataType = true;
                                        if($rowFound['IS_NULLABLE'] != $detailArr[2]){
                                            $needToUpdateNullable = true;
                                        }
                                    } else {
                                        //check nullable
                                        if($rowFound['IS_NULLABLE'] != $detailArr[2]){
                                            $needToUpdateNullable = true;
                                        } else {

                                            //check default
                                            if($rowFound['COLUMN_DEFAULT'] != "(" . $detailArr[3] . ")"){
                                                $needToUpdateDefault = true;
                                            } else {

                                                //check size
                                                if($rowFound['DATA_TYPE'] == 'nvarchar' && intval($rowFound['CHARACTER_MAXIMUM_LENGTH']) != intval($detailArr[1])){
                                                    $needToUpdateSize = true;
                                                } else if($rowFound['DATA_TYPE'] == 'decimal' && $rowFound['NUMERIC_PRECISION'] . "," . $rowFound['NUMERIC_SCALE'] != $detailArr[1]){
                                                    $needToUpdateSize = true;
                                                }  else {

                                                    //COLUMN IS FINE
                                                }
                                            }
                                        }
                                    }
                                } else {

                                }

                                if($needToUpdateDataType || $needToUpdateNullable || $needToUpdateNullable || $needToUpdateSize){

                                    if($needToUpdateNullable){
                                        if($detailArr[2] == "NO"){
                                            if(!$needToUpdateDataType){
                                                $sqlUpdate = "UPDATE " . $columnList["C_TABLE"] . " SET " . $column . " = " . $detailArr[3] . " " . 
                                                                " WHERE " . $column . " is null ";
                                                Log::write($sqlUpdate);
                                                $this->db->executeSql($sqlUpdate);
                                            }
                                            else {
                                                if(($rowFound['DATA_TYPE'] == 'text' || $rowFound['DATA_TYPE'] == 'ntext' || $rowFound['DATA_TYPE'] == 'nvarchar' || $rowFound['DATA_TYPE'] == 'varchar') && 
                                                    ($detailArr[0] == 'int' || $detailArr[0] == 'decimal')){
                                                    
                                                    $sqlUpdate = "UPDATE " . $columnList["C_TABLE"] . " SET " . $column . " = '0' " . 
                                                                    " WHERE " . $column . " is null ";
                                                    Log::write($sqlUpdate);
                                                    $this->db->executeSql($sqlUpdate);
                                                } else if(($rowFound['DATA_TYPE'] == 'int' || $rowFound['DATA_TYPE'] == 'decimal') && 
                                                        ($detailArr[0] == 'text' || $detailArr[0] == 'ntext' || $detailArr[0] == 'nvarchar' || $detailArr[0] == 'varchar')){
                                                    
                                                    $sqlUpdate = "UPDATE " . $columnList["C_TABLE"] . " SET " . $column . " = 0 " . 
                                                                    " WHERE " . $column . " is null ";
                                                    Log::write($sqlUpdate);
                                                    $this->db->executeSql($sqlUpdate);
                                                }
                                            }
                                        }
                                    } else if($needToUpdateDataType){
                                        //dropping constraint
                                        $sqlUpdateDefault = "select * from sys.columns where object_id = Object_ID(N'" . $columnList["C_TABLE"] . "') and name = '" . $column . "' ";
                                        $rowDef = $this->db->getTable($sqlUpdateDefault);

                                        if(!is_null($rowDef) && count($rowDef) > 0){
                                            $objectId = $rowDef[0]['default_object_id'];

                                            $sqlUpdateDefault = "select * from sys.default_constraints where OBJECT_NAME(parent_object_id) = '" . $columnList["C_TABLE"] . "' AND object_id = " . $objectId . " ";
                                            $rowDef = $this->db->getTable($sqlUpdateDefault);

                                            if(!is_null($rowDef) && count($rowDef) > 0){
                                                $name = $rowDef[0]['name'];

                                                $sqlUpdateDefault = " ALTER TABLE " . $columnList["C_TABLE"] . " DROP CONSTRAINT " . $name . " ";
                                                $this->db->executeSql($sqlUpdateDefault);
                                            }
                                        }
                                    }
                                    
                                    if($rowFound['DATA_TYPE'] == 'text' && $detailArr[0] == 'ntext'){
                                        $sqlTemp = "EXEC sys.sp_rename '" . $columnList["C_TABLE"] . "." . $column . "' , '" . $column . "_OLD', 'COLUMN'" ;
                                        $this->db->executeSql($sqlTemp);
                                        
                                        $sqlTemp = "ALTER TABLE " . $columnList["C_TABLE"] . " ADD " . $column . " " . 
                                                    $detailArr[0] . " " .
                                                    (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                                    ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " . 
                                                    "DEFAULT " . $detailArr[3];
                                        $this->db->executeSql($sqlTemp);
                                        
                                        $sqlTemp = "UPDATE " . $columnList["C_TABLE"] . " set " . $column . " = " . $column . "_OLD";
                                        $this->db->executeSql($sqlTemp);
                                        
                                        
                                        $sqlTemp = "ALTER TABLE " . $columnList["C_TABLE"] . " DROP COLUMN " . $column . "_OLD";
                                        $this->db->executeSql($sqlTemp);
                                    }
                                    

                                    $sqlUpdate = "ALTER TABLE " . $columnList["C_TABLE"] . " ALTER COLUMN " . $column . " " . 
                                                    $detailArr[0] . " " .
                                                    (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                                    ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " ";
                                    Log::write($rowFound['DATA_TYPE'] . " - " . $sqlUpdate);
                                    $this->db->executeSql($sqlUpdate);

                                    if($needToUpdateDefault){
                                        //dropping constraint
                                        $sqlUpdateDefault = "select * from sys.columns where object_id = Object_ID(N'" . $columnList["C_TABLE"] . "') and name = '" . $column . "' ";
                                        $rowDef = $this->db->getTable($sqlUpdateDefault);

                                        if(!is_null($rowDef) && count($rowDef) > 0){
                                            $objectId = $rowDef[0]['default_object_id'];

                                            $sqlUpdateDefault = "select * from sys.default_constraints where OBJECT_NAME(parent_object_id) = '" . $columnList["C_TABLE"] . "' AND object_id = " . $objectId . " ";
                                            $rowDef = $this->db->getTable($sqlUpdateDefault);

                                            if(!is_null($rowDef) && count($rowDef) > 0){
                                                $name = $rowDef[0]['name'];

                                                $sqlUpdateDefault = " ALTER TABLE " . $columnList["C_TABLE"] . " DROP CONSTRAINT " . $name . " ";
                                                $this->db->executeSql($sqlUpdateDefault);
                                            }
                                        }

                                        $sqlUpdateDefault = "ALTER TABLE " . $columnList["C_TABLE"] . " ADD DEFAULT " . $detailArr[3] . " FOR " . $column;
                                        Log::write("DEFAULT - " . $sqlUpdateDefault);
                                        $this->db->executeSql($sqlUpdateDefault);
                                    }
                                }
                            } else {
                                $sqlAdd = "ALTER TABLE " . $columnList["C_TABLE"] . " ADD " . $column . " " . 
                                                    $detailArr[0] . " " .
                                                    (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                                    ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " . 
                                                    "DEFAULT " . $detailArr[3] ;
                                Log::write($sqlAdd);
                                $this->db->executeSql($sqlAdd);
                            }
                        }
                    }
                } else {
                    $strColumn = "";
                    $strTable = "";
                    $strPK = "";
                    foreach($columnList as $key => $column){   
                        if ($key == "C_TABLE"){
                            $strTable = $column;
                        } else if ($key == "C_PK") {
                            $strPK = $column;
                        } else {
                            if($strColumn != "") $strColumn .= ", ";
                            
                            $detail = $columnDetailList[$key];  
                            $detailArr = explode("|", $detail); 
                            
                            if($detailArr[0] == 'identity'){
                                $strColumn .= " " . $column . " " . 
                                                    "INT IDENTITY (1,1) ";
                                
                            } else {
                                $strColumn .= " " . $column . " " . 
                                                    $detailArr[0] . " " .
                                                    (intval($detailArr[1]) > 0 ? "(" . $detailArr[1] . ")" : "" ) . " " .
                                                    ($detailArr[2] == "NO" ? "NOT NULL" : "NULL") . " " . 
                                                    "DEFAULT " . $detailArr[3];
                            }
                        }
                    }
                    
                    $sqlAddTable = "CREATE TABLE " . $strTable . " (" .
                                                    $strColumn . 
                                                    ($strPK != "" ? ", CONSTRAINT PK_" . $strTable . " PRIMARY KEY(" . $strPK . ")" : "") . ") ";
                    
                    Log::write($sqlAddTable);
                    $this->db->executeSql($sqlAddTable);
                }
                
                
            }
        } catch (Exception $e) {
            $result = $e->getMessage();
        }
        $this->db->close();
        return $result;
    }
}
?>