public DataTable GetDatas()
{
Query query = new Query("CementTypesAndConvertCoefficient");
DataTable dt = dataFactory.Query(query);
return dt;
}
public DataTable GetWorkingSection()
{
Query query = new Query("WorkingSection");
DataTable dt = dataFactory.Query(query);
return dt;
}
public TZ FindBy(Guid id)
{
Query query = new Query("TZ");
query.AddCriterion("KeyID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(query);
if (table.Rows.Count > 0)
{
TZ result = new TZ();
result.TableName = table.Rows[0]["TableName"].ToString().Trim();
result.KeyID = (Guid)table.Rows[0]["KeyID"];
result.ProductLineID = int.Parse(table.Rows[0]["ProductLineID"].ToString().Trim());
result.ReportID = int.Parse(table.Rows[0]["ReportID"].ToString().Trim());
result.Date = table.Rows[0]["Date"].ToString().Trim();
result.CreationDate = (DateTime)table.Rows[0]["CreationDate"];
result.ModifierID = table.Rows[0]["ModifierID"].ToString().Trim();
result.Version = (DateTime)table.Rows[0]["Version"];
result.ModifiedFlag = (bool)table.Rows[0]["ModifiedFlag"];
result.Remarks = table.Rows[0]["Remarks"].ToString().Trim();
return result;
}
else
{
return new TZ();
}
}
public DataTable GetKPI()
{
Query query = new Query("KPI");
DataTable dt = dataFactory.Query(query);
return dt;
}
public DataTable GetFormulaYearTable(Guid keyId, string tableName)
{
Query query = new Query(tableName);
query.AddCriterion("KeyID", keyId, CriteriaOperator.Equal);
DataTable dt = dataFactory.Query(query);
return dt;
}
public DataTable GetMachineHaltReason()
{
Query query = new Query("MachineHaltReason");
DataTable dt = dataFactory.Query(query);
return dt;
}
public DataTable GetWorkingTeam()
{
Query query = new Query("WorkingTeam");
DataTable dt = dataFactory.Query(query);
return dt;
}
/// <summary>
/// 获取员工信息
/// </summary>
/// <returns></returns>
public static DataTable GetStaffInfo()
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("StaffInfo");
return factory.Query(query);
}
/// <summary>
/// 获取停机记录
/// </summary>
/// <param name="productLineId"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public static DataTable GetHaltLog(int productLineId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("MachineHaltLog");
return factory.Query(query);
}
/// <summary>
/// 获取设备停机原因字典
/// </summary>
/// <returns></returns>
public static DataTable GetMachineHaltReason()
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("MachineHaltReason");
return factory.Query(query);
}
public static DataTable GetAmmetersByFactoryId(int factoryId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("AmmeterContrast");
return factory.Query(query);
}
public static DataTable GetProductLines()
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("ProductLine");
return factory.Query(query);
}
/// <summary>
/// 跟据截止时间获得TZ数据
/// </summary>
/// <param name="request"></param>
/// <returns></returns>
public TZResponse GetTZ(TZRequest request)
{
Query query = new Query("TZ");
if (request.ReportType == "1")
{
string startTime = request.StartTime.Split('-')[0];
string endTime = request.EndTime.Split('-')[0];
query.AddCriterion("ReportID", "ReportID",int.Parse(request.ReportName), CriteriaOperator.Equal);
query.AddCriterion("Date", "startDate", startTime, CriteriaOperator.MoreThanOrEqual);
query.AddCriterion("Date", "endDate", endTime, CriteriaOperator.LessThanOrEqual);
query.AddCriterion("Date", "____", CriteriaOperator.Like);
if (request.ModifiedFlag != "0")
{
bool modifiedFlag = bool.Parse(request.ModifiedFlag);
query.AddCriterion("ModifiedFlag","modifiedflag", modifiedFlag, CriteriaOperator.Equal);
}
}
else if (request.ReportType == "2")
{
string startTime = request.StartTime.Split('-')[0] + "-" + request.StartTime.Split('-')[1];
string endTime = request.EndTime.Split('-')[0] + "-" + request.EndTime.Split('-')[1];
query.AddCriterion("ReportID", "ReportID", int.Parse(request.ReportName), CriteriaOperator.Equal);
query.AddCriterion("Date", "startDate", startTime, CriteriaOperator.MoreThanOrEqual);
query.AddCriterion("Date", "endDate", endTime, CriteriaOperator.LessThanOrEqual);
query.AddCriterion("Date", "____-__", CriteriaOperator.Like);
if (request.ModifiedFlag != "0")
{
bool modifiedFlag = bool.Parse(request.ModifiedFlag);
query.AddCriterion("ModifiedFlag", "modifiedflag", modifiedFlag, CriteriaOperator.Equal);
}
}
else if (request.ReportType == "3")
{
string startTime = request.StartTime.Split('-')[0] + request.StartTime.Split('-')[1] + request.StartTime.Split('-')[2];
string endTime = request.EndTime.Split('-')[0] + request.EndTime.Split('-')[1] + request.EndTime.Split('-')[2];
query.AddCriterion("ReportID", "ReportID", int.Parse(request.ReportName), CriteriaOperator.Equal);
query.AddCriterion("Date", "startDate", startTime, CriteriaOperator.MoreThanOrEqual);
query.AddCriterion("Date", "endDate", endTime, CriteriaOperator.LessThanOrEqual);
query.AddCriterion("Date", "____-__-__", CriteriaOperator.Like);
if (request.ModifiedFlag != "0")
{
bool modifiedFlag = bool.Parse(request.ModifiedFlag);
query.AddCriterion("ModifiedFlag","modifiedflag", modifiedFlag, CriteriaOperator.Equal);
}
}
else
{
throw new Exception("没有匹配的报表类型");
}
IEnumerable<TZ> tz = tzRepository.FindBy(query);
return new TZResponse
{
TZViews = tz.ConvertToViews(),
Success = true
};
}
/// <summary>
/// 按生产线ID获取所电耗报警设置
/// </summary>
/// <param name="productLineId"></param>
/// <returns></returns>
public static DataTable GetElectricityConsumptionAlarmSettingsByFactoryId(int productLineId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("ElectricityConsumptionAlarmParameterSetting");
query.AddCriterion("ProductLineID", productLineId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
return factory.Query(query);
}
/// <summary>
/// 按起始时间段获取停机记录
/// </summary>
/// <param name="productLineId"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public static DataTable GetHaltLog(int productLineId, DateTime start)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("MachineHaltLog");
query.AddCriterion("HaltTime", start, SqlServerDataAdapter.Infrastruction.CriteriaOperator.MoreThanOrEqual);
return factory.Query(query);
}
/// <summary>
/// 按生产线ID获取班组
/// </summary>
/// <param name="productLineId"></param>
/// <returns></returns>
public static DataTable GetWorkingTeamByProductLineId(int productLineId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("WorkingTeam");
query.AddCriterion("ProductLineID", productLineId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
return factory.Query(query);
}
/// <summary>
/// 按分厂ID获取所有公式组
/// </summary>
/// <param name="factoryId"></param>
/// <returns></returns>
public static DataTable GetFormulaGroupsByFactoryId(int factoryId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("FormulaGroup");
query.AddCriterion("FactoryID", factoryId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
return factory.Query(query);
}
public ReportResponse GetRepoersByType(ReportRequest request)
{
ReportResponse response = new ReportResponse();
Query query = new Query("Report");
query.AddCriterion("Type", request.ReportType, CriteriaOperator.Equal);
IEnumerable<Report> reports = reportRepository.FindBy(query);
response.Reports = reports;
return response;
}
private void InitializeDictionary()
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("ViewsDictionary");
DataTable dt = factory.Query(query);
foreach (DataRow row in dt.Rows)
dictionary.Add(row["English"].ToString(), row["Chinese"].ToString());
}
public Report FindBy(int id)
{
Query query = new Query("Report");
query.AddCriterion("ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(query);
return new Report
{
Id = int.Parse(table.Rows[0]["ID"].ToString().Trim()),
Name = table.Rows[0]["Name"].ToString().Trim(),
Type = (NXJC.Model.ReportForm.ReportType)table.Rows[0]["Type"],
Remarks = table.Rows[0]["Remarks"].ToString().Trim()
};
}
public IEnumerable<ProductLine> FindBy(Query query)
{
IList<ProductLine> results = new List<ProductLine>();
DataTable table = dataFactory.Query(query);
foreach(DataRow row in table.Rows)
{
results.Add(new ProductLine{
Address = row["Address"].ToString().Trim(),
Id = int.Parse(row["ID"].ToString().Trim()),
Name = row["Name"].ToString().Trim(),
Remarks = row["Remarks"].ToString().Trim()
});
}
return results;
}
public ProductLine FindBy(int id)
{
Query query = new Query("ProductLine");
query.AddCriterion("ID", id, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(query);
ProductLine result = new ProductLine
{
Id = int.Parse(table.Rows[0]["ID"].ToString().Trim()),
Address = table.Rows[0]["Address"].ToString().Trim(),
Name = table.Rows[0]["Name"].ToString().Trim(),
Remarks = table.Rows[0]["Remarks"].ToString().Trim(),
};
return result;
}
/// <summary>
/// 按生产线ID获取标签
/// </summary>
/// <param name="productLineId"></param>
/// <returns></returns>
public static DataTable GetLabelsByProductLineId(int productLineId)
{
string connectionString = ConnectionStringFactory.GetByProductLineId(productLineId);
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("ContrastTable");
DataTable dt = factory.Query(query);
foreach (DataRow row in dt.Rows)
{
row["ViewName"] = ViewsDictionary.Current.GetChineseSafely(row["ViewName"].ToString().Trim());
}
return dt;
}
public IEnumerable<ReportNameView> GetReportNameViews()
{
IList<ReportNameView> result = new List<ReportNameView>();
Query query = new Query("Report");
IEnumerable<Report> reports = reportRepository.FindBy(query);
foreach (var item in reports)
{
result.Add(new ReportNameView
{
ID = item.Id,
Name = item.Name
});
}
return result;
}
/// <summary>
/// 获得视图变量路径信息
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
public IEnumerable<DataPathInformation> GetDataPaths(int productLineId, string viewName)
{
string connectionString = ConnectionStringFactory.GetConnectionStringByProductLineID(productLineId, DatabaseType.DCSSystemDatabase);
ISqlServerDataFactory dataFactory = new SqlServerDataFactory(connectionString);
IList<DataPathInformation> results = new List<DataPathInformation>();
Query query = new Query("ContrastTable");
query.AddCriterion("ViewName", "viewName", viewName, CriteriaOperator.Equal);
DataTable table = dataFactory.Query(query);
foreach (DataRow item in table.Rows)
{
results.Add(new DataPathInformation
{
ViewId = item["VariableName"].ToString().Trim(),
FieldName = item["FieldName"].ToString().Trim(),
TableName = item["TableName"].ToString().Trim()
});
}
return results;
}
public IEnumerable<GroupTreeNode> GetFactoryTreeNode(GroupTreeNode companyTreeNode)
{
IList<GroupTreeNode> result = new List<GroupTreeNode>();
Query query = new Query("Factory");
query.AddCriterion("CompanyID", companyTreeNode.CompanyID, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
DataTable dt = dataFactory.Query(query);
foreach (DataRow row in dt.Rows)
{
GroupTreeNode item = new GroupTreeNode();
item.CompanyID = int.Parse(row["CompanyID"].ToString().Trim());
item.FactoryID = int.Parse(row["ID"].ToString().Trim());
item.Name = row["Name"].ToString().Trim();
item.Address = row["Address"].ToString().Trim();
item.CommissioningDate = row["CommissioningDate"].ToString();
item.Products = row["Products"].ToString().Trim();
result.Add(item);
}
return result;
}
/// <summary>
/// 获取用电量
/// </summary>
/// <param name="productLineId"></param>
/// <param name="formulaGroupId"></param>
/// <returns></returns>
public static DataTable GetElectricPowerCount(int productLineId, Guid formulaGroupId)
{
double peakUnitPrice = 0.6876;
double valleyUnitPrice = 0.4310;
double flatUnitPrice = 0.2744;
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("table_ElectricPowerCount");
query.AddCriterion("ProductLineID", "productLineId", productLineId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
query.AddCriterion("FormulaGroupID", "formulaGroupId", formulaGroupId.ToString(), SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
DataTable result = factory.Query(query);
DataColumn PeakCost = new DataColumn("PeakCost");
result.Columns.Add(PeakCost);
DataColumn ValleyCost = new DataColumn("ValleyCost");
result.Columns.Add(ValleyCost);
DataColumn FlatCost = new DataColumn("FlatCost");
result.Columns.Add(FlatCost);
DataColumn TotalCost = new DataColumn("TotalCost");
result.Columns.Add(TotalCost);
foreach (DataRow row in result.Rows)
{
double peakCost = int.Parse(row["PeakPower"].ToString()) * peakUnitPrice;
double valleyCost = int.Parse(row["ValleyPower"].ToString()) * valleyUnitPrice;
double flatCost = int.Parse(row["FlatPower"].ToString()) * flatUnitPrice;
double totalCost = peakCost + valleyCost + flatCost;
row["PeakCost"] = peakCost;
row["ValleyCost"] = valleyCost;
row["FlatCost"] = flatCost;
row["TotalCost"] = totalCost;
}
return result;
}
public IEnumerable<GroupTreeNode> GetCompanyTreeNode()
{
IList<GroupTreeNode> result = new List<GroupTreeNode>();
Query query = new Query("Company");
DataTable dt = dataFactory.Query(query);
foreach (DataRow row in dt.Rows)
{
GroupTreeNode item = new GroupTreeNode();
if (row["ID"].ToString().Trim() != "")
{
item.CompanyID = int.Parse(row["ID"].ToString().Trim());
}
item.Name = row["Name"].ToString().Trim();
item.Address = row["Address"].ToString().Trim();
item.CommissioningDate = row["CommissioningDate"].ToString();
item.Products = row["Products"].ToString().Trim();
result.Add(item);
}
return result;
}
public IEnumerable<TZ> FindBy(Query query)
{
IList<TZ> result = new List<TZ>();
DataTable table = dataFactory.Query(query);
foreach (DataRow row in table.Rows)
{
TZ tz = new TZ();
tz.KeyID = (Guid)row["KeyID"];
tz.ProductLineID = int.Parse(row["ProductLineID"].ToString().Trim());
tz.ReportID = int.Parse(row["ReportID"].ToString().Trim());
tz.TableName = row["TableName"].ToString().Trim();
tz.Date = row["Date"].ToString().Trim();
tz.CreationDate = (DateTime)row["CreationDate"];
tz.ModifierID = row["ModifierID"].ToString().Trim();
if (row["Version"].ToString().Trim() != "")
{
tz.Version = (DateTime)row["Version"];
}
tz.ModifiedFlag = (bool)row["ModifiedFlag"];
tz.Remarks = row["Remarks"].ToString().Trim();
//result.Add(new TZ
//{
// KeyID = (Guid)row["KeyID"],
// ProductLineID = int.Parse(row["ProductLineID"].ToString().Trim()),
// ReportID = int.Parse(row["ReportID"].ToString().Trim()),
// Date = row["Date"].ToString().Trim(),
// CreationDate = (DateTime)row["CreationDate"],
// ModifierID = row["ModifierID"].ToString().Trim(),
// Version = (DateTime)row["Version"],
// ModifiedFlag = (bool)row["ModifiedFlag"],
// Remarks = row["Remarks"].ToString().Trim()
//});
result.Add(tz);
}
return result;
}
public IList<PVFItem> GetPVFByCompanyID(int companyId)
{
IList<PVFItem> result = new List<PVFItem>();
Query query = new Query("PeakValleyFlat");
query.AddCriterion("CompanyID", companyId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
DataTable dt = dataFactory.Query(query);
foreach (DataRow row in dt.Rows)
{
PVFItem item = new PVFItem();
item.ID = int.Parse(row["ID"].ToString());
item.GroupID = int.Parse(row["GroupID"].ToString());
item.CompanyID = int.Parse(row["CompanyID"].ToString());
item.EndTime = row["EndTime"].ToString().Trim();
item.StartTime = row["StartTime"].ToString().Trim();
item.Flag = (bool)row["Flag"];
item.Type = int.Parse(row["Type"].ToString());
result.Add(item);
}
return result;
}