Newer
Older
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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Sqcy.Model.Model;
using Sqcy.Service.Impl;
using Sqcy.Service;
using Hxj.Data;
using System.Data.SqlClient;
using Sqcy.Page;
public partial class UpdateKuCun : MyPage
{
Kenanfans.KDO.SqlDataBaseOperator _Sdbo = null;
private ServiceGoods sg = ServiceHelper.GetGoodsService();
private ServiceGoodsType st = ServiceHelper.GetGoodsTypeService();
protected void Page_Load(object sender, EventArgs e)
{
_Sdbo = DataService.Sdbo;
if (!IsPostBack)
{
string goodsid = Request.QueryString["GoodsID"].ToString();
DataTable dt = sg.GetGoods(goodsid);
MdlGoodsType mdl1 = new MdlGoodsType();
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
mdl1 = st.GetGoodsType(dt.Rows[i]["SmallTypeID"].ToString());
}
}
// string xgkucun = this.txtNum.Value;//修改后的库存
string pandianriKC = Request.QueryString["PandianriKC"].ToString();//盘点日库存
if (mdl1 != null)
{
if (mdl1.LossRate.ToString() != "" && Convert.ToDecimal(pandianriKC) != 0)
{
//decimal num = (((Convert.ToDecimal(xgkucun) - Convert.ToDecimal(pandianriKC)) / Convert.ToDecimal(pandianriKC)) * 100) - Convert.ToDecimal(mdl1.LossRate);
hfUserType.Value = pandianriKC;
hfUserType1.Value = mdl1.LossRate.ToString();
}
}
}
String act = Request.QueryString["Action"];
String msg = "";
JSONObject jo = new JSONObject();
jo.Add("Action", act);
string strAction = Action.Value;
if (strAction == "save")
{
string goodsid = Request.QueryString["GoodsID"].ToString();
string deliveryID = Request.QueryString["DeliveryID"].ToString();
string pandianDate = Request.QueryString["PandianDate"].ToString();//盘点日期
string xgkucun = this.txtNum.Value;//修改后的库存
string pandianriKC = Request.QueryString["PandianriKC"].ToString();//盘点日库存
//decimal kcsl = Convert.ToDecimal(GetGoodsKC(goodsid, pandianDate, deliveryID));//获取到选中盘点日期该商品的库存总量
decimal kcsl = Convert.ToDecimal(pandianriKC);//获取到选中盘点日期该商品的库存总量
//变动数量
decimal bdsl;
bdsl = kcsl - Convert.ToDecimal(xgkucun);
SqlConnection conn = _Sdbo.GetConnection();
conn.Open();
//SqlTransaction st = conn.BeginTransaction();
try
{
SqlCommand cmd = conn.CreateCommand();
//cmd.Transaction = st;
cmd.CommandType = CommandType.Text;
String sql1 = "";
decimal left = bdsl;
if (left > 0)//领料(当明细中的库存总量大于要修改为的库存数量时如：100 => 80 则说明要从总的明细中 领掉20 属于 领料行为)
{
//保存领料信息
Guid InventoryID = Guid.NewGuid();
string date = DateTime.Now.ToString("yyyyMMdd");
sql1 = String.Format("INSERT INTO Warehouse.Inventory VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}')", InventoryID, MySession.UserGroupID, MySession.UserID, StorageType.OUT, DateTime.Now, "库存调整", Guid.NewGuid(), Guid.NewGuid(), Guid.NewGuid(), Guid.NewGuid(), "", date, deliveryID);
cmd.CommandText = sql1;
int i1 = cmd.ExecuteNonQuery();
decimal totalPrice = 0;
//***********************找出该配送点该商品库存数量List*******************************//
string sqlkc1 = @"select a.InventoryID,InvertoryLast,InvertoryPrice from Warehouse.Inventory a left join Warehouse.InventoryDetail b on a.InventoryID=b.InventoryID
where GoodsID='{0}' and DeliveryID='{1}' and InvertoryLast>0 and [Type]='入库' order by InventoryTime asc";//按照时间正序，查出入库的产品及其明细，返回不同时期产品对应的库存及其单价
DataTable dt = _Sdbo.ExecuteDataTable(sqlkc1, goodsid, deliveryID);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (left > Convert.ToDecimal(dt.Rows[i][1]))//当领料数量大于第一条入库记录的库存数量时，则按照此条记录中产品的报价计算
{
left = left - Convert.ToDecimal(dt.Rows[i][1]);//计算出剩余要领料的数量
totalPrice = totalPrice + Convert.ToDecimal(dt.Rows[i][1]) * Convert.ToDecimal(dt.Rows[i][2]);
sql1 = String.Format("update Warehouse.InventoryDetail set InvertoryLast='{0}' where InventoryID='{1}' and GoodsID='{2}'", 0, dt.Rows[i][0], goodsid);
cmd.CommandText = sql1;
int j = cmd.ExecuteNonQuery();
}
else
{
totalPrice = totalPrice + Convert.ToDecimal(dt.Rows[i][2]) * left;//剩余的数量按照对应的第‘i’条入库记录 的价格 计算
sql1 = String.Format("update Warehouse.InventoryDetail set InvertoryLast='{0}' where InventoryID='{1}' and GoodsID='{2}'", Convert.ToDecimal(dt.Rows[i][1]) - left, dt.Rows[i][0], goodsid);
cmd.CommandText = sql1;
int t = cmd.ExecuteNonQuery();
break;
}
}
//领料时产生一条领料明细记录插入明细表中，其中明细中的InventoryPrice 为总价（即：数量*单价 的最后总和）
sql1 = String.Format("INSERT INTO Warehouse.InventoryDetail VALUES ('{0}','{1}',{2},'{3}','{4}',{5},'{6}')", InventoryID, goodsid, left, DateTime.Now, totalPrice, 0, 0);
cmd.CommandText = sql1;
int j1 = cmd.ExecuteNonQuery();
//修改库存
//库存中减去领料数量
//***********************找出该配送点该商品库存明细中入库的总库存量*******************************//
string sqlkc = @"select sum(InvertoryLast) as ZongLast from Warehouse.Inventory a left join Warehouse.InventoryDetail b on a.InventoryID=b.InventoryID
where GoodsID='{0}' and DeliveryID='{1}' and InvertoryLast>0 and [Type]='入库'";
DataRow dr = _Sdbo.ExecuteDataRow(sqlkc, goodsid, deliveryID);
Decimal il;
if (dr != null && dr[0].ToString() != "")
il = Convert.ToDecimal(dr[0].ToString());
else
il = 0;
Decimal xgkc = Convert.ToDecimal(xgkucun);
DataRow dr1 = _Sdbo.ExecuteDataRow("select * from dbo.StorageInfo where GoodsID='{0}' and DeliveryID='{1}' ", goodsid, deliveryID);
if (dr1 != null)
{
sql1 = String.Format("update dbo.StorageInfo set ACount={0} where GoodsID='{1}' and DeliveryID='{2}' ", il, goodsid, deliveryID);
cmd.CommandText = sql1;
int t1 = cmd.ExecuteNonQuery();
}
jo.Add("status", "success");
decimal xghzkc = 0;//修改后库存总量
sql1 = "select ACount from dbo.StorageInfo where GoodsID='{0}' and DeliveryID='{1}' ";
DataRow dr2 = _Sdbo.ExecuteDataRow(sql1, goodsid, deliveryID);//修改后查出总库存和 盘点日库存
if (dr2 != null && dr2["ACount"].ToString() != "")
{
xghzkc = Convert.ToDecimal(dr2["ACount"]);
}
//往调整日志表里查数据
sql1 = "INSERT INTO dbo.KCTZDetail VALUES ('{0}','{1}','{2}',{3},{4},{5},'{6}','{7}','{8}')";
_Sdbo.ExecuteNonQuery(sql1, Guid.NewGuid().ToString(), MySession.UserID, DateTime.Now, kcsl, xgkucun, Convert.ToDecimal(xgkucun) - kcsl, pandianDate,this.txtMemo.Value,InventoryID);
string price = GetGoodsZJE(goodsid, deliveryID);
msg = goodsid + "@" + xghzkc + "/" + kcsl + '$' + price;//返回变动数值 格式为 goodsid@数值
jo.Add("msg", msg);
//Response.Write(JSONConvert.SerializeObject(jo));
ClientScript.RegisterStartupScript(this.GetType(), "保存成功！", "<script language='javascript'>window.parent.CBClose('" + goodsid + "','" + xgkucun + "');</script>");
}
else if (left < 0)//入库：(当明细中的库存总量小于要修改为的库存数量时如：80 => 100 则说明要向总的明细中 入库20 属于 入库行为)
{
left = -left;
//分两种情况：判断是否为特殊采购：
//
//
string sqlprice = @"select Price from Supplier.PriceSheet A inner join Supplier.PriceSheetDetail B on A.PriceSheetID=B.PriceSheetID
where GoodsID='{0}' order by EndTime desc";
DataRow dt = _Sdbo.ExecuteDataRow(sqlprice, goodsid);
Guid InventoryID = Guid.NewGuid();
string date = DateTime.Now.ToString("yyyyMMdd");
Decimal dc = 0;
if (dt == null)//如果报价表与报价明细表中该商品的记录为空 则为特殊采购的商品
{
string sqlts = @"select InvertoryPrice from Warehouse.Inventory a left join Warehouse.InventoryDetail b on a.InventoryID=b.InventoryID
where GoodsID='{0}' and DeliveryID='{1}' and [Type]='入库' order by InventoryTime desc";//按照时间正序，查出入库的产品及其明细，返回不同时期产品对应的库存及其单价
DataRow dt1 = _Sdbo.ExecuteDataRow(sqlts, goodsid, deliveryID);
dc = Convert.ToDecimal(dt1[0].ToString());
}
else
{
dc = Convert.ToDecimal(dt[0].ToString());
}
//入库时产生一条入库记录插入明细表及产品表中，其中InvertoryPrice 为最近一次的最新报价（即：dc）
sql1 = String.Format("INSERT INTO Warehouse.Inventory VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", InventoryID, MySession.UserGroupID, MySession.UserID, StorageType.IN, DateTime.Now, "库存调整", Guid.NewGuid(), Guid.NewGuid(), Guid.NewGuid(), Guid.NewGuid(), "", date, deliveryID);
cmd.CommandText = sql1;
int i1 = cmd.ExecuteNonQuery();
sql1 = String.Format("INSERT INTO Warehouse.InventoryDetail VALUES ('{0}','{1}','{2}','{3}','{4}',{5},'{6}')", InventoryID, goodsid, left, DateTime.Now, dc, 0, left);
cmd.CommandText = sql1;
int j1 = cmd.ExecuteNonQuery();
//***********************找出该配送点该商品库存明细中入库的总库存量*******************************//
string sqlkc = @"select sum(InvertoryLast) as ZongLast from Warehouse.Inventory a left join Warehouse.InventoryDetail b on a.InventoryID=b.InventoryID
where GoodsID='{0}' and DeliveryID='{1}' and InvertoryLast>0 and [Type]='入库'";
DataRow dr = _Sdbo.ExecuteDataRow(sqlkc, goodsid, deliveryID);
Decimal il;
if (dr != null && dr[0].ToString() != "")
il = Convert.ToDecimal(dr[0].ToString());
else
il = 0;
Decimal xgkc = Convert.ToDecimal(xgkucun);
//修改总库存
DataRow dr1 = _Sdbo.ExecuteDataRow("select * from dbo.StorageInfo where GoodsID='{0}' and DeliveryID='{1}' ", goodsid, deliveryID);
if (dr1 != null)
{
sql1 = String.Format("update dbo.StorageInfo set ACount={0} where GoodsID='{1}' and DeliveryID='{2}' ", il, goodsid, deliveryID);
cmd.CommandText = sql1;
int t1 = cmd.ExecuteNonQuery();
}
else
{
sql1 = String.Format("insert into dbo.StorageInfo values('{0}','{1}','{2}','{3}') ", goodsid, deliveryID, il - bdsl, null);
cmd.CommandText = sql1;
int t1 = cmd.ExecuteNonQuery();
}
jo.Add("status", "success");
decimal xghzkc = 0;//修改后库存总量
sql1 = "select ACount from dbo.StorageInfo where GoodsID='{0}' and DeliveryID='{1}' ";
DataRow dr2 = _Sdbo.ExecuteDataRow(sql1, goodsid, deliveryID);//修改后查出总库存和 盘点日库存
if (dr2 != null && dr2["ACount"].ToString() != "")
{
xghzkc = Convert.ToDecimal(dr2["ACount"]);
}
//往调整日志表里查数据
sql1 = "INSERT INTO dbo.KCTZDetail VALUES ('{0}','{1}','{2}',{3},{4},{5},'{6}','{7}','{8}')";
_Sdbo.ExecuteNonQuery(sql1, Guid.NewGuid().ToString(), MySession.UserID, DateTime.Now, kcsl, xgkucun, Convert.ToDecimal(xgkucun) - kcsl, pandianDate, this.txtMemo.Value, InventoryID);
string price = GetGoodsZJE(goodsid, deliveryID);
msg = goodsid + "@" + xghzkc + "/" + kcsl + "$" + price;//返回变动数值 格式为 goodsid@数值
jo.Add("msg", msg);
//Response.Write(JSONConvert.SerializeObject(jo));
ClientScript.RegisterStartupScript(this.GetType(), "保存成功！", "<script language='javascript'>window.parent.CBClose('" + goodsid + "','" + xgkucun + "');</script>");
}
else
{
jo.Add("status", "falie");
msg = "请重新输入，要修改的库存数不能与原库存数重复！";
jo.Add("msg", msg);
Response.Write(JSONConvert.SerializeObject(jo));
}
}
catch { }
}
}
/// <summary>
/// 获取 总金额
/// </summary>
/// <param name="goodsID"></param>
/// <param name="deliveryID"></param>
/// <param name="totalLast"></param>
/// <returns></returns>
private string GetGoodsZJE(string goodsID, string deliveryID)
{
decimal Price = 0;
string sql = @"select a.InventoryID,InvertoryLast*InvertoryPrice as Price from Warehouse.InventoryDetail a left join Warehouse.Inventory b on a.InventoryID=b.InventoryID
where GoodsID='{0}' and DeliveryID='{1}' and Type='入库' and InvertoryLast>0 order by InventoryTime desc ";
DataTable dt = _Sdbo.ExecuteDataTable(sql, goodsID, deliveryID);
foreach (DataRow dr in dt.Rows)
{
Price += Convert.ToDecimal(dr["Price"]);
}
return String.Format("{0:F}", Price);
}
}