dccloud/WebAPIBase.NetCore/WebAPIBase.NetCore.BusinessCore/BaseCore/RequisitionManager.cs

using Sugar.Enties;
using SqlSugar;
using System;
using System.Collections.Generic;
using NLog;
using System.Data;
using WebAPIBase.Utils;

public class RequisitionManager : DbContext<Requisition>
{
    private static Logger logger = NLog.LogManager.GetCurrentClassLogger();
    //当前类已经继承了 DbContext增、删、查、改的方法

    //这里面写的代码不会给覆盖,如果要重新生成请删除 RequisitionManager.cs
    /// <summary>
    /// 签证申请单插入
    /// </summary>
    /// <param name="requisition"></param>
    /// <param name="list"></param>
    /// <returns></returns>
    public string InsertRequisition(Requisition requisition, List<RequisitionContract> list)
    {

        try
        {
            string requisitionCode = "";
            Db.BeginTran();

            var sql = string.Format("declare @value varchar(50);exec sp_get_syscode 'RequisitionCode', @value output;select @value;");
            requisitionCode = Db.Ado.GetString(sql);
            //写事务代码
            requisition.RequisitionCode = requisitionCode;
            requisition.TotalEstimateCash = 0;    //暂估金额总额

            var row2 = Db.Insertable(requisition).IgnoreColumns(it => new { it.TimeStamp }).ExecuteCommand();
            logger.Info($"row2:{row2}");
            foreach (var item in list)
            {
                item.RequisitionCode = requisition.RequisitionCode;
                var requisitionContractCode = Db.Ado.GetScalar(string.Format(" SELECT RequisitionContractCode FROM  RequisitionContract WHERE RequisitionCode='{0}' and ContractCode='{1}'", item.RequisitionCode, item.ContractCode));
                if (requisitionContractCode == null)
                {

                    var row3 = Db.Insertable<RequisitionContract>(item).ExecuteCommand();
                    logger.Info($"row3:{row3}");
                    requisition.TotalEstimateCash += item.EstimateCash;
                }
                else
                {
                    var row4 = Db.Deleteable<RequisitionContract>(requisitionContractCode).ExecuteCommand();
                    logger.Info($"row4:{row4}");
                    var row5 = Db.Insertable<RequisitionContract>(item).ExecuteCommand();
                    logger.Info($"row5:{row5}");
                    requisition.TotalEstimateCash += item.EstimateCash;
                }
            }
            sql = string.Format("update Requisition set TotalEstimateCash={0} where RequisitionCode='{1}'", requisition.TotalEstimateCash, requisitionCode);
            var row1 = Db.Ado.ExecuteCommand(sql);
            logger.Info($"row1:{row1}");
            Db.CommitTran();





            return requisitionCode;
        }
        catch (Exception ex)
        {
            Db.RollbackTran();
            logger.Error(ex);
            throw;

        }

    }

    /// <summary>
    /// 更新签证申请单
    /// </summary>
    /// <param name="requisition"></param>
    /// <param name="list"></param>
    /// <returns></returns>
    public string UpdateRequisition(Requisition requisition, List<RequisitionContract> list)
    {

        try
        {

            Db.BeginTran();

            var sql = $"UPDATE dbo.Requisition SET RequisitionName='{requisition.RequisitionName}',RequisitionDate='{requisition.RequisitionDate}',ChangeType='{requisition.ChangeType}',RequisitionReason='{requisition.RequisitionReason}',Remark='{requisition.Remark}' WHERE RequisitionCode='{requisition.RequisitionCode}'";


            requisition.TotalEstimateCash = 0;    //暂估金额总额

            var row2 = Db.Ado.ExecuteCommand(sql);       //更新签证申请单表
            logger.Info($"row2:{row2}");

            Db.Ado.ExecuteCommand($"delete from RequisitionContract  where RequisitionCode='{requisition.RequisitionCode}'");
            foreach (var item in list)
            {
                item.RequisitionCode = requisition.RequisitionCode;
                var requisitionContractCode = Db.Ado.GetScalar(string.Format(" SELECT RequisitionContractCode FROM  RequisitionContract WHERE RequisitionCode='{0}' and ContractCode='{1}'", item.RequisitionCode, item.ContractCode));
                if (requisitionContractCode == null)
                {

                    var row3 = Db.Insertable<RequisitionContract>(item).ExecuteCommand();
                    logger.Info($"row3:{row3}");
                    requisition.TotalEstimateCash += item.EstimateCash;
                }
                else
                {
                    var row4 = Db.Deleteable<RequisitionContract>(requisitionContractCode).ExecuteCommand();
                    logger.Info($"row4:{row4}");
                    var row5 = Db.Insertable<RequisitionContract>(item).ExecuteCommand();
                    logger.Info($"row5:{row5}");
                    requisition.TotalEstimateCash += item.EstimateCash;
                }
            }
            sql = string.Format("update Requisition set TotalEstimateCash={0} where RequisitionCode='{1}'", requisition.TotalEstimateCash, requisition.RequisitionCode);
            var row1 = Db.Ado.ExecuteCommand(sql);
            logger.Info($"row1:{row1}");
            Db.CommitTran();


            return requisition.RequisitionCode;
        }
        catch (Exception ex)
        {
            Db.RollbackTran();
            logger.Error(ex);
            throw;

        }

    }

    /// <summary>
    /// 根据项目代码获取关联合同列表
    /// </summary>
    /// <param name="projectCode"></param>
    /// <returns></returns>
    public List<V_ContractPayMoney> GetRelationContract(string projectCode, string search)
    {
        List<V_ContractPayMoney> list;
        if (string.IsNullOrEmpty(search))
        {
            list = Db.Queryable<V_ContractPayMoney>().Where(m => m.ProjectCode == projectCode && m.Status == 0).OrderBy("contractid desc").Take(20).ToList();
        }
        else
        {
            list = Db.Queryable<V_ContractPayMoney>().Where(m => (m.ContractID.Contains(search) || m.ContractName.Contains(search)) && m.ProjectCode == projectCode && m.Status == 0).OrderBy(m => m.ContractID, OrderByType.Desc).Take(20).ToList();
        }

        return list;
    }

    /// <summary>
    /// 获取签证申请单列表
    /// </summary>
    /// <param name="where"></param>
    /// <returns></returns>
    public List<Requisition> GetRequisitionList(string where)
    {
        if (where.IsNullOrEmpty())
        {
            where = "1=1";
        }
        var sql = $"select * from Requisition where {where} order by RequisitionCode desc";
        var list = Db.Queryable<Requisition>().Where(where).OrderBy("RequisitionCode desc").ToList();
        return list;
    }
    /// <summary>
    /// 根据签证申请单编号获取单一签证申请单实例
    /// </summary>
    /// <param name="requisitionCode"></param>
    /// <returns></returns>
    public Requisition GetRequisition(string requisitionCode)
    {
        var entity = Db.Queryable<Requisition>().First(m => m.RequisitionCode == requisitionCode);
        return entity;
    }
    /// <summary>
    /// 获取关联合同
    /// </summary>
    /// <param name="requisitionCode"></param>
    /// <returns></returns>
    public List<RequisitionContract> GetRequisitionContracts(string requisitionCode)
    {
        var list = Db.Queryable<RequisitionContract>().Where(m => m.RequisitionCode == requisitionCode).ToList();
        return list;
    }

    /// <summary>
    /// 获取签证申请单附件
    /// </summary>
    /// <param name="requisitionCode"></param>
    /// <returns></returns>
    public List<AttachMent> GetAttachMents(string requisitionCode, string attachMentType)
    {
        var list = Db.Queryable<AttachMent>().Where(m => m.MasterCode == requisitionCode && m.AttachMentType == attachMentType).ToList();
        return list;
    }

    /// <summary>
    /// 签证申请关联合同表和合同表联合查询，返回一个匿名对象列表
    /// </summary>
    /// <param name="requisitionCode"></param>
    /// <returns></returns>
    public List<RelationContractDTO> GetRelationContracts(string requisitionCode)
    {
        string sql = $"SELECT a.ContractCode, a.ContractID,a.ContractName,b.EstimateCash FROM dbo.Contract a INNER JOIN RequisitionContract b ON a.ContractCode=b.ContractCode WHERE b.RequisitionCode='{requisitionCode}'";
        var list = Db.Ado.SqlQuery<RelationContractDTO>(sql);
        return list;
    }
    /// <summary>
    /// 根据项目代码和签证申请单号获取相应的申请单关联合同
    /// </summary>
    /// <param name="projectCode"></param>
    /// <param name="requisitionCode"></param>
    /// <returns></returns>
    public List<Contract> GetRelationRequistionContract(string projectCode, string requisitionCode)
    {
        var sql = $"select * from Contract where Status=0 and ProjectCode = '{projectCode}' and ContractCode in ( select ContractCode from RequisitionContract where RequisitionCode='{requisitionCode}' ) Order By ContractID DESC  ";
        var list = Db.Ado.SqlQuery<Contract>(sql);
        return list;
    }
}