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