using Bro.Common.Helper;
|
using Bro.M071.Model;
|
using Bro.M071.Model.Model;
|
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.SQLite;
|
using System.Linq;
|
using System.Text;
|
|
namespace Bro.M071.DBManager
|
{
|
public class MeasurementUnitResultManager : ModelManager<MeasurementUnitResult>
|
{
|
//DbProviderFactory Dbfactory = SQLiteFactory.Instance;
|
protected override void ActionBeforeNewModel(DBModel context, MeasurementUnitResult config)
|
{
|
CheckProductionCodeDuplicate(context, config);
|
}
|
|
protected override void ActionBeforeUpdateModel(DBModel context, MeasurementUnitResult config)
|
{
|
CheckProductionCodeDuplicate(context, config);
|
}
|
|
private void CheckProductionCodeDuplicate(DBModel context, MeasurementUnitResult config)
|
{
|
}
|
|
public List<MeasurementUnitResult_DTO> GetMeasurementUnitResult(MeasurementUnitResultRequest request)
|
{
|
using (DBModel context = new DBModel())
|
{
|
var mList = context.MeasurementUnitResult.Where(u => u.IS_DELETED == 0);
|
var pList = context.ProductionMeasurementRecords.Where(u => u.IS_DELETED == 0);
|
|
if (!string.IsNullOrWhiteSpace(request.ProductionBarcode))
|
{
|
pList = pList.Where(u => u.ProductionBarcode.Contains(request.ProductionBarcode));
|
}
|
if (!string.IsNullOrWhiteSpace(request.ProductionCode))
|
{
|
pList = pList.Where(u => u.ProductionCode.Contains(request.ProductionCode));
|
}
|
if (request.StartTime != null)
|
{
|
pList = pList.Where(u => u.OperationStartTime >= request.StartTime);
|
}
|
if (request.EndTime != null)
|
{
|
pList = pList.Where(u => u.OperationStartTime <= request.EndTime);
|
}
|
//mList
|
if (!string.IsNullOrWhiteSpace(request.MeasurementName))
|
{
|
mList = mList.Where(u => u.MeasurementName.Contains(request.MeasurementName));
|
}
|
if (!string.IsNullOrWhiteSpace(request.MeasurementResult))
|
{
|
mList = mList.Where(u => u.MeasurementResult == request.MeasurementResult);
|
}
|
if (!string.IsNullOrWhiteSpace(request.MeasurementType))
|
{
|
mList = mList.Where(u => u.MeasurementType == request.MeasurementType);
|
}
|
|
var query = from productionMeasurementRecords in pList
|
join measurementUnitResult in mList
|
on productionMeasurementRecords.ID equals measurementUnitResult.ProductionMeasurementRecordsId
|
//into pmList
|
select new MeasurementUnitResult_DTO
|
{
|
ProductionCode = productionMeasurementRecords.ProductionCode,
|
ProductionBarcode = productionMeasurementRecords.ProductionBarcode,
|
ProductionMeasurementRecordsId = productionMeasurementRecords.ID,
|
OperationStartTime = productionMeasurementRecords.OperationStartTime,
|
OperationEndTime = productionMeasurementRecords.OperationEndTime,
|
|
Id = measurementUnitResult.ID,
|
MeasurementName = measurementUnitResult.MeasurementName,
|
MeasurementType = measurementUnitResult.MeasurementType,
|
MeasurementValue = measurementUnitResult.MeasurementValue,
|
MeasurementResult = measurementUnitResult.MeasurementResult
|
};
|
var pageList = query.OrderBy(u => u.OperationStartTime).ToPagedList(request);
|
request.TotalNum = mList.Count();
|
return pageList;
|
}
|
}
|
|
|
public ProductionMeasurementUnitResultExportDataSet GetProductionMeasurementUnitResultExportDataSet(MeasurementUnitResultRequest request)
|
{
|
ProductionMeasurementUnitResultExportDataSet productionMeasurementUnitResultExportDataSet = new ProductionMeasurementUnitResultExportDataSet();
|
using (DBModel context = new DBModel())
|
{
|
var mList = context.MeasurementUnitResult.Where(u => u.IS_DELETED == 0);
|
var pList = context.ProductionMeasurementRecords.Where(u => u.IS_DELETED == 0);
|
var kList = context.KeyUnitData.Where(u => u.IS_DELETED == 0);
|
|
if (!string.IsNullOrWhiteSpace(request.ProductionBarcode))
|
{
|
pList = pList.Where(u => u.ProductionBarcode.Contains(request.ProductionBarcode));
|
}
|
if (!string.IsNullOrWhiteSpace(request.ProductionCode))
|
{
|
pList = pList.Where(u => u.ProductionCode.Contains(request.ProductionCode));
|
}
|
if (request.StartTime != null)
|
{
|
pList = pList.Where(u => u.OperationStartTime >= request.StartTime);
|
}
|
if (request.EndTime != null)
|
{
|
pList = pList.Where(u => u.OperationStartTime <= request.EndTime);
|
}
|
|
//mList
|
if (!string.IsNullOrWhiteSpace(request.MeasurementName))
|
{
|
mList = mList.Where(u => u.MeasurementName.Contains(request.MeasurementName));
|
}
|
if (!string.IsNullOrWhiteSpace(request.MeasurementResult))
|
{
|
mList = mList.Where(u => u.MeasurementResult == request.MeasurementResult);
|
}
|
if (!string.IsNullOrWhiteSpace(request.MeasurementType))
|
{
|
mList = mList.Where(u => u.MeasurementType == request.MeasurementType);
|
}
|
|
productionMeasurementUnitResultExportDataSet.ProductionMeasurementRecordList = pList.ToList();
|
var measurementUnitResults = from productionMeasurementRecords in pList
|
join measurementUnitResult in mList on productionMeasurementRecords.ID equals measurementUnitResult.ProductionMeasurementRecordsId
|
select new MeasurementUnitResult_DTO
|
{
|
ProductionCode = productionMeasurementRecords.ProductionCode,
|
ProductionBarcode = productionMeasurementRecords.ProductionBarcode,
|
ProductionMeasurementRecordsId = productionMeasurementRecords.ID,
|
OperationStartTime = productionMeasurementRecords.OperationStartTime,
|
OperationEndTime = productionMeasurementRecords.OperationEndTime,
|
|
Id = measurementUnitResult.ID,
|
MeasurementName = measurementUnitResult.MeasurementName,
|
MeasurementType = measurementUnitResult.MeasurementType,
|
MeasurementValue = measurementUnitResult.MeasurementValue,
|
MeasurementResult = measurementUnitResult.MeasurementResult
|
};
|
|
productionMeasurementUnitResultExportDataSet.MeasurementUnitResultList = measurementUnitResults.ToList();
|
|
var keyUnitDataQuery = from productionMeasurementRecords in pList
|
join keyUnitData in kList on productionMeasurementRecords.ID equals
|
keyUnitData.ProductionMeasurementRecordsId
|
select new KeyUnitData_DTO
|
{
|
ProductionCode = productionMeasurementRecords.ProductionCode,
|
ProductionBarcode = productionMeasurementRecords.ProductionBarcode,
|
ProductionMeasurementRecordsId = productionMeasurementRecords.ID,
|
|
Id = keyUnitData.ID,
|
Key = keyUnitData.Key,
|
MeasurementItem = keyUnitData.MeasurementItem,
|
ItemValue = keyUnitData.ItemValue,
|
};
|
productionMeasurementUnitResultExportDataSet.KeyUnitDataList = keyUnitDataQuery.ToList();
|
|
return productionMeasurementUnitResultExportDataSet;
|
}
|
}
|
public void BatchAddMeasurementUnitResult(List<MeasurementUnitResult> records)
|
{
|
try
|
{
|
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
|
{
|
using (SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
|
{
|
StringBuilder strSql = new StringBuilder();
|
strSql.Append("insert into MeasurementUnitResult (");
|
strSql.Append("ID,ProductionMeasurementRecordsId,MeasurementName,MeasurementType,MeasurementValue,MeasurementResult,IS_DISABLED,IS_DELETED,CREATE_USER,CREATE_TIME, UPDATE_USER,UPDATE_TIME");
|
strSql.Append(")values (");
|
strSql.Append("@ID,@ProductionMeasurementRecordsId,@MeasurementName,@MeasurementType,@MeasurementValue,@MeasurementResult,@IS_DISABLED,@IS_DELETED,@CREATE_USER,@CREATE_TIME,@UPDATE_USER,@UPDATE_TIME)");
|
|
insertRngCmd.CommandText = strSql.ToString();
|
conn.Open();
|
var transaction = conn.BeginTransaction();
|
|
foreach (var record in records)
|
{
|
insertRngCmd.Parameters.AddWithValue("@ID", record.ID);
|
insertRngCmd.Parameters.AddWithValue("@ProductionMeasurementRecordsId", record.ProductionMeasurementRecordsId);
|
insertRngCmd.Parameters.AddWithValue("@MeasurementName", record.MeasurementName);
|
insertRngCmd.Parameters.AddWithValue("@MeasurementType", record.MeasurementType);
|
insertRngCmd.Parameters.AddWithValue("@MeasurementValue", record.MeasurementValue);
|
insertRngCmd.Parameters.AddWithValue("@MeasurementResult", record.MeasurementResult);
|
insertRngCmd.Parameters.AddWithValue("@IS_DISABLED", record.IS_DISABLED);
|
insertRngCmd.Parameters.AddWithValue("@IS_DELETED", record.IS_DELETED);
|
insertRngCmd.Parameters.AddWithValue("@CREATE_USER", record.CREATE_USER);
|
insertRngCmd.Parameters.AddWithValue("@CREATE_TIME", record.CREATE_TIME);
|
insertRngCmd.Parameters.AddWithValue("@UPDATE_USER", record.UPDATE_USER);
|
insertRngCmd.Parameters.AddWithValue("@UPDATE_TIME", record.UPDATE_TIME);
|
insertRngCmd.ExecuteNonQuery();
|
}
|
transaction.Commit();
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
throw new ProcessException(ex.Message, null);
|
}
|
}
|
}
|
}
|
