private void CheckOutStatistic_Load(object sender, EventArgs e) { CheckOutStatisticDomain checkOutStatisticDomain = checkOutService.getCheckOutDetailsFee(DateTime.Now.Year); List <ObservablePoint> observablePoints = new List <ObservablePoint>(); if (checkOutStatisticDomain.January != -1) { observablePoints.Add(new ObservablePoint(1, checkOutStatisticDomain.January)); } else { goto A; } if (checkOutStatisticDomain.February != -1) { observablePoints.Add(new ObservablePoint(2, checkOutStatisticDomain.February)); } else { goto A; } if (checkOutStatisticDomain.March != -1) { observablePoints.Add(new ObservablePoint(3, checkOutStatisticDomain.March)); } else { goto A; } if (checkOutStatisticDomain.April != -1) { observablePoints.Add(new ObservablePoint(4, checkOutStatisticDomain.April)); } else { goto A; } if (checkOutStatisticDomain.May != -1) { observablePoints.Add(new ObservablePoint(5, checkOutStatisticDomain.May)); } else { goto A; } if (checkOutStatisticDomain.June != -1) { observablePoints.Add(new ObservablePoint(6, checkOutStatisticDomain.June)); } else { goto A; } if (checkOutStatisticDomain.July != -1) { observablePoints.Add(new ObservablePoint(7, checkOutStatisticDomain.July)); } else { goto A; } if (checkOutStatisticDomain.August != -1) { observablePoints.Add(new ObservablePoint(8, checkOutStatisticDomain.August)); } else { goto A; } if (checkOutStatisticDomain.September != -1) { observablePoints.Add(new ObservablePoint(9, checkOutStatisticDomain.September)); } else { goto A; } if (checkOutStatisticDomain.October != -1) { observablePoints.Add(new ObservablePoint(10, checkOutStatisticDomain.October)); } else { goto A; } if (checkOutStatisticDomain.November != -1) { observablePoints.Add(new ObservablePoint(11, checkOutStatisticDomain.November)); } else { goto A; } if (checkOutStatisticDomain.December != -1) { observablePoints.Add(new ObservablePoint(12, checkOutStatisticDomain.December)); } else { goto A; } A : cartesianChart1.Series = new LiveCharts.SeriesCollection { new LineSeries { Values = new ChartValues <ObservablePoint>(observablePoints), PointGeometrySize = 15 } }; }
public CheckOutStatisticDomain getCheckOutDetailsFee(int year) { con = SqlServerConnection.getConnnection(); con.Open(); CheckOutStatisticDomain checkOutStatisticDomain = null; StringBuilder sql = new StringBuilder(); sql.Append("select" + " ( " + " case when 1 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 1 then isnull(c.fee, 0) end), 0) " + " else -1 end " + " ) as thang1, " + " ( " + " case when 2 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 2 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang2, " + " ( " + " case when 3 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 3 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang3, " + " ( " + " case when 4 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 4 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang4, " + " ( " + " case when 5 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 5 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang5, " + " ( " + " case when 6 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 6 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang6, " + " ( " + " case when 7 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 7 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang7, " + " ( " + " case when 8 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 8 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang8, " + " ( " + " case when 9 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 9 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang9, " + " ( " + " case when 10 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 10 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang10, " + " ( " + " case when 11 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 11 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang11, " + " ( " + " case when 12 <= @maxMonth then isnull(sum(case when month(c.CheckOutDate) = 12 then isnull(c.fee,0) end),0) " + " else -1 end " + " ) as thang12 " + " from Check_Out c " + " where YEAR(c.CheckOutDate) = @year " ); SqlCommand command = new SqlCommand(sql.ToString(), con); command.Parameters.AddWithValue("year", year); if (year < DateTime.Now.Year) { command.Parameters.AddWithValue("maxMonth", 12); } else { command.Parameters.AddWithValue("maxMonth", DateTime.Now.Month); } SqlDataReader data = command.ExecuteReader(); while (data.Read()) { checkOutStatisticDomain = new CheckOutStatisticDomain(); checkOutStatisticDomain.January = data.GetSqlMoney(0).ToDouble(); checkOutStatisticDomain.February = data.GetSqlMoney(1).ToDouble(); checkOutStatisticDomain.March = data.GetSqlMoney(2).ToDouble(); checkOutStatisticDomain.April = data.GetSqlMoney(3).ToDouble(); checkOutStatisticDomain.May = data.GetSqlMoney(4).ToDouble(); checkOutStatisticDomain.June = data.GetSqlMoney(5).ToDouble(); checkOutStatisticDomain.July = data.GetSqlMoney(6).ToDouble(); checkOutStatisticDomain.August = data.GetSqlMoney(7).ToDouble(); checkOutStatisticDomain.September = data.GetSqlMoney(8).ToDouble(); checkOutStatisticDomain.October = data.GetSqlMoney(9).ToDouble(); checkOutStatisticDomain.November = data.GetSqlMoney(10).ToDouble(); checkOutStatisticDomain.December = data.GetSqlMoney(11).ToDouble(); } data.Close(); con.Close(); return(checkOutStatisticDomain); }