USE hotel;

ALTER TABLE hotel.tenantd  CHANGE GuestNotes GuestNotes VARCHAR(75);
ALTER TABLE hotel.Trans    CHANGE GuestNotes GuestNotes VARCHAR(75);

CREATE TABLE hotel.MasterTempPC1 (
   sn INT,
   AccNo VARCHAR(20),
   Clasify VARCHAR(20),
   EClasify VARCHAR(20),
   Docno VARCHAR(20),
   xdate DATE,
   Credit DECIMAL(18,3) DEFAULT '0',
   Debit DECIMAL(18,3) DEFAULT '0'
) ENGINE = InnoDB ROW_FORMAT = DEFAULT;

CREATE TABLE IF Not Exists MasterTempPC2  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC3  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC4  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC5  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC6  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC7  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC8  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC9  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC10  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC11  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC12  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC13  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC14  LIKE MasterTempPC1;
CREATE TABLE IF Not Exists MasterTempPC15  LIKE MasterTempPC1;

ALTER TABLE hotel.status   ADD RmName VARCHAR(20) AFTER Description;
update status set RmName=Room;

ALTER TABLE hotel.roombalance   ADD RmName VARCHAR(20) AFTER RoomNo;
ALTER TABLE hotel.mmovement     ADD RmName VARCHAR(20) AFTER XTime;
ALTER TABLE hotel.invdetails    ADD costCenter INT AFTER PF;
ALTER TABLE hotel.Transinvdetails  ADD costCenter INT AFTER PF;
ALTER TABLE hotel.temp   ADD NationalID VARCHAR(20);


CREATE TABLE `details` (
  `AgencyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(45) DEFAULT NULL,
  `FromDate` datetime DEFAULT NULL,
  `ToDate` datetime DEFAULT NULL,
  `ReservNo` varchar(45) DEFAULT NULL,
  `Days` int(10) unsigned DEFAULT NULL,
  `RoomType` varchar(45) DEFAULT NULL,
  `RoomCount` int(10) unsigned DEFAULT NULL,
  `Persons` int(10) unsigned DEFAULT NULL,
  `Notes` varchar(45) DEFAULT NULL,
  `ResvGrant` int(10) unsigned DEFAULT NULL,
  `RoomNo` int(10) unsigned DEFAULT NULL,
  `Assigned` int(10) unsigned DEFAULT NULL,
  `Email` varchar(45) DEFAULT NULL,
  `Phone` varchar(45) DEFAULT NULL,
  `AsgRoom` int(10) unsigned DEFAULT NULL,
  `AccNo` int(10) unsigned DEFAULT NULL,
  `Status` int(10) unsigned DEFAULT NULL,
  `ArrivStatus` varchar(45) DEFAULT NULL,
  `ReservDate` date DEFAULT NULL,
  `OpName` varchar(40) DEFAULT NULL,
  `ismeeting` varchar(20) DEFAULT NULL,
  `MeetingName` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`AgencyID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

CREATE TABLE `board` (
  `Xdate` date NOT NULL,
  `S1B` int(10) NOT NULL DEFAULT '0',
  `King` int(10) unsigned NOT NULL DEFAULT '0',
  `D1B` int(10) unsigned NOT NULL DEFAULT '0',
  `D2B` int(10) unsigned NOT NULL DEFAULT '0',
  `T3B` int(10) unsigned NOT NULL DEFAULT '0',
  `Deluxe` int(10) unsigned NOT NULL DEFAULT '0',
  `BMSuit` int(10) unsigned NOT NULL DEFAULT '0',
  `FSuit` int(10) unsigned NOT NULL DEFAULT '0',
  `Total` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`Xdate`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `rtypes` (
  `RType` int(10) unsigned NOT NULL DEFAULT '0',
  `RName` varchar(45) DEFAULT NULL,
  `Reservcode` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`RType`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

use Hotel;

use hotel;
ALTER TABLE hotel.rightsnew
 ADD uid INT AFTER uname;

update rightsnew a inner join staffd b on a.uname=b.operator set a.uid=b.operatorID;

update rightsnew SET
   A1 =  1    ,A2 = 1,    A3 = 1    ,A4 = 1    ,A5 = 1    ,A6 = 1    ,A7 = 1    ,A8 = 1    ,A9 = 1    ,A10 = 1    ,A11 = 1    ,A12 = 1    ,A13 = 1  
  ,A14 = 1    ,A15 = 1    ,A16 = 1    ,A17 = 1    ,A18 = 1    ,A19 = 1    ,A20 = 1    ,A21 = 1    ,A22 = 1    ,A23 = 1    ,A24 = 1    ,A25 = 1  
  ,A26 = 1    ,A27 = 1    ,A28 = 1    ,A29 = 1    ,A30 = 1    ,A31 = 1    ,A32 = 1    ,A33 = 1    ,A34 = 1    ,A35 = 1    ,A36 = 1    ,A37 = 1  
  ,A38 = 1    ,A39 = 1    ,A40 = 1    ,A41 = 1    ,A42 = 1    ,A43 = 1    ,A44 = 1    ,A45 = 1    ,A46 = 1    ,A47 = 1    ,A48 = 1    ,A49 = 1  
  ,A50 = 1    ,A51 = 1    ,A52 = 1    ,A53 = 1    ,A54 = 1    ,A55 = 1    ,A56 = 1    ,A57 = 1    ,A58 = 1    ,A59 = 1    ,A60 = 1    ,A61 = 1  
  ,A62 = 1    ,A63 = 1    ,A64 = 1    ,A65 = 1    ,A66 = 1    ,A67 = 1    ,A68 = 1    ,A69 = 1    ,A70 = 1    ,A71 = 1    ,A72 = 1    ,A73 = 1  
  ,A74 = 1    ,A75 = 1    ,A76 = 1    ,A77 = 1    ,A78 = 1    ,A79 = 1    ,A80 = 1    ,A81 = 1    ,A82 = 1    ,A83 = 1    ,A84 = 1    ,A85 = 1  
  ,A86 = 1    ,A87 = 1    ,A88 = 1    ,A89 = 1    ,A90 = 1    ,A91 = 1    ,A92 = 1    ,A93 = 1    ,A94 = 1    ,A95 = 1    ,A96 = 1    ,A97 = 1  
  ,A98 = 1    ,A99 = 1    WHERE uid= 2;
  
  update rightsnew set a83=1, a84=1, a85=1, a86=1, a87=1;
  
  ALTER TABLE hotel.`return`
 ADD MoneyType INT DEFAULT '0' AFTER OpID;
 update hotel.`return` set MoneyType=0;
   
  ALTER TABLE hotel.`trans_return`
 ADD MoneyType INT DEFAULT '0' AFTER OpID;
  update hotel.`trans_return` set MoneyType=0;
  
  CREATE TABLE `openmove` (
  `agencyid` int(11) NOT NULL DEFAULT '0',
  `debit` decimal(18,3) DEFAULT '0.000',
  `credit` decimal(18,3) DEFAULT '0.000',
  PRIMARY KEY (`agencyid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE hotel.specialprice
 ADD sn INT AUTO_INCREMENT ,
  ADD PRIMARY KEY (sn);

  ALTER TABLE hotel.invdetails
 ADD Servcode INT(10) DEFAULT '0';

 ALTER TABLE hotel.transinvdetails
 ADD Servcode INT(10) DEFAULT '0';

 ALTER TABLE hotel.price
 ADD price5 DECIMAL(18,3) DEFAULT '0' AFTER RType,
 ADD price6 DECIMAL(18,3) DEFAULT '0',
 ADD xpic VARCHAR(75) AFTER price6;


 ALTER TABLE hotel.roomcatag
 ADD price5 DECIMAL(18,3) DEFAULT '0' AFTER RType,
 ADD price6 DECIMAL(18,3) DEFAULT '0',
 ADD xpic VARCHAR(75) AFTER price6;
 
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--  //////////////////////////////////////////////////////// 22/07/2018
 CREATE TABLE `vouchers` (
  `Sn` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL,
  `docno` int(11) NOT NULL,
  PRIMARY KEY (`Sn`,`code`,`docno`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- /////////////////////////////////////////////////////////////////////////////////////////
 ALTER TABLE hotel.staffd
 CHANGE Password Pass_word VARCHAR(45) NOT NULL;

 -- find duplicate receipt
Select receiptno, count(receiptno) from receipt group by receiptno having COUNT(receiptno)>1

ALTER TABLE hotel.staffd  CHANGE Pass_word Password1 VARCHAR(45) NOT NULL;
   
ALTER TABLE hotel.Transnewdaily  ADD Agencyid INT DEFAULT '-1' AFTER ServCode;

ALTER TABLE hotel.Newdaily  ADD Agencyid INT DEFAULT '-1' AFTER ServCode;

-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--  //////////////////////////////////////////////////////// 25/04/2019

ALTER TABLE hotel.tempdailystnew ADD CApaidout DECIMAL(12,3) DEFAULT '0' AFTER CAPayment;

ALTER TABLE hotel.DailyStatementNew ADD CApaidout DECIMAL(12,3) DEFAULT '0' AFTER CAPayment;

insert into accno (accno,accname,Acctype,AccEname) values (78,'استرجاع حسابات جارية',2,'CAPaidOut' );
 
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
--  //////////////////////////////////////////////////////// 12/06/2019 
CREATE TABLE hotel.roombalanceCA (
   RoomNo INT(10) UNSIGNED NOT NULL DEFAULT '0',
   RmName VARCHAR(20),
   Pf INT(10) UNSIGNED,
   Invoce_No INT(10) UNSIGNED,
   EnterD DATETIME,
   TenantN VARCHAR(45),
   Agent VARCHAR(45),
   AgencyID INT(10) UNSIGNED,
   Credit DECIMAL(12,3) UNSIGNED DEFAULT '0.000',
   Debit DECIMAL(12,3) UNSIGNED DEFAULT '0.000',
   CB DECIMAL(12,3) DEFAULT '0.000',
   DB DECIMAL(12,3) DEFAULT '0.000',
   AccNo INT(10) UNSIGNED,
  PRIMARY KEY (RoomNo)
) ENGINE = innodb ROW_FORMAT = DEFAULT;
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE TABLE `excludeforbsrvs` (
  `sn` int(11) NOT NULL AUTO_INCREMENT,
  `AgencyID` int(11) NOT NULL,
  `InvoiceNo` int(11) DEFAULT NULL,
  `RoomNo` int(11) NOT NULL,
  `ServCode` int(11) NOT NULL,
  `MaxAmount` decimal(18,3) DEFAULT '0.000',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE `forbiddensrvs` (
  `SN` int(11) NOT NULL AUTO_INCREMENT,
  `Agencyid` int(11) NOT NULL,
  `ServCode` int(11) NOT NULL,
  `maxamount` decimal(18,3) DEFAULT '0.000',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`SN`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE TABLE `mosadaka` (
  `sn` int(11) NOT NULL AUTO_INCREMENT,
  `Agencyid` int(11) DEFAULT NULL,
  `issuedate` date DEFAULT NULL,
  `mosadakaDate` date DEFAULT NULL,
  `AttentionMR` varchar(150) DEFAULT NULL,
  `remarks` varchar(200) DEFAULT NULL,
  `amount` decimal(18,3) DEFAULT '0.000',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
ALTER TABLE hotel.rightsnew
 ADD A100 TINYINT(1) NOT NULL DEFAULT '0' AFTER A99,
 ADD A101 TINYINT(1) NOT NULL DEFAULT '0' AFTER A100,
 ADD A102 TINYINT(1) NOT NULL DEFAULT '0' AFTER A101, 
 ADD A103 TINYINT(1) NOT NULL DEFAULT '0' AFTER A102,
 ADD A104 TINYINT(1) NOT NULL DEFAULT '0' AFTER A103,
 ADD A105 TINYINT(1) NOT NULL DEFAULT '0' AFTER A104,
 ADD A106 TINYINT(1) NOT NULL DEFAULT '0' AFTER A105;
-- /////////////////////////////////////////////////////////////////////////////////        23/09/2019  ///////////////////////////////////////////////////
ALTER TABLE hotel.return RENAME hotel.paidout;
ALTER TABLE forbiddensrvs Add InvoiceNo INT AFTER Sn;
ALTER TABLE hotel.mosadaka
CHANGE sn sn INT(11) AUTO_INCREMENT NOT NULL DEFAULT '0';
-- /////////////////////////////////////////////////////////////////////////////////        23/09/2019  ///////////////////////////////////////////////////

ALTER TABLE hotel.cashsp  CHANGE roomno roomno VARCHAR(10) NOT NULL;

ALTER TABLE hotel.changes  CHANGE FromRoom FromRoom VARCHAR(10) NOT NULL,  CHANGE ToRoom ToRoom VARCHAR(10) NOT NULL;

ALTER TABLE hotel.dailyreserv  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.details  CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.excludeforbsrvs  CHANGE RoomNo RoomNo VARCHAR(11) NOT NULL;

ALTER TABLE hotel.immediate  CHANGE Roomno Roomno VARCHAR(10) NOT NULL;

ALTER TABLE hotel.invoces  CHANGE RoomNo RoomNo VARCHAR(11), CHANGE Residance Residance DECIMAL(18,3) DEFAULT '0', CHANGE Restaurant Restaurant DECIMAL(18,3) DEFAULT '0';

ALTER TABLE hotel.maintrequest  CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.mmovement  CHANGE RoomNO RoomNO VARCHAR(10);

ALTER TABLE hotel.moneytrans  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.newdaily  CHANGE Room Room VARCHAR(10) NOT NULL;

ALTER TABLE hotel.paidout CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.pcmovement  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.portfolio CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL; 

-- //////////////////////////////////////////////////////////////////////////
ALTER TABLE hotel.pospc1  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.pospc2  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.pospc3  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.pospc4  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.pospc5  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.pospc6  CHANGE RoomNo RoomNo VARCHAR(11);
-- //////////////////////////////////////////////////////////////////////////

ALTER TABLE hotel.price  CHANGE ROOM ROOM VARCHAR(10) NOT NULL DEFAULT '0';

ALTER TABLE hotel.receipt CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.roombalance  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.roombalanceca  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.roomcatag  CHANGE ROOM ROOM VARCHAR(10) NOT NULL;

ALTER TABLE hotel.roomitems  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.roomnotes  CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.status CHANGE ROOM ROOM VARCHAR(10) NOT NULL;

ALTER TABLE hotel.temp_service  CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.tempfees CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.tempnetcharge  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

-- //////////////////////////////////////////////////////////////////////////
ALTER TABLE hotel.temppc1  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.temppc2  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.temppc3  CHANGE RoomNo RoomNo VARCHAR(11);
ALTER TABLE hotel.temppc4  CHANGE RoomNo RoomNo VARCHAR(11);
-- //////////////////////////////////////////////////////////////////////////

ALTER TABLE hotel.tenantd  CHANGE RoomNo RoomNo VARCHAR(10) NOT NULL;

ALTER TABLE hotel.trans CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.trans_receipt CHANGE RoomNo RoomNo VARCHAR(10);

ALTER TABLE hotel.trans_return  CHANGE RoomNo RoomNo VARCHAR(11);

-- //////////////////////////////////////////////////////////////////////////
ALTER TABLE hotel.price ADD roomid INT AFTER Price6;
-- //////////////////////////////////////////////////////////////////////////
ALTER TABLE hotel.mastersp  CHANGE accno accno VARCHAR(10) NOT NULL, CHANGE price price DECIMAL(10,3) NOT NULL;
-- //////////////////////////////////////////////////////////////////////////
ALTER TABLE hotel.tenantd  ADD TelNo VARCHAR(25) AFTER Address;
ALTER TABLE hotel.trans  ADD TelNo VARCHAR(25) AFTER Address;
ALTER TABLE hotel.tempdata1  ADD TelNo VARCHAR(25) AFTER Address;

ALTER TABLE hotel.tenantd  ADD CarType VARCHAR(25) AFTER Address;
ALTER TABLE hotel.trans  ADD CarType VARCHAR(25) AFTER Address;
ALTER TABLE hotel.tempdata1  ADD CarType VARCHAR(25) AFTER Address;

ALTER TABLE hotel.tenantd  ADD CarBoardNo VARCHAR(25) AFTER Address;
ALTER TABLE hotel.trans  ADD CarBoardNo VARCHAR(25) AFTER Address;
ALTER TABLE hotel.tempdata1  ADD CarBoardNo VARCHAR(25) AFTER Address;

-- //////////////////////////////////////////////////////////////////////////
CREATE TABLE `tempdata1` (
  `TenantN` varchar(35) DEFAULT NULL,
  `Passport` varchar(15) DEFAULT NULL,
  `RoomNo` varchar(10) ,
  `Bd` varchar(45) DEFAULT NULL,
  `ETIME` time DEFAULT NULL,
  `Bp` varchar(45) DEFAULT NULL,
  `Address` varchar(45) DEFAULT NULL,
  `Single` int(10) unsigned DEFAULT NULL,
  `EnterD` date NOT NULL,
  `COMEFROM` varchar(45) DEFAULT NULL,
  `Gender` varchar(45) DEFAULT NULL,
  `ShiftNo` int(10) unsigned DEFAULT NULL,
  `Pf` varchar(45) NOT NULL,
  `Exitdate` date DEFAULT NULL,
  `Groupname` varchar(45) DEFAULT NULL,
  `Invoice_No` int(10) unsigned DEFAULT NULL,
  `GuestNotes` varchar(45) DEFAULT NULL,
  `GuestType` int(10) unsigned DEFAULT NULL,
  `Email` varchar(45) DEFAULT NULL,
  `Goingto` varchar(45) DEFAULT NULL,
  `AccNo` int(10) unsigned DEFAULT NULL,
  `NationID` int(10) unsigned DEFAULT NULL,
  `AgencyID` int(10) unsigned DEFAULT NULL,
  `GStatus` int(10) unsigned DEFAULT NULL,
  `Job` varchar(45) DEFAULT NULL,
  `InvFlag` varchar(45) DEFAULT NULL,
  `ID` int(10) unsigned NOT NULL DEFAULT '0',
  `EXITTIME` time DEFAULT NULL,
  `invoce_No` int(11) DEFAULT NULL,
  `OpID` int(11) DEFAULT NULL,
  `NationalID` varchar(45) DEFAULT NULL,
  `TelNo` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF Not Exists tempdata2 LIKE tempdata1;
CREATE TABLE IF Not Exists tempdata3 LIKE tempdata1;
CREATE TABLE IF Not Exists tempdata4 LIKE tempdata1;
CREATE TABLE IF Not Exists tempdata5 LIKE tempdata1;
CREATE TABLE IF Not Exists tempdata6 LIKE tempdata1;
-- //////////////////////////////////////////////////////////////////////////