239 lines
10 KiB
C#
239 lines
10 KiB
C#
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.Report.ReportObjects;
|
|
using BeWo.Service.DCEntityMapper;
|
|
using BS.Shared.DataContracts;
|
|
using DevExpress.XtraPrinting;
|
|
using DevExpress.XtraReports.UI;
|
|
|
|
namespace SBBMainzReports.Export
|
|
{
|
|
public class FibuExport
|
|
{
|
|
List<FileAttachmentDC> liste = new List<FileAttachmentDC>();
|
|
public static QueryDC CreateQuery(QueryDC query)
|
|
{
|
|
DateTime dt = DateTime.Now;
|
|
DateTime.TryParse(query.Parameter[0].Value.ToString(), out dt);
|
|
|
|
var table = ExecuteQuery(GetMonatlicheRechnungenSql(dt), dt);
|
|
|
|
query.FileName = String.Format("BeWoPlaner-FiBu-Export{0:yyyyMM}.csv", dt);
|
|
query.QueryResult = GetAbrechnungenString(dt, table);
|
|
query.Attachments = CreateInvoiceFiles(table);
|
|
return query;
|
|
}
|
|
|
|
public static String GetAbrechnungenString(DateTime dt, DataTable table)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.AppendLine("Datum;Erlöskonto;Kostenstelle;ReNr;Debitor;Leistungsempfänger;Gesamtsumme;Rechnungsbetrag;Leistung;Kostenträger");
|
|
var s = GetMonatlicheRechnungenString(dt, table);
|
|
if (!String.IsNullOrWhiteSpace(s))
|
|
{
|
|
sb.Append(s);
|
|
}
|
|
|
|
return sb.ToString();
|
|
}
|
|
|
|
public static String GetMonatlicheRechnungenString(DateTime date, DataTable dt)
|
|
{
|
|
// '8000' as 'Gegenkonto', sip.Claim, ib.invoicenumber, ib.InvoiceDate, c.DebitorNumber, c.CostCenter, org.Name, p.FirstName, p.LastName,
|
|
// '8000' as 'Gegenkonto', Round(ii.AmountTotal, 2) AS 'Betrag', sums.Gesamtbetrag, ib.invoicenumber, ib.InvoiceDate, c.DebitorNumber, c.CostCenter, org.Name, CONCAT(p.`LastName`, ', ', p.`FirstName`) as 'Verwendung',
|
|
// ii.ItemDescription,
|
|
StringBuilder sb = new StringBuilder();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
if (sb.Length > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
DateTime lastDate = new DateTime(date.Year, date.Month, 1);
|
|
lastDate = lastDate.AddMonths(1).AddDays(-1);
|
|
|
|
sb.Append(String.Format("{0:dd.MM.yyyy}", lastDate));
|
|
sb.Append(";");
|
|
string konto = row[0].ToString();
|
|
string costcenter = row[6].ToString().Trim();
|
|
string leistung = row[9].ToString();
|
|
leistung = GetLeistungsart(costcenter, leistung);
|
|
if (row[9] != null && row[9].ToString().Contains("Handgeld"))
|
|
{
|
|
konto = "1361";
|
|
costcenter = "";
|
|
}
|
|
sb.Append(String.Format("\"{0}\"", konto)); //Erlöskonto
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", costcenter)); // Kostenstelle
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", row[3])); // ReNr
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", row[5])); // Debitor
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", row[8])); // Leistungsempfänger
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0:0.00}", row[1])); // Betrag pro Leistung
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0:0.00}", row[2])); // Rechnungsbetrag
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", leistung)); // Leistung(sart)
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", row[7])); // Kostenträger Name
|
|
sb.Append(";");
|
|
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
private static string GetLeistungsart(string costcenter, string leistung)
|
|
{
|
|
if (costcenter == "210")
|
|
{
|
|
return leistung = "Eingliederungshilfe nach SGB IX";
|
|
}
|
|
if (costcenter == "220")
|
|
{
|
|
return leistung = "Qualifizierte Assistenz";
|
|
}
|
|
if (costcenter == "300")
|
|
{
|
|
return leistung; // SPFH
|
|
}
|
|
return leistung;
|
|
}
|
|
|
|
private static List<FileAttachmentDC> CreateInvoiceFiles(DataTable dt)
|
|
{
|
|
try
|
|
{
|
|
List<FileAttachmentDC> liste = new List<FileAttachmentDC>();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
var pdf = new FileAttachmentDC();
|
|
|
|
var invoiceOid = row[10].ToString();
|
|
if (!String.IsNullOrEmpty(invoiceOid))
|
|
{
|
|
var oid = long.Parse(invoiceOid);
|
|
var invoice = DAOFactory.SearchDAO.GetServiceInvoiceByInvoiceBaseOid(oid);
|
|
//var invoice = DAOFactory.GenericDAO.LoadByID<ServiceInvoice>(oid);
|
|
var idc = MapperFactory.ServiceInvoiceDC_ServiceInvoice.MapToNewDC(invoice);
|
|
|
|
var anhang = new ServiceInvoiceReport();
|
|
var ro = ServiceInvoiceRO.Create(idc);
|
|
anhang.SetReportDataSource(ro);
|
|
anhang.CreateDocument();
|
|
|
|
pdf.FileName = anhang.PrintingSystem.Document.Name + ".pdf";
|
|
using (var ms = new MemoryStream())
|
|
{
|
|
anhang.ExportToPdf(ms);
|
|
pdf.BinaryData = ms.ToArray();
|
|
}
|
|
liste.Add(pdf);
|
|
//string name = anhang.PrintingSystem.Document.Name;
|
|
//string path = @"C:\Temp\FiBuExport\";
|
|
//anhang.ExportToPdf(path + name + ".pdf");
|
|
}
|
|
}
|
|
return liste;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
// continue - auch wenn gar nichts stimmt
|
|
}
|
|
return null;
|
|
}
|
|
|
|
private static String GetMonatlicheRechnungenSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
select
|
|
'8000' as 'Gegenkonto',
|
|
Round(ii.AmountTotal, 2) AS 'Betrag',
|
|
sums.Gesamtbetrag,
|
|
ib.invoicenumber,
|
|
ib.InvoiceDate,
|
|
c.DebitorNumber,
|
|
c2s.KostenstelleHilfeplan,
|
|
org.Name,
|
|
CONCAT(p.`FirstName`, ' ',p.`LastName`) as 'Verwendung',
|
|
ii.ItemDescription,
|
|
ib.Oid
|
|
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 costbearer cb on c2s.costbeareroid = cb.oid
|
|
inner join organisation org on org.costbeareroid = cb.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
|
|
inner join invoiceitem ii on ii.serviceinvoiceperiodoid = sip.oid
|
|
|
|
join
|
|
(select SUM(sip.Claim) AS Gesamtbetrag,
|
|
ib.oid
|
|
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 costbearer cb on c2s.costbeareroid = cb.oid
|
|
inner join organisation org on org.costbeareroid = cb.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.IsActive = 1 and ib.IsExported = 0
|
|
AND (((ib.InvoiceTypeText != 'Storno' OR ib.InvoiceTypeText IS NULL) AND ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End')
|
|
OR (ib.InvoiceTypeText = 'Storno' AND ib.`InvoiceDate` >= ':Monat_Start' AND ib.`InvoiceDate` < ':Monat_End'))
|
|
Group by ib.oid) sums
|
|
on ib.oid = sums.oid
|
|
WHERE ib.IsActive = 1 and ib.IsExported = 0
|
|
AND (((ib.InvoiceTypeText != 'Storno' OR ib.InvoiceTypeText IS NULL) AND ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End')
|
|
OR (ib.InvoiceTypeText = 'Storno' AND ib.`InvoiceDate` >= ':Monat_Start' AND ib.`InvoiceDate` < ':Monat_End'))
|
|
UNION
|
|
select
|
|
'8000' as 'Gegenkonto',
|
|
Round(ii.AmountTotal, 2) AS 'Betrag',
|
|
(select SUM(Round(ii2.AmountTotal, 2)) from invoiceitem ii2 where ii2.invoicebaseoid = ib.Oid) as 'Gesamtbetrag',
|
|
ib.invoicenumber,
|
|
ib.InvoiceDate,
|
|
'10000' as 'DebitorNumber',
|
|
'210' as 'Kostenstelle',
|
|
ib.RecipientOrganisation as 'Name',
|
|
'Manuelle Rechnung' as 'Verwendung',
|
|
ii.ItemDescription,
|
|
ib.Oid
|
|
from invoicebase ib
|
|
inner join invoiceitem ii on ii.invoicebaseoid = ib.Oid
|
|
WHERE ib.IsActive = 1 and ib.IsExported = 0
|
|
and ib.Type = 2
|
|
and ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End'
|
|
|
|
order by invoicenumber
|
|
";
|
|
|
|
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_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];
|
|
}
|
|
}
|
|
} |