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 OtherRPDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(OtherRP m)
    {
        string sql = @"insert ARAP_OtherRP(rp_flag,order_number,cus_id,vd_id,
			                        dep_id,ele_id,crc_id,exchange_rate,remark,
                                    tax_rate,total_price,tax_total_price,
                                    nat_total_price,nat_tax_total_price,
                                    new_uid,new_user,new_date,
                                    update_uid,update_user,update_date,
                                    verify_uid,verify_user,from_id,from_type,
                                    sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                                    ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select  @rp_flag,@order_number,@cus_id,@vd_id,
			                    @dep_id,@ele_id,@crc_id,@exchange_rate,@remark,
                                @tax_rate,@total_price,@tax_total_price,
                                @nat_total_price,@nat_tax_total_price,
                                @new_uid,@new_user,@new_date,
                                @update_uid,@update_user,@update_date,
                                @verify_uid,@verify_user,@from_id,@from_type,
                                @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                                @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@rp_flag",m.rp_flag),
            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("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_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("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@total_price",XTools.DecimalToDBNull(m.total_price)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@nat_total_price",XTools.DecimalToDBNull(m.nat_total_price)),
            new SqlParameter("@nat_tax_total_price",XTools.DecimalToDBNull(m.nat_tax_total_price)),
            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("@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);
    }
    //打印用的SQL
    public string sql_print = "";
    /// <summary>
    /// 得到实体
    /// </summary>
    /// <param name="主键id">orp_id</param>
    /// <returns>OtherRP</returns>
    public OtherRP Get(int orp_id)
    {
        OtherRP m = new OtherRP();
        string sql = @"select orp.*,cus.cus_name,vd.vd_name,d.dep_name,
                    ele.ele_name,crc.crc_name,
                    cus.sdefine1 as cus_sdefine1,cus.sdefine2 as cus_sdefine2,cus.sdefine3 as cus_sdefine3,
                    cus.sdefine4 as cus_sdefine4,cus.sdefine5 as cus_sdefine5,
                    cus.ndefine1 as cus_ndefine1,cus.ndefine2 as cus_ndefine2,cus.ndefine3 as cus_ndefine3,
                    cus.ndefine4 as cus_ndefine4,cus.ndefine5 as cus_ndefine5
                    from ARAP_OtherRP orp
                    left join Base_Customer cus on orp.cus_id=cus.cus_id
                    left join Base_Vendor vd on orp.vd_id=vd.vd_id
                    left join Base_Department d on orp.dep_id=d.dep_id
                    left join Base_Employee ele on orp.ele_id=ele.ele_id
                    left join Base_Currency crc on orp.crc_id=crc.crc_id
                    where orp.orp_id=@orp_id";
        sql = sql.Replace("@orp_id", orp_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.orp_id = Convert.ToInt32(r["orp_id"]);
                m.rp_flag = r["rp_flag"].ToString();//收付标志:AR应收、AP应付
                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.dep_id = XTools.DBNullToInt(r["dep_id"]);
                if (r["dep_id"] != DBNull.Value)
                    m.department = new DepartmentDAL().Get(Convert.ToInt32(m.dep_id));
                m.ele_id = XTools.DBNullToInt(r["ele_id"]);
                if (r["ele_id"] != DBNull.Value)
                    m.employee = new EmployeeDAL().Get(Convert.ToInt32(m.ele_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.tax_rate = XTools.DBNullToDecimal(r["tax_rate"]);
                m.total_price = XTools.DBNullToDecimal(r["total_price"]);//无税金额
                m.tax_total_price = XTools.DBNullToDecimal(r["tax_total_price"]);//含税金额
                m.nat_total_price = XTools.DBNullToDecimal(r["nat_total_price"]);//本币金额
                m.nat_tax_total_price = XTools.DBNullToDecimal(r["nat_tax_total_price"]);//本币含税金额
                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"]);
                m.from_id = XTools.DBNullToInt(r["from_id"]);
                m.from_type = r["from_type"].ToString();
                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(OtherRP m)
    {
        string sql = @"update ARAP_OtherRP set 
                        rp_flag=@rp_flag,
                        order_number=@order_number,
                        cus_id=@cus_id,
                        vd_id=@vd_id,
                        dep_id=@dep_id,
                        ele_id=@ele_id,
                        crc_id=@crc_id,
                        exchange_rate=@exchange_rate,
                        tax_rate=@tax_rate,
                        total_price=@total_price,
                        tax_total_price=@tax_total_price,
                        nat_total_price=@nat_total_price,
                        nat_tax_total_price=@nat_tax_total_price,
                        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,
                        from_id=@from_id,
                        from_type=@from_type,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where orp_id=@orp_id";
        SqlParameter[] par = {
            new SqlParameter("@orp_id",m.orp_id),
            new SqlParameter("@rp_flag",m.rp_flag),
            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("@dep_id",XTools.IntToDBNull(m.dep_id)),
            new SqlParameter("@ele_id",XTools.IntToDBNull(m.ele_id)),
            new SqlParameter("@crc_id",XTools.IntToDBNull(m.crc_id)),
            new SqlParameter("@exchange_rate",XTools.DecimalToDBNull(m.exchange_rate)),
            new SqlParameter("@tax_rate",XTools.DecimalToDBNull(m.tax_rate)),
            new SqlParameter("@total_price",XTools.DecimalToDBNull(m.total_price)),
            new SqlParameter("@tax_total_price",XTools.DecimalToDBNull(m.tax_total_price)),
            new SqlParameter("@nat_total_price",XTools.DecimalToDBNull(m.nat_total_price)),
            new SqlParameter("@nat_tax_total_price",XTools.DecimalToDBNull(m.nat_tax_total_price)),
            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("@from_id",XTools.IntToDBNull(m.from_id)),
            new SqlParameter("@from_type",XTools.StringToDBNull(m.from_type)),
            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">ID</param>
    public string Delete(int orp_id)
    {
            //删除主表
            string sql = "delete ARAP_OtherRP where orp_id=@orp_id";
            SqlParameter[] par = {
                new SqlParameter("@orp_id",orp_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_OtherRP 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(orp_id),0) from ARAP_OtherRP";
        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="rp_flag">AR销售对账,AP采购对账</param>
    /// <returns></returns>
    public int GetPrevNextID(int current_id, string type, string rp_flag)
    {
        string sql = @"select isnull(max(orp_id),0) from ARAP_OtherRP 
                    where rp_flag=@rp_flag and (orp_id<@current_id or isnull(@current_id,0)=0)";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(orp_id),0) from ARAP_OtherRP where rp_flag=@rp_flag and orp_id>@current_id";
        }
        SqlParameter[] par ={
                new SqlParameter("@rp_flag", rp_flag),
                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 orp_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select orp_id,'单号：'+order_number as info,
	                    case when
                        isnull(nat_tax_total_price,0)=0
	                    then '未填金额'
	                    else 'success' end as msg
	                    from ARAP_OtherRP where orp_id=@orp_id
                    )t where msg!='success'
                    order by orp_id";
        SqlParameter[] par ={
                new SqlParameter("@orp_id", orp_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString() + " " + dt.Rows[0]["msg"].ToString();
        }
        return "success";
    }

    /// <summary>
    /// 应收应付单生成应收应付明细账
    /// </summary>
    /// <param name="orp_id">应收应付单主表ID</param>
    /// <returns></returns>
    public string OtherRPToAPDetail(int orp_id)
    {
        OtherRPDAL dal = new OtherRPDAL();
        APDetailDAL ap_dal = new APDetailDAL();
        OtherRP m = dal.Get(orp_id);
        string title;
        if (m.rp_flag.Equals("AR"))
        {
            title = "其它应收单";
        }
        else
        {
            title = "其它应付单";
        }
        APDetail ap = new APDetail();
        ap.orp_id = m.orp_id;//应收应付单主表ID
        ap.rp_flag = m.rp_flag;//收付标志:AR应收、AP应付
        ap.from_id = m.orp_id;//应收应付单主表ID
        ap.from_type = title;//单据来源
        ap.from_order_number = m.order_number;//应收应付单号
        ap.cus_id = m.cus_id;
        ap.vd_id = m.vd_id;
        ap.dep_id = m.dep_id;
        ap.ele_id = m.ele_id;
        ap.new_date = m.new_date;
        ap.new_uid = m.new_uid;
        ap.new_user = m.new_user;
        ap.verify_uid = m.verify_uid;
        ap.verify_user = m.verify_user;
        ap.tax_rate = m.tax_rate;
        ap.total_price = m.total_price;
        ap.tax_total_price = m.tax_total_price;
        ap.nat_total_price = m.nat_total_price;
        ap.nat_tax_total_price = m.nat_tax_total_price;
        ap.crc_id = m.crc_id;
        ap.exchange_rate = m.exchange_rate;
        ap.remark = m.remark;
        ap.d_sdefine1 = m.sdefine1;
        ap.d_sdefine2 = m.sdefine2;
        ap.d_sdefine3 = m.sdefine3;
        ap.d_sdefine4 = m.sdefine4;
        ap.d_sdefine5 = m.sdefine5;
        ap.d_ndefine1 = m.ndefine1;
        ap.d_ndefine2 = m.ndefine2;
        ap.d_ndefine3 = m.ndefine3;
        ap.d_ndefine4 = m.ndefine4;
        ap.d_ndefine5 = m.ndefine5;
        ap_dal.Add(ap);


        return "success";
    }

    /// <summary>
    /// 取消应收应付单生成的应收应付明细账
    /// </summary>
    /// <param name="orp_id">应收应付单主表ID</param>
    /// <returns></returns>
    public string DeleteAPDetail(int orp_id)
    {
        //检查是否已核销
        string result = CheckIsCanceled(orp_id);
        if (!result.Equals("success"))
            return result;
        APDetailDAL ap_dal = new APDetailDAL();
        string sql = "delete ARAP_APDetail where orp_id=@orp_id";
        SqlParameter[] par ={
                new SqlParameter("@orp_id", orp_id)
        };
        new SqlUtil().ExecuteNonQuery(sql, par);
        return "success";
    }


    /// <summary>
    /// 检查是否已核销
    /// </summary>
    /// <param name="orp_id">应收应付单D</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckIsCanceled(int orp_id)
    {
        string sql = @"SELECT top 1 info FROM
                    (
                        select '本单已核销，收付款单号:'+sfk.order_number as info
                        from ARAP_APCancel acl
                        left join ARAP_SFKuan sfk on acl.sfk_id=sfk.sfk_id
                        left join ARAP_APDetail apd on apd.apd_id=acl.apd_id
                        left join ARAP_OtherRP orp on orp.orp_id=apd.orp_id
                        where orp.orp_id=@orp_id
                    ) x";
        SqlParameter[] par ={
                new SqlParameter("@orp_id", orp_id)
            };
        DataTable dt = new SqlUtil().GetDataTable(sql, par);
        if (dt.Rows.Count > 0)
        {
            return dt.Rows[0]["info"].ToString();
        }
        return "success";
    }
}
