Groupware/Project/Web/MachineBridge/MachineBridge.Jobreport.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 Jobreport API

        /// <summary>
        /// 업무일지 목록 조회 (vJobReportForUser 뷰 사용)
        /// </summary>
        public string Jobreport_GetList(string sd, string ed, string uid, string cate, string searchKey)
        {
            try
            {
                var sql = @"SELECT idx, pidx, pdate, id, name,type, svalue, hrs,ot,requestpart,package,userprocess,status, projectName, description, ww,otpms,process
                           FROM vJobReportForUser WITH (nolock)
                           WHERE gcode = @gcode AND (pdate BETWEEN @sd AND @ed)";

                var parameters = new List<SqlParameter>();
                parameters.Add(new SqlParameter("@gcode", info.Login.gcode));
                parameters.Add(new SqlParameter("@sd", sd));
                parameters.Add(new SqlParameter("@ed", ed));

                if (!string.IsNullOrEmpty(uid))
                {
                    sql += " AND id = @uid";
                    parameters.Add(new SqlParameter("@uid", uid));
                }

                if (!string.IsNullOrEmpty(searchKey))
                {
                    sql += " AND (requestpart LIKE @searchKey OR package LIKE @searchKey OR projectName LIKE @searchKey OR process LIKE @searchKey OR [type] LIKE @searchKey OR description LIKE @searchKey)";
                    parameters.Add(new SqlParameter("@searchKey", "%" + searchKey + "%"));
                }

                sql += " ORDER BY pdate DESC, idx DESC";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        return JsonConvert.SerializeObject(new { Success = true, Data = dt });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 업무일지 사용자 목록 조회
        /// </summary>
        public string Jobreport_GetUsers()
        {
            try
            {
                var sql = @"SELECT u.id, u.name
                           FROM vGroupUser u WITH (nolock)
                           WHERE u.gcode = @gcode AND u.useJobReport = 1
                           ORDER BY u.name";

                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);
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        return JsonConvert.SerializeObject(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Jobreport_GetUsers 오류: {ex.Message}");
                return "[]";
            }
        }

        /// <summary>
        /// 업무일지 상세 조회 (vJobReportForUser 뷰 + JobReport 테이블 조인)
        /// </summary>
        public string Jobreport_GetDetail(int id)
        {
            try
            {
                // 뷰에서 기본 정보 조회, 원본 테이블에서 jobgrp, tag, otStart, otEnd 추가 조회
                var sql = @"SELECT v.idx, v.pidx, v.pdate, v.id, v.name, v.type, v.svalue, v.hrs, v.ot,
                           v.requestpart, v.package, v.userprocess, v.status, v.projectName, v.description,
                           v.ww, v.otpms, v.process, j.jobgrp, j.tag, j.otStart, j.otEnd
                           FROM vJobReportForUser v WITH (nolock)
                           INNER JOIN JobReport j WITH (nolock) ON v.idx = j.idx
                           WHERE v.idx = @idx AND v.gcode = @gcode";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@idx", id);
                    cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            var row = dt.Rows[0];
                            var data = new Dictionary<string, object>();
                            foreach (DataColumn col in dt.Columns)
                            {
                                data[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
                            }
                            return JsonConvert.SerializeObject(new { Success = true, Data = data });
                        }
                        return JsonConvert.SerializeObject(new { Success = false, Message = "데이터를 찾을 수 없습니다." });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 업무일지 추가 (JobReport 테이블)
        /// </summary>
        public string Jobreport_Add(string pdate, string projectName, int pidx, string requestpart, string package,
            string type, string process, string status, string description, double hrs, double ot, string jobgrp, string tag,
            string otStart, string otEnd)
        {
            try
            {
                // 마감 체크
                var smon = pdate.Substring(0, 7);
                if (DBM.GetMagamStatus(smon))
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = $"등록일이 속한 월({smon})이 마감되었습니다." });
                }

                var sql = @"INSERT INTO JobReport (gcode, uid, pdate, projectName, requestpart, package,
                           type, process, status, description, hrs, ot, jobgrp, tag, wuid, wdate, pidx, otStart, otEnd)
                           VALUES (@gcode, @uid, @pdate, @projectName, @requestpart, @package,
                           @type, @process, @status, @description, @hrs, @ot, @jobgrp, @tag, @wuid, GETDATE(), @pidx, @otStart, @otEnd);
                           SELECT SCOPE_IDENTITY();";

                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("@uid", info.Login.no);
                    cmd.Parameters.AddWithValue("@pdate", pdate);
                    cmd.Parameters.AddWithValue("@projectName", projectName ?? "");
                    cmd.Parameters.AddWithValue("@pidx", pidx);
                    cmd.Parameters.AddWithValue("@requestpart", requestpart ?? "");
                    cmd.Parameters.AddWithValue("@package", package ?? "");
                    cmd.Parameters.AddWithValue("@type", type ?? "");
                    cmd.Parameters.AddWithValue("@process", process ?? "");
                    cmd.Parameters.AddWithValue("@status", status ?? "진행 완료");
                    cmd.Parameters.AddWithValue("@description", description ?? "");
                    cmd.Parameters.AddWithValue("@hrs", hrs);
                    cmd.Parameters.AddWithValue("@ot", ot);
                    cmd.Parameters.AddWithValue("@jobgrp", jobgrp ?? "");
                    cmd.Parameters.AddWithValue("@tag", tag ?? "");
                    cmd.Parameters.AddWithValue("@wuid", info.Login.no);
                    
                    // otStart, otEnd 처리 (HH:mm 형식을 datetime으로 변환)
                    if (!string.IsNullOrEmpty(otStart) && !string.IsNullOrEmpty(otEnd))
                    {
                        var otStartDateTime = DateTime.Parse($"{pdate} {otStart}:00");
                        var otEndDateTime = DateTime.Parse($"{pdate} {otEnd}:00");
                        cmd.Parameters.AddWithValue("@otStart", otStartDateTime);
                        cmd.Parameters.AddWithValue("@otEnd", otEndDateTime);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@otStart", DBNull.Value);
                        cmd.Parameters.AddWithValue("@otEnd", DBNull.Value);
                    }

                    cn.Open();
                    var newId = Convert.ToInt32(cmd.ExecuteScalar());
                    return JsonConvert.SerializeObject(new { Success = true, Message = "저장되었습니다.", Data = new { idx = newId } });
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 업무일지 수정 (JobReport 테이블)
        /// </summary>
        public string Jobreport_Edit(int idx, string pdate, string projectName, int pidx, string requestpart, string package,
            string type, string process, string status, string description, double hrs, double ot, string jobgrp, string tag,
            string otStart, string otEnd)
        {
            try
            {
                // 권한 체크
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.jobreport));

                // 마감 체크
                var smon = pdate.Substring(0, 7);
                if (DBM.GetMagamStatus(smon))
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = $"등록일이 속한 월({smon})이 마감되었습니다." });
                }

                // 본인 자료인지 체크 (관리자가 아닌 경우)
                if (curLevel < 5)
                {
                    var checkSql = "SELECT uid FROM JobReport WHERE idx = @idx AND gcode = @gcode";
                    var cs2 = Properties.Settings.Default.gwcs;
                    using (var cn2 = new SqlConnection(cs2))
                    using (var cmd2 = new SqlCommand(checkSql, cn2))
                    {
                        cmd2.Parameters.AddWithValue("@idx", idx);
                        cmd2.Parameters.AddWithValue("@gcode", info.Login.gcode);
                        cn2.Open();
                        var ownerUid = cmd2.ExecuteScalar()?.ToString();
                        if (ownerUid != info.Login.no)
                        {
                            return JsonConvert.SerializeObject(new { Success = false, Message = "타인의 자료는 수정할 수 없습니다." });
                        }
                    }
                }

                var sql = @"UPDATE JobReport SET
                           pdate = @pdate, projectName = @projectName, pidx = @pidx, requestpart = @requestpart,
                           package = @package, type = @type, process = @process, status = @status,
                           description = @description, hrs = @hrs, ot = @ot, jobgrp = @jobgrp, tag = @tag,
                           otStart = @otStart, otEnd = @otEnd, wuid = @wuid, wdate = GETDATE()
                           WHERE idx = @idx AND gcode = @gcode";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddWithValue("@idx", idx);
                    cmd.Parameters.AddWithValue("@gcode", info.Login.gcode);
                    cmd.Parameters.AddWithValue("@pdate", pdate);
                    cmd.Parameters.AddWithValue("@projectName", projectName ?? "");
                    cmd.Parameters.AddWithValue("@pidx", pidx);
                    cmd.Parameters.AddWithValue("@requestpart", requestpart ?? "");
                    cmd.Parameters.AddWithValue("@package", package ?? "");
                    cmd.Parameters.AddWithValue("@type", type ?? "");
                    cmd.Parameters.AddWithValue("@process", process ?? "");
                    cmd.Parameters.AddWithValue("@status", status ?? "");
                    cmd.Parameters.AddWithValue("@description", description ?? "");
                    cmd.Parameters.AddWithValue("@hrs", hrs);
                    cmd.Parameters.AddWithValue("@ot", ot);
                    cmd.Parameters.AddWithValue("@jobgrp", jobgrp ?? "");
                    cmd.Parameters.AddWithValue("@tag", tag ?? "");
                    cmd.Parameters.AddWithValue("@wuid", info.Login.no);
                    
                    // otStart, otEnd 처리 (HH:mm 형식을 datetime으로 변환)
                    if (!string.IsNullOrEmpty(otStart) && !string.IsNullOrEmpty(otEnd))
                    {
                        var otStartDateTime = DateTime.Parse($"{pdate} {otStart}:00");
                        var otEndDateTime = DateTime.Parse($"{pdate} {otEnd}:00");
                        cmd.Parameters.AddWithValue("@otStart", otStartDateTime);
                        cmd.Parameters.AddWithValue("@otEnd", otEndDateTime);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@otStart", DBNull.Value);
                        cmd.Parameters.AddWithValue("@otEnd", DBNull.Value);
                    }

                    cn.Open();
                    var result = cmd.ExecuteNonQuery();
                    return JsonConvert.SerializeObject(new { Success = result > 0, Message = result > 0 ? "수정되었습니다." : "수정에 실패했습니다." });
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 업무일지 삭제
        /// </summary>
        public string Jobreport_Delete(int idx)
        {
            try
            {
                // 권한 체크
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.jobreport));

                // 본인 자료인지 체크 (관리자가 아닌 경우)
                if (curLevel < 5)
                {
                    var checkSql = "SELECT uid, pdate FROM JobReport WHERE idx = @idx AND gcode = @gcode";
                    var cs2 = Properties.Settings.Default.gwcs;
                    using (var cn2 = new SqlConnection(cs2))
                    using (var cmd2 = new SqlCommand(checkSql, cn2))
                    {
                        cmd2.Parameters.AddWithValue("@idx", idx);
                        cmd2.Parameters.AddWithValue("@gcode", info.Login.gcode);
                        cn2.Open();
                        using (var reader = cmd2.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                var ownerUid = reader["uid"]?.ToString();
                                var pdate = reader["pdate"]?.ToString();

                                if (ownerUid != info.Login.no)
                                {
                                    return JsonConvert.SerializeObject(new { Success = false, Message = "타인의 자료는 삭제할 수 없습니다." });
                                }

                                // 마감 체크
                                if (!string.IsNullOrEmpty(pdate) && pdate.Length >= 7)
                                {
                                    var smon = pdate.Substring(0, 7);
                                    if (DBM.GetMagamStatus(smon))
                                    {
                                        return JsonConvert.SerializeObject(new { Success = false, Message = $"등록일이 속한 월({smon})이 마감되었습니다." });
                                    }
                                }
                            }
                        }
                    }
                }

                var sql = "DELETE FROM JobReport WHERE idx = @idx AND gcode = @gcode";

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

                    cn.Open();
                    var result = cmd.ExecuteNonQuery();
                    return JsonConvert.SerializeObject(new { Success = result > 0, Message = result > 0 ? "삭제되었습니다." : "삭제에 실패했습니다." });
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 업무형태 목록 조회 (Common 테이블, grp='15')
        /// 트리뷰 형태: process > jobgrp > type
        /// </summary>
        public string Jobreport_GetJobTypes(string process = "")
        {
            try
            {
                var sql = @"SELECT idx, code, memo as [type], svalue as jobgrp, svalue2 as process
                           FROM Common WITH (nolock)
                           WHERE gcode = @gcode AND grp = '15'";

                var parameters = new List<SqlParameter>();
                parameters.Add(new SqlParameter("@gcode", info.Login.gcode));

                if (!string.IsNullOrEmpty(process))
                {
                    sql += " AND svalue2 = @process";
                    parameters.Add(new SqlParameter("@process", process));
                }

                sql += " ORDER BY svalue2, svalue, memo";

                var cs = Properties.Settings.Default.gwcs;
                using (var cn = new SqlConnection(cs))
                using (var cmd = new SqlCommand(sql, cn))
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);
                        return JsonConvert.SerializeObject(new { Success = true, Data = dt });
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 업무일지 권한 정보 조회
        /// 본인이거나 권한 레벨 5 이상이면 OT 열을 볼 수 있음
        /// </summary>
        public string Jobreport_GetPermission(string targetUserId)
        {
            try
            {
                int curLevel = Math.Max(info.Login.level, DBM.getAuth(DBM.eAuthType.jobreport));
                bool canViewOT = string.IsNullOrEmpty(targetUserId) ||
                                 targetUserId == info.Login.no ||
                                 curLevel >= 5;

                return JsonConvert.SerializeObject(new
                {
                    Success = true,
                    CurrentUserId = info.Login.no,
                    Level = curLevel,
                    CanViewOT = canViewOT
                });
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        /// <summary>
        /// 특정 프로젝트의 마지막 업무일지 조회 (프로젝트 선택 시 기본값 설정용)
        /// </summary>
        public string Jobreport_GetLastByProject(int pidx, string projectName)
        {
            try
            {
                var currentUserId = info.Login.no;
                var sql = "";

                if (pidx > 0)
                {
                    // pidx로 조회
                    sql = @"SELECT TOP 1 requestpart, package, type, jobgrp, process, status
                           FROM JobReport WITH (nolock)
                           WHERE gcode = @gcode AND uid = @uid AND ISNULL(pidx, -1) = @pidx
                           ORDER BY pdate DESC, idx DESC";
                }
                else if (!string.IsNullOrEmpty(projectName))
                {
                    // projectName으로 조회
                    sql = @"SELECT TOP 1 requestpart, package, type, jobgrp, process, status
                           FROM JobReport WITH (nolock)
                           WHERE gcode = @gcode AND uid = @uid AND ISNULL(projectName, '') LIKE @projectName
                           ORDER BY pdate DESC, idx DESC";
                }
                else
                {
                    return JsonConvert.SerializeObject(new { Success = false, Message = "프로젝트 정보가 없습니다." });
                }

                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("@uid", currentUserId);
                    if (pidx > 0)
                    {
                        cmd.Parameters.AddWithValue("@pidx", pidx);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@projectName", projectName);
                    }

                    using (var da = new SqlDataAdapter(cmd))
                    {
                        var dt = new DataTable();
                        da.Fill(dt);

                        if (dt.Rows.Count > 0)
                        {
                            var row = dt.Rows[0];
                            var data = new Dictionary<string, object>();
                            foreach (DataColumn col in dt.Columns)
                            {
                                data[col.ColumnName] = row[col] == DBNull.Value ? null : row[col];
                            }
                            return JsonConvert.SerializeObject(new { Success = true, Data = data });
                        }
                        else
                        {
                            return JsonConvert.SerializeObject(new { Success = false, Message = "이전 기록이 없습니다." });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return JsonConvert.SerializeObject(new { Success = false, Message = ex.Message });
            }
        }

        #endregion
    }
}