public string APPtest(string parameter)
{
string value = HttpContext.Current.Request.Form["Data"];
JObject jObject = (JObject)JsonConvert.DeserializeObject(value);
string DBID = jObject["DBID"].ToString();
string UserName = jObject["UserName"].ToString();
string PassWord = jObject["PassWord"].ToString();
string SearchType = jObject["SearchType"].ToString();
string PersonId = jObject["PersonId"].ToString();
JObject jsonRoot = new JObject();
JArray Arr = new JArray();
JObject basedata = new JObject();
KEEPER.K3.App.CRMService crm = new KEEPER.K3.App.CRMService();
List <long> ids = new List <long>();
string sContent = "";
Context ctx = getContext(UserName, PassWord, 2052, DBID, "http://localhost/K3Cloud/");
ApiClient client = new ApiClient("http://localhost/K3Cloud/");
bool bLogin = client.Login(DBID, UserName, PassWord, 2052);
if (bLogin)//登录成功
{
if (SearchType.Equals("0"))
{
ids = crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId));
jsonRoot.Add("IsSuccess", "true");
jsonRoot.Add("Reason", "");
}
else if (SearchType.Equals("1"))
{
ids = crm.getProjectIds(ctx, Convert.ToInt64(PersonId));
jsonRoot.Add("IsSuccess", "true");
jsonRoot.Add("Reason", "");
}
else
{
jsonRoot.Add("IsSuccess", "false");
jsonRoot.Add("Reason", "请传入正确的参数");
}
}
else
{
jsonRoot.Add("IsSuccess", "false");
jsonRoot.Add("Reason", "登录失败");
}
if (ids != null)
{
Arr.Add(ids.ToArray());
}
jsonRoot.Add("Ids", Arr);
sContent = JsonConvert.SerializeObject(jsonRoot);
return(sContent);
}
public string APPtest(string parameter)
{
string value = HttpContext.Current.Request.Form["Data"];
JObject jObject = (JObject)JsonConvert.DeserializeObject(value);
string DBID = jObject["DBID"].ToString();
string UserName = jObject["UserName"].ToString();
string PassWord = jObject["PassWord"].ToString();
//过滤条件：起始日期/截至日期/部门/业务员
string PersonId = jObject["PersonId"].ToString();
string SalesId = jObject["SalesId"].ToString();
string DeptId = jObject["DeptId"].ToString();
string startDate = jObject["startDate"].ToString();
string endDate = jObject["endDate"].ToString();
JObject jsonRoot = new JObject();
JArray Arr = new JArray();
JObject basedata = new JObject();
KEEPER.K3.App.CRMService crm = new KEEPER.K3.App.CRMService();
string sContent = "";
Context ctx = getContext(UserName, PassWord, 2052, DBID, "http://localhost/K3Cloud/");
ApiClient client = new ApiClient("http://localhost/K3Cloud/");
bool bLogin = client.Login(DBID, UserName, PassWord, 2052);
if (bLogin)//登录成功
{
string salerLimit = "";
Boolean flag = false;
//获取当前用户权限内的销售数据
if (crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId)) != null)
{
//获取当前用户可见的销售员集合
List <long> salerList = crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId));
if (salerList != null)
{
var a = from Id in salerList select Id;
string ids = string.Join(",", a.ToArray());
salerLimit = string.Format(@"and SALESMAN.FID in ({0})", ids);
}
}
//报表sql
StringBuilder sql1 = new StringBuilder();
sql1.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTL.FNAME) AS FSeq, DEPTL.FNAME AS DEPTNAME, EMPL.FNAME AS SALERNAME, FBILLNO, CONVERT(FLOAT, ROUND(F_PEJK_SUMAMOUNT, 2)) AS SUMAMOUNT, F_PEJK_PRONO, CONVERT(FLOAT, ROUND(F_PEJK_FPAmount, 2)) AS FPAMOUNT, CAST(CONVERT(FLOAT, ROUND(F_PEJK_SumRatio * 100, 3)) as varchar) + ' %' AS RATIO ");
sql1.AppendLine(" FROM PEJK_PRODETAIL DETAIL ");
sql1.AppendLine(" LEFT JOIN T_CRM_CONTRACT CONTRACT ON DETAIL.FID = CONTRACT.FID ");
sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = CONTRACT.FSALERID ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT_L DEPTL ON DEPTL.FDEPTID = SALESMAN.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_STAFF STAFF ON STAFF.FSTAFFID = SALESMAN.FSTAFFID ");
sql1.AppendLine(" LEFT JOIN T_HR_EMPINFO_L EMPL ON EMPL.FID = STAFF.FEMPINFOID ");
sql1.AppendLine(" WHERE DEPTL.FLOCALEID = 2052 AND EMPL.FLOCALEID = 2052 ");
if (flag)
{
sql1.AppendLine(salerLimit);
}
if (!SalesId.Equals("") && SalesId != null)
{
sql1.AppendLine(" and STAFF.fstaffid in (").Append(SalesId).Append(") ");
}
if (!DeptId.Equals("") && DeptId != null)
{
sql1.AppendLine(" and AND DEPT.fdeptid in (").Append(DeptId).Append(") ");
}
if (!startDate.Equals("") && startDate != null)
{
sql1.AppendFormat(" AND CONTRACT.FDATE >= '{0}' ", startDate);
}
if (!endDate.Equals("") && endDate != null)
{
sql1.AppendFormat(" AND CONTRACT.FDATE <= '{0}' ", endDate);
}
DynamicObjectCollection periodColl = DBUtils.ExecuteDynamicObject(ctx, sql1.ToString());
foreach (DynamicObject item in periodColl)
{
basedata = new JObject();
basedata.Add("FSeq", Convert.ToInt64(item["FSeq"]));
basedata.Add("DEPTNAME", Convert.ToInt64(item["DEPTNAME"]));
basedata.Add("SALERNAME", Convert.ToInt64(item["SALERNAME"]));
basedata.Add("FBILLNO", Convert.ToInt64(item["FBILLNO"]));
basedata.Add("SUMAMOUNT", Convert.ToInt64(item["SUMAMOUNT"]));
basedata.Add("F_PEJK_PRONO", Convert.ToInt64(item["F_PEJK_PRONO"]));
basedata.Add("FPAMOUNT", Convert.ToInt64(item["FPAMOUNT"]));
basedata.Add("RATIO", Convert.ToInt64(item["RATIO"]));
jsonRoot.Add("Result", basedata);
}
}
sContent = JsonConvert.SerializeObject(jsonRoot);
return(sContent);
}
public string APPtest(string parameter)
{
string value = HttpContext.Current.Request.Form["Data"];
JObject jObject = (JObject)JsonConvert.DeserializeObject(value);
string DBID = jObject["DBID"].ToString();
string UserName = jObject["UserName"].ToString();
string PassWord = jObject["PassWord"].ToString();
//过滤条件：起始日期/截至日期/部门/业务员
string PersonId = jObject["PersonId"].ToString();
string SalesId = jObject["SalesId"].ToString();
string DeptId = jObject["DeptId"].ToString();
string startDate = jObject["startDate"].ToString();
string endDate = jObject["endDate"].ToString();
JObject jsonRoot = new JObject();
JArray Arr = new JArray();
JObject basedata = new JObject();
KEEPER.K3.App.CRMService crm = new KEEPER.K3.App.CRMService();
string sContent = "";
Context ctx = getContext(UserName, PassWord, 2052, DBID, "http://localhost/K3Cloud/");
ApiClient client = new ApiClient("http://localhost/K3Cloud/");
bool bLogin = client.Login(DBID, UserName, PassWord, 2052);
if (bLogin)//登录成功
{
//生成中间临时表
System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1)); // 当地时区
string timeStamp = (DateTime.Now - startTime).TotalMilliseconds.ToString();
string salerLimit = "";
Boolean flag = false;
//获取当前用户权限内的销售数据
if (crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId)) != null)
{
//获取当前用户可见的销售员集合
List <long> salerList = crm.getSalerPersonids(ctx, Convert.ToInt64(PersonId));
if (salerList != null)
{
var a = from Id in salerList select Id;
string ids = string.Join(",", a.ToArray());
salerLimit = string.Format(@"and SALESMAN.FID in ({0})", ids);
}
}
//报表sql
StringBuilder sql1 = new StringBuilder();
sql1.AppendLine(@"/*dialect*/ SELECT DEPTNAME, SALERNAME, QUOTA, COMPLETEAMOUNT, ");
sql1.AppendFormat(" CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar)+' %' AS COMPLETERATE INTO {0} ", timeStamp);
sql1.AppendLine(" FROM ");
sql1.AppendLine(" (SELECT DEPTL.FNAME AS DEPTNAME, ");
sql1.AppendLine(" EMPL.FNAME AS SALERNAME, ");
sql1.AppendLine(" (SELECT F_PEJK_CONTRACTQUNTA FROM PEJK_SALERQUNTAENTRY WHERE F_PEJK_SALER = RESOLVESALER.F_PEJK_SALER) AS QUOTA, ");
sql1.AppendLine(" SUM(F_PEJK_DETAILLAMOUNT) AS COMPLETEAMOUNT ");
sql1.AppendLine(" FROM PEJK_SALECONTRACTENTRY RESOLVESALER ");
sql1.AppendLine(" LEFT JOIN PEJK_SALECONTRACTS RESOLVEBASIC ON RESOLVESALER.FID = RESOLVEBASIC.FID ");
sql1.AppendLine(" LEFT JOIN V_BD_SALESMAN SALESMAN ON SALESMAN.FID = RESOLVESALER.F_PEJK_SALER ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT_L DEPTL ON DEPTL.FDEPTID = SALESMAN.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_DEPARTMENT DEPT ON DEPTL.FDEPTID = DEPT.FDEPTID ");
sql1.AppendLine(" LEFT JOIN T_BD_STAFF STAFF ON STAFF.FSTAFFID = SALESMAN.FSTAFFID ");
sql1.AppendLine(" LEFT JOIN T_HR_EMPINFO_L EMPL ON EMPL.FID = STAFF.FEMPINFOID ");
sql1.AppendLine(" WHERE DEPTL.FLOCALEID = 2052 AND EMPL.FLOCALEID = 2052 ");
if (flag)
{
sql1.AppendLine(salerLimit);
}
if (!SalesId.Equals("") && SalesId != null)
{
sql1.AppendLine(" and STAFF.fstaffid in (").Append(SalesId).Append(") ");
}
if (!DeptId.Equals("") && DeptId != null)
{
sql1.AppendLine(" and AND DEPT.fdeptid in (").Append(DeptId).Append(") ");
}
if (!startDate.Equals("") && startDate != null)
{
sql1.AppendFormat(" AND RESOLVEBASIC.F_PEJK_DATE >= '{0}' ", startDate);
}
if (!endDate.Equals("") && endDate != null)
{
sql1.AppendFormat(" AND RESOLVEBASIC.F_PEJK_DATE <= '{0}' ", endDate);
}
sql1.AppendLine(" GROUP BY F_PEJK_SALER, EMPL.FNAME, DEPTL.FNAME) TMP ");
DBUtils.ExecuteDynamicObject(ctx, sql1.ToString());
StringBuilder sql2 = new StringBuilder();
sql2.AppendFormat(@"/*dialect*/INSERT INTO {0} SELECT '合计' , '', QUOTA, COMPLETEAMOUNT, ", timeStamp);
sql2.AppendLine(" CAST(CONVERT(FLOAT, ROUND((COMPLETEAMOUNT * 1.00 / (QUOTA * 1.00)) * 100, 3)) as varchar) + ' %' AS COMPLETERATE ");
sql2.AppendLine(" FROM ");
sql2.AppendFormat(" (SELECT SUM(QUOTA) AS QUOTA, SUM(COMPLETEAMOUNT) AS COMPLETEAMOUNT FROM {0}) TMP ", timeStamp);
DBUtils.ExecuteDynamicObject(ctx, sql2.ToString());
StringBuilder sql3 = new StringBuilder();
sql3.AppendFormat(@"/*dialect*/ SELECT ROW_NUMBER() OVER (ORDER BY DEPTNAME) AS FSeq, DEPTNAME, SALERNAME, CONVERT(FLOAT, ROUND(QUOTA, 2)) AS TOTALQUOTA, CONVERT(FLOAT, ROUND(COMPLETEAMOUNT, 2)) AS TOTALAMOUNT, COMPLETERATE ");
sql3.AppendFormat(" FROM {0} ", timeStamp);
DynamicObjectCollection periodColl = DBUtils.ExecuteDynamicObject(ctx, sql3.ToString());
foreach (DynamicObject item in periodColl)
{
basedata = new JObject();
basedata.Add("FSeq", Convert.ToInt64(item["FSeq"]));
basedata.Add("DEPTNAME", Convert.ToInt64(item["DEPTNAME"]));
basedata.Add("SALERNAME", Convert.ToInt64(item["SALERNAME"]));
basedata.Add("TOTALQUOTA", Convert.ToInt64(item["TOTALQUOTA"]));
basedata.Add("TOTALAMOUNT", Convert.ToInt64(item["TOTALAMOUNT"]));
basedata.Add("COMPLETERATE", Convert.ToInt64(item["COMPLETERATE"]));
jsonRoot.Add("Result", basedata);
}
string dropSql2 = string.Format(@"/*dialect*/ drop table {0}", timeStamp);
DBUtils.Execute(ctx, dropSql2);
}
sContent = JsonConvert.SerializeObject(jsonRoot);
return(sContent);
}
