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.
702 lines
33 KiB
702 lines
33 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 ICSStorageInfoDAL
|
|
{
|
|
public static void import(List<string> barList)
|
|
{
|
|
SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand();
|
|
cmd.CommandType = CommandType.Text;
|
|
SqlTransaction trans = conn.BeginTransaction();
|
|
cmd.Transaction = trans;
|
|
cmd.Connection = conn;
|
|
|
|
try
|
|
{
|
|
string barCodeStr = "";
|
|
string maxNo = @"select max(ToCheckNO ) as ToCheckNO from dbo.ICSToCheck";
|
|
DataTable dtNo = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, maxNo).Tables[0];
|
|
|
|
for (int i = 0; i < barList.Count;i++ )
|
|
{
|
|
if (barCodeStr == "")
|
|
{
|
|
barCodeStr = "'" + barList[i] + "'";
|
|
}
|
|
else
|
|
{
|
|
barCodeStr += ",'" + barList[i] + "'";
|
|
}
|
|
if (i % 200 == 0)
|
|
{
|
|
string lineSql = @"INSERT INTO [dbo].[ICSToCheckLog]
|
|
([Serial]
|
|
,[BarCode]
|
|
,[Mtime])
|
|
SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSStockBarCode WHERE BarCode IN ("+barCodeStr+@")
|
|
UNION ALL
|
|
SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSMOBarCode WHERE BarCode IN (" + barCodeStr + ")";
|
|
cmd.CommandText = lineSql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
lineSql = @"insert into dbo.ICSToChecks
|
|
select distinct NEWID(),'{0}',CASE WHEN b.ItemCode IS NULL THEN c.ItemCode ELSE b.ItemCode END AS ItemCode,
|
|
CASE WHEN b.BatchCode IS NULL THEN c.BatchCode ELSE b.ItemCode END AS BatchCode,
|
|
e.Free1,e.Free2,e.Free3,e.Free4,a.BarCode,
|
|
CASE WHEN b.Quantity IS NULL THEN c.Quantity ELSE b.Quantity END AS Quantity,
|
|
f.StorageCode,'{1}',GETDATE()
|
|
from dbo.ICSStorageInfo a
|
|
left join dbo.ICSStockBarCode b on a.BarCode = b.BarCode
|
|
left join dbo.ICSMOBarCode c on a.BarCode = c.BarCode
|
|
left join dbo.ICSPurchasingStorage d on b.cFree1 = d.Serial
|
|
left join dbo.ICSMO e on c.cFree1 = e.Serial
|
|
left join dbo.ICSStorage f on a.Storage_Serial = f.Serial
|
|
where a.barcode in (" + barCodeStr + @")";
|
|
lineSql = string.Format(lineSql, dtNo.Rows[0]["ToCheckNO"].ToString(), AppConfig.UserName);
|
|
cmd.CommandText = lineSql;
|
|
cmd.ExecuteNonQuery();
|
|
barCodeStr = "";
|
|
}
|
|
|
|
}
|
|
string Sql = @"insert into dbo.ICSToChecks
|
|
select distinct NEWID(),'{0}',CASE WHEN b.ItemCode IS NULL THEN c.ItemCode ELSE b.ItemCode END AS ItemCode,
|
|
CASE WHEN b.BatchCode IS NULL THEN c.BatchCode ELSE b.ItemCode END AS BatchCode,
|
|
e.Free1,e.Free2,e.Free3,e.Free4,a.BarCode,
|
|
CASE WHEN b.Quantity IS NULL THEN c.Quantity ELSE b.Quantity END AS Quantity,
|
|
f.StorageCode,'{1}',GETDATE()
|
|
from dbo.ICSStorageInfo a
|
|
left join dbo.ICSStockBarCode b on a.BarCode = b.BarCode
|
|
left join dbo.ICSMOBarCode c on a.BarCode = c.BarCode
|
|
left join dbo.ICSPurchasingStorage d on b.cFree1 = d.Serial
|
|
left join dbo.ICSMO e on c.cFree1 = e.Serial
|
|
left join dbo.ICSStorage f on a.Storage_Serial = f.Serial
|
|
where a.barcode in (" + barCodeStr + @")";
|
|
Sql = string.Format(Sql, dtNo.Rows[0]["ToCheckNO"].ToString(), AppConfig.UserName);
|
|
cmd.CommandText = Sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
Sql = @"INSERT INTO [dbo].[ICSToCheckLog]
|
|
([Serial]
|
|
,[BarCode]
|
|
,[Mtime])
|
|
SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSStockBarCode WHERE BarCode IN (" + barCodeStr + @")
|
|
UNION ALL
|
|
SELECT NEWID(),BarCode,GETDATE() FROM dbo.ICSMOBarCode WHERE BarCode IN (" + barCodeStr + ")";
|
|
cmd.CommandText = Sql;
|
|
cmd.ExecuteNonQuery();
|
|
barCodeStr = "";
|
|
|
|
Sql = @"UPDATE dbo.ICSToCheck SET ActualQty = ISNULL(ActualQty,0)+ISNULL(b.qty,0)
|
|
FROM dbo.ICSToCheck a
|
|
INNER JOIN (SELECT distinct CASE WHEN d.[ItemCode] IS NULL THEN e.ItemCode ELSE d.[ItemCode] END AS ItemCode,
|
|
isnull(e.Free1,'') as Free1,isnull(e.Free2,'') as Free2,
|
|
isnull(e.Free3,'') as Free3,isnull(e.Free4,'') as Free4,
|
|
sum(a.BarCodeQty) AS qty,a.StorageCode,info.ItemType
|
|
FROM [ICSToChecks] a
|
|
left join dbo.ICSStorageInfo info on a.BarCode = info.BarCode
|
|
LEFT JOIN ICSMOBarCode b
|
|
ON a.BarCode=b.BarCode
|
|
LEFT JOIN dbo.ICSStockBarCode c
|
|
ON a.BarCode=c.BarCode
|
|
LEFT JOIN dbo.ICSPurchasingStorage d
|
|
ON c.cFree1 = d.Serial
|
|
LEFT JOIN dbo.ICSMO e ON b.cFree1 = e.Serial
|
|
GROUP BY d.[ItemCode],e.ItemCode,e.Free1,e.Free2,e.Free3,
|
|
e.Free4,a.StorageCode,info.ItemType) b
|
|
ON a.ItemCode = b.ItemCode AND a.Free1 = b.Free1 AND a.Free2 = b.Free2
|
|
AND a.Free3 = b.Free3 AND a.Free4 = b.Free4 AND a.StorageCode = b.StorageCode
|
|
AND a.itemtype = b.ItemType";
|
|
cmd.CommandText = Sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
trans.Rollback();
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static void StorageIn(string barCodeStr, string storage, string stack)
|
|
{
|
|
SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand();
|
|
SqlTransaction trans = conn.BeginTransaction();
|
|
cmd.Transaction = trans;
|
|
cmd.Connection = conn;
|
|
try
|
|
{
|
|
|
|
string strSql = @"INSERT INTO [dbo].[ICSStorageInfo]
|
|
([Serial]
|
|
,[BarCode]
|
|
,[Storage_Serial]
|
|
,[Stack_Serial]
|
|
,[Mtime]
|
|
,[Muser]
|
|
,[WorkPoint],ItemType)
|
|
SELECT NEWID(),BarCode,'{0}','{1}',GETDATE(),'{2}','{3}','原料'
|
|
FROM dbo.ICSStockBarCode WHERE BarCode IN ({4})";
|
|
strSql = string.Format(strSql, storage, stack, AppConfig.UserName, AppConfig.WorkPointCode, barCodeStr);
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = strSql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
strSql = @" UPDATE dbo.ICSStockBarCode SET Status = '入库'
|
|
WHERE BarCode IN ({0})";
|
|
strSql = string.Format(strSql, barCodeStr);
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = strSql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
trans.Commit();
|
|
}
|
|
catch(Exception ex)
|
|
{
|
|
trans.Rollback();
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static void Delete(string delStr)
|
|
{
|
|
SqlConnection conn = new SqlConnection(AppConfig.AppConnectString);
|
|
conn.Open();
|
|
SqlCommand cmd = new SqlCommand();
|
|
SqlTransaction trans = conn.BeginTransaction();
|
|
cmd.Transaction = trans;
|
|
cmd.Connection = conn;
|
|
try
|
|
{
|
|
string strSql = @"DELETE dbo.ICSStorageInfo WHERE barCode in ({0})";
|
|
strSql = string.Format(strSql, delStr);
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = strSql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
strSql = @"DELETE dbo.ICSMOStorageINERP WHERE BarCode IN ({0})";
|
|
strSql = string.Format(strSql, delStr);
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = strSql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
strSql = @" UPDATE dbo.ICSStockBarCode SET Status = '新增'
|
|
WHERE BarCode IN ({0})";
|
|
strSql = string.Format(strSql, delStr);
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = strSql;
|
|
cmd.ExecuteNonQuery();
|
|
trans.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
trans.Rollback();
|
|
throw ex;
|
|
}
|
|
}
|
|
|
|
public static void StorageInERP(string barCodeStr, storageInfoContext Context)
|
|
{
|
|
string sql = "";
|
|
string ConnectString = AppConfig.AppConnectString;
|
|
|
|
#region 默认代码
|
|
Context.ERPCardNumber = "24";
|
|
#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
|
|
{
|
|
#region 修改MES数据库表
|
|
sql = @"INSERT INTO [dbo].[ICSStorageInfo]
|
|
([Serial]
|
|
,[BarCode]
|
|
,[Storage_Serial]
|
|
,[Stack_Serial]
|
|
,[Mtime]
|
|
,[Muser]
|
|
,[WorkPoint],ItemType)
|
|
SELECT NEWID(),BarCode,'{0}','{1}',GETDATE(),'{2}','{3}','原料'
|
|
FROM dbo.ICSStockBarCode WHERE BarCode IN ({4})";
|
|
sql = string.Format(sql, Context.Storage_Serial, Context.Stack_Serial,
|
|
AppConfig.UserName, AppConfig.WorkPointCode, barCodeStr);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sql = @"SELECT BarCode FROM dbo.ICSStockBarCode
|
|
WHERE BarCode IN ({0}) AND Status != '新增'";
|
|
sql = string.Format(sql, barCodeStr);
|
|
cmd.CommandText = sql;
|
|
DataTable barDt = SQlReturnData(cmd);
|
|
if (barDt != null && barDt.Rows.Count > 0)
|
|
{
|
|
throw new Exception("请选择状态为新增的条码进行入库!!");
|
|
}
|
|
|
|
sql = @" UPDATE dbo.ICSStockBarCode SET Status = '入库'
|
|
WHERE BarCode IN ({0})";
|
|
sql = string.Format(sql, barCodeStr);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
sql = @"SELECT d.ID,d.cVenCode,d.cpocode,d.cCode,d.dDate FROM dbo.ICSStockBarCode a
|
|
LEFT JOIN dbo.ICSPurchasingStorage b
|
|
ON a.cFree1 = b.Serial
|
|
LEFT JOIN {0}.dbo.PU_ArrivalVouchs c ON b.Rd01AutoID = c.autoid
|
|
LEFT JOIN {0}.dbo.PU_ArrivalVouch d ON c.id=d.id
|
|
WHERE a.BarCode IN ({1})
|
|
GROUP BY d.ID,d.cVenCode,d.cpocode,d.cCode,d.dDate";
|
|
sql = string.Format(sql, Context.ERPDataName, barCodeStr);
|
|
cmd.CommandText = sql;
|
|
DataTable Hdt = SQlReturnData(cmd);
|
|
|
|
for (int j = 0; j < Hdt.Rows.Count;j++ )
|
|
{
|
|
#region 修改ERP信息
|
|
#region 汇总信息
|
|
sql = @"SELECT ROW_NUMBER() OVER(ORDER BY a.ItemCode) AS row,b.Rd01AutoID,d.cVenCode,
|
|
d.ID,d.cCode,c.iPOsID,c.iOriCost AS 原币无税单价,c.iOriTaxCost AS 原币含税单价,
|
|
SUM(a.Quantity) AS qty,a.ItemCode,a.BatchCode,0 as erpdid,c.btaxcost,c.iinvexchrate,
|
|
SUM(a.Quantity)*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END) as sQty,
|
|
inv.cComUnitCode,b.AssComUnit as cAssComUnitCode into #t1 FROM dbo.ICSStockBarCode a
|
|
LEFT JOIN dbo.ICSPurchasingStorage b ON a.cFree1 = b.Serial
|
|
LEFT JOIN {0}.dbo.PU_ArrivalVouchs c ON b.Rd01AutoID = c.autoid
|
|
LEFT JOIN {0}.dbo.PU_ArrivalVouch d ON c.id=d.id
|
|
LEFT JOIN {0}.dbo.Inventory inv ON a.ItemCode=inv.cInvCode
|
|
WHERE d.ID = '{1}' AND a.BarCode IN ({2})
|
|
GROUP BY a.ItemCode,a.BatchCode,inv.cComUnitCode,b.AssComUnit,b.Rd01AutoID
|
|
,d.cVenCode,d.ID,c.iPOsID,c.iOriCost,c.iOriTaxCost,c.btaxcost,d.cCode,c.iinvexchrate
|
|
select *,sQty*原币无税单价 as 原币无税金额,sQty*原币含税单价 as 原币价税合计,
|
|
sQty*原币含税单价-sQty*原币无税单价 as 原币税额 into ##rdqty from #t1
|
|
select * from ##rdqty
|
|
drop table #t1";
|
|
|
|
sql = string.Format(sql, Context.ERPDataName, Hdt.Rows[j]["ID"].ToString(), barCodeStr);
|
|
cmd.CommandText = sql;
|
|
DataTable temp = SQlReturnData(cmd);
|
|
#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('24',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.RdRecord01 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.RdRecord01 (id,bRdFlag,cVouchType,cBusType,cSource,cBusCode,cWhCode,dDate,cCode,cRdCode,
|
|
cVenCode,cOrderCode,cHandler,bTransFlag,cMaker,dVeriDate,bpufirst,biafirst,VT_ID,bIsSTQc,
|
|
ipurorderid,iTaxRate,iExchRate,cExch_Name,bOMFirst,bFromPreYear,bIsComplement,
|
|
iDiscountTaxType,ireturncount,iverifystate,iswfcontrolled,dnmaketime,dnverifytime,
|
|
bredvouch,bCredit,cARVCode,ipurarriveid,dARVDate)
|
|
VALUES
|
|
(@id,'1','01','普通采购','采购到货单',null,@cWhCode,CONVERT(varchar(15), GETDATE(), 23),@cCode,@cRdCode,
|
|
@cVenCode,@cOrderCode,@cHandler,'0',@cMaker,GETDATE(),'0','0',@VT_ID,'0',
|
|
@ipurorderid,'17.000000','1','人民币','0','0','0',
|
|
'0','0','0','0',GETDATE(),GETDATE(),'0','0',@cARVCode,@ipurorderid,@dARVDate)";
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
cmd.CommandText = sql;
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("@id", Context.ERPrdID);
|
|
cmd.Parameters.AddWithValue("@cWhCode", Context.ERPcWhCode);
|
|
cmd.Parameters.AddWithValue("@cCode", Context.ERPrdCode);
|
|
cmd.Parameters.AddWithValue("@cRdCode", Context.ERPrdcRdCode);
|
|
cmd.Parameters.AddWithValue("@cVenCode", Hdt.Rows[j]["cVenCode"].ToString());
|
|
cmd.Parameters.AddWithValue("@cHandler", Context.UserName);
|
|
cmd.Parameters.AddWithValue("@cMaker", Context.UserName);
|
|
cmd.Parameters.AddWithValue("@VT_ID", Context.ERPDEF_ID);
|
|
cmd.Parameters.AddWithValue("@cOrderCode", Hdt.Rows[j]["cpocode"].ToString());
|
|
cmd.Parameters.AddWithValue("@ipurorderid", Hdt.Rows[j]["ID"].ToString());
|
|
cmd.Parameters.AddWithValue("@cARVCode", Hdt.Rows[j]["cCode"].ToString());
|
|
cmd.Parameters.AddWithValue("@dARVDate", Hdt.Rows[j]["dDate"].ToString());
|
|
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.rdrecords01
|
|
(AutoID,ID,cInvCode,iNum,iQuantity,iUnitCost,iPrice,iAPrice,iFlag,iSQuantity,iSNum,iMoney,
|
|
iPOsID,fACost,iNQuantity,iNNum,cAssUnit,chVencode,iOriTaxCost,iOriCost,iOriMoney,iOriTaxPrice,
|
|
ioriSum,iTaxRate,iTaxPrice,iSum,bTaxCost,cPOID,iMatSettleState,iBillSettleCount,bLPUseFree,
|
|
iOriTrackID,bCosting,iExpiratDateCalcu,iordertype,isotype,irowno,bgift,iArrsId,cbarvcode,iinvexchrate)
|
|
SELECT erpdid,@ID,ItemCode,qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END),
|
|
qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END),
|
|
原币无税单价,原币无税金额,原币无税金额,'0','0','0','0',iPOsID,原币无税单价,
|
|
qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END),
|
|
qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END),cAssComUnitCode,
|
|
cVenCode,原币含税单价,原币无税单价,原币无税金额,原币税额,
|
|
原币价税合计,'17.000000',原币税额,原币价税合计,btaxcost,@cPOID,'0','0','0',
|
|
'0','0','0','0','0',row,'0',Rd01AutoID,cCode,iinvexchrate FROM ##rdqty";
|
|
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("@ID", Context.ERPrdID);
|
|
cmd.Parameters.AddWithValue("@cPOID", Hdt.Rows[j]["cpocode"].ToString());
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#endregion
|
|
|
|
#region 改变现存量
|
|
|
|
foreach (DataRow dr in dt.Rows)
|
|
{
|
|
|
|
int ItemID = -1;
|
|
double qty = 0;
|
|
string change = dr["iinvexchrate"].ToString();
|
|
if (change != "")
|
|
{
|
|
qty = double.Parse(dr["qty"].ToString()) * double.Parse(dr["iinvexchrate"].ToString());
|
|
}
|
|
else {
|
|
qty = double.Parse(dr["qty"].ToString());
|
|
}
|
|
string invcode = dr["itemcode"].ToString();
|
|
string batchCode = dr["batchCode"].ToString();
|
|
string autoID = dr["itemcode"].ToString();
|
|
|
|
#region 取得物料的itemID
|
|
sql = @"IF NOT EXISTS(
|
|
SELECT Id FROM {0}.dbo.SCM_Item WHERE
|
|
cFree1 = '' AND cFree2 = '' AND cFree3 = '' AND cFree4 = ''
|
|
AND cFree5 = '' AND cFree6 = '' AND cFree7 = '' AND cFree8 = ''
|
|
AND cFree9 = '' AND cFree10 = '' AND 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
|
|
cFree1 = '' AND cFree2 = '' AND cFree3 = '' AND cFree4 = ''
|
|
AND cFree5 = '' AND cFree6 = '' AND cFree7 = '' AND cFree8 = ''
|
|
AND cFree9 = '' AND cFree10 = '' AND 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 EXISTS (SELECT AutoID FROM {0}.dbo.CurrentStock
|
|
WHERE cWhCode = @cWhCode AND cInvCode = @cInvCode
|
|
AND cBatch = @cBatch AND ItemId = @ItemId)
|
|
BEGIN
|
|
|
|
UPDATE {0}.dbo.CurrentStock SET iQuantity = iQuantity + @iQuantity
|
|
WHERE cWhCode = @cWhCode AND cInvCode = @cInvCode AND cBatch = @cBatch
|
|
AND ItemId = @ItemId
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
INSERT INTO {0}.dbo.CurrentStock
|
|
(cWhCode,cInvCode,ItemId,cBatch,iSoType,iQuantity,
|
|
iNum,cFree1,fOutQuantity,fOutNum,fInQuantity,fInNum,cFree2,
|
|
cFree3,bStopFlag,fTransInQuantity,fTransInNum,
|
|
fTransOutQuantity,fTransOutNum,fPlanQuantity,fPlanNum,fDisableQuantity,
|
|
fDisableNum,fAvaQuantity,fAvaNum,BGSPSTOP,fStopQuantity,
|
|
fStopNum,ipeqty,ipenum)
|
|
SELECT @cWhCode,@cInvCode,@ItemId,@cBatch,'0',@iQuantity,
|
|
'0',@cFree1,'0','0','0','0',@cFree2,
|
|
@cFree3,'0','0','0','0','0','0','0','0',
|
|
'0','0','0','0','0','0','0','0' FROM ##rdqty
|
|
where batchCode = '{1}'
|
|
END ";
|
|
sql = string.Format(sql, Context.ERPDataName, batchCode);
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.Parameters.Clear();
|
|
cmd.Parameters.AddWithValue("@cWhCode", Context.ERPcWhCode);
|
|
cmd.Parameters.AddWithValue("@cInvCode", invcode);
|
|
cmd.Parameters.AddWithValue("@ItemId", ItemID);
|
|
cmd.Parameters.AddWithValue("@cBatch", batchCode);
|
|
cmd.Parameters.AddWithValue("@iQuantity", qty);
|
|
cmd.Parameters.AddWithValue("@cFree1", "");
|
|
cmd.Parameters.AddWithValue("@cFree2", "");
|
|
cmd.Parameters.AddWithValue("@cFree3", "");
|
|
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
}
|
|
#endregion
|
|
|
|
#region 将入库单的ID，DID，Code，ERPName 回写 MES数据库
|
|
sql = @"INSERT INTO [dbo].[ICSMOStorageINERP]
|
|
([Serial]
|
|
,[BarCode]
|
|
,[rdTypeName]
|
|
,[rdID]
|
|
,[rdDID]
|
|
,[rdCode]
|
|
,[rdDate]
|
|
,[WorkPoint])
|
|
SELECT NEWID(),a.BarCode,'采购入库','{0}',b.erpdid,'{1}','{2}','{3}'
|
|
FROM dbo.ICSStockBarCode a inner join ##rdqty b on a.BatchCode = b.BatchCode
|
|
where BarCode in ({4}) ";
|
|
sql = string.Format(sql, id, Context.ERPrdCode, Context.UserTime, AppConfig.WorkPointCode, barCodeStr);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
#endregion
|
|
|
|
#region 更新采购,到货单入库数量
|
|
sql = @"SELECT Rd01AutoID,SUM(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END)) AS qty,
|
|
sum(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END)) as num INTO ##sumQty FROM ##rdqty
|
|
GROUP BY Rd01AutoID";
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sql = @"SELECT iPOsID,SUM(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 1 ELSE iinvexchrate END)) AS qty,
|
|
sum(qty*(CASE WHEN iinvexchrate=0 OR iinvexchrate IS NULL THEN 0 ELSE 1 END)) as num INTO ##POsumQty FROM ##rdqty
|
|
GROUP BY iPOsID";
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sql = @"UPDATE b SET fValidInQuan = ISNULL(fValidInQuan,0)+a.qty,fValidInNum = ISNULL(fValidInNum,0)+a.num
|
|
FROM ##sumQty a INNER JOIN {0}.dbo.PU_ArrivalVouchs b ON a.Rd01AutoID= b.AutoID";
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
|
|
sql = @"UPDATE b SET freceivedqty = ISNULL(freceivedqty,0)+a.qty,freceivednum = ISNULL(freceivednum,0)+a.num
|
|
FROM ##POsumQty a INNER JOIN {0}.dbo.PO_Podetails b ON a.iPOsID= b.ID ";
|
|
sql = string.Format(sql, Context.ERPDataName);
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
|
|
#region 写入记账表
|
|
sql = @" INSERT INTO {0}.[dbo].[IA_ST_UnAccountVouch01]
|
|
SELECT '{1}',erpdid,'{2}','{3}' FROM ##rdqty
|
|
drop table ##rdqty
|
|
drop table ##sumQty
|
|
drop table ##POsumQty";
|
|
sql = string.Format(sql, Context.ERPDataName, Context.ERPrdID, "01", "普通采购");
|
|
cmd.CommandText = sql;
|
|
cmd.ExecuteNonQuery();
|
|
#endregion
|
|
|
|
#endregion
|
|
}
|
|
|
|
|
|
//throw new Exception("操作完成那个，取消保存，来进行下一次测试");
|
|
cmd.Transaction.Commit();
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
cmd.Transaction.Rollback();
|
|
throw ex;
|
|
}
|
|
|
|
|
|
}
|
|
|
|
#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 storageInfoContext
|
|
{
|
|
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; }
|
|
|
|
|
|
}
|
|
}
|