using System; using System.Configuration; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.Text; using System.Net; using System.IO; using System.Web; using System.Net.Security; using System.Security.Cryptography.X509Certificates; using NJ = Newtonsoft.Json; using NJL = Newtonsoft.Json.Linq; using SuperMap.RealEstate.CoreFrameWork.Dictionary.Business; using SuperMap.RealEstate.ExchangeData.Business; using SuperMap.RealEstate.MemberShip.Storage.Business; using SRSSB = SuperMap.RealEstate.Seller.Storage.Business; using SRHSB = SuperMap.RealEstate.HighWay.SellData.Business; using Business = SuperMap.RealEstate.HighWay.Storage.Business; using Model = SuperMap.RealEstate.HighWay.Storage.Model; using HZQR.Common; using QRWL.Common; namespace SuperMap.RealEstate.HighWay.Handler { ///

/// handler_ajax 的摘要说明 ///

public class handler_ajax : IHttpHandler { //AppID(应用ID) public const string AppID = "wx85e69a5fb601be16"; //AppSecret(应用密钥) public const string AppSecret = "dfcf7748e6c7027695cbec8ffedc04dc"; public const string charSet = "1,2,3,4,5,6,7,8,9"; protected string _serverpart_id = ConfigurationManager.AppSettings["UmiformCommodity"].ToString().Split('|')[0]; protected string _PROVINCE_CODE = ConfigurationManager.AppSettings["UmiformCommodity"].ToString().Split('|')[2]; protected string _CigaretteType = ConfigurationManager.AppSettings["CigaretteType"].ToString().Split('|')[0]; protected string _UrlAddress = ConfigurationManager.AppSettings["UrlAddress"].ToString(); protected string _FilePath = ConfigurationManager.AppSettings["FilePath"].ToString(); protected string _CopyPath = ConfigurationManager.AppSettings["CopyPath"].ToString(); public void ProcessRequest(HttpContext context) { DataTable _DataTable = null; DataTable _DataTablePoint = null; ServiceModel.Transaction _Transaction = new ServiceModel.Transaction(); string action_type = HttpContext.Current.Request.Form["action_type"]; string action_data = HttpContext.Current.Request.Form["action_data"]; string action_record = ""; List _ListServerPartObject = null; string reString = string.Empty; string StrSql = string.Empty; List _ListTreeItem = null; if (string.IsNullOrEmpty(action_type)) { action_type = HttpContext.Current.Request.Params["action_type"]; action_data = HttpContext.Current.Request.Params["action_data"]; } //耗时监听 Stopwatch sw = new Stopwatch(); sw.Start(); try { switch (action_type) { case "getServerPart": reString = getServerPart(action_data); break; //获得对应的子类 case "getServerPartTypeByCode": action_record = HttpContext.Current.Request.Form["action_record"]; reString = getServerPartTypeByCode(action_data, _Transaction, action_record); break; case "getServerPartByType": reString = getServerPartByType(action_data, _Transaction); break; case "getServerPartByName": reString = getServerPartByName(action_data, _Transaction); break; #region 获得服务区名称 case "getServerPartName": try { bool HasFlag = false; string ProwerString = ""; action_data = action_data.Replace("|", ","); _ListServerPartObject = new List(); _ListTreeItem = new List(); foreach (FieldEnum _FieldEnum in (new FieldEnum(_Transaction)).FillCollection( "where FieldEnum_Value in(" + action_data + ")")) { ProwerString += ProwerString == "" ? _FieldEnum.FieldEnum_ID.ToString() : "," + _FieldEnum.FieldEnum_ID; } foreach (Business.SERVERPART _SERVERPART in (new Business.SERVERPART(_Transaction)).FillCollection( "where FieldEnum_ID in(" + ProwerString + ") order by SERVERPART_INDEX,SERVERPART_CODE")) { TreeItem _TreeItem = new TreeItem(); _TreeItem.id = _SERVERPART.SERVERPART_ID_Encrypt; _TreeItem.pid = "0"; _TreeItem.open = false; _TreeItem.name = _SERVERPART.SERVERPART_NAME; _TreeItem.tag = _SERVERPART.SERVERPART_X.ToString() + "|" + _SERVERPART.SERVERPART_Y.ToString(); _TreeItem.icon = "/HighWay/Resources/v1_0/TreeView/css/zTreeStyle/img/diy/car.png"; if (_SERVERPART.SERVERPART_X == null) continue; if (_SERVERPART.SERVERPART_NAME == "高速驿网") { HasFlag = true; } _ListTreeItem.Add(_TreeItem); } if (!HasFlag) { //添加高速驿网 Business.SERVERPART _SERVERPART = new Business.SERVERPART(_Transaction); _SERVERPART.AddSearchParameter("SERVERPART_CODE", "330001"); if (_SERVERPART.Search()) { TreeItem _TreeItem = new TreeItem(); _TreeItem.id = _SERVERPART.SERVERPART_ID_Encrypt; _TreeItem.pid = "0"; _TreeItem.open = false; _TreeItem.name = _SERVERPART.SERVERPART_NAME; _TreeItem.icon = "/HighWay/Resources/v1_0/TreeView/css/zTreeStyle/img/diy/car.png"; _TreeItem.tag = _SERVERPART.SERVERPART_X.ToString() + "|" + _SERVERPART.SERVERPART_Y.ToString(); _ListTreeItem.Add(_TreeItem); } } reString = JsonHelper.ListToJson(_ListTreeItem, "TreeItem"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获得销售数据 case "getSellDataObject": try { List _ListSellDataObject = new List(); StrSql = "SELECT * FROM (SELECT COMMODITY_NAME,SELLCOUNT,SELLPRICE,FACTAMOUNT,SELLDATA_DATE " + "FROM HIGHWAY_SELLDATA.T_SELLDATA WHERE SERVERPART_ID = " + action_data.ToDecrypt() + " ORDER BY SELLDATA_DATE DESC) WHERE ROWNUM < 20 "; DataTable dt = (new Business.COMMODITY(_Transaction)).ExecuteDataTable(StrSql); foreach (DataRow _DataRow in dt.Rows) { SellDataObject _SellDataObject = new SellDataObject(); _SellDataObject.name = _DataRow["COMMODITY_NAME"].ToString(); _SellDataObject.price = _DataRow["SELLPRICE"].ToString(); _SellDataObject.commoditycount = _DataRow["SELLCOUNT"].ToString(); _SellDataObject.totalprice = _DataRow["FACTAMOUNT"].ToString(); _SellDataObject.selltime = DateTime.Parse(_DataRow["SELLDATA_DATE"].ToString()).ToLongTimeString(); //if (DateTime.Parse(dt.Rows[0]["SELLDATA_DATE"].ToString()) < DateTime.Now.AddMinutes(-20)) //{ // _SellDataObject.selltime = DateTime.Parse(_DataRow["SELLDATA_DATE"].ToString()).AddMinutes( // (DateTime.Now - DateTime.Parse(dt.Rows[0]["SELLDATA_DATE"].ToString())).TotalMinutes).ToLongTimeString(); //} _ListSellDataObject.Add(_SellDataObject); } reString = JsonHelper.ListToJson(_ListSellDataObject, "SellDataObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取实时销售信息 case "GetCurSellData": try { List _ListSellDataObject = new List(); StrSql = "SELECT * FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE SERVERPART_ID = " + action_data.ToDecrypt() + " AND CONTENT LIKE '%|%|%|%' ORDER BY CONNECT_DATE DESC"; DataTable dt = (new Business.COMMODITY(_Transaction)).ExecuteDataTable(StrSql); foreach (DataRow _DataRow in dt.Rows) { if (_DataRow["CONTENT"].ToString().Split('|').Length == 5) { //解析对应的商品 1.最后销售日期 + 销售时间 + 销售商品 + 销售金额 + 版本号 //商品 + 销售时间 + 金额 string SellString = ""; // 7 最后销售商品 string CodeString = ""; //foreach (string str in _DataRow["CONTENT"].ToString().Split('|')[2].Split(',')) //{ // CodeString += (CodeString == "" ? "" : ",") + "'" + str + "'"; //} CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN(" + CodeString + ")")) { if (SellString.Length > 20) { SellString = SellString.Substring(0, 20) + "..."; break; } SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME; } // 9 最后销售金额 reString = "金额:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; // 8 最后销售时间 //reString += _DataRow["CONTENT"].ToString().Split('|')[1].Split(' ')[1] + ","; //reString += (SellString.Length > 20 ? SellString.Substring(0, 20) + "..." : SellString); SellDataObject _SellDataObject = new SellDataObject(); _SellDataObject.name = reString; _SellDataObject.selltime = _DataRow["CONNECT_DATE"].ToString(); _ListSellDataObject.Add(_SellDataObject); break; } else if (_DataRow["CONTENT"].ToString().Split('|').Length == 6) { string SellString = ""; // 7 最后销售商品 string CodeString = ""; //foreach (string str in _DataRow["CONTENT"].ToString().Split('|')[2].Split(',')) //{ // CodeString += (CodeString == "" ? "" : ",") + "'" + str + "'"; //} CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; int i = 0; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN(" + CodeString + ")")) { if (SellString.Length > 20) { SellString = SellString.Substring(0, 20) + "..."; break; } SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME + _DataRow["CONTENT"].ToString().Split('|')[4].Split(',')[i]; i++; } // 9 最后销售金额 reString = "金额:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; // 8 最后销售时间 //reString += _DataRow["CONTENT"].ToString().Split('|')[1].Split(' ')[1] + ","; //reString += (SellString.Length > 20 ? SellString.Substring(0, 20) + "..." : SellString); SellDataObject _SellDataObject = new SellDataObject(); _SellDataObject.name = reString; _SellDataObject.selltime = _DataRow["CONNECT_DATE"].ToString(); _ListSellDataObject.Add(_SellDataObject); break; } } reString = JsonHelper.ListToJson(_ListSellDataObject, "SellDataObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurTotalSellData": try { List _ListSellDataObject = new List(); StrSql = @"SELECT * FROM(SELECT SERVERPART_ID,SERVERPART_NAME,CONNECT_DATE,SHOPNAME,SHOPCODE,CONTENT, PROVINCE_CODE,ROW_NUMBER()OVER(PARTITION BY SERVERPART_ID ORDER BY CONNECT_DATE DESC)COLNUM FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE CONTENT LIKE '%|%|%|%') WHERE COLNUM = 1"; DataTable dt = (new Business.COMMODITY(_Transaction)).ExecuteDataTable(StrSql); foreach (DataRow _DataRow in dt.Rows) { if (_DataRow["CONTENT"].ToString().Split('|').Length == 5) { //解析对应的商品 1.最后销售日期 + 销售时间 + 销售商品 + 销售金额 + 版本号 string SellString = ""; // 7 最后销售商品 //string CodeString = ""; //CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN('" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "' AND PROVINCE_CODE = '" + _DataRow["PROVINCE_CODE"].ToString() + "')")) { if (SellString.Length > 20) { SellString = SellString + "..."; break; } SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME; } // 9 最后销售金额 reString = "当前交易:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; SellDataObject _SellDataObject = new SellDataObject(); _SellDataObject.name = _DataRow["SERVERPART_ID"].ToEncrypt(); _SellDataObject.totalprice = reString; _SellDataObject.selltime = _DataRow["CONNECT_DATE"].ToString(); _ListSellDataObject.Add(_SellDataObject); } else if (_DataRow["CONTENT"].ToString().Split('|').Length == 6) { string SellString = ""; // 7 最后销售商品 string CodeString = ""; //CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; //int i = 0; //foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( // "WHERE COMMODITY_CODE IN('" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "')")) //{ // if (SellString.Length > 20) // { // SellString = SellString + "..."; // break; // } // CodeString = _DataRow["CONTENT"].ToString().Split('|')[4].Split(',')[i]; // SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME + // "(" + double.Parse(CodeString.Split('*')[0].Split('(')[1]) + "*" + // double.Parse(CodeString.Split('*')[1].Split(')')[0]) + ")"; // i++; //} CodeString = _DataRow["CONTENT"].ToString().Split('|')[2]; for (int i = 0; i < CodeString.Split(',').Length; i++) { if (SellString.Length > 20) { SellString = SellString + "..."; break; } Business.COMMODITY _COMMODITY = new Business.COMMODITY(_Transaction); _COMMODITY.AddSearchParameter("COMMODITY_CODE", CodeString.Split(',')[i]); _COMMODITY.AddSearchParameter("PROVINCE_CODE", _DataRow["PROVINCE_CODE"].ToString()); if (_COMMODITY.Search()) { SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME + "(" + _DataRow["CONTENT"].ToString().Split('|')[4].Split(',')[i] + ")"; } } // 9 最后销售金额 reString = "当前交易:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; SellDataObject _SellDataObject = new SellDataObject(); _SellDataObject.ServerpartName = _DataRow["SERVERPART_NAME"].ToString(); _SellDataObject.price = _DataRow["CONTENT"].ToString().Split('|')[3]; _SellDataObject.name = _DataRow["SERVERPART_ID"].ToEncrypt(); _SellDataObject.totalprice = reString; _SellDataObject.selltime = _DataRow["CONNECT_DATE"].ToString(); _ListSellDataObject.Add(_SellDataObject); } } reString = JsonHelper.ListToJson(_ListSellDataObject, "SellDataObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取实时稽核信息 case "GetCurCheckInfo": try { List _ListCheckInfoObject = new List(); StrSql = "SELECT * FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE SERVERPARTSHOP_ID = " + action_data + " AND CONTENT LIKE '%|%|%|%' ORDER BY CONNECT_DATE DESC"; _DataTable = (new CONFIGURATION(_Transaction)).ExecuteDataTable(StrSql); foreach (DataRow _DataRow in _DataTable.Rows) { if (_DataRow["CONTENT"].ToString().Split('|').Length == 5) { CheckInfoObject _CheckInfoObject = new CheckInfoObject(); _CheckInfoObject.SHOPNAME = _DataRow["SHOPNAME"].ToString(); _CheckInfoObject.CURRCOMMCOUNT = _DataRow["CURRCOMMCOUNT"].ToString(); _CheckInfoObject.CONNECT_DATE = _DataRow["CONNECT_DATE"].ToString(); _CheckInfoObject.CURRAMOUNT = _DataRow["CURRAMOUNT"].ToString(); _CheckInfoObject.CURRSELLCOUNT = _DataRow["CURRSELLCOUNT"].ToString(); _CheckInfoObject.CURRPRESON = _DataRow["CURRPRESON"].ToString(); _CheckInfoObject.MACHINENAME = _DataRow["MACHINENAME"].ToString(); _CheckInfoObject.CONNECT_IP = _DataRow["CONNECT_IP"].ToString(); //_CheckInfoObject.CONTENT = _DataRow["CONTENT"].ToString(); //解析对应的商品 1.最后销售日期 + 销售时间 + 销售商品 + 销售金额 + 版本号 //商品 + 销售时间 + 金额 string SellString = ""; // 7 最后销售商品 string CodeString = ""; CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN(" + CodeString + ") AND PROVINCE_CODE = " + _DataRow["PROVINCE_CODE"].ToString())) { SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME; } // 9 最后销售金额 _CheckInfoObject.CONTENT = "金额:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; _CheckInfoObject.VERSION_NUMBER = _DataRow["CONTENT"].ToString().Split('|')[4]; // 8 最后销售时间 //reString += _DataRow["CONTENT"].ToString().Split('|')[1].Split(' ')[1] + ","; //reString += (SellString.Length > 20 ? SellString.Substring(0, 20) + "..." : SellString); _ListCheckInfoObject.Add(_CheckInfoObject); //break; } else if (_DataRow["CONTENT"].ToString().Split('|').Length == 6) { string SellString = ""; // 7 最后销售商品 string CodeString = ""; CheckInfoObject _CheckInfoObject = new CheckInfoObject(); _CheckInfoObject.SHOPNAME = _DataRow["SHOPNAME"].ToString(); _CheckInfoObject.CURRCOMMCOUNT = _DataRow["CURRCOMMCOUNT"].ToString(); _CheckInfoObject.CONNECT_DATE = _DataRow["CONNECT_DATE"].ToString(); _CheckInfoObject.CURRAMOUNT = _DataRow["CURRAMOUNT"].ToString(); _CheckInfoObject.CURRSELLCOUNT = _DataRow["CURRSELLCOUNT"].ToString(); _CheckInfoObject.CURRPRESON = _DataRow["CURRPRESON"].ToString(); _CheckInfoObject.MACHINENAME = _DataRow["MACHINENAME"].ToString(); _CheckInfoObject.CONNECT_IP = _DataRow["CONNECT_IP"].ToString(); CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; int i = 0; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN(" + CodeString + ") AND PROVINCE_CODE = " + _DataRow["PROVINCE_CODE"].ToString())) { SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME + _DataRow["CONTENT"].ToString().Split('|')[4].Split(',')[i]; i++; } // 9 最后销售金额 _CheckInfoObject.CONTENT = "金额:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; _CheckInfoObject.VERSION_NUMBER = _DataRow["CONTENT"].ToString().Split('|')[5]; // 8 最后销售时间 //reString += _DataRow["CONTENT"].ToString().Split('|')[1].Split(' ')[1] + ","; //reString += (SellString.Length > 20 ? SellString.Substring(0, 20) + "..." : SellString); _ListCheckInfoObject.Add(_CheckInfoObject); //break; } } reString = JsonHelper.ListToJson(_ListCheckInfoObject, "CheckInfoObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurCheckInfoByServerpart": try { List _ListCheckInfoObject = new List(); StrSql = "SELECT * FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE SERVERPART_ID = " + action_data + " AND CONTENT LIKE '%|%|%|%' ORDER BY CONNECT_DATE DESC"; _DataTable = (new CONFIGURATION(_Transaction)).ExecuteDataTable(StrSql); foreach (DataRow _DataRow in _DataTable.Rows) { if (_DataRow["CONTENT"].ToString().Split('|').Length == 5) { CheckInfoObject _CheckInfoObject = new CheckInfoObject(); _CheckInfoObject.SHOPNAME = _DataRow["SHOPNAME"].ToString(); _CheckInfoObject.CURRCOMMCOUNT = _DataRow["CURRCOMMCOUNT"].ToString(); _CheckInfoObject.CONNECT_DATE = _DataRow["CONNECT_DATE"].ToString(); _CheckInfoObject.CURRAMOUNT = _DataRow["CURRAMOUNT"].ToString(); _CheckInfoObject.CURRSELLCOUNT = _DataRow["CURRSELLCOUNT"].ToString(); _CheckInfoObject.CURRPRESON = _DataRow["CURRPRESON"].ToString(); _CheckInfoObject.MACHINENAME = _DataRow["MACHINENAME"].ToString(); _CheckInfoObject.CONNECT_IP = _DataRow["CONNECT_IP"].ToString(); //_CheckInfoObject.CONTENT = _DataRow["CONTENT"].ToString(); //解析对应的商品 1.最后销售日期 + 销售时间 + 销售商品 + 销售金额 + 版本号 //商品 + 销售时间 + 金额 string SellString = ""; // 7 最后销售商品 string CodeString = ""; CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN(" + CodeString + ") AND PROVINCE_CODE = " + _DataRow["PROVINCE_CODE"].ToString())) { SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME; } // 9 最后销售金额 _CheckInfoObject.CONTENT = "金额:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; _CheckInfoObject.VERSION_NUMBER = _DataRow["CONTENT"].ToString().Split('|')[4]; // 8 最后销售时间 //reString += _DataRow["CONTENT"].ToString().Split('|')[1].Split(' ')[1] + ","; //reString += (SellString.Length > 20 ? SellString.Substring(0, 20) + "..." : SellString); _ListCheckInfoObject.Add(_CheckInfoObject); } else if (_DataRow["CONTENT"].ToString().Split('|').Length == 6) { string SellString = ""; // 7 最后销售商品 string CodeString = ""; CheckInfoObject _CheckInfoObject = new CheckInfoObject(); _CheckInfoObject.SHOPNAME = _DataRow["SHOPNAME"].ToString(); _CheckInfoObject.CURRCOMMCOUNT = _DataRow["CURRCOMMCOUNT"].ToString(); _CheckInfoObject.CONNECT_DATE = _DataRow["CONNECT_DATE"].ToString(); _CheckInfoObject.CURRAMOUNT = _DataRow["CURRAMOUNT"].ToString(); _CheckInfoObject.CURRSELLCOUNT = _DataRow["CURRSELLCOUNT"].ToString(); _CheckInfoObject.CURRPRESON = _DataRow["CURRPRESON"].ToString(); _CheckInfoObject.MACHINENAME = _DataRow["MACHINENAME"].ToString(); _CheckInfoObject.CONNECT_IP = _DataRow["CONNECT_IP"].ToString(); CodeString = "'" + _DataRow["CONTENT"].ToString().Split('|')[2].Replace(",", "','") + "'"; int i = 0; foreach (Business.COMMODITY _COMMODITY in new Business.COMMODITY(_Transaction).FillCollection( "WHERE COMMODITY_CODE IN(" + CodeString + ") AND PROVINCE_CODE = " + _DataRow["PROVINCE_CODE"].ToString())) { SellString += (SellString == "" ? "" : ",") + _COMMODITY.COMMODITY_NAME + _DataRow["CONTENT"].ToString().Split('|')[4].Split(',')[i]; i++; } // 9 最后销售金额 _CheckInfoObject.CONTENT = "金额:" + _DataRow["CONTENT"].ToString().Split('|')[3] + "元," + SellString; _CheckInfoObject.VERSION_NUMBER = _DataRow["CONTENT"].ToString().Split('|')[5]; // 8 最后销售时间 //reString += _DataRow["CONTENT"].ToString().Split('|')[1].Split(' ')[1] + ","; //reString += (SellString.Length > 20 ? SellString.Substring(0, 20) + "..." : SellString); _ListCheckInfoObject.Add(_CheckInfoObject); } } reString = JsonHelper.ListToJson(_ListCheckInfoObject, "CheckInfoObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取服务区信息 case "getTotalServerPart": try { List _SERVERPARTList = new List(); foreach (Business.SERVERPART _SERVERPART in new Business.SERVERPART(_Transaction).FillCollection( @"WHERE SERVERPART_ID IN (SELECT SERVERPART_ID FROM HIGHWAY_STORAGE.T_SERVERPARTSHOP WHERE ISVALID = 1) ORDER BY SERVERPART_INDEX,SERVERPART_CODE")) { _SERVERPART.SERVERPART_DESC = null; _SERVERPART.SERVERPART_INFO = null; _SERVERPARTList.Add(_SERVERPART); } reString = JsonHelper.ListToJson(_SERVERPARTList, "SERVERPARTObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getTotalServerPartWidthRegion": try { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable(@"SELECT REGION_ID, TYPE_NAME,SERVERPART_ID,SERVERPART_NAME,SERVERPART_ADDRESS,SERVERPART_INDEX, SERVERPART_CODE,PROVINCE_CODE,REGIONTYPE_ID,STATISTIC_TYPE,SERVERPARTTYPE_ID FROM HIGHWAY_STORAGE.V_REGIONSERVERPART")); reString = JsonHelper.DataTableToJson(_DataTable, "ServerPartRegion"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取门店信息 case "getServerPartShop": try { DataTable _SERVERPARTSHOPList = new Business.SERVERPARTSHOP(_Transaction).FillDataTable( "WHERE ISVALID = 1 AND SERVERPART_ID = " + action_data + " ORDER BY SERVERPART_ID,SHOPREGION,SHOPTRADE,SHOPCODE"); reString = JsonHelper.DataTableToJson(_SERVERPARTSHOPList, "ServerPartShopObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取移动支付信息 case "getMobilePay": try { action_record = HttpContext.Current.Request.Params["action_record"]; _DataTable = (new Business.SERVERPART(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_SELLDATA.V_MOBILEPAYMENT WHERE 1 = 1" + (string.IsNullOrEmpty(action_data) ? "" : " AND SERVERPART_ID = " + action_data) + (string.IsNullOrEmpty(action_record) ? "" : " AND SELLDATA_DATE >= TO_DATE('" + action_record.Split(',')[0] + "','YYYY/MM/DD') AND SELLDATA_DATE < TO_DATE('" + action_record.Split(',')[1] + "','YYYY/MM/DD') + 1") + " ORDER BY SERVERPARTCODE,SHOPCODE")); reString = JsonHelper.DataTableToJson(_DataTable, "MobilePay"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getTotalMobilePay": try { action_record = HttpContext.Current.Request.Params["action_record"]; _DataTable = (new Business.SERVERPART(_Transaction).ExecuteDataTable( @"SELECT TRUNC(SELLDATA_DATE) AS SELLDATA_DATE,SUM(SELLCOUNT) AS SELLCOUNT, SUM(OFFPRICE) AS OFFPRICE,SUM(FACTAMOUNT) AS FACTAMOUNT, SUM(TICKETBILL) AS TICKETBILL,SUM(OTHERPAY) AS OTHERPAY,COUNT(1) AS ROWCOUNT FROM HIGHWAY_SELLDATA.V_MOBILEPAYMENT WHERE 1 = 1" + (string.IsNullOrEmpty(action_data) ? "" : " AND SERVERPART_ID = " + action_data) + (string.IsNullOrEmpty(action_record) ? "" : " AND SELLDATA_DATE >= TO_DATE('" + action_record.Split(',')[0] + "','YYYY/MM/DD') AND SELLDATA_DATE < TO_DATE('" + action_record.Split(',')[1] + "','YYYY/MM/DD') + 1") + " GROUP BY TRUNC(SELLDATA_DATE) ORDER BY SELLDATA_DATE")); reString = JsonHelper.DataTableToJson(_DataTable, "MobilePay"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getTotalMobilePayByServerpart": try { action_record = HttpContext.Current.Request.Params["action_record"]; if (string.IsNullOrEmpty(action_data)) { _DataTable = (new Business.SERVERPART(_Transaction).ExecuteDataTable( @"SELECT SERVERPART_ID,SERVERPARTCODE,SERVERPART_NAME,PROVINCE_CODE, SUM(SELLCOUNT) AS SELLCOUNT,SUM(OFFPRICE) AS OFFPRICE,SUM(FACTAMOUNT) AS FACTAMOUNT, SUM(TICKETBILL) AS TICKETBILL,SUM(OTHERPAY) AS OTHERPAY,COUNT(1) AS ROWCOUNT FROM HIGHWAY_SELLDATA.V_MOBILEPAYMENT" + (string.IsNullOrEmpty(action_record) ? "" : " WHERE SELLDATA_DATE >= TO_DATE('" + action_record.Split(',')[0] + "','YYYY/MM/DD') AND SELLDATA_DATE < TO_DATE('" + action_record.Split(',')[1] + "','YYYY/MM/DD') + 1") + " GROUP BY SERVERPART_ID,SERVERPARTCODE,SERVERPART_NAME,PROVINCE_CODE")); } else { _DataTable = (new Business.SERVERPART(_Transaction).ExecuteDataTable( @"SELECT SERVERPART_ID,SERVERPARTCODE,SERVERPART_NAME,PROVINCE_CODE,SHOPCODE,SHOPNAME, SUM(SELLCOUNT) AS SELLCOUNT,SUM(OFFPRICE) AS OFFPRICE,SUM(FACTAMOUNT) AS FACTAMOUNT, SUM(TICKETBILL) AS TICKETBILL,SUM(OTHERPAY) AS OTHERPAY,COUNT(1) AS ROWCOUNT FROM HIGHWAY_SELLDATA.V_MOBILEPAYMENT WHERE SERVERPART_ID = " + action_data + (string.IsNullOrEmpty(action_record) ? "" : " AND SELLDATA_DATE >= TO_DATE('" + action_record.Split(',')[0] + "','YYYY/MM/DD') AND SELLDATA_DATE < TO_DATE('" + action_record.Split(',')[1] + "','YYYY/MM/DD') + 1") + " GROUP BY SERVERPART_ID,SERVERPARTCODE,SERVERPART_NAME,PROVINCE_CODE,SHOPCODE,SHOPNAME")); } reString = JsonHelper.DataTableToJson(_DataTable, "MobilePay"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 根据单个服务器或者对应的百度地图解析 case "getPointPos": _DataTable = new DataTable(); _DataTable.Columns.Add("MAPINFO"); _DataTable.Columns.Add("MAP_ADDRESS"); _DataTable.Columns.Add("MAP_TEL"); _DataTable.Columns.Add("MAP_OBJECTCODE"); _DataTable.Columns.Add("MAPX"); _DataTable.Columns.Add("MAPY"); try { //获得服务区数据 Storage.Business.SERVERPART _SERVERPART = new Storage.Business.SERVERPART(_Transaction); _SERVERPART.AddSearchParameter("SERVERPART_ID", action_data.ToDecrypt()); if (_SERVERPART.Search()) { //检测对应的服务区图片 //string imagePath = "SELECT A.IMAGE_PATH FROM T_IMAGE A,T_SERVERPARTTYPE B WHERE B.STATIC_TYPE = A.IMAGE_ID AND " DataRow _DataRow = _DataTable.NewRow(); _DataRow["MAPINFO"] = _SERVERPART.SERVERPART_NAME + " " + _SERVERPART.SERVERPART_INFO; _DataRow["MAP_ADDRESS"] = _SERVERPART.SERVERPART_ADDRESS; _DataRow["MAP_TEL"] = _SERVERPART.SERVERPART_TEL; _DataRow["MAP_OBJECTCODE"] = _SERVERPART.SERVERPART_ID_Encrypt; if (_SERVERPART.SERVERPART_X == null) reString = "error"; _DataRow["MAPX"] = _SERVERPART.SERVERPART_X; _DataRow["MAPY"] = _SERVERPART.SERVERPART_Y; _DataTable.Rows.Add(_DataRow); } string PointString = string.Empty; PointString = BaiDuMapHelper.GetBaiduPointString(PointString, _DataTable); string imagePath = ""; try { imagePath = HttpContext.Current.Request.Form["imagepath"]; } catch { } PointString = PointString.Replace("isOpen: 0", "isOpen: 0,imagepath:\"" + imagePath + "\""); reString = PointString;//"{\"markerArr\":[" + PointString + "]}"; } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 根据类型获取点位 case "getPointPosByServerPartType": string OutTypeString = action_data.ToDecrypt(); _DataTablePoint = new DataTable(); _DataTablePoint.Columns.Add("MAPINFO"); _DataTablePoint.Columns.Add("MAP_ADDRESS"); _DataTablePoint.Columns.Add("MAP_TEL"); _DataTablePoint.Columns.Add("MAP_OBJECTCODE"); _DataTablePoint.Columns.Add("MAPX"); _DataTablePoint.Columns.Add("MAPY"); _DataTablePoint.Columns.Add("IMAGEPATH"); try { //根据类别获得服务区 GetSubServerPartType(ref OutTypeString, action_data.ToDecrypt(), "", true, _Transaction); //获得类别对应的某一个类别下面的数据 foreach (Business.SERVERPART _SERVERPART in (new Business.SERVERPART(_Transaction)).FillCollection( "where SERVERPART_ID in (select SERVERPART_ID from HIGHWAY_STORAGE.T_SPSTATICTYPE where SERVERPARTTYPE_ID IN( " + OutTypeString + ")) order by SERVERPART_INDEX,SERVERPART_CODE")) { DataRow _DataRow = _DataTablePoint.NewRow(); _DataRow["MAPINFO"] = _SERVERPART.SERVERPART_NAME + " " + _SERVERPART.SERVERPART_INFO; _DataRow["MAP_ADDRESS"] = _SERVERPART.SERVERPART_ADDRESS; _DataRow["MAP_TEL"] = _SERVERPART.SERVERPART_TEL; _DataRow["MAP_OBJECTCODE"] = _SERVERPART.SERVERPART_ID_Encrypt; if (_SERVERPART.SERVERPART_X == null) continue; _DataRow["MAPX"] = _SERVERPART.SERVERPART_X; _DataRow["MAPY"] = _SERVERPART.SERVERPART_Y; //获得图片的路径 string sqlString = @"SELECT * FROM (select A.IMAGE_PATH FROM T_IMAGE A,T_SERVERPARTTYPE B,T_SPSTATICTYPE C WHERE A.IMAGE_ID = B.STATIC_TYPE AND B.SERVERPARTTYPE_ID = C.SERVERPARTTYPE_ID AND C.SERVERPART_ID=" + _SERVERPART.SERVERPART_ID + " AND C.SERVERPARTTYPE_ID IN( " + OutTypeString + ") ORDER BY C.SERVERPARTTYPE_ID DESC) where ROWNUM =1"; DataTable _DataTableNew = (new Business.SERVERPART(_Transaction)).ExecuteDataTable(sqlString); if (_DataTableNew.Rows.Count != 0) { _DataRow["IMAGEPATH"] = _DataTableNew.Rows[0][0].ToString(); } else { _DataRow["IMAGEPATH"] = ""; } _DataTablePoint.Rows.Add(_DataRow); } string PointString = string.Empty; PointString = BaiDuMapHelper.GetBaiduPointString(PointString, _DataTablePoint); //string imagePath = ""; //try //{ // imagePath = HttpContext.Current.Request.Form["imagepath"]; //} //catch //{ } //PointString = PointString.Replace("isOpen: 0", "isOpen: 0,imagepath:\"" + imagePath + "\""); reString = PointString;//"{\"markerArr\":[" + PointString + "]}"; } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 根据查询内容获取点位 case "getPointPosByServerPartName": string Parameter = action_data; Parameter = "WHERE SERVERPART_NAME LIKE '%" + Parameter + "%' OR SERVERPART_ADDRESS LIKE '%" + Parameter + "%' OR EXPRESSWAY_NAME LIKE '%" + Parameter + "%' OR OWNEDCOMPANY LIKE '%" + Parameter + "%' OR MANAGERCOMPANY LIKE '%" + Parameter + "%'"; _DataTablePoint = new DataTable(); _DataTablePoint.Columns.Add("MAPINFO"); _DataTablePoint.Columns.Add("MAP_ADDRESS"); _DataTablePoint.Columns.Add("MAP_TEL"); _DataTablePoint.Columns.Add("MAP_OBJECTCODE"); _DataTablePoint.Columns.Add("MAPX"); _DataTablePoint.Columns.Add("MAPY"); _DataTablePoint.Columns.Add("IMAGEPATH"); try { //获得类别对应的某一个类别下面的数据 foreach (Business.SERVERPART _SERVERPART in (new Business.SERVERPART(_Transaction)).FillCollection(Parameter)) { DataRow _DataRow = _DataTablePoint.NewRow(); _DataRow["MAPINFO"] = _SERVERPART.SERVERPART_NAME + " " + _SERVERPART.SERVERPART_INFO; _DataRow["MAP_ADDRESS"] = _SERVERPART.SERVERPART_ADDRESS; _DataRow["MAP_TEL"] = _SERVERPART.SERVERPART_TEL; _DataRow["MAP_OBJECTCODE"] = _SERVERPART.SERVERPART_ID_Encrypt; if (_SERVERPART.SERVERPART_X == null) continue; _DataRow["MAPX"] = _SERVERPART.SERVERPART_X; _DataRow["MAPY"] = _SERVERPART.SERVERPART_Y; _DataTablePoint.Rows.Add(_DataRow); } string PointString = string.Empty; PointString = BaiDuMapHelper.GetBaiduPointString(PointString, _DataTablePoint); reString = PointString;//"{\"markerArr\":[" + PointString + "]}"; } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获得设置图片的信息 case "getImageDataObject": try { List _ListImageObject = new List(); StrSql = "SELECT * FROM (SELECT IMAGE_PATH,TABLE_ID FROM HIGHWAY_STORAGE.T_IMAGE WHERE TABLE_ID = " + action_data.ToDecrypt() + " AND TABLE_NAME='T_SERVERPART' ORDER BY IMAGE_INDEX ASC) WHERE ROWNUM = 1"; //图片信息 foreach (DataRow _DataRow in (new Business.COMMODITY(_Transaction)).ExecuteDataTable(StrSql).Rows) { ImageObject _ImageObject = new ImageObject(); _ImageObject.path = _DataRow["IMAGE_PATH"].ToString(); _ImageObject.code = _DataRow["TABLE_ID"].ToEncrypt(); _ListImageObject.Add(_ImageObject); } reString = JsonHelper.ListToJson(_ListImageObject, "ImageObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获得一品多码商品 case "getCommodityDetail": try { //获得枚举信息 _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_STORAGE.V_RTMULTIBARCODE where MULTIBARCODE_ID = " + action_data.ToDecrypt())); reString = JsonHelper.DataTableToJson(_DataTable, "COMMODITY"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获得商品销售排行 case "getServerpartCommoditySale": try { string COMMODITY_BARCODE = action_data.ToDecrypt().Split('&')[0]; string StatisticStart_Date = action_data.ToDecrypt().Split('&')[1]; string StatisticEnd_Date = action_data.ToDecrypt().Split('&')[2]; //string SERVERPARTTYPE_ID = action_data.ToDecrypt().Split('&')[3]; string SERVERPART_ID = action_data.ToDecrypt().Split('&')[3]; string SHOPTRADE = action_data.ToDecrypt().Split('&')[4]; string SERVERPARTSHOP_ID = action_data.ToDecrypt().Split('&')[5]; string ROWNUMS = action_data.ToDecrypt().Split('&')[6]; string rowWhere = ""; //获得枚举信息 string WhereSQL = string.Empty; string OtherSQL = string.Empty; if (COMMODITY_BARCODE != "") { WhereSQL += " AND COMMODITY_BARCODE = '" + COMMODITY_BARCODE + "'"; OtherSQL += " AND COMMODITY_BARCODE = '" + COMMODITY_BARCODE + "'"; } if (StatisticStart_Date != "") { WhereSQL += " AND SELLDATE >= TO_DATE('" + StatisticStart_Date + "','YYYY/MM/DD')"; OtherSQL += " AND SELLDATE >= TO_DATE('" + StatisticStart_Date + "','YYYY/MM/DD')"; } if (StatisticEnd_Date != "") { WhereSQL += " AND SELLDATE <= TO_DATE('" + StatisticEnd_Date + "','YYYY/MM/DD')"; OtherSQL += " AND SELLDATE <= TO_DATE('" + StatisticEnd_Date + "','YYYY/MM/DD')"; } //if (!string.IsNullOrEmpty(SERVERPARTTYPE_ID)) //{ // WhereSQL += "AND SERVERPARTTYPE_ID IN(" + SERVERPARTTYPE_ID + ")"; //} if (!string.IsNullOrEmpty(SERVERPART_ID)) { WhereSQL += "AND SERVERPART_ID IN(" + SERVERPART_ID + ")"; OtherSQL += "AND SERVERPART_ID IN(" + SERVERPART_ID + ")"; } if (!string.IsNullOrEmpty(SERVERPARTSHOP_ID)) { WhereSQL += "AND SERVERPARTSHOP_ID IN(" + SERVERPARTSHOP_ID + ")"; OtherSQL += "AND SERVERPARTSHOP_ID IN(" + SERVERPARTSHOP_ID + ")"; } if (!string.IsNullOrEmpty(SHOPTRADE) && SHOPTRADE != "1=1") { WhereSQL += "AND BUSINESSTYPE = " + SHOPTRADE; OtherSQL += "AND BUSINESSTYPE = " + SHOPTRADE; } if (ROWNUMS != "全部") { rowWhere = " WHERE ROWNUM <= " + ROWNUMS; } string strSql = @"SELECT ROWNUM,SERVERPART_ID,SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPNAME,TOTALSELLAMOUNT,TOTALCOUNT FROM (SELECT SERVERPART_ID,SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPNAME,MAX(COMMODITY_NAME) AS COMMODITY_NAME, COMMODITY_BARCODE,MAX(COMMODITY_RULE) AS COMMODITY_RULE,MAX(COMMODITY_UNIT) AS COMMODITY_UNIT, SUM(TOTALSELLAMOUNT) AS TOTALSELLAMOUNT,SUM(TOTALCOUNT) AS TOTALCOUNT FROM HIGHWAY_SELLDATA.V_SALERANKS WHERE 1=1 " + WhereSQL + " GROUP BY SERVERPART_ID,SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPNAME,COMMODITY_BARCODE" + " ORDER BY TOTALSELLAMOUNT DESC) " + rowWhere; _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable(strSql); int ROWNUM = 0; foreach (DataRow dr in _DataTable.Rows) { ROWNUM = int.Parse(dr["ROWNUM"].ToString()); SERVERPART_ID += (SERVERPART_ID == "" ? "" : ",") + dr["SERVERPART_ID"].ToString(); SERVERPARTSHOP_ID += (SERVERPARTSHOP_ID == "" ? "" : ",") + dr["SERVERPARTSHOP_ID"].ToString(); } string otherSql = @"SELECT ROWNUMS,SERVERPART_ID,SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPNAME, SUM(TOTALSELLAMOUNT) AS TOTALSELLAMOUNT,SUM(TOTALCOUNT) AS TOTALCOUNT FROM (SELECT 0 AS ROWNUMS,1 AS SERVERPART_ID,'其他服务区' as SERVERPART_NAME, 1 AS SERVERPARTSHOP_ID,'其他门店' AS SHOPNAME, COMMODITY_CODE,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_RULE, COMMODITY_UNIT,SUM(TOTALSELLAMOUNT) AS TOTALSELLAMOUNT,SUM(TOTALCOUNT) AS TOTALCOUNT FROM HIGHWAY_SELLDATA.V_SALERANKS WHERE 1=1 " + OtherSQL + " AND SERVERPART_ID NOT IN (" + SERVERPART_ID + ")" + " AND SERVERPARTSHOP_ID NOT IN (" + SERVERPARTSHOP_ID + ")" + " GROUP BY SERVERPART_ID,SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPNAME,COMMODITY_ID,COMMODITY_CODE," + "COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_RULE,COMMODITY_UNIT,COMMODITY_CURRPRICE" + " ORDER BY TOTALSELLAMOUNT DESC) GROUP BY ROWNUMS,SERVERPART_ID,SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPNAME"; foreach (DataRow dr in new Business.COMMODITY(_Transaction).ExecuteDataTable(otherSql).Rows) { DataRow _DataRow = _DataTable.NewRow(); for (int rownum = 0; rownum < dr.ItemArray.Length; rownum++) { _DataRow[rownum] = dr[rownum]; } _DataRow[0] = (ROWNUM + 1).ToString(); _DataTable.Rows.Add(_DataRow); } reString = JsonHelper.DataTableToJson(_DataTable, "SALERANKS"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getSaleRanks": try { string COMMODITY_BARCODE = action_data.ToDecrypt().Split('&')[0]; string StatisticStart_Date = action_data.ToDecrypt().Split('&')[1]; string StatisticEnd_Date = action_data.ToDecrypt().Split('&')[2]; //string SERVERPARTTYPE_ID = action_data.ToDecrypt().Split('&')[3]; string SERVERPART_ID = action_data.ToDecrypt().Split('&')[3]; string SHOPTRADE = action_data.ToDecrypt().Split('&')[4]; string SERVERPARTSHOP_ID = action_data.ToDecrypt().Split('&')[5]; string ROWNUMS = action_data.ToDecrypt().Split('&')[6]; string rowWhere = ""; //获得枚举信息 string WhereSQL = string.Empty; string OtherSQL = string.Empty; if (COMMODITY_BARCODE != "") { WhereSQL += " AND COMMODITY_BARCODE = '" + COMMODITY_BARCODE + "'"; OtherSQL += " AND COMMODITY_BARCODE = '" + COMMODITY_BARCODE + "'"; } if (StatisticStart_Date != "") { WhereSQL += " AND SELLDATE >= TO_DATE('" + StatisticStart_Date + "','YYYY/MM/DD')"; OtherSQL += " AND SELLDATE >= TO_DATE('" + StatisticStart_Date + "','YYYY/MM/DD')"; } if (StatisticEnd_Date != "") { WhereSQL += " AND SELLDATE <= TO_DATE('" + StatisticEnd_Date + "','YYYY/MM/DD')"; OtherSQL += " AND SELLDATE <= TO_DATE('" + StatisticEnd_Date + "','YYYY/MM/DD')"; } //if (!string.IsNullOrEmpty(SERVERPARTTYPE_ID)) //{ // WhereSQL += "AND SERVERPARTTYPE_ID IN(" + SERVERPARTTYPE_ID + ")"; //} if (!string.IsNullOrEmpty(SERVERPART_ID)) { WhereSQL += " AND SERVERPART_ID IN(" + SERVERPART_ID + ")"; OtherSQL += " AND SERVERPART_ID IN(" + SERVERPART_ID + ")"; } if (!string.IsNullOrEmpty(SERVERPARTSHOP_ID)) { WhereSQL += " AND " + SERVERPARTSHOP_ID; OtherSQL += " AND " + SERVERPARTSHOP_ID; } if (!string.IsNullOrEmpty(SHOPTRADE) && SHOPTRADE != "1=1") { WhereSQL += " AND BUSINESSTYPE = " + SHOPTRADE; OtherSQL += " AND BUSINESSTYPE = " + SHOPTRADE; } if (ROWNUMS != "全部") { rowWhere = " WHERE ROWNUM <= " + ROWNUMS; } string strSql = string.Format(@"SELECT ROWNUM,SERVERPARTSHOP_ID, SERVERPART_NAME,SHOPNAME,TOTALSELLAMOUNT,TOTALCOUNT FROM (SELECT SERVERPART_NAME,SHOPNAME,SERVERPARTSHOP_ID, MAX(COMMODITY_NAME) AS COMMODITY_NAME,COMMODITY_BARCODE, MAX(COMMODITY_RULE) AS COMMODITY_RULE,MAX(COMMODITY_UNIT) AS COMMODITY_UNIT, SUM(TOTALSELLAMOUNT) AS TOTALSELLAMOUNT,SUM(TOTALCOUNT) AS TOTALCOUNT FROM HIGHWAY_SELLDATA.V_SALERANKS WHERE 1 = 1 {0} GROUP BY SERVERPARTSHOP_ID,SERVERPART_NAME,SHOPNAME,COMMODITY_BARCODE ORDER BY TOTALSELLAMOUNT DESC) {1}", WhereSQL, rowWhere); _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable(strSql); int ROWNUM = 0; SERVERPARTSHOP_ID = ""; //初始化门店内码 foreach (DataRow dr in _DataTable.Rows) { ROWNUM = int.Parse(dr["ROWNUM"].ToString()); SERVERPARTSHOP_ID += (SERVERPARTSHOP_ID == "" ? "" : ",") + dr["SERVERPARTSHOP_ID"].ToString(); } if (!string.IsNullOrWhiteSpace(SERVERPARTSHOP_ID)) { string otherSql = string.Format(@"SELECT '其他服务区' as SERVERPART_NAME,'其他门店' AS SHOPNAME, SUM(TOTALSELLAMOUNT) AS TOTALSELLAMOUNT,SUM(TOTALCOUNT) AS TOTALCOUNT FROM HIGHWAY_SELLDATA.V_SALERANKS WHERE 1 = 1 {0} AND SERVERPARTSHOP_ID NOT IN ({1})", OtherSQL, SERVERPARTSHOP_ID); DataTable dtOtherData = new Business.COMMODITY(_Transaction).ExecuteDataTable(otherSql); if (dtOtherData.Rows.Count > 0 && dtOtherData.Rows[0][3].ToString() != "") { DataRow _DataRow = _DataTable.NewRow(); _DataRow["SERVERPART_NAME"] = dtOtherData.Rows[0]["SERVERPART_NAME"]; _DataRow["SHOPNAME"] = dtOtherData.Rows[0]["SHOPNAME"]; _DataRow["TOTALSELLAMOUNT"] = dtOtherData.Rows[0]["TOTALSELLAMOUNT"]; _DataRow["TOTALCOUNT"] = dtOtherData.Rows[0]["TOTALCOUNT"]; _DataRow[0] = (ROWNUM + 1).ToString(); _DataTable.Rows.Add(_DataRow); } } reString = JsonHelper.DataTableToJson(_DataTable, "SALERANKS"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获得服务区商品销售排行 case "getCommoditySale": try { string COMMODITY_ID = action_data.ToDecrypt().Split('&')[0]; string StatisticsStartDay = action_data.ToDecrypt().Split('&')[1]; string StatisticsEndDay = action_data.ToDecrypt().Split('&')[2]; //获得枚举信息 string WhereSQL = string.Empty; string SERVERPART_ID = string.Empty; if (COMMODITY_ID != "") { WhereSQL += " AND COMMODITY_ID = " + COMMODITY_ID; } if (StatisticsStartDay != "") { WhereSQL += " AND ENDDATE >= TO_DATE('" + StatisticsStartDay + "','YYYY/MM/DD')"; } if (StatisticsEndDay != "") { WhereSQL += " AND ENDDATE < TO_DATE('" + StatisticsEndDay + "','YYYY/MM/DD') + 1"; } _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable( @"SELECT ROWNUM,SERVERPART_ID,SERVERPART_NAME,TICKETCOUNT,TOTALCOUNT,CASHPAY FROM ( SELECT SERVERPART_ID,NVL(SUM(TICKETCOUNT),0) AS TICKETCOUNT,NVL(SUM(TOTALCOUNT),0) AS TOTALCOUNT, SERVERPART_NAME,NVL(SUM(TOTALSELLAMOUNT),0) AS CASHPAY FROM HIGHWAY_SELLDATA.V_COMMODITYSALE WHERE 1 = 1" + WhereSQL + " GROUP BY SERVERPART_ID,SERVERPART_NAME ORDER BY TOTALCOUNT DESC)WHERE ROWNUM <= 10"); int ROWNUM = 0; foreach (DataRow dr in _DataTable.Rows) { ROWNUM = int.Parse(dr["ROWNUM"].ToString()); SERVERPART_ID += (SERVERPART_ID == "" ? "" : ",") + dr["SERVERPART_ID"].ToString(); } foreach (DataRow dr in new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT 1,0,'其它服务区合计',NVL(SUM(TICKETCOUNT),0) AS TICKETCOUNT,NVL(SUM(TOTALCOUNT),0) AS TOTALCOUNT, NVL(SUM(TOTALSELLAMOUNT),0) AS CASHPAY FROM HIGHWAY_SELLDATA.V_COMMODITYSALE WHERE 1 = 1" + WhereSQL + " AND SERVERPART_ID NOT IN (" + SERVERPART_ID + ")").Rows) { DataRow _DataRow = _DataTable.NewRow(); for (int rownum = 0; rownum < dr.ItemArray.Length; rownum++) { _DataRow[rownum] = dr[rownum]; } _DataRow[0] = (ROWNUM + 1).ToString(); _DataTable.Rows.Add(_DataRow); } reString = JsonHelper.DataTableToJson(_DataTable, "DAILYSALES"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getCommoditySaleByName": try { string COMMODITY_NAME = action_data.Split('|')[0]; string StatisticsStartDay = action_data.Split('|')[1]; string StatisticsEndDay = action_data.Split('|')[2]; string province_code = HttpContext.Current.Request.Form["province_code"]; //获得枚举信息 string WhereSQL = string.Empty; string SERVERPART_ID = string.Empty; if (string.IsNullOrEmpty(province_code)) { province_code = HttpContext.Current.Request.Params["province_code"]; if (!string.IsNullOrEmpty(province_code)) { FieldExplain _FieldExplain = new FieldExplain(_Transaction); _FieldExplain.AddSearchParameter("FIELDEXPLAIN_FIELD", "DIVISION_CODE"); if (_FieldExplain.Search()) { FieldEnum _FieldEnum = new FieldEnum(_Transaction); _FieldEnum.AddSearchParameter("FieldExplain_ID", _FieldExplain.FieldExplain_ID); _FieldEnum.AddSearchParameter("FIELDENUM_VALUE", province_code); if (_FieldEnum.Search()) { WhereSQL += " AND PROVINCE_CODE = '" + _FieldEnum.FieldEnum_ID + "'"; } } } } if (COMMODITY_NAME != "") { WhereSQL += " AND COMMODITY_NAME = '" + COMMODITY_NAME + "'"; } if (StatisticsStartDay != "") { WhereSQL += " AND ENDDATE >= TO_DATE('" + StatisticsStartDay + "','YYYY/MM/DD')"; } if (StatisticsEndDay != "") { WhereSQL += " AND ENDDATE < TO_DATE('" + StatisticsEndDay + "','YYYY/MM/DD') + 1"; } _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable( @"SELECT ROWNUM,SERVERPART_ID,SERVERPART_NAME,TOTALCOUNT,CASHPAY FROM ( SELECT SERVERPART_ID,NVL(SUM(TOTALCOUNT),0) AS TOTALCOUNT, SERVERPART_NAME,NVL(SUM(TOTALSELLAMOUNT),0) AS CASHPAY FROM HIGHWAY_SELLDATA.V_COMMODITYSALE WHERE 1 = 1" + WhereSQL + " GROUP BY SERVERPART_ID,SERVERPART_NAME ORDER BY TOTALCOUNT DESC) WHERE ROWNUM <= 10"); int ROWNUM = 0; foreach (DataRow dr in _DataTable.Rows) { ROWNUM = int.Parse(dr["ROWNUM"].ToString()); SERVERPART_ID += (SERVERPART_ID == "" ? "" : ",") + dr["SERVERPART_ID"].ToString(); } foreach (DataRow dr in new Business.COMMODITY(_Transaction).ExecuteDataTable(@"SELECT 1,0,'其它服务区合计',NVL(SUM(TOTALCOUNT),0) AS TOTALCOUNT,NVL(SUM(TOTALSELLAMOUNT),0) AS CASHPAY FROM HIGHWAY_SELLDATA.V_COMMODITYSALE WHERE 1 = 1" + WhereSQL + " AND SERVERPART_ID NOT IN (" + (string.IsNullOrEmpty(SERVERPART_ID) ? "0" : SERVERPART_ID) + ")").Rows) { DataRow _DataRow = _DataTable.NewRow(); for (int rownum = 0; rownum < dr.ItemArray.Length; rownum++) { _DataRow[rownum] = dr[rownum]; } _DataRow[0] = (ROWNUM + 1).ToString(); _DataTable.Rows.Add(_DataRow); } reString = JsonHelper.DataTableToJson(_DataTable, "DAILYSALES"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取门店结账信息 case "GetEndaccount": try { DataTable ENDACCOUNTList = new SRHSB.ENDACCOUNT(_Transaction).FillDataTable( "WHERE SERVERPART_ID = " + action_data.Split(',')[0].ToDecrypt() + " AND SHOPCODE = '" + action_data.Split(',')[1].ToDecrypt() + "' AND MACHINECODE = '" + action_data.Split(',')[2].ToDecrypt() + "' AND ENDACCOUNT_STARTDATE = TO_DATE('" + action_data.Split(',')[3].ToDecrypt() + "','YYYY/MM/DD HH24:MI:SS')"); foreach (DataRow _ENDACCOUNTRow in ENDACCOUNTList.Rows) { if (string.IsNullOrWhiteSpace(_ENDACCOUNTRow["FACT_AMOUNT"].ToString())) { _ENDACCOUNTRow["FACT_AMOUNT"] = (_ENDACCOUNTRow["TICKETBILL"].TryParseToDouble() + _ENDACCOUNTRow["OTHERPAY"].TryParseToDouble()).Round(2); } } reString = JsonHelper.DataTableToJson(ENDACCOUNTList, "EndaccountObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getServerPartEndaccount": try { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_SELLDATA.V_ENDACCOUNT WHERE SERVERPARTSHOP_ID IN (" + action_data + ") AND ENDACCOUNT_ID > 0 ORDER BY ENDACCOUNT_DATE")); reString = JsonHelper.DataTableToJson(_DataTable, "EndaccountObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "getServerPartEndaccountByDate": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_SELLDATA.V_ENDACCOUNT WHERE SERVERPARTSHOP_ID IN (" + action_data + ") " + (string.IsNullOrEmpty(action_record) ? "" : " AND TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) = TO_DATE('" + action_record.Split(' ')[0] + "','YYYY/MM/DD')") + " ORDER BY ENDACCOUNT_DATE")); reString = JsonHelper.DataTableToJson(_DataTable, "EndaccountObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountTotalAmount": try { if (string.IsNullOrEmpty(action_data)) { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT NVL(CASHPAY,0) AS TOTALPRICE,TICKETCOUNT AS TOTALTICKET,TOTALCOUNT AS CURSELLCOUNT FROM HIGHWAY_SELLDATA.V_SERVERPARTDAILYSALES WHERE SERVERPART_NAME = '合计' AND TRUNC(STATISTICS_DATE) = TRUNC(SYSDATE) - 1")); } else { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT TRUNC(STATISTICS_DATE) AS STATISTICS_DATE,SUM(NVL(CASHPAY,0)) AS TOTALPRICE, SUM(TICKETCOUNT) AS TOTALTICKET,SUM(TOTALCOUNT) AS CURSELLCOUNT FROM HIGHWAY_SELLDATA.V_SERVERPARTDAILYSALES WHERE SERVERPART_NAME = '合计' AND TRUNC(STATISTICS_DATE) BETWEEN TO_DATE('" + action_data.Split(',')[0].Split(' ')[0] + "','YYYY/MM/DD') AND TO_DATE('" + action_data.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD') GROUP BY TRUNC(STATISTICS_DATE) ORDER BY STATISTICS_DATE")); } reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountAmount": try { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT * FROM HIGHWAY_SELLDATA.V_SERVERPARTDAILYSALES WHERE SERVERPART_NAME <> '合计' AND TRUNC(STATISTICS_DATE) = TRUNC(SYSDATE) - 1 ORDER BY CASHPAY DESC")); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountByRegion": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } if (string.IsNullOrEmpty(action_data)) { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT SUM(CASHPAY) AS TOTALPRICE,SUM(TICKETCOUNT) AS TOTALTICKET, SUM(TOTALCOUNT) AS CURSELLCOUNT,C.TYPE_NAME FROM HIGHWAY_SELLDATA.V_SERVERPARTDAILYSALES A, HIGHWAY_STORAGE.T_SPSTATICTYPE B, HIGHWAY_STORAGE.T_SERVERPARTTYPE C WHERE TRUNC(STATISTICS_DATE) = TRUNC(SYSDATE) - 1 AND A.SERVERPART_ID = B.SERVERPART_ID AND B.SERVERPARTTYPE_ID = C.SERVERPARTTYPE_ID AND C.TYPE_PID = 1 GROUP BY C.TYPE_NAME")); } else { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT SUM(CASHPAY) AS TOTALPRICE,SUM(TICKETCOUNT) AS TOTALTICKET, SUM(TOTALCOUNT) AS CURSELLCOUNT,TRUNC(STATISTICS_DATE) AS STATISTICS_DATE FROM HIGHWAY_SELLDATA.V_SERVERPARTDAILYSALES A, HIGHWAY_STORAGE.V_REGIONSERVERPART B WHERE A.SERVERPART_ID = B.SERVERPART_ID AND REGION_ID = " + action_data + " AND TRUNC(STATISTICS_DATE) BETWEEN TO_DATE('" + action_record.Split(',')[0].Split(' ')[0] + "','YYYY/MM/DD') AND TO_DATE('" + action_record.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD') GROUP BY TRUNC(STATISTICS_DATE) ORDER BY STATISTICS_DATE")); } reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountByBusiness": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT SUM(CASHPAY) AS TOTALPRICE,SUM(TICKETCOUNT) AS TOTALTICKET, SUM(TOTALCOUNT) AS CURSELLCOUNT,NVL(STATISTICS_DATE,ENDACCOUNT_DATE) AS STATISTICS_DATE FROM HIGHWAY_SELLDATA.V_ENDACCOUNT A WHERE SERVERPARTSHOP_ID IN (" + action_data + ") AND NVL(STATISTICS_DATE,ENDACCOUNT_DATE) BETWEEN TO_DATE('" + action_record.Split(',')[0].Split(' ')[0] + "','YYYY/MM/DD') AND TO_DATE('" + action_record.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD') AND SERVERPART_ID = " + action_record.Split(',')[2] + " GROUP BY NVL(STATISTICS_DATE,ENDACCOUNT_DATE) ORDER BY STATISTICS_DATE")); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountByBusinessType": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } if (string.IsNullOrEmpty(action_record)) { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable(string.Format( @"SELECT SUM(CASHPAY) AS TOTALPRICE,SUM(TICKETCOUNT) AS TOTALTICKET,SUM(TOTALCOUNT) AS CURSELLCOUNT, CASE WHEN SHOPTRADE = 1000 THEN 'MARKET' WHEN SHOPTRADE LIKE '2%' THEN 'SNACK' WHEN SHOPTRADE LIKE '3%' THEN 'RESTAURANT' ELSE 'OTHER' END AS SHOPTRADE FROM HIGHWAY_SELLDATA.V_ENDACCOUNT WHERE TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) = TRUNC(SYSDATE) - 1 AND PROVINCE_CODE = {0} GROUP BY CASE WHEN SHOPTRADE = 1000 THEN 'MARKET' WHEN SHOPTRADE LIKE '2%' THEN 'SNACK' WHEN SHOPTRADE LIKE '3%' THEN 'RESTAURANT' ELSE 'OTHER' END", _PROVINCE_CODE))); } else { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable(string.Format( @"SELECT SUM(CASHPAY) AS TOTALPRICE,SUM(TICKETCOUNT) AS TOTALTICKET,SUM(TOTALCOUNT) AS CURSELLCOUNT, CASE WHEN SHOPTRADE = 1000 THEN 'MARKET' WHEN SHOPTRADE LIKE '2%' THEN 'SNACK' WHEN SHOPTRADE LIKE '3%' THEN 'RESTAURANT' ELSE 'OTHER' END AS SHOPTRADE FROM HIGHWAY_SELLDATA.V_ENDACCOUNT A,HIGHWAY_STORAGE.V_REGIONSERVERPART B WHERE A.SERVERPART_ID = B.SERVERPART_ID {0} AND TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) BETWEEN TO_DATE('{1}','YYYY/MM/DD') AND TO_DATE('{2}','YYYY/MM/DD') AND B.PROVINCE_CODE = {3} GROUP BY CASE WHEN SHOPTRADE = 1000 THEN 'MARKET' WHEN SHOPTRADE LIKE '2%' THEN 'SNACK' WHEN SHOPTRADE LIKE '3%' THEN 'RESTAURANT' ELSE 'OTHER' END", (string.IsNullOrEmpty(action_data) ? "" : " AND B.REGION_ID = " + action_data), action_record.Split(',')[0].Split(' ')[0], action_record.Split(',')[1].Split(' ')[0], _PROVINCE_CODE))); } reString = JsonHelper.DataTableToJson(_DataTable, "Endaccount"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountByType": try { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT SUM(CASHPAY) AS TOTALPRICE,SUM(TICKETCOUNT) AS TOTALTICKET, SUM(TOTALCOUNT) AS CURSELLCOUNT,TYPE_NAME,TYPE_INDEX FROM HIGHWAY_SELLDATA.V_SERVERPARTDAILYSALES A, HIGHWAY_STORAGE.V_SERVERPARTBYTYPE B WHERE A.SERVERPART_ID = B.SERVERPART_ID AND TRUNC(STATISTICS_DATE) BETWEEN TO_DATE('" + action_data.Split(',')[0].Split(' ')[0] + "','YYYY/MM/DD') AND TO_DATE('" + action_data.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD') GROUP BY TYPE_NAME,TYPE_INDEX ORDER BY TYPE_INDEX")); reString = JsonHelper.DataTableToJson(_DataTable, "Endaccount"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取会员码消费信息 case "GetWechatInfo": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } string url = "https://api.weixin.qq.com/sns/oauth2/access_token?appid=" + AppID + "&secret=" + AppSecret + "&code=" + action_data + "&grant_type=authorization_code"; List _WechatInfoObjectObject = new List(); WechatInfoObject _WechatInfoObject = new WechatInfoObject(); //根据url创建HttpWebRequest对象 HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url); objRequest.Method = "get"; //读取服务器返回信息 HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse(); //using作为语句，用于定义一个范围，在此范围的末尾将释放对象 using (StreamReader sr = new StreamReader(objResponse.GetResponseStream())) { //ReadToEnd适用于小文件的读取，一次性的返回整个文件 NJL.JObject _JObject = (NJL.JObject)NJ.JsonConvert.DeserializeObject(sr.ReadToEnd()); try { _WechatInfoObject.WECHAT_OPENID = _JObject["openid"].ToString(); if (string.IsNullOrEmpty(action_record)) { USERMATCHING _USERMATCHING = new USERMATCHING(_Transaction); _USERMATCHING.AddSearchParameter("WECHAT_OPENID", _JObject["openid"].ToString()); if (_USERMATCHING.Search()) { _WechatInfoObject.RTWECHATPUSH_ID = _USERMATCHING.MEMBERSHIP_ID.ToString(); _WechatInfoObject.LINE_NUMBER = "1"; MACHINEINFO _MACHINEINFO = new MACHINEINFO(_Transaction); _DataTable = _MACHINEINFO.ExecuteDataTable( @"SELECT MAX(SUBSTR(TICKETCODE,LENGTH(TICKETCODE) - 5,2)) FROM HIGHWAY_EXCHANGE.T_OPERATELOG WHERE MEMBERSHIP_ID = " + _USERMATCHING.MEMBERSHIP_ID + " AND TRUNC(RECODE_DATE) = TRUNC(SYSDATE)"); if (_DataTable.Rows.Count > 0 && _DataTable.Rows[0][0].ToString() != "") { _WechatInfoObject.LINE_NUMBER = (int.Parse(_DataTable.Rows[0][0].ToString()) + 1).ToString(); } } } else { if (action_record.Split('|')[0] == "ScanAudit") { Business.RTWECHATPUSH _RTWECHATPUSH = new Business.RTWECHATPUSH(_Transaction); _RTWECHATPUSH.AddSearchParameter("WECHAT_OPENID", _JObject["openid"].ToString()); if (_RTWECHATPUSH.Search()) { if (_RTWECHATPUSH.PUSH_MODULE.Contains("W2VClFsAnvma-1412lx-fCxd_wWyxfN293TXAugG76c")) { _WechatInfoObject.RTWECHATPUSH_ID = _RTWECHATPUSH.RTWECHATPUSH_ID.ToString(); _WechatInfoObject.LINE_NUMBER = _RTWECHATPUSH.USER_NAME; } } } } } catch (Exception ex) { SuperMap.RealEstate.Utility.ErrorLogHelper.Write(ex); } sr.Close(); _WechatInfoObjectObject.Add(_WechatInfoObject); reString = JsonHelper.ListToJson(_WechatInfoObjectObject, "WechatInfoObject"); } } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetMembershipConsumption": try { action_record = HttpContext.Current.Request.Params["action_record"]; _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable(@"SELECT B.SERVERPART_NAME,C.SHOPNAME,A.RECODE_AMOUNT FROM T_OPERATELOG@NEWEX A,HIGHWAY_STORAGE.T_SERVERPART B,HIGHWAY_STORAGE.T_SERVERPARTSHOP C WHERE A.SERVERPARTCODE = B.SERVERPART_CODE AND A.SHOPCODE = C.SHOPCODE AND B.SERVERPART_ID = C.SERVERPART_ID AND A.MEMBERSHIP_ID = " + action_data + " AND SUBSTR(TICKETCODE,LENGTH(TICKETCODE) - 5,2) = '" + action_record.Split(',')[1] + "' AND TRUNC(RECODE_DATE) = TO_DATE('" + action_record.Split(',')[0] + "','YYYY/MM/DD')"); reString = JsonHelper.DataTableToJson(_DataTable, "MembershipConsumption"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetMembershipConsumptionList": try { action_record = HttpContext.Current.Request.Params["action_record"]; _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable( "SELECT * FROM MEMBERSHIP_STORAGE.V_RECHARGERECORD WHERE MEMBERSHIP_ID = " + action_data + (action_record == "1000" ? " AND RECODE_TYPE IN (1000,1010)" : (action_record == "3000" ? "RECODE_TYPE IN (2000,2010,3000,3010)" : " AND RECODE_TYPE NOT IN (1020,1030) AND RECODE_TYPE < 4000")) + " ORDER BY RECODE_DATE DESC"); reString = JsonHelper.DataTableToJson(_DataTable, "MembershipConsumption"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetMembershipInfo": try { action_record = HttpContext.Current.Request.Params["action_record"]; _DataTable = new MACHINEINFO(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_EXCHANGE.T_MEMBERSHIP WHERE MEMBERSHIP_ID = " + action_data); reString = JsonHelper.DataTableToJson(_DataTable, "MEMBERSHIP"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取短信验证码 case "SearchSMSIdentityCode": try { action_record = HttpContext.Current.Request.Params["action_record"]; _DataTable = new MACHINEINFO(_Transaction).ExecuteDataTable( "SELECT 1 FROM HIGHWAY_EXCHANGE.T_SMSIDENTITY WHERE CELLPHONE_NUMBER = '" + action_data + "' AND IDENTITY_DATE > SYSDATE - 5/1440 AND IDENTITY_CODE = '" + action_record + "'"); reString = JsonHelper.DataTableToJson(_DataTable, "SMSIdentity"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetSMSIdentityCode": try { string querys = "ParamString={0}&RecNum=" + action_data + "&SignName=" + HttpUtility.UrlEncode("驿商科技") + "&TemplateCode=SMS_65555007"; string bodys = "", host = "http://sms.market.alicloudapi.com"; string url = host + "/singleSendSms"; HttpWebRequest httpRequest = null; HttpWebResponse httpResponse = null; string _IDENTITY_CODE = CreateRandomCode(6); querys = string.Format(querys, System.Web.HttpUtility.UrlEncode("{\"code\":\"" + _IDENTITY_CODE + "\",\"product\":\"骋客\"}")); if (0 < querys.Length) { url = url + "?" + querys; } if (host.Contains("https://")) { ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CheckValidationResult); httpRequest = (HttpWebRequest)WebRequest.CreateDefault(new Uri(url)); } else { httpRequest = (HttpWebRequest)WebRequest.Create(url); } httpRequest.Method = "get"; httpRequest.Headers.Add("Authorization", "APPCODE " + "99f7d21d35e647e98706f75109f4b655"); if (0 < bodys.Length) { byte[] data = Encoding.UTF8.GetBytes(bodys); using (Stream stream = httpRequest.GetRequestStream()) { stream.Write(data, 0, data.Length); } } try { httpResponse = (HttpWebResponse)httpRequest.GetResponse(); //using作为语句，用于定义一个范围，在此范围的末尾将释放对象 using (StreamReader sr = new StreamReader(httpResponse.GetResponseStream())) { //ReadToEnd适用于小文件的读取，一次性的返回整个文件 NJL.JObject _JObject = (NJL.JObject)NJ.JsonConvert.DeserializeObject(sr.ReadToEnd()); try { if (_JObject["success"].ToString().ToLower() == "true") { MACHINEINFO _MACHINEINFO = new MACHINEINFO(_Transaction); _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SMSIDENTITY ( SMSIDENTITY_ID,CELLPHONE_NUMBER,IDENTITY_DATE,IDENTITY_CODE) VALUES (HIGHWAY_EXCHANGE.SEQ_SMSIDENTITY.NEXTVAL,'" + action_data + "',SYSDATE,'" + _IDENTITY_CODE + "')", null); } reString = _JObject.ToString(); } catch (Exception _Exception) { reString = "error:" + _Exception.Message; } sr.Close(); } } catch (WebException ex) { httpResponse = (HttpWebResponse)ex.Response; } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取会员内码 case "GetMembership_ID": try { string MobileNumber = action_data.Split(',')[0], Membership_Card = action_data.Split(',')[1]; action_record = HttpContext.Current.Request.Params["action_record"]; if (string.IsNullOrEmpty(action_record)) { reString = ""; } else { MEMBERSHIP _MEMBERSHIP = new MEMBERSHIP(_Transaction); _MEMBERSHIP.AddSearchParameter("MEMBERSHIP_CARD", Membership_Card); _MEMBERSHIP.AddSearchParameter("MEMBERSHIP_MOBILEPHONE", MobileNumber); if (_MEMBERSHIP.Search()) { List _WechatInfoObjectObject = new List(); WechatInfoObject _WechatInfoObject = new WechatInfoObject(); _WechatInfoObject.WECHAT_OPENID = action_record; _WechatInfoObject.LINE_NUMBER = "1"; _WechatInfoObject.RTWECHATPUSH_ID = _MEMBERSHIP.MEMBERSHIP_ID.ToString(); USERMATCHING _USERMATCHING = new USERMATCHING(_Transaction); _USERMATCHING.AddSearchParameter("WECHAT_OPENID", action_record); if (_USERMATCHING.Search()) { reString = "已关联会员信息，请刷新后重试！"; } else { _USERMATCHING.ResetProperty(); _USERMATCHING.MEMBERSHIP_ID = _MEMBERSHIP.MEMBERSHIP_ID; _USERMATCHING.MEMBERSHIP_NAME = _MEMBERSHIP.MEMBERSHIP_NAME; _USERMATCHING.MEMBERSHIP_CARD = _MEMBERSHIP.MEMBERSHIP_CARD; _USERMATCHING.REALNAME = _MEMBERSHIP.MEMBERSHIP_NAME; _USERMATCHING.CELLPHONE = string.IsNullOrEmpty(MobileNumber) ? _MEMBERSHIP.MEMBERSHIP_MOBILEPHONE : MobileNumber; _USERMATCHING.USERMATCHING_STATE = 1; _USERMATCHING.WECHAT_OPENID = action_record; Seller.Storage.Business.USERSERVERPART _USERSERVERPART = new Seller.Storage.Business.USERSERVERPART(_Transaction); _USERSERVERPART.AddSearchParameter("WECHAT_OPENID", action_record); if (_USERSERVERPART.Search()) { _USERMATCHING.MALLUSER_ID = _USERSERVERPART.MALLUSER_ID; _USERMATCHING.USERNAME = _USERSERVERPART.USERNAME; _USERMATCHING.NICK = _USERSERVERPART.NICK; } else { Storage.Business.RTWECHATPUSH _RTWECHATPUSH = new Storage.Business.RTWECHATPUSH(_Transaction); _RTWECHATPUSH.AddSearchParameter("WECHAT_OPENID", action_record); if (_RTWECHATPUSH.Search()) { _USERMATCHING.NICK = _RTWECHATPUSH.NICK_NAME; } } _USERMATCHING.Insert(); _WechatInfoObjectObject.Add(_WechatInfoObject); reString = JsonHelper.ListToJson(_WechatInfoObjectObject, "WechatInfoObject"); } } else { reString = "未找到会员信息，请至发卡处办理会员！"; } } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取实时营收数据 case "GetCurrentTotalAmount": try { string provincecode = HttpContext.Current.Request.Form["provincecode"]; if (string.IsNullOrEmpty(provincecode)) { provincecode = HttpContext.Current.Request.Params["provincecode"]; if (string.IsNullOrEmpty(provincecode)) { provincecode = _PROVINCE_CODE; } else { FieldExplain _FieldExplain = new FieldExplain(_Transaction); _FieldExplain.AddSearchParameter("FIELDEXPLAIN_FIELD", "DIVISION_CODE"); if (_FieldExplain.Search()) { FieldEnum _FieldEnum = new FieldEnum(_Transaction); _FieldEnum.AddSearchParameter("FieldExplain_ID", _FieldExplain.FieldExplain_ID); _FieldEnum.AddSearchParameter("FIELDENUM_VALUE", provincecode); if (_FieldEnum.Search()) { provincecode = _FieldEnum.FieldEnum_ID.ToString(); } } } } else { FieldExplain _FieldExplain = new FieldExplain(_Transaction); _FieldExplain.AddSearchParameter("FIELDEXPLAIN_FIELD", "DIVISION_CODE"); if (_FieldExplain.Search()) { FieldEnum _FieldEnum = new FieldEnum(_Transaction); _FieldEnum.AddSearchParameter("FieldExplain_ID", _FieldExplain.FieldExplain_ID); _FieldEnum.AddSearchParameter("FIELDENUM_VALUE", provincecode); if (_FieldEnum.Search()) { provincecode = _FieldEnum.FieldEnum_ID.ToString(); } } } _DataTable = (new CONFIGURATION(_Transaction).ExecuteDataTable( @"SELECT NVL(TOTALCURRSELLCOUNT,0) AS TOTALPRICE, TOTALCURRAMOUNT AS TOTALTICKET,CURSELLCOUNT FROM HIGHWAY_STORAGE.V_SERVERCONNECTPOINTS WHERE SERVERPART_NAME = '合计' AND PROVINCE_CODE = " + provincecode)); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurrentAmount": try { _DataTable = (new CONFIGURATION(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_STORAGE.V_SERVERCONNECTPOINTS WHERE " + (string.IsNullOrEmpty(action_data) ? "SERVERPART_NAME <> '合计'" : "SERVERPART_ID = " + action_data) + " AND TOTALCURRSELLCOUNT IS NOT NULL ORDER BY TOTALCURRSELLCOUNT DESC")); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurrentAmountByRegion": try { _DataTable = (new CONFIGURATION(_Transaction).ExecuteDataTable( @"SELECT SUM(TOTALCURRSELLCOUNT) AS TOTALPRICE,SUM(TOTALCURRAMOUNT) AS TOTALTICKET, C.TYPE_NAME,SUM(CURSELLCOUNT) AS CURSELLCOUNT FROM HIGHWAY_STORAGE.V_SERVERCONNECTPOINTS A,HIGHWAY_STORAGE.V_REGIONSERVERPART C WHERE A.SERVERPART_ID = C.SERVERPART_ID GROUP BY C.TYPE_NAME")); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurrentAmountByBusinessType": try { _DataTable = new CONFIGURATION(_Transaction).ExecuteDataTable(string.Format( @"SELECT SUM(CURRAMOUNT) AS TOTALPRICE,SUM(CURRSELLCOUNT) AS TOTALTICKET, CASE WHEN SHOPTRADE = 1000 THEN 'MARKET' WHEN SHOPTRADE LIKE '2%' THEN 'SNACK' WHEN SHOPTRADE LIKE '3%' THEN 'RESTAURANT' ELSE 'OTHER' END AS SHOPTRADE, SUM(CURSELLCOUNT) AS CURSELLCOUNT FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE TRUNC(CONNECT_DATE) = TRUNC(SYSDATE) AND SERVERPARTCODE NOT IN ('888888','330005') AND PROVINCE_CODE = {0} GROUP BY CASE WHEN SHOPTRADE = 1000 THEN 'MARKET' WHEN SHOPTRADE LIKE '2%' THEN 'SNACK' WHEN SHOPTRADE LIKE '3%' THEN 'RESTAURANT' ELSE 'OTHER' END", _PROVINCE_CODE)); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurrentCount": try { _DataTable = (new CONFIGURATION(_Transaction).ExecuteDataTable( @"SELECT COUNT(DISTINCT SERVERPART_ID) AS SERVERPARTCOUNT, COUNT(DISTINCT SERVERPARTSHOP_ID || MACHINECODE) AS MACHINECOUNT, SUM(CURSELLCOUNT) AS CURSELLCOUNT FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE CONNECT_DATE > TRUNC(SYSDATE) AND SERVERPARTCODE NOT IN ('888888','330005') AND PROVINCE_CODE = " + _PROVINCE_CODE)); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurrentCountByShop": try { _DataTable = (new CONFIGURATION(_Transaction).ExecuteDataTable( @"SELECT COUNT(DISTINCT SERVERPARTSHOP_ID) AS SERVERPARTSHOPCOUNT, COUNT(DISTINCT SERVERPARTSHOP_ID || MACHINECODE) AS MACHINECOUNT, SERVERPART_ID,SERVERPARTSHOP_ID,SHOPNAME,SUM(CURSELLCOUNT) AS CURSELLCOUNT FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE CONNECT_DATE > SYSDATE - 1/144 AND SERVERPARTCODE NOT IN ('888888','330005') AND PROVINCE_CODE = " + _PROVINCE_CODE + " GROUP BY SERVERPART_ID,SERVERPARTSHOP_ID,SHOPNAME")); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetCurrentAmountByType": try { _DataTable = (new CONFIGURATION(_Transaction).ExecuteDataTable( @"SELECT SUM(CURRAMOUNT) AS TOTALPRICE,SUM(CURRSELLCOUNT) AS TOTALTICKET,SHOPNAME, SUM(CURSELLCOUNT) AS CURSELLCOUNT FROM (SELECT CURRAMOUNT,CURRSELLCOUNT,CURSELLCOUNT, CASE WHEN SHOPNAME LIKE '%驿佰购%' THEN '驿佰购' WHEN SHOPNAME LIKE '%驿佰味%' THEN '驿佰味' WHEN SHOPNAME LIKE '%五芳斋%' THEN '五芳斋' WHEN SHOPNAME LIKE '%唯新小吃%' THEN '唯新小吃' END AS SHOPNAME FROM HIGHWAY_STORAGE.V_CONNECTPOINT WHERE TRUNC(CONNECT_DATE) = TRUNC(SYSDATE) AND SERVERPARTCODE NOT IN ('888888','330005') AND PROVINCE_CODE = " + _PROVINCE_CODE + ") GROUP BY SHOPNAME")); reString = JsonHelper.DataTableToJson(_DataTable, "CurrentData"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取异常数据 case "GetException": try { _DataTable = (new Business.COMMODITY(_Transaction).ExecuteDataTable(@"SELECT * FROM ( SELECT A.*,B.FIELDENUM_NAME FROM HIGHWAY_SELLDATA.V_SALEEXCEPTION A,PLATFORM_DICTIONARY.V_FIELDENUM B WHERE TRUNC(EXCEPTION_DATE) = TRUNC(SYSDATE) AND A.EXCEPTIONTYPE = B.FIELDENUM_VALUE AND B.FIELDEXPLAIN_FIELD = 'EXCEPTION_TYPE' AND EXCEPTIONTYPE NOT IN ('1019','1029','2029') AND SERVERPARTCODE NOT IN ('888888','330005') AND EXCEPTIONTYPE < '9000' ORDER BY EXCEPTION_DATE DESC) WHERE ROWNUM <= 100")); reString = JsonHelper.DataTableToJson(_DataTable, "Exception"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取微信人员 case "GetWechatInfoByCode": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } string url = "https://api.weixin.qq.com/sns/oauth2/access_token?appid=" + AppID + "&secret=" + AppSecret + "&code=" + action_data + "&grant_type=authorization_code"; List _WechatInfoObjectObject = new List(); WechatInfoObject _WechatInfoObject = new WechatInfoObject(); //根据url创建HttpWebRequest对象 HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url); objRequest.Method = "get"; //读取服务器返回信息 HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse(); //using作为语句，用于定义一个范围，在此范围的末尾将释放对象 using (StreamReader sr = new StreamReader(objResponse.GetResponseStream())) { //ReadToEnd适用于小文件的读取，一次性的返回整个文件 NJL.JObject _JObject = (NJL.JObject)NJ.JsonConvert.DeserializeObject(sr.ReadToEnd()); try { _WechatInfoObject.WECHAT_OPENID = _JObject["openid"].ToString(); Business.RTWECHATPUSH _RTWECHATPUSH = new Business.RTWECHATPUSH(_Transaction); _RTWECHATPUSH.AddSearchParameter("WECHAT_OPENID", _WechatInfoObject.WECHAT_OPENID); if (_RTWECHATPUSH.Search() && !string.IsNullOrEmpty(action_record)) { UniformSend.Storage.Business.PURCHASE _PURCHASE = new UniformSend.Storage.Business.PURCHASE(_Transaction); _PURCHASE.PURCHASE_ID_Encrypt = action_record.Split(',')[1].ToEncrypt(); if (_PURCHASE.Select()) { if (_RTWECHATPUSH.PUSH_MODULE.Contains("3RK4FPn6JMBOByVXU8CruLpBbNUr8iCSMdFlt6ukBKM") && (("," + _RTWECHATPUSH.SERVERPART_IDS + ",").Contains("," + _serverpart_id + ",") || ("," + _RTWECHATPUSH.SERVERPART_IDS + ",").Contains("," + _PURCHASE.SERVERPART_ID + ","))) { _WechatInfoObject.LINE_NUMBER = _RTWECHATPUSH.USER_NAME; _WechatInfoObject.RTWECHATPUSH_ID = _RTWECHATPUSH.RTWECHATPUSH_ID.ToString(); } } } } catch (Exception ex) { reString = "error:" + ex.Message + "(" + _JObject + ")"; break; //SuperMap.RealEstate.Utility.ErrorLogHelper.Write(ex); } sr.Close(); } _WechatInfoObjectObject.Add(_WechatInfoObject); reString = JsonHelper.ListToJson(_WechatInfoObjectObject, "WechatInfoObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "WechatPushPerson": try { _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_STORAGE.T_RTWECHATPUSH WHERE " + (string.IsNullOrEmpty(action_data) ? "RTWECHATPUSH_STATE = 1" : "WECHAT_OPENID = '" + action_data + "'")); reString = JsonHelper.DataTableToJson(_DataTable, "RTWECHATPUSHObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetAccessToken": try { string url = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + AppID + "&secret=" + AppSecret; //根据url创建HttpWebRequest对象 HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url); objRequest.Method = "get"; //读取服务器返回信息 HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse(); //using作为语句，用于定义一个范围，在此范围的末尾将释放对象 using (StreamReader sr = new StreamReader(objResponse.GetResponseStream())) { //ReadToEnd适用于小文件的读取，一次性的返回整个文件 NJL.JObject _JObject = (NJL.JObject)NJ.JsonConvert.DeserializeObject(sr.ReadToEnd()); try { reString = _JObject["access_token"].ToString(); } catch (Exception ex) { reString = "错误代码：" + _JObject["errcode"].ToString() + "，错误原因：" + _JObject["errmsg"].ToString() + "(" + ex.Message + ")"; } sr.Close(); } } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "WechatPersonInfo": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } string url = "https://api.weixin.qq.com/cgi-bin/user/info?access_token=" + action_data + "&openid=" + action_record + "&lang=zh_CN"; //根据url创建HttpWebRequest对象 HttpWebRequest objRequest = (HttpWebRequest)WebRequest.Create(url); objRequest.Method = "get"; //读取服务器返回信息 HttpWebResponse objResponse = (HttpWebResponse)objRequest.GetResponse(); //using作为语句，用于定义一个范围，在此范围的末尾将释放对象 using (StreamReader sr = new StreamReader(objResponse.GetResponseStream())) { //ReadToEnd适用于小文件的读取，一次性的返回整个文件 NJL.JObject _JObject = (NJL.JObject)NJ.JsonConvert.DeserializeObject(sr.ReadToEnd()); if (!string.IsNullOrEmpty(_JObject["headimgurl"].ToString())) { Business.RTWECHATPUSH _RTWECHATPUSH = new Business.RTWECHATPUSH(_Transaction); if (_RTWECHATPUSH.GetCount( "WHERE headimgurl IS NULL AND WECHAT_OPENID = '" + action_record + "'") > 0) { _RTWECHATPUSH.ExecuteNonQuery("UPDATE HIGHWAY_STORAGE.T_RTWECHATPUSH SET headimgurl = '" + _JObject["headimgurl"].ToString() + "' WHERE WECHAT_OPENID = '" + action_record + "'", null); } } reString = _JObject.ToString(); sr.Close(); } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取配送单信息 case "CopyDeliverIndex": try { string FileName = _FilePath + _CopyPath + action_data + ".html"; FileInfo _FileInfo = new FileInfo(FileName); if (_FileInfo.Exists) { reString = "https://open.weixin.qq.com/connect/oauth2/authorize?appid=wx85e69a5fb601be16&redirect_uri=" + HttpUtility.UrlEncode(_UrlAddress + action_data + ".html") + "&response_type=code&scope=snsapi_base&connect_redirect=1#wechat_redirect"; } else { DirectoryInfo _DirectoryInfo = new DirectoryInfo(_FilePath); if (_DirectoryInfo.Exists) { foreach (FileInfo fileinfo in _DirectoryInfo.GetFiles("index.html", SearchOption.TopDirectoryOnly)) { //FileStream _FileStream = new FileStream(FileName, FileMode.Create); //fileinfo.CopyTo(_FileStream.Name); //_FileStream.Flush(); //_FileStream.Close(); fileinfo.CopyTo(FileName); reString = "https://open.weixin.qq.com/connect/oauth2/authorize?appid=wx85e69a5fb601be16&redirect_uri=" + HttpUtility.UrlEncode(_UrlAddress + action_data + ".html") + "&response_type=code&scope=snsapi_base&connect_redirect=1#wechat_redirect"; break; } } } } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetGoodsDeliver": try { action_record = HttpContext.Current.Request.Params["action_record"]; if (string.IsNullOrEmpty(action_record)) { reString = ""; } else { _DataTable = (new SRSSB.GOODSDELIVER(_Transaction).ExecuteDataTable( "SELECT * FROM SELLER_STORAGE.V_GOODSDELIVER WHERE GOODSDELIVER_CODE = '" + action_data + "' AND PURCHASE_ID = " + action_record)); reString = JsonHelper.DataTableToJson(_DataTable, "Exception"); } } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetGoodsDeliverDtail": try { action_record = HttpContext.Current.Request.Params["action_record"]; if (string.IsNullOrEmpty(action_record)) { reString = ""; } else { _DataTable = (new SRSSB.GOODSDELIVER(_Transaction).ExecuteDataTable( "SELECT * FROM SELLER_STORAGE.V_GOODSDELIVERBILL WHERE GOODSDELIVER_CODE = '" + action_data + "' AND PURCHASE_ID = " + action_record)); reString = JsonHelper.DataTableToJson(_DataTable, "Exception"); } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取入库信息 case "DeliverCodeCheck": try { if (action_data.Split(',').Length != 5) { reString = "单据无效"; } else { SRSSB.GOODSDELIVER _GOODSDELIVER = new SRSSB.GOODSDELIVER(_Transaction); _DataTable = _GOODSDELIVER.ExecuteDataTable( "SELECT * FROM SELLER_STORAGE.V_GOODSDELIVER WHERE GOODSDELIVER_CODE = '" + action_data.Split(',')[0] + "' AND PURCHASE_ID = " + action_data.Split(',')[1]); if (_DataTable.Rows.Count > 0) { switch (_DataTable.Rows[0]["PURCHASE_STATE"].ToString()) { case "2000": reString = "等待配送中"; break; case "2010": reString = "单据有效"; if (_GOODSDELIVER.ExecuteDataTable( "SELECT 1 FROM SELLER_STORAGE.T_OPERATELOG WHERE GOODSDELIVER_CODE = '" + action_data.Split(',')[0] + "' AND PURCHASE_ID = " + action_data.Split(',')[1]).Rows.Count == 0) { _GOODSDELIVER.ExecuteNonQuery(@"INSERT INTO SELLER_STORAGE.T_OPERATELOG (OPERATELOG_ID,GOODSDELIVER_CODE,PURCHASE_ID,SERVERPARTSHOP_ID, CASHWORKER_NAME,RTWECHATPUSH_ID,WECHAT_OPENID,OPERATELOG_DATE) SELECT SELLER_STORAGE.SEQ_OPERATELOG.NEXTVAL,GOODSDELIVER_CODE, PURCHASE_ID,SERVERPARTSHOP_ID,'" + action_data.Split(',')[2] + "'," + action_data.Split(',')[3] + ",'" + action_data.Split(',')[4] + "',SYSDATE FROM SELLER_STORAGE.V_GOODSDELIVER WHERE GOODSDELIVER_CODE = '" + action_data.Split(',')[0] + "' AND PURCHASE_ID = " + action_data.Split(',')[1], null); } break; case "3000": reString = "单据已接收"; break; case "4000": reString = "单据已入库"; break; } } else { reString = "单据无效"; } } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取结账、收银员、单品数据 case "ScanCodeUpdate": try { action_data = HttpUtility.UrlDecode(action_data); action_record = HttpContext.Current.Request.Params["action_record"]; bool Flag = true; decimal CONTENT_LENGTH = 0, _Length = 0, _RowCount = 0, _RecordCount = 0; string _EndAccount = "", _PersonSell = "", _CommoditySale = "", TimeString = ""; DateTime _MaxDate = DateTime.Parse("2000/01/01"); foreach (string str in action_data.Split('&')) { switch (str.Split('=')[0].ToUpper()) { case "ENDACCOUNT": CONTENT_LENGTH += str.Split('=')[1].Length; _EndAccount = str.Split('=')[1]; break; case "PERSONSELL": CONTENT_LENGTH += str.Split('=')[1].Length; _PersonSell = str.Split('=')[1]; break; case "COMMODITYSALE": CONTENT_LENGTH += str.Split('=')[1].Length; _CommoditySale = str.Split('=')[1]; break; case "LENGTH": _Length = decimal.Parse(str.Split('=')[1]); break; case "ROWCOUNT": _RowCount = decimal.Parse(str.Split('=')[1]); break; case "RECORDCOUNT": _RecordCount = decimal.Parse(str.Split('=')[1]); break; } } if (_Length != CONTENT_LENGTH) { reString = "0"; } else { MACHINEINFO _MACHINEINFO = new MACHINEINFO(_Transaction); if (!string.IsNullOrEmpty(_EndAccount)) { if (_MACHINEINFO.ExecuteDataTable("SELECT 1 FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT = '" + _EndAccount + "' AND SCAN_TYPE = 'EndAccount' AND CONTENT_LENGTH = " + _Length + " AND ROW_COUNT = " + _RowCount + " AND RECORD_COUNT = " + _RecordCount).Rows.Count == 0) { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SCANCODEUPDATE ( SCANCODEUPDATE_ID,SCAN_TYPE,SCAN_CONTENT,CONTENT_LENGTH,ROW_COUNT,RECORD_COUNT) VALUES (HIGHWAY_EXCHANGE.SEQ_SCANCODEUPDATE.NEXTVAL,'EndAccount','" + _EndAccount + "'," + _Length + "," + _RowCount + "," + _RecordCount + ")", null); } else { Flag = false; } } TimeString = _EndAccount.Split('|')[2]; TimeString = TimeString.Substring(0, 4) + "/" + TimeString.Substring(4, 2) + "/" + TimeString.Substring(6, 2) + " " + TimeString.Substring(8, 2) + ":" + TimeString.Substring(10, 2) + ":" + TimeString.Substring(12, 2); if (DateTime.Parse(TimeString) > _MaxDate) { _MaxDate = DateTime.Parse(TimeString); reString = ConvertDataTimeLong(_MaxDate).ToString() + _EndAccount.Split('|')[4]; } } if (!string.IsNullOrEmpty(_PersonSell)) { foreach (string PersonSellString in _PersonSell.Split(',')) { if (_MACHINEINFO.ExecuteDataTable("SELECT 1 FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT = '" + PersonSellString + "' AND SCAN_TYPE = 'PersonSell' AND CONTENT_LENGTH = " + _Length + " AND ROW_COUNT = " + _RowCount + " AND RECORD_COUNT = " + _RecordCount).Rows.Count == 0) { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SCANCODEUPDATE ( SCANCODEUPDATE_ID,SCAN_TYPE,SCAN_CONTENT,CONTENT_LENGTH,ROW_COUNT,RECORD_COUNT) VALUES (HIGHWAY_EXCHANGE.SEQ_SCANCODEUPDATE.NEXTVAL,'PersonSell','" + PersonSellString + "'," + _Length + "," + _RowCount + "," + _RecordCount + ")", null); } else { Flag = false; } } TimeString = PersonSellString.Split('|')[5]; TimeString = TimeString.Substring(0, 4) + "/" + TimeString.Substring(4, 2) + "/" + TimeString.Substring(6, 2) + " " + TimeString.Substring(8, 2) + ":" + TimeString.Substring(10, 2) + ":" + TimeString.Substring(12, 2); if (DateTime.Parse(TimeString) > _MaxDate) { _MaxDate = DateTime.Parse(TimeString); reString = ConvertDataTimeLong(_MaxDate).ToString() + PersonSellString.Split('|')[2]; } } } if (!string.IsNullOrEmpty(_CommoditySale)) { if (_MACHINEINFO.ExecuteDataTable("SELECT 1 FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT = '" + _CommoditySale + "' AND SCAN_TYPE = 'CommoditySale' AND CONTENT_LENGTH = " + _Length + " AND ROW_COUNT = " + _RowCount + " AND RECORD_COUNT = " + _RecordCount).Rows.Count == 0) { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SCANCODEUPDATE ( SCANCODEUPDATE_ID,SCAN_TYPE,SCAN_CONTENT,CONTENT_LENGTH,ROW_COUNT,RECORD_COUNT) VALUES (HIGHWAY_EXCHANGE.SEQ_SCANCODEUPDATE.NEXTVAL,'CommoditySale','" + _CommoditySale + "'," + _Length + "," + _RowCount + "," + _RecordCount + ")", null); } else { Flag = false; } } DataTable DataTableSale = _MACHINEINFO.ExecuteDataTable( "SELECT DISTINCT ROW_COUNT FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT LIKE '%|" + _CommoditySale.Split('|')[1] + "|" + _CommoditySale.Split('|')[2] + "|" + _CommoditySale.Split('|')[3] + "|" + _CommoditySale.Split('|')[4] + "|%' AND SCAN_TYPE = 'CommoditySale' ORDER BY ROW_COUNT"); if (DataTableSale.Rows.Count == _RecordCount) { TimeString = _CommoditySale.Split('|')[4]; TimeString = TimeString.Substring(0, 4) + "/" + TimeString.Substring(4, 2) + "/" + TimeString.Substring(6, 2) + " " + TimeString.Substring(8, 2) + ":" + TimeString.Substring(10, 2) + ":" + TimeString.Substring(12, 2); if (DateTime.Parse(TimeString) > _MaxDate) { _MaxDate = DateTime.Parse(TimeString); reString = ConvertDataTimeLong(_MaxDate).ToString() + _CommoditySale.Split('|')[2]; } } else { foreach (DataRow _DataRow in DataTableSale.Rows) { if (!("," + reString + ",").Contains("," + _DataRow["ROW_COUNT"].ToString() + ",")) { reString += (string.IsNullOrEmpty(reString) ? "" : ",") + _DataRow["ROW_COUNT"].ToString(); } } reString = "2:" + reString; } } if (!Flag) { reString = "0"; } } } catch (Exception ex) { _Transaction.Rollback(); reString = "error:" + ex.Message; } break; case "ScanCodeUpdate_New": try { action_data = HttpUtility.UrlDecode(action_data); action_record = HttpContext.Current.Request.Params["action_record"]; bool Flag = true; decimal CONTENT_LENGTH = 0, _Length = 0, _RowCount = 0, _RecordCount = 0, _ScanIndex = 0, _ScanCount = 0; string _ScanAudit = "", _EndAccount = "", _PersonSell = "", _CommoditySale = "", TimeString = "", _CheckCode = "", _WeChatCode = ""; DateTime _MaxDate = DateTime.Parse("2000/01/01"); foreach (string str in action_data.Split('&')) { switch (str.Split('=')[0].ToUpper()) { case "SCANAUDIT": //CONTENT_LENGTH += str.Split('=')[1].Length; //_ScanAudit = str.Split('=')[1]; //break; case "ENDACCOUNT": CONTENT_LENGTH += str.Split('=')[1].Length; _EndAccount = str.Split('=')[1]; break; case "PERSONSELL": CONTENT_LENGTH += str.Split('=')[1].Length; _PersonSell = str.Split('=')[1]; break; case "COMMODITYSALE": CONTENT_LENGTH += str.Split('=')[1].Length; _CommoditySale = str.Split('=')[1]; break; case "LENGTH": _Length = decimal.Parse(str.Split('=')[1]); break; case "ROWCOUNT": _RowCount = decimal.Parse(str.Split('=')[1]); break; case "RECORDCOUNT": _RecordCount = decimal.Parse(str.Split('=')[1]); break; case "CHECKCODE": _CheckCode = str.Split('=')[1]; break; case "SCANINDEX": _ScanIndex = decimal.Parse(str.Split('=')[1]); break; case "SCANCOUNT": _ScanCount = decimal.Parse(str.Split('=')[1]); break; case "CODE": _WeChatCode = str.Split('=')[1]; break; } } if (_Length != CONTENT_LENGTH) { reString = "0"; } else { MACHINEINFO _MACHINEINFO = new MACHINEINFO(_Transaction); if (!string.IsNullOrEmpty(_EndAccount)) { if (_MACHINEINFO.ExecuteDataTable("SELECT 1 FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT = '" + _EndAccount + "' AND SCAN_TYPE = 'EndAccount' AND CONTENT_LENGTH = " + _Length).Rows.Count == 0) { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SCANCODEUPDATE ( SCANCODEUPDATE_ID,SCAN_TYPE,SCAN_CONTENT,CONTENT_LENGTH,ROW_COUNT, RECORD_COUNT,CHECK_CODE,SCAN_INDEX,SCAN_COUNT) VALUES (HIGHWAY_EXCHANGE.SEQ_SCANCODEUPDATE.NEXTVAL,'EndAccount','" + _EndAccount + "'," + _Length + "," + _RowCount + "," + _RecordCount + ",'" + _CheckCode + "'," + _ScanIndex + "," + _ScanCount + ")", null); } else { Flag = false; } } else { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"UPDATE HIGHWAY_EXCHANGE.T_SCANCODEUPDATE SET CHECK_CODE = '" + _CheckCode + "',SCAN_INDEX = " + _ScanIndex + ",SCAN_COUNT = " + _ScanCount + " WHERE SCAN_TYPE = 'EndAccount' AND SCAN_CONTENT = '" + _EndAccount + "' AND CONTENT_LENGTH = " + _Length, null); } else { Flag = false; } } if (!string.IsNullOrEmpty(_PersonSell)) { foreach (string PersonSellString in _PersonSell.Split(',')) { if (_MACHINEINFO.ExecuteDataTable("SELECT 1 FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT = '" + PersonSellString + "' AND SCAN_TYPE = 'PersonSell' AND CONTENT_LENGTH = " + _Length).Rows.Count == 0) { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SCANCODEUPDATE ( SCANCODEUPDATE_ID,SCAN_TYPE,SCAN_CONTENT,CONTENT_LENGTH,ROW_COUNT, RECORD_COUNT,CHECK_CODE,SCAN_INDEX,SCAN_COUNT) VALUES (HIGHWAY_EXCHANGE.SEQ_SCANCODEUPDATE.NEXTVAL,'PersonSell','" + PersonSellString + "'," + _Length + "," + _RowCount + "," + _RecordCount + ",'" + _CheckCode + "'," + _ScanIndex + "," + _ScanCount + ")", null); } else { Flag = false; } } else { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"UPDATE HIGHWAY_EXCHANGE.T_SCANCODEUPDATE SET CHECK_CODE = '" + _CheckCode + "',SCAN_INDEX = " + _ScanIndex + ",SCAN_COUNT = " + _ScanCount + " WHERE SCAN_CONTENT = '" + PersonSellString + "' AND SCAN_TYPE = 'PersonSell' AND CONTENT_LENGTH = " + _Length, null); } else { Flag = false; } } } } string strMd5 = _MACHINEINFO.ExecuteDataTable("SELECT WM_CONCAT(SCAN_CONTENT) FROM (" + "SELECT SCAN_CONTENT FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE CHECK_CODE = '" + _CheckCode + "' AND SCAN_TYPE = 'EndAccount' ORDER BY SCAN_INDEX)").Rows[0][0].ToString(); if (strMd5 != "") { strMd5 = CreateMD5(strMd5); if (strMd5 == _CheckCode) { reString = strMd5; } else { TimeString = _EndAccount.Split('|')[2]; TimeString = TimeString.Substring(0, 4) + "/" + TimeString.Substring(4, 2) + "/" + TimeString.Substring(6, 2) + " " + TimeString.Substring(8, 2) + ":" + TimeString.Substring(10, 2) + ":" + TimeString.Substring(12, 2); reString = "3|" + TimeString; } } } if (!string.IsNullOrEmpty(_CommoditySale)) { if (_MACHINEINFO.ExecuteDataTable("SELECT 1 FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT = '" + _CommoditySale + "' AND SCAN_TYPE = 'CommoditySale' AND CONTENT_LENGTH = " + _Length + " AND ROW_COUNT = " + _RowCount + " AND RECORD_COUNT = " + _RecordCount).Rows.Count == 0) { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"INSERT INTO HIGHWAY_EXCHANGE.T_SCANCODEUPDATE (SCANCODEUPDATE_ID, SCAN_TYPE,SCAN_CONTENT,CONTENT_LENGTH,ROW_COUNT,RECORD_COUNT,CHECK_CODE,SCAN_INDEX,SCAN_COUNT) VALUES (HIGHWAY_EXCHANGE.SEQ_SCANCODEUPDATE.NEXTVAL,'CommoditySale','" + _CommoditySale + "'," + _Length + "," + _RowCount + "," + _RecordCount + ",'" + _CheckCode + "'," + _ScanIndex + "," + _ScanCount + ")", null); } else { Flag = false; } } else { if (action_record == "1") { _MACHINEINFO.ExecuteNonQuery(@"UPDATE HIGHWAY_EXCHANGE.T_SCANCODEUPDATE SET CHECK_CODE = '" + _CheckCode + "',SCAN_INDEX = " + _ScanIndex + ",SCAN_COUNT = " + _ScanCount + " WHERE SCAN_CONTENT = '" + _CommoditySale + "' AND CONTENT_LENGTH = " + _Length + " AND SCAN_TYPE = 'CommoditySale' AND ROW_COUNT = " + _RowCount + " AND RECORD_COUNT = " + _RecordCount, null); } else { Flag = false; } } DataTable DataTableSale = _MACHINEINFO.ExecuteDataTable( "SELECT DISTINCT ROW_COUNT FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE SCAN_CONTENT LIKE '%|" + _CommoditySale.Split('|')[1] + "|" + _CommoditySale.Split('|')[2] + "|" + _CommoditySale.Split('|')[3] + "|" + _CommoditySale.Split('|')[4] + "|%' AND SCAN_TYPE = 'CommoditySale' ORDER BY ROW_COUNT"); if (DataTableSale.Rows.Count == _RecordCount) { string strMd5 = _MACHINEINFO.ExecuteDataTable("SELECT WM_CONCAT(SCAN_CONTENT) FROM (" + "SELECT SCAN_CONTENT FROM HIGHWAY_EXCHANGE.T_SCANCODEUPDATE WHERE CHECK_CODE = '" + _CheckCode + "' AND SCAN_TYPE = 'CommoditySale' ORDER BY SCAN_INDEX)").Rows[0][0].ToString(); if (strMd5 != "") { strMd5 = CreateMD5(strMd5); if (strMd5 == _CheckCode) { reString = strMd5; } else { TimeString = _CommoditySale.Split('|')[4]; TimeString = TimeString.Substring(0, 4) + "/" + TimeString.Substring(4, 2) + "/" + TimeString.Substring(6, 2) + " " + TimeString.Substring(8, 2) + ":" + TimeString.Substring(10, 2) + ":" + TimeString.Substring(12, 2); reString = "3|" + TimeString; } } } else { foreach (DataRow _DataRow in DataTableSale.Rows) { if (!("," + reString + ",").Contains("," + _DataRow["ROW_COUNT"].ToString() + ",")) { reString += (string.IsNullOrEmpty(reString) ? "" : ",") + _DataRow["ROW_COUNT"].ToString(); } } reString = "2|" + reString; } } if (!Flag) { reString = "0"; } } } catch (Exception ex) { _Transaction.Rollback(); reString = "0"; } break; #endregion #region 数据采集 case "CurDayCollection": try { _DataTable = new CONFIGURATION(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_EXCHANGE.V_CURDATACOLLECTION WHERE PROVINCE_CODE = '" + (string.IsNullOrEmpty(action_data) ? _PROVINCE_CODE : action_data) + "'"); reString = JsonHelper.DataTableToJson(_DataTable, "CollectionObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "CurHalfCollection": try { _DataTable = new CONFIGURATION(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_EXCHANGE.V_DATACOLLECTION_AVRHALF WHERE PROVINCE_CODE = '" + (string.IsNullOrEmpty(action_data) ? _PROVINCE_CODE : action_data) + "' ORDER BY AVR_TICKET DESC"); reString = JsonHelper.DataTableToJson(_DataTable, "CollectionObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetWechatPushDtail": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } switch (action_data) { case "1010": case "1020": if (action_record.Split(',').Length < 2) { _DataTable = null; } else { _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( @"SELECT * FROM HIGHWAY_STORAGE.T_WECHATPUSHDETAIL WHERE DIRECTION_IDS = '" + action_record.Split(',')[0] + "' AND GROUP_TYPE = " + action_data + " AND TRUNC(STATISTICS_DATE) = TO_DATE('" + action_record.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD')" + (action_record.Split(',').Length > 2 ? " AND PROVINCE_CODE = '" + action_record.Split(',')[2] + "'" : "") + " ORDER BY STATISTICS_DATE DESC"); } break; case "2000": _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( @"SELECT * FROM HIGHWAY_STORAGE.T_WECHATPUSHDETAIL WHERE GROUP_TYPE = " + action_data + " AND RTWECHATPUSH_ID = " + action_record.Split(',')[2] + " AND DIRECTION_IDS = '" + action_record.Split(',')[0] + "' AND TRUNC(STATISTICS_DATE) = TO_DATE('" + action_record.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD')" + (action_record.Split(',').Length > 3 ? " AND PROVINCE_CODE = '" + action_record.Split(',')[3] + "'" : "") + " ORDER BY STATISTICS_DATE DESC"); break; case "1000": default: _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( @"SELECT * FROM HIGHWAY_STORAGE.T_WECHATPUSHDETAIL WHERE DIRECTION_IDS IS NULL AND GROUP_TYPE = " + (string.IsNullOrEmpty(action_data) ? "1000" : action_data) + " AND " + (string.IsNullOrEmpty(action_record) ? "STATISTICS_DATE > TRUNC(SYSDATE - 3/2)" : "TRUNC(STATISTICS_DATE) = TO_DATE('" + action_record.Split(',')[0].Split(' ')[0] + "','YYYY/MM/DD')" + (action_record.Split(',').Length > 1 ? " AND PROVINCE_CODE = '" + action_record.Split(',')[1] + "'" : "")) + " ORDER BY STATISTICS_DATE DESC"); /*for (int RowCount = 0; RowCount < _DataTable.Rows.Count; RowCount++) { DataTable UploadData = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable(string.Format( @"SELECT COUNT(DISTINCT SERVERPARTSHOP_ID) AS TOTALCOUNT, CASE WHEN SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN 95 ELSE SERVERPART_ID END AS SERVERPART_ID FROM HIGHWAY_SELLDATA.V_ENDACCOUNT_TEMP WHERE NVL(BUSINESS_STATE,1000) = 1000 AND NVL(STATISTIC_TYPE,1000) = 1000 AND ENDACCOUNT_ID <> 0 AND TRUNC(ENDACCOUNT_DATE) = TO_DATE('{0}','YYYY/MM/DD') GROUP BY CASE WHEN SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN 95 ELSE SERVERPART_ID END", _DataTable.Rows[RowCount]["STATISTICS_DATE"].ToString().Split(' ')[0])); DataTable TotalCount = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable(string.Format( @"SELECT COUNT(1) AS TOTALCOUNT,CASE WHEN A.SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN 95 ELSE A.SERVERPART_ID END AS SERVERPART_ID FROM HIGHWAY_STORAGE.T_SERVERPART A,HIGHWAY_STORAGE.T_SERVERPARTSHOP B WHERE A.SERVERPART_ID = B.SERVERPART_ID AND NVL(BUSINESS_STATE,1000) = 1000 AND NVL(B.STATISTIC_TYPE,1000) = 1000 AND ISVALID = 1 AND NVL(B.BUSINESS_ENDDATE,SYSDATE) >= TO_DATE('{0}','YYYY/MM/DD') AND TRUNC(NVL(B.BUSINESS_DATE,TO_DATE('2015/06/01','YYYY/MM/DD'))) <= TO_DATE('{0}','YYYY/MM/DD') GROUP BY CASE WHEN A.SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN 95 ELSE A.SERVERPART_ID END", _DataTable.Rows[RowCount]["STATISTICS_DATE"].ToString().Split(' ')[0])); DataTable Revenue = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable(string.Format( @"SELECT A.TYPE_NAME,A.TYPE_INDEX,SUM(CASHPAY) AS CASHPAY, CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN '金华服务区' ELSE A.SERVERPART_NAME END AS SERVERPART_NAME, CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN 95 ELSE A.SERVERPART_ID END AS SERVERPART_ID, CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN 4010 ELSE A.SERVERPART_INDEX END AS SERVERPART_INDEX, CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN '334010' ELSE A.SERVERPART_CODE END AS SERVERPART_CODE FROM HIGHWAY_STORAGE.V_REGIONSERVERPART A,HIGHWAY_SELLDATA.V_ENDACCOUNT_TEMP B WHERE NVL(B.BUSINESS_STATE,1000) = 1000 AND NVL(B.STATISTIC_TYPE,1000) = 1000 AND A.SERVERPART_ID = B.SERVERPART_ID AND A.PROVINCE_CODE = {0} AND TRUNC(ENDACCOUNT_DATE) = TO_DATE('{1}','YYYY/MM/DD') GROUP BY A.TYPE_NAME,A.TYPE_INDEX,CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN '金华服务区' ELSE A.SERVERPART_NAME END,CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN 95 ELSE A.SERVERPART_ID END, CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN 4010 ELSE A.SERVERPART_INDEX END, CASE WHEN A.SERVERPART_ID = 123 AND A.SERVERPART_NAME LIKE '金华%' THEN '334010' ELSE A.SERVERPART_CODE END ORDER BY A.TYPE_INDEX,SERVERPART_INDEX,SERVERPART_CODE", _PROVINCE_CODE, _DataTable.Rows[RowCount]["STATISTICS_DATE"].ToString().Split(' ')[0])); string _TYPE_NAME = "", _SERVERPART_REVENUE = ""; foreach (DataRow _DataRow in Revenue.Rows) { string _SERVERPART_ID = _DataRow["SERVERPART_ID"].ToString(); if (_TYPE_NAME != _DataRow["TYPE_NAME"].ToString()) { _TYPE_NAME = _DataRow["TYPE_NAME"].ToString(); _SERVERPART_REVENUE += (string.IsNullOrEmpty(_SERVERPART_REVENUE) ? "" : "|") + _DataRow["TYPE_NAME"].ToString(); } _SERVERPART_REVENUE += "," + _DataRow["SERVERPART_NAME"].ToString().Replace( "(便利店)", "").Replace("（便利店）", "").Replace("(便利店）", "").Replace("（便利店)", "") + "&&" + _DataRow["CASHPAY"].ToString() + "&&" + (UploadData.Select("SERVERPART_ID = " + _SERVERPART_ID).Length > 0 ? UploadData.Select("SERVERPART_ID = " + _SERVERPART_ID)[0]["TOTALCOUNT"].ToString() : "0") + "/" + (TotalCount.Select( "SERVERPART_ID = " + _SERVERPART_ID).Length > 0 ? TotalCount.Select("SERVERPART_ID = " + _SERVERPART_ID)[0]["TOTALCOUNT"].ToString() : "0") + "&&" + _SERVERPART_ID; } if (!string.IsNullOrEmpty(_SERVERPART_REVENUE)) { _DataTable.Rows[RowCount]["SERVERPART_REVENUE"] = _SERVERPART_REVENUE; } }*/ break; } reString = JsonHelper.DataTableToJson(_DataTable, "CollectionObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetWechatPushSales": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( "SELECT * FROM HIGHWAY_STORAGE.T_WECHATPUSHSALES WHERE WECHATPUSHDETAIL_ID = " + action_data + (string.IsNullOrEmpty(action_record) ? "" : " AND PROVINCE_CODE = '" + action_record + "'")); reString = JsonHelper.DataTableToJson(_DataTable, "WechatPushSalesObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetYestdayEndaccount": try { _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( @"SELECT SERVERPART_ID,SERVERPART_NAME,COUNT(DISTINCT SHOPCODE) AS CheckCount, COUNT(CASE WHEN TOTALSELLAMOUNT <> 0 AND ((DIFFERENT_PRICE < 0 AND TOTALSELLAMOUNT > CASHPAY) OR (DIFFERENT_PRICE > 0 AND CASHPAY_DOWNLORD + COSTBILL + TICKETBILL + OTHERPAY > CASHPAY)) THEN 1 END) AS AbnormalCount FROM HIGHWAY_SELLDATA.T_ENDACCOUNT WHERE TREATMENT_MARK = 2 AND TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) = " + (string.IsNullOrEmpty(action_data) ? "TRUNC(SYSDATE - 1)" : "TO_DATE('" + action_data.Split(' ')[0] + "','YYYY/MM/DD'") + " GROUP BY SERVERPART_ID,SERVERPART_NAME"); reString = JsonHelper.DataTableToJson(_DataTable, "WechatPushSalesObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndaccountByServerpart": try { try { action_record = HttpContext.Current.Request.Params["action_record"]; } catch { action_record = ""; } _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( @"SELECT TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) AS ENDACCOUNT_DATE, NVL(SUM(CASHPAY),0) AS CASHPAY,NVL(SUM(DIFFERENT_PRICE),0) AS DIFFERENT_PRICE, NVL(SUM(TICKETCOUNT),0) AS TICKETCOUNT,NVL(SUM(TOTALCOUNT),0) AS TOTALCOUNT FROM HIGHWAY_SELLDATA.T_ENDACCOUNT WHERE SERVERPART_ID IN (" + action_data + ") AND TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) BETWEEN TO_DATE('" + action_record.Split(',')[0].Split(' ')[0] + "','YYYY/MM/DD') AND TO_DATE('" + action_record.Split(',')[1].Split(' ')[0] + "','YYYY/MM/DD') " + "GROUP BY TRUNC(NVL(STATISTICS_DATE,ENDACCOUNT_DATE)) ORDER BY ENDACCOUNT_DATE"); reString = JsonHelper.DataTableToJson(_DataTable, "WechatPushSalesObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndAccountSale": try { _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable( "SELECT COUNT(1) AS SALECOUNT,COUNT(CASE WHEN COMMODITYTYPE_CODE IN (" + _CigaretteType + ") THEN 1 END) AS cigaretteCount FROM HIGHWAY_SELLDATA.V_COMMODITYSALE_CHECK WHERE ENDACCOUNT_ID = " + action_data); reString = JsonHelper.DataTableToJson(_DataTable, "EndAccountSaleObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; case "GetEndAccountMobilePay": try { _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable(@"SELECT COUNT(1) AS MobilePayCount FROM HIGHWAY_SELLDATA.T_SELLDATA_EXTRA A WHERE A.SELLDATA_TYPE IN('ALIPAY','WECHAT') AND EXISTS(SELECT 1 FROM HIGHWAY_SELLDATA.T_ENDACCOUNT B WHERE A.SERVERPART_ID = B.SERVERPART_ID AND A.SHOPCODE = B.SHOPCODE AND A.MACHINECODE = B.MACHINECODE AND A.SELLDATA_DATE BETWEEN B.ENDACCOUNT_STARTDATE AND B.ENDACCOUNT_DATE AND B.ENDACCOUNT_ID = " + action_data + ")"); reString = JsonHelper.DataTableToJson(_DataTable, "EndAccountMobilePayObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 微信模块权限获取 case "GetModuleAuthority": try { _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( "SELECT * FROM MOBILESERVICE_PLATFORM.V_MODULEAUTHORITY WHERE WECHAT_OPENID = '" + action_data + "'"); reString = JsonHelper.DataTableToJson(_DataTable, "ModuleAuthorityObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取上传门店数量 case "GetUploadShops": try { _DataTable = new Business.RTWECHATPUSH(_Transaction).ExecuteDataTable( string.Format(@"SELECT COUNT(DISTINCT SHOPCODE) AS SHOPCOUNT, CASE WHEN SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN 95 ELSE SERVERPART_ID END AS SERVERPART_ID, CASE WHEN SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN '金华服务区' ELSE SERVERPART_NAME END AS SERVERPART_NAME, COUNT(CASE WHEN DESCRIPTION_DATE IS NOT NULL AND TOTALSELLAMOUNT <> 0 AND ((DIFFERENT_PRICE < 0 AND TOTALSELLAMOUNT > CASHPAY) OR (DIFFERENT_PRICE > 0 AND CASHPAY_DOWNLORD + COSTBILL + TICKETBILL + OTHERPAY > CASHPAY)) THEN 1 ELSE NULL END) AS EXCEPTION_COUNT,SUM(CASHPAY) AS CASHPAY FROM HIGHWAY_SELLDATA.T_ENDACCOUNT_TEMP WHERE VALID = 1 AND TRUNC(ENDACCOUNT_DATE) = {0} GROUP BY CASE WHEN SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN 95 ELSE SERVERPART_ID END,CASE WHEN SERVERPART_ID = 123 AND SERVERPART_NAME LIKE '金华%' THEN '金华服务区' ELSE SERVERPART_NAME END", (string.IsNullOrEmpty(action_data) ? "TRUNC(SYSDATE) - 1" : "TO_DATE('" + action_data.Split(' ')[0] + "','YYYY/MM/DD')"))); reString = JsonHelper.DataTableToJson(_DataTable, "DataObject"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取商品流程详情 case "GetCommodityList": try { string SqlString = @"SELECT COMMODITYTYPE_NAME,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_UNIT, HIGHWAY_STORAGE.FUNC_GETENUMVALUE('BUSINESSTYPE',TO_CHAR(BUSINESSTYPE)) AS BUSINESSTYPE, COMMODITY_RULE,COMMODITY_CURRPRICE,COMMODITY_RETAILPRICE,COMMODITY_PURCHASEPRICE,OPERATE_DATE, HIGHWAY_STORAGE.FUNC_GETENUMVALUE('COMMODITYSTATE',TO_CHAR(COMMODITY_STATE)) AS COMMODITY_STATE FROM HIGHWAY_STORAGE.V_COMMODITYAPPROVAL WHERE HIGHWAYPROINST_ID = " + action_data.ToDecrypt(); _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable(SqlString); reString = JsonHelper.DataTableToJson(_DataTable, "CommodityList"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 商品信息列表 case "GetApproveCommodity": try { Running.Business.HIGHWAYPROINST _HIGHWAYPROINST = new Running.Business.HIGHWAYPROINST(_Transaction); Business.HIGHWAYPROINST _StorageHIGHWAYPROINST = new Business.HIGHWAYPROINST(_Transaction); _HIGHWAYPROINST.HIGHWAYPROINST_ID_Encrypt = action_data; _StorageHIGHWAYPROINST.HIGHWAYPROINST_ID_Encrypt = action_data; if (_HIGHWAYPROINST.Select()) { string SqlString = @"SELECT COMMODITYTYPE_NAME,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_UNIT, HIGHWAY_STORAGE.FUNC_GETENUMVALUE('BUSINESSTYPE',TO_CHAR(BUSINESSTYPE)) AS BUSINESSTYPE, COMMODITY_RULE,COMMODITY_CURRPRICE,COMMODITY_RETAILPRICE,COMMODITY_PURCHASEPRICE,OPERATE_DATE, HIGHWAY_STORAGE.FUNC_GETENUMVALUE('BUSINESSTYPE',TO_CHAR(BUSINESSTYPE)) AS BUSINESSTYPE_NAME, HIGHWAY_STORAGE.FUNC_GETENUMVALUE('COMMODITYSTATE',TO_CHAR(COMMODITY_STATE)) AS COMMODITY_STATE, NULL AS COMMODITY_ISVALID FROM HIGHWAY_STORAGE.V_COMMODITYAPPROVAL WHERE HIGHWAYPROINST_ID = " + action_data.ToDecrypt(); _DataTable = new Business.SERVERPART(_Transaction).ExecuteDataTable(SqlString); } else if (_StorageHIGHWAYPROINST.Select() && _StorageHIGHWAYPROINST.HIGHWAYPROINST_NEXTID == 9000) { #region 获取商品 string SqlString = string.Format( @"SELECT COMMODITY_TYPE,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_UNIT,COMMODITY_RULE, COMMODITYTYPE_NAME,COMMODITY_CURRPRICE,COMMODITY_RETAILPRICE,COMMODITY_PURCHASEPRICE, OPERATE_DATE,NULL AS COMMODITY_ISVALID,BUSINESSTYPE,SERVERPART_ID,HIGHWAYPROINST_ENDDATE, HIGHWAY_STORAGE.FUNC_GETENUMVALUE('COMMODITYSTATE',TO_CHAR(COMMODITY_STATE)) AS COMMODITY_STATE , HIGHWAY_STORAGE.FUNC_GETENUMVALUE('BUSINESSTYPE',TO_CHAR(BUSINESSTYPE)) AS BUSINESSTYPE_NAME FROM (SELECT D.SERVERPART_ID,D.SERVERPART_NAME,D.COMMODITYTYPE_NAME,D.COMMODITYTYPE_CODE, D.HIGHWAYPROINST_ID,D.MODIFYFROMSTORAGE_ID,D.COMMODITY_ID,D.COMMODITY_TYPE, D.COMMODITY_CODE,D.COMMODITY_NAME,D.COMMODITY_BARCODE,D.COMMODITY_SERVERCODE, D.COMMODITY_ALLNAME,D.COMMODITY_EN,D.COMMODITY_UNIT,D.COMMODITY_RULE, D.COMMODITY_ORI,D.COMMODITY_GRADE,D.COMMODITY_CURRPRICE,D.COMMODITY_ORIPRICE, D.COMMODITY_RETAILPRICE,D.COMMODITY_MAXPRICE,D.COMMODITY_MINPRICE, D.COMMODITY_PROMOTIONPRICE,D.COMMODITY_UNIFORMPRICE,D.COMMODITY_GROUPPRICE, D.COMMODITY_MEMBERPRICE,D.COMMODITY_PURCHASEPRICE,D.COMMODITY_STATE, D.COMMODITY_DESC,D.SUPPLIER_ID,D.COMMODITY_BRAND,D.DUTY_PARAGRAPH,D.RETAIL_DUTY, D.COMMODITY_COUNT,D.COMMODITY_FROZENCOUNT,D.ADDTIME,D.CANSALE,D.PROVINCE_CODE, D.BUSINESSTYPE,D.ISBULK,D.METERINGMETHOD,D.OPERATE_DATE,D.COMMODITY_SYMBOL,D.HIGHWAYPROINST_ENDDATE FROM (SELECT * FROM (SELECT A.SERVERPART_ID,A.SERVERPART_NAME,E.COMMODITYTYPE_NAME, E.COMMODITYTYPE_CODE,B.HIGHWAYPROINST_ID,C.MODIFYFROMSTORAGE_ID,D.COMMODITY_ID, D.COMMODITY_TYPE,D.COMMODITY_CODE,D.COMMODITY_NAME,D.COMMODITY_BARCODE, D.COMMODITY_SERVERCODE,D.COMMODITY_ALLNAME,D.COMMODITY_EN,D.COMMODITY_UNIT, D.COMMODITY_RULE,D.COMMODITY_ORI,D.COMMODITY_GRADE,D.COMMODITY_CURRPRICE, D.COMMODITY_ORIPRICE,D.COMMODITY_RETAILPRICE,D.COMMODITY_MAXPRICE,D.COMMODITY_MINPRICE, D.COMMODITY_PROMOTIONPRICE,D.COMMODITY_UNIFORMPRICE,D.COMMODITY_GROUPPRICE, D.COMMODITY_MEMBERPRICE,D.COMMODITY_PURCHASEPRICE,D.COMMODITY_STATE, D.COMMODITY_DESC,D.SUPPLIER_ID,D.COMMODITY_BRAND,D.DUTY_PARAGRAPH,D.RETAIL_DUTY, D.COMMODITY_COUNT,D.COMMODITY_FROZENCOUNT,D.ADDTIME,D.CANSALE,D.PROVINCE_CODE, D.BUSINESSTYPE,D.ISBULK,D.METERINGMETHOD,D.OPERATE_DATE,D.COMMODITY_SYMBOL, B.HIGHWAYPROINST_ENDDATE,ROW_NUMBER()OVER(PARTITION BY COMMODITY_ID ORDER BY B.HIGHWAYPROINST_ENDDATE DESC) AS COLNUM FROM HIGHWAY_STORAGE.T_SERVERPART A, HIGHWAY_STORAGE.T_HIGHWAYPROINST B, HIGHWAY_STORAGE.T_MODIFYFROMSTORAGE C, HIGHWAY_STORAGE.T_COMMODITY D, HIGHWAY_STORAGE.T_COMMODITYTYPE E WHERE A.SERVERPART_ID = B.DEPT_ID AND D.COMMODITY_TYPE = E.COMMODITYTYPE_ID AND B.HIGHWAYPROINST_ID = C.HIGHWAYPROINST_ID AND C.TABLE_NAME = 'T_COMMODITY' AND C.FIELD_NAME = 'COMMODITY_ID' AND C.FIELD_VALUE = D.COMMODITY_ID AND B.HIGHWAYPROINST_ID = {0})) D LEFT JOIN (SELECT * FROM (SELECT COMMODITY_ID,COMMODITY_TYPE,E.COMMODITYTYPE_NAME, COMMODITY_CODE,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_SERVERCODE, COMMODITY_ALLNAME,COMMODITY_EN,COMMODITY_UNIT,COMMODITY_RULE,COMMODITY_ORI, COMMODITY_GRADE,COMMODITY_CURRPRICE,COMMODITY_ORIPRICE,COMMODITY_RETAILPRICE, COMMODITY_MAXPRICE,COMMODITY_MINPRICE,COMMODITY_PROMOTIONPRICE,COMMODITY_UNIFORMPRICE, COMMODITY_GROUPPRICE,COMMODITY_MEMBERPRICE,COMMODITY_PURCHASEPRICE, COMMODITY_STATE,COMMODITY_DESC,SUPPLIER_ID,COMMODITY_BRAND,DUTY_PARAGRAPH, RETAIL_DUTY,COMMODITY_COUNT,COMMODITY_FROZENCOUNT,ADDTIME,CANSALE,PROVINCE_CODE, BUSINESSTYPE,ISBULK,METERINGMETHOD,OPERATE_DATE,COMMODITY_SYMBOL, ROW_NUMBER()OVER(PARTITION BY COMMODITY_ID ORDER BY OPERATE_DATE DESC) AS COLNUM FROM HIGHWAY_HISTORY.T_COMMODITY C,HIGHWAY_STORAGE.T_COMMODITYTYPE E WHERE C.COMMODITY_TYPE = E.COMMODITYTYPE_ID AND EXISTS(SELECT 1 FROM HIGHWAY_STORAGE.T_HIGHWAYPROINST A,HIGHWAY_STORAGE.T_MODIFYFROMSTORAGE B WHERE B.HIGHWAYPROINST_ID = A.HIGHWAYPROINST_ID AND B.TABLE_NAME = 'T_COMMODITY' AND B.FIELD_NAME = 'COMMODITY_ID' AND B.FIELD_VALUE = C.COMMODITY_ID AND B.HIGHWAYPROINST_ID = {0})) WHERE COLNUM = 1) F ON D.COMMODITY_ID = F.COMMODITY_ID WHERE D.COLNUM = 1 UNION ALL SELECT SERVERPART_ID,SERVERPART_NAME,COMMODITYTYPE_NAME,COMMODITYTYPE_CODE, HIGHWAYPROINST_ID,MODIFYFROMSTORAGE_ID,COMMODITY_ID,COMMODITY_TYPE,COMMODITY_CODE, COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_SERVERCODE,COMMODITY_ALLNAME,COMMODITY_EN, COMMODITY_UNIT,COMMODITY_RULE,COMMODITY_ORI,COMMODITY_GRADE,COMMODITY_CURRPRICE, COMMODITY_ORIPRICE,COMMODITY_RETAILPRICE,COMMODITY_MAXPRICE,COMMODITY_MINPRICE, COMMODITY_PROMOTIONPRICE,COMMODITY_UNIFORMPRICE,COMMODITY_GROUPPRICE, COMMODITY_MEMBERPRICE,COMMODITY_PURCHASEPRICE,COMMODITY_STATE,COMMODITY_DESC, SUPPLIER_ID,COMMODITY_BRAND,DUTY_PARAGRAPH,RETAIL_DUTY,COMMODITY_COUNT, COMMODITY_FROZENCOUNT,ADDTIME,CANSALE,PROVINCE_CODE,BUSINESSTYPE,ISBULK, METERINGMETHOD,OPERATE_DATE,COMMODITY_SYMBOL,HIGHWAYPROINST_ENDDATE FROM (SELECT A.SERVERPART_ID,A.SERVERPART_NAME,E.COMMODITYTYPE_NAME,E.COMMODITYTYPE_CODE, B.HIGHWAYPROINST_ID,C.MODIFYFROMSTORAGE_ID,D.COMMODITY_ID,D.COMMODITY_TYPE, D.COMMODITY_CODE,D.COMMODITY_NAME,D.COMMODITY_BARCODE,D.COMMODITY_SERVERCODE, D.COMMODITY_ALLNAME,D.COMMODITY_EN,D.COMMODITY_UNIT,D.COMMODITY_RULE,D.COMMODITY_ORI, D.COMMODITY_GRADE,D.COMMODITY_CURRPRICE,D.COMMODITY_ORIPRICE,D.COMMODITY_RETAILPRICE, D.COMMODITY_MAXPRICE,D.COMMODITY_MINPRICE,D.COMMODITY_PROMOTIONPRICE,D.COMMODITY_UNIFORMPRICE, D.COMMODITY_GROUPPRICE,D.COMMODITY_MEMBERPRICE,D.COMMODITY_PURCHASEPRICE,D.COMMODITY_STATE, D.COMMODITY_DESC,D.SUPPLIER_ID,D.COMMODITY_BRAND,D.DUTY_PARAGRAPH,D.RETAIL_DUTY, D.COMMODITY_COUNT,D.COMMODITY_FROZENCOUNT,D.ADDTIME,D.CANSALE,D.PROVINCE_CODE, D.BUSINESSTYPE,D.ISBULK,D.METERINGMETHOD,D.OPERATE_DATE,D.COMMODITY_SYMBOL,B.HIGHWAYPROINST_ENDDATE FROM HIGHWAY_STORAGE.T_SERVERPART A, HIGHWAY_STORAGE.T_HIGHWAYPROINST B, HIGHWAY_STORAGE.T_MODIFYFROMSTORAGE C, (SELECT * FROM (SELECT COMMODITY_ID,COMMODITY_TYPE, COMMODITY_CODE,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_SERVERCODE, COMMODITY_ALLNAME,COMMODITY_EN,COMMODITY_UNIT,COMMODITY_RULE,COMMODITY_ORI, COMMODITY_GRADE,COMMODITY_CURRPRICE,COMMODITY_ORIPRICE,COMMODITY_RETAILPRICE, COMMODITY_MAXPRICE,COMMODITY_MINPRICE,COMMODITY_PROMOTIONPRICE,COMMODITY_UNIFORMPRICE, COMMODITY_GROUPPRICE,COMMODITY_MEMBERPRICE,COMMODITY_PURCHASEPRICE, COMMODITY_STATE,COMMODITY_DESC,SUPPLIER_ID,COMMODITY_BRAND,DUTY_PARAGRAPH, RETAIL_DUTY,COMMODITY_COUNT,COMMODITY_FROZENCOUNT,ADDTIME,CANSALE,PROVINCE_CODE, BUSINESSTYPE,ISBULK,METERINGMETHOD,OPERATE_DATE,COMMODITY_SYMBOL, ROW_NUMBER()OVER(PARTITION BY COMMODITY_ID ORDER BY OPERATE_DATE) AS COLNUM FROM HIGHWAY_HISTORY.T_COMMODITY WHERE OPERATE_DATE > TO_DATE('{1}','YYYY-MM-DD HH24:MI:SS')) WHERE COLNUM = 1) D LEFT JOIN (SELECT * FROM (SELECT COMMODITY_ID,COMMODITY_TYPE,E.COMMODITYTYPE_NAME, COMMODITY_CODE,COMMODITY_NAME,COMMODITY_BARCODE,COMMODITY_SERVERCODE, COMMODITY_ALLNAME,COMMODITY_EN,COMMODITY_UNIT,COMMODITY_RULE,COMMODITY_ORI, COMMODITY_GRADE,COMMODITY_CURRPRICE,COMMODITY_ORIPRICE,COMMODITY_RETAILPRICE, COMMODITY_MAXPRICE,COMMODITY_MINPRICE,COMMODITY_PROMOTIONPRICE,COMMODITY_UNIFORMPRICE, COMMODITY_GROUPPRICE,COMMODITY_MEMBERPRICE,COMMODITY_PURCHASEPRICE, COMMODITY_STATE,COMMODITY_DESC,SUPPLIER_ID,COMMODITY_BRAND,DUTY_PARAGRAPH, RETAIL_DUTY,COMMODITY_COUNT,COMMODITY_FROZENCOUNT,ADDTIME,CANSALE,PROVINCE_CODE, BUSINESSTYPE,ISBULK,METERINGMETHOD,OPERATE_DATE,COMMODITY_SYMBOL, ROW_NUMBER()OVER(PARTITION BY COMMODITY_ID ORDER BY OPERATE_DATE DESC) AS COLNUM FROM HIGHWAY_HISTORY.T_COMMODITY C,HIGHWAY_STORAGE.T_COMMODITYTYPE E WHERE C.COMMODITY_TYPE = E.COMMODITYTYPE_ID AND EXISTS(SELECT 1 FROM HIGHWAY_STORAGE.T_HIGHWAYPROINST A,HIGHWAY_STORAGE.T_MODIFYFROMSTORAGE B WHERE B.HIGHWAYPROINST_ID = A.HIGHWAYPROINST_ID AND B.TABLE_NAME = 'T_COMMODITY' AND B.FIELD_NAME = 'COMMODITY_ID' AND B.FIELD_VALUE = C.COMMODITY_ID AND B.HIGHWAYPROINST_ID = {0}))) F ON D.COMMODITY_ID = F.COMMODITY_ID AND F.OPERATE_DATE < TO_DATE('{1}','YYYY-MM-DD HH24:MI:SS'), HIGHWAY_STORAGE.T_COMMODITYTYPE E WHERE A.SERVERPART_ID = B.DEPT_ID AND D.COMMODITY_TYPE = E.COMMODITYTYPE_ID AND B.HIGHWAYPROINST_ID = C.HIGHWAYPROINST_ID AND C.TABLE_NAME = 'T_COMMODITY' AND C.FIELD_NAME = 'COMMODITY_ID' AND C.FIELD_VALUE = D.COMMODITY_ID AND D.OPERATE_DATE > B.HIGHWAYPROINST_ENDDATE AND B.HIGHWAYPROINST_ID = {0}) WHERE ROWNUM <= 1)", _StorageHIGHWAYPROINST.HIGHWAYPROINST_ID, _StorageHIGHWAYPROINST.HIGHWAYPROINST_ENDDATE); DataTable COMMODITYAPPROVALLIST = _StorageHIGHWAYPROINST.ExecuteDataTable(SqlString); _DataTable = COMMODITYAPPROVALLIST.Clone(); #endregion //门店信息 DataTable ServerpartShop = _StorageHIGHWAYPROINST.ExecuteDataTable( "SELECT * FROM HIGHWAY_STORAGE.T_SERVERPARTSHOP"); Business.SERVERPART _SERVERPART = new Business.SERVERPART(_Transaction); _SERVERPART.SERVERPART_ID = _StorageHIGHWAYPROINST.Dept_ID; if (_SERVERPART.Select()) { //基础信息信息 CONFIGURATION _CONFIGURATION = new CONFIGURATION(_Transaction); DataTable HighwayProinstMachine = _CONFIGURATION.ExecuteDataTable( "SELECT * FROM HIGHWAY_EXCHANGE.T_BASEINFOFEEDBACK WHERE SERVERPART_CODE = '" + _SERVERPART.SERVERPART_CODE + "' AND TABLE_NAME = 'T_COMMODITY'"); for (int RowCount = 0; RowCount < COMMODITYAPPROVALLIST.Rows.Count; RowCount++) { string commodityisvalid = null; foreach (DataRow shopid in ServerpartShop.Select("SERVERPART_ID = " + COMMODITYAPPROVALLIST.Rows[RowCount]["SERVERPART_ID"].ToString() + " AND SHOPTRADE = '" + COMMODITYAPPROVALLIST.Rows[RowCount]["BUSINESSTYPE"].ToString() + "'")) { string restring = null, errorstring = null; foreach (DataRow machine in HighwayProinstMachine.Select("SERVERPARTSHOP_ID = " + shopid.ItemArray[0])) { DateTime HIGHWAYPROINST_ENDDATE = Convert.ToDateTime( COMMODITYAPPROVALLIST.Rows[RowCount]["HIGHWAYPROINST_ENDDATE"].ToString()); DateTime MACHINE_ENDDATE = Convert.ToDateTime(machine.ItemArray[10].ToString()); if (DateTime.Compare(MACHINE_ENDDATE, HIGHWAYPROINST_ENDDATE) >= 0) { restring += (string.IsNullOrEmpty(restring) ? "" : ",") + machine.ItemArray[6].ToString(); } else { errorstring += (string.IsNullOrEmpty(errorstring) ? "" : ",") + machine.ItemArray[6].ToString(); } } if (!string.IsNullOrEmpty(restring)) { commodityisvalid += shopid.ItemArray[2].ToString() + "(已生效)" + (string.IsNullOrEmpty(restring) ? "" : "：") + restring + "
"; } if (!string.IsNullOrEmpty(errorstring)) { commodityisvalid += shopid.ItemArray[2].ToString() + "(未生效)" + (string.IsNullOrEmpty(errorstring) ? "" : "：") + errorstring + "
"; } } COMMODITYAPPROVALLIST.Rows[RowCount]["COMMODITY_ISVALID"] = commodityisvalid; _DataTable.Rows.Add(COMMODITYAPPROVALLIST.Rows[RowCount].ItemArray); } } } reString = JsonHelper.DataTableToJson(_DataTable, "CommodityList"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 按员工内码获取账号反馈 case "GetApproveCashWorkerByID": try { //判断存在商品 Business.CASHWORKER _CASHWORKER = new Business.CASHWORKER(_Transaction); _CASHWORKER.CASHWORKER_ID_Encrypt = action_data; if (_CASHWORKER.Select()) { DataTable CashWorker = _CASHWORKER.ExecuteDataTable( "SELECT OPERATE_ID,OPERATE_NAME,OPERATE_DATE FROM HIGHWAY_STORAGE.T_CASHWORKER WHERE CASHWORKER_ID =" + action_data.ToDecrypt()); Business.SERVERPART _SERVERPART = new Business.SERVERPART(_Transaction); _SERVERPART.SERVERPART_ID = _CASHWORKER.SERVERPART_ID; if (_SERVERPART.Select()) { //基础信息信息 CONFIGURATION _CONFIGURATION = new CONFIGURATION(_Transaction); DataTable HighwayProinstMachine = _CONFIGURATION.ExecuteDataTable( "SELECT * FROM HIGHWAY_EXCHANGE.T_BASEINFOFEEDBACK WHERE SERVERPART_CODE = '" + _SERVERPART.SERVERPART_CODE + "' AND TABLE_NAME = 'T_CASHWORKER'"); //获取关联门店 DataTable RTCASHSHOP = _SERVERPART.ExecuteDataTable( "SELECT SERVERPARTSHOP_ID FROM HIGHWAY_STORAGE.T_RTCASHSHOP WHERE CASHWORKER_ID = " + action_data.ToDecrypt()); //新建datatabel DataTable tblDatas = new DataTable("Datas"); tblDatas.Columns.Add("SHOPNAME", Type.GetType("System.String")); tblDatas.Columns.Add("CASHWORKER_VAILD", Type.GetType("System.String")); tblDatas.Columns.Add("CASHWORKER_INVAILD", Type.GetType("System.String")); if (RTCASHSHOP.Rows.Count > 0) { //获取下发情况 foreach (Business.SERVERPARTSHOP _SERVERPARTSHOP in new Business.SERVERPARTSHOP(_Transaction).FillCollection( @"WHERE SERVERPARTSHOP_ID IN(SELECT SERVERPARTSHOP_ID FROM HIGHWAY_STORAGE.T_RTCASHSHOP WHERE CASHWORKER_ID = " + action_data.ToDecrypt() + ")")) { string restring = "", errorstring = ""; for (int RowCount = 0; RowCount < HighwayProinstMachine.Rows.Count; RowCount++) { if (HighwayProinstMachine.Rows[RowCount]["SERVERPARTSHOP_ID"].ToString() == _SERVERPARTSHOP.SERVERPARTSHOP_ID.ToString() && !string.IsNullOrEmpty(CashWorker.Rows[0][2].ToString())) { DateTime HIGHWAYPROINST_ENDDATE = Convert.ToDateTime(CashWorker.Rows[0][2].ToString()); DateTime MACHINE_ENDDATE = Convert.ToDateTime( HighwayProinstMachine.Rows[RowCount]["MACHINE_ENDDATE"].ToString()); if (DateTime.Compare(MACHINE_ENDDATE, HIGHWAYPROINST_ENDDATE) >= 0) { restring += (string.IsNullOrEmpty(restring) ? "" : ",") + HighwayProinstMachine.Rows[RowCount]["MACHINECODE"].ToString(); } else { errorstring += (string.IsNullOrEmpty(errorstring) ? "" : ",") + HighwayProinstMachine.Rows[RowCount]["MACHINECODE"].ToString(); ; } } } tblDatas.Rows.Add(new object[] { _SERVERPARTSHOP.SHOPNAME, restring, errorstring }); } } else { //获取下发情况 foreach (Business.SERVERPARTSHOP _SERVERPARTSHOP in new Business.SERVERPARTSHOP(_Transaction).FillCollection( "WHERE SERVERPART_ID = " + _SERVERPART.SERVERPART_ID)) { string restring = "", errorstring = ""; for (int RowCount = 0; RowCount < HighwayProinstMachine.Rows.Count; RowCount++) { if (HighwayProinstMachine.Rows[RowCount]["SERVERPARTSHOP_ID"].ToString() == _SERVERPARTSHOP.SERVERPARTSHOP_ID.ToString() && !string.IsNullOrEmpty(CashWorker.Rows[0][2].ToString())) { DateTime HIGHWAYPROINST_ENDDATE = Convert.ToDateTime(CashWorker.Rows[0][2].ToString()); DateTime MACHINE_ENDDATE = Convert.ToDateTime( HighwayProinstMachine.Rows[RowCount]["MACHINE_ENDDATE"].ToString()); if (DateTime.Compare(MACHINE_ENDDATE, HIGHWAYPROINST_ENDDATE) >= 0) { restring += (string.IsNullOrEmpty(restring) ? "" : ",") + HighwayProinstMachine.Rows[RowCount]["MACHINECODE"].ToString(); } else { errorstring += (string.IsNullOrEmpty(errorstring) ? "" : ",") + HighwayProinstMachine.Rows[RowCount]["MACHINECODE"].ToString(); ; } } } tblDatas.Rows.Add(new object[] { _SERVERPARTSHOP.SHOPNAME, restring, errorstring }); } } reString = JsonHelper.DataTableToJson(tblDatas, "CashWorkList"); } } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 按商品获取快捷键反馈 case "GetApproveHotKeyByID": try { //判断存在商品 Business.COMMODITY _COMMODITY = new Business.COMMODITY(_Transaction); _COMMODITY.COMMODITY_ID_Encrypt = action_data; if (_COMMODITY.Select()) { //判断存在服务区 Business.SERVERPART _SERVERPART = new Business.SERVERPART(_Transaction); _SERVERPART.SERVERPART_ID = _COMMODITY.SERVERPART_ID; if (_SERVERPART.Select()) { DataTable V_UNIQUECOMMODITY = _COMMODITY.ExecuteDataTable( @"SELECT OPERATE_ID,OPERATE_NAME,OPERATE_DATE,SERVERPART_ID,BUSINESSTYPE FROM HIGHWAY_STORAGE.V_UNIQUECOMMODITY WHERE COMMODITY_ID = " + action_data.ToDecrypt()); //基础信息信息 CONFIGURATION _CONFIGURATION = new CONFIGURATION(_Transaction); DataTable HighwayProinstMachine = _CONFIGURATION.ExecuteDataTable( "SELECT * FROM HIGHWAY_EXCHANGE.T_BASEINFOFEEDBACK WHERE SERVERPART_CODE = '" + _SERVERPART.SERVERPART_CODE + "' AND TABLE_NAME = 'T_HOTKEYSET'"); //+ (V_UNIQUECOMMODITY.Rows.Count == 1 ? " AND STAFF_ID = " + // V_UNIQUECOMMODITY.Rows[0]["OPERATE_ID"].ToString() + " AND STAFF_NAME = '" + // V_UNIQUECOMMODITY.Rows[0]["OPERATE_NAME"].ToString() + "' AND TABLE_NAME = 'T_HOTKEYSET'" : "")) //新建datatable DataTable tblDatas = new DataTable("Datas"); tblDatas.Columns.Add("SHOPNAME", Type.GetType("System.String")); tblDatas.Columns.Add("HotKey_VAILD", Type.GetType("System.String")); tblDatas.Columns.Add("HotKey_INVAILD", Type.GetType("System.String")); //获取下发情况 foreach (Business.SERVERPARTSHOP _SERVERPARTSHOP in new Business.SERVERPARTSHOP(_Transaction).FillCollection( "WHERE SHOPTRADE = " + V_UNIQUECOMMODITY.Rows[0]["BUSINESSTYPE"].ToString() + " AND SERVERPART_ID = " + V_UNIQUECOMMODITY.Rows[0]["SERVERPART_ID"].ToString())) { string restring = "", errorstring = ""; for (int RowCount = 0; RowCount < HighwayProinstMachine.Rows.Count; RowCount++) { if (HighwayProinstMachine.Rows[RowCount]["SERVERPARTSHOP_ID"].ToString() == _SERVERPARTSHOP.SERVERPARTSHOP_ID.ToString()) { DateTime HIGHWAYPROINST_ENDDATE = Convert.ToDateTime( V_UNIQUECOMMODITY.Rows[0]["OPERATE_DATE"].ToString()); DateTime MACHINE_ENDDATE = Convert.ToDateTime( HighwayProinstMachine.Rows[RowCount]["MACHINE_ENDDATE"].ToString()); if (DateTime.Compare(MACHINE_ENDDATE, HIGHWAYPROINST_ENDDATE) >= 0) { restring += (string.IsNullOrEmpty(restring) ? "" : ",") + HighwayProinstMachine.Rows[RowCount]["MACHINECODE"].ToString(); } else { errorstring += (string.IsNullOrEmpty(errorstring) ? "" : ",") + HighwayProinstMachine.Rows[RowCount]["MACHINECODE"].ToString(); } } } tblDatas.Rows.Add(new object[] { _SERVERPARTSHOP.SHOPNAME, restring, errorstring }); restring = ""; errorstring = ""; } reString = JsonHelper.DataTableToJson(tblDatas, "HotKeyList"); } } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 按商品获取业务流程 case "GetProinstByCommodity": try { _DataTable = new Business.COMMODITY(_Transaction).ExecuteDataTable( @"SELECT A.DEPT_NAME,A.HIGHWAYPROINST_ID,A.STAFF_NAME,A.HIGHWAYPROINST_CREATEDATE, CASE WHEN A.HIGHWAYPROINST_NEXTID = 1000 THEN '业务受理' WHEN A.HIGHWAYPROINST_NEXTID = 2000 THEN '业务审核' WHEN A.HIGHWAYPROINST_NEXTID = 3000 THEN '业务复审' END AS HIGHWAYPROINST_NEXTID, CASE WHEN A.ACCEPT_CODE = '100100' THEN '商品新增审批' WHEN A.ACCEPT_CODE = '100200' THEN '信息修改审批' WHEN A.ACCEPT_CODE = '100201' THEN '商品调价审批' END AS ACCEPT_CODE FROM HIGHWAY_RUNNING.T_HIGHWAYPROINST A, HIGHWAY_RUNNING.T_MODIFYFROMSTORAGE B, HIGHWAY_RUNNING.T_COMMODITY C, HIGHWAY_STORAGE.T_COMMODITYTYPE D WHERE A.HIGHWAYPROINST_ID = B.HIGHWAYPROINST_ID AND B.TABLE_NAME = 'T_COMMODITY' AND B.FIELD_NAME = 'COMMODITY_ID' AND B.FIELD_VALUE = C.COMMODITY_ID AND C.COMMODITY_TYPE = D.COMMODITYTYPE_ID(+) AND C.COMMODITY_ID = " + action_data); if (_DataTable.Rows.Count == 1) { reString = JsonHelper.DataTableToJson(_DataTable, "PRONISTCOMMPDITY"); } } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取同条码商品信息 case "getServerpartCommodityList": try { //获得枚举信息 _DataTable = new Business.COMMODITY(_Transaction).ExecuteDataTable( "SELECT * FROM V_COMMODITY A WHERE COMMODITY_STATE = 1 AND COMMODITY_ID != " + action_data.ToDecrypt() + " AND EXISTS (SELECT 1 FROM T_COMMODITY B WHERE B.COMMODITY_ID = " + action_data.ToDecrypt() + " AND A.COMMODITY_BARCODE = B.COMMODITY_BARCODE AND A.PROVINCE_CODE = B.PROVINCE_CODE)"); reString = JsonHelper.DataTableToJson(_DataTable, "COMMODITY"); } catch (Exception ex) { reString = "error:" + ex.Message; } break; #endregion #region 获取视频图像信息 case "GetVedioImg": reString = CommonInterface.BaseInformation.GetVedioImg(_Transaction); break; #endregion #region 获取数据校验的单品数据、香烟数据、移动支付、促销数据 case "GetEndAccountDate": reString = CommonInterface.BaseInformation.GetEndAccountDate(_Transaction); break; #endregion #region 阳光公示点赞操作 case "ThumbUp": reString = CommonInterface.Publicity.ThumbUp(_Transaction); break; #endregion #region 阳光公示添加评论 case "AddComment": reString = CommonInterface.Publicity.AddComment(_Transaction); break; #endregion default: reString = "error"; break; } sw.Stop(); HZQR.Common.LogUtil.WriteLog(null, action_type + "接口耗时：" + sw.Elapsed.TotalSeconds.ToString()); if (_Transaction.IsOpen) { _Transaction.Commit(); } } catch (Exception ex) { SuperMap.RealEstate.Utility.ErrorLogHelper.Write(ex); } finally { _Transaction.Release(); _Transaction.Dispose(); } context.Response.ContentType = "text/plain"; context.Response.Write(reString); context.Response.End(); } #region 方法 -> 获取服务区类型 private string getServerPart(string param) { // string RetString = ""; switch (param) { case "第一区域": break; case "第二区域": break; case "第三区域": break; case "第四区域": break; case "A类": break; case "B类": break; case "C类": break; } return RetString; } #endregion #region 方法 -> 根据分类方式或得对应的分类 private string getServerPartTypeByCode(string param, ServiceModel.Transaction _Transaction, string action_record) { List _ListSERVERPARTTYPE = new List(); if (action_record != "all") { foreach (Business.SERVERPARTTYPE _SERVERPARTTYPE in (new Business.SERVERPARTTYPE(_Transaction)).FillCollection( "where TYPE_PID=" + param.ToDecrypt() + " AND ISMAP =1 and rownum <= " + action_record + " order by TYPE_INDEX")) { _ListSERVERPARTTYPE.Add(_SERVERPARTTYPE.PropertyObject); } } else { foreach (Business.SERVERPARTTYPE _SERVERPARTTYPE in (new Business.SERVERPARTTYPE(_Transaction)).FillCollection( "where TYPE_PID=" + param.ToDecrypt() + " AND ISMAP =1 order by TYPE_INDEX")) { _ListSERVERPARTTYPE.Add(_SERVERPARTTYPE.PropertyObject); } } return JsonHelper.ListToJson(_ListSERVERPARTTYPE, "SERVERPARTTYPE"); } #endregion #region 方法 -> 根据分类获得对应的服务区 private string getServerPartByType(string param, ServiceModel.Transaction _Transaction) { //根据类别获得对应的服务区 List _ListSERVERPART = new List(); string OutTypeString = param.ToDecrypt(); //获得子类 GetSubServerPartType(ref OutTypeString, param.ToDecrypt(), "", true, _Transaction); //获得类别对应的某一个类别下面的数据 foreach (Business.SERVERPART _SERVERPART in (new Business.SERVERPART(_Transaction)).FillCollection( "where SERVERPART_ID in (select SERVERPART_ID from HIGHWAY_STORAGE.T_SPSTATICTYPE where SERVERPARTTYPE_ID IN( " + OutTypeString + ")) order by SERVERPART_INDEX,SERVERPART_CODE")) { //获得图片的路径 // string sqlString = @"SELECT * FROM (select A.IMAGE_PATH FROM T_IMAGE A,T_SERVERPARTTYPE B,T_SPSTATICTYPE C // WHERE A.IMAGE_ID = B.STATIC_TYPE AND B.SERVERPARTTYPE_ID = C.SERVERPARTTYPE_ID AND C.SERVERPART_ID=" + // _SERVERPART.SERVERPART_ID + " AND C.SERVERPARTTYPE_ID IN( " + OutTypeString + // ") ORDER BY C.SERVERPARTTYPE_ID DESC) where ROWNUM =1"; //DataTable _DataTable = (new Business.SERVERPART(_Transaction)).ExecuteDataTable(sqlString); //if (_DataTable.Rows.Count != 0) //{ // _SERVERPART. SERVERPART_IPADDRESS = _DataTable.Rows[0][0].ToString(); //} //else //{ // _SERVERPART.SERVERPART_IPADDRESS = ""; //} _SERVERPART.SERVERPART_IPADDRESS = ""; _SERVERPART.PropertyObject.SERVERPART_DESC = ""; _ListSERVERPART.Add(_SERVERPART.PropertyObject); } return JsonHelper.ListToJson(_ListSERVERPART, "SERVERPART"); } #endregion #region 方法 -> 根据查询条件获得对应的服务区 private string getServerPartByName(string param, ServiceModel.Transaction _Transaction) { //根据类别获得对应的服务区 List _ListSERVERPART = new List(); string OutTypeString = param; OutTypeString = "SERVERPART_NAME LIKE '%" + OutTypeString + "%' OR SERVERPART_ADDRESS LIKE '%" + OutTypeString + "%' OR EXPRESSWAY_NAME LIKE '%" + OutTypeString + "%' OR OWNEDCOMPANY LIKE '%" + OutTypeString + "%' OR MANAGERCOMPANY LIKE '%" + OutTypeString + "%'"; foreach (Business.SERVERPART _SERVERPART in (new Business.SERVERPART(_Transaction)).FillCollection( "WHERE " + OutTypeString + " ORDER BY SERVERPART_INDEX,SERVERPART_CODE")) { //获得图片的路径 // string sqlString = @"SELECT * FROM (select A.IMAGE_PATH FROM T_IMAGE A,T_SERVERPARTTYPE B,T_SPSTATICTYPE C // WHERE A.IMAGE_ID = B.STATIC_TYPE AND B.SERVERPARTTYPE_ID = C.SERVERPARTTYPE_ID AND C.SERVERPART_ID=" + // _SERVERPART.SERVERPART_ID + " AND C.SERVERPARTTYPE_ID IN( " + OutTypeString + // ") ORDER BY C.SERVERPARTTYPE_ID DESC) where ROWNUM =1"; //DataTable _DataTable = (new Business.SERVERPART(_Transaction)).ExecuteDataTable(sqlString); //if (_DataTable.Rows.Count != 0) //{ // _SERVERPART. SERVERPART_IPADDRESS = _DataTable.Rows[0][0].ToString(); //} //else //{ // _SERVERPART.SERVERPART_IPADDRESS = ""; //} _SERVERPART.SERVERPART_IPADDRESS = ""; _SERVERPART.PropertyObject.SERVERPART_DESC = ""; _ListSERVERPART.Add(_SERVERPART.PropertyObject); } return JsonHelper.ListToJson(_ListSERVERPART, "SERVERPART"); } #endregion #region 方法 -> 获得服务区类别 public void GetSubServerPartType(ref string RetString, string Pid, string obscureName, bool isValid, ServiceModel.Transaction _Transaction, DataTable _SearchDataTable = null) { //由于数据量商品类别不可能太多，所以一次性全部取出 if (_SearchDataTable == null) { _SearchDataTable = GetServerPartTypeDataSource(_Transaction); } string SearchString = "TYPE_PID = '" + Pid + "'" + (obscureName == "" ? "" : " AND TYPE_NAME LIKE '" + obscureName + "'") + (isValid == true ? " AND TYPE_STATE = '1'" : ""); //("columnname1 like '%xx%'"); DataRow[] _DataRowList = _SearchDataTable.Select(SearchString); if (_DataRowList.Length == 0) { return; } else { foreach (DataRow _CurrRow in _DataRowList) { RetString += RetString == "" ? _CurrRow["SERVERPARTTYPE_ID"].ToString() : "," + _CurrRow["SERVERPARTTYPE_ID"].ToString(); GetSubServerPartType(ref RetString, _CurrRow["SERVERPARTTYPE_ID"].ToString(), obscureName, isValid, _Transaction, _SearchDataTable); } } } private DataTable GetServerPartTypeDataSource(ServiceModel.Transaction _Transaction) { string StrSql = "select SERVERPARTTYPE_ID,TYPE_NAME,TYPE_STATE,TYPE_PID from HIGHWAY_STORAGE.T_SERVERPARTTYPE order by TYPE_PID,TYPE_INDEX,TYPE_NAME"; return (new Business.SERVERPARTTYPE(_Transaction)).ExecuteDataTable(StrSql, null, 0, 0); } #endregion public bool IsReusable { get { return false; } } public static bool CheckValidationResult(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors errors) { return true; } #region 方法 -> 生成验证码 ///

/// 生成验证码 /// 位数 /// 验证码字符串 private string CreateRandomCode(int n) { string[] CharArray = charSet.Split(','); string randomCode = ""; int temp = -1; Random rand = new Random(); for (int i = 0; i < n; i++) { if (temp != -1) { rand = new Random(i * temp * ((int)DateTime.Now.Ticks)); } int t = rand.Next(CharArray.Length - 1); if (temp == t) { return CreateRandomCode(n); } temp = t; randomCode += CharArray[t]; } return randomCode; } #endregion #region 方法 -> 日期格式转换 ///

/// 将DateTime类型转换为long类型 ///

/// 时间 /// public static long ConvertDataTimeLong(DateTime dt) { DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1)); TimeSpan toNow = dt.Subtract(dtStart); long timeStamp = toNow.Ticks; timeStamp = long.Parse(timeStamp.ToString().Substring(0, timeStamp.ToString().Length - 7)); return timeStamp; } ///

/// 将Long类型转换为DateTime类型 ///

/// long /// s public static DateTime ConvertLongDateTime(long d) { DateTime dtStart = TimeZone.CurrentTimeZone.ToLocalTime(new DateTime(1970, 1, 1)); long lTime = long.Parse(d + "0000000"); TimeSpan toNow = new TimeSpan(lTime); DateTime dtResult = dtStart.Add(toNow); return dtResult; } #endregion #region 创建md5 16位大写加密 public static string CreateMD5(string text) { var md5 = new System.Security.Cryptography.MD5CryptoServiceProvider(); //换成utf8后对于中文加密也适用 byte[] output = md5.ComputeHash(System.Text.Encoding.UTF8.GetBytes(text)); string pass2md5 = BitConverter.ToString(output, 4, 8).Replace("-", ""); //pass2md5 = pass2md5.ToUpper(); return pass2md5; } #endregion } }