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 });
}
