private void GetWordsWithParent(string wordIds)
{
if (!string.IsNullOrWhiteSpace(wordIds))
{
//check
string[] s_ids = wordIds.Split(',');
foreach (var id in s_ids)
{
Convert.ToInt32(id);
}
var db = DBCRMTree.GetInstance();
var sql_text_part = Interna ? "[text_en]" : "[text_cn]";
var sql = string.Format(@"SELECT
[id]
,[p_id]
,{0} as [text]
,[value] --,[id] as [value]
,[isSelect] as selected
FROM [words]
WHERE id in({1}) or p_id IN({1})
order by p_id,sort
", sql_text_part, wordIds);
var o = db.Query <dynamic>(sql);
Response.Write(JsonConvert.SerializeObject(o));
}
}
private void Save_Service(dynamic data)
{
CRMTree.BLL.BL_Campaign bll = new CRMTree.BLL.BL_Campaign();
var db = DBCRMTree.GetInstance();
try
{
using (var tran = db.GetTransaction())
{
Save_Schedule(data, db);
Save_Resources(data, db);
Save_Options(data);
Save_Departments(data, db);
tran.Complete();
}
Save_Camp_Call(data);
Response.Write(JsonConvert.SerializeObject(new { isOK = true }));
}
catch (Exception ex)
{
db.AbortTransaction();
Response.Write(JsonConvert.SerializeObject(new { isOK = false, msg = ex.Message }));
}
}
private void ExecActive(dynamic data)
{
var db = DBCRMTree.GetInstance();
Response.Write(JsonConvert.SerializeObject(new { isOK = true }));
}
/// <summary>
/// 保存客户信息
/// </summary>
/// <param name="data"></param>
private void Save_Cars(dynamic data)
{
var db = DBCRMTree.GetInstance();
try
{
var au_code = (int)data.au_code;
var CI_Code = (int)data.o.CI_Code;
Save_Cars(data, au_code, CI_Code);
using (var tran = db.GetTransaction())
{
if (au_code > 0)
{
int Car_Code = Get_User_Only_Car(db, au_code);
Save_Cars(data, au_code, CI_Code);
}
tran.Complete();
}
Response.Write(JsonConvert.SerializeObject(new { isOK = true }));
}
catch (Exception ex)
{
db.AbortTransaction();
Response.Write(JsonConvert.SerializeObject(new { isOK = false, msg = ex.Message }));
}
}
private void Save_Param_Value(dynamic data, int cg_code, DBCRMTree db)
{
var s_param_value = JsonConvert.SerializeObject(data.param_value);
List <CT_Param_Value> pvs = JsonConvert.DeserializeObject <List <CT_Param_Value> >(s_param_value);
var o = new
{
PV_CG_Code = cg_code,
PV_UType = (byte)UserSession.User.UG_UType,
PV_AD_OM_Code = (int)UserSession.DealerEmpl.DE_AD_OM_Code
};
db.Execute(@"DELETE FROM CT_Param_Value WHERE
PV_CG_Code = @PV_CG_Code
and PV_UType = @PV_UType
and PV_AD_OM_Code = @PV_AD_OM_Code
and PV_Type=1"
, o);
foreach (var pv in pvs)
{
pv.PV_CG_Code = o.PV_CG_Code;
pv.PV_UType = o.PV_UType;
pv.PV_AD_OM_Code = o.PV_AD_OM_Code;
pv.Insert();
}
}
private void Save_Param_Value_Report(dynamic data)
{
var s_param_value = JsonConvert.SerializeObject(data.param_value);
List <CT_Param_Value> pvs = JsonConvert.DeserializeObject <List <CT_Param_Value> >(s_param_value);
var pv_type = (byte)data.PV_Type;
var o = new
{
PL_RP_Code = (int)data.PL_RP_Code,
PV_Type = pv_type,
PV_UType = (byte)UserSession.User.UG_UType,
PV_AD_OM_Code = UserSession.DealerEmpl.DE_AD_OM_Code
};
var db = DBCRMTree.GetInstance();
db.Execute(@"DELETE FROM CT_Param_Value
WHERE (PV_Type=10 or PV_Type=@PV_Type) AND PV_CG_Code=-1
AND exists(select 1 from CT_Paramters_list where PV_PL_Code = PL_Code and PL_RP_Code=@PL_RP_Code)
AND PV_UType=@PV_UType AND PV_AD_OM_Code = @PV_AD_OM_Code",
o);
foreach (var pv in pvs)
{
pv.PV_CG_Code = -1;
pv.PV_Type = o.PV_Type;
pv.PV_UType = o.PV_UType;
pv.PV_AD_OM_Code = o.PV_AD_OM_Code;
pv.Insert();
}
Response.Write(JsonConvert.SerializeObject(new { isOK = true }));
}
private void Save_Options(dynamic data)
{
var s_options = JsonConvert.SerializeObject(data.options);
if (string.IsNullOrWhiteSpace(s_options))
{
return;
}
CT_Service_Dep servDep = JsonConvert.DeserializeObject <CT_Service_Dep>(s_options);
servDep.SD_AD_Code = UserSession.DealerEmpl.DE_AD_OM_Code;
servDep.SD_Update_dt = DateTime.Now;
var db = DBCRMTree.GetInstance();
if (CT_Service_Dep.Exists("SD_AD_Code = @SD_AD_Code AND SD_PDN_Code = @SD_PDN_Code", servDep))
{
db.Update <CT_Service_Dep>(@"SET
SD_SA_Selection=@SD_SA_Selection,
SD_Serv_Package=@SD_Serv_Package,
SD_Update_dt=@SD_Update_dt
where SD_AD_Code = @SD_AD_Code AND SD_PDN_Code = @SD_PDN_Code", servDep);
}
else
{
servDep.Insert();
}
}
/// <summary> /// 根据词语编号获得下拉列表数据
/// </summary>
/// <param name="wordIds">1,2,3</param>
private void GetWords(string wordIds)
{
if (!string.IsNullOrWhiteSpace(wordIds))
{
//check
string[] s_ids = wordIds.Split(',');
foreach (var id in s_ids)
{
Convert.ToInt32(id);
}
var db = DBCRMTree.GetInstance();
var sql_text_part = Interna ? "[text_en]" : "[text_cn]";
var sql_MC_Name_part = Interna ? "[MC_Name_EN]" : "[MC_Name_CN]";
var sql = string.Format(@"SELECT p_id ,{0} as [text],[value],[isSelect] AS selected,sort FROM [words]
WHERE p_id IN({1}) AND p_id <> 4064
UNION
SELECT '4064' AS p_id,{2} AS [text],MC.MC_Code AS [value] ,'true' as selected,MC.MC_Code AS sort FROM CT_Messaging_Carriers MC
ORDER BY p_id,sort
", sql_text_part, wordIds, sql_MC_Name_part);
var o = db.Query <dynamic>(sql);
Response.Write(JsonConvert.SerializeObject(o));
}
}
/// <summary>
/// 保存经销商员工信息
/// </summary>
/// <param name="data"></param>
/// <param name="au_code"></param>
private void Save_Contacts_List(dynamic data, long au_code)
{
var s_personal = JsonConvert.SerializeObject(data.personal);
// int Empl_Code = (int)data.AU_Code;
// if (Empl_Code > 0)
// {
// var empl = CT_Dealer_Empl.SingleOrDefault(Empl_Code);
// empl.EX_DE_Activate_dt = empl.DE_Activate_dt.HasValue ? empl.DE_Activate_dt.Value.ToString(Interna ? "MM/dd/yyyy" : "yyyy-MM-dd") : "";
// o.dealerEmpl = empl;
// CT_All_User empl = JsonConvert.DeserializeObject<CT_All_User>(s_personal);
CT_Drivers_List Contacts_List = JsonConvert.DeserializeObject <CT_Drivers_List>(s_personal);
Contacts_List.DL_AU_Code = au_code;
Contacts_List.DL_M_AU_Code = data.MAU_Code;
Contacts_List.DL_Access = data.personal.DL_Acc == "" ? null : data.personal.DL_Acc;
Contacts_List.DL_Relation = data.personal.DL_Rel == "" ? null : data.personal.DL_Rel;
Contacts_List.DL_Update_dt = DateTime.Now;
if (Contacts_List.DL_AU_Code > 0)
{
var db = DBCRMTree.GetInstance();
db.Update <CT_Drivers_List>(@" Set
[DL_Access] = @DL_Access
,[DL_Relation] = @DL_Relation
,[DL_Update_dt] = @DL_Update_dt
Where DL_M_AU_Code=@DL_M_AU_Code and DL_AU_Code=@DL_AU_Code ", Contacts_List);
}
else
{
Contacts_List.Insert();
}
}
private void Get_Dealer_Advisor_All()
{
var db = DBCRMTree.GetInstance();
var sql = BL_Reports.GetReportSql(131, null);
var o = db.Query <dynamic>(sql);
Response.Write(JsonConvert.SerializeObject(o));
}
/// <summary>
/// 获得运输类型
/// </summary>
/// <returns></returns>
private IEnumerable <dynamic> Get_CT_Transportation()
{
var db = DBCRMTree.GetInstance();
var text = Interna ? "[PTP_Desc_EN]" : "[PTP_Desc_CN]";
var sql = string.Format(@"SELECT [PTP_Code] as [value],{0} as [text] FROM [CT_Transportation]", text);
return(db.Query <dynamic>(sql));
}
/// <summary>
/// 获得服务类别
/// </summary>
/// <returns></returns>
private IEnumerable <dynamic> Get_CT_Serv_Category()
{
var db = DBCRMTree.GetInstance();
var text = Interna ? "[SC_Desc_EN]" : "[SC_Desc_CN]";
var sql = string.Format(@"SELECT [SC_Code] as [value],{0} as [text] FROM [CT_Serv_Category] where SC_AD_Code={1}", text, UserSession.DealerEmpl.DE_AD_OM_Code);
return(db.Query <dynamic>(sql));
}
private IEnumerable <int> Get_Report_Param(int RP_Code)
{
var db = DBCRMTree.GetInstance();
var sql = string.Format(@"SELECT PL_CODE FROM CT_REPORTS INNER JOIN CT_PARAMTERS_LIST ON RP_CODE=PL_RP_CODE
WHERE RP_CODE={0}", RP_Code);
return(db.Query <int>(sql));
}
private IEnumerable <dynamic> Get_CT_MP_Mileage(dynamic data)
{
var db = DBCRMTree.GetInstance();
return(db.Query <dynamic>(
";exec [CT_Get_MP_Mileage] @Dealer_Code,@CI_Code,@IsEn"
, new { Dealer_Code = UserSession.Dealer.AD_Code, CI_Code = (int)data.CI_Code, IsEn = Interna }
));
}
private void Get_User_Groups(dynamic data)
{
var db = DBCRMTree.GetInstance();
var o = db.Query <dynamic>(";exec P_Get_User_Groups @UG,@PUG,@IsEn"
, new { UG = UserSession.User.AU_UG_Code, PUG = (int)data.PUG, IsEn = Interna }
);
Response.Write(JsonConvert.SerializeObject(o));
}
private void Get_Auth_Tab_Links()
{
var db = DBCRMTree.GetInstance();
var o = db.Query <dynamic>(";exec P_Get_Auth_Tab_Links @AU_Code,@IsEn"
, new { AU_Code = UserSession.User.AU_Code, IsEn = Interna }
);
Response.Write(JsonConvert.SerializeObject(o));
}
private void Save_Incoming_Calls(dynamic data)
{
//var ug_code = this.UserSession.User.AU_UG_Code;
//if (ug_code != 28 && ug_code != 40)
//{
// throw new Exception(Interna ? "Without the permission!" : "无此权限！");
//}
var db = DBCRMTree.GetInstance();
try
{
using (var tran = db.GetTransaction())
{
//CT_Handler
var s_data = JsonConvert.SerializeObject(data.data);
if (string.IsNullOrWhiteSpace(s_data))
{
throw new Exception(Interna ? "No data!" : "无数据！");
}
CT_Comm_History ch = JsonConvert.DeserializeObject <CT_Comm_History>(s_data);
ch.CH_UType = (byte)UserSession.User.UG_UType;
ch.CH_AD_OM_Code = UserSession.DealerEmpl.DE_AD_OM_Code;
ch.CH_Update_dt = DateTime.Now;
ch.CH_Code = Convert.ToInt32(ch.Insert());
CT_Handler handler = JsonConvert.DeserializeObject <CT_Handler>(s_data);
handler.HD_CH_Code = ch.CH_Code;
handler.HD_AU_Code = UserSession.User.AU_Code;
handler.HD_Code = Convert.ToInt32(handler.Insert());
CT_Dialog_Hist dh = JsonConvert.DeserializeObject <CT_Dialog_Hist>(s_data);
if (!dh.DH_Duration.HasValue)
{
dh.DH_Duration = -1;
}
if (!dh.DH_legacy.HasValue)
{
dh.DH_legacy = false;
}
dh.DH_HD_Code = handler.HD_Code;
dh.DH_Update_dt = DateTime.Now;
dh.Insert();
//db.AbortTransaction();
tran.Complete();
}
Response.Write(JsonConvert.SerializeObject(new { isOK = true }));
}
catch (Exception ex)
{
db.AbortTransaction();
Response.Write(JsonConvert.SerializeObject(new { isOK = false, msg = ex.Message }));
}
}
private IEnumerable <dynamic> Get_CT_Dept_Type()
{
var db = DBCRMTree.GetInstance();
var sql_text_part = Interna ? "[PDT_Name_EN]" : "[PDT_Name_CN]";
var sql = string.Format(@"
SELECT {0} as [text],PDT_Code as [value],[PDT_Var_Type] FROM CT_Dept_Type",
sql_text_part);
return(db.Query <dynamic>(sql));
}
/// <summary>
/// 获得服务类型
/// </summary>
/// <returns></returns>
private void Get_CT_Service_Types(dynamic data)
{
var ST_SC_Code = (int)data.id;
var db = DBCRMTree.GetInstance();
var text = Interna ? "[ST_Desc_EN]" : "[ST_Desc_CN]";
var sql = string.Format(@"SELECT [ST_Code] as [value],{0} as [text] FROM [CT_Service_Types]", text);
var o = db.Query <dynamic>(sql + " where ST_SC_Code=@0", ST_SC_Code);
Response.Write(JsonConvert.SerializeObject(o));
}
private void Get_CustomerCars(dynamic data)
{
var AU_Code = (int)data.AU_Code;
var db = DBCRMTree.GetInstance();
var sql = ";exec [CT_GetCustomerCars] @User_code,@IsEn";
var o = db.Query <dynamic>(sql, new { User_code = AU_Code, IsEn = Interna });
Response.Write(JsonConvert.SerializeObject(o));
}
private IEnumerable <dynamic> Get_CT_Appt_Mileage(dynamic data)
{
var db = DBCRMTree.GetInstance();
var sql = ";exec [P_Appt_GetMileage] @CI_Code";
return(db.Query <dynamic>(sql, new
{
CI_Code = (int)data.CI_Code
}));
}
private IEnumerable <dynamic> Get_CT_Camp_Tags()
{
var db = DBCRMTree.GetInstance();
var sql = string.Format(@"SELECT CT_Code AS [value],
{0} AS [text]
FROM CT_Camp_Tags;",
Interna ? "CT_Desc_EN" : "CT_Desc_CN");
return(db.Query <dynamic>(sql));
}
private void Get_Tab_Links(int TL_Code)
{
var db = DBCRMTree.GetInstance();
var o = db.Query <dynamic>(";exec P_Get_Tab_Links @TL_Code,@IsEn"
, new { TL_Code = TL_Code, IsEn = Interna }
);
string _o = JsonConvert.SerializeObject(o);
Response.Write(_o);
}
private IEnumerable <dynamic> Get_CT_Appt_GetDealerTime(dynamic data)
{
var db = DBCRMTree.GetInstance();
var sql = ";exec [P_Appt_GetDealerTime] @AD_Code";
return(db.Query <dynamic>(sql, new
{
AD_Code = data.AD_Code.Value
}));
}
private int Get_User_Only_Car(DBCRMTree db, long au_code)
{
string sql = "select top 1 CI_Code from CT_Car_Inventory where CI_AU_Code=@0;";
dynamic car = db.Query <dynamic>(sql, au_code);
dynamic _car = JsonConvert.DeserializeObject <dynamic>(JsonConvert.SerializeObject(car));
string _CI = JsonConvert.SerializeObject(_car[0].CI_Code);
return(Convert.ToInt32(_CI));
}
private IEnumerable <dynamic> Get_CT_Insurance_Comp()
{
var db = DBCRMTree.GetInstance();
var sql = @"SELECT
[IC_Code] as [value]
,[IC_Name] as [text]
FROM [CT_Insurance_Comp]
order by IC_Name";
return(db.Query <dynamic>(sql));
}
/// <summary>
/// 年份
/// </summary>
/// <returns></returns>
private IEnumerable <dynamic> Get_CT_Years()
{
var db = DBCRMTree.GetInstance();
var sql = @"SELECT
[YR_Code] as [value]
,[YR_Year] as [text]
FROM [CT_Years]
order by YR_Year desc";
return(db.Query <dynamic>(sql));
}
private IEnumerable <dynamic> Get_CT_Insurance_Agents(int IA_IC_Code)
{
var db = DBCRMTree.GetInstance();
var sql = @"SELECT IA_Code AS value
,AU_Name AS [text]
,IA_AU_Code
FROM CT_Insurance_Agents ia INNER JOIN CT_All_Users au
ON au.AU_Code = ia.IA_AU_Code
WHERE IA_IC_Code = @0";
return(db.Query <dynamic>(sql, IA_IC_Code));
}
/// <summary>
/// 颜色
/// </summary>
/// <returns></returns>
private IEnumerable <dynamic> Get_CT_Color_List()
{
var db = DBCRMTree.GetInstance();
var sql_text_part = Interna ? "[CL_Color_EN]" : "[CL_Color_CN]";
var sql = string.Format(@"SELECT
{0} as [text]
,[CL_Code] as [value]
FROM [CT_Color_List]
", sql_text_part);
return(db.Query <dynamic>(sql));
}
private IEnumerable <dynamic> Get_CT_User_Groups()
{
var db = DBCRMTree.GetInstance();
var lng = Interna ? "EN" : "CN";
var sql = string.Format(@"SELECT [UG_Code] AS value
,UG_Name_{0} AS [text]
,(Select text_{0} from [Words] where p_id=4203 and value=[UG_UType]) as [group]
FROM [CT_User_Groups]
WHERE UG_UType <= @0 and isnull(UG_Name_{0},'') <> '' and UG_Code <= @1 order by UG_Utype Asc,UG_Code ASC", lng);
return(db.Query <dynamic>(sql, UserSession.User.UG_UType, UserSession.User.AU_UG_Code));
}
