public static List <SelectDataModel> GetDataSources()
{
SGBD s = new SGBD();
List <SelectDataModel> sources = new List <SelectDataModel>();
DataTable brute = s.Cmd(Helper.GetSQL("SelectDataSources.sql"));
var last = string.Empty;
foreach (DataRow row in brute.Rows)
{
if (row["TABLE_NAME"].ToString() != last)
{
last = row["TABLE_NAME"].ToString();
sources.Add(new SelectDataModel()
{
Value = last,
Display = last.Replace("VIEW_", "").Replace("_BO_", ""),
Attributes = new Dictionary <string, object>()
});
}
var attributes = sources.Last().Attributes;
if (!attributes.Keys.Contains(row["Field"].ToString()))
{
attributes.Add(row["Field"].ToString(), new { primary = row["Key"].ToString(), FORM_SOURCE = row["FORM_SOURCE"].ToString() });
}
}
return(sources);
}
public void SetPlusValues()
{
try
{
//DataTable metaFields = new SGBD().Cmd("select *, JSON_VALUE(JSON_DATA,'$.DEFAULT') as 'DEFAULT' from META_FIELD where META_BO_ID = " + this.MetaBoID
// + " AND JSON_VALUE(JSON_DATA,'$.DEFAULT') like '%[+%]%'");
DataTable metaFields = new SGBD().Cmd("select * from META_FIELD where META_BO_ID = " + this.MetaBoID
+ " AND JSON_DATA like '%\"DEFAULT\":%[+%]%' ");
foreach (DataRow mf in metaFields.Rows)
{
if (this.Items.ContainsKey(mf["DB_NAME"].ToString()))
{
var jsonData = JsonConvert.DeserializeObject <Dictionary <string, string> >(mf["JSON_DATA"].ToString());
var df = new MetaFieldRepo().GetDefaultValue(jsonData["DEFAULT"].ToString(), this.MetaBO.BO_DB_NAME, 1);
if (df.type != "error")
{
this.Items[mf["DB_NAME"].ToString()] = df.value;
}
}
}
}
catch (Exception)
{
}
}
//public async System.Threading.Tasks.Task<List<SelectDataModel>> GetAsync(FAISEntities db)
//{
// return await db.Database.SqlQuery<SelectDataModel>(this.sqlQuery).ToListAsync();
//}
public List <SelectDataModel> Get()
{
SGBD s = new SGBD();
List <SelectDataModel> sources = new List <SelectDataModel>();
DataTable brute = s.Cmd(this.sqlQuery);
foreach (DataRow row in brute.Rows)
{
sources.Add(new SelectDataModel()
{
Value = row["value"].ToString(),
Display = row["display"].ToString(),
Attributes = new Dictionary <string, object>()
});
var attributes = sources.Last().Attributes;
foreach (DataColumn coll in brute.Columns)
{
attributes.Add(coll.ColumnName, row[coll.ColumnName].ToString());
}
}
return(sources);
}
public async Task <IHttpActionResult> GetOne(long id, long param)
{
var meta = await db.META_BO.FindAsync(id);
/* ACCESS RIGHTS */
try
{
UserRoleManager.Instance.VerifyRead(meta.BO_DB_NAME);
}
catch (UnauthorizedAccessException ex)
{
return(Content(HttpStatusCode.Forbidden, ex.Message));
}
/* FIN ACCESS RIGHTS */
var s = new SGBD();
var Gen = new BORepositoryGenerator();
Dictionary <string, object> bind = new Dictionary <string, object>();
bind.Add("BO_ID", param);
var dt = s.Cmd(Gen.GenSelectOne(meta.BO_DB_NAME), bind);
return(Ok(dt));
}
public async Task <IHttpActionResult> SelectChilds(string Tname, long parentId)
{
var meta = await db.META_BO.Where(x => x.BO_DB_NAME == Tname).FirstOrDefaultAsync();
if (meta == null)
{
return(BadRequest());
}
/* ACCESS RIGHTS */
try
{
UserRoleManager.Instance.VerifyRead(meta.BO_DB_NAME);
}
catch (UnauthorizedAccessException ex)
{
return(Content(HttpStatusCode.Forbidden, ex.Message));
}
/* FIN ACCESS RIGHTS */
var s = new SGBD();
var Gen = new BORepositoryGenerator();
var dt = s.Cmd(Gen.GenSelectChilds(meta.BO_DB_NAME, parentId));
return(Ok(dt));
}
public async Task <IHttpActionResult> FilterIncludeSubForm(FilterModel model)
{
var meta = await db.META_BO.FindAsync(model.MetaBoID);
/* ACCESS RIGHTS */
try
{
UserRoleManager.Instance.VerifyRead(meta.BO_DB_NAME);
}
catch (UnauthorizedAccessException ex)
{
return(Content(HttpStatusCode.Forbidden, ex.Message));
}
/* FIN ACCESS RIGHTS */
if (meta == null)
{
return(BadRequest());
}
var s = new SGBD();
var Gen = new BORepositoryGenerator();
string reqSelect = Gen.GenSelectIncludeSubForm(meta, db.GetSubForm((int)meta.META_BO_ID).FirstOrDefault()) + " where 1=1 " + model.Format();
var dt = s.Cmd(reqSelect, model.mapping);
return(Ok(dt));
//return Ok();
}
public async Task <IHttpActionResult> valider(int id, string status, int boid)
{
var s = new SGBD();
s.Cmd("update Task set etat = 1 where task_id=" + id);
s.Cmd("update bo set status='" + status + "' where BO_ID=" + boid);
return(Ok(new { success = true, status }));
}
public ActionResult Img(string bo, string field, string id)
{
var s = new SGBD();
var Gen = new BORepositoryGenerator();
var dt = s.Cmd(Gen.GenSelectFields(bo, new List <string> {
field
}, " where c.BO_ID=" + id));
ImageBase64 img = System.Web.Helpers.Json.Decode <ImageBase64>(dt.Rows[0][field].ToString());
return(base.File(Convert.FromBase64String(img.Base64.Split(new string[] { "base64," }, StringSplitOptions.None)[1]), "image/jpeg"));
}
public async System.Threading.Tasks.Task CommitAsync(long id, string userName)
{
VERSIONS vERSIONS = await db.VERSIONS.FindAsync(id);
if (vERSIONS == null)
{
throw new Exception("VERSION NOT FOUND");
}
META_BO mETA_BO = await new MetaBoRepo().GetMETAForCommitAsync(vERSIONS.META_BO_ID.Value);
if (mETA_BO.META_FIELD.Count <= 0)
{
throw new Exception("No meta field found !");
}
var fields = "";
foreach (var f in mETA_BO.META_FIELD)
{
fields += string.Format(" [{0}] {1} {2} , "
, f.DB_NAME
, f.DB_TYPE == "DateTime" ? " varchar(20) " : f.DB_TYPE
, f.DB_NULL == 0 ? " NOT NULL " : " NULL "
);
}
var sqlQuery = Helper.GetSQL("CreateTable.sql");
sqlQuery = string.Format(sqlQuery,
vERSIONS.VERSIONS_ID.ToString()
, mETA_BO.BO_DB_NAME
, fields
, userName
, mETA_BO.META_BO_ID.ToString()
, vERSIONS.NUM
, mETA_BO.BO_DB_NAME);
sqlQuery = sqlQuery.Replace("[SQLQUERY]", sqlQuery.Replace("'", "''"));
var s = new SGBD();
s.Cmd(sqlQuery);
}
public async Task <IHttpActionResult> ValidateWokflow(int id)
{
var s = new SGBD();
var tasks = s.Cmd("select top 1 * from Task where bo_id=" + id + " and etat=0 order by task_id ");
if (tasks.Rows.Count > 0)
{
if (tasks.Rows[0]["task_type"].ToString() == "VALIDATION")
{
bool mine = false;
var listV = System.Web.Helpers.Json.Decode(tasks.Rows[0]["JSON_DATA"].ToString());
foreach (var v in listV)
{
if (v.email == User.Identity.Name)
{
mine = true;
}
}
if (mine)
{
return(Ok(new { success = true, task_id = tasks.Rows[0]["task_id"], type = tasks.Rows[0]["task_type"], status = tasks.Rows[0]["STATUS"] }));
}
else
{
return(Ok(new { success = false }));
}
}
else
{
var dt = s.Cmd("select * from META_BO where META_BO_ID = (select bo_type from BO where BO_ID = " + id + ")");
await Insert_Bo_Using_Mapping(int.Parse(dt.Rows[0]["META_BO_ID"].ToString()), dt.Rows[0]["BO_DB_NAME"].ToString(), id, int.Parse(tasks.Rows[0]["task_id"].ToString()));
return(Ok(new { success = true, type = "BO", bo = dt.Rows[0]["BO_NAME"].ToString() }));
}
}
return(Ok(new { success = false }));
}
// GET api/values
public IHttpActionResult Get()
{
SGBD s = new SGBD();
DataTable types = s.Cmd("select * from [dbo].[Types_BO_]");
DataTable t = s.Cmd(@"
select
BO_ID,
Type,
[Transaction],
Emplacement,
Pieces,
Salles_de_bains,
quipements_et_Caracteristiques,
Prix,
Description,
Premium,
Titre,
Superficie,
Refrence,
Image_2
from akkorimm_admin_user.article_BO_
Where Active = 1 AND Refrence is not null");
var biens = new XElement("biens");
foreach (DataRow row in t.Rows)
{
var bien = new XElement("bien");
bien.SetAttributeValue("REFERENCE", row["Refrence"].ToString());
bien.Add(new XElement("TITLE", row["Titre"].ToString()));
bien.Add(new XElement("TYPE", types.Select("BO_ID = " + row["Type"].ToString())[0]["Label"].ToString()));
bien.Add(new XElement("TRANSACTION", types.Select("BO_ID = " + row["Transaction"].ToString())[0]["Label"].ToString()));
bien.Add(new XElement("DESCRIPTION", row["Description"].ToString()));
bien.Add(new XElement("CITY", types.Select("BO_ID = " + row["Emplacement"].ToString())[0]["Label"].ToString()));
bien.Add(new XElement("Currency", "MAD"));
bien.Add(new XElement("PRICE", row["Prix"].ToString()));
bien.Add(new XElement("SURFACE", row["Superficie"].ToString()));
bien.Add(new XElement("NBRE_PIECES", row["Pieces"].ToString()));
bien.Add(new XElement("NBRE_BATHS", row["Salles_de_bains"].ToString()));
bien.Add(new XElement("FULLKITCHEN", row["quipements_et_Caracteristiques"].ToString().Contains("\"37\"") ? "yes" : "no"));
bien.Add(new XElement("TERRACE", row["quipements_et_Caracteristiques"].ToString().Contains("\"29\"") ? "yes" : "no"));
bien.Add(new XElement("POOL", row["quipements_et_Caracteristiques"].ToString().Contains("\"17\"") ? "yes" : "no"));
bien.Add(new XElement("GARDEN", row["quipements_et_Caracteristiques"].ToString().Contains("\"27\"") ? "yes" : "no"));
bien.Add(new XElement("ELEVATOR", row["quipements_et_Caracteristiques"].ToString().Contains("\"28\"") ? "yes" : "no"));
bien.Add(new XElement("HEATING", row["quipements_et_Caracteristiques"].ToString().Contains("\"53\"") ? "yes" : "no"));
bien.Add(new XElement("FIREPLACE", row["quipements_et_Caracteristiques"].ToString().Contains("\"67\"") ? "yes" : "no"));
bien.Add(new XElement("TV", row["quipements_et_Caracteristiques"].ToString().Contains("\"18\"") ? "yes" : "no"));
try
{
// GET IMAGES
var d = new DirectoryInfo(System.Web.Hosting.HostingEnvironment.MapPath("~/Content/files/" + row["Image_2"].ToString()));
//bien.PHOTO1 = "http://net.akkor-immobilier.com/Content/files/"+ row["Image_2"].ToString() + "/" + d.GetFiles().FirstOrDefault().Name;
int idx = 1;
foreach (var x in d.GetFiles())
{
bien.Add(new XElement("PHOTO" + idx, "http://net.akkor-immobilier.com/Content/files/" + row["Image_2"].ToString() + "/" + x.Name));
idx++;
}
}
catch (Exception ex)
{
//throw ex;
}
biens.Add(bien);
}
//return biens;
return(Content(HttpStatusCode.OK, biens, Configuration.Formatters.XmlFormatter));
}
//Insert data automaticly using the Mapping on the WORKFLOWs
public async Task <IHttpActionResult> Insert_Bo_Using_Mapping(int id, string BO_DB_NAME, int bo_id, int taks_id)
{
var s = new SGBD();
dynamic _JSON_MAPP;
// DATA
var mapping = s.Cmd("select * from TASK t where TASK_TYPE='BO' and TASK_ID=" + taks_id + " and bo_id not in (select bo_id from task where TASK_TYPE='VALIDATION' and etat=0 and BO_ID=t.BO_ID)");
if (mapping.Rows.Count == 0)
{
return(NotFound());
}
_JSON_MAPP = System.Web.Helpers.Json.Decode(mapping.Rows[0]["JSON_DATA"].ToString());
Dictionary <string, object> JSONA_STRING = new Dictionary <string, object>();
Dictionary <string, object> JSONA_STRING_SUBFORM = new Dictionary <string, object>();
foreach (var item in _JSON_MAPP["MyMapping"]["mapping"])
{
var originalField = s.Cmd("select DB_NAME from META_FIELD where FORM_NAME='" + item["parent"].ToString() + "' and META_BO_ID=" + id);
var value = s.Cmd("select " + originalField.Rows[0][0].ToString() + " from " + BO_DB_NAME + " where BO_ID =" + bo_id);
JSONA_STRING.Add(item.child_to_db.ToString(), value.Rows[0][0].ToString());
}
// DAT SUB-FORM
var parentData = await Insert((int)_JSON_MAPP["value"], JSONA_STRING);
//////foreach (var item_sub_form in _JSON_MAPP["MyMapping"]["mapping_sub_form"])
//////{
////// var originalField = s.Cmd("select DB_NAME from META_FIELD where FORM_NAME='" + item_sub_form["parent"].ToString() + "' and META_BO_ID=" + item_sub_form["id_subform_p"]);
////// var tableName = s.Cmd("select BO_DB_NAME from META_bo where META_BO_ID=" + item_sub_form["id_subform_p"]);
////// var value_s = s.Cmd("select " + originalField.Rows[0][0].ToString() + " from " + tableName.Rows[0][0].ToString() + " where BO_ID in (select BO_CHILD_ID from BO_CHILDS where BO_PARENT_ID = " + bo_id + ")");
////// foreach (var v in value_s.Rows)
////// {
////// JSONA_STRING_SUBFORM = new Dictionary<string, object>();
////// JSONA_STRING_SUBFORM.Add(item_sub_form.child_to_db_sb.ToString(), ((System.Data.DataRow)v).ItemArray[0].ToString());
////// await InsertChilds((int)item_sub_form["id_subform_c"], (long)BO_Insert_return, JSONA_STRING_SUBFORM);
////// }
//////}
// TEST BEGIN
var dt = s.Cmd("select BO_CHILD_ID from BO_CHILDS where BO_PARENT_ID = " + bo_id);
int id_subform_c = 0;
foreach (var r in dt.Rows)
{
JSONA_STRING_SUBFORM = new Dictionary <string, object>();
foreach (var item_sub_form in _JSON_MAPP["MyMapping"]["mapping_sub_form"])
{
var originalField = s.Cmd("select DB_NAME from META_FIELD where FORM_NAME='" + item_sub_form["parent"].ToString() + "' and META_BO_ID=" + item_sub_form["id_subform_p"]);
var tableName = s.Cmd("select BO_DB_NAME from META_bo where META_BO_ID=" + item_sub_form["id_subform_p"]);
var value_s = s.Cmd("select " + originalField.Rows[0][0].ToString() + " from " + tableName.Rows[0][0].ToString() + " where BO_ID =" + ((System.Data.DataRow)r).ItemArray[0].ToString());
id_subform_c = (int)item_sub_form["id_subform_c"];
JSONA_STRING_SUBFORM.Add(item_sub_form.child_to_db_sb.ToString(), value_s.Rows[0][0].ToString());
}
await InsertChilds(id_subform_c, (long)BO_Insert_return, JSONA_STRING_SUBFORM);
}
//TEST FIN
s.Cmd("update task set ETAT=1 where task_id=" + taks_id);
return(Ok(BO_DB_NAME));
}
public async Task <IHttpActionResult> Insert(int id, Dictionary <string, object> Items)
{
Logger.Info("POST Crud: " + id);
var model = new CrudModel()
{
MetaBoID = id,
Items = Items
};
// var meta = await db.META_BO.FindAsync(model.MetaBoID);
var meta = db.META_BO.Find(model.MetaBoID);
/* ACCESS RIGHTS */
try
{
UserRoleManager.Instance.VerifyWrite(meta.BO_DB_NAME);
}
catch (UnauthorizedAccessException ex)
{
return(Content(HttpStatusCode.Forbidden, ex.Message));
}
/* FIN ACCESS RIGHTS */
BO bo_model = new BO()
{
CREATED_BY = User.Identity.Name,
CREATED_DATE = DateTime.Now,
UPDATED_BY = User.Identity.Name,
UPDATED_DATE = DateTime.Now,
STATUS = "1",
BO_TYPE = model.MetaBoID.ToString(),
VERSION = meta.VERSION
};
db.BO.Add(bo_model);
await db.SaveChangesAsync();
int id_ = (int)bo_model.BO_ID;
BO_Insert_return = id_;
model.MetaBO = meta;
model.BO_ID = id_;
model.Items.Add("BO_ID", model.BO_ID);
//return Ok(model.FormatInsert());
string insert = model.Insert();
// Workflow executer begin
var s = new SGBD();
var db_workflow = s.Cmd(" select * from WORKFLOW CROSS APPLY OPENJSON(ITEMS) with(type varchar(50) '$.type', " +
" precedent varchar(50) '$.precedent', [index] int '$.index', val nvarchar(500) '$.value.value') as jsonValues where ACTIVE = 1 and jsonValues.val = " + id +
"and jsonValues.type = 'bo'");
dynamic _JSON;
var where = "";
foreach (DataRow item in db_workflow.Rows)
{
_JSON = System.Web.Helpers.Json.Decode(item["ITEMS"].ToString());
int level = 0;
for (var i = (int)item["index"] + 1; i < _JSON.Length; i++)
{
var elm = _JSON[i];
if (elm["status"] != "deleted")
{
where = "";
level++;
if (elm["type"] == "validation")
{
foreach (var rule in elm.value.rules)
{
var value = rule["value"];
if (!"int,float,decimal,nvarchar(MAX)".Contains(rule["field"]["DB_TYPE"]))
{
value = "'" + value + "'";
}
where += " " + rule["logic"] + " " + rule["field"]["DB_NAME"] + " " + rule["condition"] + " " + value;
}
var check_validation = s.Cmd("select * from " + meta.BO_DB_NAME + " where BO_ID=" + model.BO_ID + " " + where);
if (check_validation.Rows.Count > 0)
{
TASK valid = new TASK()
{
BO_ID = id_,
JSON_DATA = System.Web.Helpers.Json.Encode(elm.value.validators),
CREATED_BY = User.Identity.Name,
CREATED_DATE = DateTime.Now,
STATUS = elm.value.status,
ETAT = 0,
TASK_LEVEL = level,
TASK_TYPE = "VALIDATION"
};
db.TASK.Add(valid);
foreach (var _validator in elm.value.validators)
{
NOTIF notification = new NOTIF()
{
VALIDATOR = _validator["email"],
CREATED_DATE = DateTime.Now,
ETAT = 0
};
db.NOTIF.Add(notification);
}
db.SaveChanges();
}
}
else if (elm["type"] == "bo")
{
TASK valid = new TASK()
{
BO_ID = id_,
JSON_DATA = System.Web.Helpers.Json.Encode(elm.value),
CREATED_BY = User.Identity.Name,
CREATED_DATE = DateTime.Now,
ETAT = 0,
TASK_LEVEL = level,
TASK_TYPE = "BO"
};
db.TASK.Add(valid);
db.SaveChanges();
var task_id = (int)valid.TASK_ID;
// await Insert_Bo_Using_Mapping(id, meta.BO_DB_NAME, model.BO_ID, task_id);
}
}
}
}
if (insert == "")
{
BO_Insert_return = id_;
return(Ok(model));
}
else
{
return(InternalServerError(new Exception(insert)));
}
}
