Groupware/Project/Web/MachineBridge/MachineBridge.HolidayRequest.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using FCOMMON;

namespace Project.Web
{
    public partial class MachineBridge
    {
        #region HolidayRequest API (휴가/외출 신청)

        /// <summary>
        /// 휴가/외출 신청 목록 조회
        /// </summary>
        public string HolidayRequest_GetList(string startDate, string endDate, string userId, int userLevel)
        {
            try
            {
                // 권한에 따른 uid 필터링
                // userLevel < 5: 본인만 조회
                // userLevel >= 5: userId가 '%'이면 전체, 특정 uid면 해당 사용자만
                var uidFilter = userLevel < 5 ? info.Login.no : (string.IsNullOrEmpty(userId) || userId == "%" ? "%" : userId);

                var sql = @"
                    SELECT  
                        hr.idx, hr.gcode, hr.uid, hr.cate, hr.sdate, hr.edate, hr.Remark, hr.wuid, hr.wdate, 
                        u.dept, u.name, u.grade, u.tel, u.processs, 
                        hr.Response, hr.conf, hr.HolyReason, hr.HolyBackup, hr.HolyLocation, 
                        hr.HolyDays, hr.HolyTimes, hr.sendmail, hr.stime, hr.etime, hr.conf_id, hr.conf_time
                    FROM EETGW_HolydayRequest hr WITH (nolock)
                    LEFT OUTER JOIN vGroupUser u ON hr.uid = u.id AND hr.gcode = u.gcode
                    WHERE hr.gcode = @gcode 
                        AND hr.sdate >= @startDate 
                        AND hr.sdate <= @endDate 
                        AND hr.uid LIKE @uid
                    ORDER BY hr.conf, hr.sdate DESC";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                    cmd.Parameters.AddWithValue("@startDate", startDate);
                    cmd.Parameters.AddWithValue("@endDate", endDate);
                    cmd.Parameters.AddWithValue("@uid", uidFilter);

                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        
                        // 승인/미승인 합계 계산
                        decimal sumApprovedDays = 0;
                        decimal sumApprovedTimes = 0;
                        decimal sumPendingDays = 0;
                        decimal sumPendingTimes = 0;

                        foreach (DataRow row in dt.Rows)
                        {
                            var conf = Convert.ToInt32(row["conf"]);
                            var days = row["HolyDays"] != DBNull.Value ? Convert.ToDecimal(row["HolyDays"]) : 0;
                            var times = row["HolyTimes"] != DBNull.Value ? Convert.ToDecimal(row["HolyTimes"]) : 0;

                            if (conf == 1)
                            {
                                sumApprovedDays += days;
                                sumApprovedTimes += times;
                            }
                            else
                            {
                                sumPendingDays += days;
                                sumPendingTimes += times;
                            }
                        }

                        return JsonConvert.SerializeObject(new 
                        { 
                            Success = true, 
                            Data = dt,
                            Summary = new
                            {
                                ApprovedDays = sumApprovedDays,
                                ApprovedTimes = sumApprovedTimes,
                                PendingDays = sumPendingDays,
                                PendingTimes = sumPendingTimes
                            }
                        });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 휴가/외출 신청 저장
        /// </summary>
        public string HolidayRequest_Save(int idx, string uid, string cate, string sdate, string edate, 
            string remark, string response, int conf, string holyReason, string holyBackup, 
            string holyLocation, decimal holyDays, decimal holyTimes, string stime, string etime)
        {
            try
            {
                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                {
                    cn.Open();
                    var sql = "";
                    
                    if (idx == 0) // INSERT
                    {
                        sql = @"INSERT INTO EETGW_HolydayRequest 
                                (gcode, uid, cate, sdate, edate, conf, Remark, wuid, wdate, Response, 
                                 HolyReason, HolyBackup, HolyLocation, HolyDays, HolyTimes, stime, etime)
                                VALUES 
                                (@gcode, @uid, @cate, @sdate, @edate, @conf, @remark, @wuid, GETDATE(), @response,
                                 @holyReason, @holyBackup, @holyLocation, @holyDays, @holyTimes, @stime, @etime)";
                    }
                    else // UPDATE
                    {
                        sql = @"UPDATE EETGW_HolydayRequest 
                                SET uid = @uid, cate = @cate, sdate = @sdate, edate = @edate, conf = @conf, 
                                    Remark = @remark, Response = @response, HolyReason = @holyReason, 
                                    HolyBackup = @holyBackup, HolyLocation = @holyLocation, 
                                    HolyDays = @holyDays, HolyTimes = @holyTimes, stime = @stime, etime = @etime
                                WHERE idx = @idx AND gcode = @gcode";
                    }

                    using (var cmd = new SqlCommand(sql, cn))
                    {
                        cmd.Parameters.AddWithValue("@idx", idx);
                        cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                        cmd.Parameters.AddWithValue("@uid", uid);
                        cmd.Parameters.AddWithValue("@cate", cate);
                        cmd.Parameters.AddWithValue("@sdate", sdate);
                        cmd.Parameters.AddWithValue("@edate", edate);
                        cmd.Parameters.AddWithValue("@conf", conf);
                        cmd.Parameters.AddWithValue("@remark", remark ?? "");
                        cmd.Parameters.AddWithValue("@wuid", info.Login.no); // 작성자
                        cmd.Parameters.AddWithValue("@response", response ?? "");
                        cmd.Parameters.AddWithValue("@holyReason", holyReason ?? "");
                        cmd.Parameters.AddWithValue("@holyBackup", holyBackup ?? "");
                        cmd.Parameters.AddWithValue("@holyLocation", holyLocation ?? "");
                        cmd.Parameters.AddWithValue("@holyDays", holyDays);
                        cmd.Parameters.AddWithValue("@holyTimes", holyTimes);
                        cmd.Parameters.AddWithValue("@stime", stime ?? "");
                        cmd.Parameters.AddWithValue("@etime", etime ?? "");

                        cmd.ExecuteNonQuery();
                    }
                }
                return JsonConvert.SerializeObject(new { Success = true, Message = "저장되었습니다." });
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        #endregion
    }
}