public PaymentNoticeInfo GetPayNoticInfoByPayid(Guid payid)
{
PaymentNoticeInfo pninfo = null;
const string SQL_PN_SELECT_BY_PAYID = @"Select [PayId],[OrderId],[OrderNo],[PayBank],[PayPrince],[PayTime],[PayName],[PayDes],[PayState],SaleFilialeId,SalePlatformId From lmShop_PaymentNotice Where PayId=@PayId ";
var parm = new SqlParameter(PAYID, SqlDbType.UniqueIdentifier)
{
Value = payid
};
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL_PN_SELECT_BY_PAYID, parm))
{
if (rdr.Read())
{
pninfo = new PaymentNoticeInfo
{
PayId = SqlRead.GetGuid(rdr, "PayId"),
OrderId = SqlRead.GetGuid(rdr, "OrderId"),
OrderNo = SqlRead.GetString(rdr, "OrderNo"),
PayBank = SqlRead.GetString(rdr, "PayBank"),
PayPrince = SqlRead.GetDecimal(rdr, "PayPrince"),
PayTime = SqlRead.GetDateTime(rdr, "PayTime"),
PayName = SqlRead.GetString(rdr, "PayName"),
PayDes = SqlRead.GetString(rdr, "PayDes"),
PayState = SqlRead.GetInt(rdr, "PayState"),
SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"),
SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId")
};
}
}
return(pninfo);
}
private PurchaseSetInfo ReaderPurchaseSet(IDataReader dr)
{
var info = new PurchaseSetInfo
{
GoodsId = SqlRead.GetGuid(dr, "GoodsId"),
GoodsName = SqlRead.GetString(dr, "GoodsName"),
WarehouseId = SqlRead.GetGuid(dr, "WarehouseId"),
CompanyId = SqlRead.GetGuid(dr, "CompanyId"),
PurchasePrice = SqlRead.GetDecimal(dr, "PurchasePrice"),
PersonResponsible = SqlRead.GetGuid(dr, "PersonResponsible"),
Memo = SqlRead.GetString(dr, "Memo"),
PromotionId = SqlRead.GetGuid(dr, "PromotionId"),
IsStockUp = SqlRead.GetBoolean(dr, "IsStockUp"),
PurchaseGroupId = SqlRead.GetGuid(dr, "PurchaseGroupId"),
FilingForm = SqlRead.GetInt(dr, "FilingForm"),
StockUpDay = SqlRead.GetInt(dr, "StockUpDay"),
FirstWeek = SqlRead.GetInt(dr, "FirstWeek"),
SecondWeek = SqlRead.GetInt(dr, "SecondWeek"),
ThirdWeek = SqlRead.GetInt(dr, "ThirdWeek"),
FourthWeek = SqlRead.GetInt(dr, "FourthWeek"),
FilingTrigger = SqlRead.GetInt(dr, "FilingTrigger"),
Insufficient = SqlRead.GetInt(dr, "Insufficient"),
IsDelete = Convert.ToInt32(dr["IsDelete"]),
CompanyName = SqlRead.GetString(dr, "CompanyName"),
HostingFilialeId = SqlRead.GetGuid(dr, "HostingFilialeId")
};
return(info);
}
/// <summary>
/// 查询商品毛利中超过一个自然月或一个自然月以上未完成的数据（例如：当前是7月份，订单时间是5月份的订单在7月1号之前没有完成的数据）
/// </summary>
/// <param name="dayTime">完成时间</param>
/// <returns></returns>
public IList <GoodsGrossProfitInfo> GetGoodsGrossProfitRecordDetailInfosForMoreMonth(DateTime dayTime)
{
var builder = new StringBuilder(@"
SELECT GoodsId,GoodsType,SUM(Quantity) AS Quantity,SaleFilialeId,SalePlatformId,SUM(SalesPriceTotal) AS SalesPriceTotal,SUM(PurchaseCostTotal) AS PurchaseCostTotal,OrderType,(CONVERT(VARCHAR(7),OrderTime,120)+'-01') AS OrderTime
FROM GoodsGrossProfitRecordDetail WITH(NOLOCK) WHERE State=0 AND DayTime>=dateadd(month,2,CONVERT(VARCHAR(7),OrderTime,120)+'-01') ");
builder.AppendFormat(" AND DayTime<'{0}' ", dayTime);
builder.Append(" GROUP BY [GoodsId],[GoodsType],[SaleFilialeId],[SalePlatformId],[OrderType],(CONVERT(VARCHAR(7),OrderTime,120)+'-01')");
IList <GoodsGrossProfitInfo> dataList = new List <GoodsGrossProfitInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_REPORT_DB_NAME, true, builder.ToString()))
{
if (rdr != null)
{
while (rdr.Read())
{
dataList.Add(new GoodsGrossProfitInfo
{
GoodsId = SqlRead.GetGuid(rdr, "GoodsId"),
GoodsType = SqlRead.GetInt(rdr, "GoodsType"),
Quantity = SqlRead.GetInt(rdr, "Quantity"),
SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"),
SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"),
SalesPriceTotal = SqlRead.GetDecimal(rdr, "SalesPriceTotal"),
PurchaseCostTotal = SqlRead.GetDecimal(rdr, "PurchaseCostTotal"),
OrderType = SqlRead.GetInt(rdr, "OrderType"),
DayTime = SqlRead.GetDateTime(rdr, "OrderTime")
});
}
}
}
return(dataList);
}
/// <summary>
/// 查询公司毛利中超过一个自然月或一个自然月以上未完成的数据（例如：当前是7月份，订单时间是5月份的订单在7月1号之前没有完成的数据）
/// </summary>
/// <param name="dayTime">完成时间</param>
/// <returns></returns>
public IList <CompanyGrossProfitRecordInfo> GetCompanyGrossProfitDetailInfosForMoreMonth(DateTime dayTime)
{
var builder = new StringBuilder(@"
SELECT
[SaleFilialeId]
,[SalePlatformId]
,SUM([SalesAmount]) AS [SalesAmount]
,SUM([GoodsAmount]) AS [GoodsAmount]
,SUM([ShipmentIncome]) AS [ShipmentIncome]
,SUM([PromotionsDeductible]) AS [PromotionsDeductible]
,SUM([PointsDeduction]) AS [PointsDeduction]
,SUM([ShipmentCost]) AS [ShipmentCost]
,SUM([PurchaseCosts]) AS [PurchaseCosts]
,SUM([CatCommission]) AS [CatCommission]
,[OrderType]
,CONVERT(VARCHAR(7),[OrderTime],120)+'-01' AS [OrderTime]
FROM [CompanyGrossProfitRecordDetail] WITH(NOLOCK) WHERE State=0 AND DayTime>=dateadd(month,2,CONVERT(VARCHAR(7),OrderTime,120)+'-01') ");
builder.AppendFormat(" AND DayTime<'{0}' ", dayTime);
builder.Append(" GROUP BY [SaleFilialeId],[SalePlatformId],[OrderType],CONVERT(VARCHAR(7),[OrderTime],120)+'-01'");
IList <CompanyGrossProfitRecordInfo> dataList = new List <CompanyGrossProfitRecordInfo>();
using (var rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_REPORT_DB_NAME, true, builder.ToString()))
{
if (rdr != null)
{
while (rdr.Read())
{
dataList.Add(new CompanyGrossProfitRecordInfo
{
SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"),
SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"),
SalesAmount = SqlRead.GetDecimal(rdr, "SalesAmount"),
GoodsAmount = SqlRead.GetDecimal(rdr, "GoodsAmount"),
ShipmentIncome = SqlRead.GetDecimal(rdr, "ShipmentIncome"),
PromotionsDeductible = SqlRead.GetDecimal(rdr, "PromotionsDeductible"),
PointsDeduction = SqlRead.GetDecimal(rdr, "PointsDeduction"),
ShipmentCost = SqlRead.GetDecimal(rdr, "ShipmentCost"),
PurchaseCosts = SqlRead.GetDecimal(rdr, "PurchaseCosts"),
CatCommission = SqlRead.GetDecimal(rdr, "CatCommission"),
OrderType = SqlRead.GetInt(rdr, "OrderType"),
DayTime = SqlRead.GetDateTime(rdr, "OrderTime")
});
}
}
}
return(dataList);
}
/// <summary>
/// 查询当前月份入库往来帐及账期往来帐
/// </summary>
/// <param name="startTime"></param>
/// <param name="endTime"></param>
public IList <RecordReckoningInfo> SelectRecordReckoningInfos(DateTime startTime, DateTime endTime)
{
const string SQL_SELECT = @"SELECT [ReckoningId],FilialeId,[ThirdCompanyID] AS CompanyId,R.[TradeCode],'{0}' AS DayTime,(CASE WHEN CC.PaymentDays>0 THEN DATEADD(MM,CC.PaymentDays,'{0}') ELSE '{0}' END) AS [PaymentDayTime],[AccountReceivable],TradeCode,R.[State],[IsChecked],[LinkTradeType],[LinkTradeCode] FROM lmShop_Reckoning AS R with(nolock)
INNER JOIN lmshop_CompanyCussent CC ON R.ThirdCompanyID=CC.CompanyID AND CC.CompanyType=1
where R.DateCreated between '{0}' and '{1}'
and AuditingState=1 AND LEFT(LinkTradeCode,2) NOT IN('LI','LO','BI','BO')
and TradeCode not like 'GT%'
";
IList <RecordReckoningInfo> list = new List <RecordReckoningInfo>();
IDataReader sdr = null;
try
{
sdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true,
string.Format(string.Format(SQL_SELECT, startTime, endTime), new DateTime(startTime.Year, startTime.Month, 1)));
while (sdr.Read())
{
list.Add(new RecordReckoningInfo
{
ReckoningId = SqlRead.GetGuid(sdr, "ReckoningId"),
FilialeId = SqlRead.GetGuid(sdr, "FilialeId"),
CompanyId = SqlRead.GetGuid(sdr, "CompanyId"),
TradeCode = SqlRead.GetString(sdr, "TradeCode"),
DayTime = SqlRead.GetDateTime(sdr, "DayTime"),
PaymentDayTime = SqlRead.GetDateTime(sdr, "PaymentDayTime"),
AccountReceivable = SqlRead.GetDecimal(sdr, "AccountReceivable"),
LinkTradeType = SqlRead.GetInt(sdr, "LinkTradeType"),
State = SqlRead.GetInt(sdr, "State"),
LinkTradeCode = SqlRead.GetString(sdr, "LinkTradeCode"),
IsChecked = SqlRead.GetInt(sdr, "IsChecked")
});
}
}
catch (Exception ex)
{
throw new Exception("当前往来帐明细存档异常", ex);
}
finally
{
sdr?.Close();
}
return(list);
}
/// <summary>
/// 汇总同一商品同一公司不同平台的数据
/// </summary>
/// <param name="startTime">记录年月</param>
/// <param name="endTime"></param>
/// <param name="goodsTypes">商品类型</param>
/// <param name="saleFilialeId">销售公司</param>
/// <param name="salePlatformIds">销售平台</param>
/// <param name="orderTypes">订单类型(0:网络发货订单;1:门店采购订单;2:帮门店发货订单;)</param>
/// <returns></returns>
/// zal 2017-07-18
public IList <GoodsGrossProfitInfo> SumGoodsGrossProfitFromMonthByGoodsIdAndSaleFilialeId(DateTime startTime, DateTime?endTime, string goodsTypes, Guid saleFilialeId, string salePlatformIds, string orderTypes)
{
string goodsTypeStr = string.Empty, salePlatformIdStr = string.Empty, orderTypeStr = string.Empty;
var builder = new StringBuilder(@"
SELECT
[GoodsId]
,[GoodsType]
,SUM([Quantity]) AS Quantity
,[SaleFilialeId]
,'00000000-0000-0000-0000-000000000000' AS [SalePlatformId]
,SUM([SalesPriceTotal]) AS SalesPriceTotal
,SUM([PurchaseCostTotal]) AS PurchaseCostTotal
,'-1' AS [OrderType]
FROM [GoodsGrossProfitRecord] WITH(NOLOCK) WHERE 1=1 ");
if (endTime == null || endTime == DateTime.MinValue)
{
builder.AppendFormat(" AND DayTime='{0}' ", startTime);
}
else
{
builder.AppendFormat(" AND DayTime>='{0}' AND DayTime<'{1}'", startTime, endTime);
}
if (!string.IsNullOrEmpty(goodsTypes))
{
goodsTypeStr = goodsTypes.Split(',').Aggregate(goodsTypeStr, (current, item) => current + string.Format(",{0}", item)).Substring(1);
builder.AppendFormat(" AND GoodsType IN({0}) ", goodsTypeStr);
}
if (!string.IsNullOrEmpty(salePlatformIds))
{
salePlatformIdStr = salePlatformIds.Split(',').Aggregate(salePlatformIdStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1);
builder.AppendFormat(" AND SalePlatformId IN({0}) ", salePlatformIdStr);
}
if (!string.IsNullOrEmpty(orderTypes))
{
orderTypeStr = orderTypes.Split(',').Aggregate(orderTypeStr, (current, item) => current + string.Format(",{0}", item)).Substring(1);
builder.AppendFormat(" AND OrderType IN({0}) ", orderTypeStr);
}
if (saleFilialeId != Guid.Empty)
{
builder.AppendFormat(" AND SaleFilialeId = '{0}' ", saleFilialeId);
}
builder.Append(" GROUP BY [GoodsId],[GoodsType],[SaleFilialeId]");
IList <GoodsGrossProfitInfo> dataList = new List <GoodsGrossProfitInfo>();
IDataReader rdr = null;
try
{
rdr = SqlHelper.ExecuteReader(GlobalConfig.ERP_REPORT_DB_NAME, true, builder.ToString());
while (rdr.Read())
{
dataList.Add(new GoodsGrossProfitInfo
{
GoodsId = SqlRead.GetGuid(rdr, "GoodsId"),
GoodsType = SqlRead.GetInt(rdr, "GoodsType"),
Quantity = SqlRead.GetInt(rdr, "Quantity"),
SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"),
SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"),
SalesPriceTotal = SqlRead.GetDecimal(rdr, "SalesPriceTotal"),
PurchaseCostTotal = SqlRead.GetDecimal(rdr, "PurchaseCostTotal"),
OrderType = SqlRead.GetInt(rdr, "OrderType")
});
}
}
finally
{
rdr?.Close();
}
return(dataList);
}
/// <summary>
/// 汇总同一公司同一订单类型不同平台的数据 说明：“门店采购订单”和“帮门店发货订单”按公司和订单类型合计，网络发货订单不进行合计，即将门店数据汇总
/// </summary>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="saleFilialeId">销售公司</param>
/// <param name="salePlatformIds">销售平台</param>
/// <param name="orderTypes">订单类型(0:网络发货订单;1:门店采购订单;2:帮门店发货订单;)</param>
/// <returns></returns>
/// zal 2017-07-17
public IList <CompanyGrossProfitRecordInfo> SumCompanyGrossProfitBySaleFilialeIdAndOrderType(DateTime startTime, DateTime endTime, Guid saleFilialeId, string salePlatformIds, string orderTypes)
{
string salePlatformIdStr = string.Empty, orderTypeStr = string.Empty;
var parameter = new StringBuilder();
if (endTime == DateTime.MinValue)
{
parameter.AppendFormat(" AND OrderTime='{0}' ", startTime);
}
else
{
parameter.AppendFormat(" AND OrderTime>='{0}' AND OrderTime<'{1}'", startTime, endTime);
}
if (!string.IsNullOrEmpty(salePlatformIds))
{
salePlatformIdStr = salePlatformIds.Split(',').Aggregate(salePlatformIdStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1);
parameter.AppendFormat(" AND SalePlatformId IN({0}) ", salePlatformIdStr);
}
if (!string.IsNullOrEmpty(orderTypes))
{
orderTypeStr = orderTypes.Split(',').Aggregate(orderTypeStr, (current, item) => current + string.Format(",{0}", item)).Substring(1);
parameter.AppendFormat(" AND OrderType IN({0}) ", orderTypeStr);
}
if (saleFilialeId != Guid.Empty)
{
parameter.AppendFormat(" AND SaleFilialeId = '{0}' ", saleFilialeId);
}
var builder = new StringBuilder(@"
SELECT
[SaleFilialeId]
,'00000000-0000-0000-0000-000000000000' AS [SalePlatformId]
,SUM([SalesAmount]) AS [SalesAmount]
,SUM([GoodsAmount]) AS [GoodsAmount]
,SUM([ShipmentIncome]) AS [ShipmentIncome]
,SUM([PromotionsDeductible]) AS [PromotionsDeductible]
,SUM([PointsDeduction]) AS [PointsDeduction]
,SUM([ShipmentCost]) AS [ShipmentCost]
,SUM([PurchaseCosts]) AS [PurchaseCosts]
,SUM([CatCommission]) AS [CatCommission]
,[OrderType]
FROM [CompanyGrossProfitRecordDetail] WITH(NOLOCK)
WHERE 1=1 AND State=0 AND [OrderType] IN(1,2)");
builder.Append(parameter);
builder.Append(" GROUP BY [SaleFilialeId],[OrderType]");
builder.Append(" UNION");
builder.Append(@"
SELECT
[SaleFilialeId]
,[SalePlatformId]
,SUM([SalesAmount]) AS[SalesAmount]
,SUM([GoodsAmount]) AS[GoodsAmount]
,SUM([ShipmentIncome]) AS[ShipmentIncome]
,SUM([PromotionsDeductible]) AS[PromotionsDeductible]
,SUM([PointsDeduction]) AS[PointsDeduction]
,SUM([ShipmentCost]) AS[ShipmentCost]
,SUM([PurchaseCosts]) AS[PurchaseCosts]
,SUM([CatCommission]) AS[CatCommission]
,[OrderType]
FROM[CompanyGrossProfitRecordDetail] WITH(NOLOCK)
WHERE 1=1 AND State = 0 AND [OrderType]=0");
builder.Append(parameter);
builder.Append(" GROUP BY [SaleFilialeId],[SalePlatformId],[OrderType]");
IList <CompanyGrossProfitRecordInfo> dataList = new List <CompanyGrossProfitRecordInfo>();
using (var connection = Databases.GetSqlConnection(GlobalConfig.ERP_REPORT_DB_NAME, Transaction.Current == null))
{
IDataReader rdr = null;
try
{
connection.Open();
var command = new SqlCommand(builder.ToString(), connection)
{
CommandTimeout = 600
};
rdr = command.ExecuteReader();
while (rdr.Read())
{
dataList.Add(new CompanyGrossProfitRecordInfo
{
SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"),
SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"),
SalesAmount = SqlRead.GetDecimal(rdr, "SalesAmount"),
GoodsAmount = SqlRead.GetDecimal(rdr, "GoodsAmount"),
ShipmentIncome = SqlRead.GetDecimal(rdr, "ShipmentIncome"),
PromotionsDeductible = SqlRead.GetDecimal(rdr, "PromotionsDeductible"),
PointsDeduction = SqlRead.GetDecimal(rdr, "PointsDeduction"),
ShipmentCost = SqlRead.GetDecimal(rdr, "ShipmentCost"),
PurchaseCosts = SqlRead.GetDecimal(rdr, "PurchaseCosts"),
CatCommission = SqlRead.GetDecimal(rdr, "CatCommission"),
OrderType = SqlRead.GetInt(rdr, "OrderType"),
DayTime = new DateTime(startTime.Year, startTime.Month, startTime.Day)
});
}
}
finally
{
connection.Close();
if (rdr != null)
{
rdr.Close();
}
}
}
return(dataList);
}
/// <summary>
/// 查询历史月份公司毛利信息
/// </summary>
/// <param name="startTime">记录年月</param>
/// <param name="endTime"></param>
/// <param name="saleFilialeId">销售公司</param>
/// <param name="salePlatformIds">销售平台</param>
/// <param name="orderTypes">订单类型(0:网络发货订单;1:门店采购订单;2:帮门店发货订单;)</param>
/// <returns></returns>
public IList <CompanyGrossProfitRecordInfo> SelectCompanyGrossProfitInfos(DateTime startTime, DateTime?endTime, Guid saleFilialeId, string salePlatformIds, string orderTypes)
{
string salePlatformIdStr = string.Empty, orderTypeStr = string.Empty;
var builder = new StringBuilder(@"
SELECT [ID]
,[SaleFilialeId]
,[SalePlatformId]
,[SalesAmount]
,[GoodsAmount]
,[ShipmentIncome]
,[PromotionsDeductible]
,[PointsDeduction]
,[ShipmentCost]
,[PurchaseCosts]
,[CatCommission]
,[OrderType]
,[DayTime]
FROM [CompanyGrossProfitRecord] WITH(NOLOCK) WHERE 1=1 ");
if (endTime == null || endTime == DateTime.MinValue)
{
builder.AppendFormat(" AND DayTime='{0}' ", startTime);
}
else
{
builder.AppendFormat(" AND DayTime>='{0}' AND DayTime<'{1}'", startTime, endTime);
}
if (!string.IsNullOrEmpty(salePlatformIds))
{
salePlatformIdStr = salePlatformIds.Split(',').Aggregate(salePlatformIdStr, (current, item) => current + string.Format(",'{0}'", item)).Substring(1);
builder.AppendFormat(" AND SalePlatformId IN({0}) ", salePlatformIdStr);
}
if (!string.IsNullOrEmpty(orderTypes))
{
orderTypeStr = orderTypes.Split(',').Aggregate(orderTypeStr, (current, item) => current + string.Format(",{0}", item)).Substring(1);
builder.AppendFormat(" AND OrderType IN({0}) ", orderTypeStr);
}
if (saleFilialeId != Guid.Empty)
{
builder.AppendFormat(" AND SaleFilialeId = '{0}' ", saleFilialeId);
}
IList <CompanyGrossProfitRecordInfo> dataList = new List <CompanyGrossProfitRecordInfo>();
using (var connection = Keede.DAL.RWSplitting.Databases.GetSqlConnection(GlobalConfig.ERP_REPORT_DB_NAME, Transaction.Current == null))
{
IDataReader rdr = null;
try
{
connection.Open();
var command = new SqlCommand(builder.ToString(), connection)
{
CommandTimeout = 600
};
rdr = command.ExecuteReader();
while (rdr.Read())
{
dataList.Add(new CompanyGrossProfitRecordInfo
{
ID = SqlRead.GetGuid(rdr, "ID"),
SaleFilialeId = SqlRead.GetGuid(rdr, "SaleFilialeId"),
SalePlatformId = SqlRead.GetGuid(rdr, "SalePlatformId"),
SalesAmount = SqlRead.GetDecimal(rdr, "SalesAmount"),
GoodsAmount = SqlRead.GetDecimal(rdr, "GoodsAmount"),
ShipmentIncome = SqlRead.GetDecimal(rdr, "ShipmentIncome"),
PromotionsDeductible = SqlRead.GetDecimal(rdr, "PromotionsDeductible"),
PointsDeduction = SqlRead.GetDecimal(rdr, "PointsDeduction"),
ShipmentCost = SqlRead.GetDecimal(rdr, "ShipmentCost"),
PurchaseCosts = SqlRead.GetDecimal(rdr, "PurchaseCosts"),
CatCommission = SqlRead.GetDecimal(rdr, "CatCommission"),
OrderType = SqlRead.GetInt(rdr, "OrderType"),
DayTime = SqlRead.GetDateTime(rdr, "DayTime")
});
}
}
finally
{
connection.Close();
if (rdr != null)
{
rdr.Close();
}
}
}
return(dataList);
}