Example #1
0
        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);
            }
        }
Example #2
0
        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);
            }
        }