/**
|
* OA_CorporateClientsDAL.cs
|
*
|
* 功 能： 合作客户数据访问接口实现类
|
* 类 名： OA_CorporateClientsDAL
|
*
|
* Ver 变更日期 负责人 变更内容
|
* ───────────────────────────────────
|
* V0.01 2013-4-2 15:11 吴辉 初版
|
* V0.02 2013-5-29 17:40 吴崎均 增加转换在线会员为厂商客户方法
|
*
|
*
|
*
|
*
|
*
|
*
|
*/
|
using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using CY.IDAL;
|
using System.Data.SqlClient;
|
using System.Data;
|
using CY.Model;
|
using CY.IBaseDAL;
|
using System.Transactions;
|
using CY.Infrastructure.Common;
|
using System.Xml;
|
using System.Data.SqlTypes;
|
|
namespace CY.SQLDAL
|
{
|
/// <summary>
|
/// 合作客户数据访问接口实现类
|
/// </summary>
|
public class OA_CorporateClientsDAL : IOA_CorporateClientsDAL
|
{
|
|
private Database _dataBase = null;
|
#region 常量
|
/// <summary>
|
/// 查询目标
|
/// </summary>
|
const string SELECTTARGET = " t.* ";
|
/// <summary>
|
/// 查询来源
|
/// </summary>
|
const string FROMSOUCEBEFORE = " ( select a.*,f.*,b.Mobile as Mobile,b.Email as Email,b.CompanyPhone as CompanyPhone ,d_Shifouwanjie.Name as ShifouwanjieName,b.Province as Province,b.City as City ,b.County as County,d_DegreeImportanId.Name as DegreeImportanIdName,k.Name as CreditIdName,b.BusinessManagers as BusinessManagers,l.Name as SourcesInfoIdName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName from [OA_CorporateClients] a join [OA_CustomerCommunications] b on a.Keyid=b.Keyid Left Join Sys_Dictionary as d_DegreeImportanId On (a.[DegreeImportanId]=d_DegreeImportanId.Keyid) Left Join Sys_Dictionary as k On (a.[CreditId]=k.Keyid) Left Join Sys_Dictionary as l On (a.SourcesInfoId=l.Keyid) LEFT JOIN dbo.OA_Staff AS d_OA_Staff ON a.BusinessManagerId=d_OA_Staff.Keyid LEFT JOIN dbo.OA_Staff AS d_OA_Staff1 ON a.AccountManagerId=d_OA_Staff1.Keyid " +
|
" Left Join Sys_Dictionary as d_Shifouwanjie On(d_Shifouwanjie.DicType= '客户完结状态' and a.[Shifouwanjie]= d_Shifouwanjie.MeanValue) " +
|
" LEFT JOIN( SELECT CustomerId AS Fid,Remark as XRemark, 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 WHERE CutomerType ='True' GROUP BY CustomerId,Remark) AS f ON CONVERT(VARCHAR(200),a.CustomerId)=f.Fid and a.FirmId = f.XRemark where 0=0 ";
|
const string FROMSOUCEEND = ") as t ";
|
/// <summary>
|
/// 分页默认排序字段
|
/// </summary>
|
const string ORDERBY = " Keyid DESC ";
|
#endregion
|
public OA_CorporateClientsDAL()
|
{
|
_dataBase = new Database();
|
}
|
|
public OA_CorporateClientsDAL(Database database)
|
{
|
_dataBase = database;
|
}
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClients;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
SqlParameter par = null;
|
if (trueModel.LastOrderTime.HasValue)
|
{
|
par = new SqlParameter("@LastOrderTime", trueModel.LastOrderTime.Value);
|
}
|
else
|
{
|
par = new SqlParameter("@LastOrderTime", DBNull.Value);
|
}
|
SqlParameter par1 = null;
|
if (trueModel.ExpireTime.HasValue)
|
{
|
par1 = new SqlParameter("@ExpireTime", trueModel.ExpireTime.Value);
|
}
|
else
|
{
|
par1 = new SqlParameter("@ExpireTime", DBNull.Value);
|
}
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@FirmId",trueModel.FirmId),
|
new SqlParameter("@CustomerId",trueModel.CustomerId),
|
new SqlParameter("@CompanyName",trueModel.CompanyName),
|
new SqlParameter("@CustomerIndustriesId",trueModel.CustomerIndustriesId),
|
new SqlParameter("@CustomerTypeId",trueModel.CustomerTypeId),
|
new SqlParameter("@SourcesInfoId",trueModel.SourcesInfoId),
|
new SqlParameter("@DegreeImportanId",trueModel.DegreeImportanId),
|
new SqlParameter("@AccountManagerId",trueModel.AccountManagerId),
|
new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@CreditId",trueModel.CreditId),
|
new SqlParameter("@LoginPhone",trueModel.LoginPhone),
|
new SqlParameter("@CorporateWebsite",trueModel.CorporateWebsite),
|
new SqlParameter("@BusinessAnalysisId",trueModel.BusinessAnalysisId),
|
new SqlParameter("@Credit",trueModel.Credit),
|
new SqlParameter("@IsLoginCorporateWeb",trueModel.IsLoginCorporateWeb),
|
new SqlParameter("@Bank",trueModel.Bank),
|
new SqlParameter("@TaxID",trueModel.TaxID),
|
new SqlParameter("@AccountID",trueModel.AccountID),
|
new SqlParameter("@OrderCount",trueModel.OrderCount),
|
new SqlParameter("@OrderMoney",trueModel.OrderMoney),
|
par,
|
new SqlParameter("@Prepayments",trueModel.Prepayments),
|
new SqlParameter("@IsOutsourcing",trueModel.IsOutsourcing),
|
new SqlParameter("@OutVendorName",trueModel.OutVendorName),
|
new SqlParameter("@IsPriority",trueModel.IsPriority),
|
new SqlParameter("@CumulativePrepayments",trueModel.CumulativePrepayments),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator),
|
new SqlParameter("@Remark",trueModel.Remark),
|
new SqlParameter("@InquiryId",trueModel.InquiryId),
|
new SqlParameter("@MemberId",trueModel.MemberId) ,
|
new SqlParameter("@LoginPwd",trueModel.LoginPwd) ,
|
new SqlParameter("@OutRate",trueModel.OutRate) ,
|
new SqlParameter("@Paytime",trueModel.Paytime) ,
|
new SqlParameter("@Paydays",trueModel.Paydays) ,
|
new SqlParameter("@Biddingcompany",trueModel.Biddingcompany) ,
|
new SqlParameter("@Contracttype",trueModel.Contracttype) ,
|
new SqlParameter("@Contractmoney",trueModel.Contractmoney) ,
|
par1,
|
new SqlParameter("@Jigoudaima",trueModel.Jigoudaima) ,
|
new SqlParameter("@Piaoju",trueModel.Piaoju) ,
|
new SqlParameter("@Kaipiaoziliao",trueModel.Kaipiaoziliao) ,
|
new SqlParameter("@Expiretype",trueModel.Expiretype) ,
|
new SqlParameter("@Shifouwanjie",trueModel.Shifouwanjie) ,
|
new SqlParameter("@Note",trueModel.Note) ,
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClients_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_CorporateClients trueModel = model as Model.OA_CorporateClients;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
SqlParameter par = null;
|
if (trueModel.LastOrderTime.HasValue)
|
{
|
par = new SqlParameter("@LastOrderTime", trueModel.LastOrderTime.Value);
|
}
|
else
|
{
|
par = new SqlParameter("@LastOrderTime", DBNull.Value);
|
}
|
SqlParameter par1 = null;
|
if (trueModel.ExpireTime.HasValue)
|
{
|
par1 = new SqlParameter("@ExpireTime", trueModel.ExpireTime.Value);
|
}
|
else
|
{
|
par1 = new SqlParameter("@ExpireTime", DBNull.Value);
|
}
|
SqlParameter Biddingcompany = null;
|
if (trueModel.Biddingcompany.HasValue)
|
{
|
Biddingcompany = new SqlParameter("@Biddingcompany", trueModel.Biddingcompany.Value);
|
}
|
else
|
{
|
Biddingcompany = new SqlParameter("@Biddingcompany", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@FirmId",trueModel.FirmId),
|
new SqlParameter("@CustomerId",trueModel.CustomerId ?? 10000),
|
new SqlParameter("@CompanyName",trueModel.CompanyName??""),
|
new SqlParameter("@CustomerIndustriesId",trueModel.CustomerIndustriesId??0),
|
new SqlParameter("@CustomerTypeId",trueModel.CustomerTypeId??0),
|
new SqlParameter("@SourcesInfoId",trueModel.SourcesInfoId??0),
|
new SqlParameter("@DegreeImportanId",trueModel.DegreeImportanId??0),
|
new SqlParameter("@AccountManagerId",trueModel.AccountManagerId??0),
|
new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId??0),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@CreditId",trueModel.CreditId??0),
|
new SqlParameter("@LoginPhone",trueModel.LoginPhone??""),
|
new SqlParameter("@CorporateWebsite",trueModel.CorporateWebsite??""),
|
new SqlParameter("@BusinessAnalysisId",trueModel.BusinessAnalysisId??0),
|
new SqlParameter("@Credit",trueModel.Credit??0),
|
new SqlParameter("@IsLoginCorporateWeb",trueModel.IsLoginCorporateWeb),
|
new SqlParameter("@Bank",trueModel.Bank??""),
|
new SqlParameter("@TaxID",trueModel.TaxID??""),
|
new SqlParameter("@AccountID",trueModel.AccountID??""),
|
new SqlParameter("@OrderCount",trueModel.OrderCount??0),
|
new SqlParameter("@OrderMoney",trueModel.OrderMoney??0),
|
par,
|
new SqlParameter("@Prepayments",trueModel.Prepayments??0),
|
new SqlParameter("@IsOutsourcing",trueModel.IsOutsourcing),
|
new SqlParameter("@OutVendorName",trueModel.OutVendorName??""),
|
new SqlParameter("@IsPriority",trueModel.IsPriority),
|
new SqlParameter("@CumulativePrepayments",trueModel.CumulativePrepayments??0),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
new SqlParameter("@Operator",trueModel.Operator??""),
|
new SqlParameter("@Remark",trueModel.Remark??""),
|
new SqlParameter("@InquiryId",trueModel.InquiryId),
|
new SqlParameter("@MemberId",trueModel.MemberId) ,
|
new SqlParameter("@LoginPwd",trueModel.LoginPwd ?? "") ,
|
new SqlParameter("@OutRate",trueModel.OutRate ?? 0),
|
new SqlParameter("@Paytime",trueModel.Paytime) ,
|
new SqlParameter("@Paydays",trueModel.Paydays) ,
|
new SqlParameter("@Biddingcompany",trueModel.Biddingcompany??0) ,
|
new SqlParameter("@Contracttype",trueModel.Contracttype??0) ,
|
new SqlParameter("@Contractmoney",trueModel.Contractmoney??0) ,
|
par1,
|
new SqlParameter("@Jigoudaima",trueModel.Jigoudaima??"") ,
|
new SqlParameter("@Piaoju",trueModel.Piaoju??0) ,
|
new SqlParameter("@Kaipiaoziliao",trueModel.Kaipiaoziliao??"") ,
|
new SqlParameter("@Expiretype",trueModel.Expiretype??0) ,
|
new SqlParameter("@Shifouwanjie",trueModel.Shifouwanjie??0) ,
|
new SqlParameter("@Note",trueModel.Note??"") ,
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClients_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 转移客户
|
/// </summary>
|
/// <param name="BussinessManager"></param>
|
/// <param name="CostomManager"></param>
|
/// <param name="type"></param>
|
/// <param name="keyids"></param>
|
/// <returns></returns>
|
public bool ShiftIntOrCus(int? BussinessManager, int? CostomManager, string type, string keyids)
|
{
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@BussinessManager",BussinessManager),
|
new SqlParameter("@CostomManager",CostomManager),
|
new SqlParameter("@type",type) ,
|
new SqlParameter("@keyids",keyids)
|
};
|
try
|
{
|
_dataBase.Query("sp_ShiftIntOrCos_BussinessAndCustom", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 删除
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
|
{
|
Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClients;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid)
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClients_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_CorporateClients> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
if (null == pagination || null == query || null == query.Criteria || 1 > query.Criteria.Count)
|
return null;
|
//query.Criteria 首个元素必须是排序字段，其值为结果排序字段
|
|
int maxParamIndex = query.Criteria.Count - 1;//最大索引
|
|
string[] orderbys = new string[] { ORDERBY };
|
string resultOrderBy = "";//结果集排序方式
|
|
if ("@orderBy".Equals(query.Criteria[maxParamIndex].PropertyName))
|
{
|
orderbys = string.Format("{0}", query.Criteria[maxParamIndex].Value).Split(',');
|
resultOrderBy = 1 == orderbys.Length ? resultOrderBy : orderbys[1];
|
}
|
string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE, query.Criteria[0].Value, FROMSOUCEEND);//拼装条件
|
return _dataBase.SelectModelPage<Model.OA_CorporateClients>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
|
}
|
|
/// <summary>
|
/// 统计金额
|
/// </summary>
|
/// <param name="query"></param>
|
/// <param name="pagination"></param>
|
/// <returns></returns>
|
public Model.OA_CorporateClients SumRecordMoney(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
|
{
|
if (null == pagination || null == query || null == query.Criteria || 1 > query.Criteria.Count)
|
return null;
|
//query.Criteria 首个元素必须是排序字段，其值为结果排序字段
|
|
int maxParamIndex = query.Criteria.Count - 1;//最大索引
|
|
string[] orderbys = new string[] { ORDERBY };
|
string resultOrderBy = "";//结果集排序方式
|
|
if ("@orderBy".Equals(query.Criteria[maxParamIndex].PropertyName))
|
{
|
orderbys = string.Format("{0}", query.Criteria[maxParamIndex].Value).Split(',');
|
resultOrderBy = 1 == orderbys.Length ? resultOrderBy : orderbys[1];
|
}
|
string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE, query.Criteria[0].Value, FROMSOUCEEND);//拼装条件
|
IList<Model.OA_CorporateClients> m_OA_CorporateClientsList = _dataBase.SelectModel<Model.OA_CorporateClients>(" sum(t.Prepayments) as SumAllPrepayments,sum(t.Credit) as SumAllCredit,sum(t.OrderMoney) as SumAllOrderMoney", fromSouce);
|
if (m_OA_CorporateClientsList != null && m_OA_CorporateClientsList.Count > 0)
|
return m_OA_CorporateClientsList[0];
|
else
|
return null;
|
}
|
|
/// <summary>
|
/// 分页查询外协厂商
|
/// </summary>
|
/// <param name="pagination"></param>
|
/// <param name="MemberId"></param>
|
/// <param name="OutVendorName"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CorporateClients> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid MemberId, string OutVendorName)
|
{
|
if (null == pagination || null == MemberId)
|
return null;
|
//query.Criteria 首个元素必须是排序字段，其值为结果排序字段
|
string condition = " MemberId='" + MemberId + "' and IsOutsourcing ='True'";
|
if (!string.IsNullOrEmpty(OutVendorName))
|
{
|
condition += " and OutVendorName like '%" + OutVendorName + "%'";
|
}
|
return _dataBase.SelectModelPage<Model.OA_CorporateClients>(pagination, "*", "OA_CorporateClients", " IsPriority DESC ", " IsPriority DESC ", condition);
|
}
|
|
/// <summary>
|
/// 全部查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CorporateClients> SelectAllModel(Infrastructure.Query.Query query)
|
{
|
return _dataBase.SelectModel<Model.OA_CorporateClients>(" * ", " OA_CorporateClients ") as IList<Model.OA_CorporateClients>;//执行查询
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="Keyid">编号</param>
|
/// <returns></returns>
|
public OA_CorporateClients SelectModelByKeyid(Guid Keyid)
|
{
|
if (Keyid == null)
|
return null;//错误数据返会空
|
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" a.*,b.QQ,b.CompanyPhone,b.Mobile,b.BusinessManagers ", " dbo.OA_CorporateClients AS a LEFT JOIN dbo.OA_CustomerCommunications AS b ON a.Keyid = b.Keyid ", string.Format(" a.Keyid='{0}'", Keyid)) as IList<OA_CorporateClients>;//执行查询
|
|
return (null == result || result.Count == 0) ? null : result[0];//返回结果
|
}
|
|
/// <summary>
|
/// 单个查询详细
|
/// </summary>
|
/// <param name="Keyid">编号</param>
|
/// <returns></returns>
|
public OA_CorporateClients SelectModelDetailByKeyid(Guid Keyid)
|
{
|
if (Keyid == null)
|
return null;//错误数据返会空
|
|
string selectTarget = "t.*,d_SourcesInfoId.Name as SourcesInfoIdName,d_CustomerIndustriesId.Name as CustomerIndustriesIdName,d_CustomerTypeId.Name as CustomerTypeIdName,d_CreditId.Name as CreditIdName,d_Shifouwanjie.Name as ShifouwanjieName ,d_Piaoju.Name as PiaojuName ,d_Expiretype.Name as ExpiretypeName,d_Contracttype.Name as ContracttypeName,d_Biddingcompany.CompanyName as BiddingcompanyName ,d_PaytimeId.Name as Paytimename ,d_DegreeImportanId.Name as DegreeImportanIdName,d_BusinessAnalysisId.Name as BusinessAnalysisIdName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName";
|
string fromSouce = "(select * From OA_CorporateClients Where Keyid='" + Keyid + "' ) as t " +
|
"Left Join Sys_Dictionary as d_SourcesInfoId On(t.[SourcesInfoId]=d_SourcesInfoId.Keyid) " +
|
"Left Join Sys_Dictionary as d_CustomerIndustriesId On(t.[CustomerIndustriesId]=d_CustomerIndustriesId.Keyid) " +
|
"Left Join Sys_Dictionary as d_CustomerTypeId On(t.[CustomerTypeId]=d_CustomerTypeId.Keyid) " +
|
"Left Join Sys_Dictionary as d_CreditId On(t.[CreditId]=d_CreditId.Keyid) " +
|
"Left Join Sys_Dictionary as d_PaytimeId On( d_PaytimeId.DicType='付款时间' and t.[Paytime]=d_PaytimeId.MeanValue ) " +
|
"Left Join Sys_Dictionary as d_DegreeImportanId On(t.[DegreeImportanId]=d_DegreeImportanId.Keyid) " +
|
"Left Join Sys_Dictionary as d_BusinessAnalysisId On(t.[BusinessAnalysisId]=d_BusinessAnalysisId.Keyid) " +
|
"LEFT JOIN dbo.OA_Staff AS d_OA_Staff ON (t.BusinessManagerId=d_OA_Staff.Keyid) " +
|
"Left Join OA_CorporateClientsBiddingcompany as d_Biddingcompany On t.[Biddingcompany]=d_Biddingcompany.Keyid1 " +
|
"Left Join Sys_Dictionary as d_Contracttype On( d_Contracttype.DicType='合同金额' and t.[Contracttype]=d_Contracttype.MeanValue ) " +
|
"Left Join Sys_Dictionary as d_Expiretype On( d_Expiretype.DicType='到期时间' and t.[Expiretype]=d_Expiretype.MeanValue ) " +
|
"Left Join Sys_Dictionary as d_Piaoju On( d_Piaoju.DicType='票据' and t.[Piaoju]=d_Piaoju.MeanValue ) " +
|
"Left Join Sys_Dictionary as d_Shifouwanjie On( d_Shifouwanjie.DicType='客户完结状态' and t.[Shifouwanjie]=d_Shifouwanjie.MeanValue ) " +
|
"LEFT JOIN dbo.OA_Staff AS d_OA_Staff1 ON (t.AccountManagerId=d_OA_Staff1.Keyid) ";
|
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(selectTarget, fromSouce) as IList<OA_CorporateClients>;//执行查询
|
|
return (null == result || result.Count == 0) ? null : result[0];//返回结果
|
}
|
|
/// <summary>
|
/// 单个查询
|
/// </summary>
|
/// <param name="FirmId">编号</param>
|
/// <returns></returns>
|
public OA_CorporateClients SelectModelByFirmId(Guid FirmId)
|
{
|
if (FirmId == null)
|
return null;//错误数据返会空
|
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' ORDER BY CustomerId DESC", FirmId)) as IList<OA_CorporateClients>;//执行查询
|
|
return (null == result || result.Count == 0) ? null : result[0];//返回结果
|
}
|
|
/// <summary>
|
/// 根据厂商编号查询全部会员
|
/// </summary>
|
/// <param name="FirmId">编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClients> SelectListByFirmId(Guid FirmId)
|
{
|
if (FirmId == null)
|
return null;//错误数据返会空
|
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' ORDER BY CreateTime DESC", FirmId)) as IList<OA_CorporateClients>;//执行查询
|
|
return result;//返回结果
|
}
|
|
/// <summary>
|
/// 根据厂商编号查询全部外协厂商
|
/// </summary>
|
/// <param name="MemberId">编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClients> SelectListByMemberId(Guid MemberId)
|
{
|
if (MemberId == null)
|
return null;//错误数据返会空
|
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" MemberId='{0}' and IsOutsourcing='True' ORDER BY CreateTime DESC", MemberId)) as IList<OA_CorporateClients>;//执行查询
|
|
return result;//返回结果
|
}
|
|
/// <summary>
|
/// 根据会员编号查询所有已添加我为客户的厂商
|
/// </summary>
|
/// <param name="MemberId">编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClients> SelectListByMyId(Guid MemberId)
|
{
|
if (MemberId == null)
|
return null;//错误数据返会空
|
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId IN (SELECT MemberId FROM dbo.EC_MemberBasic) and MemberId='{0}' ORDER BY CreateTime DESC", MemberId)) as IList<OA_CorporateClients>;//执行查询
|
|
return result;//返回结果
|
}
|
|
/// <summary>
|
/// 新增合作客户
|
/// </summary>
|
/// <param name="rType"></param>
|
/// <param name="m_OA_CustomerCommunications"></param>
|
/// <param name="m_EC_AcceptWayByCustomers"></param>
|
/// <returns></returns>
|
public bool InsertModel(OA_CorporateClients m_OA_CorporateClients, OA_CustomerCommunications m_OA_CustomerCommunications, EC_AcceptWayByCustomers m_EC_AcceptWayByCustomers)
|
{
|
IUnitOfWork unit = new UnitOfWorkForSql();
|
unit.RegisterNew(m_OA_CorporateClients, this);
|
unit.RegisterNew(m_OA_CustomerCommunications, new OA_CustomerCommunicationsDAL(_dataBase));
|
unit.RegisterNew(m_EC_AcceptWayByCustomers, new EC_AcceptWayByCustomersDAL(_dataBase));
|
return unit.Commit();
|
}
|
|
/// <summary>
|
/// 意向客户导入合作客户
|
/// </summary>
|
/// <param name="m_OA_CorporateClients"></param>
|
/// <param name="m_OA_CustomerCommunications"></param>
|
/// <param name="m_EC_AcceptWayByCustomers"></param>
|
/// <param name="m_OA_IntentionCustomer"></param>
|
/// <returns></returns>
|
public bool IntIntoModel(OA_CorporateClients m_OA_CorporateClients, OA_CustomerCommunications m_OA_CustomerCommunications, EC_AcceptWayByCustomers m_EC_AcceptWayByCustomers, OA_IntentionCustomer m_OA_IntentionCustomer)
|
{
|
IUnitOfWork unit = new UnitOfWorkForSql();
|
unit.RegisterNew(m_OA_CorporateClients, this);
|
unit.RegisterNew(m_OA_CustomerCommunications, new OA_CustomerCommunicationsDAL(_dataBase));
|
unit.RegisterNew(m_EC_AcceptWayByCustomers, new EC_AcceptWayByCustomersDAL(_dataBase));
|
unit.RegisterRemoved(m_OA_IntentionCustomer, new OA_IntentionCustomerDAL(_dataBase));
|
return unit.Commit();
|
}
|
|
/// <summary>
|
/// 修改合作客户
|
/// </summary>
|
/// <param name="rType"></param>
|
/// <returns></returns>
|
public bool UpdateModel(OA_CorporateClients m_OA_CorporateClients, OA_CustomerCommunications m_OA_CustomerCommunications, EC_AcceptWayByCustomers m_EC_AcceptWayByCustomers)
|
{
|
IUnitOfWork unit = new UnitOfWorkForSql();
|
unit.RegisterAmended(m_OA_CorporateClients, this);
|
unit.RegisterAmended(m_OA_CustomerCommunications, new OA_CustomerCommunicationsDAL(_dataBase));
|
unit.RegisterAmended(m_EC_AcceptWayByCustomers, new EC_AcceptWayByCustomersDAL(_dataBase));
|
return unit.Commit();
|
}
|
|
/// <summary>
|
/// 获取询价编号
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <param name="CustormId"></param>
|
/// <returns></returns>
|
public Guid GetInquiryId(Guid FirmId, Guid CustormId)
|
{
|
Guid InquiryId = new Guid();
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and Keyid='{1}'", FirmId, CustormId)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
EC_MemberBasicDAL dal_EC_MemberBasicDAL = new EC_MemberBasicDAL();
|
EC_MemberBasic m_EC_MemberBasic = dal_EC_MemberBasicDAL.GetMemberByMemberId(FirmId);
|
InquiryId = m_EC_MemberBasic.InquiryId;
|
}
|
else
|
{
|
InquiryId = result[0].InquiryId;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return InquiryId;
|
}
|
|
/// <summary>
|
/// 根据会员id和厂商id获取客户编号
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public Guid GetCustormId(Guid FirmId, Guid MemberId)
|
{
|
Guid CustormId = new Guid();
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
CustormId = Guid.Empty;
|
}
|
else
|
{
|
CustormId = result[0].Keyid;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return CustormId;
|
}
|
|
/// <summary>
|
/// 判断企业网站能否登陆成功
|
/// </summary>
|
/// <param name="LoginPhone"></param>
|
/// <param name="PwdNum"></param>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public OA_CorporateClients IfHasMember(string LoginPhone, string PwdNum, Guid MemberId)
|
{
|
string selTarget = " * ";
|
string fromSource = " OA_CorporateClients where LoginPhone='" + LoginPhone + "' and LoginPwd='" + PwdNum + "' and FirmId='" + MemberId + "' and IsLoginCorporateWeb='True' ";
|
if (_dataBase.SelectModel<OA_CorporateClients>(selTarget, fromSource).Count() > 0)
|
{
|
return _dataBase.SelectModel<OA_CorporateClients>(selTarget, fromSource)[0];
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 获取总的预付款余额
|
/// </summary>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public decimal GetAllPrepayments(Guid MemberId)
|
{
|
try
|
{
|
decimal allPrepayments = 0;
|
string selTarget = " SUM(Prepayments) AS AllPrepayments ";
|
string fromSource = "dbo.OA_CorporateClients WHERE FirmId='" + MemberId + "'";
|
DataTable ds = _dataBase.SelectModel(selTarget, fromSource);
|
if (ds.Rows.Count > 0)
|
{
|
return Convert.ToDecimal(ds.Rows[0][0]);
|
}
|
return allPrepayments;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 根据厂商以及会员编号查询客户信息
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public OA_CorporateClients SelectModelByFirmIdandMemberId(Guid FirmId, Guid MemberId)
|
{
|
OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
m_OA_CorporateClients = null;
|
}
|
else
|
{
|
m_OA_CorporateClients = result[0];
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return m_OA_CorporateClients;
|
}
|
|
/// <summary>
|
/// 根据厂商以及会员编号查询外协厂商
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public OA_CorporateClients SelectOutsourceByFirmIdandMemberId(Guid FirmId, Guid MemberId)
|
{
|
OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}' and IsOutsourcing ='True'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
m_OA_CorporateClients = null;
|
}
|
else
|
{
|
m_OA_CorporateClients = result[0];
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return m_OA_CorporateClients;
|
}
|
|
/// <summary>
|
/// 判断会员是否是厂商的客户
|
/// </summary>
|
/// <param name="FirmId"></param>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public bool IsCustormByFirmIdandMemberId(Guid FirmId, Guid MemberId)
|
{
|
bool Iscus = false;
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
Iscus = false;
|
}
|
else
|
{
|
Iscus = true;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return Iscus;
|
}
|
|
/// <summary>
|
/// 模糊查询外协公司名称
|
/// </summary>
|
/// <param name="CompanyName">公司名</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClients> SelectListListByName(string CompanyName, Guid MemberId)
|
{
|
if (string.IsNullOrEmpty(CompanyName))
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" top 5 * ", "OA_CorporateClients", string.Format(" OutVendorName like '%{0}%' and MemberId ='{1}' AND Keyid <> FirmId ", CompanyName, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 模糊查询客户公司名称
|
/// </summary>
|
/// <param name="CompanyName"></param>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClients> SelectCustomListByName(string CompanyName, Guid MemberId)
|
{
|
if (string.IsNullOrEmpty(CompanyName))
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" top 5 * ", "OA_CorporateClients", string.Format(" CompanyName = '{0}' and FirmId ='{1}'", CompanyName, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 根据客户编号获取客户
|
/// </summary>
|
/// <param name="CustomId">客户编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClients> SelectListListByCustomId(string CustomId, Guid MemberId)
|
{
|
if (string.IsNullOrEmpty(CustomId))
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" a.*,f.*,b.Province as Province, b.City as City,b.County as County , b.DetailedAddress as DetailedAddress,b.Postcode as Postcode,b.Mobile as Mobile,b.CompanyPhone as CompanyPhone,b.QQ as QQ,b.BusinessManagers as BusinessManagers,b.Email as Email,b.LegalRepresentative as LegalRepresentative,d_DegreeImportanId.Name as DegreeImportanIdName,l.Name as SourcesInfoIdName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName,d_CreditId.Name as CreditIdName", "OA_CorporateClients as a left join OA_CustomerCommunications as b on a.Keyid = b.Keyid Left Join Sys_Dictionary as d_DegreeImportanId On (a.[DegreeImportanId]=d_DegreeImportanId.Keyid) Left Join Sys_Dictionary as k On (a.[SourcesInfoId]=k.Keyid) Left Join Sys_Dictionary as l On (a.SourcesInfoId=l.Keyid) Left Join Sys_Dictionary as d_CreditId On(a.[CreditId]=d_CreditId.Keyid) LEFT JOIN dbo.OA_Staff AS d_OA_Staff ON a.BusinessManagerId=d_OA_Staff.Keyid LEFT JOIN dbo.OA_Staff AS d_OA_Staff1 ON a.AccountManagerId=d_OA_Staff1.Keyid " +
|
" LEFT JOIN( SELECT CustomerId AS Fid, 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 WHERE CutomerType ='True' GROUP BY CustomerId) AS f ON CONVERT(VARCHAR(200),a.CustomerId)=f.Fid ", string.Format(" CustomerId in ({0}) and a.FirmId ='{1}' order by CustomerId ASC", CustomId, MemberId)) as IList<OA_CorporateClients>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 设置自动外协
|
/// </summary>
|
/// <param name="Keyid"></param>
|
/// <param name="i"></param>
|
/// <returns></returns>
|
public bool EditModel(Guid Keyid, string i)
|
{
|
try
|
{
|
bool IsSuccess = true;
|
OA_CorporateClients m_OA_CorporateClients = SelectModelByKeyid(Keyid);
|
m_OA_CorporateClients.IsPriority = (i == "1");
|
|
List<OA_CorporateClients> m_OA_CorporateClientsList = SelectListByMemberId(m_OA_CorporateClients.MemberId) as List<OA_CorporateClients>;
|
using (TransactionScope t_TransactionScope = new TransactionScope())
|
{
|
foreach (var item in m_OA_CorporateClientsList)
|
{
|
if (IsSuccess)
|
{
|
item.IsPriority = false;
|
IsSuccess = UpdateModel(item);
|
}
|
else
|
return false;
|
}
|
if (IsSuccess)
|
IsSuccess = UpdateModel(m_OA_CorporateClients);
|
if (IsSuccess)
|
t_TransactionScope.Complete();
|
}
|
return true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 转化在线用户为某厂商客户方法
|
/// </summary>
|
/// <param name="firmId">厂商编号</param>
|
/// <param name="memberId">会员编号</param>
|
/// <param name="theOperator">操作人</param>
|
/// <returns></returns>
|
public int ConvertToCorporateClient(Guid firmId, Guid memberId, string theOperator)
|
{
|
List<SqlParameter> paramList = new List<SqlParameter>() {
|
new SqlParameter(){ParameterName="@returnValue",SqlDbType=SqlDbType.Int,Direction=ParameterDirection.ReturnValue},
|
new SqlParameter(){ParameterName="@FirmId",Value=firmId,SqlDbType=SqlDbType.UniqueIdentifier,DbType=DbType.Guid},
|
new SqlParameter(){ParameterName="@MemberId",Value=memberId,SqlDbType=SqlDbType.UniqueIdentifier,DbType=DbType.Guid},
|
new SqlParameter(){ParameterName="@Operator",Value=theOperator,SqlDbType=SqlDbType.VarChar,DbType=DbType.String,Size=20}
|
};
|
|
try
|
{
|
|
_dataBase.Query("sp_ConvertMemberToCorporateClient", CommandType.StoredProcedure, paramList.ToArray());
|
return (int)paramList[0].Value;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 订单收款，外协付款使用预付款
|
/// </summary>
|
/// <param name="MemberId">订单收款传客户编号，外协付款传自己的会员编号</param>
|
/// <param name="FirmId">订单收款传自己的会员编号，外协付款传外协厂商的编号</param>
|
/// <param name="Money">操作金额</param>
|
/// <param name="SubjectName">科目名称</param>
|
/// <param name="Operator">操作人</param>
|
/// <param name="OperatTypeId">操作类型</param>
|
/// <returns></returns>
|
public bool OrderReciveMoney(Guid MemberId, Guid FirmId, decimal? Money, string SubjectName, string Operator, int? OperatTypeId)
|
{
|
try
|
{
|
|
OA_CorporateClients m_OA_CorporateClients = SelectModelByFirmIdandMemberId(FirmId, MemberId);
|
|
if (m_OA_CorporateClients.Prepayments >= Money)
|
{
|
m_OA_CorporateClients.Prepayments -= Money;
|
}
|
else
|
{
|
m_OA_CorporateClients.Credit -= (Money - m_OA_CorporateClients.Prepayments);
|
m_OA_CorporateClients.Prepayments = 0;
|
}
|
m_OA_CorporateClients.Operator = Operator;
|
m_OA_CorporateClients.LastUpdateTime = DateTime.Now;
|
bool isWin = UpdateModel(m_OA_CorporateClients);
|
if (!isWin)
|
return isWin;
|
else
|
;
|
Sys_DictionaryDAL dal_Sys_DictionaryDAL = new Sys_DictionaryDAL(_dataBase);
|
OA_AdvanceMoneyRecord m_OA_AdvanceMoneyRecord = new OA_AdvanceMoneyRecord();
|
m_OA_AdvanceMoneyRecord.CustomerId = m_OA_CorporateClients.Keyid;
|
m_OA_AdvanceMoneyRecord.OperatTypeId = dal_Sys_DictionaryDAL.GetNameByMeanValue(OperatTypeId, "客户预付款明细类型");
|
m_OA_AdvanceMoneyRecord.OperatMoney = Money;
|
m_OA_AdvanceMoneyRecord.SubjectName = SubjectName;
|
m_OA_AdvanceMoneyRecord.AccountType = "";
|
m_OA_AdvanceMoneyRecord.AccountName = "";
|
m_OA_AdvanceMoneyRecord.LastUpdateTime = DateTime.Now;
|
m_OA_AdvanceMoneyRecord.Operator = Operator;
|
m_OA_AdvanceMoneyRecord.Remark = "";
|
OA_AdvanceMoneyRecordDAL dal_OA_AdvanceMoneyRecordDAL = new OA_AdvanceMoneyRecordDAL(_dataBase);
|
isWin = dal_OA_AdvanceMoneyRecordDAL.InserModel(m_OA_AdvanceMoneyRecord);
|
if (!isWin)
|
return isWin;
|
else
|
{
|
};
|
return true;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 检测厂商是否存在
|
/// </summary>
|
/// <param name="CompanyName"></param>
|
/// <param name="FirmId"></param>
|
/// <param name="Keyid"></param>
|
/// <returns></returns>
|
public bool isExistCompanyName(String CompanyName, Guid FirmId, Guid Keyid)
|
{
|
try
|
{
|
String selectTarget = " * ";
|
string fromSouce = "OA_CorporateClients where CompanyName='" + CompanyName + "' and FirmId='" + FirmId + "'";
|
List<OA_CorporateClients> m_OA_CorporateClientsrList = _dataBase.SelectModel<OA_CorporateClients>(selectTarget, fromSouce) as List<OA_CorporateClients>;
|
if (m_OA_CorporateClientsrList != null && m_OA_CorporateClientsrList.Count() > 0)
|
{
|
if (m_OA_CorporateClientsrList[0].Keyid == Keyid)
|
return false;
|
else
|
return true;
|
}
|
else
|
return false;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 合并客户
|
/// </summary>
|
/// <param name="DefaultId"></param>
|
/// <param name="DelId"></param>
|
/// <param name="InquiryId"></param>
|
/// <returns></returns>
|
public bool MergerCustom(Guid DefaultId, Guid DelId, Guid InquiryId)
|
{
|
int returnState = 0;
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@DefaultId",DefaultId),
|
new SqlParameter("@DelId",DelId),
|
new SqlParameter("@InquiryId",InquiryId),
|
new SqlParameter("@returnState", returnState){SqlDbType=System.Data.SqlDbType.Int, Direction=ParameterDirection.Output}
|
};
|
try
|
{
|
_dataBase.Query("sp_CorporateClientsMerger", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
if (MyConvert.ConvertToInt32(sqlParms[3].Value) <= 0)
|
{
|
return false;
|
}
|
else
|
{
|
return true;
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 根据个人网店编号查询自动外协厂商
|
/// </summary>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public OA_CorporateClients GetDefaultOutFirm(Guid MemberId)
|
{
|
OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" * ", " dbo.OA_CorporateClients AS a LEFT JOIN dbo.EC_MemberBasic AS b ON a.MemberId = b.MemberId ", string.Format(" a.MemberId='{0}' and IsOutsourcing ='True' and IsPriority ='True' AND b.MemberType='个人网店' ", MemberId)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
m_OA_CorporateClients = null;
|
}
|
else
|
{
|
m_OA_CorporateClients = result[0];
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return m_OA_CorporateClients;
|
}
|
|
/// <summary>
|
/// 根据会员ID和外协厂商名称查询外协厂商
|
/// </summary>
|
/// <param name="MemberId"></param>
|
/// <returns></returns>
|
public OA_CorporateClients GetModelByMemberIdAndName(Guid MemberId,string OutVendorName)
|
{
|
OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
|
try
|
{
|
IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" * ", " dbo.OA_CorporateClients", string.Format(" MemberId='{0}' and IsOutsourcing ='True' and OutVendorName='{1}' ", MemberId, OutVendorName)) as IList<OA_CorporateClients>;//执行查询
|
if (null == result || result.Count == 0)
|
{
|
m_OA_CorporateClients = null;
|
}
|
else
|
{
|
m_OA_CorporateClients = result[0];
|
}
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return m_OA_CorporateClients;
|
}
|
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InsertModelVisit(CY.Model.OA_CorporateClientsVisit trueModel)
|
{
|
//Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClientsVist;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
SqlParameter par = null;
|
if (trueModel.VisitTime.HasValue)
|
{
|
par = new SqlParameter("@VisitTime", trueModel.VisitTime.Value);
|
}
|
else
|
{
|
par = new SqlParameter("@VisitTime", DBNull.Value);
|
}
|
SqlParameter par1 = null;
|
if (trueModel.CreateTime.HasValue)
|
{
|
par1 = new SqlParameter("@CreateTime", trueModel.CreateTime.Value);
|
}
|
else
|
{
|
par1 = new SqlParameter("@CreateTime", DBNull.Value);
|
}
|
SqlParameter par2 = null;
|
if (trueModel.LastUpdateTime.HasValue)
|
{
|
par2 = new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime.Value);
|
}
|
else
|
{
|
par2 = new SqlParameter("@LastUpdateTime", DBNull.Value);
|
}
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),
|
new SqlParameter("@VisitTime",trueModel.VisitTime),
|
new SqlParameter("@Remark",trueModel.Remark),
|
new SqlParameter("@Creater",trueModel.Creater),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@Updater",trueModel.Updater),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClientsVisit_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModelVisit(CY.Model.OA_CorporateClientsVisit trueModel)
|
{
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
SqlParameter par = null;
|
if (trueModel.VisitTime.HasValue)
|
{
|
par = new SqlParameter("@VisitTime", trueModel.VisitTime.Value);
|
}
|
else
|
{
|
par = new SqlParameter("@VisitTime", DBNull.Value);
|
}
|
SqlParameter par1 = null;
|
if (trueModel.CreateTime.HasValue)
|
{
|
par1 = new SqlParameter("@CreateTime", trueModel.CreateTime.Value);
|
}
|
else
|
{
|
par1 = new SqlParameter("@CreateTime", DBNull.Value);
|
}
|
SqlParameter par2 = null;
|
if (trueModel.LastUpdateTime.HasValue)
|
{
|
par2 = new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime.Value);
|
}
|
else
|
{
|
par2 = new SqlParameter("@LastUpdateTime", DBNull.Value);
|
}
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),
|
new SqlParameter("@VisitTime",trueModel.VisitTime),
|
new SqlParameter("@Remark",trueModel.Remark),
|
new SqlParameter("@Creater",trueModel.Creater),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@Updater",trueModel.Updater),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClientsVisit_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
|
/// <summary>
|
/// 根据客户id查询拜访记录
|
/// </summary>
|
/// <param name="CustomId">编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClientsVisit> SelectVisitListByCorId(Guid CustomId)
|
{
|
if (CustomId == null)
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_CorporateClientsVisit> result = _dataBase.SelectModel<OA_CorporateClientsVisit>(" a.*,b.[Name] as CreaterName ", "OA_CorporateClientsVisit a left join EC_MemberBasic b on a.Creater = b.MemberId ", string.Format(" CorporateClientsid = '{0}' order by a.[VisitTime] desc", CustomId)) as IList<OA_CorporateClientsVisit>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
|
/// <summary>
|
/// 新增
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InsertModelPrint(CY.Model.OA_CorporateClientsPrint trueModel)
|
{
|
//Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClientsVist;
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter(){ ParameterName ="@Keyid", SqlDbType=System.Data.SqlDbType.Int, DbType=System.Data.DbType.Int32, Direction=ParameterDirection.Output},
|
new SqlParameter("@BusinessManagers",trueModel.BusinessManagers),
|
new SqlParameter("@ManagersMobile",trueModel.@ManagersMobile),
|
new SqlParameter("@ManagersQQ",trueModel.ManagersQQ),
|
|
new SqlParameter("@Xinge",trueModel.Xinge),
|
new SqlParameter("@DocumentName",trueModel.DocumentName),
|
new SqlParameter("@UnitPrice",trueModel.UnitPrice),
|
new SqlParameter("@SumPrice",trueModel.SumPrice),
|
new SqlParameter("@Deliveryrequirements",trueModel.Deliveryrequirements),
|
new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),
|
|
new SqlParameter("@Creater",trueModel.Creater),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@Updater",trueModel.Updater),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
|
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClientsPrint_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
trueModel.Keyid = CY.Infrastructure.Common.MyConvert.ConvertToInt32(sqlParms[0].Value).Value;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
/// <summary>
|
/// 修改
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModelPrint(CY.Model.OA_CorporateClientsPrint trueModel)
|
{
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{ new SqlParameter("@Keyid",trueModel.Keyid),
|
new SqlParameter("@BusinessManagers",trueModel.BusinessManagers),
|
new SqlParameter("@ManagersMobile",trueModel.@ManagersMobile),
|
new SqlParameter("@ManagersQQ",trueModel.ManagersQQ),
|
|
new SqlParameter("@Xinge",trueModel.Xinge),
|
new SqlParameter("@DocumentName",trueModel.DocumentName),
|
new SqlParameter("@UnitPrice",trueModel.UnitPrice),
|
new SqlParameter("@SumPrice",trueModel.SumPrice),
|
new SqlParameter("@Deliveryrequirements",trueModel.Deliveryrequirements),
|
new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),
|
|
new SqlParameter("@Creater",trueModel.Creater),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
new SqlParameter("@Updater",trueModel.Updater),
|
new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
|
|
|
};
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClientsPrint_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
return true;
|
}
|
|
|
|
/// <summary>
|
/// 根据客户id查询订单需求
|
/// </summary>
|
/// <param name="CustomId">编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClientsPrint> SelectPrintListByCorId(Guid CustomId)
|
{
|
if (CustomId == null)
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_CorporateClientsPrint> result = _dataBase.SelectModel<OA_CorporateClientsPrint>(" a.*,b.[Name] as CreaterName,c.PrintParameter ", "OA_CorporateClientsPrint a left join EC_MemberBasic b on a.Creater = b.MemberId left join OA_CorporateClientsParameter c on a.Keyid = c.TargetId ", string.Format(" CorporateClientsid = '{0}' order by a.[CreateTime] desc", CustomId)) as IList<OA_CorporateClientsPrint>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
/// <summary>
|
/// 根据客户id查询订单需求
|
/// </summary>
|
/// <param name="CustomId">编号</param>
|
/// <returns></returns>
|
public OA_CorporateClientsPrint SelectPrintListByPrintId(int Keyid)
|
{
|
if (Keyid == 0)
|
new OA_CorporateClientsPrint();
|
|
try
|
{
|
IList<OA_CorporateClientsPrint> result = _dataBase.SelectModel<OA_CorporateClientsPrint>(" a.*,b.[Name] as CreaterName,c.PrintParameter ", "OA_CorporateClientsPrint a left join EC_MemberBasic b on a.Creater = b.MemberId left join OA_CorporateClientsParameter c on a.Keyid = c.TargetId ", string.Format(" Keyid = '{0}' ", Keyid)) as IList<OA_CorporateClientsPrint>;//执行查询
|
if(result!=null&& result.Count > 0)
|
{
|
return result[0];//返回结果
|
|
}
|
else
|
{
|
new OA_CorporateClientsPrint();
|
}
|
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
|
return new OA_CorporateClientsPrint();
|
}
|
|
|
/// <summary>
|
/// 添加订单印刷选项
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InsertModelParameter(CY.Model.OA_CorporateClientsParameter trueModel)
|
{
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
using (XmlTextReader rdr = new XmlTextReader(trueModel.PrintParameter, XmlNodeType.Document, null))
|
{
|
|
SqlXml sqlXml = new SqlXml(rdr);
|
SqlParameter printPar = new SqlParameter("@PrintParameter", SqlDbType.Xml, sqlXml.Value.Length);
|
printPar.Value = sqlXml;
|
SqlParameter[] parameters = {
|
new SqlParameter("@TargetId", SqlDbType.Int,4),
|
printPar
|
};
|
parameters[0].Value = trueModel.TargetId;
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClientsParameter_Insert", CommandType.StoredProcedure, parameters);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
return true;
|
}
|
/// <summary>
|
/// 修改订单印刷选项
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool UpdateModelParameter(CY.Model.OA_CorporateClientsParameter trueModel)
|
{
|
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
using (XmlTextReader rdr = new XmlTextReader(trueModel.PrintParameter, XmlNodeType.Document, null))
|
{
|
|
SqlXml sqlXml = new SqlXml(rdr);
|
SqlParameter printPar = new SqlParameter("@PrintParameter", SqlDbType.Xml, sqlXml.Value.Length);
|
printPar.Value = sqlXml;
|
SqlParameter[] parameters = {
|
new SqlParameter("@TargetId", SqlDbType.Int,4),
|
printPar
|
};
|
parameters[0].Value = trueModel.TargetId;
|
try
|
{
|
_dataBase.Query("sp_OA_CorporateClientsParameter_Update", CommandType.StoredProcedure, parameters);
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
return true;
|
|
}
|
|
|
/// <summary>
|
/// 修改订单印刷选项
|
/// </summary>
|
/// <param name="model"></param>
|
/// <returns></returns>
|
public bool InsertModelBiddingcompany(CY.Model.OA_CorporateClients trueModel)
|
{
|
|
if (trueModel == null)
|
{
|
return false;
|
}
|
|
|
IList<SqlParameter> sqlParms = new List<SqlParameter>()
|
{
|
new SqlParameter("@FirmId",trueModel.FirmId),
|
new SqlParameter("@CompanyName",trueModel.CompanyName),
|
new SqlParameter("@CreateTime",trueModel.CreateTime),
|
|
};
|
|
try
|
{
|
_dataBase.ExecuteSql("INSERT INTO [OA_CorporateClientsBiddingcompany] ([FirmId] ,[CompanyName],[CreateTime]) VALUES (@FirmId,@CompanyName,@CreateTime)", sqlParms.ToArray<SqlParameter>());
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
|
return true;
|
|
}
|
|
|
/// <summary>
|
/// 全部查询
|
/// </summary>
|
/// <param name="query"></param>
|
/// <returns></returns>
|
public IEnumerable<Model.OA_CorporateClients> SelectBiddingcompanyByFirmId(Guid FirmId)
|
{
|
return _dataBase.SelectModel<Model.OA_CorporateClients>(" * ", " OA_CorporateClientsBiddingcompany ", string.Format(" FirmId = '{0}' order by [CreateTime] ", FirmId)) as IList<Model.OA_CorporateClients>;//执行查询
|
}
|
|
|
|
/// <summary>
|
/// 根据客户id查询拜访记录
|
/// </summary>
|
/// <param name="CustomId">编号</param>
|
/// <returns></returns>
|
public IEnumerable<OA_CorporateClientsVisit> SelectVisitListByBuzAndTime(int? BusinessManagerId, string chaxuntime)
|
{
|
if (BusinessManagerId == null || string.IsNullOrEmpty(chaxuntime))
|
return null;//错误数据返会空
|
|
try
|
{
|
IList<OA_CorporateClientsVisit> result = _dataBase.SelectModel<OA_CorporateClientsVisit>(" a.*,b.[Name] as CreaterName,oc.[CompanyName] as CorporateClientName ", " OA_CorporateClientsVisit a inner join OA_CorporateClients oc on a.[CorporateClientsid] = oc.Keyid inner join [OA_Staff] os on a.[Creater] = os.MemberId left join EC_MemberBasic b on a.Creater = b.MemberId ", " os.[Keyid] = '" + BusinessManagerId + "' and DATEDIFF(MONTH,a.[VisitTime],'" + chaxuntime + "') =0 order by oc.[CompanyName] asc ,a.[VisitTime] desc") as IList<OA_CorporateClientsVisit>;//执行查询
|
return result;//返回结果
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
}
|
|
}
|
}
|
