public IHttpActionResult GenMemberPhoneNumbers(String userId) { var id = Guid.Empty; if (Guid.TryParse(userId, out id)) { using (var ctx = new RT2020Entities()) { var staff = ctx.Staff.Where(x => x.StaffId == id && x.Status > 0).SingleOrDefault(); if (staff != null) { BackgroundJob.Enqueue(() => SuperUserHelper.GenMemberPhoneNumbers(id)); //SuperUserHelper.GenMemberPhoneNumbers(id); log.Info(String.Format("[bot, SuperUser, GenMemberPhoneNumbers] \r\nHangfire findished the Job\r\nUser = {0}", String.IsNullOrEmpty(staff.FullName) ? staff.StaffCode : staff.FullName)); return(StatusCode(HttpStatusCode.Accepted)); // 202 or use: return new StatusCodeResult(202); } } } log.Info(String.Format("[bot, SuperUser, GenMemberPhoneNumbers] \r\nError found before submitting to Hangfire\r\nUser Id = {0}", userId)); return(BadRequest()); }
/// <summary> /// Generate dbo.Country. Write your time consuming task here /// </summary> /// <param name="s"></param> public void LTT_CountryCode(string s) { using (var ctx = new RT2020Entities()) { ctx.Configuration.LazyLoadingEnabled = false; #region Generate missing dbo.Country from VWG.Community.Util.CountryCodes var dboCounttries = ctx.Country.ToList(); var ccodes = new CountryCodes(); var codelist = ccodes.GetList(); OpenCC.Load(); using (var scope = ctx.Database.BeginTransaction()) { try { foreach (var item in codelist) { //! CountryCode = ISO3166_1_Alpha_2,CountryName = CLDR_displayname if (item.ISO3166_1_Alpha_2 == String.Empty) { continue; } // 2020.11.25 paulus: 再改番用 ISO3166_1_Alpha_2 //var exist = ctx.Country.Where(x => x.CountryCode == item.FIPS).FirstOrDefault(); var exist = ctx.Country.Where(x => x.CountryCode == item.ISO3166_1_Alpha_2).FirstOrDefault(); if (exist == null) { exist = new Country(); exist.CountryId = Guid.NewGuid(); exist.CountryCode = item.ISO3166_1_Alpha_2; exist.CountryName = item.CLDR_displayname; if (!String.IsNullOrEmpty(item.official_name_cn)) { exist.CountryName_Chs = item.official_name_cn; exist.CountryName_Cht = OpenCC.Convert(item.official_name_cn, OpenCCType.s2hk.ToString("g")); } ctx.Country.Add(exist); ctx.SaveChanges(); } else { exist.CountryName = item.CLDR_displayname; exist.CountryName_Chs = item.official_name_cn; exist.CountryName_Cht = OpenCC.Convert(item.official_name_cn, OpenCCType.s2hk.ToString("g")); ctx.SaveChanges(); } _CurrentItem = exist.CountryName; } scope.Commit(); } catch { scope.Rollback(); } } #endregion } }
public HttpResponseMessage GetExcelFile(string filename, string id, string lang) { using (var ctx = new RT2020Entities()) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.BadRequest); // 如果 RT2020 call 會有 VWGContext,RT2020.Api call 就冇 VWGContext,咁就要用 AppSettings var directory = VWGContext.Current != null? VWGContext.Current.Config.GetDirectory("Reports") : ConfigHelper.ReportsBox; using (new Impersonate(directory, ConfigHelper.Impersonate_UserName, ConfigHelper.Impersonate_UserPassword)) { // 喺 ReportsBox directory path 內搵出隻 excel template file var excelFile = Directory.GetFiles(ConfigHelper.ReportsBox, "*.xlsx", SearchOption.AllDirectories) .Where(x => Path.GetFileNameWithoutExtension(x) == filename) .FirstOrDefault(); if (excelFile != null) { if (File.Exists(excelFile)) { // 讀入 dbo.Pipeline 記低嘅 user 要嘅 options var item = PipelineEx.Get(string.Format("PipelineId = '{0}'", id)); if (item != null) { #region 問 RT2020.Reports.dll 攞隻 Excel data source 送返俾 caller var raw = Helper.SA1340Helper.GenExcelDataSource(filename, item.Content, lang); if (raw != null) { var contentLength = raw.Length; //var mimeXls = "application/vnd.ms-excel"; var mimeXlsx = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; var statuscode = HttpStatusCode.OK; // 200 successful response = Request.CreateResponse(statuscode); response.Content = new StreamContent(new MemoryStream(raw)); response.Content.Headers.ContentType = new MediaTypeHeaderValue(mimeXlsx); response.Content.Headers.ContentLength = contentLength; ContentDispositionHeaderValue contentDisposition = null; if (ContentDispositionHeaderValue.TryParse("inline; filename=" + filename + ".xlsx", out contentDisposition)) { response.Content.Headers.ContentDisposition = contentDisposition; } } else { var message = String.Format("Unable to find resource. Resource \"{0}\" may not exist.", filename); HttpError err = new HttpError(message); response = Request.CreateErrorResponse(HttpStatusCode.NotFound, err); } #endregion } } } } return(response); } }
public HttpResponseMessage GetWptFile(string filename, string id, string lang) { using (var ctx = new RT2020Entities()) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.BadRequest); // 2021.10.14 paulus:WebPivotTable 係用 SheetJs 嚟 parse Excel contents,用 CSV 會安全啲 //var dsUrl = string.Format(_ExcelUrl, filename, id, lang); var dsUrl = string.Format(this.CsvUrl, filename, id, lang); var wptText = PivotHelper.GetLocalizedWptFile(filename, lang); if (wptText != "") { //if (File.Exists(wptFile)) //{ #region 讀入隻 wpt template 化做 response 送返俾 caller //var text = (File.ReadAllText(wptFile)).Replace("#dsUrl#", dsUrl); //! 替換 wpt 內的 data source Url var text = wptText.Replace("#dsUrl#", dsUrl); //! 替換 wpt 內的 data source Url var raw = Encoding.UTF8.GetBytes(text); if (raw != null) { var contentLength = raw.Length; var mimeJson = "application/json"; var statuscode = HttpStatusCode.OK; // 200 successful response = Request.CreateResponse(statuscode); response.Content = new StreamContent(new MemoryStream(raw)); response.Content.Headers.ContentType = new MediaTypeHeaderValue(mimeJson); response.Content.Headers.ContentLength = contentLength; ContentDispositionHeaderValue contentDisposition = null; if (ContentDispositionHeaderValue.TryParse("inline; filename=" + filename + ".wpt", out contentDisposition)) { response.Content.Headers.ContentDisposition = contentDisposition; } } else { var message = String.Format("Unable to find resource. Resource \"{0}\" may not exist.", filename); HttpError err = new HttpError(message); response = Request.CreateErrorResponse(HttpStatusCode.NotFound, err); } #endregion //} } return(response); } }
public HttpResponseMessage GetCsvFile(string filename, string id, string lang) { using (var ctx = new RT2020Entities()) { HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.BadRequest); // 讀入 dbo.Pipeline 記低嘅 user 要嘅 options var item = PipelineEx.Get(string.Format("PipelineId = '{0}'", id)); if (item != null) { #region 問 RT2020.Reports.dll 攞隻 Csv data source 送返俾 caller var raw = Helper.SA1340Helper.GenCsvDataSource(filename, item.Content, lang); if (raw != "") { var content = Encoding.UTF8.GetBytes(raw); //! 將原本嘅 string 轉為 UTF8 byte[] var contentLength = content.Length; var mimeCsv = "text/csv"; var statuscode = HttpStatusCode.OK; // 200 successful response = Request.CreateResponse(statuscode); response.Content = new ByteArrayContent(Encoding.UTF8.GetPreamble().Concat(content).ToArray()); //! 加上 UTF8 Preamble response.Content.Headers.ContentType = new MediaTypeHeaderValue(mimeCsv); response.Content.Headers.ContentLength = contentLength; ContentDispositionHeaderValue contentDisposition = null; if (ContentDispositionHeaderValue.TryParse("inline; filename=" + filename + ".csv", out contentDisposition)) { response.Content.Headers.ContentDisposition = contentDisposition; } } else { var message = String.Format("Unable to find resource. Resource \"{0}\" may not exist.", filename); HttpError err = new HttpError(message); response = Request.CreateErrorResponse(HttpStatusCode.NotFound, err); } #endregion } return(response); } }
public bool LoadJsondata(Guid id) { bool result = false; using (var ctx = new RT2020Entities()) { try { var item = ctx.WorkplaceZone.Find(id); if (item != null) { this.Id = id; this.JsonData = JsonConvert.DeserializeObject <JsonSchema>(item.MetadataXml); result = true; } } catch { } } return(result); }
public bool SaveJsondata() { bool result = false; if (this.Id != Guid.Empty) { using (var ctx = new RT2020Entities()) { try { var item = ctx.WorkplaceZone.Find(this.Id); if (item != null) { item.MetadataXml = JsonConvert.SerializeObject(this.JsonData); ctx.SaveChanges(); result = true; } } catch { } } } return(result); }
public static void GenMemberPhoneNumbers(Guid id) { using (var ctx = new RT2020Entities()) { ctx.Configuration.LazyLoadingEnabled = false; #region Generate random phone numbers for each Member var mbrAddresses = ctx.MemberAddress.Where(x => x.PhoneTag1Value != "" || x.PhoneTag2Value != "").ToList(); var counts = mbrAddresses.Count(); if (counts > 0) { var ccodes = new CountryCodes(); var codelist = ccodes.GetList(); //using (var scope = ctx.Database.BeginTransaction()) //{ try { //List<String> uniqueCountries = new List<string>(); int i = 0; foreach (var item in mbrAddresses) { #region 用 random gen phone number 取代原有嘅 phone number /** 假設條件(address 先決): * 0. 冇地址,當 others * 1. 非 ASCII 地址就係中國 * 2. 由 Address 找到所屬 Country,分為:中國﹑香港﹑其他 * 3. 如果 match 唔倒,當係 香港 */ String currentCountry = "", number1 = "", number2 = "", number3 = "", number4 = "", number5 = ""; currentCountry = (String.IsNullOrEmpty(item.Address)) ? "" : IsChinese(item.Address) ? "china" : StripCountry(item.Address); // 非 ASCII 地址就係中國 if (currentCountry != String.Empty) { #region 地址 currentCountry = currentCountry.ToLower().Replace(".", ""); var isoCountry = ccodes.FindByDisplayName(currentCountry); // 由 Address 找到所屬 Country var found = isoCountry != null ? true : false; if (!found) // match 唔倒,當係 香港 { currentCountry = "hong kong"; isoCountry = ccodes.FindByISO3166_Alpha3("HKG"); //! 應該加埋:FindByISO3166_Alpha2,可惜 :( } else { currentCountry = isoCountry.CLDR_displayname.ToLower(); } var dboCountry = ctx.Country.Where(x => x.CountryCode == isoCountry.FIPS).FirstOrDefault(); #region get random Phone Numbers into: number1 number2 switch (isoCountry.FIPS) { case "CN": number1 = (item.PhoneTag1Value.Trim() != String.Empty) ? GetRandomPhoneNumber_CN() : ""; number2 = (item.PhoneTag2Value.Trim() != String.Empty) ? GetRandomPhoneNumber_CN() : ""; number3 = (item.PhoneTag3Value.Trim() != String.Empty) ? GetRandomPhoneNumber_CN() : ""; number4 = (item.PhoneTag4Value.Trim() != String.Empty) ? GetRandomPhoneNumber_CN() : ""; number5 = (item.PhoneTag5Value.Trim() != String.Empty) ? GetRandomPhoneNumber_CN() : ""; break; case "HK": number1 = (item.PhoneTag1Value.Trim() != String.Empty) ? GetRandomPhoneNumber_HK() : ""; number2 = (item.PhoneTag2Value.Trim() != String.Empty) ? GetRandomPhoneNumber_HK() : ""; number3 = (item.PhoneTag3Value.Trim() != String.Empty) ? GetRandomPhoneNumber_HK() : ""; number4 = (item.PhoneTag4Value.Trim() != String.Empty) ? GetRandomPhoneNumber_HK() : ""; number5 = (item.PhoneTag5Value.Trim() != String.Empty) ? GetRandomPhoneNumber_HK() : ""; break; default: number1 = (item.PhoneTag1Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number2 = (item.PhoneTag2Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number3 = (item.PhoneTag3Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number4 = (item.PhoneTag4Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number5 = (item.PhoneTag5Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; break; } #endregion if (dboCountry != null) { item.CountryId = dboCountry.CountryId; } item.PhoneTag1Value = number1; item.PhoneTag2Value = number2; item.PhoneTag3Value = number3; item.PhoneTag4Value = number4; item.PhoneTag5Value = number5; /** Code used in debugging * var inList = uniqueCountries.Where(x => x == currentCountry).FirstOrDefault(); * if (inList == null) uniqueCountries.Add(currentCountry); */ #endregion } else { #region 冇地址 number1 = (item.PhoneTag1Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number2 = (item.PhoneTag2Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number3 = (item.PhoneTag3Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number4 = (item.PhoneTag4Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; number5 = (item.PhoneTag5Value.Trim() != String.Empty) ? GetRandomPhoneNumber_Others() : ""; item.PhoneTag1Value = number1; item.PhoneTag2Value = number2; item.PhoneTag3Value = number3; item.PhoneTag4Value = number4; item.PhoneTag5Value = number5; #endregion } //ctx.BulkSaveChanges(options => options.BatchSize = 100); #endregion i++; #region SaveChanges each batch: say 100 if ((i % 100) == 0) { ctx.BulkSaveChanges(); // uncomment for debugging //if (i >= 50) break; } #endregion } if (ctx.ChangeTracker.HasChanges()) { ctx.BulkSaveChanges(); } //scope.Commit(); } catch (Exception ex) { //scope.Rollback(); throw ex; } //} } #endregion } }
/// <summary> /// 將費時嘅工序放喺呢度做 /// </summary> /// <param name="s"></param> public void LTT_MemberPhoneNumber(string s) { // Write your time consuming task here using (var ctx = new RT2020Entities()) { ctx.Configuration.LazyLoadingEnabled = false; #region Generate random phone numbers for each Member var mbrAddresses = ctx.MemberAddress.Where(x => x.PhoneTag1Value != "" || x.PhoneTag2Value != ""); var counts = mbrAddresses.Count(); if (counts > 0) { OpenCC.Load(); var ccodes = new CountryCodes(); var codelist = ccodes.GetList(); using (var scope = ctx.Database.BeginTransaction()) { try { List <String> countries = new List <string>(); foreach (var item in mbrAddresses) { /* 假設條件(address 先決): * 1. 非 ASCII 地址就係中國 * 2. 由 Address 找到所屬 Country,分為:中國﹑香港﹑其他 * 3. 如果 match 唔倒,當係 香港 */ String country = "", areacode = "", number1 = "", number2 = ""; country = IsChinese(item.Address) ? "china" : StripCountry(item.Address); if (country != String.Empty) { country = country.ToLower().Replace(".", ""); var validCountry = ccodes.FindByDisplayName(country); var found = validCountry != null ? true : false; if (!found) { country = "hong kong"; } var inList = countries.Where(x => x == country).FirstOrDefault(); if (inList == null) { countries.Add(country); } } _CurrentItem = (--counts).ToString(); //ctx.SaveChanges(); } #region 顯示結果:countries 有哂啲 unique 嘅名,轉做 array 再轉為 json string 方便顯示個 result var str = String.Join("; ", countries.OrderBy(x => x).ToArray()); _AllCountries = Encoding.ASCII.GetString(Encoding.ASCII.GetBytes(str)).Replace("?", ""); #endregion //scope.Commit(); } catch { //scope.Rollback(); } } } #endregion } }