/// <summary>获取所有公司采购索取额度
/// </summary>
/// <param name="dateYear">年份</param>
/// <param name="dateMonth">月份</param>
/// <returns>Return:采购索票额度集合</returns>
public IList <ProcurementTicketLimitInfo> GetProcurementTicketLimitList(int dateYear, int dateMonth)
{
const string SQL = @" SELECT SUM(TakerTicketLimit) AS TotalTakerTicketLimit,FilialeId FROM [ProcurementTicketLimit]
WHERE DateYear=@DateYear AND DateMonth=@DateMonth
GROUP BY FilialeId";
var parms = new[] {
new SqlParameter("@DateYear", SqlDbType.Int)
{
Value = dateYear
},
new SqlParameter("@DateMonth", SqlDbType.Int)
{
Value = dateMonth
}
};
IList <ProcurementTicketLimitInfo> list = new List <ProcurementTicketLimitInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL, parms))
{
while (rdr.Read())
{
var info = new ProcurementTicketLimitInfo
{
FilialeId = new Guid(rdr["FilialeId"].ToString()),
TotalTakerTicketLimit = Convert.ToDecimal(rdr["TotalTakerTicketLimit"])
};
list.Add(info);
}
}
return(list);
}
/// <summary>保存公司具体年份和月份的采购索票额度
/// </summary>
/// <param name="procurementTicketLimitInfo">采购索票额度</param>
/// <returns>Return：true/false</returns>
public bool SaveProcurementTicketLimit(ProcurementTicketLimitInfo procurementTicketLimitInfo)
{
const string SQL = @"
IF NOT EXISTS(SELECT TakerTicketLimit FROM [ProcurementTicketLimit] WHERE FilialeId=@FilialeId AND CompanyId=@CompanyId AND DateYear=@DateYear AND DateMonth=@DateMonth)
INSERT INTO [ProcurementTicketLimit](FilialeId,CompanyId,DateYear,DateMonth,TakerTicketLimit) VALUES(@FilialeId,@CompanyId,@DateYear,@DateMonth,@TakerTicketLimit);
ELSE
UPDATE [ProcurementTicketLimit] SET TakerTicketLimit=@TakerTicketLimit WHERE FilialeId=@FilialeId AND CompanyId=@CompanyId AND DateYear=@DateYear AND DateMonth=@DateMonth;";
var parms = new[]
{
new SqlParameter("@FilialeId", SqlDbType.UniqueIdentifier),
new SqlParameter("@CompanyId", SqlDbType.UniqueIdentifier),
new SqlParameter("@DateYear", SqlDbType.Int),
new SqlParameter("@DateMonth", SqlDbType.Int),
new SqlParameter("@TakerTicketLimit", SqlDbType.Decimal)
};
try
{
parms[0].Value = procurementTicketLimitInfo.FilialeId;
parms[1].Value = procurementTicketLimitInfo.CompanyId;
parms[2].Value = procurementTicketLimitInfo.DateYear;
parms[3].Value = procurementTicketLimitInfo.DateMonth;
parms[4].Value = procurementTicketLimitInfo.TakerTicketLimit;
return(SqlHelper.ExecuteNonQuery(GlobalConfig.ERP_DB_NAME, false, SQL, parms) > 0);
}
catch (Exception exp)
{
SAL.LogCenter.LogService.LogError(string.Format("采购索票额度保存失败,FilialeId={0},CompanyId={1},DateYear={2},DateMonth={3},TakerTicketLimit={4}", procurementTicketLimitInfo.FilialeId, procurementTicketLimitInfo.CompanyId, procurementTicketLimitInfo.DateYear, procurementTicketLimitInfo.DateMonth, procurementTicketLimitInfo.TakerTicketLimit), "仓库管理", exp);
return(false);
}
}
protected void RadProcurementTicketLimit_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
var filialeList = CacheCollection.Filiale.GetHeadList();
IList<ProcurementTicketLimitInfo> list1 = _procurementTicketLimitDal.GetProcurementTicketLimitList(DateYear, DateMonth);
if (list1.Count > 0)
{
IList<ProcurementTicketLimitInfo> list2 = new List<ProcurementTicketLimitInfo>();
foreach (var filialeInfo in filialeList)
{
var info = new ProcurementTicketLimitInfo();
var item = list1.FirstOrDefault(ent => ent.FilialeId == filialeInfo.ID);
if (item != null)
{
info.FilialeId = filialeInfo.ID;
info.FilialeName = filialeInfo.Name;
info.TotalTakerTicketLimit = item.TotalTakerTicketLimit;
info.DateYear = DateYear;
info.DateMonth = DateMonth;
}
else
{
info.FilialeId = filialeInfo.ID;
info.FilialeName = filialeInfo.Name;
info.TotalTakerTicketLimit = Convert.ToDecimal("0.00");
info.DateYear = DateYear;
info.DateMonth = DateMonth;
}
list2.Add(info);
}
RadGridProcurementTicketLimit.DataSource = list2.OrderByDescending(act => act.TotalTakerTicketLimit);
}
else
{
IList<ProcurementTicketLimitInfo> list3 = filialeList.Select(filialeInfo => new ProcurementTicketLimitInfo
{
FilialeId = filialeInfo.ID,
FilialeName = filialeInfo.Name,
TotalTakerTicketLimit = Convert.ToDecimal("0.00"),
DateYear = DateYear,
DateMonth = DateMonth
}).ToList();
RadGridProcurementTicketLimit.DataSource = list3;
}
}
/// <summary>获取当前年月供应商设置的采购索取额度
/// </summary>
/// <param name="companyId">供应商ID</param>
/// <param name="dateYear">年份</param>
/// <param name="dateMonth">月份 </param>
/// <returns></returns>
public IList <ProcurementTicketLimitInfo> GetProcurementTicketLimitDetailByCompanyId(Guid companyId, int dateYear, int dateMonth)
{
const string SQL = @"
SELECT
[FilialeId]
,[CompanyId]
,[TakerTicketLimit]
,ISNULL([AlreadyCompleteLimit],0) AS AlreadyCompleteLimit
,[TakerTicketLimit]-ISNULL([AlreadyCompleteLimit],0) AS SurplusLimit
FROM [ProcurementTicketLimit]
WHERE DateYear=@DateYear AND DateMonth=@DateMonth
AND CompanyId =@CompanyId";
var parms = new[] {
new SqlParameter("@CompanyId", SqlDbType.UniqueIdentifier)
{
Value = companyId
},
new SqlParameter("@DateYear", SqlDbType.Int)
{
Value = dateYear
},
new SqlParameter("@DateMonth", SqlDbType.Int)
{
Value = dateMonth
}
};
IList <ProcurementTicketLimitInfo> list = new List <ProcurementTicketLimitInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL, parms))
{
while (rdr.Read())
{
var info = new ProcurementTicketLimitInfo
{
FilialeId = new Guid(rdr["FilialeId"].ToString()),
CompanyId = new Guid(rdr["CompanyId"].ToString()),
TakerTicketLimit = Convert.ToDecimal(rdr["TakerTicketLimit"]),
AlreadyCompleteLimit = Convert.ToDecimal(rdr["AlreadyCompleteLimit"])
};
list.Add(info);
}
}
return(list);
}
/// <summary>获取具体供应商对应采购索取额度
/// </summary>
/// <param name="filialeId">公司ID</param>
/// <param name="companyIds">供应商Ids</param>
/// <param name="dateYear">年份</param>
/// <param name="dateMonth">月份</param>
/// <returns></returns>
public IList <ProcurementTicketLimitInfo> GetProcurementTicketLimitDetailByCompanyIds(Guid filialeId, string companyIds, int dateYear, int dateMonth)
{
const string SQL = @"
SELECT CompanyId,TakerTicketLimit AS ElseCompanyIdTakerTicketLimit,FilialeId AS ElseFilialeId FROM [ProcurementTicketLimit]
WHERE DateYear=@DateYear AND DateMonth=@DateMonth
AND CompanyId IN ({0})
AND FilialeId!=@FilialeId";
var parms = new[] {
new SqlParameter("@FilialeId", SqlDbType.UniqueIdentifier)
{
Value = filialeId
},
new SqlParameter("@DateYear", SqlDbType.Int)
{
Value = dateYear
},
new SqlParameter("@DateMonth", SqlDbType.Int)
{
Value = dateMonth
}
};
IList <ProcurementTicketLimitInfo> list = new List <ProcurementTicketLimitInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, string.Format(SQL, companyIds), parms))
{
while (rdr.Read())
{
var info = new ProcurementTicketLimitInfo
{
CompanyId = new Guid(rdr["CompanyId"].ToString()),
ElseFilialeId = new Guid(rdr["ElseFilialeId"].ToString()),
ElseCompanyIdTakerTicketLimit = Convert.ToDecimal(rdr["ElseCompanyIdTakerTicketLimit"]),
};
list.Add(info);
}
}
return(list);
}
