using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace EDI_Automated_PCRS_Reports
{
class Program
{
static void Main(string[] args)
{
Program p = new Program();
p.setupObjects();
}
private void setupObjects()
{
CignaPayerSolutionsQPAClaimReportGlobalcareZelisReport();
}
private void CignaPayerSolutionsQPAClaimReportGlobalcareZelisReport()
{
//Database
System.Data.DataTable CCRResults = _SQLAPI.spExecute("Authentication", "uspReport_CignaPayerSolutionsQPAClaimReportGlobalcareZelis", new object[] { }).Tables[0];
ExportAndEmailExcelReportForCignaPayerSolutionsQPAClaimReportGlobalcareZelis(CCRResults);
}
private void CignaPayerSolutionsElianceClaimsforpreviousmonth()
{
//Database
_SQLAPI = new SHNUtilities.SQLAPI(_Logging);
_SQLAPI.NewConnection("Authentication",
string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};",
Options.Default.DBServer,
Options.Default.DBServerDatabase,
Options.Default.DBServerUserID,
Options.Default.DBServerPW));
_SQLAPI.MaxTries = Options.Default.MaxTries;
_SQLAPI.LogSQL = Options.Default.LogSQL;
_Logging.ApplicationName = "EDI_Automated_PCRS_Reports";
_Logging.SQLAPI = _SQLAPI;
_Logging.LogToDatabaseLevel = System.Diagnostics.TraceLevel.Off;
System.Data.DataTable CCRResults = _SQLAPI.spExecute("Authentication", "uspReport_CignaPayerSolutionsElianceClaimsforpreviousmonth", new object[] { }).Tables[0];
ExportAndEmailExcelReportForCignaPayerSolutionsElianceClaimsforpreviousmonth(CCRResults);
}
protected void ExportAndEmailExcelReportForCignaPayerSolutionsQPAClaimReportGlobalcareZelis(DataTable dataTable)
{
C1.C1Excel.C1XLBook C1XLBook = new C1.C1Excel.C1XLBook();
C1XLBook.Sheets.Clear();
C1.C1Excel.XLSheet XLSheet = C1XLBook.Sheets.Add("CCR Results");
int RowIndex = 0;
XLSheet[0, 0].Value = "Cigna Claim Number";
XLSheet[0, 1].Value = "insured group number";
XLSheet[0, 2].Value = "insured group name";
XLSheet[0, 3].Value = "claim type";
XLSheet[0, 4].Value = "Outbound EDI PartnerID";
XLSheet[0, 5].Value = "patient last name";
XLSheet[0, 6].Value = "patient first name";
XLSheet[0, 7].Value = "from date";
XLSheet[0, 8].Value = "Claim Sent Date";
XLSheet[0, 9].Value = "Total Claim Charge Amount";
XLSheet[0, 10].Value = "QPA";
XLSheet[0, 11].Value = "NPIC";
XLSheet[0, 12].Value = "EDI File Name";
foreach (DataRow row in dataTable.Rows)
{
RowIndex++;
XLSheet[RowIndex, 0].Value = row["CignaClaimNumber"].ToString();
XLSheet[RowIndex, 1].Value = row["insuredgroupnumber"].ToString();
XLSheet[RowIndex, 2].Value = row["insuredgroupname"].ToString();
XLSheet[RowIndex, 3].Value = row["claimtype"].ToString();
XLSheet[RowIndex, 4].Value = row["OutboundEDIPartnerID"].ToString();
XLSheet[RowIndex, 5].Value = row["patientlastname"].ToString();
XLSheet[RowIndex, 6].Value = row["patientfirstname"].ToString();
XLSheet[RowIndex, 7].Value = row["fromdate"].ToString();
XLSheet[RowIndex, 8].Value = row["ClaimSentDate"].ToString();
XLSheet[RowIndex, 9].Value = row["TotalClaimChargeAmount"].ToString();
XLSheet[RowIndex, 10].Value = row["QPA"].ToString();
XLSheet[RowIndex, 11].Value = row["NPIC"].ToString();
XLSheet[RowIndex, 12].Value = row["EDIFileName"].ToString();
}
FinishStylingSheet(0, 8, C1XLBook, XLSheet);
System.IO.MemoryStream ms = new System.IO.MemoryStream();
C1XLBook.Save("CignaPayerSolutionsQPAClaimReportGlobalcareZelis.xlsx");
SHNUtilities.EMail email = new SHNUtilities.EMail(_Logging);
email.From = Options.Default.EmailFrom; // "noreply@cignapayersolutions.com";
email.To = Options.Default.EmailTo.Split(';').ToArray();
email.Subject = "Cigna Payer Solutions QPA Claim Report – Globalcare(Zelis)";
System.Text.StringBuilder EmailBody = new System.Text.StringBuilder();
email.BodyType = SHNUtilities.BodyType.HTML;
EmailBody.AppendLine("<table><tr><td></br></br></td></tr><table>");
List<string> listOfEmailAttachments = new List<string>();
listOfEmailAttachments.Add("CignaPayerSolutionsQPAClaimReportGlobalcareZelis.xlsx");
email.Attachments = listOfEmailAttachments.ToArray();
email.Send();
Console.WriteLine(email.Subject);
}
protected void ExportAndEmailExcelReportForCignaPayerSolutionsElianceClaimsforpreviousmonth(DataTable dataTable)
{
C1.C1Excel.C1XLBook C1XLBook = new C1.C1Excel.C1XLBook();
C1XLBook.Sheets.Clear();
C1.C1Excel.XLSheet XLSheet = C1XLBook.Sheets.Add("CCR Results");
int RowIndex = 0;
XLSheet[0, 0].Value = "Cigna Claim Number";
XLSheet[0, 1].Value = "Claim Type";
XLSheet[0, 2].Value = "Provider TIN";
XLSheet[0, 3].Value = "Provider NPI";
XLSheet[0, 4].Value = "Provider Name";
XLSheet[0, 5].Value = "Claim Sent Date";
XLSheet[0, 6].Value = "EDI File Name";
XLSheet[0, 7].Value = "From Date";
XLSheet[0, 8].Value = "Thru Date";
XLSheet[0, 9].Value = "total claim charge amount";
XLSheet[0, 10].Value = "Allowed Amount";
XLSheet[0, 11].Value = "Insured Group Number";
XLSheet[0, 12].Value = "Insured Group Name";
XLSheet[0, 13].Value = "Insured Lst Name";
XLSheet[0, 14].Value = "Insured First Name";
XLSheet[0, 15].Value = "Insured Member ID";
XLSheet[0, 16].Value = "insuredid1";
XLSheet[0, 17].Value = "insured birth date";
XLSheet[0, 18].Value = "individual relation shipcode";
XLSheet[0, 19].Value = "Patient Last Name";
XLSheet[0, 20].Value = "Patient First Name";
XLSheet[0, 21].Value = "Patient Birth Date";
XLSheet[0, 22].Value = "NPFIndicator";
foreach (DataRow row in dataTable.Rows)
{
RowIndex++;
XLSheet[RowIndex, 0].Value = row["CignaClaimNumber"].ToString();
XLSheet[RowIndex, 1].Value = row["ClaimType"].ToString();
XLSheet[RowIndex, 2].Value = row["ProviderTIN"].ToString();
XLSheet[RowIndex, 3].Value = row["ProviderNPI"].ToString();
XLSheet[RowIndex, 4].Value = row["ProviderName"].ToString();
XLSheet[RowIndex, 5].Value = row["ClaimSentDate"].ToString();
XLSheet[RowIndex, 6].Value = row["EDIFileName"].ToString();
XLSheet[RowIndex, 7].Value = row["FromDate"].ToString();
XLSheet[RowIndex, 8].Value = row["ThruDate"].ToString();
XLSheet[RowIndex, 9].Value = row["totalclaimchargeamount"].ToString();
XLSheet[RowIndex, 10].Value = row["AllowedAmount"].ToString();
XLSheet[RowIndex, 11].Value = row["InsuredGroupNumber"].ToString();
XLSheet[RowIndex, 12].Value = row["InsuredGroupName"].ToString();
XLSheet[RowIndex, 13].Value = row["InsuredLastName"].ToString();
XLSheet[RowIndex, 14].Value = row["InsuredFirstName"].ToString();
XLSheet[RowIndex, 15].Value = row["InsuredMemberID"].ToString();
XLSheet[RowIndex, 16].Value = row["insuredid1"].ToString();
XLSheet[RowIndex, 17].Value = row["insuredbirthdate"].ToString();
XLSheet[RowIndex, 18].Value = row["individualrelationshipcode"].ToString();
XLSheet[RowIndex, 19].Value = row["PatientLastName"].ToString();
XLSheet[RowIndex, 20].Value = row["PatientFirstName"].ToString();
XLSheet[RowIndex, 21].Value = row["PatientBirthDate"].ToString();
XLSheet[RowIndex, 22].Value = row["NPFIndicator"].ToString();
}
FinishStylingSheet(0, 8, C1XLBook, XLSheet);
System.IO.MemoryStream ms = new System.IO.MemoryStream();
C1XLBook.Save("CignaPayerSolutionsElianceClaimsforpreviousmonth.xlsx");
SHNUtilities.EMail email = new SHNUtilities.EMail(_Logging);
email.From = Options.Default.EmailFrom; // "noreply@cignapayersolutions.com";
email.To = Options.Default.EmailTo.Split(';').ToArray();
email.Subject = "Cigna Payer Solutions Eliance Claims for previous month";
System.Text.StringBuilder EmailBody = new System.Text.StringBuilder();
email.BodyType = SHNUtilities.BodyType.HTML;
EmailBody.AppendLine("<table><tr><td></br></br></td></tr><table>");
List<string> listOfEmailAttachments = new List<string>();
listOfEmailAttachments.Add("CignaPayerSolutionsElianceClaimsforpreviousmonth.xlsx");
email.Attachments = listOfEmailAttachments.ToArray();
email.Send();
Console.WriteLine(email.Subject);
}
private void FinishStylingSheet(int StartRow, int ColCount, C1.C1Excel.C1XLBook C1XLBook, C1.C1Excel.XLSheet XLSheet)
{
//Add Styles
C1.C1Excel.XLStyle XLStyle;
C1.C1Excel.XLCellRange XLCellRange;
//Header Row
XLCellRange = new C1.C1Excel.XLCellRange(XLSheet, StartRow, StartRow, 0, ColCount);
XLStyle = new C1.C1Excel.XLStyle(C1XLBook);
XLStyle.Font = new System.Drawing.Font("Calibri", 11, System.Drawing.FontStyle.Bold);
XLCellRange.Style = XLStyle;
//Data Rows
XLCellRange = new C1.C1Excel.XLCellRange(XLSheet, StartRow + 1, XLSheet.Rows.Count - 1, 0, ColCount);
XLStyle = new C1.C1Excel.XLStyle(C1XLBook);
XLStyle.Font = new System.Drawing.Font("Calibri", 11, System.Drawing.FontStyle.Regular);
XLCellRange.Style = XLStyle;
//Auto Size Columns
_AutoSizeColumns(C1XLBook, XLSheet);
}
protected void _AutoSizeColumns(C1.C1Excel.C1XLBook C1XLBook, C1.C1Excel.XLSheet XLSheet)
{
System.Drawing.Graphics Graphics = System.Drawing.Graphics.FromHwnd(IntPtr.Zero);
using (Graphics)
{
int RowIndex, ColIndex;
for (ColIndex = 0; ColIndex < XLSheet.Columns.Count; ColIndex++)
{
int MaxWidth = -1;
for (RowIndex = 0; RowIndex < XLSheet.Rows.Count; RowIndex++)
{
object Value = XLSheet[RowIndex, ColIndex].Value;
if (Value != null)
{
// get value (unformatted at this point)
string Text = Value.ToString();
// format value if cell has a style with format set
C1.C1Excel.XLStyle XLStyle = XLSheet[RowIndex, ColIndex].Style;
if ((XLStyle != null) && (XLStyle.Format.Length > 0))
{
IFormattable IFormattable = Value as IFormattable;
if (IFormattable != null)
{
string fmt = C1.C1Excel.XLStyle.FormatXLToDotNet(XLStyle.Format);
Text = IFormattable.ToString(fmt, System.Globalization.CultureInfo.CurrentCulture);
}
}
// get font (default or style)
System.Drawing.Font Font = C1XLBook.DefaultFont;
if (XLStyle != null)
{
if (XLStyle.Font != null)
{ Font = XLStyle.Font; }
}
// measure string (add a little tolerance)
System.Drawing.Size Size = System.Drawing.Size.Ceiling(Graphics.MeasureString(Text + "XX", Font));
// keep widest so far
if (Size.Width > MaxWidth)
{ MaxWidth = Size.Width; }
}
}
if (MaxWidth > -1)
{ XLSheet.Columns[ColIndex].Width = C1.C1Excel.C1XLBook.PixelsToTwips(MaxWidth); }
}
}
}
}
}
No comments:
Post a Comment