public ActionResult Excel(Guid id, string format, bool?titles, bool?useMailFlags)
{
var ctl = new MailingController {
UseTitles = titles ?? false, UseMailFlags = useMailFlags ?? false
};
switch (format)
{
case "Individual":
case "GroupAddress":
return(ExportPeople.FetchExcelList(id, maxExcelRows, useMailFlags ?? false).ToExcel());
case "Library":
return(ExportPeople.FetchExcelLibraryList(id));
case "AllFamily":
return(ExportPeople.FetchExcelListFamily(id));
case "Family":
return(ctl.FetchExcelFamily(id, maxExcelRows));
case "ParentsOf":
return(ctl.FetchExcelParents(id, maxExcelRows));
case "CouplesEither":
return(ctl.FetchExcelCouplesEither(id, maxExcelRows));
case "CouplesBoth":
return(ctl.FetchExcelCouplesBoth(id, maxExcelRows));
case "Involvement":
return(new ExcelResult(ExportInvolvements.InvolvementList(id), "Involvements.xls"));
case "Children":
return(ExportInvolvements.ChildrenList(id, maxExcelRows));
case "Church":
return(ExportInvolvements.ChurchList(id, maxExcelRows));
case "Attend":
return(ExportInvolvements.AttendList(id, maxExcelRows));
case "Promotion":
return(ExportInvolvements.PromoList(id, maxExcelRows));
case "IndividualPicture":
return(ExcelExportModel.Result(id));
case "FamilyMembers":
return(ExportPeople.FetchExcelListFamilyMembers(id));
case "OrgMembers":
return(OrgsMembersExcelModel.Export(Util2.CurrentOrgId ?? 0));
case "Groups":
return(ExportInvolvements.OrgMemberListGroups());
}
return(Content("no format"));
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
var q = ExcelExportModel.List(id);
if (!q.Any())
{
Response.Write("no data found");
return;
}
using (var ms = new MemoryStream())
{
var col = 0;
var row = 0;
var tt = dd.InsertTable(q.Count / 3 + (q.Count % 3 > 0 ? 1 : 0), 6);
tt.AutoFit = AutoFit.ColumnWidth;
for (var r = 0; r < tt.RowCount; r++)
{
var rr = tt.Rows[r];
rr.Height = Pixels(pa.RowHeight);
for (var i = 0; i < 6; i++)
{
var c = rr.Cells[i];
c.MarginRight = 0;
if (i % 2 == 0)
{
c.Width = Pixels(pa.PicWidth);
c.MarginLeft = 0;
}
else
{
c.Width = Pixels(pa.LabelWidth - pa.PicWidth);
c.MarginLeft = Pixels(pa.SpacerWidth);
}
}
}
foreach (var p in q)
{
col = FillCell(tt.Rows[row], col, p);
if (col < 6)
{
continue;
}
row++;
col = 0;
}
dd.SaveAs(ms);
Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
Response.AddHeader("content-disposition", "filename=picturedir.docx");
Response.AddHeader("content-length", ms.Length.ToString());
Response.BinaryWrite(ms.ToArray());
Response.End();
}
}
public ActionResult ContributionStatements(DateTime?fromDate, DateTime?endDate, string startswith, string sort, int?tagid, bool excludeelectronic, string customstatement = null, bool exportcontributors = false)
{
if (!fromDate.HasValue || !endDate.HasValue)
{
return(Content("<h3>Must have a Startdate and Enddate</h3>"));
}
var runningtotals = new ContributionsRun
{
Started = DateTime.Now,
Count = 0,
Processed = 0
};
var cs = Models.Report.ContributionStatements.GetStatementSpecification(customstatement);
if (!startswith.HasValue())
{
startswith = null;
}
if (exportcontributors)
{
var db = DbUtil.Db;
var noaddressok = !db.Setting("RequireAddressOnStatement", true);
const bool useMinAmt = true;
if (tagid == 0)
{
tagid = null;
}
var qc = APIContribution.Contributors(db, fromDate.Value, endDate.Value, 0, 0, 0, cs.Funds, noaddressok, useMinAmt, startswith, sort, tagid: tagid, excludeelectronic: excludeelectronic);
return(ExcelExportModel.ToDataTable(qc.ToList()).ToExcel("Contributors.xlsx"));
}
DbUtil.Db.ContributionsRuns.InsertOnSubmit(runningtotals);
DbUtil.Db.SubmitChanges();
var cul = DbUtil.Db.Setting("Culture", "en-US");
var host = Util.Host;
var output = Output();
if (tagid == 0)
{
tagid = null;
}
HostingEnvironment.QueueBackgroundWorkItem(ct =>
{
Thread.CurrentThread.CurrentUICulture = new CultureInfo(cul);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(cul);
var m = new ContributionStatementsExtract(host, fromDate.Value, endDate.Value, output, startswith, sort, tagid, excludeelectronic);
m.DoWork(cs);
});
return(Redirect("/Statements/Progress"));
}
public void ExcelListShouldNotHaveDeceased()
{
var requestManager = FakeRequestManager.Create();
var db = requestManager.CurrentDatabase;
var family = new Family();
db.Families.InsertOnSubmit(family);
db.SubmitChanges();
var hoh = new Person
{
Family = family,
FirstName = RandomString(),
LastName = RandomString(),
EmailAddress = RandomString() + "@example.com",
MemberStatusId = MemberStatusCode.Member,
PositionInFamilyId = PositionInFamily.PrimaryAdult
};
var child = new Person
{
Family = family,
FirstName = RandomString(),
LastName = RandomString(),
EmailAddress = RandomString() + "@example.com",
DeceasedDate = DateTime.Now,
MemberStatusId = MemberStatusCode.Member,
PositionInFamilyId = PositionInFamily.Child
};
db.People.InsertOnSubmit(hoh);
db.People.InsertOnSubmit(child);
db.SubmitChanges();
string code = "FamilyId = " + family.FamilyId;
var query = QueryModel.QueryCode(db, code);
query.Count().ShouldBeGreaterThan(0);
var ExcelPics = ExcelExportModel.List(query.QueryId.Value);
ExcelPics.Where(p => p.Children.HasValue()).Count().ShouldBe(0);
db.PurgePerson(hoh.PeopleId);
db.PurgePerson(child.PeopleId);
db.Families.DeleteOnSubmit(family);
db.SubmitChanges();
}
public override void ExecuteResult(ControllerContext context)
{
var response = context.HttpContext.Response;
var list = ExcelExportModel.DirectoryList(id);
if (list.Count == 0)
{
response.Write("no data found");
return;
}
var wc = new WebClient();
var bytes = wc.DownloadData(template);
var ms = new MemoryStream(bytes);
docx = DocX.Load(ms);
replacements = new EmailReplacements(DbUtil.Db, docx);
emptyReplacementsDictionary = replacements.DocXReplacementsDictionary(null);
if (emptyReplacementsDictionary.ContainsKey("{lastnamestartswith}"))
{
BuildDocumentIndexStyle(list);
}
else
{
BuildDocument(list);
}
response.Clear();
response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document";
response.AddHeader("content-disposition", $"attachment;filename={filename}-{DateTime.Now.ToSortableDateTime()}.docx");
ms = new MemoryStream();
docx.SaveAs(ms);
ms.WriteTo(response.OutputStream);
response.End();
}
public List <ExcelExportModel> ImportExcel()
{
List <ExcelExportModel> list = new List <ExcelExportModel>();
IFormFile postedFile = Request.Form.Files[0];
var tempPath = "";
var path = System.IO.Path.Combine(Directory.GetCurrentDirectory(), "wwwroot");
tempPath = path + "\\Data_Init\\Import\\Import.xlsx";
using (FileStream fileStream = new FileStream(tempPath, FileMode.Create))
{
postedFile.CopyToAsync(fileStream).Wait();
}
byte[] bin = System.IO.File.ReadAllBytes(tempPath);
FileInfo file = new FileInfo(tempPath);
//create a new Excel package in a memorystream
using (MemoryStream stream = new MemoryStream(bin))
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
//create an instance of the the first sheet in the loaded file
foreach (ExcelWorksheet worksheet in excelPackage.Workbook.Worksheets)
{
if (worksheet.Name == "Data")
{
for (var i = 2; i <= worksheet.Dimension.End.Row; i++)
{
ExcelExportModel excelItem = new ExcelExportModel();
if (worksheet.Cells[i, 1].Value != null)
{
excelItem.Image = worksheet.Cells[i, 1].Value.ToString();
}
if (worksheet.Cells[i, 2].Value != null)
{
excelItem.VariantTitle = worksheet.Cells[i, 2].Value.ToString();
}
if (worksheet.Cells[i, 3].Value != null)
{
excelItem.Price = worksheet.Cells[i, 3].Value.ToString();
}
if (worksheet.Cells[i, 4].Value != null)
{
excelItem.TotalPrice = worksheet.Cells[i, 4].Value.ToString();
}
if (worksheet.Cells[i, 5].Value != null)
{
excelItem.Quantity = worksheet.Cells[i, 5].Value.ToString();
}
if (worksheet.Cells[i, 6].Value != null)
{
excelItem.Order = worksheet.Cells[i, 6].Value.ToString();
}
if (worksheet.Cells[i, 7].Value != null)
{
excelItem.Phone = worksheet.Cells[i, 7].Value.ToString();
}
if (worksheet.Cells[i, 8].Value != null)
{
excelItem.SKU = worksheet.Cells[i, 8].Value.ToString();
}
if (worksheet.Cells[i, 9].Value != null)
{
excelItem.EMail = worksheet.Cells[i, 9].Value.ToString();
}
if (worksheet.Cells[i, 10].Value != null)
{
excelItem.ProductName = worksheet.Cells[i, 10].Value.ToString();
}
if (worksheet.Cells[i, 11].Value != null)
{
excelItem.OrderNotes = worksheet.Cells[i, 11].Value.ToString();
}
if (worksheet.Cells[i, 12].Value != null)
{
excelItem.City = worksheet.Cells[i, 12].Value.ToString();
}
if (worksheet.Cells[i, 13].Value != null)
{
excelItem.Country = worksheet.Cells[i, 13].Value.ToString();
}
if (worksheet.Cells[i, 14].Value != null)
{
excelItem.Province = worksheet.Cells[i, 14].Value.ToString();
}
if (worksheet.Cells[i, 15].Value != null)
{
excelItem.Zip = worksheet.Cells[i, 15].Value.ToString();
}
if (worksheet.Cells[i, 16].Value != null)
{
excelItem.Address = worksheet.Cells[i, 16].Value.ToString();
}
if (worksheet.Cells[i, 17].Value != null)
{
excelItem.ShippingFullname = worksheet.Cells[i, 17].Value.ToString();
}
if (worksheet.Cells[i, 18].Value != null)
{
excelItem.ProvinceCode = worksheet.Cells[i, 18].Value.ToString();
}
if (worksheet.Cells[i, 19].Value != null)
{
excelItem.AddressFull = worksheet.Cells[i, 19].Value.ToString();
}
if (worksheet.Cells[i, 20].Value != null)
{
excelItem.CountryCode = worksheet.Cells[i, 20].Value.ToString();
}
if (worksheet.Cells[i, 21].Value != null)
{
excelItem.TransactionCard = worksheet.Cells[i, 21].Value.ToString();
}
if (worksheet.Cells[i, 22].Value != null)
{
excelItem.PayPalTransactionId = worksheet.Cells[i, 22].Value.ToString();
}
if (worksheet.Cells[i, 23].Value != null)
{
excelItem.Tracking = worksheet.Cells[i, 23].Value.ToString();
}
if (worksheet.Cells[i, 24].Value != null)
{
excelItem.TrackingUrl = worksheet.Cells[i, 24].Value.ToString();
}
if (worksheet.Cells[i, 25].Value != null)
{
excelItem.Carrer = worksheet.Cells[i, 25].Value.ToString();
}
list.Add(excelItem);
}
}
}
}
return(list);
}
public ActionResult ContributionStatements(DateTime?fromDate, DateTime?endDate, string startswith, string sort, int?tagid, bool excludeelectronic, string customstatement = null, bool exportcontributors = false)
{
if (!fromDate.HasValue || !endDate.HasValue)
{
return(Content("<h3>Must have a Startdate and Enddate</h3>"));
}
if (fromDate.Value > endDate.Value)
{
return(Content("<h3>The Startdate must be earlier than the Enddate</h3>"));
}
var spec = ContributionStatementsExtract.GetStatementSpecification(CurrentDatabase, customstatement);
if (!startswith.HasValue())
{
startswith = null;
}
var noaddressok = !CurrentDatabase.Setting("RequireAddressOnStatement", true);
const bool useMinAmt = true;
if (tagid == 0)
{
tagid = null;
}
var qc = APIContribution.Contributors(CurrentDatabase, fromDate.Value, endDate.Value, 0, 0, 0, spec.Funds, noaddressok, useMinAmt, startswith, sort, tagid: tagid, excludeelectronic: excludeelectronic);
var contributors = qc.ToList();
if (exportcontributors)
{
return(ExcelExportModel.ToDataTable(contributors).ToExcel("Contributors.xlsx"));
}
var statementsRun = new ContributionsRun
{
Started = DateTime.Now,
Count = contributors.Count,
Processed = 0,
UUId = Guid.NewGuid(),
UserId = CurrentDatabase.UserId,
};
CurrentDatabase.ContributionsRuns.InsertOnSubmit(statementsRun);
CurrentDatabase.SubmitChanges();
var cul = CurrentDatabase.Setting("Culture", "en-US");
var host = CurrentDatabase.Host;
var id = $"{statementsRun.UUId:n}";
var output = Output(host, id);
if (tagid == 0)
{
tagid = null;
}
var showCheckNo = CurrentDatabase.Setting("RequireCheckNoOnStatement");
var showNotes = CurrentDatabase.Setting("RequireNotesOnStatement");
var statements = new ContributionStatements
{
UUId = Guid.Parse(id),
FromDate = fromDate.Value,
ToDate = endDate.Value,
typ = 3,
//TODO: once we switch to entirely html-based statement templates we won't need to check for these options
NumberOfColumns = showCheckNo || showNotes ? 1 : 2,
ShowCheckNo = showCheckNo,
ShowNotes = showNotes,
};
if (CurrentDatabase.Setting("UseNewStatementsFormat"))
{
// Must do this before entering the background worker because it relies on the Application context
statements.GetConverter();
}
var elmah = Elmah.ErrorLog.GetDefault(System.Web.HttpContext.Current);
HostingEnvironment.QueueBackgroundWorkItem(ct =>
{
Thread.CurrentThread.CurrentUICulture = new CultureInfo(cul);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(cul);
try
{
var m = new ContributionStatementsExtract(host, fromDate.Value, endDate.Value, output, startswith, sort, tagid, excludeelectronic)
{
id = id
};
m.DoWork(statements, spec, contributors);
}
catch (Exception e)
{
elmah.Log(new Elmah.Error(e));
var db = CMSDataContext.Create(host);
var run = db.ContributionsRuns.Single(c => c.UUId == Guid.Parse(id));
run.Error = e.Message;
run.Completed = DateTime.Now;
db.SubmitChanges();
}
});
return(Redirect($"/Statements/Progress/{id}"));
}
public List <OrderModel> Get()
{
List <string> listFullField = Utils.GetDataFromFile("ListFullField");
List <string> listRefunds = Utils.GetDataFromFile("Refunds");
Utils.SetProduct();
var listOrder = new List <OrderModel>();
//int Stt = 1;
foreach (var itemSite in Utils.GetApp())
{
var data = Utils.GetDataFromLink("List_Order", "orders", itemSite);
ShopifyModel shopifyModel = new ShopifyModel();
shopifyModel = JsonConvert.DeserializeObject <ShopifyModel>(data);
shopifyModel.orders.Reverse();
if (shopifyModel != null && shopifyModel.orders != null)
{
foreach (var item in shopifyModel.orders)
{
//show list
#region
OrderModel orderModel = new OrderModel();
orderModel.shippingAddress = item.shipping_address;
//orderModel.Stt = Stt.ToString();
orderModel.Email = item.email != null?item.email.ToString() : "";
orderModel.Id = item.id != null?item.id.ToString() : "";
orderModel.Order = item.name != null?item.name.ToString() : "";
orderModel.Date = item.created_at != null?item.created_at.ToString() : "";
orderModel.Customer = item.customer != null && item.customer.first_name != null?item.customer.first_name.ToString() : "";
orderModel.Payment = item.financial_status != null ? item.financial_status : "";
orderModel.Fulfillment = "UnFullField";
foreach (var itemFullField in listFullField)
{
if (orderModel.Order == itemFullField)
{
orderModel.Fulfillment = "FullField";
break;
}
}
foreach (var itemRefunds in listRefunds)
{
var check = itemRefunds.Split("~");
if (orderModel.Order == check[0])
{
orderModel.Payment = check[1];
break;
}
}
orderModel.Total = item.total_price != null ? item.total_price : "";
#endregion
//Show list excel
#region
foreach (var itemSub in item.line_items)
{
try
{
var itemProduct = new ExcelExportModel();
itemProduct.Image = Utils.GetImageUrl(itemSub.title, itemSub.variant_id.ToString(), Utils.productModel);
itemProduct.Order = item.name;
itemProduct.VariantTitle = itemSub.variant_title != null ? itemSub.variant_title : "";
itemProduct.Price = itemSub.price != null ? itemSub.price : "";
itemProduct.TotalPrice = item.total_price != null ? item.total_price : "";
itemProduct.Quantity = itemSub.quantity != 0 ? itemSub.quantity.ToString() : "";
itemProduct.Phone = item.shipping_address != null?item.shipping_address.phone.ToString() : "";
itemProduct.SKU = itemSub.sku != null ? itemSub.sku : "";
itemProduct.EMail = item.email != null ? item.email : "";
itemProduct.ProductName = itemSub.name != null ? itemSub.name : "";
itemProduct.OrderNotes = item.note != null ? item.note : "";
try
{
itemProduct.City = item.shipping_address != null?item.shipping_address.city.ToString() : "";
}
catch
{
}
itemProduct.Country = item.shipping_address != null ? item.shipping_address.country : "";
itemProduct.Province = item.shipping_address != null ? item.shipping_address.province : "";
itemProduct.Zip = item.shipping_address != null ? item.shipping_address.zip : "";
itemProduct.Address = item.shipping_address != null ? item.shipping_address.address1 : "";
itemProduct.ShippingFullname = item.shipping_address != null && item.shipping_address.last_name != null ? item.shipping_address.first_name + item.shipping_address.last_name : "";
itemProduct.ProvinceCode = item.shipping_address != null ? item.shipping_address.province_code : "";
itemProduct.AddressFull = item.shipping_address != null && item.shipping_address.address2 != null ? item.shipping_address.address1 + item.shipping_address.address2 : "";
itemProduct.CountryCode = item.shipping_address != null ? item.shipping_address.country_code : "";
orderModel.listProduct.Add(itemProduct);
}
catch
{
}
}
#endregion
listOrder.Add(orderModel);
// Stt++;
}
}
}
int Stt = listOrder.Count;
//foreach(var item in listOrder)
//{
// item.Stt = Stt.ToString();
// Stt--;
//}
var listTemp = new List <OrderModel>();
for (int i = Stt; i > 0 && i != 0; i--)
{
listOrder[i - 1].Stt = i.ToString();
listTemp.Add(listOrder[i - 1]);
}
return(listTemp);
}
