public string TotalCount(TransferParam tParam) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) FROM ( "); sb.Append("SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus(a.trans_flag, a.recv_flag, a.pctrans_flag) as transStatus, a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" '1' as downPossible, To_char(To_timestamp(COALESCE(b.expired_date, '00000000'),'YYYYMMDD'),'YYYY-MM-DD') as expiredDate, coalesce(b.download_count, 0) as downCount, "); sb.Append(" get_forward_flag(a.trans_seq) as dataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '0' as receiveType "); sb.Append(" ,c.src_system_id, c.dest_system_id "); sb.Append(" FROM tbl_transfer_req_info a "); sb.Append(" LEFT OUTER JOIN view_backup_period b ON (a.trans_seq = b.trans_seq) "); sb.Append(" LEFT OUTER JOIN tbl_transfer_req_sub_his c ON (a.trans_seq = c.trans_seq) "); sb.Append("WHERE a.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append("UNION ALL "); sb.Append("SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus(a.trans_flag, a.recv_flag, a.pctrans_flag) as transStatus, a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" coalesce(b.download_alive, '0') as downPossible, To_char(To_timestamp(COALESCE(b.expired_date, '00000000'),'YYYYMMDD'),'YYYY-MM-DD') as expiredDate, "); sb.Append(" coalesce(b.download_count, 1) as downCount, "); sb.Append(" get_forward_flag(a.trans_seq) as DataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '0' as receiveType "); sb.Append(" ,c.src_system_id, c.dest_system_id "); sb.Append(" FROM tbl_transfer_req_his a "); sb.Append(" LEFT OUTER JOIN view_backup_period b ON (a.trans_seq = b.trans_seq) "); sb.Append(" LEFT OUTER JOIN tbl_transfer_req_sub_his c ON (a.trans_seq = c.trans_seq) "); sb.Append("WHERE a.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append("UNION ALL "); sb.Append("SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus_fwd(a.trans_flag, a.recv_flag, a.pctrans_flag, '1', b.download_count, a.approve_flag) as transStatus, "); sb.Append(" a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" '1' as downPossible, To_char(To_timestamp('00000000','YYYYMMDD'),'YYYY-MM-DD') as ExpiredDate, 0 as downCount, "); sb.Append(" '2' as dataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '1' as receiveType "); sb.Append(" ,c.src_system_id, c.dest_system_id "); sb.Append("FROM view_transfer_all a "); sb.Append(" INNER JOIN tbl_forward_info b ON (a.trans_seq = b.trans_seq) "); sb.Append(" LEFT OUTER JOIN tbl_transfer_req_sub_his c ON (a.trans_seq = c.trans_seq) "); sb.Append("WHERE b.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append("UNION ALL "); sb.Append("SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus_fwd(a.trans_flag, a.recv_flag, a.pctrans_flag, '2', b.download_count, a.approve_flag) as transstatus, "); sb.Append(" a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" '0' as downPossible, To_char(To_timestamp('00000000','YYYYMMDD'),'YYYY-MM-DD') as ExpiredDate, 1 as downCount, "); sb.Append(" '2' as dataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '1' as receiveType "); sb.Append(" ,c.src_system_id, c.dest_system_id "); sb.Append(" FROM view_transfer_all a "); sb.Append(" INNER JOIN tbl_forward_info_his b ON (a.trans_seq = b.trans_seq) "); sb.Append(" LEFT OUTER JOIN tbl_transfer_req_sub_his c ON (a.trans_seq = c.trans_seq) "); sb.Append("WHERE b.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append(") as x "); sb.Append("where 1=1"); if (tParam.TransKind.Equals("1") || tParam.TransKind.Equals("2")) { sb.Append(" AND ioType = '" + tParam.TransKind + "'"); } if (tParam.TransStatus.Equals("W")) { if (tParam.TransStatus != null && tParam.TransStatus.Length > 0) { sb.Append(" AND approveFlag != '3' "); } } if (tParam.TransStatus.Equals("C")) { if (tParam.TransStatus != null && tParam.TransStatus.Length > 0) { sb.Append(" AND (transStatus = '" + tParam.TransStatus + "' OR approveFlag = '3' )"); } } else { if (tParam.TransStatus != null && tParam.TransStatus.Length > 0) { sb.Append(" AND transStatus = '" + tParam.TransStatus + "'"); } } if (tParam.ApprStatus.Equals("4")) { if (tParam.ApprStatus != null && tParam.ApprStatus.Length > 0) { sb.Append(" AND transStatus = 'C' "); sb.Append(" AND approveFlag != '2' "); sb.Append(" AND approveFlag != '3' "); } } else { if (tParam.ApprStatus != null && tParam.ApprStatus.Length > 0) { sb.Append(" AND approveFlag = '" + tParam.ApprStatus + "'"); } } if (tParam.ApprStatus != null && tParam.ApprStatus == "1") { sb.Append(" AND approveFlag = '" + tParam.ApprStatus + "' AND transStatus != 'C' "); } if (tParam.Title != null && tParam.Title.Length > 0) { //sb.Append(" AND title LIKE '%' || '" + tParam.Title + "' || '%'"); sb.Append(" AND UPPER(title) LIKE UPPER('%' || '" + tParam.Title + "' || '%')"); } // 기존 : 송신내용 /* * if (tParam.Src_system_id != null && tParam.Src_system_id.Length > 0) * { * sb.Append(" AND src_system_id = '" + tParam.Src_system_id + "'"); * } * if (tParam.Dest_system_id != null && tParam.Dest_system_id.Length > 0) * { * sb.Append(" AND dest_system_id = '" + tParam.Dest_system_id + "'"); * }*/ // 변경 : 송신내용 /* * if (tParam.Src_system_id != null && tParam.Src_system_id.Length > 0) * { * sb.Append(" AND (substring(src_system_id,1,1) = '" + tParam.Src_system_id.Substring(0,1) + "')"); * } */ if (tParam.Dest_system_id != null && tParam.Dest_system_id.Length > 0) { sb.Append(" AND (substring(dest_system_id,1,2) = '" + tParam.Dest_system_id.Substring(0, 2) + "')"); // 목적망:자신선택때사용 } // 추가 : 수신내용 //if (tParam.Src_system_id != null && tParam.Src_system_id.Length > 0 && // (tParam.Dest_system_id == null || tParam.Dest_system_id.Length == 0)) //{ // sb.Append("OR dest_system_id = '" + tParam.Src_system_id + "')"); // 목적망:전체선택때,수신망 정보나오게 사용 //} return(sb.ToString()); }
//날짜포멧 확인필요('-'나 '/' 지워졌는지 확인) public string List(TransferParam tParam) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ( "); sb.Append("SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus(a.trans_flag, a.recv_flag, a.pctrans_flag) as transStatus, a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" '1' as downPossible, To_char(To_timestamp(COALESCE(b.expired_date, '00000000'),'YYYYMMDD'),'YYYY-MM-DD') as expiredDate, coalesce(b.download_count, 0) as downCount, "); sb.Append(" get_forward_flag(a.trans_seq) as dataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '0' as receiveType "); sb.Append("FROM tbl_transfer_req_info a "); sb.Append(" LEFT OUTER JOIN view_backup_period b ON (a.trans_seq = b.trans_seq) "); sb.Append("WHERE a.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append(" UNION ALL "); sb.Append(" SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus(a.trans_flag, a.recv_flag, a.pctrans_flag) as transStatus, a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" coalesce(b.download_alive, '0') as downPossible, To_char(To_timestamp(COALESCE(b.expired_date, '00000000'),'YYYYMMDD'),'YYYY-MM-DD') as expiredDate, "); sb.Append(" coalesce(b.download_count, 1) as downCount, "); sb.Append(" get_forward_flag(a.trans_seq) as DataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '0' as receiveType "); sb.Append("FROM tbl_transfer_req_his a "); sb.Append(" LEFT OUTER JOIN view_backup_period b ON (a.trans_seq = b.trans_seq) "); sb.Append("WHERE a.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append(" UNION ALL "); sb.Append(" SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus_fwd(a.trans_flag, a.recv_flag, a.pctrans_flag, '1', b.download_count, a.approve_flag) as transStatus, "); sb.Append(" a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" '1' as downPossible, To_char(To_timestamp('00000000','YYYYMMDD'),'YYYY-MM-DD') as ExpiredDate, 0 as downCount, "); sb.Append(" '2' as dataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '1' as receiveType "); sb.Append("FROM view_transfer_all a "); sb.Append(" INNER JOIN tbl_forward_info b ON (a.trans_seq = b.trans_seq) "); sb.Append("WHERE b.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append(" UNION ALL "); sb.Append(" SELECT a.trans_seq as transSeq, a.dlp as dlp, CASE WHEN substring(a.system_id, 1, 1)='I' THEN '1' ELSE '2' END as ioType, "); sb.Append(" func_transstatus_fwd(a.trans_flag, a.recv_flag, a.pctrans_flag, '2', b.download_count, a.approve_flag) as transstatus, "); sb.Append(" a.approve_kind as approveKind, a.approve_flag as approveFlag, a.title as title, a.file_size as fileSize, "); sb.Append(" func_transfilepos(a.system_id, a.trans_flag, a.recv_flag, a.pctrans_flag) as transPos, to_char(to_timestamp(substring(a.request_time, 1, 14),'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS') as requestTime, "); sb.Append(" '0' as downPossible, To_char(To_timestamp('00000000','YYYYMMDD'),'YYYY-MM-DD') as ExpiredDate, 1 as downCount, "); sb.Append(" '2' as dataForwarded, get_user_info(a.user_seq) as orgUserInfo, a.recv_pos as recvPos, '1' as receiveType "); sb.Append(" FROM view_transfer_all a "); sb.Append(" INNER JOIN tbl_forward_info_his b ON (a.trans_seq = b.trans_seq) "); sb.Append(" WHERE b.user_seq IN (select user_seq from tbl_user_info where user_id = '" + tParam.UserID + "') "); if (!(tParam.SearchFromDay.Equals("")) && (tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "'"); } else if ((tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time <= '" + tParam.SearchToDay + "'"); } else if (!(tParam.SearchFromDay.Equals("")) && !(tParam.SearchToDay.Equals(""))) { sb.Append(" AND a.request_time >= '" + tParam.SearchFromDay + "' AND a.request_time <= '" + tParam.SearchToDay + "'"); } sb.Append(") as x "); sb.Append("where 1=1"); if (tParam.TransKind.Equals("1") || tParam.TransKind.Equals("2")) { sb.Append(" AND ioType = '" + tParam.TransKind + "'"); } if (tParam.TransStatus.Equals("W")) { if (tParam.TransStatus != null && tParam.TransStatus.Length > 0) { sb.Append(" AND approveFlag != '3' "); } } if (tParam.TransStatus.Equals("C")) { if (tParam.TransStatus != null && tParam.TransStatus.Length > 0) { sb.Append(" AND (transStatus = '" + tParam.TransStatus + "' OR approveFlag = '3' )"); } } else { if (tParam.TransStatus != null && tParam.TransStatus.Length > 0) { sb.Append(" AND transStatus = '" + tParam.TransStatus + "'"); } } if (tParam.ApprStatus.Equals("4")) { if (tParam.ApprStatus != null && tParam.ApprStatus.Length > 0) { sb.Append(" AND transStatus = 'C' "); sb.Append(" AND approveFlag != '2' "); sb.Append(" AND approveFlag != '3' "); } } else { if (tParam.ApprStatus != null && tParam.ApprStatus == "1") { sb.Append(" AND approveFlag = '" + tParam.ApprStatus + "' AND transStatus != 'C' "); } else if (tParam.ApprStatus != null && tParam.ApprStatus.Length > 0) { sb.Append(" AND approveFlag = '" + tParam.ApprStatus + "'"); } } if (tParam.Title != null && tParam.Title.Length > 0) { //sb.Append(" AND title LIKE '%' || '" + tParam.Title + "' || '%'"); sb.Append(" AND UPPER(title) LIKE UPPER('%' || '" + tParam.Title + "' || '%')"); } sb.Append(" ORDER BY requestTime desc"); sb.Append(" limit " + tParam.PageListCount + " offset (" + tParam.ViewPageNo + "-1) * " + tParam.PageListCount); return(sb.ToString()); }