public void WhereEqualsTest()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Text.Equals("Gurux", StringComparison.OrdinalIgnoreCase));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE UPPER(TestClass.`Text`) LIKE('GURUX')", arg.ToString());
}
public void WhereSimple2Test()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Id == t.Id);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` = 1", arg.ToString());
}
public void WhereContainsTest()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => q.Text.Contains("Gurux"));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Text` LIKE('%Gurux%')", arg.ToString());
}
public void WhereClassTest()
{
TestClass t = new TestClass();
t.Id = 1;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => t);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE `ID` = 1", arg.ToString());
}
public void WhereClassArrayTest()
{
TestClass[] list = new TestClass[] { new TestClass(), new TestClass() };
list[0].Id = 1;
list[1].Id = 2;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => list);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE `ID` IN(1, 2)", arg.ToString());
}
public void FilterByStatus()
{
TestClass filter = new TestClass();
filter.Status = State.Failed;
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.FilterBy(filter);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Status` = 200", arg.ToString());
}
public void SelectTablesTest()
{
GXSelectArgs arg = GXSelectArgs.Select <TestClass2>(q => q.Parent);
arg.Columns.Clear();
arg.Columns.Add <TestClass2>(q => q.Name);
arg.Columns.Add <TestClass>(q => q.Guid);
arg.Joins.AddRightJoin <TestClass2, TestClass>(x => x.Parent, x => x.Id);
Assert.AreEqual("SELECT TestClass2.`Name`, TestClass.`Guid` FROM TestClass2 RIGHT OUTER JOIN TestClass ON TestClass2.`ParentID`=TestClass.`ID`", arg.ToString());
}
public void AppendWhereTest()
{
GXSelectArgs append = GXSelectArgs.Select <TestClass>(x => x.Guid);
append.Where.And <TestClass>(q => GXSql.In(q.Id, new int[] { 1, 2, 3 }));
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.Append(append.Where);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` IN (1, 2, 3)", arg.ToString());
}
public ActionResult <GetNextTaskResponse> Post(GetNextTask request)
{
GetNextTaskResponse ret = new GetNextTaskResponse();
lock (host)
{
GXSelectArgs arg = GXSelectArgs.Select <GXTask>(c => c.Id, q => q.Start == DateTime.MinValue);
arg.Joins.AddInnerJoin <GXTask, GXObject>(a => a.Object, b => b.Id);
arg.Joins.AddInnerJoin <GXObject, GXDevice>(a => a.DeviceId, b => b.Id);
//Don't get meters that are mapped to other readers.
arg.Joins.AddLeftJoin <GXDevice, GXDeviceToReader>(a => a.Id, b => b.DeviceId);
if (request.DeviceId != 0)
{
arg.Where.And <GXDevice>(q => q.Id == request.DeviceId);
}
if (!request.Listener)
{
arg.Where.And <GXDevice>(q => q.Dynamic == false);
}
arg.OrderBy.Add <GXTask>(q => q.Id);
GXSelectArgs onProgress = GXSelectArgs.Select <GXObject>(c => c.DeviceId, q => q.Removed == DateTime.MinValue);
onProgress.Joins.AddInnerJoin <GXTask, GXObject>(a => a.Object, b => b.Id);
onProgress.Where.And <GXTask>(q => q.Start != DateTime.MinValue && q.End == DateTime.MinValue);
arg.Where.And <GXObject>(q => !GXSql.Exists <GXObject, GXDevice>(a => a.DeviceId, b => b.Id, onProgress));
GXTask task = host.Connection.SingleOrDefault <GXTask>(arg);
if (task != null)
{
//Get task device ID and creation time.
arg = GXSelectArgs.SelectAll <GXTask>(q => q.Id == task.Id);
arg.Columns.Add <GXObject>();
arg.Joins.AddInnerJoin <GXTask, GXObject>(a => a.Object, b => b.Id);
arg.Joins.AddInnerJoin <GXObject, GXDevice>(a => a.DeviceId, b => b.Id);
task = host.Connection.SingleOrDefault <GXTask>(arg);
//Get all tasks that are created at the same time for same meter.
arg = GXSelectArgs.SelectAll <GXTask>(q => q.Generation == task.Generation);
arg.Where.And <GXDevice>(q => q.Id == task.Object.DeviceId);
arg.Where.And <GXObject>(q => q.Removed == DateTime.MinValue);
arg.Columns.Add <GXObject>();
arg.Columns.Add <GXDevice>();
arg.Columns.Add <GXAttribute>();
arg.Joins.AddInnerJoin <GXTask, GXObject>(a => a.Object, b => b.Id);
arg.Joins.AddInnerJoin <GXObject, GXDevice>(a => a.DeviceId, b => b.Id);
arg.Joins.AddInnerJoin <GXObject, GXAttribute>(a => a.Id, b => b.ObjectId);
ret.Tasks = host.Connection.Select <GXTask>(arg).ToArray();
DateTime now = DateTime.Now;
foreach (GXTask it in ret.Tasks)
{
it.Start = now;
host.Connection.Update(GXUpdateArgs.Update(it, q => q.Start));
}
host.SetChange(TargetType.Tasks, DateTime.Now);
}
}
return(ret);
}
public void WhereEquals3Test()
{
TestClass t = new TestClass();
t.Id = 1;
t.Text = "Gurux";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Text == t.Text);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Text` = 'Gurux'", arg.ToString());
}
public void WhereEquals5Test()
{
TestClass t = new TestClass();
t.Id = 1;
t.Text = "Gurux";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => new { x.Guid });
arg.Where.And <TestClass>(q => q.Text.Equals(t.Text) && q.Id == t.Id);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE (UPPER(TestClass.`Text`) LIKE('GURUX')) AND (TestClass.`ID` = 1)", arg.ToString());
}
public void FilterByDateTime()
{
TestClass filter = new TestClass();
filter.Status = State.OK;
filter.Time = new DateTime(2020, 4, 1);
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.FilterBy(filter, false);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`Time` >= '2020-04-01 00.00.00'", arg.ToString());
}
public void FilterByTest()
{
TestClass filter = new TestClass();
filter.Text2 = "More";
filter.Text3 = "Gurux";
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.FilterBy(filter);
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE ((TestClass.`SimpleText` = 'More') AND (TestClass.`Text3` = 'Gurux') AND (TestClass.`Status` = 0))", arg.ToString());
}
public ActionResult <ReaderDeviceResponse> GetMeters(ReaderDevices request)
{
GXSelectArgs arg = GXSelectArgs.SelectAll <GXDevice>();
arg.Joins.AddInnerJoin <GXDevice, GXDeviceToReader>(d => d.Id, i => i.DeviceId);
arg.Where.And <GXDeviceToReader>(q => request.Ids.Contains(q.ReaderId));
ReaderDeviceResponse ret = new ReaderDeviceResponse();
ret.Devices = host.Connection.Select <GXDevice>(arg).ToArray();
return(ret);
}
public void SqlNotInTest2()
{
List <int> list = new List <int>();
list.Add(1);
list.Add(2);
list.Add(3);
GXSelectArgs arg = GXSelectArgs.Select <TestClass>(x => x.Guid);
arg.Where.And <TestClass>(q => !list.Contains(q.Id));
Assert.AreEqual("SELECT `Guid` FROM TestClass WHERE TestClass.`ID` NOT IN (1, 2, 3)", arg.ToString());
}
public ActionResult <ListValuesResponse> Post(ListValues request)
{
ListValuesResponse ret = new ListValuesResponse();
GXSelectArgs arg = GXSelectArgs.SelectAll <GXValue>();
if (request.Ids != null && request.Ids.Length != 0)
{
arg.Where.And <GXValue>(q => request.Ids.Contains(q.AttributeId));
}
ret.Items = host.Connection.Select <GXValue>(arg).ToArray();
return(ret);
}
public void CreateSimpleViewTest2()
{
GXSelectArgs arg = GXSelectArgs.Select <Company>(q => new { q.Id, q.Name });
arg.Columns.Add <Country>(q => q.Name);
arg.Joins.AddInnerJoin <Company, Country>(a => a.Country, b => b.Id);
GXCreateViewArgs view = GXCreateViewArgs.Create <CountriesView>(arg);
string expected = "Create View Countries AS SELECT Company.`Id`, Company.`Name`, Country.`CountryName` FROM Company INNER JOIN Country ON Company.`CountryID`=Country.`ID`";
string actual = view.ToString();
Assert.AreEqual(expected, actual);
}
public ActionResult <ListReadersResponse> Post(ListReaders request)
{
ListReadersResponse ret = new ListReadersResponse();
GXSelectArgs arg = GXSelectArgs.SelectAll <GXReaderInfo>();
if (request.Ids != null && request.Ids.Length != 0)
{
arg.Where.And <GXReaderInfo>(q => request.Ids.Contains(q.Id));
}
ret.Readers = host.Connection.Select <GXReaderInfo>(arg).ToArray();
return(ret);
}
public void MikkoTest()
{
GXWorkflow workflow = new GXWorkflow();
workflow.Id = Guid.Parse("17f9f275-7dba-44a8-b9c5-5c404283cf7e");
GXSelectArgs arg = GXSelectArgs.SelectAll <GXJob>(q => q.Workflow == workflow && q.Removed == null);
arg.Columns.Add <GXModule>();
arg.Columns.Exclude <GXModule>(e => e.Jobs);
arg.Joins.AddLeftJoin <GXModule, GXJob>(j => j.Id, j => j.Module);
Assert.AreEqual("Mikko", arg.ToString());
}
public void NotExistsTest()
{
GXSelectArgs arg2 = GXSelectArgs.Select <Company>(q => q.Id);
arg2.Where.And <Company>(q => q.Name.Equals("Gurux"));
GXSelectArgs arg = GXSelectArgs.SelectAll <Country>();
arg.Where.And <Country>(q => !GXSql.Exists <Company, Country>(a => a.Country, b => b.Id, arg2));
string expected = "SELECT `ID`, `CountryName` FROM Country WHERE NOT EXISTS (SELECT `Id` FROM Company WHERE UPPER(Company.`Name`) LIKE('GURUX') AND Country.`ID` = Company.`CountryID`)";
string actual = arg.ToString();
Assert.AreEqual(expected, actual);
}
public ActionResult <ListDeviceLogResponse> Post(ListDeviceLog request)
{
ListDeviceLogResponse ret = new ListDeviceLogResponse();
GXSelectArgs arg = GXSelectArgs.SelectAll <GXDeviceLog>();
arg.Count = (UInt32)request.Count;
arg.Descending = true;
arg.Where.And <GXDeviceLog>(q => request.Ids.Contains(q.Id));
arg.OrderBy.Add <GXDeviceLog>(q => q.Id);
arg.OrderBy.Add <GXDeviceLog>(q => q.Generation);
ret.Logs = host.Connection.Select <GXDeviceLog>(arg).ToArray();
return(ret);
}
public void CreateSimpleViewTest()
{
GXSelectArgs arg2 = GXSelectArgs.Select <Company>(q => q.Id);
arg2.Where.And <Company>(q => q.Name.Equals("Gurux"));
GXSelectArgs arg = GXSelectArgs.Select <Country>(q => new { q.Id, q.Name });
arg.Where.And <Country>(q => !GXSql.Exists <Company, Country>(a => a.Country, b => b.Id, arg2));
GXCreateViewArgs view = GXCreateViewArgs.Create <CountriesView>(arg);
string expected = "Create View Countries AS SELECT `ID`, `CountryName` FROM Country WHERE NOT EXISTS (SELECT `Id` FROM Company WHERE UPPER(Company.`Name`) LIKE('GURUX') AND Country.`ID` = Company.`CountryID`)";
string actual = view.ToString();
Assert.AreEqual(expected, actual);
}
public ActionResult <ListSchedulesResponse> Post(ListSchedules request)
{
ListSchedulesResponse ret = new ListSchedulesResponse();
GXSelectArgs arg = GXSelectArgs.SelectAll <GXSchedule>();
arg.Distinct = true;
arg.Where.And <GXSchedule>(q => q.Removed == DateTime.MinValue);
if (request.ObjectId != 0)
{
arg.Joins.AddInnerJoin <GXSchedule, GXScheduleToAttribute>(s => s.Id, o => o.ScheduleId);
arg.Joins.AddInnerJoin <GXScheduleToAttribute, GXAttribute>(s => s.AttributeId, o => o.Id);
arg.Where.And <GXAttribute>(q => q.ObjectId == request.ObjectId);
}
else if (request.DeviceId != 0)
{
arg.Joins.AddInnerJoin <GXSchedule, GXScheduleToAttribute>(s => s.Id, o => o.ScheduleId);
arg.Joins.AddInnerJoin <GXScheduleToAttribute, GXAttribute>(s => s.AttributeId, o => o.Id);
arg.Joins.AddInnerJoin <GXAttribute, GXObject>(s => s.ObjectId, o => o.Id);
arg.Where.And <GXObject>(q => q.DeviceId == request.DeviceId);
}
/*
* if ((request.Targets & TargetType.Attribute) != 0)
* {
* arg.Columns.Add<GXObject>();
* arg.Columns.Add<GXScheduleToAttribute>();
* arg.Joins.AddLeftJoin<GXSchedule, GXScheduleToAttribute>(s => s.Id, o => o.ScheduleId);
* arg.Joins.AddLeftJoin<GXScheduleToAttribute, GXAttribute>(s => s.AttributeId, o => o.Id);
* arg.Joins.AddInnerJoin<GXAttribute, GXObject>(s => s.ObjectId, o => o.Id);
* arg.Where.And<GXObject>(q => q.Removed == DateTime.MinValue);
* arg.Where.And<GXAttribute>(q => q.Removed == DateTime.MinValue);
* }
*/
ret.Schedules = host.Connection.Select <GXSchedule>(arg).ToArray();
foreach (GXSchedule it in ret.Schedules)
{
arg = GXSelectArgs.SelectAll <GXObject>();
arg.Columns.Add <GXAttribute>();
arg.Distinct = true;
arg.Joins.AddInnerJoin <GXSchedule, GXScheduleToAttribute>(s => s.Id, o => o.ScheduleId);
arg.Joins.AddInnerJoin <GXScheduleToAttribute, GXAttribute>(s => s.AttributeId, o => o.Id);
arg.Joins.AddInnerJoin <GXAttribute, GXObject>(s => s.ObjectId, o => o.Id);
arg.Where.And <GXObject>(q => q.Removed == DateTime.MinValue);
arg.Where.And <GXAttribute>(q => q.Removed == DateTime.MinValue);
arg.Where.And <GXSchedule>(q => q.Id == it.Id);
it.Objects = host.Connection.Select <GXObject>(arg);
}
return(ret);
}
public void WhereStringIsNullTest()
{
string expected = "WHERE TestClass.`Text` IS NULL";
string text = null;
GXSelectArgs args = GXSelectArgs.SelectAll <TestClass>(q => q.Text == text);
string actual = args.Where.ToString();
Assert.AreEqual(expected, actual);
args = GXSelectArgs.SelectAll <TestClass>(q => q.Text.Equals(text));
actual = args.Where.ToString();
Assert.AreEqual(expected, actual);
expected = "WHERE (TestClass.`Text` IS NULL OR TestClass.`Text` = '')";
args = GXSelectArgs.SelectAll <TestClass>(q => string.IsNullOrEmpty(q.Text));
actual = args.Where.ToString();
Assert.AreEqual(expected, actual);
}
public ActionResult <ObjectDeleteResponse> Post(ObjectDelete request)
{
if (request.Ids != null)
{
GXSelectArgs arg = GXSelectArgs.Select <GXObject>(a => a.Id, q => request.Ids.Contains(q.Id));
List <GXObject> list = host.Connection.Select <GXObject>(arg);
if (list.Count != 0)
{
foreach (GXObject it in list)
{
it.Removed = DateTime.Now;
}
host.Connection.Update(GXUpdateArgs.UpdateRange(list, q => q.Removed));
host.SetChange(TargetType.Object, DateTime.Now);
}
}
return(new ObjectDeleteResponse());
}
public void MultipleTablesTest()
{
GXUser user = new GXUser()
{
Id = "Gurux"
};
GXUserGroup userGroup = new GXUserGroup();
userGroup.Users.Add(user);
GXSelectArgs arg = GXSelectArgs.Select <GXUserGroup>(s => s.Id, where => where.Removed == null);
arg.Where.FilterBy(userGroup, false);
arg.Joins.AddInnerJoin <GXUserGroup, GXUserGroupUser>(j => j.Id, j => j.UserGroupId);
arg.Joins.AddInnerJoin <GXUserGroupUser, GXUser>(j => j.UserId, j => j.Id);
string[] userIds = new string[] { "Gurux" };
arg.Where.And <GXUser>(where => where.Removed == null && userIds.Contains(where.Id));
Assert.AreEqual("Mikko", arg.ToString());
}
public ActionResult <DeviceDeleteResponse> Post(DeviceDelete request)
{
if (request.Ids != null)
{
GXSelectArgs arg = GXSelectArgs.Select <GXDevice>(a => a.Id, q => request.Ids.Contains(q.Id));
List <GXDevice> list = host.Connection.Select <GXDevice>(arg);
if (list.Count != 0)
{
DateTime now = DateTime.Now;
foreach (GXDevice it in list)
{
it.Removed = now;
host.Connection.Update(GXUpdateArgs.Update(it, q => q.Removed));
}
host.SetChange(TargetType.Device, now);
}
}
return(new DeviceDeleteResponse());
}
public ActionResult <ListDevicesResponse> Post(ListDevices request)
{
ListDevicesResponse ret = new ListDevicesResponse();
GXSelectArgs arg = GXSelectArgs.SelectAll <GXDevice>();
arg.Where.And <GXDevice>(q => q.Removed == DateTime.MinValue);
if ((request.Targets & TargetType.Attribute) != 0)
{
arg.Columns.Add <GXObject>();
arg.Columns.Add <GXAttribute>();
arg.Joins.AddLeftJoin <GXDevice, GXObject>(d => d.Id, o => o.DeviceId);
arg.Joins.AddLeftJoin <GXObject, GXAttribute>(o => o.Id, a => a.ObjectId);
arg.Where.And <GXObject>(q => q.Removed == DateTime.MinValue);
arg.Where.And <GXAttribute>(q => q.Removed == DateTime.MinValue);
}
else if ((request.Targets & TargetType.Object) != 0)
{
arg.Columns.Add <GXObject>();
arg.Joins.AddLeftJoin <GXDevice, GXObject>(d => d.Id, o => o.DeviceId);
arg.Where.And <GXObject>(q => q.Removed == DateTime.MinValue);
}
byte[] st = GXCommon.HexToBytes(request.SystemTitle);
if (st.Length == 8)
{
string str = GXCommon.ToHex(st, false);
arg.Where.And <GXDevice>(q => q.DeviceSystemTitle.Equals(str));
}
else if (!string.IsNullOrEmpty(request.Name))
{
string name = request.Name;
arg.Where.And <GXDevice>(q => q.Name.Contains(name));
}
else if (request.Ids != null)
{
arg.Where.And <GXDevice>(q => request.Ids.Contains(q.Id));
}
ret.Devices = host.Connection.Select <GXDevice>(arg).ToArray();
return(ret);
}
public ActionResult <AddReaderResponse> Post(AddReader request)
{
GXSelectArgs arg = GXSelectArgs.SelectAll <GXReaderInfo>();
arg.Where.And <GXReaderInfo>(q => q.Guid == request.Reader.Guid);
GXReaderInfo i = host.Connection.SingleOrDefault <GXReaderInfo>(arg);
if (i == null)
{
request.Reader.Detected = request.Reader.Generation = DateTime.Now;
host.Connection.Insert(GXInsertArgs.Insert(request.Reader));
}
else
{
i.Detected = DateTime.Now;
host.Connection.Update(GXUpdateArgs.Update(i, u => u.Detected));
}
host.SetChange(TargetType.Readers, DateTime.Now);
return(new AddReaderResponse());
}
public ActionResult <AddTaskResponse> Post(AddTask request)
{
AddTaskResponse ret = new AddTaskResponse();
DateTime now = DateTime.Now;
foreach (var it in request.Actions)
{
if (it.Object.Id == 0)
{
UInt64 dId = it.Object.DeviceId;
int ot = it.Object.ObjectType;
string ln = it.Object.LogicalName;
it.Object = host.Connection.SingleOrDefault <GXObject>(GXSelectArgs.Select <GXObject>(null, q => q.DeviceId == dId && q.ObjectType == ot && q.LogicalName == ln));
if (it.Object == null)
{
return(BadRequest(string.Format("Invalid target {0}:{1} DeviceID {2}.", ot, ln, dId)));
}
}
it.Generation = now;
host.Connection.Insert(GXInsertArgs.Insert(it));
}
host.SetChange(TargetType.Tasks, DateTime.Now);
return(ret);
}
