

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ProductSales.aspx.cs" Inherits="Webroot_DPCS_51ditu_ProductSales" %>
<%@ Register Assembly="Myvas.Web.UI.WebControls" Namespace="Myvas.Web.UI.WebControls"
TagPrefix="cc1" %>
<%-- %><%@ Register TagPrefix="cc1" Namespace="Myvas.Web.UI.WebControls" Assembly="Myvas.Web.UI.WebControls" %> --%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>管理体系 - 产品销售</title>
<link rel="stylesheet" type="text/css" href="StyleSheet.css" />
<script language="javascript" src="http://api.51ditu.com/js/maps.js" type="text/jscript"></script>
<script language="javascript" src="http://api.51ditu.com/js/ajax.js" type="text/jscript"></script>
<script language="javascript" src="./js/tools.js" type="text/jscript"></script>
<script language="javascript" src="./js/pub.js" type="text/jscript"></script>
<script language="javascript" src="./js/postdata.js" type="text/jscript"></script>
<script language="javascript" src="./js/marker.js" type="text/jscript"></script>
<script language="javascript" src="./js/getdata.js" type="text/jscript"></script>
<script language="javascript" src="./js/business.js" type="text/jscript"></script>
<script language="javascript" src="./js/sendRequest.js" type="text/jscript"></script>
<script language ="javascript" src="./js/select.js" type="text/javascript"></script>
</head>
<body onload="onLoad()">
<form id="form1" runat="server">
<asp:HiddenField ID="QueryString" runat="server" Value='<%$ Resources:Resource,Query %>'/>
<div>
<div class="headInfo"><div class="MapName"><asp:Label ID="ProductSale_management" runat="server" Text='<%$ Resources:Resource,ProductSale_management %>'/></div><div class="helpTitle" style="width:auto;"><a href="#"><asp:Label ID="help" runat="server" Text='<%$ Resources:Resource,help %>'/></a></div></div>
<div id="toolsDiv" class="Tools" ></div>
<div id="bzDiv" class="selSection">
<div class="rightSelectionHead" ><div class="SelectionTitle"><asp:Label ID="check_condition" runat="server" Text='<%$ Resources:Resource,check_condition %>'/></div></div>
<div class="bzContont" style="left: 2px; width: 194px; top: 0px; height: 520px;"> <br/>
<div class="ptZone" style="margin-left:5px;" >
<div class="ptSelect"><asp:Label ID="MarkerLevel" runat="server" Text='<%$ Resources:Resource,MarkerLevel %>'/>
<asp:DropDownList ID="levelList" runat="server">
<asp:ListItem Selected="True" Text='<%$ Resources:Resource,Region %>'></asp:ListItem>
<asp:ListItem Text='<%$ Resources:Resource,Filiale %>'></asp:ListItem>
<asp:ListItem Text='<%$ Resources:Resource,Agent %>'></asp:ListItem>
<asp:ListItem Text='<%$ Resources:Resource,City %>'></asp:ListItem>
</asp:DropDownList>
<%--<select id="levelList" name="levelList">
<option selected="selected" value="Region"><asp:Label ID="Label1" runat="server" Text='<%$ Resources:Resource,Region %>'/></option>
<option value="Filiale"><asp:Label ID="Label2" runat="server" Text='<%$ Resources:Resource,Filiale %>'/></option>
<option value="Agent"><asp:Label ID="Label3" runat="server" Text='<%$ Resources:Resource,Agent %>'/></option>
<option value="Province"><asp:Label ID="Label4" runat="server" Text='<%$ Resources:Resource,Province %>'/></option>
<option value="City"><asp:Label ID="Label5" runat="server" Text='<%$ Resources:Resource,City %>'/></option>
</select>--%>
</div></div>
<br/>
<span style="margin-left:10px; font-family:黑体; font-size:14px; color:#7B8DBD; margin-top:10px;"><asp:Label ID="Data_timeRange" runat="server" Text='<%$ Resources:Resource,Data_timeRange %>'/></span>
<table style="margin-top:5px; margin-left:20px;"><tr><td>
<div class="dateTitle"><asp:Label ID="Shop_StartTime" runat="server" Text='<%$ Resources:Resource,Shop_StartTime %>'/><cc1:datetimeselector id="sBegin" runat="server" sitepath="http://www.cnblogs.com/DateTimeSelector/"></cc1:datetimeselector></div></td>
</tr>
<tr><td>
<div class="dateTitle"><asp:Label ID="Shop_EndTime" runat="server" Text='<%$ Resources:Resource,Shop_EndTime %>'/><cc1:datetimeselector id="sEnd" runat="server" sitepath="http://www.cnblogs.com/DateTimeSelector/"></cc1:datetimeselector></div></td>
</tr>
</table>
<br />
<span style="margin-left:10px; font-family:黑体; font-size:14px; color:#7B8DBD; margin-top:10px;"><asp:Label ID="Data_range" runat="server" Text='<%$ Resources:Resource,Data_range %>'/></span>
<div class="corpSelect"><asp:Label ID="Shop_RetailerName" runat="server" Text='<%$ Resources:Resource,Shop_RetailerName %>'/> <input id="chainShopInput" name="chainShopInput" size="12" />
</div>
<div class="productCat"> <asp:Label ID="Product_Category" runat="server" Text='<%$ Resources:Resource,Product_Category %>'/>
<asp:DropDownList ID="productCat" runat="server">
</asp:DropDownList></div>
<div class="inputTitle"><asp:Label ID="Product_Sku" runat="server" Text='<%$ Resources:Resource,Product_Sku %>'/><input id="productSKU" name="productSKU" size="12" style="margin-left:18px;"/></div>
<div class="inputTitle"><asp:Label ID="Product_Code" runat="server" Text='<%$ Resources:Resource,Product_Code %>'/><input id="productCoding" name="productCoding" size="12" style="margin-left:13px;" /></div>
<div id="btnQuery" style="margin-top:30px; margin-left:70px;">
<asp:Button ID="btnViewPOI" runat="server" Text='<%$ Resources:Resource,Query %>' OnClientClick="return ReqProductSales()"/></div>
</div>
</div>
<div id="mapDiv" class="map" ></div>
</div>
<div id="markerEdit" style="display:none; margin-top:800px; margin-left:80px;" >
<input type="hidden" id="index" value="0" />
<asp:HiddenField ID="Culture" runat="server" />
</div>
<asp:HiddenField ID="CorpId" runat="server" />
</form>
</body>
</html>
以下为页面绑定代码


using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Myvas.Web.UI.WebControls;
using System.Globalization;
using System.Threading;
public partial class Webroot_DPCS_51ditu_ProductSales : System.Web.UI.Page
{
/// <summary>
/// 根据session设置启用中文版或是英文版线程
/// </summary>
protected override void InitializeCulture()
{
string UserCulture = string.Empty;
if (Session["CultureName"] != null)
{ UserCulture = Session["CultureName"].ToString();
}
if (UserCulture != "")
{
// there is a user language setting in the profile: switch to it
Thread.CurrentThread.CurrentUICulture = new CultureInfo(UserCulture);
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture(UserCulture);
}
}
protected void Page_Load(object sender, EventArgs e)
{
this.sBegin.Text = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
this.sEnd.Text = DateTime.Now.ToString("yyy-MM-dd");
this.CorpId.Value = Session["CorpId"].ToString();
this.Culture.Value = Session["CultureName"].ToString(); //add by LFP 20080928
if (!IsPostBack)
{
//注释 by LFP 20080908 start
//productSeriesBind();
//productCatBind();
//注释 by LFP 20080908 end
productCategoryBind();//add by LFP 20080908
}
}
/// <summary>
/// 系列表变化时,重新绑定数据
/// </summary>
protected void productCategoryBind()
{
// string productSelectValue = this.productSeries.SelectedValue; //注释 by LFP 20080908
string productCatlistSql = "select category from v_map where category !='' and corpid=" + Session["CorpId"].ToString() + " group by category ";
// string productCatlistSql = "select category from v_map where category !='' group by category ";
string sConn = Utils.Instance.GetConnectionString();
SqlConnection Conn = new SqlConnection(sConn);
SqlDataAdapter adapter = new SqlDataAdapter(productCatlistSql, sConn);
Conn.Open();
DataSet ds = new DataSet();
adapter.Fill(ds);
Conn.Close();
productCat.DataSource = ds;
productCat.DataTextField = "category";
productCat.DataValueField = "category";
productCat.DataBind();
//添加字符串"全部"
DPWebUtils.AddOptionToSelect(this.productCat, (string)GetGlobalResourceObject("Resource", "lbSelectAll"), "");
Conn.Close();
}
}
该页面 按某级别(如:大区,省,门店)查询时 的按键js——负责向productsalesHandler.ashx处理


////////////////////////////////////////管理体系 - 产品销售查询请求/////////////////////////////////////////////
////////////////////////
var levelname2=null;
function ReqProductSales()
{var type;
var serials;
//modified by LFP 20080927
if($("productCat").options($("productCat").selectedIndex).innerHTML=="全部"||$("productCat").options($("productCat").selectedIndex).innerHTML=="Select

type="";
else
type =$("productCat").options($("productCat").selectedIndex).value;
//设定请求处理参数 by LFP 20080908
var s = "level="+ $("levelList").options($("levelList").selectedIndex).innerHTML +
"&starttime="+$("sBegin_sBegin").value+
"&endtime="+$("sEnd_sEnd").value+
"&owner=" + $("chainShopInput").value+
"&type=" + type+
// "&serials=" + serials+ //删除 by LFP 20080908
"&sku=" +$("productSKU").value+
"&code=" + $("productCoding").value+
"&corpid="+$("CorpId").value+
"&culture="+$("Culture").value;
//设定请求处理的页面by LFp 20080929
var url = encodeURI("./productsalesHandler.ashx?"+ s);
xhrSendRequest.open("post",url,false);
xhrSendRequest.setrequestheader("content-length",url.length);
xhrSendRequest.setrequestheader("content-type","application/x-www-form-urlencoded");
//判别是否中英版本modified by LFp 20080929
if($("Culture").value=="en-US")
{
xhrSendRequest.onreadystatechange = showProductSalesGeoPointsEn;
levelname2=$("levelList").options($("levelList").selectedIndex).innerHTML;
}
else
xhrSendRequest.onreadystatechange = showProductSalesGeoPoints;
//发送请求by LFp 20080929
xhrSendRequest.send();
return false; //增加js调用的返回为false 以防止页面再刷新by LFp 20080929
}
productsalesHandler.ashx页面处理代码


<%@ WebHandler Language="C#" Class="productsalesHandler" %>
using System;
using System.Web;
using System.Data.Sql;
using System.Data;
public class productsalesHandler : IHttpHandler {
public void ProcessRequest (HttpContext context)
{
//获取jscript所传来的参数信息
context.Response.ContentType = "text/plain";
string regionLevelName = context.Request.QueryString["level"];
string startTime = context.Request.QueryString["starttime"];
string endTime = context.Request.QueryString["endtime"];
string owner = context.Request.QueryString["owner"];
string type = context.Request.QueryString["type"];
//string serials = context.Request.QueryString["serials"];//注释 by LFP 20080908
string sku = context.Request.QueryString["sku"];
string code = context.Request.QueryString["code"];
string corpid = context.Request.QueryString["corpid"];//add by LFP 20080903
string culture = context.Request.QueryString["culture"];//add by LFP 20080929
//请求处理函数处理,并将结果存在result字段
string result = Handler(regionLevelName, startTime, endTime, owner, type, sku, code, corpid, culture);
//返回参数
context.Response.Clear();
context.Response.Write(result);
context.Response.Flush();
context.Response.Close();
}
/// <summary>
/// 根据传入数据访问数据库,获取返回结果集
/// </summary>
/// <param name="regionLevelName">标注点级别</param>
/// <param name="starTime">开始时间</param>
/// <param name="endTime">结束时间</param>
/// <param name="owner">隶属连锁店</param>
/// <param name="type">类别</param>
/// <param name="serials">系列</param>
/// <param name="sku">产品名称</param>
/// <param name="code">产品编码</param>
/// <returns>result返回数据字串</returns>
private string Handler(string regionLevelName, string startTime, string endTime, string owner, string type, string sku, string code,string corpid,string culture)
{
///初始化局部变量 by LFP 20080908
string sql = string.Empty; //select主体句
string groupbySql=string .Empty; //group by 主体句
string ownerSql = string.Empty;
string typeSql = string.Empty;
// string serialsSql = string.Empty;//注释 by LFP 20080908
string skuSql = string.Empty;
string codeSql=string.Empty;
string tablename=string.Empty;//关联的表名
string tablecol=string.Empty ;//关联的列名
string col=string.Empty;//v_map的列名
string tablecolEn = string.Empty;//add by LFP 20080928
///生成查询语句
if (culture == "en-US") //英文版
switch (regionLevelName)
{
case "Region": tablename = " dp_regions ";
tablecol = "regionname";
col = "Region";
tablecolEn = "RegionNameEn";
break;
case "Filiale": tablename = " dp_filiales ";
tablecol = "filialeName";
col = "filialeName";
tablecolEn = "FilialeNameEn";
break;
case "Agent": tablename = " dp_agents ";
tablecol = "agentname";
col = "agentname";
tablecolEn = "agentname";
break;
case "Province": tablename = " dp_cities ";
tablecol = "District";
col = "province";
tablecolEn = "DistrictEn";
break;
case "City": tablename = "dp_cities ";
tablecol = "District";
col = "city";
tablecolEn = "DistrictEn";
break;
default: break;
}
else //中文版
switch(regionLevelName)
{
case "大区":tablename=" dp_regions ";
tablecol="regionname";
col="Region";
tablecolEn = "RegionName";
break;
case "分公司":tablename=" dp_filiales ";
tablecol="filialeName";
col="filialeName";
tablecolEn="filialeName";
break;
case "代理商":tablename=" dp_agents ";
tablecol="agentname";
col="agentname";
tablecolEn="agentname";
break;
case "省":tablename=" dp_cities ";
tablecol="District";
col="province";
tablecolEn = "District";
break;
case "地级市":tablename="dp_cities ";
tablecol="District";
col="city";
tablecolEn = "District";
break;
default:break;
}
///根据传入的参数是否为空,确定添加不同的sql查询条件
string cols = "";
if (owner != string.Empty)
{
ownerSql = " and a.retailername like '%" + owner + "%' ";
cols += " ,a.retailername ";
}
else ownerSql = " ";
if (type != string.Empty)
{
typeSql = " and a.category='" + type + "' ";
cols += ",a.category ";
}
else typeSql = " ";
if (sku != string.Empty)
{
skuSql = " and a.sku like '%" + sku + "%' ";
cols += ",a.sku ";
}
else skuSql = " ";
if (code != string.Empty)
{
codeSql = " and a.code like '%" + code + "%' ";
cols += ",a.code";
}
else codeSql = " ";
///确定主体sql语句
sql = " select distinct b." + tablecolEn + ",b.longitude,b.latitude,sum(a.Distribution) as sumDistrib,convert (numeric(20,2) ,isnull(sum(a.Price*a.Distribution)/sum(a.Distribution),0)) as avgPrice,isnull(sum(a.Price*a.Distribution),0) as sumPrice"
+ " from V_Map a left outer join " + tablename + " b on b." + tablecol + "=a." + col + " "
+ " where convert(varchar(10),a.RecvTime,120) >= '" + startTime + "'"
+ " and convert(varchar(10),a.recvtime,120) < '" + endTime + "'"
+ " and a." + col + " is not null "
+ " and b.longitude is not null "
+ " and b.latitude is not null "
+ " and a.CorpId=" + corpid + " " //add by LFP 20080904
+ ownerSql
//+ serialsSql //注释 by LFP 20080908
+ typeSql
+ skuSql
+ codeSql
+ " group by b." + tablecolEn + " "
+ " ,b.longitude,b.latitude";
///执行查询并返回查询结果
System.Data.DataTable dtResult = Utils.Instance.GetDataFromSqlServerr(sql);
//生成json字串
string result = "[";
foreach (System.Data.DataRow dr in dtResult.Rows)
{
result += "{";
result += string.Format("levelname:'{0}',x:'{1}',y:'{2}',sumdistrib:'{3}',avgPrice:'{4}',sumPrice:'{5}'", dr[tablecolEn].ToString(), dr["longitude"].ToString(), dr["latitude"].ToString(), dr["sumDistrib"].ToString(), dr["avgPrice"].ToString(), dr["sumPrice"].ToString());
result += "},";
}
//去除json最后一个对象"{},"中的','字符
if (result.Length > 1)
{
result = result.Remove(result.Length - 1);
}
result += "]";
//如果结果为空,返回空字符
if (result == "[]")
{
return "";
}
else
{
return result;
}
}
public bool IsReusable
{
get {
return false;
}
}
}
处理后的结果显示:


//在地图上显示从服务端返回的产品销售记录结果
function showProductSalesGeoPoints()
{
/////如果成功返回,则设置地图图标显示模式
if (xhrSendRequest.readyState==4 || xhrSendRequest.readyState=="complete")
{
var points = [];
var items = null;
///试探是否对象成功返回
try
{
//alert(xhrSendRequest.responseText);
items = eval("(" + xhrSendRequest.responseText + ")");
//alert(items);
}
catch(ex)
{
alert("未找到相关数据.");
}
///初始化局部参数
var marker = null;//标注点符号
var point = null;//地理点
var pointName = null;//标注点名称
var sumdistrib = null;//产品销量
var avgPrice = null;//平均价格
var sumPrice = null;//总额
if(!items) return;//对象如果为空,则返回
///开始设置地图图标的显示模式
map.clearOverLays(); //清除图标
//添加标点标签
for (i=0; i<(items.length); i++)
{
var node = items[i];
if(!node) continue;
//生成点集
point = new LTPoint(parseInt(node.x),parseInt(node.y));
points.push(point);
pointName = node.levelname;
sumdistrib = node.sumdistrib;
avgPrice = node.avgPrice;
sumPrice=node.sumPrice;
////////根据级别显示标注点图标////////////////////////////////////////////////////////////////////////////////////////
//设置点的图标
var ptIcon = new LTIcon();
ptIcon.setWidth( 32 );
ptIcon.setHeight( 32 );
//获取所查询的级别名
var leverName= $("levelList").options($("levelList").selectedIndex).innerHTML;
//关联点与图标
marker = new LTMarker(point,ptIcon);
//添加图标右侧的附加显示信息
var text = new LTMapText( marker );
text.setLabel( "<div style=background:#809FFF;><span style='text-decoration: underline;font-size:14px; font-color:#FFFFFF;'><strong>" + pointName+ "</strong></span>" +leverName+"<br/>产品销量:<span style='text-decoration: underline;font-size:12px; font-color:#FFFFFF;'>" + sumdistrib +"台</span><br/>"
+"零销总额:<span style='text-decoration: underline;font-size:12px; font-color:#FFFFFF;'>" + sumPrice +"元</span><br/>平均价格:<span style='text-decoration: underline;font-size:12px; font-color:#FFFFFF;'>" + avgPrice +"元</span><br/></div>");
//将点层及text层加入地图
map.addOverLay(marker);
map.addOverLay( text );
}
var c = new LTSmallMapControl();
map.addControl(c);
map.getBestMap(points);
}
}
//////////////////英文版/////////////////////////////////
//在地图上显示从服务端返回的产品销售记录结果
function showProductSalesGeoPointsEn()
{
/////如果成功返回,则设置地图图标显示模式
if (xhrSendRequest.readyState==4 || xhrSendRequest.readyState=="complete")
{
var points = [];
var items = null;
///试探是否对象成功返回
try
{
//alert(xhrSendRequest.responseText);
items = eval("(" + xhrSendRequest.responseText + ")");
//alert(items);
}
catch(ex)
{
alert("未找到相关数据.");
}
///初始化局部参数
var marker = null;//标注点符号
var point = null;//地理点
var pointName = null;//标注点名称
var sumdistrib = null;//产品销量
var avgPrice = null;//平均价格
var sumPrice = null;//总额
if(!items) return;//对象如果为空,则返回
///开始设置地图图标的显示模式
map.clearOverLays(); //清除图标
//添加标点标签
for (i=0; i<(items.length); i++)
{
var node = items[i];
if(!node) continue;
//生成点集
point = new LTPoint(parseInt(node.x),parseInt(node.y));
points.push(point);
pointName = node.levelname;
sumdistrib = node.sumdistrib;
avgPrice = node.avgPrice;
sumPrice=node.sumPrice;
////////根据级别显示标注点图标////////////////////////////////////////////////////////////////////////////////////////
//设置点的图标
var ptIcon = new LTIcon();
ptIcon.setWidth( 32 );
ptIcon.setHeight( 32 );
//获取所查询的级别名
var leverName= $("levelList").options($("levelList").selectedIndex).innerHTML;
//关联点与图标
marker = new LTMarker(point,ptIcon);
//添加图标右侧的附加显示信息
var text = new LTMapText( marker );
text.setLabel( "<div style=background:#809FFF;><span style='text-decoration: underline;font-size:14px; font-color:#FFFFFF;'><strong>" + pointName+ "</strong></span> " +levelname2+"<br/>Product_Distribution:<span style='text-decoration: underline;font-size:12px; font-color:#FFFFFF;'>" + sumdistrib +"台</span><br/>"
+"Gross Retail Sales:<span style='text-decoration: underline;font-size:12px; font-color:#FFFFFF;'>" + sumPrice +"元</span><br/>Average Price:<span style='text-decoration: underline;font-size:12px; font-color:#FFFFFF;'>" + avgPrice +"元</span><br/></div>");
//将点层及text层加入地图
map.addOverLay(marker);
map.addOverLay( text );
}
var c = new LTSmallMapControl();
map.addControl(c);
map.getBestMap(points);
}
}