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