using CsbrHcgClient.Models;
using CsbrHcgClient.Models.SAP.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CsbrHcgClient
{
    public class WvControl
    {
        public DataTable GetPrintCount()
        {
            string sqlstr = @"SELECT
                               count(1) allcount,
                               IFNULL(SUM(if(tb.printstate='N',1,0)),0) printcount,
                               IFNULL(sum(if(tb.printstate='Y',1,0)),0) printedcount
                            FROM
	                        spd_wvdetail_scend tb
                        WHERE
                            TB.printstate<>@printstate
                           ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "printstate";
            parameters[0].Value = "S";
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public DataTable GetPrintInfo(string deptcd)
        {
           string sqlstr = @"SELECT
                              'false'  选择,
                              (@rowNum:=@rowNum+1) rownum,
                              TA.GoodsCode 商品编码,
                              TB.GoodsName 商品名称,
                              TB.GoodsSpec 规格,
                              TB.MODEL  型号,
                              TB.Producer 生产厂家,
                              TA.Lot  批号,
                              TA.ExpireDate 有效期,
                              Ta.BARCODE 高值码,
                              TA.SUPPLIERNAME 供应商,
                              if(TA.PrintState='N','未打印','已打印')  打印状态,
                              TA.BillNo 单据号,
                              TA.RowNo 行号

                            FROM
                           ( SELECT
                                a.GoodsCode,
                                a.lot,
                                a.expiredate,
                                a.suppliername,
                                a.billno,
                                a.rowno,
                                b.barcode,
                                b.printstate
                            FROM
                            spd_wvdetail a,
                            spd_wvdetail_scend b
                           where a.billno=b.billno and a.rowno=b.rowno
                              and b.PrintState=@printstate
                              and a.orgguid=if(@deptcd='all',a.orgguid,@deptcd)
                         ) ta left outer join his_goods tb
                               on ta.goodscode=tb.guid
                               inner join (select (@rowNum :=0) ) TC
                            ORDER BY ta.BARCODE ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "printstate";
            parameters[0].Value = "N";

            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "deptcd";
            parameters[1].Value = deptcd;
            DataSet ds= MySQLHelper.GetDataSet(sqlstr,CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetPrintedInfo(string deptcd)
        {
            string sqlstr = @"SELECT
                               
                              'false'  选择1,
                            (@rowNum:=@rowNum+1) rownum1,
                              TA.GoodsCode 商品编码1,
                              Tb.GoodsName 商品名称1,
                              Tb.GoodsSpec 规格1,
                              Tb.MODEL  型号1,
                              Tb.Producer 生产厂家1,
                              TA.Lot  批号1,
                              TA.ExpireDate 有效期1,
                              Ta.BARCODE 高值码1,
                              TA.SUPPLIERNAME 供应商1,
                              if(Ta.PrintState='N','未打印','已打印')  打印状态1,
                              TA.BillNo 单据号1,
                              TA.RowNo 行号1
                              

                            FROM
                            (SELECT
                                a.GoodsCode,
                                a.lot,
                                a.expiredate,
                                a.suppliername,
                                a.billno,
                                a.rowno,
                                b.barcode,
                                b.printstate
                            FROM
                            spd_wvdetail a,
                            spd_wvdetail_scend b
                           where a.billno=b.billno and a.rowno=b.rowno
                              and b.PrintState=@printstate
                              and a.orgguid=if(@deptcd='all',a.orgguid,@deptcd)
                            ) ta left outer join his_goods tb
                            on ta.goodscode=tb.guid
                            inner join (Select (@rowNum :=0) ) Tc
                            
                        order by
                            Ta.BARCODE ";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "printstate";
            parameters[0].Value = "Y";
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "deptcd";
            parameters[1].Value = deptcd;
            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public DataTable GetDept()
        {
            string sqlstr = @"SELECT
	                        @all guid,
	                        '全部' orgname
                        UNION ALL
			                    SELECT
				                    b.orgguid,
                                    b.orgname
			                    FROM
				                    spd_wvdetail_scend a,
				                    spd_wvdetail b
			                    WHERE
				                    a.billno = b.billno
			                    AND A.printstate = 'N'
                                group by b.orgguid,b.orgname
		                    ";
            MySql.Data.MySqlClient.MySqlParameter [] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "all";
            parameters[0].Value = "all";

        DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
        return ds.Tables[0];
        }

        public DataTable GetDepted()
        {
            //string sqlstr = @"SELECT
	           //                 @all guid,
	           //                 '全部' orgname
            //                UNION ALL
	           //                 SELECT
		          //                  guid,
		          //                  orgname
	           //                 FROM
		          //                  sys_org ta
	           //                 WHERE
		          //                  EXISTS (
			         //                   SELECT
				        //                    'x'
			         //                   FROM
				        //                    spd_wvdetail_scend a,
				        //                    spd_wvdetail b
			         //                   WHERE
				        //                    a.billno = b.billno
			         //                   AND A.printstate = 'Y'
			         //                   AND B.ORGGUID = TA.GUID
		          //                  )";

            string sqlstr = @"SELECT
	                            @all guid,
	                            '全部' orgname
                            UNION ALL
	                            SELECT
		                            guid,
		                            orgname
	                            FROM
		                            sys_org ta";
            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "all";
            parameters[0].Value = "all";

            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }
        public int Update(string billno, string rowno, string rfiduid)
        {
            string updatestr = @" update spd_wvdetail_scend 
                                    set printstate=@printstate,
                                        Rfiduid=@rfiduid,
                                        PrintTime=@printtime
                                where billno=@billno and rowno=@rowno";
            MySql.Data.MySqlClient.MySqlParameter[] parames = new MySql.Data.MySqlClient.MySqlParameter[5];
            parames[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[0].ParameterName = "printstate";
            parames[0].Value = "Y";
            parames[1] = new MySql.Data.MySqlClient.MySqlParameter();
            
            parames[1].ParameterName = "rfiduid";

            parames[1].Value = rfiduid;
            parames[2] = new MySql.Data.MySqlClient.MySqlParameter();

            parames[2].ParameterName = "billno";
            parames[2].Value = billno;
            parames[3] = new MySql.Data.MySqlClient.MySqlParameter();

            parames[3].ParameterName = "rowno";
            parames[3].Value = rowno;

            parames[4] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[4].ParameterName = "printtime";
            parames[4].Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
            int i = MySQLHelper.ExecuteNonQuery(updatestr,CommandType.Text,parames);
            return i;
        }

        public int UpdateSts(string billno, string rowno)
        {
            string updatestr = @" update spd_wvdetail_scend 
                                    set printstate=@printstate
                                where billno=@billno and rowno=@rowno";
            MySql.Data.MySqlClient.MySqlParameter[] parames = new MySql.Data.MySqlClient.MySqlParameter[3];
            parames[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[0].ParameterName = "printstate";
            parames[0].Value = "S";
         
            parames[1] = new MySql.Data.MySqlClient.MySqlParameter();

            parames[1].ParameterName = "billno";
            parames[1].Value = billno;
            parames[2] = new MySql.Data.MySqlClient.MySqlParameter();

            parames[2].ParameterName = "rowno";
            parames[2].Value = rowno;

           int i = MySQLHelper.ExecuteNonQuery(updatestr, CommandType.Text, parames);
            return i;
        }

        public DataTable GetGoods(string goodsCode)
        {
            string sqlstr = @"SELECT `GUID`,`GoodsCode`,`GoodsName`,`IsRK`,`IsZR` FROM his_goods WHERE guid =@goodsCode";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[1];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "goodsCode";
            parameters[0].Value = goodsCode;

            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        public DataTable GetGoodsList(string goodsCode,string goodsName,bool strIsRK,string strDepotGuid)
        {
            string sqlstr = @"SELECT 
                              (@rowNum := @rowNum + 1) rownum1,
                              `GUID`,
                              `GoodsCode`,
                              `GoodsName`,
                              `IsRK`,
                               GoodsCate,
                               UnitStyle,
                               Model,
                              `IsZR` 
                            FROM
                              his_goods 
                            INNER JOIN (SELECT (@rowNum :=0) ) Tc WHERE DepotGuid ='" + strDepotGuid + "' ";

            if (!string.IsNullOrEmpty(goodsCode))
            {
                sqlstr += " AND GoodsCode LIKE '%" + goodsCode + "%'";
            }

            if (!string.IsNullOrEmpty(goodsName))
            {
                sqlstr += " AND GoodsName LIKE '%" + goodsName + "%'";
            }

            if (strIsRK)
            {
                sqlstr += " AND IsRK LIKE '%" + strIsRK + "%'";
            }

            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text);
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取入库单明细信息
        /// </summary>
        /// <param name="goodsCode">商品编号</param>
        /// <param name="billNo">订单编号</param>
        /// <param name="sapRowNo">SAP行号</param>
        /// <returns></returns>
        public DataTable GetWvDetail(string goodsCode, string billNo, string sapRowNo)
        {
            string sqlstr = @"SELECT 
                              `GUID`,
                              `BillNo`,
                              `RowNo`,
                              `GoodsGuid`,
                              `GoodsCode`,
                              `Lot`,
                              `ExpireDate` 
                            FROM
                              `spd_wvdetail` 
                            WHERE `BillNo` = @BillNo 
                              AND `GoodsCode` = @GoodsCode 
                              AND Memo =@Memo
                            UNION
                            SELECT 
                              `GUID`,
                              `BillNo`,
                              `RowNo`,
                              `GoodsGuid`,
                              `GoodsCode`,
                              `Lot`,
                              `ExpireDate` 
                            FROM
                              `spd_wvdetail_log` 
                            WHERE `BillNo` = @BillNo 
                              AND `GoodsCode` = @GoodsCode 
                              AND Memo =@Memo;";

            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[3];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[0].ParameterName = "BillNo";
            parameters[0].Value = billNo;

            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[1].ParameterName = "GoodsCode";
            parameters[1].Value = goodsCode;

            parameters[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameters[2].ParameterName = "Memo";
            parameters[2].Value = sapRowNo;

            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text, parameters);
            return ds.Tables[0];
        }

        /// <summary>
        /// 获取已打印RFID打印数量
        /// </summary>
        /// <param name="wvDetailGuid">明细Guid</param>
        /// <returns></returns>
        public DataTable GetRfidPintCount(string wvDetailGuid)
        {
            string sqlstr = "SELECT COUNT(GUID) AS PrintCount FROM spd_wvdetail_scend WHERE RDGUID ='" + wvDetailGuid + "'";

            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text);
            return ds.Tables[0];
        }

        /// <summary>
        /// 添加商品资料
        /// </summary>
        /// <param name="hisGood">商品信息</param>
        /// <returns></returns>
        public int AddGoods(HisGoods hisGood)
        {
            string updatestr = @"INSERT INTO `his_goods` (
                                                  `GUID`,
                                                  `GoodsCode`,
                                                  `GoodsName`,
                                                  `BizState`,
                                                  `UnitStyle`,
                                                  `GoodsCate`,
                                                  `Memo`,
                                                  `ProductionAddress`,
                                                  `Model`,
                                                  `IsRK`,
                                                  `IsZR`,
                                                  `DepotGuid`,
                                                  `DepotName`,
                                                   Producer,
                                                   GoodsSpec
                                                ) 
                                                VALUES
                                                  (
                                                    @Guid,
                                                    @GoodsCode,
                                                    @GoodsName,
                                                    @BizState,
                                                    @UnitStyle,
                                                    @GoodsCate,
                                                    @Memo,
                                                    @Productionaddress,
                                                    @Model,
                                                    @Isrk,
                                                    @Iszr,
                                                    @Depotguid,
                                                    @Depotname,
                                                    @Producer,
                                                    @GoodsSpec);";
            MySql.Data.MySqlClient.MySqlParameter[] parames = new MySql.Data.MySqlClient.MySqlParameter[15];
            parames[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[0].ParameterName = "Guid";
            parames[0].Value = hisGood.Guid;

            parames[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[1].ParameterName = "GoodsCode";
            parames[1].Value = hisGood.GoodsCode;

            parames[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[2].ParameterName = "GoodsName";
            parames[2].Value = hisGood.GoodsName;

            parames[3] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[3].ParameterName = "BizState";
            parames[3].Value = hisGood.BizState;

            parames[4] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[4].ParameterName = "UnitStyle";
            parames[4].Value = hisGood.UnitStyle;

            parames[5] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[5].ParameterName = "GoodsCate";
            parames[5].Value = hisGood.GoodsCate;

            parames[6] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[6].ParameterName = "Memo";
            parames[6].Value = hisGood.Memo;

            parames[7] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[7].ParameterName = "Productionaddress";
            parames[7].Value = hisGood.Productionaddress;

            parames[8] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[8].ParameterName = "Model";
            parames[8].Value = hisGood.Model;

            parames[9] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[9].ParameterName = "Isrk";
            parames[9].Value = hisGood.Isrk;

            parames[10] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[10].ParameterName = "Iszr";
            parames[10].Value = hisGood.Iszr;

            parames[11] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[11].ParameterName = "Depotguid";
            parames[11].Value = hisGood.Depotguid;

            parames[12] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[12].ParameterName = "Depotname";
            parames[12].Value = hisGood.Depotname;

            parames[13] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[13].ParameterName = "Producer";
            parames[13].Value = hisGood.Producer;

            parames[14] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[14].ParameterName = "GoodsSpec";
            parames[14].Value = hisGood.GoodsSpec;

            int i = MySQLHelper.ExecuteNonQuery(updatestr, CommandType.Text, parames);
            return i;
        }

        /// <summary>
        /// 修改商品资料
        /// </summary>
        /// <param name="strGuid">商品GUID</param>
        /// <param name="strIsRK">是否入库</param>
        /// <param name="strIsZR">是否植入</param>
        /// <returns></returns>
        public int UpdateGoods(string strGuid,string strIsRK, string strIsZR)
        {
            string updatestr = @"UPDATE his_goods SET `IsRK` ='" + strIsRK + "' , `IsZR` ='" + strIsZR + "' WHERE `GUID` ='" + strGuid + "'";

            int i = MySQLHelper.ExecuteNonQuery(updatestr, CommandType.Text);
            return i;
        }

        /// <summary>
        /// 填写入库单信息
        /// </summary>
        /// <param name="item">明细数据</param>
        /// <param name="strMedDept">工厂（科室）</param>
        /// <param name="MedStore">库房</param>
        /// <returns></returns>
        public int AddSpdWv(DataRow drItem, string strMedDept, string MedStore)
        {
            string strWvDetailGuid = String.Empty;
            int i = 0;

            #region 添加单头表

            string sqlGetWv = @"SELECT guid FROM spd_wv WHERE BillNo ='" + drItem["EBELN"].ToString() + "'";
            DataSet dsGetWv = MySQLHelper.GetDataSet(sqlGetWv, CommandType.Text);

            if (dsGetWv == null || dsGetWv.Tables[0].Rows.Count <= 0)
            {
                #region SQL
                string strInsertWv = @"INSERT INTO `spd_wv` (
                                              `GUID`,
                                              `BillNo`,
                                              `MedName`,
                                              `MedDeptGuid`,
                                              `MedDeptName`,
                                              `MedStoreGuid`,
                                              `MedStoreName`,
                                              `SupplierGUID`,
                                              `SupplierName`,
                                              `InputDate`
                                            ) 
                                            VALUES
                                              (
                                                @GUID,
                                                @BillNo,
                                                '亚心总院',
                                                @MedDeptGuid,
                                                @MedDeptName,
                                                @MedStoreGuid,
                                                @MedStoreName,
                                                @SupplierGUID,
                                                @SupplierName,
                                                @InputDate
                                              ) ;";
                #endregion

                #region 参数设置
                MySql.Data.MySqlClient.MySqlParameter[] parames = new MySql.Data.MySqlClient.MySqlParameter[9];
                parames[0] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[0].ParameterName = "GUID";
                parames[0].Value = Guid.NewGuid().ToString("N");

                parames[1] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[1].ParameterName = "BillNo";
                parames[1].Value = drItem["EBELN"].ToString(); 

                parames[2] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[2].ParameterName = "MedDeptGuid";
                parames[2].Value = strMedDept;

                parames[3] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[3].ParameterName = "MedDeptName";
                parames[3].Value = strMedDept;

                parames[4] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[4].ParameterName = "MedStoreGuid";
                parames[4].Value = MedStore;

                parames[5] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[5].ParameterName = "MedStoreName";
                parames[5].Value = MedStore;

                parames[6] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[6].ParameterName = "SupplierGUID";
                parames[6].Value = drItem["LIFNR"].ToString();

                parames[7] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[7].ParameterName = "SupplierName";
                parames[7].Value = drItem["LIFNR"].ToString();

                parames[8] = new MySql.Data.MySqlClient.MySqlParameter();
                parames[8].ParameterName = "InputDate";
                parames[8].Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
                #endregion

                i = MySQLHelper.ExecuteNonQuery(strInsertWv, CommandType.Text, parames);

                if (i <=0)
                {
                    throw new Exception("采购订单" + drItem["EBELN"].ToString() + "回写订单主表数据库时异常");
                }
            }

            #endregion

            #region 添加明细表
            string sqlGetWvDetail = @"SELECT guid FROM spd_wvdetail WHERE BillNo ='" + drItem["EBELN"].ToString() + "' AND GoodsCode = '" + drItem["MATNR"].ToString() + "' AND Lot ='" + drItem["Lot"].ToString() + "' AND Memo ='" + drItem["EBELP"].ToString() + "'";

            DataSet dsGetWvDetail = MySQLHelper.GetDataSet(sqlGetWvDetail, CommandType.Text);

            if (dsGetWvDetail != null && dsGetWvDetail.Tables.Count > 0 && dsGetWvDetail.Tables[0].Rows.Count > 0)
            {
                strWvDetailGuid = dsGetWvDetail.Tables[0].Rows[0][0].ToString();
            }
            else
            {
                strWvDetailGuid = Guid.NewGuid().ToString("N");

                int intRow = 0;
                string sqlGetWvDetailCount = @"SELECT COUNT(GUID) AS BillCount FROM spd_wvdetail WHERE BillNo ='" + drItem["EBELN"].ToString() + "'";
                DataSet dsGetWvDetailCount = MySQLHelper.GetDataSet(sqlGetWvDetailCount, CommandType.Text);
                if (dsGetWvDetailCount != null && dsGetWvDetailCount.Tables.Count > 0 && dsGetWvDetailCount.Tables[0].Rows.Count > 0)
                {
                    intRow = int.Parse(dsGetWvDetailCount.Tables[0].Rows[0][0].ToString()) + 1;
                }

                #region SQL
                    string strInsertWvDetail = @"INSERT INTO `spd_wvdetail` (
                                                          `GUID`,
                                                          `BillNo`,
                                                          `GoodsGuid`,
                                                          `GoodsCode`,
                                                          `GoodsName`,
                                                          `Unit`,
                                                          `SupplierGUID`,
                                                          `SupplierName`,
                                                          `SettleSupplier`,
                                                          `MVQty`,
                                                          `Lot`,
                                                          `ExpireDate`,
                                                          `MvDate`,
                                                          `OrgGUID`,
                                                          `OrgName`,
                                                          `RowNo`,
                                                           Memo,
                                                           model
                                                        ) 
                                                        VALUES
                                                          (
                                                            @GUID,
                                                            @BillNo,
                                                            @GoodsGuid,
                                                            @GoodsCode,
                                                            @GoodsName,
                                                            @Unit,
                                                            @SupplierGUID,
                                                            @SupplierName,
                                                            @SettleSupplier,
                                                            @MVQty,
                                                            @Lot,
                                                            @ExpireDate,
                                                            @MvDate,
                                                            @OrgGUID,
                                                            @OrgName,
                                                            @RowNo,
                                                            @Memo,
                                                            @model
                                                          ) ;";

                #endregion

                #region 参数设置
                MySql.Data.MySqlClient.MySqlParameter[] parameWvDetail = new MySql.Data.MySqlClient.MySqlParameter[18];
                parameWvDetail[0] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[0].ParameterName = "GUID";
                parameWvDetail[0].Value = strWvDetailGuid;

                parameWvDetail[1] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[1].ParameterName = "BillNo";
                parameWvDetail[1].Value = drItem["EBELN"].ToString();

                parameWvDetail[2] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[2].ParameterName = "GoodsGuid";
                parameWvDetail[2].Value = drItem["MATNR"].ToString();

                parameWvDetail[3] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[3].ParameterName = "GoodsCode";
                parameWvDetail[3].Value = drItem["MATNR"].ToString();

                parameWvDetail[4] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[4].ParameterName = "GoodsName";
                parameWvDetail[4].Value = drItem["MAKTX"].ToString();

                parameWvDetail[5] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[5].ParameterName = "Unit";
                parameWvDetail[5].Value = drItem["MSEH1"].ToString();

                parameWvDetail[6] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[6].ParameterName = "SupplierGUID";
                parameWvDetail[6].Value = drItem["LIFNR"].ToString();
                
                parameWvDetail[7] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[7].ParameterName = "SupplierName";
                parameWvDetail[7].Value = drItem["LIFNR"].ToString();

                parameWvDetail[8] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[8].ParameterName = "SettleSupplier";
                parameWvDetail[8].Value = drItem["LIFN2"].ToString();
                
                parameWvDetail[9] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[9].ParameterName = "MVQty";
                parameWvDetail[9].Value = drItem["MENG1"].ToString();

                parameWvDetail[10] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[10].ParameterName = "Lot";
                parameWvDetail[10].Value = drItem["Lot"].ToString();

                parameWvDetail[11] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[11].ParameterName = "ExpireDate";
                parameWvDetail[11].Value = drItem["ExpireDate"].ToString();

                parameWvDetail[12] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[12].ParameterName = "MvDate";
                parameWvDetail[12].Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");

                parameWvDetail[13] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[13].ParameterName = "OrgGUID";
                parameWvDetail[13].Value = strMedDept;

                parameWvDetail[14] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[14].ParameterName = "OrgName";
                parameWvDetail[14].Value = strMedDept;

                parameWvDetail[15] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[15].ParameterName = "RowNo";
                parameWvDetail[15].Value = intRow;

                parameWvDetail[16] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[16].ParameterName = "Memo";
                parameWvDetail[16].Value = drItem["EBELP"].ToString();

                parameWvDetail[17] = new MySql.Data.MySqlClient.MySqlParameter();
                parameWvDetail[17].ParameterName = "model";
                parameWvDetail[17].Value = drItem["MEIN1"].ToString();

                #endregion

                i = MySQLHelper.ExecuteNonQuery(strInsertWvDetail, CommandType.Text, parameWvDetail);

                if (i <= 0)
                {
                    throw new Exception("采购订单" + drItem["EBELN"].ToString() + "回写订单明细表数据库时异常");
                }
            }

            #endregion

            #region 添加scend明细表

            #region SQL
            string strInsertWvScend = @"INSERT INTO `spd_wvdetail_scend` (
                                                          `GUID`,
                                                          `RDGUID`,
                                                          `BillNo`,
                                                          `BarCode`,
                                                          `RfidUid`,
                                                          `PrintState`,
                                                          `Sts`,
                                                          `PrintTime`,
                                                          `BillState`,
                                                          `IsDelete`
                                                        ) 
                                                        VALUES
                                                          (
                                                            @GUID,
                                                            @RDGUID,
                                                            @BillNo,
                                                            @BarCode,
                                                            @RfidUid,
                                                            'Y',
                                                            'N',
                                                            @PrintTime,
                                                            'N',
                                                            'N'
                                                          ) ;";
            #endregion

            #region 参数设置
            MySql.Data.MySqlClient.MySqlParameter[] parameWvScend = new MySql.Data.MySqlClient.MySqlParameter[6];
            parameWvScend[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parameWvScend[0].ParameterName = "GUID";
            parameWvScend[0].Value = Guid.NewGuid().ToString("N");

            parameWvScend[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parameWvScend[1].ParameterName = "RDGUID";
            parameWvScend[1].Value = strWvDetailGuid;

            parameWvScend[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parameWvScend[2].ParameterName = "BillNo";
            parameWvScend[2].Value = drItem["EBELN"].ToString();

            parameWvScend[3] = new MySql.Data.MySqlClient.MySqlParameter();
            parameWvScend[3].ParameterName = "BarCode";
            parameWvScend[3].Value = drItem["BarCode"].ToString();

            parameWvScend[4] = new MySql.Data.MySqlClient.MySqlParameter();
            parameWvScend[4].ParameterName = "RfidUid";
            parameWvScend[4].Value = drItem["RfidCode"].ToString();

            parameWvScend[5] = new MySql.Data.MySqlClient.MySqlParameter();
            parameWvScend[5].ParameterName = "PrintTime";
            parameWvScend[5].Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
            #endregion 

            i = MySQLHelper.ExecuteNonQuery(strInsertWvScend, CommandType.Text, parameWvScend);

            if (i <= 0)
            {
                throw new Exception("采购订单" + drItem["EBELN"].ToString() + "回写订单明细表数据库时异常");
            }
            #endregion

            return i;
        }

        /// <summary>
        /// 查询已打印的物流信息
        /// </summary>
        /// <param name="strBillNo">单据编号</param>
        /// <param name="strGoodInfo">商品信息</param>
        /// <param name="strBarCode">追溯码</param>
        /// <param name="strLot">批号</param>
        /// <returns></returns>
        public DataTable GetWvScend(string strBillNo,string strGoodInfo,string strBarCode,string strLot)
        {
            string sqlstr = @"SELECT 
                                  (@rowNum:=@rowNum+1) rownum1,
                                  D.`GoodsGuid`,
                                  D.`GoodsCode`,
                                  D.`GoodsName`,
                                  D.`Lot`,
                                  D.`ExpireDate`,
                                  S.`BarCode`,
                                  S.`RfidUid`,
                                  S.`BillNo`,
                                  D.`SupplierGUID` 
                                FROM
                                  `spd_wvdetail_scend` AS S 
                                  LEFT JOIN `spd_wvdetail` AS D 
                                    ON S.`RDGUID` = D.`GUID` 
                                  INNER JOIN (SELECT (@rowNum :=0) ) Tc
                                WHERE 1 = 1";

            if (!string.IsNullOrEmpty(strBillNo))
            {
                sqlstr += " AND s.`BillNo` LIKE '%" + strBillNo + "%'";
            }

            if (!string.IsNullOrEmpty(strGoodInfo))
            {
                sqlstr += " AND (d.`GoodsCode` LIKE '%" + strGoodInfo + "%' OR d.`GoodsName` LIKE '%" + strGoodInfo + "%')";
            }
       
            if (!string.IsNullOrEmpty(strBarCode))
            {
                sqlstr += " AND s.`BarCode` LIKE '%" + strBarCode + "%'";
            }

            if (!string.IsNullOrEmpty(strLot))
            {
                sqlstr += " AND d.`Lot` LIKE '%" + strLot + "%'";
            }

            DataSet ds = MySQLHelper.GetDataSet(sqlstr, CommandType.Text);
            return ds.Tables[0];
        }

        public int Update(string strNewRfid, string strOldRfid)
        {
            string updatestr = @" update spd_wvdetail_scend 
                                    set Rfiduid=@newfide,
                                        PrintTime=@printtime
                                where Rfiduid=@oldrfid";
            MySql.Data.MySqlClient.MySqlParameter[] parames = new MySql.Data.MySqlClient.MySqlParameter[3];
            parames[0] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[0].ParameterName = "newfide";
            parames[0].Value = strNewRfid;

            parames[1] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[1].ParameterName = "oldrfid";
            parames[1].Value = strOldRfid;

            parames[2] = new MySql.Data.MySqlClient.MySqlParameter();
            parames[2].ParameterName = "printtime";
            parames[2].Value = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
            int i = MySQLHelper.ExecuteNonQuery(updatestr, CommandType.Text, parames);
            return i;
        }
    }
}