public List <ItfEoInfo> TermEOList(int Term)
{
try
{
if (!dbCon.IsDBConnected)
{
dbCon.DBConnect();
}
string sQuery = @"
select * from itf_eo_info
where itf_dt ::date < now() - interval '{0} day'";
sQuery = string.Format(sQuery, Term);
DataSet ds = dbCon.NewDataSet(sQuery);
List <ItfEoInfo> list = ConvertDataSetToList.ConvertTo <ItfEoInfo>(ds.Tables[0]);
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
public List <ItfFileInfo> TermFileList(int Term)
{
try
{
if (!dbCon.IsDBConnected)
{
dbCon.DBConnect();
}
string sQuery = @"
select itf_dt, part_no, part_rev_no, file_idx, file_org_nm, file_conv_nm from (
select max(c.itf_dt) as itf_dt, a.part_no, a.part_rev_no, a.file_idx, a.file_org_nm, a.file_conv_nm from itf_file_info A
left join itf_part_master B on A.part_no = B.part_no and A.part_rev_no = B.part_rev_no
left join itf_eo_info C on B.eo_idx = C.eo_idx
where is_itf='Y' and is_del ='N'
group by a.part_no, a.part_rev_no, a.file_idx, a.file_org_nm, a.file_conv_nm) f
where itf_dt ::date < now() - interval '{0} day'";
sQuery = string.Format(sQuery, Term);
DataSet ds = dbCon.NewDataSet(sQuery);
List <ItfFileInfo> list = ConvertDataSetToList.ConvertTo <ItfFileInfo>(ds.Tables[0]);
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
public List <DistMasterModel> GetDistList()
{
try
{
if (!dbCon.IsDBConnected)
{
dbCon.DBConnect();
}
string sQuery = @"
SELECT
A.dist_idx, A.dist_title, A.dist_msg, A.create_dt, A.dist_dt, A.edit_dt,
A.create_us, B.us_nm as create_us_nm, A.finish_dt, A.dist_st, C.kor_nm as dist_st_nm, A.eo_fl, D.eo_idx, D.eo_nm, D.eo_no , grp_list.vender_list,
recvTotal.recvCnt as recv_cnt, recvTotal.recvFullDownCnt as recv_full_down_cnt
FROM dist_master A
LEFT JOIN sys_users B ON A.create_us = B.us_idx AND B.us_role > 10
LEFT JOIN v_sys_library C ON C.parent_comm_code = 'DIST_ST' AND A.dist_st = C.comm_code
LEFT JOIN v_dist_eo D ON A.dist_idx = D.dist_idx
LEFT JOIN (
SELECT dist_idx, array_to_string(array_agg(DISTINCT grp_list.group_nm order by grp_list.group_nm), ', ') vender_list
FROM (
SELECT dist_idx, case when recvU.us_role = 10 then '협력사 : ' || vend.vend_nm else '자사 : ' || dept.dept_nm end as group_nm
FROM dist_receiver recv
LEFT JOIN sys_users recvU on recv.recv_us = recvU.us_idx
LEFT JOIN sys_department dept on recvU.us_group = dept.dept_idx and recvU.us_role > 10
LEFT JOIN sys_vender vend on recvU.us_group = vend.vend_idx and recvU.us_role = 10
GROUP BY dist_idx, recvU.us_role, recvU.us_group,vend.vend_nm, dept.dept_nm ) grp_list
GROUP BY grp_list.dist_idx ) as grp_list on A.dist_idx = grp_list.dist_idx
LEFT JOIN (
select dist_idx, count(recv_idx) as recvCnt, sum(isFullRecv) as recvFullDownCnt from (
select dist_idx, recv_idx
, case when count(dist_file_idx) = sum(hiscnt) then 1 else 0 end as isFullRecv from (
select
A.dist_idx, A.recv_idx, B.dist_file_idx, case when L.action_target_idx is null then 0 else 1 end hiscnt
from dist_receiver A
left join dist_recv_file B on A.recv_idx = B.recv_idx
left join log_action_history L on L.action_module = 10 and L.action_type = 90 and L.action_target_idx = B.dist_file_idx
group by A.dist_idx, A.recv_idx, B.dist_file_idx, L.action_target_idx) as g
group by dist_idx, recv_idx) t
group by dist_idx ) as recvTotal on A.dist_idx = recvTotal.dist_idx
WHERE 1=1
AND A.dist_st = 'DS'";
DataSet ds = dbCon.NewDataSet(sQuery);
List <DistMasterModel> list = ConvertDataSetToList.ConvertTo <DistMasterModel>(ds.Tables[0]);
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
public List <DistReceiverModel> GetRecvDistList()
{
try
{
if (!dbCon.IsDBConnected)
{
dbCon.DBConnect();
}
string sQuery = @"
SELECT * FROM dist_receiver
WHERE recv_dist_st = 'DS'";
DataSet ds = dbCon.NewDataSet(sQuery);
List <DistReceiverModel> list = ConvertDataSetToList.ConvertTo <DistReceiverModel>(ds.Tables[0]);
return(list);
}
catch (Exception ex)
{
throw ex;
}
}
