public IList <LoadBillStatistics> GetLoadBillStatistics(List <string> loadBillNum)
{
var query = NHibernateSession.CreateSQLQuery("SELECT BatchNO as LoadBillNum,SUM(WayBillFee) as WayBillFee,SUM(ProcessingFee) as ProcessingFee FROM WayBillCost WHERE BatchNO IN (:loadBillNum) GROUP BY BatchNO;");
query.SetParameterList("loadBillNum", loadBillNum);
return(query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(LoadBillStatistics))).List <LoadBillStatistics>());
}
public long GetCountByLoadBillNum(string loadBillNum)
{
var query = NHibernateSession.CreateSQLQuery("SELECT COUNT(*) FROM WayBillCost WHERE BatchNO =:loadBillNum;");
query.SetParameter("loadBillNum", loadBillNum);
return(query.UniqueResult <long>());
}
public IList <string> GetProblemLoadBillNum(IList <string> loadBillNums)
{
var query = NHibernateSession.CreateSQLQuery("select DISTINCT LoadBillNO FROM ExpressNoExceptionDetail where LoadBillNO IN (:loadBillNums)");
query.SetParameterList("loadBillNums", loadBillNums);
return(query.List <string>());
}
public void InvalidTraficAndPermit(decimal personId, DateTime date)
{
string SQLComand = @"
UPDATE TA_BaseTraffic
SET BasicTraffic_Used = 0
WHERE BasicTraffic_PersonID = :personId
AND
BasicTraffic_Date >= :date ;
DELETE FROM TA_ProceedTraffic
WHERE ProceedTraffic_PersonId = :personId
AND
ProceedTraffic_FromDate >= :date ;
UPDATE TA_PermitPair
SET PermitPair_IsApplyedOnTraffic = 0
WHERE Permitpair_PermitId in (SELECT Permit_ID FROM TA_Permit
WHERE Permit_PersonId = :personId
AND
Permit_FromDate >= :date);" ;
IQuery query = NHibernateSession.CreateSQLQuery(SQLComand)
.SetParameter("personId", personId)
.SetParameter("date", date);
query.UniqueResult();
}
public IList <string> GetProblemLoadBillCost(IList <string> loadBillNums)
{
var query = NHibernateSession.CreateSQLQuery("SELECT DISTINCT LoadBillNum FROM LoadBillCost where LoadBillNum IN (:loadBillNums) AND `Status`<>0;");
query.SetParameterList("loadBillNums", loadBillNums);
return(query.List <string>());
}
public void UpdateCFP(decimal personId, DateTime date)
{
IQuery query = NHibernateSession.CreateSQLQuery("exec spr_UpdateCFP :personId,:date")
.SetParameter("personId", personId)
.SetParameter("date", date);
query.UniqueResult();
}
/// <summary>
/// 删除异常数据和成本数据
/// </summary>
/// <param name="wayBillCostID"></param>
/// <returns></returns>
public int DeleteException(int wayBillCostID, WayBillException Excmodel, WayBillCost Costmodel)
{
using (ITransaction trans = NHibernateSession.BeginTransaction())
{
try
{
var a = 0;
//删除异常数据
var query = NHibernateSession.CreateSQLQuery("DELETE FROM ExpressNoExceptionDetail WHERE WayBillCostID = :WayBillCostID");
query.SetParameter("WayBillCostID", wayBillCostID);
query.ExecuteUpdate();
//删除成本数据
query = NHibernateSession.CreateSQLQuery("DELETE FROM WayBillCost WHERE ID = :ID");
query.SetParameter("ID", wayBillCostID);
query.ExecuteUpdate();
//添加删除异常记录
query = NHibernateSession.CreateSQLQuery("insert into DelExpressNoExceptionDetail (ExpressNo,ExceptionType,ExceptionMsg,CreateTime,LoadBillNO,Status,WayBillCostID,PostingTime,DelTime) values (:ExpressNo,:ExceptionType,:ExceptionMsg,:CreateTime,:LoadBillNO,:Status,:WayBillCostID,:PostingTime,:DelTime)");
query.SetParameter("ExpressNo", Excmodel.ExpressNo); //运单号
query.SetParameter("ExceptionType", Excmodel.ExceptionType); //异常类型
query.SetParameter("ExceptionMsg", Excmodel.ExceptionMsg); //异常说明
query.SetParameter("CreateTime", DateTime.Now); //创建时间
query.SetParameter("LoadBillNO", Excmodel.LoadBillNum); //提单号
query.SetParameter("Status", 0); //状态
query.SetParameter("WayBillCostID", Excmodel.WayBillCostID); //异常成本ID
query.SetParameter("PostingTime", Excmodel.PostingTime); //收寄日期
query.SetParameter("DelTime", DateTime.Now); //删除时间
query.ExecuteUpdate();
//添加删除成本记录
query = NHibernateSession.CreateSQLQuery("insert into DelWayBillCostEx (ExpressNo,WayBillFee,Weight,CreateTime,SendAddress,ProcessingFee,Product,BatchNO,PostingTime,ReconcileDate,PayStatus,PayTime,DelTime) values (:ExpressNo,:WayBillFee,:Weight,:CreateTime,:SendAddress,:ProcessingFee,:Product,:BatchNO,:PostingTime,:ReconcileDate,:PayStatus,:PayTime,:DelTime)");
query.SetParameter("ExpressNo", Costmodel.ExpressNo); //邮件号
query.SetParameter("WayBillFee", Costmodel.WayBillFee); //邮资
query.SetParameter("Weight", Costmodel.Weight); //重量
query.SetParameter("CreateTime", DateTime.Now); //创建时间
query.SetParameter("SendAddress", Costmodel.SendAddress); //寄达地
query.SetParameter("ProcessingFee", Costmodel.ProcessingFee); //邮件处理费
query.SetParameter("Product", Costmodel.Product); //产品
query.SetParameter("BatchNO", Costmodel.BatchNO); //批次
query.SetParameter("PostingTime", Costmodel.PostingTime); //收寄日期
query.SetParameter("ReconcileDate", Costmodel.ReconcileDate); //结算月份
query.SetParameter("PayStatus", Costmodel.PayStatus); //结算状态
query.SetParameter("PayTime", Costmodel.PayTime); //结算时间
query.SetParameter("DelTime", DateTime.Now); //删除时间
query.ExecuteUpdate();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return(1);
}
public IPageOfList <LoadBillReconciliation> GetByMonthPayOffFilter(ParameterFilter filter)
{
string column = @"a.ID,
b.ReconcileDate AS ReconcileDate,
c.Cus_Name AS CusName,
a.LoadBillNum AS LoadBillNum,
a.BillWeight AS FeeWeight,
a.OrderCounts AS ExpressCount,
a.CompletionTime AS CompletionTime,
IFNULL(b.GroundHandlingFee,0) AS GroundHandlingFee,
IFNULL(b.StoreFee,0) AS CostStoreFee,
CASE WHEN b.ID IS NULL THEN a.PreTotalCollectFees ELSE 0 END AS CostExpressFee,
CASE WHEN b.ID IS NULL THEN a.PreTotalOperateFee ELSE 0 END AS CostOperateFee,
null AS CostOtherFee,
null AS CostTotalFee,
b.PayStatus AS CostStatus,
a.LoadFee AS InComeLoadFee,
a.StoreFee AS InComeStoreFee,
a.TotalCollectFees AS InComeExpressFee,
a.TotalOperateFee AS InComeOperateFee,
a.OtherFee AS InComeOtherFee,
null AS InComeTotalFee,
a.PayStatus AS InComeStatus,
null AS TotalGrossProfit,
null AS GrossProfitRate,
CASE IFNULL(a.IsAddMonthPayOff,0) WHEN 0 THEN '待添加到月结表' ELSE '已添加月结表' END AS Status,
cast(a.IsAddMonthPayOff as signed) as IsAddMonthPayOff,
b.ID AS IsReal,
a.OrderWeight as ExpressWeight";
string sql = @" FROM LoadBillInCome a INNER JOIN MonthPayOffDetail d ON a.ID=d.LoadBillID LEFT JOIN LoadBillCost b ON a.LoadBillNum=b.LoadBillNum LEFT JOIN CustomerInfo c ON a.CustomerID=c.ID WHERE 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
var countQuery = NHibernateSession.CreateSQLQuery(string.Format("select COUNT(a.ID) as Count {0}", sql));
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} {2} ", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
//var Count = countQuery.List<object[]>()[0];
var Count = countQuery.UniqueResult <long>();
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(LoadBillReconciliation))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <LoadBillReconciliation>().ToList();
return(new LRPageOfList <LoadBillReconciliation>(list, pageIndex, pageSize, Count));
}
/// <summary>
/// 根据运单成本ID运单成本详细
/// </summary>
/// <param name="ExpressNo"></param>
/// <returns></returns>
public IList <WayBillCost> GetCostByExpByID(string CostID)
{
string column = @"ID,ExpressNo,BatchNO,PostingTime,Weight,SendAddress,ProcessingFee,WayBillFee,Product";
string sql = @" from WayBillCost where ID='" + CostID + "'";
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} ", column, sql)).AddEntity(typeof(WayBillCost));
//StatModel model = new StatModel();
return(query.List <WayBillCost>());
}
public void ExpressMath(int DeliveryID, int customerInfoID)
{
var query = NHibernateSession.CreateSQLQuery(@"UPDATE LoadBillInCome SET CustomerID=:customerInfoID where DeliveryID=:DeliveryID;
UPDATE WayBillInCome SET CustomerID=:customerInfoID where DeliveryID=:DeliveryID;
UPDATE IdentityCardDetail SET CustomerID=:customerInfoID where DeliveryID=:DeliveryID;");
query.SetParameter("DeliveryID", DeliveryID);
query.SetParameter("customerInfoID", customerInfoID);
query.ExecuteUpdate();
}
/// <summary>
/// 根据运单查询运单收入详细
/// </summary>
/// <param name="ExpressNo"></param>
/// <returns></returns>
public IList <WayBillReconciliation> GetWayBillInComeByExpressNo(string ExpressNo)
{
string column = @"a.ExpressNo,a.LoadBillNO as LoadBillNum,a.Weight,a.ReceiverProvince,a.ExpressType as ExpressTypeget,a.ExpressFee,a.OperateFee,b.CompletionTime ";
string sql = @"from WayBillInCome a LEFT JOIN LoadBillInCome b on (a.LoadBillNO=b.LoadBillNum) where a.ExpressNo='" + ExpressNo + "'";
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} ", column, sql));
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillReconciliation))).List <WayBillReconciliation>().ToList();
//StatModel model = new StatModel();
return(list);// query.List<WayBillReconciliation>();
}
public void DeleteCalendarsByType(decimal calendarTypeId, DateTime fromDate, DateTime toDate)
{
string SQLCommand = @"DELETE from TA_Calendar where Calendar_CalendarTypeId=:typeId
AND Calendar_Date >= :fromDate
AND Calendar_Date <= :toDate";
NHibernateSession.CreateSQLQuery(SQLCommand)
.SetParameter("typeId", calendarTypeId)
.SetParameter("fromDate", fromDate)
.SetParameter("toDate", toDate)
.ExecuteUpdate();
}
public override IPageOfList <WayBillException> GetByFilter(ParameterFilter filter)
{
string column = @"e.ID AS ID,
w.ID AS IsInputCost,
w.PostingTime AS PostingTime,
e.ExpressNo AS ExpressNo,
e.LoadBillNO AS LoadBillNum,
w.Weight AS Weight,
c.Cus_Name AS CusName,
(CASE WHEN w.ExpressNo IS NULL THEN a.PreExpressFee ELSE w.WayBillFee END) AS WayBillFee,
(CASE WHEN w.ExpressNo IS NULL THEN a.PreOperateFee ELSE w.ProcessingFee END) AS ProcessingFee,
a.ExpressFee AS ExpressFee,
a.OperateFee AS OperateFee,
w.PayStatus AS CostStatus,
a.PayStatus AS InComeStatus,
a.ExpressFee - w.WayBillFee AS WayBillProfit,
e.ExceptionMsg,e.WayBillCostID ";
string sql = @" FROM ExpressNoExceptionDetail e
INNER JOIN WayBillCost w ON e.WayBillCostID = w.ID AND e.Status=0
LEFT JOIN WayBillInCome a ON e.ExpressNo = a.ExpressNo
LEFT JOIN CustomerInfo c ON a.CustomerID = c.ID
where 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
string strSQL = "SELECT COUNT(e.ID) as Count " + sql;
var countQuery = NHibernateSession.CreateSQLQuery(strSQL);
var query = NHibernateSession.CreateSQLQuery(string.Format("SELECT {0} {1} {2}", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
var Count = countQuery.List <long>()[0];
long totalCounts = Count;
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillException))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <WayBillException>().ToList();
return(new WRPageOfList <WayBillException>(list, pageIndex, pageSize, totalCounts, new StatModel()));
}
/// <summary>
/// حذف شیفتهای استثنا قبلی
/// </summary>
/// <param name="personId"></param>
/// <param name="fromDate"></param>
/// <param name="toDate"></param>
public void DeleteExceptionShift(decimal personId, DateTime fromDate, DateTime toDate)
{
String sqlCommand = @"delete from TA_ExceptionShift
where ExceptionShift_PersonID=:personId
AND ExceptionShift_Date>= :fromDate
AND ExceptionShift_Date<= :toDate";
NHibernateSession.CreateSQLQuery(sqlCommand)
.SetParameter("personId", personId)
.SetParameter("fromDate", fromDate.Date)
.SetParameter("toDate", toDate.Date)
.ExecuteUpdate();
}
public DateTime?GetFirstShiftUsedInWorkGroup(decimal shiftId, decimal workGroupId)
{
String sqlCommand = @"select WorkGroupDtl_Date from TA_WorkGroupDetail
where WorkGroupDtl_WorkGroupId =:workGroupId
AND WorkGroupDtl_ShiftId=:shiftId";
IList <DateTime> result = NHibernateSession.CreateSQLQuery(sqlCommand)
.SetParameter("workGroupId", workGroupId)
.SetParameter("shiftId", shiftId)
.List <DateTime>();
return(result.FirstOrDefault());
}
public decimal?GetShiftIdByWorkGroupId(decimal workGroupId, DateTime date)
{
String sqlCommand = @"select WorkGroupDtl_ShiftId from TA_WorkGroupDetail
where WorkGroupDtl_WorkGroupId =:workGroupId
AND WorkGroupDtl_Date=:date";
IList <decimal> result = NHibernateSession.CreateSQLQuery(sqlCommand)
.SetParameter("workGroupId", workGroupId)
.SetParameter("date", date.Date)
.List <decimal>();
return(result.FirstOrDefault());
}
public decimal?GetShiftIdByPersonId(decimal personId, DateTime date)
{
String sqlCommand = @"select WorkGroupDtl_ShiftId from TA_WorkGroupDetail
where WorkGroupDtl_WorkGroupId in (select top(1)AsgWorkGroup_WorkGroupId from TA_AssignWorkGroup where TA_AssignWorkGroup.AsgWorkGroup_PersonId=:personId AND AsgWorkGroup_FromDate <=:date ORDER BY AsgWorkGroup_FromDate DESC)
AND WorkGroupDtl_Date=:date";
IList <decimal> result = NHibernateSession.CreateSQLQuery(sqlCommand)
.SetParameter("personId", personId)
.SetParameter("date", date.Date)
.List <decimal>();
return(result.FirstOrDefault());
}
public override IPageOfList <MonthPayOff> GetByFilter(ParameterFilter filter)
{
string column = @"a.ID,
a.PayOffMonth,
cast(COUNT(b.ID)as signed) as LoadBillCounts,
cast(SUM(c.OrderCounts)as signed) AS OrderCounts,
SUM(b.PreTotalCostFee) AS PreTotalCostFee,
SUM(b.TotalCostFee) AS TotalCostFee,
SUM(b.PreInComeFee) AS PreInComeFee,
SUM(b.InComeFee) AS InComeFee,
SUM(b.TotalMargin) AS TotalMargin,
SUM(b.TotalMargin)/SUM(b.PreInComeFee) AS MarginRate,
a.Remark,
a.`Status`";
string sql = @" FROM MonthPayOff a LEFT JOIN MonthPayOffDetail b ON a.ID=b.MonthPayOffID LEFT JOIN LoadBillInCome c ON b.LoadBillID=c.ID where 1=1";
if (filter.HasQueryString)
{
sql = filter.ToHql();
}
else
{
sql += filter.ToHql();
}
var paras = filter.GetParameters();
var countQuery = NHibernateSession.CreateSQLQuery(string.Format("select COUNT(DISTINCT a.ID) as Count {0}", sql));
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} group by a.ID {2} ", column, sql, filter.GetOrderString()));
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
query.SetParameter(key, paras[key]);
}
int pageIndex = filter.PageIndex;
int pageSize = filter.PageSize;
//var Count = countQuery.List<object[]>()[0];
var Count = countQuery.UniqueResult <long>();
if (Count == 0)
{
return(new PageOfList <MonthPayOff>(new List <MonthPayOff>(), pageIndex, pageSize, Count));
}
else
{
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(MonthPayOff))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List <MonthPayOff>().ToList();
return(new PageOfList <MonthPayOff>(list, pageIndex, pageSize, Count));
}
}
public Dictionary <decimal, decimal> GetUivalidationPersonIdList(IList <decimal> personIdList)
{
Dictionary <decimal, decimal> UiValidationGroupPersonIdDic = new Dictionary <decimal, decimal>();
string SQLCommand = string.Empty;
if (personIdList.Count < this.operationBatchSizeValue && this.operationBatchSizeValue < 2100)
{
SQLCommand = @" select prs_Id,prsTA_UIValidationGroupID from TA_PersonTASpec as pts
inner join TA_Person prs
on pts.prsTA_ID = prs.Prs_ID
inner join TA_UIValidationGroup as uvg
on pts.prsTA_UIValidationGroupID = uvg.UIValGrp_ID
where pts.prsTA_ID in (:personlist) and prs.Prs_Active =1 and prs.prs_IsDeleted =0
and prsTA_UIValidationGroupID is not null
group by prsTA_UIValidationGroupID,prs_Id ";
var resultList = NHibernateSession.CreateSQLQuery(SQLCommand)
.SetParameterList("personlist", personIdList.ToArray()).List <object>();
foreach (var item in resultList)
{
UiValidationGroupPersonIdDic.Add(Convert.ToDecimal(((object[])(item))[0]), Convert.ToDecimal(((object[])(item))[1]));
}
}
else
{
TempRepository tempRep = new TempRepository(false);
string operationGUID = tempRep.InsertTempList(personIdList);
SQLCommand = @"select prs_Id,prsTA_UIValidationGroupID from TA_PersonTASpec as pts
inner join TA_Person prs
on pts.prsTA_ID = prs.Prs_ID
Inner Join TA_Temp temp
on prs.Prs_ID=temp.temp_ObjectID
inner join TA_UIValidationGroup as uvg
on pts.prsTA_UIValidationGroupID = uvg.UIValGrp_ID
where prs.Prs_Active =1 and prs.prs_IsDeleted =0 and temp_OperationGUID =:operationGUID
and prsTA_UIValidationGroupID is not null
group by prsTA_UIValidationGroupID,prs_Id
";
var resultList = NHibernateSession.CreateSQLQuery(SQLCommand)
.SetParameter("operationGUID", operationGUID).List <object>();
tempRep.DeleteTempList(operationGUID);
foreach (var item in resultList)
{
UiValidationGroupPersonIdDic.Add(Convert.ToDecimal(((object[])(item))[0]), Convert.ToDecimal(((object[])(item))[1]));
}
}
return(UiValidationGroupPersonIdDic);
}
/// <summary>
/// پرسنلی که باید محاسبات برای آنها انجام شود را برمی گرداند
/// اگر تاریخ آخرین محاسبه پرسنلی کوچکتر از تاریخ ورودی بود یا محاسباتش نا معتبر بود، آن پرسنل برای محاسبه واکشی می گردد
/// </summary>
/// <param name="Date">زمان حال که محاسبات تا آن زمان باید انجام شود</param>
/// <returns></returns>
public IList <ExecutablePersonCalculation> GetAll(DateTime Date)
{
string SQLCommand = @"declare @date datetime
set @date=:Date
SELECT CFP.*
FROM TA_Calculation_Flag_Persons as CFP
join TA_Person on Prs_ID=CFP_PrsId
WHERE prs_IsDeleted=0 AND Prs_Active=1 and
CFP_Date < @date or (CFP_Date = @date and CFP_CalculationIsValid = 0)";
IList <ExecutablePersonCalculation> result = NHibernateSession.CreateSQLQuery(SQLCommand)
.AddEntity(typeof(ExecutablePersonCalculation))
.SetParameter("Date", Date.Date)
.List <ExecutablePersonCalculation>();
return(result);
}
public IList <decimal> GetUivalidationIdListByCalculationRangeGroup(decimal calculationRangeGroupId)
{
string SQLCommand = @"select prsTA_UIValidationGroupID from TA_PersonTASpec as pts
inner join TA_Person prs
on pts.prsTA_ID = prs.Prs_ID
inner join TA_PersonRangeAssignment as awg
on awg.PrsRangeAsg_PersonId = prs.Prs_ID
where awg.PrsRangeAsg_CalcRangeGrpId =:CalculationRangeGroupId and prs.Prs_Active =1 and prs.prs_IsDeleted =0
and prsTA_UIValidationGroupID is not null
group by prsTA_UIValidationGroupID
";
IQuery query = NHibernateSession.CreateSQLQuery(SQLCommand)
.SetParameter("CalculationRangeGroupId", calculationRangeGroupId);
IList <decimal> UiValidationGroupIdList = query.List <decimal>();
return(UiValidationGroupIdList);
}
public List <CurrencyRate> GetLastList()
{
string query = @"select CR.Id from CurrencyRate CR
inner join (select max([Date]) as MaxDate, CurrencyId
from CurrencyRate
group by CurrencyId) LCR on CR.Date = LCR.MaxDate and CR.CurrencyId = LCR.CurrencyId";
IQuery q = NHibernateSession.CreateSQLQuery(query);
List <int> tempids = q.List <int>() as List <int>;
ICriteria crit = GetCriteria();
crit.Add(Expression.In("ID", tempids));
return(crit.List <CurrencyRate>() as List <CurrencyRate>);
}
public void RunSQL(string Query)
{
try
{
if (!String.IsNullOrEmpty(Query))
{
NHibernateSession.CreateSQLQuery(Query).ExecuteUpdate();
}
}
finally
{
if (this.disconnectedly)
{
this.NHibernateSession.Disconnect();
}
}
}
/// <summary>
/// 运单对账页面统计异常数据数量
/// </summary>
/// <returns></returns>
public long GetExceptionCount(ParameterFilter strWhere)
{
string sql = @"SELECT COUNT(e.ID) as Count FROM ExpressNoExceptionDetail e
INNER JOIN WayBillCost w ON e.WayBillCostID = w.ID AND e.Status=0
LEFT JOIN WayBillInCome a ON e.ExpressNo = a.ExpressNo
LEFT JOIN CustomerInfo c ON a.CustomerID = c.ID
where 1=1" + strWhere.ToHql();
var countQuery = NHibernateSession.CreateSQLQuery(sql);
var paras = strWhere.GetParameters();
foreach (var key in paras.Keys)
{
countQuery.SetParameter(key, paras[key]);
}
long result = countQuery.UniqueResult <long>();
return(result);
}
/// <summary>
/// 修改后 删除异常记录
/// </summary>
/// <param name="costinfo"></param>
/// <returns></returns>
public int UpdateException(WayBillCost costinfo, WayBillException model, int WayBillCostID)
{
using (ITransaction trans = NHibernateSession.BeginTransaction())
{
try
{
int a = 0;
//删除异常表记录
var query = NHibernateSession.CreateSQLQuery("DELETE FROM ExpressNoExceptionDetail WHERE WayBillCostID = :WayBillCostID ");
query.SetParameter("WayBillCostID", WayBillCostID);
query.ExecuteUpdate();
//修改运单成本信息
query = NHibernateSession.CreateSQLQuery("Update WayBillCost set Product=:Product,WayBillFee=:WayBillFee ,ProcessingFee=:ProcessingFee WHERE ID = :ID ");
query.SetParameter("ID", WayBillCostID);
query.SetParameter("Product", costinfo.Product); //快递类型
query.SetParameter("WayBillFee", costinfo.WayBillFee); //运费
query.SetParameter("ProcessingFee", costinfo.ProcessingFee); //操作费
query.ExecuteUpdate();
//添加 已删除运单异常记录
query = NHibernateSession.CreateSQLQuery("insert into DelExpressNoExceptionDetail (ExpressNo,ExceptionType,ExceptionMsg,CreateTime,LoadBillNO,Status,WayBillCostID,PostingTime,DelTime) values (:ExpressNo,:ExceptionType,:ExceptionMsg,:CreateTime,:LoadBillNO,:Status,:WayBillCostID,:PostingTime,:DelTime)");
query.SetParameter("ExpressNo", model.ExpressNo); //运单号
query.SetParameter("ExceptionType", model.ExceptionType); //异常类型
query.SetParameter("ExceptionMsg", model.ExceptionMsg); //异常说明
query.SetParameter("CreateTime", DateTime.Now); //创建时间
query.SetParameter("LoadBillNO", model.LoadBillNum); //提单号
query.SetParameter("Status", 0); //状态
query.SetParameter("WayBillCostID", model.WayBillCostID); //异常成本ID
query.SetParameter("PostingTime", model.PostingTime); //收寄日期
query.SetParameter("DelTime", DateTime.Now); //删除时间
query.ExecuteUpdate();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
}
return(1);
}
public IList <decimal> GetUivalidationIdList(IList <decimal> personIdList)
{
IList <decimal> UiValidationGroupIdList = new List <decimal>();
string SQLCommand = string.Empty;
if (personIdList.Count < this.operationBatchSizeValue && this.operationBatchSizeValue < 2100)
{
SQLCommand = @" select prsTA_UIValidationGroupID from TA_PersonTASpec as pts
inner join TA_Person prs
on pts.prsTA_ID = prs.Prs_ID
inner join TA_UIValidationGroup as uvg
on pts.prsTA_UIValidationGroupID = uvg.UIValGrp_ID
where pts.prsTA_ID in (:personlist) and prs.Prs_Active =1 and prs.prs_IsDeleted =0
and prsTA_UIValidationGroupID is not null
group by prsTA_UIValidationGroupID
";
UiValidationGroupIdList = NHibernateSession.CreateSQLQuery(SQLCommand)
.SetParameterList("personlist", personIdList.ToArray()).List <decimal>();
}
else
{
TempRepository tempRep = new TempRepository(false);
string operationGUID = tempRep.InsertTempList(personIdList);
SQLCommand = @"select prsTA_UIValidationGroupID from TA_PersonTASpec as pts
inner join TA_Person prs
on pts.prsTA_ID = prs.Prs_ID
Inner Join TA_Temp temp
on prs.Prs_ID=temp.temp_ObjectID
inner join TA_UIValidationGroup as uvg
on pts.prsTA_UIValidationGroupID = uvg.UIValGrp_ID
where prs.Prs_Active =1 and prs.prs_IsDeleted =0 and temp_OperationGUID =:operationGUID
and prsTA_UIValidationGroupID is not null
group by prsTA_UIValidationGroupID
";
UiValidationGroupIdList = NHibernateSession.CreateSQLQuery(SQLCommand)
.SetParameter("operationGUID", operationGUID)
.List <decimal>();
tempRep.DeleteTempList(operationGUID);
}
return(UiValidationGroupIdList);
}
public IList <WayBillException> GetExceByID(string exceID)
{
//--客户名称 --收寄日期 --运单号 --提单号 --重量 --邮政邮政 -- 邮政操作费 --邮政其他费用（暂时没有） --成本状态 --客户运费 --客户操作费 --客户其他费用（暂时没有） 收入状态 异常原因
string column = @"a.ExceptionType ,
b.PostingTime,b.ExpressNo,b.BatchNO as LoadBillNum,b.Weight,b.WayBillFee,b.ProcessingFee,b.PayStatus as CostStatus,
c.ExpressFee,c.OperateFee,c.PayStatus as InComeStatus,
d.Cus_Name as CusName ";
string sql = @" FROM ExpressNoExceptionDetail a
left join WayBillCost b on a.WayBillCostID=b.ID
left JOIN WayBillInCome c on b.ExpressNo=c.ExpressNo
left JOIN CustomerInfo d on c.CustomerID=d.ID
where a.ID=" + exceID + " ";
var query = NHibernateSession.CreateSQLQuery(string.Format("select {0} {1} ", column, sql));
//StatModel model = new StatModel();
//var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillReconciliation))).SetFirstResult(pageIndex * pageSize).SetMaxResults(pageSize).List<WayBillReconciliation>().ToList();
//query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillReconciliation))).List<WayBillReconciliation>().ToList();
var list = query.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean(typeof(WayBillException))).List <WayBillException>().ToList();
return(list);
}
public CurrencyRate GetLast(Currency currency)
{
string query = @"select CR.Id from CurrencyRate CR
inner join (select max([Date]) as MaxDate, CurrencyId
from CurrencyRate
group by CurrencyId) LCR on CR.Date = LCR.MaxDate and CR.CurrencyId = LCR.CurrencyId
where CR.CurrencyId = :CurrencyId";
IQuery q = NHibernateSession.CreateSQLQuery(query);
q.SetInt32("CurrencyId", currency.ID);
int tempid = q.UniqueResult <int>();
ICriteria crit = GetCriteria();
crit.Add(Expression.Eq("ID", tempid));
return(crit.UniqueResult <CurrencyRate>());
}
private bool CanErase(int id)
{
string hql = "SELECT R.Id from mem_Rol R where R.SiteId = :SiteId ";
IQuery q = NHibernateSession.CreateSQLQuery(hql);
q.SetInt32("SiteId", id);
string hql2 = "SELECT F.Id from mem_Function F where F.SiteId = :SiteId ";
IQuery q2 = NHibernateSession.CreateSQLQuery(hql2);
q2.SetInt32("SiteId", id);
if (q.List <int>().Count > 0 || q2.List <int>().Count > 0)
{
return(false);
}
return(true);
}
public IEnumerable <DichVu21DonGia> GetDichVu21(int month, int year, long?cskcbId)
{
StringBuilder sqlQuery = new StringBuilder();
sqlQuery.AppendFormat(
"SELECT dichvu21.ID Id, bhyt21.MADVKT MaDichVu, dichvu21.TEN_DICHVU21 TenDichVu,bhyt21.SOLUONG SoLuong, bhyt21.DONGIA DonGia, bhyt21.THANHTIEN ThanhTien " +
"FROM BHYT_21 bhyt21, COCAUGIA_DICHVU21 dichvu21 " +
"WHERE SUBSTR(bhyt21.MADVKT, -4, 4) = SUBSTR(dichvu21.MA_DICHVU21, -4, 4)" +
"AND bhyt21.THANG_QT={0} AND bhyt21.NAM_QT={1}", month, year);
if (cskcbId != null)
{
sqlQuery.AppendFormat(" AND bhyt21.COSOKCB_ID = {0}", cskcbId);
}
var result = NHibernateSession.CreateSQLQuery(sqlQuery.ToString())
.SetResultTransformer(Transformers.AliasToBean <DichVu21DonGia>())
.List <DichVu21DonGia>();
return(result);
}
