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