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); }