public DataSet GetFinanInfoHistoryInfo(string getGrade,string getClass,string getName,string getNumber,DateTime getDate)
{
using ( FinanInfoDataAccess finanInfoDataAccess = new FinanInfoDataAccess() )
{
try
{
return finanInfoDataAccess.GetFinanHistoryInfo(getGrade,getClass,getName,getNumber,getDate);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
}
public void FinanceStatPrint(DataTable data, string className, DateTime date, string savePath)
{
DataTable dtPresents = new FinanInfoDataAccess().GetStudentPresents(date, className);
if (dtPresents == null || dtPresents.Rows.Count == 0)
{
throw new Exception("没有要使用的数据！");
}
else
{
data.Columns.AddRange(new DataColumn[]{ new DataColumn("小计", Type.GetType("System.Double")),
new DataColumn("stuPresent", Type.GetType("System.String")),
new DataColumn("stuAbsent", Type.GetType("System.String"))});
if (dtPresents.Rows.Count != data.Rows.Count)
{
throw new Exception("检测到数据完整性错误，请尝试重新生成数据！");
}
else
{
for (int i = 0; i < dtPresents.Rows.Count; i++)
{
data.Rows[i]["小计"] = 0;
data.Rows[i]["stuPresent"] = dtPresents.Rows[i]["times"];
data.Rows[i]["stuAbsent"] = dtPresents.Rows[i]["times_abs"];
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath + @"report\FinanceStat.xls",
m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,
m_objOpt,m_objOpt,m_objOpt);
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(1);
object[,] objData = new object[data.Rows.Count + 1, data.Columns.Count + 4];
for(int row = 0; row < data.Rows.Count; row++)
{
objData[row, 0] = row + 1;
for (int column = 0; column < data.Columns.Count - 3; column++)
{
if (column <= 2)
{
objData[row, column + 1] = data.Rows[row][column];
}
else if(column == 3)
{
objData[row, column + 1] = data.Rows[row][data.Columns.Count - 2];
objData[row, column + 2] = data.Rows[row][data.Columns.Count - 1];
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column >= 4 && column < 7)
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 3] = temp;
objData[data.Rows.Count, column + 3] = temp + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
}
else if (column == 7)
{
double temp1 = Convert.ToDouble(data.Rows[row][column - 4]) +
Convert.ToDouble(data.Rows[row][column - 3]) + Convert.ToDouble(data.Rows[row][column - 2]) +
Convert.ToDouble(data.Rows[row][column - 1]);
objData[row, column + 3] = temp1;
double temp2 = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp2;
objData[data.Rows.Count, column + 3] = temp1 + (objData[data.Rows.Count, column + 3] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 3]));
objData[data.Rows.Count, column + 4] = temp2 + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
else
{
double temp = Convert.ToDouble(data.Rows[row][column]);
objData[row, column + 4] = temp;
objData[data.Rows.Count, column + 4] = temp + (objData[data.Rows.Count, column + 4] == null ? 0 :
Convert.ToDouble(objData[data.Rows.Count, column + 4]));
}
}
}
m_objRange = m_objSheet.get_Range("A6", m_objOpt);
m_objRange = m_objRange.get_Resize(data.Rows.Count + 1, data.Columns.Count + 1);
m_objRange.Value = objData;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10;
m_objRange = m_objSheet.get_Range("G3", m_objOpt);
m_objRange.Value = "各项费用";
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, 7], m_objSheet.Cells[4, data.Columns.Count]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 12;
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[3, data.Columns.Count + 1], m_objSheet.Cells[4, data.Columns.Count + 1]);
m_objRange.Merge(m_objOpt);
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
object[, ] objColumn = new object[1, data.Columns.Count - 5];
for (int column = 3; column < data.Columns.Count - 3; column++ )
{
if (column < 7)
{
objColumn[0, column - 3] = data.Columns[column].ColumnName;
}
if (column == 7)
{
objColumn[0, column - 3] = data.Columns[data.Columns.Count - 3].ColumnName;
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
else
{
objColumn[0, column - 2] = data.Columns[column].ColumnName;
}
}
m_objRange = m_objSheet.get_Range("G5", m_objOpt);
m_objRange = m_objRange.get_Resize(1, data.Columns.Count - 5);
m_objRange.Value = objColumn;
m_objRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = System.Windows.Forms.BorderStyle.FixedSingle;
m_objRange.Font.Size = 10.5;
for (int column = 5; column < data.Columns.Count - 3; column++)
{
m_objRange = m_objSheet.get_Range(m_objSheet.Cells[6, column + 2], m_objSheet.Cells[5 + data.Rows.Count, column + 2]);
object i = m_objRange.Calculate();
string s = "asf";
}
m_objBook.SaveAs(savePath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
}
public DataTable MakingFinanceStat(string templateName, string grade, string className, DateTime date,
out ArrayList list)
{
list = new ArrayList();
try
{
using (DataTable dtStudentPresents = new FinanInfoDataAccess().GetStudentPresents(date, className))
{
if (dtStudentPresents != null && dtStudentPresents.Rows.Count > 0)
{
DataTable dtFilterTemplateContents = FilterTemplateContents(templateName, grade, className);
DataTable dtBuilded = BuildingFinanceStat(dtFilterTemplateContents);
foreach (DataRow dr in dtStudentPresents.Rows)
{
DataRow newRow = dtBuilded.NewRow();
int index = 3;
foreach(DataRow drContents in dtFilterTemplateContents.Rows)
{
TemplateContentsRecordSet contents = new TemplateContentsRecordSet(drContents["name"].ToString(),
drContents["fullDays"].ToString(), drContents["fullDaysSpend"].ToString(),
drContents["halfDaysSpend"].ToString(), drContents["perDaySpend"].ToString(),
drContents["noSpendMonth"].ToString(), drContents["halfSpendMonth"].ToString(),
drContents["grade"].ToString(), drContents["class"].ToString());
newRow[0] = dr["学号"];
newRow[1] = dr["姓名"];
newRow[2] = dr["班级"];
if (contents.Grade == "不选择" ||
Regex.Match(contents.Grade + contents.ClassName, string.Format("^{2}{0}|{2}{1}$", className, "不选择", grade)).Success)
{
if (!contents.FullDays.Equals("-"))
{
if (!contents.FullDaysSpend.Equals("-"))
{
int fullDays = Convert.ToInt32(contents.FullDays);
int presentDays = Convert.ToInt32(dr["times"]);
if (presentDays >= fullDays)
{
newRow[index] = contents.FullDaysSpend;
}
else
{
if (!contents.HalfDaysSpend.Equals("-"))
{
newRow[index] = contents.HalfDaysSpend;
}
else
{
if (!contents.PerDaySpend.Equals("-"))
{
double perDaySpend = Convert.ToDouble(contents.PerDaySpend);
newRow[index] = Convert.ToInt32(dr["times"]) * perDaySpend;
}
else
{
newRow[index] = 0;
}
}
}
}
else
{
if (!contents.PerDaySpend.Equals("-"))
{
double perDaySpend = Convert.ToDouble(contents.PerDaySpend);
newRow[index] = Convert.ToInt32(dr["times"]) * perDaySpend;
}
else
{
newRow[index] = 0;
}
}
}
else
{
if (!contents.PerDaySpend.Equals("-"))
{
double perDaySpend = Convert.ToDouble(contents.PerDaySpend);
newRow[index] = Convert.ToInt32(dr["times"]) * perDaySpend;
}
else
{
newRow[index] = 0;
}
}
if (!contents.NoSpendMonth.Equals("-"))
{
string month = date.Month.ToString();
string pattern = string.Format(@"^{0}$", contents.NoSpendMonth.Replace(",", "|"));
if (Convert.ToInt32(dr["times"]) == 0)
{
newRow[index] = Regex.Match(month, pattern).Success ? 0 : newRow[index];
}
}
if (!contents.HalfSpendMonth.Equals("-"))
{
string month = date.Month.ToString();
string pattern = string.Format(@"^{0}$", contents.HalfSpendMonth.Replace(",", "|"));
newRow[index] = Regex.Match(month, pattern).Success ?
Convert.ToDouble(newRow[index]) / 2 : newRow[index];
}
}
else
{
if (!list.Contains(contents.ColumnName))
{
list.Add(contents.ColumnName);
}
newRow[index] = 0;
}
index++;
}
dtBuilded.Rows.Add(newRow);
}
return dtBuilded;
}
else
{
return null;
}
}
}
catch(Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
return null;
}
}
public DataTable FilterTemplateContents(string templateName, string grade, string className)
{
DataTable dtTemplateContents = new FinanInfoDataAccess().GetTemplateContents(templateName);
if (dtTemplateContents != null && dtTemplateContents.Rows.Count > 0)
{
DataTable dtReBuilding = dtTemplateContents.Clone();
foreach (DataRow dr in dtTemplateContents.Rows)
{
string name = dr["name"].ToString();
DataRow newRow = dtReBuilding.NewRow();
DataRow[] temp1 = dtTemplateContents.Select(string.Format("name='{0}'", name), "order asc");
if (temp1.Length > 1)
{
if (dtReBuilding.Select(string.Format("name='{0}'", name)).Length == 0)
{
DataRow[] temp2 = dtTemplateContents.Select(string.Format("name='{0}' AND grade='{1}' AND (class = " +
"'{2}' OR class = '不选择')", name, grade, className), "order asc");
if (temp2.Length == 1)
{
newRow.ItemArray = temp2[0].ItemArray;
dtReBuilding.Rows.Add(newRow);
}
else
{
DataRow[] temp3 = dtTemplateContents.Select(string.Format("name='{0}' AND grade='不选择'", name), "order asc");
if (temp3.Length == 1)
{
newRow.ItemArray = temp3[0].ItemArray;
dtReBuilding.Rows.Add(newRow);
}
else
{
newRow.ItemArray = dr.ItemArray;
dtReBuilding.Rows.Add(newRow);
}
}
}
}
else
{
newRow.ItemArray = dr.ItemArray;
dtReBuilding.Rows.Add(newRow);
}
}
return dtReBuilding;
}
else
{
return null;
}
}
public DataSet GetContiAbsForMess(string getGrade,string getClass,
string getName,string getNumber,DateTime balanceMonth,FinanInfo finanInfo)
{
using ( FinanInfoDataAccess finanInfoDataAccess = new FinanInfoDataAccess() )
{
try
{
DataSet dsFinanBuild = finanInfoDataAccess.SetFinanStuInfo(getGrade,getClass,getName,getNumber);
DataSet dsAdmRestoreDays = finanInfoDataAccess.SetContiAbsForMess(true,getGrade,getClass,
getName,getNumber,balanceMonth,finanInfo.GetMessRestoreDays);
DataSet dsMessRestoreDays = finanInfoDataAccess.SetContiAbsForMess(false,getGrade,getClass,
getName,getNumber,balanceMonth,finanInfo.GetMessRestoreDays);
DataColumn needHandDaysColumn = new DataColumn("info_needHandDays");
needHandDaysColumn.DataType = System.Type.GetType("System.Int32");
dsFinanBuild.Tables[0].Columns.Add(needHandDaysColumn);
DataColumn messStopDaysColumn = new DataColumn("info_messStopDays");
messStopDaysColumn.DataType = System.Type.GetType("System.Int32");
dsFinanBuild.Tables[0].Columns.Add(messStopDaysColumn);
dsFinanBuild.Tables[0].Columns.AddRange(new DataColumn[]{
new DataColumn("info_admCharge"),new DataColumn("info_messCharge"),
new DataColumn("info_nightCharge"),new DataColumn("info_commCharge"),
new DataColumn("info_milkCharge"),new DataColumn("info_extraCharge"),
new DataColumn("info_admRestoreCharge"),new DataColumn("info_messRestoreCharge"),
new DataColumn("info_currency"),new DataColumn("info_remark")});
for ( int finanRow = 0; finanRow < dsFinanBuild.Tables[0].Rows.Count; finanRow ++ )
{
//应交天数
dsFinanBuild.Tables[0].Rows[finanRow][3] = GetWorkDays(balanceMonth);
//停伙天数及退伙食费
dsFinanBuild.Tables[0].Rows[finanRow][4] = 0;
dsFinanBuild.Tables[0].Rows[finanRow][12] = "0.00￥";
foreach(DataRow messRow in dsMessRestoreDays.Tables[0].Rows)
{
if ( dsFinanBuild.Tables[0].Rows[finanRow][0].ToString().Equals(messRow[0].ToString()) )
{
dsFinanBuild.Tables[0].Rows[finanRow][4] = messRow[1];
dsFinanBuild.Tables[0].Rows[finanRow][12] = (finanInfo.GetMessCharge/GetWorkDays(balanceMonth)*
Convert.ToDouble(messRow[1])).ToString("0.00")+"￥";
getMessRestoreCharge = finanInfo.GetMessCharge/GetWorkDays(balanceMonth)*
Convert.ToDouble(messRow[1]);
break;
}
else
{
dsFinanBuild.Tables[0].Rows[finanRow][4] = 0;
dsFinanBuild.Tables[0].Rows[finanRow][12] = "0.00￥";
getMessRestoreCharge = 0;
}
}
//管理费
dsFinanBuild.Tables[0].Rows[finanRow][5] = finanInfo.GetAdmCharge.ToString("0.00")+"￥";
dsFinanBuild.Tables[0].Rows[finanRow][11] = "0.00￥";
foreach(DataRow admRow in dsAdmRestoreDays.Tables[0].Rows)
{
if ( dsFinanBuild.Tables[0].Rows[finanRow][0].ToString().Equals(admRow[0].ToString()))
{
if ( Convert.ToInt32(admRow[1]) >= finanInfo.GetAdmRestoreDays )
{
dsFinanBuild.Tables[0].Rows[finanRow][11] = (finanInfo.GetAdmCharge/2).ToString("0.00")+"￥";
getAdmRestoreCharge = finanInfo.GetAdmCharge/2;
break;
}
else
{
dsFinanBuild.Tables[0].Rows[finanRow][11] = "0.00￥";
getAdmRestoreCharge = 0;
}
}
// else
// {
// dsFinanBuild.Tables[0].Rows[finanRow][5] = finanInfo.GetAdmCharge.ToString("0.00")+"￥";
// dsFinanBuild.Tables[0].Rows[finanRow][11] = "0.00￥";
// getAdmRestoreCharge = 0;
// }
}
//伙食费
dsFinanBuild.Tables[0].Rows[finanRow][6] = finanInfo.GetMessCharge.ToString("0.00")+"￥";
//晚托费
dsFinanBuild.Tables[0].Rows[finanRow][7] = finanInfo.GetNightCharge.ToString("0.00")+"￥";
//代办费
dsFinanBuild.Tables[0].Rows[finanRow][8] = finanInfo.GetCommCharge.ToString("0.00")+"￥";
//牛奶费
dsFinanBuild.Tables[0].Rows[finanRow][9] = finanInfo.GetMilkCharge.ToString("0.00")+"￥";
//附加费
dsFinanBuild.Tables[0].Rows[finanRow][10] = finanInfo.GetExtraCharge.ToString("0.00")+"￥";
//实收金额
dsFinanBuild.Tables[0].Rows[finanRow][13] = (finanInfo.GetMessCharge + finanInfo.GetAdmCharge +
finanInfo.GetNightCharge + finanInfo.GetMilkCharge + finanInfo.GetCommCharge +finanInfo.GetExtraCharge -
getMessRestoreCharge - getAdmRestoreCharge).ToString("0.00")+"￥";
//备注
dsFinanBuild.Tables[0].Rows[finanRow][14] = finanInfo.GetRemark;
}
return dsFinanBuild;
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
}
public void DoDeleteFinanInfo(DateTime getDate)
{
using ( FinanInfoDataAccess finanInfoDataAccess = new FinanInfoDataAccess() )
{
try
{
finanInfoDataAccess.DeleteFinanInfo(getDate);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
}
}
public void DoInsertFinanInfo(DataSet dsFinanInfo,DateTime getDate,int getMessRestoreDays,int getAdmRestoreDays)
{
using ( FinanInfoDataAccess finanInfoDataAccess = new FinanInfoDataAccess() )
{
try
{
foreach(DataRow finanInfoRow in dsFinanInfo.Tables[0].Rows)
{
finanInfoDataAccess.InsertFinanInfo(finanInfoRow,getDate,getMessRestoreDays,getAdmRestoreDays);
}
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
}
}
public DataSet ModifyFinanInfo(FinanInfo finanInfo,DataSet getDS,string getNumber,DateTime balanceMonth)
{
using ( FinanInfoDataAccess finanInfoDataAccess = new FinanInfoDataAccess() )
{
try
{
DataSet dsModifyMessInfo = finanInfoDataAccess.SetContiAbsForMess(false,"","","",getNumber,
balanceMonth,finanInfo.GetMessRestoreDays);
DataSet dsModifyAdmInfo = finanInfoDataAccess.SetContiAbsForMess(true,"","","",getNumber,
balanceMonth,finanInfo.GetMessRestoreDays);
foreach(DataRow modifyRow in getDS.Tables[0].Rows)
{
if ( modifyRow[0].ToString().Equals(getNumber) )
{
modifyRow[4] = 0;
modifyRow[12] = "0.00￥";
if ( dsModifyMessInfo.Tables[0].Rows.Count != 0)
{
modifyRow[4] = dsModifyAdmInfo.Tables[0].Rows[0][1];
modifyRow[12] = (finanInfo.GetMessCharge/GetWorkDays(balanceMonth)*
Convert.ToDouble(dsModifyAdmInfo.Tables[0].Rows[0][1])).ToString("0.00")+"￥";
getModifyMessRestoreCharge = finanInfo.GetMessCharge/GetWorkDays(balanceMonth)*
Convert.ToDouble(dsModifyAdmInfo.Tables[0].Rows[0][1]);
}
modifyRow[5] = finanInfo.GetAdmCharge.ToString("0.00")+"￥";
modifyRow[11] = "0.00￥";
if ( dsModifyAdmInfo.Tables[0].Rows.Count !=0 )
{
if ( Convert.ToInt32(dsModifyAdmInfo.Tables[0].Rows[0][1]) >= finanInfo.GetAdmRestoreDays )
{
modifyRow[11] = (finanInfo.GetAdmCharge/2).ToString("0.00")+"￥";
getModifyAdmRestoreCharge = finanInfo.GetAdmCharge/2;
}
}
modifyRow[6] = finanInfo.GetMessCharge.ToString("0.00")+"￥";
modifyRow[7] = finanInfo.GetNightCharge.ToString("0.00")+"￥";
modifyRow[8] = finanInfo.GetCommCharge.ToString("0.00")+"￥";
modifyRow[9] = finanInfo.GetMilkCharge.ToString("0.00")+"￥";
modifyRow[10] = finanInfo.GetExtraCharge.ToString("0.00")+"￥";
modifyRow[13] = (finanInfo.GetMessCharge + finanInfo.GetAdmCharge +
finanInfo.GetNightCharge + finanInfo.GetMilkCharge + finanInfo.GetCommCharge +finanInfo.GetExtraCharge -
getModifyMessRestoreCharge - getModifyAdmRestoreCharge).ToString("0.00")+"￥";
modifyRow[14] = finanInfo.GetRemark;
}
}
return getDS;
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
}
public DataTable GetFinanceStatHistory(string templateName, string className, DateTime date)
{
try
{
using (DataTable dt = new FinanInfoDataAccess().GetFinanceStatHistory(templateName, className, date))
{
return dt;
}
}
catch(Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
return null;
}
}
public bool IsFinanceStatExisted(string templateName)
{
try
{
DataTable dt = new FinanInfoDataAccess().IsFinanceStatExisted(templateName);
if (dt != null && dt.Rows.Count > 0)
{
return Convert.ToInt32(dt.Rows[0][0]) == 1;
}
else
{
return false;
}
}
catch(Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
return false;
}
}
public DataTable GetTemplateContents(string templateName)
{
try
{
using (DataTable dtTemplateContents = new FinanInfoDataAccess().GetTemplateContents(templateName))
{
return dtTemplateContents;
}
}
catch(Exception ex)
{
Util.WriteLog(ex.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
