public string TotalCount(PCURLApproveParam tParam) { StringBuilder sb = new StringBuilder(); sb.Append(" SELECT COUNT(A.TRANS_SEQ) "); sb.Append(" FROM ( "); sb.Append(" SELECT * "); sb.Append(" FROM TBL_PCURL_REQ_INFO "); sb.Append(" UNION ALL "); sb.Append(" SELECT * "); sb.Append(" FROM TBL_PCURL_REQ_HIS "); sb.Append(" WHERE REQUEST_TIME between '" + tParam.SearchFromDay + "' and '" + tParam.SearchToDay + "'"); sb.Append(" ) P "); sb.Append(" INNER JOIN( "); sb.Append(" SELECT 1 D_POS, * FROM TBL_APPROVE_INFO "); sb.Append(" UNION ALL "); sb.Append(" SELECT 0 D_POS, * FROM TBL_APPROVE_HIS "); sb.Append(" WHERE APPR_REQ_TIME between '" + tParam.SearchFromDay + "' and '" + tParam.SearchToDay + "'"); sb.Append(" ) A ON P.TRANS_SEQ = A.TRANS_SEQ "); sb.Append(" LEFT OUTER JOIN ( "); sb.Append(" SELECT * "); sb.Append(" FROM TBL_USER_SFM S "); sb.Append(" WHERE NOW() BETWEEN TO_DATE(FROMDATE || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE(TODATE || '235959', 'YYYYMMDDHH24MISS') "); sb.Append(" ) S ON A.APPROVE_USER_SEQ = S.USER_SEQ "); sb.Append(" LEFT JOIN TBL_USER_INFO U ON U.USER_SEQ = A.USER_SEQ "); sb.Append(" WHERE(A.APPROVE_USER_SEQ = " + tParam.UserSeq + " OR S.SFM_USER_SEQ = " + tParam.UserSeq + ") "); if (tParam.ApprStatus != null && tParam.ApprStatus.Length > 0) { if (tParam.ApprStatus == "11") { sb.Append(" AND p.req_flag = '2' "); } else { sb.Append(" AND p.approve_flag = '" + tParam.ApprStatus + "'"); } } if (tParam.ReqUserName != null && tParam.ReqUserName.Length > 0) { sb.Append(" AND u.user_name like '%" + tParam.ReqUserName + "%'"); } if (tParam.Url != null && tParam.Url.Length > 0) { sb.Append(" AND func_nl2_urlcount(p.trans_seq, '" + tParam.Url + "')>0 "); } if (tParam.Title != null && tParam.Title.Length > 0) { sb.Append(" AND title like '%" + tParam.Title + "%'"); } return(sb.ToString()); }
public string List(PCURLApproveParam tParam) { StringBuilder sb = new StringBuilder(); sb.Append(" SELECT A.TRANS_SEQ, A.REQ_SEQ, CAST((CASE WHEN GETAPPROVEPOSSIBLE2(A.APPROVE_USER_SEQ, A.TRANS_SEQ, A.APPROVE_ORDER) > 0 THEN 1 ELSE 0 END) AS CHARACTER) APPROVEPOSSIBLE "); sb.Append(" , CAST(A.D_POS AS VARCHAR) APPROVESTAUTS2 "); sb.Append(" , CAST((CASE WHEN P.REQ_FLAG = '2' THEN '11' ELSE A.APPROVE_FLAG END) AS CHARACTER VARYING) APPROVESTAUTS "); sb.Append(" , CAST(U.USER_NAME || (CASE WHEN COALESCE(U.USER_RANK, U.USER_POSITION, '') != '' THEN('' || COALESCE(U.USER_RANK, U.USER_POSITION)) ELSE '' END) AS VARCHAR) "); sb.Append(" , P.TITLE, P.CONTENT, CAST(P.REQUEST_TIME AS CHARACTER VARYING), CAST(P.APPROVE_TIME AS CHARACTER VARYING) "); sb.Append(" FROM ( "); sb.Append(" SELECT * "); sb.Append(" FROM TBL_PCURL_REQ_INFO "); sb.Append(" UNION ALL "); sb.Append(" SELECT * "); sb.Append(" FROM TBL_PCURL_REQ_HIS "); sb.Append(" WHERE REQUEST_TIME between '" + tParam.SearchFromDay + "' and '" + tParam.SearchToDay + "'"); sb.Append(" ) P "); sb.Append(" INNER JOIN( "); sb.Append(" SELECT 1 D_POS, * FROM TBL_APPROVE_INFO "); sb.Append(" UNION ALL "); sb.Append(" SELECT 0 D_POS, * FROM TBL_APPROVE_HIS "); sb.Append(" WHERE APPR_REQ_TIME between '" + tParam.SearchFromDay + "' and '" + tParam.SearchToDay + "'"); sb.Append(" ) A ON P.TRANS_SEQ = A.TRANS_SEQ "); sb.Append(" LEFT OUTER JOIN ( "); sb.Append(" SELECT * "); sb.Append(" FROM TBL_USER_SFM S "); sb.Append(" WHERE NOW() BETWEEN TO_DATE(FROMDATE || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE(TODATE || '235959', 'YYYYMMDDHH24MISS') "); sb.Append(" ) S ON A.APPROVE_USER_SEQ = S.USER_SEQ "); sb.Append(" LEFT JOIN TBL_USER_INFO U ON U.USER_SEQ = A.USER_SEQ "); sb.Append(" WHERE(A.APPROVE_USER_SEQ = " + tParam.UserSeq + " OR S.SFM_USER_SEQ = " + tParam.UserSeq + ") "); if (tParam.ApprStatus != null && tParam.ApprStatus.Length > 0) { if (tParam.ApprStatus == "11") { sb.Append(" AND p.req_flag = '2' "); } else { sb.Append(" AND p.approve_flag = '" + tParam.ApprStatus + "'"); } } if (tParam.ReqUserName != null && tParam.ReqUserName.Length > 0) { sb.Append(" AND u.user_name like '%" + tParam.ReqUserName + "%'"); } if (tParam.Url != null && tParam.Url.Length > 0) { sb.Append(" AND func_nl2_urlcount(p.trans_seq, '" + tParam.Url + "')>0 "); } if (tParam.Title != null && tParam.Title.Length > 0) { sb.Append(" AND title like '%" + tParam.Title + "%'"); } sb.Append(" order by p.request_time desc "); sb.Append(" limit " + tParam.PageListCount + " offset (" + tParam.ViewPageNo + "-1) * " + tParam.PageListCount); return(sb.ToString()); }