BeWoPlaner/ReportImp/PassgenauUG/Export/DatevExporter.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using BeWo.Data.Access;
using BeWo.Data.Entities;
using BeWo.Service.DCEntityMapper;
using BS.Shared;
using BS.Shared.DataContracts;
using BS.Shared.Extensions;

namespace PassgenauUG.Export
{
    public class DatevExporter
    {

        public static QueryDC CreateQuery(QueryDC query)
        {
            DateTime dt = DateTime.Now;
            DateTime.TryParse(query.Parameter[0].Value.ToString(), out dt);

            String bereich = "";
            if (query.Parameter.Count > 1 && query.Parameter[1].Value != null)
            {
                bereich = query.Parameter[1].Value.ToString();
            }
            DateTime? minInsts = null;
            
            if (query.Parameter.Count > 2 && query.Parameter[2].Value != null)
            {
                DateTime dt2 = DateTime.MinValue;
                if (DateTime.TryParse(query.Parameter[2].Value.ToString(), out dt2))
                {
                    minInsts = dt2;
                }
            }
            query.FileName = String.Format("Datev Export {0:MM}-{0:yyyy}.csv", dt);
            query.QueryResult = GetAbrechnungenString(dt, bereich, minInsts);
            
            
            return query;
        }


        

        public static String GetAbrechnungenString(DateTime date, String bereich, DateTime? minInsts)
        {
            //Bereiche:
            //queryDc.Parameter[1].CustomValues.Add("Ambulant betreutes Wohnen");
            //queryDc.Parameter[1].CustomValues.Add("Begleitende Elternschaft");
            //queryDc.Parameter[1].CustomValues.Add("Einzelfallhilfe");
            //queryDc.Parameter[1].CustomValues.Add("Familien entlastender Dienst");
            //queryDc.Parameter[1].CustomValues.Add("Tagesstruktur");
            //queryDc.Parameter[1].CustomValues.Add("Wohnstätte");

            StringBuilder sb = new StringBuilder();

            sb.AppendLine("Debitor;Rechnungsnummer;Belegdatum;Umsatz;H;Konto;Buchungstext");

            //sb.AppendLine("Betrag;Gegenkonto;Rechnungsnummer;Belegfeld 2;Belegdatum;Konto;Kostenstelle;Buchungstext");

            var s = GetRechnungenString(date, minInsts);
            if (!String.IsNullOrWhiteSpace(s))
            {
                sb.AppendLine(s);
            }
            
            return sb.ToString();
        }
        
        public static String GetRechnungenString(DateTime date, DateTime? minInsts)
        {
            

            StringBuilder sb = new StringBuilder();
            

            var s = GetMonatlicheRechnungenString(date, "", "", "4095", minInsts);

            if (!String.IsNullOrWhiteSpace(s))
            {
                sb.Append(s);
            }

            return sb.ToString();
        }


        public static String GetMonatlicheRechnungenString(DateTime date, String rechnungId, String buchungstext, String konto, DateTime? minInsts)
        {
            
            StringBuilder sb = new StringBuilder();
            var sql = GetMonatlicheRechnungenSql(date, rechnungId, minInsts);

            /*
sip.Claim,
c.DebitorNumber,
 ib.InvoiceDate,
 ib.invoicenumber,
 c.CostCenter,
c2s.Oid,
ib.InvoiceId
             */
            var dt = ExecuteQuery(sql, date);

            //sb.AppendLine("Debitorennummer,Rechnungsnr, Rechnungsdatum (monatsletzter des Auswertedatums),Umsatz,H,Konto,Kostenstelle,Buchungstext,0");
            foreach (DataRow row in dt.Rows)
            {
                if (sb.Length > 0)
                {
                    sb.AppendLine();
                }
                sb.Append(String.Format("{0:0.00}", row[1])); // Gegenkonto
                sb.Append(";");
                sb.Append(String.Format("{0}", row[3])); // Rechnungsnummer
                sb.Append(";");
                DateTime lastDate = new DateTime(date.Year, date.Month, 1);
                lastDate = lastDate.AddMonths(1).AddDays(-1);
                sb.Append(String.Format("{0:dd.MM.yyyy}", lastDate)); //Belegdatum
                sb.Append(";");
                sb.Append(String.Format("{0:0.00}", row[0])); // Betrag
                sb.Append(";");
                sb.Append("H");
                sb.Append(";");
                sb.Append(konto); // Konto
                sb.Append(";");

                
                
                /*
Name, Vorname Rechnungsadressat wenn kein Name, Vorname vorhanden, dann das Feld Firma ausgeben - plus Name, Vorname Kunde -  
plus Datum aus Leistung Format mmjj

wenn Re-Adressat gleich Kunde, dann nur den Name, Vorname des Kunden ausgeben."
Bsp. LK Stade - Mustermann, Max - 11.2019

                 */

                var text = "";
                long? cOid = (long?)row[5];

                if (cOid.HasValue)
                {
                    var c = DAOFactory.GenericDAO.LoadByID<Customer>(cOid.Value);

                    text = String.Format("{0}, {1}", c.Person.LastName, c.Person.FirstName);
                }




                sb.Append(text);


            }

            return sb.ToString();
        }

        private static String GetMonatlicheRechnungenSql(DateTime date, String rechnungId, DateTime? mininsts)
        {
            
            String sql = @"
(select 
 sip.Claim,
c.DebitorNumber,
 ib.InvoiceDate,
 ib.invoicenumber,
 c.CostCenter,
c.Oid,
ib.InvoiceId
 from
 person p
 inner join customer c on c.personoid = p.oid
 inner join supportconcept sc on sc.customeroid = c.oid
 inner join costbearer2supportconcept c2s on c2s.supportconceptoid = sc.oid
 inner join invoicebase ib on ib.costbearer2supportconceptoid = c2s.oid
 inner join serviceinvoice si on si.invoicebaseoid = ib.oid
 inner join serviceinvoiceperiod sip on sip.serviceinvoiceoid = si.oid
WHERE ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End' and ib.IsActive = 1
order by ib.invoicenumber)
union
(
select 
(select sum(ii.amounttotal) from invoiceitem ii where ii.invoicebaseoid = ib.oid),
c.DebitorNumber,
 ib.InvoiceDate,
 ib.invoicenumber,
 c.CostCenter,
c.oid,
ib.InvoiceId
 from
 person p
 inner join customer c on c.personoid = p.oid
 inner join invoicebase ib on ib.RecipientCustomerOid = c.oid
WHERE ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End' and ib.IsActive = 1)
";
            sql = sql.Replace("%bereich%", "%" + rechnungId + "%");

            string instsfilter = "";

            if (mininsts.HasValue)
            {
                instsfilter = String.Format("AND ib.Insts >= '{0:yyyy-MM-dd}'", mininsts);
            }
            sql = sql.Replace("%instsFilter%", instsfilter);
            return sql;
        }


        public static DataTable ExecuteQuery(String sql, DateTime dt)
        {
            var newsql = sql;
            newsql = newsql.Replace(":Abrechnungsmonat", String.Format("{0:dd.MM.yyyy}", dt));
            newsql = newsql.Replace(":Belegnr", String.Format("{0:yyMM}", dt));
            newsql = newsql.Replace(":Monat_MM", String.Format("{0:MM}", dt));
            newsql = newsql.Replace(":Monat_Start", String.Format("{0:yyyy-MM}-01", dt));
            dt = dt.AddMonths(1);
            newsql = newsql.Replace(":Monat_End", String.Format("{0:yyyy-MM}-01", dt));

            return DAOFactory.AdoDAO.ExecuteQuery(newsql).Tables[0];
        }
        

    }
}