406 lines
20 KiB
C#
406 lines
20 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Text;
|
||
using BeWo.Data.Access;
|
||
using BS.Shared.DataContracts;
|
||
|
||
namespace DiakonieWerkEssen.Export
|
||
{
|
||
public class DiamantExporter
|
||
{
|
||
public static QueryDC CreateQuery(QueryDC query)
|
||
{
|
||
DateTime dt = DateTime.Now;
|
||
DateTime.TryParse(query.Parameter[0].Value.ToString(), out dt);
|
||
|
||
query.FileName = String.Format("F{0:yyyyMM}.er2", dt);
|
||
query.QueryResult = GetAbrechnungenString(dt);
|
||
|
||
return query;
|
||
}
|
||
|
||
public static string CreateExportString(string[] pHeaderCaption, string[][] pContent)
|
||
{
|
||
if (pHeaderCaption != null && pHeaderCaption.Length > 1 && pHeaderCaption[0].Contains("Diamant"))
|
||
{
|
||
DateTime dt = DateTime.Now;
|
||
DateTime.TryParse(pHeaderCaption[1], out dt);
|
||
return GetAbrechnungenString(dt);
|
||
}
|
||
return null;
|
||
}
|
||
|
||
public static String GetAbrechnungenString(DateTime date)
|
||
{
|
||
StringBuilder sb = new StringBuilder();
|
||
var s = GetMonatlicheAbrechnungLvrString(date);
|
||
if (!String.IsNullOrWhiteSpace(s))
|
||
{
|
||
sb.Append(s);
|
||
}
|
||
s = GetMonatlicheAbrechnungString(date);
|
||
if (!String.IsNullOrWhiteSpace(s))
|
||
{
|
||
if (sb.Length > 0)
|
||
{
|
||
sb.AppendLine();
|
||
}
|
||
sb.Append(s);
|
||
}
|
||
return sb.ToString();
|
||
}
|
||
|
||
public static String GetMonatlicheAbrechnungString(DateTime date)
|
||
{
|
||
var dt = ExecuteQuery(GetMonatlicheRechnungenSql(), date);
|
||
return ProcessAbrechnungRows(dt);
|
||
}
|
||
|
||
private static String ProcessAbrechnungRows(DataTable dt)
|
||
{
|
||
// F-Zeile: Rechnungs-/Buchungsangaben,
|
||
// G-Zeile: Gegenbuchung Erlöskonto, künstlich aufteilt für Faktor-Aufschläge
|
||
// K-Zeile: Kostenstelle/Position (kann mehrfach vorkommen bei mehreren Positionen)
|
||
const int startIdx = 1;
|
||
const int stopIdx1 = 34; // G-Zeile beginnt hier
|
||
const int stopIdx2 = 42; // K-Zeile beginnt hier
|
||
const int idxFaktorBetrag = 51; // vorberechneter Aufschlag aus SQL (negativ, wie K-Betrag)
|
||
|
||
// Erster Pass: Gesamtaufschlag je OID akkumulieren.
|
||
// Nötig weil eine Rechnung mehrere K-Positionen haben kann (je eine Zeile im DataTable),
|
||
// die G-Zeile aber nur einmal ausgegeben wird und um den Gesamtbetrag gekürzt werden muss.
|
||
var oid2TotalFaktor = new Dictionary<string, decimal>();
|
||
foreach (DataRow row in dt.Rows)
|
||
{
|
||
string orgName = row[36].ToString();
|
||
if (!orgName.Contains("FLS") && !orgName.Contains("DLS")) continue;
|
||
decimal faktorBetrag = row.ItemArray.Length > idxFaktorBetrag ? ErzeugeBetrag(row[idxFaktorBetrag]) : 0m;
|
||
if (faktorBetrag == 0m) continue;
|
||
string oid = row[0].ToString();
|
||
oid2TotalFaktor[oid] = Math.Round((oid2TotalFaktor.ContainsKey(oid) ? oid2TotalFaktor[oid] : 0m) + faktorBetrag, 2, MidpointRounding.AwayFromZero);
|
||
}
|
||
|
||
StringBuilder sb = new StringBuilder();
|
||
Dictionary<string, DataRow> oid2Row = new Dictionary<string, DataRow>();
|
||
foreach (DataRow row in dt.Rows)
|
||
{
|
||
string oid = row[0].ToString();
|
||
bool storno = false;
|
||
string orgName = row[36].ToString();
|
||
oid2TotalFaktor.TryGetValue(oid, out decimal totalFaktor);
|
||
bool addFaktor = totalFaktor != 0m;
|
||
string faktorKonto = orgName.Contains("FLS") ? "42410" : "42455";
|
||
|
||
if (!oid2Row.ContainsKey(oid))
|
||
{
|
||
oid2Row.Add(oid, row);
|
||
|
||
if (sb.Length > 0)
|
||
sb.AppendLine();
|
||
|
||
for (int i = startIdx; i < row.ItemArray.Length; i++)
|
||
{
|
||
if (i > 50) continue;
|
||
var item = row[i];
|
||
if (i == 15)
|
||
{
|
||
decimal betrag = ErzeugeBetrag(item);
|
||
if (betrag != 0)
|
||
{
|
||
if (betrag < 0)
|
||
storno = true;
|
||
sb.Append(String.Format("{0:0.00}", betrag));
|
||
sb.Append(";");
|
||
}
|
||
}
|
||
else if (i == 16)
|
||
{
|
||
sb.Append('"');
|
||
sb.Append(item);
|
||
sb.Append('"');
|
||
}
|
||
else if (i == stopIdx1)
|
||
{
|
||
sb.AppendLine();
|
||
sb.Append(item);
|
||
sb.Append(";");
|
||
}
|
||
else if (i == 36)
|
||
{
|
||
sb.Append(GetErloeskonto(item.ToString()));
|
||
sb.Append(";");
|
||
}
|
||
else if (i == 39)
|
||
{
|
||
// G-Betrag: bei FLS/DLS um den Faktoranteil kürzen
|
||
decimal betrag = ErzeugeBetrag(item);
|
||
if (addFaktor)
|
||
betrag = Math.Round(betrag - totalFaktor, 2, MidpointRounding.AwayFromZero);
|
||
if (betrag != 0)
|
||
{
|
||
sb.Append(String.Format("{0:0.00}", betrag));
|
||
sb.Append(";");
|
||
}
|
||
}
|
||
else if (i == 40)
|
||
{
|
||
sb.Append('"');
|
||
sb.Append(item);
|
||
sb.Append('"');
|
||
}
|
||
else if (i == stopIdx2)
|
||
{
|
||
// Zusätzliche G-Zeile für FLS/DLS-Faktoranteil – vor der ersten K-Zeile
|
||
if (addFaktor)
|
||
{
|
||
sb.AppendLine();
|
||
sb.Append("G;0;");
|
||
sb.Append(faktorKonto);
|
||
sb.Append(";;;");
|
||
sb.Append(String.Format("{0:0.00}", totalFaktor));
|
||
sb.Append(";\"\"");
|
||
sb.Append(";");
|
||
}
|
||
sb.AppendLine();
|
||
sb.Append(item);
|
||
sb.Append(";");
|
||
}
|
||
else if (i == 50)
|
||
{
|
||
decimal betrag = ErzeugeBetrag(item);
|
||
if (betrag != 0)
|
||
{
|
||
sb.Append(String.Format("{0:0.00}", betrag));
|
||
sb.Append(";");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
sb.Append(item);
|
||
sb.Append(";");
|
||
}
|
||
}
|
||
}
|
||
else
|
||
{
|
||
if (sb.Length > 0)
|
||
sb.AppendLine();
|
||
|
||
for (int i = startIdx; i < row.ItemArray.Length; i++)
|
||
{
|
||
if (i > 50) continue;
|
||
var item = row[i];
|
||
// mach nichts mit den ersten Spalten -> erstelle nur Kostenstellen
|
||
if (i == stopIdx2)
|
||
{
|
||
sb.Append(item);
|
||
sb.Append(";");
|
||
}
|
||
else if (i == 50)
|
||
{
|
||
decimal betrag = ErzeugeBetrag(item);
|
||
if (betrag != 0)
|
||
{
|
||
sb.Append(String.Format("{0:0.00}", betrag));
|
||
sb.Append(";");
|
||
}
|
||
}
|
||
else if (i > stopIdx2)
|
||
{
|
||
sb.Append(item);
|
||
sb.Append(";");
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
return sb.ToString();
|
||
}
|
||
|
||
private static String GetErloeskonto(String orgName)
|
||
{
|
||
// zzgl. Erlöskonten Faktor: orgName.Contains("FLS") ? "42410" : "42455";
|
||
if (orgName.Contains("Jugendamt") || orgName.Contains("Essen"))
|
||
return "42425";
|
||
if (orgName.Contains("FLS"))
|
||
return "42405";
|
||
if (orgName.Contains("DLS"))
|
||
return "42450";
|
||
|
||
return "42405";
|
||
}
|
||
|
||
public static decimal ErzeugeBetrag(object betragObj)
|
||
{
|
||
decimal gesamtbetrag = 0;
|
||
if (betragObj != null)
|
||
{
|
||
Decimal.TryParse(betragObj.ToString().Replace(".", ","), out gesamtbetrag);
|
||
}
|
||
decimal betrag = Math.Round(gesamtbetrag, 2, MidpointRounding.AwayFromZero);
|
||
return betrag;
|
||
}
|
||
|
||
public static String GetMonatlicheAbrechnungLvrString(DateTime date)
|
||
{
|
||
var dt = ExecuteQuery(GetMonatlicheAbrechnungLvrSql(), date);
|
||
return ProcessAbrechnungRows(dt);
|
||
}
|
||
|
||
private static String GetMonatlicheAbrechnungLvrSql()
|
||
{
|
||
String sql = @"
|
||
SELECT
|
||
qry.Oid AS OID,
|
||
'F', '0', '204', null, '', 'ARBW', ':Abrechnungsmonat', ':Periode', null,
|
||
'',
|
||
qry.DebitorNumber,
|
||
null, null, null,
|
||
Round(SUM(qry.Betrag), 2) AS 'Betrag',
|
||
CONCAT(qry.Verwendung, ' Abgrenzung :PeriodePunkt ', qry.Kostentraeger) as 'Verwendung',
|
||
null, null, null, 'EUR',
|
||
null, null, null, null, null, null, null, null, null, null, null, null, null,
|
||
'G', '0',qry.Kostentraeger, null, null,
|
||
Round(SUM(qry.Betrag) * -1, 2) AS 'Betrag2',
|
||
CONCAT(qry.Verwendung, ' Abgrenzung :PeriodePunkt ', qry.Kostentraeger) as 'Verwendung2',
|
||
null,
|
||
'K', 0, '206222', null, null, null, null, null, Round(SUM(qry.Betrag) * -1, 2) AS 'AmountTotal',
|
||
Round(SUM(qry.FaktorBetrag) * -1, 2) AS FaktorTotal
|
||
FROM
|
||
(
|
||
(SELECT
|
||
IF(crpRateFactor.`CostRateValue` is null, ROUND((sr.`GeleisteteFLM` / 60) * (SELECT crp.`CostRateValue` FROM `costrateperiod` crp WHERE crp.`ObjectTid` = 22 AND crp.`CostRateType` = 0 AND crp.`ObjectOid` = cb2sc.`CostBearerOid` AND (crp.`EndDate` is null or crp.`EndDate` > ':Monat_Start') order by IF(crp.`EndDate` is null, MAKEDATE(9999,365),crp.`EndDate`) LIMIT 1 )),
|
||
ROUND(ROUND((sr.`GeleisteteFLM` / 60), 2) * (SELECT crp.`CostRateValue` FROM `costrateperiod` crp WHERE crp.`ObjectTid` = 22 AND crp.`CostRateType` = 0 AND crp.`ObjectOid` = cb2sc.`CostBearerOid` AND (crp.`EndDate` is null or crp.`EndDate` > ':Monat_Start') order by IF(crp.`EndDate` is null, MAKEDATE(9999,365),crp.`EndDate`) LIMIT 1 ) * ((100 + crpRateFactor.`CostRateValue`)/100), 2)) AS 'Betrag',
|
||
c.DebitorNumber,
|
||
CONCAT(p.`LastName`, ', ', p.`FirstName`) as 'Verwendung',
|
||
org.Name AS Kostentraeger,
|
||
cb2sc.Oid,
|
||
IF(crpRateFactor.`CostRateValue` is null, 0,
|
||
ROUND(ROUND((sr.`GeleisteteFLM` / 60), 2) * (SELECT crp.`CostRateValue` FROM `costrateperiod` crp WHERE crp.`ObjectTid` = 22 AND crp.`CostRateType` = 0 AND crp.`ObjectOid` = cb2sc.`CostBearerOid` AND (crp.`EndDate` is null or crp.`EndDate` > ':Monat_Start') order by IF(crp.`EndDate` is null, MAKEDATE(9999,365),crp.`EndDate`) LIMIT 1 ) * (crpRateFactor.`CostRateValue` / 100), 2)
|
||
) AS FaktorBetrag
|
||
FROM `supportconcept` sc
|
||
INNER JOIN `costbearer2supportconcept` cb2sc ON sc.`Oid` = cb2sc.`SupportConceptOid`
|
||
INNER JOIN `costbearer` cb ON cb2sc.`CostBearerOid` = cb.`Oid`
|
||
LEFT JOIN
|
||
(SELECT crp.`ObjectOid`, crp.`CostRateValue` FROM `costrateperiod` crp
|
||
WHERE crp.`ObjectTid` = 22 AND crp.`CostRateType` = 2 AND (crp.`EndDate` is null or crp.`EndDate` > NOW()))
|
||
AS crpRateFactor ON crpRateFactor.`ObjectOid` = cb.`Oid`
|
||
INNER JOIN `organisation` org ON org.`CostBearerOid` = cb.`Oid`
|
||
INNER JOIN `customer` c ON sc.`CustomerOid` = c.`Oid`
|
||
INNER JOIN `person` p on c.`PersonOid` = p.`Oid`
|
||
LEFT JOIN
|
||
(SELECT sr2.`CostBearer2SupportConceptOid`, SUM(sr2.`roundedduration` / IF(sr2.`GroupEmployeeCount` is null, 1, sr2.`GroupEmployeeCount`)) AS GeleisteteFLM FROM `servicerecord` sr2
|
||
INNER JOIN `servicedescription` sd ON sr2.`ServiceDescriptionOid` = sd.`Oid`
|
||
INNER JOIN `servicecategory` sc ON sd.`ServiceCategoryOid` = sc.`Oid`
|
||
WHERE sr2.`StartDate` >= ':Monat_Start' AND sr2.`StartDate` < ':Monat_End' AND sc.`Billable` = 1 AND sd.Name <> 'Fehlkontakt' GROUP BY sr2.`CostBearer2SupportConceptOid`)
|
||
AS sr ON sr.`CostBearer2SupportConceptOid` = cb2sc.`Oid`
|
||
WHERE (org.Oid = 142 OR org.Oid = 2) AND c.`IsActive` <> 0 AND sc.`IsActive` <> 0 AND (sr.`GeleisteteFLM` is not null))
|
||
UNION ALL
|
||
(SELECT
|
||
ROUND(0.8 * (sr.`GeleisteteFLM` / 60) * (SELECT crp.`CostRateValue` FROM `costrateperiod` crp WHERE crp.`ObjectTid` = 22 AND crp.`CostRateType` = 0 AND crp.`ObjectOid` = cb2sc.`CostBearerOid` AND (crp.`EndDate` is null or crp.`EndDate` > ':Monat_Start') order by IF(crp.`EndDate` is null, MAKEDATE(9999,365),crp.`EndDate`) LIMIT 1 ), 2) AS 'Betrag',
|
||
c.DebitorNumber,
|
||
CONCAT(p.`LastName`, ', ', p.`FirstName`) as 'Verwendung',
|
||
org.Name AS Kostentraeger,
|
||
cb2sc.Oid,
|
||
0 AS FaktorBetrag
|
||
FROM `supportconcept` sc
|
||
INNER JOIN `costbearer2supportconcept` cb2sc ON sc.`Oid` = cb2sc.`SupportConceptOid`
|
||
INNER JOIN `costbearer` cb ON cb2sc.`CostBearerOid` = cb.`Oid`
|
||
INNER JOIN `organisation` org ON org.`CostBearerOid` = cb.`Oid`
|
||
INNER JOIN `customer` c ON sc.`CustomerOid` = c.`Oid`
|
||
INNER JOIN `person` p on c.`PersonOid` = p.`Oid`
|
||
LEFT JOIN
|
||
(SELECT sr2.`CostBearer2SupportConceptOid`, SUM(sr2.`roundedduration` / IF(sr2.`GroupEmployeeCount` is null, 1, sr2.`GroupEmployeeCount`)) AS GeleisteteFLM FROM `servicerecord` sr2
|
||
INNER JOIN `servicedescription` sd ON sr2.`ServiceDescriptionOid` = sd.`Oid`
|
||
INNER JOIN `servicecategory` sc ON sd.`ServiceCategoryOid` = sc.`Oid`
|
||
WHERE sr2.`StartDate` >= ':Monat_Start' AND sr2.`StartDate` < ':Monat_End' AND sc.`Billable` = 1 AND sd.Name = 'Fehlkontakt' GROUP BY sr2.`CostBearer2SupportConceptOid`)
|
||
AS sr ON sr.`CostBearer2SupportConceptOid` = cb2sc.`Oid`
|
||
WHERE (org.Oid = 142 OR org.Oid = 2) AND c.`IsActive` <> 0 AND sc.`IsActive` <> 0 AND (sr.`GeleisteteFLM` is not null))
|
||
) qry
|
||
GROUP BY qry.Oid, qry.DebitorNumber, qry.Verwendung, qry.Kostentraeger
|
||
ORDER BY qry.Verwendung
|
||
";
|
||
return sql;
|
||
}
|
||
|
||
private static String GetMonatlicheRechnungenSql()
|
||
{
|
||
String sql = @"
|
||
|
||
SELECT
|
||
CONCAT(cb2sc.Oid, ib.InvoiceNumber, Round(sip.Claim, 0)) AS OID,
|
||
'F', '0', '204', null, '', 'ARBW', ':Abrechnungsmonat', ':Periode', null,
|
||
ib.InvoiceNumber,
|
||
c.DebitorNumber,
|
||
null, null, null,
|
||
Round(sip.Claim, 2) AS 'Betrag',
|
||
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', ' Abrechnung :PeriodePunkt ', org.Name) as 'Verwendung',
|
||
null, null, null, 'EUR',
|
||
null, null, null, null, null, null, null, null, null, null, null, null, null,
|
||
'G', '0', org.Name, null, null,
|
||
Round(sip.Claim * -1, 2) AS 'Betrag2',
|
||
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', ' Abrechnung :PeriodePunkt ', org.Name) as 'Verwendung2', null,
|
||
'K', 0, '206222', null, null, null, null, null, Round(ii.AmountTotal * -1, 2),
|
||
IF(ii.ItemDescription = 'Fehlkontakt', 0, Round(ii.AmountTotal * (20 / 120) * -1, 2)) AS FaktorAmount
|
||
FROM
|
||
person p
|
||
INNER JOIN customer c on c.personoid = p.oid
|
||
INNER JOIN supportconcept sc on sc.customeroid = c.oid
|
||
INNER JOIN costbearer2supportconcept cb2sc on cb2sc.supportconceptoid = sc.oid
|
||
INNER JOIN costbearer cb on cb2sc.costbeareroid = cb.oid
|
||
INNER JOIN organisation org on org.costbeareroid = cb.oid
|
||
INNER JOIN invoicebase ib on ib.costbearer2supportconceptoid = cb2sc.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
|
||
WHERE c.`IsActive` = 1 AND sc.`IsActive` = 1 and ib.isactive = 1 and ib.type <> 1 AND ib.`IsExported` = 0
|
||
and ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodStart` < ':Monat_End'
|
||
UNION
|
||
SELECT
|
||
CONCAT(cb2sc.Oid, ib.InvoiceNumber, Round(si.Claim, 0)) AS OID,
|
||
'F', '0', '204', null, '', 'ARBW', ':Abrechnungsmonat', ':Periode', null,
|
||
ib.InvoiceNumber,
|
||
c.DebitorNumber,
|
||
null, null, null,
|
||
Round(si.Claim, 2) AS 'Betrag',
|
||
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', ' Abrechnung :PeriodePunkt ', org.Name) as 'Verwendung',
|
||
null, null, null, 'EUR',
|
||
null, null, null, null, null, null, null, null, null, null, null, null, null,
|
||
'G', '0', org.Name, null, null,
|
||
Round(si.Claim * -1, 2) AS 'Betrag2',
|
||
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', ' Abrechnung :PeriodePunkt ', org.Name) as 'Verwendung2', null,
|
||
'K', 0, '206222', null, null, null, null, null, Round(si.Claim * -1, 2),
|
||
Round(si.Claim * (20 / 120) * -1, 2) AS FaktorAmount
|
||
FROM invoicebase ib
|
||
INNER JOIN settlementinvoice si on si.invoicebaseoid = ib.oid
|
||
INNER JOIN supportconcept sc on sc.oid = ib.supportconceptoid
|
||
INNER JOIN costbearer2supportconcept cb2sc on cb2sc.supportconceptoid = sc.oid
|
||
INNER JOIN costbearer cb on cb2sc.costbeareroid = cb.oid
|
||
INNER JOIN organisation org on org.costbeareroid = cb.oid
|
||
INNER JOIN customer c on c.oid = sc.customeroid
|
||
INNER JOIN person p on c.`PersonOid` = p.`Oid`
|
||
WHERE ib.type = 1 AND ib.isactive = 1 AND c.isactive <> 0 AND ib.`IsExported` = 0
|
||
AND ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodStart` < ':Monat_End'
|
||
ORDER BY 11
|
||
";
|
||
return sql;
|
||
}
|
||
|
||
public static DataTable ExecuteQuery(String sql, DateTime dt)
|
||
{
|
||
var newsql = sql;
|
||
newsql = newsql.Replace(":PeriodePunkt", String.Format("{0:MM.yyyy}", dt));
|
||
newsql = newsql.Replace(":PeriodeSlash", String.Format("{0:MM/yyyy}", dt));
|
||
newsql = newsql.Replace(":Periode", String.Format("{0:MMyyyy}", dt));
|
||
newsql = newsql.Replace(":Abrechnungsmonat", String.Format("{0:ddMMyyyy}", dt));
|
||
newsql = newsql.Replace(":Abrechnungsmonat", String.Format("{0:ddMMyyyy}", 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];
|
||
}
|
||
}
|
||
} |