ZLPMS/PMS.DBService/ProductManager/ProductDBservice.cs

using QWPlatform.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PMS.BusinessModels.Account;
using PMS.BusinessModels.Problem;
using System.Data;
using QWPlatform.SystemLibrary.LogManager;
using QWPlatform.SystemLibrary;
using PMS.EntityModels.Problem;
using PMS.Plugins.Common;

namespace PMS.DBService.ProductManager
{
    public class ProductDBservice : DataServiceBase
    {
        protected override void DBFctory(string conName)
        {
            base.DBFctory(conName);
        }


        //登记产品问题
        public ProblemStateModel RegisterProductProblem(ProblemBusinessModel model, UserInfo user, string id)
        {
            ProblemStateModel state = new ProblemStateModel();
            try
            {
                //根据项目产品模块ID获取产品ID                
                string CPID = SelectProjectModualInfo(model.CPID).Rows[0]["产品ID"].ToString();

                state.Count = 0;

                //问题性质
                int Nature = SelectNature(model.WTXZ).Rows[0]["代码"].ToInt32();

                //读取紧急程度
                DataTable dt = SelectBuilder.From("基础编码").Columns("代码").Where("ID", model.JJDM).Select();
                string JJCD = dt.Rows[0]["代码"].ToString();

                var FKR = string.Empty;//反馈人
                if (!string.IsNullOrEmpty(model.FKR) && model.FKR.Length > 0)
                {
                    DataTable dd = SelectBuilder.From("人员信息").Columns("姓名").Where("ID", model.FKR).Select();
                    if (dd.Rows.Count == 0)
                    {
                        FKR = model.FKR;
                    }
                    else
                    {
                        FKR = dd.Rows[0]["姓名"].ToString();
                    }
                }
                if (model.YFCLRID.IsNullOrEmpty())//获取研发人员
                {
                    model.YFCLR = "";
                }
                else
                {
                    model.YFCLR = SelectBuilder.From("人员信息").Columns("姓名").Where("ID", model.YFCLRID).Select().Rows[0]["姓名"].ToString();
                }
                if (model.CSCLRID.IsNullOrEmpty())//获取测试人员
                {
                    model.CSCLR = "";

                }
                else
                {
                    model.CSCLR = SelectBuilder.From("人员信息").Columns("姓名").Where("ID", model.CSCLRID).Select().Rows[0]["姓名"].ToString();

                }

                int? FromID = null;
                if (!string.IsNullOrEmpty(model.LYDM))
                { //来源代码
                    FromID = SelectBuilder.From("基础编码").Columns("代码").Where("ID", model.LYDM).Select().Rows[0]["代码"]
                        .ToInt32();
                }

                //登记问题是否选择机构
                if (!model.JGID.IsNullOrEmpty())
                {
                    dt = SelectBuilder.From("项目问题流程").Columns("工作流目录ID").Where("项目ID", model.XMID).Where("机构ID", model.JGID).Select();
                }
                else
                {
                    dt = SelectBuilder.From("项目问题流程").Columns("工作流目录ID").Where("项目ID", model.XMID).Where("机构ID is null").Select();
                }
                if(dt==null)
                {
                    dt = SelectBuilder.From("项目问题流程").Columns("工作流目录ID").Where("项目ID", model.XMID).Select();
                }
                //工作流目录ID
                int CatalogID = int.Parse((dt.Rows[0]["工作流目录ID"].ToString()));
                DataTable dz = SelectBuilder.From("问题工作流程").Columns("ID").Columns("环节ID").Where("目录ID", CatalogID).Where("名称", "研发处理").Select();
                int FlowID = dz.Rows[0]["ID"].ToInt32();//流程ID
                int RingID = dz.Rows[0]["环节ID"].ToInt32();//环节ID

                var time = this.DataBaseBuilder.GetDateTime;
                //添加数据到问题记录表中
                state.Count = InsertBuilder.Insert("问题记录")
                .Column("ID", id)
                .Column("编号", model.BH)
                .Column("模块ID", model.MKID)
                .Column("项目ID", model.XMID)
                .Column("渠道ID", model.QDID)
                .Column("产品ID", CPID)
                .Column("机构ID", model.JGID)
                .Column("紧急代码", JJCD)
                .Column("紧急描述", model.JJMS)
                .Column("要求时间", model.YQSJ)
                .Column("问题类型", model.WTLX == null ? 1 : model.WTLX)
                .Column("登记人", user.Name)
                .Column("登记人ID", user.PersonID)
                .Column("版本号", model.BBH)
                .Column("问题描述", model.WTMS)
                .Column("登记日期", time)
                .Column("反馈日期", model.DJRJ)
                .Column("问题标题", model.WTBT)
                .Column("反馈人", FKR)
                .Column("技术支持", 1)
                .Column("研发协助", 1)
                .Column("项目处理", 1)
                .Column("是否终止", 0)
                .Column("是否解决", 0)
                .Column("环节ID", RingID)
                .Column("来源代码", FromID)
                .Column("问题性质", Nature)
                .Column("是否受理", 1)
                  .Column("流程ID", FlowID)
                .Column("解决版本", model.JJBB)
                .Column("受理人", model.SLR)
                .Column("受理人ID", model.SLRID)
                  .Column("受理时间", time)
                .Column("BH编号", model.BHBH)
                .Column("BH版本", model.BHBB)
                .Column("是否发布", model.SFFB == null ? 0 : model.SFFB)
                .Column("常见问题", string.IsNullOrEmpty(model.CJWT) ? 0 : 1)
                .Column("需要培训", model.XYPX == null ? 0 : model.XYPX)
                .Column("内部产品问题", model.NBWT == null ? 0 : model.NBWT)
                .Column("修改说明", model.XGSM)
                .Column("升级说明", model.SJSM)
                .Column("测试说明", model.CSSM)
                .Column("关联ID", model.GLID)
                .Column("迭代ID", model.DDID)
                .Column("研发处理人ID", model.YFCLRID)
                .Column("测试处理人ID", model.CSCLRID)
                .Column("研发处理人", model.YFCLR)
                .Column("测试处理人", model.CSCLR)
                .Execute();
                if (state.Count == 1)
                {
                    //问题记录过程ID
                    state.ProblemProcessID = Guid.NewGuid().ToString();
                    state.Count = InsertBuilder.Insert("问题记录过程")
                        .Column("ID", state.ProblemProcessID)
                        .Column("问题ID", id)
                        .Column("记录人ID", user.PersonID)
                        .Column("记录时间", time)
                        .Column("当前流程ID", FlowID)
                        .Column("状态", 1)
                        .Column("耗时", 0)
                        .Column("描述", "登记问题")
                        .Execute();
                }

                //是否直接处理
                if (model.RegisterType == 2)
                {
                    UpdateBuilder.Update("问题记录")
                        .Where("ID", id)
                        .Column("是否解决", 1)
                        .Column("解决方案", model.JJFA)
                        .Column("受理人", user.Name)
                        .Column("是否受理", 1)
                        .Column("受理人ID", user.PersonID)
                        .Execute();
                    var dealtime = this.DataBaseBuilder.GetDateTime;
                    InsertBuilder.Insert("问题记录过程")
                        .Column("ID", Guid.NewGuid().ToString())
                        .Column("问题ID", id)
                        .Column("记录人ID", user.PersonID)
                        .Column("记录时间", dealtime.AddSeconds(1))
                        .Column("结束时间", dealtime.AddSeconds(1))
                        .Column("当前流程ID", FlowID)
                        .Column("状态", 7)
                        .Column("耗时", 0)
                        .Column("描述", model.JJFA)
                        .Execute();
                    UpdateBuilder.Update("问题记录过程").Where("ID", state.ProblemProcessID).Column("结束时间", dealtime.AddSeconds(1)).Execute();
                }

                return state; ;
            }
            catch (Exception ex)
            {
                Logger.Instance.Error("执行RegisterProductProblem出错", ex);
                state.Count = 0;
                return state;
            }
        }

        //项目产品模块信息
        public DataTable SelectProjectModualInfo(string CPID)
        {
            return SelectBuilder.From("项目产品模块").Columns("产品ID").Columns("版本").Where("ID", CPID).Select();
        }

        //根据性质ID返回性质代码
        public DataTable SelectNature(string wTXZ)
        {
            return SelectBuilder.From("基础编码").Columns("代码").Columns("显示名").Columns("ID").Where("ID", wTXZ).Where("启用", 1).Select();
        }

        #region 新处理版本
        /// <summary>
        /// 获取产品版本
        /// </summary>
        /// <param name="proid"></param>
        /// <returns></returns>
        public DataTable GetVersion(string pid)
        {
            var dt = SqlBuilder.SqlText("select 产品ID,解决版本 from 问题记录 where id=:ID").Parameters("ID", GetFirstId(pid)).Select();
            string sql = @" select p.id, p.版本号
                               from 产品版本配置 p,
                                    (select 解决版本
                                       from 问题记录 x
                                      where x.id =:glid    and 是否删除 = 0
                                        and 是否终止 = 0
                                        and 是否回退 = 0) w
                              where p.产品id = :cpid
                                and p.版本号 not in
                                    ((select 解决版本
                                       from 问题记录 w
                                      where w.关联id =:glid
                                        and 是否删除 = 0
                                        and 是否终止 = 0
                                        and 是否回退 = 0))
                             and p.版本号 !=(decode(w.解决版本,null,'无',w.解决版本)) and 状态!=5";
            return SqlBuilder.SqlText(sql).Parameters("glid", GetFirstId(pid)).Parameters("cpid", dt.Rows[0]["产品ID"].ToString()).Select();
        }

        //获取唯一问题序列编号
        public DataTable GetSequence()
        {
            string sql = "select 产品问题记录_编号.nextval from dual";
            return SqlBuilder.SqlText(sql).Select();
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="pid"></param>
        /// <param name="version">版本号</param>
        /// <param name="productid">产品ID</param>
        /// <param name="user"></param>
        /// <returns></returns>
        public int SaveVersion(string pid, string version, string productid, UserInfo user)
        {
            using (var tran = this.DBTransaction.BeginTrans())
            {
                try
                {
                    var model = SqlBuilder.SqlText("select * from 问题记录 where id=:ID").Parameters("ID", pid).Select().Rows[0];
                    ProblemBusinessModel proModel = new ProblemBusinessModel();
                    proModel.GLID = GetFirstId(pid);

                    if (!string.IsNullOrEmpty(model["产品ID"].ToString()))
                    {
                        if (!string.IsNullOrEmpty(productid))
                        {
                            model["产品ID"] = productid;
                    }
                        proModel.CPID = SqlBuilder.SqlText("select id from 项目产品模块 where 产品id=:CPID").Parameters("CPID", model["产品ID"].ToString()).Select().Rows[0]["id"].ToString();
                    }

                  
                    if (!string.IsNullOrEmpty(model["反馈人"].ToString()))
                    {
                        var fkr = SqlBuilder.SqlText("select id from 人员信息 where 姓名=:XM").Parameters("XM", model["反馈人"].ToString()).Select();
                        if(fkr.Rows.Count > 0)
                        {
                            proModel.FKR = fkr.Rows[0]["id"].ToString();
                        }
                    }
                    if (!string.IsNullOrEmpty(model["来源代码"].ToString()))
                    {
                        proModel.LYDM = GetBaseID("2DDA172A-499A-40CC-BA6A-29AB0FAEEDCB", model["来源代码"].ToString());

                    }
                    //proModel.QDID = model["渠道ID"].ToString();
                    //proModel.XMID = model["项目ID"].ToString();
                    proModel.QDID = user.CompanyID;//由于维保管控，复制的问题对应项目没有维保记录，所以挂总部渠道，内部登记项目上面
                    proModel.XMID = "f40e3a7d-f4cf-4f34-aa65-11f06206d7a8";//内部登记
                    proModel.MKID = model["模块ID"].ToString();
                    proModel.JJMS = model["紧急描述"].ToString();
                    proModel.WTBT = model["问题标题"].ToString();
                    //proModel.JGID = model["机构ID"].ToString();
                    proModel.NBWT = model["内部产品问题"].ToInt32();
                    proModel.BBH = version;
                    proModel.JJDM = GetBaseID("A10D6027-DEDD-4B6D-9549-33830DE52AEA", model["紧急代码"].ToString());
                    proModel.WTXZ = GetBaseID("AFBA7E41-A62E-4AB0-B861-40BAB5DA6F73", model["问题性质"].ToString());
                    proModel.DJR = user.Name;
                    proModel.DJRID = user.PersonID;
                    proModel.BH = int.Parse(GetSequence().Rows[0]["NEXTVAL"].ToString());
                    proModel.DJRJ = DataBaseBuilder.GetDateTime;
                    proModel.RegisterType = 1;
                    proModel.SLR = model["受理人"].ToString();
                    proModel.SLRID = model["受理人ID"].ToString();
                    proModel.WTMS= model["问题描述"].ToString();
                    var state = RegisterProductProblem(proModel, user, Guid.NewGuid().ToString());
                    if (state.Count > 0)
                    {
                        var count = SqlBuilder.SqlText("select 关联数量 from 问题记录 where id=:ID").Parameters("ID", GetFirstId(pid)).Select().Rows[0]["关联数量"].ToString();
                        UpdateBuilder.Update("问题记录").Where("ID", GetFirstId(pid)).Column("关联数量", String.IsNullOrEmpty(count) ? 1 : count.ToInt32() + 1).Execute(tran);
                    }
                    tran.CommitTrans();
                    return 1;
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    Logger.Instance.Error("调用SaveVersion时发生异常错误", ex);
                    return 0;
                }
            }

        }

        //递归查原始被关联的问题ID
        public string GetFirstId(string pid)
        {
            var dt = SqlBuilder.SqlText("select id,关联id from 问题记录 where id=:ID").Parameters("ID", pid).Select();
            if (!String.IsNullOrEmpty(dt.Rows[0]["关联id"].ToString()))
            {
                return GetFirstId(dt.Rows[0]["关联id"].ToString());
            }
            else
            {
                return dt.Rows[0]["id"].ToString();
            }

        }

        public string GetBaseID(string type, string id)
        {
            return SqlBuilder.SqlText("select id from 基础编码 where 分类id=:type and 代码=:code").Parameters("type", type).Parameters("code", id).Select().Rows[0]["id"].ToString();
        }

        //返回关联子版本
        public DataTable ShowConnect(string id)
        {
            return SqlBuilder.SqlText("select '['||编号||']    '||解决版本 as 编号版本 from 问题记录 where 关联id=:ID  and 是否删除 = 0 and 是否终止 = 0 and 是否回退 = 0").Parameters("ID", id).Select();
        }
        #endregion
    }
}