后端API using Azure;
using log4net;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ProductAPI.Models;
using ProductAPI.Services;
using System.Data;
using System.Text;
using static System.Runtime.InteropServices.JavaScript.JSType;
using System.Text.Json;
namespace ProductAPI.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class ProductionController : ControllerBase
{
private readonly ManageDbContext _oracleDbContext;
private static readonly ILog logger = LogManager.GetLogger(typeof(ProductionController));
private string _connectionString = "User Id=your_user;Password=your_password;Data Source=your_datasource";
public ProductionController(ManageDbContext oracleDbContext)
{
_oracleDbContext = oracleDbContext;
}
[HttpGet("GetImageProductInfo")]
public async Task<IActionResult> GetImageProductInfo(DateTime date)
{
// 1. 准备时间范围
string startTime = date.Date.ToString("yyyy-MM-dd HH:mm:ss");
string endTime = date.Date.AddHours(23).AddMinutes(59).AddSeconds(59).ToString("yyyy-MM-dd HH:mm:ss");
// 2. 从配置表获取原始数据
var configData = await _oracleDbContext.MANAGE_IMAGE_CFG
.Select(c => new { c.IMAGECFGLINE, c.IMAGECFGOP, c.IMAGECFGADDRESS })
.ToListAsync();
// 3. 获取所有唯一的Line列表
var distinctLines = configData
.Select(c => c.IMAGECFGLINE)
.Distinct()
.ToList();
// 4. 获取所有Line的数据库配置
var dbConfigs = await _oracleDbContext.API_SERVER_DB
.Where(c => distinctLines.Contains(c.NEW_NAME))
.ToDictionaryAsync(c => c.NEW_NAME, c => new { c.ACC_LINE, c.CONN_STR });
// 5. 按Line和OP分组配置数据
var groupedData = configData
.GroupBy(c => new { c.IMAGECFGLINE, c.IMAGECFGOP })
.Select(g => new ProductionSummary
{
Line = g.Key.IMAGECFGLINE,
Op = g.Key.IMAGECFGOP,
Addresses = g.Select(x => x.IMAGECFGADDRESS).Distinct().ToList(),
ProductionCount = 0, // 初始化为0，后面会填充
PhotoCount = 0 // 初始化为0，后面会填充
})
.ToList();
// 6. 按Line分组处理生产量查询
var lineGroups = groupedData
.GroupBy(g => g.Line)
.ToList();
foreach (var lineGroup in lineGroups)
{
string line = lineGroup.Key;
if (!dbConfigs.TryGetValue(line, out var config))
{
logger.Info(string.Format("No database config found for line: {0}", line));
continue;
}
// 获取该Line下所有的Op列表
var ops = lineGroup.Select(g => g.Op).Distinct().ToList();
// 使用动态SQL查询多个Op的生产量
var productionData = await GetProductionCountsAsync(
config.CONN_STR,
config.ACC_LINE,
ops,
startTime,
endTime
);
// 更新分组的生产量
foreach (var summary in lineGroup)
{
if (productionData.TryGetValue(summary.Op, out var count))
{
summary.ProductionCount = count;
}
}
}
// 7. 并行统计照片数量
var parallelOptions = new ParallelOptions { MaxDegreeOfParallelism = Environment.ProcessorCount };
Parallel.ForEach(groupedData, parallelOptions, summary =>
{
summary.PhotoCount = GetPhotoCount(summary.Addresses, date.Date, date.Date.AddHours(23).AddMinutes(59).AddSeconds(59));
});
return Ok(groupedData.Select(summary => new {
summary.Line,
summary.Op,
summary.ProductionCount,
summary.PhotoCount
}));
}
// 查询多个Op的生产量（使用IN子句优化）
private async Task<Dictionary<string, int>> GetProductionCountsAsync(
string connectionString,
string accLine,
List<string> ops,
string startTime,
string endTime)
{
var result = new Dictionary<string, int>();
if (string.IsNullOrEmpty(connectionString) || ops.Count == 0)
return result;
try
{
// 使用方法参数 connectionString 初始化 OracleHelper
var dbhelper = new OracleHelper(connectionString);
// 构建参数化查询（防止SQL注入）
var sql = new StringBuilder();
sql.AppendLine("SELECT op, SUM(CASE WHEN \"RESULT\" = '1' THEN 1 ELSE 0 END) AS CountOK, ");
sql.AppendLine(" SUM(CASE WHEN \"RESULT\" = '0' THEN 1 ELSE 0 END) AS CountNOK ");
sql.AppendLine("FROM acc_unithistory ");
sql.AppendLine("WHERE line = :line AND op IN (" + string.Join(",", ops.Select((_, i) => $":op{i}")) + ") ");
sql.AppendLine(string.Format("and enddt between to_date('{0}', 'yyyy-MM-dd HH24:mi:ss') ", startTime));
sql.AppendLine(string.Format("and to_date ('{0}', 'yyyy-MM-dd HH24:mi:ss') ", endTime));
sql.AppendLine("GROUP BY op");
var parameters = new Dictionary<string, object>
{
{ ":line", accLine }
};
// 添加Op参数
for (int i = 0; i < ops.Count; i++)
{
parameters.Add($":op{i}", ops[i]);
}
var queryResult = await dbhelper.ExecuteQueryAsync(sql.ToString(), parameters);
foreach (var row in queryResult)
{
string op = row["OP"].ToString();
int countOK = row.ContainsKey("COUNTOK") ? Convert.ToInt32(row["COUNTOK"]) : 0;
int countNOK = row.ContainsKey("COUNTNOK") ? Convert.ToInt32(row["COUNTNOK"]) : 0;
result[op] = countOK + countNOK;
}
}
catch (Exception ex)
{
logger.Info(string.Format("Error querying production counts for line {0}", accLine) + ex.Message);
}
return result;
}
// 更新照片统计方法以支持日期范围
private int GetPhotoCount(List<string> addresses, DateTime startDate, DateTime endDate)
{
int totalCount = 0;
var photoExtensions = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
{
".jpg", ".jpeg", ".png"
};
// 计算日期范围中的所有日期
var dateRange = GetDateRange(startDate, endDate);
// 并行处理每个地址
Parallel.ForEach(addresses, address =>
{
int addressCount = 0;
// 处理日期范围内的每一天
foreach (var date in dateRange)
{
try
{
// 构建完整路径: 基础路径 + 配置地址 + 日期文件夹
string dateFolder = date.ToString("yyyyMMdd");
string fullPath = $@"\\at3a90100.ad.trw.com\TATS_image$\TATS_Image\{address}\{dateFolder}";
// 规范化路径
fullPath = Path.GetFullPath(fullPath);
if (Directory.Exists(fullPath))
{
// 统计该日期文件夹内的照片数量
var files = Directory.EnumerateFiles(
fullPath,
"*.*",
SearchOption.TopDirectoryOnly
);
int dateCount = files.Count(file =>
photoExtensions.Contains(Path.GetExtension(file))
);
addressCount += dateCount;
logger.Info($"Found {dateCount} files in {fullPath}");
}
else
{
logger.Info($"Directory not found: {fullPath}");
}
}
catch (Exception ex)
{
logger.Info(ex + $"Error accessing directory for address {address} on {date:yyyy-MM-dd}");
}
}
// 原子操作更新总计数
Interlocked.Add(ref totalCount, addressCount);
});
return totalCount;
}
// 获取日期范围内的所有日期
private List<DateTime> GetDateRange(DateTime startDate, DateTime endDate)
{
var dates = new List<DateTime>();
// 确保日期范围合理
if (startDate > endDate)
{
logger.Info($"Invalid date range: {startDate} to {endDate}");
return dates;
}
// 添加范围内的所有日期
for (DateTime date = startDate.Date; date <= endDate.Date; date = date.AddDays(1))
{
dates.Add(date);
}
return dates;
}
[HttpGet("GetImageStatusHistory")]
public async Task<IActionResult> GetImageStatusHistory(string Line, DateTime? date)
{
Console.WriteLine("GetImageStatusHistory");
// 使用 IQueryable 延迟查询，便于动态拼接条件
IQueryable<MANAGE_IMAGE_STATUS_LOG> query = _oracleDbContext.MANAGE_IMAGE_STATUS_LOG;
// 动态添加查询条件
if (!string.IsNullOrEmpty(Line))
{
query = query.Where(x => x.IMAGECFGLINE == Line);
}
if (date.HasValue)
{
query = query.Where(x => x.IMAGEDATE.Date == date.Value.Date);
}
var data = await query.ToListAsync();
return Ok(new { Codestatus = 200, data });
}
[HttpGet("GetImageLine")]
public async Task<IActionResult> GetImageLine()
{
Console.WriteLine("GetImageLineInfo");
try
{
var result = await _oracleDbContext.API_SERVER_DB
.GroupBy(c => c.NEW_NAME)
.Select(c => c.Key)
.ToListAsync();
if (result == null || !result.Any())
{
return BadRequest(new { Codestatus = 400, message = "No data found." });
}
return Ok(new { Codestatus = 200, data = result });
}
catch (Exception ex)
{
return StatusCode(500, new { Codestatus = 500, message = $"服务器错误: {ex.Message}" });
}
}
[HttpGet("GetImageLineOP")]
public async Task<IActionResult> GetImageLineOP(string line)
{
Console.WriteLine("GetImageOP Line: " + line);
var response = new
{
Code = 200,
Msg = string.Empty
};
var config = _oracleDbContext.API_SERVER_DB.Where(c => c.NEW_NAME == line).FirstOrDefault();
if (config == null)
{
response = new
{
Code = 200,
Msg = "No Mach Line conn date"
};
return Ok(response);
}
_connectionString = config.CONN_STR;
var dbhelper = new OracleHelper(_connectionString);
string Line = config.ACC_LINE;
var sql = "select OP from acc_lineop_cfg where line = :line";
var parameters = new Dictionary<string, object>
{
{ "line", Line } // 不需要加引号
};
try
{
var result = await dbhelper.ExecuteSecureQueryAsync(sql, parameters);
if (result != null && result.Count > 0)
{
var list = result
.Where(row => row.ContainsKey("OP"))
.Select(row => row["OP"]?.ToString())
.Where(op => !string.IsNullOrEmpty(op))
.ToList();
return Ok(new { Codestatus = 200, data = list });
}
return BadRequest(new { Codestatus = 400, message = "No data found." });
}
catch (Exception ex)
{
return StatusCode(500, new { Codestatus = 500, message = $"服务器错误: {ex.Message}" });
}
}
[HttpGet("GetImageCfg")]
public async Task<IActionResult> GetImageCfg()
{
Console.WriteLine("GetImageCfg.");
try
{
var result = await _oracleDbContext.MANAGE_IMAGE_CFG.ToListAsync();
if (result == null || !result.Any())
{
return NotFound(new { Codestatus = 404, message = "No data found." });
}
return Ok(new { Codestatus = 200, data = result });
}
catch (Exception ex)
{
logger.Info(ex + "Error occurred in GetImageCfg");
return StatusCode(500, new { Codestatus = 500, message = "Internal server error." });
}
}
[HttpPost("SaveImageConfig")]
public async Task<IActionResult> SaveImageConfig([FromBody] MANAGE_IMAGE_CFG Imageform)
{
Console.WriteLine(JsonSerializer.Serialize(Imageform));
// 临时打印调试
Console.WriteLine($"LINE: {Imageform.IMAGECFGLINE}, OP: {Imageform.IMAGECFGOP}");
// 如果字段为 null，说明模型绑定失败
if (Imageform.IMAGECFGLINE == null)
{
return BadRequest("模型绑定失败");
}
Imageform.IMAGECFGID = Guid.NewGuid().ToString(); // 生成唯一 ID
try
{
await _oracleDbContext.MANAGE_IMAGE_CFG.AddAsync(Imageform);
int result = await _oracleDbContext.SaveChangesAsync();
if (result > 0)
return Ok(new { Codestatus = 200, message = "保存成功", id = Imageform.IMAGECFGID });
else
return StatusCode(500, new { Codestatus = 500, message = "保存失败" });
}
catch (Exception ex)
{
return StatusCode(500, new { Codestatus = 500, message = "异常：" + ex.Message });
}
}
[HttpDelete("DeleteImageCfg/{id}")]
public async Task<IActionResult> DeleteImageCfg(string id)
{
var cfg = await _oracleDbContext.MANAGE_IMAGE_CFG.Where(c => c.IMAGECFGID == id).FirstAsync();
if (cfg == null)
return NotFound(new { Codestatus = 404, message = "未找到记录" });
_oracleDbContext.MANAGE_IMAGE_CFG.Remove(cfg);
await _oracleDbContext.SaveChangesAsync();
return Ok(new { Codestatus = 200, message = "删除成功" });
}
[HttpPut("UpdateImageCfg/{id}")]
public async Task<IActionResult> UpdateImageCfg(string id, [FromBody] MANAGE_IMAGE_CFG updatedCfg)
{
var cfg = await _oracleDbContext.MANAGE_IMAGE_CFG.Where(c => c.IMAGECFGID == id).FirstAsync();
if (cfg == null)
return NotFound(new { Codestatus = 404, message = "未找到记录" });
cfg.IMAGECFGLINE = updatedCfg.IMAGECFGLINE;
cfg.IMAGECFGOP = updatedCfg.IMAGECFGOP;
cfg.IMAGECFGLIMIT = updatedCfg.IMAGECFGLIMIT;
cfg.IMAGECFGADDRESS = updatedCfg.IMAGECFGADDRESS;
await _oracleDbContext.SaveChangesAsync();
return Ok(new { Codestatus = 200, message = "更新成功" });
}
[HttpGet("CheckBoxNo")]
public async Task<IActionResult> CheckBoxNo(string box_no)
{
//(EPP4-A021W591-Q1925050440|FGLABEL2|A021W591|||65||||B)
string PackLabel = box_no[1..^1];
string[] Packinfo = PackLabel.Split('|');
string Lineinfo = string.Empty;
string PartnoInfo = string.Empty;
var response = new
{
Code = 200,
Msg = string.Empty
};
if (Packinfo.Length <= 9)
{
response = new
{
Code = 200,
Msg = "BarCode not math Code Pattern"
};
return Ok(response);
}
else
{
string[] PackNoInfo = Packinfo[0].Split('-');
if (PackNoInfo.Length <= 2)
{
response = new
{
Code = 200,
Msg = "BarCode not math Code Pattern"
};
return Ok(response);
}
else
{
string line = PackNoInfo[0];
string partno = PackNoInfo[1];
string PackID = PackNoInfo[2];
var config = _oracleDbContext.API_SERVER_DB.Where(c => c.ACC_LINE == line).FirstOrDefault();
if (config == null)
{
response = new
{
Code = 200,
Msg = "No Mach Line conn date"
};
return Ok(response);
}
_connectionString = config.CONN_STR;
var helper = new OracleHelper(_connectionString);
var sql = "select tag_value from acc_pack_tag where packid = :packid";
var parameters = new Dictionary<string, object>
{
{ "packid", PackID } // 不需要加引号
};
var result = await helper.ExecuteDataTableAsync(sql, parameters);
if (result != null && result.Rows.Count > 0)
{
var tagValue = result.Rows[0]["tag_value"].ToString();
Console.WriteLine($"Tag Value: {tagValue}");
if (tagValue.Equals(box_no))
{
response = new
{
Code = 100,
Msg = "OK"
};
return Ok(response);
}
else
{
response = new
{
Code = 200,
Msg = "Label info not match"
};
return Ok(response);
}
}
else
{
Console.WriteLine("No result found or tag_value not present.");
response = new
{
Code = 200,
Msg = "No result found or tag_value not present."
};
return Ok(response);
}
}
}
}
[HttpGet("GetACCFGData")]
public async Task<IActionResult> GetACCFGData(string Line, string Time)
{
var config = _oracleDbContext.API_SERVER_DB.Where(c => c.NEW_NAME == Line).FirstOrDefault();
if (config == null)
{
var response = new
{
Code = 400,
Message = "No Mach Line conn date"
};
return Ok(response);
}
logger.Info("Have targetDbContext: " + config.CONN_STR);
_connectionString = config.CONN_STR;
string line = config.ACC_LINE;
int shiftStart = 8;
var helper = new OracleHelper(_connectionString);
// 使用 SQL 查询获取数据
//var sqlQuery = "SELECT Id, Name FROM SomeTable WHERE SomeCondition = @param";
StringBuilder stringQuery = new StringBuilder();
stringQuery.Append("SELECT to_number(to_char(DT,'FMHH24')) period,PARTNO, COUNT(*) as Total FROM acc_unitstatus ");
stringQuery.Append(string.Format("WHERE Status = '2' AND line = '{0}' AND result = '1' ", line));
stringQuery.Append(string.Format("AND trunc(DT - 1 / 24 * {0}) = to_date('{1}', 'YYYY-MM-DD') ", shiftStart, Time));
stringQuery.Append("GROUP BY PARTNO, to_number(to_char(DT,'FMHH24')) ");
stringQuery.Append("ORDER BY PARTNO, to_number(to_char(DT,'FMHH24')) ");
var Oracledata = await helper.ExecuteQueryAsync(stringQuery.ToString());
logger.Info("Have Oracledata row Count = " + Oracledata.Count);
if (Oracledata.Count > 0)
{
return Ok(Oracledata);
}
else
{
var response = new
{
Code = 400,
Message = "Not Found Data"
};
return Ok(response);
}
}
}
}
本文介绍了一种在网页上操作表格的方法，包括如何在表格中添加一行并填充单元格内容，以及如何删除指定的表格行。这些操作通过JavaScript实现，适用于需要动态更新表格数据的应用场景。
扫一扫
1928
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
