public int ImportExcelData(DataTable dt)
{
if (dt != null && dt.Rows.Count > 0)
{
string billNo = "";
string updateSql = "update LH_V1 set BAG_NO=@BAG_NO,I_E_FLAG=@I_E_FLAG where BILL_NO=@BILL_NO and HY_NAME=@HY_NAME";
string insertSql = "insert into LH_V1(BILL_NO,BAG_NO,I_E_FLAG,HY_NAME) values(@BILL_NO,@BAG_NO,@I_E_FLAG,@HY_NAME)";
Hashtable ht = new Hashtable();
int rs = 0;
foreach (DataRow dr in dt.Rows)
{
ht.Clear();
billNo = dr[1].ToString();
ht["BILL_NO"] = billNo;
ht["BAG_NO"] = dr[0].ToString();
ht["I_E_FLAG"] = dr[2].ToString().ToUpper();
ht["HY_NAME"] = _HYNAME;
if (CheckBiLLNOExist(billNo))
{
rs += comm.UID(updateSql, ht);
}
else
{
rs += comm.UID(insertSql, ht);
}
}
return(rs);
}
return(0);
}
public int DeleteData(string voyageNo)
{
string sql = "delete from InputWork where VoyageNo = @VoyageNo";
Hashtable ht = new Hashtable();
ht["VoyageNo"] = voyageNo;
return(comm.UID(sql, ht));
}
public string UpdateLoginIp(int userId)
{
string newIP = NetHelper.GetLocalIP();
Hashtable ht = new Hashtable();
ht["id"] = userId;
ht["LastLoginIp"] = newIP;
comm.UID("update users set LastLoginIp = @LastLoginIp where id=@id", ht);
return(newIP);
}
private void SendComData(int insertId, Hashtable htparm)
{
_com.Send(string.Format("#1,{0},{1},{2}",
htparm["SCAN_NO"].ToString(), htparm["SEND_TYPE"].ToString(), htparm["BILL_NO"].ToString()));
string sql = "update EHS_SCAN_TMP1 set send_time =getdate() where id=@id";
Hashtable ht = new Hashtable();
ht["id"] = insertId;
comm.UID(sql, ht);
}
private void btnDelV1_Click(object sender, EventArgs e)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要删除吗?", "删除V1表过期数据", messButton);
if (dr == DialogResult.OK)
{
string sql = "delete LH_V1 where addtime < getdate() -30";
int rs = comm.UID(sql);
MessageBox.Show("删除了" + rs + "笔数据");
}
}
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);
}
public bool DeleteUser(int id)
{
string sql = "delete from users where id=@id";
Hashtable ht = new Hashtable();
ht["id"] = id;
return(comm.UID(sql, ht) > 0 ? true : false);
}