using System;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using SuperMap.RealEstate.CoreFrameWork;
using SuperMap.RealEstate.CoreFrameWork.Dictionary.Business;
using SuperMap.RealEstate.Web.UI.WebControls;
using EDB = SuperMap.RealEstate.ExchangeData.Business;
using HWSDB = SuperMap.RealEstate.HighWay.SellData.Business;
using Business = SuperMap.RealEstate.HighWay.Storage.Business;
using HZQR.Common;
namespace SuperMap.RealEstate.HighWay.Modules.SellData
{
public partial class SaleDataCompare : BasePage
{
#region 方法 -> 页面加载
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack) return;
InitControls();
//设置回车焦点按钮
SetControlClientAction(ButtonStatistics, false);
SetControlClientAction(ButtonSearch);
}
#endregion
#region 方法 -> 初始化页面控件
private void InitControls()
{
PROVINCE_CODE.Items.Clear();
ListItemEx _ListItemEx = new ListItemEx();
_ListItemEx.Text = "所有省份";
_ListItemEx.Value = "1=1";
PROVINCE_CODE.Items.Add(_ListItemEx);
FieldExplain _FieldExplain = DictionaryHelper.GetFieldExplain("DIVISION_CODE", Transaction);
foreach (FieldEnum _FieldEnum in new FieldEnum(Transaction).FillCollection("WHERE FIELDEXPLAIN_ID = " +
_FieldExplain.FieldExplain_ID + " AND FIELDENUM_STATUS = 1 AND FIELDENUM_PID = -1 ORDER BY FIELDENUM_INDEX"))
{
_ListItemEx = new ListItemEx();
_ListItemEx.Text = _FieldEnum.FieldEnum_Name;
_ListItemEx.Value = _FieldEnum.KeyID;
_ListItemEx.Level = 1;
PROVINCE_CODE.Items.Add(_ListItemEx);
}
PROVINCE_CODE.SelectedValue = "1=1";
SERVERPART_ID.Items.Clear();
_ListItemEx = new ListItemEx();
_ListItemEx.Text = "所有服务区";
_ListItemEx.Value = "1=1";
SERVERPART_ID.Items.Add(_ListItemEx);
SERVERPART_ID.SelectedValue = "1=1";
SERVERPARTSHOP_ID.Items.Clear();
_ListItemEx = new ListItemEx();
_ListItemEx.Text = "所有门店";
_ListItemEx.Value = "1=1";
SERVERPARTSHOP_ID.Items.Add(_ListItemEx);
SERVERPARTSHOP_ID.SelectedValue = "1=1";
StatisticsDay.Text = DateTime.Now.Date.AddDays(-1).ToString();
}
#endregion
#region 方法 -> 绑定下拉框门店
protected void PROVINCE_CODE_CallBackSetControl(object sender, ClientSetEventArgs e)
{
SERVERPART_ID.Items.Clear();
ListItemEx _ListItemEx = new ListItemEx();
_ListItemEx.Text = "所有服务区";
_ListItemEx.Value = "1=1";
SERVERPART_ID.Items.Add(_ListItemEx);
if (!string.IsNullOrWhiteSpace(e.Argument) && e.Argument != "1=1")
{
foreach (Business.SERVERPART _SERVERPART in new Business.SERVERPART(Transaction).FillCollection(
"WHERE STATISTICS_TYPE IN (1000) AND PROVINCE_CODE = " + e.Argument +
" ORDER BY SPREGIONTYPE_INDEX,SPREGIONTYPE_ID,SERVERPART_INDEX,SERVERPART_CODE"))
{
_ListItemEx = new ListItemEx();
_ListItemEx.Value = _SERVERPART.KeyID;
_ListItemEx.Text = _SERVERPART.SERVERPART_NAME;
_ListItemEx.Level = 1;
SERVERPART_ID.Items.Add(_ListItemEx);
}
}
SERVERPART_ID.SelectedValue = "1=1";
SERVERPARTSHOP_ID.Items.Clear();
_ListItemEx = new ListItemEx();
_ListItemEx.Text = "所有门店";
_ListItemEx.Value = "1=1";
SERVERPARTSHOP_ID.Items.Add(_ListItemEx);
SERVERPARTSHOP_ID.SelectedValue = "1=1";
e.SetDropDownListEx(SERVERPART_ID);
e.SetDropDownListEx(SERVERPARTSHOP_ID);
}
protected void SERVERPART_ID_CallBackSetControl(object sender, ClientSetEventArgs e)
{
SERVERPARTSHOP_ID.Items.Clear();
ListItemEx _ListItemEx = new ListItemEx();
_ListItemEx.Text = "所有门店";
_ListItemEx.Value = "1=1";
SERVERPARTSHOP_ID.Items.Add(_ListItemEx);
//绑定对应的商家统计
if (!string.IsNullOrEmpty(e.Argument) && e.Argument != "1=1")
{
foreach (Business.SERVERPARTSHOP _SERVERPARTSHOP in new Business.SERVERPARTSHOP(Transaction).FillCollection(
"WHERE ISVALID = 1 AND SHOPTRADE NOT IN ('9032','9999') AND SERVERPART_ID = " + SERVERPART_ID.SelectedValue +
" AND SHOPCODE IS NOT NULL AND NVL(STATISTIC_TYPE,1000) IN (1000,2000) ORDER BY SHOPREGION,SHOPTRADE,SHOPCODE"))
{
_ListItemEx = new ListItemEx();
_ListItemEx.Text = _SERVERPARTSHOP.SHOPNAME;
_ListItemEx.Value = _SERVERPARTSHOP.SHOPCODE;
_ListItemEx.Level = 1;
SERVERPARTSHOP_ID.Items.Add(_ListItemEx);
}
}
e.SetDropDownListEx(SERVERPARTSHOP_ID);
}
#endregion
#region 方法 -> 查询
protected void ButtonStatistics_Click(object sender, EventArgs e)
{
LoadData(null);
#region 表头信息
GridViewEx1.HeaderRow.Cells[0].ColumnSpan = 4;
GridViewEx1.HeaderRow.Cells[0].HorizontalAlign = HorizontalAlign.Center;
GridViewEx1.HeaderRow.Cells[0].Font.Bold = true;
string TmpTd = "服务区信息" +
"| 对客营收 | " +
"收银系统 | ";
TmpTd += "序号 | " +
"服务区 | " +
"门店名称 | " +
"机器编号 | ";
TmpTd += "销售流水 | " +
"自然日报表 | " +
"差异金额 | ";
TmpTd += "机器版本 | " +
"心跳时间 | ";
GridViewEx1.HeaderRow.Cells[0].Text = TmpTd;
for (int HeaderRowCount = 1; HeaderRowCount < GridViewEx1.HeaderRow.Cells.Count; HeaderRowCount++)
{
GridViewEx1.HeaderRow.Cells[HeaderRowCount].Visible = false;
}
GridViewEx1.Visible = true;
#endregion
//绑定统计日期、制单人、制单时间
ENDACCOUNT_DATE.Visible = true;
ENDACCOUNT_DATE.Text = "统计时间：" + StatisticsDay.Text;
div_sign.Attributes["style"] = "display:block";
lblStaffName.Visible = true;
lblStaffName.Text = "制单人：" + PassportInfo.Name;
lblDate.Visible = true;
lblDate.Text = "制单时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
ButtonSearch.Visible = true;
}
#endregion
#region 方法 -> 搜索按钮事件
protected void ButtonSearch_Click(object sender, EventArgs e)
{
DataTable dataTable = null;
if (!string.IsNullOrWhiteSpace(hdData.Value))
{
dataTable = QRWL.Common.JsonHelper.JsonToDataTable(hdData.Value);
}
LoadData(dataTable);
#region 表头信息
GridViewEx1.HeaderRow.Cells[0].ColumnSpan = 4;
GridViewEx1.HeaderRow.Cells[0].HorizontalAlign = HorizontalAlign.Center;
GridViewEx1.HeaderRow.Cells[0].Font.Bold = true;
string TmpTd = "服务区信息" +
"对客营收 | " +
"收银系统 | ";
TmpTd += "序号 | " +
"服务区 | " +
"门店名称 | " +
"机器编号 | ";
TmpTd += "销售流水 | " +
"自然日报表 | " +
"差异金额 | ";
TmpTd += "机器版本 | " +
"心跳时间 | ";
GridViewEx1.HeaderRow.Cells[0].Text = TmpTd;
for (int HeaderRowCount = 1; HeaderRowCount < GridViewEx1.HeaderRow.Cells.Count; HeaderRowCount++)
{
GridViewEx1.HeaderRow.Cells[HeaderRowCount].Visible = false;
}
GridViewEx1.Visible = true;
#endregion
//绑定统计日期、制单人、制单时间
ENDACCOUNT_DATE.Visible = true;
ENDACCOUNT_DATE.Text = "统计时间：" + StatisticsDay.Text;
div_sign.Attributes["style"] = "display:block";
lblStaffName.Visible = true;
lblStaffName.Text = "制单人：" + PassportInfo.Name;
lblDate.Visible = true;
lblDate.Text = "制单时间：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
ButtonSearch.Visible = true;
}
#endregion
#region 方法 -> 获取列表数据
private void LoadData(DataTable dataTable)
{
if (dataTable == null)
{
#region 定义列表内容
dataTable = new DataTable();
dataTable.Columns.Add("SERVERPARTCODE", typeof(string));
dataTable.Columns.Add("SERVERPART_NAME", typeof(string));
dataTable.Columns.Add("SHOPCODE", typeof(string));
dataTable.Columns.Add("SHOPNAME", typeof(string));
dataTable.Columns.Add("MACHINECODE", typeof(string));
dataTable.Columns.Add("SELLMASTER_AMOUNT", typeof(string));
dataTable.Columns.Add("TOTALSELLAMOUNT", typeof(string));
dataTable.Columns.Add("SELLMASTER_AMOUNT_DF", typeof(string));
dataTable.Columns.Add("VERSION_NUM", typeof(string));
dataTable.Columns.Add("CONNECT_DATE", typeof(string));
#endregion
#region 获取数据
string SQLString = "", WhereSQL = "", WhereSQL_State = "";
if (!string.IsNullOrEmpty(SERVERPARTSHOP_ID.SelectedValue) && SERVERPARTSHOP_ID.SelectedValue != "1=1")
{
WhereSQL += " AND SHOPCODE = '" + SERVERPARTSHOP_ID.SelectedValue + "'";
WhereSQL_State += " AND B.SHOPCODE = '" + SERVERPARTSHOP_ID.SelectedValue + "'";
}
if (!string.IsNullOrWhiteSpace(SERVERPART_ID.SelectedValue) && SERVERPART_ID.SelectedValue != "1=1")
{
Label1.Text = SERVERPART_ID.SelectedItem.Text + "门店销售流水汇总表";
WhereSQL += " AND SERVERPART_ID = " + SERVERPART_ID.SelectedValue;
WhereSQL_State += " AND A.SERVERPART_ID = " + SERVERPART_ID.SelectedValue;
}
else if (!string.IsNullOrWhiteSpace(PROVINCE_CODE.SelectedValue) && PROVINCE_CODE.SelectedValue != "1=1")
{
DataTable dtServerpart = new Business.SERVERPART(Transaction).ExecuteDataTable(
"SELECT WM_CONCAT(SERVERPART_ID) FROM T_SERVERPART WHERE PROVINCE_CODE = " + PROVINCE_CODE.SelectedValue);
if (dtServerpart.Rows.Count > 0 && dtServerpart.Rows[0][0].ToString() != "")
{
WhereSQL += " AND SERVERPART_ID IN (" + dtServerpart.Rows[0][0].ToString() + ")";
}
WhereSQL_State += " AND A.PROVINCE_CODE = " + PROVINCE_CODE.SelectedValue;
}
if (!string.IsNullOrEmpty(StatisticsDay.Text.Trim()))
{
WhereSQL += " AND {0} >= TO_DATE('" + StatisticsDay.Text.Trim() +
"','YYYY/MM/DD') AND {0} < TO_DATE('" + StatisticsDay.Text.Trim() + "','YYYY/MM/DD') + 1";
}
//查询销售流水汇总数据
SQLString = string.Format(@"SELECT
TRUNC(A.SELLMASTER_DATE) AS SELLMASTER_DATE,A.SERVERPARTCODE,
B.SERVERPART_NAME,A.SHOPCODE,MAX(A.SHOPNAME) AS SHOPNAME,MACHINECODE,
SUM(A.SELLMASTER_COUNT) AS SELLMASTER_COUNT,SUM(A.SELLMASTER_AMOUNT) AS SELLMASTER_AMOUNT,
SUM(A.SELLMASTER_OFFPRICE) AS SELLMASTER_OFFPRICE,
SUM(A.CASHPAY) AS CASHPAY,SUM(A.MOBILEPAY) AS MOBILEPAY
FROM
HIGHWAY_SELLDATA.T_SELLMASTER A,
HIGHWAY_STORAGE.T_SERVERPART B
WHERE
A.SERVERPARTCODE = B.SERVERPART_CODE AND A.SELLMASTER_STATE IN (1,9){0}
GROUP BY
A.SERVERPARTCODE,A.SHOPCODE,B.SERVERPART_NAME,A.MACHINECODE,TRUNC(A.SELLMASTER_DATE)",
string.Format(WhereSQL, "A.SELLMASTER_DATE"));
DataTable dtSaleStatistics = new EDB.SELLDATA(Transaction).ExecuteDataTable(SQLString);
//查询自然日报表数据
SQLString = string.Format(@"SELECT
ENDACCOUNT_DATE,SERVERPARTCODE,SERVERPART_NAME,SHOPCODE,SHOPNAME,MACHINECODE,
TOTALCOUNT,TOTALSELLAMOUNT,TOTALOFFAMOUNT,CASH,TICKETBILL + OTHERPAY AS MOBILEPAYMENT
FROM
HIGHWAY_SELLDATA.T_ENDACCOUNT_DAILY A
WHERE
VALID = 1 AND MACHINECODE IS NOT NULL{0}", string.Format(WhereSQL, "ENDACCOUNT_DATE"));
DataTable dtDailyEndaccount = new HWSDB.ENDACCOUNT(Transaction).ExecuteDataTable(SQLString);
//查询门店心跳数据
SQLString = string.Format(@"SELECT
C.SERVERPARTCODE,A.SERVERPART_NAME,C.SHOPCODE,B.SHOPNAME,C.MACHINECODE,D.FIELDENUM_NAME,
MAX(C.CONNECT_DATE) AS CONNECT_DATE,MAX(VERSION_NUM) AS VERSION_NUM
FROM
HIGHWAY_STORAGE.T_SERVERPART A,
HIGHWAY_STORAGE.T_SERVERPARTSHOP B,
HIGHWAY_STORAGE.T_STATEFEEDBACK C,
PLATFORM_DICTIONARY.T_FIELDENUM D
WHERE
A.SERVERPART_ID = B.SERVERPART_ID AND B.SHOPCODE = C.SHOPCODE AND
A.SERVERPART_CODE = C.SERVERPARTCODE AND A.PROVINCE_CODE = D.FIELDENUM_ID AND
C.CONNECT_DATE > TRUNC(SYSDATE) - 1{0}
GROUP BY
C.SERVERPARTCODE,A.SERVERPART_NAME,C.SHOPCODE,B.SHOPNAME,C.MACHINECODE,D.FIELDENUM_NAME", WhereSQL_State);
DataTable dtShop = new Business.SERVERPARTSHOP(Transaction).ExecuteDataTable(SQLString);
#endregion
#region 统计数据
DataTable dtMerge = dtDailyEndaccount.Copy();
//将dtSaleStatistics和dtDailyEndaccount合并至一起
if (dtSaleStatistics.Rows.Count > 0)
{
dtMerge.Merge(dtSaleStatistics);
DataView dv = new DataView(dtMerge);
dtMerge = dv.ToTable(true, "SERVERPARTCODE", "SHOPCODE", "MACHINECODE");
}
if (dtShop.Rows.Count > 0)
{
dtMerge.Merge(dtShop);
DataView dv = new DataView(dtMerge);
dtMerge = dv.ToTable(true, "SERVERPARTCODE", "SHOPCODE", "MACHINECODE");
}
//插入Datatable
foreach (DataRow drMerge in dtMerge.Select("", "SERVERPARTCODE,SHOPCODE,MACHINECODE"))
{
//判断门店信息是否已经插入
DataRow[] DataRowList = dataTable.Select("SERVERPARTCODE = '" + drMerge["SERVERPARTCODE"].ToString() + "' AND SHOPCODE = '" +
drMerge["SHOPCODE"].ToString() + "' AND MACHINECODE = '" + drMerge["MACHINECODE"].ToString() + "'");
//获取门店信息
DataRow[] drShop = dtShop.Select("SERVERPARTCODE = '" + drMerge["SERVERPARTCODE"].ToString() + "' AND SHOPCODE = '" +
drMerge["SHOPCODE"].ToString() + "' AND MACHINECODE = '" + drMerge["MACHINECODE"].ToString() + "'");
//判断自然日报表是否有数据
DataRow[] drDailyEndaccount = dtDailyEndaccount.Rows.Count == 0 ? null : dtDailyEndaccount.Select(
"SERVERPARTCODE = '" + drMerge["SERVERPARTCODE"].ToString() + "' AND SHOPCODE = '" +
drMerge["SHOPCODE"].ToString() + "' AND MACHINECODE = '" + drMerge["MACHINECODE"].ToString() + "'");
//判断销售流水中是否有数据
DataRow[] drSaleStatistics = dtSaleStatistics.Rows.Count == 0 ? null : dtSaleStatistics.Select(
"SERVERPARTCODE = '" + drMerge["SERVERPARTCODE"].ToString() + "' AND SHOPCODE = '" +
drMerge["SHOPCODE"].ToString() + "' AND MACHINECODE = '" + drMerge["MACHINECODE"].ToString() + "'");
DataRow dataRow = DataRowList.Length > 0 ? DataRowList[0] : dataTable.NewRow();
dataRow["SERVERPARTCODE"] = drMerge["SERVERPARTCODE"].ToString(); //服务区编码
dataRow["SHOPCODE"] = drMerge["SHOPCODE"].ToString(); //门店编码
dataRow["MACHINECODE"] = drMerge["MACHINECODE"].ToString(); //机器编码
if (string.IsNullOrWhiteSpace(dataRow["CONNECT_DATE"].ToString()) && drShop.Length > 0)
{
//省份+服务区名称
dataRow["SERVERPART_NAME"] = "【" + drShop[0]["FIELDENUM_NAME"].ToString().Replace(
"省", "").Replace("自治区", "") + "】" + drShop[0]["SERVERPART_NAME"].ToString();
//门店名称
dataRow["SHOPNAME"] = drShop[0]["SHOPNAME"].ToString();
//收银版本号
dataRow["VERSION_NUM"] = drShop[0]["VERSION_NUM"].ToString();
//心跳时间
dataRow["CONNECT_DATE"] = drShop[0]["CONNECT_DATE"].ToString();
}
else if (dtShop.Select("SERVERPARTCODE = '" + drMerge["SERVERPARTCODE"].ToString() + "'").Length > 0)
{
drShop = dtShop.Select("SERVERPARTCODE = '" + drMerge["SERVERPARTCODE"].ToString() + "'");
//省份+服务区名称
dataRow["SERVERPART_NAME"] = "【" + drShop[0]["FIELDENUM_NAME"].ToString().Replace(
"省", "").Replace("自治区", "") + "】" + drShop[0]["SERVERPART_NAME"].ToString();
}
//收银端失败，通道有流水，则标识收银机端有误
if (drDailyEndaccount != null && drDailyEndaccount.Length > 0)
{
if (string.IsNullOrWhiteSpace(dataRow["SERVERPART_NAME"].ToString()))
{
dataRow["SERVERPART_NAME"] = drDailyEndaccount[0]["SERVERPART_NAME"].ToString();
}
if (string.IsNullOrWhiteSpace(dataRow["SHOPNAME"].ToString()))
{
dataRow["SHOPNAME"] = drDailyEndaccount[0]["SHOPNAME"].ToString();
}
dataRow["TOTALSELLAMOUNT"] = drDailyEndaccount[0]["TOTALSELLAMOUNT"];
}
else
{
dataRow["TOTALSELLAMOUNT"] = "";
}
if (drSaleStatistics != null && drSaleStatistics.Length > 0)
{
if (string.IsNullOrWhiteSpace(dataRow["SERVERPART_NAME"].ToString()))
{
dataRow["SERVERPART_NAME"] = drDailyEndaccount[0]["SERVERPART_NAME"].ToString();
}
if (string.IsNullOrWhiteSpace(dataRow["SHOPNAME"].ToString()))
{
dataRow["SHOPNAME"] = drSaleStatistics[0]["SHOPNAME"].ToString();
}
dataRow["SELLMASTER_AMOUNT"] = drSaleStatistics[0]["SELLMASTER_AMOUNT"];
}
else
{
dataRow["SELLMASTER_AMOUNT"] = "";
}
if (!string.IsNullOrWhiteSpace(dataRow["SELLMASTER_AMOUNT"].ToString()) &&
!string.IsNullOrWhiteSpace(dataRow["TOTALSELLAMOUNT"].ToString()))
{
dataRow["SELLMASTER_AMOUNT_DF"] = dataRow["SELLMASTER_AMOUNT"].TryParseToDecimal() -
dataRow["TOTALSELLAMOUNT"].TryParseToDecimal();
}
if (DataRowList.Length == 0)
{
dataTable.Rows.Add(dataRow);
}
}
hdData.Value = QRWL.Common.JsonHelper.DataTableToJson(dataTable);
#endregion
}
string SelectSQL = "", searchInfo = "";
foreach (SearchItem searchItem in GridViewSearch1.Items)
{
if (searchItem.Checked && !string.IsNullOrWhiteSpace(TextBox_Search.Text.Trim()))
{
searchInfo += (searchInfo == "" ? "" : " OR ") + searchItem.FieldName +
" LIKE '%" + TextBox_Search.Text.Trim() + "%'";
}
}
switch (DATA_TYPE.SelectedValue)
{
case "1":
SelectSQL = "((SELLMASTER_AMOUNT = '' and TOTALSELLAMOUNT <> '0') or " +
"(SELLMASTER_AMOUNT <> '' and SELLMASTER_AMOUNT <> TOTALSELLAMOUNT))";
break;
}
if (!string.IsNullOrWhiteSpace(searchInfo))
{
SelectSQL += SelectSQL == "" ? searchInfo : " and (" + searchInfo + ")";
}
if (!string.IsNullOrWhiteSpace(SelectSQL))
{
dataTable.DefaultView.RowFilter = SelectSQL;
}
dataTable.DefaultView.Sort = "SERVERPARTCODE,SHOPCODE,MACHINECODE";
dataTable = dataTable.DefaultView.ToTable();
GridViewEx1.DataSource = dataTable;
GridViewEx1.DataBind();
}
#endregion
#region 方法 -> 行绑定事件
protected void GridViewEx1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
if (DATA_TYPE.SelectedValue != "1")
{
if (e.Row.Cells[e.Row.Cells.Count - 3].Text.TryParseToDecimal() != 0)
{
e.Row.ForeColor = System.Drawing.Color.Red;
}
}
if (e.Row.Cells[e.Row.Cells.Count - 1].Text.TryParseToDateTime() < DateTime.Now.Date)
{
e.Row.Cells[e.Row.Cells.Count - 1].ForeColor = System.Drawing.Color.LightGray;
}
else if (e.Row.Cells[e.Row.Cells.Count - 1].Text.TryParseToDateTime() > DateTime.Now.AddMinutes(-20))
{
e.Row.Cells[e.Row.Cells.Count - 1].ForeColor = System.Drawing.Color.DeepSkyBlue;
}
}
}
#endregion
}
}