public async Task <int> InsertUpdateFGMASTERFILE(FGMASTERFILEFILTER filter) { using (IDbConnection con = new SqlConnection(QNEConnectionString.ChooseConnection(filter.companyCode))) { string sql = ""; var p = new DynamicParameters(); p.Add("@STCODE", filter.StockCode); if (filter.CATID != null || filter.CATID > 0) { switch (filter.Category) { case 'S': sql = "UPDATE CATEGORIZED_PRODUCTS SET SWINE = @SWINE WHERE STOCKCODE = @STCODE"; p.Add("@SWINE", filter.IsChecked); break; case 'C': sql = "UPDATE CATEGORIZED_PRODUCTS SET COMMON = @COMMON WHERE STOCKCODE = @STCODE"; p.Add("@COMMON", filter.IsChecked); break; case 'P': sql = "UPDATE CATEGORIZED_PRODUCTS SET POULTRY = @POULTRY WHERE STOCKCODE = @STCODE"; p.Add("@POULTRY", filter.IsChecked); break; } return(await con.ExecuteAsync(sql, p, commandType : CommandType.Text)); } else { sql = @"INSERT INTO CATEGORIZED_PRODUCTS (STOCKCODE,SWINE,POULTRY,COMMON) VALUES (@STCODE,@SWINE,@POULTRY,@COMMON)"; switch (filter.Category) { case 'S': p.Add("@SWINE", "1"); p.Add("@POULTRY", "0"); p.Add("@COMMON", "0"); break; case 'C': p.Add("@SWINE", "0"); p.Add("@POULTRY", "0"); p.Add("@COMMON", "1"); break; case 'P': p.Add("@SWINE", "0"); p.Add("@POULTRY", "1"); p.Add("@COMMON", "0"); break; } } return(await con.ExecuteAsync(sql, p, commandType : CommandType.Text)); //sql = @"UPDATE CATEGORIZED_PRODUCTS SET {0}=@Status WHERE STOCKCODE = @StockCode // IF @@ROWCOUNT=0 // INSERT INTO CATEGORIZED_PRODUCTS (STOCKCODE) VALUES(@StockCode);"; } }
public async Task <IActionResult> AddUpdateFG(FGMASTERFILEFILTER filter) { int AffectedRows = await qneDataService.InsertUpdateFGMASTERFILE(filter); if (AffectedRows > 0) { return(Ok()); } return(BadRequest()); }