275 lines
12 KiB
C#
275 lines
12 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.Service.DCEntityMapper;
|
|
using BS.Shared;
|
|
using BS.Shared.DataContracts;
|
|
using BS.Shared.Extensions;
|
|
|
|
namespace AkggMid.Export
|
|
{
|
|
public class ExactExporter
|
|
{
|
|
public static QueryDC CreateQuery(QueryDC query)
|
|
{
|
|
DateTime dt = DateTime.Now;
|
|
DateTime.TryParse(query.Parameter[0].Value.ToString(), out dt);
|
|
|
|
query.FileName = String.Format("Exact Export {0:yyyyMM}.csv", dt);
|
|
query.QueryResult = GetAbrechnungenString(dt);
|
|
return query;
|
|
}
|
|
|
|
public static String GetAbrechnungenString(DateTime date)
|
|
{
|
|
StringBuilder sb = new StringBuilder();
|
|
//Überschriften sind nicht nötig
|
|
//sb.AppendLine("regelnumber, fakt_code, debnr, refer, refer1, refer2, refer3, ordernr, orddat, magcode, ex_artcode, verteg, kstplcode, selcode, inv_in_vv, " +
|
|
// "nettoprijs, valcode, koers, betcond, levwijze, vrachtkost, orderkost, fakt_kort, tot_bdr, colli, bruto_gew, netto_gew, afldat, adr_code, -, " +
|
|
// "bruto / netto, -, -, -, -, -, -, -, -, -, -, fak_soort, ord_fakt, deb_id, fiattering, fakdebnr, -, faknr, kstplcode");
|
|
//sb.AppendLine("regelnumber,-,-,-,-,-,-,-,-,magcode,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,-,afldat,afl_week,artcode,ar_soort,oms45,aantal," +
|
|
// "btw_code,prijslijst,korting,prijs,prijs_n,kstdrcode,text,reknr,-,-,-,-,-,kstplcode,-,");
|
|
|
|
var s = GetMonatlicheRechnungenString(date);
|
|
if (!String.IsNullOrWhiteSpace(s))
|
|
{
|
|
sb.Append(s);
|
|
}
|
|
return sb.ToString();
|
|
}
|
|
|
|
public static String GetMonatlicheRechnungenString(DateTime date)
|
|
{
|
|
var sql = GetMonatlicheRechnungenSql(date);
|
|
|
|
/*
|
|
Round(sip.Claim, 2),
|
|
c.DebitorNumber,
|
|
CONCAT( p.`LastName`, '; ' p.`FirstName`, ' SA :PeriodeSlash') as 'Verwendung',
|
|
ib.InvoiceDate,
|
|
ib.invoicenumber,
|
|
ib.Oid AS InvoiceBaseOid,
|
|
sip.Oid AS sipOID,
|
|
c.CostCenter,
|
|
c2s.Oid,
|
|
ib.InvoiceId,
|
|
org.DebitorNumber - 10
|
|
*/
|
|
|
|
var dt = ExecuteQuery(sql, date, null);
|
|
|
|
StringBuilder sb = new StringBuilder();
|
|
foreach (DataRow row in dt.Rows)
|
|
{
|
|
DateTime lastDate = new DateTime(date.Year, date.Month, 1);
|
|
lastDate = lastDate.AddMonths(1).AddDays(-1);
|
|
|
|
//sb.Append(String.Format("{0:ddMM}", lastDate));
|
|
sb.Append("0,V,111,");
|
|
sb.Append(String.Format("{0}, ", lastDate.Month.ToString())); //Period
|
|
sb.Append(String.Format("{0:yyyy}, , ", lastDate)); //Financial year
|
|
sb.Append(String.Format("{0}, ", row[2])); //Description
|
|
sb.Append(String.Format("{0:ddMMyyyy}, , ", lastDate));
|
|
sb.Append(String.Format("{0}, , , ", row[10])); //Debtor
|
|
sb.Append(String.Format("{0:0.00}, ,", row[0].ToString().Replace(",", "."))); //amount
|
|
sb.Append("EUR, 1,B ,0.00,");
|
|
sb.Append(String.Format("{0:ddMMyyyy},", row[3]));
|
|
sb.Append(" , , , ,sa ,I , , , , , , ,N , , , , , , , , ,");
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
|
|
string ibOid = String.Format("{0}", row[5]);
|
|
string sipOid = String.Format("{0}", row[6]);
|
|
|
|
if (!String.IsNullOrWhiteSpace(ibOid))
|
|
{
|
|
sql = GetInvoiceItems();
|
|
var sdt = ExecuteQuery(sql, date, ibOid);
|
|
if (sdt.Rows.Count != 0)
|
|
{
|
|
//foreach (DataRow srow in sdt.Rows)
|
|
for (int i = 0; i < sdt.Rows.Count; i++)
|
|
{
|
|
DataRow srow = sdt.Rows[i];
|
|
if (!srow[0].ToString().StartsWith("0,00"))
|
|
{
|
|
sb.Append(i + 1);
|
|
sb.Append(",V,111,");
|
|
sb.Append(String.Format("{0}, ", lastDate.Month.ToString())); //Period
|
|
sb.Append(String.Format("{0:yyyy}, , ", lastDate)); //Financial year
|
|
sb.Append(String.Format("{0}, ", row[2])); //Description
|
|
sb.Append(String.Format("{0:ddMMyyyy},", lastDate));
|
|
if (srow[4] != null && srow[4].ToString() == "Klassenfahrt")
|
|
{
|
|
sb.Append("8145,");
|
|
}
|
|
else
|
|
{
|
|
sb.Append("8140,");
|
|
}
|
|
sb.Append(String.Format("{0}, ,", row[10])); //Debtor
|
|
sb.Append(String.Format("{0},", row[4])); //Invoicenr
|
|
sb.Append(String.Format("{0:0.00}, ,", srow[2].ToString().Replace(",", "."))); //amount
|
|
sb.Append("EUR, 1, , , ,");
|
|
sb.Append(" ,0 ,0.00 , , , , ,6100 , ,");
|
|
sb.Append(String.Format("{0:0.00} ,", srow[0].ToString().Replace(",", ".")));
|
|
sb.Append(" , , K, , , , , , , , ,");
|
|
if (sb.Length > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
else
|
|
{
|
|
if (!String.IsNullOrWhiteSpace(sipOid))
|
|
{
|
|
sql = GetInvoiceItemsSIP();
|
|
sdt = ExecuteQuery(sql, date, sipOid);
|
|
if (sdt.Rows.Count != 0)
|
|
{
|
|
for (int i = 0; i < sdt.Rows.Count; i++)
|
|
{
|
|
DataRow srow = sdt.Rows[i];
|
|
if (!srow[0].ToString().StartsWith("0,00"))
|
|
{
|
|
sb.Append(i + 1);
|
|
sb.Append(",V,111,");
|
|
sb.Append(String.Format("{0}, ", lastDate.Month.ToString())); //Period
|
|
sb.Append(String.Format("{0:yyyy}, , ", lastDate)); //Financial year
|
|
sb.Append(String.Format("{0}, ", row[2])); //Description
|
|
sb.Append(String.Format("{0:ddMMyyyy},", lastDate));
|
|
if (srow[3] != null && srow[3].ToString() == "Klassenfahrt")
|
|
{
|
|
sb.Append("8145,");
|
|
}
|
|
else
|
|
{
|
|
sb.Append("8140,");
|
|
}
|
|
sb.Append(String.Format("{0}, ,", row[10])); //Debtor
|
|
sb.Append(String.Format("{0},", row[4])); //Invoicenr
|
|
sb.Append(String.Format("{0:0.00}, ,", srow[2].ToString().Replace(",", "."))); //amount
|
|
sb.Append("EUR, 1, , , ,");
|
|
sb.Append(" ,0 ,0.00 , , , , ,6100 , ,");
|
|
sb.Append(String.Format("{0:0.00} ,", srow[0].ToString().Replace(",", ".")));
|
|
sb.Append(" , , K, , , , , , , , ,");
|
|
if (sb.Length > 0)
|
|
{
|
|
sb.AppendLine();
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
return sb.ToString();
|
|
}
|
|
|
|
|
|
|
|
private static Dictionary<long, decimal> CreateHourlyRateDict(List<long> catOids, DateTime date)
|
|
{
|
|
Dictionary<long, decimal> dict = new Dictionary<long, decimal>();
|
|
|
|
foreach (var oid in catOids)
|
|
{
|
|
var cat = DAOFactory.GenericDAO.LoadByID<ServiceCategory>(oid);
|
|
var crps = MapperFactory.CostRatePeriodDC_CostRatePeriod.MapToNewDCs(cat.CostRatePeriods);
|
|
var cp = crps.GetCostRatePeriodForDate(CostRatePeriodType.AmountOfMoney, date);
|
|
if (cp != null && cp.CostRateValue.HasValue)
|
|
dict.Add(oid, cp.CostRateValue.Value);
|
|
}
|
|
|
|
return dict;
|
|
}
|
|
|
|
private static String GetMonatlicheRechnungenSql(DateTime date)
|
|
{
|
|
String sql = @"
|
|
select
|
|
Round(sip.Claim, 2),
|
|
c.DebitorNumber,
|
|
CONCAT( p.`LastName`, '; ', p.`FirstName`, ' SA :PeriodeSlash') as 'Verwendung',
|
|
ib.InvoiceDate,
|
|
ib.invoicenumber,
|
|
ib.Oid AS InvoiceBaseOid,
|
|
sip.Oid AS sipOID,
|
|
c.CostCenter,
|
|
c2s.Oid,
|
|
ib.InvoiceId,
|
|
org.DebitorNumber
|
|
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
|
|
order by ib.invoicenumber
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
private static String GetInvoiceItems()
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
Round(UnitCount, 2),
|
|
Round(AmountPerUnit, 2),
|
|
Round(AmountTotal, 2),
|
|
ItemDescription
|
|
FROM
|
|
invoiceitem
|
|
WHERE invoicebaseoid = ':ibOid'
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
private static String GetInvoiceItemsSIP()
|
|
{
|
|
String sql = @"
|
|
SELECT
|
|
Round(UnitCount, 2),
|
|
Round(AmountPerUnit, 2),
|
|
Round(AmountTotal, 2),
|
|
ItemDescription
|
|
FROM
|
|
invoiceitem
|
|
WHERE serviceinvoiceperiodoid = ':ibOid'
|
|
";
|
|
|
|
return sql;
|
|
}
|
|
|
|
public static DataTable ExecuteQuery(String sql, DateTime dt, String ibOid)
|
|
{
|
|
var newsql = sql;
|
|
newsql = newsql.Replace(":Abrechnungsmonat", String.Format("{0:dd.MM.yyyy}", dt));
|
|
newsql = newsql.Replace(":PeriodeSlash", String.Format("{0:MM}/{0:yyyy}", dt));
|
|
newsql = newsql.Replace(":Belegnr", String.Format("{0:yyMM}", dt));
|
|
newsql = newsql.Replace(":Monat_MM", String.Format("{0:MM}", 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));
|
|
newsql = newsql.Replace(":ibOid", ibOid);
|
|
|
|
return DAOFactory.AdoDAO.ExecuteQuery(newsql).Tables[0];
|
|
}
|
|
}
|
|
} |