ToDO: operatorguest.php in there the prsid is original prsid... probably need to convert ...
Create database
===============
CREATE DATABASE `creative_dbblanc` ;
Need to go to cpanel to add User "creative_root" to dbase "creative_dbblanc"
CREATE TABLE `orders` ( this is for Stripe Payment gateway
`id` int(11) NOT NULL,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`item_number` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`item_price` float(10,2) NOT NULL,
`item_price_currency` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`paid_amount` float(10,2) NOT NULL,
`paid_amount_currency` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`txn_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`checkout_session_id` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`payment_status` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `creative_dbblanc`.`ticketsale` (
`tksid` INT NOT NULL AUTO_INCREMENT ,
`tksbookid` INT ,
`tksskitem` VARCHAR( 100 ) NOT NULL ,
`tksskqty` INT NOT NULL DEFAULT '0',
`tksstatus` VARCHAR( 2 ) DEFAULT '0',
`tksdateadd` VARCHAR( 8 ) ,
PRIMARY KEY ( `tksid` ) ,
INDEX ( `tksdateadd`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`products` (
`prsid` INT NOT NULL AUTO_INCREMENT ,
`prsname` VARCHAR( 50 ) NOT NULL ,
`prsgender` VARCHAR( 6 ) ,
`prscountry` VARCHAR( 50 ) ,
`prsdatecreated` VARCHAR( 50 ) ,
`prsemail` VARCHAR( 50 ) ,
`prsphone` VARCHAR( 50 ) ,
`prspassport` VARCHAR( 50 ) ,
`prsdob` VARCHAR( 8 ) ,
`prsarrivaldate` VARCHAR( 8 ) NOT NULL ,
`prsarrivaldateyear` VARCHAR( 4 ) NOT NULL ,
`prsarrivaldatemonth` VARCHAR( 2 ) NOT NULL ,
`prsarrivaldateday` VARCHAR( 2 ) NOT NULL ,
`prsarrivaltime` VARCHAR( 5 ) NOT NULL ,
`prsnodays` INT NOT NULL ,
`prsenddate` VARCHAR( 8 ) NOT NULL ,
`prsenddateyear` VARCHAR( 4 ) NOT NULL ,
`prsenddatemonth` VARCHAR( 2 ) NOT NULL ,
`prsenddateday` VARCHAR( 2 ) NOT NULL ,
`prsroomtype` VARCHAR( 50 ) NOT NULL ,
`prsroomtypeid` VARCHAR( 2 ) NOT NULL ,
`prspriceplanid` VARCHAR( 3 ) NOT NULL ,
`prsnopeople` INT NOT NULL ,
`prsquantity` INT NOT NULL ,
`prsstatus` VARCHAR( 2 ) DEFAULT '0',
`prsdateadd` INT NOT NULL ,
`prsdatemodify` INT ,
`prsoutlet` VARCHAR( 50 ) ,
`prstotalprice` FLOAT(5.2) ,
`prsuserid_operatorbook` VARCHAR( 50 ) ,
`prsuserid_operatorreject` VARCHAR( 50 ) ,
`prsuserid_operatorconfirm1` VARCHAR( 50 ) ,
`prsuserid_operatorcancel2` VARCHAR( 50 ) ,
`prsspecialrequest` text ,
`prsremark` text ,
`prsspecialattention` VARCHAR( 2 ) DEFAULT 'N',
`prsnodaysactual` INT ,
`prsquantityactual` INT ,
`prstotalpriceactual` FLOAT(5.2) ,
`prsuserid_operatormodify` VARCHAR( 50 ) ,
`prsdemandflag` VARCHAR( 2 ) DEFAULT 'N',
`prsdemand_user` VARCHAR( 50 ) ,
`prsdemandguestrequest` text ,
`prsdemandremark` text ,
`prsbookstatus` VARCHAR( 2 ) DEFAULT '0',
`prscashtocollect` FLOAT(5.2) ,
`prsagentreference` VARCHAR( 50 ) ,
`prsairbnbroom` VARCHAR( 8 ) ,
`prsagentfee` FLOAT(5.2) ,
`prsagentprepaidamount` FLOAT(5.2) ,
`prsprepaidamountreceived` FLOAT(5.2) ,
`prscashreceived` FLOAT(5.2) ,
`prs_paypal_total` FLOAT(5.2) ,
`prs_paypal_token` VARCHAR( 50 ) ,
`prs_paypal_payer_id` VARCHAR( 50 ) ,
`prs_paypal_authorization_id` VARCHAR( 50 ) ,
`prs_paypal_transaction_id` VARCHAR( 50 ) ,
`prs_payment_status` VARCHAR( 10 ) ,
`prs_payment_captured_date` VARCHAR( 8 ) ,
`prs_refund_total` FLOAT(5.2) ,
`prs_refund_description` VARCHAR( 100 ) ,
`prs_refund_date` VARCHAR( 8 ) ,
`prs_internet_checked_in_flag` VARCHAR( 50 ) ,
`prs_self_check_in_flag` VARCHAR( 1 ) ,
`prsphysicalcheckinstatus` VARCHAR( 1 ) ,
`prssource` VARCHAR( 20 ) ,
`prscreditcard` VARCHAR( 100 ) ,
`prsunsubscribe` VARCHAR( 1 ) ,
`prsdirectbookflag` VARCHAR( 1 ) DEFAULT '0',
PRIMARY KEY ( `prsid` ) ,
INDEX ( `prsarrivaldate`) ,
INDEX ( `prsarrivaldateyear`,`prsarrivaldatemonth`,`prsarrivaldateday`) ,
INDEX ( `prsenddate`) ,
INDEX ( `prsenddateyear`,`prsenddatemonth`,`prsenddateday`) ,
INDEX ( `prsdateadd`) ,
INDEX ( `prs_paypal_token`) ,
INDEX ( `prsagentreference`)
) ENGINE = MYISAM ;
IMPORTANT: prsarrivaldate DATA cannot be changed. REASON: this is startdate used as benchmark references to many programs.
prsspecialattention means Hotel Remark needs attention. (Flag, N-Nothing, Y-Yes, need attention; D-Done, closed
prssource eg korea means booknig came from korean booking
prsphysicalcheckinstatus "", "Y", "N"
prs_internet_checked_in_flag show guests has ever click SUBMIT button before.
prs_self_check_in_flag show guest personally select YES/NO if they want self check in.
prs_payment_status: Not-Paid (blank), Authorised, Paid, Void, Refunded
prs_paypal_total to store sum of multiple roomtype bookings in one-go.
prscashtocollect used to store cash payment received over-counter.
prsagentfee as agent fee 10% or 20% paid to agent
prsagentprepaidamount as agent receive from customer and will in term pay to Blanc Inn
prsprepaidamountreceived as "guest pre-paid" payment received by Blanc Inn
prscashreceived as payment received at Blanc Inn, after guest arrived.
prsroomtype is used to store priceplan, prspriceplanid new added.
Agentbooking: status = 3, prsuserid_operatorbook = guest, prsuserid_operatorconfirm1 =xxx-a
TongWahwebbooking: status = 2, prsuserid_operatorbook = guest
Paypal booking, status from 0 to 2, status "1" become no-use.
prsstatus:
- 0=Booking Entered => send reminder email ...
- 1=Booking Awaiting Hostel Confirmation, lookup for email reply (guest softBook) . After Paypal, this status no-use.
- 2=Boking Confirmed (operator hardbook), => operator act, send email to inform guest
- 3=manualbook by operator, also, enter through bookingagent.php
- 23=Historical Confirmed Bookings
- C0=softcancel by guest(for future date case) => guest act, no further action
- C1=softcancel by guest(for future date case) => guest act, no further action
- C2=hardcancel by guest(for future date case) => guest act, drop onto operator screen
- CC=Cancel from operator1.php acknowledge screen(for future date case),
- CM=Cancel from opertor3xx screens (I think)
- R1=reject by operator(for future date case) because full house etc, => operator reject, send email to inform guest
- M2=Modify for internet confirm(for past due case)
- M3=Modify for operator confirm(for past due case)
bookingc.php => display all (FUTURE DATE) prsstatus = 0, 1 , 2, C , R, M cases
For status = 0 => Confirm button, 0 -> 1 OR Cancel button,0 -> C1 or 1 ->
For status = 1 => Cancel button, if status 1 -> C1
For status = 2 => Cancel button, if status 2 -> C2
(This is for AUTHORISATION etc)
CREATE TABLE `creative_dbblanc`.`paypal` (
`pp_id` INT NOT NULL AUTO_INCREMENT ,
`pp_status` VARCHAR( 10 ) ,
`pp_date` VARCHAR( 8 ) ,
`pp_time` VARCHAR( 4 ) ,
`pp_type` VARCHAR( 10 ) ,
`pp_amount` FLOAT(5.2) ,
`pp_token` VARCHAR( 50 ) ,
`pp_payer_id` VARCHAR( 50 ) ,
`pp_authorization_id` VARCHAR( 50 ) ,
`pp_transaction_id` VARCHAR( 50 ) ,
`pp_book_id` VARCHAR( 50 ) ,
PRIMARY KEY ( `pp_id`)
) ENGINE = MYISAM
CREATE TABLE `creative_dbblanc`.`credit_paypal_validate` (
`cpvprsid` INT NOT NULL AUTO_INCREMENT ,
`cpvprsbookid` VARCHAR( 50 ) ,
`cpvprsagentreference` VARCHAR( 50 ) ,
`cpvprsname` VARCHAR( 50 ) ,
`cpvprsstatus` VARCHAR( 2 ) DEFAULT '0',
`cpvprs_paypal_total` FLOAT(5.2) ,
`cpvprs_paypal_token` VARCHAR( 50 ) ,
`cpvprs_paypal_payer_id` VARCHAR( 50 ) ,
`cpvprs_paypal_authorization_id` VARCHAR( 50 ) ,
`cpvprs_paypal_transaction_id` VARCHAR( 50 ) ,
`cpvprs_payment_status` VARCHAR( 10 ) ,
`cpvprs_payment_captured_date` VARCHAR( 8 ) ,
`cpvprsdatecreated` VARCHAR( 50 ) ,
`cpvprsdateadd` INT NOT NULL ,
`cpvprsuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `cpvprsid` )
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`roomtype` (
`rtid` INT NOT NULL AUTO_INCREMENT ,
`rtpriceplanid` VARCHAR( 3 ) NOT NULL ,
`rtroomtypeid` VARCHAR( 2 ) NOT NULL ,
`rtoutlet` VARCHAR( 50 ) NOT NULL ,
`rtroomtype` VARCHAR( 50 ) NOT NULL ,
`rtroomtypecn` VARCHAR( 50 ) NOT NULL ,
`rtdefaultprice` INT ,
`rtpriceweekend` INT ,
`rtdefaultroomtypecapacity` INT ,
`rtstatus` VARCHAR( 1 ) DEFAULT 'A' ,
`rtremark` VARCHAR( 100 ) ,
`rtadatedd` VARCHAR( 8 ) ,
`rtdatemodify` INT ,
`rtuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `rtid`) ,
UNIQUE (`rtoutlet`,`rtpriceplanid`)
) ENGINE = MYISAM ;
rtstatus (A-Alcove, S-Suite (both roomtype and priceplan), I-Inactive, P-PricePlan only, 0 zero is for invalid status)
(roomtype)rtroomtypeid == (dateroom)dtroomtypeid
rtroomtypecn to put Chinese roomtype
rtpriceweekend for price from Mon-Thurs
============
INTRODUCE GROUP RESERVATION to Suite Private Room
1/ room (rmsuite) 4 records
2a/ roomtype rtstatus (A-Alcove, S-Suite), rtroomtypeid(priceid): SA(S10), SB(S9), SC(S6), SDS14),
2b/ roomtype rtstatus (G-Group), rtroomtypeid(priceplanid): GG(G7, ..... G11,...)
3/ products， products.prsroomtypeid pre-requisite: room's roomtype_id need to be set first, then booking under that roomtype_id.
4/ datetype (4 new types * days)
dateroombook (4 new rooms * days) ==> NO NEED
5/ operatordatetypecapacitysqlupdate.php, operatordatetypestatussqlupdate.php program enhanced
When SA (for example) Room (1,2,3,4) all empty => dtstatus=0 dtacknowledgestatus=0 (cell colour shows background_colour)
When SA (for example) Room (1,2,3,4) any NOT empty => dtstatus=2 dtacknowledgestatus=2 (cell colour shows green)
================
CREATE TABLE `creative_dbblanc`.`datetype` (
`dtid` INT NOT NULL AUTO_INCREMENT ,
`dtdate` VARCHAR( 8 ) NOT NULL ,
`dtdateyear` VARCHAR( 4 ) NOT NULL ,
`dtdatemonth` VARCHAR( 2 ) NOT NULL ,
`dtoutlet` VARCHAR( 50 ) NOT NULL ,
`dtroomtypeid` VARCHAR( 3 ) NOT NULL ,
`dtcapacity` INT NOT NULL ,
`dtcapacityint` INT NOT NULL ,
`dtavailable` INT NOT NULL ,
`dttotalbed` INT NOT NULL ,
`dtoverrideflag` VARCHAR( 1 ) DEFAULT 'N' ,
`dtbookedhard` INT NOT NULL DEFAULT '0' ,
`dtcancelled` INT NOT NULL DEFAULT '0' ,
`dtadatedd` VARCHAR( 8 ) NOT NULL ,
`dtdatemodify` INT ,
`dtuserid` VARCHAR( 50 ) ,
`dtdefaultprice` INT ,
`dtactualprice` INT ,
`dtdifferenceprice` INT ,
`dtstatus` VARCHAR( 1 ) DEFAULT '0' ,
`dtacknowledgestatus` VARCHAR( 1 ) DEFAULT '0' ,
`dtacknowledgeremark` TEXT ,
`dtagodacloseall` VARCHAR( 1 ) NOT NULL DEFAULT 'N' ,
`dthostelworldcloseall` VARCHAR( 1 ) NOT NULL DEFAULT 'N' ,
`dtagentkiv` VARCHAR( 1 ) NOT NULL DEFAULT 'N' , ==> use as KIV flag, 2014 July
`dtasiaroomcloseall` VARCHAR( 1 ) NOT NULL DEFAULT 'N' ,
`dtbookingcloseall` VARCHAR( 1 ) NOT NULL DEFAULT 'N' , ==> No use, 2014 July
`dtctripcloseall` VARCHAR( 1 ) NOT NULL DEFAULT 'N' , ==> No use, 2014 July
PRIMARY KEY ( `dtid`) ,
INDEX ( `dtdate`) ,
INDEX ( `dtdate`,`dtroomtypeid`)
) ENGINE = MYISAM ;
dtdefaultprice is price sold at agents
dtactualprice is price we want to sell on Paypal internet
dtdifferenceprice is always 1.0; forgot (20131104) why so ?
dttotalbed: total bed for whole hostel for that day
dtavailable: Theoretical available that can be booked.
dtoverrideflag = Y if dtcapacity != 0 and yet put dtcapacityint = 0
dtcapacityint: what is available for internet booking, slave of dtcapacity
dtcapacity: what is available for internet booking
dtbookedhard: what is booked but have not alloted.
dtcancelled not used
dtstatus: Computer recommend Agent Close Availability status
0 - nothing
1 - Partial Close OR KIV ;
2 - I closed all agent sites' room
dtacknowledgestatus (Has Manager do something flag):
0 - nothing
1 - Partial Close ;
2 - I closed all agent sites' room
3 - KIV
Suggestion: Instead of fix percentage of 50% room allocated to Agoda, go by number of rooms allocated to Agoda (only).
P1: 4 (Capacity 2)
P2: 4 (Capacity 4)
P3: 1 (Capacity 2)
Q2: 5 (Capacity 7)
Q4: 1 (Capacity 2)
P5: 1 (Capacity 0.5)
P6: 1 (Capacity 0.5)
CREATE TABLE `creative_dbblanc`.`room` (
`rmid` INT NOT NULL AUTO_INCREMENT ,
`rmno` VARCHAR( 3 ) NOT NULL ,
`rmsuite` VARCHAR( 3 ) ,
`rmdoorcode` VARCHAR( 3 ) ,
`rmcabinetcode` VARCHAR( 3 ) ,
`rmremark` VARCHAR( 100 ) ,
`rmoutlet` VARCHAR( 50 ) ,
`rmdata` VARCHAR( 100 ) ,
`rmadatedd` VARCHAR( 8 ) ,
`rmdefaultroomtypeid` VARCHAR( 2 ) ,
`rmstatus` VARCHAR( 1 ) DEFAULT 'A' ,
`rmcleanstatus` VARCHAR( 4 ) DEFAULT 'OK' ,
`rmdatemodify` INT ,
`rmuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `rmid`) ,
UNIQUE (`rmoutlet`,`rmno`)
) ENGINE = MYISAM ;
Note: rmdefaultroomtypecapacity is synchronised taken from RoomType table
rmremark only visible on operator8.php screen
rmstatus "A" for Alcove, "S" for Suite
CREATE TABLE `creative_dbblanc`.`dateroombook` (
`drbid` INT NOT NULL AUTO_INCREMENT ,
`drbdate` VARCHAR( 8 ) NOT NULL ,
`drbdateyear` VARCHAR( 4 ) NOT NULL ,
`drbdatemonth` VARCHAR( 2 ) NOT NULL ,
`drbdateday` VARCHAR( 4 ) NOT NULL ,
`drboutlet` VARCHAR( 50 ) NOT NULL ,
`drbrmno` VARCHAR( 3 ) NOT NULL ,
`drbroomtypeid` VARCHAR( 2 ) ,
`drbroomtypecapacity` INT ,
`drbremark` VARCHAR( 100 ) ,
`drbbed1` INT DEFAULT '0',
`drbbed2` INT DEFAULT '0',
`drbbed3` INT DEFAULT '0',
`drbbed4` INT DEFAULT '0',
`drbbed5` INT DEFAULT '0',
`drbbed6` INT DEFAULT '0',
`drbbed7` INT DEFAULT '0',
`drbbed8` INT DEFAULT '0',
`drbbed1pay` VARCHAR( 20 ) ,
`drbbed2pay` VARCHAR( 20 ) ,
`drbbed3pay` VARCHAR( 20 ) ,
`drbbed4pay` VARCHAR( 20 ) ,
`drbbed5pay` VARCHAR( 20 ) ,
`drbbed6pay` VARCHAR( 20 ) ,
`drbbed7pay` VARCHAR( 20 ) ,
`drbbed8pay` VARCHAR( 20 ) ,
`drbdatemodify` INT ,
`drbuserid` VARCHAR( 50 ) ,
`drbdateadd` VARCHAR( 8 ) ,
`drbbookstatus` VARCHAR( 2 ) DEFAULT '0',
PRIMARY KEY ( `drbid`) ,
UNIQUE (`drbdate`,`drbrmno`),
INDEX ( `drbdate`,`drbroomtypeid`),
INDEX ( `drbdate`,`drbrmno`)
) ENGINE = MYISAM ;
Apr 2013 Tyrwhitt drbbed,2,3,4,5,6/pay is no longer used, all programs remains these fields.
Jan2011 added INDEX (`drbrmno`, `drbdate`) to try if improve performance, like not much
Note: drbremark is not used in the program.
CREATE TABLE `creative_dbblanc`.`dateroom_airbnb` (
`draid` INT NOT NULL AUTO_INCREMENT ,
`dradate` VARCHAR( 8 ) NOT NULL ,
`dradateyear` VARCHAR( 4 ) NOT NULL ,
`dradatemonth` VARCHAR( 2 ) NOT NULL ,
`dradateday` VARCHAR( 4 ) NOT NULL ,
`draoutlet` VARCHAR( 50 ) NOT NULL ,
`draroom` VARCHAR( 8 ) NOT NULL ,
`drastatus` VARCHAR( 2 ) ,
`drabookid` VARCHAR( 50 ) ,
`dradatemodify` INT ,
`drauserid` VARCHAR( 50 ) ,
`dradateadd` VARCHAR( 8 ) ,
PRIMARY KEY ( `draid`) ,
UNIQUE (`dradate`,`draroom`)
) ENGINE = MYISAM ;
drastatus : B for Booked, blank for Open room, C for closed room
CREATE TABLE `creative_dbblanc`.`trailprsmodify` (
`id` INT NOT NULL AUTO_INCREMENT ,
`tprsid` INT,
`tprsnodaysbefore` INT ,
`tprsquantitybefore` INT ,
`tprstotalpricebefore` INT ,
`tprsremarkbefore` VARCHAR( 100 ) ,
`tprsnodaysafter` INT ,
`tprsquantityafter` INT ,
`tprstotalpriceafter` INT ,
`tprsremarkafter` VARCHAR( 100 ) ,
`tdatemodify` INT ,
`tprsuserid_modify` VARCHAR( 50 ) ,
PRIMARY KEY ( `id` ) ,
INDEX ( `tprsid`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`parameter` (
`patype` VARCHAR( 20 ) NOT NULL ,
`pavalue` VARCHAR( 20 ) NOT NULL ,
`padescription` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `patype`,`pavalue`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`users` (
`usrid` VARCHAR( 50 ) NOT NULL ,
`usrpassword` VARCHAR( 50 ) NOT NULL,
`usroutlet` VARCHAR( 50 ) NOT NULL,
`usraccess` VARCHAR( 1 ) NOT NULL,
`usrapplication` VARCHAR( 20 ) NOT NULL,
PRIMARY KEY ( `usrid`)
) ENGINE = MYISAM ;
INSERT INTO `creative_dbblanc`.`users` (
`usrid` ,
`usrpassword` ,
`usroutlet`,
`usraccess`,
`usrapplication`
)
VALUES
('SSS' , 'SSS' , 'Tyrwhitt', '5','Hostel' ),
('AAA' , 'AAA' , 'Tyrwhitt', '1' ,'Hostel' )
INSERT INTO `creative_dbblancnonprod`.`users` (
`usrid` ,
`usrpassword` ,
`usroutlet`,
`usraccess`
)
VALUES
('T01' , 'T01' , 'Tyrwhitt','1' ),
('T02' , 'T02' , 'Tyrwhitt','5' )
Access 9 (anything more than 5): can view operator6 DayTotal $, higher access.
CREATE TABLE `creative_dbblanc`.`contactus` (
`csid` INT NOT NULL AUTO_INCREMENT ,
`csdate` VARCHAR( 8 ) NOT NULL ,
`csname` VARCHAR( 50 ) NOT NULL,
`csemail` VARCHAR( 50 ) ,
`cssubject` VARCHAR( 100 ) ,
`csbody` text ,
`cssendreply1` text ,
`cssendreply2` text ,
`csdatereply1` VARCHAR( 8 ) ,
`csdatereply2` VARCHAR( 8 ) ,
`csuseridreply1` VARCHAR( 50 ),
`csuseridreply2` VARCHAR( 50 ),
`csstatus` VARCHAR( 2 ),
`csdatemodify` INT ,
`csuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `csid`) ,
INDEX ( `csdate`)
) ENGINE = MYISAM ;
(status 1: email received, 5: reply1 done 9. permanent delete
CREATE TABLE `creative_dbblanc`.`DateData` ( ==> Not used
`ddid` INT NOT NULL AUTO_INCREMENT ,
`dddate` VARCHAR( 8 ) NOT NULL ,
`dddateyear` VARCHAR( 4 ) NOT NULL ,
`dddatemonth` VARCHAR( 2 ) NOT NULL ,
`dddateday` VARCHAR( 4 ) NOT NULL ,
`ddoutlet` VARCHAR( 50 ) NOT NULL ,
`ddtype` VARCHAR( 3 ) NOT NULL ,
`ddvalue` VARCHAR( 3 ) NOT NULL ,
`dddatemodify` INT ,
`dduserid` VARCHAR( 50 ) ,
`dddateadd` VARCHAR( 8 ) ,
PRIMARY KEY ( `ddid`) ,
UNIQUE (`dddate`,`ddoutlet`,`ddtype`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblancnonprod`.`hitpage` (
CREATE TABLE `creative_dbblanc`.`hitpage` (
`pgeid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`pgename` TEXT NOT NULL ,
`pgehits` INT NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;
CREATE TABLE `creative_travelblog`.`hitpage` (
`pgeid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`pgename` TEXT NOT NULL ,
`pgehits` INT NOT NULL DEFAULT '0'
) ENGINE = MYISAM ;
INSERT INTO `creative_dbblancnonprod`.`hitpage` (
INSERT INTO `creative_dbblanc`.`hitpage` (
`pgeid` ,
`pgename` ,
`pgehits`
)
VALUES (
NULL , 'index.php' , '500'
);
INSERT INTO `creative_dbblanc`.`roomtype` (
`rtpriceplanid` ,
`rtroomtypeid` ,
`rtoutlet` ,
`rtroomtype` ,
`rtstatus` ,
`rtdefaultprice` ,
`rtpriceweekend` ,
`rtdefaultroomtypecapacity` ,
`rtadatedd`
)
VALUES
('P3','P3','Tyrwhitt','Privacy Room (Single/Queen beds,3 persons)','A','133','1','20220831'),
('G6','GG','Tyrwhitt','Suite Private Room, 1 alcove (6 persons)','G','258','258','1','20140111'),
('G7','GG','Tyrwhitt','Suite Private Room, 4 alcoves (7 persons)','G','308','293','1','20140111'),
('G8','GG','Tyrwhitt','Suite Private Room, 4 alcoves (8 persons)','G','308','293','1','20140111'),
('G9','GG','Tyrwhitt','Suite Private Room, 4 alcoves (9 persons)','G','348','331','1','20140111'),
('G10','GG','Tyrwhitt','Suite Private Room, 4 alcoves (10 persons)','G','390','371','1','20140111'),
('G11','GG','Tyrwhitt','Suite Private Room, 6 alcoves (11 persons)','G','514','488','1','20140111'),
('G12','GG','Tyrwhitt','Suite Private Room, 6 alcoves (12 persons)','G','514','488','1','20140111'),
('G13','GG','Tyrwhitt','Suite Private Room, 6 alcoves (13 persons)','G','514','488','1','20140111'),
('G14','GG','Tyrwhitt','Suite Private Room, 6 alcoves (14 persons)','G','554','526','1','20140111'),
('G15','GG','Tyrwhitt','Suite Private Room, 7 alcoves (15 persons)','G','632','600','1','20140111'),
('G16','GG','Tyrwhitt','Suite Private Room, 7 alcoves (16 persons)','G','674','640','1','20140111'),
('G17','GG','Tyrwhitt','Suite Private Room, 8 alcoves (17 persons)','G','698','663','1','20140111'),
('G18','GG','Tyrwhitt','Suite Private Room, 8 alcoves (18 persons)','G','698','663','1','20140111'),
('G19','GG','Tyrwhitt','Suite Private Room, 8 alcoves (19 persons)','G','738','701','1','20140111'),
('G20','GG','Tyrwhitt','Suite Private Room, 9 alcoves (20 persons)','G','838','796','1','20140111'),
('G21','GG','Tyrwhitt','Suite Private Room, 10 alcoves (21 persons)','G','862','819','1','20140111'),
('G22','GG','Tyrwhitt','Suite Private Room, 10 alcoves (22 persons)','G','862','819','1','20140111'),
('G23','GG','Tyrwhitt','Suite Private Room, 10 alcoves (23 persons)','G','902','857','1','20140111'),
('G24','GG','Tyrwhitt','Suite Private Room, 10 alcoves (24 persons)','G','944','897','1','20140111'),
('G25','GG','Tyrwhitt','Suite Private Room, 11 alcoves (25 persons)','G','1022','971','1','20140111'),
('G26','GG','Tyrwhitt','Suite Private Room, 13 alcoves (26 persons)','G','1188','1129','1','20140111'),
('G27','GG','Tyrwhitt','Suite Private Room, 13 alcoves (27 persons)','G','1188','1129','1','20140111'),
('G28','GG','Tyrwhitt','Suite Private Room, 13 alcoves (28 persons)','G','1188','1129','1','20140111'),
('G29','GG','Tyrwhitt','Suite Private Room, 13 alcoves (29 persons)','G','1188','1129','1','20140111'),
('G30','GG','Tyrwhitt','Suite Private Room, 13 alcoves (30 persons)','G','1228','1167','1','20140111'),
('G31','GG','Tyrwhitt','Suite Private Room, 14 alcoves (31 persons)','G','1252','1189','1','20140111'),
('G32','GG','Tyrwhitt','Suite Private Room, 14 alcoves (32 persons)','G','1252','1189','1','20140111'),
('G33','GG','Tyrwhitt','Suite Private Room, 14 alcoves (33 persons)','G','1292','1227','1','20140111'),
('G34','GG','Tyrwhitt','Suite Private Room, 17 alcoves (34 persons)','G','1536','1459','1','20140111'),
('G35','GG','Tyrwhitt','Suite Private Room, 17 alcoves (35 persons)','G','1536','1459','1','20140111'),
('G36','GG','Tyrwhitt','Suite Private Room, 17 alcoves (36 persons)','G','1536','1459','1','20140111'),
('G37','GG','Tyrwhitt','Suite Private Room, 17 alcoves (37 persons)','G','1536','1459','1','20140111'),
('G38','GG','Tyrwhitt','Suite Private Room, 17 alcoves (38 persons)','G','1536','1459','1','20140111'),
('G39','GG','Tyrwhitt','Suite Private Room, 17 alcoves (39 persons)','G','1576','1497','1','20140111'),
('S6','SC','Tyrwhitt','Suite Private, 3 alcoves (5 to 6 persons)','S','60','1','20140108'),
('S9','SB','Tyrwhitt','Suite Private, 4 alcoves (7 to 9 persons)','S','60','1','20140108'),
('S10','SA','Tyrwhitt','Suite Private, 4 alcoves (8 to 10 persons)','S','60','1','20140108'),
('S14','SD','Tyrwhitt','Suite Private, 4 alcoves (11 to 14 persons)','S','60','1','20140108'),
('0','0','Tyrwhitt','Not in use','0','0','0','20100412'),
('P1','P1','Tyrwhitt','Privacy Room (Single bed,1 person)','A','65','1','20130318'),
('P2','P2','Tyrwhitt','Privacy Room (Single bed,2 persons)','A','70','1','20130318'),
('Q2','Q2','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','A','75','1','20130318'),
('P3','P3','Tyrwhitt','Privacy Room (Single/Queen beds,3 persons)','A','110','1','20130318'),
('Q4','Q4','Tyrwhitt','Privacy Room (Queen beds,4 persons)','A','150','1','20130318'),
('P5','P6','Tyrwhitt','Privacy Room (Single/Queen bed,5 persons)','P','185','1','20130318'),
('P6','P6','Tyrwhitt','Privacy Room (Single/Queen bed,6 persons)','A','220','1','20130318')
Insert INTO `creative_dbblancnonprod`.`room` (
Insert INTO `creative_dbblanc`.`room` (
`rmno` ,
`rmoutlet` ,
`rmdata` ,
`rmdefaultroomtypeid` ,
`rmadatedd`,
`rmstatus`
)
VALUES
('09A','Tyrwhitt','Privacy Room (Single bed,1 person)','P1','20220520','A'),
('09B','Tyrwhitt','Privacy Room (Single bed,1 person)','P1','20220520','A'),
('00A','Tyrwhitt','Suite Private , 4 alcoves (8 to 10 persons)','SA','20140108','S'),
('00B','Tyrwhitt','Suite Private, 4 alcoves (7 to 9 persons)','SB','20140108','S'),
('00C','Tyrwhitt','Suite Private, 3 alcoves (5 to 6 persons)','SC','20140108','S'),
('00D','Tyrwhitt','Suite Private, 4 alcoves (11 to 14 persons)','SD','20140108','S'),
('001','Tyrwhitt','Privacy Room (Queen beds,4 persons)','Q4','20130318'),
('002','Tyrwhitt','Privacy Room (Single bed,2 persons)','P2','20130318'),
('003','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318'),
('004','Tyrwhitt','Privacy Room (Single bed,2 persons)','P2','20130318'),
('005','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318'),
('006','Tyrwhitt','Privacy Room (Single bed,2 persons)','P2','20130318'),
('007','Tyrwhitt','Privacy Room (Single/Queen beds,3 persons)','P3','20130318'),
('008','Tyrwhitt','Privacy Room (Single bed,2 persons)','P2','20130318'),
('009','Tyrwhitt','Privacy Room (Queen beds,4 persons)','Q4','20130318'),
('010','Tyrwhitt','Privacy Room (Single bed,1 person)','P1','20130318'),
('011','Tyrwhitt','Privacy Room (Single bed,1 person)','P1','20130318'),
('12A','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318'),
('12B','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318'),
('013','Tyrwhitt','Privacy Room (Single/Queen beds,6 persons)','P6','20130318'),
('13A','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318'),
('13B','Tyrwhitt','Privacy Room (Single/Queen beds,3 persons)','P3','20130318'),
('014','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318'),
('015','Tyrwhitt','Privacy Room(Queen bed, for 2 persons)','Q2','20130318')
INSERT INTO `creative_dbblanc`.`dateroombook` (
=> use operator2tongwahmassload.php program
=> modify the $month data and check parameter table before running the program.
'
customer FUTURE
cuid
cuname
cugender
cucountry
cucity
cuphonepassport
custatus
cudatemodify
curemark
cuuserid
INSERT INTO `creative_dbblanc`.`parameter` (
`patype` ,
`pavalue` ,
`padescription`
)
VALUES
('airbnbroomcode', 'P1-1', 'a'), //padescription for ascending order
('airbnbroomcode', 'P1-2', 'b'),
('airbnbroomcode', 'P1-3', 'c'),
('airbnbroomcode', 'P1-4', 'd'),
('airbnbroomcode', 'P1-5', 'e'),
('airbnbroomcode', 'P1-6', 'f'),
('airbnbroomcode', 'P1-A', 'i'),
('airbnbroomcode', 'P1-B', 'j'),
('airbnbroomcode', 'P1-C', 'k'),
('airbnbroomcode', 'P1-D', 'l'),
('airbnbroomcode', 'P1-7', 'm'),
('airbnbroomcode', 'P2-1', 'o'),
('airbnbroomcode', 'P3-1', 'p'),
('airbnbroomcode', 'Q2-1', 's'),
('airbnbroomcode', 'Q2-2', 't'),
('airbnbroomcode', 'Q3-1', 'v'),
('airbnbroomcode', 'Q4-1', 'w'),
('airbnbroomcode', 'BK/AG/EX', 'x'),
('airbnbroomcode', 'OTA-Q2', 'y'),
('airbnbroomcode', 'OTA-P3', 'z'),
('airbnbroomcode', 'OTA-Q4', 'za'),
('doorcode', 'main', '3439'),
('doorcode', '00A', '4545'),
('doorcode', '00B', '6868'),
('doorcode', '00C', '5678'),
('doorcode', '00D', '1414'),
('company', 'Blanc Inn', 'Blanc Inn'),
('rdowner' , '1' , 'Tong Wah' ),
('rdowner' , '2' , 'Peggy' ),
('rdowner' , '3' , 'LG' ),
('rdowner' , '4' , 'Shwan' ),
('Tyrwhitt' , '1' , 'Testing,Room 302 customer very angry' ),
('Tyrwhitt' , '2' , 'Testing,Room 301 Air Con need repair urgently' ),
('Tyrwhitt' , '3' , ''),
('Tyrwhitt' , '4' , ''),
('Tyrwhitt' , '5' , ''),
('prsstatus' , '0' , 'Booking Entered, please activate your Booking Submission' ),
('prsstatus' , '1' , 'Booking Submitted, Hostel is in progress registering your booking' ),
('prsstatus' , '2' , 'Booking Confirmed' ),
('prsstatus' , '3' , 'Booking Confirmed' ),
('prsstatus' , 'C0' , 'Booking Cancelled' ),
('prsstatus' , 'C1' , 'Booking Cancelled' ),
('prsstatus' , 'C2' , 'Booking Cancelled, Hostel is in progress registering your cancellation'),
('prsstatus' , 'CC' , 'Booking Cancelled' ),
('prsstatus' , 'CM' , 'Booking Cancelled' ),
('prsstatus' , 'R1' , 'Booking Rejected, Full House' ),
('prsstatus' , 'M2' , 'Modified for internet confirm' ),
('prsstatus' , 'M3' , 'Modified for operator confirm' ),
('prsbookstatus' , '0' , 'Room/Bed Not Alloted to this Booking' ),
('prsbookstatus' , '1' , 'NG' ),
('prsbookstatus' , '2' , 'OK' ),
('prsinvstatus' , '0' , 'NotPaid' ),
('prsinvstatus' , '1' , 'HalfPaid' ),
('prsinvstatus' , '2' , 'Paid' ),
('drbbookstatus' , '0' , '0' ),
('drbbookstatus' , '1' , 'NG' ),
('drbbookstatus' , '2' , 'OK' ),
('drbbookstatus' , '3' , 'NA' ),
('drbbookstatus' , '4' , 'Av' ),
('csstatus' , '1' , 'New' ),
('csstatus' , '5' , 'Replied' ),
('csstatus' , '9' , 'Delete' ),
('outlet' , '1' , 'Tyrwhitt' ),
('outlet' , '2' , 'Hamilton' ),
('offset' , '0' , 'Used for prsid when the number get too big' ),
('dtstatus' , '0' , 'Available' ),
('dtstatus' , '1' , 'Action Required to Close' ),
('dtacknowledgestatus' , '0' , 'No action' ),
('dtacknowledgestatus' , '2' , 'Manager acknowleged close' ),
('dtacknowledgestatus' , '1' , 'Manager Read, but not acknowlege yet' ),
CREATE TABLE `creative_dbblanc`.`cash` (
`invid` INT NOT NULL AUTO_INCREMENT ,
`invoutlet` VARCHAR( 50 ) NOT NULL ,
`invdrbid` INT NOT NULL ,
`invdrbrmno` VARCHAR( 50 ) NOT NULL ,
`invdrbbedno` INT NOT NULL ,
`invprsid` INT NOT NULL ,
`invamount` FLOAT(5.2) ,
`invdate` VARCHAR( 8 ) NOT NULL ,
`invstatus` VARCHAR( 2 ) NOT NULL ,
`invdatemodify` INT NOT NULL ,
`invuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `invid` ) ,
INDEX ( `invoutlet`,`invdate`,`invamount`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`operationmemo`(
`omid` INT NOT NULL AUTO_INCREMENT ,
`omoutlet` VARCHAR( 50 ) NOT NULL ,
`omdate` VARCHAR( 8 ) NOT NULL ,
`omowner` VARCHAR( 300 ) NOT NULL ,
`omremark1` text NOT NULL ,
`omremark2` text ,
`omstatus` VARCHAR( 2 ) ,
`omdatemodify` INT ,
`omuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `omid` ) ,
INDEX ( `omoutlet`,`omdate`)
) ENGINE = MYISAM ;
omstatus ( nonevent: 0; delete: 1 ) => to be entered by Manager periodically.
TO USE AS BLACK BOARD
INSERT INTO `creative_dbblanc`.`operationmemo` (
`omoutlet` ,
`omdate` ,
`omowner` ,
`omdatemodify`
)
VALUES ('Tyrwhitt' ,'20130601','memopad3' , '20130601' ),
('Tyrwhitt' ,'20130601','memopad2' , '20130601' ),
('Tyrwhitt' ,'20130601','memopad' , '20130601' )
omoutlet = Studio : To record maintenance done on Studio
CREATE TABLE `creative_dbblanc`.`faq`(
`faqid` INT NOT NULL AUTO_INCREMENT ,
`faqoutlet` VARCHAR( 50 ) NOT NULL ,
`faqdate` VARCHAR( 8 ) NOT NULL ,
`faqowner` VARCHAR( 30 ) NOT NULL ,
`faqremark1` text ,
`faqremark2` text ,
`faqstatus` VARCHAR( 2 ) NOT NULL DEFAULT '0',
`faqdatemodify` INT NOT NULL ,
`faquserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `faqid` ) ,
INDEX ( `faqoutlet`,`faqdate`)
) ENGINE = MYISAM ;
faqstatus ( nonevent: 0; delete: 1 ) => to be entered by Manager periodically.
CREATE TABLE `creative_dbblanc`.`stock` (
`skid` INT NOT NULL AUTO_INCREMENT ,
`skoutlet` VARCHAR( 50 ) ,
`skseq` VARCHAR( 50 ) NOT NULL DEFAULT '0',
`sktype` VARCHAR( 20 ) NOT NULL ,
`skitem` VARCHAR( 100 ) NOT NULL ,
`skremark` VARCHAR( 100 ) ,
`skprice` FLOAT(5.2) ,
`skcost` FLOAT(5.2) ,
`skattractionrate` FLOAT(5.2) ,
`skquantitybalance` FLOAT(5.2) ,
`skrop` INT ,
`skmax` INT ,
`skstatus` VARCHAR( 1 ) DEFAULT 'A' ,
`skstockflag` VARCHAR( 1 ) DEFAULT 'N' ,
`skdatemodify` INT ,
`skuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `skid`) ,
UNIQUE (`skoutlet`,`skitem`)
) ENGINE = MYISAM ;
skseq: for item display in accordance to desired skseq
sktype: T:ticket
skstockflag: Y: Stockable, N: Not Stockable
if (balance < rop) {Order_Qty = Max - balance)
INSERT INTO `creative_dbblanc`.`stock` (
`skoutlet`,
`skseq`,
`sktype`,
`skitem`,
`skprice`,
`skquantitybalance`
)
VALUES
('Tyrwhitt','50','S','A - EZ Link ** ($5 + Stored_Value)','12','5'),
('Tyrwhitt','60','S','B - Adaptor ** $2','2','18'),
('Tyrwhitt','62','S','B - Towel (new) ** $4','4','51'),
('Tyrwhitt','64','S','B - Towel (old) ** $1','1','10'),
('Tyrwhitt','66','S','B - Padlock ** $3','3','35'),
('Tyrwhitt','68','S','B - Tooth Brush ** $0.5','0.5','75'),
('Tyrwhitt','70','S','B - Ear Plug ** $1','1','95'),
('Tyrwhitt','72','S','B - Panadol ** $1','1','10'),
('Tyrwhitt','74','S','C - Coke ** $1','1','97'),
('Tyrwhitt','76','S','C - Mineral Water ** $1','1','98'),
('Tyrwhitt','100','B','SAEx 1-Day Bus Pass','13','10'),
('Tyrwhitt','102','B','SAEx Per-Trip Bus Ticket','5','10'),
('Tyrwhitt','103','B','(Child)SAEx 1-Day Bus Pass','13','10'),
('Tyrwhitt','104','B','(Child)SAEx Per-Trip Bus Ticket','5','10'),
('Tyrwhitt','112','T','Singapore Zoo','24','10'),
('Tyrwhitt','116','T','Night Safari','32','10'),
('Tyrwhitt','120','T','River Safari','22','10'),
('Tyrwhitt','124','T','Jurong BirdPark','23','10'),
('Tyrwhitt','128','T','RWS- Universe Studio','67','10'),
('Tyrwhitt','132','T','RWS- SEA Aquarium','35','10'),
('Tyrwhitt','136','T','RWS - Adventure Cove','30','10'),
('Tyrwhitt','140','T','Sentosa - Cable Car 2-way','20','10'),
('Tyrwhitt','142','T','Sentosa - Song of the Sea (7:40pm)','9.5','0'),
('Tyrwhitt','143','T','Sentosa - Song of the Sea (8:40pm)','9.5','0'),
('Tyrwhitt','144','T','Sentosa - Merlion','6.50','10'),
('Tyrwhitt','148','T','Sentosa - Images of Singapore','8.5','10'),
('Tyrwhitt','152','T','Sentosa - Skyline Luge','10.50','10'),
('Tyrwhitt','160','T','Singapore Flyer(Before 6pm)','25','10'),
('Tyrwhitt','164','T','Singapore Flyer(6pm - 1030pm)','27','10'),
('Tyrwhitt','168','T','Singapore River Cruise','10','10'),
('Tyrwhitt','172','T','Garden By The Bay','25','10'),
('Tyrwhitt','176','T','National Orchid Garden','4','10'),
('Tyrwhitt','180','T','(Child)Singapore Zoo','14','10'),
('Tyrwhitt','184','T','(Child)Night Safari','21','10'),
('Tyrwhitt','188','T','(Child)River Safari','14','10'),
('Tyrwhitt','192','T','(Child)Jurong BirdPark','14','10'),
('Tyrwhitt','196','T','(Child)RWS- Universe Studio','49','10'),
('Tyrwhitt','200','T','(Child)RWS- SEA Aquarium','25','10'),
('Tyrwhitt','204','T','(Child)RWS - Adventure Cove','22','10'),
('Tyrwhitt','208','T','(Child)Sentosa - Cable Car 2-way','13','10'),
('Tyrwhitt','212','T','(Child)Sentosa - Merlion','5','10'),
('Tyrwhitt','216','T','(Child)Sentosa - Images of Singapore','7','10'),
('Tyrwhitt','220','T','(Child)Sentosa - Skyline Luge','10.50','10'),
('Tyrwhitt','224','T','(Child)Singapore Flyer(Before 6pm)','21','10'),
('Tyrwhitt','228','T','(Child)Singapore Flyer(6pm - 1030pm)','21','10'),
('Tyrwhitt','232','T','(Child)Singapore River Cruise','10','10'),
('Tyrwhitt','236','T','(Child)Garden By The Bay','14','10'),
('Tyrwhitt','240','T','(Child)National Orchid Garden','4','10')
Chinese
('Tyrwhitt','100','B','SAEx 1-Day Bus Pass 24小时易通车费','13','10'),
('Tyrwhitt','102','B','SAEx Round-Trip Bus Ticket往返车','10','10'),
('Tyrwhitt','102','B','SAEx Per-Trip Bus Ticket 单程车','5','10'),
('Tyrwhitt','112','T','Singapore Zoo 新加坡动物园','24','10'),
('Tyrwhitt','116','T','Night Safari 夜间野生动物园','32','10'),
('Tyrwhitt','120','T','River Safari 新加坡河川生态园','22','10'),
('Tyrwhitt','124','T','Jurong BirdPark 裕廊飞禽公园','23','10'),
('Tyrwhitt','128','T','RWS- Universe Studio 新加坡环球影城','67','10'),
('Tyrwhitt','132','T','RWS- SEA Aquarium 新加坡海事博物馆及水族馆','35','10'),
('Tyrwhitt','136','T','RWS - Adventure Cove 新加坡水上探险乐园','30','10'),
('Tyrwhitt','140','T','Sentosa - Cable Car 2-way 圣淘沙缆车','20','10'),
('Tyrwhitt','142','T','Sentosa - Song of the Sea, 圣淘沙 - 海之颂 (7:40pm)','9.5','0'),
('Tyrwhitt','143','T','Sentosa - Song of the Sea, 圣淘沙 - 海之颂 (8:40pm)','9.5','0'),
('Tyrwhitt','144','T','Sentosa - Merlion 圣淘沙鱼尾狮塔','6.50','10'),
('Tyrwhitt','148','T','Sentosa - Images of Singapore 圣淘沙新加坡万象馆','8.5','10'),
('Tyrwhitt','152','T','Sentosa - Skyline Luge 圣淘沙斜坡滑車','10.50','10'),
('Tyrwhitt','160','T','Singapore Flyer 新加坡摩天观景轮 (Before 6pm)','25','10'),
('Tyrwhitt','164','T','Singapore Flyer 新加坡摩天观景轮(6pm - 1030pm)','27','10'),
('Tyrwhitt','168','T','Singapore River Cruise 新加坡河游船','10','10'),
('Tyrwhitt','172','T','Garden By The Bay 滨海湾花园','25','10'),
('Tyrwhitt','176','T','National Orchid Garden 新加坡国立兰花园','4','10'),
('Tyrwhitt','180','T','(Child)Singapore Zoo 新加坡动物园','14','10'),
('Tyrwhitt','184','T','(Child)Night Safari 夜间野生动物园','21','10'),
('Tyrwhitt','188','T','(Child)River Safari 新加坡河川生态园','14','10'),
('Tyrwhitt','192','T','(Child)Jurong BirdPark 裕廊飞禽公园','14','10'),
('Tyrwhitt','196','T','(Child)RWS- Universe Studio 新加坡环球影城','49','10'),
('Tyrwhitt','200','T','(Child)RWS- SEA Aquarium 新加坡海事博物馆及水族馆','25','10'),
('Tyrwhitt','204','T','(Child)RWS - Adventure Cove 新加坡水上探险乐园','22','10'),
('Tyrwhitt','208','T','(Child)Sentosa - Cable Car 2-way 圣淘沙缆车','13','10'),
('Tyrwhitt','212','T','(Child)Sentosa - Merlion 圣淘沙鱼尾狮塔','5','10'),
('Tyrwhitt','216','T','(Child)Sentosa - Images of Singapore 圣淘沙新加坡万象馆','7','10'),
('Tyrwhitt','220','T','(Child)Sentosa - Skyline Luge 圣淘沙斜坡滑車','10.50','10'),
('Tyrwhitt','224','T','(Child)Singapore Flyer 新加坡摩天观景轮 (Before 6pm)','21','10'),
('Tyrwhitt','228','T','(Child)Singapore Flyer 新加坡摩天观景轮(6pm - 1030pm)','21','10'),
('Tyrwhitt','232','T','(Child)Singapore River Cruise 新加坡河游船','10','10'),
('Tyrwhitt','236','T','(Child)Garden By The Bay 滨海湾花园','14','10'),
('Tyrwhitt','240','T','(Child)National Orchid Garden 新加坡国立兰花园','4','10')
CREATE TABLE `creative_dbblanc`.`stocktransaction` (
`stid` INT NOT NULL AUTO_INCREMENT ,
`stoutlet` VARCHAR( 50 ) NOT NULL ,
`stdate` VARCHAR( 8 ) NOT NULL ,
`stbuysell` VARCHAR( 20 ) NOT NULL DEFAULT '0',
`stitem` VARCHAR( 200 ) NOT NULL ,
`stquantity` FLOAT(5.2) ,
`stquantitybalance` FLOAT(5.2) ,
`stdatemodify` INT NOT NULL ,
`stuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `stid` ) ,
INDEX ( `stoutlet`,`stdate`) ,
INDEX ( `stoutlet`,`stdatemodify`,`stitem`)
) ENGINE = MYISAM ;
20130420select distinct cbdate as 'Date', cbremark as 'Description', cbamount as 'Amount', cbamountbalance as 'Balance', cbstatus as '1/Revenue 2/Cost' FROM cashboxuser1 where cbdate >= '20130420' order by cbid desc
CREATE TABLE `creative_dbblanc`.`cashbox` (
`cbid` INT NOT NULL AUTO_INCREMENT ,
`cboutlet` VARCHAR( 50 ) NOT NULL ,
`cbdate` VARCHAR( 8 ) NOT NULL ,
`cbremark` VARCHAR( 200 ) NOT NULL ,
`cbamount` FLOAT(5.2) ,
`cbamountbalance` FLOAT(5.2) ,
`cbstatus` VARCHAR( 2 ) NOT NULL DEFAULT '0',
`cbdatemodify` INT NOT NULL ,
`cbuserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `cbid` ) ,
INDEX ( `cboutlet`,`cbdate`)
) ENGINE = MYISAM ;
cbstatus ( nonevent: 0; revenue: 1, cost: 2 ) => to be entered by Manager periodically.
CREATE TABLE `creative_dbblanc`.`cashboxuser1` (
`cb1id` INT NOT NULL AUTO_INCREMENT ,
`cb1outlet` VARCHAR( 50 ) NOT NULL ,
`cb1date` VARCHAR( 8 ) NOT NULL ,
`cb1remark` VARCHAR( 200 ) NOT NULL ,
`cb1amount` FLOAT(5.2) ,
`cb1amountbalance` FLOAT(5.2) ,
`cb1status` VARCHAR( 2 ) NOT NULL DEFAULT '0',
`cb1datemodify` INT NOT NULL ,
`cb1userid` VARCHAR( 50 ) ,
PRIMARY KEY ( `cb1id` ) ,
INDEX ( `cb1outlet`,`cb1date`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`cashboxuser2` (
`cb2id` INT NOT NULL AUTO_INCREMENT ,
`cb2outlet` VARCHAR( 50 ) NOT NULL ,
`cb2date` VARCHAR( 8 ) NOT NULL ,
`cb2remark` VARCHAR( 200 ) NOT NULL ,
`cb2amount` FLOAT(5.2) ,
`cb2amountbalance` FLOAT(5.2) ,
`cb2status` VARCHAR( 2 ) NOT NULL DEFAULT '0',
`cb2datemodify` INT NOT NULL ,
`cb2userid` VARCHAR( 50 ) ,
PRIMARY KEY ( `cb2id` ) ,
INDEX ( `cb2outlet`,`cb2date`)
) ENGINE = MYISAM ;
Important Note:
===============
1/ Where Guest Name List shown up?
Operator3b (Room) => 1 guest to show what room they are in. (program: operator3b.php)
Operator6 (Room) => same (program: operator3b.php)
Operator5AAc => Booking Modification )to show list of guest in one room.(program: operator5AAcb.php)
2/ Where prsbookstatus update ? - operator3sqlupdate.php and operator5bcsqlupdate.php
(either day/qty cancel and/or room allotment need these computation)
3/ Where drbbookstatus update ? - operator3sqlupdate.php and operator5bsqlupdate.php
(either day/qty cancel and/or room allotment need these computation)
CREATE TABLE `creative_dbblanc`.`receptionduty`(
`rdid` INT NOT NULL AUTO_INCREMENT ,
`rdoutlet` VARCHAR( 50 ) NOT NULL ,
`rddate` VARCHAR( 8 ) NOT NULL ,
`rddateyear` VARCHAR( 4 ) NOT NULL ,
`rddatemonth` VARCHAR( 2 ) NOT NULL ,
`rdowner1` VARCHAR( 30 ) ,
`rdowner2` VARCHAR( 30 ) ,
`rdowner3` VARCHAR( 30 ) ,
`rdremark1` VARCHAR( 200 ) ,
`rdremark2` VARCHAR( 200 ) ,
`rdremark3` VARCHAR( 200 ) ,
`rdstatus` VARCHAR( 2 ) NOT NULL DEFAULT '0',
`rddatemodify` INT NOT NULL ,
`rduserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `rdid` ) ,
INDEX ( `rdoutlet`,`rddate`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`receptionduty`(
`rdtid` INT NOT NULL AUTO_INCREMENT ,
`rdtoutlet` VARCHAR( 50 ) NOT NULL ,
`rdtdate` VARCHAR( 8 ) NOT NULL ,
`rdtdateyear` VARCHAR( 4 ) NOT NULL ,
`rdtdatemonth` VARCHAR( 2 ) NOT NULL ,
`rdtowner` VARCHAR( 30 ) NOT NULL ,
`rdtremark1` VARCHAR( 200 ) ,
`rdtremark2` VARCHAR( 200 ) ,
`rdtstatus` VARCHAR( 2 ) NOT NULL DEFAULT '0',
`rdtdatemodify` INT NOT NULL ,
`rduserid` VARCHAR( 50 ) ,
PRIMARY KEY ( `rdid` ) ,
INDEX ( `rdoutlet`,`rddate`)
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`userlogin` (
`uinid` INT NOT NULL AUTO_INCREMENT ,
`uinusrid` VARCHAR( 50 ) NOT NULL ,
`uindatetime` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `uinid` )
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`trailmemo` (
`tmmid` INT NOT NULL AUTO_INCREMENT ,
`tmmoutlet` VARCHAR( 50 ) NOT NULL ,
`tmmdate` INT ,
`tmmmemo` VARCHAR( 100 ) ,
`tmmuserid` VARCHAR( 20 ) ,
PRIMARY KEY ( `tmmid` ) ,
INDEX ( `tmmid`)
) ENGINE = MYISAM
NOTE: drbremark no where visible now ... it is taken by room-remark.
CREATE TABLE `creative_dbblanc`.`tourcategory` (
`tcid` INT NOT NULL AUTO_INCREMENT ,
`tclanguage` VARCHAR( 1 ) NOT NULL ,
`tcseqcategory` INT,
`tccategory12` VARCHAR( 100 ) ,
`tccategory1` VARCHAR( 200 ) ,
`tccategory2` VARCHAR( 200 ) ,
`tcaddress` VARCHAR( 100 ) ,
`tccategoryurl` VARCHAR( 50 ) ,
`tcmapurl` VARCHAR( 50 ) ,
`tctitle` text ,
`tckeyword` text ,
`tcdescription` text ,
`tcpara1title` text ,
`tcpara2title` text ,
`tcpara3title` text ,
`tcpara1` text ,
`tcpara2` text ,
`tcpara3` text ,
`tcimage1` VARCHAR( 50 ),
`tcimage2` VARCHAR( 50 ),
`tcimage3` VARCHAR( 50 ),
`tcimage4` VARCHAR( 50 ),
`tcimage5` VARCHAR( 50 ),
`tcimage6` VARCHAR( 50 ),
`tcstatus` VARCHAR( 2 ),
`tccontributeby` VARCHAR( 100 ),
`tcdateadd` INT ,
`tcdatemodify` INT ,
`tc11` VARCHAR( 100 ) ,
`tc12` VARCHAR( 200 ) ,
`tc21` VARCHAR( 100 ) ,
`tc22` VARCHAR( 200 ) ,
`tc31` VARCHAR( 100 ) ,
`tc32` VARCHAR( 200 ) ,
`tc41` VARCHAR( 100 ) ,
`tc42` VARCHAR( 200 ) ,
`tc51` VARCHAR( 100 ) ,
`tc52` VARCHAR( 200 ) ,
`tc61` VARCHAR( 100 ) ,
`tc62` VARCHAR( 200 ) ,
`tc71` VARCHAR( 100 ) ,
`tc72` VARCHAR( 200 ) ,
`tc81` VARCHAR( 100 ) ,
`tc82` VARCHAR( 200 ) ,
`tc91` VARCHAR( 100 ) ,
`tc92` VARCHAR( 200 ) ,
`tc01` VARCHAR( 100 ) ,
`tc02` VARCHAR( 200 ) ,
PRIMARY KEY ( `tcid` )
) ENGINE = MYISAM
tcimage4 is reserved for location map, if any entry, it will show up on "/sgtourmap/mapxxx.php file
(Status D is to delete the line,
M - for menu
2 - tour
3 - shop
4 - food
5 - house
6 - map
7 - laugh)
tccategory1 = nil if no Category1, tccategory2 is h1 text
CREATE TABLE `creative_dbblanc`.`emaillist` (
`mailid` INT NOT NULL AUTO_INCREMENT ,
`maildateadd` VARCHAR( 8) ,
`prsname` VARCHAR( 50 ) NOT NULL ,
`mail` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `mailid` )
) ENGINE = MYISAM
check tongwahmakemaillist.php and tongwahsendemail.php files
CREATE TABLE `creative_dbblanc`.`emaillist` (
`mailid` INT NOT NULL AUTO_INCREMENT ,
`maildateadd` VARCHAR( 8) ,
`mail` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `mailid` )
) ENGINE = MYISAM
[Export from emailist for distinct mail and import (at the bottom) into table emaillist2, csv format)
(import into this table, column=mail)
CREATE TABLE `creative_dbblanc`.`emaillistihub` (
`mailid` INT NOT NULL AUTO_INCREMENT ,
`maildateadd` VARCHAR( 8) ,
`prsname` VARCHAR( 50 ) NOT NULL ,
`mail` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `mailid` )
) ENGINE = MYISAM
CREATE TABLE `paypal_payment_info` (
`firstname` varchar(100) NOT NULL default '',
`lastname` varchar(100) NOT NULL default '',
`buyer_email` varchar(100) NOT NULL default '',
`street` varchar(100) NOT NULL default '',
`city` varchar(50) NOT NULL default '',
`state` char(3) NOT NULL default '',
`zipcode` varchar(11) NOT NULL default '',
`memo` varchar(255) default NULL,
`itemname` varchar(255) default NULL,
`itemnumber` varchar(50) default NULL,
`os0` varchar(20) default NULL,
`on0` varchar(50) default NULL,
`os1` varchar(20) default NULL,
`on1` varchar(50) default NULL,
`quantity` char(3) default NULL,
`paymentdate` varchar(50) NOT NULL default '',
`paymenttype` varchar(10) NOT NULL default '',
`txnid` varchar(30) NOT NULL default '',
`mc_gross` varchar(6) NOT NULL default '',
`mc_fee` varchar(5) NOT NULL default '',
`paymentstatus` varchar(15) NOT NULL default '',
`pendingreason` varchar(10) default NULL,
`txntype` varchar(10) NOT NULL default '',
`tax` varchar(10) default NULL,
`mc_currency` varchar(5) NOT NULL default '',
`reasoncode` varchar(20) NOT NULL default '',
`custom` varchar(255) NOT NULL default '',
`country` varchar(20) NOT NULL default '',
`datecreation` date NOT NULL default '0000-00-00'
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbblanc`.`feedback` (
`fbid` INT NOT NULL AUTO_INCREMENT ,
`fbdate` VARCHAR( 8 ) NOT NULL ,
`fbname` VARCHAR( 50 ) NOT NULL,
`fbemail` VARCHAR( 50 ) ,
`fboutlet` VARCHAR( 50 ) ,
`fbbodygood` text ,
`fbbodybad` text ,
`fboverallrate` int ,
PRIMARY KEY ( `fbid`) ,
INDEX ( `fbdate`)
) ENGINE = MYISAM ;
fbsubject not used, as spare
CREATE TABLE `creative_dbblanc`.`ihubpagehist` (
`iphid` INT NOT NULL AUTO_INCREMENT ,
`iphpagename` VARCHAR( 100 ) ,
`iphdateadd` VARCHAR( 8) ,
`iphcount` INT ,
PRIMARY KEY ( `iphid` )
) ENGINE = MYISAM
===========================================================================================================
CREATE DATABASE `creative_dbihub` ;
create root user for database creative_dbihub,
CREATE TABLE `creative_dbihub`.`ihubproject` (
`ihid` INT NOT NULL AUTO_INCREMENT ,
`ihusrid` VARCHAR( 50 ) NOT NULL ,
`ihusrpassword` VARCHAR( 50 ) NOT NULL,
`ihprojectname` VARCHAR( 100 ) ,
`ihgender` VARCHAR( 2 ) ,
`ihname` VARCHAR( 50 ) ,
`ihemail` VARCHAR( 50 ) ,
`ihphone` VARCHAR( 50 ) ,
`ihprice` FLOAT(5.2) ,
`ihinvcollected` FLOAT(5.2) ,
`ihurl` VARCHAR( 50 ) ,
`ihrequest` text ,
`ihremark` text ,
`ihstatus` VARCHAR( 2 ) ,
`ihdateadd` INT ,
`ihdatemodify` INT ,
PRIMARY KEY ( `ihusrid` ) ,
INDEX ( `ihid` )
) ENGINE = MYISAM ;
CREATE TABLE `creative_dbihub`.`sampleproject` (
`icid` INT NOT NULL AUTO_INCREMENT ,
`icihusrid` VARCHAR( 50 ) ,
`iclanguage` VARCHAR( 1 ) NOT NULL ,
`icseqcategory` INT,
`iccategory12` VARCHAR( 100 ) ,
`iccategory1` VARCHAR( 200 ) ,
`iccategory2` VARCHAR( 200 ) ,
`icaddress` VARCHAR( 100 ) ,
`iccategoryurl` VARCHAR( 50 ) ,
`icmapurl` VARCHAR( 50 ) ,
`ictitle` text ,
`ickeyword` text ,
`icdescription` text ,
`ictabletitle` text ,
`icpara1title` text ,
`icpara2title` text ,
`icpara3title` text ,
`icpara4title` text ,
`icpara5title` text ,
`icpara6title` text ,
`icpara7title` text ,
`icpara8title` text ,
`icpara9title` text ,
`icpara10title` text ,
`icpara1` text ,
`icpara2` text ,
`icpara3` text ,
`icpara4` text ,
`icpara5` text ,
`icpara6` text ,
`icpara7` text ,
`icpara8` text ,
`icpara9` text ,
`icpara10` text ,
`icimage1` VARCHAR( 50 ),
`icimage2` VARCHAR( 50 ),
`icimage3` VARCHAR( 50 ),
`icimage4` VARCHAR( 50 ),
`icimage5` VARCHAR( 50 ),
`icimage6` VARCHAR( 50 ),
`icstatus` VARCHAR( 2 ),
`iccontributeby` VARCHAR( 100 ),
`icdateadd` INT ,
`icdatemodify` INT ,
`ic11` VARCHAR( 100 ) ,
`ic12` VARCHAR( 200 ) ,
`ic21` VARCHAR( 100 ) ,
`ic22` VARCHAR( 200 ) ,
`ic31` VARCHAR( 100 ) ,
`ic32` VARCHAR( 200 ) ,
`ic41` VARCHAR( 100 ) ,
`ic42` VARCHAR( 200 ) ,
`ic51` VARCHAR( 100 ) ,
`ic52` VARCHAR( 200 ) ,
`ic61` VARCHAR( 100 ) ,
`ic62` VARCHAR( 200 ) ,
`ic71` VARCHAR( 100 ) ,
`ic72` VARCHAR( 200 ) ,
`ic81` VARCHAR( 100 ) ,
`ic82` VARCHAR( 200 ) ,
`ic91` VARCHAR( 100 ) ,
`ic92` VARCHAR( 200 ) ,
`ic01` VARCHAR( 100 ) ,
`ic02` VARCHAR( 200 ) ,
`ic1a` VARCHAR( 100 ) ,
`ic1b` VARCHAR( 200 ) ,
`ic2a` VARCHAR( 100 ) ,
`ic2b` VARCHAR( 200 ) ,
`ic3a` VARCHAR( 100 ) ,
`ic3b` VARCHAR( 200 ) ,
`ic4a` VARCHAR( 100 ) ,
`ic4b` VARCHAR( 200 ) ,
`ic5a` VARCHAR( 100 ) ,
`ic5b` VARCHAR( 200 ) ,
PRIMARY KEY ( `icid` )
) ENGINE = MYISAM
(Status D is to delete the line,
1 - main menu1
2 - main menu2
3 - main menu3
4 - main menu4
5 - main menu5
6 - main menu6
7 - main menu7
iccategory1 = nil if no Category1, iccategory2 is h1 text
INSERT INTO `creative_dbihub`.`sampleproject` (
`icihusrid` ,
`iccategory12`,`icseqcategory`,
`iccategory1` ,
`iccategory2` ,
`icpara1title` ,
`icpara2title` ,
`icpara3title` ,
`icpara1` ,
`icpara2` ,
`icpara3` ,
`icstatus`
)
VALUES (....icihusrid value change to user email ....)
('icihusrid','main1','1','Home','Home','Title1','Title2','Title3','Para1','Para2','Para3','1'),
('icihusrid','main2','1','Product-CatA','Sub-CatA','Title1','Title2','Title3','Para1','Para2','Para3','2'),
('icihusrid','main3','1','Product-CatB','Sub-CatB','Title1','Title2','Title3','Para1','Para2','Para3','3'),
('icihusrid','main4','1','Product-CatC','Sub-CatC','Title1','Title2','Title3','Para1','Para2','Para3','4'),
('icihusrid','main5','1','Product-CatD','Sub-CatD','Title1','Title2','Title3','Para1','Para2','Para3','5'),
('icihusrid','main6','1','Product-CatE','Sub-CatE','Title1','Title2','Title3','Para1','Para2','Para3','6'),
('icihusrid','main7','1','Contact us','Contact us','Title1','Title2','Title3','Para1','Para2','Para3','7'),
('icihusrid','1280806247','3','','Sub-CatA2','Title1','Title2','Title3','Para1','Para2','Para3','2'),
('icihusrid','1280806248','5','','Sub-CatA3','Title1','Title2','Title3','Para1','Para2','Para3','2'),
('icihusrid','1280806249','3','','Sub-CatB2','Title1','Title2','Title3','Para1','Para2','Para3','3'),
('icihusrid','1280806250','3','','Sub-CatC2','Title1','Title2','Title3','Para1','Para2','Para3','4')
CREATE TABLE `creative_dbmyo`.`myodetail` (
`icid` INT NOT NULL AUTO_INCREMENT ,
`iclanguage` VARCHAR( 1 ) NOT NULL ,
`iccategory12` VARCHAR( 100 ) ,
`icihusrid` VARCHAR( 50 ) ,
`icstatus` VARCHAR( 2 ) ,
`icseqcategory` INT,
`iccategory1` VARCHAR( 200 ) ,
`iccategory2` VARCHAR( 200 ) ,
`icad` VARCHAR( 4 ) ,
`icdistrict` VARCHAR( 3 ) ,
`icmrt` VARCHAR( 50 ) ,
`icshortdesc` text ,
`iccontact` VARCHAR( 100 ) ,
`icavailabledate` VARCHAR( 11 ) ,
`icrentasking` VARCHAR( 50 )
`iclongdesc` text ,
`icpostcode` VARCHAR( 6 ) ,
`icimage1` VARCHAR( 50 ),
`icimage2` VARCHAR( 50 ),
`icimage3` VARCHAR( 50 ),
`icimage4` VARCHAR( 50 ),
`icvideo` text ,
`icremark` text ,
`icmodifydate` VARCHAR( 8 ),
`icpgehits` INT NOT NULL DEFAULT '0'
PRIMARY KEY ( `icid` ) ,
INDEX ( `icid` )
) ENGINE = MYISAM ;
icstatus - A, I, D
iccategory1 - (Studio Unit, Master Room, Coihon Room, Student Accomodation, Backpackers Hostel)
for iccategory12 = main1, main2, main3, main4, main5
CREATE TABLE `creative_dbmyo`.`myomaster` (
`ihid` INT NOT NULL AUTO_INCREMENT ,
`ihusrid` VARCHAR( 50 ) NOT NULL ,
`ihusrpassword` VARCHAR( 50 ) NOT NULL,
`ihprojectname` VARCHAR( 100 ) ,
`ihgender` VARCHAR( 2 ) ,
`ihname` VARCHAR( 50 ) ,
`ihemail` VARCHAR( 50 ) ,
`ihphone` VARCHAR( 50 ) ,
`ihprice` FLOAT(5.2) ,
`ihinvcollected` FLOAT(5.2) ,
`ihurl` VARCHAR( 50 ) ,
`ihrequest` text ,
`ihremark` text ,
`ihaboutus` text ,
`ihstatus` VARCHAR( 2 ) ,
`ihdateadd` INT ,
`ihdatemodify` INT ,
PRIMARY KEY ( `ihusrid` ) ,
INDEX ( `ihid` )
) ENGINE = MYISAM ;
INSERT INTO `creative_dbmyo`.`myodetail` (
`icihusrid` ,
`iclanguage`,
`iccategory12`,`icseqcategory`,
`iccategory1` ,
`iccategory2` ,
`icstatus` ,
`icad`
)
VALUES
('raymond','e','main1','1','Landed Houses','Cat1','1','SALE'),
('raymond','e','main2','1','Private Apartments','Cat2','2','SALE'),
('raymond','e','main3','1','HDB Apartments','Cat3','3','SALE'),
('raymond','e','main4','1','Landed Houses','Cat4','4','RENT'),
('raymond','e','main5','1','Private Apartments','Cat5','5','RENT'),
('raymond','e','main6','1','HDB Apartments','Cat6','6','RENT'),
('raymond','e','main7','1','Rooms','Cat7','7','RENT')
INSERT INTO `creative_dbmyo`.`myodetail` (
`icihusrid` ,
`iclanguage`,
`iccategory12`,`icseqcategory`,
`iccategory1` ,
`iccategory2` ,
`icstatus` ,
`icad`
)
VALUES
('ideal','c','main61','999','Home','Cat1','61','rent'),
('ideal','c','main62','999','Product-CatA','Cat2','62','rent'),
('ideal','c','main63','999','Product-CatB','Cat3','63','rent'),
('ideal','c','main64','999','Product-CatC','Cat4','64','rent'),
('ideal','c','main65','999','Product-CatD','Cat5','65','rent'),
('eugene','c','main66','1','Product-CatE','Cat6','66','rent'),
('eugene','c','main67','1','Contact us','Cat7','67','rent')
'c' for chinese, 'e' for english
1 to 7 for english, 61 to 67 for chinese
CREATE TABLE `creative_dbmyo`.`myopagehist` (
`iphid` INT NOT NULL AUTO_INCREMENT ,
`iphpagename` VARCHAR( 100 ) ,
`iphdateadd` VARCHAR( 8) ,
`iphcount` INT ,
PRIMARY KEY ( `iphid` )
) ENGINE = MYISAM
(Not required)
CREATE TABLE `creative_creative_dbmyo`.`parameter` (
`patype` VARCHAR( 20 ) NOT NULL ,
`pavalue` VARCHAR( 20 ) NOT NULL ,
`padescription` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `patype`,`pavalue`)
) ENGINE = MYISAM ;
INSERT INTO `creative_dbmyo`.`parameter` (
`patype` ,
`pavalue` ,
`padescription`
)
VALUES
('icadtype' , '1' , 'Studio Unit' ),
('icadtype' , '2' , 'Master Room' ),
('icadtype' , '3' , 'Common Room'),
('icadtype' , '4' , 'Student Accomodation' ),
('icadtype' , '5' , 'Backpackers Hostel' ),
('icstatus' , 'A' , 'Active' ),
('icstatus' , 'I' , 'Inactive' ),
('icstatus' , 'D' , 'Delete'),
CREATE TABLE `creative_wrdp1`.`hitpage` (
`pgeid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`pgename` TEXT NOT NULL ,
`pgehits` INT NOT NULL DEFAULT '0') ENGINE = MYISAM
INSERT INTO `creative_dbblanc`.`hitpage` (
`pgeid` ,
`pgename` ,
`pgehits`
)
VALUES
(NULL , 'chinese-where-to-stay-singapore.php' , '1' ),
(NULL , 'korean-where-to-stay-singapore.php' , '1' ),
(NULL , 'cn.inn.com.sg' , '1' )
CREATE TABLE `creative_wrdp1`.`hitpagehist` (
`iphid` INT NOT NULL AUTO_INCREMENT ,
`iphpagename` VARCHAR( 100 ) ,
`iphdateadd` VARCHAR( 8) ,
`iphcount` INT ,
PRIMARY KEY ( `iphid` )
) ENGINE = MYISAM
CREATE TABLE `creative_wrdp2`.`hitpage` (
`pgeid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`pgename` TEXT NOT NULL ,
`pgehits` INT NOT NULL DEFAULT '0') ENGINE = MYISAM
INSERT INTO `creative_wrdp2`.`hitpage` (
`pgeid` ,
`pgename` ,
`pgehits`
)
VALUES (
NULL , 'index.php' , '1' )
CREATE TABLE `creative_wrdp2`.`hitpagehist` (
`iphid` INT NOT NULL AUTO_INCREMENT ,
`iphpagename` VARCHAR( 100 ) ,
`iphdateadd` VARCHAR( 8) ,
`iphcount` INT ,
PRIMARY KEY ( `iphid` )
) ENGINE = MYISAM
CREATE TABLE `creative_dbblanc`.`frompage_en_inn_com_sg` (
`frmid` INT NOT NULL AUTO_INCREMENT ,
`frmdateadd` VARCHAR( 8) ,
`frmreferer` VARCHAR( 300 ) ,
`frmremoteaddr` VARCHAR( 30 ) ,
PRIMARY KEY ( `frmid` )
) ENGINE = MYISAM
Bus trip table
==================
CREATE TABLE `creative_dbblanc`.`bustrip` (
`busid` INT NOT NULL AUTO_INCREMENT ,
`busstatus` VARCHAR( 2 ) DEFAULT '0',
`busprsid` VARCHAR( 50 ) NOT NULL ,
`buspickupdate` VARCHAR( 8 ) NOT NULL ,
`buspickupyear` VARCHAR( 4 ) NOT NULL ,
`buspickupmonth` VARCHAR( 2 ) NOT NULL ,
`buspickupday` VARCHAR( 2 ) NOT NULL ,
`buspickuptime` VARCHAR( 4 ) NOT NULL ,
`buspickuppoint` VARCHAR( 50 ) NOT NULL ,
`busdestination` VARCHAR( 50 ) NOT NULL ,
`bustrippax` VARCHAR( 2 ) NOT NULL ,
`busprice` FLOAT(5.2) DEFAULT '0.1',
`buscompany` VARCHAR( 50 ) ,
`buscost` FLOAT(5.2) DEFAULT '0.1',
`busflightdetailremark` text ,
`busleadername` VARCHAR( 50 ) ,
`busleaderphone` VARCHAR( 50 ) ,
`busdutydrivername` VARCHAR( 50 ) ,
`buspaymentremark` VARCHAR( 150 ) ,
`busguestpaymentremark` VARCHAR( 150 ) ,
`busdateadd` VARCHAR( 8 ) ,
`bususeradd` VARCHAR( 50 ) ,
`busdatemodify` VARCHAR( 8 ) ,
`bususermodify` VARCHAR( 50 ) ,
PRIMARY KEY ( `busid` ) ,
UNIQUE (`busprsid`,`buspickupdate`,`buspickuptime` ) ,
INDEX ( `buscompany`,`buspickupdate`)
) ENGINE = MYISAM ;
busstatus Data
==============
INSERT INTO `creative_dbblanc`.`parameter` (
`patype` ,
`pavalue` ,
`padescription`
)
VALUES
('busstatus' , '0' , 'BusTrip NEW'),
('busstatus' , '1' , 'BusTrip Confirmed by Bus Company' ),
('busstatus' , '2' , 'BusTrip ReConfirmed by Bus Company' ),
('busstatus' , 'C0' , 'BusTrip REQUEST Cancel' ),
('busstatus' , 'C2' , 'BusTrip ACCEPTED Cancelled' ),
('busstatus' , 'P1' , 'BusTrip Paid coach' ),
('busstatus' , 'PP' , 'BusTrip Paid - from guest/to-coach' )
Bus company Driver Name/Phone data:
====================================
INSERT INTO `creative_dbblanc`.`operationmemo` (
`omoutlet` ,
`omdate` ,
`omowner` ,
`omremark1`
)
VALUES
('Tyrwhitt' , '20170718' , 'buscompanymatthew' , 'Driver Phone number' )
('Tyrwhitt' , '20170718' , 'busblancinn' , 'Blanc Inn Bus Blackboard' )