Line
//获取折线图数据方法
[Obsolete]
public void GetDataLine(string OrganizationName, int year,int month)
{
/*传入参数
OrganizationName //组织名称
year //年份
month //超出月份
*/
try
{
//打开数据库连接
conn.Open();
//清理表数据
this.chartControl1.Titles.Clear();
this.chartControl1.Series.Clear();
//获取stockidArray数组
string stockSQL = "exec Stock_YearOverAge_table '" + OrganizationName + "'," + year + "," + month + ",''";
var stockidArray = StockIDArray(stockSQL);
//如果数据为空,返回无呆滞物资和空数据
if (stockidArray.Length <= 0)
{
//空表
DataTable dt = new DataTable();
dt.Columns.Add("月份", typeof(string));
dt.Columns.Add("不常用品种数", typeof(int));
var dtNew = CompleteYearData(dt, stockidArray);
Series sr = new Series("", ViewType.Bar);
//设置Series样式
sr.ArgumentScaleType = ScaleType.Qualitative;//定性的
sr.ValueScaleType = ScaleType.Numerical;//数字类型
sr.PointOptions.PointView = PointView.ArgumentAndValues;//显示表示的信息和数据
//绑定数据源
sr.DataSource = dtNew.DefaultView;//newdtb是获取到的数据(可以是数据库中的表,也可以是DataTable)
sr.ArgumentDataMember = "月份";//绑定的文字信息(名称)(坐标横轴)
sr.ValueDataMembers[0] = "不常用品种数";//绑定的值(数据)(坐标纵轴)
this.chartControl1.Series.Add(sr);
conn.Close();
ChartTitle ctnull = new ChartTitle
{
Text = "" + year + "年无呆滞物资",
TextColor = Color.FromArgb(0, 128, 0),//颜色
Font = new Font("Tahoma", 14),//字体
Dock = ChartTitleDockStyle.Top,//停靠在上方
Alignment = StringAlignment.Center//居中显示
};
this.chartControl1.Titles.Add(ctnull);
chartControl1.Legend.Visible = true;//不现实指示图
return;
}
#region 循环stockidArray数组
for (int i = 0; i < stockidArray.Length; i++)
{
#region 执行存储过程
int stockid = stockidArray[i];
string sql = "exec Stock_YearOverAge_table '" + OrganizationName + "'," + year + "," + month + "," + stockid + "";
SqlCommand comm = new SqlCommand(sql, conn); //定义执行SQL语句的对象,并将SQL语句赋予对象
SqlDataAdapter da = new SqlDataAdapter(comm); //执行连接
DataSet ds = new DataSet();
da.Fill(ds); //将数据存入数据容器
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count<=0)
{
continue; //如果数据为空,跳出当前循环
}
#endregion
//补全12个月数据表
var dtNew = CompleteYearData(dt, stockidArray);
#region 新建Line
Series sr = new Series(dtNew.Rows[0]["仓库名称"].ToString() + stockid.ToString(), ViewType.Line);//名称与图标的类型
//设置Series样式
sr.ArgumentScaleType = ScaleType.Qualitative;//定性的
sr.ValueScaleType = ScaleType.Numerical;//数字类型
sr.PointOptions.PointView = PointView.ArgumentAndValues;//显示表示的信息和数据
//绑定数据源
sr.DataSource = dtNew.DefaultView;//newdtb是获取到的数据(可以是数据库中的表,也可以是DataTable)
sr.ArgumentDataMember = "月份";//绑定的文字信息(名称)(坐标横轴)
sr.ValueDataMembers[0] = "不常用品种数";//绑定的值(数据)(坐标纵轴)
//在上方显示数值
//sr.LabelsVisibility = DevExpress.Utils.DefaultBoolean.True;
//添加到统计图上
this.chartControl1.Series.Add(sr);
#endregion
}
#endregion
conn.Close();
//图例设置
SimpleDiagram3D diagram = new SimpleDiagram3D
{
RuntimeRotation = true,
RuntimeScrolling = true,
RuntimeZooming = true
};
//设置图表标题
ChartTitle ct = new ChartTitle
{
Text = "" + year + "年呆滞物资趋势统计",
TextColor = Color.FromArgb(86, 86, 86),//颜色
Font = new Font("Tahoma", 12),//字体
Dock = ChartTitleDockStyle.Top,//停靠在上方
Alignment = StringAlignment.Center//居中显示
};
this.chartControl1.Titles.Add(ct);
chartControl1.Legend.Visible = true;//不现实指示图
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
conn.Close();
}
}
Bar
//获取柱形图数据方法
[Obsolete]
public void GetDataBar(string OrganizationName, int year,int month,int day)
{
/*传入参数
OrganizationName //组织名称
year //年度
month //月份
day //超出天数
*/
try
{
//打开数据库连接
conn.Open();
//清理表数据
this.chartControl2.Titles.Clear();
this.chartControl2.Series.Clear();
//获取stockidArray数组
string stockSQL = "exec Stock_MonthOverAge_table '" + OrganizationName + "'," + year + "," + month + "," + day + ",''";
var stockidArray = StockIDArray(stockSQL);
//如果数据为空,返回无超龄数量和空数据
if (stockidArray.Length <= 0)
{
//空表
Series sr = new Series("", ViewType.Bar);
this.chartControl2.Series.Add(sr);
conn.Close();
ChartTitle ctnull = new ChartTitle
{
Text = "" + year + "年" + month + "月份各仓库无超龄数量",
TextColor = Color.FromArgb(0, 128, 0),//颜色
Font = new Font("Tahoma", 14),//字体
Dock = ChartTitleDockStyle.Top,//停靠在上方
Alignment = StringAlignment.Center//居中显示
};
this.chartControl2.Titles.Add(ctnull);
chartControl2.Legend.Visible = true;//不现实指示图
return;
}
for (int i = 0; i < stockidArray.Length; i++)
{
#region 执行存储过程
int stockid = stockidArray[i];
string sql = "exec Stock_MonthOverAge_table '" + OrganizationName + "'," + year + "," + month + "," + day + "," + stockid + "";
SqlCommand comm = new SqlCommand(sql, conn); //定义执行SQL语句的对象,并将SQL语句赋予对象
SqlDataAdapter da = new SqlDataAdapter(comm); //执行连接
DataSet ds = new DataSet();
da.Fill(ds); //将数据存入数据容器
DataTable dt = new DataTable();
dt = ds.Tables[0];
if (dt.Rows.Count <= 0)
{
continue; //如果数据为空,跳出当前循环
}
#endregion
#region 新建Bar
Series sr = new Series("", ViewType.Bar);//名称与图标的类型
//设置Series样式
sr.ArgumentScaleType = ScaleType.Qualitative;//定性的
sr.ValueScaleType = ScaleType.Numerical;//数字类型
sr.PointOptions.PointView = PointView.ArgumentAndValues;//显示表示的信息和数据
//绑定数据源
sr.DataSource = dt.DefaultView;//newdtb是获取到的数据(可以是数据库中的表,也可以是DataTable)
sr.ArgumentDataMember = "仓库名称";//绑定的文字信息(名称)(坐标横轴)
sr.ValueDataMembers[0] = "不常用品种数";//绑定的值(数据)(坐标纵轴)
//在上方显示数值
BarSeriesLabel label = (BarSeriesLabel)sr.Label;
label.Position = BarSeriesLabelPosition.Top;
sr.LabelsVisibility = DevExpress.Utils.DefaultBoolean.True;
//添加到统计图上
this.chartControl2.Series.Add(sr);
#endregion
}
conn.Close();
//图例设置
SimpleDiagram3D diagram = new SimpleDiagram3D();
diagram.RuntimeRotation = true;
diagram.RuntimeScrolling = true;
diagram.RuntimeZooming = true;
//设置图表标题
ChartTitle ct = new ChartTitle();
ct.Text = "" + year + "年" + month + "月份各仓库超龄种类统计";
ct.TextColor = Color.FromArgb(86, 86, 86);//颜色
ct.Font = new Font("Tahoma", 12);//字体
ct.Dock = ChartTitleDockStyle.Top;//停靠在上方
ct.Alignment = StringAlignment.Center;//居中显示
this.chartControl2.Titles.Add(ct);
chartControl2.Legend.Visible = true;//不现实指示图
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
conn.Close();
}
}
方法
1、获取仓库ID数组方法
//获取仓库ID数组方法
public int[] StockIDArray(string stockSQL)
{
#region 获取Stockid数组
//执行存储过程,取到有数据的仓库id
SqlCommand commStock = new SqlCommand(stockSQL, conn); //定义执行SQL语句的对象,并将SQL语句赋予对象
SqlDataAdapter Stockid = new SqlDataAdapter(commStock); //执行连接
DataSet dsStock = new DataSet();
Stockid.Fill(dsStock);
DataTable dtStock = new DataTable();
dtStock = dsStock.Tables[0];
//将stockid传进list赋值给数组
List<int> liststock = new List();
for (int i = 0; i < dtStock.Rows.Count; i++)
{
liststock.Add(int.Parse(dtStock.Rows[i]["FSTOCKID"].ToString()));
}
List<int> listNew = new List();
//删除重复仓位id
foreach (int eachString in liststock)
{
if (!listNew.Contains(eachString))
listNew.Add(eachString);
}
int[] stockID = listNew.ToArray();
#endregion
return stockID;
}
2、补全仓库12个月数据
//补全仓库12个月数据
public DataTable CompleteYearData(DataTable dt, int[] stockidArray)
{
/*传入参数
dt //stockid本年度数据表
stockidArray //stockid集合,如果为空配置空数据
*/
#region 补全12个月数据并排序
for (int m = 1; m <= 12; m++)
{
//月份需补全0才能正确排序
String monthname;
if (m < 10)
{
monthname = "0" + m.ToString() + "月";
}
else
{
monthname = m.ToString() + "月";
}
//判断是否存在当月数据,不存在插入一行当月数据
DataRow[] dataRows = dt.Select("月份" + "='" + monthname + "'");
if (dataRows.Length > 0)
{
continue;
}
if (dataRows.Length <= 0 && stockidArray.Length <= 0)
{
DataRow newRow = dt.NewRow();
newRow["月份"] = monthname;
newRow["不常用品种数"] = 0;
dt.Rows.Add(newRow);
}
else
{
DataRow newRow = dt.NewRow();
newRow["组织名称"] = dt.Rows[0][0];
newRow["仓库名称"] = dt.Rows[0][1];
newRow["FSTOCKID"] = dt.Rows[0][2];
newRow["月份"] = monthname;
newRow["不常用品种数"] = 0;
dt.Rows.Add(newRow);
}
}
dt.DefaultView.Sort = "月份 ASC";//按月份倒序
dt = dt.DefaultView.ToTable();//返回一个新的DataTable
#endregion
return dt;
}