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

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


//2014-07-07 Ada.Li 零件号分级管理

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

        /// <summary>
        /// 获取零件显示清单
        /// </summary>
        /// <param name="strWhere">查询条件</param>
        /// <param name="filedOrder">排序条件</param>
        /// <returns></returns>
        public DataTable GetLevelPartList(string partID)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" SELECT a.*, b.model_name, c.route_name, d.locate_name, e.vendor_name, ");
                SqlStringBuilder.Append("        f.factory_name, g.customer_name, h.enum_desc settle, ");
                SqlStringBuilder.Append("        i.enum_desc supply, j.enum_desc instock, k.enum_desc option_10, ");
                SqlStringBuilder.Append("        l.enum_desc uom_desc, m.enum_desc sn_proc, ");
                SqlStringBuilder.Append("        n.enum_desc part_location_desc ");
                SqlStringBuilder.Append(" FROM   sys_part a ");
                SqlStringBuilder.Append("        LEFT JOIN sys_model b ON a.model_id = b.model_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_route c ON a.route_id = c.route_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_locate d ON a.locate_id = d.locate_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_vendor e ON a.vendor_id = e.vendor_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_factory f ON a.factory_code = f.factory_code ");
                SqlStringBuilder.Append("        LEFT JOIN sys_customer g ON a.custome_id = g.customer_id ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum h ON a.settle_type = h.enum_value ");
                SqlStringBuilder.Append("                                AND h.enum_type = 'sys_part_settle_type' ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum i ON a.supply_type = i.enum_value ");
                SqlStringBuilder.Append("                                AND i.enum_type = 'sys_part_supply_type' ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum j ON a.instock_type = j.enum_value ");
                SqlStringBuilder.Append("                                AND j.enum_type = 'sys_part_instock_type' ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum k ON a.option10 = k.enum_value ");
                SqlStringBuilder.Append("                                AND k.enum_type = 'sys_part_option' ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum l ON a.uom = l.enum_value ");
                SqlStringBuilder.Append("                                AND l.enum_type = 'sys_part_uom' ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum m ON a.rule_set = m.enum_value ");
                SqlStringBuilder.Append("                                AND m.enum_type = 'sys_create_sn_proc' ");
                SqlStringBuilder.Append("        LEFT JOIN sys_enum n ON a.part_location = n.enum_value ");
                SqlStringBuilder.Append("                                AND n.enum_type = 'sys_part_location'   ");
                SqlStringBuilder.Append(" WHERE a.part_id = " + partID + " ");

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

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        /// <param name="PageSize">一页显示多少条数据</param>
        /// <param name="PageIndex">当前第几页</param>
        /// <param name="strWhere">条件</param>
        /// <param name="OrderBy">排序字段</param>
        /// <returns></returns>
        public Hashtable getLevelPartListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            lock (_remotingProxy)
            {
                Hashtable result = new Hashtable();

                //关联表
                string tableName = "sys_part a ";
                tableName += " LEFT JOIN sys_model b ON a.model_id = b.model_id ";
                tableName += " LEFT JOIN sys_route c ON a.route_id = c.route_id ";
                tableName += " LEFT JOIN sys_locate d ON a.locate_id = d.locate_id ";
                tableName += " LEFT JOIN sys_vendor e ON a.vendor_id = e.vendor_id";
                tableName += " LEFT JOIN sys_factory f ON a.factory_code = f.factory_code";
                tableName += " LEFT JOIN sys_customer g ON a.custome_id = g.customer_id";
                tableName += " LEFT JOIN sys_enum h ON a.settle_type = h.enum_value";
                tableName += "                    AND h.enum_type = 'sys_part_settle_type'";
                tableName += " LEFT JOIN sys_enum i ON a.supply_type = i.enum_value";
                tableName += "                    AND i.enum_type = 'sys_part_supply_type'";
                tableName += " LEFT JOIN sys_enum j ON a.instock_type = j.enum_value";
                tableName += "                    AND j.enum_type = 'sys_part_instock_type'";
                tableName += " LEFT JOIN sys_enum k ON a.option10 = k.enum_value";
                tableName += "                   AND k.enum_type = 'sys_part_option'";
                tableName += " LEFT JOIN sys_enum l ON a.uom = l.enum_value";
                tableName += "                    AND l.enum_type = 'sys_part_uom'";
                tableName += " LEFT JOIN sys_enum m ON a.rule_set = m.enum_value";
                tableName += "                    AND m.enum_type = 'sys_create_sn_proc'";
                tableName += " LEFT JOIN sys_enum n ON a.part_location = n.enum_value";
                tableName += "                    AND n.enum_type = 'sys_part_location'";

                string columnName = " a.*, b.model_name, c.route_name, d.locate_name, e.vendor_name, ";
                columnName += " f.factory_name, g.customer_name, h.enum_desc settle,";
                columnName += " i.enum_desc supply, j.enum_desc instock, k.enum_desc option_10,";
                columnName += " l.enum_desc uom_desc, m.enum_desc sn_proc,";
                columnName += " n.enum_desc part_location_desc";

                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", tableName));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@Column", columnName));
                parameters.Add(new StoreProcedureParameter(DbType.String, ParameterDirection.Input, "@OrderColumn", OrderBy));
                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", strWhere));

                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="enumType">枚举类型</param>
        /// <returns></returns>
        public DataTable getEnum(string enumType)
        {
            lock (_remotingProxy)
            {
                string sql = "select enum_value as [value], enum_desc as [key] from sys_enum where enum_type = '" + enumType + "' order by enum_value";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 查询零件配置
        /// </summary>
        /// <returns></returns>
        public DataTable getModelName()
        {
            lock (_remotingProxy)
            {
                string sql = "SELECT  model_id as [value], model_name as [key] FROM dbo.sys_model ORDER BY model_name";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 查询供应商信息
        /// </summary>
        /// <returns></returns>
        public DataTable getVendor()
        {
            lock (_remotingProxy)
            {
                string sql = "SELECT vendor_id AS [value] , vendor_name AS [key] FROM dbo.sys_vendor WHERE enabled = 'Y' ORDER BY vendor_name";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 查询零件默认库位
        /// </summary>
        /// <returns></returns>
        public DataTable getLocate()
        {
            lock (_remotingProxy)
            {
                string sql = "SELECT locate_id AS [value], locate_name AS [key] FROM dbo.sys_locate WHERE enabled = 'Y' ORDER BY locate_name";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 查询客户信息
        /// </summary>
        /// <returns></returns>
        public DataTable getCustomer()
        {
            lock (_remotingProxy)
            {
                string sql = "select customer_id as [value] ,customer_name as [key] from sys_customer order by customer_name";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 查询工艺流程信息
        /// </summary>
        /// <returns></returns>
        public DataTable getRoute()
        {
            lock (_remotingProxy)
            {
                string sql = "select route_id as [value], route_name as [key] from sys_route where enabled='Y' order by route_name";

                return _remotingProxy.GetDataTable(sql);
            }
        }
        
        /// <summary>
        /// 查询厂区信息
        /// </summary>
        /// <returns></returns>
        public DataTable getFactory()
        {
            lock (_remotingProxy)
            {
                string sql = "SELECT factory_id AS [value] , factory_code AS [key] FROM dbo.sys_factory WHERE enabled = 'Y' ORDER BY factory_name";

                return _remotingProxy.GetDataTable(sql);
            }
        }

        /// <summary>
        /// 修改更新
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateLevelPartMasterDefine(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("UPDATE  dbo.sys_part ");
                SqlStringBuilder.Append("SET     part_no = @part_no, part_spec = @part_spec, part_spec2 = @part_spec2, ");
                SqlStringBuilder.Append("        part_location = @part_location, part_type = @part_type, ");
                SqlStringBuilder.Append("        part_class = @part_class, model_id = @model_id, ");
                SqlStringBuilder.Append("        label_file = @label_file, label_type = @label_type, ");
                SqlStringBuilder.Append("        label_qty = @label_qty, erp_part_no = @erp_part_no, ");
                SqlStringBuilder.Append("        rule_set = @rule_set, uom = @uom, route_id = @route_id, ");
                SqlStringBuilder.Append("        locate_id = @locate_id, upc_code = @upc_code, ucc_code = @ucc_code, ");
                SqlStringBuilder.Append("        ean_code = @ean_code, burnin_time = @burnin_time, version = @version, ");
                SqlStringBuilder.Append("        custome_id = @custome_id, vendor_id = @vendor_id, ");
                SqlStringBuilder.Append("        cust_part_no = @cust_part_no, vendor_part_no = @vendor_part_no, ");
                SqlStringBuilder.Append("        mfger_part_no = @mfger_part_no, subboard_qty = @subboard_qty, ");
                SqlStringBuilder.Append("        split_flag = @split_flag, batch_control = @batch_control, ");
                SqlStringBuilder.Append("        min_pack_qty = @min_pack_qty, min_stock_qty = @min_stock_qty, ");
                SqlStringBuilder.Append("        max_stock_qty = @max_stock_qty, wip_min_stock_qty = @wip_min_stock_qty, ");
                SqlStringBuilder.Append("        indate_flag = @indate_flag, indate = @indate, ");
                SqlStringBuilder.Append("        sampling_plan_id = @sampling_plan_id, over_request = @over_request, ");
                SqlStringBuilder.Append("        default_box_qty = @default_box_qty, ");
                SqlStringBuilder.Append("        default_reel_qty = @default_reel_qty, ");
                SqlStringBuilder.Append("        default_set_qty = @default_set_qty, supply_type = @supply_type, ");
                SqlStringBuilder.Append("        settle_type = @settle_type, recheck_cycle = @recheck_cycle, ");
                SqlStringBuilder.Append("        instock_type = @instock_type, package_size = @package_size, ");
                SqlStringBuilder.Append("        part_no_3c = @part_no_3c, part_abc = @part_abc, ");
                SqlStringBuilder.Append("        part_group = @part_group, uom1 = @uom1, coefficient1 = @coefficient1, ");
                SqlStringBuilder.Append("        uom2 = @uom2, coefficient2 = @coefficient2, uom3 = @uom3, ");
                SqlStringBuilder.Append("        coefficient3 = @coefficient3, factory_code = @factory_code, ");
                SqlStringBuilder.Append("        mc_code = @mc_code, option1 = @option1, option2 = @option2, ");
                SqlStringBuilder.Append("        option3 = @option3, option5 = @option5, ");
                SqlStringBuilder.Append("        option6 = @option6, option7 = @option7, option8 = @option8, ");
                SqlStringBuilder.Append("        option9 = @option9, option10 = @option10, option11 = @option11, ");
                SqlStringBuilder.Append("        option12 = @option12, option13 = @option13, ");
                SqlStringBuilder.Append("        update_userid = @update_userid, update_ymd = dbo.get_ymd(), ");
                SqlStringBuilder.Append("        update_hms = dbo.get_hms() ");
                SqlStringBuilder.Append("WHERE   part_id = @part_id ");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 保存零件数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveLevelPartMasterDefine(Hashtable htParams)
        {
            lock (_remotingProxy)
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT  INTO dbo.sys_part ( part_no, part_spec, part_spec2, part_location, ");
                SqlStringBuilder.Append("                            part_type, part_class, model_id, label_file, ");
                SqlStringBuilder.Append("                            label_type, label_qty, erp_part_no, rule_set, uom, ");
                SqlStringBuilder.Append("                            route_id, locate_id, upc_code, ucc_code, ean_code, ");
                SqlStringBuilder.Append("                            burnin_time, version, enabled, custome_id, ");
                SqlStringBuilder.Append("                            vendor_id, cust_part_no, vendor_part_no, ");
                SqlStringBuilder.Append("                            mfger_part_no, subboard_qty, split_flag, ");
                SqlStringBuilder.Append("                            batch_control, min_pack_qty, min_stock_qty, ");
                SqlStringBuilder.Append("                            max_stock_qty, wip_min_stock_qty, indate_flag, ");
                SqlStringBuilder.Append("                            indate, sampling_plan_id, over_request, ");
                SqlStringBuilder.Append("                            default_box_qty, default_reel_qty, default_set_qty, ");
                SqlStringBuilder.Append("                            supply_type, settle_type, recheck_cycle, ");
                SqlStringBuilder.Append("                            instock_type, package_size, part_no_3c, part_abc, ");
                SqlStringBuilder.Append("                            part_group, uom1, coefficient1, uom2, coefficient2, ");
                SqlStringBuilder.Append("                            uom3, coefficient3, factory_code, mc_code, option1, ");
                SqlStringBuilder.Append("                            option2, option3, option5, option6, ");
                SqlStringBuilder.Append("                            option7, option8, option9, option10, option11, ");
                SqlStringBuilder.Append("                            option12, option13, update_userid, update_ymd, ");
                SqlStringBuilder.Append("                            update_hms, create_userid, create_ymd, create_hms, ");
                SqlStringBuilder.Append("                            guid, timestamp ) ");
                SqlStringBuilder.Append("VALUES  ( @part_no, @part_spec, @part_spec2, @part_location, @part_type, ");
                SqlStringBuilder.Append("          @part_class, @model_id, @label_file, @label_type, @label_qty, ");
                SqlStringBuilder.Append("          @erp_part_no, @rule_set, @uom, @route_id, @locate_id, @upc_code, ");
                SqlStringBuilder.Append("          @ucc_code, @ean_code, @burnin_time, @version, 'Y', @custome_id, ");
                SqlStringBuilder.Append("          @vendor_id, @cust_part_no, @vendor_part_no, @mfger_part_no, ");
                SqlStringBuilder.Append("          @subboard_qty, @split_flag, @batch_control, @min_pack_qty, ");
                SqlStringBuilder.Append("          @min_stock_qty, @max_stock_qty, @wip_min_stock_qty, @indate_flag, ");
                SqlStringBuilder.Append("          @indate, @sampling_plan_id, @over_request, @default_box_qty, ");
                SqlStringBuilder.Append("          @default_reel_qty, @default_set_qty, @supply_type, @settle_type, ");
                SqlStringBuilder.Append("          @recheck_cycle, @instock_type, @package_size, @part_no_3c, @part_abc, ");
                SqlStringBuilder.Append("          @part_group, @uom1, @coefficient1, @uom2, @coefficient2, @uom3, ");
                SqlStringBuilder.Append("          @coefficient3, @factory_code, @mc_code, @option1, @option2, @option3, ");
                SqlStringBuilder.Append("          @option5, @option6, @option7, @option8, @option9, ");
                SqlStringBuilder.Append("          @option10, @option11, @option12, @option13, @create_userid, ");
                SqlStringBuilder.Append("          dbo.get_ymd(), dbo.get_hms(), NEWID() ) ");

                return _remotingProxy.ExecuteNonQuery(SqlStringBuilder.ToString(), htParams);
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="partID"></param>
        /// <returns></returns>
        public int deleteLevelPartMasterDefine(string part_id)
        {
            lock (_remotingProxy)
            {
                string sql = "DELETE FROM dbo.sys_part WHERE part_id IN (" + part_id + ")";

                return _remotingProxy.ExecuteNonQuery(sql);
            }
        }



    }
}