using Newtonsoft.Json.Linq;
using OperatingData.SDK;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace TableDataService.Method
{
public class UploadMethod
{
#region 方法 -> 数据上传子项
///
///
///
/// 数据库连接
/// 上传的表名称
/// 上传的数据集合，json字符串
/// 服务区编码、门店编码、机器编码数据集合
/// 字段暂不使用
///
public static string DataUploading(OracleHelper _OracleHelper, string tableName,
string jsonString, string[] codes, int maxDataQuantity = 4)
{
string strBack = string.Empty; //返回内容
string _ISCheckData = string.Empty; //存储未上传必填项的数据集
string[] _RequiredDatas = null; //定义上传数据的必填项内容
bool isReplaceColumnName = false; //是否需要替换字段名称
try
{
//营收数据相关新表
/* T_SELLMASTER 销售流水主表【后续删除】
* T_SELLDETAILS 销售流水详情表【后续删除】
* T_ENDACCOUNT_NEW 日结报表
* T_PERSONSELL_NEW 收银交班表
* T_COMMODITYSALE_NEW 单品销售表【改成文本传输】
* T_COMMODITYSALE_EXTAR 特殊单品报表【后续删除】
* T_DATACOLLECTION 十分钟营收数据表【后续迁移】
*/
//营收数据相关旧表【云端传输删除】
/* T_SELLDATA 销售流水表【旧】
* T_TRANSFER_SELLDATA 销售流水集合表【旧】
* T_ENDACCOUNT 日结账期表【旧】
* T_PERSONSELL 收银员交班表【旧】
* T_COMMODITYSALE 单品报表【旧】
* T_TRANSFER_SALE 单品数据集合【旧】
* T_EXCEPTION 异常稽核数据表【旧】
*/
//收银稽核相关表
/* T_ABNORMALITY 异常操作记录主表【改成文本传输】
* T_ABNORMALITYDETAIL 异常操作稽核统计表【改成文本传输】
* T_ABNOEMALITYANALYSIS 异常操作稽核统计表【改成文本传输】
*/
//移动支付相关表
/* T_SELLDATA_EXTRA 离线移动支付交易记录表【收银前端删除】
* T_MOBILE_PAY 收银前端异常移动支付数据表【改成文本传输】
*/
//会员促销相关表
/* T_CONSUMPTIONRECORD 促销流水表【后续删除】
* T_PROMOTIONRECORD 促销流水表-2021.04新增，未启用
* T_RECHARGERECORD 企业内部会员消费记录表【商业集团食堂离线支付交易记录】【收银前端删除】
*/
//客群分析相关表【重新设置，规划传输链路】
/* T_CUSTOMERINFO 客群分析数据表
* T_CUSTOMERANALYSIS 客单时段统计表
* T_COMMODITYANALYSIS 推荐单品统计表
* T_SALESANALYSIS 客单区段统计表
* T_SALESINTERVALANALYSIS 客单时段统计表
*/
/* T_BUSINESSTIME 门店营业时间表【删除】
* T_USERSATISFACTION 满意度评价【删除】
* T_ADVERTFEEDBACK 广告播放情况【删除】
* T_TRANSMISSIONERROR 数据传输异常记录表【删除】
* T_TRANSFERSTATISTICS 数据传输上传统计表【删除】
* T_COMMODITYEX_NEW 收银机本地商品数据表【用于比对数据下发状况，未正式启用】【删除】
*/
switch (tableName)
{
case "HIGHWAY_EXCHANGE.T_SELLMASTER":
#region HIGHWAY_EXCHANGE.T_SELLMASTER 销售流水主表
//必填项
_RequiredDatas = new string[] { "SELLMASTER_ID", "SELLMASTER_CODE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataSellmaster =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataSellmaster != null && _DataSellmaster.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataSellmaster.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataSellmaster.Min(p => p.SELLMASTER_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataSellmaster.Max(p => p.SELLMASTER_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT SELLMASTER_CODE FROM HIGHWAY_EXCHANGE.T_SELLMASTER
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
MACHINECODE = '{codes[3]}' AND SELLMASTER_DATE BETWEEN TO_DATE('{codes[4]}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') ").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.SELLMASTER _SELLMASTER in _DataSellmaster)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select($" SELLMASTER_CODE = '{_SELLMASTER.SELLMASTER_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_SELLMASTER);
continue;
}
}
//需更新的集合
_InsertList.Add(_SELLMASTER);
}
if (_InsertList.Count + _UpdateList.Count == _DataSellmaster.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_SELLMASTER");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SELLDETAILS":
#region HIGHWAY_EXCHANGE.T_SELLDETAILS 销售流水详情表
//必填项
_RequiredDatas = new string[] { "SELLDETAILS_ID", "SELLMASTER_CODE", "CREATE_DATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataSelldetails = JsonHelper.JSONStringToListUTC(jsonString);
if (_DataSelldetails != null && _DataSelldetails.Count > 0)
{
var _MinDate = _DataSelldetails.Min(p => p.CREATE_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataSelldetails.Max(p => p.CREATE_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT SELLMASTER_CODE,COMMODITY_CODE,LINENUM
FROM HIGHWAY_EXCHANGE.T_SELLDETAILS
WHERE CREATE_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.SELLDETAILS _SELLDETAILS in _DataSelldetails)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" SELLMASTER_CODE = '{_SELLDETAILS.SELLMASTER_CODE}' AND " +
$" COMMODITY_CODE = '{_SELLDETAILS.COMMODITY_CODE}' AND " +
$" LINENUM = '{_SELLDETAILS.LINENUM}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_SELLDETAILS);
continue;
}
}
//需更新的集合
_InsertList.Add(_SELLDETAILS);
}
if (_InsertList.Count + _UpdateList.Count == _DataSelldetails.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_SELLDETAILS");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW":
#region HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW 日结报表
//设置必填项：日结账单内码【ENDACCOUNT_ID】、日结账单编码（唯一值）ENDACCOUNT_CODE
_RequiredDatas = new string[] { "ENDACCOUNT_ID", "ENDACCOUNT_CODE" };
//验证上传的数据中是否缺失必填项
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
//存储缺失必填项的数据
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
//解析json字符串，转化为数据对象ENDACCOUNT_NEW
List _DataEndaccount_new =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataEndaccount_new != null && _DataEndaccount_new.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataEndaccount_new.Select(p => new
{
p.SERVERPARTCODE, //服务区编码
p.SHOPCODE, //门店编码
p.MACHINECODE //机器编码
}).Distinct().ToArray();
//获取上传的服务区门店机器信息集合，用于获取已上传至服务器的日结数据
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") +
_ShopCodeTemp.SERVERPARTCODE.Trim() +
_ShopCodeTemp.SHOPCODE.Trim() +
_ShopCodeTemp.MACHINECODE.Trim() + "'";
}
//获取上传的数据集中最小的日结时间
var _MinDate = _DataEndaccount_new.Min(p => p.STARTDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
//获取上传的数据集中最大的日结时间
var _MaxDate = _DataEndaccount_new.Max(p => p.STARTDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//查询最小和最大日结时间范围内，该服务区门店的日结数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ENDACCOUNT_ID,ENDACCOUNT_CODE
FROM HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW
WHERE SERVERPARTCODE || SHOPCODE || MACHINECODE IN ({_strShopCode}) AND
STARTDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
List _ContinueList = new List();//不需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
//遍历日结数据集合，获取需要插入的集合、更新的集合、过滤还未结账的数据集合
foreach (Model.EXCHANGE.ENDACCOUNT_NEW _ENDACCOUNT_NEW in _DataEndaccount_new)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
//判断日结数据是否已上传
_DataRows = _DataTable.Select($" ENDACCOUNT_CODE = '{_ENDACCOUNT_NEW.ENDACCOUNT_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
if (_ENDACCOUNT_NEW.ENDDATE == null)
{
//记录已存在、不需要更新的数据（正在进行的账期数据）
_ContinueList.Add(_ENDACCOUNT_NEW);
}
else
{
//记录已存在、需更新的数据
_UpdateList.Add(_ENDACCOUNT_NEW);
}
continue;
}
}
//需更新的集合
_InsertList.Add(_ENDACCOUNT_NEW);
}
//判断数据完整性，插入集合数量+更新集合数量+过滤集合数量=总数据量
if (_InsertList.Count + _UpdateList.Count + _ContinueList.Count == _DataEndaccount_new.Count)
{
if (_InsertList.Count > 0)
{
//执行日结数据插入语句
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW");
}
if (_UpdateList.Count > 0)
{
try
{
//执行日结数据更新语句
OperationDataHelper.UpdateTableData(
_OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_ENDACCOUNT_NEW",
new string[] { "ENDACCOUNT_CODE" });
}
catch (Exception)
{
//出现异常，返回更新失败的集合
strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
break;
}
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_PERSONSELL_NEW":
#region HIGHWAY_EXCHANGE.T_PERSONSELL_NEW 收银交班表
//必填项
_RequiredDatas = new string[] { "PERSONSELL_ID", "ENDACCOUNT_CODE", "WOKER_NUMBER" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataPersonsell_new =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataPersonsell_new != null && _DataPersonsell_new.Count > 0)
{
//从上传的数据中获取服务区、门店、收银机号信息，用于服务器已有数据读取和判断
string str_DataCode = "";
var _DataCodeList = _DataPersonsell_new.Select(p => new
{
p.SERVERPARTCODE,
p.SHOPCODE,
p.MACHINECODE
}).Distinct().ToArray();
foreach (var _DataCodeTemp in _DataCodeList)
{
str_DataCode += (string.IsNullOrWhiteSpace(str_DataCode) ? "'" : ",'") +
_DataCodeTemp.SERVERPARTCODE.Trim() +
_DataCodeTemp.SHOPCODE.Trim() +
_DataCodeTemp.MACHINECODE.Trim() + "'";
}
//获取上传的数据中最小的账期时间
var _MinDate = _DataPersonsell_new.Min(p => p.STARTDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
//获取上传的数据中最大的账期时间
var _MaxDate = _DataPersonsell_new.Max(p => p.STARTDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT PERSONSELL_ID,ENDACCOUNT_CODE,WOKER_NUMBER
FROM HIGHWAY_EXCHANGE.T_PERSONSELL_NEW
WHERE SERVERPARTCODE || SHOPCODE || MACHINECODE IN ({str_DataCode}) AND
STARTDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.PERSONSELL_NEW _PERSONSELL_NEW in _DataPersonsell_new)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ENDACCOUNT_CODE = '{_PERSONSELL_NEW.ENDACCOUNT_CODE}' AND " +
$" WOKER_NUMBER = {_PERSONSELL_NEW.WOKER_NUMBER}");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_PERSONSELL_NEW);
continue;
}
}
//需更新的集合
_InsertList.Add(_PERSONSELL_NEW);
}
if (_InsertList.Count + _UpdateList.Count == _DataPersonsell_new.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_PERSONSELL_NEW");
}
if (_UpdateList.Count > 0)
{
try
{
OperationDataHelper.UpdateTableData(
_OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_PERSONSELL_NEW",
new string[] { "ENDACCOUNT_CODE", "WOKER_NUMBER" });
}
catch (Exception)
{
strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
break;
}
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYSALE_NEW":
#region HIGHWAY_EXCHANGE.T_COMMODITYSALE_NEW 单品销售表
//必填项
_RequiredDatas = new string[] { "COMMODITYSALE_ID", "ENDACCOUNT_CODE", "CREATE_DATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataCommoditysale_new =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataCommoditysale_new != null && _DataCommoditysale_new.Count > 0)
{
var _MinDate = _DataCommoditysale_new.Min(p => p.CREATE_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataCommoditysale_new.Max(p => p.CREATE_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ENDACCOUNT_CODE,COMMODITY_CODE,WOKER_NUMBER
FROM HIGHWAY_EXCHANGE.T_COMMODITYSALE_NEW
WHERE CREATE_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') ").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.COMMODITYSALE_NEW _COMMODITYSALE_NEW in _DataCommoditysale_new)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ENDACCOUNT_CODE = '{_COMMODITYSALE_NEW.ENDACCOUNT_CODE}' AND " +
$" COMMODITY_CODE = '{_COMMODITYSALE_NEW.COMMODITY_CODE}' AND " +
$" WOKER_NUMBER = '{_COMMODITYSALE_NEW.WOKER_NUMBER}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_COMMODITYSALE_NEW);
continue;
}
}
//需更新的集合
_InsertList.Add(_COMMODITYSALE_NEW);
}
if (_InsertList.Count + _UpdateList.Count == _DataCommoditysale_new.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_COMMODITYSALE_NEW");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYSALE_EXTAR":
#region HIGHWAY_EXCHANGE.T_COMMODITYSALE_EXTAR 特殊单品报表
//必填项
_RequiredDatas = new string[] { "COMMODITYSALE_ID", "STARTDATE",
"ENDDATE", "SERVERPARTCODE", "SHOPCODE", "MACHINECODE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataCommoditysale_extar =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataCommoditysale_extar != null && _DataCommoditysale_extar.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataCommoditysale_extar.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataCommoditysale_extar.Min(p => p.STARTDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataCommoditysale_extar.Max(p => p.STARTDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT COMMODITYSALE_ID,STARTDATE,ENDDATE,
SERVERPARTCODE,SHOPCODE,MACHINECODE
FROM HIGHWAY_EXCHANGE.T_COMMODITYSALE_EXTAR
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
STARTDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.COMMODITYSALE_EXTAR _COMMODITYSALE_EXTAR in _DataCommoditysale_extar)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" COMMODITYSALE_ID = {_COMMODITYSALE_EXTAR.COMMODITYSALE_ID} AND " +
$" STARTDATE = '{_COMMODITYSALE_EXTAR.STARTDATE.ToString()}' AND " +
$" ENDDATE = '{_COMMODITYSALE_EXTAR.ENDDATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_COMMODITYSALE_EXTAR.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_COMMODITYSALE_EXTAR.SHOPCODE}' AND " +
$" MACHINECODE = '{_COMMODITYSALE_EXTAR.MACHINECODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_COMMODITYSALE_EXTAR);
continue;
}
}
//需更新的集合
_InsertList.Add(_COMMODITYSALE_EXTAR);
}
if (_InsertList.Count + _UpdateList.Count == _DataCommoditysale_extar.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_COMMODITYSALE_EXTAR");
}
//if (_UpdateList.Count > 0)
//{
// try
// {
// OperationDataHelper.UpdateTableData(
// _OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_COMMODITYSALE_EXTAR",
// new string[] { "COMMODITYSALE_ID", "STARTDATE", "ENDDATE",
// "SERVERPARTCODE", "SHOPCODE", "MACHINECODE" });
// }
// catch (Exception)
// {
// strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
// JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
// break;
// }
//}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_DATACOLLECTION":
#region HIGHWAY_EXCHANGE.T_DATACOLLECTION 十分钟营收数据表
//必填项
_RequiredDatas = new string[] { "DATACOLLECTION_ID", "MACADDRESS", "DATACOLLECTION_DATE", "DATACOLLECTION_TYPE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataDatacollection = JsonHelper.JSONStringToListUTC(jsonString);
if (_DataDatacollection != null && _DataDatacollection.Count > 0)
{
var _MinDate = _DataDatacollection.Min(p => p.DATACOLLECTION_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataDatacollection.Max(p => p.DATACOLLECTION_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT MACADDRESS,DATACOLLECTION_DATE
FROM HIGHWAY_EXCHANGE.T_DATACOLLECTION
WHERE SERVERPARTCODE = '{codes[0]}' AND DATACOLLECTION_TYPE IN (1000,2000) AND
DATACOLLECTION_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.DATACOLLECTION _DATACOLLECTION in _DataDatacollection)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select($" MACADDRESS = '{_DATACOLLECTION.MACADDRESS}' AND " +
$" DATACOLLECTION_DATE = '{_DATACOLLECTION.DATACOLLECTION_DATE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_DATACOLLECTION);
continue;
}
}
//需插入的集合
_InsertList.Add(_DATACOLLECTION);
}
if (_InsertList.Count + _UpdateList.Count == _DataDatacollection.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_DATACOLLECTION");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_PROMOTIONRECORD":
#region HIGHWAY_EXCHANGE.T_PROMOTIONRECORD 促销流水表-2021.04新增，未启用
_RequiredDatas = new string[]
{
"PROMOTIONRECORD_ID",
"PROMOTIONRECORD_CODE",
"PROMOTION_ID",
"SERVERPARTCODE",
"SHOPCODE"
};
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataPromotionRecord =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataPromotionRecord != null && _DataPromotionRecord.Count > 0)
{
//从上传的数据中获取服务区、门店、收银机号信息，用于服务器已有数据读取和判断
string str_DataCode = "";
var _DataCodeList = _DataPromotionRecord.Select(p => new
{
p.SERVERPARTCODE,
p.SHOPCODE,
p.MACHINECODE
}).Distinct().ToArray();
foreach (var _DataCodeTemp in _DataCodeList)
{
str_DataCode += (string.IsNullOrWhiteSpace(str_DataCode) ? "'" : ",'") +
_DataCodeTemp.SERVERPARTCODE.Trim() +
_DataCodeTemp.SHOPCODE.Trim() +
_DataCodeTemp.MACHINECODE.Trim() + "'";
}
var _MinDate = _DataPromotionRecord.Min(p => p.PROMOTIONRECORD_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataPromotionRecord.Max(p => p.PROMOTIONRECORD_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT PROMOTIONRECORD_ID,PROMOTIONRECORD_CODE
FROM HIGHWAY_EXCHANGE.T_PROMOTIONRECORD
WHERE SERVERPARTCODE||SHOPCODE||MACHINECODE IN({str_DataCode}) AND PROMOTIONRECORD_DATE BETWEEN
TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.PROMOTIONRECORD _PROMOTIONRECORD in _DataPromotionRecord)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" PROMOTIONRECORD_ID = '{_PROMOTIONRECORD.PROMOTIONRECORD_ID.ToString()}' AND " +
$" PROMOTIONRECORD_CODE = '{_PROMOTIONRECORD.PROMOTIONRECORD_CODE}' ");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_PROMOTIONRECORD);
continue;
}
}
//需插入的集合
_InsertList.Add(_PROMOTIONRECORD);
}
if (_InsertList.Count + _UpdateList.Count == _DataPromotionRecord.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_PROMOTIONRECORD");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_CONSUMPTIONRECORD":
#region HIGHWAY_EXCHANGE.T_CONSUMPTIONRECORD 促销流水表
_RequiredDatas = new string[]
{
"CONSUMPTIONRECORD_DATE",
"SERVERPART_CODE",
"SHOPCODE",
"MACHINECODE",
"TICKET_CODE",
"COMMODITY_CODE",
"LINE_NUMBER"
};
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataConsumptionRecord =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataConsumptionRecord != null && _DataConsumptionRecord.Count > 0)
{
var _MinDate = _DataConsumptionRecord.Min(p => p.CONSUMPTIONRECORD_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataConsumptionRecord.Max(p => p.CONSUMPTIONRECORD_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT CONSUMPTIONRECORD_DATE,SERVERPART_CODE,SHOPCODE,
MACHINECODE,TICKET_CODE,COMMODITY_CODE,LINE_NUMBER
FROM HIGHWAY_EXCHANGE.T_CONSUMPTIONRECORD
WHERE SERVERPART_CODE = '{codes[0]}' AND CONSUMPTIONRECORD_DATE BETWEEN
TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.CONSUMPTIONRECORD _CONSUMPTIONRECORD in _DataConsumptionRecord)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" CONSUMPTIONRECORD_DATE = '{_CONSUMPTIONRECORD.CONSUMPTIONRECORD_DATE.ToString()}' AND " +
$" SERVERPART_CODE = '{_CONSUMPTIONRECORD.SERVERPART_CODE}' AND " +
$" SHOPCODE = '{_CONSUMPTIONRECORD.SHOPCODE}' AND " +
$" MACHINECODE = '{_CONSUMPTIONRECORD.MACHINECODE}' AND " +
$" TICKET_CODE = '{_CONSUMPTIONRECORD.TICKET_CODE}' AND " +
$" COMMODITY_CODE = '{_CONSUMPTIONRECORD.COMMODITY_CODE}' AND " +
$" LINE_NUMBER = '{_CONSUMPTIONRECORD.LINE_NUMBER}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_CONSUMPTIONRECORD);
continue;
}
}
//需插入的集合
_InsertList.Add(_CONSUMPTIONRECORD);
}
if (_InsertList.Count + _UpdateList.Count == _DataConsumptionRecord.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_CONSUMPTIONRECORD");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_MOBILE_PAY":
#region HIGHWAY_EXCHANGE.T_MOBILE_PAY 收银前端异常移动支付数据表
//必填项
_RequiredDatas = new string[] { "MOBILE_PAY_ID", "MOBILEPAY_TYPE", "TICKET_CODE", "MOBILEPAY_DATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataMobile_Pay =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataMobile_Pay != null && _DataMobile_Pay.Count > 0)
{
var _MinDate = _DataMobile_Pay.Min(p => p.MOBILEPAY_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataMobile_Pay.Max(p => p.MOBILEPAY_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT MOBILE_PAY_ID,MOBILEPAY_TYPE,TICKET_CODE
FROM HIGHWAY_EXCHANGE.T_MOBILE_PAY
WHERE SERVERPARTCODE = '{codes[0]}' AND MOBILEPAY_DATE BETWEEN
TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.MOBILE_PAY _MOBILE_PAY in _DataMobile_Pay)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" MOBILE_PAY_ID = {_MOBILE_PAY.MOBILE_PAY_ID} AND " +
$" MOBILEPAY_TYPE = '{_MOBILE_PAY.MOBILEPAY_TYPE}' AND " +
$" TICKET_CODE = '{_MOBILE_PAY.TICKET_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_MOBILE_PAY);
continue;
}
}
//需更新的集合
_InsertList.Add(_MOBILE_PAY);
}
if (_InsertList.Count + _UpdateList.Count == _DataMobile_Pay.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_MOBILE_PAY");
}
if (_UpdateList.Count > 0)
{
try
{
OperationDataHelper.UpdateTableData(
_OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_MOBILE_PAY",
new string[] { "MOBILE_PAY_ID", "MOBILEPAY_TYPE", "TICKET_CODE" });
}
catch (Exception)
{
strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
break;
}
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SELLDATA":
#region HIGHWAY_EXCHANGE.T_SELLDATA 销售流水表【旧】
//必填项
_RequiredDatas = new string[] { "SELLDATA_ID", "SELLDATA_DATE", "SERVERPARTCODE",
"SHOPCODE", "MACHINECODE", "TICKETCODE", "WORKERCODE", "COMMODITY_CODE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataSelldata = JsonHelper.JSONStringToListUTC(jsonString);
if (_DataSelldata != null && _DataSelldata.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataSelldata.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataSelldata.Min(p => p.SELLDATA_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataSelldata.Max(p => p.SELLDATA_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT SELLDATA_ID,SELLDATA_DATE,SERVERPARTCODE,
SHOPCODE,MACHINECODE,TICKETCODE,LINENUM
FROM HIGHWAY_EXCHANGE.T_SELLDATA
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
SELLDATA_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.SELLDATA _SELLDATA in _DataSelldata)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" SELLDATA_DATE = '{_SELLDATA.SELLDATA_DATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_SELLDATA.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_SELLDATA.SHOPCODE}' AND " +
$" MACHINECODE = '{_SELLDATA.MACHINECODE}' AND " +
$" TICKETCODE = '{_SELLDATA.TICKETCODE}' AND " +
$" LINENUM = {_SELLDATA.LINENUM}");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_SELLDATA);
continue;
}
}
//需更新的集合
_InsertList.Add(_SELLDATA);
}
if (_InsertList.Count + _UpdateList.Count == _DataSelldata.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_SELLDATA");
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_TRANSFER_SELLDATA":
#region HIGHWAY_EXCHANGE.T_TRANSFER_SELLDATA 销售流水集合表
_RequiredDatas = new string[] { "TRANSFER_ID", "SELLDATA_DATE", "SERVERPARTCODE",
"SHOPCODE", "MACHINECODE", "TICKETCODE", "WORKERCODE", "SELLCOUNT",
"OFFPRICE", "FACTAMOUNT", "TRANSFER_FIRSTDATAS" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataTransferSell =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataTransferSell != null && _DataTransferSell.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataTransferSell.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataTransferSell.Min(p => p.SELLDATA_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataTransferSell.Max(p => p.SELLDATA_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT TRANSFER_ID,SELLDATA_DATE,SERVERPARTCODE,
SHOPCODE,MACHINECODE,TICKETCODE
FROM HIGHWAY_EXCHANGE.T_TRANSFER_SELLDATA
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
SELLDATA_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.TRANSFER_SELLDATA _TRANSFER_SELLDATA in _DataTransferSell)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" SELLDATA_DATE = '{_TRANSFER_SELLDATA.SELLDATA_DATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_TRANSFER_SELLDATA.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_TRANSFER_SELLDATA.SHOPCODE}' AND " +
$" MACHINECODE = '{_TRANSFER_SELLDATA.MACHINECODE}' AND " +
$" TICKETCODE = '{_TRANSFER_SELLDATA.TICKETCODE}' AND " +
$" TRANSFER_ID = {_TRANSFER_SELLDATA.TRANSFER_ID}");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_TRANSFER_SELLDATA);
continue;
}
}
//需插入的集合
_InsertList.Add(_TRANSFER_SELLDATA);
}
if (_InsertList.Count + _UpdateList.Count == _DataTransferSell.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_TRANSFER_SELLDATA");
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ENDACCOUNT":
#region HIGHWAY_EXCHANGE.T_ENDACCOUNT 日结账期表【旧】
//必填项
try
{
if (_OracleHelper.ExcuteSqlGetDataSet(
@"SELECT 1 FROM ALL_TAB_COLUMNS
WHERE OWNER = 'HIGHWAY_EXCHANGE' AND
TABLE_NAME = 'T_ENDACCOUNT' AND
COLUMN_NAME = 'SERVERPART_CODE'").Tables[0].Rows.Count > 0)
{
isReplaceColumnName = true;
}
}
catch { }
_RequiredDatas = new string[]
{
"ENDACCOUNT_ID",
"ENDACCOUNT_STARTDATE",
"SERVERPARTCODE",
"SHOPCODE",
"MACHINECODE" ,
"FLAG"
};
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
if (isReplaceColumnName)
{
jsonString = jsonString.Replace("SERVERPARTCODE", "SERVERPART_CODE");
List _DataEndaccount =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataEndaccount != null && _DataEndaccount.Count > 0)
{
string str_DataCode = "";
//获取数据包中的服务区、门店、收银机号信息
var _DataCodeList = _DataEndaccount.Select(p => new
{
p.SERVERPART_CODE,
p.SHOPCODE,
p.MACHINECODE
}).Distinct().ToArray();
//拼接组成区服已有数据查询条件值
foreach (var _DataCodeTemp in _DataCodeList)
{
str_DataCode += (string.IsNullOrWhiteSpace(str_DataCode) ? "'" : ",'") +
_DataCodeTemp.SERVERPART_CODE.Trim() +
_DataCodeTemp.SHOPCODE.Trim() +
_DataCodeTemp.MACHINECODE.Trim() + "'";
}
//获取数据包中最小时间
var _MinDate = _DataEndaccount.Min(p => p.ENDACCOUNT_STARTDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
//获取数据包中最大时间
var _MaxDate = _DataEndaccount.Max(p => p.ENDACCOUNT_STARTDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//获取数据包中包含的服务区门店中已上传过的数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ENDACCOUNT_ID,ENDACCOUNT_STARTDATE,
SERVERPART_CODE,SHOPCODE,MACHINECODE,FLAG
FROM HIGHWAY_EXCHANGE.T_ENDACCOUNT
WHERE SERVERPART_CODE || SHOPCODE || MACHINECODE IN ({str_DataCode}) AND ENDACCOUNT_STARTDATE BETWEEN
TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.HIGHWAY.ENDACCOUNT_SC _ENDACCOUNT in _DataEndaccount)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ENDACCOUNT_ID = {_ENDACCOUNT.ENDACCOUNT_ID} AND " +
$" ENDACCOUNT_STARTDATE = '{_ENDACCOUNT.ENDACCOUNT_STARTDATE.ToString()}' AND " +
$" SERVERPART_CODE = '{_ENDACCOUNT.SERVERPART_CODE}' AND " +
$" SHOPCODE = '{_ENDACCOUNT.SHOPCODE}' AND " +
$" MACHINECODE = '{_ENDACCOUNT.MACHINECODE}' AND " +
$" FLAG = {_ENDACCOUNT.FLAG}");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_ENDACCOUNT);
continue;
}
}
//需更新的集合
_InsertList.Add(_ENDACCOUNT);
}
if (_InsertList.Count + _UpdateList.Count == _DataEndaccount.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_ENDACCOUNT");
}
if (_UpdateList.Count > 0)
{
try
{
OperationDataHelper.UpdateTableData(
_OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_ENDACCOUNT",
new string[] { "ENDACCOUNT_ID", "ENDACCOUNT_STARTDATE", "SERVERPART_CODE", "SHOPCODE", "MACHINECODE", "FLAG" });
}
catch (Exception)
{
strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
break;
}
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
}
else
{
List _DataEndaccount =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataEndaccount != null && _DataEndaccount.Count > 0)
{
string str_DataCode = "";
//获取数据包中的服务区、门店、收银机信息
var _DataCodeList = _DataEndaccount.Select(p => new
{
p.SERVERPARTCODE,
p.SHOPCODE,
p.MACHINECODE
}).Distinct().ToArray();
//拼接组成已上传数据查询条件值
foreach (var _DataCodeTemp in _DataCodeList)
{
str_DataCode += (string.IsNullOrWhiteSpace(str_DataCode) ? "'" : ",'") +
_DataCodeTemp.SERVERPARTCODE.Trim() +
_DataCodeTemp.SHOPCODE.Trim() +
_DataCodeTemp.MACHINECODE.Trim() + "'";
}
//获取数据包中最小时间
var _MinDate = _DataEndaccount.Min(p => p.ENDACCOUNT_STARTDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
//获取数据包中最大时间
var _MaxDate = _DataEndaccount.Max(p => p.ENDACCOUNT_STARTDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ENDACCOUNT_ID,ENDACCOUNT_STARTDATE,
SERVERPARTCODE,SHOPCODE,MACHINECODE,FLAG
FROM HIGHWAY_EXCHANGE.T_ENDACCOUNT
WHERE SERVERPARTCODE || SHOPCODE || MACHINECODE IN ({str_DataCode}) AND
ENDACCOUNT_STARTDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.HIGHWAY.ENDACCOUNT _ENDACCOUNT in _DataEndaccount)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ENDACCOUNT_ID = {_ENDACCOUNT.ENDACCOUNT_ID} AND " +
$" ENDACCOUNT_STARTDATE = '{_ENDACCOUNT.ENDACCOUNT_STARTDATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_ENDACCOUNT.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_ENDACCOUNT.SHOPCODE}' AND " +
$" MACHINECODE = '{_ENDACCOUNT.MACHINECODE}' AND " +
$" FLAG = {_ENDACCOUNT.FLAG}");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_ENDACCOUNT);
continue;
}
}
//需更新的集合
_InsertList.Add(_ENDACCOUNT);
}
if (_InsertList.Count + _UpdateList.Count == _DataEndaccount.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_ENDACCOUNT");
}
if (_UpdateList.Count > 0)
{
try
{
OperationDataHelper.UpdateTableData(
_OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_ENDACCOUNT",
new string[] { "ENDACCOUNT_ID", "ENDACCOUNT_STARTDATE",
"SERVERPARTCODE", "SHOPCODE", "MACHINECODE", "FLAG" });
}
catch (Exception)
{
strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
break;
}
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_PERSONSELL":
#region HIGHWAY_EXCHANGE.T_PERSONSELL 收银员交班表【旧】
//必填项
_RequiredDatas = new string[] { "PERSONSELL_ID", "SERVERPARTCODE", "SHOPCODE", "MACHINECODE", "STARTDATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataPersonsell =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataPersonsell != null && _DataPersonsell.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataPersonsell.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataPersonsell.Min(p => p.STARTDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataPersonsell.Max(p => p.STARTDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT PERSONSELL_ID,SERVERPARTCODE,
SHOPCODE,MACHINECODE,STARTDATE
FROM HIGHWAY_EXCHANGE.T_PERSONSELL
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
STARTDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.HIGHWAY.PERSONSELL _PERSONSELL in _DataPersonsell)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" PERSONSELL_ID = {_PERSONSELL.PERSONSELL_ID} AND " +
$" STARTDATE = '{_PERSONSELL.STARTDATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_PERSONSELL.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_PERSONSELL.SHOPCODE}' AND " +
$" MACHINECODE = '{_PERSONSELL.MACHINECODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_PERSONSELL);
continue;
}
}
//需更新的集合
_InsertList.Add(_PERSONSELL);
}
if (_InsertList.Count + _UpdateList.Count == _DataPersonsell.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_PERSONSELL");
}
if (_UpdateList.Count > 0)
{
try
{
OperationDataHelper.UpdateTableData(
_OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_PERSONSELL",
new string[] { "PERSONSELL_ID", "SERVERPARTCODE", "SHOPCODE",
"MACHINECODE", "STARTDATE" });
}
catch (Exception)
{
strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
break;
}
}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYSALE":
#region HIGHWAY_EXCHANGE.T_COMMODITYSALE 单品报表【旧】
//必填项
_RequiredDatas = new string[] { "COMMODITYSALE_ID", "STARTDATE",
"ENDDATE", "SERVERPARTCODE", "SHOPCODE" ,"COMMODITY_CODE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataCommoditysale =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataCommoditysale != null && _DataCommoditysale.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataCommoditysale.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataCommoditysale.Min(p => p.ENDDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataCommoditysale.Max(p => p.ENDDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT COMMODITYSALE_ID,STARTDATE,ENDDATE,
SERVERPARTCODE,SHOPCODE,MACHINECODE,COMMODITY_CODE
FROM HIGHWAY_EXCHANGE.T_COMMODITYSALE
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
ENDDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.HIGHWAY.COMMODITYSALE _COMMODITYSALE in _DataCommoditysale)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" STARTDATE = '{_COMMODITYSALE.STARTDATE.ToString()}' AND " +
$" ENDDATE = '{_COMMODITYSALE.ENDDATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_COMMODITYSALE.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_COMMODITYSALE.SHOPCODE}' AND " +
$" COMMODITY_CODE = '{_COMMODITYSALE.COMMODITY_CODE}' AND " +
$" COMMODITYSALE_ID = {_COMMODITYSALE.COMMODITYSALE_ID}");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_COMMODITYSALE);
continue;
}
}
//需更新的集合
_InsertList.Add(_COMMODITYSALE);
}
if (_InsertList.Count + _UpdateList.Count == _DataCommoditysale.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_COMMODITYSALE");
}
//if (_UpdateList.Count > 0)
//{
// try
// {
// OperationDataHelper.UpdateTableData(
// _OracleHelper, _UpdateList, "HIGHWAY_EXCHANGE.T_COMMODITYSALE",
// new string[] { "STARTDATE", "ENDDATE", "SERVERPARTCODE", "SHOPCODE", "COMMODITY_CODE" });
// }
// catch (Exception)
// {
// strBack = "{\"error\": 2 ,\"msg\": \"添加成功，更新失败！\",\"rows\":" +
// JsonHelper.ListToJson(_UpdateList, "rows", false) + "}";
// break;
// }
//}
//添加、更新成功无需传递任何参数。
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_TRANSFER_SALE":
#region HIGHWAY_EXCHANGE.T_TRANSFER_SALE 单品数据集合
//必填项
_RequiredDatas = new string[] { "TRANSFER_ID", "SERVERPARTCODE", "TRANSFER_FIRSTDATAS" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataTransfer_sale =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataTransfer_sale != null && _DataTransfer_sale.Count > 0)
{
var _MinDate = _DataTransfer_sale.Min(p => p.ENDDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataTransfer_sale.Max(p => p.ENDDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
string.Format(@"SELECT TRANSFER_ID,SERVERPARTCODE,
SHOPCODE,STARTDATE,ENDDATE,TRANSFER_FIRSTDATAS
FROM HIGHWAY_EXCHANGE.T_TRANSFER_SALE
WHERE SERVERPARTCODE = '{0}' AND ENDDATE BETWEEN
TO_DATE('{1}','YYYY/MM/DD HH24:MI:SS') AND TO_DATE('{2}','YYYY/MM/DD HH24:MI:SS')",
codes[0], _MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss"),
_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss"))).Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.TRANSFER_SALE _TRANSFER_SALE in _DataTransfer_sale)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" TRANSFER_ID = {_TRANSFER_SALE.TRANSFER_ID} AND " +
$" SERVERPARTCODE = '{_TRANSFER_SALE.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_TRANSFER_SALE.SHOPCODE}' AND " +
$" ENDDATE = '{_TRANSFER_SALE.ENDDATE}' AND " +
$" TRANSFER_FIRSTDATAS = '{_TRANSFER_SALE.TRANSFER_FIRSTDATAS}' ");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_TRANSFER_SALE);
continue;
}
}
//需更新的集合
_InsertList.Add(_TRANSFER_SALE);
}
if (_InsertList.Count + _UpdateList.Count == _DataTransfer_sale.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_TRANSFER_SALE");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_EXCEPTION":
#region HIGHWAY_EXCHANGE.T_EXCEPTION 异常稽核数据表【旧】
//必填项
_RequiredDatas = new string[] { "EXCEPTION_ID", "EXCEPTION_DATE",
"SERVERPARTCODE", "SHOPCODE", "MACHINECODE", "EXCEPTIONTYPE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataException = JsonHelper.JSONStringToListUTC(jsonString);
if (_DataException != null && _DataException.Count > 0)
{
var _MinDate = _DataException.Min(p => p.EXCEPTION_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataException.Max(p => p.EXCEPTION_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.MinValue)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT EXCEPTION_ID,EXCEPTION_DATE,SERVERPARTCODE,
SHOPCODE,MACHINECODE,EXCEPTIONTYPE
FROM HIGHWAY_EXCHANGE.T_EXCEPTION
WHERE SERVERPARTCODE = '{codes[0]}' AND EXCEPTION_DATE BETWEEN
TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.EXCEPTION _EXCEPTION in _DataException)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" EXCEPTION_ID = {_EXCEPTION.EXCEPTION_ID} AND " +
$" EXCEPTION_DATE = '{_EXCEPTION.EXCEPTION_DATE.ToString()}' AND " +
$" SERVERPARTCODE = '{_EXCEPTION.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_EXCEPTION.SHOPCODE}' AND " +
$" MACHINECODE = '{_EXCEPTION.MACHINECODE}' AND " +
$" EXCEPTIONTYPE = '{_EXCEPTION.EXCEPTIONTYPE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_EXCEPTION);
continue;
}
}
//需更新的集合
_InsertList.Add(_EXCEPTION);
}
if (_InsertList.Count + _UpdateList.Count == _DataException.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_EXCEPTION");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ABNORMALITY":
#region HIGHWAY_EXCHANGE.T_ABNORMALITY 异常操作记录主表
//必填项
_RequiredDatas = new string[] { "ABNORMALITY_CODE", "SERVERPART_CODE", "SHOPCODE",
"ABNORMALITY_TIME", "ABNORMALITY_TYPE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataAbnormality =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataAbnormality != null && _DataAbnormality.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataAbnormality.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataAbnormality.Min(p => p.ABNORMALITY_TIME);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataAbnormality.Max(p => p.ABNORMALITY_TIME);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ABNORMALITY_CODE
FROM HIGHWAY_EXCHANGE.T_ABNORMALITY
WHERE SERVERPART_CODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
ABNORMALITY_TIME BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.ABNORMALITY _ABNORMALITY in _DataAbnormality)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ABNORMALITY_CODE = '{_ABNORMALITY.ABNORMALITY_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_ABNORMALITY);
continue;
}
}
//需更新的集合
_InsertList.Add(_ABNORMALITY);
}
if (_InsertList.Count + _UpdateList.Count == _DataAbnormality.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_ABNORMALITY", true,
"ABNORMALITY_ID", "HIGHWAY_EXCHANGE.SEQ_ABNORMALITY.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ABNORMALITYDETAIL":
#region HIGHWAY_EXCHANGE.T_ABNORMALITYDETAIL 异常操作稽核统计表
//必填项
_RequiredDatas = new string[] { "ABNORMALITYDETIL_CODE", "ABNORMALITY_CODE",
"COMMODITY_CODE", "COMMODITY_BARCODE", "OPERATEDATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataAbnormalityDetail =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataAbnormalityDetail != null && _DataAbnormalityDetail.Count > 0)
{
var _MinDate = _DataAbnormalityDetail.Min(p => p.OPERATEDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataAbnormalityDetail.Max(p => p.OPERATEDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ABNORMALITYDETIL_CODE
FROM HIGHWAY_EXCHANGE.T_ABNORMALITYDETAIL
WHERE ABNORMALITYDETIL_CODE LIKE '{codes[0] + codes[1] + "%"}' AND
OPERATEDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.ABNORMALITYDETAIL _ABNORMALITYDETAIL in _DataAbnormalityDetail)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ABNORMALITYDETIL_CODE = '{_ABNORMALITYDETAIL.ABNORMALITYDETIL_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_ABNORMALITYDETAIL);
continue;
}
}
//需更新的集合
_InsertList.Add(_ABNORMALITYDETAIL);
}
if (_InsertList.Count + _UpdateList.Count == _DataAbnormalityDetail.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_ABNORMALITYDETAIL", true,
"ABNORMALITYDETIL_ID", "HIGHWAY_EXCHANGE.SEQ_ABNORMALITYDETAIL.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_ABNOEMALITYANALYSIS":
#region HIGHWAY_EXCHANGE.T_ABNOEMALITYANALYSIS 异常操作稽核统计表
//必填项
_RequiredDatas = new string[] { "ABNOEMALITYANALYSIS_CODE", "SERVERPART_CODE",
"SHOPCODE", "ENDACCOUNT_START", "ENDACCOUNT_END", "ABNOEMALITY_TYPE",
"OPERATEDATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataAbnoemalityAnalysis =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataAbnoemalityAnalysis != null && _DataAbnoemalityAnalysis.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataAbnoemalityAnalysis.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataAbnoemalityAnalysis.Min(p => p.OPERATEDATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataAbnoemalityAnalysis.Max(p => p.OPERATEDATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT ABNOEMALITYANALYSIS_CODE
FROM HIGHWAY_EXCHANGE.T_ABNOEMALITYANALYSIS
WHERE SERVERPART_CODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
OPERATEDATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.ABNOEMALITYANALYSIS _ABNOEMALITYANALYSIS in _DataAbnoemalityAnalysis)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" ABNOEMALITYANALYSIS_CODE = '{_ABNOEMALITYANALYSIS.ABNOEMALITYANALYSIS_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_ABNOEMALITYANALYSIS);
continue;
}
}
//需更新的集合
_InsertList.Add(_ABNOEMALITYANALYSIS);
}
if (_InsertList.Count + _UpdateList.Count == _DataAbnoemalityAnalysis.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_ABNOEMALITYANALYSIS", true,
"ABNOEMALITYANALYSIS_ID", "HIGHWAY_EXCHANGE.SEQ_ABNOEMALITYANALYSIS.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_CUSTOMERINFO":
#region HIGHWAY_EXCHANGE.T_CUSTOMERINFO 客群分析数据表
//必填项
_RequiredDatas = new string[] { "SELLMASTER_CODE", "SERVERPARTCODE", "ACQUISITION_TIME" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataCustomerinfo =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataCustomerinfo != null && _DataCustomerinfo.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataCustomerinfo.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataCustomerinfo.Min(p => p.ACQUISITION_TIME);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataCustomerinfo.Max(p => p.ACQUISITION_TIME);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT SELLMASTER_CODE,ACQUISITION_TIME,
SERVERPARTCODE,SHOPCODE,MACHINECODE
FROM HIGHWAY_EXCHANGE.T_CUSTOMERINFO
WHERE SERVERPARTCODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
MACHINECODE = '{codes[3]}' AND ACQUISITION_TIME BETWEEN
TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.CUSTOMERINFO _CUSTOMERINFO in _DataCustomerinfo)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" SELLMASTER_CODE = '{_CUSTOMERINFO.SELLMASTER_CODE}' AND " +
$" ACQUISITION_TIME = '{_CUSTOMERINFO.ACQUISITION_TIME.ToString()}' AND " +
$" SERVERPARTCODE = '{_CUSTOMERINFO.SERVERPARTCODE}' AND " +
$" SHOPCODE = '{_CUSTOMERINFO.SHOPCODE}' AND " +
$" MACHINECODE = '{_CUSTOMERINFO.MACHINECODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_CUSTOMERINFO);
continue;
}
}
//需更新的集合
_InsertList.Add(_CUSTOMERINFO);
}
if (_InsertList.Count + _UpdateList.Count == _DataCustomerinfo.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData_OracleParameter(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_CUSTOMERINFO");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_CUSTOMERANALYSIS":
#region HIGHWAY_EXCHANGE.T_CUSTOMERANALYSIS 客单时段统计表
//必填项
_RequiredDatas = new string[] { "CUSTOMERANALYSIS_CODE", "SERVERPART_CODE",
"SHOPCODE", "BUSINESSTYPE", "CUSTOMERANALYSIS_DATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataCustomerAnalysis =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataCustomerAnalysis != null && _DataCustomerAnalysis.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataCustomerAnalysis.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataCustomerAnalysis.Min(p => p.CUSTOMERANALYSIS_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataCustomerAnalysis.Max(p => p.CUSTOMERANALYSIS_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT CUSTOMERANALYSIS_CODE
FROM HIGHWAY_EXCHANGE.T_CUSTOMERANALYSIS
WHERE SERVERPART_CODE = '{codes[0]}' AND SHOPCODE = ({_strShopCode}) AND
CUSTOMERANALYSIS_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.CUSTOMERANALYSIS _CUSTOMERANALYSIS in _DataCustomerAnalysis)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" CUSTOMERANALYSIS_CODE = '{_CUSTOMERANALYSIS.CUSTOMERANALYSIS_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_CUSTOMERANALYSIS);
continue;
}
}
//需更新的集合
_InsertList.Add(_CUSTOMERANALYSIS);
}
if (_InsertList.Count + _UpdateList.Count == _DataCustomerAnalysis.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_CUSTOMERANALYSIS", true,
"CUSTOMERANALYSIS_ID", "HIGHWAY_EXCHANGE.SEQ_CUSTOMERANALYSIS.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SALESANALYSIS":
#region HIGHWAY_EXCHANGE.T_SALESANALYSIS 客单区段统计表
//必填项
_RequiredDatas = new string[] { "SALESANALYSIS_CODE", "SERVERPART_CODE",
"SHOPCODE", "SALESANALYSIS_DATE", "SALEAMOUNT_START", "SALEAMOUNT_END" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataSalesAnalysis =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataSalesAnalysis != null && _DataSalesAnalysis.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataSalesAnalysis.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataSalesAnalysis.Min(p => p.SALESANALYSIS_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataSalesAnalysis.Max(p => p.SALESANALYSIS_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT SALESANALYSIS_CODE
FROM HIGHWAY_EXCHANGE.T_SALESANALYSIS
WHERE SERVERPART_CODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
SALESANALYSIS_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.SALESANALYSIS _SALESANALYSIS in _DataSalesAnalysis)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" SALESANALYSIS_CODE = '{_SALESANALYSIS.SALESANALYSIS_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_SALESANALYSIS);
continue;
}
}
//需更新的集合
_InsertList.Add(_SALESANALYSIS);
}
if (_InsertList.Count + _UpdateList.Count == _DataSalesAnalysis.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_SALESANALYSIS", true,
"SALESANALYSIS_ID", "HIGHWAY_EXCHANGE.SEQ_SALESANALYSIS.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_COMMODITYANALYSIS":
#region HIGHWAY_EXCHANGE.T_COMMODITYANALYSIS 推荐单品统计表
//必填项
_RequiredDatas = new string[] { "COMMODITYANALYSIS_CODE", "SERVERPART_CODE",
"BUSINESSTYPE","SHOPCODE", "COMMODITY_CODE", "COMMODITYANALYSIS_DATE" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataCommodityAnalysis =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataCommodityAnalysis != null && _DataCommodityAnalysis.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataCommodityAnalysis.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataCommodityAnalysis.Min(p => p.COMMODITYANALYSIS_DATE);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataCommodityAnalysis.Max(p => p.COMMODITYANALYSIS_DATE);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT COMMODITYANALYSIS_CODE
FROM HIGHWAY_EXCHANGE.T_COMMODITYANALYSIS
WHERE SERVERPART_CODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
COMMODITYANALYSIS_DATE BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.COMMODITYANALYSIS _COMMODITYANALYSIS in _DataCommodityAnalysis)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" COMMODITYANALYSIS_CODE = '{_COMMODITYANALYSIS.COMMODITYANALYSIS_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_COMMODITYANALYSIS);
continue;
}
}
//需更新的集合
_InsertList.Add(_COMMODITYANALYSIS);
}
if (_InsertList.Count + _UpdateList.Count == _DataCommodityAnalysis.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_COMMODITYANALYSIS", true,
"COMMODITYANALYSIS_ID", "HIGHWAY_EXCHANGE.SEQ_COMMODITYANALYSIS.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
case "HIGHWAY_EXCHANGE.T_SALESINTERVALANALYSIS":
#region HIGHWAY_EXCHANGE.T_SALESINTERVALANALYSIS 客单时段统计表
//必填项
_RequiredDatas = new string[] { "SALESINTERVALANALYSIS_CODE", "SERVERPART_CODE",
"SHOPCODE", "DATE_START", "DATE_END","AMOUNT_START", "AMOUNT_END" };
_ISCheckData = ISCheckData(jsonString, _RequiredDatas);
if (_ISCheckData != null)
{
strBack = _ISCheckData;
break;
}
List _DataSalesIntervalAnalysis =
JsonHelper.JSONStringToListUTC(jsonString);
if (_DataSalesIntervalAnalysis != null && _DataSalesIntervalAnalysis.Count > 0)
{
string _strShopCode = "";
var _ShopCodeList = _DataSalesIntervalAnalysis.Select(p => p.SHOPCODE).Distinct().ToArray();
foreach (var _ShopCodeTemp in _ShopCodeList)
{
_strShopCode += (string.IsNullOrWhiteSpace(_strShopCode) ? "'" : ",'") + _ShopCodeTemp.Trim() + "'";
}
var _MinDate = _DataSalesIntervalAnalysis.Min(p => p.DATE_END);
if (_MinDate == null || _MinDate.Value == DateTime.MinValue)
{
_MinDate = DateTime.Now;
}
var _MaxDate = _DataSalesIntervalAnalysis.Max(p => p.DATE_END);
if (_MaxDate == null || _MaxDate.Value == DateTime.Now)
{
_MaxDate = DateTime.Now;
}
//该服务区所有数据
DataTable _DataTable = _OracleHelper.ExcuteSqlGetDataSet(
$@"SELECT SALESINTERVALANALYSIS_CODE
FROM HIGHWAY_EXCHANGE.T_SALESINTERVALANALYSIS
WHERE SERVERPART_CODE = '{codes[0]}' AND SHOPCODE IN ({_strShopCode}) AND
DATE_END BETWEEN TO_DATE('{_MinDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS') AND
TO_DATE('{_MaxDate.Value.ToString("yyyy/MM/dd HH:mm:ss")}','YYYY/MM/DD HH24:MI:SS')").Tables[0];
List _InsertList = new List();//需插入的集合
List _UpdateList = new List();//需更新的集合
DataRow[] _DataRows = null; //临时DataRow数组
foreach (Model.EXCHANGE.SALESINTERVALANALYSIS _SALESINTERVALANALYSIS in _DataSalesIntervalAnalysis)
{
if (_DataTable != null && _DataTable.Rows.Count > 0)
{
_DataRows = _DataTable.Select(
$" SALESINTERVALANALYSIS_CODE = '{_SALESINTERVALANALYSIS.SALESINTERVALANALYSIS_CODE}'");
if (_DataRows != null && _DataRows.Length > 0)
{
//记录已存在、需更新的数据
_UpdateList.Add(_SALESINTERVALANALYSIS);
continue;
}
}
//需更新的集合
_InsertList.Add(_SALESINTERVALANALYSIS);
}
if (_InsertList.Count + _UpdateList.Count == _DataSalesIntervalAnalysis.Count)
{
if (_InsertList.Count > 0)
{
OperationDataHelper.InsertTableData(
_OracleHelper, _InsertList, "HIGHWAY_EXCHANGE.T_SALESINTERVALANALYSIS", true,
"SALESINTERVALANALYSIS_ID", "HIGHWAY_EXCHANGE.SEQ_SALESINTERVALANALYSIS.NEXTVAL");
}
strBack = "{\"error\": 1 ,\"msg\": \"添加成功！\",\"rows\":[]}";
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：数据分割失败！\",\"rows\":[]}";
}
}
else
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
#endregion
break;
break;
default:
strBack = "{\"error\": -1 ,\"msg\": \"数据上传表类型不正确\",\"rows\":[]}";
break;
}
}
catch (Exception ex)
{
strBack = "{\"error\": -1 ,\"msg\": \"添加失败：" + ex.Message + "\",\"rows\":[]}";
}
return strBack;
}
#endregion
#region 方法 -> 检查必要参数
///
/// 检查必要参数
///
///
///
///
private static string ISCheckData(string jsonString, string[] _RequiredDatas, int maxDataQuantity = 4)
{
if (!string.IsNullOrEmpty(jsonString))
{
JArray _JsonArray = JArray.Parse(jsonString);
if (_JsonArray.Count > maxDataQuantity * 100)
{
return "{\"error\": -1 ,\"msg\": \"添加失败：json数据包不可超过400条数据\",\"rows\":[]}";
}
for (int i = 0; i < _JsonArray.Count; i++)
{
JObject _Json = JObject.Parse(_JsonArray[i].ToString());
foreach (string _RequiredData in _RequiredDatas)
{
if ((_Json.Property(_RequiredData.ToUpper()) == null && _Json.Property(_RequiredData.ToLower()) == null) ||
(_Json.Property(_RequiredData.ToUpper()) != null && _Json.Property(_RequiredData.ToUpper()).Value.ToString() == "") ||
(_Json.Property(_RequiredData.ToLower()) != null && _Json.Property(_RequiredData.ToLower()).Value.ToString() == ""))
{
return "{\"error\": -1 ,\"msg\": \"添加失败：第" + (i + 1) +
"组数据中，JSON缺少必要参数：" + _RequiredData + "\",\"rows\":[]}";
}
}
}
return null;
}
else
{
return "{\"error\": -1 ,\"msg\": \"添加失败：JSON参数解析异常！\",\"rows\":[]}";
}
}
#endregion
}
}