public void GravaOperacao(EF_MOV_OPER modMovOper)
{
using (SQLiteConnection conn = new SQLiteConnection(conexaoSqlLite()))
{
conn.Open();
if (modMovOper.ID == 0)
{
comandoSql = @"INSERT INTO EEF100( ANO
,MES
,TP_EVENTO
,TPINI
,TPDECLARADO
,NIDECLARADO
,NOMEDECLARADO
,DATANASC
,ENDERECOLIVRE
,PAIS
,TPCONTA
,SUBTPCONTA
,TPNUMCONTA
,NUMCONTA
,TPRELACAODECLARADO
,TOTCREDITOS
,TOTDEBITOS
,TOTCREDITOSMESMATITULARIDADE
,TOTDEBITOSMESMATITULARIDADE
,VLULTDIA
,TPPAGTO
,TOTPAGTOACUM
,TOTCOMPRAS
,TOTVENDAS
,TOTTRANSFERENCIAS
,NRRECIBO
,ID_EVENTO
,REPORTAVEL
,PAISNACIONALIDADE)
VALUES(" + modMovOper.ANO+
"," + modMovOper.MES+
",'M'"+
",'" + modMovOper.TPINI + "'" +
",'" + modMovOper.TPDECLARADO + "'" +
",'" + modMovOper.NIDECLARADO + "'" +
",'" + modMovOper.NOMEDECLARADO + "'" +
",'" + modMovOper.DATANASC + "'" +
",'" + modMovOper.ENDERECOLIVRE + "'" +
",'" + modMovOper.PAIS + "'" +
",'" + modMovOper.TPCONTA + "'" +
",'" + modMovOper.SUBTPCONTA + "'" +
",'" + modMovOper.TPNUMCONTA + "'" +
",'" + modMovOper.NUMCONTA + "'" +
",'" + modMovOper.TPRELACAODECLARADO + "'" +
"," + FloatToStr(modMovOper.TOTCREDITOS.ToString(),2) +
"," + FloatToStr(modMovOper.TOTDEBITOS.ToString(), 2) +
"," + FloatToStr(modMovOper.TOTCREDITOSMESMATITULARIDADE.ToString(), 2) +
"," + FloatToStr(modMovOper.TOTDEBITOSMESMATITULARIDADE.ToString(), 2) +
"," + FloatToStr(modMovOper.VLULTDIA.ToString(), 2) +
",'" + modMovOper.TPPAGTO + "'" +
"," + FloatToStr(modMovOper.TOTPAGTOACUM.ToString(), 2) +
"," + FloatToStr(modMovOper.TOTCOMPRAS.ToString(), 2) +
"," + FloatToStr(modMovOper.TOTVENDAS.ToString(), 2) +
"," + FloatToStr(modMovOper.TOTTRANSFERENCIAS.ToString(), 2) +
",'" + modMovOper.NRRECIBO + "'" +
",'" + modMovOper.ID_EVENTO + "'" +
",'" + modMovOper.REPORTAVEL + "'" +
",'" + modMovOper.PAISNACIONALIDADE + "'" +
")";
}
else
{
comandoSql = "";
}
SQLiteCommand ler = new SQLiteCommand(comandoSql, conn);
ler.ExecuteNonQuery();
if (modMovOper.ID == 0)
{
comandoSql = @"SELECT MAX(ID) AS ID_MAX FROM EEF100";
SQLiteCommand ler1 = new SQLiteCommand(comandoSql, conn);
SQLiteDataReader dr1 = ler1.ExecuteReader();
dr1.Read();
String idEvento = "ID" + dr1["ID_MAX"].ToString().Trim().PadLeft(11, '0');
comandoSql = @"UPDATE EEF100 SET ID_EVENTO ='"+ idEvento + "' WHERE ID ="+ Convert.ToInt32(dr1["ID_MAX"].ToString());
SQLiteCommand ler2 = new SQLiteCommand(comandoSql, conn);
ler2.ExecuteNonQuery();
dr1.Close();
}
conn.Close();
}
}
public String ImportarOperacaoMov()
{
String retorno = String.Empty;
String conexaoString = String.Empty;
EF_MOV_OPER movOper = new EF_MOV_OPER();
using (SQLiteConnection conn = new SQLiteConnection(conexaoSqlLite()))
{
conn.Open();
comandoSql = @"SELECT BANCO, CONN_STRING FROM TEF801 WHERE BANCO = 3";
SQLiteCommand ler = new SQLiteCommand(comandoSql, conn);
SQLiteDataReader dr = ler.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
connSql = dr["CONN_STRING"].ToString();
}
}
dr.Close();
comandoSql = @"SELECT BANCO, CONN_STRING FROM TEF801 WHERE BANCO = 4";
SQLiteCommand ler1 = new SQLiteCommand(comandoSql, conn);
SQLiteDataReader dr1 = ler1.ExecuteReader();
if (dr1.HasRows)
{
while (dr1.Read())
{
connOracle = dr1["CONN_STRING"].ToString();
}
}
dr1.Close();
conn.Close();
}
if (connSql == String.Empty && connOracle == String.Empty)
{
retorno = "Nenhuma conexão com banco foi cadastrado";
}
else
{
if (connSql != String.Empty)
{
using (SqlConnection conn = new SqlConnection(connSql))
{
conn.Open();
comandoSql = @"SELECT ID
,DT_INICIO
,DT_FIM
,ANO
,MES
,TP_EVENTO
,TPINI
,TPDECLARADO
,NIDECLARADO
,NOMEDECLARADO
,DATANASC
,ENDERECOLIVRE
,PAIS
,TPCONTA
,SUBTPCONTA
,TPNUMCONTA
,NUMCONTA
,TPRELACAODECLARADO
,ISNULL(TOTCREDITOS,0) TOTCREDITOS
,ISNULL(TOTDEBITOS,0) TOTDEBITOS
,ISNULL(TOTCREDITOSMESMATITULARIDADE,0) TOTCREDITOSMESMATITULARIDADE
,ISNULL(TOTDEBITOSMESMATITULARIDADE,0) TOTDEBITOSMESMATITULARIDADE
,ISNULL(VLULTDIA,0) VLULTDIA
,ISNULL(TPPAGTO,0) TPPAGTO
,ISNULL(TOTPAGTOACUM,0) TOTPAGTOACUM
,ISNULL(TOTCOMPRAS,0) TOTCOMPRAS
,ISNULL(TOTVENDAS,0) TOTVENDAS
,ISNULL(TOTTRANSFERENCIAS,0) TOTTRANSFERENCIAS
,NRRECIBO
,ID_EVENTO
,REPORTAVEL
,PAISNACIONALIDADE
FROM FATCA_MOVOPFIN WHERE TP_EVENTO = 'M' ORDER BY MES";
SqlCommand ler = new SqlCommand(comandoSql, conn);
SqlDataReader dr = ler.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
movOper.ID = 0;
movOper.DT_INICIO = dr["DT_INICIO"].ToString();
movOper.DT_FIM = dr["DT_FIM"].ToString();
movOper.ANO = Convert.ToInt32(dr["ANO"].ToString());
movOper.MES = Convert.ToInt32(dr["MES"].ToString());
movOper.TP_EVENTO = dr["TP_EVENTO"].ToString();
movOper.TPINI = dr["TPINI"].ToString();
movOper.TPDECLARADO = dr["TPDECLARADO"].ToString();
movOper.NIDECLARADO = dr["NIDECLARADO"].ToString();
movOper.NOMEDECLARADO = dr["NOMEDECLARADO"].ToString();
movOper.DATANASC = dr["DATANASC"].ToString();
movOper.ENDERECOLIVRE = dr["ENDERECOLIVRE"].ToString();
movOper.PAIS = dr["PAIS"].ToString();
movOper.TPCONTA = dr["TPCONTA"].ToString();
movOper.SUBTPCONTA = dr["SUBTPCONTA"].ToString();
movOper.TPNUMCONTA = dr["TPNUMCONTA"].ToString();
movOper.NUMCONTA = dr["NUMCONTA"].ToString();
movOper.TPRELACAODECLARADO = dr["TPRELACAODECLARADO"].ToString();
movOper.TOTCREDITOS = Convert.ToDecimal(dr["TOTCREDITOS"].ToString());
movOper.TOTDEBITOS = Convert.ToDecimal(dr["TOTDEBITOS"].ToString());
movOper.TOTCREDITOSMESMATITULARIDADE = Convert.ToDecimal(dr["TOTCREDITOSMESMATITULARIDADE"].ToString());
movOper.TOTDEBITOSMESMATITULARIDADE = Convert.ToDecimal(dr["TOTDEBITOSMESMATITULARIDADE"].ToString());
movOper.VLULTDIA = Convert.ToDecimal(dr["VLULTDIA"].ToString());
movOper.TPPAGTO = Convert.ToInt32(dr["TPPAGTO"].ToString());
movOper.TOTPAGTOACUM = Convert.ToDecimal(dr["TOTPAGTOACUM"].ToString());
movOper.TOTCOMPRAS = Convert.ToDecimal(dr["TOTCOMPRAS"].ToString());
movOper.TOTVENDAS = Convert.ToDecimal(dr["TOTVENDAS"].ToString());
movOper.TOTTRANSFERENCIAS = Convert.ToDecimal(dr["TOTTRANSFERENCIAS"].ToString());
movOper.NRRECIBO = dr["NRRECIBO"].ToString();
movOper.ID_EVENTO = dr["ID_EVENTO"].ToString();
movOper.REPORTAVEL = dr["REPORTAVEL"].ToString();
movOper.PAISNACIONALIDADE = dr["PAISNACIONALIDADE"].ToString();
GravaOperacao(movOper);
}
}
conn.Close();
}
}
if (connOracle != String.Empty)
{
using (OracleConnection conn = new OracleConnection(connOracle))
{
conn.Open();
conn.Close();
}
}
}
return retorno;
}
public frmEF_Movimento_Operacoes_Novo(EF_MOV_OPER modMovOper)
{
InitializeComponent();
modMovOperAux = modMovOper;
}
