//改
public static bool AlterByID(string id, string price)
{
double Price = Convert.ToDouble(price);
string sql = "update goods set G_Price=" + Price + " where G_ID='" + id + "'";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//增
public static bool Insert(string name, string phone, string region)
{
string sql = "insert into `marketmanage`.`supplier` (`S_Name`, `S_Phone`, `S_Region`) " +
"values('" + name + "', '" + phone + "', '" + region + "')";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
public static bool UpdateSupplylist(Supplylist sl)
{
string sql = "update `marketmanage`.`supplylist` " +
"set `SL_Price`='" + sl.SL_Price + "' " +
"where `S_ID`='" + sl.S_ID + "' and `G_ID`='" + sl.G_ID + "'";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
public static bool AddGoods(Goods goods)
{
String sql = String.Format("insert into `marketmanage`.`goods` (`G_ID`, `G_Name`,`G_Class`,`G_Unit`,`G_ExpirationDate`,`G_Price`,`G_Store`) " +
"values ('{0}', '{1}', '{2}', '{3}', {4}, {5}, {6})"
, goods.ID, goods.Name, goods.Category, goods.Unit, goods.ExpirationDate, goods.Price, goods.Store);
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//ADD
public static bool AddDiscount(Discount dis)
{
dis.ID = IDFormat.getID_Date16();
string sql = "INSERT INTO `discount`(`D_ID`,`G_ID`,`D_Discount`,`D_Start`,`D_End`)" +
" VALUES('" + dis.ID + "','" + dis.G_ID + "','" + dis.DDiscount + "','" + dis.Start + "','" + dis.End + "')";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//改
public static bool AlterByID(Supplier s)
{
string sql = "UPDATE `marketmanage`.`supplier` " +
"SET `S_Name`='" + s.Name + "',`S_Phone`='" + s.Phone + "',`S_Region`='" + s.Rigion + "' " +
"WHERE `S_ID`='" + s.ID + "'";
// Console.WriteLine("sql:" + sql);
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//ADD
public static bool AddOrder(Order order, List <Orderlist> ol)//order属性E_ID传递进来，其他不用
{
//系统自动生成订单号
order.ID = IDFormat.getID_Date16();
double o_price = 0;
order.Time = DateTime.Now.Date;
//先行插入order
string sqlorder = String.Format("insert into `marketmanage`.`order` (`O_ID`, `O_Price`, `O_time`, `E_ID`) " +
"values ('{0}','{1}','{2}','{3}')", order.ID, o_price, order.Time, order.E_ID);
ExecuteSQL.ExecuteNonQuerySQL_GetBool(sqlorder);
//插入订单列表
foreach (Orderlist orderlist in ol)
{
//根据ID查询商品价格
string sql1 = "select G_Price from Goods where G_ID='" + orderlist.G_ID + "'";
OdbcConnection odbcConnection = DBManager.GetOdbcConnection();
odbcConnection.Open();
OdbcCommand odbcCommand = new OdbcCommand(sql1, odbcConnection);
OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
odbcDataReader.Read();
orderlist.Price = odbcDataReader.GetDouble(0);
odbcConnection.Close();
//orderlist
string sql = "INSERT INTO `marketmanage`.`orderlist` (`O_ID`, `G_ID`, `OL_Price`, `OL_Num`, `OL_Discount`) " +
"VALUES" +
" ('" + order.ID + "', '" + orderlist.G_ID + "','" + orderlist.Price + "', '" + orderlist.Num + "', '" + orderlist.Discount + "')";
ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql);
//更新商品表库存
string sql_goods = "update goods set G_Store=G_Store-" + orderlist.Num + " where G_ID='" + orderlist.G_ID + "'";
ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_goods);
//更新库存表
string sql_store = "select * from storelist where G_ID='" + orderlist.G_ID + "'";// order by SL_ProduceDate desc";//升序输出结果集
odbcConnection = DBManager.GetOdbcConnection();
odbcConnection.Open();
odbcCommand = new OdbcCommand(sql_store, odbcConnection);
odbcDataReader = odbcCommand.ExecuteReader();
odbcDataReader.Read();
string gi_id = odbcDataReader.GetString(1);
string sql_storee = "update storelist set SL_Num=SL_Num-" + orderlist.Num + " where G_ID='" + orderlist.G_ID + "' and GI_ID='" + gi_id + "'";
odbcCommand = new OdbcCommand(sql_storee, odbcConnection);
odbcConnection.Close();
//更新商品统计表
StatisticGoods_C.AddData(orderlist.G_ID, orderlist.Num, orderlist.Price);
//订单金额
o_price += orderlist.Price;
}
//更新销售统计表
StatisticDay_C.AddData(o_price);
//更新订单价格（whole）
string sqlorder_p = "update `marketmanage`.`order` set `O_Price`=" + o_price + " where O_ID='" + order.ID + "'";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sqlorder_p));
}
//Alter
public static bool AlterByD_ID(Discount ds)
{
string sql = "UPDATE `marketmanage`.`discount`"
+ "SET"
+ "`D_Discount`=" + ds.DDiscount.ToString()
+ " ,`D_Start`='" + ds.Start.ToString("yyyy-MM-dd") + "'"
+ " ,`D_End`='" + ds.End.ToString("yyyy-MM-dd") + "'"
+ "WHERE"
+ "`D_ID`='" + ds.ID + "'";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//入库（手动输入生产日期）
public static bool AddGoods(GoodsIn goodsin, DateTime producedate)
{
//判断商品ID是否存在
string sql = "select * from goods where G_ID='" + goodsin.G_ID + "'";
OdbcConnection odbcConnection = DBManager.GetOdbcConnection();
odbcConnection.Open();
OdbcCommand odbcCommand = new OdbcCommand(sql, odbcConnection);
OdbcDataReader odbcDataReader = odbcCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (!odbcDataReader.HasRows)//商品ID不存在 返回空
{
return(false);
}
//商品ID存在 插入
bool flag;
goodsin.GI_ID = IDFormat.getID_8();
String insertSql = String.Format("insert into `marketmanage`.`goodsin` (`GI_ID`, `G_ID`,`S_ID`,`GI_PriceIn`,`GI_Num`,`GI_Date`,`GI_OriginPlace`) " +
"values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')"
, goodsin.GI_ID, goodsin.G_ID, goodsin.S_ID, goodsin.PriceIn, goodsin.Num, goodsin.Date, goodsin.OriginPlace);
flag = ExecuteSQL.ExecuteNonQuerySQL_GetBool(insertSql);
//更新库存表
if (!flag)
{
return(false);
}
string update_storelist = String.Format("insert into `marketmanage`.`storelist` (`G_ID`,`GI_ID`,`SL_Num`,`SL_ProducedDate`) values ('{0}','{1}','{2}','{3}')"
, goodsin.G_ID, goodsin.GI_ID, goodsin.Num, producedate);
flag = ExecuteSQL.ExecuteNonQuerySQL_GetBool(update_storelist);
//更新商品表（库存数量）
if (!flag)
{
return(false);
}
String update_goods = "update Goods set G_Store=G_Store+" + goodsin.Num + " where G_ID='" + goodsin.G_ID + "'";
flag = ExecuteSQL.ExecuteNonQuerySQL_GetBool(update_goods);
return(flag? true : false);
}
public static bool AddData(double price)
{
string sql_CheckDate = "SELECT * FROM `marketmanage`.`statisticday` WHERE `SD_Date`='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
string sql_Insert = "INSERT INTO `marketmanage`.`statisticday`(`SD_Date`,`SD_Price`) " +
"VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + price + "')";
string sql_Update = "UPDATE `marketmanage`.`statisticday`" +
"SET `SD_Price`= `SD_Price`+'" + price + "'" +
"WHERE `SD_Date`=" + DateTime.Now.ToString("yyyy-MM-dd");
if (ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_CheckDate))
{
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_Update));
}
else
{
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_Insert));
}
}
public static bool AddData(string G_ID, double num, double price)
{
string sql_CheckDate = "SELECT * FROM `marketmanage`.`statisticgoods` " +
"WHERE `SG_Date`='" + DateTime.Now.ToString("yyyy-MM-dd") + "' AND `G_ID`='" + G_ID + "'";
string sql_Insert = "INSERT INTO `marketmanage`.`statisticgoods` (`G_ID`,`SG_Date`,`SG_Price`,`SG_Num`) " +
"VALUES ('" + G_ID + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + price + "','" + num + "')";
string sql_Update = "UPDATE `marketmanage`.`statisticgoods` " +
"SET `SG_Price`='" + price + "', `SG_Num`='" + num + "' " +
"WHERE `G_ID`='" + G_ID + "' AND `SG_Date`='" + DateTime.Now.ToString("yyyy-MM-dd") + "'";
if (ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_CheckDate))
{
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_Update));
}
else
{
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql_Insert));
}
}
public static bool AddSupplylist(Supplylist sl)
{
string sql = "INSERT INTO `supplylist`(`S_ID`,`G_ID`,`SL_Price`) VALUES(" + sl.S_ID + ",'" + sl.G_ID + "','" + sl.SL_Price + "')";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//删
public static bool DeleteByID(string id)
{
string sql = "DELETE FROM goods WHERE G_ID='" + id + "'";
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//Delete
public static bool DeleteDisaccountByD_ID(string D_ID)
{
string sql = "DELETE FROM `marketmanage`.`discount` WHERE `D_ID`= " + D_ID;
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}
//删
public static bool DeleteByID(string id)
{
string sql = "DELETE FROM `marketmanage`.`supplier` WHERE S_ID=" + id;
return(ExecuteSQL.ExecuteNonQuerySQL_GetBool(sql));
}