399 lines
18 KiB
C#
399 lines
18 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Text;
|
|
using BeWo.Data.Access;
|
|
using BeWo.Data.Entities;
|
|
using BeWo.Report;
|
|
using BeWo.Report.ReportObjects;
|
|
using BeWo.Service.DCEntityMapper;
|
|
using BS.Shared.DataContracts;
|
|
using DevExpress.XtraPrinting;
|
|
using DevExpress.XtraReports.UI;
|
|
|
|
namespace BellaDonna.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);
|
|
var tableLL = ExecuteQuery(GetMonatlicheBetraegeLvrLwlSql(dt), dt);
|
|
|
|
query.FileName = String.Format("BUCH_BO_{0:yyyyMM}.scs", dt);
|
|
query.QueryResult = GetAbrechnungenString(dt, table, tableLL);
|
|
query.Attachments = CreateInvoiceFiles(table);
|
|
return query;
|
|
}
|
|
|
|
public static String GetAbrechnungenString(DateTime dt, DataTable table, DataTable tableLL)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
sb.AppendLine("\"MND\";500");
|
|
sb.AppendLine(String.Format("\"BZR\";{0:MMyyyy}", dt));
|
|
var s = GetMonatlicheBetraegeString(dt, tableLL);
|
|
if (!String.IsNullOrWhiteSpace(s))
|
|
{
|
|
sb.Append(s);
|
|
}
|
|
s = GetMonatlicheRechnungenString(dt, table);
|
|
if (!String.IsNullOrWhiteSpace(s))
|
|
{
|
|
if (sb.Length > 0)
|
|
sb.AppendLine();
|
|
|
|
sb.Append(s);
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
public static String GetMonatlicheBetraegeString(DateTime date, DataTable dt)
|
|
{
|
|
/* 'BCH', sip.Claim, '8106' as 'Gegenkonto', ib.invoicenumber, ib.InvoiceDate, org.DebitorNumber, '3170', org.Name, p.FirstName, p.LastName*/
|
|
/*'BCH', sum(qry.Betrag) AS Betrag, '8017' as 'Gegenkonto', qry.DebitorNumber, qry.CostCenter, org.Name, qry.Verwendung, qry.Oid*/
|
|
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);
|
|
string orgaName = "";
|
|
string orgaDeb = "";
|
|
if (row[5] != null)
|
|
{
|
|
orgaName = row[5].ToString();
|
|
}
|
|
sb.Append(String.Format("\"{0}\"", row[0]));
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0:0.00}", row[1]));
|
|
sb.Append(";;;");
|
|
string gKonto = GetErlöskonto(orgaName);
|
|
sb.Append(String.Format("{0}", gKonto)); //Gegenkonto
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0:yyyyMM}", lastDate)); // Belegnummer JJJJMM
|
|
sb.Append(";;");
|
|
sb.Append(String.Format("{0:ddMMyyyy}", lastDate)); //Simba nimmt nur Buchungen an, ReDatum = Abrechnungszeitraum
|
|
sb.Append(";;");
|
|
if (row[3] != null)
|
|
{
|
|
orgaDeb = row[3].ToString();
|
|
}
|
|
string debitor = GetDebitor(orgaDeb);
|
|
sb.Append(String.Format("{0}", debitor)); // Debitornummer Orga
|
|
sb.Append(";");
|
|
string costcenter = GetKostenstelle(orgaName, orgaDeb);
|
|
sb.Append(String.Format("{0}", costcenter)); // Kostenstelle Organisation
|
|
sb.Append(";;;");
|
|
sb.Append(String.Format("\"{0}\"", row[6]));
|
|
sb.Append(";\"EUR\";;;;;;;;;;");
|
|
//sb.Append(String.Format("{0}", row[3]));
|
|
sb.Append(";;;;;"); // bei Rechnungen kommt hier noch der Beleg als PDF
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
private static string GetDebitor(string orgaDeb)
|
|
{
|
|
if (orgaDeb.Length > 4)
|
|
{
|
|
return orgaDeb.Trim().Substring(0, 5);
|
|
}
|
|
else
|
|
{
|
|
return "";
|
|
}
|
|
}
|
|
|
|
private static string GetErlöskonto(string orgaName)
|
|
{
|
|
if (orgaName.Contains("LWL") || orgaName.Contains("Landschaftsverband Rheinland") || orgaName.Contains("LVR"))
|
|
{
|
|
return "8030";
|
|
}
|
|
else if (orgaName.Contains("Stadt Essen"))
|
|
{
|
|
return "8020";
|
|
}
|
|
else if (orgaName == "CSE")
|
|
{
|
|
return "8090";
|
|
}
|
|
else
|
|
{
|
|
return "8030";
|
|
}
|
|
}
|
|
|
|
private static string GetKostenstelle(string orgaName, string orgaDeb)
|
|
{
|
|
if (orgaDeb.Length > 10 && orgaDeb.ToLower().Contains("kst"))
|
|
{
|
|
return orgaDeb.Trim().Substring(orgaDeb.Length-2, 2);
|
|
}
|
|
else
|
|
{
|
|
if (orgaName.Contains("Jugendamt der Stadt Essen"))
|
|
{
|
|
return "34";
|
|
}
|
|
else if (orgaName == "CSE")
|
|
{
|
|
return "33";
|
|
}
|
|
else
|
|
{
|
|
return "32";
|
|
}
|
|
}
|
|
}
|
|
|
|
private static String GetMonatlicheBetraegeLvrLwlSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
'BCH',
|
|
sum(qry.Betrag) AS Betrag,
|
|
'8020' as 'Gegenkonto',
|
|
qry.DebitorNumber,
|
|
qry.CostCenter,
|
|
qry.Name,
|
|
qry.Verwendung,
|
|
qry.Oid,
|
|
sum(qry.FLS) AS FLS,
|
|
qry.Preis
|
|
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',
|
|
org.DebitorNumber,
|
|
c.CostCenter,
|
|
org.Name,
|
|
CONCAT(org.Name,', ', p.`LastName`, ', ', p.`FirstName`) as 'Verwendung',
|
|
cb2sc.Oid,
|
|
ROUND(sr.`GeleisteteFLM` / 60, 2) as 'FLS',
|
|
ROUND((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 'Preis'
|
|
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 c.`IsActive` = 1 AND sc.`IsActive` = 1 and (sr.`GeleisteteFLM` is not null) and (org.Name = 'Landschaftsverband Rheinland Dez 7' OR org.Name = 'LWL'))
|
|
UNION
|
|
(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 )) AS 'Betrag',
|
|
org.DebitorNumber,
|
|
c.CostCenter,
|
|
org.Name,
|
|
CONCAT(org.Name,', ', p.`LastName`, ', ', p.`FirstName`) as 'Verwendung',
|
|
cb2sc.Oid,
|
|
ROUND(0.8 * sr.`GeleisteteFLM` / 60, 2) as 'FLS',
|
|
ROUND((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 'Preis'
|
|
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 c.`IsActive` = 1 AND sc.`IsActive` = 1 and (sr.`GeleisteteFLM` is not null) and (org.Name = 'Landschaftsverband Rheinland Dez 7' OR org.Name = 'LWL'))
|
|
)qry
|
|
GROUP BY qry.OID
|
|
ORDER BY qry.Verwendung
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
public static String GetMonatlicheRechnungenString(DateTime date, DataTable dt)
|
|
{
|
|
/* 'BCH', sip.Claim, '8106' as 'Gegenkonto', ib.invoicenumber, ib.InvoiceDate, org.DebitorNumber, '3170', org.Name, p.FirstName, p.LastName*/
|
|
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);
|
|
string orgaName = "";
|
|
string orgaDeb = "";
|
|
if (row[7] != null)
|
|
{
|
|
orgaName = row[7].ToString();
|
|
}
|
|
if (row[5] != null)
|
|
{
|
|
orgaDeb = row[5].ToString();
|
|
}
|
|
|
|
sb.Append(String.Format("\"{0}\"", row[0]));
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0:0.00}", row[1]));
|
|
sb.Append(";;;");
|
|
string gKonto = GetErlöskonto(orgaName);
|
|
sb.Append(String.Format("{0}", gKonto)); //Gegenkonto
|
|
sb.Append(";");
|
|
sb.Append(String.Format("{0}", row[3]));
|
|
sb.Append(";;");
|
|
sb.Append(String.Format("{0:ddMMyyyy}", lastDate)); //Simba nimmt nur Buchungen an, ReDatum = Abrechnungszeitraum
|
|
//sb.Append(String.Format("{0:ddMMyyyy}", row[4]));
|
|
sb.Append(";;");
|
|
string debitor = GetDebitor(orgaDeb);
|
|
sb.Append(String.Format("{0}", debitor)); // Debitornummer Orga
|
|
sb.Append(";");
|
|
string costcenter = GetKostenstelle(orgaName, orgaDeb);
|
|
sb.Append(String.Format("{0}", costcenter)); // Kostenstelle Organisation
|
|
sb.Append(";;;");
|
|
sb.Append(String.Format("\"{0}, {1} {2}\"", row[7], row[8], row[9]));
|
|
sb.Append(";\"EUR\";;;;;;;;;;");
|
|
sb.Append(String.Format("{0}", row[3]));
|
|
sb.Append(";;;;");
|
|
sb.Append(String.Format("\"Rechnung {0}.pdf\"", row[3]));
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
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 ro = ServiceInvoiceRO.Create(idc);
|
|
|
|
if (idc.InvoiceBase.RecipientOrganisation == "Landschaftsverband Rheinland" || idc.InvoiceBase.RecipientOrganisation.Contains("Amt für Soziales "))
|
|
{
|
|
var anhang = new Rechnung67();
|
|
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);
|
|
}
|
|
else
|
|
{
|
|
var anhang = new RechnungFlex();
|
|
anhang.SetReportDataSource(ro);
|
|
anhang.CreateDocument();
|
|
pdf.FileName = "Rechnung " + ro.InvoiceNumber + ".pdf";
|
|
using (var ms = new MemoryStream())
|
|
{
|
|
anhang.ExportToPdf(ms);
|
|
pdf.BinaryData = ms.ToArray();
|
|
}
|
|
liste.Add(pdf);
|
|
}
|
|
}
|
|
}
|
|
return liste;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
// continue - auch wenn gar nichts stimmt
|
|
}
|
|
return null;
|
|
}
|
|
|
|
private static String GetMonatlicheRechnungenSql(DateTime date)
|
|
{
|
|
//normale Einzelrechnungen u AllgRechnungen
|
|
String sql = @"
|
|
select
|
|
'BCH',
|
|
sip.Claim,
|
|
'8030' as 'Gegenkonto',
|
|
ib.invoicenumber,
|
|
ib.InvoiceDate,
|
|
org.DebitorNumber,
|
|
'32',
|
|
org.Name,
|
|
p.FirstName,
|
|
p.LastName,
|
|
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.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End' and ib.IsActive = 1 AND ib.`IsExported` = 0
|
|
UNION
|
|
SELECT DISTINCT
|
|
'BCH',
|
|
(select sum(ii.AmountTotal) from invoiceitem ii where ii.invoicebaseoid = ib.oid),
|
|
'8030' as 'Gegenkonto',
|
|
ib.invoicenumber,
|
|
ib.InvoiceDate,
|
|
org.DebitorNumber,
|
|
'32',
|
|
ib.RecipientOrganisation,
|
|
'',
|
|
'',
|
|
ib.Oid
|
|
from invoicebase ib
|
|
inner join organisation org on org.costbeareroid = ib.RecipientOrganisationOid
|
|
WHERE ib.`AccountingPeriodEnd` >= ':Monat_Start' AND ib.`AccountingPeriodEnd` < ':Monat_End' and ib.IsActive = 1
|
|
and ib.Type = 2 AND ib.`IsExported` = 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];
|
|
}
|
|
}
|
|
} |