public static async void Search(CustomSearchControl SearchControl) { var client = Helper.getServiceClient(); ReligionQuery query = new ReligionQuery(); //by default we have an empty query if (SearchControl.OptionOne.IsChecked == true) { query = new ReligionQuery() { Code = SearchControl.SearchTextBox.Text }; } else if (SearchControl.OptionTwo.IsChecked == true) { query = new ReligionQuery() { Name = SearchControl.SearchTextBox.Text }; } int pagesize = SearchControl.PageSize; int pagePosition = SearchControl.PagePosition; var response = await client.QueryReligionAsync(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 <HR_RELIGION>(response.Result.ToList <HR_RELIGION>());; }
public HttpResponseMessage Export(GlobalSolusindoDb Db, string fileName, UserSearchFilter filter) { _fileName = fileName; _workbook = new XSSFWorkbook(); //Creating New Excel object var workbook = new XLWorkbook(); using (var userSearch = new UserSearch(Db)) { var data = userSearch.GetDataByFilter(filter); var dataExport = data.Records.Select(c => new UserExportDTO { Nomor = c.User_PK, JoinDate = c.JoinDate, UserName = c.Username, KategoriJabatanTitle = c.KategoriJabatanTitle, Name = c.Name, TglLahir = c.TglLahir, NoKTP = c.NoKTP, ReligionName = c.Religion == null || c.Religion == 0 ? "" : religionQuery.GetByPrimaryKey((int)c.Religion).Name, //? CategoryContract = c.CategoryContract == null || c.CategoryContract == 0 ? "" : categoryContractQuery.GetByPrimaryKey((int)c.CategoryContract).Name, Project = c.Project == null || c.Project == 0 ? "" : projectQuery.GetByPrimaryKey((int)c.Project).Project_PK + "-" + projectQuery.GetByPrimaryKey((int)c.Project).OperatorTitle + "-" + projectQuery.GetByPrimaryKey((int)c.Project).VendorTitle + "-" + projectQuery.GetByPrimaryKey((int)c.Project).DeliveryAreaTitle, //? Gender = c.Gender == null || c.Gender == 0 ? "" : genderQuery.GetByPrimaryKey((int)c.Gender).Name, MartialStatus = c.MaritalStatus == null || c.MaritalStatus == 0 ? "" : maritalStatusQuery.GetByPrimaryKey((int)c.MaritalStatus).Name, NPWP = c.NPWP, //? BPJS = c.BPJS, //? ContactNumber = c.NoHP, G1EmailID = c.Email, PersonalEmail = c.PersonalEmail, Address = c.Address, NamaBank = c.BankName, //? NoRekening = c.AccountNumber, //? Salary = c.Salary, //? Remark = c.Description, //? Status = null //? }).ToList(); projectQuery.Dispose(); DataTable user = new DataTable("UserUpload"); UserExportDTO obj = new UserExportDTO(); foreach (var item in obj.GetType().GetProperties()) { user.Columns.Add(item.Name); } DataRow dr; foreach (var item in dataExport) { dr = user.NewRow(); dr["Nomor"] = item.Nomor; dr["UserName"] = item.UserName; dr["KategoriJabatanTitle"] = item.KategoriJabatanTitle; dr["Name"] = item.Name; dr["TglLahir"] = item.TglLahir; dr["NoKTP"] = item.NoKTP; dr["ReligionName"] = item.ReligionName; dr["CategoryContract"] = item.CategoryContract; dr["Project"] = item.Project; dr["Gender"] = item.Gender; dr["MartialStatus"] = item.MartialStatus; dr["NPWP"] = item.NPWP; dr["BPJS"] = item.BPJS; dr["JoinDate"] = item.JoinDate; dr["ContactNumber"] = item.ContactNumber; dr["G1EmailID"] = item.G1EmailID; dr["PersonalEmail"] = item.PersonalEmail; dr["Address"] = item.Address; dr["NamaBank"] = item.NamaBank; dr["NoRekening"] = item.NoRekening; dr["Salary"] = item.Salary; dr["Remark"] = item.Remark; dr["Status"] = item.Status; user.Rows.Add(dr); } workbook.AddWorksheet(user); var worksheet = workbook.Worksheet(1); worksheet.Columns().Width = 15; using (var jabatanQry = new KategoriJabatanQuery()) { //SETUP TABLE DataTable validationTable = new DataTable(); validationTable.TableName = "Kategori Jabatan"; //SETUP COLUMN LOVDTO objkatja = new LOVDTO(); foreach (var item in objkatja.GetType().GetProperties()) { validationTable.Columns.Add(item.Name); } //DATA ROW var dataval = jabatanQry.GetQuery().Select(c => new LOVDTO { Id = c.KategoriJabatan_PK, Name = c.Title }).ToList(); DataRow dr2; int startcell = 2, endcell = 2; foreach (var item in dataval) { dr2 = validationTable.NewRow(); dr2["Id"] = item.Id; dr2["Name"] = item.Name; validationTable.Rows.Add(dr2); endcell++; } var worksheet2 = workbook.AddWorksheet(validationTable); worksheet.Column(3).SetDataValidation().List(worksheet2.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true); //GENDER using (var genderQuery = new GenderQuery()) { //SETUP TABLE DataTable validationTablegender = new DataTable(); validationTablegender.TableName = "Gender"; //SETUP COLUMN GenderDTO objgender = new GenderDTO(); foreach (var item in objkatja.GetType().GetProperties()) { validationTablegender.Columns.Add(item.Name); } var datagender = genderQuery.GetQuery().Select(c => new GenderDTO { Gender_PK = c.Gender_PK, Name = c.Name }).ToList(); DataRow drgender; startcell = 2; endcell = 2; foreach (var item in datagender) { drgender = validationTablegender.NewRow(); drgender["Id"] = item.Gender_PK; drgender["Name"] = item.Name; validationTablegender.Rows.Add(drgender); endcell++; } var worksheetgender = workbook.AddWorksheet(validationTablegender); worksheet.Column(10).SetDataValidation().List(worksheetgender.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true); //RELIGION using (var ReligionQuery = new ReligionQuery()) { //SETUP TABLE DataTable validationTableReligion = new DataTable(); validationTableReligion.TableName = "Religion"; //SETUP COLUMN ReligionDTO objReligion = new ReligionDTO(); foreach (var item in objkatja.GetType().GetProperties()) { validationTableReligion.Columns.Add(item.Name); } var dataReligion = ReligionQuery.GetQuery().Select(c => new ReligionDTO { Religion_PK = c.Religion_PK, Name = c.Name }).ToList(); DataRow drReligion; startcell = 2; endcell = 2; foreach (var item in dataReligion) { drReligion = validationTableReligion.NewRow(); drReligion["Id"] = item.Religion_PK; drReligion["Name"] = item.Name; validationTableReligion.Rows.Add(drReligion); endcell++; } var worksheetReligion = workbook.AddWorksheet(validationTableReligion); worksheet.Column(7).SetDataValidation().List(worksheetReligion.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true); //MaritalStatus using (var MaritalStatusQuery = new MaritalStatusQuery()) { //SETUP TABLE DataTable validationTableMaritalStatus = new DataTable(); validationTableMaritalStatus.TableName = "MaritalStatus"; //SETUP COLUMN MaritalStatusDTO objMaritalStatus = new MaritalStatusDTO(); foreach (var item in objkatja.GetType().GetProperties()) { validationTableMaritalStatus.Columns.Add(item.Name); } var dataMaritalStatus = MaritalStatusQuery.GetQuery().Select(c => new MaritalStatusDTO { MaritalStatus_PK = c.MaritalStatus_PK, Name = c.Name }).ToList(); DataRow drMaritalStatus; startcell = 2; endcell = 2; foreach (var item in dataMaritalStatus) { drMaritalStatus = validationTableMaritalStatus.NewRow(); drMaritalStatus["Id"] = item.MaritalStatus_PK; drMaritalStatus["Name"] = item.Name; validationTableMaritalStatus.Rows.Add(drMaritalStatus); endcell++; } var worksheetMaritalStatus = workbook.AddWorksheet(validationTableMaritalStatus); worksheet.Column(11).SetDataValidation().List(worksheetMaritalStatus.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true); //CategoryContract using (var CategoryContractQuery = new CategoryContractQuery()) { //SETUP TABLE DataTable validationTableCategoryContract = new DataTable(); validationTableCategoryContract.TableName = "CategoryContract"; //SETUP COLUMN CategoryContractDTO objCategoryContract = new CategoryContractDTO(); foreach (var item in objkatja.GetType().GetProperties()) { validationTableCategoryContract.Columns.Add(item.Name); } var dataCategoryContract = CategoryContractQuery.GetQuery().Select(c => new CategoryContractDTO { CategoryContract_PK = c.CategoryContract_PK, Name = c.Name }).ToList(); DataRow drCategoryContract; startcell = 2; endcell = 2; foreach (var item in dataCategoryContract) { drCategoryContract = validationTableCategoryContract.NewRow(); drCategoryContract["Id"] = item.CategoryContract_PK; drCategoryContract["Name"] = item.Name; validationTableCategoryContract.Rows.Add(drCategoryContract); endcell++; } var worksheetCategoryContract = workbook.AddWorksheet(validationTableCategoryContract); worksheet.Column(8).SetDataValidation().List(worksheetCategoryContract.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true); //Project using (var ProjectQuery2 = new ProjectQuery()) { //SETUP TABLE DataTable validationTableProject = new DataTable(); validationTableProject.TableName = "Project"; //SETUP COLUMN ProjectDTO objProject = new ProjectDTO(); foreach (var item in objkatja.GetType().GetProperties()) { validationTableProject.Columns.Add(item.Name); } var dataProject = ProjectQuery2.GetQuery(); DataRow drProject; 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(9).SetDataValidation().List(worksheetProject.Range("B" + startcell.ToString() + ":B" + endcell.ToString()), true); } } } } } } worksheet.Protect("kairosg1") .SetFormatCells() // Cell Formatting .SetInsertColumns() // Inserting Columns .SetDeleteColumns() // Deleting Columns .SetDeleteRows(); // Deleting Rows; worksheet.Columns(1, 2).Style.Fill.BackgroundColor = XLColor.DarkGray; worksheet.Columns(1, 2).Style.Font.FontColor = XLColor.Black; worksheet.Columns(3, 20).Style.Protection.SetLocked(false); } 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 <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); }