1. 第一步 在k3数据库端成两个视图 , 采购入库单(TicketViewBuy)、 销售出库单(TicketViewSale)
//采购入库单视图
USE [K3数据库]
GO
/****** 对象: View [dbo].[TicketViewBuy] 脚本日期: 07/16/2018 18:41:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER View [dbo].[TicketViewBuy] As
Select * From ( Select v1.FInterID AS [FInterID],
u1.FEntryID AS [FEntryID],
t3.FName AS [FSupplyID],
v1.Fdate AS [Fdate],
v1.FBillNo AS [FBillNo],
case when v1.FCheckerID>0 then '※' when v1.FCheckerID<0 then '※' else '' end AS [FCheckFlag],
case when v1.FStatus=3 then '※' else '' end AS [FStatusEx],
u1.FMapNumber AS [FMapNumber],
u1.FMapName AS [FMapName],
t14.FShortNumber AS [FShortNumber],
t14.Fname AS [FItemID],
t14.Fmodel AS [Fmodel],
t17.FName AS [FUnitID],
t8.FName AS [FCurrencyID],
v1.FExchangeRate AS [FExchangeRate],
t9.FName AS [FMangerID],
t10.FName AS [FDeptID],
t11.FName AS [FEmpID],
t12.FName AS [FBillerID],
u1.FSourceTranType AS [FSourceTranType],
u1.FSourceBillNo AS [FSourceBillNo],
u1.FAuxQty AS [FAuxQty],
u1.Fdate AS [FCommitdate],
u1.Fauxprice AS [Fauxprice],
u1.FTaxRate AS [FTaxRate],
u1.FCess AS [FCess],
u1.FAmount AS [FAmount],
u1.FNote AS [FNote],
t13.FName AS [FCheckerID],
t18.FName AS [FUnitGroupID],
u1.FQty AS [FQty],
case when v1.FCancellation=1 then '※' else '' end AS [FCancellation],
t20.FName AS [FRelateBrID],
t14.FNumber AS [FNumber],
t5.FName AS [FPOStyle],
u1.FCommitQty AS [FCommitQty],
u1.FAuxCommitQty AS [FAuxCommitQty],
u1.FStockQty AS [FStockQty],
u1.FAuxStockQty AS [FAuxStockQty],
Case WHEN t14.FCUUnitID=0 THEN '' Else t500.FName end AS [FCUUnitID],
Case WHEN t14.FCUUnitID=0 THEN 0 Else u1.FQty/t500.FCoefficient end AS [FcuQty],
v1.FBrNo as 公司内码ID,
v1.FBillerID as 制单人ID,
v1.FTranType as 单据类型ID,
v1.FDeptID as 部门ID,
v1.FMangerID as 主管ID,
v1.FEmpID as 业务员ID,
v1.FSupplyID as 供应商ID,
v1.FCheckerID as 审核人ID,
v1.FPOStyle as 采购方式ID,
u1.FItemID as 货名ID,
u1.FUnitID as 单位ID,
v1.FStatus as 状态,
round(u1.FAuxQty-u1.FCommitQty,3) as 剩余量
from POOrder v1 (NOLOCK) Left Join POOrderEntry u1 (NOLOCK) on v1.FInterID=u1.FInterID
left outer join t_Supplier t3 (NOLOCK) on v1.FSupplyID=t3.FItemID
left outer join t_SubMessage t5 (NOLOCK) on v1.FPOStyle=t5.FInterID
Left Join t_Currency t8 (NOLOCK) on v1.FCurrencyID=t8.FCurrencyID
left outer join t_Emp t9 (NOLOCK) on v1.FMangerID=t9.FItemID
left outer join t_Department t10 (NOLOCK) on v1.FDeptID=t10.FItemID
left outer join t_Emp t11 (NOLOCK) on v1.FEmpID=t11.FItemID
left outer join t_User t12 (NOLOCK) on v1.FBillerID=t12.FUserID
left outer join t_User t13 (NOLOCK) on v1.FCheckerID=t13.FUserID
Left Join t_ICItem t14 (NOLOCK) on u1.FItemID=t14.FItemID
Left Join t_MeasureUnit t17 (NOLOCK) on u1.FUnitID=t17.FItemID
Left Join t_MeasureUnit t18 (NOLOCK) on t14.FUnitGroupID=t18.FUnitGroupID
left outer join t_SonCompany t20 (NOLOCK) on v1.FRelateBrID=t20.FItemID
left outer join t_MeasureUnit t500 (NOLOCK) on t14.FCUUnitID=t500.FItemID
left outer join t_Currency t503 (NOLOCK) on v1.FCurrencyID=t503.FCurrencyID
where t18.FStandard=1
) As [TicketViewBuy]
//销售出库单视图
USE [K3数据库]
GO
/****** 对象: View [dbo].[TicketViewSale] 脚本日期: 07/16/2018 18:45:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[TicketViewSale]
AS
SELECT FInterID, FEntryID, FCheckFlag, FClosed, Fdate, FCustID, FSaleStyle, FFetchStyle, FBillNo, FCurrencyID, FFetchAdd, FMangerID, FDeptID, FEmpID, FBillerID, FSettleID,
FExchangeRate, FShortNumber, FItemID, Fmodel, FUnitID, FSourceTranType, FSourceBillNo, Fauxqty, Fauxprice, Famount, FTaxRate, FUniDiscount, FTaxAmount,
FFinalAmount, FUnitGroupID, FQty, FLockFlag, FCheckerID, FCommitDate, FNote, FNumber, FCancellation, FCess, FTransitAheadTime, FAdviceConsignDate,
FMapNumber, FMapName, FBomInterID, FRelateBrID, FPOOrdBillNo, FStockQty, FCommitQty, FATPDeduct, FCUUnitID, FcuQty, FauxStockQty, FauxCommitQty,
FCostObjectID, 源单类型ID, 单据类型ID,制单人ID, 公司内码ID, 购货单位ID,部门ID,审核人ID,
业务员ID,主管ID, 销售方式ID,单位ID,计划模式ID,产品ID,剩余量
FROM (SELECT v1.FInterID, u1.FEntryID, CASE WHEN v1.FCheckerID > 0 THEN '※' WHEN v1.FCheckerID < 0 THEN '※' ELSE '' END AS FCheckFlag,
CASE WHEN v1.FStatus = 3 THEN '1' ELSE '' END AS FClosed, v1.FDate AS Fdate, t4.FName AS FCustID, t5.FName AS FSaleStyle,
t7.FName AS FFetchStyle, v1.FBillNo, t8.FName AS FCurrencyID, v1.FFetchAdd, t10.FName AS FMangerID, t11.FName AS FDeptID, t12.FName AS FEmpID,
t13.FName AS FBillerID, t14.FName AS FSettleID, v1.FExchangeRate, t16.FShortNumber, t16.FName AS FItemID, t16.FModel AS Fmodel,
t19.FName AS FUnitID, u1.FSourceTranType, u1.FSourceBillNo, u1.FAuxQty AS Fauxqty, u1.FAuxPrice AS Fauxprice, u1.FAmount AS Famount, u1.FTaxRate,
u1.FUniDiscount, u1.FTaxAmount, u1.FFinalAmount, t25.FName AS FUnitGroupID, u1.FQty,
CASE WHEN u1.FLockFlag = 1 THEN '※' ELSE '' END AS FLockFlag, t24.FName AS FCheckerID, u1.FDate AS FCommitDate, u1.FNote, t16.FNumber,
CASE WHEN v1.FCancellation = 1 THEN '※' ELSE '' END AS FCancellation, u1.FCESS AS FCess, v1.FTransitAheadTime, u1.FAdviceConsignDate,
u1.FMapNumber, u1.FMapName,
(SELECT FBOMNumber
FROM dbo.ICBOM WITH (NOLOCK)
WHERE (FInterID = u1.FBomInterID)) AS FBomInterID, t65.FName AS FRelateBrID, v1.FPOOrdBillNo, u1.FStockQty, u1.FCommitQty,
CASE WHEN u1.FATPDeduct = 1 THEN '※' ELSE '' END AS FATPDeduct, CASE WHEN t16.FCUUnitID = 0 THEN '' ELSE t500.FName END AS FCUUnitID,
CASE WHEN t16.FCUUnitID = 0 THEN u1.FQty ELSE u1.FQty / t500.FCoefficient END AS FcuQty,
CASE WHEN t16.FCUUnitID = 0 THEN u1.FStockQty ELSE u1.FStockQty / t500.FCoefficient END AS FauxStockQty,
CASE WHEN t16.FCUUnitID = 0 THEN u1.FCommitQty ELSE u1.FCommitQty / t500.FCoefficient END AS FauxCommitQty, t21.FName AS FCostObjectID,
v1.FSelTranType as 源单类型ID,
v1.FTranType as 单据类型ID,
v1.FBillerID as 制单人ID,
v1.FBrNo as 公司内码ID,
v1.FCustID as 购货单位ID,
v1.FEmpID as 业务员ID,
v1.FDeptID as 部门ID,
v1.FCheckerID as 审核人ID,
v1.FMangerID as 主管ID,
v1.FSaleStyle as 销售方式ID,
u1.FUnitID as 单位ID,
u1.FPlanMode as 计划模式ID,
u1.FItemID as 产品ID,
round(u1.fQty - u1.fCommitQty,3) as 剩余量
FROM
dbo.SEOrder AS v1 WITH (NOLOCK) LEFT OUTER JOIN
dbo.SEOrderEntry AS u1 WITH (NOLOCK) ON v1.FInterID = u1.FInterID LEFT OUTER JOIN
dbo.t_Organization AS t4 WITH (NOLOCK) ON v1.FCustID = t4.FItemID LEFT OUTER JOIN
dbo.t_SubMessage AS t5 WITH (NOLOCK) ON v1.FSaleStyle = t5.FInterID LEFT OUTER JOIN
dbo.t_SubMessage AS t7 WITH (NOLOCK) ON v1.FFetchStyle = t7.FInterID LEFT OUTER JOIN
dbo.t_Currency AS t8 WITH (NOLOCK) ON v1.FCurrencyID = t8.FCurrencyID LEFT OUTER JOIN
dbo.t_Emp AS t10 WITH (NOLOCK) ON v1.FMangerID = t10.FItemID LEFT OUTER JOIN
dbo.t_Department AS t11 WITH (NOLOCK) ON v1.FDeptID = t11.FItemID LEFT OUTER JOIN
dbo.t_Emp AS t12 WITH (NOLOCK) ON v1.FEmpID = t12.FItemID LEFT OUTER JOIN
dbo.t_User AS t13 WITH (NOLOCK) ON v1.FBillerID = t13.FUserID LEFT OUTER JOIN
dbo.t_Settle AS t14 WITH (NOLOCK) ON v1.FSettleID = t14.FItemID LEFT OUTER JOIN
dbo.t_ICItem AS t16 WITH (NOLOCK) ON u1.FItemID = t16.FItemID LEFT OUTER JOIN
dbo.t_MeasureUnit AS t19 WITH (NOLOCK) ON u1.FUnitID = t19.FItemID LEFT OUTER JOIN
dbo.t_MeasureUnit AS t25 WITH (NOLOCK) ON t16.FUnitGroupID = t25.FUnitGroupID LEFT OUTER JOIN
dbo.t_User AS t24 WITH (NOLOCK) ON v1.FCheckerID = t24.FUserID LEFT OUTER JOIN
dbo.cbCostObj AS t21 WITH (NOLOCK) ON u1.FCostObjectID = t21.FItemID LEFT OUTER JOIN
dbo.t_SonCompany AS t65 WITH (NOLOCK) ON v1.FRelateBrID = t65.FItemID LEFT OUTER JOIN
dbo.t_MeasureUnit AS t500 WITH (NOLOCK) ON t16.FCUUnitID = t500.FItemID LEFT OUTER JOIN
dbo.t_Currency AS t504 WITH (NOLOCK) ON v1.FCurrencyID = t504.FCurrencyID ---LEFT OUTER JOIN
WHERE (t25.FStandard = 1)) AS TicketViewSale
2.第二步 在本地生成 两个表 用来存储订单信息,分别对应 采购入库单和销售出库单,表明分别是: TicketBuy、TicketSale
//存放采购订单信息
USE [本地客户端数据库]
GO
/****** 对象: Table [dbo].[TicketBuy] 脚本日期: 07/16/2018 18:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TicketBuy](
[FInterID] [int] NOT NULL,
[FEntryID] [int] NULL,
[FSupplyID] [varchar](80) NULL,
[Fdate] [nvarchar](50) NULL,
[FBillNo] [nvarchar](255) NOT NULL,
[FCheckFlag] [varchar](2) NOT NULL,
[FStatusEx] [varchar](2) NOT NULL,
[FMapNumber] [varchar](80) NULL,
[FMapName] [nvarchar](256) NULL,
[FShortNumber] [varchar](80) NULL,
[FItemID] [varchar](255) NULL,
[Fmodel] [varchar](255) NULL,
[FUnitID] [varchar](80) NULL,
[FCurrencyID] [varchar](40) NULL,
[FExchangeRate] [float] NULL,
[FMangerID] [varchar](255) NULL,
[FDeptID] [varchar](80) NULL,
[FEmpID] [varchar](255) NULL,
[FBillerID] [nvarchar](50) NULL,
[FSourceTranType] [int] NULL,
[FSourceBillNo] [nvarchar](255) NULL,
[FAuxQty] [decimal](28, 10) NULL,
[FCommitdate] [nvarchar](50) NULL,
[Fauxprice] [decimal](28, 10) NULL,
[FTaxRate] [decimal](28, 10) NULL,
[FCess] [decimal](20, 2) NULL,
[FAmount] [decimal](20, 2) NULL,
[FNote] [varchar](1024) NULL,
[FCheckerID] [nvarchar](50) NULL,
[FUnitGroupID] [varchar](80) NULL,
[FQty] [decimal](28, 10) NULL,
[FCancellation] [varchar](2) NOT NULL,
[FRelateBrID] [varchar](80) NULL,
[FNumber] [varchar](80) NULL,
[FPOStyle] [varchar](80) NULL,
[FCommitQty] [decimal](28, 10) NULL,
[FAuxCommitQty] [decimal](28, 10) NULL,
[FStockQty] [decimal](28, 10) NULL,
[FAuxStockQty] [decimal](28, 10) NULL,
[FCUUnitID] [varchar](80) NULL,
[FcuQty] [decimal](38, 10) NULL,
[公司内码ID] [nvarchar](50) NULL,
[制单人ID] [int] NULL,
[单据类型ID] [int] NOT NULL,
[部门ID] [int] NULL,
[主管ID] [int] NULL,
[业务员ID] [int] NULL,
[供应商ID] [int] NULL,
[审核人ID] [int] NULL,
[采购方式ID] [int] NULL,
[货名ID] [int] NULL,
[单位ID] [int] NULL,
[状态] [nvarchar](50) NULL,
[剩余量] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
//存放销售订单信息
USE [本地客户端数据库]
GO
/****** 对象: Table [dbo].[TicketSale] 脚本日期: 07/16/2018 18:53:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TicketSale](
[FInterID] [int] NOT NULL,
[FEntryID] [int] NULL,
[FCheckFlag] [varchar](2) NOT NULL,
[FClosed] [varchar](1) NOT NULL,
[Fdate] [nvarchar](50) NULL,
[FCustID] [varchar](80) NULL,
[FSaleStyle] [varchar](80) NULL,
[FFetchStyle] [varchar](80) NULL,
[FBillNo] [nvarchar](255) NOT NULL,
[FCurrencyID] [varchar](40) NULL,
[FFetchAdd] [varchar](255) NULL,
[FMangerID] [varchar](255) NULL,
[FDeptID] [varchar](80) NULL,
[FEmpID] [varchar](255) NULL,
[FBillerID] [nvarchar](50) NULL,
[FSettleID] [varchar](80) NULL,
[FExchangeRate] [float] NULL,
[FShortNumber] [varchar](80) NULL,
[FItemID] [varchar](255) NULL,
[Fmodel] [varchar](255) NULL,
[FUnitID] [varchar](80) NULL,
[FSourceTranType] [int] NULL,
[FSourceBillNo] [nvarchar](255) NULL,
[Fauxqty] [decimal](28, 10) NULL,
[Fauxprice] [decimal](28, 10) NULL,
[Famount] [decimal](20, 2) NULL,
[FTaxRate] [decimal](28, 10) NULL,
[FUniDiscount] [decimal](28, 10) NULL,
[FTaxAmount] [decimal](20, 2) NULL,
[FFinalAmount] [decimal](20, 2) NULL,
[FUnitGroupID] [varchar](80) NULL,
[FQty] [decimal](28, 10) NULL,
[FLockFlag] [varchar](2) NOT NULL,
[FCheckerID] [nvarchar](50) NULL,
[FCommitDate] [nvarchar](50) NULL,
[FNote] [varchar](1024) NULL,
[FNumber] [varchar](80) NULL,
[FCancellation] [varchar](2) NOT NULL,
[FCess] [decimal](20, 2) NULL,
[FTransitAheadTime] [real] NULL,
[FAdviceConsignDate] [nvarchar](50) NULL,
[FMapNumber] [varchar](80) NULL,
[FMapName] [nvarchar](256) NULL,
[FBomInterID] [varchar](300) NULL,
[FRelateBrID] [varchar](80) NULL,
[FPOOrdBillNo] [nvarchar](510) NULL,
[FStockQty] [decimal](28, 10) NULL,
[FCommitQty] [decimal](28, 10) NULL,
[FATPDeduct] [varchar](2) NOT NULL,
[FCUUnitID] [varchar](80) NULL,
[FcuQty] [decimal](38, 10) NULL,
[FauxStockQty] [decimal](38, 10) NULL,
[FauxCommitQty] [decimal](38, 10) NULL,
[FCostObjectID] [varchar](255) NULL,
[源单类型ID] [int] NOT NULL,
[单据类型ID] [int] NOT NULL,
[制单人ID] [int] NULL,
[公司内码ID] [varchar](10) NOT NULL,
[购货单位ID] [int] NULL,
[部门ID] [int] NULL,
[业务员ID] [int] NULL,
[主管ID] [int] NULL,
[销售方式ID] [int] NULL,
[单位ID] [int] NULL,
[计划模式ID] [int] NULL,
[产品ID] [int] NULL,
[剩余量] [decimal](29, 10) NULL,
[审核人ID] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
3.C# webService 调用k3,下载订单信息和上传出入库信息。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
/// <summary>
///WebService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
//若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务,请取消对下行的注释。
// [System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
public Dictionary<string, string> mSaverFiled = new Dictionary<string, string>();
public WebService()
{
//,如果使用设计的组件请取消注释以下行
//InitializeComponent();
DataBaseHelper.getConnStr();
}
[WebMethod]
public string HelloWorld()
{
return "Hello World";
}
[WebMethod]
public string restart()
{
DataBaseHelper.mConnString = string.Empty;
DataBaseHelper.getConnStr();
return "重新设置成功!";
}
/// <summary>
/// 销售订单
/// </summary>
/// <returns></returns>
[WebMethod]
public string getSaleTicket()
{
string sql = "select * from TicketViewSale ";
DataTable dt = DataBaseHelper.getTable(sql);
dt.TableName = "SaleTicket";
StringWriter strWriter = new StringWriter();
dt.WriteXml(strWriter, false);
string strXml = strWriter.ToString();
strWriter.Close();
return strXml;
}
/// <summary>
/// 采购订单
/// </summary>
/// <returns></returns>
[WebMethod]
public string getBuyTicket()
{
string sql = "select * from TicketViewBuy";
DataTable dt = DataBaseHelper.getTable(sql);
dt.TableName = "BuyTicket";
StringWriter strWriter = new StringWriter();
dt.WriteXml(strWriter, false);
string strXml = strWriter.ToString();
return strXml;
}
/// <summary>
/// 下载职员表
/// </summary>
/// <returns></returns>
[WebMethod]
public string getElem()
{
try
{
string sql = " select * from t_Emp where FDeleted='0' ";
DataTable dt = DataBaseHelper.getTable(sql);
dt.TableName = "elemployee";
string xmlStr = new DataTableToXmlHelper().dataTable2XmlStr(dt);
return xmlStr;
}
catch (Exception ex)
{
return "";
}
}
/// <summary>
/// 下载仓库表
/// </summary>
/// <returns></returns>
[WebMethod]
public string getStock()
{
string sql = " select FItemID,fname from t_Stock ";
DataTable dt = DataBaseHelper.getTable(sql);
dt.TableName = "Stock";
string xmlStr = new DataTableToXmlHelper().dataTable2XmlStr(dt);
return xmlStr;
}
/// <summary>
/// 上传销售订单
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
[WebMethod]
public string uploadSale(string xmlData)
{
string result = string.Empty;
if (string.IsNullOrEmpty(xmlData)) return "{\"result\":\"false\",\"tip\":\"请求字符串为空\"}";
DataTable dt = new DataTableToXmlHelper().xmlTodataTable(xmlData);
if (dt != null)
{
using (SqlConnection conn = new SqlConnection(DataBaseHelper.mConnString))
{
SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
//计算出主表ID
int fBillId = Convert.ToInt32(dt.Rows[0]["制单人ID"].ToString());
string fid = getMaxId(fBillId);
if (string.IsNullOrEmpty(fid))
{
return "{\"result\":\"false\",\"tip\":\"获取业务流水失败\"}";
}
//添加主表
StringBuilder sqlBuider = new StringBuilder(" insert into ICStockBill")
.Append("(FBrNo,FInterID,FTranType,FDate,FBillNo,FDeptID,FEmpID,FSupplyID,FCheckerID,")
.Append("FVchInterID,FSManagerID,FFManagerID,FRelateBrID,FManagerId,")
.Append("FSelTranType,fChildren,FBrId,")
.Append("FBillerID,FSaleStyle,FROB,FStatus,FUpStockWhenSave,Fnote)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'21',")
.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd")).Append("',")
.Append("'").Append(dt.Rows[0]["id"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["部门ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["业务员ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["购货单位ID"].ToString()).Append("',")
.Append("'',")
.Append("'0',") //凭证内码
.Append("'").Append(dt.Rows[0]["保管人"].ToString()).Append("',") //保管人
.Append("'").Append(dt.Rows[0]["验收人"].ToString()).Append("',")//验收人
.Append("'0',") //分支机构内码
.Append("'").Append(dt.Rows[0]["主管ID"].ToString()).Append("',")//制单人
.Append("'81',")//源单类型
.Append("'1',")//关联标识
.Append("'0',")//制单机构
.Append("'").Append(dt.Rows[0]["制单人ID"].ToString()).Append("',")//制单人
.Append("'").Append(dt.Rows[0]["销售方式ID"].ToString()).Append("',") //销售方式
.Append("'1',") //红蓝字
.Append("'1',")//状态
.Append("'0',") //更新库存
.Append("'").Append(dt.Rows[0]["PrintNumber"].ToString()).Append("')");
//添加子表
StringBuilder sqlBuiderEntry = new StringBuilder(" insert into ICStockBillEntry(FBrNo,FInterId,fentryId,FitemId")
.Append(",FqtyMust,Fqty,Fprice,FAmount,FUnitID,FAuxPrice,FauxQty,FauxQtyMust,FConsignPrice,FConsignAmount,")
.Append("FSourceTranType,FSourceInterId,FSourceEntryId,FSourceBillNo,FOrderBillNo,FOrderInterID,FOrderEntryID,")
.Append("FDCStockID)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'").Append("1").Append("',")
.Append("'").Append(dt.Rows[0]["产品ID"].ToString()).Append("',")//货名ID
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',")//申请数量
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //实际数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //金额
.Append("'").Append(dt.Rows[0]["单位ID"].ToString()).Append("',") //单位
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',")//辅助单价
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //辅助实际数量
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',") //辅助账存数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //代销单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //代销金额
.Append("'").Append(dt.Rows[0]["单据类型ID"].ToString()).Append("',") //源单类型
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //源单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',") //原分录号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //源单单号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //订单单号
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //订单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',")//订单分录
.Append("'").Append(dt.Rows[0]["仓库编号"].ToString()).Append("'")//仓库编号
.Append(")");
//更新计划
StringBuilder updateTicket = new StringBuilder(" update SEOrderEntry set ")
.Append("FCommitQty=FCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxCommitQty=FAuxCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FStockQty=FStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxStockQty=FAuxStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FQtyInvoice=FQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxQtyInvoice=FAuxQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("'")
.Append(" where fInterID='").Append(dt.Rows[0]["FInterID"].ToString()).Append("'")
.Append(" and FEntryID='").Append(dt.Rows[0]["FEntryID"].ToString()).Append("'");
//更新maxId
// string updateMaxId = "update ICMaxNum set FMaxNum=FMaxNum+1 where ftablename='ICStockBill'";
List<string> sqlList = new List<string>();
sqlList.Add(sqlBuider.ToString());
sqlList.Add(sqlBuiderEntry.ToString());
sqlList.Add(updateTicket.ToString());
// sqlList.Add(updateMaxId);
for (int index = 0; index < sqlList.Count; index++)
{
cmd.CommandText = sqlList[index];
cmd.ExecuteNonQuery();
}
tran.Commit();
result = "{\"result\":\"true\",\"tip\":\"上传成功\"}";
}
catch (Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
result = "{\"result\":\"false\",\"tip\":\"" + ex.Message + ex.StackTrace + "\"}";
}
}
}
else
{
result = "{\"result\":\"false\",\"tip\":\"解析错误!\"}";
}
return result;
}
/// <summary>
/// 修改销售订单
/// </summary>
/// <param name="xmlData">修改的称重数据</param>
/// <param name="oldWeightId">被修该的称重ID</param>
/// <param name="ticketId">被修改的过磅订单编号</param>
/// <param name="oldNet">被修改的净重</param>
/// <returns></returns>
[WebMethod]
public string updateSale(string xmlData, string oldWeightId, string fInnerId,string entryId , string oldNet)
{
string result = string.Empty;
if (string.IsNullOrEmpty(xmlData)) return "{\"result\":\"false\",\"tip\":\"请求字符串为空\"}";
DataTable dt = new DataTableToXmlHelper().xmlTodataTable(xmlData);
if (dt != null)
{
SqlTransaction tran = null;
using (SqlConnection conn = new SqlConnection(DataBaseHelper.mConnString))
{
try
{
conn.Open();
tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
//删除 出库单信息
string deleteStocke = "delete from icStockBill where FBillNo='" + oldWeightId + "'";
cmd.CommandText = deleteStocke;
int count = cmd.ExecuteNonQuery();
if (count == 0)
{
tran.Rollback();
return "{\"result\":\"false\",\"tip\":\"删除销售出库单出错\"}";
}
//更新合同信息
StringBuilder rollBackTicket = new StringBuilder(" update SEOrderEntry set ")
.Append("FCommitQty=FCommitQty-'").Append(oldNet).Append("',")
.Append("FAuxCommitQty=FAuxCommitQty-'").Append(oldNet).Append("',")
.Append("FStockQty=FAuxCommitQty-'").Append(oldNet).Append("',")
.Append("FAuxStockQty=FAuxCommitQty-'").Append(oldNet).Append("',")
.Append("FQtyInvoice=FAuxCommitQty-'").Append(oldNet).Append("',")
.Append("FAuxQtyInvoice=FAuxCommitQty-'").Append(oldNet).Append("'")
.Append(" where FInterId='").Append(fInnerId).Append("'")
.Append(" and FEntryId='").Append(entryId).Append("'");
cmd.CommandText = rollBackTicket.ToString();
count = cmd.ExecuteNonQuery();
if (count == 0)
{
tran.Rollback();
return "{\"result\":\"false\",\"tip\":\"回滚计划出错\"}";
}
//计算出主表ID
int fBillId = Convert.ToInt32(dt.Rows[0]["制单人ID"].ToString());
string fid = getMaxId(fBillId);
if (string.IsNullOrEmpty(fid))
{
return "{\"result\":\"false\",\"tip\":\"获取业务流水失败\"}";
}
//添加主表
StringBuilder sqlBuider = new StringBuilder(" insert into ICStockBill")
.Append("(FBrNo,FInterID,FTranType,FDate,FBillNo,FDeptID,FEmpID,FSupplyID,FCheckerID,")
.Append("FVchInterID,FSManagerID,FFManagerID,FRelateBrID,FManagerId,")
.Append("FSelTranType,fChildren,FBrId,")
.Append("FBillerID,FSaleStyle,FROB,FStatus,FUpStockWhenSave,Fnote)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'21',")
.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd")).Append("',")
.Append("'").Append(dt.Rows[0]["id"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["部门ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["业务员ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["购货单位ID"].ToString()).Append("',")
.Append("'',")
.Append("'0',") //凭证内码
.Append("'").Append(dt.Rows[0]["保管人"].ToString()).Append("',") //保管人
.Append("'").Append(dt.Rows[0]["验收人"].ToString()).Append("',")//验收人
.Append("'0',") //分支机构内码
.Append("'").Append(dt.Rows[0]["主管ID"].ToString()).Append("',")//制单人
.Append("'81',")//源单类型
.Append("'1',")//关联标识
.Append("'0',")//制单机构
.Append("'").Append(dt.Rows[0]["制单人ID"].ToString()).Append("',")//制单人
.Append("'").Append(dt.Rows[0]["销售方式ID"].ToString()).Append("',") //销售方式
.Append("'1',") //红蓝字
.Append("'1',")//状态
.Append("'0',") //更新库存
.Append("'").Append(dt.Rows[0]["Platenumber"].ToString()).Append("')");
//添加子表
StringBuilder sqlBuiderEntry = new StringBuilder(" insert into ICStockBillEntry(FBrNo,FInterId,fentryId,FitemId")
.Append(",FqtyMust,Fqty,Fprice,FAmount,FUnitID,FAuxPrice,FauxQty,FauxQtyMust,FConsignPrice,FConsignAmount,")
.Append("FSourceTranType,FSourceInterId,FSourceEntryId,FSourceBillNo,FOrderBillNo,FOrderInterID,FOrderEntryID,")
.Append("FDCStockID)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'").Append("1").Append("',")
.Append("'").Append(dt.Rows[0]["产品ID"].ToString()).Append("',")//货名ID
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',")//申请数量
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //实际数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //金额
.Append("'").Append(dt.Rows[0]["单位ID"].ToString()).Append("',") //单位
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',")//辅助单价
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //辅助实际数量
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',") //辅助账存数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //代销单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //代销金额
.Append("'").Append(dt.Rows[0]["单据类型ID"].ToString()).Append("',") //源单类型
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //源单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',") //原分录号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //源单单号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //订单单号
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //订单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',")//订单分录
.Append("'").Append(dt.Rows[0]["仓库编号"].ToString()).Append("'")//仓库编号
.Append(")");
//更新计划
StringBuilder updateTicket = new StringBuilder(" update SEOrderEntry set ")
.Append("FCommitQty=FCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxCommitQty=FAuxCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FStockQty=FStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxStockQty=FAuxStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FQtyInvoice=FQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxQtyInvoice=FAuxQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("'")
.Append(" where FInterId='").Append(fInnerId).Append("'")
.Append(" and FEntryId='").Append(entryId).Append("'");
//更新maxId
//string updateMaxId = "update ICMaxNum set FMaxNum=FMaxNum+1 where ftablename='ICStockBill'";
List<string> sqlList = new List<string>();
sqlList.Add(sqlBuider.ToString());
sqlList.Add(sqlBuiderEntry.ToString());
sqlList.Add(updateTicket.ToString());
// sqlList.Add(updateMaxId);
for (int index = 0; index < sqlList.Count; index++)
{
cmd.CommandText = sqlList[index];
cmd.ExecuteNonQuery();
}
tran.Commit();
result = "{\"result\":\"true\",\"tip\":\"修改成功\"}";
}
catch (Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
result = "{\"result\":\"false\",\"tip\":\"" + ex.Message + ex.StackTrace + "\"}";
}
}
}
else
{
result = "{\"result\":\"false\",\"tip\":\"解析错误!\"}";
}
return result;
}
/// <summary>
/// 获取订单剩余量
/// </summary>
/// <param name="ticketNo">订单编号</param>
/// <returns></returns>
[WebMethod]
public string getSaleSurplus(string ticketNo)
{
string result = "{\"result\":\"false\",\"val\":\"0\",\"flag\":\"-1\"}";
string sql = "select 剩余量,FClosed from TicketViewsale where FBillNo='" + ticketNo + "'";
DataTable dt = DataBaseHelper.getTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
result = "{\"result\":\"true\",\"val\":\"" + dt.Rows[0]["剩余量"].ToString() + "\",\"flag\":\"" + dt.Rows[0]["FClosed"].ToString() + "\"}";
}
return result;
}
/// <summary>
///获取采购订单
/// </summary>
/// <param name="ticketNo"></param>
/// <returns></returns>
[WebMethod]
public string getBuySurplus(string ticketNo)
{
string result = "{\"result\":\"false\",\"val\":\"0\",\"flag\":\"-1\"}";
string sql = "select 剩余量,状态 from TicketViewBuy where FBillNo='" + ticketNo + "'";
DataTable dt = DataBaseHelper.getTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
result = "{\"result\":\"true\",\"val\":\"" + dt.Rows[0]["剩余量"].ToString() + "\",\"flag\":\"" + dt.Rows[0]["状态"].ToString() + "\"}";
}
return result;
}
/// <summary>
/// 上传采购订单
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
[WebMethod]
public string uploadBuy(string xmlData)
{
string result = string.Empty;
if (string.IsNullOrEmpty(xmlData)) return "{\"result\":\"false\",\"tip\":\"请求字符串为空\"}";
DataTable dt = new DataTableToXmlHelper().xmlTodataTable(xmlData);
if (dt != null)
{
using (SqlConnection conn = new SqlConnection(DataBaseHelper.mConnString))
{
SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
//计算出主表ID
int fBillId = Convert.ToInt32(dt.Rows[0]["制单人ID"].ToString());
string fid = getMaxId(fBillId);
if (string.IsNullOrEmpty(fid))
{
return "{\"result\":\"false\",\"tip\":\"获取业务流水失败\"}";
}
//添加主表
StringBuilder sqlBuider = new StringBuilder(" insert into ICStockBill")
.Append("(FBrNo,FInterID,FTranType,FDate,FBillNo,FDeptID,FEmpID,FSupplyID,FCheckerID,")
.Append("FVchInterID,FSManagerID,FFManagerID,FRelateBrID,FManagerId,")
.Append("FSelTranType,fChildren,FBrId,")
.Append("FBillerID,FPOStyle,FROB,FStatus,FUpStockWhenSave,FSettleDate,Fnote)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'1',")
.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd")).Append("',")
.Append("'").Append(dt.Rows[0]["id"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["部门ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["业务员ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["供应商ID"].ToString()).Append("',")
.Append("'',")
.Append("'0',") //凭证内码
.Append("'").Append(dt.Rows[0]["保管人"].ToString()).Append("',") //保管人
.Append("'").Append(dt.Rows[0]["验收人"].ToString()).Append("',")//验收人
.Append("'0',") //分支机构内码
.Append("'").Append(dt.Rows[0]["主管ID"].ToString()).Append("',")//制单人
.Append("'71',")//源单类型(采购的为71)
.Append("'1',")//关联标识
.Append("'0',")//制单机构
.Append("'").Append(dt.Rows[0]["制单人ID"].ToString()).Append("',")//制单人
.Append("'").Append(dt.Rows[0]["采购方式ID"].ToString()).Append("',") //销售方式
.Append("'1',") //红蓝字
.Append("'1',")//状态
.Append("'0',") //更新库存
.Append("'").Append(dt.Rows[0]["sDate"].ToString() + " 00:00:00.000").Append("',") //销售方式
.Append("'").Append(dt.Rows[0]["PrintNumber"].ToString()).Append("')");
//添加子表
StringBuilder sqlBuiderEntry = new StringBuilder(" insert into ICStockBillEntry(FBrNo,FInterId,fentryId,FitemId")
.Append(",FqtyMust,Fqty,Fprice,FAmount,FUnitID,FAuxPrice,FauxQty,FauxQtyMust,FConsignPrice,FConsignAmount,")
.Append("FSourceTranType,FSourceInterId,FSourceEntryId,FSourceBillNo,FOrderBillNo,FOrderInterID,FOrderEntryID,")
.Append("FDCStockID)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'").Append("1").Append("',")
.Append("'").Append(dt.Rows[0]["货名ID"].ToString()).Append("',")//货名ID
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',")//申请数量
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //实际数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //金额
.Append("'").Append(dt.Rows[0]["单位ID"].ToString()).Append("',") //单位
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',")//辅助单价
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //辅助实际数量
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',") //辅助账存数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //代销单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //代销金额
.Append("'").Append(dt.Rows[0]["单据类型ID"].ToString()).Append("',") //源单类型
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //源单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',") //原分录号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //源单单号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //订单单号
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //订单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',")//订单分录
.Append("'").Append(dt.Rows[0]["仓库编号"].ToString()).Append("'")//仓库编号
.Append(")");
//更新计划
StringBuilder updateTicket = new StringBuilder(" update POOrderEntry set ")
.Append("FCommitQty=FCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxCommitQty=FAuxCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FStockQty=FStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxStockQty=FAuxStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FQtyInvoice=FQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxQtyInvoice=FAuxQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("'")
.Append(" where fInterID='").Append(dt.Rows[0]["FInterID"].ToString()).Append("'")
.Append(" and FEntryID='").Append(dt.Rows[0]["FEntryID"].ToString()).Append("'");
//更新maxId
//string updateMaxId = "update ICMaxNum set FMaxNum=FMaxNum+1 where ftablename='ICStockBill'";
List<string> sqlList = new List<string>();
sqlList.Add(sqlBuider.ToString());
sqlList.Add(sqlBuiderEntry.ToString());
sqlList.Add(updateTicket.ToString());
// sqlList.Add(updateMaxId);
for (int index = 0; index < sqlList.Count; index++)
{
cmd.CommandText = sqlList[index];
cmd.ExecuteNonQuery();
}
tran.Commit();
result = "{\"result\":\"true\",\"tip\":\"上传成功\"}";
}
catch (Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
result = "{\"result\":\"false\",\"tip\":\"" + ex.Message + ex.StackTrace + "\"}";
}
}
}
else
{
result = "{\"result\":\"false\",\"tip\":\"解析错误!\"}";
}
return result;
}
/// <summary>
/// 修改采购订单
/// </summary>
/// <param name="xmlData">修改的称重数据</param>
/// <param name="oldWeightId">被修该的称重ID</param>
/// <param name="ticketId">被修改的过磅订单编号</param>
/// <param name="oldNet">被修改的净重</param>
/// <returns></returns>
[WebMethod]
public string updateBuy(string xmlData, string oldWeightId, string fInnerId, string entryId, string oldNet)
{
string result = string.Empty;
if (string.IsNullOrEmpty(xmlData)) return "{\"result\":\"false\",\"tip\":\"请求字符串为空\"}";
DataTable dt = new DataTableToXmlHelper().xmlTodataTable(xmlData);
if (dt != null)
{
SqlTransaction tran = null;
using (SqlConnection conn = new SqlConnection(DataBaseHelper.mConnString))
{
try
{
conn.Open();
tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
//删除 出库单信息
string deleteStocke = "delete from icStockBill where FBillNo='" + oldWeightId + "'";
cmd.CommandText = deleteStocke;
int count = cmd.ExecuteNonQuery();
if (count == 0)
{
tran.Rollback();
return "{\"result\":\"false\",\"tip\":\"删除销售出库单出错\"}";
}
//更新计划
StringBuilder rollBackTicket = new StringBuilder(" update POOrderEntry set ")
.Append("FCommitQty=FCommitQty-'").Append(oldNet).Append("',")
.Append("FAuxCommitQty=FAuxCommitQty-'").Append(oldNet).Append("',")
.Append("FStockQty=FStockQty-'").Append(oldNet).Append("',")
.Append("FAuxStockQty=FAuxStockQty-'").Append(oldNet).Append("',")
.Append("FQtyInvoice=FQtyInvoice-'").Append(oldNet).Append("',")
.Append("FAuxQtyInvoice=FAuxQtyInvoice-'").Append(oldNet).Append("'")
.Append(" where fInterID='").Append(dt.Rows[0]["FInterID"].ToString()).Append("'")
.Append(" and FEntryID='").Append(dt.Rows[0]["FEntryID"].ToString()).Append("'");
cmd.CommandText = rollBackTicket.ToString();
count = cmd.ExecuteNonQuery();
if (count == 0)
{
tran.Rollback();
return "{\"result\":\"false\",\"tip\":\"回滚计划出错\"}";
}
//计算出主表ID
int fBillId = Convert.ToInt32(dt.Rows[0]["制单人ID"].ToString());
string fid = getMaxId(fBillId);
if (string.IsNullOrEmpty(fid))
{
return "{\"result\":\"false\",\"tip\":\"获取业务流水失败\"}";
}
//添加主表
StringBuilder sqlBuider = new StringBuilder(" insert into ICStockBill")
.Append("(FBrNo,FInterID,FTranType,FDate,FBillNo,FDeptID,FEmpID,FSupplyID,FCheckerID,")
.Append("FVchInterID,FSManagerID,FFManagerID,FRelateBrID,FManagerId,")
.Append("FSelTranType,fChildren,FBrId,")
.Append("FBillerID,FPOStyle,FROB,FStatus,FUpStockWhenSave,FSettleDate,Fnote)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'1',")
.Append("'").Append(DateTime.Now.ToString("yyyy-MM-dd")).Append("',")
.Append("'").Append(dt.Rows[0]["id"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["部门ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["业务员ID"].ToString()).Append("',")
.Append("'").Append(dt.Rows[0]["供应商ID"].ToString()).Append("',")
.Append("'',")
.Append("'0',") //凭证内码
.Append("'").Append(dt.Rows[0]["保管人"].ToString()).Append("',") //保管人
.Append("'").Append(dt.Rows[0]["验收人"].ToString()).Append("',")//验收人
.Append("'0',") //分支机构内码
.Append("'").Append(dt.Rows[0]["主管ID"].ToString()).Append("',")//制单人
.Append("'71',")//源单类型(采购的为71)
.Append("'1',")//关联标识
.Append("'0',")//制单机构
.Append("'").Append(dt.Rows[0]["制单人ID"].ToString()).Append("',")//制单人
.Append("'").Append(dt.Rows[0]["采购方式ID"].ToString()).Append("',") //销售方式
.Append("'1',") //红蓝字
.Append("'1',")//状态
.Append("'0',") //更新库存
.Append("'").Append(dt.Rows[0]["sDate"].ToString() + " 00:00:00.000").Append("',") //销售方式
.Append("'").Append(dt.Rows[0]["PrintNumber"].ToString()).Append("')");
//添加子表
StringBuilder sqlBuiderEntry = new StringBuilder(" insert into ICStockBillEntry(FBrNo,FInterId,fentryId,FitemId")
.Append(",FqtyMust,Fqty,Fprice,FAmount,FUnitID,FAuxPrice,FauxQty,FauxQtyMust,FConsignPrice,FConsignAmount,")
.Append("FSourceTranType,FSourceInterId,FSourceEntryId,FSourceBillNo,FOrderBillNo,FOrderInterID,FOrderEntryID,")
.Append("FDCStockID)values(")
.Append("'").Append(dt.Rows[0]["公司内码ID"].ToString()).Append("',")
.Append("'").Append(fid).Append("',")
.Append("'").Append("1").Append("',")
.Append("'").Append(dt.Rows[0]["货名ID"].ToString()).Append("',")//货名ID
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',")//申请数量
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //实际数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //金额
.Append("'").Append(dt.Rows[0]["单位ID"].ToString()).Append("',") //单位
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',")//辅助单价
.Append("'").Append(dt.Rows[0]["Net"].ToString()).Append("',") //辅助实际数量
.Append("'").Append(dt.Rows[0]["剩余量"].ToString()).Append("',") //辅助账存数量
.Append("'").Append(dt.Rows[0]["Price"].ToString()).Append("',") //代销单价
.Append("'").Append(dt.Rows[0]["Amount"].ToString()).Append("',") //代销金额
.Append("'").Append(dt.Rows[0]["单据类型ID"].ToString()).Append("',") //源单类型
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //源单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',") //原分录号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //源单单号
.Append("'").Append(dt.Rows[0]["FBillNo"].ToString()).Append("',") //订单单号
.Append("'").Append(dt.Rows[0]["FInterID"].ToString()).Append("',") //订单编号
.Append("'").Append(dt.Rows[0]["FEntryID"].ToString()).Append("',")//订单分录
.Append("'").Append(dt.Rows[0]["仓库编号"].ToString()).Append("'")//仓库编号
.Append(")");
//更新计划
StringBuilder updateTicket = new StringBuilder(" update POOrderEntry set ")
.Append("FCommitQty=FCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxCommitQty=FAuxCommitQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FStockQty=FStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxStockQty=FAuxStockQty+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FQtyInvoice=FQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("',")
.Append("FAuxQtyInvoice=FAuxQtyInvoice+'").Append(dt.Rows[0]["Net"].ToString()).Append("'")
.Append(" where fInterID='").Append(dt.Rows[0]["FInterID"].ToString()).Append("'")
.Append(" and FEntryID='").Append(dt.Rows[0]["FEntryID"].ToString()).Append("'");
//更新maxId
// string updateMaxId = "update ICMaxNum set FMaxNum=FMaxNum+1 where ftablename='ICStockBill'";
List<string> sqlList = new List<string>();
sqlList.Add(sqlBuider.ToString());
sqlList.Add(sqlBuiderEntry.ToString());
sqlList.Add(updateTicket.ToString());
// sqlList.Add(updateMaxId);
for (int index = 0; index < sqlList.Count; index++)
{
cmd.CommandText = sqlList[index];
cmd.ExecuteNonQuery();
}
tran.Commit();
result = "{\"result\":\"true\",\"tip\":\"修改成功\"}";
}
catch (Exception ex)
{
if (tran != null)
{
tran.Rollback();
}
result = "{\"result\":\"false\",\"tip\":\"" + ex.Message + ex.StackTrace + "\"}";
}
}
}
else
{
result = "{\"result\":\"false\",\"tip\":\"解析错误!\"}";
}
return result;
}
private void restartOne()
{
DataBaseHelper.mConnString = string.Empty;
DataBaseHelper.getConnStr();
}
public string getMaxId(int fbillId)
{
string result = string.Empty;
using (SqlConnection conn = new SqlConnection(DataBaseHelper.mConnString))
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
//计算出主表ID
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetICMaxNum";
SqlParameter tablenamePara = new SqlParameter("@TableName", SqlDbType.VarChar);
tablenamePara.Value = "ICStockBill";
SqlParameter FInterID = new SqlParameter("@FInterID", SqlDbType.Int);
FInterID.Direction = ParameterDirection.Output;
SqlParameter incrementParam = new SqlParameter("@Increment", SqlDbType.Int);
incrementParam.Value = 1;
SqlParameter userIdPara = new SqlParameter("@UserID", SqlDbType.VarChar);
userIdPara.Value = fbillId;
cmd.Parameters.Add(tablenamePara);
cmd.Parameters.Add(FInterID);
cmd.Parameters.Add(incrementParam);
cmd.Parameters.Add(userIdPara);
cmd.ExecuteNonQuery();
string value = Convert.ToString( FInterID.Value);
if (value.Length > 0)
{
result = value;
}
}
catch (Exception ex)
{
}
return result;
}
}
}