mysql的properties,sql-mysql.properties

#系统任务日志视图

AD_TASK_LOG_VIEW=select t1.logID,t1.startTime,t1.endTime,t1.result,t1.message,t2.*

from AD_TASK_LOG t1

left join AD_TASK t2 on t1.taskID=t2.taskID

#用户视图

SYS_USER_VIEW=select t1.userID,t1.userName,t1.loginName,t1.mobile,t1.departID,t1.leaderID,t1.workNo,t1.enterTime,

t1.leaveTime,t1.status,t1.loginCount,t1.lastLoginTime,t1.roleID,

t2.departName,t3.roleName,t4.userName as leaderName

from SYS_USER t1

left join SYS_DEPARTMENT t2 on t1.departID=t2.departID

left join SYS_ROLE t3 on t1.roleID=t3.roleID

left join SYS_USER t4 on t1.leaderID=t4.userID

#OA日报视图

OA_REPORT_DAY_VIEW=select t1.*,t2.workNo as workNo,t2.userName as userName,t2.leaderID,t2.departID,

t3.userName as checkUserName,t4.departName

from OA_REPORT_DAY t1

left join SYS_USER t2 on t1.userID=t2.userID

left join SYS_USER t3 on t1.checkUserID=t3.userID

left join SYS_DEPARTMENT t4 on t2.departID=t4.departID

WHERE t2.status=1

#工作日报明细视图

OA_REPORT_DAY_DETAIL_VIEW=select t1.*,t2.workNo as workNo,t2.userName as userName,t3.departName

from OA_REPORT_DAY t1

left join SYS_USER t2 on t1.userID=t2.userID

left join SYS_DEPARTMENT t3 on t2.departID=t3.departID

#根据用户权限获取授权的菜单

sys.menu.author.user.menu=select * from SYS_MENU

where used=1 and (authorRole is null or authorRole like ?) and (forbidRole is null or forbidRole not like ?)

#考勤补漏表视图

OA_WORK_SUPPLY_VIEW=SELECT t1.*,t2.workNo,t2.userName,t3.userName as checkUserName,t4.departName

FROM OA_WORK_SUPPLY t1

left join SYS_USER t2 on t1.userID=t2.userID

left join SYS_USER t3 on t1.checkUserID=t3.userID

left join SYS_DEPARTMENT t4 on t2.departID=t4.departID

#请假表视图

OA_WORK_APPLYTIME_VIEW=SELECT t1.*,t2.workNo,t2.userName,t3.userName as checkUserName,t4.departName

FROM OA_WORK_APPLYTIME t1

left join SYS_USER t2 on t1.userID=t2.userID

left join SYS_USER t3 on t1.checkUserID=t3.userID

left join SYS_DEPARTMENT t4 on t2.departID=t4.departID

#OA加班单视图

OA_WORK_OVERTIME_VIEW=select t1.*,t2.userName as userName,t3.userName as checkUserName,t4.departName

from OA_WORK_OVERTIME t1

left join SYS_USER t2 on t1.userID=t2.userID

left join SYS_USER t3 on t1.checkUserID=t3.userID

left join SYS_DEPARTMENT t4 on t2.departID=t4.departID

#离职申请视图

OA_LEAVE_APPLY_VIEW=select t1.*,t2.userName as departCheckUserName

from OA_LEAVE_APPLY t1

left join SYS_USER t2 on t1.departCheckUserID=t2.userID

#打卡记录表

REC_CLOCK_ON_VIEW=select t1.*,t2.userName as userName

from REC_CLOCK_ON t1

left join SYS_USER t2 on t1.userID=t2.userID

#合同记录视图

REC_CONTRACT_VIEW=select t1.*,t2.userName as userName

from REC_CONTRACT t1

left join SYS_USER t2 on t1.userID=t2.userID

#派车记录视图

REC_DISPATCH_CAR_VIEW=select t1.*,t2.userName as userName ,t3.carName,t3.carNo,t3.seatSum

from REC_DISPATCH_CAR t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_CAR t3 on t1.carID=t3.carID

#住宿记视图

REC_DORMITORY_VIEW=select t1.*,t2.userName as userName ,t3.dormitoryNo,t3.dormitoryName,t3.dormitoryType

from REC_DORMITORY t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_DORMITORY t3 on t1.dormitoryID=t3.dormitoryID

#评价记录视图

REC_EVALUATE_VIEW=select t1.*,t2.userName as userName,t2.userName as managerName

from REC_EVALUATE t1

left join SYS_USER t2 on t1.userID=t2.userID

left join SYS_USER t3 on t1.managerID=t3.userID

#体检记录视图

REC_EXAMINE_VIEW=select t1.*,t2.examineTime,t2.recordID

from SYS_USER t1

left join REC_EXAMINE t2 on t1.userID=t2.userID

#保险记录视图

REC_INSURANCE_VIEW=select t1.*,t2.userName as userName,t3.insureName,t3.insureSort

from REC_INSURANCE t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_INSURANCE t3 on t1.insureID=t3.insureID

#订餐记录视图

REC_MEAL_ORDER_VIEW=select t1.*,t2.userName as userName,t3.projectName

from REC_MEAL_ORDER t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_PROJECT t3 on t1.projectID=t3.projectID

#员工技能记录视图

REC_PRORESSION_VIEW=select t1.*,t2.userName as userName,t3.professionName,t3.professionSort,t3.professionGrade

from REC_PRORESSION t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_PROFESSION t3 on t1.professionID=t3.professionID

#培训管理视图

BAS_TRAIN_VIEW=select t1.*,t3.companyName

from BAS_TRAIN t1

left join BAS_INTER_COMPANY t3 on t1.companyID=t3.companyID

#培训记录视图

REC_TRAIN_VIEW=select t1.recordID,t1.userID,t2.userName,t4.companyName,t3.*

from REC_TRAIN t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_TRAIN t3 on t1.trainID=t3.trainID

left join BAS_INTER_COMPANY t4 on t3.companyID=t4.companyID

#用车记录视图

REC_VEHICLE_USED_VIEW=select t1.*,t2.userName as userName

from REC_VEHICLE_USED t1

left join SYS_USER t2 on t1.userID=t2.userID

#工程调度附件视图

OA_DISPATCH_WORK_FILE_VIEW=select t1.workFileID,t1.dispatchWorkID,t1.sequence,t2.*

from OA_DISPATCH_WORK_FILE t1

left join SYS_FILE t2 on t1.fileID=t2.fileID

#驾驶员信息视图

BAS_DRIVER_VIEW=select t1.*,t2.userName as userName

from BAS_DRIVER t1

left join SYS_USER t2 on t1.userID=t2.userID

#派工申请记录视图

REC_DISPATCH_USER_VIEW=select t1.*,t2.userName as userName,t3.engineerNo,t3.engineerName,t4.projectName

from REC_DISPATCH_USER t1

left join SYS_USER t2 on t1.userID=t2.userID

left join BAS_ENGINEER t3 on t1.engineerID=t3.engineerID

left join BAS_PROJECT t4 on t1.projectID=t4.projectID

#项目视图

BAS_PROJECT_VIEW=select t1.*,t2.engineerName

from BAS_PROJECT t1

left join BAS_ENGINEER t2 on t1.engineerID=t2.engineerID

#工程人员调度记录视图

OA_DISPATCH_USER_VIEW=select t1.*,t5.userName,t4.jobName,t4.jobGrade,t3.projectName,t2.engineerName

from OA_DISPATCH_USER t1

left join BAS_ENGINEER t2 on t1.engineerID=t2.engineerID

left join BAS_PROJECT t3 on t1.projectID=t3.projectID

left join BAS_WORK_TYPE t4 on t1.jobID=t4.jobID

left join SYS_USER t5 on t1.userID=t5.userID

#工程人员视图

OA_USER_DISPATCH_VIEW=select t1.userID,t1.workNo,t1.userName,t1.jobID,t2.jobName,t2.jobGrade

from SYS_USER t1

left join BAS_WORK_TYPE t2 on t1.jobID=t2.jobID

where t1.status=1 and t1.projectWoker=1

#工程申请调度视图

OA_DISPATCH_WORK_VIEW=select t1.*,t2.engineerName,t2.companyID,t3.companyName,t4.userName as dispatcherName

from OA_DISPATCH_WORK t1

left join BAS_ENGINEER t2 on t1.engineerID=t2.engineerID

left join BAS_INTER_COMPANY t3 on t2.companyID=t3.companyID

left join SYS_USER t4 on t1.dispatcherID=t4.userID

一键复制

编辑

Web IDE

原始数据

按行查看

历史