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