1.Plugin Demo:
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using Microsoft.Crm.Sdk; using Microsoft.Crm.SdkTypeProxy; using Microsoft.Crm.Sdk.Query; namespace Www.company.Com.Crm40.Plugins { public class SaveMaintainApp:IPlugin { public void Execute(IPluginExecutionContext context) { CommonFunction cf = new CommonFunction(); cf.InitializeConfigFromXML(); cf.WriteLog("", "初始化", "Stage=" + context.Stage + ";MessageName=" + context.MessageName); if (context.Stage == 10) { switch (context.MessageName) { case MessageName.Delete: DoDeletePre(context, cf); break; } } if (context.Stage == 50) { switch (context.MessageName) { case MessageName.Create: CrmUtil cu = new CrmUtil(); cu.LogFile += "company_test_new_maintain_app_PlugIn\\SaveMaintainApp"; DoCreatPost(context, cu); break; } } } //创建 private void DoCreatPost(IPluginExecutionContext context, CrmUtil cu) { if (context.InputParameters.Properties.Contains(ParameterName.Target) && context.InputParameters.Properties[ParameterName.Target] is DynamicEntity) { DynamicEntity createEntity = (DynamicEntity)context.PostEntityImages["postImage"]; if (createEntity != null) { ICrmService crmService = (ICrmService)context.CreateCrmService(true); #region 创建维修申请单的时,获取其部分信息 Guid newKeyId = Guid.Empty; Guid defective_partsid = Guid.Empty; string defective_partsname = string.Empty; decimal parts_price = 0M; decimal new_parts_amount = 0M; string defective_parts_no = string.Empty; Guid itransactioncurrencyid = Guid.Empty; Guid vin = Guid.Empty; Guid distributor = Guid.Empty; if (createEntity.Properties.Contains("new_maintain_appid")) { newKeyId = ((Key)createEntity.Properties["new_maintain_appid"]).Value;//获取维修申请单Id cu.WriteLog("newKeyId:"+newKeyId); } if (createEntity.Properties.Contains("new_defective_parts")) { defective_partsid = ((Lookup)createEntity.Properties["new_defective_parts"]).Value;//获取部件规格型号id defective_partsname = ((Lookup)createEntity.Properties["new_defective_parts"]).name;//获取部件规格信号的名称 cu.WriteLog("defective_partsid:" + defective_partsid); cu.WriteLog("defective_partsname:" + defective_partsname); } if (createEntity.Properties.Contains("new_defective_parts_no")) { defective_parts_no = createEntity.Properties["new_defective_parts_no"].ToString();//祸首部件名称 } if (createEntity.Properties.Contains("transactioncurrencyid")) { itransactioncurrencyid = ((Lookup)createEntity.Properties["transactioncurrencyid"]).Value;//获取货币的值 cu.WriteLog("itransactioncurrencyid:" + itransactioncurrencyid); } if (createEntity.Properties.Contains("new_vin")) { vin = ((Lookup)createEntity.Properties["new_vin"]).Value;//与 维修申请单 关联的 整车信息 的唯一标识符。 cu.WriteLog("vin:" + vin); } if (createEntity.Properties.Contains("new_distributor")) { distributor = ((Lookup)createEntity.Properties["new_distributor"]).Value;//获取经销商的值 cu.WriteLog("distributor:" + distributor); } #endregion #region Create 计划更换配件明细 DynamicEntity creEntity = new DynamicEntity("new_pm_qa_old_detail"); if (newKeyId != null) { creEntity.Properties.Add(new LookupProperty("new_maintain_app", new Lookup("new_maintain_app", newKeyId))); cu.WriteLog("newKeyId:" + newKeyId); } if (!string.IsNullOrEmpty(defective_partsid.ToString())) { creEntity.Properties.Add(new LookupProperty("new_parts",new Lookup("new_maintain_app",defective_partsid)));//添加配件规格型号 cu.WriteLog("defective_partsid:" + defective_partsid); } if (!string.IsNullOrEmpty(defective_parts_no)) { creEntity.Properties.Add(new StringProperty("new_parts_name", defective_parts_no));//添加配件名称 cu.WriteLog("defective_parts_no:" + defective_parts_no); } if (creEntity.Properties.Contains("importsequencenumber")) { int importsequencenumbe = ((CrmNumber)creEntity.Properties["importsequencenumber"]).Value; creEntity.Properties.Add(new CrmNumberProperty("importsequencenumber", new CrmNumber(importsequencenumbe)));//添加配件序列号 cu.WriteLog("importsequencenumbe:" + importsequencenumbe); } int change_old_parts = 1;//数量默认为1 creEntity.Properties.Add(new CrmNumberProperty("new_change_old_parts",new CrmNumber(change_old_parts)));//添加数量 cu.WriteLog("change_old_parts:" + change_old_parts); //添加配件单价 decimal GetNew_parts_pric = cu.ParseDecimal(GetNew_parts_price(defective_partsid, cu)); cu.WriteLog("GetNew_parts_pric:" + GetNew_parts_pric); decimal GetFirsts = cu.ParseDecimal(GetFirstsl(vin, cu)); cu.WriteLog("GetFirsts:" + GetFirsts); decimal Geth = cu.ParseDecimal(Gethl(itransactioncurrencyid, cu)); cu.WriteLog("Geth:" + Geth); parts_price = (GetNew_parts_pric / GetFirsts) * Geth; cu.WriteLog("parts_price:" + parts_price); creEntity.Properties.Add(new CrmMoneyProperty("new_parts_price", new CrmMoney(parts_price)));//添加配件价格 //添加结算总价 decimal zj = cu.ParseDecimal(GetJsxs(distributor, cu)); decimal dj = Math.Round(parts_price,2); cu.WriteLog("dj:" + dj); new_parts_amount = dj * 1 * zj; creEntity.Properties.Add(new CrmMoneyProperty("new_parts_amount", new CrmMoney(new_parts_amount)));//添加总价格 cu.WriteLog("new_parts_amount:" + new_parts_amount + "zj:" + zj); //货币显示为美元($) creEntity.Properties.Add(new LookupProperty("transactioncurrencyid", new Lookup("transactioncurrency", new Guid("A4419C79-BB1B-E111-88C2-001CC497CFFC")))); try { Guid guid = crmService.Create(creEntity);//创建记录 cu.WriteLog("guid:" + guid); } catch (System.Web.Services.Protocols.SoapException ex) { var msg = "DoCreatPost:" + (ex.Message + "." + ex.Detail.InnerText); throw new Exception(msg); } #endregion } } } #region 获取配件价格 /// <summary> /// 获取配件价格 /// </summary> /// <param name="itemsalespriceid">itemsalespriceid</param> /// <param name="cu">cu</param> /// <returns></returns> private string GetNew_parts_price(Guid itemsalespriceid, CrmUtil cu) { StringBuilder strBuilder = new StringBuilder(); strBuilder.AppendLine("select New_salesprice from new_itemsalespriceExtensionBase where new_itemsalesprice=@new_itemsalesprice"); SqlCommand cmd = new SqlCommand(strBuilder.ToString()); cmd.Parameters.Add("@new_itemsalesprice", SqlDbType.UniqueIdentifier).Value = itemsalespriceid; DataTable dt = cu.QueryBySql(cmd); string strSaleprice = string.Empty; if (dt != null && dt.Rows.Count > 0) { strSaleprice = dt.Rows[0][0].ToString(); } return strSaleprice; } #endregion #region 获取汇率 /// <summary> /// 获取汇率 /// </summary> /// <param name="transactioncurrency">transactioncurrency</param> /// <param name="cu">cu</param> /// <returns></returns> private string Gethl(Guid transactioncurrency, CrmUtil cu) { StringBuilder strBuilder = new StringBuilder(); strBuilder.AppendLine("select exchangerate from TransactionCurrencyBase where statecode = 0 and transactioncurrencyid=@transactioncurrencyid"); SqlCommand cmd = new SqlCommand(strBuilder.ToString()); cmd.Parameters.Add("@transactioncurrencyid", SqlDbType.UniqueIdentifier).Value = transactioncurrency; DataTable dt = cu.QueryBySql(cmd); string strexchangerate = string.Empty; if (dt != null && dt.Rows.Count > 0) { strexchangerate = dt.Rows[0][0].ToString(); } return strexchangerate; } #endregion #region 获取产品一级税率 /// <summary> /// 获取产品一级税率 /// </summary> /// <param name="vehicleid">vehicleid</param> /// <param name="cu">cu</param> /// <returns></returns> private string GetFirstsl(Guid vehicleid, CrmUtil cu) { StringBuilder sbSql = new StringBuilder(); sbSql.AppendLine("select new_parts_exchange from new_product_first a inner join new_vehicle b on a.New_product_firstId = b.new_product_first where b.statecode = 0 and b.new_vehicleid =@vehicleid"); SqlCommand cmd = new SqlCommand(sbSql.ToString()); cmd.Parameters.Add("@vehicleid", SqlDbType.UniqueIdentifier).Value = vehicleid; DataTable dt = cu.QueryBySql(cmd); string strparts_exchange = string.Empty; if (dt != null && dt.Rows.Count > 0) { strparts_exchange = dt.Rows[0][0].ToString(); } return strparts_exchange; } #endregion #region 获取结算系数 /// <summary> /// 获取结算系数 /// </summary> /// <param name="accountid">accountid</param> /// <param name="cu">cu</param> /// <returns></returns> private string GetJsxs(Guid accountid, CrmUtil cu) { StringBuilder strB = new StringBuilder(); strB.AppendLine("select new_ratio from new_qa_parts_ratio a inner join account b on a.New_country = b.New_country where a.statecode = 0 and b.statecode = 0 and accountid=@accountid"); SqlCommand cmd = new SqlCommand(strB.ToString()); cmd.Parameters.Add("@accountid", SqlDbType.UniqueIdentifier).Value = accountid; DataTable dt = cu.QueryBySql(cmd); string str_ratio = string.Empty; if (dt != null && dt.Rows.Count > 0) { str_ratio = dt.Rows[0][0].ToString(); } return str_ratio; } #endregion private void DoUpdatePre(IPluginExecutionContext context,CommonFunction cf) { if (context.InputParameters.Properties.Contains(ParameterName.Target) && context.InputParameters.Properties[ParameterName.Target] is DynamicEntity) { DynamicEntity entity = (DynamicEntity)context.InputParameters.Properties[ParameterName.Target]; DynamicEntity entityPre = (DynamicEntity)context.PreEntityImages["PreImage"]; Target_new_maintain_app new_maintain_app = new Target_new_maintain_app(); if (entity.Properties.Contains("new_status")) { new_maintain_app.new_status = ((Picklist)entity.Properties["new_status"]).Value; } //如果状态是提交 if(new_maintain_app.new_status==99) { if (entityPre.Properties.Contains("new_status")) { new_maintain_app.new_statusPre = ((Picklist)entityPre.Properties["new_status"]).Value; } //如果提交前状态是草稿 if (new_maintain_app.new_statusPre == 10) { if (entity.Properties.Contains("new_vin")) { new_maintain_app.new_vin = ((Lookup)entity.Properties["new_vin"]).Value; } else { new_maintain_app.new_vin = ((Lookup)entityPre.Properties["new_vin"]).Value; } if (entity.Properties.Contains("new_use_hours")) { new_maintain_app.new_use_hours = ((CrmNumber)entity.Properties["new_use_hours"]).Value; } else { new_maintain_app.new_use_hours = ((CrmNumber)entityPre.Properties["new_use_hours"]).Value; } if (entity.Properties.Contains("new_maintain_appid")) { new_maintain_app.new_maintain_appid = ((Key)entity.Properties["new_maintain_appid"]).Value; } //获取整车信息 GetVehicleInfoBySQL(new_maintain_app,cf); cf.WriteLog("", "获取参数", "new_vin=" + new_maintain_app.new_vin.ToString() + ";new_use_hours=" + new_maintain_app.new_use_hours + ";new_maintain_appid=" + new_maintain_app.new_maintain_appid + ";new_vehicle_status=" + new_maintain_app.new_vehicle_status + ";new_pa_hours=" + new_maintain_app.new_pa_hours); if (new_maintain_app.new_vehicle_status != 3 && new_maintain_app.new_vehicle_status != 4) { cf.WriteLog("", "报错", "此整车不是待销售状态或不在质保期内!"); throw new InvalidPluginExecutionException("此整车不是待销售状态或不在质保期内!"); } if (new_maintain_app.new_use_hours > new_maintain_app.new_pa_hours) { cf.WriteLog("", "报错", "此整车已运行时数已超过质保运转时数!"); throw new InvalidPluginExecutionException("此整车已运行时数已超过质保运转时数!"); } //获取配件总费用 GetSumPartsMoney(new_maintain_app, cf); UpdatePrimaryEntity(new_maintain_app, entity); } } } } //删除 private void DoDeletePre(IPluginExecutionContext context,CommonFunction cf) { DynamicEntity entityPre = (DynamicEntity)context.PreEntityImages["PreImage"]; if (entityPre.Properties.Contains("new_status")) { if (((Picklist)entityPre.Properties["new_status"]).Value != 10) { throw new Exception("已提交信息无法删除!"); } } } //获取整车信息 private void GetVehicleInfoBySQL(Target_new_maintain_app new_maintain_app, CommonFunction cf) { try { StringBuilder sbVehicleSQL = new StringBuilder(); sbVehicleSQL.AppendLine("SELECT ISNULL(vehicle_EB.new_pa_hours,0) AS new_pa_hours,vehicle_EB.new_vehicle_status"); sbVehicleSQL.AppendLine("FROM dbo.New_vehicleExtensionBase vehicle_EB"); sbVehicleSQL.AppendLine("INNER JOIN New_vehicleBase vehicle_B ON vehicle_EB.New_vehicleId = vehicle_B.New_vehicleId"); sbVehicleSQL.AppendLine("WHERE vehicle_B.DeletionStateCode=0 AND vehicle_B.statecode=0"); sbVehicleSQL.AppendLine("AND vehicle_B.New_vehicleId=@vehicleId"); SqlCommand cmd = new SqlCommand(sbVehicleSQL.ToString()); cmd.Parameters.Add("@vehicleId", SqlDbType.UniqueIdentifier).Value = new_maintain_app.new_vin; DataTable dt = cf.QueryBySQL(cmd); if (dt.Rows.Count > 0) { new_maintain_app.new_pa_hours = (int)dt.Rows[0]["new_pa_hours"]; new_maintain_app.new_vehicle_status = (int)dt.Rows[0]["new_vehicle_status"]; } } catch (Exception ex) { throw new Exception("获取整车信息失败,详细信息" + ex.Message); } } //获取配件总费用 private void GetSumPartsMoney(Target_new_maintain_app new_maintain_app, CommonFunction cf) { StringBuilder sbSumPartsSQL = new StringBuilder(); sbSumPartsSQL.AppendLine("SELECT SUM(ISNULL(new_parts_amount,0))"); sbSumPartsSQL.AppendLine("FROM new_pm_qa_old_detailBase detail_B"); sbSumPartsSQL.AppendLine("INNER JOIN dbo.New_pm_qa_old_detailExtensionBase detail_EB ON detail_B.New_pm_qa_old_detailId = detail_EB.New_pm_qa_old_detailId"); sbSumPartsSQL.AppendLine("WHERE New_maintain_app=@New_maintain_app"); SqlCommand cmd = new SqlCommand(sbSumPartsSQL.ToString()); cmd.Parameters.Add("@New_maintain_app", SqlDbType.UniqueIdentifier).Value = new_maintain_app.new_maintain_appid; DataTable dt=cf.QueryBySQL(cmd); new_maintain_app.new_parts_total = (decimal)dt.Rows[0][0]; } //更新主实体 private void UpdatePrimaryEntity(Target_new_maintain_app new_maintain_app, DynamicEntity entity) { CrmMoneyProperty new_parts_total = new CrmMoneyProperty(); new_parts_total.Name = "new_parts_total"; new_parts_total.Value = new CrmMoney(); new_parts_total.Value.Value = new_maintain_app.new_parts_total; entity.Properties.Add(new_parts_total); CrmDateTimeProperty new_submit_date = new CrmDateTimeProperty(); new_submit_date.Name = "new_submit_date"; new_submit_date.Value = new CrmDateTime(); new_submit_date.Value.Value = DateTime.Now.ToString(); entity.Properties.Add(new_submit_date); } } } Target_new_maintain_app.cs: using System; using System.Collections.Generic; using System.Text; namespace Www.company.Com.Crm40.Plugins { class Target_new_maintain_app { public Guid new_maintain_appid; public int new_status; public int new_statusPre; public Guid new_vin; public int new_use_hours; public int new_pa_hours; public int new_vehicle_status; public decimal new_parts_total; } } CrmUtil.cs: using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; using System.Xml; namespace Www.company.Com.Crm40.Plugins { public class CrmUtil { #region 定义变量和常量 const string CONFIG_FILE_PATH = @"C:\CRMExtensionConfig\testCRMExtensionConfig.xml"; public SqlConnection conn; public string LogFile; #endregion public CrmUtil() { try { XmlDocument doc = new XmlDocument(); doc.Load(CONFIG_FILE_PATH); XmlNode xmlnode = doc.SelectSingleNode("testCRMExtensionConfig/testPlugins"); //CRMOrganizationName = xmlnode.SelectSingleNode("CRMOrganizationName").InnerText; //CRMServerHost = xmlnode.SelectSingleNode("CRMServerHost").InnerText; //CRMServerPort = xmlnode.SelectSingleNode("CRMServerPort").InnerText; //CRMDomainName = xmlnode.SelectSingleNode("CRMDomainName").InnerText; //CRMAdminUserName = xmlnode.SelectSingleNode("CRMAdminUserName").InnerText; //CRMAdminUserPassword = xmlnode.SelectSingleNode("CRMAdminUserPassword").InnerText; LogFile = xmlnode.SelectSingleNode("CRMLogPath").InnerText; string CRMDBConstr = xmlnode.SelectSingleNode("CRMSqlPath").InnerText; conn = new SqlConnection(CRMDBConstr); } catch (Exception ex) { WriteLog("初始化参数失败:" + ex.Message); } } #region 数据库相关操作 //获取数据库连接 public void GetConn() { try { if (conn.State != ConnectionState.Open) { conn.Open(); } } catch (Exception ex) { throw new Exception(ex.Message); } } //关闭的数据库连接 public void CloseConn() { if (conn.State != ConnectionState.Closed) { conn.Close(); } } //数据库查询 public DataTable QueryBySql(SqlCommand cmd) { try { GetConn(); SqlDataAdapter sda = new SqlDataAdapter(cmd); cmd.Connection = conn; DataSet ds = new DataSet(); sda.Fill(ds); DataTable dt = ds.Tables[0]; return dt; } catch { return null; } finally { CloseConn(); } } public string GetAllValueById(string strFieldName, Guid guidKeyId, string strEntityName, bool blnInnerJoinExtensionTable) { string strKeyIdName = strEntityName + "Id"; //主键字段名 string strBaseTableName = strEntityName + "Base"; //基础表名 string strExtensionBaseTableName = strEntityName + "ExtensionBase"; //扩展表名 StringBuilder sb = new StringBuilder(); sb.AppendLine("SELECT " + strFieldName + " FROM " + strBaseTableName); if (blnInnerJoinExtensionTable) { sb.AppendLine("INNER JOIN " + strExtensionBaseTableName + " ON " + strBaseTableName + "." + strKeyIdName + "=" + strExtensionBaseTableName + "." + strKeyIdName + ""); } sb.AppendLine("WHERE " + strBaseTableName + "." + strKeyIdName + "=@KeyId"); SqlCommand cmd = new SqlCommand(sb.ToString()); cmd.Parameters.Add("@KeyId", SqlDbType.UniqueIdentifier).Value = guidKeyId; DataTable dt = QueryBySql(cmd); string strValue = string.Empty; if (dt != null && dt.Rows.Count > 0) { strValue = dt.Rows[0][0].ToString(); } return strValue; } /// <summary> /// 根据主键值查询表中状态 /// </summary> /// <param name="intType">状态类型 1:记录状态(statecode) 2:记录删除标识(deletionstatecode)</param> /// <param name="guidKeyId">主键的值</param> /// <param name="strEntityName">实体名</param> /// <param name="blnSpecialEntity">是否系统特殊实体,针对部分系统实体的状态字段为Disabled</param> /// <returns>返回状态的值</returns> public string GetValueById(int intType, Guid guidKeyId, string strEntityName, bool blnSpecialEntity) { string strFieldName = string.Empty; string strKeyIdName = strEntityName + "Id"; //主键字段名 string strBaseTableName = strEntityName + "Base"; //基础表名 if (intType == 1) { if (blnSpecialEntity) { strFieldName = "isdisabled"; } else { strFieldName = "statecode"; } } else if (intType == 2) { strFieldName = "deletionstatecode"; } StringBuilder sb = new StringBuilder(); sb.AppendLine("SELECT " + strFieldName + " FROM " + strBaseTableName); sb.AppendLine("WHERE " + strBaseTableName + "." + strKeyIdName + "=@KeyId"); SqlCommand cmd = new SqlCommand(sb.ToString()); cmd.Parameters.Add("@KeyId", SqlDbType.UniqueIdentifier).Value = guidKeyId; DataTable dt = QueryBySql(cmd); string strValue = string.Empty; if (dt != null && dt.Rows.Count > 0) { strValue = dt.Rows[0][0].ToString(); } return strValue; } /// <summary> /// 根据主键值查询表中其它字段的值 /// </summary> /// <param name="strFieldName">需要查询字段的名称</param> /// <param name="guidKeyId">主键的值</param> /// <param name="strEntityName">实体名</param> /// <param name="blnInnerJoinExtensionTable">是否需要与扩展表进行联合查询(INNER JOIN),默认只查询基础表</param> /// <returns>返回查询出的字段的值</returns> public string GetValueById(string strFieldName, Guid guidKeyId, string strEntityName, bool blnInnerJoinExtensionTable) { return GetValueById(strFieldName, guidKeyId, strEntityName, blnInnerJoinExtensionTable, false); } /// <summary> /// 根据主键值查询表中其它字段的值 /// </summary> /// <param name="strFieldName">需要查询字段的名称</param> /// <param name="guidKeyId">主键的值</param> /// <param name="strEntityName">实体名</param> /// <param name="blnInnerJoinExtensionTable">是否需要与扩展表进行联合查询(INNER JOIN),默认只查询基础表</param> /// <param name="blnSpecialEntity">是否系统特殊实体,针对部分系统实体的状态字段为Disabled</param> /// <returns>返回查询出的字段的值</returns> public string GetValueById(string strFieldName, Guid guidKeyId, string strEntityName, bool blnInnerJoinExtensionTable, bool blnSpecialEntity) { string strKeyIdName = strEntityName + "Id"; //主键字段名 string strBaseTableName = strEntityName + "Base"; //基础表名 string strExtensionBaseTableName = strEntityName + "ExtensionBase"; //扩展表名 StringBuilder sb = new StringBuilder(); sb.AppendLine("SELECT " + strFieldName + " FROM " + strBaseTableName); if (blnInnerJoinExtensionTable) { sb.AppendLine("INNER JOIN " + strExtensionBaseTableName + " ON " + strBaseTableName + "." + strKeyIdName + "=" + strExtensionBaseTableName + "." + strKeyIdName + ""); } if (blnSpecialEntity) { sb.AppendLine("WHERE " + strBaseTableName + ".IsDisabled=0 AND " + strBaseTableName + ".DeletionStateCode=0"); } else { sb.AppendLine("WHERE " + strBaseTableName + ".StateCode=0 AND " + strBaseTableName + ".DeletionStateCode=0"); } sb.AppendLine("AND " + strBaseTableName + "." + strKeyIdName + "=@KeyId"); SqlCommand cmd = new SqlCommand(sb.ToString()); cmd.Parameters.Add("@KeyId", SqlDbType.UniqueIdentifier).Value = guidKeyId; DataTable dt = QueryBySql(cmd); string strValue = string.Empty; if (dt != null && dt.Rows.Count > 0) { strValue = dt.Rows[0][0].ToString(); } return strValue; } #endregion #region 类型转换 //转换为整型 public int ParseInt(string strValue) { int intValue = 0; try { intValue = int.Parse(strValue); } catch { } return intValue; } //转为DateTime public DateTime ParseDateTime(string strValue) { DateTime dateTimeValue = DateTime.Now; try { dateTimeValue = DateTime.Parse(strValue); } catch { } return dateTimeValue; } //把字符串转为decimal型 public decimal ParseDecimal(string strValue) { decimal dcmValue = 0; try { dcmValue = decimal.Parse(strValue); } catch { } return dcmValue; } //四舍五入,保留2位小数 public decimal RoundDecimal(string strValue) { return RoundDecimal(strValue, 2); } public decimal RoundDecimal(decimal dcmOldValue) { return RoundDecimal(dcmOldValue, 2); } //四舍五入,保留指定位小数 public decimal RoundDecimal(string strValue, int intPrecision) { decimal dcmValue = 0; try { dcmValue = Math.Round(ParseDecimal(strValue), intPrecision, MidpointRounding.AwayFromZero); } catch { } return dcmValue; } public decimal RoundDecimal(decimal dcmOldValue, int intPrecision) { decimal dcmValue = 0; try { dcmValue = Math.Round(dcmOldValue, intPrecision, MidpointRounding.AwayFromZero); } catch { } return dcmValue; } #endregion //将日志文件写入指定的文件 public void WriteLog(string content) { System.IO.StreamWriter sr; try { string logFilePath = LogFile + "_" + DateTime.Today.ToString("yyyy-MM-dd") + ".txt"; if (System.IO.Directory.Exists(System.IO.Path.GetDirectoryName(logFilePath)) == false) { System.IO.Directory.CreateDirectory(System.IO.Path.GetDirectoryName(logFilePath)); } if (System.IO.File.Exists(logFilePath)) { sr = System.IO.File.AppendText(logFilePath); } else { sr = System.IO.File.CreateText(logFilePath); } sr.WriteLine(DateTime.Now.ToString("yyyy/MM/dd H:mm:ss ") + content); sr.Close(); } catch (Exception ex) { throw ex; } } } } CommonFunction.cs: using System; using System.Collections.Generic; using System.Web; using System.Data; using System.Data.SqlClient; using System.Xml; using System.Text; using Microsoft.Crm.Sdk; using Microsoft.Crm.SdkTypeProxy; using Microsoft.Crm.Sdk.Query; using Microsoft.Crm.Sdk.Metadata; namespace Www.company.Com.Crm40.Plugins { public class CommonFunction { /// <summary> /// 参数定义 /// </summary> private string functionCommonName = "company_test_Plugin_FailurePartsExecute"; const string CONFIG_FILE_PATH = @"C:\CRMExtensionConfig\testCRMExtensionConfig.xml"; private SqlConnection conn; private string CRMSQLConnStr = string.Empty; public string CRMServerHost = string.Empty; public string CRMServerPort = string.Empty; public string CRMOrganizationName = string.Empty; public string CRMDomainName = string.Empty; public string CRMUserName = string.Empty; public string CRMUserPassword = string.Empty; public CrmService CrmService = new CrmService(); /// <summary> /// 从XML文件中初始化系统配置信息 /// </summary> public void InitializeConfigFromXML() { XmlDocument doc = new XmlDocument(); doc.Load(CONFIG_FILE_PATH); XmlNode xmlNode = doc.SelectSingleNode("testCRMExtensionConfig/testPlugins"); CRMServerHost = xmlNode.SelectSingleNode("CRMServerHost").InnerText; CRMServerPort = xmlNode.SelectSingleNode("CRMServerPort").InnerText; CRMOrganizationName = xmlNode.SelectSingleNode("CRMOrganizationName").InnerText; CRMDomainName = xmlNode.SelectSingleNode("CRMDomainName").InnerText; CRMUserName = xmlNode.SelectSingleNode("CRMAdminUserName").InnerText; CRMUserPassword = xmlNode.SelectSingleNode("CRMAdminUserPassword").InnerText; CRMSQLConnStr = xmlNode.SelectSingleNode("CRMSqlPath").InnerText; } /// <summary> /// 初始化WebService /// </summary> public void InitializeCrmService() { try { CrmAuthenticationToken token = new CrmAuthenticationToken(); token.AuthenticationType = 0; token.OrganizationName = CRMOrganizationName; CrmService.Url = string.Format("http://{0}:{1}/MSCRMServices/2007/CrmService.asmx", CRMServerHost, CRMServerPort); CrmService.Credentials = new System.Net.NetworkCredential(CRMUserName, CRMUserPassword, CRMDomainName); CrmService.CrmAuthenticationTokenValue = token; } catch (Exception ex) { WriteLog("", "初始化WebService", ex.Message); } } /// <summary> /// 打开数据库链接 /// </summary> public void OpenConn() { try { conn = new SqlConnection(CRMSQLConnStr); if (conn.State != ConnectionState.Open) { conn.Open(); } } catch (Exception ex) { throw new Exception("开启数据库链接失败:" + ex.Message); } } /// <summary> /// 关闭数据库链接 /// </summary> public void CloseConn() { try { if (conn.State != ConnectionState.Closed) { conn.Close(); } } catch (Exception ex) { throw new Exception("关闭数据库链接失败:" + ex.Message); } } /// <summary> /// SQL脚本查询 /// </summary> /// <param name="cmd">执行命令</param> /// <returns>返回表</returns> public DataTable QueryBySQL(SqlCommand cmd) { try { OpenConn(); SqlDataAdapter sda = new SqlDataAdapter(cmd); cmd.Connection = conn; DataSet ds = new DataSet(); sda.Fill(ds); DataTable dt = ds.Tables[0]; return dt; } catch (Exception ex) { return null; throw new Exception("SQL查询数据库失败:" + ex.Message); } finally { CloseConn(); } } /// <summary> /// SQL脚本执行 /// </summary> /// <param name="cmd">执行命令</param> public void ExecuteBySQL(SqlCommand cmd) { string rValue = string.Empty; int ReturnCode = 0; string ReturnMessage = string.Empty; try { OpenConn(); int intResult = 1; cmd.Connection = conn; cmd.CommandTimeout = 600; intResult = cmd.ExecuteNonQuery(); if (intResult >= 1) ReturnCode = intResult; if (ReturnCode >= 0) ReturnMessage = "执行成功"; } catch (Exception ex) { ReturnCode = -10; ReturnMessage = "ExecuteSql Exception:" + ex.Message; throw new Exception("执行SQL语句失败:" + ex.Message); } finally { CloseConn(); } } /// <summary> /// SQL存储过程执行 /// </summary> /// <param name="cmd">命令</param> /// <returns>生效记录数</returns> public int ExecuteProcBySQL(SqlCommand cmd) { try { OpenConn(); cmd.Connection = conn; //cmd.CommandTimeout = 600; cmd.CommandType = CommandType.StoredProcedure; int rValue = cmd.ExecuteNonQuery(); return rValue; } catch (Exception ex) { return 0; throw new Exception("执行SQL失败:" + ex.Message); } finally { CloseConn(); } } /// <summary> /// 日志记录 /// </summary> /// <param name="functionName">方法名</param> /// <param name="logStep">步骤</param> /// <param name="logMessage">日志信息</param> public void WriteLog(string functionName, string logStep, string logMessage) { try { if (string.IsNullOrEmpty(functionName)) { functionName = functionCommonName; } //StringBuilder sbSql = new StringBuilder(); //sbSql.AppendLine("INSERT INTO company_CrmLog(FunctionName,LogStep,LogMessage) "); //sbSql.AppendLine("VALUES (@FunctionName,@LogStep,@LogMessage)"); //SqlCommand cmd = new SqlCommand(sbSql.ToString()); //cmd.CommandType = CommandType.Text; //cmd.Parameters.Add("@FunctionName", SqlDbType.NVarChar).Value = functionName; //cmd.Parameters.Add("@LogStep", SqlDbType.NVarChar).Value = logStep; //cmd.Parameters.Add("@LogMessage", SqlDbType.NVarChar).Value = logMessage; //ExecuteBySQL(cmd); string strSQL = "UP_company_WriteSystemLog"; SqlCommand cmd = new SqlCommand(strSQL); cmd.Parameters.Add("@FunctionName", SqlDbType.NVarChar,100).Value = functionName; cmd.Parameters.Add("@LogStep", SqlDbType.NVarChar,100).Value = logStep; cmd.Parameters.Add("@LogMessage", SqlDbType.NVarChar,4000).Value = logMessage; ExecuteProcBySQL(cmd); } catch (Exception ex) { throw new Exception("日志记录出错,详细信息:"+ex.Message); } } } }
2.该示例演示如何使用查询表达式检索其负责人的姓氏不是 Cannon 的所有客户
// Set up the CRM Service. CrmAuthenticationToken token = new CrmAuthenticationToken(); token.AuthenticationType = 0; token.OrganizationName = "AdventureWorksCycle"; CrmService service = new CrmService(); service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx"; service.CrmAuthenticationTokenValue = token; service.Credentials = System.Net.CredentialCache.DefaultCredentials; // Create a column set holding the names of the columns to be retrieved. ColumnSet cols = new ColumnSet(); cols.Attributes = new string [] {"name", "accountid"}; // Create the ConditionExpression. ConditionExpression condition = new ConditionExpression(); // Set the condition to be when the account owner's last name is not Cannon. condition.AttributeName = "lastname"; condition.Operator = ConditionOperator.Equal; condition.Values = new string [] {"Cannon"}; // Build the filter that is based on the condition. FilterExpression filter = new FilterExpression(); filter.FilterOperator = LogicalOperator.And; filter.Conditions = new ConditionExpression[] {condition}; // Create a LinkEntity to link the owner's information to the account. LinkEntity link = new LinkEntity(); // Set the LinkEntity properties. link.LinkCriteria = filter; // Set the linking entity to account. link.LinkFromEntityName = EntityName.account.ToString(); // Set the linking attribute to owninguser. link.LinkFromAttributeName = "owninguser"; // The attribute being linked to is systemuserid. link.LinkToAttributeName = "systemuserid"; // The entity being linked to is systemuser. link.LinkToEntityName = EntityName.systemuser.ToString(); // Create an instance of the query expression class. QueryExpression query = new QueryExpression(); // Set the query properties. query.EntityName = EntityName.account.ToString(); query.ColumnSet = cols; query.LinkEntities = new LinkEntity[] {link}; // Create the request. RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest(); // Set the request properties. retrieve.Query = query; // Execute the request. RetrieveMultipleResponse retrieved2 = (RetrieveMultipleResponse) service.Execute(retrieve);
3.以下代码示例演示如何使用 FilterExpression,其中 city 为 Redmond,且 firstname 为 Joe 或 John。
// Create the query expression and set the entity to contact. QueryExpression query = new QueryExpression(); query.EntityName = "contact"; // Create a condition where the first name equals Joe. ConditionExpression condition1 = new ConditionExpression(); condition1.AttributeName = "firstname"; condition1.Operator = ConditionOperator.Equal; condition1.Values = new string[] { "Joe" }; // Create another condition where the first name equals John. ConditionExpression condition2 = new ConditionExpression(); condition2 .AttributeName = "firstname"; condition2 .Operator = ConditionOperator.Equal; condition2 .Values = new string[] { "John" }; // Create another condition where the city equals Redmond. ConditionExpression condition3 = new ConditionExpression(); condition3 .AttributeName = "city"; condition3 .Operator = ConditionOperator.Equal; condition3 .Values = new string[] { "Redmond" }; // Create a child filter to test for John OR Joe. FilterExpression childFilter = new FilterExpression(); childFilter.FilterOperator = LogicalOperator.Or; childFilter.Conditions = new ConditionExpression[] { condition1, condition2 }; // Create a parent filter to test for the city AND test for the child filter (first name). FilterExpression topFilter = new FilterExpression(); topFilter.FilterOperator = LogicalOperator.And; topFilter.Conditions = new ConditionExpression[] { condition3 }; topFilter.Filters = new FilterExpression[] { childFilter }; // Set the filter critera for the query to the complete filter expression. query.Criteria = topFilter;
4.单表查询
private DynamicEntity GetNewPrInfo(Guid NewPrID)//根据GUID查询某字段 { ColumnSet colSet = new ColumnSet(NewPrInfo.EntityName); colSet.AddColumn(NewPrInfo.AttributeName_Assigner); colSet.AddColumn(NewPrInfo.AttributeName_AssignNode); TargetRetrieveDynamic target = new TargetRetrieveDynamic(); target.EntityId = NewPrID; target.EntityName = NewPrInfo.EntityName; RetrieveRequest request = new RetrieveRequest(); request.ColumnSet = colSet; request.ReturnDynamicEntities = true; request.Target = target; RetrieveResponse response = (RetrieveResponse)this.crmService.Execute(request); DynamicEntity PromotionPe = (DynamicEntity)response.BusinessEntity; return PromotionPe; }
5.返回多个实体
// Set up the CRM Service. CrmAuthenticationToken token = new CrmAuthenticationToken(); token.AuthenticationType = 0; token.OrganizationName = "AdventureWorksCycle"; CrmService service = new CrmService(); service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx"; service.CrmAuthenticationTokenValue = token; service.Credentials = System.Net.CredentialCache.DefaultCredentials; // Create a column set holding the names of the columns to be retrieved. ColumnSet cols = new ColumnSet(); cols.Attributes = new string [] {"name", "accountid"}; // Create the query object. QueryByAttribute query = new QueryByAttribute(); query.ColumnSet = cols; query.EntityName = EntityName.account.ToString(); // The query will retrieve all accounts whose address1_city is Sammamish. query.Attributes = new string [] {"address1_city"}; query.Values = new string [] {"Sammamish"}; // Execute the retrieval. BusinessEntityCollection retrieved = service.RetrieveMultiple(query);
6.以下代码示例演示如何使用 FetchXML。第一条 FetchXML 语句检索所有客户。第二条语句检索姓氏不为 Cannon 的所有客户。
注意:不论哪种情况,登录用户的权限都会影响返回的记录集。Fetch 方法仅检索登录用户有读取访问权限的记录。
[C#] // Set up the CRM Service. CrmAuthenticationToken token = new CrmAuthenticationToken(); token.AuthenticationType = 0; token.OrganizationName = "AdventureWorksCycle"; CrmService service = new CrmService(); service.Url = ""http://<servername>:<port>/mscrmservices/2007/crmservice.asmx"; service.CrmAuthenticationTokenValue = token; service.Credentials = System.Net.CredentialCache.DefaultCredentials; // Retrieve all accounts. // Be aware that using all-attributes may adversely affect performance // and cause unwanted cascading in subsequent updates. // A best practice is to retrieve the least amount of data required. string fetch1 = "<fetch mapping='logical'>"; fetch1 += "<entity name='account'><all-attributes/>"; fetch1 += "</entity></fetch>"; // Fetch the results.