/// <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()); } }