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

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

namespace Estsh.Core.Repositories
{
    public class EDIKeyDataDefineDal : BaseApp
    {
        public EDIKeyDataDefineDal(RemotingProxy remotingProxy)
            : base(remotingProxy)
        { }


        /// <summary>
        /// 根据查询条件得出结果集
        /// </summary>
        /// <param name="aWhere">查询条件</param>
        /// <returns>符合条件的结果集</returns>
        public Hashtable GetQuery(string strWhere, int PageSize, int PageIndex)
        {
            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));

                StringBuilder SqlStringBuilder = new StringBuilder();
                SqlStringBuilder.Append(" (SELECT DISTINCT b.billId, b.csn,b.model_id,b.vin,d.model_name,d.model_desc,b.remark,b.is_production,b.is_shipping  ");
                SqlStringBuilder.Append("   FROM dbo.magna_edi(NOLOCK) AS a   ");
                SqlStringBuilder.Append("   LEFT JOIN dbo.magne_edi_detail(NOLOCK) AS b ON b.billId = a.billId  ");
                SqlStringBuilder.Append("	LEFT JOIN dbo.sys_part(NOLOCK) AS c ON b.materialId=c.part_no_3c  ");
                SqlStringBuilder.Append("	LEFT JOIN  dbo.sys_model(NOLOCK) AS d ON b.model_id=d.model_id  ");
                SqlStringBuilder.Append("	WHERE a.plantId=3302 AND c.enabled='Y' " + strWhere + " ) as bb ");
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", SqlStringBuilder.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", "*"));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", " csn "));
                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", ""));


              

                Hashtable values = new Hashtable(2);

                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 DataTable GetRow(string aWhere)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  DISTINCT b.billId, b.csn,b.model_id,b.vin,d.model_name,d.model_desc,b.remark ,b.is_production,b.is_shipping  ");
                SqlStringBuilder.Append(" From dbo.magne_edi_detail(NOLOCK) AS b   ");
                SqlStringBuilder.Append(" LEFT JOIN dbo.sys_part(NOLOCK) AS c ON b.materialId=c.part_no_3c ");
                SqlStringBuilder.Append(" LEFT JOIN  dbo.sys_model(NOLOCK) AS d ON b.model_id=d.model_id   ");
                SqlStringBuilder.Append("  WHERE 1= 1  ");
               
                if (!string.IsNullOrEmpty(aWhere))
                {
                    SqlStringBuilder.Append(aWhere);
                }

                return _remotingProxy.GetDataTable(SqlStringBuilder.ToString());
            }
        }
        //public int insert(string part_no, string process_id, string keydata_id, string item_count)
        //{
        //    lock (_remotingProxy)
        //    {
        //        StringBuilder SqlStringBuilder = new StringBuilder(1024);
        //        SqlStringBuilder.Append("INSERT  INTO sys_part_keydata ");
        //        SqlStringBuilder.Append("        ( part_id , ");
        //        SqlStringBuilder.Append("          process_id , ");
        //        SqlStringBuilder.Append("          terminal_id , ");
        //        SqlStringBuilder.Append("          keydata_id , ");
        //        SqlStringBuilder.Append("          item_count , ");
        //        SqlStringBuilder.Append("          guid ");
        //        SqlStringBuilder.Append("        ) ");
        //        SqlStringBuilder.Append("VALUES  ( ( SELECT TOP 1 ");
        //        SqlStringBuilder.Append("                    part_id ");
        //        SqlStringBuilder.Append("            FROM    dbo.sys_part ");
        //        SqlStringBuilder.Append("            WHERE   part_no = @part_no ");
        //        SqlStringBuilder.Append("          ) , ");
        //        SqlStringBuilder.Append("          @process_id , ");
        //        SqlStringBuilder.Append("          ( SELECT TOP 1 ");
        //        SqlStringBuilder.Append("                    terminal_id ");
        //        SqlStringBuilder.Append("            FROM    dbo.sys_terminal ");
        //        SqlStringBuilder.Append("            WHERE   process_id = @process_id ");
        //        SqlStringBuilder.Append("          ) , ");
        //        SqlStringBuilder.Append("          @keydata_id , ");
        //        SqlStringBuilder.Append("          @item_count , ");
        //        SqlStringBuilder.Append("          NEWID() ");
        //        SqlStringBuilder.Append("        ) ");

        //        Hashtable values = new Hashtable();

        //        values.Add("@part_no", part_no);
        //        values.Add("@process_id", process_id);
        //        values.Add("@keydata_id", keydata_id);
        //        values.Add("@item_count", item_count);
        //        return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), values);
        //    }
        //}


        public string inEdiData(string csn, string vin,string billid, string model,string isProduction, string isShipping)
        {
            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@csn", csn));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@vin", vin));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@billid", billid));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@model", model));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@isProduction", isProduction));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@isShipping", isShipping));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.InYYEdiData", Parameters);

                if (!ht.ContainsKey("@tres"))
                    return string.Empty;

                return ht["@tres"].ToString();
            }
        }
      
        public bool GetEDICSN(string CSN)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT  * FROM  dbo.magne_edi_detail(NOLOCK) WHERE  csn=@CSN ");

                Hashtable values = new Hashtable();

                values.Add("@CSN", CSN);
                return _remotingProxy.GetScalar(SqlStringBuilder.ToString(), values) != null;
            }
        }

        public bool GetEDIVIN(string VIN)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT  * FROM  dbo.magne_edi_detail(NOLOCK) WHERE  csn=@VIN ");

                Hashtable values = new Hashtable();

                values.Add("@VIN", VIN);
                return _remotingProxy.GetScalar(SqlStringBuilder.ToString(), values) != null;
            }
        }
        public bool GetEDIBillId(string BillID)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT  * FROM  dbo.magne_edi_detail(NOLOCK) WHERE  billId=@BillID ");

                Hashtable values = new Hashtable();

                values.Add("@BillID", BillID);
                return _remotingProxy.GetScalar(SqlStringBuilder.ToString(), values) != null;
            }
        }



        public ArrayList GetModelID()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append("SELECT model_id AS [value],model_name AS [key] FROM  dbo.sys_model WHERE enabled='Y'");

                return DataTypeConvert.NewObject.DataTableToArrayList(_remotingProxy.GetDataTable(SqlStringBuilder.ToString()));
            }
        }

        /// <summary>
        /// 获取关键数据信息
        /// </summary>
        /// <returns></returns>
        public ArrayList GetKeyDataID()
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);

                SqlStringBuilder.Append(" select  keydata_id as [value],keydata_name as [key] from sys_keydata ");

                return DataTypeConvert.NewObject.DataTableToArrayList(_remotingProxy.GetDataTable(SqlStringBuilder.ToString()));
            }
        }

        /// <summary>
        ///更改可用状态
        /// </summary>
        /// <param name="status">true or false</param>
        /// <param name="dr">当前行</param>
        /// <returns></returns>
        public bool ChangeDetailStatus(bool status, DataRow dr)
        {
            lock (_remotingProxy)
            {
                string guid = dr["guid"].ToString();

                string enabled = string.Empty;

                //string partID = GetPartMessage(partNo).Rows[0][0].ToString();

                string SqlString = "update dbo.sys_part_keydata set enabled=@status where guid=@part_id";

                if (status)
                {
                    enabled = "Y";
                }
                else
                {
                    enabled = "N";
                }

                Hashtable values = new Hashtable();

                values.Add("@status", enabled);
                values.Add("@part_id", guid);

                return _remotingProxy.ExecuteNonQuery(SqlString, values) > 0;
            }
        }

        public int DeleteDetail(string guid)
        {
            lock (_remotingProxy)
            {
                string SqlString = "delete from sys_part_keydata where guid in (" + guid + ")";

                return _remotingProxy.ExecuteNonQuery(SqlString);
            }
        }

        /// <summary>
        /// 更新关键数据和零件关系
        /// </summary>
        /// <param name="guid">guid</param>
        /// <param name="processid">制程编号</param>
        /// <param name="partid">零件编号</param>
        /// <param name="keydataid">关键数据编号</param>
        /// <param name="itemvalue">用量</param>
        /// <returns></returns>
        public string UpdateKeyData(string csn, string vin, string billid, string model, string isProduction, string isShipping)
        {
        

            lock (_remotingProxy)
            {
                List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@csn", csn));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@vin", vin));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@billid", billid));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@model", model));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@isProduction", isProduction));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@isShipping", isShipping));

                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.UpYYEdiData", Parameters);

                if (!ht.ContainsKey("@tres"))
                    return string.Empty;

                return ht["@tres"].ToString();
            }
        }


        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public DataTable GetEDIData(string aWhere, Pager pager, ref int totalCount)
        {
            lock (_remotingProxy)
            {
                Hashtable ht = 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));
                StringBuilder SqlStringBuilder = new StringBuilder();
                SqlStringBuilder.Append(" (SELECT DISTINCT b.billId, b.csn,b.model_id,b.vin,d.model_name,d.model_desc,b.remark,b.is_production,b.is_shipping  ");
                SqlStringBuilder.Append("   FROM dbo.magna_edi(NOLOCK) AS a   ");
                SqlStringBuilder.Append("   LEFT JOIN dbo.magne_edi_detail(NOLOCK) AS b ON b.billId = a.billId  ");
                SqlStringBuilder.Append("	LEFT JOIN dbo.sys_part(NOLOCK) AS c ON b.materialId=c.part_no_3c  ");
                SqlStringBuilder.Append("	LEFT JOIN  dbo.sys_model(NOLOCK) AS d ON b.model_id=d.model_id  ");
                SqlStringBuilder.Append("	WHERE a.plantId=3302 AND b.status=90 AND c.enabled='Y' " + aWhere + " ) as bb ");
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Table", SqlStringBuilder.ToString()));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", @"* "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@orderColumn", " csn "));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@GroupColumn", ""));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@PageSize", pager.pageSize));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@CurrentPage", pager.pageNo));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Group", 0));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Condition", ""));
                Hashtable values = new Hashtable(2);
                DataTable dt = new DataTable();
                dt = _remotingProxy.ExecuteSotreProcedure("Com_Pagination", parameters, ref values);
                ArrayList dataList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
                ht.Add("dataList", dataList);
                ht.Add("totalCount", values["@TotalCount"].ToString());
                totalCount = Convert.ToInt32(values["@TotalCount"]);
                return dt;
            }
        }


        ///// <summary>
        ///// 获取分页数据列表
        ///// </summary>
        //public DataTable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        //{
        //    lock (_remotingProxy)
        //    {
        //        if (OrderBy.Trim() != "")
        //        {
        //            OrderBy = " " + OrderBy;  //排序
        //        }
        //        Hashtable Params = new Hashtable(6);

        //        StringBuilder sb = new StringBuilder();
        //        sb.Append(" ( SELECT TOP 3000 ");
        //        sb.Append(" a.[guid] ,");
        //        sb.Append(" a.item_count ,");
        //        sb.Append(" b.part_no ,");
        //        sb.Append(" c.process_name ,");
        //        sb.Append(" d.keydata_name, ");
        //        sb.Append(" e.terminal_name ");
        //        sb.Append(" FROM    dbo.sys_part_keydata a ");
        //        sb.Append(" LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
        //        sb.Append(" LEFT JOIN dbo.sys_process c ON a.process_id = c.process_id ");
        //        sb.Append(" LEFT JOIN dbo.sys_keydata d ON a.keydata_id = d.keydata_id ");
        //        sb.Append(" LEFT JOIN dbo.sys_terminal e ON a.terminal_id=e.terminal_id ");
        //        sb.Append(" WHERE   a.enabled = 'Y' " + strWhere + " ) AS z ");


        //        Params.Add("@TotalCount", 100);
        //        Params.Add("@TotalPage", 100);
        //        Params.Add("@GroupColumn", "");
        //        Params.Add("@Table", sb.ToString());
        //        Params.Add("@Column", "*");
        //        Params.Add("@PageSize", PageSize);
        //        Params.Add("@CurrentPage", PageIndex);
        //        Params.Add("@Condition", " 1=1 ");
        //        Params.Add("@OrderColumn", " z.part_no ");
        //        Params.Add("@Group", 0);

        //        String strProcedure = "EXEC Com_Pagination @TotalCount, @TotalPage, @Table, @Column,@OrderColumn,@GroupColumn,@PageSize, @CurrentPage,@Group,@Condition";
        //        return _remotingProxy.GetDataTable(strProcedure, Params);
        //    }
        //}


        ///// <summary>
        ///// 导入数据方法
        ///// </summary>
        ///// <param name="dt">需要导入的数据表</param>
        ///// <returns>true or false</returns>
        //public bool InsertData(DataTable dt)
        //{
        //    lock (_remotingProxy)
        //    {
        //        List<String> SqlStrings = new List<string>(dt.Rows.Count);

        //        List<Hashtable> hs = new List<Hashtable>(dt.Rows.Count);

        //        for (int i = 0; i < dt.Rows.Count; i++)
        //        {
                   

        //            StringBuilder SqlStringBuilder = new StringBuilder(1024);


        //            lock (_remotingProxy)
        //            {
        //                List<StoreProcedureParameter> Parameters = new List<StoreProcedureParameter>();

        //                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@csn", csn));

        //                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@vin", vin));

        //                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@billid", billid));

        //                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@model", model));

        //                Parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Output, "@tres", 500));

        //                Hashtable ht = _remotingProxy.ExecuteSotreProcedure("dbo.UpYYEdiData", Parameters);

        //                if (!ht.ContainsKey("@tres"))
        //                    return string.Empty;

        //                return ht["@tres"].ToString();
        //            }
        //            //SqlStringBuilder.Append("INSERT INTO sys_part_keydata (part_id,process_id,terminal_id,item_count,keydata_id) ");
        //            //SqlStringBuilder.Append("VALUES(@part_id,@process_id,@terminal_id,@item_count,@keydata_id)");

        //            Hashtable values = new Hashtable(5);

        //            values.Add("@part_id", dt.Rows[i][""]);
        //            values.Add("@process_id", processID);
        //            values.Add("@terminal_id", terminalID);
        //            values.Add("@item_count", qty);
        //            values.Add("@keydata_id", keydataID);

        //            SqlStrings.Add(SqlStringBuilder.ToString());

        //            hs.Add(values);
        //        }

        //        return _remotingProxy.ExecuteSqlTransaction(SqlStrings, hs);
        //    }

        //}


        public int GetCSN(string csn)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT  * FROM  dbo.magne_edi_detail(NOLOCK) WHERE  csn=@CSN ";

                Hashtable Params = new Hashtable(1);

                Params.Add("@csn", csn);

                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);

                if (dt == null)
                    return 0;

                if (dt.Rows.Count == 0)
                    return 0;

                return Convert.ToInt32(dt.Rows[0]["runningNumber"].ToString());
            }
        }

 
        public int GetVIN(string vin)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT  * FROM  dbo.magne_edi_detail(NOLOCK) WHERE  vin=@vin ";

                Hashtable Params = new Hashtable(1);

                Params.Add("@vin", vin);

                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);

                if (dt == null)
                    return 0;

                if (dt.Rows.Count == 0)
                    return 0;

                return Convert.ToInt32(dt.Rows[0]["runningNumber"].ToString());
            }
        }


        public int GetBillID(string billid)
        {
            lock (_remotingProxy)
            {
                //lvf 202105014 加入校验，若在汇总或明细表中存在数据，则报错
                string SqlString = @"SELECT  billId FROM  dbo.magne_edi_detail(NOLOCK) WHERE  billId= @billid 
    UNION ALL SELECT billId FROM magna_edi WHERE billId = @billid";

                Hashtable Params = new Hashtable(1);

                Params.Add("@billid", billid);

                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);

                if (dt == null)
                    return 0;

                if (dt.Rows.Count == 0)
                    return 0;

                return dt.Rows.Count;
            }
        }

        /// <summary>
        /// 获取keydataID
        /// </summary>
        /// <param name="partNo"></param>
        /// <returns></returns>
        public int GetModel(string modelname)
        {
            lock (_remotingProxy)
            {
                string SqlString = "SELECT  model_id FROM  dbo.sys_model WHERE model_name=@modelname AND enabled='Y' ";

                Hashtable Params = new Hashtable(1);

                Params.Add("@modelname", modelname);

                DataTable dt = _remotingProxy.GetDataTable(SqlString, Params);

                if (dt == null)
                    return 0;

                if (dt.Rows.Count == 0)
                    return 0;

                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }
        }
    }
}