HEGII.ss.mes.check/wwwroot/main/Login/api/GetWorkStationUserReg.ashx

<%@ WebHandler Language="C#" Class="GetWorkStationUserReg" %>

using System.Web;
using System.Data;
using System.Web.SessionState;
using System.Web.Configuration;
using Newtonsoft.Json.Linq;
using Curtain.DataAccess;
using Curtain.Log;
using DK.XuWei.WebMes;

/// <summary>
/// 关联员工打卡
/// </summary>
public class GetWorkStationUserReg : IHttpHandler, IReadOnlySessionState
{
    public void ProcessRequest(HttpContext context)
    {
        using (IDataAccess conn = DataAccess.Create())
        {
            conn.BeginTransaction();

            //更新模板数据 TP_MST_USERSTAFF
            string[] staffIds = context.Request["StaffId"].ToString().Split(',');
            //取选择的工种 xuwei modify 2020-07-28
            string[] jobIds = context.Request["JobId"].ToString().Split(',');

            //2021年11月18日14:05:39 by fy modify 按工种打卡（验证处理），做变量 JobReg = 1 执行按工种打卡，JobReg = 0 一般打卡
            if (context.Request["JobReg"] == null || context.Request["JobReg"].ToString() == "1") {
                string jobStr = "'";
                if (jobIds != null) {
                    for (int i=0;i<jobIds.Length;i++) {
                        jobStr += jobIds[i] +"'";
                        if (i < jobIds.Length - 1) {
                            jobStr  += ",'";
                        }
                    }
                }
                //查询工号工种
                DataTable dt0 = conn.ExecuteDatatable(@"
				    SELECT 
                        j.JOBSID,
                        j.JOBSNAME
				    FROM
                        TP_MST_USERJOBS uj
                        INNER JOIN TP_MST_JOBS j ON j.JOBSID = uj.JOBSID
                        INNER JOIN TP_MST_USER u ON u.USERID = uj.USERID
				    WHERE
                        j.VALUEFLAG = '1'
					    AND u.VALUEFLAG = '1'
				        AND u.USERCODE = @USERCODE@ 
                        AND uj.JOBSID NOT IN ({JOBSTR})
				    ".Replace("{JOBSTR}",jobStr),
                   new CDAParameter("USERCODE",context.Request["UserCode"])
                );
                if (dt0 != null && dt0.Rows != null && dt0.Rows.Count > 0) {
                    string noHaveStr = "";
                    for (int j=0;j<dt0.Rows.Count;j++) {
                        noHaveStr += dt0.Rows[j]["JOBSNAME"];
                        if (j < dt0.Rows.Count - 1) {
                            noHaveStr  += ",";
                        }
                    }
                    context.Response.Write(new JsonResult( JsonStatus.otherError){ message="以下工种没有员工打卡："+noHaveStr}.ToJson());
                    return;
                }
            }

            //取用户ID和工种ID
            DataTable dt = conn.ExecuteDatatable(@"
				SELECT 
					u.USERID,
                    u.USERCODE,
                    u.ACCOUNTID,
                    w.UJOBSID
				FROM
					TP_MST_WORKSTATION w
                    LEFT JOIN TP_MST_WORKSTATIONUSER wu ON wu.WORKSTATIONID = w.WORKSTATIONID
                    LEFT JOIN TP_MST_USER u ON wu.USERID = u.USERID
				WHERE
					w.VALUEFLAG = '1'
					AND u.USERCODE = @USERCODE@ 
                    AND w.WORKSTATIONID = @WORKSTATIONID@
				",
                new CDAParameter("USERCODE", context.Request["UserCode"]),
                new CDAParameter("WORKSTATIONID", context.Request["WorkstationId"])
            );
            string userId = dt.Rows[0]["USERID"].ToString();
            string userCode = dt.Rows[0]["USERCODE"].ToString();
            string accountId = dt.Rows[0]["ACCOUNTID"].ToString();

            //删除模板数据 xuwei 2020-10-13 fix
            conn.ExecuteNonQuery(@"
				DELETE 
					TP_MST_USERSTAFF 
				WHERE 
					USERID = @USERID@
				",
                new CDAParameter("USERID", userId)
            );

            //写入模板数据
            for(int i = 0; i < staffIds.Length; i++)
            {
                //读取详细信息
                DataTable dtStaff = conn.ExecuteDatatable(@"
                SELECT U.USERID
                      ,U.USERCODE
                      ,D.STAFFID
                      ,D.STAFFSTATUS
                      ,US.UJOBSID
                  FROM TP_MST_WORKSTATION W
                  LEFT JOIN TP_MST_WORKSTATIONUSER WU
                    ON WU.WORKSTATIONID = W.WORKSTATIONID
                  LEFT JOIN TP_MST_USER U
                    ON WU.USERID = U.USERID
                  LEFT JOIN TP_MST_WORKSTATIONUSERDETAIL D
                    ON U.USERID = D.USERID
                  LEFT JOIN TP_MST_USERSTAFF US
                    ON US.USERID = U.USERID
                 WHERE W.VALUEFLAG = '1'
                   AND u.USERCODE = @USERCODE@ 
                   AND w.WORKSTATIONID = @WORKSTATIONID@
                   AND d.STAFFID = @STAFFID@ 
				",
                    new CDAParameter("USERCODE", context.Request["UserCode"]),
                    new CDAParameter("WORKSTATIONID", context.Request["WorkstationId"]),
                    new CDAParameter("STAFFID", staffIds[i])
                );

                //添加数据
                conn.ExecuteNonQuery(@"
				    INSERT INTO TP_MST_USERSTAFF (
						    USERID,
                            UJOBSID,
                            STAFFID,
						    ACCOUNTID,CREATEUSERID,UPDATEUSERID
					    ) VALUES (
						    @USERID@,
                            @UJOBSID@,
                            @STAFFID@,
						    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
					    )
				    ",
                new CDAParameter("USERID", userId),
                //取选择的工种 xuwei modify 2020-07-28
                new CDAParameter("UJOBSID", jobIds[i]),
                new CDAParameter("STAFFID", staffIds[i]),
                new CDAParameter("ACCOUNTID", accountId),
                new CDAParameter("CREATEUSERID", userId),
                new CDAParameter("UPDATEUSERID", userId)
                );

                // 更新工位工号明细工种 add by fubin 2020/7/28
                conn.ExecuteNonQuery(@"
                    UPDATE TP_MST_WORKSTATIONUSERDETAIL WSD
                       SET WSD.UJOBSID      = @UJOBSID@
                          ,WSD.UPDATEUSERID = @USERID@
                     WHERE WSD.USERID = @USERID@
                       AND WSD.STAFFID = @STAFFID@",

                new CDAParameter("UJOBSID", jobIds[i]),
                new CDAParameter("USERID", userId),
                new CDAParameter("STAFFID", staffIds[i])
                );
            }

            //更新班次配置 TP_PC_CLASSESSETTING
            string classesSettingid = conn.GetSequenceNextval("SEQ_PC_CLASSESSETTING_ID").ToString();
            conn.ExecuteNonQuery(@"
				INSERT INTO TP_PC_CLASSESSETTING (
						CLASSESSETTINGID,USERID,USERCODE,
						ACCOUNTDATE,ACCOUNTID,CREATEUSERID,UPDATEUSERID
					) VALUES (
						@CLASSESSETTINGID@,@USERID@,@USERCODE@,
						TRUNC(SYSDATE),@ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
					)
				",
                new CDAParameter("CLASSESSETTINGID", classesSettingid),
                new CDAParameter("USERID", userId),
                new CDAParameter("USERCODE", userCode),
                new CDAParameter("ACCOUNTID", accountId),
                new CDAParameter("CREATEUSERID", userId),
                new CDAParameter("UPDATEUSERID", userId)
            );


            //更新班次配置明细 TP_PC_CLASSESDETAIL
            for(int j=0;j<staffIds.Length;j++)
            {
                //读取详细信息
                DataTable dtStaff = conn.ExecuteDatatable(@"
				SELECT 
                    d.USERID,
                    d.USERCODE,
					w.UJOBSID,
                    u.ORGANIZATIONID AS UORGANIZATIONID,
                    d.STAFFSTATUS,
                    s.JOBS AS SJOBSID,
                    s.ORGANIZATIONID AS SORGANIZATIONID
				FROM
	                TP_MST_WORKSTATION w
	                LEFT JOIN TP_MST_WORKSTATIONUSER wu ON wu.WORKSTATIONID = w.WORKSTATIONID
	                LEFT JOIN TP_MST_USER u ON wu.USERID = u.USERID 
	                LEFT JOIN TP_MST_WORKSTATIONUSERDETAIL d ON u.USERID = d.USERID
                    LEFT JOIN TP_HR_STAFF s ON s.STAFFID = d.STAFFID
				WHERE
					d.VALUEFLAG = '1'
                    AND d.USERCODE = @USERCODE@
	                AND w.WORKSTATIONID = @WORKSTATIONID@
					AND d.STAFFID = @STAFFID@ 
				",
                    new CDAParameter("USERCODE", context.Request["UserCode"]),
                    new CDAParameter("WORKSTATIONID", context.Request["WorkstationId"]),
                    new CDAParameter("STAFFID", staffIds[j])
                );

                conn.ExecuteNonQuery(@"
				    INSERT INTO TP_PC_CLASSESDETAIL (
						    CLASSESSETTINGID,
                            ACCOUNTDATE,
                            USERID,
                            USERCODE,
                            STAFFID,
                            STAFFSTATUS,
                            UJOBSID,
                            SJOBSID,
                            UORGANIZATIONID,
                            SORGANIZATIONID,
                            UJOBSNUM,
						    ACCOUNTID,CREATEUSERID,UPDATEUSERID
					    ) VALUES (
						    @CLASSESSETTINGID@,
						    TRUNC(SYSDATE),
						    @USERID@,
						    @USERCODE@,
						    @STAFFID@,
						    @STAFFSTATUS@,
						    @UJOBSID@,
						    @SJOBSID@,
						    @UORGANIZATIONID@,
						    @SORGANIZATIONID@,
						    @UJOBSNUM@,
						    @ACCOUNTID@,@CREATEUSERID@,@UPDATEUSERID@
					    )
				    ",
                    new CDAParameter("CLASSESSETTINGID", classesSettingid),
                    new CDAParameter("USERID", dtStaff.Rows[0]["USERID"]),
                    new CDAParameter("USERCODE", dtStaff.Rows[0]["USERCODE"]),
                    new CDAParameter("STAFFID", staffIds[j]),
                    new CDAParameter("STAFFSTATUS", dtStaff.Rows[0]["STAFFSTATUS"]),
                    //取选择的工种 xuwei modify 2020-07-28
                    new CDAParameter("UJOBSID", jobIds[j]),
                    new CDAParameter("SJOBSID", dtStaff.Rows[0]["SJOBSID"]),
                    new CDAParameter("UORGANIZATIONID", dtStaff.Rows[0]["UORGANIZATIONID"]),
                    new CDAParameter("SORGANIZATIONID", dtStaff.Rows[0]["SORGANIZATIONID"]),
                    new CDAParameter("UJOBSNUM", 0),
                    new CDAParameter("ACCOUNTID", accountId),
                    new CDAParameter("CREATEUSERID", userId),
                    new CDAParameter("UPDATEUSERID", userId)
                );
            }

            //删除当前工号在任何一个工位，以确保当前工号在工位中唯一
            conn.ExecuteNonQuery(@"
				UPDATE TP_MST_WORKSTATION 
				SET
					USERCODE = NULL
				WHERE 
					USERCODE = @USERCODE@
				",
                new CDAParameter("USERCODE", context.Request["UserCode"])
            );

            //更新工位当前工号
            conn.ExecuteNonQuery(@"
				UPDATE TP_MST_WORKSTATION 
				SET
					USERCODE = @USERCODE@,
					UPDATEUSERID = @UPDATEUSERID@,
					UPDATETIME = SYSDATE
				WHERE 
					WORKSTATIONID = @WORKSTATIONID@
				",
                new CDAParameter("USERCODE", context.Request["UserCode"]),
                new CDAParameter("UPDATEUSERID", userId),
                new CDAParameter("WORKSTATIONID", context.Request["WorkstationId"])
            );

            //xuwei add 2020-10-20
            //打卡时更新 TP_MST_HGWS_USERSINFO 
            //Classsettingid 班次ID
            //Classsettingstaffcount 打卡人数
            //Updatetime 打卡时间
            conn.ExecuteNonQuery(@"
                UPDATE TP_MST_HGWS_USERSINFO 
                SET
                    CLASSSETTINGID = @CLASSSETTINGID@,
                    CLASSSETTINGSTAFFCOUNT = @CLASSSETTINGSTAFFCOUNT@,
                    UPDATETIME = SYSDATE
                WHERE 
                    USERID = @USERID@
                ",
                new CDAParameter("CLASSSETTINGID",classesSettingid),
                new CDAParameter("CLASSSETTINGSTAFFCOUNT",staffIds.Length),
                new CDAParameter("USERID",userId)
            );


            conn.Commit();
            context.Response.Write(new JsonResult( JsonStatus.success).ToJson());
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}