public void UpdateTeaOndutyRemarks(int teaOndutyID,string remarks)
{
using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
{
dutyInfoDA.UpdateTeaOndutyRemarks(teaOndutyID,remarks);
}
}
public DataSet GetTeaOutDetails(DateTime startTime,DateTime endTime,string getDept,string getDuty,string getName,string getNumber)
{
DataSet allTeaOutInfo = null;
using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
{
allTeaOutInfo = dutyInfoDA.GetTeaOutDetails(startTime,endTime,getDept,getDuty,getName,getNumber);
}
return allTeaOutInfo;
}
public DataSet GetAllTeaDutyInfo()
{
DataSet allTeaDutyInfo = null;
using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
{
allTeaDutyInfo = dutyInfoDA.GetAllTeaDutyInfo();
}
return allTeaDutyInfo;
}
public DataSet GetTeaDutyNormail(string getDept,string getDuty,string getName,string getNumber,DateTime getBegDate,DateTime getEndDate, int state)
{
DataSet teaDutyNormal = null;
using ( DutyInfoDA dutyInfoDA = new DutyInfoDA() )
{
teaDutyNormal = dutyInfoDA.GetTeaDutyNormal(getDept,getDuty,getName,getNumber,getBegDate,getEndDate, state);
}
return teaDutyNormal;
}
public DataSet GetDutyInfoList()
{
DataSet dutyInfoList = null;
using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
{
dutyInfoList = dutyInfoDA.GetDutyInfoList();
}
return dutyInfoList;
}
public int UpdateDutyInfoList(DataSet dutyInfoList)
{
int rowsAffected = 0;
using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
{
rowsAffected = dutyInfoDA.UpdateDutyInfo(dutyInfoList);
}
return rowsAffected;
}
public DataSet BuildDutySummary(DateTime getBegDate,DateTime getEndDate,string getDept,string getDuty,string getName,string getNumber)
{
using ( DutyInfoDA dutyInfo = new DutyInfoDA() )
{
DataSet buildDutySummary = dutyInfo.GetTeaListForDutySummary(getDept,getDuty,getName,getNumber);
if ( buildDutySummary.Tables[0].Rows.Count > 0 )
{
buildDutySummary.Tables[0].Columns.AddRange(new DataColumn[]{new DataColumn("Late"),new DataColumn("OffTime"),
new DataColumn("Absence"),new DataColumn("Attend"),
new DataColumn("ShouldAttend"),new DataColumn("Out")});
for ( int numberRow=0; numberRow<buildDutySummary.Tables[0].Rows.Count; numberRow++ )
{
int getShouldAttendTimes = 0;
int[] getValue = new DutyStatistics().TeaDutyStatistics(getBegDate,getEndDate,"","","",buildDutySummary.Tables[0].Rows[numberRow][1].ToString(),ref getShouldAttendTimes);
if ( getShouldAttendTimes != 0 )
{
buildDutySummary.Tables[0].Rows[numberRow]["Late"] = getValue[0].ToString() + " (" + ((float)getValue[0]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
buildDutySummary.Tables[0].Rows[numberRow]["OffTime"] = getValue[1].ToString() + "(" + ((float)getValue[1]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
buildDutySummary.Tables[0].Rows[numberRow]["Absence"] = getShouldAttendTimes-getValue[2] < 0 ? "0(0.00%)" : (getShouldAttendTimes-getValue[2]).ToString() + "(" + (1-(float)getValue[2]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
buildDutySummary.Tables[0].Rows[numberRow]["Attend"] = (float)getValue[2]/(float)getShouldAttendTimes > 1 ? getValue[2].ToString() + "(100%)" : getValue[2].ToString() + "(" + ((float)getValue[2]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
buildDutySummary.Tables[0].Rows[numberRow]["ShouldAttend"] = SetAttendDays(getBegDate, getEndDate);//不考虑多班次问题
buildDutySummary.Tables[0].Rows[numberRow]["Out"] = getValue[3];
}
else
{
buildDutySummary.Tables[0].Rows[numberRow]["Late"] = 0;
buildDutySummary.Tables[0].Rows[numberRow]["OffTime"] = 0;
buildDutySummary.Tables[0].Rows[numberRow]["Absence"] = 0;
buildDutySummary.Tables[0].Rows[numberRow]["Attend"] = 0;
buildDutySummary.Tables[0].Rows[numberRow]["ShouldAttend"] = 0;
buildDutySummary.Tables[0].Rows[numberRow]["Out"] = 0;
}
}
}
return buildDutySummary;
}
}
public int SaveCurDuty()
{
using ( DutyInfoDA dutyInfo = new DutyInfoDA() )
{
return dutyInfo.SaveCurDuty();
}
}
public int UpdateDutyInfo(DateTime getHisDutyDate,bool isToLoadCurDuty)
{
using ( DutyInfoDA dutyInfo = new DutyInfoDA() )
{
return dutyInfo.UpdateDutyInfo(getHisDutyDate,isToLoadCurDuty);
}
}
// public float[] TeaDutyReport(string tID,string startTime,string endTime)
// {
// float[] reportResult = new float[4];
//
// using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
// {
// reportResult = dutyInfoDA.TeaDutyReport(tID,startTime,endTime);
// }
//
// return reportResult;
// }
public int UpdateTeaDutyDetail(string tID,string sundayDuty,
string mondayDuty,string tuesdayDuty,string wednesdayDuty,
string thursdayDuty,string fridayDuty,string saturdayDuty)
{
int rowsAffected = 0;
using(DutyInfoDA dutyInfoDA = new DutyInfoDA())
{
rowsAffected = dutyInfoDA.UpdateTeaDutyDetail(tID,sundayDuty,
mondayDuty,tuesdayDuty,wednesdayDuty,thursdayDuty,fridayDuty,
saturdayDuty);
}
return rowsAffected;
}
public int[] TeaDutyStatistics(DateTime getBegDate,DateTime getEndDate,string getDept,string getDuty,string getName,string getNumber,ref int getShouldAttendTimes)
{
string[] getStatistics = new string[6];
DateTime firDateThisWeek;
DateTime today = DateTime.Now.Date;
// int getShouldAttendTimes = 0;
if ( today.DayOfWeek.ToString().Equals("Sunday") )
firDateThisWeek = today.AddDays(-6);
else
firDateThisWeek = today.AddDays(-(Convert.ToInt32(today.DayOfWeek.ToString("d"))-1));
using ( DutyInfoDA dutyInfo = new DutyInfoDA() )
{
getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(getBegDate.AddDays(7),getEndDate.AddDays(7),getDept,getDuty,getName,getNumber,false);
// if ( getBegDate == today )
// {
// getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(today,today,getDept,getDuty,getName,getNumber,true);
// }
//
// else if ( getEndDate == firDateThisWeek )
// {
// getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(getEndDate,getEndDate,getDept,getDuty,getName,getNumber,true);
// }
if ( getBegDate < firDateThisWeek && getEndDate <= today && getEndDate >= firDateThisWeek )
{
getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(firDateThisWeek,getEndDate,getDept,getDuty,getName,getNumber,true);
}
else if ( getBegDate >= firDateThisWeek && getEndDate <= today )
{
getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(getBegDate,getEndDate,getDept,getDuty,getName,getNumber,true);
}
else if ( getBegDate >= firDateThisWeek && getEndDate > today && getBegDate <= today)
{
getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(getBegDate,today,getDept,getDuty,getName,getNumber,true);
}
else if ( getBegDate < firDateThisWeek && getEndDate > today )
{
getShouldAttendTimes += dutyInfo.CalcTeaDutyInfo(firDateThisWeek,today,getDept,getDuty,getName,getNumber,true);
}
// if ( getShouldAttendTimes == 0 )
// {
// for ( int i=0; i<=5; i++ )
// getStatistics[i] = "0";
// }
// else
// {
int[] getValue = dutyInfo.TeaDutyReport(getBegDate,getEndDate,getDept,getDuty,getName,getNumber);
// getStatistics[0] = " " + getValue[0].ToString() + " (" + ((float)getValue[0]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
// getStatistics[1] = " " + getValue[1].ToString() + " (" + ((float)getValue[1]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
// getStatistics[2] = " " + (getShouldAttendTimes-getValue[2]).ToString() + " (" + (1-(float)getValue[2]/(float)getShouldAttendTimes).ToString("0.00%") + ")";
// getStatistics[3] = " " + getValue[2].ToString() + " (" + ((float)getValue[2]/(float)getShouldAttendTimes).ToString("0.00%") + " )";
// getStatistics[4] = "\t" + getShouldAttendTimes.ToString();
// getStatistics[5] = "\t" + getValue[3].ToString();
// }
return getValue;
}
}
private void ExportSingle(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
using (DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty, string.Empty, string.Empty, string.Empty, startDate, endDate, 100).Tables[0])
{
if (dt != null && dt.Rows.Count > 0)
{
DataView dv = dt.DefaultView;
using (DataTable dtBaseInfo = new TeacherBaseDataAccess().GetTcBaseInfo(string.Empty, string.Empty, string.Empty, string.Empty).Tables[0])
{
object[, ] objData = null;
foreach(DataRow dr in dtBaseInfo.Rows)
{
dv.RowFilter = "T_Number = " + dr[1].ToString();
objData = new object[dv.Count, 6];
if (dv.Count > 0)
{
for (int i = 0; i < dv.Count; i++)
{
objData[i,0] = dv[i][3].ToString();
objData[i,1] = dv[i][2].ToString();
objData[i,2] = dv[i][0].ToString();
objData[i,3] = dv[i][1].ToString();
objData[i,4] = dv[i][4].ToString();
objData[i,5] = dv[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Copy(Type.Missing, m_objSheet);
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(5));
m_objSheet.Name = objData[0, 0].ToString() + string.Format("({0})", objData[0, 1]);
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dv.Count, 6);
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_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dv.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dv.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(4));
m_objSheet.Delete();
}
}
}
private void ExportSingleStat(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
object[,] objData;
using(DataTable dt = new DutyInfoDA().GetTeacherStatSingle(startDate, endDate))
{
if (dt != null && dt.Rows.Count > 0 )
{
objData = new Object[dt.Rows.Count + 1,6];
int days = SetAttendDays(startDate, endDate);
for( int i=0; i<dt.Rows.Count; i++ )
{
double attendCount = Convert.ToInt32(dt.Rows[i][2]);
double shouldAttendCount = days;
double absenceCount = shouldAttendCount - attendCount;
objData[i, 0] = dt.Rows[i][0].ToString();
objData[i, 1] = dt.Rows[i][1].ToString();
objData[i, 2] = shouldAttendCount;
objData[i, 3] = attendCount;
objData[i, 4] = absenceCount < 0 ? 0 : absenceCount ;
objData[i, 5] = shouldAttendCount == 0 ? attendCount.ToString("0.00%") : (attendCount / shouldAttendCount).ToString("0.00%");
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(3));
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dt.Rows.Count,6);
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_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
private void ExportAllNormal(
DateTime startDate,
DateTime endDate,
Excel._Workbook m_objBook,
Excel.Sheets m_objSheets,
Excel._Worksheet m_objSheet,
Excel.Range m_objRange,
Excel.Font m_objFont,
System.Reflection.Missing m_objOpt)
{
object[,] objData;
using(DataTable dt = new DutyInfoDA().GetTeaDutyNormal(string.Empty,string.Empty,string.Empty,string.Empty,startDate,endDate,100).Tables[0])
{
if ( dt != null && dt.Rows.Count > 0 )
{
objData = new Object[dt.Rows.Count,6];
for( int i=0; i<dt.Rows.Count; i++ )
{
objData[i,0] = dt.Rows[i][3].ToString();
objData[i,1] = dt.Rows[i][2].ToString();
objData[i,2] = dt.Rows[i][0].ToString();
objData[i,3] = dt.Rows[i][1].ToString();
objData[i,4] = dt.Rows[i][4].ToString();
objData[i,5] = dt.Rows[i][5].ToString();
}
m_objSheets = (Excel.Sheets)m_objBook.Sheets;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objRange = m_objSheet.get_Range("A3",m_objOpt);
m_objRange = m_objRange.get_Resize(dt.Rows.Count,6);
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_objFont = m_objRange.Font;
m_objFont.Size = 9;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = "园所:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+4).ToString(),m_objOpt);
m_objRange.Value = new GardenInfoDataAccess().GetGardenInfo().Tables[0].Rows[0][1].ToString();
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = "统计开始日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+5).ToString(),m_objOpt);
m_objRange.Value = startDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("A"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = "统计结束日期:";
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
m_objRange = m_objSheet.get_Range("B"+(dt.Rows.Count+6).ToString(),m_objOpt);
m_objRange.Value = endDate.ToString("yyyy.MM.dd");
m_objRange.Font.Bold = true;
m_objRange.Font.Size = 9;
m_objRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
}
}
}
