InventoryAlertAction.cs
5.3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Infrastructure;
using Quartz;
using WebApp;
using WebRepository;
namespace WebMvc
{
/// <summary>
/// 库存预警
/// </summary>
[PersistJobDataAfterExecution]
[DisallowConcurrentExecution]
public class InventoryAlertAction
{
private string ConnString { set; get; }
IJobExecutionContext Context { set; get; }
public InventoryAlertAction(string _ConnString, IJobExecutionContext _Context)
{
ConnString = _ConnString;
Context = _Context;
}
public void Execute(JobContainer jobContainer)
{
UnitWork _unitWork = new UnitWork(AppSettingsJson.JobContext(ConnString));
DbHelp dbHelp = new DbHelp(ConnString);
try
{
string sql = "";
string sqlia = "";
string sqliac = "";
string sqlup = "";
List<Inventory> inventories = _unitWork.Find<Inventory>(n => n.CreateTime < DateTime.Now.AddDays(-1 * double.Parse(60.ToString()))).ToList();
List<InventoryAlert> inventoryAlerts = _unitWork.Find<InventoryAlert>(n => n.HadQty != 0).ToList();
if (inventories.Any())
{
foreach (var inventorie in inventories)
{
DateTime d1 = (DateTime)inventorie.CreateTime;
DateTime d2 = DateTime.Now;
TimeSpan d3 = d2 - d1;
InventoryAlert IsinventoryAlert = _unitWork.Find<InventoryAlert>(n => n.HadQty == inventorie.Id).FirstOrDefault();
if (IsinventoryAlert == null)
{
InventoryAlert inventoryAlert = new InventoryAlert()
{
ShipmentCode = inventorie.ContainerCode,
MaterialCode = inventorie.WarehouseType,
Qty = d3.Days,
HadQty = inventorie.Id,
CreateTime = inventorie.CreateTime
};
_unitWork.Add(inventoryAlert);
}
}
}
foreach (var InvAlert in inventoryAlerts)
{
Inventory Inv = _unitWork.Find<Inventory>(n => n.Id == InvAlert.HadQty).FirstOrDefault();
if (Inv == null)
{
_unitWork.Delete(InvAlert);
}
}
//sql = string.Format(@"SELECT A.code MaterialCode, A.type Type, A.name MaterialName, A.alertNum AlertNum,(SELECT SUM(B.qty) FROM dbo.inventory B
// WHERE A.code = B.materialCode AND B.qty <> 0
// GROUP BY B.materialCode) Num FROM dbo.material A
// WHERE (SELECT SUM(B.qty) FROM dbo.inventory B
// WHERE A.code = B.materialCode AND B.qty <> 0
// GROUP BY B.materialCode) <= A.alertNum
// AND A.code NOT IN (SELECT DISTINCT materialCode FROM dbo.inventory_Alert)");
//DataSet ds = dbHelp.SelectGet(sql);
//sqliac = string.Format(@"DELETE dbo.inventory_Alert WHERE materialCode NOT IN (SELECT A.materialCode MaterialCode FROM dbo.inventory A
// JOIN dbo.material B ON A.materialCode = B.code
// GROUP BY A.materialCode
// HAVING (SELECT C.alertNum FROM dbo.material C WHERE C.code = A.materialCode) >= SUM(A.qty) AND SUM(A.qty) <> 0)");
//dbHelp.DataOperator(sqliac);
//sqlup = string.Format(@"UPDATE dbo.inventory_Alert
// SET inventoryNum = (SELECT SUM(B.qty) FROM dbo.inventory B
// GROUP BY B.materialCode HAVING (SELECT A.inventoryNum FROM dbo.inventory_Alert A WHERE A.materialCode = B.materialCode) <> SUM(B.qty))
// WHERE materialCode = (SELECT B.materialCode FROM dbo.inventory B
// GROUP BY B.materialCode HAVING (SELECT A.inventoryNum FROM dbo.inventory_Alert A WHERE A.materialCode = B.materialCode) <> SUM(B.qty))");
//dbHelp.DataOperator(sqlup);
//foreach (DataRow dr in ds.Tables[0].Rows)
//{
// sqlia = string.Format(@"INSERT INTO dbo.inventory_Alert (materialCode, type, materialName, inventoryNum, alertNum, createTime, updateTime)
// VALUES ('{0}', '{1}', '{2}', {3}, {4}, '{5}', '{6}')", dr["MaterialCode"].ToString(), dr["Type"].ToString()
// , dr["MaterialName"].ToString(), dr["Num"].ToString(), dr["AlertNum"].ToString(), DateTime.Now.ToString(), DateTime.Now.ToString());
// dbHelp.DataOperator(sqlia);
//}
}
catch (Exception ex)
{
throw ex;
}
}
}
}