public List <SaveResult <SOWDTO> > ExecuteImport(SOWImportDTO importDTO, DateTime dateStamp) { using (var transaction = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromHours(1))) { var list = CreateListFromExcelBase64(importDTO); var result = GetSaveResults(list, dateStamp); Db.SaveChanges(); transaction.Complete(); return(result); } }
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); }