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