public List<Device> getDevices()
{
string sql = "select * from bu_device INNER JOIN bu_type ON bu_device.typeId=bu_type.Id INNER JOIN bu_brand ON bu_device.brandId=bu_brand.Id INNER JOIN bu_cell ON bu_device.cellId=bu_cell.Id";
DataSet ds = new MySqlDa().fillDataSet(sql);
List<Device> lstDevice = new List<Device>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Device device = fillDevice(ds,i);
Brand brand = new Brand();
brand.Id=(int)ds.Tables[0].Rows[i]["brandId"];
brand.Name = ds.Tables[0].Rows[i][9].ToString();
device.Brand = brand;
BackupAMSData.Type type = new BackupAMSData.Type();
type.Id = (int)ds.Tables[0].Rows[i]["typeId"];
type.Name = ds.Tables[0].Rows[i][7].ToString();
device.Type = type;
Cell cell = new Cell();
cell.Id = (int)ds.Tables[0].Rows[i]["cellId"];
cell.Name=ds.Tables[0].Rows[i][12].ToString();
cell.CellNr = (int)ds.Tables[0].Rows[i]["CellNr"];
device.Cell = cell;
lstDevice.Add(device);
}
return lstDevice;
}
public TaskData GetTask(int taskId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from bu_task where id =" + taskId);
DataSet ds = new MySqlDa().fillDataSet(sql.ToString());
TaskData task = fillTask(ds,0);
return task;
}
public Device GetDevice(int id)
{
string sql = "select * from bu_device where id="+id;
DataSet ds = new MySqlDa().fillDataSet(sql);
Device device = fillDevice(ds, 0);
return device;
}
public bool checkAuthentication(string userName, string password)
{
DataSet ds = new MySqlDa().fillDataSet("SELECT * FROM bu_user where userName='******'");
string bu_userName = ds.Tables[0].Rows[0]["userName"].ToString();
string bu_password = ds.Tables[0].Rows[0]["password"].ToString();
if (bu_userName.Equals(userName) && bu_password.Equals(password))
return true;
else
return false;
}
public List<Cell> GetCells()
{
string sql = "SELECT * FROM ams_backup.bu_cell";
DataSet ds = new MySqlDa().fillDataSet(sql);
List<Cell> lstCells = new List<Cell>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Cell cell = fillCell(ds, i);
lstCells.Add(cell);
}
return lstCells;
}
public List<Device> GetDevicesByCell(int cellId)
{
string sql = "select * from bu_device where cellId=" + cellId;
DataSet ds = new MySqlDa().fillDataSet(sql);
List<Device> lstDevice = new List<Device>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Device device = fillDevice(ds, i);
lstDevice.Add(device);
}
return lstDevice;
}
public List<BackupAMSData.Brand> GetBrands()
{
string sql = "select *from bu_brand";
DataSet ds = new MySqlDa().fillDataSet(sql);
List<BackupAMSData.Brand> lstBrands = new List<BackupAMSData.Brand>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
BackupAMSData.Brand brand = new BackupAMSData.Brand();
brand.Id = (int)ds.Tables[0].Rows[i]["id"];
brand.Name = ds.Tables[0].Rows[i]["name"].ToString();
lstBrands.Add(brand);
}
return lstBrands;
}
public List<BackupAMSData.Type> GetTypes()
{
string sql = "select *from bu_type";
DataSet ds = new MySqlDa().fillDataSet(sql);
List<BackupAMSData.Type> lstTypes = new List<BackupAMSData.Type>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
BackupAMSData.Type type = new BackupAMSData.Type();
type.Id = (int)ds.Tables[0].Rows[i]["id"];
type.Name = ds.Tables[0].Rows[i]["name"].ToString();
lstTypes.Add(type);
}
return lstTypes;
}
public int Save(TaskDeviceData taskDeviceData)
{
StringBuilder sql = new StringBuilder();
if (taskDeviceData.Id > 0)
{
throw new NotImplementedException();
}
else
{
sql.Append("INSERT INTO bu_task_device (bu_task_device.bu_device_id,bu_task_device.bu_task_id) VALUES ('");
sql.Append(taskDeviceData.Bu_device_id + ",");
sql.Append(taskDeviceData.Bu_task_id);
sql.Append(") SET @id = SCOPE_IDENTITY();");
}
MySqlParameterCollection sqlCollection = new MySqlDa().ExecuteNonQuery(sql.ToString());
return (int)sqlCollection["@id"].Value;
}
public List<TaskData> getTaskByWeek(DateTime dt)
{
DateTime wkStDt = DateTime.MinValue;
DateTime wkEndDt = DateTime.MinValue;
wkStDt = dt.AddDays(1 - Convert.ToDouble(dt.DayOfWeek));
wkEndDt = dt.AddDays(7 - Convert.ToDouble(dt.DayOfWeek));
List<DateTime> lstWeekDates = Enumerable.Range(0, 1 + wkEndDt.Subtract(wkStDt).Days)
.Select(offset => wkStDt.AddDays(offset)).ToList<DateTime>();
StringBuilder sql = new StringBuilder();
sql.Append("select * from bu_task ");
//if (deepLinking)
//sql.Append(" INNER JOIN bu_device ON bu_task.bu_device_id=bu_device.id ");
sql.Append("where( ");
// Specifieke Datum , Lijst van deze week
sql.Append("datefield IN ("); //'20120919', '20120920'
string temp = "";
for (int i = 0; i < lstWeekDates.Count; i++)
{
DateTime date = lstWeekDates[i];
temp += date.Year.ToString() + date.Month.ToString("d2") + date.Day.ToString("d2") + ",";
}
temp=temp.TrimEnd(',');
sql.Append(temp);
sql.Append(" ) ");
// Dagelijks of weekelijkse herhaling
sql.Append("or bu_task.repeat IN (1,2)");
// Maandelijkse herhaling
sql.Append("or bu_task.repeat =3 and repeatDay IN ( ");
temp = "";
for (int i = 0; i < lstWeekDates.Count; i++)
{
DateTime date = lstWeekDates[i];
temp += date.Day.ToString() + ",";
}
temp = temp.TrimEnd(',');
sql.Append(temp);
sql.Append(" )");
// Jaarlijkse herhaling
sql.Append("or bu_task.repeat =4 and repeatDay IN( ");
sql.Append(temp);
sql.Append(" )");
sql.Append("and repeatMonth IN (");
string month = "0";
temp = "";
for (int i = 0; i < lstWeekDates.Count; i++)
{
DateTime date = lstWeekDates[i];
if (month != date.Month.ToString())
{
month = date.Month.ToString("d2");
temp += date.Month.ToString("d2") + ",";
}
}
temp = temp.TrimEnd(',');
sql.Append(temp+")");
sql.Append(")");
//Get all jobs for this week
DataSet ds = new MySqlDa().fillDataSet(sql.ToString());
List<TaskData> lstTaskData = new List<TaskData>();
//Create Object
/*for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
TaskData task = fillTask(ds,i);
if (deepLinking)
{
Device device = new Device();
device.Id = (int)ds.Tables[0].Rows[i]["bu_task_device_id"];
device.IP = ds.Tables[0].Rows[i]["ip"].ToString();
device.Name = ds.Tables[0].Rows[i]["name"].ToString();
device.TypeId = (int)ds.Tables[0].Rows[i]["typeId"];
device.BrandId = (int)ds.Tables[0].Rows[i]["brandId"];
device.CellId = (int)ds.Tables[0].Rows[i]["cellId"];
task.devices.Add(device);
}
lstTaskData.Add(task);
}*/
return lstTaskData;
}
public int Save(TaskData taskData)
{
StringBuilder sql = new StringBuilder();
if (taskData.Id > 0)
{
/*sql.Append("UPDATE bu_task SET datefield='");
sql.Append(taskData.Date.Year+""+taskData.Date.Month+""+taskData.Date.Day);
sql.Append("',timefield="); sql.Append(taskData.Date.Hour+":"+taskData.Date.Minute);
sql.Append("',repeat='"); sql.Append(taskData.Repeat);
sql.Append("',repeatDayOftheWeek='"); sql.Append(taskData.RepeatDayOftheWeek);
sql.Append("',repeatDay='"); sql.Append(taskData.RepeatDay);
sql.Append("',repeatMonth='"); sql.Append(taskData.RepeatMonth);
sql.Append("',bu_device_id='"); sql.Append(taskData.DeviceId);
sql.Append("' WHERE id="); sql.Append(taskData.Id);*/
throw new NotImplementedException();
}
else
{
sql.Append("INSERT INTO bu_task (bu_task.datefield, bu_task.timefield, bu_task.repeat,bu_task.repeatDayOftheWeek,bu_task.repeatDay,bu_task.repeatMonth) VALUES ('");
sql.Append(taskData.Date.Year+ "" + taskData.Date.Month.ToString("D2") + "" + taskData.Date.Day.ToString("D2") + "','");
sql.Append(taskData.Date.Hour.ToString("D2") + ":" + taskData.Date.Minute.ToString("D2") + "',");
sql.Append(taskData.Repeat + ",");
sql.Append(taskData.RepeatDayOftheWeek + ",");
sql.Append(taskData.RepeatDay + ",");
sql.Append(taskData.RepeatMonth);
sql.Append(");");
}
sss
MySqlParameterCollection sqlCollection = new MySqlDa().ExecuteNonQuery(sql.ToString());
return (int)sqlCollection["@id"].Value;
}
public List<TaskData> getTasks(DateTime date)
{
//Get all jobs for today from when the server is started
StringBuilder SQLString = new StringBuilder();
SQLString.Append("SELECT * FROM bu_task where (datefield='"+date.ToString("yyyyMMdd")+"' ");
SQLString.Append("and time(timefield) > '"+date.Hour.ToString("d2")+":"+date.Minute.ToString("d2")+"' and bu_task.repeat=0) or");
SQLString.Append("(bu_task.repeat='1' and time(timefield) > '"+date.Hour.ToString("d2")+":"+date.Minute.ToString("d2")+"') or");
SQLString.Append("(repeatDayOfTheWeek ='"+((int)date.DayOfWeek).ToString() +"' and bu_task.repeat=2 and time(timefield) > '"+date.Hour.ToString("d2")+":"+date.Minute.ToString("d2")+"') or ");
SQLString.Append("(repeatDay ='"+date.ToString("dd") +"' and bu_task.repeat=3 and time(timefield) > '"+date.Hour.ToString("d2")+":"+date.Minute.ToString("d2")+"') or");
SQLString.Append("(repeatDay ='"+date.ToString("dd")+"' and repeatMonth='"+date.ToString("MM")+"' and bu_task.repeat=4 and time(timefield) > '"+date.Hour.ToString("d2")+":"+date.Minute.ToString("d2")+"')");
DataSet ds = new MySqlDa().fillDataSet(SQLString.ToString());
List<TaskData> lstTaskData = new List<TaskData>();
//Create Object
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
TaskData task = new TaskData();
task.Id = (int)ds.Tables[0].Rows[i]["id"];
string sDate = ds.Tables[0].Rows[i]["datefield"].ToString().Split(' ')[0] + " " + ds.Tables[0].Rows[i]["timefield"];
task.Date = DateTime.Parse(sDate);
task.Repeat = (int)ds.Tables[0].Rows[i]["repeat"];
task.RepeatDayOftheWeek = (int)ds.Tables[0].Rows[i]["repeatDayOftheWeek"];
task.RepeatDay = (int)ds.Tables[0].Rows[i]["repeatDay"];
task.RepeatMonth = (int)ds.Tables[0].Rows[i]["repeatMonth"];
lstTaskData.Add(task);
}
return lstTaskData;
}
public List<Device> getDevicesByTime(DateTime date)
{
string sql = "select * from bu_task inner join bu_task_device on bu_task.id = bu_task_device.bu_task_id where timefield ='" + date.ToString("HH:mm") + "' and datefield='" + date.ToString("yyyMMdd") + "'";
DataSet ds = new MySqlDa().fillDataSet(sql);
List<Device> lstDevice = new List<Device>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Device device = new Device();
device.Id = (int)ds.Tables[0].Rows[i]["id"];
device.IP = ds.Tables[0].Rows[i]["ip"].ToString();
device.Name = ds.Tables[0].Rows[i]["name"].ToString();
device.TypeId = (int)ds.Tables[0].Rows[i]["typeId"];
device.BrandId = (int)ds.Tables[0].Rows[i]["brandId"];
device.CellId = (int) ds.Tables[0].Rows[i]["cellId"];
Cell cell = new Cell();
cell.Id = (int)ds.Tables[0].Rows[i]["cellId"];
cell.Name = ds.Tables[0].Rows[i][16].ToString();
cell.CellNr = (int)ds.Tables[0].Rows[i]["CellNr"];
device.Cell = cell;
lstDevice.Add(device);
}
return lstDevice;
}
public List<Device> GetDevicesFromTask(int taskId)
{
//dateTime.ToString("yyyyMMdd") + "' and timefield='" + dateTime.ToString("HHmmss") +
string sql = "SELECT * FROM bu_device inner join bu_task_device on bu_device.id = bu_task_device.bu_device_id where bu_task_device.bu_task_id=" +taskId;
List<Device> lstDevice = new List<Device>();
DataSet ds = new MySqlDa().fillDataSet(sql);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Device device = fillDevice(ds,i);
lstDevice.Add(device);
}
return lstDevice;
}