227 lines
9.6 KiB
C#
227 lines
9.6 KiB
C#
using AUTS.Domain.Entities;
|
|
using AUTS.Domain.ViewModels;
|
|
using AUTS.Services.DBUtility.Custom;
|
|
using AUTS.Services.Enums;
|
|
using AUTS.Services.Extensions;
|
|
using AUTS.Services.Tool;
|
|
using MySql.Data.MySqlClient;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
|
|
namespace AUTS.Services.Manager
|
|
{
|
|
public partial class Orders
|
|
{
|
|
private static string TestLogTbName = "TBL_{0}_{1}_TestLog";//测试记录表名
|
|
|
|
public static ReturnResult<object> GetOrdersPlannedSpeed(List<TBL_Project> cacheUserProject, List<TBL_Orders> cacheUserOrder, List<TBL_Customer> cacheUserCustomer, List<TBL_StationList> cacheUserStationList)
|
|
{
|
|
ReturnResult<object> result = new ReturnResult<object>();
|
|
|
|
try
|
|
{
|
|
var orderNotList = cacheUserOrder.Where(x => x.OrderStatus == 1);//取出未完成订单
|
|
|
|
var minTime = orderNotList.Min(m => m.CreateTime);//订单最早日期
|
|
var maxTime = DateTime.Now;//订单最晚日期
|
|
|
|
minTime = Convert.ToDateTime(minTime.ToString("yyyy/MM/dd"));//订单最早日期
|
|
maxTime = Convert.ToDateTime(maxTime.ToString("yyyy/MM/dd"));//订单最晚日期
|
|
|
|
TimeSpan ts = maxTime - minTime;
|
|
int differenceInDays = ts.Days + 1;
|
|
|
|
|
|
DataTable tblDatas = new DataTable("Datas");
|
|
|
|
tblDatas.Columns.Add("ID", Type.GetType("System.String"));//新列
|
|
tblDatas.Columns.Add("单号", Type.GetType("System.String"));//新列
|
|
tblDatas.Columns.Add("机型", Type.GetType("System.String"));//新列
|
|
tblDatas.Columns.Add("客户", Type.GetType("System.String"));//新列
|
|
tblDatas.Columns.Add("数量", Type.GetType("System.String"));//新列
|
|
tblDatas.Columns.Add("已完成", Type.GetType("System.String"));//新列
|
|
|
|
for (var i = 0; i < differenceInDays; i++)
|
|
{
|
|
tblDatas.Columns.Add(minTime.AddDays(i).ToString("yy/MM/dd"), Type.GetType("System.String"));//新列
|
|
|
|
}
|
|
|
|
var dbTableName = "";
|
|
var sumTotal = 0;
|
|
var dalHelperCustom = new DALHelperCustom<VM_PlannedSpeed>();
|
|
var thisDataList = new List<VM_PlannedSpeed>();
|
|
|
|
string connectionString = Users.GerOnUserCofin();//当前库连接串
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
{
|
|
DataSet dataSet = new DataSet();
|
|
|
|
connection.Open();
|
|
MySqlDataAdapter command = new MySqlDataAdapter();
|
|
var strSql = "";
|
|
|
|
foreach (var item in orderNotList)
|
|
{
|
|
strSql = "";
|
|
dataSet.Clear();
|
|
sumTotal = 0;
|
|
DataRow newRow = tblDatas.NewRow();//新行
|
|
|
|
newRow["ID"] = item.ID;
|
|
newRow["单号"] = item.OrderNo;
|
|
newRow["机型"] = cacheUserProject.Single(x => x.ID == item.ProductID).ProjectName;
|
|
newRow["客户"] = cacheUserCustomer.Single(x => x.ID == item.CompanyID).CustomerAbbr;
|
|
newRow["数量"] = item.OrderCount;
|
|
|
|
var endTestStationID = cacheUserStationList.Where(x => x.ProjectID == item.ProductID && x.StationType == StationType.Test.GetEnumDesc()).Max(m => m.ID);//最大测试站
|
|
|
|
dbTableName = string.Format(TestLogTbName, item.ProductID, endTestStationID);//最大测试站表名
|
|
|
|
strSql = "select StartTime,DATE_FORMAT(StartTime,'%Y%m%d') DAYS,COUNT(*) Total from `" + dbTableName + "` Where OrderID=" + item.ID + " AND TestResult=1 GROUP BY DAYS;";//查询语句
|
|
|
|
command = new MySqlDataAdapter(strSql.ToString(), connection);
|
|
command.Fill(dataSet, "ds");
|
|
|
|
if (dataSet.Tables[0].Rows.Count > 0)
|
|
{
|
|
thisDataList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]);
|
|
|
|
foreach (var itemData in thisDataList)
|
|
{
|
|
//tblDatas.Rows[rowKey][itemData.CreateTime.ToString("yyyy/MM/dd")] = itemData.Total;
|
|
newRow[itemData.StartTime.ToString("yy/MM/dd")] = itemData.Total;
|
|
sumTotal += itemData.Total;
|
|
}
|
|
}
|
|
|
|
newRow["已完成"] = sumTotal;
|
|
tblDatas.Rows.Add(newRow);
|
|
}
|
|
}
|
|
|
|
var rows = ModelHelper.TableToRow(tblDatas);
|
|
|
|
result.Data = rows;
|
|
result.Status = 200;
|
|
}
|
|
catch (MySqlException ex)
|
|
{
|
|
result.Message = ex.Message;
|
|
Logs.WriteErrorLog(ex);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result.Message = "网络系统繁忙，请稍候再试!";
|
|
LogHelp.WriteExceptionLog(ex);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
|
|
public static ReturnResult<object> GetOrdersPlannedSpeedDetails(int id, List<TBL_Project> cacheUserProject, List<TBL_Orders> cacheUserOrder, List<TBL_Customer> cacheUserCustomer, List<TBL_StationList> cacheUserStationList, string connectionString)
|
|
{
|
|
ReturnResult<object> result = new ReturnResult<object>();
|
|
|
|
try
|
|
{
|
|
var order = cacheUserOrder.Single(x => x.ID == id);//取出订单
|
|
|
|
var stationList = cacheUserStationList.Where(x => x.StationType != StationType.QA.GetEnumDesc() && x.ProjectID == order.ProductID);//不为该机型QA的测试站
|
|
|
|
var minTime = order.CreateTime;//订单最早日期
|
|
var maxTime = DateTime.Now;//订单最晚日期
|
|
minTime = Convert.ToDateTime(minTime.ToString("yyyy/MM/dd"));//订单最早日期
|
|
maxTime = Convert.ToDateTime(maxTime.ToString("yyyy/MM/dd"));//订单最晚日期
|
|
|
|
TimeSpan ts = maxTime - minTime;
|
|
int differenceInDays = ts.Days + 1;
|
|
|
|
DataTable tblDatas = new DataTable("Datas");
|
|
|
|
tblDatas.Columns.Add("测试站", Type.GetType("System.String"));//新列
|
|
tblDatas.Columns.Add("数量", Type.GetType("System.String"));//新列
|
|
|
|
for (var i = 0; i < differenceInDays; i++)
|
|
{
|
|
tblDatas.Columns.Add(minTime.AddDays(i).ToString("yy/MM/dd"), Type.GetType("System.String"));//新列
|
|
}
|
|
|
|
var dbTableName = "";
|
|
var strSql = "";
|
|
var sumTotal = 0;
|
|
var dalHelperCustom = new DALHelperCustom<VM_PlannedSpeed>();
|
|
|
|
//string connectionString = Users.GerOnUserCofin();//当前库连接串
|
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
|
{
|
|
DataSet dataSet = new DataSet();
|
|
MySqlDataAdapter command = new MySqlDataAdapter();
|
|
|
|
connection.Open();
|
|
|
|
//测试站列表
|
|
foreach (var itemStation in stationList)
|
|
{
|
|
strSql = "";
|
|
sumTotal = 0;
|
|
dataSet.Clear();
|
|
DataRow newRow = tblDatas.NewRow();//新行
|
|
newRow["测试站"] = itemStation.StationName;
|
|
newRow["数量"] = "当日";
|
|
|
|
DataRow newRow2 = tblDatas.NewRow();//新行
|
|
newRow2["测试站"] = itemStation.StationName;
|
|
newRow2["数量"] = "累计";
|
|
|
|
dbTableName = string.Format(TestLogTbName, itemStation.ProjectID, itemStation.ID);//测试站表名
|
|
|
|
|
|
strSql = "select StartTime,DATE_FORMAT(StartTime,'%Y%m%d') DAYS,COUNT(*) Total from `" + dbTableName + "` Where OrderID=" + id + " AND TestResult=1 GROUP BY DAYS;";//查询语句
|
|
|
|
command = new MySqlDataAdapter(strSql.ToString(), connection);
|
|
command.Fill(dataSet, "ds");
|
|
|
|
if (dataSet.Tables[0].Rows.Count > 0)
|
|
{
|
|
var thisDataList = dalHelperCustom.DataRowToModels(dataSet.Tables[0]).OrderBy(x => x.StartTime);
|
|
|
|
foreach (var itemData in thisDataList)
|
|
{
|
|
newRow[itemData.StartTime.ToString("yy/MM/dd")] = itemData.Total;
|
|
sumTotal += itemData.Total;
|
|
|
|
newRow2[itemData.StartTime.ToString("yy/MM/dd")] = sumTotal;
|
|
}
|
|
}
|
|
tblDatas.Rows.Add(newRow);
|
|
tblDatas.Rows.Add(newRow2);
|
|
}
|
|
|
|
}
|
|
|
|
var rows = ModelHelper.TableToRow(tblDatas);
|
|
|
|
result.Data = rows;
|
|
result.Status = 200;
|
|
}
|
|
catch (MySqlException ex)
|
|
{
|
|
result.Message = ex.Message;
|
|
Logs.WriteErrorLog(ex);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
result.Message = "网络系统繁忙，请稍候再试!";
|
|
LogHelp.WriteExceptionLog(ex);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
}
|
|
}
|