private bool InserirPJ(ExcelPJModel row)
{
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@NOME", row.NOME),
new SqlParameter("@SUBTITULO", row.SUBTITULO),
new SqlParameter("@RAMO", row.RAMO),
new SqlParameter("@TIPO", row.TIPO),
new SqlParameter("@DDD1", row.DDD1),
new SqlParameter("@NUMTEL1", row.NUMTEL1),
new SqlParameter("@TIPOTEL1", row.TIPOTEL1),
new SqlParameter("@EMAIL1", row.EMAIL1),
new SqlParameter("@LOGRADOURO1", row.LOGRADOURO1),
new SqlParameter("@NUMEND1", row.NUMEND1),
new SqlParameter("@BAIRRO1", row.BAIRRO1),
new SqlParameter("@COMP1", row.COMP1),
new SqlParameter("@CEP1", row.CEP1),
new SqlParameter("@CIDADE1", row.CIDADE1),
new SqlParameter("@SIGLAESTAD1", row.SIGLAESTAD1),
new SqlParameter("@DDD2", row.DDD2),
new SqlParameter("@NUMTEL2", row.NUMTEL2),
new SqlParameter("@TIPOTEL2", row.TIPOTEL2),
new SqlParameter("@EMAIL2", row.EMAIL2),
new SqlParameter("@LOGRADOURO2", row.LOGRADOURO2),
new SqlParameter("@NUMEND2", row.NUMEND2),
new SqlParameter("@BAIRRO2", row.BAIRRO2),
new SqlParameter("@COMP2", row.COMP2),
new SqlParameter("@CEP2", row.CEP2),
new SqlParameter("@CIDADE2", row.CIDADE2),
new SqlParameter("@SIGLAESTAD2", row.SIGLAESTAD2),
new SqlParameter("@DDD3", row.DDD3),
new SqlParameter("@NUMTEL3", row.NUMTEL3),
new SqlParameter("@TIPOTEL3", row.TIPOTEL3),
new SqlParameter("@EMAIL3", row.EMAIL3),
new SqlParameter("@LOGRADOURO3", row.LOGRADOURO3),
new SqlParameter("@NUMEND3", row.NUMEND3),
new SqlParameter("@BAIRRO3", row.BAIRRO3),
new SqlParameter("@COMP3", row.COMP3),
new SqlParameter("@CEP3", row.CEP3),
new SqlParameter("@CIDADE3", row.CIDADE3),
new SqlParameter("@SIGLAESTAD3", row.SIGLAESTAD3),
new SqlParameter("@TIPOINTERNET1", row.TIPOINTERNET1),
new SqlParameter("@ENDINTERNET1", row.ENDINTERNET1),
new SqlParameter("@TIPOINTERNET2", row.TIPOINTERNET2),
new SqlParameter("@ENDINTERNET2", row.ENDINTERNET2),
new SqlParameter("@TIPOINTERNET3", row.TIPOINTERNET3),
new SqlParameter("@ENDINTERNET3", row.ENDINTERNET3),
new SqlParameter("@OBSERVACAO", row.OBSERVACAO)
};
DataTable dt = db.ExecuteReader<SqlDataReader>("proc_importaPJ", parms);
string erro = dt.Rows[0]["ERRO"].ToString();
if (erro != "")
throw new Exception(erro);
return true;
}
public byte[] FiltroPJ(string _nome, int? _idRamo, int? _idTipo)
{
List<PJModel> listaPJ = new PJ().FiltroFull(_nome, _idRamo, _idTipo);
List<ExcelPJModel> listaExcelPJ = new List<ExcelPJModel>();
ExcelPJModel excelPJ;
foreach (var pj in listaPJ)
{
excelPJ = new ExcelPJModel();
excelPJ.NOME = pj.Nome;
excelPJ.SUBTITULO = pj.Subtitulo;
excelPJ.RAMO = pj.Ramo.Nome;
excelPJ.TIPO = pj.Tipo.Nome;
excelPJ.OBSERVACAO = pj.Observacao;
if (pj.Telefones.Count > 0)
{
excelPJ.DDD1 = pj.Telefones[0].Ddd;
excelPJ.NUMTEL1 = pj.Telefones[0].Numero;
excelPJ.TIPOTEL1 = pj.Telefones[0].TipoTelefone.Nome;
if (pj.Telefones.Count > 1)
{
excelPJ.DDD2 = pj.Telefones[1].Ddd;
excelPJ.NUMTEL2 = pj.Telefones[1].Numero;
excelPJ.TIPOTEL2 = pj.Telefones[1].TipoTelefone.Nome;
if (pj.Telefones.Count > 2)
{
excelPJ.DDD3 = pj.Telefones[2].Ddd;
excelPJ.NUMTEL3 = pj.Telefones[2].Numero;
excelPJ.TIPOTEL3 = pj.Telefones[2].TipoTelefone.Nome;
}
}
}
if (pj.Emails.Count > 0)
{
excelPJ.EMAIL1 = pj.Emails[0].Nome;
if (pj.Emails.Count > 1)
{
excelPJ.EMAIL2 = pj.Emails[1].Nome;
if (pj.Emails.Count > 2)
{
excelPJ.EMAIL3 = pj.Emails[2].Nome;
}
}
}
if (pj.Enderecos.Count > 0)
{
excelPJ.LOGRADOURO1 = pj.Enderecos[0].Logradouro;
excelPJ.NUMEND1 = pj.Enderecos[0].Numero.ToString();
excelPJ.BAIRRO1 = pj.Enderecos[0].Bairro;
excelPJ.COMP1 = pj.Enderecos[0].Complemento;
excelPJ.CEP1 = pj.Enderecos[0].CEP;
excelPJ.CIDADE1 = pj.Enderecos[0].Cidade.Nome;
excelPJ.SIGLAESTAD1 = pj.Enderecos[0].Cidade.Estado.Sigla;
if (pj.Enderecos.Count > 1)
{
excelPJ.LOGRADOURO2 = pj.Enderecos[1].Logradouro;
excelPJ.NUMEND2 = pj.Enderecos[1].Numero.ToString();
excelPJ.BAIRRO2 = pj.Enderecos[1].Bairro;
excelPJ.COMP2 = pj.Enderecos[1].Complemento;
excelPJ.CEP2 = pj.Enderecos[1].CEP;
excelPJ.CIDADE2 = pj.Enderecos[1].Cidade.Nome;
excelPJ.SIGLAESTAD2 = pj.Enderecos[1].Cidade.Estado.Sigla;
if (pj.Enderecos.Count > 2)
{
excelPJ.LOGRADOURO3 = pj.Enderecos[2].Logradouro;
excelPJ.NUMEND3 = pj.Enderecos[2].Numero.ToString();
excelPJ.BAIRRO3 = pj.Enderecos[2].Bairro;
excelPJ.COMP3 = pj.Enderecos[2].Complemento;
excelPJ.CEP3 = pj.Enderecos[2].CEP;
excelPJ.CIDADE3 = pj.Enderecos[2].Cidade.Nome;
excelPJ.SIGLAESTAD3 = pj.Enderecos[2].Cidade.Estado.Sigla;
}
}
}
if (pj.Internets.Count > 0)
{
excelPJ.TIPOINTERNET1 = pj.Internets[0].TipoInternet.Nome;
excelPJ.ENDINTERNET1 = pj.Internets[0].Nome;
if (pj.Internets.Count > 1)
{
excelPJ.TIPOINTERNET2 = pj.Internets[1].TipoInternet.Nome;
excelPJ.ENDINTERNET2 = pj.Internets[1].Nome;
if (pj.Internets.Count > 2)
{
excelPJ.TIPOINTERNET3 = pj.Internets[2].TipoInternet.Nome;
excelPJ.ENDINTERNET3 = pj.Internets[2].Nome;
}
}
}
listaExcelPJ.Add(excelPJ);
}
if (listaExcelPJ.Count > 0)
return FormatExcelPJ(listaExcelPJ);
else
return null;
}
public byte[] ImportaPJ(Stream file)
{
List<ExcelPJModel> lista = new List<ExcelPJModel>();
using (var package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
ExcelPJModel excelRow;
for (int rowE = 2; worksheet.Cells[rowE, 1].Value != null; rowE++)
{
excelRow = new ExcelPJModel();
excelRow.NOME = trataValor(worksheet.Cells[rowE, 1]);
excelRow.SUBTITULO = trataValor(worksheet.Cells[rowE, 2]);
excelRow.RAMO = trataValor(worksheet.Cells[rowE, 3]);
excelRow.TIPO = trataValor(worksheet.Cells[rowE, 4]);
excelRow.DDD1 = trataValor(worksheet.Cells[rowE, 5]);
excelRow.NUMTEL1 = trataValor(worksheet.Cells[rowE, 6]);
excelRow.TIPOTEL1 = trataValor(worksheet.Cells[rowE, 7]);
excelRow.EMAIL1 = trataValor(worksheet.Cells[rowE, 8]);
excelRow.LOGRADOURO1 = trataValor(worksheet.Cells[rowE, 9]);
excelRow.NUMEND1 = trataValor(worksheet.Cells[rowE, 10]);
excelRow.BAIRRO1 = trataValor(worksheet.Cells[rowE, 11]);
excelRow.COMP1 = trataValor(worksheet.Cells[rowE, 12]);
excelRow.CEP1 = trataValor(worksheet.Cells[rowE, 13]);
excelRow.CIDADE1 = trataValor(worksheet.Cells[rowE, 14]);
excelRow.SIGLAESTAD1 = trataValor(worksheet.Cells[rowE, 15]);
excelRow.DDD2 = trataValor(worksheet.Cells[rowE, 16]);
excelRow.NUMTEL2 = trataValor(worksheet.Cells[rowE, 17]);
excelRow.TIPOTEL2 = trataValor(worksheet.Cells[rowE, 18]);
excelRow.EMAIL2 = trataValor(worksheet.Cells[rowE, 19]);
excelRow.LOGRADOURO2 = trataValor(worksheet.Cells[rowE, 20]);
excelRow.NUMEND2 = trataValor(worksheet.Cells[rowE, 21]);
excelRow.BAIRRO2 = trataValor(worksheet.Cells[rowE, 22]);
excelRow.COMP2 = trataValor(worksheet.Cells[rowE, 23]);
excelRow.CEP2 = trataValor(worksheet.Cells[rowE, 24]);
excelRow.CIDADE2 = trataValor(worksheet.Cells[rowE, 25]);
excelRow.SIGLAESTAD2 = trataValor(worksheet.Cells[rowE, 26]);
excelRow.DDD3 = trataValor(worksheet.Cells[rowE, 27]);
excelRow.NUMTEL3 = trataValor(worksheet.Cells[rowE, 28]);
excelRow.TIPOTEL3 = trataValor(worksheet.Cells[rowE, 29]);
excelRow.EMAIL3 = trataValor(worksheet.Cells[rowE, 30]);
excelRow.LOGRADOURO3 = trataValor(worksheet.Cells[rowE, 31]);
excelRow.NUMEND3 = trataValor(worksheet.Cells[rowE, 32]);
excelRow.BAIRRO3 = trataValor(worksheet.Cells[rowE, 33]);
excelRow.COMP3 = trataValor(worksheet.Cells[rowE, 34]);
excelRow.CEP3 = trataValor(worksheet.Cells[rowE, 35]);
excelRow.CIDADE3 = trataValor(worksheet.Cells[rowE, 36]);
excelRow.SIGLAESTAD3 = trataValor(worksheet.Cells[rowE, 37]);
excelRow.TIPOINTERNET1 = trataValor(worksheet.Cells[rowE, 38]);
excelRow.ENDINTERNET1 = trataValor(worksheet.Cells[rowE, 39]);
excelRow.TIPOINTERNET2 = trataValor(worksheet.Cells[rowE, 40]);
excelRow.ENDINTERNET2 = trataValor(worksheet.Cells[rowE, 41]);
excelRow.TIPOINTERNET3 = trataValor(worksheet.Cells[rowE, 42]);
excelRow.ENDINTERNET3 = trataValor(worksheet.Cells[rowE, 43]);
excelRow.OBSERVACAO = trataValor(worksheet.Cells[rowE, 44]);
lista.Add(excelRow);
}
}
List<ExcelPJModel> listaErro = new List<ExcelPJModel>();
foreach (var row in lista)
{
try
{
InserirPJ(row);
}
catch (Exception ex)
{
row.ERRO = ex.Message;
listaErro.Add(row);
}
}
if (listaErro != null && listaErro.Count > 0)
return FormatExcelPJ(listaErro);
else
return null;
}