强烈推荐篇：HR组织架构管理 / 组织架构管理（2）

--建表脚本--

create table T_ORG_ORGCHARTSCHEM(

FCHARTID int not null  comment '架构图方案内码'

,FNUMBER nvarchar(30) default null  comment '架构图方案编码'

,FSCHEMEID int default null  comment '组织架构方案内码'

,FUNITID int default null  comment '顶层组织单元'

,FGENERMODE char(1) default null  comment '生成方式'

,FSHOWMODE char(1) default null  comment '显示模式'

,FSHOWDEPTH int default null  comment '显示层级'

,FISCUSTOPUNIT char(1) default ''0''  comment '是否自定义顶层组织'

,FISDATACTRL char(1) default ''0''  comment '是否控制数据权限'

,FISSHOWUNITNO char(1) default ''1''  comment '是否显示组织单元编号'

,FISSHOWUNITNAME char(1) default ''1''  comment '是否显示组织单元名称'

,FISSHOWPHOTO char(1) default ''0''  comment '是否显示负责人相片'

,FISSHOWLEADERNAME char(1) default ''0''  comment '是否显示负责人姓名'

,FISSHOWLEADERPOST char(1) default ''0''  comment '是否显示负责人职位'

,FISSHOWLEADERNO char(1) default ''0''  comment '是否显示负责人编号'

,FCREATEORGID int default 0  comment '创建组织内码'

,FUSEORGID int default 0  comment '使用组织内码'

,FCREATORID int default null  comment '创建人内码'

,FCREATEDATE datetime default null  comment '创建日期'

,FMODIFIERID int default null  comment '修改人内码'

,FMODIFYDATE datetime default null  comment '修改日期'

,FFORBIDDERID int default null  comment '禁用人内码'

,FFORBIDDATE datetime default null  comment '禁用日期'

,FFORBIDSTATUS char(1) default null  comment '禁用状态'

,FDOCUMENTSTATUS char(1) default null  comment '数据状态'

,FAUDITORID int default null  comment '审核人'

,FAUDITDATE datetime default null  comment '审核日期'

,FORGCHART xml default null  comment '架构图'

,primary key (FCHARTID)

) comment = 'hr组织架构图方案'

--查询--

select FCHARTID as "fchartid",FNUMBER as "fnumber",FSCHEMEID as "fschemeid",FUNITID as "funitid",FGENERMODE as "fgenermode",FSHOWMODE as "fshowmode",FSHOWDEPTH as "fshowdepth",FISCUSTOPUNIT as "fiscustopunit",FISDATACTRL as "fisdatactrl",FISSHOWUNITNO as "fisshowunitno",FISSHOWUNITNAME as "fisshowunitname",FISSHOWPHOTO as "fisshowphoto",FISSHOWLEADERNAME as "fisshowleadername",FISSHOWLEADERPOST as "fisshowleaderpost",FISSHOWLEADERNO as "fisshowleaderno",FCREATEORGID as "fcreateorgid",FUSEORGID as "fuseorgid",FCREATORID as "fcreatorid",FCREATEDATE as "fcreatedate",FMODIFIERID as "fmodifierid",FMODIFYDATE as "fmodifydate",FFORBIDDERID as "fforbidderid",FFORBIDDATE as "fforbiddate",FFORBIDSTATUS as "fforbidstatus",FDOCUMENTSTATUS as "fdocumentstatus",FAUDITORID as "fauditorid",FAUDITDATE as "fauditdate",FORGCHART as "forgchart" from T_ORG_ORGCHARTSCHEM

--查询(中文字段)--

select FCHARTID as "架构图方案内码",FNUMBER as "架构图方案编码",FSCHEMEID as "组织架构方案内码",FUNITID as "顶层组织单元",FGENERMODE as "生成方式",FSHOWMODE as "显示模式",FSHOWDEPTH as "显示层级",FISCUSTOPUNIT as "是否自定义顶层组织",FISDATACTRL as "是否控制数据权限",FISSHOWUNITNO as "是否显示组织单元编号",FISSHOWUNITNAME as "是否显示组织单元名称",FISSHOWPHOTO as "是否显示负责人相片",FISSHOWLEADERNAME as "是否显示负责人姓名",FISSHOWLEADERPOST as "是否显示负责人职位",FISSHOWLEADERNO as "是否显示负责人编号",FCREATEORGID as "创建组织内码",FUSEORGID as "使用组织内码",FCREATORID as "创建人内码",FCREATEDATE as "创建日期",FMODIFIERID as "修改人内码",FMODIFYDATE as "修改日期",FFORBIDDERID as "禁用人内码",FFORBIDDATE as "禁用日期",FFORBIDSTATUS as "禁用状态",FDOCUMENTSTATUS as "数据状态",FAUDITORID as "审核人",FAUDITDATE as "审核日期",FORGCHART as "架构图" from T_ORG_ORGCHARTSCHEM

--INSERT脚本--

insert into T_ORG_ORGCHARTSCHEM(FCHARTID,FNUMBER,FSCHEMEID,FUNITID,FGENERMODE,FSHOWMODE,FSHOWDEPTH,FISCUSTOPUNIT,FISDATACTRL,FISSHOWUNITNO,FISSHOWUNITNAME,FISSHOWPHOTO,FISSHOWLEADERNAME,FISSHOWLEADERPOST,FISSHOWLEADERNO,FCREATEORGID,FUSEORGID,FCREATORID,FCREATEDATE,FMODIFIERID,FMODIFYDATE,FFORBIDDERID,FFORBIDDATE,FFORBIDSTATUS,FDOCUMENTSTATUS,FAUDITORID,FAUDITDATE,FORGCHART) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

--UPDATE脚本--

update T_ORG_ORGCHARTSCHEM set FCHARTID=?,FNUMBER=?,FSCHEMEID=?,FUNITID=?,FGENERMODE=?,FSHOWMODE=?,FSHOWDEPTH=?,FISCUSTOPUNIT=?,FISDATACTRL=?,FISSHOWUNITNO=?,FISSHOWUNITNAME=?,FISSHOWPHOTO=?,FISSHOWLEADERNAME=?,FISSHOWLEADERPOST=?,FISSHOWLEADERNO=?,FCREATEORGID=?,FUSEORGID=?,FCREATORID=?,FCREATEDATE=?,FMODIFIERID=?,FMODIFYDATE=?,FFORBIDDERID=?,FFORBIDDATE=?,FFORBIDSTATUS=?,FDOCUMENTSTATUS=?,FAUDITORID=?,FAUDITDATE=?,FORGCHART=? where FCHARTID=?

--delete脚本--

delete from T_ORG_ORGCHARTSCHEM where FCHARTID=?

--给字段加备注--

alter table T_ORG_ORGCHARTSCHEM comment 'hr组织架构图方案';

alter table T_ORG_ORGCHARTSCHEM modify column FCHARTID int not null  comment '架构图方案内码';

alter table T_ORG_ORGCHARTSCHEM modify column FNUMBER nvarchar(30) default null  comment '架构图方案编码';

alter table T_ORG_ORGCHARTSCHEM modify column FSCHEMEID int default null  comment '组织架构方案内码';

alter table T_ORG_ORGCHARTSCHEM modify column FUNITID int default null  comment '顶层组织单元';

alter table T_ORG_ORGCHARTSCHEM modify column FGENERMODE char(1) default null  comment '生成方式';

alter table T_ORG_ORGCHARTSCHEM modify column FSHOWMODE char(1) default null  comment '显示模式';

alter table T_ORG_ORGCHARTSCHEM modify column FSHOWDEPTH int default null  comment '显示层级';

alter table T_ORG_ORGCHARTSCHEM modify column FISCUSTOPUNIT char(1) default ''0''  comment '是否自定义顶层组织';

alter table T_ORG_ORGCHARTSCHEM modify column FISDATACTRL char(1) default ''0''  comment '是否控制数据权限';

alter table T_ORG_ORGCHARTSCHEM modify column FISSHOWUNITNO char(1) default ''1''  comment '是否显示组织单元编号';

alter table T_ORG_ORGCHARTSCHEM modify column FISSHOWUNITNAME char(1) default ''1''  comment '是否显示组织单元名称';

alter table T_ORG_ORGCHARTSCHEM modify column FISSHOWPHOTO char(1) default ''0''  comment '是否显示负责人相片';

alter table T_ORG_ORGCHARTSCHEM modify column FISSHOWLEADERNAME char(1) default ''0''  comment '是否显示负责人姓名';

alter table T_ORG_ORGCHARTSCHEM modify column FISSHOWLEADERPOST char(1) default ''0''  comment '是否显示负责人职位';

alter table T_ORG_ORGCHARTSCHEM modify column FISSHOWLEADERNO char(1) default ''0''  comment '是否显示负责人编号';

alter table T_ORG_ORGCHARTSCHEM modify column FCREATEORGID int default 0  comment '创建组织内码';

alter table T_ORG_ORGCHARTSCHEM modify column FUSEORGID int default 0  comment '使用组织内码';

alter table T_ORG_ORGCHARTSCHEM modify column FCREATORID int default null  comment '创建人内码';

alter table T_ORG_ORGCHARTSCHEM modify column FCREATEDATE datetime default null  comment '创建日期';

alter table T_ORG_ORGCHARTSCHEM modify column FMODIFIERID int default null  comment '修改人内码';

alter table T_ORG_ORGCHARTSCHEM modify column FMODIFYDATE datetime default null  comment '修改日期';

alter table T_ORG_ORGCHARTSCHEM modify column FFORBIDDERID int default null  comment '禁用人内码';

alter table T_ORG_ORGCHARTSCHEM modify column FFORBIDDATE datetime default null  comment '禁用日期';

alter table T_ORG_ORGCHARTSCHEM modify column FFORBIDSTATUS char(1) default null  comment '禁用状态';

alter table T_ORG_ORGCHARTSCHEM modify column FDOCUMENTSTATUS char(1) default null  comment '数据状态';

alter table T_ORG_ORGCHARTSCHEM modify column FAUDITORID int default null  comment '审核人';

alter table T_ORG_ORGCHARTSCHEM modify column FAUDITDATE datetime default null  comment '审核日期';

alter table T_ORG_ORGCHARTSCHEM modify column FORGCHART xml default null  comment '架构图';

--建表脚本--

create table T_ORG_ORGCHARTSCHEM_L(

FPKID int not null  comment '多语言内码'

,FCHARTID int default null  comment '架构图方案内码'

,FLOCALEID int default null  comment '语言码'

,FNAME nvarchar(80) default null  comment '名称'

,FDESCRIPTION nvarchar(255) default null  comment '描述'

,primary key (FPKID)

) comment = 'hr组织架构图方案多语言'

--查询--

select FPKID as "fpkid",FCHARTID as "fchartid",FLOCALEID as "flocaleid",FNAME as "fname",FDESCRIPTION as "fdescription" from T_ORG_ORGCHARTSCHEM_L

--查询(中文字段)--

select FPKID as "多语言内码",FCHARTID as "架构图方案内码",FLOCALEID as "语言码",FNAME as "名称",FDESCRIPTION as "描述" from T_ORG_ORGCHARTSCHEM_L

--INSERT脚本--

insert into T_ORG_ORGCHARTSCHEM_L(FPKID,FCHARTID,FLOCALEID,FNAME,FDESCRIPTION) values (?,?,?,?,?)

--UPDATE脚本--

update T_ORG_ORGCHARTSCHEM_L set FPKID=?,FCHARTID=?,FLOCALEID=?,FNAME=?,FDESCRIPTION=? where FPKID=?

--delete脚本--

delete from T_ORG_ORGCHARTSCHEM_L where FPKID=?

--给字段加备注--

alter table T_ORG_ORGCHARTSCHEM_L comment 'hr组织架构图方案多语言';

alter table T_ORG_ORGCHARTSCHEM_L modify column FPKID int not null  comment '多语言内码';

alter table T_ORG_ORGCHARTSCHEM_L modify column FCHARTID int default null  comment '架构图方案内码';

alter table T_ORG_ORGCHARTSCHEM_L modify column FLOCALEID int default null  comment '语言码';

alter table T_ORG_ORGCHARTSCHEM_L modify column FNAME nvarchar(80) default null  comment '名称';

alter table T_ORG_ORGCHARTSCHEM_L modify column FDESCRIPTION nvarchar(255) default null  comment '描述';

--建表脚本--

create table T_ORG_POST(

FPOSTID int not null  comment '岗位信息内码'

,FNUMBER nvarchar(80) default null  comment '岗位信息编码'

,FMASTERID int default null  comment '唯一标识字段名'

,FISHRPOST char(1) not null  comment '是否hr岗位'

,FDEPTID int default 0  comment '所属部门'

,FHELPCODE nvarchar(255) default null  comment '助记码'

,FEFFECTDATE datetime default null  comment '生效时间'

,FLAPSEDATE datetime default null  comment '失效时间'

,FCREATEORGID int default null  comment '创建组织'

,FUSEORGID int default null  comment '使用组织'

,FCREATORID int default null  comment '创建人'

,FMODIFIERID int default null  comment '修改人'

,FCREATEDATE datetime default null  comment '创建时间'

,FMODIFYDATE datetime default null  comment '修改时间'

,FAUDITORID int default null  comment '审核人'

,FAUDITDATE datetime default null  comment '审核时间'

,FFORBIDDERID int default null  comment '禁用人'

,FFORBIDDATE datetime default null  comment '禁用时间'

,FDOCUMENTSTATUS char(1) default null  comment '数据状态'

,FFORBIDSTATUS char(1) default null  comment '禁用状态'

,FISSHR char(1) not null  comment '是否shr'

,primary key (FPOSTID)

) comment = '岗位信息基础表'

--查询--

select FPOSTID as "fpostid",FNUMBER as "fnumber",FMASTERID as "fmasterid",FISHRPOST as "fishrpost",FDEPTID as "fdeptid",FHELPCODE as "fhelpcode",FEFFECTDATE as "feffectdate",FLAPSEDATE as "flapsedate",FCREATEORGID as "fcreateorgid",FUSEORGID as "fuseorgid",FCREATORID as "fcreatorid",FMODIFIERID as "fmodifierid",FCREATEDATE as "fcreatedate",FMODIFYDATE as "fmodifydate",FAUDITORID as "fauditorid",FAUDITDATE as "fauditdate",FFORBIDDERID as "fforbidderid",FFORBIDDATE as "fforbiddate",FDOCUMENTSTATUS as "fdocumentstatus",FFORBIDSTATUS as "fforbidstatus",FISSHR as "fisshr" from T_ORG_POST

--查询(中文字段)--

select FPOSTID as "岗位信息内码",FNUMBER as "岗位信息编码",FMASTERID as "唯一标识字段名",FISHRPOST as "是否hr岗位",FDEPTID as "所属部门",FHELPCODE as "助记码",FEFFECTDATE as "生效时间",FLAPSEDATE as "失效时间",FCREATEORGID as "创建组织",FUSEORGID as "使用组织",FCREATORID as "创建人",FMODIFIERID as "修改人",FCREATEDATE as "创建时间",FMODIFYDATE as "修改时间",FAUDITORID as "审核人",FAUDITDATE as "审核时间",FFORBIDDERID as "禁用人",FFORBIDDATE as "禁用时间",FDOCUMENTSTATUS as "数据状态",FFORBIDSTATUS as "禁用状态",FISSHR as "是否shr" from T_ORG_POST

--INSERT脚本--

insert into T_ORG_POST(FPOSTID,FNUMBER,FMASTERID,FISHRPOST,FDEPTID,FHELPCODE,FEFFECTDATE,FLAPSEDATE,FCREATEORGID,FUSEO