private void dansearchbutton_Click(object sender, RoutedEventArgs e)
{
SearchObject so = daninfosearch.DataContext as SearchObject;
if (null == so.GetPropertyValue("SecondStair"))
{
so.SetPropertyValue("org", so.GetPropertyValue("FirstStair"), false);
}
else
{
if (null == so.GetPropertyValue("ThirdStair"))
{
so.SetPropertyValue("org", so.GetPropertyValue("FirstStair") + "." + so.GetPropertyValue("SecondStair"), false);
}
else
{
if (null == so.GetPropertyValue("FourthStair"))
{
so.SetPropertyValue("org", so.GetPropertyValue("FirstStair") + "." + so.GetPropertyValue("SecondStair") + "." + so.GetPropertyValue("ThirdStair"), false);
}
else
{
if (null == so.GetPropertyValue("f_sgoperator"))
{
so.SetPropertyValue("org", so.GetPropertyValue("FirstStair") + "." + so.GetPropertyValue("SecondStair") + "." + so.GetPropertyValue("ThirdStair") + "." + so.GetPropertyValue("FourthStair"), false);
}
}
}
}
so.Search();
}
/// <summary>
/// 安检统计列表选择改变
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void paperGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
GeneralObject go = paperGrid.SelectedItem as GeneralObject;
if (go == null)
{
return;
}
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
if (go.GetPropertyValue("state").Equals("未检"))
{
String HQL = "select id, checkplanid, id id1, cast('未检' as varchar(10)) condition, null deleted, null hasnotified, null user_name, null card_id, road, unit_name, cus_dom, cus_dy, cus_floor, cus_room, null old_address, null save_people, null repairman from t_ic_safecheck_paper where checkplanid='{0}' and road = '{1}' and unit_name='{2}' and cus_dom='{3}' and cus_dy='{4}' and cus_floor='{5}' and cus_room='{6}' and id='{7}'";
checkList.HQL = String.Format(HQL, new String[] { conditions.GetPropertyValue("CHECKPLANID") as string, go.GetPropertyValue("road").ToString(), go.GetPropertyValue("rarea").ToString(), go.GetPropertyValue("building").ToString(), go.GetPropertyValue("unit").ToString(), go.GetPropertyValue("floor").ToString(), go.GetPropertyValue("room").ToString(), go.GetPropertyValue("paperid").ToString() });
checkList.Load();
}
else
{
String rightJoinCriteria = " and 1=1 ";
String repairman = conditions.GetPropertyValue("REPAIRMAN") as string;
if (!IsNullOrEmpty(repairman))
{
rightJoinCriteria += " and REPAIRMAN like '%" + repairman + "%'";
}
String SAVE_PEOPLE = conditions.GetPropertyValue("SAVE_PEOPLE") as string;
if (!IsNullOrEmpty(SAVE_PEOPLE))
{
rightJoinCriteria += " and SAVE_PEOPLE like '%" + SAVE_PEOPLE + "%'";
}
String card_id = conditions.GetPropertyValue("CARD_ID") as string;
if (!IsNullOrEmpty(card_id))
{
rightJoinCriteria += " and card_id =" + card_id + "'";
}
String HQL = "select id, checkplan_id, checkpaper_id, condition, deleted, hasnotified, user_name, card_id, road, unit_name, cus_dom, cus_dy, cus_floor, cus_room, old_address, save_people, repairman from t_inspection where checkplan_id='{0}' and road = '{1}' and unit_name='{2}' and cus_dom='{3}' and cus_dy='{4}' and cus_floor='{5}' and cus_room='{6}' and checkpaper_id='{7}' {8}";
checkList.HQL = String.Format(HQL, new String[] { conditions.GetPropertyValue("CHECKPLANID") as string, go.GetPropertyValue("road").ToString(), go.GetPropertyValue("rarea").ToString(), go.GetPropertyValue("building").ToString(), go.GetPropertyValue("unit").ToString(), go.GetPropertyValue("floor").ToString(), go.GetPropertyValue("room").ToString(), go.GetPropertyValue("paperid").ToString(), rightJoinCriteria });
checkList.Load();
}
}
/// <summary>
/// 处理查询按钮
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
//按下时，组装条件，并加载
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
PagedList paperList = (paperGrid.ItemsSource as PagedList);
paperList.LoadOnPathChanged = false;
//assemble the conditions
String criteria = " where 1=1 ";
String rarea = conditions.GetPropertyValue("UNIT_NAME") as string;
if (!IsNullOrEmpty(rarea))
{
criteria += " and rarea like '%" + rarea + "%'";
}
String building = conditions.GetPropertyValue("CUS_DOM") as string;
if (!IsNullOrEmpty(building))
{
criteria += " and building like '%" + building + "%'";
}
String unit = conditions.GetPropertyValue("CUS_DY") as string;
if (!IsNullOrEmpty(unit))
{
criteria += " and unit like '%" + unit + "%'";
}
String floor = conditions.GetPropertyValue("CUS_FLOOR") as string;
if (!IsNullOrEmpty(floor))
{
criteria += " and floor like '%" + floor + "%'";
}
String room = conditions.GetPropertyValue("CUS_ROOM") as string;
if (!IsNullOrEmpty(room))
{
criteria += " and room like '%" + room + "%'";
}
String rightJoinCriteria = "";
String repairman = conditions.GetPropertyValue("REPAIRMAN") as string;
if (!IsNullOrEmpty(repairman))
{
rightJoinCriteria += " and REPAIRMAN like '%" + repairman + "%'";
}
String SAVE_PEOPLE = conditions.GetPropertyValue("SAVE_PEOPLE") as string;
if (!IsNullOrEmpty(SAVE_PEOPLE))
{
rightJoinCriteria += " and SAVE_PEOPLE like '%" + SAVE_PEOPLE + "%'";
}
String card_id = conditions.GetPropertyValue("CARD_ID") as string;
if (!IsNullOrEmpty(card_id))
{
rightJoinCriteria += " and card_id =" + card_id + "'";
}
String state = (cmbState.SelectedItem as ContentControl).Content.ToString();
if (!IsNullOrEmpty(state))
{
state = " having " + (state.Equals("已检") ? "sum(flag)>0" : (state.Equals("未检") ? "sum(flag)=0" : "sum(flag)<0"));
}
else
{
state = "";
}
if (duplicateInspection.IsChecked.Value)
{
if (state.Length == 0)
{
state = " having sum(flag)>1 ";
}
else
{
state += " and sum(flag)>1 ";
}
}
//如果卡号为空，不用人员查询，全连接
if (rightJoinCriteria.Length == 0)
{
String HQL = @"select sum(abs(flag)) mark, cast(case when sum(flag)=0 then '未检' when sum(flag)>0 then '已检' else '新增' end as varchar(10)) state, paperid paperid, road road, rarea rarea, building building, unit unit, floor floor, room room FROM (select NVL2(pid, NVL2(tid,1, 0), -1) flag, NVL2(pid, pid, paperid) paperid, NVL2(proad, proad, troad) road , NVL2(punit_name, punit_name, tunit_name) rarea, NVL2(pcus_dom, pcus_dom, tcus_dom) building,NVL2(pcus_dy, pcus_dy, tcus_dy) unit, NVL2(pcus_floor, pcus_floor, tcus_floor) floor, NVL2(pcus_room, pcus_room, tcus_room) room from (select t.id tid, p.id pid, t.checkpaper_id paperid, t.road troad, t.unit_name tunit_name, t.cus_dom tcus_dom, t.cus_dy tcus_dy, t.cus_floor tcus_floor, t.cus_room tcus_room,p.road proad, p.unit_name punit_name, p.cus_dom pcus_dom, p.cus_dy pcus_dy, p.cus_floor pcus_floor, p.cus_room pcus_room from (select * from T_IC_SAFECHECK_PAPER where CHECKPLANID='{0}') p full join (select * from T_INSPECTION where checkplan_id='{0}') t on p.id=t.checkpaper_id)) {1} group by paperid, road, rarea, building, unit, floor, room {2} order by road, rarea, length(building), building, length(unit), unit, length(floor), floor, length(room), room";
paperList.HQL = String.Format(HQL, new string[] { conditions.GetPropertyValue("CHECKPLANID") as string, criteria, state });
}
//否则退化成右连接
else
{
String HQL = @"select sum(abs(flag)) mark, cast(case when sum(flag)=0 then '未检' when sum(flag)>0 then '已检' else '新增' end as varchar(10)) state, paperid paperid, road road, rarea rarea, building building, unit unit, floor floor, room room FROM (select NVL2(pid, NVL2(tid,1, 0), -1) flag, NVL2(pid, pid, paperid) paperid, NVL2(proad, proad, troad) road , NVL2(punit_name, punit_name, tunit_name) rarea, NVL2(pcus_dom, pcus_dom, tcus_dom) building,NVL2(pcus_dy, pcus_dy, tcus_dy) unit, NVL2(pcus_floor, pcus_floor, tcus_floor) floor, NVL2(pcus_room, pcus_room, tcus_room) room from (select t.id tid, p.id pid, t.checkpaper_id paperid, t.road troad, t.unit_name tunit_name, t.cus_dom tcus_dom, t.cus_dy tcus_dy, t.cus_floor tcus_floor, t.cus_room tcus_room,p.road proad, p.unit_name punit_name, p.cus_dom pcus_dom, p.cus_dy pcus_dy, p.cus_floor pcus_floor, p.cus_room pcus_room from (select * from T_IC_SAFECHECK_PAPER where CHECKPLANID='{0}') p right join (select * from T_INSPECTION where checkplan_id='{0}' {1} ) t on p.id=t.checkpaper_id)) {2} group by paperid, road, rarea, building, unit, floor, room {3} order by road, rarea, length(building), building, length(unit), unit, length(floor), floor, length(room), room";
paperList.HQL = String.Format(HQL, new string[] { conditions.GetPropertyValue("CHECKPLANID") as string, rightJoinCriteria, criteria, state });
}
paperList.Load();
}