public void ExportDataToExcelByGridCombine(string id)
{
try
{
DataSet dsZyjl = this.GetZyjlById(int.Parse(id));
if (dsZyjl.Tables.Count > 0 && dsZyjl.Tables[0].Rows.Count > 0)
{
DataRow drZyjl = dsZyjl.Tables[0].Rows[0];
string name = drZyjl["name"].ToString();
string ybhm = drZyjl["ylzh"].ToString();
//创建报表对象
Util.ReportShow reportShow = new Util.ReportShow("shenhe_old.grf");
reportShow.SetParameter("name", name);
reportShow.SetParameter("ybhm", ybhm);
//先计算 在再并导出
Business.MainList mainList = new Business.MainList();
bool result = mainList.ComputeFeeDetail(id);
if (result)
{
DataSet ds = this.CombineFeeDetail(id);
//甲类总费用
string classAFee = "";
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable dtCombineResult = ds.Tables[0];
//计算总费用
string zfy = dtCombineResult.Compute("sum(金额)", "").ToString();
reportShow.SetParameter("zfy", zfy);
//计算甲类费用
DataRow[] drClassA = dtCombineResult.Select("自付比=0 ");
if (drClassA.Length > 0)
{
object obj = dtCombineResult.Compute("sum(金额)", "自付比=0 ");
if (obj != DBNull.Value)
{
classAFee = obj.ToString();
}
reportShow.SetParameter("ClassAFee", classAFee);
}
//创建新表
DataSet dsClassB = new DataSet();
DataTable dtClassB = dtCombineResult.Clone();
dtClassB.Columns.Add("项目编码1");
dtClassB.Columns.Add("项目名称1");
dtClassB.Columns.Add("金额1");
dtClassB.Columns.Add("自付比1");
dtClassB.Columns.Add("自付金额1");
dtClassB.Columns.Add("超限价金额1");
int rowNum = 0;//新表的总行数
//计算乙类费用 10%
DataRow[] drClassB10Array = dtCombineResult.Select("自付比=0.1");
if (drClassB10Array.Length > 0)
{
//计算10%的总费用 自付总金额
string ClassB10Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.1 ").ToString();
string ClassB10SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.1 ").ToString();
reportShow.SetParameter("ClassB10Zfy", ClassB10Zfy);
reportShow.SetParameter("ClassB10SFeeTotal", ClassB10SFeeTotal);
for (int i = 1; i <= drClassB10Array.Length; i++)
{
//奇数
if (Util.Util.IsOdd(i))
{
dtClassB.Rows.Add(drClassB10Array[i - 1].ItemArray);
rowNum++;
}
else//偶数
{
dtClassB.Rows[rowNum - 1]["项目编码1"] = drClassB10Array[i - 1]["项目编码"];
dtClassB.Rows[rowNum - 1]["项目名称1"] = drClassB10Array[i - 1]["项目名称"];
dtClassB.Rows[rowNum - 1]["金额1"] = drClassB10Array[i - 1]["金额"];
dtClassB.Rows[rowNum - 1]["自付比1"] = drClassB10Array[i - 1]["自付比"];
dtClassB.Rows[rowNum - 1]["自付金额1"] = drClassB10Array[i - 1]["自付金额"];
dtClassB.Rows[rowNum - 1]["超限价金额1"] = drClassB10Array[i - 1]["超限价金额"];
}
}
}
//计算乙类费用 20%
DataRow[] drClassB20Array = dtCombineResult.Select("自付比=0.2");
if (drClassB20Array.Length > 0)
{
//计算20%的总费用 自付总金额
string ClassB20Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.2 ").ToString();
string ClassB20SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.2 ").ToString();
reportShow.SetParameter("ClassB20Zfy", ClassB20Zfy);
reportShow.SetParameter("ClassB20SFeeTotal", ClassB20SFeeTotal);
for (int i = 1; i <= drClassB20Array.Length; i++)
{
//奇数
if (Util.Util.IsOdd(i))
{
dtClassB.Rows.Add(drClassB20Array[i - 1].ItemArray);
rowNum++;
}
else//偶数
{
dtClassB.Rows[rowNum - 1]["项目编码1"] = drClassB20Array[i - 1]["项目编码"];
dtClassB.Rows[rowNum - 1]["项目名称1"] = drClassB20Array[i - 1]["项目名称"];
dtClassB.Rows[rowNum - 1]["金额1"] = drClassB20Array[i - 1]["金额"];
dtClassB.Rows[rowNum - 1]["自付比1"] = drClassB20Array[i - 1]["自付比"];
dtClassB.Rows[rowNum - 1]["自付金额1"] = drClassB20Array[i - 1]["自付金额"];
dtClassB.Rows[rowNum - 1]["超限价金额1"] = drClassB20Array[i - 1]["超限价金额"];
}
}
}
//计算乙类费用 30%
DataRow[] drClassB30Array = dtCombineResult.Select("自付比=0.3");
if (drClassB30Array.Length > 0)
{
//计算30%的总费用 自付总金额
string ClassB30Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.3 ").ToString();
string ClassB30SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.3 ").ToString();
reportShow.SetParameter("ClassB30Zfy", ClassB30Zfy);
reportShow.SetParameter("ClassB30SFeeTotal", ClassB30SFeeTotal);
for (int i = 1; i <= drClassB30Array.Length; i++)
{
//奇数
if (Util.Util.IsOdd(i))
{
dtClassB.Rows.Add(drClassB30Array[i - 1].ItemArray);
rowNum++;
}
else//偶数
{
dtClassB.Rows[rowNum - 1]["项目编码1"] = drClassB30Array[i - 1]["项目编码"];
dtClassB.Rows[rowNum - 1]["项目名称1"] = drClassB30Array[i - 1]["项目名称"];
dtClassB.Rows[rowNum - 1]["金额1"] = drClassB30Array[i - 1]["金额"];
dtClassB.Rows[rowNum - 1]["自付比1"] = drClassB30Array[i - 1]["自付比"];
dtClassB.Rows[rowNum - 1]["自付金额1"] = drClassB30Array[i - 1]["自付金额"];
dtClassB.Rows[rowNum - 1]["超限价金额1"] = drClassB30Array[i - 1]["超限价金额"];
}
}
}
//计算乙类费用 40%
DataRow[] drClassB40Array = dtCombineResult.Select("自付比=0.4");
if (drClassB40Array.Length > 0)
{
//计算40%的总费用 自付总金额
string ClassB40Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.4 ").ToString();
string ClassB40SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.4 ").ToString();
reportShow.SetParameter("ClassB40Zfy", ClassB40Zfy);
reportShow.SetParameter("ClassB40SFeeTotal", ClassB40SFeeTotal);
for (int i = 1; i <= drClassB40Array.Length; i++)
{
//奇数
if (Util.Util.IsOdd(i))
{
dtClassB.Rows.Add(drClassB40Array[i - 1].ItemArray);
rowNum++;
}
else//偶数
{
dtClassB.Rows[rowNum - 1]["项目编码1"] = drClassB40Array[i - 1]["项目编码"];
dtClassB.Rows[rowNum - 1]["项目名称1"] = drClassB40Array[i - 1]["项目名称"];
dtClassB.Rows[rowNum - 1]["金额1"] = drClassB40Array[i - 1]["金额"];
dtClassB.Rows[rowNum - 1]["自付比1"] = drClassB40Array[i - 1]["自付比"];
dtClassB.Rows[rowNum - 1]["自付金额1"] = drClassB40Array[i - 1]["自付金额"];
dtClassB.Rows[rowNum - 1]["超限价金额1"] = drClassB40Array[i - 1]["超限价金额"];
}
}
}
//创建新自费表
DataSet dsClassC = new DataSet();
DataTable dtClassC = dtCombineResult.Clone();
dtClassC.Columns.Add("项目编码1");
dtClassC.Columns.Add("项目名称1");
dtClassC.Columns.Add("金额1");
dtClassC.Columns.Add("自付比1");
dtClassC.Columns.Add("自付金额1");
dtClassC.Columns.Add("超限价金额1");
dtClassC.Columns.Add("项目编码2");
dtClassC.Columns.Add("项目名称2");
dtClassC.Columns.Add("金额2");
dtClassC.Columns.Add("自付比2");
dtClassC.Columns.Add("自付金额2");
dtClassC.Columns.Add("超限价金额2");
int rowNumSelf = 0;//新表的总行数
//计算全自付费用
DataRow[] drSelfFeeArray = dtCombineResult.Select("自付比=1 or (自付比>0 and 自付比<1 and 超限价金额>0)");
if (drSelfFeeArray.Length > 0)
{
//如果 自付比=1 全自费
//如果 自付比>0 and 自付比<1 and 超限价金额>0 超限价
for (int i = 1; i <= drSelfFeeArray.Length; i++)
{
string nameOver = "";
string nameAllSelf = "";
if (drSelfFeeArray[i - 1]["自付比"].ToString() == "1")
{
nameAllSelf = "(全自费)";
}
if (drSelfFeeArray[i - 1]["自付比"].ToString() != "1" && drSelfFeeArray[i - 1]["自付比"].ToString() != "0" && drSelfFeeArray[i - 1]["超限价金额"].ToString() != "0")
{
nameOver = "(超)";
}
//第一列
if (i % 3 == 1)
{
object[] objArray = new object[] {
drSelfFeeArray[i - 1]["项目编码"], drSelfFeeArray[i - 1]["项目名称"] + nameOver + nameAllSelf, drSelfFeeArray[i - 1]["金额"], drSelfFeeArray[i - 1]["自付比"], drSelfFeeArray[i - 1]["自付金额"], drSelfFeeArray[i - 1]["超限价金额"]
};
// dtClassC.Rows.Add(drSelfFeeArray[i - 1].ItemArray);
dtClassC.Rows.Add(objArray);
rowNumSelf++;
}
else if (i % 3 == 2)//第二列
{
dtClassC.Rows[rowNumSelf - 1]["项目编码1"] = drSelfFeeArray[i - 1]["项目编码"];
dtClassC.Rows[rowNumSelf - 1]["项目名称1"] = drSelfFeeArray[i - 1]["项目名称"] + nameOver + nameAllSelf;
dtClassC.Rows[rowNumSelf - 1]["金额1"] = drSelfFeeArray[i - 1]["金额"];
dtClassC.Rows[rowNumSelf - 1]["自付比1"] = drSelfFeeArray[i - 1]["自付比"];
dtClassC.Rows[rowNumSelf - 1]["自付金额1"] = drSelfFeeArray[i - 1]["自付金额"];
dtClassC.Rows[rowNumSelf - 1]["超限价金额1"] = drSelfFeeArray[i - 1]["超限价金额"];
}
else//第三列
{
dtClassC.Rows[rowNumSelf - 1]["项目编码2"] = drSelfFeeArray[i - 1]["项目编码"];
dtClassC.Rows[rowNumSelf - 1]["项目名称2"] = drSelfFeeArray[i - 1]["项目名称"] + nameOver + nameAllSelf;
dtClassC.Rows[rowNumSelf - 1]["金额2"] = drSelfFeeArray[i - 1]["金额"];
dtClassC.Rows[rowNumSelf - 1]["自付比2"] = drSelfFeeArray[i - 1]["自付比"];
dtClassC.Rows[rowNumSelf - 1]["自付金额2"] = drSelfFeeArray[i - 1]["自付金额"];
dtClassC.Rows[rowNumSelf - 1]["超限价金额2"] = drSelfFeeArray[i - 1]["超限价金额"];
}
}
}
//Util.Util.WriteExcel(dtClassB, "d:/test.xls");
dsClassB.Tables.Add(dtClassB);
dsClassC.Tables.Add(dtClassC);
reportShow.Report.LoadDataFromXML(dsClassB.GetXml());
grproLib.GridppReport subReport = new grproLib.GridppReport();
reportShow.Report.ControlByName("SubReport").AsSubReport.Report = subReport;
subReport.LoadDataFromXML(dsClassC.GetXml());
reportShow.Show();
}
}
else
{
MessageBox.Show("计算费用明细失败！");
return;
}
}
else
{
throw new ApplicationException("未获取到该住院记录的信息!");
}
}
catch (Exception ex)
{
throw ex;
}
}
public void ExportDataToExcelByGrid(string id)
{
try
{
DataSet dsZyjl = this.GetZyjlById(int.Parse(id));
if (dsZyjl.Tables.Count > 0 && dsZyjl.Tables[0].Rows.Count > 0)
{
DataRow drZyjl = dsZyjl.Tables[0].Rows[0];
string name = drZyjl["name"].ToString();
string ybhm = drZyjl["ylzh"].ToString();
//创建报表对象
Util.ReportShow reportShow = new Util.ReportShow("shenhe.grf");
reportShow.SetParameter("name", name);
reportShow.SetParameter("ybhm", ybhm);
//先计算 在再并导出
Business.MainList mainList = new Business.MainList();
bool result = mainList.ComputeFeeDetail(id);
if (result)
{
DataSet ds = this.CombineFeeDetail(id);
//甲类总费用
string classAFee = "";
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
DataTable dtCombineResult = ds.Tables[0];
//计算总费用
string zfy = dtCombineResult.Compute("sum(金额)", "").ToString();
reportShow.SetParameter("zfy", zfy);
//计算甲类费用
DataRow[] drClassA = dtCombineResult.Select("自付比=0 ");
if (drClassA.Length > 0)
{
object obj = dtCombineResult.Compute("sum(金额)", "自付比=0 ");
if (obj != DBNull.Value)
{
classAFee = obj.ToString();
}
reportShow.SetParameter("ClassAFee", classAFee);
}
//计算乙类费用 10%
DataRow[] drClassB10Array = dtCombineResult.Select("自付比=0.1 ");
if (drClassB10Array.Length > 0)
{
//计算10%的总费用 自付总金额
string ClassB10Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.1 ").ToString();
string ClassB10SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.1 ").ToString();
reportShow.SetParameter("ClassB10Zfy", ClassB10Zfy);
reportShow.SetParameter("ClassB10SFeeTotal", ClassB10SFeeTotal);
DataTable dtClassB10 = dtCombineResult.Clone();
foreach (DataRow drClassB10 in drClassB10Array)
{
dtClassB10.Rows.Add(drClassB10.ItemArray);
}
for (int i = 0; i < dtClassB10.Rows.Count; i++)
{
for (int j = 0; j < dtClassB10.Columns.Count; j++)
{
switch (dtClassB10.Columns[j].ColumnName)
{
case "项目名称":
reportShow.SetParameter("B10Name" + (i + 1), dtClassB10.Rows[i]["项目名称"].ToString());
break;
case "金额":
reportShow.SetParameter("B10Fee" + (i + 1), dtClassB10.Rows[i]["金额"].ToString());
break;
case "自付金额":
reportShow.SetParameter("B10SFee" + (i + 1), dtClassB10.Rows[i]["自付金额"].ToString());
break;
}
}
}
}
//计算乙类费用 20%
DataRow[] drClassB20Array = dtCombineResult.Select("自付比=0.2 ");
if (drClassB20Array.Length > 0)
{
//计算20%的总费用 自付总金额
string ClassB20Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.2 ").ToString();
string ClassB20SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.2 ").ToString();
reportShow.SetParameter("ClassB20Zfy", ClassB20Zfy);
reportShow.SetParameter("ClassB20SFeeTotal", ClassB20SFeeTotal);
DataTable dtClassB20 = dtCombineResult.Clone();
foreach (DataRow drClassB20 in drClassB20Array)
{
dtClassB20.Rows.Add(drClassB20.ItemArray);
}
for (int i = 0; i < dtClassB20.Rows.Count; i++)
{
for (int j = 0; j < dtClassB20.Columns.Count; j++)
{
switch (dtClassB20.Columns[j].ColumnName)
{
case "项目名称":
reportShow.SetParameter("B20Name" + (i + 1), dtClassB20.Rows[i]["项目名称"].ToString());
break;
case "金额":
reportShow.SetParameter("B20Fee" + (i + 1), dtClassB20.Rows[i]["金额"].ToString());
break;
case "自付金额":
reportShow.SetParameter("B20SFee" + (i + 1), dtClassB20.Rows[i]["自付金额"].ToString());
break;
}
}
}
}
//计算乙类费用 30%
DataRow[] drClassB30Array = dtCombineResult.Select("自付比=0.3 ");
if (drClassB30Array.Length > 0)
{
//计算30%的总费用 自付总金额
string ClassB30Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.3 ").ToString();
string ClassB30SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.3 ").ToString();
reportShow.SetParameter("ClassB30Zfy", ClassB30Zfy);
reportShow.SetParameter("ClassB30SFeeTotal", ClassB30SFeeTotal);
DataTable dtClassB30 = dtCombineResult.Clone();
foreach (DataRow drClassB30 in drClassB30Array)
{
dtClassB30.Rows.Add(drClassB30.ItemArray);
}
for (int i = 0; i < dtClassB30.Rows.Count; i++)
{
for (int j = 0; j < dtClassB30.Columns.Count; j++)
{
switch (dtClassB30.Columns[j].ColumnName)
{
case "项目名称":
reportShow.SetParameter("B30Name" + (i + 1), dtClassB30.Rows[i]["项目名称"].ToString());
break;
case "金额":
reportShow.SetParameter("B30Fee" + (i + 1), dtClassB30.Rows[i]["金额"].ToString());
break;
case "自付金额":
reportShow.SetParameter("B30SFee" + (i + 1), dtClassB30.Rows[i]["自付金额"].ToString());
break;
}
}
}
}
//计算乙类费用 40%
DataRow[] drClassB40Array = dtCombineResult.Select("自付比=0.4 ");
if (drClassB40Array.Length > 0)
{
//计算40%的总费用 自付总金额
string ClassB40Zfy = dtCombineResult.Compute("sum(金额)", "自付比=0.4 ").ToString();
string ClassB40SFeeTotal = dtCombineResult.Compute("sum(自付金额)", "自付比=0.4 ").ToString();
reportShow.SetParameter("ClassB40Zfy", ClassB40Zfy);
reportShow.SetParameter("ClassB40SFeeTotal", ClassB40SFeeTotal);
DataTable dtClassB40 = dtCombineResult.Clone();
foreach (DataRow drClassB40 in drClassB40Array)
{
dtClassB40.Rows.Add(drClassB40.ItemArray);
}
for (int i = 0; i < dtClassB40.Rows.Count; i++)
{
for (int j = 0; j < dtClassB40.Columns.Count; j++)
{
switch (dtClassB40.Columns[j].ColumnName)
{
case "项目名称":
reportShow.SetParameter("B40Name" + (i + 1), dtClassB40.Rows[i]["项目名称"].ToString());
break;
case "金额":
reportShow.SetParameter("B40Fee" + (i + 1), dtClassB40.Rows[i]["金额"].ToString());
break;
case "自付金额":
reportShow.SetParameter("B40SFee" + (i + 1), dtClassB40.Rows[i]["自付金额"].ToString());
break;
}
}
}
}
decimal SelfFeeTotal = 0;
//计算全自付费用
DataRow[] drSelfFee = dtCombineResult.Select("自付比=1");
if (drSelfFee.Length > 0)
{
object objSelfFee = dtCombineResult.Compute("sum(金额)", "自付比=1");
if (objSelfFee != DBNull.Value)
{
SelfFeeTotal += Decimal.Parse(objSelfFee.ToString());
}
DataTable dtClassC = dtCombineResult.Clone();
foreach (DataRow drC in drSelfFee)
{
dtClassC.Rows.Add(drC.ItemArray);
}
for (int i = 0; i < dtClassC.Rows.Count; i++)
{
for (int j = 0; j < dtClassC.Columns.Count; j++)
{
switch (dtClassC.Columns[j].ColumnName)
{
case "项目名称":
reportShow.SetParameter("CName" + (i + 1), dtClassC.Rows[i]["项目名称"].ToString());
break;
case "金额":
reportShow.SetParameter("CFee" + (i + 1), dtClassC.Rows[i]["金额"].ToString());
break;
}
}
}
}
//计算自付比>0 <1 超限价部分
DataRow[] drOverFee = dtCombineResult.Select("自付比>0 and 自付比<1 and 超限价金额>0");
if (drOverFee.Length > 0)
{
object objOverFee = dtCombineResult.Compute("sum(金额)", "自付比>0 and 自付比<1 and 超限价金额>0");
if (objOverFee != DBNull.Value)
{
SelfFeeTotal += Decimal.Parse(objOverFee.ToString());
}
DataTable dtClassD = dtCombineResult.Clone();
foreach (DataRow drD in drOverFee)
{
dtClassD.Rows.Add(drD.ItemArray);
}
for (int i = 0; i < dtClassD.Rows.Count; i++)
{
for (int j = 0; j < dtClassD.Columns.Count; j++)
{
switch (dtClassD.Columns[j].ColumnName)
{
case "项目名称":
reportShow.SetParameter("CName" + (i + 1 + drSelfFee.Length), dtClassD.Rows[i]["项目名称"].ToString());
break;
case "金额":
reportShow.SetParameter("CFee" + (i + 1 + drSelfFee.Length), dtClassD.Rows[i]["金额"].ToString());
break;
}
}
}
}
if (SelfFeeTotal > 0)
{
reportShow.SetParameter("SelfFeeTotal", Convert.ToString(SelfFeeTotal));
}
reportShow.Show();
}
}
else
{
MessageBox.Show("计算费用明细失败！");
return;
}
}
else
{
throw new ApplicationException("未获取到该住院记录的信息!");
}
}
catch (Exception ex)
{
throw ex;
}
}
