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 PurchaseOrderDAL
{
    /// <summary>
    /// 新增
    /// </summary>
    /// <param name="m">实体</param>
    public void Add(PurchaseOrder m)
    {
        string sql = @"insert Purchase_PurchaseOrder(order_number,vd_id,pt_id,
			                        dep_id,ele_id,crc_id,exchange_rate,remark,
                                    tax_rate,sw_id,ship_address,contact,phone,
                                    new_uid,new_user,new_date,
                                    update_uid,update_user,update_date,
                                    verify_uid,verify_user,
                                    sdefine1,sdefine2,sdefine3,sdefine4,sdefine5,
                                    ndefine1,ndefine2,ndefine3,ndefine4,ndefine5)
                        select  @order_number,@vd_id,@pt_id,
			                    @dep_id,@ele_id,@crc_id,@exchange_rate,@remark,
                                @tax_rate,@sw_id,@ship_address,@contact,@phone,
                                @new_uid,@new_user,@new_date,
                                @update_uid,@update_user,@update_date,
                                @verify_uid,@verify_user,
                                @sdefine1,@sdefine2,@sdefine3,@sdefine4,@sdefine5,
                                @ndefine1,@ndefine2,@ndefine3,@ndefine4,@ndefine5";
        SqlParameter[] par = {
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@pt_id",XTools.IntToDBNull(m.pt_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("@sw_id",XTools.IntToDBNull(m.sw_id)),
            new SqlParameter("@ship_address",XTools.StringToDBNull(m.ship_address)),
            new SqlParameter("@contact",XTools.StringToDBNull(m.contact)),
            new SqlParameter("@phone",XTools.StringToDBNull(m.phone)),
            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("@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">po_id</param>
    /// <returns>PurchaseOrder</returns>
    public PurchaseOrder Get(int po_id)
    {
        PurchaseOrder m = new PurchaseOrder();
        string sql = @"select po.*,convert(char(10),po.new_date,23) as new_date_format,
                    dep.dep_name,vd.vd_name,
                    ele.ele_name,pt.pt_name,sw.sw_name,crc.crc_name,
                    vd.sdefine1 as vd_sdefine1,vd.sdefine2 as vd_sdefine2,vd.sdefine3 as vd_sdefine3,
                    vd.sdefine4 as vd_sdefine4,vd.sdefine5 as vd_sdefine5,
                    vd.ndefine1 as vd_ndefine1,vd.ndefine2 as vd_ndefine2,vd.ndefine3 as vd_ndefine3,
                    vd.ndefine4 as vd_ndefine4,vd.ndefine5 as vd_ndefine5
                    from Purchase_PurchaseOrder po
                    left join Base_Vendor vd on po.vd_id=vd.vd_id
                    left join Base_Department dep on po.dep_id=dep.dep_id
                    left join Base_Employee ele on po.ele_id=ele.ele_id
                    left join Base_PurchaseType pt on po.pt_id=pt.pt_id
                    left join Base_Currency crc on po.crc_id=crc.crc_id
                    left join Base_ShipWay sw on po.sw_id=sw.sw_id
                    where po.po_id=@po_id";
        sql = sql.Replace("@po_id", po_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.po_id = Convert.ToInt32(r["po_id"]);
                m.order_number = r["order_number"].ToString();
                m.new_date = XTools.DBNullToTime(r["new_date"]);
                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.pt_id = XTools.DBNullToInt(r["pt_id"]);
                if (r["pt_id"] != DBNull.Value)
                    m.purchasetype = new PurchaseTypeDAL().Get(Convert.ToInt32(m.pt_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.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.sw_id = XTools.DBNullToInt(r["sw_id"]);
                if (r["sw_id"] != DBNull.Value)
                    m.ship_way = new ShipWayDAL().Get(Convert.ToInt32(m.sw_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.ship_address = r["ship_address"].ToString();
                m.contact = r["contact"].ToString();
                m.phone = r["phone"].ToString();
                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.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"]);
            }

            //r.Close();

        } //su.reader.Close(); su.CloseConnection();
        return m;
    }
    /// <summary>
    /// 更新
    /// </summary>
    /// <param name="m">实体</param>
    public void Update(PurchaseOrder m)
    {
        string sql = @"update Purchase_PurchaseOrder set 
                        order_number=@order_number,
                        vd_id=@vd_id,
                        pt_id=@pt_id,
                        dep_id=@dep_id,
                        ele_id=@ele_id,
                        crc_id=@crc_id,
                        exchange_rate=@exchange_rate,
                        tax_rate=@tax_rate,
                        sw_id=@sw_id,
                        ship_address=@ship_address,
                        contact=@contact,
                        phone=@phone,
                        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,
                        sdefine1=@sdefine1,
                        sdefine2=@sdefine2,
                        sdefine3=@sdefine3,
                        sdefine4=@sdefine4,
                        sdefine5=@sdefine5,
                        ndefine1=@ndefine1,
                        ndefine2=@ndefine2,
                        ndefine3=@ndefine3,
                        ndefine4=@ndefine4,
                        ndefine5=@ndefine5
                       where po_id=@po_id";
        SqlParameter[] par = {
            new SqlParameter("@po_id",m.po_id),
            new SqlParameter("@order_number",m.order_number),
            new SqlParameter("@vd_id",XTools.IntToDBNull(m.vd_id)),
            new SqlParameter("@pt_id",XTools.IntToDBNull(m.pt_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("@sw_id",XTools.IntToDBNull(m.sw_id)),
            new SqlParameter("@ship_address",XTools.StringToDBNull(m.ship_address)),
            new SqlParameter("@contact",XTools.StringToDBNull(m.contact)),
            new SqlParameter("@phone",XTools.StringToDBNull(m.phone)),
            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("@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 po_id)
    {
        //删除子表
        new PurchaseOrdersDAL().DeleteByOrder(po_id);
        //删除主表
        string sql = "delete Purchase_PurchaseOrder where po_id=@po_id";
        SqlParameter[] par = {
            new SqlParameter("@po_id",po_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 Purchase_PurchaseOrder 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(po_id),0) from Purchase_PurchaseOrder";
        SqlUtil su = new SqlUtil();
        int max_id = Convert.ToInt32(su.ExecuteScalar(sql, null));
        return max_id;
    }
    /// <summary>
    /// 得到上一个ID
    /// </summary>
    public int GetPrevNextID(int current_id, string type)
    {
        string sql = "select isnull(max(po_id),0) from Purchase_PurchaseOrder where po_id<@current_id or isnull(@current_id,0)=0";
        if (type.Equals("2"))
        {
            sql = "select isnull(min(po_id),0) from Purchase_PurchaseOrder where po_id>@current_id";
        }
        SqlParameter[] par ={
                new SqlParameter("@current_id", current_id)
            };
        return Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
    }
    /// <summary>
    /// 判断订单是否已入库
    /// </summary>
    /// <param name="so_id">订单主表ID</param>
    /// <returns>bool</returns>
    public bool CheckHasRuKu(int po_id)
    {
        string sql = @"
                    select count(1) from Purchase_PurchaseOrders
                    where po_id=@po_id and 
                    (
                        isnull(acc_in_quantity,0)<>0
                        or
                        pos_id in
                        (
	                        select distinct pos_id from Stock_InOuts where pos_id is not null
                        )
                    )";
        SqlParameter[] par ={
                new SqlParameter("@po_id", po_id)
            };
        int rows = Convert.ToInt32(new SqlUtil().ExecuteScalar(sql, par));
        if (rows > 0)
            return true;
        return false;
    }
    /// <summary>
    /// 检查必填项
    /// </summary>
    /// <param name="po_id">单据ID</param>
    /// <returns>返回success为通过，否则为提示信息</returns>
    public string CheckMustInput(int po_id)
    {
        string sql = @"SELECT top 1 info,msg FROM
                    (
	                    select d.pos_id,i.i_code+' ' +i.i_name as info,
	                    case when
                        i.on_free1=1 and isnull(d.free1,'')=''
	                    then '未填自由项1'
	                    when
	                    i.on_free2=1 and isnull(d.free2,'')=''
	                    then '未填自由项2'
                        when
	                    isnull(d.quantity,0)=0
	                    then '未填数量'
	                    else 'success' end as msg
	                    from Purchase_PurchaseOrders d
	                    left join Base_Inventory i on d.i_id=i.i_id
	                    where d.po_id=@po_id
                    )t where msg!='success'
                    order by pos_id";
        SqlParameter[] par ={
                new SqlParameter("@po_id", po_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>
    /// <returns>decimal</returns>
    public decimal GetTotalPrice(int po_id)
    {
        string sql = "select isnull(sum(tax_total_price),0) from Purchase_PurchaseOrders where po_id=@po_id ";
        SqlParameter[] par ={
                new SqlParameter("@po_id", po_id)
            };
        decimal sum_price = Convert.ToDecimal(new SqlUtil().ExecuteScalar(sql, par));
        return sum_price;
    }
}
