private DataTable MakeExportID(DataTable dtViewName)
{
//--- make query using all viewname , execute the query to get distinct ptn_pk and store them in table dtViewPtnPk
IExport ExportManager = (IExport)ObjectFactory.CreateInstance("BusinessProcess.Administration.BExport, BusinessProcess.Administration");
string theQuery = "";
int r = 0;
foreach (DataRow theDRV in dtViewName.Rows)
{
if (theDRV["Ptn_Pk"].ToString() == "T")
{
if (r == 0)
{
theQuery = "SELECT DISTINCT " + theDRV["ViewName"] + ".Ptn_Pk" + " FROM " + theDRV["ViewName"];
}
else
if (theQuery.ToString() != "")
{
theQuery = theQuery + " UNION " + " SELECT DISTINCT " + theDRV["ViewName"] + ".Ptn_Pk" + " FROM " + theDRV["ViewName"];
}
else
{
theQuery = " SELECT DISTINCT " + theDRV["ViewName"] + ".Ptn_Pk" + " FROM " + theDRV["ViewName"];
}
}
r = 1;
}
theQuery = theQuery + " ORDER BY Ptn_Pk";
DataTable dtViewPtnPk = new DataTable();
dtViewPtnPk = ExportManager.RunQuery(theQuery);
string Ptn_Pk_List = "";
foreach (DataRow theDR in dtViewPtnPk.Rows)
{
if (Ptn_Pk_List == "")
{
Ptn_Pk_List = Ptn_Pk_List + theDR["Ptn_Pk"].ToString();
}
else
{
Ptn_Pk_List = Ptn_Pk_List + "," + theDR["Ptn_Pk"].ToString();
}
}
dtViewPtnPk = null;
DataTable dtPtnPkExport = new DataTable();
if (Ptn_Pk_List != "")
{
dtPtnPkExport = ExportManager.MakeExportID(Ptn_Pk_List);
}
return(dtPtnPkExport);
}
private DataView ManipulateData(string theQuery)
{
#region "RUN QUERY AND GET ALL DATA"
IExport ExportManager = (IExport)ObjectFactory.CreateInstance("BusinessProcess.Administration.BExport, BusinessProcess.Administration");
DataTable dtToExport = ExportManager.RunQuery(theQuery);
#endregion
#region "GET UNIQUE EXPORTID AND STORE IN A TABLE - call MakeExportID"
DataTable dtExportId = new DataTable();//--to store ptnpk , export it
dtExportId = MakeExportID(dtViewName);
DataView theDV = new DataView(dtExportId);
foreach (DataRow theDR1 in dtToExport.Rows)
{
theDV.RowFilter = "Ptn_Pk=" + theDR1["Ptn_Pk"];
theDR1["ExportId"] = theDV[0][1]; //insert exportid in dtToExport
}
dtExportId = null;
#endregion
#region " REMOVE ALL ID COLUMNS "
string[] IdColNames = new string[50];
int j = 0;
foreach (DataColumn theCol in dtToExport.Columns)
{
if ((theCol.ColumnName.ToString().Trim().ToUpper() != "EXPORTID") &&
(theCol.ColumnName.ToString().Trim().ToUpper() != "APPOINTMENT PROVIDER") &&
(theCol.ColumnName.ToString().Trim().ToUpper() != "AIDSRELIEF FINANCED LAB") &&
(theCol.ColumnName.ToString().Trim().ToUpper() != "AIDSRELIEF FINANCED DRUG"))
{
if ((theCol.ColumnName.ToString().ToUpper().IndexOf("ID") >= 0) ||
(theCol.ColumnName.ToString().ToUpper().IndexOf("PTN_PK") >= 0))
{
IdColNames[j] = theCol.ColumnName;
j++;
}
}
}
for (int k = 0; k < j; k++)
{
if (IdColNames[k].ToString().Trim() == null)
{
break;
}
foreach (DataColumn theCol in dtToExport.Columns)
{
if (theCol.ColumnName.ToString().Trim() == IdColNames[k].ToString().Trim())
{
dtToExport.Columns.Remove(theCol);
break;
}
}
}
#endregion
#region "MONTHLY INCOME"
//IQCareUtils theUtils = new IQCareUtils();
//int r = 0;
//foreach (DataRow theDR in dtToExport.Rows)
//{
// int c = 0;
// foreach (DataColumn theCol in dtToExport.Columns)
// {
// if (theCol.ColumnName.ToString().Trim() == "Monthly Income")
// {
// if (dtToExport.Rows[r][c] == null)
// {
// dtToExport.Rows[r][c] = System.DBNull.Value;
// }
// else if (dtToExport.Rows[r][c].ToString() == "")
// {
// dtToExport.Rows[r][c] = System.DBNull.Value;
// }
// else if (Convert.ToInt32(dtToExport.Rows[r][c]) == 0)
// {
// dtToExport.Rows[r][c] = System.DBNull.Value;
// }
// }
// c++;
// }
// r++;
//}
#endregion
#region "CHANGE COLUMN NAME FROM EXPORTID TO PATIENTID"
foreach (DataColumn theDC in dtToExport.Columns)
{
if (theDC.ColumnName.ToString() == "ExportId")
{
theDC.ColumnName = "PatientId";
break;
}
}
#endregion
#region "REMOVE BLANK ROWS-1 & SORT"
dtToExport = RemoveBlankRow(dtToExport);
SortDataTable(dtToExport, "PatientId Asc");
#endregion
#region "MERGE DATA"
//DataTable dtToExport2 = new DataTable();
//foreach (DataColumn theDC in dtToExport.Columns)
//{
// dtToExport2.Columns.Add(theDC.ColumnName.ToString().Trim(), System.Type.GetType("System.String"));
//}
////--insert unique values
//int r = 0;
//foreach (DataRow theDR in dtToExport.Rows)
//{
// int c = 0;
// foreach (DataColumn theDC in dtToExport.Columns)
// {
// if (c == 0) //insert new row else update
// {
// DataRow theDR2 = dtToExport2.NewRow();
// theDR2[c] = "";
// dtToExport2.Rows.Add(theDR2);
// }
// if (r == 0)
// {
// dtToExport2.Rows[r][c] = theDR[c].ToString().Trim();
// }
// else
// {
// if (dtToExport.Rows[r][0].ToString().Trim() == dtToExport.Rows[r - 1][0].ToString().Trim())
// {
// if (dtToExport.Rows[r][c].ToString().Trim() != dtToExport.Rows[r - 1][c].ToString().Trim())
// dtToExport2.Rows[r][c] = theDR[c].ToString().Trim();
// }
// else
// {
// if (dtToExport.Rows[r][c].ToString().Trim().Length != 0)
// dtToExport2.Rows[r][c] = theDR[c].ToString().Trim();
// }
// }
// c++;
// }
// r++;
//}
//dtToExport = null;
#endregion
#region "REMOVE BALNK ROW-2"
DataView dvToExport2 = new DataView();
dvToExport2 = RemoveBlankRow2(dtToExport);
return(dvToExport2);
#endregion
}
private void ExportToExcel(string theQuery)
{
try
{
IExport ExportManager = (IExport)ObjectFactory.CreateInstance("BusinessProcess.Administration.BExport, BusinessProcess.Administration");
DataTable dtToExport = ExportManager.RunQuery(theQuery);
ExportManager = null;
//dg1.DataSource = dvToExport2;
dg1.DataSource = dtToExport;
dg1.DataBind();
dg1.HeaderStyle.Font.Bold = true;
dg1.GridLines = GridLines.Vertical;
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=Export.xls");
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
dg1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
//dg1.Visible = false;
#region "EXPORT TEST - Column wise"
/*
* //Excel.ApplicationClass theApp = new Excel.ApplicationClass();
*
* Excel._Application theApp = new Excel._Application();
* //Excel.Workbook theWorkBook = theApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
* Excel.Workbook theWorkBook = theApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
* //Excel.Worksheet theSheet = (Excel.Worksheet)theWorkBook.Worksheets[1];
* Excel.Worksheet theSheet = (Excel.Worksheet)theWorkBook.Worksheets[1];
*
* /////////////////// Include Header ////////////////////
*
* Excel.Range theRange = theSheet.get_Range(GetIndexLetter(0) + "1", GetIndexLetter(dg1.Columns.Count) + "1");
* for (int i = 0; i < dg1.Columns.Count; i++)
* {
* ((Excel.Range)theRange["1", GetIndexLetter(i)]).Value2 = dg1.Columns[i].HeaderText.ToString();
* ((Excel.Range)theRange["1", GetIndexLetter(i)]).ColumnWidth = 20;
* }
*
* // theSheet.SaveAs("c:\\Issues.xls", ".xls", "", "", false, false, false, "", "", true);
*
* theApp.Workbooks[1].Close(true, "c:\\Issues.xls", null);
* //theApp.Save("c:\\issue.xls");
* theSheet = null;
* theApp = null;
* theWorkBook = null;
*/
#endregion "EXPORT TEST - Column wise"
}
catch (Exception err)
{
MsgBuilder theBuilder = new MsgBuilder();
theBuilder.DataElements["MessageText"] = err.Message.ToString();
IQCareMsgBox.Show("#C1", theBuilder, this);
//IQCareMsgBox.Show("TooManyRec",this);
}
}