public IEnumerable <RBILiaisonOffice> GetRBILiaisonOffice(RBILiaisonOffice rBILiaisonOffice) { using (DemsifyEntities dataContext = new DemsifyEntities()) { ObjectParameter totalPageCount = new ObjectParameter("TotalPageCount", typeof(int)); ObjectParameter totalRecord = new ObjectParameter("TotalRecord", typeof(int)); var rBILiaisonOffices = dataContext.RBILiaisonOfficeGet(rBILiaisonOffice.RBILiaisonOfficeId, Utility.TrimString(rBILiaisonOffice.SearchText), rBILiaisonOffice.IsActive, rBILiaisonOffice.PageNumber, rBILiaisonOffice.PageSize, rBILiaisonOffice.IsPagingRequired, Utility.TrimString(rBILiaisonOffice.OrderBy), Utility.TrimString(rBILiaisonOffice.OrderByDirection), totalPageCount, totalRecord).ToList(); var rBILiaisonOfficeList = new List <RBILiaisonOffice>(); foreach (var rBILiaisonOfficeDetail in rBILiaisonOffices) { rBILiaisonOfficeList.Add(new RBILiaisonOffice() { RBILiaisonOfficeId = rBILiaisonOfficeDetail.RBILiaisonOfficeId, SerialNo = rBILiaisonOfficeDetail.SerialNo, NameAndAddressOfTheCompany = rBILiaisonOfficeDetail.NameAndAddressOfTheCompany, PlaceOfTheLiaisonOffice = rBILiaisonOfficeDetail.PlaceOfTheLiaisonOffice, DateOfApprovalGrantedOrUINAlloted = Convert.ToDateTime(rBILiaisonOfficeDetail.DateOfApprovalGrantedOrUINAlloted), UIN = rBILiaisonOfficeDetail.UIN, IsActive = rBILiaisonOfficeDetail.IsActive, TotalPageCount = Convert.ToInt32(totalPageCount.Value), TotalRecord = Convert.ToInt32(totalRecord.Value) }); } return(rBILiaisonOfficeList); } }
public void AddRBILiaisonOfficeFromXML(RBILiaisonOffice rBILiaisonOffice) { using (DemsifyEntities dataContext = new DemsifyEntities()) { ObjectParameter rBILiaisonOfficesResult = new ObjectParameter("RBILiaisonOfficesResult", typeof(string)); ObjectParameter result = new ObjectParameter("Result", typeof(int)); dataContext.RBILiaisonOfficeAddFromXML(rBILiaisonOffice.RBILiaisonOffices.ToString().Replace("'", "''"), rBILiaisonOffice.CreatedBy, rBILiaisonOfficesResult, result); rBILiaisonOffice.RBILiaisonOffices = (rBILiaisonOfficesResult.Value != null) ? XElement.Parse(rBILiaisonOfficesResult.Value.ToString()) : rBILiaisonOffice.RBILiaisonOffices; rBILiaisonOffice.Result = Convert.ToInt32(result.Value); } }
public IHttpActionResult GetRBILiaisonOffice([FromUri] GetRBILiaisonOfficeRequest getRBILiaisonOfficeRequest) { var responses = new Responses(); try { if (Utility.UserId < 0) { return(BadRequest(Utility.INVALID_USER)); } if (getRBILiaisonOfficeRequest == null) { getRBILiaisonOfficeRequest = new GetRBILiaisonOfficeRequest(); } if (getRBILiaisonOfficeRequest.PageSize == null) { getRBILiaisonOfficeRequest.PageSize = Convert.ToInt32(ConfigurationManager.AppSettings["PageSize"]); } var rBILiaisonOffice = new RBILiaisonOffice() { RBILiaisonOfficeId = getRBILiaisonOfficeRequest.RBILiaisonOfficeId, SearchText = getRBILiaisonOfficeRequest.SearchText, IsActive = getRBILiaisonOfficeRequest.IsActive, PageNumber = getRBILiaisonOfficeRequest.PageNumber, PageSize = Convert.ToInt32(getRBILiaisonOfficeRequest.PageSize), IsPagingRequired = (getRBILiaisonOfficeRequest.PageNumber != null) ? true : false, OrderBy = getRBILiaisonOfficeRequest.OrderBy, OrderByDirection = getRBILiaisonOfficeRequest.OrderByDirection }; var rBILiaisonOffices = iRBILiaisonOffice.GetRBILiaisonOffice(rBILiaisonOffice); var rBILiaisonOfficeList = new List <GetRBILiaisonOfficeResponse>(); foreach (var rBILiaisonOfficeDetail in rBILiaisonOffices) { rBILiaisonOfficeList.Add(new GetRBILiaisonOfficeResponse() { RBILiaisonOfficeId = Convert.ToInt32(rBILiaisonOfficeDetail.RBILiaisonOfficeId), SerialNo = rBILiaisonOfficeDetail.SerialNo, NameAndAddressOfTheCompany = rBILiaisonOfficeDetail.NameAndAddressOfTheCompany, PlaceOfTheLiaisonOffice = rBILiaisonOfficeDetail.PlaceOfTheLiaisonOffice, DateOfApprovalGrantedOrUINAlloted = rBILiaisonOfficeDetail.DateOfApprovalGrantedOrUINAlloted, UIN = rBILiaisonOfficeDetail.UIN, IsActive = rBILiaisonOfficeDetail.IsActive, CreatedBy = rBILiaisonOfficeDetail.CreatedBy, TotalPageCount = rBILiaisonOfficeDetail.TotalPageCount, TotalRecord = rBILiaisonOfficeDetail.TotalRecord }); } responses.Status = Utility.SUCCESS_STATUS_RESPONSE; responses.Description = "RBILiaisonOffice retrieved successfully"; responses.Response = rBILiaisonOfficeList; } catch (Exception ex) { responses.Status = Utility.ERROR_STATUS_RESPONSE; responses.Description = "Error while retrieving RBILiaisonOffice."; Utility.WriteLog("GetRBILiaisonOffice", getRBILiaisonOfficeRequest, "Error while retrieving RBILiaisonOffice. (RBILiaisonOfficeAdminController)", ex.ToString()); } return(Ok(responses)); }
public IHttpActionResult AddRBILiaisonOfficeFromExcel() { var responses = new Responses(); try { if (Utility.UserId < 0) { return(BadRequest(Utility.INVALID_USER)); } var httpRequest = HttpContext.Current.Request; if (httpRequest.Files.Count == 0) { return(BadRequest("File not found")); } var file = httpRequest.Files[0]; string filename = DateTime.Now.ToString("ddMMyyyyhhmmssfff") + "_" + file.FileName; string destinationPath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["RBILiaisonOfficeExcelPath"]); destinationPath = Path.Combine(destinationPath, filename); file.SaveAs(destinationPath); XElement xRBILiaisonOffices = new XElement("RBILiaisonOffices"); XElement xSkippedRBILiaisonOffices = new XElement("SkippedRBILiaisonOffices"); if (!File.Exists(destinationPath)) { return(BadRequest("File not found")); } string extension = System.IO.Path.GetExtension(filename).ToLower(); string connString = (extension.Trim() == ".xls") ? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + destinationPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"" : (extension.Trim() == ".xlsx") ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + destinationPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"" : null; if (string.IsNullOrEmpty(connString)) { return(BadRequest("File must be extension with .xls or .xlsx")); } DataTable dt = Utility.ConvertXSLXtoDataTable(destinationPath, connString); int count = 0, lineNumber = 0; foreach (DataRow dr in dt.Rows) { if (count != 0) { lineNumber = count; try { xRBILiaisonOffices.Add(new XElement("RBILiaisonOffice", new XAttribute("SerialNo", dr[0].ToString().Trim()) , new XAttribute("NameAndAddressOfTheCompany", dr[1].ToString().Trim()) , new XAttribute("PlaceOfTheLiaisonOffice", dr[2].ToString().Trim()) , new XAttribute("DateOfApprovalGrantedOrUINAlloted", DateTime.ParseExact(dr[3].ToString().Trim(), "dd-MM-yyyy", CultureInfo.InvariantCulture)) , new XAttribute("UIN", dr[4].ToString().Trim()) , new XAttribute("Status", lineNumber + 3) , new XAttribute("LineNumber", lineNumber))); } catch (Exception ex) { Utility.WriteLog("AddRBILiaisonOfficeFromFile", null, "Error while reading RBILiaisonOffice file and storing in xml (Admin)", string.Format("LineNumber : {0} \n Error : {1}", lineNumber, ex.ToString())); try { xSkippedRBILiaisonOffices.Add(new XElement("RBILiaisonOffice", new XAttribute("SerialNo", dr[0].ToString().Trim()) , new XAttribute("NameAndAddressOfTheCompany", dr[1].ToString().Trim()) , new XAttribute("PlaceOfTheLiaisonOffice", dr[2].ToString().Trim()) , new XAttribute("DateOfApprovalGrantedOrUINAlloted", dr[3].ToString().Trim()) , new XAttribute("UIN", dr[4].ToString().Trim()) , new XAttribute("Status", "SKIPPED") , new XAttribute("LineNumber", lineNumber + 3))); } catch (Exception exCatch) { Utility.WriteLog("AddRBILiaisonOfficeFromFile", null, "Error while reading RBILiaisonOffice file and storing in skipped xml (Admin)", string.Format("LineNumber : {0} \n Error : {1}", lineNumber, exCatch.ToString())); } } } count++; } var rBILiaisonOffice = new RBILiaisonOffice() { RBILiaisonOffices = xRBILiaisonOffices, CreatedBy = Utility.UserId }; iRBILiaisonOffice.AddRBILiaisonOfficeFromXML(rBILiaisonOffice); if (rBILiaisonOffice.Result > 0) { var rBILiaisonOfficeData = rBILiaisonOffice.RBILiaisonOffices.Descendants("RBILiaisonOffice").Select(x => new { SerialNo = x.Attribute("SerialNo").Value, NameAndAddressOfTheCompany = x.Attribute("NameAndAddressOfTheCompany").Value, PlaceOfTheLiaisonOffice = x.Attribute("PlaceOfTheLiaisonOffice").Value, DateOfApprovalGrantedOrUINAlloted = x.Attribute("DateOfApprovalGrantedOrUINAlloted").Value, UIN = x.Attribute("UIN").Value, Status = x.Attribute("Status").Value, LineNumber = x.Attribute("LineNumber").Value }).ToList(); var skippedRBILiaisonOffices = xSkippedRBILiaisonOffices.Descendants("RBILiaisonOffice").Select(x => new { SerialNo = x.Attribute("SerialNo").Value, NameAndAddressOfTheCompany = x.Attribute("NameAndAddressOfTheCompany").Value, PlaceOfTheLiaisonOffice = x.Attribute("PlaceOfTheLiaisonOffice").Value, DateOfApprovalGrantedOrUINAlloted = x.Attribute("DateOfApprovalGrantedOrUINAlloted").Value, UIN = x.Attribute("UIN").Value, Status = x.Attribute("Status").Value, LineNumber = x.Attribute("LineNumber").Value }).ToList(); rBILiaisonOfficeData.AddRange(skippedRBILiaisonOffices); List <NotProcessedRow> notProcessedRowList = rBILiaisonOfficeData.Where(x => x.Status != "SUCCESS").Select(x => new NotProcessedRow() { SerialNo = Convert.ToInt32(x.SerialNo), NameAndAddressOfTheCompany = x.NameAndAddressOfTheCompany, PlaceOfTheLiaisonOffice = x.PlaceOfTheLiaisonOffice, DateOfApprovalGrantedOrUINAlloted = x.DateOfApprovalGrantedOrUINAlloted, UIN = x.UIN, Status = x.Status, LineNumber = x.LineNumber }).ToList(); var addRBILiaisonOfficeFromFileResponse = new AddRBILiaisonOfficeFromFileResponse() { SuccessRow = rBILiaisonOfficeData.Where(x => x.Status == "SUCCESS").Count(), SkippedRow = rBILiaisonOfficeData.Where(x => x.Status == "SKIPPED").Count(), ErrorRow = rBILiaisonOfficeData.Where(x => x.Status == "ERROR").Count(), NotProcessedRow = notProcessedRowList }; responses.Response = addRBILiaisonOfficeFromFileResponse; responses.Status = Utility.SUCCESS_STATUS_RESPONSE; responses.Description = "RBILiaisonOffice added successfully."; } else { responses.Status = Utility.ERROR_STATUS_RESPONSE; responses.Description = "Error while adding RBILiaisonOffice"; } } catch (Exception ex) { responses.Status = Utility.ERROR_STATUS_RESPONSE; responses.Description = "Error while adding RBILiaisonOffice"; Utility.WriteLog("AddRBILiaisonOfficeFromFile", null, "Error while adding RBILiaisonOffice list.", ex.ToString()); } return(Ok(responses)); }