731 lines
27 KiB
C#
731 lines
27 KiB
C#
|
using JinYuan.Helper;
|
||
|
|
using System;
|
||
|
|
using System.Collections;
|
||
|
|
using System.Collections.Generic;
|
||
|
|
using System.Data;
|
||
|
|
using System.Linq;
|
||
|
|
using System.Text;
|
||
|
|
using System.Threading.Tasks;
|
||
|
|
|
||
|
|
namespace JinYuan.VirtualDataLibrary
|
||
|
|
{
|
||
|
|
public class DBSQL
|
||
|
|
{
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 获取PLC换型参数绑定值
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="modelName"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetPlcParamSql(string modelName)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
'{modelName}' AS `ModelName`,
|
||
|
|
`pb`.`PLCAddress`,
|
||
|
|
`pc`.`PLCValue`,
|
||
|
|
`pc`.`UpdateTime`,
|
||
|
|
`pb`.`PLCRemark`,
|
||
|
|
`pb`.`OrderNum`
|
||
|
|
FROM
|
||
|
|
`Plcconfigbase` `pb`
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT * FROM `Plcconfig` WHERE `ModelName` = '{modelName}') `pc`
|
||
|
|
ON
|
||
|
|
`pb`.`PLCAddress` = `pc`.`PLCAddress`
|
||
|
|
ORDER BY
|
||
|
|
`pb`.`OrderNum`";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 获取产品型号参数
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strModelType"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetParamSql(string strModelType)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
`pb`.`ParaName`,
|
||
|
|
'{strModelType}' AS `ModelName`,
|
||
|
|
`pl`.`ParaValueMin`,
|
||
|
|
`pl`.`ParaValueMax`,
|
||
|
|
`pl`.`Uint`,
|
||
|
|
`pb`.`Remark`,
|
||
|
|
`pl`.`UpdateTime`
|
||
|
|
FROM
|
||
|
|
`Parambase` `pb`
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT * FROM `ParamList` WHERE `ModelName` = '{strModelType}') `pl`
|
||
|
|
ON
|
||
|
|
`pb`.`ParaName` = `pl`.`ParaName`
|
||
|
|
ORDER BY
|
||
|
|
`pb`.`fNo` ASC";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 获取产品型号列表
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strProdType"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetProdTypeSql(string strProdType = "")
|
||
|
|
{
|
||
|
|
string strSql = @"SELECT * FROM ProductModel ";
|
||
|
|
if (strProdType != "")
|
||
|
|
{
|
||
|
|
strSql += string.Format(" where ModelName='{0}'", strProdType);
|
||
|
|
}
|
||
|
|
strSql += " order by ModelName";
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 获取产品型号
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strModelType"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetProductParaByProdModel(string strModelType)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
`pb`.`ParaName`,
|
||
|
|
'{strModelType}' AS `ModelName`,
|
||
|
|
`pl`.`ParaValueMin`,
|
||
|
|
`pl`.`ParaValueMax`,
|
||
|
|
`pl`.`Uint`,
|
||
|
|
`pb`.`Remark`,
|
||
|
|
`pl`.`UpdateTime`
|
||
|
|
FROM
|
||
|
|
`Parambase` `pb`
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT * FROM `Paramlist` WHERE `ModelName` = '{strModelType}') `pl`
|
||
|
|
ON
|
||
|
|
`pb`.`ParaName` = `pl`.`ParaName`
|
||
|
|
ORDER BY
|
||
|
|
`pb`.`fNo` ASC";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
public string GetPLCConfigBases()
|
||
|
|
{
|
||
|
|
string strSql = string.Format(@"SELECT * FROM `Plcconfigbase` ORDER BY `OrderNum`");
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
public string GetSysAdminSql()
|
||
|
|
{
|
||
|
|
string strSql = string.Format(@"SELECT
|
||
|
|
`LoginId`,
|
||
|
|
`LoginName`,
|
||
|
|
`LoginPwd`,
|
||
|
|
`LoginUid`,
|
||
|
|
`LoginLevel`,
|
||
|
|
`SystemLog`,
|
||
|
|
`CpkData`,
|
||
|
|
`HistoryData`,
|
||
|
|
`ParamSet`,
|
||
|
|
`SystemSet`,
|
||
|
|
`UserManage`
|
||
|
|
FROM
|
||
|
|
`SysAdmin`");
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 查询历史数据
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strBarCode"></param>
|
||
|
|
/// <param name="strDate1"></param>
|
||
|
|
/// <param name="strDate2"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string SelectHostData(int type, string strRfid, string strBarCode, string strDate1, string strDate2)
|
||
|
|
{
|
||
|
|
Dictionary<int, int> outTypes = new Dictionary<int, int>()
|
||
|
|
{
|
||
|
|
{ 3, 1 },
|
||
|
|
{ 4, 2 }
|
||
|
|
};
|
||
|
|
string strSql = string.Empty;
|
||
|
|
if (type == 0)
|
||
|
|
{
|
||
|
|
strSql = @"SELECT
|
||
|
|
DATE_FORMAT(`Time`, '%Y-%m-%d %H:%i:%s') AS `TestTime`,
|
||
|
|
`BarCode`,
|
||
|
|
`Location` AS `CellPosition`,
|
||
|
|
`TrayID` AS `RFID`,
|
||
|
|
`Model`,
|
||
|
|
`Grade` AS `CellGrade`,
|
||
|
|
`Gongwei`,
|
||
|
|
`Result`,
|
||
|
|
`Remark`
|
||
|
|
FROM
|
||
|
|
`AGearEntity`
|
||
|
|
WHERE
|
||
|
|
`Time` >= '" + strDate1 + "' AND `Time` <= '" + strDate2 + "'";
|
||
|
|
|
||
|
|
// 2. 安全拼接查询条件：参数化+CONCAT函数
|
||
|
|
if (!string.IsNullOrEmpty(strRfid))
|
||
|
|
{
|
||
|
|
strSql += " AND `TrayID` LIKE '%" + strRfid + "%'";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!string.IsNullOrEmpty(strBarCode))
|
||
|
|
{
|
||
|
|
strSql += " AND `BarCode` LIKE '%" + strBarCode + "%'";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
else if (type == 1) //装箱或者强制出站
|
||
|
|
{
|
||
|
|
strSql = @"SELECT
|
||
|
|
`identification` AS `BarCode`,
|
||
|
|
DATE_FORMAT(`packTime`, '%Y-%m-%d %H:%i:%s') AS `TestTime`,
|
||
|
|
`location` AS `CellPosition`,
|
||
|
|
`grade` AS `CellGrade`,
|
||
|
|
`containerCode` AS `ContainerCode`,
|
||
|
|
`packResult` AS `PackResult`,
|
||
|
|
`packFalseReason` AS `PackFailReason`,
|
||
|
|
CASE
|
||
|
|
WHEN `outStationType` = 1 THEN '满箱出站'
|
||
|
|
ELSE '强制出站'
|
||
|
|
END AS `OutStationType`
|
||
|
|
FROM
|
||
|
|
`BatteryEntity`
|
||
|
|
WHERE
|
||
|
|
`packTime` >= '" + strDate1 + "' and `packTime` <= '" + strDate2 + "' ";
|
||
|
|
|
||
|
|
|
||
|
|
// 安全拼接查询条件：参数化+CONCAT函数，
|
||
|
|
if (!string.IsNullOrEmpty(strRfid))
|
||
|
|
{
|
||
|
|
strSql += " AND `containerCode` LIKE '%" + strRfid + "%'";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!string.IsNullOrEmpty(strBarCode))
|
||
|
|
{
|
||
|
|
strSql += " AND `identification` LIKE '%" + strBarCode + "%'";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
else if (type == 2)//电芯替换
|
||
|
|
{
|
||
|
|
strSql = @"SELECT
|
||
|
|
DATE_FORMAT(`ReplaceTime`, '%Y-%m-%d %H:%i:%s') AS `TestTime`,
|
||
|
|
`NewBarCode`,
|
||
|
|
`OldBarCode`,
|
||
|
|
`Location` AS `CellPosition`,
|
||
|
|
`VassoioID` AS `ContainerCode`,
|
||
|
|
`GongWei` AS `Gongwei`,
|
||
|
|
`Grade` AS `CellGrade`,
|
||
|
|
`Remark`
|
||
|
|
FROM
|
||
|
|
`BatteryReplace`
|
||
|
|
WHERE
|
||
|
|
`ReplaceTime` >= '" + strDate1 + "' and `ReplaceTime` <= '" + strDate2 + "' ";
|
||
|
|
|
||
|
|
if (!string.IsNullOrEmpty(strRfid))
|
||
|
|
{
|
||
|
|
strSql += " AND `VassoioID` LIKE '%" + strRfid + "%'";
|
||
|
|
}
|
||
|
|
|
||
|
|
if (!string.IsNullOrEmpty(strBarCode))
|
||
|
|
{
|
||
|
|
string safeRfid = strRfid?.Replace("'", "''") ?? "";
|
||
|
|
string safeBarCode = strBarCode?.Replace("'", "''") ?? "";
|
||
|
|
|
||
|
|
|
||
|
|
strSql += $" AND (`NewBarCode` LIKE '%{safeRfid}%' OR `OldBarCode` LIKE '%{safeBarCode}%')";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
else
|
||
|
|
{
|
||
|
|
LogHelper.Instance.WriteError($"不支持的查询类型：{type}", "DataErrorLog");
|
||
|
|
}
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 查询报警历史数据
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="start"></param>
|
||
|
|
/// <param name="end"></param>
|
||
|
|
/// <param name="alarmType"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string SelectHostAlarmData(string start, string end, int alarmType)
|
||
|
|
{
|
||
|
|
string strSql = "";
|
||
|
|
switch (alarmType)
|
||
|
|
{
|
||
|
|
case 1:
|
||
|
|
strSql = @"SELECT
|
||
|
|
DATE_FORMAT(`AlarmTime`, '%Y-%m-%d %H:%i:%s') AS `AlarmTime`,
|
||
|
|
`PLCAdress`,
|
||
|
|
`AlarmContent`,
|
||
|
|
`AlarmCount`,
|
||
|
|
`Class`
|
||
|
|
FROM
|
||
|
|
`AlarmCountEntry`
|
||
|
|
WHERE
|
||
|
|
`AlarmTime` >= '" + start + "' AND `AlarmTime` <= '" + end + $"' AND `Class` = '{CommonMethods.strClass}'";
|
||
|
|
break;
|
||
|
|
case 2:
|
||
|
|
strSql = @"SELECT
|
||
|
|
DATE_FORMAT(`AlarmTime`, '%Y-%m-%d %H:%i:%s') AS `AlarmTime`,
|
||
|
|
`PLCAdress`,
|
||
|
|
`AlarmContent`,
|
||
|
|
`AlarmCount`,
|
||
|
|
`Class`
|
||
|
|
FROM
|
||
|
|
`AlarmCountEntry`
|
||
|
|
WHERE
|
||
|
|
`AlarmTime` >= '" + start + "' AND `AlarmTime` <= '" + end + $"' AND `Class` = '{CommonMethods.strClass}'";
|
||
|
|
break;
|
||
|
|
default:
|
||
|
|
case 0:
|
||
|
|
strSql = @"SELECT
|
||
|
|
DATE_FORMAT(`AlarmTime`, '%Y-%m-%d %H:%i:%s') AS `AlarmTime`,
|
||
|
|
`PLCAdress`,
|
||
|
|
`AlarmContent`,
|
||
|
|
`AlarmCount`,
|
||
|
|
`Class`
|
||
|
|
FROM
|
||
|
|
`AlarmCountEntry`
|
||
|
|
WHERE
|
||
|
|
`AlarmTime` >= '" + start + "' AND `AlarmTime` <= '" + end + "'";
|
||
|
|
break;
|
||
|
|
}
|
||
|
|
#region 旧
|
||
|
|
//if (alarmType == "全天")
|
||
|
|
//{
|
||
|
|
// strSql = @"SELECT
|
||
|
|
// DATE_FORMAT(`AlarmTime`, '%Y-%m-%d %H:%i:%s') AS `AlarmTime`,
|
||
|
|
// `PLCAdress`,
|
||
|
|
// `AlarmContent`,
|
||
|
|
// `AlarmCount`,
|
||
|
|
// `Class`
|
||
|
|
// FROM
|
||
|
|
// `AlarmCountEntry`
|
||
|
|
// WHERE
|
||
|
|
// `AlarmTime` >= '" + start + "' AND `AlarmTime` <= '" + end + "'";
|
||
|
|
//}
|
||
|
|
//else if (alarmType == "白班")
|
||
|
|
//{
|
||
|
|
// strSql = @"SELECT
|
||
|
|
// DATE_FORMAT(`AlarmTime`, '%Y-%m-%d %H:%i:%s') AS `AlarmTime`,
|
||
|
|
// `PLCAdress`,
|
||
|
|
// `AlarmContent`,
|
||
|
|
// `AlarmCount`,
|
||
|
|
// `Class`
|
||
|
|
// FROM
|
||
|
|
// `AlarmCountEntry`
|
||
|
|
// WHERE
|
||
|
|
// `AlarmTime` >= '" + start + "' AND `AlarmTime` <= '" + end + "' AND `Class` = '白班'";
|
||
|
|
//}
|
||
|
|
//else
|
||
|
|
//{
|
||
|
|
// strSql = @"SELECT
|
||
|
|
// DATE_FORMAT(`AlarmTime`, '%Y-%m-%d %H:%i:%s') AS `AlarmTime`,
|
||
|
|
// `PLCAdress`,
|
||
|
|
// `AlarmContent`,
|
||
|
|
// `AlarmCount`,
|
||
|
|
// `Class`
|
||
|
|
// FROM
|
||
|
|
// `AlarmCountEntry`
|
||
|
|
// WHERE
|
||
|
|
// `AlarmTime` >= '" + start + "' AND `AlarmTime` <= '" + end + "' AND `Class` = '夜班'";
|
||
|
|
//}
|
||
|
|
#endregion
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 按日统计
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="startDateTime"></param>
|
||
|
|
/// <param name="endDateTime"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetStaticByDay(string startDateTime, string endDateTime)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
DATE_FORMAT(`TestTime`, '%Y-%m-%d %H:00:00') AS `Day`,
|
||
|
|
`ProdIn`,
|
||
|
|
`ProdOut`,
|
||
|
|
`Ppm`,
|
||
|
|
CONCAT(CAST(ROUND(`JiaDL` * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN `ProdIn` > 0 THEN ROUND(COALESCE(`ProdOut`, 0) / COALESCE(`ProdIn`, 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{startDateTime}' AND '{endDateTime}'
|
||
|
|
GROUP BY
|
||
|
|
`TestTime`, `ProdOut`, `ProdIn`, `Ppm`, `JiaDL`
|
||
|
|
UNION ALL
|
||
|
|
SELECT
|
||
|
|
'Total' AS `Day`,
|
||
|
|
SUM(`ProdIn`) AS ProdIn,
|
||
|
|
SUM(`ProdOut`) AS ProdOut,
|
||
|
|
AVG(`Ppm`) AS Ppm,
|
||
|
|
CONCAT(CAST(ROUND(AVG(`JiaDL`) * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN SUM(`ProdIn`) > 0 THEN ROUND(COALESCE(SUM(`ProdOut`), 0) / COALESCE(SUM(`ProdIn`), 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{startDateTime}' AND '{endDateTime}'";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 按周查询
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="startDateTime"></param>
|
||
|
|
/// <param name="endDateTime"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetStaticByWeek(string startDateTime, string endDateTime)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
DATE_FORMAT(`TestTime`, '%Y-%m') AS `Week`,
|
||
|
|
SUM(`ProdIn`) AS ProdIn,
|
||
|
|
SUM(`ProdOut`) AS ProdOut,
|
||
|
|
AVG(`Ppm`) AS Ppm,
|
||
|
|
CONCAT(CAST(ROUND(`JiaDL` * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN SUM(`ProdIn`) > 0 THEN ROUND(COALESCE(SUM(`ProdOut`), 0) / COALESCE(SUM(`ProdIn`), 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{startDateTime}' AND '{endDateTime}'
|
||
|
|
GROUP BY
|
||
|
|
DATE_FORMAT(`TestTime`, '%Y-%m'), `JiaDL`
|
||
|
|
UNION ALL
|
||
|
|
SELECT
|
||
|
|
'Total' AS `Week`,
|
||
|
|
SUM(`ProdIn`) AS ProdIn,
|
||
|
|
SUM(`ProdOut`) AS ProdOut,
|
||
|
|
AVG(`Ppm`) AS Ppm,
|
||
|
|
CONCAT(CAST(ROUND(AVG(`JiaDL`) * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN SUM(`ProdIn`) > 0 THEN ROUND(COALESCE(SUM(`ProdOut`), 0) / COALESCE(SUM(`ProdIn`), 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{startDateTime}' AND '{endDateTime}'";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 按月查询
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="startDateTime"></param>
|
||
|
|
/// <param name="endDateTime"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetStaticByMonth(string startDateTime, string endDateTime)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
DATE_FORMAT(`TestTime`, '%Y-%m') AS `Month`,
|
||
|
|
SUM(`ProdIn`) AS ProdIn,
|
||
|
|
SUM(`ProdOut`) AS ProdOut,
|
||
|
|
AVG(`Ppm`) AS Ppm,
|
||
|
|
CONCAT(CAST(ROUND(`JiaDL` * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN SUM(`ProdIn`) > 0 THEN ROUND(COALESCE(SUM(`ProdOut`), 0) / COALESCE(SUM(`ProdIn`), 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{startDateTime}' AND '{endDateTime}'
|
||
|
|
GROUP BY
|
||
|
|
DATE_FORMAT(`TestTime`, '%Y-%m'), `JiaDL`
|
||
|
|
UNION ALL
|
||
|
|
SELECT
|
||
|
|
'Total' AS `Month`,
|
||
|
|
SUM(`ProdIn`) AS ProdIn,
|
||
|
|
SUM(`ProdOut`) AS ProdOut,
|
||
|
|
AVG(`Ppm`) AS Ppm,
|
||
|
|
CONCAT(CAST(ROUND(AVG(`JiaDL`) * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN SUM(`ProdIn`) > 0 THEN ROUND(COALESCE(SUM(`ProdOut`), 0) / COALESCE(SUM(`ProdIn`), 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{startDateTime}' AND '{endDateTime}'";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 按班次统计
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="dateTime"></param>
|
||
|
|
/// <param name="shift"></param>
|
||
|
|
/// <param name="changeShiftTime"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetStaticByShift(DateTime dateTime, int shift, string changeShiftTime)
|
||
|
|
{
|
||
|
|
string sDateTime1 = string.Empty;
|
||
|
|
string sDateTime2 = string.Empty;
|
||
|
|
if (shift == 0)// 白班
|
||
|
|
{
|
||
|
|
DateTime sTempDateTime = Convert.ToDateTime($"{dateTime.ToString("yyyy-MM-dd")} {changeShiftTime}");
|
||
|
|
|
||
|
|
sDateTime1 = sTempDateTime.AddHours(-12).ToString("yyyy-MM-dd HH:mm:ss");
|
||
|
|
sDateTime2 = sTempDateTime.ToString("yyyy-MM-dd HH:mm:ss");
|
||
|
|
}
|
||
|
|
else
|
||
|
|
{
|
||
|
|
sDateTime1 = $"{dateTime.ToString("yyyy-MM-dd")} {changeShiftTime}";
|
||
|
|
sDateTime2 = Convert.ToDateTime(sDateTime1).AddHours(12).ToString("yyyy-MM-dd HH:mm:ss");
|
||
|
|
}
|
||
|
|
|
||
|
|
string strsql = $@"SELECT
|
||
|
|
DATE_FORMAT(`TestTime`, '%Y-%m-%d %H:00:00') AS `TestTime`,
|
||
|
|
`ProdIn`,
|
||
|
|
`ProdOut`,
|
||
|
|
`Ppm`,
|
||
|
|
CONCAT(CAST(ROUND(`JiaDL` * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN `ProdIn` > 0 THEN ROUND(COALESCE(`ProdOut`, 0) / COALESCE(`ProdIn`, 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{sDateTime1}' AND '{sDateTime2}'
|
||
|
|
GROUP BY
|
||
|
|
`TestTime`, `ProdOut`, `ProdIn`, `Ppm`, `JiaDL`
|
||
|
|
UNION ALL
|
||
|
|
SELECT
|
||
|
|
'Total' AS `Time`,
|
||
|
|
SUM(`ProdIn`) AS ProdIn,
|
||
|
|
SUM(`ProdOut`) AS ProdOut,
|
||
|
|
AVG(`Ppm`) AS Ppm,
|
||
|
|
CONCAT(CAST(ROUND(AVG(`JiaDL`) * 100, 1) AS CHAR(10)), '%') AS `JiaDL`,
|
||
|
|
CONCAT(CAST(
|
||
|
|
CASE
|
||
|
|
WHEN SUM(`ProdIn`) > 0 THEN ROUND(COALESCE(SUM(`ProdOut`), 0) / COALESCE(SUM(`ProdIn`), 0) * 100, 2)
|
||
|
|
ELSE 0
|
||
|
|
END AS CHAR(10)), '%') AS `Prod`
|
||
|
|
FROM
|
||
|
|
`Hourprod`
|
||
|
|
WHERE
|
||
|
|
`TestTime` BETWEEN '{sDateTime1}' AND '{sDateTime2}'";
|
||
|
|
|
||
|
|
return strsql;
|
||
|
|
}
|
||
|
|
/// <summary>
|
||
|
|
/// 获取进站信息
|
||
|
|
/// </summary>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetInTime(string strBarCode)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT TestTime
|
||
|
|
,BarCode
|
||
|
|
,Result
|
||
|
|
,Remark
|
||
|
|
FROM AGearEntity
|
||
|
|
WHERE BarCode = '{strBarCode}'
|
||
|
|
ORDER BY TestTime ASC";
|
||
|
|
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 获取最新的数据
|
||
|
|
/// </summary>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetStatic(string strDateTime)
|
||
|
|
{
|
||
|
|
|
||
|
|
string strsql = $@" SELECT [FDate]
|
||
|
|
,[FHour]
|
||
|
|
,[ProdIn]
|
||
|
|
,[ProdOut]
|
||
|
|
,[TestTime]
|
||
|
|
FROM [dbo].[Hourprod] where FDate='{strDateTime}' order by TestTime desc";
|
||
|
|
return strsql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 根据条码查询是否存在
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strProdType"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetBarcode(string barCode)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT EXISTS(
|
||
|
|
SELECT 1
|
||
|
|
FROM `CGearEntity`
|
||
|
|
WHERE BarCode = '{barCode}') AS barcode_exists";
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// 根据条码获取进站时间最新的一条数据
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strProdType"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetInStationTop1Sql(string barCode)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
`ChannelNo`,
|
||
|
|
`TestTime`,
|
||
|
|
`RFID`,
|
||
|
|
`BarCode`,
|
||
|
|
`Result`,
|
||
|
|
`Remark`,
|
||
|
|
`Flag`
|
||
|
|
FROM
|
||
|
|
`AGearEntity`
|
||
|
|
WHERE `BarCode` = '{barCode}' ORDER BY `TestTime` DESC
|
||
|
|
LIMIT 1";
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 获取表头注释 按照设计顺序
|
||
|
|
/// </summary>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetSqlFields(string tableName)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
COLUMN_NAME,
|
||
|
|
PLC.DataType,
|
||
|
|
COLUMN_COMMENT
|
||
|
|
FROM
|
||
|
|
INFORMATION_SCHEMA.COLUMNS
|
||
|
|
WHERE
|
||
|
|
TABLE_SCHEMA = 'largesquareone' AND TABLE_NAME = '{tableName}'
|
||
|
|
ORDER BY
|
||
|
|
ORDINAL_POSITION;";
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
/// <summary>
|
||
|
|
/// 根据档位获取当前档位中的所有电芯数据
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="grade"></param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public string GetGradeBatterySql(string grade)
|
||
|
|
{
|
||
|
|
string strSql = $@"SELECT
|
||
|
|
`identification`,
|
||
|
|
`locationBat`,
|
||
|
|
`grade`,
|
||
|
|
`innerContainerCode`,
|
||
|
|
`packResult`,
|
||
|
|
`packFalseReason`
|
||
|
|
FROM
|
||
|
|
`BatteryEntity`
|
||
|
|
WHERE `grade` = '{grade}' ORDER BY `TestTime` DESC";
|
||
|
|
return strSql;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
public class M_DBCtrl
|
||
|
|
{
|
||
|
|
/// <summary>--本机数据库表名
|
||
|
|
///
|
||
|
|
/// </summary>
|
||
|
|
//public string machineInSQLTable = "agearentity";
|
||
|
|
public string machineOutSQLTable = "bgearentity";
|
||
|
|
|
||
|
|
private string[] headerTextOut;
|
||
|
|
/// <summary>
|
||
|
|
/// 出站数据库表头
|
||
|
|
/// </summary>
|
||
|
|
public string[] HeaderTextOut
|
||
|
|
{
|
||
|
|
get { return headerTextOut; }
|
||
|
|
set { headerTextOut = value; }
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>查询SQL数据库中的数据,并以string数组列表形式返回
|
||
|
|
///
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="strSql"></param>
|
||
|
|
/// <returns>所查询到的数据，null表示不存在</returns>
|
||
|
|
public List<string[]> SQLAllDataSearchToStrArrList(string strSql)
|
||
|
|
{
|
||
|
|
List<string[]> strResArrList = new List<string[]>();
|
||
|
|
List<string> strResList = new List<string>();
|
||
|
|
try
|
||
|
|
{
|
||
|
|
DataTable dt = CommonMethods.db.QueryDataTable(strSql);
|
||
|
|
if (dt != null)
|
||
|
|
{
|
||
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
||
|
|
{
|
||
|
|
for (int j = 0; j < dt.Columns.Count; j++)
|
||
|
|
{
|
||
|
|
string resRow = dt.Rows[i][j].ToString();
|
||
|
|
strResList.Add(resRow);
|
||
|
|
}
|
||
|
|
string[] strResArr = strResList.ToArray();
|
||
|
|
strResList.Clear();
|
||
|
|
strResArrList.Add(strResArr);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
catch (Exception e)
|
||
|
|
{
|
||
|
|
return strResArrList = new List<string[]>();
|
||
|
|
}
|
||
|
|
return strResArrList;
|
||
|
|
}
|
||
|
|
|
||
|
|
/// <summary>根据下标在返回的数据库表信息 集合中获取相应数据
|
||
|
|
/// index 0:字段 2:中文注释 1:变量类型int\char等
|
||
|
|
/// </summary>
|
||
|
|
/// <param name="SqlList">liststring[]类型的集合</param>
|
||
|
|
/// <param name="index">string[] 的下标</param>
|
||
|
|
/// <returns></returns>
|
||
|
|
public String[] HeaderTextAssignment(List<string[]> SqlList, int index)
|
||
|
|
{
|
||
|
|
string[] headStringlist = new string[SqlList.Count];
|
||
|
|
// int i = 0;
|
||
|
|
try
|
||
|
|
{
|
||
|
|
for (int i = 0; i < SqlList.Count; i++)
|
||
|
|
{
|
||
|
|
headStringlist[i] = SqlList[i][index];
|
||
|
|
}
|
||
|
|
}
|
||
|
|
catch (Exception)
|
||
|
|
{
|
||
|
|
}
|
||
|
|
return headStringlist;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|