/// <summary>
/// 供应商每月采购出入库总金额
/// </summary>
/// <param name="year"></param>
/// <param name="filialeId"></param>
/// <param name="companyName"></param>
/// <returns></returns>
public IList <SupplierPaymentsReportInfo> SelectStockReportsGroupByCompanyId(int year, Guid filialeId, string companyName)
{
const string SQL = @"SELECT S.CompanyId,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=1 THEN TotalAmount ELSE 0 END),0) AS January,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=2 THEN TotalAmount ELSE 0 END),0) AS February,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=3 THEN TotalAmount ELSE 0 END),0) AS March,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=4 THEN TotalAmount ELSE 0 END),0) AS April,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=5 THEN TotalAmount ELSE 0 END),0) AS May,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=6 THEN TotalAmount ELSE 0 END),0) AS June,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=7 THEN TotalAmount ELSE 0 END),0) AS July,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=8 THEN TotalAmount ELSE 0 END),0) AS August,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=9 THEN TotalAmount ELSE 0 END),0) AS September,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=10 THEN TotalAmount ELSE 0 END),0) AS October,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=11 THEN TotalAmount ELSE 0 END),0) AS November,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=12 THEN TotalAmount ELSE 0 END),0) AS December
FROM SupplierPurchasingRecord AS S WITH(NOLOCK)
WHERE YEAR(S.DayTime)={0} {1}
GROUP BY S.CompanyId";
string filieleStr = string.Empty;
if (filialeId != Guid.Empty)
{
filieleStr = string.Format(" AND S.FilialeId='{0}' ", filialeId);
}
var newSQL = string.Format(SQL, year, filieleStr);
using (var db = DatabaseFactory.CreateRdb())
{
return(db.Select <SupplierPaymentsReportInfo>(true, newSQL).ToList());
}
}
/// <summary>
/// 公司每月对应的采购出入库总金额
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public IList <SupplierPaymentsReportInfo> SelectStockReprotsGroupByFilialeId(int year)
{
const string SQL = @"SELECT FilialeId,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=1 THEN TotalAmount ELSE 0 END),0) AS January,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=2 THEN TotalAmount ELSE 0 END),0) AS February,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=3 THEN TotalAmount ELSE 0 END),0) AS March,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=4 THEN TotalAmount ELSE 0 END),0) AS April,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=5 THEN TotalAmount ELSE 0 END),0) AS May,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=6 THEN TotalAmount ELSE 0 END),0) AS June,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=7 THEN TotalAmount ELSE 0 END),0) AS July,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=8 THEN TotalAmount ELSE 0 END),0) AS August,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=9 THEN TotalAmount ELSE 0 END),0) AS September,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=10 THEN TotalAmount ELSE 0 END),0) AS October,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=11 THEN TotalAmount ELSE 0 END),0) AS November,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=12 THEN TotalAmount ELSE 0 END),0) AS December
FROM SupplierPurchasingRecord WITH(NOLOCK)
WHERE YEAR(DayTime)={0}
GROUP BY FilialeId";
var newSQL = string.Format(SQL, year);
using (var db = DatabaseFactory.CreateRdb())
{
return(db.Select <SupplierPaymentsReportInfo>(true, newSQL).ToList());
}
}
/// <summary>
/// 删除特定时间内的临时数据
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
public bool DeleteData(int year, int month)
{
const string SQL = @"DELETE GoodsGrossProfitRecord WHERE YEAR(DayTime)={0} AND Month(DayTime)={1} ";
using (var db = DatabaseFactory.CreateRdb())
{
return(db.Execute(false, string.Format(SQL, year, month)));
}
}
/// <summary>
/// 判断当前月份数据是否存在
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
/// <returns></returns>
public bool CurrentIsExist(DateTime startTime, DateTime endTime)
{
const string SQL = @"SELECT COUNT(ID) FROM GoodsGrossProfitRecord WITH(NOLOCK) WHERE DayTime BETWEEN '{0}' AND '{1}'";
using (var db = DatabaseFactory.CreateRdb())
{
return(db.GetValue <int>(true, string.Format(SQL, startTime, endTime)) > 0);
}
}
/// <summary>
/// 判断当月往来帐是否记录 （某天）
/// </summary>
/// <param name="dayTime"></param>
/// <returns></returns>
public bool IsExistsRecent(DateTime dayTime)
{
const string SQL = @"SELECT COUNT(0) FROM TempSupplierReckoningRecord WITH(NOLOCK) WHERE DayTime='{0}'";
using (var db = DatabaseFactory.CreateRdb())
{
return(db.GetValue <int>(true, string.Format(SQL, dayTime)) > 0);
}
}
/// <summary>
/// 判断某月份是否已存档
/// </summary>
/// <param name="dayTime"></param>
/// <returns></returns>
public bool IsExists(DateTime dayTime)
{
const string SQL = @"SELECT COUNT(*) FROM SupplierSaleRecord WHERE DayTime='{0}'";
using (var db = DatabaseFactory.CreateRdb())
{
return(db.GetValue <int>(true, string.Format(SQL, dayTime)) > 0);
}
}
/// <summary>
/// 是否存在商品毛利数据
/// </summary>
/// <param name="dayTime"></param>
/// <returns></returns>
public bool Exists(DateTime dayTime)
{
const string SQL = @"SELECT COUNT(ID) FROM GoodsGrossProfitRecord WITH(NOLOCK) WHERE DayTime=@DayTime";
var parms = new[]
{
new Parameter("@DayTime", dayTime)
};
using (var db = DatabaseFactory.CreateRdb())
{
return(db.GetValue <int>(true, SQL, parms) > 0);
}
}
/// <summary>
/// 是否存在公司毛利记录数据明细
/// </summary>
/// <param name="dayTime"></param>
/// <returns></returns>
public bool Exists(DateTime dayTime)
{
const string SQL = "SELECT COUNT(0) FROM CompanyGrossProfitRecordDetail WHERE DayTime=@DayTime";
var parms = new[]
{
new Parameter("@DayTime", dayTime)
};
using (var db = DatabaseFactory.CreateRdb())
{
return(db.GetValue <int>(true, SQL, parms) > 0);
}
}
/// <summary>
/// 删除特定时间内的临时数据
/// </summary>
/// <param name="year"></param>
/// <param name="month"></param>
/// <returns></returns>
public bool DeleteData(int year, int month)
{
const string SQL = @"DELETE CompanyGrossProfitRecord WHERE YEAR(DayTime)=@YEAR AND Month(DayTime)=@Month ";
try
{
var parms = new[]
{
new Parameter("@YEAR", year),
new Parameter("@Month", month)
};
using (var db = DatabaseFactory.CreateRdb())
{
db.Execute(false, SQL, parms);
return(true);
}
}
catch
{
return(false);
}
}
/// <summary>
/// 应付款查询，根据公司和查询供应商的应付款明细
/// </summary>
/// <param name="year"></param>
/// <param name="filialeId"></param>
/// <param name="companyName"></param>
/// <returns></returns>
public IList <SupplierPaymentsReportInfo> SelectPaymentsReportsGroupByCompanyId(int year,
Guid filialeId, string companyName)
{
const string SQL = @"SELECT '{0}' AS FilialeId,S.CompanyId,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=1 THEN TotalAmount ELSE 0 END),0) AS January,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=2 THEN TotalAmount ELSE 0 END),0) AS February,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=3 THEN TotalAmount ELSE 0 END),0) AS March,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=4 THEN TotalAmount ELSE 0 END),0) AS April,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=5 THEN TotalAmount ELSE 0 END),0) AS May,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=6 THEN TotalAmount ELSE 0 END),0) AS June,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=7 THEN TotalAmount ELSE 0 END),0) AS July,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=8 THEN TotalAmount ELSE 0 END),0) AS August,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=9 THEN TotalAmount ELSE 0 END),0) AS September,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=10 THEN TotalAmount ELSE 0 END),0) AS October,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=11 THEN TotalAmount ELSE 0 END),0) AS November,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=12 THEN TotalAmount ELSE 0 END),0) AS December,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=1 THEN TotalNoPayed ELSE 0 END),0) AS January1,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=2 THEN TotalNoPayed ELSE 0 END),0) AS February2,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=3 THEN TotalNoPayed ELSE 0 END),0) AS March3,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=4 THEN TotalNoPayed ELSE 0 END),0) AS April4,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=5 THEN TotalNoPayed ELSE 0 END),0) AS May5,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=6 THEN TotalNoPayed ELSE 0 END),0) AS June6,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=7 THEN TotalNoPayed ELSE 0 END),0) AS July7,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=8 THEN TotalNoPayed ELSE 0 END),0) AS August8,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=9 THEN TotalNoPayed ELSE 0 END),0) AS September9,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=10 THEN TotalNoPayed ELSE 0 END),0) AS October10,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=11 THEN TotalNoPayed ELSE 0 END),0) AS November11,
ISNULL(SUM(CASE WHEN MONTH(DayTime)=12 THEN TotalNoPayed ELSE 0 END),0) AS December12
FROM SupplierPaymentsRecord AS S WITH(NOLOCK)
WHERE FilialeId='{0}' AND YEAR(DayTime)={1}
GROUP BY S.CompanyId";
var newSQL = string.Format(SQL, filialeId, year);
using (var db = DatabaseFactory.CreateRdb())
{
return(db.Select <SupplierPaymentsReportInfo>(true, newSQL).ToList());
}
}
/// <summary>
/// 供应商销量页面显示数据(对应公司的销量) PASS
/// </summary>
/// <param name="year"></param>
/// <returns></returns>
public IList <SupplierSaleReportInfo> SelectSupplierSaleReportInfos(int year)
{
const string SQL = @"
SELECT CompanyID,ABS(sum(case when Month(DayTime)=1 then TotalSettlePrice else 0 end)) as January,
ABS(sum(case when Month(DayTime)=2 then TotalSettlePrice else 0 end)) as February,
ABS(sum(case when Month(DayTime)=3 then TotalSettlePrice else 0 end)) as March,
ABS(sum(case when Month(DayTime)=4 then TotalSettlePrice else 0 end))as April,
ABS(sum(case when Month(DayTime)=5 then TotalSettlePrice else 0 end)) as May,
ABS(sum(case when Month(DayTime)=6 then TotalSettlePrice else 0 end)) as June,
ABS(sum(case when Month(DayTime)=7 then TotalSettlePrice else 0 end)) as July,
ABS(sum(case when Month(DayTime)=8 then TotalSettlePrice else 0 end)) as August,
ABS(sum(case when Month(DayTime)=9 then TotalSettlePrice else 0 end)) as September,
ABS(sum(case when Month(DayTime)=10 then TotalSettlePrice else 0 END)) as October,
ABS(sum(case when Month(DayTime)=11 then TotalSettlePrice else 0 end)) as November,
ABS(sum(case when Month(DayTime)=12 then TotalSettlePrice else 0 end)) as December FROM SupplierSaleRecord
WHERE YEAR(DayTime)={0}
GROUP BY CompanyID ";
using (var db = DatabaseFactory.CreateRdb())
{
return(db.Select <SupplierSaleReportInfo>(true, string.Format(SQL, year)).ToList());
}
}