NBDXSY/LeaRun.Application/LeaRun.Application.Service/WarehouseManage/WMMaterialTypeService.cs

using LeaRun.Application.Code;
using LeaRun.Application.Entity.WarehouseManage;
using LeaRun.Data;
using LeaRun.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LeaRun.Application.Service.WarehouseManage
{
    public class WMMaterialTypeService
    {
        SqlHelper sqlHelper = new SqlHelper("InstallDB");

        public List<MaterialTypeTreeEntiy> GetList(int level, string parentId)
        {
            switch (level)
            {
                case 0:
                    return GetAllList(level, parentId);
                case 1:
                    return GetMaterialList(level, parentId);

                default:
                    return new List<MaterialTypeTreeEntiy>();
            }
        }

        public void SaveMeterialForm(String data)
        {
            String sql = null;
            MaterialTypeTreeEntiy entity = data.ToObject<MaterialTypeTreeEntiy>();
            String op = OperatorProvider.Provider.Current().UserName;
            if (string.IsNullOrEmpty(entity.Id))
            {
                sql = @"INSERT INTO WM_MaterialType (Code,Name,CreateDT,Operator,UpdateDt,IsWaterMeter) VALUES 
                                                     ('" + entity.Code + "','" + entity.Name + "',GETDATE(),'" + op + "',GETDATE(),0)";
            }
            else
            {
                sql = @"UPDATE InstallManage.dbo.WM_MaterialType
                                       SET Code ='"+entity.Code+@"' 
                                          ,Name = '"+entity.Name+@"'
                                          ,Operator = '"+op+ @"'
                                          ,UpdateDt = GETDATE()
                                     WHERE materialId="+entity.Id+@"";
            }
            try
            {
                sqlHelper.ExecuteNonQuery(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        /// <summary>
        /// 删除材料
        /// </summary>
        /// <param name="goodID"></param>
        /// <returns></returns>
        public int DelGoods(string goodID)
        {
            int flag;
            string sql = @"delete from InstallManage.dbo.WM_Goods 
                           where GoodId = " + goodID;
            try
            {
                flag = sqlHelper.ExecuteNoParams(sql, CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw;
            }
            return flag;
        }
        /// <summary>
        /// 删除材料类型
        /// </summary>
        /// <param name="ItemText"></param>
        /// <returns></returns>
        public int DelMaterialType(string id)
        {
            int flag;
            string sql = @"delete from WM_MaterialType
                           where materialId = " + id;
            try
            {
                flag = sqlHelper.ExecuteNoParams(sql, CommandType.Text) > 0 ? 1 : 0;
            }
            catch (Exception ex)
            {
                throw;
            }
            return flag;
        }
        public DataTable GetGoodsByGoodID(string goodID)
        {
            DataTable dt;
            string sql = @"SELECT [GoodId]
                              ,[WarehouseId]
                              ,[Name]
                              ,[Specifications]
                              ,u.CalculateUnit
                              ,[MaxStock]
                              ,[MinStock]
                              ,[PurcharePrice]
                              ,[RetailPrice]
                              ,[MinRetaPrice]
                              ,[StockNum]
                              ,[CostPrice]
                              ,[Cost]
                              ,[UpdateDt]
                              ,[BasePrice]
                              ,[LabourCost]
                              ,[MechanicalCost]
                              ,[IsWaterMeter]
                              ,[OutdoorMechanicalCost]
                              ,[OutdoorLabourCost]
                              ,[OutdoorBasePrice]
                              ,[IsStop]
                              ,g.[CreateDt]
                              ,[Operator]
                              ,[MaterialID]
                          FROM WM_Goods g 
                          left join  WM_CalculateUnit u  ON g.CalculateUnit=u.UnitId
                           where GoodId = " + goodID;
            try
            {
                dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }
        /// <summary>
        /// 删除材料类型之前，查询材料类型是否被材料引用
        /// </summary>
        /// <param name="materialName"></param>
        /// <returns></returns>
        public DataTable GetGoodsByType(string materialid)
        {
            DataTable dt;
            string sql = @"SELECT [GoodId]
                              ,[WarehouseId]
                              ,[Name]
                              ,[Specifications]
                              ,u.CalculateUnit
                              ,[MaxStock]
                              ,[MinStock]
                              ,[PurcharePrice]
                              ,[RetailPrice]
                              ,[MinRetaPrice]
                              ,[StockNum]
                              ,[CostPrice]
                              ,[Cost]
                              ,[UpdateDt]
                              ,[BasePrice]
                              ,[LabourCost]
                              ,[MechanicalCost]
                              ,[IsWaterMeter]
                              ,[OutdoorMechanicalCost]
                              ,[OutdoorLabourCost]
                              ,[OutdoorBasePrice]
                              ,[IsStop]
                              ,g.[CreateDt]
                              ,[Operator]
                              ,[MaterialID]
                          FROM WM_Goods g 
                          left join  WM_CalculateUnit u  ON g.CalculateUnit=u.UnitId
                          where MaterialID=(select top 1 materialId from WM_MaterialType where materialId=" + materialid + ")";
            try
            {
                dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }

        public DataTable SearchGoodsByKeyword(string keyWord)
        {
            DataTable dt;
            string sql = @"SELECT [GoodId]
                              ,[WarehouseId]
                              ,[Name]
                              ,[Specifications]
                              ,u.CalculateUnit
                              ,[MaxStock]
                              ,[MinStock]
                              ,[PurcharePrice]
                              ,[RetailPrice]
                              ,[MinRetaPrice]
                              ,[StockNum]
                              ,[CostPrice]
                              ,[Cost]
                              ,[UpdateDt]
                              ,[BasePrice]
                              ,[LabourCost]
                              ,[MechanicalCost]
                              ,[IsWaterMeter]
                              ,[OutdoorMechanicalCost]
                              ,[OutdoorLabourCost]
                              ,[OutdoorBasePrice]
                              ,[IsStop]
                              ,g.[CreateDt]
                              ,[Operator]
                              ,[MaterialID]
                          FROM WM_Goods g 
                          left join  WM_CalculateUnit u  ON g.CalculateUnit=u.UnitId
                           where Warehouseid like '%" + keyWord + "%' or Name like '%" + keyWord + "%'";
            try
            {
                dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
            return dt;
        }
        public void SaveMeterialDetailForm(string keyvalue, String data, string unitid)
        {
            String sql = null;
            MaterialGoodsEntity materialGoodsEntity = data.ToObject<MaterialGoodsEntity>();
            if (!string.IsNullOrEmpty(keyvalue))
            {
                materialGoodsEntity.MaterialID = GetMaterialID(keyvalue);
            }
            materialGoodsEntity.CalculateUnit = unitid;
            String op = OperatorProvider.Provider.Current().UserName;
            if (string.IsNullOrEmpty(materialGoodsEntity.GoodId.ToString()))
            {
                sql = @"INSERT INTO dbo.WM_Goods
                                           (WarehouseId
                                           ,Name
                                           ,Specifications
                                           ,CalculateUnit
                                           ,MaxStock
                                           ,MinStock
                                           ,PurcharePrice
                                           ,RetailPrice
                                           ,MinRetaPrice
                                           ,StockNum
                                           ,CostPrice
                                           ,Cost
                                           ,UpdateDt
                                           ,BasePrice
                                           ,LabourCost
                                           ,MechanicalCost
                                           ,IsWaterMeter
                                           ,OutdoorMechanicalCost
                                           ,OutdoorLabourCost
                                           ,OutdoorBasePrice
                                           ,IsStop
                                           ,CreateDt
                                           ,Operator
                                           ,MaterialID)
                                     VALUES
                                           (
                                            " + "'" + materialGoodsEntity.WarehouseId + "'," + "'" + materialGoodsEntity.Name + "'," + "'" + materialGoodsEntity.Specifications + "',"
                                                  + "'" + materialGoodsEntity.CalculateUnit + "'," + "'" + materialGoodsEntity.MaxStock + "',"
                                                  + "'" + materialGoodsEntity.MinStock + "'," + "'" + materialGoodsEntity.PurcharePrice + "'," + "'" +
                                                  materialGoodsEntity.RetailPrice + "'," + "'" + materialGoodsEntity.MinRetaPrice + "',"
                                                  + "'" + materialGoodsEntity.StockNum + "'," + "'" + materialGoodsEntity.CostPrice + "'," + "'" +
                                                  materialGoodsEntity.Cost + "',GETDATE(),'" + materialGoodsEntity.BasePrice + "'," + "'" + materialGoodsEntity.LabourCost + "',"
                                                  + "'" + materialGoodsEntity.MechanicalCost + "'," + "'" + materialGoodsEntity.IsWaterMeter + "'," + "'" +
                                                  materialGoodsEntity.OutdoorMechanicalCost + "'," + "'" + materialGoodsEntity.OutdoorLabourCost + "',"
                                                  + "'" + materialGoodsEntity.OutdoorBasePrice + "'," + "'" + materialGoodsEntity.IsStop
                                                  + "',GETDATE()," + "'" + op + "'" + ",'" + materialGoodsEntity.MaterialID + "'" + " )";
            }
            else
            {
                sql = @"UPDATE dbo.WM_Goods
                                   SET WarehouseId = " + "'" + materialGoodsEntity.WarehouseId + "'" + @"
                                      ,Name = " + "'" + materialGoodsEntity.Name + "'" + @"
                                      ,Specifications = " + "'" + materialGoodsEntity.Specifications + "'" + @"
                                      ,CalculateUnit = " + "'" + materialGoodsEntity.CalculateUnit + "'" + @"
                                      ,MaxStock = " + "'" + materialGoodsEntity.MaxStock + "'" + @"
                                      ,MinStock = " + "'" + materialGoodsEntity.MinStock + "'" + @"
                                      ,PurcharePrice = " + "'" + materialGoodsEntity.PurcharePrice + "'" + @"
                                      ,RetailPrice = " + "'" + materialGoodsEntity.RetailPrice + "'" + @"
                                      ,MinRetaPrice = " + "'" + materialGoodsEntity.MinRetaPrice + "'" + @"
                                      ,StockNum = " + "'" + materialGoodsEntity.StockNum + "'" + @"
                                      ,CostPrice = " + "'" + materialGoodsEntity.CostPrice + "'" + @"
                                      ,Cost = " + "'" + materialGoodsEntity.Cost + "'" + @"
                                      ,UpdateDt = " + "GETDATE()" + @"
                                      ,BasePrice = " + "'" + materialGoodsEntity.BasePrice + "'" + @"
                                      ,LabourCost = " + "'" + materialGoodsEntity.LabourCost + "'" + @"
                                      ,MechanicalCost =" + "'" + materialGoodsEntity.MechanicalCost + "'" + @"
                                      ,IsWaterMeter = " + "'" + materialGoodsEntity.IsWaterMeter + "'" + @"
                                      ,OutdoorMechanicalCost = " + "'" + materialGoodsEntity.OutdoorMechanicalCost + "'" + @"
                                      ,OutdoorLabourCost = " + "'" + materialGoodsEntity.OutdoorLabourCost + "'" + @"
                                      ,OutdoorBasePrice = " + "'" + materialGoodsEntity.OutdoorBasePrice + "'" + @"
                                      ,IsStop = " + "'" + materialGoodsEntity.IsStop + "'" + @"
                                      ,Operator = " + "'" + op + "'" + @"
                                 WHERE GoodId=" + materialGoodsEntity.GoodId;
            }
            try
            {
                sqlHelper.ExecuteNonQuery(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        public int GetMaterialID(string MaterialName)
        {
            string sql = "select materialId from InstallManage.dbo.WM_MaterialType where Name='" + MaterialName + "'";
            DataTable dt;
            try
            {
                dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
            return Convert.ToInt32(dt.Rows[0]["materialId"].ToString());
        }
        public DataTable GetCalCulateUnitDT()
        {
            string sqlUnit = "select UnitId, CalculateUnit from dbo.WM_CalculateUnit";
            DataTable dtUnit = sqlHelper.ExecuteDataTable(sqlUnit, CommandType.Text, null);
            return dtUnit;
        }
        //获取计量单位
        public String GetCalCulateUnit()
        {
            String json = "";
            String sql = "SELECT UnitId,CalculateUnit FROM WM_CalculateUnit";
            DataTable dt;
            try
            {
                dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
            return json;
        }


        //获取仓库
        public String GetWarehouse()
        {
            String json = "";
            String sql = "SELECT materialId,Name FROM WM_MaterialType";
            DataTable dt;
            try
            {
                dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
            }
            catch (Exception ex)
            {
                throw;
            }
            return json;
        }

        private List<MaterialTypeTreeEntiy> GetAllList(int level, string parentId)
        {
            List<MaterialTypeTreeEntiy> list = new List<MaterialTypeTreeEntiy>();
            MaterialTypeTreeEntiy entity = new MaterialTypeTreeEntiy();
            entity.Id = "level0_0";
            entity.Code = "";
            entity.Name = "材料类别";
            entity.ParentId = "0";
            entity.Level = 0;
            list.Add(entity);
            return list;
        }

        private List<MaterialTypeTreeEntiy> GetMaterialList(int level, string parentId)
        {
            String sql = "SELECT materialId,Code,Name FROM WM_MaterialType";
            DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);

            List<MaterialTypeTreeEntiy> list = new List<MaterialTypeTreeEntiy>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                MaterialTypeTreeEntiy entity = new MaterialTypeTreeEntiy();
                entity.Id = "level1_" + dt.Rows[i]["materialId"].ToString();
                entity.Code = dt.Rows[i]["Code"].ToString();
                entity.Name = dt.Rows[i]["Name"].ToString();
                entity.ParentId = "level0_0";
                entity.Level = 1;
                list.Add(entity);
            }
            return list;
        }

    }
}