public static string GetLocaleStringResource(int languageID, string resourceName)
{
using (var context = new SPPContext())
{
if (CacheHelper.Get(resourceName + languageID) != null)
{
return(CacheHelper.Get(resourceName + languageID).ToString());
}
else
{
string result = string.Empty;
var item = context.System_LocaleStringResource.Where(m => m.ResourceName == resourceName && m.System_Language_UID == languageID).FirstOrDefault();
if (item == null)
{
result = resourceName;
}
else
{
result = item.ResourceValue;
CacheHelper.Set(resourceName + languageID, result);
}
return(result);
}
}
}
public CustomUserManager GetUserManager(SPPContext context)
{
CustomUserStore store = new CustomUserStore(context);
CustomUserManager manager = new CustomUserManager(store);
return(manager);
}
/// <summary>
/// 取得設備明細檔 RP_Flowchart_Detail_ME_Equipment
/// </summary>
/// <param name="rP_Flowchart_Detail_ME_Equipment_UID">設備明細檔流水號</param>
/// <returns></returns>
public RP_ME_D_Equipment GetME_D_Equipment(int rP_Flowchart_Detail_ME_Equipment_UID)
{
RP_ME_D_Equipment query = new RP_ME_D_Equipment();
var RP_Flowchart_Detail_ME_Equipment_UID = new SqlParameter("@RP_Flowchart_Detail_ME_Equipment_UID", rP_Flowchart_Detail_ME_Equipment_UID);
object[] parameter = new object[] { RP_Flowchart_Detail_ME_Equipment_UID };
using (var context = new SPPContext())
{
var sql_str = string.Empty;
sql_str += @"
SELECT b.[Process_Seq],
b.[Process_Station],
c.Organization_Name as FunPlant_Organization_Name,
b.Process,
b.[Capacity_ByHour],
b.[Capacity_ByDay],
a.*
FROM [dbo].[RP_Flowchart_Detail_ME_Equipment] a
INNER JOIN [dbo].[RP_Flowchart_Detail_ME] b ON a.[RP_Flowchart_Detail_ME_UID] = b.[RP_Flowchart_Detail_ME_UID]
INNER JOIN [dbo].[System_Organization] c ON b.[FunPlant_Organization_UID] = c.Organization_UID
WHERE a.[RP_Flowchart_Detail_ME_Equipment_UID] = @RP_Flowchart_Detail_ME_Equipment_UID
";
query = context.Database.SqlQuery <RP_ME_D_Equipment>(sql_str, parameter).SingleOrDefault();
}
return(query);
}
public void InsertBatchLog(Batch_Log newLog)
{
using (var context = new SPPContext())
{
context.Batch_Log.Add(newLog);
context.SaveChanges();
}
}
public IHttpActionResult GetLanguagesAllAPI()
{
using (var context = new SPPContext())
{
var languages = context.System_Language.Where(m => m.Enable_Flag == true).ToList();
var dtos = AutoMapper.Mapper.Map <List <SystemLanguageDTO> >(languages);
return(Ok(dtos));
}
}
public IHttpActionResult GetLanguagesAPI(int Language_UID)
{
using (var context = new SPPContext())
{
if (Language_UID == 0)
{
Language_UID = 2;
}
var languages = context.System_Language.Where(m => m.Enable_Flag == true).ToList();
LanguageVM vm = new LanguageVM();
vm.Languages = AutoMapper.Mapper.Map <List <SystemLanguageDTO> >(languages);
vm.CurrentLanguage = vm.Languages.Where(m => m.System_Language_UID.Equals(Language_UID)).First();
return(Ok(vm));
}
}
/// <summary>
/// 取得ME主檔 Change History
/// </summary>
/// <param name="plant_Organization_UID">plant_Organization_UID</param>
/// <param name="bG_Organization_UID">bG_Organization_UID</param>
/// <param name="project_UID">project_UID</param>
/// <returns></returns>
public List <RP_M> GetME_ChangeHistory(int plant_Organization_UID, int bG_Organization_UID, int project_UID)
{
List <RP_M> query = new List <RP_M>();
var Plant_Organization_UID = new SqlParameter("@plant_Organization_UID", plant_Organization_UID);
var BG_Organization_UID = new SqlParameter("@BG_Organization_UID", bG_Organization_UID);
var Project_UID = new SqlParameter("@Project_UID", project_UID);
object[] parameter = new object[] { Plant_Organization_UID, BG_Organization_UID, Project_UID };
using (var context = new SPPContext())
{
var sql_str = string.Empty;
sql_str += @"
--DECLARE @Plant_Organization_UID nvarchar(50) = 1,
-- @BG_Organization_UID nvarchar(50) = 3,
-- @Project_UID nvarchar(50) = 66
--;
SELECT a.RP_Flowchart_Master_UID,
d.BU_Name + '-' + c.BU_D_Name as BU,
b.[Project_Name],
a.[Part_Types],
a.[Product_Phase],
a.[FlowChart_Version],
a.[FlowChart_Version_Comment],
a.[Daily_Targetoutput],
a.[FPY],
a.[Is_Closed],
a.[Created_UID],
e.[User_Name] + ' (' + e.[User_NTID] + ')' as [Created_User_Name],
a.[Created_Date],
a.[Modified_UID],
f.[User_Name] + ' (' + f.[User_NTID] + ')' as [Modified_User_Name],
a.[Modified_Date]
FROM [dbo].[RP_Flowchart_Master] a
INNER JOIN [dbo].[System_Project] b ON ａ.[Project_UID] = b.[Project_UID]
INNER JOIN [dbo].[System_BU_D] c ON b.[BU_D_UID] = c.[BU_D_UID]
INNER JOIN [dbo].[System_BU_M] d ON c.[BU_M_UID] = d.[BU_M_UID]
INNER JOIN [dbo].[System_Users] e ON a.[Created_UID] = e.[Account_UID]
INNER JOIN [dbo].[System_Users] f ON a.[Modified_UID] = f.[Account_UID]
WHERE [Plant_Organization_UID] = @Plant_Organization_UID
AND [BG_Organization_UID] = @BG_Organization_UID
AND a.[Project_UID] = @Project_UID
ORDER BY [FlowChart_Version]
";
query = context.Database.SqlQuery <RP_M>(sql_str, parameter).ToList();
}
return(query);
}
/// <summary>
/// 取得ME_D資料清單by ME主檔流水號
/// </summary>
/// <param name="rP_Flowchart_Master_UID">ME主檔流水號</param>
/// <returns></returns>
public List <RP_ME_D> GetME_Ds(int rP_Flowchart_Master_UID)
{
List <RP_ME_D> query = new List <RP_ME_D>();
var RP_Flowchart_Master_UID = new SqlParameter("@RP_Flowchart_Master_UID", rP_Flowchart_Master_UID);
object[] parameter = new object[] { RP_Flowchart_Master_UID };
using (var context = new SPPContext())
{
var sql_str = string.Empty;
sql_str += @"
--DECLARE @RP_Flowchart_Master_UID int = 33
--;
SELECT f.BU_Name + '-' +e.BU_D_Name as BU,
d.[Project_Name],
b.[Part_Types],
b.[Product_Phase],
a.[Process_Station],
c.Organization_Name as FunPlant_Organization_Name,
a.[Process],
a.[Process_Desc],
a.[Processing_Equipment],
a.[Automation_Equipment],
a.[Processing_Fixtures],
a.[Auxiliary_Equipment],
a.[Equipment_CT],
a.[Setup_Time],
a.[Total_Cycletime],
a.[ME_Estimate_Yield],
a.[Manpower_Ratio],
a.[Capacity_ByHour],
a.[Capacity_ByDay],
a.[Equipment_RequstQty],
a.[Manpower_2Shift]
FROM [dbo].[RP_Flowchart_Detail_ME] a
INNER JOIN [dbo].[RP_Flowchart_Master] b ON a.[RP_Flowchart_Master_UID] = b.[RP_Flowchart_Master_UID]
INNER JOIN [dbo].[System_Organization] c ON a.[FunPlant_Organization_UID] = c.Organization_UID
INNER JOIN [dbo].[System_Project] d ON b.[Project_UID] = d.[Project_UID]
INNER JOIN [dbo].[System_BU_D] e ON d.[BU_D_UID] = e.[BU_D_UID]
INNER JOIN [dbo].[System_BU_M] f ON f.[BU_M_UID] = e.[BU_M_UID]
WHERE b.[RP_Flowchart_Master_UID] = @RP_Flowchart_Master_UID
";
query = context.Database.SqlQuery <RP_ME_D>(sql_str, parameter).ToList();
}
return(query);
}
public void InsertLogInfo(string flag, bool sendFlag, string errorInfo)
{
using (var context = new SPPContext())
{
Batch_Log newLog = new Batch_Log();
newLog.Batch_Date = DateTime.Now;
if (sendFlag)
{
SetSuccessItem(flag, newLog);
}
else
{
SetFailedItem(flag, newLog, errorInfo);
}
context.Batch_Log.Add(newLog);
context.SaveChanges();
}
}
/// <summary>
/// 取得設備明細檔 RP_Flowchart_Detail_ME_Equipment
/// </summary>
/// <param name="search">搜尋條件集合</param>
/// <param name="page">分頁參數</param>
/// <returns></returns>
public List <RP_ME_D_Equipment> GetME_D_Equipments(ME_EquipmentSearchVM search, Page page, out int count)
{
List <RP_ME_D_Equipment> query = new List <RP_ME_D_Equipment>();
var RP_Flowchart_Master_UID = new SqlParameter("@RP_Flowchart_Master_UID", search.RP_Flowchart_Master_UID);
var Equipment_Type = new SqlParameter("@Equipment_Type", search.Equipment_Type);
object[] parameter = new object[] { RP_Flowchart_Master_UID, Equipment_Type };
using (var context = new SPPContext())
{
var sql_str = string.Empty;
sql_str += @"
--DECLARE @RP_Flowchart_Master_UID int = 33,
-- @Equipment_Type nvarchar(50) = N''
--;
SELECT b.RP_Flowchart_Detail_ME_Equipment_UID,
a.[Process_Seq],
a.[Process_Station],
c.Organization_Name as FunPlant_Organization_Name,
a.[Process],
b.[Equipment_Name],
b.Equipment_Spec,
b.Equipment_Qty,
b.Ratio,
b.Request_Qty,
b.EQP_Variable_Qty,
b.NPI_Current_Qty,
b.MP_Current_Qty,
a.[Capacity_ByDay],
a.[Capacity_ByHour],
a.ME_Estimate_Yield,
b.Plan_CT
FROM [dbo].[RP_Flowchart_Detail_ME] a
INNER JOIN [dbo].[RP_Flowchart_Detail_ME_Equipment] b ON a.[RP_Flowchart_Detail_ME_UID] = b.[RP_Flowchart_Detail_ME_UID]
INNER JOIN [dbo].[System_Organization] c ON a.[FunPlant_Organization_UID] = c.Organization_UID
WHERE a.[RP_Flowchart_Master_UID] = @RP_Flowchart_Master_UID AND ([Equipment_Type] = @Equipment_Type OR ISNULL(@Equipment_Type,'') = '')
";
query = context.Database.SqlQuery <RP_ME_D_Equipment>(sql_str, parameter).ToList();
}
count = query.Count();
return(query.Skip(page.Skip).Take(page.PageSize).ToList());
}
public List <ProductLocationItem> QueryProductInputLocation(ProductInputLocationSearch search, Page page)
{
List <ProductLocationItem> resultList = new List <ProductLocationItem>();
using (var context = new SPPContext())
{
if (search.Time_Interval == "全天" || search.Time_Interval == "ALL")
{
var strSql = @"
SELECT
p.Good_QTY,
p.Picking_QTY,
p.WH_Picking_QTY,
p.NG_QTY,
p.WH_QTY,
p.WIP_QTY,
p.Place,
p.Time_Interval,
p.Unacommpolished_Reason
FROM
Product_Input_Location p
JOIN dbo.FlowChart_Detail f ON f.FlowChart_Detail_UID = p.FlowChart_Detail_UID
";
var date = Convert.ToDateTime(search.Product_Date);
var paramwhere =
$" WHERE Is_Comfirm = 1 AND p.FlowChart_Master_UID ={search.FlowChart_Master_UID} AND p.Process = N'{search.Process}' AND p.Color = N'{search.Color}' AND Product_Date = '{date}'";
strSql = strSql + paramwhere;
var result = DataContext.Database.SqlQuery <ProductLocationItem>(strSql).ToList();
var timeInterValModelList = GetMaxTimeInterVal(search.opType);
var PlaceDic = result.GroupBy(p => p.Place).ToDictionary(p => p.Key, q => q);
foreach (var pdItems in PlaceDic)
{
var timeIntervalDic = new Dictionary <int, int?>();
foreach (var item in pdItems.Value)
{
var timeInterModel = timeInterValModelList.Where(p => p.Enum_Value == item.Time_Interval).FirstOrDefault();
timeIntervalDic.Add(int.Parse(timeInterModel.Enum_Name), item.WIP_QTY);
}
var WIP_QTY = timeIntervalDic.OrderByDescending(p => p.Key).FirstOrDefault().Value;
var resultModel = new ProductLocationItem()
{
Good_QTY = pdItems.Value.Sum(p => p.Good_QTY),
Picking_QTY = pdItems.Value.Sum(p => p.Picking_QTY),
WH_Picking_QTY = pdItems.Value.Sum(p => p.WH_Picking_QTY),
NG_QTY = pdItems.Value.Sum(p => p.NG_QTY),
WH_QTY = pdItems.Value.Sum(p => p.WH_QTY),
Place = pdItems.Key,
WIP_QTY = WIP_QTY
};
resultList.Add(resultModel);
}
}
else
{
var query = from l in context.Product_Input_Location
where l.FlowChart_Master_UID == search.FlowChart_Master_UID &&
l.Color == search.Color && l.Process == search.Process &&
l.Product_Date == search.Product_Date && l.Time_Interval == search.Time_Interval
select new ProductLocationItem
{
Place = l.Place,
Picking_QTY = l.Picking_QTY,
Good_QTY = l.Good_QTY,
NG_QTY = l.NG_QTY,
WH_Picking_QTY = l.WH_Picking_QTY,
WH_QTY = l.WH_QTY,
Adjust_QTY = l.Adjust_QTY,
WIP_QTY = l.WIP_QTY,
Unacommpolished_Reason = l.Unacommpolished_Reason
};
if (page != null)
{
query = query.Skip(page.Skip).Take(page.PageSize);
}
resultList = query.ToList();
}
return(resultList);
}
}
public void ExecToDoOne()
{
try
{
var DataContext = new SPPContext();
List <Fixture_Maintenance_Record> recordList = new List <Fixture_Maintenance_Record>();
using (var trans = DataContext.Database.BeginTransaction())
{
//全插操作
StringBuilder sb = new StringBuilder();
//1.先把7天以外治具保养表数据找到，然后插入到历史表中，然后再删除7天以外的治具保养表数据，
//2.先把7天以外的治具履历表数据找到，然后插入到历史表中，然后再删除7天以外的治具履历表数据。
var insertSql = string.Format(@"INSERT INTO Fixture_Maintenance_Record_History(
Fixture_Maintenance_Record_UID
, Fixture_Maintenance_Profile_UID
, Maintenance_Record_NO
, Fixture_M_UID
, Maintenance_Date
, Maintenance_Status
, Maintenance_Person_Number
, Maintenance_Person_Name
, Confirm_Date
, Confirm_Status
, Confirmor_UID
, Created_UID
, Created_Date
, Modified_UID
, Modified_Date)
SELECT
Fixture_Maintenance_Record_UID
, Fixture_Maintenance_Profile_UID
, Maintenance_Record_NO
, Fixture_M_UID
, Maintenance_Date
, Maintenance_Status
, Maintenance_Person_Number
, Maintenance_Person_Name
, Confirm_Date
, Confirm_Status
, Confirmor_UID
, Created_UID
, Created_Date
, Modified_UID
, Modified_Date FROM Fixture_Maintenance_Record WHERE Confirm_Status = 1 AND Modified_Date <= N'{0}';
DELETE FROM Fixture_Maintenance_Record WHERE Confirm_Status = 1 AND Modified_Date <= N'{0}';
INSERT INTO Fixture_Resume_History(
Fixture_Resume_UID
, Fixture_M_UID
, Data_Source
, Resume_Date
, Source_UID
, Source_NO
, Resume_Notes
, Modified_UID
, Modified_Date)
SELECT
Fixture_Resume_UID
, Fixture_M_UID
, Data_Source
, Resume_Date
, Source_UID
, Source_NO
, Resume_Notes
, Modified_UID
, Modified_Date FROM Fixture_Resume WHERE Modified_Date <= N'{0}';
DELETE FROM Fixture_Resume WHERE Modified_Date <= N'{0}'; ",
DateTime.Now.AddDays(-7).ToString(FormatConstants.DateTimeFormatString)
);
sb.AppendLine(insertSql);
string sql = sb.ToString();
DataContext.Database.ExecuteSqlCommand(sb.ToString());
trans.Commit();
}
}
catch (Exception e)
{
// return e.Message;
}
}
//查询邮件发送列表准备发送邮件
public void ExecSendEmail()
{
var Excetion_Email_UID = Convert.ToInt32(ConfigurationManager.AppSettings["Excetion_Email_UID"]);
var functionName = StructConstants.BatchModuleName.EmailFunctionName;
List <BatchExecVM> matchList = new List <BatchExecVM>();
using (var context = new SPPContext())
{
try
{
string strSql = @"SELECT B.*,C.Function_Name FROM dbo.System_Module A
JOIN dbo.System_Schedule B ON B.System_Module_UID = A.System_Module_UID
JOIN dbo.System_Function C ON B.Function_UID = C.Function_UID
WHERE A.Is_Enable = 1 AND B.Is_Enable = 1 AND C.Function_Name = '{0}'";
strSql = string.Format(strSql, functionName);
var list = context.Database.SqlQuery <BatchExecVM>(strSql).ToList();
#region 第一步：查询System_Schedule将时间相匹配的数据进行更新
StringBuilder sb = new StringBuilder();
var nowDate = DateTime.Now;
DateTime? nextDate = null;
foreach (var item in list)
{
Excetion_Email_UID = item.System_Schedule_UID;
//Next_Execution_Date若为当前时间，则执行
if (
item.Next_Execution_Date.Year == nowDate.Year &&
item.Next_Execution_Date.Month == nowDate.Month &&
item.Next_Execution_Date.Day == nowDate.Day &&
item.Next_Execution_Date.Hour == nowDate.Hour &&
item.Next_Execution_Date.Minute == nowDate.Minute
)
{
//var strTimeList = item.Exec_Moment.Split(',').ToList();
//将list<string>转换为list<int>
//var intIdList = strTimeList.Select<string, int>(x => Convert.ToInt32(x)).ToList();
matchList.Add(item);
switch (item.Cycle_Unit)
{
case "H": //按小时
nextDate = GetHourTime(item, nowDate);
break;
case "W": //按周
nextDate = GetWeekDay(item, nowDate);
break;
case "M": //按月
nextDate = GetMonthDay(item, nowDate);
break;
}
//更新下次执行时间Next_Execution_Date
var strUpdate = @"UPDATE dbo.System_Schedule SET Last_Execution_Date = GETDATE(),
Next_Execution_Date = '{1}',
Modified_Date = GETDATE(),
Modified_UID = 99999
WHERE System_Schedule_UID = {0}; ";
strUpdate = string.Format(strUpdate, item.System_Schedule_UID, nextDate);
sb.AppendLine(strUpdate);
}
}
if (sb.Length > 0)
{
context.Database.ExecuteSqlCommand(sb.ToString());
}
#endregion
#region 第二步：发送邮件通知
//发送三天之内的邮件
//ISNULL(Reservation_Date,Modified_Date),GETDATE()) <= 3 防止预订的时间到了程序还没跑到
var strExecSql = @"SELECT * FROM dbo.System_Email_M WHERE (Is_Send = 0 OR Is_Send IS NULL)
AND (DATEDIFF(DAY,ISNULL(Reservation_Date,Modified_Date),GETDATE()) <= 3)
AND GETDATE() >= ISNULL(Reservation_Date,Modified_Date) ";
//上面的查询语句条件不能加System_Schedule_UID，因为邮件执行的排程外键不是发邮件的外键
var vmList = context.Database.SqlQuery <BatchMailVM>(strExecSql).ToList();
if (vmList.Count() > 0)
{
foreach (var item in vmList)
{
var IsSuccess = SendMail(item);
var entity = context.System_Email_M.Find(item.System_Email_M_UID);
if (IsSuccess)
{
entity.Is_Send = true;
entity.Send_Time = nowDate;
//执行完毕后写入日志
Batch_Log newLog = new Batch_Log
{
System_Schedule_UID = item.System_Schedule_UID,
Batch_Name = StructConstants.BatchLog.Email_Module_Success,
Batch_Status = true,
Batch_Desc = StructConstants.BatchLog.Email_Module_Success,
Batch_Date = DateTime.Now
};
InsertBatchLog(newLog);
}
}
context.SaveChanges();
}
#endregion
}
catch (Exception ex)
{
InsertExceptionBatchLog(Excetion_Email_UID, ex.Message);
}
}
}
/// <summary>
/// 通过制程序号获取
/// </summary>
public List <ProductLocationItem> GetPDInputLocationByProSeqAPI(PDByProSeqSearch search, Page page)
{
List <ProductLocationItem> resultList = new List <ProductLocationItem>();
using (var context = new SPPContext())
{
if (search.Time_Interval == "全天" || search.Time_Interval == "ALL")
{
var strSql = @"
SELECT
p.Good_QTY,
p.Picking_QTY,
p.WH_Picking_QTY,
p.NG_QTY,
p.WH_QTY,
p.WIP_QTY,
p.Place,
p.Color,
p.Time_Interval
FROM
Product_Input_Location p
JOIN dbo.FlowChart_Detail f ON f.FlowChart_Detail_UID = p.FlowChart_Detail_UID
";
var paramWhere =
$" WHERE Is_Comfirm = 1 AND p.Project = N'{search.project}' AND p.Product_Phase = N'{search.Product_Phase}' AND p.Customer = N'{search.customer}' AND p.Part_Types = N'{search.part_types}' AND p.Process = N'{search.Process_Seq}' AND p.Product_Date = '{search.input_date}'";
strSql = strSql + paramWhere;
var result = DataContext.Database.SqlQuery <ProductLocationItem>(strSql).ToList();
var timeInterValModelList = GetMaxTimeInterVal(search.optype);
//1 按楼栋分组
var PlaceDic = result.GroupBy(p => p.Place).ToDictionary(p => p.Key, q => q);
foreach (var pdItems in PlaceDic)
{
//2 按颜色分组
var colorDic = pdItems.Value.GroupBy(p => p.Color).ToDictionary(p => p.Key, q => q);
int?WIP_QTY = 0;
foreach (var coloritem in colorDic)
{
var timeIntervalDic = new Dictionary <int, int?>();
foreach (var itemVal in coloritem.Value)
{
var timeInterModel =
timeInterValModelList.FirstOrDefault(p => p.Enum_Value == itemVal.Time_Interval);
if (timeInterModel != null)
{
timeIntervalDic.Add(int.Parse(timeInterModel.Enum_Name), itemVal.WIP_QTY);
}
}
WIP_QTY += timeIntervalDic.OrderByDescending(p => p.Key).FirstOrDefault().Value;
}
var resultModel = new ProductLocationItem()
{
Good_QTY = pdItems.Value.Sum(p => p.Good_QTY),
Picking_QTY = pdItems.Value.Sum(p => p.Picking_QTY),
WH_Picking_QTY = pdItems.Value.Sum(p => p.WH_Picking_QTY),
NG_QTY = pdItems.Value.Sum(p => p.NG_QTY),
WH_QTY = pdItems.Value.Sum(p => p.WH_QTY),
Place = pdItems.Key,
WIP_QTY = WIP_QTY,
};
resultList.Add(resultModel);
}
}
else
{
var strSql = @"
SELECT
SUM ([Good_QTY]) AS Good_QTY,
SUM ([Picking_QTY]) AS Picking_QTY,
SUM ([WH_Picking_QTY]) AS WH_Picking_QTY,
SUM ([NG_QTY]) AS NG_QTY,
SUM ([WH_QTY]) AS WH_QTY,
sum (p.WIP_QTY) AS WIP_QTY,
p.Place
FROM
Product_Input_Location p
JOIN dbo.FlowChart_Detail f ON f.FlowChart_Detail_UID = p.FlowChart_Detail_UID
WHERE
Is_Comfirm = 1
AND p.Project =N'{0}'
AND p.Product_Phase = N'{1}'
AND p.Customer = N'{2}'
AND p.Part_Types = N'{3}'
AND p.Process = N'{4}'
AND p.Product_Date = '{5}'
AND p.Time_Interval = '{6}'
GROUP BY
p.Place
";
strSql = string.Format(strSql, search.project, search.Product_Phase, search.customer,
search.part_types, search.Process_Seq, search.input_date, search.Time_Interval);
var query = DataContext.Database.SqlQuery <ProductLocationItem>(strSql).ToList();
if (page != null)
{
query = query.Skip(page.Skip).Take(page.PageSize).ToList();
}
resultList = query;
}
return(resultList);
}
}
public void InsertExceptionBatchLog(int Excetion_Email_UID, string errorInfo)
{
using (var context = new SPPContext())
{
//发生异常后，需要发送邮件通知对应的系统人员或角色人员
var excetionSql = @"SELECT A.System_PIC_UIDs,B.Users_PIC_UIDs,B.Role_UIDs,C.Function_Name FROM dbo.System_Module A
JOIN dbo.System_Schedule B ON B.System_Module_UID = A.System_Module_UID
JOIN dbo.System_Function C ON B.Function_UID = C.Function_UID
WHERE B.System_Schedule_UID = {0}";
excetionSql = string.Format(excetionSql, Excetion_Email_UID);
var item = context.Database.SqlQuery <BatchExecVM>(excetionSql).First();
List <int> AccountUIDList = new List <int>();
var sysUIDList = item.System_PIC_UIDs.Split(',').ToList();
var intSysUIdList = sysUIDList.Select <string, int>(x => Convert.ToInt32(x)).ToList();
AccountUIDList.AddRange(intSysUIdList);
if (!string.IsNullOrEmpty(item.Users_PIC_UIDs))
{
var userUIDList = item.Users_PIC_UIDs.Split(',').ToList();
var intUserUIdList = userUIDList.Select <string, int>(x => Convert.ToInt32(x)).ToList();
AccountUIDList.AddRange(intUserUIdList);
}
if (!string.IsNullOrEmpty(item.Role_UIDs))
{
var roleUIDList = item.Role_UIDs.Split(',').ToList();
var intRoleUIDList = roleUIDList.Select <string, int>(x => Convert.ToInt32(x)).ToList();
var roleUserUIDList = context.System_User_Role.Where(m => intRoleUIDList.Contains(m.Role_UID)).Select(m => m.Account_UID).ToList();
AccountUIDList.AddRange(roleUserUIDList);
}
AccountUIDList = AccountUIDList.Distinct().ToList();
//var emailList = context.System_Users.Where(m => !string.IsNullOrEmpty(m.Email) && AccountUIDList.Contains(m.Account_UID)).Select(m => m.Email).ToList();
//System_Email_M emailItem = new System_Email_M();
//emailItem.System_Schedule_UID = Excetion_Email_UID;
//emailItem.Subject = StructConstants.BatchLog.Email_Module_Failed;
//emailItem.Body = item.Function_Name + "出现错误，请联系系统管理员";
//emailItem.Email_From = StructConstants.Email_From.PIS_Email_From;
//emailItem.Email_To = string.Join(",", emailList);
//emailItem.Email_To_UIDs = string.Join(",", AccountUIDList);
//emailItem.Is_Send = false;
//emailItem.Email_Type = 1;
//emailItem.Modified_UID = ConstConstants.AdminUID;
//emailItem.Modified_Date = DateTime.Now;
//context.System_Email_M.Add(emailItem);
//context.SaveChanges();
Batch_Log newLog = new Batch_Log
{
System_Schedule_UID = Excetion_Email_UID,
Batch_Name = item.Function_Name + "执行失败",
Batch_Status = false,
Batch_Desc = errorInfo,
Batch_Date = DateTime.Now
};
InsertBatchLog(newLog);
}
}
public GenericRepository(SPPContext context)
{
this.context = context;
dbSet = context.Set <TEntity>();
}
public void ExecToDoOne()
{
#region 注释，因数据量太多，采用存储过程
//using (var context = new SPPContext())
//{
// var sql = GetBatchSql();
// var list = context.Database.SqlQuery<FixtureBatchVM>(sql).ToList();
// List<Fixture_Maintenance_Record> recordList = new List<Fixture_Maintenance_Record>();
// var groupList = list.GroupBy(m => new { m.Plant_Organization_UID, m.BG_Organization_UID, m.FunPlant_Organization_UID,
// m.Cycle_ID, m.Cycle_Interval, m.Cycle_Unit });
// int i = 1;
// foreach (var groupItem in groupList)
// {
// var seq = i.ToString().PadLeft(3, '0');
// var seqNo = string.Format("MP{0}_{1}_{2}", DateTime.Now.ToString("yyyyMMdd"), seq, groupItem.Key.Cycle_ID + groupItem.Key.Cycle_Interval + groupItem.Key.Cycle_Unit);
// var groupItemList = groupItem.ToList();
// foreach (var item in groupItemList)
// {
// Fixture_Maintenance_Record recordItem = new Fixture_Maintenance_Record();
// recordItem.Fixture_Maintenance_Profile_UID = item.Fixture_Maintenance_Profile_UID;
// recordItem.Maintenance_Record_NO = seqNo;
// recordItem.Fixture_M_UID = item.Fixture_M_UID;
// recordItem.Maintenance_Status = 0;
// recordItem.Created_UID = ConstConstants.AdminUID;
// recordItem.Created_Date = DateTime.Now;
// recordItem.Modified_UID = ConstConstants.AdminUID;
// recordItem.Modified_Date = DateTime.Now;
// recordList.Add(recordItem);
// }
// i++;
// }
// context.Fixture_Maintenance_Record.AddRange(recordList);
// //更新Maintenance_Plan表信息
// var planUID = list.Select(m => m.Maintenance_Plan_UID).Distinct().ToList();
// var planList = context.Maintenance_Plan.Where(m => planUID.Contains(m.Maintenance_Plan_UID)).ToList();
// var nextDate = DateTime.Now;
// foreach (var planItem in planList)
// {
// switch (planItem.Cycle_Unit)
// {
// case "W": //planItem.Next_Execution_Date.Value这个日期是本次执行的日期，后面要改写
// nextDate = planItem.Next_Execution_Date.Value.AddDays(7 * planItem.Cycle_Interval);
// break;
// case "M": //planItem.Next_Execution_Date.Value这个日期是本次执行的日期，后面要改写
// nextDate = planItem.Next_Execution_Date.Value.AddMonths(planItem.Cycle_Interval);
// break;
// case "H": //planItem.Next_Execution_Date.Value这个日期是本次执行的日期，后面要改写
// nextDate = planItem.Next_Execution_Date.Value.AddHours(planItem.Cycle_Interval);
// break;
// }
// planItem.Last_Execution_Date = DateTime.Now;
// planItem.Next_Execution_Date = nextDate;
// planItem.Modified_UID = ConstConstants.AdminUID;
// planItem.Modified_Date = DateTime.Now;
// }
// context.SaveChanges();
//}
#endregion
using (var context = new SPPContext())
{
context.Database.ExecuteSqlCommand("Fixture_Batch_Record");
}
}
/// <summary>
/// 取得ME清單
/// </summary>
/// <param name="search">查詢條件集合</param>
/// <param name="page">分頁參數</param>
/// <param name="count">筆數</param>
/// <returns></returns>
public List <RP_ME_VM> QueryMEs(RP_MESearch search, Page page, out int count)
{
List <RP_ME_VM> query = new List <RP_ME_VM>();
var Plant_Organization_UID = new SqlParameter("@Plant_Organization_UID", search.Plant_Organization_UID);
var BG_Organization_UID = new SqlParameter("@BG_Organization_UID", search.BG_Organization_UID);
var Project_Name = new SqlParameter("@Project_Name", search.Project_Name == null ? string.Empty : search.Project_Name.Trim());
var BU = new SqlParameter("@BU", search.BU == null ? string.Empty : search.BU.Trim());
var Part_Types = new SqlParameter("@Part_Types", search.Part_Types == null ? string.Empty : search.Part_Types.Trim());
var Product_Phase = new SqlParameter("@Product_Phase", search.Product_Phase == null ? string.Empty : search.Product_Phase.Trim());
var Is_Closed = new SqlParameter("@Is_Closed", search.Is_Closed == null ? false : search.Is_Closed);
var Start_Date = new SqlParameter("@Start_Date", search.Start_Date == null ? string.Empty : search.Start_Date);
var End_Date = new SqlParameter("@End_Date", search.End_Date == null ? string.Empty : search.End_Date);
object[] parameter = new object[] { Plant_Organization_UID, BG_Organization_UID, Project_Name, BU, Part_Types, Product_Phase, Is_Closed, Start_Date, End_Date };
using (var context = new SPPContext())
{
var sql_str = string.Empty;
sql_str += @"
--DECLARE @Plant_Organization_UID int = 1,
-- @BG_Organization_UID int = 3,
-- @Project_Name nvarchar(50) = '',
-- @BU nvarchar(50) = '',
-- @Part_Types nvarchar(50) = '',
-- @Product_Phase nvarchar(50) = '',
-- @Is_Closed bit = null,
-- @Start_Date nvarchar(50) = '',
-- @End_Date nvarchar(50) = ''
--;
SELECT a.[RP_Flowchart_Master_UID],
h.BU_Name + '-' +e.BU_D_Name as BU,
d.[Project_UID],
d.[Project_Name],
a.[Part_Types],
a.[Product_Phase],
a.[Plant_Organization_UID],
b.[Organization_Name] as Plant_Organization_Name,
a.[BG_Organization_UID],
c.[Organization_Name] as [BG_Organization_Name],
a.[Daily_Targetoutput],
a.[FPY],
a.[FlowChart_Version],
a.[FlowChart_Version_Comment],
a.[Created_UID],
f.[User_Name] as [Created_UserName],
a.[Created_Date],
a.[Modified_UID],
g.[User_Name] as [Modified_UserName],
a.[Modified_Date]
FROM [dbo].[RP_Flowchart_Master] a
LEFT JOIN [dbo].[System_Organization] b ON a.[Plant_Organization_UID] = b.[Organization_UID]
LEFT JOIN [dbo].[System_Organization] c ON a.[BG_Organization_UID] = c.[Organization_UID]
INNER JOIN [dbo].[System_Project] d ON a.[Project_UID] = d.[Project_UID]
INNER JOIN [dbo].[System_BU_D] e ON d.[BU_D_UID] = e.[BU_D_UID]
INNER JOIN [dbo].[System_Users] f ON a.[Created_UID] = f.[Account_UID]
INNER JOIN [dbo].[System_Users] g ON a.[Modified_UID] = g.[Account_UID]
INNER JOIN [dbo].[System_BU_M] h ON e.[BU_M_UID] = h.[BU_M_UID]
WHERE [Is_Latest] = 1 AND [Is_Closed] = 0
AND (a.[Plant_Organization_UID] = @Plant_Organization_UID OR @Plant_Organization_UID = 0)
AND (a.[BG_Organization_UID] = @BG_Organization_UID OR @BG_Organization_UID = 0)
AND (d.[Project_Name] like '%'+ @Project_Name +'%' OR ISNULL(@Project_Name,'') = '')
AND (h.BU_Name like N'%'+ @BU +'%' OR e.BU_D_Name like N'%'+ @BU +'%' OR ISNULL(@BU,'') = '')
AND (a.[Part_Types] like '%'+ @Part_Types +'%' OR ISNULL(@Part_Types,'') = '')
AND (a.[Product_Phase] like N'%'+ @Product_Phase +'%' OR ISNULL(@Product_Phase,'') = '')
AND (a.[Is_Closed] = @Is_Closed OR ISNULL(@Is_Closed,'') = '')
AND ((@Start_Date <= CONVERT(char(10),a.[Modified_Date],126) OR ISNULL(@Start_Date,'') = '') and
(@End_Date >= CONVERT(char(10),a.[Modified_Date],126) OR ISNULL(@End_Date,'') = ''))
";
query = context.Database.SqlQuery <RP_ME_VM>(sql_str, parameter).ToList();
}
count = query.Count();
return(query);
}
public SPPContext Get()
{
return(dataContext ?? (dataContext = new SPPContext()));
}
public void ExecToDoOne()
{
//治具逾期提醒By'P'
using (var context = new SPPContext())
{
var sql = GetBatchSql();
//获取Maintenance_Type = 'P'的超期未保养治具列表
var list = context.Database.SqlQuery <FixtureBatchVM>(sql).ToList();
List <int> idList = new List <int>();
foreach (var item in list)
{
var dateStr = item.Last_Execution_Date.Value.ToString("yyyyMMdd");
var uidList = context.Fixture_Maintenance_Record.Where(m => m.Fixture_Maintenance_Profile_UID == item.Fixture_Maintenance_Profile_UID &&
m.Maintenance_Record_NO.Contains(dateStr) && m.Maintenance_Person_Number == null).Select(m => m.Fixture_M_UID).ToList();
idList.AddRange(uidList);
}
List <Fixture_Resume> ResumeList = new List <Fixture_Resume>();
var Fixture_M_List = context.Fixture_M.Where(m => idList.Contains(m.Fixture_M_UID)).ToList();
int i = 1;
foreach (var item in Fixture_M_List)
{
item.Status = StructConstants.FixtureStatus.StatusSix;
item.Modified_UID = ConstConstants.AdminUID;
item.Modified_Date = DateTime.Now;
var seq = i.ToString().PadLeft(4, '0');
var seqNo = string.Format("OP_{0}_{1}", DateTime.Now.ToString("yyyyMMdd"), seq);
ResumeList.Add(new Fixture_Resume {
Fixture_M_UID = item.Fixture_M_UID,
Data_Source = "6",
Resume_Date = item.Modified_Date,
Source_UID = item.Fixture_M_UID,
Source_NO = seqNo,
Resume_Notes = "週期保養逾時",
Modified_UID = ConstConstants.AdminUID,
Modified_Date = item.Modified_Date
});
i++;
}
context.Fixture_Resume.AddRange(ResumeList);
context.SaveChanges();
}
//治具逾期保养By'D'
using (var context = new SPPContext())
{
var sql = GetBatchSqlByD();
//获取Maintenance_Type = 'D'的超期未保养治具列表
var list = context.Database.SqlQuery <FixtureBatchVM>(sql).ToList();
List <int> idList = new List <int>();
foreach (var item in list)
{
var dateStr = item.Last_Execution_Date.Value.ToString("yyyyMMdd");
var uidList = context.Fixture_Maintenance_Record.Where(m => m.Fixture_Maintenance_Profile_UID == item.Fixture_Maintenance_Profile_UID &&
m.Maintenance_Record_NO.Contains(dateStr) && m.Maintenance_Person_Number == null).Select(m => m.Fixture_M_UID).ToList();
idList.AddRange(uidList);
}
List <Fixture_Resume> ResumeList = new List <Fixture_Resume>();
var Fixture_M_List = context.Fixture_M.Where(m => idList.Contains(m.Fixture_M_UID)).ToList();
int i = 1;
foreach (var item in Fixture_M_List)
{
item.Status = StructConstants.FixtureStatus.StatusSix;
item.Modified_UID = ConstConstants.AdminUID;
item.Modified_Date = DateTime.Now;
var seq = i.ToString().PadLeft(4, '0');
var seqNo = string.Format("OD_{0}_{1}", DateTime.Now.ToString("yyyyMMdd"), seq);
ResumeList.Add(new Fixture_Resume
{
Fixture_M_UID = item.Fixture_M_UID,
Data_Source = "6",
Resume_Date = item.Modified_Date,
Source_UID = item.Fixture_M_UID,
Source_NO = seqNo,
Resume_Notes = "週期保養逾時",
Modified_UID = ConstConstants.AdminUID,
Modified_Date = item.Modified_Date
});
i++;
}
context.Fixture_Resume.AddRange(ResumeList);
context.SaveChanges();
}
}
public List <BatchExecVM> ExecBatch()
{
var Excetion_Email_UID = Convert.ToInt32(ConfigurationManager.AppSettings["Excetion_Email_UID"]);
var functionName = StructConstants.BatchModuleName.EmailFunctionName;
List <BatchExecVM> matchList = new List <BatchExecVM>();
using (var context = new SPPContext())
{
try
{
string strSql = @"SELECT B.*,C.Function_Name FROM dbo.System_Module A
JOIN dbo.System_Schedule B ON B.System_Module_UID = A.System_Module_UID
JOIN dbo.System_Function C ON B.Function_UID = C.Function_UID
WHERE A.Is_Enable = 1 AND B.Is_Enable = 1 AND C.Function_Name != '{0}'";
strSql = string.Format(strSql, functionName);
var list = context.Database.SqlQuery <BatchExecVM>(strSql).ToList();
//进行时间点对比
StringBuilder sb = new StringBuilder();
var nowDate = DateTime.Now;
DateTime? nextDate = null;
foreach (var item in list)
{
Excetion_Email_UID = item.System_Schedule_UID;
//年月日时分对比,因为WindowsService间隔是每分钟一次，所以不会有误差
if (
item.Next_Execution_Date.Year == nowDate.Year &&
item.Next_Execution_Date.Month == nowDate.Month &&
item.Next_Execution_Date.Day == nowDate.Day &&
item.Next_Execution_Date.Hour == nowDate.Hour &&
item.Next_Execution_Date.Minute == nowDate.Minute
)
{
matchList.Add(item);
if (item.Exec_Moment.ToUpper() == "Month_End".ToUpper())
{
var execTimeList = item.Exec_Time.Split(',').ToList();
var nowHour = nowDate.Hour;
var nowMinute = nowDate.Minute;
for (int i = 0; i < execTimeList.Count(); i++)
{
var timeList = execTimeList[i].Split(':').ToList();
var execTimeHour = Convert.ToInt32(timeList[0]);
var execTimeMinute = Convert.ToInt32(timeList[1]);
//如果当前的时间点不是最后执行的时间点
if (nowHour == execTimeHour && nowMinute == execTimeMinute && (i != execTimeList.Count() - 1))
{
var nextTimeList = execTimeList[i + 1].Split(':').ToList();
var nextExecTimeHour = Convert.ToInt32(nextTimeList[0]);
var nextExecTimeMinute = Convert.ToInt32(nextTimeList[1]);
nextDate = item.Next_Execution_Date.Date.AddHours(nextExecTimeHour).AddMinutes(nextExecTimeMinute);
break;
}
//如果当前的时间点是最后执行的时间点
if (nowHour == execTimeHour && nowMinute == execTimeMinute && (i == execTimeList.Count() - 1))
{
var firstTimeList = execTimeList[0].Split(':').ToList();
var firstExecTimeHour = Convert.ToInt32(firstTimeList[0]);
var firstExecTimeMinute = Convert.ToInt32(firstTimeList[1]);
//下个月的第一天
nextDate = item.Next_Execution_Date.AddDays(1);
//下个月的最后一天
nextDate = nextDate.Value.Date.AddDays(1 - nextDate.Value.Day).AddMonths(1).AddDays(-1).AddHours(firstExecTimeHour).AddMinutes(firstExecTimeMinute);
break;
}
}
//下个月的第一天
//nextDate = item.Next_Execution_Date.AddDays(1);
//下个月的最后一天
//nextDate = nextDate.Value.AddDays(1 - nextDate.Value.Day).AddMonths(1).AddDays(-1);
}
else
{
//更新System_Schedule表的数据
switch (item.Cycle_Unit)
{
case "H": //按小时
nextDate = emailRepository.GetHourTime(item, nowDate);
break;
case "W": //按周
nextDate = emailRepository.GetWeekDay(item, nowDate);
break;
case "M": //按月
nextDate = emailRepository.GetMonthDay(item, nowDate);
break;
}
}
//更新下次执行日期Next_Execution_Date
var strUpdate = @"UPDATE dbo.System_Schedule SET Last_Execution_Date = GETDATE(),
Next_Execution_Date = '{1}',
Modified_Date = GETDATE(),
Modified_UID = 99999
WHERE System_Schedule_UID = {0}; ";
strUpdate = string.Format(strUpdate, item.System_Schedule_UID, nextDate);
sb.AppendLine(strUpdate);
}
}
if (sb.Length > 0)
{
context.Database.ExecuteSqlCommand(sb.ToString());
}
foreach (var item in matchList)
{
//执行完毕后写入日志
Batch_Log newLog = new Batch_Log
{
System_Schedule_UID = item.System_Schedule_UID,
Batch_Name = item.Function_Name + "执行成功",
Batch_Status = true,
Batch_Desc = item.Function_Name + "执行成功",
Batch_Date = DateTime.Now
};
emailRepository.InsertBatchLog(newLog);
}
}
catch (Exception ex)
{
emailRepository.InsertExceptionBatchLog(Excetion_Email_UID, ex.Message);
}
return(matchList);
}
}
