public DataSet GetStuBackHomeCheckInfo(string getGrade,string getClass,string getName,string getNumber,
string getBegDate,string getEndDate,string getState)
{
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
try
{
return stuCheckInfoDataAccess.GetBackHomeCheckInfo(getGrade,getClass,getName,getNumber,
getBegDate,getEndDate,getState);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
}
public DataSet GetStuMorningCheckInfo(string getGrade,string getClass,string getName,string getNumber,
string getBegDate,string getEndDate,string getState)
{
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
try
{
this.ClearCellManualModify();
this.ClearCellManualInput();
DataSet dsMorningCheckInfo = stuCheckInfoDataAccess.GetMorningCheckInfo(getGrade,getClass,getName,
getNumber,getBegDate,getEndDate,getState);
for ( int modifyRow = 0;modifyRow < dsMorningCheckInfo.Tables[0].Rows.Count; modifyRow ++)
{
// 添加需要对修改作出标识的行号
if ( Convert.ToInt32(dsMorningCheckInfo.Tables[0].Rows[modifyRow][5]) == 1 ||
Convert.ToInt32(dsMorningCheckInfo.Tables[0].Rows[modifyRow][5]) == 3)
{
// 手工修改
// this.SetCellManualModify(modifyRow);
this.SetCellManualModify(dsMorningCheckInfo.Tables[0].Rows[modifyRow][1].ToString());
this.SetCellManualModify(dsMorningCheckInfo.Tables[0].Rows[modifyRow][3].ToString());
}
if ( Convert.ToInt32(dsMorningCheckInfo.Tables[0].Rows[modifyRow][5]) == 2 ||
Convert.ToInt32(dsMorningCheckInfo.Tables[0].Rows[modifyRow][5]) == 4)
{
// 手动输入
// this.SetCellManualInput(modifyRow);
this.SetCellManualInput(dsMorningCheckInfo.Tables[0].Rows[modifyRow][1].ToString());
this.SetCellManualInput(dsMorningCheckInfo.Tables[0].Rows[modifyRow][3].ToString());
}
}
return dsMorningCheckInfo;
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
}
//将学生每天出勤信息写入EXCEL
private void WriteExcelForDetail(DateTime getBegDate,DateTime getEndDate,DataSet dsStuCheckInfo_Detail,int nameRow,string getName)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[2 * nameRow + 9, 1], (Excel.Range)m_objSheet.Cells[2 * nameRow + 9, 1]);
m_objRange.Value = getName;
m_objRange.Font.Size = 11;
m_objRange = m_objSheet.get_Range("A"+(2*nameRow+9).ToString(),"AI"+(2*nameRow+10).ToString());
m_objRange.WrapText = true;
m_objRange.Borders.LineStyle = BorderStyle.FixedSingle;
for ( int dateColumn=5; dateColumn<=35; dateColumn++ )
{
bool hasWrittenExcel = false;
m_objRangeDate = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[5, dateColumn], (Excel.Range)m_objSheet.Cells[5, dateColumn]);
if ( getBegDate.Day == dateColumn-4 )
{
bool hasCheckInfo = new StudentCheckInfoDataAccess().HasCheckInfoOneDay(getBegDate);
if ( getBegDate.Day <= getEndDate.Day)
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn], (Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn]);
if ( dsStuCheckInfo_Detail.Tables[0].Rows.Count > 0 )
{
foreach ( DataRow detailRow in dsStuCheckInfo_Detail.Tables[0].Rows )
{
string dateIndexer = detailRow[1] is DBNull ? string.Empty : Convert.ToDateTime(detailRow[1]).ToString("yyyy-MM-dd");
if (getBegDate.Date.ToString("yyyy-MM-dd").Equals(dateIndexer))
{
if (dateIndexer.Length == 0)
{
m_objRange.Value = "×";
hasWrittenExcel = true;
break;
}
else
{
if ( detailRow[2].ToString().Equals("健康") )
{
m_objRange.Value = "√";
}
else if ( detailRow[2].ToString().Equals("观察") )
{
m_objRange.Value = "○";
}
else
{
m_objRange.Value = "□";
}
hasWrittenExcel = true;
break;
}
}
}
}
if ( !hasWrittenExcel )
{
m_objRange.Value = "×";
}
if ( getBegDate.Month == 5 || getBegDate.Month == 10 )
{
if ( getBegDate.Day >= 1 && getBegDate.Day <= 3 )
{
if ( getBegDate.Day == Convert.ToInt32(m_objRangeDate.Value) )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn], (Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn]);
if (!hasCheckInfo)
{
m_objRange.Value = "△";
getBegDate = getBegDate.AddDays(1);
continue;
}
}
}
}
if ( getBegDate.Month == 1 && getBegDate.Day == 1)
{
if ( getBegDate.Day == Convert.ToInt32(m_objRangeDate.Value) )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn], (Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn]);
if (!hasCheckInfo)
{
m_objRange.Value = "△";
getBegDate = getBegDate.AddDays(1);
continue;
}
}
}
if ( getBegDate.DayOfWeek.ToString().Equals("Saturday") || getBegDate.DayOfWeek.ToString().Equals("Sunday") )
{
if ( getBegDate.Day == Convert.ToInt32(m_objRangeDate.Value) )
{
m_objRange = m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn], (Excel.Range)m_objSheet.Cells[2 * nameRow + 9, dateColumn]);
if (!hasCheckInfo)
{
m_objRange.Value = "△";
getBegDate = getBegDate.AddDays(1);
continue;
}
}
}
}
getBegDate = getBegDate.AddDays(1);
}
}
}
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 DataSet GetClassName(string getName,string getNumber)
{
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
try
{
return stuCheckInfoDataAccess.GetClassName(getName,getNumber);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
}
public void UpdateState(string getNumber,string getEnterDate,string getState,string getCurRecTime,string getOrigState)
{
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
try
{
stuCheckInfoDataAccess.DoUpdateState(getNumber,getEnterDate,getState,getCurRecTime,getOrigState);
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
}
}
}
public DataSet GetStuCustomStatusList()
{
try
{
using ( StudentCheckInfoDataAccess stuCheckInfoDataAccess = new StudentCheckInfoDataAccess() )
{
return stuCheckInfoDataAccess.GetCustomStatusList();
}
}
catch(Exception e)
{
Util.WriteLog(e.Message,Util.EXCEPTION_LOG_TITLE);
return null;
}
}
