JLH/JLHHJSvr/Excutor/CopyMtrlDefExcutor.cs

using System;
using System.Data.SqlClient;
using System.Linq;
using DirectService.Tools;
using JLHHJSvr.BLL;
using JLHHJSvr.Com;
using JLHHJSvr.LJException;
using LJLib.DAL.SQL;
using LJLib.Net.SPI.Server;
using LJLib.SQLEX;

namespace JLHHJSvr.Excutor
{
    internal sealed class CopyMtrlDefExcutor : ExcutorBase<CopyMtrlDefRequest, CopyMtrlDefResponse>
    {
        protected override void ExcuteInternal(CopyMtrlDefRequest request, object state, CopyMtrlDefResponse rslt)
        {
            var tokendata = BllHelper.GetToken(request.token);
            if (tokendata == null)
            {
                rslt.ErrMsg = "会话已经中断，请重新登录";
                return;
            }
            if(request.pricelistid_from == null || request.pricelistid_from <= 0)
            {
                rslt.ErrMsg = "源头价格表id有误,请检查";
                return;
            }
            if (request.pricelistid_to == null || request.pricelistid_to <= 0)
            {
                rslt.ErrMsg = "目标价格表id有误,请检查";
                return;
            }

            using (var con = GlobalVar.ConnectionString.NewSqlConnection())
            using (var cmd = con.CreateCommand())
            {
                con.Open();

                using (cmd.Transaction = con.BeginTransaction())
                {
                    try
                    {
                        cmd.CommandText = @"INSERT INTO u_mtrl_price_pricelist (
	                                            mtrlid
	                                            ,pricelistid
	                                            ,price
	                                            ,pricetype
	                                            ,price_formula
	                                            )
                                            SELECT u_mtrl_price.mtrlid
	                                            ,@pricelistid
	                                            ,0
	                                            ,0
	                                            ,''
                                            FROM u_mtrl_price
                                            WHERE mtrlid NOT IN (SELECT mtrlid
		                                            FROM u_mtrl_price_pricelist
		                                            WHERE pricelistid = @pricelistid)";
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@pricelistid", request.pricelistid_to);
                        cmd.Parameters.AddWithValue("@rate", request.rate);
                        cmd.ExecuteNonQuery();

                        if(request.mtrlids != null && request.mtrlids.Count > 0)
                        {
                            cmd.CommandText = $@"UPDATE u_mtrl_price_pricelist
                                            SET u_mtrl_price_pricelist.price = ISNULL(u_temp.price, 0) * @rate
	                                            ,u_mtrl_price_pricelist.pricetype = u_temp.pricetype
	                                            ,u_mtrl_price_pricelist.price_formula = u_temp.price_formula
                                            FROM u_mtrl_price_pricelist
                                            INNER JOIN (
	                                            SELECT mtrlid
		                                            ,price
		                                            ,pricetype
		                                            ,price_formula
	                                            FROM u_mtrl_price_pricelist
	                                            WHERE pricelistid = @pricelistid_from
	                                            ) u_temp ON u_temp.mtrlid = u_mtrl_price_pricelist.mtrlid
                                            WHERE u_mtrl_price_pricelist.pricelistid = @pricelistid_to AND u_mtrl_price_pricelist.mtrlid IN {ListEx.getString(request.mtrlids)}";
                        } else
                        {
                            cmd.CommandText = @"UPDATE u_mtrl_price_pricelist
                                            SET u_mtrl_price_pricelist.price = ISNULL(u_temp.price, 0) * @rate
	                                            ,u_mtrl_price_pricelist.pricetype = u_temp.pricetype
	                                            ,u_mtrl_price_pricelist.price_formula = u_temp.price_formula
                                            FROM u_mtrl_price_pricelist
                                            INNER JOIN (
	                                            SELECT mtrlid
		                                            ,price
		                                            ,pricetype
		                                            ,price_formula
	                                            FROM u_mtrl_price_pricelist
	                                            WHERE pricelistid = @pricelistid_from
	                                            ) u_temp ON u_temp.mtrlid = u_mtrl_price_pricelist.mtrlid
                                            WHERE u_mtrl_price_pricelist.pricelistid = @pricelistid_to";
                        }

                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@pricelistid_from", request.pricelistid_from);
                        cmd.Parameters.AddWithValue("@pricelistid_to", request.pricelistid_to);
                        cmd.Parameters.AddWithValue("@rate", request.rate);
                        cmd.ExecuteNonQuery();

                        cmd.Transaction.Commit();
                    }
                    catch (Exception e)
                    {
                        cmd.Transaction.Rollback();
                        rslt.ErrMsg = e.ToString();
                    }
                }
            }
        }
    }
}