代碼規範化培訓
一. 變量命名規則:
1) 類變量
1> 第一個字母小寫,並注明變量類型
2> 明確定義變量是甚麼,第一個字母大寫
3> 如: iDogqty表示整形狗數量變量
2) 函數變量
1> 必須以下划槓開頭
2> 第一個字母小寫,並注明變量類型
3> 明確定義變量是甚麼,第一個字母大寫
4> 如: _iDogqty表示整形狗數量變量
3) 循環變量
1> 必須以i , j , k
4) 常量,只讀變量
1> 所有必須大寫, 明確定義變量是甚麼
2> 最後用一個下划槓加三個大寫表示常量類型
3> 如: DOG_NAME_STR表示狗名字是字符串型
二. 類,接口,函數,枚舉等命名規則
1) 類命名規則
1> 明確定義類是甚麼,每個單詞首字母大寫
2> 如: AnimalCry表示動物叫
2) 接口命名規則
1> 明確定義接口是甚麼,每個單詞首字母大寫
2> 以大寫I開頭
3> 如: IAnimalCry表示動物叫
3) 函數命名規則
1> 明確定義函數動作,單詞首字母大寫
2> 明確定義函數是甚麼,每個單詞首字母大寫
3> 如:GetDogPropertys()表示獲取狗的屬性方法,Update,Insert,Mount,Delete等等
4) 枚舉命名規則
1> 明確定義枚舉是甚麼,每個單詞首字母大寫
2> 枚舉值都必須大寫
3> 如: AnimalType {DOG,CAT,}
5) 事件命名規則
1> 明確定義事件是甚麼,每個單詞首字母大寫
2> 如果有可能的化在事件名字加動作名他們之間加下划槓
3> 如: Dog_Eat(….)狗吃東西事件
三. 注釋規則
1) 單行 (//)
1> 注釋在變量後面
2> 明確干甚麼用
2) 多行 (/* */)
1> 用在文件最開始處
2> 明確谁做甚麼在甚麼時候,為甚麼
3> 如: 圖1
3) XML (///)
1> 用在類或函數前
2> 函數或類干甚麼用,參數說明,有返回值說明是甚麼
圖 1
四. 長字符串或SQL語句規則
1> 長度不能超過需要用戶拉動左右滾動條
2> 如無特殊情況竟可能使用Format()格式
3> SQL語句需分組分行
4> 如:
Select *
From *
Where *
Group by *
Order by *
DELIMITER $$ USE `hrms`$$ DROP PROCEDURE IF EXISTS `INSERT_MONTH_ATTENDANCE_DATA`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERT_MONTH_ATTENDANCE_DATA`(IN EMPLISTSTR VARCHAR(1000),IN YEARMONTHDT DATE,IN OPEIDINT INT) SQL SECURITY INVOKER BEGIN /* Author Date Reason ========== =========== ============================ Jason Zhang 2013/11/04 插入考勤汇总数据 Jason Zhang 2013/11/24 Update Jason Zhang 2014/01/04 解决循环最后一天为下个月的问题 Jason Zhang 2014/02/24 处理离职人员考勤汇总问题 Jason Zhang 2014/02/26 处理新入职人员的考勤汇总 Jason Zhang 2014/02/28 处理当月入职与离职人员安实际考勤汇总 */ DECLARE ERRCODE INT DEFAULT 0; DECLARE ERRMSG VARCHAR(100) DEFAULT 'NO ERROR'; DECLARE MONTHFIRST DATE; #定义月的第一天 DECLARE MONTHLAST DATE; #定义月的最后一天 DECLARE EMPLISTCOPY VARCHAR(1000); DECLARE EMP_ID VARCHAR(50); #定义雇员ID DECLARE EMP_CODE VARCHAR(50); #定义雇员工号 DECLARE MONTHQTY INT DEFAULT 0; #月考勤审核记录数 DECLARE EMPERROR VARCHAR(1000); #审核不成功的雇员 DECLARE ISQUITE INT DEFAULT 0; #定义用户离职 DECLARE ISENTRY INT DEFAULT 0; #定义用户入职 DECLARE EMP_TMPLHOUR INT; #雇员迟到小时数 DECLARE EMP_TMPEHOUR INT; #雇员早退小时数 DECLARE EMP_TMPOTHOUR INT; #雇员加班小时数 DECLARE EMP_TMPRHOUR INT; #雇员实际小时数 DECLARE EMP_TMPPHOUR INT; #雇员全勤小时数 DECLARE EMP_TMPABHOUR INT; #雇员旷工小时数 DECLARE EMP_TMPIHOUR INT; #雇员病假小时数 DECLARE EMP_TMPTHOUR INT; #雇员事假小时数 DECLARE EMP_TMPLTOTAL DECIMAL(6,2); #雇员月迟到小时数 DECLARE EMP_TMPETOTAL DECIMAL(6,2); #雇员月早退小时数 DECLARE EMP_TMPOTTOTAL DECIMAL(6,2); #雇员月加班小时数 DECLARE EMP_TMPRTOTAL DECIMAL(8,2); #雇员月实际小时数 DECLARE EMP_TMPPTOTAL DECIMAL(8,2); #雇员月全勤小时数 DECLARE EMP_TMPABTOTAL DECIMAL(6,2); #雇员月旷工小时数 DECLARE EMP_TMPITOTAL DECIMAL(6,2); #雇员月病假小时数 DECLARE EMP_TMPTTOTAL DECIMAL(6,2); #雇员月事假小时数 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND BEGIN SET ERRCODE = 1; SET ERRMSG = 'HAVE ERROR'; END; START TRANSACTION; SET MONTHFIRST = DATE_SUB(YEARMONTHDT,INTERVAL EXTRACT(DAY FROM YEARMONTHDT)-1 DAY); SET MONTHLAST = LAST_DAY(YEARMONTHDT); SET EMPLISTCOPY = EMPLISTSTR; #循环用户 WHILE LOCATE(';',EMPLISTCOPY) > 0 DO SET ISQUITE = 0; SET ISENTRY = 0; SET EMP_ID = LEFT (EMPLISTCOPY,LOCATE(';',EMPLISTCOPY)-1); SET EMPLISTCOPY = SUBSTRING(EMPLISTCOPY,LOCATE(';',EMPLISTCOPY)+1); SELECT COUNT(*) INTO MONTHQTY FROM attendance_audit WHERE IS_AUDIT=1 AND IS_DELETE=0 AND IS_EXCEPTION=0 AND EMPLOYEE_ID=EMP_ID AND TO_DAYS(DATE) BETWEEN TO_DAYS(MONTHFIRST) AND TO_DAYS(MONTHLAST); #判断用户是/否当月离职 IF EXISTS (SELECT * FROM employee WHERE ID=EMP_ID AND QUIT_DATE IS NOT NULL AND DATE_FORMAT(QUIT_DATE,'%Y%m') = DATE_FORMAT(YEARMONTHDT,'%Y%m') ) THEN SET ISQUITE = 1; END IF; #判断用户是/否当月入职 IF EXISTS (SELECT * FROM employee WHERE ID=EMP_ID AND DATE_FORMAT(ENTRY_DATE,'%Y%m') = DATE_FORMAT(YEARMONTHDT,'%Y%m') ) THEN SET ISENTRY = 1; END IF; #判断用户月考勤审核记录是/否完善 IF (MONTHQTY <> DAYOFMONTH(MONTHLAST)) AND ISQUITE=0 AND ISENTRY=0 THEN SELECT EMPCODE INTO EMP_CODE FROM employee WHERE ID=EMP_ID; SET EMPERROR = CONCAT_WS('|',EMPERROR,EMP_CODE); ELSEIF ISQUITE = 1 OR (MONTHQTY = DAYOFMONTH(MONTHLAST)) OR ISENTRY=1 THEN IF NOT EXISTS (SELECT * FROM attendance_summary WHERE EMPLOYEE_ID=EMP_ID AND MONTH=DATE_FORMAT(MONTHFIRST,'%Y%m') AND IS_DELETE=0) THEN #初始化值 SET EMP_TMPLTOTAL = 0; SET EMP_TMPETOTAL = 0; SET EMP_TMPOTTOTAL = 0; SET EMP_TMPRTOTAL = 0; SET EMP_TMPPTOTAL = 0; SET EMP_TMPABTOTAL =0; SET EMP_TMPITOTAL = 0; SET EMP_TMPTTOTAL = 0; #循环日期 WHILE MONTHFIRST <= MONTHLAST DO #初始化值 SET EMP_TMPLHOUR = 0; SET EMP_TMPEHOUR = 0; SET EMP_TMPOTHOUR = 0; SET EMP_TMPRHOUR = 0; SET EMP_TMPPHOUR = 0; SET EMP_TMPABHOUR =0; SET EMP_TMPIHOUR = 0; SET EMP_TMPTHOUR = 0; #判断是/否存在这天的审核记录 IF EXISTS (SELECT * FROM attendance_audit WHERE EMPLOYEE_ID=EMP_ID AND DATE=MONTHFIRST AND IS_AUDIT=1 AND IS_DELETE=0 AND IS_EXCEPTION=0) THEN #赋值 SELECT PLAN_MINUTES,REAL_MINUTES,LATE_MINUTES,EARLY_MINUTES,REAL_OT_MINUTES,ABSENT_MINUTES INTO EMP_TMPPHOUR,EMP_TMPRHOUR,EMP_TMPLHOUR,EMP_TMPEHOUR,EMP_TMPOTHOUR,EMP_TMPABHOUR FROM attendance_audit WHERE EMPLOYEE_ID=EMP_ID AND DATE=MONTHFIRST AND IS_AUDIT=1 AND IS_DELETE=0 AND IS_EXCEPTION=0; #获取雇员事假分钟数 SET EMP_TMPTHOUR = GET_EMPLOYEE_THING_LEAVE_MINUTES(EMP_ID,MONTHFIRST); #获取雇员病假分钟数 SET EMP_TMPIHOUR = GET_EMPLOYEE_ILL_LEAVE_MINUTES(EMP_ID,MONTHFIRST); #求和 SET EMP_TMPLTOTAL = EMP_TMPLTOTAL + EMP_TMPLHOUR; SET EMP_TMPETOTAL = EMP_TMPETOTAL + EMP_TMPEHOUR; SET EMP_TMPOTTOTAL = EMP_TMPOTTOTAL + EMP_TMPOTHOUR; SET EMP_TMPRTOTAL = EMP_TMPRTOTAL + EMP_TMPRHOUR; SET EMP_TMPPTOTAL = EMP_TMPPTOTAL + EMP_TMPPHOUR; SET EMP_TMPABTOTAL = EMP_TMPABTOTAL + EMP_TMPABHOUR; SET EMP_TMPITOTAL = EMP_TMPITOTAL + EMP_TMPIHOUR; SET EMP_TMPTTOTAL = EMP_TMPTTOTAL + EMP_TMPTHOUR; END IF;#判断是/否存在这天的审核记录 SET MONTHFIRST = DATE_ADD(MONTHFIRST,INTERVAL 1 DAY); END WHILE;#循环日期 SET EMP_TMPLTOTAL = ROUND(EMP_TMPLTOTAL / 60 ,2); SET EMP_TMPETOTAL = ROUND(EMP_TMPETOTAL / 60 ,2); SET EMP_TMPOTTOTAL = ROUND(EMP_TMPOTTOTAL / 60,2); SET EMP_TMPRTOTAL = ROUND(EMP_TMPRTOTAL / 60,2); SET EMP_TMPPTOTAL = ROUND(EMP_TMPPTOTAL / 60,2); SET EMP_TMPABTOTAL = ROUND(EMP_TMPABTOTAL / 60,2); SET EMP_TMPITOTAL = ROUND(EMP_TMPITOTAL / 60,2); SET EMP_TMPTTOTAL = ROUND(EMP_TMPTTOTAL / 60,2); #插入记录到月考勤审核表中,判断是/否是当月入职或离职 IF ISQUITE=0 AND ISENTRY=0 THEN INSERT INTO attendance_summary (EMPLOYEE_ID,MONTH,FULL_HOUR,REAL_HOUR,OT_HOUR,THING_LEAVE_HOUR, ILL_LEAVE_HOUR,LATE_HOUR,EARLY_HOUR,ABSENTEEISM_HOURS,CREATE_BY,CREATE_DATE) VALUES (EMP_ID,DATE_FORMAT(MONTHLAST,'%Y%m'),174,(174-EMP_TMPTTOTAL-EMP_TMPITOTAL-EMP_TMPABTOTAL),EMP_TMPOTTOTAL,EMP_TMPTTOTAL, EMP_TMPITOTAL,EMP_TMPLTOTAL,EMP_TMPETOTAL,EMP_TMPABTOTAL,OPEIDINT,NOW()); ELSE INSERT INTO attendance_summary (EMPLOYEE_ID,MONTH,FULL_HOUR,REAL_HOUR,OT_HOUR,THING_LEAVE_HOUR, ILL_LEAVE_HOUR,LATE_HOUR,EARLY_HOUR,ABSENTEEISM_HOURS,CREATE_BY,CREATE_DATE) VALUES (EMP_ID,DATE_FORMAT(MONTHLAST,'%Y%m'),EMP_TMPPTOTAL,(EMP_TMPPTOTAL-EMP_TMPTTOTAL-EMP_TMPITOTAL-EMP_TMPABTOTAL),EMP_TMPOTTOTAL,EMP_TMPTTOTAL, EMP_TMPITOTAL,EMP_TMPLTOTAL,EMP_TMPETOTAL,EMP_TMPABTOTAL,OPEIDINT,NOW()); END IF; END IF; END IF;#判断用户月考勤审核记录是/否完善 SET MONTHFIRST = DATE_SUB(YEARMONTHDT,INTERVAL EXTRACT(DAY FROM YEARMONTHDT)-1 DAY); END WHILE;#循环用户 IF ERRCODE = 0 THEN SET ERRMSG = EMPERROR; COMMIT; ELSE ROLLBACK; END IF; SELECT ERRMSG AS 'ERRMSG'; END$$ DELIMITER ;
DELIMITER $$ USE `hrms`$$ DROP FUNCTION IF EXISTS `GET_EMPLOYEE_THING_LEAVE_MINUTES`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `GET_EMPLOYEE_THING_LEAVE_MINUTES`(EMPIDINT INT,MYDATEDT DATE) RETURNS INT(11) SQL SECURITY INVOKER BEGIN /* Author Date Reason ========== =========== ============================ Jason Zhang 2013/11/04 New Jason Zhang 2013/11/19 加入处理汉字的匹配 Jason Zhang 2014/02/10 加入对应员工序号判断条件和记录判断 Jason Zhang 2014/02/17 BetweenAnd修改为>= and <=模式 Jason Zhang 2014/02/25 排除请假跨休息班次 */ DECLARE LEAVETOTAL INT DEFAULT 0; DECLARE QTY INT DEFAULT 0; DECLARE SHIFTID INT DEFAULT 0; #判断是/否有事假记录存在 SELECT COUNT(*) INTO QTY FROM holidy a INNER JOIN parameter b ON a.HOLIDY_TYPE = b.VALMEMBER INNER JOIN parameter c ON a.STATUS = c.VALMEMBER WHERE a.IS_DELETE = 0 AND a.EMPLOYEE_ID = EMPIDINT AND TO_DAYS(MYDATEDT) >= TO_DAYS(a.REAL_BEGIN) AND TO_DAYS(MYDATEDT) <= TO_DAYS(a.REAL_END) AND b.NAME = 'HOLIDY TYPE' AND c.NAME = 'ORDER STATUS' AND b.DISMEMBER = N'事假' AND c.DISMEMBER = N'完成'; SET SHIFTID = GET_EMPLOYEE_SHIFT(EMPIDINT,MYDATEDT); #判断当前日期是/否为工作日 IF EXISTS (SELECT * FROM shift WHERE ID=SHIFTID AND IS_DELETE=0 AND NORMAL_MINUTES>0 ) THEN IF QTY > 0 THEN SELECT SUM(AVG_TIME) INTO LEAVETOTAL FROM holidy a INNER JOIN parameter b ON a.HOLIDY_TYPE = b.VALMEMBER INNER JOIN parameter c ON a.STATUS = c.VALMEMBER WHERE a.IS_DELETE = 0 AND a.EMPLOYEE_ID = EMPIDINT AND TO_DAYS(MYDATEDT) >= TO_DAYS(a.REAL_BEGIN) AND TO_DAYS(MYDATEDT) <= TO_DAYS(a.REAL_END) AND b.NAME = 'HOLIDY TYPE' AND c.NAME = 'ORDER STATUS' AND b.DISMEMBER = N'事假' AND c.DISMEMBER = N'完成'; END IF; END IF;#判断当前日期是/否为工作日 RETURN LEAVETOTAL; END$$ DELIMITER ;