public List <UserDTO> CreateListFromExcelBase64(UserImportDTO 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("UserUpload");
var nonEmptyRowCount = sheet.RowsUsed().Count() + 1;
var userQuery = new UserQuery(Db);
List <UserDTO> userList = new List <UserDTO>();
var jabatanQuery = new KategoriJabatanQuery(Db);
MaritalStatusQuery maritalStatusQuery = new MaritalStatusQuery();
GenderQuery genderQuery = new GenderQuery();
CategoryContractQuery categoryContractQuery = new CategoryContractQuery();
ReligionQuery religionQuery = new ReligionQuery();
//first index is 1, and the first one is header title
for (int i = 2; i < nonEmptyRowCount; i++)
{
var row = sheet.Row(i);
//Excel value
var xNomor = row.Cell(1).Value;
var xUserName = row.Cell(2).Value;
var XJabatanName = row.Cell(3).Value;
var xFullName = row.Cell(4).Value;
var xTglLahir = row.Cell(5).Value;
var xKtp = row.Cell(6).Value;
var xReligion = row.Cell(7).Value;
var xCategoryContract = row.Cell(8).Value;
var xProject = row.Cell(9).Value;
var xGender = row.Cell(10).Value;
var xMaritalStatus = row.Cell(11).Value;
var xNpwp = row.Cell(12).Value;
var xBpjs = row.Cell(13).Value;
var xJoinDate = row.Cell(14).Value;
var xContactNumber = row.Cell(15).Value;
var xG1EmailId = row.Cell(16).Value;
var xPersonalEmail = row.Cell(17).Value;
var xAddress = row.Cell(18).Value;
var xBankName = row.Cell(19).Value;
var xAccountNumber = row.Cell(20).Value;
var xSalary = row.Cell(21).Value;
var xRemark = row.Cell(22).Value;
var xStatus = row.Cell(23).Value;
var defaultEmptyDate = new DateTime(1990, 01, 01);
var dateContainer = new DateTime(1990, 01, 01);
double doubleContainer = 0;
int intContainer = 0;
var nomor = row.Cell(1).Value;
var userName = xUserName == null ? "" : xUserName.ToString();
var jabatanName = XJabatanName == null ? "" : XJabatanName.ToString();
var fullName = xFullName == null ? "" : xFullName.ToString();
var tglLahir = xTglLahir == null ? defaultEmptyDate :
DateTime.TryParse(xTglLahir.ToString(), out dateContainer) == true ? dateContainer : defaultEmptyDate;
var ktp = xKtp == null ? "" : xKtp.ToString();
var religion = xReligion == null ? "" : xReligion.ToString();
var categoryContract = xCategoryContract == null ? "" : xCategoryContract.ToString();
var project = xProject == null ? "" : xProject.ToString();
var gender = xGender == null ? "" : xGender.ToString();
var maritalStatus = xMaritalStatus == null ? "" : xMaritalStatus.ToString();
var npwp = xNpwp == null ? "" : xNpwp.ToString();
var bpjs = xBpjs == null ? "" : xBpjs.ToString();
var joinDate = xJoinDate == null ? defaultEmptyDate :
DateTime.TryParse(xJoinDate.ToString(), out dateContainer) == true ? dateContainer : defaultEmptyDate;
var contactNumber = xContactNumber == null ? "" : xContactNumber.ToString();
var g1EmailId = xG1EmailId == null ? "" : xG1EmailId.ToString();
var personalEmail = xPersonalEmail == null ? "" : xPersonalEmail.ToString();
var address = xAddress == null ? "" : xAddress.ToString();
var bankName = xBankName == null ? "" : xBankName.ToString();
var accountNumber = xAccountNumber == null ? "" : xAccountNumber.ToString();
var salary = xSalary == null ? 0 :
double.TryParse(xSalary.ToString(), out doubleContainer) == true ? doubleContainer : 0;
var remark = xRemark == null ? "" : xRemark.ToString();
var status = xStatus == null ? 1 :
int.TryParse(xStatus.ToString(), out intContainer) == true ? intContainer : 1;
var userPk = 0;
var userDetailFk = 0;
//get by itsname first;
var userDtoOnDb = userQuery.GetByUsername(userName);
var userCode = userName;
if (userDtoOnDb != null)
{
userPk = userDtoOnDb.User_PK;
userDetailFk = userDtoOnDb.UserDetail_FK;
}
var jabatanFk = 0;
var jabatan = jabatanQuery.GetByTitle(jabatanName);
if (jabatan != null)
{
jabatanFk = jabatan.KategoriJabatan_PK;
}
var newUser = new UserDTO()
{
User_PK = userPk,
UserDetail_FK = userDetailFk,
UserDetail_PK = userDetailFk,
UserCode = userCode,
Name = fullName,
TglLahir = tglLahir,
NoKTP = ktp,
NoHP = contactNumber,
Email = g1EmailId,
PersonalEmail = personalEmail,
Address = address,
Description = remark,
Username = userName,
KategoriJabatan_FK = jabatanFk,
KategoriJabatanTitle = jabatanName,
//Password = user.Password,
Status_FK = status,
Password = "******",
AccountNumber = accountNumber,
BankName = bankName,
BPJS = bpjs,
////WAIT
CategoryContract = categoryContract == "" ? 0 : categoryContractQuery.GetByName(categoryContract).CategoryContract_PK,
Gender = gender == "" ? 0 : genderQuery.GetByName(gender).Gender_PK,
JoinDate = joinDate,
MaritalStatus = maritalStatus == "" ? 0 : maritalStatusQuery.GetByName(maritalStatus).MaritalStatus_PK,
NPWP = npwp,
Project = project == "" ? 0 : Convert.ToInt16(project.Split('-')[0]),
Religion = religion == "" ? 0 : religionQuery.GetByName(religion).Religion_PK,
Salary = salary
};
userList.Add(newUser);
}
return(userList);
}
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);
}