bat restore

cd\
cd C:\Program Files\JHCIS\MySQL\bin
mysql --default-character-set=utf8 -uroot -p123456 -P3333 -f jhcisdb < "C:\\sqlBackupFileName.sql"
pause

create_person_FEDERATED ( remote sever table mysql )

DROP TABLE IF EXISTS `person_FEDERATED`;
CREATE TABLE `person_FEDERATED` (
  `pcucodeperson` char(5) NOT NULL default '',
  `pid` int(11) NOT NULL,
  `hcode` int(11) NOT NULL,
  `prename` varchar(20) default NULL,
  `fname` varchar(25) NOT NULL,
  `lname` varchar(35) default NULL,
  `birth` date default NULL,
  `sex` varchar(1) NOT NULL,
  `idcard` varchar(13) default NULL,
  `bloodgroup` varchar(2) default NULL,
  `bloodrh` varchar(1) default NULL,
  `allergic` varchar(100) default NULL,
  `marystatus` varchar(1) default NULL,
  `educate` char(2) default NULL,
  `occupa` char(4) default NULL,
  `nation` varchar(3) default NULL,
  `origin` varchar(3) default NULL,
  `intercode` varchar(2) default NULL,
  `religion` char(2) default NULL,
  `familyno` tinyint(4) default NULL,
  `familyposition` varchar(1) default NULL,
  `income` double default NULL,
  `typelive` varchar(1) default NULL,
  `datein` date default NULL,
  `dischargetype` varchar(1) default NULL,
  `dischargedate` date default NULL,
  `father` varchar(257) default NULL,
  `fatherid` varchar(13) default NULL,
  `mother` varchar(257) default NULL,
  `motherid` varchar(13) default NULL,
  `mate` varchar(257) default NULL,
  `mateid` varchar(13) default NULL,
  `privatedoc` varchar(255) default NULL,
  `rightcode` char(4) default NULL,
  `rightno` varchar(18) default NULL,
  `hosmain` char(9) default NULL,
  `hossub` char(9) default NULL,
  `dateregis` date default NULL,
  `datestart` date default NULL,
  `dateexpire` date default NULL,
  `officework` char(254) default NULL,
  `hnomoi` varchar(75) default NULL,
  `roadmoi` varchar(50) default NULL,
  `mumoi` char(2) default NULL,
  `subdistcodemoi` char(2) default NULL,
  `distcodemoi` char(2) default NULL,
  `provcodemoi` char(2) default NULL,
  `postcodemoi` char(5) default NULL,
  `telephoneperson` varchar(35) default NULL,
  `hcodeoldin` int(11) default NULL,
  `dateupdate` datetime default NULL,
  `flag18fileexpo` varchar(1) default NULL,
  `messengername` varchar(255) default NULL,
  `messengeraddr` varchar(255) default NULL,
  `messengertel` varchar(55) default NULL,
  `patientrelate` varchar(255) default NULL,
  `mommilk` int(11) default NULL,
  `persondisease` varchar(350) default NULL,
  `flagoffline` char(1) default NULL,
  `nickname` varchar(25) default NULL,
  `prenameeng` varchar(25) default NULL,
  `fnameeng` varchar(35) default NULL,
  `lnameeng` varchar(45) default NULL,
  `person_house_position_id_from_hosxp` int(11) default NULL,
  `Address from antidrug` text,
  `Update Date antidrug` date default NULL,
  `passpotnumber` varchar(37) default NULL,
  `workpermitnumber` varchar(37) default NULL,
  `hidmoi11` char(11) default NULL,
  `housetype` char(1) default NULL,
  `roomno` varchar(10) default NULL,
  `condo` varchar(75) default NULL,
  `soisub` varchar(255) default NULL,
  `soimain` varchar(255) default NULL,
  `dateupdateaddressout` datetime default NULL,
  `candobedhomesocial` char(1) default NULL,
  `beastprojectout` char(1) default NULL,
  PRIMARY KEY  (`pcucodeperson`,`pid`),
  UNIQUE KEY `id_Card` (`pcucodeperson`,`idcard`),
  UNIQUE KEY `RN` (`pcucodeperson`,`rightno`),
  KEY `house_pers` (`pcucodeperson`,`hcode`),
  KEY `per_educate` (`educate`),
  KEY `per_nation` (`nation`),
  KEY `per_race` (`origin`),
  KEY `per_occupa` (`occupa`),
  KEY `per_relig` (`religion`),
  KEY `per_right` (`rightcode`),
  KEY `per_status` (`marystatus`),
  KEY `per_hosmain` (`hosmain`),
  KEY `chos_pshs1` (`hossub`),
  CONSTRAINT `person_ibfk_1` FOREIGN KEY (`hossub`) REFERENCES `chospital` (`hoscode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_10` FOREIGN KEY (`occupa`) REFERENCES `coccupa` (`occupacode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_2` FOREIGN KEY (`origin`) REFERENCES `cnation` (`nationcode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_3` FOREIGN KEY (`religion`) REFERENCES `creligion` (`religioncode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_4` FOREIGN KEY (`rightcode`) REFERENCES `cright` (`rightcode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_5` FOREIGN KEY (`marystatus`) REFERENCES `cstatus` (`statuscode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_6` FOREIGN KEY (`pcucodeperson`, `hcode`) REFERENCES `house` (`pcucode`, `hcode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_7` FOREIGN KEY (`educate`) REFERENCES `ceducation` (`educationcode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_8` FOREIGN KEY (`hosmain`) REFERENCES `chospital` (`hoscode`) ON UPDATE CASCADE,
  CONSTRAINT `person_ibfk_9` FOREIGN KEY (`nation`) REFERENCES `cnation` (`nationcode`) ON UPDATE CASCADE
)
ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://root:123456@127.0.0.1:3333/jhcisdb/person';

ดูผู้ป่วย DM

'ต้องมี view ชื่อ cid จากข้อที่แล้ว
SELECT
substr(house.villcode,7,2) as moo,
house.hno,
person.pid,
person.idcard,
ctitle.titlename,
person.fname,
person.lname,
cid.`อายุ`
FROM
person
LEFT JOIN cid ON person.idcard = cid.cid2
INNER JOIN house ON person.pcucodeperson = house.pcucode AND person.hcode = house.hcode
LEFT JOIN ctitle ON ctitle.titlecode = person.prename
WHERE
cid.cid2 IS NOT NULL AND
LENGTH(cid.cid2) = 13 AND
cid.`ตำบล` = 'หนองหัวช้าง' AND
cid.`หมู่` IN ('1','2','5','8','9','12')
ORDER BY
house.villcode ASC

ตัด - ออกจาก cid (view ชื่อ cid)

select `dm_kr`.`CID` AS `CID`,concat(substr(`dm_kr`.`CID`,1,1),substr(`dm_kr`.`CID`,3,4),substr(`dm_kr`.`CID`,8,5),substr(`dm_kr`.`CID`,14,2),substr(`dm_kr`.`CID`,17,1)) AS `cid2`,`dm_kr`.`ลงทะเบียน` AS `ลงทะเบียน`,`dm_kr`.`HN` AS `HN`,`dm_kr`.`clinicno` AS `clinicno`,`dm_kr`.`fullname` AS `fullname`,`dm_kr`.`ptdob` AS `ptdob`,`dm_kr`.`อายุ` AS `อายุ`,`dm_kr`.`oldnew` AS `oldnew`,`dm_kr`.`cliname` AS `cliname`,`dm_kr`.`บ้านเลขที่` AS `บ้านเลขที่`,`dm_kr`.`หมู่` AS `หมู่`,`dm_kr`.`ตำบล` AS `ตำบล`,`dm_kr`.`อำเภอ` AS `อำเภอ`,`dm_kr`.`จังหวัด` AS `จังหวัด`,`dm_kr`.`typearea` AS `typearea`,`dm_kr`.`icd10` AS `icd10`,`dm_kr`.`หมายเหตุ` AS `หมายเหตุ` from `dm_kr`

sql injection

adm' or '1'='1

FEDERATED Table (remote database table connect)

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `ncd_person_ncd_screen_b4edit_ncd`;
CREATE TABLE `ncd_person_ncd_screen_b4edit_ncd` (
  `pcucode` char(5) NOT NULL DEFAULT '',
  `pid` varchar(13) NOT NULL,
  `no` int(6) NOT NULL,
  `age_year` int(6) NOT NULL,
  `screen_date` date NOT NULL,
  `height` decimal(6,2) NOT NULL,
  `weight` decimal(6,2) NOT NULL,
  `waist` int(3) NOT NULL,
  `hbp_s1` int(3) NOT NULL,
  `hbp_d1` int(3) NOT NULL,
  `screen_q1` varchar(1) NOT NULL,
  `screen_q2` varchar(1) NOT NULL,
  `screen_q3` varchar(1) NOT NULL,
  `screen_q4` varchar(1) NOT NULL,
  `screen_q5` varchar(1) NOT NULL,
  `screen_q6` varchar(1) NOT NULL,
  `do_measure` varchar(1) DEFAULT NULL,
  `hbp_s2` int(3) DEFAULT NULL,
  `hbp_d2` int(3) DEFAULT NULL,
  `bsl` decimal(6,2) DEFAULT NULL,
  `bmi` double NOT NULL,
  `result_new_dm` varchar(1) NOT NULL,
  `result_new_hbp` varchar(1) NOT NULL,
  `result_new_waist` varchar(1) NOT NULL,
  `result_new_obesity` varchar(1) NOT NULL,
  `d_update` date NOT NULL,
  `user_update` varchar(45) NOT NULL,
  `visitno` int(11) DEFAULT NULL,
  `smoke` char(1) DEFAULT NULL,
  `alcohol` char(1) DEFAULT NULL,
  `htfamily` char(1) DEFAULT NULL,
  `bstest` char(1) DEFAULT NULL,
  `dateupdate` datetime DEFAULT NULL,
  `servplace` char(1) DEFAULT NULL,
  `flag18fileexpo` char(1) DEFAULT NULL,
  `blackarmpit` char(1) DEFAULT NULL,
  `flagoffline` char(1) DEFAULT NULL,
  PRIMARY KEY (`pcucode`,`pid`,`no`)
) ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://root:123456@127.0.0.1:3333/jhcisdbb4ncdedit/ncd_person_ncd_screen';

crontab

# /etc/crontab: system-wide crontab
# Unlike any other crontab you don't have to run the `crontab'
# command to install the new version when you edit this file
# and files in /etc/cron.d. These files also have username fields,
# that none of the other crontabs do.
#mod by polngpole

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# m h dom mon dow user command
17 * * * * root    cd / && run-parts --report /etc/cron.hourly
25 6 * * * root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )
47 6 * * 7 root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly )
52 6 1 * * root test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )
40 17,19,23 * * 0-6 root /var/jhcis/mysql-backup-jhcisdb.sh >/dev/null 2>&1
30 0-23  * * 0-6 root    /var/jepidem/runjepidem.sh >/dev/null 2>&1
50 0-23  * * 0-6 root    /var/jxp18/runjxp18.sh >/dev/null 2>&1
20 13,16,21,02,06,10 * * 0-6 root /var/jautoup/runjauto.sh >/dev/null 2>&1
*/5 * * * * root /var/apache2/checkapache2.sh >/dev/null 2>&1

sudo /etc/init.d/crond restart

bk

# !/bin/sh
if [ ! -d /home/jhcis/Dropbox/backupjhcis ] ; then
sudo mkdir /home/jhcis/Dropbox/backupjhcis
sudo chmod 777 -fR /home/jhcis/Dropbox/backupjhcis
fi

sudo mysqldump   --default-character-set=utf8  -u  root  -p123456  jhcisdb --routines --compatible=no_key_options > /home/jhcis/Dropbox/backupjhcis/jhcisbackup.03301-$(date -d "+0 day" "+%Y-%m-%d").sql
sudo rm -f /home/jhcis/Dropbox/backupjhcis/jhcisbackup.03301-$(date -d "-7 day" "+%Y-%m-%d").sql
 
exit 0