public static List <Empresas> GetEmails() { Empresas listaemailcliente = new Empresas(); var listacliente = new List <Empresas>(); try { SqlDataReader _dtReader = null; var _rCmd = @"exec PRC_RETORNA_CLIENTES_EMAIL"; _dtReader = ExecSql.execsqlDr(_rCmd); while (_dtReader.Read()) { string emailcliente = _dtReader["emails"].ToString(); string[] emails = emailcliente.Split(';'); foreach (var eml in emails) { listaemailcliente.emails.Add(eml); } listaemailcliente.CNPJ = _dtReader["ukey"].ToString(); listaemailcliente.relType = "6"; listacliente.Add(new Empresas() { CNPJ = _dtReader["ukey"].ToString(), relType = "6", emails = listaemailcliente.emails }); } _dtReader.Close(); return(listacliente); } catch (Exception ex) { throw ex; } finally { } }
public static string GetEmailByConhecimentoUkey(string Ukey) { var _rCmd = @" SELECT A03_043_C FROM CONHECIMENTO_DAYTONA INNER JOIN A03 ON CONHECIMENTO_DAYTONA.A03_UKEY_REMET = A03.UKEY WHERE CONHECIMENTO_DAYTONA.UKEY ='@UKEY'" ; _rCmd = _rCmd.Replace("@UKEY", Ukey); var _dtReader = ExecSql.execsqlDr(_rCmd); var email = ""; while (_dtReader.Read()) { email = _dtReader.GetString(_dtReader.GetOrdinal("A03_043_C")); } _dtReader.Close(); return(email.Trim()); }
/*Porque voce está ? vc tem uma demanda solicitada pelo Saulo, Cassia ou Deus? * se nao tem , nao mude nada nessa classe, pois podera cair sobre você uma terrivel maldição * se vc leu isso, vc está tendo uma chance de nao sofrer isso * * Porém se vc teve a solicitação das pessoas acima Leia descrição do que essa classe faz * * 1ª no metodo NeedSendToday recebe o parametro -1 que é a quantidade de Dias sem enviar o email, e vc ja percebeu aqui que * essa classe manda email, nao so manda como envia com anexo, esse anexo é um xls, XLS NAO UM CSV. * com as ocorrencias do Dia para empresas do grupo ELG , ele manda para 3 empresas. * * */ public void Processar(Empresas empresa) { WriteToAnEventLog log = new WriteToAnEventLog(); if (NeedSendToday.isNow(-1, empresa.relType, 21)) { var Ocorrencias = new List <OcorrenciaConhecimento>(); try { Ocorrencias = GenerateCSV(empresa.CNPJ); if (Ocorrencias != null && Ocorrencias.Count() != 0) { var EmailToSend = NeedSendToday.GetEmailByConhecimentoUkey(Ocorrencias.FirstOrDefault().UkeyConhecimento); var body = @" <strong>Atenção:</strong> Este é um envio de e-mail automático via sistema e não deve ser respondido. Qualquer dúvida, entre em contato diretamente com a Daytona Express "; using (var smtpClient = new SmtpClient("mail.daytonaexpress.com.br")) { smtpClient.UseDefaultCredentials = false; smtpClient.Host = "mail.daytonaexpress.com.br"; smtpClient.Port = 587; smtpClient.EnableSsl = false; smtpClient.Credentials = new NetworkCredential("*****@*****.**", "dayt@011"); var emailMensage = new MailMessage { IsBodyHtml = true, From = new MailAddress("*****@*****.**"), Body = body, Subject = "Informativo de Ocorrência", }; emailMensage.To.Add(new MailAddress("*****@*****.**")); emailMensage.To.Add(new MailAddress("*****@*****.**")); emailMensage.To.Add(new MailAddress("*****@*****.**")); foreach (var email in empresa.emails) { emailMensage.To.Add(new MailAddress(email)); } Attachment attachment = new Attachment(attachmentFilename, MediaTypeNames.Application.Octet); ContentDisposition disposition = attachment.ContentDisposition; disposition.CreationDate = File.GetCreationTime(attachmentFilename); disposition.ModificationDate = File.GetLastWriteTime(attachmentFilename); disposition.ReadDate = File.GetLastAccessTime(attachmentFilename); disposition.FileName = Path.GetFileName(attachmentFilename); disposition.Size = new FileInfo(attachmentFilename).Length; disposition.DispositionType = DispositionTypeNames.Attachment; emailMensage.Attachments.Add(attachment); smtpClient.Send(emailMensage); } foreach (var OcorrenciaConhecimento in Ocorrencias.Where(x => x.LogRegister == 1).ToList()) { var _cmd = "INSERT INTO OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA VALUES(@SEQ,'@UKEYCONHECIMENTO',@CODIGO,@STATUS,CONVERT(DATETIME,'@DATA',120),'@MENSAGEM','@TYPEREL')"; _cmd = _cmd.Replace("@SEQ", OcorrenciaConhecimento.Seq.ToString()); _cmd = _cmd.Replace("@UKEYCONHECIMENTO", OcorrenciaConhecimento.UkeyConhecimento); _cmd = _cmd.Replace("@CODIGO", OcorrenciaConhecimento.CodigoOcorrencia.ToString()); _cmd = _cmd.Replace("@STATUS", "1"); _cmd = _cmd.Replace("@TYPEREL", empresa.relType); _cmd = _cmd.Replace("@DATA", String.Format("{0:yyyy-MM-dd HH:mm:ss}", DateTime.Now)); _cmd = _cmd.Replace("@MENSAGEM", "Enviado Ok"); ExecSql.execsqlDr(_cmd); } } } catch (Exception e) { log.RegistraLog("Erro Processa grupo ELG" + e.StackTrace, 3); } } }
public static List <OcorrenciaConhecimento> GenerateCSV(string UKEY) { WriteToAnEventLog log = new WriteToAnEventLog(); var OcorrenciasToRegisterLog = new List <OcorrenciaConhecimento>(); try { //BITTENCOURT AUDIO E VIDEO -CENTRAL SUPORTES var _rCmd = @"exec SP_RETORNA_DADOS_RELATORIO_OCORRENCIA '" + UKEY + "'"; SqlDataReader _dtReader = ExecSql.execsqlDr(_rCmd); var CNPJ = ""; // Data / Hawb / NF / Valor / Destinatário / Link Rastreio Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("Confirmação de envio"); worksheet.Cells[0, 0] = new Cell("DATA COLETA"); worksheet.Cells[0, 1] = new Cell("NR HAWB"); worksheet.Cells[0, 2] = new Cell("NR NF"); worksheet.Cells[0, 3] = new Cell("VALOR"); worksheet.Cells[0, 4] = new Cell("DESTINATARIO"); worksheet.Cells[0, 5] = new Cell("LINK_RASTREIO"); int i = 1; while (_dtReader.Read()) { try { CNPJ = _dtReader.GetString(_dtReader.GetOrdinal("A03_010_C")).Trim(); } catch { } var NR_NF = ""; var NR_HAWB = ""; var A03_003_C_DES = ""; DateTime DT_COLETA; var LINK = ""; double VALOR = 0.00; try { NR_HAWB = _dtReader.GetString(_dtReader.GetOrdinal("nr_hawb")).Trim(); } catch { } try { NR_NF = _dtReader.GetString(_dtReader.GetOrdinal("NR_NF")).Trim(); } catch { } try { LINK = _dtReader.GetString(_dtReader.GetOrdinal("LINK")).Trim(); } catch { } try { VALOR = (double)_dtReader.GetDecimal(_dtReader.GetOrdinal("vl_total")); } catch { } try { A03_003_C_DES = _dtReader.GetString(_dtReader.GetOrdinal("A03_003_C_DES")).Trim(); } catch { } DT_COLETA = _dtReader.GetDateTime(_dtReader.GetOrdinal("DT_COLETA")); // Data / Hawb / NF / Valor / Destinatário / Link Rastreio worksheet.Cells[i, 0] = new Cell(DT_COLETA, @"DD/MM/YYYY"); worksheet.Cells[i, 1] = new Cell(NR_HAWB); worksheet.Cells[i, 2] = new Cell(NR_NF); worksheet.Cells[i, 3] = new Cell(VALOR); worksheet.Cells[i, 4] = new Cell(A03_003_C_DES); worksheet.Cells[i, 5] = new Cell(LINK); OcorrenciasToRegisterLog.Add(new OcorrenciaConhecimento { Seq = 1, CodigoOcorrencia = 75, SatusEnviado = 1, UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")).Trim(), LogRegister = _dtReader.GetInt32(_dtReader.GetOrdinal("LogRegister")) }); i++; } _dtReader.Close(); //Resolve problema: O Excel encontrou conteúdo ilegível / Invalid or corrupt file (unreadable content) for (int j = i; j < 100; j++) { worksheet.Cells[j, 0] = new Cell(""); worksheet.Cells[j, 1] = new Cell(""); worksheet.Cells[j, 2] = new Cell(""); worksheet.Cells[j, 3] = new Cell(""); worksheet.Cells[j, 4] = new Cell(""); } if (OcorrenciasToRegisterLog.Count() == 0) { return(null); } //var filePath = "G:\\DAYTONA\\LOG_ENVIO_ENTRADA_ENTREGA\\" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".html"; //var filePath = "C:\\"+CNPJ+"_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".XLS"; // aqui produçao var filePath = "G:\\Daytona\\LogOcorrencias\\" + CNPJ + "_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".xls"; // pra testes leandro // var filePath = "C:\\TEMP\\" + CNPJ + "_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".XLS"; // geração antiga // ds.Tables.Add(dt); // ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, ds); workbook.Worksheets.Add(worksheet); workbook.Save(filePath); attachmentFilename = filePath; } catch (Exception e) { log.RegistraLog("Erro GeraCSV grupo ELG" + e.StackTrace, 3); } return(OcorrenciasToRegisterLog); }
public static bool NeedSendToday(float Days, string relType) { // somente para testes //return true; SqlDataReader _dtReader = null; try { var _rCmd = @" SELECT MAX(DATA_ENVIO) AS ULTIMO_ENVIO FROM OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA WHERE TYPEREL = '" + relType + "'"; _dtReader = ExecSql.execsqlDr(_rCmd); DateTime UltimoEnvio = new DateTime(); try { while (_dtReader.Read()) { UltimoEnvio = _dtReader.GetDateTime(_dtReader.GetOrdinal("ULTIMO_ENVIO")); } } catch { _rCmd = @" SELECT Count(1) AS COUNTER FROM OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA" ; _dtReader = ExecSql.execsqlDr(_rCmd); try { int qtd = 0; while (_dtReader.Read()) { qtd = _dtReader.GetInt32(_dtReader.GetOrdinal("COUNTER")); } if (qtd == 0) { _dtReader.Close(); return(true); } } catch { } } _dtReader.Close(); // Alterado Joederli - 28/05/2019 - chamado 1490 - incluir envio as 16:00 e alterado para enviar as 9 e não a partir das 9 if (UltimoEnvio.Date <= DateTime.Now.Date.AddDays(Days) && DateTime.Now.Hour == 21) { return(true); } //if (DateTime.Now.Hour == 21 && DateTime.Now.Minute <= 05) //{ // return true; //} } catch { } return(false); }
public static bool isNow(float Days, string relType, int timeTosend) { // somente para testes //return true; SqlDataReader _dtReader = null; try { var _rCmd = @" SELECT MAX(DATA_ENVIO) AS ULTIMO_ENVIO FROM OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA WHERE TYPEREL = '" + relType + "'"; _dtReader = ExecSql.execsqlDr(_rCmd); DateTime UltimoEnvio = new DateTime(); try { while (_dtReader.Read()) { UltimoEnvio = _dtReader.GetDateTime(_dtReader.GetOrdinal("ULTIMO_ENVIO")); } } catch { _rCmd = @" SELECT Count(1) AS COUNTER FROM OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA" ; _dtReader = ExecSql.execsqlDr(_rCmd); try { int qtd = 0; while (_dtReader.Read()) { qtd = _dtReader.GetInt32(_dtReader.GetOrdinal("COUNTER")); } if (qtd == 0) { _dtReader.Close(); return(true); } } catch { } } _dtReader.Close(); if (UltimoEnvio.Date <= DateTime.Now.Date.AddDays(Days) && DateTime.Now.Hour == timeTosend) { return(true); } } catch { } return(false); }
public static List <OcorrenciaConhecimento> GenerateCSV() { WriteToAnEventLog log = new WriteToAnEventLog(); var OcorrenciasToRegisterLog = new List <OcorrenciaConhecimento>(); try { var _rCmd = @"exec SP_RETORNA_DADOS_RELATORIO_OCORRENCIA 'STAR_F4D17A24-187D-4' "; SqlDataReader _dtReader = ExecSql.execsqlDr(_rCmd); var CNPJ = ""; //DataSet ds = new DataSet("New_DataSet"); //DataTable dt = new DataTable("Faturas"); //dt.Columns.Add("NR_NF"); //dt.Columns.Add("OS"); //dt.Columns.Add("DATA_ENTREGA"); //dt.Columns.Add("LINK_RASTREIO"); //dt.Columns.Add("VALOR"); Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("Confirmação de envio"); worksheet.Cells[0, 0] = new Cell("NR NF"); worksheet.Cells[0, 1] = new Cell("OS"); worksheet.Cells[0, 2] = new Cell("DATA ENTREGA"); worksheet.Cells[0, 3] = new Cell("LINK_RASTREIO"); worksheet.Cells[0, 4] = new Cell("VALOR"); int i = 1; while (_dtReader.Read()) { try { CNPJ = _dtReader.GetString(_dtReader.GetOrdinal("A03_010_C")).Trim(); } catch { } var NR_NF = ""; var OS = ""; DateTime DT_ENTREGA; var LINK = ""; double VALOR = 0.00; try { NR_NF = _dtReader.GetString(_dtReader.GetOrdinal("NR_NF")).Trim(); } catch { } try { LINK = _dtReader.GetString(_dtReader.GetOrdinal("LINK")).Trim(); } catch { } try { VALOR = (double)_dtReader.GetDecimal(_dtReader.GetOrdinal("vl_total")); } catch { } // aqui adiciono os dados no excel worksheet.Cells[i, 0] = new Cell(NR_NF); worksheet.Cells[i, 1] = new Cell(OS); if (_dtReader.GetInt32(_dtReader.GetOrdinal("TEM_ENTREGA")) == 1) { try { DT_ENTREGA = _dtReader.GetDateTime(_dtReader.GetOrdinal("DT_ENTREGA")); worksheet.Cells[i, 2] = new Cell(DT_ENTREGA, @"DD/MM/YYYY"); OcorrenciasToRegisterLog.Add(new OcorrenciaConhecimento { Seq = 2, CodigoOcorrencia = 1, SatusEnviado = 1, UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")).Trim(), LogRegister = _dtReader.GetInt32(_dtReader.GetOrdinal("LogRegister")) }); } catch { } } else { OcorrenciasToRegisterLog.Add(new OcorrenciaConhecimento { Seq = 1, CodigoOcorrencia = 75, SatusEnviado = 1, UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")).Trim(), LogRegister = _dtReader.GetInt32(_dtReader.GetOrdinal("LogRegister")) }); worksheet.Cells[i, 2] = new Cell("");; } worksheet.Cells[i, 3] = new Cell(LINK); worksheet.Cells[i, 4] = new Cell(VALOR); i++; } _dtReader.Close(); //Resolve problema: O Excel encontrou conteúdo ilegível / Invalid or corrupt file (unreadable content) for (int j = i; j < 100; j++) { worksheet.Cells[j, 0] = new Cell(""); worksheet.Cells[j, 1] = new Cell(""); worksheet.Cells[j, 2] = new Cell(""); worksheet.Cells[j, 3] = new Cell(""); worksheet.Cells[j, 4] = new Cell(""); } if (OcorrenciasToRegisterLog.Count() == 0) { return(null); } //var filePath = "G:\\DAYTONA\\LOG_ENVIO_ENTRADA_ENTREGA\\" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".html"; //var filePath = "C:\\"+CNPJ+"_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".XLS"; // aqui produçao var filePath = "G:\\Daytona\\LogOcorrencias\\" + CNPJ + "_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".xls"; // pra testes leandro // var filePath = "C:\\temp\\" + CNPJ + "_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".XLS"; // geração antiga // ds.Tables.Add(dt); // ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, ds); workbook.Worksheets.Add(worksheet); workbook.Save(filePath); attachmentFilename = filePath; } catch (Exception e) { log.RegistraLog("Erro GenerateCSV SELBETTI " + e.StackTrace, 3); return(null); } return(OcorrenciasToRegisterLog); }
public static List <OcorrenciaConhecimento> GenerateCSV() { WriteToAnEventLog log = new WriteToAnEventLog(); var OcorrenciasToRegisterLog = new List <OcorrenciaConhecimento>(); try { //BITTENCOURT AUDIO E VIDEO -CENTRAL SUPORTES var _rCmd = @"exec SP_RETORNA_DADOS_RELATORIO_OCORRENCIA 'EDI_PLANILHA'"; SqlDataReader _dtReader = ExecSql.execsqlDr(_rCmd); // Data / Hawb / NF / Valor / Destinatário / Link Rastreio Workbook workbook = new Workbook(); Worksheet worksheet = new Worksheet("Confirmacao de envio"); worksheet.Cells[0, 0] = new Cell("CTE"); worksheet.Cells[0, 1] = new Cell("ETD"); worksheet.Cells[0, 2] = new Cell("ETA"); worksheet.Cells[0, 3] = new Cell("order_number"); worksheet.Cells[0, 4] = new Cell("numero de rastreio"); worksheet.Cells[0, 5] = new Cell("origem"); worksheet.Cells[0, 6] = new Cell("destino"); worksheet.Cells[0, 7] = new Cell("conta_consignatario"); worksheet.Cells[0, 8] = new Cell("consignatario"); worksheet.Cells[0, 9] = new Cell("endereco _consignatario1"); worksheet.Cells[0, 10] = new Cell("endereco _consignatario2"); worksheet.Cells[0, 11] = new Cell("endereco _consignatario3"); worksheet.Cells[0, 12] = new Cell("cidade_consignatario"); worksheet.Cells[0, 13] = new Cell("estado_consignatario"); worksheet.Cells[0, 14] = new Cell("cep_consignatario"); worksheet.Cells[0, 15] = new Cell("pais-consignatario"); worksheet.Cells[0, 16] = new Cell("email-consignatario"); worksheet.Cells[0, 17] = new Cell("telefone_consignatario"); worksheet.Cells[0, 18] = new Cell("celular_consignatario"); worksheet.Cells[0, 19] = new Cell("CPF/CNPJ_consignatario"); worksheet.Cells[0, 20] = new Cell("peças"); worksheet.Cells[0, 21] = new Cell("peso bruto"); worksheet.Cells[0, 22] = new Cell("pesoliquido"); worksheet.Cells[0, 23] = new Cell("unidade_de_medida"); worksheet.Cells[0, 24] = new Cell("altura"); worksheet.Cells[0, 25] = new Cell("comprimento"); worksheet.Cells[0, 26] = new Cell("largura"); worksheet.Cells[0, 27] = new Cell("descricao_mercadoria"); worksheet.Cells[0, 28] = new Cell("valor_mercadoria"); worksheet.Cells[0, 29] = new Cell("frete"); worksheet.Cells[0, 30] = new Cell("moeda"); worksheet.Cells[0, 31] = new Cell("tipo de serviçp"); worksheet.Cells[0, 32] = new Cell("conta_embarcador"); worksheet.Cells[0, 33] = new Cell("nome_embarcador"); worksheet.Cells[0, 34] = new Cell("endereço_embarcador1"); worksheet.Cells[0, 35] = new Cell("endereço_embarcador2"); worksheet.Cells[0, 36] = new Cell("cidade_embarcador"); worksheet.Cells[0, 37] = new Cell("estado_embarcador"); worksheet.Cells[0, 38] = new Cell("cep_embarcador"); worksheet.Cells[0, 39] = new Cell("pais-embarcador"); worksheet.Cells[0, 40] = new Cell("email-embarcador"); worksheet.Cells[0, 41] = new Cell("telefone_embarcador"); int i = 1; while (_dtReader.Read()) { string CTE = ""; string ETD = ""; string ETA = ""; string ORDER_NUMBER = ""; string NUMERO_RASTREIO = ""; string ORIGEM = ""; string DESTINO = ""; string conta_consignatario = ""; string consignatario = ""; string endereco_consignatario1 = ""; string endereco_consignatario2 = ""; string endereco_consignatario3 = ""; string cidade_consignatario = ""; string estado_consignatario = ""; string cep_consignatario = ""; string pais_consignatario = ""; string email_consignatario = ""; string telefone_consignatario = ""; string celular_consignatario = ""; string CPF_CNPJ_consignatario = ""; Int32 pecas = 0; decimal pesobruto = 0; decimal pesoliquido = 0; string unidade_de_medida = ""; decimal altura = 0; decimal comprimento = 0; decimal largura = 0; string descricao_mercadoria = ""; decimal valor_mercadoria = 0; decimal frete = 0; string moeda = ""; string tipodeservico = ""; string conta_embarcador = ""; string nome_embarcador = ""; string endereco_embarcador1 = ""; string endereco_embarcador2 = ""; string cidade_embarcador = ""; string estado_embarcador = ""; string cep_embarcador = ""; string pais_embarcador = ""; string email_embarcador = ""; string telefone_embarcador = ""; try { CTE = _dtReader.GetString(_dtReader.GetOrdinal("CTE")).Trim(); } catch (Exception e) { } try { ETD = _dtReader.GetString(_dtReader.GetOrdinal("ETD")).Trim(); } catch (Exception e) { } try { ETA = _dtReader.GetString(_dtReader.GetOrdinal("ETA")).Trim(); } catch (Exception e) { } try { ORDER_NUMBER = _dtReader.GetString(_dtReader.GetOrdinal("ORDER_NUMBER")).Trim(); } catch (Exception e) { } try { NUMERO_RASTREIO = _dtReader.GetString(_dtReader.GetOrdinal("NUMERO_RASTREIO")).Trim(); } catch (Exception e) { } try { ORIGEM = _dtReader.GetString(_dtReader.GetOrdinal("ORIGEM")).Trim(); } catch (Exception e) { } try { DESTINO = _dtReader.GetString(_dtReader.GetOrdinal("DESTINO")).Trim(); } catch (Exception e) { } try { conta_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("conta_consignatario")).Trim(); } catch (Exception e) { } try { consignatario = _dtReader.GetString(_dtReader.GetOrdinal("consignatario")).Trim(); } catch (Exception e) { } try { endereco_consignatario1 = _dtReader.GetString(_dtReader.GetOrdinal("endereco_consignatario1")).Trim(); } catch (Exception e) { } try { endereco_consignatario2 = _dtReader.GetString(_dtReader.GetOrdinal("endereco_consignatario2")).Trim(); } catch (Exception e) { } try { endereco_consignatario3 = _dtReader.GetString(_dtReader.GetOrdinal("endereco_consignatario3")).Trim(); } catch (Exception e) { } try { cidade_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("cidade_consignatario")).Trim(); } catch (Exception e) { } try { estado_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("estado_consignatario")).Trim(); } catch (Exception e) { } try { cep_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("cep_consignatario")).Trim(); } catch (Exception e) { } try { pais_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("pais_consignatario")).Trim(); } catch (Exception e) { } try { email_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("email_consignatario")).Trim(); } catch (Exception e) { } try { telefone_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("telefone_consignatario")).Trim(); } catch (Exception e) { } try { celular_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("celular_consignatario")).Trim(); } catch (Exception e) { } try { CPF_CNPJ_consignatario = _dtReader.GetString(_dtReader.GetOrdinal("CPF_CNPJ_consignatario")).Trim(); } catch (Exception e) { } try { pecas = (Int32)_dtReader.GetInt32(_dtReader.GetOrdinal("pecas")); } catch (Exception e) { } try { pesobruto = _dtReader.GetDecimal(_dtReader.GetOrdinal("pesobruto")); } catch (Exception e) { } try { pesoliquido = _dtReader.GetDecimal(_dtReader.GetOrdinal("pesoliquido")); } catch (Exception e) { } try { unidade_de_medida = _dtReader.GetString(_dtReader.GetOrdinal("unidade_de_medida")); } catch (Exception e) { } try { altura = _dtReader.GetDecimal(_dtReader.GetOrdinal("altura")); } catch (Exception e) { } try { comprimento = _dtReader.GetDecimal(_dtReader.GetOrdinal("comprimento")); } catch (Exception e) { } try { largura = _dtReader.GetDecimal(_dtReader.GetOrdinal("largura")); } catch (Exception e) { } try { descricao_mercadoria = _dtReader.GetString(_dtReader.GetOrdinal("descricao_mercadoria")).Trim(); } catch (Exception e) { } try { valor_mercadoria = _dtReader.GetDecimal(_dtReader.GetOrdinal("VALOR_MERCADORIA")); } catch (Exception e) { } try { frete = _dtReader.GetDecimal(_dtReader.GetOrdinal("frete")); } catch (Exception e) { } try { moeda = _dtReader.GetString(_dtReader.GetOrdinal("moeda")).Trim(); } catch (Exception e) { } try { tipodeservico = _dtReader.GetString(_dtReader.GetOrdinal("tipodeservico")).Trim(); } catch (Exception e) { } try { conta_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("conta_embarcador")).Trim(); } catch (Exception e) { } try { nome_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("nome_embarcador")).Trim(); } catch (Exception e) { } try { endereco_embarcador1 = _dtReader.GetString(_dtReader.GetOrdinal("endereco_embarcador1")).Trim(); } catch (Exception e) { } try { endereco_embarcador2 = _dtReader.GetString(_dtReader.GetOrdinal("endereco_embarcador2")).Trim(); } catch (Exception e) { } try { cidade_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("cidade_embarcador")).Trim(); } catch (Exception e) { } try { estado_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("estado_embarcador")).Trim(); } catch (Exception e) { } try { cep_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("cep_embarcador")).Trim(); } catch (Exception e) { } try { pais_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("pais_embarcador")).Trim(); } catch (Exception e) { } try { email_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("email_embarcador")).Trim(); } catch (Exception e) { } try { telefone_embarcador = _dtReader.GetString(_dtReader.GetOrdinal("telefone_embarcador")).Trim(); } catch (Exception e) { } worksheet.Cells[i, 0] = new Cell(CTE); worksheet.Cells[i, 1] = new Cell(ETD); worksheet.Cells[i, 2] = new Cell(ETA); worksheet.Cells[i, 3] = new Cell(ORDER_NUMBER); worksheet.Cells[i, 4] = new Cell(NUMERO_RASTREIO); worksheet.Cells[i, 5] = new Cell(ORIGEM); worksheet.Cells[i, 6] = new Cell(DESTINO); worksheet.Cells[i, 7] = new Cell(conta_consignatario); worksheet.Cells[i, 8] = new Cell(consignatario); worksheet.Cells[i, 9] = new Cell(endereco_consignatario1); worksheet.Cells[i, 10] = new Cell(endereco_consignatario2); worksheet.Cells[i, 11] = new Cell(endereco_consignatario3); worksheet.Cells[i, 12] = new Cell(cidade_consignatario); worksheet.Cells[i, 13] = new Cell(estado_consignatario); worksheet.Cells[i, 14] = new Cell(cep_consignatario); worksheet.Cells[i, 15] = new Cell(pais_consignatario); worksheet.Cells[i, 16] = new Cell(email_consignatario); worksheet.Cells[i, 17] = new Cell(telefone_consignatario); worksheet.Cells[i, 18] = new Cell(celular_consignatario); worksheet.Cells[i, 19] = new Cell(CPF_CNPJ_consignatario); worksheet.Cells[i, 20] = new Cell(pecas); worksheet.Cells[i, 21] = new Cell(pesobruto); worksheet.Cells[i, 22] = new Cell(pesoliquido); worksheet.Cells[i, 23] = new Cell(unidade_de_medida); worksheet.Cells[i, 24] = new Cell(altura); worksheet.Cells[i, 25] = new Cell(comprimento); worksheet.Cells[i, 26] = new Cell(largura); worksheet.Cells[i, 27] = new Cell(descricao_mercadoria); worksheet.Cells[i, 28] = new Cell(valor_mercadoria); worksheet.Cells[i, 29] = new Cell(frete); worksheet.Cells[i, 30] = new Cell(moeda); worksheet.Cells[i, 31] = new Cell(tipodeservico); worksheet.Cells[i, 32] = new Cell(conta_embarcador); worksheet.Cells[i, 33] = new Cell(nome_embarcador); worksheet.Cells[i, 34] = new Cell(endereco_embarcador1); worksheet.Cells[i, 35] = new Cell(endereco_embarcador2); worksheet.Cells[i, 36] = new Cell(cidade_embarcador); worksheet.Cells[i, 37] = new Cell(estado_embarcador); worksheet.Cells[i, 38] = new Cell(cep_embarcador); worksheet.Cells[i, 39] = new Cell(pais_embarcador); worksheet.Cells[i, 40] = new Cell(email_embarcador); worksheet.Cells[i, 41] = new Cell(telefone_embarcador); OcorrenciasToRegisterLog.Add(new OcorrenciaConhecimento { Seq = 1, CodigoOcorrencia = 75, SatusEnviado = 1, UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")).Trim(), LogRegister = 1 });; i++; } _dtReader.Close(); //Resolve problema: O Excel encontrou conteúdo ilegível / Invalid or corrupt file (unreadable content) for (int j = i; j < 100; j++) { worksheet.Cells[i, 1] = new Cell(""); worksheet.Cells[i, 2] = new Cell(""); worksheet.Cells[i, 3] = new Cell(""); worksheet.Cells[i, 4] = new Cell(""); worksheet.Cells[i, 5] = new Cell(""); worksheet.Cells[i, 6] = new Cell(""); worksheet.Cells[i, 7] = new Cell(""); worksheet.Cells[i, 8] = new Cell(""); worksheet.Cells[i, 9] = new Cell(""); worksheet.Cells[i, 10] = new Cell(""); worksheet.Cells[i, 11] = new Cell(""); worksheet.Cells[i, 12] = new Cell(""); worksheet.Cells[i, 13] = new Cell(""); worksheet.Cells[i, 14] = new Cell(""); worksheet.Cells[i, 15] = new Cell(""); worksheet.Cells[i, 16] = new Cell(""); worksheet.Cells[i, 17] = new Cell(""); worksheet.Cells[i, 18] = new Cell(""); worksheet.Cells[i, 19] = new Cell(""); worksheet.Cells[i, 20] = new Cell(""); worksheet.Cells[i, 21] = new Cell(""); worksheet.Cells[i, 22] = new Cell(""); worksheet.Cells[i, 23] = new Cell(""); worksheet.Cells[i, 24] = new Cell(""); worksheet.Cells[i, 25] = new Cell(""); worksheet.Cells[i, 26] = new Cell(""); worksheet.Cells[i, 27] = new Cell(""); worksheet.Cells[i, 28] = new Cell(""); worksheet.Cells[i, 29] = new Cell(""); worksheet.Cells[i, 30] = new Cell(""); worksheet.Cells[i, 31] = new Cell(""); worksheet.Cells[i, 32] = new Cell(""); worksheet.Cells[i, 33] = new Cell(""); worksheet.Cells[i, 34] = new Cell(""); worksheet.Cells[i, 35] = new Cell(""); worksheet.Cells[i, 36] = new Cell(""); worksheet.Cells[i, 37] = new Cell(""); worksheet.Cells[i, 38] = new Cell(""); worksheet.Cells[i, 39] = new Cell(""); worksheet.Cells[i, 40] = new Cell(""); worksheet.Cells[i, 41] = new Cell(""); worksheet.Cells[i, 42] = new Cell(""); } if (OcorrenciasToRegisterLog.Count() == 0) { return(null); } //var filePath = "G:\\DAYTONA\\LOG_ENVIO_ENTRADA_ENTREGA\\" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".html"; //var filePath = "C:\\"+CNPJ+"_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".XLS"; // aqui produçao var filePath = "G:\\Daytona\\LogOcorrencias\\EDI_PLANILHA_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".xls"; // pra testes leandro // var filePath = "C:\\TEMP\\EDI_PLANILHA_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".XLS"; // geracao antiga // ds.Tables.Add(dt); // ExcelLibrary.DataSetHelper.CreateWorkbook(filePath, ds); workbook.Worksheets.Add(worksheet); workbook.Save(filePath); attachmentFilename = filePath; } catch (Exception e) { log.RegistraLog("Erro GeraCSV grupo ELG" + e.StackTrace, 3); } return(OcorrenciasToRegisterLog); }
public static List <Email> GetConhecimentosComOcorrenciasSemEnviar() { SqlDataReader _dtReader = null; var listUkeyOcorrencia = new List <Email>(); string _sComando = @"SELECT * FROM [VW_EMAIL_CONFIRMACAO_ENTRADA_ENTREGA] ORDER BY DT_HAWB"; _dtReader = ExecSql.execsqlDr(_sComando); while (_dtReader.Read()) { var _Email = new Email { Ocorrencias = new List <OcorrenciaConhecimento>() }; _Email.EmailToSend = string.IsNullOrEmpty(_Email.EmailToSend) ? _dtReader.GetString(_dtReader.GetOrdinal("EMAIL")).ToString().Trim() : _Email.EmailToSend; _Email.Nome = string.IsNullOrEmpty(_Email.Nome) ? _dtReader.GetString(_dtReader.GetOrdinal("NOME")).ToString().Trim() : _Email.Nome; _Email.NR_HAWB = string.IsNullOrEmpty(_Email.NR_HAWB) ? "HAWB - " + _dtReader.GetString(_dtReader.GetOrdinal("NR_HAWB")).ToString().Trim() : _Email.NR_HAWB; _Email.Ocorrencias.Add(new OcorrenciaConhecimento { UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")), CodigoOcorrencia = 75, Local = "", DataOcorrencia = _dtReader.GetDateTime(_dtReader.GetOrdinal("DT_HAWB")), DescricaoOcorrencia = "Coleta solicitada" }); var DT_ENTREGA = new DateTime(); try { DT_ENTREGA = _dtReader.GetDateTime(_dtReader.GetOrdinal("DT_ENTREGA")); } catch { } if (DT_ENTREGA != null && DT_ENTREGA != new DateTime()) { _Email.Ocorrencias.Add(new OcorrenciaConhecimento { UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")), CodigoOcorrencia = 1, Local = _dtReader.GetString(_dtReader.GetOrdinal("LOCAL_ENTREGA")), RecebidoPor = _dtReader.GetString(_dtReader.GetOrdinal("DS_RECEBIDO")), DescricaoOcorrencia = "Entregue", DataOcorrencia = DT_ENTREGA, }); } listUkeyOcorrencia.Add(_Email); } _dtReader.Close(); return(listUkeyOcorrencia); }
public static List <OcorrenciaConhecimento> GenerateCSV2(int relType, string A03_UKEY, string fileInitialName) { var _rCmd = @"exec SP_RETORNA_DADOS_RELATORIO_OCORRENCIA_COMPLETO " + relType.ToString() + ", '" + A03_UKEY + "'"; SqlDataReader _dtReader = ExecSql.execsqlDr(_rCmd); var newLineChar = (char)13; //Previsão de entrega, Nome do destinatário, CEP, UF e Município var FileContent = "DATA_COLETA;NR_NOTA_FISCAL;LINK_RASTREIO;DATA_ENTREGA;PREVISAO_ENTREGA;NOME_DESTINATARIO;CEP_DESTINATARIO;UF_DESTINATARIO;MUNICIPIO_DESTINATARIO" + newLineChar; var CNPJ = ""; var OcorrenciasToRegisterLog = new List <OcorrenciaConhecimento>(); while (_dtReader.Read()) { try { CNPJ = _dtReader.GetString(_dtReader.GetOrdinal("A03_010_C")).Trim(); } catch { } FileContent = FileContent + string.Format("{0:dd/MM/yyyy}", _dtReader.GetDateTime(_dtReader.GetOrdinal("DT_COLETA"))) + ";"; try { FileContent = FileContent + _dtReader.GetString(_dtReader.GetOrdinal("NR_NF")).Trim() + ";"; } catch { FileContent = FileContent + "" + ";"; } FileContent = FileContent + _dtReader.GetString(_dtReader.GetOrdinal("LINK")).Trim() + ";"; if (relType == 0) { OcorrenciasToRegisterLog.Add(new OcorrenciaConhecimento { Seq = 1, CodigoOcorrencia = 75, SatusEnviado = 1, UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")).Trim(), LogRegister = _dtReader.GetInt32(_dtReader.GetOrdinal("LogRegister")), CEP_DESTINATARIO = _dtReader.GetString(_dtReader.GetOrdinal("CEP_DESTINATARIO")).Trim(), ESTADO_DESTINATARIO = _dtReader.GetString(_dtReader.GetOrdinal("ESTADO_DESTINATARIO")).Trim(), NOME_RECIBIMENTO = _dtReader.GetString(_dtReader.GetOrdinal("NOME_RECIBIMENTO")).Trim(), MUNICIPIO_DESTINATARIO = _dtReader.GetString(_dtReader.GetOrdinal("MUNICIPIO_DESTINATARIO")).Trim(), Prev_Entrega = _dtReader.GetInt32(_dtReader.GetOrdinal("Prev_Entrega")) }); } try { FileContent = FileContent + string.Format("{0:dd/MM/yyyy}", _dtReader.GetDateTime(_dtReader.GetOrdinal("DT_ENTREGA"))) + ";"; OcorrenciasToRegisterLog.Add(new OcorrenciaConhecimento { Seq = 2, CodigoOcorrencia = 1, SatusEnviado = 1, UkeyConhecimento = _dtReader.GetString(_dtReader.GetOrdinal("UKEY")).Trim(), LogRegister = _dtReader.GetInt32(_dtReader.GetOrdinal("LogRegister")), CEP_DESTINATARIO = _dtReader.GetString(_dtReader.GetOrdinal("CEP_DESTINATARIO")).Trim(), ESTADO_DESTINATARIO = _dtReader.GetString(_dtReader.GetOrdinal("ESTADO_DESTINATARIO")).Trim(), NOME_RECIBIMENTO = _dtReader.GetString(_dtReader.GetOrdinal("NOME_RECIBIMENTO")).Trim(), MUNICIPIO_DESTINATARIO = _dtReader.GetString(_dtReader.GetOrdinal("MUNICIPIO_DESTINATARIO")).Trim(), Prev_Entrega = _dtReader.GetInt32(_dtReader.GetOrdinal("Prev_Entrega")) }); } catch { FileContent = FileContent + "" + ";"; //FileContent = FileContent + "" + newLineChar; } try { FileContent = FileContent + _dtReader.GetInt32(_dtReader.GetOrdinal("Prev_Entrega")).ToString() + ";"; } catch { FileContent = FileContent + "" + ";"; } try { FileContent = FileContent + _dtReader.GetString(_dtReader.GetOrdinal("NOME_RECIBIMENTO")) + ";"; } catch { FileContent = FileContent + "" + ";"; } try { FileContent = FileContent + _dtReader.GetString(_dtReader.GetOrdinal("CEP_DESTINATARIO")) + ";"; } catch { FileContent = FileContent + "" + ";"; } try { FileContent = FileContent + _dtReader.GetString(_dtReader.GetOrdinal("ESTADO_DESTINATARIO")) + ";"; } catch { FileContent = FileContent + "" + ";"; } try { FileContent = FileContent + _dtReader.GetString(_dtReader.GetOrdinal("MUNICIPIO_DESTINATARIO")) + newLineChar; } catch { FileContent = FileContent + "" + newLineChar; } } _dtReader.Close(); if (OcorrenciasToRegisterLog.Count() == 0) { return(null); } var filePath = "G:\\Daytona\\LogOcorrencias\\" + fileInitialName + CNPJ + "_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".CSV"; try { System.IO.File.WriteAllText(filePath, FileContent); } catch { filePath = "C:\\" + fileInitialName + CNPJ + "_" + string.Format("{0:yyyy_MM_dd}", DateTime.Now) + ".CSV"; System.IO.File.WriteAllText(filePath, FileContent); } attachmentFilename = filePath; return(OcorrenciasToRegisterLog); }
public static bool WasSent(int typeRel) { SqlDataReader _dtReader = null; try { var _rCmd = @"SELECT MAX(DATA_ENVIO) AS ULTIMO_ENVIO FROM OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA WHERE TYPEREL=" + typeRel.ToString(); _dtReader = ExecSql.execsqlDr(_rCmd); DateTime UltimoEnvio = new DateTime(); try { while (_dtReader.Read()) { UltimoEnvio = _dtReader.GetDateTime(_dtReader.GetOrdinal("ULTIMO_ENVIO")); } } catch { _rCmd = @" SELECT Count(1) AS COUNTER FROM OCORRENCIAS_ENVIADAS_ENTRADA_ENTREGA WHERE TYPEREL=" + typeRel.ToString(); _dtReader = ExecSql.execsqlDr(_rCmd); try { int qtd = 0; while (_dtReader.Read()) { qtd = _dtReader.GetInt32(_dtReader.GetOrdinal("COUNTER")); } if (qtd == 0) { _dtReader.Close(); return(false); } } catch { } } _dtReader.Close(); if (UltimoEnvio.Day == DateTime.Now.Day && UltimoEnvio.Month == DateTime.Now.Month && UltimoEnvio.Year == DateTime.Now.Year) { return(true); } } catch { } return(false); }