/// <summary>
/// 根据发票号查询发票单据信息
/// </summary>
/// <param name="cSBVCode">单据号</param>
/// <returns></returns>
public static DataTable GetSaleBillVouchByCSBVCode(string connectionString, string cSBVCode, out string errMsg)
{
DataTable dt = null;
errMsg = string.Empty;
//首先判断单据是否存在
string strSql = string.Format("IF EXISTS(SELECT 1 FROM SaleBillVouch WHERE cSBVCode='{0}') SELECT 1 ELSE SELECT 0", cSBVCode);
int result = Convert.ToInt32(DBHelperSQL.ExecuteScalar(connectionString, strSql));
if (result == 0)
{
errMsg = "单据不存在！";
return(dt);
}
//再判断单据是否审核
strSql = string.Format("IF EXISTS(SELECT 1 FROM SaleBillVouch WHERE cSBVCode='{0}' AND cVerifier IS NOT NULL AND dverifydate IS NOT NULL) SELECT 1 ELSE SELECT 0", cSBVCode);
result = Convert.ToInt32(DBHelperSQL.ExecuteScalar(connectionString, strSql));
if (result == 0)
{
errMsg = "单据未审核！";
return(dt);
}
strSql = string.Format("SELECT * FROM SaleBillVouch WHERE cSBVCode='{0}'", cSBVCode);
dt = DBHelperSQL.Query(connectionString, strSql).Tables[0];
return(dt);
}
/// <summary>
/// 根据存货编码，盘点单，批次信息查询盘点单详细
/// </summary>
/// <param name="barcode">存货编码</param>
/// <param name="cCVCode">盘点单</param>
/// <param name="cCVBatch">批次</param>
/// <param name="connectionString">链接字符串</param>
/// <param name="qty"></param>
/// <param name="sqty"></param>
/// <param name="autoid"></param>
/// <param name="cinvname">存货名称</param>
/// <param name="cinvdefine1">生产厂家</param>
/// <param name="cinvstd">规格</param>
/// <param name="cinvdefine6">产地</param>
/// <returns></returns>
public static int getQtyByBarcode(string barcode, string cCVCode, ref string cCVBatch, string connectionString,
out string qty, out string sqty, out string autoid, out string cinvname, out string cinvdefine1, out string cinvstd, out string cinvdefine6, out string errMsg)
{
string sql = "";
DataSet ds = null;
errMsg = string.Empty;
qty = "";
sqty = "";
autoid = "";
cinvname = "";
cinvdefine1 = "";
cinvstd = "";
cinvdefine6 = "";
sql = "select a.iCVQuantity,a.autoid,a.ccvbatch,i.cinvname,i.cinvdefine1,i.cInvStd,i.cinvdefine6 from checkvouchs a "
+ " join inventory i on i.cinvcode=a.cinvcode "
+ "join checkvouch b on b.id=a.id and b.cCVCode='" + cCVCode + "' "
+ " and a.cinvcode='" + barcode + "'";
if (!cCVBatch.Equals(""))
{
sql = sql + " and a.cCVBatch = '" + cCVBatch + "'";
}
//OperationSql.GetDataset(sql, connectionString, out ds, out errMsg);
ds = DBHelperSQL.Query(connectionString, sql);
if (ds != null && ds.Tables[0].Rows.Count == 0)
{
string _invname = null;
sql = "select cinvname from inventory where cinvcode='" + barcode + "'";
//OperationSql.GetString(sql, connectionString, out _invname, out errMsg);
_invname = DBHelperSQL.ExecuteScalar(connectionString, sql).ToString();
if (_invname == null || _invname.Length < 1)
{
errMsg = "该产品条码有误!";
return(-1);
}
sqty = "0";
autoid = "0";
cinvname = _invname;
errMsg = "该产品不属于该盘点单!";
return(-1);
}
sqty = ds.Tables[0].Rows[0][0].ToString();
autoid = ds.Tables[0].Rows[0][1].ToString();
cCVBatch = ds.Tables[0].Rows[0][2].ToString();
cinvname = ds.Tables[0].Rows[0][3].ToString();
cinvdefine1 = ds.Tables[0].Rows[0][4].ToString(); //生产厂家
cinvstd = ds.Tables[0].Rows[0][5].ToString(); //批次
cinvdefine6 = ds.Tables[0].Rows[0][6].ToString(); //产地
return(0);
}
public static int GetSTInProduct(string cInvCode, string connectionString, out DataSet list, out string errMsg)
{
list = null;
errMsg = "";
string strSql = @"SELECT [KCInventoryEntity_Inventory].[cInvCode] as cInvCode,
[KCInventoryEntity_Inventory].[cInvAddCode] as cInvAddCode,
[KCInventoryEntity_Inventory].[cInvName] as cInvName,
[KCInventoryEntity_Inventory].[cInvStd] as cInvStd,
[KCInventoryEntity_Inventory].[cInvDefine1] as cInvDefine1,
[KCInventoryEntity_Inventory].[cInvCCode] as cInvCCode,
[KCInventoryEntity_Inventory].[iMassDate] as iMassDate,
[KCInventoryEntity_Inventory].[cMassUnit] as cMassUnit,
[KCInventoryEntity_Inventory].[iInvRCost] as iInvRCost,
[KCInventoryEntity_Inventory].[dSDate] as dSDate,
[KCInventoryEntity_Inventory].[dEDate] as dEDate,
[KCInventoryEntity_Inventory].[cInvDefine2] as cInvDefine2,
[KCInventoryEntity_ComputationUnit].[cGroupCode] as cGroupCode,
[KCInventoryEntity_Inventory].[cComUnitCode] as cComUnitCode,
[KCInventoryEntity_ComputationUnit].[cComUnitName] as cComUnitName,
[KCInventoryEntity_InventoryClass].[cInvCName] as cInvCName ,
[KCInventoryEntity_Inventory].[cSTComUnitCode] as cSTComUnitCode,
[COMUNIT].[iChangRate] as iinvexchrate
FROM [Inventory] AS [KCInventoryEntity_Inventory]
LEFT JOIN [ComputationUnit] AS [KCInventoryEntity_ComputationUnit] ON [KCInventoryEntity_ComputationUnit].[cGroupCode] = [KCInventoryEntity_Inventory].[cGroupCode] and [KCInventoryEntity_ComputationUnit].[cComunitCode] = [KCInventoryEntity_Inventory].[cComUnitCode]
LEFT JOIN [InventoryClass] AS [KCInventoryEntity_InventoryClass] ON [KCInventoryEntity_InventoryClass].[cInvCCode] = [KCInventoryEntity_Inventory].[cInvCCode]
LEFT JOIN [ComputationUnit] AS [COMUNIT] ON [KCInventoryEntity_Inventory].[cSTComUnitCode] = [COMUNIT].[cComunitCode]
WHERE [KCInventoryEntity_Inventory].[bService]=0
And [KCInventoryEntity_Inventory].[bInvType]=0
and [KCInventoryEntity_Inventory].[bSelf]=1
and datediff(day,isnull([KCInventoryEntity_Inventory].[dEDate],N'9999-12-31'),GETDATE())<=0
AND isnull([KCInventoryEntity_Inventory].[bPlanInv],0)=0
and isnull([KCInventoryEntity_Inventory].[bCheckItem],0)=0
and isnull([KCInventoryEntity_Inventory].[bPTOModel],0)=0
and [KCInventoryEntity_Inventory].[cInvCode] = N'" + cInvCode + "'";
//return OperationSql.GetDataset(strSql, connectionString, out list, out errMsg);
int flag = -1;
try
{
list = DBHelperSQL.Query(connectionString, strSql);
flag = 0;
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(flag);
}
public static int getCVcodeList(string connectionString, out DataSet ds, out string errMsg)
{
errMsg = string.Empty;
ds = null;
//string sql = @"select cCVCode, cWhName from CheckVouch c left join Warehouse w on w.cWhCode = c.cWhCode where dveridate is null";
string sql = "SELECT cCVCode ,cWhCode FROM dbo.CheckVouch WHERE dveridate IS NULL";
try
{
ds = DBHelperSQL.Query(connectionString, sql);
}
catch (Exception ex)
{
errMsg = ex.Message;
}
if (ds.Tables[0].Rows.Count == 0)
{
errMsg = "没有查询到盘点单";
return(-1);
}
return(0);
}
/// <summary>
/// 根据货位及存货编码获取盘点单子表数据
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="cCVCode">盘点单</param>
/// <param name="cInvCode">存货编码</param>
/// <param name="cPosition">货位</param>
/// <param name="cBatch">批次</param>
/// <param name="ds">查询结果列表</param>
/// <param name="errMsg"></param>
/// <returns></returns>
public static int GetQtyByCode(string connectionString, string cCVCode, string cInvCode, string cPosition, string cBatch, out DataSet ds, out string errMsg)
{
ds = null;
errMsg = string.Empty;
///查询条件
string sqlCon = string.Empty;
//判断是否有货位管理
if (!string.IsNullOrEmpty(cPosition))
{
sqlCon += string.Format(" AND a.cPosition='{0}'", cPosition);
}
//判断是否按批次查询
if (!string.IsNullOrEmpty(cBatch))
{
sqlCon += string.Format(" AND a.cCVBatch='{0}'", cBatch);
}
string SQL = string.Format(@"SELECT i.cInvCode, i.cInvName,i.cInvStd,i.cinvdefine1,i.cinvdefine6,c.cComUnitName,a.cCVBatch,a.cPosition,a.iCVQuantity,a.dMadeDate,a.cExpirationdate,a.dDisDate
FROM dbo.CheckVouchs a
join inventory i on i.cinvcode=a.cinvcode
LEFT JOIN ComputationUnit c ON i.cComUnitCode= c.cComunitCode
join checkvouch b on b.id=a.id
WHERE b.cCVCode='{0}' and a.cinvcode='{1}' {2}" , cCVCode, cInvCode, sqlCon);
//return OperationSql.GetDataset(SQL, connectionString, out ds, out errMsg);
int flag = -1;
try
{
ds = DBHelperSQL.Query(connectionString, SQL);
flag = 0;
}
catch (Exception ex)
{
errMsg = ex.Message;
}
return(flag);
}
/// <summary>
/// 获取所有的物流名称
/// </summary>
/// <returns></returns>
public static DataTable GetShoppingChoiceList(string connectionString)
{
string strSql = "SELECT cSCCode,cSCName FROM ShippingChoice";
return(DBHelperSQL.Query(connectionString, strSql).Tables[0]);
}