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