-- phpMyAdmin SQL Dump
-- version 3.5.8.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Dec 25, 2013 at 12:48 PM
-- Server version: 5.1.70
-- PHP Version: 5.3.27

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
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 utf8 */;

--
-- Database: `alicia_user_master`
--

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

--
-- Table structure for table `appointments`
--

CREATE TABLE IF NOT EXISTS `appointments` (
  `apm_key` varchar(32) NOT NULL,
  `apm_number` varchar(16) NOT NULL,
  `pat_key` varchar(32) NOT NULL,
  `apm_start` datetime NOT NULL,
  `apm_end` datetime NOT NULL,
  `doctor_key` varchar(32) NOT NULL,
  `room_key` varchar(32) NOT NULL,
  `note` text NOT NULL,
  `apm_status` tinyint(1) NOT NULL,
  `apm_notify` char(1) NOT NULL DEFAULT 'N',
  `apm_user` varchar(32) NOT NULL,
  `publicdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`apm_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `appointments_change`
--

CREATE TABLE IF NOT EXISTS `appointments_change` (
  `apc_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `old_date_start` datetime NOT NULL,
  `old_date_end` datetime NOT NULL,
  `new_date_start` datetime NOT NULL,
  `new_date_end` datetime NOT NULL,
  `old_doctor` varchar(32) NOT NULL,
  `new_doctor` varchar(32) NOT NULL,
  `old_room` varchar(32) NOT NULL,
  `new_room` varchar(32) NOT NULL,
  `old_note` text NOT NULL,
  `new_note` text NOT NULL,
  `change_status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0=cancel,1=change',
  `change_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`apc_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `autonumber`
--

CREATE TABLE IF NOT EXISTS `autonumber` (
  `year_1` int(2) unsigned zerofill NOT NULL,
  `year_2` int(2) unsigned zerofill NOT NULL,
  `month` int(2) unsigned zerofill NOT NULL,
  `day` int(2) unsigned zerofill NOT NULL,
  `ccount_1` int(2) unsigned zerofill NOT NULL,
  `ccount_2` int(4) unsigned zerofill NOT NULL,
  `ccount_3` int(6) unsigned zerofill NOT NULL,
  `ccount_4` int(8) unsigned zerofill NOT NULL,
  `fcount_1` int(2) unsigned zerofill NOT NULL,
  `fcount_2` int(4) unsigned zerofill NOT NULL,
  `fcount_3` int(6) unsigned zerofill NOT NULL,
  `fcount_4` int(8) unsigned zerofill NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `autonumber`
--

INSERT INTO `autonumber` (`year_1`, `year_2`, `month`, `day`, `ccount_1`, `ccount_2`, `ccount_3`, `ccount_4`, `fcount_1`, `fcount_2`, `fcount_3`, `fcount_4`) VALUES
(13, 56, 11, 27, 01, 0001, 000001, 00000001, 01, 0001, 000001, 00000001);

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

--
-- Table structure for table `clinic_settings`
--

CREATE TABLE IF NOT EXISTS `clinic_settings` (
  `cn_format` varchar(32) NOT NULL,
  `clinic_bill_text` varchar(32) NOT NULL DEFAULT 'ALI',
  `clinic_bill_style` varchar(32) NOT NULL DEFAULT '3df5629c8435422979e73f549cdd1c91',
  `clinic_bill_paper_size` varchar(32) NOT NULL DEFAULT 'c6f2f93133905f75da4b02ccc19ab66a',
  `year_format` char(2) NOT NULL COMMENT 'BE=พุทธศักราช,AD=คริสตศักราช',
  `doctor_notification` tinyint(1) NOT NULL COMMENT '0=No,1=MySettings,2=Yes',
  `patient_notification` tinyint(1) NOT NULL COMMENT '0=No,1=MySettings,2=Yes'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `clinic_settings`
--

INSERT INTO `clinic_settings` (`cn_format`, `clinic_bill_text`, `clinic_bill_style`, `clinic_bill_paper_size`, `year_format`, `doctor_notification`, `patient_notification`) VALUES
('9ac383d3923a089986ef8534de84bcc9', 'ALI', '04464f39a7a323f4efa51bd958d4f870', 'c6f2f93133905f75da4b02ccc19ab66a', 'BE', 1, 1);

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

--
-- Table structure for table `doctor`
--

CREATE TABLE IF NOT EXISTS `doctor` (
  `doctor_key` varchar(32) NOT NULL,
  `doctor_name` varchar(64) NOT NULL,
  `doctor_lastname` varchar(64) NOT NULL,
  `doctor_image` varchar(128) NOT NULL DEFAULT 'noimg.jpg',
  `doctor_cert` varchar(64) NOT NULL,
  `doctor_position` varchar(64) NOT NULL,
  `doctor_email` varchar(128) NOT NULL,
  `doctor_notify` char(1) NOT NULL DEFAULT 'N',
  `doctor_status` tinyint(1) NOT NULL,
  `public_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`doctor_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `drugs_export`
--

CREATE TABLE IF NOT EXISTS `drugs_export` (
  `exp_key` varchar(32) NOT NULL,
  `drug_key` varchar(32) NOT NULL,
  `drug_export` double NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `user_key` varchar(32) NOT NULL,
  `export_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`exp_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `drugs_import`
--

CREATE TABLE IF NOT EXISTS `drugs_import` (
  `imp_key` varchar(32) NOT NULL,
  `drug_key` varchar(32) NOT NULL,
  `drug_import` double NOT NULL,
  `user_key` varchar(32) NOT NULL,
  `import_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`imp_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `drugs_store`
--

CREATE TABLE IF NOT EXISTS `drugs_store` (
  `drug_key` varchar(32) NOT NULL,
  `treatment_key` varchar(32) NOT NULL,
  `drug_howto` text NOT NULL,
  `drug_warning` text NOT NULL,
  `drug_minimum` double NOT NULL,
  `drug_instore` double NOT NULL,
  `drug_alert` double NOT NULL,
  `drug_sub_stock` char(1) NOT NULL DEFAULT 'Y',
  `pay_with_out_drugs` char(1) NOT NULL DEFAULT 'N',
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`drug_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `finance`
--

CREATE TABLE IF NOT EXISTS `finance` (
  `fin_key` varchar(32) NOT NULL,
  `fin_number` varchar(16) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `fin_pay_type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=cash,2=credit,3=debt,4=gift',
  `fin_total` double NOT NULL,
  `fin_pay_total` double NOT NULL,
  `user_key` varchar(32) NOT NULL,
  `pay_status` tinyint(1) NOT NULL DEFAULT '0',
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`fin_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `items_categories`
--

CREATE TABLE IF NOT EXISTS `items_categories` (
  `categories_key` varchar(32) NOT NULL,
  `categories_code` varchar(16) NOT NULL,
  `categories_name` varchar(256) NOT NULL,
  `categories_color` varchar(7) NOT NULL DEFAULT '#666666',
  `categories_status` tinyint(1) NOT NULL,
  PRIMARY KEY (`categories_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `items_list`
--

CREATE TABLE IF NOT EXISTS `items_list` (
  `categories_key` varchar(32) NOT NULL,
  `treatment_key` varchar(32) NOT NULL,
  `treatment_code` varchar(16) NOT NULL,
  `treatment_name` varchar(256) NOT NULL,
  `treatment_price` double NOT NULL,
  `treatment_unit` varchar(32) NOT NULL,
  `treatment_is_drugs` char(1) NOT NULL DEFAULT 'N',
  `treatment_for_doctor` double NOT NULL,
  `treatment_for_doctor_type` tinyint(1) NOT NULL COMMENT '0=No,1=Fixed,2=Percent',
  `treatment_cost` double NOT NULL,
  `treatment_status` tinyint(1) NOT NULL,
  PRIMARY KEY (`treatment_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `patient_healthy`
--

CREATE TABLE IF NOT EXISTS `patient_healthy` (
  `pat_key` varchar(32) NOT NULL,
  `pat_weight` float NOT NULL,
  `pat_stature` float NOT NULL,
  `pat_temperature` float NOT NULL,
  `pat_pressure_low` float NOT NULL,
  `pat_pressure_high` float NOT NULL,
  `pat_smoking` tinyint(1) NOT NULL,
  `pat_drinking` tinyint(1) NOT NULL,
  `pat_diseases_type` tinyint(1) NOT NULL,
  `pat_diseases` varchar(256) NOT NULL,
  `pat_diseases_note` text NOT NULL,
  `pat_drugs_allergic` varchar(256) NOT NULL,
  `pat_drugs_allergic_note` text NOT NULL,
  `pat_lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pat_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `patient_informations`
--

CREATE TABLE IF NOT EXISTS `patient_informations` (
  `pat_key` varchar(32) NOT NULL,
  `pat_cn` varchar(16) NOT NULL,
  `pat_cid` varchar(16) NOT NULL,
  `pat_passport` varchar(32) NOT NULL,
  `pat_prefix` varchar(32) NOT NULL,
  `pat_name` varchar(64) NOT NULL,
  `pat_middle` varchar(64) NOT NULL,
  `pat_lastname` varchar(64) NOT NULL,
  `pat_photo` varchar(128) NOT NULL DEFAULT 'noimg.jpg',
  `pat_sex` varchar(8) NOT NULL,
  `pat_mstatus` varchar(16) NOT NULL,
  `pat_born` date NOT NULL,
  `pat_address` varchar(128) NOT NULL,
  `pat_subdistrict` varchar(64) NOT NULL,
  `pat_district` varchar(64) NOT NULL,
  `pat_province` varchar(64) NOT NULL,
  `pat_zipcode` varchar(10) NOT NULL,
  `pat_country` varchar(32) NOT NULL,
  `phone_home` varchar(16) NOT NULL,
  `phone_home_contact` varchar(32) NOT NULL,
  `phone_mobile` varchar(16) NOT NULL,
  `phone_mobile_contact` varchar(32) NOT NULL,
  `pat_email` varchar(128) NOT NULL,
  `pat_language` varchar(32) NOT NULL,
  `pat_create` datetime NOT NULL,
  `pat_lastvisit` datetime NOT NULL,
  `pat_lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pat_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `patient_relative`
--

CREATE TABLE IF NOT EXISTS `patient_relative` (
  `pat_key` varchar(32) NOT NULL,
  `pat_rt_name` varchar(64) NOT NULL,
  `pat_rt_lastname` varchar(64) NOT NULL,
  `pat_rt_cid` varchar(16) NOT NULL,
  `pat_rt_relative` varchar(64) NOT NULL,
  `pat_rt_address` text NOT NULL,
  `pat_rt_mobile` varchar(16) NOT NULL,
  `pat_rt_email` varchar(128) NOT NULL,
  `pat_rt_note` text NOT NULL,
  `pat_rt_last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pat_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `promotions`
--

CREATE TABLE IF NOT EXISTS `promotions` (
  `promo_key` varchar(32) NOT NULL,
  `promo_code` varchar(16) NOT NULL,
  `promo_name` varchar(64) NOT NULL,
  `promo_font_color` varchar(7) NOT NULL DEFAULT '#FF9900',
  `promo_start` date NOT NULL,
  `promo_exp` date NOT NULL,
  `promo_status` tinyint(1) NOT NULL,
  `promo_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`promo_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `promotions_items`
--

CREATE TABLE IF NOT EXISTS `promotions_items` (
  `promo_key` varchar(32) NOT NULL,
  `treatment_key` varchar(32) NOT NULL,
  `promo_price` double NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `room`
--

CREATE TABLE IF NOT EXISTS `room` (
  `room_key` varchar(32) NOT NULL,
  `room_name` varchar(32) NOT NULL,
  `room_des` text NOT NULL,
  `room_status` tinyint(1) NOT NULL,
  `room_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`room_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `treatments_drugs`
--

CREATE TABLE IF NOT EXISTS `treatments_drugs` (
  `tmd_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `treatment_key` varchar(32) NOT NULL,
  `tmd_how_to` text NOT NULL,
  `tmd_spend` double NOT NULL,
  `tmd_price` double NOT NULL,
  `treatment_unit` varchar(32) NOT NULL,
  `user_key` varchar(32) NOT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tmd_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `treatments_file`
--

CREATE TABLE IF NOT EXISTS `treatments_file` (
  `tmf_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `tmf_title` varchar(256) NOT NULL,
  `tmf_file` varchar(256) NOT NULL,
  `tmf_ext` varchar(128) NOT NULL,
  `tmf_size` varchar(128) NOT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tmf_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `treatments_history`
--

CREATE TABLE IF NOT EXISTS `treatments_history` (
  `tmh_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `tmh_text` text NOT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tmh_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `treatments_payment`
--

CREATE TABLE IF NOT EXISTS `treatments_payment` (
  `tmp_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `treatment_key` varchar(32) NOT NULL,
  `tmp_count` double NOT NULL DEFAULT '1',
  `tmp_price_per_count` double NOT NULL,
  `item_is_promotion` char(1) NOT NULL DEFAULT 'N',
  `user_key` varchar(32) NOT NULL,
  `insert_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`tmp_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `treatments_tooth`
--

CREATE TABLE IF NOT EXISTS `treatments_tooth` (
  `tmt_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `tooth_key` varchar(16) NOT NULL,
  PRIMARY KEY (`tmt_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `treatment_for_doctor`
--

CREATE TABLE IF NOT EXISTS `treatment_for_doctor` (
  `tfd_key` varchar(32) NOT NULL,
  `apm_key` varchar(32) NOT NULL,
  `doctor_key` varchar(32) NOT NULL,
  `treatment_key` varchar(32) NOT NULL,
  `treatment_for_type` tinyint(1) NOT NULL,
  `treatment_how_much` double NOT NULL,
  `doctor_treatment_price` double NOT NULL,
  `tfd_count` double NOT NULL DEFAULT '1',
  `pay_doctor_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0=pandding,1=pay,2=cancel',
  `tfd_status` tinyint(1) NOT NULL DEFAULT '1',
  `tfd_insert` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`tfd_key`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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