public static string GenerateCode(Stream sampleStream)
{
StringBuilder sb = new StringBuilder();
TextReader reader = new StreamReader(sampleStream);
TextWriter writer = new StringWriter(sb);
QueryGenerator generator = new QueryGenerator(reader, new CSharpCodeProvider(), "Query", null);
generator.Generate(writer);
return sb.ToString();
}
public IHttpActionResult RemoveLinkedUser([FromBody] object data)
{
try
{
var headers = Request.Headers;
string idMe = headers.GetValues(Models.User.COL_ID).First();
string idTarget = headers.GetValues(LinkedUser.PARAM_TARGET).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// conditions 1
ArrayList conditions1 = new ArrayList();
string c1;
ArrayList columnsC1 = new ArrayList();
ArrayList conditionsC1 = new ArrayList();
columnsC1.Add(LinkedUser.COL_ID);
conditionsC1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
c1 = QueryGenerator.GenerateSqlSelect(columnsC1, LinkedUser.TABLE, conditionsC1
, null, QueryGenerator.KW_ASC, 1);
c1 = QueryGenerator.KW_EXISTS + QueryGenerator.SPACE + QueryGenerator.ParenthesisString(c1);
conditions1.Add(c1);
// conditions 2
ArrayList conditions2 = new ArrayList();
string c2;
ArrayList columnsC2 = new ArrayList();
ArrayList conditionsC2 = new ArrayList();
columnsC2.Add(LinkedUser.COL_ID);
conditionsC2.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
c2 = QueryGenerator.GenerateSqlSelect(columnsC2, LinkedUser.TABLE, conditionsC2
, null, QueryGenerator.KW_ASC, 1);
c2 = QueryGenerator.KW_EXISTS + QueryGenerator.SPACE + QueryGenerator.ParenthesisString(c2);
conditions2.Add(c2);
// statement 1
ArrayList assignS1 = new ArrayList();
ArrayList conditionS1 = new ArrayList();
string s1;
assignS1.Add(LinkedUser.COL_ADDED1 + "=0");
assignS1.Add(LinkedUser.COL_DELETED + "=1");
conditionS1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
s1 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS1, conditionS1);
// statement 2
ArrayList assignS2 = new ArrayList();
ArrayList conditionS2 = new ArrayList();
string s2;
assignS2.Add(LinkedUser.COL_ADDED2 + "=0");
assignS2.Add(LinkedUser.COL_DELETED + "=1");
conditionS2.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
s2 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS2, conditionS2);
// statement 3
string s3 = QueryGenerator.KW_DONOTHING;
string statement;
statement = QueryGenerator.GenerateSqlIfElseIfElse(conditions1, conditions2, s1, s2, s3);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch
{
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.BadRequest, false));
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
//return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, true));
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult GetLinkedUsersById([FromBody] object data)
{
ArrayList linkedUsers = new ArrayList();
LinkedUser lu;
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// query linked user list
ArrayList columsS1 = new ArrayList();
ArrayList conditionsS1 = new ArrayList();
string statement1 = string.Empty;
columsS1.Add(LinkedUser.COL_ID);
columsS1.Add(LinkedUser.COL_USERID1);
columsS1.Add(LinkedUser.COL_USERID2);
columsS1.Add(LinkedUser.COL_ALERT1);
columsS1.Add(LinkedUser.COL_ALERT2);
columsS1.Add(LinkedUser.COL_MUTE1);
columsS1.Add(LinkedUser.COL_MUTE2);
columsS1.Add(LinkedUser.COL_DELETED);
columsS1.Add(LinkedUser.COL_ADDED1);
columsS1.Add(LinkedUser.COL_ADDED2);
conditionsS1.Add(LinkedUser.COL_USERID1 + "=" + id);
conditionsS1.Add(QueryGenerator.KW_OR);
conditionsS1.Add(LinkedUser.COL_USERID2 + "=" + id);
statement1 = QueryGenerator.GenerateSqlSelect(columsS1, LinkedUser.TABLE, conditionsS1);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement1;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
lu = new LinkedUser();
int i = 0;
lu.ID = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
if (lu.UserIDMe == int.Parse(id))
{
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
}
else
{
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
}
linkedUsers.Add(lu);
}
dr.Close();
}
linkedUsers.Sort();
// query linked user name and status
ArrayList columsS2 = new ArrayList();
ArrayList conditionsS2 = new ArrayList();
ArrayList ordersS2 = new ArrayList();
string statement2 = string.Empty;
columsS2.Add(Models.User.COL_ID);
columsS2.Add(Models.User.COL_DELETED);
columsS2.Add(Models.User.COL_USERNAME);
columsS2.Add(Models.User.COL_STATUS);
for (int i = 0; i < linkedUsers.Count; ++i)
{
if (i > 0)
{
conditionsS2.Add(QueryGenerator.KW_OR);
}
conditionsS2.Add(Models.User.COL_ID + "=" + ((LinkedUser)linkedUsers[i]).UserIDTarget);
}
ordersS2.Add(Models.User.COL_ID);
statement2 = QueryGenerator.GenerateSqlSelect(columsS2, Models.User.TABLE, conditionsS2, ordersS2, QueryGenerator.KW_ASC);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement2;
using (SqlDataReader dr = cmd.ExecuteReader())
{
int i = 0;
if (linkedUsers.Count > 0)
{
while (dr.Read())
{
if (((LinkedUser)linkedUsers[i]).UserIDTarget == dr.GetInt32(0) &&
!dr.GetBoolean(1))
{
((LinkedUser)linkedUsers[i]).NameTarget = dr.GetString(2);
((LinkedUser)linkedUsers[i]).StatusTarget = dr.GetInt32(3);
}
else
{
((LinkedUser)linkedUsers[i]).NameTarget = string.Empty;
((LinkedUser)linkedUsers[i]).StatusTarget = 5;
}
i++;
}
}
dr.Close();
}
for (int i = linkedUsers.Count - 1; i >= 0; --i)
{
if (((LinkedUser)linkedUsers[i]).NameTarget.Length == 0)
{
linkedUsers.RemoveAt(i);
}
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
return(Ok(new { linkedUsers }));
}
public void TestGetSearchQuery()
{
Assert.Multiple(() =>
{
Assert.IsEmpty(QueryGenerator.GetSearchStr(null, null, null, null));
Assert.AreEqual($"{nameof(CodeSource.FileName)}:A", QueryGenerator.GetSearchStr("A", null, null, null));
Assert.AreEqual($"{nameof(CodeSource.Content)}:B", QueryGenerator.GetSearchStr(null, "B", null, null));
Assert.AreEqual($"{nameof(CodeSource.FileExtension)}:C", QueryGenerator.GetSearchStr(null, null, "C", null));
Assert.AreEqual($"{nameof(CodeSource.FilePath)}:D", QueryGenerator.GetSearchStr(null, null, null, "D"));
Assert.AreEqual($"{nameof(CodeSource.FileName)}:A AND {nameof(CodeSource.Content)}:B AND {nameof(CodeSource.FileExtension)}:C AND {nameof(CodeSource.FilePath)}:D", QueryGenerator.GetSearchStr("A", "B", "C", "D"));
Assert.IsEmpty(QueryGenerator.GetSearchStr(" ", " ", string.Empty, " "));
Assert.AreEqual(nameof(CodeSource.FilePath) + ":" + "\"C:\\\\WWWROOT\"", QueryGenerator.GetSearchStr(null, null, null, "\"C:\\WWWROOT\""));
});
}
public void ProjectComputedEntity()
{
// Asserts if the query result for a relationship join operation is equal
// to a handcrafted query
RequiredDataContainer ModelData = ProjectDataProvider.ComputedEntityData();
// Load handcrafted query
string HandcraftedQuery = Utils.ReadQueryFromFile("HandcraftedQueries/projectQuery.js");
// Assert if the handcrafted query is not null
Assert.IsNotNull(HandcraftedQuery);
// Prepare query generator
ComputedEntity CarRepairedByGarage = new ComputedEntity("CarManufacturedBy",
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Car"), "car"),
(Relationship)ModelData.EntityRelationshipModel.FindByName("ManufacturedBy"),
new List <QueryableEntity> {
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Manufacturer"), "manufacturer")
});
RelationshipJoinOperator RJoinOp = new RelationshipJoinOperator(
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Person"), "person"),
(Relationship)ModelData.EntityRelationshipModel.FindByName("Owns"),
new List <QueryableEntity> {
new QueryableEntity(CarRepairedByGarage)
},
ModelData.ERMongoMapping);
VirtualMap VMap = RJoinOp.ComputeVirtualMap();
Dictionary <string, ProjectExpression> ProjectPersonAttrs = new Dictionary <string, ProjectExpression>();
QueryableEntity Person = new QueryableEntity(ModelData.EntityRelationshipModel.FindByName("Person"));
QueryableEntity Car = new QueryableEntity(ModelData.EntityRelationshipModel.FindByName("Car"));
QueryableEntity Manufacturer = new QueryableEntity(ModelData.EntityRelationshipModel.FindByName("Manufacturer"));
List <ProjectArgument> ProjectArguments = new List <ProjectArgument>();
ProjectArguments.Add(new ProjectArgument(Person.GetAttribute("name"), Person, new BooleanExpr(true)));
ProjectArguments.Add(new ProjectArgument(Car.GetAttribute("model"), Car, new BooleanExpr(true)));
ProjectArguments.Add(new ProjectArgument(Car.GetAttribute("year"), Car, new BooleanExpr(true)));
ProjectArguments.Add(new ProjectArgument(Manufacturer.GetAttribute("name"), Manufacturer, new BooleanExpr(true)));
ProjectStage ProjectOp = new ProjectStage(ProjectArguments, VMap);
List <AlgebraOperator> OpList = new List <AlgebraOperator> {
RJoinOp, ProjectOp
};
FromArgument StartArg = new FromArgument(new QueryableEntity(ModelData.EntityRelationshipModel.FindByName("Person")),
ModelData.ERMongoMapping);
QueryGenerator QueryGen = new QueryGenerator(StartArg, OpList);
string GeneratedQuery = QueryGen.Run();
// Assert if generated query is not null
Assert.IsNotNull(GeneratedQuery);
// Run Queries
QueryRunner Runner = new QueryRunner("mongodb://localhost:27017", "ceManyToMany2");
string HandcraftedResult = Runner.GetJSON(HandcraftedQuery);
string GeneratedResult = Runner.GetJSON(GeneratedQuery);
// Check if either result is null
Assert.IsNotNull(HandcraftedResult);
Assert.IsNotNull(GeneratedResult);
// Check if both results are equal
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(HandcraftedResult), JToken.Parse(GeneratedResult)));
}
public void RetrieveAllEmployeeMetricsSuccess()
{
System.DateTime dt = new System.DateTime();
List <EmployeeMetrics> dbRetVal = new List <EmployeeMetrics>()
{
new EmployeeMetrics("lol1", "lol2", "lol3", 1.0, 2.0, 3.0, 4.0, 5.0, dt),
new EmployeeMetrics("lul", "lul", "lul", 2.0, 2.0, 2.0, 2.0, 2.0, dt)
};
MockServiceProvider serviceProvider = new MockServiceProvider()
.AddMockDBConnection();
serviceProvider.MockDBConnection
.Setup(_ => _.ExecuteQuery <EmployeeMetrics>(QueryGenerator.GetEmployeeMetricsHeader() + QueryGenerator.WhereRole("Developer")))
.Returns(dbRetVal);
EmployeeService employeeService = new EmployeeService(serviceProvider.Build());
List <EmployeeResponse> response = employeeService.RetrieveAllEmployeeMetrics();
Assert.Equal("lol1", response[0].EmployeeId);
Assert.Equal(3.0, response[0].Billable.CurrentHours);
Assert.Equal(2.0, response[0].Billable.CurrentTarget);
Assert.Equal(1.0, response[0].Billable.TotalTarget);
Assert.Equal(-1.0, response[0].Growth.HoursRemaining);
Assert.Equal(5.0, response[0].Growth.HoursUsed);
Assert.Equal(4.0, response[0].Growth.TotalGrowth);
Assert.Equal("lul", response[1].EmployeeId);
Assert.Equal(2.0, response[1].Billable.CurrentHours);
Assert.Equal(2.0, response[1].Billable.CurrentTarget);
Assert.Equal(2.0, response[1].Billable.TotalTarget);
Assert.Equal(0, response[1].Growth.HoursRemaining);
Assert.Equal(2.0, response[1].Growth.HoursUsed);
Assert.Equal(2.0, response[1].Growth.TotalGrowth);
}
public void RetrieveAllEmployeeMetricsFail()
{
List <EmployeeMetrics> dbRetVal = new List <EmployeeMetrics>();
MockServiceProvider serviceProvider = new MockServiceProvider()
.AddMockDBConnection();
serviceProvider.MockDBConnection
.Setup(_ => _.ExecuteQuery <EmployeeMetrics>(QueryGenerator.GetEmployeeMetricsHeader() + QueryGenerator.WhereRole("Developer")))
.Returns(dbRetVal);
EmployeeService employeeService = new EmployeeService(serviceProvider.Build());
var response = employeeService.RetrieveAllEmployeeMetrics();
Assert.Null(response);
}
protected void ImportExcel()
{
var tableModel = Model.Table;
var logicModel = Model.Logic;
var tableName = String.Format("#{0}", tableModel.Name.Trim());
var dataSet = ExcelUtil.ConvertToDataSet(fuImport.FileBytes);
var dataTable = dataSet.Tables[tableName];
var queryGen = new QueryGenerator(DataContext, logicModel);
var connectionString = GetConnectionString();
using (var connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (var transaction = connection.BeginTransaction())
{
var insertQuery = queryGen.InsertQuery();
using (var command = new SqlCommand(insertQuery, connection, transaction))
{
foreach (var columnModel in tableModel.Columns)
{
var columnName = queryGen.AllColumns[columnModel.Name.Trim()];
var paramName = queryGen.AllColumnsParams[columnName];
var dbType = queryGen.DbTypes[columnName];
var param = command.CreateParameter();
param.ParameterName = paramName;
param.SqlDbType = dbType;
param.IsNullable = true;
command.Parameters.Add(param);
}
foreach (DataRow dataRow in dataTable.Rows)
{
foreach (var columnModel in tableModel.Columns)
{
var dataColumnName = String.Format("#{0}", columnModel.Name.Trim());
var columnName = queryGen.AllColumns[columnModel.Name.Trim()];
var columnValue = dataRow[dataColumnName];
var paramName = queryGen.AllColumnsParams[columnName];
var sqlParam = command.Parameters[paramName];
sqlParam.Value = GetCorrectValue(columnValue, sqlParam.SqlDbType);
}
command.ExecuteNonQuery();
}
try
{
transaction.Commit();
}
catch (Exception)
{
transaction.Rollback();
}
}
}
}
}
public IHttpActionResult GetEmergencyBldgAll()
{
ArrayList buildings = new ArrayList();
EmergencyBldg bldg;
try
{
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
string statement = string.Empty;
colums.Add(EmergencyBldg.COL_ID);
colums.Add(EmergencyBldg.COL_CATEGORY);
colums.Add(EmergencyBldg.COL_BLDGID);
colums.Add(EmergencyBldg.COL_BLDGNAME);
colums.Add(EmergencyBldg.COL_STRNUM);
colums.Add(EmergencyBldg.COL_STRNAME);
colums.Add(EmergencyBldg.COL_MAPREF);
colums.Add(EmergencyBldg.COL_LAT);
colums.Add(EmergencyBldg.COL_LNG);
colums.Add(EmergencyBldg.COL_PHONE);
colums.Add(EmergencyBldg.COL_LOCNAME);
statement = QueryGenerator.GenerateSqlSelect(colums,
EmergencyBldg.TABLE, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
bldg = new EmergencyBldg();
int i = 0;
bldg.ID = dr.GetInt32(i);
i++;
bldg.Category = dr.GetInt32(i);
i++;
bldg.BldgID = dr.GetInt32(i);
i++;
bldg.BldgName = dr.GetString(i);
i++;
bldg.StrNum = dr.GetInt32(i);
i++;
bldg.StrName = dr.GetString(i);
i++;
bldg.MapRef = dr.GetInt32(i);
i++;
bldg.Lat = dr.GetDouble(i);
i++;
bldg.Lng = dr.GetDouble(i);
i++;
if (dr.GetValue(i) != DBNull.Value)
{
bldg.Phone = dr.GetInt64(i);
}
i++;
if (dr.GetValue(i) != DBNull.Value)
{
bldg.LocName = dr.GetString(i);
}
buildings.Add(bldg);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
if (buildings.Count == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No building is found.", "")));
}
return(Ok(new { buildings }));
}
public static void Main()
{
DataContainer data = CreateDataContainer();
QueryableEntity Person = new QueryableEntity(data.EntityRelationshipModel.FindByName("Person"));
QueryableEntity Car = new QueryableEntity(data.EntityRelationshipModel.FindByName("Car"));
Relationship Drives = (Relationship)data.EntityRelationshipModel.FindByName("Drives");
ModelMapping Map = data.ERMongoMapping;
QueryableEntity Garage = new QueryableEntity(data.EntityRelationshipModel.FindByName("Garage"));
RelationshipJoinOperator RJoinDrives = new RelationshipJoinOperator(
Person,
Drives,
new List <QueryableEntity>()
{
Car
},
Map);
CartesianProductOperator CartesianOp = new CartesianProductOperator(
Person,
Garage,
Map);
ProjectArgument ProjectArg1 = new ProjectArgument(Person.GetAttribute("name"), Person, new BooleanExpr(true));
ProjectArgument ProjectArg2 = new ProjectArgument(Person.GetAttribute("surname"), Person, new BooleanExpr(true));
ProjectArgument ProjectArg3 = new ProjectArgument(Car.GetAttribute("reg_no"), Car, new BooleanExpr(true));
ProjectStage ProjectOp = new ProjectStage(new ProjectArgument[] { ProjectArg1, ProjectArg2, ProjectArg3 }, RJoinDrives.ComputeVirtualMap());
MapRule PersonRule = Map.FindMainRule(Person.Element);
SelectArgument SelectArg = new SelectArgument(new EqExpr($"${PersonRule.GetRuleValueForAttribute( Person.GetAttribute( "surname" ) )}", "Smith"));
SelectStage SelectOp = new SelectStage(SelectArg, Map);
//List<MongoDBOperator> Operations = RJoinDrives.Run().Commands;
//foreach ( MongoDBOperator Op in Operations )
//{
// Console.WriteLine( Op.GetType().Name );
// Console.WriteLine( Op.ToString() );
// Console.WriteLine( Op.ToJavaScript() );
// if ( Op is LookupOperator )
// {
// LookupOperator OpAsLookup = (LookupOperator)Op;
// foreach ( MongoDBOperator PipelineOp in OpAsLookup.Pipeline )
// {
// Console.WriteLine( PipelineOp.GetType().Name );
// Console.WriteLine( PipelineOp.ToString() );
// Console.WriteLine( PipelineOp.ToJavaScript() );
// }
// }
//}
QueryGenerator QueryGen = new QueryGenerator(new FromArgument(Person, Map), new List <AlgebraOperator>()
{
SelectOp
});
string Query = QueryGen.Run();
Console.WriteLine($"Query: {Environment.NewLine}{Query}");
Console.Read();
}
public override void OnActionExecuting(System.Web.Http.Controllers.HttpActionContext actionContext)
{
if (actionContext.Request.Headers.Authorization == null)
{
actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
}
else
{
string authToken = actionContext.Request.Headers.Authorization.Parameter;
string decodedToken = Encoding.UTF8.GetString(Convert.FromBase64String(authToken));
string email = decodedToken.Substring(0, decodedToken.IndexOf(":"));
string password = decodedToken.Substring(decodedToken.IndexOf(":") + 1);
User user = new User();
try
{
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
string statement = string.Empty;
colums.Add(User.COL_EMAIL);
colums.Add(User.COL_PASSWORD);
colums.Add(User.COL_DELETED);
colums.Add(User.COL_LOGIN);
conditions.Add(User.COL_EMAIL + " = " + QueryGenerator.QuoteString(email));
statement = QueryGenerator.GenerateSqlSelect(colums, User.TABLE, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
user.Email = dr.GetString(0);
user.Password = dr.GetString(1);
user.Deleted = dr.GetBoolean(2);
user.Login = dr.GetBoolean(3);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.InternalServerError);
}
if (email.Equals(user.Email) &&
password.Equals(user.Password) &&
!user.Deleted &&
user.Login)
{
HttpContext.Current.User = new GenericPrincipal(new ApiIdentity(user), new string[] { });
base.OnActionExecuting(actionContext);
}
else
{
actionContext.Response = new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!btnSave.Enabled)
{
return;
}
Validator _validator = SCMS.Validators[this];
if (!Materia.Valid(_validator, txtAddress, !string.IsNullOrEmpty(txtAddress.Text.RLTrim()), "Please specify company address."))
{
tbctrl.SelectedTab = tbCompany; return;
}
if (!Materia.Valid(_validator, cboCountry, cboCountry.SelectedIndex >= 0, "Please specify a valid country."))
{
tbctrl.SelectedTab = tbCompany; return;
}
if (!Materia.Valid(_validator, cboCashAdvance, cboCashAdvance.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboUnallocatedPayments, cboUnallocatedPayments.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboRawMaterials, cboRawMaterials.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboStockConsumption, cboStockConsumption.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboStockAdjustment, cboStockAdjustment.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (!Materia.Valid(_validator, cboRollForward, cboRollForward.SelectedIndex >= 0, "Please specify a valid account."))
{
tbctrl.SelectedTab = tbAccounts; return;
}
if (chkAutoBackup.Checked)
{
if (!Materia.Valid(_validator, lblPath, !string.IsNullOrEmpty(lblPath.Text.RLTrim()), "Please specify backup output destination."))
{
tbctrl.SelectedTab = tbWorkstation; return;
}
if (!Materia.Valid(_validator, lblPath, Directory.Exists(lblPath.Text), "Please specify backup output destination."))
{
tbctrl.SelectedTab = tbWorkstation; return;
}
if (dtpBackUpTime2.LockUpdateChecked)
{
DateTime _date1 = VisualBasic.CDate(DateTime.Now.ToShortDateString() + " " + VisualBasic.Format(dtpBackUpTime1.Value, "hh:mm tt"));
DateTime _date2 = VisualBasic.CDate(DateTime.Now.ToShortDateString() + " " + VisualBasic.Format(dtpBackUpTime2.Value, "hh:mm tt"));
if (!Materia.Valid(_validator, dtpBackUpTime2, _date2 >= _date1, "Please specify a time equal or higher than the first instance."))
{
tbctrl.SelectedTab = tbWorkstation; return;
}
}
}
DataTable _settings = Cache.GetCachedTable("settings");
if (_settings != null)
{
DataRow[] _rows = _settings.Select("[Company] LIKE '" + SCMS.CurrentCompany.Company.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
_row["Address"] = txtAddress.Text; _row["Country"] = cboCountry.SelectedValue.ToString();
_row["Phone"] = txtPhone.Text; _row["Mobile"] = txtMobile.Text;
_row["Fax"] = txtFax.Text; _row["Email"] = txtEmail.Text;
try { _row["CompanyLogo"] = pctCompanyLogo.Image.ToByteArray(); }
catch { }
try { _row["ReportLogo"] = pctReportLogo.Image.ToByteArray(); }
catch { }
_row["CashAdvanceAccountCode"] = cboCashAdvance.SelectedValue;
_row["RawMaterialAccountCode"] = cboRawMaterials.SelectedValue;
_row["StockConsumptionAccountCode"] = cboStockConsumption.SelectedValue;
_row["StockAdjustmentAccountCode"] = cboStockAdjustment.SelectedValue;
_row["UnallocatedPaymentAccountCode"] = cboUnallocatedPayments.SelectedValue;
_row["RollForwardAccountCode"] = cboRollForward.SelectedValue;
QueryGenerator _generator = new QueryGenerator(_settings);
string _query = _generator.ToString();
_generator = null; Materia.RefreshAndManageCurrentProcess();
if (string.IsNullOrEmpty(_query.RLTrim()))
{
GlobalSettings.AutomaticBackupEnabled = chkAutoBackup.Checked;
if (chkAutoBackup.Checked)
{
GlobalSettings.AutomaticBackupEnabled2 = dtpBackUpTime2.LockUpdateChecked;
}
else
{
GlobalSettings.AutomaticBackupEnabled2 = false;
}
GlobalSettings.AutomaticBackupPath = lblPath.Text;
GlobalSettings.AutomaticBackupTime1 = dtpBackUpTime1.Value;
if (chkAutoBackup.Checked &&
dtpBackUpTime2.LockUpdateChecked)
{
GlobalSettings.AutomaticBackupTime2 = dtpBackUpTime2.Value;
}
if (txtIdleTime.LockUpdateChecked)
{
GlobalSettings.AutomaticLockTime = txtIdleTime.Value;
}
else
{
GlobalSettings.AutomaticLockTime = 0;
}
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(UserAction.Edit, "Updated the application settings.");
while (!_logresult.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_logresult.IsCompleted)
{
try { _logresult = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
return;
}
btnSave.Enabled = false;
IAsyncResult _result = Que.BeginExecution(SCMS.Connection, _query);
while (!_result.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
_settings.RejectChanges();
if (!_result.IsCompleted)
{
try { _result = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
else
{
QueResult _queresult = Que.EndExecution(_result);
if (string.IsNullOrEmpty(_queresult.Error.RLTrim()))
{
_settings.AcceptChanges(); Cache.Save(); _updated = false;
GlobalSettings.AutomaticBackupEnabled = chkAutoBackup.Checked;
if (chkAutoBackup.Checked)
{
GlobalSettings.AutomaticBackupEnabled2 = dtpBackUpTime2.LockUpdateChecked;
}
else
{
GlobalSettings.AutomaticBackupEnabled2 = false;
}
GlobalSettings.AutomaticBackupPath = lblPath.Text;
GlobalSettings.AutomaticBackupTime1 = dtpBackUpTime1.Value;
if (chkAutoBackup.Checked &&
dtpBackUpTime2.LockUpdateChecked)
{
GlobalSettings.AutomaticBackupTime2 = dtpBackUpTime2.Value;
}
if (txtIdleTime.LockUpdateChecked)
{
GlobalSettings.AutomaticLockTime = txtIdleTime.Value;
}
else
{
GlobalSettings.AutomaticLockTime = 0;
}
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(UserAction.Edit, "Updated the application settings.");
while (!_logresult.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_logresult.IsCompleted)
{
try { _logresult = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
else
{
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
else
{
_settings.RejectChanges(); SCMS.LogError(this.GetType().Name, new Exception(_queresult.Error));
MsgBoxEx.Alert("Failed to save application settings.", "Save Settings"); btnSave.Enabled = true;
}
}
}
}
}
public void GetAllProductsFromStore()
{
RequiredDataContainer DataMap = MarketingCMSDataProvider.MapEntitiesToCollections();
RequiredDataContainer DataMapDuplicates = MarketingCMSDataProvider.MapEntitiesToCollectionDuplicates();
RequiredDataContainer DataMapCategoryDuplicated = MarketingCMSDataProvider.MapEntitiesToCollectionCategoryDuplicated();
RequiredDataContainer DataMapStoreDuplicated = MarketingCMSDataProvider.MapEntitiesToCollectionsStoreDuplicated();
RequiredDataContainer DataMapUserDuplicated = MarketingCMSDataProvider.MapEntitiesToCollectionsUserDuplicated();
QueryableEntity Store = new QueryableEntity(DataMap.EntityRelationshipModel.FindByName("Store"));
QueryableEntity Product = new QueryableEntity(DataMap.EntityRelationshipModel.FindByName("Product"));
RelationshipJoinOperator JoinOpMap1 = new RelationshipJoinOperator(Store,
(Relationship)DataMap.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Product
},
DataMap.ERMongoMapping);
RelationshipJoinOperator JoinOpMap2 = new RelationshipJoinOperator(Store,
(Relationship)DataMapDuplicates.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Product
},
DataMapDuplicates.ERMongoMapping);
RelationshipJoinOperator JoinOpMap3 = new RelationshipJoinOperator(Store,
(Relationship)DataMapCategoryDuplicated.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Product
},
DataMapCategoryDuplicated.ERMongoMapping);
RelationshipJoinOperator JoinOpMap4 = new RelationshipJoinOperator(Store,
(Relationship)DataMapStoreDuplicated.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Product
},
DataMapStoreDuplicated.ERMongoMapping);
RelationshipJoinOperator JoinOpMap5 = new RelationshipJoinOperator(Store,
(Relationship)DataMapUserDuplicated.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Product
},
DataMapUserDuplicated.ERMongoMapping);
SortArgument SortArg = new SortArgument(Store, Store.GetAttribute("store_id"), MongoDBSort.Ascending);
SortStage SortOpMap1 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMap.ERMongoMapping);
SortStage SortOpMap2 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapDuplicates.ERMongoMapping);
SortStage SortOpMap3 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapCategoryDuplicated.ERMongoMapping);
SortStage SortOpMap4 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapStoreDuplicated.ERMongoMapping);
SortStage SortOpMap5 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapUserDuplicated.ERMongoMapping);
List <AlgebraOperator> OperatorsToExecuteMap1 = new List <AlgebraOperator>()
{
JoinOpMap1, SortOpMap1
};
List <AlgebraOperator> OperatorsToExecuteMap2 = new List <AlgebraOperator>()
{
JoinOpMap2, SortOpMap2
};
List <AlgebraOperator> OperatorsToExecuteMap3 = new List <AlgebraOperator>()
{
JoinOpMap3, SortOpMap3
};
List <AlgebraOperator> OperatorsToExecuteMap4 = new List <AlgebraOperator>()
{
JoinOpMap4, SortOpMap4
};
List <AlgebraOperator> OperatorsToExecuteMap5 = new List <AlgebraOperator>()
{
JoinOpMap5, SortOpMap5
};
FromArgument StartArgMap1 = new FromArgument(Store, DataMap.ERMongoMapping);
FromArgument StartArgMap2 = new FromArgument(Store, DataMapDuplicates.ERMongoMapping);
FromArgument StartArgMap3 = new FromArgument(Store, DataMapCategoryDuplicated.ERMongoMapping);
FromArgument StartArgMap4 = new FromArgument(Store, DataMapStoreDuplicated.ERMongoMapping);
FromArgument StartArgMap5 = new FromArgument(Store, DataMapUserDuplicated.ERMongoMapping);
QueryGenerator GeneratorMap1 = new QueryGenerator(StartArgMap1, OperatorsToExecuteMap1);
QueryGenerator GeneratorMap2 = new QueryGenerator(StartArgMap2, OperatorsToExecuteMap2);
QueryGenerator GeneratorMap3 = new QueryGenerator(StartArgMap3, OperatorsToExecuteMap3);
QueryGenerator GeneratorMap4 = new QueryGenerator(StartArgMap4, OperatorsToExecuteMap4);
QueryGenerator GeneratorMap5 = new QueryGenerator(StartArgMap5, OperatorsToExecuteMap5);
string QueryMap1 = GeneratorMap1.Run();
string QueryMap2 = GeneratorMap2.Run();
string QueryMap3 = GeneratorMap3.Run();
string QueryMap4 = GeneratorMap4.Run();
string QueryMap5 = GeneratorMap5.Run();
Assert.IsNotNull(QueryMap1, "Query [Map1] cannot be null");
Assert.IsNotNull(QueryMap2, "Query [Map2] cannot be null");
Assert.IsNotNull(QueryMap3, "Query [Map3] cannot be null");
Assert.IsNotNull(QueryMap4, "Query [Map4] cannot be null");
Assert.IsNotNull(QueryMap5, "Query [Map5] cannot be null");
QueryRunner RunnerMap1 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms");
QueryRunner RunnerMap2 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_duplicados");
QueryRunner RunnerMap3 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_category_duplicado");
QueryRunner RunnerMap4 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_store_duplicado");
QueryRunner RunnerMap5 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_user_duplicado");
string ResultMap1 = RunnerMap1.GetJSON(QueryMap1);
string ResultMap2 = RunnerMap2.GetJSON(QueryMap2);
string ResultMap3 = RunnerMap3.GetJSON(QueryMap3);
string ResultMap4 = RunnerMap4.GetJSON(QueryMap4);
string ResultMap5 = RunnerMap5.GetJSON(QueryMap5);
Assert.IsNotNull(ResultMap1, "Result [Map1] cannot be null");
Assert.IsNotNull(ResultMap2, "Result [Map2] cannot be null");
Assert.IsNotNull(ResultMap3, "Result [Map3] cannot be null");
Assert.IsNotNull(ResultMap4, "Result [Map4] cannot be null");
Assert.IsNotNull(ResultMap5, "Result [Map5] cannot be null");
Assert.IsTrue(ResultMap1 != string.Empty, "Result [Map1] cannot be empty");
Assert.IsTrue(ResultMap2 != string.Empty, "Result [Map2] cannot be empty");
Assert.IsTrue(ResultMap3 != string.Empty, "Result [Map3] cannot be empty");
Assert.IsTrue(ResultMap4 != string.Empty, "Result [Map4] cannot be empty");
Assert.IsTrue(ResultMap5 != string.Empty, "Result [Map5] cannot be empty");
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap2)));
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap3)));
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap4)));
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap5)));
}
public void GetAllProducts()
{
RequiredDataContainer DataMap = MarketingCMSDataProvider.MapEntitiesToCollections();
RequiredDataContainer DataMapDuplicates = MarketingCMSDataProvider.MapEntitiesToCollectionDuplicates();
RequiredDataContainer DataMapCategoryDuplicated = MarketingCMSDataProvider.MapEntitiesToCollectionCategoryDuplicated();
RequiredDataContainer DataMapStoreDuplicated = MarketingCMSDataProvider.MapEntitiesToCollectionsStoreDuplicated();
RequiredDataContainer DataMapUserDuplicated = MarketingCMSDataProvider.MapEntitiesToCollectionsUserDuplicated();
QueryableEntity Product = new QueryableEntity(DataMap.EntityRelationshipModel.FindByName("Product"));
QueryableEntity Store = new QueryableEntity(DataMap.EntityRelationshipModel.FindByName("Store"));
QueryableEntity Category = new QueryableEntity(DataMap.EntityRelationshipModel.FindByName("Category"));
QueryableEntity User = new QueryableEntity(DataMap.EntityRelationshipModel.FindByName("User"));
RelationshipJoinOperator RJoinStoreMap1 = new RelationshipJoinOperator(Product,
(Relationship)DataMap.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Store
},
DataMap.ERMongoMapping);
RelationshipJoinOperator RJoinCategoryMap1 = new RelationshipJoinOperator(Product,
(Relationship)DataMap.EntityRelationshipModel.FindByName("CategoryHasManyProducts"),
new List <QueryableEntity>()
{
Category
},
DataMap.ERMongoMapping);
RelationshipJoinOperator RJoinUserMap1 = new RelationshipJoinOperator(Product,
(Relationship)DataMap.EntityRelationshipModel.FindByName("HasManyProducts"),
new List <QueryableEntity>()
{
User
},
DataMap.ERMongoMapping);
// DEBUG
//LimitStage LimitOp = new LimitStage( 150000 );
SortArgument SortArg = new SortArgument(Product, Product.GetAttribute("product_id"), MongoDBSort.Ascending);
SortStage SortOp = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMap.ERMongoMapping);
SortStage SortOp2 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapDuplicates.ERMongoMapping);
SortStage SortOp3 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapCategoryDuplicated.ERMongoMapping);
SortStage SortOp4 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapStoreDuplicated.ERMongoMapping);
SortStage SortOp5 = new SortStage(new List <SortArgument>()
{
SortArg
}, DataMapUserDuplicated.ERMongoMapping);
// =====
// Build and execute Map1 query
List <AlgebraOperator> Map1OperatorsList = new List <AlgebraOperator>()
{
RJoinStoreMap1, RJoinCategoryMap1, RJoinUserMap1, SortOp
};
FromArgument StartArgMap1 = new FromArgument(Product, DataMap.ERMongoMapping);
QueryGenerator Generator = new QueryGenerator(StartArgMap1, Map1OperatorsList);
string Map1Query = Generator.Run();
Assert.IsNotNull(Map1Query, "Generated query [Map1Query] cannot be null");
RelationshipJoinOperator RJoinStoreMap2 = new RelationshipJoinOperator(Product,
(Relationship)DataMapDuplicates.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Store
},
DataMapDuplicates.ERMongoMapping);
RelationshipJoinOperator RJoinCategoryMap2 = new RelationshipJoinOperator(Product,
(Relationship)DataMapDuplicates.EntityRelationshipModel.FindByName("CategoryHasManyProducts"),
new List <QueryableEntity>()
{
Category
},
DataMapDuplicates.ERMongoMapping);
RelationshipJoinOperator RJoinUserMap2 = new RelationshipJoinOperator(Product,
(Relationship)DataMapDuplicates.EntityRelationshipModel.FindByName("HasManyProducts"),
new List <QueryableEntity>()
{
User
},
DataMapDuplicates.ERMongoMapping);
List <AlgebraOperator> MapDuplicatesOpList = new List <AlgebraOperator>()
{
RJoinStoreMap2, RJoinCategoryMap2, RJoinUserMap2, SortOp2
};
FromArgument StartArgMap2 = new FromArgument(Product, DataMapDuplicates.ERMongoMapping);
QueryGenerator GeneratorDuplicates = new QueryGenerator(StartArgMap2, MapDuplicatesOpList);
string MapDuplicatesQuery = GeneratorDuplicates.Run();
Assert.IsNotNull(MapDuplicatesQuery, "Generated query [MapDuplicatesQuery] cannot be null");
RelationshipJoinOperator RJoinStoreMap3 = new RelationshipJoinOperator(Product,
(Relationship)DataMapCategoryDuplicated.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Store
},
DataMapCategoryDuplicated.ERMongoMapping);
RelationshipJoinOperator RJoinCategoryMap3 = new RelationshipJoinOperator(Product,
(Relationship)DataMapCategoryDuplicated.EntityRelationshipModel.FindByName("CategoryHasManyProducts"),
new List <QueryableEntity>()
{
Category
},
DataMapCategoryDuplicated.ERMongoMapping);
RelationshipJoinOperator RJoinUserMap3 = new RelationshipJoinOperator(Product,
(Relationship)DataMapCategoryDuplicated.EntityRelationshipModel.FindByName("HasManyProducts"),
new List <QueryableEntity>()
{
User
},
DataMapCategoryDuplicated.ERMongoMapping);
List <AlgebraOperator> MapCategoryDuplicatedOpList = new List <AlgebraOperator>()
{
RJoinStoreMap3, RJoinCategoryMap3, RJoinUserMap3, SortOp3
};
FromArgument StartArgCategoryDuplicated = new FromArgument(Product, DataMapCategoryDuplicated.ERMongoMapping);
QueryGenerator GeneratorCategoryDuplicated = new QueryGenerator(StartArgCategoryDuplicated, MapCategoryDuplicatedOpList);
string MapCategoryDuplicatedQuery = GeneratorCategoryDuplicated.Run();
Assert.IsNotNull(MapCategoryDuplicatedQuery, "Generated query [MapCategoryDuplicatedQuery] cannot be null");
RelationshipJoinOperator RJoinStoreMap4 = new RelationshipJoinOperator(Product,
(Relationship)DataMapStoreDuplicated.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Store
},
DataMapStoreDuplicated.ERMongoMapping);
RelationshipJoinOperator RJoinCategoryMap4 = new RelationshipJoinOperator(Product,
(Relationship)DataMapStoreDuplicated.EntityRelationshipModel.FindByName("CategoryHasManyProducts"),
new List <QueryableEntity>()
{
Category
},
DataMapStoreDuplicated.ERMongoMapping);
RelationshipJoinOperator RJoinUserMap4 = new RelationshipJoinOperator(Product,
(Relationship)DataMapStoreDuplicated.EntityRelationshipModel.FindByName("HasManyProducts"),
new List <QueryableEntity>()
{
User
},
DataMapStoreDuplicated.ERMongoMapping);
List <AlgebraOperator> MapStoreDuplicatedOpList = new List <AlgebraOperator>()
{
RJoinStoreMap4, RJoinCategoryMap4, RJoinUserMap4, SortOp4
};
FromArgument StartArgStoreDuplicated = new FromArgument(Product, DataMapStoreDuplicated.ERMongoMapping);
QueryGenerator GeneratorStoreDuplicated = new QueryGenerator(StartArgStoreDuplicated, MapStoreDuplicatedOpList);
string MapStoreDuplicatedQuery = GeneratorStoreDuplicated.Run();
Assert.IsNotNull(MapStoreDuplicatedQuery, "Generated query [MapStoreDuplicatedQuery] cannot be null");
RelationshipJoinOperator RJoinStoreMap5 = new RelationshipJoinOperator(Product,
(Relationship)DataMapUserDuplicated.EntityRelationshipModel.FindByName("StoreHasManyProducts"),
new List <QueryableEntity>()
{
Store
},
DataMapUserDuplicated.ERMongoMapping);
RelationshipJoinOperator RJoinCategoryMap5 = new RelationshipJoinOperator(Product,
(Relationship)DataMapUserDuplicated.EntityRelationshipModel.FindByName("CategoryHasManyProducts"),
new List <QueryableEntity>()
{
Category
},
DataMapUserDuplicated.ERMongoMapping);
RelationshipJoinOperator RJoinUserMap5 = new RelationshipJoinOperator(Product,
(Relationship)DataMapUserDuplicated.EntityRelationshipModel.FindByName("HasManyProducts"),
new List <QueryableEntity>()
{
User
},
DataMapUserDuplicated.ERMongoMapping);
List <AlgebraOperator> MapUserDuplicatedOpList = new List <AlgebraOperator>()
{
RJoinStoreMap5, RJoinCategoryMap5, RJoinUserMap5, SortOp5
};
FromArgument StartArgUserDuplicated = new FromArgument(Product, DataMapUserDuplicated.ERMongoMapping);
QueryGenerator GeneratorUserDuplicated = new QueryGenerator(StartArgUserDuplicated, MapUserDuplicatedOpList);
string MapUserDuplicatedQuery = GeneratorUserDuplicated.Run();
Assert.IsNotNull(MapUserDuplicatedQuery, "Generated query [MapUserDuplicatedQuery] cannot be null");
QueryRunner RunnerMap1 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms");
QueryRunner RunnerMap2 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_duplicados");
QueryRunner RunnerMap3 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_category_duplicado");
QueryRunner RunnerMap4 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_store_duplicado");
QueryRunner RunnerMap5 = new QueryRunner("mongodb://localhost:27017", "pesquisa_cms_user_duplicado");
string ResultMap1 = RunnerMap1.GetJSON(Map1Query);
string ResultMap2 = RunnerMap2.GetJSON(MapDuplicatesQuery);
string ResultMap3 = RunnerMap3.GetJSON(MapCategoryDuplicatedQuery);
string ResultMap4 = RunnerMap4.GetJSON(MapStoreDuplicatedQuery);
string ResultMap5 = RunnerMap5.GetJSON(MapUserDuplicatedQuery);
Assert.IsNotNull(ResultMap1, "Result [Map1] cannot be null");
Assert.IsNotNull(ResultMap2, "Result [Map2] cannot be null");
Assert.IsNotNull(ResultMap3, "Result [Map3] cannot be null");
Assert.IsNotNull(ResultMap4, "Result [Map4] cannot be null");
Assert.IsNotNull(ResultMap5, "Result [Map5] cannot be null");
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap2)));
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap3)));
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap4)));
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(ResultMap1), JToken.Parse(ResultMap5)));
}
public InsertEntityToEntityStorageGenerator(string projectName, OperationInterfaceGenerator operationInterface, StorageInterfaceGenerator storageInterface,
InsertEntityToEntityStorage modelParameters, IList <Modelable> storages, QueryGenerator query)
: base(
projectName, "Operations", modelParameters.Name, typeof(InsertEntityToEntityStorageTemplate),
modelParameters, modelParameters.Name)
{
Query = query;
Storage = storages.Single(x => x.Key.Equals(modelParameters.EntityStorageName));
OperationInterface = operationInterface;
StorageInterface = storageInterface;
}
public static void Synchronize(STDataServerSyncsInfo syncInfo)
{
if (!syncInfo.FK_STDataServerID.HasValue)
{
return;
}
if (!syncInfo.IsPull && !syncInfo.IsPush)
{
return;
}
if (syncInfo.LastSyncDate.HasValue && syncInfo.IntervalMinute.HasValue &&
(DateTime.Now < syncInfo.LastSyncDate.Value || DateTime.Now.Subtract(syncInfo.LastSyncDate.Value).TotalMinutes < syncInfo.IntervalMinute.Value))
{
return;
}
STDataServersController serverCtrl = new STDataServersController();
STDataServersInfo server = serverCtrl.GetObjectByID(syncInfo.FK_STDataServerID.Value) as STDataServersInfo;
if (server == null)
{
return;
}
DBConnectionController connection = null;
if (DataStructureProvider.IsSystemTable(syncInfo.TableName))
{
connection = InitSystemConnection(server.STDataServerID);
}
else
{
connection = InitCompanyConnection(server.STDataServerID);
}
if (connection != null)
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
String strRunQuery = @"SET XACT_ABORT ON;BEGIN TRANSACTION ABCSYNC; ";
String strQuery = String.Empty;
if (syncInfo.IsPush)
{
if (syncInfo.IsPushMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [{0}].[{1}].[dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"INSERT INTO [{0}].[{1}].[dbo].[{2}] SELECT * FROM [dbo].[{2}] WHERE [dbo].[{2}].[{3}] IN ({4}) AND [dbo].[{2}].[{3}] NOT IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = String.Format(@"UPDATE [{0}].[{1}].[dbo].[{2}] SET #FIELD# FROM [{0}].[{1}].[dbo].[{2}] Target,[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [{0}].[{1}].[dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
if (syncInfo.IsPull)
{
if (syncInfo.IsPullMatched)
{
#region Delete
strQuery = String.Format(@"DELETE [dbo].[{2}] WHERE [{3}] NOT IN (SELECT [{3}] FROM [{0}].[{1}].[dbo].[{2}] )", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
#region Insert New Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colCreateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colCreateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"INSERT INTO [dbo].[{2}] SELECT * FROM [{0}].[{1}].[dbo].[{2}] WHERE [{3}] IN ({4}) AND [{3}] NOT IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
#region Update Modified Records
strQuery = QueryGenerator.GenSelect(syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), false, false);
if (syncInfo.LastSyncDate.HasValue && DataStructureProvider.IsTableColumn(syncInfo.TableName, ABCCommon.ABCConstString.colUpdateTime))
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} > '{1}'", ABCCommon.ABCConstString.colUpdateTime, syncInfo.LastSyncDate.Value.ToString("yyyy-MM-dd HH:mm:ss")));
}
strQuery = strQuery.Replace("[dbo]", String.Format(@"[{0}].[{1}].[dbo]", server.Name, connection.Connection.DatabaseName));
strQuery = String.Format(@"UPDATE [dbo].[{2}] SET #FIELD# FROM [dbo].[{2}] Target,[{0}].[{1}].[dbo].[{2}] Source WHERE Target.[{3}]=Source.[{3}] AND Source.[{3}] IN ({4}) AND Source.[{3}] IN (SELECT {3} FROM [dbo].[{2}])", server.Name, connection.Connection.DatabaseName, syncInfo.TableName, DataStructureProvider.GetPrimaryKeyColumn(syncInfo.TableName), strQuery);
List <String> lstSetFields = new List <string>();
foreach (String strField in DataStructureProvider.GetAllTableColumns(syncInfo.TableName).Keys)
{
if (DataStructureProvider.IsPrimaryKey(syncInfo.TableName, strField) == false)
{
lstSetFields.Add(String.Format(" [{0}]=Source.[{0}] ", strField));
}
}
strQuery = strQuery.Replace("#FIELD#", String.Join(",", lstSetFields));
strRunQuery = String.Format(@"{0} {1}", strRunQuery, Environment.NewLine + strQuery);
#endregion
}
strRunQuery = String.Format(@"{0} COMMIT TRANSACTION ABCSYNC; SET XACT_ABORT OFF;", strRunQuery + Environment.NewLine);
BusinessObjectController.RunQuery(strRunQuery, syncInfo.TableName);
BusinessObjectController.RunQuery(String.Format(@"UPDATE STDataServerSyncs SET LastSyncDate =GetDate() WHERE STDataServerSyncID='{0}'", syncInfo.GetID()), syncInfo.AATableName);
scope.Complete();
}
}
}
public IHttpActionResult Alert([FromBody] object data)
{
LinkedUser lu;
ArrayList linkedUsers = new ArrayList();
ArrayList emailsToAlert = new ArrayList();
string userName = string.Empty;
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
string lat = headers.GetValues(Location.COL_LAT).First();
string lng = headers.GetValues(Location.COL_LNG).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// Set flag for user table ---------------------
ArrayList assignmentsS1 = new ArrayList();
ArrayList conditionsS1 = new ArrayList();
string statement1;
assignmentsS1.Add(Models.User.COL_STATUS + "=6");
conditionsS1.Add(Models.User.COL_ID + "=" + id);
statement1 = QueryGenerator.GenerateSqlUpdate(Models.User.TABLE, assignmentsS1, conditionsS1);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement1.ToString();
cmd.ExecuteNonQuery();
// Set location for location table -------------
ArrayList valuesS2 = new ArrayList();
ArrayList assignmentsS2 = new ArrayList();
string statement2 = string.Empty;
valuesS2.Add(id);
valuesS2.Add(lat);
valuesS2.Add(lng);
valuesS2.Add(QueryGenerator.QuoteString(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")));
statement2 = QueryGenerator.GenerateSqlInsert(valuesS2, Location.TABLE);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement2;
cmd.ExecuteNonQuery();
// Get linked users ----------------------------
ArrayList columsS3 = new ArrayList();
ArrayList conditionsS3 = new ArrayList();
string statement3 = string.Empty;
columsS3.Add(LinkedUser.COL_ID);
columsS3.Add(LinkedUser.COL_USERID1);
columsS3.Add(LinkedUser.COL_USERID2);
columsS3.Add(LinkedUser.COL_ALERT1);
columsS3.Add(LinkedUser.COL_ALERT2);
columsS3.Add(LinkedUser.COL_MUTE1);
columsS3.Add(LinkedUser.COL_MUTE2);
columsS3.Add(LinkedUser.COL_DELETED);
columsS3.Add(LinkedUser.COL_ADDED1);
columsS3.Add(LinkedUser.COL_ADDED2);
conditionsS3.Add(LinkedUser.COL_USERID1 + "=" + id);
conditionsS3.Add(QueryGenerator.KW_OR);
conditionsS3.Add(LinkedUser.COL_USERID2 + "=" + id);
statement3 = QueryGenerator.GenerateSqlSelect(columsS3, LinkedUser.TABLE, conditionsS3);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement3;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
lu = new LinkedUser();
int i = 0;
lu.ID = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
if (lu.UserIDMe == int.Parse(id))
{
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
}
else
{
lu.UserIDTarget = dr.GetInt32(i);
i++;
lu.UserIDMe = dr.GetInt32(i);
i++;
lu.AlertTarget = dr.GetBoolean(i);
i++;
lu.AlertMe = dr.GetBoolean(i);
i++;
lu.MuteTarget = dr.GetBoolean(i);
i++;
lu.MuteMe = dr.GetBoolean(i);
i++;
lu.Deleted = dr.GetBoolean(i);
i++;
lu.AddedTarget = dr.GetBoolean(i);
i++;
lu.AddedMe = dr.GetBoolean(i);
}
linkedUsers.Add(lu);
}
dr.Close();
}
linkedUsers.Sort();
// Filter linked users -------------------------
for (int i = linkedUsers.Count - 1; i >= 0; --i)
{
LinkedUser linkedUser = (LinkedUser)linkedUsers[i];
if (!(linkedUser.AddedMe && linkedUser.AddedTarget) ||
!linkedUser.AlertMe ||
linkedUser.MuteTarget ||
linkedUser.Deleted)
{
linkedUsers.RemoveAt(i);
}
}
// Get linked user emails ----------------------
ArrayList columsS4 = new ArrayList();
ArrayList conditionsS4 = new ArrayList();
string statement4 = string.Empty;
columsS4.Add(Models.User.COL_DELETED);
columsS4.Add(Models.User.COL_EMAIL);
for (int i = 0; i < linkedUsers.Count; ++i)
{
if (i > 0)
{
conditionsS4.Add(QueryGenerator.KW_OR);
}
conditionsS4.Add(Models.User.COL_ID + "=" + ((LinkedUser)linkedUsers[i]).UserIDTarget);
}
statement4 = QueryGenerator.GenerateSqlSelect(columsS4, Models.User.TABLE, conditionsS4);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement4;
// Set linked user emails ----------------------
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (!dr.GetBoolean(0))
{
if (dr.GetValue(1) != DBNull.Value)
{
emailsToAlert.Add(dr.GetString(1));
}
}
}
dr.Close();
}
// Get this user's name ------------------------
ArrayList columsS5 = new ArrayList();
ArrayList conditionsS5 = new ArrayList();
string statement5 = string.Empty;
columsS5.Add(Models.User.COL_USERNAME);
conditionsS5.Add(Models.User.COL_ID + "=" + id);
statement5 = QueryGenerator.GenerateSqlSelect(columsS5, Models.User.TABLE, conditionsS5);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement5;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
userName = dr.GetString(0);
}
dr.Close();
}
}
con.Close();
}
// Send notification
foreach (string email in emailsToAlert)
{
string message = "[" + userName + "] Help!";
SendNotification(message, email);
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
if (emailsToAlert.Count == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No linked user is found.", "")));
}
//return ResponseMessage(JsonContent.ReturnMessage("Linked users are alerted.", ""));
return(Ok(emailsToAlert));
}
public void OneToManyComputedEntityMultipleEntities()
{
// Asserts if the query result for a relationship join operation is equal
// to a handcrafted query
RequiredDataContainer ModelData = ComputedEntityDataProvider.OneToManyComputedEntity();
// Load handcrafted query
string HandcraftedQuery = Utils.ReadQueryFromFile("HandcraftedQueries/ceOneToMany-2.js");
// Assert if the handcrafted query is not null
Assert.IsNotNull(HandcraftedQuery);
// Prepare query generator
ComputedEntity CarRepairedByGarage = new ComputedEntity("CarRepairedByGarage",
new QueryableEntity(( Entity)ModelData.EntityRelationshipModel.FindByName("Car"), "car"),
(Relationship)ModelData.EntityRelationshipModel.FindByName("Repaired"),
new List <QueryableEntity> {
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Garage"), "garage"),
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Supplier"), "supplier")
});
RelationshipJoinOperator RJoinOp = new RelationshipJoinOperator(
new QueryableEntity(( Entity)ModelData.EntityRelationshipModel.FindByName("Person"), "person"),
(Relationship)ModelData.EntityRelationshipModel.FindByName("Drives"),
new List <QueryableEntity> {
new QueryableEntity(CarRepairedByGarage)
},
ModelData.ERMongoMapping);
RelationshipJoinOperator RJoinOp2 = new RelationshipJoinOperator(
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Person"), "person"),
(Relationship)ModelData.EntityRelationshipModel.FindByName("HasInsurance"),
new List <QueryableEntity> {
new QueryableEntity((Entity)ModelData.EntityRelationshipModel.FindByName("Insurance"), "insurance")
},
ModelData.ERMongoMapping);
List <AlgebraOperator> OpList = new List <AlgebraOperator> {
RJoinOp, RJoinOp2
};
FromArgument StartArg = new FromArgument(new QueryableEntity(ModelData.EntityRelationshipModel.FindByName("Person")),
ModelData.ERMongoMapping);
QueryGenerator QueryGen = new QueryGenerator(StartArg, OpList);
string GeneratedQuery = QueryGen.Run();
// Assert if generated query is not null
Assert.IsNotNull(GeneratedQuery);
// Run Queries
QueryRunner Runner = new QueryRunner("mongodb://localhost:27017", "ceOneToMany");
string HandcraftedResult = Runner.GetJSON(HandcraftedQuery);
string GeneratedResult = Runner.GetJSON(GeneratedQuery);
// Check if either result is null
Assert.IsNotNull(HandcraftedResult);
Assert.IsNotNull(GeneratedResult);
// Check if both results are equal
Assert.IsTrue(JToken.DeepEquals(JToken.Parse(HandcraftedResult), JToken.Parse(GeneratedResult)));
}
public EmployeeResponse RetrieveEmployeeMetrics(string email)
{
using (IServiceScope scope = ServiceProvider.CreateScope())
{
IDBConnection dbConnection = scope.ServiceProvider.GetRequiredService <IDBConnection>();
dbConnection.Connect();
var result = dbConnection.ExecuteQuery <EmployeeMetrics>(QueryGenerator.GetEmployeeMetricsHeader() + QueryGenerator.WhereEmail(email));
return(result.Any() ? new EmployeeResponse(result.First()) : null);
}
}
public List <EmployeeResponse> RetrieveAllEmployeeMetrics()
{
using (IServiceScope scope = ServiceProvider.CreateScope())
{
IDBConnection dbConnection = scope.ServiceProvider.GetRequiredService <IDBConnection>();
dbConnection.Connect();
var results = dbConnection.ExecuteQuery <EmployeeMetrics>(QueryGenerator.GetEmployeeMetricsHeader() + QueryGenerator.WhereRole("Developer"));
var castedResults = new List <EmployeeResponse>();
foreach (var res in results)
{
castedResults.Add(new EmployeeResponse(res));
}
return(castedResults.Any() ? castedResults : null);
}
}
public void RetrieveEmployeeMetricsNoResults()
{
List <EmployeeMetrics> dbRetVal = new List <EmployeeMetrics>();
MockServiceProvider serviceProvider = new MockServiceProvider()
.AddMockDBConnection();
serviceProvider.MockDBConnection
.Setup(_ => _.ExecuteQuery <EmployeeMetrics>(QueryGenerator.GetEmployeeMetricsHeader() + QueryGenerator.WhereEmail("*****@*****.**")))
.Returns(dbRetVal);
EmployeeService employeeService = new EmployeeService(serviceProvider.Build());
EmployeeResponse response = employeeService.RetrieveEmployeeMetrics("*****@*****.**");
Assert.Null(response);
}
private static void GenerateSingleFile(TextReader input, string modelName)
{
QueryGenerator generator = new QueryGenerator(input, provider, options.BaseNamespace, null);
queryWriter.WriteModel(generator, modelName);
}
protected ModelApiControllerBase(IEventSender eventSender, QueryGenerator queryGenerator, string modelClassNameInUpperCamelCaseExcludeDots)
{
this.eventSender = eventSender;
this.queryGenerator = queryGenerator;
this.modelClassNameInUpperCamelCaseExcludeDots = modelClassNameInUpperCamelCaseExcludeDots;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (sender == null)
{
return;
}
if (!btnSave.Enabled)
{
return;
}
Validator _validator = SCMS.Validators[this];
if (!Materia.Valid(_validator, txtUsername, !String.IsNullOrEmpty(txtUsername.Text.RLTrim()), "Please specify the user account's logon name."))
{
return;
}
if (!Materia.Valid(_validator, txtPassword, !String.IsNullOrEmpty(txtPassword.Text.RLTrim()), "Please specify the user account's password."))
{
return;
}
if (!Materia.Valid(_validator, txtFirstName, !String.IsNullOrEmpty(txtFirstName.Text.RLTrim()), "Please specify the account holder's given name."))
{
return;
}
if (!Materia.Valid(_validator, txtLastName, !String.IsNullOrEmpty(txtLastName.Text.RLTrim()), "Please specify the account holder's surname."))
{
return;
}
if (!Materia.Valid(_validator, cboDepartment, cboDepartment.SelectedIndex >= 0, "Please specify a valid department."))
{
return;
}
if (!Materia.Valid(_validator, cboPosition, cboPosition.SelectedIndex >= 0, "Please specify a valid position."))
{
return;
}
DataTable _users = Cache.GetCachedTable("users");
if (_users != null)
{
DataRow[] _exists = _users.Select("([Username] LIKE '" + txtUsername.Text.ToSqlValidString(true) + "') AND\n" +
"NOT ([Username] LIKE '" + _username.ToSqlValidString(true) + "')");
if (!Materia.Valid(_validator, txtUsername, _exists.Length <= 0, "Username already exists."))
{
return;
}
string _role = (chkSuperUser.Checked ? SystemUserInfo.SuperUserRole : SystemUserInfo.RegularUserRole).ToString();
string _privileges = "";
if (chkSuperUser.Checked)
{
for (int i = 0; i <= 800; i++)
{
_privileges += "1";
}
}
if (_isnew)
{
_users.Rows.Add(new object[] {
txtUsername.Text, txtPassword.Text.Encrypt(SCMS.EncryptionKey),
txtFirstName.Text, txtMiddleName.Text, txtLastName.Text,
cboPosition.SelectedValue.ToString(), cboDepartment.SelectedValue.ToString(),
(chkActive.Checked? 1 : 0), _role, _privileges,
(chkAllowAllCustomers.Checked? 1 : 0), (chkAllowAllCompanies.Checked? 1:0),
DateTime.Now, DateTime.Now, 0, DBNull.Value
});
}
else
{
DataRow[] _rows = _users.Select("[Username] LIKE '" + _username.ToSqlValidString(true) + "'");
if (_rows.Length > 0)
{
DataRow _row = _rows[0];
_row["Username"] = txtUsername.Text;
_row["Password"] = txtPassword.Text.Encrypt(SCMS.EncryptionKey);
_row["FirstName"] = txtFirstName.Text;
_row["MiddleName"] = txtMiddleName.Text;
_row["LastName"] = txtLastName.Text;
_row["Position"] = cboPosition.SelectedValue.ToString();
_row["Department"] = cboDepartment.SelectedValue.ToString();
_row["Active"] = (chkActive.Checked ? 1 : 0);
_row["Role"] = _role; _row["Privileges"] = _privileges;
_row["AllCustomers"] = (chkAllowAllCustomers.Checked ? 1 : 0);
_row["AllCompanies"] = (chkAllowAllCompanies.Checked ? 1 : 0);
}
}
QueryGenerator _generator = new QueryGenerator(_users);
_generator.ExcludedFields.Add("LastModified");
string _query = _generator.ToString(); _generator = null;
Materia.RefreshAndManageCurrentProcess();
if (!String.IsNullOrEmpty(_query.RLTrim()))
{
if (Regex.IsMatch(_query, "WHERE[A-Za-z0-9\\s\\n\\r\\t\\W]+"))
{
string _tempquery = _query;
_query = Regex.Replace(_tempquery, "WHERE[A-Za-z0-9\\s\\n\\r\\t\\W]+", "WHERE (`Username` LIKE '" + _username.ToSqlValidString() + "')");
}
btnSave.Enabled = false; btnSaveAndClose.Enabled = false;
IAsyncResult _queresult = Que.BeginExecution(SCMS.Connection, _query);
while (!_queresult.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_queresult.IsCompleted)
{
try { _queresult = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
}
else
{
QueResult _result = Que.EndExecution(_queresult);
if (string.IsNullOrEmpty(_result.Error.RLTrim()))
{
_users.AcceptChanges(); Cache.Save();
if (!_withupdates)
{
_withupdates = true;
}
Cursor = Cursors.WaitCursor;
UserAction _action = UserAction.Add;
if (!_isnew)
{
_action = UserAction.Edit;
}
string _logdescription = "Added a new system user account : " + txtUsername.Text + ".";
if (!_isnew)
{
_logdescription = "Updated user account : " + _username + ".";
}
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(_action, _logdescription);
_logresult.WaitToFinish(); Cursor = Cursors.Default;
_isnew = false; _username = txtUsername.Text;
if (_updated)
{
_updated = false;
}
Text = Text.Replace(" *", "").Replace("*", "");
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
else
{
if (_result.Error.ToLower().Contains("duplicate"))
{
Materia.Valid(_validator, txtUsername, false, "Username already exists.");
}
else
{
SCMS.LogError(this.GetType().Name, new Exception(_result.Error));
MsgBoxEx.Alert("Failed to save the user account.", "Save User Account");
}
}
_result.Dispose(); _result = null; Materia.RefreshAndManageCurrentProcess();
btnSave.Enabled = true; btnSaveAndClose.Enabled = true;
}
}
else
{
if (_updated)
{
_updated = false;
}
Text = Text.Replace(" *", "").Replace("*", "");
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
}
}
else
{
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.None; Close();
}
}
}
public void RefreshDisplayGrid( )
{
DisplayGridView.TableName = this.TableName;
DisplayGridView.Columns.Clear();
DisplayGridView.Bands.Clear();
DisplayGridView.BandConfigs = this.BandsList;
DisplayGridView.ColumnConfigs = this.ColumnList;
DisplayGridView.LoadBands();
#region Script
if (String.IsNullOrWhiteSpace(this.TableName))
{
if (String.IsNullOrWhiteSpace(Script) == false)
{
DataSet ds = DataQueryProvider.RunQuery(Script);
if (ds != null && ds.Tables.Count > 0)
{
this.DisplayGridCtrl.DataSource = ds.Tables[0];
this.DisplayGridCtrl.RefreshDataSource();
DisplayGridView.ShowCustomization();
}
}
return;
}
#endregion
if (DataCachingProvider.LookupTables.ContainsKey(this.TableName))
{
this.DisplayGridCtrl.DataSource = DataCachingProvider.LookupTables[this.TableName];
}
else
{
ABCHelper.ConditionBuilder strBuilder = new ABCHelper.ConditionBuilder();
strBuilder.Append(String.Format(@"SELECT TOP 5 * FROM {0}", this.TableName));
if (DataStructureProvider.IsExistABCStatus(this.TableName))
{
strBuilder.AddCondition(QueryGenerator.GenerateCondition(this.TableName, ABCCommon.ABCColumnType.ABCStatus));
}
strBuilder.Append(String.Format(@" ORDER BY {0} DESC", DataStructureProvider.GetPrimaryKeyColumn(this.TableName)));
try
{
DataSet ds = DataQueryProvider.RunQuery(strBuilder.ToString());
if (ds != null && ds.Tables.Count > 0)
{
this.DisplayGridCtrl.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{
}
}
this.DisplayGridCtrl.RefreshDataSource();
if (DisplayGridView.CustomizationForm == null || DisplayGridView.CustomizationForm.Visible == false)
{
DisplayGridView.ShowCustomization();
}
}
public static void SynchronizeVoucherPermission( )
{
GEPermissionVouchersController voucherPermissionCtrl = new GEPermissionVouchersController();
String strQuery = String.Format(@"DELETE FROM GEPermissionVouchers WHERE FK_GEPermissionID NOT IN (SELECT GEPermissionID FROM GEPermissions)");
BusinessObjectController.RunQuery(strQuery);
strQuery = String.Format(@"DELETE FROM GEPermissionVouchers WHERE FK_GEVoucherID NOT IN ({0})", QueryGenerator.GenSelect("GEVouchers", "GEVoucherID", false));
BusinessObjectController.RunQuery(strQuery);
#region Init Vouchers
GEVouchersController voucherCtrl = new GEVouchersController();
Dictionary <Guid, GEVouchersInfo> lstGEVouchers = new Dictionary <Guid, GEVouchersInfo>();
DataSet ds = voucherCtrl.GetDataSetAllObjects();
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
GEVouchersInfo voucherInfo = voucherCtrl.GetObjectFromDataRow(dr) as GEVouchersInfo;
if (lstGEVouchers.ContainsKey(voucherInfo.GEVoucherID) == false)
{
lstGEVouchers.Add(voucherInfo.GEVoucherID, voucherInfo);
}
}
}
#endregion
foreach (GEPermissionsInfo permission in new GEPermissionsController().GetListAllObjects())
{
#region Voucher
Dictionary <Guid, GEPermissionVouchersInfo> lstGroupVouchers = new Dictionary <Guid, GEPermissionVouchersInfo>();
foreach (GEPermissionVouchersInfo voucherInfo in voucherPermissionCtrl.GetListByForeignKey("FK_GEPermissionID", permission.GEPermissionID).Cast <GEPermissionVouchersInfo>().ToList())
{
if (voucherInfo.FK_GEVoucherID.HasValue && lstGroupVouchers.ContainsKey(voucherInfo.FK_GEVoucherID.Value) == false)
{
if (lstGEVouchers.ContainsKey(voucherInfo.FK_GEVoucherID.Value))
{
lstGroupVouchers.Add(voucherInfo.FK_GEVoucherID.Value, voucherInfo);
}
else
{
voucherPermissionCtrl.DeleteObject(voucherInfo);
}
}
}
foreach (Guid voucherTypeID in lstGEVouchers.Keys)
{
if (lstGroupVouchers.ContainsKey(voucherTypeID) == false && lstGEVouchers[voucherTypeID].Title != String.Empty)
{
GEPermissionVouchersInfo voucherInfo = new GEPermissionVouchersInfo();
voucherInfo.FK_GEVoucherID = voucherTypeID;
voucherInfo.FK_GEPermissionID = permission.GEPermissionID;
voucherInfo.AllowView = true;
voucherInfo.AllowNew = true;
voucherInfo.AllowEdit = true;
voucherInfo.AllowDelete = true;
voucherInfo.AllowApproval = true;
voucherInfo.AllowLock = true;
voucherInfo.AllowPost = true;
voucherPermissionCtrl.CreateObject(voucherInfo);
lstGroupVouchers.Add(voucherTypeID, voucherInfo);
}
}
#endregion
}
}
static void Main(string[] args)
{
var mapping = QueryBuilderParser.ParseMapping(new FileStream(args[0], FileMode.Open));
Console.WriteLine($"ERModel: {mapping.EntityRelationshipModel.Name}");
Console.WriteLine("\n\n****** ER Model ********\n\n");
foreach (var e in mapping.EntityRelationshipModel.Elements.FindAll(e => e.GetType() == typeof(Entity)))
{
Console.WriteLine($"Entity: {e.Name}");
e.Attributes.ForEach(a =>
{
Console.WriteLine($" Attribute: {a.Name} of type {a.OfType}, multivalued={a.MultiValued}");
});
}
foreach (var r in mapping.EntityRelationshipModel.Elements.FindAll(e => e.GetType() == typeof(Relationship)).ConvertAll <Relationship>(e => (Relationship)e))
{
Console.WriteLine($"Relationship: {r.Name}");
r.Ends.ForEach(e =>
{
Console.WriteLine($" End: {e.TargetEntity.Name}");
});
r.Attributes.ForEach(a =>
{
Console.WriteLine($" Attribute: {a.Name} of type {a.OfType}, multivalued={a.MultiValued}");
});
}
Console.WriteLine("\n\n****** Mongo DB Schema ********\n\n");
foreach (var c in mapping.MongoDBSchema.Collections)
{
Console.WriteLine($"Collection: {c.Name}");
c.DocumentSchema.Attributes.ForEach(a =>
{
Console.WriteLine($" Field: {a.Name} of type {a.OfType}, multivalued={a.MultiValued}");
});
}
Console.WriteLine("\n\n****** Mapping ********\n\n");
foreach (var r in mapping.ERMongoMapping.Rules)
{
Console.WriteLine($"Rule: {r.Source.Name} = {r.Target.Name} (Main={r.IsMain})");
foreach (var sr in r.Rules)
{
Console.WriteLine($" {sr.Key} - {sr.Value}");
}
}
Console.WriteLine("\n\n****** Warnings and Errors ********\n\n");
mapping.Warnings.ForEach(w => Console.WriteLine(w));
mapping.Errors.ForEach(e => Console.WriteLine(e));
//string[] queries = { "from Author a rjoin <BookAndAuthor ba> (Book b)" };
string[] queries = { "from Author a rjoin <BookAndAuthor ba> (Book b rjoin <PublishedBy pb> (Publisher p))" };
// string[] queries = { "from Person p rjoin <Insurance i> (Car c, InsuranceCompany ic)",
// "from Car c rjoin <Repaired r> (Garage g)",
// "from Person p rjoin <Insurance i> (Car c rjoin <Repaired r> (Garage g), InsuranceCompany ic)",
// "from (Person p rjoin <Drives d> (Car c)) rjoin <Repaired r> (Garage g)",
// "from (Person p rjoin <Drives d> (Car c rjoin <Repaired r> (Garage g)))",
// "from Person p rjoin <Drives d> (Car c rjoin <Repaired r> (Garage g))" };
foreach (var q in queries)
{
Console.WriteLine(q);
var generatedQuery = QueryBuilderParser.ParseQuery(q, mapping);
Console.WriteLine("*************");
Console.WriteLine($"Start Argument: {generatedQuery.StartArgument.Entity.Element.Name} AS {generatedQuery.StartArgument.Entity.Alias}");
var i = 1;
foreach (var Op in generatedQuery.PipelineOperators)
{
Console.WriteLine($"Operator {i++}: {Op.ToString()}");
}
Console.WriteLine("*************");
QueryRunner runner = new QueryRunner("mongodb://localhost:27017", "sampleAuthorBookPublisher");
Console.WriteLine(runner.GetJSON(generatedQuery.Run()));
}
// Nested join example using manual code
Console.WriteLine("Running example ============");
QueryableEntity Author = new QueryableEntity(mapping.EntityRelationshipModel.FindByName("Author"));
QueryableEntity Book = new QueryableEntity(mapping.EntityRelationshipModel.FindByName("Book"));
QueryableEntity Publisher = new QueryableEntity(mapping.EntityRelationshipModel.FindByName("Publisher"));
Relationship BookAndAuthor = (Relationship)mapping.EntityRelationshipModel.FindByName("BookAndAuthor");
Relationship PublishedBy = (Relationship)mapping.EntityRelationshipModel.FindByName("PublishedBy");
ComputedEntity BookAndPublisher = new ComputedEntity("BookAndPublisher", Book,
PublishedBy, new List <QueryableEntity>()
{
Publisher
});
RelationshipJoinOperator JoinOp = new RelationshipJoinOperator(Author, BookAndAuthor,
new List <QueryableEntity>()
{
new QueryableEntity(BookAndPublisher)
},
mapping.ERMongoMapping);
FromArgument fromArg = new FromArgument(Author, mapping.ERMongoMapping);
List <AlgebraOperator> Operations = new List <AlgebraOperator>()
{
JoinOp
};
QueryGenerator queryGen = new QueryGenerator(fromArg, Operations);
string queryString = queryGen.Run();
QueryRunner queryRunner = new QueryRunner("mongodb://localhost:27017", "sampleAuthorBookPublisher");
Console.WriteLine(queryRunner.GetJSON(queryString));
}
public IHttpActionResult LoginByEmail([FromBody] object data)
{
User user = new User();
try
{
var headers = Request.Headers;
string[] authToken = headers.GetValues("Authorization").First().Split(' ');
string decodedToken = Encoding.UTF8.GetString(Convert.FromBase64String(authToken[1]));
string email = decodedToken.Substring(0, decodedToken.IndexOf(":"));
string password = decodedToken.Substring(decodedToken.IndexOf(":") + 1);
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList assignments = new ArrayList();
ArrayList conditions = new ArrayList();
string statement;
assignments.Add(Models.User.COL_LASTLOGIN
+ "=" + QueryGenerator.QuoteString(DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")));
assignments.Add(Models.User.COL_LOGIN + "=1");
conditions.Add(Models.User.COL_EMAIL + "=" + QueryGenerator.QuoteString(email));
conditions.Add(QueryGenerator.KW_AND);
conditions.Add(Models.User.COL_PASSWORD + "=" + QueryGenerator.QuoteString(password));
conditions.Add(QueryGenerator.KW_AND);
conditions.Add(Models.User.COL_DELETED + "=0");
statement = QueryGenerator.GenerateSqlUpdate(Models.User.TABLE, assignments, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
ArrayList columsS2 = new ArrayList();
ArrayList conditionsS2 = new ArrayList();
string statement2 = string.Empty;
columsS2.Add(Models.User.COL_ID);
columsS2.Add(Models.User.COL_USERNAME);
columsS2.Add(Models.User.COL_EMAIL);
columsS2.Add(Models.User.COL_PHONE);
columsS2.Add(Models.User.COL_LOGIN);
columsS2.Add(Models.User.COL_LASTLOGIN);
columsS2.Add(Models.User.COL_STATUS);
conditionsS2.Add(Models.User.COL_EMAIL + "=" + QueryGenerator.QuoteString(email));
conditionsS2.Add(QueryGenerator.KW_AND);
conditionsS2.Add(Models.User.COL_PASSWORD + "=" + QueryGenerator.QuoteString(password));
conditionsS2.Add(QueryGenerator.KW_AND);
conditionsS2.Add(Models.User.COL_DELETED + "=0");
statement2 = QueryGenerator.GenerateSqlSelect(columsS2, Models.User.TABLE, conditionsS2);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement2;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
user.ID = dr.GetInt32(0);
user.UserName = dr.GetString(1);
user.Email = dr.GetString(2);
user.Phone = dr.GetInt64(3);
user.Login = dr.GetBoolean(4);
if (dr.GetValue(5) != DBNull.Value)
{
user.LastLogin = dr.GetDateTime(5);
}
user.Status = dr.GetInt32(6);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", e.ToString())));
}
if (user.ID == 0)
{
new System.Net.Http.HttpResponseMessage(System.Net.HttpStatusCode.Unauthorized);
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
HttpContext.Current.User = new GenericPrincipal(new ApiIdentity(user), new string[] { });
return(Ok(new { user }));
}
public IHttpActionResult SetLinkedUserMute([FromBody] object data)
{
try
{
var headers = Request.Headers;
string idMe = headers.GetValues(Models.User.COL_ID).First();
string idTarget = headers.GetValues(LinkedUser.PARAM_TARGET).First();
string mute = headers.GetValues(LinkedUser.PARAM_MUTE).First();
if (mute.ToLower() == "true")
{
mute = "1";
}
else if (mute.ToLower() == "false")
{
mute = "0";
}
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
// condition 1
string c1;
ArrayList columnsC1 = new ArrayList();
ArrayList conditionsC1 = new ArrayList();
columnsC1.Add(LinkedUser.COL_USERID1);
conditionsC1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
conditionsC1.Add(QueryGenerator.KW_OR);
conditionsC1.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
c1 = QueryGenerator.GenerateSqlSelect(columnsC1, LinkedUser.TABLE, conditionsC1
, null, QueryGenerator.KW_ASC, 1);
c1 = QueryGenerator.ParenthesisString(c1) + "=" + idMe;
// conditions
ArrayList conditions = new ArrayList();
conditions.Add(c1);
// statement 1
ArrayList assignS1 = new ArrayList();
ArrayList conditionS1 = new ArrayList();
string s1;
assignS1.Add(LinkedUser.COL_MUTE1 + "=" + mute);
conditionS1.Add(LinkedUser.COL_USERID1 + "=" + idMe
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idTarget);
s1 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS1, conditionS1);
// statement 2
ArrayList assignS2 = new ArrayList();
ArrayList conditionS2 = new ArrayList();
string s2;
assignS2.Add(LinkedUser.COL_MUTE2 + "=" + mute);
conditionS2.Add(LinkedUser.COL_USERID1 + "=" + idTarget
+ QueryGenerator.SPACE + QueryGenerator.KW_AND + QueryGenerator.SPACE
+ LinkedUser.COL_USERID2 + "=" + idMe);
s2 = QueryGenerator.GenerateSqlUpdate(LinkedUser.TABLE, assignS2, conditionS2);
string statement;
statement = QueryGenerator.GenerateSqlIfElse(conditions, s1, s2);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement.ToString();
cmd.ExecuteNonQuery();
}
con.Close();
}
}
catch
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
return(ResponseMessage(JsonContent.ReturnMessage("The request is processed.", "")));
}
public IHttpActionResult GetUserById([FromBody] object data)
{
User user = new User();
try
{
var headers = Request.Headers;
string id = headers.GetValues(Models.User.COL_ID).First();
using (SqlConnection con = new SqlConnection(QueryGenerator.ConnectionString()))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
ArrayList colums = new ArrayList();
ArrayList conditions = new ArrayList();
string statement = string.Empty;
colums.Add(Models.User.COL_ID);
colums.Add(Models.User.COL_USERNAME);
colums.Add(Models.User.COL_EMAIL);
colums.Add(Models.User.COL_PHONE);
colums.Add(Models.User.COL_LOGIN);
colums.Add(Models.User.COL_LASTLOGIN);
colums.Add(Models.User.COL_STATUS);
conditions.Add(Models.User.COL_ID + "=" + id);
conditions.Add(QueryGenerator.KW_AND);
conditions.Add(Models.User.COL_DELETED + "=0");
statement = QueryGenerator.GenerateSqlSelect(colums, Models.User.TABLE, conditions);
cmd.CommandType = CommandType.Text;
cmd.CommandText = statement;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
user.ID = dr.GetInt32(0);
user.UserName = dr.GetString(1);
user.Email = dr.GetString(2);
user.Phone = dr.GetInt64(3);
user.Login = dr.GetBoolean(4);
if (dr.GetValue(5) != DBNull.Value)
{
user.LastLogin = dr.GetDateTime(5);
}
user.Status = dr.GetInt32(6);
}
dr.Close();
}
}
con.Close();
}
}
catch (Exception e)
{
return(ResponseMessage(JsonContent.ReturnMessage("The request is invalid.", "")));
}
if (user.ID == 0)
{
return(ResponseMessage(JsonContent.ReturnMessage("No user is found.", "")));
}
return(Ok(new { user }));
}
private void btnSave_Click(object sender, EventArgs e)
{
if (!btnSave.Enabled)
{
return;
}
Validator _validator = SCMS.Validators[this];
if (!Materia.Valid(_validator, txtAccountNo, !string.IsNullOrEmpty(txtAccountNo.Text.RLTrim()), "Please specify bank account number."))
{
return;
}
if (!Materia.Valid(_validator, txtAccountName, !string.IsNullOrEmpty(txtAccountName.Text.RLTrim()), "Please specify bank account name."))
{
return;
}
if (!Materia.Valid(_validator, cboBankingCompany, cboBankingCompany.SelectedIndex >= 0, "Please specify a valid banking company."))
{
return;
}
if (!Materia.Valid(_validator, cboCurrency, cboCurrency.SelectedIndex >= 0, "Please specify bank currency."))
{
return;
}
if (!Materia.Valid(_validator, cboAccountCode, cboAccountCode.SelectedIndex >= 0, "Please specify bank's associated G/L account."))
{
return;
}
DataTable _bankaccounts = Cache.GetCachedTable("bankaccounts");
if (_bankaccounts != null)
{
DataRow[] _rows = _bankaccounts.Select("([AccountNo] LIKE '" + txtAccountNo.Text.ToSqlValidString(true) + "' AND\n" +
" [Company] LIKE '" + SCMS.CurrentCompany.Company.ToSqlValidString(true) + "') AND\n" +
"NOT ([BankAccountCode] LIKE '" + _bankaccountcode.ToSqlValidString(true) + "')");
if (!Materia.Valid(_validator, txtAccountNo, _rows.Length <= 0, "Account already exists."))
{
return;
}
string _query = ""; string _refno = ""; string _seriesno = "";
DataColumnCollection _cols = _bankaccounts.Columns;
if (_isnew)
{
Func <string, bool, string> _delegate = new Func <string, bool, string>(SCMS.GetTableSeriesNumber);
IAsyncResult _result = _delegate.BeginInvoke("bankaccounts", true, null, _delegate);
while (!_result.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_result.IsCompleted)
{
try { _result = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
return;
}
_seriesno = _delegate.EndInvoke(_result);
_refno = "BANK-" + SCMS.CurrentCompany.Company + "-" + _seriesno;
object[] _values = new object[_cols.Count];
_values[_cols["BankAccountCode"].Ordinal] = _refno;
_values[_cols["AccountNo"].Ordinal] = txtAccountNo.Text;
_values[_cols["AccountName"].Ordinal] = txtAccountName.Text;
_values[_cols["Currency"].Ordinal] = cboCurrency.SelectedValue.ToString();
_values[_cols["AccountCode"].Ordinal] = cboAccountCode.SelectedValue;
_values[_cols["Swift"].Ordinal] = txtSwift.Text;
_values[_cols["IBAN"].Ordinal] = txtIban.Text;
_values[_cols["Bank"].Ordinal] = cboBankingCompany.SelectedValue;
_values[_cols["Branch"].Ordinal] = txtBranch.Text;
_values[_cols["Notes"].Ordinal] = txtNotes.Text;
_values[_cols["Company"].Ordinal] = SCMS.CurrentCompany.Company;
_values[_cols["DateCreated"].Ordinal] = DateTime.Now;
_values[_cols["LastModified"].Ordinal] = DateTime.Now;
_bankaccounts.Rows.Add(_values);
}
else
{
DataRow[] _existing = _bankaccounts.Select("[BankAccountCode] LIKE '" + _bankaccountcode.ToSqlValidString(true) + "'");
if (_existing.Length > 0)
{
_existing[0]["AccountNo"] = txtAccountNo.Text;
_existing[0]["AccountName"] = txtAccountName.Text;
_existing[0]["Currency"] = cboCurrency.SelectedValue;
_existing[0]["AccountCode"] = cboAccountCode.SelectedValue;
_existing[0]["Swift"] = txtSwift.Text;
_existing[0]["IBAN"] = txtIban.Text;
_existing[0]["Bank"] = cboBankingCompany.SelectedValue;
_existing[0]["Branch"] = txtBranch.Text;
_existing[0]["Notes"] = txtNotes.Text;
}
}
QueryGenerator _generator = new QueryGenerator(_bankaccounts);
_query = _generator.ToString();
_generator = null; Materia.RefreshAndManageCurrentProcess();
if (!string.IsNullOrEmpty(_query.RLTrim()))
{
btnSave.Enabled = false; btnSaveAndClose.Enabled = false;
IAsyncResult _result = Que.BeginExecution(SCMS.Connection, _query);
while (!_result.IsCompleted &&
!_cancelled)
{
Thread.Sleep(1); Application.DoEvents();
}
if (_cancelled)
{
if (!_result.IsCompleted)
{
try { _result = null; }
catch { }
finally { Materia.RefreshAndManageCurrentProcess(); }
}
return;
}
else
{
QueResult _queresult = Que.EndExecution(_result);
if (string.IsNullOrEmpty(_queresult.Error.RLTrim()))
{
UserAction _action = UserAction.Add;
if (!_isnew)
{
_action = UserAction.Edit;
}
string _log = "Added a new bank account : " + txtAccountNo.Text + " - " + txtAccountName.Text + ".";
if (!_isnew)
{
_log = "Updated bank account : " + txtAccountNo.Text + " - " + txtAccountName.Text + ".";
}
_bankaccounts.AcceptChanges();
if (_isnew)
{
_bankaccountcode = _refno;
}
if (_isnew)
{
_isnew = false;
}
if (_updated)
{
_updated = false;
}
if (!_withupdates)
{
_withupdates = true;
}
Text = Text.Replace(" *", "").Replace("*", "");
Cursor = Cursors.WaitCursor;
if (!txtSearch.AutoCompleteCustomSource.Contains(txtAccountNo.Text))
{
txtSearch.AutoCompleteCustomSource.Add(txtAccountNo.Text);
}
if (!txtSearch.AutoCompleteCustomSource.Contains(txtAccountName.Text))
{
txtSearch.AutoCompleteCustomSource.Add(txtAccountName.Text);
}
if (!txtSearch.AutoCompleteCustomSource.Contains(cboBankingCompany.SelectedValue.ToString()))
{
txtSearch.AutoCompleteCustomSource.Add(cboBankingCompany.SelectedValue.ToString());
}
if (!txtSearch.AutoCompleteCustomSource.Contains(cboAccountCode.SelectedValue.ToString()))
{
txtSearch.AutoCompleteCustomSource.Add(cboAccountCode.SelectedValue.ToString());
}
IAsyncResult _logresult = SCMS.CurrentSystemUser.LogActionAsync(_action, _log);
_logresult.WaitToFinish();
Cursor = Cursors.Default;
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.OK; Close();
}
else
{
if (!tbOutstanding.Visible)
{
tbOutstanding.Visible = true;
}
if (!tbBankLedger.Visible)
{
tbBankLedger.Visible = true;
}
}
}
else
{
if (_queresult.Error.Contains("duplicate"))
{
btnSave_Click(sender, new EventArgs());
}
else
{
SCMS.LogError(this.GetType().Name, new Exception(_queresult.Error));
MsgBoxEx.Alert("Failed to save the current bank account.", "Save Bank Account");
}
_bankaccounts.RejectChanges();
}
_queresult.Dispose();
}
btnSave.Enabled = true; btnSaveAndClose.Enabled = true;
}
}
else
{
if (sender == btnSaveAndClose)
{
DialogResult = System.Windows.Forms.DialogResult.None; Close();
}
}
}
public static String GetAlertQueryString(Guid alertID)
{
if (AlertList == null)
{
GetAlertConfigs(ABCUserProvider.CurrentUser.ADUserID);
}
if (AlertList.ContainsKey(alertID) == false)
{
return(String.Empty);
}
if (AlertQueryList.ContainsKey(alertID))
{
return(AlertQueryList[alertID]);
}
GEAlertsInfo alertInfo = AlertList[alertID];
if (alertInfo == null || alertInfo.GetID() == null)
{
return(String.Empty);
}
String strQuery = QueryGenerator.GenSelect(alertInfo.TableName, "*", false);
strQuery = QueryGenerator.AddCondition(strQuery, alertInfo.ConditionString);
#region Filter with current User
if (alertInfo.ByUser)
{
String strFK = DataStructureProvider.GetForeignKeyOfTableName(alertInfo.TableName, "ADUsers");
if (!String.IsNullOrWhiteSpace(strFK) && ABCUserProvider.CurrentUser != null)
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format(" {0} = '{1}'", strFK, ABCUserProvider.CurrentUser.ADUserID));
}
}
if (alertInfo.ByUserGroup)
{
String strFK = DataStructureProvider.GetForeignKeyOfTableName(alertInfo.TableName, "ADUsers");
if (!String.IsNullOrWhiteSpace(strFK) && ABCUserProvider.CurrentUser != null)
{
if (ABCUserProvider.CurrentUserGroup != null)
{
String strQueryUserID = QueryGenerator.AddEqualCondition(QueryGenerator.GenSelect("ADUsers", "ADUserID", false), "FK_ADUserGroupID", ABCUserProvider.CurrentUserGroup.ADUserGroupID);
strQuery = QueryGenerator.AddCondition(strQuery, String.Format("{0} IN ({1})", strFK, strQueryUserID));
}
}
else
{
strFK = DataStructureProvider.GetForeignKeyOfTableName(alertInfo.TableName, "ADUserGroups");
if (!String.IsNullOrWhiteSpace(strFK) && ABCUserProvider.CurrentUserGroup != null)
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format(" {0} = '{1}'", strFK, ABCUserProvider.CurrentUserGroup.ADUserGroupID));
}
}
}
if (alertInfo.ByEmployee)
{
String strFK = DataStructureProvider.GetForeignKeyOfTableName(alertInfo.TableName, "HREmployees");
if (!String.IsNullOrWhiteSpace(strFK) && ABCUserProvider.CurrentEmployee != null)
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format(" {0} = '{1}'", strFK, ABCUserProvider.CurrentEmployee.HREmployeeID));
}
}
if (alertInfo.ByCompanyUnit)
{
String strFK = DataStructureProvider.GetForeignKeyOfTableName(alertInfo.TableName, "GECompanyUnits");
if (!String.IsNullOrWhiteSpace(strFK) && ABCUserProvider.CurrentCompanyUnit != null)
{
strQuery = QueryGenerator.AddCondition(strQuery, String.Format(" {0} = '{1}'", strFK, ABCUserProvider.CurrentCompanyUnit.GECompanyUnitID));
}
else
{
if (!String.IsNullOrWhiteSpace(strFK) && !String.IsNullOrWhiteSpace(DataStructureProvider.GetForeignKeyOfTableName("HREmployees", "GECompanyUnits")))
{
String strTemp = QueryGenerator.GenSelect("GECompanyUnits", DataStructureProvider.GetForeignKeyOfTableName("HREmployees", "GECompanyUnits"), false);
strQuery = QueryGenerator.AddCondition(strQuery, String.Format(" {0} IN ({1})", strFK, strTemp));
}
}
}
#endregion
if (DataStructureProvider.IsTableColumn(alertInfo.TableName, ABCCommon.ABCConstString.colDocumentDate))
{
strQuery = strQuery + String.Format(@" ORDER BY {0} DESC", ABCCommon.ABCConstString.colDocumentDate);
}
AlertQueryList.Add(alertID, strQuery);
return(strQuery);
}