591 lines
20 KiB
C#
591 lines
20 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Text;
|
|
using System.Text.RegularExpressions;
|
|
using BeWo.Data.Access;
|
|
using BeWo.Report.ReportObjects;
|
|
using BS.Shared.DataContracts;
|
|
|
|
namespace ASBHamm.Export
|
|
{
|
|
public class FibuNetExporter
|
|
{
|
|
|
|
public static QueryDC CreateQuery(QueryDC query)
|
|
{
|
|
DateTime dt = DateTime.Now;
|
|
DateTime.TryParse(query.Parameter[0].Value.ToString(), out dt);
|
|
|
|
if (query.Title == "FibuNet Export Stammdaten")
|
|
{
|
|
query.FileName = String.Format("BWP Stammdaten {0:yyyyMM}.txt", dt);
|
|
query.QueryResult = GetDebitorenString(dt);
|
|
}
|
|
else
|
|
{
|
|
query.FileName = String.Format("BWP Buchungsdaten {0:yyyyMM}.txt", dt);
|
|
query.QueryResult = GetAbrechnungenString(dt);
|
|
}
|
|
|
|
return query;
|
|
}
|
|
|
|
public static string CreateExportString(string[] pHeaderCaption, string[][] pContent)
|
|
{
|
|
if (pHeaderCaption != null && pHeaderCaption.Length > 1 && pHeaderCaption[0].Contains("FibuNet"))
|
|
{
|
|
DateTime dt = DateTime.Now;
|
|
|
|
DateTime.TryParse(pHeaderCaption[1], out dt);
|
|
|
|
return GetAbrechnungenString(dt);
|
|
|
|
}
|
|
|
|
|
|
return null;
|
|
}
|
|
|
|
public static String GetDebitorenString(DateTime date)
|
|
{
|
|
//Beispiel
|
|
|
|
//( ):2010;0;"Kontonummer";"Kontobezeichnung";;1;0;0;
|
|
//( ):2011;"Kontoname 1";"Kontoname 2";"Straße";"";"PLZ";"Ort";
|
|
//( ):5;
|
|
|
|
|
|
//Informationen zu den einzelnen Buchungssätzen finden Sie in der Schnittstellenbeschreibung:
|
|
|
|
|
|
//Beschreibung 2010er Satz Seite 107
|
|
//Beschreibung 2011er Satz Seite 109
|
|
|
|
var dt = ExecuteQuery(GetDebitorenSql(date), date);
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
|
|
int rowIndex = 1;
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
Erstelle2010Satz(sb, row, date, rowIndex);
|
|
sb.AppendLine();
|
|
Erstelle2011Satz(sb, row, date, rowIndex);
|
|
sb.AppendLine();
|
|
//ErstelleSatz21(sb, row, date, rowIndex);
|
|
//sb.AppendLine();
|
|
//ErstelleSatz22(sb, row, date, rowIndex);
|
|
|
|
rowIndex++;
|
|
}
|
|
|
|
sb.AppendLine(String.Format("( ):5;"));
|
|
|
|
return sb.ToString();
|
|
}
|
|
|
|
public static String GetAbrechnungenString(DateTime date)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
|
|
return GetMonatlicheRechnungen(date);
|
|
|
|
|
|
|
|
|
|
//s = GetAbschlagszahlungenString(date);
|
|
//if (!String.IsNullOrWhiteSpace(s))
|
|
//{
|
|
// sb.Append(s);
|
|
//}
|
|
//if (sb.Length > 0)
|
|
// sb.AppendLine();
|
|
|
|
//s = GetSelbstzahlerString(date);
|
|
//if (!String.IsNullOrWhiteSpace(s))
|
|
//{
|
|
// sb.Append(s);
|
|
//}
|
|
|
|
|
|
return sb.ToString();
|
|
}
|
|
|
|
|
|
|
|
private static String ErstelleBetragString(int anzahlZeichen, decimal betrag)
|
|
{
|
|
bool isNegative = betrag < 0;
|
|
var decString = String.Format("{0:0}", Math.Round(betrag, 2, MidpointRounding.AwayFromZero) * 100);
|
|
if (!isNegative)
|
|
{
|
|
return FuelleMitNull(anzahlZeichen, decString);
|
|
}
|
|
return "-" + FuelleMitNull(anzahlZeichen - 1, decString);
|
|
|
|
}
|
|
|
|
private static String FuelleMitNull(int anzahlNullen, String wert)
|
|
{
|
|
String countStr = wert;
|
|
|
|
while (countStr.Length < anzahlNullen)
|
|
{
|
|
countStr = "0" + countStr;
|
|
}
|
|
|
|
return countStr;
|
|
}
|
|
|
|
|
|
public static String GetMonatlicheRechnungen(DateTime date)
|
|
{
|
|
/*
|
|
|
|
( ):1000;;07022019
|
|
( ):1100;14280;;;10907;;;1960133;;07022019;8408;1;M;F/G 1960133;0;;;;1;;;;;;160;
|
|
( ):1110;14280;;;1;10907;8408;1;M;1960133;;07022019;;F/G 1960133;;;0
|
|
( ):5;"CareMan"
|
|
|
|
Beispiel Aufteilungsbuchung
|
|
|
|
( ):1000;;07022019
|
|
( ):1100;1392966;;;11085;;;1960129;;07022019;0;;M;F/G 1960129;0;;;;5;;;;;;0;0
|
|
( ):1100;800000;;;11085;;;1960129;;07022019;8419;1;M;F/G 1960129;0;;;;5;;;;;;130;
|
|
( ):1100;195000;;;11085;;;1960129;;07022019;8420;1;M;F/G 1960129;0;;;;5;;;;;;130;
|
|
( ):1100;175560;;;11085;;;1960129;;07022019;8409;1;M;F/G 1960129;0;;;;5;;;;;;130;
|
|
( ):1110;1392966;;;1;11085;8409;3;M;1960129;;07022019;;F/G 1960129;;;0
|
|
( ):5;"CareMan"
|
|
|
|
|
|
Informationen zu den einzelnen Buchungssätzen finden Sie in der Schnittstellenbeschreibung:
|
|
|
|
|
|
Beschreibung 1000er Satz Seite 23
|
|
Beschreibung 1100er Satz Seite 34-36
|
|
Beschreibung 1110er Satz Seite 43-45
|
|
|
|
In den aktuellen Beispielen wird aufgrund eines Buchungskenners (1100er Satz, Feld 18) automatisch eine Kostenstellenbuchung erzeugt. Hierfür ist die Angabe der Kostenstelle zwingend erforderlich.
|
|
|
|
Alternativ wäre der 1120er Satz mit einzubauen (ab Seite 61)
|
|
*/
|
|
|
|
var dt = ExecuteQuery(GetMonatlicheRechnungenSql(date), date);
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
|
|
sb.AppendLine(String.Format("( ):1000;;{0:ddMMyyyy}", date));
|
|
|
|
int rowIndex = 1;
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
Erstelle1100Satz(sb, row, date, rowIndex);
|
|
sb.AppendLine();
|
|
Erstelle1110Satz(sb, row, date, rowIndex);
|
|
sb.AppendLine();
|
|
|
|
rowIndex++;
|
|
}
|
|
|
|
sb.AppendLine(String.Format("( ):5;"));
|
|
|
|
//F,0,050,,BEWO,DA,01012016,012016,iBelegNr1,Belegnr1,220001,,,,999.99, "Name AZ Abrechnung 01/2016 LVR",,,EUR,,,,,,,,,,,R,,,
|
|
//G,0,1695,,,-999.99,"Name AZ Abrechnung 01/2016 LVR",
|
|
|
|
return sb.ToString();
|
|
}
|
|
|
|
private static void Erstelle1100Satz(StringBuilder sb, DataRow row, DateTime date, int rowIndex)
|
|
{
|
|
//( ):1100; 14280; ; ; 10907; ; ; 1960133; ; 07022019; 8408; 1; M; F / G 1960133; 0; ; ; ; 1; ; ; ; ; ; 160;
|
|
sb.Append("( ):1100;");
|
|
sb.Append(String.Format("{0:0.00};;;", (decimal) row[3]).Replace(",", "")); // Betrag
|
|
|
|
sb.Append(row[1].ToString()); // SollKonto (Debitor)
|
|
sb.Append(";;;");
|
|
sb.Append(row[5].ToString()); // Belegnummer
|
|
sb.Append(";;");
|
|
sb.Append(String.Format("{0:ddMMyyyy}", row[0])); // Belegdatum
|
|
sb.Append(";");
|
|
sb.Append("89015"); // HabenKonto
|
|
sb.Append(";1;M;"); //
|
|
sb.Append(row[4].ToString()); // Buchungstext
|
|
sb.Append(";0;;;;5;;;;;;");
|
|
sb.Append("4300"); // Kostenstelle
|
|
sb.Append(";");
|
|
}
|
|
|
|
private static void Erstelle1110Satz(StringBuilder sb, DataRow row, DateTime date, int rowIndex)
|
|
{
|
|
//( ):1110;14280;;;1;10907;8408;1;M;1960133;;07022019;;F/G 1960133;;;0
|
|
sb.Append("( ):1110;");
|
|
sb.Append(String.Format("{0:0.00};;;", (decimal)row[3]).Replace(",", "")); // Betrag
|
|
sb.Append("1;"); // Satzart
|
|
sb.Append("89015"); // HabenKonto
|
|
sb.Append(";1;M;"); //
|
|
sb.Append(row[5].ToString()); // Belegnummer
|
|
sb.Append(";;");
|
|
sb.Append(String.Format("{0:ddMMyyyy}", row[0])); // Belegdatum
|
|
sb.Append(";;");
|
|
sb.Append(row[4].ToString()); // Buchungstext
|
|
sb.Append(";;;0");
|
|
|
|
}
|
|
|
|
private static void Erstelle2010Satz(StringBuilder sb, DataRow row, DateTime date, int rowIndex)
|
|
{
|
|
//( ):2010;0;"Kontonummer";"Kontobezeichnung";;1;0;0;
|
|
sb.Append("( ):2010;0;");
|
|
sb.Append(row[2].ToString()); // Kontonummer
|
|
sb.Append(";"); //
|
|
sb.Append(""); // Kontobezeichnung?
|
|
sb.Append(";;1;0;0;");
|
|
}
|
|
|
|
private static void Erstelle2011Satz(StringBuilder sb, DataRow row, DateTime date, int rowIndex)
|
|
{
|
|
//( ):2011;"Kontoname 1";"Kontoname 2";"Straße";"";"PLZ";"Ort";
|
|
sb.Append("( ):2011;");
|
|
sb.Append(String.Format("{0} {1}", row[0], row[1])); // Kontoname 1
|
|
sb.Append(";"); //
|
|
sb.Append(""); // Kontoname 2
|
|
sb.Append(";"); //
|
|
sb.Append(row[4]); // Str.
|
|
sb.Append(";;"); //
|
|
sb.Append(row[5]); // Plz
|
|
sb.Append(";"); //
|
|
sb.Append(row[6]); // Ort
|
|
sb.Append(";");
|
|
|
|
}
|
|
|
|
private static String ErweitereMitLeerzeichen(String text, int anzahlZeichen)
|
|
{
|
|
if (text.Length > anzahlZeichen)
|
|
{
|
|
return text.Substring(0, anzahlZeichen);
|
|
}
|
|
|
|
String text2 = text;
|
|
|
|
while (text2.Length < anzahlZeichen)
|
|
{
|
|
text2 += " ";
|
|
}
|
|
|
|
return text2;
|
|
}
|
|
|
|
|
|
|
|
public static String GetAbschlagszahlungenString(DateTime date)
|
|
{
|
|
var dt = ExecuteQuery(GetAbschlagszahlungenSql(date), date);
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
if (sb.Length > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
for (int i = 0; i < row.ItemArray.Length; i++)
|
|
{
|
|
if (i > 0)
|
|
{
|
|
sb.Append(",");
|
|
}
|
|
//if (i == 32)
|
|
//{
|
|
// sb.AppendLine();
|
|
//}
|
|
var item = row[i];
|
|
bool quote = i == 15 || i == 38;
|
|
|
|
if (quote)
|
|
{
|
|
sb.Append('"');
|
|
sb.Append(item);
|
|
sb.Append('"');
|
|
}
|
|
else
|
|
{
|
|
sb.Append(item);
|
|
}
|
|
}
|
|
|
|
}
|
|
//F,0,050,,BEWO,DA,02012016,012016,iBelegNr2,Belegnr2,220001,,,,-888.88, "Name AZ Abschlagszahlung LVR 01/2016",,,EUR,,,,,,,,,,,R,,,
|
|
//G,0,4473,,,888.88,"Name AZ Abschlagszahlung LVR 05/2016",
|
|
|
|
return sb.ToString();
|
|
}
|
|
|
|
public static String GetSelbstzahlerString(DateTime date)
|
|
{
|
|
var dt = ExecuteQuery(GetSelbstzahlerSql(date), date);
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
if (sb.Length > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
for (int i = 0; i < row.ItemArray.Length; i++)
|
|
{
|
|
if (i > 0)
|
|
{
|
|
sb.Append(",");
|
|
}
|
|
//if (i == 32)
|
|
//{
|
|
// sb.AppendLine();
|
|
//}
|
|
|
|
var item = row[i];
|
|
bool quote = i == 15 || i == 38;
|
|
|
|
if (quote)
|
|
{
|
|
sb.Append('"');
|
|
sb.Append(item);
|
|
sb.Append('"');
|
|
}
|
|
else
|
|
{
|
|
sb.Append(item);
|
|
}
|
|
}
|
|
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
private static String GetDebitorenSql(DateTime date)
|
|
{
|
|
|
|
String sql = @"
|
|
SELECT
|
|
distinct
|
|
p.`FirstName`,
|
|
p.`LastName`,
|
|
c.DebitorNumber,
|
|
cb2sc.CustomerRefenrenceNumber,
|
|
a.Street,
|
|
a.PostalCode,
|
|
a.Town
|
|
FROM `customer` c
|
|
INNER JOIN `person` p ON p.`Oid` = c.`PersonOid`
|
|
INNER JOIN supportconcept sc on sc.CustomerOID = c.Oid
|
|
INNER JOIN costbearer2supportconcept cb2sc on cb2sc.SupportConceptOID = sc.Oid
|
|
LEFT JOIN Address a on p.addressOid = a.oid
|
|
WHERE c.isactive=1
|
|
order by p.`LastName`, p.FirstName
|
|
";
|
|
return sql;
|
|
}
|
|
|
|
private static String GetMonatlicheRechnungenSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
select
|
|
ib.InvoiceDate,
|
|
c.DebitorNumber,
|
|
c.CostCenter,
|
|
sip.Claim,
|
|
CONCAT(p.LastName, ', ', p.FirstName),
|
|
ib.invoicenumber,
|
|
(select t.name from team t inner join team2customer t2c on t2c.teamoid = t.oid where t2c.customeroid = c.Oid limit 1)
|
|
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
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
private static String GetAbschlagszahlungenSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
'F',
|
|
'0',
|
|
'005',
|
|
null,
|
|
'BEWO',
|
|
'DA',
|
|
':Abrechnungsmonat',
|
|
':Periode',
|
|
GetNextBelegnr(0),
|
|
GetNextBelegnr(1),
|
|
c.DebitorNumber,
|
|
null,
|
|
null,
|
|
null,
|
|
REPLACE(-1 * ROUND(at.`GesamtBetrag`, 2), ',', '.') as 'Abschlagszahlungen',
|
|
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', IF(cb2sc.CustomerRefenrenceNumber is null, '', cb2sc.CustomerRefenrenceNumber), ' Abschlagszahlung :PeriodeSlash ', org.Name) as 'Verwendung',
|
|
null,
|
|
null,
|
|
'EUR',
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
'R',
|
|
null,
|
|
null,
|
|
'G',
|
|
'0',
|
|
'xxxx',
|
|
null,
|
|
null,
|
|
REPLACE(ROUND(at.`GesamtBetrag`, 2), ',', '.') as 'Abschlagszahlungen2',
|
|
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', IF(cb2sc.CustomerRefenrenceNumber is null, '', cb2sc.CustomerRefenrenceNumber), ' Abschlagszahlung :PeriodeSlash ', org.Name) as 'Verwendung2',
|
|
null
|
|
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 at2.`CostBearer2SupportConceptOid`, SUM(at2.`Amount`) AS GesamtBetrag FROM `accountingtransaction` at2
|
|
WHERE at2.`BookingDate` >= ':Monat_Start' AND at2.`BookingDate` < ':Monat_End' GROUP BY at2.`CostBearer2SupportConceptOid`)
|
|
AS at ON at.`CostBearer2SupportConceptOid` = cb2sc.`Oid`
|
|
WHERE c.`IsActive` = 1 AND sc.`IsActive` = 1
|
|
and (at.`GesamtBetrag` is not null)
|
|
ORDER BY p.`LastName`, p.`FirstName`, cb2sc.`ApprovedStartDate`
|
|
";
|
|
return sql;
|
|
}
|
|
|
|
private static String GetSelbstzahlerSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
'F',
|
|
'0',
|
|
'005',
|
|
null,
|
|
'BEWO',
|
|
'DA',
|
|
':Abrechnungsmonat',
|
|
':Periode',
|
|
GetNextBelegnr(0),
|
|
GetNextBelegnr(1),
|
|
c.DebitorNumber,
|
|
null,
|
|
null,
|
|
null,
|
|
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',
|
|
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', IF(cb2sc.CustomerRefenrenceNumber is null, '', cb2sc.CustomerRefenrenceNumber), ' Abrechnung :PeriodeSlash ', org.Name) as 'Verwendung',
|
|
null,
|
|
null,
|
|
'EUR',
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
null,
|
|
'R',
|
|
null,
|
|
null,
|
|
'G',
|
|
'0',
|
|
'xxxx',
|
|
null,
|
|
null,
|
|
-1 * 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 'Betrag2',
|
|
CONCAT(p.`LastName`, ', ', p.`FirstName`, ' ', IF(cb2sc.CustomerRefenrenceNumber is null, '', cb2sc.CustomerRefenrenceNumber), ' Abrechnung :PeriodeSlash ', org.Name) as 'Verwendung2',
|
|
null
|
|
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 GROUP BY sr2.`CostBearer2SupportConceptOid`)
|
|
AS sr ON sr.`CostBearer2SupportConceptOid` = cb2sc.`Oid`
|
|
WHERE c.`IsActive` = 1 AND sc.`IsActive` = 1
|
|
and (sr.`GeleisteteFLM` is not null)
|
|
ORDER BY p.`LastName`, p.`FirstName`, cb2sc.`ApprovedStartDate`
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
public static DataTable ExecuteQuery(String sql, DateTime dt)
|
|
{
|
|
var newsql = sql;
|
|
newsql = newsql.Replace(":PeriodeSlash", String.Format("{0:MM/yyyy}", dt));
|
|
newsql = newsql.Replace(":Periode", String.Format("{0:yyyyMM}", 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];
|
|
}
|
|
|
|
|
|
|
|
|
|
//Buchung Abrechnung Stundenzettel
|
|
//F,0,050,, BEWO, DA,01012016,012016, iBelegNr1, Belegnr1,220001,,,,999.99, "Name AZ Abrechnung 01/2016 LVR",,, EUR,,,,,,,,,,, R,,,
|
|
//G,0,1695,,,-999.99,"Name AZ Abrechnung 01/2016 LVR",
|
|
|
|
|
|
|
|
//Buchung Abschlagszahlung
|
|
//F,0,050,, BEWO, DA,02012016,012016, iBelegNr2, Belegnr2,220001,,,,-888.88, "Name AZ Abschlagszahlung LVR 01/2016",,, EUR,,,,,,,,,,, R,,,
|
|
//G,0,4473,,,888.88,"Name AZ Abschlagszahlung LVR 05/2016",
|
|
|
|
|
|
//Buchung Abrechnung LVR / Selbstzahler
|
|
//F,0,050,, BEWO, DA,01022016,022016, iBelegNr3, Belegnr3,220001,,,,900.00, "Name AZ Abrechnung LVR + SZ 02/2016",,, EUR,,,,,,,,,,, R,,,
|
|
//G,0,1695,,,-800.00,"Name AZ Abrechnung 05/2016 LVR",
|
|
//G,0,1695,,,-100.00,"Name AZ Abrechnung 05/2016 SZ",
|
|
|
|
|
|
|
|
//schwarz = konstant
|
|
//rot = variabel
|
|
|
|
|
|
}
|
|
} |