lj_plc/Actions/ChemicalWeighing/Mesnac.Action.ChemicalWeighing/MaterialManage/MaterialHelper.cs

using Mesnac.Action.ChemicalWeighing.Entity;
using Mesnac.Action.ChemicalWeighing.Technical.PmtRecipe.entity;
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
{
    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 row_number() over(order by material_Id) as index_id,* from Base_MaterialInfo";
            dbHelper.CommandText = strSql;
            DataTable table = dbHelper.ToDataTable();
            return table;
        }

        #endregion

        #region 新增物料
        /// <summary>
        /// 向数据库中新增物料信息
        /// </summary>
        /// <param name="material"></param>
        public static void addMaterial(Base_MaterialInfo 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 Base_MaterialInfo (material_Id, material_Name, remark) VALUES (@material_Id, @material_Name,@remark)";
            dbHelper.CommandText = insertUserSql;

            dbHelper.AddParameter("@material_Id", material.materialId);
            dbHelper.AddParameter("@material_Name", material.materialName);
            dbHelper.AddParameter("@remark", material.remark);
            dbHelper.ExecuteNonQuery();
        }
        #endregion

        #region 根据ID修改物料信息
        public static void updateMaterial(Base_MaterialInfo 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 Base_MaterialInfo set material_Id = @material_Id,material_Name = @material_Name,remark = @remark where material_Id = @uid";
                dbHelper.CommandText = strSql;
                dbHelper.AddParameter("@material_Id", material.materialId);
                dbHelper.AddParameter("@material_Name", material.materialName);
                dbHelper.AddParameter("@remark", material.remark);
                dbHelper.AddParameter("@uid", material.uid);
                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 Base_MaterialInfo WHERE material_Id = @material_Id";
            dbHelper.CommandText = strSql;
            dbHelper.AddParameter("@material_Id", id);
            dbHelper.ExecuteNonQuery();
        }
        #endregion

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

            foreach (DataRow row in table.Rows)
            {
                Base_MaterialInfo material = new Base_MaterialInfo
                {
                    materialId = Mesnac.Basic.DataProcessor.RowValue(row, "material_Id", String.Empty),
                    materialName = Mesnac.Basic.DataProcessor.RowValue(row, "material_Name", String.Empty),
                    remark = Mesnac.Basic.DataProcessor.RowValue(row, "remark", String.Empty),
                };
                lst.Add(material);
            }
            return lst;
        }
        #endregion

        /// <summary>
        /// 根据罐号获取物料
        /// </summary>
        /// <param name="canid"></param>
        /// <returns></returns>
        public static List<Base_MaterialInfo> GetBaseMaterilaInfo(int canid)
        {
            try
            {
                List<Base_MaterialInfo> recipeMaterials = new List<Base_MaterialInfo>();

                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 sqlstr = "select *from (select row_number() over(order by material_Id) as index_id,* from Base_MaterialInfo) t1 where index_id = @index_id";

                dbHelper.CommandText = sqlstr;
                dbHelper.AddParameter("@index_id", canid);
                DataTable table = dbHelper.ToDataTable();

                if (table != null && table.Rows.Count > 0)
                {
                    foreach (DataRow dr in table.Rows)
                    {
                        Base_MaterialInfo recipeMaterial = new Base_MaterialInfo();
                        recipeMaterial = ConvertDataRowToBaseRecipeInfo(dr);
                        recipeMaterials.Add(recipeMaterial);
                    }
                }
                return recipeMaterials;
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        public static Base_MaterialInfo ConvertDataRowToBaseRecipeInfo(DataRow dr)
        {
            if (dr != null)
            {
                Base_MaterialInfo recipe = new Base_MaterialInfo();
                recipe.materialId = Mesnac.Basic.DataProcessor.RowValue(dr, "material_Id", String.Empty);
                recipe.materialName = Mesnac.Basic.DataProcessor.RowValue(dr, "material_Name", String.Empty);
                return recipe;
            }
            else
            {
                return null;
            }
        }

    }
}