public string AddPbcnetRecords(dbData3060 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("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 int betalinger_fra_pbs(dbData3060 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)
{
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)
{
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("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);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0237")))
{
// -- Afvist betaling
// -- BEHANDL: Afvist betaling
read042(sektion, "0237", rec);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0238")))
{
// -- Afmeldt betaling
// -- BEHANDL: Afmeldt betaling
read042(sektion, "0238", rec);
}
else if (((rec.Substring(0, 5) == "BS042") &&
(rec.Substring(13, 4) == "0239")))
{
// -- Tilbagef?rt betaling
// -- BEHANDL: Tilbagef?rt betaling
read042(sektion, "0239", rec);
}
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);
}
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);
}
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
p_dbData3060.SaveChanges();
return(AntalFiler);
}
public int aftaleoplysninger_fra_pbs(dbData3060 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("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 string WriteTilSFtp(dbData3060 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;
sendAttachedFile(p_dbData3060, TilPBSFilename, b_TilPBSFile, true);
string fullpath = m_rec_sftp.inbound + "/" + TilPBSFilename;
MemoryStream ms_TilPBSFile = new MemoryStream(b_TilPBSFile);
Program.Log(string.Format("Start Upload of {0}", fullpath));
m_sftp.UploadFile(ms_TilPBSFile, fullpath);
Program.Log(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 int ReadFromLocalFile(dbData3060 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];
Program.Log(string.Format("Start Reading of {0}", fullpath));
using (FileStream ts = file.Open(FileMode.Open, FileAccess.Read, FileShare.None))
{
ts.Read(b_data, 0, numBytes);
}
Program.Log(string.Format("{0} Reading Completed", fullpath));
sendAttachedFile(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_pbsfiles = new tblpbsfilename
{
type = 8,
path = file.Directory.FullName,
filename = file.Name,
size = (int)file.Length,
atime = file.LastAccessTime,
mtime = file.LastWriteTime
};
p_dbData3060.tblpbsfilename.Add(m_rec_pbsfiles);
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);
}
}
m_rec_pbsfiles.transmittime = DateTime.Now;
//var cb = new SqlConnectionStringBuilder(p_dbData3060.Connection.ConnectionString);
Program.Log(string.Format("Start Write {0} to SQL Database {1} on {2}", file.Name, "cb.InitialCatalog", "cb.DataSource"));
p_dbData3060.SaveChanges();
Program.Log(string.Format("{0} Write to SQL Database Completed", file.Name));
return(1);
}
public int ReadFraSFtp(dbData3060 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);
Program.Log(string.Format("Start Download of {0}", fullpath));
m_sftp.DownloadFile(fullpath, stream);
Program.Log(string.Format("{0} Download Completed", fullpath));
sendAttachedFile(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);
}
}
m_rec_pbsfiles.transmittime = DateTime.Now;
//var cb = new SqlConnectionStringBuilder(p_dbData3060.Connection.ConnectionString);
Program.Log(string.Format("Start Write {0} to SQL Database {1} on {2}", rec_pbsnetdir.Filename, "cb.InitialCatalog", "cb.DataSource"));
p_dbData3060.SaveChanges();
Program.Log(string.Format("{0} Write to SQL Database Completed", rec_pbsnetdir.Filename));
}
}
p_dbData3060.SaveChanges();
return(AntalFiler);
}
public void krdfaktura_overfoersel_action(dbData3060 p_dbData3060, int lobnr)
{
string rec;
int seq = 0;
int wleveranceid;
DateTime wdispositionsdato;
int whour;
int wbankdage;
// Betalingsoplysninger
int belobint;
// Tællere
int antalos5; // Antal OS5: Antal foranstående OS5 records
int belobos5; // Beløb: Nettobeløb i OS5 records
int antalsek; // Antal sektioner i leverancen
int antalos5tot; // Antal OS5: Antal foranstående OS5 records
int belobos5tot; // Beløb: Nettobeløb i OS5 records
whour = DateTime.Now.Hour;
if (whour > 17)
{
wbankdage = 3;
}
else
{
wbankdage = 2;
}
wdispositionsdato = clsOverfoersel.bankdageplus(DateTime.Now, wbankdage);
antalos5 = 0;
belobos5 = 0;
antalsek = 0;
antalos5tot = 0;
belobos5tot = 0;
{
var antal = (from c in p_dbData3060.tbltilpbs
where c.id == lobnr
select c).Count();
if (antal == 0)
{
throw new Exception("101 - Der er ingen PBS forsendelse for id: " + lobnr);
}
}
{
var antal = (from c in p_dbData3060.tbltilpbs
where c.id == lobnr && c.pbsforsendelseid != null
select c).Count();
if (antal > 0)
{
throw new Exception("102 - Pbsforsendelse for id: " + lobnr + " er allerede sendt");
}
}
{
var antal = (from c in p_dbData3060.tbloverforsel
where c.tilpbsid == lobnr
select c).Count();
if (antal == 0)
{
throw new Exception("103 - Der er ingen pbs transaktioner for tilpbsid: " + lobnr);
}
}
var rsttil = (from c in p_dbData3060.tbltilpbs
where c.id == lobnr
select c).First();
if (rsttil.udtrukket == null)
{
rsttil.udtrukket = DateTime.Now;
}
if (rsttil.bilagdato == null)
{
rsttil.bilagdato = rsttil.udtrukket;
}
if (rsttil.delsystem == null)
{
rsttil.delsystem = "OS1";
}
if (rsttil.leverancetype != null)
{
rsttil.leverancetype = null;
}
p_dbData3060.SaveChanges();
wleveranceid = p_dbData3060.nextval("leveranceid");
tblpbsforsendelse rec_pbsforsendelse = new tblpbsforsendelse
{
delsystem = rsttil.delsystem,
leverancetype = rsttil.leverancetype,
oprettetaf = "Udb",
oprettet = DateTime.Now,
leveranceid = wleveranceid
};
p_dbData3060.tblpbsforsendelse.Add(rec_pbsforsendelse);
rec_pbsforsendelse.tbltilpbs.Add(rsttil);
tblpbsfilename rec_pbsfiles = new tblpbsfilename();
rec_pbsforsendelse.tblpbsfilename.Add(rec_pbsfiles);
tblkreditor krd = (from k in p_dbData3060.tblkreditor
where k.delsystem == rsttil.delsystem
select k).First();
// -- Leverance Start - OS1
// - rstkrd.Datalevnr - Dataleverandørnr.: Dataleverandørens SE-nummer
// - wleveranceid - Leveranceidentifikation: Løbenummer efter eget valg
rec = writeOS1(krd.datalevnr, wleveranceid);
tblpbsfile rec_pbsfile = new tblpbsfile {
seqnr = ++seq, data = rec
};
rec_pbsfiles.tblpbsfile.Add(rec_pbsfile);
var qrydeb = from h in p_dbData3060.tbloverforsel
where h.tilpbsid == lobnr
select h;
// Start loop over betalinger i tbloverforsel
int testantal = qrydeb.Count();
foreach (tbloverforsel rec_overfoersel in qrydeb)
{
DateTime Betalingsdato = (rec_overfoersel.betalingsdato == null) ? wdispositionsdato : (DateTime)rec_overfoersel.betalingsdato;
if (Betalingsdato < wdispositionsdato)
{
Betalingsdato = wdispositionsdato;
}
rec_overfoersel.betalingsdato = Betalingsdato; //opdater aktuel betalingsdato
// Sektion start – (OS2)
antalos5 = 0;
belobos5 = 0;
// -- OS2
// - Betalingsdato - Dispositionsdato
// - krd.regnr - Reg.nr.: Overførselsregistreringsnummer
// - krd.kontonr - Kontonr.: Overførselskontonummer
// - krd.datalevnr - Dataleverandørnr.: Dataleverandørens SE-nummer
rec = writeOS2(Betalingsdato, krd.regnr, krd.kontonr, krd.datalevnr);
rec_pbsfile = new tblpbsfile {
seqnr = ++seq, data = rec
};
rec_pbsfiles.tblpbsfile.Add(rec_pbsfile);
++antalsek;
// -- Forfald betaling
if (rec_overfoersel.advisbelob > 0)
{
belobint = (int)(rec_overfoersel.advisbelob * ((decimal)100));
belobos5 = belobos5 + belobint;
belobos5tot = belobos5tot + belobint;
}
else
{
belobint = 0;
}
// -- OS5
// - debinfo.bankregnr - Betalingsmodtager registreringsnummer
// - debinfo.bankkontonr - Betalingsmodtager kontonummer
// - belobint - Beløb: Beløb i øre uden fortegn
// - Betalingsdato - Dispositionsdato
// - krd.regnr - Reg.nr.: Overførselsregistreringsnummer
// - krd.kontonr - Kontonr.: Overførselskontonummer
// - deb.advistekst - Tekst på Betalingsmodtagers kontoudtog
// - deb.SFakID - Ref til betalingsmodtager til eget brug
rec = writeOS5(rec_overfoersel.bankregnr, rec_overfoersel.bankkontonr, belobint, Betalingsdato, krd.regnr, krd.kontonr, rec_overfoersel.advistekst, (int)rec_overfoersel.SFakID);
rec_pbsfile = new tblpbsfile {
seqnr = ++seq, data = rec
};
rec_pbsfiles.tblpbsfile.Add(rec_pbsfile);
antalos5++;
antalos5tot++;
// -- Sektion s**t – (OS8)
// - OS8
// - antalos5 - Antal 042: Antal foranstående 042 records
// - belobos5 - Beløb: Nettobeløb i 042 records
// - Betalingsdato - Dispositionsdato
// - krd.regnr - Reg.nr.: Overførselsregistreringsnummer
// - krd.kontonr - Kontonr.: Overførselskontonummer
// - krd.datalevnr - Dataleverandørnr.: Dataleverandørens SE-nummer
rec = writeOS8(antalos5, belobos5, Betalingsdato, krd.regnr, krd.kontonr, krd.datalevnr);
rec_pbsfile = new tblpbsfile {
seqnr = ++seq, data = rec
};
rec_pbsfiles.tblpbsfile.Add(rec_pbsfile);
}
// -- Leverance s**t - (OS9)
// --OS9
// - antalos5tot - Antal 042: Antal foranstående 042 records
// - belobos5tot - Beløb: Nettobeløb i 042 records
// - krd.datalevnr - Dataleverandørnr.: Dataleverandørens SE-nummer
rec = writeOS9(antalos5tot, belobos5tot, krd.datalevnr);
rec_pbsfile = new tblpbsfile {
seqnr = ++seq, data = rec
};
rec_pbsfiles.tblpbsfile.Add(rec_pbsfile);
rsttil.udtrukket = DateTime.Now;
rsttil.leverancespecifikation = wleveranceid.ToString();
p_dbData3060.SaveChanges();
}
public int aftaleoplysninger_fra_pbs(dbData3060 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("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);
}