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 CancelOrderDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(CancelOrder m)
    {
        string sql = @"insert ARAP_CancelOrder(cl_type,order_number,cus_id,vd_id,
			                        crc_id,exchange_rate,remark,
                                    new_uid,new_user,new_date,
                                    update_uid,update_user,update_date,
                                    verify_uid,verify_user)
                        select  @cl_type,@order_number,@cus_id,@vd_id,
			                    @crc_id,@exchange_rate,@remark,
                                @new_uid,@new_user,@new_date,
                                @update_uid,@update_user,@update_date,
                                @verify_uid,@verify_user";
        SqlParameter[] par = {
            new SqlParameter("@cl_type",m.cl_type),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            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 SqlUtil().ExecuteNonQuery(sql, par);
    }
    //打印用的SQL
    public string sql_print = "";
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">co_id</param>
    /// <returns>CancelOrder</returns>
    public CancelOrder Get(int co_id)
    {
        CancelOrder m = new CancelOrder();
        string sql = @"select ca.*,convert(char(10),ca.new_date,23) as new_date_format,
                    cus.cus_name,vd.vd_name,crc.crc_name
                    from ARAP_CancelOrder ca
                    left join Base_Customer cus on ca.cus_id=cus.cus_id
                    left join Base_Vendor vd on ca.vd_id=vd.vd_id
                    left join Base_Currency crc on ca.crc_id=crc.crc_id
                    where ca.co_id=@co_id";
        sql = sql.Replace("@co_id", co_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.co_id = Convert.ToInt32(r["co_id"]);
                m.cl_type = Convert.ToInt32(r["cl_type"]);
                if (r["cl_type"] != DBNull.Value)
                    m.cancel_type = new CancelTypeDAL().Get(Convert.ToInt32(m.cl_type));
                m.order_number = r["order_number"].ToString();
                m.new_date = XTools.DBNullToTime(r["new_date"]);
                m.cus_id = XTools.DBNullToInt(r["cus_id"]);
                if(r["cus_id"] != DBNull.Value)
                    m.customer = new CustomerDAL().Get(Convert.ToInt32(m.cus_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.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.remark = r["remark"].ToString();
                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"]);
            }
        }
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(CancelOrder m)
    {
        string sql = @"update ARAP_CancelOrder set 
                        cl_type=@cl_type,
                        order_number=@order_number,
                        cus_id=@cus_id,
                        vd_id=@vd_id,
                        crc_id=@crc_id,
                        exchange_rate=@exchange_rate,
                        remark=@remark,
                        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
                       where co_id=@co_id";
        SqlParameter[] par = {
            new SqlParameter("@co_id",m.co_id),
            new SqlParameter("@cl_type",m.cl_type),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@cus_id",XTools.IntToDBNull(m.cus_id)),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@exchange_rate",XTools.DecimalToDBNull(m.exchange_rate)),
            new SqlParameter("@remark",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 SqlUtil().ExecuteNonQuery(sql, par);
    }
    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="id">ID</param>
    public string Delete(int co_id)
    {
        //删除主表
        string sql = @"
                    delete ARAP_APCancel where co_id=@co_id;
                    delete ARAP_CancelOrder where co_id=@co_id;";
        SqlParameter[] par = {
            new SqlParameter("@co_id",co_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
        return "success";
    }
    /// <summary>
    /// 验证单据号是否存在
    /// </summary>
    /// <param name="order_number">编码</param>
    /// <returns>bool</returns>
    public bool CheckExist(string order_number)
    {
        string sql = "select count(1) from ARAP_CancelOrder 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(co_id),0) from ARAP_CancelOrder";
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, null));
    }
    /// <summary>
    /// 得到上一个ID
    /// </summary>
    /// <param name="current_id">当前id</param>
    /// <param name="type">1上翻,2下翻</param>
    /// <param name="cl_type">AR收款,AP付款</param>
    /// <returns></returns>
    public int GetPrevNextID(int current_id, string type)
    {
        string sql = @"select isnull(max(co_id),0) from ARAP_CancelOrder 
                    where (co_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(co_id),0) from ARAP_CancelOrder where co_id>@current_id";
        }
        SqlParameter[] par ={
                new SqlParameter("@current_id", current_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="do_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int co_id)
    {
        return "success";
    }
    /// <summary>
    /// 得到某张收付款单的所有核销明细
    /// </summary>
    /// <param name="ca_id"></param>
    /// <returns></returns>
    public DataTable GetDetailsByOrder(int co_id)
    {
        string sql = "select * from ARAP_APCancel where co_id=@co_id";
        SqlParameter[] par = {
            new SqlParameter("@co_id", co_id)
        };
        return new SqlUtil().GetDataTable(sql, par);
    }
    /// <summary>
    /// 加载需要核销的应收应付账
    /// </summary>
    /// <param name="sfk_id">收款单主表ID</param>
    /// <returns></returns>
    public string LoadNeedForCancel(int co_id)
    {
        CancelOrder m = Get(co_id);
        int? cus_id = m.cus_id;
        int? vd_id = m.vd_id;
        int? crc_id = m.crc_id;
        int cl_type = m.cl_type;
        DateTime dt = DateTime.Parse(m.new_date.ToString());
        string crc_year = dt.Year.ToString();
        string crc_month = dt.Month.ToString();
        string sql = @"
                    delete ARAP_APCancel where co_id=@co_id;
                    insert ARAP_APCancel(co_id,cl_type,sfk_id,d_remark)
                    select @co_id,@cl_type,sfk.sfk_id,sfk.remark
                    from ARAP_SFKuan sfk
                    where 1=1 ";
        sql += " and sfk.crc_id=@crc_id";

        sql += " and isnull(sfk.amount,0)-isnull(sfk.acc_hx_amount,0)>0 and isnull(sfk.nat_amount,0)-isnull(sfk.acc_hx_nat_amount,0)>0 ";
        if (m.cl_type == 3)
        {
            sql += " and cus_id=@cus_id";
            sql = sql.Replace("@cus_id", cus_id.ToString());
        }
          else if (m.cl_type == 4)
        {
            sql += " and vd_id=@vd_id";
            sql = sql.Replace("@vd_id", vd_id.ToString());
        }

        sql += @"
                    insert ARAP_APCancel(co_id,cl_type,apd_id,d_remark)
                    select @co_id as co_id,@cl_type,apd.apd_id,apd.remark
                    from ARAP_APDetail apd
                    where 1=1 ";
        sql += " and apd.crc_id=@crc_id";

        sql += " and ISNULL(tax_total_price,0)>ISNULL(acc_hx_amount,0) and ISNULL(nat_tax_total_price,0)>ISNULL(acc_hx_nat_amount,0) ";
        if (m.cl_type == 3)
        {
            sql += " and cus_id=@cus_id";
            sql = sql.Replace("@cus_id", cus_id.ToString());
        }
        else if (m.cl_type == 4)
        {
            sql += " and vd_id=@vd_id";
            sql = sql.Replace("@vd_id", vd_id.ToString());
        }

        sql = sql.Replace("@co_id", co_id.ToString())
                .Replace("@crc_id", crc_id.ToString())
                .Replace("@cl_type", cl_type.ToString())
                .Replace("@crc_year", crc_year)
                .Replace("@crc_month", crc_month)
                .Replace("@crc_id", crc_id.ToString());
        new SqlUtil().ExecuteNonQuery(sql, null);
        return "success";
    }
    /// <summary>
    /// 检查是否应收款是否超预收款单金额核销
    /// </summary>
    /// <param name="co_id">核销单ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckCancelOverSFKuanAmount(int co_id)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select '单据：' + co.order_number + ' 核销金额:'+cast(isnull(t2.hx2,0) as varchar(50))+' 不等于 本单金额:'+cast(isnull(t1.hx1,0) as varchar(50)) as info
                        from ARAP_CancelOrder co
                        left join
                        (
	                        select co_id,sum(hx_amount) as hx1 from 
	                        ARAP_APCancel where co_id=@co_id and isnull(sfk_id,0)<>0
	                        group by co_id
                        )t1 on co.co_id=t1.co_id
                        left join(
	                        select co_id,sum(hx_amount) as hx2 from 
	                        ARAP_APCancel where co_id=@co_id and isnull(apd_id,0)<>0
	                        group by co_id
                        )t2 on t1.co_id=t2.co_id
                        where round(isnull(t1.hx1,0),2)<>round(isnull(t2.hx2,0),2)
                    )x";
        SqlParameter[] par ={
                new SqlParameter("@co_id", co_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }
    /// <summary>
    /// 检查本次核销金额是否超应收金额
    /// </summary>
    /// <param name="co_id">核销单ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckCurrentOverAPAmount(int co_id)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select '单据：' + co.order_number + ' 核销总金额:'+cast(isnull(cl.nat_hx_amount,0)+ISNULL(apd.acc_hx_nat_amount,0) as varchar(50))+' 大于 应收应付总金额:'+cast(isnull(apd.nat_tax_total_price,0) as varchar(50)) as info
	                    from ARAP_APCancel cl
                        left join ARAP_CancelOrder co on cl.co_id=co.co_id
	                    left join ARAP_APDetail apd
	                    on cl.apd_id=apd.apd_id
	                    where cl.co_id=@co_id and isnull(cl.apd_id,0)<>0
	                    and round(isnull(cl.nat_hx_amount,0)+ISNULL(apd.acc_hx_nat_amount,0),2)>round(isnull(apd.nat_tax_total_price,0),2)

                    )x";
        SqlParameter[] par ={
                new SqlParameter("@co_id", co_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }

    /// <summary>
    /// 检查本次核销金额是否超收付款金额
    /// </summary>
    /// <param name="co_id">核销单ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckCurrentOverSFKuanAmount(int co_id)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select '单据：' + co.order_number + ' 核销总金额:'+cast(isnull(cl.nat_hx_amount,0)+ISNULL(sfk.acc_hx_nat_amount,0) as varchar(50))+' 大于 收付款单总金额:'+cast(isnull(sfk.nat_amount,0) as varchar(50)) as info
                        from ARAP_APCancel cl
                        left join ARAP_CancelOrder co on cl.co_id=co.co_id
                        left join ARAP_SFKuan sfk
                        on cl.sfk_id=sfk.sfk_id
                        where cl.co_id=@co_id and isnull(cl.sfk_id,0)<>0
                        and round(isnull(cl.nat_hx_amount,0)+ISNULL(sfk.acc_hx_nat_amount,0),2)>round(isnull(sfk.nat_amount,0),2)
                    )x";
        SqlParameter[] par ={
                new SqlParameter("@co_id", co_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }
    /// <summary>
    /// 核销单审核更新应收应付账的累计核销金额
    /// </summary>
    /// <param name="co_id">核销单主表ID</param>
    /// <param name="direction">方向：1,加累计核销金额;-1,减累计核销金额</param>
    /// <returns></returns>
    public string UpdateAPDetailHxAmount(int co_id, int direction)
    {
        string sql = @"
            update ARAP_APDetail set 
            acc_hx_amount=isnull(acc_hx_amount,0) @plus ISNULL(acl.hx_amount,0),
            acc_hx_nat_amount=isnull(acc_hx_nat_amount,0) @plus ISNULL(acl.nat_hx_amount,0)
            from ARAP_APCancel acl
            where acl.apd_id=ARAP_APDetail.apd_id
            and acl.co_id=@co_id and isnull(acl.apd_id,0)<>0;

            update ARAP_SFKuan set 
            acc_hx_amount=isnull(acc_hx_amount,0) @plus isnull(t.hx_amount,0),
            acc_hx_nat_amount=isnull(acc_hx_nat_amount,0) @plus isnull(t.nat_hx_amount,0)
            from
            (
	            select sfk_id,sum(hx_amount) as hx_amount,sum(nat_hx_amount) as nat_hx_amount
	            from ARAP_APCancel where co_id=@co_id and isnull(sfk_id,0)<>0
	            group by sfk_id
            )t where ARAP_SFKuan.sfk_id=t.sfk_id;

            delete ARAP_APCancel where co_id=@co_id and isnull(hx_amount,0)=0;";
        sql = sql.Replace("@co_id", co_id.ToString());
        if (direction == 1)
        {
            //加
            sql = sql.Replace("@plus", "+");
        }
        else
        {
            //减
            sql = sql.Replace("@plus", "-");
        }
        new SqlUtil().ExecuteNonQuery(sql, null);
        return "success";
    }
}
