test/Member/CoreEntity/BAL/IDHelper.cs

using Common.Model;
using Common.Wechat;
using CoreEntity.DAL;
using CoreEntity.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using ZcPeng.PublicLibrary;

namespace CoreEntity.BAL
{
    class IDHelper
    {
        /// <summary>
        /// 订单汇总里面关键的几个字段
        ///一个是billno在智友的syscode表中prefix = 'F'的即为最大编号,
        ///一个是billcode在智友的billcode表中，需要对应的是采购订单的RULEID（固定5x80rrxrb24q2ktt），
        ///从billcode表中取到recnum,billcode的生成规则:PDD+机构标识+8位长度的流水码(recnum，不足补零)例如PDD+Y30+00000058
        ///订单明细中没有BILLCODE，但是有billsn，即明细的序号
        /// </summary>
        /// <returns></returns>
        public static long GetRecNo()
        {
            long id = 0;
            #region 查id

            string commandTextUser = "Select RecNum From  "
            + "SysCode Where Prefix=@Prefix";
            string resultUser;
            DataTable dt = new DataTable();

            List<SqlParameter> parametersUser = new List<SqlParameter>();
            parametersUser.Add(new SqlParameter("Prefix", 'F'));
            //DataAccess.GetValues(commandTextUser, ref dt, parametersUser.ToArray(), out resultUser);
            //List<SysCode> users = new List<SysCode>();

            //users = (List<SysCode>)ModelConvertHelper<SysCode>.ConvertToModel(dt);
            //SysCode user = users.ToArray()[0];

            //id = user.RECNUM;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            bool successUser = DataAccess.GetOneValue(commandTextUser, parametersUser.ToArray(), out var v, out resultUser, out conn, out tran, true);
            id = long.Parse(v.ToString());

            string commandText = "Update " + "SysCode set RecNum = @id +1  " +
                " Where Prefix=@Prefix ;" + commandTextUser;
            string result;

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter( "id", id));
            parameters.Add(new SqlParameter( "Prefix", 'F'));

            List<DataAccessCommand> list = new List<DataAccessCommand>();
            list.Add(new DataAccessCommand(commandText, parameters, CommandType.Text, true));
            bool success = DataAccess.ExecuteBatchCommands(list, out result,conn,tran);
            //bool success = DataAccess.GetOneValue(commandText,parameters, out var v, out result);
            //id = (long)v;
            #endregion
            if (success)
                return id + 1;

            return 0;
        }
        public static long GetBillCodeRecNo(string EntId = "E1WB67UEYPG")
        {
            long id = 0;
            #region 查id

            string commandTextUser = "select RecNum from  "
            + "billcode Where ruleid=@ruleid and EntId=@EntId";
            string resultUser;
            DataTable dt = new DataTable();

            List<SqlParameter> parametersUser = new List<SqlParameter>();
            parametersUser.Add(new SqlParameter("ruleid", "5x80rrxrb24q2ktt"));
            parametersUser.Add(new SqlParameter("EntId", EntId));
            //DataAccess.GetValues(commandTextUser, ref dt, parametersUser.ToArray(), out resultUser);
            //List<BillCode> users = new List<BillCode>();

            //users = (List<BillCode>)ModelConvertHelper<BillCode>.ConvertToModel(dt);
            //BillCode user = users.ToArray()[0];

            //id = user.RECNUM;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            bool successUser = DataAccess.GetOneValue(commandTextUser, parametersUser.ToArray(), out var v, out resultUser,out conn, out tran, true);
            id = long.Parse(v.ToString());

            string commandText = "Update " + "billcode set RecNum = @id +1   " +
                " Where ruleid=@ruleid  and EntId=@EntId;" + commandTextUser;
            string result;

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter( "id", id ));
            parameters.Add(new SqlParameter( "ruleid", "5x80rrxrb24q2ktt" ));
            parameters.Add(new SqlParameter("EntId", EntId));

            List<DataAccessCommand> list = new List<DataAccessCommand>();
            list.Add(new DataAccessCommand(commandText, parameters, CommandType.Text, true));
            bool success = DataAccess.ExecuteBatchCommands(list, out result, conn, tran);
            //bool success = DataAccess.GetOneValue(commandText, parameters, out var v, out result);
            //id = (long)v;
            #endregion
            if (success) return id + 1;
            else return 0;
        }

        public static PGPrice GetPackId(string GoodsId,string EntId = "E1WB67UEYPG")
        {
            #region 查id

            string commandTextUser = " select packId,unit,Meas  from PGPrice where IsBase = 'Y' " +
                " and goodsid = @GoodsId " +
                " and EntId = @EntId ";
            string resultUser;
            DataTable dt = new DataTable();

            List<SqlParameter> parametersUser = new List<SqlParameter>();
            parametersUser.Add(new SqlParameter( "GoodsId", GoodsId ));
            parametersUser.Add(new SqlParameter("EntId", EntId));
            DataAccess.GetValues(commandTextUser, ref dt, parametersUser.ToArray(), out resultUser);

            if (dt!= null　&& dt.Rows.Count > 0)
            {
                List<PGPrice> users = new List<PGPrice>();

                users = (List<PGPrice>)ModelConvertHelper<PGPrice>.ConvertToModel(dt);
                PGPrice user = users.ToArray()[0];
                return user;
            }
            else
            {
                return null;
            }
            
            #endregion

        }


        /// <summary>
        /// 付款计划 单据编号BillCode 规则
        ///select RecNum,* from billcode where ruleid = '6wfq837d3f6vfdxd'
        ///付款计划单据标识：PJH   对应机构机构标识：NPW （默认药业机构） 自动补充：00000001（8位）
        /// </summary>
        /// <returns></returns>
        public static long GetPlanBillCodeRecNo(string EntId = "E1WB67UEYPG")
        {
            //var connection = @"Data Source =192.168.50.30; Initial Catalog = LJHYBZK; Persist Security Info = True; User ID = sa; Password = xq!@#2014;";
            //DataAccess.connectionString = connection;

            long id = 0;
            #region 查id

            string commandTextUser = "select RecNum from  "
            + "billcode Where ruleid=@ruleid";
            string resultUser;
            DataTable dt = new DataTable();

            List<SqlParameter> parametersUser = new List<SqlParameter>();
            parametersUser.Add(new SqlParameter("ruleid", "6wfq837d3f6vfdxd"));
            //DataAccess.GetValues(commandTextUser, ref dt, parametersUser.ToArray(), out resultUser);
            //List<BillCode> users = new List<BillCode>();

            //users = (List<BillCode>)ModelConvertHelper<BillCode>.ConvertToModel(dt);
            //BillCode user = users.ToArray()[0];

            //id = user.RECNUM;
            SqlConnection conn = null;
            SqlTransaction tran = null;
            bool successUser = DataAccess.GetOneValue(commandTextUser, parametersUser.ToArray(), out var v, out resultUser,out conn,out tran, true);
            id = long.Parse(v.ToString());

            string commandText = "Update billcode set RecNum = @id +1   " +
                " Where ruleid=@ruleid;" + commandTextUser;
            string result;

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter( "id", id ));
            parameters.Add(new SqlParameter( "ruleid", "6wfq837d3f6vfdxd" ));

            List<DataAccessCommand> list = new List<DataAccessCommand>();
            list.Add(new DataAccessCommand(commandText, parameters, CommandType.Text, true));
            bool success = DataAccess.ExecuteBatchCommands(list, out result, conn, tran);
            //bool success = DataAccess.GetOneValue(commandText, parameters, out var v, out result);
            //id = (long)v;
            #endregion
            if (success) return id + 1;
            else return 0;
        }


        public static void Main(string[] args)
        {
            var id = IDHelper.GetRecNo();

            string orderNo = id.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("","");//PurOrderDAL.ORGID
            String OrderCode = "PJH" + orgno//机构标识
                + sb.ToString();
            Console.Write("文本-:"+id);
        }
    }
}