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;

/// <summary>
/// 收付款
/// </summary>
public class APCancelDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(APCancel m)
    {
        string sql = @"insert ARAP_APCancel(sfk_id,apd_id,hx_amount,nat_hx_amount)
                        select @sfk_id,@apd_id,@hx_amount,@nat_hx_amount";
        SqlParameter[] par = {
            new SqlParameter("@sfk_id",XTools.IntToDBNull(m.sfk_id)),
            new SqlParameter("@apd_id",XTools.IntToDBNull(m.apd_id)),
            new SqlParameter("@hx_amount",XTools.DecimalToDBNull(m.hx_amount)),
            new SqlParameter("@nat_hx_amount",XTools.DecimalToDBNull(m.nat_hx_amount))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }

    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">acl_id</param>
    /// <returns>APCancel</returns>
    public APCancel Get(int acl_id)
    {
        APCancel m = new APCancel();
        string sql = @"select * from ARAP_APCancel
                    where acl_id=@acl_id";
        sql = sql.Replace("@acl_id", acl_id.ToString());
        //提供sql给打印用
        sql_print = sql;
        SqlUtil su = new SqlUtil();
        using (DataSet dt = su.GetDataSet(sql, null))
        {
            foreach (DataRow r in dt.Tables[0].Rows)
            {
                m.acl_id = Convert.ToInt32(r["acl_id"]);
                m.sfk_id = XTools.DBNullToInt(r["sfk_id"]);
                m.apd_id = XTools.DBNullToInt(r["apd_id"]);
                m.hx_amount = XTools.DBNullToDecimal(r["hx_amount"]);
                m.nat_hx_amount = XTools.DBNullToDecimal(r["nat_hx_amount"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(APCancel m)
    {
        string sql = @"update ARAP_APCancel set 
                        sfk_id=@sfk_id,
                        apd_id=@apd_id,
                        hx_amount=@hx_amount,
                        nat_hx_amount=@nat_hx_amount
                       where acl_id=@acl_id";
        SqlParameter[] par = {
            new SqlParameter("@acl_id",m.acl_id),
            new SqlParameter("@sfk_id",XTools.IntToDBNull(m.sfk_id)),
            new SqlParameter("@apd_id",XTools.IntToDBNull(m.apd_id)),
            new SqlParameter("@hx_amount",XTools.DecimalToDBNull(m.hx_amount)),
            new SqlParameter("@nat_hx_amount",XTools.DecimalToDBNull(m.nat_hx_amount))
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public string Delete(int acl_id)
    {
        //删除主表
        string sql = "delete ARAP_APCancel where acl_id=@acl_id";
        SqlParameter[] par = {
            new SqlParameter("@acl_id",acl_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
        return "success";
    }
    /// <summary>
    /// 通过收付款单删除整个核销子表
    /// </summary>
    /// <param name="sfk_id">主表ID</param>
    public void DeleteByOrder(int sfk_id)
    {
        CheckAccounts m = new CheckAccounts();
        string sql = "delete ARAP_APCancel where sfk_id=@sfk_id";
        SqlParameter[] par = {
                new SqlParameter("@sfk_id",sfk_id)
            };
        new SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number)
    {
        string sql = "select count(1) from ARAP_APCancel where order_number=@order_number";
        SqlParameter[] par ={
                new SqlParameter("@order_number", order_number)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 得到最大ID号
    /// </summary>
    public int GetMaxID()
    {
        string sql = "select isnull(max(acl_id),0) from ARAP_APCancel";
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, null));
    }
    //打印用的SQL
    public string sql_print = "";
    /// <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(
	                select acl.*,crc.crc_name,apd.from_type,apd.from_order_number,apd.bus_order_number,
                    apd.tax_total_price as from_order_amount,apd.acc_hx_amount,apd.acc_hx_nat_amount,
                    isnull(apd.tax_total_price,0)-isnull(apd.acc_hx_amount,0) as wait_hx_amount,
                    apd.i_id,apd.free1,apd.free2,apd.batch,
                    i.i_code,i.i_name,i.standard,i.munit,i.aunit,
	                apd.d_sdefine1,apd.d_sdefine2,apd.d_sdefine3,apd.d_sdefine4,apd.d_sdefine5,
	                apd.d_ndefine1,apd.d_ndefine2,apd.d_ndefine3,apd.d_ndefine4,apd.d_ndefine5
                    from ARAP_APCancel acl
                    left join ARAP_APDetail apd on acl.apd_id=apd.apd_id
                    left join Base_Currency crc on crc.crc_id=apd.crc_id
                    left join Base_Inventory i on apd.i_id=i.i_id
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .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 ARAP_APCancel where 1=1 ";
        //分页处理与查询条件
        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>
    /// <returns>int</returns>
    public int GetOneOrder_TotalNumbers(int sfk_id)
    {
        string sql = "select count(1) from ARAP_APCancel where sfk_id=@sfk_id";
        SqlParameter[] par = {
                new SqlParameter("@sfk_id",sfk_id)
            };
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, par);
        return total_numbers;
    }

    /// <summary>
    /// 得到核销单表体
    /// </summary>
    /// <param name="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList_For_SFKuan(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(
	                select acl.co_id,acl.sfk_id,acl.acl_id,acl.d_remark,
                    acl.hx_amount,acl.nat_hx_amount,
                    sfk.order_number,convert(char(10),sfk.new_date,23) as new_date,
                    sfk.amount,sfk.nat_amount,
                    sfk.acc_hx_amount,
                    sfk.acc_hx_nat_amount,
                    round(isnull(sfk.amount,0)-isnull(sfk.acc_hx_amount,0),2) as wait_hx_amount,
                    round(isnull(sfk.nat_amount,0)-isnull(sfk.acc_hx_nat_amount,0),2) as wait_hx_nat_amount
                    from ARAP_APCancel acl
                    left join ARAP_SFKuan sfk on acl.sfk_id=sfk.sfk_id
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .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_For_SFKuan(Hashtable pg)
    {
        string sql = "select count(1) from ARAP_APCancel where 1=1 ";
        //分页处理与查询条件
        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="sql_select_field">格式定义指定显示列</param>
    /// <returns>DataTable</returns>
    public DataTable GetList_For_APDetail(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(
	                select acl.co_id,acl.apd_id,acl.acl_id,acl.d_remark,
                    acl.hx_amount,
                    acl.nat_hx_amount,
                    apd.from_order_number as order_number,
                    convert(char(10),apd.new_date,23) as new_date,
                    apd.tax_total_price as amount,apd.nat_tax_total_price as nat_amount,
                    apd.acc_hx_amount,
                    apd.acc_hx_nat_amount,
                    isnull(apd.tax_total_price,0)-isnull(apd.acc_hx_amount,0) as wait_hx_amount,
                    isnull(apd.nat_tax_total_price,0)-isnull(apd.acc_hx_nat_amount,0) as wait_hx_nat_amount
                    from ARAP_APCancel acl
                    left join ARAP_APDetail apd on acl.apd_id=apd.apd_id
                )t
                where 1=1 ";
        sql = sql.Replace("@order_field", order_field)
                 .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_For_APDetail(Hashtable pg)
    {
        string sql = "select count(1) from ARAP_APCancel where 1=1 ";
        //分页处理与查询条件
        PageHelper ph = new PageHelper();
        sql = ph.GetPageSQL(sql, pg, false);//得到分页SQL
        int total_numbers = (int)new SqlUtil().ExecuteScalar(sql, null);
        return total_numbers;
    }
}
