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