樹心幽徑

« 20181104修改new_cust資料表中某一客戶(custno)的儲值金額(remain) t14.c | Main | 20181107new_cust顧客基本資料編修程式(t16.c) »

20181105調減new_goods資料表中指定貨品編號(gdsno)的貨品庫存量(stock_qty) t15.c
2018/11/06,20:53

(1)t15.c程式碼:

#include <my_global.h>
#include <mysql.h>
#define HOST "localhost"
#define USER "treehrt"
#define PWD "abc123abc123"
#define DB "ilvssell"
void finish_with_error(MYSQL *con)
{
  fprintf(stderr, "%s\n", mysql_error(con));
  mysql_close(con);
  exit(1);       
}
typedef struct GOODSREC
{
  char gdsno[20];
  char gdsname[30];
  char spec[10];
  double cost;
  double price_in;
  double price_special;
  double price_sell;
  char unit[4];
  int stock_qty;
  char fac_no[20];
//  char mem[80];
} goodsRec ;


int  GetGoodsRecByNo(char *gdsno, goodsRec *pv)
{
  char st[280];
  sprintf(st,"select gdsno,gdsname,cost,price_in,price_special,price_sell,unit,stock_qty,fac_no from new_goods where gdsno='%s'",gdsno);
  MYSQL *con = mysql_init(NULL);
  if (con == NULL) {
      fprintf(stderr, "%s\n", mysql_error(con));
      exit(1);
  }
  if (mysql_real_connect(con, HOST , USER, PWD, DB, 0, NULL, 0) == NULL) finish_with_error(con);
  mysql_query(con, "set names utf8");
  if (mysql_query(con, st)) finish_with_error(con);
  printf("SQL指令: [%s] 執行成功! \n\n", st);
  MYSQL_RES *result = mysql_store_result(con);
  if (result == NULL)  finish_with_error(con);
  MYSQL_ROW row;
  row = mysql_fetch_row(result);
  if (row){
    strcpy(pv->gdsno,row[0]);
    strcpy(pv->gdsname,row[1]);
    pv->cost=atof(row[2]);
    pv->price_in=atof(row[3]);
    pv->price_special=atof(row[4]);
    pv->price_sell=atof(row[5]);
    strcpy(pv->unit,row[6]);
    pv->stock_qty=atoi(row[7]);
    strcpy(pv->fac_no,row[8]);
  }
  else {
    printf("can not found gdsno: %s!!\n",gdsno);
    }
  mysql_free_result(result);
  mysql_close(con);
  return 0;
}

void  minus_goods_qty(char *gdsno, int qty)
{
    char st[500];
        sprintf(st,"update new_goods set stock_qty=stock_qty-(%d) where gdsno='%s'",qty,gdsno);
    MYSQL *con = mysql_init(NULL);
    if (con == NULL)  finish_with_error(con);
    if (mysql_real_connect(con, HOST , USER, PWD, DB, 0, NULL, 0) == NULL) finish_with_error(con);
    if (mysql_query(con, st)) finish_with_error(con);
      printf("SQL指令: [%s] 執行成功! \n\n", st);
}
int main(int argc, char **argv)
{
  char gdsno[20];
  printf("貨品存量調減程式\n輸入貨品編號gdsno=");
  goodsRec gdR;
  scanf("%s",gdsno);
  GetGoodsRecByNo(gdsno, &gdR);
  printf("修改前 %s[%s]庫存量=%d\n",gdsno,gdR.gdsname,gdR.stock_qty);
  int qty;
  printf("輸入要調減的貨品數量qty=");
  scanf("%d",&qty);
  minus_goods_qty(gdsno, qty);
  GetGoodsRecByNo(gdsno, &gdR);
  printf("修改後 %s[%s]庫存量=%d\n",gdsno,gdR.gdsname,gdR.stock_qty);
  exit(0);
}

 

(2)編譯並執行

treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ gcc t15.c `mysql_config --cflags --libs`

treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$ ./a.out
貨品存量調減程式
輸入貨品編號gdsno=a001
SQL指令: [select gdsno,gdsname,cost,price_in,price_special,price_sell,unit,stock_qty,fac_no from new_goods where gdsno='a001'] 執行成功!

修改前 a001[蕃茄]庫存量=300
輸入要調減的貨品數量qty=300
SQL指令: [update new_goods set stock_qty=stock_qty-(300) where gdsno='a001'] 執行成功!

SQL指令: [select gdsno,gdsname,cost,price_in,price_special,price_sell,unit,stock_qty,fac_no from new_goods where gdsno='a001'] 執行成功!

修改後 a001[蕃茄]庫存量=0
treehrt@treehrt-BM6AE-BM1AE-BP1AE:~/mysqlc$

 

迴響

 
Accessible and Valid XHTML 1.0 Strict and CSS Powered by LifeType