using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SysCore;
using SysCore.Control;

public class CusInvDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(CusInv m)
    {
        string sql = @"insert Base_CusInv(cus_id,i_id,free1,free2,
                        cus_i_name,cus_price,crc_id,tax_rate,
                        sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                        ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select @cus_id,@i_id,@free1,@free2,
                        @cus_i_name,@cus_price,@crc_id,@tax_rate,
                        @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                        @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@cus_id",m.cus_id),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@cus_i_name",XTools.StringToDBNull(m.cus_i_name)),
            new SqlParameter("@cus_price",XTools.DecimalToDBNull(m.cus_price)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">ci_id</param>
    /// <returns>CusInv</returns>
    public CusInv Get(int ci_id)
    {
        CusInv m = new CusInv();
        string sql = @"select *
                      from Base_CusInv where ci_id=@ci_id";
        SqlParameter[] par = {
                new SqlParameter("@ci_id",ci_id)
            };
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            CustomerDAL cus_dal = new CustomerDAL();
            InventoryDAL i_dal = new InventoryDAL();
            CurrencyDAL crc_dal = new CurrencyDAL();
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.ci_id = Convert.ToInt32(r["ci_id"]);
                m.cus_id = Convert.ToInt32(r["cus_id"]);
                if (r["cus_id"] != DBNull.Value)
                {
                    m.customer = cus_dal.Get(Convert.ToInt32(r["cus_id"]));
                }
                m.i_id = Convert.ToInt32(r["i_id"]);
                if (r["i_id"] != DBNull.Value)
                {
                    m.inventory = i_dal.Get(Convert.ToInt32(r["i_id"]));
                }
                m.free1 = r["free1"].ToString();
                m.free2 = r["free2"].ToString();
                m.cus_i_name = r["cus_i_name"].ToString();
                m.cus_price = XTools.DBNullToDecimal(r["cus_price"]);
                m.crc_id = XTools.DBNullToInt(r["crc_id"]);
                if (r["crc_id"] != DBNull.Value)
                {
                    m.currency = crc_dal.Get(Convert.ToInt32(r["crc_id"]));
                }
                m.tax_rate = XTools.DBNullToDecimal(r["tax_rate"]);
                m.sdefine1 = r["sdefine1"].ToString();
                m.sdefine2 = r["sdefine2"].ToString();
                m.sdefine3 = r["sdefine3"].ToString();
                m.sdefine4 = r["sdefine4"].ToString();
                m.sdefine5 = r["sdefine5"].ToString();
                m.ndefine1 = XTools.DBNullToDecimal(r["ndefine1"]);
                m.ndefine2 = XTools.DBNullToDecimal(r["ndefine2"]);
                m.ndefine3 = XTools.DBNullToDecimal(r["ndefine3"]);
                m.ndefine4 = XTools.DBNullToDecimal(r["ndefine4"]);
                m.ndefine5 = XTools.DBNullToDecimal(r["ndefine5"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(CusInv m)
    {
        string sql = @"update Base_CusInv set 
                        cus_id=@cus_id,
                        i_id=@i_id,
                        free1=@free1,
                        free2=@free2,
                        cus_i_name=@cus_i_name,
                        cus_price=@cus_price,
                        crc_id=@crc_id,
                        tax_rate=@tax_rate,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where ci_id=@ci_id";
        SqlParameter[] par = {
            new SqlParameter("@ci_id",m.ci_id),
            new SqlParameter("@cus_id",m.cus_id),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@cus_i_name",XTools.StringToDBNull(m.cus_i_name)),
            new SqlParameter("@cus_price",XTools.DecimalToDBNull(m.cus_price)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@sdefine1",XTools.StringToDBNull(m.sdefine1)),
            new SqlParameter("@sdefine2",XTools.StringToDBNull(m.sdefine2)),
            new SqlParameter("@sdefine3",XTools.StringToDBNull(m.sdefine3)),
            new SqlParameter("@sdefine4",XTools.StringToDBNull(m.sdefine4)),
            new SqlParameter("@sdefine5",XTools.StringToDBNull(m.sdefine5)),
            new SqlParameter("@ndefine1",XTools.DecimalToDBNull(m.ndefine1)),
            new SqlParameter("@ndefine2",XTools.DecimalToDBNull(m.ndefine2)),
            new SqlParameter("@ndefine3",XTools.DecimalToDBNull(m.ndefine3)),
            new SqlParameter("@ndefine4",XTools.DecimalToDBNull(m.ndefine4)),
            new SqlParameter("@ndefine5",XTools.DecimalToDBNull(m.ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="ci_id">ci_id</param>
    public void Delete(int ci_id)
    {
        CusInv m = new CusInv();
        string sql = "delete Base_CusInv where ci_id in (@ci_id)";
        SqlParameter[] par = {
                new SqlParameter("@ci_id",ci_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到所有
    /// </summary>
    /// <returns>DataTable</returns>
    public DataTable GetListAll()
    {
        string sql = @"select * from Base_CusInv order by ci_id";
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }

    //打印用的sql
    public string sql_print = "";

    string sql_list = @"
                    select ci.*,crc.crc_name,cus.cus_code,
                    cus.cus_name,cus.cus_full_name,ele.ele_id,ele.ele_name,
                    i.i_name,i.i_code,i.standard,i.munit,i.ic_id,ic.ic_code,ic.ic_name,
                    i.sdefine1 as inv_sdefine1,i.sdefine2 as inv_sdefine2,i.sdefine3 as inv_sdefine3,
                    i.sdefine4 as inv_sdefine4,i.sdefine5 as inv_sdefine5,
                    i.ndefine1 as inv_ndefine1,i.ndefine2 as inv_ndefine2,i.ndefine3 as inv_ndefine3,
                    i.ndefine4 as inv_ndefine4,i.ndefine5 as inv_ndefine5
                    from Base_CusInv ci
                    left join Base_Customer cus on cus.cus_id=ci.cus_id
                    left join Base_Employee ele on ele.ele_id=cus.ele_id
                    left join Base_Inventory i on i.i_id=ci.i_id
                    left join Base_InventoryClass ic on ic.ic_id=i.ic_id
                    left join Base_Currency crc on crc.crc_id=ci.crc_id";

    /// <summary>
    /// 得到列表
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList(Hashtable pg, string sql_select_field)
    {
        string order_field = pg["order_field"].ToString();
        string sql = @"select ROW_NUMBER() OVER(@order_field) AS row_num,@sql_select_field 
                from(@sql_list)t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .Replace("@sql_list", sql_list)
                 .Replace("@sql_select_field", sql_select_field);
        //打印是则不需要分页,只需要加上筛选条件
        sql_print = "select top 1000000000 * from (" + sql + ") t where 1=1 " + pg["sql_end"] + " " + pg["order_field"].ToString();
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, true);//得到分页后的SQL
        DataTable dt = new SqlUtil().GetDataTable(sql, null);
        return dt;
    }
    /// <summary>
    /// 得到总记录数
    /// </summary>
    /// <returns>int</returns>
    public int GetList_TotalNumbers(Hashtable pg)
    {
        string sql = "select count(1) from (@sql_list)t where 1=1 ";
        sql = sql.Replace("@sql_list", sql_list);
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }

    /// <summary>
    /// 验证档案是否存在
    /// </summary>
    /// <param name="cus_code">客户编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(int cus_id, int i_id, string free1, string free2, int? crc_id)
    {
        string sql = @"select count(1) from Base_CusInv 
                        where cus_id=@cus_id
                        and i_id=@i_id and isnull(crc_id,'')=isnull(@crc_id,'')
                        and isnull(free1,'')=isnull(@free1,'')
                        and isnull(free2,'')=isnull(@free2,'')";
        SqlParameter[] par ={
                new SqlParameter("@cus_id", cus_id),
                new SqlParameter("@i_id", i_id),
                new SqlParameter("@crc_id", XTools.IntToDBNull(crc_id)),
                new SqlParameter("@free1", free1),
                new SqlParameter("@free2", free2)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }

}
