public DataTable GetDataByVoyAgeNo(string voyageNo, string tradeId)
{
string sql = "select * from InputWork where VoyageNo = @VoyageNo";
Hashtable ht = new Hashtable();
if (tradeId != "")
{
sql += " and CompanyId= @CompanyId";
ht["CompanyId"] = tradeId;
}
ht["VoyageNo"] = voyageNo;
DataTable dt = comm.Query(sql, ht);
return(dt);
}
private void LoadUserData()
{
DataTable dt = comm.Query("select id,UserName,CompanyId,CompanyName,CompanyShortName,LoginName,"
+ "PermissonLevel,LastLoginIp,IsActive,'' as edit,'' as deleted from users");
this.superGrid1.DataSource = dt;
}
public string SaveToV2(string sn)
{
string lastSN = "";
Hashtable ht = new Hashtable();
ht["bag_no"] = sn;
ht["HY_NAME"] = _HYNAME;
string sql = "select * from LH_V1 where bag_no =@bag_no and HY_NAME =@HY_NAME";
DataTable billNoList = comm.Query(sql, ht);
int rs = 0;
if (billNoList != null && billNoList.Rows.Count > 0)
{
string insertSql = "update LH_V1 set scan_flag=1 where id = @id;"
+ "delete from LH_V2 where bill_no =@bill_no and HY_NAME = @HY_NAME;"
+ "insert into LH_V2(bill_no,HY_NAME,bag_no,i_e_flag,scan_no,scan_time,read_flag,send_flag) "
+ "values(@bill_no,@HY_NAME,@bag_no,@i_e_flag,(select isnull(max(scan_no),0) from LH_V2 where HY_NAME = @HY_NAME) + 1,getdate(),0,2);";
foreach (DataRow dr in billNoList.Rows)
{
ht.Clear();
ht["id"] = Convert.ToInt32(dr["id"]);
ht["bill_no"] = dr["bill_no"].ToString();
ht["HY_NAME"] = dr["HY_NAME"].ToString();
ht["bag_no"] = dr["bag_no"].ToString();
ht["i_e_flag"] = dr["i_e_flag"].ToString();
lastSN = dr["bill_no"].ToString();
rs += comm.UID(insertSql, ht);
}
}
else
{
string insertSql2 = "delete from LH_V2 where bill_no =@bill_no and HY_NAME = @HY_NAME;"
+ "insert into LH_V2(bill_no,HY_NAME,scan_no,scan_time,read_flag,send_flag) "
+ "values(@bill_no,@HY_NAME,(select isnull(max(scan_no),0) from LH_V2 where HY_NAME = @HY_NAME) + 1,getdate(),0,2);";
ht.Clear();
ht["bill_no"] = sn;
ht["HY_NAME"] = _HYNAME;
//ht["bag_no"] = dr["bag_no"].ToString();
// ht["i_e_flag"] = dr["i_e_flag"].ToString();
lastSN = sn;
rs += comm.UID(insertSql2, ht);
}
return(lastSN);
}
private void LoadV1data()
{
Hashtable ht = new Hashtable();
ht["HY_NAME"] = userInfo.HY_NAME;
DataTable dt = comm.Query("select *,'' as SCANSTATUS from LH_V1 where HY_NAME= @HY_NAME order by id desc", ht);
this.superGrid1.DataSource = dt;
}
private void LoadV1Data()
{
string sql = "";
Hashtable ht = new Hashtable();
if (this.userInfo.UserName.ToLower() != "admin")
{
sql = "select BILL_NO,BAG_NO,SCAN_FLAG,HY_NAME from LH_V1 "
+ "where HY_NAME=@HY_NAME and addtime >= @stime and addtime <= @etime order by BAG_NO,BILL_NO";
ht["HY_NAME"] = userInfo.HY_NAME;
}
else
{
sql = "select BILL_NO,BAG_NO,SCAN_FLAG,HY_NAME from LH_V1 "
+ "where addtime >= @stime and addtime <= @etime order by HY_NAME, BAG_NO,BILL_NO";
}
DateTime dtNow = DateTime.Now.Date;
ht["stime"] = dtNow;
ht["etime"] = dtNow.AddDays(1);
DataTable dt = comm.Query(sql, ht);
this.superGrid2.DataSource = dt;
}
private void BindData()
{
string sql = "select top 100 a.SCAN_NO,a.BILL_NO,a.PACK_NO,a.VOYAGE_NO,b.BILL_SUM,b.PACK_SUM,"
+ " 0 as ALLSCANBILL,0 as ALLSCANPACK,"
+ " (case a.DEC_TYPE when 0 then 1 else 0 end) as FANGXING,"
+ " (case a.DEC_TYPE when 1 then 1 else 0 end) as CHAYAN,"
+ " (case a.SEND_TYPE when 2 then 1 else 0 end) as NODATA"
+ " from EHS_SCAN_TMP1 a left"
+ " join EHS_SCAN_LIST2 b on a.VOYAGE_NO = b.VOYAGE_NO order by a.SCAN_NO desc";
DataTable dt = comm.Query(sql);
foreach (DataRow dr in dt.Rows)
{
this.superGrid1.Rows.Add(dr.ItemArray);
}
}
private void LoadV2data()
{
Hashtable ht = new Hashtable();
ht["HY_NAME"] = userInfo.HY_NAME;
gridTable = comm.Query("select SCAN_NO,BILL_NO,VOYAGE_NO,MAIN_G_NAME,I_E_FLAG,I_E_PORT,D_DATE, "
// + "(case R_FLAG when 1 then '1' else '' end) R_FLAG, "
// + "(case GJ_FLAG when 1 then '1' else '' end) GJ_FLAG,"
+ "(case RSK_FLAG when 1 then '1' when 0 then '0' else '' end) RSK_FLAG,"
+ "(case OP_TYPE when '04' then '1' else '' end) OP_TYPE "
+ " from LH_V2 where HY_NAME= @HY_NAME order by SCAN_NO desc", ht);
this.superGrid1.DataSource = gridTable;
labAllCount.Text = gridTable.Rows.Count.ToString();
// int maxNum = StringHelper.SafeGetIntFromObj(gridTable.Rows[0][0], 0);
ReflashGrid fc = new ReflashGrid(AppendGrid);
fc.BeginInvoke(null, null);
}
private void LoadV2data()
{
lock (obLoad)
{
while (true)
{
Hashtable ht = new Hashtable();
ht["HY_NAME"] = userInfo.HY_NAME;
DataTable dt = comm.Query("select SCAN_NO,BILL_NO,BAG_NO,MAIN_G_NAME,I_E_FLAG,"
+ "(case RSK_FLAG when 1 then '1' when 0 then '0' else '' end) RSK_FLAG,"
+ "(case OP_TYPE when '04' then '1' else '' end) OP_TYPE,"
+ "HY_NAME,TRADE_NAME,SCAN_TIME"
+ " from LH_V2 where HY_NAME= @HY_NAME order by SCAN_NO desc", ht);
// this.superGrid1.DataSource = gridTable;
if (this.superGrid1.InvokeRequired)
{
this.superGrid1.Invoke(new MethodInvoker(delegate
{
//this.superGrid1.Refresh();
this.superGrid1.DataSource = dt;
// this.superGrid1.Update();
// this.superGrid1.EndEdit();
}));
}
else
{
// this.superGrid1.Refresh();
this.superGrid1.DataSource = dt;
// this.superGrid1.Update();
// this.superGrid1.EndEdit();
}
// labAllCount.Text = gridTable.Rows.Count.ToString();
Thread.Sleep(10000);
}
}
}
private DataTable GetData()
{
StringBuilder sb = new StringBuilder();
Hashtable ht = new Hashtable();
string billno = tbBillNo.Text.Trim();
if (!string.IsNullOrEmpty(billno))
{
sb.Append(" and bill_no like @bill_no");
ht["bill_no"] = "%" + billno + "%";
}
string voyageno = txtVoyageNo.Text.Trim();
if (!string.IsNullOrEmpty(voyageno))
{
sb.Append(" and voyage_no like @voyage_no");
ht["voyage_no"] = "%" + voyageno + "%";
}
else if (rdHistory.Checked)
{
MessageBox.Show("查询历史数据必须输入总运单号！");
return(null);
}
DateTime dtsix = DateTime.Now.Date.AddHours(6);
ht["nowsix"] = dtsix;
if (rdHistory.Checked)
{
sb.Append(" and scan_time >= @stime and scan_time <= @etime and scan_time <= @nowsix");
ht["stime"] = stime.Value;
ht["etime"] = etime.Value;
}
else
{
sb.Append(" and scan_time >= @nowsix");
}
switch (cboxType.SelectedIndex)
{
case 1:
sb.Append(" and dec_type =0");
break;
case 2:
sb.Append(" and dec_type =1");
break;
case 3:
sb.Append(" and send_type =2");
break;
default:
break;
}
string table = rbToday.Checked ? "EHS_SCAN_TMP1" : "EHS_SCAN_TMP2";
string sql = " select SCAN_NO,BILL_NO,VOYAGE_NO,I_E_FLAG,SCAN_TIME,"
+ " (case DEC_TYPE when 0 then 1 else 0 end) as FANGXING,"
+ " (case DEC_TYPE when 1 then 1 else 0 end) as CHAYAN,"
+ " (case SEND_TYPE when 2 then 1 else 0 end) as NODATA from " + table + " where 1=1 " + sb.ToString();
DataTable dt = comm.Query(sql, ht);
return(dt);
}
private void LoadUserData()
{
DataTable dt = comm.Query("select *,'' as edit,'' as deleted from users");
this.superGrid1.DataSource = dt;
}