public void SqlRdmsConnectionTestExecSP_Insert()
{
//String StrCnn = @"Data Source=GBERNAL;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
String StrCnn = @"Data Source=DevSrv2008R2;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
var param = new List <SqlParameter>();
//param.Add(new SqlParameter("@IdBCfg", "SCCM"));
//param.Add(new SqlParameter("@IdPais", 1));
//param.Add(new SqlParameter("@NCfg", "Sobre Cargo Carga Minima"));
//param.Add(new SqlParameter("@Value", 60003171));
//param.Add(new SqlParameter("@IsActive", true));
//param.Add(new SqlParameter("@IdBCfg", "N"));
//param.Add(new SqlParameter("@IdPais", 1));
//param.Add(new SqlParameter("@NCfg", "Cargo Adicional Concreto Tipo N"));
//param.Add(new SqlParameter("@IdTConfig", 1));
//param.Add(new SqlParameter("@Value", 20));
//param.Add(new SqlParameter("@IsActive", true));
param.Add(new SqlParameter("@IdBCfg", "P"));
param.Add(new SqlParameter("@IdPais", 1));
param.Add(new SqlParameter("@NCfg", "Cargo Adicional Concreto Tipo N"));
param.Add(new SqlParameter("@IdTConfig", 1));
param.Add(new SqlParameter("@Value", 15));
param.Add(new SqlParameter("@IsActive", true));
SqlRdmsConnection <EmpDto> cnn = new SqlRdmsConnection <EmpDto>(
new SqlConnection(StrCnn), "bcfg.SPI_BsConfig", param);
var result = cnn.Execute(false, CommandType.StoredProcedure);
Assert.IsTrue(result != null);
}
public void DeleteFile(string Id)
{
var param = new List<SqlParameter>();
param = new List<SqlParameter>();
param.Add(new SqlParameter("@Id", Id));
IRdmsConnection cnn = new SqlRdmsConnection<DocsDTO>(UtilSh.strCnn2, "dbo.DeleteDoc", param);//Procedimiento almacenado para borrar archivo
cnn.Execute(true, CommandType.StoredProcedure);
}
public IEnumerable<SelectableDTO> GetPais()
{
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn2, "dbo.GetPais");
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (SelectableDTO)r).ToList();
return res;
}
public IEnumerable<RegistrohcrDTO> CargarDocs(String Llave)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Llave", Llave));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn, "HCR.CargarDocs", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from o in result
select (RegistrohcrDTO)o).ToList();
return res;
}
public void SqlRdmsConnectionTestExecCmd_Select()
{
//String StrCnn = @"Data Source=GBERNAL;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
String StrCnn = @"Data Source=DevSrv2008R2;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
SqlRdmsConnection <EmpDto> cnn = new SqlRdmsConnection <EmpDto>(new SqlConnection(StrCnn), "select * from hr.Emp");
var result = cnn.Execute(true, CommandType.Text);
Assert.IsTrue(result != null);
}
public IEnumerable<AuthDTO> GetAuth(String CemexID)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@CemexID", CemexID));
IRdmsConnection cnn = new SqlRdmsConnection<AuthDTO>(UtilSh.strCnn, "auth_usr.GetAuth", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (AuthDTO)r).ToList();
return res;
}
public IEnumerable<SelectableDTO> GetNCliente(string CodCliente)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@CodCliente", CodCliente));
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn2, "dbo.GetNCliente",param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (SelectableDTO)r).ToList();
return res;
}
public IEnumerable<PgDTO> GetPages(int PaisApp, int Rol)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Papp", PaisApp));
param.Add(new SqlParameter("@rol",Rol));
IRdmsConnection cnn = new SqlRdmsConnection<PgDTO>(UtilSh.strCnn,"auth_usr.GetPages",param);
var result2 = cnn.Execute(true, CommandType.StoredProcedure);
var res2 = (from r in result2
select (PgDTO)r).ToList();
return res2;
}
public IEnumerable<DocsDTO> GetFiles(string Llave)
{
var param = new List<SqlParameter>();
param = new List<SqlParameter>();
param.Add(new SqlParameter("@Llave", Llave));
IRdmsConnection cnn = new SqlRdmsConnection<DocsDTO>(UtilSh.strCnn2, "dbo.GetFiles", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (DocsDTO)r).ToList();
return res;
}
public IEnumerable <ValDTO> ConsultarCondiciones(long Obra)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IDOBRA", Obra.ToString()));
IRdmsConnection cnn = new SqlRdmsConnection <ValDTO>(UtilSh.strCnn2, "MASTERS.GetSegmentacionCli", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(ValDTO) r).ToList();
return(res);
}
public void SqlRdmsConnectionTestExecCmd_Insert()
{
//String StrCnn = @"Data Source=GBERNAL;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
String StrCnn = @"Data Source=DevSrv2008R2;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
SqlRdmsConnection <EmpDto> cnn = new SqlRdmsConnection <EmpDto>(
new SqlConnection(StrCnn), "INSERT INTO bcfg.tbConfig (IdCfg, IdPais, NCfg, IdTCfg, Value, DFin)" +
"VALUES ('Z', 1, 'Cargo Z', 'N', 8, '2013-07-30')");
var result = cnn.Execute(false, CommandType.Text);
Assert.IsTrue(result != null);
}
public void SaveMats(int Num, string Mat, double Vol, string Deno)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Num", Num));
param.Add(new SqlParameter("@Mat", Mat));
param.Add(new SqlParameter("@Vol", Vol));
param.Add(new SqlParameter("@Deno", Deno));
IRdmsConnection cnn = new SqlRdmsConnection <ProfMatDTO>(UtilSh.strCnn, "Proforma.SetMatProform", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public string GetObraPriceList(string IdCliente, int IdSector)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdCliente", IdCliente));
param.Add(new SqlParameter("@IdSector", IdSector));
IRdmsConnection cnn = new SqlRdmsConnection <MasterMatDTO>(UtilSh.strCnn2, "MASTERS.GetVolObra", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(string) r).ToList();
return(res.First());
}
public String GetZTERM(long Obra, string Sector)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdObra", Obra.ToString()));
param.Add(new SqlParameter("@Sector", Sector));
IRdmsConnection cnn = new SqlRdmsConnection <ZtermDTO>(UtilSh.strCnn2, "MASTERS.GetZtermCli", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(ZtermDTO) r).ToList();
return(res.First().CondPago);
}
public void SqlRdmsConnectionTestExecCmd_Update()
{
//String StrCnn = @"Data Source=GBERNAL;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
String StrCnn = @"Data Source=DevSrv2008R2;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
SqlRdmsConnection <EmpDto> cnn = new SqlRdmsConnection <EmpDto>
(new SqlConnection(StrCnn), "UPDATE bcfg.tbConfig " +
"SET NCfg = 'Cargo P Mod', IdTCfg = 'Z', Value = '100', DFin = '2013-08-01'" +
"WHERE IdCfg = 'P' AND IdPais = 1");
var result = cnn.Execute(true, CommandType.Text);
Assert.IsTrue(result != null);
}
public string GetCentro(long Obra, string Sector)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdObra", Obra.ToString()));
param.Add(new SqlParameter("@Sector", int.Parse(Sector) < 10?"0" + Sector :Sector));
IRdmsConnection cnn = new SqlRdmsConnection <CentroDTO>(UtilSh.strCnn2, "MASTERS.GetCentroCli", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(CentroDTO) r).ToList();
return(res.First().Centro);
}
public void SaveSolici(int Num, string Name, string Cargo, string Tel, string Mail)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Num", Num));
param.Add(new SqlParameter("@Name", Name));
param.Add(new SqlParameter("@Cargo", Cargo));
param.Add(new SqlParameter("@Tel", Tel));
param.Add(new SqlParameter("@Mail", Mail));
IRdmsConnection cnn = new SqlRdmsConnection <ProfSolDTO>(UtilSh.strCnn, "Proforma.SetSoliProform", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public VolMaxDto GetVolMax(String idObra, Int32 idOrg, Int32 idSector)
{
String strCnn = System.Configuration.ConfigurationManager.ConnectionStrings["cnnSAPDB"].ToString();
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdObra", idObra));
param.Add(new SqlParameter("@IdOrg", idOrg));
param.Add(new SqlParameter("@IdSector", idSector));
param.Add(new SqlParameter("@Type", "VOLMAX"));
IRdmsConnection <VolMaxDto> cnn = new SqlRdmsConnection <VolMaxDto>(new SqlConnection(strCnn), "rep.SPS_ObrSC", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
return(result.Count() > 0 ? (VolMaxDto)result.First() : null);
}
public BConfigDto GetBConfig(String idBCfg, Int32 idPais, String idTConfig, bool isActive)
{
String strCnn = System.Configuration.ConfigurationManager.ConnectionStrings["cnnCOS"].ToString();
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdTConfig", idTConfig));
param.Add(new SqlParameter("@IdBCfg", idBCfg));
param.Add(new SqlParameter("@IdPais", idPais));
param.Add(new SqlParameter("@IsActive", isActive));
IRdmsConnection <BConfigDto> cnn = new SqlRdmsConnection <BConfigDto>(new SqlConnection(strCnn), "bcfg.SPS_BsConfig", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
return(result.Count() > 0 ? (BConfigDto)result.First() : null);
}
public String ValSegmentacion(String SegCli, String SegReg, Boolean planta)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IDOBRA", "14251"));
param.Add(new SqlParameter("@SEGCLI", SegCli));
param.Add(new SqlParameter("@SEGREG", SegReg));
param.Add(new SqlParameter("@PLANTA", planta));
param.Add(new SqlParameter("@TCOM", 3));
IRdmsConnection cnn = new SqlRdmsConnection <object>(UtilSh.strCnn2, "[MASTERS].[GetSegRegional_Cliente]", param);
var result = cnn.Execute(true);
return(((object[])result.ToList().First()).First().ToString());
}
public void SqlRdmsConnectionTestExecSP_Select()
{
String StrCnn = @"Data Source=GBERNAL;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
//String StrCnn = @"Data Source=DevSrv2008R2;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdTConfig", "SCBME"));
//param.Add(new SqlParameter("@IdBCfg", "N"));
param.Add(new SqlParameter("@IdPais", 1));
param.Add(new SqlParameter("@IsActive", true));
IRdmsConnection <BConfigDto> cnn = new SqlRdmsConnection <BConfigDto>(new SqlConnection(StrCnn), "bcfg.SPS_BsConfig", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
Assert.IsTrue(result != null);
}
public List <MatbytypeDTO> GetMaterialesPorTipo(string Tipo)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Tipo", Tipo));
IRdmsConnection cnn = new SqlRdmsConnection <MatbytypeDTO>(UtilSh.strCnn, "dbo.GetMatsByType", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
List <MatbytypeDTO> res = new List <MatbytypeDTO> ();
foreach (MatbytypeDTO r in result)
{
res.Add(r);
}
return(res);
}
public void SavePLog(string CemexId, int CodCliente, string NCliente, int CodObra, string Segmentacion, string Tipo, string Logistica, Decimal MaxDto)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@CemexId", CemexId));
param.Add(new SqlParameter("@CodCliente", CodCliente));
param.Add(new SqlParameter("@NCliente", NCliente));
param.Add(new SqlParameter("@CodObra", CodObra));
param.Add(new SqlParameter("@Segmentacion", Segmentacion));
param.Add(new SqlParameter("@Tipo", Tipo));
param.Add(new SqlParameter("@Logistica", Logistica));
param.Add(new SqlParameter("@MaxDto", MaxDto));
IRdmsConnection cnn = new SqlRdmsConnection <LogDTO>(UtilSh.strCnn, "dbo.DescuentosLog", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public IEnumerable <CtgDTO> GetCtg(Int32 idCtg, Int32 idPais, Int32 idSupBCtg = 0, int isActive = -99)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdSupBCtg", idSupBCtg));
param.Add(new SqlParameter("@IdCtg", idCtg));
param.Add(new SqlParameter("@IdPais", idPais));
param.Add(new SqlParameter("@IsActive", isActive));
IRdmsConnection cnn = new SqlRdmsConnection <CtgDTO>(UtilSh.strCnn, "ctg.SPS_TbCtg", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
//return (result.Count() > 0 ? (CtgDTO)result.ToList() : null);
var res = (from r in result
select(CtgDTO) r).ToList();
return(res.Count() > 0 ? res : null);
}
public int Save(string Tipo, string Obra, string Cliente, string Comercial, string Sector, long Total)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Tipo", Tipo));
param.Add(new SqlParameter("@Obra", Obra));
param.Add(new SqlParameter("@Cliente", Cliente));
param.Add(new SqlParameter("@Comercial", Comercial));
param.Add(new SqlParameter("@Sector", Sector));
param.Add(new SqlParameter("@Total", Total));
IRdmsConnection cnn = new SqlRdmsConnection <ProformDTO>(UtilSh.strCnn, "Proforma.SetProform", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(ProformDTO) r).ToList();
return(res.First().Id);
}
public void SqlRdmsConnectionTestExecSP_Update()
{
//String StrCnn = @"Data Source=GBERNAL;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
String StrCnn = @"Data Source=DevSrv2008R2;Initial Catalog=SCDBDev;Persist Security Info=True;User ID=scappuser;Password=scappuser$;WSID=IP(WS_Name)\WS_Usr\App_Usr;App=SINCO";
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdCfg", "N"));
param.Add(new SqlParameter("@IdPais", 1));
param.Add(new SqlParameter("@NCfg", "Cargo Adicional Modificado"));
param.Add(new SqlParameter("@Value", "15"));
param.Add(new SqlParameter("@DFin", DateTime.Today.AddDays(2)));
SqlRdmsConnection <EmpDto> cnn = new SqlRdmsConnection <EmpDto>(
new SqlConnection(StrCnn), "bcfg.SPU_BsConfig", param);
var result = cnn.Execute(false, CommandType.StoredProcedure);
Assert.IsTrue(result != null);
}
public MasterDTO GetMaestrosByObra(long Obra)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Obra", Obra));
IRdmsConnection cnn = new SqlRdmsConnection <MasterDTO>(UtilSh.strCnn2, "MASTERS.GetMaestrosByObra", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(MasterDTO) r).ToList();
if (res.Count > 0)//johanProforma validacion por si no encuentra la obra
{
return(res.First());
}
else
{
return(null);
}
}
//public IEnumerable<ValDTO> ValObra(long IdObra)
//{
// var param = new List<SqlParameter>();
// param.Add(new SqlParameter("@IdObra", long.Parse(IdObra.ToString())));
// IRdmsConnection cnn = new SqlRdmsConnection<ValDTO>(UtilSh.strCnn2, "MASTERS.GetSegmentacionCli", param);
// var result = cnn.Execute(true, CommandType.StoredProcedure);
// var res = (from r in result
// select (ValDTO)r).ToList();
// return res;
//}
public IEnumerable <ValDTO> ValObra(long IdObra, Boolean planta)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@IdObra", IdObra.ToString()));
if (planta)
{
param.Add(new SqlParameter("@planta", true));
}
else
{
param.Add(new SqlParameter("@planta", false));
}
IRdmsConnection cnn = new SqlRdmsConnection <ValDTO>(UtilSh.strCnn2, "MASTERS.GetSegmentacionCli", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(ValDTO) r).ToList();
return(res);
}
public MasterMatDTO GetMatUM(string Familia, string Norma, string Res, string TamGra, string TipGra, string Edad, string Asen, string Bomb, string TipCem, string Var)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@Familia", Familia));
param.Add(new SqlParameter("@Norma", Norma));
param.Add(new SqlParameter("@Res", Res));
param.Add(new SqlParameter("@TamGra", TamGra));
param.Add(new SqlParameter("@TipGra", TipGra));
param.Add(new SqlParameter("@Edad", Edad));
param.Add(new SqlParameter("@Asen", Asen));
param.Add(new SqlParameter("@Bomb", Bomb));
param.Add(new SqlParameter("@TipCem", TipCem));
param.Add(new SqlParameter("@Var", Var));
IRdmsConnection cnn = new SqlRdmsConnection <MasterMatDTO>(UtilSh.strCnn2, "MASTERS.GetMatUM", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select(MasterMatDTO) r).ToList();
return(res.First());
}
public IEnumerable <proformaDatos> GetDtosPrfm(int Tcom, String codCliente, String codObra, String codComercial, String cemexID, String valNeto, String fecha, String nomProdAdd, String ivaProdAdd)//johanProforma
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@TCOM", Tcom));
param.Add(new SqlParameter("@codCliente", codCliente));
param.Add(new SqlParameter("@codObra", codObra));
param.Add(new SqlParameter("@codComercial", codComercial));
param.Add(new SqlParameter("@cemexID", cemexID));
param.Add(new SqlParameter("@valNeto", valNeto));
param.Add(new SqlParameter("@fecha", fecha));
param.Add(new SqlParameter("@nomProdAdd", nomProdAdd));
param.Add(new SqlParameter("@ivaProdAdd", ivaProdAdd));
IRdmsConnection cnn = new SqlRdmsConnection <proformaDatos>(UtilSh.strCnn, "dbo.SP_Prfm_GetSetDatos", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
//return (result.Count() > 0 ? (CtgDTO)result.ToList() : null);
var res = (from r in result
select(proformaDatos) r).ToList();
return(res.Count() > 0 ? res : null);
}
public void SaveProgLog(string CemexId, string SAPID, string CodObra, string Mat, string Vol, string Centro, string DateOrd, bool Ajuste, string Ad1, string Ad2, string Ad3, string Bomba, string Saldo, string Costo)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@CemexId", CemexId));
param.Add(new SqlParameter("@SAPID", SAPID));
param.Add(new SqlParameter("@CodObra", CodObra));
param.Add(new SqlParameter("@Mat", Mat));
param.Add(new SqlParameter("@Vol", Vol));
param.Add(new SqlParameter("@Centro", Centro));
param.Add(new SqlParameter("@DateOrd", DateOrd));
param.Add(new SqlParameter("@Ajuste", Ajuste));
param.Add(new SqlParameter("@Ad1", Ad1));
param.Add(new SqlParameter("@Ad2", Ad2));
param.Add(new SqlParameter("@Ad3", Ad3));
param.Add(new SqlParameter("@Bomba", Bomba));
param.Add(new SqlParameter("@Saldo", Saldo));
param.Add(new SqlParameter("@Costo", Costo));
IRdmsConnection cnn = new SqlRdmsConnection <LogDTO>(UtilSh.strCnn, "dbo.ProgLog", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public void SaveCLog(string CemexId, Int64 Obra, int Sector, string NombreObra, Int64 RazonSocial, string NombreSocial, string CondObra, string Comercial, Int64 Cupo, Int64 EstadoCta, Int64 Cartera, Int64 CompSap, Int64 SaldoTotal, string Msj)
{
var param = new List <SqlParameter>();
param.Add(new SqlParameter("@CemexId", CemexId));
param.Add(new SqlParameter("@CodObra", Obra));
param.Add(new SqlParameter("@Sector", Sector));
param.Add(new SqlParameter("@NombreObra", NombreObra));
param.Add(new SqlParameter("@RazonSocial", RazonSocial));
param.Add(new SqlParameter("@NombreSocial", NombreSocial));
param.Add(new SqlParameter("@CondObra", CondObra));
param.Add(new SqlParameter("@Comercial", Comercial));
param.Add(new SqlParameter("@Cupo", Cupo));
param.Add(new SqlParameter("@EstadoCta", EstadoCta));
param.Add(new SqlParameter("@Cartera", Cartera));
param.Add(new SqlParameter("@CompSAP", CompSap));
param.Add(new SqlParameter("@SaldoTotal", SaldoTotal));
param.Add(new SqlParameter("@Msj", Msj));
IRdmsConnection cnn = new SqlRdmsConnection <LogDTO>(UtilSh.strCnn, "dbo.ConsultasLog", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public int UploadArchivo(long CodCliente,string Tipo,string Nombre, string Id)
{
var param = new List<SqlParameter>();
param = new List<SqlParameter>();
param.Add(new SqlParameter("@CodCliente", CodCliente));
param.Add(new SqlParameter("@Tipo", Tipo));
param.Add(new SqlParameter("@Nombre", Nombre));
param.Add(new SqlParameter("@Id", Id));
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn2, "dbo.UpdateFiles", param);//Procedimiento almacenado para subir archivo
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (SelectableDTO)r).ToList();
return int.Parse(res.First().Id);
}
public void UpdateUsers(string CemexID, string NUsuario, string Email, string Rol)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@CemexID", CemexID));
param.Add(new SqlParameter("@NUsuario", NUsuario));
param.Add(new SqlParameter("@Email", Email));
param.Add(new SqlParameter("@Rol", Rol));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn, "dbo.UpdateUsers", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public void UpdateDestiny(int Id, string Admin, string Vice)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Id", Id));
param.Add(new SqlParameter("@Admin", Admin));
param.Add(new SqlParameter("@Vice", Vice));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn2, "dbo.UpdateDestiny", param);//Procedimiento almacenado para inserción de Paises.
cnn.Execute(true, CommandType.StoredProcedure);
}
public void UpdateCupos(string Llave, string CodCliente, string NCliente, string Comercial, string Pais, string Cupo, String CupoOtorgado, string Plazo, String PlazoOtorgado, String Estado, String Vice, String ConceptoCredito, String ComentarioCredito, String NotasImportantes, String FechaDocCompletos, String FechaGarantia, String FechaConceptoCredito, String FechaSeguro, String FechaJefeCartera, String FechaGerente, String FechaDirector, String FechaVicepresidente, String FechaComite, String FechaPresidentePais, String FechaPresidenteRegion, String FechaNegado, String FechaTramitado, String FechaSuspendido, String FechaProximaRevision,String FechaRad, string Asegurado, string EstadoSeg, string CupoActual, string PlazoActual,string Tipo,string CodClienteA)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Llave", Llave));
param.Add(new SqlParameter("@CodCliente", CodCliente));
param.Add(new SqlParameter("@NCliente", NCliente));
param.Add(new SqlParameter("@Comercial", Comercial));
param.Add(new SqlParameter("@Pais", Pais));
param.Add(new SqlParameter("@Cupo", Cupo));
param.Add(new SqlParameter("@CupoOtorgado", CupoOtorgado));
param.Add(new SqlParameter("@Plazo", Plazo));
param.Add(new SqlParameter("@PlazoOtorgado", PlazoOtorgado));
param.Add(new SqlParameter("@Estado", Estado));
param.Add(new SqlParameter("@Vice", Vice));
param.Add(new SqlParameter("@ConceptoCredito", ConceptoCredito));
param.Add(new SqlParameter("@ComentarioCredito", ComentarioCredito));
param.Add(new SqlParameter("@NotasImportantes", NotasImportantes));
param.Add(new SqlParameter("@FechaDocCompletos", FechaDocCompletos));
param.Add(new SqlParameter("@FechaGarantia", FechaGarantia));
param.Add(new SqlParameter("@FechaConceptoCredito", FechaConceptoCredito));
param.Add(new SqlParameter("@FechaSeguro", FechaSeguro));
param.Add(new SqlParameter("@FechaJefeCartera", FechaJefeCartera));
param.Add(new SqlParameter("@FechaGerente", FechaGerente));
param.Add(new SqlParameter("@FechaDirector", FechaDirector));
param.Add(new SqlParameter("@FechaVicepresidente", FechaVicepresidente));
param.Add(new SqlParameter("@FechaComite", FechaComite));
param.Add(new SqlParameter("@FechaPresidentePais", FechaPresidentePais));
param.Add(new SqlParameter("@FechaPresidenteRegion", FechaPresidenteRegion));
param.Add(new SqlParameter("@FechaNegado", FechaNegado));
param.Add(new SqlParameter("@FechaTramitado", FechaTramitado));
param.Add(new SqlParameter("@FechaSuspendido", FechaSuspendido));
param.Add(new SqlParameter("@FechaProximaRevision", FechaProximaRevision));
param.Add(new SqlParameter("@FechaRad", FechaRad));
param.Add(new SqlParameter("@Asegurado", Asegurado));
param.Add(new SqlParameter("@EstadoSeg", EstadoSeg));
param.Add(new SqlParameter("@CupoActual", CupoActual));
param.Add(new SqlParameter("@PlazoActual", PlazoActual));
param.Add(new SqlParameter("@Tipo", Tipo));
param.Add(new SqlParameter("@CodClienteA", CodClienteA));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn2, "dbo.UpdateCupo", param);
cnn.Execute(true, CommandType.StoredProcedure);
}
public Regs RegsGrid(String CemexId)
{
Regs userse = new Regs();
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@CemexId", CemexId));
IRdmsConnection cnn = new SqlRdmsConnection<UserIdDTO>(UtilSh.strCnn2, "dbo.GetPermission", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (UserIdDTO)r).ToArray();
param.Clear();
string connectionString = UtilSh.strCnn2;/* System.Configuration.ConfigurationManager.ConnectionStrings["cnnSql2"].ToString();*/
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = "Exec dbo.RegsGrid @User="******"ID"]);
user.Llave = Convert.ToString(dataReader["Llave"]);
user.CodCliente = Convert.ToString(dataReader["CodCliente"]);
user.NCliente = Convert.ToString(dataReader["NCliente"]);
user.Pais = Convert.ToString(dataReader["Pais"]);
user.Cupo = Convert.ToString(dataReader["Cupo"]);
user.CupoOtorgado = Convert.ToString(dataReader["CupoOtorgado"]);
user.Plazo = Convert.ToString(dataReader["Plazo"]);
user.PlazoOtorgado = Convert.ToString(dataReader["PlazoOtorgado"]);
user.Tipo = Convert.ToString(dataReader["Tipo"]);
user.Estado = Convert.ToString(dataReader["Estado"]);
user.Vice = Convert.ToString(dataReader["Vice"]);
user.SolCre = Convert.ToString(dataReader["SolCre"]);
user.Pagare = Convert.ToString(dataReader["Pagare"]);
user.Cedula = Convert.ToString(dataReader["Cedula"]);
user.Camara = Convert.ToString(dataReader["Camara"]);
user.Rut = Convert.ToString(dataReader["Rut"]);
user.Comentarios = Convert.ToString(dataReader["Comentarios"]);
user.Fecha = Convert.ToString(dataReader["Fecha"]);
user.Hora = Convert.ToString(dataReader["Hora"]);
user.ComentarioCredito = Convert.ToString(dataReader["ComentarioCredito"]);
user.Comercial = Convert.ToString(dataReader["Comercial"]);
userse.Add(user);
}
}
//Convert.ToInt32(paramTotalRecords.Value);
}
}
/* param = new List<SqlParameter>();
param.Add(new SqlParameter("@User", CemexId));
cnn = new SqlRdmsConnection<RegDTO>(UtilSh.strCnn2, "dbo.RegsGrid",param);//Procedimiento almacenado para inserción de solicitudes.
var result2 = cnn.Execute(true, CommandType.StoredProcedure);
var res2 = (from r in result2
select (RegDTO)r).ToArray();
foreach(var x in res2)
{
userse.Add(x);
}*/
return userse;
}
public void InsertUsers(string CemexID, string NUsuario, string Email, string Rol)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@CemexID", CemexID));
param.Add(new SqlParameter("@NUsuario", NUsuario));
param.Add(new SqlParameter("@Email", Email));
param.Add(new SqlParameter("@Rol", Rol));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn, "dbo.InsertUser", param);//Procedimiento almacenado para inserción de Usuarios. (HCR_AuthDB)
cnn.Execute(true, CommandType.StoredProcedure);
}
public int InsertReg(string Comercial,string Name, string Pais, string Cliente, string NCliente, string Cupo, string Plazo, string Tipo, string Vice)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Usuario", Name));
param.Add(new SqlParameter("@Pais", Pais));
param.Add(new SqlParameter("@Comercial", Comercial));
param.Add(new SqlParameter("@CodCliente", Cliente));
param.Add(new SqlParameter("@NCliente", NCliente));
param.Add(new SqlParameter("@Cupo", Cupo));
param.Add(new SqlParameter("@Plazo", Plazo));
param.Add(new SqlParameter("@Tipo", Tipo));
param.Add(new SqlParameter("@Giro", Vice));
param.Add(new SqlParameter("@Fecha", DateTime.Now.ToString("yyyy-mm-dd").ToString()));
param.Add(new SqlParameter("@Hora", DateTime.Now.ToString("hh:mm:ss").ToString()));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn2, "dbo.InsertSol", param);//Procedimiento almacenado para inserción de solicitudes.
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (RegistrohcrDTO)r).ToList();
return res.ElementAt(0).Llave;
}
public void InsertPaises(string NPais)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@NPais", NPais));
IRdmsConnection cnn = new SqlRdmsConnection<RegistrohcrDTO>(UtilSh.strCnn2, "dbo.InsertPais", param);//Procedimiento almacenado para inserción de Paises.
cnn.Execute(true, CommandType.StoredProcedure);
}
public IEnumerable<SelectableDTO> GetRecipents(string Vice)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Vice", Vice));
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn2, "dbo.GetRecipents", param);//Procedimiento almacenado para inserción de solicitudes.
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from r in result
select (SelectableDTO)r).ToList();
return res;
}
public string GetName(string User)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@User", User));
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn, "dbo.GetName", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from o in result
select (SelectableDTO)o).ToList();
return res.First().Name;
}
public int GetMaxID()
{
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn2, "dbo.GetMaxID");
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from o in result
select (SelectableDTO)o).ToList();
return int.Parse(res.First().Id);
}
public int GetLlave(string Id)
{
var param = new List<SqlParameter>();
param.Add(new SqlParameter("@Id", Id));
IRdmsConnection cnn = new SqlRdmsConnection<SelectableDTO>(UtilSh.strCnn2, "dbo.GetLlave", param);
var result = cnn.Execute(true, CommandType.StoredProcedure);
var res = (from o in result
select (SelectableDTO)o).ToList();
return int.Parse(res.First().Id);
}
private void BasePage_PreInit(object sender, EventArgs e)
{
try
{
if (Session["Usr"] != null)
{
var objCache = (IObjectCache)HttpContext.Current.Cache.Get(((Usr)Session["Usr"]).usr.CemexID);
/*using (var mp = new AuthDBEntities())//Convertir en Stored Procedure
{
foreach(var mpid in mp.relPappRolPg) {
if(objCache.Auth.ElementAt(0).RolId==mpid.RolId && objCache.Auth.ElementAt(0).PaisAppId==mpid.PaisAppId)
{foreach(var mpurl in mp.Entities){
if (mpid.MPId == mpurl.EntityId)
{
MasterPageFile ="http://*****:*****@CemexId", ((Usr)Session["Usr"]).usr.CemexID));
IRdmsConnection cnn = new SqlRdmsConnection<PappDTO>(UtilSh.strCnn, "auth_usr.Papp", param);
var result2 = cnn.Execute(true, CommandType.StoredProcedure);
var res2 = (from r in result2
select (PappDTO)r).ToList();
Dictionary<string, int> aux = new Dictionary<string, int>(2);
aux.Add(res2.ElementAt(0).Description.ToString(), Convert.ToInt32(res2.ElementAt(0).Value));
ListaPa = aux;
}
var lpa = (from l in ListaPa.ToList()
select l).ToList();
((DropDownList)Master.FindControl("ddlPaisCtx")).DataSource = lpa;
((DropDownList)Master.FindControl("ddlPaisCtx")).DataTextField = "Key";
((DropDownList)Master.FindControl("ddlPaisCtx")).DataValueField = "Value";
((DropDownList)Master.FindControl("ddlPaisCtx")).DataBind();
}
else
{
Response.Redirect("~/Account/CerrarSession.aspx");
}
}
catch (Exception)
{
Response.Redirect("~/Account/CerrarSession.aspx");
}
}