-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 12, 2020 at 03:33 PM
-- Server version: 10.1.29-MariaDB
-- PHP Version: 7.2.0

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 */;

--
-- Structure for view `first_view`
--
-- DROP TABLE IF EXISTS `first_view`;

-- CREATE  VIEW `first_view`  AS  (select `transactions`.`description` AS `description`,`transactions`.`facilityId` AS `facilityId`,`transactions`.`transaction_source` AS `Account_head`,`transactions`.`amount` AS `credit`,0 AS `debit`,`transactions`.`transaction_id` AS `transaction_id`,`transactions`.`modeOfPayment` AS `mode`,cast(`transactions`.`createdAt` as date) AS `day`,NULL AS `head` from `transactions` where `transactions`.`transaction_source` in (select distinct `transactions`.`transaction_source` from `transactions`)) ;

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

--
-- Structure for view `revenue_view`
--
-- DROP TABLE IF EXISTS `revenue_view`;

-- CREATE  VIEW `revenue_view`  AS  select `a`.`subhead` AS `subHead`,`b`.`description` AS `description`,`b`.`modeOfPayment` AS `modeOfPayment`,`b`.`destination` AS `destination`,`b`.`createdAt` AS `createdAt`,`b`.`debited` AS `debited` from (`account` `a` join `transactions` `b`) where (`a`.`head` = `b`.`destination`) ;

--
-- Indexes for dumped tables
--

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

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

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

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

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

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

--
-- Indexes for table `drugpurchaserecords`
--
ALTER TABLE `drugpurchaserecords`
  ADD PRIMARY KEY (`drug`,`cost_price`,`expiry_date`);

--
-- Indexes for table `drugs`
--
ALTER TABLE `drugs`
  ADD PRIMARY KEY (`drug_id`);

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

--
-- Indexes for table `lab`
--
ALTER TABLE `lab`
  ADD PRIMARY KEY (`test_id`);

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

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

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

--
-- Indexes for table `prescriptionrequests`
--
ALTER TABLE `prescriptionrequests`
  ADD PRIMARY KEY (`drug_request_id`);

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

--
-- Indexes for table `services`
--
ALTER TABLE `services`
  ADD PRIMARY KEY (`service_id`);

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

--
-- Indexes for table `transactions`
--
-- ALTER TABLE `transactions`
--   ADD PRIMARY KEY (`transaction_id`);

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

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `account`
--
ALTER TABLE `account`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=68;

--
-- AUTO_INCREMENT for table `diagnosis`
--
ALTER TABLE `diagnosis`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=70;

--
-- AUTO_INCREMENT for table `dieselrefuel`
--
ALTER TABLE `dieselrefuel`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `dieselusage`
--
ALTER TABLE `dieselusage`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `dispensary`
--
ALTER TABLE `dispensary`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=120;

--
-- AUTO_INCREMENT for table `drugs`
--
ALTER TABLE `drugs`
  MODIFY `drug_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=508;

--
-- AUTO_INCREMENT for table `lab`
--
ALTER TABLE `lab`
  MODIFY `test_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32;

--
-- AUTO_INCREMENT for table `labservices`
--
ALTER TABLE `labservices`
  MODIFY `id` int(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT for table `patientfileinfo`
--
ALTER TABLE `patientfileinfo`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `prescriptionrequests`
--
ALTER TABLE `prescriptionrequests`
  MODIFY `drug_request_id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=66;

--
-- AUTO_INCREMENT for table `services`
--
ALTER TABLE `services`
  MODIFY `service_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;

--
-- AUTO_INCREMENT for table `suppliersinfo`
--
ALTER TABLE `suppliersinfo`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `transactionsetup`
--
-- ALTER TABLE `transactionsetup`
-- MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `transfers`
--
ALTER TABLE `transfers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=42;
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 */;
