/// <summary>
/// Function to insert data in tblattendance
/// </summary>
public void AttendanceAdd(AttendanceInfo attendanceInfo)
{
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
MySqlCommand sccmd = new MySqlCommand("AttendanceAdd", sqlcon);
sccmd.CommandType = CommandType.StoredProcedure;
MySqlParameter sprmparam = new MySqlParameter();
sprmparam = sccmd.Parameters.Add("?Date", MySqlDbType.DateTime);
sprmparam.Value = attendanceInfo.Date;
sprmparam = sccmd.Parameters.Add("?StudentId", MySqlDbType.Int32);
sprmparam.Value = attendanceInfo.StudentId;
sprmparam = sccmd.Parameters.Add("?TermId", MySqlDbType.Int32);
sprmparam.Value = attendanceInfo.TermId;
sprmparam = sccmd.Parameters.Add("?ClassNo", MySqlDbType.Int32);
sprmparam.Value = attendanceInfo.ClassId;
sprmparam = sccmd.Parameters.Add("?Status", MySqlDbType.VarChar);
sprmparam.Value = attendanceInfo.Status;
sccmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(" attendance sql exception" + ex.Message);
}
finally
{
sqlcon.Close();
}
}
public List <HourlyRateMappingInfo> DetermineHoulyRate(AttendanceInfo info)
{
string query = @"
select * from HourlyRateMapping
where ClientCode = @ClientCode
and BU = @BU
and PositionGrade = @PositionGrade
and Gender = @Gender
and TimeSlot = @TimeSlot
and CHARINDEX(@DayOfWeek,[DayOfWeek]) > 0
and (Interval = 'D' or ((HoursFrom <= @WeekHours or HoursFrom is null) and (HoursTo >= @WeekHours or HoursTo is null)))
and (Type <> 'H' or exists (select 1 from Holiday where HolidayDate = @AttendanceDate) )
order by Rate desc
";
HourlyRateMappingInfo mappingCriteria = new HourlyRateMappingInfo();
mappingCriteria.ClientCode = info.Client;
mappingCriteria.BU = info.BU;
mappingCriteria.PositionGrade = info.PositionGrade;
mappingCriteria.Gender = info.Gender;
mappingCriteria.AttendanceDate = info.AttendanceDate;
mappingCriteria.TimeSlot = info.TimeSlot;
mappingCriteria.WeekHours = info.WeeklyHours;
mappingCriteria.DayOfWeek = ((int)info.AttendanceDate.Value.DayOfWeek).ToString();
List <HourlyRateMappingInfo> mapping = (List <HourlyRateMappingInfo>)db.Query <HourlyRateMappingInfo>(query, mappingCriteria, this.transaction);
return(mapping);
}
public void Update(AttendanceInfo info)
{
db.Open();
string query = " UPDATE [dbo].[Attendance] SET "
+ " [WorkerID] = @WorkerID "
+ ", [Client] = @Client "
+ ", [Store] = @Store "
+ ", [ClientStaffNo] = @ClientStaffNo "
+ ", [ClientStaffName] = @ClientStaffName "
+ ", [AttendanceDate] = @AttendanceDate "
+ ", [TimeIn] = @TimeIn "
+ ", [TimeOut] = @TimeOut "
+ ", [TotalHours] = @TotalHours "
+ ", [AdjustedHours] = @AdjustedHours "
+ ", [AdjustedOTHours] = @AdjustedOTHours "
+ ", [HourRate] = @HourRate "
+ ", [OTHourRate] = @OTHourRate "
+ ", [Amount] = @Amount "
+ ", [Remarks] = @Remarks "
+ " where AttendanceID = @AttendanceID ";
db.Execute(query, info);
db.Close();
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
AttendanceInfo att = new AttendanceInfo();
att.AtId = Convert.ToInt32(Request.QueryString["UID"]);
att.Name = txtNickName.Text;
att.Introduce = txtAddress.Text;
if (rblSex.SelectedValue == "加分")
{
att.Praise = Convert.ToInt32(TextpassWord.Text);
att.Punish = 0;
}
else
{
att.Punish = Convert.ToInt32(TextpassWord.Text);
att.Praise = 0;
}
if (BLL.AdminManager.upatttapyByID(att) == 1)
{
Response.Write("<script>alert('成功')</script>");
}
else
{
Response.Write("<script>alert('失败')</script>");
}
}
/// <summary>
/// 获取员工考勤信息
/// </summary>
/// <param name="adminId"></param>
/// <param name="curr_year"></param>
/// <param name="curr_month"></param>
/// <returns></returns>
public AttendanceInfo GetAttendanceInfo(int adminId, int curr_year, int curr_month)
{
var admin = _adminContract.Administrators.FirstOrDefault(x => !x.IsDeleted && x.IsEnabled && x.Id == adminId);
if (admin != null)
{
int days = DateTime.DaysInMonth(curr_year, curr_month);
var curr_startTime = DateTime.Parse(curr_year.ToString() + "-" + curr_month.ToString().PadLeft(2, '0') + "-01 0:00");
var curr_endTime = DateTime.Parse(curr_year.ToString() + "-" + curr_month.ToString().PadLeft(2, '0') + "-" + days.ToString() + " 23:59");
AttendanceInfo ad = new AttendanceInfo();
ad.NormalDays = _attendanceContract.Attendances.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId &&
x.AttendanceTime.Year == curr_year && x.AttendanceTime.Month == curr_month).Count();
ad.OvertimeDays = _overtimeContract.Overtimes.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId && x.VerifyType == 1 &&
x.StartTime >= curr_startTime && x.EndTime <= curr_endTime).ToList().Sum(x => x.OvertimeDays);
ad.LeaveDays = _leaveInfoContract.LeaveInfos.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId && x.VerifyType == 1 &&
x.StartTime >= curr_startTime && x.EndTime <= curr_endTime && x.LeaveMethod == 1).ToList().Sum(x => x.LeaveDays);
ad.FieldDays = _fieldContract.Fields.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId && x.VerifyType == 1 &&
x.StartTime >= curr_startTime && x.EndTime <= curr_endTime).ToList().Sum(x => x.FieldDays);
ad.LateCount = _attendanceContract.Attendances.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId &&
x.AttendanceTime.Year == curr_year && x.AttendanceTime.Month == curr_month && x.IsLate == -1).Count();
ad.LeaveEarlyCount = _attendanceContract.Attendances.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId &&
x.AttendanceTime.Year == curr_year && x.AttendanceTime.Month == curr_month && x.IsLeaveEarly == -1).Count();
ad.NoSignOutCount = _attendanceContract.Attendances.Where(x => !x.IsDeleted && x.IsEnabled && x.AdminId == adminId &&
x.AttendanceTime.Year == curr_year && x.AttendanceTime.Month == curr_month && x.IsNoSignOut == -1).Count();
ad.RealName = admin.Member == null ? "" : admin.Member.RealName;
return(ad);
}
else
{
return(null);
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
AttendanceInfo att = new AttendanceInfo();
att.Name = txtNickName.Text;
att.Introduce = txtAddress.Text;
if (rblSex.SelectedValue == "加分")
{
att.Praise = Convert.ToInt32(TextpassWord.Text);
att.Punish = 0;
}
else
{
att.Punish = Convert.ToInt32(TextpassWord.Text);
att.Praise = 0;
}
if (BLL.AdminManager.addATTTYPE(att) == 1)
{
Response.Write("<script>alert('成功')</script>");
txtNickName.Text = "";
txtAddress.Text = "";
TextpassWord.Text = "";
}
else
{
Response.Write("<script>alert('失败')</script>");
}
}
/// <summary>
/// 签到保存数据
/// </summary>
/// <param name="LoginName"></param>
/// <returns></returns>
public JsonResult UpAttendanceData(string LoginName)
{
using (var db = new SHBTONLINEContext())
{
//开启事务
using (var tran = db.Database.BeginTransaction())
{
try
{
var userinfo = db.userinfoes.Where(a => a.LoginName == LoginName).FirstOrDefault();
var queryAttend = db.AttendanceInfos.Where(p => p.AD_LoginName == LoginName).Where(p => p.AD_AttendTime.Value.Year == DateTime.Now.Year && p.AD_AttendTime.Value.Month == DateTime.Now.Month && p.AD_AttendTime.Value.Day == DateTime.Now.Day).ToList();
//说明未签到
if (queryAttend.Count() < 1)
{
//签到数据绑定。
AttendanceInfo model = new AttendanceInfo()
{
ID = Guid.NewGuid().ToString(),
AD_Id = userinfo.ID,
AD_LoginName = LoginName,
AD_AttendTime = DateTime.Now,
Create_Time = DateTime.Now
};
//var queryattendline=db.AttendanceInfos.
db.AttendanceInfos.Add(model);
//db.Entry(model).State= System.Data.Entity.EntityState.Added;
//签到给S币
//s币数据的计算[判断方法]建议改掉S币数据类型
userinfo.SCrrency = userinfo.SCrrency + 2;
db.userinfoes.Attach(userinfo);
db.Entry(userinfo).Property(x => x.SCrrency).IsModified = true;
db.SaveChanges();
tran.Commit();
ReturnJson r = new ReturnJson()
{
s = "ok", r = "签到完成，获得S币+2s！"
};
return(Json(r));
}
else
{
ReturnJson r = new ReturnJson()
{
s = "error", r = "已签到,想白嫖？"
};
return(Json(r));
}
}
catch (Exception e)
{
tran.Rollback();
ReturnJson r = new ReturnJson {
r = "error", s = "删除失败！" + e.Message
};
return(Json(r));
}
}
}
}
public async Task DeleteAttendenceAsync(AttendanceInfo monthinfo, CancellationToken cancellationToken = default(CancellationToken))
{
if (monthinfo == null)
{
throw new ArgumentNullException(nameof(monthinfo));
}
Context.Remove(monthinfo);
await Context.SaveChangesAsync(cancellationToken);
}
public bool IsExisted(AttendanceInfo info)
{
db.Open();
String query = "select count(*) from Attendance "
+ " where AttendanceID = @AttendanceID ";
var obj = (List <int>)db.Query <int>(query, info);
db.Close();
return(obj[0] > 0);
}
private async void NewAddAttendance()
{
//Get LunchTime
TimeSpan LunchTime;
using (var da = new DataAccess())
{
var lunchTime = da.First <AttendanceSettingModel>(true);
LunchTime = lunchTime.LunchTime;
}
if (LunchTime != null)
{
//Calculation
WorkingTime = outTime - inTime - LunchTime;
}
//Get Time Hour
string inTimeHours = string.Format("{0:D2}", inTime.Hours);
string outTimeHours = string.Format("{0:D2}", outTime.Hours);
//Get AM PM
string In_AM_PM = commanFuction.CheckTimeForAM_PM(inTimeHours);
string Out_AM_PM = commanFuction.CheckTimeForAM_PM(outTimeHours);
//Set String Time in DataBase Added
string InTimes = string.Format("{0:D2}:{1:D2}:{2:D2}", inTime.Hours, inTime.Minutes, 00) + " " + In_AM_PM;
string OutTimes = string.Format("{0:D2}:{1:D2}:{2:D2}", outTime.Hours, outTime.Minutes, 00) + " " + Out_AM_PM;
var attendance = new AttendanceInfo()
{
AttendanceDate = AttendanceDate.Date,
InTime = InTimes,
OutTime = OutTimes,
WorkingTime = WorkingTime,
Aph = "Present",
};
try
{
using (var da = new DataAccess())
{
da.Insert(attendance);
await dialogService.ShowMessage("Success", "Insert Data Successfully...!!!");
await navigationService.Back();
Clear();
}
}
catch (Exception ex)
{
await dialogService.ShowMessage("Error", ex.Message);
}
}
public string Update(AttendanceInfo info)
{
string error = "";
string query = @"
select count(*) Total from [dbo].[Attendance]
where WorkerID = @WorkerID
and Client = @Client
and BU = @BU
and AttendanceDate = @AttendanceDate
and TimeSlot = @TimeSlot
and IsPaid = 1";
var isPaidList = db.Query(query, info, this.transaction);
foreach (var item in isPaidList)
{
if (item.Total > 0)
{
error = string.Format("Can't update the paid attendance of {0} on {1:dd/MM/yyyy}", info.WorkerID, info.AttendanceDate);
}
break;
}
if (string.IsNullOrEmpty(error))
{
query = " UPDATE [dbo].[Attendance] SET "
+ " [WorkerID] = @WorkerID "
+ ", [Client] = @Client "
+ ", [BU] = @BU "
+ ", [ClientStaffNo] = @ClientStaffNo "
+ ", [ClientStaffName] = @ClientStaffName "
+ ", [AttendanceDate] = @AttendanceDate "
+ ", [TimeIn] = @TimeIn "
+ ", [TimeOut] = @TimeOut "
+ ", [Hours] = @Hours "
+ ", [OTHours] = @OTHours "
+ ", [AdjustedHours] = @AdjustedHours "
+ ", [AdjustedOTHours] = @AdjustedOTHours "
+ ", [HourRate] = @HourRate "
+ ", [OTHourRate] = @OTHourRate "
+ ", [Amount] = @Amount "
+ ", [Remarks] = @Remarks "
+ @" where WorkerID = @WorkerID
and Client = @Client
and BU = @BU
and AttendanceDate = @AttendanceDate
and TimeSlot = @TimeSlot";
db.Execute(query, info, this.transaction);
}
return(error);
}
public DismissionChecker(AttendanceInfo firstNode)
{
this.FirstAbsentNode = firstNode;
this.PreNode = firstNode;
this.AbsentDays = 0;
this.PreState = AttendanceState.Absent;
this.NextState = AttendanceState.Absent;
}
public void Reset(AttendanceInfo currNode)
{
this.AbsentDays = 0;
this.PreNode = currNode;
this.FirstAbsentNode = currNode;
this.PreState = AttendanceState.Absent;
this.NextState = AttendanceState.Absent;
}
/// <summary>
/// 计数方法(保存数据)
/// </summary>
/// <param name="Name"></param>
/// <returns></returns>
public JsonResult GetsacrifInfo(string Name)
{
using (var db = new SHBTONLINEContext())
{
using (var tran = db.Database.BeginTransaction())
{
try
{
///将登陆名确定下来
var loginname = SessionManager.Instance.UserInfoSession.LoginName;
///查询人员基础信息
var userinfo = db.userinfoes.Where(a => a.LoginName == loginname).FirstOrDefault();
AttendanceInfo model = new AttendanceInfo()
{
ID = Guid.NewGuid().ToString(),
AD_LoginName = loginname,
//因为公用一张表所以考勤时间记为空
AD_AttendTime = null,
sacrificeName = Name,
Create_Time = DateTime.Now
};
//签到表与上香表在同一张表中[上线没有签到时间AD_AttendTime的数值为null]
db.AttendanceInfos.Add(model);
//更新人员表上香次数
userinfo.sacrifNum = userinfo.sacrifNum + 1;
db.userinfoes.Attach(userinfo);
db.Entry(userinfo).Property(x => x.sacrifNum).IsModified = true;
db.SaveChanges();
tran.Commit();
var Count = 0;
//计数xk+wyh（被上香次数）
if (Name == "xk")
{
Count = db.AttendanceInfos.Where(p => p.sacrificeName == "xk").Count();
}
else
{
Count = db.AttendanceInfos.Where(p => p.sacrificeName == "wyh").Count();
}
//返回计算出来的数值（上香次数）
return(Json(Count));
}
catch (Exception e) {
tran.Rollback();
ReturnJson r = new ReturnJson {
r = "error", s = "出现问题！" + e.Message
};
return(Json(r));
}
}
}
}
public void Save(AttendanceInfo info)
{
if (this.IsExisted(info))
{
this.Update(info);
}
else
{
this.Insert(info);
}
}
public AddAttendance(AttendanceInfo details)
{
InitializeComponent();
navigationService = new NavigationService();
attendanceService = new AttendanceService();
BindingContext = new MainViewModel();
if (details != null)
{
model = details;
EditAttendance(model);
}
}
public void SetAttendeesToCompetitions(ISession session, List <Pupil> pupils)
{
var competitions = session.Query <AcademicCompetition>();
var resultTypes = session.Query <ResultType>().ToList();
var random = new Random();
foreach (var competition in competitions)
{
var count = random.Next(50, 100);
var ids = pupils.Select(x => x.Id).ToList();
for (int i = 0; i < count; i++)
{
var index = random.Next(ids.Count);
var id = ids[index];
if (competition.AttendanceInfo == null)
{
competition.AttendanceInfo = new List <AttendanceInfo>();
}
if (competition.Results == null)
{
competition.Results = new List <Result>();
}
var pupil = pupils.First(x => x.Id == id);
ids.Remove(id);
var info = new AttendanceInfo
{
Attendee = pupil,
Participated = true
};
var result = new Result()
{
Pupil = pupil,
Type = resultTypes[random.Next(resultTypes.Count)],
NumberOfPoints = random.Next(101)
};
competition.AttendanceInfo.Add(info);
competition.Results.Add(result);
}
using (var tx = session.BeginTransaction())
{
session.Update(competition);
tx.Commit();
}
}
}
public string TakeAttendance(AttendanceInfo info)
{
VotingReplyEntry votingReplyEntry = new VotingReplyEntry();
VotingReplyEntryInfo VotingReplyEntryList = votingReplyEntry.Get(info.ClientID, info.EventID, info.CreditorID);
//if (VotingReplyEntryList == null) return null;
////voting method checking
//if (VotingReplyEntryList != null && VotingReplyEntryList.VoteMethod != "2") return null;
db.Open();
try
{
if (!this.IsAttended(info.ClientID, info.CreditorID, info.EventID))
{
info.CreditorIDNo = VotingReplyEntryList.CreditorIDNo;
string query = "INSERT INTO [dbo].[Attendance] "
+ "([ClientID] "
+ ",[EventID] "
+ ",[CreditorID] "
+ ",[CreditorIDNo] "
+ ",[CreditAgent] "
+ ",[CreditAgentIDNo] "
+ ",[CreateDate] "
+ ",[CreateUser]) "
+ "VALUES "
+ "(@ClientID "
+ ",@EventID "
+ ",@CreditorID "
+ ",@CreditorIDNo "
+ ",@CreditAgent "
+ ",@CreditAgentIDNo "
+ ",@CreateDate "
+ ",@CreateUser) ";
db.Execute(query, info, transaction);
return("1");
}
else
{
return("2");
}
}
finally
{
db.Close();
}
// return VotingReplyEntryList;
}
public bool UpdateAttendance(AttendanceInfo model)
{
bool result = false;
if (model.Id != 0)
{
using (var da = new DataAccess())
{
da.Update(model);
}
result = true;
}
return(result);
}
private void EditAttendance(AttendanceInfo modelAttendance)
{
dpPopup.Date = modelAttendance.AttendanceDate;
string[] InTimeValues = model.InTime.Split(':');
string[] OutTimeValues = model.OutTime.Split(':');
TimeSpan InTime = new TimeSpan(Convert.ToInt32(InTimeValues[0]), Convert.ToInt32(InTimeValues[1]), 0);
IntpPopup.Time = InTime;
TimeSpan OutTime = new TimeSpan(Convert.ToInt32(OutTimeValues[0]), Convert.ToInt32(OutTimeValues[1]), 0);
OuttpPopup.Time = OutTime;
}
public static AnalysisOfDay GetAnalysisResultOfOneDay(List <AttendanceInfo> day_infos)
{
AnalysisOfDay analysis_of_day = new AnalysisOfDay();
AttendanceInfo first_info = day_infos.First();
AttendanceInfo last_info = day_infos.Last();
analysis_of_day.date = first_info.inout_time.Date;
analysis_of_day.week = Convert.ToInt32(first_info.inout_time.DayOfWeek);
analysis_of_day.card_id = first_info.card_id;
analysis_of_day.job_num = first_info.job_num;
analysis_of_day.emp_name = first_info.emp_name;
analysis_of_day.first_tm = first_info.inout_time;
analysis_of_day.last_tm = last_info.inout_time;
TimeSpan timespan = first_info.inout_time.TimeOfDay;
analysis_of_day.first_hr = timespan.Minutes % 60 > 30 ? timespan.Hours + 1 : timespan.Hours;
timespan = last_info.inout_time.TimeOfDay;
analysis_of_day.last_hr = timespan.Minutes % 60 > 30 ? timespan.Hours + 1 : timespan.Hours;
if (analysis_of_day.last_tm.Day > analysis_of_day.first_tm.Day)
{
analysis_of_day.last_hr += 24;
}
analysis_of_day.shift = analysisUtil.GetShift(analysis_of_day.first_hr);
FirstAndLastPunchCardAnalysisResult result = analysisUtil.GetFirstAndLastPunchCardAnalysis(analysis_of_day.first_tm, analysis_of_day.last_tm, analysis_of_day.first_hr, analysis_of_day.last_hr, analysis_of_day.shift);
analysis_of_day.punch_card_hour = result.punch_card_hour;
analysis_of_day.punch_card_time = result.punch_card_time;
analysis_of_day.punch_card_hour_compare = result.punch_card_hour_compare;
analysis_of_day.punch_card_minute_compare = result.punch_card_minute_compare;
analysis_of_day.perfect = result.perfect;
analysis_of_day.half_late = result.half_late;
analysis_of_day.half_late_tm = result.half_late_tm;
analysis_of_day.late = result.late;
analysis_of_day.late_tm = result.late_tm;
analysis_of_day.half_leave_early = result.half_leave_early;
analysis_of_day.half_leave_early_tm = result.half_leave_early_tm;
analysis_of_day.leave_early = result.leave_early;
analysis_of_day.leave_early_tm = result.leave_early_tm;
analysis_of_day.punch_card_count_total = day_infos.Count;
analysis_of_day.punch_card_count_valid = analysisUtil.GetValidPunchCardCount(day_infos);
return(analysis_of_day);
}
static void SetDismissionNodes(List <AttendanceInfo> unsualInfo, AttendanceInfo firstNode, AttendanceInfo currNode)
{
foreach (var nextInfo in unsualInfo)
{
if (nextInfo.Date.DayOfYear < firstNode.Date.DayOfYear)
{
continue;
}
nextInfo.State = AttendanceState.Dimission;
if (nextInfo == currNode)
{
return;
}
}
}
public bool IsExisted(AttendanceInfo info)
{
String query = @"
select count(*)
from Attendance
where WorkerID = @WorkerID
and Client = @Client
and BU = @BU
and AttendanceDate = @AttendanceDate
and TimeSlot = @TimeSlot
";
var obj = (List <int>)db.Query <int>(query, info, this.transaction);
return(obj[0] > 0);
}
public void Insert(AttendanceInfo info)
{
string query = "INSERT INTO [dbo].[Attendance] ( "
+ "[WorkerID] "
+ ",[Client] "
+ ",[TimeSlot] "
+ ",[BU] "
+ ",[ClientStaffNo] "
+ ",[ClientStaffName] "
+ ",[AttendanceDate] "
+ ",[TimeIn] "
+ ",[TimeOut] "
+ ",[Hours] "
+ ",[OTHours] "
+ ",[AdjustedHours] "
+ ",[AdjustedOTHours] "
+ ",[HourRate] "
+ ",[OTHourRate] "
+ ",[Amount] "
+ ",[Remarks] "
+ ") "
+ "VALUES ( "
+ "@WorkerID "
+ ",@Client "
+ ",@TimeSlot "
+ ",@BU "
+ ",@ClientStaffNo "
+ ",@ClientStaffName "
+ ",@AttendanceDate "
+ ",@TimeIn "
+ ",@TimeOut "
+ ",@Hours "
+ ",@OTHours "
+ ",@AdjustedHours "
+ ",@AdjustedOTHours "
+ ",@HourRate "
+ ",@OTHourRate "
+ ",@Amount "
+ ",@Remarks "
+ ") ";
db.Execute(query, info, this.transaction);
}
public void Insert(AttendanceInfo info)
{
db.Open();
string query = "INSERT INTO [dbo].[Attendance] ( [AttendanceID] "
+ ",[WorkerID] "
+ ",[Client] "
+ ",[Store] "
+ ",[ClientStaffNo] "
+ ",[ClientStaffName] "
+ ",[AttendanceDate] "
+ ",[TimeIn] "
+ ",[TimeOut] "
+ ",[TotalHours] "
+ ",[AdjustedHours] "
+ ",[AdjustedOTHours] "
+ ",[HourRate] "
+ ",[OTHourRate] "
+ ",[Amount] "
+ ",[Remarks] "
+ ") "
+ "VALUES ( @AttendanceID "
+ ",@WorkerID "
+ ",@Client "
+ ",@Store "
+ ",@ClientStaffNo "
+ ",@ClientStaffName "
+ ",@AttendanceDate "
+ ",@TimeIn "
+ ",@TimeOut "
+ ",@TotalHours "
+ ",@AdjustedHours "
+ ",@AdjustedOTHours "
+ ",@HourRate "
+ ",@OTHourRate "
+ ",@Amount "
+ ",@Remarks "
+ ") ";
db.Execute(query, info);
db.Close();
}
protected void btnAdd_Click(object sender, EventArgs e)
{
divaddout.Style.Add("display", "none");
addoutc.Style.Add("display", "none");
if (FileUploadExcel.HasFile)//判断FileUploadExcel是否为空
{
LabTips.Text = "";
string serverRoot = System.Web.HttpContext.Current.Request.PhysicalApplicationPath;
string strPath = serverRoot + "Update\\" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
FileUploadExcel.PostedFile.SaveAs(strPath);
string url = strPath;
//取得全部的上传文件路径
try
{
DataTable dataset = CreateDataSource(url);
if (dataset.Rows.Count > 2)
{
List <AttendanceInfo> list = new List <AttendanceInfo>();
foreach (DataRow dr in dataset.Rows)
{
AttendanceInfo ai = new AttendanceInfo();
ai.UserID = dr[0].ToString();
ai.FaceTime = Convert.ToDateTime(dr[2]);
list.Add(ai);
}
UserMa.UpdateAttanceInfo(list);
LabTips.Text = "文件保存成功！";
}
else
{
LabTips.Text = "此文件为空！";
}
}
catch
{
LabTips.Text = "不支持此文件！";
}
}
else
{
LabTips.Text = "请选择文件！";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string id = Request.QueryString["UID"];
AttendanceInfo user1 = BLL.AdminManager.SelectattInfoByid(id);
txtNickName.Text = user1.Name;
txtAddress.Text = user1.Introduce;
if (user1.Punish == 0)
{
rblSex.SelectedValue = "加分";
TextpassWord.Text = user1.Praise.ToString();
}
else
{
rblSex.SelectedValue = "减分";
TextpassWord.Text = user1.Punish.ToString();
}
}
}
//public List<AttendanceInfo> Import_Nike(Stream file, string clientCode, int BU)
//{
// List<AttendanceInfo> attendanceList = new List<AttendanceInfo>();
// List<AttendanceInfo> invalidAttendanceList = new List<AttendanceInfo>();
// Attendance attendanceObj = new Attendance();
// AttendanceInfo tempAttendance;
// XSSFWorkbook hssfwb = new XSSFWorkbook(file);
// ISheet sheet = hssfwb.GetSheet("Export to PDF");
// IRow currentRow;
// //ICell currentCell;
// string dateFormat = "MM/dd/yyyy";
// DateTime startDate = DateTime.MaxValue;
// /// string datetimeFormat = "yyyy-MM-dd HH:mm";
// string[] workerInfo;
// return attendanceList;
// }
public void Save(List <AttendanceEntry> attendanceEntries, string clientCode, int BU)
{
List <AttendanceInfo> attendanceList = new List <AttendanceInfo>();
List <AttendanceInfo> invalidAttendanceList = new List <AttendanceInfo>();
Attendance attendanceObj = new Attendance();
AttendanceInfo tempAttendance;
Worker workerObj = new Worker();
int row = 0;
DateTime startDate = DateTime.MinValue;
WorkerInfo tmpWorkerInfo = null;
foreach (var info in attendanceEntries)
{
++row;
tmpWorkerInfo = workerObj.CheckWorkerWithBU(clientCode, BU, info.WorkerID);
if (tmpWorkerInfo == null)
{
throw new Exception(string.Format("Worker in row {0} is not valid", row));
}
tempAttendance = new AttendanceInfo();
tempAttendance.WorkerID = info.WorkerID;
tempAttendance.Gender = tmpWorkerInfo.Gender;
tempAttendance.PositionGrade = tmpWorkerInfo.PositionGrade;
tempAttendance.Client = clientCode;
tempAttendance.BU = BU;
tempAttendance.AttendanceDate = info.AttendanceDate;
tempAttendance.TimeSlot = info.Timeslot;
tempAttendance.TimeIn = DateTime.ParseExact(info.AttendanceDate.ToString(GlobalSetting.DateFormat) + " " + info.TimeIn + ":00", GlobalSetting.DateTimeFormat, CultureInfo.InvariantCulture);
tempAttendance.TimeOut = DateTime.ParseExact(info.AttendanceDate.ToString(GlobalSetting.DateFormat) + " " + info.TimeOut + ":00", GlobalSetting.DateTimeFormat, CultureInfo.InvariantCulture);
tempAttendance.Hours = info.TotalHours;
startDate = startDate > tempAttendance.AttendanceDate.Value ? tempAttendance.AttendanceDate.Value : startDate;
attendanceList.Add(tempAttendance);
}
attendanceObj.BatchInsert(attendanceList);
}
/// <summary>
/// 将Excel内的数据转化为AttendanceInfo对象
/// </summary>
/// <param name="row">Excel行</param>
/// <param name="columnOrder">列名顺序列表</param>
/// <returns>AttendanceInfo对象</returns>
public static AttendanceInfo GetAttendanceFromExcel(IRow row, List <int> columnOrder)
{
AttendanceInfo attendance = new AttendanceInfo();
attendance.seq_num = int.Parse(row.GetCell(columnOrder[0]).ToString());
attendance.card_id = row.GetCell(columnOrder[1]).ToString();
attendance.job_num = row.GetCell(columnOrder[2]).ToString().Trim();
attendance.emp_name = row.GetCell(columnOrder[3]).ToString();
attendance.inout_time = Convert.ToDateTime(row.GetCell(columnOrder[4]).ToString().Substring(0, 19));
string place = row.GetCell(columnOrder[5]).ToString();
attendance.place = 0;
switch (place)
{
case "大门-进门":
attendance.place = 51;
break;
case "大门-出门":
attendance.place = 52;
break;
case "6F大门-进门":
attendance.place = 61;
break;
case "6F大门-出门":
attendance.place = 62;
break;
case "7F大门-进门":
attendance.place = 71;
break;
case "7F大门-出门":
attendance.place = 72;
break;
}
attendance.pass = row.GetCell(columnOrder[6]).ToString().Equals("1") ? true : false;
return(attendance);
}
public static List <AttendanceInfo> GetAllAttendanceList()
{
List <AttendanceInfo> AttendanceList = new List <AttendanceInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From attendance ;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
AttendanceInfo Attendance = new AttendanceInfo();
Attendance.id = msqlReader.GetString("id");
Attendance.atnddate = msqlReader.GetDateTime("atnddate");
Attendance.stuname = msqlReader.GetString("stuname");
Attendance.tchrname = msqlReader.GetString("tchrname");
Attendance.stuclass = msqlReader.GetString("stuclass");
Attendance.present = msqlReader.GetString("present");
AttendanceList.Add(Attendance);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return(AttendanceList);
}
//private string GetDateString(DateTime Date)
//{
// return Date.ToShortDateString();
//}
//private bool IsAllAttendance(AttendanceInfo Attendance)
//{
// if (Attendance.PeriodType.Equals("集會") && mMeetingList.Contains(Attendance.Absence))
// {
// mMeetingDict[Attendance.Absence]++;
// return true;
// }
// else if (Attendance.PeriodType.Equals("一般") && mGeneralList.Contains(Attendance.Absence))
// {
// mGeneralDict[Attendance.Absence]++;
// return true;
// }
// return false;
//}
private void CountAttendance(AttendanceInfo Attendance)
{
if (Attendance.Semester == SmartSchool.Customization.Data.SystemInformation.Semester && Attendance.SchoolYear == SmartSchool.Customization.Data.SystemInformation.SchoolYear)
{
if (Attendance.PeriodType.Equals("集會") && mMeetingList.Contains(Attendance.Absence))
{
mMeetingSemesterDict[Attendance.Absence]++;
}
else if (Attendance.PeriodType.Equals("一般") && mGeneralList.Contains(Attendance.Absence))
{
mGeneralSemesterDict[Attendance.Absence]++;
}
}
if (Attendance.PeriodType.Equals("集會") && mMeetingList.Contains(Attendance.Absence))
{
mMeetingDict[Attendance.Absence]++;
}
else if (Attendance.PeriodType.Equals("一般") && mGeneralList.Contains(Attendance.Absence))
{
mGeneralDict[Attendance.Absence]++;
}
}
