Difference between revisions of "ETK"

From SPGC Document Wiki
Jump to: navigation, search
 
(8 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
== Reset ETK password ==
+
[[Main_Page | Main Page]] > [[Application_Development_Team | Application Development Team]] > [[System_Support | System Support]]
  
 +
== '''Reset ETK password''' ==
 
# Open SQL Yog connect to 52 server
 
# Open SQL Yog connect to 52 server
 
# Get the employee number
 
# Get the employee number
Line 6: Line 7:
 
# 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''' ==
 
# Open SQL Yog connect to 52 server
 
# Open SQL Yog connect to 52 server
 
# Get the employee number
 
# Get the employee number
 
# Run this query  
 
# Run this query  
 
# 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"
  
−
== PAYROLL ==
+
== '''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'
 +
 
 +
[[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

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'

Main Page > Application Development Team > System Support

Retrieved from "http://115.85.30.86:286/spgcwiki/index.php?title=ETK&oldid=3940"