//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)); }
//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)); }
//添加员工_2s public static Employee AddEmployee(Employee employee) { int i = 0; string id = ""; OdbcConnection odbcConnection = DBManager.GetOdbcConnection(); odbcConnection.Open(); OdbcCommand odbcCommand = new OdbcCommand(); OdbcDataReader odbcDataReader = null; odbcCommand.Connection = odbcConnection; string selectSql = "select * from employee where E_ID='"; while (i < 5)//检查id是否可用 { id = IDFormat.getID_8(); selectSql = selectSql + id + "'"; odbcCommand.CommandText = selectSql; odbcDataReader = odbcCommand.ExecuteReader(CommandBehavior.CloseConnection); if (!odbcDataReader.HasRows) { break; } odbcDataReader.Close(); i++; } if (i >= 5)//5次获取不到ID,返回null { odbcConnection.Close(); return(null); } //成功获取到ID odbcDataReader.Close(); odbcConnection.Close(); odbcConnection.Open(); employee.ID = id; employee.PassWord = "******"; String insertSql = String.Format("insert into `marketmanage`.`employee` (`E_ID`, `E_Name`, `E_Sex`, `E_Phone`, `E_Birth`, `E_BankAccount`, `E_Email`, `E_Position`, `E_Password`) " + "values ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}')" , employee.ID, employee.Name, employee.Sex, employee.Phone, employee.Birth, employee.BankAccount, employee.Email, employee.Position, employee.PassWord); odbcCommand.CommandText = insertSql; i = odbcCommand.ExecuteNonQuery(); odbcConnection.Close(); return((i > 0) ? employee : null); }
//入库(手动输入生产日期) 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); }