Estsh-Yiri/Estsh.Core.Services/Repositories/VendorOrderPrintDal.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ApServerProvider;
using System.Collections;
using System.Text;
using System.Data;
using DbCommon;
using Estsh.Web.Util;

/***************************************************************************************************
 * 
 * 作者：王勇
 * 
 * *************************************************************************************************/
namespace Estsh.Core.Repositories
{
    /// <summary>
    /// 目视单打印模块的业务数据
    /// </summary>
    public class VendorOrderPrintDal : BaseApp
    {
        public VendorOrderPrintDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        {
        }

        public Hashtable getStockVendorOrderPrintByPage(int PageSize, int PageIndex, string str)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();
                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", "  (SELECT DISTINCT EBELN,WERKS ,LIFNR ,vendor_name ,'' AS PART_QTY ,( SELECT    [dbo].[sap2wms_ymd](zdate) ) + ' ' + ( SELECT  [dbo].[sap2wms_hms](zcjsj)) AS [TIME] ,( CASE STAFLG  WHEN '1' THEN '已发布'WHEN '2' THEN '已确认'WHEN '3' THEN '已备货' WHEN '4' THEN '已发货'WHEN '5' THEN '已收货'ELSE '' END ) AS STAFLG ,LGORT ,COMMENTS FROM    dbo.SAP_DSPO AS a LEFT JOIN dbo.sys_part AS b ON a.MATNR = b.part_no LEFT JOIN dbo.sys_vendor AS c ON a.LIFNR = c.vendor_code  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " * "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " a.ebeln    "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", str + " )AS a 	"));
                Hashtable values = new Hashtable(2);
                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                result.Add("dataList", dataList);
                result.Add("totalCount", values["@TotalCount"].ToString());
                return result;
            }
        }

        public Hashtable getStockOrderDetailByPage(int PageSize, int PageIndex, string str)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                List<StoreProcedureParameter> parameters = new List<StoreProcedureParameter>();

                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalCount", 100));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Output, "@TotalPage", 100));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", " dbo.WMS_DSPOD AS a  LEFT JOIN dbo.sys_part AS b ON a.matnr=b.part_no  "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", " * "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " evrtp "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@PageSize", PageSize));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@CurrentPage", PageIndex));
                parameters.Add(new StoreProcedureParameter(DbType.Int32, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", str));

                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                result.Add("dataList", dataList);
                result.Add("totalCount", values["@TotalCount"].ToString());

                return result;
            }
        }

        /// <summary>
        /// 修改条码打印状态
        /// </summary>
        /// <param name="ztxm"></param>
        /// <returns></returns>
        //public bool changeOrderStatus(string ztxm)
        //{
        //    lock (_remotingProxy)
        //    {
        //        StringBuilder SqlStringBuilder = new StringBuilder(1024);
        //        SqlStringBuilder.Append(" UPDATE dbo.WMS_DSPOD SET prnsta=CONVERT(INT,prnsta)+1 WHERE ztxm='" + ztxm + "'");

        //        return _remotingProxy.Exists(SqlStringBuilder.ToString());
        //    }
        //}

        public bool changeOrderStatus(string ebeln)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" UPDATE dbo.WMS_DSPOD SET prnsta=CONVERT(INT,prnsta)+1 WHERE ebeln='" + ebeln + "'");

                return _remotingProxy.Exists(SqlStringBuilder.ToString());
            }
        }

        /// <summary>
        /// 修改订单状态
        /// </summary>
        /// <param name="ztxm"></param>
        /// <returns></returns>
        public bool updateDspoSetStaflg(string PoNo_Data, string PartNo_Data, string TIME, string evrtp)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" UPDATE dbo.SAP_DSPO SET staflg=3 WHERE ebeln='" + PoNo_Data + "' and matnr='" + PartNo_Data + "' and zdate='" + TIME + "'");

                return _remotingProxy.Exists(SqlStringBuilder.ToString());
            }
        }


        /// <summary>
        /// 修改订单状态
        /// </summary>
        /// <param name="ztxm"></param>
        /// <returns></returns>
        public bool updateDspoSetStaflg(string EBELN)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                changeOrderStatus(EBELN);
                SqlStringBuilder.Append(" UPDATE dbo.SAP_DSPO SET staflg=3 WHERE ebeln='" + EBELN + "'");

                return _remotingProxy.Exists(SqlStringBuilder.ToString());
            }
        }
        /// <summary>
        /// 获取工厂
        /// </summary>
        /// <returns></returns>
        public DataTable GetFactory()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT factory_code as [value],factory_name as [key] FROM dbo.sys_factory ");

                return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
            }
        }

        /// <summary>
        /// 获取供应商
        /// </summary>
        /// <returns></returns>
        public DataTable GetVendor()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT vendor_code as [value],vendor_name as [key] FROM dbo.sys_vendor ");

                return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
            }
        }


        public DataTable updatePrintData(string ebeln)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                string SqlStrings = "";
                Hashtable Parameters = new Hashtable();

                Hashtable Params = new Hashtable(1);

                SqlStrings = " SELECT * FROM dbo.SAP_DSPO WHERE ebeln='" + ebeln + "' ORDER BY evrtp ";
                Hashtable values = new Hashtable(2);

                DataTable dt = new DataTable();
                dt = _remotingProxy.GetDataTable(SqlStrings, Parameters);
                //ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                //result.Add("dataList", dataList);

                return dt;
            }
        }

        /// <summary>
        /// 订单确认
        /// </summary>
        /// <returns></returns>
        public bool dingDanQueRen(string ebeln)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" UPDATE dbo.SAP_DSPO SET STAFLG=2 WHERE EBELN IN (" + ebeln + ") and STAFLG=1 ");

                int count = _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString());

                bool bool_1 = false;
                if (count > 0)
                {
                    bool_1 = true;
                }

                return bool_1;
            }
        }

        /// <summary>
        /// 发运确认
        /// </summary>
        /// <returns></returns>
        public bool shipQueRen(string ebeln)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" UPDATE dbo.SAP_DSPO SET STAFLG=4 WHERE EBELN IN (" + ebeln + ") and STAFLG=3 ");

                int count = _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString());

                bool bool_1 = false;
                if (count > 0)
                {
                    bool_1 = true;
                }

                return bool_1;
            }
        }

        /// <summary>
        /// 查询是否全部打印
        /// </summary>
        /// <returns></returns>
        public bool selectPrintState(string PoNo_Data, string PartNo_Data, string TIME, string evrtp)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT * FROM dbo.WMS_DSPOD WHERE ebeln= '" + PoNo_Data + "' AND matnr='" + PartNo_Data + "' AND evrtp='" + evrtp + "' AND prnsta=1 ");

                bool result = _remotingProxy.Exists(SqlStringBuilder.ToString());

                return result;
            }
        }

        /// <summary>
        /// 条码明细
        /// </summary>
        /// <returns></returns>
        public DataTable GetCodeDetail(string orderNo)
        {
            lock (_remotingProxy)
            {
                string sql = @"SELECT  f.factory_name, v.vendor_name, p.part_spec, p.option3,
                                    a.EBELN + '_' + a.REVNO as order_no, a.EVRTP, a.LIFNR, a.MATNR, a.ZDATE, a.WERKS,
                                    b.ZTXM, b.ZDEV_NUM, a.ZDEV_NUM, a.BSTRF,
                                    CONVERT(VARCHAR(4),CONVERT(INT,SUBSTRING(b.ztxm,14,4)))+'/'+CONVERT(VARCHAR(4),CEILING(a.ZDEV_NUM/a.BSTRF)) as seq
                            FROM    dbo.SAP_DSPO a
                                    JOIN dbo.WMS_DSPOD b ON a.EBELN = b.EBELN
                                                            AND a.EVRTP = b.EVRTP
                                    JOIN dbo.sys_factory f ON a.werks = f.factory_code
                                    JOIN dbo.sys_part p ON a.matnr = p.part_no
                                    JOIN dbo.sys_vendor v ON a.lifnr = v.vendor_code
                            WHERE   a.EBELN = '" + orderNo + "'order by a.ZDATE,a.ebeln,b.ZTXM asc";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 获取导入数据最早时间
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public DataTable GetStartDate(string orderNo)
        {
            lock (_remotingProxy)
            {
                string sql = @"select distinct dbo.sap2wms_ymd(a.zdate) as InDate from SAP_DSPO a 
                            where a.ebeln='" + orderNo + "' order by dbo.sap2wms_ymd(a.zdate) asc";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 获取第一模板明细
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public DataSet GetExpInfo(string orderNo)
        {
            lock (_remotingProxy)
            {
                string strSql = string.Empty;
                strSql += @" select DISTINCT a.lifnr,a.recymd,sv.vendor_name,sv.vendor_tel,sv.vendor_fax,sv.vendor_mtel,sv.vendor_sale,
                        se.emp_name,a.lgort
                        from SAP_DSPO a 
                        left join sys_vendor sv on a.lifnr=sv.vendor_code
                        left join sys_emp se on a.recusr=se.emp_id
                        where a.ebeln=@orderNo";
                //strSql += @" select DISTINCT recymd from SAP_DSPO where ebeln=@orderNo";

                strSql += @" select distinct a.matnr as PartNo,sp.part_spec as PartDesc,a.prnnum as Qty,dbo.sap2wms_ymd(a.zdate) as InDate 
                        from SAP_DSPO a left join sys_part sp on sp.part_no=a.matnr where a.ebeln=@orderNo 
                        order by a.matnr,dbo.sap2wms_ymd(a.zdate)";

                strSql += @" select distinct a.matnr,sp.part_spec from SAP_DSPO a left join sys_part sp on sp.part_no=a.matnr where a.ebeln=@orderNo";

                Hashtable param = new Hashtable(1);
                param.Add("@orderNo", orderNo);
                return _remotingProxy.GetDataSet(strSql, param);
            }
        }

        /// <summary>
        ///获取客户名称
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public string GetFactoryName()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select DISTINCT a.factory_name from sys_factory a  ");
                object obj = _remotingProxy.GetScalar(SqlStringBuilder.ToString());
                if (obj == null)
                    return string.Empty;
                else
                    return obj.ToString();
            }
        }

        /// <summary>
        /// 获取单号条码数量
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public int GetWmsCodeCount(string orderNo, string partNo, string evrtp)
        {
            lock (_remotingProxy)
            {
                string sql = "select COUNT(1) from WMS_DSPOD where ebeln='" + orderNo + "' and matnr='" + partNo + "' and evrtp='" + evrtp + "'";
                return Convert.ToInt32(_remotingProxy.GetScalar(sql));
            }
        }

        /// <summary>
        /// 根据周计划单号获取箱条码信息
        /// </summary>
        /// <param name="weekNo"></param>
        /// <returns></returns>
        public DataTable GetCartonNo(string weekNo)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select DISTINCT a.ebeln AS order_no,a.lifnr AS vendor_code,d.vendor_name,  ");
                SqlStringBuilder.Append("        a.matnr AS part_no, f.part_spec,w.zdev_num,  ");
                SqlStringBuilder.Append("        dbo.sap2wms_ymd(a.zdate) AS zdate, ");
                SqlStringBuilder.Append("        f.option3 ,w.ZTXM as carton_no,a.REVNO as version ,w.evrtp ");
                SqlStringBuilder.Append("from WMS_DSPOD w ");
                SqlStringBuilder.Append("        left join SAP_DSPO a on w.EBELN = a.ebeln and w.evrtp=a.evrtp ");
                SqlStringBuilder.Append("        LEFT JOIN sys_emp b ON a.recusr = b.emp_id  ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_vendor d ON a.lifnr = d.vendor_code ");
                SqlStringBuilder.Append("        LEFT JOIN dbo.sys_part f ON a.matnr = f.part_no  ");
                SqlStringBuilder.Append("WHERE   1 = 1 ");
                SqlStringBuilder.Append("        and a.ebeln = @weekno");
                SqlStringBuilder.Append("        order by a.ebeln,zdate,w.ZTXM asc");

                Hashtable ht = new Hashtable();
                ht.Add("@weekno", weekNo);

                return _remotingProxy.GetDataTable(SqlStringBuilder.ToString(), ht);
            }
        }
    }
}