using System;
using System.Configuration;
using System.Data;
using System.Linq;
using SuperMap.RealEstate.ServiceModel;
using SuperMap.RealEstate.CoreFrameWork;
using SuperMap.RealEstate.CoreFrameWork.Dictionary.Business;
using SuperMap.RealEstate.HighWay.SellData.Business;
using SuperMap.RealEstate.HighWay.Storage.Business;
using ServerPartTransmission.Common;
namespace ServerPartTransmission.Method
{
public class DownloadMethod
{
#region 方法 -> 数据下发子项 (分页分发)
///
/// 数据下发子项 (分页分发)
///
///
///
///
///
///
///
public static string DataGeneration(OracleHelper _OracleHelper, string tableName,
string lastTime, string[] codes, int maxDataDownload = 10, Transaction _Transaction = null)
{
string strBack = string.Empty;
string strIsServerPart = string.Empty;
try
{
strIsServerPart = ConfigurationManager.AppSettings["IsServerPart"].ToString();
}
catch
{
strIsServerPart = "1";
}
int minimum = (string.IsNullOrWhiteSpace(codes[5]) ? 0 : (int.Parse(codes[5]) - 1)) * 100 * maxDataDownload; //默认分页启始值
int maximum = (string.IsNullOrWhiteSpace(codes[5]) ? 1 : int.Parse(codes[5])) * 100 * maxDataDownload; //默认分页结束值
//string _strProvinceCode = "";
//try
//{
// _strProvinceCode = ConfigurationManager.AppSettings["ProvinceCode"].ToString();
//}
//catch { }
//服务区编码集合
string _strServerPart = "";
string _strServerPartOld = "";
//上次更新时间
string _strLastTime = "";
foreach (string ServerPartCode in codes[0].Split(','))
{
_strServerPart += (string.IsNullOrWhiteSpace(_strServerPart) ? "'" : ",'") + ServerPartCode + "'";
_strServerPartOld += (string.IsNullOrWhiteSpace(_strServerPartOld) ? "'" : ",'") + ServerPartCode + "'";
}
DataSet _DataSet = null;
DataTable _DataTable = null;
try
{
switch (tableName)
{
case "HIGHWAY_EXCHANGE.T_DOWNUPDATE":
#region HIGHWAY_EXCHANGE.T_DOWNUPDATE
if (!string.IsNullOrWhiteSpace(codes[6]) && codes[6].Split('.').Count() > 1)
{
string _strTableName = codes[6].Split('.')[1];
switch (_strTableName)
{
case "T_COMMODITYEX":
_strTableName = "T_COMMODITYEX_EX";
break;
case "T_SHOPMESSAGE":
_strTableName = "T_SHOPMESSAGE_EX";
break;
case "T_SELLWORKER":
_strTableName = "T_SELLWORKER_EX";
break;
case "T_HOTKEYSET":
_strTableName = "T_HOTKEYSET_EX";
break;
case "T_SALESPROMOTE":
_strTableName = "T_SALESPROMOTE_EX";
break;
}
switch (_strTableName)
{
case "T_MEMBERSHIP":
//注：此处SQL语句待会员系统改版后需要去除业主单位条件的注释
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT MAX(A.OPERATE_DATE) AS VERSIONUPDATE_DATE
FROM MEMBERSHIP_STORAGE.T_MEMBERSHIP A
--WHERE OWNERUNIT_CODE IN({_strServerPart})");
break;
case "T_RECHARGERECORD":
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
@"SELECT MAX(A.RECODE_DATE) AS VERSIONUPDATE_DATE
FROM MEMBERSHIP_STORAGE.T_RECHARGERECORD A
WHERE A.RECODE_TYPE IN (1010,2000,2010,3010) AND
A.RECODE_DATE >= TRUNC(SYSDATE) - 60");
break;
case "T_ICCARDINFO":
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT MAX(A.OPERATE_DATE) AS VERSIONUPDATE_DATE
FROM MEMBERSHIP_STORAGE.T_ICCARDINFO A");
break;
case "T_COMPANYAUTHORIZE":
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT MAX(A.OPERATE_DATE) AS VERSIONUPDATE_DATE
FROM MEMBERSHIP_STORAGE.T_COMPANYAUTHORIZE A
WHERE A.SERVERPART_CODE IN ({_strServerPart})");
break;
default:
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT DOWNLOADUPDATE_ID,PROVINCE_CODE,
SERVERPARTCODE,SHOPCODE,MACHINECODE,
MACHINE_MACADDRESS,TABLENAME,VERSIONUPDATE_DATE,
STAFF_ID,STAFF_NAME,CREATE_DATE,DOWNLOADUPDATE_DESC
FROM HIGHWAY_EXCHANGE.T_DOWNLOADUPDATE
WHERE SERVERPARTCODE IN ({_strServerPart}) AND
TABLENAME IN ('{_strTableName.Replace(",", "','")}')
ORDER BY VERSIONUPDATE_DATE DESC");
break;
}
//if (_Data != null && _Data.Tables[0].Rows.Count == 0)
//{
// DataRow _DataRow = _Data.Tables[0].NewRow();
// _DataRow["DOWNLOADUPDATE_ID"] = 1;
// _DataRow["SERVERPARTCODE"] = codes[0];
// _DataRow["SHOPCODE"] = codes[1];
// _DataRow["MACHINECODE"] = codes[3];
// _DataRow["MACHINE_MACADDRESS"] = codes[4];
// _DataRow["TABLENAME"] = codes[6];
// _DataRow["VERSIONUPDATE_DATE"] = DateTime.Now;
// _Data.Tables[0].Rows.Add(_DataRow);
//}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"缺少预下发表名\",\"rows\":[]}";
break;
}
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0 &&
!string.IsNullOrWhiteSpace(_DataSet.Tables[0].Rows[0]["VERSIONUPDATE_DATE"].ToString()))
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据更新时间\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYEX_UNIT":
#region 获取业主审批商品数据
FieldEnum _FieldEnum = DictionaryHelper.GetFieldEnum("DIVISION_CODE", codes[0], _Transaction);
SERVERPART _SERVERPART = Serverpart.GetUniformServerpart(_Transaction, _FieldEnum.FieldEnum_ID);
if (_SERVERPART.PropertyObject != null && _SERVERPART.SERVERPART_ID != null)
{
_DataTable = Method.DataTransfer.GetCommodityFromUnit(_Transaction,
_SERVERPART.SERVERPART_CODE, lastTime, codes[0], minimum, maximum);
}
else
{
_DataTable = Method.DataTransfer.GetCommodityFromUnit(_Transaction,
codes[0], lastTime, codes[0], minimum, maximum);
}
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYEX_CLOUD":
#region 获取商户审批商品数据
_DataTable = Method.DataTransfer.GetCommodityFromCloud(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MOBILEPAYCODE":
#region HIGHWAY_EXCHANGE.T_MOBILEPAYCODE
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
@"SELECT MOBILEPAYCODE_ID,PROVINCE_CODE,SERVERPART_ID,
SERVERPART_CODE,SERVERPART_NAME,SERVERPARTSHOP_ID,
SERVERPARTSHOP_CODE,SERVERPARTSHOP_NAME,BUSINESSTYPE,
PAYMENT_CHANNEL,STAFF_ID,STAFF_NAME,OPERATE_DATE,AUTOTYPE_DESC
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT MOBILEPAYCODE_ID,PROVINCE_CODE,SERVERPART_ID,
SERVERPART_CODE,SERVERPART_NAME,SERVERPARTSHOP_ID,
SERVERPARTSHOP_CODE,SERVERPARTSHOP_NAME,BUSINESSTYPE,
PAYMENT_CHANNEL,STAFF_ID,STAFF_NAME,OPERATE_DATE,
MOBILEPAYCODE_DESC AS AUTOTYPE_DESC
FROM HIGHWAY_EXCHANGE.T_MOBILEPAYCODE
WHERE SERVERPART_CODE IN ({0})
ORDER BY MOBILEPAYCODE_ID) T
WHERE ROWNUM <={1})
WHERE RN > {2}", _strServerPart, maximum, minimum));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYEX_EX":
#region HIGHWAY_EXCHANGE.T_COMMODITYEX_EX
if (!string.IsNullOrWhiteSpace(lastTime))
{
_strLastTime = " AND DOWNLOADDATE > TO_DATE('" + lastTime + "','YYYY/MM/DD HH24:MI:SS')";
}
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
@"SELECT COMMODITYEX_EX_ID,SERVERPART_CODE,COMMODITY_TYPECODE,
COMMODITY_TYPENAME,COMMODITY_CODE,COMMODITY_BARCODE,
COMMODITY_NAME,COMMODITY_EN,COMMODITY_RULE,COMMODITY_UNIT,
COMMODITY_RETAILPRICE,COMMODITY_MEMBERPRICE,CANCHANGEPRICE,
ISBULK,METERINGMETHOD,BUSINESSTYPE,COMMODITY_HOTKEY,
USERDEFINEDTYPE_ID,COMMODITY_STATE,GUARANTEE_DAY,REMINDER_DAY,
COMMODITY_SYMBOL,DOWNLOADDATE,COMMODITY_DESC
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT COMMODITYEX_EX_ID,SERVERPART_CODE,COMMODITY_TYPECODE,
COMMODITY_TYPENAME,COMMODITY_CODE,COMMODITY_BARCODE,
COMMODITY_NAME,COMMODITY_EN,COMMODITY_RULE,COMMODITY_UNIT,
COMMODITY_RETAILPRICE,COMMODITY_MEMBERPRICE,CANCHANGEPRICE,
ISBULK,METERINGMETHOD,BUSINESSTYPE,COMMODITY_HOTKEY,
USERDEFINEDTYPE_ID,COMMODITY_STATE,GUARANTEE_DAY,REMINDER_DAY,
COMMODITY_SYMBOL,DOWNLOADDATE,COMMODITY_DESC
FROM HIGHWAY_EXCHANGE.T_COMMODITYEX_EX
WHERE SERVERPART_CODE IN ({0}){3}
ORDER BY COMMODITYEX_EX_ID) T
WHERE ROWNUM <={1})
WHERE RN > {2}", _strServerPart, maximum, minimum, _strLastTime));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SERVERPART":
#region 获取服务区基本信息
_DataTable = Method.DataTransfer.DownloadServerpart(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX":
#region 获取门店基本信息
_DataTable = Method.DataTransfer.DownloadServerpartShopByCode(_Transaction, codes[0],
lastTime, minimum, maximum, codes[1]);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SELLWORKER_UNIT":
#region 获取业主管理收银员数据
_DataTable = Method.DataTransfer.DownloadSellWorkerFromUnit(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SELLWORKER_CLOUD":
#region 获取商户管理收银员数据
_DataTable = Method.DataTransfer.DownloadSellWorkerFromCloud(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE_UNIT":
#region 获取业主管理自定义类
_DataTable = Method.DataTransfer.DownloadUserdefindTypeFromUnit(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE_CLOUD":
#region 获取商户管理自定义类
_DataTable = Method.DataTransfer.DownloadUserdefindTypeFromCloud(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ADVERTISING":
#region HIGHWAY_EXCHANGE.T_ADVERTISING
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
@"SELECT * FROM (SELECT ROWNUM AS rowno,t.*
FROM HIGHWAY_EXCHANGE.T_ADVERTISING t
WHERE SERVERPARTCODE = '{0}' AND SHOPCODE = '{1}' AND ROWNUM <= {2}) table_alias
WHERE table_alias.rowno > {3}", codes[0], codes[1], maximum, minimum));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_CONFIGURATION":
#region 获取收银机配置信息
_DataTable = Method.DataTransfer.GetConfiguration(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MACHINEINFO":
#region 获取机器信息
_DataTable = Method.DataTransfer.GetMachineInfo(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE":
#region HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
@"SELECT * FROM (SELECT ROWNUM AS rowno, t.*
FROM HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE t
WHERE SERVERPARTCODE = '{0}' AND BUSINESSTYPE IN
( SELECT BUSINESSTYPE FROM HIGHWAY_EXCHANGE.T_SHOPMESSAGE
WHERE SERVERPARTSHOP_ID = {1} ) AND ROWNUM <= {2}) table_alias
WHERE table_alias.rowno > {3} ", codes[0], codes[2], maximum, minimum));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ICCARDINFO":
#region 数据源：MEMBERSHIP_STORAGE.T_ICCARDINFO
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ICCARDINFO_ID,MEMBERSHIP_ID,ICCARD_CODE,
MEMBERSHIP_CARD,ICCART_STATUE,ICCART_PWD,
SENDCARD_DATE,OPERATE_DATE,ICCARD_DESC
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT A.ICCARDINFO_ID,A.MEMBERSHIP_ID,A.ICCARD_CODE,
A.MEMBERSHIP_CARD,A.ICCART_STATUE,A.ICCART_PWD,
A.SENDCARD_DATE,A.OPERATE_DATE,A.ICCARD_DESC
FROM MEMBERSHIP_STORAGE.T_ICCARDINFO A
ORDER BY A.ICCARDINFO_ID) T
WHERE ROWNUM <= {maximum})
WHERE RN > {minimum}");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MEMBERSHIP":
#region 数据源：MEMBERSHIP_STORAGE.T_MEMBERSHIP
//注：此处SQL语句待会员系统改版后需要去除业主单位条件的注释
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT MEMBERSHIP_ID,MEMBERSHIP_TYPE,MEMBERSHIP_CODE,
MEMBERSHIP_NAME,MEMBERSHIP_CARD,MEMBERSHIP_LEVEL,
MEMBERSHIP_PASSWORD,MEMBERSHIP_POINT,ACCOUNT_BALANCE,
MEMBERSHIP_SEX,MEMBERSHIP_BIRTHDAY,MEMBERSHIP_MOBILEPHONE,
MEMBERSHIP_TELEPHONE,CERTIFICATE_NUMBER,MEMBERSHIP_EMAIL,
MEMBERSHIP_ADDRESS,RECOMMEND_ID,RECOMMEND_NAME,
MEMBERSHIP_STATE,OPERATE_DATE,MEMBERSHIP_DESC,
MEMBERSHIP_COMPANY,EXTERNALMEMBER_ID
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT A.MEMBERSHIP_ID,A.MEMBERSHIP_TYPE,A.MEMBERSHIP_CODE,
A.MEMBERSHIP_NAME,A.MEMBERSHIP_CARD,A.MEMBERSHIP_LEVEL,
A.MEMBERSHIP_PASSWORD,A.MEMBERSHIP_POINT,A.ACCOUNT_BALANCE,
A.MEMBERSHIP_SEX,A.MEMBERSHIP_BIRTHDAY,A.MEMBERSHIP_MOBILEPHONE,
A.MEMBERSHIP_TELEPHONE,A.CERTIFICATE_NUMBER,A.MEMBERSHIP_EMAIL,
A.MEMBERSHIP_ADDRESS,A.RECOMMEND_ID,A.RECOMMEND_NAME,
A.MEMBERSHIP_STATE,A.OPERATE_DATE,A.MEMBERSHIP_DESC,
A.MEMBERSHIP_COMPANY,A.EXTERNALMEMBER_ID
FROM MEMBERSHIP_STORAGE.T_MEMBERSHIP A
--WHERE A.OWNERUNIT_CODE IN({_strServerPart})
ORDER BY A.MEMBERSHIP_ID ) T
WHERE ROWNUM <= {maximum} )
WHERE RN > {minimum}");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMPANYAUTHORIZE":
#region 数据源：MEMBERSHIP_STORAGE.T_COMPANYAUTHORIZE
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT COMPANYAUTHORIZE_ID,COMPANY_ID,PROVINCE_CODE,
SERVERPART_ID,SERVERPART_CODE,SERVERPART_NAME,
SERVERPARTSHOP_ID,SHOPCODE,SHOPNAME,OPERATE_DATE
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT A.COMPANYAUTHORIZE_ID,A.COMPANY_ID,
A.PROVINCE_CODE,A.SERVERPART_ID,A.SERVERPART_CODE,
A.SERVERPART_NAME,A.SERVERPARTSHOP_ID,A.SHOPCODE,
A.SHOPNAME,A.OPERATE_DATE
FROM MEMBERSHIP_STORAGE.T_COMPANYAUTHORIZE A
WHERE A.SERVERPART_CODE IN ({_strServerPart})
ORDER BY A.COMPANYAUTHORIZE_ID ) T
WHERE ROWNUM <= {maximum} )
WHERE RN > {minimum}");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SALESPROMOTE_EX":
#region 获取促销信息
_DataTable = Method.DataTransfer.GetPromotion(_Transaction, codes[0], lastTime, minimum, maximum);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//实例化DataSet
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_AUDITTASKS":
#region HIGHWAY_EXCHANGE.T_AUDITTASKS
_DataTable = new AUDITTASKS(_Transaction).ExecuteDataTable(
$@"SELECT AUDITTASKS_ID,SERVERPART_ID,SERVERPARTCODE,
SERVERPART_NAME,SERVERPARTSHOP_ID,SHOPCODE,SHOPNAME,
BUSINESSTYPE,BUSINESSTYPE_NAME,AUDITTASKS_STARTDATE,
AUDITTASKS_ENDDATE,AUDITTASKS_DURATION,AUDITTASKS_COUNT,
AUDITTASKS_INTERVAL,AUDITTASKS_ISVALID,AUDITTASKS_TYPE,
AUDITTASKS_FIRSTTIME,AUDITTASKS_SECONDTIME,AUDITTASKS_THIRDTIME,
OPERATE_DATE,STAFF_ID,STAFF_NAME,AUDITTASKS_DESC,STRATTIME,ENDTIME
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT B.AUDITTASKSDETILS_ID AS AUDITTASKS_ID,
A.SERVERPART_ID,A.SERVERPARTCODE,A.SERVERPART_NAME,
A.SERVERPARTSHOP_ID,A.SHOPCODE,A.SHOPNAME,
A.BUSINESSTYPE,A.BUSINESSTYPE_NAME,B.STRATTIME,B.ENDTIME,
A.AUDITTASKS_STARTDATE,A.AUDITTASKS_ENDDATE,
A.AUDITTASKS_DURATION,A.AUDITTASKS_COUNT,
A.AUDITTASKS_INTERVAL,A.AUDITTASKS_ISVALID,
A.AUDITTASKS_TYPE,A.AUDITTASKS_FIRSTTIME,
A.AUDITTASKS_SECONDTIME,A.AUDITTASKS_THIRDTIME,
A.OPERATE_DATE,A.STAFF_ID,A.STAFF_NAME,A.AUDITTASKS_DESC
FROM HIGHWAY_SELLDATA.T_AUDITTASKS A,HIGHWAY_SELLDATA.T_AUDITTASKSDETILS B
WHERE A.AUDITTASKS_ID = B.AUDITTASKS_ID AND A.SERVERPARTCODE IN ({_strServerPart})
ORDER BY B.AUDITTASKSDETILS_ID) T
WHERE ROWNUM <= {maximum} )
WHERE RN > {minimum}");
if (_DataTable.Rows.Count > 0)
{
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_RECHARGERECORD.DOWN":
#region 数据源：MEMBERSHIP_STORAGE.T_RECHARGERECORD (类型：1010,2000,2010,3010)
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT RECHARGERECORD_ID,MEMBERSHIP_ID,SERVERPARTCODE,
SHOPCODE,MACHINECODE,ICCARD_CODE,RECODE_AMOUNT,RECODE_DATE,
RECODE_TYPE,RECODE_DESC,MEMBERSHIP_CARD,MEMBERSHIP_NAME
FROM (SELECT ROWNUM AS RN,T.*
FROM (SELECT A.RECHARGERECORD_ID,A.MEMBERSHIP_ID,A.SERVERPARTCODE,
A.SHOPCODE,A.MACHINECODE,A.ICCARD_CODE,A.RECODE_AMOUNT,A.RECODE_DATE,
A.RECODE_TYPE,A.RECODE_DESC,A.MEMBERSHIP_CARD,A.MEMBERSHIP_NAME
FROM MEMBERSHIP_STORAGE.T_RECHARGERECORD A
WHERE A.RECODE_TYPE IN (1010,2000,2010,3010) AND
A.RECODE_DATE >= TRUNC(SYSDATE) - 7
ORDER BY RECHARGERECORD_ID) T
WHERE ROWNUM <= {maximum} )
WHERE RN > {minimum}");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发成功\",\"rows\":" + JsonHelper.DataSetToJson(_DataSet) + "}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
default:
strBack = "{\"error\": -1 ,\"msg\": \"数据下发表类型不正确\",\"rows\":[]}";
break;
}
}
catch (Exception ex)
{
strBack = "{\"error\": -2 ,\"msg\": \"获取数据分发失败：" + ex.Message + "\",\"rows\":[]}";
}
finally
{
//释放Datatable资源
if (_DataTable != null)
{
_DataTable.Clear();
_DataTable.Dispose();
}
//释放dataset资源
if (_DataSet != null)
{
_DataSet.Clear();
_DataSet.Dispose();
}
}
return strBack;
}
#endregion
#region 方法 -> 数据下发子项 (获取总数)
///
///
///
///
///
///
///
///
///
public static string TotalCount(OracleHelper _OracleHelper, string tableName,
string lastTime, string[] codes, int maxDataDownload = 10, Transaction _Transaction = null)
{
//string _strProvinceCode = "";
//try
//{
// _strProvinceCode = ConfigurationManager.AppSettings["ProvinceCode"].ToString();
//}
//catch { }
string _strServerPart = "";
string _strServerPartOld = "";
foreach (string ServerPartCode in codes[0].Split(','))
{
_strServerPart += (string.IsNullOrWhiteSpace(_strServerPart) ? "'" : ",'") + ServerPartCode + "'";
_strServerPartOld += (string.IsNullOrWhiteSpace(_strServerPartOld) ? "'" : ",'") + ServerPartCode + "'";
}
string strBack = string.Empty;
int numCount;
int numPage;
DataSet _DataSet = null;
DataTable _DataTable = null;
try
{
switch (tableName)
{
case "HIGHWAY_EXCHANGE.T_ADVERTISING":
#region HIGHWAY_EXCHANGE.T_ADVERTISING
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
@"SELECT 1 FROM HIGHWAY_EXCHANGE.T_ADVERTISING
WHERE SERVERPARTCODE = '{0}' AND SHOPCODE = '{1}'", codes[0], codes[1]));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE":
#region HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
string.Format(@"SELECT 1 FROM HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE
WHERE SERVERPARTCODE = '{0}' AND BUSINESSTYPE IN
( SELECT BUSINESSTYPE FROM HIGHWAY_EXCHANGE.T_SHOPMESSAGE
WHERE SERVERPARTSHOP_ID = {1} )", codes[0], codes[2]));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYEX_UNIT":
#region 获取业主审批商品数据
FieldEnum _FieldEnum = DictionaryHelper.GetFieldEnum("DIVISION_CODE", codes[0], _Transaction);
SERVERPART _SERVERPART = Serverpart.GetUniformServerpart(_Transaction, _FieldEnum.FieldEnum_ID);
if (_SERVERPART.PropertyObject != null && _SERVERPART.SERVERPART_ID != null)
{
_DataTable = Method.DataTransfer.GetCommodityFromUnit(_Transaction, _SERVERPART.SERVERPART_CODE, lastTime, codes[0]);
}
else
{
_DataTable = Method.DataTransfer.GetCommodityFromUnit(_Transaction, codes[0], lastTime, codes[0]);
}
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYEX_CLOUD":
#region 获取商户审批商品数据
_DataTable = Method.DataTransfer.GetCommodityFromCloud(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYEX_EX":
#region HIGHWAY_EXCHANGE.T_COMMODITYEX_EX
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
"SELECT 1 FROM HIGHWAY_EXCHANGE.T_COMMODITYEX_EX WHERE SERVERPART_CODE IN ({0}) ", _strServerPart));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SERVERPART":
#region 获取服务区基本信息
_DataTable = Method.DataTransfer.DownloadServerpart(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SHOPMESSAGE_EX":
#region 获取门店基本信息
_DataTable = Method.DataTransfer.DownloadServerpartShopByCode(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SELLWORKER_UNIT":
#region 获取业主管理收银员数据
_DataTable = Method.DataTransfer.DownloadSellWorkerFromUnit(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SELLWORKER_CLOUD":
#region 获取商户管理收银员数据
_DataTable = Method.DataTransfer.DownloadSellWorkerFromCloud(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE_UNIT":
#region 获取业主管理自定义类
_DataTable = Method.DataTransfer.DownloadUserdefindTypeFromUnit(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_USERDEFINEDTYPE_CLOUD":
#region 获取商户管理自定义类
_DataTable = Method.DataTransfer.DownloadUserdefindTypeFromCloud(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ICCARDINFO":
#region 数据源：MEMBERSHIP_STORAGE.T_ICCARDINFO
_DataSet = _OracleHelper.ExcuteSqlGetDataSet("SELECT 1 FROM MEMBERSHIP_STORAGE.T_ICCARDINFO");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MEMBERSHIP":
#region MEMBERSHIP_STORAGE.T_MEMBERSHIP
//注：此处SQL语句待会员系统改版后需要去除业主单位条件的注释
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT 1 FROM MEMBERSHIP_STORAGE.T_MEMBERSHIP
--WHERE OWNERUNIT_CODE IN({_strServerPart})");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMPANYAUTHORIZE":
#region 数据源：MEMBERSHIP_STORAGE.T_COMPANYAUTHORIZE
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT 1 FROM MEMBERSHIP_STORAGE.T_COMPANYAUTHORIZE
WHERE SERVERPART_CODE IN ({_strServerPart})");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SALESPROMOTE_EX":
#region 获取促销信息
_DataTable = Method.DataTransfer.GetPromotion(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_CONFIGURATION":
#region 获取收银机配置信息
_DataTable = Method.DataTransfer.GetConfiguration(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MACHINEINFO":
#region 获取机器信息
_DataTable = Method.DataTransfer.GetMachineInfo(_Transaction, codes[0], lastTime);
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
numCount = _DataTable.Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": 1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_AUDITTASKS":
#region HIGHWAY_EXCHANGE.T_AUDITTASKS
_DataTable = new AUDITTASKS(_Transaction).ExecuteDataTable(
$@"SELECT 1 FROM HIGHWAY_SELLDATA.T_AUDITTASKS A,HIGHWAY_SELLDATA.T_AUDITTASKSDETILS B
WHERE A.AUDITTASKS_ID = B.AUDITTASKS_ID AND A.SERVERPARTCODE IN ({_strServerPart})");
if (_DataTable.Rows.Count > 0)
{
_DataSet = new DataSet();
_DataSet.Tables.Add(_DataTable.Copy());
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" +
numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_RECHARGERECORD.DOWN":
#region HIGHWAY_EXCHANGE.T_RECHARGERECORD_DOWN
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT 1 FROM MEMBERSHIP_STORAGE.T_RECHARGERECORD A
WHERE A.RECODE_TYPE IN (1010,2000,2010,3010) AND
A.RECODE_DATE >= TRUNC(SYSDATE) - 7");
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MOBILEPAYCODE":
#region HIGHWAY_EXCHANGE.T_MOBILEPAYCODE
_DataSet = _OracleHelper.ExcuteSqlGetDataSet(string.Format(
"SELECT 1 FROM HIGHWAY_EXCHANGE.T_MOBILEPAYCODE WHERE SERVERPART_CODE IN({0})", _strServerPart));
if (_DataSet != null && _DataSet.Tables[0].Rows.Count > 0)
{
numCount = _DataSet.Tables[0].Rows.Count;
numPage = (numCount % (100 * maxDataDownload) == 0 ? 0 : 1) + numCount / (100 * maxDataDownload);
strBack = "{\"error\": 1 ,\"msg\": \"获取数据分发总数成功\",\"rows\":[{\"numcount\":" + numCount + ",\"numpage\":" + numPage + "}]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"分发总数暂无数据\",\"rows\":[]}";
}
#endregion
break;
default:
strBack = "{\"error\": -1 ,\"msg\": \"数据下发表类型不正确\",\"rows\":[]}";
break;
}
}
catch (Exception ex)
{
strBack = "{\"error\": -1 ,\"msg\": \"获取数据分发总数失败：" + ex.Message + "\",\"rows\":[]}";
}
finally
{
//释放Datatable资源
if (_DataTable != null)
{
_DataTable.Clear();
_DataTable.Dispose();
}
//释放dataset资源
if (_DataSet != null)
{
_DataSet.Clear();
_DataSet.Dispose();
}
}
return strBack;
}
#endregion
#region 方法 -> 更新商品数据版本信息
///
/// 更新商品数据版本信息
///
/// 数据库操作类
/// 表名
/// 数据版本
/// 服务区编号
/// 业态
/// 执行结果：error=1时为操作成功状态，-1时为操作失败状态
public static string SetCommodityVersion(OracleHelper _OracleHelper, string TableName, string DataVersion, string ServerpartCode, string BusinessType)
{
//初始化状态返回信息
string strBack = "{\"error\": -1 ,\"msg\": \"参数错误！\"}";
if (string.IsNullOrEmpty(BusinessType))
{
return strBack;
}
//查找数据版本表中是否存在当前记录，匹配组合键：服务区编号+业态+表名
string Sql = "SELECT DATAVERSION_ID,DATAVERSION FROM HIGHWAY_EXCHANGE.T_DATAVERSION WHERE SERVERPARTCODE='" + ServerpartCode + "' and BUSINESSTYPE='" + BusinessType + "' " +
"AND TABLENAME='" + TableName + "'";
DataSet ds_dataversion = _OracleHelper.ExcuteSqlGetDataSet(Sql);
if (ds_dataversion == null || ds_dataversion.Tables.Count == 0 || ds_dataversion.Tables[0].Rows.Count == 0)
{
//添加数据版本信息
Sql = string.Format(@"INSERT INTO HIGHWAY_EXCHANGE.T_DATAVERSION(DATAVERSION_ID,SERVERPARTCODE,SHOPCODE,MACHINECODE,TABLENAME,DATAVERSION,BUSINESSTYPE)
VALUES(HIGHWAY_EXCHANGE.SEQ_DATAVERSION.NEXTVAL,'{0}','','','{1}',TO_DATE('{2}','YYYY-MM-DD HH24:MI:SS'),{3})", ServerpartCode, TableName, DataVersion, BusinessType);
}
else
{
//更新数据版本信息
DateTime dt_beUpdated = Convert.ToDateTime(ds_dataversion.Tables[0].Rows[0][1].ToString());
if (dt_beUpdated < Convert.ToDateTime(DataVersion))
{
Sql = string.Format(@"UPDATE HIGHWAY_EXCHANGE.T_DATAVERSION SET DATAVERSION=TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') WHERE
DATAVERSION_ID={1}", DataVersion, ds_dataversion.Tables[0].Rows[0][0].ToString());
}
else
{
ds_dataversion.Clear();
ds_dataversion.Dispose();
return "{\"error\": 1 ,\"msg\": \"更新成功！\"}";
}
}
//获取语句执行后的影响行数
int resultRows = _OracleHelper.ExcuteSql(Sql);
if (resultRows > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"更新成功！\"}";
}
//释放资源
if (ds_dataversion != null)
{
ds_dataversion.Clear();
ds_dataversion.Dispose();
}
return strBack;
}
#endregion
#region 方法 -> 更新门店数据版本信息
///
/// 更新门店数据版本信息
///
/// 数据库操作类
/// 表名
/// 数据版本号
/// 服务区编号
/// 门店编号
///
public static string SetShopDataVersion(OracleHelper _OracleHelper, string TableName, string DataVersion, string ServerpartCode, string ShopCode)
{
string strBack = "{\"error\": -1 ,\"msg\": \"参数错误！\"}";
//添加或更新门店数据版本号
if (string.IsNullOrEmpty(ShopCode))
{
return strBack;
}
string Sql = "select DATAVERSION_ID,DATAVERSION from HIGHWAY_EXCHANGE.T_DATAVERSION where SERVERPARTCODE='" + ServerpartCode + "' and SHOPCODE='" + ShopCode + "' " +
"and TABLENAME='" + TableName + "'";
DataSet ds_dataversion = _OracleHelper.ExcuteSqlGetDataSet(Sql);
if (ds_dataversion == null || ds_dataversion.Tables.Count == 0 || ds_dataversion.Tables[0].Rows.Count == 0)
{
//添加数据版本信息
Sql = string.Format(@"INSERT INTO HIGHWAY_EXCHANGE.T_DATAVERSION(DATAVERSION_ID,SERVERPARTCODE,SHOPCODE,MACHINECODE,TABLENAME,DATAVERSION,BUSINESSTYPE)
VALUES(HIGHWAY_EXCHANGE.SEQ_DATAVERSION.NEXTVAL,'{0}','{1}','','{2}',TO_DATE('{3}','YYYY-MM-DD HH24:MI:SS'),'')", ServerpartCode, ShopCode, TableName, DataVersion);
}
else
{
//更新数据版本信息
DateTime dt_beUpdated = Convert.ToDateTime(ds_dataversion.Tables[0].Rows[0][1].ToString());
if (dt_beUpdated < Convert.ToDateTime(DataVersion))
{
Sql = string.Format(@"UPDATE HIGHWAY_EXCHANGE.T_DATAVERSION SET DATAVERSION=TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') WHERE
DATAVERSION_ID={1}", DataVersion, ds_dataversion.Tables[0].Rows[0][0].ToString());
}
else
{
ds_dataversion.Clear();
ds_dataversion.Dispose();
return "{\"error\": 1 ,\"msg\": \"更新成功！\"}";
}
}
int resultRows = _OracleHelper.ExcuteSql(Sql);
if (resultRows > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"更新成功！\"}";
}
if (ds_dataversion != null)
{
ds_dataversion.Clear();
ds_dataversion.Dispose();
}
return strBack;
}
#endregion
#region 方法 -> 更新工号数据版本信息
///
/// 更新工号数据版本信息
///
/// 数据库操作类
/// 表名
/// 数据版本号
/// 服务区编号
///
public static string SetSellWorkerDataVersion(OracleHelper _OracleHelper, string TableName, string DataVersion, string ServerpartCode)
{
string strBack = "{\"error\": -1 ,\"msg\": \"更新失败！\"}";
string Sql = "select DATAVERSION_ID,DATAVERSION from HIGHWAY_EXCHANGE.T_DATAVERSION where SERVERPARTCODE='" + ServerpartCode + "'" +
"and TABLENAME='" + TableName + "'";
DataSet ds_dataversion = _OracleHelper.ExcuteSqlGetDataSet(Sql);
if (ds_dataversion == null || ds_dataversion.Tables.Count == 0 || ds_dataversion.Tables[0].Rows.Count == 0)
{
//添加数据版本信息
Sql = string.Format(@"INSERT INTO HIGHWAY_EXCHANGE.T_DATAVERSION(DATAVERSION_ID,SERVERPARTCODE,SHOPCODE,MACHINECODE,TABLENAME,DATAVERSION,BUSINESSTYPE)
VALUES(HIGHWAY_EXCHANGE.SEQ_DATAVERSION.NEXTVAL,'{0}','','','{1}',TO_DATE('{2}','YYYY-MM-DD HH24:MI:SS'),'')", ServerpartCode, TableName, DataVersion);
}
else
{
//更新数据版本信息
DateTime dt_beUpdated = Convert.ToDateTime(ds_dataversion.Tables[0].Rows[0][1].ToString());
if (dt_beUpdated < Convert.ToDateTime(DataVersion))
{
Sql = string.Format(@"UPDATE HIGHWAY_EXCHANGE.T_DATAVERSION SET DATAVERSION=TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS') WHERE
DATAVERSION_ID={1}", DataVersion, ds_dataversion.Tables[0].Rows[0][0].ToString());
}
else
{
ds_dataversion.Clear();
ds_dataversion.Dispose();
return "{\"error\": 1 ,\"msg\": \"更新成功！\"}";
}
}
int resultRows = _OracleHelper.ExcuteSql(Sql);
if (resultRows > 0)
{
strBack = "{\"error\": 1 ,\"msg\": \"更新成功！\"}";
}
if (ds_dataversion != null)
{
ds_dataversion.Clear();
ds_dataversion.Dispose();
}
return strBack;
}
#endregion
}
}