private static BundleDetail AddContributionDetail(DateTime date, int fundid,
string amount, string checkno, string routing, string account)
{
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
bd.Contribution.ContributionAmount = amount.GetAmount();
bd.Contribution.CheckNo = checkno;
var eac = Util.Encrypt(routing + "|" + account);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
return(bd);
}
private static int?BatchProcessAbundantLife(CsvReader csv, DateTime date, int?fundid)
{
var db = DbUtil.Db;
BundleHeader bh = null;
csv.MissingFieldAction = MissingFieldAction.ReplaceByEmpty;
var fid = fundid ?? BatchImportContributions.FirstFundId();
var prevbatch = "";
while (csv.ReadNextRecord())
{
if (!csv[Columns.TransNumber.ToInt()].HasValue())
{
continue; // skip summary rows
}
var batch = csv[Columns.BatchId.ToInt()];
if (bh == null || batch != prevbatch)
{
if (bh != null)
{
BatchImportContributions.FinishBundle(bh);
}
bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
prevbatch = batch;
}
var amount = csv[Columns.GrossAmount.ToInt()];
var bd = new BundleDetail
{
CreatedBy = db.UserId,
CreatedDate = DateTime.Now
};
bd.Contribution = new Contribution
{
CreatedBy = db.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fid,
ContributionStatusId = ContributionStatusCode.Recorded,
ContributionTypeId = ContributionTypeCode.CheckCash,
ContributionAmount = amount.GetAmount()
};
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh?.BundleHeaderId);
}
private static BundleDetail CreateContribution(DateTime date, int fundid)
{
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = Util.Now
};
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = Util.Now,
ContributionDate = date,
FundId = fundid,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash
};
return(bd);
}
internal static BundleDetail NewBundleDetail(DateTime date, int fundid, string amount)
{
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now
};
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
ContributionAmount = amount.GetAmount()
};
return(bd);
}
private Contribution ResolvePayment(Payment payment, ContributionFund fund, int?PersonId, BundleHeader bundle)
{
// find/create a touchpoint contribution from a pushpay payment
Contribution contribution;
var result = ContributionWithPayment(payment);
if (result.Any())
{
contribution = result.Single();
}
else
{
contribution = new Contribution
{
PeopleId = PersonId,
ContributionDate = payment.CreatedOn.ToLocalTime(),
ContributionAmount = payment.Amount.Amount,
ContributionTypeId = (fund.NonTaxDeductible == true) ? ContributionTypeCode.NonTaxDed : ContributionTypeCode.Online,
ContributionStatusId = (payment.Amount.Amount >= 0) ? ContributionStatusCode.Recorded : ContributionStatusCode.Reversed,
Origin = ContributionOriginCode.PushPay,
CreatedDate = DateTime.Now,
FundId = fund.FundId,
MetaInfo = PushPayTransactionNum + payment.TransactionId
};
db.Contributions.InsertOnSubmit(contribution);
db.SubmitChanges();
// assign contribution to bundle
BundleDetail bd = new BundleDetail
{
BundleHeaderId = bundle.BundleHeaderId,
ContributionId = contribution.ContributionId,
CreatedBy = 0,
CreatedDate = DateTime.Now
};
db.BundleDetails.InsertOnSubmit(bd);
db.SubmitChanges();
}
return(contribution);
}
private static BundleDetailConfig CreateBundleDetailConfig(CompatibilityAssetBundleManifest manifest)
{
List <BundleDetail> details = new List <BundleDetail>();
string[] bundles = manifest.GetAllAssetBundles();
foreach (var bundlePath in bundles)
{
BundleDetail detail = new BundleDetail();
detail.Path = bundlePath;
detail.Hash = manifest.GetAssetBundleHash(bundlePath).ToString();
detail.CRC = manifest.GetAssetBundleCrc(bundlePath).ToString();
detail.Dependencies = manifest.GetAllDependencies(bundlePath);
details.Add(detail);
}
BundleDetailConfig detailConfig = new BundleDetailConfig();
detailConfig.Details = details.ToArray();
return(detailConfig);
}
private static BundleDetail AddContributionDetail(DateTime date, int fundid,
string amount, string checkno, string routing, int peopleid)
{
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
bd.Contribution.ContributionAmount = amount.GetAmount();
bd.Contribution.CheckNo = checkno;
bd.Contribution.PeopleId = peopleid;
return(bd);
}
private static int?BatchProcessOakbrookChurch(CsvReader csv, DateTime date, int?fundid)
{
var cols = csv.GetFieldHeaders();
BundleHeader bh = null;
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
while (csv.ReadNextRecord())
{
if (csv[16] == "Credit")
{
if (bh != null)
{
BatchImportContributions.FinishBundle(bh);
}
bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
continue;
}
if (bh == null)
{
bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
}
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
string ck, rt, ac;
rt = csv[11];
ac = csv[13];
ck = csv[14];
bd.Contribution.ContributionAmount = csv[15].GetAmount();
bd.Contribution.CheckNo = ck;
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh.BundleHeaderId);
}
public void DeleteContributionWithTagsTest()
{
using (var db = CMSDataContext.Create(DatabaseFixture.Host))
{
var bundle = new BundleHeader
{
ChurchId = 1,
CreatedBy = 1,
CreatedDate = DateTime.Now,
RecordStatus = false,
BundleStatusId = BundleStatusCode.OpenForDataEntry,
ContributionDate = DateTime.Now,
BundleHeaderTypeId = BundleTypeCode.Online,
DepositDate = null,
BundleTotal = 0,
TotalCash = 0,
TotalChecks = 0
};
db.BundleHeaders.InsertOnSubmit(bundle);
db.SubmitChanges();
var contribution = new Contribution
{
PeopleId = 1,
ContributionDate = DateTime.Now,
ContributionAmount = 50,
ContributionTypeId = ContributionTypeCode.Online,
ContributionStatusId = ContributionStatusCode.Recorded,
CreatedDate = DateTime.Now,
FundId = 1
};
db.Contributions.InsertOnSubmit(contribution);
db.SubmitChanges();
BundleDetail bd = new BundleDetail
{
BundleHeaderId = bundle.BundleHeaderId,
ContributionId = contribution.ContributionId,
CreatedBy = 1,
CreatedDate = DateTime.Now
};
db.BundleDetails.InsertOnSubmit(bd);
bundle.BundleDetails.Add(bd);
var tag = new ContributionTag
{
ContributionId = contribution.ContributionId,
TagName = "Tag Test"
};
db.ContributionTags.InsertOnSubmit(tag);
db.SubmitChanges();
var model = new PostBundleModel(db, bundle.BundleHeaderId);
model.editid = contribution.ContributionId;
model.DeleteContribution();
db.ContributionTags.SingleOrDefault(t => t.ContributionId == contribution.ContributionId).ShouldBeNull();
db.Contributions.SingleOrDefault(c => c.ContributionId == contribution.ContributionId).ShouldBeNull();
// cleanup
db.BundleDetails.DeleteOnSubmit(bd);
db.BundleHeaders.DeleteOnSubmit(bundle);
db.SubmitChanges();
}
}
public static int?RunImport(CsvReader csv, DateTime date, int?fundid)
{
var prevbundle = -1;
var curbundle = 0;
BundleHeader bh = null;
var fieldCount = csv.FieldCount;
var cols = csv.GetFieldHeaders();
while (csv.ReadNextRecord())
{
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now
};
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash
};
string ac = null, rt = null, ck = null;
for (var c = 1; c < fieldCount; c++)
{
switch (cols[c])
{
case "DEPOSIT NUMBER":
curbundle = csv[c].ToInt();
if (curbundle != prevbundle)
{
if (bh != null)
{
BatchImportContributions.FinishBundle(bh);
}
bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
prevbundle = curbundle;
}
break;
case "AMOUNT":
bd.Contribution.ContributionAmount = csv[c].GetAmount();
break;
case "CHECK NUMBER":
ck = csv[c];
break;
case "ROUTING NUMBER":
rt = csv[c];
break;
case "ACCOUNT NUMBER":
ac = csv[c];
break;
}
}
if (!ck.HasValue())
{
if (ac.Contains(' '))
{
var a = ac.SplitStr(" ", 2);
ck = a[0];
ac = a[1];
}
}
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bd.Contribution.CheckNo = ck;
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh.BundleHeaderId);
}
public ActionResult Upload(string data)
{
CheckScanAuthentication authentication = new CheckScanAuthentication();
authentication.authenticate();
if (authentication.hasError())
{
return(CheckScanMessage.createLoginErrorReturn(authentication));
}
User user = authentication.getUser();
if (!user.InRole("Finance"))
{
return(CheckScanMessage.createErrorReturn("Finance role is required for check scanning"));
}
CheckInMessage message = CheckInMessage.createFromString(data);
List <CheckScanEntry> entries = JsonConvert.DeserializeObject <List <CheckScanEntry> >(message.data);
BundleHeader header;
if (message.id == 0)
{
header = new BundleHeader
{
BundleHeaderTypeId = 1,
BundleStatusId = BundleStatusCode.Open,
CreatedBy = user.UserId,
ContributionDate = DateTime.Now,
CreatedDate = DateTime.Now,
FundId = CurrentDatabase.Setting("DefaultFundId", "1").ToInt(),
RecordStatus = false,
TotalCash = 0,
TotalChecks = 0,
TotalEnvelopes = 0,
BundleTotal = 0
};
CurrentDatabase.BundleHeaders.InsertOnSubmit(header);
CurrentDatabase.SubmitChanges();
}
else
{
header = (from h in CurrentDatabase.BundleHeaders
where h.BundleHeaderId == message.id
select h).FirstOrDefault();
}
CheckScanMessage response = new CheckScanMessage();
if (header != null)
{
foreach (CheckScanEntry entry in entries)
{
Other other = new Other();
other.Created = DateTime.Now;
other.UserID = user.UserId;
if (entry.front.Length > 0)
{
other.First = Convert.FromBase64String(entry.front);
}
if (entry.back.Length > 0)
{
other.Second = Convert.FromBase64String(entry.back);
}
CurrentImageDatabase.Others.InsertOnSubmit(other);
CurrentImageDatabase.SubmitChanges();
var detail = new BundleDetail
{
BundleHeaderId = header.BundleHeaderId,
CreatedBy = user.UserId,
CreatedDate = DateTime.Now
};
string bankAccount = entry.routing.Length > 0 && entry.account.Length > 0 ? Util.Encrypt(entry.routing + "|" + entry.account) : "";
detail.Contribution = new Contribution
{
CreatedBy = user.UserId,
CreatedDate = detail.CreatedDate,
FundId = header.FundId ?? 0,
PeopleId = FindPerson(CurrentDatabase, entry.routing, entry.account),
ContributionDate = header.ContributionDate,
ContributionAmount = decimal.Parse(entry.amount),
ContributionStatusId = 0,
ContributionTypeId = 1,
ContributionDesc = entry.notes,
CheckNo = entry.number,
BankAccount = bankAccount,
ImageID = other.Id
};
header.BundleDetails.Add(detail);
CurrentDatabase.SubmitChanges();
}
response.setSuccess();
response.id = header.BundleHeaderId;
}
else
{
response.setError(1, "Invalid Bundle ID");
}
return(response);
}
public static int?BatchProcessBankOfNorthGeorgia(string text, DateTime date, int?fundid)
{
BundleHeader bh = null;
var sr = new StringReader(text);
string line = "";
do
{
line = sr.ReadLine();
if (line == null)
{
return(null);
}
} while (!line.Contains("Item ID"));
var sep = ',';
if (line.Contains("Item ID\t"))
{
sep = '\t';
}
for (; ;)
{
line = sr.ReadLine();
if (line == null)
{
break;
}
line = line.TrimStart();
var csv = line.Split(sep);
if (!csv[6].HasValue())
{
continue;
}
if (csv[21] == "VDP")
{
if (bh != null)
{
FinishBundle(bh);
}
bh = GetBundleHeader(date, DateTime.Now);
continue;
}
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
string ck, rt, ac;
rt = csv[14];
ac = csv[20];
ck = csv[17];
bd.Contribution.ContributionAmount = csv[9].GetAmount();
bd.Contribution.CheckNo = ck;
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
FinishBundle(bh);
return(bh.BundleHeaderId);
}
private void UploadPledges(UploadPeopleRun rt, ExcelPackage pkg)
{
var db = DbUtil.Create(host);
var data = FetchPledgeData(pkg.Workbook.Worksheets["Pledges"]).ToList();
rt.Count = data.Count;
rt.Description = $"Uploading Pledges {(Testing ? "in testing mode" : "for real")}";
rt.Processed = 0;
db2.SubmitChanges();
var weeks = (from g in data
group g by g.Date.Sunday() into weeklypledges
select weeklypledges).ToList();
BundleHeader bh = null;
foreach (var week in weeks)
{
FinishBundle(db, bh);
if (!Testing)
{
db.Dispose();
db = DbUtil.Create(host);
}
bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.Pledge,
BundleStatusId = BundleStatusCode.Closed,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Today,
ContributionDate = week.Key,
};
foreach (var pledge in week)
{
var pid = GetPeopleId(pledge.IndividualId);
if (!pid.HasValue)
{
throw new Exception($"peopleid not found from individualid {pledge.IndividualId}");
}
if (!Testing)
{
var f = db.FetchOrCreateFund(pledge.FundId, pledge.FundName ?? pledge.FundDescription);
f.FundPledgeFlag = true;
}
var bd = new BundleDetail();
bd.CreatedBy = Util.UserId;
bd.CreatedDate = DateTime.Now;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = pledge.Date,
FundId = pledge.FundId,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.Pledge,
ContributionAmount = pledge.Amount,
PeopleId = pid
};
bh.BundleDetails.Add(bd);
rt.Processed++;
db2.SubmitChanges();
}
}
FinishBundle(db, bh);
if (!Testing)
{
db.Dispose();
}
}
private static int?BatchProcessDiscoverCrosspoint(string text, DateTime date, int?fundid)
{
var db = DbUtil.Db;
var prevdt = DateTime.MinValue;
BundleHeader bh = null;
var sr = new StringReader(text);
for (; ;)
{
var line = sr.ReadLine();
if (line == null)
{
break;
}
var csv = line.Split(',');
var bd = new BundleDetail
{
CreatedBy = db.UserId,
CreatedDate = DateTime.Now,
};
var qf = from f in db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = db.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
var dt = csv[2].ToDate().Value;
if (dt != prevdt)
{
if (bh != null)
{
BatchImportContributions.FinishBundle(bh);
}
bh = BatchImportContributions.GetBundleHeader(dt, DateTime.Now);
prevdt = dt;
}
bd.Contribution.ContributionAmount = csv[1].ToDecimal();
var ck = csv[3];
var rt = csv[4];
var ac = csv[0];
bd.Contribution.CheckNo = ck;
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh.BundleHeaderId);
}
private void UploadPledges(UploadPeopleRun rt, ExcelPackage pkg)
{
var data = FetchPledgeData(pkg.Workbook.Worksheets["Pledges"]).ToList();
rt.Count = data.Count;
rt.Description = $"Uploading Pledges {(Testing ? "in testing mode" : "for real")}";
rt.Processed = 0;
ProgressDbContext.SubmitChanges();
var weeks = (from g in data
group g by g.Date.Sunday()
into weeklypledges
select weeklypledges).ToList();
BundleHeader bh = null;
foreach (var week in weeks)
{
FinishBundle(JobDbContext, bh);
bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.Pledge,
BundleStatusId = BundleStatusCode.Closed,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Today,
ContributionDate = week.Key
};
foreach (var pledge in week)
{
var pid = GetPeopleId(pledge);
var f = new ContributionFund {
FundId = 0
};
if (!Testing)
{
if (!pid.HasValue)
{
if (IgnoreMissingGifts)
{
_orphanedPledges.Append($"{pledge.IndividualId} {pledge.Date:d} {pledge.Amount:C}\n");
continue;
}
throw new Exception($"peopleid not found from individualid {pledge.IndividualId}");
}
f = ProgressDbContext.FetchOrCreateFund(pledge.FundId,
pledge.FundName ?? pledge.FundDescription);
f.FundPledgeFlag = true;
}
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = pledge.Date,
FundId = f.FundId,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.Pledge,
ContributionAmount = pledge.Amount,
PeopleId = pid,
CheckNo = pledge.CheckNo,
ContributionDesc = pledge.GiftDescription
}
};
bh.BundleDetails.Add(bd);
// save orphaned pledges
if (!Testing)
{
var currentOrphans = JobDbContext.Content("OrphanedPledges", "---", ContentTypeCode.TypeText);
currentOrphans.Body = _orphanedPledges.ToString();
JobDbContext.SubmitChanges();
}
rt.Processed++;
ProgressDbContext.SubmitChanges();
}
}
FinishBundle(JobDbContext, bh);
}
private static int?BatchProcessFbcStark2(string text, DateTime date, int?fundid)
{
var prevdt = DateTime.MinValue;
BundleHeader bh = null;
var sr = new StringReader(text);
string line = "";
do
{
line = sr.ReadLine();
if (line == null)
{
return(null);
}
} while (!line.StartsWith("Batch ID"));
var sep = ',';
if (line.StartsWith("Batch ID\t"))
{
sep = '\t';
}
for (; ;)
{
line = sr.ReadLine();
if (line == null)
{
break;
}
var csv = line.Split(sep);
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
var s = csv[3];
var m = s.Substring(0, 2).ToInt();
var d = s.Substring(2, 2).ToInt();
var y = s.Substring(4, 2).ToInt() + 2000;
var dt = new DateTime(y, m, d);
if (dt != prevdt)
{
if (bh != null)
{
BatchImportContributions.FinishBundle(bh);
}
bh = BatchImportContributions.GetBundleHeader(dt, DateTime.Now);
prevdt = dt;
}
var rt = csv[7];
var ac = csv[8];
var ck = csv[9];
bd.Contribution.ContributionAmount = csv[10].GetAmount();
bd.Contribution.CheckNo = ck;
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh.BundleHeaderId);
}
public static int?BatchProcessFbcStark(string text, DateTime date, int?fundid)
{
var prevdt = DateTime.MinValue;
BundleHeader bh = null;
var sr = new StringReader(text);
for (; ;)
{
var line = sr.ReadLine();
if (line == null)
{
break;
}
var csv = line.Split(',');
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
var dtint = csv[3].ToLong();
var y = (int)(dtint % 10000);
var m = (int)(dtint / 1000000);
var d = (int)(dtint / 10000) % 100;
var dt = new DateTime(y, m, d);
if (dt != prevdt)
{
if (bh != null)
{
FinishBundle(bh);
}
bh = GetBundleHeader(dt, DateTime.Now);
prevdt = dt;
}
bd.Contribution.ContributionAmount = csv[5].GetAmount() / 100;
string ck, rt, ac;
ck = csv[4];
rt = csv[6];
ac = csv[7];
bd.Contribution.CheckNo = ck;
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
FinishBundle(bh);
return(bh.BundleHeaderId);
}
private void UploadGifts(UploadPeopleRun rt, ExcelPackage pkg)
{
var db = DbUtil.Create(Host);
var data = FetchContributionData(pkg.Workbook.Worksheets["Gift Data"]).ToList();
rt.Count = data.Count;
rt.Description = $"Uploading Gifts {(Testing ? "in testing mode" : "for real")}";
rt.Processed = 0;
Db2.SubmitChanges();
var weeks = (from g in data
group g by g.Date.Sunday() into weeklygifts
select weeklygifts).ToList();
BundleHeader bh = null;
var c = db.Content("OrphanedGifts", "---", ContentTypeCode.TypeText);
c.Body = "";
db.SubmitChanges();
foreach (var week in weeks)
{
FinishBundle(db, bh);
if (!Testing)
{
db.Dispose();
db = DbUtil.Create(Host);
}
bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.ChecksAndCash,
BundleStatusId = BundleStatusCode.Closed,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Today,
ContributionDate = week.Key,
};
foreach (var gift in week)
{
var pid = GetPeopleId(gift);
if (!Testing)
{
if (!pid.HasValue)
{
if (IgnoreMissingGifts)
{
c = db.Content("OrphanedGifts");
c.Body += $"{gift.IndividualId} {gift.Date:d} {gift.Amount:C}\n";
db.SubmitChanges();
continue;
}
else
{
throw new Exception($"peopleid not found from individualid {gift.IndividualId}");
}
}
}
if (!Testing)
{
db.FetchOrCreateFund(gift.FundId, gift.FundName ?? gift.FundDescription);
}
var bd = new BundleDetail();
bd.CreatedBy = Util.UserId;
bd.CreatedDate = DateTime.Now;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = gift.Date,
FundId = gift.FundId,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
ContributionAmount = gift.Amount,
CheckNo = gift.CheckNo,
PeopleId = pid
};
bh.BundleDetails.Add(bd);
rt.Processed++;
Db2.SubmitChanges();
}
}
FinishBundle(db, bh);
if (!Testing)
{
db.Dispose();
}
}
private static int?BatchProcessMagTek(string lines, DateTime date)
{
var now = DateTime.Now;
var bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.ChecksAndCash,
BundleStatusId = BundleStatusCode.Open,
ContributionDate = date,
CreatedBy = Util.UserId,
CreatedDate = now,
FundId = DbUtil.Db.Setting("DefaultFundId", "1").ToInt()
};
DbUtil.Db.BundleHeaders.InsertOnSubmit(bh);
var re = new Regex(
@"(T(?<rt>[\d?]+)T(?<ac>[\d ?]*)U\s*(?<ck>[\d?]+))|
(CT(?<rt>[\d?]+)A(?<ac>[\d ?]*)C(?<ck>[\d?]+)M)",
RegexOptions.IgnoreCase);
var m = re.Match(lines);
while (m.Success)
{
var rt = m.Groups["rt"].Value;
var ac = m.Groups["ac"].Value;
var ck = m.Groups["ck"].Value;
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = now,
};
bh.BundleDetails.Add(bd);
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = now,
ContributionDate = date,
FundId = qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
bd.Contribution.ContributionDesc = ck;
var eac = Util.Encrypt(rt + "," + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bd.Contribution.ContributionDesc = ck;
m = m.NextMatch();
}
bh.TotalChecks = 0;
bh.TotalCash = 0;
bh.TotalEnvelopes = 0;
DbUtil.Db.SubmitChanges();
return(bh.BundleHeaderId);
}
private static int?BatchProcessSunTrust(CsvReader csv, DateTime date, int?fundid)
{
var prevbundle = -1;
var curbundle = 0;
var bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
var fieldCount = csv.FieldCount;
var cols = csv.GetFieldHeaders();
while (csv.ReadNextRecord())
{
var bd = new BundleDetail
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
};
var qf = from f in DbUtil.Db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
string ac = null, rt = null, ck = null, sn = null;
for (var c = 1; c < fieldCount; c++)
{
switch (cols[c].ToLower())
{
case "deposit_id":
curbundle = csv[c].ToInt();
if (curbundle != prevbundle)
{
if (curbundle == 3143)
{
foreach (var i in bh.BundleDetails)
{
Debug.WriteLine(i.Contribution.ContributionDesc);
Debug.WriteLine(i.Contribution.BankAccount);
}
}
BatchImportContributions.FinishBundle(bh);
bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
prevbundle = curbundle;
}
break;
case "amount":
bd.Contribution.ContributionAmount = csv[c].GetAmount();
break;
case "tran_code":
ck = csv[c];
break;
case "serial_number":
sn = csv[c];
break;
case "routing_transit":
rt = csv[c];
break;
case "account_number":
ac = csv[c];
break;
}
}
if (!ck.HasValue())
{
if (ac.Contains(' '))
{
var a = ac.SplitStr(" ", 2);
ck = a[0];
ac = a[1];
}
}
bd.Contribution.ContributionDesc = string.Join(" ", sn, ck);
var eac = Util.Encrypt(rt + "|" + ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh.BundleHeaderId);
}
public object PostContribution(PostBundleController ctl)
{
try
{
var bd = new BundleDetail
{
BundleHeaderId = id,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now
};
int type;
switch (PLNT)
{
case "PL":
type = ContributionTypeCode.Pledge;
break;
case "NT":
type = ContributionTypeCode.NonTaxDed;
break;
case "GK":
type = ContributionTypeCode.GiftInKind;
break;
case "SK":
type = ContributionTypeCode.Stock;
break;
default:
type = ContributionTypeCode.CheckCash;
break;
}
decimal?othersplitamt = null;
if (splitfrom > 0)
{
var q = from c in DbUtil.Db.Contributions
where c.ContributionId == splitfrom
select new
{
c,
bd = c.BundleDetails.First()
};
var i = q.Single();
othersplitamt = i.c.ContributionAmount - amt;
i.c.ContributionAmount = othersplitamt;
DbUtil.Db.SubmitChanges();
bd.BundleSort1 = i.bd.BundleDetailId;
}
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = bd.CreatedDate,
FundId = fund,
PeopleId = pid.ToInt2(),
ContributionDate = contributiondate ?? Bundle.ContributionDate,
ContributionAmount = amt,
ContributionStatusId = 0,
ContributionTypeId = type,
ContributionDesc = notes,
CheckNo = (checkno ?? "").Trim().Truncate(20)
};
Bundle.BundleDetails.Add(bd);
DbUtil.Db.SubmitChanges();
return(ContributionRowData(ctl, bd.ContributionId, othersplitamt));
}
catch (Exception ex)
{
return(new { error = ex.Message });
}
}
private static int?BatchProcessRegions(CsvReader csv, DateTime date, int?fundid)
{
var db = DbUtil.Db;
var userId = db.UserId;
var prevbundle = -1;
var bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
var re = new Regex(
@"(?<g1>d(?<rt>.*?)d\sc(?<ac>.*?)(?:c|\s)(?<ck>.*?))$
|(?<g2>d(?<rt>.*?)d(?<ck>.*?)(?:c|\s)(?<ac>.*?)c[\s!]*)$
|(?<g3>d(?<rt>.*?)d(?<ac>.*?)c(?<ck>.*?$))
|(?<g4>c(?<ck>.*?)c\s*d(?<rt>.*?)d(?<ac>.*?)c\s*$)
", RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace);
var fieldCount = csv.FieldCount;
var cols = csv.GetFieldHeaders();
while (csv.ReadNextRecord())
{
if (!csv[12].Contains("Check"))
{
continue;
}
var bd = new BundleDetail
{
CreatedBy = userId,
CreatedDate = DateTime.Now,
};
var qf = from f in db.ContributionFunds
where f.FundStatusId == 1
orderby f.FundId
select f.FundId;
bd.Contribution = new Contribution
{
CreatedBy = userId,
CreatedDate = DateTime.Now,
ContributionDate = date,
FundId = fundid ?? qf.First(),
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.CheckCash,
};
string accountNumber = null;
string routingNumber = null;
string checkingNumber = null;
string micr = null;
for (var c = 1; c < fieldCount; c++)
{
switch (cols[c].ToLower())
{
case "deposit number":
var curbundle = csv[c].ToInt();
if (curbundle != prevbundle)
{
if (curbundle == 3143)
{
foreach (var i in bh.BundleDetails)
{
Debug.WriteLine(i.Contribution.ContributionDesc);
Debug.WriteLine(i.Contribution.BankAccount);
}
}
BatchImportContributions.FinishBundle(bh);
bh = BatchImportContributions.GetBundleHeader(date, DateTime.Now);
prevbundle = curbundle;
}
break;
case "post amount":
bd.Contribution.ContributionAmount = csv[c].GetAmount();
break;
case "micr":
micr = csv[c];
break;
case "r/t":
routingNumber = csv[c];
break;
case "account number":
accountNumber = csv[c];
break;
case "check number":
checkingNumber = csv[c];
break;
}
}
if (string.IsNullOrEmpty(routingNumber) ||
string.IsNullOrEmpty(accountNumber) ||
string.IsNullOrEmpty(checkingNumber))
{
var m = re.Match(micr);
routingNumber = m.Groups["rt"].Value;
accountNumber = m.Groups["ac"].Value;
bd.Contribution.CheckNo = m.Groups["ck"].Value.Truncate(20);
}
else
{
bd.Contribution.CheckNo = checkingNumber;
}
var eac = Util.Encrypt(routingNumber + "|" + accountNumber);
var q = from kc in db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
if (pid != null)
{
bd.Contribution.PeopleId = pid;
}
bd.Contribution.BankAccount = eac;
bh.BundleDetails.Add(bd);
}
BatchImportContributions.FinishBundle(bh);
return(bh.BundleHeaderId);
}
private static int?Import(CsvReader csv, DateTime date, int?fundid)
{
var cols = csv.GetFieldHeaders();
var now = DateTime.Now;
var bh = BatchImportContributions.GetBundleHeader(date, now);
while (csv.ReadNextRecord())
{
string ac = null, oth = null, first = null, last = null, addr = null, name = null, email = null;
var dt = date;
for (var c = 1; c < csv.FieldCount; c++)
{
var col = cols[c].Trim();
switch (col)
{
case "Date Entered":
dt = csv[c].ToDate() ?? date;
break;
case "ProfileID":
ac = csv[c];
break;
case "First Name":
first = csv[c];
break;
case "Last Name":
last = csv[c];
break;
case "Full Name":
name = csv[c];
break;
case "Address":
addr = csv[c];
break;
case "Email Address":
email = csv[c];
break;
case "Designation for "Other"":
oth = csv[c];
break;
}
}
if (ac.ToInt() == 0)
{
ac = email;
}
var eac = Util.Encrypt(ac);
var q = from kc in DbUtil.Db.CardIdentifiers
where kc.Id == eac
select kc.PeopleId;
var pid = q.SingleOrDefault();
string bankac = null;
ExtraDatum ed = null;
if (pid == null)
{
bankac = eac;
string person;
if (last.HasValue())
{
person = $"{last}, {first}; {addr}";
}
else
{
person = $"{name}; {addr}";
}
ed = new ExtraDatum {
Data = person, Stamp = Util.Now
};
}
BundleDetail bd = null;
var defaultfundid = DbUtil.Db.Setting("DefaultFundId", "1").ToInt();
for (var c = 0; c < csv.FieldCount; c++)
{
var col = cols[c].Trim();
if (col != "Amount" && !col.Contains("Comment") && csv[c].StartsWith("$") && csv[c].GetAmount() > 0)
{
var fid = FindFund(col);
bd = CreateContribution(date, fid ?? defaultfundid);
bd.Contribution.ContributionAmount = csv[c].GetAmount();
if (col == "Other")
{
col = oth;
}
if (!fundid.HasValue)
{
bd.Contribution.ContributionDesc = col;
}
if (ac.HasValue())
{
bd.Contribution.BankAccount = bankac;
}
bd.Contribution.PeopleId = pid;
bh.BundleDetails.Add(bd);
if (ed != null)
{
bd.Contribution.ExtraDatum = ed;
}
}
}
}
FinishBundle(bh);
return(bh.BundleHeaderId);
}
private void UploadPledges(UploadPeopleRun rt, ExcelPackage pkg)
{
//var db = DbUtil.Create(Host);
var data = FetchPledgeData(pkg.Workbook.Worksheets["Pledges"]).ToList();
rt.Count = data.Count;
rt.Description = $"Uploading Pledges {(Testing ? "in testing mode" : "for real")}";
rt.Processed = 0;
Db2.SubmitChanges();
var weeks = (from g in data
group g by g.Date.Sunday() into weeklypledges
select weeklypledges).ToList();
BundleHeader bh = null;
var c = Db2.Content("OrphanedPledges", "---", ContentTypeCode.TypeText);
c.Body = "";
Db2.SubmitChanges();
foreach (var week in weeks)
{
FinishBundle(Db2, bh);
//if (!Testing)
//{
// Db2.Dispose();
// db = DbUtil.Create(Host);
//}
bh = new BundleHeader
{
BundleHeaderTypeId = BundleTypeCode.Pledge,
BundleStatusId = BundleStatusCode.Closed,
CreatedBy = Util.UserId,
CreatedDate = DateTime.Today,
ContributionDate = week.Key,
};
foreach (var pledge in week)
{
var pid = GetPeopleId(pledge);
var f = new ContributionFund {
FundId = 0
};
if (!Testing)
{
if (!pid.HasValue)
{
if (IgnoreMissingGifts)
{
c = Db2.Content("OrphanedPledges");
c.Body += $"{pledge.IndividualId} {pledge.Date:d} {pledge.Amount:C}\n";
Db2.SubmitChanges();
continue;
}
else
{
throw new Exception($"peopleid not found from individualid {pledge.IndividualId}");
}
}
f = Db2.FetchOrCreateFund(pledge.FundId, pledge.FundName ?? pledge.FundDescription);
f.FundPledgeFlag = true;
}
var bd = new BundleDetail();
bd.CreatedBy = Util.UserId;
bd.CreatedDate = DateTime.Now;
bd.Contribution = new Contribution
{
CreatedBy = Util.UserId,
CreatedDate = DateTime.Now,
ContributionDate = pledge.Date,
FundId = f.FundId,
ContributionStatusId = 0,
ContributionTypeId = ContributionTypeCode.Pledge,
ContributionAmount = pledge.Amount,
PeopleId = pid
};
bh.BundleDetails.Add(bd);
rt.Processed++;
Db2.SubmitChanges();
}
}
FinishBundle(Db2, bh);
//if (!Testing)
//{
// DbUtil.Db.Dispose();
//}
}
