public string GenExtractionSql(bool IS_YEAR, int num, string unitcode, DateTime end_no_earlier, DateTime begin_no_later, DateTime? begin_no_earlier = null, DateTime? end_no_later = null)
{
DateStrGenerator g = new DateStrGenerator();
string dstr, rtn, desc;
if (IS_YEAR)
{
// dstr = g.GetDateStr(new DateTime(2015, 11, 10), new DateTime(2015, 12, 31), null, null);
desc = " desc";
}
else
{
desc = "";
}
dstr = g.GetDateStr(end_no_earlier, begin_no_later, begin_no_earlier, end_no_later);
rtn = string.Format(@"select * from (select x.*, y.ab_desc, y.graph, y.check_log from (
select distinct a.log_id, e.unit_code, e.unitname, f.stationname, a.instrcode, c.instrname, a.pointid, d.ab_type_name, b.science, a.start_date, a.end_date, a.stationid from qzdata.qz_abnormity_log a, qzdata.qz_abnormity_evalist b, qzdata.qz_abnormity_instrinfo c, qzdata.qz_abnormity_type d, qzdata.qz_abnormity_units e, qzdata.qz_dict_stations f where {0} and A.AB_ID >= 2 and a.ab_id <= 7 and a.stationid = b.stationid and a.pointid = b.pointid and b.science != '辅助' and A.INSTRCODE = C.INSTRCODE and A.AB_ID = D.AB_ID and B.UNITCODE = E.UNIT_CODE and a.stationid = f.stationid and b.unitcode = '{1}' and b.ab_flag = 'Y') x, qzdata.qz_abnormity_log y where x.log_id = y.log_id and y.ab_desc is not null and y.graph is not null order by row_number() over(partition by x.ab_type_name, x.science order by x.start_date {3}), decode(x.ab_type_name, '不明原因', 1, '场地环境', 2, '自然环境', 3, '观测系统', 4, '人为干扰', 5, '地球物理事件', 6), decode(x.science, '形变', 1, '重力', 2, '流体', 3, '地磁', 4, '地电', 5)) where rownum <= {2}", dstr, unitcode, num, desc);
return rtn;
}
public string GenGSetSql(GSetSqlType type, string[] science, string[] item, string[] unitcode, string[] abtype, string[] abtype2, string[] stationid, string[] instr, bool nation_good, bool area_good, bool science_good, DateTime end_no_earlier, DateTime begin_no_later, DateTime? begin_no_earlier = null, DateTime? end_no_later = null, string span = " >= 0")
{
string order = "";
DataTable f = new DataTable();
f.Columns.Add("col");
f.Columns.Add("slist", typeof(string[]));
f.Columns.Add("filter");
f.PrimaryKey = new DataColumn[]{f.Columns["col"]};
f.Rows.Add("science", science, "");
f.Rows.Add("item", item, "");
f.Rows.Add("unit_code", unitcode, "");
f.Rows.Add("ab_type_name", abtype, "");
f.Rows.Add("type2_id", abtype2, "");
f.Rows.Add("stationid", stationid, "");
f.Rows.Add("instrid", instr, "");
foreach (DataRow r in f.Rows)
{
string[] sl = r["slist"] as string[];
// String ft = r["filter"] as String;
if (sl != null && sl.GetLength(0) > 0)
{
foreach (string s in sl)
{
r["filter"] += "x." + r["col"] + " = '" + s + "' or ";
}
r["filter"] = "(" + (r["filter"] as string).Remove((r["filter"] as string).Length - 3) + ")";
order += "x." + r["col"] + ", ";
}
else
{
r["filter"] = "(1 = 1)";
}
}
if (order != "")
{
order = "order by " + order.Remove(order.Length - 2) + " ";
}
string nation_g;
if(nation_good)
{
nation_g = "(1 = 1)";
}
else
{
nation_g = "(1 = 1)";
}
string area_g;
if(area_good)
{
area_g = "(a.log_id in (select aa.log_id from qzdata.qz_abnormity_checkview aa, qzdata.qz_abnormity_userview bb where aa.user_id = bb.user_id and bb.USER_TYPE = 'SH' and aa.is_agree = 0 and aa.flag_2 = 0 and aa.flag_3 = 0 and aa.flag_5 = 0))";
}
else
{
area_g = "(1 = 1)";
}
string science_g;
if(science_good)
{
science_g = "(a.log_id in (select aa.log_id from qzdata.qz_abnormity_checkview aa, qzdata.qz_abnormity_userview bb where aa.user_id = bb.user_id and (bb.USER_TYPE = 'XB' or bb.USER_TYPE = 'ZL' or bb.USER_TYPE = 'DC' or bb.USER_TYPE = 'DD' or bb.USER_TYPE = 'LT') and aa.is_agree = 0 and aa.flag_2 = 0 and aa.flag_3 = 0 and aa.flag_5 = 0))";
}
else
{
science_g = "(1 = 1)";
}
DateStrGenerator dg = new DateStrGenerator();
string dat = dg.GetDateStr(end_no_earlier, begin_no_later, begin_no_earlier, end_no_later);
string rtn = null;
if (type == GSetSqlType.Normal)
{
rtn = @"select x.*, y.ab_desc, y.graph from (select distinct a.log_id, a.stationid||'x'||a.pointid as instrid, e.unit_code, e.unitname, f.stationname, a.instrcode, c.instrname, a.pointid, d.ab_type_name, h.type2_id, h.type2_name, b.science, a.start_date, a.end_date, a.stationid, decode(substr(g.itemid,0,3), '411','水位','431','水温', c.item) as item from qzdata.qz_abnormity_log a, qzdata.qz_abnormity_evalist b, qzdata.qz_abnormity_instrinfo c, qzdata.qz_abnormity_type d, qzdata.qz_abnormity_units e, qzdata.qz_dict_stations f, qzdata.qz_abnormity_itemlog g, qzdata.qz_abnormity_type2 h where _DATE and (a.end_date - a.start_date _SPAN) and a.ab_id >= 2 and a.ab_id <= 7 and a.log_id = g.log_id and g.type2_id = h.type2_id and a.stationid = b.stationid and a.pointid = b.pointid and b.science != '辅助' and a.instrcode = c.instrcode and a.ab_id = d.ab_id and b.unitcode = e.unit_code and a.stationid = f.stationid and b.ab_flag = 'Y' and _NATION_GOOD and _AREA_GOOD and _SCI_GOOD ) x, qzdata.qz_abnormity_log y where x.log_id = y.log_id and y.ab_desc is not null and y.graph is not null and _SCIENCE and _ITEM and _UNIT and _ABTYPE and _ABTYP2 and _STATION and _INSTR _ORDER"
.Replace("_DATE", dat)
.Replace("_SPAN", span)
.Replace("_SCIENCE", (string)f.Rows.Find("science")["filter"])
.Replace("_ITEM", (string)f.Rows.Find("item")["filter"])
.Replace("_UNIT", (string)f.Rows.Find("unit_code")["filter"])
.Replace("_NATION_GOOD", nation_g)
.Replace("_AREA_GOOD", area_g)
.Replace("_SCI_GOOD", science_g)
.Replace("_ORDER", order)
.Replace("_ABTYPE", (string)f.Rows.Find("ab_type_name")["filter"])
.Replace("_ABTYP2", (string)f.Rows.Find("type2_id")["filter"])
.Replace("_STATION", (string)f.Rows.Find("stationid")["filter"])
.Replace("_INSTR", (string)f.Rows.Find("instrid")["filter"]);
}
else if(type == GSetSqlType.CalType2Num)
{
rtn = @"select type2_id, count(type2_id) cnt from (select distinct a.log_id, a.stationid||'x'||a.pointid as instrid, e.unit_code, e.unitname, f.stationname, a.instrcode, c.instrname, a.pointid, d.ab_type_name, h.type2_id, h.type2_name, b.science, a.start_date, a.end_date, a.stationid, decode(substr(g.itemid,0,3), '411','水位','431','水温', c.item) as item from qzdata.qz_abnormity_log a, qzdata.qz_abnormity_evalist b, qzdata.qz_abnormity_instrinfo c, qzdata.qz_abnormity_type d, qzdata.qz_abnormity_units e, qzdata.qz_dict_stations f, qzdata.qz_abnormity_itemlog g, qzdata.qz_abnormity_type2 h where _DATE and a.ab_id >= 2 and a.ab_id <= 7 and a.log_id = g.log_id and g.type2_id = h.type2_id and a.stationid = b.stationid and a.pointid = b.pointid and b.science != '辅助' and a.instrcode = c.instrcode and a.ab_id = d.ab_id and b.unitcode = e.unit_code and a.stationid = f.stationid and b.ab_flag = 'Y' and _NATION_GOOD and _AREA_GOOD and _SCI_GOOD ) x where _SCIENCE and _ITEM and _ABTYPE group by type2_id "
.Replace("_DATE", dat)
.Replace("_SCIENCE", (string)f.Rows.Find("science")["filter"])
.Replace("_ITEM", (string)f.Rows.Find("item")["filter"])
.Replace("_NATION_GOOD", nation_g)
.Replace("_AREA_GOOD", area_g)
.Replace("_SCI_GOOD", science_g)
.Replace("_ABTYPE", (string)f.Rows.Find("ab_type_name")["filter"]);
}
else if (type == GSetSqlType.CalUnitNum)
{
rtn = @"select unit_code, count(unit_code) cnt from (select distinct a.log_id, a.stationid||'x'||a.pointid as instrid, e.unit_code, e.unitname, f.stationname, a.instrcode, c.instrname, a.pointid, d.ab_type_name, h.type2_id, h.type2_name, b.science, a.start_date, a.end_date, a.stationid, decode(substr(g.itemid,0,3), '411','水位','431','水温', c.item) as item from qzdata.qz_abnormity_log a, qzdata.qz_abnormity_evalist b, qzdata.qz_abnormity_instrinfo c, qzdata.qz_abnormity_type d, qzdata.qz_abnormity_units e, qzdata.qz_dict_stations f, qzdata.qz_abnormity_itemlog g, qzdata.qz_abnormity_type2 h where _DATE and a.ab_id >= 2 and a.ab_id <= 7 and a.log_id = g.log_id and g.type2_id = h.type2_id and a.stationid = b.stationid and a.pointid = b.pointid and b.science != '辅助' and a.instrcode = c.instrcode and a.ab_id = d.ab_id and b.unitcode = e.unit_code and a.stationid = f.stationid and b.ab_flag = 'Y' and _NATION_GOOD and _AREA_GOOD and _SCI_GOOD) x where _SCIENCE and _ITEM and _ABTYPE and _ABTYP2 group by unit_code "
.Replace("_DATE", dat)
.Replace("_SCIENCE", (string)f.Rows.Find("science")["filter"])
.Replace("_ITEM", (string)f.Rows.Find("item")["filter"])
.Replace("_NATION_GOOD", nation_g).Replace("_AREA_GOOD", area_g)
.Replace("_SCI_GOOD", science_g)
.Replace("_ABTYPE", (string)f.Rows.Find("ab_type_name")["filter"])
.Replace("_ABTYP2", (string)f.Rows.Find("type2_id")["filter"]);
}
else if (type == GSetSqlType.CalStationNum)
{
rtn = @"select stationid, count(stationid) cnt from (select distinct a.log_id, a.stationid||'x'||a.pointid as instrid, e.unit_code, e.unitname, f.stationname, a.instrcode, c.instrname, a.pointid, d.ab_type_name, h.type2_id, h.type2_name, b.science, a.start_date, a.end_date, a.stationid, decode(substr(g.itemid,0,3), '411','水位','431','水温', c.item) as item from qzdata.qz_abnormity_log a, qzdata.qz_abnormity_evalist b, qzdata.qz_abnormity_instrinfo c, qzdata.qz_abnormity_type d, qzdata.qz_abnormity_units e, qzdata.qz_dict_stations f, qzdata.qz_abnormity_itemlog g, qzdata.qz_abnormity_type2 h where _DATE and a.ab_id >= 2 and a.ab_id <= 7 and a.log_id = g.log_id and g.type2_id = h.type2_id and a.stationid = b.stationid and a.pointid = b.pointid and b.science != '辅助' and a.instrcode = c.instrcode and a.ab_id = d.ab_id and b.unitcode = e.unit_code and a.stationid = f.stationid and b.ab_flag = 'Y' and _NATION_GOOD and _AREA_GOOD and _SCI_GOOD) x where _SCIENCE and _ITEM and _ABTYPE and _ABTYP2 and _UNIT group by stationid "
.Replace("_DATE", dat)
.Replace("_SCIENCE", (string)f.Rows.Find("science")["filter"])
.Replace("_ITEM", (string)f.Rows.Find("item")["filter"])
.Replace("_NATION_GOOD", nation_g)
.Replace("_AREA_GOOD", area_g)
.Replace("_SCI_GOOD", science_g)
.Replace("_ABTYPE", (string)f.Rows.Find("ab_type_name")["filter"])
.Replace("_ABTYP2", (string)f.Rows.Find("type2_id")["filter"])
.Replace("_UNIT", (string)f.Rows.Find("unit_code")["filter"]);
}
return rtn;
}
