public TechnologyCreateHandler(GlobalSolusindoDb db, tblM_User user, TechnologyValidator technologyValidator, TechnologyFactory technologyFactory, TechnologyQuery technologyQuery, AccessControl accessControl) : base(db, user)
{
this.technologyValidator = technologyValidator;
this.technologyFactory = technologyFactory;
this.technologyQuery = technologyQuery;
this.technologyEntryDataProvider = new TechnologyEntryDataProvider(db, user, accessControl, technologyQuery);
}
public IHttpActionResult Get(int id)
{
ThrowIfUserHasNoRole(readRole);
using (TechnologyQuery technologyQuery = new TechnologyQuery(Db))
{
var data = technologyQuery.GetByPrimaryKey(id);
SaveLog("Technology", "Get", JsonConvert.SerializeObject(new { primaryKey = id }));
return(Ok(new SuccessResponse(data)));
}
}
public async Task <IActionResult> GetOne(int id)
{
await Db.Connection.OpenAsync();
var query = new TechnologyQuery(Db);
var result = await query.FindOneAsync(id);
if (result is null)
{
return(NotFound(new NotFoundError("The Technology was not found")));
}
return(new OkObjectResult(result));
}
public async Task <IActionResult> DeleteOne(int id)
{
await Db.Connection.OpenAsync();
var query = new TechnologyQuery(Db);
var result = await query.FindOneAsync(id);
if (result is null)
{
return(new NotFoundResult());
}
await result.DeleteAsync();
return(new OkResult());
}
public async Task <IActionResult> Put(int id, [FromBody] Technology body)
{
await Db.Connection.OpenAsync();
var query = new TechnologyQuery(Db);
var result = await query.FindOneAsync(id);
if (result is null)
{
return(NotFound(new NotFoundError("The Technology was not found")));
}
result.Name = body.Name;
result.Description = body.Description;
await result.UpdateAsync();
return(new OkObjectResult(result));
}
public async Task <IActionResult> GetLatest(int page_num, int rec_per_page)
{
await Db.Connection.OpenAsync();
var query = new TechnologyQuery(Db);
try
{
var result = await query.GetTechnologiesAsync(page_num, rec_per_page);
return(new OkObjectResult(result));
}
catch (Exception ex)
{
throw ex;
}
}
public async Task <IActionResult> SearchResults(string text)
{
try
{
await Db.Connection.OpenAsync();
var query = new TechnologyQuery(Db);
text = Regex.Replace(text, @"[^0-9a-zA-Z]+", " ");
var result = await query.getSearchResults(text + "*");
return(new OkObjectResult(result));
}
catch (Exception ex)
{
throw ex;
}
}
private SOWEntryFormData CreateFormData(SOWDTO sowDTO)
{
if (sowDTO == null)
{
return(new SOWEntryFormData());
}
SOWEntryFormData formData = new SOWEntryFormData();
var project = new ProjectQuery(this.Db).GetByPrimaryKey(sowDTO.Project_FK);
if (project != null)
{
formData.Projects.Add(project);
}
var bts = new BTSQuery(this.Db).GetByPrimaryKey(sowDTO.BTS_FK);
if (bts != null)
{
formData.BTSes.Add(bts);
}
foreach (var assign in sowDTO.SOWAssigns)
{
var user = new UserQuery(Db).GetByPrimaryKey(assign.User_FK);
if (user != null)
{
formData.Users.Add(user);
}
}
foreach (var track in sowDTO.SOWTracks)
{
sowDTO.Technology_FK = track.TipePekerjaan_FK;
var technology = new TechnologyQuery(this.Db).GetByPrimaryKey((int)track.TipePekerjaan_FK);
if (technology != null)
{
formData.Technologies.Add(technology);
}
}
return(formData);
}
public SearchResult <SOWTrackDTO> GetDataByFilter(SOWTrackSearchFilter filter)
{
if (string.IsNullOrEmpty(filter.SortName))
{
filter.SortName = "SOWTrack_PK";
}
SOWTrackQuery sowTrackQuery = new SOWTrackQuery(this.Db);
var sowAssign = new SOWAssignQuery(Db).GetByPrimaryKey(filter.TaskId);
int sowFk = 0;
if (sowAssign == null)
{
throw new Kairos.KairosException($"Invalid task ID '{filter.TaskId}'.");
}
if (sowAssign != null)
{
sowFk = sowAssign.SOW_FK;
}
var filteredRecords =
sowTrackQuery.GetQuery();
if (filter.TaskId > 0 && sowFk > 0)
{
filteredRecords = filteredRecords.Where(x => x.SOW_FK == sowFk);
}
if (!string.IsNullOrEmpty(filter.NetworkType))
{
var technology = new TechnologyQuery(Db).GetByTitle(filter.NetworkType);
if (technology != null)
{
filteredRecords.Where(x => x.TipePekerjaan_FK == technology.Technology_PK);
}
else
{
filteredRecords.Where(x => x.TipePekerjaan_FK == 0);
}
}
var displayedRecords = filteredRecords.
SortBy(filter.SortName, filter.SortDir)
.Skip(filter.Skip)
.Take(filter.PageSize)
.ToList();
var routes =
displayedRecords = displayedRecords.Select(sowTrack => new SOWTrackDTO
{
SOWTrack_PK = sowTrack.SOWTrack_PK,
SOW_FK = sowTrack.SOW_FK,
Route = sowTrack.Route,
CreatedBy = sowTrack.CreatedBy,
CreatedDate = sowTrack.CreatedDate,
UpdatedBy = sowTrack.UpdatedBy,
UpdatedDate = sowTrack.UpdatedDate,
Status_FK = sowTrack.Status_FK
}).ToList();
var searchResult = new SearchResult <SOWTrackDTO>(filter);
searchResult.Filter = filter;
searchResult.Count.TotalRecords = sowTrackQuery.GetTotalRecords();
searchResult.Count.TotalFiltered = filteredRecords.Count();
searchResult.Count.TotalDisplayed = displayedRecords.Count();
searchResult.Records = displayedRecords;
return(searchResult);
}
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 TechnologyEntryDataProvider(GlobalSolusindoDb db, tblM_User user, AccessControl accessControl, TechnologyQuery technologyQuery) : base(db, user)
{
this.accessControl = accessControl;
this.technologyQuery = technologyQuery;
}
public HttpResponseMessage Export(GlobalSolusindoDb Db, tblM_User user, string fileName, TaskListSearchFilter filter)
{
_fileName = fileName;
//CREATE WORKBOOK
var workbook = new XLWorkbook();
DataTable SOW = new DataTable("SOWUpload"); //DataTable Name = Worksheet Name
SOWExportDTO obj = new SOWExportDTO();
//Setup Column Names
foreach (var item in obj.GetType().GetProperties())
{
SOW.Columns.Add(item.Name);
}
workbook.AddWorksheet(SOW); // NO DATA = ADD Worksheet to WorkBook
//Worksheet Properties
var worksheet = workbook.Worksheet(1);
worksheet.Columns().Width = 15;
//Validation Table
using (var ProjectQuery2 = new ProjectQuery())
{
//SETUP TABLE PROJECT
DataTable validationTableProject = new DataTable();
validationTableProject.TableName = "Project";
//SETUP COLUMN
LOVDTO objProject = new LOVDTO();
foreach (var item in objProject.GetType().GetProperties())
{
validationTableProject.Columns.Add(item.Name);
}
var dataProject = ProjectQuery2.GetProjectByPM(user.User_PK);
DataRow drProject;
int startcell = 2, endcell = 2;
foreach (var item in dataProject)
{
drProject = validationTableProject.NewRow();
drProject["Id"] = item.Project_PK;
drProject["Name"] = item.Project_PK + "-" + item.OperatorTitle + "-" + item.VendorTitle + "-" + item.DeliveryAreaTitle;
validationTableProject.Rows.Add(drProject);
endcell++;
}
var worksheetProject = workbook.AddWorksheet(validationTableProject);
worksheet.Column(2).SetDataValidation().List(worksheetProject.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE VALIDATION BTS
using (var BTSQuery = new BTSQuery())
{
//SETUP TABLE BTS
DataTable validationTableBTS = new DataTable();
validationTableBTS.TableName = "BTS";
//SETUP COLUMN
LOVDTO objBTS = new LOVDTO();
foreach (var item in objBTS.GetType().GetProperties())
{
validationTableBTS.Columns.Add(item.Name);
}
var dataBTS = BTSQuery.GetQuery();
DataRow drBTS;
startcell = 2; endcell = 2;
foreach (var item in dataBTS)
{
drBTS = validationTableBTS.NewRow();
drBTS["Id"] = item.BTS_PK;
drBTS["Name"] = item.TowerID + "-" + item.Name;
validationTableBTS.Rows.Add(drBTS);
endcell++;
}
var worksheetBTS = workbook.AddWorksheet(validationTableBTS);
worksheet.Column(4).SetDataValidation().List(worksheetBTS.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE TECHNOLOGY
using (var TechnologyQuery = new TechnologyQuery())
{
//SETUP TABLE Technology
DataTable validationTableTechnology = new DataTable();
validationTableTechnology.TableName = "Technology";
//SETUP COLUMN
LOVDTO objTechnology = new LOVDTO();
foreach (var item in objTechnology.GetType().GetProperties())
{
validationTableTechnology.Columns.Add(item.Name);
}
var dataTechnology = TechnologyQuery.GetQuery();
DataRow drTechnology;
startcell = 2; endcell = 2;
foreach (var item in dataTechnology)
{
drTechnology = validationTableTechnology.NewRow();
drTechnology["Id"] = item.Technology_PK;
drTechnology["Name"] = item.Title;
validationTableTechnology.Rows.Add(drTechnology);
endcell++;
}
var worksheetTechnology = workbook.AddWorksheet(validationTableTechnology);
worksheet.Column(5).SetDataValidation().List(worksheetTechnology.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
using (var UserQuery = new UserQuery())
{
//SETUP TABLE Teamlead
DataTable validationTableUser = new DataTable();
validationTableUser.TableName = "Teamlead";
//SETUP COLUMN
LOVDTO objUser = new LOVDTO();
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUser.Columns.Add(item.Name);
}
var dataUser = UserQuery.GetByJabatanAndProject(1, user.User_PK);
DataRow drUser;
startcell = 2; endcell = 2;
foreach (var item in dataUser)
{
drUser = validationTableUser.NewRow();
drUser["Id"] = item.User_PK;
drUser["Name"] = item.Name;
validationTableUser.Rows.Add(drUser);
endcell++;
}
var worksheetUser = workbook.AddWorksheet(validationTableUser);
worksheet.Column(12).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE PLOQC
DataTable validationTableUserPLOQC = new DataTable();
validationTableUserPLOQC.TableName = "RNO";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserPLOQC.Columns.Add(item.Name);
}
var dataUserPLOQC = UserQuery.GetByJabatanAndProject(6, user.User_PK); // PLOQC JABATAN???
DataRow drUserPLOQC;
startcell = 2; endcell = 2;
foreach (var item in dataUserPLOQC)
{
drUserPLOQC = validationTableUserPLOQC.NewRow();
drUserPLOQC["Id"] = item.User_PK;
drUserPLOQC["Name"] = item.Name;
validationTableUserPLOQC.Rows.Add(drUserPLOQC);
endcell++;
}
var worksheetUserPLOQC = workbook.AddWorksheet(validationTableUserPLOQC);
worksheet.Column(13).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE RF
DataTable validationTableUserRF = new DataTable();
validationTableUserRF.TableName = "RF";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserRF.Columns.Add(item.Name);
}
var dataUserRF = UserQuery.GetByJabatanAndProject(5, user.User_PK);
DataRow drUserRF;
startcell = 2; endcell = 2;
foreach (var item in dataUserRF)
{
drUserRF = validationTableUserRF.NewRow();
drUserRF["Id"] = item.User_PK;
drUserRF["Name"] = item.Name;
validationTableUserRF.Rows.Add(drUserRF);
endcell++;
}
var worksheetUserRF = workbook.AddWorksheet(validationTableUserRF);
worksheet.Column(14).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE Rigger
DataTable validationTableUserRigger = new DataTable();
validationTableUserRigger.TableName = "Rigger";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserRigger.Columns.Add(item.Name);
}
var dataUserRigger = UserQuery.GetByJabatanAndProject(3, user.User_PK);
DataRow drUserRigger;
startcell = 2; endcell = 2;
foreach (var item in dataUserRigger)
{
drUserRigger = validationTableUserRigger.NewRow();
drUserRigger["Id"] = item.User_PK;
drUserRigger["Name"] = item.Name;
validationTableUserRigger.Rows.Add(drUserRigger);
endcell++;
}
var worksheetUserRigger = workbook.AddWorksheet(validationTableUserRigger);
worksheet.Column(15).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
//SETUP TABLE DT
DataTable validationTableUserDT = new DataTable();
validationTableUserDT.TableName = "DriveTester";
//SETUP COLUMN
foreach (var item in objUser.GetType().GetProperties())
{
validationTableUserDT.Columns.Add(item.Name);
}
var dataUserDT = UserQuery.GetByJabatanAndProject(2, user.User_PK);
DataRow drUserDT;
startcell = 2; endcell = 2;
foreach (var item in dataUserDT)
{
drUserDT = validationTableUserDT.NewRow();
drUserDT["Id"] = item.User_PK;
drUserDT["Name"] = item.Name;
validationTableUserDT.Rows.Add(drUserDT);
endcell++;
}
var worksheetUserDT = workbook.AddWorksheet(validationTableUserDT);
worksheet.Column(16).SetDataValidation().List(worksheetUser.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true);
}
}
//SETUP TABLE TEAMLEAD, RNO, RIGGER, TESTER
}
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 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);
}
