public static List<VarianceDetail> LoadShipmentDetail(
            string customerCode,
            DateTime sendDate,
            string bundleNo,
            string containerNo,
            string container)
        {
            string sql = @"SELECT  id ,
                        khys_xh ,
                        khys_fhrq ,
                        khys_banderNo ,
                        khys_gz ,
                        khys_sl ,
                        khys_s1 ,
                        khys_s2 ,
                        khys_s3 ,
                        khys_s4 ,
                        khys_s5 ,
                        khys_s6 ,
                        khys_s7 ,
                        khys_s8 ,
                        khys_s9 ,
                        khys_s10 ,
                        khys_s11 ,
                        khys_s12 ,
                        khys_s13 ,
                        khys_s14 ,
                        khys_s15 ,
                        khys_s16 ,
                        khys_s17 ,
                        khys_s18 ,
                        khys_s19 ,
                        khys_s20 ,
                        khys_htbh ,
                        khys_bfb ,
                        1 ,
                        khys_container ,
                        khys_khjq ,
                        ISNULL(khys_htbh, '') ,
                        ISNULL(khdd, '') AS khdd
                FROM    t_tmp_ysyq
                WHERE   sta = 2
                        AND tGuid = ( SELECT TOP 1
                                                tGuid
                                      FROM      t_tmp_ysyq
                                      WHERE     khys_khh = '" + customerCode + @"'
                                      ORDER BY  rq DESC
                                    )
                        AND khys_khh = '" + customerCode + @"'
                        AND DATEDIFF(d, khys_fhrq, '" + sendDate + @"') = 0
                        AND ISNULL(khys_banderNo, '') = '" + bundleNo + @"'
                        AND ISNULL(khys_gz, '') = '" + containerNo + @"'
                        AND ISNULL(khys_container, '') = '" + container + @"'
                ORDER BY khys_xh";

            var list = new List<VarianceDetail>();

            using (var reader = SqlHelper.ExecuteReader(ConnectionHelper.ConnectionString
                , CommandType.Text
                , sql))
            {
                while (reader.Read())
                {
                    var detail = new VarianceDetail();
                    detail.Id = reader.GetInt32(0);
                    detail.ModelNo = reader.IsDBNull(1)?"":reader.GetString(1);
                    detail.SendDate = reader.GetDateTime(2);
                    detail.BundleNo = reader.IsDBNull(3) ? "" : reader.GetString(3);
                    detail.ContainerType = reader.IsDBNull(4) ? "" : reader.GetString(4);
                    detail.Total = reader.GetInt32(5);
                    detail.Size1 = reader.GetInt32(6);
                    detail.Size2 = reader.GetInt32(7);
                    detail.Size3 = reader.GetInt32(8);
                    detail.Size4 = reader.GetInt32(9);
                    detail.Size5 = reader.GetInt32(10);
                    detail.Size6 = reader.GetInt32(11);
                    detail.Size7 = reader.GetInt32(12);
                    detail.Size8 = reader.GetInt32(13);
                    detail.Size9 = reader.GetInt32(14);
                    detail.Size10 = reader.GetInt32(15);
                    detail.Size11 = reader.GetInt32(16);
                    detail.Size12 = reader.GetInt32(17);
                    detail.Size13 = reader.GetInt32(18);
                    detail.Size14 = reader.GetInt32(19);
                    detail.Size15 = reader.GetInt32(20);
                    detail.Size16 = reader.GetInt32(21);
                    detail.Size17 = reader.GetInt32(22);
                    detail.Size18 = reader.GetInt32(23);
                    detail.Size19 = reader.GetInt32(24);
                    detail.Size20 = reader.GetInt32(25);
                    detail.OldHtbh = reader.IsDBNull(26) ? "" : reader.GetString(26);
                    detail.ContractNo = reader.IsDBNull(31) ? "" : reader.GetString(31);

                    list.Add(detail);
                }
            }

            return list;
        }
        public static List<VarianceDetail> LoadDetail(
            string customerCode,
            DateTime sendDate,
            string bundleNo,
            string containerNo,
            string container)
        {
            var list = new List<VarianceDetail>();
            string sql = @"select id,khys_xh,khys_fhrq,khys_banderNo,khys_gz,khys_sl,khys_s1,khys_s2,khys_s3,khys_s4,khys_s5
                        ,khys_s6,khys_s7,khys_s8,khys_s9,khys_s10,khys_s11,khys_s12,
                        khys_s13,khys_s14,khys_s15,khys_s16,khys_s17,khys_s18,khys_s19,khys_s20,
                        khys_htbh,khys_bfb,1,khys_container,khys_fhrq as yfhrq,isnull(oldHtbh,'') as oldHtbh,isnull(cylx,0) as cylx
                        ,isnull(khdd,'') as khdd
                        from t_tmp_ysyq where sta=1 
                        and tGuid=(select top 1 tGuid from t_tmp_ysyq where khys_khh='" + customerCode + @"'order by rq desc)"
                        + @"and khys_khh= '" + customerCode + @"' and datediff(d,khys_fhrq, '" + sendDate.ToShortDateString() + @"')=0 and 
                        isnull(khys_banderNo,'')='" + bundleNo + @"' and isnull(khys_gz,'')='" + containerNo + @"' 
                        and isnull(khys_container,'')='" + container + @"'
                        order by khys_xh";

            using (var reader = SqlHelper.ExecuteReader(ConnectionHelper.ConnectionString
                , CommandType.Text
                , sql))
            {
                while (reader.Read())
                {
                    var detail = new VarianceDetail();
                    detail.Id = reader.GetInt32(0);
                    detail.ModelNo = reader.GetString(1);
                    detail.SendDate = reader.GetDateTime(2);
                    detail.BundleNo = reader.GetString(3);
                    detail.ContainerType = reader.GetString(4);
                    detail.Total = reader.GetInt32(5);
                    detail.Size1 = reader.GetInt32(6);
                    detail.Size2 = reader.GetInt32(7);
                    detail.Size3 = reader.GetInt32(8);
                    detail.Size4 = reader.GetInt32(9);
                    detail.Size5 = reader.GetInt32(10);
                    detail.Size6 = reader.GetInt32(11);
                    detail.Size7 = reader.GetInt32(12);
                    detail.Size8 = reader.GetInt32(13);
                    detail.Size9 = reader.GetInt32(14);
                    detail.Size10 = reader.GetInt32(15);
                    detail.Size11 = reader.GetInt32(16);
                    detail.Size12 = reader.GetInt32(17);
                    detail.Size13 = reader.GetInt32(18);
                    detail.Size14 = reader.GetInt32(19);
                    detail.Size15 = reader.GetInt32(20);
                    detail.Size16 = reader.GetInt32(21);
                    detail.Size17 = reader.GetInt32(22);
                    detail.Size18 = reader.GetInt32(23);
                    detail.Size19 = reader.GetInt32(24);
                    detail.Size20 = reader.GetInt32(25);
                    detail.ContractNo = reader.GetString(26);
                    detail.OldHtbh = reader.GetString(31);
                    detail.CYLX = reader.GetInt16(32);

                    list.Add(detail);
                }
            }

            return list;
        }
        public static VarianceDetail LoadById(int id)
        {
            string sql = @"select id,khys_xh,khys_fhrq,khys_banderNo,khys_gz,khys_sl,khys_s1,khys_s2,khys_s3,khys_s4,khys_s5
                        ,khys_s6,khys_s7,khys_s8,khys_s9,khys_s10,khys_s11,khys_s12,
                        khys_s13,khys_s14,khys_s15,khys_s16,khys_s17,khys_s18,khys_s19,khys_s20,
                        khys_htbh,khys_bfb,1,khys_container,khys_fhrq as yfhrq,isnull(oldHtbh,'') as oldHtbh,isnull(cylx,0) as cylx
                        ,isnull(khdd,'') as khdd
                FROM dbo.t_tmp_ysyq
                WHERE id = " + id;

            using (var reader = SqlHelper.ExecuteReader(ConnectionHelper.ConnectionString
                , CommandType.Text
                , sql))
            {
                if (reader.Read())
                {
                    var detail = new VarianceDetail();
                    detail.Id = reader.GetInt32(0);
                    detail.ModelNo = reader.GetString(1);
                    detail.SendDate = reader.GetDateTime(2);
                    detail.BundleNo = reader.GetString(3);
                    detail.ContainerType = reader.GetString(4);
                    detail.Total = reader.GetInt32(5);
                    detail.Size1 = reader.GetInt32(6);
                    detail.Size2 = reader.GetInt32(7);
                    detail.Size3 = reader.GetInt32(8);
                    detail.Size4 = reader.GetInt32(9);
                    detail.Size5 = reader.GetInt32(10);
                    detail.Size6 = reader.GetInt32(11);
                    detail.Size7 = reader.GetInt32(12);
                    detail.Size8 = reader.GetInt32(13);
                    detail.Size9 = reader.GetInt32(14);
                    detail.Size10 = reader.GetInt32(15);
                    detail.Size11 = reader.GetInt32(16);
                    detail.Size12 = reader.GetInt32(17);
                    detail.Size13 = reader.GetInt32(18);
                    detail.Size14 = reader.GetInt32(19);
                    detail.Size15 = reader.GetInt32(20);
                    detail.Size16 = reader.GetInt32(21);
                    detail.Size17 = reader.GetInt32(22);
                    detail.Size18 = reader.GetInt32(23);
                    detail.Size19 = reader.GetInt32(24);
                    detail.Size20 = reader.GetInt32(25);
                    detail.OldHtbh = reader.GetString(26);
                    detail.ContractNo = reader.GetString(33);

                    return detail;
                }
            }
            return null;

        }