public Operation()
{
MyLogger.Log("------------------------------------------------------------------------------------------------");
MyLogger.Log(DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
MyLogger.Log("Iniciando Sincronização...");
//button4.BackColor = Color.Gray;
//button4.Refresh();
MyLogger.Log("Conectando no SAP...");
sap = new SAP();
if (sap.Error == false)
{
if (sap.ExportPayments())
{
MyLogger.Log("Aguardando novas iterações..." + Environment.NewLine);
}
else
{
MyLogger.Log("Exportação não efetuada!");
}
}
else
{
MyLogger.Log("Exportação não efetuada!");
}
//textResult.Text = "SAP Conectado" + Environment.NewLine;
}
public bool WriteMessageOnQueue(string message, string queueName, IConnection connection)
{
try
{
using (var channel = connection.CreateModel())
{
///trocar casale.input para queueName
//channel.ExchangeDeclare("casale.input", "fanout");
channel.BasicPublish(queueName, "", null, Encoding.ASCII.GetBytes(message));
}
VerifySends("55fa2bd1-5f03-4153-8102-addaa44d6c3a");
MyLogger.Log("Mensagem enviada à Liber com sucesso!");
return(true);
}
catch (Exception ex)
{
MyLogger.Log("Error 506 - " + ex.Message);
MessageBox.Show("Error 506 - " + ex.Message);
return(false);
}
}
public Request()
{
MyLogger.Log("------------------------------------------------------------------------------------------------");
MyLogger.Log(DateTime.Now.ToShortDateString() + " - " + DateTime.Now.ToShortTimeString());
MyLogger.Log("Liber está enviando informações...");
LiberRabbit listen = new LiberRabbit();
listen.Connect();
String req;
do
{
req = listen.ListenQueueRequest();//producao
if (!String.IsNullOrEmpty(req))
{
//para efeito de teste
//req = "{ " + "\n";//teste
//req = req + " \"payee\": { " + "\n";//teste
//req = req + " \"id_type\": \"CNPJ\", " + "\n";//teste
//req = req + " \"id\": \"26961015000101\", " + "\n";//teste
//req = req + " \"name\": \"Nome do Fornecedor Ltda\" " + "\n";//teste
//req = req + " }, " + "\n";//teste
//req = req + " \"payer_doc_id\": \"238016-0\", " + "\n";//teste
//req = req + " \"value\": \"892.50\", " + "\n";//teste
//req = req + " \"currency\": \"BRL\", " + "\n";//teste
//req = req + " \"due_date\": \"2018-11-29\" " + "\n";//teste
//req = req + "}";//teste
dynamic jsonObj = JsonConvert.DeserializeObject(req);
String payer_doc_id = jsonObj["payer_doc_id"];
double value = Convert.ToDouble(jsonObj["value"]);
String aux = jsonObj["due_date"];
DateTime due_date = DateTime.ParseExact(aux, "yyyy-MM-dd",
System.Globalization.CultureInfo.InvariantCulture);
InvoiceRequest requested = new InvoiceRequest(payer_doc_id, value, due_date);
requests.Add(requested);
listen.Connect();
}
//req = null;//teste
}while (!String.IsNullOrEmpty(req));
if (requests.Count > 0)
{
SAP connSAP = new SAP();
connSAP.InvoiceRequested(requests);
}
else
{
MyLogger.Log("Sem títulos para atualizar");
}
MyLogger.Log("------------------------------------------------------------------------------------------------");
MyLogger.Log("Aguardando novas iterações...");
}
private void VerifySends(string message)
{
String queuename = "invoice.batch.imported";
String json;
IModel channel = conn.CreateModel();
bool noAck = false;
BasicGetResult result = channel.BasicGet(queuename, noAck);
if (result == null)
{
MyLogger.Log("No message available at this time.");
channel.Close();
}
else
{
IBasicProperties props = result.BasicProperties;
byte[] body = result.Body;
json = Encoding.UTF8.GetString(body);
// acknowledge receipt of the message
channel.BasicAck(result.DeliveryTag, false);
MessageBox.Show(json);
MyLogger.Log("Uma mensagem lida");
channel.Close();
}
}
private void VerifyInvoice(InvoiceRequest r)
{
SAPbobsCOM.JournalEntries oJou;
SAPbobsCOM.JournalEntries_Lines oJouLine;
Boolean retcode;
int transId, lineId, intretcode;
try
{
oJou = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries); //todas as transaçõesefetuadas
string[] array = r.payer_doc_id.Split(new char[] { '-' }, 2);
transId = Convert.ToInt32(array.GetValue(0).ToString());
lineId = Convert.ToInt32(array.GetValue(1).ToString());
retcode = oJou.GetByKey(transId);
if (!retcode)
{
MyLogger.Log("Erro ao encontrar titulo " + transId.ToString());
MessageBox.Show("Erro ao encontrar titulo " + transId.ToString());
return;
}
oJouLine = oJou.Lines;
oJouLine.SetCurrentLine(lineId);
if (String.Compare(r.due_date.ToShortDateString(), oJouLine.DueDate.ToShortDateString()) != 0)//verificar vencimento
{
MyLogger.Log("A parcela " + lineId + " do título " + transId + " não está com a mesma data de vencimento. O título não está aprovado para negociação");
r.Invalidate();
}
else if (r.value != oJouLine.Credit)//verificar valor do titulo
{
MyLogger.Log("A parcela " + lineId + " do título " + transId + " não está com a mesma data de vencimento. O título não está aprovado para negociação");
r.Invalidate();
}
//else if(1=1)//verificar se o titulo esta com o status confirmado -- não é necessario
//{
// // oJouLine.UserFields.Fields.
//}
else
{
// alterar para status negociado
MyLogger.Log("A parcela " + lineId + " do título " + transId + " está negociada com sucesso!");
oJouLine.UserFields.Fields.Item("U_LB_release").Value = "1";
intretcode = oJou.Update();
if (intretcode != 0)
{
MyLogger.Log(oCompany.GetLastErrorDescription());
}
}
}
catch (Exception ex)
{
MyLogger.Log("Erro 5034 -" + ex.Message);
}
}
private void VerificaDados(InvoiceRequest r, JournalEntries_Lines oJouLine, int lineId, int transId, JournalEntries oJou)
{
//if (DateTime.Compare( r.due_date, oJouLine.DueDate)!=0)
if (r.due_date.CompareTo(oJouLine.DueDate) != 0)
{
r.Invalidate();
MyLogger.Log("A parcela " + lineId + " do título " + transId + " não está com a mesma data de vencimento. O título não está aprovado para negociação");
}
////verificar valor do titulo
///
}
//escuta a fila, recebe um json
public string ListenQueueRequest()
{
try
{
String queueName = extName + ".output";
String json;
IModel channel = conn.CreateModel();
bool noAck = false;
BasicGetResult result = channel.BasicGet(queueName, noAck);
if (result == null)
{
MyLogger.Log("No message available at this time.");
channel.Close();
return(null);
}
else
{
IBasicProperties props = result.BasicProperties;
byte[] body = result.Body;
json = Encoding.UTF8.GetString(body);
// acknowledge receipt of the message
channel.BasicAck(result.DeliveryTag, false);
MessageBox.Show(json);
MyLogger.Log("Uma mensagem lida");
channel.Close();
return(json);
}
}
catch (Exception ex)
{
MyLogger.Log("Erro 409 - Falha na conexao ou busca do titulo - " + ex.Message);
MessageBox.Show("erro 409 - " + ex.Message);
return(String.Empty);
}
//InvoiceRequest request = new InvoiceRequest("abc", "\"transID:166049,lineID:0\"",
// "920,47",
// "BRL",
// "2017-12-04");
//request = JsonTextReader(new StringReader(json));
}
public bool WriteJson(String json, String routingKey)
{
String queueName = extName + ".input";
try
{
return(WriteMessageOnQueue(json, queueName, conn, routingKey));
}
catch (Exception ex)
{
MyLogger.Log("Erro 505 - " + ex.Message);
MessageBox.Show("Erro 505 - " + ex.Message);
return(false);
}
}
internal void InvoiceRequested(List <InvoiceRequest> requests)
{
//selecionar o primeiro titulo
//verificar se o titulo confere os dados com o que está no SAP
//verificar o status
//false - retonna titulo nao disponivel
//verifacar valor e vencimento
//retornar erro
//true - alterar o status do titulo para negociado e retornar informação
//ir para o proximo se é o ultimo sair
MyLogger.Log("Registrando alterações no SAP");
foreach (InvoiceRequest r in requests)
{
VerifyInvoice(r);
}
}
public bool WriteJson(string json)
{
//String queueName = extName + ".input";
String queueName = "invoice.batch.imported";
bool success = true;
try
{
//success = WriteMessageOnQueue(json, queueName, conn);
//VerifyBatch("b27b7cc7-ba8f-49a6-88cf-f0afa14afe93");
//return success;
return(WriteMessageOnQueue(json, queueName, conn));
}
catch (Exception ex)
{
MyLogger.Log("Erro 505 - " + ex.Message);
MessageBox.Show("Erro 505 - " + ex.Message);
return(false);
}
}
public Connection()
{
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
try
{
String path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
myconn = new SQLiteConnection("DataSource=" + path + "\\LiberB1\\LiberB1DB.db;");
myconn.Open();
sqlite_cmd = myconn.CreateCommand();
sqlite_cmd.CommandText = "SELECT [Id],[serverName],[dbName],[dbPass],[SapUser],[SapPass],[IsValid],[ConexaoLiber],[userLiber],[PassLiber],[dbId],[PortLiber],[SQLType] FROM [Connection];";
sqlite_datareader = sqlite_cmd.ExecuteReader();
sqlite_datareader.Read();
if (sqlite_datareader.HasRows)
{
hasConn = true;
}
else
{
hasConn = false;
}
sqlite_datareader.Close();
myconn.Close();
}
catch (Exception ex)
{
MyLogger.Log("Error 502 - " + ex.Message);
MessageBox.Show("Error 502 - " + ex.Message);
hasConn = false;
myconn.Close();
return;
}
}
public SAP()
{
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
String key = "trugLk";
Error = false;
//buscando dados de conexao no banco SQLite
try
{
myconn = new SQLiteConnection("DataSource=" + path + "\\LiberB1\\LiberB1DB.db;");
myconn.Open();
//MyLogger.Log("Conectado ao RabbitMQ");
}
catch (Exception ex)
{
MyLogger.Log("Error 502 - " + ex.Message);
MessageBox.Show("Error 502 - " + ex.Message);
return;
}
sqlite_cmd = myconn.CreateCommand();
sqlite_cmd.CommandText = "SELECT [servername],[dbname],[sapuser],[sappass],[sqltype]" +
"from[Connection]; ";
sqlite_datareader = sqlite_cmd.ExecuteReader();
sqlite_datareader.Read();
//usando os dados de conexao para conectar no SAP
int retCode = -1;
string strMsg;
try
{
oCompany = new SAPbobsCOM.Company();
oCompany.Server = sqlite_datareader.GetString(0);
oCompany.CompanyDB = sqlite_datareader.GetString(1);
switch (sqlite_datareader.GetString(4))
{
case "dst_MSSQL": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL; break;
case "dst_DB_2": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_DB_2; break;
case "dst_SYBASE": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_SYBASE; break;
case "dst_MSSQL2005": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2005; break;
case "dst_MAXDB": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MAXDB; break;
case "dst_MSSQL2008": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008; break;
case "dst_MSSQL2012": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012; break;
case "dst_MSSQL2014": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2014; break;
case "dst_HANADB": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_HANADB; break;
case "dst_MSSQL2016": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2016; break;
case "dst_MSSQL2017": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2017; break;
default:
oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2017;;
break;
}
oCompany.UserName = sqlite_datareader.GetString(2);
oCompany.Password = Cryptography.Decrypt(sqlite_datareader.GetString(3), key);
oCompany.UseTrusted = true;
myconn.Close();
retCode = oCompany.Connect();
if (retCode != 0)
{
strMsg = oCompany.GetLastErrorDescription();
MessageBox.Show(strMsg);
MyLogger.Log("Error 509 - " + strMsg);
Error = true;
return;
}
MyLogger.Log("SAP Conectado!");
//lendo outros parametros
//sqlite_cmd = myconn.CreateCommand();
//sqlite_cmd.CommandText = "SELECT [Id],[QueueName],[OriginDoc],[TimeSAP],[FieldChaveAcesso],[TimeStartSAP],[TimeStopSAP],[Import]"+
// "FROM[Configuration];";
//sqlite_datareader = sqlite_cmd.ExecuteReader();
//sqlite_datareader.Read();
//MessageBox.Show(sqlite_datareader.GetString(7));
}
catch (Exception ex)
{
MyLogger.Log("Error 503 - " + ex.Message);
MessageBox.Show("Error 503 - " + ex.Message);
}
}
public bool Connect()
{
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
String key = "trugLk";
try
{
String path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
myconn = new SQLiteConnection("DataSource=" + path + "\\LiberB1\\LiberB1DB.db;");
myconn.Open();
}
catch (Exception ex)
{
MessageBox.Show("Error 501 - " + ex.Message);
}
sqlite_cmd = myconn.CreateCommand();
sqlite_cmd.CommandText = "SELECT [ConexaoLiber],[userLiber],[PassLiber],[PortLiber]" +
"from[Connection]; ";
sqlite_datareader = sqlite_cmd.ExecuteReader();
sqlite_datareader.Read();
endpoint = sqlite_datareader.GetString(0);
LiberUser = sqlite_datareader.GetString(1);
LiberPass = Cryptography.Decrypt(sqlite_datareader.GetString(2), key);
LiberPort = sqlite_datareader.GetString(3);
myconn.Close();
//MessageBox.Show(endpoint + " ---- " + LiberUser + " ---- " + LiberPass + " ---- " + LiberPort);
//testar a conexao com o rabbit
ConnectionFactory factory = new ConnectionFactory
{
UserName = LiberUser,
Password = LiberPass,
VirtualHost = "/",
Port = System.Convert.ToInt32(LiberPort),
HostName = endpoint
};
try
{
conn = factory.CreateConnection();
//IModel channel = conn.CreateModel();
//MessageBox.Show("Conectado com sucesso!");
//channel.Close();
//conn.Close();
//MyLogger.Log("Rabbit conectado");
return(true);
}
catch (Exception ex)
{
MyLogger.Log("Falha na conexão - " + ex);
MessageBox.Show("Falha na conexão - " + ex);
return(false);
}
}
public Configuration()
{
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
try
{
String path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
myconn = new SQLiteConnection("DataSource=" + path + "\\LiberB1\\LiberB1DB.db;");
myconn.Open();
}
catch (Exception ex)
{
MyLogger.Log("Error 502 - " + ex.Message);
MessageBox.Show("Error 502 - " + ex.Message);
}
sqlite_cmd = myconn.CreateCommand();
sqlite_cmd.CommandText = "SELECT [Id],[QueueName],[OriginDoc],[TimeSAP],[FieldChaveAcesso],[TimeStartSAP]" +
",[TimeStopSAP],[Import] " +
"FROM[Configuration];";
sqlite_datareader = sqlite_cmd.ExecuteReader();
sqlite_datareader.Read();
if (sqlite_datareader.HasRows)
{
hascfg = true;
QueueName = sqlite_datareader.GetString(sqlite_datareader.GetOrdinal("QueueName")) ?? "";
if (sqlite_datareader.IsDBNull(sqlite_datareader.GetOrdinal("OriginDoc")))
{
OriginDoc = string.Empty;
}
else
{
OriginDoc = sqlite_datareader.GetString(sqlite_datareader.GetOrdinal("OriginDoc")) ?? "";
}
if (sqlite_datareader.IsDBNull(sqlite_datareader.GetOrdinal("TimeSAP")))
{
timesap = -1;
}
else
{
timesap = sqlite_datareader.GetInt32(sqlite_datareader.GetOrdinal("TimeSAP"));
}
if (sqlite_datareader.IsDBNull(sqlite_datareader.GetOrdinal("FieldChaveAcesso")))
{
FieldChaveAcesso = string.Empty;
}
else
{
FieldChaveAcesso = sqlite_datareader.GetString(sqlite_datareader.GetOrdinal("FieldChaveAcesso")) ?? "";
}
if (sqlite_datareader.IsDBNull(sqlite_datareader.GetOrdinal("TimeStartSAP")))
{
TimeStartSAP = string.Empty;
}
else
{
TimeStartSAP = sqlite_datareader.GetString(5) ?? "";
}
if (sqlite_datareader.IsDBNull(sqlite_datareader.GetOrdinal("TimeStopSAP")))
{
TimeStopSAP = string.Empty;
}
else
{
TimeStopSAP = sqlite_datareader.GetString(sqlite_datareader.GetOrdinal("TimeStopSAP")) ?? "";
}
if (sqlite_datareader.GetInt32(sqlite_datareader.GetOrdinal("Import")) == 0)
{
import = false;
}
else
{
import = true;
}
}
else
{
hascfg = false;
}
}
public bool ExportPayments(/*DateTime lasttime*/)
{
MyLogger.Log("Exportando títulos...");
try
{
SAPbobsCOM.Recordset oRecordset =
this.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
String query = "";
query = query + "SELECT DISTINCT " + "\n";
query = query + " REPLACE(REPLACE(REPLACE ( T9.cnpjFornecedor , '.' , '' ), '/',''),'-','') as id, "+ "\n";
query = query + " t9.type id_type, "+ "\n";
query = query + " T2.[CardName] legal_name, "+ "\n";
query = query + " t2.E_Mail as email, "+ "\n";
query = query + " t2.Phone1 + ' - ' + t2.Phone2 + ' - ' +t2.Cellular as phone, "+ "\n";
query = query + " t2.CardCode, "+ "\n";
query = query + " T1.[BalDueCred] - ISNULL( "+ "\n";
query = query + " (SELECT SUM(TT7.WTAMNT/ISNULL(TT8.INSTNUM,1)) "+ "\n";
query = query + " FROM JDT1 TT1 "+ "\n";
query = query + " INNER JOIN OCRD TT2 ON TT1.ShortName = TT2.CardCode "+ "\n";
query = query + " LEFT JOIN VPM2 TT3 ON TT1.TransId = TT3.DocNum AND TT3.InvType = 18 "+ "\n";
query = query + " INNER JOIN OPCH TT6 ON TT1.SOURCEID = TT6.DOCNUM AND ( TT1.[TransType] <> 204 and TT1.[TransType] <> 30) "+ "\n";
query = query + " INNER JOIN PCH5 TT7 ON TT7.ABSENTRY = TT6.DOCENTRY AND ( TT1.[TransType] <> 204 and TT1.[TransType] <> 30) "+ "\n";
query = query + " INNER JOIN OCTG TT8 ON TT8.GROUPNUM = TT6.GROUPNUM AND ( TT1.[TransType] <> 204 and TT1.[TransType] <> 30) "+ "\n";
query = query + " WHERE Substring(TT1.[ShortName],1,1) = 'F' "+ "\n";
query = query + " AND (TT1.[TransType] = 30 or TT1.[TransType]=18 or TT1.[TransType]=204) "+ "\n";
query = query + " AND SUBSTRING (TT1.[Account] ,1,1)= '2' "+ "\n";
query = query + " AND TT1.[Credit]> 0 "+ "\n";
query = query + " AND TT1.BALDUECRED <>0 "+ "\n";
query = query + " AND TT1.[ShortName] = T1.[ShortName] "+ "\n";
query = query + " AND TT1.[TransType] = T1.[TransType] "+ "\n";
query = query + " AND TT1.[TransId]= T1.[TransId] "+ "\n";
query = query + " AND TT7.[Category] = 'P' "+ "\n";
query = query + " --AND TT1.[DueDate]>= @dtin and TT1.[DueDate]<= @dtfi "+ "\n";
query = query + " )/(ISNULL(T8.INSTNUM,1)),0) as value, "+ "\n";
query = query + " T1.[Credit] as original_value, "+ "\n";
query = query + " substring(CONVERT(varchar,T1.[DueDate],126),0,11) AS due_date, "+ "\n";
query = query + " substring(CONVERT(varchar,t1.refdate ,126),0,11) as issue_date, "+ "\n";
query = query + " T6.Serial as tax_doc_id, "+ "\n";
query = query + " t6.SeriesStr as tax_doc_sec_id, "+ "\n";
query = query + " isnull(t6.U_chaveacesso,'') as tax_doc_key, "+ "\n";
query = query + " [Ref3Line] installment, "+ "\n";
query = query + " (select top 1 isnull(c1.ISOCurrCod,(select top 1 z1.ISOCurrCod from OADM z0 left join OCRN z1 on z0.SysCurrncy = z1.CurrCode)) "+ "\n";
query = query + " from JDT1 C0 left join OCRN C1 on c0.FCCurrency = c1.CurrCode ) as currency, "+ "\n";
query = query + " t1.TransId, "+ "\n";
query = query + " t1.Line_ID, "+ "\n";
query = query + " case when (t1.U_lb_release = 1) then 'true' else 'false' end as release "+ "\n";
query = query + " --'lastchange' as lastchange "+ "\n";
query = query + " " + "\n";
query = query + " FROM JDT1 T1 "+ "\n";
query = query + " INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode "+ "\n";
query = query + " LEFT JOIN VPM2 T3 ON T1.TransId = T3.DocNum AND (T3.InvType <> 30 and T3.InvType <> 18 and T3.InvType <> 204 ) "+ "\n";
query = query + " LEFT JOIN OPCH T6 ON T1.SOURCEID = T6.DocEntry AND ( T1.[TransType] <> 204 and T1.[TransType] <> 30) "+ "\n";
query = query + " LEFT JOIN PCH5 T7 ON T7.ABSENTRY = T6.DOCENTRY AND ( T1.[TransType] <> 204 and T1.[TransType] <> 30) "+ "\n";
query = query + " LEFT JOIN OCTG T8 ON T8.GROUPNUM = T6.GROUPNUM AND ( T1.[TransType] <> 204 and T1.[TransType] <> 30) "+ "\n";
query = query + " LEFT JOIN (SELECT max( ISNULL (T0.TaxId0 , T0.TaxId4) ) as cnpjFornecedor ,T0.CARDCODE, case when T0.TaxId0 is not null then 'CNPJ' else 'CPF' end as type "+ "\n";
query = query + " FROM CRD7 T0 "+ "\n";
query = query + " where t0.CardCode like 'F%' and (T0.TaxId0 is not null or T0.TaxId4 is not null) "+ "\n";
query = query + " GROUP BY T0.CARDCODE, T0.TaxId0) as T9 ON T9.CARDCODE = T2.CardCode "+ "\n";
query = query + " " + "\n";
query = query + " WHERE "+ "\n";
query = query + " --(T9.cnpjFornecedor IS NOT NULL AND T9.cnpjFornecedor !='') AND "+ "\n";
query = query + " Substring(T1.[ShortName],1,1) = 'F' "+ "\n";
query = query + " AND T1.[TransType]=18 "+ "\n";
query = query + " AND SUBSTRING (T1.[Account] ,1,1)= '2' "+ "\n";
query = query + " AND T1.[Credit]> 0 "+ "\n";
query = query + " AND T1.BALDUECRED <>0 "+ "\n";
query = query + " --AND T1.[DueDate] between @dtin AND @dtfi "+ "\n";
query = query + " --and t1.TransId = 221672 "+ "\n";
query = query + " " + "\n";
query = query + " --ORDER BY emailContatoFornecedor "+ "\n";
query = query + " --ORDER BY DueDater";
oRecordset.DoQuery(query);
List <TitulosSAP> titulos = new List <TitulosSAP>();
while (!oRecordset.EoF)
{
List <Contacts> conts = new List <Contacts>();
SAPbobsCOM.Recordset oRecordset2 =
this.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
String varname1 = "";
varname1 = varname1 + "select *, ROW_NUMBER() over(partition by cardcode order by sort, email,phone) as rownumber " + "\n";
varname1 = varname1 + " from( " + "\n";
varname1 = varname1 + " select distinct T1.CardCode, "+ "\n";
varname1 = varname1 + " isnull(t0.Name,'') +' - '+ isnull(t0.FirstName,'') as name, "+ "\n";
varname1 = varname1 + " isnull(t0.E_MailL,t1.E_Mail) as email, "+ "\n";
varname1 = varname1 + " ISNULL(t0.Tel1+ '-','') + ISNULL(t0.Tel2+ ' / ','') + "+ "\n";
varname1 = varname1 + " isnull(t1.Phone1+ '-', '')+ISNULL(t1.Phone2,'') as phone, "+ "\n";
varname1 = varname1 + " case when isnull(t0.E_MailL,t1.E_Mail) is null then '3' else '1' end sort "+ "\n";
varname1 = varname1 + " from OCRD t1 left join OCPR t0 on t0.CardCode = t1.CardCode "+ "\n";
varname1 = varname1 + " "+ "\n";
varname1 = varname1 + " where t0.Name like ('%fin%') or t0.E_MailL like ('%fin%') "+ "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " UNION "+ "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " select distinct T1.CardCode, "+ "\n";
varname1 = varname1 + " isnull(t0.Name,'') +' - '+ isnull(t0.FirstName,''), "+ "\n";
varname1 = varname1 + " isnull(t0.E_MailL,t1.E_Mail), "+ "\n";
varname1 = varname1 + " ISNULL(t0.Tel1+ '-','') + ISNULL(t0.Tel2+ ' / ','') + "+ "\n";
varname1 = varname1 + " isnull(t1.Phone1+ '-', '')+ISNULL(t1.Phone2,''), "+ "\n";
varname1 = varname1 + " case when isnull(t0.E_MailL,t1.E_Mail) is null then '3' else '2' end"+ "\n";
varname1 = varname1 + " "+ "\n";
varname1 = varname1 + " from OCRD t1 left join OCPR t0 on t0.CardCode = t1.CardCode "+ "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " ) t0 " + "\n";
varname1 = varname1 + " where CardCode = '" + oRecordset.Fields.Item("cardcode").Value + "' " + "\n";
varname1 = varname1 + " order by cardcode, sort, rownumber";
oRecordset2.DoQuery(varname1);
while (!oRecordset2.EoF)
{
Contacts cont = new Contacts(oRecordset2.Fields.Item("name").Value + "",
oRecordset2.Fields.Item("email").Value + "",
oRecordset2.Fields.Item("phone").Value + ""
);
conts.Add(cont);
oRecordset2.MoveNext();
}
Payees payee = new Payees(oRecordset.Fields.Item("id_type").Value + "",
oRecordset.Fields.Item("id").Value + "",
oRecordset.Fields.Item("legal_name").Value + "",
null, //trade_name
oRecordset.Fields.Item("email").Value + "",
oRecordset.Fields.Item("phone").Value + "",
null, //accounts
conts
);
//inserindo CNPJ sacado
SAPbobsCOM.Recordset oRecordset3 =
this.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecordset3.DoQuery("select top 1 REPLACE(REPLACE(REPLACE ( TaxIdNum , '.' , '' ), '/',''),'-','') as payer from OADM");
//criando o pay_doc_id
String payer_doc_id = oRecordset.Fields.Item("transID").Value + "-" + oRecordset.Fields.Item("Line_ID").Value;
TitulosSAP titulo = new TitulosSAP(
oRecordset3.Fields.Item("payer").Value + "",
payee,
oRecordset.Fields.Item("value").Value + "",
oRecordset.Fields.Item("original_value").Value + "",
oRecordset.Fields.Item("currency").Value + "",
oRecordset.Fields.Item("due_date").Value + "",
oRecordset.Fields.Item("issue_date").Value + "",
oRecordset.Fields.Item("tax_doc_id").Value + "",
oRecordset.Fields.Item("tax_doc_sec_id").Value + "",
oRecordset.Fields.Item("tax_doc_key").Value + "",
oRecordset.Fields.Item("installment").Value + "",
payer_doc_id,
String.Equals(oRecordset.Fields.Item("release").Value + "", "true"),
//inserindo o ref_number
oRecordset.Fields.Item("tax_doc_id").Value + "",
oRecordset3.Fields.Item("payer").Value + ""
);
titulos.Add(titulo);
oRecordset.MoveNext();
}
String invoice = JsonConvert.SerializeObject(titulos, Formatting.Indented);
json = "{\"batch\": \"" + System.Guid.NewGuid() + "\", \"invoices\":" + invoice + "}";
System.IO.File.WriteAllText(path + "\\LiberB1\\json.txt", json);
LiberRabbit rabbitsend = new LiberRabbit();
rabbitsend.Connect();
oCompany.Disconnect();
MyLogger.Log("Usuário SAP desconectado.");
//escrever: SAP desconectador usuário "++"desconectado
MyLogger.Log("Escrito na fila");
return(rabbitsend.WriteJson(json));
}
catch (Exception ex)
{
MyLogger.Log("Error 508 - " + ex);
return(false);
}
}
private void VerifyInvoice(InvoiceRequest r)
{
SAPbobsCOM.JournalEntries oJou;
SAPbobsCOM.JournalEntries_Lines oJouLine;
Boolean retcode;
int transId, lineId, intretcode;
try
{
oJou = oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oJournalEntries); //todas as transaçõesefetuadas
string[] array = r.payer_doc_id.Split(new char[] { '-' }, 2);
transId = Convert.ToInt32(array.GetValue(0).ToString());
lineId = Convert.ToInt32(array.GetValue(1).ToString());
retcode = oJou.GetByKey(transId);
if (!retcode)
{
MyLogger.Log("Erro ao encontrar titulo " + transId.ToString());
//MessageBox.Show("Erro ao encontrar titulo " + transId.ToString());
return;
}
oJouLine = oJou.Lines;
oJouLine.SetCurrentLine(lineId);
VerificaDados(r, oJouLine, lineId, transId, oJou);
////verificar valor do titulo
///
if (r.value != oJouLine.Credit)
{
MyLogger.Log("A parcela " + lineId + " do título " + transId + " não está com o mesmo valor do negociado. O título não está aprovado para negociação");
r.Invalidate();
}
//neste momento deve-se comparar o valor do titulo que está no rabbit com o valor calculado descontando as devolucoes e também as conciliações
if (r.valid)
{
// alterar para status negociado
MyLogger.Log("A parcela " + lineId + " do título " + transId + " está negociada com sucesso!");
oJouLine.UserFields.Fields.Item("U_Lb_release").Value = "2";
//campo para atualizar os dados de pagamento
//oJouLine.UserFields.Fields.Item("U_LB_Observacao").Value =;
intretcode = oJou.Update();
if (intretcode != 0)
{
MyLogger.Log(oCompany.GetLastErrorDescription());
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(oJou);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oJouLine);
}
catch (Exception ex)
{
MyLogger.Log("Erro 5034 -" + ex.Message);
}
}
public SAP()
{
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
String key = "trugLk";
//buscando dados de conexao no banco SQLite
try
{
String path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
myconn = new SQLiteConnection("DataSource=" + path + "\\LiberB1\\LiberB1DB.db;");
myconn.Open();
//MyLogger.Log("Conectado ao RabbitMQ");
}
catch (Exception ex)
{
MyLogger.Log("Error 509 - " + ex.Message);
MessageBox.Show("Error 509 - " + ex.Message);
return;
}
sqlite_cmd = myconn.CreateCommand();
sqlite_cmd.CommandText = "SELECT [servername],[dbname],[sapuser],[sappass],[sqltype],[dbId],[dbPass]" +
"from[Connection]; ";
sqlite_datareader = sqlite_cmd.ExecuteReader();
sqlite_datareader.Read();
//usando os dados de conexao para conectar no SAP
int retCode = -1;
string strMsg;
try
{
oCompany = new SAPbobsCOM.Company();
oCompany.Server = sqlite_datareader.GetString(0);
oCompany.CompanyDB = sqlite_datareader.GetString(1);
switch (sqlite_datareader.GetString(4))
{
case "dst_MSSQL": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL; break;
case "dst_DB_2": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_DB_2; break;
case "dst_SYBASE": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_SYBASE; break;
case "dst_MSSQL2005": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2005; break;
case "dst_MAXDB": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MAXDB; break;
case "dst_MSSQL2008": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008; break;
case "dst_MSSQL2012": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012; break;
case "dst_MSSQL2014": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2014; break;
case "dst_HANADB": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_HANADB; break;
case "dst_MSSQL2016": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2016; break;
case "dst_MSSQL2017": oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2017; break;
default:
oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2017;;
break;
}
oCompany.UserName = sqlite_datareader.GetString(2);
oCompany.Password = Cryptography.Decrypt(sqlite_datareader.GetString(3), key);
oCompany.UseTrusted = false;
oCompany.DbUserName = sqlite_datareader.GetString(5);
oCompany.DbPassword = Cryptography.Decrypt(sqlite_datareader.GetString(6), key);
myconn.Close();
retCode = oCompany.Connect();
if (retCode != 0)
{
strMsg = oCompany.GetLastErrorDescription();
MyLogger.Log("Não Conectado, verifique as configurações de conexão");
this.connect = false;
}
else
{
MyLogger.Log("SAP Conectado!");
this.connect = true;
}
}
catch (Exception ex)
{
MyLogger.Log("Error 504 - " + ex.Message);
MessageBox.Show("Error 504 - " + ex.Message);
}
}
public bool ExportPayments(/*DateTime lasttime*/)
{
MyLogger.Log("Exportando títulos...");
try
{
SAPbobsCOM.Recordset oRecordset =
this.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
String query = "";
query = query + "select * from dbo.[LB_titulos]";
oRecordset.DoQuery(query);
List <TitulosSAP> titulos = new List <TitulosSAP>();
while (!oRecordset.EoF)
{
List <Contacts> conts = new List <Contacts>();
SAPbobsCOM.Recordset oRecordset2 =
this.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
String varname1 = "";
varname1 = varname1 + "select *, ROW_NUMBER() over(partition by cardcode order by sort, email,phone) as rownumber " + "\n";
varname1 = varname1 + " from( " + "\n";
varname1 = varname1 + " select distinct T1.CardCode, "+ "\n";
varname1 = varname1 + " isnull(t0.Name,'') +' - '+ isnull(t0.FirstName,'') as name, "+ "\n";
varname1 = varname1 + " isnull(t0.E_MailL,t1.E_Mail) as email, "+ "\n";
varname1 = varname1 + " ISNULL(t0.Tel1+ '-','') + ISNULL(t0.Tel2+ ' / ','') + "+ "\n";
varname1 = varname1 + " isnull(t1.Phone1+ '-', '')+ISNULL(t1.Phone2,'') as phone, "+ "\n";
varname1 = varname1 + " case when isnull(t0.E_MailL,t1.E_Mail) is null then '3' else '1' end sort "+ "\n";
varname1 = varname1 + " from OCRD t1 left join OCPR t0 on t0.CardCode = t1.CardCode "+ "\n";
varname1 = varname1 + " "+ "\n";
varname1 = varname1 + " where t0.Name like ('%fin%') or t0.E_MailL like ('%fin%') "+ "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " UNION "+ "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " select distinct T1.CardCode, "+ "\n";
varname1 = varname1 + " isnull(t0.Name,'') +' - '+ isnull(t0.FirstName,''), "+ "\n";
varname1 = varname1 + " isnull(t0.E_MailL,t1.E_Mail), "+ "\n";
varname1 = varname1 + " ISNULL(t0.Tel1+ '-','') + ISNULL(t0.Tel2+ ' / ','') + "+ "\n";
varname1 = varname1 + " isnull(t1.Phone1+ '-', '')+ISNULL(t1.Phone2,''), "+ "\n";
varname1 = varname1 + " case when isnull(t0.E_MailL,t1.E_Mail) is null then '3' else '2' end"+ "\n";
varname1 = varname1 + " "+ "\n";
varname1 = varname1 + " from OCRD t1 left join OCPR t0 on t0.CardCode = t1.CardCode "+ "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " " + "\n";
varname1 = varname1 + " ) t0 " + "\n";
varname1 = varname1 + " where CardCode = '" + oRecordset.Fields.Item("cardcode").Value + "' " + "\n";
varname1 = varname1 + " order by cardcode, sort, rownumber";
oRecordset2.DoQuery(varname1);
while (!oRecordset2.EoF)
{
Contacts cont = new Contacts(oRecordset2.Fields.Item("name").Value + "",
oRecordset2.Fields.Item("email").Value + "",
oRecordset2.Fields.Item("phone").Value + ""
);
conts.Add(cont);
oRecordset2.MoveNext();
}
Payees payee = new Payees(oRecordset.Fields.Item("id_type").Value + "",
oRecordset.Fields.Item("id").Value + "",
oRecordset.Fields.Item("legal_name").Value + "",
null, //trade_name
oRecordset.Fields.Item("email").Value + "",
oRecordset.Fields.Item("phone").Value + "",
null, //accounts
conts
);
//inserindo CNPJ sacado
SAPbobsCOM.Recordset oRecordset3 =
this.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset);
oRecordset3.DoQuery("select top 1 REPLACE(REPLACE(REPLACE ( TaxIdNum , '.' , '' ), '/',''),'-','') as payer from OADM");
//criando o pay_doc_id
String payer_doc_id = oRecordset.Fields.Item("transID").Value + "-" + oRecordset.Fields.Item("Line_ID").Value;
TitulosSAP titulo = new TitulosSAP(
oRecordset3.Fields.Item("payer").Value + "",
payee,
oRecordset.Fields.Item("value").Value + "",
oRecordset.Fields.Item("original_value").Value + "",
oRecordset.Fields.Item("currency").Value + "",
oRecordset.Fields.Item("due_date").Value + "",
oRecordset.Fields.Item("issue_date").Value + "",
oRecordset.Fields.Item("tax_doc_id").Value + "",
oRecordset.Fields.Item("tax_doc_sec_id").Value + "",
oRecordset.Fields.Item("tax_doc_key").Value + "",
oRecordset.Fields.Item("installment").Value + "",
payer_doc_id,
String.Equals(oRecordset.Fields.Item("release").Value + "", "true")
);
titulos.Add(titulo);
oRecordset.MoveNext();
}
//String invoice = JsonConvert.SerializeObject(titulos, Formatting.Indented);
String invoice = JsonConvert.SerializeObject(titulos, Formatting.None);
json = "{\"batch\": \"" + System.Guid.NewGuid() + "\", \"invoices\":" + invoice + "}";
System.IO.File.WriteAllText(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\LiberB1\\path.txt", json);
oCompany.Disconnect();
MyLogger.Log("Usuário SAP desconectado.");
LiberRabbit rabbitsend = new LiberRabbit();
rabbitsend.Connect();
rabbitsend.WriteJson(json, "invoice.batch.imported");
MyLogger.Log("Escrito na fila");
return(true);
}
catch (Exception ex)
{
MyLogger.Log("Error 508 - " + ex);
return(false);
}
}