/// <summary> /// Obtiene las propiedades de una tabla a partir de una entidad /// </summary> /// <param name="entity">Entidad para buscar su tabla</param> /// <param name="database">enumerado para saber a que base de datos se va a conectar</param> /// <returns>Lista de tipo ColumnDefinition con todos los datos</returns> /// <history> /// [emoguel] created 05/08/2016 /// </history> public static List <ColumnDefinition> GetFieldsByTable <T>(T entity, EnumDatabase database) where T : class { DbContext dbContext = null; #region dbContext switch (database) { case EnumDatabase.IntelligentMarketing: { dbContext = new IMEntities(ConnectionHelper.ConnectionString()); break; } case EnumDatabase.Asistencia: { dbContext = new AsistenciaEntities(ConnectionHelper.ConnectionString(database)); break; } case EnumDatabase.IntelligenceContracts: { dbContext = new ICEntities(ConnectionHelper.ConnectionString(database)); break; } } #endregion using (dbContext) { string strTableName = GetTableName(entity, dbContext); string strDatabaseName = dbContext.Database.Connection.Database; #region Query string strQuery = "Use " + strDatabaseName + @" SELECT C.name as [Column], T.name as [Type], C.Precision, C.Scale, CASE WHEN CE.NUMERIC_PRECISION IS NOT NULL THEN CE.NUMERIC_PRECISION + (CASE WHEN CE.NUMERIC_SCALE > 0 THEN 1 ELSE 0 END) ELSE IsNull(CE.CHARACTER_MAXIMUM_LENGTH, C.precision) END as MaxLength, CE.COLUMN_DEFAULT as [Default Value], CE.IS_NULLABLE as Nullable, IsNull(P.value, '') as Description FROM sys.tables as TA LEFT JOIN sysusers S on TA.schema_id = S.uid INNER JOIN sys.columns as C on C.object_id = TA.object_id INNER JOIN sys.types as T on T.system_type_id = C.system_type_id and T.name <> 'sysname' LEFT JOIN INFORMATION_SCHEMA.COLUMNS CE ON CE.TABLE_SCHEMA = S.name AND CE.TABLE_NAME = TA.name AND CE.COLUMN_NAME = C.name LEFT JOIN sys.extended_properties as P on P.major_id = C.object_id and P.minor_id = C.column_id and P.class = 1 WHERE TA.name = '" + strTableName + @"' ORDER BY S.name, TA.name, C.column_id "; #endregion var dQuery = dbContext.Database.SqlQuery <ColumnDefinition>(strQuery); return(dQuery.ToList()); } }
/// <summary> /// Elimina MemberSalesmen /// </summary> /// <history> /// [jorcanche] created 08/07/2016 /// </history> public static async Task <int> DeleteMemberSalesman(decimal?cLmemopcId) { return(await Task.Run(() => { using (var dbContext = new ICEntities(ConnectionHelper.ConnectionString(EnumDatabase.IntelligenceContracts))) { return dbContext.USP_CL_DeleteMemberSalesman(cLmemopcId); } })); }
/// <summary> /// Devuelve la lista MemberSalesmen /// </summary> /// <returns>Lista de tipo MemberSalesmen</returns> /// <history> /// [jorcanche] created 08/07/2016 /// </history> public static async Task <List <MemberSalesmen> > GetMemberSalesmen(string application, string job, string code, string roles) { return(await Task.Run(() => { using (var dbContext = new ICEntities(ConnectionHelper.ConnectionString(EnumDatabase.IntelligenceContracts))) { return dbContext.USP_CL_GetMemberSalesmen(application, job, code, roles).ToList(); } })); }
/// <summary> /// Devuelve un boleano indicando si existe o no la membresia que se mando como parametro en la base de Intelligence Contracts /// </summary> /// <param name="membershipNum">Numero de membresia</param> /// <history> /// [jorcanche] created 01/ago/2016 /// </history> public static async Task <bool> ExistsMembershipNum(string membershipNum) { return(await Task.Run(() => { using (var dbContext = new ICEntities(ConnectionHelper.ConnectionString(EnumDatabase.IntelligenceContracts))) { return dbContext.Members.Any(me => me.APPLICATION == membershipNum); } })); }
/// <summary> /// Indica si existe un Salesman /// </summary> /// <param name="zOna"></param> /// <param name="cOde"></param> /// <history> /// [jorcanche] created 08/07/2016 /// </history> public static async Task <bool> ExistsSalesman(string zOna, string cOde) { return(await Task.Run(() => { using (var dbContext = new ICEntities(ConnectionHelper.ConnectionString(EnumDatabase.IntelligenceContracts))) { var aa = dbContext.USP_CL_ExistsSalesman(zOna, cOde).FirstOrDefault() == 1; return aa; } })); }
/// <summary> /// Obtiene el resultado en tipo de Salesman cuando se guarda est /// </summary> /// <param name="rEcnum"></param> /// <param name="memberSalesmen"></param> /// <param name="user"></param> /// <history> /// [jorcanche] created 08/07/2016 /// </history> public static async Task <MemberSalesmen> SaveMemberSalesman(decimal?reccum, MemberSalesmen memberSalesmen, string user) { return(await Task.Run(() => { using (var dbContext = new ICEntities(ConnectionHelper.ConnectionString(EnumDatabase.IntelligenceContracts))) { return dbContext.USP_CL_SaveMemberSalesman( reccum, memberSalesmen.CLMEMOPC_ID, memberSalesmen.APPLICATION, memberSalesmen.CLAOPC_ID, memberSalesmen.OPC1, memberSalesmen.OPC_PCT, memberSalesmen.OPC_PCT2, memberSalesmen.OPC_CPT3, memberSalesmen.OPC_PCT4, memberSalesmen.STATUS, memberSalesmen.ZONA, memberSalesmen.OPC, user) .FirstOrDefault(); } })); }
/// <summary> /// Guarda los Salesmen en la base de datos de Intelligence Contracts /// </summary> /// <param name="saleNew">Sale que se modifico</param> /// <param name="user">Usuario que realizo los cambios</param> /// <history> /// [jorcanche] created 04/ago/2016 /// </history> public static async Task <List <string> > SaveMemberSalesmenClubes(Sale saleNew, string user, List <MemberSalesmanClubles> lstMemberSalesmenClubes, List <Personnel> lstPersonels) { return(await Task.Run(() => { using (var dbContextIC = new ICEntities(ConnectionHelper.ConnectionString(EnumDatabase.IntelligenceContracts))) { using (var transaction = dbContextIC.Database.BeginTransaction(System.Data.IsolationLevel.Serializable)) { try { string sPRs = string.Empty, sLiners = string.Empty, sClosers = string.Empty, sExits = string.Empty; var mensaje = new List <string>(); //Obtenemos los vendedores de Intelligence Contracts List <MemberSalesmen> lstMemberSalesmens = dbContextIC.USP_CL_GetMemberSalesmen(saleNew.saMembershipNum, "ALL", "ALL", "OPC,LINER,CLOSER,EXIT").ToList(); // Guarda un vendedor por rol en Intelligence Contracts foreach (var mbc in lstMemberSalesmenClubes) { //si tiene una clave de vendedor de origos if (!string.IsNullOrEmpty(mbc.Id)) { //agregamos la clave de vendedor en IM switch (mbc.Role) { case "OPC": sPRs = sPRs + mbc.Id + " "; break; case "LINER": sLiners = sLiners + mbc.Id + " "; break; case "CLOSER": sClosers = sClosers + mbc.Id + " "; break; case "EXIT": sExits = sExits + mbc.Id + " "; break; } //obtenemos la clave del vendedor de Intelligence Contracts relaionando al vendedor de IM Personnel personnel = new Personnel(); //lstPersonels.FirstOrDefault(p => p.peID == mbc.Id); if (personnel == null) { mensaje.Add($"The personnel {mbc.Id} is not associated with a Intelligence Contract's salesman."); break; } //Validamos si el vendedor de origos tiene relacionada una clave de Intelligence Contracts var salemanId = personnel.peSalesmanID; if (string.IsNullOrEmpty(salemanId)) { mensaje.Add($"The personnel {mbc.Id} is not associated with a Intelligence Contract's salesman."); break; } //Validamos si existe la clave de intelligence Contracts var zone = saleNew.sasr; if (!(dbContextIC.USP_CL_ExistsSalesman(zone, salemanId).FirstOrDefault() == 1)) { mensaje.Add($"The salesman {salemanId} from zone {zone} does not exists on Intelligence Contracts"); break; } //Localizamos el vendedor de Intelligence contracts var member = lstMemberSalesmens.FirstOrDefault(sa => sa.OPC == salemanId && sa.CLAOPC_ID == mbc.Job); //si no se localizo if (member == null) { //lo agregamos en Intelligence contracts var memberSalesmen = new MemberSalesmen { CLMEMOPC_ID = 0, APPLICATION = saleNew.saMembershipNum, CLAOPC_ID = mbc.Job, OPC1 = string.Empty, OPC_PCT = 0, OPC_PCT2 = 0, OPC_CPT3 = 0, OPC_PCT4 = 0, STATUS = "A", ZONA = zone, OPC = salemanId }; lstMemberSalesmens.Add(memberSalesmen); //Guarda un vendedor de una afiliacion en Intelligence Contracts dbContextIC.USP_CL_SaveMemberSalesman(0, memberSalesmen.CLMEMOPC_ID, memberSalesmen.APPLICATION, memberSalesmen.CLAOPC_ID, memberSalesmen.OPC1, memberSalesmen.OPC_PCT, memberSalesmen.OPC_PCT2, memberSalesmen.OPC_CPT3, memberSalesmen.OPC_PCT4, memberSalesmen.STATUS, memberSalesmen.ZONA, memberSalesmen.OPC, user); } else { // si tiene el rol solicitado if (member.Role == mbc.Role) { member.CLAOPC_ID = mbc.Job; member.STATUS = "A"; member.ZONA = saleNew.sasr; //Actualizamos el vendedor en Intelligence Contracts dbContextIC.USP_CL_SaveMemberSalesman( member.RECNUM, member.CLMEMOPC_ID, member.APPLICATION, member.CLAOPC_ID, member.OPC1, member.OPC_PCT, member.OPC_PCT2, member.OPC_CPT3, member.OPC_PCT4, member.STATUS, member.ZONA, member.OPC, user); } else //Si no tiene el rol solicitad o { //Agregamos en Intelligence contracts var memberSalesmen = new MemberSalesmen { CLMEMOPC_ID = 0, APPLICATION = saleNew.saMembershipNum, CLAOPC_ID = mbc.Job, OPC1 = string.Empty, OPC_PCT = 0, OPC_PCT2 = 0, OPC_CPT3 = 0, OPC_PCT4 = 0, STATUS = "A", ZONA = zone, OPC = salemanId }; lstMemberSalesmens.Add(memberSalesmen); //Agregamos el vendedor en intelligence Contracts dbContextIC.USP_CL_SaveMemberSalesman(0, memberSalesmen.CLMEMOPC_ID, memberSalesmen.APPLICATION, memberSalesmen.CLAOPC_ID, memberSalesmen.OPC1, memberSalesmen.OPC_PCT, memberSalesmen.OPC_PCT2, memberSalesmen.OPC_CPT3, memberSalesmen.OPC_PCT4, memberSalesmen.STATUS, memberSalesmen.ZONA, memberSalesmen.OPC, user); } } } } //Elimina los vendedores de una afiliacion en Intelligence Contracts si no estan en Intelligence marketing List <MemberSalesmanClubles> lstDeleteSalesmenClubes = new List <MemberSalesmanClubles>(); lstDeleteSalesmenClubes.Add(new MemberSalesmanClubles { Role = "OPC", Salesmen = sPRs }); lstDeleteSalesmenClubes.Add(new MemberSalesmanClubles { Role = "LINER", Salesmen = sLiners }); lstDeleteSalesmenClubes.Add(new MemberSalesmanClubles { Role = "CLOSER", Salesmen = sClosers }); lstDeleteSalesmenClubes.Add(new MemberSalesmanClubles { Role = "EXIT", Salesmen = sExits }); //Eliminamos los vendedores de una afiliacion en Intelligence Contracts si no estan en Intelligence Marketing foreach (var item in lstDeleteSalesmenClubes) { //Obtenemos vendedores actuales de Intelligence Contracts var memberSalesmens = dbContextIC.USP_CL_GetMemberSalesmen(saleNew.saMembershipNum, "ALL", "ALL", item.Role).ToList(); if (memberSalesmens.Any()) { //Si el vendedor no esta en la lista de vendedores de Origos, lo eliminamos en Intellligence Contracts memberSalesmens.ForEach(ms => { if (!item.Salesmen.Contains(ms.OPC)) { dbContextIC.USP_CL_DeleteMemberSalesman(ms.CLMEMOPC_ID); } }); } } dbContextIC.SaveChanges(); transaction.Commit(); return mensaje; } catch (System.Exception) { transaction.Rollback(); throw; } } } })); }