/// <summary>
/// 根据条件返回GoodsPriceChange表的所有数据
/// </summary>
/// <param name="saleFilialeId">公司ID</param>
/// <param name="salePlatformId">销售平台ID</param>
/// <param name="goodsName">商品名称</param>
/// <param name="goodsId">商品编号</param>
/// <param name="type">0:销售价；1:加盟价；2:批发价</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total">数据总数</param>
/// <returns></returns>
public List <GoodsPriceChange> GetAllGoodsPriceChange(Guid?saleFilialeId, Guid?salePlatformId, string goodsName, Guid goodsId, int type, int pageIndex, int pageSize, out int total)
{
var sqlsb = new StringBuilder(SQL_SELECT + " where 1=1");
if (!string.IsNullOrEmpty(saleFilialeId.ToString()) && saleFilialeId != Guid.Empty)
{
sqlsb.AppendFormat(@" AND [SaleFilialeId]='{0}'", saleFilialeId);
}
if (!string.IsNullOrEmpty(salePlatformId.ToString()) && salePlatformId != Guid.Empty)
{
sqlsb.AppendFormat(@" AND [SalePlatformId]='{0}'", salePlatformId);
}
if (!string.IsNullOrEmpty(goodsName))
{
sqlsb.AppendFormat(@" AND [GoodsName]='{0}'", goodsName);
}
if (goodsId != Guid.Empty)
{
sqlsb.AppendFormat(@" AND [GoodsId]='{0}'", goodsId);
}
if (type != -1)
{
sqlsb.AppendFormat(@" AND [Type]='{0}'", type);
}
using (var db = DatabaseFactory.Create())
{
var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sqlsb.ToString(), " Datetime DESC");
var pageItem = db.SelectByPage <GoodsPriceChange>(true, list);
total = (int)pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
public IList <PurchaseSetLogInfo> GetPurchaseSetLogListByPage(Guid goodsId, Guid warehouseId, Guid hostingFilialeId, int startPage, int pageSize, out long recordCount)
{
var strbSql = new StringBuilder(SQL_SELECT_PURCHASESETLOG);
strbSql.Append(" WHERE 1=1");
if (goodsId != Guid.Empty)
{
strbSql.Append(" AND GoodsId='").Append(goodsId).Append("'");
}
if (warehouseId != Guid.Empty)
{
strbSql.Append(" AND WarehouseId='").Append(warehouseId).Append("'");
}
if (hostingFilialeId != Guid.Empty)
{
strbSql.AppendFormat(" AND HostingFilialeId ='{0}'", hostingFilialeId);
}
strbSql.Append("AND LogType=1");
using (var db = DatabaseFactory.Create())
{
var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(startPage, pageSize, strbSql.ToString(), " ChangeDate DESC ");
var pageItem = db.SelectByPage <PurchaseSetLogInfo>(true, pageQuery);
recordCount = pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
/// <summary>
/// 根据条件筛选数据
/// </summary>
/// <param name="startTime">创建开始时间</param>
/// <param name="endTime">创建结束时间</param>
/// <param name="state">借记单状态</param>
/// <param name="warehouseId">仓库id</param>
/// <param name="companyId">供应商id</param>
/// <param name="personResponsibleId">责任人id</param>
/// <param name="activityTimeStart">活动开始时间</param>
/// <param name="activityTimeEnd">活动结束时间</param>
/// <param name="titleOrMemo">标题或备注</param>
/// <param name="startPage">开始页索引</param>
/// <param name="pageSize">每页显示的数量</param>
/// <param name="recordCount">查询数据的总和</param>
/// <returns></returns>
public IList <DebitNoteInfo> GetDebitNoteList(DateTime startTime, DateTime endTime, int state, Guid warehouseId, Guid companyId, Guid personResponsibleId, string activityTimeStart, string activityTimeEnd, string titleOrMemo, int startPage, int pageSize, out long recordCount)
{
var strb = new StringBuilder(SQL_SELECT_DEBITNOTE);
strb.Append(" WHERE 1=1 ");
if (startTime != DateTime.MinValue)
{
strb.Append(" AND [CreateDate]>='").Append(startTime.ToString("yyyy-MM-dd 00:00:00")).Append("'");
}
if (endTime != DateTime.MinValue)
{
strb.Append(" AND [CreateDate]<'").Append(endTime.AddDays(1).ToString("yyyy-MM-dd 00:00:00")).Append("'");
}
if (state != -1)
{
strb.Append(" AND [State]=").Append(state);
}
if (warehouseId != Guid.Empty)
{
strb.Append(" AND [WarehouseId]='").Append(warehouseId).Append("'");
}
if (companyId != Guid.Empty)
{
strb.Append(" AND [CompanyId]='").Append(companyId).Append("'");
}
if (personResponsibleId != Guid.Empty)
{
strb.Append(" AND [PersonResponsible]='").Append(personResponsibleId).Append("'");
}
if (!string.IsNullOrEmpty(activityTimeStart))
{
strb.Append(" AND [ActivityTimeStart]>='").Append(DateTime.Parse(activityTimeStart)).Append("'");
}
if (!string.IsNullOrEmpty(activityTimeEnd))
{
strb.Append(" AND [ActivityTimeEnd]<='").Append(DateTime.Parse(activityTimeEnd).AddDays(1).AddMilliseconds(-1)).Append("'");
}
if (!string.IsNullOrEmpty(titleOrMemo))
{
strb.Append(" AND [Title] like '%").Append(titleOrMemo).Append("%' or [Memo] like '%").Append(titleOrMemo).Append("%'");
}
using (var db = DatabaseFactory.Create())
{
var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(startPage, pageSize, strb.ToString(), " [State],[CreateDate] DESC ");
var pageItem = db.SelectByPage <DebitNoteInfo>(true, pageQuery, null);
recordCount = pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
/// <summary>
/// 根据ManuallyCheckBillDetail表的ManuallyCheckBillId字段返回数据
/// </summary>
/// <param name="manuallyCheckBillId"></param>
/// <param name="orderNo">系统订单号或者第三方订单号</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <ManuallyCheckBillDetailInfo> GetManuallyCheckBillDetailByManuallyCheckBillId(Guid manuallyCheckBillId, string orderNo, int pageIndex, int pageSize, out int total)
{
string sql = SQL_SELECT + "where [ManuallyCheckBillId] = '" + manuallyCheckBillId + "'";
if (!string.IsNullOrEmpty(orderNo))
{
sql += " AND (SystemOrderNo='" + orderNo + "' OR ThirdOrderNo='" + orderNo + "')";
}
using (var db = DatabaseFactory.Create())
{
var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sql, " OrderTime DESC");
var pageItem = db.SelectByPage <ManuallyCheckBillDetailInfo>(true, list);
total = (int)pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
/// <summary>
/// 根据条件获取对账记录
/// </summary>
/// <param name="checkBillPersonnelId"></param>
/// <param name="tradeCode"></param>
/// <param name="checkState"></param>
/// <param name="checkBillDateStart"></param>
/// <param name="checkBillDateEnd"></param>
/// <param name="salePlatformId"></param>
/// <param name="receiptState"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <ManuallyCheckBillInfo> GetAllManuallyCheckBill(Guid checkBillPersonnelId, string tradeCode, CheckType checkState, DateTime checkBillDateStart, DateTime checkBillDateEnd, Guid salePlatformId, int receiptState, int pageIndex, int pageSize, out int total)
{
var sb = new StringBuilder(SQL_SELECT + " WHERE 1=1 ");
if (!checkBillPersonnelId.Equals(Guid.Empty))
{
sb.AppendFormat(@" AND CheckBillPersonnelId='{0}'", checkBillPersonnelId);
}
if (!string.IsNullOrEmpty(tradeCode))
{
sb.AppendFormat(@" AND TradeCode='{0}'", tradeCode);
}
if (!checkState.Equals(CheckType.AllCheck))
{
sb.AppendFormat(@" AND CheckState={0}", (int)checkState);
}
if (checkBillDateStart != DateTime.MinValue)
{
sb.AppendFormat(@" AND CheckBillDate>='{0}'", checkBillDateStart);
}
if (checkBillDateEnd != DateTime.MaxValue)
{
sb.AppendFormat(@" AND CheckBillDate<'{0}'", checkBillDateEnd.AddDays(1));
}
if (!salePlatformId.Equals(Guid.Empty))
{
sb.AppendFormat(@" AND SalePlatformId='{0}'", salePlatformId);
}
if (receiptState != -1)
{
sb.AppendFormat(@" AND ReceiptState={0} ", receiptState);
}
using (var db = DatabaseFactory.Create())
{
var list = new Keede.DAL.Helper.Sql.PageQuery(pageIndex, pageSize, sb.ToString(), " CheckBillDate DESC");
var pageItem = db.SelectByPage <ManuallyCheckBillInfo>(true, list);
total = (int)pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
/// <summary>
/// 红冲单据分页查询
/// </summary>
/// <param name="warehouseId">仓库</param>
/// <param name="startTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="redType">红冲类型</param>
/// <param name="documentType">单据类型</param>
/// <param name="state">状态</param>
/// <param name="no">单号</param>
/// <param name="startPage">当前页</param>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="recordCount">总记录数</param>
/// <returns></returns>
public IList <DocumentRedInfo> GetDocumentRedListToPage(Guid warehouseId, DateTime startTime, DateTime endTime, int redType, int documentType, int state, string no, int startPage, int pageSize, out long recordCount)
{
var sql = new StringBuilder(@"
SELECT [RedId]
,[FilialeId]
,[ThirdCompanyId]
,[TradeCode]
,[DateCreated]
,[Transactor]
,[Description]
,[AccountReceivable]
,[SubtotalQuantity]
,[RedType]
,[DocumentType]
,[State]
,[WarehouseId]
,[AuditTime]
,[StorageType]
,[LinkTradeCode]
,[LinkTradeId]
,[LinkDateCreated]
,[LinkDescription]
,[Memo]
,[IsOut]
FROM [DocumentRed] WITH(NOLOCK) WHERE 1=1");
if (warehouseId != Guid.Empty)
{
sql.Append(" AND WarehouseId='").Append(warehouseId).Append("'");
}
if (startTime != DateTime.MinValue)
{
sql.Append(" AND DateCreated>='").Append(startTime).Append("'");
}
if (endTime != DateTime.MinValue)
{
sql.Append(" AND DateCreated<'").Append(endTime).Append("'");
}
if (!redType.Equals(0))
{
sql.Append(" AND RedType='").Append(redType).Append("'");
}
if (!state.Equals(0))
{
sql.Append(" AND State='").Append(state).Append("'");
}
if (!documentType.Equals(-1))
{
sql.Append(" AND DocumentType ='").Append(documentType).Append("'");
}
if (!string.IsNullOrEmpty(no))
{
sql.Append(" AND (TradeCode like '%").Append(no).Append("%'");
sql.Append(" OR LinkTradeCode like '%").Append(no).Append("%')");
}
using (var db = DatabaseFactory.Create())
{
var pageQuery = new Keede.DAL.Helper.Sql.PageQuery(startPage, pageSize, sql.ToString(), " DateCreated DESC ");
var pageItem = db.SelectByPage <DocumentRedInfo>(true, pageQuery);
recordCount = pageItem.RecordCount;
return(pageItem.Items.ToList());
}
}
