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>根据采购负责人ID获取其绑定的供应商ID集合
/// </summary>
/// <param name="personnelId">采购人负责人ID</param>
/// <returns></returns>
public IList <CompanyCussentInfo> GetCompanyIds(Guid personnelId)
{
const string SQL = @"
SELECT CC.CompanyId,CompanyName FROM lmShop_CompanyCussent AS CC
INNER JOIN (
SELECT DISTINCT CompanyId
FROM [dbo].[lmshop_PurchaseSet]
WHERE PersonResponsible=@PersonnelId
)
AS PS ON CC.CompanyId=PS.CompanyId
WHERE CC.CompanyType=1--供应商
AND CC.[State]=1--启用";
IList <CompanyCussentInfo> list = new List <CompanyCussentInfo>();
using (var dr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, SQL, new SqlParameter("@PersonnelId", personnelId)))
{
while (dr.Read())
{
var info = new CompanyCussentInfo
{
CompanyId = SqlRead.GetGuid(dr, "CompanyId"),
CompanyName = SqlRead.GetString(dr, "CompanyName")
};
list.Add(info);
}
}
return(list);
}
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);
}
public IList <CompanyPurchaseGoupInfo> GetCompanyPurchaseGoupList(Guid companyId)
{
IList <CompanyPurchaseGoupInfo> list = new List <CompanyPurchaseGoupInfo>();
if (companyId != Guid.Empty)
{
string sql = string.Format("SELECT [CompanyId],[PurchaseGroupId],[PurchaseGroupName],[OrderIndex] FROM [CompanyPurchaseGoup] WHERE [CompanyId]='{0}'", companyId);
using (var dr = SqlHelper.ExecuteReader(GlobalConfig.ERP_DB_NAME, true, sql))
{
if (dr != null)
{
while (dr.Read())
{
list.Add(new CompanyPurchaseGoupInfo
{
CompanyId = SqlRead.GetGuid(dr, "CompanyId"),
PurchaseGroupId = SqlRead.GetGuid(dr, "PurchaseGroupId"),
PurchaseGroupName = SqlRead.GetString(dr, "PurchaseGroupName"),
OrderIndex = SqlRead.GetInt(dr, "OrderIndex")
});
}
}
}
}
return(list);
}
/// <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);
}
