MES_Client/TGMES/Core.StlMes.Client.Sale.BLL.SaleBusinessMgt/BaseData/SlmBaseCustomberBll.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CoreFS.CA06;

using System.ComponentModel;
using System.Drawing;
using System.Windows.Forms;
using Infragistics.Win.UltraWinGrid;
using System.Collections;
using ColumnDefine = Core.StlMes.Client.Sale.Common.TbColumnDefine.TbColumnDefine;


namespace Core.StlMes.Client.Sale.BLL.SaleBusinessMgt.BaseData
{
    public class SlmBaseCustomberBll : SaleBusinessMgt.BllBase.BaseBll 
    {
        public SlmBaseCustomberBll(OpeBase ob)
            : base(ob)
        {
            
        }

        /// <summary>
        /// 查询入口
        /// </summary>
        /// <param name="sqlCondition"></param>
        /// <returns></returns>
        private DataSet GetSlmBaseCustomer(string sqlCondition)
        {
            string sqlStr = string.Format(SqlCollection.BaseData._QUERYSLMBASECUSTOMER, sqlCondition, base.DateTimeTocharFormat);
            return base.ExecuteQuery(sqlStr);
        }

        private DataSet GetSlmBaseCustUnit(string sqlCondition)
        {
            string sqlStr = string.Format(SqlCollection.BaseData._QUERYSLMBASECUSTMUNITIF, sqlCondition, base.DateTimeTocharFormat);
            return base.ExecuteQuery(sqlStr);
        }

        private DataSet GetSlmBaseCustFreight(string sqlCondition)
        {
            string sqlStr = string.Format(SqlCollection.BaseData._QUERYSLMBASECUSTFERINFO, sqlCondition, base.DateTimeTocharFormat);
            return base.ExecuteQuery(sqlStr);
        }

        /// <summary>
        /// 通过子公司查找所有的客户信息
        /// </summary>
        /// <param name="sale_org"></param>
        /// <returns></returns>
        public DataSet GetSlmBaseCustomerByForecorpCode(string forecorpCode)
        {
            string sqlCondition = string.Format("  and forecorpcode = '{0}' ", forecorpCode);
            return GetSlmBaseCustomer(sqlCondition);
        }

        /// <summary>
        /// 查询有效客户基础信息
        /// </summary>
        /// <param name="validflag"></param>
        /// <returns></returns>
        public DataSet GetValidSlmBaseCustomber()
        {
            string sqlCondition = string.Format(" and validflag ='{0}'", "1");
            return GetSlmBaseCustomer(sqlCondition);
        }

        /// <summary>
        /// 查询无效客户基础信息
        /// </summary>
        /// <param name="validflag"></param>
        /// <returns></returns>
        public DataSet GetNotValidSlmBaseCustomber()
        {
            string sqlCondition = string.Format(" and validflag ='{0}'", "0");
            return GetSlmBaseCustomer(sqlCondition);
        }

        /// <summary>
        /// 根据客户编号查询客户基础信息
        /// </summary>
        /// <param name="customerNo"></param>
        /// <returns></returns>
        public DataSet GetSlmBaseCustomberByCustNo(string customerNo)
        {
            string sqlCondition = string.Format(" and customer_no = '{0}'",customerNo);
            return GetSlmBaseCustomer(sqlCondition);
        }

        /// <summary>
        /// 获取所有客户信息，包括有效和无效的
        /// </summary>
        /// <returns></returns>
        public DataSet GetSlmBaseCustomberAll()
        {
            return GetSlmBaseCustomer("");
        }

        /// <summary>
        /// 通过销售组合和客户编码获取客户信息
        /// </summary>
        /// <param name="sale_org"></param>
        /// <param name="customer_no"></param>
        /// <returns></returns>
        public DataSet GetGetSlmBaseCustomerInfoByForeCorpCodeAndCustomerNo(string forecorpcode, string customer_no)
        {
            string sqlCondition = string.Format("  and forecorpcode = '{0}'  and  customer_no = '{1}' ", forecorpcode, customer_no);
            return GetSlmBaseCustomer(sqlCondition);
        }

        /// <summary>
        /// 通过客户编号查询客户扩展信息
        /// </summary>
        /// <param name="CustmNo"></param>
        /// <returns></returns>
        public DataSet GetSlmBaseCustmNuitByCustmNo(string CustmNo)
        {
            string sqlCondition = string.Format(" and customer_no = '{0}'", CustmNo);
            return GetSlmBaseCustUnit(sqlCondition);
        }

        /// <summary>
        /// 根据客户编码查询货运信息
        /// </summary>
        /// <param name="CustmNo"></param>
        /// <returns></returns>
        public DataSet GetSlmBaseCustmFreightByCustNo(string CustmNo)
        { 
            string sqlCondition = " and customer_no = '"+CustmNo+"'";
            return GetSlmBaseCustFreight(sqlCondition);
        }

        /// <summary>
        /// 查询所有货运信息
        /// </summary>
        /// <returns></returns>
        public DataSet GetSlmBaseCustmFreightAll()
        {
            return GetSlmBaseCustFreight("");
        }

        /// <summary>
        /// 查询到站下拉框信息
        /// </summary>
        /// <returns></returns>
        public DataSet SelectFreightColumn()
        {
            string sql = "select station_no,station_nm from slm_base_station where station_nm != 'null' or station_nm != ''";
            return base.ExecuteQuery(sql);
        }

        /// <summary>
        /// 查询汽运下拉框
        /// </summary>
        /// <returns></returns>
        public DataSet SelectLoadDockColumn()
        {
            string sql = "select station_no,loaddock from slm_base_station where loaddock is not null";
            return base.ExecuteQuery(sql);
        }

        /// <summary>
        /// 查询船运下拉框
        /// </summary>
        /// <returns></returns>
        public DataSet SelectUnLoadDockColumn()
        {
            string sql = "select station_no,unloaddock from slm_base_station where loaddock is not null";
            return base.ExecuteQuery(sql);
        }

        /// <summary>
        /// 新增客户基础信息
        /// </summary>
        /// <param name="cust"></param>
        /// <param name="errMsg"></param>
        public void Insert(Hashtable hs,out string errMsg)
        {
            errMsg = "";
            hs.Add(ColumnDefine.SLM_BASE_CUSTOMER.CUSTOMER_NO, GetSlmBaseCustomberCode());
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "insert", new object[] { hs });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 新增扩展信息
        /// </summary>
        /// <param name="hs"></param>
        /// <param name="errMsg"></param>
        public void CustmUnitInsert(Hashtable hs, out string errMsg)
        {
            errMsg = "";
            hs.Add(ColumnDefine.SLM_BASE_CUSTM_UNIT.CUST_SEQ, GetSlmBaseCustmUnitInfoCode());
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "custUnitInsert", new object[] { hs });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 新增货运信息
        /// </summary>
        /// <param name="hs"></param>
        /// <param name="errMsg"></param>
        public void FreightInfoInsert(Hashtable hs, out string errMsg)
        {
            errMsg = "";
            hs.Add(ColumnDefine.SLM_BASE_CUSTM_FREIGHT.FREIGHT_NO, GetSlmBaseCustFreightInfoCode());
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "freightInsert", new object[] { hs });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 扩展信息删除
        /// </summary>
        /// <param name="custSeq"></param>
        public void CustUnitDelete(string custSeq,out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "custUnitDelete", new object[] { custSeq });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 货运信息删除
        /// </summary>
        /// <param name="freigtNo"></param>
        /// <param name="errMsg"></param>
        public void FreightDelete(string freigtNo, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "freightDelete", new object[] { freigtNo });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 扩展信息修改
        /// </summary>
        /// <param name="hs"></param>
        /// <param name="errMsg"></param>
        public void CustmUnitUpdate(Hashtable hs, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "custUnitUpdate", new object[] { hs });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 货运信息修改
        /// </summary>
        /// <param name="hs"></param>
        /// <param name="errMsg"></param>
        public void FreightUpdate(Hashtable hs, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "freightUpdate", new object[] { hs });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 客户等级修改
        /// </summary>
        /// <param name="custNo"></param>
        /// <param name="custlev"></param>
        /// <param name="errMsg"></param>
        public void CustLveUpdate(string custNo, string custlev, out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "custLevUpdate", new object[] { custNo, custlev });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 删除客户基础信息
        /// </summary>
        /// <param name="customer_no"></param>
        /// <param name="deleteName"></param>
        public void Delete(string customer_no, string deleteName,out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "delete", new object[] { customer_no, deleteName });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 取消删除
        /// </summary>
        /// <param name="customer_no"></param>
        public void unDelete(string customer_no,out string errMsg) 
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "unDelete", new object[] { customer_no });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            if (result.ReturnInfo == Network_error)
            {
                result.ReturnInfo = "";
            }
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 修改客户基础信息
        /// </summary>
        /// <param name="cust"></param>
        /// <param name="errMsg"></param>
        public void Update(Hashtable ht,string nowCustomerNm ,out string errMsg)
        {
            errMsg = "";
            CoreClientParam param = base.GetCoreClientParam(_ServerName, "update", new object[] { ht, nowCustomerNm });
            CoreClientParam result = base.fr.ExecuteNonQuery(param, CoreInvokeType.Internal);
            errMsg = base.GetReturnErrorInfo(result);
        }

        /// <summary>
        /// 获取数据行数
        /// </summary>
        /// <returns></returns>
        private double GetCoutOfSlmBaseCustomber()
        {
            string sqlStr = "select count(1) from slm_base_customer ";
            DataSet ds = base.ExecuteQuery(sqlStr);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return 0;
            return Util.ConverObject.ConvertToDouble(ds.Tables[0].Rows[0][0]);
        }

        /// <summary>
        /// 自动生成客户编码
        /// </summary>
        /// <returns></returns>
        private string GetSlmBaseCustomberCode()
        {

            string customberno = "";
            string sqlStr = @"select * "+
                             "from (select lpad(l || '', 6, 0) customberno, a.customer_no, rownum rn " +
                             "from (select level - 1 l from dual connect by level < {0}) b "+
                             "left join slm_base_customer a on a.customer_no = " +
                             "lpad(b.l || '', 6, 0) "+
                             "where a.customer_no is null " +
                             "order by b.l) "+
                             "where rn = 1";
            sqlStr = string.Format(sqlStr, GetCoutOfSlmBaseCustomber() + 100);
            DataSet ds = base.ExecuteQuery(sqlStr);
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    customberno = "000001";
                else
                    customberno = ds.Tables[0].Rows[0]["CUSTOMBERNO"].ToString();
            }
            catch
            {
            }
            return customberno;
        }

        /// <summary>
        /// 获取数据行数
        /// </summary>
        /// <returns></returns>
        private double GetCoutOfSlmBaseCustmUnitInfo()
        {
            string sqlStr = "select count(1) from slm_base_custm_unit ";
            DataSet ds = base.ExecuteQuery(sqlStr);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return 0;
            return Util.ConverObject.ConvertToDouble(ds.Tables[0].Rows[0][0]);
        }

        /// <summary>
        /// 自动生成客户编码
        /// </summary>
        /// <returns></returns>
        private string GetSlmBaseCustmUnitInfoCode()
        {

            string customberno = "";
            string sqlStr = @"select * " +
                             "from (select lpad(l || '', 6, 0) customberno, a.cust_seq, rownum rn " +
                             "from (select level - 1 l from dual connect by level < {0}) b " +
                             "left join slm_base_custm_unit a on a.cust_seq = " +
                             "lpad(b.l || '', 6, 0) " +
                             "where a.cust_seq is null " +
                             "order by b.l) " +
                             "where rn = 1";
            sqlStr = string.Format(sqlStr, GetCoutOfSlmBaseCustmUnitInfo() + 100);
            DataSet ds = base.ExecuteQuery(sqlStr);
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    customberno = "000001";
                else
                    customberno = ds.Tables[0].Rows[0]["CUSTOMBERNO"].ToString();
            }
            catch
            {
            }
            return customberno;
        }

        /// <summary>
        /// 获取数据行数
        /// </summary>
        /// <returns></returns>
        private double GetSlmFreightCount()
        {
            string sqlStr = "select count(1) from slm_base_custm_freight ";
            DataSet ds = base.ExecuteQuery(sqlStr);
            if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
                return 0;
            return Util.ConverObject.ConvertToDouble(ds.Tables[0].Rows[0][0]);
        }

        /// <summary>
        /// 自动生成客户编码
        /// </summary>
        /// <returns></returns>
        private string GetSlmBaseCustFreightInfoCode()
        {

            string customberno = "";
            string sqlStr = @"select * " +
                             "from (select lpad(l || '', 6, 0) customberno, a.freight_no, rownum rn " +
                             "from (select level - 1 l from dual connect by level < {0}) b " +
                             "left join slm_base_custm_freight a on a.freight_no = " +
                             "lpad(b.l || '', 6, 0) " +
                             "where a.freight_no is null " +
                             "order by b.l) " +
                             "where rn = 1";
            sqlStr = string.Format(sqlStr, GetSlmFreightCount() + 100);
            DataSet ds = base.ExecuteQuery(sqlStr);
            try
            {
                if (ds.Tables[0].Rows.Count == 0)
                    customberno = "000001";
                else
                    customberno = ds.Tables[0].Rows[0]["CUSTOMBERNO"].ToString();
            }
            catch
            {
            }
            return customberno;
        }

        protected const string Network_error = "错误：网络错误";
        private string _ServerName = "core.stlmes.server.sale.service.basedata.SlmBaseCustomber";
    }
}