public int InserirLogDownload(OrigemDownloadEnum origemDnl, CategoriaDownloadEnum categoriaDnl, DateTime?dataRef, int idFundo, string cpfcnpj = null) { SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdmFundos"].ConnectionString); sqlConn.Open(); int iOrigDnl = (int)origemDnl; int iCategDnl = (int)categoriaDnl; string cpf = String.Empty; if (!String.IsNullOrEmpty(cpfcnpj)) { cpf = cpfcnpj; } string sqlQuery = "INSERT INTO tbArquivoDownload (idOrigem, idCategoria, idFundo, dtReferencia, stTransacao, numTentativas, cpfCnpj) VALUES "; sqlQuery += " (" + iOrigDnl.ToString() + ", "; sqlQuery += iCategDnl.ToString() + ", "; sqlQuery += idFundo.ToString() + ", "; if (dataRef != null) { sqlQuery += " @dataRef, 'N', 0, "; } else { sqlQuery += " GETDATE(), 'N', 0, "; } sqlQuery += "'" + cpf + "')"; sqlQuery += "SELECT SCOPE_IDENTITY()"; SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn); if (dataRef != null) { sqlCmd.Parameters.AddWithValue("@dataRef", dataRef); } sqlCmd.CommandType = System.Data.CommandType.Text; int ident = (int)(decimal)sqlCmd.ExecuteScalar(); sqlConn.Close(); sqlConn.Dispose(); return(ident); }
/// <summary> /// /// </summary> /// <param name="origemDnl"></param> /// <returns></returns> public List <FundoInfo> ObterFundos(OrigemDownloadEnum origemDnl) { List <FundoInfo> lRetorno = new List <FundoInfo>(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdmFundos"].ConnectionString); sqlConn.Open(); SqlDataAdapter lAdapter; DataTable table = new DataTable(); int iOrigDnl = (int)origemDnl; string sqlQuery = "SELECT * FROM tbFundos WHERE idOrigem=" + iOrigDnl; SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn); sqlCmd.CommandType = System.Data.CommandType.Text; lAdapter = new SqlDataAdapter(sqlCmd); lAdapter.SelectCommand.Connection = sqlConn; lAdapter.Fill(table); if (table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { FundoInfo info = new FundoInfo(); info.CodFundo = table.Rows[i]["codFundo"].ToString(); info.DsFundo = table.Rows[i]["dsFundo"].ToString(); info.IDFundo = Convert.ToInt32(table.Rows[i]["idFundo"].ToString()); info.IDOrigem = Convert.ToInt32(table.Rows[i]["idOrigem"].ToString()); info.GrupoCarteira = table.Rows[i]["grpCarteira"].ToString(); info.IsSegregada = table.Rows[i]["isSegregada"].ToString(); lRetorno.Add(info); } } sqlConn.Close(); sqlConn.Dispose(); return(lRetorno); }
/// <summary> /// /// </summary> /// <param name="origemDnl"></param> /// <returns></returns> public Dictionary <int, LogDownloadInfo> ObterLogDownload(OrigemDownloadEnum origemDnl, CategoriaDownloadEnum categoriaDnl, DateTime dataIni) { Dictionary <int, LogDownloadInfo> lRetorno = new Dictionary <int, LogDownloadInfo>(); SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdmFundos"].ConnectionString); sqlConn.Open(); SqlDataAdapter lAdapter; DataTable table = new DataTable(); int iOrigDnl = (int)origemDnl; int iCategDnl = (int)categoriaDnl; string sqlQuery = "SELECT * FROM tbArquivoDownload WHERE idOrigem=" + iOrigDnl; sqlQuery += " AND idCategoria=" + iCategDnl; sqlQuery += " AND DATEADD(D, 0, DATEDIFF(D, 0, dtReferencia)) = DATEADD(D, 0, DATEDIFF(D, 0, @dataRef)) "; logger.Debug("sqlQuery = [" + sqlQuery + "] dataRef=" + dataIni.ToString("yyyy/MM/dd HH:mm:ss.fff")); //DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn); sqlCmd.Parameters.AddWithValue("@dataRef", dataIni); sqlCmd.CommandType = System.Data.CommandType.Text; lAdapter = new SqlDataAdapter(sqlCmd); lAdapter.SelectCommand.Connection = sqlConn; lAdapter.Fill(table); if (table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { LogDownloadInfo info = new LogDownloadInfo(); info.IDDownloadTransacao = Convert.ToInt32(table.Rows[i]["idDownloadTransacao"].ToString()); info.IDCategoria = Convert.ToInt32(table.Rows[i]["idCategoria"].ToString()); info.IDFundo = Convert.ToInt32(table.Rows[i]["idFundo"].ToString()); info.cpfCnpj = table.Rows[i]["cpfCnpj"].ToString(); info.dtReferencia = table.Rows[i]["dtReferencia"].DBToDateTime(); info.dtUltimaTentativa = table.Rows[i]["dtUltimaTentativa"].DBToDateTime(); info.stTransacao = table.Rows[i]["stTransacao"].DBToString(); info.pathArquivo = table.Rows[i]["pathArquivo"].DBToString(); info.IDOrigem = Convert.ToInt32(table.Rows[i]["idOrigem"].ToString()); info.numTentativas = Convert.ToInt32(table.Rows[i]["numTentativas"].ToString()); lRetorno.Add(info.IDFundo, info); } } sqlConn.Close(); sqlConn.Dispose(); return(lRetorno); }