using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using VueWebApi.Models;
using VueWebApi.Tools;

namespace VueWebApi.DLL.DAL
{
    public class AppPurchManagementDAL
    {
        public static DataTable dt;    //定义全局变量dt
        public static bool res;       //定义全局变量dt
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象
        public static string strProcName = ""; //定义全局sql变量
        public static List<SqlParameter> listStr = new List<SqlParameter>(); //定义全局参数集合
        public static SqlParameter[] parameters; //定义全局SqlParameter参数数组

        #region[采购到货：获取采购订单列表]
        public static ToMessage MesAppPurchOrderSearch(string purchordercode)
        {
            string sql = "";
            string search = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                if (purchordercode != "" && purchordercode != null) //订单号不为空
                {
                    search += " and A.ordercode like '%'+@purchordercode+'%' ";
                    //dynamicParams.Add("@purchordercode", purchordercode);
                }
                //查询未关闭且未全部到货的采购订单
                sql = @"select distinct A.ordercode  from T_PurchaseOrder A
                        inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
                        where B.countarrivalquantity<quantity" + search;
                dynamicParams.Add("@purchordercode", purchordercode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.Message = "查询成功!";
                    mes.data = data;
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "无订单数据!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[采购到货：选择采购订单获取物料信息]
        public static ToMessage MesAppPurchOrderPartSearch(string purchordercode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //根据采购订单号查询物料信息，供应商信息
                sql = @"select B.rownumber, B.materiel_code as partcode,M.partname,M.partspec,M.is_batchno,M.is_incheck,
                        B.quantity,B.customercode,C.name as customername,A.departmentcode,T.org_name as departmentname,U.code as uomcode,U.name as uomname,
                        A.ordercode,B.countarrivalquantity,A.acceptdate  
                        from T_PurchaseOrder A
                        inner join T_PurchaseOrder_sub B on A.ordercode=B.purchaseorder_code
                        left  join TMateriel_Info M on B.materiel_code=M.partcode
                        left  join TUom U on M.uom_code=U.code
                        left  join TCustomer C on B.customercode=C.code
                        left  join TOrganization T on A.departmentcode=T.org_code
                        where A.ordercode=@purchordercode";
                dynamicParams.Add("@purchordercode", purchordercode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.Message = "查询成功!";
                    mes.data = data;
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "暂无物料数据!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[采购到货：生成系统条码]
        public static ToMessage MesAppPurchOrderLabelBarCode(string partcode, string arrivalqty, string onelableqty)
        {
            mes = AppLableBarCode.EncodingSeach("2060", partcode, arrivalqty, onelableqty);
            return mes;
        }
        #endregion

        #region[采购到货：提交]
        public static ToMessage MesAppPurchOrderSave(string purordercode, string orderstaus, string username, List<AppPurchOrderSave> json)
        {
            var sql = "";
            string djwo = "", numvalue = "";
            List<object> list = new List<object>();
            var dynamicParams = new DynamicParameters();
            try
            {
                list.Clear();
                mes = SeachEncode.EncodingSeach("2060");  //APP端采购到货生成单号
                if (mes.code == "300")
                {
                    return mes;
                }
                else
                {
                    List<string> wo = (List<string>)mes.data;
                    djwo = wo[0].ToString(); //获取单号
                    numvalue = wo[1].ToString(); //获取流水号
                }
                if (json == null || json.Count <= 0)
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "物料信息不能为空!";
                    mes.data = null;
                    return mes;
                }
                //Lambda表达式过滤获取预计到货日期、部门、供应商取第一个
                string hacceptdate = json.Select(p => p.hacceptdate).Distinct().ToList().First().ToString(); //预计到货日期
                string dpartment = json.Select(p => p.hdepartmentcode).Distinct().ToList().First().ToString(); //部门
                string customercode = json.Select(p => p.hcustomercode).Distinct().ToList().First().ToString(); //供应商
                //写入采购到货单主表
                sql = @"insert into T_PurchaseArrivalOrder(hbillno,hdate,hacceptdate,hbillstaus,hdepartmentcode,hcustomercode,hsourcebillno,lm_user,lm_date) 
                        values(@hbillno,@hdate,@hacceptdate,@hbillstaus,@hdepartmentcode,@hcustomercode,@hsourcebillno,@lm_user,@lm_date)";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        hbillno = djwo,
                        hdate = DateTime.Now.ToString(),
                        hacceptdate =hacceptdate==""?null:hacceptdate,
                        hbillstaus = orderstaus,
                        hdepartmentcode = dpartment,
                        hcustomercode = customercode,
                        hsourcebillno = purordercode,
                        lm_user = username,
                        lm_date = DateTime.Now.ToString()
                    }
                });
                //循环获取物信息
                for (int i = 0; i < json.Count; i++)
                {
                    //写入采购到货单子表
                    sql = @"insert into T_PurchaseArrivalOrder_sub(hbillno,rownumber,hpartcode,hplanqty,hqty,hacceptdate,hcheckstaus,hbatchno) 
                        values(@hbillno,@rownumber,@hpartcode,@hplanqty,@hqty,@hacceptdate,@hcheckstaus,@hbatchno)";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            hbillno = djwo,
                            rownumber=json[i].rownumber,
                            hpartcode = json[i].hpartcode,
                            hplanqty = decimal.Parse(json[i].hplanqty),
                            hqty = decimal.Parse(json[i].hqty),
                            hacceptdate = hacceptdate == "" ? null : hacceptdate,
                            hcheckstaus = json[i].hischeck,
                            hbatchno = json[i].hbatchno
                        }
                    });

                    //循环获取物料生成标签信息
                    for (int j = 0; j < json[i].children.Count; j++)
                    {
                        //写入条码档案表
                        sql = @"insert into T_BarCodeBill(rightcode,hbillno,hbilltype,hbarcode,hbarcodestatus,hbarcodetype,hmaterialcode,hqty,hbatchno,hcustomercode,hdeptcode,hsourcebillno,hsourcebilltype,barcodeposition,lm_user,lm_date) 
                        values(@rightcode,@hbillno,@hbilltype,@hbarcode,@hbarcodestatus,@hbarcodetype,@hmaterialcode,@hqty,@hbatchno,@hcustomercode,@hdeptcode,@hsourcebillno,@hsourcebilltype,@barcodeposition,@lm_user,@lm_date)";
                        list.Add(new
                        {
                            str = sql,
                            parm = new
                            {
                                rightcode = "2060",
                                hbillno = djwo,
                                hbilltype = "1103", //采购到货单类型
                                hbarcode = json[i].children[j].labcode,
                                hbarcodestatus = "Y",
                                hbarcodetype = "P",
                                hmaterialcode = json[i].children[j].partnumber,
                                hqty = decimal.Parse(json[i].children[j].labqty),
                                hbatchno = json[i].hbatchno,
                                hcustomercode = customercode,
                                hdeptcode = dpartment,
                                hsourcebillno = purordercode,
                                hsourcebilltype = "1102",
                                barcodeposition="2060;采购到货",
                                lm_user = username,
                                lm_date = DateTime.Now.ToString()
                            }
                        });
                    }
                    //更新采购订单子表：累计到货数量
                    sql = @"update T_PurchaseOrder_sub set countarrivalquantity=countarrivalquantity+@hqty where purchaseorder_code=@purordercode and materiel_code=@materiel_code and rownumber=@rownumber";
                    list.Add(new
                    {
                        str = sql,
                        parm = new
                        {
                            purordercode = purordercode,
                            materiel_code = json[i].hpartcode,
                            rownumber=json[i].rownumber,
                            hqty=decimal.Parse(json[i].hqty)
                        }
                    });
                }
                //更新采购订单主表：订单状态
                sql = @"update T_PurchaseOrder set status=@status where ordercode=@ordercode";
                list.Add(new
                {
                    str = sql,
                    parm = new
                    {
                        status = orderstaus,
                        ordercode = purordercode
                    }
                });
                bool aa = DapperHelper.DoTransaction(list);
                if (aa)
                {
                    mes.code = "200";
                    mes.count = 0;
                    mes.Message = "采购到货成功!";
                    mes.data = djwo;
                }
                else
                {
                    mes.code = "300";
                    mes.count = 0;
                    mes.Message = "采购到货失败!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion


        #region[采购入库选择有到货采购订单]
        public static ToMessage MesAppPurchInStorSelectCGOrder()
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //有到货或者部分入库的采购订单
                sql = @"select distinct A.ordercode   from T_PurchaseOrder A
                        inner join T_PurchaseArrivalOrder B on A.ordercode=B.hsourcebillno
                        where B.hbillstaus in('BINARIVE','INARIVE','BINSTOG')";
                var data = DapperHelper.selecttable(sql);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.Message = "success!";
                    mes.data = data;
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "无可执行订单!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[选择采购订单带出订单下有到货的物料信息]
        public static ToMessage MesAppPurchInStorSelectCGOrderPart(string ordercode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //选择采购订单带出订单下有到货的物料信息
                sql = @"select A.ordercode,M.partcode,M.partname,M.partspec,avg(S.hplanqty) as hplanqty,sum(S.hqty) as hqty 
                        from T_PurchaseOrder A
                        inner join T_PurchaseArrivalOrder B on A.ordercode=B.hsourcebillno
                        inner join T_PurchaseArrivalOrder_sub S on B.hbillno=S.hbillno
                        left  join TMateriel_Info M on S.hpartcode=M.partcode
                        where B.hbillstaus in('BINARIVE','INARIVE','BINSTOG') and A.ordercode=@ordercode
                        group by A.ordercode,M.partcode,M.partname,M.partspec";
                dynamicParams.Add("@ordercode", ordercode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.Message = "success!";
                    mes.data = data;
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "当前订单无可执行物料!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

        #region[采购入库扫描标签条码带出到货单信息]
        public static ToMessage MesAppPurchInStorScanLabCode(string labcode,string ordercode)
        {
            string sql = "";
            var dynamicParams = new DynamicParameters();
            try
            {
                //判断条码状态
                mes = LabCodeStatusMessage.LabCodeStatus("2060", "1103",labcode);//功能编号、单据类型编码、标签编码
                if (mes.code == "300") 
                {
                    return mes;
                }
                //判断扫描标签是否为当前选择采购订单下的到货单所生成标签
                sql = @"select *   from T_BarCodeBill where hsourcebillno=@ordercode";
                dynamicParams.Add("@ordercode", ordercode);
                var data0 = DapperHelper.selectdata(sql, dynamicParams);
                if (data0.Rows.Count < 0) 
                {
                    mes.code = "300";
                    mes.Message = "当前条码不是采购订单【"+ ordercode + "】生成条码!";
                    mes.data = null;
                    return mes;
                }
                //根据标签条码查询到货生成的标签信息
                sql = @"select A.hbarcode,A.hbillno,A.hqty,A.hcustomercode,C.name as hcustomername,A.hbatchno,A.qualitystatus   
                        from T_BarCodeBill A
                        left join TCustomer C on A.hcustomercode=C.code
                        where A.hbarcode=@labcode";
                dynamicParams.Add("@labcode", labcode);
                var data = DapperHelper.selectdata(sql, dynamicParams);
                if (data.Rows.Count > 0)
                {
                    mes.code = "200";
                    mes.Message = "success!";
                    mes.data = data;
                }
                else
                {
                    mes.code = "300";
                    mes.Message = "标签信息不存在!";
                    mes.data = null;
                }
            }
            catch (Exception e)
            {
                mes.code = "300";
                mes.count = 0;
                mes.Message = e.Message;
                mes.data = null;
            }
            return mes;
        }
        #endregion

    }
}