public static string GetProductUseCount(SubDeliverySendDetail model)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append("select @UseCount=((isnull(sp.ProductCount,0)+isnull(sp.InCount,0)+isnull(sp.RoadCount,0)-isnull(sp.OutCount,0)-isnull(sp.OrderCount,0))) from officedba.StorageProduct as sp where sp.StorageID=@StorageID AND sp.ProductID=@ProductID AND sp.CompanyCD=@CompanyCD ");
SqlParameter[] Paras = {
new SqlParameter("@UseCount",SqlDbType.Int),
new SqlParameter("@StorageID",SqlDbType.Int),
new SqlParameter("@ProductID",SqlDbType.Int),
new SqlParameter("@CompanyCD",SqlDbType.VarChar)
};
Paras[0].Direction = ParameterDirection.Output;
Paras[1].Value = model.StorageID;
Paras[2].Value = model.ProductID;
Paras[3].Value = model.CompanyCD;
SqlHelper.ExecuteSql(sbSql.ToString(), Paras);
return Paras[0].Value.ToString();
}
/*打印使用*/
public static DataTable GetSubDeliverySendDetailPrint(SubDeliverySendDetail model, int DeptID)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@"SELECT stsd.ID, stsd.CompanyCD, stsd.SendNo, stsd.SortNo, stsd.ProductID, stsd.StorageID,stsd.BatchNo, pi.ProdNo, pi.ProductName, pi.Specification, pi.UnitID, pi.MinusIs,stsd.UnitID AS UsedUnitID,stsd.UsedUnitCount
,CONVERT(NUMERIC(12,2),ISNULL(stsd.SendPrice,0)) SendPrice
,CONVERT(NUMERIC(12,2),ISNULL(stsd.SendCount,0)) SendCount
,CONVERT(NUMERIC(12,2),ISNULL(stsd.SendPriceTotal,0)) SendPriceTotal
,CASE WHEN spsp.ID IS NULL THEN sp.ID ELSE spsp.ID END AS Expr1
,CASE WHEN spsp.CompanyCD IS NULL THEN sp.CompanyCD ELSE spsp.CompanyCD END AS Expr2
,CASE WHEN spsp.ProductID IS NULL THEN sp.ProductID ELSE spsp.ProductID END AS Expr3
,CASE WHEN spsp.DeptID IS NULL THEN sp.DeptID ELSE spsp.DeptID END AS DeptID
,CASE WHEN spsp.Creator IS NULL THEN sp.Creator ELSE spsp.Creator END AS Creator
,CASE WHEN spsp.CreateDate IS NULL THEN sp.CreateDate ELSE spsp.CreateDate END AS CreateDate
,CASE WHEN spsp.ModifiedUserID IS NULL THEN sp.ModifiedUserID ELSE spsp.ModifiedUserID END AS ModifiedUserID
,CONVERT(NUMERIC(12,2),ISNULL(CASE WHEN spsp.SendPrice IS NULL THEN sp.SendPrice ELSE spsp.SendPrice END,0)) SendPrice
,CONVERT(NUMERIC(12,2),ISNULL(CASE WHEN spsp.SendPriceTax IS NULL THEN sp.SendPriceTax ELSE spsp.SendPriceTax END,0)) SendPriceTax
,CONVERT(NUMERIC(12,2),ISNULL(CASE WHEN spsp.SendTax IS NULL THEN sp.SendTax ELSE spsp.SendTax END,0)) SendTax
,CONVERT(NUMERIC(12,2),ISNULL(CASE WHEN spsp.Discount IS NULL THEN sp.Discount ELSE spsp.Discount END,0)) Discount
,(SELECT ISNULL(ProductCount, 0) + ISNULL(InCount, 0) + ISNULL(RoadCount, 0) - ISNULL(OutCount, 0) - ISNULL(OrderCount, 0) AS Expr1
FROM officedba.StorageProduct AS sp
WHERE (StorageID = stsd.StorageID) AND (ProductID = stsd.ProductID) AND (CompanyCD = stsd.CompanyCD) AND isnull(stsd.BatchNo,'')=isnull(sp.BatchNO,'') ) AS UseCount,
(SELECT CodeName
FROM officedba.CodeUnitType AS ui
WHERE (ID = pi.UnitID)) AS UnitName,
(SELECT CodeName
FROM officedba.CodeUnitType AS ui1
WHERE (ID = stsd.UnitID)) AS UsedUnitName,
(SELECT StorageName
FROM officedba.StorageInfo AS si
WHERE (ID = stsd.StorageID)) AS StorageName
FROM officedba.SubDeliverySendDetail AS stsd
INNER JOIN officedba.ProductInfo AS pi ON stsd.CompanyCD=PI.CompanyCD AND stsd.ProductID = pi.ID
LEFT JOIN officedba.SubProductSendPrice AS sp ON stsd.CompanyCD = sp.CompanyCD AND stsd.ProductID = sp.ProductID AND sp.DeptID=@DeptID
LEFT JOIN officedba.SubProductSendPrice spsp ON stsd.CompanyCD=spsp.CompanyCD AND stsd.ProductID=spsp.ProductID AND spsp.DeptID=0
WHERE stsd.CompanyCD=@CompanyCD AND stsd.SendNo=@SendNo ");
SqlParameter[] Paras = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@SendNo",SqlDbType.VarChar),
new SqlParameter("@DeptID",SqlDbType.Int)};
Paras[0].Value = model.CompanyCD;
Paras[1].Value = model.SendNo;
Paras[2].Value = DeptID;
DataTable dt = SqlHelper.ExecuteSql(sbSql.ToString(), Paras);
if (dt != null && dt.Rows.Count > 0)
return dt;
else
return GetSubDeliverySendDetailPrint(model, 0);
}
public static DataTable GetSubDeliverySendDetail(SubDeliverySendDetail model, int DeptID)
{
StringBuilder sbSql = new StringBuilder();
sbSql.Append(@" SELECT sdsd.*
,CASE WHEN spsp.ID IS NULL THEN spsp2.ID ELSE spsp.ID END AS ID
,CASE WHEN spsp.DeptID IS NULL THEN spsp2.DeptID ELSE spsp.DeptID END AS DeptID
,CASE WHEN spsp.SendPrice IS NULL THEN spsp2.SendPrice ELSE spsp.SendPrice END AS SendPrice
,CASE WHEN spsp.SendPriceTax IS NULL THEN spsp2.SendPriceTax ELSE spsp.SendPriceTax END AS SendPriceTax
,CASE WHEN spsp.SendTax IS NULL THEN spsp2.SendTax ELSE spsp.SendTax END AS SendTax
,CASE WHEN spsp.Discount IS NULL THEN spsp2.Discount ELSE spsp.Discount END AS Discount
,CASE WHEN spsp.Creator IS NULL THEN spsp2.Creator ELSE spsp.Creator END AS Creator
,CASE WHEN spsp.CreateDate IS NULL THEN spsp2.CreateDate ELSE spsp.CreateDate END AS CreateDate
,CASE WHEN spsp.ModifiedDate IS NULL THEN spsp2.ModifiedDate ELSE spsp.ModifiedDate END AS ModifiedDate
,CASE WHEN spsp.ModifiedUserID IS NULL THEN spsp2.ModifiedUserID ELSE spsp.ModifiedUserID END AS ModifiedUserID
,pi1.ProdNo,pi1.ProductName,pi1.Specification,pi1.UnitID,pi1.MinusIs,pi1.IsBatchNo
,(SELECT ui.CodeName from officedba.CodeUnitType as ui where ui.ID=pi1.UnitID) AS UnitName
, (select (isnull(sp.ProductCount,0)+isnull(sp.InCount,0)+isnull(sp.RoadCount,0)-isnull(sp.OutCount,0)-isnull(sp.OrderCount,0))
from officedba.StorageProduct sp
WHERE sdsd.CompanyCD=sp.CompanyCD AND sdsd.StorageID=sp.StorageID AND sdsd.ProductID=sp.ProductID AND isnull(sdsd.BatchNo,'')=isnull(sp.BatchNO,'') ) AS UseCount
,CASE WHEN pi1.IsBatchNo='1' THEN sds.BatchNo ELSE '' END AS SendBatchNo
FROM officedba.SubDeliverySendDetail sdsd
INNER JOIN officedba.SubDeliverySend sds ON sdsd.CompanyCD=sds.CompanyCD AND sdsd.SendNo=sds.SendNo
INNER JOIN officedba.ProductInfo pi1 ON sdsd.CompanyCD=pi1.CompanyCD AND sdsd.ProductID=pi1.ID
LEFT JOIN officedba.SubProductSendPrice spsp ON sdsd.CompanyCD=spsp.CompanyCD AND sdsd.ProductID=spsp.ProductID AND spsp.DeptID=@DeptID
LEFT JOIN officedba.SubProductSendPrice spsp2 ON sdsd.CompanyCD=spsp2.CompanyCD AND sdsd.ProductID=spsp2.ProductID AND spsp2.DeptID=0
where sdsd.CompanyCD=@CompanyCD AND sdsd.SendNo=@SendNo ");
SqlParameter[] Paras = {
new SqlParameter("@CompanyCD",SqlDbType.VarChar),
new SqlParameter("@SendNo",SqlDbType.VarChar),
new SqlParameter("@DeptID",SqlDbType.Int)};
Paras[0].Value = model.CompanyCD;
Paras[1].Value = model.SendNo;
Paras[2].Value = DeptID;
DataTable dt = SqlHelper.ExecuteSql(sbSql.ToString(), Paras);
return dt;
}
/// <summary>
/// 保存明细信息
/// </summary>
/// <param name="m"></param>
/// <returns></returns>
private static SqlCommand SaveDetail(SubDeliverySendDetail m)
{
StringBuilder strSubSql = new StringBuilder();
strSubSql.Append("insert into officedba.SubDeliverySendDetail(");
strSubSql.Append("CompanyCD,SendNo,SortNo,ProductID,StorageID,SendCount,SendPrice,SendPriceTotal,UsedUnitCount,UnitID,ExRate,BatchNo)");
strSubSql.Append(" values (");
strSubSql.Append("@CompanyCD,@SendNo,@SortNo,@ProductID,@StorageID,@SendCount,@SendPrice,@SendPriceTotal,@UsedUnitCount,@UnitID,@ExRate,@BatchNo)");
strSubSql.Append(";select @@IDENTITY");
SqlParameter[] subParameters = {
new SqlParameter("@CompanyCD", SqlDbType.VarChar,8),
new SqlParameter("@SendNo", SqlDbType.VarChar,50),
new SqlParameter("@SortNo", SqlDbType.Int,4),
new SqlParameter("@ProductID", SqlDbType.Int,4),
new SqlParameter("@StorageID", SqlDbType.Int,4),
new SqlParameter("@SendCount", SqlDbType.Decimal,9),
new SqlParameter("@SendPrice", SqlDbType.Decimal,9),
new SqlParameter("@SendPriceTotal", SqlDbType.Decimal,9)};
subParameters[0].Value = m.CompanyCD;
subParameters[1].Value = m.SendNo;
subParameters[2].Value = m.SortNo;
subParameters[3].Value = m.ProductID;
subParameters[4].Value = m.StorageID;
subParameters[5].Value = m.SendCount;
subParameters[6].Value = m.SendPrice;
subParameters[7].Value = m.SendPriceTotal;
SqlCommand SqlSubCmd = new SqlCommand { CommandText = strSubSql.ToString() };
SqlSubCmd.Parameters.AddRange(subParameters);
if (!string.IsNullOrEmpty(m.BatchNo))
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@BatchNo", m.BatchNo));
}
else
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@BatchNo", DBNull.Value));
}
if (m.UnitID.HasValue)
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UnitID", m.UnitID.Value));
}
else
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UnitID", DBNull.Value));
}
if (m.UsedUnitCount.HasValue)
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", m.UsedUnitCount.Value));
}
else
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@UsedUnitCount", DBNull.Value));
}
if (m.ExRate.HasValue)
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@ExRate", m.ExRate.Value));
}
else
{
SqlSubCmd.Parameters.Add(SqlHelper.GetParameter("@ExRate", DBNull.Value));
}
return SqlSubCmd;
}
public static string GetProductUseCount(SubDeliverySendDetail model)
{
return XBase.Data.Office.LogisticsDistributionManager.SubDeliverySendSaveDBHelper.GetProductUseCount(model);
}
/*打印使用*/
public static DataTable GetSubDeliverySendDetailPrint(SubDeliverySendDetail model, int DeptID)
{
return XBase.Data.Office.LogisticsDistributionManager.SubDeliverySendSaveDBHelper.GetSubDeliverySendDetailPrint(model, DeptID);
}