using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Infrastructure.Query;
|
|
namespace CY.SQLDAL
|
{
|
public class OA_CustomerAccessRecordDAL : IOA_CustomerAccessRecordDAL
|
{
|
private Database _dataBase = null;
|
|
public OA_CustomerAccessRecordDAL()
|
{
|
_dataBase = new Database();
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_CustomerAccessRecord trueModel = model as Model.OA_CustomerAccessRecord;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@CutomerType",trueModel.CutomerType),
|
new SqlParameter("@CustomerId",trueModel.CustomerId),
|
new SqlParameter("@AccessTypeId",trueModel.AccessTypeId),
|
new SqlParameter("@StuffId",trueModel.StuffId),
|
new SqlParameter("@AccesserId",trueModel.AccesserId),
|
new SqlParameter("@StartTime",trueModel.StartTime),
|
new SqlParameter("@Receiver",trueModel.Receiver),
|
new SqlParameter("@AccessContent",trueModel.AccessContent),
|
new SqlParameter("@TurnoverIntention",trueModel.TurnoverIntention),
|
new SqlParameter("@EndTime",trueModel.EndTime),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CustomerAccessRecord_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_CustomerAccessRecord trueModel = model as Model.OA_CustomerAccessRecord;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@CutomerType",trueModel.CutomerType),
|
new SqlParameter("@CustomerId",trueModel.CustomerId),
|
new SqlParameter("@AccessTypeId",trueModel.AccessTypeId),
|
new SqlParameter("@StuffId",trueModel.StuffId),
|
new SqlParameter("@AccesserId",trueModel.AccesserId),
|
new SqlParameter("@StartTime",trueModel.StartTime),
|
new SqlParameter("@Receiver",trueModel.Receiver),
|
new SqlParameter("@AccessContent",trueModel.AccessContent),
|
new SqlParameter("@TurnoverIntention",trueModel.TurnoverIntention),
|
new SqlParameter("@EndTime",trueModel.EndTime),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CustomerAccessRecord_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="id"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_CustomerAccessRecord trueModel = model as Model.OA_CustomerAccessRecord;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CustomerAccessRecord_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 分页查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="pagination"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CustomerAccessRecord> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 访问记录
|
/// </summary>
|
/// <param name="pa"></param>
|
/// <param name="CustomerID"></param>
|
/// <param name="Type"></param>
|
/// <param name="MemberId"></param>
|
/// <param name="RecordType"></param>
|
/// <param name="RecordStartTime"></param>
|
/// <param name="RecordEndTime"></param>
|
/// <param name="RecordVisiter"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CustomerAccessRecord> SelectModelPage(Infrastructure.Query.Pagination pa, string CustomerID, string Type, Guid MemberId, string RecordType, DateTime? RecordStartTime, DateTime? RecordEndTime, string RecordVisiter)
|
{
|
string condition = " And 1=1 ";
|
if (!string.IsNullOrEmpty(RecordType))
|
condition += " And AccessTypeId ='" + RecordType + "' ";
|
if (RecordStartTime != null)
|
condition += " And CreateTime >='" + RecordStartTime + "' ";
|
if (RecordEndTime != null)
|
condition += " And CreateTime <='" + RecordEndTime.Value.AddDays(1) + "' ";
|
if (!string.IsNullOrEmpty(RecordVisiter))
|
condition += " And AccesserId ='" + RecordVisiter + "' ";
|
|
string selTarget = " a.*,c.Name AS AccessType,d.Name AS StuffName ";
|
string fromSource = " (SELECT * FROM dbo.OA_CustomerAccessRecord WHERE CustomerId='" + CustomerID + "' and CutomerType ='" + Type + "' and Remark='" + MemberId + "' " + condition + ") AS a LEFT JOIN dbo.Sys_Dictionary AS c ON c.DicType='客户访问类型' AND a.AccessTypeId=c.MeanValue LEFT JOIN dbo.OA_Staff AS d ON a.StuffId=d.Keyid ";
|
return _dataBase.SelectModelPage<Model.OA_CustomerAccessRecord>(pa, selTarget, fromSource, " a.LastUpdateTime DESC");
|
}
|
|
/// <summary>
|
/// 打印访问记录
|
/// </summary>
|
/// <param name="pa"></param>
|
/// <param name="Keyids"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CustomerAccessRecord> SelectModelPage(Infrastructure.Query.Pagination pa, string Keyids)
|
{
|
try
|
{
|
string selTarget = " a.*,c.Name AS AccessType,d.Name AS StuffName ";
|
string fromSource = " (SELECT * FROM dbo.OA_CustomerAccessRecord WHERE Keyid in (" + Keyids + ") ) AS a LEFT JOIN dbo.Sys_Dictionary AS c ON c.DicType='客户访问类型' AND a.AccessTypeId=c.MeanValue LEFT JOIN dbo.OA_Staff AS d ON a.StuffId=d.Keyid ";
|
return _dataBase.SelectModelPage<Model.OA_CustomerAccessRecord>(pa, selTarget, fromSource, " a.LastUpdateTime DESC");
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CustomerAccessRecord> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
throw new NotImplementedException();
|
}
|
|
/// <summary>
|
/// 员工绩效列表
|
/// </summary>
|
/// <param name="pa"></param>
|
/// <param name="FirmId"></param>
|
/// <param name="StuffId"></param>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CustomerAccessRecord> getAllIntentionAccessList(Pagination pa, Guid FirmId, string StaffName, string StaffStatus, DateTime? StartTime, DateTime? EndTime)
|
{
|
string Condition = " where 1=1 ";
|
if (!string.IsNullOrEmpty(StaffName))
|
{
|
Condition += " and a.Name = '" + StaffName + "' ";
|
}
|
if (!string.IsNullOrEmpty(StaffStatus))
|
{
|
Condition += " and a.Status = '" + StaffStatus + "' ";
|
}
|
string conditon1 = " where 1=1 and FirmId='" + FirmId + "' ";
|
string conditon2 = " where 1=1 and FirmId='" + FirmId + "' ";
|
if (StartTime != null)
|
{
|
conditon1 += " and CreateTime >= '" + StartTime.ToString() + "' ";
|
conditon2 += " and CreateTime < '" + StartTime.ToString() + "' ";
|
}
|
if (EndTime != null)
|
{
|
conditon1 += " and CreateTime <= '" + EndTime.Value.AddDays(1).ToString() + "' ";
|
}
|
|
string selectTarget = @" b.*,a.Keyid AS StuffKeyId,a.Name AS StuffName ,c.NowMoney AS NowMoney,d.OldMoney AS OldMoney,e.AllMoney AS AllMoney,c.AllCustom AS AllCustom,g.AllIntention AS AllIntention";
|
string fromSouce = "( SELECT * FROM dbo.OA_Staff WHERE FirmId='" + FirmId + "') AS a LEFT JOIN " +
|
" (SELECT StuffId , SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
|
" SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
|
" SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
|
" SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
|
" SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
|
" SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial, " +
|
" COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY StuffId ) AS b ON a.Keyid = b.stuffid " +
|
" LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS NowMoney ,SUM(CASE when OrderMoney >= 0 THEN 1 ELSE 0 END) AS AllCustom FROM dbo.OA_CorporateClients " + conditon1 + " GROUP BY BusinessManagerId ) AS c ON a.Keyid = c.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS OldMoney FROM dbo.OA_CorporateClients " + conditon2 + " GROUP BY BusinessManagerId ) AS d ON a.Keyid = d.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS AllMoney FROM dbo.OA_CorporateClients where FirmId='" + FirmId + "' GROUP BY BusinessManagerId ) AS e ON a.Keyid = e.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(CASE when Keyid >0 THEN 1 ELSE 0 END) AS AllIntention FROM dbo.OA_IntentionCustomer " + conditon1 + " GROUP BY BusinessManagerId ) AS g ON a.Keyid = g.BusinessManagerId " + Condition;
|
|
return _dataBase.SelectModelPage<Model.OA_CustomerAccessRecord>(pa, selectTarget, fromSouce, "b.StuffId ");
|
|
}
|
|
/// <summary>
|
/// 员工绩效统计
|
/// </summary>
|
/// <param name="pa"></param>
|
/// <param name="FirmId"></param>
|
/// <param name="StaffName"></param>
|
/// <param name="StaffStatus"></param>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public Model.OA_CustomerAccessRecord SumAllIntentionAccessList(Pagination pa, Guid FirmId, string StaffName, string StaffStatus, DateTime? StartTime, DateTime? EndTime)
|
{
|
string Condition = " where 1=1 ";
|
if (!string.IsNullOrEmpty(StaffName))
|
{
|
Condition += " and a.Name = '" + StaffName + "' ";
|
}
|
if (!string.IsNullOrEmpty(StaffStatus))
|
{
|
Condition += " and a.Status = '" + StaffStatus + "' ";
|
}
|
string conditon1 = " where 1=1 and FirmId='" + FirmId + "' ";
|
string conditon2 = " where 1=1 and FirmId='" + FirmId + "' ";
|
if (StartTime != null)
|
{
|
conditon1 += " and CreateTime >= '" + StartTime.ToString() + "' ";
|
conditon2 += " and CreateTime < '" + StartTime.ToString() + "' ";
|
}
|
if (EndTime != null)
|
{
|
conditon1 += " and CreateTime <= '" + EndTime.Value.AddDays(1).ToString() + "' ";
|
}
|
|
string selectTarget = @" sum(b.Nupdoor) as Nupdoor,sum(b.Nphone) as Nphone,sum(b.Nqq) as Nqq,sum(b.Nmessage) as Nmessage,sum(b.NDirectMail) as NDirectMail,sum(b.Nemial) as Nemial, sum(c.NowMoney) AS NowMoney,sum(d.OldMoney) AS OldMoney,sum(e.AllMoney) AS AllMoney,sum(c.AllCustom) AS AllCustom,sum(g.AllIntention) AS AllIntention,sum(b.Nall) AS Nall";
|
string fromSouce = "( SELECT * FROM dbo.OA_Staff WHERE FirmId='" + FirmId + "') AS a LEFT JOIN " +
|
" (SELECT StuffId , SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
|
" SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
|
" SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
|
" SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
|
" SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
|
" SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial, " +
|
" COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY StuffId ) AS b ON a.Keyid = b.stuffid " +
|
" LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS NowMoney ,SUM(CASE when OrderMoney >= 0 THEN 1 ELSE 0 END) AS AllCustom FROM dbo.OA_CorporateClients " + conditon1 + " GROUP BY BusinessManagerId ) AS c ON a.Keyid = c.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS OldMoney FROM dbo.OA_CorporateClients " + conditon2 + " GROUP BY BusinessManagerId ) AS d ON a.Keyid = d.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS AllMoney FROM dbo.OA_CorporateClients where FirmId='" + FirmId + "' GROUP BY BusinessManagerId ) AS e ON a.Keyid = e.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(CASE when Keyid >0 THEN 1 ELSE 0 END) AS AllIntention FROM dbo.OA_IntentionCustomer " + conditon1 + " GROUP BY BusinessManagerId ) AS g ON a.Keyid = g.BusinessManagerId " + Condition;
|
|
IList<Model.OA_CustomerAccessRecord> m_OA_CustomerAccessRecordList = _dataBase.SelectModel<Model.OA_CustomerAccessRecord>(selectTarget, fromSouce);
|
if (m_OA_CustomerAccessRecordList != null && m_OA_CustomerAccessRecordList.Count > 0)
|
{
|
return m_OA_CustomerAccessRecordList[0];
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 员工绩效统计-核算工资
|
/// </summary>
|
/// <param name="pa"></param>
|
/// <param name="FirmId"></param>
|
/// <param name="StaffName"></param>
|
/// <param name="StaffStatus"></param>
|
/// <param name="StartTime"></param>
|
/// <param name="EndTime"></param>
|
/// <returns></returns>
|
public Model.OA_CustomerAccessRecord SumAccoutingWages(Pagination pa, Guid FirmId, string StaffName, string StaffStatus, DateTime? StartTime, DateTime? EndTime)
|
{
|
string Condition = " 1=1 ";
|
if (!string.IsNullOrEmpty(StaffName))
|
{
|
Condition += " and c.Name = '" + StaffName + "' ";
|
}
|
Condition += " and c.FirmId='" + FirmId + "' ";
|
if (StartTime != null)
|
{
|
Condition += " and a.CreateTime >= '" + StartTime.ToString() + "' ";
|
}
|
if (EndTime != null)
|
{
|
Condition += " and a.CreateTime <= '" + EndTime.Value.AddDays(1).ToString() + "' ";
|
}
|
|
string selectTarget = @" SUM(a.SumPrice) as NowMoney ";
|
|
string fromSouce = " dbo.EC_OrderBasic AS a LEFT JOIN dbo.EC_OrderExtend AS b ON a.Keyid = b.Keyid LEFT JOIN dbo.OA_Staff AS c ON b.BusinessManagerId = c.Keyid ";
|
|
IList<Model.OA_CustomerAccessRecord> m_OA_CustomerAccessRecordList = _dataBase.SelectModel<Model.OA_CustomerAccessRecord>(selectTarget, fromSouce, Condition);
|
if (m_OA_CustomerAccessRecordList != null && m_OA_CustomerAccessRecordList.Count > 0)
|
{
|
return m_OA_CustomerAccessRecordList[0];
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 员工绩效列表-打印页面
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <param name="StaffIds"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CustomerAccessRecord> getAllIntentionAccessListByIds(Guid FirmId, string StaffIds)
|
{
|
string Condition = "";
|
if (!string.IsNullOrEmpty(StaffIds))
|
{
|
Condition += " and Keyid in (" + StaffIds + ") ";
|
}
|
string conditon1 = " where 1=1 and FirmId='" + FirmId + "' ";
|
string conditon2 = " where 1=1 and FirmId='" + FirmId + "' ";
|
|
string selectTarget = @" b.*,a.Name AS StuffName ,c.NowMoney AS NowMoney,d.OldMoney AS OldMoney,e.AllMoney AS AllMoney,c.AllCustom AS AllCustom,g.AllIntention AS AllIntention";
|
string fromSouce = "( SELECT * FROM dbo.OA_Staff WHERE FirmId='" + FirmId + "' " + Condition + ") AS a LEFT JOIN " +
|
" (SELECT StuffId , SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
|
" SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
|
" SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
|
" SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
|
" SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
|
" SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial, " +
|
" COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord GROUP BY StuffId ) AS b ON a.Keyid = b.stuffid " +
|
" LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS NowMoney ,SUM(CASE when OrderMoney >= 0 THEN 1 ELSE 0 END) AS AllCustom FROM dbo.OA_CorporateClients " + conditon1 + " GROUP BY BusinessManagerId ) AS c ON a.Keyid = c.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS OldMoney FROM dbo.OA_CorporateClients " + conditon2 + " GROUP BY BusinessManagerId ) AS d ON a.Keyid = d.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(OrderMoney) AS AllMoney FROM dbo.OA_CorporateClients where FirmId='" + FirmId + "' GROUP BY BusinessManagerId ) AS e ON a.Keyid = e.BusinessManagerId LEFT JOIN ( SELECT BusinessManagerId, SUM(CASE when Keyid >0 THEN 1 ELSE 0 END) AS AllIntention FROM dbo.OA_IntentionCustomer " + conditon1 + " GROUP BY BusinessManagerId ) AS g ON a.Keyid = g.BusinessManagerId ";
|
|
return _dataBase.SelectModel<Model.OA_CustomerAccessRecord>(selectTarget, fromSouce, "");
|
|
}
|
}
|
}
|
