/// <summary> /// 更新临时工上、下班打卡时间 /// </summary> /// <param name="identity">身份证号</param> public void Insert(string identity) { List <SqlParameter> list = new List <SqlParameter>(); list.Add(new SqlParameter("@identity", identity)); //根据临时工打卡时间获取这个时间内和临时工有关的服务 DataTable orderDT = SqlDbHelper.ExecuteDataTable("SELECT s.F_OrderId,s.F_MeetingName,s.F_StartTime,s.F_EndTime,t.F_CategoryId,t.f_userid,ISNULL(type.f_price,0) AS f_price FROM F_Base_TempWorkOrderUserDetail t INNER JOIN LR_Base_TempUser u ON t.F_UserId=u.F_UserId INNER JOIN F_Base_TempWorkOrderCategoryDetail type ON t.F_TempWorkOrderId=type.F_TempWorkOrderId AND type.F_CategoryName=t.F_CategoryId INNER JOIN F_Base_TempWorkOrder s ON t.F_TempWorkOrderId=s.F_OrderId WHERE u.F_Identity = @identity AND GETDATE() BETWEEN DATEADD( mi, -CONVERT( int, ( SELECT F_ItemValue FROM LR_Base_DataItemDetail WHERE F_ItemDetailId = '81d54b07-2acf-4efc-b2d3-079e823e3c35' ) ), s.F_StartTime) AND s.F_EndTime ORDER BY s.F_StartTime", list.ToArray()); list = new List <SqlParameter>(); list.Add(new SqlParameter("@identity", identity)); int checkBlack = SqlDbHelper.ExecuteScalar("SELECT COUNT(*) AS num FROM LR_Base_TempUser WHERE F_Identity=@identity AND F_EnabledMark=0", list.ToArray()); //判断是否是黑名单用户 if (checkBlack > 0) { label13.ForeColor = Color.Red; label13.Text = "无效用户!"; } else { if (orderDT != null && orderDT.Rows.Count > 0) { //打卡时间 DateTime time = DateTime.Now; list = new List <SqlParameter>(); list.Add(new SqlParameter("@identity", identity)); list.Add(new SqlParameter("@orderID", orderDT.Rows[0]["F_OrderId"].ToString())); //获取临时工当天打卡的两个时间用以判断是 上班还是下班 DataTable dt = SqlDbHelper.ExecuteDataTable("SELECT F_First,F_Second FROM LR_Base_CardRecord WHERE F_Identity=@identity AND F_OrderId=@orderID AND F_RecordDate=CONVERT(CHAR(10), GETDATE(), 120)", list.ToArray()); if (dt != null && dt.Rows.Count > 0) { //获取工种小时工资 int categoryPrice = Convert.ToInt32(orderDT.Rows[0]["f_price"].ToString()); if (dt.Rows[0]["F_First"] == null || string.IsNullOrEmpty(dt.Rows[0]["F_First"].ToString())) { list = new List <SqlParameter>(); list.Add(new SqlParameter("@identity", identity)); list.Add(new SqlParameter("@orderID", orderDT.Rows[0]["F_OrderId"].ToString())); int lateState = 0; if (DateTime.Compare(time, Convert.ToDateTime(orderDT.Rows[0]["F_StartTime"].ToString())) > 0) { //迟到 lateState = 1; } list.Add(new SqlParameter("@time", time)); SqlDbHelper.ExecuteNonQuery( "UPDATE LR_Base_CardRecord SET F_First=@time,f_lateState=" + lateState.ToString() + " WHERE F_Identity=@identity AND F_OrderId=@orderID AND F_RecordDate=CONVERT(CHAR(10), GETDATE(), 120)", list.ToArray()); } else if (dt.Rows[0]["F_Second"] == null || string.IsNullOrEmpty(dt.Rows[0]["F_Second"].ToString())) { list = new List <SqlParameter>(); list.Add(new SqlParameter("@identity", identity)); list.Add(new SqlParameter("@orderID", orderDT.Rows[0]["F_OrderId"].ToString())); //获取迟到状态 int lateState = 0; //lateState = SqlDbHelper.ExecuteScalar( "SELECT f_lateState FROM LR_Base_CardRecord WHERE F_Identity='" + identity + "' AND F_OrderId='" + orderDT.Rows[ 0 ][ "F_OrderId" ].ToString() + "' AND F_RecordDate=CONVERT(CHAR(10), GETDATE(), 120)" ); //早退状态 int leaveEarly = 0; //应发工资 int yfPrice = 0; if (DateTime.Compare(time, Convert.ToDateTime(orderDT.Rows[0]["F_EndTime"].ToString())) < 0) { //早退 leaveEarly = 1; } TimeSpan ts1 = new TimeSpan(time.Ticks); TimeSpan ts2 = new TimeSpan(Convert.ToDateTime(orderDT.Rows[0]["F_StartTime"].ToString()).Ticks); TimeSpan ts3 = ts1.Subtract(ts2).Duration(); //计算工资(工种小时单价 * 总工作时间 ) int price = categoryPrice * ts3.Hours; yfPrice = price; if (ts3.Minutes >= 30) { //加班如果超过半小时就发半小时工资 price = price + (categoryPrice / 2); yfPrice = yfPrice + (categoryPrice / 2); } #region 迟到扣钱(暂时屏蔽) //获取迟到需扣的金额 //int latePrice = SqlDbHelper.ExecuteScalar( "SELECT ISNULL(F_ItemValue,0) AS f_itemValue FROM LR_Base_DataItemDetail WHERE F_ItemDetailId='d96eb355-7af5-4a7e-b9da-6e6f3ca75783'" ); //迟到扣钱 // if ( lateState == 1 ) // { // price = price - latePrice; // } //早退扣钱 // if ( leaveEarly == 1 ) // { // price = price - latePrice; // } #endregion List <SqlParameter> tempList = new List <SqlParameter>(); tempList.Add(new SqlParameter("@orderID", orderDT.Rows[0]["F_OrderId"].ToString())); //获取吃饭福利状态 int checkMealTime = SqlDbHelper.ExecuteScalar("SELECT ISNULL(F_CheckMealtime,0) AS f_checkMealtime FROM F_Base_TempWorkOrder WHERE f_orderid=@orderID", tempList.ToArray()); //判断当前订单是否包含吃饭这个福利 if (checkMealTime == 1) { DataTable mealTimeDT = SqlDbHelper.ExecuteDataTable("SELECT F_ItemDetailId,F_ItemValue FROM LR_Base_DataItemDetail WHERE F_ItemId='2db79aaf-4c93-4c17-b587-e3709e4a398e' ORDER BY F_SortCode"); //获取吃饭分钟数(总) TimeSpan temp = TimeSpan.FromMinutes(Convert.ToInt32(mealTimeDT.Rows[2]["F_ItemValue"].ToString())); //午饭 if (DateTime.Compare(time, Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " " + mealTimeDT.Rows[0]["F_ItemValue"].ToString())) > 0) { int t = temp.Duration().Hours *categoryPrice; if (temp.Duration().Minutes >= 30) { t = t + (categoryPrice / 2); } price = price - t; yfPrice = yfPrice - t; } //晚饭 if (DateTime.Compare(time, Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd") + " " + mealTimeDT.Rows[1]["F_ItemValue"].ToString())) > 0) { int t = temp.Duration().Hours *categoryPrice; if (temp.Duration().Minutes >= 30) { t = t + (categoryPrice / 2); } price = price - t; yfPrice = yfPrice - t; } } //应发工资 list.Add(new SqlParameter("@f_shouldDaysalary", yfPrice)); //实发日工资 list.Add(new SqlParameter("@f_realDaySalary", price)); list.Add(new SqlParameter("@time", time)); SqlDbHelper.ExecuteNonQuery( "UPDATE LR_Base_CardRecord SET F_Second=@time,f_LeaveEarly=" + leaveEarly.ToString() + ",f_realDaySalary=@f_realDaySalary,f_shouldDaysalary=@f_shouldDaysalary WHERE F_Identity=@identity AND F_OrderId=@orderID AND F_RecordDate=CONVERT(CHAR(10), GETDATE(), 120)", list.ToArray()); } label13.Text = orderDT.Rows[0]["F_MeetingName"].ToString(); label13.ForeColor = Color.Green; } } else { label13.ForeColor = Color.Red; label13.Text = "未检测到所参与的服务!"; } } }
/// <summary> /// 人员采集 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void buttonX3_Click_1(object sender, EventArgs e) { if (!checkValid) { return; } string activity = ddlActivity.SelectedValue.ToString().Split(',')[0]; string type = string.Empty; try { type = ddlType.SelectedValue.ToString(); } catch { type = "0"; } if (activity == "0") { MessageBox.Show("请选择一个活动!"); } else if (type == "0") { MessageBox.Show("请选择一个工种!"); } else if (string.IsNullOrEmpty(identity)) { MessageBox.Show("没有检测到打卡人!"); } else { List <SqlParameter> list = new List <SqlParameter>(); list.Add(new SqlParameter("@identity", identity)); //获取小时工信息 DataTable userInfo = SqlDbHelper.ExecuteDataTable("SELECT f_userid,F_RealName,F_Gender FROM LR_Base_TempUser WHERE F_Identity=@identity", list.ToArray()); int num = 0; string userID = string.Empty; if (userInfo != null && userInfo.Rows.Count > 0) { list = new List <SqlParameter>(); list.Add(new SqlParameter("@orderID", activity)); list.Add(new SqlParameter("@userID", userInfo.Rows[0]["f_userid"].ToString())); userID = userInfo.Rows[0]["f_userid"].ToString(); num = Convert.ToInt32(SqlDbHelper.ExecuteScalar("SELECT COUNT(*) AS num FROM F_Base_TempWorkOrderUserDetail WHERE F_TempWorkOrderId=@orderID AND f_userID=@userID", list.ToArray()).ToString()); } else { userID = Guid.NewGuid().ToString(); List <SqlParameter> usersParameter = new List <SqlParameter>(); usersParameter.Add(new SqlParameter("@F_RealName", label2.Text)); usersParameter.Add(new SqlParameter("@F_Gender", label3.Text == "男" ? "1" : "2")); usersParameter.Add(new SqlParameter("@F_Mobile", txtPhone.Text)); usersParameter.Add(new SqlParameter("@f_userid", userID)); usersParameter.Add(new SqlParameter("@F_Identity", identity)); SqlDbHelper.ExecuteNonQuery("INSERT LR_Base_TempUser(f_userid,F_RealName,F_Gender,f_createtime,F_Mobile,F_Identity) VALUES(@f_userid,@F_RealName,@F_Gender,GETDATE(),@F_Mobile,@F_Identity)", usersParameter.ToArray()); list = new List <SqlParameter>(); list.Add(new SqlParameter("@orderID", activity)); list.Add(new SqlParameter("@userID", userID)); num = Convert.ToInt32(SqlDbHelper.ExecuteScalar("SELECT COUNT(*) AS num FROM F_Base_TempWorkOrderUserDetail WHERE F_TempWorkOrderId=@orderID AND f_userID=@userID", list.ToArray()).ToString()); } if (num > 0) { MessageBox.Show("人员在活动里已存在!"); } else { list = new List <SqlParameter>(); list.Add(new SqlParameter("@orderID", activity)); list.Add(new SqlParameter("@userID", userID)); list.Add(new SqlParameter("@f_id", Guid.NewGuid().ToString())); list.Add(new SqlParameter("@f_employerid", ddlActivity.SelectedValue.ToString().Split(',')[1])); list.Add(new SqlParameter("@f_categoryid", type)); //添加小时工与活动对应关系 SqlDbHelper.ExecuteNonQuery("INSERT F_Base_TempWorkOrderUserDetail(f_id,F_TempWorkOrderId,f_userid,f_employerid,f_categoryid,F_WorkSubstitute) VALUES(@f_id,@orderID,@userID,@f_employerid,@f_categoryid,0)", list.ToArray()); //--------------------------往打卡记录表中初始经小时工打卡记录------------------------------ list = new List <SqlParameter>(); list.Add(new SqlParameter("@orderID", activity)); //获取活动的开始和结束时间 DataTable dt = SqlDbHelper.ExecuteDataTable("SELECT * FROM F_Base_TempWorkOrder WHERE f_orderid=@orderID", list.ToArray()); DateTime startTime = DateTime.Parse(dt.Rows[0]["F_StartTime"].ToString()); DateTime endTime = DateTime.Parse(dt.Rows[0]["F_EndTime"].ToString()); for (DateTime t = startTime; t <= endTime; t = t.AddDays(1)) { list = new List <SqlParameter>(); list.Add(new SqlParameter("@orderID", activity)); list.Add(new SqlParameter("@RecordId", Guid.NewGuid().ToString())); list.Add(new SqlParameter("@Identity", identity)); list.Add(new SqlParameter("@CreateTime", t)); list.Add(new SqlParameter("@RealName", label2.Text.Trim())); list.Add(new SqlParameter("@Gender", label3.Text.Trim() == "男" ? "1" : "2")); list.Add(new SqlParameter("@RecordDate", t.ToString("yyyy-MM-dd"))); //自动给临时工往打卡记录里初始化打卡记录 int a = SqlDbHelper.ExecuteNonQuery("INSERT LR_Base_CardRecord(F_RecordId,F_Identity,F_CreateTime,F_RealName,F_Gender,F_OrderId,F_RecordDate) VALUES (@RecordId,@Identity,@CreateTime,@RealName,@Gender,@orderID,@RecordDate)", list.ToArray()); } } identity = string.Empty; } }