public static int GetPrinterCount(PrinterCondition pc) { DataSet ds = new DataSet(); string sql = string.Empty; using (SqlConnection conn = new SqlConnection(connLocal)) { switch (pc) { case PrinterCondition.All: sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127)"; break; case PrinterCondition.Up: // 墨盒低于10%的数量 { sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Up' "; sql += "and (case when substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus))<>'' then CAST(substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus)) as int) else 999 end) <= 10"; } break; case PrinterCondition.Down: sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Down'"; break; } SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql, conn); da.SelectCommand = cmd; conn.Open(); da.Fill(ds); conn.Close(); } return(Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString())); }
public static DataSet GetPrinterInformation(PrinterCondition pc) { DataSet ds = new DataSet(); string sql = string.Empty; using (SqlConnection conn = new SqlConnection(connLocal)) { switch (pc) { case PrinterCondition.All: sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) order by storeNo"; break; case PrinterCondition.Up: { sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='UP' "; sql += "order by dbo.GetNumber(tonerStatus), storeNo"; } break; case PrinterCondition.Down: sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='DOWN' order by storeNo"; break; } SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql, conn); da.SelectCommand = cmd; conn.Open(); da.Fill(ds); conn.Close(); } return(ds); }
public static DataSet GetPrinterInformation(PrinterCondition pc) { DataSet ds = new DataSet(); string sql = string.Empty; using (SqlConnection conn = new SqlConnection(connLocal)) { switch (pc) { case PrinterCondition.All: sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) order by storeNo"; break; case PrinterCondition.Up: { sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Up' "; sql += "order by (case when substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus))<>'' then CAST(substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus)) as int) else 999 end), storeNo"; } break; case PrinterCondition.Down: sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Down' order by storeNo"; break; } SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql, conn); da.SelectCommand = cmd; conn.Open(); da.Fill(ds); conn.Close(); } return(ds); }
public static int GetPrinterCount(PrinterCondition pc) { DataSet ds = new DataSet(); string sql = string.Empty; using (SqlConnection conn = new SqlConnection(connLocal)) { switch (pc) { case PrinterCondition.All: sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127)"; break; case PrinterCondition.Up: // 墨盒低于10%的数量 { sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='UP' "; sql += "and dbo.GetNumber(tonerStatus) between 1 and 10"; } break; case PrinterCondition.Down: sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='DOWN'"; break; } SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql, conn); da.SelectCommand = cmd; conn.Open(); da.Fill(ds); conn.Close(); } return(Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString())); }
private static ArrayList GetData(PrinterCondition pc) { ArrayList list = new ArrayList(); DataSet ds = SqlHelper.GetPrinterInformation(pc); int count = ds.Tables[0].Rows.Count; for (int i = 0; i < count; i++) { PrinterInformation printer = new PrinterInformation(); printer.StoreNo = ds.Tables[0].Rows[i]["storeNo"].ToString(); printer.StoreRegion = ds.Tables[0].Rows[i]["storeRegion"].ToString(); printer.StoreType = ds.Tables[0].Rows[i]["storeType"].ToString(); printer.PrinterStatus = ds.Tables[0].Rows[i]["printerStatus"].ToString(); printer.TonerStatus = ds.Tables[0].Rows[i]["tonerStatus"].ToString(); printer.PrinterType = ds.Tables[0].Rows[i]["printerType"].ToString(); printer.TonerType = ds.Tables[0].Rows[i]["tonerType"].ToString(); printer.Date = ds.Tables[0].Rows[i]["date"].ToString(); printer.PrinterNetwork = ds.Tables[0].Rows[i]["printerNetwork"].ToString(); list.Add(printer); } return(list); }
private static ArrayList GetData(PrinterCondition pc) { ArrayList list = new ArrayList(); DataSet ds = SqlHelper.GetPrinterInformation(pc); int count = ds.Tables[0].Rows.Count; for (int i = 0; i < count; i++) { PrinterInformation printer = new PrinterInformation(); printer.StoreNo = ds.Tables[0].Rows[i]["storeNo"].ToString(); printer.StoreRegion = ds.Tables[0].Rows[i]["storeRegion"].ToString(); printer.StoreType = ds.Tables[0].Rows[i]["storeType"].ToString(); printer.PrinterStatus = ds.Tables[0].Rows[i]["printerStatus"].ToString(); printer.TonerStatus = ds.Tables[0].Rows[i]["tonerStatus"].ToString(); printer.PrinterType = ds.Tables[0].Rows[i]["printerType"].ToString(); printer.TonerType = ds.Tables[0].Rows[i]["tonerType"].ToString(); printer.Date = ds.Tables[0].Rows[i]["date"].ToString(); printer.PrinterNetwork = ds.Tables[0].Rows[i]["printerNetwork"].ToString(); list.Add(printer); } return list; }
public static DataSet GetPrinterInformation(PrinterCondition pc) { DataSet ds = new DataSet(); string sql = string.Empty; using (SqlConnection conn = new SqlConnection(connLocal)) { switch (pc) { case PrinterCondition.All: sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) order by storeNo"; break; case PrinterCondition.Up: { sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Up' "; sql += "order by (case when substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus))<>'' then CAST(substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus)) as int) else 999 end), storeNo"; } break; case PrinterCondition.Down: sql = "select * from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Down' order by storeNo"; break; } SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql, conn); da.SelectCommand = cmd; conn.Open(); da.Fill(ds); conn.Close(); } return ds; }
public static int GetPrinterCount(PrinterCondition pc) { DataSet ds = new DataSet(); string sql = string.Empty; using (SqlConnection conn = new SqlConnection(connLocal)) { switch (pc) { case PrinterCondition.All: sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127)"; break; case PrinterCondition.Up: // 墨盒低于10%的数量 { sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Up' "; sql += "and (case when substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus))<>'' then CAST(substring(tonerStatus,PATINDEX('%[0-9]%',tonerStatus),CHARINDEX('%',tonerStatus,1)-PATINDEX('%[0-9]%',tonerStatus)) as int) else 999 end) <= 10"; } break; case PrinterCondition.Down: sql = "select count(*) total from dbo.PrinterInformation where convert(nvarchar(10),date,127) = convert(nvarchar(10),GETDATE(),127) and printerNetwork='Down'"; break; } SqlDataAdapter da = new SqlDataAdapter(); SqlCommand cmd = new SqlCommand(sql, conn); da.SelectCommand = cmd; conn.Open(); da.Fill(ds); conn.Close(); } return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()); }