1、栏位显示图片：插入-图片：图片地址：/images/logobd.jpg
2、占用一格的代码：插入-代码：
<style>
textarea {
width:98%!important;
}
</style> 3、字段联动设置
4、增加JS示例：
<script type="text/javascript">
jQuery(document).ready(function(){
var submitFun = window.doSubmit;
window.doSubmit = function(obj) {
setvalue();
submitFun(obj);
};
});
function setvalue(){
var djhj=jQuery("#field7162").val();//单价合计
var zjhj=jQuery("#field7161").val();//总价合计
if(djhj<300 && zjhj<2000){
jQuery("#field8365").val("0");//
}else{
jQuery("#field8365").val("1");//
}
}
</script>
有时，标准功能“显示属性联动”中的设置字段是否必填，并不能达到需求效果。这时就需要借助JS脚本了。
输入框和浏览框：
/**
* 设置字段必填/选填
* @param {boolean} necessary true-必填;false-选填
* @param {string} fieldId 字段id，如field12121
* @param {string} spanImgId 界面“！”id，通常为field12121span，浏览框为field12121spanimg
*/
function setNecessityOfField(necessary,fieldId, spanImgId){
var viewtype=necessary?"1":"0";
$('#' + fieldId).attr('viewtype', viewtype);
var oldFieldStr = $('input[name=needcheck]').val();
if(necessary){
newFieldStr=oldFieldStr.indexOf(fieldId)!=-1?oldFieldStr:oldFieldStr +',' +fieldId;
}else{
newFieldStr=oldFieldStr.replace(','+fieldId, '');
}
$('input[name=needcheck]').val(newFieldStr);
if(necessary&&$('#' + fieldId).val()!=""){
return;
}
var html=necessary?'<img src="/images/BacoError_wev8.gif" align="absMiddle">':'';
$('#' + spanImgId).html(html);
}
附件字段：
/**
* 设置附件字段必填/选填
* @param {boolean} neccessary true-必填;false-选填
* @param {string} fieldId 字段id，如field12121
* @param {string} spanId 界面“（必填）”id，附件字段为field_12121span
*/
function setNecessityOfEnclosureField(neccessary, fieldId) {
var viewtype = neccessary ? "1" : "0";
$('#' + fieldId).attr('viewtype', viewtype);
var oldFieldStr = $('input[name=needcheck]').val();
if (neccessary) {
newFieldStr = oldFieldStr.indexOf(fieldId) != -1 ? oldFieldStr : oldFieldStr + ',' + fieldId;
} else {
newFieldStr = oldFieldStr.replace(',' + fieldId, '');
}
$('input[name=needcheck]').val(newFieldStr);
if (neccessary && $('#' + fieldId).val() != "") {
return;
}
var spanId = fieldId.replace("field", "field_") + "span";
var html = neccessary ? "（必填）" : "";
$('#' + spanId).html(html);
}
5、触发器写法：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hefeiyu
-- Create date: 2019-11-22
-- Description: HR触发器同步给OA
-- =============================================
CREATE TRIGGER JobClass_To_OA
ON [dbo].[T_HR_JobClass]
AFTER INSERT,UPDATE
AS
BEGIN
DECLARE @OldName NVARCHAR(150)
DECLARE @NewName NVARCHAR(150)
DECLARE @Id INT
DECLARE @IsHave INT
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--判断该触发操作是Insert 还是Delete ,获取Name,注意修改动作是inset delete同时进行的
SELECT @OldName = JobClassName FROM Deleted
SELECT @NewName = JobClassName FROM Inserted
--根据得到的Name 获取OA中是否存在同样数据
--SELECT @IsHave = COUNT(0) FROM [192.168.0.19].[ecology].[dbo].[HrmJobGroups] WHERE [jobgroupname] = @OldName
SELECT @Id = ID FROM [192.168.0.19].[ecology].[dbo].[HrmJobGroups] WHERE [jobgroupname] = @OldName
IF (@OldName IS NOT NULL and @Id IS NOT NULL)
BEGIN
UPDATE [192.168.0.19].[ecology].[dbo].[HrmJobGroups]
SET [jobgroupname] = @OldName
WHERE [ID] = @Id
END
ELSE
BEGIN
INSERT INTO [192.168.0.19].[ecology].[dbo].[HrmJobGroups]
([jobgroupname],[jobgroupremark])
VALUES (@NewName,@NewName)
END
END
GO
6、OA更新人员归属地
--select rygd from formtable_main_73
update formtable_main_104 set rygsd=(select locationid from HrmResource where id=shenqr)
where shenqr in(select id from HrmResource) and rygsd is null
--select * from HrmResource
select * from formtable_main_74 where danh='FI04-2019100003'
select * from formtable_main_75 where danh='FI05-2019100004'
update formtable_main_74 set fygsd=(select locationid from HrmResource where id=shenqr)
where danh='FI04-2019100003'7、OA更新报销单据没有合计金额
select * from formtable_main_64 where danh='FI01-2019080024'
select * from formtable_main_64_dt1 where mainid=(select id from formtable_main_64 where danh='FI01-2019080024')
--差旅费用报销
update formtable_main_64_dt1 set bxxj=ISNULL(jpf,0)+ISNULL(hcf,0)+ISNULL(ctqcf,0)+ISNULL(cf,0)+ISNULL(snjt,0)+ISNULL(zsf,0)+ISNULL(ccbz,0)+ISNULL(zdf,0)+ISNULL(qt,0)+ISNULL(cyf,0)
where mainid=(select id from formtable_main_64 where danh='FI01-2019090095')
update formtable_main_64 set
bxje=(select ISNULL(jpf,0)+ISNULL(hcf,0)+ISNULL(ctqcf,0)+ISNULL(cf,0)+ISNULL(snjt,0)+ISNULL(zsf,0)+ISNULL(ccbz,0)+ISNULL(zdf,0)+ISNULL(qt,0)+ISNULL(cyf,0)
from formtable_main_64_dt1 where mainid=(select id from formtable_main_64 where danh='FI01-2019090095')),
jedx=(select ISNULL(jpf,0)+ISNULL(hcf,0)+ISNULL(ctqcf,0)+ISNULL(cf,0)+ISNULL(snjt,0)+ISNULL(zsf,0)+ISNULL(ccbz,0)+ISNULL(zdf,0)+ISNULL(qt,0)+ISNULL(cyf,0)
from formtable_main_64_dt1 where mainid=(select id from formtable_main_64 where danh='FI01-2019090095'))
where danh='FI01-2019090095'
--select ISNULL(jpf,0)+ISNULL(hcf,0)+ISNULL(ctqcf,0)+ISNULL(cf,0)+ISNULL(snjt,0)+ISNULL(zsf,0)+ISNULL(ccbz,0)+ISNULL(zdf,0)+ISNULL(qt,0)
--from formtable_main_64_dt1 where mainid=(select id from formtable_main_64 where danh='FI01-2019080024')
select * from formtable_main_64_dt1 where mainid=(select id from formtable_main_64 where danh='FI01-2019090095')8、OA改单据各种日期
select * from formtable_main_59 where danh='FI09-2019080009'
update formtable_main_91 set shenqrq='2019-04-08' where danh='QC03-2019080126'
select * from workflow_requestlog where requestid=52026
update workflow_requestlog set operatedate='2019-04-08' where requestid=49233
select * from workflow_requestbase where requestid=52026
update workflow_requestbase set requestname='受控文件会签-张琴-2019-04-08',createdate='2019-04-08',
lastoperatedate='2019-04-08',requestnamenew='受控文件会签-张琴-2019-04-08' where requestid=49233
select * from workflow_requestlog where requestid=52026 and nodeid=1473
update workflow_requestlog set remark='同意<br/>' where requestid=52026 and nodeid=14739、OA抛来数据被删除后重新生成语句
INSERT INTO npp_file(
nppsys,npp00,npp01,npp011,npp02,
npp03,npp04,npp05,npp06,npp07,
nppglno,npptype,npplegal,npp08
)
SELECT 'OA' nppsys,'2' npp00,tc_apa01 npp01,1 npp011,tc_apa02 npp02,
NULL npp03,NULL npp04,NULL npp05,tc_apa100 npp06,'00' npp07,
NULL nppglno,'0' npptype,tc_apa100 npplegal,NULL npp08
FROM tc_apa_file
WHERE tc_apa00 ='OA'
AND tc_apa01 = 'FI01-2019040084'
--#单身npq_file:1-借方:费用科目1
INSERT INTO npq_file(
npqsys,npq00,npq01,npq011,npq02,
npq03,npq04,npq05,npq06,npq07f,
npq07,npq08,npq11,npq12,npq13,
npq14,npq15,npq21,npq22,npq23,
npq24,npq25,npq26,npq27,npq28,
npq29,npq30,npq31,npq32,npq33,
npq34,npq35,npq36,npq37,npq930,
npqtype,npqlegal
)
SELECT 'OA' npqsys,'2' npq00,tc_apa01 npq01,1 npq011,1 npq02,
tc_apa51 npq03,tc_apa25 npq04,CASE WHEN x.aag05='Y' THEN tc_apa22 ELSE NULL END npq05,'1' npq06,tc_apa31 npq07f,
tc_apa31 npq07,NULL npq08,CASE WHEN TRIM(x.aag15) IS NULL THEN NULL ELSE gen02 END npq11,NULL npq12,NULL npq13,
NULL npq14,NULL npq15,NULL npq21,NULL npq22,tc_apaud01 npq23,
tc_apa13 npq24,tc_apa14 npq25,NULL npq26,NULL npq27,NULL npq28,
NULL npq29,tc_apa100 npq30,NULL npq31,NULL npq32,NULL npq33,
NULL npq34,NULL npq35,null npq36,NULL npq37,NULL npq930,'0' npqtype,tc_apalegal npqlegal
FROM tc_apa_file,gen_file,(SELECT aag01,aag05,aag15 FROM aag_file WHERE aag00='00') x
WHERE tc_apa00 ='OA'
AND tc_apa01 = 'FI01-2019040084'
AND tc_apa06=gen01(+) AND tc_apa51=x.aag01(+)
-- #单身npq_file:1-借方:增值税2
--IF g_tc_apa.tc_apa32>0 THEN
INSERT INTO npq_file(
npqsys,npq00,npq01,npq011,npq02,
npq03,npq04,npq05,npq06,npq07f,
npq07,npq08,npq11,npq12,npq13,
npq14,npq15,npq21,npq22,npq23,
npq24,npq25,npq26,npq27,npq28,
npq29,npq30,npq31,npq32,npq33,
npq34,npq35,npq36,npq37,npq930,
npqtype,npqlegal
)
SELECT 'OA' npqsys,'2' npq00,tc_apa01 npq01,1 npq011,2 npq02,
tc_apa52 npq03,tc_apa25 npq04,CASE WHEN x.aag05='Y' THEN tc_apa22 ELSE NULL END npq05,'1' npq06,tc_apa32 npq07f,
tc_apa32 npq07,NULL npq08,CASE WHEN TRIM(x.aag15) IS NULL THEN NULL ELSE gen02 END npq11,NULL npq12,NULL npq13,
NULL npq14,NULL npq15,NULL npq21,NULL npq22,tc_apaud01 npq23,
tc_apa13 npq24,tc_apa14 npq25,NULL npq26,NULL npq27,NULL npq28,
NULL npq29,tc_apa100 npq30,NULL npq31,NULL npq32,NULL npq33,
NULL npq34,NULL npq35,null npq36,NULL npq37,NULL npq930,'0' npqtype,tc_apalegal npqlegal
FROM tc_apa_file,gen_file,(SELECT aag01,aag05,aag15 FROM aag_file WHERE aag00='00') x
WHERE tc_apa00 ='OA'
AND tc_apa01 = 'FI01-2019040084'
AND tc_apa06=gen01(+) AND tc_apa52=x.aag01(+) and tc_apa32>0
-- #单身npq_file:2-贷:银行存款
INSERT INTO npq_file(
npqsys,npq00,npq01,npq011,npq02,
npq03,npq04,npq05,npq06,npq07f,
npq07,npq08,npq11,npq12,npq13,
npq14,npq15,npq21,npq22,npq23,
npq24,npq25,npq26,npq27,npq28,
npq29,npq30,npq31,npq32,npq33,
npq34,npq35,npq36,npq37,npq930,
npqtype,npqlegal
)
SELECT 'OA' npqsys,'2' npq00,tc_apa01 npq01,1 npq011,3 npq02,
tc_apa101 npq03,tc_apa25 npq04,CASE WHEN x.aag05='Y' THEN tc_apa22 ELSE NULL END npq05,'2' npq06,tc_apa34 npq07f,
tc_apa34 npq07,NULL npq08,CASE WHEN TRIM(x.aag15) IS NULL THEN NULL ELSE gen02 END npq11,NULL npq12,NULL npq13,
NULL npq14,NULL npq15,NULL npq21,NULL npq22,tc_apaud01 npq23,
tc_apa13 npq24,tc_apa14 npq25,NULL npq26,NULL npq27,NULL npq28,
NULL npq29,tc_apa100 npq30,NULL npq31,NULL npq32,NULL npq33,
NULL npq34,NULL npq35,null npq36,NULL npq37,NULL npq930,'0' npqtype,tc_apalegal npqlegal
FROM tc_apa_file,gen_file,(SELECT aag01,aag05,aag15 FROM aag_file WHERE aag00='00') x
WHERE tc_apa00 ='OA'
AND tc_apa01 = 'FI01-2019040084'
AND tc_apa06=gen01(+) AND tc_apa101=x.aag01(+)
10、OA修改评论
select * from workflow_requestlog where requestid=46432
update workflow_requestlog set remark='删除了方太的费用' where requestid=46432 and operatetime='12:21:44'
update workflow_requestlog set remark='奥克斯也删除' where requestid=46432 and operatetime='11:53:39'11、OA修改字段类型
select * from workflow_billfield where billid=-188 and fieldlabel=-11951
select * from htmllabelindex where indexdesc='电阻剪脚-52E电阻(齐脚)'
-11951 电阻剪脚-52E电阻(齐脚)
select * from workflow_bill where tablename='formtable_main_188'
select * from workflow_browserurl where labelid=-11951
select * from formtable_main_188
select * from where billid=13630
--改第一步
alter table formtable_main_188 alter column d1 int;
alter table formtable_main_188 alter column d2 int;
alter table formtable_main_188 alter column d3 int;
--改第二步
update workflow_billfield set fielddbtype='int',fieldhtmltype=1,type=2 where billid=-188 and fieldname='d1';
update workflow_billfield set fielddbtype='int',fieldhtmltype=1,type=2 where billid=-188 and fieldname='d2';
update workflow_billfield set fielddbtype='int',fieldhtmltype=1,type=2 where billid=-188 and fieldname='d3';12、OA员工借款异常处理
INSERT INTO dgapr.tc_apa_file
SELECT * FROM tc_apa_file WHERE tc_apa01 IN(
SELECT tc_apa01 FROM tc_apa_file WHERE tc_apa01 LIKE 'FI05-%'
AND tc_apa01 NOT IN(SELECT tc_apa01 FROM dgapr.tc_apa_file)
AND tc_apa01 NOT IN('FI05-2018120002')
)
SELECT * FROM tc_apb_file WHERE tc_apb01 LIKE 'FI05-2018120002%'
SELECT * FROM amp.npp_file WHERE npp01 LIKE 'FI05-2018120002%'
SELECT * FROM npq_file WHERE npq01 LIKE 'FI05-2018120002%'
INSERT INTO dgapr.npp_file
SELECT * FROM npp_file WHERE npp01 IN(
SELECT npp01 FROM tc_apa_file WHERE npp01 LIKE 'FI05-%' AND npp01 NOT IN(SELECT npp01 FROM dgapr.npp_file)
AND npp01 NOT IN('FI05-2018120002')
)
INSERT INTO dgapr.npq_file
SELECT * FROM npq_file WHERE npq01 IN(
SELECT npq01 FROM npq_file WHERE npq01 LIKE 'FI05-%'
AND npq01 NOT IN(SELECT npq01 FROM dgapr.npq_file)
AND npq01 NOT IN('FI05-2018120002')
)
转载请注明：赫非域 » hfy泛微语句常用记录