private void LoadData(string name)
{
ArrayList al = new ArrayList();
if (!this.ForExit)
{
if (name == "")
{
al = DBLayer.Clients.GetListForInput();
}
else
{
al = DBLayer.Clients.GetListForInputByName(name);
}
}
else
{
if (name == "")
{
al = DBLayer.Visits.GetListOnline();
}
else
{
al = DBLayer.Clients.GetListByName(name);
}
}
DataTable dt = new DataTable();
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("FIO");
for (int i = 0; i < al.Count; i++)
{
DataRow dr = dt.Rows.Add();
if (!ForExit)
{
DBLayer.Clients.Clients_WideDetails det = (DBLayer.Clients.Clients_WideDetails)al[i];
dr["Id"] = det.Id;
dr["FIO"] = det.FIO;
}
else
{
DBLayer.Visits.Visits_WideDetails detV = (DBLayer.Visits.Visits_WideDetails)al[i];
dr["Id"] = detV.ClientId;
dr["FIO"] = detV.ClientName;
}
}
gridControl1.DataSource = dt;
advBandedGridView1.BestFitColumns();
}
public void LoadData()
{
ArrayList al = DBLayer.Visits.GetList(Date1, Date2);
DataTable dt = new DataTable();
dt.Columns.Add("ClientID", typeof(int));
dt.Columns.Add("FIO");
dt.Columns.Add("Time");
dt.Columns.Add("Card");
dt.Columns.Add("AbonementName");
dt.Columns.Add("BoxType");
dt.Columns.Add("Number", typeof(int));
dt.Columns.Add("Coach");
dt.Columns.Add("Date", typeof(DateTime));
dt.Columns.Add("TimeOff");
for (int i = 0; i < al.Count; i++)
{
DataRow dr = dt.Rows.Add();
DBLayer.Visits.Visits_WideDetails det = (DBLayer.Visits.Visits_WideDetails)al[i];
dr["ClientID"] = det.ClientId;
dr["FIO"] = det.ClientName;
dr["Time"] = det.Time;
dr["Date"] = det.Date;
if (det.WithoutKey)
{
dr["Card"] = "Без карты";
}
dr["Number"] = det.Number.ToString();
dr["AbonementName"] = det.AbonementName;
dr["BoxType"] = det.BoxType;
dr["Coach"] = det.CoachName;
dr["TimeOff"] = det.TimeOff;
}
grClients.DataSource = dt;
slblTotal.Text = al.Count.ToString();
advBandedGridView1.BestFitColumns();
}
public static ArrayList GetList(DateTime date1, DateTime date2)
{
string sql = "SELECT DISTINCT v.*, b.[Number], c.FIO, a.[Name] AS Abonement, b.Sex, b.[Type] AS BoxType, co.[Name] AS CoachName FROM Visits AS v ";
sql += " INNER JOIN Boxes As b on b.[Id] = v.BoxId INNER JOIN Clients As c ON c.[Id] = v.ClientID ";
sql += " INNER JOIN ClientsAbonements As ca ON c.[Id] = ca.ClientId AND v.[Date] BETWEEN ca.DateStart AND DATEADD(day, 1, ca.DateFinish)";
sql += " INNER JOIN Abonements AS a ON ca.AbonementId = a.[Id]";
sql += " LEFT JOIN Coaches AS co ON v.CoachId = co.[Id] ";
sql += " WHERE v.TimeOff <> '' ";
sql += " AND v.[Date] BETWEEN '" + date1.ToString("yyyyMMdd") + "' AND '" + date2.ToString("yyyyMMdd") + "'";
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.Visits.Visits_WideDetails det = new DBLayer.Visits.Visits_WideDetails();
det.BoxId = Convert.ToInt32(dr["BoxId"]);
det.ClientId = Convert.ToInt32(dr["ClientID"]);
det.ClientName = dr["FIO"].ToString();
det.Date = Convert.ToDateTime(dr["Date"]);
det.DateVisit = Convert.ToDateTime(dr["Date"]);
det.Id = Convert.ToInt32(dr["Id"]);
det.Number = Convert.ToInt32(dr["Number"]);
det.Time = dr["Time"].ToString();
det.TimeOff = dr["TimeOff"].ToString();
det.Type = Convert.ToInt32(dr["Type"]);
det.WithoutKey = Convert.ToBoolean(dr["WithoutKey"]);
det.CoachName = dr["CoachName"].ToString();
string sex = "";
if (Convert.ToInt32(dr["Sex"]) == 0)
{
sex = "Ж";
}
else
{
sex = "М";
}
det.BoxType = sex;
det.AbonementName = dr["Abonement"].ToString();
al.Add(det);
}
return(al);
}
public static ArrayList GetListOnline(string name)
{
string sql = "SELECT v.*, b.[Number], c.FIO, a.[Name] AS Abonement, b.Sex, b.[Type] AS BoxType, co.[Name] AS CoachName FROM Visits AS v ";
sql += " INNER JOIN Boxes As b on b.[Id] = v.BoxId INNER JOIN Clients As c ON c.[Id] = v.ClientID ";
sql += " INNER JOIN ClientsAbonements As ca ON c.[Id] = ca.ClientId ";
sql += " INNER JOIN Abonements AS a ON ca.AbonementId = a.[Id]";
sql += " LEFT JOIN Coaches AS co ON v.CoachId = co.[Id] ";
sql += " WHERE v.TimeOff = '' AND c.FIO LIKE '%" + name + "%'";
DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql);
ArrayList al = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
DBLayer.Visits.Visits_WideDetails det = new DBLayer.Visits.Visits_WideDetails();
det.BoxId = Convert.ToInt32(dr["BoxId"]);
det.ClientId = Convert.ToInt32(dr["ClientID"]);
det.ClientName = dr["FIO"].ToString();
det.Date = Convert.ToDateTime(dr["Date"]);
det.DateVisit = Convert.ToDateTime(dr["Date"]);
det.Id = Convert.ToInt32(dr["Id"]);
det.Number = Convert.ToInt32(dr["Number"]);
det.Time = dr["Time"].ToString();
det.Type = Convert.ToInt32(dr["Type"]);
det.WithoutKey = Convert.ToBoolean(dr["WithoutKey"]);
det.CoachName = dr["CoachName"].ToString();
string sex = "";
if (Convert.ToInt32(dr["Sex"]) == 0)
{
sex = "Ж";
}
else
{
sex = "М";
}
det.BoxType = sex + " " + dr["BoxType"].ToString();
det.AbonementName = dr["Abonement"].ToString();
al.Add(det);
}
return(al);
}