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);
}
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 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);
}
}
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 List <ZcProviderInfo> FindAll()
{
List <ZcProviderInfo> list = new List <ZcProviderInfo>();
OracleConnection conn = null;
try
{
conn = OracleUtil.OpenConn();
string sql = "select id,provider_nickname from zc_provider_info";
OracleCommand command = new OracleCommand(sql);
command.Connection = conn;
var reader = command.ExecuteReader();
while (reader.Read())
{
ZcProviderInfo obj = new ZcProviderInfo();
obj.Id = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
obj.ProviderNickName = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
list.Add(obj);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
OracleUtil.CloseConn(conn);
}
return(list);
}
/// <summary>
/// Returns the GSD2 C3 record
/// </summary>
/// <exception cref="ConfigurationErrorsException">A configuration file could not be loaded.</exception>
/// <exception cref="ArgumentNullException"><paramref>
/// <name>key</name>
/// </paramref>
/// is null. </exception>
/// <exception cref="NotSupportedException">The property is set and the <see cref="T:System.Collections.Hashtable" />
/// is read-only.-or- The property is set, <paramref>
/// <name>key</name>
/// </paramref>
/// does not exist in the collection, and the
/// <see cref="T:System.Collections.Hashtable" /> has a fixed size. </exception>
/// <exception cref="FormatException"><paramref>
/// <name>s</name>
/// </paramref>
/// is not in the correct format. </exception>
/// <exception cref="OverflowException"><paramref>
/// <name>s</name>
/// </paramref>
/// represents a number less than <see cref="F:System.Int32.MinValue" /> or greater than <see cref="F:System.Int32.MaxValue" />. </exception>
public static DataTable GSD2_DELIVERYPOINT(string tspNo, string startDte, string endDte, string ctr_no)
{
var procName = ConfigUtil.GetStoredProcedureName("GSD2_DELIVERYPOINT");
var oracleParams = new OracleParameter[5];
oracleParams[0] = new OracleParameter("i_tsp_no", OracleDbType.Int32, ParameterDirection.Input)
{
Value = int.Parse(tspNo)
};
oracleParams[1] = new OracleParameter("i_start_dte", OracleDbType.Date, ParameterDirection.Input)
{
Value = startDte
};
oracleParams[2] = new OracleParameter("i_end_dte", OracleDbType.Date, ParameterDirection.Input)
{
Value = endDte
};
oracleParams[3] = new OracleParameter("i_ctr_no", OracleDbType.Varchar2, ParameterDirection.Input)
{
Value = ctr_no
};
//oracleParams[3] = new OracleParameter("row_id", OracleDbType.Int32, ParameterDirection.Input) {
// Value = rowId
//};
oracleParams[4] = new OracleParameter("o_cursor", OracleDbType.RefCursor, ParameterDirection.Output);
var dt = OracleUtil.ExecuteOracleReader(procName, oracleParams, CommandType.StoredProcedure);
return(dt);
}
public string getSorteId(string ip)
{
string sorteId = "";
String sql = "select \"sorteId\" from zc_sorte_status where IP = :abIP";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":abIP", ip);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
sorteId = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
}
}
catch (Exception e)
{
Console.WriteLine("根据ip查询sorteId失败" + e.Message);
}
return(sorteId);
}
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);
}
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 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);
}
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);
}
}
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);
}
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>
/// This Method gets Operationally Available Capacity data in a Dataset.
/// </summary>
/// <param name="startDate">Starting Date</param>
/// <param name="envAndPipe">TSP Number</param>
/// <returns>DataSet</returns>
/// <exception cref="OracleException">Condition.</exception>
/// <exception cref="InvalidOperationException">Condition.</exception>
/// <exception cref="ArgumentException">The value was not a valid <see cref="T:System.Data.CommandType" />. </exception>
public static DataTable GetOperationallyAvailableData(string envAndPipe, DateTime startDateValue, DateTime endDateValue)
{
// Check that the difference between start and end dates is less than 3 years (1096 days)
// The website restricts users' date selection. But if a hacker finds a way to bypass the restriction,
// then return an empty table without throwing any exceptions.
if (((endDateValue - startDateValue).Days > 1096) || (startDateValue > endDateValue))
{
return(new DataTable());
}
// append end of day hour, minute, and second to the end date value
string modifiedEndDate = string.Format("{0:MM/dd/yyyy}", endDateValue) + " 23:59:59";
endDateValue = Convert.ToDateTime(modifiedEndDate);
string procName = "CNSS.WEBIP_CAPACITY.GET_OPERAVAIL_BYDATES";
var oracleParams = new OracleParameter[3];
oracleParams[0] = new OracleParameter("StartDate", OracleDbType.Date, ParameterDirection.Input)
{
Value = startDateValue
};
oracleParams[1] = new OracleParameter("EndDate", OracleDbType.Date, ParameterDirection.Input)
{
Value = endDateValue
};
oracleParams[2] = new OracleParameter("io_cursor", OracleDbType.RefCursor, ParameterDirection.Output);
var dt = OracleUtil.ExecuteOracleReader(envAndPipe, procName, oracleParams, CommandType.StoredProcedure);
return(dt);
}
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);
}
/// <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);
}
}
/// <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);
}
}
/// <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);
}
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);
}
}
/// <summary>
/// 加载登录用户的分店信息
/// 现在先通过oracle 获取这些信息 之后会换成本地数据库
/// </summary>
private void loadLoginUserInfo()
{
OracleConnection conn = null;
try
{
conn = OracleUtil.OpenConn();
string sql = "select a.id, a.branch_code,a.branch_name,d.street "
+ "from zc_branch_total a "
+ " left join zc_user_info b on a.id = b.BRANCH_NAME_ID "
+ " left join ctp_user c on c.id=b.user_id left join zc_zoning d on d.id=a.zoning_id where c.id='" + LoginUserInfo.id + "'";
OracleCommand cmd = new OracleCommand(sql, conn);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
LoginUserInfo.branchId = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
LoginUserInfo.branchCode = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
LoginUserInfo.branchName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
LoginUserInfo.street = reader.IsDBNull(3) ? string.Empty : reader.GetString(3);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
OracleUtil.CloseConn(conn);
}
}
public TestOracleUtil()
{
string conn = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.1.188)(PORT=1521))(CONNECT_DATA=(SID=orcl)));User Id=amyhu;Password=amyhu";
this.ou = new OracleUtil(conn);
this.ou.Open();
}
public StoreHouse FindByGoodsFileId(string goodsFileId)
{
StoreHouse obj = new StoreHouse();
string sql = "select id,store, storemoney from zc_storehouse where branch_id = :branchId and goodsfile_id = :goodsFileId ";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.Parameters.Add(":branchId", ConstantUtil.BranchId);
cmd.Parameters.Add(":goodsFileId", goodsFileId);
OracleDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
obj.Id = reader.IsDBNull(0) ? string.Empty : reader.GetString(0);
obj.Store = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
obj.StoreMoney = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
obj.GoodsFileId = goodsFileId;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(obj);
}
private void processList_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 id, odd, createTime from zc_processgoods where createTime between :first and :last order by 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_processgoods");
dataGridView1.DataSource = ds;
dataGridView1.DataMember = "zc_processgoods";
dataGridView1.AutoGenerateColumns = false;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
public string getBranchName(string street)
{
//获取数据库连接
OracleConnection connection = OracleUtil.OpenConn();
string queryString = "select a.branch_name from zc_branch_total a left join zc_zoning b on b.id = a.zoning_id where b.street= '" + street + "'";
OracleCommand command = new OracleCommand(queryString);
command.Connection = connection;
try
{
var reader = command.ExecuteReader();
if (reader.Read())
{
string confirmPassword = string.Format("{0}", reader["branch_name"]);
return(confirmPassword);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
OracleUtil.CloseConn(connection);
}
return(null);
}
public void deleteSorteStatus(string ip)
{
string sql1 = "delete from zc_sorte_status where ip ='" + ip + "'";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql1;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
cmd.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
//删除processOrder
public void deletePorcessOrder(string id)
{
string sql = "delete from ZC_ORDER_PROCESS where id='" + id + "'";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
cmd.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
//更新
public void UpdateStatus(object id)
{
string sql = "update ZC_SORTE set AUDITS_TATUS = '4' where id = '" + id + "'";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
//cmd.Parameters.Add(":status", obj.OrderStatus);
//cmd.Parameters.Add(":id", obj.Id);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
cmd.Dispose();
tran.Dispose();
OracleUtil.CloseConn(conn);
}
}
public bool DeleteById(string id)
{
bool flag = false;
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
string sql = "delete from zc_order_transit where id = :id";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":id", id);
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("服务器订单号删除订单失败", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
public bool UpdateStatus(string p1, string p2)
{
bool flag = false;
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
string sql = "update zc_order_transit set orderstatus = :status, updateTime = :updateTime where id = :id";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":status", p2);
cmd.Parameters.Add(":updateTime", DateTime.Now);
cmd.Parameters.Add(":id", p1);
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("服务器订单号删除订单失败", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
/// <summary>
/// 上传退款明细信息
/// </summary>
/// <param name="list"></param>
public void AddZcOrderRefundItem(List <ZcOrderRefundItem> list)
{//
string sql = "insert into zc_order_refund_item (id, createTime, updateTime, serialNumber, order_id, order_nums, refund_nums, price, refund_amount, salesman_id, hand_date, street, goodsFile_id, refund_id, refund_reason) values (:id,:createTime,:updateTime,:serialNumber,:order_id,:order_nums,:refund_nums,:price,:refund_amount,:salesman_id,:hand_date,:street,:goodsFile_id,:refund_id,:refund_reason)";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
foreach (ZcOrderRefundItem obj in list)
{
cmd.Parameters.Add(":id", obj.Id);
cmd.Parameters.Add(":createTime", obj.CreateTime);
cmd.Parameters.Add(":updateTime", obj.UpdateTime);
cmd.Parameters.Add(":serialNumber", obj.SerialNumber);
cmd.Parameters.Add(":order_id", obj.Order_id);
cmd.Parameters.Add(":order_nums", obj.Order_nums);
cmd.Parameters.Add(":refund_nums", obj.Refund_nums);
cmd.Parameters.Add(":price", obj.Price);
cmd.Parameters.Add(":refund_amount", obj.Refund_amount);
cmd.Parameters.Add(":salesman_id", obj.Salesman_id);
cmd.Parameters.Add(":hand_date", obj.Hand_date);
cmd.Parameters.Add(":street", obj.Street);
cmd.Parameters.Add(":goodsFile_id", obj.GoodsFile_id);
cmd.Parameters.Add(":refund_id", obj.Refund_id);
cmd.Parameters.Add(":refund_reason", obj.Refund_reason);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
List <UploadInfo> uploadList = new List <UploadInfo>();
UploadDao uploadDao = new UploadDao();
foreach (ZcOrderRefundItem obj in list)
{
UploadInfo obj2 = new UploadInfo();
obj2.Id = obj.Id;
obj2.CreateTime = DateTime.Now;
obj2.UpdateTime = DateTime.Now;
obj2.Type = Constant.ZC_ORDER_REFUND_ITEM;
uploadList.Add(obj2);
}
uploadDao.AddUploadInfo(uploadList);
log.Error("上传退款明细数据到服务器发生异常", ex);
}
finally
{
cmd.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
