private void Button_Click(object sender, RoutedEventArgs e)
{
if ((userfiles.ItemsSource as PagedList).Count < 1 || CoboxStair.SelectedValue == null || "".Equals(CoboxStair.SelectedValue))
{
MessageBox.Show("请选择要修改用户和阶梯气价");
return;
}
ui_searchBusy.IsBusy = true;
search.Search();
if (!"1=1".Equals(search.Condition.Trim()) && null != search.Condition)
{
if (ui_stairmonths.Text != "" || ui_stair1amount.Text != "" || ui_stair1price.Text != "" || ui_stair2amount.Text != "" || ui_stair2price.Text != "" || ui_stair3amount.Text != "" || ui_stair3price.Text != "" || CoboxStair.SelectedValue != null)
{
//提交更改
string sql = "update t_userinfo set f_stairtype='" + CoboxStair.SelectedValue + "', f_stair1amount=" + ui_stair1amount.Text + " , " +
"f_stair1price=" + ui_stair1price.Text + " , f_stair2amount='" + ui_stair2amount.Text + "', f_stair2price=" + ui_stair2price.Text + ",f_stair3amount='" + ui_stair3amount.Text + "', f_stair3price=" + ui_stair3price.Text + ", f_stair4price=" + ui_stair4price.Text + " ,f_stairmonths=" + ui_stairmonths.Text + " where " + search.Condition + "";
HQLAction action = new HQLAction();
action.HQL = sql;
action.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
action.Name = "t_userfiles";
action.Completed += action_Completed;
action.Invoke();
}
}
else
{
MessageBox.Show("请先按照条件筛选出变更的用户，再确认变更！");
ui_searchBusy.IsBusy = false;
}
}
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();
}
private void dansearchbutton_Click(object sender, RoutedEventArgs e)
{
ui_busys.IsBusy = true;
userSearch.Search();
userList.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
userList.LoadOnPathChanged = false;
string sql = "select f_userid,old,f_meternumber,f_username,f_usertype,f_cardid,f_gaswatchbrand,f_metertype," +
"lastinputgasnum,f_zhye,f_address,f_districtname,f_apartment,f_phone,f_gasproperties,f_gaspricetype,f_gasprice," +
"f_wallhangboiler,f_cumulativepurchase,f_metergasnums,CONVERT(varchar(12), f_beginfee, 111 ) f_beginfee," +
"CONVERT(varchar(12), f_givecarddate, 111 ) f_givecarddate,f_whethergivecard,f_userstate,f_finallybought," +
"CONVERT(varchar(12), f_finabuygasdate, 111 ) f_finabuygasdate,f_anzhuanguser,CONVERT(varchar(12), " +
"f_watchinstalldate, 111 ) f_watchinstalldate,f_payment,f_bankname,f_usermc,f_idofcard,f_filiale,f_yytdepa," +
"f_yytoper,CONVERT(varchar(12), f_yytdate, 111 ) f_yytdate,f_credentials,f_idnumber,f_dibaohu,f_gasmetermanufacturers," +
"f_aroundmeter,f_gasmeteraccomodations,f_aliasname,f_stairtype,f_stair1amount,f_stair1price,f_stair2amount,f_stair2price,f_stair3amount,f_stair3price,f_stairmonths from t_userfiles " +
"where " + userSearch.Condition + " order by id";
userList.LoadOnPathChanged = false;
userList.Path = "sql";
userList.SumHQL = "select f_userid,old,f_meternumber,f_username,f_usertype,f_cardid,f_gaswatchbrand,f_metertype," +
"lastinputgasnum,f_zhye,f_address,f_districtname,f_apartment,f_phone,f_gasproperties,f_gaspricetype,f_gasprice," +
"f_wallhangboiler,f_cumulativepurchase,f_metergasnums,CONVERT(varchar(12), f_beginfee, 111 ) f_beginfee," +
"CONVERT(varchar(12), f_givecarddate, 111 ) f_givecarddate,f_whethergivecard,f_userstate,f_finallybought," +
"CONVERT(varchar(12), f_finabuygasdate, 111 ) f_finabuygasdate,f_anzhuanguser,CONVERT(varchar(12), " +
"f_watchinstalldate, 111 ) f_watchinstalldate,f_payment,f_bankname,f_usermc,f_idofcard,f_filiale,f_yytdepa," +
"f_yytoper,CONVERT(varchar(12), f_yytdate, 111 ) f_yytdate,f_credentials,f_idnumber,f_dibaohu,f_gasmetermanufacturers," +
"f_aroundmeter,f_gasmeteraccomodations,f_aliasname,f_stairtype,f_stair1amount,f_stair1price,f_stair2amount,f_stair2price,f_stair3amount,f_stair3price,f_stairmonths from t_userfiles " +
"where " + userSearch.Condition + "";
userList.HQL = sql;
userList.PageSize = pager2.PageSize;
userList.SumNames = ",";
userList.DataLoaded += setList_DataLoaded;
userList.PageIndex = pageIndex;
userList.Load();
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String dt = " 1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt = " DEPARTURE_TIME>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt.Length > 3)
{
dt += " and DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt = " DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59' ";
}
}
dt = " and " + dt;
checkerList.Path = "sql";
checkerList.Names = "precaution,road,unit_name,cus_dom,cus_dy,cus_floor,cus_room,user_name,telphone,departure_time,precaution_notified";
String sql = @"SELECT CAST (CASE f_heater_trapped WHEN 1 THEN '热水器直排或烟道未排出室外' ELSE '' END AS VARCHAR (50)) + CAST (CASE f_furnace_trapped WHEN 1 THEN '壁挂锅炉直排或烟道未排出室外' ELSE '' END AS VARCHAR (50)) + CAST ( CASE f_precaution_kitchen WHEN 1 THEN '开放式或密闭式厨房' ELSE '' END AS VARCHAR (50)) + CAST (CASE f_precaution_multisource WHEN 1 THEN '多种火源' ELSE '' END AS VARCHAR (50)) + CAST (CASE f_precaution_otheruse WHEN '卧室' THEN '更改用途(卧室)' WHEN '客厅' THEN '更改用途(客厅)' WHEN '浴室' THEN '更改用途(浴室)' ELSE '' END AS VARCHAR (50)) precaution, road,unit_name, cus_dom,cus_dy,cus_floor,cus_room,f_consumername user_name,f_consumerphone telphone,SUBSTRING (DEPARTURE_TIME, 1, 10) departure_time,CAST ( CASE ISNULL(f_renow_id, '') WHEN '' THEN '否' ELSE '是' END AS VARCHAR (50)) precaution_notified FROM T_INSPECTION WHERE 1=1 and {0}";
checkerList.HQL = String.Format(sql, new string[] { conditions.Condition + dt }).Replace("\t", " ").Replace("\n", " ").Replace("\r", " ");
checkerList.Load();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
if (ui_SearchUserList.Count < 1 || CoboxStair.SelectedValue == null || "".Equals(CoboxStair.SelectedValue))
{
MessageBox.Show("请选择要修改用户和阶梯水价");
return;
}
ui_searchBusy.IsBusy = true;
search.Search();
string sql = "update t_userinfo set f_stairtype='" + CoboxStair.SelectedValue + "', f_stair1amount=" + ui_stair1amount.Text + " , " +
"f_stair1price=" + ui_stair1price.Text + " , f_stair2amount='" + ui_stair2amount.Text + "', f_stair2price=" + ui_stair2price.Text + ",f_stair3amount='" + ui_stair3amount.Text + "', f_stair3price=" + ui_stair3price.Text + ", f_stair4price=" + ui_stair4price.Text + ", f_zongjiprice=" + ui_zongjiprice.Text + " ,f_stairmonths=" + ui_stairmonths.Text + " where " + search.Condition + "";
HQLAction action = new HQLAction();
action.HQL = sql;
action.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
action.Name = "t_userfiles";
action.Completed += action_Completed;
action.Invoke();
string sql1 = "update t_userfiles set f_stairtype='" + CoboxStair.SelectedValue + "', f_stair1amount=" + ui_stair1amount.Text + " , " +
"f_stair1price=" + ui_stair1price.Text + " , f_stair2amount='" + ui_stair2amount.Text + "', f_stair2price=" + ui_stair2price.Text + ",f_stair3amount='" + ui_stair3amount.Text + "', f_stair3price=" + ui_stair3price.Text + ", f_stair4price=" + ui_stair4price.Text + ", f_zongjiprice=" + ui_zongjiprice.Text + " ,f_stairmonths=" + ui_stairmonths.Text + " where " + search.Condition + "";
HQLAction action1 = new HQLAction();
action1.HQL = sql1;
action1.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
action1.Name = "t_userfiles";
action1.Invoke();
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String dt = "1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt = " DEPARTURE_TIME>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt.Length > 3)
{
dt += " and DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt = " DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59' ";
}
}
checkerList.LoadOnPathChanged = false;
checkerList.Path = "sql";
checkerList.Names = "id,CONDITION,UNIT_NAME,CUS_DOM,CUS_DY,CUS_FLOOR,CUS_ROOM,USER_NAME,CARD_ID,TELPHONE,SAVE_PEOPLE,DEPARTURE_TIME,ARRIVAL_TIME,RQB_AROUND,REPAIRMAN,CONTENT,sn";
String sql = @"select t.id,t.CONDITION,t.UNIT_NAME,t.CUS_DOM,t.CUS_DY,t.CUS_FLOOR,t.CUS_ROOM,t.USER_NAME,t.CARD_ID,t.TELPHONE,t.SAVE_PEOPLE,t.DEPARTURE_TIME,t.ARRIVAL_TIME,t.RQB_AROUND,t.REPAIRMAN,tt.CONTENT from T_INSPECTION t , T_INSPECTION_LINE tt where t.id=tt.inspection_id
and (t.DELETED is null or t.DELETED <> '是') and tt.EQUIPMENT='燃气表' and tt.CONTENT in ('表不过气', '长通表', '死表') and {0} and {1}";
checkerList.HQL = String.Format(sql, new String[] { conditions.Condition, dt }).Replace("\r\n", " ");
checkerList.Load();
}
private void dansearchbutton_Click(object sender, RoutedEventArgs e)
{
ui_busys.IsBusy = true;
userSearch.Search();
userList.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
userList.LoadOnPathChanged = false;
string sql = "select f_userid,old,f_username,f_usertype,f_districtname,f_apartment,f_idnumber," +
"lastinputgasnum,f_zhye,f_address,f_phone,f_gasproperties,f_stairtype,f_gasprice," +
"f_cumulativepurchase,f_credentials,f_userstate," +
"CONVERT(varchar(12), f_finabuygasdate, 111 ) f_finabuygasdate, " +
"f_payment,f_bankname,f_usermc,f_idofcard,f_filiale,f_yytdepa," +
"f_yytoper,CONVERT(varchar(12), f_yytdate, 111 ) f_yytdate,f_xiangou," +
"f_stair1amount,f_stair1price,f_stair2amount,f_stair2price,f_stair3amount,f_stair3price,f_stairmonths from t_userinfo " +
"where " + userSearch.Condition + " order by id";
userList.LoadOnPathChanged = false;
userList.Path = "sql";
userList.SumHQL = "select f_userid,old,f_username,f_usertype,f_districtname,f_credentials,f_apartment,f_idnumber," +
"lastinputgasnum,f_zhye,f_address,f_phone,f_gasproperties,f_stairtype,f_gasprice,f_cumulativepurchase," +
"CONVERT(varchar(12), f_finabuygasdate, 111 ) f_finabuygasdate,f_userstate," +
"f_payment,f_bankname,f_usermc,f_idofcard,f_filiale,f_yytdepa," +
"f_yytoper,CONVERT(varchar(12), f_yytdate, 111 ) f_yytdate,f_xiangou," +
"f_stair1amount,f_stair1price,f_stair2amount,f_stair2price,f_stair3amount,f_stair3price,f_stairmonths from t_userinfo " +
"where " + userSearch.Condition + "";
userList.HQL = sql;
userList.PageSize = pager2.PageSize;
userList.SumNames = ",";
userList.DataLoaded += setList_DataLoaded;
userList.PageIndex = pageIndex;
userList.Load();
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String where = conditions.Condition;
String dt = "1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt = " DEPARTURE_TIME>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt.Length > 3)
{
dt += " and DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt = " DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
}
checkerList.Path = "sql";
checkerList.Names = "save_people,days,papers,notifications,notins,defects,leakages";
String sql = @"SELECT t0.save_people,days,papers,notifications,notins,defects,leakages FROM(SELECT save_people,SUM (ruhu) papers,COUNT (DISTINCT dt) days,SUM (notin) notins FROM(SELECT id,save_people,SUBSTRING (departure_time, 1, 10) dt,CAST (CASE condition WHEN '正常' THEN 1 ELSE 0 END AS INTEGER) ruhu,CAST (CASE condition WHEN '无人' THEN 1 ELSE 0 END AS INTEGER) notin FROM T_INSPECTION WHERE 1 = 1 AND {0} AND {1}) b GROUP BY save_people) t0 LEFT JOIN (SELECT save_people,COUNT (id) leakages FROM(SELECT id, save_people FROM T_INSPECTION WHERE (f_qblouqi = 1 OR f_jpglouqi = 1 OR f_lglouqi = 1 OR f_cooker_leakage = 1 OR f_heater_leakage = 1 OR f_furnace_leakage = 1 OR f_bhglouqi = 1 OR f_biaoqianfa = '漏气' OR f_zaoqianfa = '漏气' OR f_zibifa = '漏气' OR f_zjxianzhuang = '漏气' OR f_rshqxianzhuang = '漏气' OR f_bglxianzhuang = '漏气') AND {0} AND {1}) a GROUP BY save_people) t1 ON t0.save_people = t1.save_people LEFT JOIN (SELECT save_people,COUNT (id) notifications FROM(SELECT id, save_people FROM T_INSPECTION WHERE f_renow_id IS NOT NULL AND f_renow_id <> '' AND {0} AND {1}) c GROUP BY save_people) t2 ON t0.save_people = t2.save_people LEFT JOIN (SELECT save_people,COUNT (id) defects FROM(SELECT id,save_people FROM T_INSPECTION WHERE (f_sibiao = 1 OR f_changtong = 1 OR f_fanzhuang = 1 OR f_qblouqi = 1 OR f_reading_mismatch = 1 OR (f_qibiao IS NOT NULL AND f_qibiao <> '')) AND {0} AND {1}) d GROUP BY save_people) t3 ON t0.save_people = t3.save_people";
checkerList.HQL = String.Format(sql, new String[] { where, dt });
checkerList.Load();
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String dt = "1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt = " DEPARTURE_TIME>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt.Length > 3)
{
dt += " and DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt = " DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59' ";
}
}
checkerList.LoadOnPathChanged = false;
checkerList.Path = "sql";
checkerList.Names = "id,CONDITION,UNIT_NAME,CUS_DOM,CUS_DY,CUS_FLOOR,CUS_ROOM,USER_NAME,TELPHONE,RQB_AROUND,CONTENT,SAVE_PEOPLE,DEPARTURE_TIME,REPAIRMAN";
String sql = @"SELECT id, CONDITION, UNIT_NAME, CUS_DOM, CUS_DY, CUS_FLOOR, CUS_ROOM, f_consumername USER_NAME, f_consumerphone TELPHONE, f_rqbiaoxing RQB_AROUND, CAST(CASE f_sibiao WHEN 1 THEN '死表' ELSE '' END AS VARCHAR (50)) + CAST(CASE f_changtong WHEN 1 THEN '常通' ELSE '' END AS VARCHAR (50)) + CAST(CASE f_fanzhuang WHEN 1 THEN '反装' ELSE '' END AS VARCHAR (50)) + CAST(CASE f_qblouqi WHEN 1 THEN '漏气' ELSE '' END AS VARCHAR (50)) + CAST(CASE f_reading_mismatch WHEN 1 THEN '气量不符' ELSE '' END AS VARCHAR (50)) + CAST(CASE f_qbqita WHEN 1 THEN f_qibiao ELSE '' END AS VARCHAR (50)) CONTENT, SAVE_PEOPLE, DEPARTURE_TIME, REPAIRMAN FROM T_INSPECTION WHERE 1=1 AND (f_sibiao = 1 OR f_changtong = 1 OR f_fanzhuang = 1 OR f_qblouqi = 1 OR f_reading_mismatch = 1 OR f_qbqita = 1) AND {0} AND {1}";
checkerList.HQL = String.Format(sql, new String[] { conditions.Condition, dt }).Replace("\r", " ").Replace("\t", " ").Replace("\n", " ");
checkerList.Load();
}
private void dansearchbutton_Click(object sender, RoutedEventArgs e)
{
ui_busys.IsBusy = true;
userSearch.Search();
userList.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
userList.LoadOnPathChanged = false;
string sql = "select f_userinfoid,f_userid,f_meternumber,f_username,f_usertype,f_gaswatchbrand,f_metertype," +
"lastinputgasnum,f_phone,f_gasproperties,f_stairtype,f_yytdepa," +
"f_metergasnums,f_metertitles,f_gasmeterstyle,f_gasmeteraccomodations," +
"f_userstate,f_finallybought,f_weizhi,f_cumulativepurchase,f_filiale,f_yytoper," +
"CONVERT(varchar(12), f_finabuygasdate, 111 ) f_finabuygasdate," +
"CONVERT(varchar(12), f_yytdate, 111 ) f_yytdate,f_gasmetermanufacturers," +
"f_aliasname from t_userfiles " +
"where " + userSearch.Condition + " order by id";
userList.LoadOnPathChanged = false;
userList.Path = "sql";
userList.SumHQL = "select f_userinfoid,f_userid,f_meternumber,f_username,f_usertype,f_gaswatchbrand,f_metertype," +
"lastinputgasnum,f_phone,f_gasproperties,f_stairtype,f_filiale,f_yytdepa," +
"f_metergasnums,f_metertitles,f_gasmeterstyle,f_gasmeteraccomodations," +
"f_userstate,f_finallybought,f_weizhi,f_cumulativepurchase,f_yytoper," +
"CONVERT(varchar(12), f_finabuygasdate, 111 ) f_finabuygasdate,f_anzhuanguser," +
"CONVERT(varchar(12), f_yytdate, 111 ) f_yytdate,f_gasmetermanufacturers," +
"f_aliasname from t_userfiles " +
"where " + userSearch.Condition + "";
userList.HQL = sql;
userList.PageSize = pager2.PageSize;
userList.SumNames = ",";
userList.DataLoaded += setList_DataLoaded;
userList.PageIndex = pageIndex;
userList.Load();
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String where = conditions.Condition;
String dt = "1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt = " DEPARTURE_TIME>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt.Length > 3)
{
dt += " and DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt = " DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
}
String dt2 = "1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt2 = " SAVE_DATE>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt2.Length > 3)
{
dt2 += " and SAVE_DATE<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt2 = " SAVE_DATE<='" + EndDate.Text + " 23:59:59'";
}
}
checkerList.Path = "sql";
checkerList.Names = "save_people,papers,days,notifications,notins,leakages,defects";
String sql = @"SELECT t0.save_people, papers, days, notifications,notins, leakages, defects from
(select save_people, sum(ruhu) papers, count(DISTINCT dt) days, sum(notin) notins from (
select id, save_people, substring(departure_time, 1, 10) dt,cast(case condition when '正常' then 1 else 0 end as INTEGER) ruhu, cast(case condition when '无人' then 1 else 0 end as INTEGER) notin from T_INSPECTION where (deleted is null or deleted!='是') and {0} and {1} ) b group by save_people) t0 left join (select save_people, count(save_people) leakages FROM(
select save_people from T_INSPECTION_LINE where inspection_id is not null and content like '%漏气%' and {2} and {3} ) a group by save_people) t1 on t0.save_people = t1.save_people left join
(select save_people, count(DISTINCT inspection_id) defects FROM(
select inspection_id,save_people from T_INSPECTION_LINE where inspection_id is not null and equipment = '燃气表' and content in('长通表', '死表', '表不过气', '其他') and {4} and {5} ) c group by save_people) t2 on t1.save_people=t2.save_people left join (select save_people, count(save_people) notifications FROM(
select save_people from T_INSPECTION_LINE where inspection_id is not null and content = '已发近期安检报告书' and {6} and {7} ) d group by save_people) t3 on t2.save_people = t3.save_people";
checkerList.HQL = String.Format(sql, new String[] { where, dt, where, dt2, where, dt2, where, dt2 });
checkerList.Load();
}
private void ui_StairSearchButton_Click(object sender, RoutedEventArgs e)
{
ui_searchBusy.IsBusy = true;
stairSearch.Search();
string sql = "from t_stairprice where " + stairSearch.Condition;
stairList.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
stairList.LoadOnPathChanged = false;
stairList.Path = "hql";
stairList.HQL = sql;
stairList.PageSize = 100;
stairList.DataLoaded += stairList_DataLoaded;
stairList.PageIndex = 0;
}
private void Button_Click(object sender, RoutedEventArgs e)
{
ui_searchBusy.IsBusy = true;
search.Search();
string sql = "update t_repairsys set f_downloadstatus=NULL,f_accepter='" + f_accepter.SelectedValue + "' where " + search.Condition + "";
HQLAction action = new HQLAction();
action.HQL = sql;
action.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
action.Name = "t_repairsys";
action.Completed += action_Completed;
action.Invoke();
}
private void searchbutton_Click(object sender, RoutedEventArgs e)
{
SearchObject conditions = (applysearch.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String where = conditions.Condition;
checkerList.Path = "sql";
checkerList.Names = "f_cusDom,f_cusDy,f_cusFloor,f_apartment";
String sql = @"select f_cusDom,f_cusDy,f_cusFloor,f_apartment from t_gasaddress where {0} order by length(f_cusDom),f_cusDom,length(f_cusDy),f_cusDy,length(f_cusFloor),f_cusFloor,length(f_apartment),f_apartment";
checkerList.HQL = String.Format(sql, where);
checkerList.Load();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
ui_searchBusy.IsBusy = true;
search.Search();
string sql = "update t_userfiles set f_stairtype='" + CoboxStair.SelectedValue + "', f_stair1amount=" + ui_stair1amount.Text + " , " +
"f_stair1price=" + ui_stair1price.Text + " , f_stair2amount='" + ui_stair2amount.Text + "', f_stair2price=" + ui_stair2price.Text + ",f_stair3amount='" + ui_stair3amount.Text + "', f_stair3price=" + ui_stair3price.Text + ", f_stair4price=" + ui_stair4price.Text + " ,f_stairmonths=" + ui_stairmonths.Text + " where " + search.Condition + "";
HQLAction action = new HQLAction();
action.HQL = sql;
action.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
action.Name = "t_userfiles";
action.Completed += action_Completed;
action.Invoke();
}
private void so_heji(object sender, RoutedEventArgs e)
{
SearchObject so = daninfosearch.DataContext as SearchObject;
PagedList hejilists = (PagedList)daninfos.ItemsSource;
so.Search();
String sql = "" +
//小区，安检情况
" count(t1.id) hj, t1.f_meetunit f_meetunit,t1.f_accepter f_accepter,count(t2.online) online,count(t3.online_zyj) online_zyj,count(h1.ywc) ywc,count(h2.dwc) dwc,count(h3.wwc) wwc,count(f1.fwmy) fwmy,count(f2.fwbmy) fwbmy,count(z1.zlmy) zlmy,count(z2.zlbmy) zlbmy" +
" FROM t_repairsys t1" +
" LEFT JOIN " +
" (select id,count(f_dealtype) online from t_repairsys where f_dealtype ='电话处理' group by id) t2" +
" ON t1.id=t2.id" +
" LEFT JOIN" +
" (select id,count(f_dealtype) online_zyj from t_repairsys where f_dealtype ='转交维修' group by id) t3" +
" ON t1.id=t3.id" +
" LEFT JOIN" +
" (select id,count(f_havacomplete) ywc from t_repairsys where f_havacomplete ='已完成' group by id) h1" +
" ON t1.id=h1.id" +
" LEFT JOIN" +
" (select id,count(f_havacomplete) dwc from t_repairsys where f_havacomplete ='待完成' group by id) h2" +
" ON t1.id=h2.id" +
" LEFT JOIN " +
" (select id,count(f_havacomplete) wwc from t_repairsys where f_havacomplete ='未完成' group by id) h3" +
" ON t1.id=h3.id" +
" LEFT JOIN " +
" (select id,count(f_fwtdmiqk) fwmy from t_repairsys where f_fwtdmiqk ='满意' group by id) f1" +
" ON t1.id=f1.id" +
" LEFT JOIN " +
" (select id,count(f_fwtdmiqk) fwbmy from t_repairsys where f_fwtdmiqk ='不满意' group by id) f2" +
" ON t1.id=f2.id" +
" LEFT JOIN " +
" (select id,count(f_zlmiqk) zlmy from t_repairsys where f_zlmiqk ='满意' group by id) z1" +
" ON t1.id=z1.id" +
" LEFT JOIN " +
" (select id,count(f_zlmiqk) zlbmy from t_repairsys where f_zlmiqk ='不满意' group by id) z2" +
" ON t1.id=z2.id ";
hejilists.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
hejilists.LoadOnPathChanged = false;
hejilists.Path = "sql";
hejilists.SumNames = "hj,";
hejilists.PageSize = 1000;
hejilists.HQL = "select " + sql + " where " + so.Condition + " group by t1.f_meetunit, t1.f_accepter order by t1.f_meetunit";
hejilists.PageIndex = 0;
hejilists.DataLoaded += hejilists_DataLoaded;
//hejilists.LoadDetail();
}
// 查询按钮处理过程
private void dansearchbutton_Click(object sender, RoutedEventArgs e)
{
ui_handBusy.IsBusy = true;
// 掉用search对象的search方法，产生条件
SearchObject search = daninfosearch.DataContext as SearchObject;
search.Search();
// 调用服务
WebClientInfo wci = Application.Current.Resources["server"] as WebClientInfo;
string uri = wci.BaseAddress + "/handcharge/download" + "?uuid=" + System.Guid.NewGuid().ToString();
WebClient client = new WebClient();
client.UploadStringCompleted += dansearch_UploadStringCompleted;
client.UploadStringAsync(new Uri(uri), search.Condition);
}
private void ui_SearchButton_Click(object sender, RoutedEventArgs e)
{
ui_userBusy.IsBusy = true;
userSearch.Search();
// 生成sql语句
sql = " from t_changmeter where " + userSearch.Condition + " order by id";
userList.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
userList.LoadOnPathChanged = false;
userList.Path = "hql";
userList.SumHQL = " from t_changmeter where " + userSearch.Condition;
userList.HQL = sql;
userList.PageSize = ui_pager.PageSize;
userList.SumNames = "id";
userList.PageIndex = pageIndex;
userList.Load();
}
// 查询按钮处理过程
private void dansearchbutton_Click(object sender, RoutedEventArgs e)
{
ui_handBusy.IsBusy = true;
// 掉用search对象的search方法，产生条件
SearchObject search = daninfosearch.DataContext as SearchObject;
search.Search();
// 调用服务
GeneralObject loginuser = (GeneralObject)FrameworkElementExtension.FindResource(this.saveButton, "LoginUser");
string fengongsi = loginuser.GetPropertyValue("f_fengongsi").ToString();
WebClientInfo wci = Application.Current.Resources["server"] as WebClientInfo;
string uri = wci.BaseAddress + "/handcharge/download/" + fengongsi + "?uuid=" + System.Guid.NewGuid().ToString();
WebClient client = new WebClient();
client.UploadStringCompleted += dansearch_UploadStringCompleted;
client.UploadStringAsync(new Uri(uri), search.Condition);
}
private void searchbutton_Click(object sender, RoutedEventArgs e)
{
ui_userBusy.IsBusy = true;
userSearch.Search();
// 生成sql语句
string sql = "select * from t_changmeter where " + userSearch.Condition + " and f_payfeevalid!='无效' and f_state='换表销账' and f_dengjistate='申请已处理' order by id";
userList.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
userList.LoadOnPathChanged = false;
userList.Path = "sql";
userList.SumHQL = "select * from t_changmeter where " + userSearch.Condition + " and f_payfeevalid!='无效' and f_state='换表销账' and f_dengjistate='申请已处理'";
userList.HQL = sql;
userList.PageSize = ui_pager.PageSize;
userList.SumNames = "id";
userList.PageIndex = pageIndex;
userList.Load();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
ui_searchBusy.IsBusy = true;
search.Search();
if (!"1=1".Equals(search.Condition.Trim()) && null != search.Condition)
{
//提交更改
string sql = "update t_userfiles set f_operate_zl='" + f_operate_zl_zl.SelectedValue + "', f_returnvalueoperate=" + "'1' " +
" where f_gasmeterstyle in ('物联网表','物联表','积成远传气表','远传表','短信表') and " + search.Condition + "";
HQLAction action = new HQLAction();
action.HQL = sql;
action.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
action.Name = "t_userfiles";
action.Completed += action_Completed;
action.Invoke();
}
else
{
MessageBox.Show("请先按照条件筛选出进行阀门操作的用户，再确认执行！");
ui_searchBusy.IsBusy = false;
}
}
private void search_Click(object sender, RoutedEventArgs e)
{
SearchObject criteria = (applysearch.DataContext as SearchObject);
criteria.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String where = criteria.Condition;
String dt = districtname.Text;
String state = " 1=1 ";
if ((cmbState.SelectedItem as ContentControl).Content.ToString().Length > 0)
{
state = " state='" + (cmbState.SelectedItem as ContentControl).Content + "'";
}
applyslist.LoadOnPathChanged = false;
applyslist.Path = "sql";
applyslist.Names = "state,road,districtname,cusDy,cusDom,cusFloor,apartment,placeholder";
String sql = @"select * from (select state,road,districtname,cusDy,cusDom,cusFloor,apartment from (
select case when a.f_districtname is null then '新增' when b.f_districtname is null then '已建' else '重复 ' end state,
isnull( a.f_road, b.f_road) road,
isnull( a.f_districtname, b.f_districtname) districtname,
isnull(a.f_cusDy, b.f_cusDy) cusDy,
isnull(a.f_cusDom, b.f_cusDom) cusDom,
isnull(a.f_cusFloor, b.f_cusFloor) cusFloor,
isnull( a.f_apartment, b.f_apartment) apartment
from
(select f_road, f_districtname, f_cusDy, f_cusDom, f_cusFloor, f_apartment from T_GASADDRESS where {0} and f_districtname='{1}') a
full join
(select f_road, f_districtname, f_cusDy, f_cusDom, f_cusFloor, f_apartment from T_GASADDRESSTEMP where {0}) b
on a.f_road = b.f_road and a.f_districtname = b.f_districtname and a.f_cusDy=b.f_cusDy and a.f_cusDom=b.f_cusDom and a.f_cusFloor = b.f_cusFloor and a.f_apartment=b.f_apartment) t
) tt where {2} ";
String orderBy = " order by len(road), road, len(districtname), districtname,len(cusDom), cusDom,len(cusDy), cusDy,len(cusFloor), cusFloor,len(apartment), apartment";
applyslist.SumHQL = String.Format(sql, new String[] { where, dt, state }).Replace("\r\n", " ");
applyslist.HQL = (applyslist.SumHQL + orderBy).Replace("\r\n", " ");
applyslist.Load();
}
private void dansearchbutton_Click(object sender, System.Windows.RoutedEventArgs e)
{
searchbusy.IsBusy = true;
search.Search();
// TODO: Add event handler implementation here.
string sql = "select u.f_userid,u.f_username,u.f_cardid,u.f_gaswatchbrand,u.f_metertype,u.f_address,u.f_districtname,u.f_apartment " +
",u.f_phone,u.f_cumulativepurchase,u.f_metergasnums,CONVERT(varchar(100),u.f_dateofopening,23) f_dateofopening, CONVERT(varchar(100),d.f_deliverydate,23) f_deliverydate,d.f_pregas from (" +
"select s.f_userid,t.f_deliverydate,t.f_pregas from" +
"(select MAX(id) m,f_userid from t_sellinggas where f_payfeevalid = '有效' group by f_userid)s left join t_sellinggas t on s.m = t.id)d left join t_userfiles u on d.f_userid = u.f_userid where f_gasmeterstyle = '卡表' and " + search.Condition + " order by u.f_userid";
list.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
list.LoadOnPathChanged = false;
list.Path = "sql";
list.HQL = sql;
list.SumHQL = "select u.f_userid,u.f_username,u.f_cardid,u.f_gaswatchbrand,u.f_metertype,u.f_address,u.f_districtname,u.f_apartment " +
",u.f_phone,u.f_cumulativepurchase,u.f_metergasnums,u.f_dateofopening,d.f_deliverydate,d.f_pregas from (" +
"select s.f_userid,t.f_deliverydate,t.f_pregas from" +
"(select MAX(id) m,f_userid from t_sellinggas where f_payfeevalid = '有效' group by f_userid)s left join t_sellinggas t on s.m = t.id)d left join t_userfiles u on d.f_userid = u.f_userid where f_gasmeterstyle = '卡表' and " + search.Condition + "";
list.SumNames = ",";
list.PageSize = pager.PageSize;
list.PageIndex = PageIndex;
list.Load();
}
private void btnSearchCore_Click(object sender, EventArgs e)
{
SearchObject.Search();
}
private void btnSearch_Click(object sender, RoutedEventArgs e)
{
/*SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
* conditions.Search();
* WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
* WebClient wc = new WebClient();
* String precaution = "";
* if(cmbPrecaution.SelectedValue.ToString().Length>0)
* precaution = " where precaution ='" + cmbPrecaution.SelectedValue + "'";
* String dt = " and departure_time >='" + StartDate.Text + "' and departure_time <= '" + EndDate.Text + " 23:59:59' ";
* String HQL = @"select precaution, road, unit_name, cus_dom, cus_dy, cus_floor, cus_room, user_name, telphone, departure_time, precaution_notified, cast(row_number() over (partition by precaution order by precaution) as INTEGER) sn from
* (select
* cast(case content when '燃气设备安装在卧室' then '燃气设施安装在卧室'
* when '燃气设施安装在卧室/卫生间' then '燃气设施安装在卧室'
* when '安装在卧室' then '热水器或壁挂炉安装在卧室'
* when '壁挂炉安装在卧室' then '热水器或壁挂炉安装在卧室'
* when '直排热水器' then '使用直排式热水器'
* else '热水器未安装烟道或烟道未接到室外' end as VARCHAR(50)) precaution,
* t.ROAD,t.UNIT_NAME, t.CUS_DOM, t.CUS_DY, t.CUS_FLOOR,t.CUS_ROOM, t.USER_NAME, t.TELPHONE, SUBSTRING(t.DEPARTURE_TIME,1,10) DEPARTURE_TIME, NVL(t.PRECAUTION_NOTIFIED,'') precaution_notified
* from T_INSPECTION t join T_INSPECTION_LINE tl on t.id = tl.INSPECTION_ID where
* CONTENT in ('燃气设备安装在卧室','燃气设施安装在卧室/卫生间','壁挂炉安装在卧室','安装在卧室','未安装到烟道', '烟道未排到室外', '直排热水器') and (t.DELETED is null or t.DELETED <> '是') and {0}
* ) {1} order by precaution, road, unit_name, length(cus_dom), cus_dom, length(cus_dy), cus_dy, length(cus_floor), cus_floor, length(cus_room), cus_room ";
* HQL = String.Format(HQL, new string[]{conditions.Condition + dt, precaution }).Replace("\r\n", " ").Replace("\n", " ").Replace("\r", " ");
* wc.UploadStringCompleted += wc_UploadStringCompleted;
* wc.UploadStringAsync(new Uri(wci.BaseAddress + "/sql/precaution,road,unit_name,cus_dom,cus_dy,cus_floor,cus_room,user_name,telphone,departure_time,precaution_notified,sn/0/999999999"), "POST", HQL);*/
SearchObject conditions = (criteriaPanel.DataContext as SearchObject);
conditions.Search();
WebClientInfo wci = App.Current.Resources["dbclient"] as WebClientInfo;
String precaution = "";
if (cmbPrecaution.SelectedValue.ToString().Length > 0)
{
precaution = " where precaution ='" + cmbPrecaution.SelectedValue + "'";
}
String dt = " 1=1";
if (StartDate.Text.Trim().Length != 0)
{
dt = " DEPARTURE_TIME>='" + StartDate.Text + "'";
}
if (EndDate.Text.Trim().Length != 0)
{
if (dt.Length > 3)
{
dt += " and DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59'";
}
else
{
dt = " DEPARTURE_TIME<='" + EndDate.Text + " 23:59:59' ";
}
}
dt = " and " + dt;
checkerList.Path = "sql";
checkerList.Names = "precaution,road,unit_name,cus_dom,cus_dy,cus_floor,cus_room,user_name,telphone,departure_time,precaution_notified,sn";
String sql = @"select precaution, road, unit_name, cus_dom, cus_dy, cus_floor, cus_room, user_name, telphone, departure_time, precaution_notified, cast(row_number() over (partition by precaution order by precaution) as INTEGER) sn from
(select
cast(case content when '燃气设备安装在卧室' then '燃气设施安装在卧室'
when '燃气设施安装在卧室/卫生间' then '燃气设施安装在卧室'
when '安装在卧室' then '热水器或壁挂炉安装在卧室'
when '壁挂炉安装在卧室' then '热水器或壁挂炉安装在卧室'
when '直排热水器' then '使用直排式热水器'
else '热水器未安装烟道或烟道未接到室外' end as VARCHAR(50)) precaution,
t.ROAD,t.UNIT_NAME, t.CUS_DOM, t.CUS_DY, t.CUS_FLOOR,t.CUS_ROOM, t.USER_NAME, t.TELPHONE, SUBSTRING(t.DEPARTURE_TIME,1,10) DEPARTURE_TIME, isnull(t.PRECAUTION_NOTIFIED,'') precaution_notified
from T_INSPECTION t join T_INSPECTION_LINE tl on t.id = tl.INSPECTION_ID where
CONTENT in ('燃气设备安装在卧室','燃气设施安装在卧室/卫生间','壁挂炉安装在卧室','安装在卧室','未安装到烟道', '烟道未排到室外', '直排热水器') and (t.DELETED is null or t.DELETED <> '是') and {0}
) o {1} order by precaution, road, unit_name, len(cus_dom), cus_dom, len(cus_dy), cus_dy, len(cus_floor), cus_floor, len(cus_room), cus_room ";
checkerList.HQL = String.Format(sql, new string[] { conditions.Condition + dt, precaution }).Replace("\r\n", " ").Replace("\n", " ").Replace("\r", " ");
checkerList.Load();
}
private void so_heji(object sender, RoutedEventArgs e)
{
SearchObject so = areafilesearch.DataContext as SearchObject;
so.Search();
String sql = " select " +
//小区，安检情况
" count(DISTINCT UNIT_NAME) xiaoqs,COUNT(t1.id) yonghs, COUNT(zhuangt1.id) yiaj，COUNT(zhuangt2.id) wuren，COUNT(zhuangt3.id) jujian," +
//设备用途
" (COUNT(sb2.id)+COUNT(sb3.id)+COUNT(sb5.id)+COUNT(sb6.id)) sbhj,COUNT(sb1.id) issb,COUNT(sb2.id) sbdj,COUNT(sb3.id) sbrsq,COUNT(sb5.id) sbbg,COUNT(sb6.id) sbqn," +
//供暖方式
" (COUNT(gn2.id)+COUNT(gn3.id)+COUNT(gn5.id)+COUNT(gn6.id)) gnhj,COUNT(gn1.id) isgn,COUNT(gn2.id) guablgn,COUNT(gn3.id) jizhgn,COUNT(gn5.id) qunqgn,COUNT(gn6.id) qit," +
//重点户
" (COUNT(zd2.id)+COUNT(zd3.id)+COUNT(zd5.id)+COUNT(zd6.id)) zdhj,COUNT(zd1.id) iszd,COUNT(zd2.id) qilcr,COUNT(zd3.id) zhongdyh,COUNT(zd5.id) guglr,COUNT(zd6.id) buszdh," +
//小区性质
" (COUNT(xqxz2.id)+COUNT(xqxz3.id)+COUNT(xqxz5.id)+COUNT(xqxz6.id)) xqxzhj,COUNT(xqxz1.id) isxqxz,COUNT(xqxz2.id) anzhxq,COUNT(xqxz3.id) qishiydw,COUNT(xqxz5.id) wuyxq,COUNT(xqxz6.id) zijf," +
//租住
" (COUNT(zzh2.id)+COUNT(zzh3.id)+COUNT(zzh5.id)+COUNT(zzh6.id)+COUNT(zzh7.id)+COUNT(zzh8.id)) zzhhj,COUNT(zzh1.id) iszzh,COUNT(zzh2.id) caiy,COUNT(zzh3.id) putjz,COUNT(zzh5.id) tuogpx,COUNT(zzh6.id) bang,COUNT(zzh7.id) jitss,COUNT(zzh8.id) fou," +
//报警器
" COUNT (CASE WHEN f_baojingqi='有' THEN 1 ELSE NULL END) baojq,count(CASE WHEN (CASE WHEN f_alarm_expire_time is not null THEN substring(CONVERT(varchar(50),GETDATE(),120),1,7)- substring(CONVERT(varchar(50),f_alarm_expire_time,120),1,7) ELSE NULL END)>0 THEN 1 ELSE NULL END) baojqdq," +
//热水器
" count(CASE WHEN f_rshqshiyong=0 THEN 1 ELSE NULL END) resq,count(CASE WHEN (CASE WHEN f_heater_expire_time is not null THEN substring(CONVERT(varchar(50),GETDATE(),120),1,7)- substring(CONVERT(varchar(50),f_heater_expire_time,120),1,7) ELSE NULL END)>0 THEN 1 ELSE NULL END) resqdq," +
//热水器隐患
" (count(CASE WHEN f_heater_overdue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_heater_softconnector=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_heater_trapped=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_heater_emit=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_heater_leakage=1 THEN 1 ELSE NULL END)) resqyh,count(CASE WHEN f_heater_overdue=1 THEN 1 ELSE NULL END) resqcq,count(CASE WHEN f_heater_softconnector=1 THEN 1 ELSE NULL END) resqrglj, count(CASE WHEN f_heater_trapped=1 THEN 1 ELSE NULL END) resqwpsw,count(CASE WHEN f_heater_emit=1 THEN 1 ELSE NULL END) resqzp, count(CASE WHEN f_heater_leakage=1 THEN 1 ELSE NULL END) resqlq," +
//灶具
" count(CASE WHEN f_zjshiyong=0 THEN 1 ELSE NULL END) zaoj,count(CASE WHEN (CASE WHEN f_cooker_expire_time is not null THEN substring(CONVERT(varchar(50),GETDATE(),120),1,7)- substring(CONVERT(varchar(50),f_cooker_expire_time,120),1,7) ELSE NULL END)>0 THEN 1 ELSE NULL END) zaojdq," +
//灶具隐患
" (count(CASE WHEN f_cooker_overdue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_cooker_nofireprotection=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_cooker_leakage=1 THEN 1 ELSE NULL END)) zaojyh,count(CASE WHEN f_cooker_overdue=1 THEN 1 ELSE NULL END) zaojcqsy,count(CASE WHEN f_cooker_nofireprotection=1 THEN 1 ELSE NULL END) zaojwbh, count(CASE WHEN f_cooker_leakage=1 THEN 1 ELSE NULL END) zaojlq," +
//挂壁炉
" count(CASE WHEN f_bglshiyong=0 THEN 1 ELSE NULL END) guabz,count(CASE WHEN (CASE WHEN f_furnace_expire_time is not null THEN substring(CONVERT(varchar(50),GETDATE(),120),1,7)- substring(CONVERT(varchar(50),f_furnace_expire_time,120),1,7) ELSE NULL END)>0 THEN 1 ELSE NULL END) guabzdq," +
//挂壁炉隐患
" (count(CASE WHEN f_furnace_overdue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_furnace_softconnector=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_furnace_emit=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_furnace_trapped=1 THEN 1 ELSE NULL END) +count(CASE WHEN f_furnace_leakage=1 THEN 1 ELSE NULL END)) guabzyh,count(CASE WHEN f_furnace_overdue=1 THEN 1 ELSE NULL END) guabzcqsy,count(CASE WHEN f_furnace_softconnector=1 THEN 1 ELSE NULL END) guabzrglj,count(CASE WHEN f_furnace_emit=1 THEN 1 ELSE NULL END) guabzzp,count(CASE WHEN f_furnace_trapped=1 THEN 1 ELSE NULL END) guabzwpsw,count(CASE WHEN f_furnace_leakage=1 THEN 1 ELSE NULL END) guabzlq," +
//立管隐患
" (count(CASE WHEN f_lgbaoguo=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgguawu=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lghuoyuan=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgweiguding=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgchuanyue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgbubianweixiu=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgfushi=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgsigai=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lglouqi=1 THEN 1 ELSE NULL END)) lgyh,count(CASE WHEN f_lgbaoguo=1 THEN 1 ELSE NULL END) lgbg,count(CASE WHEN f_lgguawu=1 THEN 1 ELSE NULL END) lggw,count(CASE WHEN f_lghuoyuan=1 THEN 1 ELSE NULL END) lghyj,count(CASE WHEN f_lgweiguding=1 THEN 1 ELSE NULL END) lgwgd,count(CASE WHEN f_lgchuanyue=1 THEN 1 ELSE NULL END) lgcyjq,count(CASE WHEN f_lgbubianweixiu=1 THEN 1 ELSE NULL END) lgbbwx,count(CASE WHEN f_lgfushi=1 THEN 1 ELSE NULL END) lgfs,count(CASE WHEN f_lgsigai=1 THEN 1 ELSE NULL END) lgsg,count(CASE WHEN f_lglouqi=1 THEN 1 ELSE NULL END) lglq," +
//表后管隐患
" (count(CASE WHEN f_bhgbaoguan=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_bhglouqi=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_bhgdianyuan=1 THEN 1 ELSE NULL END)) bhgyh,count(CASE WHEN f_bhgbaoguan=1 THEN 1 ELSE NULL END) bhgbg,count(CASE WHEN f_bhglouqi=1 THEN 1 ELSE NULL END) bhglq,count(CASE WHEN f_bhgdianyuan=1 THEN 1 ELSE NULL END) bhgdyj, " +
//表前阀隐患
" count(CASE WHEN f_biaoqianfa='漏气' THEN 1 ELSE NULL END)+count(CASE WHEN f_biaoqianfa='失效' THEN 1 ELSE NULL END) bqfyh,count(CASE WHEN f_biaoqianfa='漏气' THEN 1 ELSE NULL END) bqflq，count(CASE WHEN f_biaoqianfa='失效' THEN 1 ELSE NULL END) bqfsx," +
//灶前阀隐患
" count(CASE WHEN f_zaoqianfa='漏气' THEN 1 ELSE NULL END)+count(CASE WHEN f_zaoqianfa='失效' THEN 1 ELSE NULL END) zqfyh,count(CASE WHEN f_zaoqianfa='漏气' THEN 1 ELSE NULL END) zqflq，count(CASE WHEN f_zaoqianfa='失效' THEN 1 ELSE NULL END) zqfsx," +
//自闭阀隐患
" count(CASE WHEN f_zibifa='漏气' THEN 1 ELSE NULL END)+count(CASE WHEN f_zibifa='失效' THEN 1 ELSE NULL END) zbfyh,count(CASE WHEN f_zibifa='漏气' THEN 1 ELSE NULL END) zbflq，count(CASE WHEN f_zibifa='失效' THEN 1 ELSE NULL END) zbfsx," +
//其他隐患
" (count(CASE WHEN f_precaution_kitchen=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_precaution_openkitchen=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_precaution_multisource=1 THEN 1 ELSE NULL END)+count(f_precaution_otheruse)) qtyh, count(CASE WHEN f_precaution_kitchen=1 THEN 1 ELSE NULL END) qtfbcf,count(CASE WHEN f_precaution_openkitchen=1 THEN 1 ELSE NULL END) qtkfs,count(CASE WHEN f_precaution_multisource=1 THEN 1 ELSE NULL END) qtdzhy,count(f_precaution_otheruse) qtggyt," +
//是否需要维修
" count(CASE WHEN NEEDS_REPAIR='是' THEN 1 ELSE NULL END) NEEDS_REPAIR," +
//是否维修
" count(CASE WHEN REPAIR_STATE='未维修' THEN 1 ELSE NULL END) REPAIR_STATE,count(f_renow_id) f_renow_id," +
//是否换表
" count(CASE WHEN f_newmeter=1 THEN 1 ELSE NULL END) rqbhb," +
//燃气表故障
" (count(CASE WHEN f_sibiao=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_changtong=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_fanzhuang=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_qblouqi=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_reading_mismatch=1 THEN 1 ELSE NULL END)) rqbgz, count(CASE WHEN f_sibiao=1 THEN 1 ELSE NULL END) rqbsb, count(CASE WHEN f_changtong=1 THEN 1 ELSE NULL END) rqbct, count(CASE WHEN f_fanzhuang=1 THEN 1 ELSE NULL END) rqbfz, count(CASE WHEN f_qblouqi=1 THEN 1 ELSE NULL END) rqblq, count(CASE WHEN f_reading_mismatch=1 THEN 1 ELSE NULL END) rqbds," +
//燃气表隐患
" (count(CASE WHEN f_meter_wrapped=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_meter_hanger=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_meter_nearfire=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_meter_unfavorable=1 THEN 1 ELSE NULL END)) rqbyh, count(CASE WHEN f_meter_wrapped=1 THEN 1 ELSE NULL END) rqbbg, count(CASE WHEN f_meter_hanger=1 THEN 1 ELSE NULL END) rqbgw, count(CASE WHEN f_meter_nearfire=1 THEN 1 ELSE NULL END) rqbjhy, count(CASE WHEN f_meter_unfavorable=1 THEN 1 ELSE NULL END) rqbbbwx," +
//各项隐患合计
" （(count(CASE WHEN f_heater_overdue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_heater_softconnector=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_heater_trapped=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_heater_emit=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_heater_leakage=1 THEN 1 ELSE NULL END))" +
"+(count(CASE WHEN f_cooker_overdue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_cooker_nofireprotection=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_cooker_leakage=1 THEN 1 ELSE NULL END))" +
"+(count(CASE WHEN f_furnace_overdue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_furnace_softconnector=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_furnace_emit=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_furnace_trapped=1 THEN 1 ELSE NULL END) +count(CASE WHEN f_furnace_leakage=1 THEN 1 ELSE NULL END))" +
"+(count(CASE WHEN f_lgbaoguo=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgguawu=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lghuoyuan=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgweiguding=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgchuanyue=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgbubianweixiu=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgfushi=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lgsigai=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_lglouqi=1 THEN 1 ELSE NULL END))" +
"+(count(CASE WHEN f_bhgbaoguan=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_bhglouqi=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_bhgdianyuan=1 THEN 1 ELSE NULL END))" +
"+count(CASE WHEN f_biaoqianfa='漏气' THEN 1 ELSE NULL END)+count(CASE WHEN f_biaoqianfa='失效' THEN 1 ELSE NULL END)" +
"+count(CASE WHEN f_zaoqianfa='漏气' THEN 1 ELSE NULL END)+count(CASE WHEN f_zaoqianfa='失效' THEN 1 ELSE NULL END)" +
"+count(CASE WHEN f_zibifa='漏气' THEN 1 ELSE NULL END)+count(CASE WHEN f_zibifa='失效' THEN 1 ELSE NULL END)" +
"+(count(CASE WHEN f_precaution_kitchen=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_precaution_openkitchen=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_precaution_multisource=1 THEN 1 ELSE NULL END)+count(f_precaution_otheruse))" +
"+(count(CASE WHEN f_meter_wrapped=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_meter_hanger=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_meter_nearfire=1 THEN 1 ELSE NULL END)+ count(CASE WHEN f_meter_unfavorable=1 THEN 1 ELSE NULL END))" +
"+(count(CASE WHEN f_jpglouqi=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpglaohua=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpgguochang=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpgwuguanjia=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpgdiaoding=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpganmai=1 THEN 1 ELSE NULL END))) yhhj," +
//燃气表到期
" count(CASE WHEN (CASE WHEN f_meter_manufacture_date is not null THEN substring(CONVERT(varchar(50),GETDATE(),120),1,7)- substring(CONVERT(varchar(50),f_meter_manufacture_date,120),1,7) ELSE NULL END)>3650 THEN 1 ELSE NULL END) rqbdq," +
//灶具管隐患
" (count(CASE WHEN f_jpglouqi=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpglaohua=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpgguochang=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpgwuguanjia=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpgdiaoding=1 THEN 1 ELSE NULL END)+count(CASE WHEN f_jpganmai=1 THEN 1 ELSE NULL END)) jbgyh,count(CASE WHEN f_jpglouqi=1 THEN 1 ELSE NULL END) jbglq,count(CASE WHEN f_jpglaohua=1 THEN 1 ELSE NULL END) jbglh,count(CASE WHEN f_jpgguochang=1 THEN 1 ELSE NULL END) jbggc,count(CASE WHEN f_jpgwuguanjia=1 THEN 1 ELSE NULL END) jbgwgj,count(CASE WHEN f_jpgdiaoding=1 THEN 1 ELSE NULL END) jbgdd,count(CASE WHEN f_jpganmai=1 THEN 1 ELSE NULL END) jbgam，count(CASE WHEN f_jpgzhengchang=1 THEN 1 ELSE NULL END) jbgzc," +
//各故障占比
" COUNT(t1.id) id from (select * from T_INSPECTION where CONDITION is not null and " + so.Condition + " ) t1" +
" LEFT JOIN" +
" (select id,F_GONGNUAN from T_INSPECTION where F_GONGNUAN is NULL) gn1" +
" on t1.id=gn1.id" +
" LEFT JOIN" +
" (select id,F_GONGNUAN from T_INSPECTION where F_GONGNUAN='壁挂炉供暖') gn2" +
" on t1.id=gn2.id " +
" LEFT JOIN" +
" (select id,F_GONGNUAN from T_INSPECTION where F_GONGNUAN='集中供暖') gn3" +
" on t1.id=gn3.id " +
" LEFT JOIN" +
" (select id,F_GONGNUAN from T_INSPECTION where F_GONGNUAN='燃气取暖器供暖') gn5" +
" on t1.id=gn5.id" +
" LEFT JOIN" +
" (select id,F_GONGNUAN from T_INSPECTION where F_GONGNUAN='其他') gn6" +
" on t1.id=gn6.id" +
" LEFT JOIN" +
" (select id,f_iszhongdian from T_INSPECTION where f_iszhongdian is NULL) zd1" +
" on t1.id=zd1.id" +
" LEFT JOIN" +
" (select id,f_iszhongdian from T_INSPECTION where f_iszhongdian='气量出入') zd2" +
" on t1.id=zd2.id " +
" LEFT JOIN" +
" (select id,f_iszhongdian from T_INSPECTION where f_iszhongdian='重大隐患') zd3" +
" on t1.id=zd3.id " +
" LEFT JOIN" +
" (select id,f_iszhongdian from T_INSPECTION where f_iszhongdian='孤寡老人') zd5" +
" on t1.id=zd5.id" +
" LEFT JOIN" +
" (select id,f_iszhongdian from T_INSPECTION where f_iszhongdian='否') zd6" +
" on t1.id=zd6.id" +
" LEFT JOIN" +
" (select id,f_property from T_INSPECTION where f_property is NULL) xqxz1" +
" on t1.id=xqxz1.id" +
" LEFT JOIN" +
" (select id,f_property from T_INSPECTION where f_property='安置小区') xqxz2" +
" on t1.id=xqxz2.id " +
" LEFT JOIN" +
" (select id,f_property from T_INSPECTION where f_property='企事业单位') xqxz3" +
" on t1.id=xqxz3.id " +
" LEFT JOIN" +
" (select id,f_property from T_INSPECTION where f_property='物业小区') xqxz5" +
" on t1.id=xqxz5.id" +
" LEFT JOIN" +
" (select id,f_property from T_INSPECTION where f_property='自建房') xqxz6" +
" on t1.id=xqxz6.id" +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu is NULL) zzh1" +
" on t1.id=zzh1.id" +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu='餐饮') zzh2" +
" on t1.id=zzh2.id " +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu='普通居住') zzh3" +
" on t1.id=zzh3.id " +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu='托管培训') zzh5" +
" on t1.id=zzh5.id" +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu='办公') zzh6" +
" on t1.id=zzh6.id" +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu='集体宿舍') zzh7" +
" on t1.id=zzh7.id" +
" LEFT JOIN" +
" (select id,f_zuzhu from T_INSPECTION where f_zuzhu='否') zzh8" +
" on t1.id=zzh8.id" +
" LEFT JOIN" +
" (select id,CONDITION from T_INSPECTION where CONDITION='正常') zhuangt1" +
" on t1.id=zhuangt1.id" +
" LEFT JOIN" +
" (select id,CONDITION from T_INSPECTION where CONDITION='无人') zhuangt2" +
" on t1.id=zhuangt2.id" +
" LEFT JOIN" +
" (select id,CONDITION from T_INSPECTION where CONDITION='拒检') zhuangt3" +
" on t1.id=zhuangt3.id" +
" LEFT JOIN" +
" (select id,f_devices from T_INSPECTION where f_devices is NULL) sb1" +
" on t1.id=sb1.id" +
" LEFT JOIN" +
" (select id,f_devices from T_INSPECTION where f_devices='单灶') sb2" +
" on t1.id=sb2.id" +
" LEFT JOIN" +
" (select id,f_devices from T_INSPECTION where f_devices='灶具+热水器') sb3" +
" on t1.id=sb3.id" +
" LEFT JOIN" +
" (select id,f_devices from T_INSPECTION where f_devices='灶具+壁挂炉') sb5" +
" on t1.id=sb5.id" +
" LEFT JOIN" +
" (select id,f_devices from T_INSPECTION where f_devices='灶具+燃气取暖器') sb6" +
" on t1.id=sb6.id";
hejilists.WebClientInfo = Application.Current.Resources["dbclient"] as WebClientInfo;
hejilists.LoadOnPathChanged = false;
hejilists.Path = "sql";
hejilists.SumNames = "id,";
hejilists.PageSize = 10;
hejilists.HQL = sql;
hejilists.PageIndex = 0;
hejilists.DataLoaded += hejilists_Completed;
hejilists.Load();
}