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 APDetailDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(APDetail m)
    {
        string sql = @"insert ARAP_APDetail(rp_flag,invoice_no,dep_id,i_id,free1,free2,batch,cas_id,orp_id,
                                unit_price,tax_unit_price,quantity,num,convert_rate,
                                total_price,tax_total_price,
                                nat_unit_price,nat_total_price,nat_tax_unit_price,
                                nat_tax_total_price,tax_rate,crc_id,exchange_rate,remark,
                                cus_id,vd_id,ele_id,
                                new_uid,new_user,new_date,
                                update_uid,update_user,update_date,
                                verify_uid,verify_user,
                                from_id,from_type,bus_type,from_order_number,bus_order_number,
                                d_sdefine1,d_sdefine2,d_sdefine3,d_sdefine4,d_sdefine5,
                                d_ndefine1,d_ndefine2,d_ndefine3,d_ndefine4,d_ndefine5)
                        select  @rp_flag,@invoice_no,@dep_id,@i_id,@free1,@free2,@batch,@cas_id,@orp_id,
                                @unit_price,@tax_unit_price,@quantity,@num,@convert_rate,
                                @total_price,@tax_total_price,
                                @nat_unit_price,@nat_total_price,@nat_tax_unit_price,
                                @nat_tax_total_price,@tax_rate,@crc_id,@exchange_rate,@remark,
                                @cus_id,@vd_id,@ele_id,
                                @new_uid,@new_user,@new_date,
                                @update_uid,@update_user,@update_date,
                                @verify_uid,@verify_user,
                                @from_id,@from_type,@bus_type,@from_order_number,@bus_order_number,
                                @d_sdefine1,@d_sdefine2,@d_sdefine3,@d_sdefine4,@d_sdefine5,
                                @d_ndefine1,@d_ndefine2,@d_ndefine3,@d_ndefine4,@d_ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@rp_flag",m.rp_flag),
            new SqlParameter("@invoice_no",XTools.StringToDBNull(m.invoice_no)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@i_id",XTools.IntToDBNull(m.i_id)),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@batch",XTools.StringToDBNull(m.batch)),
            new SqlParameter("@cas_id",XTools.IntToDBNull(m.cas_id)),
            new SqlParameter("@orp_id",XTools.IntToDBNull(m.orp_id)),
            new SqlParameter("@unit_price",XTools.DecimalToDBNull(m.unit_price)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@total_price",XTools.DecimalToDBNull(m.total_price)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@nat_unit_price",XTools.DecimalToDBNull(m.nat_unit_price)),
            new SqlParameter("@nat_total_price",XTools.DecimalToDBNull(m.nat_total_price)),
            new SqlParameter("@nat_tax_unit_price",XTools.DecimalToDBNull(m.nat_tax_unit_price)),
            new SqlParameter("@nat_tax_total_price",XTools.DecimalToDBNull(m.nat_tax_total_price)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@exchange_rate",XTools.DecimalToDBNull(m.exchange_rate)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@new_uid",XTools.IntToDBNull(m.new_uid)),
            new SqlParameter("@new_user",XTools.StringToDBNull(m.new_user)),
            new SqlParameter("@new_date",m.new_date),
            new SqlParameter("@update_uid",XTools.IntToDBNull(m.update_uid)),
            new SqlParameter("@update_user",XTools.StringToDBNull(m.update_user)),
            new SqlParameter("@update_date",XTools.DateTimeToDBNull(m.update_date)),
            new SqlParameter("@verify_uid",XTools.IntToDBNull(m.verify_uid)),
            new SqlParameter("@verify_user",XTools.StringToDBNull(m.verify_user)),
            new SqlParameter("@from_id",XTools.IntToDBNull(m.from_id)),
            new SqlParameter("@from_type",XTools.StringToDBNull(m.from_type)),
            new SqlParameter("@from_order_number",XTools.StringToDBNull(m.from_order_number)),
            new SqlParameter("@bus_order_number",XTools.StringToDBNull(m.bus_order_number)),
            new SqlParameter("@bus_type",XTools.StringToDBNull(m.bus_type)),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@d_sdefine1",XTools.StringToDBNull(m.d_sdefine1)),
            new SqlParameter("@d_sdefine2",XTools.StringToDBNull(m.d_sdefine2)),
            new SqlParameter("@d_sdefine3",XTools.StringToDBNull(m.d_sdefine3)),
            new SqlParameter("@d_sdefine4",XTools.StringToDBNull(m.d_sdefine4)),
            new SqlParameter("@d_sdefine5",XTools.StringToDBNull(m.d_sdefine5)),
            new SqlParameter("@d_ndefine1",XTools.DecimalToDBNull(m.d_ndefine1)),
            new SqlParameter("@d_ndefine2",XTools.DecimalToDBNull(m.d_ndefine2)),
            new SqlParameter("@d_ndefine3",XTools.DecimalToDBNull(m.d_ndefine3)),
            new SqlParameter("@d_ndefine4",XTools.DecimalToDBNull(m.d_ndefine4)),
            new SqlParameter("@d_ndefine5",XTools.DecimalToDBNull(m.d_ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">apd_id</param>
    /// <returns>APDetail</returns>
    public APDetail Get(int apd_id)
    {
        APDetail m = new APDetail();
        string sql = @"select d.*,
                    i.sdefine1 as d_inv_sdefine1,i.sdefine2 as d_inv_sdefine2,i.sdefine3 as d_inv_sdefine3,
                    i.sdefine4 as d_inv_sdefine4,i.sdefine5 as d_inv_sdefine5,
                    i.ndefine1 as d_inv_ndefine1,i.ndefine2 as d_inv_ndefine2,i.ndefine3 as d_inv_ndefine3,
                    i.ndefine4 as d_inv_ndefine4,i.ndefine5 as d_inv_ndefine5
                    from ARAP_APDetail d 
                    left join Base_Inventory i on d.i_id=i.i_id
                    where d.apd_id=@apd_id";
        SqlParameter[] par = {
                new SqlParameter("@apd_id",apd_id)
            };
        CheckAccountDAL parent_dal = new CheckAccountDAL();
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, par))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.apd_id = Convert.ToInt32(r["apd_id"]);//子表id
                m.rp_flag = r["rp_flag"].ToString();//收付标志:AR应收、AP应付
                m.invoice_no = r["invoice_no"].ToString();
                m.i_id = Convert.ToInt32(r["i_id"]);//存货id
                if(r["i_id"] != null)
                    m.inventory = new InventoryDAL().Get((int)m.i_id);
                m.free1 = r["free1"].ToString();
                m.free2 = r["free2"].ToString();
                m.batch = r["batch"].ToString();
                m.cas_id = XTools.DBNullToInt(r["cas_id"]);//进出单子表ID
                if (r["cas_id"] != null)
                    m.check_accounts = new CheckAccountsDAL().Get(Convert.ToInt32(m.cas_id));
                m.orp_id = XTools.DBNullToInt(r["orp_id"]);//应收应付单主表ID
                if (r["orp_id"] != null)
                    m.other_rp = new OtherRPDAL().Get(Convert.ToInt32(m.orp_id));
                m.unit_price = XTools.DBNullToDecimal(r["unit_price"]);//无税单价
                m.tax_unit_price = XTools.DBNullToDecimal(r["tax_unit_price"]);//含税单价
                m.quantity = XTools.DBNullToDecimal(r["quantity"]);//数量
                m.num = XTools.DBNullToDecimal(r["num"]);//件数
                m.convert_rate = XTools.DBNullToDecimal(r["convert_rate"]);//换算率
                m.total_price = XTools.DBNullToDecimal(r["total_price"]);//无税金额
                m.tax_total_price = XTools.DBNullToDecimal(r["tax_total_price"]);//含税金额
                m.nat_unit_price = XTools.DBNullToDecimal(r["nat_unit_price"]);//本币单价
                m.nat_total_price = XTools.DBNullToDecimal(r["nat_total_price"]);//本币金额
                m.nat_tax_unit_price = XTools.DBNullToDecimal(r["nat_tax_unit_price"]);//本币含税单价
                m.nat_tax_total_price = XTools.DBNullToDecimal(r["nat_tax_total_price"]);//本币含税金额
                m.tax_rate = XTools.DBNullToDecimal(r["tax_rate"]);//税率
                m.remark = r["remark"].ToString();//备注
                m.crc_id = XTools.DBNullToInt(r["crc_id"]);
                if (r["crc_id"] != DBNull.Value)
                    m.currency = new CurrencyDAL().Get(Convert.ToInt32(m.crc_id));
                m.exchange_rate = XTools.DBNullToDecimal(r["exchange_rate"]);
                m.dep_id = XTools.DBNullToInt(r["dep_id"]);//部门id
                if (r["dep_id"] != DBNull.Value)
                    m.department = new DepartmentDAL().Get((int)m.dep_id);
                m.ele_id = XTools.DBNullToInt(r["ele_id"]);//员工id
                if (r["ele_id"] != DBNull.Value)
                    m.employee = new EmployeeDAL().Get(Convert.ToInt32(m.ele_id));
                m.vd_id = XTools.DBNullToInt(r["vd_id"]);
                if (r["vd_id"] != DBNull.Value)
                    m.vendor = new VendorDAL().Get(Convert.ToInt32(m.vd_id));
                m.cus_id = XTools.DBNullToInt(r["cus_id"]);
                if (r["cus_id"] != DBNull.Value)
                    m.customer = new CustomerDAL().Get(Convert.ToInt32(m.cus_id));
                if (r["cas_id"] != DBNull.Value)
                    m.check_accounts = new CheckAccountsDAL().Get(Convert.ToInt32(m.cas_id));
                m.new_uid = XTools.DBNullToInt(r["new_uid"]);
                m.new_user = r["new_user"].ToString();
                m.verify_uid = XTools.DBNullToInt(r["verify_uid"]);
                m.verify_user = r["verify_user"].ToString();
                m.update_uid = XTools.DBNullToInt(r["update_uid"]);
                m.update_user = r["update_user"].ToString();
                m.update_date = XTools.DBNullToTime(r["update_date"]);
                m.from_id = XTools.DBNullToInt(r["from_id"]);
                m.from_type = r["from_type"].ToString();
                m.bus_type = r["bus_type"].ToString();
                m.from_order_number = r["from_order_number"].ToString();
                m.bus_order_number = r["bus_order_number"].ToString();
                m.d_sdefine1 = r["d_sdefine1"].ToString();
                m.d_sdefine2 = r["d_sdefine2"].ToString();
                m.d_sdefine3 = r["d_sdefine3"].ToString();
                m.d_sdefine4 = r["d_sdefine4"].ToString();
                m.d_sdefine5 = r["d_sdefine5"].ToString();
                m.d_ndefine1 = XTools.DBNullToDecimal(r["d_ndefine1"]);
                m.d_ndefine2 = XTools.DBNullToDecimal(r["d_ndefine2"]);
                m.d_ndefine3 = XTools.DBNullToDecimal(r["d_ndefine3"]);
                m.d_ndefine4 = XTools.DBNullToDecimal(r["d_ndefine4"]);
                m.d_ndefine5 = XTools.DBNullToDecimal(r["d_ndefine5"]);

            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(APDetail m)
    {
        string sql = @"update ARAP_APDetail set
                        rp_flag=@rp_flag,invoice_no=@invoice_no,
                        i_id=@i_id,free1=@free1,free2=@free2,
                        batch=@batch,cas_id=@cas_id,orp_id=@orp_id,order_type=@order_type,
                        unit_price=@unit_price,tax_unit_price=@tax_unit_price,
                        quantity=@quantity,num=@num,convert_rate=@convert_rate,
                        total_price=@total_price,tax_total_price=@tax_total_price,
                        nat_unit_price=@nat_unit_price,nat_total_price=@nat_total_price,
                        nat_tax_unit_price=@nat_tax_unit_price,
                        nat_tax_total_price=@nat_tax_total_price,
                        crc_id=@crc_id,exchange_rate=@exchange_rate,
                        tax_rate=@tax_rate,remark=@remark,
                        from_id=@from_id,
                        from_type=@from_type,
                        bus_type=@bus_type,
                        from_order_number=@from_order_number,
                        bus_order_number=@bus_order_number,
                        new_uid=@new_uid,
                        new_user=@new_user,
                        new_date=@new_date,
                        update_uid=@update_uid,
                        update_user=@update_user,
                        update_date=@update_date,
                        verify_uid=@verify_uid,
                        verify_user=@verify_user,
                        dep_id=@dep_id,ele_id=@ele_id,cus_id=@cus_id,vd_id=@vd_id,
                        d_sdefine1=@d_sdefine1,d_sdefine2=@d_sdefine2,d_sdefine3=@d_sdefine3,
                        d_sdefine4=@d_sdefine4,d_sdefine5=@d_sdefine5,
                        d_ndefine1=@d_ndefine1,d_ndefine2=@d_ndefine2,d_ndefine3=@d_ndefine3,
                        d_ndefine4=@d_ndefine4,d_ndefine5=@d_ndefine5
                        where apd_id=@apd_id";
        SqlParameter[] par = {
            new SqlParameter("@apd_id",m.apd_id),
            new SqlParameter("@rp_flag",m.rp_flag),
            new SqlParameter("@invoice_no",XTools.StringToDBNull(m.invoice_no)),
            new SqlParameter("@i_id",m.i_id),
            new SqlParameter("@free1",XTools.StringToDBNull(m.free1)),
            new SqlParameter("@free2",XTools.StringToDBNull(m.free2)),
            new SqlParameter("@batch",XTools.StringToDBNull(m.batch)),
            new SqlParameter("@cas_id",XTools.IntToDBNull(m.cas_id)),
            new SqlParameter("@orp_id",XTools.IntToDBNull(m.orp_id)),
            new SqlParameter("@unit_price",XTools.DecimalToDBNull(m.unit_price)),
            new SqlParameter("@tax_unit_price",XTools.DecimalToDBNull(m.tax_unit_price)),
            new SqlParameter("@quantity",XTools.DecimalToDBNull(m.quantity)),
            new SqlParameter("@num",XTools.DecimalToDBNull(m.num)),
            new SqlParameter("@convert_rate",XTools.DecimalToDBNull(m.convert_rate)),
            new SqlParameter("@total_price",XTools.DecimalToDBNull(m.total_price)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@nat_unit_price",XTools.DecimalToDBNull(m.nat_unit_price)),
            new SqlParameter("@nat_total_price",XTools.DecimalToDBNull(m.nat_total_price)),
            new SqlParameter("@nat_tax_unit_price",XTools.DecimalToDBNull(m.nat_tax_unit_price)),
            new SqlParameter("@nat_tax_total_price",XTools.DecimalToDBNull(m.nat_tax_total_price)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@exchange_rate",XTools.DecimalToDBNull(m.exchange_rate)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@from_id",XTools.IntToDBNull(m.from_id)),
            new SqlParameter("@from_type",XTools.StringToDBNull(m.from_type)),
            new SqlParameter("@bus_type",XTools.StringToDBNull(m.bus_type)),
            new SqlParameter("@from_order_number",XTools.StringToDBNull(m.from_order_number)),
            new SqlParameter("@bus_order_number",XTools.StringToDBNull(m.bus_order_number)),
            new SqlParameter("@remark",XTools.StringToDBNull(m.remark)),
            new SqlParameter("@new_uid",XTools.IntToDBNull(m.new_uid)),
            new SqlParameter("@new_user",XTools.StringToDBNull(m.new_user)),
            new SqlParameter("@new_date",XTools.DateTimeToDBNull(m.new_date)),
            new SqlParameter("@update_uid",XTools.IntToDBNull(m.update_uid)),
            new SqlParameter("@update_user",XTools.StringToDBNull(m.update_user)),
            new SqlParameter("@update_date",XTools.DateTimeToDBNull(m.update_date)),
            new SqlParameter("@verify_uid",XTools.IntToDBNull(m.verify_uid)),
            new SqlParameter("@verify_user",XTools.StringToDBNull(m.verify_user)),
            new SqlParameter("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@cas_id",XTools.IntToDBNull(m.cas_id)),
            new SqlParameter("@d_sdefine1",XTools.StringToDBNull(m.d_sdefine1)),
            new SqlParameter("@d_sdefine2",XTools.StringToDBNull(m.d_sdefine2)),
            new SqlParameter("@d_sdefine3",XTools.StringToDBNull(m.d_sdefine3)),
            new SqlParameter("@d_sdefine4",XTools.StringToDBNull(m.d_sdefine4)),
            new SqlParameter("@d_sdefine5",XTools.StringToDBNull(m.d_sdefine5)),
            new SqlParameter("@d_ndefine1",XTools.DecimalToDBNull(m.d_ndefine1)),
            new SqlParameter("@d_ndefine2",XTools.DecimalToDBNull(m.d_ndefine2)),
            new SqlParameter("@d_ndefine3",XTools.DecimalToDBNull(m.d_ndefine3)),
            new SqlParameter("@d_ndefine4",XTools.DecimalToDBNull(m.d_ndefine4)),
            new SqlParameter("@d_ndefine5",XTools.DecimalToDBNull(m.d_ndefine5))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除应收应付明细
    /// </summary>
    /// <param name="apd_id">子表ID</param>
    public void Delete(int apd_id)
    {
        APDetail m = new APDetail();
        string sql = "delete ARAP_APDetail where apd_id=@apd_id";
        SqlParameter[] par = {
                new SqlParameter("@apd_id",apd_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

}
