public HttpResponseMessage DeviceSelect([FromBody] DeviceAjax deviceAjax) { Db db = new Db(); Response res = new Response(); Dictionary <string, object> data = new Dictionary <string, object>(); //查询系统select string sql1 = @" SELECT A.SYSTEM_ID AS SYSTEM_ID, B.NAME AS SYSTEM_NAME FROM GDMS_USER_SYSTEM A LEFT JOIN GDMS_SYSTEM B ON A.SYSTEM_ID = B.ID WHERE A.USER_ID = '" + deviceAjax.userId + "'"; var ds1 = db.QueryT(sql1); Dictionary <string, string> dict1 = new Dictionary <string, string>(); foreach (DataRow col in ds1.Rows) { dict1.Add(col["SYSTEM_ID"].ToString(), col["SYSTEM_NAME"].ToString()); } data.Add("system", dict1); //查询地点select string sql2 = @" SELECT A.SYSTEM_ID AS SYSTEM_ID, A.ID AS SITE_ID, A.NAME AS SITE_NAME FROM GDMS_SITE A LEFT JOIN GDMS_USER_SYSTEM B ON A.SYSTEM_ID = B.SYSTEM_ID WHERE B.USER_ID = '" + deviceAjax.userId + "' ORDER BY A.SYSTEM_ID ASC "; var ds2 = db.QueryT(sql2); Dictionary <string, object> siteData = new Dictionary <string, object>(); Dictionary <string, string> dict2 = new Dictionary <string, string>(); var index = "0"; foreach (DataRow col in ds2.Rows) { if (index == "0" || index == col["SYSTEM_ID"].ToString()) { dict2.Add(col["SITE_ID"].ToString(), col["SITE_NAME"].ToString()); index = col["SYSTEM_ID"].ToString(); } else { Dictionary <string, string> temp = new Dictionary <string, string>(dict2); siteData.Add(index, temp); dict2.Clear(); dict2.Add(col["SITE_ID"].ToString(), col["SITE_NAME"].ToString()); index = col["SYSTEM_ID"].ToString(); } } siteData.Add(index, dict2); data.Add("site", siteData); //查询类型select string sql3 = @" SELECT A.SYSTEM_ID AS SYSTEM_ID, A.ID AS TYPE_ID, A.NAME AS TYPE_NAME FROM GDMS_TYPE A LEFT JOIN GDMS_USER_SYSTEM B ON A.SYSTEM_ID = B.SYSTEM_ID WHERE B.USER_ID = '" + deviceAjax.userId + "' ORDER BY A.SYSTEM_ID ASC "; var ds3 = db.QueryT(sql3); Dictionary <string, object> TypeData = new Dictionary <string, object>(); Dictionary <string, string> dict3 = new Dictionary <string, string>(); index = "0"; foreach (DataRow col in ds3.Rows) { if (index == "0" || index == col["SYSTEM_ID"].ToString()) { dict3.Add(col["TYPE_ID"].ToString(), col["TYPE_NAME"].ToString()); index = col["SYSTEM_ID"].ToString(); } else { Dictionary <string, string> temp = new Dictionary <string, string>(dict3); TypeData.Add(index, temp); dict3.Clear(); dict3.Add(col["TYPE_ID"].ToString(), col["TYPE_NAME"].ToString()); index = col["SYSTEM_ID"].ToString(); } } TypeData.Add(index, dict3); data.Add("type", TypeData); //查询式样select string sql4 = @" SELECT A.TYPE_ID AS TYPE_ID, A.ID AS STYLE_ID, A.NAME AS STYLE_NAME FROM GDMS_STYLE A LEFT JOIN GDMS_TYPE B ON A.TYPE_ID = B.ID LEFT JOIN GDMS_USER_SYSTEM C ON B.SYSTEM_ID = C.SYSTEM_ID WHERE C.USER_ID = '" + deviceAjax.userId + "' ORDER BY A.TYPE_ID ASC"; var ds4 = db.QueryT(sql4); Dictionary <string, object> StyleData = new Dictionary <string, object>(); Dictionary <string, string> dict4 = new Dictionary <string, string>(); index = "0"; foreach (DataRow col in ds4.Rows) { if (index == "0" || index == col["TYPE_ID"].ToString()) { dict4.Add(col["STYLE_ID"].ToString(), col["STYLE_NAME"].ToString()); index = col["TYPE_ID"].ToString(); } else { Dictionary <string, string> temp = new Dictionary <string, string>(dict4); StyleData.Add(index, temp); dict4.Clear(); dict4.Add(col["STYLE_ID"].ToString(), col["STYLE_NAME"].ToString()); index = col["TYPE_ID"].ToString(); } } StyleData.Add(index, dict4); data.Add("style", StyleData); //查询位置select string sql5 = @" SELECT A.SITE_ID AS SITE_ID, A.ID AS STN_ID, A.NAME AS STN_NAME FROM GDMS_STN_MAIN A LEFT JOIN GDMS_SITE B ON A.SITE_ID = B.ID LEFT JOIN GDMS_USER_SYSTEM C ON B.SYSTEM_ID = C.SYSTEM_ID WHERE C.USER_ID = '" + deviceAjax.userId + "' ORDER BY A.SITE_ID ASC"; var ds5 = db.QueryT(sql5); Dictionary <string, object> StnData = new Dictionary <string, object>(); Dictionary <string, string> dict5 = new Dictionary <string, string>(); index = "0"; foreach (DataRow col in ds5.Rows) { if (index == "0" || index == col["SITE_ID"].ToString()) { dict5.Add(col["STN_ID"].ToString(), col["STN_NAME"].ToString()); index = col["SITE_ID"].ToString(); } else { Dictionary <string, string> temp = new Dictionary <string, string>(dict5); StnData.Add(index, temp); dict5.Clear(); dict5.Add(col["STN_ID"].ToString(), col["STN_NAME"].ToString()); index = col["SITE_ID"].ToString(); } } StnData.Add(index, dict5); data.Add("stn", StnData); //查询项目select string sql6 = @" SELECT DISTINCT A.ID AS PROJECT_ID, A.NAME AS PROJECT_NAME FROM GDMS_PROJECT A LEFT JOIN GDMS_USER_SYSTEM B ON A.SYSTEM_ID = B.SYSTEM_ID WHERE B.USER_ID = '" + deviceAjax.userId + "' ORDER BY A.NAME ASC"; var ds6 = db.QueryT(sql6); Dictionary <string, object> ProjectData = new Dictionary <string, object>(); Dictionary <string, string> dict6 = new Dictionary <string, string>(); index = "0"; foreach (DataRow col in ds6.Rows) { if (index == "0" || index == col["PROJECT_ID"].ToString()) { dict6.Add(col["PROJECT_ID"].ToString(), col["PROJECT_NAME"].ToString()); index = col["PROJECT_ID"].ToString(); } else { Dictionary <string, string> temp = new Dictionary <string, string>(dict6); ProjectData.Add(index, temp); dict6.Clear(); dict6.Add(col["PROJECT_ID"].ToString(), col["PROJECT_NAME"].ToString()); index = col["PROJECT_ID"].ToString(); } } ProjectData.Add(index, dict6); data.Add("project", ProjectData); res.code = 0; res.msg = ""; res.data = data; var resJsonStr = JsonConvert.SerializeObject(res); HttpResponseMessage resJson = new HttpResponseMessage { Content = new StringContent(resJsonStr, Encoding.GetEncoding("UTF-8"), "application/json") }; return(resJson); }
public HttpResponseMessage DeviceList([FromBody] DeviceAjax deviceAjax) { Db db = new Db(); string where = ""; if (deviceAjax.systemId != null) { where = where + " AND G.SYSTEM_ID = '" + deviceAjax.systemId + "'"; } if (deviceAjax.devId != null) { where = where + " AND A.ID = '" + deviceAjax.devId + "'"; } if (deviceAjax.siteId != null) { where = where + " AND B.SITE_ID = '" + deviceAjax.siteId + "'"; } if (deviceAjax.typeId != null) { where = where + " AND D.TYPE_ID = '" + deviceAjax.typeId + "'"; } if (deviceAjax.stnId != null) { where = where + " AND A.STN_ID = '" + deviceAjax.stnId + "'"; } if (deviceAjax.styleId != null) { where = where + " AND A.STYLE_ID = '" + deviceAjax.styleId + "'"; } if (deviceAjax.projectId != null) { where = where + " AND A.PROJECT_ID = '" + deviceAjax.projectId + "'"; } if (deviceAjax.keyword != null && deviceAjax.keyword.Length != 0) //模糊搜索项:序列号、备注 { where = where + " AND ( " + "UPPER(A.SN) LIKE '%" + deviceAjax.keyword.ToUpper() + "%' or " + "UPPER(A.REMARK) LIKE '%" + deviceAjax.keyword.ToUpper() + "%')"; } if (deviceAjax.filterStatus != null && deviceAjax.filterStatus.Length != 0) //筛选状态 { where = where + " AND A.STATUS = '" + deviceAjax.filterStatus + "'"; } if (deviceAjax.filterDeliveryStart != null && deviceAjax.filterDeliveryStart.Length != 0) //筛选到货日期 - 起始日期 { where = where + " AND DELIVERY_DATE >= to_date('" + deviceAjax.filterDeliveryStart + "','yyyy-mm-dd')"; } if (deviceAjax.filterDeliveryEnd != null && deviceAjax.filterDeliveryEnd.Length != 0) //筛选到货日期 - 结束日期 { where = where + " AND DELIVERY_DATE <= to_date('" + deviceAjax.filterDeliveryEnd + "','yyyy-mm-dd')"; } if (deviceAjax.filterSite == "0") //筛选地点 { where = where + " AND (SITE_ID = '' OR SITE_ID IS NULL)"; } else if (deviceAjax.filterSite == "1") { where = where + " AND (SITE_ID <> '' OR SITE_ID IS NOT NULL)"; } if (deviceAjax.filterStn == "0") //筛选位置 { where = where + " AND (STN_ID = '' OR STN_ID IS NULL)"; } else if (deviceAjax.filterStn == "1") { where = where + " AND (STN_ID <> '' OR STN_ID IS NOT NULL)"; } string sqlnp = @" SELECT A.COUNT, A.SN, TO_CHAR(A.DELIVERY_DATE,'YYYY-MM-DD') AS DELIVERY_DATE, A.STATUS, A.REMARK, B.NAME AS STN_NAME, C.NAME AS SITE_NAME, D.NAME AS STYLE_NAME, E.NAME AS PROJECT_NAME, G.NAME AS TYPE_NAME, A.ID AS DEV_ID, B.ID AS STN_ID, C.ID AS SITE_ID, D.ID AS STYLE_ID, E.ID AS PROJECT_ID, G.ID AS TYPE_ID, H.ID AS SYSTEM_ID FROM GDMS_DEV_MAIN A LEFT JOIN GDMS_STN_MAIN B ON A.STN_ID = B.ID LEFT JOIN GDMS_SITE C ON B.SITE_ID = C.ID LEFT JOIN GDMS_STYLE D ON A.STYLE_ID = D.ID LEFT JOIN GDMS_PROJECT E ON A.PROJECT_ID = E.ID LEFT JOIN GDMS_SYSTEM F ON B.SITE_ID = F.ID LEFT JOIN GDMS_TYPE G ON D.TYPE_ID = G.ID LEFT JOIN GDMS_SYSTEM H ON G.SYSTEM_ID = H.ID WHERE G.SYSTEM_ID IN (SELECT SYSTEM_ID FROM GDMS_USER_SYSTEM WHERE USER_ID = '" + deviceAjax.userId + "') " + where + "ORDER BY G.ID ASC,D.ID ASC,A.DELIVERY_DATE DESC,A.SN ASC"; int limit1 = (deviceAjax.page - 1) * deviceAjax.limit + 1; int limit2 = deviceAjax.page * deviceAjax.limit; string sql = "SELECT * FROM(SELECT p1.*,ROWNUM rn FROM(" + sqlnp + ")p1)WHERE rn BETWEEN " + limit1 + " AND " + limit2; var ds = db.QueryT(sql); Response res = new Response(); ArrayList data = new ArrayList(); foreach (DataRow col in ds.Rows) { var status = ""; if (col["STATUS"].ToString() == "0") { status = "备件"; } else if (col["STATUS"].ToString() == "1") { status = "在用"; } else if (col["STATUS"].ToString() == "2") { status = "故障"; } else if (col["STATUS"].ToString() == "3") { status = "维修"; } Dictionary <string, string> dict = new Dictionary <string, string> { { "TYPE_NAME", col["TYPE_NAME"].ToString() }, { "STN_NAME", col["STN_NAME"].ToString() }, { "SITE_NAME", col["SITE_NAME"].ToString() }, { "STYLE_NAME", col["STYLE_NAME"].ToString() }, { "PROJECT_NAME", col["PROJECT_NAME"].ToString() }, { "COUNT", col["COUNT"].ToString() }, { "SN", col["SN"].ToString() }, { "DELIVERY_DATE", col["DELIVERY_DATE"].ToString() }, { "STATUS", status }, { "STATUS_ID", col["STATUS"].ToString() }, { "REMARK", col["REMARK"].ToString() }, { "DEV_ID", col["DEV_ID"].ToString() }, { "STN_ID", col["STN_ID"].ToString() }, { "SITE_ID", col["SITE_ID"].ToString() }, { "STYLE_ID", col["STYLE_ID"].ToString() }, { "PROJECT_ID", col["PROJECT_ID"].ToString() }, { "TYPE_ID", col["TYPE_ID"].ToString() }, { "SYSTEM_ID", col["SYSTEM_ID"].ToString() } }; data.Add(dict); } string sql2 = @" SELECT COUNT(*) AS COUNT FROM GDMS_DEV_MAIN A LEFT JOIN GDMS_STN_MAIN B ON A.STN_ID = B.ID LEFT JOIN GDMS_SITE C ON B.SITE_ID = C.ID LEFT JOIN GDMS_STYLE D ON A.STYLE_ID = D.ID LEFT JOIN GDMS_PROJECT E ON A.PROJECT_ID = E.ID LEFT JOIN GDMS_SYSTEM F ON B.SITE_ID = F.ID LEFT JOIN GDMS_TYPE G ON D.TYPE_ID = G.ID LEFT JOIN GDMS_SYSTEM H ON G.SYSTEM_ID = H.ID WHERE G.SYSTEM_ID IN (SELECT SYSTEM_ID FROM GDMS_USER_SYSTEM WHERE USER_ID = '" + deviceAjax.userId + "') " + where; var ds2 = db.QueryT(sql2); foreach (DataRow col in ds2.Rows) { res.count = col["count"].ToString(); } res.code = 0; res.msg = ""; res.data = data; var resJsonStr = JsonConvert.SerializeObject(res); HttpResponseMessage resJson = new HttpResponseMessage { Content = new StringContent(resJsonStr, Encoding.GetEncoding("UTF-8"), "application/json") }; return(resJson); }
public HttpResponseMessage DeviceList([FromBody] DeviceAjax deviceAjax) { Db db = new Db(); string where = ""; if (deviceAjax.systemId != null) { where = where + " AND G.SYSTEM_ID = '" + deviceAjax.systemId + "'"; } if (deviceAjax.devId != null) { where = where + " AND A.ID = '" + deviceAjax.devId + "'"; } if (deviceAjax.siteId != null) { where = where + " AND B.SITE_ID = '" + deviceAjax.siteId + "'"; } if (deviceAjax.typeId != null) { where = where + " AND D.TYPE_ID = '" + deviceAjax.typeId + "'"; } if (deviceAjax.stnId != null) { where = where + " AND A.STN_ID = '" + deviceAjax.stnId + "'"; } if (deviceAjax.styleId != null) { where = where + " AND A.STYLE_ID = '" + deviceAjax.styleId + "'"; } if (deviceAjax.projectId != null) { where = where + " AND A.PROJECT_ID = '" + deviceAjax.projectId + "'"; } if (deviceAjax.keyword != null && deviceAjax.keyword.Length != 0) { where = where + "AND ( A.SN LIKE '" + deviceAjax.keyword + "' or A.REMARK LIKE '" + deviceAjax.keyword + "')"; } string sql = @" SELECT A.COUNT, A.SN, TO_CHAR(A.DELIVERY_DATE,'YYYY-MM-DD') AS DELIVERY_DATE, A.STATUS, A.REMARK, B.NAME AS STN_NAME, C.NAME AS SITE_NAME, D.NAME AS STYLE_NAME, E.NAME AS PROJECT_NAME, G.NAME AS TYPE_NAME, A.ID AS DEV_ID, B.ID AS STN_ID, C.ID AS SITE_ID, D.ID AS STYLE_ID, E.ID AS PROJECT_ID, G.ID AS TYPE_ID, H.ID AS SYSTEM_ID FROM GDMS_DEV_MAIN A LEFT JOIN GDMS_STN_MAIN B ON A.STN_ID = B.ID LEFT JOIN GDMS_SITE C ON B.SITE_ID = C.ID LEFT JOIN GDMS_STYLE D ON A.STYLE_ID = D.ID LEFT JOIN GDMS_PROJECT E ON A.PROJECT_ID = E.ID LEFT JOIN GDMS_SYSTEM F ON B.SITE_ID = F.ID LEFT JOIN GDMS_TYPE G ON D.TYPE_ID = G.ID LEFT JOIN GDMS_SYSTEM H ON G.SYSTEM_ID = H.ID WHERE G.SYSTEM_ID IN (SELECT SYSTEM_ID FROM GDMS_USER_SYSTEM WHERE USER_ID = '" + deviceAjax.userId + "') " + where; var ds = db.QueryT(sql); Response res = new Response(); ArrayList data = new ArrayList(); foreach (DataRow col in ds.Rows) { var status = ""; if (col["STATUS"].ToString() == "0") { status = "备件"; } else if (col["STATUS"].ToString() == "1") { status = "在用"; } else if (col["STATUS"].ToString() == "2") { status = "故障"; } else if (col["STATUS"].ToString() == "3") { status = "维修"; } Dictionary <string, string> dict = new Dictionary <string, string> { { "TYPE_NAME", col["TYPE_NAME"].ToString() }, { "STN_NAME", col["STN_NAME"].ToString() }, { "SITE_NAME", col["SITE_NAME"].ToString() }, { "STYLE_NAME", col["STYLE_NAME"].ToString() }, { "PROJECT_NAME", col["PROJECT_NAME"].ToString() }, { "COUNT", col["COUNT"].ToString() }, { "SN", col["SN"].ToString() }, { "DELIVERY_DATE", col["DELIVERY_DATE"].ToString() }, { "STATUS", status }, { "REMARK", col["REMARK"].ToString() }, { "DEV_ID", col["DEV_ID"].ToString() }, { "STN_ID", col["STN_ID"].ToString() }, { "SITE_ID", col["SITE_ID"].ToString() }, { "STYLE_ID", col["STYLE_ID"].ToString() }, { "PROJECT_ID", col["PROJECT_ID"].ToString() }, { "TYPE_ID", col["TYPE_ID"].ToString() } }; data.Add(dict); } string sql2 = @" SELECT COUNT(*) AS COUNT FROM GDMS_DEV_MAIN A LEFT JOIN GDMS_STN_MAIN B ON A.STN_ID = B.ID LEFT JOIN GDMS_SITE C ON B.SITE_ID = C.ID LEFT JOIN GDMS_STYLE D ON A.STYLE_ID = D.ID LEFT JOIN GDMS_PROJECT E ON A.PROJECT_ID = E.ID LEFT JOIN GDMS_SYSTEM F ON B.SITE_ID = F.ID LEFT JOIN GDMS_TYPE G ON D.TYPE_ID = G.ID LEFT JOIN GDMS_SYSTEM H ON G.SYSTEM_ID = H.ID WHERE G.SYSTEM_ID IN (SELECT SYSTEM_ID FROM GDMS_USER_SYSTEM WHERE USER_ID = '" + deviceAjax.userId + "') " + where; var ds2 = db.QueryT(sql2); foreach (DataRow col in ds2.Rows) { res.count = col["count"].ToString(); } res.code = 0; res.msg = ""; res.data = data; var resJsonStr = JsonConvert.SerializeObject(res); HttpResponseMessage resJson = new HttpResponseMessage { Content = new StringContent(resJsonStr, Encoding.GetEncoding("UTF-8"), "application/json") }; return(resJson); }