private static void GetClaimDetailsModel(string SSN, string DependentSeq, string ClaimNumber, DateTime? Fromdate, DateTime? Todate,string Dependent,string SortingColumn, string Orderby, int page, int size, List<ClaimDetailModel> claimDetailModels) { DataTable MemberClaimTable = new DataTable(); if (DependentSeq == null || DependentSeq == "0") { MemberClaimTable = Db2Connnect.GetDataTable(GetSqlQuery.GeTMemberClaims(SSN, ClaimNumber, Fromdate, Todate, Dependent,SortingColumn, Orderby,page,size), CommandType.Text); } else { MemberClaimTable = Db2Connnect.GetDataTable(GetSqlQuery.GeTDependentClaims(SSN, DependentSeq, ClaimNumber, Fromdate, Todate, Dependent,SortingColumn, Orderby,page,size), CommandType.Text); } foreach (DataRow item in MemberClaimTable.Rows) { ClaimDetailModel claimDetailModel = new ClaimDetailModel(); claimDetailModel.EOBNO = item["EOBNo"].ToString(); claimDetailModel.SSN = SSN; claimDetailModel.ClaimNo = item["ClaimNumber"].ToString(); claimDetailModel.For = item["ForPerson"].ToString().Replace("*",","); claimDetailModel.Type = item["ClaimType"].ToString(); claimDetailModel.Total = ((decimal)item["ClaimAmount"]).ToString("C", CultureInfo.CurrentCulture); claimDetailModel.PlanPaid = ((decimal)item["Paid"]).ToString("C", CultureInfo.CurrentCulture); claimDetailModel.MemerResp = ((decimal)item["MemberPaid"]).ToString("C", CultureInfo.CurrentCulture); claimDetailModel.ClaimYear = item["ClaimYear"].ToString(); claimDetailModel.ClaimMonth = item["ClaimMonth"].ToString(); claimDetailModel.ClaimDate = item["ClaimDate"].ToString(); claimDetailModel.Provider = item["PROVIDER"].ToString(); claimDetailModels.Add(claimDetailModel); } }
private static List<DEDMET_OOP_Model> GetDEDMETOOP(string SSN, int Year) { DataTable dt = new DataTable(); List<DEDMET_OOP_Model> dEDMET_OOP_Models = new List<DEDMET_OOP_Model>(); DataTable dtDedScheduled = new DataTable(); DataTable dtOOPScheduled = new DataTable(); DataTable dtOOPMetFamily = new DataTable(); DataTable dtOOPMetIndividual = new DataTable(); DataTable dtDedMetIndividual = new DataTable(); DataTable dtDedMetFamily = new DataTable(); // This code will change based on client , MED is currently hardcoded for AMO string DeductibleCode = "MED"; string FamilyCode = "F"; string IndividualCode = "I"; dtDedScheduled = Db2Connnect.GetDataTable(GetSqlQuery.GetDeductibleMax(DeductibleCode, Year.ToString()), CommandType.Text); dtOOPScheduled = Db2Connnect.GetDataTable(GetSqlQuery.GetOOPMax(DeductibleCode, Year.ToString()), CommandType.Text); dtOOPMetIndividual = Db2Connnect.GetDataTable(GetSqlQuery.GetOOPMet(DeductibleCode, Year.ToString(), IndividualCode, Convert.ToInt32(SSN)), CommandType.Text); dtOOPMetFamily = Db2Connnect.GetDataTable(GetSqlQuery.GetOOPMet(DeductibleCode, Year.ToString(), FamilyCode, Convert.ToInt32(SSN)), CommandType.Text); dtDedMetIndividual = Db2Connnect.GetDataTable(GetSqlQuery.GetDeductibleMet(DeductibleCode, Year.ToString(), IndividualCode, Convert.ToInt32(SSN)), CommandType.Text); dtDedMetFamily = Db2Connnect.GetDataTable(GetSqlQuery.GetDeductibleMet(DeductibleCode, Year.ToString(), FamilyCode, Convert.ToInt32(SSN)), CommandType.Text); return AccumullatorClass.GetAccumullatorLogic(Year,dEDMET_OOP_Models, dtDedScheduled, dtOOPScheduled, dtOOPMetFamily, dtOOPMetIndividual, dtDedMetIndividual, dtDedMetFamily); }
public int GetTotalCount_ClaimDetailTable(string SSN, string DependentSeq, string ClaimNumber, DateTime? Fromdate, DateTime? Todate,string Dependent) { int TotalCount = 0; DataTable dt = new DataTable(); try { dt = Db2Connnect.GetDataTable(GetSqlQuery.GetTotalCountClaimDetailTable(SSN, DependentSeq, ClaimNumber, Fromdate, Todate, Dependent), CommandType.Text); if (dt.Rows.Count>0) { TotalCount = (int)dt.Rows[0]["TotalCount"]; } } catch (Exception ex) { throw; } return TotalCount; }
public JsonResult GetMember(int page, string SearchMember, bool GlobalSearch, string SortingColumn = "Member", string Orderby = "ASC") { ViewBag.GlobalSearch = GlobalSearch; //bool GlobalSearch = (bool)TempData.Peek("GlobalSearch"); List <MemeberDetailsModel> memeberDetailsModels = new List <MemeberDetailsModel>(); string viewContent = ""; int TotalCount = 0; try { if (GlobalSearch) { DataTable dt = new DataTable(); DBManager db = new DBManager("CustomerServicePortal"); UserModel userModel = (UserModel)Session["UserModel"]; dt = db.GetDataTable(GetSqlQuery.GetEMployDetails_GlobalSearch(SearchMember, SortingColumn, Orderby, userModel.UserId, page, 10), CommandType.Text); GlobalAndLocalSearch_Datatable_TOList(memeberDetailsModels, dt); viewContent = ConvertViewToString("_MemberListPartialView", memeberDetailsModels); TotalCount = (int)db.GetScalarValue(GetSqlQuery.GlobalSearchTotalCount(SearchMember, userModel.UserId), CommandType.Text); } else { GetmemeberListModel(SearchMember, memeberDetailsModels, page, SortingColumn, Orderby); viewContent = ConvertViewToString("_MemberListPartialView", memeberDetailsModels); TotalCount = (int)Db2Connnect.GetDataTable(GetSqlQuery.TotalMemeberCount(SearchMember), CommandType.Text).Rows[0]["Total"]; } } catch (Exception ex) { throw; } return(Json(new { Page = page, TotalCount = TotalCount, viewContent = viewContent }, JsonRequestBehavior.AllowGet)); }
public ActionResult MemebersList(string SearchMember, bool GlobalSearch) { List <MemeberDetailsModel> memeberDetailsModels = new List <MemeberDetailsModel>(); try { TempData["GlobalSearch"] = GlobalSearch; ViewBag.GlobalSearch = GlobalSearch; if (GlobalSearch) { DataTable dt = new DataTable(); DBManager db = new DBManager("CustomerServicePortal"); UserModel userModel = (UserModel)Session["UserModel"]; dt = db.GetDataTable(GetSqlQuery.GetEMployDetails_GlobalSearch(SearchMember, "EMNAME", "ASC", userModel.UserId, 1, 10), CommandType.Text); GlobalAndLocalSearch_Datatable_TOList(memeberDetailsModels, dt); ViewBag.TotalmemeberCount = db.GetScalarValue(GetSqlQuery.GlobalSearchTotalCount(SearchMember, userModel.UserId), CommandType.Text); } else { GetmemeberListModel(SearchMember, memeberDetailsModels, 1, "EMNAME", "ASC"); ViewBag.TotalmemeberCount = (int)Db2Connnect.GetDataTable(GetSqlQuery.TotalMemeberCount(SearchMember), CommandType.Text).Rows[0]["Total"]; } ViewBag.SearchMember = SearchMember; } catch (Exception ex) { throw; } return(View(memeberDetailsModels)); }
private static void IdCardRequestDatatableToList(List <IdCardListModel> idCardListModels, DataSet ds) { foreach (DataRow item in ds.Tables[1].Rows) { IdCardListModel idCardListModel = new IdCardListModel(); idCardListModel.Id = (long)item["Id"]; idCardListModel.EMSSN = item["EMSSN"].ToString(); idCardListModel.Name = item["Name"].ToString().Replace("*", ""); idCardListModel.Gender = item["Gender"].ToString(); idCardListModel.Completestatus = (bool)item["Complete"]; idCardListModel.IDCardType = item["IDCardType"].ToString(); idCardListModel.IDCardQuantity = (int)item["Quantity"]; DataTable dt1 = new DataTable(); dt1 = Db2Connnect.GetDataTable(GetSqlQuery.GetMemberDetailsWIthSSN(idCardListModel.EMSSN), CommandType.Text); if (dt1.Rows.Count > 0) { idCardListModel.Addr1 = dt1.Rows[0]["Addr1"].ToString().TrimEnd().TrimStart(); idCardListModel.Addr2 = dt1.Rows[0]["Addr2"].ToString().TrimEnd().TrimStart(); idCardListModel.City = dt1.Rows[0]["City"].ToString().TrimEnd().TrimStart(); idCardListModel.MemberId = dt1.Rows[0]["Id"].ToString(); idCardListModel.State = dt1.Rows[0]["State"].ToString().TrimEnd().TrimStart(); idCardListModel.Zip1 = dt1.Rows[0]["Zip1"].ToString().TrimEnd().TrimStart(); } idCardListModels.Add(idCardListModel); } }
private static void GetmemeberListModel(string SearchMember, List <MemeberDetailsModel> memeberDetailsModels, int page, string SortingColumn, string Orderby) { DataTable dt = new DataTable(); dt = Db2Connnect.GetDataTable(GetSqlQuery.GetEMployDetails(SearchMember, SortingColumn, Orderby, page, 10), CommandType.Text); GlobalAndLocalSearch_Datatable_TOList(memeberDetailsModels, dt); }
private static DependentDetailModel GetDependentWithSEQtModel(string SSN,int DEPSEQ) { DataTable dependenttable = new DataTable(); dependenttable = Db2Connnect.GetDataTable(GetSqlQuery.GetDependentDetailsWithSeq(SSN,DEPSEQ), CommandType.Text); DependentDetailModel dependentDetailModel = new DependentDetailModel(); foreach (DataRow item in dependenttable.Rows) { dependentDetailModel.SSN =item["DPSSN"].ToString(); dependentDetailModel.DependentSeq = item["SEQ"].ToString(); dependentDetailModel.DependenetName = item["NAME"].ToString().Replace("*", ","); //(item["NAME"].ToString().Split('*')[1] + "*" + item["NAME"].ToString().Split('*')[0]).Replace("*", ",").Replace(" ", "()").Replace(")(", "").Replace("()", " "); dependentDetailModel.Relation = item["RELATION"].ToString(); dependentDetailModel.Status = item["STATUS"].ToString(); dependentDetailModel.BirthYear = item["DOBY"].ToString(); dependentDetailModel.BirthMonth = item["DOBM"].ToString(); dependentDetailModel.BirthDay = item["DOBD"].ToString(); dependentDetailModel.Class = item["CLASS"].ToString(); dependentDetailModel.Plan = item["PLAN"].ToString(); dependentDetailModel.BoolStatus = item["STATUS"].ToString() == "A" ? true : false; dependentDetailModel.EffectiveYear = item["EFDY"].ToString(); dependentDetailModel.EffectiveMonth = item["EFDM"].ToString(); dependentDetailModel.EffectiveDay = item["EFDD"].ToString(); dependentDetailModel.ADDRESS1 = item["ADDRESS1"].ToString(); dependentDetailModel.ADDRESS2 = item["ADDRESS2"].ToString(); dependentDetailModel.ADDRESS3 = item["ADDRESS3"].ToString(); dependentDetailModel.STATE = item["STATE"].ToString(); dependentDetailModel.CITY = item["CITY"].ToString(); dependentDetailModel.ZIP4 = item["ZIP4"].ToString(); dependentDetailModel.ZIP5 = item["ZIP5"].ToString(); if (item["TDTY"].ToString()!="0" && item["TDTM"].ToString() != "0" && item["TDTD"].ToString() != "0") { dependentDetailModel.TerminationDate = new DateTime(Convert.ToInt32(item["TDTY"].ToString()), Convert.ToInt32(item["TDTM"].ToString()), Convert.ToInt32(item["TDTD"].ToString())); } if (item["EFDY"].ToString() != "0" && item["EFDM"].ToString() != "0" && item["EFDD"].ToString() != "0") { dependentDetailModel.EffectiveDate = new DateTime(Convert.ToInt32(item["EFDY"].ToString()), Convert.ToInt32(item["EFDM"].ToString()), Convert.ToInt32(item["EFDD"].ToString())); } } return dependentDetailModel; }
private static void GetClaimDetailsExpandList(string SSN, string Claimnumber, List<ClaimDeatilExpandModel> claimDeatilExpandModels) { DataTable dt = new DataTable(); dt = Db2Connnect.GetDataTable(GetSqlQuery.GetClaimDetailsWIthClaimNumber(SSN, Claimnumber), CommandType.Text); foreach (DataRow item in dt.Rows) { ClaimDeatilExpandModel claimDeatilExpandModel = new ClaimDeatilExpandModel(); claimDeatilExpandModel.BenefitCode = item["BenefitCode"].ToString(); claimDeatilExpandModel.ClaimNo = item["ClaimNo"].ToString(); claimDeatilExpandModel.Coinsurance = ((decimal)item["Coinsurance"]).ToString("C", CultureInfo.CurrentCulture); claimDeatilExpandModel.CPT = item["CPT#"].ToString(); claimDeatilExpandModel.Dedcutible = ((decimal)item["Dedcutible"]).ToString("C", CultureInfo.CurrentCulture); claimDeatilExpandModel.LineNo = ((decimal)item["LineNo"]).ToString(); claimDeatilExpandModel.OOP = ((decimal)item["OOP"]).ToString("C", CultureInfo.CurrentCulture); ; claimDeatilExpandModel.Paid = ((decimal)item["Paid"]).ToString("C", CultureInfo.CurrentCulture); ; claimDeatilExpandModel.ProviderDiscount = ((decimal)item["ProviderDiscount"]).ToString("C", CultureInfo.CurrentCulture); claimDeatilExpandModel.Status = item["Status"].ToString(); claimDeatilExpandModel.TotalCharge = ((decimal)item["TotalCharge"]).ToString("C", CultureInfo.CurrentCulture); claimDeatilExpandModels.Add(claimDeatilExpandModel); } }
private static EMPdetails GetEMployDetailsModelWIthSSN(string SSN) { DataTable Employdetails = new DataTable(); Employdetails = Db2Connnect.GetDataTable(GetSqlQuery.GetMemberDetailsWIthSSN(SSN), CommandType.Text); EMPdetails eMPdetails = new EMPdetails(); foreach (DataRow item in Employdetails.Rows) { eMPdetails.Id = item["Id"].ToString(); eMPdetails.Name = item["Name"].ToString().Replace("*", ","); eMPdetails.Gender = item["Gender"].ToString(); eMPdetails.DOBDay = item["DOBD"].ToString(); eMPdetails.DOBMonth = item["DOBM"].ToString(); eMPdetails.DOBYear = item["DOBY"].ToString(); eMPdetails.EMSSN = item["EMSSN"].ToString(); eMPdetails.Addr1= item["Addr1"].ToString().TrimEnd().TrimStart(); eMPdetails.Addr2 = item["Addr2"].ToString().TrimEnd().TrimStart(); eMPdetails.Addr3 = item["Addr3"].ToString().TrimEnd().TrimStart(); eMPdetails.Addr4 = item["Addr4"].ToString().TrimEnd().TrimStart(); eMPdetails.City = item["City"].ToString().TrimEnd().TrimStart(); eMPdetails.State = item["State"].ToString().TrimEnd().TrimStart(); eMPdetails.Zip1 = item["Zip1"].ToString().TrimEnd().TrimStart(); eMPdetails.Zip2 = item["Zip2"].ToString().TrimEnd().TrimStart(); eMPdetails.Zip3 = item["Zip3"].ToString().TrimEnd().TrimStart(); } DBManager db = new DBManager("CustomerServicePortal"); string Commadtext = @"SELECT CASE WHEN COUNT(CASE WHEN complete = 0 then 1 ELSE NULL END)> 0 THEN 0 ELSE 1 end from [IDCardRequestDetails] where EMSSN = @SSN"; var parameters = new List<IDbDataParameter>(); parameters.Add(db.CreateParameter("@SSN", SSN, DbType.String)); eMPdetails.ShowRequestId = ((int)db.GetScalarValue(Commadtext, CommandType.Text, parameters.ToArray())==1)? true:false; return eMPdetails; }
private static List<DependentDetailModel> GetDependentListModel(string SSN) { DataTable dependenttable = new DataTable(); dependenttable = Db2Connnect.GetDataTable(GetSqlQuery.GetDependentDetails(SSN), CommandType.Text); List<DependentDetailModel> dependentDetailModels = new List<DependentDetailModel>(); foreach (DataRow item in dependenttable.Rows) { DependentDetailModel dependentDetailModel = new DependentDetailModel(); dependentDetailModel.SSN = item["DPSSN"].ToString(); dependentDetailModel.DependentSeq = item["SEQ"].ToString(); dependentDetailModel.DependenetName = item["Name"].ToString().Replace("*", ","); dependentDetailModel.Relation = item["RELATION"].ToString(); dependentDetailModel.Status = item["STATUS"].ToString(); dependentDetailModel.BirthYear = item["DOBY"].ToString(); dependentDetailModel.BirthMonth = item["DOBM"].ToString(); dependentDetailModel.BirthDay = item["DOBD"].ToString(); dependentDetailModel.Class = item["CLASS"].ToString(); dependentDetailModel.Plan = item["PLAN"].ToString(); dependentDetailModels.Add(dependentDetailModel); } return dependentDetailModels; }
public JsonResult ExportExcel(string SSN = "", string DEPSEQ = "", string claim = "", string Dependent = "", string SortingColumn = "", string Orderby = "", DateTime? Fromdate = null, DateTime? Todate = null) { DataTable ExcelDatatable = new DataTable(); var fileName = "ClaimDetails" + ".xlsx"; string fullPath = Path.Combine(Server.MapPath("~/ExcelFile"), fileName); try { DataTable dt = new DataTable(); if (DEPSEQ == null || DEPSEQ == "0") { dt = Db2Connnect.GetDataTable(GetSqlQuery.GeTMemberClaims(SSN, claim, Fromdate, Todate, Dependent, SortingColumn, Orderby, 0, 0), CommandType.Text); } else { dt = Db2Connnect.GetDataTable(GetSqlQuery.GeTDependentClaims(SSN, DEPSEQ, claim, Fromdate, Todate, Dependent, SortingColumn, Orderby, 0, 0), CommandType.Text); } ExcelDatatable = GetClaimDetailExportDatatable(dt); using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookpart = myDoc.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); Worksheet ws = new Worksheet(); SheetData sheetData = new SheetData(); WorkbookStylesPart wbsp = workbookpart.AddNewPart<WorkbookStylesPart>(); wbsp.Stylesheet = GenerateStylesheet(); wbsp.Stylesheet.Save(); Columns columns = new Columns(); columns.Append(CreateColumnData(1, 1, 11)); columns.Append(CreateColumnData(2, 3, 16)); columns.Append(CreateColumnData(4, 4, 20)); columns.Append(CreateColumnData(5, 5, 11)); columns.Append(CreateColumnData(6, 6, 28)); columns.Append(CreateColumnData(7, 9, 11)); ws.Append(columns); //add a row Row firstRow = new Row(); firstRow.RowIndex = (UInt32)1; Cell dataCell = new Cell(); CellValue cellValue = new CellValue(); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A1"; dataCell.StyleIndex = 2; dataCell.CellValue = new CellValue("Filtered By"); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A2"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("From Date : " + ((Fromdate == null) ? "ALL" : Fromdate?.ToString("MM/dd/yyyy"))); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A3"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("To Date : " + ((Todate == null) ? "ALL" : Todate?.ToString("MM/dd/yyyy"))); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A4"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("DEPN# : " + ((DEPSEQ == "") ? "ALL" : DEPSEQ)); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.CellReference = "A5"; dataCell.StyleIndex = 0; dataCell.CellValue = new CellValue("CLAIM# : " + ((claim == "") ? "ALL" : claim)); firstRow.Append(dataCell); sheetData.Append(firstRow); firstRow = new Row(); List<String> columns1 = new List<string>(); foreach (System.Data.DataColumn column in ExcelDatatable.Columns) { columns1.Add(column.ColumnName); dataCell = new Cell(); dataCell.DataType = CellValues.String; dataCell.StyleIndex = 2; dataCell.CellValue = new CellValue(column.ColumnName); firstRow.Append(dataCell); } sheetData.Append(firstRow); foreach (DataRow dsrow in ExcelDatatable.Rows) { firstRow = new Row(); foreach (String col in columns1) { dataCell = new Cell(); dataCell.DataType = CellValues.String; //c.CellReference = "A2"; dataCell.StyleIndex = 1; dataCell.CellValue = new CellValue(dsrow[col].ToString()); firstRow.Append(dataCell); } sheetData.Append(firstRow); } ws.Append(sheetData); worksheetPart.Worksheet = ws; MergeCells mergeCells = new MergeCells(); //append a MergeCell to the mergeCells for each set of merged cells mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:I1") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A2:I2") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A3:I3") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A4:I4") }); mergeCells.Append(new MergeCell() { Reference = new StringValue("A5:I5") }); worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First()); //this is the part that was missing from your code Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets()); sheets.AppendChild(new Sheet() { Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()), SheetId = 1, Name = "Sheet1" }); //myDoc.WorkbookPart.Workbook = workbookpart.Workbook; myDoc.WorkbookPart.Workbook.Save(); myDoc.Close(); } } catch (Exception) { throw; } return Json(new { fileName = fileName }); }