411 lines
15 KiB
C#
411 lines
15 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.IO;
|
|
using System.Text;
|
|
using System.Xml;
|
|
using BeWo.Data.Access;
|
|
using BS.Shared.DataContracts;
|
|
|
|
namespace SabGmbH.Export
|
|
{
|
|
public class EGeckoExporter
|
|
{
|
|
|
|
public static QueryDC CreateQuery(QueryDC query)
|
|
{
|
|
DateTime dt = DateTime.Now;
|
|
DateTime.TryParse(query.Parameter[0].Value.ToString(), out dt);
|
|
|
|
query.FileName = String.Format("Export{0:yyyyMM}.xml", dt);
|
|
query.QueryResult = GetAbrechnungenString(dt);
|
|
|
|
|
|
return query;
|
|
}
|
|
|
|
public static String GetAbrechnungenString(DateTime date)
|
|
{
|
|
return GetMonatlicheRechnungenString(date);
|
|
|
|
|
|
//if (!String.IsNullOrWhiteSpace(s))
|
|
//{
|
|
// sb.Append(s);
|
|
//}
|
|
//if (sb.Length > 0)
|
|
// sb.AppendLine();
|
|
|
|
//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();
|
|
}
|
|
|
|
public static String GetMonatlicheRechnungenString(DateTime date)
|
|
{
|
|
var dt = ExecuteQuery(GetMonatlicheRechnungenSql(date), date);
|
|
|
|
|
|
XmlDocument doc = new XmlDocument();
|
|
|
|
XmlDeclaration xmlDeclaration = doc.CreateXmlDeclaration("1.0", "iso-8859-1", null);
|
|
XmlElement root = doc.DocumentElement;
|
|
doc.InsertBefore(xmlDeclaration, root);
|
|
|
|
|
|
XmlElement belege = doc.CreateElement("FibuBelege");
|
|
doc.AppendChild(belege);
|
|
|
|
AppendXmlElement(doc, belege, "firmaNr", "02");
|
|
AppendXmlElement(doc, belege, "datumFormat", "ddMMyy");
|
|
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
belege.AppendChild(CreateBelegElement(doc, row));
|
|
|
|
}
|
|
//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",
|
|
|
|
String formattedXml = "";
|
|
using (var sw = new StringWriter())
|
|
{
|
|
doc.Save(sw);
|
|
formattedXml = sw.ToString();
|
|
}
|
|
|
|
return formattedXml;
|
|
}
|
|
|
|
private static XmlElement CreateBelegElement(XmlDocument doc, DataRow row)
|
|
{
|
|
/*
|
|
0: ib.InvoiceDate,
|
|
1: c.DebitorNumber,
|
|
2: c.CostCenter,
|
|
3: sip.Claim,
|
|
4: CONCAT(p.LastName, ', ', p.FirstName),
|
|
5: ib.invoicenumber,
|
|
(select t.name from team t inner join team2customer t2c on t2c.teamoid = t.oid where t2c.customeroid = c.Oid limit 1)
|
|
*/
|
|
var beleg = doc.CreateElement("FibuBeleg");
|
|
|
|
var kopf = doc.CreateElement("Belegkopf");
|
|
var posList = doc.CreateElement("FibuBelegpositionen");
|
|
|
|
beleg.AppendChild(kopf);
|
|
beleg.AppendChild(posList);
|
|
|
|
DateTime belegDatum = (DateTime) row[0];
|
|
|
|
AppendXmlElement(doc, kopf, "belegart", "ra");
|
|
AppendXmlElement(doc, kopf, "belegnummer", row[5] as String);
|
|
AppendXmlElement(doc, kopf, "referenznr", row[5] as String);
|
|
AppendXmlElement(doc, kopf, "belegdatum", String.Format("{0:ddMMyy}", belegDatum));
|
|
AppendXmlElement(doc, kopf, "belegperiode", String.Format("{0:yyyy}/{0:MM}", belegDatum));
|
|
AppendXmlElement(doc, kopf, "belegwaehrung", "EUR");
|
|
AppendXmlElement(doc, kopf, "belegwaehrungskurs", "1.00000");
|
|
AppendXmlElement(doc, kopf, "bruttoErfassung", "j");
|
|
AppendXmlElement(doc, kopf, "buchungstext", row[4] as String);
|
|
|
|
posList.AppendChild(CreateBelegpositionDebitorElement(doc, row));
|
|
posList.AppendChild(CreateBelegpositionSachkontoElement(doc, row));
|
|
|
|
return beleg;
|
|
}
|
|
|
|
private static XmlElement CreateBelegpositionDebitorElement(XmlDocument doc, DataRow row)
|
|
{
|
|
var pos = doc.CreateElement("FibuBelegposition");
|
|
AppendXmlElement(doc, pos, "buchungsschluessel", "210");
|
|
AppendXmlElement(doc, pos, "kontonummer", row[1] as String);
|
|
AppendXmlElement(doc, pos, "betrag", String.Format("{0:0.00}", row[3]));
|
|
|
|
var opinfos = doc.CreateElement("OpInfos");
|
|
var opangaben = doc.CreateElement("OpAngaben");
|
|
|
|
pos.AppendChild(opinfos);
|
|
opinfos.AppendChild(opangaben);
|
|
|
|
AppendXmlElement(doc, opangaben, "opNr", row[5] as String);
|
|
AppendXmlElement(doc, opangaben, "opText", row[4] as String);
|
|
|
|
return pos;
|
|
}
|
|
|
|
private static XmlElement CreateBelegpositionSachkontoElement(XmlDocument doc, DataRow row)
|
|
{
|
|
var pos = doc.CreateElement("FibuBelegposition");
|
|
AppendXmlElement(doc, pos, "buchungsschluessel", "150");
|
|
AppendXmlElement(doc, pos, "kontonummer", "8018"); // Welches Sachkonto?
|
|
AppendXmlElement(doc, pos, "betrag", String.Format("{0:0.00}", row[3]));
|
|
|
|
var kposliste = doc.CreateElement("FibuKoreBelegpositionen");
|
|
var kpos = doc.CreateElement("FibuKoreBelegposition");
|
|
|
|
pos.AppendChild(kposliste);
|
|
kposliste.AppendChild(kpos);
|
|
|
|
AppendXmlElement(doc, kpos, "kostenart", "8018"); // Welche Kostenart?
|
|
AppendXmlElement(doc, kpos, "kostenstelle", "7029"); // Welche Kostenstelle?
|
|
AppendXmlElement(doc, kpos, "nettobetrag", String.Format("{0:0.00}", row[3]));
|
|
|
|
return pos;
|
|
}
|
|
|
|
private static XmlElement AppendXmlElement(XmlDocument doc, XmlElement parent, string tag, string text)
|
|
{
|
|
XmlElement e = doc.CreateElement(tag);
|
|
e.InnerText = text;
|
|
parent.AppendChild(e);
|
|
return e;
|
|
}
|
|
|
|
public static String GetAbschlagszahlungenString(DateTime date)
|
|
{
|
|
var dt = ExecuteQuery(GetAbschlagszahlungenSql(date), date);
|
|
|
|
|
|
//Dictionary<long, int> oid2Index = new Dictionary<long, int>();
|
|
//foreach (DataRow row in dt.Rows)
|
|
//{
|
|
// var oid = (long)row[0];
|
|
// var belegnr = row[2] as String;
|
|
|
|
// if (belegnr.EndsWith("-a"))
|
|
// {
|
|
// if (!oid2Index.ContainsKey(oid))
|
|
// {
|
|
// oid2Index.Add(oid, 0);
|
|
// }
|
|
|
|
// oid2Index[oid]++;
|
|
// }
|
|
//}
|
|
|
|
Dictionary<long, int> oid2Index = new Dictionary<long, int>();
|
|
StringBuilder sb = new StringBuilder();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
if (sb.Length > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
var oid = (long)row[0];
|
|
|
|
for (int i = 1; i < row.ItemArray.Length; i++)
|
|
{
|
|
|
|
|
|
if (i > 1)
|
|
{
|
|
sb.Append(";");
|
|
}
|
|
|
|
if (i == 2)
|
|
{
|
|
var belegnr = row[2] as String;
|
|
|
|
if (belegnr.EndsWith("-a"))
|
|
{
|
|
belegnr = belegnr.Replace("-a", "-");
|
|
|
|
if (!oid2Index.ContainsKey(oid))
|
|
{
|
|
oid2Index.Add(oid, 0);
|
|
}
|
|
oid2Index[oid]++;
|
|
|
|
belegnr += String.Format("{0}", oid2Index[oid]);
|
|
}
|
|
|
|
sb.Append(belegnr);
|
|
}
|
|
else
|
|
{
|
|
var item = row[i];
|
|
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(";");
|
|
}
|
|
|
|
var item = row[i];
|
|
sb.Append(item);
|
|
}
|
|
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
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 GetMonatlicheAbrechnungLvrSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
':Abrechnungsmonat',
|
|
':Belegnr',
|
|
c.DebitorNumber as 'Konto Soll',
|
|
'' as 'Kostenstelle Soll',
|
|
'S86400' as 'Konto Haben',
|
|
c.CostCenter as 'Kostenstelle Haben',
|
|
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('Leistungsabrechnung, ', p.`FirstName`, ' ', p.`LastName`) as 'Buchungstext'
|
|
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` <> 0 AND sc.`IsActive` <> 0
|
|
and (sr.`GeleisteteFLM` is not null)
|
|
HAVING Betrag > 0
|
|
ORDER BY p.`LastName`, p.`FirstName`, cb2sc.`ApprovedStartDate`
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
private static String GetAbschlagszahlungenSql(DateTime date)
|
|
{
|
|
//"Abrechnung" = OID -ab
|
|
// "Monatliche Abschlagszahlungen" OID 77 "-a"
|
|
String sql = @"
|
|
SELECT
|
|
c.Oid,
|
|
date_format(If(at.ValidityDate is null, at.Bookingdate, at.ValidityDate), '%d.%m.%Y') as 'Belegdatum',
|
|
CASE ve.Oid
|
|
WHEN 78
|
|
THEN ':Belegnr-ab'
|
|
ELSE ':Belegnr-a'
|
|
End,
|
|
'S13690' as 'Konto Soll',
|
|
'' as 'Kostenstelle Soll',
|
|
c.DebitorNumber as 'Konto Haben',
|
|
'' as 'Kostenstelle Haben',
|
|
Round(at.Amount, 2) as 'Betrag',
|
|
CONCAT('Abschlagszahlung, ', p.FirstName, ' ', p.LastName, ', ', at.notice) as 'Buchungstext'
|
|
from accountingtransaction at
|
|
join costbearer2supportconcept c2s on c2s.oid = at.costbearer2supportconceptoid
|
|
join supportconcept sc on sc.oid = c2s.supportconceptoid
|
|
join customer c on sc.customeroid = c.oid
|
|
join person p on c.personoid = p.oid
|
|
left join valuelistentry2object v2o on v2o.objectoid = at.oid and v2o.objecttid = 26
|
|
left join valuelistentry ve on v2o.valuelistentryoid = ve.oid
|
|
WHERE If (at.ValidityDate is null, at.Bookingdate, at.ValidityDate) >= ':Monat_Start' AND If (at.ValidityDate is null, at.Bookingdate, at.ValidityDate) < ':Monat_End' and c.`IsActive` <> 0 AND sc.`IsActive` <> 0
|
|
ORDER BY p.`LastName`, p.`FirstName`
|
|
";
|
|
return sql;
|
|
}
|
|
|
|
private static String GetSelbstzahlerSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
date_format(ib.InvoiceDate, '%d.%m.%Y') as 'Belegdatum',
|
|
ib.InvoiceNumber as 'Belegnummer',
|
|
c.DebitorNumber as 'Konto Soll',
|
|
'' as 'Kostenstelle Soll',
|
|
'S86400' as 'Konto Haben',
|
|
c.CostCenter as 'Kostenstelle Haben',
|
|
Round(ii.AmountTotal, 2) as 'Betrag',
|
|
CONCAT('Eigenanteil, ', p.`FirstName`, ' ', p.`LastName`) as 'Buchungstext'
|
|
from invoicebase ib
|
|
join invoiceitem ii on ii.invoicebaseoid = ib.oid
|
|
join customer c on c.oid = ib.recipientcustomeroid
|
|
join `person` p on c.`PersonOid` = p.`Oid`
|
|
where ib.`InvoiceDate` >= ':Monat_Start' AND ib.`InvoiceDate` < ':Monat_End'
|
|
and ib.type = 0 and ib.isactive = 1 and c.isactive <> 0
|
|
";
|
|
|
|
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];
|
|
}
|
|
|
|
|
|
}
|
|
} |