//查询排水检修记录
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);
}
