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());
}