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