Difference between revisions of "ETK"

Revision as of 15:47, 16 June 2016

Reset ETK password

1. Open SQL Yog connect to 52 server
2. Get the employee number
3. Run this query
4. UPDATE myleave.myuser SET password=md5("init1234") WHERE myusername="123456"

Transfer Employee from RNF to VIP

1. Open SQL Yog connect to 52 server
2. Get the employee number
3. Run this query
4. UPDATE myleave.olvemp SET emppccd=2 WHERE empnumb="123456"
5. UPDATE myleave.myuser SET emppccd=2,myuseraccess="VIP" WHERE empnumb="123456"

== Checked filled transaction.txt== // FILED LEAVE

Select * from myleave.lv2016 where loanumb = '15002535'

//FILED OT

select * from myleave.ot2016 where otmnumb = '15002535'

//FILED OBTP

Select * from myleave.ob2016 where obtmnumb = '15002535'

//FILED UNDERTIME

Select * from myleave.un2016 where untmnumb = '15002535'

ETK Change Department Name.txt

sql for updating department table and employee table

TEC MPCD

SELECT * FROM myleave.olvemp WHERE empdepcd = 'TEC MPCD' UPDATE myleave.olvemp SET empdepcd = 'TEC RMD' WHERE empdepcd = 'TEC MPCD'

ETK Reset Password.txt

• /updating PASSWORD TO ETK FILING */

SELECT * FROM mypmsdb.pmtempl WHERE emplnam = 'HALLY'

SELECT * FROM myleave.myusers WHERE myusername = '14003834';

SELECT * FROM UPDATE myleave.myusers SET `myuserpass` = MD5('init1234') WHERE myusername = '14003834';

Online Efiling Creation of Account.txt

/* adding new created employee to online filing system employee profile table

• /

INSERT IGNORE INTO `myleave`.`olvemp`

           (`empuser`,
            `empnumb`,
            `emplnam`,
            `empmnam`,
            `empfnam`,
            `empgender`,
            `empstat`,
            `empcocd`,
            `emppccd`)
            

SELECT xx.EMVNUMB,xx.EMVNUMB __ENUMB,yy.__LNAM,yy.__MNAM,yy.__FNAM,yy.EMPSXCD,xx.EMVESCD,xx.EMVCOCD,xx.EMVPCCD FROM mypmsdb.pmtemve xx JOIN (SELECT aa.EMPNUMB,TRIM(aa.EMPLNAM) __LNAM,TRIM(aa.EMPFNAM) __FNAM,TRIM(aa.EMPMNAM) __MNAM,aa.EMPSXCD, MAX(DATE(bb.EMVEDTE)) __EDTE FROM mypmsdb.pmtempl aa,mypmsdb.pmtemve bb WHERE aa.EMPNUMB = bb.EMVNUMB GROUP BY EMVNUMB) yy ON (xx.EMVNUMB = yy.EMPNUMB AND DATE(xx.EMVEDTE) = DATE(yy.__EDTE)) /*------------------------------------*/

SELECT * FROM ( SELECT xx.EMVNUMB,xx.EMVNUMB __ENUMB ,yy.__LNAM,yy.__FNAM,yy.__MNAM,yy.EMPSXCD,xx.EMVESCD,xx.EMVCOCD,xx.EMVPCCD FROM mypmsdb.pmtemve xx JOIN (SELECT aa.EMPNUMB,TRIM(aa.EMPLNAM) __LNAM,TRIM(aa.EMPFNAM) __FNAM,TRIM(aa.EMPMNAM) __MNAM,aa.EMPSXCD, MAX(DATE(bb.EMVEDTE)) __EDTE FROM mypmsdb.pmtempl aa,mypmsdb.pmtemve bb WHERE aa.EMPNUMB = bb.EMVNUMB GROUP BY EMVNUMB) yy ON (xx.EMVNUMB = yy.EMPNUMB AND DATE(xx.EMVEDTE) = DATE(yy.__EDTE)) ) oa WHERE EMVNUMB NOT IN( SELECT myusername FROM myleave.myusers )

/* vip --------------------------------------------------- */ /*------------------------------------*/ /* adding new created employee to online filing system login table

• /

INSERT IGNORE INTO `myleave`.`myusers`

           (`myusername`,
            `myuserfname`,
            `myuserlname`,
            `myusermname`,
            `myuserpass`,
            `myuserlevel`,
            `myuservalis`,
            `myuservalie`,
            `myuseracomp`,
            `myusertype`,
            `myuseraccess`)

SELECT * FROM ( SELECT xx.EMVNUMB,yy.__FNAM,yy.__LNAM,yy.__MNAM,MD5('init1234') __MPASS,'S' __ULEVEL, NOW() __MSTART,DATE_ADD(NOW(),INTERVAL 365 DAY) __MEND,'essi','2','VIP' FROM mypmsdv.pmtemve xx JOIN (SELECT aa.EMPNUMB,TRIM(aa.EMPLNAM) __LNAM,TRIM(aa.EMPFNAM) __FNAM,TRIM(aa.EMPMNAM) __MNAM, MAX(DATE(bb.EMVEDTE)) __EDTE FROM mypmsdv.pmtempl aa,mypmsdv.pmtemve bb WHERE aa.EMPNUMB = bb.EMVNUMB GROUP BY EMVNUMB) yy ON (xx.EMVNUMB = yy.EMPNUMB AND DATE(xx.EMVEDTE) = DATE(yy.__EDTE)) WHERE (xx.EMVCOCD = '4' OR xx.EMVCOCD = 'C') ) oa WHERE EMVNUMB NOT IN( SELECT myusername FROM myleave.myusers )

/* adding new created employee to online filing system employee profile table

• /

INSERT IGNORE INTO `myleave`.`olvemp`

           (`empuser`,
            `empnumb`,
            `emplnam`,
            `empmnam`,
            `empfnam`,
            `empgender`,
            `empstat`,
            `empcocd`,
            `emppccd`)

SELECT xx.EMVNUMB,xx.EMVNUMB __ENUMB ,yy.__LNAM,yy.__MNAM,yy.__FNAM,yy.EMPSXCD,xx.EMVESCD,xx.EMVCOCD,xx.EMVPCCD FROM mypmsdv.pmtemve xx JOIN (SELECT aa.EMPNUMB,TRIM(aa.EMPLNAM) __LNAM,TRIM(aa.EMPFNAM) __FNAM,TRIM(aa.EMPMNAM) __MNAM,aa.EMPSXCD, MAX(DATE(bb.EMVEDTE)) __EDTE FROM mypmsdv.pmtempl aa,mypmsdv.pmtemve bb WHERE aa.EMPNUMB = bb.EMVNUMB GROUP BY EMVNUMB) yy ON (xx.EMVNUMB = yy.EMPNUMB AND DATE(xx.EMVEDTE) = DATE(yy.__EDTE))

SELECT * FROM ( SELECT xx.EMVNUMB,xx.EMVNUMB __ENUMB ,yy.__LNAM,yy.__MNAM,yy.__FNAM,yy.EMPSXCD,xx.EMVESCD,xx.EMVCOCD,xx.EMVPCCD FROM mypmsdv.pmtemve xx JOIN (SELECT aa.EMPNUMB,TRIM(aa.EMPLNAM) __LNAM,TRIM(aa.EMPFNAM) __FNAM,TRIM(aa.EMPMNAM) __MNAM,aa.EMPSXCD, MAX(DATE(bb.EMVEDTE)) __EDTE FROM mypmsdv.pmtempl aa,mypmsdv.pmtemve bb WHERE aa.EMPNUMB = bb.EMVNUMB GROUP BY EMVNUMB) yy ON (xx.EMVNUMB = yy.EMPNUMB AND DATE(xx.EMVEDTE) = DATE(yy.__EDTE)) ) oa WHERE EMVNUMB NOT IN( SELECT myusername FROM myleave.myusers )

SELECT * FROM myleave.olvemp WHERE empdepcd LIKE '%MPCD%'

SELECT * FROM myleave.olvdepthie WHERE deptcd LIKE '%MPCD%'

UPDATE myleave.olvdepthie SET deptcd = 'TEC RMD' WHERE deptcd = 'TEC MPCD'

Update OT Record 52.txt

SELECT * FROM myleave.olvemp WHERE emplnam = 'INAL'

SELECT * FROM myleave.ot2015 WHERE OTMNUMB = '20048129' AND OTMDATE = '2015-08-08'

UPDATE myleave.ot2015 SET OTMTTME = '1730' WHERE OTMNUMB = '20048129' AND OTMDATE = '2015-08-08'