Difference between revisions of "ETK"
| (4 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]] | ||
| + | |||
== '''Reset ETK password''' == | == '''Reset ETK password''' == | ||
# Open SQL Yog connect to 52 server | # Open SQL Yog connect to 52 server | ||
| Line 4: | Line 6: | ||
# Run this query | # Run this query | ||
# UPDATE myleave.myuser SET password=md5("init1234") WHERE myusername="123456" | # UPDATE myleave.myuser SET password=md5("init1234") WHERE myusername="123456" | ||
| − | |||
== '''Transfer Employee from RNF to VIP''' == | == '''Transfer Employee from RNF to VIP''' == | ||
| Line 12: | Line 13: | ||
# UPDATE myleave.olvemp SET emppccd=2 WHERE empnumb="123456" | # UPDATE myleave.olvemp SET emppccd=2 WHERE empnumb="123456" | ||
# UPDATE myleave.myuser SET emppccd=2,myuseraccess="VIP" WHERE empnumb="123456" | # UPDATE myleave.myuser SET emppccd=2,myuseraccess="VIP" WHERE empnumb="123456" | ||
| − | |||
== '''Checked filled transaction.txt''' == | == '''Checked filled transaction.txt''' == | ||
| Line 32: | Line 32: | ||
Select * from myleave.un2016 where untmnumb = '15002535' | Select * from myleave.un2016 where untmnumb = '15002535' | ||
| − | == ETK Change Department Name.txt == | + | == '''ETK Change Department Name.txt''' == |
sql for updating department table and employee table | sql for updating department table and employee table | ||
| Line 42: | Line 42: | ||
UPDATE myleave.olvemp SET empdepcd = 'TEC RMD' | UPDATE myleave.olvemp SET empdepcd = 'TEC RMD' | ||
WHERE empdepcd = 'TEC MPCD' | WHERE empdepcd = 'TEC MPCD' | ||
| − | |||
== '''ETK Reset Password.txt''' == | == '''ETK Reset Password.txt''' == | ||
| Line 53: | Line 52: | ||
SELECT * FROM | SELECT * FROM | ||
UPDATE myleave.myusers SET `myuserpass` = MD5('init1234') WHERE myusername = '14003834'; | UPDATE myleave.myusers SET `myuserpass` = MD5('init1234') WHERE myusername = '14003834'; | ||
| − | |||
== '''Online Efiling Creation of Account.txt''' == | == '''Online Efiling Creation of Account.txt''' == | ||
| Line 159: | Line 157: | ||
UPDATE myleave.olvdepthie SET deptcd = 'TEC RMD' | UPDATE myleave.olvdepthie SET deptcd = 'TEC RMD' | ||
WHERE deptcd = 'TEC MPCD' | WHERE deptcd = 'TEC MPCD' | ||
| − | |||
== '''Update OT Record 52.txt''' == | == '''Update OT Record 52.txt''' == | ||
| Line 167: | Line 164: | ||
UPDATE myleave.ot2015 SET OTMTTME = '1730' WHERE OTMNUMB = '20048129' AND OTMDATE = '2015-08-08' | UPDATE myleave.ot2015 SET OTMTTME = '1730' WHERE OTMNUMB = '20048129' AND OTMDATE = '2015-08-08' | ||
| + | |||
| + | [[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]] | ||
Latest revision as of 09:14, 1 September 2016
Main Page > Application Development Team > System Support
Contents
Reset ETK password
- Open SQL Yog connect to 52 server
- Get the employee number
- Run this query
- UPDATE myleave.myuser SET password=md5("init1234") WHERE myusername="123456"
Transfer Employee from RNF to VIP
- Open SQL Yog connect to 52 server
- Get the employee number
- Run this query
- UPDATE myleave.olvemp SET emppccd=2 WHERE empnumb="123456"
- 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'
