You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1213 lines
51 KiB
1213 lines
51 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using ICSSoft.Frame.Data.Entity;
|
|
using ICSSoft.Base.Config.AppConfig;
|
|
using System.Data;
|
|
using ICSSoft.Base.Config.DBHelper;
|
|
using System.Data.SqlClient;
|
|
|
|
namespace ICSSoft.Frame.Data.DAL
|
|
{
|
|
public static class StockBarCodeDAL
|
|
{
|
|
public static void ImportData(List<InitialInvtory> list,string ErpName) {
|
|
SqlConnection conn = new System.Data.SqlClient.SqlConnection(AppConfig.AppConnectString);
|
|
conn.Open();
|
|
SqlTransaction sqlTran = conn.BeginTransaction();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.Transaction = sqlTran;
|
|
cmd.Connection = conn;
|
|
try {
|
|
foreach(InitialInvtory init in list){
|
|
string sql = @"SELECT cInvCode FROM {0}.dbo.CurrentStock
|
|
WHERE cInvCode = '{1}' AND cBatch = '{2}' AND cWhCode = '{3}'
|
|
AND iQuantity>='{4}'";
|
|
sql = string.Format(sql, ErpName,init.ItemCode, init.BatchCode, init.WhCode,init.Qty);
|
|
cmd.CommandText = sql;
|
|
DataTable currDt = SQlReturnData(cmd);
|
|
if (currDt==null||currDt.Rows.Count==0)
|
|
{
|
|
throw new Exception("物料: " + init.ItemCode + "批号: " + init.BatchCode + "仓库: " + init.WhCode + "ERP现存量不足");
|
|
}
|
|
|
|
sql = @"INSERT INTO [dbo].[ICSPurchasingStorage]
|
|
([Serial]
|
|
,[ItemCode]
|
|
,[ItemName]
|
|
,[ItemStd]
|
|
,[VenCode]
|
|
,[VenName]
|
|
,[ComUnit]
|
|
,[Rd01AutoID]
|
|
,[AssComUnit]
|
|
,[dDate]
|
|
,[BatchCode]
|
|
,[Quantity]
|
|
,[OutQuantity]
|
|
,[PackQuantiy]
|
|
,[Mtime]
|
|
,[Muser]
|
|
,[cFree1]
|
|
,[cFree2]
|
|
,[WorkPoint])
|
|
SELECT NEWID(),cInvCode,cInvName,cInvStd,a.cVenCode AS 供应商编码,a.cVenName AS 供应商名称,
|
|
c.cComUnitName,'',d.cComUnitName,GETDATE(),'{1}' AS 批号,'{2}' AS 数量,'0','0',GETDATE(),
|
|
'{3}' AS 维护人,'{6}','','0001' FROM {0}.dbo.Inventory b
|
|
LEFT JOIN {0}.dbo.ComputationUnit c ON c.cComUnitCode = b.cComunitCode
|
|
LEFT JOIN {0}.dbo.ComputationUnit d ON d.cComUnitCode = b.cAssComUnitCode
|
|
,{0}.dbo.Vendor a
|
|
WHERE cInvCode = '{4}' AND a.cVenCode = '{5}'";
|
|
sql = string.Format(sql, ErpName,init.BatchCode,init.Qty,AppConfig.UserName,init.ItemCode,init.VenCode,init.WhCode);
|
|
cmd.CommandText = sql;
|
|
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
cmd.Transaction.Commit();
|
|
|
|
}catch(Exception ex){
|
|
cmd.Transaction.Rollback();
|
|
throw ex;
|
|
}
|
|
|
|
}
|
|
|
|
public static void stackMOPicking(string barCodeStr,Rd11Context Context, string MoNo)
|
|
{
|
|
string sql = "";
|
|
string ConnectString = AppConfig.AppConnectString;
|
|
|
|
///产成品入库单的CardNumber 0411
|
|
#region 默认代码
|
|
Context.ERPCardNumber = "0412";
|
|
#endregion
|
|
|
|
#region 取得out库单的默认显示模版
|
|
sql = @" SELECT DEF_ID FROM {0}.dbo.Vouchers WHERE CardNumber = '{1}' ";
|
|
sql = string.Format(sql, Context.ERPDataName, Context.ERPCardNumber);
|
|
DataTable DEF_ID = DBHelper.ExecuteDataset(ConnectString, CommandType.Text, sql).Tables[0];
|
|
if (DEF_ID.Rows.Count == 0)
|
|
{
|
|
throw new Exception("产成品入库单的默认显示模版取得失败");
|
|
}
|
|
Context.ERPDEF_ID = DEF_ID.Rows[0]["DEF_ID"].ToString();
|
|
DEF_ID.Clear();
|
|
#endregion
|
|
|
|
SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConnectString);
|
|
conn.Open();
|
|
SqlTransaction sqlTran = conn.BeginTransaction();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.Transaction = sqlTran;
|
|
cmd.Connection = conn;
|
|
try
|
|
{
|
|
sql = @"SELECT BarCode
|
|
FROM [dbo].[ICSStockBarCode]
|
|
WHERE BarCode IN ({0}) AND Status !='入库'";
|
|
sql = string.Format(sql, barCodeStr);
|
|
cmd.CommandText = sql;
|
|
DataTable bDt = SQlReturnData(cmd);
|
|
if (bDt != null && bDt.Rows.Count > 0)
|
|
{
|
|
throw new Exception("请选择状态为入库的条码进行出库!!");
|
|
}
|
|
|
|
sql = @"SELECT DISTINCT b.Serial,b.StorageCode FROM dbo.ICSStorageInfo a
|
|
INNER JOIN dbo.ICSStorage b ON a.Storage_Serial = b.Serial
|
|
WHERE a.BarCode IN (" + barCodeStr + ")";
|
|
cmd.CommandText = sql;
|
|
DataTable whDt = SQlReturnData(cmd);
|
|
for (int w = 0; w < whDt.Rows.Count;w++ )
|
|
{
|
|
#region 汇总信息
|
|
sql = @"SELECT ROW_NUMBER() OVER(ORDER BY a.ItemCode) AS row,
|
|
CAST(0 AS DECIMAL(18,6)) AS qty,s.StorageCode,
|
|
CAST(0 AS DECIMAL(18,6)) AS Numqty,
|
|
a.ItemCode,0 as erpdid,
|
|
loc.AllocateId,loc.SortSeq,
|
|
inv.cComUnitCode,inv.cAssComUnitCode,
|
|
c.iChangRate,info.Storage_Serial into ##rdqty
|
|
FROM ICSStockBarCode a
|
|
LEFT JOIN ICSPurchasingStorage b ON a.cFree1=b.Serial
|
|
LEFT JOIN ICSStorageInfo info ON a.BarCode = info.BarCode
|
|
LEFT JOIN dbo.ICSStorage s ON info.Storage_Serial = s.Serial
|
|
LEFT JOIN {0}.dbo.ComputationUnit c ON b.AssComUnit = c.cComunitCode
|
|
LEFT JOIN {0}.dbo.Inventory inv ON a.ItemCode=inv.cInvCode
|
|
left join (select * from {0}.dbo.mom_moallocate
|
|
where MoDId = '{3}') loc on a.ItemCode = loc.InvCode
|
|
WHERE a.BarCode IN ({1}) and info.Storage_Serial = '{2}'
|
|
GROUP BY a.ItemCode,inv.cComUnitCode,inv.cAssComUnitCode
|
|
,c.iChangRate,s.StorageCode,s.StorageCode,
|
|
info.Storage_Serial,loc.AllocateId,loc.SortSeq
|
|
SELECT * FROM ##rdqty";
|
|
|
|
sql = string.Format(sql, Context.ERPDataName, barCodeStr, whDt.Rows[w]["Serial"].ToString(),
|
|
Context.MoDid);
|
|
cmd.CommandText = sql;
|
|
DataTable temp = SQlReturnData(cmd);
|
|
if(temp == null || temp.Rows.Count==0){
|
|
throw new Exception("");
|
|
}
|
|
|
|
DataTable barDt = new DataTable();
|
|
foreach (var dic in Context.Bar_Qty)
|
|
{
|
|
sql = @"SELECT a.ItemCode,c.iChangRate
|
|
FROM ICSStockBarCode a
|
|
LEFT JOIN ICSPurchasingStorage b ON a.cFree1=b.Serial
|
|
LEFT JOIN ICSStorageInfo info ON a.BarCode = info.BarCode
|
|
LEFT JOIN {0}.dbo.ComputationUnit c ON b.AssComUnit = c.cComunitCode
|
|
WHERE a.BarCode = '{1}'";
|
|
sql = string.Format(sql, Context.ERPDataName,dic.Key);
|
|
cmd.CommandText = sql;
|
|
barDt = SQlReturnData(cmd);
|
|
decimal qty = 0;
|
|
decimal sum = 0;
|
|
for (int i = 0; i < temp.Rows.Count; i++)
|
|
{
|
|
if (barDt.Rows[0]["ItemCode"].ToString() == temp.Rows[i]["ItemCode"].ToString())
|
|
{
|
|
if (barDt.Rows[0]["iChangRate"].ToString() != "" && decimal.Parse(barDt.Rows[0]["iChangRate"].ToString()) != 0)
|
|
{
|
|
qty = decimal.Parse(dic.Value) * decimal.Parse(barDt.Rows[0]["iChangRate"].ToString());
|
|
sum = decimal.Parse(dic.Value);
|
|
}
|
|
else
|
|
{
|
|
qty = decimal.Parse(dic.Value);
|
|
sum = 0;
|
|
}
|
|
|
|
sql = @"UPDATE ##rdqty SET qty =qty+ '{0}' ,Numqty = Numqty+'{1}'
|
|
WHERE ItemCode = '{2}'";
|
|
sql = string.Format(sql, qty, sum,
|
|
barDt.Rows[0]["ItemCode"].ToString());
|
|
cmd.CommandText = sql;
|
|
int a = cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 取得ERP的ID
|
|
string num = "1000000000";
|
|
sql = @"select count(*) from ##rdqty";
|
|
cmd.CommandText = sql;
|
|
DataTable RowCountDt = SQlReturnData(cmd);
|
|
Context.ERPrdRowCount = Convert.ToInt32(RowCountDt.Rows[0][0]);
|
|
int id = 0;
|
|
int did = 0;
|
|
SaveGetrdIDandAutoID(ConnectString, "材料出库", "rd", Context.ERPDataName.Substring(7, 3), Context.ERPrdRowCount, out id, out did);
|
|
string strId = id.ToString();
|
|
Context.ERPrdID = int.Parse(num.Substring(0, num.Length - strId.Length) + strId);
|
|
string strDid = did.ToString();
|
|
Context.ERPrdDid = int.Parse(num.Substring(0, num.Length - strDid.Length) + strDid);
|
|
#endregion
|
|
|
|
#region 取得ERP的Code
|
|
sql = @"DECLARE @Code nvarchar(100)
|
|
set @Code = ''
|
|
UPDATE VoucherHistory SET cNumber = cNumber + 1
|
|
FROM {0}.dbo.VoucherHistory WHERE CardNumber = '{1}'
|
|
SELECT @Code = '0000000000' + CAST(cNumber AS NVARCHAR(50)) FROM {0}.dbo.VoucherHistory WHERE CardNumber = '{1}'
|
|
SET @Code = RIGHT(@Code,10)
|
|
select @Code";
|
|
sql = string.Format(sql, Context.ERPDataName, Context.ERPCardNumber);
|
|
cmd.CommandText = sql;
|
|
DataTable codedt = SQlReturnData(cmd);
|
|
if (codedt.Rows[0][0].ToString() == "")
|
|
{
|
|
sql = "insert into {0}.dbo.VoucherHistory values('0411',null,'日期','月','null',1,0)";
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
SQlInsertAndUpdate(sql, conn, sqlTran, cmd);
|
|
Context.ERPrdCode = "0000000001";
|
|
}
|
|
if (codedt.Rows[0][0].ToString() != "")
|
|
{
|
|
Context.ERPrdCode = codedt.Rows[0][0].ToString();
|
|
}
|
|
if (Context.ERPrdCode == "")
|
|
{
|
|
throw new Exception("产成品入库单号取得失败");
|
|
}
|
|
sql = @"IF EXISTS (SELECT cCode FROM {1}.dbo.rdrecord11 WHERE ccode = '{0}' and cVouchType = '10')
|
|
RAISERROR('产成品入库单号重复，保存失败',16,0)";
|
|
sql = string.Format(sql, Context.ERPrdCode, Context.ERPDataName);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
|
|
#region 写入表头
|
|
sql = @"INSERT INTO {0}.dbo.rdrecord11
|
|
(ID,bRdFlag,cVouchType,cBusType,cSource,cWhCode,dDate,cCode,cRdCode,cDepCode,
|
|
cHandler,cMemo,bTransFlag,cMaker,dVeriDate,bpufirst,biafirst,iMQuantity,VT_ID,bIsSTQc,
|
|
iproorderid,bOMFirst,bFromPreYear,bIsComplement,iDiscountTaxType,ireturncount,iverifystate,
|
|
iswfcontrolled,dnmaketime,dnverifytime,bredvouch,bmotran,bHYVouch,iPrintCount,cMPoCode)
|
|
VALUES
|
|
(@ID,'0','11','领料','生产订单号',@cWhCode,CONVERT(varchar(15), GETDATE(), 23) ,@cCode,@cRdCode,@cDepCode,
|
|
null,@cMemo,'0',@cMaker,null,'0','0','0',@VT_ID,'0',
|
|
@iproorderid,'0','0','0','0','0','0','0',GETDATE(),null,'0','0','0','0',@cMPoCode)";
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
cmd.CommandText = sql;
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("@ID", Context.ERPrdID);
|
|
cmd.Parameters.AddWithValue("@cWhCode", whDt.Rows[w]["StorageCode"].ToString());
|
|
cmd.Parameters.AddWithValue("@cCode", Context.ERPrdCode);
|
|
cmd.Parameters.AddWithValue("@cRdCode", Context.ERPrdcRdCode);
|
|
cmd.Parameters.AddWithValue("@cDepCode", Context.ERPDeptCode);
|
|
//cmd.Parameters.AddWithValue("@cHandler", DBNull.Value);
|
|
cmd.Parameters.AddWithValue("@cMemo", "");
|
|
cmd.Parameters.AddWithValue("@cMaker", Context.UserName);
|
|
cmd.Parameters.AddWithValue("@VT_ID", Context.ERPDEF_ID);
|
|
cmd.Parameters.AddWithValue("@iproorderid", Context.MoId);
|
|
cmd.Parameters.AddWithValue("@cMPoCode",MoNo);
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#endregion
|
|
|
|
#region 更新表体ID
|
|
sql = @"SELECT * FROM ##rdqty";
|
|
cmd.CommandText = sql;
|
|
DataTable dt = SQlReturnData(cmd);
|
|
|
|
int rowNo = 1;
|
|
for (int i = 0; i < dt.Rows.Count; i++)
|
|
{
|
|
sql = @"update ##rdqty set erpdid = {0} where row='{1}' ";
|
|
sql = string.Format(sql, Context.ERPrdDid, rowNo);
|
|
Context.ERPrdDid -= 1;
|
|
rowNo++;
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
#endregion
|
|
|
|
#region 写入表体
|
|
sql = @"INSERT INTO {0}.dbo.rdrecords11
|
|
(AutoID,ID,cInvCode,iNum,iQuantity,cFree1,cFree2,iFlag,cFree3,cAssUnit,bLPUseFree,
|
|
iOriTrackID,bCosting,bVMIUsed,iinvexchrate,iExpiratDateCalcu,iordertype,isotype,ipesotype,
|
|
irowno,iMPoIds,cmocode,imoseq,ipesodid,cpesocode,ipesoseq)
|
|
SELECT erpdid,@ID,ItemCode,Numqty,qty,'','','0','',cAssComUnitCode,'0',
|
|
'0','1','0',iChangRate,'0','0','0','0',row,AllocateId,'{1}',SortSeq,AllocateId,'{1}',SortSeq FROM ##rdqty";
|
|
sql = string.Format(sql, Context.ERPDataName,MoNo);
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("@ID", Context.ERPrdID);
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#endregion
|
|
|
|
#region 判断现存量
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
|
|
int ItemID = -1;
|
|
string invcode = dr["itemcode"].ToString();
|
|
string iinvexchrate = dr["iChangRate"].ToString();
|
|
string cWhcode = whDt.Rows[w]["StorageCode"].ToString();
|
|
double qty = double.Parse(dr["qty"].ToString());
|
|
|
|
#region 取得物料的itemID
|
|
sql = @"IF NOT EXISTS(
|
|
SELECT Id FROM {0}.dbo.SCM_Item WHERE
|
|
cinvcode = '{1}')
|
|
BEGIN
|
|
INSERT INTO {0}.dbo.SCM_Item(cInvCode ,
|
|
cFree1 ,
|
|
cFree2 ,
|
|
cFree3 ,
|
|
cFree4 ,
|
|
cFree5 ,
|
|
cFree6 ,
|
|
cFree7 ,
|
|
cFree8 ,
|
|
cFree9 ,
|
|
cFree10 ,
|
|
PartId) VALUES('{1}','','','','','','','','','','',0)
|
|
|
|
END
|
|
SELECT Id FROM
|
|
{0}.dbo.SCM_Item WHERE
|
|
cinvcode = '{1}'";
|
|
|
|
sql = string.Format(sql, Context.ERPDataName, invcode);
|
|
cmd.CommandText = sql;
|
|
DataTable Itemdt = SQlReturnData(cmd);
|
|
if (Itemdt.Rows.Count == 0)
|
|
{
|
|
throw new Exception("物料的ItemID取得失败");
|
|
}
|
|
ItemID = int.Parse(Itemdt.Rows[0]["Id"].ToString());
|
|
#endregion
|
|
|
|
//判断现存量是否符合
|
|
#region 更新失败，插入现存量
|
|
sql = @"IF NOT EXISTS (SELECT AutoID FROM {0}.dbo.CurrentStock
|
|
WHERE cWhCode = '{2}' AND cInvCode = '{3}'
|
|
AND ItemId = '{4}' and iQuantity >= '{1}')
|
|
BEGIN
|
|
RAISERROR('ERP现存量不足!!',16,0)
|
|
END ";
|
|
sql = string.Format(sql, Context.ERPDataName, qty, cWhcode, invcode, ItemID);
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sql = @"UPDATE {0}.dbo.CurrentStock SET iQuantity = iQuantity-{1}
|
|
WHERE cWhCode = '{2}' AND cInvCode = '{3}' AND ItemId = '{4}' ";
|
|
sql = string.Format(sql, Context.ERPDataName, qty, cWhcode, invcode, ItemID);
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
}
|
|
#endregion
|
|
|
|
#region 写入记账表
|
|
sql = @" INSERT INTO {0}.[dbo].[IA_ST_UnAccountVouch11]
|
|
SELECT '{1}',erpdid,'{2}','{3}' FROM ##rdqty ";
|
|
sql = string.Format(sql, Context.ERPDataName, Context.ERPrdID, "11", "领料");
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
|
|
#region 回写工单子件领料数量
|
|
sql = @"update b set b.IssQty = b.IssQty+a.qty
|
|
from ##rdqty a inner join {0}.dbo.mom_moallocate b on a.AllocateId = b.AllocateId";
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
|
|
#region 将入库单的ID，DID，Code，ERPName 回写 MES数据库
|
|
sql = @"INSERT INTO [dbo].[ICSMOStorageINERP]
|
|
([Serial]
|
|
,[BarCode]
|
|
,[rdTypeName]
|
|
,[rdID]
|
|
,[rdDID]
|
|
,[rdCode]
|
|
,[rdDate]
|
|
,[WorkPoint])
|
|
SELECT distinct NEWID(),a.BarCode,'材料出库','{0}',b.erpdid,'{1}','{2}','{3}'
|
|
FROM dbo.ICSStockBarCode a LEFT JOIN ICSStorageInfo c
|
|
ON a.BarCode = c.BarCode
|
|
left join ##rdqty b on a.itemcode = b.itemcode
|
|
where a.BarCode in ({4}) AND c.Storage_Serial = '{5}'
|
|
drop table ##rdqty";
|
|
sql = string.Format(sql, id, Context.ERPrdCode, Context.UserTime, AppConfig.WorkPointCode, barCodeStr, whDt.Rows[w]["Serial"].ToString());
|
|
cmd.CommandText = sql;
|
|
int s = cmd.ExecuteNonQuery();
|
|
#endregion
|
|
|
|
}
|
|
|
|
#region 修改MES数据库表
|
|
string sqlStr = "";
|
|
foreach (var dic in Context.Bar_Qty)
|
|
{
|
|
sql = @"SELECT SUM(Qty) AS Qty FROM [dbo].[ICSMOPicking]
|
|
WHERE BarCode = '" + dic.Key + "' GROUP BY BarCode";
|
|
cmd.CommandText = sql;
|
|
DataTable outQty = SQlReturnData(cmd);
|
|
|
|
if (outQty == null || outQty.Rows.Count == 0)
|
|
{
|
|
sqlStr = @"DELETE a FROM dbo.ICSStorageInfo a INNER JOIN
|
|
dbo.ICSStockBarCode b ON a.barcode = b.barcode
|
|
WHERE a.BarCode = '{0}'
|
|
AND b.quantity = '{1}'";
|
|
sqlStr = string.Format(sqlStr, dic.Key, dic.Value);
|
|
cmd.CommandText = sqlStr;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sqlStr = @" UPDATE dbo.ICSStockBarCode SET Status = '领料出库'
|
|
WHERE quantity = '" + dic.Value + @"'
|
|
and BarCode = '" + dic.Key + "'";
|
|
cmd.CommandText = sqlStr;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
else
|
|
{
|
|
decimal d = decimal.Parse(dic.Value) + decimal.Parse(outQty.Rows[0]["Qty"].ToString());
|
|
sqlStr = @"DELETE a FROM dbo.ICSStorageInfo a INNER JOIN
|
|
dbo.ICSStockBarCode b ON a.barcode = b.barcode
|
|
WHERE a.BarCode = '{0}'
|
|
AND b.quantity = '{1}'";
|
|
sqlStr = string.Format(sqlStr, dic.Key, d);
|
|
cmd.CommandText = sqlStr;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sqlStr = @" UPDATE dbo.ICSStockBarCode SET Status = '领料出库'
|
|
WHERE quantity = '" + d + @"'
|
|
and BarCode = '" + dic.Key + "'";
|
|
cmd.CommandText = sqlStr;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
sqlStr = @"INSERT INTO [dbo].[ICSMOPicking]
|
|
([Serial]
|
|
,[MoCode]
|
|
,[BarCode]
|
|
,[Mtime]
|
|
,[Muser]
|
|
,[WorkPoint],Qty)
|
|
VALUES
|
|
(NEWID(),'{0}','{1}',GETDATE(),'{2}','{3}','{4}')";
|
|
sqlStr = string.Format(sqlStr, MoNo, dic.Key, AppConfig.UserName, AppConfig.WorkPointCode, dic.Value);
|
|
cmd.CommandText = sqlStr;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
}
|
|
|
|
sql = @"UPDATE b SET b.OutQuantity = b.OutQuantity+a.qty
|
|
FROM (SELECT SUM(Quantity) AS qty,cFree1 FROM dbo.ICSStockBarCode WHERE BarCode IN (" + barCodeStr + @")
|
|
GROUP BY cFree1) a INNER JOIN ICSPurchasingStorage b ON a.cFree1 = b.Serial";
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#endregion
|
|
|
|
|
|
//throw new Exception("操作完成那个，取消保存，来进行下一次测试");
|
|
cmd.Transaction.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
cmd.Transaction.Rollback();
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static ICSStockBarCode query(string cartonNo)
|
|
{
|
|
FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
|
|
db.Connection.Open();
|
|
try
|
|
{
|
|
var line = db.ICSStockBarCode.SingleOrDefault(a => a.BarCode == cartonNo && a.WorkPoint == AppConfig.WorkPointCode);
|
|
return (ICSStockBarCode)line;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable Stockquery(string cartonNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT a.BarCode,a.Quantity,b.ItemCode,b.ItemName,b.ItemStd,
|
|
b.BatchCode,b.ComUnit,b.AssComUnit,a.Status FROM dbo.ICSStockBarCode a LEFT JOIN dbo.ICSPurchasingStorage b
|
|
ON a.cFree1 = b.Serial WHERE a.BarCode='" + cartonNo + "'";
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable StockQuerys(string cartonNo)
|
|
{
|
|
try
|
|
{
|
|
string sql = @"SELECT a.BarCode,(a.Quantity-ISNULL(c.Qty,0)) as Qty ,b.ItemCode,b.ItemName,b.ItemStd,
|
|
b.BatchCode,b.ComUnit,b.AssComUnit,a.Status FROM dbo.ICSStockBarCode a LEFT JOIN dbo.ICSPurchasingStorage b
|
|
ON a.cFree1 = b.Serial left join (SELECT SUM(Qty) AS Qty,BarCode FROM [dbo].[ICSMOPicking]
|
|
WHERE BarCode = '" + cartonNo + "' GROUP BY BarCode) c on a.BarCode = c.BarCode WHERE a.BarCode='" + cartonNo + "'";
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable getMaxNO(string conn)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = @"SELECT MAX(BarCode) AS maxBarCode FROM dbo.ICSStockBarCode";
|
|
return DBHelper.ExecuteDataset(conn, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable querys(string erpName,string cartonNo)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = @"SELECT a.[Serial]
|
|
,a.[SelectID]
|
|
,a.[CartonNO]
|
|
,a.[PackingSpec]
|
|
,a.[Collected]
|
|
,a.[BatchCode]
|
|
,a.[ItemCode]
|
|
,a.[CartonStatus]
|
|
,a.[MUSER]
|
|
,a.[MUSERName]
|
|
,a.[MTIME]
|
|
,a.[EATTRIBUTE1]
|
|
,a.[WorkPoint]
|
|
,b.MadeDate,b.ItemStd,c.UserCode,e.cDepName,d.cDept_num
|
|
FROM [ICSCarton] a
|
|
LEFT JOIN dbo.ICSItemLot b ON a.BatchCode=b.BatchCode
|
|
LEFT JOIN Sys_User c ON a.MUSER = c.ID
|
|
LEFT JOIN {0}.dbo.hr_hi_person d ON c.UserCode=d.cPsn_Num
|
|
LEFT JOIN {0}.dbo.Department e ON d.cDept_num=e.cDepCode
|
|
WHERE a.CartonNO='{1}'";
|
|
sql = string.Format(sql, erpName, cartonNo);
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable queryInfo( string cartonNo)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = @"SELECT e.ItemCode,e.ItemName,e.ItemStd,e.BatchCode,b.CartonNO,b.Collected,
|
|
c.StorageName,d.rdcCode,ISNULL(a.Stack_Serial,'') as Stack_Serial,f.StackName FROM dbo.ICSStorageLotInfo a
|
|
LEFT JOIN dbo.ICSCarton b ON a.Carton_Serial=b.Serial
|
|
LEFT JOIN dbo.ICSStorage c ON a.Storage_Serial=c.Serial
|
|
LEFT JOIN dbo.ICSStack f ON a.Stack_Serial=f.Serial
|
|
LEFT JOIN dbo.ICSCartionERPLinkInfo d ON b.Serial=d.CartonSerial
|
|
LEFT JOIN dbo.ICSItemLot e ON b.BatchCode=e.BatchCode
|
|
WHERE d.rdcCode=(SELECT rdcCode FROM dbo.ICSCartionERPLinkInfo a
|
|
LEFT JOIN dbo.ICSCarton b ON a.CartonSerial=b.Serial WHERE b.CartonNO='{0}')";
|
|
sql = string.Format(sql, cartonNo);
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable queryByNo(string cartonNo)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = @"SELECT e.ItemCode,e.ItemName,e.ItemStd,e.BatchCode,b.CartonNO,b.Collected,
|
|
c.StorageName,d.rdcCode,ISNULL(a.Stack_Serial,'') FROM dbo.ICSStorageLotInfo a
|
|
LEFT JOIN dbo.ICSCarton b ON a.Carton_Serial=b.Serial
|
|
LEFT JOIN dbo.ICSStorage c ON a.Storage_Serial=c.Serial
|
|
LEFT JOIN dbo.ICSCartionERPLinkInfo d ON b.Serial=d.CartonSerial
|
|
LEFT JOIN dbo.ICSItemLot e ON b.BatchCode=e.BatchCode
|
|
WHERE b.CartonNO='{0}' AND a.Stack_Serial is NULL";
|
|
sql = string.Format(sql, cartonNo);
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable outQuery(string cartonNo)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = @"SELECT a.[BatchCode]
|
|
,a.[ItemCode]
|
|
,a.[Storage_Serial]
|
|
,a.[Stack_Serial]
|
|
,a.[Carton_Serial]
|
|
,b.PackingSpec
|
|
,b.CartonNO
|
|
,b.CartonStatus
|
|
,c.ItemStd
|
|
,c.ItemName
|
|
,c.MadeDate
|
|
,d.StorageCode
|
|
FROM [ICSStorageLotInfo] a LEFT JOIN dbo.ICSCarton b
|
|
ON a.Carton_Serial=b.Serial
|
|
LEFT JOIN dbo.ICSItemLot c ON a.BatchCode=c.BatchCode
|
|
LEFT JOIN dbo.ICSStorage d ON a.Storage_Serial=d.Serial
|
|
WHERE b.CartonNO='" + cartonNo + "'";
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static void update(ICSStockBarCode carton)
|
|
{
|
|
//FramDataContext db = new FramDataContext(AppConfig.AppConnectString);
|
|
//db.Connection.Open();
|
|
//db.Transaction = db.Connection.BeginTransaction();
|
|
|
|
//try
|
|
//{
|
|
// var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == carton.Serial);
|
|
// line.Status = carton.Status;
|
|
// line.Mtime = carton.Mtime;
|
|
// line.Muser = AppConfig.UserName;
|
|
// line.WorkPoint = AppConfig.WorkPointCode;
|
|
// db.SubmitChanges();
|
|
// db.Transaction.Commit();
|
|
|
|
//}
|
|
//catch (Exception ex)
|
|
//{
|
|
// db.Transaction.Rollback();
|
|
// throw ex;
|
|
//}
|
|
}
|
|
/// <summary>
|
|
/// 生成箱号条形码
|
|
/// </summary>
|
|
/// <param name="Listguid"></param>
|
|
/// <param name="Appconstr"></param>
|
|
public static void CreatebarCode(List<CartonNoCreat> Listguid, string Appconstr, string PSerial, decimal sumPack)
|
|
{
|
|
//FramDataContext db = new FramDataContext(Appconstr);
|
|
//db.Connection.Open();
|
|
//db.Transaction = db.Connection.BeginTransaction();
|
|
//try
|
|
//{
|
|
// string autoID = "";
|
|
// string whcode = "";
|
|
// string whSerial = "";
|
|
// var puLine = db.ICSPurchasingStorage.SingleOrDefault(a => a.Serial == PSerial);
|
|
// if (puLine != null)
|
|
// {
|
|
// autoID = puLine.Rd01AutoID;
|
|
// whcode = puLine.cFree1;
|
|
// }
|
|
|
|
// foreach (CartonNoCreat TBLIcsCartonNo in Listguid)
|
|
// {
|
|
// bool isNew = false;
|
|
// var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == TBLIcsCartonNo.serial);
|
|
// if (line != null)
|
|
// {
|
|
// continue;
|
|
// }
|
|
// var lines = db.ICSStockBarCode.Where(a => a.BarCode == TBLIcsCartonNo.BarCode && a.WorkPoint == AppConfig.WorkPointCode).ToList();
|
|
// if (lines.Count() > 0)
|
|
// {
|
|
// throw new Exception("条码出现重复");
|
|
// }
|
|
// if (string.IsNullOrEmpty(autoID))
|
|
// {
|
|
// var storageLine = db.ICSStorage.SingleOrDefault(a => a.StorageCode == whcode);
|
|
// if (storageLine != null)
|
|
// {
|
|
// whSerial = storageLine.Serial;
|
|
// }
|
|
// TBLIcsCartonNo.Status = "入库";
|
|
// ICSStorageInfo puInfo = new ICSStorageInfo();
|
|
// puInfo.Serial = AppConfig.GetGuid();
|
|
// puInfo.BarCode = TBLIcsCartonNo.BarCode;
|
|
// puInfo.ItemType = "原料";
|
|
// puInfo.Storage_Serial = whSerial;
|
|
// puInfo.Stack_Serial = "2f77085c-1a32-4a15-b71d-424266f5b682";
|
|
// puInfo.Muser = AppConfig.UserName;
|
|
// puInfo.Mtime = DateTime.Now;
|
|
// puInfo.WorkPoint = "0001";
|
|
// db.ICSStorageInfo.InsertOnSubmit(puInfo);
|
|
// }
|
|
// isNew = true;
|
|
// line = new ICSStockBarCode();
|
|
// line.Serial = TBLIcsCartonNo.serial;
|
|
// line.BarCode = TBLIcsCartonNo.BarCode;
|
|
// line.BatchCode = TBLIcsCartonNo.BatchCode;
|
|
// line.ItemCode = TBLIcsCartonNo.ItemCode;
|
|
// line.Quantity = decimal.Parse(TBLIcsCartonNo.Quantity.ToString());
|
|
// line.Status = TBLIcsCartonNo.Status;
|
|
// line.Muser = TBLIcsCartonNo.Muser;
|
|
// line.cFree1 = TBLIcsCartonNo.cFree1;
|
|
// line.Mtime = DateTime.Parse(TBLIcsCartonNo.Mtime);
|
|
// line.WorkPoint = AppConfig.WorkPointCode;
|
|
// if (isNew) db.ICSStockBarCode.InsertOnSubmit(line);
|
|
|
|
// }
|
|
// var purch = db.ICSPurchasingStorage.SingleOrDefault(a => a.Serial == PSerial);
|
|
// if (purch != null)
|
|
// {
|
|
// purch.PackQuantiy = purch.PackQuantiy + sumPack;
|
|
// }
|
|
|
|
// db.SubmitChanges();
|
|
// db.Transaction.Commit();
|
|
//}
|
|
//catch (Exception ex)
|
|
//{
|
|
// db.Transaction.Rollback();
|
|
// throw new Exception(ex.Message);
|
|
//}
|
|
|
|
|
|
}
|
|
/// <summary>
|
|
/// 分箱
|
|
/// </summary>
|
|
/// <param name="Listguid"></param>
|
|
/// <param name="Appconstr"></param>
|
|
public static void CartonOpenOri(List<string> Listguid, string NewQty, string Appconstr)
|
|
{
|
|
//FramDataContext db = new FramDataContext(Appconstr);
|
|
//db.Connection.Open();
|
|
//db.Transaction = db.Connection.BeginTransaction();
|
|
//try
|
|
//{
|
|
// foreach(string serial in Listguid)
|
|
// {
|
|
// var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == serial);
|
|
// if(line!=null)
|
|
// {
|
|
// decimal OriginQty = decimal.Parse(line.Quantity.ToString());
|
|
// line.WorkPoint = AppConfig.WorkPointCode;
|
|
// }
|
|
// }
|
|
// db.SubmitChanges();
|
|
// db.Transaction.Commit();
|
|
//}
|
|
//catch(Exception ex)
|
|
//{
|
|
// throw ex;
|
|
//}
|
|
}
|
|
/// <summary>
|
|
/// 分箱
|
|
/// </summary>
|
|
/// <param name="list"></param>
|
|
/// <param name="Serial"></param>
|
|
/// <param name="NewCartonNo"></param>
|
|
/// <param name="NewQty"></param>
|
|
/// <param name="Appcon"></param>
|
|
///
|
|
public static void CartonOpenNew(List<string>list,string oldBarCode,string NewCartonNo,decimal NewQty,string Appcon)
|
|
{
|
|
// SqlConnection conn = new SqlConnection(Appcon);
|
|
// conn.Open();
|
|
// SqlCommand cmd = new SqlCommand();
|
|
// SqlTransaction trans = conn.BeginTransaction();
|
|
// cmd.Transaction = trans;
|
|
// cmd.Connection = conn;
|
|
// FramDataContext db = new FramDataContext(Appcon);
|
|
// db.Connection.Open();
|
|
// try
|
|
// {
|
|
// foreach(string ID in list)
|
|
// {
|
|
// DataTable dt = findBySerial(ID);
|
|
|
|
// string strSql = @"UPDATE dbo.ICSStockBarCode SET Quantity=Quantity-{0},
|
|
// Muser='{1}',Mtime='{2}'
|
|
// WHERE Serial='{3}'";
|
|
// strSql = string.Format(strSql, NewQty,
|
|
// AppConfig.UserName,DateTime.Now,ID);
|
|
// cmd.CommandType = CommandType.Text;
|
|
// cmd.CommandText = strSql;
|
|
// cmd.ExecuteNonQuery();
|
|
|
|
// //string SelectID = dt.Rows[0]["SelectID"].ToString();
|
|
// string BatchCode = dt.Rows[0]["BatchCode"].ToString();
|
|
// string ItemCode = dt.Rows[0]["ItemCode"].ToString();
|
|
// //decimal PackingSpec = decimal.Parse(dt.Rows[0]["PackingSpec"].ToString());
|
|
// string serial=AppConfig.GetGuid();
|
|
// string insertSql = @"INSERT INTO [ICSStockBarCode]
|
|
// ([Serial]
|
|
// ,[BarCode]
|
|
// ,[ItemCode]
|
|
// ,[BatchCode]
|
|
// ,[Quantity]
|
|
// ,[Status]
|
|
// ,[Mtime]
|
|
// ,[Muser]
|
|
// ,[cFree1]
|
|
// ,[cFree2]
|
|
// ,[WorkPoint])
|
|
// VALUES
|
|
// ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')";
|
|
// insertSql = string.Format(insertSql, serial, NewCartonNo, ItemCode, BatchCode, NewQty,
|
|
// "入库",AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss"),
|
|
// AppConfig.UserName, dt.Rows[0]["cFree1"].ToString(), "", AppConfig.WorkPointCode);
|
|
// cmd.CommandType = CommandType.Text;
|
|
// cmd.CommandText = insertSql;
|
|
// cmd.ExecuteNonQuery();
|
|
|
|
// var LotInfo = db.ICSStorageInfo.SingleOrDefault(a => a.BarCode == oldBarCode);
|
|
// string itemType = LotInfo.ItemType;
|
|
// string Storage_Serial = LotInfo.Storage_Serial;
|
|
// string Stack_Serial = LotInfo.Stack_Serial;
|
|
|
|
|
|
// string LotGuid = AppConfig.GetGuid();
|
|
// var LotInfoNewline = db.ICSStorageInfo.SingleOrDefault(a => a.BarCode == LotGuid);
|
|
// if (LotInfoNewline == null)
|
|
// {
|
|
// LotInfoNewline = new ICSStorageInfo();
|
|
// LotInfoNewline.Serial = LotGuid;
|
|
// LotInfoNewline.BarCode = NewCartonNo;
|
|
// LotInfoNewline.ItemType = itemType;
|
|
// LotInfoNewline.Storage_Serial = Storage_Serial;
|
|
// LotInfoNewline.Stack_Serial = Stack_Serial;
|
|
// LotInfoNewline.Mtime = AppConfig.GetSeverDateTime("yyyy-MM-dd HH:mm:ss");
|
|
// LotInfoNewline.Muser = AppConfig.UserName;
|
|
// LotInfoNewline.WorkPoint = AppConfig.WorkPointCode;
|
|
|
|
// }
|
|
// db.ICSStorageInfo.InsertOnSubmit(LotInfoNewline);
|
|
// }
|
|
// db.SubmitChanges();
|
|
// trans.Commit();
|
|
// }
|
|
// catch(Exception ex)
|
|
// {
|
|
// throw ex;
|
|
// }
|
|
}
|
|
/// <summary>
|
|
/// 合箱
|
|
/// </summary>
|
|
/// <param name="Listguid"></param>
|
|
/// <param name="Appconstr"></param>
|
|
public static void CartonClose(List<CartonNoCreat> Listguid,List<CartonNoCreat> Delguid, string Appconstr)
|
|
{
|
|
//FramDataContext db = new FramDataContext(Appconstr);
|
|
//db.Connection.Open();
|
|
//db.Transaction = db.Connection.BeginTransaction();
|
|
//try
|
|
//{
|
|
// foreach (CartonNoCreat TBLIcsCartonNo in Listguid)
|
|
// {
|
|
// var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == TBLIcsCartonNo.serial);
|
|
// if(line!=null)
|
|
// {
|
|
// line.Quantity = decimal.Parse(TBLIcsCartonNo.Quantity.ToString());
|
|
// line.Muser = AppConfig.UserName;
|
|
// line.Mtime = AppConfig.GetSeverDateTime("yyyy-MM-dd");
|
|
// line.WorkPoint = AppConfig.WorkPointCode;
|
|
// }
|
|
// }
|
|
// foreach (CartonNoCreat TBLIcsCartonNo in Delguid)
|
|
// {
|
|
// var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == TBLIcsCartonNo.serial);
|
|
// if (line != null)
|
|
// {
|
|
// db.ICSStockBarCode.DeleteOnSubmit(line);
|
|
// }
|
|
|
|
// var lines = db.ICSStorageInfo.Where(a => a.BarCode.Contains(TBLIcsCartonNo.BarCode));
|
|
// if (lines != null)
|
|
// {
|
|
// db.ICSStorageInfo.DeleteAllOnSubmit(lines);
|
|
// }
|
|
|
|
// }
|
|
// db.SubmitChanges();
|
|
// db.Transaction.Commit();
|
|
//}
|
|
//catch(Exception ex)
|
|
//{
|
|
// throw ex;
|
|
//}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改箱号
|
|
/// </summary>
|
|
/// <param name="serial"></param>
|
|
/// <param name="Collect"></param>
|
|
/// <param name="AppStr"></param>
|
|
public static void CartonNoModify(string serial,string Collect, string AppStr)
|
|
{
|
|
//FramDataContext db = new FramDataContext(AppStr);
|
|
//db.Connection.Open();
|
|
//db.Transaction = db.Connection.BeginTransaction();
|
|
//try
|
|
//{
|
|
// var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == serial);
|
|
// if(line!=null)
|
|
// {
|
|
// line.Quantity = decimal.Parse(Collect);
|
|
// line.Muser = AppConfig.UserName;
|
|
// line.Mtime = AppConfig.GetSeverDateTime("yyyy-MM-dd");
|
|
// line.WorkPoint = AppConfig.WorkPointCode;
|
|
// }
|
|
// db.SubmitChanges();
|
|
// db.Transaction.Commit();
|
|
//}
|
|
//catch(Exception ex)
|
|
//{
|
|
// throw ex;
|
|
//}
|
|
}
|
|
/// <summary>
|
|
/// 删除箱号
|
|
/// </summary>
|
|
/// <param name="Guidlist"></param>
|
|
/// <param name="AppStr"></param>
|
|
public static List<string> CartonNoDel(List<KeyValuePair<string, string>> Guidlist, string AppStr)
|
|
{
|
|
List<string> DelSuccess = new List<string>();
|
|
FramDataContext db = new FramDataContext(AppStr);
|
|
db.Connection.Open();
|
|
db.Transaction = db.Connection.BeginTransaction();
|
|
try
|
|
{
|
|
bool b = true;
|
|
string carton = "";
|
|
foreach (KeyValuePair<string, string> strvalue in Guidlist.ToArray())
|
|
{
|
|
//var line = db.ICSStockBarCode.SingleOrDefault(a => a.Serial == strvalue.Key);
|
|
//if (line != null)
|
|
//{
|
|
// db.ICSStockBarCode.DeleteOnSubmit(line);
|
|
//}
|
|
if (b == true)
|
|
{
|
|
carton = "" + "'" + strvalue.Value + "'" + "";
|
|
b = false;
|
|
}
|
|
else
|
|
{
|
|
carton += "" + ",'" + strvalue.Value + "'" + "";
|
|
}
|
|
|
|
}
|
|
string sql = @"UPDATE dbo.ICSPurchasingStorage SET PackQuantiy = ISNULL(PackQuantiy,0)- pack.sumPack
|
|
FROM dbo.ICSPurchasingStorage lot ,(SELECT cFree1, SUM(Quantity) AS sumPack FROM ICSStockBarCode WHERE BarCode IN
|
|
(" + carton + ") GROUP BY cFree1) pack WHERE lot.Serial=pack.cFree1";
|
|
DBHelper.ExecuteNonQuery(AppStr, CommandType.Text, sql);
|
|
|
|
db.SubmitChanges();
|
|
db.Transaction.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
db.Transaction.Rollback();
|
|
throw ex;
|
|
}
|
|
return DelSuccess;
|
|
}
|
|
|
|
#region ICSItemLot 实体类
|
|
public class CartonNoCreat
|
|
{
|
|
public string serial { get; set; }
|
|
public string BarCode { get; set; }
|
|
public string ItemCode { get; set; }
|
|
public string BatchCode { get; set; }
|
|
public decimal Quantity { get; set; }
|
|
public string Status { get; set; }
|
|
public string Mtime { get; set; }
|
|
public string Muser { get; set; }
|
|
public string cFree1 { get; set; }
|
|
public string cFree2 { get; set; }
|
|
public string WorkPoint { get; set; }
|
|
}
|
|
#endregion
|
|
|
|
public static void search(List<string> guidList1, string appstr)
|
|
{
|
|
FramDataContext db = new FramDataContext(appstr);
|
|
db.Connection.Open();
|
|
try
|
|
{
|
|
var line = db.ICSStockBarCode.Where(a => guidList1.Contains(a.BatchCode) && a.WorkPoint == AppConfig.WorkPointCode);
|
|
|
|
if (line.ToList().Count > 0 )
|
|
{
|
|
throw new Exception("批号已使用不可操作");
|
|
}
|
|
|
|
|
|
}
|
|
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static DataTable findBySerial(string serial)
|
|
{
|
|
try
|
|
{
|
|
|
|
string sql = @"SELECT [Serial]
|
|
,[BarCode]
|
|
,[ItemCode]
|
|
,[BatchCode]
|
|
,[Quantity]
|
|
,[Status]
|
|
,[Mtime]
|
|
,[Muser]
|
|
,[cFree1]
|
|
,[cFree2]
|
|
,[WorkPoint]
|
|
FROM [ICSStockBarCode] WHERE Serial='" + serial + "'";
|
|
return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0];
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 生成箱号条形码
|
|
/// </summary>
|
|
/// <param name="Listguid"></param>
|
|
/// <param name="Appconstr"></param>
|
|
public static void UpdateLotPack(string Batch, decimal sumPack, string Appconstr, string PSerial)
|
|
{
|
|
//FramDataContext db = new FramDataContext(Appconstr);
|
|
//db.Connection.Open();
|
|
//db.Transaction = db.Connection.BeginTransaction();
|
|
//try
|
|
//{
|
|
// var line = db.ICSPurchasingStorage.SingleOrDefault(a => a.Serial == PSerial);
|
|
// if (line != null)
|
|
// {
|
|
// line.PackQuantiy = line.PackQuantiy+sumPack;
|
|
// }
|
|
|
|
// db.SubmitChanges();
|
|
// db.Transaction.Commit();
|
|
//}
|
|
//catch (Exception ex)
|
|
//{
|
|
// db.Transaction.Rollback();
|
|
// throw new Exception(ex.Message);
|
|
//}
|
|
|
|
|
|
}
|
|
|
|
#region SQL事物操作函数以及ERP的ID的读取与写入的
|
|
/// 插入或更新的操作
|
|
/// </summary>
|
|
/// <param name="SQl"></param>
|
|
/// <param name="conn"></param>
|
|
/// <param name="sqlTran"></param>
|
|
/// <returns></returns>
|
|
private static int SQlInsertAndUpdate(string SQl, SqlConnection conn, SqlTransaction sqlTran, SqlCommand cmd)
|
|
{
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = SQl;
|
|
cmd.Transaction = sqlTran;
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
/// <summary>
|
|
/// 查询SQL
|
|
/// </summary>
|
|
/// <param name="SQl"></param>
|
|
/// <param name="conn"></param>
|
|
/// <param name="sqlTran"></param>
|
|
/// <returns></returns>
|
|
private static DataTable SQlReturnData(SqlCommand cmd)
|
|
{
|
|
DataTable dt = new DataTable();
|
|
SqlDataAdapter dr = new System.Data.SqlClient.SqlDataAdapter();
|
|
dr.SelectCommand = cmd;
|
|
dr.Fill(dt);
|
|
return dt;
|
|
}
|
|
|
|
private static void SaveGetrdIDandAutoID(string connectstring, string returnTxt, string IDtype, string cAcc_id, int rowCount, out int id, out int autoID)
|
|
{
|
|
try
|
|
{
|
|
string str = @"DECLARE @ID int
|
|
DECLARE @DID int
|
|
SET @ID = 0
|
|
SET @DID = 0
|
|
IF NOT EXISTS (SELECT * FROM ufsystem..ua_identity WHERE cacc_id = '{0}' AND cVouchType = '{1}')
|
|
INSERT INTO ufsystem..ua_identity(cAcc_Id,cVouchType,iFatherId,iChildId) VALUES('{0}','{1}',1,1)
|
|
SELECT @ID = ifatherID + 1 ,@DID = ichildID + {2}
|
|
FROM ufsystem..ua_identity
|
|
WHERE cVouchType = '{1}'
|
|
AND cAcc_id = '{0}'
|
|
|
|
UPDATE ufsystem..ua_identity
|
|
SET ifatherID = ifatherID + 1,ichildID = ichildID + {2}
|
|
WHERE cVouchType = '{1}' AND cAcc_id = '{0}'
|
|
select @ID as ID,@DID as DID";
|
|
str = string.Format(str, cAcc_id, IDtype, rowCount.ToString());
|
|
DataTable dt = DBHelper.ExecuteDataset(connectstring, CommandType.Text, str).Tables[0];
|
|
if (dt.Rows.Count == 0)
|
|
{
|
|
throw new Exception("ID取得失败");
|
|
}
|
|
id = Convert.ToInt32(dt.Rows[0]["ID"]);
|
|
autoID = Convert.ToInt32(dt.Rows[0]["DID"]);
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw new Exception(returnTxt + ex.Message);
|
|
}
|
|
}
|
|
|
|
#endregion
|
|
|
|
}
|
|
|
|
public class Rd11Context
|
|
{
|
|
public string ERPDataName { get; set; }
|
|
public string WorkPointCode { get; set; }
|
|
public string UserName { get; set; }
|
|
public DateTime UserTime { get; set; }
|
|
public string Storage_Serial { get; set; }
|
|
public string Stack_Serial { get; set; }
|
|
public string ERPDeptCode { get; set; }
|
|
public string ERPrdCode { get; set; }
|
|
public string ERPrdcRdCode { get; set; }
|
|
public int ERPrdID { get; set; }
|
|
public int ERPrdDid { get; set; }
|
|
public int ERPrdRowCount { get; set; }
|
|
public string ERPcWhCode { get; set; }
|
|
public string ERPrdPerson { get; set; }
|
|
public string ERPcMaker { get; set; }
|
|
public string ERPrdcDate { get; set; }
|
|
public string ERPDEF_ID { get; set; }
|
|
public string ERPCardNumber { get; set; }
|
|
public string ERPcPersonCode { get; set; }
|
|
public string MoDid { get; set; }
|
|
public string MoId { get; set; }
|
|
public Dictionary<string,string> Bar_Qty { get; set; }
|
|
|
|
|
|
}
|
|
|
|
public class InitialInvtory
|
|
{
|
|
public string ItemCode { get; set; }
|
|
public string BatchCode { get; set; }
|
|
public string Qty { get; set; }
|
|
public string VenCode { get; set; }
|
|
public string WhCode { get; set; }
|
|
public string Free1 { get; set; }
|
|
public string Free2 { get; set; }
|
|
public string Free3 { get; set; }
|
|
public string AuxUnitCode { get; set; }
|
|
public string AuxQty { get; set; }
|
|
public string Line { get; set; }
|
|
}
|
|
}
|