public int UpdateClassInfo(Classes classes)
{
int rowsAffected = 0;
using(ClassesDataAccess classDataAccess = new ClassesDataAccess())
{
rowsAffected = classDataAccess.UpdateClassInfo(classes);
}
return rowsAffected;
}
public int DeleteClassInfo(int classID,int gradeID)
{
int rowsAffected = 0;
using(ClassesDataAccess classesDataAccess = new ClassesDataAccess())
{
rowsAffected = classesDataAccess.DeleteClassInfo(classID,gradeID);
}
return rowsAffected;
}
public DataSet GetClassInfo(int classID,int gradeID)
{
DataSet classInfo;
using (ClassesDataAccess classDataAccess = new ClassesDataAccess())
{
classInfo = classDataAccess.GetClassInfo(classID,gradeID);
}
return classInfo;
}
public int InsertClassInfo(Classes classes,string gradeName)
{
int rowsAffected = 0;
using(ClassesDataAccess classDataAccess = new ClassesDataAccess())
{
rowsAffected = classDataAccess.InsertClassInfo(classes,gradeName);
}
return rowsAffected;
}
public DataSet GetClassInfoList()
{
DataSet classInfoList;
using (ClassesDataAccess classDataAccess = new ClassesDataAccess())
{
classInfoList = classDataAccess.GetClassInfoList();
}
return classInfoList;
}
public DataTable GetGetClassAndGrade()
{
DataTable dt = null;
try
{
dt = new ClassesDataAccess().GetClassAndGrade();
}
catch (Exception ex)
{
Util.WriteLog(ex.Message, Util.EXCEPTION_LOG_TITLE);
}
return dt;
}
public void PrintKidDetailInfo(string getGrade,string getClass,string getName,string getNumber,string savePath)
{
try
{
DataSet dsStuList_Stat = null;
DateTime getBegDate = BegDate;
DateTime getEndDate = EndDate;
int mbTwoDates = (getEndDate.Year-getBegDate.Year)*12 + (getEndDate.Month-getBegDate.Month);
int monthes = (getEndDate.Year-getBegDate.Year)*12 + (getEndDate.Month-getBegDate.Month);
string classInfo = string.Empty;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
if ( getName.Equals("") && getNumber.Equals("") )
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_GradeClass(getGrade,getClass);
classInfo = new ClassesDataAccess().GetClassInfo(Convert.ToInt32(getClass),Convert.ToInt32(getGrade)).Tables[0].Rows[0][1].ToString();
}
else
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_NameNumber(getName,getNumber);
classInfo = dsStuList_Stat.Tables[0].Rows[0][2].ToString();
}
}
if ( dsStuList_Stat.Tables[0].Rows.Count > 0 )
{
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfoKidDetail.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.Sheets;
for ( int i=0; i<=monthes; i++ )
{
GetExcelEndDate(getBegDate,ref getEndDate);
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(i+1));
if ( mbTwoDates >= 1 )
m_objSheet.Copy(m_objOpt,m_objSheet);
m_objRange = m_objSheet.get_Range("AE2",m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange = m_objSheet.get_Range("AE3",m_objOpt);
m_objRange.Value = classInfo;
m_objRange = m_objSheet.get_Range("AE4",m_objOpt);
m_objRange.Value = "'" + getBegDate.ToString("yyyy.MM");
WriteMonth(getBegDate);
for ( int nameRow=0; nameRow<dsStuList_Stat.Tables[0].Rows.Count; nameRow++ )
{
DataSet dsStuCheckInfo_Detail = null;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
dsStuCheckInfo_Detail = stuCheckInfoDataAccess.GetStuCheckInfo_Detail(dsStuList_Stat.Tables[0].Rows[nameRow][0].ToString(),getBegDate,getEndDate);
WriteExcelForDetail(getBegDate,getEndDate,dsStuCheckInfo_Detail,nameRow,
dsStuList_Stat.Tables[0].Rows[nameRow][1].ToString());
}
}
mbTwoDates--;
getBegDate = getEndDate.AddDays(1);
getEndDate = EndDate;
}
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);
}
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void PrintKidStatInfo(string getGrade,string getClass,string getName,string getNumber,string savePath)
{
try
{
DataSet dsStuList_Stat = null;
object[,] objData = null;
DateTime getBegDate = BegDate;
DateTime getEndDate = EndDate;
string classInfo = string.Empty;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
if ( getName.Equals("") && getNumber.Equals("") )
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_GradeClass(getGrade,getClass);
classInfo = new ClassesDataAccess().GetClassInfo(Convert.ToInt32(getClass),Convert.ToInt32(getGrade)).Tables[0].Rows[0][1].ToString();
}
else
{
dsStuList_Stat = stuCheckInfoDataAccess.GetStuList_NameNumber(getName,getNumber);
classInfo = dsStuList_Stat.Tables[0].Rows[0][2].ToString();
}
}
if ( dsStuList_Stat.Tables[0].Rows.Count > 0 )
{
objData = new object[dsStuList_Stat.Tables[0].Rows.Count,8];
for ( int nameRow=0; nameRow<dsStuList_Stat.Tables[0].Rows.Count; nameRow++ )
{
DataSet dsStuCheckInfo_Stat = null;
int times = 0;
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
dsStuCheckInfo_Stat = stuCheckInfoDataAccess.GetStuCheckInfo_Stat(dsStuList_Stat.Tables[0].Rows[nameRow][0].ToString(),getBegDate,getEndDate,ref times);
objData[nameRow,0] = dsStuList_Stat.Tables[0].Rows[nameRow][1].ToString();
objData[nameRow,1] = 0;
objData[nameRow,2] = 0;
objData[nameRow,3] = 0;
objData[nameRow,6] = 0;
int total = SetAttendDays();
int health = 0;
int ill = 0;
int watch = 0;
int absence = 0;
DataRow[] drHealth = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=0");
if (drHealth.Length > 0)
{
health = Convert.ToInt32(drHealth[0]["stateCount"]);
}
DataRow[] drWatch = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=2");
if (drWatch.Length > 0)
{
watch = Convert.ToInt32(drWatch[0]["stateCount"]);
}
DataRow[] drIll = dsStuCheckInfo_Stat.Tables[0].Select("flow_stuFlowEnterState=3");
if (drIll.Length > 0)
{
ill = Convert.ToInt32(drIll[0]["stateCount"]);
}
absence = total - health - watch - ill;
objData[nameRow,1] = string.Format("{0} ({1})",
health, ((double)health/(double)total).ToString("0.00%"));
objData[nameRow,2] = string.Format("{0} ({1})",
watch, ((double)watch/(double)total).ToString("0.00%"));
objData[nameRow,3] = string.Format("{0} ({1})",
ill, ((double)ill/(double)total).ToString("0.00%"));
objData[nameRow,6] = string.Format("{0} ({1})",
absence, ((double)absence/(double)total).ToString("0.00%"));
// foreach ( DataRow stateRow in dsStuCheckInfo_Stat.Tables[0].Rows )
// {
//
//
//
// if ( Convert.ToInt32(stateRow[1]) == 0 )
// {
// objData[nameRow,1] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
//
// else if ( Convert.ToInt32(stateRow[1]) == 2 )
// {
// objData[nameRow,2] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
//
// else if ( Convert.ToInt32(stateRow[1]) == 3 )
// {
// objData[nameRow,3] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
//
// else if ( Convert.ToInt32(stateRow[1]) == -1 )
// {
//// int total = SetAttendDays();
//// int health =
////
//// objData[nameRow,6] = stateRow[0].ToString()
//// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
//// continue;
//
// objData[nameRow,6] = stateRow[0].ToString()
// +" ("+(Convert.ToDouble(stateRow[0])/(double)SetAttendDays()).ToString("0.00%")+")";
// continue;
// }
// //晨检状态错误
// else
// {
// Util.WriteLog("晨检状态错误！",Util.EXCEPTION_LOG_TITLE);
// break;
// }
// }
objData[nameRow,4] = SetAttendDays();
objData[nameRow,5] = times;
objData[nameRow,7] = (times/(double)SetAttendDays()).ToString("0.00%");
}
}
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)m_objBooks.Open(excelPath+@"report\MorningCheckInfoKidStat.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.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A4",m_objOpt);
m_objRange = m_objRange.get_Resize(dsStuList_Stat.Tables[0].Rows.Count,8);
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;
WriteJumpboard(dsStuList_Stat.Tables[0].Rows.Count,classInfo);
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);
}
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
finally
{
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBook = null;
m_objBooks = null;
m_objExcel = null;
GC.Collect();
KillProcess();
}
}
public void GenerateCheckReports(string gardenName, DateTime[] dates, Action notify)
{
var dt = new ClassesDataAccess().GetClassAndGrade();
var rows = new DataRow[dt.Rows.Count];
dt.Rows.CopyTo(rows, 0);
foreach (var date in dates)
{
KillProcess();
var dir = excelPath + @"report\成长记录报表\" + date.ToString("yyyy");
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
var checkInDays = new UtilDataAccess().GetAttendDays(date, date.AddMonths(1).AddDays(-1));
var hierarchy = new GrowUpReportDataAccess().GetGrowUpCheckReports(rows.Select(r => r["info_className"].ToString()).ToArray(), date);
GenerateCheckReportsPersonByPerson(gardenName, hierarchy, checkInDays, date, dir, notify);
GenerateCheckReportsClassByClass(gardenName, hierarchy, checkInDays, date, dir, notify);
GenerateCheckReportsGradeByGrade(gardenName, hierarchy, checkInDays, date, dir, notify);
}
}
