/// <summary>
///
/// </summary>
/// <param name="identity">身份证号</param>
public void Insert(string identity)
{
List <SqlParameter> 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 = "无效用户！";
checkValid = false;
}
else
{
label13.Text = string.Empty;
checkValid = true;
}
}
/// <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;
}
}