public List <Pad> ExecuteQuery(Dictionary <string, string> phases, string projectName, string performerKeyWork)
{
string strSql = @"select performer.performer, pvt.* from
(
select
ac1.short_name as pad_num,
ac2.short_name as pad_name,
(
case " ;
foreach (KeyValuePair <string, string> pair in phases)
{
strSql += String.Format("when t.task_name like '{0}%' then '{1}' ", pair.Value, pair.Key);
}
strSql += @"else 'unknown'
end
) as task_name,
(
case
when t.status_code='TK_Complete' then 1
when t.status_code='TK_NotStart' then 0
when t.status_code='TK_Active' and t.task_type = 'TT_LOE' then
convert(decimal,(t.restart_date - t.act_start_date))/convert(decimal,(t.early_end_date - t.act_start_date))
else (1-t.remain_drtn_hr_cnt/t.target_drtn_hr_cnt)
end) as task_percent
from task t
inner join PROJECT p on p.proj_id = t.proj_id
inner join TASKACTV ta1 on ta1.task_id = t.task_id
inner join ACTVCODE ac1 on ac1.actv_code_id = ta1.actv_code_id
inner join ACTVTYPE at1 on at1.actv_code_type_id = ac1.actv_code_type_id
inner join TASKACTV ta2 on ta2.task_id = t.task_id
inner join ACTVCODE ac2 on ac2.actv_code_id = ta2.actv_code_id
inner join ACTVTYPE at2 on at2.actv_code_type_id = ac2.actv_code_type_id
where
p.proj_short_name = '" + projectName + @"'
and at1.actv_code_type = 'Ось'
and at2.actv_code_type = 'Плита'
and ( " ;
foreach (KeyValuePair <string, string> pair in phases)
{
strSql += String.Format("t.task_name like '{0}%' or ", pair.Value);
}
strSql += @"t.task_name = 'not_using_this_value' )
) t
pivot (
max(task_percent) for task_name in (" ;
foreach (KeyValuePair <string, string> pair in phases)
{
strSql += String.Format("[{0}], ", pair.Key);
}
;
strSql += @"[unknown]
)
) pvt
left join (
select
ac1.short_name as pad_num,
ac2.short_name as pad_name,
ac3.short_name as performer
from TASK t
inner join PROJECT p on p.proj_id = t.proj_id
inner join TASKACTV ta1 on ta1.task_id = t.task_id
inner join ACTVCODE ac1 on ac1.actv_code_id = ta1.actv_code_id
inner join ACTVTYPE at1 on at1.actv_code_type_id = ac1.actv_code_type_id
inner join TASKACTV ta2 on ta2.task_id = t.task_id
inner join ACTVCODE ac2 on ac2.actv_code_id = ta2.actv_code_id
inner join ACTVTYPE at2 on at2.actv_code_type_id = ac2.actv_code_type_id
left join TASKACTV ta3 on ta3.task_id = t.task_id
left join ACTVCODE ac3 on ac3.actv_code_id = ta3.actv_code_id
left join ACTVTYPE at3 on at3.actv_code_type_id = ac3.actv_code_type_id
where at3.actv_code_type='Участок'
and at1.actv_code_type = 'Ось'
and at2.actv_code_type = 'Плита'
and p.proj_short_name = '" + projectName +
"' and t.task_name like '" + performerKeyWork +
@"%') performer on performer.pad_name = pvt.pad_name and performer.pad_num = pvt.pad_num
order by pad_num, pad_name";
scn.Open();
SqlCommand sc = new SqlCommand(strSql, scn);
SqlDataReader myDataReader = sc.ExecuteReader();
List <Pad> resultPadList = new List <Pad>();
while (myDataReader.Read())
{
Pad pad = new Pad();
pad.Performer = myDataReader.GetValue(0).ToString();
pad.Axis = Convert.ToInt32(myDataReader.GetValue(1));
pad.Name = myDataReader.GetValue(2).ToString();
for (int i = 3; i < myDataReader.FieldCount; i++)
{
PadPhase pp = new PadPhase()
{
Name = myDataReader.GetName(i).ToString(),
Percent = myDataReader.GetValue(i) is Decimal?
Convert.ToDouble(myDataReader.GetValue(i)) : -1.0
};
pad.AddPhase(pp);
}
resultPadList.Add(pad);
}
scn.Close();
return(resultPadList);
}
public void AddPhase(PadPhase pp)
{
phases.Add(pp);
}
