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 GetFormulaYearTable(Guid keyId, string tableName)
{
Query query = new Query(tableName);
query.AddCriterion("KeyID", keyId, CriteriaOperator.Equal);
DataTable dt = dataFactory.Query(query);
return dt;
}
/// <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);
}
/// <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>
/// 按起始时间段获取停机记录
/// </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 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);
}
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;
}
/// <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 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<GroupTreeNode> GetProductLineTreeNode(GroupTreeNode factoryTreeNode)
{
IList<GroupTreeNode> result = new List<GroupTreeNode>();
Query query = new Query("ProductLine");
query.AddCriterion("CompanyID", "a",factoryTreeNode.CompanyID, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
query.AddCriterion("FactoryID", "b",factoryTreeNode.FactoryID, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
DataTable dt = dataFactory.Query(query);
foreach (DataRow row in dt.Rows)
{
GroupTreeNode item = new GroupTreeNode();
item.ProductLineID = int.Parse(row["ID"].ToString().Trim());
item.CompanyID = int.Parse(row["CompanyID"].ToString().Trim());
item.FactoryID = int.Parse(row["FactoryID"].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();
item.children = null;
result.Add(item);
}
return result;
}
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>
/// 获得视图变量路径信息
/// </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 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;
}
/// <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>
/// 获得DataSetInformation
/// </summary>
/// <param name="viewName"></param>
/// <returns></returns>
private IEnumerable<DataSetInformation> GetDataSetInformation(string viewName)
{
IList<DataSetInformation> results = new List<DataSetInformation>();
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 DataSetInformation
{
ViewId = item["VariableName"].ToString().Trim(),
FieldName = item["FieldName"].ToString().Trim(),
TableName = item["TableName"].ToString().Trim()
});
}
return results;
}
/// <summary>
/// 按公式组ID获取所有公式
/// </summary>
/// <param name="groupId"></param>
/// <returns></returns>
public static DataTable GetFormulasByGroupId(Guid groupId)
{
string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("Formula");
query.AddCriterion("GroupID", groupId, SqlServerDataAdapter.Infrastruction.CriteriaOperator.Equal);
query.OrderByClause = new SqlServerDataAdapter.Infrastruction.OrderByClause("LevelCode", false);
return factory.Query(query);
}
/// <summary>
/// 按生产线ID获取数据库名（优先从缓存中查找）
/// </summary>
/// <param name="productLineId"></param>
/// <returns></returns>
private static string GetCatalogByProductLineId(int productLineId)
{
if (ProcessCatalogDictionary.ContainsKey(productLineId))
return ProcessCatalogDictionary[productLineId];
string connectionString = ConfigurationManager.ConnectionStrings["ManagementData"].ConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("DataBaseContrast");
query.AddCriterion("ProductLineId", productLineId, CriteriaOperator.Equal);
DataTable dt = factory.Query(query);
if (dt.Rows.Count == 0)
throw new ApplicationException("没有此生产线");
ProcessCatalogDictionary.Add(productLineId, dt.Rows[0]["DataBaseName"].ToString().Trim());
return ProcessCatalogDictionary[productLineId];
}
/// <summary>
/// 初始化过程数据库名字典
/// </summary>
private static void InitializeProcessCatalogDictionary()
{
ProcessCatalogDictionary.Clear();
string connectionString = ConfigurationManager.ConnectionStrings["ManagementData"].ConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("DataBaseContrast");
query.AddCriterion("ProductLineID", 0, CriteriaOperator.MoreThanOrEqual);
DataTable dt = factory.Query(query);
foreach(DataRow row in dt.Rows)
ProcessCatalogDictionary.Add(int.Parse(row["ProductLineID"].ToString()), row["DataBaseName"].ToString().Trim());
}
/// <summary>
/// 按分厂ID获取数据库名（优先从缓存中查找）
/// </summary>
/// <param name="factoryID"></param>
/// <returns></returns>
private static string GetCatalogByFactoryID(int factoryID)
{
if (MeterCatalogDaictionary.ContainsKey(factoryID))
return MeterCatalogDaictionary[factoryID];
string connectionString = ConfigurationManager.ConnectionStrings["ManagementData"].ConnectionString;
ISqlServerDataFactory factory = new SqlServerDataFactory(connectionString);
Query query = new Query("DataBaseContrast");
query.AddCriterion("FactoryID", factoryID, CriteriaOperator.Equal);
DataTable dt = factory.Query(query);
if (dt.Rows.Count == 0)
throw new ApplicationException("没有此分公司");
MeterCatalogDaictionary.Add(factoryID, dt.Rows[0]["DataBaseName"].ToString().Trim());
return MeterCatalogDaictionary[factoryID];
}