/// <summary>保存公司具体年份对应供应商采购合同量
/// </summary>
/// <param name="procurementCompactQuantityInfo">供应商采购合同量</param>
/// <returns>Return：true/false</returns>
public bool SaveProcurementCompactQuantity(ProcurementCompactQuantityInfo procurementCompactQuantityInfo)
{
const string SQL = @"
IF NOT EXISTS(SELECT CompactMoney FROM [ProcurementCompactQuantity] WHERE CompanyId=@CompanyId AND DateYear=@DateYear)
INSERT INTO [ProcurementCompactQuantity](CompanyId,DateYear,CompactMoney) VALUES(@CompanyId,@DateYear,@CompactMoney);
ELSE
UPDATE [ProcurementCompactQuantity] SET CompactMoney=@CompactMoney WHERE CompanyId=@CompanyId AND DateYear=@DateYear;";
var parms = new[]
{
new SqlParameter("@CompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@DateYear", SqlDbType.Int),
new SqlParameter("@CompactMoney", SqlDbType.Decimal)
};
try
{
parms[0].Value = procurementCompactQuantityInfo.CompanyId;
parms[1].Value = procurementCompactQuantityInfo.DateYear;
parms[2].Value = procurementCompactQuantityInfo.CompactMoney;
return(SqlHelper.ExecuteNonQuery(GlobalConfig.ERP_DB_NAME, false, SQL, parms) > 0);
}
catch (Exception exp)
{
SAL.LogCenter.LogService.LogError(string.Format("采购合同量保存失败,CompanyId={0},DateYear={1},CompactMoney={2}", procurementCompactQuantityInfo.CompanyId, procurementCompactQuantityInfo.DateYear, procurementCompactQuantityInfo.CompactMoney), "仓库管理", exp);
return(false);
}
}
/// <summary>保存
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void OnTextChanged_CompactMoney(object sender, EventArgs e)
{
try
{
var tbCompactMoney = (TextBox)sender;
var dataItem = (GridDataItem)tbCompactMoney.Parent.Parent;
var companyId = dataItem.GetDataKeyValue("CompanyId");
var compactMoney = WebControl.NumberRecovery(tbCompactMoney.Text);
//正则表达式
if (Regex.IsMatch(compactMoney, @"^(([1-9]\d{0,9})|0)(\.\d{1,2})?$"))
{
var info = new ProcurementCompactQuantityInfo
{
CompanyId = new Guid(companyId.ToString()),
DateYear = DateYear,
CompactMoney = Convert.ToDecimal(compactMoney)
};
var result = _procurementCompactQuantityDal.SaveProcurementCompactQuantity(info);
if (!result)
{
RAM.Alert("系统提示：数据保存异常，请尝试刷新后继续操作！");
}
else
{
RadGridProcurementCompactQuantity.Rebind();
}
}
else
{
var companyInfo = _companyCussent.GetCompanyCussent(new Guid(companyId.ToString()));
var companyName = string.Empty;
if (companyInfo != null)
{
companyName = companyInfo.CompanyName;
}
RAM.Alert(string.Format("系统提示：{0}输入金额格式不正确！(不能设置负值)", companyName));
}
}
catch (Exception)
{
RAM.Alert("系统提示：保存异常，请尝试刷新后继续操作！");
}
}
/// <summary>获取采购合同量集合
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
private IList <ProcurementCompactQuantityInfo> GetProcurementCompactQuantityList(string sql, SqlParameter[] commandParameters)
{
IList <ProcurementCompactQuantityInfo> list = new List <ProcurementCompactQuantityInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, sql, commandParameters))
{
while (rdr.Read())
{
var info = new ProcurementCompactQuantityInfo
{
CompanyId = new Guid(rdr["CompanyID"].ToString()),
CompanyName = rdr["CompanyName"].ToString(),
CompactMoney = Convert.ToDecimal(rdr["CompactMoney"]),
TheMonthProcurementMoney = Convert.ToDecimal(Convert.ToDecimal(rdr["TheMonthProcurementMoney"]).ToString("#0.00")),
FinishedCompactMoney = Convert.ToDecimal(Convert.ToDecimal(rdr["FinishedCompactMoney"]).ToString("#0.00")),
SurplusCompactMoney = Convert.ToDecimal(Convert.ToDecimal(rdr["SurplusCompactMoney"]).ToString("#0.00")),
};
list.Add(info);
}
}
return(list);
}
/// <summary>根据年份获取供应商采购合同量
/// </summary>
/// <param name="year">年份</param>
/// <returns>Return：供应商采购合同量集合</returns>
public IList <ProcurementCompactQuantityInfo> GetProcurementCompactQuantityList(int year)
{
string sql = @"
DECLARE @time1 SMALLDATETIME ,@time2 SMALLDATETIME
SET @time1= DATEADD(MM,DATEDIFF(MM,0,GETDATE()),0)
SET @time2= DATEADD(MS,-3,DATEADD(MM, DATEDIFF(MM,0,GETDATE())+1, 0))
SELECT
t1.CompanyId,t1.CompanyName,
ISNULL(t2.CompactMoney,0) AS CompactMoney,
(ISNULL(t3.TheMonthProcurementMoney,0)-ISNULL(SR1.TheMonthRetStockOutMoney,0)) AS TheMonthProcurementMoney,
(ISNULL(t4.FinishedCompactMoney,0)-ISNULL(SR2.TheYearRetStockOutMoney,0)) AS FinishedCompactMoney,
(ISNULL(CompactMoney,0)-ISNULL(FinishedCompactMoney,0)) AS SurplusCompactMoney
FROM lmShop_CompanyCussent t1
LEFT JOIN
(
SELECT CompanyId,CompactMoney FROM ProcurementCompactQuantity
WHERE DateYear =@DateYear
) t2
ON t1.CompanyId = t2.CompanyId
-----------获取公司供应商当月采购金额-----------------------------------
LEFT JOIN (
SELECT CompanyID,SUM(RealityQuantity*Price) AS TheMonthProcurementMoney
FROM lmShop_PurchasingDetail pd
INNER JOIN
(
SELECT PurchasingID FROM [lmShop_Purchasing] WHERE PurchasingState BETWEEN 1 AND 4
AND StartTime >= @time1
AND StartTime < @time2
)
AS PS ON pd.PurchasingID=ps.PurchasingID
GROUP BY CompanyID
) t3 on t1.CompanyId = t3.CompanyID
LEFT JOIN (
SELECT ThirdCompanyID,SUM(ABS(AccountReceivable)) AS TheMonthRetStockOutMoney FROM StorageRecord WHERE StockType=" + (int)StorageRecordType.BuyStockOut + @"
AND StockState=" + (int)StorageRecordState.Finished + @"
AND DateCreated >= @time1
AND DateCreated <@time2
GROUP BY ThirdCompanyID
)
SR1 ON SR1.ThirdCompanyID=t1.CompanyId
-------------END----------------------------------------------------------
-----------获取公司供应商当年已完成签约金额-------------------------------
LEFT JOIN (
SELECT CompanyID,SUM(RealityQuantity*Price) AS FinishedCompactMoney
FROM lmShop_PurchasingDetail pd
INNER JOIN
(
SELECT PurchasingID FROM [lmShop_Purchasing] WHERE PurchasingState BETWEEN 1 AND 4
AND StartTime >=@StartTime
AND StartTime < @EndTime
)
AS PS ON pd.PurchasingID=ps.PurchasingID
GROUP BY CompanyID
)t4 on t1.CompanyId = t4.CompanyID
LEFT JOIN (
SELECT ThirdCompanyID,SUM(ABS(AccountReceivable)) AS TheYearRetStockOutMoney FROM StorageRecord WHERE StockType=" + (int)StorageRecordType.BuyStockOut + @"
AND StockState=" + (int)StorageRecordState.Finished + @"
AND DateCreated >= @StartTime
AND DateCreated <@EndTime
GROUP BY ThirdCompanyID
)
SR2 ON SR2.ThirdCompanyID=t1.CompanyId
WHERE T1.CompanyType=1--供应商
AND T1.[State]=1--启用
-------------END----------------------------------------------------------";
var startTime = Convert.ToDateTime(string.Format("{0}-01-01 00:00:00", year));
var endTime = Convert.ToDateTime(string.Format("{0}-01-01 00:00:00", year + 1));
var parms = new[] {
new SqlParameter("@DateYear", SqlDbType.Int)
{
Value = year
},
new SqlParameter("@StartTime", SqlDbType.DateTime)
{
Value = startTime
},
new SqlParameter("@EndTime", SqlDbType.DateTime)
{
Value = endTime
}
};
IList <ProcurementCompactQuantityInfo> list = new List <ProcurementCompactQuantityInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, sql, parms))
{
while (rdr.Read())
{
var info = new ProcurementCompactQuantityInfo
{
CompanyId = new Guid(rdr["CompanyID"].ToString()),
CompanyName = rdr["CompanyName"].ToString(),
CompactMoney = Convert.ToDecimal(rdr["CompactMoney"]),
TheMonthProcurementMoney = Convert.ToDecimal(Convert.ToDecimal(rdr["TheMonthProcurementMoney"]).ToString("#0.00")),
FinishedCompactMoney = Convert.ToDecimal(Convert.ToDecimal(rdr["FinishedCompactMoney"]).ToString("#0.00")),
SurplusCompactMoney = Convert.ToDecimal(Convert.ToDecimal(rdr["SurplusCompactMoney"]).ToString("#0.00")),
};
list.Add(info);
}
}
return(list);
}
