-- phpMyAdmin SQL Dump
-- version 4.7.9
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 06, 2020 at 09:08 AM
-- Server version: 10.1.31-MariaDB
-- PHP Version: 5.6.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `villa_belissa`
--

-- --------------------------------------------------------

--
-- Table structure for table `buyer_status_folder_bank`
--

CREATE TABLE `buyer_status_folder_bank` (
  `ReservationId` bigint(255) NOT NULL,
  `Facility` int(5) DEFAULT NULL,
  `DateDelivered` date DEFAULT NULL,
  `ReturnedDate` date DEFAULT NULL,
  `BankOfficer` char(255) DEFAULT NULL,
  `LOG` date DEFAULT NULL,
  `Booking` date DEFAULT NULL,
  `TakeOut` date DEFAULT NULL,
  `Reason` text,
  `CreatedDate` timestamp NULL DEFAULT NULL,
  `CreatedBy` char(50) DEFAULT NULL,
  `UpdatedDate` timestamp NULL DEFAULT NULL,
  `UpdatedBy` char(50) DEFAULT NULL,
  `Status` char(10) DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `construction_and_utilities`
--

CREATE TABLE `construction_and_utilities` (
  `UnitId` bigint(255) NOT NULL,
  `Block` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Lot` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Road` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `SideWalkGutter` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Meralco` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Water` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `HouseConstruction` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `HouseConstructionPercentage` decimal(10,2) DEFAULT NULL,
  `CreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatedBy` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedBy` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contractor`
--

CREATE TABLE `contractor` (
  `Id` bigint(255) NOT NULL,
  `Contractor` varchar(255) DEFAULT NULL,
  `ContactPerson` varchar(255) DEFAULT NULL,
  `ContactNo` char(100) DEFAULT NULL,
  `EmailAddress` char(255) DEFAULT NULL,
  `Address` text,
  `Status` char(10) DEFAULT 'active',
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatedBy` char(50) DEFAULT NULL,
  `UpdatedDate` timestamp NULL DEFAULT NULL,
  `UpdatedBy` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `conversion`
--

CREATE TABLE `conversion` (
  `ReservationId` bigint(255) NOT NULL,
  `DueConversion` date DEFAULT NULL,
  `ReleasedPagibig` date DEFAULT NULL,
  `ProcessedDate` date DEFAULT NULL,
  `ReleasedDate` date DEFAULT NULL,
  `CreatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `CreatedBy` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UpdatedDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedBy` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `permits_and_licenses`
--

CREATE TABLE `permits_and_licenses` (
  `Id` bigint(255) NOT NULL,
  `Block` char(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Lot` char(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `TaxDecLandProcessed` date DEFAULT NULL,
  `TaxDecLandReleased` date DEFAULT NULL,
  `TaxDecBuildingProcessed` date DEFAULT NULL,
  `TaxDecBuildingReleased` date DEFAULT NULL,
  `TaxReceiptLandProcessed` date DEFAULT NULL,
  `TaxReceiptLandReleased` date DEFAULT NULL,
  `TaxReceiptBuildingProcessed` date DEFAULT NULL,
  `TaxReceiptBuildingReleased` date DEFAULT NULL,
  `BuildingPermitProcessed` date DEFAULT NULL,
  `BuildingPermitReleased` date DEFAULT NULL,
  `ElectricalPermitProcessed` date DEFAULT NULL,
  `ElectricalPermitReleased` date DEFAULT NULL,
  `SanitaryPermitProcessed` date DEFAULT NULL,
  `SanitaryPermitReleased` date DEFAULT NULL,
  `OccupancyPermitProcessed` date DEFAULT NULL,
  `OccupancyPermitReleased` date DEFAULT NULL,
  `CreatedBy` char(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CreatedDate` date DEFAULT NULL,
  `UpdatedBy` char(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UpdatedDate` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `releases_to_broker`
--

CREATE TABLE `releases_to_broker` (
  `ReservationId` bigint(255) NOT NULL DEFAULT '0',
  `DateRFDeposited` date DEFAULT NULL,
  `Reservation` decimal(10,2) DEFAULT NULL,
  `LessCA` decimal(10,2) DEFAULT NULL,
  `ReservationLessCA` decimal(10,2) DEFAULT NULL,
  `TCP` decimal(10,2) DEFAULT NULL,
  `LessDiscount` decimal(10,2) DEFAULT NULL,
  `MiscExpenses` decimal(10,2) DEFAULT NULL,
  `MiscExpensesRate` varchar(255) DEFAULT NULL,
  `BasisCommission` decimal(10,2) DEFAULT NULL,
  `Rate` varchar(255) DEFAULT NULL,
  `Commission` decimal(10,2) DEFAULT NULL,
  `WHT` varchar(255) DEFAULT NULL,
  `FirstReleaseCheckbox` varchar(5) DEFAULT NULL,
  `FirstReleaseType` varchar(10) NOT NULL DEFAULT '1st',
  `CACheckbox` varchar(5) DEFAULT NULL,
  `FirstReleaseAmount` decimal(10,2) DEFAULT NULL,
  `FirstReleaseWHT` decimal(10,2) DEFAULT NULL,
  `FirstReleaseCash` decimal(10,2) DEFAULT NULL,
  `FirstReleaseNet` decimal(10,2) DEFAULT NULL,
  `FirstReleaseDate` date DEFAULT NULL,
  `FirstReleaseCheck` varchar(255) DEFAULT NULL,
  `SecondReleaseCheckbox` varchar(5) DEFAULT NULL,
  `SecondReleaseType` varchar(10) NOT NULL DEFAULT '2nd',
  `SecondReleaseAmount` decimal(10,2) DEFAULT NULL,
  `SecondReleaseWHT` decimal(10,2) DEFAULT NULL,
  `SecondReleaseCash` decimal(10,2) DEFAULT NULL,
  `SecondReleaseNet` decimal(10,2) DEFAULT NULL,
  `SecondReleaseDate` date DEFAULT NULL,
  `SecondReleaseCheck` varchar(255) DEFAULT NULL,
  `ThirdReleaseCheckbox` varchar(5) DEFAULT NULL,
  `ThirdReleaseType` varchar(10) NOT NULL DEFAULT '3rd',
  `ThirdReleaseAmount` decimal(10,2) DEFAULT NULL,
  `ThirdReleaseWHT` decimal(10,2) DEFAULT NULL,
  `ThirdReleaseCash` decimal(10,2) DEFAULT NULL,
  `ThirdReleaseNet` decimal(10,2) DEFAULT NULL,
  `ThirdReleaseDate` date DEFAULT NULL,
  `ThirdReleaseCheck` varchar(255) DEFAULT NULL,
  `TotalReleaseAmount` decimal(10,2) DEFAULT NULL,
  `TotalReleaseWHT` decimal(10,2) DEFAULT NULL,
  `TotalReleaseCash` decimal(10,2) DEFAULT NULL,
  `TotalReleaseNet` decimal(10,2) DEFAULT NULL,
  `ReleasesStatus` varchar(255) DEFAULT NULL,
  `ReleasesRemarks` text,
  `CreatedDate` timestamp NULL DEFAULT NULL,
  `CreatedBy` char(50) DEFAULT NULL,
  `UpdatedDate` timestamp NULL DEFAULT NULL,
  `UpdatedBy` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `releases_to_contractor`
--

CREATE TABLE `releases_to_contractor` (
  `Id` bigint(255) NOT NULL,
  `DatePOIssued` date DEFAULT NULL,
  `DateIssued` date DEFAULT NULL,
  `RetentionProcessed` date DEFAULT NULL,
  `ReleasesStatus` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ReleasesRemarks` text COLLATE utf8mb4_unicode_ci,
  `Contractor` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Block` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Lot` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Units` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ContractPrice` decimal(10,2) DEFAULT NULL,
  `WHT` decimal(10,2) DEFAULT NULL,
  `NetContractPrice` decimal(10,2) DEFAULT NULL,
  `Details` text COLLATE utf8mb4_unicode_ci,
  `CreatedDate` date DEFAULT NULL,
  `CreatedBy` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UpdatedDate` date DEFAULT NULL,
  `UpdatedBy` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `releases_to_contractor_details`
--

CREATE TABLE `releases_to_contractor_details` (
  `id` bigint(11) NOT NULL,
  `release_contractor_id` bigint(10) DEFAULT NULL,
  `releases_checkbox` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `releases` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `accomplishment_percentage` decimal(10,2) DEFAULT NULL,
  `accomplishment_amount` decimal(10,2) DEFAULT NULL,
  `wht` decimal(10,2) DEFAULT NULL,
  `recoup` decimal(10,2) DEFAULT NULL,
  `retention` decimal(10,2) DEFAULT NULL,
  `electricity` decimal(10,2) DEFAULT NULL,
  `water` decimal(10,2) DEFAULT NULL,
  `others` decimal(10,2) DEFAULT NULL,
  `net` decimal(10,2) DEFAULT NULL,
  `date_processed` date DEFAULT NULL,
  `check_no` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `units`
--

CREATE TABLE `units` (
  `Id` bigint(255) NOT NULL,
  `Block` char(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Lot` char(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CreatedBy` char(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CreatedDate` timestamp NULL DEFAULT NULL,
  `UpdatedBy` char(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `UpdatedDate` timestamp NULL DEFAULT NULL,
  `Status` char(10) COLLATE utf8mb4_unicode_ci DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `buyer_status_folder_bank`
--
ALTER TABLE `buyer_status_folder_bank`
  ADD PRIMARY KEY (`ReservationId`);

--
-- Indexes for table `construction_and_utilities`
--
ALTER TABLE `construction_and_utilities`
  ADD PRIMARY KEY (`UnitId`);

--
-- Indexes for table `contractor`
--
ALTER TABLE `contractor`
  ADD PRIMARY KEY (`Id`),
  ADD KEY `Broker` (`Contractor`);

--
-- Indexes for table `conversion`
--
ALTER TABLE `conversion`
  ADD PRIMARY KEY (`ReservationId`);

--
-- Indexes for table `permits_and_licenses`
--
ALTER TABLE `permits_and_licenses`
  ADD PRIMARY KEY (`Id`);

--
-- Indexes for table `releases_to_broker`
--
ALTER TABLE `releases_to_broker`
  ADD PRIMARY KEY (`ReservationId`);

--
-- Indexes for table `releases_to_contractor`
--
ALTER TABLE `releases_to_contractor`
  ADD PRIMARY KEY (`Id`);

--
-- Indexes for table `releases_to_contractor_details`
--
ALTER TABLE `releases_to_contractor_details`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `buyer_status_folder_bank`
--
ALTER TABLE `buyer_status_folder_bank`
  MODIFY `ReservationId` bigint(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `contractor`
--
ALTER TABLE `contractor`
  MODIFY `Id` bigint(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `permits_and_licenses`
--
ALTER TABLE `permits_and_licenses`
  MODIFY `Id` bigint(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `releases_to_contractor`
--
ALTER TABLE `releases_to_contractor`
  MODIFY `Id` bigint(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `releases_to_contractor_details`
--
ALTER TABLE `releases_to_contractor_details`
  MODIFY `id` bigint(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

ALTER TABLE `buyer_payment_monitoring` ADD `Financing` CHAR(10) NULL AFTER `UpdatedBy`;

ALTER TABLE `monitoring_folders` ADD `HouseConstructionPercentage` DECIMAL(10,2) NULL AFTER `UpdatedBy`;

ALTER TABLE `checklist` ADD `ITRDate` DATE NULL AFTER `Status`, ADD `OtherField` TEXT NULL AFTER `ITRDate`, ADD `Co1ITRDate` DATE NULL AFTER `OtherField`, ADD `Co1OtherField` TEXT NULL AFTER `Co1ITRDate`, ADD `Co2ITRDate` DATE NULL AFTER `Co1OtherField`, ADD `Co2OtherField` TEXT NULL AFTER `Co2ITRDate`, ADD `Co3ITRDate` DATE NULL AFTER `Co2OtherField`, ADD `Co3OtherField` TEXT NULL AFTER `Co3ITRDate`;

INSERT INTO `user` (`FirstName`, `LastName`, `EmailAddress`, `Username`, `MobileNo`, `Password`, `IsBroker`, `BrokerDate`, `BrokerLicense`, `RegisteredDate`, `Status`, `ActivationLink`, `UserType`, `CreatedBy`, `CreatedDate`, `UpdatedBy`, `UpdatedDate`, `SeqNo`) VALUES
('Accounting', 'Officer', NULL, 'accounting_officer', NULL, '3526fb90818992ae1e521a652100e224', NULL, NULL, NULL, NULL, 'active', NULL, 'accounting_officer', 'admin', '2020-09-06 07:39:22', NULL, NULL, NULL),
('Liaison', '', 'Officer', 'liaison', NULL, '3526fb90818992ae1e521a652100e224', NULL, NULL, NULL, NULL, 'active', NULL, 'liaison', 'admin', '2020-09-06 07:39:43', NULL, NULL, NULL);
