public int TCont()
{
string Sql = "select count(id) from m_zyimg";
int Num = DBLL.ExecuteInt(MyGlobal.DataBase, Sql);
return(Num);
}
public List <VisitBill> P_HisVisit(string userName, int curPage, int pageSize)
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);
}
MLogin.GetExeUname();
int num = curPage * pageSize;
string sql = "select top " + pageSize.ToString() + " o.* from ( " +
" select row_number() over(order by id desc) as rownumber,a.* from( " +
" select id, client, billdate, notepre, code from clientservice_VisitBill where billman = '" + userName + "') as a ) as o where rownumber> " + num.ToString();
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
List <VisitBill> history = new List <VisitBill>();
foreach (DataRow c in dt.Rows)
{
VisitBill b = new VisitBill();
b.code = c["CODE"].ToString();
b.client = c["CLIENT"].ToString();
b.date = Convert.ToDateTime(c["BILLDATE"].ToString()).ToString("yyyy-MM-dd");
b.content = c["NOTEPRE"].ToString();
history.Add(b);
}
return(history);
}
public List <PushPoi> AuditPoi(string userName, int curPage, int pageSize)
{
//获得bill
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);//身份验证失败
}
MLogin.GetExeUname();
int num = curPage * pageSize;
string where = "";
string sql = "";
if (userName == "郑昌仁")
{
where = " and zf1='总经办' ";
}
else if (userName == "王娅丽")
{
where = " and zf1='销售内勤' ";
}
else if (userName == "邵凯丽")
{
}
else
{
return(null);
}
sql = "select top " + pageSize.ToString() + " o.* from ( " +
" select row_number() over(order by id desc) as rownumber,a.* from( " +
" select * from L_PushPoi where 1=1 and OPERATE_TYPE='已提交' " + where + ") as a ) as o where rownumber> " + num.ToString();
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
List <PushPoi> result = new List <PushPoi>();
foreach (DataRow row in dt.Rows)
{
PushPoi p = new PushPoi();
p.ID = row["ID"].ToString();
p.BILLMAN = row["BILLMAN"].ToString();
p.OPERATE_TIME = row["OPERATE_TIME"].ToString();
p.OPERATE_TYPE = row["OPERATE_TYPE"].ToString();
p.CLIENT = row["CLIENT"].ToString();
p.BEFORE_LNG = row["BEFORE_LNG"].ToString(); //原始定位
p.BEFORE_LAT = row["BEFORE_LAT"].ToString();
p.AFTER_LNG = row["AFTER_LNG"].ToString(); //经度//现在请求定位
p.AFTER_LAT = row["AFTER_LAT"].ToString(); //纬度
double dis = ScueFun.LngLatDis.GetDistance(Convert.ToDouble(row["BEFORE_LNG"].ToString()),
Convert.ToDouble(row["BEFORE_LAT"].ToString()),
Convert.ToDouble(row["AFTER_LNG"].ToString()),
Convert.ToDouble(row["AFTER_LAT"].ToString()));
p.DISTANCE = Math.Round(dis / 1000, 2).ToString() + "km";
result.Add(p);
}
return(result);
}
public kfContact GetContact(string client)
{
kfContact contact = new kfContact();
List <Contact> result = new List <Contact>();
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);
}
MLogin.GetExeUname();
string kf_code = string.Empty;
string tablename = string.Empty;
#region
//string kf_tb = string.Empty;
//kfku m = new kfku();
//List<kfku> ms = m.Select(" and NAME='" + client + "' order by id desc ");
//if (ms.Count > 0)
//{
// kf_code = ms[0].CODE;
// //kf_tb = "kfku";
// tablename = "kfku_contacter";
//}
//else
//{
#endregion
ClientService_kfku k = new ClientService_kfku();
List <ClientService_kfku> ks = k.Select(" and NAME='" + client + "' order by id desc ");
if (ks.Count > 0)
{
kf_code = ks[0].CODE;
tablename = "clientservice_kfku_contacter";
}
if (kf_code == "" || tablename == "")//
{
return(null);
}
else
{
string sql = " select * from " + tablename + " where 单位代码=" + kf_code.ToString();
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
foreach (DataRow row in dt.Rows)
{
Contact con = new Contact();
con.id = row["ID"].ToString();
con.name = row["姓名"].ToString();
con.phone = row["手机"].ToString();
con.position = row["职务"].ToString();
result.Add(con);
}
contact.list = result;
contact.tablename = tablename;
contact.kfcode = kf_code;
}
return(contact);
}
public List <WXOBJ1> AfterSRepair_P(string userName, int type, int curPage, int pageSize)
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);//身份验证失败
}
MLogin.GetExeUname();
int num = curPage * pageSize;
string where = " and BILLMAN='" + userName + "' and zf3 <> '' and left(code,1) <> '*' ";
if (type == 0)
{
where += " and ((zf2='' and sz10=0 ) or (zf2<>'" + userName + "')) ";//未接收
}
else if (type == 1)
{
where += " and zf2='" + userName + "' and id in( select zf1 from L_AfterRepair a where not exists(select 1 from L_AfterRepair b where b.refercode = a.refercode and b.id > a.id) and OPERATE_TYPE = '接受' and BILLMAN ='" + userName + "' ) ";//处理中
}
else if (type == 2)
{
where += " and zf2='" + userName + "' and id in( select zf1 from L_AfterRepair a where not exists(select 1 from L_AfterRepair b where b.refercode = a.refercode and b.id > a.id) and OPERATE_TYPE = '完结' and BILLMAN ='" + userName + "' ) ";//维修完成
}
else
{
}
List <WXOBJ1> ms = new List <WXOBJ1>();
string sql = " select top " + pageSize.ToString() + " o.* from ( " +
" select row_number() over(order by id desc) as rownumber, N_A.* from( " +
" select * from clientservice_AfterServiceBill " +
" where 1 = 1 " + where +
" ) as N_A) as o where rownumber> " + num.ToString();
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
foreach (DataRow row in dt.Rows)
{
WXOBJ1 o = new WXOBJ1();
o.ID = Convert.ToInt32(row["ID"].ToString());
o.CODE = row["CODE"].ToString();//维修单号
o.BILLMAN = row["BILLMAN"].ToString();
o.CLIENT = row["CLIENT"].ToString();
o.BILLTYPE = row["BILLTYPE"].ToString();
o.ZF1 = row["ZF1"].ToString(); //机床编号
o.NOTEPRE = row["NOTEPRE"].ToString(); //内容描述（电话回访）
o.ZF3 = row["ZF3"].ToString(); //分析描述 初判断
o.CONTACT = row["ZF27"].ToString();
o.PHONE = row["ZF28"].ToString();
o.ZF5 = row["ZF5"].ToString();
ms.Add(o);
}
return(ms);
}
public List <FeeBill> MyFeeBills(string userName, int curPage, int pageSize)
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);//身份验证失败
}
MLogin.GetExeUname();
// P_MyBill result = new P_MyBill();
int num = curPage * pageSize;
// curPage++;
string sql = " select top 10 o.* from( " +
" select row_number() over(order by id desc) as rownumber, a.* from( " +
" select * from TreatFee where BILLMAN = '" + userName + "' ) as a) as o where rownumber > " + num.ToString();
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
List <FeeBill> bills = new List <FeeBill>();
foreach (DataRow row in dt.Rows)
{
FeeBill bill = new FeeBill();
bill.billcode = row["BILLCODE"].ToString();
bill.billman = row["BILLMAN"].ToString();
bill.createtime = row["CREATETIME"].ToString();
bill.state = Convert.ToInt16(row["STATE"].ToString());
if (bill.state == 0)
{
bill.state_desc = "未审核";
}
else if (bill.state == 10)
{
bill.state_desc = "审核通过";
}
else if (bill.state == -1)
{
bill.state_desc = "审核失败";
}
bill.vbcode = row["VBCODE"].ToString();
bill.client = row["CLIENT"].ToString();
bill.eating = row["EATING"].ToString();
bill.other = row["OTHER"].ToString();
bill.person = row["PERSON"].ToString();
bill.traffic = row["TRAFFIC"].ToString();
bill.commission = row["COMMISSION"].ToString();
bill.remark = row["BLANK_STR_1"].ToString();
bill.opinion = row["BLANK_STR_2"].ToString();
bills.Add(bill);
}
// result.list = bills;
// result.page = curPage;
return(bills);
}
public List <LLBill> GetDBBill(string client, string userName, string dept)//获取待办事项
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);//身份验证失败
}
MLogin.GetExeUname();
List <LLBill> result = new List <LLBill>();
string where = "";
if (dept.Contains("维修"))
{
where = " and (depar = '" + userName + "' or depar = '维修部' or depar = '智能化' )";
}
if (dept.Contains("业务") || dept.Contains("销售") || dept.Contains("文员"))
{
where = " and (depar = '" + userName + "' or depar = '销售部' or depar = '智能化' )";
}
string sql = " select top 15 * from " +
" (select * from L_ContactSheet as aa " +
" left join " +
" (select name, ZF29, ZF30,CONTACTOR,PHONE from clientservice_kfku ) as bb on aa.Client = bb.NAME " +
" ) as cc" +
" where type=0 and del =0 and Client='" + client + "' " + where + " order by finshdate ";
DataTable tb = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
foreach (DataRow row in tb.Rows)
{
LLBill n = new LLBill();
n.id = row["ID"].ToString(); //客户
n.client = row["CLIENT"].ToString(); //客户
n.code = row["CODE"].ToString(); //单号
n.depar = row["DEPAR"].ToString(); //部门
n.finshdate = Convert.ToDateTime(row["FINSHDATE"].ToString()).ToString("yyyy-MM-dd"); //要求完成时间
n.id = row["ID"].ToString();
n.inserter = row["INSERTER"].ToString(); //提出人
n.machinecn = row["MACHINECN"].ToString(); //机床编号
n.mtitle = row["MTITLE"].ToString(); //内容
n.type = row["TYPE"].ToString(); //状态
//n.distance = row["DISTANCE"].ToString()+"km"; //距离
n.contactor = row["CONTACTOR"].ToString(); //联系人
n.phone = row["PHONE"].ToString(); //电话
n.lng = row["ZF29"].ToString(); //经度
n.lat = row["ZF30"].ToString(); //纬度
result.Add(n);
}
return(result);
}
/// <summary>
/// 附近的打卡点
/// </summary>
/// <param name="longitude"></param>
/// <param name="latitude"></param>
/// <param name="where"></param>
/// <param name="userName"></param>
/// <param name="type"></param>
/// <param name="radius"></param>
/// <returns></returns>
public static DataTable NeighbourCpn(double longitude, double latitude)
{
//先计算查询点的经纬度范围
double r = 6371;//地球半径千米
double dis = MyGlobal.AREA_COMPANY;
double dlng = 2 * Math.Asin(Math.Sin(dis / (2 * r)) / Math.Cos(latitude * Math.PI / 180));
dlng = dlng * 180 / Math.PI;//角度转为弧度
double dlat = dis / r;
dlat = dlat * 180 / Math.PI;
double minlat = latitude - dlat;
double maxlat = latitude + dlat;
double minlng = longitude - dlng;
double maxlng = longitude + dlng;
string sql = string.Empty;
string typename = string.Empty;
sql = " select * " +
" from sanqi_kfku where zf29 between '" + minlng.ToString() + "' and '" + maxlng.ToString() + "' " +
" and zf30 between '" + minlat.ToString() + "' and '" + maxlat.ToString() + "' ";
DataTable tb = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
int len = tb.Rows.Count;
tb.Columns.Add("DISTANCE", typeof(Double));
for (int m = 0; m < len; m++)
{
double distance = ScueFun.LngLatDis.GetDistance(longitude, latitude, Convert.ToDouble(tb.Rows[m]["ZF29"].ToString()), Convert.ToDouble(tb.Rows[m]["ZF30"].ToString()));
tb.Rows[m]["DISTANCE"] = distance;
}
int i = 0, j = 0;
for (i = 0; i < len; i++)
{
for (j = 0; j < len - i - 1; j++)
{
double disj = Convert.ToDouble(tb.Rows[j]["DISTANCE"].ToString());
double disj_1 = Convert.ToDouble(tb.Rows[j + 1]["DISTANCE"].ToString());
if (disj > disj_1)// 前 > 后 则交换，即从小到大排序
{
DataRow dr = tb.NewRow();
dr.ItemArray = tb.Rows[j].ItemArray;//暂存
tb.Rows[j].ItemArray = tb.Rows[j + 1].ItemArray;
tb.Rows[j + 1].ItemArray = dr.ItemArray;
}
}
}
return(tb);
}
/// <summary>
/// 获取客户code，Code= CalKFcode()
/// </summary>
/// <returns></returns>
public static string CalKFcode()
{
string maxV = string.Empty;
string sql = "select code from ClientService_kfku where id = " +
" (select max(id) from ClientService_kfku where code <> '' ) ";
DataTable tb = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
if (tb.Rows.Count <= 0)
{
maxV = "1";
}
else
{
maxV = (Convert.ToInt32(tb.Rows[0]["code"].ToString()) + 1).ToString();
}
return(maxV);
}
public static void Set(string userName, string name, string position)
{
//ClientService_kfku k = new ClientService_kfku();
string[] po = position.Split(',');
//k.ZF29 = po[0];//经度
//k.ZF30 = po[1];//纬度
//k.ZF28 = userName;
//k.Updata(" and name='" + name + "' ");
string sql_测试库 = " update makelanaln.dbo.clientservice_kfku set zf28 ='" + userName + "', " +
" zf29 = '" + po[0] + "', zf30 = '" + po[1] + "' where makelanaln.dbo.clientservice_kfku.NAME ='" + name + "' ";
DBLL.ExecuteNonQuery(MyGlobal.DataBase, sql_测试库);
string sql = " update sysanqi.dbo.clientservice_kfku set zf28 ='" + userName + "', " +
" zf29 = '" + po[0] + "', zf30 = '" + po[1] + "' where sysanqi.dbo.clientservice_kfku.NAME ='" + name + "' ";
DBLL.ExecuteNonQuery(MyGlobal.DataBase, sql);
}
/// <summary>
/// 附近的待办事项，距离为：米
/// </summary>
/// <param name="longitude"></param>
/// <param name="latitude"></param>
/// <param name="where"></param>
/// <param name="userName"></param>
/// <param name="type"></param>
/// <param name="radius"></param>
/// <returns></returns>
public static DataTable NeighbourBill(int curPage, int pageSize, double longitude, double latitude)
{
//先计算查询点的经纬度范围
double r = 6371;//地球半径千米
double dis = MyGlobal.DIS;
double dlng = 2 * Math.Asin(Math.Sin(dis / (2 * r)) / Math.Cos(latitude * Math.PI / 180));
dlng = dlng * 180 / Math.PI;//角度转为弧度
double dlat = dis / r;
dlat = dlat * 180 / Math.PI;
double minlat = latitude - dlat;
double maxlat = latitude + dlat;
double minlng = longitude - dlng;
double maxlng = longitude + dlng;
string sql = string.Empty;
string typename = string.Empty;
int num = curPage * pageSize;
sql = " select top " + pageSize.ToString() + " * from ( " +
" select row_number() over(order by finshdate) as rownumber, a.* from( " +
" select * from " +
" (select * from L_ContactSheet as aa " +
" left join " +
" (select name, ZF29, ZF30, CONTACTOR, PHONE from clientservice_kfku ) as bb on aa.Client = bb.NAME " +
" ) as cc " +
" where type=0 and del =0 " +
" and zf29 between '" + minlng.ToString() + "' and '" + maxlng.ToString() + "' " +
" and zf30 between '" + minlat.ToString() + "' and '" + maxlat.ToString() + "' " +
" ) as a) as o where rownumber > " + num.ToString();
//sql = " select * from " +
// " (select * from L_ContactSheet as aa " +
// " left join " +
// " (select name, ZF29, ZF30,CONTACTOR,PHONE from clientservice_kfku ) as bb on aa.Client = bb.NAME " +
// " ) as cc" +
// " where type=0 and zf29 between '" + minlng.ToString() + "' and '" + maxlng.ToString() + "' " +
// " and zf30 between '" + minlat.ToString() + "' and '" + maxlat.ToString() + "' order by finshdate ";
DataTable tb = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
int len = tb.Rows.Count;
tb.Columns.Add("DISTANCE", typeof(Double));
for (int m = 0; m < len; m++)
{
double distance = ScueFun.LngLatDis.GetDistance(longitude, latitude, Convert.ToDouble(tb.Rows[m]["ZF29"].ToString()), Convert.ToDouble(tb.Rows[m]["ZF30"].ToString()));
tb.Rows[m]["DISTANCE"] = Math.Round(distance / 1000, 2);
}
int i = 0, j = 0;
for (i = 0; i < len; i++)
{
for (j = 0; j < len - i - 1; j++)
{
double disj = Convert.ToDouble(tb.Rows[j]["DISTANCE"].ToString());
double disj_1 = Convert.ToDouble(tb.Rows[j + 1]["DISTANCE"].ToString());
if (disj > disj_1)// 前 > 后 则交换，即从小到大排序
{
DataRow dr = tb.NewRow();
dr.ItemArray = tb.Rows[j].ItemArray;//暂存
tb.Rows[j].ItemArray = tb.Rows[j + 1].ItemArray;
tb.Rows[j + 1].ItemArray = dr.ItemArray;
}
}
}
return(tb);
}
/// <summary>
///
/// 附近客户(全局变量范围内可见) | 最近客户（radius半径缩小即可）
/// type 1:访问时间越久远的越靠前 搜索范围由全局变量DIS而定
/// 2:距离越近越靠前 默认搜索范围由全局变量RADIUS而定，可调整radius
/// 默认为 1
/// </summary>
/// <param name="longitude">当前位置 经度</param>
/// <param name="latitude">当前位置 纬度</param>
/// <param name="where">选择类型</param>
/// <param name="type">type 排序类型默认是第一种</param>
/// <param name="radius">搜索范围 单位：千米（大于0），默认就是全局变量,</param>
/// <returns></returns>
public static DataTable NeighbourPosition(string dept, double longitude, double latitude, string where, string userName, int type = 1, double radius = -1)
{
//先计算查询点的经纬度范围
double r = 6371;//地球半径千米
double dis;
if (type == 1)
{
dis = MyGlobal.DIS;
}
else
{
if (radius <= 0)
{
dis = MyGlobal.RADIUS;
}
else
{
dis = radius;
}
}
double dlng = 2 * Math.Asin(Math.Sin(dis / (2 * r)) / Math.Cos(latitude * Math.PI / 180));
dlng = dlng * 180 / Math.PI;//角度转为弧度
double dlat = dis / r;
dlat = dlat * 180 / Math.PI;
double minlat = latitude - dlat;
double maxlat = latitude + dlat;
double minlng = longitude - dlng;
double maxlng = longitude + dlng;
string sql = string.Empty;
string typename = string.Empty;
if (where == "in")//公司客户
{
typename = " and TRADETYPENAME='公司客户' ";
}
else if (where == "out")//潜在客户-不在kf表
{
typename = " and TRADETYPENAME <> '公司客户' ";
}
else
{
typename = " ";
}
sql = " select top " + MyGlobal.TOPCOUNT + " name,contactor,phone,zf29,zf30,TRADETYPENAME,introducerDate,BillMan,NotePre from ( " +
" select cc.*,dd.introducerDate,dd.BillMan,dd.NotePre,LEV-DATEDIFF(DAY, dd.introducerDate,GETDATE()) AS REDUCE from( " +
" select name,contactor,phone,zf29,zf30,TRADETYPENAME, " +
" CASE LEV WHEN '1级' THEN 150 WHEN '2级' THEN 100 WHEN '3级' THEN 60 WHEN '4级' THEN 30 WHEN '5级' THEN 7 ELSE '' END LEV " +
" from ClientService_kfku where zf29 between '" + minlng.ToString() + "' and '" + maxlng.ToString() + "' " +
" and zf30 between '" + minlat.ToString() + "' and '" + maxlat.ToString() + "' " +
" and LEV <> '' " + typename +
" group by name,contactor,phone,zf29,zf30,TRADETYPENAME,LEV " +
" ) as cc " +
" left join" +
" (select * from clientservice_VisitBill a where not exists(select 1 from clientservice_VisitBill b where b.Client = a.Client and b.id > a.id) )" +
" as dd" +
" on dd.Client = cc.NAME " +
" )as ff where REDUCE<0 order by REDUCE,lev asc ";
//if (type == 1)
//{
// sql += " order by introducerDate ";//相隔时间久的排名靠前
//}
DataTable tb = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
string task_Sql = "";
if (dept == "维修员")
{
task_Sql = " select client from clientservice_AfterServiceBill " +
" where BILLMAN = '" + userName + "' and zf3<> '' and left(code,1) <> '*' " +
" and((zf2 = '' and sz10 = 0) or(zf2 <> '" + userName + "')) or " +
" (zf2 = '" + userName + "' and id in (select zf1 from L_AfterRepair a " +
" where not exists(select 1 from L_AfterRepair b where b.refercode = a.refercode and b.id > a.id) " +
" and OPERATE_TYPE = '接受' and BILLMAN = '" + userName + "' )) ";
}
else
{
task_Sql = " select VisitClient from L_VisitSend WHERE State != 10 AND SendUser = '******' ";
}
string sql_send = " select top 15 cc.*,dd.introducerDate,dd.BillMan,dd.NotePre from( " +
" select name, contactor, phone, zf29, zf30, TRADETYPENAME" +
" from ClientService_kfku where NAME in ( " + task_Sql + " ) " +
" and zf29 between '" + minlng.ToString() + "' and '" + maxlng.ToString() + "' " +
" and zf30 between '" + minlat.ToString() + "' and '" + maxlat.ToString() + "' " + typename +
" group by name,contactor,phone,zf29,zf30,TRADETYPENAME" +
" ) as cc" +
" left join" +
" (select * from clientservice_VisitBill a where not exists(select 1 from clientservice_VisitBill b where b.Client = a.Client and b.id > a.id) ) " +
" as dd" +
" on dd.Client = cc.NAME order by RQ1 asc ";
DataTable tb2 = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql_send);
tb.Merge(tb2);
////多个表合并可能产生重复数据，过滤掉重复数据
DataView dv = new DataView(tb);
tb = dv.ToTable(true, new[] { "NAME", "CONTACTOR", "PHONE", "ZF29", "ZF30", "TRADETYPENAME", "INTRODUCERDATE", "BILLMAN", "NOTEPRE" });
if (type == 2)
{
int len = tb.Rows.Count;
int i = 0, j = 0;
for (i = 0; i < len; i++)
{
for (j = 0; j < len - i - 1; j++)
{
double disj = ScueFun.LngLatDis.GetDistance(longitude, latitude, Convert.ToDouble(tb.Rows[j]["ZF29"].ToString()), Convert.ToDouble(tb.Rows[j]["ZF30"].ToString()));
double disj_1 = ScueFun.LngLatDis.GetDistance(longitude, latitude, Convert.ToDouble(tb.Rows[j + 1]["ZF29"].ToString()), Convert.ToDouble(tb.Rows[j + 1]["ZF30"].ToString()));
if (disj > disj_1)
{
DataRow dr = tb.NewRow();
dr.ItemArray = tb.Rows[j].ItemArray;//暂存
tb.Rows[j].ItemArray = tb.Rows[j + 1].ItemArray;
tb.Rows[j + 1].ItemArray = dr.ItemArray;
}
}
}
}
return(tb);
}
public string PoiApply(string userName, string name, string position)
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return("身份验证失败,请重新登录系统！");//身份验证失败
}
MLogin.GetExeUname();
ClientService_kfku k = new ClientService_kfku();
List <ClientService_kfku> ts = k.Select(" and name='" + name.Trim() + "' and zf29<>'' and zf30<>'' ");
if (ts.Count > 0)
{
L_PushPoi p = new L_PushPoi();
string sql = " select * from L_PushPoi a where not exists( " +
" select 1 from L_PushPoi b where b.client = a.client and b.id > a.id) " +
" and client='" + name.Trim() + "' ";
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
if (dt != null && dt.Rows.Count > 0)
{
string 当前单据状态 = dt.Rows[0]["OPERATE_TYPE"].ToString();
string 当前审核人 = dt.Rows[0]["ZF1"].ToString();
if (当前单据状态 != "已提交")//最新申请已被查看
{
p.ZF1 = "总经办";
p.BILLMAN = userName;
p.OPERATE_TIME = SysTime.GetTime.ToString();
p.OPERATE_TYPE = "已提交";
p.CLIENT = name;
p.BEFORE_LNG = ts[0].ZF29;//原始定位
p.BEFORE_LAT = ts[0].ZF30;
string[] po = position.Split(',');
p.AFTER_LNG = po[0]; //经度//现在请求定位
p.AFTER_LAT = po[1]; //纬度
p.Insert();
return("已提交审核至总经办！"); //已提交总经办审核
}
else//最新记录还在审核状态
{
return("定位还在审核，请勿重复提交！");//
}
}
else
{
p.ZF1 = "销售内勤";
p.BILLMAN = userName;
p.OPERATE_TIME = SysTime.GetTime.ToString();
p.OPERATE_TYPE = "已提交";
p.CLIENT = name;
p.BEFORE_LNG = ts[0].ZF29;//原始定位
p.BEFORE_LAT = ts[0].ZF30;
string[] po = position.Split(',');
p.AFTER_LNG = po[0]; //经度//现在请求定位
p.AFTER_LAT = po[1]; //纬度
p.Insert();
return("已提交审核至销售内勤！");
}
}
else
{
return("该客户尚未定位，无需申请修正！");//没有该用户
}
}
public int PushPoi(string userName, string name, string position) //修改为name 20180728 skl
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(-10);//身份验证失败
}
MLogin.GetExeUname();
ClientService_kfku k = new ClientService_kfku();
List <ClientService_kfku> ts = k.Select(" and name='" + name.Trim() + "' ");
if (ts.Count > 0)
{
//注释 2018 10 12 原始定位需提交审核单。
if (ts[0].ZF29 != null && ts[0].ZF29.Trim() != "")//当前客户有定位
{
return(-1);
}
else
{
try
{
//事务处理开始,劫持链接通道的sql语句
TransactionSql.Start(MyGlobal.DataBase); //===>开始
ClientService_kfku ke = new ClientService_kfku();
string[] po = position.Split(',');
ke.ZF29 = po[0]; //经度
ke.ZF30 = po[1]; //纬度
ke.ZF28 = userName;
ke.Updata(" and name='" + name + "' ");
// string sql_测试库 = " update makelanaln.dbo.clientservice_kfku set zf28 ='" + userName + "', " +
//" zf29 = '" + po[0] + "', zf30 = '" + po[1] + "' where makelanaln.dbo.clientservice_kfku.NAME ='" + name + "' ";
// DBLL.ExecuteNonQuery(MyGlobal.DataBase, sql_测试库);
string sql_正式库 = " update sysanqi.dbo.clientservice_kfku set zf28 ='" + userName + "', " +
" zf29 = '" + po[0] + "', zf30 = '" + po[1] + "' where sysanqi.dbo.clientservice_kfku.NAME ='" + name + "' ";
DBLL.ExecuteNonQuery(MyGlobal.DataBase, sql_正式库);
//提交事务到sql服务器处理
if (!TransactionSql.EndSql())
{
return(-3); //回滚触发
} //===>结束
else
{
return(0);
}
}
catch (Exception ex)
{
return(-4);//程序出现错误
}
}
}
else
{
return(-2);
}
}
public List <VSBill> YWBillGet_P(string userName, int index, int curPage, int pageSize)
{
string cookies = HttpContext.Current.Request.Cookies["guid"] == null ? "" : HttpContext.Current.Request.Cookies["guid"].Value as string;
string session = Session.SessionID.ToString();
if (!cookies.Equals(session))
{
return(null);//身份验证失败
}
MLogin.GetExeUname();
int num = curPage * pageSize;
List <VSBill> returnlist = new List <VSBill>();
//未接收消息
string ORDER = " order by id ";
string WHERE = string.Empty;
if (index == 0)//未接收
{
WHERE = " and state=0 and visittype='上门拜访' ";
}
else if (index == 1)//处理中
{
WHERE = " and state=7 and visittype='上门拜访' ";
}
else//完成
{
WHERE = " and state=10 and visittype='上门拜访' ";
ORDER = " order by id desc ";
}
string today = System.DateTime.Now.ToString("yyyy-MM-dd 23:59:59");
string sql = " select top " + pageSize.ToString() + " o.* from ( " +
" select row_number() over(" + ORDER + ") as rownumber, N_A.* from( " +
" select * from L_VisitSend " +
" where 1 = 1 and SENDUSER='******' and LEFT(CODE,1)<>'*' and VISITDATE < '" + today + "' " + WHERE +
" ) as N_A) as o where rownumber> " + num.ToString();
DataTable dt = DBLL.ExecuteDataTable(MyGlobal.DataBase, sql);
// List<L_VisitSend> list = s.Select(" and SENDUSER='******' and LEFT(CODE,1)<>'*' and VISITDATE < '" + today + "' " + where, " top 20 * ");
if (dt == null || dt.Rows.Count <= 0)
{
return(null);//没有消息
}
else
{
foreach (DataRow row in dt.Rows)
{
VSBill vs = new VSBill();
vs.id = Convert.ToInt32(row["ID"].ToString());
vs.client = row["VISITCLIENT"].ToString();
vs.hisname = row["VISITNAME"].ToString();
vs.hisphone = row["VISITTEL"].ToString();
vs.content = row["VISITCONTENT"].ToString();
vs.notice = row["DOTHING"].ToString();
vs.visittype = row["VISITTYPE"].ToString();
vs.date = row["VISITDATE"].ToString();
vs.code = row["CODE"].ToString();
vs.state = Convert.ToInt32(row["STATE"].ToString());
vs.rq1 = row["RQ1"].ToString();
vs.rq2 = row["RQ2"].ToString();
vs.zf5 = row["ZF5"].ToString();
returnlist.Add(vs);
}
}
return(returnlist);
}