dySdDesigner/conf/sqlroot/com/sdtool/business/dao/SystemDao.dql

-- [login]
SELECT
  id, loginName, userName,
  companyId, deptId, userType,
  email, phonenumber, sex,
  avatar, status, delFlag,
  remark, postIds
FROM $table$
WHERE loginName = #{loginName} AND password = #{password};


-- [queryLoginUsers]
select a.id, a.companyId, a.deptId, a.loginName,
       a.userName, a.userType, a.email, a.phoneNumber,
       a.sex, a.avatar, a.status, a.delFlag,
       a.createBy, a.createTime, a.updateBy,
       a.updateTime, a.remark, a.postIds, b.deptName
from $table1$ a
         left join $table2$ b on a.deptId = b.id
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- if deptId > 0
    and a.deptId = #{deptId}
  -- end
  -- if status >= 0
    and a.status = #{status}
  -- end
  -- isNotEmpty keyWord
    and (a.loginName LIKE #{Like:keyWord} or a.userName LIKE #{Like:keyWord})
  -- end
  -- isNotEmpty loginName
    and a.loginName LIKE #{Like:loginName}
  -- end
  -- isNotEmpty phone
    and a.phoneNumber LIKE #{Like:phoneNumber}
  -- end
  -- isNotEmpty beginTime
    and a.createTime >= #{beginTime}
  -- end
  -- isNotEmpty endTime
    and a.createTime <= #{endTime}
  -- end
-- end
-- isEmpty orderBy
order by createTime desc
-- else
order by #{orderBy}
-- end
;

-- [getUserInfo]
SELECT
  id, loginName, userName,
  companyId, deptId, userType,
  email, phonenumber, sex,
  avatar, status, delFlag,
  remark, postIds
FROM $table$
WHERE id = #{id};

-- [accountExists]
SELECT count(id)
FROM $table$
WHERE loginName = #{loginName};

-- [insertLoginUser]
insert into $table$ (
id, companyId, deptId,
loginName, userName, userType,
email, phonenumber, sex,
avatar, password, salt,
status, createBy,
createTime, updateBy, updateTime,
remark, postIds, shareCode)
values (
#{id}, #{companyid}, #{deptid},
#{loginname}, #{username}, #{usertype},
#{email}, #{phonenumber}, #{sex},
#{avatar}, #{password}, #{salt},
#{status}, #{createby},
#{createtime}, #{updateby}, #{updatetime},
#{remark}, #{postids}, #{sharecode}
);

-- [insertUserRole]
insert into $table$ (userId, roleId)
VALUES (#{userid}, #{roleid});

-- [clearUserRole]
delete from $table$ where userId = #{userId};

-- [updateLoginUser]
update $table$ set
deptId = #{deptid},
userName = #{username},
email = #{email},
phonenumber = #{phonenumber},
sex = #{sex},
updateBy = #{updateby},
updateTime = #{updatetime},
remark = #{remark},
postIds = #{postids}
where id = #{id};

-- [voidLoginUser]
update $table$ set
status = #{status}
where id = #{id};

-- [resetLoginUser]
update $table$ set
password = #{password}
where id = #{id};

-- [changeMyPassword]
UPDATE $table$ SET
password = #{password}
WHERE id = #{id};


-- [queryAllMenus]
select *
from $table$
-- isEmpty orderBy
order by parentId, sortNum
-- else
order by #{orderBy}
-- end
;

-- [queryAllRoles]
select *
from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- isNotEmpty roleName
    roleName LIKE #{Like:roleName}
  -- end
  -- if status >= 0
    and status = #{status}
  -- end
-- end
-- isEmpty orderBy
order by sortNum
-- else
order by #{orderBy}
-- end
;

-- [insertRole]
insert into $table$
(id, roleName,
 sortNum, status, createBy,
 createTime, updateBy, updateTime,
 remark, systemFlag)
VALUES (
  #{id}, #{rolename},
  #{sortnum}, #{status}, #{createby},
  #{createtime}, #{updateby}, #{updatetime},
  #{remark}, #{systemflag}
);

-- [updateRole]
update $table$ set
  roleName = #{rolename},
  sortNum = #{sortnum},
  updateBy = #{updateby},
  updateTime = #{updatetime},
  remark = #{remark}
where id = #{id};

-- [voidRole]
update $table$ set
  status = #{status}
where id = #{id};

-- [insertRoleMenu]
insert into $table$ (roleId, menuId) VALUES (#{roleid}, #{menuid});

-- [clearRoleMenu]
delete from $table$ where roleId = #{roleId};

-- [queryRoleMenus]
select a.*, b.perms as menuperms, b.menuType, b.parentId from $table1$ a
  left join $table2$ b on a.menuId = b.id
where a.roleId= #{roleId} and b.menuType in ('M', 'C');

-- [queryRoleActions]
select a.*, b.perms as menuperms, b.menuType, b.parentId from $table1$ a
  left join $table2$ b on a.menuId = b.id
where a.roleId= #{roleId};

-- [queryUserRoles]
select userId,roleId from $table$ WHERE userId = #{userid};

-- [queryMenuRoles]
select b.id, b.roleName, b.roleKey, b.sortNum, b.remark
from $table1$ a
left join $table2$ b on a.roleId = b.id
where a.menuId = #{menuId}
order by b.sortNum;

-- [queryRoleUsers]
select b.id, b.companyId, b.deptId, b.loginName, b.userName, b.email, b.phonenumber, b.status
from $table1$ a
left join $table2$ b on a.userId = b.id
where a.roleId = #{roleId}
order by b.id;

--[queryAllDepts]
select * from $table$

--[addDept]
insert into $table$ (id, parentId, deptName, deptCode,
sortNum, leader, phone,
email, status, parentName,
createBy, createTime, createTimeLong,
updateBy, updateTime, updateTimeLong ) values ( #{id}, #{parentid}, #{deptname}, #{deptcode},
#{sortnum}, #{leader}, #{phone},
#{email}, #{status}, #{parentname},
#{createby}, #{createtime},
#{createtimelong}, #{updateby}, #{updatetime},
#{updatetimelong} );

-- [updateDept]
update $table$ set parentId = #{parentid},deptName = #{deptname},deptCode = #{deptcode},sortNum = #{sortnum},
leader = #{leader},phone = #{phone},email = #{email},status = #{status},
parentName = #{parentname},
updateBy = #{updateby},updateTime = #{updatetime},updateTimeLong = #{updatetimelong}
 where id = #{id};

-- [voidDept]
update $table$ set
  status = #{status},
  updateBy = #{updateby},
  updateTime = #{updatetime}
where id=#{id};

-- [insertMenu]
insert into $table$
(id, menuName, parentId,
 sortNum, url, menuType,
 visible, cacheFlag, perms, icon,
 createBy, createTime, updateBy,
 updateTime, remark,
 route, pathName,
 runKind, delphiPackage, delphiParams,
 delphiRunKind, delphiRunForm, delphiFormShow)
values (
  #{id}, #{menuname}, #{parentid},
  #{sortnum}, #{url}, #{menutype},
  #{visible}, #{cacheflag}, #{perms}, #{icon},
  #{createby}, #{createtime}, #{updateby},
  #{updatetime}, #{remark},
  #{route}, #{pathname},
  #{runkind}, #{delphipackage}, #{delphiparams},
  #{delphirunkind}, #{delphirunform}, #{delphiformshow}
);

-- [updateMenu]
update $table$ set
  menuName = #{menuname},
  parentId = #{parentid},
  sortNum = #{sortnum},
  url = #{url},
  menuType = #{menutype},
  perms = #{perms},
  icon = #{icon},
  updateBy = #{updateby},
  updateTime = #{updatetime},
  remark = #{remark},
  cacheFlag = #{cacheflag},
  route = #{route},
  pathName = #{pathname},
  runKind = #{runkind},
  delphiPackage = #{delphipackage},
  delphiParams = #{delphiparams},
  delphiRunKind = #{delphirunkind},
  delphiRunForm = #{delphirunform},
  delphiFormShow = #{delphiformshow}
where id = #{id};

-- [voidMenu]
update $table$ set
  visible = #{visible},
  updateBy = #{updateby},
  updateTime = #{updatetime}
where id = #{id};

--[deleteRoleMenu]
delete from $table$ where menuId in (/* in ids */)

--[deleteMenu]
delete from $table$ where id in (/* in ids */)

-- [insertERPFile]
insert into $table$
(idUser, userName, fileKind,
srcFileName, fileDescription, downloadUrl,
createBy, createTime, createTimeLong,
updateBy, updateTime, updateTimeLong )
values (#{iduser}, #{username}, #{filekind},
#{srcfilename}, #{filedescription}, #{downloadurl},
#{createby}, #{createtime}, #{createtimelong},
#{updateby}, #{updatetime}, #{updatetimelong} );

-- [queryAllConfig]
select * from $table$;

-- [queryConfig]
select * from $table$
where configKey = #{configKey};

-- [insertConfigValue]
insert into $table$
(configKey, configName, configValue1,
 configValue2, configValue3, configType,
 remark)
VALUES (
  #{configkey}, #{configname}, #{configvalue1},
  #{configvalue2}, #{configvalue3}, #{configtype},
  #{remark}
);

-- [updateConfigValue]
update $table$ set
  configName = #{configname},
  configModule = #{configmodule},
  configValue1 = #{configvalue1},
  configValue2 = #{configvalue2},
  configValue3 = #{configvalue3},
  remark = #{remark}
where configKey = #{configkey};

-- [updateOnlyConfigValue]
update $table$ set
configValue1 = #{configvalue1}
where configKey = #{configkey};

-- [deleteConfigValue]
delete from $table$
where configKey = #{configkey};

-- [queryDictTypes]
select *
from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- if voidFlag >= 0
    voidFlag = #{voidFlag}
  -- end
  -- isNotEmpty keyWord
    and (dictName LIKE #{Like:keyWord} or dictType LIKE #{Like:keyWord})
  -- end
-- end
-- isEmpty orderBy
order by createTime desc
-- else
order by #{orderBy}
-- end
;

-- [addDictType]
insert into $table$
(id, dictName, dictType,
voidFlag, remark, createBy,
createTime, updateBy, updateTime)
values (
#{id}, #{dictname}, #{dicttype},
#{voidflag}, #{remark}, #{createby},
#{createtime}, #{updateby}, #{updatetime}
);

-- [updateDictType]
update $table$ set
dictName = #{dictname},
dictType = #{dicttype},
remark = #{remark},
updateBy = #{updateby},
updateTime = #{updatetime}
where id = #{id};

-- [voidDictType]
update $table$ set
voidFlag = #{voidflag},
updateBy = #{updateby},
updateTime = #{updatetime}
where id = #{id};

-- [queryDictDatas]
select *
from $table$
-- trim prefix=WHERE prefixOverrides=AND|OR
  -- if idType >= 0
    idType = #{idType}
  -- end
  -- if voidFlag >= 0
    voidFlag = #{voidFlag}
  -- end
  -- isNotEmpty dictLabel
    and dictLabel LIKE #{Like:dictLabel}
  -- end
-- end
-- isEmpty orderBy
order by sortNum desc
-- else
order by #{orderBy}
-- end
;

-- [addDictData]
insert into $table$
(id, idType, dictLabel,
dictValue, sortNum, cssClass,
listClass, voidFlag, remark,
createBy, createTime, updateBy,
updateTime)
values (
#{id}, #{idtype}, #{dictlabel},
#{dictvalue}, #{sortnum}, #{cssclass},
#{listclass}, #{voidflag}, #{remark},
#{createby}, #{createtime}, #{updateby},
#{updatetime}
);

-- [updateDictData]
update $table$ set
dictLabel = #{dictlabel},
dictValue = #{dictvalue},
sortNum = #{sortnum},
cssClass = #{cssclass},
listClass = #{listclass},
remark = #{remark},
updateBy = #{updateby},
updateTime = #{updatetime}
where id = #{id};

-- [voidDictData]
update $table$ set
voidFlag = #{voidflag},
updateBy = #{updateby},
updateTime = #{updatetime}
where id = #{id};

-- [selectAllNoticeMessage]
select * from $table$;

-- [addNoticeMessage]
insert into $table$ (
id, messageCode, messageName, messageKind,
voidFlag, createBy, createTime,
createTimeLong, updateBy, updateTime,
updateTimeLong )
values (
#{id}, #{messagecode}, #{messagename}, #{messagekind},
#{voidflag}, #{createby}, #{createtime},
#{createtimelong}, #{updateby}, #{updatetime},
#{updatetimelong} );

-- [updateNoticeMessage]
update $table$ set
messageName = #{messagename},
messageKind = #{messagekind},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

-- [voidNoticeMessage]
update $table$ set
voidFlag = #{voidflag},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

-- [selectUserNoticeMessage]
SELECT a.*, b.messageCode FROM $table1$ a
LEFT JOIN $table2$ b ON a.idNoticeMessage = b.id;

-- [addUserNoticeMessage]
insert into $table$ (
id, idUser,
idNoticeMessage, createBy, createTime,
createTimeLong, updateBy, updateTime,
updateTimeLong )
values (
#{id}, #{iduser},
#{idnoticemessage}, #{createby}, #{createtime},
#{createtimelong}, #{updateby}, #{updatetime},
#{updatetimelong} );

-- [deleteNoticeMessage]
delete from $table$ where idUser = #{iduser} and idNoticeMessage = #{idnoticemessage};

-- [queryPersonConfigValue]
select * from $table$;

-- [addPersonConfigValue]
insert into $table$ (
idUser, configKey, configName,
configValue1, configValue2, configValue3,
createBy, createTime, createTimeLong,
updateBy, updateTime, updateTimeLong )
values (
#{iduser}, #{configkey}, #{configname},
#{configvalue1}, #{configvalue2}, #{configvalue3},
#{createby}, #{createtime}, #{createtimelong},
#{updateby}, #{updatetime}, #{updatetimelong} );

-- [updatePersonConfigValue]
update $table$ set
configValue1 = #{configvalue1},
configValue2 = #{configvalue2},
configValue3 = #{configvalue3},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where idUser = #{iduser} and configKey = #{configkey};

-- [selectDataPurview]
select * from $table$ order by idData, idUser, idCreateUser;

-- [addDataPurview]
insert into $table$ (
idUser, userName, idCreateUser,
createUserName, idData )
values (
#{iduser}, #{username}, #{idcreateuser},
#{createusername}, #{iddata} );

-- [deleteDataPurview]
delete from $table$
where idData = #{iddata} and idUser = #{iduser} and idCreateUser = #{idcreateuser};

-- [insertTaskQueue]
insert into $table$
(userId, createTime, taskName,
queryBeginDate, queryEndDate, queryContent,
errorMsg, fileName, fileURL,
webFileURL)
values (
#{userid}, #{createtime}, #{taskname},
#{querybegindate}, #{queryenddate}, #{querycontent},
#{errormsg}, #{filename}, #{fileurl},
#{webfileurl}
);

-- [selectTaskQueue]
select * from $table$
where userId = #{userId}
-- isNotEmpty beginTime
and createTime >= #{beginTime}
-- end
-- isNotEmpty endTime
and createTime <= #{endTime}
-- end
order by createTime desc;


-- [insertPageTableColumn]
insert into $table$ (
idUser, pageName, columnName,
columnTitle, columnWidth, columnIndex)
values (
#{iduser}, #{pagename}, #{columnname},
#{columntitle}, #{columnwidth}, #{columnindex});

-- [deletePageTableColumn]
delete from $table$
where idUser = #{idUser} and pageName = #{pageName};

-- [selectPageTableColumn]
select * from $table$
where idUser = #{idUser} and pageName = #{pageName}
order by columnindex;

-- [insertAnnouncement]
insert into $table$ (id, idAnnouncementType, announcementType, announcementTitle,
announcementState, voidFlag, idReleaseMan,
releaseMan, releaseDate,
createMan, createBy,
createTime, updateBy,
updateTime ) values ( #{id}, #{idannouncementtype}, #{announcementtype}, #{announcementtitle},
#{announcementstate}, #{voidflag}, #{idreleaseman},
#{releaseman}, #{releasedate},
#{createman}, #{createby},
#{createtime}, #{updateby},
#{updatetime} );

-- [updateAnnouncement]
update $table$ set idAnnouncementType = #{idannouncementtype},announcementType = #{announcementtype},announcementTitle = #{announcementtitle},announcementState = #{announcementstate},
voidFlag = #{voidflag},idReleaseMan = #{idreleaseman},releaseMan = #{releaseman},releaseDate = #{releasedate},
createMan = #{createman},createBy = #{createby},
createTime = #{createtime},updateBy = #{updateby},updateTime = #{updatetime} where id = #{id};

-- [voidAnnouncement]
update $table$ set
voidFlag = #{voidflag},
updateBy = #{updateby},
updateTime = #{updatetime}
where id = #{id};

-- [insertAnnouncementAttachment]
insert into $table$ (id, idAnnouncement, idUser, userName,
originName, srcFileName, fileDescription,
attachmentUrl, downloadUrl, sortNum,
createBy, createTime,
updateBy, updateTime ) values ( #{id}, #{idannouncement}, #{iduser}, #{username},
#{originname}, #{srcfilename}, #{filedescription},
#{attachmenturl}, #{downloadurl}, #{sortnum},
#{createby}, #{createtime},
#{updateby}, #{updatetime} );

-- [deleteAnnouncementAttachment]
delete from $table$ where id = #{id};

-- [selectSiteContent]
select * from $table$
order by sortIndex;

-- [addSiteContent]
insert into $table$ (
id, groupName, caption, contentUrl,
sortIndex, siteLabel,
createBy, createTime,
createTimeLong, updateBy, updateTime,
updateTimeLong )
values (
#{id}, #{groupname}, #{caption}, #{contenturl},
#{sortindex}, #{sitelabel},
#{createby}, #{createtime},
#{createtimelong}, #{updateby}, #{updatetime},
#{updatetimelong} );

-- [updateSiteContent]
update $table$ set
groupName = #{groupname},
siteLabel = #{sitelabel},
caption = #{caption},
contentUrl = #{contenturl},
sortIndex = #{sortindex},
updateBy = #{updateby},
updateTime = #{updatetime},
updateTimeLong = #{updatetimelong}
where id = #{id};

-- [deleteSiteContent]
delete from $table$ where id = #{id};