public void updateNum(string p, string goodsNum)
{
string sql = "update zc_processgoods set goodstotalnum = :num where id = :id";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":num", goodsNum);
cmd.Parameters.Add(":id", p);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
private void loadIp()
{
string sql = "select workip from zc_workstation ";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string ip = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
ConstantUtil.ipList.Add(ip);
}
}
catch (Exception e)
{
log.Error("加载各工位IP", e);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
public string FindGoodsNum(string p)
{
string num = "";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
string sql = "select goodstotalnum from zc_processgoods where id = :id";
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":id", p);
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
num = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(num);
}
public void setIdCodeToBranch(string code, ZcBranchTotal zcBranchTotal, string id)
{
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
string sql = "insert into zc_branchId_info (id,createTime, updateTime,branch_name_id, branch_selfcode) values (:id, :createTime, :updataTime,:branch_name, :branch_selfcode)";
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":id", Guid.NewGuid().ToString());
cmd.Parameters.Add(":createTime", DateTime.Now);
cmd.Parameters.Add(":updateTime", DateTime.Now);
cmd.Parameters.Add(":branch_name", zcBranchTotal.Id);
cmd.Parameters.Add(":branch_selfcode", code);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
log.Error("上传系统编码信息发生异常", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
/// <summary>
/// 从线上服务器获取所有的用户
/// </summary>
/// <returns></returns>
public List <User> FindAll()
{
List <User> list = new List <User>();
OracleConnection conn = null;
try
{
conn = OracleUtil.OpenConn();
string sql = "select ID,CREATETIME, UPDATETIME, GENDER, NAME, PASSWORD from ctp_user";
OracleCommand command = new OracleCommand(sql);
command.Connection = conn;
var reader = command.ExecuteReader();
while (reader.Read())
{
User user = new User();
user.Id = reader.IsDBNull(0)? string.Empty : reader.GetString(0);
user.CreateTime = reader.IsDBNull(1)? default(DateTime) : reader.GetDateTime(1);
user.UpdateTime = reader.IsDBNull(2) ? default(DateTime) : reader.GetDateTime(2);
user.Gender = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
user.Name = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
user.Password = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
list.Add(user);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
OracleUtil.CloseConn(conn);
}
return(list);
}
/// <summary>
/// 添加收获信息
/// </summary>
/// <param name="list"></param>
public void AddDailyReceiveGoods(List <DailyReceiveGoods> list)
{
string sql = "insert into zc_daily_receive (id, createTime, updateTime,actmoney, actnums, goodsname, goodsordernums, goodsprice, ordermoney, receivedate, salesman, serialnumber, specifications, unit, branch_id, classify_id, sortenum) values "
+ " (:id ,:createTime, :updateTime, :receive_amount , :actual_quantity, :name, :nums, :price, :order_amount, :receiveDate,:salesman, :serialnumber, :goods_specifications , :unit, :branchId, :classify , :sortenum)";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
foreach (DailyReceiveGoods obj in list)
{
cmd.Parameters.Add(":id", obj.Id);
cmd.Parameters.Add(":createTime", obj.CreateTime);
cmd.Parameters.Add(":updateTime", obj.UpdateTime);
cmd.Parameters.Add(":receive_amount", obj.ReceiveAmount);
cmd.Parameters.Add(":actual_quantity", obj.ActualQuantity);
cmd.Parameters.Add(":name", obj.Name);
cmd.Parameters.Add(":nums", obj.Nums);
cmd.Parameters.Add(":price", obj.GooodsPrice);
cmd.Parameters.Add(":order_amount", obj.OrderAmount);
cmd.Parameters.Add(":receiveDate", obj.receiveDate);
cmd.Parameters.Add(":salesman", obj.Salesman);
cmd.Parameters.Add(":serialnumber", obj.SerialNumber);
cmd.Parameters.Add(":goods_specifications", obj.GoodsSpecifications);
cmd.Parameters.Add(":unit", obj.GoodsUnit);
cmd.Parameters.Add(":branchId", obj.BranchId);
cmd.Parameters.Add(":classify", obj.Classify);
cmd.Parameters.Add(":sortenum", obj.sortenum);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
List <UploadInfo> uploadList = new List <UploadInfo>();
foreach (DailyReceiveGoods obj in list)
{
UploadInfo uploadInfo = new UploadInfo();
uploadInfo.Id = obj.Id;
uploadInfo.CreateTime = DateTime.Now;
uploadInfo.UpdateTime = DateTime.Now;
uploadInfo.Type = Constant.DAILY_RECEIVE_GOODS;
uploadList.Add(uploadInfo);
}
log.Error("插入数据到daily_receive_goods表发生异常", ex);
}
finally
{
cmd.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
public bool UpdateOrderStatusByIdsI(string id, string status)
{
bool flag = false;
string sql = "update zc_order_history set orderstatus = '" + status + "' where id =" + id + "";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("上传zcOrderHistory发生异常", ex);
}
finally
{
cmd.Dispose();
tran.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
public void DeleteById(string processItemId)
{
string sql = "delete from zc_processgoods_items where id = :id";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":id", processItemId);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
public void UpdateItem(ProcessItem item)
{
string sql = "update zc_processgoods_items set goodsnum = :goodsnum , goodsweight = :weight, createTime = :createTime , updateTime = :updateTime where id = :id";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":goodsnum", item.GoodsNum);
cmd.Parameters.Add(":weight", item.GoodsWeight);
cmd.Parameters.Add(":createTime", item.CreateTime);
cmd.Parameters.Add(":updateTime", item.UpdateTime);
cmd.Parameters.Add(":id", item.Id);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
public List <ZcZoning> FindAll()
{
List <ZcZoning> list = new List <ZcZoning>();
OracleConnection conn = null;
try
{
conn = OracleUtil.OpenConn();
string sql = "select * from ZC_ZONING";
OracleCommand command = new OracleCommand(sql);
command.Connection = conn;
var reader = command.ExecuteReader();
while (reader.Read())
{
ZcZoning obj = new ZcZoning();
obj.Id = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
obj.CreateTime = reader.IsDBNull(1) ? default(DateTime) : reader.GetDateTime(1);
obj.UpdateTime = reader.IsDBNull(2) ? default(DateTime) : reader.GetDateTime(2);
obj.City = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
obj.County = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
obj.Province = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
obj.Street = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
list.Add(obj);
}
}
catch (Exception ex)
{
log.Error("下载全部数据失败", ex);
}
finally
{
OracleUtil.CloseConn(conn);
}
return(list);
}
public ZcBranchTotal FindOfAll(string id)
{
OracleConnection conn = null;
ZcBranchTotal obj = new ZcBranchTotal();
try
{
conn = OracleUtil.OpenConn();
string sql = "select * from ZC_BRANCH_TOTAL where branch_code ='" + id + "'";
OracleCommand command = new OracleCommand(sql);
command.Connection = conn;
var reader = command.ExecuteReader();
while (reader.Read())
{
obj.Id = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
obj.CreateTime = reader.IsDBNull(1) ? default(DateTime) : reader.GetDateTime(1);
obj.UpdateTime = reader.IsDBNull(2) ? default(DateTime) : reader.GetDateTime(2);
obj.BranchCode = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
obj.BranchName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4);
obj.DelFlag = reader.IsDBNull(5) ? string.Empty : reader.GetString(5);
obj.Money = reader.IsDBNull(6) ? string.Empty : reader.GetString(6);
obj.CustomerId = reader.IsDBNull(7) ? string.Empty : reader.GetString(7);
obj.ZoningId = reader.IsDBNull(8) ? string.Empty : reader.GetString(8);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
OracleUtil.CloseConn(conn);
}
return(obj);
}
private void SorteForm_Load(object sender, EventArgs e)
{
DateTime first = DateTime.Today;
DateTime last = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd 23:59:59"));
string sql = "select a.id, a.code, a.createTime,b.username, a.make_time from zc_sorte a left join zc_user_info b on b.user_id = a.make_men where a.createTime between :first and :last order by a.createTime ";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
DataSet ds = new DataSet();
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":first", first);
cmd.Parameters.Add(":last", last);
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(ds, "zc_sorte");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "zc_sorte";
dataGridView1.AutoGenerateColumns = false;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
/// <summary>
/// 上传交班信息
/// </summary>
/// <param name="obj"></param>
public void AddDeliverAmountInfo(DeliverAmountInfo obj)
{
string sql = "insert into zc_deliver_amountinfo (id, createTime, updateTime, amountcashier, branch_id, exchangedate, salesman) values (:id, :createTime, :updateTime, :amount, :branchId, :exdate, :salesman)";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":id", obj.Id);
cmd.Parameters.Add(":createTime", obj.CreateTime);
cmd.Parameters.Add(":updateTime", obj.UpdateTime);
cmd.Parameters.Add(":amount", obj.AmountCashier);
cmd.Parameters.Add(":branchId", obj.BranchId);
cmd.Parameters.Add(":exdate", obj.Date);
cmd.Parameters.Add(":salesman", obj.Salesman);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
log.Error("上传交班信息到服务器失败", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
public string getIpName(string street)
{
//获取数据库连接
OracleConnection connection = OracleUtil.OpenConn();
string queryString = "select id from ZC_WORKSTATION where WORKIP='" + street + "'";
OracleCommand command = new OracleCommand(queryString);
command.Connection = connection;
try
{
var reader = command.ExecuteReader();
if (reader.Read())
{
string confirmPassword = string.Format("{0}", reader["id"]);
return(confirmPassword);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
OracleUtil.CloseConn(connection);
}
return(null);
}
public List <string> FindBy(string goodsId, string weight, string street)
{
List <string> list = new List <string>();
string sql = "select id from zc_orders_sorte where goods_id = :goodsId and address = :street and weight= :weight";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":goodsId", goodsId);
cmd.Parameters.Add(":street", street);
cmd.Parameters.Add(":weight", weight);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string id = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
list.Add(id);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(list);
}
/// <summary>
/// 添加数据到服务器
/// </summary>
/// <param name="history"></param>
public void AddZcOrderHistory(ZcOrderHistory obj)
{
string sql = "insert into zc_order_history (id, createTime, updateTime, branchId, cansignphone, consignee, goodsnum, isgift, membercardnumber, orderamount, ordercome, orderdate, ordernum, orderreduceamount, orderstatus, ordertotalvalue, pullflag, member_Id, zczoning_id, actual_money, actual_nums) values (:id,:createTime,:updateTime,:branchId,:phone,:consignee,:goodsnum,:isgift,"
+ ":cardNum,:orderAmount,:orderCome,:orderDate,:orderNum,:uceAmount,:status,:total,:flag,:memberId,:zczoningId, :actual_money, :actual_nums)";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(new OracleParameter(":id", obj.Id));
cmd.Parameters.Add(new OracleParameter(":createTime", obj.CreateTime));
cmd.Parameters.Add(new OracleParameter(":updateTime", obj.UpdateTime));
cmd.Parameters.Add(new OracleParameter(":branchId", obj.BranchId));
cmd.Parameters.Add(new OracleParameter(":phone", obj.Cansignphone));
cmd.Parameters.Add(new OracleParameter(":consignee", obj.Consignee));
cmd.Parameters.Add(new OracleParameter(":goodsnum", obj.GoodSnum));
cmd.Parameters.Add(new OracleParameter(":isgift", obj.IsGift));
cmd.Parameters.Add(new OracleParameter(":cardNum", obj.MemberCardNumber));
cmd.Parameters.Add(new OracleParameter(":orderAmount", obj.OrderAmount));
cmd.Parameters.Add(new OracleParameter(":orderCome", obj.OrderCome));
cmd.Parameters.Add(new OracleParameter(":orderDate", obj.OrderDate));
cmd.Parameters.Add(new OracleParameter(":orderNum", obj.OrderNum));
cmd.Parameters.Add(new OracleParameter(":uceAmount", obj.OrderRedUceAmount));
cmd.Parameters.Add(new OracleParameter(":status", obj.OrderStatus));
cmd.Parameters.Add(new OracleParameter(":total", obj.OrderTotalValue));
cmd.Parameters.Add(new OracleParameter(":flag", obj.PullFlag));
cmd.Parameters.Add(new OracleParameter(":memberId", obj.MemberId));
cmd.Parameters.Add(new OracleParameter(":zczoningId", obj.ZczoingId));
cmd.Parameters.Add(":actual_money", obj.ActualMoney);
cmd.Parameters.Add(":actual_nums", obj.ActualNums);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
List <UploadInfo> uploadList = new List <UploadInfo>();
UploadDao uploadDao = new UploadDao();
UploadInfo obj1 = new UploadInfo();
obj1.Id = obj.Id;
obj1.CreateTime = DateTime.Now;
obj1.UpdateTime = DateTime.Now;
obj1.Type = Constant.ZC_ORDER_HISTORY;
uploadList.Add(obj1);
uploadDao.AddUploadInfo(uploadList);
log.Error("上传zcOrderHistory发生异常", ex);
}
finally
{
cmd.Dispose();
tran.Dispose();
OracleUtil.CloseConn(conn);
}
}
public bool AddZcOrderHistoryItemI(ZcOrderHistoryItem obj)
{
bool flag = false;
string sql = "insert into zc_order_history_item values (:id,:createTime,:updateTime, :actual_money, :actual_nums,:addOn,:amount,:bn,:cost,:gPrice,:goodsState,:itemType,"
+ ":name,:nums,:objId,:orderId,:price,:productId,:score,:sendNum,:TypeId,:weight,:fileId,:providerId)";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(new OracleParameter(":id", obj.Id));
cmd.Parameters.Add(new OracleParameter(":createTime", obj.CreateTime));
cmd.Parameters.Add(new OracleParameter(":updateTime", obj.UpdateTime));
cmd.Parameters.Add(":actual_money", obj.actualMoney);
cmd.Parameters.Add(":actual_nums", obj.actualNums);
cmd.Parameters.Add(new OracleParameter(":addOn", obj.AddOn));
cmd.Parameters.Add(new OracleParameter(":amount", obj.Amount));
cmd.Parameters.Add(new OracleParameter(":bn", obj.Bn));
cmd.Parameters.Add(new OracleParameter(":cost", obj.Cost));
cmd.Parameters.Add(new OracleParameter(":gPrice", obj.GPrice));
cmd.Parameters.Add(new OracleParameter(":goodsState", obj.GoodsState));
cmd.Parameters.Add(new OracleParameter(":itemType", obj.ItemType));
cmd.Parameters.Add(new OracleParameter(":name", obj.Name));
cmd.Parameters.Add(new OracleParameter(":nums", obj.Nums));
cmd.Parameters.Add(new OracleParameter(":objId", obj.ObjId));
cmd.Parameters.Add(new OracleParameter(":orderId", obj.OrderId));
cmd.Parameters.Add(new OracleParameter(":price", obj.Price));
cmd.Parameters.Add(new OracleParameter(":productId", obj.ProductId));
cmd.Parameters.Add(new OracleParameter(":score", obj.Score));
cmd.Parameters.Add(new OracleParameter(":sendNum", obj.SendNum));
cmd.Parameters.Add(new OracleParameter(":TypeId", obj.TypeId));
cmd.Parameters.Add(new OracleParameter(":weight", obj.Weight));
cmd.Parameters.Add(new OracleParameter(":fileId", obj.GoodsFileId));
cmd.Parameters.Add(new OracleParameter(":providerId", obj.ProviderId));
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("新增zc_order_history_item发生异常", ex);
}
finally
{
cmd.Dispose();
tran.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
//添加分拣记录
public List <string> AddtransitItem(List <orderSorte> list)
{
List <string> idStr = new List <string>();
string sql1 = "insert into ZC_ORDERS_SORTE (id, CREATETIME, UPDATETIME, ADDRESS, GOODS_ID, GOODS_NAME, ORDERSNUM, SORTENUM, WEIGHT) values "
+ " (:id ,:createTime, :updateTime, :ADDRESS , :GOODS_ID, :GOODS_NAME, :ORDERSNUM, :SORTENUM, :weight)";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.Connection = conn;
foreach (orderSorte obj in list)
{
string id = obj.id;
if (id == null || "".Equals(id))
{
id = Guid.NewGuid().ToString();
idStr.Add(id);
cmd.CommandText = sql1;
cmd.Parameters.Add(":id", id);
cmd.Parameters.Add(":createTime", obj.createTime);
cmd.Parameters.Add(":updateTime", obj.updateTime);
cmd.Parameters.Add(":ADDRESS", obj.address);
cmd.Parameters.Add(":GOODS_ID", obj.goods_id);
cmd.Parameters.Add(":GOODS_NAME", obj.goods_name);
cmd.Parameters.Add(":ORDERSNUM", obj.orderNum);
cmd.Parameters.Add(":SORTENUM", obj.sorteNum);
cmd.Parameters.Add(":weight", obj.weight);
}
else
{
string sql2 = "update zc_orders_sorte a set a.sortenum = '" + obj.sorteNum + "' where a.id='" + obj.id + "'";
cmd.CommandText = sql2;
}
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();
return(idStr);
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
cmd.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(null);
}