public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
lock (lockobj)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
BaseAdo ba = new BaseAdo();
string sql = @"
declare @dj varchar(30)
exec PROC_GETID 'RW03',@dj output
insert into RW03(RW03001,RW03002,RW03003,RW03004,RW03005,RW03006,RW03007,RW03008)
values(@dj,getdate(),@userid,@username,'','','',N'创建条码')
select @dj t";
DataSet dsdj = ba.GetDataSet(sql, new SqlParameter("@userid", this.LoginUserID), new SqlParameter("@username", this.LoginUserName));
string dj = "";
if (dsdj != null && dsdj.Tables[0].Rows.Count > 0)
dj = dsdj.Tables[0].Rows[0][0] + "";
int count = 0;
foreach (POLineModel item in this.POLineModels)
{
item.LoginUserID = this.LoginUserID;
item.LoginUserName = this.LoginUserName;
item.Danju = dj;
item.Insert();
count += item.BarcodeQTY;
}
DataSet ds = ba.GetDataSet("select top " + count + " RW01001 from RW01 order by RW01001 desc");
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string[] ids = new string[ds.Tables[0].Rows.Count];
for (int i = 0; i < ids.Length; i++)
{
ids[i] = ds.Tables[0].Rows[i][0] + "";
}
this.ReturnIDS = ids;
}
ts.Complete();
return this;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
lock (lockobj)
{
try
{
using (TransactionScope ts = new TransactionScope())
{
BaseAdo ba = new BaseAdo();
string sql = @"
declare @dj varchar(30)
exec PROC_GETID 'RW03',@dj output
select @dj t";
DataSet dsdj = ba.GetDataSet(sql);
string dj = "";
if (dsdj != null && dsdj.Tables[0].Rows.Count > 0)
dj = dsdj.Tables[0].Rows[0][0] + "";
int count = 0;
foreach (WOModel item in this.WOModels)
{
item.LoginUserID = this.LoginUserID;
item.LoginUserName = this.LoginUserName;
item.Danju = dj;
item.WorkOrder = item.WorkOrder;
item.Insert();
count += item.BarcodeQty;
}
DataSet ds = ba.GetDataSet("select top " + count + " RW01001 from RW01 order by RW01001 desc");
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string[] ids = new string[ds.Tables[0].Rows.Count];
for (int i = 0; i < ids.Length; i++)
{
ids[i] = ds.Tables[0].Rows[i][0] + "";
}
this.ReturnIDS = ids;
}
ts.Complete();
return this;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
public override List<BaseSearchModel> GetALL(bool enableSearch = false)
{
List<BaseSearchModel> lst = new List<BaseSearchModel>();
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet("select UR04003 from UR04 where UR04002=@roleID", new SqlParameter("@roleID", this.RoleID));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string[] rs = new string[ds.Tables[0].Rows.Count];
for (int i = 0; i < rs.Length; i++)
{
rs[i] = ds.Tables[0].Rows[i][0] + "";
}
this.Rights = rs;
}
lst.Add(this);
return lst;
}
public override BaseSearchModel Insert()
{
if (SOLines == null || SOLines.Length == 0)
throw new Exception("请选择发货单项");
string sql = @"
declare @id varchar(30)
exec PROC_GETID 'RW18',@id output
insert into RW18(RW18001,RW18002,RW18003,RW18004,RW18005,RW18006,RW18007,RW18008,RW18009)
values(@id,getdate(),@userid,@username,1,@CustomerCode,@CustomerName,@so,@dn)
";
List<SqlParameter> list = new List<SqlParameter>();
list.Add(new SqlParameter("@userid", this.LoginUserID));
list.Add(new SqlParameter("@username", this.LoginUserName));
list.Add(new SqlParameter("@CustomerCode", SOLines[0].CustomerCode));
list.Add(new SqlParameter("@CustomerName", SOLines[0].CustomerName));
list.Add(new SqlParameter("@so", SOLines[0].OrderNumber));
list.Add(new SqlParameter("@dn", SOLines[0].DeliveryNumber));
StringBuilder sb = new StringBuilder();
sb.Append(sql);
int i = 10;
foreach (SOLineModel item in this.SOLines)
{
sb.Append(string.Format(@"insert into RW19(RW19002,RW19003,RW19004,RW19005,RW19006,RW19007,RW19008,RW19009,RW19010,RW19011,RW19012,RW19013,RW19014)
values(@line{0},@id,getdate(),@userid,@username,@stock{0},@stockname{0},@orderqty{0},@unit{0},0,@orderqty{0},@warehouse{0},@batch{0})
", i));
list.Add(new SqlParameter("@line" + i, item.LineNumber));
list.Add(new SqlParameter("@stock" + i, item.StockCode));
list.Add(new SqlParameter("@stockname" + i, item.StockName));
list.Add(new SqlParameter("@orderqty" + i, item.OrderQty));
list.Add(new SqlParameter("@unit" + i, item.Unit));
list.Add(new SqlParameter("@warehouse" + i, item.Warehouse));
list.Add(new SqlParameter("@batch" + i, item.Batch));
i = i + 10;
}
sb.Append("select @id SONO");
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sb.ToString(), list.ToArray());
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count >= 1)
this.SONO = ds.Tables[0].Rows[0][0] + "";
return this;
}
public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
if (WOLine == null || WOLine.Length == 0)
throw new Exception("请选择领料单项");
string sql = @"
declare @id varchar(30)
exec PROC_GETID 'RW08',@id output
insert into RW08(RW08001,RW08002,RW08003,RW08004,RW08005,RW08006,RW08007,RW08008)
values(@id,getdate(),@userid,@username,@HopeTime,@isUrgent,@Remark,1)
";
List<SqlParameter> list = new List<SqlParameter>();
list.Add(new SqlParameter("@userid", this.LoginUserID));
list.Add(new SqlParameter("@username", this.LoginUserName));
list.Add(new SqlParameter("@HopeTime", this.HopeTime));
list.Add(new SqlParameter("@isUrgent", this.isUrgent));
list.Add(new SqlParameter("@Remark", this.Remark));
StringBuilder sb = new StringBuilder();
sb.Append(sql);
int i = 10;
foreach (WOLineModel item in this.WOLine)
{
sb.Append(string.Format(@"insert into RW09(RW09002,RW09003,RW09004,RW09005,RW09006,RW09007,RW09008,RW09009,RW09010,RW09011,RW09012)
values(@line{0},@id,getdate(),@userid,@username,@stock{0},@stockname{0},@planqty{0},@unit{0},0,@planqty{0})
", i));
list.Add(new SqlParameter("@line"+i, item.Number));
list.Add(new SqlParameter("@stock" + i, item.StockCode));
list.Add(new SqlParameter("@stockname" + i, item.StockName));
list.Add(new SqlParameter("@planqty" + i, item.PlanQTY));
list.Add(new SqlParameter("@unit" + i, item.Unit));
i = i + 10;
}
sb.Append("select @id PLNO");
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sb.ToString(), list.ToArray());
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count>=1)
this.PLNO = ds.Tables[0].Rows[0][0] + "";
return this;
}
public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
//RW01中加入流水号字段。
//①：生成流水号字段
//--------------分包：
//RP+YY+MM+0000
//--------------重命名：
//RN+YY+MM+0000
//依据月份来计算流水号。
//②：修改RW01流水号字段。
//③：创建connectivity 文件
using (TransactionScope ts = new TransactionScope())
{
string sql = @"
declare @pr varchar(30)
exec PROC_GETID @type,@pr output
select @pr PR,RW01008,RW01009 from RW01 where RW01001=@bid
";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql, new SqlParameter("@bid", this.IssueBarcodes[0]), new SqlParameter("@type", this.Action == 1 ? "PR" : "RN"));
EnterWarehouseModel ewm = new EnterWarehouseModel();
ewm.Barcodes = this.ReceiveBarcodes;
ewm.Bin = ds.Tables[0].Rows[0]["RW01009"] + "";
ewm.Warehouse = ds.Tables[0].Rows[0]["RW01008"] + "";
ewm.FRNumber = ds.Tables[0].Rows[0]["PR"] + "";
ewm.Insert();
DestroyBarcodeModel dbm = new DestroyBarcodeModel();
dbm.Barcodes = this.IssueBarcodes;
dbm.isExportFile = true;
dbm.FRNumber = ds.Tables[0].Rows[0]["PR"] + "";
dbm.Insert();
ts.Complete();
}
return this;
}
private void ExportXML(string bids)
{
string sql = @"
select FG01016,FG01017,FG01008,FG01009,FG01002,SUM(FG01006) FG01006,FG01007,FG01037 from FG01 where FG01001 IN ([BIDS])
group by FG01016,FG01017,FG01008,FG01009,FG01002,FG01007,FG01034
";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
string company = ds.Tables[0].Rows[0]["FG01037"] + "";
StringBuilder export = new StringBuilder();
export.Append(@"
<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
<msg:Hdr>
<msg:Sender>
<msg:Name>Generic Sender</msg:Name>
<msg:Subname>[COM]-Scala</msg:Subname>
</msg:Sender>
</msg:Hdr>
<msg:Body>
<msg:Req msg-type=""Stock Transaction"" action=""Process"">
<msg:Dta>
<dta:StockTransaction xsi:schemaLocation=""http://www.scala.net/StockTransaction/1.1 http://scshost/schemas/Scala/1.1/StockTransaction.xsd"" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/StockTransaction/1.1"">");
export.Replace("[COM]", company);
string transTemp = @"
<dta:Movement>
<dta:TransDate>{0}</dta:TransDate>
<dta:Qty>{1}</dta:Qty>
<dta:StockCode>{2}</dta:StockCode>
<dta:WhCodeFrom>{3}</dta:WhCodeFrom>
<dta:BinCodeTo>{4}</dta:BinCodeTo>
<dta:WhCodeTo>{5}</dta:WhCodeTo>
<dta:BinCodeFrom>{6}</dta:BinCodeFrom>
<dta:BatchCode BatchHandlingMethod=""1"">{7}</dta:BatchCode>
</dta:Movement>";
foreach (DataRow item in ds.Tables[0].Rows)
{
export.Append(string.Format(transTemp, DateTime.Now.ToString("yyyy-MM-dd"), item["FG01006"], item["FG01002"], item["FG01016"], item["FG01008"], item["FG01009"], item["FG01017"], item["FG01007"]));
}
export.Append(@"
</dta:StockTransaction>
</msg:Dta>
</msg:Req>
</msg:Body>
</msg:Msg>
");
string[] path = SysConfig.GetXMLExportPath(company);
string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
}
private void ExportXML(string bids)
{
string sql = @"
select FG01002,SUM(FG01006) FG01006,FG01033,FG01034,FG01008,FG01009,FG01037,FG01007 from FG01 where FG01001 IN ([BIDS])
group by FG01002,FG01033,FG01034,FG01008,FG01009,FG01037,FG01007
select SUM(FG01006) FG01006 from FG01 where FG01001 IN ([BIDS])";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
string company = ds.Tables[0].Rows[0]["FG01037"] + "";
string ordernum = ds.Tables[0].Rows[0]["FG01033"] + "";
StringBuilder export = new StringBuilder();
export.Append(@"
<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
<msg:Hdr>
<msg:Sender>
<msg:Name>Generic Sender</msg:Name>
<msg:Subname>[COM]-Scala</msg:Subname>
</msg:Sender>
</msg:Hdr>
<msg:Body>
<msg:Req msg-type=""Work Order Receipt"" action=""Process"">
<msg:Dta>
<dta:WorkOrderReceiptRequest xsi:schemaLocation=""http://www.scala.net/WorkOrderReceiptRequest/1.1 http://scshost/schemas/Scala/1.1/WorkOrderReceiptRequest.xsd "" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/WorkOrderReceiptRequest/1.1"">
<dta:WorkorderNumber>[WO]</dta:WorkorderNumber>
<dta:ReceiptDate>[DATE]</dta:ReceiptDate>
<dta:ReceiptWarehouse>[WH]</dta:ReceiptWarehouse>
<dta:ReceiptQuantity>[SUMQTY]</dta:ReceiptQuantity>
<dta:CloseWorkorder>1</dta:CloseWorkorder>
<dta:ReceiptLines>");
export.Replace("[COM]", company);
export.Replace("[WO]", ordernum);
export.Replace("[WH]", ds.Tables[0].Rows[0]["FG01008"] + "");
export.Replace("[SUMQTY]", ds.Tables[1].Rows[0]["FG01006"] + "");
export.Replace("[DATE]", DateTime.Now.ToString("yyyy-MM-dd"));
string receTemp = @"
<dta:ReceiptLine>
<dta:Batch>
<dta:BatchCode>{0}</dta:BatchCode>
</dta:Batch>
<dta:ReceiptWarehouse>{1}</dta:ReceiptWarehouse>
<dta:ReceiptDate>{2}</dta:ReceiptDate>
<dta:ReceiptQuantity>{3}</dta:ReceiptQuantity>
<dta:BinCode>{4}</dta:BinCode>
</dta:ReceiptLine>";
int i = 0;
foreach (DataRow item in ds.Tables[0].Rows)
{
export.Append(string.Format(receTemp, item["FG01007"], item["FG01008"], DateTime.Now.ToString("yyyy-MM-dd"), item["FG01006"], item["FG01009"]));
}
export.Append(@"
</dta:ReceiptLines>
</dta:WorkOrderReceiptRequest>
</msg:Dta>
</msg:Req>
</msg:Body>
</msg:Msg>
");
string[] path = SysConfig.GetXMLExportPath(company);
string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
}
public override List<BaseSearchModel> GetALL(bool enableSearch = false)
{
string wherebarcode = "";
string wherescala = "";
List<SqlParameter> list = new List<SqlParameter>();
if (enableSearch)
{
if (!string.IsNullOrEmpty(this.Stock))
{
wherebarcode += "and RW01002=@stock ";
wherescala += "and SC33001=@stock ";
list.Add(new SqlParameter("@stock", this.Stock));
}
if (!string.IsNullOrEmpty(this.SupplierBatch))
{
wherebarcode += "and RW01035=@supbatch ";
wherescala += "and SC33022=@supbatch ";
list.Add(new SqlParameter("@supbatch", this.SupplierBatch));
}
if (!string.IsNullOrEmpty(this.Warehouse))
{
wherebarcode += "and RW01008=@wh ";
wherescala += "and SC33002=@wh ";
list.Add(new SqlParameter("@wh", this.Warehouse));
}
if (!string.IsNullOrEmpty(this.Bin))
{
wherebarcode += "and RW01009=@bin ";
wherescala += "and SC33004=@bin ";
list.Add(new SqlParameter("@bin", this.Bin));
}
}
BaseAdo ba = new BaseAdo();
string sqlbarcode = @"
select RW01002,RW01008,RW01009,round(sum(RW01006/RW01040),2) KCQTY,RW01035,RW01034,round(sum(case when RW12001 is null then 0 else RW01006/RW01040 end),2) PDQTY FROM RW01 left join RW12 ON RW01001=RW12006
WHERE RW01032=2 [WHERE] GROUP BY RW01002,RW01008,RW01009,RW01035,RW01034 order by RW01002,RW01008,RW01009";
sqlbarcode = sqlbarcode.Replace("[WHERE]", wherebarcode);
DataSet dsbarcode = ba.GetDataSet(sqlbarcode, list.ToArray());
string sqlscala = @"
SELECT SC33001,SC33002,SC33004,round(SUM(SC33005),2) SC33005,SC33022,'31' Company FROM SC333100
WHERE SC33005<>0 [WHERE]
GROUP BY SC33001,SC33002,SC33004,SC33022
union all
SELECT SC33001,SC33002,SC33004,round(SUM(SC33005),2) SC33005,SC33022,'32' Company FROM SC333200
WHERE SC33005<>0 [WHERE]
GROUP BY SC33001,SC33002,SC33004,SC33022 ORDER BY SC33022";
sqlscala = sqlscala.Replace("[WHERE]", wherescala);
ba.ConnectionStr = SysConfig.DBScalaConnection;
DataSet dsscala = ba.GetDataSet(sqlscala, list.ToArray());
List<RWStockReportModel> result = new List<RWStockReportModel>();
foreach (DataRow item in dsbarcode.Tables[0].Rows)
{
RWStockReportModel rm = new RWStockReportModel()
{
BarcodeQty = float.Parse(item["KCQTY"] + ""),
PDQTY = float.Parse(item["PDQTY"] + ""),
Bin = (item["RW01009"] + "").Trim(),
Warehouse = (item["RW01008"] + "").Trim(),
Stock = (item["RW01002"] + "").Trim(),
SupplierBatch = (item["RW01035"] + "").Trim(),
Company = (item["RW01034"] + "").Trim()
};
result.Add(rm);
}
foreach (DataRow item in dsscala.Tables[0].Rows)
{
RWStockReportModel rm = new RWStockReportModel()
{
ScalaQty = float.Parse(item["SC33005"] + ""),
Bin = (item["SC33004"] + "").Trim(),
Warehouse = (item["SC33002"] + "").Trim(),
Stock = (item["SC33001"] + "").Trim(),
SupplierBatch = (item["SC33022"] + "").Trim(),
Company = (item["Company"] + "").Trim(),
PDQTY = 0
};
if (result.Exists(m => m.Warehouse == rm.Warehouse && m.Bin == rm.Bin && m.Stock == rm.Stock && m.SupplierBatch == rm.SupplierBatch && m.Company == rm.Company))
{
RWStockReportModel rtemp = result.Find(m => m.Warehouse == rm.Warehouse && m.Bin == rm.Bin && m.Stock == rm.Stock && m.SupplierBatch == rm.SupplierBatch && m.Company == rm.Company);
rm.BarcodeQty = rtemp.BarcodeQty;
rm.PDQTY = rtemp.PDQTY;
}
else
result.Add(rm);
}
return result.ConvertAll<BaseSearchModel>(m => m as BaseSearchModel);
}
public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
lock (objLock)
{
using (TransactionScope ts = new TransactionScope())
{
BaseAdo ba = new BaseAdo();
BaseAdo bascala = new BaseAdo() { ConnectionStr = SysConfig.DBScalaConnection };
string sqlpo = @"
select * from view_poline where PC01001=@po and SC01003=@stockspec and Company=@company
select * from view_Stock where SC01003=@stockspec and Company=@company
";
string sql = @"
insert into RW13(RW13002,RW13003,RW13004,RW13005,RW13006,RW13007,RW13008,RW13009,RW13010,RW13011,RW13012,RW13013,RW13014,RW13015,RW13016,RW13017,RW13018,RW13019,RW13020,RW13021,RW13022)
values(getdate(),@IMPNO,@userid,@po,@poline,@stockcode,@stockname,@stockspec,@lpn,@supplierbatch,@productdate,@exdate,@qty,@unit,@company,@boxid,@supcode,@supname,@pounit,@saleunit,@stocktype)";
string strImport = System.Text.Encoding.ASCII.GetString(this.Bytes);
string[] strImportLine = strImport.Replace("\r", " ").Split('\n');
IMPNO = Guid.NewGuid().ToString();
for (int i = 1; i < strImportLine.Length; i++)
{
string BarcodeId = "";//导入文件中获取
string PackageId = "";//导入文件中获取
string StockCode = "";
string StockName = "";
string StockSpec = "";//导入文件中获取
string StockUnit = "";//导入文件中获取
string PO = "";//导入文件中获取
decimal StockUnitQty = 0;//导入文件中获取
string Company = "";//导入文件中获取
string SupplierBatch = "";//导入文件中获取
string supplierCode = "";
string supplierName = "";
double purunit = 1;
double saleunit = 1;
string stockType = "02";
string poline = "";
DateTime ProductionTime = DateTime.Now;//导入文件中获取
DateTime ValidityTime = DateTime.Now;//导入文件中获取
string[] strImprotLineItem = strImportLine[i].Split(';');
switch (strImprotLineItem[0])
{
case "11117": Company = "31"; break;
case "11116": Company = "32"; break;
default: Company = "-"; break;
}
PO = strImprotLineItem[2];
StockSpec = strImprotLineItem[3];
PackageId = strImprotLineItem[5];
BarcodeId = strImprotLineItem[6];
SupplierBatch = strImprotLineItem[7];
StockUnitQty = decimal.Parse(strImprotLineItem[10]);
DateTime.TryParse(strImprotLineItem[15], out ProductionTime);
DateTime.TryParse(strImprotLineItem[16], out ValidityTime);
DataSet ds = bascala.GetDataSet(sqlpo, new SqlParameter("@Company", Company), new SqlParameter("@stockspec", StockSpec), new SqlParameter("@po", PO));
if (ds.Tables[0].Rows.Count > 0)
{
poline = ds.Tables[0].Rows[0]["PC03002"] + "";
supplierCode = ds.Tables[0].Rows[0]["PL01001"] + "";
supplierName = ds.Tables[0].Rows[0]["PL01002"] + "";
}
if (ds.Tables[1].Rows.Count > 0)
{
double.TryParse(ds.Tables[1].Rows[0]["SC01072"] + "", out purunit);
double.TryParse(ds.Tables[1].Rows[0]["SC01073"] + "", out saleunit);
stockType = ds.Tables[1].Rows[0]["SC01035"] + "";
StockCode = ds.Tables[1].Rows[0]["SC01001"] + "";
StockName = ds.Tables[1].Rows[0]["SC01002"] + "";
StockUnit = ds.Tables[1].Rows[0]["UNIT"] + "";
}
ba.ExecuteSql(sql, new SqlParameter("@IMPNO", IMPNO),
new SqlParameter("@userid", this.LoginUserID),
new SqlParameter("@po", PO),
new SqlParameter("@poline", poline),
new SqlParameter("@stockcode", StockCode),
new SqlParameter("@stockname", StockName),
new SqlParameter("@stockspec", StockSpec),
new SqlParameter("@lpn", BarcodeId),
new SqlParameter("@supplierbatch", SupplierBatch),
new SqlParameter("@productdate", ProductionTime),
new SqlParameter("@exdate", ValidityTime),
new SqlParameter("@qty", StockUnitQty),
new SqlParameter("@unit", StockUnit),
new SqlParameter("@company", Company),
new SqlParameter("@boxid", PackageId),
new SqlParameter("@supcode", supplierCode),
new SqlParameter("@supname", supplierName),
new SqlParameter("@pounit", purunit),
new SqlParameter("@saleunit", saleunit),
new SqlParameter("@stocktype", stockType));
}
ModelAdo<USBarcodeModel> adoUB = new ModelAdo<USBarcodeModel>();
this.ReturnUSBarcodes = adoUB.GetList("RW13003=@impno", "", "", new SqlParameter("@impno", IMPNO));
ModelAdo<USBarcodeGroup> adoUBG = new ModelAdo<USBarcodeGroup>();
this.ReturnUSBarcodeGroups = adoUBG.GetList("RW13003=@impno", "", "", new SqlParameter("@impno", IMPNO));
ts.Complete();
}
}
return this;
}
public static List<KeyValuePair<string, double>> GetScalaBatch(string stock, string warehouse, string bin, string supplierbatch, double qty, string company)
{
List<KeyValuePair<string, double>> list = new List<KeyValuePair<string, double>>();
string sql = @"SELECT SC33001,SC33002,SC33003,SC33004,SUM(SC33005) SC33005,SC33022,SC33010 FROM SC33[COM]00 WHERE SC33005<>0
and SC33001=@stock and SC33002=@wh and SC33004=@sh and (SC33022=@supbatch or SC33009=@supbatch)
GROUP BY SC33001,SC33002,SC33003,SC33004,SC33022,SC33010 ORDER BY SC33003
select SC01002 FROM SC01[COM]00 WHERE SC01001=@stock";
sql = sql.Replace("[COM]", company);
double qtytemp = 0;
double blockqty = 0;
BaseAdo ba = new BaseAdo();
ba.ConnectionStr = SysConfig.DBScalaConnection;
DataSet ds = ba.GetDataSet(sql, new SqlParameter("@stock", stock), new SqlParameter("@wh", warehouse), new SqlParameter("@sh", bin), new SqlParameter("@supbatch", supplierbatch));
string stockname = "";
if (ds.Tables[1].Rows.Count > 0)
stockname = ds.Tables[1].Rows[0][0] + "";
foreach (DataRow item in ds.Tables[0].Rows)
{
double q = double.Parse(item["SC33005"] + "");
//if (item["SC33010"] + "" == "0")
//{
if (qtytemp + q >= qty)
{
list.Add(new KeyValuePair<string, double>(item["SC33003"] + "", qty - qtytemp));
qtytemp = qty;
break;
}
else
{
list.Add(new KeyValuePair<string, double>(item["SC33003"] + "", q));
qtytemp = qtytemp + q;
}
//}
//else
// blockqty += q;
}
if (qtytemp < qty)
throw new Exception("物料" + stock + " " + stockname + " 批次" + supplierbatch + " " + warehouse + "_" + bin + " SCALA可移库存仅有" + qtytemp + ",锁住库存" + blockqty);
return list;
}
public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
//①：解析获取的文件数据，依据每行来读取。
//②：替换对应公司，17-31,16-32[[不存在回滚]]
//③：替换PART_CODE，通过SC01003，找到对应的StockName，StockCode[不存在回滚]
//④：条码编号：[存在回滚]
//⑤：是否存在装箱号条码。有：判断装箱条码中是否存在，如果存在：直接并入该装箱号条码。如果不存在：新建
//⑥：插入原材料： 1：插入，父单据号表；
// 2：插入，子单据号明细表；
// 3：插入，原材料条码表；
// 4：插入，条码历史记录表；
lock (objLock)
{
using (TransactionScope ts = new TransactionScope())
{
try
{
BaseAdo ba = new BaseAdo();
string sql = @"
declare @billid varchar(30)
exec PROC_GETID 'RW03',@billid output
--单据头
insert into RW03(RW03001,RW03002,RW03003,RW03004,RW03005,RW03006,RW03007,RW03008)
values(@billid,getdate(),@userid,@username,'','','',N'创建条码')
select @billid t";
DataSet dsBillId = ba.GetDataSet(sql, new SqlParameter("@userid", this.LoginUserID),
new SqlParameter("@username", this.LoginUserName));
string BillId = ""; //总单据号
if (dsBillId != null && dsBillId.Tables[0].Rows.Count > 0)
{
BillId = dsBillId.Tables[0].Rows[0][0] + "";
}
string strImport = System.Text.Encoding.ASCII.GetString(this.Bytes);
string[] strImportLine = strImport.Replace("\r", " ").Split('\n');
for (int i = 1; i < strImportLine.Length; i++)
{
string BarcodeId = "";//导入文件中获取
string PackageId = "";//导入文件中获取
string StockCode = "";
string StockName = "";
string StockSpec = "";//导入文件中获取
string StockUnit = "";//导入文件中获取
string PO = "";//导入文件中获取
decimal StockUnitQty = 0;//导入文件中获取
string Company = "";//导入文件中获取
string SupplierBatch = "";//导入文件中获取
DateTime ProductionTime = DateTime.Now;//导入文件中获取
DateTime ValidityTime = DateTime.Now;//导入文件中获取
string[] strImprotLineItem = strImportLine[i].Split(';');
if (strImprotLineItem[0].Contains("17"))
{
Company = "31";
}
else if (strImprotLineItem[0].Contains("16"))
{
Company = "32";
}
else
{
throw new Exception("未找到相对应的公司信息！");
}
PO = strImprotLineItem[2];
StockSpec = strImprotLineItem[3];
PackageId = strImprotLineItem[5];
BarcodeId = strImprotLineItem[6];
SupplierBatch = strImprotLineItem[7];
StockUnitQty = decimal.Parse(strImprotLineItem[10]);
DateTime.TryParse(strImprotLineItem[15], out ProductionTime);
DateTime.TryParse(strImprotLineItem[16], out ValidityTime);
ModelAdo<RWBarcodeModel> adoBarcode = new ModelAdo<RWBarcodeModel>();
RWBarcodeModel rwModel = adoBarcode.GetModelByID(BarcodeId);
if (rwModel != null && rwModel.ID.Length >= 1)
{
throw new Exception(BarcodeId + "已存在于条码系统中！");
}
if (!string.IsNullOrEmpty(PackageId))
{
ModelAdo<PackingModel> adoPackage = new ModelAdo<PackingModel>();
PackingModel packageModel = adoPackage.GetModelByID(PackageId);
if (packageModel != null)
{
}
else
{
string sqlPackage = @"
--装箱单
insert into RW07(RW07001,RW07002,RW07003,RW07004,RW07005,RW07006,RW07007,RW07008)
values(@packageid,getdate(),@userid,@username,0,'',0,2)";
ba.ExecuteSql(sqlPackage, new SqlParameter("packageid", PackageId),
new SqlParameter("@userid", this.LoginUserID),
new SqlParameter("@username", this.LoginUserName));
}
}
ModelAdo<StockModel> adoStock = new ModelAdo<StockModel>();
List<StockModel> Stocks = adoStock.GetList("SC01003=@stockspec", "", "", new SqlParameter("@stockspec", StockSpec));
if (Stocks != null && Stocks.Count >= 1)
{
StockCode = Stocks[0].StockCode;
StockName = Stocks[0].StockName;
StockUnit = Stocks[0].Unit;
StockUnitQty = Stocks[0].UQTY;
}
else
{
//throw new Exception("未找到相对应的物料信息！");
continue;
}
string barcodeSql = @"
--单据明细
insert into RW04(RW04002,RW04003,RW04004,RW04005) values(getdate(),@billid,@barcodeid,N'创建条码')
--历史记录
insert into RW02(RW02002,RW02003,RW02004,RW02005,RW02010,RW02011)
values(@barcodeid,getdate(),@userid,@username,@billid,N'创建条码标签')
--原材料
insert into RW01(RW01001,RW01002,RW01003,RW01004,RW01005,RW01006,RW01014,RW01024,RW01025,RW01027,RW01031,RW01032,RW01034,RW01035,RW01037,RW01038,RW01040,RW01041,RW01043)
values(@barcodeid,@stock,@stockname,@stockspec,@unit,@qty,@po,@userid,@username,@billid,@packageid,1,@company,@supplierbatch,@productiontime ,@validitytime,@tqty,@stype,'PO')
";
ba.ExecuteSql(barcodeSql, new SqlParameter("@userid", this.LoginUserID),
new SqlParameter("@username", this.LoginUserName),
new SqlParameter("@stock", StockCode),
new SqlParameter("@stockname", StockName),
new SqlParameter("@barcodeid", BarcodeId),
new SqlParameter("@stockspec", StockSpec),
new SqlParameter("@packageid", PackageId),
new SqlParameter("@unit", StockUnit),
new SqlParameter("@qty", StockUnitQty),
new SqlParameter("@tqty", Stocks[0].UQTY),
new SqlParameter("@po", PO),
new SqlParameter("@stype", Stocks[0].StockType),
new SqlParameter("@company", Company),
new SqlParameter("@supplierbatch", SupplierBatch),
new SqlParameter("@productiontime", ProductionTime),
new SqlParameter("@validitytime", ValidityTime),
new SqlParameter("@billid", BillId));
}
ts.Complete();
}
catch (Exception ex)
{
throw ex;
}
}
}
return this;
}
private void GetLoginUser(string accesstoken, string modeltype)
{
string sql = @"
select UR01001,UR01005 from UR01 where UR01001 in (select UR05002 from UR05 where UR05001=@act and UR05005=1)
";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql, new SqlParameter("@act", accesstoken));
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
this.loginUserID = ds.Tables[0].Rows[0][0] + "";
this.loginUserName = ds.Tables[0].Rows[0][1] + "";
}
else
throw new Exception("无法访问，未登录系统");
}
private void ExportXML(string bids)
{
string sql = @"
select FG01030,FG01039,FG01002,FG01007,sum(FG01006) FG01006,FG01037 from FG01 where FG01001 in ([BIDS]) GROUP BY FG01030,FG01039,FG01002,FG01007,FG01037";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
string company = ds.Tables[0].Rows[0]["FG01037"] + "";
string ordernum = ds.Tables[0].Rows[0]["FG01030"] + "";
StringBuilder export = new StringBuilder();
export.Append(@"
<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
<msg:Hdr>
<msg:Sender>
<msg:Name>Generic Sender</msg:Name>
<msg:Subname>[COM]-Scala</msg:Subname>
</msg:Sender>
</msg:Hdr>
<msg:Body>
<msg:Req msg-type=""Sales Order Delivery"" action=""Process"">
<msg:Dta>
<dta:SalesOrderDelivery xsi:schemaLocation=""http://www.scala.net/SalesOrderDelivery/1.1 http://scshost/schemas/Scala/1.1/SalesOrderDelivery.xsd"" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/SalesOrderDelivery/1.1"">
<dta:OrderHeader>
<dta:OrdNum>[SO]</dta:OrdNum>
</dta:OrderHeader>
<dta:OrderLineList>");
export.Replace("[COM]", company);
export.Replace("[SO]", ordernum);
string delLine = @"
<dta:OrderLine>
<dta:LineNum>{0}</dta:LineNum>
<dta:StockCode>{1}</dta:StockCode>
<dta:QtyDeliv>{2}</dta:QtyDeliv>
<dta:DelivDateAct>{3}</dta:DelivDateAct>
</dta:OrderLine>";
foreach (DataRow item in ds.Tables[0].Rows)
{
export.Append(string.Format(delLine, item["FG01039"], item["FG01002"], item["FG01006"], DateTime.Now.ToString("yyyy-MM-dd")));
}
export.Append(@"
</dta:OrderLineList>
</dta:SalesOrderDelivery>
</msg:Dta>
</msg:Req>
</msg:Body>
</msg:Msg>
");
string[] path = SysConfig.GetXMLExportPath(company);
string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
}
public override BarcodeModel.MODEL.BaseSearchModel Insert()
{
using (TransactionScope ts = new TransactionScope())
{
ModelAdo<RWBarcodeModel> adoRW = new ModelAdo<RWBarcodeModel>();
RWBarcodeModel barcode = adoRW.GetModelByID(this.RWID);
ModelAdo<ReportModel> adoRM = new ModelAdo<ReportModel>();
ReportModel rm = adoRM.GetModelByID(this.ReportLineID + "");
if (barcode.Warehouse != rm.Warehouse)
{
throw new Exception("此工单需汇报" + rm.Warehouse + "仓库的料");
}
string sql = @"
declare @lineno varchar(30)
declare @reportnumber varchar(30)
declare @stock varchar(40)
declare @stocktype varchar(40)
declare @barcodeQTY numeric(15,3)
select @wo=WK02002,@lineno=WK02012,@reportnumber=WK02013 from WK02 where WK02001=@rlid
select @stock=RW01002,@stocktype=RW01041,@barcodeQTY=RW01006 from RW01 where RW01001=@bid
if(@barcodeQTY=@deqty)
update RW01 set RW01032=3 where RW01001=@bid
else
update RW01 set RW01006=RW01006-@deqty where RW01001=@bid
insert into RW02(RW02002,RW02003,RW02004,RW02005,RW02011)
select RW01001,getdate(),@userid,@username,N'扣料 工单号'+@wo+' 数量'+convert(varchar(10),@deqty) from RW01 where RW01001=@bid
update WK02 set WK02008=case when WK02008 is null then getdate() else WK02008 end,WK02009=WK02009+@deqty,WK02010=@wc where WK02001=@rlid
update WK01 set WK01005=getdate() where WK01001=@wo and WK01005 is null and WK01006 is null
if not exists(select * from WK04 where WK04001=@wo and WK04002=@wc and WK04004 is null)
insert into WK04(WK04001,WK04002,WK04003)
values(@wo,@wc,getdate())
if not exists(select * from WK02 where WK02002=@wo and WK02009<WK02005)
update WK01 set WK01008=3 where WK01001=@wo
else
update WK01 set WK01008=2 where WK01001=@wo
insert into WK05(WK05002,WK05003,WK05004,WK05005,WK05006,WK05007,WK05008,WK05009,WK05010)
values(@wc,@wo,@bid,@stock,@deqty,@stocktype,getdate(),case @stocktype when '02' then 1 else 0 end,@rlid)
select @wo WO,@reportnumber RN,@lineno LN,RW01017,RW01008,RW01009,RW01002,RW01006/RW01040 RW01006,RW01007,RW01034,RW01035,RW01041 from RW01 where RW01001=@bid
";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql,
new SqlParameter("@userid", this.LoginUserID),
new SqlParameter("@username", this.LoginUserName),
new SqlParameter("@wo", this.WorkOrder),
new SqlParameter("@bid", this.RWID),
new SqlParameter("@deqty", this.DeQTY),
new SqlParameter("@rlid", this.ReportLineID),
new SqlParameter("@wc", this.WorkCenter));
if (ds.Tables[0].Rows[0]["RW01041"] + "" != "04" && ds.Tables[0].Rows[0]["RW01041"] + "" != "00")
{
List<Hashtable> list = new List<Hashtable>();
Hashtable h = new Hashtable();
//h["Qty"] = ds.Tables[0].Rows[0]["RW01006"];
h["Qty"] = this.DeQTY;
h["StockCode"] = ds.Tables[0].Rows[0]["RW01002"];
h["ReportNumber"] = ds.Tables[0].Rows[0]["RN"];
h["LineNumber"] = ds.Tables[0].Rows[0]["LN"];
h["Bin"] = ds.Tables[0].Rows[0]["RW01009"];
h["Company"] = ds.Tables[0].Rows[0]["RW01034"];
h["SupplierBatch"] = ds.Tables[0].Rows[0]["RW01035"];
h["WorkOrder"] = ds.Tables[0].Rows[0]["WO"];
h["Warehouse"] = ds.Tables[0].Rows[0]["RW01008"];
list.Add(h);
StockTransactionXml.ExportWOReportXML(list);
}
ts.Complete();
return this;
}
}
private void ExportXML(string bids)
{
string sql = @"
select RW01016,RW01017,RW01008,RW01009,RW01002,SUM(RW01006/RW01040) RW01006,RW01007,RW01034,RW01035 from RW01 where RW01001 IN ([BIDS])
group by RW01016,RW01017,RW01008,RW01009,RW01002,RW01007,RW01034,RW01035
";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
List<Hashtable> list = new List<Hashtable>();
foreach (DataRow item in ds.Tables[0].Rows)
{
Hashtable h = new Hashtable();
h["Qty"] = item["RW01006"];
h["StockCode"] = item["RW01002"];
h["WhCodeFrom"] = item["RW01016"];
h["BinCodeFrom"] = item["RW01017"];
h["WhCodeTo"] = item["RW01008"];
h["BinCodeTo"] = item["RW01009"];
h["Company"] = item["RW01034"];
h["SupplierBatch"] = item["RW01035"];
list.Add(h);
}
StockTransactionXml.ExportMoveXML(list);
// string company = ds.Tables[0].Rows[0]["RW01034"] + "";
// StringBuilder export = new StringBuilder();
// export.Append(@"
//<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
// <msg:Hdr>
// <msg:Sender>
// <msg:Name>Generic Sender</msg:Name>
// <msg:Subname>[COM]-Scala</msg:Subname>
// </msg:Sender>
// </msg:Hdr>
// <msg:Body>
// <msg:Req msg-type=""Stock Transaction"" action=""Process"">
// <msg:Dta>
// <dta:StockTransaction xsi:schemaLocation=""http://www.scala.net/StockTransaction/1.1 http://scshost/schemas/Scala/1.1/StockTransaction.xsd"" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/StockTransaction/1.1"">");
// export.Replace("[COM]", company);
// string transTemp = @"
// <dta:Movement>
// <dta:TransDate>{0}</dta:TransDate>
// <dta:Qty>{1}</dta:Qty>
// <dta:StockCode>{2}</dta:StockCode>
// <dta:WhCodeFrom>{3}</dta:WhCodeFrom>
// <dta:BinCodeTo>{4}</dta:BinCodeTo>
// <dta:WhCodeTo>{5}</dta:WhCodeTo>
// <dta:BinCodeFrom>{6}</dta:BinCodeFrom>
// <dta:BatchCode BatchHandlingMethod=""1"">{7}</dta:BatchCode>
// </dta:Movement>";
// foreach (DataRow item in ds.Tables[0].Rows)
// {
// export.Append(string.Format(transTemp, DateTime.Now.ToString("yyyy-MM-dd"), item["RW01006"], item["RW01002"], item["RW01016"], item["RW01009"], item["RW01008"], item["RW01017"], item["RW01007"]));
// }
// export.Append(@"
// </dta:StockTransaction>
// </msg:Dta>
// </msg:Req>
// </msg:Body>
//</msg:Msg>
//");
// string[] path = SysConfig.GetXMLExportPath(company);
// string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
// System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
// System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
}
private void ExportXML(string bids)
{
string sql = @"
select RW01016,RW01017,RW01008,RW01009,RW01002,SUM(RW01006/RW01040) RW01006,RW01007,RW01034,RW01035 from RW01 where RW01001 IN ([BIDS])
group by RW01016,RW01017,RW01008,RW01009,RW01002,RW01007,RW01034,RW01035
";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
List<Hashtable> list = new List<Hashtable>();
foreach (DataRow item in ds.Tables[0].Rows)
{
Hashtable h = new Hashtable();
h["Qty"] = item["RW01006"];
h["StockCode"] = item["RW01002"];
h["Warehouse"] = item["RW01008"];
h["Bin"] = item["RW01009"];
h["Company"] = item["RW01034"];
h["SupplierBatch"] = item["RW01035"];
list.Add(h);
}
StockTransactionXml.ExportStockReceiptXML(list);
}
}
public override BaseSearchModel Insert()
{
using (TransactionScope ts = new TransactionScope())
{
if (PrintCount == 0)
{
throw new Exception("打印数量不能为空！");
}
string sqlStr = @"
declare @bid varchar(30)
declare @hid int
declare @i int
declare @dj varchar(30)
exec PROC_GETID 'RW03',@dj output
insert into RW03(RW03001,RW03002,RW03003,RW03004,RW03005,RW03006,RW03007,RW03008)
values(@dj,getdate(),@userid,@username,'','','',N'创建条码')
set @i=0
while @i<@printcount
begin
exec PROC_GETID 'RW01',@bid output
insert into RW02(RW02002,RW02003,RW02004,RW02005,RW02010,RW02011)
values(@bid,getdate(),@userid,@username,@dj,N'创建条码标签')
insert into RW01(RW01001,RW01002,RW01003,RW01004,RW01005,RW01006,RW01012,RW01013,RW01014,RW01015,RW01018,RW01024,RW01025,RW01027,RW01032,RW01034,RW01035,RW01037,RW01038,RW01040,RW01041,RW01043)
values(@bid,@stock,@stockname,@stockspec,@unit,@qty,@sup,@supname,@po,@poline,@remark,@userid,@username,@dj,1,@company,@SupplierBatch,@ProductionTime ,@ValidityTime,@tqty,@type,'REC')
insert into RW04(RW04002,RW04003,RW04004,RW04005) values(getdate(),@dj,@bid,N'创建条码')
set @i=@i+1
end
select top [COUNT] RW01001 from RW01 order by RW01001 desc
";
sqlStr = sqlStr.Replace("[COUNT]", this.PrintCount + "");
ModelAdo<StockModel> adoStock = new ModelAdo<StockModel>();
StockModel sm = adoStock.GetModelByID(this.StockCode);
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sqlStr, new SqlParameter("@userid", this.LoginUserID),
new SqlParameter("@username", this.LoginUserName),
new SqlParameter("@stock", this.StockCode),
new SqlParameter("@stockname", sm.StockName),
new SqlParameter("@stockspec", sm.StockSpec),
new SqlParameter("@unit", this.StockUnit),
new SqlParameter("@qty", this.StockUnitQty),
new SqlParameter("@tqty", sm.StockType == "02" ? 1 : sm.UQTY),
new SqlParameter("@po", this.PO),
new SqlParameter("@poline", this.POLine),
new SqlParameter("@sup", this.SupplierCode),
new SqlParameter("@supname", this.SupplierName),
new SqlParameter("@remark", this.Remark1),
new SqlParameter("@company", this.CompanyCode),
new SqlParameter("@SupplierBatch", this.SupplierBatch),
new SqlParameter("@printcount", this.PrintCount),
new SqlParameter("@type", sm.StockType),
new SqlParameter("@ProductionTime", this.ProductDate),
new SqlParameter("@ValidityTime", this.ExpirationDate)
);
//StringBuilder sb = new StringBuilder();
//foreach (DataRow item in ds.Tables[0].Rows)
//{
// sb.Append(",'" + item[0] + "'");
//}
//sb.Remove(0, 1);
//ExportXML(sb.ToString());
ts.Complete();
}
return this;
}
private string GetLineNO(string po, string stock, string company)
{
BaseAdo ba = new BaseAdo();
ba.ConnectionStr = SysConfig.DBScalaConnection;
string sql = "select PC03002 from PC03[COM]00 where PC03001=@po and PC03005=@stock";
DataSet ds = ba.GetDataSet(sql.Replace("[COM]", company), new SqlParameter("@po", po), new SqlParameter("@stock", stock));
if (ds.Tables[0].Rows.Count > 0)
return ds.Tables[0].Rows[0][0] + "";
else
return "";
}
private void ExportXMLPO(string bids)
{
string sql = @"
select RW01014,RW01015,RW01002,SUM(RW01006) RW01006,RW01035,RW01008,RW01009,convert(varchar(10),RW01037,121) RW01037,convert(varchar(10),RW01038,121) RW01038,RW01034,RW01043,RW01033 from RW01 where RW01001 IN ([BIDS])
group by RW01014,RW01015,RW01002,RW01035,RW01037,RW01038,RW01034,RW01008,RW01009,RW01043,RW01033
order by RW01014,RW01015,RW01035
select SUM(RW01006) RW01006 FROM RW01 where RW01001 IN ([BIDS]) ";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
string company = ds.Tables[0].Rows[0]["RW01034"] + "";
string receivetype = ds.Tables[0].Rows[0]["RW01043"] + "";
if (receivetype == "WO")//WO收货入库
{
string ordernum = ds.Tables[0].Rows[0]["RW01033"] + "";
StringBuilder export = new StringBuilder();
export.Append(@"
<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
<msg:Hdr>
<msg:Sender>
<msg:Name>Generic Sender</msg:Name>
<msg:Subname>[COM]-Scala</msg:Subname>
</msg:Sender>
</msg:Hdr>
<msg:Body>
<msg:Req msg-type=""Work Order Receipt"" action=""Process"">
<msg:Dta>
<dta:WorkOrderReceiptRequest xsi:schemaLocation=""http://www.scala.net/WorkOrderReceiptRequest/1.1 http://scshost/schemas/Scala/1.1/WorkOrderReceiptRequest.xsd "" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/WorkOrderReceiptRequest/1.1"">
<dta:WorkorderNumber>[WO]</dta:WorkorderNumber>
<dta:ReceiptDate>[DATE]</dta:ReceiptDate>
<dta:ReceiptWarehouse>[WH]</dta:ReceiptWarehouse>
<dta:ReceiptQuantity>[SUMQTY]</dta:ReceiptQuantity>
<dta:CloseWorkorder>1</dta:CloseWorkorder>
<dta:ReceiptLines>");
export.Replace("[COM]", company);
export.Replace("[WO]", ordernum);
export.Replace("[WH]", ds.Tables[0].Rows[0]["RW01008"] + "");
export.Replace("[SUMQTY]", ds.Tables[1].Rows[0]["RW01006"] + "");
export.Replace("[DATE]", DateTime.Now.ToString("yyyy-MM-dd"));
string receTemp = @"
<dta:ReceiptLine>
<dta:Batch>
<dta:BatchCode>{0}</dta:BatchCode>
</dta:Batch>
<dta:ReceiptWarehouse>{1}</dta:ReceiptWarehouse>
<dta:ReceiptDate>{2}</dta:ReceiptDate>
<dta:ReceiptQuantity>{3}</dta:ReceiptQuantity>
<dta:BinCode>{4}</dta:BinCode>
<dta:ManufDate>{5}</dta:ManufDate>
<dta:ExpiryDate>{6}</dta:ExpiryDate>
<dta:SuppBatchCode>{0}</dta:SuppBatchCode>
</dta:ReceiptLine>";
int i = 0;
foreach (DataRow item in ds.Tables[0].Rows)
{
export.Append(string.Format(receTemp, item["RW01035"], item["RW01008"], DateTime.Now.ToString("yyyy-MM-dd"), item["RW01006"], item["RW01009"], item["RW01037"], item["RW01038"]));
}
export.Append(@"
</dta:ReceiptLines>
</dta:WorkOrderReceiptRequest>
</msg:Dta>
</msg:Req>
</msg:Body>
</msg:Msg>
");
string[] path = SysConfig.GetXMLExportPath(company);
string filename = "WORECEIVE" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
else if (receivetype == "REC")//Receive收货
{
List<Hashtable> list = new List<Hashtable>();
foreach (DataRow item in ds.Tables[0].Rows)
{
Hashtable h = new Hashtable();
h["Qty"] = item["RW01006"];
h["StockCode"] = item["RW01002"];
h["Warehouse"] = item["RW01008"];
h["Bin"] = item["RW01009"];
h["Company"] = item["RW01034"];
h["SupplierBatch"] = item["RW01035"];
h["FRNUMBER"] = this.FRNumber;
list.Add(h);
}
StockTransactionXml.ExportStockReceiptXML(list);
}
else if (receivetype == "PO")//PO收货
{
string ordernum = ds.Tables[0].Rows[0]["RW01014"] + "";
StringBuilder export = new StringBuilder();
export.Append(@"
<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
<msg:Hdr>
<msg:Sender>
<msg:Name>Generic Sender</msg:Name>
<msg:Subname>[COM]-Scala</msg:Subname>
</msg:Sender>
</msg:Hdr>
<msg:Body>
<msg:Req msg-type=""Goods Receive Note"" action=""Process"">
<msg:Dta>
<dta:PurchaseOrderDelivery xsi:schemaLocation=""http://www.scala.net/PurchaseOrderDelivery/1.1 http://scshost/schemas/Scala/1.1/PurchaseOrderDelivery.xsd"" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/PurchaseOrderDelivery/1.1"">
<dta:OrderHeader>
<dta:OrdNum>[ORDERNO]</dta:OrdNum>
<dta:GoodsReceivedNoteNum>[NOTENUM]</dta:GoodsReceivedNoteNum>
</dta:OrderHeader>
<dta:OrderLineList>");
export.Replace("[COM]", company);
export.Replace("[ORDERNO]", ordernum);
export.Replace("[NOTENUM]", "");
string lineno = "";
string lineBegin = @"
<dta:OrderLine>
<dta:LineNum>{0}</dta:LineNum>
<dta:StructLineNum/>
<dta:StockCode>{1}</dta:StockCode>
<dta:DelivLineList>";
string delLine = @"
<dta:DelivLine>
<dta:DelivLineNum>{0}</dta:DelivLineNum>
<dta:QtyDeliv>{1}</dta:QtyDeliv>
<dta:BatchCode></dta:BatchCode>
<dta:SuppBatchCode>{2}</dta:SuppBatchCode>
<dta:DelivDateAct>{3}</dta:DelivDateAct>
<dta:ManufDate>{4}</dta:ManufDate>
<dta:ExpiryDate>{5}</dta:ExpiryDate>
<dta:BinCode>{6}</dta:BinCode>
</dta:DelivLine>";
int i = 0;
foreach (DataRow item in ds.Tables[0].Rows)
{
string line = item["RW01015"] + "";
if (line.Length == 0)
line = this.GetLineNO(ordernum, item["RW01002"] + "", company);
if (lineno != line)
{
if (i != 0)//行结束
{
export.Append(@"
</dta:DelivLineList>
</dta:OrderLine>");
}
i = 0;
export.Append(string.Format(lineBegin, line, item["RW01002"]));
}
i = i + 10;
export.Append(string.Format(delLine, "", item["RW01006"], item["RW01035"], DateTime.Now.ToString("yyyy-MM-dd"), item["RW01037"], item["RW01038"], item["RW01009"]));
}
export.Append(@"</dta:DelivLineList>
</dta:OrderLine>
</dta:OrderLineList>
</dta:PurchaseOrderDelivery>
</msg:Dta>
</msg:Req>
</msg:Body>
</msg:Msg>
");
string[] path = SysConfig.GetXMLExportPath(company);
string filename = "PORECEV" + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
}
}
public override BaseSearchModel Insert()
{
if (SOLines == null || SOLines.Length == 0)
throw new Exception("请选择发货单项");
string sql = @"
declare @id varchar(30)
exec PROC_GETID 'FG08',@id output
insert into FG08(FG08001,FG08002,FG08003,FG08004,FG08005,FG08006,FG08007,FG08008,FG08011,FG08012)
values(@id,getdate(),@userid,@username,@HopeTime,@isUrgent,@Remark,1,@CustomerCode,@CustomerName)
";
List<SqlParameter> list = new List<SqlParameter>();
list.Add(new SqlParameter("@userid", this.LoginUserID));
list.Add(new SqlParameter("@username", this.LoginUserName));
list.Add(new SqlParameter("@HopeTime", this.HopeTime));
list.Add(new SqlParameter("@isUrgent", this.isUrgent));
list.Add(new SqlParameter("@Remark", this.Remark));
list.Add(new SqlParameter("@CustomerCode", SOLines[0].CustomerCode));
list.Add(new SqlParameter("@CustomerName", SOLines[0].CustomerName));
StringBuilder sb = new StringBuilder();
sb.Append(sql);
int i = 10;
foreach (SOLineModel item in this.SOLines)
{
sb.Append(string.Format(@"insert into FG09(FG09002,FG09003,FG09004,FG09005,FG09006,FG09007,FG09008,FG09009,FG09010,FG09011,FG09012)
values(@line{0},@id,getdate(),@userid,@username,@stock{0},@stockname{0},@orderqty{0},@unit{0},0,@orderqty{0})
", i));
list.Add(new SqlParameter("@line" + i, item.LineNumber));
list.Add(new SqlParameter("@stock" + i, item.StockCode));
list.Add(new SqlParameter("@stockname" + i, item.StockName));
list.Add(new SqlParameter("@orderqty" + i, item.OrderQty));
list.Add(new SqlParameter("@unit" + i, item.Unit));
i = i + 10;
}
sb.Append("select @id SONO");
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sb.ToString(), list.ToArray());
if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count >= 1)
this.SONO = ds.Tables[0].Rows[0][0] + "";
return this;
}
private void ExportXML(string bids)
{
string sql = @"
select RW01030,RW01039,RW01002,RW01007,RW01008,RW01009,sum(RW01006) RW01006,RW01034,RW01035 from RW01 where RW01001 IN (SELECT RW20007 FROM RW20 INNER JOIN RW19 ON RW19001=RW20006 WHERE RW20007 IN ([BIDS]) and RW19013 NOT LIKE 'CW%')
GROUP BY RW01030,RW01039,RW01002,RW01007,RW01034,RW01035,RW01008,RW01009";
BaseAdo ba = new BaseAdo();
DataSet ds = ba.GetDataSet(sql.Replace("[BIDS]", bids));
if (ds.Tables[0].Rows.Count > 0)
{
string company = ds.Tables[0].Rows[0]["RW01034"] + "";
string ordernum = ds.Tables[0].Rows[0]["RW01030"] + "";
StringBuilder export = new StringBuilder();
export.Append(@"
<msg:Msg xsi:schemaLocation=""http://Epicor.com/Message/2.0 http://scshost/schemas/epicor/ScalaMessage.xsd"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:msg=""http://Epicor.com/Message/2.0"">
<msg:Hdr>
<msg:Sender>
<msg:Name>Generic Sender</msg:Name>
<msg:Subname>[COM]-Scala</msg:Subname>
</msg:Sender>
</msg:Hdr>
<msg:Body>
<msg:Req msg-type=""Sales Order Delivery"" action=""Process"">
<msg:Dta>
<dta:SalesOrderDelivery xsi:schemaLocation=""http://www.scala.net/SalesOrderDelivery/1.1 http://scshost/schemas/Scala/1.1/SalesOrderDelivery.xsd"" xmlns:msg=""http://Epicor.com/InternalMessage/1.1"" xmlns:dta=""http://www.scala.net/SalesOrderDelivery/1.1"">
<dta:OrderHeader>
<dta:OrdNum>[SO]</dta:OrdNum>
</dta:OrderHeader>
<dta:OrderLineList>");
export.Replace("[COM]", company);
export.Replace("[SO]", ordernum);
string delLine = @"
<dta:OrderLine>
<dta:LineNum>{0}</dta:LineNum>
<dta:StockCode>{1}</dta:StockCode>
<dta:DelivLineList>
<dta:DelivLine>
<dta:DelivLineNum></dta:DelivLineNum>
<dta:QtyDeliv>{2}</dta:QtyDeliv>
<dta:BatchCode>{4}</dta:BatchCode>
<dta:DelivDateAct>{3}</dta:DelivDateAct>
</dta:DelivLine>
</dta:DelivLineList>
</dta:OrderLine>";
foreach (DataRow item in ds.Tables[0].Rows)
{
string stock = item["RW01002"] + "";
string warehouse = item["RW01008"] + "";
string bin = item["RW01009"] + "";
string SupplierBatch = item["SupplierBatch"] + "";
double qty = double.Parse(item["Qty"] + "");
List<KeyValuePair<string, double>> list = StockTransactionXml.GetScalaBatch(stock, warehouse, bin, SupplierBatch, qty, company);
foreach (KeyValuePair<string, double> batch in list)
{
export.Append(string.Format(delLine, item["RW01039"], stock, batch.Value, DateTime.Now.ToString("yyyy-MM-dd"), batch.Key));
}
}
export.Append(@"
</dta:OrderLineList>
</dta:SalesOrderDelivery>
</msg:Dta>
</msg:Req>
</msg:Body>
</msg:Msg>
");
string[] path = SysConfig.GetXMLExportPath(company);
string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999) + ".xml";
System.IO.File.WriteAllText(path[0] + filename, export.ToString(), Encoding.Unicode);
System.IO.File.WriteAllText(path[1] + "bak" + filename, export.ToString(), Encoding.Unicode);
}
}