InventoryAlertAction.cs
3.46 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
using System;
using System.Collections.Generic;
using System.Data;
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)
{
DbHelp dbHelp = new DbHelp(ConnString);
try
{
string sql = "";
string sqlia = "";
string sqliac = "";
string sqlup = "";
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;
}
}
}
}