test/Member/CoreEntity/DAL/PlanMTDAL.cs

using Common.Model;
using CoreEntity.BAL;
using CoreEntity.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ZcPeng.PublicLibrary;
using System.Linq;

namespace CoreEntity.DAL
{
    public class PlanMTDAL
    {
        private const string RULEID = "6wfq837d3f6vfdxd";

        public static bool addPayPlan(
            string SaleManId,string ContactId,string SuppliersId,
            decimal PTaxAmount,string Remark,
            string PayType,
            List<APGoodsMT> apgoodsmt,
            out string result,
            string entid,//= PurOrderDAL.ENTID
            string orgid,//= PurOrderDAL.ORGID
            string depid//= PurOrderDAL.DEPID
            ) {

            SqlConnection conn = null;
            SqlTransaction trans = null;
            //如果单据中有已经申请付款的,已经付款的, 返回失败
            string BillNos = "";
            foreach (APGoodsMT gmt in apgoodsmt)
            {
                BillNos = BillNos + gmt.BillNo + ",";
            }
            BillNos = BillNos.Substring(0, BillNos.Length - 1);
            DataAccess.GetOneValue("select BillNo from APGoodsMT where IsSettle ='Y' and BIllNo in (" + BillNos + ")", out var obj, out var resultSettle,
                conn, trans, true);

            DataAccess.GetOneValue("select BillNo from PlanBillDT where RfBIllNo in (" + BillNos + ")", new SqlParameter[] { }, out var objPlan, out var resultPlan,
                out conn, out trans, true);
            if (obj != null || objPlan != null)
            {
                result = "已经申请付款的不能重复申请付款";
                if (trans != null)
                    trans.Commit();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                }
                return false;
            }

            //初始化明细数据
            var i0 = 0;
            string billNos = "";
            string result1 = "";
            foreach (APGoodsMT apgoodsmts in apgoodsmt)
            {
                if (i0 == apgoodsmt.Count - 1)
                    billNos = billNos + apgoodsmts.BillNo ;
                else
                    billNos = billNos + apgoodsmts.BillNo + ",";
                i0 = i0 + 1;
            }
            DataTable dt = new DataTable();
            var success2 = DataAccess.GetValues("select * from APGoodsDT where BillNo in (" + billNos + ")", ref dt, new SqlParameter[] { }, out result1);

            //付款总额：PTaxAmount
            PTaxAmount = 0m;
            List<APGoodsDT> resultDt = new List<APGoodsDT>();
            if (dt != null && dt.Rows.Count > 0)
            {
                resultDt = (List<APGoodsDT>)ModelConvertHelper<APGoodsDT>.ConvertToModel(dt);
                var lookup = resultDt.ToLookup(x => x.BillNo, x => x);

                foreach (APGoodsMT apgoodsmts in apgoodsmt)
                {
                    apgoodsmts.GoodsList = new List<APGoodsDT>(5);
                    if (lookup.Contains(apgoodsmts.BillNo))
                    {
                        apgoodsmts.GoodsList.AddRange(lookup[apgoodsmts.BillNo]);
                    }
                    PTaxAmount = PTaxAmount + apgoodsmts.TaxAmount;
                }
            }

            string BillNo = "";
            string BillCode = "";

            var PlanBillNo = IDHelper.GetRecNo();
            BillNo = PlanBillNo.ToString() ;
            if (PlanBillNo == 0)
            {
                result = "获取billno失败";
                return false;
            }
            //生成billcode
            var PlanBillCodeNo = IDHelper.GetPlanBillCodeRecNo(entid);
            if (PlanBillCodeNo == 0)
            {
                result = "获取billcode失败";
                return false;
            }
            //取ordercode 
            string orderNo = PlanBillCodeNo.ToString();
            StringBuilder sb = new StringBuilder("");
            if (orderNo.Length < 8)
            {
                for (int ik = 0; ik < (8 - orderNo.Length); ik++)
                {
                    sb.Append('0');
                }
                sb.Append(orderNo);
            }
            string orgno = OrgDAL.GetOrgNo(orgid,entid);
            BillCode = "PJH" + orgno//机构标识
                + sb.ToString();

            
            #region 添加计划汇总
            string commandText = "INSERT INTO  PlanMT (BillNo,EntId,BillCode,RuleId,PayOrgId,OrgId,DeptId," +
                "SaleManId,caozy,Dates,OnTime,SysDates,RfId,RfType," +
                "PTaxAmount,PayDdate,PlanPayType,IsEnd,IsSettle,jiesje,Summaries," +
                "Remark,BillState,PayType,K_IsXTBill)"
                + " VALUES (@BillNo,@EntId,@BillCode,@RuleId,@PayOrgId,@OrgId,@DeptId," +
                "@SaleManId,@caozy,@Dates,@OnTime,@SysDates,@RfId,@RfType," +
                "@PTaxAmount,@PayDdate,@PlanPayType,@IsEnd,@IsSettle,@jiesje,@Summaries," +
                "@Remark,@BillState,@PayType,@K_IsXTBill)";

            List<DataAccessCommand> list = new List<DataAccessCommand>(1+ apgoodsmt.Count * 8);
            //准备参数
            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter ("@BillNo", BillNo));
            parameters.Add(new SqlParameter ("@EntId", entid));
            parameters.Add(new SqlParameter ("@BillCode", BillCode));
            parameters.Add(new SqlParameter ("@RuleId", RULEID));
            parameters.Add(new SqlParameter ("@PayOrgId", orgid));
            parameters.Add(new SqlParameter ("@OrgId", orgid));
            parameters.Add(new SqlParameter ("@DeptId", depid));

            parameters.Add(new SqlParameter ("@SaleManId", SaleManId));//SysContextToken.CurrentEmployeeID
            parameters.Add(new SqlParameter ("@caozy", SaleManId));//SysContextToken.CurrentUserID
            parameters.Add(new SqlParameter ("@Dates", DateTime.Now.ToString("yyyy-MM-dd")));
            parameters.Add(new SqlParameter ("@OnTime", DateTime.Now.ToString("T")));
            parameters.Add(new SqlParameter ("@SysDates", DateTime.Now.ToString("yyyy-MM-dd")));
            parameters.Add(new SqlParameter ("@RfId", SuppliersId));//对应供应商Id
            parameters.Add(new SqlParameter ("@RfType", '0'));

            parameters.Add(new SqlParameter ("@PTaxAmount", PTaxAmount));
            parameters.Add(new SqlParameter ("@PayDdate", ""));
            var PlanPayType = new SqlParameter("@PlanPayType", SqlDbType.Int);
            PlanPayType.Value = 0;
            parameters.Add(PlanPayType);
            parameters.Add(new SqlParameter ("@IsEnd", 'N'));
            parameters.Add(new SqlParameter ("@IsSettle", 'N'));
            var jiesje = new SqlParameter("@jiesje", SqlDbType.Decimal);
            jiesje.Value = 0;
            parameters.Add(jiesje);

            parameters.Add(new SqlParameter ("@Summaries", "采购付款计划"));
            parameters.Add(new SqlParameter ("@Remark", Remark));
            var BillState = new SqlParameter("@BillState", SqlDbType.Int);
            BillState.Value = 0;
            parameters.Add(BillState);
            parameters.Add(new SqlParameter ("@PayType", PayType));
            parameters.Add(new SqlParameter("@K_IsXTBill", 'Y'));

            //int success = DataAccess.ExecuteCommand(commandText, parameters, out result);
            list.Add(new DataAccessCommand(commandText, parameters, CommandType.Text, true));
            #endregion

            int i = 1;
            int j = 1;
            foreach (APGoodsMT gmt in apgoodsmt) { 
                #region 添加计划单据
                string commandTextBill = "INSERT INTO  PlanBillDT (BillNo,EntId,BillSn,BillSort,RfBillNo,TaxAmount,PtaxAmount," +
                    "PayDDate,IsSettle,jiesje,Remark" +
                    ")"
                    + " VALUES (@BillNo,@EntId,@BillSn,@BillSort,@RfBillNo,@TaxAmount,@PtaxAmount," +
                    "@PayDDate,@IsSettle,@jiesje,@Remark)";
                //string resultBill;
                //准备参数
                List<SqlParameter> parametersBill = new List<SqlParameter>();
                parametersBill.Add(new SqlParameter ("@BillNo", BillNo));
                parametersBill.Add(new SqlParameter ("@EntId", entid));
                parametersBill.Add(new SqlParameter ("@BillSn", i));
                parametersBill.Add(new SqlParameter ("@BillSort", i));
                parametersBill.Add(new SqlParameter ("@RfBillNo", gmt.BillNo));

                parametersBill.Add(new SqlParameter ("@TaxAmount", gmt.TaxAmount));
                parametersBill.Add(new SqlParameter ("@PtaxAmount", gmt.TaxAmount));
                parametersBill.Add(new SqlParameter ("@PayDDate", ""));
                parametersBill.Add(new SqlParameter ("@IsSettle", 'N'));
                var jiesjeBill = new SqlParameter("@jiesje", SqlDbType.Decimal);
                jiesjeBill.Value = 0;
                parametersBill.Add(jiesjeBill);
                parametersBill.Add(new SqlParameter ("@Remark", gmt.Remark == null ? "" : gmt.Remark));

                //int successBill = DataAccess.ExecuteCommand(commandTextBill, parametersBill, out result);
                list.Add(new DataAccessCommand(commandTextBill, parametersBill, CommandType.Text, true));
                i = i + 1;
                #endregion

                foreach (APGoodsDT gdt in gmt.GoodsList)
                {
                    #region 添加计划商品明细
                    string commandTextDT = "INSERT INTO  PlanGoodsDT (BillNo,EntId,BillSn,BillSort,RfBillNo,RfBillSn,TaxAmount,PtaxAmount," +
                    "PayDDate,IsSettle,jiesje,Remark" +
                    ")"
                    + " VALUES (@BillNo,@EntId,@BillSn,@BillSort,@RfBillNo,@RfBillSn,@TaxAmount,@PtaxAmount," +
                    "@PayDDate,@IsSettle,@jiesje,@Remark)";
                    //string result;
                    //准备参数
                    List<SqlParameter> parametersDT = new List<SqlParameter>();
                    parametersDT.Add(new SqlParameter("@BillNo", BillNo));
                    parametersDT.Add(new SqlParameter("@EntId", entid));
                    parametersDT.Add(new SqlParameter("@BillSn", j));
                    parametersDT.Add(new SqlParameter("@BillSort", j));
                    parametersDT.Add(new SqlParameter("@RfBillNo", gdt.BillNo));
                    parametersDT.Add(new SqlParameter("@RfBillSn", gdt.BillSn));

                    parametersDT.Add(new SqlParameter("@TaxAmount", gdt.TaxAmount));
                    parametersDT.Add(new SqlParameter("@PtaxAmount", gdt.TaxAmount));//分批 结算金额
                    parametersDT.Add(new SqlParameter("@PayDDate", ""));
                    parametersDT.Add(new SqlParameter("@IsSettle", 'N'));
                    var jiesjeDT = new SqlParameter("@jiesje", SqlDbType.Decimal);
                    jiesjeDT.Value = 0;
                    parametersDT.Add(jiesjeDT);
                    parametersDT.Add(new SqlParameter("@Remark", gdt.Remark==null?"": gdt.Remark));

                    //int success = DataAccess.ExecuteCommand(commandTextDT, parametersDTDT, out result);
                    list.Add(new DataAccessCommand(commandTextDT, parametersDT, CommandType.Text, true));
                    j = j + 1;
                    #endregion
                }

                #region 回写计划金额到应付单据
                string commandTextMT = "Update APGoodsMT set K_IsApplyP = 'Y',PtaxAmount=@PtaxAmount  " +
                    " Where BillNo=@BillNo ;";
                #endregion

                List<SqlParameter> parametersMT = new List<SqlParameter>();
                parametersMT.Add(new SqlParameter("BillNo", gmt.BillNo));
                parametersMT.Add(new SqlParameter("PtaxAmount", gmt.TaxAmount));

                list.Add(new DataAccessCommand(commandTextMT, parametersMT, CommandType.Text, true));
            }

            bool success = DataAccess.ExecuteBatchCommands(list, out result,conn,trans);
            return success;
        }
    }
}