584 lines
34 KiB
C#
584 lines
34 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.OracleClient;
|
|
using WX.CRM.Common;
|
|
using WX.CRM.Model;
|
|
using WX.CRM.Model.MAP;
|
|
|
|
namespace WX.CRM.DAL.Res
|
|
{
|
|
public class CUSTOMER_DAL
|
|
{
|
|
public int ResgisterCustomer(string CNumber, string ResId, string CustomerFrom, string customerDetailXml = null)
|
|
{
|
|
try
|
|
{
|
|
CNumber = CNumber.Replace("+86", "");
|
|
if (CNumber.StartsWith("01") && CNumber.Length == 12)
|
|
{
|
|
CNumber = CNumber.Substring(1);
|
|
}
|
|
var belongToMobile = CNumber.Substring(1, 7);
|
|
string enkey = WX.CRM.Common.Utility.GetSettingByKey("CRMClientKey");
|
|
Interface.Security.ClientKey clientid = Interface.Security.ClientKey.GetClientKey(enkey);
|
|
Interface.Security.EncDecUtil sHelper = new Interface.Security.EncDecUtil();
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "p_CNumber", OracleType = OracleType.NVarChar, Value =sHelper.encyptData(CNumber,clientid.AccessKey)},
|
|
new OracleParameter() { ParameterName = "p_ResId", OracleType = OracleType.VarChar, Value = ResId },
|
|
new OracleParameter() { ParameterName = "p_CustomerFrom", OracleType = OracleType.NVarChar, Value = CustomerFrom },
|
|
new OracleParameter() { ParameterName = "p_BelongToMobile", OracleType = OracleType.VarChar, Value = belongToMobile }
|
|
};
|
|
param.Add(!string.IsNullOrEmpty(customerDetailXml) ? new OracleParameter() { ParameterName = "p_customerDetailXml", OracleType = OracleType.VarChar, Value = customerDetailXml } : new OracleParameter() { ParameterName = "p_customerDetailXml", OracleType = OracleType.VarChar, Value = DBNull.Value });
|
|
param.Add(!string.IsNullOrEmpty(CNumber) ? new OracleParameter() { ParameterName = "p_ECNumber", OracleType = OracleType.VarChar, Value = WX.CRM.Common.Utility.NumberFormat(CNumber) } : new OracleParameter() { ParameterName = "p_ECNumber", OracleType = OracleType.VarChar, Value = DBNull.Value });
|
|
return OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_RES.res_ResgisterCustomer", param.ToArray());
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
WX.CRM.Common.LogHelper.Error(string.Concat("【ResgisterCustomer】---CNumber=" + CNumber + "-----ResId=" + ResId + "-----CustomerFrom=" + CustomerFrom));
|
|
WX.CRM.Common.LogHelper.Error(string.Concat("ResgisterCustomer", e.Message, e.StackTrace));
|
|
throw;
|
|
}
|
|
}
|
|
|
|
public int NewResgisterCustomer(string CNumber, string ResId, string Umid, string CustomerFrom, string customerDetailXml = null)
|
|
{
|
|
try
|
|
{
|
|
LogHelper.Info($"注册进线{CNumber}_{ResId}_{Umid}_{CustomerFrom}_{customerDetailXml}");
|
|
CNumber = CNumber.Replace("+86", "");
|
|
if (CNumber.StartsWith("01") && CNumber.Length == 12)
|
|
{
|
|
CNumber = CNumber.Substring(1);
|
|
}
|
|
var belongToMobile = CNumber.Substring(1, 7);
|
|
string enkey = WX.CRM.Common.Utility.GetSettingByKey("CRMClientKey");
|
|
Interface.Security.ClientKey clientid = Interface.Security.ClientKey.GetClientKey(enkey);
|
|
Interface.Security.EncDecUtil sHelper = new Interface.Security.EncDecUtil();
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "p_CNumber", OracleType = OracleType.NVarChar, Value =sHelper.encyptData(CNumber,clientid.AccessKey)},
|
|
new OracleParameter() { ParameterName = "p_ResId", OracleType = OracleType.VarChar, Value = ResId },
|
|
new OracleParameter() { ParameterName = "p_Umid", OracleType = OracleType.VarChar, Value = Umid },
|
|
new OracleParameter() { ParameterName = "p_CustomerFrom", OracleType = OracleType.NVarChar, Value = CustomerFrom },
|
|
new OracleParameter() { ParameterName = "p_BelongToMobile", OracleType = OracleType.VarChar, Value = belongToMobile }
|
|
};
|
|
param.Add(!string.IsNullOrEmpty(customerDetailXml) ? new OracleParameter() { ParameterName = "p_customerDetailXml", OracleType = OracleType.VarChar, Value = customerDetailXml } : new OracleParameter() { ParameterName = "p_customerDetailXml", OracleType = OracleType.VarChar, Value = DBNull.Value });
|
|
param.Add(!string.IsNullOrEmpty(CNumber) ? new OracleParameter() { ParameterName = "p_ECNumber", OracleType = OracleType.VarChar, Value = WX.CRM.Common.Utility.NumberFormat(CNumber) } : new OracleParameter() { ParameterName = "p_ECNumber", OracleType = OracleType.VarChar, Value = DBNull.Value });
|
|
return OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_RES.res_ResgisterCustomer_new", param.ToArray());
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
WX.CRM.Common.LogHelper.Error(string.Concat("【ResgisterCustomer】---CNumber=" + CNumber + "-----ResId=" + ResId + "-----CustomerFrom=" + CustomerFrom));
|
|
WX.CRM.Common.LogHelper.Error(string.Concat("ResgisterCustomer", e.Message, e.StackTrace));
|
|
throw;
|
|
}
|
|
}
|
|
|
|
public void SetMobileBelongToArea(string belongToMobile, string resId)
|
|
{
|
|
try
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "v_mobile", OracleType = OracleType.VarChar, Value = belongToMobile },
|
|
new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resId }
|
|
};
|
|
OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_MOBILE.SetMobileBelongToArea", param.ToArray());
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
LogHelper.Error(e);
|
|
}
|
|
}
|
|
|
|
public int CrossDBResgisterCustomer(string CNumber, string ResId, string CustomerFrom, string customerDetailXml = null)
|
|
{
|
|
try
|
|
{
|
|
string enkey = WX.CRM.Common.Utility.GetSettingByKey("CRMClientKey");
|
|
Interface.Security.ClientKey clientid = Interface.Security.ClientKey.GetClientKey(enkey);
|
|
Interface.Security.EncDecUtil sHelper = new Interface.Security.EncDecUtil();
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "p_CNumber", OracleType = OracleType.NVarChar, Value =sHelper.encyptData(CNumber,clientid.AccessKey)},
|
|
new OracleParameter() { ParameterName = "p_ResId", OracleType = OracleType.VarChar, Value = ResId },
|
|
new OracleParameter() { ParameterName = "p_CustomerFrom", OracleType = OracleType.NVarChar, Value = CustomerFrom }
|
|
};
|
|
param.Add(!string.IsNullOrEmpty(customerDetailXml) ? new OracleParameter() { ParameterName = "p_customerDetailXml", OracleType = OracleType.VarChar, Value = customerDetailXml } : new OracleParameter() { ParameterName = "p_customerDetailXml", OracleType = OracleType.VarChar, Value = DBNull.Value });
|
|
param.Add(!string.IsNullOrEmpty(CNumber) ? new OracleParameter() { ParameterName = "p_ECNumber", OracleType = OracleType.VarChar, Value = WX.CRM.Common.Utility.NumberFormat(CNumber) } : new OracleParameter() { ParameterName = "p_ECNumber", OracleType = OracleType.VarChar, Value = DBNull.Value });
|
|
return OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_RES.res_CrossDB_ResgisterCustomer", param.ToArray());
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
WX.CRM.Common.LogHelper.Error(string.Concat("【ResgisterCustomer】---CNumber=" + CNumber + "-----ResId=" + ResId + "-----CustomerFrom=" + CustomerFrom));
|
|
WX.CRM.Common.LogHelper.Error(string.Concat("ResgisterCustomer", e.Message, e.StackTrace));
|
|
throw;
|
|
}
|
|
}
|
|
|
|
public int ContractUser(string resid, string userName)
|
|
{
|
|
try
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "vResid", OracleType = OracleType.Char, Value = resid },
|
|
new OracleParameter() { ParameterName = "vUserName", OracleType = OracleType.VarChar, Value = userName }
|
|
};
|
|
|
|
return OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_RES.res_ContractSoftUser", param.ToArray());
|
|
}
|
|
catch
|
|
{
|
|
throw;
|
|
}
|
|
}
|
|
|
|
public List<string> ResIDNoOutboundFilter(DataTable tab, string importId, DateTime stime, DateTime etime, decimal t_type, string slt_Open, string slt_NotOpen)
|
|
{
|
|
List<string> list = new List<string>();
|
|
|
|
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(OracleHelper.AYCRMConn);
|
|
//OracleConnection conn1 = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
List<DataRow> deleteRow = new List<DataRow>();
|
|
foreach (DataRow item in tab.Rows)
|
|
{
|
|
if (item["IMPORTDATA"] == DBNull.Value || item["IMPORTDATA"].ToString() == "")
|
|
deleteRow.Add(item);
|
|
}
|
|
foreach (DataRow item in deleteRow)
|
|
{
|
|
tab.Rows.Remove(item);
|
|
}
|
|
//将数据先插入虚拟表中去
|
|
OracleHelper.OracleBulkInsert(new Oracle.DataAccess.Client.OracleBulkCopy(conn, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction), "TEMP_IMP_BULKRESIMPORTID", tab);
|
|
if (t_type == 0 || t_type == 1 || t_type == 2 || t_type == 8 || t_type == 9 || t_type == 10)
|
|
{
|
|
//筛选客户id
|
|
var param = new List<Oracle.DataAccess.Client.OracleParameter>
|
|
{
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_importId", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal, Value = importId },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_stime", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Date, Value = stime },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_etime", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Date, Value = etime },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_type", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal, Value = t_type },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_Open", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2, Value = slt_Open },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_NotOpen", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2, Value = slt_NotOpen },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_resIdCursor", OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = new DataSet();
|
|
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
|
|
cmd.Connection = conn;
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = "PACK_RES.res_GetNotContactResId";
|
|
cmd.Parameters.AddRange(param.ToArray());
|
|
Oracle.DataAccess.Client.OracleDataAdapter adpt = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
|
|
adpt.Fill(ds);
|
|
foreach (DataRow item in ds.Tables[0].Rows)
|
|
{
|
|
list.Add(string.Format("{0}", item["IMPORTDATA"]));
|
|
}
|
|
return list;
|
|
}
|
|
else
|
|
{
|
|
//转换数据
|
|
var param = new List<Oracle.DataAccess.Client.OracleParameter>
|
|
{
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_importId", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal, Value = importId },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_type", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal, Value = t_type },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "p_resIdCursor", OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = new DataSet();
|
|
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
|
|
cmd.Connection = conn;
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = "PACK_RES.res_TransformCustomerData";
|
|
cmd.Parameters.AddRange(param.ToArray());
|
|
Oracle.DataAccess.Client.OracleDataAdapter adpt = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
|
|
adpt.Fill(ds);
|
|
foreach (DataRow item in ds.Tables[0].Rows)
|
|
{
|
|
list.Add(string.Format("{0}", item["importdata"]));
|
|
}
|
|
return list;
|
|
}
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据导入resid进行条件过滤
|
|
/// </summary>
|
|
/// <param name="tab">资源数据</param>
|
|
/// <param name="importId">导入id</param>
|
|
/// <param name="stime">过滤开始时间</param>
|
|
/// <param name="etime">过滤结束时间</param>
|
|
/// <param name="checkType_hid">选项ID</param>
|
|
/// <param name="subType_hid">工单过滤类型ID</param>
|
|
/// <returns></returns>
|
|
public List<string> res_GetFilterImportRes(DataTable tab, string importId, DateTime? stime, DateTime? etime, string checkType_hid, string subType_hid)
|
|
{
|
|
List<string> list = new List<string>();
|
|
|
|
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(OracleHelper.AYCRMConn);
|
|
//OracleConnection conn1 = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
List<DataRow> deleteRow = new List<DataRow>();
|
|
foreach (DataRow item in tab.Rows)
|
|
{
|
|
if (item["IMPORTDATA"] == DBNull.Value || item["IMPORTDATA"].ToString() == "")
|
|
deleteRow.Add(item);
|
|
}
|
|
foreach (DataRow item in deleteRow)
|
|
{
|
|
tab.Rows.Remove(item);
|
|
}
|
|
//将数据先插入虚拟表中去
|
|
OracleHelper.OracleBulkInsert(new Oracle.DataAccess.Client.OracleBulkCopy(conn, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction), "TEMP_IMP_BULKRESIMPORTID", tab);
|
|
|
|
//筛选客户id
|
|
var param = new List<Oracle.DataAccess.Client.OracleParameter>
|
|
{
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "v_importId", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Decimal, Value = importId },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "v_stime", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Date, Value = stime },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "v_etime", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Date, Value = etime },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "v_checkType_hid", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2, Value = checkType_hid },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "v_subType_hid", OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2, Value = subType_hid },
|
|
new Oracle.DataAccess.Client.OracleParameter() { ParameterName = "v_resIdCursor", OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet ds = new DataSet();
|
|
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
|
|
cmd.Connection = conn;
|
|
cmd.CommandType = CommandType.StoredProcedure;
|
|
cmd.CommandText = "PACK_RES.res_GetFilterImportRes";
|
|
cmd.Parameters.AddRange(param.ToArray());
|
|
Oracle.DataAccess.Client.OracleDataAdapter adpt = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
|
|
adpt.Fill(ds);
|
|
foreach (DataRow item in ds.Tables[0].Rows)
|
|
{
|
|
list.Add(string.Format("{0}", item["IMPORTDATA"]));
|
|
}
|
|
return list;
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataTable Res_GetCustomerByResIds(string CustomerIds)
|
|
{
|
|
try
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "p_resids", OracleType = OracleType.VarChar, Value = CustomerIds },
|
|
new OracleParameter() { ParameterName = "p_customer", OracleType = OracleType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_RES.res_GetCustomerByResIds", param.ToArray());
|
|
return dest.Tables[0];
|
|
}
|
|
catch { throw; }
|
|
}
|
|
|
|
/// <summary>
|
|
/// 判断该资源是否能够查看手机号码
|
|
/// </summary>
|
|
/// <param name="v_resid">客户ID</param>
|
|
/// <param name="v_userid">员工ID</param>
|
|
/// <param name="v_roleCodes">权限码</param>
|
|
/// <returns></returns>
|
|
public DataTable CheckResidCanShowMobile(string v_resid, decimal v_userid, string v_roleCodes)
|
|
{
|
|
try
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = v_resid },
|
|
new OracleParameter() { ParameterName = "v_userid", OracleType = OracleType.Number, Value = v_userid },
|
|
new OracleParameter() { ParameterName = "v_roleCodes", OracleType = OracleType.VarChar, Value = v_roleCodes },
|
|
new OracleParameter() { ParameterName = "v_data", OracleType = OracleType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_RES_CUSTOMER_CHECK.CheckResidCanShowMobile", param.ToArray());
|
|
return dest.Tables[0];
|
|
}
|
|
catch { throw; }
|
|
}
|
|
|
|
public DataTable GetOpenAccountRes()
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "p_openAccountRes", OracleType = OracleType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_RES.res_getOpenAccountRes", param.ToArray());
|
|
return dest.Tables[0];
|
|
}
|
|
|
|
public DataSet GetResChoiceExpor(string resStime, string resEtime, int checkWeixin, int checkOrder, string memoSubType, string memoStime, string memoEtime)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
{
|
|
conn.Open();
|
|
}
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName="p_resStime",OracleType= OracleType.VarChar,Value=resStime},
|
|
new OracleParameter() { ParameterName="p_resEtime",OracleType= OracleType.VarChar,Value=resEtime},
|
|
new OracleParameter() { ParameterName="p_checkWeixin",OracleType= OracleType.Number,Value=checkWeixin},
|
|
new OracleParameter() { ParameterName="p_checkOrder",OracleType= OracleType.Number,Value=checkOrder},
|
|
new OracleParameter() {ParameterName="p_memoSubType",OracleType = OracleType.VarChar,Value=memoSubType },
|
|
new OracleParameter() { ParameterName="p_memoStime",OracleType= OracleType.VarChar,Value=memoStime},
|
|
new OracleParameter() { ParameterName="p_memoEtime",OracleType= OracleType.VarChar,Value=memoEtime},
|
|
new OracleParameter() {ParameterName="p_data",OracleType = OracleType.Cursor,Direction=ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_RES.res_GetResChoiceExport", parms.ToArray());
|
|
trans.Commit();
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataSet GetResIdByMemoChoiceExport(DateTime? memoStime, DateTime? memoEtime, decimal? memoType1, decimal? memoType2, decimal? group1, decimal? group2)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
{
|
|
conn.Open();
|
|
}
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName="p_memoStime",OracleType= OracleType.DateTime,Value=memoStime},
|
|
new OracleParameter() { ParameterName="p_memoEtime",OracleType= OracleType.DateTime,Value=memoEtime},
|
|
new OracleParameter() { ParameterName="p_memoType1",OracleType= OracleType.Number,Value=memoType1},
|
|
new OracleParameter() { ParameterName="p_memoType2",OracleType= OracleType.Number,Value=memoType2},
|
|
new OracleParameter() { ParameterName="p_group1",OracleType= OracleType.Number,Value=group1},
|
|
new OracleParameter() { ParameterName="p_group2",OracleType= OracleType.Number,Value=group2},
|
|
new OracleParameter() {ParameterName="p_data",OracleType = OracleType.Cursor,Direction=ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_RES.res_GetResIdByMemoChoiceExport", parms.ToArray());
|
|
trans.Commit();
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataSet Res_GetHowDayNoCCall(DateTime v_resStartTime, DateTime v_resEndTime, DateTime v_whStartTime, DateTime v_whEndTime)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
{
|
|
conn.Open();
|
|
}
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var parms = new List<OracleParameter>()
|
|
{
|
|
new OracleParameter() { ParameterName="v_resStartTime",OracleType= OracleType.DateTime,Value=v_resStartTime},
|
|
new OracleParameter() { ParameterName="v_resEndTime",OracleType= OracleType.DateTime,Value=v_resEndTime},
|
|
new OracleParameter() { ParameterName="v_whStartTime",OracleType= OracleType.DateTime,Value=v_whStartTime},
|
|
new OracleParameter() { ParameterName="v_whEndTime",OracleType= OracleType.DateTime,Value=v_whEndTime},
|
|
new OracleParameter() {ParameterName="v_resIdCursor",OracleType = OracleType.Cursor,Direction=ParameterDirection.Output }
|
|
};
|
|
DataSet ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_RES.res_GetHowDayNoCCall", parms.ToArray());
|
|
trans.Commit();
|
|
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public void res_customerdetial_log(string ip, string resid, decimal inneruserid, decimal eid)
|
|
{
|
|
try
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "v_inneruserid", OracleType = OracleType.Number, Value =inneruserid},
|
|
new OracleParameter() { ParameterName = "v_eid", OracleType = OracleType.Number, Value = eid },
|
|
new OracleParameter() { ParameterName = "v_resid", OracleType = OracleType.VarChar, Value = resid },
|
|
new OracleParameter() { ParameterName = "v_ip", OracleType = OracleType.VarChar, Value = ip }
|
|
};
|
|
//OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_RES.res_CrossDB_ResgisterCustomer", param.ToArray());
|
|
OracleHelper.ExecuteNonQuery(ConStringHelper.WSKUSERCon, CommandType.StoredProcedure, "PACK_RESLOG.res_customerdetial_log", param.ToArray());
|
|
}
|
|
catch (Exception e)
|
|
{
|
|
WX.CRM.Common.LogHelper.Error(e.ToString());
|
|
}
|
|
}
|
|
|
|
#region 资源效果分析
|
|
|
|
public DataSet Res_ResourceEffectAnalysis(ref Pager pager, DateTime? p_sd1, DateTime? p_sd2, string orderSql, decimal restypeId, decimal activityId, QueryUserComboDto usercomboDto)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var p2 = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() {ParameterName = "p_sd1", OracleType = OracleType.DateTime, Value = p_sd1},
|
|
new OracleParameter() {ParameterName = "p_sd2", OracleType = OracleType.DateTime, Value = p_sd2},
|
|
new OracleParameter() {ParameterName = "p_restypeId", OracleType = OracleType.Number, Value = restypeId},
|
|
new OracleParameter() {ParameterName = "p_activityId", OracleType = OracleType.Number, Value = activityId},
|
|
new OracleParameter() { ParameterName = "p_pageIndex", OracleType = OracleType.Number, Value = pager.page },
|
|
new OracleParameter() { ParameterName = "p_pageSize", OracleType = OracleType.Number, Value = pager.rows },
|
|
new OracleParameter() { ParameterName = "p_rowCount", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "v_emps", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
new OracleParameter() { ParameterName = "p_dataCount", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
//string.IsNullOrEmpty(pager.order)
|
|
// ?new OracleParameter(){ ParameterName = "p_Sort",OracleType = OracleType.VarChar,Value = DBNull.Value}
|
|
// :new OracleParameter(){ ParameterName = "p_Sort",OracleType = OracleType.VarChar,Value = pager.order}
|
|
// ,
|
|
// string.IsNullOrEmpty(pager.sort)
|
|
// ?new OracleParameter(){ ParameterName = "p_Column",OracleType = OracleType.VarChar,Value = DBNull.Value}
|
|
// :new OracleParameter(){ ParameterName = "p_Column",OracleType = OracleType.VarChar,Value = pager.sort} ,
|
|
|
|
new OracleParameter() { ParameterName="p_orderSql", OracleType=OracleType.VarChar, Value=orderSql },
|
|
string.IsNullOrEmpty(usercomboDto.groupIds) ? new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = DBNull.Value } : new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = usercomboDto.groupIds },
|
|
usercomboDto.userId.HasValue ? new OracleParameter() { ParameterName = "p_inneruserId", OracleType = OracleType.VarChar, Value = usercomboDto.userId.Value.ToString() } : new OracleParameter() { ParameterName = "p_inneruserId", OracleType = OracleType.VarChar, Value = DBNull.Value },
|
|
string.IsNullOrEmpty(usercomboDto.deptId) ? new OracleParameter() { ParameterName = "p_dept", OracleType = OracleType.VarChar, Value = DBNull.Value } : new OracleParameter() { ParameterName = "p_dept", OracleType = OracleType.VarChar, Value = usercomboDto.deptId },
|
|
usercomboDto.companyId.HasValue ? new OracleParameter() { ParameterName = "p_companyId", OracleType = OracleType.VarChar, Value = usercomboDto.companyId.Value.ToString() } : new OracleParameter() { ParameterName = "p_companyId", OracleType = OracleType.VarChar, Value = DBNull.Value }
|
|
};
|
|
var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_RES.res_ResourceEffectAnalysis", p2.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
public DataSet Res_ResourceOrderList(DateTime? p_sd1, DateTime? p_sd2, decimal restypeId, decimal activityId, string groupId, string inneruserId)
|
|
{
|
|
var conn = new OracleConnection(OracleHelper.AYCRMConn);
|
|
if (conn.State == ConnectionState.Closed)
|
|
conn.Open();
|
|
try
|
|
{
|
|
using (var trans = conn.BeginTransaction())
|
|
{
|
|
var p2 = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() {ParameterName = "p_sd1", OracleType = OracleType.DateTime, Value = p_sd1},
|
|
new OracleParameter() {ParameterName = "p_sd2", OracleType = OracleType.DateTime, Value = p_sd2},
|
|
new OracleParameter() {ParameterName = "p_restypeId", OracleType = OracleType.Number, Value = restypeId},
|
|
new OracleParameter() {ParameterName = "p_activityId", OracleType = OracleType.Number, Value = activityId},
|
|
new OracleParameter() { ParameterName = "p_groupId", OracleType = OracleType.VarChar, Value = groupId },
|
|
new OracleParameter() { ParameterName = "p_inneruserId", OracleType = OracleType.VarChar, Value = inneruserId },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor, Direction = ParameterDirection.Output },
|
|
};
|
|
var ds = OracleHelper.DataQueray(trans, CommandType.StoredProcedure, "PACK_RES.res_ResourceOrderList", p2.ToArray());
|
|
trans.Commit();
|
|
return ds;
|
|
}
|
|
}
|
|
catch (Exception)
|
|
{
|
|
throw;
|
|
}
|
|
finally
|
|
{
|
|
conn.Close();
|
|
}
|
|
}
|
|
|
|
#endregion 资源效果分析
|
|
|
|
public void HandlerSalerAddCustomer(string resId, decimal userId)
|
|
{
|
|
try
|
|
{
|
|
var param = new List<OracleParameter>
|
|
{
|
|
new OracleParameter() { ParameterName = "p_resId", OracleType = OracleType.VarChar, Value = resId},
|
|
new OracleParameter() { ParameterName = "p_userId", OracleType = OracleType.Number, Value = userId }
|
|
};
|
|
OracleHelper.ExecuteNonQuery(CommandType.StoredProcedure, "PACK_RES_ALLOCATE.HandlerSalerAddCustomer", param.ToArray());
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
LogHelper.Error(ex.ToString());
|
|
}
|
|
}
|
|
|
|
public DataTable ResTrace(string resId)
|
|
{
|
|
var param = new List<OracleParameter> {
|
|
new OracleParameter() { ParameterName = "p_resId", OracleType = OracleType.VarChar, Value = resId },
|
|
new OracleParameter() { ParameterName = "p_data", OracleType = OracleType.Cursor,Direction = ParameterDirection.Output }
|
|
};
|
|
DataSet dest = OracleHelper.DataQueray(CommandType.StoredProcedure, "PACK_RES.res_trace", param.ToArray());
|
|
return dest.Tables[0];
|
|
}
|
|
}
|
|
} |