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