public DataSet selectReceipt(string supplier_id, string batch_number)
{
try
{
string sql = "select * from receipt where supplier_id = '" + supplier_id + "' and batch_number = '" + batch_number + "' and merchant_id = '" + user_ifms.ID + "'";
return(select.Data(sql, "receipt"));
}
catch
{
MessageBox.Show("查询失败。");
return(null);
}
}
public DataSet display(string merchant_ID)
{
try
{
string sql = "select client_id, grade, discount from client_VIP natural join merchant_vip where merchant_ID = '" + merchant_ID + "'";
return(select.Data(sql, "client_VIP natural join merchant_vip"));
}
catch (Exception ex)
{
return(null);
}
}
//判断是否存在一条表项
static public bool IsAnEntryExist(string merchant_id, string supplier_id, string batch_number, string id_in_batch)
{
//string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection dbcon = new OracleConnection(db);
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string search = "select merchant_id from merchandise where merchant_id = '" + merchant_id
+ "' and supplier_id = '" + supplier_id
+ "' and batch_number = '" + batch_number
+ "' and id_in_batch = '" + id_in_batch
+ "'";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
//DataSet ds = new DataSet();
//oda.Fill(ds, "merchandise");
DataSet ds = select.Data(search, "merchandise");
for (int i = 0; i < ds.Tables["merchandise"].Rows.Count; i++)
{
if (merchant_id == (string)ds.Tables["merchandise"].Rows[i]["merchant_id"])
{
return(true);
}
}
return(false);
}
//显示全部摊位信息
public DataSet selectStall()
{
try
{
string sql = "select * from stall,quotation where stall.area= quotation.area";
return(select.Data(sql, "stall,quotation"));
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(null);
}
}
//显示所有商家信息
public DataSet showallMerchant()
{
try
{
//string s = "DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection con = new OracleConnection(s);
//con.Open();
string sql = "select * from merchant";
//OracleCommand cmd = new OracleCommand(sql, con);
//cmd.ExecuteNonQuery();
//OracleDataAdapter oda = new OracleDataAdapter(cmd);
//DataSet ds = new DataSet();
//oda.Fill(ds, "merchant");
//con.Close();
return(select.Data(sql, "merchant"));
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return(null);
}
}
public DataSet selectDeliverOrder(string client_id, string order_id)
{
try
{
//string s = "DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234 ";
//OracleConnection con = new OracleConnection(s);
//con.Open();
string sql = "select * from deliver_order where client_id = '" + client_id + "' and order_id = '" + order_id + "' and merchant_id = '" + user_ifms.ID + "'";
//OracleCommand cmd = new OracleCommand(sql, con);
//cmd.ExecuteNonQuery();
//OracleDataAdapter da = new OracleDataAdapter(cmd);
//DataSet ds = new DataSet();
//da.Fill(ds, "deliver_order");
//con.Close();
return(select.Data(sql, "deliver_order"));
}
catch
{
MessageBox.Show("查询失败。");
return(null);
}
}
public DataSet showlead()//显示所有负责的人
{
try
{
//string s = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234 ";
//OracleConnection con = new OracleConnection(s);
//con.Open();
string sql = "select * from leading";
//OracleCommand cmd = new OracleCommand(sql, con);
//cmd.ExecuteNonQuery();
//OracleDataAdapter da = new OracleDataAdapter(cmd);
//DataSet ds = new DataSet();
//da.Fill(ds, "leading");
//con.Close();
return(select.Data(sql, "leading"));
}
catch
{
return(null);
}
}
//通过货物名称查询
static public DataSet SearchByName(string merchant_id, string name)
{
//string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection dbcon = new OracleConnection(db);
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string search = "select * from merchandise where merchant_id = '" + merchant_id + "' and name = '" + name + "'";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
DataSet ds = new DataSet();
//oda.Fill(ds, "merchandise");
ds = select.Data(search, "merchandise");
return(ds);
}
//查看商家所有货物数量信息
static public DataSet ShowAllMerchandiseNumber(string merchant_id)
{
//string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection dbcon = new OracleConnection(db);
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string search = "select name, sum(amount) as sum_amount from merchandise where merchant_id = '" + merchant_id + "' group by name";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
DataSet ds = new DataSet();
//oda.Fill(ds, "merchandise");
ds = select.Data(search, "merchandise");
return(ds);
}
//判断某仓库是否有足够的空间
static public double IsWarehouseCapacityEnough(string warehouse_id, double amount)
{
//string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection dbcon = new OracleConnection(db);
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string search = "select left_capacity from warehouse where warehouse_id = '" + warehouse_id + "'";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
DataSet ds = new DataSet();
//oda.Fill(ds, "warehouse");
ds = select.Data(search, "warehouse");
return(Convert.ToDouble(ds.Tables["warehouse"].Rows[0]["left_capacity"]) - amount);
}
static public DataSet selectReceipt(string supplier_id)
{
try
{
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string sql = "select * from receipt where supplier_id = '" + supplier_id + "' and merchant_id = '" + user_ifms.ID + "'";
return(select.Data(sql, "receipt"));
}
catch
{
MessageBox.Show("查询失败。");
return(null);
}
}
//判断某仓库是否存在
public bool IsWarehouseExisted(string warehouse_id)
{
string search = "select warehouse_id from warehouse where warehouse_id = '" + warehouse_id + "'";
DataSet ds = select.Data(search, "warehouse");
for (int i = 0; i < ds.Tables["warehouse"].Rows.Count; i++)
{
if (warehouse_id == (string)ds.Tables["warehouse"].Rows[i]["warehouse_id"])
{
return(true);
}
}
return(false);
}
//判断要取出的货物是否充足
static public double IsMerchandiseEnough(string merchant_id, string supplier_id, string batch_number, string id_in_batch /*, string name*/, double amount_to_pick /*, string warehouse_id*/)
{
//string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection dbcon = new OracleConnection(db);
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string search = "select amount from merchandise where merchant_id = '" + merchant_id
+ "' and supplier_id = '" + supplier_id
+ "' and batch_number = '" + batch_number
+ "' and id_in_batch = '" + id_in_batch
+ "'";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
DataSet ds = new DataSet();
//oda.Fill(ds, "merchandise");
ds = select.Data(search, "merchandise");
return(Convert.ToDouble(ds.Tables["merchandise"].Rows[0]["amount"]) - amount_to_pick);
}
//判断某仓库是否属于某商家
static public bool IsWarehouseBelongToMerchant(string warehouse_id, string merchant_id)
{
//string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
//OracleConnection dbcon = new OracleConnection(db);
Doselect select = Doselect.instance;
Doexecute execute = Doexecute.instance;
string search = "select warehouse_id from warehouse where merchant_id = '" + merchant_id + "'";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
DataSet ds = new DataSet();
//oda.Fill(ds, "warehouse");
ds = select.Data(search, "warehouse");
for (int i = 0; i < ds.Tables["warehouse"].Rows.Count; i++)
{
if (warehouse_id == (string)ds.Tables["warehouse"].Rows[i]["warehouse_id"])
{
return(true);
}
}
return(false);
}
public DataSet dataset(string name, string phone_number)
{
try
{
//string s = "DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234 ";
//OracleConnection con = new OracleConnection(s);
//con.Open();
string sql = "select * from client where name = '" + name + "' and phone_number = '" + phone_number + "'";
//OracleCommand cmd = new OracleCommand(sql, con);
//cmd.ExecuteNonQuery();
//OracleDataAdapter da = new OracleDataAdapter(cmd);
//DataSet ds = new DataSet();
//da.Fill(ds, "client");
//con.Close();
//return ds;
return(select.Data(sql, "client"));
}
catch
{
return(null);
}
}
//取货
public bool PickUpMerchandise(string merchant_id, string supplier_id, string batch_number, string id_in_batch, /*string name, */ double amount_to_pick /* string warehouse_id*/)
{
string db = " DATA SOURCE=localhost:1521/orcl2;USER ID=scott; password = 1234";
OracleConnection dbcon = new OracleConnection(db);
dbcon.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = dbcon;
OracleTransaction trans = dbcon.BeginTransaction();
//查找warehouse_id
string search = "select warehouse_id from merchandise where merchant_id = '" + merchant_id
+ "' and supplier_id = '" + supplier_id
+ "' and batch_number = '" + batch_number
+ "' and id_in_batch = '" + id_in_batch
+ "'";
//OracleDataAdapter oda = new OracleDataAdapter(search, dbcon);
DataSet ds = new DataSet();
//oda.Fill(ds, "merchandise");
ds = select.Data(search, "merchandise");
string warehouse_id = Convert.ToString(ds.Tables["merchandise"].Rows[0]["warehouse_id"]);
double left_amount = IsMerchandiseEnough(merchant_id, supplier_id, batch_number, id_in_batch, amount_to_pick);
if (left_amount > 0)
{
try {
string updateMerchandise = "update merchandise set amount = " + left_amount.ToString()
+ " where merchant_id = '" + merchant_id
+ "' and supplier_id = '" + supplier_id
+ "' and batch_number = '" + batch_number
+ "' and id_in_batch = '" + id_in_batch
+ "'";
//cmd.CommandText = updateMerchandise;
//cmd.ExecuteNonQuery();
execute.Do(updateMerchandise);
//更新仓库剩余容量
double new_left_capacity = amount_to_pick + IsWarehouseCapacityEnough(warehouse_id, 0);
string update = "update warehouse set left_capacity = " + new_left_capacity.ToString() + "where warehouse_id = '" + warehouse_id + "'";
//cmd.CommandText = update;
// cmd.ExecuteNonQuery();
execute.Do(update);
trans.Commit();
dbcon.Close();
return(true);
}
catch (Exception ex) {
//MessageBox.Show(ex.ToString());
trans.Rollback();
return(false);
}
}
else
{
try {
string deleteMerchandise = "delete from merchandise where merchant_id = '" + merchant_id
+ "' and supplier_id = '" + supplier_id
+ "' and batch_number = '" + batch_number
+ "' and id_in_batch = '" + id_in_batch
+ "'";
//cmd.CommandText = deleteMerchandise;
//cmd.ExecuteNonQuery();
execute.Do(deleteMerchandise);
double new_left_capacity = amount_to_pick + IsWarehouseCapacityEnough(warehouse_id, 0);
string update = "update warehouse set left_capacity = " + new_left_capacity.ToString() + " where warehouse_id = '" + warehouse_id + "'";
//cmd.CommandText = update;
//cmd.ExecuteNonQuery();
execute.Do(update);
trans.Commit();
dbcon.Close();
return(true);
}
catch (Exception ex) {
//MessageBox.Show(ex.ToString());
trans.Rollback();
return(false);
}
}
}