PLT-SolventIPC/Actions/ChemicalWeighing/Mesnac.Action.ChemicalWeighing/MaterialManage/MaterialHelper.cs

using Mesnac.Action.ChemicalWeighing.Entity;
using Mesnac.Action.ChemicalWeighing.Entity.material;
using Mesnac.Codd.Session;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Mesnac.Action.ChemicalWeighing.MaterialManage
{
    public class MaterialHelper
    {
        #region 查询物料信息

        /// <summary>
        /// 查询物料信息
        /// </summary>
        /// <returns>物料信息表</returns>
        public static DataTable getMaterial()
        {
            DbHelper dbHelper = Mesnac.Basic.DataSourceFactory.Instance.GetDbHelper(Mesnac.Basic.DataSourceFactory.MCDbType.Local);
            if (dbHelper == null)
            {
                throw new Exception(Mesnac.Basic.LanguageHelper.DataBaseConnectError);
            }
            dbHelper.ClearParameter();
            dbHelper.CommandType = CommandType.Text;
            string strSql = "SELECT ID,row_number() over(order by MID) as '序号', Material_code, Material_name, Remark, Batch_number, Barcode1, Barcode2, Barcode3,IsEnable FROM Pmt_material order by MID asc";
            dbHelper.CommandText = strSql;
            DataTable table = dbHelper.ToDataTable();
            return table;
        }

        #endregion

        #region 新增物料
        /// <summary>
        /// 向数据库中新增物料信息
        /// </summary>
        /// <param name="material"></param>
        public static void addMaterial(Pmt_material material)
        {
            DbHelper dbHelper = Mesnac.Basic.DataSourceFactory.Instance.GetDbHelper(Mesnac.Basic.DataSourceFactory.MCDbType.Local);
            if (dbHelper == null)
            {
                throw new Exception(Mesnac.Basic.LanguageHelper.DataBaseConnectError);
            }
            dbHelper.ClearParameter();
            dbHelper.CommandType = CommandType.Text;

            string insertUserSql = "INSERT INTO Pmt_material(ID, Material_code, Material_name,IsEnable) VALUES(@M_ID, @M_Code, @M_Name,@IsEnable)";
            dbHelper.CommandText = insertUserSql;

            dbHelper.AddParameter("@M_ID", material.ID);
            dbHelper.AddParameter("@M_Code", material.Material_code); 
            dbHelper.AddParameter("@M_Name", material.Material_name);
            dbHelper.AddParameter("@IsEnable", material.IsEnable);
            dbHelper.ExecuteNonQuery();
        }
        #endregion

        #region 根据ID修改物料信息
        public static void updateMaterial(Pmt_material material)
        {
            try
            {
                DbHelper dbHelper = Mesnac.Basic.DataSourceFactory.Instance.GetDbHelper(Mesnac.Basic.DataSourceFactory.MCDbType.Local);
                if (dbHelper == null)
                {
                    throw new Exception(Mesnac.Basic.LanguageHelper.DataBaseConnectError);
                }
                dbHelper.ClearParameter();
                dbHelper.CommandType = CommandType.Text;
                string strSql = "UPDATE Pmt_material SET Material_code=@MaterialCode, Material_name=@MaterialName,IsEnable=@IsEnable WHERE ID=@ID";
                dbHelper.CommandText = strSql;
                dbHelper.AddParameter("@MaterialCode", material.Material_code);
                dbHelper.AddParameter("@MaterialName", material.Material_name);
                dbHelper.AddParameter("@IsEnable", material.IsEnable);
                dbHelper.AddParameter("@ID", material.ID);
                dbHelper.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ICSharpCode.Core.LoggingService<MaterialHelper>.Error("修改物料信息异常：" + ex.Message, ex);
            }
        }
        #endregion

        #region 根据物料ID删除物料
        public static void deleteMaterial(string id)
        {
            DbHelper dbHelper = Mesnac.Basic.DataSourceFactory.Instance.GetDbHelper(Mesnac.Basic.DataSourceFactory.MCDbType.Local);
            if (dbHelper == null)
            {
                throw new Exception(Mesnac.Basic.LanguageHelper.DataBaseConnectError);
            }
            dbHelper.CommandType = CommandType.Text;
            dbHelper.ClearParameter();
            string strSql = "DELETE FROM Pmt_material WHERE ID = @ID";
            dbHelper.CommandText = strSql;
            dbHelper.AddParameter("@ID", id);
            dbHelper.ExecuteNonQuery();
        }
        #endregion

        #region 获取物料对象集合
        /// <summary>
        /// 获取包含所有物料对象的集合
        /// </summary>
        /// <returns>返回一个list集合 其中是Material对象</returns>
        public static List<Pmt_material> getMaterialList()
        {
            DataTable table = getMaterial();
            List<Pmt_material> lst = new List<Pmt_material>();

            foreach (DataRow row in table.Rows)
            {
                Pmt_material material = new Pmt_material
                {
                    ID = Mesnac.Basic.DataProcessor.RowValue(row, "ID", String.Empty),
                    Material_code = Mesnac.Basic.DataProcessor.RowValue(row, "Material_code", String.Empty),
                    Material_name = Mesnac.Basic.DataProcessor.RowValue(row, "Material_name", String.Empty),
                    Remark = Mesnac.Basic.DataProcessor.RowValue(row, "Remark", String.Empty),
                    Batch_number = Mesnac.Basic.DataProcessor.RowValue(row, "Batch_number", String.Empty),
                    Barcode1 = Mesnac.Basic.DataProcessor.RowValue(row, "Barcode1", String.Empty),
                    Barcode2 = Mesnac.Basic.DataProcessor.RowValue(row, "Barcode2", String.Empty),
                    Barcode3 = Mesnac.Basic.DataProcessor.RowValue(row, "Barcode3", String.Empty),
                    IsEnable = Mesnac.Basic.DataProcessor.RowValue(row, "IsEnable", String.Empty),
                };
                lst.Add(material);
            }
            return lst;
        }
        #endregion


        /// <summary>
        /// 获取物料编码，仓库
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public static MaterialBarCode QueryMaterBarCode(string code)
        {
            MaterialBarCode entity = new MaterialBarCode();

            DbHelper dbHelper = Mesnac.Basic.DataSourceFactory.Instance.GetDbHelper(Mesnac.Basic.DataSourceFactory.MCDbType.Local);
            if (dbHelper == null)
            {
                throw new Exception(Mesnac.Basic.LanguageHelper.DataBaseConnectError);
            }
            dbHelper.CommandType = CommandType.Text;
            string strSql = "select b.Bin_Serial,b.Bin_Name,m.Material_code,m.Material_name from Pmt_Bin b left join xl_material m on b.Material_ID=m.ID where Material_code = @Code and IsEnable='是'";
            dbHelper.CommandText = strSql;
            dbHelper.ClearParameter();
            dbHelper.AddParameter("@Code", code);
            DataTable table = dbHelper.ToDataTable();
            if (table != null && table.Rows.Count > 0)
            {
                foreach (DataRow dr in table.Rows)
                {
                    entity = ConvertDataRowToBarCode(dr);
                    return entity;
                }
            }
            return entity;
        }

        private static MaterialBarCode ConvertDataRowToBarCode(DataRow dr)
        {
            if (dr != null)
            {
                MaterialBarCode bar = new MaterialBarCode();
                bar.Bin_Serial = Mesnac.Basic.DataProcessor.RowValue(dr, "Bin_Serial", 0);
                bar.Bin_Name = Mesnac.Basic.DataProcessor.RowValue(dr, "Bin_Name", String.Empty);
                bar.Material_code = Mesnac.Basic.DataProcessor.RowValue(dr, "Material_code", String.Empty);
                bar.Material_name = Mesnac.Basic.DataProcessor.RowValue(dr, "Material_name", String.Empty);

                return bar;
            }
            else
            {
                return null;
            }
        }
    }
}