private void BeregnKontingent(dbData3060DataContext p_dbData3060, DateTime startdato)
{
decimal kont = 0;
DateTime KontingentFradato = startdato;
DateTime KontingentTildato = KontingentFradato.AddYears(1).AddDays(-1);
var qry1 = from k in p_dbData3060.TblKontingent
where k.Startdato.Date >= KontingentFradato.Date || k.Slutdato.Date >= KontingentFradato.Date
orderby k.Startdato
select k;
int antal = qry1.Count();
int n = 1;
foreach (var k in qry1)
{
if (n == 1)
{
kont += k.Aarskontingent;
}
n++;
}
this.Kontingent = kont;
this.KontingentTildato = KontingentTildato;
}
public void imapSaveAttachedFile(dbData3060DataContext p_dbData3060, string filename, byte[] data, bool bTilPBS)
{
string local_filename = filename.Replace('.', '_') + ".txt";
MimeMessage message = new MimeMessage();
TextPart body;
message.To.Add(new MailboxAddress(@"Regnskab Puls3060", @"*****@*****.**"));
message.From.Add(new MailboxAddress(@"Regnskab Puls3060", @"*****@*****.**"));
if (bTilPBS)
{
message.Subject = "Til PBS: " + local_filename;
body = new TextPart("plain")
{
Text = @"Til PBS: " + local_filename
};
}
else
{
message.Subject = "Fra PBS: " + local_filename;
body = new TextPart("plain")
{
Text = @"Fra PBS: " + local_filename
};
}
var attachment = new MimePart("text", "plain")
{
ContentObject = new ContentObject(new MemoryStream(data), ContentEncoding.Default),
ContentDisposition = new ContentDisposition(ContentDisposition.Attachment),
ContentTransferEncoding = ContentEncoding.Base64,
FileName = local_filename
};
var multipart = new Multipart("mixed");
multipart.Add(body);
multipart.Add(attachment);
message.Body = multipart;
using (var client = new ImapClient())
{
client.Connect("imap.gigahost.dk", 993, true);
client.AuthenticationMechanisms.Remove("XOAUTH");
client.Authenticate(clsApp.GigaHostImapUser, clsApp.GigaHostImapPW);
var PBS = client.GetFolder("INBOX.PBS");
PBS.Open(FolderAccess.ReadWrite);
PBS.Append(message);
PBS.Close();
client.Disconnect(true);
}
}
public void readaftale042(dbData3060DataContext p_dbData3060, string sektion, string transkode, string rec)
{
// -- pbssektionnr
// -- pbstranskode
// - transkode 0230, aktiv aftale
// - transkode 0231, tilgang af ny betalingsaftale
// - transkode 0232, aftale afmeldt af pengeinstitut
// - transkode 0233, aftale afmeldt af kreditor
// - transkode 0234, aftale afmeldt af betalingsservice
m_rec_aftalelin = new Tblaftalelin
{
Pbssektionnr = sektion,
Pbstranskode = transkode
};
// Medlem Nr
try
{
m_rec_aftalelin.Nr = int.Parse(rec.Substring(33, 7));
}
catch
{
m_rec_aftalelin.Nr = 0;
}
// debitorkonto
m_rec_aftalelin.Debitorkonto = rec.Substring(25, 15);
// debgrpnr
m_rec_aftalelin.Debgrpnr = rec.Substring(20, 5);
// aftalenr
m_rec_aftalelin.Aftalenr = int.Parse(rec.Substring(40, 9));
// aftalestartdato
if (rec.Substring(49, 6) != "000000")
{
m_rec_aftalelin.Aftalestartdato = DateTime.Parse("20" + rec.Substring(53, 2) + "-" + rec.Substring(51, 2) + "-" + rec.Substring(49, 2));
}
else
{
m_rec_aftalelin.Aftalestartdato = null;
};
// aftaleslutdato
if (rec.Substring(55, 6) != "000000")
{
m_rec_aftalelin.Aftaleslutdato = DateTime.Parse("20" + rec.Substring(59, 2) + "-" + rec.Substring(57, 2) + "-" + rec.Substring(55, 2));
}
else
{
m_rec_aftalelin.Aftaleslutdato = null;
};
// Add tblaftalelin
m_rec_frapbs.Tblaftalelin.Add(m_rec_aftalelin);
}
public clsSFTP(dbData3060DataContext p_dbData3060)
{
m_rec_sftp = (from s in p_dbData3060.Tblsftp where s.Navn == "Produktion" select s).First();
Console.WriteLine(string.Format("host={0}, port={1}, user={2}", m_rec_sftp.Host, int.Parse(m_rec_sftp.Port), m_rec_sftp.User), "SFTP ConnectionString");
byte[] bPK = Encoding.UTF8.GetBytes(m_rec_sftp.Certificate);
MemoryStream mPK = new MemoryStream(bPK);
PrivateKeyFile PK = new PrivateKeyFile(mPK, m_rec_sftp.Pincode);
m_sftp = new SftpClient(m_rec_sftp.Host, int.Parse(m_rec_sftp.Port), m_rec_sftp.User, PK);
m_sftp.Connect();
Console.WriteLine("SFTP Connected");
}
public string AddPbcnetRecords(dbData3060DataContext p_dbData3060, string delsystem, int leveranceid, int pbsfilesid)
{
int antal;
int pbcnetrecords;
DateTime transmisionsdato;
int idlev;
string filename;
string rec;
pbcnetrecords = (from h in p_dbData3060.Tblpbsfile
where h.Pbsfilesid == pbsfilesid & (h.Seqnr == 0 | h.Seqnr == 9999)
select h).Count();
if (pbcnetrecords == 0)
{
// Find antal records
antal = (from h in p_dbData3060.Tblpbsfile
where h.Pbsfilesid == pbsfilesid & h.Seqnr != 0 & h.Seqnr != 9999
select h).Count();
transmisionsdato = DateTime.Now;
idlev = p_dbData3060.nextval_v2("idlev");
Tblpbsfilename rec_pbsfiles = (from h in p_dbData3060.Tblpbsfilename where h.Id == pbsfilesid select h).First();
rec = write00(delsystem, transmisionsdato, idlev, leveranceid);
Tblpbsfile rec_pbsfile = new Tblpbsfile {
Seqnr = 0, Data = rec
};
rec_pbsfiles.Tblpbsfile.Add(rec_pbsfile);
rec = write90(delsystem, transmisionsdato, idlev, leveranceid, antal);
rec_pbsfile = new Tblpbsfile {
Seqnr = 9999, Data = rec
};
rec_pbsfiles.Tblpbsfile.Add(rec_pbsfile);
p_dbData3060.SaveChanges();
filename = "D";
filename += lpad(String.Format("{0:yyMMdd}", transmisionsdato), 6, '?');
filename += lpad(idlev, 2, '0');
filename += ".";
filename += rpad(delsystem, 3, '_');
}
else
{
filename = "Unknown";
}
return(filename);
}
public clsUniconta(dbData3060DataContext p_dbData3060, CrudAPI api)
{
m_dbData3060 = p_dbData3060;
m_api = api;
var task = api.Query <CompanyFinanceYear>();
task.Wait();
var cols = task.Result;
foreach (var col in cols)
{
if (col._Current)
{
m_CurrentCompanyFinanceYear = col;
}
}
}
public Tblbetlin Update_Nr_Faknr(Tblbetlin m_rec_betlin, dbData3060DataContext p_dbData3060)
{
Tblbetlin m_rec_betlin_save = m_rec_betlin;
try
{
var xFaknr = m_rec_betlin.Faknr;
var xDebitorkonto = m_rec_betlin.Debitorkonto;
var xNr = m_rec_betlin.Nr;
// find Tblfak
var qry1 = from f in p_dbData3060.Tblfak where f.Faknr == xFaknr select f;
var count1 = qry1.Count();
if (count1 > 0)
{
var recfak1 = qry1.First();
m_rec_betlin.Nr = recfak1.Nr;
}
else
{
var qry2 = from f in p_dbData3060.Tblfak where f.Indbetalerident == xDebitorkonto select f;
var count2 = qry2.Count();
if (count2 > 0)
{
var recfak2 = qry2.First();
m_rec_betlin.Nr = recfak2.Nr;
m_rec_betlin.Faknr = recfak2.Faknr;
}
}
}
catch
{
return(m_rec_betlin_save);
}
return(m_rec_betlin);
}
public int aftaleoplysninger_fra_pbs(dbData3060DataContext p_dbData3060)
{
int dummy = 0;
string rec;
string leverance;
string leverancespecifikation;
DateTime leverancedannelsesdato;
string sektion;
int wpbsfilesid;
int wleveranceid;
int AntalFiler = 0;
var qrypbsfiles = from h in p_dbData3060.Tblpbsfilename
where h.Pbsforsendelseid == null
join d in p_dbData3060.Tblpbsfile on h.Id equals d.Pbsfilesid
where d.Seqnr == 1 && d.Data.Substring(16, 4) == "0603" && d.Data.Substring(0, 2) == "BS"
select new
{
h.Id,
h.Path,
h.Filename,
leverancetype = d.Data.Substring(16, 4),
delsystem = d.Data.Substring(13, 3),
};
int DebugCount = qrypbsfiles.Count();
foreach (var rstpbsfiles in qrypbsfiles)
{
//try
{
wpbsfilesid = rstpbsfiles.Id;
AntalFiler++;
leverance = "";
sektion = "";
leverancespecifikation = "";
var qrypbsfile = from d in p_dbData3060.Tblpbsfile
where d.Pbsfilesid == wpbsfilesid && d.Data.Substring(0, 6) != "PBCNET"
orderby d.Seqnr
select d;
foreach (var rstpbsfile in qrypbsfile)
{
rec = rstpbsfile.Data;
// Bestem Leverance Type
if (rstpbsfile.Seqnr == 1)
{
if (rec.Substring(0, 5) == "BS002")
{ // Leverance Start
leverance = rec.Substring(16, 4);
leverancespecifikation = rec.Substring(20, 10);
leverancedannelsesdato = DateTime.Parse("20" + rec.Substring(53, 2) + "-" + rec.Substring(51, 2) + "-" + rec.Substring(49, 2));
}
else
{
throw new Exception("241 - Første record er ikke en Leverance start record");
};
if (leverance == "0603")
{
// -- Leverance 0603
var antal = (from c in p_dbData3060.Tblfrapbs
where c.Leverancespecifikation == leverancespecifikation
select c).Count();
if (antal > 0)
{
throw new Exception("242 - Leverance med pbsfilesid: " + wpbsfilesid + " og leverancespecifikation: " + leverancespecifikation + " er indlæst tidligere");
}
wleveranceid = p_dbData3060.nextval_v2("leveranceid");
m_rec_pbsforsendelse = new Tblpbsforsendelse
{
Delsystem = "BS1",
Leverancetype = "0603",
Oprettetaf = "Bet",
Oprettet = DateTime.Now,
Leveranceid = wleveranceid
};
p_dbData3060.Tblpbsforsendelse.Add(m_rec_pbsforsendelse);
m_rec_frapbs = new Tblfrapbs
{
Delsystem = "BS1",
Leverancetype = "0603",
Leverancespecifikation = leverancespecifikation,
Leverancedannelsesdato = leverancedannelsesdato,
Udtrukket = DateTime.Now
};
m_rec_pbsforsendelse.Tblfrapbs.Add(m_rec_frapbs);
m_rec_pbsfiles = (from c in p_dbData3060.Tblpbsfilename
where c.Id == rstpbsfiles.Id
select c).First();
m_rec_pbsforsendelse.Tblpbsfilename.Add(m_rec_pbsfiles);
}
;
}
;
if (leverance == "0603")
{ // Leverance 0603
// Bestem Sektions Type
if (sektion == "")
{
if (rec.Substring(0, 5) == "BS012")
{ // Sektion Start
sektion = rec.Substring(13, 4);
}
else
{
if (!((rec.Substring(0, 5) == "BS992") || (rec.Substring(0, 5) == "BS002")))
{
throw new Exception("243 - Første record er ikke en Sektions start record");
}
;
};
}
;
if (rec.Substring(0, 5) == "BS002")
{ // Leverance start
// BEHANDL: Leverance start
dummy = 1;
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0210")
{ // Sektion 0210 Aktive aftaler
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0210"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0230"))
{ // Aktive aftaler
// BEHANDL: Aktive aftaler
readaftale042(p_dbData3060, sektion, "0230", rec);
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0210"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("244 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0212")
{ // Sektion 0212 Til- og afgang af betalingsaftaler
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0212"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0231"))
{ // Tilgang af nye betalingsaftaler
// BEHANDL: Tilgang af nye betalingsaftaler
readaftale042(p_dbData3060, sektion, "0231", rec);
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0232"))
{ // Aftale afmeldt af pengeinstitut
// BEHANDL: aftale afmeldt af pengeinstitut
readaftale042(p_dbData3060, sektion, "0232", rec);
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0233"))
{ // Aftaler afmeldt af kreditor
// BEHANDL: aftaler afmeldt af kreditor
readaftale042(p_dbData3060, sektion, "0233", rec);
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0234"))
{ // Aftaler afmeldt af betalingsservice
// BEHANDL: aftaler afmeldt af betalingsservice
readaftale042(p_dbData3060, sektion, "0234", rec);
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0212"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("245 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0214")
{ // Sektion 0214 Forfaldsoplysninger
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0214"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0235"))
{ // Forfald automatisk betaling
// BEHANDL: Forfald automatisk betaling
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0295"))
{ // Forfald manuel betaling
// BEHANDL: Forfald manuel betaling
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0214"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("246 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0215")
{ // Sektion 0215 Debitornavn/-adresse
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(14 - 1, 4) == "0215"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS022") && (rec.Substring(13, 4) == "0240"))
{ // Navn/adresse på debitor
// BEHANDL: Navn/adresse på debitor
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(14 - 1, 4) == "0215"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("247 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0217")
{ // Sektion 0217 Oplysninger fra indbetalingskort
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0217"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0295"))
{ // Oplysninger fra indbetalingskort
// BEHANDL: Oplysninger fra indbetalingskort
readgirokort042(p_dbData3060, sektion, "0295", rec);
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0217"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("248 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0219")
{ // Sektion 0219 Aktive aftaler om Elektronisk Indbetalingskort
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0219"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0230"))
{ // Aktiv aftale om Elektronisk Indbetalingskort
// BEHANDL: Aktiv aftale om Elektronisk Indbetalingskort
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(14 - 1, 4) == "0219"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("249 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (rec.Substring(0, 5) == "BS992")
{ // Leverance s**t
// BEHANDL: Leverance S**t
leverance = "";
}
else
{
throw new Exception("250 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
}
else
{
throw new Exception("251 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
}
}
/*
* catch (Exception e)
* {
* switch (e.Message.Substring(0, 3))
* {
* case "241": //241 - Første record er ikke en Leverance start record
* case "242": //242 - Leverancen er indlæst tidligere
* case "243": //243 - Første record er ikke en Sektions start record
* case "244": //244 - Record ukendt
* case "245": //245 - Record ukendt
* case "246": //246 - Record ukendt
* case "247": //247 - Record ukendt
* case "248": //248 - Record ukendt
* case "249": //249 - Record ukendt
* case "250": //250 - Record ukendt
* case "251": //251 - Record ukendt
* AntalFiler--;
* break;
*
* default:
* throw;
* }
* }
*/
}
if (dummy == 1)
{
dummy = 2;
}
p_dbData3060.SaveChanges();
return(AntalFiler);
}
public clsKontingent(dbData3060DataContext p_dbData3060, DateTime startdato)
{
this.BeregnKontingent(p_dbData3060, startdato);
}
public void readindbetalingskort042(dbData3060DataContext p_dbData3060, string sektion, string transkode, string rec)
{
// -- pbssektionnr
// --- sektion 0195, indbetalingskort til e-Boks
// --- sektion 0196, elektronisk indbetalingskort i netbank
// --- sektion 0197, papirindbetalingskort
// -- pbstranskode
decimal belobmun;
int belob;
m_rec_indbetalingskort = new Tblindbetalingskort
{
Pbssektionnr = sektion,
Pbstranskode = transkode
};
// Medlem Nr
try
{
m_rec_indbetalingskort.Nr = int.Parse(rec.Substring(35, 7));
}
catch
{
m_rec_indbetalingskort.Nr = 0;
}
// debitorkonto
m_rec_indbetalingskort.Debitorkonto = rec.Substring(27, 15);
// debgrpnr
m_rec_indbetalingskort.Debgrpnr = rec.Substring(22, 5);
// Kortartkode
m_rec_indbetalingskort.Kortartkode = rec.Substring(128, 2);
// FI-kreditor
m_rec_indbetalingskort.Fikreditornr = rec.Substring(130, 8);
// Indbetalerident
m_rec_indbetalingskort.Indbetalerident = "000" + rec.Substring(82, 16); //Tidligere 19 lang i 217 record
// dato
if (rec.Substring(51, 8) != "00000000")
{
m_rec_indbetalingskort.Dato = DateTime.Parse(rec.Substring(55, 4) + "-" + rec.Substring(53, 2) + "-" + rec.Substring(51, 2));
}
else
{
m_rec_indbetalingskort.Dato = null;
};
// Beløb
belob = int.Parse(rec.Substring(60, 13));
belobmun = ((decimal)belob) / 100;
m_rec_indbetalingskort.Belob = belobmun;
// Faknr
m_rec_indbetalingskort.Faknr = int.Parse(rec.Substring(73, 9));
// Netbank regnr
if (sektion == "0196")
{
m_rec_indbetalingskort.Regnr = rec.Substring(138, 4);
}
if ((from h in p_dbData3060.Tblfak where h.Faknr == m_rec_indbetalingskort.Faknr select h).Count() == 1)
{
if ((from k in p_dbData3060.Tblindbetalingskort where k.Nr == m_rec_indbetalingskort.Nr && k.Indbetalerident == m_rec_indbetalingskort.Indbetalerident select k).Count() == 0)
{
// Add tblindbetalingskort
m_rec_frapbs.Tblindbetalingskort.Add(m_rec_indbetalingskort);
}
}
}
public void PbsAutoKontingent(dbData3060DataContext m_dbData3060, CrudAPI api)
{
//DateTime Nu = new DateTime(2018, 8, 13); //<-------- DEBUG
DateTime Nu = DateTime.Now;
int Dag = Nu.Day;
if (Dag > 13)
{
return;
}
DateTime Nu_plus_1 = Nu.AddMonths(1);
DateTime p_DatoKontingentForfald = new DateTime(Nu_plus_1.Year, Nu_plus_1.Month, 1);
DateTime Nu_plus_2 = Nu.AddMonths(2);
DateTime p_DatoBetaltKontingentTil = new DateTime(Nu_plus_2.Year, Nu_plus_2.Month, 12);
clsPbs601 objPbs601d = new clsPbs601();
List <string[]> items = objPbs601d.UniConta_KontingentForslag(p_DatoBetaltKontingentTil, m_dbData3060, api);
int AntalForslag = items.Count();
if (AntalForslag > 0)
{
Console.WriteLine(string.Format("Medlem3060Service {0} begin", "Send Kontingent File til PBS"));
Memkontingentforslag memKontingentforslag = new Memkontingentforslag();
foreach (string[] item in items)
{
string user_id = item[0];
string name = item[1];
DateTime fradato = DateTime.Parse(item[4]);
double advisbelob = double.Parse(item[5]);
DateTime tildato = DateTime.Parse(item[6]);
bool indmeldelse = (item[7] == "J") ? true : false;
bool tilmeldtpbs = (item[8] == "J") ? true : false;
int memberid = (!string.IsNullOrEmpty(item[10])) ? int.Parse(item[10]) : m_dbData3060.nextval_v2("memberid");
recKontingentforslag rec_Kontingentforslag = new recKontingentforslag
{
betalingsdato = clsHelper.bankdageplus(p_DatoKontingentForfald, 0),
bsh = false,
user_id = int.Parse(user_id),
membership_id = 6,
advisbelob = (decimal)advisbelob,
fradato = fradato,
tildato = tildato,
indmeldelse = indmeldelse,
tilmeldtpbs = tilmeldtpbs,
memberid = memberid,
name = name
};
memKontingentforslag.Add(rec_Kontingentforslag);
}
Tuple <int, int> tresulte = objPbs601d.UniConta_kontingent_fakturer_bs1(m_dbData3060, memKontingentforslag, pbsType.betalingsservice);
int AntalFakturaer = tresulte.Item1;
int lobnr = tresulte.Item2;
if ((AntalFakturaer > 0))
{
//pbsType.betalingsservice
objPbs601d.faktura_og_rykker_601_action_lobnr(m_dbData3060, lobnr, api);
clsSFTP objSFTPd = new clsSFTP(m_dbData3060);
objSFTPd.WriteTilSFtp(m_dbData3060, lobnr);
objSFTPd.DisconnectSFtp();
objSFTPd = null;
}
tresulte = objPbs601d.UniConta_kontingent_fakturer_bs1(m_dbData3060, memKontingentforslag, pbsType.indbetalingskort);
AntalFakturaer = tresulte.Item1;
lobnr = tresulte.Item2;
if ((AntalFakturaer > 0))
{
//pbsType.indbetalingskort
objPbs601d.faktura_og_rykker_601_action_lobnr(m_dbData3060, lobnr, api);
clsSFTP objSFTPd = new clsSFTP(m_dbData3060);
objSFTPd.WriteTilSFtp(m_dbData3060, lobnr);
objSFTPd.DisconnectSFtp();
objSFTPd = null;
}
Console.WriteLine(string.Format("Medlem3060Service {0} end", "Send Kontingent File til PBS"));
}
}
public int ReadFraSFtp(dbData3060DataContext p_dbData3060)
{
m_memPbsnetdir = new MemPbsnetdir(); //opret ny memPbsnetdir
// Iterate over the files.
foreach (SftpFile fileObj in m_sftp.ListDirectory(m_rec_sftp.Outbound))
{
if (fileObj.Name != "." && fileObj.Name != "..")
{
recPbsnetdir rec = new recPbsnetdir
{
Type = 8,
Path = m_rec_sftp.Outbound,
Filename = fileObj.Name,
Size = (int)fileObj.Length,
Atime = fileObj.LastAccessTime,
Mtime = fileObj.LastWriteTime,
Gid = fileObj.GroupId,
Uid = fileObj.UserId,
Perm = ""
};
m_memPbsnetdir.Add(rec);
}
}
var leftqry_pbsnetdir =
from h in m_memPbsnetdir
//join d1 in p_dbData3060.Tblpbsfiles on new { h.Path, h.Filename } equals new { d1.Path, d1.Filename } into details
//from d1 in details.DefaultIfEmpty(new Tblpbsfiles { Id = -1, Type = (int?)null, Path = null, Filename = null, Size = (int?)null, Atime = (DateTime?)null, Mtime = (DateTime?)null, Perm = null, Uid = (int?)null, Gid = (int?)null })
//where d1.Path == null && d1.Filename == null
select h;
int AntalFiler = leftqry_pbsnetdir.Count();
if (leftqry_pbsnetdir.Count() > 0)
{
foreach (var rec_pbsnetdir in leftqry_pbsnetdir)
{
Tblpbsfilename m_rec_pbsfiles = new Tblpbsfilename
{
Type = rec_pbsnetdir.Type,
Path = rec_pbsnetdir.Path,
Filename = rec_pbsnetdir.Filename,
Size = rec_pbsnetdir.Size,
Atime = rec_pbsnetdir.Atime,
Mtime = rec_pbsnetdir.Mtime,
Perm = rec_pbsnetdir.Perm,
Uid = rec_pbsnetdir.Uid,
Gid = rec_pbsnetdir.Gid
};
p_dbData3060.Tblpbsfilename.Add(m_rec_pbsfiles);
//***********************************************************************
// Open a file on the server:
string fullpath = rec_pbsnetdir.Path + "/" + rec_pbsnetdir.Filename;
int numBytes = (int)rec_pbsnetdir.Size;
byte[] b_data = new byte[numBytes];
MemoryStream stream = new MemoryStream(b_data, true);
Console.WriteLine(string.Format("Start Download of {0}", fullpath));
m_sftp.DownloadFile(fullpath, stream);
Console.WriteLine(string.Format("{0} Download Completed", fullpath));
//clsAzure objAzure = new clsAzure();
//objAzure.uploadBlob(rec_pbsnetdir.Filename, b_data, false);
imapSaveAttachedFile(p_dbData3060, rec_pbsnetdir.Filename, b_data, false);
char[] c_data = System.Text.Encoding.GetEncoding("windows-1252").GetString(b_data).ToCharArray();
string filecontens = new string(c_data);
string filecontens2 = filecontens.TrimEnd('\n');
string filecontens3 = filecontens2.TrimEnd('\r');
string filecontens4 = filecontens3.TrimEnd('\n');
string[] lines = filecontens4.Split('\n');
string ln = null;
int seqnr = 0;
string ln0_6;
for (int idx = 0; idx < lines.Count(); idx++)
{
ln = lines[idx].TrimEnd('\r');
try { ln0_6 = ln.Substring(0, 6); }
catch { ln0_6 = ""; }
if (((seqnr == 0) && !(ln0_6 == "PBCNET")) || (seqnr > 0))
{
seqnr++;
}
if (ln.Length > 0)
{
m_rec_pbsfile = new Tblpbsfile
{
Seqnr = seqnr,
Data = ln
};
m_rec_pbsfiles.Tblpbsfile.Add(m_rec_pbsfile);
}
}
//this.Database.GetDbConnection()
m_rec_pbsfiles.Transmittime = DateTime.Now;
//var cb = new SqlConnectionStringBuilder(p_dbData3060.Connection.ConnectionString);
Console.WriteLine(string.Format("Start Write {0} to SQL Database {1} on {2}", rec_pbsnetdir.Filename, "cb.InitialCatalog", "cb.DataSource"));
try
{
p_dbData3060.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine(string.Format("p_dbData3060.SaveChanges() failed with message: {0}", e.Message));
throw;
}
Console.WriteLine(string.Format("{0} Write to SQL Database Completed", rec_pbsnetdir.Filename));
}
}
p_dbData3060.SaveChanges();
return(AntalFiler);
}
public int betalinger_opdate_uniconta(dbData3060DataContext p_dbData3060, CrudAPI api)
{
int saveBetid = 0;
var rsmbrshp = from bl in p_dbData3060.Tblbetlin
where (bl.Pbstranskode == "0236" || bl.Pbstranskode == "0297")
join b in p_dbData3060.Tblbet on bl.Betid equals b.Id
where b.Rsmembership == null || b.Rsmembership == false
join p in p_dbData3060.Tblfrapbs on b.Frapbsid equals p.Id
orderby p.Id, b.Id, bl.Id
select new
{
Frapbsid = p.Id,
p.Leverancespecifikation,
Betid = b.Id,
Betlinid = bl.Id,
bl.Betalingsdato,
bl.Indbetalingsdato,
bl.Indbetalingsbelob,
bl.Faknr,
bl.Debitorkonto
};
int AntalBetalinger = rsmbrshp.Count();
Console.WriteLine(string.Format("betalinger_til_rsmembership - AntalBetalinger {0} <----", AntalBetalinger));
if (rsmbrshp.Count() > 0)
{
foreach (var b in rsmbrshp)
{
if (saveBetid != b.Betid) // ny gruppe
{
saveBetid = b.Betid;
var rec_bet = (from ub in p_dbData3060.Tblbet where ub.Id == b.Betid select ub).First();
rec_bet.Rsmembership = true;
}
// Do somthing here
var qry = from f in p_dbData3060.Tblfak
where f.Faknr == b.Faknr
select f;
if (qry.Count() == 1)
{
var fak = qry.First();
var critMedlem = new List <PropValuePair>();
var pairMedlem = PropValuePair.GenereteWhereElements("KeyStr", typeof(String), fak.Nr.ToString());
critMedlem.Add(pairMedlem);
var taskMedlem = api.Query <Medlem>(critMedlem);
taskMedlem.Wait();
var resultMedlem = taskMedlem.Result;
var antalMedlem = resultMedlem.Count();
if (antalMedlem == 1)
{
var recMedlem = resultMedlem.First();
recMedlem.medlemtil = (DateTime)fak.Tildato;
recMedlem.status = "Medlem";
var taskMedlemUpdate = api.Update(recMedlem); //Opdater Medlem
}
}
}
p_dbData3060.SaveChanges();
}
return(AntalBetalinger);
}
public int betalinger_fra_pbs(dbData3060DataContext p_dbData3060)
{
string rec;
string leverancetype;
string leverancespecifikation;
DateTime leverancedannelsesdato;
string sektion;
int wpbsfilesid;
int wleveranceid;
int AntalFiler = 0;
// wpbsfilesid = 3450 //'--test test
// leverancetype = "0602"
// sektion = "0211"
// rec = "BS0420398564402360000000100000000001231312345678910120310000000012755000000125 3112031112030000000012755"
var qrypbsfiles = from h in p_dbData3060.Tblpbsfilename
where h.Pbsforsendelseid == null
join d in p_dbData3060.Tblpbsfile on h.Id equals d.Pbsfilesid
where d.Seqnr == 1 && d.Data.Substring(16, 4) == "0602" && d.Data.Substring(0, 2) == "BS"
select new
{
h.Id,
h.Path,
h.Filename,
leverancetype = d.Data.Substring(16, 4),
delsystem = d.Data.Substring(13, 3),
};
foreach (var rstpbsfiles in qrypbsfiles.ToList())
{
try
{
wpbsfilesid = rstpbsfiles.Id;
AntalFiler++;
leverancetype = "";
sektion = "";
leverancespecifikation = "";
var qrypbsfile = from d in p_dbData3060.Tblpbsfile
where d.Pbsfilesid == wpbsfilesid && d.Data.Substring(0, 6) != "PBCNET"
orderby d.Seqnr
select d;
foreach (var rstpbsfile in qrypbsfile.ToList())
{
rec = rstpbsfile.Data;
// -- Bestem Leverance Type
if (rstpbsfile.Seqnr == 1)
{
if ((rec.Substring(0, 5) == "BS002"))
{
// -- Leverance Start
leverancetype = rec.Substring(16, 4);
leverancespecifikation = rec.Substring(20, 10);
leverancedannelsesdato = DateTime.Parse("20" + rec.Substring(53, 2) + "-" + rec.Substring(51, 2) + "-" + rec.Substring(49, 2));
}
else
{
throw new Exception("241 - Første record er ikke en Leverance start record");
}
if ((leverancetype == "0602"))
{
// -- Leverance 0602
var antal = (from c in p_dbData3060.Tblfrapbs
where c.Leverancespecifikation == leverancespecifikation
select c).Count();
if (antal > 0)
{
throw new Exception("242 - Leverance med pbsfilesid: " + wpbsfilesid + " og leverancespecifikation: " + leverancespecifikation + " er indlæst tidligere");
}
wleveranceid = p_dbData3060.nextval_v2("leveranceid");
m_rec_pbsforsendelse = new Tblpbsforsendelse
{
Delsystem = "BS1",
Leverancetype = "0602",
Oprettetaf = "Bet",
Oprettet = DateTime.Now,
Leveranceid = wleveranceid
};
p_dbData3060.Tblpbsforsendelse.Add(m_rec_pbsforsendelse);
m_rec_frapbs = new Tblfrapbs
{
Delsystem = "BS1",
Leverancetype = "0602",
Leverancespecifikation = leverancespecifikation,
Leverancedannelsesdato = leverancedannelsesdato,
Udtrukket = DateTime.Now
};
m_rec_pbsforsendelse.Tblfrapbs.Add(m_rec_frapbs);
m_rec_pbsfiles = (from c in p_dbData3060.Tblpbsfilename
where c.Id == rstpbsfiles.Id
select c).First();
m_rec_pbsforsendelse.Tblpbsfilename.Add(m_rec_pbsfiles);
}
}
if ((leverancetype == "0602"))
{
// -- Leverance 0602*********
// -- Bestem Sektions Type
if ((sektion == ""))
{
if ((rec.Substring(0, 5) == "BS012"))
{
// -- Sektion Start
sektion = rec.Substring(13, 4);
}
else if (!((rec.Substring(0, 5) == "BS992") || (rec.Substring(0, 5) == "BS002")))
{
throw new Exception("243 - Første record er ikke en Sektions start record");
}
}
if ((rec.Substring(0, 5) == "BS002"))
{
// -- Leverance start
// -- BEHANDL: Leverance start
}
else if ((sektion == "0211"))
{
// -- Sektion 0211 Betalingsinformation
if (((rec.Substring(0, 5) == "BS012") &&
(rec.Substring(13, 4) == "0211")))
{
// -- Sektion Start
// -- BEHANDL: Sektion Start
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0236")))
{
// -- Gennemf?rt automatisk betaling
// -- BEHANDL: Gennemf?rt automatisk betaling
read042(sektion, "0236", rec, p_dbData3060);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0237")))
{
// -- Afvist betaling
// -- BEHANDL: Afvist betaling
read042(sektion, "0237", rec, p_dbData3060);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0238")))
{
// -- Afmeldt betaling
// -- BEHANDL: Afmeldt betaling
read042(sektion, "0238", rec, p_dbData3060);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0239")))
{
// -- Tilbagef?rt betaling
// -- BEHANDL: Tilbagef?rt betaling
read042(sektion, "0239", rec, p_dbData3060);
}
else if (((rec.Substring(0, 5) == "BS092") &&
(rec.Substring(13, 4) == "0211")))
{
// -- Sektion S**t
// -- BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("244 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
}
}
else if ((sektion == "0215"))
{
// -- Sektion 0215 FI-Betalingsinformation
if (((rec.Substring(0, 5) == "BS012") &&
(rec.Substring(13, 4) == "0215")))
{
// -- Sektion Start
// -- BEHANDL: Sektion Start
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0297")))
{
// -- Gennemf?rt FI-betaling
// -- BEHANDL: Gennemf?rt FI-betaling
read042(sektion, "0297", rec, p_dbData3060);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0299")))
{
// -- Tilbagef?rt FI-betaling
// -- BEHANDL: Tilbagef?rt FI-betaling
read042(sektion, "0299", rec, p_dbData3060);
}
else if (((rec.Substring(0, 5) == "BS092") &&
(rec.Substring(13, 4) == "0215")))
{
// -- Sektion S**t
// -- BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("245 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
}
}
else if ((rec.Substring(0, 5) == "BS992"))
{
// -- Leverance s**t
// -- BEHANDL: Leverance S**t
leverancetype = "";
}
else
{
throw new Exception("246 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
}
}
else
{
throw new Exception("247 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
}
} // s**t rstpbsfile
// -- Update indbetalingsbelob
foreach (Tblbet rec_bet in m_rec_frapbs.Tblbet)
{
var SumIndbetalingsbelob = (
from c in rec_bet.Tblbetlin
group c by c.Betid into g
select new { Betid = g.Key, SumIndbetalingsbelob = g.Sum(c => c.Indbetalingsbelob) }
).First().SumIndbetalingsbelob;
rec_bet.Indbetalingsbelob = SumIndbetalingsbelob;
}
}
catch (Exception e)
{
switch (e.Message.Substring(0, 3))
{
case "241": //241 - Første record er ikke en Leverance start record
case "242": //242 - Leverancen er indlæst tidligere
case "243": //243 - Første record er ikke en Sektions start record
case "244": //244 - Record ukendt
case "245": //245 - Record ukendt
case "246": //246 - Record ukendt
case "247": //247 - Record ukendt
AntalFiler--;
break;
default:
throw;
}
}
} // s**t rstpbsfiles
try
{
p_dbData3060.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine(string.Format("clsPbs602 p_dbData3060.SaveChanges() failed with message: {0}", e.Message));
AntalFiler = 0;
}
return(AntalFiler);
}
public void read042(string sektion, string transkode, string rec, dbData3060DataContext p_dbData3060)
{
int fortegn;
decimal belobmun;
int belob;
// -- pbssektionnr
// -- pbstranskode
// - transkode 0236, gennemført automatisk betaling
// - transkode 0237, afvist automatisk betaling
// - transkode 0238, afmeldt automatisk betaling
// - transkode 0239, tilbageført betaling
m_rec_betlin = new Tblbetlin
{
Pbssektionnr = sektion,
Pbstranskode = transkode
};
// -- debitorkonto
if ((sektion == "0211"))
{
m_rec_betlin.Debitorkonto = rec.Substring(25, 15);
if (m_rec_betlin.Debitorkonto.StartsWith("03200161"))
{
m_rec_betlin.Nr = int.Parse(rec.Substring(33, 7)); //***MHA***
}
else
{
m_rec_betlin.Nr = null;
}
}
else if ((sektion == "0215"))
{
m_rec_betlin.Debitorkonto = rec.Substring(29, 15);
if (m_rec_betlin.Debitorkonto.StartsWith("03200161"))
{
m_rec_betlin.Nr = int.Parse(rec.Substring(37, 7));
}
else
{
m_rec_betlin.Nr = null;
}
}
else
{
m_rec_betlin.Nr = null;
m_rec_betlin.Debitorkonto = null;
}
// -- aftalenr
if ((sektion == "0211"))
{
m_rec_betlin.Aftalenr = int.Parse(rec.Substring(40, 9));
}
else
{
m_rec_betlin.Aftalenr = null;
}
// -- pbskortart
if ((sektion == "0215"))
{
m_rec_betlin.Pbskortart = rec.Substring(44, 2);
}
else
{
m_rec_betlin.Pbskortart = null;
}
// -- pbsgebyrbelob
if ((sektion == "0215"))
{
fortegn = int.Parse(rec.Substring(46, 1));
belob = int.Parse(rec.Substring(47, 5));
belobmun = (belob / 100);
if ((fortegn == 0))
{
m_rec_betlin.Pbsgebyrbelob = 0;
}
else if ((fortegn == 1))
{
m_rec_betlin.Pbsgebyrbelob = belobmun;
}
else
{
m_rec_betlin.Pbsgebyrbelob = 0;
}
}
else
{
m_rec_betlin.Pbsgebyrbelob = 0;
}
// -- betalingsdato
if ((sektion == "0211"))
{
if ((rec.Substring(49, 6) != "000000"))
{
m_rec_betlin.Betalingsdato = DateTime.Parse("20"
+ (rec.Substring(53, 2) + ("-"
+ (rec.Substring(51, 2) + ("-" + rec.Substring(49, 2))))));
}
else
{
m_rec_betlin.Betalingsdato = null;
}
}
else if ((sektion == "0215"))
{
if ((rec.Substring(52, 6) != "000000"))
{
m_rec_betlin.Betalingsdato = DateTime.Parse("20"
+ (rec.Substring(56, 2) + ("-"
+ (rec.Substring(54, 2) + ("-" + rec.Substring(52, 2))))));
}
else
{
m_rec_betlin.Betalingsdato = null;
}
}
else
{
m_rec_betlin.Betalingsdato = null;
}
// -- belob
if ((sektion == "0211"))
{
fortegn = int.Parse(rec.Substring(55, 1));
belob = int.Parse(rec.Substring(56, 13));
belobmun = (belob / 100);
if ((fortegn == 0))
{
m_rec_betlin.Belob = 0;
}
else if ((fortegn == 1))
{
m_rec_betlin.Belob = belobmun;
}
else if ((fortegn == 2))
{
m_rec_betlin.Belob = (belobmun * -1);
}
else
{
m_rec_betlin.Belob = null;
}
}
else if ((sektion == "0215"))
{
fortegn = int.Parse(rec.Substring(58, 1));
belob = int.Parse(rec.Substring(59, 13));
belobmun = (belob / 100);
if ((fortegn == 0))
{
m_rec_betlin.Belob = 0;
}
else if ((fortegn == 1))
{
m_rec_betlin.Belob = belobmun;
}
else
{
m_rec_betlin.Belob = null;
}
}
else
{
m_rec_betlin.Belob = null;
}
// -- faknr
if ((sektion == "0211"))
{
m_rec_betlin.Faknr = int.Parse("0" + rec.Substring(69, 9).Trim()); //***MHA***
}
else if ((sektion == "0215"))
{
m_rec_betlin.Faknr = int.Parse("0" + rec.Substring(72, 9).Trim());
}
else
{
m_rec_betlin.Faknr = null;
}
// -- pbsarkivnr
if ((sektion == "0215"))
{
m_rec_betlin.Pbsarkivnr = rec.Substring(81, 22);
}
else
{
m_rec_betlin.Pbsarkivnr = null;
}
// -- indbetalingsdato
if ((rec.Substring(103, 6) != "000000"))
{
m_rec_betlin.Indbetalingsdato = DateTime.Parse("20"
+ (rec.Substring(107, 2) + ("-"
+ (rec.Substring(105, 2) + ("-" + rec.Substring(103, 2))))));
}
else
{
m_rec_betlin.Indbetalingsdato = null;
}
// -- bogforingsdato
if ((rec.Substring(109, 6) != "000000"))
{
m_rec_betlin.Bogforingsdato = DateTime.Parse("20"
+ (rec.Substring(113, 2) + ("-"
+ (rec.Substring(111, 2) + ("-" + rec.Substring(109, 2))))));
}
else
{
m_rec_betlin.Bogforingsdato = null;
}
// -- indbetalingsbelob
if ((sektion == "0211"))
{
fortegn = int.Parse(rec.Substring(55, 1));
belob = int.Parse(rec.Substring(115, 13));
belobmun = (belob / 100);
if ((fortegn == 0))
{
m_rec_betlin.Indbetalingsbelob = 0;
}
else if ((fortegn == 1))
{
m_rec_betlin.Indbetalingsbelob = belobmun;
}
else if ((fortegn == 2))
{
m_rec_betlin.Indbetalingsbelob = (belobmun * -1);
}
else
{
m_rec_betlin.Indbetalingsbelob = null;
}
}
else if ((sektion == "0215"))
{
fortegn = int.Parse(rec.Substring(58, 1));
belob = int.Parse(rec.Substring(115, 13));
belobmun = (belob / 100);
if ((fortegn == 0))
{
m_rec_betlin.Indbetalingsbelob = 0;
}
else if ((fortegn == 1))
{
m_rec_betlin.Indbetalingsbelob = belobmun;
}
else
{
m_rec_betlin.Indbetalingsbelob = null;
}
}
else
{
m_rec_betlin.Indbetalingsbelob = null;
}
// Find or Create tblber record
try
{
m_rec_bet = (from c in m_rec_frapbs.Tblbet
where c.Pbssektionnr == sektion &&
c.Transkode == transkode &&
c.Bogforingsdato == m_rec_betlin.Bogforingsdato
select c).First();
}
catch (System.InvalidOperationException)
{
m_rec_bet = new Tblbet
{
Pbssektionnr = sektion,
Transkode = transkode,
Bogforingsdato = m_rec_betlin.Bogforingsdato
};
m_rec_frapbs.Tblbet.Add(m_rec_bet);
}
// Add tblbetlin
Tblbetlin w_rec_betlin = Update_Nr_Faknr(m_rec_betlin, p_dbData3060);
m_rec_bet.Tblbetlin.Add(w_rec_betlin);
try
{
p_dbData3060.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine(string.Format("clsPbs602 read042 p_dbData3060.SaveChanges() failed with message: {0}", e.Message));
}
}
public int ReadFromLocalFile(dbData3060DataContext p_dbData3060, string FilePath)
{
FileInfo file;
try
{
file = new FileInfo(FilePath);
if (!(file.Exists))
{
return(0);
}
}
catch (Exception)
{
return(0);
}
//***********************************************************************
// Open a local file:
string fullpath = file.FullName;
int numBytes = (int)file.Length;
byte[] b_data = new byte[numBytes];
Console.WriteLine(string.Format("Start Reading of {0}", fullpath));
using (FileStream ts = file.Open(FileMode.Open, FileAccess.Read, FileShare.None))
{
ts.Read(b_data, 0, numBytes);
}
Console.WriteLine(string.Format("{0} Reading Completed", fullpath));
//clsAzure objAzure = new clsAzure();
//objAzure.uploadBlob(file.Name, b_data, false);
imapSaveAttachedFile(p_dbData3060, file.Name, b_data, false);
char[] c_data = System.Text.Encoding.GetEncoding("windows-1252").GetString(b_data).ToCharArray();
string filecontens = new string(c_data);
string filecontens2 = filecontens.TrimEnd('\n');
string filecontens3 = filecontens2.TrimEnd('\r');
string filecontens4 = filecontens3.TrimEnd('\n');
string[] lines = filecontens4.Split('\n');
string ln = null;
int seqnr = 0;
string ln0_6;
Tblpbsfilename m_rec_pbsfilename = new Tblpbsfilename
{
Type = 8,
Path = file.Directory.FullName,
Filename = file.Name,
Size = (int)file.Length,
Atime = file.LastAccessTime,
Mtime = file.LastWriteTime
};
for (int idx = 0; idx < lines.Count(); idx++)
{
ln = lines[idx].TrimEnd('\r');
try { ln0_6 = ln.Substring(0, 6); }
catch { ln0_6 = ""; }
if (((seqnr == 0) && !(ln0_6 == "PBCNET")) || (seqnr > 0))
{
seqnr++;
}
if (ln.Length > 0)
{
m_rec_pbsfile = new Tblpbsfile
{
Seqnr = seqnr,
Data = ln
};
m_rec_pbsfilename.Tblpbsfile.Add(m_rec_pbsfile);
}
}
m_rec_pbsfilename.Transmittime = DateTime.Now;
p_dbData3060.Tblpbsfilename.Add(m_rec_pbsfilename);
//var cb = new SqlConnectionStringBuilder(p_dbData3060.Connection.ConnectionString);
Console.WriteLine(string.Format("Start Write {0} to SQL Database {1} on {2}", file.Name, "cb.InitialCatalog", "cb.DataSource"));
try
{
p_dbData3060.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine(string.Format("p_dbData3060.SaveChanges() failed with message: {0}", e.Message));
throw;
}
Console.WriteLine(string.Format("{0} Write to SQL Database Completed", file.Name));
return(1);
}
public void readgirokort042(dbData3060DataContext p_dbData3060, string sektion, string transkode, string rec)
{
// -- pbssektionnr
// -- pbstranskode
decimal belobmun;
int belob;
m_rec_indbetalingskort = new Tblindbetalingskort
{
Pbssektionnr = sektion,
Pbstranskode = transkode
};
// Medlem Nr
try
{
m_rec_indbetalingskort.Nr = int.Parse(rec.Substring(33, 7));
}
catch
{
m_rec_indbetalingskort.Nr = 0;
}
// debitorkonto
m_rec_indbetalingskort.Debitorkonto = rec.Substring(25, 15);
// debgrpnr
m_rec_indbetalingskort.Debgrpnr = rec.Substring(20, 5);
// Kortartkode
m_rec_indbetalingskort.Kortartkode = rec.Substring(40, 2);
// FI-kreditor
m_rec_indbetalingskort.Fikreditornr = rec.Substring(42, 8);
// Indbetalerident
m_rec_indbetalingskort.Indbetalerident = rec.Substring(50, 19);
// dato
if (rec.Substring(55, 6) != "000000")
{
m_rec_indbetalingskort.Dato = DateTime.Parse("20" + rec.Substring(73, 2) + "-" + rec.Substring(71, 2) + "-" + rec.Substring(69, 2));
}
else
{
m_rec_indbetalingskort.Dato = null;
};
// Beløb
belob = int.Parse(rec.Substring(75, 13));
belobmun = ((decimal)belob) / 100;
m_rec_indbetalingskort.Belob = belobmun;
// Faknr
m_rec_indbetalingskort.Faknr = int.Parse(rec.Substring(88, 9));
if ((from k in p_dbData3060.Tblindbetalingskort where k.Nr == m_rec_indbetalingskort.Nr && k.Indbetalerident == m_rec_indbetalingskort.Indbetalerident select k).Count() == 0)
{
// Add tblindbetalingskort
m_rec_frapbs.Tblindbetalingskort.Add(m_rec_indbetalingskort);
}
}
public string WriteTilSFtp(dbData3060DataContext p_dbData3060, int lobnr)
{
string TilPBSFilename = "Unknown";
int FilesSize;
var qry_selectfiles =
from h in p_dbData3060.Tblpbsforsendelse
join d1 in p_dbData3060.Tblpbsfilename on h.Id equals d1.Pbsforsendelseid into details1
from d1 in details1.DefaultIfEmpty()
where d1.Id != null && d1.Filename == null
join d2 in p_dbData3060.Tbltilpbs on h.Id equals d2.Pbsforsendelseid into details2
from d2 in details2.DefaultIfEmpty()
where d2.Id == lobnr
select new
{
tilpbsid = (int?)d2.Id,
d2.Leverancespecifikation,
d2.Delsystem,
d2.Leverancetype,
Bilagdato = (DateTime?)d2.Bilagdato,
Pbsforsendelseid = (int?)d2.Pbsforsendelseid,
Udtrukket = (DateTime?)d2.Udtrukket,
pbsfilesid = (int?)d1.Id,
Leveranceid = (int)h.Leveranceid
};
int antal = qry_selectfiles.Count();
if (antal > 0)
{
var rec_selecfiles = qry_selectfiles.First();
var qry_pbsfiles = from h in p_dbData3060.Tblpbsfilename
where h.Id == rec_selecfiles.pbsfilesid
select h;
if (qry_pbsfiles.Count() > 0)
{
Tblpbsfilename m_rec_pbsfiles = qry_pbsfiles.First();
TilPBSFilename = AddPbcnetRecords(p_dbData3060, rec_selecfiles.Delsystem, rec_selecfiles.Leveranceid, m_rec_pbsfiles.Id);
var qry_pbsfile =
from h in m_rec_pbsfiles.Tblpbsfile
orderby h.Seqnr
select h;
string TilPBSFile = "";
int i = 0;
foreach (var rec_pbsfile in qry_pbsfile)
{
if (i++ > 0)
{
TilPBSFile += "\r\n";
}
TilPBSFile += rec_pbsfile.Data;
}
char[] c_TilPBSFile = TilPBSFile.ToCharArray();
byte[] b_TilPBSFile = System.Text.Encoding.GetEncoding("windows-1252").GetBytes(c_TilPBSFile);
FilesSize = b_TilPBSFile.Length;
//clsAzure objAzure = new clsAzure();
//objAzure.uploadBlob(TilPBSFilename, b_TilPBSFile, true);
imapSaveAttachedFile(p_dbData3060, TilPBSFilename, b_TilPBSFile, true);
string fullpath = m_rec_sftp.Inbound + "/" + TilPBSFilename;
MemoryStream ms_TilPBSFile = new MemoryStream(b_TilPBSFile);
Console.WriteLine(string.Format("Start Upload of {0}", fullpath));
m_sftp.UploadFile(ms_TilPBSFile, fullpath);
Console.WriteLine(string.Format("{0} Upload Completed", fullpath));
m_rec_pbsfiles.Type = 8;
m_rec_pbsfiles.Path = m_rec_sftp.Inbound;
m_rec_pbsfiles.Filename = TilPBSFilename;
m_rec_pbsfiles.Size = FilesSize;
m_rec_pbsfiles.Atime = DateTime.Now;
m_rec_pbsfiles.Mtime = DateTime.Now;
m_rec_pbsfiles.Transmittime = DateTime.Now;
p_dbData3060.SaveChanges();
}
}
return(TilPBSFilename);
}
public string getinfotekst(dbData3060DataContext dbData3060)
{
string[] crlf = { "\r\n" };
string infotext = null;
try { infotext = (from i in dbData3060.Tblinfotekst where i.Id == infotekst_id select i).First().Msgtext; }
catch (System.InvalidOperationException) { }
RegexOptions options = ((RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline) | RegexOptions.IgnoreCase);
Regex regexParam = new Regex(@"(\#\#[^\#]+\#\#)", options);
string MsgtextSub = "";
if (infotext != null)
{
MsgtextSub = regexParam.Replace(infotext, delegate(Match match)
{
string v = match.ToString();
switch (v.ToLower())
{
case "##navn_medlem##":
if (navn_medlem != null)
{
return(navn_medlem);
}
break;
case "##kaldenavn##":
if (kaldenavn != null)
{
return(kaldenavn);
}
if (navn_medlem != null)
{
return(navn_medlem);
}
break;
case "##fradato##":
if (fradato != null)
{
return(string.Format("{0:d. MMMM yyyy}", fradato));
}
break;
case "##tildato##":
if (tildato != null)
{
return(string.Format("{0:d. MMMM yyyy}", tildato));
}
break;
case "##betalingsdato##":
if (betalingsdato != null)
{
return(string.Format("{0:dddd}", betalingsdato) + " den " + string.Format("{0:d. MMMM}", betalingsdato));
}
break;
case "##advisbelob##":
if (advisbelob != null)
{
return(String.Format("{0:###0.00}", advisbelob).Replace('.', ','));
}
break;
case "##ocrstring##":
if (ocrstring != null)
{
return(ocrstring);
}
break;
case "##underskrift_navn##":
if (underskrift_navn != null)
{
return(underskrift_navn);
}
break;
case "##bankkonto##":
if (bankkonto != null)
{
return(bankkonto);
}
break;
case "##advistekst##":
if (advistekst != null)
{
return(advistekst);
}
break;
case "##sendtsom##":
if (sendtsom != null)
{
return(sendtsom);
}
break;
case "##kundenr##":
if (sendtsom != null)
{
return(kundenr);
}
break;
}
return(v);
});
if (numofcol == null)
{
return(MsgtextSub);
}
else
{
return(splittextincolumns(MsgtextSub, (int)numofcol));
}
}
return("");
}
public int aftaleoplysninger_fra_pbs(dbData3060DataContext p_dbData3060)
{
int dummy = 0;
string rec;
string leverance;
string leverancespecifikation;
DateTime leverancedannelsesdato;
string sektion;
int wpbsfilesid;
int wleveranceid;
int AntalFiler = 0;
var qrypbsfiles = from h in p_dbData3060.Tblpbsfilename
where h.Pbsforsendelseid == null
join d in p_dbData3060.Tblpbsfile on h.Id equals d.Pbsfilesid
where d.Seqnr == 1 && d.Data.Substring(16, 4) == "0686" && d.Data.Substring(0, 2) == "BS"
select new
{
h.Id,
h.Path,
h.Filename,
leverancetype = d.Data.Substring(16, 4),
delsystem = d.Data.Substring(13, 3),
};
int DebugCount = qrypbsfiles.Count();
foreach (var rstpbsfiles in qrypbsfiles)
{
//try
{
wpbsfilesid = rstpbsfiles.Id;
AntalFiler++;
leverance = "";
sektion = "";
leverancespecifikation = "";
var qrypbsfile = from d in p_dbData3060.Tblpbsfile
where d.Pbsfilesid == wpbsfilesid && d.Data.Substring(0, 6) != "PBCNET"
orderby d.Seqnr
select d;
foreach (var rstpbsfile in qrypbsfile)
{
rec = rstpbsfile.Data;
// Bestem Leverance Type
if (rstpbsfile.Seqnr == 1)
{
if (rec.Substring(0, 5) == "BS002")
{ // Leverance Start
leverance = rec.Substring(16, 4);
leverancespecifikation = rec.Substring(20, 10);
leverancedannelsesdato = DateTime.Parse(rec.Substring(53, 4) + "-" + rec.Substring(51, 2) + "-" + rec.Substring(49, 2));
}
else
{
throw new Exception("241 - Første record er ikke en Leverance start record");
};
if (leverance == "0686")
{
// -- Leverance 0686
var antal = (from c in p_dbData3060.Tblfrapbs
where c.Leverancespecifikation == leverancespecifikation
select c).Count();
if (antal > 0)
{
throw new Exception("242 - Leverance med pbsfilesid: " + wpbsfilesid + " og leverancespecifikation: " + leverancespecifikation + " er indlæst tidligere");
}
wleveranceid = p_dbData3060.nextval_v2("leveranceid");
m_rec_pbsforsendelse = new Tblpbsforsendelse
{
Delsystem = "BS1",
Leverancetype = "0686",
Oprettetaf = "Bet",
Oprettet = DateTime.Now,
Leveranceid = wleveranceid
};
p_dbData3060.Tblpbsforsendelse.Add(m_rec_pbsforsendelse);
m_rec_frapbs = new Tblfrapbs
{
Delsystem = "BS1",
Leverancetype = "0686",
Leverancespecifikation = leverancespecifikation,
Leverancedannelsesdato = leverancedannelsesdato,
Udtrukket = DateTime.Now
};
m_rec_pbsforsendelse.Tblfrapbs.Add(m_rec_frapbs);
m_rec_pbsfiles = (from c in p_dbData3060.Tblpbsfilename
where c.Id == rstpbsfiles.Id
select c).First();
m_rec_pbsforsendelse.Tblpbsfilename.Add(m_rec_pbsfiles);
}
;
}
;
if (leverance == "0686")
{ // Leverance 0686
// Bestem Sektions Type
if (sektion == "")
{
if (rec.Substring(0, 5) == "BS012")
{ // Sektion Start
sektion = rec.Substring(13, 4);
}
else
{
if (!((rec.Substring(0, 5) == "BS992") || (rec.Substring(0, 5) == "BS002")))
{
throw new Exception("243 - Første record er ikke en Sektions start record");
}
;
};
}
;
if (rec.Substring(0, 5) == "BS002")
{ // Leverance start
// BEHANDL: Leverance start
dummy = 1;
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0195")
{ // Sektion 0195 Indbetalingskort til e-Boks
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0195"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0274"))
{ // Indbetalingskort til e-Boks
// BEHANDL: Indbetalingskort til e-Boks
readindbetalingskort042(p_dbData3060, sektion, "0274", rec);
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0195"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("244 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0196")
{ // Sektion 0196 elektronisk indbetalingskort
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0196"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0274"))
{ // elektronisk indbetalingskort
// BEHANDL: elektronisk indbetalingskort
readindbetalingskort042(p_dbData3060, sektion, "0274", rec);
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0196"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("245 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0197")
{ // Sektion 0197 papirindbetalingskort
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0197"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0274"))
{ // papirindbetalingskort
// BEHANDL: papirindbetalingskort
readindbetalingskort042(p_dbData3060, sektion, "0274", rec);
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0197"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("246 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0198")
{ // Sektion 0198 ej placerbare
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0198"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0274"))
{ // ej placerbare
// BEHANDL: ej placerbare
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0198"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("247 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (sektion == "0199")
{ // Sektion 0199 uanbringelig
if ((rec.Substring(0, 5) == "BS012") && (rec.Substring(13, 4) == "0199"))
{ // Sektion Start
// BEHANDL: Sektion Start
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS042") && (rec.Substring(13, 4) == "0275"))
{ // uanbringelig
// BEHANDL: uanbringelig
dummy = 1;
}
else if ((rec.Substring(0, 5) == "BS092") && (rec.Substring(13, 4) == "0199"))
{ // Sektion S**t
// BEHANDL: Sektion S**t
sektion = "";
}
else
{
throw new Exception("248 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
// -******************************************************************************************************
// -******************************************************************************************************
}
else if (rec.Substring(0, 5) == "BS992")
{ // Leverance s**t
// BEHANDL: Leverance S**t
leverance = "";
}
else
{
throw new Exception("249 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
}
else
{
throw new Exception("250 - Rec# " + rstpbsfile.Seqnr + " ukendt: " + rec);
};
}
}
/*
* catch (Exception e)
* {
* switch (e.Message.Substring(0, 3))
* {
* case "241": //241 - Første record er ikke en Leverance start record
* case "242": //242 - Leverancen er indlæst tidligere
* case "243": //243 - Første record er ikke en Sektions start record
* case "244": //244 - Record ukendt
* case "245": //245 - Record ukendt
* case "246": //246 - Record ukendt
* case "247": //247 - Record ukendt
* case "248": //248 - Record ukendt
* case "249": //249 - Record ukendt
* case "250": //250 - Record ukendt
* AntalFiler--;
* break;
*
* default:
* throw;
* }
* }
*/
}
if (dummy == 1)
{
dummy = 2;
}
p_dbData3060.SaveChanges();
return(AntalFiler);
}
