//查询排水检修记录 public static string fixIndosQuery(string json2) { PipeFix p1 = JsonConvert.DeserializeObject <PipeFix>(json2); List <PipeFix> listInfos = sdeConnection.getPipeFixInfos(p1); string resInfo = ConvertToJson(listInfos); return(resInfo); }
//管线检修数据,查询 public static List <PipeFix> getPipeFixInfos(PipeFix queryInfos) { List <PipeFix> fixInfos = new List <PipeFix>(); SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["PIPE"]); ConnectSQL(conn); string fixSql = "select *from PIPEFIX where 1=1" + string.Format(string.IsNullOrEmpty(queryInfos.ManagerUnits) ? "" : "and ManagerUnits='" + queryInfos.ManagerUnits + "'") + string.Format(string.IsNullOrEmpty(queryInfos.ProjectName) ? "" : "and ProjectName='" + queryInfos.ProjectName + "'") + string.Format(string.IsNullOrEmpty(queryInfos.NameAndPhone) ? "" : "and NameAndPhone='" + queryInfos.NameAndPhone + "'") + string.Format(string.IsNullOrEmpty(queryInfos.ProjectCode) ? "" : "and ProjectCode='" + queryInfos.ProjectCode + "'") + string.Format(string.IsNullOrEmpty(queryInfos.place) ? "" : "and place='" + queryInfos.place + "'") + string.Format(string.IsNullOrEmpty(queryInfos.Principal) ? "" : "and Principal='" + queryInfos.Principal + "'") + string.Format(string.IsNullOrEmpty(queryInfos.Problem) ? "" : "and Problem='" + queryInfos.Problem + "'") + string.Format(string.IsNullOrEmpty(queryInfos.MoneyCost) ? "" : "and FLOOR(MoneyCost)=" + queryInfos.MoneyCost) +//花费为float型要区别开,此处使用FLOOR对MoneyCost取整 string.Format(string.IsNullOrEmpty(queryInfos.PIPES) ? "" : "and PIPES='" + queryInfos.PIPES + "'") + string.Format(string.IsNullOrEmpty(queryInfos.FixType) ? "" : "and FixType='" + queryInfos.FixType + "'") + string.Format(string.IsNullOrEmpty(queryInfos.StartTime) ? "" : "and StartTime Like'" + queryInfos.StartTime + "%'") + string.Format(string.IsNullOrEmpty(queryInfos.EndTime) ? "" : "and EndTime Like'" + queryInfos.EndTime + "%'"); try { SqlCommand cmd = null; SqlDataAdapter da = null; DataSet ds = null; DataTable dt = null; cmd = new SqlCommand(fixSql, conn); da = new SqlDataAdapter(cmd); ds = new DataSet(); da.Fill(ds, "ds"); dt = ds.Tables[0]; PipeFix tmp; for (int i = 0; i < dt.Rows.Count; i++) { tmp = new PipeFix(); DataRow row = dt.Rows[i]; tmp.ManagerUnits = (Convert.IsDBNull(row["ManagerUnits"])) ? "" : (string)row["ManagerUnits"]; tmp.ConstructionUnits = (Convert.IsDBNull(row["ConstructionUnits"])) ? "" : (string)row["ConstructionUnits"]; tmp.ProjectName = (Convert.IsDBNull(row["ProjectName"])) ? "" : (string)row["ProjectName"]; tmp.NameAndPhone = (Convert.IsDBNull(row["NameAndPhone"])) ? "" : (string)(row["NameAndPhone"]); tmp.ProjectCode = (Convert.IsDBNull(row["ProjectCode"])) ? "" : (string)(row["ProjectCode"]); tmp.place = (Convert.IsDBNull(row["place"])) ? "0" : (string)(row["place"]); tmp.Principal = (Convert.IsDBNull(row["Principal"])) ? "" : (string)(row["Principal"]); tmp.Problem = (Convert.IsDBNull(row["Problem"])) ? "" : (string)(row["Problem"]); tmp.MoneyCost = (Convert.IsDBNull(row["MoneyCost"])) ? "" : (row["MoneyCost"]).ToString(); tmp.MoreInfo = (Convert.IsDBNull(row["MoreInfo"])) ? "" : (string)row["MoreInfo"]; tmp.PIPES = (Convert.IsDBNull(row["PIPES"])) ? "" : (string)row["PIPES"]; tmp.FixType = (Convert.IsDBNull(row["FixType"])) ? "" : (row["FixType"]).ToString(); tmp.StartTime = (Convert.IsDBNull(row["StartTime"])) ? "" : ((DateTime)(row["StartTime"])).ToString("yyyy-MM-dd"); tmp.EndTime = (Convert.IsDBNull(row["EndTime"])) ? "" : ((DateTime)(row["EndTime"])).ToString("yyyy-MM-dd"); tmp.RecordTime = (Convert.IsDBNull(row["RecordTime"])) ? "" : ((DateTime)(row["RecordTime"])).ToString("yyyy-MM-dd"); fixInfos.Add(tmp); } } catch { conn.Close(); } finally { conn.Close(); } return(fixInfos); }