dictservice.cs

客户关系管理系统 客户关系管理系统

using System;
using System.Collections.Generic;
using System.Text;
using MyCRM.IDAL;
using MyCRM.Models;
using System.Data;
using System.Data.SqlClient;
using MyCRM.DBUtility;

namespace MyCRM.DAL
{
    /// <summary>
    /// 杂项数据层
    /// </summary>
   public class DictService:IDict
    {
       /// <summary>
       /// 根据字典类型查询
       /// </summary>
       /// <param name="item"></param>
       /// <returns></returns>
       public List<Bas_dict> GetDict(string item)
       {
           string sql = "select * from Bas_dict where Dict_type='" + item + "'";
           return GetDictBySql(sql);
       }
       /// <summary>
       /// 查询所有信息
       /// </summary>
       /// <returns></returns>
       public List<Bas_dict> GetAll()
       {
           string sql = "select * from Bas_dict";
           return GetDictBySql(sql);
       }
       /// <summary>
       /// 条件查询
       /// </summary>
       /// <param name="name"></param>
       /// <param name="time"></param>
       /// <param name="type"></param>
       /// <returns></returns>
       public List<Bas_dict> GetSelect(string name, string time, string type)
       {
           string sql = "select * from Bas_dict ";
           string tiaojian = "";
           if (name != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = "where dict_type='" + name + "' ";
               }
               else
               {
                   tiaojian += "and dict_type='" + name + "' ";
               }
           }
           if (time != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = " where dict_item='" + time + "' ";
               }
               else
               {
                   tiaojian += " and dict_item='" + time + "'";
               }
           }
           if (type != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = " where dict_value='" + type + "' ";
               }
               else
               {
                   tiaojian += " and dict_value='" + type + "'";
               }
           }


           sql = sql + tiaojian;
           return GetDictBySql(sql);
       }
        /// <summary>
        /// 依据sql语句查询用户
        /// </summary>
        /// <param name="safeSql">sql语句</param>
        /// <returns>返回集合</returns>
        public List<Bas_dict> GetDictBySql(string safeSql)
        {
            List<Bas_dict> list = new List<Bas_dict>();

            using (DataTable table = DBHelper.GetDataSet(safeSql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Bas_dict dict = new Bas_dict();

                    dict.Dict_id = (int)row["Dict_id"];
                    dict.Dict_is_editable = (bool)row["Dict_is_editable"];
                    dict.Dict_item = (string)row["Dict_item"];
                    dict.Dict_type = (string)row["Dict_type"];
                    dict.Dict_value = (string)row["Dict_value"];
                    list.Add(dict);
                }

                return list;
            }
        }
       /// <summary>
       /// 按条件查询库存
       /// </summary>
       /// <param name="name"></param>
       /// <param name="time"></param>
       /// <returns></returns>
        public List<Storage> GetSelectsto(string name, string time)
        {
            Product pro = GetProByName(name);
            string sql = "select * from storage ";
            string tiaojian = "";
            if (pro!=null)
            {
                if (tiaojian == "")
                {
                    tiaojian = "where stk_prod_id=" + pro.Product_id + "";
                }
                else
                {
                    tiaojian += "and stk_prod_id=" + pro.Product_id + " ";
                }
            }
            if (time != "")
            {
                if (tiaojian == "")
                {
                    tiaojian = " where stk_warehouse='" + time + "' ";
                }
                else
                {
                    tiaojian += " and stk_warehouse='" + time + "'";
                }
            }
            


            sql = sql + tiaojian;
            return GetstoBySql(sql);
        }
       /// <summary>
       /// 查询所有库存
       /// </summary>
       /// <returns></returns>
        public List<Storage> Getallsto()
        {
            string sql = "select * from Storage";
            return GetstoBySql(sql);
        }
       /// <summary>
       /// 根据SQL语句查询
       /// </summary>
       /// <param name="safeSql"></param>
       /// <returns></returns>
        public List<Storage> GetstoBySql(string safeSql)
        {
            List<Storage> list = new List<Storage>();

            using (DataTable table = DBHelper.GetDataSet(safeSql))
            {
                foreach (DataRow row in table.Rows)
                {
                    Storage sto = new Storage();
                    sto.Product_id = GetProByProid((int)row["stk_prod_id"]);
                    sto.Stk_count = (int)row["Stk_count"];
                    sto.Stk_id=(int)row["Stk_id"];
                    sto.Stk_memo=(string)row["Stk_memo"];
                    sto.Stk_ware=(string)row["Stk_ware"];
                    sto.Stk_warehouse=(string)row["Stk_warehouse"];
                    list.Add(sto);
                }

                return list;
            }
        }
       /// <summary>
       /// 按条件查询商品
       /// </summary>
       /// <param name="name"></param>
       /// <param name="time"></param>
       /// <param name="type"></param>
       /// <returns></returns>
       public List<Product> GetSelectpro(string name, string time, string type)
       {
           string sql = "select * from product ";
           string tiaojian = "";
           if (name != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = "where product_name='" + name + "' ";
               }
               else
               {
                   tiaojian += "and product_name='" + name + "' ";
               }
           }
           if (time != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = " where product_type='" + time + "' ";
               }
               else
               {
                   tiaojian += " and product_type='" + time + "'";
               }
           }
           if (type != "")
           {
               if (tiaojian == "")
               {
                   tiaojian = " where product_batch='" + type + "' ";
               }
               else
               {
                   tiaojian += " and product_batch='" + type + "'";
               }
           }


           sql = sql + tiaojian;
           return GetProBySql(sql);
       }
       /// <summary>
       /// 查询所有商品
       /// </summary>
       /// <returns></returns>
       public List<Product> GetallPro()
       {
           string sql = "select * from Product";
           return GetProBySql(sql);
       }
       /// <summary>
       /// 根据SQL语句查询
       /// </summary>
       /// <param name="safeSql"></param>
       /// <returns></returns>
       public List<Product> GetProBySql(string safeSql)
       {
           List<Product> list = new List<Product>();

           using (DataTable table = DBHelper.GetDataSet(safeSql))
           {
               foreach (DataRow row in table.Rows)
               {
                   Product pro = new Product();
                   pro.Product_name=(string)row["product_name"];
                   pro.Product_id=(int)row["product_id"];
                   pro.Product_type=(string)row["product_type"];
                   pro.Product_batch=(string)row["product_batch"];
                   pro.Product_unit=(string)row["product_unit"];
                   pro.Product_price=(decimal)row["product_price"];
                   pro.Product_memo=(string)row["product_memo"];
                   list.Add(pro);
               }

               return list;
           }
       }
       /// <summary>
       /// 添加新条目
       /// </summary>
       /// <param name="dict"></param>
       /// <returns></returns>
       public int AddDict(Bas_dict dict)
       {
           int ab = 0;
           if (dict.Dict_is_editable)
           {
               ab = 1;
           }
           
           string sql = "insert into Bas_dict values("+dict.Dict_id+",'"+dict.Dict_type+"','"+dict.Dict_item+"','"+dict.Dict_value+"',"+ab+")";
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 修改条目
       /// </summary>
       /// <param name="dict"></param>
       /// <returns></returns>
       public int Update(Bas_dict dict)
       {
           int ab = 0;
           if (dict.Dict_is_editable)
           {
               ab = 1;
           }
           string sql = "update bas_dict set dict_type='"+dict.Dict_type+"',dict_item='"+dict.Dict_item+"',dict_value='"+dict.Dict_value+"',dict_is_editable="+ab+" where dict_id="+dict.Dict_id;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 删除条目
       /// </summary>
       /// <param name="dictid"></param>
       /// <returns></returns>
       public int Del(int dictid)
       {
           string sql = "delete from bas_dict where dict_id="+dictid;
           int result = DBHelper.ExecuteCommand(sql);
           return result;
       }
       /// <summary>
       /// 根据字典ID查询
       /// </summary>
       /// <param name="Chanceid"></param>
       /// <returns></returns>
       public Bas_dict GetdictById(int Chanceid)
       {
           string sql = "select * from Bas_dict where dict_id=" + Chanceid;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Bas_dict dict = new Bas_dict();

               dict.Dict_id = (int)reader["Dict_id"];
               dict.Dict_is_editable = (bool)reader["Dict_is_editable"];
               dict.Dict_item = (string)reader["Dict_item"];
               dict.Dict_type = (string)reader["Dict_type"];
               dict.Dict_value = (string)reader["Dict_value"];
               reader.Close();
               return dict;
           }
           else
           {
               reader.Close();
               return null;
           }

       }
       /// <summary>
       /// 根据商品ID查询
       /// </summary>
       /// <param name="proid"></param>
       /// <returns></returns>
       public Product GetProByProid(int proid)
       {
           string sql = "select * from product where product_id=" + proid;
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Product pro = new Product();
               pro.Product_name = (string)reader["product_name"];
               pro.Product_id = (int)reader["product_id"];
               pro.Product_type = (string)reader["product_type"];
               pro.Product_batch = (string)reader["product_batch"];
               pro.Product_unit = (string)reader["product_unit"];
               pro.Product_price = (decimal)reader["product_price"];
               pro.Product_memo = (string)reader["product_memo"];
               reader.Close();
               return pro;
           }
           else
           {
               reader.Close();
               return null;
           }
       }
       /// <summary>
       /// 根据商品名查询
       /// </summary>
       /// <param name="proid"></param>
       /// <returns></returns>
       public Product GetProByName(string proid)
       {
           string[] x = proid.Split(new Char[] { '-','-'});
           string x0 = x[0];
           string x1 = x[1];
           string x2 = x[2];

           string sql = "select * from product where product_name='"+x0.Trim()+"'and product_type='"+x1.Trim()+"' and product_batch='"+x2.Trim()+"'";
           SqlDataReader reader = DBHelper.GetReader(sql);
           if (reader.Read())
           {
               Product pro = new Product();
               pro.Product_name = (string)reader["product_name"];
               pro.Product_id = (int)reader["product_id"];
               pro.Product_type = (string)reader["product_type"];
               pro.Product_batch = (string)reader["product_batch"];
               pro.Product_unit = (string)reader["product_unit"];
               pro.Product_price = (decimal)reader["product_price"];
               pro.Product_memo = (string)reader["product_memo"];
               reader.Close();
               return pro;
           }
           else
           {
               reader.Close();
               return null;
           }
       }
    }
}