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