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;
}