Exemplo n.º 1
0
        /// <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());
            }
        }
Exemplo n.º 2
0
 /// <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);
         }
     }));
 }
Exemplo n.º 3
0
 /// <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();
         }
     }));
 }
Exemplo n.º 4
0
 /// <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);
         }
     }));
 }
Exemplo n.º 5
0
 /// <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;
         }
     }));
 }
Exemplo n.º 6
0
 /// <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();
         }
     }));
 }
Exemplo n.º 7
0
        /// <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;
                        }
                    }
                }
            }));
        }