private void LoadData() { ArrayList al = DBLayer.AbonementIncome.GetList(0, Date1, Date2); DataTable dt = new DataTable(); dt.Columns.Add("AbonementName"); dt.Columns.Add("CoachName"); dt.Columns.Add("Weekday"); dt.Columns.Add("Time"); dt.Columns.Add("Client"); dt.Columns.Add("User"); dt.Columns.Add("Date", typeof(DateTime)); dt.Columns.Add("Summ", typeof(double)); dt.Columns.Add("IsDeleted"); dt.Columns.Add("DeleteDate", typeof(DateTime)); dt.Columns.Add("DeleteReason"); dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("AbonementType"); int total = 0; double totalSumm = 0; for (int i = 0; i < al.Count; i++) { DBLayer.AbonementIncome.AbonementIncome_WideDetails det = (DBLayer.AbonementIncome.AbonementIncome_WideDetails)al[i]; DataRow dr = dt.Rows.Add(); if (!det.IsDeleted) { totalSumm += det.Summ; total++; } dr["AbonementName"] = det.AbonementName; dr["Client"] = det.FIO; dr["User"] = det.User; dr["Date"] = det.Date; dr["Summ"] = det.Summ; dr["CoachName"] = det.CoachName; dr["Weekday"] = det.Weekday; dr["Time"] = det.Time; if (det.IsDeleted) { dr["IsDeleted"] = "Удален"; } if (det.DeleteDate != DateTime.MinValue) { dr["DeleteDate"] = det.DeleteDate; } dr["DeleteReason"] = det.DeleteReason; dr["Id"] = det.Id; string name = ""; switch (det.AbonementGroup) { case 0: name = "Тренажерный зал"; break; case 1: name = "Аэробный зал"; break; default: name = ""; break; } dr["AbonementType"] = name; } grSales.DataSource = dt; slblTotal.Text = total.ToString(); slblTotalMoney.Text = totalSumm.ToString(); advBandedGridView1.BestFitColumns(); }
public static ArrayList GetFitnessList(int id, DateTime date1, DateTime date2) { string sql = "SELECT DISTINCT a.AbonementGroup, ai.[Date], ai.[Summ], ai.[Id], c.FIO, a.[Name] AS AbonementName, u.FIO AS [User], ai.ClientId, ai.AbonementId, ai.UserId, ai.IsDeleted, ai.DeleteDate, ai.DeleteReason, co.Name AS CoachName, ca.Time, ca.Weekday "; sql += " FROM AbonementIncome AS ai INNER JOIN Clients AS c ON c.[Id] = ai.CLientId "; sql += " INNER JOIN Abonements AS a ON a.[Id] = ai.AbonementId "; sql += " LEFT JOIN ClientsAbonements AS ca ON ai.ClientAbonementId = ca.Id "; sql += " LEFT JOIN Coaches AS co ON co.[Id] = ca.[CoachId] "; sql += " INNER JOIN Users AS u ON u.[Id] = ai.[UserId] "; sql += " WHERE ai.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "' AND a.AbonementGroup = 0 "; sql += " ORDER BY ai.[Date]"; DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql); ArrayList al = new ArrayList(); foreach (DataRow dr in dt.Rows) { DBLayer.AbonementIncome.AbonementIncome_WideDetails det = new DBLayer.AbonementIncome.AbonementIncome_WideDetails(); if (!dr.IsNull("Id")) { det.Id = Convert.ToInt32(dr["Id"]); } det.AbonementName = dr["AbonementName"].ToString(); det.FIO = dr["FIO"].ToString(); det.User = dr["User"].ToString(); if (!dr.IsNull("ClientId")) { det.ClientId = Convert.ToInt32(dr["ClientId"]); } if (!dr.IsNull("AbonementId")) { det.AbonementId = Convert.ToInt32(dr["AbonementId"]); } if (!dr.IsNull("UserId")) { det.UserId = Convert.ToInt32(dr["UserId"]); } if (!dr.IsNull("Date")) { det.Date = Convert.ToDateTime(dr["Date"]); } if (!dr.IsNull("Summ")) { det.Summ = Convert.ToDouble(dr["Summ"]); } if (!dr.IsNull("IsDeleted")) { det.IsDeleted = Convert.ToBoolean(dr["IsDeleted"]); } if (!dr.IsNull("DeleteDate")) { det.DeleteDate = Convert.ToDateTime(dr["DeleteDate"]); } det.DeleteReason = dr["DeleteReason"].ToString(); if (!dr.IsNull("AbonementGroup")) { det.AbonementGroup = Convert.ToInt32(dr["AbonementGroup"]); } det.CoachName = dr["CoachName"].ToString(); det.Weekday = dr["Weekday"].ToString(); det.Time = dr["Time"].ToString(); al.Add(det); } return(al); }