private BTSEntryFormData CreateFormData(BTSDTO btsDTO)
{
if (btsDTO == null)
{
return(new BTSEntryFormData());
}
BTSEntryFormData formData = new BTSEntryFormData();
formData.Technologies = new TechnologyQuery(Db).GetQuery().ToList();
var _operator = new OperatorQuery(this.Db).GetByPrimaryKey(btsDTO.Operator_FK);
if (_operator != null)
{
formData.Operators.Add(_operator);
}
var btsStatusFK = btsDTO.StatusBTS_FK != null ? btsDTO.StatusBTS_FK.Value : 0;
//var btsStatus = new BTSStatusQuery(this.Db).GetByPrimaryKey(btsStatusFK);
// var btsStatus = new BTSStatusQuery(this.Db).GetByPrimaryKey(btsStatusFK);
formData.BTSStatuses = new BTSStatusQuery(this.Db).GetQuery().ToList();
var area = new AreaQuery(this.Db).GetByPrimaryKey(btsDTO.Area_FK);
if (area != null)
{
formData.Areas.Add(area);
}
return(formData);
}
/// <summary>
/// 根据地区编号获取地区详细名称
/// </summary>
/// <param name="areaId">地区编号</param>
/// <returns>给定地区编号对应的地区详细名称</returns>
private string GetAreaNameByAreaID(long areaId = -1)
{
string AreaName = "";
var svc = Service;
var pathQuery = new AreaPathQuery
{
IDs = new long[] { areaId }
};
var paths = svc.SelectOrEmpty(pathQuery);
if (paths != null && paths.Any())
{
var pathIdset = paths.Select(c => c.AreaID ?? -1);
long[] pathIds = new long[pathIdset.Count()];
for (int i = 0; i < pathIds.Length; i++)
{
pathIds[i] = pathIdset.ToList()[i];
}
var _areaQuery = new AreaQuery
{
IDs = pathIds
};
var areaList = svc.SelectOrEmpty(_areaQuery).OrderBy(c => c.Depth);
if (areaList != null && areaList.Any())
{
foreach (var item in areaList)
{
AreaName += item.Name;
}
}
}
return(AreaName);
}
public async Task <List <Area> > GetAreas(AreaQuery query)
{
var content = await RepositoryParser.ParseRepository <List <Area> >(filePaths.Areas);
var queryedItems = FilterAreas(content, query);
return(queryedItems);
}
public IHttpActionResult Get(int id)
{
ThrowIfUserHasNoRole(readRole);
using (AreaQuery areaQuery = new AreaQuery(Db))
{
var data = areaQuery.GetByPrimaryKey(id);
SaveLog("Area", "Get", JsonConvert.SerializeObject(new { primaryKey = id }));
return(Ok(new SuccessResponse(data)));
}
}
public IHttpActionResult Search([FromUri] AreaSearchFilter filter)
{
ThrowIfUserHasNoRole(readRole);
if (filter == null)
{
throw new KairosException("Missing search filter parameter");
}
using (var areaQuery = new AreaQuery(Db))
{
var data = areaQuery.Search(filter);
return(Ok(new SuccessResponse(data)));
}
}
public async Task <IActionResult> GetAreas([FromQuery] AreaQuery query)
{
try
{
return(Ok(await areasService.GetAreas(query)));
}
catch (FileNotFoundException ex)
{
return(BadRequest(ex));
}
catch (Exception ex)
{
return(BadRequest(ex));
}
}
public static async void Search(CustomSearchControl SearchControl)
{
var client = Helper.getServiceClient();
AreaQuery query = new AreaQuery();
if (SearchControl.OptionOne.IsChecked == true)
{
query = new Service.AreaQuery()
{
AreaCode = SearchControl.SearchTextBox.Text
};
}
else if (SearchControl.OptionTwo.IsChecked == true)
{
query = new Service.AreaQuery()
{
AreaName = SearchControl.SearchTextBox.Text
};
}
else if (SearchControl.OptionThree.IsChecked == true)
{
query = new Service.AreaQuery()
{
RegionCode = SearchControl.SearchTextBox.Text
};
}
//The search control needs to know the method it has to use inorder to search. So we pass a method
SearchControl.Search = Search;
int pagesize = SearchControl.PageSize;
int pagePosition = SearchControl.PagePosition;
var response = await client.QueryAreaAsync(query, pagesize, pagePosition);
//No response; exit
if (response == null)
{
MessageBox.Show("Service isn't responding, please try again later", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
return;
}
SearchControl.ResultCount = response.TotalResultCount;
//Fill the datagrid with the results
SearchControl.ResultsGrid.ItemsSource = new ObservableCollection <FIN_Area>(response.Result.ToList <FIN_Area>());;
}
private async Task <IEnumerable <Hotel> > GetHotelsAsync(AreaQuery searchQuery)//ホテルデータランダム生成
{
var hotels = new List <Hotel>();
/*
* // Filling the hotels results manually just for demo purposes
* for (int i = 1; i <= 5; i++)
* {
* var random = new Random(i);
* Hotel hotel = new Hotel()
* {
* Name = $"{searchQuery.住所} Hotel {i}",
* Location = searchQuery.住所,
* Rating = random.Next(1, 5),
* NumberOfReviews = random.Next(0, 5000),
* PriceStarting = random.Next(80, 450),
* Image = $"https://placeholdit.imgix.net/~text?txtsize=35&txt=Hotel+{i}&w=500&h=260"
* };
*
* hotels.Add(hotel);
* }
*
* hotels.Sort((h1, h2) => h1.PriceStarting.CompareTo(h2.PriceStarting));*/
string data = new QnAMaker.Program2(searchQuery.住所);
var random = new Random();
Hotel hotel = new Hotel()
{
Name = $"{searchQuery.住所}",
Location = data,
Rating = random.Next(1, 5),
NumberOfReviews = random.Next(0, 5000),
PriceStarting = random.Next(80, 450)
};
hotels.Add(hotel);
return(hotels);
}
private List <Area> FilterAreas(List <Area> areas, AreaQuery query)
{
if (query == null)
{
return(areas);
}
if (query.Id != null)
{
areas = areas.Where(x => query.Id.IndexOf(x.Id) >= 0).ToList();
}
if (query.Name != null)
{
areas = areas.Where(x =>
query.Name.Any(y =>
x.Name.ToLower()
.Contains(y.ToLower()
)
)
).ToList();
}
if (query.GameId != null)
{
areas = areas.Where(x =>
query.GameId.Any(id =>
x.AppearsInGame.Any(
y => y.Contains(id.ToString())
)
)
).ToList();
}
return(areas);
}
public List <SOWDTO> CreateListFromExcelBase64(SOWImportDTO importDTO)
{
BTSQuery btsquery = new BTSQuery();
UserQuery userQuery = new UserQuery();
var base64 = importDTO.File;
base64 = base64.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", "");
var wb = ExcelConverter.FromBase64(base64);
var sheet = wb.Worksheet("SOWUpload");
var nonEmptyRowCount = sheet.RowsUsed().Count() + 1;
var areaQuery = new AreaQuery(Db);
var operatorQuery = new OperatorQuery(Db);
var technologyQuery = new TechnologyQuery(Db);
var SOWQuery = new SOWQuery(Db);
List <SOWDTO> SOWList = new List <SOWDTO>();
List <SOWAssignDTO> sowAssign = new List <SOWAssignDTO>();
//first index is 1, and the first one is header title
for (int i = 2; i < nonEmptyRowCount; i++)
{
var row = sheet.Row(i);
var pmouniq = row.Cell(1).Value.ToString();
var project = row.Cell(2).Value.ToString();
var duid = row.Cell(3).Value.ToString();
var bts = row.Cell(4).Value.ToString();
var tech = row.Cell(5).Value.ToString();
var sowname = row.Cell(6).Value.ToString();
var codate = row.Cell(7).Value.ToString();
var lvdate = row.Cell(8).Value.ToString();
var accdate = row.Cell(9).Value.ToString();
var tp = row.Cell(10).Value.ToString();
var rtp = row.Cell(11).Value.ToString();
var teamlead = row.Cell(12).Value.ToString();
var ploqc = row.Cell(13).Value.ToString();
var rf = row.Cell(14).Value.ToString();
var rigger = row.Cell(15).Value.ToString();
var dt = row.Cell(16).Value.ToString();
if (teamlead != "")
{
sowAssign.Add(new SOWAssignDTO()
{
User_FK = userQuery.GetByUsername(teamlead).User_PK
});
}
if (ploqc != "")
{
sowAssign.Add(new SOWAssignDTO()
{
User_FK = userQuery.GetByUsername(ploqc).User_PK
});
}
if (rf != "")
{
sowAssign.Add(new SOWAssignDTO()
{
User_FK = userQuery.GetByUsername(rf).User_PK
});
}
if (rigger != "")
{
sowAssign.Add(new SOWAssignDTO()
{
User_FK = userQuery.GetByUsername(rigger).User_PK
});
}
if (dt != "")
{
sowAssign.Add(new SOWAssignDTO()
{
User_FK = userQuery.GetByUsername(dt).User_PK
});
}
SOWList.Add(new SOWDTO()
{
SOWName = sowname,
PMOUniq = pmouniq,
BTS_FK = bts == "" ? 0 : Convert.ToInt16(project.Split('-')[0]),
Project_FK = project == "" ? 0 : Convert.ToInt16(project.Split('-')[0]),
Technology_FK = tech == "" ? 0 : technologyQuery.GetByTitle(tech).Technology_PK,
TglMulai = DateTime.Parse(codate),
DUID = duid,
LVDate = DateTime.Parse(lvdate),
AcceptedDate = DateTime.Parse(accdate),
StatusSOW_FK = 1,
SOWAssigns = sowAssign
});
}
return(SOWList);
}
private void Start() => Instance = this;
public async Task <QueryResult <AreaQueryRow> > QueryArea(
AreaQueryFilter filter = null,
AreaQuerySort sort = null,
AreaQueryProjection projection = null,
AreaQueryPaging paging = null,
AreaQueryOptions options = null)
{
var conn = context.Database.GetDbConnection();
var openConn = conn.OpenAsync();
var query = AreaQuery.CreateDynamicSql();
#region General
if (filter != null)
{
query = query.SqlFilter(filter);
}
if (projection != null)
{
query = query.SqlJoin(projection);
}
DynamicSql countQuery = null; int?totalCount = null; Task <int> countTask = null;
if (options != null && options.count_total)
{
countQuery = query.SqlCount("*");
}
if (projection != null)
{
query = query.SqlProjectFields(projection);
}
#endregion
await openConn;
if (options != null && !options.single_only)
{
#region List query
if (sort != null)
{
query = query.SqlSort(sort);
}
if (paging != null && (!options.load_all || !AreaQueryOptions.IsLoadAllAllowed))
{
query = query.SqlSelectPage(paging.page, paging.limit);
}
#endregion
#region Count query
if (options.count_total)
{
countTask = conn.ExecuteScalarAsync <int>(
sql: countQuery.PreparedForm,
param: countQuery.DynamicParameters);
}
#endregion
}
var queryResult = await conn.QueryAsync(
sql : query.PreparedForm,
types : query.GetTypesArr(),
map : (objs) => ProcessMultiResults(query, objs),
splitOn : string.Join(',', query.GetSplitOns()),
param : query.DynamicParameters);
if (options != null && options.single_only)
{
var single = queryResult.FirstOrDefault();
return(new QueryResult <AreaQueryRow>
{
SingleResult = single
});
}
if (options != null && options.count_total)
{
totalCount = await countTask;
}
return(new QueryResult <AreaQueryRow>
{
Results = queryResult,
TotalCount = totalCount
});
}
public PagedResult <AreaView> SearchPage([FromBody] AreaQuery queryInfo)
{
return(_Service.FilterPage(queryInfo));
}
public AreaListDataProvider(GlobalSolusindoDb db, tblM_User user, AreaQuery areaQuery) : base(db, user)
{
this.areaQuery = areaQuery;
}
public List <BTSDTO> CreateListFromExcelBase64(BTSImportDTO importDTO)
{
var base64 = importDTO.File;
base64 = base64.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", "");
var wb = ExcelConverter.FromBase64(base64);
var sheet = wb.Worksheet("BTSUpload");
var nonEmptyRowCount = sheet.RowsUsed().Count() + 1;
var areaQuery = new AreaQuery(Db);
var operatorQuery = new OperatorQuery(Db);
var technologyQuery = new TechnologyQuery(Db);
var btsQuery = new BTSQuery(Db);
List <BTSDTO> btsList = new List <BTSDTO>();
//first index is 1, and the first one is header title
for (int i = 2; i < nonEmptyRowCount; i++)
{
var row = sheet.Row(i);
var towerId = row.Cell(1).Value.ToString();
var btsName = row.Cell(2).Value.ToString();
//get by itsname first;
var btsDtoOnDb = btsQuery.GetByTowerID(towerId);
var btsPk = 0;
if (btsDtoOnDb == null)
{
btsDtoOnDb = btsQuery.GetByName(btsName);
}
if (btsDtoOnDb != null)
{
btsPk = btsDtoOnDb.BTS_PK;
}
var cellId = row.Cell(3).Value.ToString();
var operatorName = row.Cell(4).Value.ToString();
var operatorId = operatorQuery.GetByTitle(operatorName).Operator_PK;
var longitude = Convert.ToString(row.Cell(5).Value);
var latitude = Convert.ToString(row.Cell(6).Value);
var areaName = row.Cell(7).Value.ToString();
var areaId = areaQuery.GetByTitle(areaName).Area_PK;
var address = row.Cell(8).Value.ToString();
var status = row.Cell(9).Value.ToString();
List <BTSTechnologyDTO> techno = new List <BTSTechnologyDTO>();
var technology = row.Cell(10).Value.ToString().Split(';').ToList();
foreach (var item in technology)
{
BTSTechnologyDTO itemtech = new BTSTechnologyDTO();
try
{
itemtech.Technology_FK = technologyQuery.GetByTitle(item).Technology_PK;
}
catch (Exception)
{
itemtech.Technology_FK = 0;
}
techno.Add(itemtech);
}
btsList.Add(new BTSDTO()
{
BTS_PK = btsPk,
Name = btsName,
CellID = cellId,
Latitude = latitude,
Longitude = longitude,
Alamat = address,
Area_FK = areaId,
AreaTitle = areaName,
OperatorTitle = operatorName,
Operator_FK = operatorId,
Status_FK = 1,
TowerID = towerId,
StatusBTS_FK = 1,
BTSTechnologies = techno
});
}
return(btsList);
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, string fileName, BTSStatusSearchFilter filter)
{
_fileName = fileName;
//CREATE WORKBOOK
var workbook = new XLWorkbook();
DataTable bts = new DataTable("BTSUpload"); //DataTable Name = Worksheet Name
BTSExportDTO obj = new BTSExportDTO();
//Setup Column Names
foreach (var item in obj.GetType().GetProperties())
{
bts.Columns.Add(item.Name);
}
workbook.AddWorksheet(bts); // NO DATA = ADD Worksheet to WorkBook
//Worksheet Properties
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
//BTS Status
using (var BTSStatusSearch = new BTSStatusSearch(Db))
{
// Validation BTS Status
DataTable validationTable = new DataTable();
validationTable.Columns.Add("StatusBTSFK");
validationTable.Columns.Add("StatusBTS");
validationTable.TableName = "StatusBTS";
var data = BTSStatusSearch.GetDataByFilter(filter);
var dataExport = data.Records.Select(c => new tblM_BTSStatus
{
BTSStatus_PK = c.BTSStatus_PK,
Title = c.Title
}).ToList();
DataRow dr;
int startcell = 2, endcell = 2;
foreach (var item in dataExport)
{
dr = validationTable.NewRow();
dr["StatusBTSFK"] = item.BTSStatus_PK;
dr["StatusBTS"] = item.Title;
validationTable.Rows.Add(dr);
endcell++;
}
var worksheet2 = workbook.AddWorksheet(validationTable);
worksheet.Column(9).SetDataValidation().List(worksheet2.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Validation Operator
DataTable validationTable2 = new DataTable();
validationTable2.Columns.Add("Operator_FK");
validationTable2.Columns.Add("Operator");
validationTable2.TableName = "Operator";
//Operator
OperatorSearchFilter a = new OperatorSearchFilter();
using (var OperatorSearch = new OperatorSearch(Db))
{
var data2 = OperatorSearch.GetDataByFilter(a);
var dataExport2 = data2.Records.Select(c => new tblM_Operator
{
Operator_PK = c.Operator_PK,
Title = c.Title
}).ToList();
DataRow dr2;
startcell = 2; endcell = 2;
foreach (var item in dataExport2)
{
dr2 = validationTable2.NewRow();
dr2["Operator_FK"] = item.Operator_PK;
dr2["Operator"] = item.Title;
validationTable2.Rows.Add(dr2);
endcell++;
}
var worksheet3 = workbook.AddWorksheet(validationTable2);
worksheet.Column(4).SetDataValidation().List(worksheet3.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//Validation Area
DataTable validationTable3 = new DataTable();
validationTable3.Columns.Add("AreaFK");
validationTable3.Columns.Add("Area");
validationTable3.TableName = "Area";
//Operator
AreaSearchFilter b = new AreaSearchFilter();
using (var AreaSearch = new AreaQuery(Db))
{
var data3 = AreaSearch.Search(b);
var dataExport3 = data3.Records.Select(e => new tblM_Area
{
Area_PK = e.Area_PK,
Title = e.Title
}).ToList();
DataRow dr3;
startcell = 2; endcell = 2;
foreach (var item in dataExport3)
{
dr3 = validationTable3.NewRow();
dr3["AreaFK"] = item.Area_PK;
dr3["Area"] = item.Title;
validationTable3.Rows.Add(dr3);
endcell++;
}
var worksheet4 = workbook.AddWorksheet(validationTable3);
worksheet.Column(7).SetDataValidation().List(worksheet4.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
}
//worksheet.Column(5).SetDataValidation().List(worksheet2.Range("B2:B4"), true);
MemoryStream memoryStream = GetStream(workbook);
var response = new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ByteArrayContent(memoryStream.ToArray())
};
response.Content.Headers.ContentType = new MediaTypeHeaderValue
("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition =
new ContentDispositionHeaderValue("attachment")
{
FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
};
return(response);
}
public AreaEntryDataProvider(GlobalSolusindoDb db, tblM_User user, AccessControl accessControl, AreaQuery areaQuery) : base(db, user)
{
this.accessControl = accessControl;
this.areaQuery = areaQuery;
}