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 static ArrayList GetOnlineList() { string sql = "SELECT c.Barcode, c.Id FROM Clients AS c INNER JOIN Visits AS v ON v.[ClientId] = c.[Id] "; sql += " INNER JOIN ClientsAbonements AS ca ON ca.ClientId = c.[Id] AND GETDATE() BETWEEN ca.DateStart AND DATEADD(day, 1, ca.DateFinish) "; sql += " WHERE v.TimeOff = '' AND GETDATE() BETWEEN ca.DateStart AND DATEADD(day, 1, ca.DateFinish) "; DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql); ArrayList al = new ArrayList(); foreach (DataRow dr in dt.Rows) { DBLayer.Clients.Clients_WideDetails det = new DBLayer.Clients.Clients_WideDetails(); det.Barcode = dr["Barcode"].ToString(); det.Id = Convert.ToInt32(dr["Id"]); al.Add(det); } return(al); }
private void LoadData() { ArrayList al = new ArrayList(); if (tbName.Text.Trim() == "") { if (tbBarcode.Text.Trim() == "") { al = DBLayer.Clients.GetList(); } else { al = DBLayer.Clients.GetListByBarcode(tbBarcode.Text); } } else { al = DBLayer.Clients.GetListByName(tbName.Text); } DataTable dt = new DataTable(); dt.Columns.Add("ClientType"); dt.Columns.Add("DocumentType"); dt.Columns.Add("Source"); dt.Columns.Add("VisitsStatus"); dt.Columns.Add("FIO"); dt.Columns.Add("Card"); dt.Columns.Add("RegisterDate", typeof(DateTime)); dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("VisitsCount", typeof(int)); dt.Columns.Add("AbonementsVisitsCount", typeof(int)); dt.Columns.Add("Phone"); dt.Columns.Add("Sex"); dt.Columns.Add("AbonementName"); dt.Columns.Add("FinishDate", typeof(DateTime)); dt.Columns.Add("Type"); dt.Columns.Add("Coach"); for (int i = 0; i < al.Count; i++) { DBLayer.Clients.Clients_WideDetails det = (DBLayer.Clients.Clients_WideDetails)al[i]; DataRow dr = dt.Rows.Add(); if (det.AVisitsCount > 0) { dr["AbonementsVisitsCount"] = det.AVisitsCount; } dr["Card"] = det.Barcode; dr["Id"] = det.Id; dr["ClientType"] = det.TypeName; dr["Coach"] = det.CoachName; dr["DocumentType"] = det.DocumentName; dr["Source"] = det.SourceName; dr["FIO"] = det.FIO; dr["RegisterDate"] = det.RegisterDate; dr["Phone"] = det.Phone; string visitsStatus = string.Empty; int vC = det.AVisitsCount - det.VisitsCount; if (det.AVisitsCount > 0) { if (det.VisitsCount == 0) { visitsStatus = "Посещения закончились"; } if (det.VisitsCount < 0) { visitsStatus = string.Empty; } if (det.VisitsCount == 1) { visitsStatus = "Последнее посещение"; } if (vC > 1) { visitsStatus = string.Empty; } } dr["VisitsStatus"] = visitsStatus; if (det.Sex == 0) { dr["Sex"] = "Женщина"; } else { dr["Sex"] = "Мужчина"; } dr["FinishDate"] = det.FinishDate; dr["AbonementName"] = det.AbonementName; if (det.VisitsCount > 0) { dr["VisitsCount"] = det.VisitsCount; } string type = ""; if (((DateTime.Now - det.FinishDate).Days > -7) && ((DateTime.Now - det.FinishDate).Days < 0)) { type = "Скоро заканчивается"; } else if ((DateTime.Now - det.FinishDate).Days >= 0) { type = "Просрочен"; } else { type = "Текущий"; } dr["Type"] = type; } grClients.DataSource = dt; advBandedGridView1.BestFitColumns(); try { slblTotal.Text = advBandedGridView1.RowCount.ToString(); } catch { } }
public static ArrayList GetList() { string sql = "SELECT c.FIO, c.[Id], c.Barcode, co.[Name] AS CoachName, dt.[Name] AS Document, s.[Name] AS Source, ct.[Name] AS ClientType, c.DocumentNumber, c.BirthDate, c.DocumentId, c.RegisterDate, c.SourceId, c.TypeId, c.Phone, c.Sex, ca.DateFinish, a.[Name] AS AbonementName, ca.VisitsCount, a.LessonsCount AS AVC "; sql += " FROM Clients AS c INNER JOIN ClientTypes AS ct ON c.[TypeId] = ct.[Id] "; sql += " INNER JOIN DocumentType AS dt ON dt.[Id] = c.[DocumentId] "; sql += " INNER JOIN AdvertisingSource AS s ON s.[Id] = c.[SourceId] "; sql += " LEFT JOIN ClientsAbonements AS ca ON ca.CLientId = c.[Id] AND ca.IsActual = 1 "; sql += " LEFT JOIN Coaches AS co ON co.Id = ca.[CoachId] "; sql += " LEFT JOIN Abonements AS a ON a.[Id] = ca.[AbonementId] "; //sql += " WHERE getdate() BETWEEN ca.DateStart AND ca.DateFinish"; sql += " WHERE a.IsSingle IS NULL OR a.IsSingle = 0"; DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql); ArrayList al = new ArrayList(); foreach (DataRow dr in dt.Rows) { DBLayer.Clients.Clients_WideDetails det = new DBLayer.Clients.Clients_WideDetails(); if (!dr.IsNull("Id")) { det.Id = Convert.ToInt32(dr["Id"]); } det.Barcode = dr["Barcode"].ToString(); det.DocumentNumber = dr["DocumentNumber"].ToString(); if (!dr.IsNull("DocumentId")) { det.DocumentId = Convert.ToInt32(dr["DocumentId"]); } det.FIO = dr["FIO"].ToString(); if (!dr.IsNull("RegisterDate")) { det.RegisterDate = Convert.ToDateTime(dr["RegisterDate"]); } if (!dr.IsNull("BirthDate")) { det.BirthDate = Convert.ToDateTime(dr["BirthDate"]); } if (!dr.IsNull("SourceId")) { det.SourceId = Convert.ToInt32(dr["SourceId"]); } if (!dr.IsNull("TypeId")) { det.TypeId = Convert.ToInt32(dr["TypeId"]); } if (!dr.IsNull("AVC")) { det.AVisitsCount = Convert.ToInt32(dr["AVC"]); } det.DocumentName = dr["Document"].ToString(); det.TypeName = dr["ClientType"].ToString(); det.SourceName = dr["Source"].ToString(); det.Phone = dr["Phone"].ToString(); if (!dr.IsNull("Sex")) { det.Sex = Convert.ToInt32(dr["Sex"]); } if (!dr.IsNull("VisitsCount")) { det.VisitsCount = Convert.ToInt32(dr["VisitsCount"]); } det.AbonementName = dr["AbonementName"].ToString(); if (!dr.IsNull("DateFinish")) { det.FinishDate = Convert.ToDateTime(dr["DateFinish"]); } det.CoachName = dr["CoachName"].ToString(); al.Add(det); } return(al); }
public static ArrayList GetListForInputByName(string name) { string sql = "SELECT c.FIO, c.[Id], c.Barcode, dt.[Name] AS Document, s.[Name] AS Source, ct.[Name] AS ClientType, c.DocumentNumber, c.BirthDate, c.DocumentId, c.RegisterDate, c.SourceId, c.TypeId, c.Phone, c.Sex "; sql += " FROM Clients AS c INNER JOIN ClientTypes AS ct ON c.[TypeId] = ct.[Id] "; sql += " INNER JOIN DocumentType AS dt ON dt.[Id] = c.[DocumentId] "; sql += " INNER JOIN AdvertisingSource AS s ON s.[Id] = c.[SourceId] WHERE FIO LIKE '%" + name + "%' AND c.[Id] NOT IN (SELECT ClientId FROM Visits WHERE TimeOff = '')"; DataTable dt = ZFort.DB.Execute.ExecuteString_DataTable(sql); ArrayList al = new ArrayList(); foreach (DataRow dr in dt.Rows) { DBLayer.Clients.Clients_WideDetails det = new DBLayer.Clients.Clients_WideDetails(); if (!dr.IsNull("Id")) { det.Id = Convert.ToInt32(dr["Id"]); } det.Barcode = dr["Barcode"].ToString(); det.DocumentNumber = dr["DocumentNumber"].ToString(); if (!dr.IsNull("DocumentId")) { det.DocumentId = Convert.ToInt32(dr["DocumentId"]); } det.FIO = dr["FIO"].ToString(); if (!dr.IsNull("RegisterDate")) { det.RegisterDate = Convert.ToDateTime(dr["RegisterDate"]); } if (!dr.IsNull("BirthDate")) { det.BirthDate = Convert.ToDateTime(dr["BirthDate"]); } if (!dr.IsNull("SourceId")) { det.SourceId = Convert.ToInt32(dr["SourceId"]); } if (!dr.IsNull("TypeId")) { det.TypeId = Convert.ToInt32(dr["TypeId"]); } det.DocumentName = dr["Document"].ToString(); det.TypeName = dr["ClientType"].ToString(); det.SourceName = dr["Source"].ToString(); det.Phone = dr["Phone"].ToString(); if (!dr.IsNull("Sex")) { det.Sex = Convert.ToInt32(dr["Sex"]); } al.Add(det); } return(al); }