一铃绒、SQL SERVICE的存儲(chǔ)過程
ALTER PROCEDURE Gene_Task_SearchData
AS
BEGIN
TRUNCATE TABLE Gene_Task_Search_Result_Tmp;
INSERT INTO Gene_Task_Search_Result_Tmp
SELECT l.id "流程實(shí)例編號(hào)",
l.LearingTilteC "案件名稱",
hr.NAME "申請(qǐng)人",
r.distribution_time "申請(qǐng)時(shí)間",
l.id "流程名稱",
hr.CORPNAME "公司名稱",
'' "status",
'' "formid",
'' "viewurl"
FROM [dbo].[LearingResult] r
inner join [dbo].[E-learing] l on r.LearingId = l.ID
inner join ckhr.dbo.IF_V_SP_CONTACTS hr on hr.CODE = r.Uid
inner join dbo.AD_User_Map um on um.userCode = hr.CODE
WHERE r.isdelete <> 1
AND (l.ID like (select '%'+processNumber+'%' from dbo.Gene_Task_Search_Para_Tmp p where p.processNumber is not null)
OR (select COUNT(*) from dbo.Gene_Task_Search_Para_Tmp p where p.processNumber is not null) = 0)
AND (l.LearingTilteC like (select '%'+casename+'%' from dbo.Gene_Task_Search_Para_Tmp p where p.casename is not null)
OR (select COUNT(*) from dbo.Gene_Task_Search_Para_Tmp p where p.casename is not null) = 0)
AND (CONVERT(varchar(100), r.distribution_time, 23) = (select applicationTime from dbo.Gene_Task_Search_Para_Tmp p where p.applicationTime is not null)
OR (select COUNT(*) from dbo.Gene_Task_Search_Para_Tmp p where p.applicationTime is not null) = 0)
AND (hr.CODE in (SELECT userCode FROM Gene_Task_Search_Para_Tmp WHERE userCode is not null))
;
SELECT * FROM Gene_Task_Search_Result_Tmp;
SELECT COUNT(*) CT FROM Gene_Task_Search_Result_Tmp;
END;
二、ORACLE的包
CREATE OR REPLACE PACKAGE oa_pkg AS
/*********************************************
This package for Ftit OA system
Author: xianyu.ying
Date: 2017-06-26
*********************************************/
/*********************************************
每天計(jì)算SVN狀態(tài)數(shù)據(jù)撼港，并填充到結(jié)果表
Author: xianyu.ying
Date: 2017-06-26
*********************************************/
PROCEDURE calc_svn_report_by_day;
END oa_pkg;
/
CREATE OR REPLACE PACKAGE BODY oa_pkg AS
/*********************************************
每天計(jì)算SVN狀態(tài)數(shù)據(jù)惫确，并填充到結(jié)果表
Author: xianyu.ying
Date: 2017-06-26
*********************************************/
PROCEDURE calc_svn_report_by_day IS
CURSOR rec_data IS
SELECT *
FROM (SELECT oa_pro.i_project_code 項(xiàng)目編號(hào),
oa_pro.i_project_name 項(xiàng)目名稱,
ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')) 開發(fā)時(shí)長(zhǎng),
oa_pro.user_sale 銷售負(fù)責(zé)人,
oa_pro.user_sale_code 銷售編號(hào),
user_pro 項(xiàng)目負(fù)責(zé)人,
oa_pro.user_charg_code 負(fù)責(zé)人編號(hào),
SUM(svn_rep.filenum) 總文件數(shù)量,
SUM(svn_rep.codeline) 總代碼數(shù)量,
COUNT(1) 總提交次數(shù),
decode((SELECT '是'
FROM t_project_developer
WHERE projectid = oa_pro.i_project_code
AND userid = oa_dev.i_user_code),
NULL,
'否',
'是') 項(xiàng)目預(yù)定成員,
decode(oa_dev.i_user_name,
NULL,
svn_rep.developer,
oa_dev.i_user_name) 開發(fā)成員名稱,
decode(oa_dev.i_user_code,
NULL,
svn_rep.developer,
oa_dev.i_user_code) 員工編號(hào),
decode(svn_day.filenum, NULL, 0, svn_day.filenum) 本日文件數(shù)量,
decode(svn_week.filenum, NULL, 0, svn_week.filenum) 本周文件數(shù)量,
decode(svn_month.filenum, NULL, 0, svn_month.filenum) 本月文件數(shù)量,
decode(svn_year.filenum, NULL, 0, svn_year.filenum) 本年文件數(shù)量,
decode(svn_day.codeline, NULL, 0, svn_day.codeline) 本日代碼行數(shù),
decode(svn_week.codeline, NULL, 0, svn_week.codeline) 本周代碼行數(shù),
decode(svn_month.codeline, NULL, 0, svn_month.codeline) 本月代碼行數(shù),
decode(svn_year.codeline, NULL, 0, svn_year.codeline) 本年代碼行數(shù),
decode(svn_day.commitcount, NULL, 0, svn_day.commitcount) 本日提交次數(shù),
decode(svn_week.commitcount, NULL, 0, svn_week.commitcount) 本周提交次數(shù),
decode(svn_month.commitcount, NULL, 0, svn_month.commitcount) 本月提交次數(shù),
decode(svn_year.commitcount, NULL, 0, svn_year.commitcount) 本年提交次數(shù),
'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
svn_rep.projectname || '/user_' || svn_rep.developer ||
'.html' 明細(xì)鏈接
FROM t_log_report svn_rep,
(SELECT u_d.i_user_code,
u_d.i_user_name,
m2.svn_developer svn_user_code
FROM t_svn_oa_developer_mapping m2, t_users_mst u_d
WHERE m2.oa_developer = u_d.i_user_code) oa_dev,
(SELECT t.*,
m.*,
t.i_user_sale user_sale_code,
t.i_user_charg user_charg_code,
u_sale.i_user_name user_sale,
u_pro.i_user_name user_pro
FROM t_project_mst t,
t_svn_oa_project_mapping m,
t_users_mst u_sale,
t_users_mst u_pro
WHERE t.i_project_code = m.oa_project
AND t.i_user_sale = u_sale.i_user_code(+)
AND t.i_user_charg = u_pro.i_user_code) oa_pro,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_char(logtime, 'yyyymmdd') =
to_char(SYSDATE - 1, 'yyyymmdd')
GROUP BY projectname, developer) svn_day,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE - 1, 'd') + 1 AND
trunc(SYSDATE - 1, 'd') + 1 + 6
GROUP BY projectname, developer) svn_week,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE, 'mm') AND last_day(SYSDATE)
GROUP BY projectname, developer) svn_month,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE, 'yy') AND
last_day(add_months(trunc(SYSDATE, 'y'), 11))
GROUP BY projectname, developer) svn_year
WHERE svn_rep.projectname = oa_pro.svn_project
AND svn_rep.developer = oa_dev.svn_user_code(+)
AND svn_rep.projectname = svn_day.projectname(+)
AND svn_rep.developer = svn_day.developer(+)
AND svn_rep.projectname = svn_week.projectname(+)
AND svn_rep.developer = svn_week.developer(+)
AND svn_rep.projectname = svn_month.projectname(+)
AND svn_rep.developer = svn_month.developer(+)
AND svn_rep.projectname = svn_year.projectname(+)
AND svn_rep.developer = svn_year.developer(+)
GROUP BY oa_pro.i_project_code,
oa_pro.i_project_name,
'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
svn_rep.projectname || '/user_' || svn_rep.developer ||
'.html',
ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')),
oa_pro.user_sale,
user_pro,
oa_pro.user_sale_code,
oa_pro.user_charg_code,
svn_rep.developer,
oa_dev.i_user_name,
oa_dev.i_user_code,
svn_day.filenum,
svn_week.filenum,
svn_month.filenum,
svn_year.filenum,
svn_day.codeline,
svn_week.codeline,
svn_month.codeline,
svn_year.codeline,
svn_day.commitcount,
svn_week.commitcount,
svn_month.commitcount,
svn_year.commitcount
UNION ALL
SELECT pd.projectid 項(xiàng)目編號(hào),
tpm.i_project_name 項(xiàng)目名稱,
ceil(SYSDATE - to_date(tpm.i_start_date, 'yyyymmdd')) 開發(fā)時(shí)長(zhǎng),
tumsale.i_user_name 銷售負(fù)責(zé)人,
tpm.i_user_sale 銷售編號(hào),
tumcharg.i_user_name 項(xiàng)目負(fù)責(zé)人,
tpm.i_user_charg 負(fù)責(zé)人編號(hào),
0 總文件數(shù)量,
0 總代碼數(shù)量,
0 總提交次數(shù),
'是' 項(xiàng)目預(yù)定成員,
tum.i_user_name 開發(fā)成員名稱,
pd.userid 員工編號(hào),
0 本日文件數(shù)量,
0 本周文件數(shù)量,
0 本月文件數(shù)量,
0 本年文件數(shù)量,
0 本日代碼行數(shù),
0 本周代碼行數(shù),
0 本月代碼行數(shù),
0 本年代碼行數(shù),
0 本日提交次數(shù),
0 本周提交次數(shù),
0 本月提交次數(shù),
0 本年提交次數(shù),
'' 明細(xì)鏈接
FROM t_project_developer pd,
t_svn_oa_developer_mapping dm,
t_users_mst tum,
t_svn_oa_project_mapping pm,
t_project_mst tpm,
t_users_mst tumsale,
t_users_mst tumcharg
WHERE dm.oa_developer = pd.userid
AND tum.i_user_code = pd.userid
AND pm.oa_project = pd.projectid
AND NOT EXISTS
(SELECT 1
FROM t_log_report r
WHERE r.developer = dm.svn_developer)
AND tpm.i_project_code = pd.projectid
AND tumsale.i_user_code(+) = tpm.i_user_sale
AND tumcharg.i_user_code = tpm.i_user_charg)
ORDER BY 2;
CURSOR rec_data2 IS
SELECT decode(oa_dev.i_user_code,
NULL,
svn_rep.developer,
oa_dev.i_user_code) 開發(fā)者編號(hào),
decode(oa_dev.i_user_name,
NULL,
svn_rep.developer,
oa_dev.i_user_name) 開發(fā)者姓名,
pnumber.a 參與項(xiàng)目數(shù),
oa_pro.i_project_code 項(xiàng)目編號(hào),
oa_pro.i_project_name 項(xiàng)目名稱,
decode((SELECT '是'
FROM t_project_developer
WHERE projectid = oa_pro.i_project_code
AND userid = oa_dev.i_user_code),
NULL,
'否',
'是') 項(xiàng)目預(yù)定成員,
oa_pro.user_sale 銷售人員,
oa_pro.user_sale_code 銷售編號(hào),
oa_pro.user_charg 項(xiàng)目負(fù)責(zé)人,
oa_pro.user_charg_code 負(fù)責(zé)人編號(hào),
ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')) 開發(fā)時(shí)長(zhǎng),
decode(svn_day.filenum, NULL, 0, svn_day.filenum) 本日文件數(shù)量,
decode(svn_week.filenum, NULL, 0, svn_week.filenum) 本周文件數(shù)量,
decode(svn_month.filenum, NULL, 0, svn_month.filenum) 本月文件數(shù)量,
decode(svn_year.filenum, NULL, 0, svn_year.filenum) 本年文件數(shù)量,
decode(svn_day.codeline, NULL, 0, svn_day.codeline) 本日代碼行數(shù),
decode(svn_week.codeline, NULL, 0, svn_week.codeline) 本周代碼行數(shù),
decode(svn_month.codeline, NULL, 0, svn_month.codeline) 本月代碼行數(shù),
decode(svn_year.codeline, NULL, 0, svn_year.codeline) 本年代碼行數(shù),
decode(svn_day.commitcount, NULL, 0, svn_day.commitcount) 本日提交次數(shù),
decode(svn_week.commitcount, NULL, 0, svn_week.commitcount) 本周提交次數(shù),
decode(svn_month.commitcount, NULL, 0, svn_month.commitcount) 本月提交次數(shù),
decode(svn_year.commitcount, NULL, 0, svn_year.commitcount) 本年提交次數(shù)
FROM t_log_report svn_rep,
t_project_developer pd,
(SELECT u_d.i_user_code,
u_d.i_user_name,
m2.svn_developer svn_user_code
FROM t_svn_oa_developer_mapping m2, t_users_mst u_d
WHERE m2.oa_developer = u_d.i_user_code) oa_dev,
(SELECT t.*,
m.*,
t.i_user_sale user_sale_code,
t.i_user_charg user_charg_code,
u_sale.i_user_name user_sale,
u_charg.i_user_name user_charg
FROM t_project_mst t,
t_svn_oa_project_mapping m,
t_users_mst u_sale,
t_users_mst u_charg
WHERE t.i_project_code = m.oa_project
AND t.i_user_sale = u_sale.i_user_code(+)
AND t.i_user_charg = u_charg.i_user_code) oa_pro,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_char(logtime, 'yyyymmdd') =
to_char(SYSDATE - 1, 'yyyymmdd')
GROUP BY projectname, developer) svn_day,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE - 1, 'd') + 1 AND
trunc(SYSDATE - 1, 'd') + 1 + 6
GROUP BY projectname, developer) svn_week,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE, 'mm') AND last_day(SYSDATE)
GROUP BY projectname, developer) svn_month,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE, 'yy') AND
last_day(add_months(trunc(SYSDATE, 'y'), 11))
GROUP BY projectname, developer) svn_year,
(SELECT COUNT(*) a, developer b
FROM (SELECT projectname, COUNT(projectname), developer
FROM t_log_report
GROUP BY projectname, developer)
GROUP BY developer) pnumber
WHERE svn_rep.projectname = oa_pro.svn_project
AND svn_rep.developer = oa_dev.svn_user_code(+)
AND svn_rep.projectname = svn_day.projectname(+)
AND svn_rep.developer = svn_day.developer(+)
AND svn_rep.projectname = svn_week.projectname(+)
AND svn_rep.developer = svn_week.developer(+)
AND svn_rep.projectname = svn_month.projectname(+)
AND svn_rep.developer = svn_month.developer(+)
AND svn_rep.projectname = svn_year.projectname(+)
AND svn_rep.developer = svn_year.developer(+)
AND svn_rep.developer = pnumber.b
GROUP BY oa_pro.i_project_code,
oa_pro.i_project_name,
pnumber.a,
ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')),
oa_pro.user_sale,
oa_pro.user_charg,
oa_pro.user_sale_code,
oa_pro.user_charg_code,
svn_rep.developer,
oa_dev.i_user_name,
oa_dev.i_user_code,
svn_day.filenum,
svn_week.filenum,
svn_month.filenum,
svn_year.filenum,
svn_day.codeline,
svn_week.codeline,
svn_month.codeline,
svn_year.codeline,
svn_day.commitcount,
svn_week.commitcount,
svn_month.commitcount,
svn_year.commitcount;
CURSOR rec_data3 IS
SELECT to_char(svn_rep.logtime, 'yyyyMMdd') 日期,
to_char(svn_rep.logtime, 'hh:MM') 時(shí)間,
svn_rep.versionnum 上傳編號(hào),
oa_pro.i_project_code 項(xiàng)目編號(hào),
oa_pro.i_project_name 項(xiàng)目名稱,
oa_pro.i_start_date 開工日期,
ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')) 開發(fā)時(shí)長(zhǎng),
oa_pro.user_sale 銷售負(fù)責(zé)人,
oa_pro.user_sale_code 銷售編號(hào),
oa_pro.user_pro 項(xiàng)目負(fù)責(zé)人,
oa_pro.user_charg_code 負(fù)責(zé)人編號(hào),
decode((SELECT '是'
FROM t_project_developer
WHERE projectid = oa_pro.i_project_code
AND userid = oa_dev.i_user_code),
NULL,
'否',
'是') 項(xiàng)目預(yù)定成員,
decode(oa_dev.i_user_name,
NULL,
svn_rep.developer,
oa_dev.i_user_name) 開發(fā)成員名稱,
decode(oa_dev.i_user_code,
NULL,
svn_rep.developer,
oa_dev.i_user_code) 員工編號(hào),
svn_rep.filenum 上傳文件數(shù)量,
svn_rep.codeline 上傳代碼行數(shù),
'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
svn_rep.projectname || '/user_' || svn_rep.developer || '.html' 明細(xì)鏈接
FROM t_log_report svn_rep,
t_project_mst pro_mst,
(SELECT u_d.i_user_code,
u_d.i_user_name,
m2.svn_developer svn_user_code
FROM t_svn_oa_developer_mapping m2, t_users_mst u_d
WHERE m2.oa_developer = u_d.i_user_code) oa_dev,
(SELECT t.*,
m.*,
t.i_user_sale user_sale_code,
t.i_user_charg user_charg_code,
u_sale.i_user_name user_sale,
u_pro.i_user_name user_pro
FROM t_project_mst t,
t_svn_oa_project_mapping m,
t_users_mst u_sale,
t_users_mst u_pro
WHERE t.i_project_code = m.oa_project
AND t.i_user_sale = u_sale.i_user_code(+)
AND t.i_user_charg = u_pro.i_user_code) oa_pro,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_char(logtime, 'yyyymmdd') =
to_char(SYSDATE - 1, 'yyyymmdd')
GROUP BY projectname, developer) svn_day,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE - 1, 'd') + 1 AND
trunc(SYSDATE - 1, 'd') + 1 + 6
GROUP BY projectname, developer) svn_week,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE, 'mm') AND last_day(SYSDATE)
GROUP BY projectname, developer) svn_month,
(SELECT projectname,
developer,
SUM(codeline) codeline,
SUM(filenum) filenum,
COUNT(1) commitcount
FROM t_log_report
WHERE to_date(to_char(logtime, 'yyyymmdd'), 'yyyymmdd') BETWEEN
trunc(SYSDATE, 'yy') AND
last_day(add_months(trunc(SYSDATE, 'y'), 11))
GROUP BY projectname, developer) svn_year
WHERE svn_rep.projectname = oa_pro.svn_project
AND svn_rep.developer = oa_dev.svn_user_code(+)
AND svn_rep.projectname = svn_day.projectname(+)
AND svn_rep.developer = svn_day.developer(+)
AND svn_rep.projectname = svn_week.projectname(+)
AND svn_rep.developer = svn_week.developer(+)
AND svn_rep.projectname = svn_month.projectname(+)
AND svn_rep.developer = svn_month.developer(+)
AND svn_rep.projectname = svn_year.projectname(+)
AND svn_rep.developer = svn_year.developer(+)
GROUP BY svn_rep.logtime,
svn_rep.versionnum,
oa_pro.i_project_code,
oa_pro.i_project_name,
oa_pro.i_start_date,
svn_rep.filenum,
svn_rep.codeline,
'http://showsan.com:8081/' || to_char(SYSDATE, 'yyyymmdd') || '/' ||
svn_rep.projectname || '/user_' || svn_rep.developer || '.html',
ceil(SYSDATE - to_date(oa_pro.i_start_date, 'yyyymmdd')),
oa_pro.user_sale,
oa_pro.user_pro,
oa_pro.user_sale_code,
oa_pro.user_charg_code,
svn_rep.developer,
oa_dev.i_user_name,
oa_dev.i_user_code;
BEGIN
/* 清空計(jì)算當(dāng)天數(shù)據(jù) */
DELETE t_svn_report_by_project
WHERE to_char(report_time, 'yyyymmdd') = to_char(SYSDATE, 'yyyymmdd');
DELETE t_svn_report_by_users
WHERE to_char(report_time, 'yyyymmdd') = to_char(SYSDATE, 'yyyymmdd');
DELETE t_svn_report_by_detail
WHERE to_char(report_time, 'yyyymmdd') = to_char(SYSDATE, 'yyyymmdd');
/* 循環(huán)寫入Project數(shù)據(jù) */
FOR rec IN rec_data LOOP
INSERT INTO t_svn_report_by_project
(project_code
,project_name
,develop_days
,user_sales
,user_project
,file_count
,code_count
,commit_count
,schedule_developer
,developer
,user_code
,file_count_day
,file_count_week
,file_count_month
,file_count_year
,code_count_day
,code_count_week
,code_count_month
,code_count_year
,commit_count_day
,commit_count_week
,commit_count_month
,commit_count_year
,detai_url
,report_time
,user_sale_code
,user_charg_code)
VALUES
(rec.項(xiàng)目編號(hào)
,rec.項(xiàng)目名稱
,rec.開發(fā)時(shí)長(zhǎng)
,rec.銷售負(fù)責(zé)人
,rec.項(xiàng)目負(fù)責(zé)人
,rec.總文件數(shù)量
,rec.總代碼數(shù)量
,rec.總提交次數(shù)
,rec.項(xiàng)目預(yù)定成員
,rec.開發(fā)成員名稱
,rec.員工編號(hào)
,rec.本日文件數(shù)量
,rec.本周文件數(shù)量
,rec.本月文件數(shù)量
,rec.本年文件數(shù)量
,rec.本日代碼行數(shù)
,rec.本周代碼行數(shù)
,rec.本月代碼行數(shù)
,rec.本年代碼行數(shù)
,rec.本日提交次數(shù)
,rec.本周提交次數(shù)
,rec.本月提交次數(shù)
,rec.本年提交次數(shù)
,rec.明細(xì)鏈接
,SYSDATE
,rec.銷售編號(hào)
,rec.負(fù)責(zé)人編號(hào));
END LOOP;
/* 循環(huán)寫入users數(shù)據(jù) */
FOR rec IN rec_data2 LOOP
INSERT INTO t_svn_report_by_users
(developer_number
,developer_name
,join_project_number
,project_number
,project_name
,schedule_developer
,user_sale
,user_project
,project_days
,file_count_day
,file_count_week
,file_count_month
,file_count_year
,code_count_day
,code_count_week
,code_count_month
,code_count_year
,commit_count_day
,commit_count_week
,commit_count_month
,commit_count_year
,report_time
,user_sale_code
,user_charg_code)
VALUES
(rec.開發(fā)者編號(hào)
,rec.開發(fā)者姓名
,rec.參與項(xiàng)目數(shù)
,rec.項(xiàng)目編號(hào)
,rec.項(xiàng)目名稱
,rec.項(xiàng)目預(yù)定成員
,rec.銷售人員
,rec.項(xiàng)目負(fù)責(zé)人
,rec.開發(fā)時(shí)長(zhǎng)
,rec.本日文件數(shù)量
,rec.本周文件數(shù)量
,rec.本月文件數(shù)量
,rec.本年文件數(shù)量
,rec.本日代碼行數(shù)
,rec.本周代碼行數(shù)
,rec.本月代碼行數(shù)
,rec.本年代碼行數(shù)
,rec.本日提交次數(shù)
,rec.本周提交次數(shù)
,rec.本月提交次數(shù)
,rec.本年提交次數(shù)
,SYSDATE
,rec.銷售編號(hào)
,rec.負(fù)責(zé)人編號(hào));
END LOOP;
/* 循環(huán)寫入detail數(shù)據(jù) */
FOR rec IN rec_data3 LOOP
INSERT INTO t_svn_report_by_detail
(submit_date
,submit_time
,upload_number
,project_number
,project_name
,start_date
,user_sale
,user_project
,schedule_developer
,developer
,user_code
,file_number
,code_number
,detai_url
,report_time
,user_sale_code
,user_charg_code
)
VALUES
(rec.日期
,rec.時(shí)間
,rec.上傳編號(hào)
,rec.項(xiàng)目編號(hào)
,rec.項(xiàng)目名稱
,rec.開工日期
,rec.銷售負(fù)責(zé)人
,rec.項(xiàng)目負(fù)責(zé)人
,rec.項(xiàng)目預(yù)定成員
,rec.開發(fā)成員名稱
,rec.員工編號(hào)
,rec.上傳文件數(shù)量
,rec.上傳代碼行數(shù)
,rec.明細(xì)鏈接
,SYSDATE
,rec.銷售編號(hào)
,rec.負(fù)責(zé)人編號(hào));
END LOOP;
/* 提交事務(wù) */
COMMIT;
/* 返回狀態(tài) */
RETURN;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END calc_svn_report_by_day;
END oa_pkg;
/
